drizzle/0003_daily_silver_samurai.sql

Linked repository file from this build.

ALTER TABLE "review_index" ADD COLUMN "county" text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE "review_index" ADD COLUMN "updated_at" timestamp with time zone DEFAULT now() NOT NULL;--> statement-breakpoint
ALTER TABLE "review_index" ADD COLUMN "routed_by" text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE "review_touches" ADD COLUMN "read_version" integer DEFAULT -1 NOT NULL;--> statement-breakpoint
ALTER TABLE "review_touches" ADD COLUMN "flagged" boolean DEFAULT false NOT NULL;--> statement-breakpoint
-- Populate the projection from durable snapshots; no review facts or rule bindings change.
UPDATE review_index AS i SET
  county = COALESCE(s.review->>'county', ''),
  updated_at = s.saved_at,
  routed_by = COALESCE((
    SELECT COALESCE(NULLIF(event->'actor'->>'name', ''), event->'actor'->>'staffId')
    FROM jsonb_array_elements(COALESCE(s.review->'activity', '[]'::jsonb)) WITH ORDINALITY AS a(event, position)
    WHERE event->>'kind' IN ('transition', 'policy_decision') AND event->>'to' = i.state
    ORDER BY position DESC LIMIT 1
  ), CASE i.examiner
    WHEN '101' THEN 'Alex Examiner' WHEN '102' THEN 'Jordan Examiner'
    WHEN '103' THEN 'Casey Examiner' WHEN '104' THEN 'Quinn Examiner'
    WHEN '105' THEN 'Taylor Examiner' WHEN '106' THEN 'Reese Examiner'
    WHEN '107' THEN 'Cameron Examiner' WHEN '108' THEN 'Drew Examiner'
    WHEN '201' THEN 'Sam Supervisor' WHEN '202' THEN 'Robin Supervisor'
    ELSE i.examiner END)
FROM (
  SELECT DISTINCT ON (review_number) review_number, review, saved_at
  FROM snap_review_snapshots ORDER BY review_number, version DESC
) AS s WHERE s.review_number = i.review_number;
--> statement-breakpoint
-- Existing visits mark only snapshots already saved at the time of that visit as read.
UPDATE review_touches AS t SET read_version = COALESCE((
  SELECT MAX(s.version) FROM snap_review_snapshots s
  WHERE s.review_number = t.review_number AND s.saved_at <= t.touched_at
), -1);