polaris-task-force/tests/test-db.ts

import { execSync } from "node:child_process";
import { Client } from "pg";

// Global setup/teardown processes never load .env (only setupFiles do); load
// it here so every entry point sees the same variables. dotenv does not
// override variables that are already set.
import "dotenv/config";

/**
 * Dedicated test database machinery for the vitest and Playwright suites.
 *
 * Tests never touch the development database: they run against a sibling
 * `<name>_test` database that is ensured, migrated, seeded and truncated per
 * run. All operations are idempotent, so a crashed run's leftovers are wiped
 * by the next run no matter what leaked.
 *
 * Override points:
 * - `TEST_DATABASE_URI` — use this exact connection string instead of the
 *   derived one.
 * - `TEST_DB_HARD_RESET=1` — drop and recreate the test database (fresh
 *   schema from migrations) instead of the default truncate flow.
 */

/** Tables that must survive truncation: PostGIS internals and migration journals. */
const SKIP_TRUNCATE = new Set(["spatial_ref_sys", "__drizzle_migrations", "payload_migrations"]);

/** True when truncation should be skipped in favor of a full drop/recreate. */
export function hardResetRequested(): boolean {
  return process.env.TEST_DB_HARD_RESET === "1";
}

/**
 * Derive the test connection string: `TEST_DATABASE_URI` when set, otherwise
 * the dev URI with its database name suffixed `_test`
 * (`postgres://host/ptf_app_dev` -> `postgres://host/ptf_app_test`).
 */
export function resolveTestDatabaseUri(): string {
  const override = process.env.TEST_DATABASE_URI;
  if (override) return override;

  const base = process.env.DATABASE_URI;
  if (!base) {
    throw new Error("DATABASE_URI (or TEST_DATABASE_URI) must be set to bootstrap the test database");
  }
  const url = new URL(base);
  const segments = url.pathname.split("/").filter(Boolean);
  const dbName = segments.pop();
  if (!dbName) {
    throw new Error("DATABASE_URI has no database name; cannot derive a test database");
  }
  url.pathname = [...segments, `${dbName}_test`].join("/");
  return url.toString();
}

function testDatabaseName(testUri: string): string {
  const name = new URL(testUri).pathname.split("/").filter(Boolean).pop();
  if (!name) throw new Error("Test database URI has no database name");
  return name;
}

function quotedIdentifier(identifier: string): string {
  return `"${identifier.replace(/"/g, '""')}"`;
}

/** Open a client to the server's maintenance database (`postgres`). */
async function withAdminClient(testUri: string, fn: (client: Client) => Promise<void>): Promise<void> {
  const url = new URL(testUri);
  url.pathname = "/postgres";
  const client = new Client({ connectionString: url.toString() });
  await client.connect();
  try {
    await fn(client);
  } finally {
    await client.end();
  }
}

/**
 * Create the test database when missing. With `hardReset`, terminate
 * connections, drop it and recreate from scratch instead.
 */
export async function ensureTestDatabase(testUri: string, hardReset: boolean): Promise<void> {
  const dbName = testDatabaseName(testUri);
  await withAdminClient(testUri, async (admin) => {
    const existing = await admin.query("SELECT 1 FROM pg_database WHERE datname = $1", [dbName]);
    if (hardReset && existing.rows.length > 0) {
      await admin.query(
        "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = $1 AND pid <> pg_backend_pid()",
        [dbName],
      );
      await admin.query(`DROP DATABASE ${quotedIdentifier(dbName)}`);
      existing.rows.length = 0;
    }
    if (existing.rows.length === 0) {
      await admin.query(`CREATE DATABASE ${quotedIdentifier(dbName)}`);
    }
  });
}

/**
 * Create or diff the full schema via drizzle push. The migration chain
 * assumes a schema that push originally created (its first migration alters
 * existing tables), so a fresh database is built by pushing, not migrating.
 */
export function pushSchema(testUri: string): void {
  execSync("bun run tests/push-schema.ts", {
    cwd: process.cwd(),
    env: { ...process.env, DATABASE_URI: testUri, DB_PUSH: "1" },
    stdio: "inherit",
  });
}

/**
 * Truncate every table in the public schema except migration journals and
 * PostGIS internals. Idempotent by construction: the result is the same empty
 * database no matter how many rows leaked from earlier runs.
 */
export async function truncateAllData(testUri: string): Promise<void> {
  const client = new Client({ connectionString: testUri });
  await client.connect();
  try {
    const tables = await client.query(
      "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename",
    );
    const names = tables.rows
      .map((row: { tablename: string }) => row.tablename)
      .filter((name) => !SKIP_TRUNCATE.has(name));
    if (names.length === 0) return;
    await client.query(`TRUNCATE TABLE ${names.map(quotedIdentifier).join(", ")} CASCADE`);
  } finally {
    await client.end();
  }
}

/**
 * Re-align every `<table>_id_seq` sequence with `MAX(id)` of its table.
 * Migrations backfill baseline rows with explicit ids, which leaves fresh
 * sequences behind the existing rows; without this repair the next create
 * collides and fails with `ValidationError: field is invalid: id`.
 */
export async function repairSequences(testUri: string): Promise<void> {
  const client = new Client({ connectionString: testUri });
  await client.connect();
  try {
    const sequences = await client.query(
      "SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' AND sequencename LIKE '%\\_id\\_seq'",
    );
    for (const row of sequences.rows) {
      const tableName = String(row.sequencename).replace(/_id_seq$/, "");
      const tableExists = await client.query(
        "SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = $1",
        [tableName],
      );
      if (tableExists.rows.length === 0) continue;
      await client.query(
        `SELECT setval($1::regclass, COALESCE((SELECT MAX(id) FROM ${quotedIdentifier(tableName)}), 1), true)`,
        [row.sequencename],
      );
    }
  } finally {
    await client.end();
  }
}

/** Seed the shared baseline data tests rely on (RBAC roles, Game Rules). */
export function runSeedScripts(testUri: string): void {
  // Self-executing scripts; run them as child processes so the bootstrap
  // module graph stays free of @payload-config (which vitest globalSetup and
  // Playwright's transpiler resolve differently).
  for (const script of ["src/tools/seed/seedRoles.ts", "tests/seed-baseline.ts"]) {
    execSync(`bun run ${script}`, {
      cwd: process.cwd(),
      env: { ...process.env, DATABASE_URI: testUri },
      stdio: "inherit",
    });
  }
}

/**
 * Full bootstrap, safe to call repeatedly: ensure the database (drop/recreate
 * on hard reset), apply migrations, truncate leftover data, reseed baselines
 * and repair sequences.
 */
export async function bootstrapTestDatabase(): Promise<string> {
  const testUri = resolveTestDatabaseUri();
  const hardReset = hardResetRequested();

  await ensureTestDatabase(testUri, hardReset);
  pushSchema(testUri);
  await truncateAllData(testUri);
  runSeedScripts(testUri);
  await repairSequences(testUri);

  const dbName = testDatabaseName(testUri);
  console.log(`[test-db] ready: ${dbName}${hardReset ? " (hard reset)" : ""}`);
  return testUri;
}