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>
51 lines
1.8 KiB
JavaScript
51 lines
1.8 KiB
JavaScript
#!/usr/bin/env node
|
|
// Regenerate setup_sql/schema.generated.sql — a readable snapshot of the current
|
|
// pf schema, for reference only. Migrations are the source of truth; this file is
|
|
// generated so it cannot drift from the database the way a hand-maintained
|
|
// schema file does. Never edit it, and never apply it to create a database.
|
|
|
|
require('dotenv').config();
|
|
const { spawnSync } = require('child_process');
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
|
|
const out = path.join(__dirname, '..', 'setup_sql', 'schema.generated.sql');
|
|
|
|
const result = spawnSync('pg_dump', [
|
|
'--schema-only', '--no-owner', '--no-privileges',
|
|
'--schema=pf',
|
|
// per-version forecast tables are created at runtime — including them would
|
|
// make this file churn every time a version is created or dropped
|
|
'--exclude-table=pf.fc_*',
|
|
'-f', out
|
|
], {
|
|
stdio: 'inherit',
|
|
env: {
|
|
...process.env,
|
|
PGHOST: process.env.DB_HOST,
|
|
PGPORT: process.env.DB_PORT || '5432',
|
|
PGDATABASE: process.env.DB_NAME,
|
|
PGUSER: process.env.DB_USER,
|
|
PGPASSWORD: process.env.DB_PASSWORD
|
|
}
|
|
});
|
|
|
|
if (result.error) {
|
|
console.error(`pg_dump failed: ${result.error.message}`);
|
|
process.exit(1);
|
|
}
|
|
if (result.status !== 0) process.exit(result.status);
|
|
|
|
// Strip the lines pg_dump varies between runs — a random \restrict token and the
|
|
// server/client version banner — so regenerating an unchanged schema produces an
|
|
// identical file instead of a spurious diff.
|
|
const cleaned = fs.readFileSync(out, 'utf8')
|
|
.split('\n')
|
|
.filter(l => !/^\\(un)?restrict /.test(l))
|
|
.filter(l => !/^-- Dumped (from|by) /.test(l))
|
|
.join('\n')
|
|
.replace(/\n{3,}/g, '\n\n');
|
|
fs.writeFileSync(out, cleaned);
|
|
|
|
console.log(`wrote ${path.relative(process.cwd(), out)} (${cleaned.split('\n').length} lines)`);
|