ExcelRewind architecture documentation suite · module: backend-data · source: supabase/migrations/*.sql, supabase/schema.sql, supabase/functions/*, supabase/tests/*
This module is a single Supabase Postgres database (project referenced by SUPABASE_URL) plus eight
Deno Edge Functions in supabase/functions/. There is deliberately no API server — every
client (the Excel add-in apps/addin-excel, the Google Sheets add-on, the team console
apps/team, the admin SPA apps/admin, the marketing site apps/website) talks to
Postgres directly via supabase-js/PostgREST, calling SECURITY DEFINER RPCs for anything that
needs authorization logic, and reads plain RLS-gated tables (devices, org_keys,
user_keys) where the policy alone is sufficient. Anything that must run under a trusted service-role key
(billing webhooks, superadmin console actions, exam file signing, DNS domain verification) is isolated into an Edge
Function instead of ever shipping the service-role key to a browser.
Schema evolution is a flat, hand-numbered, idempotent-by-construction migration sequence of
35 files in supabase/migrations/YYYYMMDD_*.sql (dated 2026-07-11 → 2026-07-31 in this
snapshot), applied with
supabase db push or pasted into the SQL Editor (see OPS.md). supabase/schema.sql
is the original one-shot bootstrap (profiles, app_config, auth_handoff); every migration after it is additive —
create table if not exists, add column if not exists, create or replace function
— so the sequence can be re-run end-to-end against a fresh project. There is no ORM: every table, policy, trigger and
RPC is raw SQL, and the 21-file test suite in supabase/tests/ (each file a self-contained
begin; …; rollback; block run by node supabase/tests/run.mjs over the Supabase management
API) is the closest thing to a schema contract test.
The database is also the trust boundary for the product's two hardest constraints: (1) no session-authed
path may ever grant a paid tier — only the Stripe/Razorpay webhook (service role) writes
profiles.tier to a paid value (security fix C-1, 20260724_org_no_forged_paid_tier.sql); and
(2) the server must be able to prove it never holds a usable decryption key for a paid-tier
.rewind file — the org_keys / user_keys / org_keys_retired tables
store only ciphertext, KDF parameters, and non-reversible fingerprints (20260729_org_keys.sql,
20260730_encryption_wiring.sql).
Every table currently defined across the migration sequence, grouped by subsystem. FK/ownership edges only —
see §6 for the RPCs that mediate access (most tables have no direct authenticated write policy; a
SECURITY DEFINER function is the only writer).
erDiagram
AUTH_USERS ||--o| PROFILES : "id (1:1, on delete cascade)"
PROFILES }o--o| ORGS : "org_id (member of)"
ORGS ||--o{ ORG_INVITES : "org_id"
ORGS ||--o| ORG_KEYS : "org_id (1:1)"
ORGS ||--o{ ORG_KEYS_RETIRED : "org_id"
PROFILES ||--o| USER_KEYS : "id (1:1)"
ORGS ||--o{ EXAMS : "org_id"
EXAMS ||--o{ EXAM_SUBMISSIONS : "exam_id"
PROFILES ||--o{ EXAM_SUBMISSIONS : "student_id"
PROFILES ||--o{ DEVICES : "user_id"
PROFILES ||--o{ USAGE_EVENTS : "user_id"
PROFILES ||--o{ BUG_REPORTS : "user_id"
PROFILES ||--o{ COUPON_REDEMPTIONS : "user_id"
COUPONS ||--o{ COUPON_REDEMPTIONS : "coupon_id"
PROFILES ||--o{ REFERRALS : "referrer_id"
PROFILES ||--o{ REFERRALS : "referee_id (unique)"
TIERS ||--o{ PROFILES : "key = profiles.tier (no FK, text key)"
TIERS ||--o{ FEATURE_FLAGS : "scope=tier, subject=key (no FK)"
ORGS ||--o{ FEATURE_FLAGS : "scope=org, org_id"
PROFILES ||--o{ FEATURE_FLAGS : "scope=user, subject=uid (no FK)"
COHORT_GRANTS }o..o| ORGS : "org_id (set once applied)"
AUTH_USERS {
uuid id PK
text email
}
PROFILES {
uuid id PK "references auth.users, on delete cascade"
text email
text tier "FK-less key into tiers; paid values ONLY writable by service-role webhook"
text status "active | revoked"
uuid org_id FK
text org_role "owner | admin | member"
text telemetry "minimal | full | off"
text referral_code UK
text referred_by
timestamptz tier_expires_at
text stripe_customer_id
text razorpay_customer_id
}
ORGS {
uuid id PK
text name
text kind "enterprise | cohort"
text tier
int seats
boolean has_members "via tiers, not stored here"
uuid owner_id FK "unique when not null"
text domain UK
boolean domain_verified
boolean auto_join
text domain_token
int pending_seat_request
}
ORG_INVITES {
bigint id PK
uuid org_id FK
text email
text role "admin | member"
text token UK
text status "pending | accepted | revoked"
uuid invited_by FK
timestamptz expires_at
}
TIERS {
text key PK "free | starter | team | enterprise (+ custom)"
text name
numeric monthly_price
int sort_order
boolean active "retire = false, never delete-with-users"
boolean has_members "manager+seats flag"
int seat_limit "total incl. manager; null = unlimited; solo=1"
}
FEATURE_FLAGS {
text scope PK "global | tier | org | user"
uuid org_id FK "set only when scope=org"
text subject "tier key or user uuid; null for global/org"
text feature PK
text state "allowed | forced | disabled"
}
COHORT_GRANTS {
bigint id PK
text email
text tier
int seats
text status "pending | applied"
uuid org_id FK "set once applied"
}
EXAMS {
uuid id PK
uuid org_id FK
text name
int duration_min
jsonb kiosk_settings "camera/mic/tier2/paste-flag + sealed"
text status "active | archived"
uuid created_by FK
}
EXAM_SUBMISSIONS {
uuid id PK
uuid exam_id FK
uuid student_id FK
text storage_path "exam-submissions bucket key"
int duration_s
jsonb flags
text integrity_hash
text status
}
ORG_KEYS {
uuid org_id PK, FK "1:1 with orgs"
text wrapped_omk_passphrase "ciphertext only"
text wrapped_omk_recovery "ciphertext only"
text salt_passphrase
text salt_recovery
int iters
text omk_fingerprint "SHA-256, non-reversible"
text org_pub "RSA-OAEP-3072 SPKI, PLAINTEXT (public key)"
text wrapped_org_priv "ciphertext, sealed under OMK"
boolean enabled
boolean seal_member_files
}
ORG_KEYS_RETIRED {
uuid org_id PK, FK
text omk_fingerprint PK
text wrapped_omk "retired OMK sealed under CURRENT OMK"
text org_pub
text wrapped_org_priv "sealed under the RETIRED OMK"
}
USER_KEYS {
uuid user_id PK, FK "1:1 with profiles/auth.users"
text wrapped_umk_passphrase
text wrapped_umk_recovery
text salt_passphrase
text salt_recovery
int iters
text umk_fingerprint
text user_pub "PLAINTEXT public key"
text wrapped_user_priv "ciphertext"
boolean enabled
}
DEVICES {
bigint id PK
uuid user_id FK
text device_id
text label
timestamptz last_seen
}
USAGE_EVENTS {
bigint id PK
uuid user_id FK
text event
jsonb props "metadata only, never cell/note content"
}
VIEWER_EVENTS {
bigint id PK
uuid viewer_id "anon localStorage id, NOT an account"
text event "fixed vocabulary, CHECK-bounded"
text source
text utm_campaign
jsonb props "capped 1KB"
}
BUG_REPORTS {
bigint id PK
uuid user_id FK
text description
text rewind_path "bug-reports bucket, only if user consented"
text workbook_path
}
COUPONS {
bigint id PK
text code UK
text kind "percent | flat | free_months"
numeric value
text tier
int max_uses
int uses
}
COUPON_REDEMPTIONS {
bigint id PK
bigint coupon_id FK
uuid user_id FK "unique per (coupon_id,user_id)"
}
REFERRALS {
bigint id PK
uuid referrer_id FK
uuid referee_id FK, UK
text code
}
PROMO_CONFIG {
int id PK "=1, single row"
boolean referrals_enabled
int referee_free_months
int referrer_free_months
text grant_tier "default starter"
}
PRICING_CONFIG {
int id PK "=1, single row"
int ent_included_seats
numeric ent_per_seat
int ent_max_seats
numeric annual_discount_pct
}
APP_CONFIG {
int id PK "=1, single row"
boolean enabled "maintenance switch"
text message
}
AUTH_HANDOFF {
text nonce PK
text access_token
text refresh_token
timestamptz created_at "10-minute TTL, one-time claim"
}
UNINSTALL_FEEDBACK {
bigint id PK
text event "install | uninstall"
text reason
text ip "rate-limit key"
}
profiles.tier and feature_flags.subject (for scope=tier/user) are plain text keys,
not foreign keys — deliberate, per 20260720_tier_feature_control.sql: "a profile whose tier
isn't listed just resolves global-only — don't crash." tier_delete() is the enforcement point instead
(blocks deleting a tier that still has users).
public.effective_features() is the single entitlement gate every client reads before showing/hiding a
feature (camera, audio, spotlight, cursor_tracking, snapshot, autosave, record_all_workbooks, analytics, encryption —
the full list is just "every feature with a global row", so a new feature ships by adding one seed row,
no function change). Resolution is most-specific-wins (resolve_feature_state in
20260720_tier_feature_control.sql), with one hard floor:
flowchart TD
Start(["effective_features() for user U, tier T, org O"]) --> G{"global row state?"}
G -- "disabled" --> KILL["Effective = DISABLED — hard floor.
No tier/org/user row can re-enable it."]
G -- "allowed / forced / no row" --> TIERQ{"tier row for T exists?"}
TIERQ -- yes --> TIERV["candidate = tier state"]
TIERQ -- no --> GLOBV["candidate = global state (or 'allowed' if no global row)"]
TIERV --> ORGQ{"org row for O exists? (only if U in an org)"}
GLOBV --> ORGQ
ORGQ -- yes --> ORGV["candidate = org state"]
ORGQ -- no --> KEEP1["candidate unchanged"]
ORGV --> USERQ{"user row for U exists?"}
KEEP1 --> USERQ
USERQ -- yes --> USERV["candidate = user state — narrowest, wins"]
USERQ -- no --> FINAL
USERV --> FINAL["Effective state returned to client"]
FINAL --> UI{"add-in / team console UI"}
UI -- allowed --> U1["user's own toggle decides"]
UI -- forced --> U2["locked ON — 'Managed by your administrator'"]
UI -- disabled --> U3["hidden / locked OFF"]
style KILL fill:#fde2e2,stroke:#b91c1c
style USERV fill:#dbeafe,stroke:#1e40af
Precedence in one line, straight from the SQL: resolve_feature_state(g,t,o,u) = case when g='disabled' then
'disabled' else coalesce(u,o,t,g) end. A narrower explicit 'allowed' row can re-enable
a broader 'forced' (e.g. a superadmin forces analytics on for the free tier, but a specific
account exception sets it back to allowed so that one paying customer can opt out) — every state is
stored as an explicit row now (no more "allowed = delete the row", which was the pre-v3.17 tighten-only model in
20260716_feature_flags.sql). feature_reset(scope, subject) deletes a target's row so it
falls back to the next-broader level.
Account-exception targeting (v3.18.0, 20260721_tiers_manager_seats.sql):
account_lookup(email) resolves one purchasing email to the correct (scope, subject) pair —
scope='org' + the org id when the account belongs to a Team/Enterprise org (so every member inherits the
exception via profiles.org_id), else scope='user' + the user id for a solo account. The
admin Tiers/Accounts UI feeds that pair straight into feature_set(scope, subject, feature, state).
flowchart LR
subgraph Clients
ADDIN["apps/addin-excel
(Office.js task pane)"]
TEAM["apps/team console"]
ADMIN["apps/admin SPA"]
WEB["apps/website
(account.html, signed-out)"]
VIEWER["apps/viewer
(zero-login SPA)"]
ADDON["apps/addon-sheets
(Apps Script sidebar)"]
end
subgraph "supabase-js / PostgREST (anon or authenticated JWT)"
RPCS["SECURITY DEFINER RPCs
e.g. effective_features, org_dashboard,
org_accept_invite, exam_submit, my_referral"]
DIRECT["Direct RLS-gated table access
devices (select), org_keys, user_keys,
org_keys_retired (select/insert/update)"]
end
subgraph "Edge Functions (service-role key, never sent to a browser)"
ADMINOPS["admin-ops
(owner Bearer token + superadmin OTP)"]
STRIPE["stripe-webhook
(Stripe signature)"]
RAZOR["razorpay-webhook"]
EXAMFN["exam-storage
(signed URL broker)"]
VERIFY["verify-domain
(real DNS TXT lookup)"]
SENDINV["send-invite"]
UNINSTALL["uninstall-feedback
(--no-verify-jwt)"]
METRICS["metrics
(owner Bearer token)"]
end
PG[("Postgres: profiles, orgs, tiers,
feature_flags, exams, org_keys,
usage_events, viewer_events, …")]
STORAGE[("Storage buckets:
exam-submissions (private),
bug-reports (private)")]
ADDIN -- "auth (email OTP / Google handoff),
effective_features, exam_submit,
telemetry (usage_events insert)" --> RPCS
ADDIN -- "read/write own org_keys, user_keys" --> DIRECT
TEAM -- "org_dashboard, org_invite,
org_feature_set, exam_create" --> RPCS
ADMIN -- "owner-token actions" --> ADMINOPS
WEB -- "public_tiers, public_promo_config (anon,
signed-out); get_my_org (raw REST,
requires a signed-in session)" --> RPCS
VIEWER -- "viewer_events insert (anon key,
schema-bounded, no auth)" --> DIRECT
ADDON -- "poll-diff, own .rewind generation
(no direct DB write — see addon-sheets docs)" -.-> RPCS
RPCS --> PG
DIRECT --> PG
ADMINOPS --> PG
STRIPE -- "profiles.tier UPDATE by email/customer_id" --> PG
RAZOR --> PG
EXAMFN -- "signed PUT/GET URLs" --> STORAGE
EXAMFN --> PG
VERIFY -- "orgs.domain_verified UPDATE" --> PG
SENDINV --> PG
UNINSTALL -- "uninstall_feedback INSERT" --> PG
METRICS -- "metrics_* function reads" --> PG
flowchart LR PASS["Org passphrase /
recovery code
(never transmitted)"] -->|"PBKDF2-SHA256, 310000 iters"| KDF["Derived wrapping key
(client memory only)"] KDF -->|"AES-256-GCM wrap"| OMK["Org Master Key (OMK)
(client memory only)"] OMK -->|"AES-GCM(OMK)"| WRAPPED["wrapped_omk_passphrase
wrapped_omk_recovery
(ciphertext)"] OMK -->|"SHA-256(OMK)"| FP["omk_fingerprint
(match-check only)"] OMK -->|"seals"| PRIVKEY["wrapped_org_priv
(ciphertext PKCS8 RSA priv)"] RSAKEYGEN["RSA-OAEP-3072 keypair
(client-generated)"] --> PUBKEY["org_pub
(PLAINTEXT — safe, it's a public key)"] RSAKEYGEN --> PRIVKEY WRAPPED --> SUPA[("public.org_keys / user_keys /
org_keys_retired — ciphertext,
salts, KDF params, fingerprint, pubkey only")] FP --> SUPA PUBKEY --> SUPA PRIVKEY --> SUPA SUPA -.->|"server / superadmin / full DB compromise"| NEVER["CANNOT derive OMK/UMK —
only undecryptable blobs"]
All three side-effects live inside one Postgres trigger, handle_new_user(), fired
after insert on auth.users. Each optional block is independently wrapped in its own
begin…exception when others then null end so a bug in referrals or cohorts can never fail the signup
itself.
sequenceDiagram
participant Client as "Add-in / website signup"
participant Auth as "Supabase Auth (auth.users)"
participant Trig as "handle_new_user() trigger"
participant PG as "public.profiles / orgs / cohort_grants / referrals"
Client->>Auth: "signUp(email, password, { data: { ref: code? } })"
Auth->>Auth: "insert row into auth.users"
Auth->>Trig: "AFTER INSERT trigger fires (security definer)"
Trig->>PG: "insert into profiles (id, email) on conflict do nothing"
Trig->>PG: "org domain auto-join: lookup orgs where domain=email domain
and auto_join=true and domain_verified=true"
alt matching verified org with a free seat
Trig->>PG: "update profiles set org_id, org_role='member',
tier = CASE WHEN _org_tier_is_paid(org) THEN org.tier ELSE tier END"
end
Trig->>PG: "assign referral_code (retry loop on unique_violation)"
alt raw_user_meta_data.ref present AND promo_config.referrals_enabled
Trig->>PG: "insert into referrals, grant referee + referrer
promo_config.grant_tier for N months (tier_expires_at)"
end
Trig->>PG: "check cohort_grants for a pending row matching this email
(only if user has no org yet)"
alt a superadmin pre-provisioned this email as a cohort sponsor
Trig->>PG: "materialize cohort org, attach as owner,
mark cohort_grants.status='applied'"
end
Trig-->>Auth: "return NEW (never raises — signup always succeeds)"
Auth-->>Client: "session (access_token, refresh_token)"
Google blocks OAuth inside Office.js webviews, so the add-in opens the system browser, which completes OAuth and stashes the session under a one-time nonce for the pane to claim by polling.
sequenceDiagram participant Pane as "Excel task pane" participant Browser as "system browser
(google-signin.html)" participant Google as "Google OAuth" participant PG as "public.auth_handoff" Pane->>Pane: "generate 192-bit nonce" Pane->>Browser: "window.open(viewer/google-signin.html?nonce=…)" Browser->>Google: "OAuth redirect flow" Google-->>Browser: "access_token, refresh_token" Browser->>PG: "rpc stash_handoff(nonce, access_token, refresh_token)" Note over PG: "nonce must be >= 32 chars,
rows older than 10 min are purged first" loop poll every ~2s, up to 10 min Pane->>PG: "rpc claim_handoff(nonce)" alt row found and fresh PG-->>Pane: "delete...returning access_token, refresh_token
(one-time claim)" Pane->>Pane: "supabase.auth.setSession(tokens)" else not yet stashed / expired PG-->>Pane: "empty result" end end
sequenceDiagram
participant Admin as "org owner/admin (team console)"
participant PG as "Postgres RPCs"
participant Invitee as "invitee (any signed-in session)"
Admin->>PG: "rpc org_invite(email, role)"
PG->>PG: "_caller_org() guard: owner/admin only"
PG->>PG: "seat check: members + live pending invites < orgs.seats"
alt seat_limit reached
PG-->>Admin: "raise 'seat_limit'"
else seat available
PG->>PG: "upsert org_invites (token = _gen_token(), status='pending', expires_at=+14d)"
PG-->>Admin: "return token (frontend builds the link — no email sent by this backend)"
end
Invitee->>PG: "rpc my_pending_invites() — by auth.email()"
PG-->>Invitee: "[{ token, org_name, role, invited_by, expires_at }]"
Invitee->>PG: "rpc org_accept_invite(token)"
PG->>PG: "already_in_org? invalid_or_expired_invite? seat_limit (re-checked)?"
PG->>PG: "_org_tier_is_paid(org): join orgs->profiles(owner)->tiers,
check tiers.has_members for the OWNER's current tier"
alt owner's tier is NOT a manager/paid tier (org never actually paid)
PG->>PG: "attach member WITHOUT touching profiles.tier
(prevents inheriting a forged paid tier)"
else owner's tier IS paid (webhook-granted)
PG->>PG: "attach member, tier = orgs.tier (genuine inheritance)"
end
PG->>PG: "mark org_invites.status='accepted'"
PG-->>Invitee: "return org id"
razorpay-webhook is the India-entity twin, same shape)sequenceDiagram participant Stripe as "Stripe" participant Fn as "supabase/functions/stripe-webhook
(--no-verify-jwt, service-role key)" participant PG as "public.profiles" Stripe->>Fn: "POST event (checkout.session.completed /
customer.subscription.deleted / invoice.payment_failed)" Fn->>Fn: "stripe.webhooks.constructEventAsync(body, sig, secret)
via SubtleCryptoProvider (Deno has no Node crypto)" alt bad signature Fn-->>Stripe: "400 bad signature" else verified alt checkout.session.completed Fn->>Stripe: "subscriptions.retrieve(subId) — price lives on the sub, not the session" Fn->>Fn: "priceToTier[priceId] (env-configured map)" Fn->>PG: "update profiles set tier=…, stripe_customer_id=…
where email ILIKE escape(email) (LIKE-metachar-escaped)" else customer.subscription.deleted OR invoice.payment_failed (final) Fn->>PG: "downgradeToFree(customerId):
update profiles set tier='free' where stripe_customer_id=customerId" alt no row matched (never linked) Fn->>Stripe: "customers.retrieve(customerId) -> email" Fn->>PG: "fallback: update profiles set tier='free' where email ILIKE escape(email)" end end Fn-->>Stripe: "200 ok" end Note over Fn,PG: "handler error -> 500 so Stripe retries the delivery.
This + razorpay-webhook (same shape, HMAC-signed) are the ONLY code allowed to write a paid tier value."
sequenceDiagram
participant Student as "rostered student (add-in kiosk mode)"
participant Fn as "exam-storage edge fn (service role)"
participant Bucket as "storage bucket: exam-submissions (private)"
participant PG as "Postgres: exams / exam_submissions"
Student->>Fn: "POST { action:'upload_url', exam_id } (Bearer JWT)"
Fn->>PG: "verify caller is a rostered member of the exam's org"
alt not rostered
Fn-->>Student: "403 not_rostered"
else rostered
Fn->>Bucket: "createSignedUploadUrl({org}/{exam}/{uid}-{ts}.rewind)"
Fn-->>Student: "{ url, storage_path } — 10 min TTL"
Student->>Bucket: "PUT encrypted .rewind bytes directly to url"
Student->>PG: "rpc exam_submit(exam_id, storage_path, duration_s, flags, integrity_hash)"
PG->>PG: "verify storage_path prefix == {org}/{exam}/{uid}- (can't claim another student's object)"
PG->>PG: "upsert exam_submissions on (exam_id, student_id) — latest wins"
end
Note over Fn,Bucket: "Review path (owner/admin): POST { action:'review_url', submission_id }
-> 5 min signed GET, after an org-ownership check."
flowchart TB BD["backend-data
(this module: Postgres + Edge Functions)"] ADDIN["addin-excel"] TEAM["team console"] ADMIN["admin SPA"] WEB["website"] VIEWER["viewer"] ADDON["addon-sheets"] STRIPE_EXT["Stripe"] RAZOR_EXT["Razorpay"] DNS["Public DNS
(_excelrewind.<domain> TXT)"] ADDIN <-->|"supabase-js: auth, effective_features,
org_keys/user_keys, exam RPCs, telemetry"| BD TEAM <-->|"supabase-js: org_* RPCs, cohort_* RPCs,
feature RPCs"| BD ADMIN -->|"HTTPS + owner Bearer token + superadmin OTP"| BD WEB -->|"raw REST rpc calls (anon):
get_my_org, public_tiers, public_promo_config"| BD VIEWER -->|"anon insert: viewer_events"| BD ADDON -.->|"no direct DB access documented here —
see apps/addon-sheets"| BD STRIPE_EXT -->|"webhook POST, HMAC-signed"| BD RAZOR_EXT -->|"webhook POST"| BD BD -->|"HTTPS TXT lookup"| DNS
| Other side | Direction | Protocol / entry point | Purpose |
|---|---|---|---|
| addin-excel | in/out | supabase-js (HTTPS/PostgREST), authenticated JWT | Email-OTP / Google-handoff auth, effective_features, device registration (2-device cap), org_keys/user_keys read-write for encryption, exam RPCs, usage_events telemetry insert, bug reports. |
| team console (apps/team) | in/out | supabase-js, authenticated JWT (owner/admin) | org_create/org_dashboard/org_invite/org_accept_invite, org_feature_set, cohort sponsor RPCs, exam create/list/submissions. |
| admin SPA (apps/admin) | out (this module is the callee) | HTTPS POST to admin-ops Edge Function, owner Bearer token + superadmin OTP | Superadmin console: users, tiers, feature flags, pricing, promos, cohorts, data reset — all service-role writes, never direct client access to the underlying RPCs. |
| website (apps/website) | in | raw REST POST /rest/v1/rpc/<fn> — anon key for public_tiers()/public_promo_config(); a signed-in user's JWT for get_my_org() (revoked from anon, granted to authenticated only) | public_tiers(), public_promo_config() (pricing page, signed-out), get_my_org() (account.html, signed-in). |
| viewer (apps/viewer) | in | supabase-js insert, anon key, zero-login | viewer_events — schema-bounded telemetry (fixed event vocabulary CHECK, 1 KB props CHECK) since the anon key is public and unauthenticated. |
| Stripe | in | HTTPS POST, Stripe webhook signature (constructEventAsync) | stripe-webhook Edge Function — the only path allowed to write a paid profiles.tier. |
| Razorpay | in | HTTPS POST, HMAC-SHA256 webhook signature (Web Crypto) | razorpay-webhook Edge Function — mirrors Stripe for the India entity: writes razorpay_customer_id AND flips profiles.tier (including to a paid value) on payment_link.paid. Co-equal with Stripe as the only two paid-tier writers post-C-1, not a metadata-only mirror. |
| Windows/Mac companion installer | in | HTTPS POST, unauthenticated (--no-verify-jwt) | uninstall-feedback Edge Function inserts install/uninstall telemetry with the service-role key; a DB trigger (not RLS, since service role bypasses RLS) rate-limits by IP. |
| Public DNS | out | TXT record lookup | verify-domain Edge Function resolves _excelrewind.<domain> and flips orgs.domain_verified on match — the real verification path (org_verify_domain_manual RPC is a test-only override). |
| Storage (Supabase Storage, same project) | in/out | Signed PUT/GET URLs | Private buckets exam-submissions and bug-reports; exam-storage Edge Function is the only broker (provider-agnostic adapter seam — swapping to S3 is a new adapter, no caller change). |
| metrics dashboard consumer (owner tooling) | in | HTTPS POST, owner Bearer token | metrics Edge Function calls metrics_event_hourly/metrics_numeric_hourly/metrics_prop_dist/metrics_w2_retention_v2/metrics_source_dist/metrics_share_loop/etc — all revoked from anon/authenticated. |
| File | Function / object | What it does | Why it matters |
|---|---|---|---|
supabase/schema.sql | handle_new_user() (base version), profiles, app_config, auth_handoff | Bootstrap: every signup gets a profiles row via an auth.users trigger; global maintenance switch; Google OAuth handoff table. | Every later migration create or replaces handle_new_user() "byte-for-byte the prior body plus one guarded block" — reconstructing the CURRENT trigger means reading the latest definition (in 20260724_org_no_forged_paid_tier.sql), not this file. |
20260712_accounts.sql | register_device definer, touch_device definer | 2-device-per-account sharing cap. Eviction happens ONLY at sign-in (register_device), never at touch_device, so two devices can't ping-pong each other out. | The one place device-sharing enforcement lives; a UX bug here directly changes how strict the license limit feels. |
20260713_org_teams.sql | _caller_org() (internal), org_create, org_dashboard, org_invite, org_accept_invite, org_set_role, _gen_token() | Self-serve orgs: seats, roles, 14-day invites, one-org-per-owner. | _caller_org() is called from inside 15+ other definer functions as the auth/role guard — it is the de-facto session-authorization primitive for the whole org subsystem (confirmed dead-code-free in supabase/tests/AUDIT.md). |
20260714_enterprise_email.sql | is_free_email_domain(email) anon | Blocks Gmail/Outlook/Yahoo/etc from owning an org (org_create guard). | Must be kept in sync with apps/team/src/freeEmail.ts by hand — no shared source of truth. |
20260715_cohorts.sql | cohort_provision service, cohort_invite_bulk definer, handle_new_user() (cohort-grant block) | Sponsor-funded group licences; a cohort IS an org with kind='cohort' — no parallel system. Can provision a sponsor by email before they have an account (parks a row in cohort_grants, materialized on signup). | Same mechanic serves "professor provisions students" and "friends bulk-buy" — only the funding source differs. |
20260720_tier_feature_control.sql | resolve_feature_state definer (pure/immutable), effective_features(), feature_set service, tier_list/create/set/delete service | The OVERRIDE resolution engine (§3) and the tier designer. | resolve_feature_state is deliberately extracted as a standalone pure SQL function "so it's unit-testable" — see supabase/tests/01_resolve_feature_state.sql. This is the single place the global-disabled hard-floor rule is encoded. |
20260721_tiers_manager_seats.sql | account_lookup(email) service, tier_list() (v2 shape) | Canonical 4-tier reconciliation (renames legacy business→team); resolves a purchasing email to the right (scope, subject) for account-level exceptions. | Single source of truth for the tier table per CLAUDE.md — never hardcode a tier list anywhere else; always read via tier_list()/public_tiers(). |
20260724_org_no_forged_paid_tier.sql | _org_tier_is_paid(org) (internal), org_create, org_accept_invite, handle_new_user() (current versions) | Security fix C-1: stops org_create from self-writing tier='enterprise'; gates tier inheritance on the org owner's tier actually being a manager tier. | THE authoritative current body of handle_new_user(), org_create(), org_accept_invite() — every earlier migration's copy of these functions is superseded. Regression-tested in supabase/tests/16_c1_forged_tier.sql. |
20260729_org_keys.sql | org_keys table + RLS policies (no RPC — plain policy-gated table) | Zero-knowledge org key store: wrapped OMK (2 ways: passphrase + recovery code), KDF params, salts, a non-reversible fingerprint. | Explicitly documents the security property in a code comment: "the server / a superadmin / a full Supabase compromise can NEVER derive the OMK — only undecryptable blobs." This is the schema-level enforcement of the SDD's "paid-tier data is never processed" guarantee. |
20260730_encryption_wiring.sql | user_keys, org_keys_retired tables | Per-user master key (UMK) mirror of org_keys; retired-OMK generations so files predating a key rotation keep opening (unlock chain: passphrase → current OMK → retired OMK → retired RSA priv → file recipient). | RSA-OAEP-3072 public keys (org_pub/user_pub) are stored PLAINTEXT on purpose — members encrypt-to-org/-user at save time without ever holding a master key. Do not mistake this for a leak. |
20260717_exams.sql | exams, exam_submissions, _exam_member_org(exam) (internal), exam_submit definer | Org-scoped exam definitions + one-submission-per-student (latest wins, upsert). exam_submit verifies the storage path prefix matches the caller's own uid — a student can't claim another student's uploaded object. | The private exam-submissions bucket has NO storage.objects policies at all — access is only ever through signed URLs from the exam-storage Edge Function under the service role. This is deliberate, not an oversight. |
20260711_telemetry.sql, 20260711_viewer_telemetry.sql | usage_events, viewer_events, set_telemetry_level definer | Two-tier consent telemetry (signed-in: minimal/full/off; anon viewer: fixed CHECK-bounded vocabulary). | Comment in the migration is explicit: "NEVER cell contents, note text, sheet/workbook names, or any free text" — enforced client-side but the CHECK constraints on viewer_events are the actual abuse backstop since the anon key is public. |
supabase/functions/stripe-webhook/index.ts | setTierByEmail, setTierByCustomerId, downgradeToFree | Billing code — no API server. LIKE-metacharacter-escapes the email before an ilike match to stop a crafted checkout email upgrading someone else's row. | This file, together with razorpay-webhook (the India-entity twin, same setTierByEmail/setTierByCustomerId shape — see below), is the ONLY code permitted to write a paid tier value post-C-1. Both are service role, deployed --no-verify-jwt because the provider signs its own requests. |
supabase/functions/razorpay-webhook/index.ts | setTierByEmail, setTierByCustomerId, downgradeToFree, verifySignature | India-entity billing twin of stripe-webhook: payment_link.paid → paid tier via RZP_LINK_TO_TIER; subscription.cancelled / a terminal payment.failed → downgrade to free. No SDK — verifies X-Razorpay-Signature as a raw HMAC-SHA256 hex digest via Web Crypto (constant-time crypto.subtle.verify). | Both webhooks run simultaneously and both can write a paid profiles.tier — see the C-1 invariant below; treat any doc or comment that names only Stripe as incomplete. |
supabase/functions/exam-storage/index.ts | StorageAdapter interface, supabaseStorage() | Provider-agnostic signed-URL broker; the storage-specific code is isolated behind a 2-method interface so swapping Supabase Storage → S3 is a new adapter, zero caller changes. | Direct example of the CLAUDE.md "keep the core cloud-agnostic" constraint implemented in code, not just policy. |
supabase/functions/admin-ops/index.ts | 27 case actions (users_list, user_set_tier, user_delete, tier_create/set/delete/list, feature_set/reset/get, cohort_create/set/list/approve_seats/revoke_grant, coupon_create/list/set_active, pricing_get/set, promo_get/set, referral_stats, account_lookup, maintenance_set, data_reset) | Single dispatch Edge Function backing the entire admin SPA — every superadmin mutation funnels through here under owner Bearer token + superadmin OTP, then calls the service-role RPCs. | There is no other privileged write surface; auditing this file's action list IS auditing what a compromised admin session can do. |
20260728_admin_data_reset.sql | admin_data_reset(p_confirm) service | Wipes all users/telemetry, keeps config (tiers, feature_flags global/tier, pricing, promo, app_config). Requires the exact phrase 'RESET DATA' and asserts every config table is still non-empty before returning, else it raises (aborting the whole transaction). | The self-check-after-wipe pattern (raise if tiers/feature_flags/etc came out empty) is what stops this from ever silently nuking the tier catalog alongside the users. |
20260723_user_delete.sql | user_delete(user, email) service | Hard-delete with two guards: exact-email confirmation (anti-fat-finger) and refuses if the user owns an org (would orphan the team). Sweeps every user-keyed row with no FK cascade by hand before deleting auth.users (which cascades profiles). | Distinct from user_set_status='revoked' (reversible) — this is the irreversible right-to-be-forgotten path; the manual sweep list is exactly the set of tables that have no FK to profiles. |
supabase/tests/run.mjs + supabase/tests/*.sql | 21 files, one begin;…rollback; transaction each | The schema's regression suite — runs against the LIVE database via the Supabase management API, never commits. | supabase/tests/AUDIT.md (produced by the same effort) is a read-only dead-code audit: confirms all 63 public functions are reachable, flags the 15 legacy metrics_* views as dead (subsequently dropped in 20260722_drop_legacy_metrics_views.sql), and documents redeem_coupon/my_referral as "backend-complete, frontend-pending" rather than garbage. |
pg_cron job downgrades expired promo/coupon tiers — not just the webhooks.
public.downgrade_expired_tiers() (20260714_promos_admin.sql) runs at 03:17 UTC and sets
tier='free' for every profile where tier_expires_at has passed, skipping org
members (org_id is null in the where) since their tier is managed by org
membership, not an expiry column. This is how a referral/coupon free_months grant (§5, redeem_coupon,
handle_new_user's referral block) actually unwinds — the schedule is registered only if the
pg_cron extension is available on the project; the migration falls back to a manual cron.schedule
snippet in a comment if not. Not called from any client; confirmed alive-by-design in supabase/tests/AUDIT.md.
20260724_org_no_forged_paid_tier.sql,
org_create() self-wrote tier='enterprise' gated only by is_free_email_domain —
any ~$1 custom-domain email defeated it, and org_accept_invite/handle_new_user let members
inherit that forged tier. The fix makes profiles.tier only ever settable to a paid value by the
service-role Stripe/Razorpay webhook, then uses the org OWNER's current profiles.tier (checked against
tiers.has_members) as a now-trustworthy "is this org genuinely paid" signal for member inheritance.
If you ever add a new tier-writing code path, it MUST go through the webhook or be re-audited against this exact
attack.
org_keys/user_keys/org_keys_retired store ciphertext, KDF params, salts, and
SHA-256 fingerprints ONLY. The wrapping secrets (org passphrase, recovery code, personal passphrase) never reach the
server. Per user memory rewind-encryption-gap: this is the SHIPPED encryption system (v3.23.0-.2,
2026-07-17) — but the SOC 2 audit (SOC2_AUDIT.md) found 48 findings with fixes not yet started; do not
assume every security claim in surrounding docs is independently verified. Never add a server-side "convenience" key
or plaintext master-key column — every migration touching these tables repeats that warning verbatim in its header
comment.
alter table … enable row level security; followed by no insert/
update/delete policy for authenticated — all writes funnel through a SECURITY DEFINER RPC
that validates auth.uid()/role first. The three deliberate exceptions are devices (plain
select policy, writes still RPC-only), and org_keys/user_keys/
org_keys_retired (full select/insert/update policies, because "there is nothing to authorize beyond org
membership" — see the comment in 20260729_org_keys.sql). If you see a table with a plain
update-own-row policy proposed for profiles, that's exactly the anti-pattern schema.sql's
own comments call out (it would let a user edit their own tier/status).
profiles.tier and feature_flags.subject have no foreign key to
tiers.key — intentional, so a profile on a tier that gets renamed/retired mid-flight just resolves
global-only instead of crashing. tier_delete() is the actual guard: it raises tier_in_use
if any profile still references the key. Tiers are retired (active=false), never
hard-deleted while in use.
viewer_events accepts anonymous inserts (the web viewer has zero login) with a fixed event vocabulary
CHECK and a 1 KB props size CHECK as the actual defense; the migration header literally says "the
SCHEMA is the defense... No rate limiting is possible at the DB layer here" and defers real rate limiting to a future
M2 API server. uninstall_feedback solves the analogous problem differently: since its writer (the
Edge Function) uses the service role and RLS doesn't apply to service role, the per-IP throttle is implemented as a
before insert trigger (fires regardless of role) rather than an RLS policy.handle_new_user() has at least 5 successive full rewrites across the sequence;
org_dashboard() is repeated in 20260713 and 20260715). To know a function's
CURRENT behavior, always read its last create or replace in migration-date order — reading an
earlier migration's copy in isolation will show you a superseded version. This documentation's function bodies were
sourced from the latest definitions only.
20260729, 20260730,
20260731, 20260717_invite_self_service) end with NOTIFY pgrst, 'reload schema';
— raw-SQL-applied migrations otherwise leave PostgREST's cache stale until the next auto-reload, producing a
misleading "Could not find the table … in the schema cache" error for a table/function that DOES exist. If you add a
new table/RPC via the SQL Editor rather than a deploy pipeline, add this notify.
org_invite()'s seat check
(members + live pending invites < seats) is a plain count under the function body, not a row lock —
two concurrent invites could both pass at seats-1 and overfill by one. The code comment marks this
explicitly: -- ponytail: … Fine at team scale; add select … for update on the org row if it ever matters.
Not a bug to "fix" reflexively — a deliberate scale tradeoff.
CLAUDE.md, this constraint is upstream of this module (enforced in apps/addin-excel), but
it shapes what this schema is allowed to store: usage_events.props and viewer_events.props
are metadata-only by CHECK/comment-convention (numbers, booleans, short enum strings) — no cell content, note text,
or sheet/workbook names ever land in this database. The .rewind file contents themselves never touch
Postgres at all except as an opaque blob behind a signed URL in the exam-submission and bug-report buckets.