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

Linked repository file from this build.

import { expect, test } from "bun:test";
import { inArray } from "drizzle-orm";
import { createSeed } from "@/programs/snap-negative/seed";
import { reviewKey } from "@/features/reviews/review-key";
import type { Actor } from "@/features/reviews/types";
import { getDb } from "./index";
import { reviewIndex, reviewSnapshots, reviewTouches, inboxEvents } from "./schema";
import { reviewStore } from "./review-store";
import { querySearchPage, searchSchema } from "./search-store";
const integration = process.env.QC_DB_INTEGRATION === "1" ? test : test.skip;
integration(
  "full stored search handles nested arrays, numeric ranges, NOT, sort, labels and scope",
  async () => {
    const id = `search-${crypto.randomUUID()}`;
    const actor: Actor = { name: "Search reader", role: "examiner", staffId: "101", program: id };
    const ids = [0, 1, 2].map((i) => `${id}-${i}`);
    const unselectedPayload = `unused-${"x".repeat(100_000)}`;
    const db = getDb();
    const reviews = ids.map((reviewNumber, i) => {
      const review = {
        ...createSeed(),
        id: crypto.randomUUID(),
        reviewNumber,
        program: id,
        examiner: "101",
        sampleMonth: ["2026-05", "2026-04", "2026-05"][i],
        clientName: ["Exact target", "Exact target continuation", "Other"][i],
      };
      review.facts.scenarios.compI.householdSize = [2, 10, 3][i];
      review.facts.scenarios.compI.rentCents = [200, 1000, 300][i];
      // Records saved before actor slimming still hold preferences, and search indexes them.
      const legacyActor = {
        ...actor,
        preferences: {
          score: [2, 10, 3][i],
          "a.b[]": i,
          malformedNumber: "2x3",
          unselectedPayload,
        },
      };
      review.notes = [
        {
          id: "note",
          body: ["rare evidence", "other evidence", "rare evidence"][i],
          actor: legacyActor,
          createdAt: "2026-09-09T12:00:00Z",
        },
      ];
      (review as typeof review & { intakeBlob: string }).intakeBlob = unselectedPayload;
      review.documents =
        i === 0
          ? [
              {
                id: "doc",
                name: "Hearing notice",
                filename: "notice.pdf",
                description: "Hearing deadline",
                category: "Evidence" as const,
                mimeType: "application/pdf",
                size: 10,
                storageKey: "unread-by-search",
                actor,
                createdAt: "2026-09-09T12:00:00Z",
              },
            ]
          : [];
      return review;
    });
    const query = (filters: unknown[], extra: Record<string, unknown> = {}) =>
      querySearchPage(actor, searchSchema.parse({ filters, ...extra }));
    try {
      for (const review of reviews)
        await reviewStore.append({
          review,
          version: 0,
          requestId: review.reviewNumber,
          actor: { name: "Import", role: "admin" },
          savedAt: new Date().toISOString(),
        });
      let result = await query([
        { field: "notes[].body", mode: "contains", term: "rare" },
        { field: "facts.scenarios.compI.householdSize", mode: "between", from: "2", through: "3" },
      ]);
      expect(result.total).toBe(2);
      expect(result.reviews[0].values["notes[].body"]).toBe("rare evidence");
      expect(JSON.stringify(result.reviews)).not.toContain(unselectedPayload);
      expect(result.reviews[0].examinerName).toBe("Alex Examiner");
      expect(
        (
          await query([
            { field: "documents[].name", mode: "contains", term: "Hearing", exclude: true },
          ])
        ).total,
      ).toBe(2);
      expect((await query([{ field: "documents", mode: "empty" }])).total).toBe(2);
      expect((await query([{ field: "documents[].name", mode: "present" }])).total).toBe(1);
      expect(
        (await query([{ field: "facts.scenarios.compI.rentCents", mode: "exact", term: "300" }]))
          .reviews[0].reviewNumber,
      ).toBe(ids[2]);
      expect(
        (
          await query([
            { field: "notes[].actor.preferences.score", mode: "between", from: "2", through: "3" },
          ])
        ).total,
      ).toBe(2);
      expect(
        (await query([{ field: "notes[].actor.preferences.a~1b~2~3", mode: "exact", term: "1" }]))
          .reviews[0].reviewNumber,
      ).toBe(ids[1]);
      expect(
        (
          await query([
            { field: "notes[].createdAt", mode: "exact", term: "2026-09-09T08:00:00-04:00" },
          ])
        ).total,
      ).toBe(3);
      for (const sort of [
        "facts.scenarios.compI.householdSize",
        "notes[].actor.preferences.score",
      ]) {
        expect(
          (await query([], { sort, direction: "asc" })).reviews.map((row) => row.reviewNumber),
        ).toEqual([ids[0], ids[2], ids[1]]);
        expect(
          (await query([], { sort, direction: "desc" })).reviews.map((row) => row.reviewNumber),
        ).toEqual([ids[1], ids[2], ids[0]]);
      }
      // The second sort breaks month ties. Swapping priorities changes the result order.
      for (const secondField of ["clientName", "facts.scenarios.compI.householdSize"]) {
        const sorts = [
          { field: "sampleMonth", direction: "desc" },
          { field: secondField, direction: "desc" },
        ];
        expect((await query([], { sorts })).reviews.map((row) => row.reviewNumber)).toEqual([
          ids[2],
          ids[0],
          ids[1],
        ]);
        expect(
          (await query([], { sorts: [sorts[0], { ...sorts[1], direction: "asc" }] })).reviews.map(
            (row) => row.reviewNumber,
          ),
        ).toEqual([ids[0], ids[2], ids[1]]);
        expect(
          (await query([], { sorts: JSON.stringify(sorts), limit: 1, offset: 1 })).reviews.map(
            (row) => row.reviewNumber,
          ),
        ).toEqual([ids[0]]);
      }
      expect(
        (
          await query([], {
            sorts: [
              { field: "facts.scenarios.compI.householdSize", direction: "desc" },
              { field: "sampleMonth", direction: "desc" },
            ],
          })
        ).reviews.map((row) => row.reviewNumber),
      ).toEqual([ids[1], ids[2], ids[0]]);
      expect(
        (
          await query([], {
            sorts: [
              { field: "sampleMonth", direction: "desc" },
              { field: "examiner", direction: "asc" },
            ],
          })
        ).reviews.map((row) => row.reviewNumber),
        // Reviews 0 and 2 tie on both sorts, so the random review key decides their order.
      ).toEqual([
        ...[reviews[0]!, reviews[2]!]
          .sort((a, b) => (reviewKey(a) < reviewKey(b) ? -1 : 1))
          .map((review) => review.reviewNumber),
        ids[1],
      ]);
      expect(
        (await query([{ field: "notes[].actor.preferences.score", mode: "exact", term: "3.0" }]))
          .reviews[0].reviewNumber,
      ).toBe(ids[2]);
      expect(
        (
          await query([
            {
              field: "notes[].actor.preferences.malformedNumber",
              mode: "between",
              from: "1",
              through: "4",
            },
          ])
        ).total,
      ).toBe(0);
      expect(
        (await query([], { q: "Hearing deadline", quick: "false" })).reviews[0].reviewNumber,
      ).toBe(ids[0]);
      expect((await query([], { q: "Alex Examiner" })).total).toBe(3);
      expect((await query([], { q: "Hearing deadline", quick: "true" })).total).toBe(0);
      expect(
        (
          await query([{ field: "documents[].name", mode: "contains", term: "Hearing" }], {
            quick: "true",
          })
        ).total,
      ).toBe(1);
      expect(
        (await query([], { sort: "facts.scenarios.compI.householdSize", direction: "asc" }))
          .reviews[0].values["facts.scenarios.compI.householdSize"],
      ).toBe("2");
      result = await query([], { q: "Exact target", sort: "relevance", direction: "desc" });
      expect(result.reviews[0].reviewNumber).toBe(ids[0]);
      expect(result.reviews[0].relevance).toBeGreaterThan(result.reviews[1].relevance);
      expect(
        (await query([], { q: "Exact target", sort: "relevance", direction: "asc" })).reviews[0]
          .reviewNumber,
      ).toBe(ids[0]);
      expect(
        (await querySearchPage({ ...actor, staffId: "108" }, searchSchema.parse({ q: "rare" })))
          .total,
      ).toBe(0);
      // Appending a changed snapshot must remove matches found only in older history.
      await reviewStore.append({
        review: { ...reviews[0], notes: [] },
        version: 1,
        requestId: `${id}-changed`,
        actor: { name: "Editor", role: "admin" },
        savedAt: new Date().toISOString(),
      });
      expect((await query([{ field: "notes[].body", mode: "contains", term: "rare" }])).total).toBe(
        1,
      );
    } finally {
      await db.delete(inboxEvents).where(inArray(inboxEvents.reviewNumber, ids));
      await db.delete(reviewTouches).where(inArray(reviewTouches.reviewNumber, ids));
      await db.delete(reviewIndex).where(inArray(reviewIndex.reviewNumber, ids));
      await db.delete(reviewSnapshots).where(inArray(reviewSnapshots.reviewNumber, ids));
    }
  },
  60000,
);