gongxiangp2/scripts/create-verified-production-backup.mjs

import { createHash, randomBytes } from 'node:crypto'
import { spawn } from 'node:child_process'
import { createReadStream } from 'node:fs'
import { lstat, open, readFile, readdir, rename, unlink, writeFile } from 'node:fs/promises'
import path from 'node:path'
import { fileURLToPath } from 'node:url'

import {
  assertBackupReceiptDocument,
  databaseFingerprintHash,
  requireExactCommit,
  verifiedBackupSummaryHash,
} from './safe-production-migrate.mjs'

const ROOT = path.resolve(path.dirname(fileURLToPath(import.meta.url)), '..')
const BAKED_COMMIT_FILE = path.join(ROOT, 'DEPLOYMENT_COMMIT')
const CREATE_AUTHORIZATION = 'CREATE_AND_RESTORE'
const RESET_AUTHORIZATION = 'RESET_VERIFIED_ISOLATED_TARGET'
const MAINTENANCE_CONFIRMATION = 'NO_WRITES_DURING_SNAPSHOT'
const ISOLATED_DATABASE_PATTERN = /^gongxiangp2_restore_[a-z0-9][a-z0-9_]{7,55}$/
const SUMMARY_SAMPLE_LIMIT = 32
const SUMMARY_STATEMENT_TIMEOUT_MS = 30_000
const MAX_TOOL_OUTPUT_BYTES = 1024 * 1024

const IDENTITY_SQL = String.raw`
/* verified-backup-identity-v1 */
SELECT json_build_object(
  'database', current_database(),
  'user', current_user,
  'serverAddress', COALESCE(inet_server_addr()::text, ''),
  'serverPort', COALESCE(inet_server_port()::text, '')
)::text;
`

const SUMMARY_SQL = String.raw`
/* verified-backup-summary-v1 */
WITH
materials_sample AS (
  SELECT id, "updatedAt"::text AS updated_at, "materialType" AS material_type
    FROM materials
   ORDER BY id
   LIMIT 32
),
users_sample AS (
  SELECT id, "updatedAt"::text AS updated_at, role::text AS role
    FROM users
   ORDER BY id
   LIMIT 32
),
settings_sample AS (
  SELECT id, "updatedAt"::text AS updated_at,
         "headerLogoUrl" AS header_logo_url, "loginLogoUrl" AS login_logo_url
    FROM system_settings
   ORDER BY id
   LIMIT 32
),
migrations_sample AS (
  SELECT migration_name, checksum, finished_at::text AS finished_at,
         COALESCE(rolled_back_at::text, '') AS rolled_back_at
    FROM "_prisma_migrations"
   ORDER BY migration_name
   LIMIT 32
)
SELECT jsonb_build_object(
  'schemaVersion', 1,
  'sampleLimit', 32,
  'materials', jsonb_build_object(
    'count', (SELECT count(*)::text FROM materials),
    'sampleSha256InputMd5', (SELECT md5(COALESCE(string_agg(
      concat_ws(E'\\x1f', id, updated_at, material_type), E'\\x1e' ORDER BY id
    ), '')) FROM materials_sample)
  ),
  'users', jsonb_build_object(
    'count', (SELECT count(*)::text FROM users),
    'sampleSha256InputMd5', (SELECT md5(COALESCE(string_agg(
      concat_ws(E'\\x1f', id, updated_at, role), E'\\x1e' ORDER BY id
    ), '')) FROM users_sample)
  ),
  'systemSettings', jsonb_build_object(
    'count', (SELECT count(*)::text FROM system_settings),
    'sampleSha256InputMd5', (SELECT md5(COALESCE(string_agg(
      concat_ws(E'\\x1f', id, updated_at, header_logo_url, login_logo_url), E'\\x1e' ORDER BY id
    ), '')) FROM settings_sample)
  ),
  'prismaMigrations', jsonb_build_object(
    'count', (SELECT count(*)::text FROM "_prisma_migrations"),
    'sampleSha256InputMd5', (SELECT md5(COALESCE(string_agg(
      concat_ws(E'\\x1f', migration_name, checksum, finished_at, rolled_back_at), E'\\x1e'
      ORDER BY migration_name
    ), '')) FROM migrations_sample)
  ),
  'productPackageUploadJobs', jsonb_build_object(
    'count', (SELECT count(*)::text FROM product_package_upload_jobs),
    'sampleSha256InputMd5', (SELECT md5(COALESCE(string_agg(
      concat_ws(E'\\x1f', id, status, "updated_at"::text), E'\\x1e' ORDER BY id
    ), '')) FROM (
      SELECT id, status, updated_at
        FROM product_package_upload_jobs
       ORDER BY id
       LIMIT 32
    ) AS jobs_sample)
  )
)::text;
`

