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

Linked repository file from this build.

import { fiscalYear, reviewKey } from "@/features/reviews/review-key";
import { expect, test } from "bun:test";
import { and, eq, inArray } from "drizzle-orm";
import { createSeed } from "@/programs/snap-negative/seed";
import type { Actor } from "@/features/reviews/types";
import { getDb } from "./index";
import { inboxEvents, inboxVisits, reviewIndex, reviewSnapshots, reviewTouches } from "./schema";
import { reviewStore } from "./review-store";
import {
  clearEvents,
  inboxSeen,
  queryEvents,
  queryInbox,
  readEvent,
  touchReview,
} from "./inbox-store";
import { inboxQuerySchema } from "./inbox-query";
import { querySearch, searchSchema } from "./search-store";

/** Index rows written directly by these tests have nothing to report. */
const noReport = { eligible: null, variances: [] };

// Uses isolated review/staff IDs and removes only its own records. Never reset the database.
const integration = process.env.QC_DB_INTEGRATION === "1" ? test : test.skip;
integration(
  "persisted inbox lifecycle, role scope, and atomic duplicate rejection",
  async () => {
    const id = `inbox-test-${crypto.randomUUID()}`;
    const examiner: Actor = {
      name: "Integration examiner",
      role: "examiner",
      staffId: `${id}-e`,
      program: "SNAP",
    };
    const supervisor: Actor = {
      name: "Integration supervisor",
      role: "supervisor",
      staffId: `${id}-s`,
      subordinateStaffIds: [examiner.staffId!],
      program: "SNAP",
    };
    const db = getDb();
    const now = new Date().toISOString();
    const review = {
      ...createSeed(),
      id: crypto.randomUUID(),
      reviewNumber: id,
      sampleMonth: now.slice(0, 7),
      examiner: examiner.staffId!,
      supervisor: supervisor.staffId!,
    };
    const original = {
      review,
      version: 0,
      requestId: `${id}-0`,
      actor: { name: "Import", role: "admin" as const },
      savedAt: now,
    };
    try {
      await reviewStore.append(original);
      expect((await queryInbox(examiner)).newReviews.map((row) => row.reviewNumber)).toEqual([id]);
      expect(
        (await querySearch({ ...examiner, staffId: `${id}-other` }, searchSchema.parse({ q: id })))
          .length,
      ).toBe(0);
      expect((await querySearch(supervisor, searchSchema.parse({ q: id }))).length).toBe(1);
      const firstNews = await queryEvents(examiner);
      expect(firstNews.unreadCount).toBe(1);
      await inboxSeen(examiner, now);
      const visited = await queryInbox(examiner);
      expect(visited.lastInboxSeenAt).toBe(now);
      // A database visit must survive the same query-string validation as the next browser page.
      const nextPage = inboxQuerySchema.parse({
        since: visited.since,
        asOf: visited.asOf,
        newPage: "1",
      });
      expect((await queryInbox(examiner, nextPage)).since).toBe(now);
      await touchReview(supervisor, review.id);
      expect((await queryInbox(examiner)).newReviews.length).toBe(1);
      await touchReview(examiner, review.id);
      expect((await queryInbox(examiner)).newReviews.length).toBe(0);
      expect((await queryInbox(examiner)).lastTouched[0]?.reviewNumber).toBe(id);
      await readEvent({ ...examiner, staffId: `${id}-other` }, firstNews.events[0]!.id);
      expect((await queryEvents(examiner)).unreadCount).toBe(1);
      await readEvent(examiner, firstNews.events[0]!.id);
      expect((await queryEvents(examiner)).unreadCount).toBe(0);
      const update = {
        ...original,
        version: 1,
        requestId: `${id}-1`,
        actor: supervisor,
        review: { ...review, appointment: "2026-10-01T10:00" },
        savedAt: new Date().toISOString(),
      };
      // A save can finish after a newer workspace touch. Keep Last touched monotonic.
      const laterTouch = new Date(Date.now() + 60_000).toISOString();
      await db
        .insert(reviewTouches)
        .values({
          staffId: supervisor.staffId!,
          reviewKey: reviewKey(review),
          reviewNumber: id,
          touchedAt: laterTouch,
        })
        .onConflictDoUpdate({
          target: [reviewTouches.staffId, reviewTouches.reviewKey],
          set: { touchedAt: laterTouch },
        });
      await reviewStore.append(update);
      await touchReview(supervisor, review.id);
      const latestTouch = (
        await db
          .select()
          .from(reviewTouches)
          .where(
            and(eq(reviewTouches.staffId, supervisor.staffId!), eq(reviewTouches.reviewNumber, id)),
          )
      )[0]!;
      expect(new Date(latestTouch.touchedAt).toISOString()).toBe(laterTouch);
      expect((await queryEvents(examiner)).events[0]?.kind).toBe("appointment_changed");
      expect(
        (await queryEvents(supervisor)).events.some(
          (event) => event.kind === "appointment_changed",
        ),
      ).toBe(false);
      await expect(
        reviewStore.append({ ...update, review: { ...update.review, state: "needs_changes" } }),
      ).rejects.toThrow();
      expect((await querySearch(examiner, searchSchema.parse({ q: id })))[0]?.state).toBe(
        "new_active",
      );
      expect((await queryEvents(examiner)).unreadCount).toBe(1);
    } finally {
      await db.batch([
        db.delete(inboxEvents).where(eq(inboxEvents.reviewNumber, id)),
        db
          .delete(inboxVisits)
          .where(inArray(inboxVisits.staffId, [examiner.staffId!, supervisor.staffId!])),
        db.delete(reviewTouches).where(eq(reviewTouches.reviewNumber, id)),
        db.delete(reviewIndex).where(eq(reviewIndex.reviewNumber, id)),
        db.delete(reviewSnapshots).where(eq(reviewSnapshots.reviewNumber, id)),
      ]);
    }
  },
  20_000,
);

