src/lib/db/inbox-store.ts

Linked repository file from this build.

import { isReviewKey } from "@/features/reviews/review-key";
import {
  and,
  count,
  desc,
  eq,
  gt,
  inArray,
  isNull,
  lte,
  max,
  ne,
  or,
  sql,
  type SQL,
} from "drizzle-orm";
import type { Actor, WorkflowState } from "@/features/reviews/types";
import { getDb } from "./index";
import { awaitingStates, inboxQuerySchema, type InboxQuery } from "./inbox-query";
import { inboxEvents, inboxVisits, reviewIndex, reviewTouches } from "./schema";
import { reviewScope } from "./scope";

const inboxColumns = {
  reviewKey: reviewIndex.reviewKey,
  reviewNumber: reviewIndex.reviewNumber,
  fiscalYear: reviewIndex.fiscalYear,
  caseNumber: reviewIndex.caseNumber,
  clientName: reviewIndex.clientName,
  examiner: reviewIndex.examiner,
  state: sql<WorkflowState>`${reviewIndex.state}`,
  appointment: reviewIndex.appointment,
};

export async function touchReview(actor: Actor, reviewId: string, readVersion?: number) {
  if (!actor.staffId) return;
  const db = getDb();
  const key = isReviewKey(reviewId) ? reviewId : undefined;
  if (!key) return;
  const permitted = await db
    .select({
      reviewKey: reviewIndex.reviewKey,
      reviewNumber: reviewIndex.reviewNumber,
      fiscalYear: reviewIndex.fiscalYear,
      version: reviewIndex.version,
    })
    .from(reviewIndex)
    .where(and(eq(reviewIndex.reviewKey, key), reviewScope(actor)))
    .limit(1);
  if (!permitted.length) return;
  const now = new Date().toISOString();
  // Match snapshot saves: lock the index before the per-staff touch row.
  await db.batch([
    db
      .update(reviewIndex)
      .set({ openedAt: now })
      .where(
        and(
          eq(reviewIndex.reviewKey, key),
          eq(reviewIndex.examiner, actor.staffId),
          isNull(reviewIndex.openedAt),
        ),
      ),
    db
      .insert(reviewTouches)
      .values({
        staffId: actor.staffId,
        reviewKey: key,
        reviewNumber: permitted[0].reviewNumber,
        touchedAt: now,
        readVersion: readVersion ?? permitted[0].version,
      })
      .onConflictDoUpdate({
        target: [reviewTouches.staffId, reviewTouches.reviewKey],
        set: {
          touchedAt: sql`greatest(${reviewTouches.touchedAt}, ${now}::timestamptz)`,
          readVersion: sql`greatest(${reviewTouches.readVersion}, ${readVersion ?? permitted[0].version})`,
        },
      }),
  ]);
}

export async function inboxSeen(actor: Actor, at = new Date().toISOString()) {
  if (!actor.staffId) return;
  await getDb()
    .insert(inboxVisits)
    .values({ staffId: actor.staffId, lastSeenAt: at })
    .onConflictDoUpdate({
      target: inboxVisits.staffId,
      set: { lastSeenAt: sql`greatest(${inboxVisits.lastSeenAt}, ${at}::timestamptz)` },
    });
}

export async function readEvent(actor: Actor, id: string) {
  await getDb()
    .update(inboxEvents)
    .set({ readAt: new Date().toISOString() })
    .where(and(eq(inboxEvents.id, id), eq(inboxEvents.staffId, actor.staffId ?? "")));
}

