| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- PAYMENTS PRO03 — make billing_invoices polymorphic over subject. |
| 4 | -- |
| 5 | -- PRO02 Q1 ratified the hybrid table strategy: invoices and |
| 6 | -- webhook-events go polymorphic (their UNIQUE indexes were already |
| 7 | -- subject-agnostic), org_billing_states stays per-subject. After this |
| 8 | -- migration billing_invoices carries (subject_kind, subject_id) |
| 9 | -- alongside the legacy org_id column. |
| 10 | -- |
| 11 | -- **Two-step deploy.** This migration adds the new columns and |
| 12 | -- backfills them but KEEPS org_id and its FK. A follow-up migration |
| 13 | -- (post-PRO04 deploy) drops org_id once every call site reads from |
| 14 | -- the polymorphic shape. Dropping it here would force a flag-day |
| 15 | -- deploy where every reader/writer must be in lockstep with this |
| 16 | -- migration. |
| 17 | |
| 18 | -- +goose Up |
| 19 | |
| 20 | CREATE TYPE billing_subject_kind AS ENUM ('user', 'org'); |
| 21 | |
| 22 | ALTER TABLE billing_invoices |
| 23 | ADD COLUMN subject_kind billing_subject_kind, |
| 24 | ADD COLUMN subject_id bigint; |
| 25 | |
| 26 | -- Backfill every existing row as an org invoice. Synchronous so the |
| 27 | -- NOT NULL constraints below apply against a fully-populated column. |
| 28 | UPDATE billing_invoices |
| 29 | SET subject_kind = 'org', |
| 30 | subject_id = org_id |
| 31 | WHERE subject_kind IS NULL; |
| 32 | |
| 33 | ALTER TABLE billing_invoices |
| 34 | ALTER COLUMN subject_kind SET NOT NULL, |
| 35 | ALTER COLUMN subject_id SET NOT NULL; |
| 36 | |
| 37 | -- Cross-row consistency: the legacy org_id is preserved during the |
| 38 | -- transitional window; while it exists, it must match subject_id when |
| 39 | -- subject_kind='org'. Future invoice rows (PRO04+) for users carry |
| 40 | -- org_id=NULL — relax the FK by making it nullable BEFORE this check |
| 41 | -- so existing org rows stay valid and user rows can land. |
| 42 | ALTER TABLE billing_invoices |
| 43 | ALTER COLUMN org_id DROP NOT NULL; |
| 44 | |
| 45 | ALTER TABLE billing_invoices |
| 46 | ADD CONSTRAINT billing_invoices_org_id_matches_subject CHECK ( |
| 47 | org_id IS NULL |
| 48 | OR (subject_kind = 'org' AND subject_id = org_id) |
| 49 | ); |
| 50 | |
| 51 | -- New index for the polymorphic invoice-listing queries; mirrors the |
| 52 | -- shape of the existing billing_invoices_org_created_idx. |
| 53 | CREATE INDEX billing_invoices_subject_created_idx |
| 54 | ON billing_invoices (subject_kind, subject_id, created_at DESC); |
| 55 | |
| 56 | -- +goose Down |
| 57 | |
| 58 | DROP INDEX IF EXISTS billing_invoices_subject_created_idx; |
| 59 | ALTER TABLE billing_invoices DROP CONSTRAINT IF EXISTS billing_invoices_org_id_matches_subject; |
| 60 | ALTER TABLE billing_invoices ALTER COLUMN org_id SET NOT NULL; |
| 61 | ALTER TABLE billing_invoices DROP COLUMN IF EXISTS subject_id; |
| 62 | ALTER TABLE billing_invoices DROP COLUMN IF EXISTS subject_kind; |
| 63 | DROP TYPE IF EXISTS billing_subject_kind; |
| 64 |