SQL Writing Skill

SQL appears in two contexts — DAO query files and schema migrations — each with its own lifecycle and risk profile. Read the section for the task at hand; the PostgreSQL conventions at the end apply to both.

Before writing any SQL, read the existing files in the same directory and follow their patterns exactly. For migrations, also read the most recent .up.sql and .down.sql to learn the current schema state before changing it.

After Every Edit

Run these in order after changing any SQL file:

pnpm format     # prettier — this is what formats SQL (via prettier-plugin-sql)
pnpm format:go  # only when the SQL change rippled into Go files
pnpm lint:go    # catches any Go-level issues introduced by the SQL change

pnpm format is the one that matters for SQL. The Go-only pnpm format:go does not touch .sql, so running just that after a SQL edit leaves indentation, comment, and whitespace drift in place. CI's lint-js job runs prettier --check and fails on the unformatted file even when Go lint is clean. Run pnpm format before pushing any .sql edit.

Then invoke the write-go-tests skill to verify or update the DAO test for the changed query. SQL changes often shift query results in ways existing fixtures surface.

DAO Query Files (internal/dao/*.sql)

DAO query files hold the raw SQL for a single database operation. They are embedded into the companion Go file with //go:embed and executed via bun's parameterized query API.

File Naming

DAO SQL files mirror the Go file they serve, with .sql replacing .go:

One SQL file per DAO operation. Never combine multiple queries in one file.

Embedding

The SQL file is embedded at package level in the companion .go file with an unexported variable:

//go:embed pg.jwkSearch.sql
var jwkSearchQuery string

The //go:embed directive and its variable must be at package level — never inside a function. Never inline DAO SQL as a raw Go string literal, and never build SQL with fmt.Sprintf. Short, parameterless maintenance SQL in cmd/ entry points (e.g. REFRESH MATERIALIZED VIEW) may stay an inline raw string, being a one-time operational command rather than a reusable query.

Parameterization

Use bun's positional parameter syntax: ?0, ?1, ?2, ... (zero-indexed). These map directly to the arguments passed to db.NewRaw(query, arg0, arg1, ...):

SELECT
  *
FROM
  active_keys
WHERE
  usage = ?0
ORDER BY
  created_at DESC
LIMIT
  ?1;

tx.NewRaw(jwkSearchQuery, request.Usage, request.Limit).Scan(ctx, &entities)

Never use PostgreSQL's native $1, $2, ... syntax in DAO files — that is the pgx driver convention and is not substituted by bun's NewRaw. Never build SQL by concatenating strings.

Return Patterns

• SELECT queries scan into a struct or slice. Use SELECT * when returning a full model row — bun maps columns to struct fields via bun: tags.

• INSERT, UPDATE, and DELETE queries that must return the affected row use RETURNING *:

  INSERT INTO
    keys (id, private_key, created_at)
  VALUES
    (?0, ?1, ?2)
  RETURNING
    *;

  UPDATE keys
  SET
    deleted_at = ?0,
    deleted_comment = ?1
  WHERE
    id = ?2
    AND deleted_at IS NULL
  RETURNING
    *;

  RETURNING * gives the caller the full row (including server-generated timestamps) in a single round-trip. The Go caller passes the result directly to Scan(ctx, entity). A write whose operation returns less names its columns instead: the cell upsert ends with RETURNING scene_id, layer_id, updated_at, since the client sent the content (see write-go-service's operations).

Bounded reads

Every query that can return more than one row ends with an explicit LIMIT. An unbounded query loads the whole set into the service's memory. No read assumes one page holds a whole set.

A list returned to a client pages with limit and offset, in the set's natural order, such as position. The same query returns the total as count(*) OVER () AS total on each row. Window functions run before LIMIT and OFFSET, so the total counts every match, and the client pages from that one query. The core caps the limit. An empty page has no row to carry the total, so it reports a total of 0.

SELECT
  *,
  count(*) OVER () AS total
FROM
  scenes
WHERE
  project_id = ?0
ORDER BY
  position
LIMIT
  ?1
OFFSET
  ?2;

WHERE runs before window functions, though, so a count over rows the query then filters out sits in a subquery. PgLayerKind counts each kind's layers across the project, then keeps the layers a delete names. Its LIMIT is the number of IDs, the most rows it can match:

SELECT
  *
FROM
  (
    SELECT
      id,
      kind,
      count(*) OVER (
        PARTITION BY
          kind
      )::integer AS count
    FROM
      layers
    WHERE
      project_id = ?0
  ) AS kinds
WHERE
  id = ANY (ARRAY[?1]::uuid[])
LIMIT
  ?2;

A schema cap bounds what a parent may hold. A check holds the position column to a range, and a unique key on the parent and the position allows one row per position. The cap does not make the set small enough to read at once, so its lists still page.

-- Migration: the range and the unique key cap a project at 500 scenes.
CREATE TABLE scenes (
  id uuid PRIMARY KEY NOT NULL DEFAULT uuidv7(),
  project_id uuid NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
  position integer NOT NULL CONSTRAINT scenes_position_check CHECK (
    position >= 0
    AND position < 500
  ),
  -- A shift moves positions in one statement, so uniqueness is checked at commit.
  CONSTRAINT scenes_project_id_position_key UNIQUE (project_id, position)
  DEFERRABLE INITIALLY DEFERRED
);

A write that renumbers positions shifts a range in SQL instead of reading every row. The client sends the target position, and the statement that shifts clamps it, so no count is read before the write. LEAST(?1, count(*)) turns a position past the end into an append. This insert shifts the scenes from there on and fills the gap in one statement, which the deferred unique key allows until commit:

WITH
  start AS (
    SELECT
      LEAST(?1, count(*))::integer AS position
    FROM
      scenes
    WHERE
      project_id = ?0
  ),
  shifted AS (
    UPDATE scenes
    SET
      position = scenes.position + 1
    FROM
      start
    WHERE
      scenes.project_id = ?0
      AND scenes.position >= start.position
  )
INSERT INTO
  scenes (id, project_id, position, title)
SELECT
  ?2,
  ?0,
  start.position,
  ?3
FROM
  start
RETURNING
  *;

A move clamps its target to count(*) - 1, the last place, and shifts the rows between the old and the new position by one toward the old one.

A write past the range breaks the check. Its DAO joins that violation to a sentinel, such as ErrSceneInsertFull, so the core can refuse the request.

A sweep repeats until a batch comes back short, and its ORDER BY puts the most urgent rows first.

Fetch related rows in the same query, with a join or = ANY(?0), never one query per parent row.

An existence check needs a single row. It ends with LIMIT 1, and its caller tests for any row. A test for exactly one row reads two unexpected matches as none.

Batch writes

A batch operation (see write-go-service's operations) applies its list in one statement: one round trip, and all of it or none. Pass each column of the list as bun.List(values) inside a typed array, ARRAY[?1]::uuid[]: bun expands the values into the array, and the cast types it. The statement returns at most one row per item, so the list cap bounds it and RETURNING takes no LIMIT.

An insert or an upsert zips its column arrays with unnest, which reads them side by side as rows. A batch insert of ordered rows also clamps its start as the single insert above does, shifts the other rows by cardinality(ARRAY[?2]::uuid[]), and numbers its rows from 1 in request order with unnest(…) WITH ORDINALITY (pg.scenesInsert.sql). The batch cell upsert:

INSERT INTO
  cells (project_id, scene_id, layer_id, content, updated_at)
SELECT
  ?0,
  data.scene_id,
  data.layer_id,
  data.content::jsonb,
  ?4
FROM
  unnest(ARRAY[?1]::uuid[], ARRAY[?2]::uuid[], ARRAY[?3]::text[]) AS data (scene_id, layer_id, content)
ON CONFLICT (scene_id, layer_id) DO UPDATE
SET
  content = EXCLUDED.content,
  updated_at = EXCLUDED.updated_at
RETURNING
  scene_id,
  layer_id,
  updated_at;

A batch update applies only when the parent holds every row it names, so it needs no transaction. A CTE counts the named rows the parent holds, and the UPDATE matches nothing unless that count equals the list's length. The core still compares the rows returned with the list to answer not-found:

WITH
  data AS (
    SELECT
      *
    FROM
      unnest(ARRAY[?1]::uuid[], ARRAY[?2]::text[]) AS data (id, title)
  ),
  held AS (
    SELECT
      count(*) AS count
    FROM
      scenes
      JOIN data ON data.id = scenes.id
    WHERE
      scenes.project_id = ?0
  )
UPDATE scenes
SET
  title = data.title,
  updated_at = ?3
FROM
  data,
  held
WHERE
  scenes.project_id = ?0
  AND scenes.id = data.id
  AND held.count = (
    SELECT
      count(*)
    FROM
      data
  )
RETURNING
  scenes.*;

A batch delete matches its IDs with = ANY and closes every gap it leaves in the same statement: each later row moves up by the number of deleted rows before it. The parts of one WITH share a snapshot, so the UPDATE still sees the rows the DELETE removes and skips them by ID. An ID the parent does not hold deletes nothing and raises no error, which is why the core counts the rows returned.

WITH
  deleted AS (
    DELETE FROM scenes
    WHERE
      project_id = ?0
      AND id = ANY (ARRAY[?1]::uuid[])
    RETURNING
      *
  ),
  shifted AS (
    UPDATE scenes
    SET
      position = scenes.position - (
        SELECT
          count(*)
        FROM
          deleted
        WHERE
          deleted.position < scenes.position
      )::integer
    WHERE
      scenes.project_id = ?0
      AND scenes.id <> ALL (ARRAY[?1]::uuid[])
      AND scenes.position > (
        SELECT
          min(position)
        FROM
          deleted
      )
  )
SELECT
  *
FROM
  deleted;

Read vs Write Targets

This service maintains two objects for the keys entity:

• keys — the base table. All writes (INSERT, UPDATE) target this table directly.
• active_keys — a plain view that exposes only non-expired, non-deleted rows, evaluated per query. All reads (SELECT) target this view.

Never read from keys directly in a DAO query — active_keys enforces the expiry and soft-delete rules automatically. Never write to active_keys.

Expired and revoked keys stay in keys for good, reachable only by manual maintenance queries. Since the view filters them at read time, the index under it leads with the lookup key, then the expiry the view filters on. keys (usage, expires_at) lets a search range-scan the live keys of its usage instead of every key the usage ever had.

Schema Migrations (internal/models/migrations/*.sql)

Migrations are the authoritative history of the database schema. Every schema change must go through a migration — no out-of-band DDL.

File Naming

YYYYMMDDHHMMSS_<description>.<up|down>.sql

The timestamp is the current local wall-clock time down to the second: run date '+%Y%m%d%H%M%S' immediately before creating the files. Never truncate to minutes, guess, or reuse an existing timestamp. Never use date -u: existing migrations carry local time, so a UTC stamp can sort before one already merged.

The description uses underscores and is as specific as possible:

20250113182800_keys_table.up.sql
20250113182800_keys_table.down.sql
20260416152344_add_user_soft_delete.up.sql
20260416152344_add_user_soft_delete.down.sql

Always Create Both Up and Down

Every migration requires a paired .up.sql and .down.sql. The down migration must fully reverse the up migration so that a rollback restores the exact prior schema state.

When reversal is inherently destructive (the down migration drops a table and loses all its data), that is expected — document it with a comment and use IF EXISTS guards.

If a change is irreversible (dropping a column that held data), still write the closest approximation of a reversal — re-add the column without its data — and comment on the limitation.

Immutability of Committed Up Migrations

Never modify a .up.sql file that has been merged to master. Migrations are applied once, in order, and are never re-run. Editing an applied migration has no effect where it already ran, and silently diverges the codebase from the actual schema.

Permitted exceptions — changes with no runtime effect:

• Adding, editing, or removing comments (-- and /* */).
• Reformatting whitespace or alignment.

