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

Linked repository file from this build.

import { expect, test } from "bun:test";
import { eq, inArray } from "drizzle-orm";
import { createSeed } from "@/programs/snap-negative/seed";
import type { Actor } from "@/features/reviews/types";
import { catalogQuerySchema } from "@/features/catalog/query";
import { getDb } from "./index";
import { reviewIndex, reviewSnapshots, reviewTouches, inboxEvents } from "./schema";
import { reviewStore } from "./review-store";
import { touchReview, queryInbox } from "./inbox-store";
import { flagReview, queryCatalog } from "./catalog-store";
import { querySearchPage, searchSchema } from "./search-store";
const integration = process.env.QC_DB_INTEGRATION === "1" ? test : test.skip;
integration(
  "catalog preserves actor flags, exact read versions, branches and independent search terms",
  async () => {
    const id = `catalog-test-${crypto.randomUUID()}`;
    const actor: Actor = {
      name: "Catalog examiner",
      role: "examiner",
      staffId: `${id}-e`,
      program: id,
    };
    const supervisor: Actor = {
      name: "Catalog supervisor",
      role: "supervisor",
      staffId: `${id}-s`,
      subordinateStaffIds: [actor.staffId!],
      program: id,
    };
    const ids = [`${id}-15`, `${id}-25`, `${id}-35`];
    const db = getDb();
    const queries = catalogQuerySchema.parse({});
    const now = new Date().toISOString();
    const rows = ids.map((reviewNumber, i) => ({
      review: {
        ...createSeed(),
        id: crypto.randomUUID(),
        reviewNumber,
        program: id,
        examiner: actor.staffId!,
        supervisor: supervisor.staffId!,
        clientName: i === 2 ? "Jamie Adams" : "Casey Zimmerman",
        sampleMonth: i === 1 ? "2026-10" : "2026-04",
        county: "02",
      },
      actor: { ...actor, role: "admin" as const, staffId: undefined },
      savedAt: now,
      version: 0,
      requestId: `${reviewNumber}-0`,
    }));
    try {
      for (const row of rows) await reviewStore.append(row);
      let catalog = await queryCatalog(actor, queries);
      expect(catalog.total).toBe(3);
      expect(catalog.branches.map((b) => b.year).sort()).toEqual([2026, 2027]);
      expect(catalog.reviews.every((r) => r.unread)).toBe(true);
      await flagReview(actor, rows[0].review.id, true);
      expect((await queryInbox(actor)).totals.lastTouched).toBe(0);
      expect(
        (await queryCatalog(actor, catalogQuerySchema.parse({ sort: "flagged" }))).reviews[0]
          .reviewNumber,
      ).toBe(ids[0]);
      expect((await queryCatalog(supervisor, queries)).reviews.every((r) => !r.flagged)).toBe(true);
      await touchReview(actor, rows[0].review.id, 0);
      expect(
        (await queryCatalog(actor, queries)).reviews.find((r) => r.reviewNumber === ids[0])?.unread,
      ).toBe(false);
      await reviewStore.append({
        ...rows[0],
        version: 1,
        requestId: `${ids[0]}-1`,
        savedAt: new Date().toISOString(),
        review: { ...rows[0].review, appointment: "2026-09-15T14:00" },
      });
      // A late response for version 0 must not mark the unseen version 1 as read.
      await touchReview(actor, rows[0].review.id, 0);
      catalog = await queryCatalog(actor, queries);
      expect(catalog.reviews.find((r) => r.reviewNumber === ids[0])).toMatchObject({
        unread: true,
        flagged: true,
      });
      expect((await reviewStore.load(rows[0].review.id))?.version).toBe(1);
      await touchReview(actor, rows[0].review.id, 1);
      await touchReview(actor, rows[0].review.id, 0);
      expect(
        (await queryCatalog(actor, queries)).reviews.find((r) => r.reviewNumber === ids[0])?.unread,
      ).toBe(false);
      const search = await querySearchPage(
        actor,
        searchSchema.parse({
          filters: JSON.stringify([
            { field: "reviewNumber", mode: "ends", term: "15" },
            { field: "clientName", mode: "starts", term: "Z" },
            { field: "sampleMonth", from: "2026-04", through: "2026-07" },
          ]),
        }),
      );
      expect(search.total).toBe(1);
      expect(search.reviews[0].reviewNumber).toBe(ids[0]);
      await expect(
        flagReview({ ...actor, staffId: `${id}-outsider` }, rows[0].review.id, true),
      ).rejects.toThrow();
      await expect(
        queryCatalog(actor, catalogQuerySchema.parse({ queue: "true" })),
      ).rejects.toThrow();
      expect((await queryCatalog({ ...actor, staffId: `${id}-outsider` }, queries)).total).toBe(0);
    } finally {
      await db.delete(inboxEvents).where(inArray(inboxEvents.reviewNumber, ids));
      await db.delete(reviewTouches).where(inArray(reviewTouches.reviewNumber, ids));
      await db.delete(reviewIndex).where(eq(reviewIndex.program, id));
      await db.delete(reviewSnapshots).where(inArray(reviewSnapshots.reviewNumber, ids));
    }
  },
  60000,
);