const preferenceNames = {
  distributed: "Distributed",
  document_added: "Documents",
  changes_requested: "Kickbacks",
  approved: "Approvals",
  dropped: "Approvals",
  pec_decision: "FinalDecisions",
  appointment_changed: "Appointments",
} as const;
export function enabledEventKinds(actor: Actor, team = false): string[] {
  return Object.entries(preferenceNames)
    .filter(([, suffix]) => actor.preferences?.[`notify${team ? "Team" : ""}${suffix}`] !== false)
    .map(([kind]) => kind);
}
function notificationScope(actor: Actor): SQL {
  const own = enabledEventKinds(actor);
  const team = enabledEventKinds(actor, true);
  const kinds =
    actor.role === "supervisor"
      ? or(
          and(
            eq(reviewIndex.examiner, actor.staffId ?? ""),
            own.length ? inArray(inboxEvents.kind, own) : sql`false`,
          ),
          and(
            ne(reviewIndex.examiner, actor.staffId ?? ""),
            team.length ? inArray(inboxEvents.kind, team) : sql`false`,
          ),
        )
      : own.length
        ? inArray(inboxEvents.kind, own)
        : sql`false`;
  return and(eq(inboxEvents.staffId, actor.staffId ?? ""), reviewScope(actor), kinds)!;
}
export async function clearEvents(actor: Actor, through: string) {
  const db = getDb();
  const visibleEvents = db
    .select({ id: inboxEvents.id })
    .from(inboxEvents)
    .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, inboxEvents.reviewKey))
    .where(
      and(
        notificationScope(actor),
        isNull(inboxEvents.readAt),
        lte(inboxEvents.createdAt, through),
      ),
    );
  await db
    .update(inboxEvents)
    .set({ readAt: new Date().toISOString() })
    .where(inArray(inboxEvents.id, visibleEvents));
}

export async function queryEvents(actor: Actor, since?: string) {
  const db = getDb();
  const scope = notificationScope(actor);
  const [rows, totals] = await Promise.all([
    db
      .select({ event: inboxEvents, fiscalYear: reviewIndex.fiscalYear })
      .from(inboxEvents)
      .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, inboxEvents.reviewKey))
      .where(and(scope, since ? gt(inboxEvents.createdAt, since) : isNull(inboxEvents.readAt)))
      .orderBy(desc(inboxEvents.createdAt), desc(inboxEvents.id))
      .limit(100),
    db
      .select({ total: count() })
      .from(inboxEvents)
      .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, inboxEvents.reviewKey))
      .where(and(scope, isNull(inboxEvents.readAt))),
  ]);
  return {
    events: rows.map(({ event, fiscalYear }) => ({ ...event, fiscalYear })),
    unreadCount: totals[0]?.total ?? 0,
  };
}