const RESET_ISOLATED_SQL = String.raw`
/* verified-backup-reset-isolated-v1 */
DROP SCHEMA IF EXISTS public CASCADE;
CREATE SCHEMA public;
`

export const PURGE_RESTORE_FUNCTION_EVIDENCE_SQL = String.raw`
/* verified-backup-purge-functions-v1 */
SELECT COALESCE(jsonb_agg(jsonb_build_object(
  'signature', CASE p.proname WHEN 'purge_reference_keys' THEN 'public.purge_reference_keys(text[],boolean)' ELSE 'public.purge_json_reference_strings(jsonb)' END,
  'definition', pg_catalog.pg_get_functiondef(p.oid), 'config', to_jsonb(p.proconfig),
  'language', l.lanname, 'returnType', pg_catalog.format_type(p.prorettype, NULL),
  'volatility', p.provolatile, 'strict', p.proisstrict, 'securityDefiner', p.prosecdef, 'parallel', p.proparallel
) ORDER BY p.proname), '[]'::jsonb)::text
FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_language l ON l.oid=p.prolang
WHERE p.oid IN (pg_catalog.to_regprocedure('public.purge_reference_keys(text[],boolean)'),
  pg_catalog.to_regprocedure('public.purge_json_reference_strings(jsonb)'));
`
export const PURGE_RESTORE_SET_SQL = String.raw`
/* verified-backup-purge-set-v1 */
ALTER FUNCTION public.purge_reference_keys(text[],boolean) SET search_path TO pg_catalog, public;
ALTER FUNCTION public.purge_json_reference_strings(jsonb) SET search_path TO pg_catalog, public;
`
export const PURGE_RESTORE_RESET_SQL = String.raw`
/* verified-backup-purge-reset-v1 */
ALTER FUNCTION public.purge_reference_keys(text[],boolean) RESET search_path;
ALTER FUNCTION public.purge_json_reference_strings(jsonb) RESET search_path;
`

export function assertPurgeRestoreFunctions(rows) {
  const expected = ['public.purge_json_reference_strings(jsonb)', 'public.purge_reference_keys(text[],boolean)']
  if (!Array.isArray(rows) || rows.length !== 2 || rows.some((row, index) => row.signature !== expected[index]
    || row.language !== 'plpgsql' || row.returnType !== 'text[]' || row.volatility !== 'i'
    || row.strict !== true || row.securityDefiner !== false || row.parallel !== 'u'
    || row.config !== null || typeof row.definition !== 'string' || !row.definition.includes('CREATE OR REPLACE FUNCTION public.'))) {
    fail('Restored purge function metadata is unsupported; backup receipt refused')
  }
}

function fail(message) {
  throw new Error(message)
}

function requiredEnv(name) {
  const value = String(process.env[name] || '').trim()
  if (!value) fail(`${name} is required`)
  return value
}

function normalizedPort(value, label) {
  const port = String(value || '').trim()
  if (!/^\d{1,5}$/.test(port) || Number(port) < 1 || Number(port) > 65_535) {
    fail(`${label} must be an integer between 1 and 65535`)
  }
  return String(Number(port))
}

function exactExpectedFingerprint(prefix) {
  return {
    configuredHost: requiredEnv(`${prefix}_HOST`),
    configuredPort: normalizedPort(requiredEnv(`${prefix}_PORT`), `${prefix}_PORT`),
    configuredDatabase: requiredEnv(`${prefix}_NAME`),
    configuredUser: requiredEnv(`${prefix}_USER`),
    serverAddress: requiredEnv(`${prefix}_SERVER_ADDRESS`),
    serverPort: normalizedPort(requiredEnv(`${prefix}_SERVER_PORT`), `${prefix}_SERVER_PORT`),
  }
}

