g1t/services/billing/migrations/0038_staff_credits.sql
| 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:%'; |