Tables — column-level catalog
Tables — column-level catalog
Mirrors
surfc/supabase/migrations/*.sql. If they drift, the SQL wins. Last verified against migrations0001–0021on 2026-05-15;prompts/ai_usage_events(0027–0029),note_links(0034), and thetranscribe_handwritingprompt seed (0035, SUR-526) verified 2026-06-01; the admin-dashboard tablesadmin_audit_log/fixed_costs/cost_snapshot(0039, SUR-289) verified 2026-06-11; the synced lexicon tableslenses(0042) andcollections/collection_memberships(0043, SUR-598) verified 2026-06-16. Siblings: Functions · Triggers. Architecture narrative lives at Data Architecture.
This file lists every Postgres public.* table in the Surfc app database plus every Dexie store from surfc/src/db.js. Each section gives the column shape, constraints, indexes, and RLS posture — and links to the Functions and Triggers that act on it.
The admin-dashboard tables admin_audit_log, fixed_costs, and cost_snapshot (SUR-230’s schema groundwork, SUR-289) landed in 0039 and are catalogued below. They have no Dexie counterpart — none of them sync to the client; the admin intranet reaches them only through service-role Edge Functions after CF Access JWT verification.
Synced-table sync columns (SUR-739 / SUR-652 — applies to all 8 synced tables). books, notes, custom_ideas, note_links, lenses, collections, collection_memberships, and note_signals each additionally carry a server-only change_seq bigint NOT NULL — the monotonic visibility watermark stamped by t02_change_seq (migration 0051) — plus a <table>_user_change_seq_idx on (user_id, change_seq) for the keyset incremental pull. change_seq is never client-writable (server-assigned; not in any upsert payload) and is not mirrored to Dexie. The per-table column tables below predate 0050/0051 and don’t repeat this shared column; see Triggers + Data Architecture for the full semantics.
user_profiles
Per-user identity, monetization tier, managed-AI quota, image-storage usage, and Stripe billing state. The single source of truth for what a user is allowed to do. Source: 0009_user_profiles.sql, extended by 0012 (quota), 0017 (entitlements), and 0019 (billing).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
profile_id | uuid | no | gen_random_uuid() | Surrogate primary key. |
user_id | uuid | no | — | FK auth.users(id) ON DELETE CASCADE. UNIQUE — one profile per auth user. |
name | text | yes | — | Display name. Bootstrapped from raw_user_meta_data.full_name, fallback split_part(email,'@',1) (handle_new_auth_user). |
display_name | text | yes | — | User-editable display name. |
email | text | yes | — | Mirror of auth.users.email at signup; not kept in sync afterward. |
role | text | no | 'user' | CHECK in ('user', 'admin'). |
profile_bio | text | yes | — | User-editable bio. |
month_limit | int4 | no | 50 | SUR-92 single source of truth for the managed-AI monthly cap. DEFAULT was 30 in 0009; flipped to 50 in 0012. |
month_usage | int4 | no | 0 | Current calendar-month request count snapshot. Maintained by apply_ai_usage_month_delta on every ai_usage_daily write. |
percent_use | real | no | GENERATED ALWAYS | month_usage / month_limit (or 0 when month_limit = 0). STORED — read by the Profile UsageBar. |
member_since | int4 | no | — | Unix epoch seconds of auth.users.created_at, captured at insert. |
created_at | timestamptz | no | now() | When the profile row itself was materialised. |
updated_at | timestamptz | no | now() | Last write to the profile row. |
user_tier | text | no | 'free' | SUR-327 SSoT for monetization tier. CHECK in ('free', 'pro'). Stripe webhook writes here on subscribe; never on cancel (resolver owns the pro→free decision). |
tier_started_at | timestamptz | no | now() | When the current tier began. Drives the SUR-328 lapsed-Pro 30-day grace window. |
image_storage_bytes_used | bigint | no | 0 | Bytes used in the note-images bucket. Maintained transactionally by adjust_image_storage_bytes. Backfilled once from storage.objects in 0017. |
allocation_override | int4 | yes | — | SUR-92 additive monthly-quota boost. NULL or 0 = no boost. Applied only when expires_at >= CURRENT_DATE. |
allocation_override_expires_at | date | yes | — | Date the override stops applying. NULL on either field means “no override” — both must be set. |
stripe_customer_id | text | yes | — | Stripe cus_…. Lazily populated by create-checkout-session on first checkout; persists across cancel+resubscribe. |
stripe_subscription_id | text | yes | — | Stripe sub_… of the active or most-recently-cancelled subscription. Retained on cancellation for audit. |
subscription_status | text | yes | — | Mirrored Stripe lifecycle status. CHECK in ('active', 'trialing', 'past_due', 'canceled', 'paused', 'incomplete', 'incomplete_expired') or NULL. |
subscription_current_period_end | timestamptz | yes | — | End of the current Stripe billing period. Drives the deferred grace window in getResolvedEntitlements. |
PK: profile_id · FK: user_id → auth.users(id) ON DELETE CASCADE · Unique: user_id; partial stripe_customer_id WHERE NOT NULL (user_profiles_stripe_customer_id_key) · Indexes: user_profiles_role_idx (role).
RLS: Enabled. SELECT and UPDATE to authenticated users for their own row (user_id = auth.uid()). No INSERT or DELETE policy — rows are materialised by handle_new_auth_user (SECURITY DEFINER) and removed by auth.users cascade. The UPDATE policy’s writable surface is restricted client-side to display_name and profile_bio; quota, tier, and Stripe columns are service-role writes only.
Triggers fired on this table: none. Triggers that write here: on_auth_user_created (INSERTs new rows) and trg_ai_usage_month_delta (updates month_usage).
waitlist_requests
Legacy waitlist signup queue. Sunsetting — the open-signup cutover (SUR-358 family) removed the trigger back-link in 0020 and the RLS gate it backed in 0021. Retirement of the table itself is tracked under SUR-369. Source: 0007_waitlist_requests.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Primary key. |
email | text | no | — | UNIQUE. Submitted via the public waitlist form. |
name | text | no | — | Display name from the form. |
use_case | text | yes | — | Free-text “why are you signing up”. |
user_id | uuid | yes | — | FK auth.users(id). Null until matched to a confirmed auth signup. The legacy match_waitlist_on_signup trigger back-filled this on approved-signup; that trigger was dropped in 0020. |
role | text | no | 'user' | CHECK in ('user', 'admin'). |
status | text | no | 'pending' | CHECK in ('pending', 'approved', 'rejected'). |
approved_at | timestamptz | yes | — | Set by admin approval. |
created_at | timestamptz | no | now() | When the waitlist row was submitted. |
PK: id · FK: user_id → auth.users(id) · Unique: email.
RLS: Enabled. INSERT open to anon (the public form has no auth). SELECT to authenticated users for their own row only (user_id = auth.uid()). No UPDATE / DELETE policy — admin approval flow goes through the approve-waitlist Edge Function (service role).
Triggers fired on this table: none.
books
Books / articles the user is reading. Core sync entity — mirrored by the Dexie books store. Source: 0001_initial_schema.sql, extended by 0025 (cover art + ISBN, SUR-198).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated (mirrors the Dexie row). |
user_id | uuid | no | — | FK auth.users. Ownership for RLS. |
title | text | no | — | Book title. |
author | text | yes | '' | Author name. |
isbn | text | yes | null | Bibliographic key (0025, SUR-198). Resolved opportunistically from the Open Library Search API and reused for higher-quality cover re-resolution; not captured at create time. |
cover_url | text | yes | null | Fully-qualified cover image URL (0025, SUR-198). Open Library M-size, or a user-supplied https URL when cover_source = 'manual'. Served directly via <img>. |
cover_source | text | yes | null | Cover provenance (0025, SUR-198): 'openlibrary' | 'manual' | null. |
cover_resolved_at | bigint | yes | null | Unix ms of the last cover-resolution attempt (0025, SUR-198). Drives future re-resolution; null = never attempted. |
created_at | bigint | no | — | Unix milliseconds — client-stamped at create time. |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change. Drives last-write-wins sync. |
deleted | boolean | yes | false | Soft-delete flag. Cascades to child notes rows client-side. |
The four 0025 columns are additive and nullable, so they survive the pending books → sources rename (SUR-464) and the dedup unique-constraint work (SUR-463/465) untouched. Cover resolution is client-side (no proxy) and sends only title/author/ISBN to Open Library; the cover fields sync last-write-wins by updated_at like the rest of the row — a newer cloud row is authoritative including explicit nulls, so a user clearing a cover converges across devices (covers are derived and re-resolvable). See Architecture → Data for the sync-flow detail.
PK: id · FK: user_id → auth.users · Indexes: books_user_updated_idx (user_id, updated_at) (0006, supports incremental sync). The 0025 columns are not indexed — they’re read on a row already in hand, never queried by key.
RLS: Enabled. Policy users see own books — auth.uid() = user_id USING + WITH CHECK. The 0007 waitlist-EXISTS overlay was layered on top of this and removed in 0021 (open-signup cutover).
Triggers fired on this table: none.
notes
User highlights, notes, and transcribed images, optionally linked to a books row. Core sync entity — mirrored by the Dexie notes store. Source: 0001_initial_schema.sql, extended by 0002 (source), 0003 (source_id + source_meta), 0010 (chapter), 0037 (ink_crop_path), 0048 (content_tag).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated. |
user_id | uuid | no | — | FK auth.users. Ownership for RLS. |
book_id | text | yes | — | FK books(id) ON DELETE SET NULL. A note can outlive its book. |
text | text | no | '' | Note body. Stored encrypted (enc:v2:<iv>.<ct> with note-ID AAD) under the Master Key architecture. Server never sees plaintext. |
page | text | yes | '' | Free-form page reference (e.g. “p. 42”). |
tags | jsonb | yes | '[]' | Idea tags (canon + custom). Mirrors Dexie’s multi-entry *tags index. |
image_path | text | yes | — | Storage path of the source image, if any. Stored, not the bytes. |
source | text | yes | 'manual' | Provenance: 'manual' / 'image' / 'readwise' (future) / 'kindle' (future). |
source_id | text | yes | — | Dedup key from the originating system (e.g. 'readwise:highlight:999'). Non-unique index. |
source_meta | jsonb | yes | '{}' | Source-specific richness (author, book title, etc). |
chapter | text | yes | — | Optional chapter or section label (mobile design refresh). |
ink_crop_path | text | yes | — | Storage path of a handwriting-region crop cut from the source photo (SUR-524). Path only, like image_path; plaintext (images are not E2EE). |
content_tag | text | yes | — | Plaintext-opaque content fingerprint for dedup (SUR-638). A per-user HMAC-SHA256(subkey, normalize(text) + '\x00' + (book_id ?? '')) where subkey is derived from the Master Key via HKDF (info surfc-content-tag-v1). Deterministic per-user so dedup converges across devices; a keyed MAC, not a hash, so the server learns only “these two of the owner’s notes are equal” — never plaintext, and not dictionary-attackable without the MK. Computed client-side from plaintext at save (the one place it’s in hand); the server never reads or queries it (no server index). Nulled on any book_id change (merge/rehome) and re-derived on the next unlocked load. |
created_at | bigint | no | — | Unix milliseconds — client-stamped. |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change. |
deleted | boolean | yes | false | Soft-delete flag. Book deletion cascades into this via a client-side transaction. |
PK: id · FKs: user_id → auth.users, book_id → books(id) ON DELETE SET NULL · Indexes: notes_source_id_idx (user_id, source_id) WHERE source_id IS NOT NULL (non-unique on purpose — cross-device dedup would block the outbox); notes_user_updated_idx (user_id, updated_at).
RLS: Enabled. Policy users see own notes — auth.uid() = user_id. Same 0007 → 0021 history as books.
Triggers fired on this table: none.
custom_ideas
User-defined idea / concept tags layered on top of the Adler 102 canon. Core sync entity — mirrored by the Dexie customIdeas store. Source: 0001_initial_schema.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated. |
user_id | uuid | no | — | FK auth.users. |
name | text | no | — | Idea name. Surfaced in the AddIdeaSheet alongside the canon. |
description | text | yes | '' | Free-text description, prompted via AddIdeaBanner after creation. |
created_at | bigint | no | 0 | Unix milliseconds — client-stamped. |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change. |
deleted | boolean | yes | false | Soft-delete flag. |
PK: id · FK: user_id → auth.users · Indexes: custom_ideas_user_updated_idx (user_id, updated_at).
RLS: Enabled. Policy users see own ideas — auth.uid() = user_id. Same 0007 → 0021 history as books.
Triggers fired on this table: none.
note_links
The parent→child edge between a printed source note and the reader’s handwritten margin note (SUR-84). The handwritten note stays a first-class notes row; only this edge is new. A parent can carry many edges, and user_metadata.user_annotation is DERIVED from them at read time (deriveUserAnnotations in surfc/src/lib/userAnnotations.js) — there is no annotation column, so the reader’s words never live in plaintext server-side; they stay in the child note’s encrypted text. The edge itself carries only ids + a relation type (no note content), so it never touches the encryption seam. Core sync entity — mirrored by the Dexie note_links store. Source: 0034_note_links.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated. |
user_id | uuid | no | — | FK auth.users. Ownership for RLS. |
from_note_id | text | no | — | FK notes(id) ON DELETE CASCADE. The parent (printed-source) note. |
to_note_id | text | no | — | FK notes(id) ON DELETE CASCADE. The child (handwritten margin) note. |
relation_type | text | no | 'handwritten_annotation' | Edge kind. Defaulted for forward-compat (future see-also / reply relations). |
created_at | bigint | no | — | Unix milliseconds — client-stamped. The reader’s annotation order is preserved by staggering this across a multi-note “Add the margins” save (SUR-526). |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change, including soft-delete, so a tombstone wins last-write-wins against a stale device that still holds the live edge. |
deleted | boolean | no | false | Soft-delete flag. |
PK: id · FKs: user_id → auth.users, from_note_id → notes(id) ON DELETE CASCADE, to_note_id → notes(id) ON DELETE CASCADE (hard-deleting either endpoint note clears the EDGE, never the other note — the account-deletion backstop, which is why deleteCloudData needs no explicit note_links delete). · Indexes: note_links_updated_at_idx (updated_at) (incremental fetchSince), note_links_from_note_id_idx (from_note_id) (resolve a parent’s annotations + cascade soft-delete by parent), note_links_user_id_idx (user_id) (RLS predicate).
RLS: Enabled. Policy users see own note_links — auth.uid() = user_id for ALL. Deliberately no AND deleted = false: the owner must still pull their own edge tombstones via fetchSince so a soft-delete converges across devices (the invariant in supabase/test/invariant.softDelete.test.js). Explicit grants (post-0033): anon none; authenticated SELECT/INSERT/UPDATE/DELETE (RLS narrows to own rows); service_role ALL.
Triggers fired on this table: none.
lenses
A reader’s user-minted lens: a saved AND-intersection of canon ideas that scopes the Commonplace at read time (SUR-598 PR1). Stores only the lens definition (name + leaf_ids) — never note content, never a node or an edge; resolution (notes whose tags ⊇ leaf_ids) happens client-side against the already-decrypted notes, so it carries no encryption seam. Canonical lenses are NOT rows here — they live in code (surfc/src/lib/canonicalLenses.js); only user-minted lenses are stored and synced. Core sync entity — mirrored by the Dexie lenses store. Source: 0042_lenses.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated. The canon: prefix is reserved for canonical lenses — saveLens rejects a user id starting with canon:. |
user_id | uuid | no | — | FK auth.users. Ownership for RLS. |
name | text | no | — | The lens’s display name. |
leaf_ids | text[] | no | '{}' | The canon leaf ideas the lens intersects. A note matches when its tags ⊇ this set. |
combinator | text | no | 'AND' | Forward-compat metadata. PR1 resolves AND only; the COOCCUR / threshold combinator is SUR-599. |
created_at | bigint | no | — | Unix milliseconds — client-stamped. |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change, including soft-delete, so a tombstone wins last-write-wins. |
deleted | boolean | no | false | Soft-delete flag. |
PK: id · FK: user_id → auth.users · Indexes: lenses_updated_at_idx (updated_at) (incremental fetchSince), lenses_user_id_idx (user_id) (RLS predicate).
RLS: Enabled. Policy users see own lenses — auth.uid() = user_id for ALL. Deliberately no AND deleted = false: the owner must still pull their own lens tombstones via fetchSince so a soft-delete converges across devices (supabase/test/invariant.softDelete.test.js). Explicit grants (post-0033, SUR-499): anon none; authenticated SELECT/INSERT/UPDATE/DELETE (RLS narrows to own rows); service_role ALL.
Triggers fired on this table: none.
collections
A manual grouping of notes (“a domain across your ideas”) that scopes the Commonplace at read time, like a lens or a source (SUR-598 PR2). Stores only the collection definition (name); the notes it groups are joined through collection_memberships. Plaintext metadata, no encryption seam. Core sync entity — mirrored by the Dexie collections store. Source: 0043_collections.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated. |
user_id | uuid | no | — | FK auth.users. Ownership for RLS. |
name | text | no | — | The collection’s display name. |
created_at | bigint | no | — | Unix milliseconds — client-stamped. |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change, including soft-delete. |
deleted | boolean | no | false | Soft-delete flag. Deleting a collection also soft-deletes its membership rows (deleteCollection, one Dexie transaction). |
PK: id · FK: user_id → auth.users · Indexes: collections_updated_at_idx (updated_at) (incremental fetchSince), collections_user_id_idx (user_id) (RLS predicate).
RLS: Enabled. Policy users see own collections — auth.uid() = user_id for ALL. Deliberately no AND deleted = false (same tombstone-convergence reason as lenses). Explicit grants (post-0033, SUR-499): anon none; authenticated SELECT/INSERT/UPDATE/DELETE; service_role ALL.
Triggers fired on this table: none.
collection_memberships
The note↔collection many-to-many join (SUR-598 PR2). A membership is a query-time join row — NOT a node and NOT a graph edge: it fabricates no co-occurrence between the notes a collection groups (the deliberate contrast with note_links). Plaintext (ids only). Core sync entity — mirrored by the Dexie collection_memberships store. Source: 0043_collections.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Deterministic — `${collectionId}:${noteId}` (membershipId, surfc/src/db.js) — so a note is at most once per collection and add/remove are idempotent across devices. |
user_id | uuid | no | — | FK auth.users. Ownership for RLS. |
note_id | text | no | — | FK notes(id) ON DELETE CASCADE. The grouped note. |
collection_id | text | no | — | FK collections(id) ON DELETE CASCADE. The owning collection. |
created_at | bigint | no | — | Unix milliseconds — client-stamped. |
updated_at | bigint | no | 0 | Unix milliseconds — bumped on every change, including soft-delete. |
deleted | boolean | no | false | Soft-delete flag. Set by softDeleteMembershipsForNote (note delete) and deleteCollection (collection delete). |
PK: id · FKs: user_id → auth.users, note_id → notes(id) ON DELETE CASCADE, collection_id → collections(id) ON DELETE CASCADE (hard-deleting either endpoint clears the membership, never the other endpoint — the account-deletion backstop behind the app’s soft-delete, mirroring note_links; deleteCloudData deletes notes first, so memberships clear for free). · Indexes: cmemberships_updated_at_idx (updated_at) (incremental fetchSince), cmemberships_user_id_idx (user_id) (RLS predicate), cmemberships_note_id_idx (note_id) (read a note’s memberships + cascade by note), cmemberships_collection_id_idx (collection_id) (resolve / cascade a collection’s members).
RLS: Enabled. Policy users see own collection_memberships — auth.uid() = user_id for ALL. Deliberately no AND deleted = false (same tombstone-convergence reason as lenses). Explicit grants (post-0033, SUR-499): anon none; authenticated SELECT/INSERT/UPDATE/DELETE; service_role ALL.
Triggers fired on this table: none.
wrapped_key_blobs
End-to-end encryption — one row per enrolled device (or transient PIN-based device transfer). Each row wraps the same Master Key using a different unlock method (passkey PRF, transfer PIN, etc.). The Master Key itself is never stored in plaintext. Source: 0008_wrapped_key_blobs.sql, extended by 0014 (server-stamped created_at) and 0015 (device_label).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | text | no | — | Primary key. Client-generated. |
user_id | uuid | no | — | FK auth.users. |
wrapper_type | text | no | — | Wrapper kind: 'prf-v1' (passkey PRF), 'transfer-v1' (PIN-based device transfer), 'pin-v1' (legacy). Not constrained by CHECK — guarded application-side. |
wrapped_key | text | no | — | Base64 ciphertext of the Master Key under the wrapper key. |
iv | text | no | — | Base64 AES-GCM IV. |
salt | text | no | — | Base64 KDF salt. |
key_version | integer | no | 1 | Master-Key generation. Future rotation moves this forward. |
is_active | boolean | no | true | Tombstone flag for revoked devices. |
created_at | bigint | no | ((extract(epoch from now()) * 1000)::bigint) | Unix milliseconds — server-stamped DEFAULT since 0014 (SUR-237). The 60s freshness check on transfer-v1 blobs no longer trusts client wall clocks. |
updated_at | bigint | no | 0 | Unix milliseconds. |
deleted | boolean | yes | false | Soft-delete flag. |
device_label | text | yes | — | "Platform · Browser" captured once at enrolment via src/utils/deviceLabel.js. Only used for display in the LinkedDevicesModal. |
PK: id · FK: user_id → auth.users · Indexes: wrapped_key_blobs_user_active_idx (user_id, is_active) WHERE deleted = false (partial — drops tombstoned rows).
RLS: Enabled. Policy users manage own key blobs — auth.uid() = user_id for ALL. Two SECURITY DEFINER RPCs bypass RLS for narrower operations that need server-side guarantees: select_fresh_transfer_blob (server-side TTL on transfer-v1) and remove_prf_device_wrapper (race-safe last-device guard on prf-v1).
Triggers fired on this table: none.
ai_usage_daily
Per-user managed Anthropic usage. One row per (user_id, action_type, window_day). Aggregate model — request count + token totals are accumulated atomically by upsert_ai_usage. Drives the per-user quota gate and the Profile UsageBar (via month_usage snapshot on user_profiles). Source: 0004_ai_usage_tracking.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Surrogate primary key. |
user_id | uuid | no | — | FK auth.users. |
action_type | text | no | — | CHECK in ('transcribe', 'discover'). Both action types share one cap. The transcribe_handwriting action (SUR-526) normalizes to 'transcribe' at the usage-write boundary so it doesn’t violate this CHECK; per-prompt granularity is preserved via prompt_name on ai_usage_events. |
window_day | date | no | — | UTC calendar date of the request. |
request_count | integer | no | 0 | Successful Anthropic responses in this window. Failed/rate-limited/guardrail-blocked calls never increment. |
input_tokens | bigint | no | 0 | Accumulated input tokens from Anthropic usage. |
output_tokens | bigint | no | 0 | Accumulated output tokens. |
created_at | bigint | no | 0 | Unix milliseconds — first upsert for this window. |
updated_at | bigint | no | 0 | Unix milliseconds — last upsert for this window. |
PK: id · FK: user_id → auth.users · Unique: (user_id, action_type, window_day) (ai_usage_daily_unique_window) · Indexes: ai_usage_daily_user_day_idx (user_id, window_day) (supports the monthly SUM query in getMonthlyUsage).
RLS: Enabled. Policy users read own usage — SELECT only, auth.uid() = user_id (foundation for a future self-service usage page). No INSERT / UPDATE policy — the anthropic-proxy Edge Function writes via the service-role key.
Triggers fired on this table: trg_ai_usage_month_delta — recomputes user_profiles.month_usage after every INSERT, UPDATE, or DELETE.
prompts
Versioned managed-AI system prompts loaded per call by anthropic-proxy (SUR-316). One active row per name; a prompt change is a new (name, version) row, not an in-place edit — that keeps prompt_version a stable telemetry key. Service-role-only. Source: 0027_sur316_prompts_table.sql, seeded 0028_sur316_seed_prompts.sql. The handwriting-only OCR prompt transcribe_handwriting — extracts only the reader’s handwriting from a note’s stored source photo for “Add the margins” — was added as a fourth name, seeded 0035_sur526_seed_transcribe_handwriting.sql (SUR-526). The loader fails open to the byte-identical code constant in prompts.ts; a prompt_version of 0 on ai_usage_events is the outage sentinel.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Surrogate primary key. |
name | text | no | — | 'transcribe' | 'transcribe_handwriting' (SUR-526) | 'discover_canon' | 'discover_with_custom'. |
version | int | no | — | Monotonic per name, ≥ 1 (CHECK). 0 is reserved by the loader as the code-fallback sentinel and is never stored. |
body | text | no | — | The system-prompt string. discover_with_custom carries a {{customList}} placeholder filled at call time; the others are verbatim. |
model | text | no | 'claude-sonnet-4-6' | Anthropic model used for this prompt. |
max_tokens | int | no | — | max_tokens for the call (> 0, CHECK). transcribe 500, transcribe_handwriting 700 (a page can carry several distinct margin notes, each its own array element), discover 512. |
is_active | boolean | no | false | Exactly one active row per name (partial unique index). |
notes | text | yes | — | Changelog / why this version exists. |
created_at | timestamptz | no | now() | Row creation. |
created_by | uuid | yes | — | FK auth.users(id) ON DELETE SET NULL — author attribution; a prompt outlives its author’s account deletion. |
PK: id · Unique: (name, version) (prompts_name_version_unique); partial unique prompts_one_active_per_name (name) WHERE is_active (at most one active row per name — activation is a deactivate-then-activate flip in one transaction).
RLS: Enabled, no policies. REVOKE ALL FROM public, anon, authenticated; GRANT ALL TO service_role — the anthropic-proxy Edge Function reads it via the service-role key, which bypasses RLS. No client access.
ai_usage_events
Append-only per-call log of managed Anthropic calls, tagged with the prompt_name + prompt_version that produced each call (SUR-316). Powers “managed AI quality by prompt_version”. Distinct from ai_usage_daily, which stays the quota aggregate — this table does not gate quota and is written best-effort. Source: 0029_sur316_ai_usage_events.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Surrogate primary key. |
user_id | uuid | no | — | FK auth.users(id) ON DELETE CASCADE — events leave with the user on account deletion (service-role-only table, so delete-account can’t pre-clear it; CASCADE keeps auth.admin.deleteUser from being blocked). |
action_type | text | no | — | CHECK in ('transcribe', 'discover') — transcribe_handwriting (SUR-526) is recorded as 'transcribe' here too, distinguished by prompt_name. |
prompt_name | text | no | — | The prompt that produced the call (e.g. transcribe, transcribe_handwriting, discover_canon). |
prompt_version | int | no | — | Active version, or 0 when the call used the code fallback (prompts table unreachable). |
input_tokens | bigint | no | 0 | Input tokens from Anthropic usage. |
output_tokens | bigint | no | 0 | Output tokens. |
created_at | bigint | no | — | Unix milliseconds — set once at insert; rows are immutable. |
PK: id · FK: user_id → auth.users · Indexes: ai_usage_events_prompt_idx (prompt_name, prompt_version, created_at) (version-slice over time).
RLS: Enabled, no policies. REVOKE ALL FROM public, anon, authenticated; GRANT ALL TO service_role. Written only via the insert_ai_usage_event RPC from the Edge Function.
stripe_webhook_events
Idempotency ledger for Stripe webhook deliveries. Primary key on Stripe’s own evt_… id; the INSERT … ON CONFLICT (event_id) DO NOTHING RETURNING event_id pattern is the dedup primitive. Source: 0019_stripe_billing.sql.
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
event_id | text | no | — | Primary key — Stripe’s evt_… id. The only deduplication identifier we have for Stripe retries. |
event_type | text | no | — | Stripe event.type, e.g. customer.subscription.created. Indexed for replay/audit. |
received_at | timestamptz | no | now() | When the webhook arrived at our endpoint. |
processed_at | timestamptz | yes | — | When the dispatch handler finished. NULL means crashed mid-dispatch (Stripe will retry). |
payload | jsonb | no | — | Full Stripe event JSON, retained for audit. Contains PII (billing address, card last4) — table is service-role-only to keep that surface tight. |
PK: event_id · Indexes: stripe_webhook_events_type_received_idx (event_type, received_at DESC).
RLS: Enabled. Zero policies for authenticated or anon. The GRANT/REVOKE matrix is locked down at the table level (REVOKE ALL FROM PUBLIC/anon/authenticated; GRANT ALL TO service_role) so even an accidentally-permissive future policy would still hit a “permission denied” before reaching RLS. The stripe-webhook Edge Function is the only intended writer and reader.
Triggers fired on this table: none.
admin_audit_log
Append-only forensic log of admin-dashboard actions — allowance edits (SUR-292), kill-switch flips (SUR-568), fixed-cost edits (SUR-377), and any future app_config write. Service-role-only writer; an admin-aware RLS SELECT policy gives admins a direct read as defense-in-depth. The trail is clean from the 0039 apply time — pre-0039 actions (e.g. the SUR-492 kill-switch) are not back-filled. Source: 0039_admin_dashboard.sql (SUR-289, under SUR-230).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Surrogate primary key. |
admin_id | uuid | yes | — | FK user_profiles(profile_id) ON DELETE SET NULL. Acting admin. SET NULL anonymises the record but keeps it — deleting an admin account must neither be blocked nor wipe forensic history. References the profile PK, not auth.users. |
action | text | no | — | What happened, e.g. set_allowance, kill_switch. |
target_type | text | no | — | Free text by design (not a narrow enum), so it logs allowance / fixed_cost / app_config / future GCE-gate writes uniformly without a schema change. |
target_id | text | yes | — | The affected entity — a user uuid, an app_config key, a fixed_costs id, etc. Nullable so a genuinely global action can still be logged. |
before | jsonb | yes | — | State snapshot before the write. NULL for a pure-creation write. |
after | jsonb | yes | — | State snapshot after the write. NULL for a pure-deletion write. |
reason | text | yes | — | Operator intent (“why killed / why restored”). Prompted-but-not-gated at the UI, hence nullable. Read by SUR-292 / SUR-568 / future app_config writes. |
created_at | timestamptz | no | now() | When the action was logged. |
PK: id · FK: admin_id → user_profiles(profile_id) ON DELETE SET NULL · Indexes: admin_audit_log_created_at_idx (created_at DESC) (recent-first listing), admin_audit_log_target_idx (target_type, target_id) (an entity’s history).
RLS: Enabled. Read policy admins read audit log — SELECT to authenticated where an EXISTS subquery confirms the caller’s own user_profiles.role = 'admin' (so an admin reads the whole log via their own JWT; a non-admin matches zero rows). Append-only enforcement lives in the GRANTs, not in any policy — service_role bypasses RLS, so the only lever against the sole writer is privilege: authenticated holds SELECT only (no write), and service_role is granted SELECT, INSERT only — deliberately not ALL — so even the Edge Function cannot UPDATE/DELETE/TRUNCATE an entry. The grant is preceded by an explicit REVOKE ALL FROM service_role: the SUR-499 / 0033 default ACL grants service_role Dxtm (TRUNCATE, REFERENCES, TRIGGER, MAINTAIN) on every new public table, and an additive grant would otherwise leave that default TRUNCATE in place. (Pinned by supabase/test/adminDashboard.rls.test.js.)
Triggers fired on this table: none.
fixed_costs
Recurring fixed costs plus a single-row operating-capital balance, for the admin spend panel. Service-role-only; edited via the SUR-377 Edge Function. Seeded with known services at placeholder amounts (the operator edits real figures). Source: 0039_admin_dashboard.sql (SUR-289).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Surrogate primary key. |
service_name | text | no | — | UNIQUE. e.g. 'Netlify', 'PostHog', 'Cloudflare'. |
monthly_usd | numeric(10,2) | no | — | Monthly cost. CHECK >= 0. For an annual row this is the as-entered monthly equivalent the operator records; presentation math lives in the panel. |
billing_cycle | text | no | — | CHECK in ('monthly', 'annual'). |
notes | text | yes | — | Free-text operator note (seeded 'placeholder — edit via SUR-377'). |
active | boolean | no | true | Whether the cost still applies. Drives the active-services listing. |
cash_balance_usd | numeric(12,2) | yes | — | Operating-capital balance — set on exactly one row, drives runway (cash_balance / monthly burn). NULL = “not set on this row”; a meaningful 0.00 is allowed and (being non-NULL) correctly occupies the single-balance slot while yielding runway 0. |
updated_at | timestamptz | no | now() | Writer-managed — the SUR-377 Edge Function sets updated_at = now() on UPDATE, matching the app_config convention. |
updated_by | uuid | yes | — | FK user_profiles(profile_id) ON DELETE SET NULL — last editor. SET NULL so deleting an admin account neither blocks the delete nor loses the cost row. |
PK: id · FK: updated_by → user_profiles(profile_id) ON DELETE SET NULL · Unique: service_name; partial unique fixed_costs_single_cash_balance ON ((true)) WHERE cash_balance_usd IS NOT NULL — single-row enforcement via a partial unique index on a constant expression (a partial unique on the column itself would only block duplicate values, still allowing two rows with different non-null balances). · Indexes: fixed_costs_active_service_idx (active, service_name) (active filter + name sort).
Seed: eight known services at placeholder amounts (Netlify, PostHog, Termly, Cloudflare, Linear, Claude Pro, Domain, Canva), ON CONFLICT (service_name) DO NOTHING so a re-run or a dev DB with edited values is neither errored nor clobbered. cash_balance_usd is intentionally left unset — no row claims a balance until the operator sets one.
RLS: Enabled, no policies. Service-role-only (SUR-499 explicit grants): REVOKE ALL FROM public, anon, authenticated; GRANT ALL TO service_role. Default-deny — the admin Edge Functions are the only reader/writer.
Triggers fired on this table: none.
cost_snapshot
Server-side cache of variable-cost upstream API envelopes (Anthropic / Supabase / Stripe), keyed by source, for the admin spend panel. Service-role-only. Append-only for v1 — retention is not strict; a follow-on prunes rows older than 30 days if it grows. There is no fetch-dedup guard, so concurrent fetches from one source land as separate rows until that pruning ships (read the latest per source via the index). Source: 0039_admin_dashboard.sql (SUR-289).
| Column | Type | Nullable | Default | Purpose |
|---|---|---|---|---|
id | uuid | no | gen_random_uuid() | Surrogate primary key. |
source | text | no | — | CHECK in ('anthropic', 'supabase', 'stripe'). Constrained (unlike admin_audit_log.target_type) — adding a variable-cost source is a deliberate DROP/ADD CONSTRAINT migration. |
payload | jsonb | no | — | The full upstream envelope, retained verbatim. |
fetched_at | timestamptz | no | now() | When the snapshot was fetched. |
PK: id · Indexes: cost_snapshot_source_fetched_idx (source, fetched_at DESC) (latest-snapshot-per-source lookup).
RLS: Enabled, no policies. Service-role-only (SUR-499 explicit grants): REVOKE ALL FROM public, anon, authenticated; GRANT ALL TO service_role. Default-deny.
Triggers fired on this table: none.
Storage buckets
note-images(private, created in0001). Path convention:<user_uuid>/<note_id>.<ext>. RLS policyusers manage own images—auth.uid()::text = (storage.foldername(name))[1](same0007 → 0021waitlist-gate history as the user-data tables). Byte counts maintained transactionally inuser_profiles.image_storage_bytes_usedviaadjust_image_storage_bytes.
Extensions
uuid-ossp(created in0001) — providesgen_random_uuid()used bywaitlist_requests,user_profiles,ai_usage_daily.
Local stores (Dexie, src/db.js)
The browser-side IndexedDB schema. Current SCHEMA_VERSION = 19. books / notes / customIdeas / note_links / lenses / collections / collection_memberships / note_signals mirror their Postgres counterparts; meta and outbox are local-only; the device-local embeddings store (v13, SUR-527) and discovery_jobs store (v17, SUR-580) are local-only-by-construction and documented in the Data Architecture narrative rather than mirrored here. Source: surfc/src/db.js.
| Store | Indexes | Purpose |
|---|---|---|
meta | key | Key-value singleton store. Holds lastSyncAt, passkeyEnrolled, encryptionPromptSeen, the cached wrapped-key blob (activeWrappedKeyBlob), master-key version, migration state. Local-only — never synced. |
books | id, createdAt, updatedAt, deleted | Mirror of Supabase books. Soft-deleted via the deleted flag; deleting a book also soft-deletes child notes inside a Dexie transaction. Carries the unindexed isbn / coverUrl / coverSource / coverResolvedAt fields (v10, SUR-198) alongside the indexed keys. |
notes | id, bookId, createdAt, updatedAt, deleted, source, sourceId, *tags, contentTag | Mirror of Supabase notes. The multi-entry *tags index supports the idea-based queries in IdeasSidebar. The scalar contentTag index (Dexie v19, SUR-638) backs the save-time content-dedup lookup. Stores plaintext imageDataUrl for offline lightbox previews; the cloud only holds the storage image_path. |
customIdeas | id, name, updatedAt, deleted | Mirror of Supabase custom_ideas. |
note_links | id, fromNoteId, toNoteId, relationType, updatedAt, deleted | Mirror of Supabase note_links (v12, SUR-84). Parent→child handwritten-annotation edges; deriveUserAnnotations joins them onto each parent’s user_metadata.user_annotation at loadAll time (the value is never stored). |
lenses | id, updatedAt, deleted | Mirror of Supabase lenses (v15, SUR-598). User-minted saved AND-intersections of canon ideas; canonical lenses are static code, not rows. Only id / updatedAt / deleted are indexed (updatedAt for fetchSince, deleted for the live-rows read). |
collections | id, updatedAt, deleted | Mirror of Supabase collections (v16, SUR-598). Manual note groupings (name only). |
collection_memberships | id, noteId, collectionId, updatedAt, deleted | Mirror of Supabase collection_memberships (v16, SUR-598). The note↔collection join; noteId / collectionId indexed so the assign sheet can read a note’s memberships and a delete can clear a collection’s or note’s rows. Deterministic id `${collectionId}:${noteId}`. |
outbox | ++id, table, recordId, createdAt | Offline write queue. Local-only — no cloud counterpart. Mutations queued when offline, flushed via flushOutbox / collapseOutboxItems() (dedup by (table, recordId), delete stickiness, chronological order) on reconnect. |
The Dexie version chain (v1 → v19) and the per-version upgrade logic live in src/db.js. Notable points across versions: deleted + updatedAt added in v2, source in v3, recordId index on outbox in v4, sourceId + sourceMeta in v5, multi-entry *tags in v6, passkey/encryption meta keys in v7, Master Key architecture meta keys in v8, chapter in v9, book cover-art + isbn fields in v10 (SUR-198), the SUR-505 tag-vocabulary rewrite in v11, the note_links edge table in v12 (SUR-84), the device-local embeddings store in v13 (SUR-527), the contemporary-canon swap in v14 (SUR-597), the lenses table in v15 and the collections + collection_memberships tables in v16 (both SUR-598), the device-local discovery_jobs store in v17 (SUR-580), the synced note_signals table in v18 (SUR-615), and the scalar notes.contentTag index in v19 (SUR-638). New tables (the lexicon tables, note_signals, embeddings, and discovery_jobs) need no upgrade function — they carry no data migration; the v19 contentTag index is likewise backfill-free (the tag derives from decrypted plaintext, so existing rows are healed lazily on the first unlocked load). SCHEMA_VERSION (used by export/import) tracks the Dexie version and is now 19.
Export / import contract. buildExport serializes books, notes, customIdeas, note_links, and the lexicon tables lenses / collections / collection_memberships (SUR-84/SUR-526/SUR-598 — a backup that omitted any synced-only table kept the notes but silently lost the associations; a lens/collection lives only in its table, never derivable from notes). parseImport defaults each of noteLinks / lenses / collections / collectionMemberships to [], so a pre-v12/v15/v16 export still imports cleanly. Import is local-authoritative, not cloud-authoritative: importMerge (add-if-absent) and importReplace (clear-then-restore, now clearing note_links and all three lexicon tables) write to Dexie only — they do not push to the cloud or the outbox, mirroring the long-standing behavior for notes/books/customIdeas. So a restored library converges to the cloud (and other devices) only as its rows are subsequently re-touched, and importReplace is a local reset, not a remote purge — the prior library’s cloud rows still exist and can re-pull on a fresh full sync. Multi-device users should re-sync rather than re-import to propagate. (Sync-reviewer note, SUR-526.) The device-local embeddings (v13) and discovery_jobs (v17) stores are excluded from buildExport but are cleared by importReplace and the full wipe — derived / orchestration state must not strand on reused note ids (regression tests assert buildExport omits each, and that the table name never reaches src/supabase.js).