How Multica Structures Its PostgreSQL Database Schema Using sqlc

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 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 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:

-- 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 contains:

-- 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 mirrors the query definitions:

// 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 file establishes a DBTX interface that abstracts both single connections (pgx.Conn) and transactions (pgx.Tx):

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
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 binds the generated queries to a live database connection:

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 demonstrates creating a workspace using the generated parameter structs:

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:

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 and its generated counterpart demonstrates handling multiple foreign keys:

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	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 (etc.)	Type-safe methods implementing each SQL query
DB abstraction	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, 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, 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.

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