Backend: services

flowchart TB
    subgraph Clients["Client surfaces"]
        Addin["apps/addin-excel
auth.ts"]
        ViewerGS["apps/viewer
google-signin.ts"]
        Team["team.excelrewind.com
(org console)"]
        AdminUI["admin.excelrewind.com
(superadmin console)"]
        Installer["Windows installer (NSIS)
(companion, no session)"]
    end

    subgraph EdgeFns["Supabase Edge Functions (Deno, supabase/functions/*)"]
        StripeWH["stripe-webhook/index.ts
--no-verify-jwt"]
        RzpWH["razorpay-webhook/index.ts
--no-verify-jwt (NOT YET DEPLOYED)"]
        AdminOps["admin-ops/index.ts
--no-verify-jwt, dual-guard"]
        Metrics["metrics/index.ts
--no-verify-jwt, owner token"]
        ExamStorage["exam-storage/index.ts
--no-verify-jwt, self-checked JWT"]
        SendInvite["send-invite/index.ts
default verify-jwt + self-check"]
        VerifyDomain["verify-domain/index.ts
default verify-jwt + self-check"]
        UninstallFb["uninstall-feedback/index.ts
--no-verify-jwt, anonymous (NOT YET DEPLOYED)"]
    end

    subgraph AuthLayer["Supabase Auth (GoTrue) + handoff"]
        GoTrue["/auth/v1 — email OTP,
Google/Apple OAuth (PKCE)"]
        Handoff["public.auth_handoff table
stash_handoff() / claim_handoff()"]
    end

    subgraph DB["Postgres (RLS on, service role bypasses)"]
        Profiles[("profiles
tier, status, stripe/razorpay_customer_id")]
        AppConfig[("app_config
global maintenance kill-switch")]
        Tiers[("tiers, pricing_config,
promo_config, coupons, referrals")]
        Orgs[("orgs, exams,
exam_submissions")]
        Feedback[("uninstall_feedback")]
        Events[("usage_events, viewer_events,
bug_reports")]
    end

    subgraph Storage["Supabase Storage"]
        Bucket[("exam-submissions bucket
{org}/{exam}/{uid}-{ts}.rewind")]
    end

    subgraph ExternalBilling["External billing providers"]
        Stripe["Stripe
Payment Links + webhooks"]
        Razorpay["Razorpay
Payment Links + webhooks"]
    end

    Resend["Resend
transactional email API"]

    Addin -- "signInWithOtp / verifyOtp
signInWithOAuth handoff poll" --> GoTrue
    Addin -- "rpc claim_handoff" --> Handoff
    Addin -- "checkAccess(): direct select,
no Edge Function involved" --> AppConfig
    Addin -- "checkAccess() + rpc register_device/
touch_device/effective_features" --> Profiles
    ViewerGS -- "exchangeCodeForSession
rpc stash_handoff" --> GoTrue
    ViewerGS --> Handoff
    Addin -- "POST action:upload_url/review_url
Authorization: Bearer <jwt>" --> ExamStorage
    Team -- "POST send-invite" --> SendInvite
    Team -- "POST verify-domain" --> VerifyDomain
    AdminUI -- "POST action:*
Bearer OWNER_TOKEN + x-superadmin-jwt" --> AdminOps
    AdminUI -- "GET ?days=N
Bearer OWNER_TOKEN" --> Metrics
    Installer -- "POST install/uninstall event
(anonymous, best-effort)" --> UninstallFb

    Stripe -- "checkout.session.completed,
customer.subscription.deleted,
invoice.payment_failed" --> StripeWH
    Razorpay -- "payment_link.paid,
subscription.cancelled, payment.failed" --> RzpWH

    StripeWH -- "update tier by email/customer_id" --> Profiles
    RzpWH -- "update tier by email/customer_id" --> Profiles
    AdminOps -- "update / rpc tier_*, feature_*,
cohort_*, coupon_*, user_*" --> Profiles
    AdminOps -- "maintenance_set" --> AppConfig
    AdminOps --> Tiers
    AdminOps --> Orgs
    Metrics -- "rpc metrics_* + direct reads" --> Profiles
    Metrics --> Events
    Metrics --> Feedback
    Metrics --> Tiers
    ExamStorage -- "signed PUT/GET via storage adapter" --> Bucket
    ExamStorage -- "reads exams/exam_submissions" --> Orgs
    ExamStorage -- "isOrgMember/orgRole via profiles.org_id" --> Profiles
    SendInvite -- "getUser(jwt) self-check" --> GoTrue
    SendInvite -- "email HTML" --> Resend
    VerifyDomain -- "getUser(jwt) self-check" --> GoTrue
    VerifyDomain -- "Deno.resolveDns TXT lookup" --> DNS["Public DNS
_excelrewind.<domain>"]
    VerifyDomain -- "update orgs.domain_verified" --> Orgs
    UninstallFb -- "insert (rate-limited by trigger)" --> Feedback

flowchart LR
    Buyer["Payer's browser"] -- "checkout form" --> PL["Stripe/Razorpay
Payment Link (hosted, no code of ours)"]
    PL -- "webhook POST, JSON body
+ stripe-signature / x-razorpay-signature header" --> WH["stripe-webhook or
razorpay-webhook (Deno)"]
    WH -- "constructEventAsync (Stripe)
or HMAC-SHA256 verify (Razorpay)" --> WH
    WH -- "PRICE_TO_TIER / RZP_LINK_TO_TIER
env JSON: price/link id -> tier string" --> WH
    WH -- "UPDATE profiles SET tier=..,
stripe_customer_id=.. WHERE email ILIKE (escaped)" --> ProfilesDB[("profiles")]
    ProfilesDB -- "SELECT tier
on every checkAccess()" --> Addin2["Excel add-in gate
(apps/addin-excel/src/auth.ts)"]
    WH -. "5xx on handler error" .-> PL
    PL -. "auto-retries on 5xx" .-> WH

flowchart LR
    Owner["Owner (browser,
admin.excelrewind.com)"] -- "email OTP sign-in" --> GoTrue2["Supabase Auth"]
    GoTrue2 -- "session JWT for SUPERADMIN_EMAIL" --> Owner
    Owner -- "POST { action, ...args }
Authorization: Bearer OWNER_METRICS_TOKEN
x-superadmin-jwt: <session JWT>" --> AdminOps2["admin-ops"]
    AdminOps2 -- "timingSafeEqual(token)" --> AdminOps2
    AdminOps2 -- "supabase.auth.getUser(jwt)
email must == SUPERADMIN_EMAIL" --> GoTrue2
    AdminOps2 -- "service-role UPDATE/INSERT
or rpc tier_*/feature_*/cohort_*/user_delete" --> PG[("Postgres
profiles, tiers, orgs,
coupons, promo_config,
pricing_config")]
    Owner -- "GET /metrics?days=N
Authorization: Bearer OWNER_METRICS_TOKEN" --> Metrics2["metrics"]
    Metrics2 -- "parallel rpc metrics_*
+ direct table reads" --> PG
    PG -- "JSON dashboard contract
(KPIs, series, funnels, revenue estimate)" --> Metrics2
    Metrics2 -- "200 JSON" --> Owner

sequenceDiagram
    participant User
    participant Pane as "Add-in pane (auth.ts)"
    participant GoTrue as "Supabase Auth"
    participant DB as "Postgres"

    User->>Pane: enters email
    Pane->>GoTrue: signInWithOtp({ email, options.data.ref })
    Note right of Pane: pendingRef() attaches a captured
referral code into raw_user_meta_data
    GoTrue-->>User: emails 6-digit code
    User->>Pane: enters code
    Pane->>GoTrue: verifyOtp({ email, token, type:'email' })
    GoTrue-->>Pane: session (access_token, refresh_token)
    alt on new user insert
        GoTrue->>DB: trigger on_auth_user_created -> handle_new_user()
        DB->>DB: insert profiles(id, email)
    end
    Pane->>DB: rpc register_device(device_id, label)
    Note right of Pane: evicts oldest device beyond 2 (feature-detected,
missing RPC = limit inactive, never blocks sign-in)
    Pane->>DB: checkAccess(): select app_config + profiles
    DB-->>Pane: { enabled, message } + { status, tier, telemetry }
    Pane->>DB: rpc touch_device(device_id)
    DB-->>Pane: true (still registered) | false (evicted elsewhere)
    alt evicted or licence blocked
        Pane->>Pane: signOut() + clear CACHE_KEY, show sign-in
    else allowed
        Pane->>Pane: cache { at, email } in localStorage (offline grace)
    end

sequenceDiagram
    participant Pane as "Add-in pane (WebView2)"
    participant Sys as "System browser"
    participant Google
    participant GS as "viewer/google-signin.html"
    participant GoTrue as "Supabase Auth"
    participant DB as "public.auth_handoff"

    Pane->>Pane: makeNonce() (24 random bytes, hex)
    Pane->>Sys: window.open(viewerOrigin + "/google-signin.html#nonce=..&provider=google")
    Note over Pane,Sys: viewer origin is deliberately absent from the
manifest AppDomains - this forces the OS to open
the SYSTEM browser instead of an in-pane webview
    Sys->>GS: load page, nonce/provider -> sessionStorage
    GS->>GoTrue: signInWithOAuth({ provider:'google', redirectTo: self })
    GoTrue->>Google: PKCE redirect
    Google-->>Sys: consent, redirect back with ?code=
    Sys->>GS: reload with ?code=
    GS->>GoTrue: exchangeCodeForSession(code)
    GoTrue-->>GS: session (access_token, refresh_token)
    GS->>DB: rpc stash_handoff(nonce, access, refresh)
    Note right of DB: security definer, nonce must be >=32 chars,
rows purge after 10 minutes
    GS-->>Sys: "Signed in - return to Excel"

    par pane polls concurrently
        loop every 2.5s, up to 5 min
            Pane->>DB: rpc claim_handoff(nonce)
            DB-->>Pane: empty (not yet) or (deletes row) tokens
        end
    end
    Pane->>GoTrue: setSession({ access_token, refresh_token })
    Pane->>DB: rpc register_device(device_id, label)
    Pane-->>Pane: adopted session, pane now signed in

sequenceDiagram
    participant Payer
    participant Stripe
    participant WH as "stripe-webhook (Deno)"
    participant DB as "profiles"

    Payer->>Stripe: opens Payment Link ?prefilled_email=user@x.com, pays
    Stripe->>WH: POST checkout.session.completed
(stripe-signature header)
    WH->>WH: stripe.webhooks.constructEventAsync(body, sig, secret, cryptoProvider)
    alt bad signature
        WH-->>Stripe: 400 "bad signature"
    else verified
        WH->>Stripe: subscriptions.retrieve(session.subscription)
        Stripe-->>WH: subscription with price id
        WH->>WH: tier = PRICE_TO_TIER[priceId]
        alt no email or unmapped price
            WH-->>Stripe: 200 ok (ignored, logged)
        else
            WH->>DB: UPDATE profiles SET tier, stripe_customer_id
WHERE email ILIKE escaped(email)
            WH-->>Stripe: 200 ok
        end
    end

    Note over Stripe,WH: later — cancellation
    Stripe->>WH: POST customer.subscription.deleted
    WH->>DB: UPDATE profiles SET tier='free' WHERE stripe_customer_id = ..
    alt no row matched (never linked)
        WH->>Stripe: customers.retrieve(customerId)
        Stripe-->>WH: customer.email
        WH->>DB: UPDATE profiles SET tier='free' WHERE email ILIKE ..
    end
    WH-->>Stripe: 200 ok

    Note over Stripe,WH: handler exception anywhere above
    WH-->>Stripe: 500 (Stripe retries automatically)

sequenceDiagram
    participant Student as "Add-in (student, exam mode)"
    participant ES as "exam-storage (Deno)"
    participant DB as "Postgres"
    participant Bucket as "Supabase Storage
exam-submissions bucket"
    participant Owner as "Team console (org owner/admin)"

    Student->>ES: POST { action:'upload_url', exam_id }
Authorization: Bearer <student JWT>
    ES->>DB: auth.getUser(jwt)
    DB-->>ES: uid
    ES->>DB: select exams where id=exam_id
    DB-->>ES: { org_id }
    ES->>DB: isOrgMember(uid, org_id) via profiles.org_id
    alt not rostered
        ES-->>Student: 403 not_rostered
    else rostered
        ES->>Bucket: createSignedUploadUrl({org}/{exam}/{uid}-{ts}.rewind)
        Bucket-->>ES: signed PUT url
        ES-->>Student: { url, storage_path }
        Student->>Bucket: PUT raw .rewind bytes directly (client never touches storage keys)
        Student->>DB: rpc exam_submit(exam_id, storage_path, duration_s, flags, integrity_hash)
    end

    Note over Owner,DB: later — review
    Owner->>ES: POST { action:'review_url', submission_id }
Authorization: Bearer <owner JWT>
    ES->>DB: orgRole(uid, exam.org_id)
    alt not owner/admin
        ES-->>Owner: 403 not_owner
    else authorized
        ES->>Bucket: createSignedUrl(storage_path, 300s)
        Bucket-->>ES: signed GET url
        ES-->>Owner: { url, student_email, exam_name }
        Owner->>Bucket: GET .rewind, replay in in-Excel/viewer replay
    end

flowchart LR
    addin["addin-excel"] -->|"email OTP, OAuth, RPCs, checkAccess()"| supabaseAuth["Supabase Auth (GoTrue)"]
    addin -->|"upload_url / review_url"| examstorage["exam-storage"]
    viewer["viewer (google-signin.html)"] -->|"exchangeCodeForSession, stash_handoff"| supabaseAuth
    team["team console"] -->|"send-invite, verify-domain"| edgefns["Edge Functions"]
    admin["admin console"] -->|"admin-ops, metrics"| edgefns
    installer["Windows installer (NSIS)"] -->|"uninstall-feedback (anonymous, not yet deployed)"| edgefns
    edgefns -->|"service-role reads/writes"| pg["Postgres (backend-data module)"]
    stripewh["stripe-webhook"] -->|"tier flip"| pg
    Stripe -->|"webhook events, signed"| stripewh
    Razorpay -->|"webhook events, HMAC-signed"| razorpaywh["razorpay-webhook (not deployed)"]
    razorpaywh --> pg
    sendinvite["send-invite"] -->|"transactional email"| Resend
    verifydomain["verify-domain"] -->|"DNS TXT lookup"| DNS2["Public DNS"]