integration(
  "task lists paginate beyond 100 with deduplicated touches, role queues, and a fixed news window",
  async () => {
    const prefix = `page-test-${crypto.randomUUID()}`;
    const examiner: Actor = {
      name: "Pagination examiner",
      role: "examiner",
      staffId: `${prefix}-e`,
      program: prefix,
    };
    const supervisor: Actor = {
      name: "Pagination supervisor",
      role: "supervisor",
      staffId: `${prefix}-s`,
      subordinateStaffIds: [examiner.staffId!],
      program: prefix,
    };
    const db = getDb();
    const asOf = new Date().toISOString();
    const before = new Date(Date.parse(asOf) - 1_000).toISOString();
    const after = new Date(Date.parse(asOf) + 1_000).toISOString();
    const active = Array.from({ length: 105 }, (_, index) => ({
      reviewNumber: `${prefix}-${String(index).padStart(3, "0")}`,
      caseNumber: `case-${index}`,
      clientName: `Client ${index}`,
      examiner: examiner.staffId!,
      supervisor: supervisor.staffId!,
      program: prefix,
      id: crypto.randomUUID(),
      fiscalYear: fiscalYear(asOf.slice(0, 7)),
      sampleMonth: asOf.slice(0, 7),
      state: "new_active",
      outcome: "",
      appointment: null,
      version: 0,
    }));
    const queues = [
      {
        ...active[0]!,
        id: crypto.randomUUID(),
        reviewNumber: `${prefix}-queue-1`,
        state: "awaiting_review",
      },
      {
        ...active[0]!,
        id: crypto.randomUUID(),
        reviewNumber: `${prefix}-queue-2`,
        state: "awaiting_pec_approval",
      },
      {
        ...active[0]!,
        id: crypto.randomUUID(),
        reviewNumber: `${prefix}-queue-3`,
        state: "awaiting_review",
        examiner: `${prefix}-other`,
      },
    ];
    const rows = [...active, ...queues];
    const numbers = rows.map((row) => row.reviewNumber);
    try {
      await db.batch([
        db
          .insert(reviewIndex)
          .values(rows.map((row) => ({ ...row, reviewKey: reviewKey(row), report: noReport }))),
        db.insert(reviewTouches).values(
          active.flatMap((row) =>
            [examiner.staffId!, supervisor.staffId!].map((staffId) => ({
              staffId,
              reviewKey: reviewKey(row),
              reviewNumber: row.reviewNumber,
              touchedAt: before,
            })),
          ),
        ),
        db.insert(inboxEvents).values(
          active.map((row) => ({
            id: `${row.reviewNumber}-news`,
            reviewKey: reviewKey(row),
            reviewNumber: row.reviewNumber,
            staffId: examiner.staffId!,
            kind: "distributed",
            actorRole: "admin",
            createdAt: before,
          })),
        ),
      ]);
      const first = await queryInbox(examiner, { pageSize: 100, asOf });
      expect(first.totals).toEqual({
        newReviews: 105,
        lastTouched: 105,
        awaitingReview: 2,
        events: 105,
      });
      expect(first.newReviews.length).toBe(100);
      expect(first.events.length).toBe(100);
      await inboxSeen(examiner, asOf);
      await db.insert(inboxEvents).values({
        id: `${prefix}-later`,
        reviewKey: reviewKey(active[0]!),
        reviewNumber: active[0]!.reviewNumber,
        staffId: examiner.staffId!,
        kind: "document_added",
        actorRole: "admin",
        createdAt: after,
      });
      const second = await queryInbox(examiner, {
        pageSize: 100,
        newPage: 1,
        touchedPage: 1,
        eventsPage: 1,
        asOf,
        since: first.since,
      });
      expect(second.newReviews.length).toBe(5);
      expect(second.lastTouched.length).toBe(5);
      expect(second.events.length).toBe(5);
      expect(second.totals.events).toBe(105);
      expect(
        new Set([...first.newReviews, ...second.newReviews].map((row) => row.reviewNumber)).size,
      ).toBe(105);
      expect(new Set([...first.events, ...second.events].map((event) => event.id)).size).toBe(105);
      // Coverage writes by two staff members still yield one row and one total per review.
      const team = await queryInbox(supervisor, { pageSize: 100, touchedPage: 1, asOf });
      expect(team.totals.lastTouched).toBe(105);
      expect(team.lastTouched.length).toBe(5);
      expect(team.totals.awaitingReview).toBe(1);
      const pec = await queryInbox({ ...examiner, role: "pec" }, { asOf });
      expect(pec.totals.awaitingReview).toBe(1);
      for (const role of ["program_manager", "admin", "statistics"] as const) {
        const senior = await queryInbox(
          { ...examiner, role },
          { asOf, pageSize: 1, queuePage: role === "program_manager" ? 1 : 2 },
        );
        expect(senior.totals.awaitingReview).toBe(role === "program_manager" ? 2 : 3);
        expect(senior.awaitingReview[0]?.reviewNumber).toBe(`${prefix}-queue-3`);
      }
      const fresh = await queryInbox(examiner, { asOf: after });
      expect(fresh.totals.events).toBe(1);
      expect(fresh.events[0]?.kind).toBe("document_added");
    } finally {
      await db.batch([
        db.delete(inboxEvents).where(inArray(inboxEvents.reviewNumber, numbers)),
        db
          .delete(inboxVisits)
          .where(inArray(inboxVisits.staffId, [examiner.staffId!, supervisor.staffId!])),
        db.delete(reviewTouches).where(inArray(reviewTouches.reviewNumber, numbers)),
        db.delete(reviewIndex).where(inArray(reviewIndex.reviewNumber, numbers)),
      ]);
    }
  },
  20_000,
);