function connectionFromEnvironment(prefix) {
  const connection = {
    host: requiredEnv(`${prefix}_PGHOST`),
    port: normalizedPort(requiredEnv(`${prefix}_PGPORT`), `${prefix}_PGPORT`),
    database: requiredEnv(`${prefix}_PGDATABASE`),
    user: requiredEnv(`${prefix}_PGUSER`),
  }
  const optionalKeys = [
    'PGPASSWORD',
    'PGPASSFILE',
    'PGSSLMODE',
    'PGSSLROOTCERT',
    'PGSSLCERT',
    'PGSSLKEY',
    'PGCONNECT_TIMEOUT',
  ]
  for (const key of optionalKeys) {
    const value = String(process.env[`${prefix}_${key}`] || '').trim()
    if (value) connection[key] = value
  }
  return connection
}

function assertConfiguredConnectionMatches(connection, expected, label) {
  if (
    connection.host !== expected.configuredHost
    || connection.port !== expected.configuredPort
    || connection.database !== expected.configuredDatabase
    || connection.user !== expected.configuredUser
  ) {
    fail(`${label} configured connection does not match its explicit expected fingerprint`)
  }
}

function assertActualIdentity(actual, expected, label) {
  if (
    String(actual?.database || '') !== expected.configuredDatabase
    || String(actual?.user || '') !== expected.configuredUser
    || String(actual?.serverAddress || '') !== expected.serverAddress
    || normalizedPort(actual?.serverPort, `${label} actual server port`) !== expected.serverPort
  ) {
    fail(`${label} server identity does not match its explicit expected fingerprint`)
  }
}

export function assertProductionAndIsolationAreDistinct(production, isolated) {
  if (!ISOLATED_DATABASE_PATTERN.test(isolated.configuredDatabase)) {
    fail('Isolated database name must match gongxiangp2_restore_<unique_lowercase_suffix>')
  }
  if (production.configuredDatabase === isolated.configuredDatabase) {
    fail('Production and isolated database names must be different')
  }
  const configuredEndpointIsSame = production.configuredHost === isolated.configuredHost
    && production.configuredPort === isolated.configuredPort
  const actualServerIsSame = production.serverAddress === isolated.serverAddress
    && production.serverPort === isolated.serverPort
  if (configuredEndpointIsSame || actualServerIsSame) {
    fail('Isolated restore must use a different PostgreSQL server, not another database on production')
  }
  if (databaseFingerprintHash(production) === databaseFingerprintHash(isolated)) {
    fail('Production and isolated database fingerprints must be different')
  }
}

function pgEnvironment(connection, extra = {}) {
  const env = { ...process.env }
  for (const prefix of ['PRODUCTION_', 'ISOLATED_']) {
    for (const key of [
      'PGPASSWORD',
      'PGPASSFILE',
      'PGSSLMODE',
      'PGSSLROOTCERT',
      'PGSSLCERT',
      'PGSSLKEY',
      'PGCONNECT_TIMEOUT',
    ]) {
      delete env[`${prefix}${key}`]
    }
  }
  for (const key of [
    'PGHOST',
    'PGPORT',
    'PGDATABASE',
    'PGUSER',
    'PGPASSWORD',
    'PGPASSFILE',
    'PGSSLMODE',
    'PGSSLROOTCERT',
    'PGSSLCERT',
    'PGSSLKEY',
    'PGCONNECT_TIMEOUT',
    'PGOPTIONS',
  ]) {
    delete env[key]
  }
  Object.assign(env, {
    PGHOST: connection.host,
    PGPORT: connection.port,
    PGDATABASE: connection.database,
    PGUSER: connection.user,
    PGAPPNAME: 'gongxiangp2-verified-backup',
  })
  for (const [key, value] of Object.entries(connection)) {
    if (key.startsWith('PG')) env[key] = value
  }
  return { ...env, ...extra }
}

