Skip to content

Backend (Cloudflare Worker + Supabase)

← Back to index

The backend is thin — a Cloudflare Worker handles API routing, auth, and KV/Supabase calls. Supabase holds user data. Stripe handles payments.

Worker layout

worker/
├── wrangler.toml              # KV bindings, env vars, deploy config
├── src/
│   ├── index.ts               # Route table + CORS + health check
│   ├── types.ts               # Env bindings, KVCellData, API types
│   ├── middleware/
│   │   ├── auth.ts            # Supabase JWT verification
│   │   └── rateLimit.ts       # KV-based rate limit counters
│   ├── utils/
│   │   └── h3.ts              # lat/lng → H3 cell ID, canton validation
│   └── routes/
│       ├── teaser.ts          # GET /api/teaser (public)
│       ├── geocode.ts         # POST /api/geocode (public, Turnstile)
│       ├── unlock.ts          # POST /api/report/unlock + GET /api/reports/*
│       ├── tokens.ts          # GET /api/tokens
│       ├── checkout.ts        # POST /api/checkout (Stripe session)
│       ├── webhook.ts         # POST /api/webhook/stripe
│       ├── feedback.ts        # POST /api/feedback
│       └── email_capture.ts   # POST /api/email-capture (public)

Routes

Route Method Auth Purpose
/api/health GET — Health check (monitoring)
/api/geocode POST Turnstile Mapbox geocoder (currently dormant — frontend uses Swiss Fed API)
/api/teaser GET — Free teaser data (letter grades, headlines, fun facts)
/api/email-capture POST — Lead magnet email capture
/api/report/unlock POST JWT Spend 1 token + save report snapshot
/api/reports GET JWT List user's saved reports
/api/reports/by-address GET JWT Fetch saved report by lat/lng
/api/reports/:id GET JWT Fetch a specific saved report
/api/tokens GET JWT Current token balance
/api/checkout POST JWT Create Stripe checkout session
/api/webhook/stripe POST Stripe sig Credit tokens on checkout.session.completed
/api/feedback POST JWT Submit "Report a problem"

All responses are JSON with consistent error shape: { error: string, code: string }.

Auth

worker/src/middleware/auth.ts exports requireAuth(request, env):

  1. Extracts Authorization: Bearer <jwt> header
  2. Verifies JWT against Supabase JWKS (cached in Worker memory)
  3. Returns { userId } or a 401 Response

Usage in routes:

const authResult = await requireAuth(request, env);
if (authResult instanceof Response) return authResult;
const { userId } = authResult;
// ... use userId with Supabase REST + service key

Supabase REST calls use the service role key (bypassing RLS). The worker is the only trusted caller.

Rate limiting

worker/src/middleware/rateLimit.ts provides a KV-backed counter:

const rl = await checkRateLimit(env.RATE_LIMIT_KV, ip, RATE_LIMITS.perMinute);
if (!rl.allowed) return rateLimitResponse(rl.retryAfter);

Presets:

Name Window Limit Used by
perMinute 60s 10 req teaser, geocode, email-capture (minute)
perHourUser 1h 30 req (available but not currently wired)
dailyUnique 24h 50 unique cells teaser (anti-scraping)

Feedback uses per-user + per-IP limits: 5/h per user, 10/h per IP (defined locally in routes/feedback.ts).

Email capture uses perMinute + a 20/day per-IP cap.

Fail-open vs fail-closed: - Email capture: fails closed on the minute limit (public + PII) - Email capture: fails open on the daily limit - Feedback: fails open on both limits (authenticated users only)

Rationale: for public endpoints handling PII, a brief outage is better than serving an unmetered endpoint during a KV incident.

KV

Two namespaces bound in wrangler.toml:

Binding ID Purpose
NEIGHBORHOOD_KV 69e40836e3ec453f874e1531d2ed5d54 Cell data (17K entries, ~60MB)
RATE_LIMIT_KV 21feb70a6678420fadbda7a9391b4162 Rate limit counters + unique lookups

NEIGHBORHOOD_KV

  • Key: H3 cell ID string (e.g., 8a1f91ac356ffff)
  • Value: JSON string with all KPIs for that cell
  • Teaser endpoint returns a subset (TEASER_FIELDS in code/export.py)
  • Unlock endpoint returns the full object

The H3 cell ID is never exposed in any API response — it's the key, not a field. worker/src/utils/h3.ts converts lat/lng to H3 server-side using h3-js and validates the canton bounds before any KV lookup.

RATE_LIMIT_KV

  • Short-lived counters with TTL (60s, 1h, 24h depending on limit)
  • Known past issue: we hit daily write limits on the free tier before upgrading to Workers Paid. The handlers now try/catch around KV calls so a failed binding never bricks the public API.

Supabase

Schema

Migrations in supabase/migration_*.sql, numbered, run in order.

Migration Table/change
001 Base tables: profiles, purchases, reports, RLS, handle_new_user trigger
002 Add token_balance column to profiles
003 Add location_key column to reports (H3 cell ID for server-side lookups)
004 unlock_report() RPC (atomic token deduction + report insert)
005 process_purchase() RPC (idempotent Stripe webhook handler)
006 Lock down RPC execute permissions (revoke from public/authenticated)
007 feedback table + strict RLS + REVOKE ALL from anon
008 email_captures table + strict RLS + REVOKE ALL from anon
009 Admin views (feedback inbox, capture pipeline, daily stats)

