Building Volunteer Clinton County: multi-tenant civic software on Supabase
A build log on the engineering behind Volunteer Clinton County, the multi-tenant civic platform I co-founded on Supabase, covering database-enforced moderation, private clearance storage, hour verification, waitlists, and the constrained AI listing import.
Volunteer Clinton County is a civic platform I co-founded and developed. Local organizations post volunteer opportunities, events, donation needs, and board openings. Residents browse by category and location, sign up, log hours, and message the organization directly. Every application table sits in a non-public Postgres schema on Supabase, walled off from the rest, and the rules are enforced there instead of in the app.
Key points
- Organizations run a pending, approved, archived lifecycle with four membership levels: owner, manager, poster, viewer.
- All five listing types share one eight-state workflow, and one database function is the only legal way to move a listing between states.
- Clearance documents, the background-check paperwork volunteers upload, sit in a private bucket and reach an organization only as a link that expires.
- Hours are verified through a locked, audited routine, with a separate subsystem for court-ordered oversight.
- Every public action runs behind a named Upstash rate limiter, a cap on how often it can run, and in production those limiters fail closed.
The full feature list is on the Volunteer Clinton County project page, and the platform is live at volunteerclintoncounty.org. The same pattern runs through my nonprofit and civic work.
Why do the rules live in the database?
Turnover. A volunteer organization rotates officers and hands the account to somebody who has never seen the admin screen. The software has to keep behaving when nobody there remembers how it was set up, and when the next developer is not me.
Moderation is the review a listing passes before it goes public. If that check lives in a route handler, any future code path can skip it: a cron job, a script, an API route added next year.
So the status machine sits in Postgres. A trigger blocks any direct change to a listing's status. The only way through is a SECURITY DEFINER function, which runs with its own permissions. It validates the transition, stamps timestamps, and writes an audit record nobody can edit later. Content cannot skip review, because the database refuses the write.
| Rule | Enforced by | What goes wrong without it |
|---|---|---|
| Listing moderation | Transition function plus a trigger blocking direct edits | A new route publishes unreviewed content |
| Clearance access | Access-check function issuing an expiring link | A forwarded document link still opens later |
| Hour verification | Locked, audited routine | Verified totals change with no trail |
| Slot capacity | Advisory lock on that listing | Two people claim the same last slot |
| Message privacy | Row-level security, a per-row check scoped to participants | A later query returns another org's thread |
What multi-tenancy means when the tenants are small boards
Multi-tenancy here means one installation, with every organization in its own scope instead of its own copy. Storage paths and access checks are keyed per organization. Membership has four levels because real boards are not flat. The director who owns the account is not the coordinator who can post an opportunity but never remove someone from the roster.
Records were pre-seeded so the directory was not empty on day one, and each one needed a claim path. There are two: a public claim form an admin approves, and single-use invite links whose tokens are stored as SHA-256 hashes, so a raw token never sits in the database.
The invite preview deliberately does not consume the token, because corporate email scanners follow every link and would otherwise burn it first.
Why do clearances never get a public URL?
Because a clearance is background-check paperwork, about the most sensitive thing a resident will upload to a county website. Pennsylvania volunteer work involves that paperwork, so the files go in a private bucket and are never public. When an organization wants to see one, a dedicated function checks roster discoverability, share opt-in, and conversation status, then issues a signed URL that works briefly and stops.
The expiry is the point. Signed links get forwarded and sit in inboxes, and a link that stops working limits how far a leak travels.
Hours, verification, and supervised service
Volunteers log their own hours, and organizations verify them through a routine that locks the record and logs every change. A verified number cannot quietly drift.
Alongside it runs a supervised-service subsystem I did not plan to build. It issues single-use codes, HMAC-hashed rather than plain text, that give a supervising agency read access to one person's hours for a set window. When the window closes, the system captures a retention snapshot nobody can edit.
That subsystem is for court-ordered or parole oversight, because community service hours are frequently a legal obligation rather than a hobby. The agency needs proof, the volunteer needs the access to expire, and the snapshot preserves what was shown.
How do waitlists handle two people and one slot?
One at a time, by locking the listing. Opportunities and events can cap their slots, and registration looks simple until two people hit the last opening at once. Without a lock you double-book, and a volunteer drives to a shift that does not exist.
The routine takes an advisory lock on that listing, counts registrations and overflow, then promotes whoever has waited longest, first in first out. That order holds whether the person is logged in or anonymous, and a cancellation emails the next in line. First in first out is the only ordering I could defend to whoever did not get the seat.
Where AI fits in the listing flow
The scope is narrow on purpose: the model fills in a form, a human reviews and submits it, and the result enters the same eight-state workflow. Org staff paste a flyer's text or upload an image of one, and a Claude call pre-fills a structured listing for any of the five types. Tool use constrains the call to a strict schema, so the model cannot free-form its way into the payload. Every field is sanitized before it touches the database.
The barrier for a small organization was never writing the listing. It was retyping a flyer that already exists.
Rate limits, email, and the deploy gate
More than 25 named rate limiters cover the public actions. In production they fail closed, so an unreachable limiter blocks the action. Every page carries a per-request CSP nonce, a one-time token that blocks unexpected scripts.
Outbound mail is logged with bounce and complaint tracking. A suppression list of addresses the system stops mailing is fed by signed webhooks. A migration-deploy gate blocks a release when the database is out of sync, which matters because the database holds the rules.
The tradeoff is real. Database enforcement is slower to change, and for a smaller product it would be overkill. It earned its place here because the data is sensitive and the operators rotate. On the Rotary Club of Downtown Lock Haven site I made a lighter call, with a three-tier role system and magic-link sign-in.
Common questions
Does enforcing the rules in Postgres make the software harder to change?
Yes, and that is the trade I took. Adding a state to the listing workflow means a migration and a rewrite of the transition function, not an edit to one file. For a product still finding its shape that would be a bad deal. Here the rules have not moved since launch, and the people operating them have.
What does a volunteer actually see of all this?
Almost none of it. A volunteer uploads a clearance once, decides field by field what a roster search can show, and gets an email when a waitlist seat opens. The pieces that took longest to build are the audit trail on hours and the expiring document links, and a volunteer never meets either unless something has gone wrong.
Can an organization skip the flyer import and just type a listing?
Yes. It is the same form either way, and both land in the same eight-state review. The import is there for the person holding a PDF in front of an empty form. That is the version of this problem I kept hearing about.
Could another developer take this over?
Yes. The framework and the hosting would not slow anyone down. The schema would. The transition function, the row-level security policies, and the storage access checks are where the behavior lives. Those are the three files I would hand someone on their first day.
Roles, clearances, and verified hours are where most of the cost sits in a build like this. The nonprofit and civic tech page covers how I scope them.