async function runTool(command, args, options = {}) {
  const stdoutChunks = []
  const stderrChunks = []
  let stdoutBytes = 0
  let stderrBytes = 0
  const child = spawn(command, args, {
    cwd: ROOT,
    env: options.env,
    stdio: ['ignore', 'pipe', 'pipe'],
    windowsHide: true,
  })
  child.stdout.on('data', (chunk) => {
    stdoutBytes += chunk.length
    if (stdoutBytes <= MAX_TOOL_OUTPUT_BYTES) stdoutChunks.push(chunk)
    else child.kill()
  })
  child.stderr.on('data', (chunk) => {
    stderrBytes += chunk.length
    if (stderrBytes <= MAX_TOOL_OUTPUT_BYTES) stderrChunks.push(chunk)
    else child.kill()
  })
  const result = await new Promise((resolve, reject) => {
    child.once('error', reject)
    child.once('close', (code, signal) => resolve({ code, signal }))
  })
  if (stdoutBytes > MAX_TOOL_OUTPUT_BYTES || stderrBytes > MAX_TOOL_OUTPUT_BYTES) {
    fail(`${options.label || command} exceeded the bounded output limit`)
  }
  if (result.code !== 0) {
    fail(`${options.label || command} failed with exit code ${result.code ?? `signal ${result.signal}`}`)
  }
  return {
    stdout: Buffer.concat(stdoutChunks).toString('utf8'),
    stderr: Buffer.concat(stderrChunks).toString('utf8'),
  }
}

function pgClientCommand(kind) {
  const fixtureDriver = String(process.env.VERIFIED_BACKUP_TEST_PG_DRIVER || '').trim()
  if (fixtureDriver) {
    if (process.env.NODE_ENV !== 'test') {
      fail('VERIFIED_BACKUP_TEST_PG_DRIVER is allowed only when NODE_ENV=test')
    }
    return { command: process.execPath, prefixArgs: [fixtureDriver, kind] }
  }
  const names = {
    pg_dump: 'pg_dump',
    pg_restore: 'pg_restore',
    psql: 'psql',
  }
  const command = names[kind]
  if (!command) fail(`Unknown PostgreSQL client: ${kind}`)
  return { command, prefixArgs: [] }
}

async function runPgClient(kind, args, options) {
  const client = pgClientCommand(kind)
  return runTool(client.command, [...client.prefixArgs, ...args], options)
}

async function probeClients() {
  const versions = {}
  for (const label of ['pg_dump', 'pg_restore', 'psql']) {
    const result = await runPgClient(label, ['--version'], { env: pgEnvironment({
      host: 'localhost', port: '5432', database: 'postgres', user: 'probe',
    }), label: `${label} version probe` })
    const version = result.stdout.trim()
    if (!version || version.length > 200) fail(`${label} version probe returned invalid output`)
    versions[label] = version
  }
  return versions
}

async function queryJson(connection, sql, label) {
  const result = await runPgClient('psql', [
    '--no-psqlrc',
    '--no-align',
    '--tuples-only',
    '--set=ON_ERROR_STOP=1',
    '--command',
    sql,
  ], {
    env: pgEnvironment(connection, {
      PGOPTIONS: `-c statement_timeout=${SUMMARY_STATEMENT_TIMEOUT_MS} -c lock_timeout=5000`,
    }),
    label,
  })
  const output = result.stdout.trim()
  try {
    return JSON.parse(output)
  } catch {
    fail(`${label} did not return exactly one valid JSON value`)
  }
}

async function executeSql(connection, sql, label) {
  await runPgClient('psql', [
    '--no-psqlrc',
    '--quiet',
    '--set=ON_ERROR_STOP=1',
    '--command',
    sql,
  ], {
    env: pgEnvironment(connection, {
      PGOPTIONS: '-c statement_timeout=300000 -c lock_timeout=5000',
    }),
    label,
  })
}

function assertMatchingSummaries(expected, actual, label) {
  const expectedDigest = verifiedBackupSummaryHash(expected)
  const actualDigest = verifiedBackupSummaryHash(actual)
  if (expectedDigest !== actualDigest) {
    fail(`${label} summary does not match the production snapshot`)
  }
  return expectedDigest
}

async function assertRealDirectoryWithoutSymlinks(input) {
  const resolved = path.resolve(input)
  const parsed = path.parse(resolved)
  let current = parsed.root
  const components = resolved.slice(parsed.root.length).split(path.sep).filter(Boolean)
  for (const component of components) {
    current = path.join(current, component)
    const stat = await lstat(current)
    if (stat.isSymbolicLink()) fail(`Backup output directory must not contain symlinks: ${current}`)
  }
  const stat = await lstat(resolved)
  if (!stat.isDirectory() || stat.isSymbolicLink()) {
    fail('BACKUP_OUTPUT_DIR must be an existing real directory')
  }
  const entries = await readdir(resolved)
  if (entries.length > 0) {
    fail('BACKUP_OUTPUT_DIR must be empty; use a new directory for each verified backup')
  }
  return resolved
}

