konnosDashboard
0044_account_purge_fk_sweep.sql91 lines · main
1-- 0044_account_purge_fk_sweep — finish the job 0042 started.
2--
3-- 0042 flipped TWO foreign keys onto users.id to ON DELETE SET NULL
4-- (organizations.created_by, audit_logs.actor_id) so the GDPR account
5-- hard-delete (services/account-deletion.ts → hardDeleteExpiredAccounts)
6-- could complete instead of raising FK violation 23503. But the SAME
7-- "blocks-the-purge" class of FK was left NO ACTION on 7+ OTHER tables, so
8-- the DELETE FROM users still fails for any user who created an api key,
9-- sdk key, mcp key, env var, tenant secret, deployment, or invitation, OR
10-- whose org owns a project that audit_logs.project_id still references.
11--
12-- This migration walks EVERY remaining `*_by` / `project_id` FK of that
13-- class and flips it to ON DELETE SET NULL (audit Theme 0 — "fix it
14-- everywhere, not in one place"). For the three keys that were also NOT
15-- NULL (api_keys / briven_auth_sdk_keys / mcp_keys created_by) we DROP NOT
16-- NULL first — a key stays scoped to its project; only the creator
17-- attribution is severed when that user is purged.
18--
19-- Each DROP CONSTRAINT lists both the drizzle-style `_users_id_fk` /
20-- `_projects_id_fk` name AND the postgres-default `_fkey` name with
21-- IF EXISTS, so the migration is robust whichever name the live constraint
22-- carries (org_invitations.invited_by was created as an inline column
23-- REFERENCES, so its live name is the `_fkey` form).
24--
25-- Also adds the project_members(user_id) index used by the sole-owner
26-- membership sweep + access checks (the composite PK is
27-- (project_id, user_id), so user_id alone was unindexed).
28
29-- api_keys.created_by: drop NOT NULL, recreate FK as ON DELETE SET NULL.
30ALTER TABLE "api_keys" ALTER COLUMN "created_by" DROP NOT NULL;
31ALTER TABLE "api_keys" DROP CONSTRAINT IF EXISTS "api_keys_created_by_users_id_fk";
32ALTER TABLE "api_keys" DROP CONSTRAINT IF EXISTS "api_keys_created_by_fkey";
33ALTER TABLE "api_keys" ADD CONSTRAINT "api_keys_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
34--> statement-breakpoint
35
36-- briven_auth_sdk_keys.created_by: drop NOT NULL, recreate FK as ON DELETE SET NULL.
37ALTER TABLE "briven_auth_sdk_keys" ALTER COLUMN "created_by" DROP NOT NULL;
38ALTER TABLE "briven_auth_sdk_keys" DROP CONSTRAINT IF EXISTS "briven_auth_sdk_keys_created_by_users_id_fk";
39ALTER TABLE "briven_auth_sdk_keys" DROP CONSTRAINT IF EXISTS "briven_auth_sdk_keys_created_by_fkey";
40ALTER TABLE "briven_auth_sdk_keys" ADD CONSTRAINT "briven_auth_sdk_keys_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
41--> statement-breakpoint
42
43-- mcp_keys.created_by: drop NOT NULL, recreate FK as ON DELETE SET NULL.
44ALTER TABLE "mcp_keys" ALTER COLUMN "created_by" DROP NOT NULL;
45ALTER TABLE "mcp_keys" DROP CONSTRAINT IF EXISTS "mcp_keys_created_by_users_id_fk";
46ALTER TABLE "mcp_keys" DROP CONSTRAINT IF EXISTS "mcp_keys_created_by_fkey";
47ALTER TABLE "mcp_keys" ADD CONSTRAINT "mcp_keys_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
48--> statement-breakpoint
49
50-- audit_logs.project_id: recreate FK as ON DELETE SET NULL (sibling of the
51-- actor_id FK that 0042 already fixed) so a project cascade during a purge
52-- doesn't block on retained audit rows. Column is already nullable.
53ALTER TABLE "audit_logs" DROP CONSTRAINT IF EXISTS "audit_logs_project_id_projects_id_fk";
54ALTER TABLE "audit_logs" DROP CONSTRAINT IF EXISTS "audit_logs_project_id_fkey";
55ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE set null ON UPDATE no action;
56--> statement-breakpoint
57
58-- deployments.triggered_by: recreate FK as ON DELETE SET NULL. Already nullable.
59ALTER TABLE "deployments" DROP CONSTRAINT IF EXISTS "deployments_triggered_by_users_id_fk";
60ALTER TABLE "deployments" DROP CONSTRAINT IF EXISTS "deployments_triggered_by_fkey";
61ALTER TABLE "deployments" ADD CONSTRAINT "deployments_triggered_by_users_id_fk" FOREIGN KEY ("triggered_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
62--> statement-breakpoint
63
64-- project_invitations.invited_by: recreate FK as ON DELETE SET NULL. Already nullable.
65ALTER TABLE "project_invitations" DROP CONSTRAINT IF EXISTS "project_invitations_invited_by_users_id_fk";
66ALTER TABLE "project_invitations" DROP CONSTRAINT IF EXISTS "project_invitations_invited_by_fkey";
67ALTER TABLE "project_invitations" ADD CONSTRAINT "project_invitations_invited_by_users_id_fk" FOREIGN KEY ("invited_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
68--> statement-breakpoint
69
70-- org_invitations.invited_by: recreate FK as ON DELETE SET NULL. Already
71-- nullable; live constraint is the inline-column `_fkey` form.
72ALTER TABLE "org_invitations" DROP CONSTRAINT IF EXISTS "org_invitations_invited_by_users_id_fk";
73ALTER TABLE "org_invitations" DROP CONSTRAINT IF EXISTS "org_invitations_invited_by_fkey";
74ALTER TABLE "org_invitations" ADD CONSTRAINT "org_invitations_invited_by_users_id_fk" FOREIGN KEY ("invited_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
75--> statement-breakpoint
76
77-- project_env_vars.created_by: recreate FK as ON DELETE SET NULL. Already nullable.
78ALTER TABLE "project_env_vars" DROP CONSTRAINT IF EXISTS "project_env_vars_created_by_users_id_fk";
79ALTER TABLE "project_env_vars" DROP CONSTRAINT IF EXISTS "project_env_vars_created_by_fkey";
80ALTER TABLE "project_env_vars" ADD CONSTRAINT "project_env_vars_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
81--> statement-breakpoint
82
83-- tenant_secrets.created_by: recreate FK as ON DELETE SET NULL. Already nullable.
84ALTER TABLE "tenant_secrets" DROP CONSTRAINT IF EXISTS "tenant_secrets_created_by_users_id_fk";
85ALTER TABLE "tenant_secrets" DROP CONSTRAINT IF EXISTS "tenant_secrets_created_by_fkey";
86ALTER TABLE "tenant_secrets" ADD CONSTRAINT "tenant_secrets_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE set null ON UPDATE no action;
87--> statement-breakpoint
88
89-- project_members(user_id) reverse-lookup index (composite PK leads with
90-- project_id, so user_id alone was unindexed).
91CREATE INDEX IF NOT EXISTS "project_members_user_id_idx" ON "project_members" USING btree ("user_id");