#!/usr/bin/env node import { createHash, randomUUID } from 'node:crypto'; import { spawn } from 'node:child_process'; import { createReadStream, createWriteStream } from 'node:fs'; import { chmod, mkdir, readFile, realpath, rename, stat, writeFile } from 'node:fs/promises'; import path from 'node:path'; import { pathToFileURL } from 'node:url'; import { pipeline } from 'node:stream/promises'; import pg from 'pg'; const TABLE_KEYS = Object.freeze({ tenants: ['id'], memberships: ['tenant_id', 'user_id'], entities: ['tenant_id', 'kind', 'id'], run_events: ['tenant_id', 'run_id', 'seq'], idempotency: ['tenant_id', 'key'], outbox: ['tenant_id', 'id'], usage_ledger: ['tenant_id', 'id'], audit_events: ['id'], }); const TABLES = Object.keys(TABLE_KEYS); const sha = value => createHash('sha256').update(value).digest('hex'); const quoteId = value => `"${String(value).replaceAll('"', '""')}"`; class RecoveryError extends Error { constructor(code, message) { super(message); this.code = code; } } export function parseArgs(argv) { const parsed = { mode: undefined, context: 'colima', container: 'wishforge-dev-postgres-1', envFile: '.env.local', manifest: undefined, help: false, }; for (let i = 0; i < argv.length; i++) { const arg = argv[i]; if (['--backup-only', '--drill', '--restore-verify'].includes(arg)) { if (parsed.mode) throw new RecoveryError('INVALID_FLAGS', 'Choose exactly one operation'); parsed.mode = arg.slice(2); } else if (arg === '--help') parsed.help = true; else if (['--context', '--container', '--env-file', '--manifest'].includes(arg)) { const value = argv[++i]; if (!value || value.startsWith('--')) throw new RecoveryError('INVALID_FLAGS', `Missing value for ${arg}`); parsed[ { '--context': 'context', '--container': 'container', '--env-file': 'envFile', '--manifest': 'manifest' }[arg] ] = value; } else throw new RecoveryError('INVALID_FLAGS', `Unsupported flag: ${arg}`); } if (parsed.help) return parsed; if (!parsed.mode) throw new RecoveryError('INVALID_FLAGS', 'Specify --backup-only, --drill, or --restore-verify explicitly'); if ((parsed.mode === 'restore-verify') !== Boolean(parsed.manifest)) throw new RecoveryError('INVALID_FLAGS', 'Only --restore-verify requires --manifest'); for (const key of ['context', 'container']) if (!/^[A-Za-z0-9][A-Za-z0-9_.-]{0,127}$/.test(parsed[key])) throw new RecoveryError('INVALID_FLAGS', `Invalid ${key}`); return parsed; } export function assertTestDatabase(name, marker, expectedMarker) { if ( !/^wf_restore_[a-f0-9]{32}$/.test(name) || marker !== expectedMarker || !/^wishforge-restore-drill:[a-f0-9-]{36}$/.test(expectedMarker) || name !== `wf_restore_${expectedMarker.slice('wishforge-restore-drill:'.length).replaceAll('-', '')}` ) throw new RecoveryError( 'CLEANUP_OWNERSHIP_MISMATCH', 'Refusing to remove a database without this drill ownership marker', ); } async function fileHash(file) { const hash = createHash('sha256'); for await (const chunk of createReadStream(file)) hash.update(chunk); return hash.digest('hex'); } async function privateJson(file, value) { await writeFile(file, JSON.stringify(value, null, 2) + '\n', { mode: 0o600, flag: 'wx' }); } async function docker(config, args, { outputFile, inputFile } = {}) { const child = spawn( 'docker', ['--context', config.context, 'exec', ...(inputFile ? ['-i'] : []), config.container, ...args], { shell: false, env: { PATH: process.env.PATH, HOME: process.env.HOME, LANG: 'C.UTF-8' }, stdio: ['pipe', 'pipe', 'pipe'], }, ); const diagnostics = createHash('sha256'); let diagnosticBytes = 0, captured = ''; child.stderr.on('data', chunk => { diagnosticBytes += chunk.length; diagnostics.update(chunk); }); const timeout = setTimeout(() => child.kill('SIGKILL'), 900000); const done = new Promise((resolve, reject) => { child.once('error', () => reject(new RecoveryError('DOCKER_UNAVAILABLE', 'Cannot run the selected Docker PostgreSQL tool')), ); child.once('close', code => { if (code === 0) resolve(); else reject( new RecoveryError('POSTGRES_TOOL_FAILED', `${args[0]} failed; no SQL rows or tool diagnostics are printed`), ); }); }); const output = outputFile ? pipeline(child.stdout, createWriteStream(outputFile, { flags: 'wx', mode: 0o600 })) : new Promise((resolve, reject) => { child.stdout.on('data', chunk => { captured += chunk.toString(); if (captured.length > 1024 * 1024) { child.kill('SIGKILL'); reject(new RecoveryError('OUTPUT_LIMIT', 'Unexpected PostgreSQL tool output')); } }); child.stdout.on('end', resolve); child.stdout.on('error', reject); }); const input = inputFile ? pipeline(createReadStream(inputFile), child.stdin) : Promise.resolve(child.stdin.end()); try { await Promise.all([done, output, input]); return { stdout: captured.trim(), diagnosticBytes, diagnosticsDigest: diagnostics.digest('hex') }; } finally { clearTimeout(timeout); } } async function dbClient(url) { const client = new pg.Client({ connectionString: url, connectionTimeoutMillis: 5000, application_name: 'wishforge-local-recovery-drill', }); try { await client.connect(); await client.query("SET TIME ZONE 'UTC'"); await client.query("SET DateStyle TO 'ISO, YMD'"); await client.query('SET extra_float_digits = 3'); return client; } catch (error) { await client.end().catch(() => {}); throw error; } } async function signatures(client) { const tables = {}; for (const [table, keys] of Object.entries(TABLE_KEYS)) { // Primary-key order and C collation are independent of database locale; rows never leave this hash accumulator. const order = keys .map(key => `${quoteId(key)}${key === 'seq' || (table === 'audit_events' && key === 'id') ? '' : ' COLLATE "C"'}`) .join(','); await client.query( `DECLARE wf_backup_cursor NO SCROLL CURSOR FOR SELECT to_jsonb(t)::text AS row FROM public.${quoteId(table)} t ORDER BY ${order}`, ); const hash = createHash('sha256'); let count = 0n; while (true) { const result = await client.query('FETCH FORWARD 500 FROM wf_backup_cursor'); if (!result.rows.length) break; for (const row of result.rows) { hash.update(row.row); hash.update('\n'); count++; } } await client.query('CLOSE wf_backup_cursor'); tables[table] = { rows: count.toString(), sha256: hash.digest('hex') }; } const schema = await client.query( ` SELECT 'columns' AS section,c.table_name AS name,jsonb_agg(jsonb_build_object('name',c.column_name,'position',c.ordinal_position,'type',c.udt_name,'nullable',c.is_nullable,'default',c.column_default,'identity',c.identity_generation,'generated',c.generation_expression) ORDER BY c.ordinal_position)::text AS definition FROM information_schema.columns c WHERE c.table_schema='public' AND c.table_name=ANY($1::text[]) GROUP BY c.table_name UNION ALL SELECT 'rls',c.relname,jsonb_build_object('enabled',c.relrowsecurity,'forced',c.relforcerowsecurity,'owner',pg_get_userbyid(c.relowner))::text FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND c.relname=ANY($1::text[]) UNION ALL SELECT 'policy',p.tablename||':'||p.policyname,to_jsonb(p)::text FROM pg_policies p WHERE p.schemaname='public' AND p.tablename=ANY($1::text[]) UNION ALL SELECT 'constraint',c.conrelid::regclass::text||':'||c.conname,pg_get_constraintdef(c.oid) FROM pg_constraint c JOIN pg_class t ON t.oid=c.conrelid JOIN pg_namespace n ON n.oid=t.relnamespace WHERE n.nspname='public' AND t.relname=ANY($1::text[]) UNION ALL SELECT 'index',i.tablename||':'||i.indexname,i.indexdef FROM pg_indexes i WHERE i.schemaname='public' AND i.tablename=ANY($1::text[]) UNION ALL SELECT 'trigger',t.tgrelid::regclass::text||':'||t.tgname,pg_get_triggerdef(t.oid) FROM pg_trigger t JOIN pg_class c ON c.oid=t.tgrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE NOT t.tgisinternal AND n.nspname='public' AND c.relname=ANY($1::text[]) UNION ALL SELECT 'function',p.proname||':'||pg_get_function_identity_arguments(p.oid),pg_get_functiondef(p.oid) FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='public' AND p.proname='audit_entity_change' UNION ALL SELECT 'grant',g.table_name||':'||g.grantor||':'||g.grantee||':'||g.privilege_type,g.is_grantable FROM information_schema.role_table_grants g WHERE g.table_schema='public' AND g.table_name=ANY($1::text[]) ORDER BY section,name,definition`, [TABLES], ); return { tables, schemaSha256: sha(JSON.stringify(schema.rows)) }; } async function toolIdentity(config, admin, url) { const source = ( await admin.query( "SELECT current_database() AS database,current_setting('server_version_num') AS version,system_identifier::text AS system FROM pg_control_system()", ) ).rows[0]; const tool = await docker(config, ['pg_dump', '--version']); const major = Number(/PostgreSQL\) (\d+)/.exec(tool.stdout)?.[1]); if (!major || major !== Math.floor(Number(source.version) / 10000)) throw new RecoveryError( 'POSTGRES_VERSION_MISMATCH', 'Use pg_dump from the same PostgreSQL major version as the server', ); const username = decodeURIComponent(url.username); const container = await docker(config, [ 'psql', '--no-psqlrc', '--no-password', '--username', username, '--dbname', source.database, '--tuples-only', '--no-align', '--command', 'SELECT system_identifier::text FROM pg_control_system()', ]); if (container.stdout !== source.system) throw new RecoveryError( 'POSTGRES_CLUSTER_MISMATCH', 'Docker tools and DATABASE_ADMIN_URL do not refer to the same cluster', ); return { ...source, username, toolVersion: tool.stdout }; } async function backup(config, url, root, identity) { const started = Date.now(), id = randomUUID(), base = `backup-${new Date().toISOString().replace(/[-:.]/g, '')}-${id}`; const dumpFile = path.join(root, `${base}.dump`), partial = `${dumpFile}.partial`, manifestFile = path.join(root, `${base}.manifest.json`); const source = await dbClient(url.toString()); let manifest; try { await source.query('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY'); const snapshot = (await source.query('SELECT pg_export_snapshot() AS id,transaction_timestamp() AS captured_at')) .rows[0]; const expected = await signatures(source); const receipt = await docker( config, [ 'pg_dump', '--no-password', '--username', identity.username, '--dbname', identity.database, '--format=custom', `--snapshot=${snapshot.id}`, '--lock-wait-timeout=30000', ], { outputFile: partial }, ); await source.query('COMMIT'); await rename(partial, dumpFile); manifest = { schemaVersion: 1, id, createdAt: new Date().toISOString(), snapshotCapturedAt: snapshot.captured_at.toISOString(), source: { database: identity.database, serverVersion: identity.version, systemIdentifier: identity.system }, toolVersion: identity.toolVersion, archive: { file: path.basename(dumpFile), bytes: (await stat(dumpFile)).size, sha256: await fileHash(dumpFile) }, ...expected, backupMilliseconds: Date.now() - started, diagnosticBytes: receipt.diagnosticBytes, diagnosticsDigest: receipt.diagnosticsDigest, }; await privateJson(manifestFile, manifest); } finally { await source.end(); } return { manifest, manifestFile, dumpFile }; } async function readManifest(filename, root) { const resolved = await realpath(filename), allowed = await realpath(root); if (!resolved.startsWith(`${allowed}${path.sep}`)) throw new RecoveryError( 'MANIFEST_SCOPE', 'Restore verification accepts only a manifest in the private local backup directory', ); const m = JSON.parse(await readFile(resolved, 'utf8')); if ( m.schemaVersion !== 1 || !/^[\w.-]+\.dump$/.test(m.archive?.file) || !/^[a-f0-9]{64}$/.test(m.archive?.sha256) || !/^[a-f0-9]{64}$/.test(m.schemaSha256) || TABLES.some(t => !/^\d+$/.test(m.tables?.[t]?.rows) || !/^[a-f0-9]{64}$/.test(m.tables?.[t]?.sha256)) ) throw new RecoveryError('INVALID_MANIFEST', 'Manifest is incomplete or unsupported'); const dumpFile = await realpath(path.join(path.dirname(resolved), m.archive.file)); if (!dumpFile.startsWith(`${allowed}${path.sep}`) || (await fileHash(dumpFile)) !== m.archive.sha256) throw new RecoveryError('ARCHIVE_INTEGRITY_FAILED', 'Archive scope or SHA-256 verification failed'); return { manifest: m, manifestFile: resolved, dumpFile }; } async function restoreVerify(config, url, admin, root, identity, input) { const started = Date.now(), id = randomUUID(), database = `wf_restore_${id.replaceAll('-', '')}`, marker = `wishforge-restore-drill:${id}`; const evidenceFile = path.join(root, `restore-${id}.evidence.json`); let created = false, oid, restoreClient; let failure; const evidence = { schemaVersion: 1, drillId: id, startedAt: new Date().toISOString(), manifestFile: path.basename(input.manifestFile), archiveSha256: input.manifest.archive.sha256, testDatabase: database, verified: false, cleanupVerified: false, }; try { // No target override, --clean, --create, or connection to an existing database is allowed. await admin.query(`CREATE DATABASE ${quoteId(database)} WITH TEMPLATE template0`); created = true; await admin.query(`COMMENT ON DATABASE ${quoteId(database)} IS '${marker}'`); oid = (await admin.query('SELECT oid FROM pg_database WHERE datname=$1', [database])).rows[0]?.oid; await admin.query(`REVOKE CONNECT ON DATABASE ${quoteId(database)} FROM PUBLIC`); const restoreStarted = Date.now(); const receipt = await docker( config, [ 'pg_restore', '--no-password', '--username', identity.username, '--dbname', database, '--exit-on-error', '--single-transaction', ], { inputFile: input.dumpFile }, ); evidence.restoreMilliseconds = Date.now() - restoreStarted; evidence.diagnosticBytes = receipt.diagnosticBytes; evidence.diagnosticsDigest = receipt.diagnosticsDigest; const target = new URL(url); target.pathname = `/${database}`; restoreClient = await dbClient(target.toString()); await restoreClient.query('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY'); const verifyStarted = Date.now(), actual = await signatures(restoreClient); const mismatches = TABLES.filter( t => actual.tables[t].rows !== input.manifest.tables[t].rows || actual.tables[t].sha256 !== input.manifest.tables[t].sha256, ); evidence.tableChecks = Object.fromEntries( TABLES.map(t => [t, { matched: !mismatches.includes(t), ...actual.tables[t] }]), ); evidence.schemaMatched = actual.schemaSha256 === input.manifest.schemaSha256; const sequence = (await restoreClient.query('SELECT last_value::text,is_called FROM public.audit_events_id_seq')) .rows[0]; const maxId = BigInt( (await restoreClient.query('SELECT COALESCE(MAX(id),0)::text AS id FROM public.audit_events')).rows[0].id, ); evidence.auditSequenceSafe = BigInt(sequence.last_value) + (sequence.is_called ? 1n : 0n) > maxId; await restoreClient.query('COMMIT'); evidence.verifyMilliseconds = Date.now() - verifyStarted; if (mismatches.length || !evidence.schemaMatched || !evidence.auditSequenceSafe) throw new RecoveryError( 'RESTORE_MISMATCH', 'Restored row digests, schema/security definitions, or audit sequence did not match the snapshot', ); evidence.verified = true; } catch (error) { failure = error; evidence.failureCode = /^[A-Z0-9_]+$/.test(error.code ?? '') ? error.code : 'RESTORE_FAILED'; } finally { await restoreClient?.end().catch(() => {}); if (created) { try { const observed = ( await admin.query( "SELECT oid,shobj_description(oid,'pg_database') AS marker FROM pg_database WHERE datname=$1", [database], ) ).rows[0]; assertTestDatabase(database, observed?.marker, marker); if (observed.oid !== oid) throw new RecoveryError('CLEANUP_OWNERSHIP_MISMATCH', 'Test database identity changed; nothing was dropped'); await admin.query(`DROP DATABASE ${quoteId(database)} WITH (FORCE)`); evidence.cleanupVerified = !(await admin.query('SELECT 1 FROM pg_database WHERE datname=$1', [database])) .rowCount; } catch (error) { evidence.cleanupFailureCode = error.code ?? 'CLEANUP_FAILED'; failure ??= error; } } evidence.completedAt = new Date().toISOString(); evidence.totalMilliseconds = Date.now() - started; await privateJson(evidenceFile, evidence); } if (failure) throw new RecoveryError( evidence.failureCode ?? evidence.cleanupFailureCode ?? 'RESTORE_FAILED', `Restore drill failed; private evidence retained at ${evidenceFile}`, ); return { evidence, evidenceFile }; } export async function main(argv = process.argv.slice(2)) { const config = parseArgs(argv); if (config.help) { console.log( 'Usage: node scripts/backup-restore.mjs (--backup-only | --drill | --restore-verify --manifest PATH) [--env-file PATH] [--context colima] [--container wishforge-dev-postgres-1]\nRestore always creates and removes a uniquely named test database. Existing databases cannot be overwritten.', ); return; } try { process.loadEnvFile(config.envFile); } catch (error) { if (error.code !== 'ENOENT' || !process.env.DATABASE_ADMIN_URL) throw new RecoveryError('CONFIG_UNAVAILABLE', 'Private environment configuration is unavailable'); } let url; try { url = new URL(process.env.DATABASE_ADMIN_URL); } catch { throw new RecoveryError('CONFIG_UNAVAILABLE', 'DATABASE_ADMIN_URL is required'); } if ( !['postgres:', 'postgresql:'].includes(url.protocol) || !['localhost', '127.0.0.1', '[::1]'].includes(url.hostname) || !url.username || !url.pathname.slice(1) ) throw new RecoveryError( 'LOCAL_DATABASE_REQUIRED', 'This drill requires an explicitly configured local PostgreSQL admin URL', ); const root = path.resolve('.wishforge/backups'); await mkdir(root, { recursive: true, mode: 0o700 }); await chmod(root, 0o700); const admin = await dbClient(url.toString()); try { const identity = await toolIdentity(config, admin, url); const input = config.mode === 'restore-verify' ? await readManifest(config.manifest, root) : await backup(config, url, root, identity); if (config.mode === 'backup-only') { console.log( JSON.stringify({ status: 'backup_ready', manifest: input.manifestFile, archive: input.dumpFile, backupMilliseconds: input.manifest.backupMilliseconds, }), ); return; } const result = await restoreVerify(config, url, admin, root, identity, input); console.log( JSON.stringify({ status: 'verified', manifest: input.manifestFile, archive: input.dumpFile, evidence: result.evidenceFile, backupMilliseconds: input.manifest.backupMilliseconds, restoreMilliseconds: result.evidence.restoreMilliseconds, verifyMilliseconds: result.evidence.verifyMilliseconds, totalDrillMilliseconds: result.evidence.totalMilliseconds, coreTablesVerified: TABLES.length, testDatabaseRemoved: result.evidence.cleanupVerified, }), ); } finally { await admin.end(); } } if (process.argv[1] && import.meta.url === pathToFileURL(path.resolve(process.argv[1])).href) main().catch(error => { console.error( JSON.stringify({ status: 'failed', code: /^[A-Z0-9_]+$/.test(error.code ?? '') ? error.code : 'RECOVERY_FAILED', message: error instanceof RecoveryError ? error.message : 'Database operation failed; credentials and SQL payloads are not printed', }), ); process.exitCode = 1; });