Database and migrations

Read this when you write a migration, change a SQL query or open a transaction.

Migrations

Migrations live in services/tadoku-api/migrations/ as numbered pairs, NNNN_<description>.up.sql and NNNN_<description>.down.sql, using the next free number. sqlc reads the same directory as its schema.

• Ship every schema or data migration as a standalone change. It lands on main in its own commit and pull request, separate from application code, and is deployed independently before any code that depends on it.
• Never combine a migration and runtime behavior in the same commit, pull request or deployment, including within a stacked PR series.
• A migration pull request contains no changes outside services/tadoku-api/migrations/. CI enforces this; the migration-move label permits mechanical relocations and logs a warning.
• A migration must stay compatible with the application version currently deployed. Additive migrations come before the code that depends on them. Destructive or cleanup migrations come later, in their own standalone pull request, once the old code is no longer deployed.
• After a migration is merged and deployed, base dependent work on the updated main.

CI checks changed .up.sql files for known migration risk patterns, and the Migration safety check fails when one matches. It also rejects SQL that PostgreSQL's parser cannot read. The checker flags:

• regular index builds;
• create index concurrently in a file with other statements. The migration runner sends each file as one query, which PostgreSQL runs in an implicit transaction when it has several statements, so put each concurrent index in its own single-statement migration;
• added columns with an expression default;
• added not null or primary key columns without a default, identity, generated expression or serial type, whatever the table size, because application versions that omit the column fail their inserts;
• column type changes;
• immediately validated foreign keys and direct unique constraint builds, including inline constraints on added columns;
• SET NOT NULL, column drops and renames;
• TRUNCATE, VACUUM FULL and CLUSTER.

The checker does not query a database or judge table size. Change the migration to clear a finding. When a finding is intended and safe, for example a column drop that no deployed code reads or writes, a required reviewer of the migration-safety-override environment can approve the run's Approve migration safety findings job; Migration safety then passes for that run only. The approval covers any checker failure on that run, including SQL the parser rejects, so read the run's findings before approving. A new push needs a new approval. Run the checker locally with bazel run //tools/ci/migrationsafety/cmd -- services/tadoku-api/migrations/NNNN_name.up.sql. The checker was inspired by Safe / Not Safe; Tadoku's Go rules are independently implemented with pg_query_go.

Schema design

• Do not create application-defined database functions, stored procedures or triggers. Keep business behavior in the application and domain layers.

• A trigger needs the maintainer's explicit approval, recorded in its pull request. It must meet all of these conditions:

  • It maintains derived data that every writer of its source tables must keep consistent, including writers outside the application, such as operator SQL and cascades.
  • It cannot fail a valid write, including a cascade from deleting a test tenant.
  • A test fails when a table the derived data depends on has no trigger.
  • Integration tests pin what each kind of write changes.

  Write trigger functions in PL/pgSQL. A trigger function that must read or write rows beyond its caller's grants is security definer, with search_path pinned the same way as the privilege-boundary functions below.

• Use declarative database features for data integrity and performance: not null, check, unique and foreign-key constraints, and indexes.

• The existing data.create_contest_round function, called by the scheduled contest jobs described in services/tadoku-api/migrations/README.md, predates this rule.

• copy_production_platform_scoring(text) and copy_production_published_pages(text) are the privilege-boundary exceptions. Each is security definer, pins search_path to the migration schema and pg_temp, has execute revoked from PUBLIC and refuses any target that is not a registered test tenant. The first copies tadoku/prod's active platform scoring rule set into a tenant without platform scoring config. The second copies tadoku/prod's published, undeleted pages with only their current content revision into a tenant without pages. The lifecycle role therefore needs no scoring or page table grants.

SQL style

Always write SQL keywords in lowercase: select and create table, not SELECT and CREATE TABLE.

Layout applies to migrations, sqlc queries, seed scripts and SQL embedded in Go:

• A statement that does not fit on one line puts each clause (select, from, where, order by and so on) on its own line. A where with several conditions puts one condition per line, with and or or leading.
• Write case expressions over several lines: case, each when ... then, else and end on their own lines.
• Separate items in column and value lists with , .
• An insert with several rows puts one row per line.

Paginated query enrichment

When optimizing a paginated query that shares an eligible-row CTE with its total count, consider aggregating presentation-only values after pagination when those values do not affect filtering or ordering. Preserve the count-only row emitted for an empty page, including its null result columns, and preserve empty arrays for returned rows without related values.

