Case study
Modelling a Credit-Hours Booking System
Granted credits became spendable, and balances are derived from sessions instead of a stored counter.
- Project
- Online tutoring platform
- Industry
- EdTech / SaaS
- Role
- Domain model, rate resolution, booking UX
- Python
- FastAPI
- SQLModel
- Alembic
- PostgreSQL
- Next.js
- TypeScript
- SWR
- Vitest
- Playwright
The context
A booking model where hours are the quota, not a fixed class schedule. Families pay offline, an admin grants hours, and parents self-book against the balance. Tutors are paid hourly for the lessons delivered.
The problem
The platform assumed a recurring scheduled plan. The business actually ran on prepaid hours with one tutor per subject.
- Manually granted credits were unspendable: every family set up through the normal assignment form hit a "tutor has no rate" error, however many hours they held.
- Balances were wrong for families who had both a scheduled plan and grants.
My responsibility
- Modelled the domain
- Fixed rate resolution
- Rebuilt the booking UX: duration-first selection, month-grid calendar, tutor cancel and reschedule with a mandatory reason
Architecture
- Admin assigns a subjectA schedule-less plan, unique per (org, student, subject), so subject implies tutor
- Admin grants hoursstudent_credit_grants
- Parent self-books, duration firstAtomic slots[] create sessions
- Balance = grants − consumptionDerived from session rows; class-quota plans excluded
- Tutor pay = rate seam × hoursFeeds the tutor payout
Key decisions
Reuse the existing entity
The temptation was a new assignment table, but one had already been retired into the plan table. An assignment is a plan with null classes_per_week and total_classes.
Subject implies tutor
A partial unique index enforces it, so the UI never shows a tutor picker.
Derived balance, no counter
Consumption is computed from session rows, which removes drift between a stored balance and reality.
Deploy order inverts for one story
Making the cancel reason mandatory on the backend would 422 the live tutor Cancel button until the frontend that supplies it ships, so the frontend ships first.
Technical challenges
1.The two-column rate trap
Tutor pay lived in two column pairs, each with its own currency default (one USD, one GBP). Reading a rate without its partner stamped the wrong currency onto a session. Fixed the resolver, then normalised all four columns server-side.
2.Zero means two things
In legacy columns zero meant "unset"; in the new one it means "pays nothing". Both semantics had to coexist during the migration.
3.Planning overturned by the code
Batch booking, row locking, and duration-aware availability already existed, and the family Cancel button on credit bookings was silently broken in production. Verifying before building changed the plan several times.
Results
- Granted credits became spendable: [X] families unblocked, [X] hours booked in the first [period]
- Admin time per booking: [X → Y minutes], since parents self-serve
- Balance disputes: [X → Y]
What I learned
- Model the business as it runs. The domain was prepaid hours, and the code was built for subscriptions.
- A column pair is one fact. Never read half of it.
- Grep for "missing" features before costing them.