ADR 0019 — pgx repositories with hand-reviewed SQL instead of sqlc codegen

Status: Accepted · Date: 2026-07-23

Context

The specified stack lists sqlc. sqlc generates Go from SQL at build time, which is excellent for stable CRUD but fights RLS-GUC transaction wrappers, dynamic filters (patient search, report facets), and the per-transaction tenancy injection this platform requires on every query. It also adds a codegen step to every schema iteration during the foundation build.

Decision

Data access uses pgx v5 through per-domain repository packages with:

• SQL kept as named constants adjacent to the repository (reviewable like sqlc files),
• one shared store.Tx helper that guarantees tenancy GUCs are set,
• integration tests executing every query against real PostgreSQL.

sqlc remains approved tooling; domains with stable query surfaces may migrate to sqlc-generated code later without API changes (a superseding ADR will track it).

Consequences

• Slightly more hand-written scanning code; mitigated with generic helpers.
• No build-time codegen dependency for the database layer.
• The database/queries/ directory documents canonical SQL per domain.