Verify DB Change

Verify DB Change

Prove a database change works end-to-end against real Postgres — not by asserting against mocks. The output is a markdown proof block, suitable for pasting into a PR description, that quotes actual captured output from each step. If a step did not run or did not pass, the proof block says so explicitly. No paraphrasing, no inferred outcomes.

This skill is designed for the case where the change is a typical projection-plane drop: one or more migration files, a writer / projector class, an entity or schema update, and (optionally) DI wiring in an app module. It generalizes cleanly to any INSERT … ON CONFLICT / UPDATE pattern and to migrations that touch existing tables.

Core principles (why this skill exists)

1. Real DB beats mocks for SQL contracts. Unit tests that stub manager.query() validate that the projector calls a query — they validate nothing about whether the query does what you think in Postgres. ON CONFLICT DO NOTHING, GREATEST(...), returning-row semantics, FK cascade, generated columns, partial indexes — these are SQL contracts. They must be tested against a real Postgres at least once before merge.
2. BEGIN…ROLLBACK is your friend for non-destructive happy / edge tests. A wrapped transaction lets you exercise the writer's actual SQL against the live dev DB, observe the results, and roll them back so the DB is byte-identical afterwards. Cheaper than spinning up a fresh DB, more honest than mocks. The only thing it doesn't cover is reversibility (revert can't run inside a transaction).
3. Never run migration:revert on a shared dev DB. Reverting on the same DB everyone is using rips the table out from under any other process — including your own running app. Always clone the schema to a throwaway DB first.
4. Test down() programmatically, not via equivalent SQL. "I read the migration file, it drops the table — looks right" is not a test. Run the actual down() method via a TypeORM DataSource against a throwaway DB and observe the SQL TypeORM emits. The CLI-equivalent (migration:revert) works too, but a one-off script is faster and lets you assert on the after-state in the same run.
5. Boot is part of the test surface. A new provider that compiles fine can still break DI at startup (missing dep, circular import, wrong scope). Booting the app with the new wiring and grepping the log for Nest can't resolve / UnknownDependencies is cheap and catches a class of bug that no unit test will.
6. Always clean up. Drop the throwaway DB, delete one-off scripts, never leave the working tree dirty. The verification should leave zero trace on disk and zero trace in Postgres — only the proof block in the PR.
7. Quote, don't paraphrase. The proof block must contain actual captured output. "Migration ran cleanly" is worthless; SELECT name FROM migrations WHERE name LIKE '%UserLogin%' returning the two expected rows is proof.

Be terse but precise in the proof block. The reviewer should be able to verify each claim by running the same command themselves.

When to use

User-facing triggers:

• "verify the migration" / "test the migration" / "prove the schema change works"
• "verify the projector" / "test the writer"
• "run the manual tests" / "run any manual test needed to prove completion"
• "make sure the database actually works"
• Reviewer says: "this PR has migrations but no proof"

Also use proactively when:

• A PR you are reviewing changes files under infra/migrations/ (or equivalent) and the description has no captured DB output.
• A user is about to merge a branch that touches an entity + a migration + a projector and has only run unit tests.

Do not use for:

• Pure read-side changes that don't touch schema (use the regular test gates instead).
• Frontend-only PRs (use proofshot / verify / dev-browser).
• Changes already covered by an integration suite that hits a real Postgres in CI — those are already proven; running this skill duplicates them.

Process

0. Orient — what's the change surface?

Before running anything, list:

git diff --stat origin/<base>...HEAD

Identify:

• Migration files — usually infra/migrations/**/*.ts or migrations/**/*.ts or prisma/migrations/. Note the timestamp / order.
• Entities or schema modules — TypeORM *.entity.ts, Prisma schema.prisma, etc.
• Writers / projectors — the class that issues the SQL. Look for INSERT, UPDATE, manager.query, queryRunner.query.
• DI wiring — modules that register the new writer (Nest *.module.ts, etc.).
• Target DBs — single DB, or multi-tenant? Find the Docker container name and the per-tenant DB name. docker ps --format 'table {{.Names}}\t{{.Image}}\t{{.Ports}}' usually reveals it.
• App to boot — which service owns the writer? That's the one whose boot you'll smoke.

If you can't answer all five questions, stop and read the diff more carefully. Verification without the map of the change is theater.

1. Static gates

Run, in parallel:

pnpm --filter <app> typecheck
pnpm --filter <app> lint
pnpm --filter <app> test -- --testPathPattern='<changed-file-names>'

For each new or changed test, capture:

• Pass / fail count
• Line + branch coverage on the new files (look for 100 | 100 or note the gap)

If coverage is < 100% on a new writer / migration, note which lines are uncovered — they're the candidates for the next step's edge cases.

If lint warnings exist in files you did not touch, note them as pre-existing. Do not bundle their fixes into this PR.

2. Schema landed per tenant

For each target DB (one DB or one per tenant):

docker exec <container> psql -U postgres -d <dbname> -c "\d <new_table>"
docker exec <container> psql -U postgres -d <dbname> -c "\d <touched_table>" | tail -25
docker exec <container> psql -U postgres -d <dbname> -c \
  "SELECT name FROM migrations WHERE name LIKE '%<MigrationStem>%' ORDER BY id;"

Capture:

• The \d <new_table> output (columns, indexes, FKs)
• The relevant lines of \d <touched_table> showing the new columns
• The SELECT FROM migrations rows confirming both migrations recorded

Watch for surprises:

• Column type mismatch with the entity (e.g. migration says varchar(64), entity says varchar(255)).
• Missing index where the writer expects one (composite for (userId, loggedInAt) etc.).
• FK present when the migration said createForeignKeys: false or vice versa.
• Default values that don't match the entity definition.

3. Boot smoke test

For an app that registers the new writer in DI:

pnpm --filter <app> start > /tmp/<app>-boot.log 2>&1 &
BOOT_PID=$!
sleep 30
kill -9 $BOOT_PID 2>/dev/null
pkill -9 -f "dist/src/main" 2>/dev/null
wait $BOOT_PID 2>/dev/null

(timeout is missing on macOS — use the background+sleep+kill pattern.)

Then grep:

grep -c "started successfully\|Application is listening" /tmp/<app>-boot.log
grep -iE "Nest can't resolve|UnknownDependencies|UnknownProvider|UnknownExports|Cannot find module" /tmp/<app>-boot.log

A passing boot has:

• Exactly one "started successfully" line
• Zero matches on the DI-error grep

Capture the "started successfully" line and one or two adjacent lines proving the relevant module initialized (e.g. EventListenerModule dependencies initialized).

4. Behavioural SQL dry-run inside BEGIN…ROLLBACK

This is the load-bearing step. For each behaviour the writer claims to implement, write a SQL statement that exercises it, wrap everything in BEGIN; … ROLLBACK;, and run it against the live dev DB.

The standard contract for an event-sourced projector is roughly:

• Happy path: a new event id inserts a row and bumps any aggregates.
• Replay / duplicate: the same event id is a no-op; aggregates do not double-bump.
• Out-of-order: an event with an older timestamp does not rewind monotone fields (GREATEST guard).
• Orphan: a write that references a missing parent row surfaces correctly (warn-only / FK-violation / etc., per the writer's contract).

Adapt to your writer's actual contract. Don't invent assertions the code doesn't make.

Write the SQL to a file (heredoc inline can mangle quoting):

cat > /tmp/<writer>-dryrun.sql <<'SQL'
\set USER_ID '''<existing-user-uuid>'''
\set EVT1 '''11111111-1111-1111-1111-111111111111'''
…
BEGIN;
\echo '===== Step 1: happy path ====='
INSERT INTO … ON CONFLICT … RETURNING id;
UPDATE … RETURNING id, "lastLoginAt", "loginCount";

\echo '===== Step 2: replay (same id) — expect ZERO rows from RETURNING ====='
INSERT …;
SELECT … FROM users WHERE id = :USER_ID;
…
ROLLBACK;
SQL

docker cp /tmp/<writer>-dryrun.sql <container>:/tmp/<writer>-dryrun.sql
docker exec <container> psql -U postgres -d <dbname> -f /tmp/<writer>-dryrun.sql

Capture the full output. Each \echo header makes the proof block readable. Build a small table mapping scenario → observed. Confirm each row of the table matches the writer's documented contract; if any doesn't, that's a real bug — stop and tell the user before continuing.

Do not skip the rollback. If a step fails partway and Postgres remains in a transaction, the next BEGIN will error.

5. Reversibility via throwaway schema clone

Reverting migrations against the shared dev DB is destructive and forbidden. Instead:

TS=$(date +%s)
DBNAME="test_revert_<short-stem>_${TS}"

# 5a. Create the throwaway DB on the same container
docker exec <container> psql -U postgres -c "CREATE DATABASE ${DBNAME};"

# 5b. Clone schema only (no row data, faster + smaller)
docker exec <container> bash -c \
  "pg_dump -U postgres --schema-only <source-dbname> | psql -U postgres -d ${DBNAME}"

# 5c. Confirm clone has the schema you intend to revert
docker exec <container> psql -U postgres -d ${DBNAME} \
  -c "SELECT to_regclass('<new_table>') AS exists;"

Then write a one-off TypeORM script that imports each migration class and calls .down(qr) in reverse order. Example skeleton:

// infra/scripts/_test-revert-<stem>.ts — temporary, delete after running
import { DataSource } from "typeorm";
import { CreateXxx1779360850300 } from "../migrations/.../1779360850300-CreateXxx";
import { AddYyy1779360850301 } from "../migrations/.../1779360850301-AddYyy";

async function main(): Promise<void> {
  const ds = new DataSource({
    type: "postgres",
    host: "localhost",
    port: <port>,
    username: "postgres",
    password: "postgres",
    database: process.env.TEST_REVERT_DBNAME!,
    logging: ["query", "error"],
  });
  await ds.initialize();
  const qr = ds.createQueryRunner();
  await qr.connect();

  // BEFORE snapshot — capture concrete existence checks
  const before = await qr.query(
    "SELECT to_regclass('<new_table>') AS exists, " +
    "ARRAY(SELECT column_name FROM information_schema.columns " +
    "WHERE table_name='<touched_table>' AND column_name IN ('newCol1','newCol2')) AS cols"
  );

  await new AddYyy1779360850301().down(qr);
  await new CreateXxx1779360850300().down(qr);

  const after = await qr.query(/* same SELECT as above */);
  console.log({ before, after });
  await qr.release();
  await ds.destroy();
}
main().catch((e) => { console.error(e); process.exit(2); });

Run with TEST_REVERT_DBNAME=<dbname> pnpm exec ts-node infra/scripts/_test-revert-<stem>.ts. The --logging=["query","error"] option makes TypeORM print the actual DROP TABLE / DROP INDEX / ALTER TABLE statements it emits — capture those for the proof block.

Pass criteria:

• after.user_logins is null (or whatever sentinel to_regclass returns for "gone")
• after.cols is [] (or contains only columns that existed before up())
• No leftover indexes matching IDX_<table>_*

If anything is left over (a stray index, a sequence, a check constraint), down() is incomplete. Surface this as a blocker.

6. Cleanup

Always:

docker exec <container> psql -U postgres -c "DROP DATABASE ${DBNAME};"
rm -f infra/scripts/_test-revert-<stem>.ts
rm -f /tmp/<writer>-dryrun.sql /tmp/<app>-boot.log
git status --short    # must show only the files the change itself touched

If git status --short shows the one-off script or any other artifact, delete it. The verification leaves zero trace.

Producing the proof block

Paste a section into the PR description that follows this exact structure. Each subsection is one of the steps above; each contains real captured output, not interpretation. If a step did not pass, the subsection says so and the verdict at the top reflects it.

## Verification

### Automated gates
- [x] `pnpm --filter <app> typecheck` — clean
- [x] `pnpm --filter <app> lint` — N errors / N warnings (note pre-existing in untouched files)
- [x] Unit specs — X/Y passing across <files>
  - `<file>` — 100% lines / 100% branches  (or note gap)

### Manual verification on the local dev stack (PG <version>, <tenants>)

**1. Migrations apply cleanly on <tenant(s)>**
\`\`\`text
$ docker exec <container> psql -U postgres -d <dbname> -c \
    "SELECT name FROM migrations WHERE name LIKE '%<Stem>%' ORDER BY id;"
                name
-------------------------------------
 <Migration1>
 <Migration2>

$ \d <new_table>
 <key columns + indexes + FK summary>

$ \d <touched_table>
 ... <new columns> ...
\`\`\`

**2. Writer SQL works end-to-end against real Postgres**

Ran the writer's SQL inside `BEGIN…ROLLBACK` against the live <dbname>:

| Scenario | Observed |
|---|---|
| Happy path | <quote> |
| Replay (same id) | <quote> |
| Out-of-order older event | <quote> |
| <other contract bullet> | <quote> |

Validates <one-sentence statement of what the SQL contract is>.

**3. <App> boots with the new <Provider> registered**

\`\`\`text
[Nest] LOG [InstanceLoader] <Module> dependencies initialized
INFO: <App> application started successfully
\`\`\`

No `Nest can't resolve` / `UnknownDependencies` errors.

**4. Migration reversibility — `down()` cleanly reverses `up()`**

Ran both migrations' `down()` against a throwaway DB cloned via `pg_dump --schema-only`:

\`\`\`text
=== BEFORE revert ===
{ <new_table>: '<new_table>', <touched_table>_cols: ['newCol1', 'newCol2'] }

=== <Migration2>.down() ===
ALTER TABLE "<touched_table>" DROP COLUMN "newCol1"
ALTER TABLE "<touched_table>" DROP COLUMN "newCol2"

=== <Migration1>.down() ===
DROP INDEX "public"."IDX_<table>_*"
DROP TABLE "<new_table>"

=== AFTER revert ===
{ <new_table>: null, <touched_table>_cols: [], leftover_indexes: [] }
\`\`\`

Throwaway DB dropped after the test.

Keep the proof block under ~80 lines. If the captured output is verbose, trim to the load-bearing lines (the \echo headers, the RETURNING rows, the DROP … statements TypeORM emitted). The reviewer wants to read it in 30 seconds, not 5 minutes.

Edge cases

• No multi-tenant setup: Step 2 collapses to a single DB. Don't invent tenants you don't have.
• Migrations include data changes (not just DDL): Step 5 is harder because pg_dump --schema-only excludes data. Either include --data-only for the affected tables, or hand-seed the throwaway with the minimum data needed for the migration to find what it expects.
• Writer is async / queued: the BEGIN…ROLLBACK trick only works for synchronous SQL. If the writer fans out to a queue or another process, that path needs an integration test, not this skill — note the gap.
• Migration uses non-transactional DDL (e.g. CONCURRENTLY index builds): BEGIN…ROLLBACK won't wrap them. Step 4 still applies to the writer's SQL, but you cannot test the migration itself inside a transaction. Use the throwaway-DB approach for that subset.
• DEFAULT 0 on a NOT NULL column added to an existing table: PG ≥ 11 fast-paths this (metadata-only). Older PG rewrites the table. Confirm the PG version in step 0 and note it in the proof block — PG <version> in the section header.
• FKs declared in a later migration: \d <new_table> may show no FK even though one is intentional later. Don't flag this as a smell without checking the rest of the chain.
• TypeORM dropTable with dropForeignKeys: false: incoming FKs from other tables can block the drop. If reversibility fails here, surface the offending FK explicitly — don't add CASCADE just to make the test pass.
• Existing PR has screenshots or other content: pull the current body first (gh pr view <n> --json body --jq .body > /tmp/cur.md), preserve it, and replace only the Verification section. Never clobber the whole body.

Tone for the proof block

Direct. The reviewer should read the proof, not the prose. If something is missing, name what's missing; if something passed, quote the line that proves it. The PR description is not the place to hedge.

If during the verification you find a real bug (the writer's SQL doesn't behave as documented, a migration's down() leaves a stray index, the app fails to boot) — stop the skill and tell the user. The proof block exists to surface bugs, not paper over them. A failed verification is more valuable than a green one.