1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS control_state (
singleton BOOLEAN PRIMARY KEY DEFAULT TRUE CHECK (singleton),
generation BIGINT NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO control_state (singleton) VALUES (TRUE) ON CONFLICT DO NOTHING;
CREATE TABLE IF NOT EXISTS tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
slug TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
slug TEXT NOT NULL,
name TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (tenant_id, slug),
UNIQUE (id, tenant_id)
);
CREATE TABLE IF NOT EXISTS api_keys (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
project_id UUID NOT NULL,
name TEXT NOT NULL,
key_prefix TEXT NOT NULL,
key_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(key_hash) = 32),
scopes JSONB NOT NULL DEFAULT '["inference"]'::jsonb,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
last_used_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
revoked_at TIMESTAMPTZ,
FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS providers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL UNIQUE,
protocol TEXT NOT NULL CHECK (protocol IN ('openai', 'anthropic')),
base_url TEXT NOT NULL,
api_key_ciphertext BYTEA NOT NULL,
enabled BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS models (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
public_id TEXT NOT NULL UNIQUE,
owned_by TEXT NOT NULL DEFAULT '',
input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0),
output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0),
cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0),
cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0),
enabled BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE models ADD COLUMN IF NOT EXISTS input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0);
CREATE TABLE IF NOT EXISTS model_routes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
provider_id UUID NOT NULL REFERENCES providers(id) ON DELETE RESTRICT,
upstream_model TEXT NOT NULL,
priority INTEGER NOT NULL DEFAULT 0 CHECK (priority >= 0),
weight INTEGER NOT NULL DEFAULT 1 CHECK (weight BETWEEN 1 AND 100),
enabled BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (model_id, provider_id, upstream_model)
);
CREATE INDEX IF NOT EXISTS api_keys_active_hash_idx ON api_keys (key_hash) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS projects_tenant_idx ON projects (tenant_id);
CREATE INDEX IF NOT EXISTS model_routes_model_idx ON model_routes (model_id) WHERE enabled;
CREATE INDEX IF NOT EXISTS model_routes_provider_idx ON model_routes (provider_id) WHERE enabled;
CREATE TABLE IF NOT EXISTS tenant_wallets (
tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
currency TEXT NOT NULL CHECK (currency = lower(currency) AND length(currency) = 3),
balance_micros BIGINT NOT NULL DEFAULT 0 CHECK (balance_micros >= 0),
reserved_micros BIGINT NOT NULL DEFAULT 0 CHECK (reserved_micros >= 0 AND reserved_micros <= balance_micros),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS billing_reservations (
request_id TEXT PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
project_id UUID NOT NULL,
key_id UUID NOT NULL,
public_model TEXT NOT NULL,
currency TEXT NOT NULL,
reserved_micros BIGINT NOT NULL CHECK (reserved_micros >= 0),
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'settled', 'released')),
input_price_micros_per_million BIGINT NOT NULL,
output_price_micros_per_million BIGINT NOT NULL,
cache_read_price_micros_per_million BIGINT NOT NULL,
cache_write_price_micros_per_million BIGINT NOT NULL,
actual_cost_micros BIGINT NOT NULL DEFAULT 0 CHECK (actual_cost_micros >= 0),
charged_micros BIGINT NOT NULL DEFAULT 0 CHECK (charged_micros >= 0),
uncollected_micros BIGINT NOT NULL DEFAULT 0 CHECK (uncollected_micros >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
settled_at TIMESTAMPTZ,
FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS usage_events (
request_id TEXT PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
project_id UUID NOT NULL,
key_id UUID NOT NULL,
public_model TEXT NOT NULL,
provider_id TEXT,
upstream_model TEXT,
protocol TEXT NOT NULL,
stream BOOLEAN NOT NULL DEFAULT FALSE,
status_code INTEGER NOT NULL,
success BOOLEAN NOT NULL,
error_type TEXT NOT NULL DEFAULT '',
attempts INTEGER NOT NULL DEFAULT 0,
started_at TIMESTAMPTZ NOT NULL,
duration_ms BIGINT NOT NULL DEFAULT 0,
input_tokens BIGINT NOT NULL DEFAULT 0,
output_tokens BIGINT NOT NULL DEFAULT 0,
total_tokens BIGINT NOT NULL DEFAULT 0,
cache_creation_input_tokens BIGINT NOT NULL DEFAULT 0,
cache_read_input_tokens BIGINT NOT NULL DEFAULT 0,
cost_micros BIGINT NOT NULL DEFAULT 0,
charged_micros BIGINT NOT NULL DEFAULT 0,
uncollected_micros BIGINT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT
);
-- Usage persistence is independent from billing. Older installations created this
-- foreign key, which prevented recording requests when prepaid billing was disabled.
ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_request_id_fkey;
CREATE TABLE IF NOT EXISTS billing_ledger (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
project_id UUID,
currency TEXT NOT NULL,
amount_micros BIGINT NOT NULL,
balance_after_micros BIGINT NOT NULL CHECK (balance_after_micros >= 0),
kind TEXT NOT NULL CHECK (kind IN ('topup', 'usage', 'adjustment', 'refund', 'release')),
source_type TEXT NOT NULL,
source_id TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (source_type, source_id)
);
CREATE TABLE IF NOT EXISTS topup_orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
amount_micros BIGINT NOT NULL CHECK (amount_micros > 0),
currency TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'failed', 'expired')),
stripe_session_id TEXT UNIQUE,
checkout_url TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
paid_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS stripe_webhook_events (
event_id TEXT PRIMARY KEY,
event_type TEXT NOT NULL,
processed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS console_users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
email TEXT NOT NULL,
display_name TEXT NOT NULL,
role TEXT NOT NULL CHECK (role IN (
'platform_admin', 'platform_viewer', 'tenant_admin',
'tenant_billing', 'tenant_developer', 'tenant_viewer'
)),
token_prefix TEXT NOT NULL,
token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
last_used_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
revoked_at TIMESTAMPTZ,
CHECK ((role LIKE 'platform_%' AND tenant_id IS NULL) OR (role LIKE 'tenant_%' AND tenant_id IS NOT NULL))
);
CREATE UNIQUE INDEX IF NOT EXISTS console_users_email_tenant_idx
ON console_users (lower(email), COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid));
CREATE TABLE IF NOT EXISTS project_limits (
project_id UUID PRIMARY KEY,
tenant_id UUID NOT NULL,
requests_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (requests_per_minute >= 0),
tokens_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (tokens_per_minute >= 0),
concurrent_requests BIGINT NOT NULL DEFAULT 0 CHECK (concurrent_requests >= 0),
monthly_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (monthly_spend_micros >= 0),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS usage_monthly_rollups (
period_start DATE NOT NULL,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
project_id UUID NOT NULL,
request_count BIGINT NOT NULL DEFAULT 0,
successful_requests BIGINT NOT NULL DEFAULT 0,
input_tokens BIGINT NOT NULL DEFAULT 0,
output_tokens BIGINT NOT NULL DEFAULT 0,
total_tokens BIGINT NOT NULL DEFAULT 0,
cost_micros BIGINT NOT NULL DEFAULT 0,
charged_micros BIGINT NOT NULL DEFAULT 0,
uncollected_micros BIGINT NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (project_id, period_start),
FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS audit_logs (
id BIGSERIAL PRIMARY KEY,
actor_id UUID REFERENCES console_users(id) ON DELETE SET NULL,
actor_type TEXT NOT NULL CHECK (actor_type IN ('bootstrap', 'console_user')),
actor_role TEXT NOT NULL,
tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
request_id TEXT NOT NULL,
method TEXT NOT NULL,
path TEXT NOT NULL,
action TEXT NOT NULL,
status_code INTEGER NOT NULL,
remote_ip INET,
user_agent TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS billing_ledger_tenant_idx ON billing_ledger (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_tenant_idx ON usage_events (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_project_idx ON usage_events (project_id, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_model_idx ON usage_events (public_model, created_at DESC);
CREATE INDEX IF NOT EXISTS billing_reservations_pending_idx ON billing_reservations (status, created_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS billing_reservations_project_pending_idx ON billing_reservations (project_id, created_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS console_users_tenant_idx ON console_users (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS audit_logs_created_idx ON audit_logs (created_at DESC);
CREATE INDEX IF NOT EXISTS audit_logs_tenant_idx ON audit_logs (tenant_id, created_at DESC);
|