Optional filters can produce different custom and generic plans. Measure filtered, unfiltered and empty cases with production planner settings and the prepared statement's sixth execution. Retain aggregation before pagination on branches where deferring it increases work. Preserve parameter order and sqlc result types when changing query structure.

Bounded reference lookups

A single-row lookup can still scan entire reference tables when the planner chooses hash joins. For an equality lookup on an enforced unique reference key, a lateral subquery with limit 1 can bound that work without changing which row is eligible. Keep required references as inner joins and optional references as left joins, preserving null handling and result types.

Verify uniqueness in the schema before using this pattern. Compare existing, missing, deleted and cross-tenant rows under the runtime role, and measure prepared plans at both representative and larger data sizes. Retain the change only when the measured latency and whole-query work checks pass.

Aggregate before reference enrichment

When reference columns do not filter aggregate inputs, aggregate on the reference key before looking up its presentation columns. Verify the key is unique and retain the original inner or outer join semantics, aggregate casts and final ordering. A bounded lateral lookup can avoid scanning the entire reference table for a small set of groups; retain it only after work and latency checks at both data scales, including empty and cross-tenant cases.

Tenancy

The canonical tenant is tadoku/prod, in both production and development. Test tenants use parsed keys such as e2e/isolation-0123abcd and registry kind test. A key has exactly two slash-separated components; each starts with a lowercase letter or digit and contains at most 56 lowercase letters, digits or hyphens. Physical database names and database roles are independent of keys.

Every application transaction or standalone statement carries its tenant through the PostgreSQL executor. Every tenant-owned table has a non-null tenant column with the strict current_setting('tadoku.tenant') default. Ordinary tenant_isolation policies permit only that tenant's rows. The jobs policy also admits the restricted all-tenants worker scope; jobs still require a non-null, registered tenant.

languages is a shared reference table. Everyone can read it, but only tadoku/prod can insert, update or delete its rows. The tenants and tenant_overrides registry policies permit test-tenant management while protecting the canonical production entry. Deleting a test entry cascades to its tenant-owned rows, including replayed jobs.

For each new tenant-owned table:

• Add a non-null tenant column with the transaction tenant default and a foreign key to tenants(key) with on delete cascade.
• Enable row-level security and the canonical tenant_isolation policy for both reads and writes.
• Prefix primary and business-unique keys with tenant, and include tenant in foreign keys that need to prevent references across tenants.
• Follow the migration safety rules, including not valid foreign keys and explicit validation, and run the schema guard.

The owner role applies migrations and owns relations. It bypasses their row-level policies and is not the application credential. The runtime role has no superuser, bypassrls or owner membership, owns no relations, and has application DML and sequence privileges. It can read schema_migrations but cannot change it. Tests preserve this distinction with separate fixture and application pools. The tadoku_tenant_lifecycle role manages test tenants with only select, insert and delete on tenants and tenant_overrides, plus execute on copy_production_platform_scoring(text) and copy_production_published_pages(text). Migrations grant nothing to it; the production infrastructure hook does. See the tenant lifecycle command.

Data migrations and operator SQL must set the intended tenant explicitly inside their transaction:

begin;
select set_config('tadoku.tenant', 'tadoku/prod', true);
-- The intended scoped statements go here.
commit;

Never set the tenant per session, in pool startup parameters, on a role or on a database. Transaction pooling can give another client the same server session. A genuinely fresh direct connection without the setting fails with 42704; a previously scoped connection can retain an empty custom-GUC placeholder. That connection reads no ordinary tenant rows and cannot persist an unscoped write. Application calls also fail before SQL when their context has no parsed tenant.

Every pg_cron command for tenant-owned work sends the local tadoku/prod setting first and its scheduled work in the same command batch. The multiple statements execute in one job transaction. Keep this prefix with the complete command; connection/session defaults cannot replace it. Operator tools that submit statements separately must use an explicit transaction.

sqlc code generation

Queries live in one package per feature under services/tadoku-api/sql/<feature>/: the feature's .sql query file, a sqlc.yaml that writes Go code to services/tadoku-api/generated/sqlc/<feature>/, and a generate.go that pins the sqlc version. Add a new package to SQLC_PACKAGES in scripts/generate-sqlc.sh.

Always regenerate after changing a SQL query. The checked-in generated Go files must exactly match the query sources. Run the generator from the repository root:

./scripts/generate-sqlc.sh

