Civic platform
Volunteer Clinton County
Client · live
Volunteer Clinton County connects local nonprofits and civic groups with residents who want to help. Organizations register and get approved, then post opportunities, events, donation needs, and board openings; residents browse by category and location, sign up, log hours, and message organizations directly.
Under the friendly surface it is a security-sensitive, multi-tenant system: every record lives in its own database schema, access is governed by row-level security, and the content workflow is enforced by the database itself.
Multi-tenant architecture
All application tables live in a dedicated, non-public Postgres schema. Organizations have a full lifecycle (pending → approved → archived) and a four-level membership hierarchy (owner › manager › poster › viewer), with storage paths and access checks scoped per organization.
Database-enforced workflow
All five listing types share an eight-state submission status machine. A single SECURITY DEFINER function is the only legal way to change status — it validates the transition, stamps timestamps, and writes an immutable audit record — and a trigger blocks any attempt to change status directly, so neither the app nor the API can bypass moderation.
Org↔volunteer messaging system
A two-sided messaging flow lets organizations reach out and volunteers accept or decline. Row-level security ensures only participants can read a thread, inserts require an accepted conversation, and a SECURITY DEFINER routine opens a conversation and its first message atomically.
Maps & local discovery
Listings are browsable on an interactive Leaflet map and filterable by location, so residents can find opportunities near them across the county.
Volunteer clearances
Volunteers upload Pennsylvania background-check and clearance documents into a private storage bucket. Files are never public; access by an organization is gated by a dedicated function that checks roster discoverability, share opt-in, and conversation status before issuing a short-lived signed URL.
Hours, verification & supervised service
Volunteers log service hours that organizations verify through a locked, audited routine. A full supervised-service subsystem issues single-use, HMAC-hashed codes that give a supervising agency time-bounded read access to a person’s hours — for court-ordered or parole oversight — capturing an immutable retention snapshot when access ends.
AI-assisted listing import
Org staff paste a flyer’s text or upload an image, and a Claude call — constrained by tool-use to a strict schema so it can’t free-form — pre-fills a structured listing for any of the five listing types, with every field sanitized before it touches the database.
Capacity & waitlists
Opportunities and events can cap their slots; a SECURITY DEFINER routine takes a per-listing advisory lock to count registrations and overflow, then automatically promotes the oldest waitlisted person — logged-in or anonymous, in FIFO order — with an email when someone cancels.
Volunteer roster
Volunteers opt into a searchable roster with per-field privacy controls; org staff can filter by interests, skills, and clearance type, bulk-message matched volunteers, and export a streaming CSV — all gated by a membership-check function.
Org claim & invite links
Pre-seeded organizations are handed to real operators two ways: a public claim form an admin approves, or single-use SHA-256-hashed invite links that show a safe, non-consuming preview to email scanners and promote the redeemer to owner inside a row-locked transaction.
Security & deliverability
Every public action runs through a named Upstash rate limiter (25+ of them, fail-closed in production) behind a per-request CSP nonce, and all outbound mail is logged with bounce and complaint tracking plus a suppression list via signed webhooks.
Start a project
Want something like this?
Tell me what you're trying to build and I'll come back with a clear, practical plan.