Square One Orbit
Full-stack task and ticketing platform for a 24/7 shopping mall — 102 employees across 9 departments, 6-tier RBAC, camera-only proof of completion, subticket dependencies, automated monthly performance reports, and a FastAPI backend with 44 endpoints.
I was the primary backend engineer and owned the API end to end: schema and migrations, authentication, the six-role RBAC model, the full ticket lifecycle, push/email notifications, the audit trail, scheduled jobs, and the Vercel deployment. That is 56 endpoints across 11 routers and 9 Postgres tables, plus the Alembic migration chain. A second engineer (Zaeem) contributed filtering enhancements that I reviewed and merged; everything else in the history under the core domain, auth, and infra is mine.
// the problem
What was broken
Square One Mall ran maintenance and inter-department requests on WhatsApp messages and paper — a request for "AC not cooling on Level 3" had no owner, no deadline, no proof it was ever done, and no record of who dropped it. Managers could not see what was outstanding across departments, staff on the floor had no structured way to claim or hand off work, and there was no accountability once a job was marked done. Everyone was coping with screenshots and memory.
// constraints
What made it hard
The people using it are floor staff and department managers, not office workers — much of the work happens on phones, frequently in basements and service corridors with no signal, so the mobile app had to work offline and sync later. The backend runs on Vercel's serverless platform (Hobby tier), which rules out long-lived processes and has a hard ceiling on cron frequency. Proof of completion had to be real — a photo or video, not a checkbox — which pulled object storage and upload limits into scope. And the role structure was fixed by the org chart: super admin, admin, head of department, department manager, supervisor, employee, each seeing a different slice of the system.
// architecture
How it fits together
The system is a stateless FastAPI service in front of a single Postgres database
(Neon), deployed as a Vercel serverless function. Statelessness was not a
preference — it was forced by the platform, and it shaped everything downstream.
The obvious first design used APScheduler for recurring jobs and FastAPI
BackgroundTasks for sending email after a response; both silently do nothing on
serverless because the process is frozen or killed the moment the HTTP response is
returned. So scheduled work (deadline alerts, monthly reports, archival) was moved
to dedicated cron endpoints triggered by Vercel Cron with a shared secret, and the
in-process APScheduler is kept only for local/non-serverless runs, gated on the
VERCEL env var.
RBAC lives in one module (permissions.py) as pure functions over the user and
the target row, rather than being scattered through the route handlers. Every
mutation calls the same can_* / is_admin_equivalent gate, so the hierarchy has
exactly one definition and the "management department behaves like admin" override
is expressed once. Notifications and the audit trail are separate services because
they are cross-cutting — nearly every state change writes an audit row and fans out
a notification, and I did not want that logic copy-pasted into a dozen endpoints.
The audit log is append-only with JSONB before/after snapshots so history can never
be edited through the API.
Mobile app
Offline-first client; queues writes, replays on reconnect with a client_reference_id idempotency key
API layer
FastAPI on Vercel — JWT (python-jose/bcrypt) auth, in-memory per-IP rate limiting, Pydantic v2 validation
RBAC gate
permissions.py — one hierarchy definition, checked before every mutation
Tickets router
Lifecycle orchestration: pending → acknowledged → in_progress → completed → resolved; idempotent create; self-claim on acknowledge
Services
notification_service (Firebase FCM), audit_service (append-only JSONB), storage_service (S3/R2 proof upload)
Postgres (Neon)
9 tables; unique (generator_id, client_reference_id) enforces dedup at the DB, not the app
// challenges
Where I got stuck
Serverless silently ate background work
The first version sent report emails via FastAPI BackgroundTasks and scheduled alerts via APScheduler. Neither errored — they just never ran, because Vercel freezes the function after the response. The fix was to stop relying on process lifetime entirely: move every recurring job to an explicit cron endpoint invoked by Vercel Cron, and do the email send inline before returning. "It works locally" was the whole trap.
FCM push failed three separate ways
Push dispatch got repaired across several commits — a blocking firebase-admin call sitting inside an async path, a malformed data payload the client couldn't parse, and failures that were swallowed so nothing surfaced. What finally worked was treating push as best-effort and isolated: it never blocks or fails the request that triggered it, and recipient lists are de-duplicated before send so one user can't get the same alert twice.
Audit writes 500'd on JSONB
Logging a ticket update crashed because the before/after snapshots contained datetime and UUID objects, which psycopg can't serialize into a JSONB column. I wrote a small recursive serialize_value that walks the snapshot and normalizes UUIDs, datetimes, enums, and nested dict/lists into JSON-safe values before the write.
Offline sync produced duplicates, then 500s
When the mobile app replayed a queued create after reconnect, a lost response caused a retry that inserted a second ticket. I added a client-supplied client_reference_id with a unique (generator_id, client_reference_id) constraint so the *database* rejects the duplicate, and the handler returns the existing row instead of erroring — the guarantee holds even for two concurrent retries. A follow-up bug was subtler: the offline queue re-serialized timestamps as timezone-aware (+05:00 / Z), and subtracting them from a naive utcnow() raised TypeError and 500'd on every retry while the same body worked online. I reproduced it with a 14-variant payload matrix against the real failing parent ticket, then normalized every inbound datetime to naive UTC at the boundary and mapped stale foreign-key references to a clean 422 instead of a 500.
Self-claim under concurrency
An unassigned, department-routed ticket has to be claimable by any eligible floor worker via acknowledge — but exactly one person can win. A read-then-write check would race. I made the transition an atomic conditional update (WHERE status = 'pending') so the status flip is the lock; the winner gets the assignment, the loser gets a 409.
// new ground
What I hadn't used before
firebase-admin for push, including shipping service-account credentials to a serverless host as base64 rather than a file. I learned how much of push reliability is about failure isolation and payload shape, not the send call itself.// takeaways
What I took away
- On serverless, silent no-ops are the dangerous failure mode; anything that relies on the process staying alive has to be redesigned, not ported.
- Idempotency belongs at the database boundary — a unique constraint on a client reference id beats any application-level duplicate check under retries and races.
- Timezone bugs hide until a second client serializes datetimes differently; normalizing to naive UTC at the API boundary is cheaper than chasing them later.
- A single-definition RBAC module is worth the up-front discipline — one hierarchy function is auditable in a way that per-endpoint checks never are.
- Distinguishing a client-data error (stale reference → 422) from a server fault (500) is what stops a mobile sync queue from retrying forever on a bad row.
// the journey
How it actually went
I started this treating Vercel like any Linux box with a Python process on it. I
wired up APScheduler for the recurring jobs and used FastAPI's BackgroundTasks
to send report emails after returning a response, tested it locally, and moved on.
It all worked on my machine and quietly did nothing in production. Finding that out
— that there were no errors, no logs, just reports that never arrived — was the
first real humbling of the project and the moment I stopped trusting "it works
locally" on serverless.
Push notifications were the next long slog. FCM failed in three different ways over a couple of weeks — a blocking call in the wrong place, a payload the app couldn't read, and errors that were being swallowed — and each looked like the previous fix hadn't worked. The thing that clicked was to stop treating push as part of the request and treat it as a side effect that is allowed to fail without taking the ticket down with it.
The offline-sync work was the most satisfying because the mobile side was adamant
the payload was identical online and offline, and on paper it was. Rather than
argue, I minted a real token, reproduced the exact failing request against the
production database inside a rolled-back transaction, and ran a matrix of payload
variants until the timezone case lit up. Seeing the same body pass naive and 500 on
+05:00 was the whole answer — the difference wasn't the body the developer typed,
it was how the offline queue re-serialized it.
If I did it again I would design for serverless and offline-first from day one, rather than retrofitting both after they broke. I would also add a small contract test that fires timezone-aware and naive timestamps at every create endpoint, because that single class of bug cost more debugging time than anything else in the project.
// outcome
What it changed
Shipped and running in production on Vercel — the live service reports version
2.2.1 and a healthy database, Firebase, and storage on its root health check. The
backend covers 56 endpoints across 11 routers over a 9-table Postgres schema, with
a 7-step Alembic migration chain and 50 unit/integration tests run in CI across the
Dev, Staging, and main branches. Core capabilities delivered: JWT auth with account
lockout after 5 failed attempts and force-expire session invalidation, six-role
RBAC, the full ticket lifecycle including a pending → acknowledged → in_progress →
completed → resolved flow with self-claim, camera-based proof-of-completion
uploads to S3/R2, fuzzy duplicate detection, offline-sync idempotency, Firebase
push plus email notifications, an append-only audit trail, and scheduled deadline
alerts, monthly reports, and archival via Vercel Cron.
- Users onboarded / active: the old WhatsApp/paper process:
// overview
In more detail
Square One Task Management replaces an ad-hoc (WhatsApp/verbal) workflow with a structured digital system covering task creation, multi-level assignment, execution, camera-proof completion, resolution, analytics, and full audit logging across a 24/7 operational mall.
The backend is FastAPI (Python 3.11+) with SQLModel ORM and 9 PostgreSQL tables. Six role tiers (Super Admin singleton enforced by partial index → Admin → HOD → Dept Manager → Supervisor → Employee) govern every endpoint. Class 1 tickets flow from any department to another; Class 2 are internal. Proof of completion requires a live camera capture — gallery upload is blocked at the API level. Background jobs handle monthly performance email reports and deadline alerts. The system supports 102 seeded real employees across 9 departments.
// features
What it does
- 6-tier RBAC (Super Admin singleton via partial index → Admin → HOD → Dept Manager → Supervisor → Employee)
- Two ticket classes: cross-department (Class 1) and internal (Class 2)
- Four-status lifecycle: Pending → In Progress → Completed → Resolved
- Subticket system with circular dependency prevention; parent cannot resolve until all children resolve
- Camera-only proof of completion — gallery upload blocked at API level; supports photo (≤5 MB) and video (≤60 s)
- 16 push notification event types across FCM (Android), APNs (iOS), in-app, and email channels
- Append-only audit log with separate DB role (INSERT + SELECT only) — tamper-proof by design
- Analytics: per-ticket KPIs, department-wise resolution times, employee performance, CSV/PDF export
- Automated monthly performance email reports and 48h/72h deadline alerts via APScheduler
// stack