Move the store to sqlc

Overview

Replace the hand-written pgx queries in internal/store/pg with code that sqlc generates from SQL files and the goose migrations. sqlc becomes the default for every query; hand-written pgx stays allowed only for a query sqlc cannot express, with a comment saying why. Nothing above the store changes: store.Store, store.Scoped and every sub-store interface keep their signatures, and the phase 2 tests carry over unchanged as the proof that behaviour did not move. This phase reverses the phase 2 decision “hand-written SQL, no sqlc” on purpose, to learn sqlc in a real codebase while the store is still small (five query files) and before phase 4 copies its pattern. GitHub issue #26.

Requirements

Functional

• Every query in internal/store/pg runs through sqlc-generated code, or is a documented escape hatch. The phase 2 queries are all expressible, so this phase is expected to ship with none.
• Optional predicates use sqlc.narg: the member-only filter on users, api_keys and audit_events, and the since, action and target_id filters on audit events. The statement text is fixed; only parameters vary.
• The tenant rule from phase 2 is unchanged and now lives in SQL files: every statement on a tenant-owned table takes tenant_id as a required parameter, bound only from the scope inside scoped. No generated method is called with a tenant id from anywhere else.
• ResolveKey stays the single tenant-less query on a tenant-owned table, still reachable only through store.AuthLookup.
• audit_events stays append-only: the audit query file and its generated Go have an INSERT and a SELECT and nothing else.

Non-functional

• The generated code lives in package internal/store/pg itself (sqlc out: internal/store/pg, package: pg). A separate generated package would be a twenty-first package and break the package cap in plan.md.
• sqlc is pinned in the Makefile and run with go run github.com/sqlc-dev/sqlc/cmd/sqlc@<version>. It is not added to go.mod, so its dependency tree never reaches the module graph or the binary.
• Types: sql_package: pgx/v5, emit_pointers_for_null_types: true, emit_db_tags: true, overrides mapping uuid to the Go 1.27 stdlib uuid.UUID (*uuid.UUID when nullable), timestamptz to time.Time (*time.Time when nullable), jsonb to json.RawMessage, and column overrides users.role to store.Role and tenants.kind to store.TenantKind. No pgtype type appears in a generated struct.
• make generate regenerates; make sqlc-check runs sqlc diff and sqlc vet and fails when the committed code is stale. There is no CI yet; phase 16’s CI job runs make sqlc-check.
• The roadmap tooling accepts a decimal phase number: build_plan_html.go and plan-template.html treat 2.1 as a phase, the build order lists it, and the Hugo site shows it between phases 2 and 3.

Architecture

