Database Conventions

Database Conventions

Status: Active RFC 2119 keywords (MUST / SHOULD / MAY) are normative. References: ADR-0002 (schema-per-service), ADR-0003 (no cross-schema FK), ADR-0004 (migrator).

This document fixes how every backend service in paper-board/{agents, identity, billing, platform, runtime, gateway, compute} accesses Postgres: driver, query approach, transactions, migrations, and tests.

---

1. Driver — pgx/v5 locked

Rule

All Postgres access MUST go through github.com/jackc/pgx/v5/pgxpool. The database/sql standard package and the lib/pq driver MUST NOT appear in internal/store/ or any service code path.

Rationale

pgx/v5 is the only Go driver with native pgtype (jsonb, uuid, arrays, hstore, vector), batch, COPY, listen/notify, and a prepared-statement cache that survives PgBouncer transaction-pool mode. ADR-0004 already implies this; agents/internal/store/store.go:11 codifies it.

Connection-mode rule (cross-reference ADR-0004)

• MIGRATION_DB_URL MUST point to direct Postgres on port 5432 (session-pool capable). Required by advisory locks.
• DATABASE_URL MUST point to PgBouncer on port 6432 (transaction-pool mode). Server runtime uses this.
• The two URLs MUST NOT be swapped. The migrator binary MUST refuse DATABASE_URL-style URLs; the server MAY warn if MIGRATION_DB_URL-style URLs are passed (extra connections held).

Pool sizing

MaxConns and MinConns MUST come from Config (see Configuration §6 migration item). Defaults if env unset: MaxConns=10, MinConns=2, MaxConnLifetime=1h. These are tuned for single-tenant deployments; revisit when replica reads come online.

Enforcement

• golangci-lint depguard: database/sql, lib/pq, jmoiron/sqlx blocked in internal/store/.
• service-template uses pgxpool.NewWithConfig.

---

2. Query approach — sqlc for static, raw pgx for dynamic

Rule

Static SELECT/INSERT/UPDATE/DELETE statements (no run-time WHERE composition) MUST go through sqlc-generated functions in internal/store/queries/. Dynamic queries (cursor pagination with (sort_field, id) > ($1, $2), optional filter composition, JSONB path queries, full-text search, pgvector similarity) MAY remain hand-written using raw pgx calls in internal/store/.

sqlc layout

internal/store/
├── store.go             # pool ownership, MustConnect, Close, WithTx wiring
├── agents.go            # raw-pgx methods for the agents aggregate (dynamic queries only)
├── sessions.go
├── events.go
└── queries/             # sqlc-generated, regenerated by `make sqlc`
    ├── querier.go
    ├── agents.sql.go
    ├── sessions.sql.go
    └── events.sql.go

sqlc.yaml at repo root MUST target pgx/v5 driver and emit:

version: "2"
sql:
  - engine: postgresql
    queries: migrations/queries.sql
    schema: migrations/schema/
    gen:
      go:
        package: queries
        out: internal/store/queries
        sql_package: pgx/v5
        emit_pointers_for_null_types: true
        emit_interface: true              # Querier interface is needed by §4 WithTx

Query annotations

Static queries live in migrations/queries.sql with -- name: <Func> :one|:many|:exec markers:

-- name: GetAgent :one
SELECT id, org_id, name, slug, model, COALESCE(system_prompt, ''), created_at, updated_at
FROM agents.agents
WHERE id = $1 AND deleted_at IS NULL;

-- name: ListAgentsByOrg :many
SELECT id, org_id, name, slug, model, COALESCE(system_prompt, ''), created_at, updated_at
FROM agents.agents
WHERE org_id = $1 AND deleted_at IS NULL
ORDER BY created_at DESC;

When to escape to raw pgx

Stay raw when:

• The query has run-time-conditional WHERE clauses.
• It uses cursor pagination (sort_field, id) > ($1, $2) (sqlc generates clumsy code).
• It uses pgvector <-> operator or LISTEN/NOTIFY.
• It uses pgx.Batch or CopyFrom.

Escape hatch must be obvious — keep it in internal/store/<aggregate>.go, not in queries/.

Rejected alternatives

