src/lib/db/search-store.ts

Linked repository file from this build.

import { and, asc, count, desc, eq, sql, type SQL } from "drizzle-orm";
import { z } from "zod";
import {
  searchFiltersSchema,
  searchSortsSchema,
  type SearchFilter,
} from "@/features/reviews/search-filters";
import {
  fieldDisplayRawValues,
  getSearchField,
  isKnownSearchField,
  searchFields,
  searchPath,
  validSearchField,
} from "@/features/reviews/search-fields";
import type { Actor, WorkflowState } from "@/features/reviews/types";
import { getDb } from "./index";
import { reviewIndex, reviewListColumns, reviewSnapshots } from "./schema";
import { reviewScope } from "./scope";

const searchColumns = { ...reviewListColumns, state: sql<WorkflowState>`${reviewIndex.state}` };

const date = z
  .string()
  .regex(/^\d{4}-\d{2}-\d{2}$/)
  .optional();
export const searchSchema = z.object({
  filters: z.preprocess((input) => {
    if (typeof input !== "string") return input;
    try {
      return JSON.parse(input);
    } catch {
      return null;
    }
  }, searchFiltersSchema.default([])),
  sorts: z.preprocess((input) => {
    if (typeof input !== "string") return input;
    try {
      return JSON.parse(input);
    } catch {
      return null;
    }
  }, searchSortsSchema.default([])),
  offset: z.coerce.number().int().min(0).max(1_000_000).default(0),
  q: z.string().trim().max(200).default(""),
  quick: z.enum(["true", "false"]).default("true"),
  field: z.enum(["reviewNumber", "caseNumber", "clientName", "examiner", "appointment"]).optional(),
  from: date,
  to: date,
  caseSensitive: z.enum(["true", "false"]).default("false"),
  exact: z.enum(["true", "false"]).default("false"),
  state: z
    .enum([
      "new_active",
      "awaiting_review",
      "needs_changes",
      "awaiting_pec_approval",
      "awaiting_manager",
      "awaiting_cao_response",
      "released_to_reporting",
      "dropped",
    ])
    .optional(),
  outcome: z.enum(["", "clean", "error", "drop"]).optional(),
  sort: z
    .string()
    .refine(
      (value) => value === "relevance" || validSearchField(value),
      "Choose a review field or relevance.",
    )
    .default("relevance"),
  direction: z.enum(["asc", "desc"]).default("desc"),
  limit: z.coerce.number().int().min(1).max(100).default(30),
});
export type SearchFilters = z.infer<typeof searchSchema>;
export function escapeLike(value: string) {
  return value.replace(/[\\%_]/g, "\\$&");
}
const jsonText = sql`value #>> '{}'`;
function valuesFor(field: string) {
  return sql`jsonb_path_query(${reviewSnapshots.review}, ${searchPath(field)}::jsonpath)`;
}
function labeledValue(field: string, raw: SQL = jsonText): SQL {
  const meta = getSearchField(field);
  if (meta.choices?.length)
    return sql`CASE ${raw} ${sql.join(
      meta.choices.map((choice) => sql`WHEN ${choice.value} THEN ${choice.label}`),
      sql` `,
    )} ELSE ${raw} END`;
  if (meta.kind === "boolean")
    return sql`CASE ${raw} WHEN 'true' THEN 'Yes' WHEN 'false' THEN 'No' ELSE ${raw} END`;
  return raw;
}
function matchText(value: SQL, term: string, mode: string, sensitive = false): SQL {
  const escaped = escapeLike(term);
  const pattern =
    mode === "starts"
      ? `${escaped}%`
      : mode === "ends"
        ? `%${escaped}`
        : mode === "exact"
          ? escaped
          : `%${escaped}%`;
  return sensitive ? sql`${value} LIKE ${pattern}` : sql`${value} ILIKE ${pattern}`;
}
function numericValue(raw: SQL) {
  return sql`CASE WHEN ${raw} ~ '^-?[0-9]+([.][0-9]+)?$' THEN (${raw})::numeric ELSE NULL END`;
}
function temporalValue(raw: SQL, field: string): SQL {
  const kind = getSearchField(field).kind;
  const type = kind === "date" ? "date" : field === "appointment" ? "timestamp" : "timestamptz";
  return sql`CASE WHEN pg_input_is_valid(${raw}, ${type}) THEN (${raw})::${sql.raw(type)} ELSE NULL END`;
}
function filterCondition(row: SearchFilter, sensitive = false): SQL | undefined {
  const mode = row.mode ?? (row.field === "sampleMonth" ? "between" : "contains");
  const meta = getSearchField(row.field);
  let condition: SQL;
  if (mode === "empty" || mode === "present") {
    const present = sql`EXISTS (SELECT 1 FROM ${valuesFor(row.field)} AS value WHERE value NOT IN ('null'::jsonb, '""'::jsonb, '[]'::jsonb, '{}'::jsonb))`;
    condition = mode === "empty" ? sql`NOT (${present})` : present;
  } else if (mode === "between") {
    if (!row.from && !row.through) return undefined;
    const numeric =
      meta.kind === "number" ||
      (!isKnownSearchField(row.field) &&
        [row.from, row.through].filter(Boolean).every((value) => /^-?\d+(?:\.\d+)?$/.test(value!)));
    const temporal = meta.kind === "datetime" || meta.kind === "date";
    const value = numeric
      ? numericValue(jsonText)
      : temporal
        ? temporalValue(jsonText, row.field)
        : jsonText;
    const bounds: SQL[] = [];
    if (row.from) bounds.push(sql`${value} >= ${numeric ? Number(row.from) : row.from}`);
    if (row.through) bounds.push(sql`${value} <= ${numeric ? Number(row.through) : row.through}`);
    condition = sql`EXISTS (SELECT 1 FROM ${valuesFor(row.field)} AS value WHERE ${and(...bounds)})`;
  } else {
    const term = row.term ?? "";
    if (!term && mode !== "exact") return undefined;
    let match =
      (meta.kind === "datetime" || meta.kind === "date") && mode === "exact" && term
        ? sql`${temporalValue(jsonText, row.field)} = ${term}`
        : meta.kind === "number" && mode === "exact"
          ? sql`${numericValue(jsonText)} = ${term === "" ? null : Number(term)}`
          : sql`(${matchText(jsonText, term, mode, sensitive)} OR ${matchText(labeledValue(row.field), term, mode, sensitive)})`;
    if (!isKnownSearchField(row.field) && mode === "exact" && /^-?\d+(?:\.\d+)?$/.test(term))
      match = sql`CASE WHEN jsonb_typeof(value) = 'number' THEN ${numericValue(jsonText)} = ${Number(term)} ELSE ${match} END`;
    if (row.field === "clientName" && mode === "starts")
      match = sql`(${match} OR ${matchText(sql`regexp_replace(trim(${jsonText}), '^.*[[:space:]]+', '')`, term, mode, sensitive)})`;
    condition = sql`EXISTS (SELECT 1 FROM ${valuesFor(row.field)} AS value WHERE ${match})`;
  }
  return row.exclude ? sql`NOT (${condition})` : condition;
}
const scalarPath = '$.** ? (@.type() != "object" && @.type() != "array" && @.type() != "null")';
function quickBag(): SQL {
  return sql`concat_ws(' ', ${reviewIndex.reviewNumber}, ${reviewIndex.caseNumber}, ${reviewIndex.clientName}, ${reviewIndex.examiner}, ${labeledValue("examiner", sql`${reviewIndex.examiner}`)}, ${reviewIndex.appointment})`;
}
function queryBag(): SQL {
  // Keys and binary PDF contents are excluded; labels make common coded identity/answer fields readable.
  const labels = searchFields
    .filter(
      (field) =>
        field.choices && (!field.key.includes(".") || field.key.startsWith("facts.answers.")),
    )
    .map(
      (field) =>
        sql`(SELECT string_agg(${labeledValue(field.key)}, ' ') FROM ${valuesFor(field.key)} AS value)`,
    );
  return sql`concat_ws(' ', (SELECT string_agg(${jsonText}, ' ') FROM jsonb_path_query(${reviewSnapshots.review}, ${scalarPath}::jsonpath) AS value), ${sql.join(labels, sql`, `)})`;
}
export function searchWhere(actor: Actor, filters: SearchFilters): SQL {
  const conditions: SQL[] = [reviewScope(actor)];
  for (const row of filters.filters) {
    const condition = filterCondition(row);
    if (condition) conditions.push(condition);
  }
  if (filters.q) {
    if (filters.field)
      conditions.push(
        filterCondition(
          {
            field: filters.field,
            term: filters.q,
            mode: filters.exact === "true" ? "exact" : "contains",
          },
          filters.caseSensitive === "true",
        )!,
      );
    else if (filters.exact === "true")
      conditions.push(
        sql`EXISTS (SELECT 1 FROM jsonb_path_query(${reviewSnapshots.review}, ${scalarPath}::jsonpath) AS value WHERE ${matchText(jsonText, filters.q, "exact", filters.caseSensitive === "true")})`,
      );
    else
      for (const word of filters.q.split(/\s+/))
        conditions.push(
          matchText(
            filters.quick === "true" ? quickBag() : queryBag(),
            word,
            "contains",
            filters.caseSensitive === "true",
          ),
        );
  }
  if (filters.from || filters.to)
    conditions.push(
      filterCondition({
        field: "sampleMonth",
        from: filters.from?.slice(0, 7),
        through: filters.to?.slice(0, 7),
      })!,
    );
  if (filters.state) conditions.push(eq(reviewIndex.state, filters.state));
  if (filters.outcome !== undefined) conditions.push(eq(reviewIndex.outcome, filters.outcome));
  return and(...conditions)!;
}
function relevance(filters: SearchFilters): SQL<number> {
  if (!filters.q) return sql<number>`0::integer`;
  // Prefer exact identity hits, then prefixes, then other literal scalar matches; stable ties use the fiscal-year review key.
  const fields = [
    reviewIndex.reviewNumber,
    reviewIndex.caseNumber,
    reviewIndex.clientName,
    reviewIndex.examiner,
  ];
  const terms = [...new Set([filters.q, ...filters.q.split(/\s+/)])];
  return sql<number>`(${sql.join(
    terms.flatMap((term) =>
      fields.map((field) => {
        const value = sql`${field}`;
        return sql`CASE WHEN ${matchText(value, term, "exact")} THEN 100 WHEN ${matchText(value, term, "starts")} THEN 20 WHEN ${matchText(value, term, "contains")} THEN 5 ELSE 0 END`;
      }),
    ),
    sql` + `,
  )})`;
}
const currentSnapshot = and(
  eq(reviewSnapshots.reviewKey, reviewIndex.reviewKey),
  eq(reviewSnapshots.version, reviewIndex.version),
);
function selectedSorts(filters: SearchFilters) {
  return filters.sorts.length
    ? filters.sorts
    : filters.sort === "relevance"
      ? []
      : [{ field: filters.sort, direction: filters.direction }];
}
function activeSearchFields(filters: SearchFilters) {
  return [
    ...new Set([
      ...filters.filters.map((filter) => filter.field),
      ...selectedSorts(filters).map((sort) => sort.field),
    ]),
  ];
}
const indexedSortColumns = {
  reviewNumber: reviewIndex.reviewNumber,
  caseNumber: reviewIndex.caseNumber,
  clientName: reviewIndex.clientName,
  examiner: reviewIndex.examiner,
  supervisor: reviewIndex.supervisor,
  program: reviewIndex.program,
  sampleMonth: reviewIndex.sampleMonth,
  state: reviewIndex.state,
  outcome: reviewIndex.outcome,
  county: reviewIndex.county,
  appointment: reviewIndex.appointment,
};
function indexedSortColumn(field: string) {
  return Object.hasOwn(indexedSortColumns, field)
    ? indexedSortColumns[field as keyof typeof indexedSortColumns]
    : undefined;
}
function snapshotNeeds(filters: SearchFilters) {
  const where =
    filters.filters.length > 0 ||
    Boolean(filters.from || filters.to) ||
    Boolean(filters.q && (filters.quick === "false" || filters.exact === "true" || filters.field));
  return {
    where,
    rows: where || selectedSorts(filters).some((sort) => !indexedSortColumn(sort.field)),
  };
}
function projectedSearchValues(fields: string[]): SQL<Record<string, unknown[]>> {
  if (!fields.length) return sql<Record<string, unknown[]>>`'{}'::jsonb`;
  return sql<Record<string, unknown[]>>`jsonb_build_object(${sql.join(
    fields.flatMap((field) => [
      sql`${field}::text`,
      sql`jsonb_path_query_array(${reviewSnapshots.review}, ${searchPath(field)}::jsonpath)`,
    ]),
    sql`, `,
  )})`;
}
function sortValue(field: string): SQL {
  const meta = getSearchField(field);
  const indexed = indexedSortColumn(field);
  if (indexed) {
    const raw = sql`${indexed}`;
    return meta.kind === "number"
      ? numericValue(raw)
      : meta.kind === "date" || meta.kind === "datetime"
        ? temporalValue(raw, field)
        : sql`lower(${labeledValue(field, raw)})`;
  }
  if (!isKnownSearchField(field))
    return sql`(SELECT value FROM ${valuesFor(field)} AS value WHERE value <> 'null'::jsonb ORDER BY value LIMIT 1)`;
  const value =
    meta.kind === "number"
      ? numericValue(jsonText)
      : meta.kind === "date" || meta.kind === "datetime"
        ? temporalValue(jsonText, field)
        : sql`lower(${labeledValue(field)})`;
  // Repeated fields sort on their smallest present value in both directions.
  return sql`(SELECT min(${value}) FROM ${valuesFor(field)} AS value WHERE value <> 'null'::jsonb)`;
}
export async function querySearch(actor: Actor, filters: SearchFilters) {
  const score = relevance(filters);
  const sorts = selectedSorts(filters);
  const order = sorts.length
    ? sorts.map((sort) => {
        const value = sortValue(sort.field);
        return sql`${sort.direction === "asc" ? asc(value) : desc(value)} NULLS LAST`;
      })
    : [desc(score)];
  const active = activeSearchFields(filters);
  const needs = snapshotNeeds(filters);
  const rows = needs.rows
    ? await getDb()
        .select({ index: searchColumns, values: projectedSearchValues(active), relevance: score })
        .from(reviewIndex)
        .innerJoin(reviewSnapshots, currentSnapshot)
        .where(searchWhere(actor, filters))
        .orderBy(...order, asc(reviewIndex.reviewKey))
        .limit(filters.limit)
        .offset(filters.offset)
    : (
        await getDb()
          .select({ index: searchColumns, relevance: score })
          .from(reviewIndex)
          .where(searchWhere(actor, filters))
          .orderBy(...order, asc(reviewIndex.reviewKey))
          .limit(filters.limit)
          .offset(filters.offset)
      ).map((row) => ({ ...row, values: {} as Record<string, unknown[]> }));
  return rows.map(({ index, values, relevance: rank }) => ({
    ...index,
    examinerName:
      getSearchField("examiner").choices?.find((choice) => choice.value === index.examiner)
        ?.label ?? index.examiner,
    relevance: Number(rank),
    values: Object.fromEntries(
      active.map((field) => [
        field,
        fieldDisplayRawValues(
          values[field] ??
            (Object.hasOwn(index, field) ? [index[field as keyof typeof index]] : []),
          field,
        ).join("; ") || "Not set",
      ]),
    ),
  }));
}
export async function querySearchPage(actor: Actor, filters: SearchFilters) {
  const needs = snapshotNeeds(filters);
  const countQuery = getDb().select({ total: count() }).from(reviewIndex);
  const [reviews, totals] = await Promise.all([
    querySearch(actor, filters),
    (needs.where ? countQuery.innerJoin(reviewSnapshots, currentSnapshot) : countQuery).where(
      searchWhere(actor, filters),
    ),
  ]);
  return { reviews, total: totals[0]?.total ?? 0 };
}

/** Response body of /api/search. Clients import this type. */
export type SearchResponse = Awaited<ReturnType<typeof querySearchPage>>;