Nexus/scripts/export-dev-seed.mjs

#!/usr/bin/env node
/**
 * export-dev-seed.mjs
 *
 * Dumps the current dev database into packages/db/prisma/dev-seed.sql.
 * The dump is safe to commit: passwords, TOTP secrets, SMTP credentials,
 * and webhook secrets are all sanitized before writing.
 *
 * Usage:
 *   node scripts/export-dev-seed.mjs
 *
 * Requirements:
 *   - The capakraken-postgres-1 Docker container must be running
 *   - DATABASE_URL must point to a local capakraken database
 */

import { execSync, spawnSync } from "node:child_process";
import { writeFileSync } from "node:fs";
import { resolve, dirname } from "node:path";
import { fileURLToPath } from "node:url";
import { loadWorkspaceEnv, resolveRealWorkspaceRoot } from "./load-env.mjs";

loadWorkspaceEnv();
const workspaceRoot = resolveRealWorkspaceRoot();

// ── Safety check ─────────────────────────────────────────────────────────────

const rawUrl = process.env["DATABASE_URL"];
if (!rawUrl) {
  console.error("❌  DATABASE_URL is not set.");
  process.exit(1);
}

let parsedUrl;
try {
  parsedUrl = new URL(rawUrl);
} catch {
  console.error("❌  DATABASE_URL is not a valid URL.");
  process.exit(1);
}

const host = parsedUrl.hostname;
if (!["localhost", "127.0.0.1", "::1"].includes(host)) {
  console.error(`❌  Refusing to export from non-local host: ${host}`);
  console.error("    export-dev-seed is only for local development databases.");
  process.exit(1);
}

// ── Docker container check ────────────────────────────────────────────────────

const CONTAINER = "capakraken-postgres-1";
const containerCheck = spawnSync("docker", ["inspect", "--format={{.State.Running}}", CONTAINER], {
  encoding: "utf8",
});
if (containerCheck.stdout.trim() !== "true") {
  console.error(`❌  Container ${CONTAINER} is not running.`);
  console.error("    Start it with: docker compose up -d postgres");
  process.exit(1);
}

// ── Tables to exclude entirely ────────────────────────────────────────────────

const EXCLUDE_TABLES = [
  "_prisma_migrations",
  "audit_logs",
  "active_sessions",
  "sessions",
  "accounts",
  "verification_tokens",
  "invite_tokens",
  "notifications",
  "import_batches",
  "staged_assignments",
  "staged_availability_rules",
  "staged_clients",
  "staged_projects",
  "staged_resources",
  "staged_unresolved_records",
  "staged_vacations",
];

const excludeFlags = EXCLUDE_TABLES.flatMap((t) => ["--exclude-table-data", `public.${t}`]);

// ── Run pg_dump inside the Docker container ───────────────────────────────────

const DB_USER = decodeURIComponent(parsedUrl.username) || "capakraken";
const DB_NAME = parsedUrl.pathname.replace(/^\/+/, "") || "capakraken";
const DB_PORT = parsedUrl.port || "5432";

console.log(`🔍  Exporting ${DB_USER}@${host}:${DB_PORT}/${DB_NAME} …`);

const pgDumpArgs = [
  "exec",
  CONTAINER,
  "pg_dump",
  "-U", DB_USER,
  "-d", DB_NAME,
  "--data-only",
  "--no-owner",
  "--no-acl",
  "--disable-triggers",
  ...excludeFlags,
];

const dump = spawnSync("docker", pgDumpArgs, { encoding: "utf8", maxBuffer: 256 * 1024 * 1024 });

if (dump.status !== 0) {
  console.error("❌  pg_dump failed:");
  console.error(dump.stderr);
  process.exit(1);
}

// ── Sanitize sensitive values ─────────────────────────────────────────────────

let sql = dump.stdout;

// Replace argon2id password hashes with a clearly invalid placeholder.
// The import script will update these with a real dev hash.
sql = sql.replace(/\$argon2id\$[^\t\n\\]*/g, "__DEV_PASSWORD_HASH__");

// Append sanitizing statements (TOTP, SMTP password, webhook secrets).
// These run after the COPY blocks so they don't require line-level parsing.
sql += `
-- ─── Sanitize secrets (applied after data load) ──────────────────────────────
UPDATE users SET "totpSecret" = NULL, "totpEnabled" = false;
UPDATE system_settings SET "smtpPassword" = NULL;
UPDATE webhooks SET secret = NULL;
`;

// ── Add header ────────────────────────────────────────────────────────────────

const header = `-- Nexus dev seed — exported ${new Date().toISOString()}
-- Source: ${DB_USER}@${host}:${DB_PORT}/${DB_NAME}
--
-- Excluded tables:
${EXCLUDE_TABLES.map((t) => `--   ${t}`).join("\n")}
--
-- Sanitized fields:
--   users.passwordHash     → placeholder (import-dev-seed sets "Dev123456!")
--   users.totpSecret       → NULL
--   users.totpEnabled      → false
--   system_settings.smtpPassword → NULL
--   webhooks.secret        → NULL
--
-- Import with:
--   node scripts/import-dev-seed.mjs
-- ─────────────────────────────────────────────────────────────────────────────

`;

// ── Write output ──────────────────────────────────────────────────────────────

const outPath = resolve(workspaceRoot, "packages/db/prisma/dev-seed.sql");
writeFileSync(outPath, header + sql, "utf8");

const lines = (header + sql).split("\n").length;
const sizeKb = Math.round(Buffer.byteLength(header + sql, "utf8") / 1024);
console.log(`✅  Written to packages/db/prisma/dev-seed.sql`);
console.log(`    ${lines.toLocaleString()} lines · ${sizeKb.toLocaleString()} KB`);
console.log();
console.log("Next step: commit dev-seed.sql or share it with your team.");
console.log("Import it with:  node scripts/import-dev-seed.mjs");