For everything else — including fixing a bug in an existing migration — create a new migration with a current timestamp.

Up migrations on the current branch (not yet merged to master) and all down migrations may be edited freely, since they have not yet been applied to any shared environment.

Exception: service-template edits its initial migrations in place

a-novel/service-template is exempt from the immutability rule. Nothing is ever published or deployed from a template, so no environment has already applied its migrations and there is no divergence to create. The rule's entire premise is absent.

In that repo, change the schema by editing the initial migration directly — do not add a new one. Every generated service inherits the template's migration set, so a second migration that only patches the first is propagated forever with no purpose.

-- service-template: edit 20250306000000_items_table.up.sql in place
created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

-- NOT this — a corrective migration in a repo that never runs migrations against a live database
ALTER TABLE items ALTER COLUMN created_at TYPE timestamp with time zone;

The template's CI follows suit. Its main.yaml skips check-migration-snapshots and buf's breaking check when github.event.repository.is_template is true, so an in-place edit needs no override label. Regenerate the schema snapshot with GOLIB_UPDATE_SNAPSHOTS=1. A fork is no template repository, so both guards run there.

This applies only to service-template. Every real service — service-authentication, service-json-keys, service-narrative-engine, and anything generated from the template — has deployed environments and follows the immutability rule above without exception.