integration(
  "clearing notifications respects scope, preferences, and the displayed cutoff",
  async () => {
    const prefix = `clear-news-${crypto.randomUUID()}`;
    const actor: Actor = {
      name: "Notification supervisor",
      role: "supervisor",
      staffId: `${prefix}-supervisor`,
      subordinateStaffIds: [`${prefix}-examiner`],
      program: prefix,
      preferences: { notifyDocuments: false, notifyTeamKickbacks: false },
    };
    const cutoff = "2026-09-13T12:00:00.000Z";
    const before = "2026-09-13T11:59:00.000Z";
    const after = "2026-09-13T12:00:01.000Z";
    const rows = [
      { suffix: "own", examiner: actor.staffId!, program: prefix },
      { suffix: "team", examiner: actor.subordinateStaffIds![0], program: prefix },
      { suffix: "outside-team", examiner: `${prefix}-other`, program: prefix },
      { suffix: "other-program", examiner: actor.staffId!, program: `${prefix}-other-program` },
    ].map(({ suffix, examiner, program }) => ({
      reviewNumber: `${prefix}-${suffix}`,
      caseNumber: suffix,
      clientName: suffix,
      examiner,
      supervisor: actor.staffId!,
      program,
      id: crypto.randomUUID(),
      fiscalYear: 2026,
      sampleMonth: "2026-09",
      state: "new_active",
      outcome: "",
      version: 0,
    }));
    const event = (
      suffix: string,
      review: string,
      kind: string,
      createdAt = before,
      staffId = actor.staffId!,
    ) => ({
      id: `${prefix}-${suffix}`,
      reviewKey: rows.find((row) => row.reviewNumber === `${prefix}-${review}`)!.id,
      reviewNumber: `${prefix}-${review}`,
      staffId,
      kind,
      actorRole: "admin",
      createdAt,
    });
    const events = [
      event("clear-before", "own", "approved"),
      event("clear-cutoff", "own", "approved", cutoff),
      event("clear-team", "team", "document_added"),
      event("later", "own", "approved", after),
      event("other-recipient", "own", "approved", before, `${prefix}-other-recipient`),
      event("other-program", "other-program", "approved"),
      event("outside-team", "outside-team", "approved"),
      event("own-disabled", "own", "document_added"),
      event("team-disabled", "team", "changes_requested"),
      { ...event("already-read", "own", "approved"), readAt: before },
    ];
    const db = getDb();
    const numbers = rows.map((row) => row.reviewNumber);
    try {
      await db
        .insert(reviewIndex)
        .values(rows.map((row) => ({ ...row, reviewKey: reviewKey(row), report: noReport })));
      await db.insert(inboxEvents).values(events);
      expect((await queryEvents(actor)).unreadCount).toBe(4);
      await clearEvents(actor, cutoff);
      const saved = await db
        .select()
        .from(inboxEvents)
        .where(inArray(inboxEvents.reviewNumber, numbers));
      for (const row of saved) {
        if (row.id === `${prefix}-already-read`)
          expect(new Date(row.readAt!).toISOString()).toBe(before);
        else if (row.id.startsWith(`${prefix}-clear-`)) expect(row.readAt).not.toBeNull();
        else expect(row.readAt).toBeNull();
      }
      const remaining = await queryEvents(actor);
      expect(remaining.unreadCount).toBe(1);
      expect(remaining.events.map((row) => row.id)).toEqual([`${prefix}-later`]);
      await clearEvents(actor, cutoff);
      const retried = await db
        .select()
        .from(inboxEvents)
        .where(inArray(inboxEvents.reviewNumber, numbers));
      expect(new Map(retried.map((row) => [row.id, row.readAt]))).toEqual(
        new Map(saved.map((row) => [row.id, row.readAt])),
      );
    } finally {
      await db.delete(inboxEvents).where(inArray(inboxEvents.reviewNumber, numbers));
      await db.delete(reviewIndex).where(inArray(reviewIndex.reviewNumber, numbers));
    }
  },
  20_000,
);