Site Incident Reporter

pwa/
├── app/
│   ├── report/[locationId]/       # Public reporter form — no auth
│   ├── (admin)/
│   │   ├── dashboard/             # Incident list + status management
│   │   ├── incidents/[id]/        # Incident detail + AI panel
│   │   ├── locations/             # Location management + QR generation
│   │   └── reporting/             # Management trend charts
│   └── api/
│       ├── incidents/route.ts     # POST — accepts submissions
│       └── locations/[id]/route.ts
├── lib/
│   ├── supabase.ts                # Client (anon key, RLS enforced)
│   └── supabase-admin.ts          # Server-only (service role)
├── components/
│   ├── reporter/                  # SpeechInput, SeverityPicker, SubmitForm
│   ├── dashboard/                 # IncidentList, IncidentDetail, AIPanel
│   └── reporting/                 # Charts, KPI tiles
└── supabase/
    ├── functions/
    │   ├── analyse-incident/      # Edge Function — AI + notification trigger
    └── migrations/
        └── 001_initial_schema.sql

Reporter submits form
  ↓
POST /api/incidents
  ↓
INSERT to incidents table  →  200 OK returned to reporter immediately
  ↓ (async, does not block reporter)
Supabase DB Webhook fires → Edge Function: analyse-incident
  ↓
Call OpenAI GPT-4o (structured output mode)
  ↓
UPDATE incidents SET ai_result = {...}
  ↓
IF escalate = true
  → Email via Resend
  → SMS via Twilio (Emergency only)
  → Realtime push → Admin dashboard toast

The "call 000" line is non-optional on Emergency severity. Duty of care consideration.

-- Locations registered in the system
CREATE TABLE locations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  address     TEXT,
  slug        TEXT UNIQUE,           -- optional friendly short code
  qr_url      TEXT,                  -- generated QR image URL
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- Submitted incidents
CREATE TABLE incidents (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  location_id   UUID REFERENCES locations(id) NOT NULL,
  severity      TEXT CHECK (severity IN ('low','medium','high')) NOT NULL,
  notes         TEXT NOT NULL,
  ai_result     JSONB,               -- populated async after submission
  submitted_at  TIMESTAMPTZ DEFAULT now(),
  notified      BOOLEAN DEFAULT false,
  reference     TEXT UNIQUE          -- 6-char alphanumeric e.g. "WH3-4F2"
);

-- Admin users (Supabase Auth handles passwords + sessions)
CREATE TABLE admin_profiles (
  id            UUID PRIMARY KEY REFERENCES auth.users(id),
  full_name     TEXT,
  email         TEXT,
  role          TEXT DEFAULT 'admin', -- future: 'superadmin', 'viewer'
  notify_sms    BOOLEAN DEFAULT false,
  phone         TEXT,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- Notification audit trail
CREATE TABLE notification_log (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id   UUID REFERENCES incidents(id),
  channel       TEXT,                -- 'email' | 'sms' | 'push'
  sent_at       TIMESTAMPTZ DEFAULT now(),
  success       BOOLEAN,
  error_message TEXT
);

-- Retry queue for failed AI analysis
CREATE TABLE failed_ai_jobs (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id   UUID REFERENCES incidents(id),
  attempts      INT DEFAULT 0,
  last_attempt  TIMESTAMPTZ,
  error         TEXT
);

RLS enforced by Supabase — no custom auth middleware needed on the reporter path. Reporter submits without logging in.