Levandor

txArchive Supabase Contract

9 min read

For Agents

This is §6 of txarchive_2025/docs/txarchive-2025-unified-design.md — the contract the new frontend consumes. It replaces the frontend spec’s §8 (“Backend API contract — this stays”), which described the abandoned Rust REST API and is OBSOLETE. The new contract is Supabase: PostgREST tables/views + RPCs + Realtime channels + 3 Edge Functions + cdn.txarchive.org/{file_key}. This note is a condensed crosswalk; the design doc has the full table. See also txArchive 2025 Rewrite and txArchive Frontend Architecture.

Client setup

One module-level singleton: createClient<Database>(VITE_SUPABASE_URL, VITE_SUPABASE_ANON_KEY) (Database types from supabase gen types typescript). The anon key is public-by-design; RLS protects everything. Auth state via supabase.auth.getSession() / supabase.auth.onAuthStateChange(...); every authenticated call just uses the client (the JWT rides automatically). Permission scopes for UI gating only come from session.user.app_metadata.scopes (the custom-access-token hook) — RLS is the real gate. Blob URLs: VITE_CDN_BASE_URL + file_key. Discord avatars: from profiles.avatar — full URL → use it; else https://cdn.discordapp.com/avatars/${discord_id}/${avatar}.png; null → a fallback (initials / default). Never in the frontend: the service-role key, R2 creds, the bot token, GOOGLE_VISION_API_KEY, EDGE_SHARED_SECRET.

Endpoint crosswalk — old Rust REST → new Supabase

