shithub Public

Watch 1 Fork 0 Star 0
MySQL · 9128 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2
3 -- ─── org_billing_states ────────────────────────────────────────────
4
5 -- name: GetOrgBillingState :one
6 SELECT * FROM org_billing_states WHERE org_id = $1;
7
8 -- name: SetStripeCustomer :one
9 INSERT INTO org_billing_states (org_id, provider, stripe_customer_id)
10 VALUES ($1, 'stripe', $2)
11 ON CONFLICT (org_id) DO UPDATE
12 SET stripe_customer_id = EXCLUDED.stripe_customer_id,
13 provider = 'stripe',
14 updated_at = now()
15 RETURNING *;
16
17 -- name: ApplySubscriptionSnapshot :one
18 WITH state AS (
19 INSERT INTO org_billing_states (
20 org_id,
21 provider,
22 plan,
23 subscription_status,
24 stripe_subscription_id,
25 stripe_subscription_item_id,
26 current_period_start,
27 current_period_end,
28 cancel_at_period_end,
29 trial_end,
30 canceled_at,
31 last_webhook_event_id,
32 past_due_at,
33 locked_at,
34 lock_reason,
35 grace_until
36 )
37 VALUES (
38 sqlc.arg(org_id)::bigint,
39 'stripe',
40 sqlc.arg(plan)::org_plan,
41 sqlc.arg(subscription_status)::billing_subscription_status,
42 sqlc.narg(stripe_subscription_id)::text,
43 sqlc.narg(stripe_subscription_item_id)::text,
44 sqlc.narg(current_period_start)::timestamptz,
45 sqlc.narg(current_period_end)::timestamptz,
46 sqlc.arg(cancel_at_period_end)::boolean,
47 sqlc.narg(trial_end)::timestamptz,
48 sqlc.narg(canceled_at)::timestamptz,
49 sqlc.arg(last_webhook_event_id)::text,
50 CASE
51 WHEN sqlc.arg(subscription_status)::billing_subscription_status = 'past_due' THEN now()
52 ELSE NULL
53 END,
54 NULL,
55 NULL,
56 NULL
57 )
58 ON CONFLICT (org_id) DO UPDATE
59 SET plan = EXCLUDED.plan,
60 subscription_status = EXCLUDED.subscription_status,
61 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
62 stripe_subscription_item_id = EXCLUDED.stripe_subscription_item_id,
63 current_period_start = EXCLUDED.current_period_start,
64 current_period_end = EXCLUDED.current_period_end,
65 cancel_at_period_end = EXCLUDED.cancel_at_period_end,
66 trial_end = EXCLUDED.trial_end,
67 canceled_at = EXCLUDED.canceled_at,
68 last_webhook_event_id = EXCLUDED.last_webhook_event_id,
69 past_due_at = CASE
70 WHEN EXCLUDED.subscription_status = 'past_due' THEN COALESCE(org_billing_states.past_due_at, now())
71 ELSE NULL
72 END,
73 locked_at = NULL,
74 lock_reason = NULL,
75 grace_until = NULL,
76 updated_at = now()
77 RETURNING *
78 ), org_update AS (
79 UPDATE orgs
80 SET plan = sqlc.arg(plan)::org_plan,
81 updated_at = now()
82 WHERE id = sqlc.arg(org_id)::bigint
83 RETURNING id
84 )
85 SELECT * FROM state;
86
87 -- name: MarkPastDue :one
88 UPDATE org_billing_states
89 SET subscription_status = 'past_due',
90 past_due_at = COALESCE(past_due_at, now()),
91 locked_at = now(),
92 lock_reason = 'past_due',
93 grace_until = sqlc.narg(grace_until)::timestamptz,
94 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
95 updated_at = now()
96 WHERE org_id = sqlc.arg(org_id)::bigint
97 RETURNING *;
98
99 -- name: MarkCanceled :one
100 WITH state AS (
101 UPDATE org_billing_states
102 SET plan = 'free',
103 subscription_status = 'canceled',
104 canceled_at = COALESCE(canceled_at, now()),
105 locked_at = now(),
106 lock_reason = 'canceled',
107 grace_until = NULL,
108 cancel_at_period_end = false,
109 last_webhook_event_id = sqlc.arg(last_webhook_event_id)::text,
110 updated_at = now()
111 WHERE org_id = sqlc.arg(org_id)::bigint
112 RETURNING *
113 ), org_update AS (
114 UPDATE orgs
115 SET plan = 'free',
116 updated_at = now()
117 WHERE id = sqlc.arg(org_id)::bigint
118 RETURNING id
119 )
120 SELECT * FROM state;
121
122 -- name: ClearBillingLock :one
123 WITH state AS (
124 UPDATE org_billing_states
125 SET plan = CASE
126 WHEN subscription_status = 'canceled' THEN 'free'
127 ELSE plan
128 END,
129 subscription_status = CASE
130 WHEN subscription_status = 'canceled' THEN 'none'
131 ELSE subscription_status
132 END,
133 locked_at = NULL,
134 lock_reason = NULL,
135 grace_until = NULL,
136 updated_at = now()
137 WHERE org_id = $1
138 RETURNING *
139 ), org_update AS (
140 UPDATE orgs
141 SET plan = state.plan,
142 updated_at = now()
143 FROM state
144 WHERE orgs.id = state.org_id
145 RETURNING orgs.id
146 )
147 SELECT * FROM state;
148
149 -- ─── billing_seat_snapshots ────────────────────────────────────────
150
151 -- name: CreateSeatSnapshot :one
152 WITH snapshot AS (
153 INSERT INTO billing_seat_snapshots (
154 org_id,
155 provider,
156 stripe_subscription_id,
157 active_members,
158 billable_seats,
159 source
160 )
161 VALUES (
162 sqlc.arg(org_id)::bigint,
163 'stripe',
164 sqlc.narg(stripe_subscription_id)::text,
165 sqlc.arg(active_members)::integer,
166 sqlc.arg(billable_seats)::integer,
167 sqlc.arg(source)::text
168 )
169 RETURNING *
170 ), state AS (
171 INSERT INTO org_billing_states (org_id, billable_seats, seat_snapshot_at)
172 SELECT org_id, billable_seats, captured_at FROM snapshot
173 ON CONFLICT (org_id) DO UPDATE
174 SET billable_seats = EXCLUDED.billable_seats,
175 seat_snapshot_at = EXCLUDED.seat_snapshot_at,
176 updated_at = now()
177 RETURNING org_id
178 )
179 SELECT * FROM snapshot;
180
181 -- name: ListSeatSnapshotsForOrg :many
182 SELECT * FROM billing_seat_snapshots
183 WHERE org_id = $1
184 ORDER BY captured_at DESC, id DESC
185 LIMIT $2;
186
187 -- ─── billing_invoices ──────────────────────────────────────────────
188
189 -- name: UpsertInvoice :one
190 INSERT INTO billing_invoices (
191 org_id,
192 provider,
193 stripe_invoice_id,
194 stripe_customer_id,
195 stripe_subscription_id,
196 status,
197 number,
198 currency,
199 amount_due_cents,
200 amount_paid_cents,
201 amount_remaining_cents,
202 hosted_invoice_url,
203 invoice_pdf_url,
204 period_start,
205 period_end,
206 due_at,
207 paid_at,
208 voided_at
209 )
210 VALUES (
211 sqlc.arg(org_id)::bigint,
212 'stripe',
213 sqlc.arg(stripe_invoice_id)::text,
214 sqlc.arg(stripe_customer_id)::text,
215 sqlc.narg(stripe_subscription_id)::text,
216 sqlc.arg(status)::billing_invoice_status,
217 sqlc.arg(number)::text,
218 sqlc.arg(currency)::text,
219 sqlc.arg(amount_due_cents)::bigint,
220 sqlc.arg(amount_paid_cents)::bigint,
221 sqlc.arg(amount_remaining_cents)::bigint,
222 sqlc.arg(hosted_invoice_url)::text,
223 sqlc.arg(invoice_pdf_url)::text,
224 sqlc.narg(period_start)::timestamptz,
225 sqlc.narg(period_end)::timestamptz,
226 sqlc.narg(due_at)::timestamptz,
227 sqlc.narg(paid_at)::timestamptz,
228 sqlc.narg(voided_at)::timestamptz
229 )
230 ON CONFLICT (provider, stripe_invoice_id) DO UPDATE
231 SET org_id = EXCLUDED.org_id,
232 stripe_customer_id = EXCLUDED.stripe_customer_id,
233 stripe_subscription_id = EXCLUDED.stripe_subscription_id,
234 status = EXCLUDED.status,
235 number = EXCLUDED.number,
236 currency = EXCLUDED.currency,
237 amount_due_cents = EXCLUDED.amount_due_cents,
238 amount_paid_cents = EXCLUDED.amount_paid_cents,
239 amount_remaining_cents = EXCLUDED.amount_remaining_cents,
240 hosted_invoice_url = EXCLUDED.hosted_invoice_url,
241 invoice_pdf_url = EXCLUDED.invoice_pdf_url,
242 period_start = EXCLUDED.period_start,
243 period_end = EXCLUDED.period_end,
244 due_at = EXCLUDED.due_at,
245 paid_at = EXCLUDED.paid_at,
246 voided_at = EXCLUDED.voided_at,
247 updated_at = now()
248 RETURNING *;
249
250 -- name: ListInvoicesForOrg :many
251 SELECT * FROM billing_invoices
252 WHERE org_id = $1
253 ORDER BY created_at DESC, id DESC
254 LIMIT $2;
255
256 -- ─── billing_webhook_events ────────────────────────────────────────
257
258 -- name: CreateWebhookEventReceipt :one
259 INSERT INTO billing_webhook_events (
260 provider,
261 provider_event_id,
262 event_type,
263 api_version,
264 payload
265 )
266 VALUES (
267 'stripe',
268 sqlc.arg(provider_event_id)::text,
269 sqlc.arg(event_type)::text,
270 sqlc.arg(api_version)::text,
271 sqlc.arg(payload)::jsonb
272 )
273 ON CONFLICT (provider, provider_event_id) DO NOTHING
274 RETURNING *;
275
276 -- name: MarkWebhookEventProcessed :one
277 UPDATE billing_webhook_events
278 SET processed_at = now(),
279 process_error = '',
280 processing_attempts = processing_attempts + 1
281 WHERE provider = 'stripe'
282 AND provider_event_id = $1
283 RETURNING *;
284
285 -- name: MarkWebhookEventFailed :one
286 UPDATE billing_webhook_events
287 SET process_error = $2,
288 processing_attempts = processing_attempts + 1
289 WHERE provider = 'stripe'
290 AND provider_event_id = $1
291 RETURNING *;
292