How Data is Stored and Managed within Pentagi: PostgreSQL, GORM, and SQLC Architecture

Pentagi persists all operational data in a PostgreSQL database with the optional pgvector extension, utilizing GORM for object-relational mapping and SQLC for type-safe SQL queries, while binary artifacts like screenshots are stored on the filesystem.

The open-source penetration testing platform vxcontrol/pentagi implements a robust dual-layer persistence strategy that combines ORM convenience with raw SQL performance. This article examines exactly how Pentagi stores and manages data across PostgreSQL relations, vector embeddings, and local filesystem storage, based on the actual source code implementation.

Database Configuration and Connection Setup

Pentagi centralizes its storage configuration in backend/pkg/config/config.go. The DatabaseURL field (lines 18–20) defines the PostgreSQL DSN, defaulting to postgres://pentagiuser:pentagipass@pgvector:5432/pentagidb?sslmode=disable. For binary artifacts that exceed efficient database storage, the DataDir field (line 22) specifies a filesystem directory path where screenshots and uploaded files reside.

Schema evolution is handled incrementally via Goose migrations stored in backend/migrations/sql/. The system executes these automatically at startup—beginning with the initial state migration—to ensure the database schema matches the application code without manual intervention.

Dual Persistence Architecture: GORM and SQLC

Pentagi employs a hybrid approach that leverages both GORM for high-level ORM operations and SQLC for performance-critical queries. This architecture allows developers to use object-oriented patterns for complex relationships while retaining the ability to write optimized, compile-time-checked SQL for hot paths.

GORM Model Layer with Struct Validation

The repository defines database schemas through GORM model structs located in backend/pkg/server/models/. Each model includes validation hooks and struct tags for automatic migration and relationship mapping.

The User struct (lines 72–86 in users.go) maps authentication accounts with fields like Hash, Mail, RoleID, and PasswordChangeRequired. Similarly, the Vecstorelog struct (lines 38–52 in vecstorelogs.go) records vector-store operations with fields for Initiator, Executor, Action, and foreign keys to FlowID. Other critical models include Flow, Task, and Subtask for penetration test hierarchies, Screenshot for binary metadata, and AgentLog for operational telemetry.

Every model implements a Valid() method using the validate package for struct-level validation, plus a Validate(*gorm.DB) wrapper that integrates errors into GORM's error chain before persistence:

func (ml Vecstorelog) Valid() error { return validate.Struct(ml) }
func (ml Vecstorelog) Validate(db *gorm.DB) {
    if err := ml.Valid(); err != nil { db.AddError(err) }
}

This pattern ensures data integrity constraints are enforced at the application layer, as seen in backend/pkg/server/models/vecstorelogs.go (lines 59–69).

SQLC Type-Safe Query Layer

For performance-sensitive operations, Pentagi uses SQLC-generated code located in backend/pkg/database/. The DBTX interface (lines 12–17 in db.go) abstracts database operations to work interchangeably with *sql.DB connections and transactions (*sql.Tx):

