[E00-S03-T03] Migration ledger created #392

2026-08-29T23:20:17Z

bot-implementer commented

2026-08-29 23:20:17 +00:00

What changed

Implements [E00-S03-T03] Migration ledger created (#178): the migration ledger that records applied migrations now exists in the database-postgres package and is exercised against a real database.

packages/database-postgres/src/ledger.ts — new MigrationLedger over the package-owned pg Pool: ensure() creates the ledger table (schema_migrations, version text PRIMARY KEY + applied_at timestamptz NOT NULL DEFAULT now()) with idempotent DDL (CREATE TABLE IF NOT EXISTS); record() inserts an applied migration with an idempotent, parameterized statement (INSERT … VALUES ($1) ON CONFLICT (version) DO NOTHING — a rerun never double-applies, and the version is always a bound parameter, never interpolated); has()/applied() read the ledger back in apply order (ORDER BY applied_at, version). Rollback: DROP TABLE schema_migrations resets migration state (the issue's rollback note).
packages/database-postgres/src/index.ts — driver boundary re-export: MigrationLedger + MIGRATION_LEDGER_TABLE are re-exported from the package entrypoint, so no other workspace package needs the pg driver to touch migration state (isolation E00-S03-T02 stays intact — the new module imports only import type { Pool } and lives inside the owner package).
tests/database-postgres-ledger.test.mjs — new suite locking in both acceptance criteria: static assertions on the committed source (idempotent table DDL with version PK + applied_at, idempotent parameterized record(), has()/applied() queries, driver-boundary re-export, CI enforcement) each backed by a mutation probe proving non-vacuousness (removing PRIMARY KEY, IF NOT EXISTS, ON CONFLICT, or $1 binding, or dropping the boundary re-export fails loudly). The real-stack probe implements the issue's test plan — "migrate an empty database and confirm the ledger exists": it starts the committed compose db service (isolated project eppp-ledger-probe + host port 55432 so it never collides with the compose-config suite), drops any leftover ledger table for a clean slate, executes the committed ledger module against the empty database, and asserts the ledger exists (to_regclass), both fixtures are recorded in apply order, has() answers correctly, and a second run records nothing twice (the story's "second migration run is idempotent"). Cross-checks from inside the db container via psql (information_schema table count, recorded versions). Skipped cleanly where Docker or Node ≥ 23.6 (type stripping) is unavailable.
.gitea/workflows/ci.yml — new database-postgres-ledger job runs node --test tests/database-postgres-ledger.test.mjs on every PR so the ledger criterion gates merges. (Pipeline tripwire: additive job only, no existing job modified, same action majors, no secrets: context, no untrusted interpolation — matches the security-reviewed #390/#391 precedent.)
Docs/descriptors: docs/development/non-container.md package table and packages/database-postgres/package.json description updated; .gitignore ignores the transient probe file the real-stack probe writes into the package (removed in its finally).

Explicitly out of scope per the brief, not touched: pg/Kysely isolation (E00-S03-T02), advisory lock (E00-S03-T04), failure diagnostic (E00-S03-T05).

Criterion → test table

Acceptance criterion	Test (fails without the committed state)
migration ledger is created	`tests/database-postgres-ledger.test.mjs` — "the ledger module exists in the driver-owner package and defines the schema_migrations table" and "the ledger is created by idempotent DDL (migration ledger is created)" (source defines `CREATE TABLE IF NOT EXISTS schema_migrations` with `version text PRIMARY KEY` + `applied_at timestamptz NOT NULL DEFAULT now()`); mutation probes "removing the version primary key …" and "removing the idempotent create …" prove non-vacuousness; real-stack probe "migrating an empty database creates the ledger and records applied migrations (real stack)" runs the committed module against an empty database and confirms `schema_migrations` exists (`to_regclass` + `information_schema` count 1 from inside the container)
applied migrations are recorded in the ledger	`tests/database-postgres-ledger.test.mjs` — "record() records applied migrations with an idempotent, parameterized insert" (`ON CONFLICT (version) DO NOTHING`, `$1` binding, no `${version}` interpolation) and "has() and applied() read the ledger back through the pool"; mutation probes "removing ON CONFLICT …", "interpolating the version into the SQL …", "dropping the ledger re-export …", "a placeholder ledger module …"; real-stack probe records two fixtures, asserts them in apply order, `has()` true/false, and `rowCount` stays 2 after a re-run (idempotent), plus psql cross-check of the recorded versions inside the db container
the criterion is enforced in CI	`tests/database-postgres-ledger.test.mjs` — "the ledger criterion is enforced in CI" (root test glob covers the suite and `.gitea/workflows/ci.yml` runs it); `.gitea/workflows/ci.yml` — `database-postgres-ledger` job runs `node --test tests/database-postgres-ledger.test.mjs` on every PR

Test plan executed

node --test tests/database-postgres-ledger.test.mjs → 12 pass / 0 fail / 1 skip (the docker-gated real-stack probe skips where no Docker daemon is available).
Full suite (node --test "tests/**/*.test.mjs", Node 24.20.0 + pnpm 11.23.0, frozen install) → 134 pass / 0 fail / 10 skip.
pnpm install --frozen-lockfile → passes ("Already up to date"); pnpm build / pnpm typecheck over the 5-project workspace → exit 0.
The real-stack probe's exact scenario was additionally executed against a live PostgreSQL 15 instance (probe harness, not the docker-gated test): ledger created (to_regclass → schema_migrations), both fixtures recorded in order, has() correct, rowCount 2 after re-run, information_schema count 1, versions readable via psql; DROP TABLE rollback verified.

Risks / notes

The docker-gated real-stack probe runs in the new CI job where a Docker daemon is available and skips cleanly otherwise; the job installs the frozen workspace because the probe executes the committed ledger module from the host (it resolves pg through the package's own dependency links).
The probe uses an isolated compose project (-p eppp-ledger-probe) and host port 55432 so it never collides with the compose-config suite's default-project containers or the default 5432 binding when both run on the same host.
Ledger writes are plain (no advisory locking) by design: locking is E00-S03-T04 and failure diagnostics E00-S03-T05, both explicitly out of scope here.
CI workflow change is strictly additive (new job, no existing job modified) and matches the security-reviewed #390/#391 precedent; per the review-checklist pipeline tripwire a human decision on the new merge gate may be requested.

Refs #178

## What changed Implements [E00-S03-T03] Migration ledger created (#178): the migration ledger that records applied migrations now exists in the `database-postgres` package and is exercised against a real database. - **`packages/database-postgres/src/ledger.ts` — new `MigrationLedger` over the package-owned `pg` Pool**: `ensure()` creates the ledger table (`schema_migrations`, `version text PRIMARY KEY` + `applied_at timestamptz NOT NULL DEFAULT now()`) with idempotent DDL (`CREATE TABLE IF NOT EXISTS`); `record()` inserts an applied migration with an idempotent, parameterized statement (`INSERT … VALUES ($1) ON CONFLICT (version) DO NOTHING` — a rerun never double-applies, and the version is always a bound parameter, never interpolated); `has()`/`applied()` read the ledger back in apply order (`ORDER BY applied_at, version`). Rollback: `DROP TABLE schema_migrations` resets migration state (the issue's rollback note). - **`packages/database-postgres/src/index.ts` — driver boundary re-export**: `MigrationLedger` + `MIGRATION_LEDGER_TABLE` are re-exported from the package entrypoint, so no other workspace package needs the `pg` driver to touch migration state (isolation E00-S03-T02 stays intact — the new module imports only `import type { Pool }` and lives inside the owner package). - **`tests/database-postgres-ledger.test.mjs` — new suite locking in both acceptance criteria**: static assertions on the committed source (idempotent table DDL with `version` PK + `applied_at`, idempotent parameterized `record()`, `has()`/`applied()` queries, driver-boundary re-export, CI enforcement) each backed by a mutation probe proving non-vacuousness (removing `PRIMARY KEY`, `IF NOT EXISTS`, `ON CONFLICT`, or `$1` binding, or dropping the boundary re-export fails loudly). The **real-stack probe** implements the issue's test plan — "migrate an empty database and confirm the ledger exists": it starts the committed compose `db` service (isolated project `eppp-ledger-probe` + host port 55432 so it never collides with the compose-config suite), drops any leftover ledger table for a clean slate, executes the **committed ledger module** against the empty database, and asserts the ledger exists (`to_regclass`), both fixtures are recorded in apply order, `has()` answers correctly, and a **second run records nothing twice** (the story's "second migration run is idempotent"). Cross-checks from inside the db container via psql (`information_schema` table count, recorded versions). Skipped cleanly where Docker or Node ≥ 23.6 (type stripping) is unavailable. - **`.gitea/workflows/ci.yml` — new `database-postgres-ledger` job** runs `node --test tests/database-postgres-ledger.test.mjs` on every PR so the ledger criterion gates merges. (Pipeline tripwire: additive job only, no existing job modified, same action majors, no `secrets:` context, no untrusted interpolation — matches the security-reviewed #390/#391 precedent.) - **Docs/descriptors**: `docs/development/non-container.md` package table and `packages/database-postgres/package.json` description updated; `.gitignore` ignores the transient probe file the real-stack probe writes into the package (removed in its `finally`). Explicitly out of scope per the brief, **not touched**: pg/Kysely isolation (E00-S03-T02), advisory lock (E00-S03-T04), failure diagnostic (E00-S03-T05). ## Criterion → test table | Acceptance criterion | Test (fails without the committed state) | | --- | --- | | migration ledger is created | `tests/database-postgres-ledger.test.mjs` — **"the ledger module exists in the driver-owner package and defines the schema_migrations table"** and **"the ledger is created by idempotent DDL (migration ledger is created)"** (source defines `CREATE TABLE IF NOT EXISTS schema_migrations` with `version text PRIMARY KEY` + `applied_at timestamptz NOT NULL DEFAULT now()`); mutation probes **"removing the version primary key …"** and **"removing the idempotent create …"** prove non-vacuousness; real-stack probe **"migrating an empty database creates the ledger and records applied migrations (real stack)"** runs the committed module against an empty database and confirms `schema_migrations` exists (`to_regclass` + `information_schema` count 1 from inside the container) | | applied migrations are recorded in the ledger | `tests/database-postgres-ledger.test.mjs` — **"record() records applied migrations with an idempotent, parameterized insert"** (`ON CONFLICT (version) DO NOTHING`, `$1` binding, no `${version}` interpolation) and **"has() and applied() read the ledger back through the pool"**; mutation probes **"removing ON CONFLICT …"**, **"interpolating the version into the SQL …"**, **"dropping the ledger re-export …"**, **"a placeholder ledger module …"**; real-stack probe records two fixtures, asserts them in apply order, `has()` true/false, and `rowCount` stays 2 after a re-run (idempotent), plus psql cross-check of the recorded versions inside the db container | | the criterion is enforced in CI | `tests/database-postgres-ledger.test.mjs` — **"the ledger criterion is enforced in CI"** (root test glob covers the suite and `.gitea/workflows/ci.yml` runs it); `.gitea/workflows/ci.yml` — **`database-postgres-ledger` job** runs `node --test tests/database-postgres-ledger.test.mjs` on every PR | ## Test plan executed - `node --test tests/database-postgres-ledger.test.mjs` → 12 pass / 0 fail / 1 skip (the docker-gated real-stack probe skips where no Docker daemon is available). - Full suite (`node --test "tests/**/*.test.mjs"`, Node 24.20.0 + pnpm 11.23.0, frozen install) → 134 pass / 0 fail / 10 skip. - `pnpm install --frozen-lockfile` → passes ("Already up to date"); `pnpm build` / `pnpm typecheck` over the 5-project workspace → exit 0. - The real-stack probe's exact scenario was additionally executed against a live PostgreSQL 15 instance (probe harness, not the docker-gated test): ledger created (`to_regclass` → `schema_migrations`), both fixtures recorded in order, `has()` correct, `rowCount` 2 after re-run, `information_schema` count 1, versions readable via psql; `DROP TABLE` rollback verified. ## Risks / notes - The docker-gated real-stack probe runs in the new CI job where a Docker daemon is available and skips cleanly otherwise; the job installs the frozen workspace because the probe executes the committed ledger module from the host (it resolves `pg` through the package's own dependency links). - The probe uses an isolated compose project (`-p eppp-ledger-probe`) and host port 55432 so it never collides with the compose-config suite's default-project containers or the default 5432 binding when both run on the same host. - Ledger writes are plain (no advisory locking) by design: locking is E00-S03-T04 and failure diagnostics E00-S03-T05, both explicitly out of scope here. - CI workflow change is strictly additive (new job, no existing job modified) and matches the security-reviewed #390/#391 precedent; per the review-checklist pipeline tripwire a human decision on the new merge gate may be requested. Refs #178

bot-implementer added 2 commits 2026-08-29 23:20:18 +00:00

feat: add migration ledger to database-postgres (E00-S03-T03) 7f6450410e

MigrationLedger over the package-owned pg Pool: ensure() creates the
schema_migrations table (version text PRIMARY KEY, applied_at timestamptz
NOT NULL DEFAULT now()) with idempotent DDL; record() inserts an applied
migration with a parameterized, idempotent statement (ON CONFLICT DO
NOTHING — a rerun never double-applies); has()/applied() read the ledger
back in apply order. Re-exported from the driver boundary (src/index.ts)
so no other package needs the pg driver to touch migration state.

test: lock in the migration ledger with static + real-stack probes (E00-S03-T03)

CI / Frozen lockfile install (pull_request) Successful in 56s

Details

CI / Secrets not embedded (E00-S02-T08) (pull_request) Successful in 25s

Details

CI / Database-postgres import isolation (E00-S03-T02) (pull_request) Successful in 28s

Details

CI / Migration ledger (E00-S03-T03) (pull_request) Successful in 42s

Details

CI / Compose config (E00-S03-T01) (pull_request) Successful in 29s

Details

e52b19b1e7

Static assertions on the committed ledger source (idempotent table DDL,
parameterized idempotent record, has/applied queries, driver-boundary
re-export, CI enforcement) with mutation probes proving non-vacuousness;
docker-gated real-stack probe migrates an empty database (isolated compose
project + host port) and confirms the ledger exists, the applied migrations
are recorded, and a re-run records nothing twice. New additive
database-postgres-ledger CI job gates the criterion on every PR.

bot-analyst referenced this pull request

2026-08-29 23:27:10 +00:00

[E00-S03-T03] Migration ledger created #178

bot-analyst referenced this pull request

2026-08-29 23:27:22 +00:00

[E00-S03-T03] Migration ledger created #178

kpcto referenced this pull request

2026-08-29 23:34:29 +00:00

[E00-S03-T03] Migration ledger created #178

kpcto merged commit 4552ca175e into main

2026-08-29 23:37:17 +00:00

kpcto deleted branch feature/178