schema.sql

-- ToolMint schema. Two tables: api_keys (who can call + their quota) and
-- usage_events (every metered call). Apply locally with:
--   npx wrangler d1 execute toolmint --local --file=./schema.sql
-- and to production with --remote.
--
-- Idempotent (IF NOT EXISTS) so re-running is safe. A copy lives in
-- migrations/0001_init.sql for the `wrangler d1 migrations apply` workflow.

-- Issued API keys. The key string itself is never stored — only a SHA-256 hash,
-- so a DB leak can't be replayed. `key_prefix` (e.g. "tm_live_a1b2") is the
-- human-readable handle shown in the dashboard and returned at creation time.
CREATE TABLE IF NOT EXISTS api_keys (
  id              TEXT PRIMARY KEY,            -- random id, also the metering foreign key
  key_hash        TEXT NOT NULL UNIQUE,        -- SHA-256 hex of the full secret key
  key_prefix      TEXT NOT NULL,               -- non-secret display handle ("tm_live_xxxx")
  name            TEXT,                        -- optional label ("acme-prod")
  monthly_quota   INTEGER NOT NULL DEFAULT 1000, -- calls/month before 402; bumped on paid upgrade
  created_ts      INTEGER NOT NULL,
  revoked         INTEGER NOT NULL DEFAULT 0,  -- 1 = key disabled (401 on use)
  last_used_ts    INTEGER
);
CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(key_hash);

-- One row per authed proxied call. The metering ledger the dashboard and the
-- monthly-quota check read from. `month` is "YYYY-MM" (UTC) for cheap windowing.
CREATE TABLE IF NOT EXISTS usage_events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  key_id      TEXT NOT NULL,                   -- -> api_keys.id
  ts          INTEGER NOT NULL,               -- unix seconds
  month       TEXT NOT NULL,                  -- "YYYY-MM" (UTC), for monthly quota windows
  path        TEXT NOT NULL,                  -- request path proxied
  method      TEXT NOT NULL,
  status      INTEGER NOT NULL,               -- upstream HTTP status returned
  units       INTEGER NOT NULL DEFAULT 1,     -- billable units (tokens if upstream reports them, else 1)
  duration_ms INTEGER                         -- upstream round-trip latency
);
CREATE INDEX IF NOT EXISTS idx_usage_key_month ON usage_events(key_id, month);
CREATE INDEX IF NOT EXISTS idx_usage_ts        ON usage_events(ts DESC);

-- Optional audit trail of quota upgrades (manual admin top-ups or settled x402/Stripe
-- payments). The billing seam writes here; not required for metering to work.
CREATE TABLE IF NOT EXISTS billing_events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  key_id      TEXT NOT NULL,                   -- -> api_keys.id
  ts          INTEGER NOT NULL,
  source      TEXT NOT NULL,                  -- "admin" | "x402" | "stripe"
  amount_usd  REAL,                           -- charged amount, if any
  quota_added INTEGER NOT NULL,               -- calls added to monthly_quota
  reference   TEXT                            -- payer addr / payment id / note
);
CREATE INDEX IF NOT EXISTS idx_billing_key ON billing_events(key_id);