wishforge-ide/scripts/backup-restore.mjs
eust-w 0ef99b1e6d style: add Prettier and EditorConfig, format the codebase
Adds prettier 3.9.8 (exact) with a shared config (single quotes,
120 columns, trailing commas), .prettierignore, .editorconfig and the
`format` / `format:check` scripts, then formats all TypeScript,
JavaScript, CSS, JSON and YAML sources.

Purely mechanical: no behaviour change. Typecheck passes, the vitest
suite and the 74 IDE node tests produce the same results as before
(socket-binding tests still cannot run inside this sandbox).
Previously the longest source line was ~3000 characters; reviewing the
following semantic changes now produces readable diffs.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-19 13:32:43 +08:00

491 lines
20 KiB
JavaScript

#!/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;
});