Municipal Road Projects Map

A mid-sized Ontario municipality runs dozens of road resurfacing projects each construction season, and before this tool the status of each one lived in spreadsheets and email threads. Residents phoned in to ask why their road was torn up, staff answered the same questions repeatedly, and there was no single source of truth. The brief was to put the whole season on one public map that anyone could open without a login, and — just as importantly — to let the project managers keep it current without a developer in the loop.

The public side is a Leaflet map that draws each project both as a coloured line along the road segment and as a dot marker, coloured by status (Not Started / In Progress / On Hold / Completed). A combobox search covers roads and individual driveway addresses in one list with full keyboard navigation, status filter pills narrow the view with live counts, and clicking anything opens a resizable detail panel. The interesting modelling problem was progress: a road is not simply "60% done" — it moves through a sequence of work activities, and surface-treatment roads follow a completely different sequence (pulverizing, then gravel/grade/compact, then surface treatment) than standard HMA overlay roads. The schema splits that into a road_work_activities child table keyed by activity type, and the detail panel renders one colour-coded progress card per activity with its own percentage and scheduled start/end dates. A third level of granularity tracks individual driveways on each road — around 470 of them, bulk-imported from the GIS department's export with each driveway's parent road resolved from its UID prefix — each carrying an address, surface material, status and completion date.

Architecturally it is deliberately plain: no framework, no build step, no package.json. Three static files (a public map, a self-contained admin page, and a stylesheet) talk directly to Supabase's PostgREST API, with Row Level Security doing the authorization — anonymous reads for the public map, authenticated writes for the admin. Sign-ups are disabled in Supabase Auth so admin accounts exist only by invitation, which means adding or revoking an editor is a click in a dashboard rather than a code change. The admin page offers two editing speeds: debounced inline auto-save on the status table for quick daily updates, and a full slide-in CRUD panel for roads, their activities and their driveways. The deployment is a Bitbucket-to-AWS-Amplify pipeline whose build step substitutes Supabase credentials into placeholder tokens in the source, so the repository is designed to carry no keys. The map also degrades gracefully: if Supabase is unreachable it falls back to a bundled GeoJSON geometry file and a CSV status snapshot so the public map still renders.

A later fix in the repo is a good illustration of the failure mode this kind of stack invites: when three new surface-treatment activity types were added to the admin UI, the Postgres CHECK constraint on the table was never migrated to match, so saves failed silently behind a delete-then-reinsert routine that only console-warned on error. The fix moved to an upsert and surfaced the failure instead of reporting success.