// db.ts: every database call of the app, from the server only, with Supabase's secret key. // Plain fetch to Supabase's REST API (PostgREST), so short-lived functions have no client or pool to keep alive. // The secret key's role (service_role) bypasses Row Level Security, so this file must never reach a browser. // Error messages hold the method, path, status and error code, never row values or the server's free text. export class DbError extends Error { readonly status: number; readonly code: string | undefined; constructor(message: string, status: number, code?: string) { super(message); this.status = status; this.code = code; } } export interface DbOptions { /** https://.supabase.co */ url?: string; /** sb_secret_... (Project Settings > API Keys). Never in a NEXT_PUBLIC_, VITE_ or PUBLIC_ variable. */ secretKey?: string; fetch?: typeof fetch; } type Method = 'GET' | 'POST' | 'PATCH' | 'DELETE'; export function createDb(opts: DbOptions = {}) { const base = (opts.url ?? process.env.SUPABASE_URL ?? '').replace(/\/$/, '') + '/rest/v1'; const key = opts.secretKey ?? process.env.SUPABASE_SECRET_KEY ?? ''; if (!key) throw new Error('SUPABASE_SECRET_KEY is not set'); // The new secret keys go in the apikey header alone, which is all this file sends: a legacy service_role key is not accepted. if (!key.startsWith('sb_secret_')) throw new Error('SUPABASE_SECRET_KEY must be a secret key (sb_secret_...)'); const f = opts.fetch ?? fetch; async function call(method: Method, path: string, o: { query?: Record; body?: unknown } = {}): Promise { const qs = o.query ? '?' + new URLSearchParams(o.query).toString() : ''; const headers: Record = { apikey: key }; if (o.body !== undefined) { headers['Content-Type'] = 'application/json'; headers.Prefer = 'return=representation'; } let res: Response; try { res = await f(base + path + qs, { method, headers, body: o.body === undefined ? undefined : JSON.stringify(o.body) }); } catch { throw new DbError(`Database request failed: ${method} ${path} -> network`, 0, 'network'); } if (!res.ok) { let code: string | undefined; try { code = ((await res.json()) as { code?: string }).code; } catch { // no JSON body: the status alone is reported } throw new DbError(`Database request failed: ${method} ${path} -> ${res.status} ${code ?? ''}`.trim(), res.status, code); } const text = await res.text(); return (text ? JSON.parse(text) : undefined) as T; } const eq = (v: string): string => `eq.${v}`; return { /** The app's user for this sign-in, created the first time. Returns its id (the users table in tables.sql). */ async upsertUser(input: { provider: string; providerUserId: string; email: string | null }): Promise { const now = new Date().toISOString(); const match = { provider: eq(input.provider), provider_user_id: eq(input.providerUserId) }; const found = await call<{ id: string }[]>('GET', '/users', { query: { ...match, select: 'id', limit: '1' } }); if (found[0]) { await call('PATCH', '/users', { query: match, body: { last_sign_in_at: now, ...(input.email ? { email: input.email } : {}) } }); return found[0].id; } const created = await call<{ id: string }[]>('POST', '/users', { body: { provider: input.provider, provider_user_id: input.providerUserId, email: input.email, last_sign_in_at: now }, }); return created[0]!.id; }, }; } export type Db = ReturnType;