pr_01m47d15m3e54sn21z27rpy5n9/services/billing/migrations/0013_invoices_trust_sales.sql
| 1 | -- Real invoices, trust that is hard to game, and sales records. |
| 2 | |
| 3 | -- Payments: which kind of card paid (prepaid cards never raise the |
| 4 | -- limit), and whether the payment was disputed (never counts again). |
| 5 | ALTER TABLE ledger ADD COLUMN funding TEXT; |
| 6 | ALTER TABLE ledger ADD COLUMN disputed INTEGER NOT NULL DEFAULT 0; |
| 7 | |
| 8 | -- The owners chose to use everything available, with no limit of their |
| 9 | -- own. Without it and without spend_limit_micros, the default applies. |
| 10 | ALTER TABLE limits ADD COLUMN spend_limit_full INTEGER NOT NULL DEFAULT 0; |
| 11 | |
| 12 | -- A workspace's invoices from g1t: one when each month closes, and one |
| 13 | -- each time it is charged near its limit. Itemised at Stripe, charged to |
| 14 | -- the card on file, and kept in Stripe's billing page with a PDF. |
| 15 | CREATE TABLE workspace_invoices ( |
| 16 | invoice_id TEXT PRIMARY KEY, |
| 17 | workspace TEXT NOT NULL, |
| 18 | -- month or threshold. |
| 19 | reason TEXT NOT NULL, |
| 20 | -- YYYY-MM for a month, the date for a threshold. |
| 21 | period TEXT NOT NULL, |
| 22 | amount_micros INTEGER NOT NULL, |
| 23 | -- paid, open, failed or void. |
| 24 | status TEXT NOT NULL, |
| 25 | hosted_url TEXT, |
| 26 | pdf_url TEXT, |
| 27 | -- Usage up to here is on this invoice. |
| 28 | through_at TEXT NOT NULL, |
| 29 | created_at TEXT NOT NULL, |
| 30 | paid_at TEXT |
| 31 | ); |
| 32 | CREATE INDEX workspace_invoices_by_workspace ON workspace_invoices (workspace, created_at); |
| 33 | |
| 34 | CREATE TABLE workspace_invoice_lines ( |
| 35 | invoice_id TEXT NOT NULL, |
| 36 | position INTEGER NOT NULL, |
| 37 | description TEXT NOT NULL, |
| 38 | amount_micros INTEGER NOT NULL, |
| 39 | PRIMARY KEY (invoice_id, position) |
| 40 | ); |
| 41 | |
| 42 | -- What staff are doing about a workspace. |
| 43 | CREATE TABLE sales_records ( |
| 44 | workspace TEXT PRIMARY KEY, |
| 45 | stage TEXT NOT NULL DEFAULT 'none', |
| 46 | owner TEXT, |
| 47 | next_step TEXT, |
| 48 | next_at TEXT, |
| 49 | updated_by TEXT NOT NULL, |
| 50 | updated_at TEXT NOT NULL |
| 51 | ); |
| 52 | |
| 53 | CREATE TABLE sales_notes ( |
| 54 | id TEXT PRIMARY KEY, |
| 55 | workspace TEXT NOT NULL, |
| 56 | text TEXT NOT NULL, |
| 57 | by TEXT NOT NULL, |
| 58 | created_at TEXT NOT NULL |
| 59 | ); |
| 60 | CREATE INDEX sales_notes_by_workspace ON sales_notes (workspace, created_at); |