Church Management System
A records system and public website for a church in Okitipupa, Ondo State — members, households, attendance, finance and content behind four roles and 23 permissions, on Express and MySQL.
- 16
- MySQL tables
- 70
- API endpoints
- 23
- RBAC permissions
- 40
- Automated tests
- Role
- Author
- Year
- 2026
- Status
- Open source
- Type
- Church records & public website
01Overview
The system gives a local church in Okitipupa, Ondo State, a public website and an admin dashboard over one database. The public side carries announcements, programmes, a gallery, a giving page and a contact form; the admin side holds members, households, attendance, income and expenses, and the content the public side shows.
I built it alone: an Express 5 API over MySQL with 70 endpoints under /api/v1, and 28 static HTML pages styled with Tailwind and driven by shared scripts. There is no frontend build step.
Most of the work went into who may do what. Member details and giving records are personal data, while announcements and the gallery are edited by whoever runs the church's media — so every admin endpoint asks for a named permission, and sessions can be revoked the moment someone's access changes.
02The problem
A church's records are small but sensitive. Contact details, household links, attendance and who gave what are personal; announcements and the weekly schedule are public. Both live in the same office and, here, in the same database.
The people using the dashboard have different jobs. Someone who posts the weekly announcement should not be able to export the finance ledger, and nobody should be able to promote themselves or delete the last administrator.
It also has to be simple to run: one Node process and a MySQL database, set up on first start without a migration tool or a frontend build.
03Architecture
- 01
One Express app, two surfaces
createApp builds the application from an injected database, rate limiters, mail and upload services. It serves 28 HTML pages — 14 public, five of them error states, and 14 admin, four of them auth pages — and 70 JSON endpoints across seven routers under /api/v1, with the same router also mounted at /api for older clients.
- 02
Sixteen tables, created on first run
db-init.js creates the database and 16 tables — users, password_resets, church_info, members, family_members, attendance, transactions, expense_categories, programs, weekly_schedule, announcements, gallery, contact_messages, contact_replies, external_links and activity_log — then seeds church details, the weekly schedule and a super_admin from the environment. Columns added after the first release are checked against information_schema before being applied, so the script is safe to re-run.
- 03
Permission-gated routes
Four roles in a hierarchy — super_admin, admin, editor, viewer — map to 23 permissions such as members:write and finance:export. All 53 admin endpoints call requirePermission; the role is read from the database once per request and cached, never taken from the client. A user cannot create, promote or delete an account at or above their own role, and the last active administrator cannot be deleted.
- 04
Revocable sessions
JWTs carry a purpose claim and a token version. Logging out, changing or resetting a password, or deactivating an account increments token_version, which invalidates every token issued before it, and a password-reset token cannot be used as a session. Password reset is a six-digit OTP by email, valid for ten minutes and usable once.
- 05
Audit trail and rate limits
Middleware wraps res.json on every write request and records each successful one in activity_log with the user, action, entity, IP address and request ID. Seventeen named rate-limit tiers run from 300 requests a minute across the API down to five password-reset attempts per ten minutes, keyed by email.
- 06
Uploads and scheduled publishing
Multer accepts images, then each file is re-checked against PNG, JPEG and WebP signatures once it is on disk, and anything that only claims to be an image is deleted. An interval scheduler publishes announcements whose scheduled time has passed, and stops cleanly on SIGTERM or SIGINT.
04Key decisions
Permissions, not role checks
Routes ask for members:write, not for admin. Changing what an editor may do is one edit to the permission table rather than a search through seven routers.
Static pages, no build step
The admin is forms and tables. Plain HTML with shared scripts runs anywhere Node runs, with nothing to compile. The cost is that Tailwind loads from a CDN, which the Content Security Policy names explicitly.
No inline scripts, so the CSP can forbid them
Pages declare a data-page attribute and js/page-init.js dispatches their behaviour, so script-src does not need 'unsafe-inline'. Announcement bodies are stored as plain text and rendered through text nodes, so stored markup cannot execute.
Test the real stack against a double
The API tests boot the real router stack from createApp against a database double, covering auth, RBAC and the public API. The frontend tests load each page with linkedom, run its real scripts, and check that selectors resolve and form payloads match the API contract. Forty tests, all passing.
05Stack
- Node.js
- Express 5
- MySQL
- mysql2
- JWT
- bcrypt
- Multer
- Nodemailer
- Tailwind CSS
- node:test
- linkedom