| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- PAYMENTS PRO03 — add subject audit columns to billing_webhook_events. |
| 4 | -- |
| 5 | -- The webhook idempotency table is already subject-agnostic at the |
| 6 | -- data-model level: it dedupes by (provider, provider_event_id) and |
| 7 | -- has no org FK. This migration only adds (subject_kind, subject_id) |
| 8 | -- audit columns so future operator queries can answer "which user/org |
| 9 | -- did this event apply to?" without re-parsing the payload. |
| 10 | -- |
| 11 | -- Nullable by design: |
| 12 | -- - Legacy rows (pre-PRO04) have no resolved subject — they predate |
| 13 | -- the metadata convention. |
| 14 | -- - Webhook events that fail to resolve a subject (corrupted |
| 15 | -- metadata, customer-id not found on either side) still get a |
| 16 | -- receipt row so they aren't replayed forever. Such rows carry |
| 17 | -- NULL subject and a non-empty process_error. |
| 18 | |
| 19 | -- +goose Up |
| 20 | |
| 21 | ALTER TABLE billing_webhook_events |
| 22 | ADD COLUMN subject_kind billing_subject_kind, |
| 23 | ADD COLUMN subject_id bigint; |
| 24 | |
| 25 | -- Both-or-neither: a receipt row either has a resolved subject (both |
| 26 | -- columns populated) or doesn't (both NULL). Mixed state means a bug |
| 27 | -- in the resolver. |
| 28 | ALTER TABLE billing_webhook_events |
| 29 | ADD CONSTRAINT billing_webhook_events_subject_both_or_neither CHECK ( |
| 30 | (subject_kind IS NULL AND subject_id IS NULL) |
| 31 | OR (subject_kind IS NOT NULL AND subject_id IS NOT NULL) |
| 32 | ); |
| 33 | |
| 34 | -- Operator-query index for "events that did resolve a subject." |
| 35 | CREATE INDEX billing_webhook_events_subject_idx |
| 36 | ON billing_webhook_events (subject_kind, subject_id, received_at DESC) |
| 37 | WHERE subject_kind IS NOT NULL; |
| 38 | |
| 39 | -- +goose Down |
| 40 | |
| 41 | DROP INDEX IF EXISTS billing_webhook_events_subject_idx; |
| 42 | ALTER TABLE billing_webhook_events DROP CONSTRAINT IF EXISTS billing_webhook_events_subject_both_or_neither; |
| 43 | ALTER TABLE billing_webhook_events DROP COLUMN IF EXISTS subject_id; |
| 44 | ALTER TABLE billing_webhook_events DROP COLUMN IF EXISTS subject_kind; |
| 45 |