Tables

profiles

Extends auth.users. Auto-created on signup by the handle_new_user trigger. Stores display name, language preference, token balance.

purchases

Stripe transaction log. One row per payment. credits_remaining tracks how many reports a bundle has left.

reports

Saved report snapshots. Includes report_data JSONB for offline fallback, and location_key TEXT for live re-fetches from KV.

Key insight: when a user views a saved report, the worker prefers live KV data over the JSONB snapshot (see worker/src/routes/unlock.ts handleGetReport). The snapshot is the receipt, KV is the live view. This means score tweaks or new features automatically appear in existing reports.

feedback (migration 007)

"Report a problem" submissions. RLS permits authenticated users to INSERT their own rows; there's no SELECT policy, so the dashboard is the only way to read them. Per-user + per-IP rate limited at the worker layer.

email_captures (migration 008)

Anonymous lead magnet. No user_id. Unique index on (lower(email), round(lat, 4), round(lng, 4)) to dedupe. Service role only — REVOKE ALL FROM anon, authenticated, PUBLIC.

Atomic RPCs

unlock_report(user_id, location_key, address_text, report_data) (migration 004)

BEGIN;
  IF existing report for (user_id, location_key) THEN RETURN existing.id;
  IF token_balance <= 0 THEN RAISE EXCEPTION 'NO_TOKENS';
  UPDATE profiles SET token_balance = token_balance - 1 WHERE id = user_id;
  INSERT INTO reports (...) RETURNING id;
COMMIT;

Transactional — if the insert fails, the token deduction rolls back. The unique constraint on (user_id, location_key) ensures a user can't accidentally double-unlock the same cell and burn 2 tokens. Callable only with service key (migration 006 revokes execute from anon/authenticated).

process_purchase(session_id, user_id, tokens_to_add, amount_chf) (migration 005)

Idempotent Stripe webhook handler. Uses session_id as a natural key to prevent double-crediting if Stripe retries the webhook.

Row-Level Security

Every user-facing table has RLS enabled. Policies follow the pattern:

CREATE POLICY <table>_select ON public.<table>
  FOR SELECT USING (auth.uid() = user_id);

The feedback and email_captures tables go further — no SELECT policy at all, plus REVOKE ALL FROM anon, authenticated. Only the service role (worker) can read them.

Admin views (migration 009)

Convenience views for the founder to query from the Supabase SQL editor:

View What it shows
admin_feedback_inbox Unresolved feedback + user email + report context
admin_feedback_stats_30d Feedback counts by category (last 30 days)
admin_feedback_daily Daily volume + unique users
admin_email_captures_pipeline Uncontacted leads
admin_email_captures_daily Daily capture volume
admin_email_captures_by_commune Demand by commune
admin_reports_daily Daily unlocks (headline metric)
admin_purchases_30d Revenue by type/status (last 30 days)

All REVOKEd from anon/authenticated. Dashboard access only.

Security notes

H3 cell IDs are server-side only

CLAUDE.md is explicit: never expose cell IDs. This is enforced by: - KV responses omit the key ({ "value": {...} }, no h3_index field inside) - The worker converts lat/lng → H3 internally (worker/src/utils/h3.ts) - Feedback report_id uses the Supabase UUID, not the cell ID

Idempotent writes

Both financial flows (unlock, Stripe webhook) are idempotent: - unlock_report uses (user_id, location_key) as a natural key - process_purchase uses session_id as a natural key

Strict Content-Type + body size

All POST routes that handle PII or user input enforce Content-Type: application/json and cap the body size (8KB for feedback, 2KB for email capture). The worker reads the body as text first, validates size, then JSON.parse — because Content-Length is client-provided and can be spoofed.

PII logging

Feedback and email capture explicitly avoid logging user input in error paths (because Postgres constraint violations echo the offending row, and Workers logs are retained). See worker/src/routes/feedback.ts line ~157 and worker/src/routes/email_capture.ts line ~155 for the redaction patterns.

Stripe

Products

Hardcoded in worker/src/types.ts::STRIPE_PRODUCTS:

Key Tokens Price (CHF) Label
single 1 19.00 1 report
pack3 3 49.00 3 reports
pack10 10 129.00 10 reports

Flow

  1. Frontend calls POST /api/checkout with { product, returnUrl }
  2. Worker creates a Stripe checkout session with the product line items + success/cancel URLs
  3. Worker returns the Stripe-hosted checkout URL
  4. Frontend does window.location.href = url
  5. User pays on Stripe
  6. Stripe calls /api/webhook/stripe with checkout.session.completed
  7. Worker verifies signature, calls process_purchase() RPC
  8. Stripe redirects user back to returnUrl?checkout=success
  9. Teaser page detects the query param, refetches token balance, auto-navigates to report

Webhook security

  • Stripe signature verified via constructEvent from the Stripe library
  • STRIPE_WEBHOOK_SECRET stored as a Worker secret (not in source)
  • Only checkout.session.completed events are processed; others are 200'd silently

Deployment

See deployment.md for the runbook.

Next: Frontend