Ilker ManapandClaude Opus 5
29617f8cb1
Phase 5 begins. This is the persistence layer for the things the cluster cannot reconstruct — who may call, what they are allowed, what they used. Everything else llmgw knows is still derived from the fleet, which cannot be wrong about itself the way a database can (§6.5). SQLite behind an interface. The plan justified that by write volume, which was the wrong argument: the write path is batched anyway, and a 500-row transaction every 200ms is nothing for either engine. The real argument is the failure mode. Postgres is a second service, and a gateway that stops serving because its accounting database is unreachable is worse than one that buffers some accounting. Embedded means the store shares the gateway's fate instead of adding an independent way to be down. That reasoning stops holding at two instances, where budgets have to be counted somewhere shared — so the interface is here from the start, the SQL is portable, and migrations are per dialect with shared version numbers. Keys are stored as sha256 and nothing else, so a database leak hands over no working credential. The fast hash is deliberate: the input is 256 random bits with no dictionary to attack, so argon2 would buy nothing and charge tens of milliseconds per request. That assumption is written down next to the code that depends on it. Two things the schema now says that it did not: budgets are integer micro-units, because repeated addition of floats drifts; and an empty model allowlist forbids everything while an absent one permits everything, which the resolver has to keep distinct. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
73 lines
2.5 KiB
SQL
73 lines
2.5 KiB
SQL
-- The tenancy tree. Three levels, each owning the next.
|
|
CREATE TABLE organizations (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE teams (
|
|
id TEXT PRIMARY KEY,
|
|
org_id TEXT NOT NULL REFERENCES organizations(id),
|
|
name TEXT NOT NULL
|
|
);
|
|
CREATE INDEX teams_by_org ON teams(org_id);
|
|
|
|
CREATE TABLE projects (
|
|
id TEXT PRIMARY KEY,
|
|
team_id TEXT NOT NULL REFERENCES teams(id),
|
|
name TEXT NOT NULL
|
|
);
|
|
CREATE INDEX projects_by_team ON projects(team_id);
|
|
|
|
-- Only sha256(key) is stored, so a database leak hands over no working
|
|
-- credential. SHA-256 rather than a slow password hash on purpose: the input is
|
|
-- 256 random bits with no dictionary to attack, so the cost of argon2 would buy
|
|
-- nothing and charge tens of milliseconds on every request (plan §7.9.1).
|
|
CREATE TABLE api_keys (
|
|
id TEXT PRIMARY KEY,
|
|
key_hash BLOB NOT NULL UNIQUE,
|
|
key_prefix TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
project_id TEXT NOT NULL REFERENCES projects(id),
|
|
band TEXT,
|
|
routing_profile TEXT,
|
|
created_at INTEGER NOT NULL,
|
|
expires_at INTEGER,
|
|
-- Revocation is a soft delete. Usage rows reference the key, and an audit
|
|
-- trail that loses its subject is not an audit trail.
|
|
revoked_at INTEGER,
|
|
last_used_at INTEGER
|
|
);
|
|
CREATE INDEX api_keys_by_project ON api_keys(project_id);
|
|
|
|
-- Limits hang off any scope rather than being duplicated onto each one. A NULL
|
|
-- column means "no limit at this level", which is not the same as 0.
|
|
CREATE TABLE limits (
|
|
scope_type TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
rpm INTEGER,
|
|
tpm INTEGER,
|
|
-- Micro-units, not a float: a budget is accumulated by repeated addition, and
|
|
-- repeated addition of floats drifts.
|
|
budget_micros INTEGER,
|
|
budget_period TEXT,
|
|
allowed_models TEXT,
|
|
PRIMARY KEY (scope_type, scope_id)
|
|
);
|
|
|
|
-- One row per request, written in batches off the request path.
|
|
CREATE TABLE usage (
|
|
ts INTEGER NOT NULL,
|
|
key_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
model TEXT NOT NULL,
|
|
served_model TEXT NOT NULL,
|
|
prompt_tokens INTEGER NOT NULL,
|
|
completion_tokens INTEGER NOT NULL,
|
|
cost_micros INTEGER NOT NULL,
|
|
status INTEGER NOT NULL,
|
|
latency_ms INTEGER NOT NULL
|
|
);
|
|
-- Budget restore reads by key within a period, which is this index exactly.
|
|
CREATE INDEX usage_by_key_ts ON usage(key_id, ts);
|
|
CREATE INDEX usage_by_ts ON usage(ts);
|