Statement Splitting: --bun:split

bun's migration runner executes each file as a single database round-trip by default. When a migration holds statements that must run sequentially (B depends on A having committed), separate them with --bun:split:

-- Step 1: drop the old plain view
DROP VIEW IF EXISTS active_keys;

--bun:split
-- Step 2: create the materialized view and its index together
CREATE MATERIALIZED VIEW active_keys AS (...);

CREATE INDEX active_keys_usage_idx ON active_keys (usage);

--bun:split
-- Step 3: populate the view (requires step 2 to have completed)
REFRESH MATERIALIZED VIEW active_keys;

Statements with no ordering dependency (CREATE TABLE then CREATE INDEX on that table) need no split — PostgreSQL handles multiple DDL statements in one round-trip. Split only when a later statement requires an earlier one to have committed first.

Down Migration Ordering

Down migrations reverse the up migration in reverse creation order:

• What was created last is dropped first.
• Scheduled pg_cron jobs are unscheduled before the objects they reference are dropped.
• Indexes are dropped before the table or view they index.
• Dependent objects (views, materialized views) are dropped before the tables they read from.

Example — down for a migration that created a materialized view with an index:

DROP INDEX IF EXISTS active_keys_usage_idx;

DROP MATERIALIZED VIEW IF EXISTS active_keys;

