shithub Public

Watch 1 Fork 0 Star 0
MySQL · 10717 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- PAYMENTS SP02 — local billing domain.
4 --
5 -- Stripe remains the payment source of truth, but shithub needs local
6 -- state for entitlement checks, UI summaries, idempotent webhook
7 -- processing, and seat snapshots. These tables deliberately store
8 -- provider IDs and invoice metadata only; card data never touches
9 -- shithub.
10
11 -- +goose Up
12
13 CREATE TYPE billing_provider AS ENUM ('stripe');
14
15 CREATE TYPE billing_subscription_status AS ENUM (
16 'none',
17 'incomplete',
18 'trialing',
19 'active',
20 'past_due',
21 'canceled',
22 'unpaid',
23 'paused'
24 );
25
26 CREATE TYPE billing_lock_reason AS ENUM (
27 'past_due',
28 'canceled',
29 'unpaid',
30 'manual'
31 );
32
33 CREATE TYPE billing_invoice_status AS ENUM (
34 'draft',
35 'open',
36 'paid',
37 'void',
38 'uncollectible'
39 );
40
41 CREATE TABLE org_billing_states (
42 org_id bigint PRIMARY KEY REFERENCES orgs(id) ON DELETE CASCADE,
43 provider billing_provider NOT NULL DEFAULT 'stripe',
44 stripe_customer_id text,
45 stripe_subscription_id text,
46 stripe_subscription_item_id text,
47 plan org_plan NOT NULL DEFAULT 'free',
48 subscription_status billing_subscription_status NOT NULL DEFAULT 'none',
49 billable_seats integer NOT NULL DEFAULT 0,
50 seat_snapshot_at timestamptz,
51 current_period_start timestamptz,
52 current_period_end timestamptz,
53 cancel_at_period_end boolean NOT NULL DEFAULT false,
54 trial_end timestamptz,
55 past_due_at timestamptz,
56 canceled_at timestamptz,
57 locked_at timestamptz,
58 lock_reason billing_lock_reason,
59 grace_until timestamptz,
60 last_webhook_event_id text NOT NULL DEFAULT '',
61 created_at timestamptz NOT NULL DEFAULT now(),
62 updated_at timestamptz NOT NULL DEFAULT now(),
63
64 CONSTRAINT org_billing_states_seats_nonnegative CHECK (billable_seats >= 0),
65 CONSTRAINT org_billing_states_customer_id_not_blank CHECK (
66 stripe_customer_id IS NULL OR char_length(stripe_customer_id) > 0
67 ),
68 CONSTRAINT org_billing_states_subscription_id_not_blank CHECK (
69 stripe_subscription_id IS NULL OR char_length(stripe_subscription_id) > 0
70 ),
71 CONSTRAINT org_billing_states_subscription_item_id_not_blank CHECK (
72 stripe_subscription_item_id IS NULL OR char_length(stripe_subscription_item_id) > 0
73 ),
74 CONSTRAINT org_billing_states_lock_reason_requires_locked CHECK (
75 lock_reason IS NULL OR locked_at IS NOT NULL
76 ),
77 CONSTRAINT org_billing_states_grace_requires_locked CHECK (
78 grace_until IS NULL OR locked_at IS NOT NULL
79 ),
80 CONSTRAINT org_billing_states_period_order CHECK (
81 current_period_start IS NULL
82 OR current_period_end IS NULL
83 OR current_period_start <= current_period_end
84 )
85 );
86
87 CREATE UNIQUE INDEX org_billing_states_stripe_customer_unique
88 ON org_billing_states (stripe_customer_id)
89 WHERE stripe_customer_id IS NOT NULL;
90
91 CREATE UNIQUE INDEX org_billing_states_stripe_subscription_unique
92 ON org_billing_states (stripe_subscription_id)
93 WHERE stripe_subscription_id IS NOT NULL;
94
95 CREATE UNIQUE INDEX org_billing_states_stripe_subscription_item_unique
96 ON org_billing_states (stripe_subscription_item_id)
97 WHERE stripe_subscription_item_id IS NOT NULL;
98
99 CREATE INDEX org_billing_states_status_idx
100 ON org_billing_states (subscription_status, updated_at DESC);
101
102 CREATE INDEX org_billing_states_locked_idx
103 ON org_billing_states (locked_at)
104 WHERE locked_at IS NOT NULL;
105
106 CREATE TRIGGER set_updated_at BEFORE UPDATE ON org_billing_states
107 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
108
109 -- Backfill current organizations as Free. orgs.plan remains the
110 -- human-facing summary; billing state starts conservative until Stripe
111 -- webhooks activate a paid subscription.
112 INSERT INTO org_billing_states (org_id, plan)
113 SELECT id, 'free'::org_plan
114 FROM orgs
115 ON CONFLICT (org_id) DO NOTHING;
116
117 -- +goose StatementBegin
118 CREATE OR REPLACE FUNCTION tg_org_billing_state_seed() RETURNS trigger AS $$
119 BEGIN
120 INSERT INTO org_billing_states (org_id, plan)
121 VALUES (NEW.id, 'free'::org_plan)
122 ON CONFLICT (org_id) DO NOTHING;
123 RETURN NEW;
124 END;
125 $$ LANGUAGE plpgsql;
126 -- +goose StatementEnd
127
128 CREATE TRIGGER tg_org_billing_state_seed_ai
129 AFTER INSERT ON orgs
130 FOR EACH ROW EXECUTE FUNCTION tg_org_billing_state_seed();
131
132 CREATE TABLE billing_seat_snapshots (
133 id bigserial PRIMARY KEY,
134 org_id bigint NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
135 provider billing_provider NOT NULL DEFAULT 'stripe',
136 stripe_subscription_id text,
137 active_members integer NOT NULL,
138 billable_seats integer NOT NULL,
139 source text NOT NULL DEFAULT 'local',
140 captured_at timestamptz NOT NULL DEFAULT now(),
141
142 CONSTRAINT billing_seat_snapshots_active_members_nonnegative CHECK (active_members >= 0),
143 CONSTRAINT billing_seat_snapshots_billable_seats_nonnegative CHECK (billable_seats >= 0),
144 CONSTRAINT billing_seat_snapshots_source_length CHECK (char_length(source) BETWEEN 1 AND 64)
145 );
146
147 CREATE INDEX billing_seat_snapshots_org_captured_idx
148 ON billing_seat_snapshots (org_id, captured_at DESC);
149
150 CREATE TABLE billing_invoices (
151 id bigserial PRIMARY KEY,
152 org_id bigint NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
153 provider billing_provider NOT NULL DEFAULT 'stripe',
154 stripe_invoice_id text NOT NULL,
155 stripe_customer_id text NOT NULL,
156 stripe_subscription_id text,
157 status billing_invoice_status NOT NULL,
158 number text NOT NULL DEFAULT '',
159 currency text NOT NULL,
160 amount_due_cents bigint NOT NULL DEFAULT 0,
161 amount_paid_cents bigint NOT NULL DEFAULT 0,
162 amount_remaining_cents bigint NOT NULL DEFAULT 0,
163 hosted_invoice_url text NOT NULL DEFAULT '',
164 invoice_pdf_url text NOT NULL DEFAULT '',
165 period_start timestamptz,
166 period_end timestamptz,
167 due_at timestamptz,
168 paid_at timestamptz,
169 voided_at timestamptz,
170 created_at timestamptz NOT NULL DEFAULT now(),
171 updated_at timestamptz NOT NULL DEFAULT now(),
172
173 CONSTRAINT billing_invoices_stripe_invoice_not_blank CHECK (char_length(stripe_invoice_id) > 0),
174 CONSTRAINT billing_invoices_stripe_customer_not_blank CHECK (char_length(stripe_customer_id) > 0),
175 CONSTRAINT billing_invoices_currency_iso CHECK (
176 char_length(currency) = 3 AND currency = lower(currency)
177 ),
178 CONSTRAINT billing_invoices_amounts_nonnegative CHECK (
179 amount_due_cents >= 0
180 AND amount_paid_cents >= 0
181 AND amount_remaining_cents >= 0
182 ),
183 CONSTRAINT billing_invoices_period_order CHECK (
184 period_start IS NULL OR period_end IS NULL OR period_start <= period_end
185 ),
186
187 UNIQUE (provider, stripe_invoice_id)
188 );
189
190 CREATE INDEX billing_invoices_org_created_idx
191 ON billing_invoices (org_id, created_at DESC);
192
193 CREATE INDEX billing_invoices_status_idx
194 ON billing_invoices (status, created_at DESC);
195
196 CREATE TRIGGER set_updated_at BEFORE UPDATE ON billing_invoices
197 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
198
199 CREATE TABLE billing_webhook_events (
200 id bigserial PRIMARY KEY,
201 provider billing_provider NOT NULL DEFAULT 'stripe',
202 provider_event_id text NOT NULL,
203 event_type text NOT NULL,
204 api_version text NOT NULL DEFAULT '',
205 payload jsonb NOT NULL DEFAULT '{}'::jsonb,
206 received_at timestamptz NOT NULL DEFAULT now(),
207 processed_at timestamptz,
208 process_error text NOT NULL DEFAULT '',
209 processing_attempts integer NOT NULL DEFAULT 0,
210
211 CONSTRAINT billing_webhook_events_provider_event_not_blank CHECK (char_length(provider_event_id) > 0),
212 CONSTRAINT billing_webhook_events_type_not_blank CHECK (char_length(event_type) > 0),
213 CONSTRAINT billing_webhook_events_attempts_nonnegative CHECK (processing_attempts >= 0),
214 CONSTRAINT billing_webhook_events_payload_object CHECK (jsonb_typeof(payload) = 'object'),
215
216 UNIQUE (provider, provider_event_id)
217 );
218
219 CREATE INDEX billing_webhook_events_received_idx
220 ON billing_webhook_events (received_at DESC);
221
222 CREATE INDEX billing_webhook_events_processed_idx
223 ON billing_webhook_events (processed_at)
224 WHERE processed_at IS NULL;
225
226 -- +goose Down
227 DROP INDEX IF EXISTS billing_webhook_events_processed_idx;
228 DROP INDEX IF EXISTS billing_webhook_events_received_idx;
229 DROP TABLE IF EXISTS billing_webhook_events;
230
231 DROP TRIGGER IF EXISTS set_updated_at ON billing_invoices;
232 DROP INDEX IF EXISTS billing_invoices_status_idx;
233 DROP INDEX IF EXISTS billing_invoices_org_created_idx;
234 DROP TABLE IF EXISTS billing_invoices;
235
236 DROP INDEX IF EXISTS billing_seat_snapshots_org_captured_idx;
237 DROP TABLE IF EXISTS billing_seat_snapshots;
238
239 DROP TRIGGER IF EXISTS tg_org_billing_state_seed_ai ON orgs;
240 DROP FUNCTION IF EXISTS tg_org_billing_state_seed();
241 DROP TRIGGER IF EXISTS set_updated_at ON org_billing_states;
242 DROP INDEX IF EXISTS org_billing_states_locked_idx;
243 DROP INDEX IF EXISTS org_billing_states_status_idx;
244 DROP INDEX IF EXISTS org_billing_states_stripe_subscription_item_unique;
245 DROP INDEX IF EXISTS org_billing_states_stripe_subscription_unique;
246 DROP INDEX IF EXISTS org_billing_states_stripe_customer_unique;
247 DROP TABLE IF EXISTS org_billing_states;
248
249 DROP TYPE IF EXISTS billing_invoice_status;
250 DROP TYPE IF EXISTS billing_lock_reason;
251 DROP TYPE IF EXISTS billing_subscription_status;
252 DROP TYPE IF EXISTS billing_provider;
253