type DBTX interface {
    ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
    PrepareContext(context.Context, string) (*sql.Stmt, error)
    QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
    QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

SQLC generates type-safe CRUD methods for each table. For instance, vecstorelogs.sql.go provides CreateVecstorelog, GetVecstorelog, and related functions with compile-time SQL validation:

func (q *Queries) CreateVecstorelog(ctx context.Context, arg CreateVecstorelogParams) (Vecstorelog, error) { … }

Service Layer and Transaction Management

Business logic resides in service structs within backend/pkg/server/services/. These services receive a GORM *gorm.DB instance (or transactions) and orchestrate CRUD operations with validation and caching.

The UserService struct (lines 41–88 in users.go) demonstrates this pattern, utilizing scoped queries (db.Scopes(scope)) for consistent filtering and a userCache from backend/pkg/server/auth/user_cache.go for authentication optimization. Key methods include GetCurrentUser (loading composite DTOs with roles), ChangePasswordCurrentUser (handling bcrypt encryption and safe map-based updates), and GetUsers (supporting pagination via rdb.TableQuery).

For atomic multi-step operations—such as creating a Flow with associated Tasks and Subtasks—services use the DBTX interface with Queries.WithTx to ensure rollback capability on any failure, guaranteeing data consistency across related tables.

File-Based Storage for Binary Artifacts

While relational data lives in PostgreSQL, binary objects like screenshots are stored on the filesystem under the DataDir path configured in backend/pkg/config/config.go (line 22). The Screenshot model (defined in backend/pkg/server/models/screenshots.go) stores only relative file paths in the database; services handle actual file I/O:

func (s *ScreenshotService) GetScreenshot(path string) ([]byte, error) {
    fullPath := filepath.Join(config.Get().DataDir, path)
    return os.ReadFile(fullPath)
}

This separation prevents database bloat while maintaining referential integrity through the path metadata stored in PostgreSQL.

Practical Implementation Examples

Creating a User with GORM Validation

The following service-level implementation demonstrates model validation and secure password handling using the patterns found in backend/pkg/server/services/users.go:

func (s *UserService) CreateUser(c *gin.Context) {
    var form models.User
    if err := c.ShouldBindJSON(&form); err != nil {
        response.Error(c, response.ErrInvalidPayload, err)
        return
    }

    if err := form.Valid(); err != nil {
        response.Error(c, response.ErrInvalidData, err)
        return
    }

    hash, _ := rdb.EncryptPassword("RandomPass123!")
    user := models.User{
        Hash:   hash,
        Type:   models.UserTypeLocal,
        Mail:   form.Mail,
        Name:   form.Name,
        RoleID: models.RoleUser,
    }

    if err := s.db.Create(&user).Error; err != nil {
        response.Error(c, response.ErrInternal, err)
        return
    }

    response.Success(c, http.StatusCreated, user)
}

Recording Vector Store Operations with SQLC

For high-performance logging of retrieval operations, services utilize the SQLC-generated interface as implemented in backend/pkg/database/vecstorelogs.sql.go:

func (svc *VecstoreService) RecordRetrieve(ctx context.Context, req models.VecstoreRequest) error {
    log := models.Vecstorelog{
        Initiator: req.Initiator,
        Executor:  req.Executor,
        Filter:    req.FilterJSON,
        Query:     req.Query,
        Action:    models.VecstoreActionTypeRetrieve,
        FlowID:    req.FlowID,
        TaskID:    &req.TaskID,
        SubtaskID: &req.SubtaskID,
    }

    if err := log.Valid(); err != nil {
        return err
    }

    q := database.New(svc.db)
    _, err := q.CreateVecstorelog(ctx, database.CreateVecstorelogParams{
        Initiator: string(log.Initiator),
        Executor:  string(log.Executor),
        Filter:    log.Filter,
        Query:     log.Query,
        Action:    string(log.Action),
        FlowID:    log.FlowID,
        TaskID:    log.TaskID,
        SubtaskID: log.SubtaskID,
    })
    return err
}

Summary

• Pentagi stores all relational data in PostgreSQL with optional pgvector support for embeddings, configured via DatabaseURL in backend/pkg/config/config.go (lines 18–20).
• Schema management uses Goose migrations applied automatically at startup from backend/migrations/sql/.
• Dual persistence strategy: GORM provides ORM mapping and validation (with Valid() and Validate() methods on models), while SQLC delivers type-safe, high-performance queries via the DBTX interface in backend/pkg/database/db.go.
• Key models include User (lines 72–86 of users.go), Vecstorelog, Flow, Task, Subtask, and Screenshot, all defined in backend/pkg/server/models/.
• Transaction safety is enforced through DBTX and Queries.WithTx, allowing atomic operations across multiple tables with automatic rollback.
• Binary artifacts are filesystem-backed, with the DataDir configuration (line 22 of config.go) determining storage location while database tables store relative paths.

Frequently Asked Questions

Does Pentagi support database engines other than PostgreSQL?

The source code specifically expects a PostgreSQL DSN format in backend/pkg/config/config.go, and the migration files in backend/migrations/sql/ utilize PostgreSQL-specific syntax including the pgvector extension for vector storage. While the DBTX interface is generic enough to support other engines, the generated SQLC queries and migration system are tightly coupled to PostgreSQL features.

How does Pentagi handle database schema migrations?

Pentagi uses the Goose migration tool to manage schema evolution. Migration files are stored in backend/migrations/sql/ (such as 20241026_115120_initial_state.sql) and are executed automatically when the application starts, ensuring the database schema remains synchronized with the application code without manual intervention.

What validation mechanisms exist for data integrity?

Every GORM model implements a Valid() method using the validate package for struct-level validation, and a Validate(*gorm.DB) hook (as seen in backend/pkg/server/models/vecstorelogs.go lines 59–69) that adds validation failures to GORM's error chain before database insertion. This ensures data integrity constraints are enforced at the application layer before reaching PostgreSQL.

Why does Pentagi use both GORM and SQLC instead of just one ORM?

GORM handles complex object relationships, automatic migrations, and ORM convenience for standard CRUD operations, while SQLC provides compile-time SQL validation and optimal performance for critical paths like vector store logging in backend/pkg/database/vecstorelogs.sql.go. This hybrid approach balances developer productivity with runtime efficiency for AI-driven penetration testing workflows.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}