src/lib/db/policy-store.ts

Linked repository file from this build.

import { asc, sql } from "drizzle-orm";
import type { Actor } from "@/features/reviews/types";
import { policySchema } from "@/features/policy/schema";
import { AccessError, can } from "@/lib/auth/scope";
import { initialPolicy, type SnapPolicy } from "@/programs/snap-negative/policy";
import { getDb } from "./index";
import { snapPolicyTables } from "./policy-schema";
import { RuleError } from "@/lib/errors";

export async function listPolicies(): Promise<SnapPolicy[]> {
  const rows = await getDb()
    .select({ policy: snapPolicyTables.policy })
    .from(snapPolicyTables)
    .orderBy(asc(snapPolicyTables.effectiveFrom));
  const policies = rows.map((row) => row.policy);
  return policies.some((policy) => policy.id === initialPolicy.id)
    ? policies
    : [initialPolicy, ...policies];
}

/** Only inserts are exposed. A published effective month cannot be replaced. */
export async function publishPolicy(actor: Actor, value: unknown) {
  if (!can(actor, "report")) throw new AccessError("Your role cannot publish policy tables.");
  const policy = policySchema.parse(value);
  if (policy.effectiveFrom <= initialPolicy.effectiveFrom)
    throw new RuleError("Use an effective month after the latest published table.");
  if (policy.id === initialPolicy.id) throw new RuleError("Use a new policy ID.");
  const db = getDb();
  // The lock is a separate statement so the following date check sees the latest commit.
  const results = await db.batch([
    db
      .select({ locked: sql`pg_advisory_xact_lock(hashtext('snap-negative-policy-publish'))` })
      .from(sql`(select 1) as policy_lock`),
    db
      .insert(snapPolicyTables)
      .select(sql`
      select ${policy.id}, ${policy.effectiveFrom}, ${JSON.stringify(policy)}::jsonb,
        ${actor.staffId ?? actor.name}, ${new Date().toISOString()}::timestamptz
      where not exists (
        select 1 from ${snapPolicyTables}
        where ${snapPolicyTables.effectiveFrom} >= ${policy.effectiveFrom}
      )
    `)
      .returning(),
  ]);
  if (!results[1].length)
    throw new RuleError("Use an effective month after the latest published table.");
  return policy;
}