--bun:split
-- Restore the prior plain view.
CREATE VIEW active_keys AS (...);

Guard Clauses in Down Migrations

In down migrations, always use IF EXISTS so a partial rollback or a re-application does not fail on missing objects:

DROP TABLE IF EXISTS keys;

DROP INDEX IF EXISTS keys_usage_idx;

DROP VIEW IF EXISTS active_keys;

DROP MATERIALIZED VIEW IF EXISTS active_keys;

In up migrations, use IF NOT EXISTS only where the migration is designed to be idempotent (adding a standalone index that is safe to re-apply). Do not use it for table creation — the timestamp uniqueness makes re-application impossible under normal operation, and masking an accidental re-application is worse than surfacing it as an error.

pg_cron Scheduled Jobs

A service's jobs live in one file, internal/models/migrations/cron/jobs.sql, embedded as migrations.Jobs. The migrations command runs it after the migrations, on every run. A database import keeps the migration history but leaves pg_cron's jobs out: pg_cron's row-level security hides them from a dump by any role but a superuser. A job that only a migration schedules is gone after an import. Change a job's schedule or command in the file, never in a new migration. The cron/ directory keeps the file out of the append-only check on the migrations' *.sql.

A job's command calls a function a migration defines, so a release changes the job's work by replacing the function, the schema census snapshots it, and a test calls it.

The file runs only where pg_cron is installed. The databases postgrestest.RunDBTest clones have no cron schema, and PL/pgSQL resolves cron.* only when the guarded statement runs. Scheduling a name again replaces the role's own job of that name. A job another role owns, such as one a database host hands to the service's role, keeps its owner and takes the definition through cron.alter_job. Only a superuser sees that job and may call alter_job. PostgreSQL checks the privilege when it plans the statement, even when no row matches, so the call sits behind a check only a superuser passes:

