Functions — Postgres functions and RPCs

Mirrors surfc/supabase/migrations/*.sql. If they drift, the SQL wins. Last verified against migrations 0001–0021 on 2026-05-15. Siblings: Tables · Triggers. Architecture narrative lives at Data Architecture.

This file lists every Postgres function in the public schema, including the two trigger functions (cross-linked to Triggers where they fire). Each entry gives the full signature, language, volatility, security mode, caller, and — for non-trivial logic — the SQL body or a tight paraphrase.

Every function in this catalog is SECURITY DEFINER with a pinned search_path. The pattern (introduced as a hardening pass in 0011 after a real outage on the Supabase “Invite User” flow) is now applied to every function defined in the repo.

The legacy match_waitlist_on_signup() function (originally 0007, rewritten by 0009 and 0011) was dropped by 0020 and again defensively by 0021. It does not exist in the current schema. The trigger that used to call it (on_auth_user_created) now fires handle_new_auth_user instead.

`upsert_ai_usage`

Atomic insert-or-increment for ai_usage_daily. The Supabase JS .upsert() helper does a full row replacement on conflict; the quota path needs request_count = current + 1 and accumulating token totals, which only the ON CONFLICT DO UPDATE SET col = table.col + EXCLUDED.col pattern can do atomically. Source: 0005_upsert_ai_usage_fn.sql; hardened with pinned search_path in 0011.

Field	Value
Signature	`upsert_ai_usage(p_user_id uuid, p_action_type text, p_window_day date, p_input_tokens bigint, p_output_tokens bigint, p_now bigint) → void`
Language	`sql`
Volatility	`volatile` (writes to `ai_usage_daily`)
Security	`definer`, `search_path = public, pg_temp`
Operates on	`ai_usage_daily`
Callers	`anthropic-proxy` Edge Function (`supabase/functions/anthropic-proxy/index.ts`) via the service-role client. No other callers.

Body:

INSERT INTO public.ai_usage_daily
  (user_id, action_type, window_day, request_count,
   input_tokens, output_tokens, created_at, updated_at)
VALUES
  (p_user_id, p_action_type, p_window_day, 1,
   p_input_tokens, p_output_tokens, p_now, p_now)
ON CONFLICT (user_id, action_type, window_day)
DO UPDATE SET
  request_count = public.ai_usage_daily.request_count + 1,
  input_tokens  = public.ai_usage_daily.input_tokens  + excluded.input_tokens,
  output_tokens = public.ai_usage_daily.output_tokens + excluded.output_tokens,
  updated_at    = excluded.updated_at;

The request_count + 1 increment hard-codes the per-call delta — failed / rate-limited / guardrail-blocked Anthropic responses must not invoke this function at all (the Edge Function only calls it on a clean pass).

Grants (SUR-316 / 0030): EXECUTE is revoked from public / anon / authenticated and granted to service_role only. Before 0030 the Supabase default left this SECURITY DEFINER function callable by any authenticated user via the PostgREST RPC endpoint — a forge-quota gap closed alongside the SUR-316 prompt-versioning work.

`insert_ai_usage_event`

Append-only INSERT into ai_usage_events — one row per managed Anthropic call, recording the prompt_name + prompt_version that produced it (SUR-316). A plain INSERT (not an upsert): every call is its own row. Written best-effort by recordUsage beside the upsert_ai_usage quota write — a failure here is logged and swallowed, so analytics never fails a user request. Source: 0029_sur316_ai_usage_events.sql.

Field	Value
Signature	`insert_ai_usage_event(p_user_id uuid, p_action_type text, p_prompt_name text, p_prompt_version int, p_input_tokens bigint, p_output_tokens bigint, p_now bigint) → void`
Language	`sql`
Volatility	`volatile` (writes to `ai_usage_events`)
Security	`definer`, `search_path = public, pg_temp`
Operates on	`ai_usage_events`
Grants	`EXECUTE` revoked from `public` / `anon` / `authenticated`; granted to `service_role` only.
Callers	`anthropic-proxy` Edge Function via the service-role client. No other callers.

Body:

INSERT INTO public.ai_usage_events
  (user_id, action_type, prompt_name, prompt_version,
   input_tokens, output_tokens, created_at)
VALUES
  (p_user_id, p_action_type, p_prompt_name, p_prompt_version,
   p_input_tokens, p_output_tokens, p_now);

prompt_version = 0 is recorded when the call used the code fallback (the prompts table was unreachable and the loader failed open) — a queryable signal distinct from any active version.

`select_fresh_transfer_blob`

Server-side TTL check on transfer-v1 rows in wrapped_key_blobs for PIN-based device transfer. Returns the most recent active transfer-v1 blob for the caller (auth.uid()) only when its server-stamped created_at falls in the freshness window. SUR-237: removed the client-clock dependency that backed the 60-second PIN expiry. Source: 0014_wrapped_key_blobs_server_time.sql.

Field	Value
Signature	`select_fresh_transfer_blob(p_max_age_ms bigint DEFAULT 60000) → setof public.wrapped_key_blobs`
Language	`sql`
Volatility	`volatile` (calls `now()`)
Security	`definer`, `search_path = public, pg_temp`
Grants	`REVOKE ALL FROM public`; `GRANT EXECUTE TO authenticated`
Operates on	`wrapped_key_blobs`
Callers	`src/supabase.js → fetchFreshTransferBlob()` (~line 420) on the receiving device during the device-transfer redeem flow.

Body:

SELECT *
FROM   public.wrapped_key_blobs
WHERE  user_id      = auth.uid()
  AND  wrapper_type = 'transfer-v1'
  AND  is_active    = true
  AND  deleted      = false
  AND  created_at   >  ((extract(epoch from now()) * 1000)::bigint - p_max_age_ms)
  AND  created_at   <= ((extract(epoch from now()) * 1000)::bigint + 5000)
ORDER BY created_at DESC
LIMIT 1;

Two safety properties:

SECURITY DEFINER bypasses RLS, so the function reads auth.uid() directly inside the body rather than accepting a user_id parameter — closes the cross-user IDOR window an attacker would otherwise have.
Both age bounds are enforced. The lower bound is the 60-second TTL; the upper bound (+5000ms) rejects rows with future-dated created_at (pre-0014 rows still carrying client-stamped timestamps, or any code path that supplies a caller-controlled created_at). Without the upper bound, a row whose created_at is hours in the future would pass the lower bound indefinitely.

The receiving client no longer does any age math — the freshness contract lives entirely on the server.

`remove_prf_device_wrapper`

Race-safe device removal for prf-v1 rows in wrapped_key_blobs. Holds row-level locks on every active prf-v1 row for the calling user, then evaluates the last-device count check + the destructive UPDATE under those locks. SUR-233 P1 fix: closes a TOCTOU between the count check and the deactivation that two concurrent removals could otherwise win against, leaving the user with zero active wrappers and no way to decrypt their notes. Source: 0016_remove_prf_device_wrapper.sql.

Field	Value
Signature	`remove_prf_device_wrapper(p_blob_id text) → jsonb`
Language	`plpgsql`
Volatility	`volatile`
Security	`definer`, `search_path = public, pg_temp`
Grants	`REVOKE ALL FROM public`; `GRANT EXECUTE TO authenticated`
Operates on	`wrapped_key_blobs`
Callers	`src/supabase.js → removePrfDeviceWrapper()` (~line 467), invoked from the LinkedDevicesModal remove button.

Return shape: jsonb with one of three contracts:

{ removed: true, active_count: <N-1> } — success; N-1 prf-v1 wrappers remain active.
{ removed: false, reason: "last_device", active_count: <N> } — refused; would lock the user out.
{ removed: false, reason: "not_found", active_count: <N> } — row already removed elsewhere.

Paraphrased body:

Resolve v_user_id := auth.uid(). Raise 'unauthenticated' (SQLSTATE 42501) if NULL.
SELECT 1 FROM public.wrapped_key_blobs WHERE user_id = v_user_id AND wrapper_type = 'prf-v1' AND is_active AND NOT deleted FOR UPDATE — takes row-level locks on every active row.
Count the locked rows. If ≤ 1, return the last_device refusal.
UPDATE the target row to is_active = false, deleted = true, updated_at = <server ms>, filtered on id, user_id, wrapper_type, is_active, deleted.
If NOT FOUND, return the not_found refusal. Otherwise return success.

Concurrent invocations serialise on the row-level locks — the second caller waits for the first to commit, then re-evaluates the count under its own lock and refuses if removing would drop below 2. A bare conditional UPDATE would not close the race under READ COMMITTED (subquery snapshots are taken per statement, not under the lock).

`adjust_image_storage_bytes`

Atomic increment/decrement of user_profiles.image_storage_bytes_used for the image-upload Edge Function. Centralises the arithmetic so concurrent uploads can’t race on a client-side read-modify-write, and floors at zero so an undercount never goes negative. SUR-327 (Phase A of SUR-235). Source: 0017_entitlements_phase_a.sql.

Field	Value
Signature	`adjust_image_storage_bytes(p_user_id uuid, p_delta bigint) → bigint`
Language	`sql`
Volatility	`volatile`
Security	`definer`, `search_path = public`
Grants	`REVOKE ALL FROM public/anon/authenticated`; `GRANT EXECUTE TO service_role` only
Operates on	`user_profiles`
Callers	`image-upload` Edge Function (`supabase/functions/image-upload/index.ts:221`) — positive delta after a successful Storage upload; negative delta on delete-account / image-delete paths.

Body:

UPDATE public.user_profiles
SET    image_storage_bytes_used = GREATEST(image_storage_bytes_used + p_delta, 0),
       updated_at = now()
WHERE  user_id = p_user_id
RETURNING image_storage_bytes_used;

The service_role-only grant is a deliberate lockdown — anon and authenticated roles must not be able to invoke this function, because they could otherwise inflate or zero a peer’s quota.

`handle_new_auth_user`

Generic profile-creation function fired by on_auth_user_created on every auth.users INSERT. Replaces the legacy match_waitlist_on_signup function the SUR-358 open-signup cutover removed — a direct signup has no waitlist row to match against, so the bootstrap had to become waitlist-agnostic. The trigger name was preserved; only the function body changed. Source: 0020_generic_profile_creation_trigger.sql.

Field	Value
Signature	`handle_new_auth_user() → trigger`
Language	`plpgsql`
Volatility	`volatile`
Security	`definer`, `search_path = public, auth, pg_temp`
Operates on	`user_profiles` (INSERT only; ON CONFLICT DO NOTHING)
Fired by	`on_auth_user_created` — AFTER INSERT on `auth.users`

Body:

INSERT INTO public.user_profiles
  (user_id, name, email, role, member_since)
VALUES
  (NEW.id,
   COALESCE(
     NULLIF(NEW.raw_user_meta_data->>'full_name', ''),
     split_part(NEW.email, '@', 1)
   ),
   NEW.email,
   'user',
   EXTRACT(EPOCH FROM NEW.created_at)::int4)
ON CONFLICT (user_id) DO NOTHING;

The other columns inherit their DEFAULTs from the user_profiles definition: month_limit = 50 (since 0012), month_usage = 0, user_tier = 'free', tier_started_at = now(), image_storage_bytes_used = 0, all Stripe columns NULL. ON CONFLICT (user_id) DO NOTHING guards against double-insert if the approve-waitlist Edge Function upserted the row first (legacy approval-after-signup path, retiring under SUR-369).

`apply_ai_usage_month_delta`

Trigger function fired by trg_ai_usage_month_delta after every write to ai_usage_daily. Recomputes user_profiles.month_usage from the calling user’s current calendar-month rows — self-healing across month boundaries. SUR-330 replaced the original additive math (which carried prior-month usage forward indefinitely) with this recompute approach. Originally captured in 0013; rewritten in 0018.

Field	Value
Signature	`apply_ai_usage_month_delta() → trigger`
Language	`plpgsql`
Volatility	`volatile`
Security	`definer`, `search_path = public, pg_temp`
Operates on	reads `ai_usage_daily`; writes `user_profiles.month_usage`
Fired by	`trg_ai_usage_month_delta` — AFTER INSERT OR UPDATE OR DELETE on `ai_usage_daily`

Paraphrased body:

Pick the affected user (NEW.user_id for INSERT/UPDATE, OLD.user_id for DELETE).
Take a per-user pg_advisory_xact_lock keyed off the user’s UUID — see concurrency note below.

Recompute month_usage from the current calendar-month window:

UPDATE public.user_profiles u
SET    month_usage = coalesce((
         SELECT sum(d.request_count)
         FROM   public.ai_usage_daily d
         WHERE  d.user_id    = v_user
           AND  d.window_day >= date_trunc('month', now())::date
           AND  d.window_day <  (date_trunc('month', now())::date + interval '1 month')
       ), 0)
WHERE  u.user_id = v_user;

Return NEW (or OLD for DELETE).

Why the advisory lock. Under READ COMMITTED, two concurrent same-user writes (e.g. a transcribe and a discover in two tabs) both fire the trigger; each trigger’s UPDATE evaluates its SUM subquery against the statement’s snapshot. EvalPlanQual after waiting on the user_profiles row lock re-evaluates only the WHERE clause against the latest committed row — it does not re-take a snapshot for the SET subquery. Without the advisory lock, the second-to-commit transaction would overwrite month_usage with a partial sum that excludes the first transaction’s just-committed ai_usage_daily row. The advisory lock forces the second trigger to wait until the first commits; its subsequent statement-start snapshot then includes the first row and the SUM is correct. pg_advisory_xact_lock auto-releases at COMMIT / ROLLBACK so the lock can never leak.

Why recompute, not delta. The earlier additive implementation (in 0013) added NEW.request_count on every INSERT and applied boundary-crossing deltas on UPDATE. It worked for steady-state within a single calendar month but never reset month_usage at month rollover — prior-month deltas stayed in the snapshot forever. SUR-330 made the failure visible (UsageBar reading 17 / 50 when actual usage was 1 / 50). The recompute fixes that by construction and also handles the DELETE branch the additive version skipped.