async function assertAbsent(filePath, label) {
  try {
    await lstat(filePath)
    fail(`${label} already exists; use a fresh backup output directory or run identifier`)
  } catch (error) {
    if (error?.code !== 'ENOENT') throw error
  }
}

async function syncFile(filePath) {
  // Windows requires a writable handle for FlushFileBuffers; the files are
  // private, newly-created backup artifacts, so r+ is safe here.
  const handle = await open(filePath, 'r+')
  try {
    await handle.sync()
  } finally {
    await handle.close()
  }
}

async function syncDirectory(directory) {
  if (process.platform === 'win32') return
  const handle = await open(directory, 'r')
  try {
    await handle.sync()
  } finally {
    await handle.close()
  }
}

async function sha256File(filePath) {
  const hash = createHash('sha256')
  await new Promise((resolve, reject) => {
    const stream = createReadStream(filePath)
    stream.on('data', (chunk) => hash.update(chunk))
    stream.once('error', reject)
    stream.once('end', resolve)
  })
  return hash.digest('hex')
}

async function writeReceiptAtomically(receiptPath, receipt) {
  const partial = `${receiptPath}.partial-${process.pid}-${randomBytes(6).toString('hex')}`
  await assertAbsent(receiptPath, 'Backup receipt')
  await assertAbsent(partial, 'Backup receipt partial')
  let published = false
  try {
    await writeFile(partial, `${JSON.stringify(receipt, null, 2)}\n`, { flag: 'wx', mode: 0o600 })
    await syncFile(partial)
    await rename(partial, receiptPath)
    published = true
    await syncDirectory(path.dirname(receiptPath))
  } catch (error) {
    await unlink(partial).catch((cleanupError) => {
      if (cleanupError?.code !== 'ENOENT') throw cleanupError
    })
    if (published) {
      await unlink(receiptPath).catch((cleanupError) => {
        if (cleanupError?.code !== 'ENOENT') throw cleanupError
      })
      await syncDirectory(path.dirname(receiptPath)).catch(() => undefined)
    }
    throw error
  }
}

async function createDump(connection, outputDirectory, basename) {
  const finalPath = path.join(outputDirectory, `${basename}.dump`)
  const partialPath = path.join(
    outputDirectory,
    `${basename}.dump.partial-${process.pid}-${randomBytes(6).toString('hex')}`,
  )
  await assertAbsent(finalPath, 'Backup artifact')
  await assertAbsent(partialPath, 'Backup artifact partial')
  const startedAt = new Date().toISOString()
  try {
    await runPgClient('pg_dump', [
      '--format=custom',
      '--serializable-deferrable',
      '--no-owner',
      '--no-privileges',
      '--file',
      partialPath,
    ], {
      env: pgEnvironment(connection),
      label: 'pg_dump',
    })
    const partialStat = await lstat(partialPath)
    if (!partialStat.isFile() || partialStat.isSymbolicLink() || partialStat.size <= 0) {
      fail('pg_dump did not create a non-empty regular custom-format artifact')
    }
    await syncFile(partialPath)
    await rename(partialPath, finalPath)
    await syncDirectory(outputDirectory)
  } catch (error) {
    await unlink(partialPath).catch((cleanupError) => {
      if (cleanupError?.code !== 'ENOENT') throw cleanupError
    })
    throw error
  }
  const stat = await lstat(finalPath)
  if (!stat.isFile() || stat.isSymbolicLink() || stat.size <= 0) {
    fail('Final backup artifact is not a non-empty regular file')
  }
  return {
    startedAt,
    completedAt: new Date().toISOString(),
    path: finalPath,
    size: stat.size,
    sha256: await sha256File(finalPath),
  }
}