DO $$
DECLARE
  definition record;
BEGIN
  IF NOT EXISTS (SELECT FROM pg_extension WHERE extname = 'pg_cron') THEN
    RETURN;
  END IF;

  FOR definition IN
    SELECT * FROM (
      VALUES
        (
          'service-genai-purge-settled',
          '10 seconds',
          format('SELECT %I.purge_settled_generations()', current_schema())
        )
    ) AS jobs (name, schedule, command)
  LOOP
    IF EXISTS (SELECT FROM cron.job WHERE jobname = definition.name AND username <> current_user) THEN
      PERFORM cron.alter_job(jobid, schedule => definition.schedule, command => definition.command)
      FROM cron.job
      WHERE jobname = definition.name AND username <> current_user;
    ELSE
      PERFORM cron.schedule(definition.name, definition.schedule, definition.command);
    END IF;
  END LOOP;
END
$$;

Name the loop record anything but job: in a query on cron.job, job.schedule is ambiguous with the table's own column. Test the file with postgrestest.RunTransactionalTest, whose database has pg_cron and whose rollback leaves its jobs as they were. Cover a superuser and a non-superuser database owner, since only the owner meets row-level security and the alter_job privilege.

To retire a job, delete it from the file and unschedule it in a migration, before any migration drops the function it calls:

DO $$
BEGIN
  IF EXISTS (SELECT FROM pg_extension WHERE extname = 'pg_cron') THEN
    PERFORM cron.unschedule(jobid) FROM cron.job WHERE jobname = 'service-genai-purge-settled';
  END IF;
END
$$;

Job names are global within the PostgreSQL instance, so a generic name collides with jobs from other services. Use a descriptive, service-scoped one: service-genai-purge-settled, not purge. The database image creates the extension in its init SQL, which runs before the first migration.

Migrations on live tables

A migration runs while the previous release still serves and long requests are in flight. The migrations job gives up a lock wait after 3 seconds and retries the file, and a server whose sessions set a lock_timeout fails a write that waits longer instead of delaying it. In production the job ends a run after 40 minutes (write-infra), so a file's work fits in that. Keep any lock that blocks writes on a populated table under a second or two:

• Build an index concurrently, alone in its file. A file runs as one implicit transaction, which CREATE INDEX CONCURRENTLY refuses. A concurrent build that fails leaves an invalid index behind and the runner reruns the file, so drop it first, in its own split section:

  DROP INDEX CONCURRENTLY IF EXISTS generations_owner_idx;
  
  --bun:split
  CREATE INDEX CONCURRENTLY generations_owner_idx ON generations (owner_id);

  Its down migration is DROP INDEX CONCURRENTLY IF EXISTS generations_owner_idx;.

• Add a constraint NOT VALID, then validate it in a later file. Adding a CHECK or a foreign key scans the table under a lock that blocks writes; VALIDATE CONSTRAINT scans under one that lets them through.
• Add a column without rewriting the table. A nullable column, or one with a constant default, changes only the catalog. A volatile default or a type change rewrites every row under an exclusive lock.
• Backfill in batches, outside the migration. One UPDATE over a table holds every row lock it takes until it commits.
• Drop only what nothing reads, in any repository. The release that stops naming a column or table deploys everywhere before the release that drops it, whose migrations run while the previous release still serves. Infra reads columns too: each service's database metrics (environments/production/cluster/queries/<service>.yaml) and their grants (<service>.sql), which run again after every migrations run. A GRANT naming a dropped column fails the deploy, so remove the column from both first (a-novel/infra#1597).

A table that stays small, such as one purged within hours, can take a plain statement: judge by the size it reaches in production, not by its size today.

Materialized Views

When creating a materialized view that will ever be refreshed with CONCURRENTLY, a unique index on the view is required by PostgreSQL:

CREATE MATERIALIZED VIEW active_keys AS (...);

