A community site where the database keeps the secrets

The problem

The first version was a static site: hand-written HTML, CSS and one JavaScript file. That was enough for events and photographs, but a static site cannot keep a secret. Every file it serves can be downloaded, so a members-only page could never be more than a courtesy — members' phone numbers could not be stored anywhere the check could reach without also publishing them.

Two smaller problems came with it. The contact form validated, showed a success message and then threw away what was typed. And every new event or story meant someone editing code, when the people running the community are not developers.

What it does

Anyone can read the public site: events, each with its own page, programmes, a photo gallery, community stories and the office holders. Members sign in to edit their own card and to read meeting minutes, which stay off the open web.

The committee gets its own screens for events, stories, photographs, members, meeting decisions and incoming messages — everything the static site needed a code change for. The contact form now lands in a table only the committee can read.

Architecture

Next.js on Vercel, pinned to the Tokyo region to sit near the people using it, in front of Supabase for the database, sign-in and file storage. Public pages are prerendered and served from the CDN, and publishing an event refreshes its page at once. Pages that depend on who is signed in are rendered per request and never cached.

The app never holds a key that can bypass the policies; the database decides what each visitor may see.

Decisions that shaped it

• The security is in the database, not the app. The public holds no grant at all on the table of phone numbers. A member can update their own card and nobody else's, and only the columns they are allowed to touch — role and committee access are not among them, so no one can promote themselves. A story a member submits arrives as pending, because the status column is not theirs to set.
• No master key in the app. Supabase's service-role key bypasses every policy, so the project does not have one. The committee's writes go through database functions that check permissions inside Postgres, which means a bug in the app cannot hand out more than the database allows.
• Membership is proven in person, for nothing. SMS codes would cost about ¥8–10 a message and grow with the community; the free email links are rate-limited to a handful an hour. An account alone only proves someone owns an email address. So the committee — who already know everyone and already hand out membership cards — issue a one-time code, shown once and stored only as a hash, and the member enters it after signing up.
• Three levels of access, not two. With only "admin" and "everyone else", the only way to let an officer post an event was to give them the power to delete members too. General members, the leadership team and the committee now each get exactly what their role needs.
• Minutes come off the open web. Fees and dates are the community talking to itself, not something to be indexed and quoted out of date. Reading them needs a signed-in member, enforced twice: the public's read grant is revoked, and the policy separately requires membership.
• Test the policies, not just the build. A passing build says nothing about who can read what, so the rules are tested against a real, throwaway PostgreSQL. The first test runner listed its migrations by hand and silently skipped three new ones, including one that had made the whole register public. The runner now picks up every migration the moment it exists.

Outcome

The site is live, and the committee now runs it without touching code — events, stories, photographs and the member register all have their own screens. Alongside it sit a user guide and a printable membership card, so the in-person step that proves membership has something to hand over.

Stack