Blame · Line-by-line history
0115_scim_provisioned_by.sql
Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.
| b4ff9c0 | 1 | -- SCIM provenance for user accounts. |
| 2 | -- | |
| 3 | -- Records the organization whose SCIM connector CREATED this account, and | |
| 4 | -- only that case: accounts that already existed when a SCIM connector first | |
| 5 | -- referenced them stay NULL forever. | |
| 6 | -- | |
| 7 | -- This is what lets SCIM deprovisioning tell a shadow account (which exists | |
| 8 | -- only because an IdP made it, and is worth disabling when the IdP drops it) | |
| 9 | -- apart from a real person's pre-existing account (which an org-scoped | |
| 10 | -- credential must never be able to delete). Before this column existed, | |
| 11 | -- deprovisioning soft-deleted the target's ENTIRE platform account — | |
| 12 | -- personal repos and every other org included. | |
| 13 | -- | |
| 14 | -- Additive and idempotent: no drops, no backfill. NULL is the safe default, | |
| 15 | -- and NULL is the value that denies deletion. | |
| 16 | -- | |
| 17 | -- The statement-breakpoint markers are load-bearing, not decoration. The | |
| 18 | -- migration runner's fallback splitter breaks on a semicolon at end of line | |
| 19 | -- and is not dollar-quote aware, so it would shred the DO block below into | |
| 20 | -- invalid fragments. Every other migration here that uses a DO block marks | |
| 21 | -- its boundaries the same way. | |
| 22 | ||
| 23 | ALTER TABLE users | |
| 24 | ADD COLUMN IF NOT EXISTS scim_provisioned_by_org_id uuid; | |
| 25 | --> statement-breakpoint | |
| 26 | DO $$ | |
| 27 | BEGIN | |
| 28 | IF NOT EXISTS ( | |
| 29 | SELECT 1 FROM pg_constraint | |
| 30 | WHERE conname = 'users_scim_provisioned_by_org_id_fkey' | |
| 31 | ) THEN | |
| 32 | ALTER TABLE users | |
| 33 | ADD CONSTRAINT users_scim_provisioned_by_org_id_fkey | |
| 34 | FOREIGN KEY (scim_provisioned_by_org_id) | |
| 35 | REFERENCES organizations(id) ON DELETE SET NULL; | |
| 36 | END IF; | |
| 37 | END $$; | |
| 38 | --> statement-breakpoint | |
| 39 | CREATE INDEX IF NOT EXISTS users_scim_provisioned_by_org_id_idx | |
| 40 | ON users (scim_provisioned_by_org_id) | |
| 41 | WHERE scim_provisioned_by_org_id IS NOT NULL; |