NeedOld (Rust REST)New (Supabase)RLS / notes
Start loginGET /auth → OAuth URL → redirect → GET /consume → /authorize?data=supabase.auth.signInWithOAuth({ provider:'discord', options:{ redirectTo:'<origin>/auth/callback', scopes:'identify guilds guilds.join' } })Supabase round-trips; lands on /auth/callback with a session.
Finish login (guild gate, profile upsert, guilds.join, consume enrollments)server did it inside /consumePOST <ref>.functions.supabase.co/complete-login — bearer = the new session JWT; body { provider_token: session.provider_token } → { profile, scopes }. On 403 {error:'not_in_guild'} → supabase.auth.signOut() + show the “you must be in the txAdmin Discord” screen.provider_token is only on the initial signInWithOAuth session (not later refreshes) → call complete-login immediately in /auth/callback.
Refresh tokenGET /auth/refreshgone — supabase-js auto-refreshes—
Logoutclear cookie + navigatesupabase.auth.signOut()—
Current userGET /user/mesupabase.from('profiles').select('*').eq('id', uid).single() (or session.user + the profiles row)RLS: any member can read any profile.
Timeline (approved feed; paginated; exclude-own)GET /timeline?skip=&exclude_own=supabase.from('timeline').select('*').order('uploaded_at',{ascending:false}).range(a,b) (+ .neq('uploader', uid) to exclude own)timeline is security_invoker → RLS on uploads applies. Page 25 via useInfiniteQuery. View has comment_count, like_count, liked_by_me, ocr_done, file_url, uploader fields.
One post (detail)GET /uploads/:idsupabase.from('post_detail').select('*').eq('id', id).maybeSingle()post_detail (new) = timeline-shaped, where true, security_invoker → owner / APPROVE / DELETE can see PENDING/DENIED; others only APPROVED.
My uploadsGET /uploads?skip=supabase.from('uploads').select('*').eq('uploader', uid).order('uploaded_at',{ascending:false}).range(a,b)RLS lets you see your own regardless of approved. Page 10. Compose file_url from file_key.
Delete my upload / admin-delete anyDELETE /uploads/:id / DELETE /admin/uploads/:idPOST <ref>.functions.supabase.co/delete-upload?id=<uuid> — bearer = user JWT; the function decides owner-delete vs admin-delete from the caller’s scopes; deletes the R2 object; cascades comments/likes/ocr/ocr_jobs; on admin-delete also calls notify-discord {post_deleted_admin} → 204RLS DELETE on uploads = service-role only; the function authorizes the caller then uses the service client.
Upload a filePOST /upload (multipart)POST <ref>.functions.supabase.co/create-upload — multipart/form-data, one file part; bearer = user JWT → the new row { id, file_key, approved:'PENDING', original_file_name, mime_type, size, uploaded_at }. Errors: NO_PERMS, MIME_NOT_ALLOWED, MIME_EXT_MISMATCH, TOO_LARGE, LIMIT_REACHED.Validates ADD scope + MIME ∈ ALLOWED_MIME_TYPES + size ≤ UPLOAD_SIZE_LIMIT + ext-matches-MIME + (unless discord_id ∈ UNLIMITED_UPLOADERS) <DAILY_UPLOAD_LIMIT uploads today; writes R2; inserts the row (service client).
Approve / denyPOST /uploads/:id/state?status=`supabase.from(‘uploads’).update({ approved:‘APPROVED''DENIED’ }).eq(‘id’, id)`
Approvals queue + countGET /approvals?skip= / GET /approvals/countsupabase.from('pending_uploads').select('*').order('uploaded_at',{ascending:true}).range(a,b) / supabase.from('pending_uploads').select('*',{count:'exact',head:true})pending_uploads = security_invoker, where approved='PENDING' → only APPROVE holders see anything. Page 20. The count drives the nav badge.
Comments on a postGET /uploads/:id/comments?skip=supabase.from('comments').select('*, author:profiles!comments_author_fkey(username,avatar,discord_id)').eq('upload_id', id).order('created_at',{ascending:false}).range(a,b)RLS: can_see_upload(upload_id). Page 50 via useInfiniteQuery.
Post a commentPOST /uploads/:id/comment {content}supabase.from('comments').insert({ upload_id:id, content }).select('*, author:profiles!comments_author_fkey(...)').single()RLS INSERT: has_scope('VIEW') AND author=auth.uid() AND can_see_upload; a before trigger forces author=auth.uid(), created_at=now(); char 1–2000 by a check. Use optimistically; the real row also arrives via Realtime (dedupe by id).
Delete a comment(didn’t exist)supabase.from('comments').delete().eq('id', id)RLS DELETE: author=auth.uid() OR has_scope('DELETE').
Like / unlike (toggle)POST /uploads/:id/likesupabase.rpc('toggle_like', { p_post_id:id }) → 'LIKED' | 'NOT_LIKED'RLS on likes enforces VIEW + visibility; composite PK fixes the dup-likes bug. Use optimistically.
Is-likedGET /uploads/:id/likedliked_by_me is already on timeline/post_detail; standalone: supabase.rpc('is_liked', { p_post_id:id }) or from('likes').select('post_id').eq('post_id',id).eq('user_id',uid).maybeSingle()—
A user’s uploads (moderation, /members)GET /user/:id/uploads?skip=supabase.from('uploads').select('*').eq('uploader', targetId).order('uploaded_at',{ascending:false}).range(a,b)RLS: to see someone else’s PENDING/DENIED you need has_scope('DELETE').
Invite a friendPOST /user/enroll/:usersupabase.rpc('enroll_user', { p_discord_id, p_scopes }) → 'ENROLLED' (had an account; scopes applied now) | 'ENROLLED_PENDING' (no account; stored in enrollments)security definer; requires has_scope('ENROLL'); validates the vocab + the ESCALATE rule.
Grant/revoke a member’s scopes(didn’t exist)supabase.rpc('set_user_scopes', { p_user_id, p_scopes })new RPC; security definer; ENROLL-gated; ESCALATE rule. Backs /members.
Cancel a pending invite(didn’t exist)supabase.rpc('cancel_enrollment', { p_invited_discord_id })new RPC; ENROLL-gated. (Or an RLS DELETE policy on enrollments for ENROLL holders — pick whichever is simpler.)
Search the archive (OCR text)(didn’t exist)supabase.rpc('search_archive', { q }) → timeline-shaped rows + headlinenew RPC; security_invoker → RLS applies; APPROVED-only; ranked, capped ~50, ts_headline snippet.
List members + their scopes(didn’t exist)supabase.from('profiles').select('*, scopes:user_scopes(scope)').order('username')RLS: any member reads profiles; user_scopes SELECT = own rows OR has_scope('ENROLL').
List pending invites(didn’t exist)supabase.from('enrollments').select('*, inviter:profiles!enrollments_invited_by_fkey(username)')RLS: SELECT = has_scope('ENROLL').
Live updatesGET /live (custom WS + interest map)Supabase Realtime — timeline: supabase.channel('timeline').on('postgres_changes',{event:'*',schema:'public',table:'uploads',filter:'approved=eq.APPROVED'},…); approvals: same, filter:'approved=eq.PENDING'; post detail: table:'comments',filter:'upload_id=eq.<id>' + table:'likes',filter:'post_id=eq.<id>'. RLS auto-applies.Replaces the entire custom WS subsystem; no interest store; no manual auth.
HealthGET /GET <ref>.functions.supabase.co/health → { ok, version, ocr_engine }—
(deleted)GET /dev/token/:tokengonedumped env vars — never coming back.

