logoIsmail Iusifli
← Back to systems
Open-Source Core2026· Sole Engineer

edtech-billing-core: Race-Safe Billing on PostgreSQL

Append-only ledger · Deadlock-free lock order · Verified payment webhooks · Open source

↗ Source Code
Integration Tests
104 Passing
All against a real PostgreSQL 16, no database mocks; green CI on every push
Mutation Check
12 / 12 Killed
Each defence removed in turn; the suite must fail for every one
Isolation Level
READ COMMITTED
Explicit lock order instead of SERIALIZABLE retries

Architectural Trade-offs & Decisions

✓ READ COMMITTED with one explicit lock order
✕ Rejected: SERIALIZABLE transactions plus SELECT … FOR UPDATE on the student (the original production design)
A concurrency probe showed roughly 9 in 10 concurrent writes aborting with serialization failures (40001), and FOR UPDATE deadlocking against a cancellation's foreign-key checks. Ordered FOR NO KEY UPDATE / FOR SHARE locks make every concurrent call succeed without retries.
✓ Append-only ledger enforced by a database trigger
✕ Rejected: Deleting and rewriting a lesson's charge when attendance changes
Corrections become reversal rows, so every balance can be explained row by row, and no application bug can silently rewrite history.

Context

The money-handling core of the production platform I run for an online school, extracted into a standalone, framework-free TypeScript library on plain PostgreSQL. The production code is private; this repository is the part worth reviewing, with every lock and constraint visible as plain SQL.

Billing: exactly-once charges under concurrency

Staff mark, re-mark, clear and cancel lessons at the same time. The student must be charged exactly once, and the ledger must always agree with the attendance journal.

  • Append-only ledger. A trigger rejects UPDATE and DELETE; corrections are reversal rows.
  • One lock order everywhere. Writes for a student take FOR NO KEY UPDATE on the student row, then FOR SHARE on the lesson. Cancellations take FOR UPDATE on the lesson, so "is this lesson cancelled?" cannot change between the check and the write.
  • Why NO KEY UPDATE. A cancellation writing a reversal needs a KEY SHARE lock on the student via foreign-key checks. FOR UPDATE would block it and deadlock; FOR NO KEY UPDATE allows it.

Payments: a webhook that trusts nothing

Rate limit → constant-time secret check → provider IP allowlist → re-fetch the payment from the provider's API and validate it at runtime → match owner, amount and currency against our own payment intent → credit idempotently with ON CONFLICT DO NOTHING on the provider's payment id. Ten identical deliveries credit once.

Auth: enumeration-safe login

Unknown emails still cost one bcrypt compare, so response time does not reveal who has an account. Login limits run per (IP, email) and per email across IPs, checked and counted atomically under a per-email advisory lock.

How it's tested

  • Deterministic interleavings. A test pauses one operation right after it takes its locks, starts a competing one and asserts it waits — one test per lock, plus the deadlock case.
  • Bursts. 20 simultaneous marks of one lesson, 10 identical webhook deliveries, 50 parallel login guesses: the ledger and the limits come out exact.
  • Randomized ledger invariants. Seeded runs of 200 random operations, five at a time in parallel, checking eight invariants after every batch.
  • Mutation check. A script removes each defence in turn — either lock, the cancelled-lesson guard, the idempotency key, the dummy bcrypt compare and others — and the suite has to fail for all 12.

A bug the randomized test caught

The first run failed on "a cancelled lesson nets to zero for every student". A student was charged, the lesson was cancelled (charge reversed), then the attendance mark was cleared — reversing the charge a second time. The student was credited for a lesson they were never charged for.

The fix: clearing refuses a cancelled lesson, and the check runs under the lesson's share lock so a cancellation committing at the same moment cannot slip between check and write. Both the plain case and the race are now tests.