src/lib/db/review-store.ts

Linked repository file from this build.

import { isReviewKey, fiscalYear, reviewKey } from "@/features/reviews/review-key";
import { transmittalEligible } from "@/features/reviews/downstream";
import { reportRowFromReview } from "@/features/reports/report-input";
import { and, desc, eq, sql } from "drizzle-orm";
import type { Actor } from "@/features/reviews/types";
import {
  protectReviewStore,
  type ReviewStore,
  type StoredReview,
} from "@/features/reviews/review-store";
import type { SnapReview } from "@/programs/snap-negative/types";
import { getDb } from "./index";
import { reviewSnapshots, reviewIndex, inboxEvents, reviewTouches } from "./schema";
import { reviewSender } from "./review-projection";
import { eventsForSave } from "./inbox-events";
import { reviewScope } from "./scope";

const currentSnapshot = and(
  eq(reviewSnapshots.reviewKey, reviewIndex.reviewKey),
  eq(reviewSnapshots.version, reviewIndex.version),
);

/** Intake needs optimistic-lock versions, not the snapshot bodies. */
export async function queryReviewVersions(actor: Actor) {
  return getDb()
    .select({
      reviewKey: reviewIndex.reviewKey,
      reviewNumber: reviewIndex.reviewNumber,
      version: reviewIndex.version,
      sampleMonth: reviewIndex.sampleMonth,
      program: reviewIndex.program,
    })
    .from(reviewIndex)
    .where(reviewScope(actor));
}

/** Return an accessible review identity for a document key without transferring its snapshot. */
export async function findReviewForDocument(actor: Actor, storageKey: string) {
  return (
    await getDb()
      .select({ reviewKey: reviewIndex.reviewKey, reviewNumber: reviewIndex.reviewNumber })
      .from(reviewIndex)
      .innerJoin(reviewSnapshots, currentSnapshot)
      .where(
        and(
          reviewScope(actor),
          sql`jsonb_path_exists(${reviewSnapshots.review}, '$.documents[*] ? (@.storageKey == $key)'::jsonpath, jsonb_build_object('key', ${storageKey}::text))`,
        ),
      )
      .limit(1)
  )[0];
}

/** Rows that one saved review version writes. The live store and the seed script share them. */
export function reviewRows(value: StoredReview, previous?: SnapReview) {
  const review = value.review;
  const { eligible, variances } = reportRowFromReview(review);
  const key = reviewKey(review);
  return {
    snapshot: { ...value, reviewKey: key, reviewNumber: review.reviewNumber },
    index: {
      report: { eligible, variances, transmittalEligible: transmittalEligible(review) },
      reviewKey: key,
      reviewNumber: review.reviewNumber,
      fiscalYear: fiscalYear(review.sampleMonth),
      caseNumber: review.caseNumber,
      clientName: review.clientName,
      examiner: review.examiner,
      supervisor: review.supervisor,
      program: review.program,
      sampleMonth: review.sampleMonth,
      state: review.state,
      outcome: review.outcome,
      appointment: review.appointment ?? null,
      county: review.county ?? "",
      updatedAt: value.savedAt,
      routedBy: reviewSender(value),
      version: value.version,
    },
    events: eventsForSave(previous, value),
  };
}

export const reviewStore: ReviewStore = protectReviewStore({
  async findBusiness(identity) {
    const row = (
      await getDb()
        .select({
          review: reviewSnapshots.review,
          version: reviewSnapshots.version,
          requestId: reviewSnapshots.requestId,
          actor: reviewSnapshots.actor,
          savedAt: reviewSnapshots.savedAt,
        })
        .from(reviewIndex)
        .innerJoin(reviewSnapshots, currentSnapshot)
        .where(
          and(
            eq(reviewIndex.program, identity.program),
            eq(reviewIndex.fiscalYear, identity.fiscalYear),
            eq(reviewIndex.reviewNumber, identity.reviewNumber),
          ),
        )
        .limit(1)
    )[0];
    return row;
  },
  async load(key) {
    if (!isReviewKey(key)) return undefined;
    return (
      await getDb()
        .select()
        .from(reviewSnapshots)
        .where(eq(reviewSnapshots.reviewKey, key))
        .orderBy(desc(reviewSnapshots.version))
        .limit(1)
    )[0];
  },
  async findRequest(requestId) {
    return (
      await getDb()
        .select()
        .from(reviewSnapshots)
        .where(eq(reviewSnapshots.requestId, requestId))
        .limit(1)
    )[0];
  },
  async append(value, known) {
    const db = getDb();
    const previous =
      known ??
      (
        await db
          .select({ review: reviewSnapshots.review })
          .from(reviewSnapshots)
          .where(eq(reviewSnapshots.reviewKey, reviewKey(value.review)))
          .orderBy(desc(reviewSnapshots.version))
          .limit(1)
      )[0]?.review;
    const review = value.review;
    const { snapshot, index: projection, events } = reviewRows(value, previous);
    // Neon batch is a single transaction: a stale snapshot also rolls back index and news.
    await db.batch([
      db.insert(reviewSnapshots).values(snapshot),
      db
        .insert(reviewIndex)
        .values(projection)
        .onConflictDoUpdate({
          target: reviewIndex.reviewKey,
          set: {
            ...projection,
            ...(previous && previous.examiner !== review.examiner ? { openedAt: null } : {}),
          },
        }),
      ...(events.length ? [db.insert(inboxEvents).values(events)] : []),
      ...(value.actor.staffId
        ? [
            db
              .insert(reviewTouches)
              .values({
                staffId: value.actor.staffId,
                reviewKey: reviewKey(review),
                reviewNumber: review.reviewNumber,
                touchedAt: value.savedAt,
                readVersion: value.version,
              })
              .onConflictDoUpdate({
                target: [reviewTouches.staffId, reviewTouches.reviewKey],
                set: {
                  readVersion: sql`greatest(${reviewTouches.readVersion}, ${value.version})`,
                  touchedAt: sql`greatest(${reviewTouches.touchedAt}, ${value.savedAt}::timestamptz)`,
                },
              }),
          ]
        : []),
    ]);
  },
  async list() {
    return getDb()
      .selectDistinctOn([reviewSnapshots.reviewKey])
      .from(reviewSnapshots)
      .orderBy(reviewSnapshots.reviewKey, desc(reviewSnapshots.version));
  },
});