Farm WarehouseFarm Warehouse

Overview

An internal web + PWA platform that digitizes FarmWarehouse's rural community-engagement lifecycle: from first contact through pre-entry assessment, value-chain survey, leadership/CHC formation, sensitization, and feasibility decision, to hub setup and launch. It replaces spreadsheets, WhatsApp groups, and paper forms with a single source of truth, a management pipeline (kanban) view, and structured, offline-capable data capture for field agents.

My role

Solo full-stack build, working against PRDs supplied by a product manager. Scope: data model and migrations, the hybrid auth/RBAC layer, all six Communities & Hubs sub-features, KoboToolbox integration, offline sync, PDF/CSV export, GPS/maps, PWA setup, and Vercel deployment.

The problem

FarmWarehouse enters rural communities to establish agro-processing hubs, but the whole engagement process, from identification to hub launch, was tracked manually across spreadsheets, WhatsApp, and paper. There was no single source of truth for where each community sat in the pipeline, which assessments were done, who the local governance contacts were, or which hubs were operational. The result was duplicated effort, lost context when staff changed, and no management visibility into pipeline health.

The solution

A Communities & Hubs operations platform that models the engagement lifecycle as a six-stage pipeline with two management approval gates, digitizing every step from community creation to hub launch. Field agents get structured, offline-capable forms; management gets a kanban pipeline and decision gates; and there is a clean handoff from community engagement to hub operations. Auth is deliberately hybrid: Supabase Auth (GoTrue) as the credential/session engine, with a custom Drizzle/Postgres domain layer adding an account state machine, granular RBAC, lockout, and audit trails.

Key features

• Six-stage engagement pipeline (initial contact → pre-entry assessment → value-chain survey → leadership/CHC → sensitization → feasibility decision → hub) with kanban + table views and two management approval gates.
• Dynamic RBAC: an 18-role taxonomy with code-defined module:action permissions, hierarchical who-can-invite rules, and assignment-based data-scoping that returns 404s outside a user's scope (IDOR protection).
• Offline-first field forms (assessment, leadership visit, sensitization) backed by a localStorage replay queue with server-side idempotency keys for safe reconnection sync.
• KoboToolbox survey integration: link a Kobo project, sync submissions via API (or a deterministic stub), compute 5-dimension readiness scores, refreshed by a cron job.
• Hub lifecycle state machine (setup → launched → operational ↔ suspended → closed) gated on feasibility approval, plus GPS capture (Leaflet pin-drop), evidence photo upload (Supabase Storage), CSV export, and branded PDF community reports.
• Invite-only onboarding with staff-issued temporary credentials and forced password setup, brute-force lockout, in-app notifications, and an append-only audit log.

Architecture & engineering

Next.js 16 App Router (React 19, Tailwind v4) over PostgreSQL with Drizzle ORM. Identity is decoupled from the auth provider: the profiles table owns its own UUID plus a nullable auth_user_id FK, so a user can exist in an "invited" state before any Supabase auth user is provisioned. Permissions are defined in code (a per-module registry) rather than in DB tables. Audit is append-only across audit_log, account_state_events, and immutable pipeline_transitions. An event-driven cache (unstable_cache with per-entity tags, busted on every mutation, with a browser-reload bypass) keeps list/profile pages fast. PWA is via Serwist. 15 Drizzle migrations; RLS is enabled on the base auth tables, with server logic running under the Supabase service role.

The hardest problem

Field agents often work where connectivity is poor, so the assessment, leadership-visit, and sensitization forms had to work offline and sync on reconnect without creating duplicate records. This was handled with a localStorage FIFO queue that replays on the browser online event, guarded by a cross-tab Web Locks lock, distinguishing transient failures (retry) from definitive 4xx ones (surfaced to the user), plus client-generated idempotency keys the server dedupes against a unique column, so a submit replayed after a crash returns the existing row instead of duplicating it.

Results

The full pipeline works end-to-end and is deployed to Vercel, backed by a 59-test Vitest suite covering the permission, invite-hierarchy, lockout, visibility, and scoring rule engines, and a Lighthouse score of 100/100/100/100 (desktop) on the production login page.

Key learnings

Decoupling identity from the auth provider (own UUID + nullable auth FK) up front unlocked the invited-before-auth and staff-issued-credential flows cleanly; retrofitting it later would have been costly. Offline correctness turned out to be an idempotency problem as much as a UI one; the queue only became safe once the server deduped on idempotency keys.