g1t/services/billing/migrations/0038_staff_credits.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.
| Billing: credits with a kind and expiry, discounts instead of comped, and safer charging | 1 | -- Credits g1t staff give a workspace from sudo: promotional, goodwill or a |
| 2 | -- refund, each with a note, who gave it, and an optional expiry. | |
| 3 | -- | |
| 4 | -- A grant is a ledger entry (kind top_up, reference `crd…`, so it is never | |
| 5 | -- a payment) with `ledger.credit_kind` set; what expires or is revoked | |
| 6 | -- unused is another, negative, with the same kind and the reference | |
| 7 | -- `<grant>_expired` or `<grant>_revoked`. How much of a grant was used is | |
| 8 | -- not stored: it is worked out from the ledger in order (grants.rs), the | |
| 9 | -- soonest-expiring grant first, so it is always what the ledger says. | |
| 10 | ALTER TABLE ledger ADD COLUMN credit_kind TEXT; | |
| 11 | ||
| 12 | CREATE TABLE IF NOT EXISTS credit_grants ( | |
| 13 | -- `crd_…`: the grant's ledger reference. | |
| 14 | id TEXT PRIMARY KEY, | |
| 15 | workspace TEXT NOT NULL, | |
| 16 | -- promotional, goodwill or refund (staff); purchased (paid for, by the | |
| 17 | -- workspace: cash, not given). | |
| 18 | kind TEXT NOT NULL, | |
| 19 | -- What it pays for: all usage, or models only (agent runs' model cost). | |
| 20 | -- Scoped credit is spent before credit for everything. | |
| 21 | scope TEXT NOT NULL DEFAULT 'all', | |
| 22 | -- Where it came from: staff (sudo), purchase, or promo_code. | |
| 23 | source TEXT NOT NULL DEFAULT 'staff', | |
| 24 | amount_micros INTEGER NOT NULL, | |
| 25 | note TEXT NOT NULL, | |
| 26 | -- A refund: what it refunds, and the day whose money it gives back. | |
| 27 | refund_for TEXT, | |
| 28 | refund_day TEXT, | |
| 29 | -- RFC 3339; null never expires. | |
| 30 | expires_at TEXT, | |
| 31 | created_by TEXT NOT NULL, | |
| 32 | created_at TEXT NOT NULL, | |
| 33 | -- Expired or revoked: when, why, and what was taken off the balance. | |
| 34 | closed_at TEXT, | |
| 35 | closed_reason TEXT, | |
| 36 | closed_note TEXT, | |
| 37 | closed_by TEXT, | |
| 38 | closed_micros INTEGER NOT NULL DEFAULT 0 | |
| 39 | ); | |
| 40 | CREATE INDEX IF NOT EXISTS credit_grants_by_workspace ON credit_grants (workspace, created_at); | |
| 41 | CREATE INDEX IF NOT EXISTS credit_grants_open ON credit_grants (closed_at, expires_at); | |
| 42 | CREATE INDEX IF NOT EXISTS credit_grants_by_month ON credit_grants (created_at); | |
| 43 | ||
| 44 | -- Spent credit, by kind: given away (promotional, goodwill). A refund's | |
| 45 | -- use is not given: it gives back money already paid, and takes it off | |
| 46 | -- cash on the day it refunds instead. | |
| 47 | ALTER TABLE margin_days ADD COLUMN given_credit_promotional_micros INTEGER NOT NULL DEFAULT 0; | |
| 48 | ALTER TABLE margin_days ADD COLUMN given_credit_goodwill_micros INTEGER NOT NULL DEFAULT 0; | |
| 49 | ||
| 50 | -- Credits from g1t until now were all "a refund or goodwill" by the form's | |
| 51 | -- word, with no way to tell which: goodwill, which counts as given and so | |
| 52 | -- never as money in. None expire. | |
| 53 | UPDATE ledger SET credit_kind = 'goodwill' | |
| 54 | WHERE kind = 'top_up' AND reference LIKE 'crd%' AND amount_micros > 0 AND description LIKE 'Credit from g1t:%' | |
| 55 | AND credit_kind IS NULL; | |
| 56 | INSERT OR IGNORE INTO credit_grants (id, workspace, kind, amount_micros, note, created_by, created_at) | |
| 57 | SELECT reference, workspace, 'goodwill', amount_micros, | |
| 58 | TRIM(substr(description, length('Credit from g1t:') + 1)), | |
| 59 | COALESCE(created_by, 'g1t'), created_at | |
| 60 | FROM ledger | |
| 61 | WHERE kind = 'top_up' AND reference LIKE 'crd%' AND amount_micros > 0 AND description LIKE 'Credit from g1t:%'; |