• All raw SQL (current agents state) — toil grows with every column rename; no compile-time safety. Forced to read every Scan(&a.X, ...) site on schema drift.
• squirrel builder — obscures static SQL; no compile-time check.
• GORM / ent / bun ORM — locks query plans behind framework AST; unacceptable for partitioned tables (e.g. future sessions / session_events partitioning) and pgvector similarity.
• sqlc for everything — fights cursor pagination and dynamic filters; force-fitting it costs more than the hand-written escape hatch saves.

Enforcement

• make sqlc regenerates; make sqlc-verify runs sqlc diff in CI and fails if stale.
• service-template ships sqlc.yaml + a one-query example.
• golangci-lint depguard: Masterminds/squirrel blocked.

---

3. Repository pattern — single Store, file-per-aggregate

Rule

Each service MUST have one Store struct in internal/store/store.go owning the pool. Methods MUST be split across files by aggregate (agents.go, sessions.go, events.go), not by concern. The struct stays single.

If any single file exceeds ~400 lines, split into per-aggregate sub-packages (internal/store/agentrepo/, internal/store/sessionrepo/).

Rationale

Sub-packages eagerly would force per-aggregate interfaces and mock fan-out the service doesn’t need. File-per-aggregate gets ~80% of the navigability win without the tax. The later split into sub-packages is file-rename-trivial; reverse direction (collapsing sub-packages back) would be expensive.

Enforcement

