drizzle/0006_fiscal_year_review_keys.sql

Linked repository file from this build.

ALTER TABLE "review_index" ADD COLUMN "review_key" text;
--> statement-breakpoint
ALTER TABLE "snap_review_snapshots" ADD COLUMN "review_key" text;
--> statement-breakpoint
ALTER TABLE "review_touches" ADD COLUMN "review_key" text;
--> statement-breakpoint
ALTER TABLE "inbox_events" ADD COLUMN "review_key" text;
--> statement-breakpoint
UPDATE "review_index" SET "review_key" =
  (substring("sample_month", 1, 4)::integer + CASE WHEN substring("sample_month", 6, 2)::integer >= 10 THEN 1 ELSE 0 END)::text
  || ':' || "review_number";
--> statement-breakpoint
-- Keep all versions under the current identity, including past sample-month edits.
UPDATE "snap_review_snapshots" AS s SET "review_key" = coalesce(
  (SELECT i."review_key" FROM "review_index" AS i WHERE i."review_number" = s."review_number"),
  (SELECT
    (substring(latest."review"->>'sampleMonth', 1, 4)::integer + CASE WHEN substring(latest."review"->>'sampleMonth', 6, 2)::integer >= 10 THEN 1 ELSE 0 END)::text
    || ':' || latest."review_number"
   FROM "snap_review_snapshots" AS latest WHERE latest."review_number" = s."review_number"
   ORDER BY latest."version" DESC LIMIT 1)
);
--> statement-breakpoint
-- Keep orphan rows under a reserved key if their former review no longer exists.
UPDATE "review_touches" AS t SET "review_key" = coalesce(
  (SELECT i."review_key" FROM "review_index" AS i WHERE i."review_number" = t."review_number"),
  (SELECT s."review_key" FROM "snap_review_snapshots" AS s WHERE s."review_number" = t."review_number" ORDER BY s."version" DESC LIMIT 1),
  'legacy:' || t."review_number"
);
--> statement-breakpoint
UPDATE "inbox_events" AS e SET "review_key" = coalesce(
  (SELECT i."review_key" FROM "review_index" AS i WHERE i."review_number" = e."review_number"),
  (SELECT s."review_key" FROM "snap_review_snapshots" AS s WHERE s."review_number" = e."review_number" ORDER BY s."version" DESC LIMIT 1),
  'legacy:' || e."review_number"
);
--> statement-breakpoint
ALTER TABLE "review_index" ALTER COLUMN "review_key" SET NOT NULL;
--> statement-breakpoint
ALTER TABLE "snap_review_snapshots" ALTER COLUMN "review_key" SET NOT NULL;
--> statement-breakpoint
ALTER TABLE "review_touches" ALTER COLUMN "review_key" SET NOT NULL;
--> statement-breakpoint
ALTER TABLE "inbox_events" ALTER COLUMN "review_key" SET NOT NULL;
--> statement-breakpoint
ALTER TABLE "review_index" DROP CONSTRAINT "review_index_pkey";
--> statement-breakpoint
ALTER TABLE "review_index" ADD PRIMARY KEY ("review_key");
--> statement-breakpoint
ALTER TABLE "snap_review_snapshots" DROP CONSTRAINT "snap_review_snapshots_review_number_version_pk";
--> statement-breakpoint
ALTER TABLE "snap_review_snapshots" ADD CONSTRAINT "snap_review_snapshots_review_key_version_pk" PRIMARY KEY ("review_key", "version");
--> statement-breakpoint
ALTER TABLE "review_touches" DROP CONSTRAINT "review_touches_staff_id_review_number_pk";
--> statement-breakpoint
ALTER TABLE "review_touches" ADD CONSTRAINT "review_touches_staff_id_review_key_pk" PRIMARY KEY ("staff_id", "review_key");
--> statement-breakpoint
CREATE INDEX "review_index_review_number_idx" ON "review_index" ("review_number");