src/lib/db/catalog-store.ts

Linked repository file from this build.

import { isReviewKey } from "@/features/reviews/review-key";
import { and, asc, count, desc, eq, inArray, sql, type SQL } from "drizzle-orm";
import type { Actor } from "@/features/reviews/types";
import { queueStates, type CatalogQuery } from "@/features/catalog/query";
import { AccessError, can } from "@/lib/auth/scope";
import { getDb } from "./index";
import { reviewIndex, reviewTouches } from "./schema";
import { reviewScope } from "./scope";

export function catalogScope(actor: Actor, queue: boolean): SQL {
  if (queue && !can(actor, "approvalQueue"))
    throw new AccessError("Your role does not include the Approval Queue.");
  return and(
    reviewScope(actor),
    queue ? inArray(reviewIndex.state, queueStates(actor)) : undefined,
  )!;
}
const fiscalYearSql = reviewIndex.fiscalYear;
const monthSql = sql<string>`substring(${reviewIndex.sampleMonth}, 1, 7)`;
const unreadSql = sql<boolean>`coalesce(${reviewTouches.readVersion}, -1) < ${reviewIndex.version}`;
const flaggedSql = sql<boolean>`coalesce(${reviewTouches.flagged}, false)`;
export async function queryCatalog(
  actor: Actor,
  options: Omit<CatalogQuery, "includeBranches"> & { includeBranches?: "true" | "false" },
) {
  const db = getDb();
  const scope = catalogScope(actor, options.queue === "true");
  const categoryScope = and(
    scope,
    options.category ? eq(reviewIndex.state, options.category) : undefined,
  )!;
  const selected = and(
    categoryScope,
    options.examiner ? eq(reviewIndex.examiner, options.examiner) : undefined,
    options.year ? sql`${fiscalYearSql} = ${options.year}` : undefined,
    options.month ? sql`${monthSql} = ${options.month}` : undefined,
  )!;
  const reader = and(
    eq(reviewTouches.reviewKey, reviewIndex.reviewKey),
    eq(reviewTouches.staffId, actor.staffId ?? ""),
  );
  const sorting = {
    newest: desc(reviewIndex.updatedAt),
    oldest: asc(reviewIndex.updatedAt),
    sender: asc(reviewIndex.routedBy),
    unread: desc(unreadSql),
    flagged: desc(flaggedSql),
    category: asc(reviewIndex.state),
  }[options.sort];
  const [reviews, totals, branches] = await Promise.all([
    db
      .select({
        reviewKey: reviewIndex.reviewKey,
        reviewNumber: reviewIndex.reviewNumber,
        fiscalYear: reviewIndex.fiscalYear,
        clientName: reviewIndex.clientName,
        caseNumber: reviewIndex.caseNumber,
        examiner: reviewIndex.examiner,
        state: reviewIndex.state,
        outcome: reviewIndex.outcome,
        updatedAt: reviewIndex.updatedAt,
        routedBy: reviewIndex.routedBy,
        unread: unreadSql,
        flagged: flaggedSql,
      })
      .from(reviewIndex)
      .leftJoin(reviewTouches, reader)
      .where(selected)
      .orderBy(sorting, desc(reviewIndex.updatedAt), asc(reviewIndex.reviewKey))
      .limit(options.pageSize)
      .offset(options.page * options.pageSize),
    db.select({ total: count() }).from(reviewIndex).where(selected),
    options.includeBranches !== "false"
      ? db
          .select({
            examiner: reviewIndex.examiner,
            year: fiscalYearSql,
            month: monthSql,
            total: count(),
            unread: sql<number>`count(*) filter (where ${unreadSql})`.mapWith(Number),
          })
          .from(reviewIndex)
          .leftJoin(reviewTouches, reader)
          .where(categoryScope)
          .groupBy(reviewIndex.examiner, fiscalYearSql, monthSql)
          .orderBy(asc(reviewIndex.examiner), desc(fiscalYearSql), desc(monthSql))
      : Promise.resolve([]),
  ]);
  return { reviews, total: totals[0]?.total ?? 0, branches };
}
export async function flagReview(actor: Actor, reviewId: string, flagged: boolean) {
  const db = getDb();
  const key = isReviewKey(reviewId) ? reviewId : undefined;
  if (!key) throw new AccessError();
  // Keep authorization in the INSERT itself; a concurrent assignment cannot bypass scope.
  const result = await db.execute(sql`
    INSERT INTO review_touches (staff_id, review_key, review_number, touched_at, read_version, flagged)
    SELECT ${actor.staffId ?? ""}, ${reviewIndex.reviewKey}, ${reviewIndex.reviewNumber}, '1970-01-01T00:00:00Z'::timestamptz, -1, ${flagged}
    FROM ${reviewIndex} WHERE ${and(reviewScope(actor), eq(reviewIndex.reviewKey, key))}
    ON CONFLICT (staff_id, review_key) DO UPDATE SET flagged = EXCLUDED.flagged
    RETURNING review_number
  `);
  if (!result.rows.length) throw new AccessError();
}