src/lib/db/report-store.integration.test.ts

Linked repository file from this build.

import { afterAll, beforeAll, expect, test } from "bun:test";
import { mkdir, writeFile } from "node:fs/promises";
import { inArray } from "drizzle-orm";
import { demoActor } from "@/lib/auth/roster";
import { aggregateReport, type ReportFilters } from "@/features/reports/report-data";
import { reportRowFromReview } from "@/features/reports/report-input";
import { jsonBytes, medianAsyncMs } from "@/lib/perf/measure";
import { budgets } from "@/lib/perf/budgets";
import { createSeed } from "@/programs/snap-negative/seed";
import type { SnapReview } from "@/programs/snap-negative/types";
import { reviewStore } from "./review-store";
import { queryReport, reportInputs } from "./report-store";
import { getDb } from "./index";
import { inboxEvents, reviewIndex, reviewSnapshots, reviewTouches } from "./schema";

const enabled = process.env.QC_DB_INTEGRATION === "1";
const integration = enabled ? test : test.skip;
const program = `report-test-${crypto.randomUUID()}`;
const actor = { ...demoActor("statistics"), program };
const manager = { ...demoActor("program_manager"), program };
const at = "2026-09-13T12:00:00Z";
const numbers: string[] = [];
beforeAll(async () => {
  if (!enabled) return;
  const base = {
    ...createSeed(),
    id: crypto.randomUUID(),
    program,
    examiner: "101",
    supervisor: "201",
    sampleMonth: "2026-04",
  };
  const flag = { actor: manager, at };
  const cases: Partial<SnapReview>[] = [
    { state: "released_to_reporting", outcome: "clean", errorSummaryReleased: flag },
    { state: "released_to_reporting", outcome: "error", errorSummaryReleased: flag },
    { state: "awaiting_cao_response", outcome: "error", memoApproved: flag },
    { state: "awaiting_manager", outcome: "error", supervisorApproved: flag, pecApproved: flag },
    { state: "dropped", outcome: "drop", dropApproved: flag },
    { state: "released_to_reporting", outcome: "error" },
  ];
  for (const [index, patch] of cases.entries()) {
    const review = {
      ...structuredClone(base),
      ...patch,
      id: crypto.randomUUID(),
      reviewNumber: `${program}-${index}`,
    };
    review.facts.answers.variances = [
      { element: "416", nature: "096" },
      { element: "540", nature: "096" },
    ];
    numbers.push(review.reviewNumber);
    await reviewStore.append({
      review,
      version: 0,
      requestId: crypto.randomUUID(),
      actor,
      savedAt: at,
    });
  }
});
afterAll(async () => {
  if (!numbers.length) return;
  for (const table of [reviewTouches, inboxEvents, reviewIndex, reviewSnapshots])
    await getDb().delete(table).where(inArray(table.reviewNumber, numbers));
});

integration(
  "report projections and snapshot fallback require release and expose one variance pair",
  async () => {
    const stored = (await reviewStore.list()).filter((row) => row.review.program === program);
    const reviews = stored.map((row) => row.review);
    const filters: ReportFilters[] = [
      {},
      { from: "2026-04", through: "2026-07" },
      { examiner: "101", county: reviews[0].county },
      { examiner: "missing" },
    ];
    for (const filter of filters) {
      const result = await queryReport(actor, filter);
      expect(result.summary).toEqual(aggregateReport(reviews.map(reportRowFromReview), filter));
      expect(result.examiners).toContain("101");
    }
    const projected = await queryReport(actor);
    expect(projected.summary).toMatchObject({
      total: 6,
      clean: 1,
      error: 1,
      eligible: 2,
      ineligibleApproved: 1,
    });
    expect(
      projected.summary.byErrorCode.map((row) => [row.element, row.nature, row.reviews]),
    ).toEqual([["416", "096", 1]]);
    const inputs = await reportInputs(actor);

    const measurements = {
      records: reviews.length,
      previousSnapshotJsonBytes: jsonBytes(stored),
      databaseProjectionJsonBytes: jsonBytes(inputs),
      reportApiJsonBytes: jsonBytes(projected),
      reportMedianMs: await medianAsyncMs(async () => {
        await queryReport(actor);
      }),
    };
    await mkdir("test-results/report-optimization", { recursive: true });
    await writeFile(
      "test-results/report-optimization/database.json",
      JSON.stringify(measurements, null, 2),
    );
    expect(measurements.reportApiJsonBytes).toBeLessThan(budgets.reportsApi);
    expect(measurements.databaseProjectionJsonBytes).toBeLessThan(
      measurements.previousSnapshotJsonBytes / 4,
    );
    expect(await queryReport({ ...actor, program: "missing" })).toMatchObject({
      summary: { total: 0 },
      examiners: [],
      counties: [],
    });
    expect(await queryReport({ ...actor, staffId: "" })).toMatchObject({ summary: { total: 0 } });
    await expect(queryReport({ ...actor, role: "examiner" })).rejects.toThrow(
      "cannot load reports",
    );
  },
);