Community Attendance & Retention

A weekly community in Jakarta — biweekly Friday worship, monthly Sunday services, ad-hoc retreats — tracked attendance on paper and a Google Form, tallied by hand. The master roster was a 191-row spreadsheet with phone numbers in three formats, birth dates in three data types, and three known duplicates.

Worse, the data was totals-only. It could say sixty people came; it could not say whether they were the loyal core or sixty strangers — so nobody could see who was drifting away, and no newcomer ever got a follow-up.

Product, schema and code — solo. I also help run the community, which makes me one of the app's own users: check-in is mobile-first because the person holding the door is holding a phone.

• Schema on paper before any code

  Four core tables — people, events, event_instances, attendance. Splitting events into a template and its dated occurrences is what keeps a recurring-event app honest: check-ins and invitations key on the occurrence, so being invited to Friday Worship can happen every month instead of exactly once, ever.

• Phone number as identity, forgiving lookup at the door

  Names alone create duplicates — "Yudi" and "Yudhi" quietly become two members. The phone number, normalized to E.164, is the unique key, and check-in lookup matches on the trailing digits so the format someone types in never matters.

• Row-level security on every table; anonymous users get nothing

  Attendee records are personal data of a religious community — sensitive by definition. Access is role-based (admin, organizer), the browser only ever holds the anonymous key, and every admin action lands in an audit log.

• History outlives the person record

  People soft-delete, and retention anonymizes in place: personal fields are tombstoned while the row and its attendance history survive. Analytics stay truthful after someone asks to be removed.

• Double check-in is impossible at the database, not the UI

  A unique constraint per person per occurrence makes concurrent check-ins safe, and the same on-conflict idempotency pattern backs the resumable roster import and invitation sends.

people           the roster — one row per person, phone (E.164) as the unique key
events           templates: “Friday Worship”, “First Sunday Communion”
event_instances  dated occurrences, materialized from recurrence rules
attendance       one row per person per occurrence — the historical record

Supporting tables — app_users, parishes, event_invitations, audit_log, app_settings, system_health — cover access, curation, invitations and monitoring. Every table sits behind row-level security.

Next.js (App Router) with TypeScript and Tailwind; Supabase for magic-link auth and Postgres with row-level security; Recharts for the analytics dashboard. Around the core: an import tool for the legacy spreadsheet plus Excel export, Telegram digests for the organizers, email invitations with proper .ics calendar attachments, and a bilingual UI — Indonesian first. Deployed on Vercel, with cron jobs materializing occurrences and writing a daily health check.

Tested with 838 Vitest unit tests and a Playwright end-to-end suite run against production.

In production on Vercel since the first sprint (May 2026), built and shipped in six sprints: auth and roles, people and check-in, events and attendance, analytics and import, notifications and invitations, admin and settings. As of August 2026 the final sprint — UAT and launch — is in progress: a fresh production reseed, the real roster import, consent capture, and a Cucumber-based UAT pilot before the community switches over.