Files
PersonalBlog/tests/database-postgres-ledger.test.mjs
implementer e52b19b1e7
CI / Frozen lockfile install (pull_request) Successful in 56s
CI / Secrets not embedded (E00-S02-T08) (pull_request) Successful in 25s
CI / Database-postgres import isolation (E00-S03-T02) (pull_request) Successful in 28s
CI / Migration ledger (E00-S03-T03) (pull_request) Successful in 42s
CI / Compose config (E00-S03-T01) (pull_request) Successful in 29s
test: lock in the migration ledger with static + real-stack probes (E00-S03-T03)
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.
2026-08-29 23:20:00 +00:00

497 lines
20 KiB
JavaScript

/**
* Migration ledger test — locks in the [E00-S03-T03] migration ledger for the
* workspace.
*
* Acceptance criteria covered (each test fails without the committed state):
* - "migration ledger is created" → the committed
* `packages/database-postgres/src/ledger.ts` defines the ledger table
* (`schema_migrations`, `version` primary key + `applied_at`) and a
* `MigrationLedger.ensure()` that creates it with idempotent DDL; when a
* Docker daemon + Compose plugin are available, the real-stack probe
* migrates an empty database and confirms the ledger exists (the issue's
* test plan) and that a second run records nothing twice (the story's
* "second migration run is idempotent").
* - "applied migrations are recorded in the ledger" → `record()` inserts
* the migration version with an idempotent, parameterized statement
* (`ON CONFLICT (version) DO NOTHING`, `$1` placeholder — the version is
* never interpolated into SQL), and `has()`/`applied()` read the ledger
* back; the real-stack probe records two migrations and confirms they are
* in the ledger.
* - the ledger is part of the driver boundary: `src/index.ts` re-exports
* `MigrationLedger`, so no other package needs the `pg` driver to touch
* migration state.
*
* Run: `node --test tests/database-postgres-ledger.test.mjs`
* (node:test — built into Node >= 18; no dependencies, lockfile untouched.)
*/
import test from 'node:test';
import assert from 'node:assert/strict';
import { readFileSync, existsSync, writeFileSync, rmSync } from 'node:fs';
import { spawnSync } from 'node:child_process';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
const REPO_ROOT = path.resolve(path.dirname(fileURLToPath(import.meta.url)), '..');
const read = (relPath) => readFileSync(path.join(REPO_ROOT, relPath), 'utf8');
/** The committed ledger module and the driver boundary that re-exports it. */
const LEDGER_SRC = 'packages/database-postgres/src/ledger.ts';
const INDEX_SRC = 'packages/database-postgres/src/index.ts';
/** The root test glob (root `scripts.test`, E00-S01-T12) that runs every suite. */
const ROOT_TEST_GLOB = 'tests/**/*.test.mjs';
/** The CI job that gates the ledger criterion on every PR. */
const CI_JOB = 'database-postgres-ledger';
/** Ledger fixtures used by the real-stack probe (distinct, ordered versions). */
const FIRST_MIGRATION = '2026-08-30_001_initial_schema';
const SECOND_MIGRATION = '2026-08-30_002_posts';
const NEVER_MIGRATION = '2026-08-30_999_never';
// ---------------------------------------------------------------------------
// Static assertions on the committed ledger source
// ---------------------------------------------------------------------------
/**
* Asserts the ledger table is created by idempotent DDL with the documented
* shape: `CREATE TABLE IF NOT EXISTS schema_migrations` keyed on `version`
* (primary key) and stamping `applied_at`. Fails fast on a missing table
* definition; the mutation probes below prove the assertions are non-vacuous.
*/
function assertLedgerTableDDL(src) {
assert.match(
src,
/CREATE TABLE IF NOT EXISTS/,
'the ledger DDL must be idempotent (CREATE TABLE IF NOT EXISTS)',
);
assert.match(
src,
/schema_migrations/,
'the ledger table must be named schema_migrations',
);
assert.match(
src,
/version\s+text\s+PRIMARY KEY/,
'the ledger must key rows on the migration version (version text PRIMARY KEY)',
);
assert.match(
src,
/applied_at\s+timestamptz\s+NOT NULL\s+DEFAULT now\(\)/,
'the ledger must record when a migration was applied (applied_at timestamptz NOT NULL DEFAULT now())',
);
}
/** Asserts record() is idempotent: re-recording a version must not duplicate it. */
function assertIdempotentRecord(src) {
assert.match(
src,
/ON CONFLICT \(version\) DO NOTHING/,
'record() must be idempotent (ON CONFLICT (version) DO NOTHING)',
);
}
/** Asserts record() binds the version as a parameter — never interpolates it. */
function assertParameterizedRecord(src) {
assert.match(
src,
/VALUES \(\$1\)/,
'record() must bind the migration version with a $1 placeholder',
);
assert.match(
src,
/\[\s*version\s*\]/,
'record() must pass the version as the bound-parameter array ([version])',
);
assert.doesNotMatch(
src,
/\$\{version\}/,
'the migration version must never be interpolated into the SQL',
);
}
/** Asserts has()/applied() read the ledger back through the pool. */
function assertLedgerQueries(src) {
assert.match(
src,
/WHERE version = \$1/,
'has() must look up the version with a $1 placeholder',
);
assert.match(
src,
/SELECT version FROM/,
'applied() must select the recorded versions',
);
assert.match(
src,
/ORDER BY applied_at, version/,
'applied() must return versions in apply order (oldest first)',
);
}
/** Asserts the driver boundary re-exports the ledger from the package entrypoint. */
function assertBoundaryReexport(src) {
assert.match(
src,
/export \{ MigrationLedger, MIGRATION_LEDGER_TABLE \} from '\.\/ledger\.js'/,
'the driver boundary must re-export the ledger (src/index.ts → ./ledger.js)',
);
}
// ---------------------------------------------------------------------------
// Docker probe helpers (integration test skips cleanly without Docker)
// ---------------------------------------------------------------------------
function run(cmd, args, opts = {}) {
return spawnSync(cmd, args, {
encoding: 'utf8',
timeout: 600_000,
...opts,
});
}
/** True when the `docker` CLI with the Compose plugin is on PATH. */
function dockerComposeAvailable() {
try {
return run('docker', ['compose', 'version'], { timeout: 15_000 }).status === 0;
} catch {
return false;
}
}
/** True when a reachable Docker daemon exists. */
function dockerDaemonAvailable() {
try {
return run('docker', ['info'], { timeout: 15_000 }).status === 0;
} catch {
return false;
}
}
/** Parses `docker compose ps --format json` (JSON array or one object per line). */
function parsePsJson(stdout) {
const text = String(stdout).trim();
if (!text) return [];
try {
const parsed = JSON.parse(text);
return Array.isArray(parsed) ? parsed : [parsed];
} catch {
return text
.split('\n')
.map((line) => line.trim())
.filter(Boolean)
.map((line) => JSON.parse(line));
}
}
/** Tolerant field lookup across compose ps JSON shapes. */
function field(container, ...names) {
for (const name of names) {
if (container[name] !== undefined) return container[name];
}
return undefined;
}
/**
* Polls `docker compose ps` until the db container of the given project
* reports healthy (or the deadline passes), so the probe never races a
* still-booting database.
*/
function waitForDbHealthy(project, deadlineMs = 60_000) {
const deadline = Date.now() + deadlineMs;
let last = '';
while (Date.now() < deadline) {
const ps = run('docker', ['compose', '-p', project, 'ps', '--format', 'json'], {
cwd: REPO_ROOT,
timeout: 15_000,
});
if (ps.status === 0) {
last = ps.stdout;
const db = parsePsJson(ps.stdout).find((c) => field(c, 'Service', 'service') === 'db');
if (db && /healthy/i.test(String(field(db, 'Health', 'health') ?? ''))) return;
}
run(process.execPath, ['-e', 'setTimeout(() => {}, 1000)']); // db still booting — retry
}
throw new Error(`the db container did not become healthy within ${deadlineMs}ms (last ps: "${last.trim()}")`);
}
/**
* The host-side probe body: exercises the committed ledger module against a
* real database (DATABASE_URL) exactly as the migration runner will — ensure
* the ledger exists, record migrations, read them back, and re-run to prove
* idempotency. Written to a temp file inside `packages/database-postgres/` so
* `pg` resolves through the package's own dependency links, then removed.
*/
const PROBE_SOURCE = `
import { Pool } from 'pg';
import { MigrationLedger, MIGRATION_LEDGER_TABLE } from './src/ledger.ts';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
try {
const ledger = new MigrationLedger(pool);
// "migrate an empty database and confirm the ledger exists": ensure()
// against the fresh database must create the schema_migrations table.
await ledger.ensure();
const tableClass = await pool.query('SELECT to_regclass($1) AS cls', ['public.' + MIGRATION_LEDGER_TABLE]);
// "applied migrations are recorded in the ledger".
await ledger.record('${FIRST_MIGRATION}');
await ledger.record('${SECOND_MIGRATION}');
const applied = await ledger.applied();
const hasFirst = await ledger.has('${FIRST_MIGRATION}');
const hasUnknown = await ledger.has('${NEVER_MIGRATION}');
// Second run must be idempotent: ensure() + record() again, nothing twice.
await ledger.ensure();
await ledger.record('${FIRST_MIGRATION}');
const count = await pool.query('SELECT count(*)::int AS n FROM ' + MIGRATION_LEDGER_TABLE);
console.log('LEDGER_PROBE_RESULT ' + JSON.stringify({
tableClass: tableClass.rows[0] ? tableClass.rows[0].cls : null,
applied,
hasFirst,
hasUnknown,
rowCount: count.rows[0].n,
}));
} finally {
await pool.end();
}
`;
// ---------------------------------------------------------------------------
// Criterion tests
// ---------------------------------------------------------------------------
test('the ledger module exists in the driver-owner package and defines the schema_migrations table', () => {
assert.ok(existsSync(path.join(REPO_ROOT, LEDGER_SRC)), `committed ${LEDGER_SRC} must exist`);
const src = read(LEDGER_SRC);
assert.match(src, /export class MigrationLedger/, 'the ledger module must export the MigrationLedger class');
assert.match(
src,
/export const MIGRATION_LEDGER_TABLE = 'schema_migrations'/,
'the ledger module must declare the schema_migrations table name constant',
);
assertLedgerTableDDL(src);
});
test('the ledger is created by idempotent DDL (migration ledger is created)', () => {
assertLedgerTableDDL(read(LEDGER_SRC));
});
test('record() records applied migrations with an idempotent, parameterized insert', () => {
const src = read(LEDGER_SRC);
assertIdempotentRecord(src);
assertParameterizedRecord(src);
});
test('has() and applied() read the ledger back through the pool', () => {
assertLedgerQueries(read(LEDGER_SRC));
});
test('the driver boundary re-exports the ledger from the package entrypoint', () => {
const src = read(INDEX_SRC);
assert.match(
src,
/from '\.\/ledger\.js'/,
'the driver boundary must import the ledger module (from \'./ledger.js\')',
);
assertBoundaryReexport(src);
});
test('the ledger criterion is enforced in CI', () => {
// Picked up by the root test command (root `scripts.test` glob).
const scripts = JSON.parse(read('package.json')).scripts ?? {};
assert.equal(
scripts.test,
`node --test "${ROOT_TEST_GLOB}"`,
`root scripts.test must run the "${ROOT_TEST_GLOB}" glob so this suite runs with the rest`,
);
// And a dedicated CI job gates it on every PR.
const workflow = read('.gitea/workflows/ci.yml');
assert.ok(
workflow.includes(`node --test tests/database-postgres-ledger.test.mjs`),
`CI must run the ledger suite (job "${CI_JOB}") on every PR`,
);
});
// ---------------------------------------------------------------------------
// Real-stack probe — the issue's test plan against a real empty database
// ---------------------------------------------------------------------------
const DOCKER_COMPOSE = dockerComposeAvailable();
const DOCKER_DAEMON = dockerDaemonAvailable();
/** True when this Node can execute the committed `.ts` ledger module (>= 23.6, type stripping). */
const TS_STRIPPING = (() => {
const [major, minor] = process.versions.node.split('.').map(Number);
return major > 23 || (major === 23 && minor >= 6);
})();
// An isolated compose project + non-default host port so this probe never
// collides with the compose-config suite's default-project containers or the
// default 5432 binding when both run on the same host.
const COMPOSE_PROJECT = 'eppp-ledger-probe';
const POSTGRES_HOST_PORT = '55432';
const DATABASE_URL = `postgres://eppp:eppp@127.0.0.1:${POSTGRES_HOST_PORT}/eppp`;
test('migrating an empty database creates the ledger and records applied migrations (real stack)', { skip: !DOCKER_COMPOSE || !DOCKER_DAEMON || !TS_STRIPPING }, () => {
// The issue's test plan: "migrate an empty database and confirm the ledger
// exists". The probe starts the committed compose `db` service (its own
// project + host port), drops any leftover ledger table for a clean slate,
// runs the committed ledger module against the empty database, and asserts
// the ledger exists, the applied migrations are recorded, and a re-run
// records nothing twice. Rollback: drop the ledger table (issue rollback
// note) and `docker compose -p eppp-ledger-probe down`.
const composeEnv = { ...process.env, POSTGRES_PORT: POSTGRES_HOST_PORT };
const compose = (args, opts = {}) =>
run('docker', ['compose', '-p', COMPOSE_PROJECT, ...args], { cwd: REPO_ROOT, env: composeEnv, ...opts });
const execPsql = (args, opts = {}) =>
compose(['exec', '-T', 'db', 'psql', '-U', 'eppp', '-d', 'eppp', ...args], opts);
const probeFile = path.join(REPO_ROOT, 'packages/database-postgres', `.ledger-probe-${process.pid}.mjs`);
try {
const up = compose(['up', '-d', 'db'], { timeout: 180_000 });
assert.equal(
up.status,
0,
`"docker compose up -d db" must exit 0:\n${(up.stdout || '')}\n${(up.stderr || '')}`.trim(),
);
waitForDbHealthy(COMPOSE_PROJECT);
// Clean slate (also recovers from a previously interrupted run): drop the
// ledger table so the database is empty of migration state.
const drop = execPsql(['-v', 'ON_ERROR_STOP=1', '-c', 'DROP TABLE IF EXISTS schema_migrations;']);
assert.equal(
drop.status,
0,
`dropping a leftover ledger table must succeed:\n${(drop.stdout || '')}\n${(drop.stderr || '')}`.trim(),
);
// Run the committed ledger module against the empty database.
writeFileSync(probeFile, PROBE_SOURCE);
const probe = run(process.execPath, [probeFile], {
cwd: REPO_ROOT,
env: { ...process.env, DATABASE_URL },
timeout: 60_000,
});
assert.equal(
probe.status,
0,
`the ledger probe must exit 0:\n${(probe.stdout || '')}\n${(probe.stderr || '')}`.trim(),
);
const resultLine = (probe.stdout || '')
.split('\n')
.map((l) => l.trim())
.find((l) => l.startsWith('LEDGER_PROBE_RESULT'));
assert.ok(resultLine, `the ledger probe must report a result line (stdout: "${(probe.stdout || '').trim()}")`);
const result = JSON.parse(resultLine.slice('LEDGER_PROBE_RESULT'.length).trim());
// "migration ledger is created": to_regclass resolves the table.
assert.ok(
result.tableClass,
`ensure() must create the schema_migrations table on an empty database (to_regclass got: ${JSON.stringify(result.tableClass)})`,
);
// "applied migrations are recorded in the ledger", in apply order.
assert.deepEqual(
result.applied,
[FIRST_MIGRATION, SECOND_MIGRATION],
'the ledger must list the applied migrations in apply order',
);
assert.equal(result.hasFirst, true, 'has() must report a recorded migration');
assert.equal(result.hasUnknown, false, 'has() must report false for an unrecorded migration');
// Idempotency: a second ensure() + record() run must not double-apply.
assert.equal(
result.rowCount,
2,
`re-running ensure() + record() must not duplicate ledger rows (got ${result.rowCount}, expected 2)`,
);
// Cross-check from inside the database container that the ledger exists
// and the applied migrations are recorded (issue test plan).
const tableCount = execPsql([
'-tA', '-c',
"SELECT count(*) FROM information_schema.tables WHERE table_name = 'schema_migrations';",
]);
assert.equal(
tableCount.status,
0,
`ledger existence query via psql must succeed:\n${(tableCount.stdout || '')}\n${(tableCount.stderr || '')}`.trim(),
);
assert.match(
tableCount.stdout.trim(),
/^1$/m,
`the schema_migrations table must exist in the database (count got: "${tableCount.stdout.trim()}")`,
);
const versions = execPsql(['-tA', '-c', 'SELECT version FROM schema_migrations ORDER BY version;']);
assert.equal(
versions.status,
0,
`ledger rows query via psql must succeed:\n${(versions.stdout || '')}\n${(versions.stderr || '')}`.trim(),
);
assert.match(versions.stdout, new RegExp(FIRST_MIGRATION.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')), 'the first migration must be recorded in the ledger');
assert.match(versions.stdout, new RegExp(SECOND_MIGRATION.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')), 'the second migration must be recorded in the ledger');
} finally {
// Rollback note from the issue: drop the ledger table to reset migration
// state, then tear down the isolated project.
try {
execPsql(['-c', 'DROP TABLE IF EXISTS schema_migrations;']);
} catch {
// container may already be gone — the compose down below still cleans up
}
compose(['down', '-v'], { timeout: 120_000 });
rmSync(probeFile, { force: true });
}
});
// ---------------------------------------------------------------------------
// Non-vacuous probes — the assertions above really do fail on violations
// ---------------------------------------------------------------------------
test('removing the version primary key makes the ledger-table criterion fail (mutation probe)', () => {
const src = read(LEDGER_SRC);
const withoutPk = src.replace(/PRIMARY KEY/, '');
assert.notEqual(withoutPk, src, 'the mutation must actually remove the PRIMARY KEY');
assert.throws(() => assertLedgerTableDDL(withoutPk), /PRIMARY KEY/);
});
test('removing the idempotent create makes the ledger-creation criterion fail (mutation probe)', () => {
const src = read(LEDGER_SRC);
const withoutIfNotExists = src.replace(/CREATE TABLE IF NOT EXISTS/, 'CREATE TABLE');
assert.notEqual(withoutIfNotExists, src, 'the mutation must actually drop IF NOT EXISTS');
assert.throws(() => assertLedgerTableDDL(withoutIfNotExists), /IF NOT EXISTS/);
});
test('removing ON CONFLICT makes the idempotent-record criterion fail (mutation probe)', () => {
const src = read(LEDGER_SRC);
const withoutConflict = src.replace(/ON CONFLICT \(version\) DO NOTHING/, '');
assert.notEqual(withoutConflict, src, 'the mutation must actually remove ON CONFLICT DO NOTHING');
assert.throws(() => assertIdempotentRecord(withoutConflict), /ON CONFLICT/);
});
test('interpolating the version into the SQL makes the parameterized criterion fail (mutation probe)', () => {
const src = read(LEDGER_SRC);
const interpolated = src.replace(/VALUES \(\$1\)/, "VALUES ('${version}')");
assert.notEqual(interpolated, src, 'the mutation must actually replace the $1 placeholder');
assert.throws(() => assertParameterizedRecord(interpolated), /\$1 placeholder/);
});
test('dropping the ledger re-export from the boundary fails the boundary criterion (mutation probe)', () => {
const src = read(INDEX_SRC);
const withoutReexport = src.replace(/export \{ MigrationLedger, MIGRATION_LEDGER_TABLE \} from '\.\/ledger\.js';\n/, '');
assert.notEqual(withoutReexport, src, 'the mutation must actually remove the ledger re-export');
assert.throws(() => assertBoundaryReexport(withoutReexport), /re-export/);
});
test('a placeholder ledger module fails the ledger-table criterion (mutation probe)', () => {
assert.throws(
() => assertLedgerTableDDL('export class MigrationLedger {}\n'),
/CREATE TABLE IF NOT EXISTS/,
);
});