/** Each task list has its own page; only slim projections and server-side totals leave the DB. */
export async function queryInbox(actor: Actor, input: Partial<InboxQuery> = {}) {
  const options = inboxQuerySchema.parse(input);
  const asOf = options.asOf ?? new Date().toISOString();
  const db = getDb();
  const visit = (
    await db
      .select()
      .from(inboxVisits)
      .where(eq(inboxVisits.staffId, actor.staffId ?? ""))
      .limit(1)
  )[0];
  const since = new Date(
    options.since ?? visit?.lastSeenAt ?? "1970-01-01T00:00:00.000Z",
  ).toISOString();
  const scope = and(
    reviewScope(actor),
    actor.role === "pec" ? eq(reviewIndex.state, "awaiting_pec_approval") : undefined,
  )!;
  const newWhere = and(scope, isNull(reviewIndex.openedAt), eq(reviewIndex.state, "new_active"))!;
  const queueWhere = and(scope, inArray(reviewIndex.state, awaitingStates(actor.role)))!;
  const touchedWhere = and(
    scope,
    lte(reviewTouches.touchedAt, asOf),
    gt(reviewTouches.touchedAt, "1970-01-01T00:00:00.000Z"),
    actor.role === "supervisor"
      ? inArray(reviewTouches.staffId, [actor.staffId ?? "", ...(actor.subordinateStaffIds ?? [])])
      : eq(reviewTouches.staffId, actor.staffId ?? ""),
  )!;
  // Aggregate BEFORE offset/count so coverage touches cannot duplicate or hide a review.
  const latestTouches = db
    .select({
      reviewKey: reviewTouches.reviewKey,
      touchedAt: max(reviewTouches.touchedAt).as("touched_at"),
    })
    .from(reviewTouches)
    .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, reviewTouches.reviewKey))
    .where(touchedWhere)
    .groupBy(reviewTouches.reviewKey)
    .as("latest_touches");
  const eventWhere = and(
    notificationScope(actor),
    gt(inboxEvents.createdAt, since),
    lte(inboxEvents.createdAt, asOf),
  )!;
  const include = (section: InboxQuery["section"]) =>
    options.section === "all" || options.section === section;
  const [
    newReviews,
    lastTouched,
    awaitingReview,
    eventRows,
    newCount,
    touchCount,
    queueCount,
    eventCount,
  ] = await Promise.all([
    include("new")
      ? db
          .select(inboxColumns)
          .from(reviewIndex)
          .where(newWhere)
          .orderBy(desc(reviewIndex.sampleMonth), reviewIndex.reviewKey)
          .limit(options.pageSize)
          .offset(options.newPage * options.pageSize)
      : Promise.resolve([]),
    include("touched")
      ? db
          .select({ review: inboxColumns, touchedAt: latestTouches.touchedAt })
          .from(latestTouches)
          .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, latestTouches.reviewKey))
          .orderBy(desc(latestTouches.touchedAt), reviewIndex.reviewKey)
          .limit(options.pageSize)
          .offset(options.touchedPage * options.pageSize)
      : Promise.resolve([]),
    include("queue")
      ? db
          .select(inboxColumns)
          .from(reviewIndex)
          .where(queueWhere)
          .orderBy(reviewIndex.fiscalYear, reviewIndex.reviewNumber, reviewIndex.reviewKey)
          .limit(options.pageSize)
          .offset(options.queuePage * options.pageSize)
      : Promise.resolve([]),
    include("events")
      ? db
          .select({ event: inboxEvents, fiscalYear: reviewIndex.fiscalYear })
          .from(inboxEvents)
          .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, inboxEvents.reviewKey))
          .where(eventWhere)
          .orderBy(desc(inboxEvents.createdAt), desc(inboxEvents.id))
          .limit(options.pageSize)
          .offset(options.eventsPage * options.pageSize)
      : Promise.resolve([]),
    include("new")
      ? db.select({ total: count() }).from(reviewIndex).where(newWhere)
      : Promise.resolve([]),
    include("touched") ? db.select({ total: count() }).from(latestTouches) : Promise.resolve([]),
    include("queue")
      ? db.select({ total: count() }).from(reviewIndex).where(queueWhere)
      : Promise.resolve([]),
    include("events")
      ? db
          .select({ total: count() })
          .from(inboxEvents)
          .innerJoin(reviewIndex, eq(reviewIndex.reviewKey, inboxEvents.reviewKey))
          .where(eventWhere)
      : Promise.resolve([]),
  ]);
  return {
    asOf,
    since,
    section: options.section,
    newReviews,
    lastTouched: lastTouched.map(({ review, touchedAt }) => ({ ...review, touchedAt })),
    awaitingReview,
    events: eventRows.map(({ event, fiscalYear }) => ({ ...event, fiscalYear })),
    lastInboxSeenAt: visit?.lastSeenAt ? new Date(visit.lastSeenAt).toISOString() : null,
    totals: {
      newReviews: newCount[0]?.total ?? 0,
      lastTouched: touchCount[0]?.total ?? 0,
      awaitingReview: queueCount[0]?.total ?? 0,
      events: eventCount[0]?.total ?? 0,
    },
  };
}

/** Response bodies of /api/inbox and /api/inbox/events. Clients import these types. */
export type InboxResponse = Awaited<ReturnType<typeof queryInbox>>;
export type EventsResponse = Awaited<ReturnType<typeof queryEvents>>;