# How Multica Structures Its PostgreSQL Database Schema Using sqlc > Multica structures its PostgreSQL schema with versioned migrations and leverages sqlc for type-safe Go code generation from handwritten queries, ensuring a compile-time validated data layer. - Repository: [multica-ai/multica](https://github.com/multica-ai/multica) - Tags: architecture - Published: 2026-04-11 --- **Multica uses versioned SQL migrations for canonical schema definitions and sqlc to generate type-safe Go code from handwritten queries, creating a compile-time validated data layer.** The open-source project [multica-ai/multica](https://github.com/multica-ai/multica) implements a rigorous database architecture that separates schema evolution from data access. By combining PostgreSQL migrations with sqlc code generation, the backend achieves type safety without ORM overhead. This article examines the exact file structure, migration strategy, and generated code patterns found in the repository. ## Schema Definition Through Versioned Migrations Multica stores its canonical **database schema** in `server/migrations/*.sql`, treating these files as the single source of truth for table structures, constraints, and indexes. ### Core Entities and Relationships The initial migration at [`server/migrations/001_init.up.sql`](https://github.com/multica-ai/multica/blob/main/server/migrations/001_init.up.sql) establishes the foundational tables using standard PostgreSQL DDL with specific conventions: - **UUID primary keys**: Every table uses `DEFAULT gen_random_uuid()` for its primary key - **Audit timestamps**: All entities include `created_at` and `updated_at` columns - **Cascading relationships**: Foreign keys explicitly declare `ON DELETE CASCADE` where appropriate The migration creates a multi-tenant architecture centered on workspaces: ```sql -- Users table CREATE TABLE "user" ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); -- Workspaces (multi-tenant containers) CREATE TABLE workspace ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, slug TEXT UNIQUE NOT NULL, description TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); -- Membership linking table CREATE TABLE member ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES "user"(id) ON DELETE CASCADE, workspace_id UUID REFERENCES workspace(id) ON DELETE CASCADE, role TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Additional entities: agent, issue, comment, inbox_item, task_queue, etc. ``` Indexes are defined at the end of migration files to optimize common access patterns, such as `idx_issue_workspace` for filtering issues by workspace. ### Migration Strategy The migration system follows the `up/down` pattern applied via `make migrate-up`. Each version-controlled SQL file represents an immutable schema state, making the **PostgreSQL schema** reproducible across development, staging, and production environments. This approach ensures that the database structure evolves through explicit, reviewable changes rather than implicit model synchronization. ## Type-Safe Data Access with sqlc Multica generates its data-access layer using **sqlc**, a compiler that converts SQL queries into statically typed Go code. This creates two distinct locations for database-related code: handwritten SQL queries and machine-generated Go wrappers. ### Handwritten SQL Queries Developers define data operations in `server/pkg/db/queries/*.sql` using sqlc annotations that specify return cardinality. For example, [`server/pkg/db/queries/workspace.sql`](https://github.com/multica-ai/multica/blob/main/server/pkg/db/queries/workspace.sql) contains: ```sql -- name: ListWorkspaces :many SELECT w.* FROM workspace w JOIN member m ON m.workspace_id = w.id WHERE m.user_id = $1 ORDER BY w.created_at ASC; -- name: CreateWorkspace :one INSERT INTO workspace (name, slug, description, context, issue_prefix) VALUES ($1, $2, $3, $4, $5) RETURNING *; ``` The `:one`, `:many`, and `:exec` annotations instruct sqlc about the expected result shape, determining whether the generated function returns a single struct, a slice, or no data. ### Generated Go Code Running `make sqlc` (invoked automatically via `make dev`) processes these SQL files and emits type-safe wrappers in `server/pkg/db/generated/*.go`. The generated code in [`workspace.sql.go`](https://github.com/multica-ai/multica/blob/main/workspace.sql.go) mirrors the query definitions: ```go // ListWorkspaces returns all workspaces a user belongs to. func (q *Queries) ListWorkspaces(ctx context.Context, userID pgtype.UUID) ([]Workspace, error) { rows, err := q.db.Query(ctx, listWorkspaces, userID) // ... error handling and scanning } // CreateWorkspace inserts a new workspace. func (q *Queries) CreateWorkspace(ctx context.Context, arg CreateWorkspaceParams) (Workspace, error) { row := q.db.QueryRow(ctx, createWorkspace, arg.Name, arg.Slug, arg.Description, arg.Context, arg.IssuePrefix) // ... scanning into Workspace struct } ``` The generated structs match table schemas exactly. For example, the `Workspace` struct contains fields corresponding to every column in the `workspace` table, using appropriate pgx types like `pgtype.Text` for nullable columns. ## Transaction Handling and DB Interface The generated [`db.go`](https://github.com/multica-ai/multica/blob/main/db.go) file establishes a `DBTX` interface that abstracts both single connections (`pgx.Conn`) and transactions (`pgx.Tx`): ```go type DBTX interface { QueryRow(context.Context, string, ...interface{}) pgx.Row Query(context.Context, string, ...interface{}) (pgx.Rows, error) // ... other pgx methods } type Queries struct { db DBTX } // New creates a Queries instance bound to a connection or pool. func New(db DBTX) *Queries { return &Queries{db: db} } // WithTx returns a Queries instance that runs within the provided transaction. func (q *Queries) WithTx(tx pgx.Tx) *Queries { return &Queries{db: tx} } ``` This design allows handlers to use **the same generated methods** whether operating on a standalone connection or within a database transaction, ensuring consistent semantics and simplified testing. ## End-to-End Data Flow When the HTTP handler `CreateWorkspace` receives a request, the execution follows this pattern: 1. **Validation**: Parse and validate incoming JSON into params structs 2. **Connection Initialization**: Obtain a connection from `pgxpool` or begin a transaction 3. **Generated Method Invocation**: Call `q.CreateWorkspace(ctx, params)` which executes the exact INSERT statement defined in [`workspace.sql`](https://github.com/multica-ai/multica/blob/main/workspace.sql) 4. **Response Construction**: Return the populated `Workspace` struct directly to the JSON serializer All entities—issues, agents, comments, and inbox items—follow this identical pattern, creating a uniform **data layer architecture** across the entire service. ## Practical Implementation Examples ### Initializing the Query Interface The `New` function in [`server/pkg/db/generated/db.go`](https://github.com/multica-ai/multica/blob/main/server/pkg/db/generated/db.go) binds the generated queries to a live database connection: ```go package dbutil import ( "context" "github.com/jackc/pgx/v5/pgxpool" db "github.com/multica-ai/multica/server/pkg/db/generated" ) func NewQueries(ctx context.Context, dsn string) (*db.Queries, error) { pool, err := pgxpool.New(ctx, dsn) if err != nil { return nil, err } return db.New(pool), nil } ``` ### Creating Entities with Type-Safe Parameters This pattern from [`server/internal/handler/workspace.go`](https://github.com/multica-ai/multica/blob/main/server/internal/handler/workspace.go) demonstrates creating a workspace using the generated parameter structs: ```go func CreateMyWorkspace(ctx context.Context, q *db.Queries) (*db.Workspace, error) { params := db.CreateWorkspaceParams{ Name: "Acme Corp", Slug: "acme", Description: pgtype.Text{String: "Internal project hub", Valid: true}, Context: pgtype.Text{String: "Engineering", Valid: true}, IssuePrefix: "AC", } return q.CreateWorkspace(ctx, params) } ``` ### Transactional Queries with WithTx For operations requiring atomicity, handlers use `WithTx` to execute generated methods within a transaction: ```go func ListUserWorkspaces(ctx context.Context, q *db.Queries, userID uuid.UUID) ([]db.Workspace, error) { tx, err := beginTransaction(ctx) // hypothetical tx opener if err != nil { return nil, err } defer tx.Rollback(ctx) tq := q.WithTx(tx) // Same interface, transactional context workspaces, err := tq.ListWorkspaces(ctx, pgtype.UUID{ Bytes: userID, Valid: true, }) if err != nil { return nil, err } if err := tx.Commit(ctx); err != nil { return nil, err } return workspaces, nil } ``` ### Complex Entity Creation The issue creation flow in [`server/pkg/db/queries/issue.sql`](https://github.com/multica-ai/multica/blob/main/server/pkg/db/queries/issue.sql) and its generated counterpart demonstrates handling multiple foreign keys: ```go func CreateIssue(ctx context.Context, q *db.Queries, wsID, creatorID uuid.UUID) (*db.Issue, error) { params := db.CreateIssueParams{ WorkspaceID: pgtype.UUID{Bytes: wsID, Valid: true}, Title: "Implement sqlc layer", Description: pgtype.Text{String: "Add typed DB access", Valid: true}, Status: "backlog", Priority: "high", CreatorType: "member", CreatorID: pgtype.UUID{Bytes: creatorID, Valid: true}, } return q.CreateIssue(ctx, params) } ``` ## Key Files in the Data Layer | Purpose | Path | Description | |---------|------|-------------| | **Base schema DDL** | [`server/migrations/001_init.up.sql`](https://github.com/multica-ai/multica/blob/main/server/migrations/001_init.up.sql) | Creates tables, constraints, indexes, and relationships | | **Schema evolutions** | `server/migrations/*.sql` | Versioned up/down migrations for iterative changes | | **SQL query definitions** | `server/pkg/db/queries/*.sql` | Handwritten, annotated queries for sqlc compilation | | **Generated types** | `server/pkg/db/generated/*.go` | Auto-generated structs matching table schemas | | **Generated queries** | [`server/pkg/db/generated/workspace.sql.go`](https://github.com/multica-ai/multica/blob/main/server/pkg/db/generated/workspace.sql.go) (etc.) | Type-safe methods implementing each SQL query | | **DB abstraction** | [`server/pkg/db/generated/db.go`](https://github.com/multica-ai/multica/blob/main/server/pkg/db/generated/db.go) | `DBTX` interface, `New()` constructor, and `WithTx()` helper | ## Summary - **Schema source of truth**: Multica stores canonical DDL in [`server/migrations/001_init.up.sql`](https://github.com/multica-ai/multica/blob/main/server/migrations/001_init.up.sql), using UUID keys, timestamps, and cascading foreign keys. - **Code generation workflow**: Developers write SQL in `server/pkg/db/queries/*.sql` and run `make sqlc` to generate type-safe Go functions. - **Compile-time safety**: Column names and types are validated at generation time, eliminating runtime SQL errors. - **Transaction flexibility**: The `WithTx` method allows identical code to run inside or outside transactions without interface changes. - **Zero ORM overhead**: Direct SQL control with generated helpers eliminates reflection and complex mapping layers. ## Frequently Asked Questions ### What is sqlc and why does Multica use it? **sqlc** is a SQL-to-Go compiler that generates type-safe code from handwritten SQL queries. Multica uses it to maintain **compile-time type safety** without the complexity and reflection overhead of traditional ORMs. This approach allows developers to write raw PostgreSQL while receiving IDE autocomplete and type checking for database operations. ### How does Multica handle schema migrations? Multica uses ordered SQL migration files in `server/migrations/*.sql` that are applied via `make migrate-up`. Each file contains explicit `up` and `down` scripts, making schema changes version-controlled and reversible. This ensures the **PostgreSQL database schema** evolves predictably across environments without automatic migration tools modifying production data unexpectedly. ### Where does Multica store the actual SQL queries? Raw SQL queries live in `server/pkg/db/queries/*.sql` (e.g., [`workspace.sql`](https://github.com/multica-ai/multica/blob/main/workspace.sql), [`issue.sql`](https://github.com/multica-ai/multica/blob/main/issue.sql)). These files contain annotated SQL statements that sqlc reads to generate the Go code found in `server/pkg/db/generated/*.go`. This separation keeps the data-access contract explicit and under version control. ### How does Multica manage database transactions with sqlc? The generated `Queries` struct implements a `WithTx(tx)` method that returns a new `Queries` instance bound to the provided transaction. This allows handlers to begin a transaction, create a transactional query object, execute multiple generated methods atomically, and commit or roll back—all while using the same type-safe function signatures used for non-transactional queries.