src/lib/db/migrations.integration.test.ts

Linked repository file from this build.

import { isReviewKey } from "@/features/reviews/review-key";
import { expect, test } from "bun:test";
import { Client } from "pg";
import { migrationFiles } from "../../../scripts/db/migrations";

const integration = process.env.QC_DB_INTEGRATION === "1" ? test : test.skip;
integration(
  "migration CLI diagnoses and rolls back failures, upgrades old data, and detects schema drift",
  async () => {
    const url = new URL(process.env.DATABASE_URL!);
    if (!["localhost", "127.0.0.1"].includes(url.hostname))
      throw new Error("Migration integration tests require local PostgreSQL.");
    const database = `qccrm_migrations_${crypto.randomUUID().replaceAll("-", "")}`;
    const admin = new Client({ connectionString: url.toString() });
    await admin.connect();
    let client: Client | undefined;
    try {
      await admin.query(`CREATE DATABASE "${database}"`);
      url.pathname = `/${database}`;
      client = new Client({ connectionString: url.toString() });
      await client.connect();
      await client.query(
        "create schema drizzle; create table drizzle.__drizzle_migrations (id serial primary key, hash text not null, created_at bigint)",
      );
      for (const migration of migrationFiles().slice(0, 6)) {
        for (const sql of migration.sql) await client.query(sql);
        await client.query(
          "insert into drizzle.__drizzle_migrations(hash, created_at) values ($1, $2)",
          [migration.hash, migration.folderMillis],
        );
      }
      await client.query(
        // The report projection stands in for the backfill that runs after 0005, before 0008.
        `insert into review_index(review_number,case_number,client_name,examiner,supervisor,program,sample_month,state,outcome,version,report) values ('fixture','case','Synthetic','101','201','SNAP','invalid','new_active','closed',2,'{"eligible":null,"variances":[]}')`,
      );
      const run = async (...args: string[]) => {
        const child = Bun.spawn(
          ["bun", "run", "scripts/db-migrate.ts", "--target", "development", ...args],
          { env: { ...process.env, DATABASE_URL: url.toString() }, stdout: "pipe", stderr: "pipe" },
        );
        const [stdout, stderr, code] = await Promise.all([
          new Response(child.stdout).text(),
          new Response(child.stderr).text(),
          child.exited,
        ]);
        return { output: stdout + stderr, code };
      };
      const status = await run("--status");
      expect(status.code).toBe(1);
      expect(status.output).toContain("pending 0006");
      const failed = await run();
      expect(failed.code).toBe(1);
      expect(failed.output).toContain("0006_fiscal_year_review_keys.sql, statement 5");
      expect(failed.output).toContain("22P02");
      expect(
        (
          await client.query(
            "select count(*)::int as n from information_schema.columns where table_name = 'review_index' and column_name = 'fiscal_year'",
          )
        ).rows[0].n,
      ).toBe(0);
      expect(
        (await client.query("select count(*)::int as n from drizzle.__drizzle_migrations")).rows[0]
          .n,
      ).toBe(6);
      await client.query("update review_index set sample_month = '2026-09'");
      for (const [version, sampleMonth] of [
        [1, "2025-09"],
        [2, "2026-09"],
      ])
        await client.query("insert into snap_review_snapshots values ($1,$2,$3,$4,$5,now())", [
          "fixture",
          version,
          `fixture-${version}`,
          { sampleMonth },
          {},
        ]);
      await client.query(
        "insert into inbox_events(id, review_number, staff_id, kind, actor_role, created_at) values ('fixture-event', 'fixture', '101', 'distributed', 'admin', now())",
      );
      await client.query(
        "insert into review_touches(staff_id, review_number, touched_at) values ('101', 'fixture', now())",
      );
      const applied = await run();
      expect(applied.code).toBe(0);
      const rows = (
        await client.query(
          "select review_key, review->>'id' as id from snap_review_snapshots order by version",
        )
      ).rows;
      expect(rows).toHaveLength(2);
      const key = rows[0].review_key;
      expect(isReviewKey(key)).toBe(true);
      expect(rows).toEqual([
        { review_key: key, id: key },
        { review_key: key, id: key },
      ]);
      expect((await client.query("select review_key, fiscal_year from review_index")).rows).toEqual(
        [{ review_key: key, fiscal_year: 2026 }],
      );
      for (const table of ["inbox_events", "review_touches"])
        expect((await client.query(`select review_key from ${table}`)).rows).toEqual([
          { review_key: key },
        ]);
      await expect(
        client.query(
          "insert into review_index(review_key, review_number, case_number, client_name, examiner, supervisor, program, sample_month, state, outcome, version, fiscal_year, report) select gen_random_uuid(), review_number, case_number, client_name, examiner, supervisor, program, sample_month, state, outcome, version, fiscal_year, report from review_index",
        ),
      ).rejects.toMatchObject({ code: "23505" });
      expect((await run()).code).toBe(0);
      expect((await run("--status")).code).toBe(0);
      await client.query("alter table inbox_events drop column review_key");
      const drift = await run();
      expect(drift.code).toBe(1);
      expect(drift.output).toContain("schema drift");
    } finally {
      await client?.end();
      await admin.query(`DROP DATABASE IF EXISTS "${database}" WITH (FORCE)`);
      await admin.end();
    }
  },
  30_000,
);