src/lib/db/schema.ts

Linked repository file from this build.

import { getTableColumns } from "drizzle-orm";
import type { ReportRow } from "@/features/reports/report-data";
import {
  boolean,
  uuid,
  uniqueIndex,
  index,
  integer,
  jsonb,
  pgTable,
  primaryKey,
  text,
  timestamp,
} from "drizzle-orm/pg-core";
import type { SnapReview } from "@/programs/snap-negative/types";
import type { ActorRef } from "@/lib/auth/actor";

/** Append-only live review snapshots; unique versions make stale writes fail atomically. */
export const reviewSnapshots = pgTable(
  "snap_review_snapshots",
  {
    reviewKey: uuid("review_key").notNull(),
    reviewNumber: text("review_number").notNull(),
    version: integer("version").notNull(),
    requestId: text("request_id").notNull().unique(),
    review: jsonb("review").$type<SnapReview>().notNull(),
    actor: jsonb("actor").$type<ActorRef>().notNull(),
    savedAt: timestamp("saved_at", { withTimezone: true, mode: "string" }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.reviewKey, table.version] })],
);

/** Small, indexed projection for queues and search; facts remain in snapshots. */
export const reviewIndex = pgTable(
  "review_index",
  {
    reviewKey: uuid("review_key").primaryKey(),
    reviewNumber: text("review_number").notNull(),
    caseNumber: text("case_number").notNull(),
    clientName: text("client_name").notNull(),
    examiner: text("examiner").notNull(),
    supervisor: text("supervisor").notNull(),
    program: text("program").notNull(),
    sampleMonth: text("sample_month").notNull(),
    fiscalYear: integer("fiscal_year").notNull(),
    state: text("state").notNull(),
    outcome: text("outcome").notNull(),
    report: jsonb("report")
      .$type<Pick<ReportRow, "eligible" | "variances"> & { transmittalEligible?: boolean }>()
      .notNull(),
    appointment: text("appointment"),
    county: text("county").notNull().default(""),
    updatedAt: timestamp("updated_at", { withTimezone: true, mode: "string" })
      .notNull()
      .defaultNow(),
    routedBy: text("routed_by").notNull().default(""),
    openedAt: timestamp("opened_at", { withTimezone: true, mode: "string" }),
    version: integer("version").notNull(),
  },
  (table) => [
    uniqueIndex("review_index_business_identity_idx").on(
      table.program,
      table.fiscalYear,
      table.reviewNumber,
    ),
    index("review_index_review_number_idx").on(table.reviewNumber),
    index("review_index_examiner_state_idx").on(table.examiner, table.state),
    index("review_index_program_month_idx").on(table.program, table.sampleMonth),
  ],
);
export const inboxEvents = pgTable(
  "inbox_events",
  {
    id: text("id").primaryKey(),
    reviewKey: uuid("review_key").notNull(),
    reviewNumber: text("review_number").notNull(),
    staffId: text("staff_id").notNull(),
    kind: text("kind").notNull(),
    actorRole: text("actor_role").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true, mode: "string" }).notNull(),
    readAt: timestamp("read_at", { withTimezone: true, mode: "string" }),
  },
  (table) => [
    index("inbox_events_recipient_time_idx").on(table.staffId, table.createdAt),
    index("inbox_events_recipient_read_idx").on(table.staffId, table.readAt),
  ],
);
export const inboxVisits = pgTable("inbox_visits", {
  staffId: text("staff_id").primaryKey(),
  lastSeenAt: timestamp("last_seen_at", { withTimezone: true, mode: "string" }).notNull(),
});
export const reviewTouches = pgTable(
  "review_touches",
  {
    staffId: text("staff_id").notNull(),
    reviewKey: uuid("review_key").notNull(),
    reviewNumber: text("review_number").notNull(),
    touchedAt: timestamp("touched_at", { withTimezone: true, mode: "string" }).notNull(),
    readVersion: integer("read_version").notNull().default(-1),
    flagged: boolean("flagged").notNull().default(false),
  },
  (table) => [
    primaryKey({ columns: [table.staffId, table.reviewKey] }),
    index("review_touches_staff_time_idx").on(table.staffId, table.touchedAt),
  ],
);

// Report-only data must not expand inbox and search responses.
const { report: _report, ...reviewListColumns } = getTableColumns(reviewIndex);
export { reviewListColumns };

export { snapPolicyTables } from "./policy-schema";