Admin Console (Superadmin) — Architecture

1. Overview

apps/admin is the owner-only superadmin console for ExcelRewind. It is a small zero-router React 18 + TypeScript single-page app (Vite, port 5175 in dev) that gives the product owner a UI over metrics and every destructive/administrative action in the system: tier design, feature entitlements, user tier changes and revocation/deletion, coupons, referral/promo configuration, pricing, cohort (group-licence) management, and a global maintenance kill switch plus a one-shot pre-launch data wipe.

It is deliberately not a normal Supabase-auth application. Instead of RLS-scoped user sessions, the admin app talks to two purpose-built Supabase Edge Functions — metrics (GET, read-only analytics) and admin-ops (POST, all mutations) — authenticated with a static bearer token (OWNER_METRICS_TOKEN, stored in the browser's localStorage) plus a second factor: a live Supabase session for one hardcoded superadmin email, obtained via email OTP and carried on every admin-ops call as the x-superadmin-jwt header. Both edge functions run under the Supabase service role (bypasses RLS) — admin-ops is explicitly documented as "the ONLY write path to these [tables] from outside SQL".

Deployment: a static Cloudflare Pages site at admin.excelrewind.com, built with vite build from this workspace. apps/admin/public/_headers sets X-Robots-Tag: noindex on every route (the console must never appear in search) and a strict CSP whose connect-src is pinned to the Supabase project origin only — no third-party script/analytics origins are allowed. The app has no server component of its own; all business logic and authorization checks live in the edge function (supabase/functions/admin-ops/index.ts) and the Postgres functions/RPCs it calls under the service role. A ?demo=1 query flag skips the gate entirely and renders hardcoded sample data (src/demo.ts) with every mutating call short-circuited — it touches no real database and is deliberately left open (documented in src/api.ts) as a backend-independent preview mode.

2. Architecture diagram

flowchart TB
  subgraph ENTRY["Entry / bootstrap"]
    main["main.tsx"] --> App["App.tsx<br/>top-level auth gate + tab router"]
  end

  subgraph AUTHUI["Auth UI"]
    Gate["Gate.tsx<br/>2-factor sign-in: owner token + email OTP"]
  end

  subgraph SHELL["Shell / chrome"]
    ShellC["Shell.tsx<br/>sidebar nav (11 tabs) + topbar + demo banner"]
    UI["ui.tsx<br/>Icons, Avatar, TierBadge, StatusBadge,<br/>ToastHost/useToast, Modal, DemoFallbackBanner, ThemeToggle"]
  end

  subgraph SCREENS["screens/ (11 tabs)"]
    Overview["Overview.tsx"]
    Growth["Growth.tsx"]
    Revenue["Revenue.tsx"]
    Cohorts["Cohorts.tsx"]
    Users["Users.tsx"]
    Coupons["Coupons.tsx"]
    Referrals["Referrals.tsx"]
    Pricing["Pricing.tsx"]
    Tiers["Tiers.tsx"]
    Features["Features.tsx"]
    System["System.tsx"]
  end

  subgraph SHARED["Shared client modules"]
    Api["api.ts<br/>store (token/url), adminOps.*, fetchMetrics,<br/>ApiError, friendly(), isMissing(), types"]
    Supa["supabase.ts<br/>supabase-js client (OTP-only),<br/>SUPERADMIN_EMAIL, superadminJwt()"]
    Demo["demo.ts<br/>demoUsers/demoTiers/demoFeatures/<br/>demoPromo/demoAccountLookup"]
    Hook["useTierOptions.ts<br/>shared tier-picker hook (calls tierList)"]
    Util["util.ts<br/>fmtNum, fmtDate"]
    Charts["charts.tsx<br/>sparkline / bar chart primitives"]
  end

  main --> AUTHUI
  App -->|"authed=false"| Gate
  App -->|"authed=true"| ShellC
  ShellC --> SCREENS
  ShellC --> UI
  Gate --> Api
  Gate --> Supa
  SCREENS --> Api
  SCREENS --> UI
  SCREENS --> Demo
  SCREENS --> Hook
  SCREENS --> Util
  Overview --> Charts
  Growth --> Charts
  Revenue --> Charts
  Hook --> Api
  Api --> Supa
  System -->|"direct read only:<br/>app_config select"| Supa

Notes on the diagram: there is no client-side router library — Tab is a plain string union persisted to localStorage (rewind.admin.tab) and switched with a ternary chain in App.tsx. There is no global state manager (Redux/Zustand); each screen owns its own useState/useEffect data-loading, following one repeated shape: load() → try adminOps.X() → on 404 fall back to demo.ts data + a toast, on other errors show a toast and set an empty/last-good state. System.tsx is the one screen that reads Postgres directly via supabase-js (app_config, RLS-readable by any signed-in session) instead of going through admin-ops, purely to reflect real maintenance state on load.

3. Data-flow diagram(s)

flowchart LR
  subgraph BROWSER["Admin browser (owner only)"]
    LS["localStorage<br/>rewind.metrics-url, rewind.metrics-token"]
    SS["sessionStorage<br/>Supabase OTP session (via supabase-js)"]
    UIState["React component state<br/>(no persistence beyond active tab)"]
  end

  subgraph EDGE["Supabase Edge Functions (Deno, service role)"]
    Metrics["metrics<br/>GET ?days=N"]
    Ops["admin-ops<br/>POST {action, ...args}"]
  end

  subgraph DB["Postgres (service role — bypasses RLS)"]
    Tables["profiles, tiers, feature_flags,<br/>coupons, coupon_redemptions, referrals,<br/>promo_config, pricing_config, orgs,<br/>app_config, exams, ..."]
    RPCs["RPCs: user_delete, tier_create/set/delete,<br/>account_lookup, admin_data_reset,<br/>feature_set/reset, cohort_* "]
  end

  LS -->|"Authorization: Bearer OWNER_METRICS_TOKEN"| Metrics
  LS -->|"Authorization: Bearer OWNER_METRICS_TOKEN"| Ops
  SS -->|"x-superadmin-jwt: <access_token> (2nd factor)"| Ops
  UIState -->|"JSON body {action, user_id, tier, ...}"| Ops
  Metrics -->|"JSON: {overview, addin, feedback, bugs,<br/>revenue, growth}"| UIState
  Ops -->|"JSON: {ok} / {users:[...]} / {tiers:[...]} / {error}"| UIState
  Metrics -.->|"read-only SELECT / views"| Tables
  Ops --> RPCs
  Ops -->|"direct table read/write<br/>(app_config, coupons, promo_config, ...)"| Tables
  RPCs --> Tables

  UIState -.->|"direct: supabase.from('app_config').select(...)"| Tables

The admin app never receives or stores .rewind file bytes, session events, or spreadsheet content — its payloads are exclusively administrative JSON (tier keys, feature states, user rows, coupon config, pricing numbers). Nothing here handles the timeline.json/annotations.json/meta.json components of the .rewind format; that lives entirely in packages/format and the add-in/viewer.

4. Process / sequence diagrams

4.1 Two-factor sign-in (owner token + email OTP)

sequenceDiagram
  participant Owner
  participant GateUI as "Gate.tsx"
  participant Api as "api.ts (store/verifyToken)"
  participant Metrics as "metrics fn"
  participant SupaJS as "supabase.ts (supabase-js)"
  participant Auth as "Supabase Auth"
  participant Ops as "admin-ops fn"

  Owner->>GateUI: enter metrics URL + owner token
  GateUI->>Api: store.url = url, store.token = token
  GateUI->>Api: verifyToken()
  Api->>Metrics: GET ?days=1 (Authorization: Bearer token)
  alt token rejected (401)
    Metrics-->>Api: 401
    Api-->>GateUI: false
    GateUI-->>Owner: "Token rejected." (stays on step 1)
  else network/other error
    Metrics-->>Api: error (non-401)
    Api-->>GateUI: true (treated as "maybe fine")
  else ok
    Metrics-->>Api: 200 JSON
    Api-->>GateUI: true
  end
  GateUI->>GateUI: setStep('otp')
  GateUI->>SupaJS: supabase.auth.signInWithOtp({ email: SUPERADMIN_EMAIL })
  SupaJS->>Auth: send OTP email
  Owner->>GateUI: enter 6-digit code
  GateUI->>SupaJS: supabase.auth.verifyOtp({ email, token: code, type:'email' })
  SupaJS->>Auth: verify
  alt bad code
    Auth-->>GateUI: error
    GateUI-->>Owner: "Code rejected"
  else ok
    Auth-->>SupaJS: session (sessionStorage)
    GateUI->>App: onAuthed()
  end
  Note over Api,Ops: Every later admin-ops call carries BOTH the owner token AND<br/>x-superadmin-jwt = current session access_token (superadminJwt()).<br/>admin-ops independently re-validates the JWT against Supabase Auth<br/>and rejects any email other than SUPERADMIN_EMAIL — the client UI<br/>gate is convenience only, not the real security boundary.

4.2 Feature entitlement write (global / tier / account exception cascade)

sequenceDiagram
  participant Admin as "Superadmin"
  participant FeaturesUI as "Features.tsx"
  participant Ops as "admin-ops fn"
  participant RPC as "feature_set() / feature_reset() (Postgres)"
  participant Flags as "feature_flags table"

  Admin->>FeaturesUI: search email in "Account exception" card
  FeaturesUI->>Ops: POST {action:'account_lookup', email}
  Ops->>RPC: account_lookup(p_email)
  RPC-->>Ops: {user_id, email, tier, org_id, member_count, scope, subject}
  Ops-->>FeaturesUI: {account}
  FeaturesUI->>Ops: POST {action:'features_get', scope, subject}
  Ops-->>FeaturesUI: {features:[{feature, global, own}]}
  Admin->>FeaturesUI: toggle a feature row to "Disabled"
  alt global state for that feature is already 'disabled'
    FeaturesUI-->>Admin: control rendered disabled client-side (lockedByGlobal)
  else
    FeaturesUI->>Ops: POST {action:'feature_set', scope, subject, feature, state}
    Ops->>RPC: feature_set(...) [service role, no RLS]
    RPC->>Flags: upsert override row at (scope, subject, feature)
    RPC-->>Ops: ok / error (bad_scope, bad_state, subject_required, bad_subject)
    alt success
      Ops-->>FeaturesUI: {ok:true}
      FeaturesUI-->>Admin: toast "ok", optimistic row update
    else error
      Ops-->>FeaturesUI: {error} (400/500)
      FeaturesUI-->>Admin: toast "err" via friendly(e)
    end
  end
  Note over RPC,Flags: Resolution order enforced server-side too: account > tier > global,<br/>EXCEPT a global 'disabled' is a hard-kill floor no narrower scope can lift.

4.3 User tier change / revoke / hard delete

sequenceDiagram
  participant Admin as "Superadmin"
  participant UsersUI as "Users.tsx"
  participant Ops as "admin-ops fn"
  participant DB as "Postgres (service role)"

  Admin->>UsersUI: click "Change tier" -> pick tier + optional free months
  UsersUI->>Ops: POST {action:'user_set_tier', user_id, tier, months?}
  Ops->>DB: UPDATE profiles SET tier=..., tier_expires_at = months ? now()+months : null
  DB-->>Ops: ok
  Ops-->>UsersUI: {ok:true}
  UsersUI-->>Admin: toast + optimistic row update

  Admin->>UsersUI: click "Revoke"
  UsersUI->>Ops: POST {action:'user_set_status', user_id, status:'revoked'}
  Ops->>DB: UPDATE profiles SET status='revoked'
  Ops-->>UsersUI: {ok:true}

  Admin->>UsersUI: click "Delete" -> type exact email to confirm
  UsersUI->>Ops: POST {action:'user_delete', user_id, email}
  Ops->>DB: rpc user_delete(p_user, p_email)
  alt email mismatch or user owns an org
    DB-->>Ops: error (user_owns_org / mismatch)
    Ops-->>UsersUI: {error} (400)
    UsersUI-->>Admin: toast "This account owns an org - reassign or delete that org first."
  else ok
    DB-->>Ops: {deleted:true, email}
    Ops-->>UsersUI: {deleted:true, email}
    UsersUI-->>Admin: row removed from table, toast "ok"
  end
  Note over UsersUI: Bulk actions (multi-select) run this same setTier/setStatus call<br/>once per selected user, sequentially, client-side (no batch RPC) —<br/>see runBulk() in Users.tsx.

4.4 Maintenance-mode kill switch

sequenceDiagram
  participant Admin as "Superadmin"
  participant SystemUI as "System.tsx"
  participant SupaJS as "supabase-js (session-authed read)"
  participant Ops as "admin-ops fn"
  participant Cfg as "app_config (id=1)"

  SystemUI->>SupaJS: on mount: select enabled,message from app_config where id=1
  SupaJS->>Cfg: SELECT (RLS: any signed-in session may read)
  Cfg-->>SystemUI: {enabled, message}
  SystemUI->>SystemUI: on = !enabled  (enabled=true means the app is UP)

  Admin->>SystemUI: flip the Maintenance Mode toggle ON
  SystemUI->>Ops: POST {action:'maintenance_set', enabled:false, message}
  Ops->>Cfg: UPDATE app_config SET enabled=false, message=... WHERE id=1
  Ops-->>SystemUI: {ok:true}
  SystemUI-->>Admin: banner "Maintenance mode is live" + toast
  Note over Cfg: Only the Excel add-in and the Google Sheets add-on read<br/>app_config.enabled to gate/block usage and show the message — the viewer<br/>and team console do not check it (verified: no app_config reference in either app).

4.5 Danger-zone pre-launch data reset

sequenceDiagram
  participant Admin as "Superadmin"
  participant SystemUI as "System.tsx (DangerZone)"
  participant Browser as "window.confirm"
  participant Ops as "admin-ops fn"
  participant RPC as "admin_data_reset() (Postgres)"

  Admin->>SystemUI: type exact phrase "RESET DATA"
  SystemUI->>SystemUI: armed = (phrase === 'RESET DATA')
  Admin->>SystemUI: click "Reset all data"
  SystemUI->>Browser: confirm("FINAL CONFIRM: permanently wipe ... Proceed?")
  alt cancelled
    Browser-->>SystemUI: false
    SystemUI-->>Admin: no-op
  else confirmed
    SystemUI->>Ops: POST {action:'data_reset', confirm:'RESET DATA'}
    Ops->>Ops: re-check body.confirm === 'RESET DATA' (guard #3, server-side)
    Ops->>RPC: admin_data_reset(p_confirm:'RESET DATA')
    RPC->>RPC: wipes users + telemetry, KEEPS tiers/flags/pricing/promo/app_config
    RPC-->>Ops: {ok, kept:{tiers,global_flags,...}, cleared:{...}}
    Ops-->>SystemUI: {ok, kept, cleared}
    SystemUI-->>Admin: "Done. ... Sign in again to recreate your superadmin account."
  end

5. Interconnections

flowchart LR
  Admin["apps/admin<br/>(admin.excelrewind.com)"]
  Metrics["Edge fn: metrics"]
  Ops["Edge fn: admin-ops"]
  Auth["Supabase Auth<br/>(email OTP)"]
  PG["Supabase Postgres<br/>(service role, RLS bypassed by admin-ops;<br/>RLS-read for direct app_config read)"]
  CFPages["Cloudflare Pages<br/>(static hosting)"]

  Admin -->|"HTTPS GET, Bearer OWNER_METRICS_TOKEN"| Metrics
  Admin -->|"HTTPS POST, Bearer OWNER_METRICS_TOKEN + x-superadmin-jwt"| Ops
  Admin -->|"supabase-js: signInWithOtp / verifyOtp / getSession"| Auth
  Admin -->|"supabase-js: select on app_config (session-RLS)"| PG
  Metrics --> PG
  Ops --> PG
  Ops -->|"validates JWT signature/expiry + email"| Auth
  CFPages -->|"serves static build"| Admin

6. Key functions & files

7. Invariants & gotchas

Other invariants and platform notes:

• Tiers table is canonical. Per CLAUDE.md: public.tiers is the single source of truth for the tier ladder; nothing else in the codebase should hardcode a tier list. useTierOptions.ts exists specifically so every <select> in the admin app (and elsewhere) reads live from tier_list() instead of a literal array.
• Tiers retire, they don't vanish. Setting a tier active=false hides it from new customers while existing members keep working; deletion is only permitted once user_count === 0 (checked both client- and server-side).
• Hard delete is blocked for org owners. user_delete refuses to remove an account that owns an org — the operator must reassign the org first. The delete modal also requires typing the exact email as a last-chance confirm.
• Bulk user actions are a client-side sequential loop (runBulk() in Users.tsx), not a batch RPC — explicitly marked ponytail: as acceptable for a rare, small-scale admin operation. Partial failure is possible and surfaced as "N succeeded, M failed."
• Referral stats never expose referee identities — referral_stats returns referrer emails and aggregate counts only, enforced in the server function's own comments and matching the product's PII-minimization posture described in CLAUDE.md (paid-tier/session data must never be casually exposed).
• ?demo=1 is intentionally left open (no server flag gates it) because it touches zero real data — every screen swaps in demo.ts fixtures and every adminOps call is skipped (if (!isDemo) await adminOps...). It is also the fallback UI whenever admin-ops 404s, so "looks like it worked" in the console does not always mean a real write happened — check for the DemoFallbackBanner.
• No Office.js / Apps Script surface. Unlike the add-in and add-on, this module has zero host-platform integration — it is a plain browser SPA with no spreadsheet access, consistent with the product rule that clients "send only session events + audio to the API — never full spreadsheet content" (this module doesn't touch spreadsheet content at all).
• CSP is intentionally narrower than the marketing site's. public/_headers restricts connect-src to 'self' plus the exact Supabase project origin — no analytics, no third-party script origins — and the whole app is X-Robots-Tag: noindex so it never appears in search results, since a leaked URL is the console's first line of defense (the gate does not even prefill the metrics endpoint).
• Session storage, not local storage, for the OTP session. supabase.ts deliberately uses window.sessionStorage so a brand-new browser session (new tab/window after closing) always re-requires the OTP step even if the owner token in localStorage is still valid.