Download src/infrastructure/database/migrations/013_create_cat_key_access.sql from Bin29/ManimCat: direct link, hf CLI and curl.
- Browser
- Download file 6.59 kB
-
https://huggingface.co/spaces/Bin29/ManimCat/resolve/main/src/infrastructure/database/migrations/013_create_cat_key_access.sql
- Command line
-
hf download hf://spaces/Bin29/ManimCat/src/infrastructure/database/migrations/013_create_cat_key_access.sql
-
curl -L -o 013_create_cat_key_access.sql https://huggingface.co/spaces/Bin29/ManimCat/resolve/main/src/infrastructure/database/migrations/013_create_cat_key_access.sql
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; | |