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

> Discover how Pentagi stores and manages data using PostgreSQL, GORM, and SQLC. Learn about database architecture and type-safe SQL queries for efficient data handling.

- Repository: [VXControl/pentagi](https://github.com/vxcontrol/pentagi)
- Tags: architecture
- Published: 2026-03-21

---

**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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/users.go)) maps authentication accounts with fields like `Hash`, `Mail`, `RoleID`, and `PasswordChangeRequired`. Similarly, the `Vecstorelog` struct (lines 38–52 in [`vecstorelogs.go`](https://github.com/vxcontrol/pentagi/blob/main/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:

```go
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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/db.go)) abstracts database operations to work interchangeably with `*sql.DB` connections and transactions (`*sql.Tx`):

```go
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`](https://github.com/vxcontrol/pentagi/blob/main/vecstorelogs.sql.go) provides `CreateVecstorelog`, `GetVecstorelog`, and related functions with compile-time SQL validation:

```go
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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/backend/pkg/config/config.go) (line 22). The `Screenshot` model (defined in [`backend/pkg/server/models/screenshots.go`](https://github.com/vxcontrol/pentagi/blob/main/backend/pkg/server/models/screenshots.go)) stores only relative file paths in the database; services handle actual file I/O:

```go
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`](https://github.com/vxcontrol/pentagi/blob/main/backend/pkg/server/services/users.go):

```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`](https://github.com/vxcontrol/pentagi/blob/main/backend/pkg/database/vecstorelogs.sql.go):

```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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/backend/pkg/database/db.go).
- **Key models** include `User` (lines 72–86 of [`users.go`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/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`](https://github.com/vxcontrol/pentagi/blob/main/backend/pkg/database/vecstorelogs.sql.go). This hybrid approach balances developer productivity with runtime efficiency for AI-driven penetration testing workflows.