wyndham-Condon/backend/scripts/db-inventory.mjs

import { createHash } from "node:crypto";
import process from "node:process";
import { createInterface } from "node:readline";
import pg from "pg";

const { Client } = pg;
const EXPECTED_DATABASE = "booking_test";
const TARGET_SCHEMA = "condon";

async function readStandardInput() {
  const lines = createInterface({
    input: process.stdin,
    terminal: false
  });
  for await (const line of lines) {
    lines.close();
    return line.trim();
  }
  throw Object.assign(new Error("Missing connection input"), { code: "MISSING_CONNECTION_INPUT" });
}

function sanitizeError(error) {
  return {
    ok: false,
    errorCode: typeof error?.code === "string" ? error.code : "INVENTORY_FAILED"
  };
}

function fingerprint(rows) {
  return createHash("sha256").update(JSON.stringify(rows)).digest("hex");
}

let client;
let transactionStarted = false;

try {
  const rawInput = await readStandardInput();
  const input = JSON.parse(rawInput);

  client = new Client({
    host: input.host,
    port: input.port,
    user: input.user,
    password: input.password,
    database: input.database,
    ssl: input.ssl ? { rejectUnauthorized: false } : undefined,
    application_name: "condon_readonly_inventory",
    connectionTimeoutMillis: 10_000,
    query_timeout: 15_000,
    options: "-c default_transaction_read_only=on -c statement_timeout=15000 -c lock_timeout=2000"
  });

  await client.connect();
  await client.query("BEGIN TRANSACTION READ ONLY");
  transactionStarted = true;

  const identityResult = await client.query(`
    SELECT
      current_database() AS database_name,
      current_user AS role_name,
      current_setting('server_version') AS server_version,
      current_setting('server_version_num') AS server_version_num,
      current_setting('transaction_read_only') AS transaction_read_only
  `);
  const identity = identityResult.rows[0];

  if (identity.database_name !== EXPECTED_DATABASE) {
    throw Object.assign(new Error("Unexpected database"), { code: "UNEXPECTED_DATABASE" });
  }

  const schemaResult = await client.query(`
    SELECT nspname AS schema_name
    FROM pg_catalog.pg_namespace
    WHERE nspname !~ '^pg_temp_'
      AND nspname !~ '^pg_toast_temp_'
    ORDER BY nspname
  `);
  const schemaNames = schemaResult.rows.map(row => row.schema_name);
  const condonExists = schemaNames.includes(TARGET_SCHEMA);

  const privilegeResult = await client.query(`
    SELECT
      has_database_privilege(current_user, current_database(), 'CONNECT') AS can_connect,
      has_database_privilege(current_user, current_database(), 'CREATE') AS can_create_schema
  `);

  const extensionResult = await client.query(`
    SELECT extname AS extension_name, extversion AS extension_version
    FROM pg_catalog.pg_extension
    ORDER BY extname
  `);

  const objectResult = await client.query(`
    SELECT
      n.nspname AS schema_name,
      c.relname AS object_name,
      c.relkind AS object_kind,
      pg_catalog.pg_get_userbyid(c.relowner) AS owner_name
    FROM pg_catalog.pg_class AS c
    JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
    WHERE n.nspname NOT LIKE 'pg_%'
      AND n.nspname <> 'information_schema'
    ORDER BY n.nspname, c.relkind, c.relname
  `);

  const routineResult = await client.query(`
    SELECT
      n.nspname AS schema_name,
      p.proname AS routine_name,
      pg_catalog.pg_get_userbyid(p.proowner) AS owner_name,
      p.prokind AS routine_kind,
      pg_catalog.pg_get_function_identity_arguments(p.oid) AS identity_arguments
    FROM pg_catalog.pg_proc AS p
    JOIN pg_catalog.pg_namespace AS n ON n.oid = p.pronamespace
    WHERE n.nspname NOT LIKE 'pg_%'
      AND n.nspname <> 'information_schema'
    ORDER BY n.nspname, p.proname, identity_arguments
  `);

  const relationCounts = Object.entries(
    objectResult.rows.reduce((counts, row) => {
      const key = `${row.schema_name}:${row.object_kind}`;
      counts[key] = (counts[key] ?? 0) + 1;
      return counts;
    }, {})
  ).map(([key, count]) => {
    const [schemaName, objectKind] = key.split(":");
    return { schemaName, objectKind, count };
  });

  const report = {
    ok: true,
    databaseVerified: identity.database_name === EXPECTED_DATABASE,
    roleVerified: identity.role_name === input.user,
    serverVersion: identity.server_version,
    serverVersionNum: Number(identity.server_version_num),
    transactionReadOnly: identity.transaction_read_only === "on",
    targetSchema: TARGET_SCHEMA,
    targetSchemaExists: condonExists,
    databasePrivileges: privilegeResult.rows[0],
    schemaNames,
    extensions: extensionResult.rows,
    relationCounts,
    existingObjectCount: objectResult.rowCount,
    existingRoutineCount: routineResult.rowCount,
    existingCatalogFingerprint: fingerprint({
      objects: objectResult.rows,
      routines: routineResult.rows
    })
  };

  process.stdout.write(`${JSON.stringify(report, null, 2)}\n`);
  process.exitCode = condonExists ? 2 : 0;
} catch (error) {
  process.stdout.write(`${JSON.stringify(sanitizeError(error))}\n`);
  process.exitCode = 1;
} finally {
  if (client) {
    if (transactionStarted) {
      try {
        await client.query("ROLLBACK");
      } catch {
        process.exitCode = 1;
      }
    }
    await client.end().catch(() => {
      process.exitCode = 1;
    });
  }
}