-- billing: optional Stripe subscriptions plus a local prepaid credit ledger drawn down by metering.
-- One Stripe customer per account (user or org). Absent when Stripe is not configured.
CREATE TABLE billing_customers (
account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
stripe_customer_id TEXT NOT NULL UNIQUE,
subscription_id TEXT,
subscription_status TEXT,
price_id TEXT,
current_period_end INTEGER,
-- created time (ms) of the newest subscription event applied, so out-of-order deliveries never regress state.
subscription_event_at INTEGER NOT NULL DEFAULT 0,
cancel_at_period_end INTEGER NOT NULL DEFAULT 0,
updated_at INTEGER NOT NULL
);
-- Webhook idempotency: an event id is processed at most once.
CREATE TABLE stripe_events (
id TEXT PRIMARY KEY,
type TEXT NOT NULL,
processed_at INTEGER NOT NULL
);
-- Prepaid credit per account and meter, in the meter's unit. Never period-scoped, so it survives rollover.
CREATE TABLE credit_balances (
account_id TEXT NOT NULL,
meter TEXT NOT NULL,
balance REAL NOT NULL DEFAULT 0 CHECK (balance >= 0),
updated_at INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (account_id, meter)
);
-- Every credit movement except draws: purchases, refund/dispute reversals and admin adjustments.
-- `ref` makes each movement idempotent (checkout session id, refund id, dispute id).
CREATE TABLE credit_ledger (
ref TEXT PRIMARY KEY,
account_id TEXT NOT NULL,
meter TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('purchase', 'refund', 'dispute', 'adjust')),
quantity REAL NOT NULL,