Compliance Hub

UK facilities managers carry a stack of statutory obligations — LOLER, PUWER, RIDDOR, RAMS, Fire Safety, Legionella L8, CDM 2015, EICR, Gas Safety, H&S, Asbestos — and most of them get tracked in a spreadsheet that's missing the actor, the timestamp, the before/after, and the evidence chain. When the HSE walks in and asks "show me that the LOLER thorough examination was scheduled, completed by a competent person, defects were closed, and the equipment wasn't operated in the meantime" — a spreadsheet is a guess. The brief was to replace the spreadsheet with a defensible system of record.

Next.js 15 App Router with React 19 Server Components, all mutations through Zod-validated Server Actions. Supabase Postgres 17 with row-level security on every exposed table. Six architectural principles drive the codebase: 1. RLS is non-negotiable. Default-deny everywhere. Every policy is org+site scoped. Contractors are explicitly walled off to records assigned to them. 2. RAG is first-class data, not a UI badge. Every operational row stores rag_status, rag_auto_status (BEFORE-trigger computed), rag_override_status, rag_override_reason — with a DB CHECK that makes a manual override illegal without a reason. 3. Delegation is a workflow, not a field. Owner, assignee, delegated-by, delegated-to, escalation-user, acceptance, accepted-at, declined-at, decline-reason, escalation-due-at, escalated-at, completed-at, closed-by — real columns, real workflows on both actions and inspections. 4. Audit is automatic. A SECURITY DEFINER trigger writes every insert/update/delete on every operational table into activity_log, stamped with auth.uid() and both previous and new values. 5. Storage is private-first. Three private buckets with path-scoped RLS. Documents accessed via signed URLs only. 6. One time-tracking primitive. A single time_logs table covers clock-on/off against actions and inspections via XOR check constraint.

19 tables across five domains: Identity & structure — organizations · profiles · sites · site_memberships · dutyholder_assignments Operational — assets · compliance_registers · inspections · inspection_findings · actions · incidents · contractors · contractor_site_links · contractor_documents · documents (polymorphic) Time tracking — time_logs with XOR check (action_id XOR inspection_id) + active_time_logs view Collaboration & audit — comments (threaded, with mentions uuid[]) · notifications (per-user inbox) · activity_log (every operational write with previous_values + new_values JSONB) Every operational table carries: organization_id, site_id, title, status, rag_status, rag_auto_status, rag_override_status, rag_override_reason, due_date, owner_user_id, assigned_to_user_id, created_by, created_at, updated_at, archived_at.

calc_rag(due_date, status, dangerous, high_risk, expires_on) — pure SQL, deterministic, attached as a BEFORE trigger to every operational table. Status closed/cancelled/archived → green. Dangerous flag → red (latches). Due/expiry past today → red. Within 30 days → amber. High-risk → amber. Otherwise → green or unknown. Manual override is settable only by managers (enforced in RLS). The DB CHECK constraint guarantees override_reason is non-empty when status is overridden. Effective RAG = coalesce(override, auto). Every change writes to activity_log with previous and new values. A nightly recalc-rag edge function re-saves every open record so a register that becomes due tomorrow flips to amber today — no app-layer drift, no race conditions, no "did we remember to recompute?"

The same primitives exist on both actions and inspections: owner_user_id → who's accountable assigned_to_user_id → who's doing it delegated_by_user_id / delegated_to_user_id / delegated_at / delegation_notes acceptance_status (pending | accepted | declined) / accepted_at / declined_at / decline_reason escalation_user_id / escalation_due_at / escalated_at completed_at / closed_by_user_id A manager creates or delegates work. The assignee gets an in-app notification. They accept, or decline with a DB-validated minimum-length reason. They clock on to start tracking time. They mark complete (actions) or sign off (inspections) with evidence notes. If acceptance stalls, the escalation edge function promotes the work to the configured escalation user. Every state change is in activity_log.

Every site gets a numeric compliance score: score = 100 − 5 × min(overdue_inspections, 10) − 4 × min(overdue_red_actions, 10) − 3 × min(high_risk_open_findings, 10) − 2 × min(expired_contractor_docs, 10) − 4 × min(riddor_review_backlog, 5) The function returns the breakdown (counts + the formula string) alongside the score so the number is never opaque. The site detail page renders both. Weights live in plain SQL — configurable per org in v0.3.

RLS enabled on every exposed table. All policies are org+site scoped — no "authenticated can read all" leaks. Contractors restricted to assigned records via membership role + linked records. service_role never reaches the browser; gated by import "server-only" and confined to Edge Functions plus a single admin client. Supabase Auth for real (password, magic link, password reset). Override requires reason at the DB CHECK level, not just the UI. SECURITY DEFINER minimised — used only for calc_rag, RLS predicates, and the audit trigger. Storage: signed-URL only, path-scoped RLS, path_org() + path_site() predicates. Server-side Zod validation on every form. CSRF via Next's cookie-bound Server Action tokens. Sign-off integrity: once signed off, the UI locks and the outcome is immutable. Security advisor: 0 lints.

Live at compliance-hub.jonnyai.co.uk on a GCP VM (PM2 + nginx + Let's Encrypt). 11 statutory compliance regimes covered. 19 tables. 14 document kinds. Photo evidence on incidents and comments. @mentions with notifications. Calendar with cadence projections forecasting up to 24 occurrences forward. Mobile-first responsive design with off-canvas drawer, native camera input for photo capture, swipe-scrollable tables. PDF export of inspection records for handing to HSE. PWA install. Dark mode. Now being productised as a SaaS: £99/mo Starter, £299/mo Pro, £999/mo + £1,500 setup white-label.