async function restoreDump(connection, artifactPath) {
  await executeSql(connection, RESET_ISOLATED_SQL, 'isolated database reset')
  const restoreSections = async sections => runPgClient('pg_restore', [
    '--exit-on-error', '--no-owner', '--no-privileges', ...sections.map(section => `--section=${section}`),
    '--dbname', connection.database, artifactPath,
  ], { env: pgEnvironment(connection), label: 'pg_restore' })
  await restoreSections(['pre-data'])
  const originalFunctions = await queryJson(connection, PURGE_RESTORE_FUNCTION_EVIDENCE_SQL, 'restored purge function metadata')
  assertPurgeRestoreFunctions(originalFunctions)
  // Only the two known legacy functions need a temporary resolver while GIN indexes
  // are recreated. pg_restore keeps its global empty search_path throughout.
  await executeSql(connection, PURGE_RESTORE_SET_SQL, 'isolated purge function resolver')
  await restoreSections(['data', 'post-data'])
  await executeSql(connection, PURGE_RESTORE_RESET_SQL, 'isolated purge function resolver reset')
  const restoredFunctions = await queryJson(connection, PURGE_RESTORE_FUNCTION_EVIDENCE_SQL, 'restored purge function final metadata')
  assertPurgeRestoreFunctions(restoredFunctions)
  if (JSON.stringify(originalFunctions) !== JSON.stringify(restoredFunctions)) fail('Restored purge function definitions or configurations changed; backup receipt refused')
}

function sanitizedAudit(mode, sourceCommit, production, isolated, versions) {
  return {
    mode,
    sourceCommit,
    productionDatabase: production,
    isolatedDatabase: isolated,
    isolatedTargetRestrictions: {
      databaseNamePattern: String(ISOLATED_DATABASE_PATTERN),
      separateConfiguredEndpoint: true,
      separateActualServer: true,
    },
    clientVersions: versions,
    writeAuthorized: mode === 'create-and-restore',
  }
}

