| 1 | -- Complimentary and custom terms that g1t staff enroll a workspace in from |
| 2 | -- sudo (src/complimentary.rs). New workspaces stay free; nothing here |
| 3 | -- enrolls anyone. |
| 4 | -- |
| 5 | -- An enrollment gives the plan's features, a monthly usage allowance valued |
| 6 | -- at ordinary usage prices, and a recurring monthly fee of its own (zero: |
| 7 | -- waived). The fee and the allowance are separate amounts. There is no |
| 8 | -- rollover. Past the allowance (`overage`): `stop`, new billable work stops |
| 9 | -- and what work already running costs past it is g1t's |
| 10 | -- (`ledger.given_micros`), never charged to the workspace afterwards; or |
| 11 | -- `charge`, g1t covers the allowance and usage past it is charged to the |
| 12 | -- workspace's own card at ordinary prices, under its limits, as on the plan |
| 13 | -- (employees, friends). Without a card, `charge` stops as `stop` does. |
| 14 | -- |
| 15 | -- A real Stripe customer and subscription represent the enrollment. The |
| 16 | -- workspace sees it only once Stripe has the subscription (`status` |
| 17 | -- `active`); until then it is `pending` and the workspace keeps its plan. |
| 18 | CREATE TABLE IF NOT EXISTS complimentary ( |
| 19 | workspace TEXT PRIMARY KEY, |
| 20 | -- `cmp_…`: the grant's identity, in the Stripe subscription's metadata and |
| 21 | -- every idempotency key for it, so a retry never makes a second one. |
| 22 | grant_id TEXT NOT NULL UNIQUE, |
| 23 | -- pending: Stripe not confirmed yet; active; ended. |
| 24 | status TEXT NOT NULL CHECK (status IN ('pending', 'active', 'ended')), |
| 25 | -- The current terms; their history is `complimentary_periods`. |
| 26 | allowance_micros INTEGER NOT NULL CHECK (allowance_micros >= 0), |
| 27 | unlimited INTEGER NOT NULL DEFAULT 0 CHECK (unlimited IN (0, 1)), |
| 28 | fee_cents INTEGER NOT NULL DEFAULT 0 CHECK (fee_cents >= 0), |
| 29 | overage TEXT NOT NULL DEFAULT 'stop' CHECK (overage IN ('stop', 'charge')), |
| 30 | -- Whether the Stripe objects below are live-mode objects: test objects |
| 31 | -- are never carried into live mode. |
| 32 | live INTEGER NOT NULL DEFAULT 0 CHECK (live IN (0, 1)), |
| 33 | customer_id TEXT, |
| 34 | subscription_id TEXT UNIQUE, |
| 35 | -- The subscription's one item and the price it has while complimentary: |
| 36 | -- a different price on that item means the owner converted to the plan. |
| 37 | item_id TEXT, |
| 38 | price_id TEXT, |
| 39 | -- What Stripe last said about the subscription. |
| 40 | stripe_status TEXT, |
| 41 | -- A Stripe operation still owed, as JSON: {"op":"create"|"terms"|"cancel"|"convert", ...}. |
| 42 | -- Kept until done, so a crash or an error is picked up by the next tick |
| 43 | -- with the same idempotency key. |
| 44 | pending_op TEXT, |
| 45 | last_error TEXT, |
| 46 | note TEXT NOT NULL, |
| 47 | created_by TEXT NOT NULL, |
| 48 | created_at TEXT NOT NULL, |
| 49 | created_ms INTEGER NOT NULL, |
| 50 | updated_by TEXT NOT NULL, |
| 51 | updated_at TEXT NOT NULL, |
| 52 | -- How it ended: revoked (staff), converted (the owner started the plan). |
| 53 | ended_at TEXT, |
| 54 | ended_reason TEXT CHECK (ended_reason IS NULL OR ended_reason IN ('revoked', 'converted')), |
| 55 | -- The plan subscription a conversion produced (the same subscription id). |
| 56 | converted_at TEXT |
| 57 | ); |
| 58 | CREATE INDEX IF NOT EXISTS complimentary_pending ON complimentary (pending_op) WHERE pending_op IS NOT NULL; |
| 59 | |
| 60 | -- The terms over time. A usage line draws on the period that covers its own |
| 61 | -- time, so late usage lands in the period it happened in, and changing the |
| 62 | -- amount closes one period and opens the next without touching what was |
| 63 | -- used: consumption is per calendar month (`allowance_use`, kind |
| 64 | -- `complimentary`), whatever the periods in it. |
| 65 | CREATE TABLE IF NOT EXISTS complimentary_periods ( |
| 66 | id TEXT PRIMARY KEY, |
| 67 | workspace TEXT NOT NULL, |
| 68 | grant_id TEXT NOT NULL, |
| 69 | -- RFC 3339; null `ends_at`: open. |
| 70 | starts_at TEXT NOT NULL, |
| 71 | ends_at TEXT, |
| 72 | allowance_micros INTEGER NOT NULL CHECK (allowance_micros >= 0), |
| 73 | unlimited INTEGER NOT NULL DEFAULT 0 CHECK (unlimited IN (0, 1)), |
| 74 | fee_cents INTEGER NOT NULL DEFAULT 0 CHECK (fee_cents >= 0), |
| 75 | overage TEXT NOT NULL DEFAULT 'stop' CHECK (overage IN ('stop', 'charge')), |
| 76 | note TEXT NOT NULL, |
| 77 | created_by TEXT NOT NULL, |
| 78 | created_at TEXT NOT NULL |
| 79 | ); |
| 80 | CREATE INDEX IF NOT EXISTS complimentary_periods_by_workspace ON complimentary_periods (workspace, starts_at); |
| 81 | -- At most one open period per workspace. |
| 82 | CREATE UNIQUE INDEX IF NOT EXISTS complimentary_periods_open ON complimentary_periods (workspace) WHERE ends_at IS NULL; |
| 83 | |
| 84 | -- What a complimentary allowance paid of a usage line: promotional, never |
| 85 | -- money in. Separate from `given_micros`, which is what g1t covered past |
| 86 | -- every allowance (a complimentary workspace's overrun included). |
| 87 | ALTER TABLE ledger ADD COLUMN gift_micros INTEGER NOT NULL DEFAULT 0; |
| 88 | |
| 89 | -- Payments a workspace made outside Stripe (a bank transfer, a cheque) |
| 90 | -- recorded by staff are money received; they were already ledger top-ups |
| 91 | -- with `credit_kind` null. Nothing to migrate: promotional credit keeps |
| 92 | -- `credit_kind = 'promotional'`, and a complimentary allowance is never a |
| 93 | -- ledger top-up at all. |