Production Web App

knockabase

A door-to-door canvassing app for an HVAC electrification crew, where a rep speaks a knock between houses and it becomes a schema-validated database record instead of a note typed at 9pm. Live on two hostnames: a marketing site on the apex and the app on app.knockabase.com.

Next.js

TypeScript

Supabase

PostGIS

Mapbox

Claude API

Live Code: Private repo

Territory map with coloured door pins along a Vancouver block, a drawn territory boundary, and a microphone dock

Employer signal

What This Project Shows

This is the project where I designed and built a whole system rather than a feature: the schema and its row-level security, the rule for when two doors are the same door, an LLM extraction pipeline whose output is treated as untrusted, an offline outbox, a hand-written service worker, and the design decisions that make it usable one-handed on a doorstep. 133 commits and 27 migrations over nine days, built for a door-to-door crew, AI-assisted throughout, one day's work ran six agents in parallel, partitioned by file ownership. Reviewing what an agent produced rather than accepting it is how, two days later, a migration a cloud agent had shipped without the credentials to apply it got caught, while production was quietly running the new code against the old schema. The part worth a reviewer's time is not the feature list: it is the two questions I answered wrongly first and then had to answer properly: when are two doors the same door, and what should a system do with a time that carries no timezone.

Problem

What Needed To Be Solved

Canvassing software assumes a person who can type. A rep works outdoors and one-handed for a five-hour shift, and residential streets and basements have no signal, so an app that needs a connection to accept a knock is an app that loses knocks. Underneath that sits a quieter problem that only surfaces once records accumulate: the address a door is filed under is a guess. It arrives from a reverse geocode or a spreadsheet, two neighbouring houses can be handed the same street line, and a spoken "unit 4B" has to find the right door among the ones already recorded at that address, or silently attach what a household said to a household that never said it.

Approach

How I Built The Solution

I made speech the primary input and treated everything downstream of it as untrusted. The phone's own recogniser runs first because it is free and instant, with a cloud transcription fallback for wind and traffic; the transcript is mapped onto a fixed schema by a model, validated against that schema on the server before it can reach the database, and shown back to the rep as a card to confirm. Below that, the guarantees that have to survive a knock arriving late (ordering, tenancy, and which door a knock belongs to) are enforced inside Postgres rather than in application code, because with an offline queue the order things arrive in is not the order they happened in.

Outcome

What It Demonstrates

What shipped is a product rather than a set of experiments: a clustered territory map with one-tap door placement, the voice-to-record pipeline, an offline outbox and territory cache, territories drawn on the map, a door list, a schedule, realtime messaging, per-rep and per-territory analytics, an org roster with email invitations, bulk CSV territory import with geocoding, and a marketing site on the apex with its own first-party analytics. Two things are unfinished and worth saying plainly: the voice path is tested end to end in a browser and has never been run through a full shift on real hardware in wind, and there is no test framework and no CI: verification is six hand-written scripts wired into one npm command, scoped on purpose to the logic that sits behind auth, behind a map, or behind a model and therefore cannot be checked by clicking, including a schedule test that runs a timezone at UTC+05:45 specifically to catch any implementation storing offsets in whole hours. The judgement the project demonstrates is knowing which parts had to be right the first time (the tenancy boundary, the ordering guarantee, and the rule for when two doors are the same door) and which could be learned by shipping and being corrected.

Technical Signals

Postgres row-level security

PostGIS geospatial queries

LLM structured outputs, validated server-side

Offline-first sync

Mapbox GL clustering

Multi-tenant schema design

Implementation Notes

