Church venue booking & smart-lock access

Background

The church needed its volunteer teams to book rooms online, admins to approve, doors to open according to bookings, and a record of who opened what. I designed and built all of it, pro bono.

What I did

• Member app: SMS sign-up with admin review, WeChat login; rooms with time-slot occupancy; team and personal bookings; three tiers of edits (content only, shorten, reschedule — rescheduling needs re-approval); door passcodes; repair requests and room borrowing.
• Admin console: dashboard, approvals and booking on behalf of members; open, restricted and dedicated room modes; lock monitoring (status, logs, alarms, revocation); idle detection, team tree, reports, audit log, settings; a capability grid for permissions.

Problems and solutions

• Three kinds of passcodes: time-limited codes are issued on approval with buffers on both ends and revoked on cancellation or expiry; one-time codes have a validity window, a concurrency cap and a cooldown; dedicated rooms get standing codes valid for 48 hours and rotated every 24.
• Tracing unlocks: each unlock log is matched to the passcode that was valid at that moment, excluding standing codes already replaced and codes revoked before the unlock.
• Alarms vs. real unlocks: alarm records don’t count as someone entering, wrong-code attempts don’t count as use, and a failed sync or offline lock never auto-releases a booking.
• Concurrency: SQLite transactions can’t prevent check-then-write races, so work is serialized per key (room, team, …) in-process, with a single allowed nesting order that rules out deadlocks by construction.
• Secrets: secrets are stored encrypted and never sent to the browser; lock passcodes use AES-256-CBC; production refuses to start with short keys.
• Trade-offs: PostgreSQL, Redis and ELK were evaluated and deliberately left out — a single SQLite instance with backups fits this scale, and the reasoning is documented.