• service-template ships the file split.
• Code review: a new aggregate (table) MUST get its own file in internal/store/.
• File-size threshold: wc -l internal/store/*.go | awk '$1>400' in CI as a warning, not a fail.

---

4. Transactions — Querier interface + WithTx(ctx, pool, fn) helper

Rule

Store methods MUST take a Querier argument as their first parameter after ctx. Querier is the sqlc-generated interface satisfied by both *pgxpool.Pool and pgx.Tx. Transactions are opened by sdk/store.WithTx(ctx, pool, fn); the function passes the transaction-bound Querier into store methods.

Sketch

internal/store/agents.go

func (s *Store) CreateAgent(
    ctx context.Context,
    q queries.Querier,
    orgID uuid.UUID, name, slug, model, systemPrompt string,
) (*core.Agent, error) {
    a, err := q.CreateAgent(ctx, queries.CreateAgentParams{...})
    if err != nil {
        return nil, fmt.Errorf("create agent: %w", err)
    }
    return toCore(a), nil
}

// caller — non-tx path
agent, err := store.CreateAgent(ctx, store.Pool(), orgID, ...)

// caller — tx path
err := sdkstore.WithTx(ctx, store.Pool(), func(q queries.Querier) error {
    if _, err := store.CreateAgent(ctx, q, orgID, ...); err != nil { return err }
    if _, err := store.AppendSessionEvent(ctx, q, ...); err != nil { return err }
    return nil
})

sdk/store.WithTx contract

sdk/store/withtx.go

package store

func WithTx(ctx context.Context, pool *pgxpool.Pool, fn func(q Querier) error) error
// - Opens tx with default isolation (read committed).
// - Calls fn(tx).
// - Commits on nil; rolls back on error.
// - Wraps fn's error with %w; tx-open / commit errors prefixed.
// - DOES NOT carry tx in ctx — the Querier is the only handle.

Rejected alternatives

• Context-carried tx — invisible to readers; risk of leaking tx scope outside helper boundary; source of “I forgot the tx scope” prod incidents in many codebases.
• Per-method dual-flavour (CreateAgent / CreateAgentTx) — doubles surface area without buying anything sqlc + Querier doesn’t already give us.

Enforcement

• golangci-lint custom analyzer: pool.Begin / pool.BeginTx MUST NOT appear outside sdk/store/withtx.go.
• Code review: new store method without q Querier parameter is rejected.

---

5. Migration format

Filename rule (locked from CLAUDE.md + ADR-0004)

NNNNNN_<snake_case>.up.sql + matching NNNNNN_<snake_case>.down.sql. 6-digit zero-padded sequence. Confirmed precedent: agents/migrations/schema/000001_minimal.up.sql.

Body rules

Every transactional migration file (in migrations/schema/) MUST:

• Wrap all statements in BEGIN; ... COMMIT;.
• Issue SET search_path TO <service>, public; after CREATE SCHEMA IF NOT EXISTS <service>;.
• Use gen_random_uuid() for default id columns; require pgcrypto extension in public.
• Use timestamptz (NOT timestamp) for time columns; default NOW() for created_at/updated_at.

Every concurrent migration file (in migrations/concurrent/) MUST:

• NOT use BEGIN/COMMIT (incompatible with CREATE INDEX CONCURRENTLY).
• Run as a separate sdk/migrator pass with --concurrent.

Soft delete

Tables that participate in user-facing soft delete MUST add:

deleted_at  timestamptz                                                 -- nullable

and a partial index:

CREATE INDEX <table>_<col>_active ON <schema>.<table> (<col>) WHERE deleted_at IS NULL;

All read paths in internal/store/ MUST filter WHERE deleted_at IS NULL unless the caller is an admin/audit endpoint.

Enum-like columns

Use text + CHECK (col IN ('a', 'b', 'c')), NOT CREATE TYPE ... AS ENUM. Postgres ENUM types require ALTER TYPE migrations that lock the type and have ordering constraints; check constraints are easier to evolve. Precedent: agents.sessions.status and agents.session_events.event_type.

Cross-schema references

ADR-0003: NO cross-schema FK. org_id columns store uuid without REFERENCES; application code validates against identity.organizations via gRPC, with a DefaultOrgID fallback for services deployed before identity is online.

Cluster-wide extensions

pgcrypto, citext, vector (pgvector for memory tables) MUST be created in the public schema, not per-service:

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;

Enforcement

• sdk/migrator validates filename pattern at startup; refuses non-conforming files.
• service-template ships an exemplar migration matching all rules.

---

6. Migrations subdirectory layout

Locked from go-service-layout §1:

migrations/
├── embed.go            // go:embed schema/*.sql
├── schema/             # transactional migrations
├── concurrent/         # non-tx migrations (CREATE INDEX CONCURRENTLY)
└── .future/            # gitignored draft area

embed.go MUST embed both schema/*.sql and concurrent/*.sql into separate embed.FS variables.

---

7. Test database strategy — testcontainers per package + TRUNCATE per test

Rule

Integration tests MUST be gated by build tag //go:build integration (locked in go-service-layout §7).

For each internal/store integration test package:

• TestMain boots one Postgres testcontainer via sdk/testfixture.MustStartPostgres(m). Connection pool is package-global.
• Each test calls sdk/testfixture.Truncate(t, pool, "<service>") at the top to wipe rows.
• Tests MAY use t.Parallel() only when they are read-only (no INSERT/UPDATE/DELETE).

sdk/testfixture v1 API

package testfixture

// MustStartPostgres boots a postgres container, applies the service's
// embedded migrations, returns a pool. Cleans up on m.Run() exit.
func MustStartPostgres(m *testing.M, embedFS embed.FS, schema string, advisoryLock int) *pgxpool.Pool

// Truncate wipes all tables in the schema with RESTART IDENTITY CASCADE.
func Truncate(t *testing.T, pool *pgxpool.Pool, schema string)

// LoadFixtures loads SQL/YAML fixtures from testdata/ relative to the caller.
// Fixture names match files: testdata/<name>.sql or testdata/<name>.yaml.
func LoadFixtures(t *testing.T, pool *pgxpool.Pool, fixtureName string)

Rejected alternatives

• testcontainers per package with no shared container — ~10s × N packages. Pure waste.
• Per-test schema with CREATE SCHEMA test_<rand> — fights schema-pinned SQL we already write (agents.agents is hardcoded in queries); each test re-runs full migrations.
• Mocked DB / sqlmock — every prod migration regression we’ve seen passes mocks. CLAUDE.md general principle: don’t mock what you can integration-test cheaply.

Enforcement

• service-template ships internal/store/store_integration_test.go as exemplar.
• Makefile targets make test (unit, no DB) and make test-integration (with build tag).
• CI runs both.

---

8. Domain types + naming layers

Cross-reference

Agent, Session, SessionEvent types currently live in internal/store/store.go:43-50. go-service-layout §2 mandates they move to internal/core/. The store layer SHOULD return core.Agent, mapping from sqlc-generated queries.Agent row types via per-aggregate toCore / fromCore helpers.

internal/core/agent.go

type Agent struct {
    ID           uuid.UUID `json:"id"`
    OrgID        uuid.UUID `json:"orgId"`        // wire: camelCase (http-api §7)
    Name         string    `json:"name"`
    Slug         string    `json:"slug"`
    Model        string    `json:"model"`
    SystemPrompt string    `json:"systemPrompt,omitempty"`
    CreatedAt    time.Time `json:"createdAt"`
    UpdatedAt    time.Time `json:"updatedAt"`
}

// internal/store/agents.go
func toCoreAgent(row queries.Agent) *core.Agent {
    return &core.Agent{
        ID: row.ID, OrgID: row.OrgID, Name: row.Name, ...
    }
}

This keeps core/ independent of the SQL layer (sqlc structs never leak past the store boundary).

Three naming layers — pinned

A single domain field traverses three identifier conventions on its way to and from storage:

Layer	Identifier example	Convention	Owned by
SQL column	org_id	snake_case	migration files (this doc §5)
Go struct field	OrgID	PascalCase	internal/core (Go style)
Wire JSON	orgId	camelCase	http-api-conventions §7
Log attribute	org_id	snake_case	observability §1

The naming convention MUST switch only at these boundaries (sqlc tag → Go field, Go field → JSON tag). Mixing — e.g. emitting orgId in SQL or org_id in wire JSON — is forbidden.

sqlc and acronyms

sqlc generates Go field names from SQL columns using Go’s exported convention. org_id becomes OrgID (sqlc respects common initialisms when configured):

# sqlc.yaml fragment
sql:
  - gen:
      go:
        rename:
          id: ID
          uuid: UUID
          org_id: OrgID
          api_key_id: APIKeyID

internal/store/queries/<table>.sql.go rows are internal — their JSON tags (if any) MUST NOT leak through core.* mapping helpers.

Enforcement

• Code review checklist line: “JSON tags camelCase? SQL snake_case? Domain Go fields PascalCase?”
• golangci-lint custom rule flags non-camelCase json:"..." tags outside internal/store/queries/.

---

8b. Identifier generation — UUIDv4 / UUIDv7

Rule (Status: Pending — sdk/ids not yet shipped)

The id version MUST follow the entity that owns it, not the table that stores it. Reference columns inherit the source’s version.

Scenario	Rule
New table for a new entity	UUIDv7 (app-side ids.NewV7())
New column storing id of a new v7 entity	UUIDv7
New column storing id of an existing v4 entity (UUID-by-reference)	UUIDv4 (matches source)
New PK column on an existing v4 table (rare)	UUIDv4 (sibling consistency)

Existing tables (agents.agents, agents.sessions, agents.session_events) keep uuid PRIMARY KEY DEFAULT gen_random_uuid() (UUIDv4). Migration of existing v4 ids to v7 is not planned — cosmetic; cost > benefit.

Generation pattern

sdk/ids/v7.go

func NewV7() uuid.UUID { return uuid.Must(uuid.NewV7()) }

The new-table SQL pattern:

id  uuid PRIMARY KEY  -- app-set; no DB-side default

Application code calls ids.NewV7() and passes it as the first INSERT parameter.

Why app-side generation

App-side generation is the canonical pattern regardless of DB capability. It keeps the codebase portable across DB versions, lets sqlc see the column as required, makes ids visible in logs before the INSERT lands, and decouples sdk/ids from any specific Postgres version’s uuidv7() support.

Enforcement

• Code review: new tables MUST use Go-side UUIDv7 generation; new columns of type uuid storing ids of new entities MUST default NULL and be set by the application.
• Reference columns: code review verifies the column’s value matches the source entity’s id version.

---

9. Summary

§	Rule	Enforcement
§1	pgx/v5 only	depguard on database/sql, lib/pq
§2	sqlc for static + raw pgx for dynamic	sqlc-verify CI step + service-template
§3	Single Store struct, file-per-aggregate	service-template + review
§4	Querier interface; sdk/store.WithTx for transactions	sdk dep; custom analyzer
§5	NNNNNN_name.up.sql + body rules (BEGIN/COMMIT, search_path, gen_random_uuid)	sdk/migrator validation
§6	migrations/{schema, concurrent, .future}	service-template
§7	testcontainers per package + TRUNCATE per test	sdk/testfixture + service-template
§8	Domain types in internal/core/; toCore mapping in store	go-arch-lint + review