AlpineTime: backend — FastAPI, async ORM, rate resolution

Stack: FastAPI, SQLAlchemy 2.0 async, PostgreSQL 16, Docker. Python 3.12. All routes under /api/v1/, all IDs as UUIDs, all timestamps as timestamptz. The boring constraints are the load-bearing ones.

Overlap detection

Two paths create time entries: the timer (start, stop) and manual entry. Both can conflict. The naive fix is to reject any entry that overlaps an existing one. That works until someone forgets to stop the timer and manually enters the real time after the fact — now there’s a conflict and the correct entry can’t be saved.

The actual fix: detect the overlap, return the conflicting entries to the frontend with full context, and let the user choose. Merge (union the time range, concatenate descriptions) or replace (delete conflicts, save new entry). Both actions go into the audit log — the conflict and the resolution. overlap_service.py handles the detection with a SQL range check: start_time < :new_end AND end_time > :new_start. Returns structured conflict objects, not a bare error.

Rate snapshotting

Rates can change. A consultant’s hourly rate in January is not necessarily their rate in March. If you compute the rate at report time, a rate change retroactively alters historical billing — which is wrong.

The solution: snapshot rate_applied and rate_effective_date at entry creation. Resolution waterfall is: task rate → project rate → client rate → employee rate → 0. At report time, entries are grouped by (project_id, task_id, rate_applied, rate_effective_date). If a rate changed mid-month, that month’s report has two line items for the same task — one at the old rate, one at the new. Correct and auditable. The alternative is simpler to implement and wrong in production.

MWST on totals

MWST (Swiss VAT, currently 8.1%) applies per client, at a configurable rate. Some clients are exempt (mwst_rate=0). The mistake I avoided: don’t calculate VAT on each line item. Calculate it once on the project-level subtotal, or the grand total. Rounding in the middle compounds.

report_service.py groups entries, sums per group, collects project subtotals, applies MWST once at the end. The client’s rate lives on the client record. Zero means exempt — no VAT line on the invoice.

Boot-time validation

Two things the app refuses to start without in production: a JWT_SECRET of at least 32 characters, and a changed ADMIN_PASSWORD. Both checked in config.py via a Pydantic model_validator. These are the two most common self-hosting mistakes — weak secret, default password — and they’re cheaper to catch at startup than to find in a security review.

Rate limiting on auth endpoints via slowapi: 5 logins per minute, 3 password reset requests per minute. Not a complete defense but raises the floor.

Cursor pagination

All list endpoints use cursor-based pagination: ?limit=50&after=<uuid>. Time entries are append-heavy — new entries come in constantly. Offset pagination skews when rows are inserted between pages; cursor pagination doesn’t. The trade-off: the frontend can’t jump to “page 5,” but nothing in this app needs to. limit is clamped to 1-100.

Other things that exist but aren’t the main event: async SQLAlchemy session factory with connection pooling, structured JSON logging with X-Request-ID threaded through via ContextVar, bcrypt for password hashing (replaced passlib — simpler, one algorithm, no abstraction overhead), domain exceptions mapped to typed error codes so the frontend can handle them without parsing message strings.