CREATE UNIQUE INDEX active_keys_id_idx ON active_keys (id);

PostgreSQL does not inherit constraints or indexes from the source table into a materialized view. Without the unique index, REFRESH MATERIALIZED VIEW CONCURRENTLY silently fails at runtime (the scheduler's hourly job runs but does nothing). Add the unique index in the same migration that creates the view, or in an immediate follow-up migration if the view already exists.

Migration Comments

Explain why — the SQL already says what it does. Comment on why the change was necessary or why a particular approach was chosen:

-- Converts active_keys from a plain view to a materialized view for read performance,
-- and replaces the COALESCE-based filter with explicit conditions so that deleted_at
-- can no longer be used as a backdoor expiry for keys that have not been revoked.
DROP VIEW IF EXISTS active_keys;

For inline column documentation inside CREATE TABLE, use block comments /* */ immediately after the column definition:

CREATE TABLE keys (
  id uuid PRIMARY KEY NOT NULL,
  /* Encrypted private key in JSON Web Key format, base64url-encoded. */
  private_key text NOT NULL CHECK (private_key <> ''),
  /* Public key in JSON Web Key format, base64url-encoded. Null for symmetric keys. */
  public_key text,
  /* Hard expiry date. Once passed, the key is excluded from the active view. */
  expires_at timestamp(0) with time zone NOT NULL,
  /* Soft-delete timestamp. Set when a key is revoked early (e.g., due to a compromise). */
  deleted_at timestamp(0) with time zone
);

PostgreSQL Conventions

These rules apply to all SQL files — both DAO queries and migrations.

Formatting

• SQL keywords in ALL CAPS: SELECT, FROM, WHERE, INSERT INTO, UPDATE, SET, RETURNING, ORDER BY, LIMIT, AND, OR, NOT, NULL, IS, IN, LIKE, etc.

• Each major clause on its own line; clause body indented two spaces:

  SELECT
    *
  FROM
    active_keys
  WHERE
    usage = ?0
    AND expires_at > CURRENT_TIMESTAMP
  ORDER BY
    created_at DESC
  LIMIT
    ?1;

• Multi-column lists (SELECT fields, INSERT column list, VALUES) have each item on its own line, indented two spaces.
• End every statement with ;.
• Use -- for standalone comments; /* */ for inline column docs inside CREATE TABLE.

Column Types

Always use timestamp(0) with time zone. The (0) precision truncates sub-second noise, making values round-trippable through Go's time.Time without drift. Never use bare timestamp (no timezone) — timezone-naive timestamps cause subtle bugs in multi-region or DST-affected deployments.

Never use varchar(n): PostgreSQL gains no performance from it over text, and length constraints belong in the core layer unless they are a true database invariant.

Required Text Fields

For required text columns that must never be empty, add an explicit CHECK constraint:

private_key text NOT NULL CHECK (private_key <> '')

Go's zero-value semantics make it easy to accidentally persist empty strings; the check constraint is the last line of defense.

Primary Keys

Use uuid for all entity primary keys. Never use serial or bigserial — auto-increment integers leak row counts and make client-side ID generation impossible. Generate UUIDs in Go before the INSERT so the caller always has the ID without a database round-trip.

Generate UUIDs with uuid.NewV7, and default the column to uuidv7(). A UUIDv7 starts with its creation time, so inserts land at the end of the primary key index. A UUIDv7 reveals when its row was created; keep uuid.New for an ID whose creation time must stay private.

Integrity Constraints

The schema keeps rows consistent with each other. The request's own rules, such as a length or an owner, stay with the service's validator and ownership check.

A row that references siblings under one parent stores the parent's ID and reaches each sibling through a composite foreign key. Each referenced table holds a UNIQUE (parent_id, id) key for it:

CREATE TABLE layers (
  id uuid PRIMARY KEY NOT NULL DEFAULT uuidv7(),
  project_id uuid NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
  -- …
  CONSTRAINT layers_project_id_id_key UNIQUE (project_id, id)
);

-- scenes holds the same key, scenes_project_id_id_key.
CREATE TABLE cells (
  project_id uuid NOT NULL,
  scene_id uuid NOT NULL,
  layer_id uuid NOT NULL,
  -- …
  PRIMARY KEY (scene_id, layer_id),
  -- Both keys carry the project, so a cell's scene and layer exist and share its project.
  CONSTRAINT cells_scene_fkey FOREIGN KEY (project_id, scene_id) REFERENCES scenes (project_id, id) ON DELETE CASCADE,
  CONSTRAINT cells_layer_fkey FOREIGN KEY (project_id, layer_id) REFERENCES layers (project_id, id) ON DELETE CASCADE
);

A write to cells then proves its scene and its layer exist in its project, with no read before it. Name every constraint whose violation the DAO maps to a sentinel, since the DAO matches the name. A list of a project's cells then filters on project_id alone.

Indexes

• Index every column used in a WHERE clause of a frequent query.
• Index foreign key columns (PostgreSQL does not auto-index them).

• Use partial indexes when a query always filters by a known condition:

  CREATE INDEX keys_active_usage_idx ON keys (usage)
  WHERE
    deleted_at IS NULL;

• For ORDER BY columns on tables that will grow large, index the sort column to avoid sequential scans.

• Give the planner a bound it can see. A value a subquery computes, such as (SELECT uuidv7(interval '-27 hours')), is known only at run time, so the planner assumes it matches a third of the rows and may scan the whole table. A stable expression is folded at planning: now() is one, while the volatile uuidv7() can never be an index condition. For a uuidv7 key, build the lowest uuidv7 of an instant from now(), as infra's GenAI usage metrics do (a-novel/infra#1600):

  generation_id >= (
    lpad(to_hex((extract(epoch FROM now() - interval '27 hours') * 1000)::bigint), 12, '0')
    || '70008000000000000000'
  )::uuid

• Unique indexes serve double duty as uniqueness constraints. Prefer them over UNIQUE column constraints when the index needs to be added after the table is created, or when IF NOT EXISTS semantics are needed.

Soft Deletes

Never hard-delete auditable entities. Use the soft-delete pattern established in the keys table:

deleted_at timestamp(0) with time zone, -- null = not deleted
deleted_comment text -- reason; required when deleted_at is set

The active_* view or materialized view filters out soft-deleted rows automatically. Direct database queries can still access them for auditing.

Time References

Use CURRENT_TIMESTAMP for the current time in both queries and DDL. Never use NOW() — the two are equivalent, but CURRENT_TIMESTAMP is the SQL standard form used throughout this codebase:

WHERE
  expires_at > CURRENT_TIMESTAMP

Common Pitfalls

The rules above, as a pre-push checklist:

• Modifying a committed up migration instead of writing a new one.
• Shipping an .up.sql with no paired .down.sql.
• Guessing or truncating a migration timestamp.
• Using $1 where bun expects ?0.
• Inlining DAO SQL in Go instead of //go:embed-ing a .sql file.
• Reading from keys instead of active_keys.
• Omitting RETURNING * on a mutating query whose row the caller needs.
• A multi-row query without a LIMIT, or one query per parent row.
• A list that reads a whole set at once, even one the schema caps, or a client list whose total comes from a separate count query.
• A renumbering that reads every row instead of shifting a range in SQL.
• A count read before a write to clamp a position, which LEAST does in the shifting statement.
• A read before a write to prove a referenced row exists in the same parent, which a composite foreign key proves in the write itself.
• Omitting --bun:split between statements that must commit in order.
• Creating a materialized view without the unique index REFRESH ... CONCURRENTLY requires.
• Dropping objects out of reverse creation order in a down migration.
• Omitting IF EXISTS in a down migration.
• varchar(n) over text, bare timestamp over timestamp(0) with time zone, NOW() over CURRENT_TIMESTAMP.
• Generic pg_cron job names that collide across services.
• A pg_cron job scheduled in a migration instead of the jobs file, which a database import loses.
• A cron.* call outside the pg_extension guard, which fails in the test clones that lack pg_cron.
• A plain CREATE INDEX, a constraint added without NOT VALID, or a table rewrite on a populated live table.