g1t/services/billing/migrations/0001_init.sql
| 1 | -- What agents cost, charged to the workspace they worked for. |
| 2 | -- Money is in millionths of a US dollar. Every timestamp is RFC 3339 UTC. |
| 3 | |
| 4 | CREATE TABLE accounts ( |
| 5 | -- The workspace's slug. |
| 6 | workspace TEXT PRIMARY KEY, |
| 7 | -- Credit left. Always the sum of the workspace's ledger. |
| 8 | balance_micros INTEGER NOT NULL DEFAULT 0, |
| 9 | -- The payment provider's customer, once the workspace has paid once. |
| 10 | customer_id TEXT, |
| 11 | created_at TEXT NOT NULL |
| 12 | ); |
| 13 | |
| 14 | -- Every change to a balance: credit bought, and each agent run. |
| 15 | CREATE TABLE ledger ( |
| 16 | id TEXT PRIMARY KEY, |
| 17 | workspace TEXT NOT NULL, |
| 18 | -- top_up or usage. |
| 19 | kind TEXT NOT NULL, |
| 20 | -- Positive for credit added, negative for usage. |
| 21 | amount_micros INTEGER NOT NULL, |
| 22 | description TEXT NOT NULL, |
| 23 | -- For usage: what the agent worked on, and what the provider charged |
| 24 | -- before g1t's margin. |
| 25 | repo TEXT, |
| 26 | number INTEGER, |
| 27 | task TEXT, |
| 28 | model TEXT, |
| 29 | cost_micros INTEGER, |
| 30 | -- The payment or the run this entry is for. Unique, so neither can be |
| 31 | -- entered twice. |
| 32 | reference TEXT NOT NULL UNIQUE, |
| 33 | -- For a top-up: the username of whoever paid. |
| 34 | created_by TEXT, |
| 35 | created_at TEXT NOT NULL |
| 36 | ); |
| 37 | CREATE INDEX ledger_by_workspace ON ledger (workspace, id); |
| 38 | |
| 39 | -- Agent runs that have started. A run is charged when its sandbox reports |
| 40 | -- what it cost, once. |
| 41 | CREATE TABLE runs ( |
| 42 | id TEXT PRIMARY KEY, |
| 43 | workspace TEXT NOT NULL, |
| 44 | repo TEXT NOT NULL, |
| 45 | number INTEGER NOT NULL, |
| 46 | task TEXT NOT NULL, |
| 47 | model TEXT NOT NULL, |
| 48 | -- SHA-256 of the token the sandbox reports with. |
| 49 | token_hash TEXT NOT NULL, |
| 50 | created_at TEXT NOT NULL, |
| 51 | finished_at TEXT |
| 52 | ); |
| 53 | |
| 54 | -- Card payments that have been started. Credited when the provider says |
| 55 | -- the payment was made, once. |
| 56 | CREATE TABLE checkouts ( |
| 57 | -- The provider's id for the payment page. |
| 58 | id TEXT PRIMARY KEY, |
| 59 | workspace TEXT NOT NULL, |
| 60 | amount_cents INTEGER NOT NULL, |
| 61 | created_by TEXT NOT NULL, |
| 62 | -- open or paid. |
| 63 | status TEXT NOT NULL DEFAULT 'open', |
| 64 | created_at TEXT NOT NULL |
| 65 | ); |
| 66 | CREATE INDEX checkouts_by_workspace ON checkouts (workspace, status); |