ManimCat / src /infrastructure /database /migrations /013_create_cat_key_access.sql
Bin29's picture
Deploy 5548f84: pre-sandbox v2.0.0-beta.1 snapshot
10dd572
Raw History Blame Contribute Delete
6.59 kB
-- Migration 013: CatKey access — key registry and per-source token usage.
--
-- Two tables, one issuing function and one reporting view. Everything here is reached only by the
-- server (service role key); the browser never holds a Supabase credential, so no policy is written
-- for `anon` / `authenticated` at all. RLS stays enabled with zero policies as a second lock, and
-- the issuing function and the view are explicitly revoked from the API-facing roles.
-- ----------------------------------------------------------------------------
-- 1. cat_keys — the registry of issued CatKeys.
--
-- Only the SHA-256 of a CatKey is stored. `key_prefix` is the maintainer-facing handle used to
-- revoke a key without ever holding its plaintext.
-- ----------------------------------------------------------------------------
create table if not exists cat_keys (
id uuid primary key default gen_random_uuid(),
key_hash text not null unique,
key_prefix text not null,
label text,
status text not null default 'active'
check (status in ('active', 'revoked')),
created_at timestamptz not null default now(),
last_used_at timestamptz
);
create index if not exists idx_cat_keys_status on cat_keys (status);
-- Revocation is looked up by prefix in the SQL Editor, and the resolver looks up by hash.
create unique index if not exists idx_cat_keys_prefix on cat_keys (key_prefix);
alter table cat_keys enable row level security;
-- ----------------------------------------------------------------------------
-- 2. cat_key_usage — one row per Workflow Job or Studio Run, holding its current cumulative value.
--
-- The token counters are checkpointed, not appended: a Studio Run may report usage several times
-- while it is still running, so every write upserts onto `(source_kind, source_id)` and overwrites
-- the totals. `request_count` is therefore the row count, and no separate counter exists.
-- ----------------------------------------------------------------------------
create table if not exists cat_key_usage (
id uuid primary key default gen_random_uuid(),
cat_key_id uuid not null references cat_keys (id) on delete cascade,
source_kind text not null
check (source_kind in ('workflow_job', 'studio_run')),
source_id text not null,
model text not null,
prompt_tokens bigint not null default 0,
completion_tokens bigint not null default 0,
total_tokens bigint not null default 0,
measured_calls integer not null default 0,
unmeasured_calls integer not null default 0,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
-- The upsert target. Declared as a constraint (not just an index) so the recorder can name it, and
-- it must stay in step with `CONFLICT_TARGET` in supabase-usage-repository.ts. `cat_key_id` leads
-- so a Job id can never be overwritten across two different CatKeys.
do $$
begin
if not exists (
select 1 from pg_constraint
where conrelid = to_regclass('cat_key_usage')
and conname = 'cat_key_usage_source_key'
) then
alter table cat_key_usage
add constraint cat_key_usage_source_key unique (cat_key_id, source_kind, source_id);
end if;
end $$;
create index if not exists idx_cat_key_usage_key_created
on cat_key_usage (cat_key_id, created_at desc);
-- The summary groups by `cat_key_id`; this covers the join without touching the heap.
create index if not exists idx_cat_key_usage_key_id on cat_key_usage (cat_key_id);
alter table cat_key_usage enable row level security;
-- ----------------------------------------------------------------------------
-- 3. issue_cat_key() — mint one CatKey and return its plaintext exactly once.
--
-- Called from the Supabase SQL Editor as the project owner:
--
-- select * from issue_cat_key('小王测试');
--
-- The plaintext is returned by the call and never persisted; only its hash and prefix are.
-- ----------------------------------------------------------------------------
create or replace function issue_cat_key (p_label text default null)
returns table (
cat_key text,
key_prefix text,
label text,
created_at timestamptz
)
language plpgsql
security definer
set search_path = public, extensions
as $$
declare
v_secret text;
v_prefix text;
begin
-- 32 random bytes from pgcrypto, hex-encoded so the key is URL- and header-safe.
v_secret := 'mc_' || encode(gen_random_bytes(32), 'hex');
v_prefix := substr(v_secret, 1, 11); -- 'mc_' plus 8 hex characters
insert into cat_keys (key_hash, key_prefix, label)
values (
encode(digest(v_secret, 'sha256'), 'hex'),
v_prefix,
nullif(btrim(coalesce(p_label, '')), '')
);
return query
select v_secret, v_prefix, nullif(btrim(coalesce(p_label, '')), ''), now();
end;
$$;
-- A `public` function is exposed by PostgREST as POST /rest/v1/rpc/issue_cat_key, and PostgreSQL
-- grants EXECUTE to PUBLIC by default. Without this revoke, anyone holding the project's anon key
-- could mint unlimited CatKeys. Signing stays a maintainer-only, SQL-Editor-only action.
revoke all on function issue_cat_key (text) from public;
revoke all on function issue_cat_key (text) from anon;
revoke all on function issue_cat_key (text) from authenticated;
-- ----------------------------------------------------------------------------
-- 4. cat_key_usage_summary — the maintainer's roll-up. Replaces any admin backend.
--
-- select * from cat_key_usage_summary order by created_at desc;
-- ----------------------------------------------------------------------------
create or replace view cat_key_usage_summary
with (security_invoker = true)
as
select
k.id as cat_key_id,
k.key_prefix,
k.label,
k.status,
k.last_used_at,
k.created_at,
count(u.id) as request_count,
coalesce(sum(u.prompt_tokens), 0) as prompt_tokens,
coalesce(sum(u.completion_tokens), 0) as completion_tokens,
coalesce(sum(u.total_tokens), 0) as total_tokens,
coalesce(sum(u.measured_calls), 0) as measured_calls,
coalesce(sum(u.unmeasured_calls), 0) as unmeasured_calls
from cat_keys k
left join cat_key_usage u on u.cat_key_id = k.id
group by k.id;
-- Views are exposed by PostgREST as well. The service role bypasses these grants; nobody else
-- should read the usage roll-up.
revoke all on cat_key_usage_summary from public;
revoke all on cat_key_usage_summary from anon;
revoke all on cat_key_usage_summary from authenticated;