Notes / conventions

  • No envelope — supabase-js returns { data, error }; trust error. The old {response,error,success,status} envelope is gone.
  • .range(from, to) is inclusive — page p, size n → from = p*n, to = p*n + n - 1.
  • Realtime payloads are row deltas ({ eventType:'INSERT'|'UPDATE'|'DELETE', new, old }) — the frontend reconciles them into the TanStack Query cache.
  • Blob-URL convention: raw uploads rows carry file_key, not a computed URL. The frontend composes VITE_CDN_BASE_URL + file_key uniformly (the file_url column on the timeline/pending_uploads/post_detail/search_archive outputs is a convenience the frontend may ignore in favour of composing the same way everywhere).
  • External media (unchanged): GET https://cdn.txarchive.org/{file_key} (uploaded blobs; also …/no_access.png and the Yorick …/*.mp4); GET https://cdn.discordapp.com/avatars/{snowflake}/{avatar}.png (avatars).

The 3 Edge Functions the frontend hits (+ health)

  • complete-login — post-OAuth: the guild gate (GUILD_GATE_ID=577993482761928734), guilds.join into GUILD_JOIN_ID=1171963758072057997, profile upsert, consume any matching enrollments row. 403 {error:'not_in_guild'} for non-members (also deletes their Supabase user). Bearer = the new session JWT; body { provider_token }.
  • create-upload — multipart/form-data (field file); validates ADD scope + MIME ∈ ALLOWED_MIME_TYPES + size ≤ UPLOAD_SIZE_LIMIT + ext-matches-MIME + the daily limit (unless UNLIMITED_UPLOADERS); writes R2; inserts the uploads row (PENDING) via the service client. Errors NO_PERMS / MIME_NOT_ALLOWED / MIME_EXT_MISMATCH / TOO_LARGE / LIMIT_REACHED. Also called by the bot (with X-Edge-Secret + x-acting-discord-id).
  • delete-upload — ?id=<uuid>; decides owner-delete vs admin-delete from the caller’s scopes; deletes the R2 object; cascades comments/likes/ocr/ocr_jobs; on admin-delete fires notify-discord {post_deleted_admin}; 204.
  • health — { ok, version, ocr_engine } for uptime checks + the frontend footer/about.

(The other two Edge Functions — notify-discord and run-ocr — are trigger/cron-invoked, not frontend-facing; they verify X-Edge-Secret.)

Data shapes (the TS the frontend works with)

type Scope = 'VIEW' | 'ADD' | 'DELETE' | 'APPROVE' | 'ENROLL' | 'ESCALATE';
type ApprovalStatus = 'PENDING' | 'APPROVED' | 'DENIED';
 
interface Profile { id: string; discord_id: string; username: string; avatar: string | null; created_at: string; updated_at: string }
interface UploadRow { id: string; uploader: string; original_file_name: string; file_key: string; mime_type: string; size: number; approved: ApprovalStatus; approved_by: string | null; approved_at: string | null; uploaded_at: string }
interface TimelineRow { id: string; uploader: string; original_file_name: string; file_key: string; mime_type: string; size: number; uploaded_at: string; approved: ApprovalStatus; approved_at: string | null; uploader_discord_id: string; uploader_username: string; uploader_avatar: string | null; file_url: string; comment_count: number; like_count: number; liked_by_me: boolean; ocr_done: boolean }
type PostDetailRow = TimelineRow;                  // same shape; not APPROVED-restricted (RLS-gated)
type SearchRow = TimelineRow & { headline: string };
type PendingUploadRow = TimelineRow;               // where approved='PENDING'; only APPROVE holders see rows
interface CommentRow { id: string; upload_id: string; content: string; created_at: string; author: Pick<Profile,'username'|'avatar'|'discord_id'> }
interface MemberRow extends Profile { scopes: { scope: Scope }[] }
interface EnrollmentRow { invited_discord_id: string; invited_by: string; scopes: Scope[]; created_at: string; inviter: { username: string } }

