0042_account_purge_fk_set_null.sql32 lines · main

1	-- 0042_account_purge_fk_set_null — unblock GDPR hard-deletion.
2	--
3	-- `hardDeleteExpiredAccounts` (services/account-deletion.ts) runs
4	-- DELETE FROM users WHERE deleted_at < now() - 30 days
5	-- which ALWAYS failed with FK violation 23503 (silently swallowed by the
6	-- account-deletion-gc worker) because two FKs onto users.id were ON DELETE
7	-- NO ACTION:
8	-- * organizations.created_by (and NOT NULL)
9	-- * audit_logs.actor_id (already nullable)
10	-- Net effect: no expired account was ever purged.
11	--
12	-- Fix: make organizations.created_by nullable and flip both FKs to
13	-- ON DELETE SET NULL. A surviving shared org keeps its row (creator nulls
14	-- out); audit rows keep their action + timestamp (actor nulls out). The
15	-- purge transaction hard-deletes the user's own sole-owner soft-deleted
16	-- orgs first (their projects cascade via projects.org_id ON DELETE CASCADE),
17	-- then deletes the user — which now succeeds instead of raising 23503.
18	--
19	-- DROP CONSTRAINT statements list both the drizzle-style name and the
20	-- postgres-default `_fkey` name with IF EXISTS so the migration is robust
21	-- whichever name the live constraint carries.
22
23	-- organizations.created_by: drop NOT NULL, recreate FK as ON DELETE SET NULL.
24	ALTER TABLE "organizations" ALTER COLUMN "created_by" DROP NOT NULL;
25	ALTER TABLE "organizations" DROP CONSTRAINT IF EXISTS "organizations_created_by_fkey";
26	ALTER TABLE "organizations" DROP CONSTRAINT IF EXISTS "organizations_created_by_users_id_fk";
27	ALTER TABLE "organizations" ADD CONSTRAINT "organizations_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
28
29	-- audit_logs.actor_id: already nullable; recreate FK as ON DELETE SET NULL.
30	ALTER TABLE "audit_logs" DROP CONSTRAINT IF EXISTS "audit_logs_actor_id_users_id_fk";
31	ALTER TABLE "audit_logs" DROP CONSTRAINT IF EXISTS "audit_logs_actor_id_fkey";
32	ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;