migrations/*.sql ──(schema)──┐
                             ├── sqlc generate ──> internal/store/pg/{db,models,*.sql}.go
internal/store/pg/queries/*.sql ┘                      (package pg: Queries, DBTX, row structs)

store.For(ctx) ──> scoped{q, scope} ──> New(s.q).ListUsers(ctx, ListUsersParams{TenantID: s.scope.TenantID, ...})
                                                       │
                                        querier (pool or tx) — unchanged

-- internal/store/pg/queries/users.sql (shape, not final text)
-- name: ListUsers :many
select id, tenant_id, email, role, status, created_at
  from users
 where tenant_id = @tenant_id
   and (sqlc.narg('only_user')::uuid is null or id = sqlc.narg('only_user'))
 order by created_at;

// internal/store/pg/user.go (shape)
func (us userStore) List(ctx context.Context) ([]store.User, error) {
	rows, err := New(us.s.q).ListUsers(ctx, ListUsersParams{
		TenantID: us.s.scope.TenantID,
		OnlyUser: us.s.ownUserID(), // nil for owner and admin
	})
	return mapRows(rows, toUser), mapErr(err)
}

• querier keeps Begin, so InTx is unchanged; it satisfies the generated DBTX, so New(s.q) works on the pool and inside a transaction alike.
• Each hand-written file keeps its sub-store type and becomes a thin mapper: build params from the scope, call the generated method, convert the generated row struct to the store type, map errors with mapErr.
• ownRowsOnly() becomes ownUserID() *uuid.UUID, returning the scope’s user id for a member and nil otherwise, which is exactly what the sqlc.narg predicate needs.

Instrumentation

No new spans or metrics. sqlc puts -- name: ListUsers :many at the head of each statement it sends, so the db.query.text attribute on otelpgx spans now starts with the query name (the span name stays the SQL verb), which makes a slow query in Tempo searchable by name. The learning doc shows this.

Related Code Files

Create

• sqlc.yaml — engine, schema (migrations), queries, output package and the type overrides.
• internal/store/pg/queries/tenants.sql, internal/store/pg/queries/users.sql, internal/store/pg/queries/api_keys.sql, internal/store/pg/queries/audit.sql, internal/store/pg/queries/auth.sql.
• internal/store/pg/db.go, internal/store/pg/models.go, and one *.sql.go per query file — generated, committed, never edited by hand.
• docs/learning/02.1-sqlc-store.md.

Modify

• internal/store/pg/tenant.go, internal/store/pg/user.go, internal/store/pg/apikey.go, internal/store/pg/audit.go, internal/store/pg/auth.go, internal/store/pg/scoped.go — replace SQL literals and scans with generated calls and mappers.
• internal/store/pg/audit_test.go — the append-only check reads queries/audit.sql and audit.sql.go instead of audit.go.
• Makefile — generate and sqlc-check targets, sqlc version pin.
• .golangci.yml — exclude generated files if a linter flags them.
• docs/tech-stack.md, docs/roadmap.md, docs/system-architecture.md, docs/learning/README.md, README.md — record sqlc and the reason; list the new lesson.
• plans/260922-0123-hango-agent-harness-roadmap/build_plan_html.go, plans/260922-0123-hango-agent-harness-roadmap/plan-template.html — accept decimal phase numbers.
• plans/260922-0123-hango-agent-harness-roadmap/phase-16-hardening-rls-ci-release.md — CI runs make sqlc-check.

Implementation Steps

1. Pin sqlc in the Makefile (SQLC := go run github.com/sqlc-dev/sqlc/cmd/[email protected], latest release) and add generate and sqlc-check. Confirm it builds on this machine (it compiles pg_query with cgo the first time).
2. Write sqlc.yaml with the options and overrides above. Run sqlc compile against the goose migration to confirm it reads the Up section and ignores Down.
3. Write queries/tenants.sql: create, get, get by slug, list, platform, user tenant, lock (for no key update). Generate, then rewrite tenant.go as mappers. Run the tenant tests.
4. Write queries/users.sql and queries/api_keys.sql, including the insert ... select ... from users guard on key creation, the owner-key count join, and revoke with the optional member predicate. Rewrite user.go, apikey.go and scoped.go (ownUserID). Run the tenancy tests.
5. Write queries/audit.sql with append and a list that uses sqlc.narg for the member, since, action and target_id filters and a bound limit. Rewrite audit.go and point the append-only test at the query file and generated file.
6. Write queries/auth.sql for ResolveKey and rewrite auth.go.
7. Delete the now-unused helpers (scanTenant, collect, column constants) only when nothing references them. If a query truly cannot be expressed, keep it hand-written with a comment naming the sqlc limit; record it in the learning doc.
8. Run make generate twice and confirm no diff; run make sqlc-check; run go build ./... && go vet ./... && go test -race ./... && go test -short ./... && golangci-lint run.
9. Teach build_plan_html.go decimal phase numbers (parse \d+(\.\d+)?, store as float64, print with %g) and check the template’s phase lookups still match. Add phase 2.1 to plan.md and regenerate plan.html. Build the Hugo site and confirm phase 2.1 and its lesson sit between phases 2 and 3.
10. Update the docs listed above, write the learning doc, and add make sqlc-check to phase 16’s CI step.

Learning Doc Outline

docs/learning/02.1-sqlc-store.md must contain:

• Concept: what sqlc reads (schema from migrations, queries from .sql files), what it generates, and what it checks at generate time that hand-written SQL only finds in tests.
• Design choices: sqlc as the default with a narrow escape hatch instead of a split hybrid; generated code in the pg package to respect the package cap; type overrides instead of pgtype; go run with a pinned version instead of a go.mod tool dependency.
• Where tenancy lives now: tenant_id is a required parameter in SQL, the scoped handle is still the only caller, and the cross-tenant tests did not change.
• Limits met in practice: optional filters with sqlc.narg, why sqlc.narg cannot tell “unset” from “set to NULL”, one row struct per query shape, type inference that needs a ::type cast, and what an escape hatch would look like.
• How to debug this layer: a stale generated file is caught by make sqlc-check; a slow query is found in Tempo by the -- name: comment at the start of db.query.text; a type mismatch shows up at sqlc generate, not at run time.
• goclaw comparison: database/sql plus sqlx with hand-written SQL, chosen because goclaw also supports SQLite; hango is Postgres-only, so it can use native pgx and sqlc.
• Exercises: add a column to users and follow the compile errors; write the audit list with a runtime sort column and see why it needs an escape hatch.
• Time budget: 6h implementation + 1h learning doc.

Success Criteria

• make generate produces no diff on a clean tree, and make sqlc-check passes.
• No SQL string literal remains in internal/store/pg outside queries/ and generated files, apart from documented escape hatches (expected: none).
• go build ./... && go vet ./... && go test -race ./... pass with Docker running, and go test -short ./... passes.
• The phase 2 tests in internal/store/pg, internal/server and internal/app pass with no edit other than the audit append-only check’s file paths.
• golangci-lint run reports 0 issues.
• go list -deps ./cmd/hango lists no sqlc or pg_query package, and go.mod does not require sqlc.
• No generated struct field has a pgtype type.
• plan.html regenerates with phase 2.1 in the phases table and build order, and the Hugo site lists phase 2.1 and its lesson between phases 2 and 3.
• docs/tech-stack.md records sqlc and the reason, and docs/learning/02.1-sqlc-store.md is written.

Evidence, 2026-10-15: make generate run twice leaves no diff and make sqlc-check passes. go build ./..., go vet ./... and go test -race -count=1 ./... pass with Docker; all 12 tests in internal/store/pg ran against Postgres 17 and passed. go test -short ./... passes and golangci-lint run reports 0 issues. go list -deps ./cmd/hango has no sqlc or pg_query package, go.mod has no sqlc, and rg pgtype internal/store/pg finds nothing. The only backtick literal left in the hand-written store files is the {} audit detail default. go run build_plan_html.go lists 21 phases, and the Hugo build orders the plan sidebar 1, 2, 2.1, 3.

Deviations, decided during implementation:

• Site weights became phase × 10 (phase 2 is 20, phase 2.1 is 21) for every phase file and learning doc. Hugo weights are whole numbers, and with phases 2 and 2.1 both at weight 2 Hugo broke the tie by title and put 2.1 first. docs/docs-site.md and plan.md state the new rule.
• Mappers are Go struct conversions (store.User(u)) instead of toUser functions. Renames trace_llm_content: TraceLLMContent and api_key_id: APIKeyID, plus aliasing key_hash as hash in ResolveAPIKey, make the generated structs field-identical to the store types. The audit event keeps a field-by-field mapper because NULL references become uuid.Nil().
• The append-only test checks every statement in queries/*.sql, comments stripped, and fails on one that names audit_events with UPDATE, DELETE or TRUNCATE. It also checks the raw literals in audit.go, where an escape hatch would go. It does not read the generated audit.sql.go, which is derived from the query files. Code review pointed out that a purge could be added to any query file, so the check covers all of them; a probe file with a delete from audit_events made it fail.
• make generate deletes internal/store/pg/*.sql.go before running sqlc, because sqlc never removes output and sqlc diff does not flag a leftover file from a renamed query file.
• .golangci.yml needed no change: the linters already skip files marked Code generated ... DO NOT EDIT.
• plan-template.html needed one regex change, so links to phase-02.1-*.md open the phase modal.
• Issue #26 differs from this spec in three places. Transactions keep phase 2’s querier, passed to New(s.q), instead of Queries.WithTx, because InTx already swaps a pool for a transaction and WithTx would add a second path. There is no identifier allowlist, because no query needed an escape hatch; the first one adds it. The CI check is deferred to phase 16, which has no CI before it, and its step now runs make sqlc-check.
• The query files have no file-level comments, because sqlc attaches the top comment of a file to the first query’s Go doc. Each note sits on the query it describes.
• make generate runs sqlc compile before deleting the old generated files, so a typo in a query leaves the committed code in place.
• Phase 4’s store steps were rewritten for sqlc: query files, the sqlc.narg member predicate, and an unnest insert for message batches.

Risk Assessment

Rollback

Revert the phase commit. The schema does not change, so no migration runs in either direction, and the phase 2 hand-written store returns exactly as it was.