Made address equality stop being an identity. A unique index on (organisation, normalised address) had started life as a spreadsheet dedupe key and been promoted by accident into the answer to "is this the same door", so the rule became address AND place: a candidate merges into an existing building only if it is also within a radius of it. The radius test runs inside the SQL function that creates a building, so it is atomic with the insert, and the same constant and a Haversine distance are mirrored in TypeScript, so the client can tell a rep how far away the pin it merged with actually sits, and so both halves are testable without a database.
Cut the merge radius from 20m to 3m when a human pressed the point on the map, because 20m is wider than a residential lot in this market and a rep tapping the second half of a duplex was being handed the first half, but deliberately not to zero, so a double-tap or a return visit still lands on the door already there. Guessed inputs, reverse geocode and bulk import, keep the wider radius.
Scoped every table by organisation with Postgres row-level security from the first migration (organisation, then team, then rep) and put the boundary in the database rather than in application code, because an offline queue means the app is not in a position to be the boundary. The policies were verified by impersonating a role against the running database inside a rolled-back transaction, rather than by reading them and concluding they looked right.
Modelled buildings and units as separate tables so a townhouse row is one map pin and N knockable doors, and made knocks an append-only log with current state denormalised onto each unit by a trigger that is both timestamp-guarded and field-coalesced, so a late sync cannot overwrite newer state, and a note that never mentioned a field cannot erase it.
Anchored the model's naive timestamps to the door's timezone before writing them. The model emits ISO 8601 with no offset because nothing in a spoken sentence names one, and Postgres reads an offset-less literal in the session timezone, so a 6pm callback was landing in the database seven hours early: on the one field whose whole job is telling a rep when to come back.
Shrank the extraction schema from 24 fields to 15 rather than growing it, because every extra enum is a call the model makes with no evidence and a plausible wrong value corrupts a CRM in a way a blank never does, while a technician re-establishes all of the equipment detail at the assessment anyway. Eleven qualifying fields went, three of them enums and the rest numbers and booleans, replaced by two: a rebate amount and a free-text decision-maker, since "wife decides, works nights" is a plan no enum holds. The normaliser discards retired keys rather than rejecting a record that still carries them, so a schema change cannot throw away a rep's day of offline-queued knocks.
Rejected the SDK's schema helper after finding it strips enum constraints out of the generated JSON Schema and appends them to the field description as prose, which would silently un-constrain the disposition field and make every mis-parsed knock read as "nobody has ever been to this door". The route instead forces a single tool call with parallel tool use disabled, validates the result against the schema server-side, and fails closed with a 502 on mismatch, on the stated grounds that forcing the tool guides the shape but does not guarantee it.
Built the map as a clustered Mapbox GL source: one tap places a pin, the address autofills from a reverse geocode, and a pin can be dragged to a new spot above a minimum zoom. Territories are polygons a manager draws by tapping the map, and door membership is computed in PostGIS with ST_Covers rather than ST_Within: partly because ST_Within has no native geography implementation, and partly because it excludes the boundary, which is exactly where addresses on a dividing street land.
Split the service worker's caches by what is in them rather than how they were fetched (bytes identical for every rep, rendered pages, and map tiles) and reconciled cache ownership at sign-in, at the map, and during the sign-out tap itself, so a crew phone handed between reps does not serve one rep's pages to the next. The queued-knock store is deliberately exempt, because those are the rep's own unsent work.
Taught the offline outbox to distinguish an expired session from a refused one: 401 is retryable, so a rep whose token lapses mid-shift signs in again and the same knocks land untouched, while 403 stays permanent because a policy that refused an authenticated request will refuse it identically forever.

Screens

Territory map with coloured door pins along a Vancouver block inside a drawn boundary — Doors cluster by zoom and carry both a colour and a glyph. Colour is the only chromatic thing in the entire app, so a pin is always the most visible object on screen, and the map still reads under colour blindness and in direct sun.

Knock sheet showing known household data, tags, and a row of outcome buttons — What the rep sees at the door: what is already known about the household, and a one-tap outcome row. Dispositions are abbreviated to the shorthand this industry already uses. Voice is the upgrade, not the requirement.