async function main() {
  const userArgs = process.argv.slice(2)
  const create = userArgs.includes('--create-and-restore')
  if (userArgs.some((arg) => !['--audit', '--create-and-restore'].includes(arg))) {
    fail('Only --audit (default) or --create-and-restore is supported')
  }
  if (create && userArgs.includes('--audit')) fail('Choose exactly one mode')

  if (create) {
    if (process.env.ALLOW_VERIFIED_BACKUP !== CREATE_AUTHORIZATION) {
      fail(`Write mode requires ALLOW_VERIFIED_BACKUP=${CREATE_AUTHORIZATION}`)
    }
    if (process.env.ALLOW_ISOLATED_DATABASE_RESET !== RESET_AUTHORIZATION) {
      fail(`Isolated reset requires ALLOW_ISOLATED_DATABASE_RESET=${RESET_AUTHORIZATION}`)
    }
    if (process.env.CONFIRM_MAINTENANCE_WINDOW !== MAINTENANCE_CONFIRMATION) {
      fail(`Snapshot validation requires CONFIRM_MAINTENANCE_WINDOW=${MAINTENANCE_CONFIRMATION}`)
    }
  }

  const expectedCommit = requireExactCommit(requiredEnv('EXPECTED_COMMIT_SHA'), 'EXPECTED_COMMIT_SHA')
  const bakedCommit = requireExactCommit(await readFile(BAKED_COMMIT_FILE, 'utf8'), 'DEPLOYMENT_COMMIT')
  if (expectedCommit !== bakedCommit) fail('EXPECTED_COMMIT_SHA does not match the helper image commit')

  const productionConnection = connectionFromEnvironment('PRODUCTION')
  const isolatedConnection = connectionFromEnvironment('ISOLATED')
  const expectedProduction = exactExpectedFingerprint('EXPECTED_DATABASE')
  const expectedIsolated = exactExpectedFingerprint('EXPECTED_ISOLATED_DATABASE')
  assertConfiguredConnectionMatches(productionConnection, expectedProduction, 'Production')
  assertConfiguredConnectionMatches(isolatedConnection, expectedIsolated, 'Isolated')
  assertProductionAndIsolationAreDistinct(expectedProduction, expectedIsolated)

  const versions = await probeClients()
  const [productionIdentity, isolatedIdentity] = await Promise.all([
    queryJson(productionConnection, IDENTITY_SQL, 'production identity query'),
    queryJson(isolatedConnection, IDENTITY_SQL, 'isolated identity query'),
  ])
  assertActualIdentity(productionIdentity, expectedProduction, 'Production')
  assertActualIdentity(isolatedIdentity, expectedIsolated, 'Isolated')

  if (!create) {
    process.stdout.write(`${JSON.stringify(sanitizedAudit(
      'audit', expectedCommit, expectedProduction, expectedIsolated, versions,
    ), null, 2)}\n`)
    process.stdout.write('Read-only verified backup audit completed; no dump or restore was executed.\n')
    return
  }

  const outputDirectory = await assertRealDirectoryWithoutSymlinks(requiredEnv('BACKUP_OUTPUT_DIR'))
  const runId = `${new Date().toISOString().replace(/[-:.TZ]/g, '')}-${randomBytes(6).toString('hex')}`
  const basename = `gongxiangp2-${runId}`
  const receiptPath = path.join(outputDirectory, `${basename}.receipt.json`)
  await assertAbsent(receiptPath, 'Backup receipt')

  const sourceBefore = await queryJson(productionConnection, SUMMARY_SQL, 'production pre-dump summary')
  const backup = await createDump(productionConnection, outputDirectory, basename)
  const sourceAfter = await queryJson(productionConnection, SUMMARY_SQL, 'production post-dump summary')
  const sourceSummarySha256 = assertMatchingSummaries(
    sourceBefore,
    sourceAfter,
    'Production before/after pg_dump',
  )

  const restoreStartedAt = new Date().toISOString()
  await restoreDump(isolatedConnection, backup.path)
  const restoredSummary = await queryJson(isolatedConnection, SUMMARY_SQL, 'restored database summary')
  const restoredSummarySha256 = assertMatchingSummaries(
    sourceAfter,
    restoredSummary,
    'Restored database',
  )
  const restoreCompletedAt = new Date().toISOString()

  const receipt = {
    schemaVersion: 1,
    generatedBy: 'scripts/create-verified-production-backup.mjs',
    sourceCommit: expectedCommit,
    database: expectedProduction,
    backup: {
      format: 'pg_dump-custom',
      startedAt: backup.startedAt,
      completedAt: backup.completedAt,
      artifactPath: backup.path,
      artifactSize: backup.size,
      artifactSha256: backup.sha256,
    },
    restoreValidation: {
      status: 'passed',
      startedAt: restoreStartedAt,
      completedAt: restoreCompletedAt,
      artifactSha256: backup.sha256,
      databaseFingerprint: databaseFingerprintHash(expectedProduction),
      isolatedDatabase: expectedIsolated,
      isolatedDatabaseFingerprint: databaseFingerprintHash(expectedIsolated),
      verificationSummary: {
        schemaVersion: 1,
        maintenanceWindowConfirmation: MAINTENANCE_CONFIRMATION,
        statementTimeoutMs: SUMMARY_STATEMENT_TIMEOUT_MS,
        sampleLimit: SUMMARY_SAMPLE_LIMIT,
        sourceBefore: sourceBefore,
        sourceAfter: sourceAfter,
        restored: restoredSummary,
        sourceSummarySha256,
        restoredSummarySha256,
      },
    },
  }
  assertBackupReceiptDocument(receipt, {
    expectedCommit,
    database: expectedProduction,
    maxAgeMinutes: 1440,
  })
  await writeReceiptAtomically(receiptPath, receipt)
  const receiptBytes = await readFile(receiptPath)
  const receiptSha256 = createHash('sha256').update(receiptBytes).digest('hex')

  process.stdout.write(`${JSON.stringify({
    ...sanitizedAudit('create-and-restore', expectedCommit, expectedProduction, expectedIsolated, versions),
    verified: true,
    artifactPath: backup.path,
    artifactSize: backup.size,
    artifactSha256: backup.sha256,
    receiptPath,
    receiptSize: receiptBytes.length,
    receiptSha256,
    sourceSummarySha256,
  }, null, 2)}\n`)
  process.stdout.write('Production backup was created, restored to the isolated server and verified.\n')
}

if (process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url)) {
  main().catch((error) => {
    process.stderr.write(`[VERIFIED_BACKUP_ERROR] ${error instanceof Error ? error.message : String(error)}\n`)
    process.exitCode = 1
  })
}