Backend deltas over the approved design (§5 of the unified design)

The backend design doc (txArchive/docs/superpowers/specs/2026-05-11-txarchive-supabase-rewrite-design.md) is authoritative for the backend; the unified design layers these additions:

  1. OCR engine = Google Cloud Vision TEXT_DETECTION — _shared/ocr/google-vision.ts POSTs to https://vision.googleapis.com/v1/images:annotate?key=$GOOGLE_VISION_API_KEY with { requests:[{ image:{ content:<base64> }, features:[{ type:'TEXT_DETECTION' }] }] }; returns { text: responses[0].fullTextAnnotation?.text ?? '', matches:[], engine:'google-vision' }. Env adds OCR_ENGINE=google-vision, GOOGLE_VISION_API_KEY. Non-image MIME → don’t enqueue (the trigger checks new.mime_type like 'image/%').
  2. search_archive(q text) RPC — language sql stable security invoker (so RLS on uploads still applies); returns timeline-shaped rows for APPROVED uploads whose ocr.search tsvector matches websearch_to_tsquery('english', q), ranked by ts_rank, capped ~50, with a ts_headline headline snippet. Joins through the timeline view (already security_invoker + APPROVED-only).
  3. set_user_scopes(p_user_id uuid, p_scopes text[]) RPC — security definer; requires has_scope('ENROLL'); validates the vocab; an ENROLL-but-not-ESCALATE caller can’t include ESCALATE; then delete from user_scopes where user_id=p_user_id; insert ... select p_user_id, unnest(p_scopes), auth.uid(). Backs /members grant/revoke.
  4. cancel_enrollment(p_invited_discord_id text) RPC — ENROLL-gated; withdraws a pending invite. (Or an RLS DELETE policy — pick whichever’s simpler.)
  5. post_detail view — same columns as timeline (security_invoker) but where true (no approved filter); RLS on uploads still gates it → owner / APPROVE / DELETE can load /post/:id for PENDING/DENIED; everyone else only APPROVED. (timeline and pending_uploads stay exactly as the backend design doc has them.)
  6. notify-discord builds the post URL from FRONTEND_POST_URL_TEMPLATE env (default https://txarchive.org/post/{id}) — replaces the old https://txarchive.org/?postId=<id>. All other webhook message text stays byte-for-byte. Events: post_approved, post_denied, post_deleted_admin, comment_created, ocr_done (optional), app_started (optional).
  7. Blob-URL convention — see Notes above (frontend composes VITE_CDN_BASE_URL + file_key uniformly; a generated column on uploads can’t use current_setting, so we don’t try).
  8. Edge Function CORS — corsHeaders allows FRONTEND_ORIGIN (csv: https://txarchive.org, http://localhost:5173, https://<pages-project>.pages.dev) — prod, Vite dev, CF Pages preview deploys.
  9. health Edge Function — { ok:true, version:<GITHUB_SHA>, ocr_engine:<OCR_ENGINE> }.
  10. Realtime publication on uploads, comments, likes (not profiles for v1).
  11. The bot — exactly per the backend design doc §11 (no deltas). guilds.join stays in complete-login (it needs the user’s OAuth token).

OCR & search pipeline (the new capability)

Upload approved → after update on uploads trigger → insert into ocr_jobs(upload_id) ... on conflict do nothing (only for mime_type like 'image/%') + pg_net notify-discord {post_approved} → pg_cron (every minute) POST <edge_base>/run-ocr for ≤5 PENDING/FAILED jobs (attempts < 3, with X-Edge-Secret) → run-ocr (verifies the secret): status='RUNNING' → GetObject from R2 → runOcr(bytes, mime) (= google-vision.ts) → insert into ocr(upload_id, engine, extracted_text, matches_found) (the search tsvector is generated always ... stored, GIN-indexed) → status='DONE' (or 'FAILED' + attempts++ + last_error; cron retries ≤3×). Optional notify-discord {ocr_done}. Backfill (in the migration): insert into ocr_jobs(upload_id) select id from uploads where approved='APPROVED' and mime_type like 'image/%' on conflict do nothing → pg_cron drains the whole archive over a few hours. Engine swap: change OCR_ENGINE + that engine’s creds; _shared/ocr/{google-vision,openai,tesseract}.ts each satisfy runOcr(bytes, mime): Promise<{text, matches, engine}>.

Graph View

Backlinks