g1t/services/billing/migrations/0012_stripe_webhooks.sql
| 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 | ); |