| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- PAYMENTS PRO03 — user billing state table. |
| 4 | -- |
| 5 | -- Mirrors org_billing_states minus the seat-specific columns |
| 6 | -- (billable_seats, seat_snapshot_at). Pro is a single-seat plan by |
| 7 | -- design; there's no seat reconciliation worker for users. The |
| 8 | -- billing_subscription_status and billing_lock_reason enums from |
| 9 | -- 0061 are subject-agnostic and reused as-is. |
| 10 | -- |
| 11 | -- Stripe customer-id namespace is global per Stripe account; the |
| 12 | -- partial-unique indexes on each table (org_billing_states + |
| 13 | -- user_billing_states) prevent a single customer-id from existing |
| 14 | -- on both tables. Defensive cross-table validation lands in PRO04's |
| 15 | -- webhook handler. |
| 16 | |
| 17 | -- +goose Up |
| 18 | |
| 19 | CREATE TABLE user_billing_states ( |
| 20 | user_id bigint PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE, |
| 21 | provider billing_provider NOT NULL DEFAULT 'stripe', |
| 22 | stripe_customer_id text, |
| 23 | stripe_subscription_id text, |
| 24 | stripe_subscription_item_id text, |
| 25 | plan user_plan NOT NULL DEFAULT 'free', |
| 26 | subscription_status billing_subscription_status NOT NULL DEFAULT 'none', |
| 27 | current_period_start timestamptz, |
| 28 | current_period_end timestamptz, |
| 29 | cancel_at_period_end boolean NOT NULL DEFAULT false, |
| 30 | trial_end timestamptz, |
| 31 | past_due_at timestamptz, |
| 32 | canceled_at timestamptz, |
| 33 | locked_at timestamptz, |
| 34 | lock_reason billing_lock_reason, |
| 35 | grace_until timestamptz, |
| 36 | last_webhook_event_id text NOT NULL DEFAULT '', |
| 37 | created_at timestamptz NOT NULL DEFAULT now(), |
| 38 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 39 | |
| 40 | CONSTRAINT user_billing_states_customer_id_not_blank CHECK ( |
| 41 | stripe_customer_id IS NULL OR char_length(stripe_customer_id) > 0 |
| 42 | ), |
| 43 | CONSTRAINT user_billing_states_subscription_id_not_blank CHECK ( |
| 44 | stripe_subscription_id IS NULL OR char_length(stripe_subscription_id) > 0 |
| 45 | ), |
| 46 | CONSTRAINT user_billing_states_subscription_item_id_not_blank CHECK ( |
| 47 | stripe_subscription_item_id IS NULL OR char_length(stripe_subscription_item_id) > 0 |
| 48 | ), |
| 49 | CONSTRAINT user_billing_states_lock_reason_requires_locked CHECK ( |
| 50 | lock_reason IS NULL OR locked_at IS NOT NULL |
| 51 | ), |
| 52 | CONSTRAINT user_billing_states_grace_requires_locked CHECK ( |
| 53 | grace_until IS NULL OR locked_at IS NOT NULL |
| 54 | ), |
| 55 | CONSTRAINT user_billing_states_period_order CHECK ( |
| 56 | current_period_start IS NULL |
| 57 | OR current_period_end IS NULL |
| 58 | OR current_period_start <= current_period_end |
| 59 | ) |
| 60 | ); |
| 61 | |
| 62 | CREATE UNIQUE INDEX user_billing_states_stripe_customer_unique |
| 63 | ON user_billing_states (stripe_customer_id) |
| 64 | WHERE stripe_customer_id IS NOT NULL; |
| 65 | |
| 66 | CREATE UNIQUE INDEX user_billing_states_stripe_subscription_unique |
| 67 | ON user_billing_states (stripe_subscription_id) |
| 68 | WHERE stripe_subscription_id IS NOT NULL; |
| 69 | |
| 70 | CREATE UNIQUE INDEX user_billing_states_stripe_subscription_item_unique |
| 71 | ON user_billing_states (stripe_subscription_item_id) |
| 72 | WHERE stripe_subscription_item_id IS NOT NULL; |
| 73 | |
| 74 | CREATE INDEX user_billing_states_status_idx |
| 75 | ON user_billing_states (subscription_status, updated_at DESC); |
| 76 | |
| 77 | CREATE INDEX user_billing_states_locked_idx |
| 78 | ON user_billing_states (locked_at) |
| 79 | WHERE locked_at IS NOT NULL; |
| 80 | |
| 81 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON user_billing_states |
| 82 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 83 | |
| 84 | -- +goose Down |
| 85 | |
| 86 | DROP TRIGGER IF EXISTS set_updated_at ON user_billing_states; |
| 87 | DROP INDEX IF EXISTS user_billing_states_locked_idx; |
| 88 | DROP INDEX IF EXISTS user_billing_states_status_idx; |
| 89 | DROP INDEX IF EXISTS user_billing_states_stripe_subscription_item_unique; |
| 90 | DROP INDEX IF EXISTS user_billing_states_stripe_subscription_unique; |
| 91 | DROP INDEX IF EXISTS user_billing_states_stripe_customer_unique; |
| 92 | DROP TABLE IF EXISTS user_billing_states; |
| 93 |