Bolton Vouched

A Bolton trades group answers “anyone know a good plumber?” every week, and every week the answers scroll away. The recommendations are real, local and worth more than any advertising — they are simply unsearchable, and the people being recommended have no way of being found outside the thread.

The obvious build is a directory. The reason most of them are worth nothing is that the ranking is for sale, the listings are scraped from somewhere without asking, and a trade certificate is a badge somebody typed in once. All three of those are the same failure: a rule that lives in the interface, where it can be talked round.

So the rules live in the database instead, and the interface is only allowed to show them.

Nobody is listed without agreeing to be. A provider goes invited → consented → live, and the move to live is not a permission the admin screen grants — it is a CHECK constraint that refuses the row unless consent is recorded. What is recorded is the part I would want checked: alongside the timestamp sits a SHA-256 of the exact wording that was on the page they agreed to, so the question “what did they actually consent to?” has an answer rather than a date.

A certificate that expires takes the listing down by itself. Gas work and driving instruction are gated twice over. A trigger refuses to file the category at all without an in-date Gas Safe or DVSA check, and the public view independently hides it again the day that check lapses — so the dangerous state is unreachable from the write side and invisible from the read side, rather than depending on somebody running a report.

Nothing about the ranking is for sale, and the schema is the proof. The score is a shrunk mean: a provider's own recency-weighted average pulled towards the register's average in proportion to how little evidence there is, with a two-year half-life so an old job counts for less than a recent one. Below three reviews there is no score at all — the interface gives those a New on Bolton Vouched section of its own rather than hiding them. The useful consequence is visible on the screenshot: a perfect record from two jobs sits below a good record from four, which is the right answer and the one a star average gets wrong. There is no argument to that function that money could reach.

A trader's number is never in the page. The only thing the public view exposes is a boolean saying whether they are on WhatsApp. Tapping Call asks a server route, which logs the tap and answers with a redirect; the number is read by a SECURITY DEFINER function the browser's own key has no permission to call. Enquiries are counted once per person per day, keyed on an HMAC of the device rather than anything stored, and the provider's own taps and mine are excluded from their count — the number is for them, so the two people with a reason to inflate it are the two it ignores.

Underneath all of it, the anonymous role starts with nothing: every grant is revoked, and the default privileges are revoked too, so a table added next year is closed before anyone remembers to close it. The one door is a single view. Getting that view to work for an anonymous reader needed the sharpest thing I learned on this build — Postgres checks function permissions against the calling role even inside a view, so the helpers it needs sit in a schema the public has no access to at all, which works because permission is checked at run time and the schema only matters when a name is looked up.

It is built and deploying, and it has not launched. The directory, categories, profiles, the Call and WhatsApp routes, the consent links and the admin area all work; the policy pages still carry a draft, pending legal review banner, the domain has not been pointed at it, and no resident can leave a review yet — the scoring is finished and tested, the way in is not open. Saying which is which is the whole point of writing it down here.

The database is tested as a database rather than through the app. Running it myself: 147 pgTAP assertions across five SQL suites, and 81 unit tests, all passing — and CI does not fake the database, it starts a real Supabase, applies every migration, runs the SQL suite against an empty register, then reloads fixtures and drives the whole thing in a browser with Playwright. The scoring tests are the ones that matter: they assert the no-score floor and the shrinkage directly, because an averaging bug is invisible until it has quietly ranked somebody's livelihood wrongly for a month.

Two decisions are worth naming because they cost something. Hashing a device needs a secret, and the function throws rather than falling back if that secret is missing — an unconfigured deployment stops counting instead of silently counting everyone as one person. And the data lives in Supabase's London region for the plain reason that the privacy notice says it is in the UK, which is a sentence that has to be kept true rather than written.

Still ahead of launch, and listed in the repository rather than remembered: a solicitor's read of the draft policy pages, ICO registration, and the twelve-month purge job the retention policy already promises — which is written down as not built, because a privacy notice describing a job that does not exist is the worst kind of untrue.