• The script downloads and runs the sqlc version pinned in each active package's generate.go (v1.31.1, generating pgx/v5 code), so go does not need to be installed or on PATH.
• Queries must return explicit result columns rather than whole table rows. sqlc reuses a table model for select *, table.*, returning *, and an equivalent complete list in table order. Adding a column then changes the generated code, which a standalone migration PR cannot contain. Select only the fields needed, or reorder the complete list so sqlc emits a query row type. Keep equivalent results in the same order for shared row mapping.
• Never edit sqlc-generated files by hand. Never delete, revert or selectively omit changes produced by the generator; commit the complete generated diff, even when it reveals previously stale output.
• If the generated changes are unexpected, investigate the query inputs and the pinned sqlc version, then rerun the generator. Do not discard the output.
• CI runs the same script on every pull request and fails if it changes the working tree.

Transactions

postgres.RunInTransaction(ctx, pool, callback) in services/tadoku-api/infra/postgres/ owns begin, commit and rollback. The callback receives a context carrying the transaction. Repositories select the database handle with postgres.Executor(ctx, pool) for each operation and pass it directly to the sqlc queries:

db, err := postgres.Executor(ctx, r.pool)
if err != nil {
    return err
}
return queries.New(db).InsertItem(ctx, params)

• The application owns transactions. Open one only when the operation needs one.
• Every operation needs a parsed tenant on its context. Missing or zero tenants return postgres.ErrNoTenant before SQL. RunInTransaction sets the tenant locally as its first statement; the callback retains it for the transaction.
• Outside a transaction, Executor returns a scoped handle that sends the local tenant setting and actual statement as one pgx batch in an implicit transaction. Inside one, it returns the active transaction. Pass the same operation context to every SQL call.
• Changing the tenant within a transaction or on a standalone handle returns postgres.ErrTenantMismatch. A context containing both a tenant and the restricted all-tenants marker also fails closed.
• Never set tadoku.tenant for a whole session. PgBouncer transaction pooling hands server sessions to other clients; only transaction-local settings end before the next client uses the session.
• Query rows retain their batch connection until Close or exhaustion. Finish iteration, close rows and check Err; it includes batch-close errors. QueryRow releases its batch after Scan and preserves pgx.ErrNoRows.
• Wrong-pool, nested and ended transaction scopes fail. An ended context never falls back to the pool. There are no retries or savepoints.
• Finish all work and row iteration before the callback returns. Do not run parallel SQL on one transaction or hold it across network or cache operations.
• Callback errors and panics keep their identity. Rollback gets an independent five-second timeout, so cancellation does not prevent the attempt.
• A commit transport error can leave the persistence outcome uncertain.

The helper's own test setup is described in services/tadoku-api/infra/postgres/README.md.

PostgreSQL errors

services/tadoku-api/infra/postgres/ classifies PostgreSQL and pgx errors by cause before they leave RunInTransaction or a standalone Executor statement. It marks only identified outages and capacity exhaustion as errx.Unavailable; every other error keeps no kind unless a repository operation translates one it expects. Classification uses SQLSTATEs and pgx error types, never message text. services/tadoku-api/transport/http/errors.go then maps the kind:

Error	Response
pgx.ErrNoRows checked by the operation	The operation's own error, usually not found
Unique violation checked by the operation with postgres.IsUniqueViolation	The operation's already-exists error, 409
A pgconn.ConnectError caused by a network error, such as an unreachable server	503, logged at Error level
SQLSTATE class 08 except 08P01, class 53 except 53400, and 57P01, 57P02 and 57P03	503, logged at Error level
Any other PostgreSQL or pgx error, including constraint, syntax, permission, data-conversion and transaction-state errors, class 40 rollbacks, 57014, a closed pool and a connection lost after a statement or COMMIT was sent	500, logged at Error level

• Check a SQLSTATE in a repository only where the operation gives that constraint a meaning. Other constraint violations are not client conflicts.
• Errors wrapping context.DeadlineExceeded return 504, and errors wrapping context.Canceled after the request was canceled return 499, whatever their source. Context errors are never classified as unavailable.
• A PostgreSQL error reported for COMMIT, such as a deferred constraint failure or 57P01, means the transaction was aborted. A transport failure after COMMIT was sent leaves the outcome unknown and stays 500.
• 503 does not make a replay safe. Failed transactions are not retried or replayed, and the classification wraps the original cause, so it remains available to errors.Is and errors.As.