drizzle/0007_opaque_review_identity.sql

Linked repository file from this build.

-- One mapping for every reference, including historical snapshots and orphan events.
CREATE TEMP TABLE review_identity_map ON COMMIT DROP AS
SELECT old_key, gen_random_uuid() AS new_key FROM (
  SELECT review_key AS old_key FROM review_index
  UNION SELECT review_key FROM snap_review_snapshots
  UNION SELECT review_key FROM inbox_events
  UNION SELECT review_key FROM review_touches
) keys;
--> statement-breakpoint
CREATE UNIQUE INDEX ON review_identity_map(old_key);
--> statement-breakpoint
UPDATE snap_review_snapshots s SET review_key = m.new_key::text,
  review = jsonb_set(s.review, '{id}', to_jsonb(m.new_key::text))
FROM review_identity_map m WHERE s.review_key = m.old_key;
--> statement-breakpoint
UPDATE review_index r SET review_key = m.new_key::text FROM review_identity_map m WHERE r.review_key = m.old_key;
--> statement-breakpoint
UPDATE inbox_events e SET review_key = m.new_key::text FROM review_identity_map m WHERE e.review_key = m.old_key;
--> statement-breakpoint
UPDATE review_touches t SET review_key = m.new_key::text FROM review_identity_map m WHERE t.review_key = m.old_key;
--> statement-breakpoint
ALTER TABLE snap_review_snapshots ALTER COLUMN review_key TYPE uuid USING review_key::uuid;
--> statement-breakpoint
ALTER TABLE review_index ALTER COLUMN review_key TYPE uuid USING review_key::uuid;
--> statement-breakpoint
ALTER TABLE inbox_events ALTER COLUMN review_key TYPE uuid USING review_key::uuid;
--> statement-breakpoint
ALTER TABLE review_touches ALTER COLUMN review_key TYPE uuid USING review_key::uuid;
--> statement-breakpoint
ALTER TABLE review_index ADD COLUMN fiscal_year integer;
--> statement-breakpoint
UPDATE review_index SET fiscal_year = substring(sample_month, 1, 4)::integer + CASE WHEN substring(sample_month, 6, 2)::integer >= 10 THEN 1 ELSE 0 END;
--> statement-breakpoint
ALTER TABLE review_index ALTER COLUMN fiscal_year SET NOT NULL;
--> statement-breakpoint
CREATE UNIQUE INDEX review_index_business_identity_idx ON review_index(program, fiscal_year, review_number);