Paul Trowbridge
d1197df7d5
setup_sql/01_schema.sql was an idempotent bootstrap script — CREATE TABLE IF NOT EXISTS plus a tail of ALTER ... ADD COLUMN IF NOT EXISTS. It had no record of what any given database had applied, which is how a branch could declare col_meta.in_grain while the running database lacked it, with nothing able to detect the mismatch. The symptom would have been a confusing 'column "in_grain" does not exist' inside an unrelated request. Migrations-only, no hand-maintained current-state file to drift: - setup_sql/migrations/*.sql applied in filename order, recorded in pf.schema_version with a checksum. Split along the schema's actual evolution, so each column is declared exactly once — 01_schema.sql had grown to declare dim_group, dim_period_col and in_grain twice each. - lib/migrations.js holds the bookkeeping, shared by the CLI and the boot check. scripts/migrate.js provides up | status | baseline. - server.js refuses to start when the database is behind, listing what is pending. This converts silent drift into a clear boot message, which was the whole point. PF_SKIP_MIGRATION_CHECK=1 bypasses. - Four integrity guards, each verified to fire: a migration modified after being applied, one recorded as applied but missing from disk, one that would apply out of order, and a re-run when already current. - No IF NOT EXISTS on new migrations. The bookkeeping already guarantees one run each, and the guards hide ordering mistakes — that is exactly why 01_schema.sql had ALTERs sitting above the CREATE TABLE they depended on, broken for anyone installing from scratch. 0004 keeps the guard only because it was applied by hand before migrations existed. Verified: replaying all four migrations into a throwaway schema reproduces the live pf schema exactly, 41 columns, column for column. schema.generated.sql is a pg_dump snapshot for reading, refreshed by npm run schema:dump. It excludes the runtime fc_* tables and strips pg_dump's random \restrict token and version banner, so regenerating an unchanged schema yields an identical file rather than a spurious diff. pf.dim_period stays out of migrations — it is a parameterised data load (fiscal year start month), not a schema change. The dev database (ubm) has been baselined at all four migrations. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
73 lines
2.8 KiB
JavaScript
73 lines
2.8 KiB
JavaScript
#!/usr/bin/env node
|
|
// Migration CLI.
|
|
//
|
|
// npm run migrate apply pending migrations
|
|
// npm run migrate:status show what is applied and what is pending
|
|
// npm run migrate:baseline record pending migrations as applied WITHOUT running
|
|
// them — for a database that already matches
|
|
//
|
|
// Pass --up-to=<filename> to baseline only through a given migration.
|
|
|
|
require('dotenv').config();
|
|
const os = require('os');
|
|
const { Client } = require('pg');
|
|
const { status, migrate, baseline } = require('../lib/migrations');
|
|
|
|
const args = process.argv.slice(2);
|
|
const command = args.find(a => !a.startsWith('--')) || 'up';
|
|
const upTo = (args.find(a => a.startsWith('--up-to=')) || '').split('=')[1] || null;
|
|
|
|
function connect() {
|
|
return new Client({
|
|
host: process.env.DB_HOST,
|
|
port: parseInt(process.env.DB_PORT) || 5432,
|
|
database: process.env.DB_NAME,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASSWORD,
|
|
ssl: false
|
|
});
|
|
}
|
|
|
|
async function showStatus(client) {
|
|
const st = await status(client);
|
|
console.log(`\n${process.env.DB_NAME} (${st.applied.length} applied, ${st.pending.length} pending)\n`);
|
|
for (const m of st.onDisk) {
|
|
const hit = st.applied.find(a => a.filename === m.filename);
|
|
const mark = !hit ? 'PENDING'
|
|
: st.modified.includes(m.filename) ? 'MODIFIED'
|
|
: 'applied';
|
|
const when = hit ? hit.applied_at.toISOString().slice(0, 19).replace('T', ' ') : '';
|
|
console.log(` ${mark.padEnd(9)} ${m.filename.padEnd(40)} ${when}`);
|
|
}
|
|
for (const f of st.missing) console.log(` MISSING ${f.padEnd(40)} recorded as applied but not on disk`);
|
|
if (st.modified.length) console.log(`\n ! modified after being applied: ${st.modified.join(', ')}`);
|
|
if (st.outOfOrder.length) console.log(` ! would apply out of order: ${st.outOfOrder.join(', ')}`);
|
|
console.log('');
|
|
return st;
|
|
}
|
|
|
|
(async () => {
|
|
const client = connect();
|
|
await client.connect();
|
|
try {
|
|
const user = `${os.userInfo().username}@${os.hostname()}`;
|
|
if (command === 'status') {
|
|
const st = await showStatus(client);
|
|
process.exitCode = (st.modified.length || st.missing.length) ? 1 : 0;
|
|
} else if (command === 'baseline') {
|
|
await baseline(client, { user, upTo });
|
|
await showStatus(client);
|
|
} else if (command === 'up') {
|
|
await migrate(client, { user });
|
|
} else {
|
|
console.error(`Unknown command "${command}" — expected up, status, or baseline.`);
|
|
process.exitCode = 2;
|
|
}
|
|
} catch (err) {
|
|
console.error(`\nmigration error: ${err.message}\n`);
|
|
process.exitCode = 1;
|
|
} finally {
|
|
await client.end();
|
|
}
|
|
})();
|