Design reference page showing four field themes and the eight-value disposition scale — Four themes chosen by the light the screen is read in, direct sun through to a true-black night shift, and the disposition scale ordered so neighbouring outcomes differ in brightness as well as hue.

Evidence From Source

The two ways of being wrong are not symmetric

Sizing the merge radius is not a tuning exercise, and the code argues the direction explicitly. Too large silently fuses two real houses into one pin and is discovered weeks later, in data nobody can reconstruct; too small splits one house into two pins, which the rep can see on the map and drag together. That asymmetry is why the migration that introduced the radius was itself corrected by a later one: the first answer, 20m, was wider than a residential lot, so a deliberate human placement now overrides a guessed address at 3m while guessed inputs keep the wide radius.

A debug screen that immediately earned itself

The first transcript put through a debug view for the extraction pipeline exposed a timezone corruption that was invisible from inside the app: JavaScript parses an offset-less date-time as local, so the confirmation card the rep read was right while the row in the database was seven hours early. The fix reads the naive string as UTC, looks up the zone's offset at that instant and applies it twice, because the first shift can cross a DST boundary and change the answer, and returns strings that already carry an offset untouched, since re-anchoring those would invent the exact error the function exists to prevent.

The measurement inverted the obvious action

Before enabling prompt caching on the extraction route I measured it, and the measurement said not to. The system prompt interpolated the knock's millisecond timestamp about a third of the way in, and the cache is an exact prefix match, so caching the prompt as written would have paid the write premium on every single call and never collected a read: roughly 18% more expensive per extraction, measured as two consecutive calls producing two writes and zero reads. Split into a static block and a per-request block, the same measurement on a larger model reads back every one of the 3,142 tokens it wrote, about 66% cheaper warm. On the default model it does nothing at all, and the entry says so: that model's minimum cacheable prefix is 4,096 tokens against this prefix's ~2,675, and a prefix under the minimum silently does not cache: no error, just zeros. The check suite therefore asserts that the static block is byte-identical across two different knocks and that no per-request value appears in it, because the failure mode is a silent zero in a ledger column rather than something that throws.

Type-checks, lint and build are not verification

Three real bugs passed tsc, eslint and next build together, and all three were found by opening the app and clicking. A constant exported from a "use server" module became a server reference, so a client-side equality check was permanently false and every form rendered an empty message box on first paint. An update ran unfiltered under a comment arguing that row-level security would pick the row, but PostgREST refuses unfiltered updates outright, before policies are ever consulted, so every save on that screen failed. And ten spinners sat at zero opacity running CSS animations forever. Nothing threw.

A test that passes in the broken case is not evidence

Production sign-in failed for a day because a byte-order mark had been welded to the front of an API key: PowerShell 5.1 prepends U+FEFF to anything piped into a native command's stdin, and the key was pushed that way. Only the key broke: the identically-corrupted URL healed itself, because the client library trims it and JavaScript's trim() treats U+FEFF as whitespace, so the symptom pointed away from the cause. Reproducing it took a deliberately expired session, because a session check with no cookie short-circuits in process and never reaches the network. Which meant the all-green smoke suite from the previous deploy, every route answering exactly as expected, was indistinguishable from what a completely broken auth stack produces. That is the rule I took from it, and it is why the checks that exist assert properties rather than absence of errors.

Design decided by measurement

I ran the marketing site against the platform-agnostic HIG foundations (accessibility, colour, layout, typography) and measured every finding in a browser at 320, 390 and 1280px, in both themes, at 100% and 200% text. The finding worth repeating: a max-width of 68ch was making line length worse rather than better, 99 characters, because Atkinson Hyperlegible's zero is 11.67px against an 8.0px average character, so the ch unit lies for that face; measured directly, 36rem gives about 72. The same pass found nine footer links at 78×17px with an 11px gap that were the only route to Sign in on a phone, and FAQ rows whose padding sat on the details element instead of the summary, so a row that looked 60px tall only opened from its middle 28px.