Offline-First Waste Audit Field Platform

The brief

A no-code v1 had been built but never deployed. It forced single-location-per-stream, photo-per-bin, no multi-floor support, and free-text composition entry — none of which matched how a real waste audit actually runs. The consultancy needed a native rebuild that fit the true shape of the work — multi-floor, multi-location, multi-stream — and survived cellular dead zones in basement loading docks and lift cores.

They asked us to build it as a custom app for their field-operations team, production-ready from day one, so it could go straight from kick-off into daily field use.

What we built — for the field assessor

A mobile-first, offline-tolerant flow shaped around how an assessor actually walks a building.

• Multi-level tenant hierarchy with on-the-fly location adds during a visit. Level labels deduplicate case-insensitively and trim-normalised, so subtle spelling variants of the same floor collapse to one row instead of fragmenting the data.
• Four priority streams visible by default with a "+ More" reveal for the rest of the catalogue. Assessors hit the common case in one tap and reach the long tail when they need it.
• Bin records per (location, stream) with size, count, emptying cycle, and fullness percentage.
• Volume-weighted Combined Fullness % rolled up per stream across every location in the assessment — the figure the report actually needs, computed from the data the assessor already entered.
• Composition sliders constrained to a 99–101% sum, with a live red / yellow / green indicator and an Auto-balance largest slider affordance. Submit is blocked outside the band; the constraint is enforced again at the database layer as a deferrable CHECK so multi-row inserts during offline drain settle correctly.
• Batch photo upload per stream (not per bin). Multi-select from the camera roll, reorder, queued via the offline sync layer, uploads never block the rest of the workflow.
• In-browser voice notes via the MediaRecorder API, transcribed asynchronously by Whisper on a Supabase Edge Function. A voice note can sign off with transcript_status = 'pending'; the report renders "transcript pending…" and reconciles automatically once the transcript completes.
• Standard-recommendation chips plus a single free-text custom recommendation per assessment.
• A "Ready for sign-off" state the assessor sets but cannot self-approve.

What we built — for the admin

A separate, role-gated surface for the people who own the data and the output.

• Full CRUD on portfolios, sites, tenants, and tenant levels, with ON DELETE RESTRICT on the FK from captured locations so a referenced level cannot be orphaned.
• Reference-data CRUD across streams, bin sizes, locations, materials (per-stream × business-type filtering), recommendations, and emptying cycles. Curated reference tables — never free-text picklists — so the captured data has no terminology drift and is clean enough for downstream BI export later.
• User invite, role change, and deactivation.
• Sign-off button that immutably snapshots the assessment and issues a tokenized public report URL in one action.
• Reopen flow that creates a new snapshot on re-sign and revokes the prior link — the previous tokenised URL returns 410 Gone, the old snapshot stays for audit, and the new snapshot gets a fresh URL.
• Server-rendered web report plus on-demand PDF download.

How we built it — production stack from day one

• Next.js 14 (App Router) + TypeScript. Server components by default; role-gated route groups for the assessor and admin surfaces.
• Supabase for Postgres, Auth, Storage, and Edge Functions. RLS on every table. Role is read via a JWT custom_access_token_hook with public.is_admin() and public.is_field_assessor() helpers, not raw JWT inspection at every query.
• Offline-first PWA via @serwist/next for the service worker and idb for the IndexedDB queue. Mutations queue on network failure, drain on the browser online event, with idempotency via per-row client_id columns plus partial unique indexes, FK ordering preserved across queued items via a drain-scoped client-id → server-id translation map, and a cross-tab drain mutex via navigator.locks so two open tabs cannot replay the same queue twice.
• Whisper transcription on a Supabase Edge Function — async by design, never blocks sign-off, admin-retryable on failure.
• PDF rendering on a Vercel Node serverless function using @sparticuz/chromium + puppeteer-core. Node runtime rather than Edge because Chromium cannot load on Edge.
• Inventory pattern — three source-of-truth files (screens, actions, database access) maintained alongside the code, with a pre-commit validator that catches drift between an added route or table and the inventory entries.
• Tokenized public report URL with a random 32-byte URL-safe token, signed-URL photo delivery, and one active link per snapshot.

The five things that decided whether it would work in the field

1. The composition-sum invariant enforced at the database layer as a deferrable CHECK constraint, so multi-row inserts during offline drain settle correctly without rejecting the whole batch.
2. Curated reference tables across streams, bin sizes, locations, materials, recommendations, and emptying cycles. Free-text picklists would have made downstream BI export a recurring data-cleanup job; curated tables make it a one-shot mapping.
3. The async-Whisper rule. The transcription pipeline is decoupled from sign-off entirely. The report renders the placeholder until the transcript completes and reconciles itself. The assessor and the admin never wait on a model.
4. Tenant-level upsert dedupe with case-insensitive, trim-normalised matching and ON DELETE RESTRICT on the captured-location FK. Orphaning a referenced floor label is structurally impossible.
5. Immutable report_snapshots. RLS allows INSERT and SELECT but no UPDATE. Every reopen issues a new row, every old row stays for audit, and the trail is intact by construction rather than by discipline.

The result

A single field-tested platform replaced a no-code build that had stalled before deployment. Field assessors can now complete a full multi-tenant audit on a phone in a basement with no signal. Sign-off, immutable snapshot, tokenized report URL, and PDF download are one button each for the admin. Report layout is consistent across audits and ready to feed downstream reporting without a per-audit clean-up pass.