g1t/services/billing/migrations/0012_stripe_webhooks.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Stripe webhooks, enterprise invoices, and sudo for both | 1 | -- Stripe telling billing what happened, as it happens. See src/webhooks.rs. |
| 2 | ||
| 3 | -- The endpoint billing registered at Stripe, one per mode (test or live), | |
| 4 | -- and the secret Stripe signs its events with. Made from sudo; the secret | |
| 5 | -- is never shown anywhere. | |
| 6 | CREATE TABLE stripe_webhooks ( | |
| 7 | mode TEXT PRIMARY KEY, | |
| 8 | endpoint_id TEXT NOT NULL, | |
| 9 | secret TEXT NOT NULL, | |
| 10 | url TEXT NOT NULL, | |
| 11 | -- Comma-separated event types. | |
| 12 | events TEXT NOT NULL, | |
| 13 | created_by TEXT NOT NULL, | |
| 14 | created_at TEXT NOT NULL | |
| 15 | ); | |
| 16 | ||
| 17 | -- Every event handled, once: Stripe may send one more than once. | |
| 18 | CREATE TABLE stripe_events ( | |
| 19 | id TEXT PRIMARY KEY, | |
| 20 | type TEXT NOT NULL, | |
| 21 | -- handled, ignored, or what went wrong. | |
| 22 | outcome TEXT NOT NULL, | |
| 23 | received_at TEXT NOT NULL | |
| 24 | ); | |
| 25 | CREATE INDEX stripe_events_by_time ON stripe_events (received_at); | |
| 26 | ||
| 27 | -- Where an enterprise's invoices go. | |
| 28 | ALTER TABLE billing_accounts ADD COLUMN billing_email TEXT; | |
| 29 | ||
| 30 | -- One invoice per enterprise per month (or sooner, from sudo), itemised | |
| 31 | -- by workspace. Paid on Stripe's hosted invoice page. | |
| 32 | CREATE TABLE enterprise_invoices ( | |
| 33 | invoice_id TEXT PRIMARY KEY, | |
| 34 | account_id TEXT NOT NULL, | |
| 35 | -- YYYY-MM it closes, or 'now' for one sent from sudo. | |
| 36 | period TEXT NOT NULL, | |
| 37 | amount_micros INTEGER NOT NULL, | |
| 38 | -- open, paid, overdue or void. | |
| 39 | status TEXT NOT NULL, | |
| 40 | hosted_url TEXT, | |
| 41 | created_by TEXT NOT NULL, | |
| 42 | created_at TEXT NOT NULL, | |
| 43 | paid_at TEXT | |
| 44 | ); | |
| 45 | CREATE INDEX enterprise_invoices_by_account ON enterprise_invoices (account_id, created_at); | |
| 46 | ||
| 47 | CREATE TABLE enterprise_invoice_lines ( | |
| 48 | invoice_id TEXT NOT NULL, | |
| 49 | workspace TEXT NOT NULL, | |
| 50 | amount_micros INTEGER NOT NULL, | |
| 51 | PRIMARY KEY (invoice_id, workspace) | |
| 52 | ); |