State, Tasks & Time — Under the Hood
The one-query list's real cost, transitions in code vs. constraints, and where the non-transactional writes are luck vs. design.
In this deep dive
Anatomy of the one-query list
The application list (packages/db/src/applications.ts:384) is the most-read query in the product. It joins four tables, aggregates three document counts with FILTER clauses, and pulls the latest substate via a correlated subquery — one round trip, any volume.
| Piece | Cost | Notes |
|---|---|---|
| Base join (apps × postings × companies) | O(n) | 1:1 joins on primary keys; n = user's applications |
| Document left-joins before GROUP BY | O(n·d) intermediate rows | d = docs per application; the row explosion GROUP BY collapses |
count(distinct) FILTER (where …) ×3 | per-group hashing | Three needs facts from one pass — the FILTER clause is the star |
| Latest-substate correlated subquery | O(log e) per app | Index on application_events(application_id); LIMIT 1 descending |
| Transition check on write | O(1) | Map lookup + array includes over ≤3 elements |
| Weekly scoreboard count | O(log n + k) | Status + appliedAt predicates over the user-status index |
Two scaling observations. First, count(distinct) is the expensive aggregate — it must track seen IDs per group. At 100 applications it's invisible; at 100k rows-per-group you'd restructure (lateral subqueries per count, or counter columns maintained by triggers — a stored-state tradeoff you'd now make with data). Second, the query has no LIMIT: it returns the user's entire pipeline. Fine at n=200; the pagination decision is deferred until a human actually has more applications than a screen — deferral with a known exit, the recurring move in this codebase.
The read-model split matters for testability economics too: createApplicationReadModel(executeQuery) separates row-mapping (unit-tested with fixture rows, milliseconds) from SQL (integration-tested on PGlite, seconds). Fast tests get run; slow tests get skipped — structuring code so the cheap tests cover the logic is performance engineering of the development loop itself.
The decision: transitions asserted in application code
Transition map in core + assert at every write
The rule lives beside its unit tests — every edge, valid and invalid, is enumerated in the suite.
Readable error messages ("Invalid transition: offer → saved") and self-loop allowance for idempotency.
Only enforced through the store — raw SQL (a migration, a console session) can write any status.
Database triggers / CHECK constraints
Enforced against every writer, including manual SQL.
Transition logic in PL/pgSQL: harder to test, version, and read; error messages surface as constraint violations.
Full event sourcing (status = fold of events)
Perfect audit and time travel; transitions validated at fold time.
Read-side complexity everywhere for a product whose reads dominate. Wera keeps the events table as an audit log and the status as a column — the hybrid that takes the cheap 80%.
Note the same hybrid on substates: they're events (latest one wins) while status is a column. The dividing line is query frequency — status filters every list; substate renders on detail rows. Frequently-filtered facts earn columns and indexes; occasionally-read nuances can stay in the log. That's a transferable rubric, not a compromise.
Delivery semantics got the same explicit treatment: claimDueEmailReminders implements at-most-once. The alternative — send first, mark after, retry on crash — is at-least-once and needs consumer-side dedup (an idempotent send key at Resend). For deadline nudges, a rare missed email beats a double email that erodes trust in every future nudge. The semantics follow the product, not the other way around.
Where non-transactional is fine — and where it's luck
Almost no multi-statement flow here runs in a database transaction (the HTTP driver makes interactive transactions awkward). Each case deserves its own verdict:
markAppliedread-then-write: two simultaneous clicks both readready_to_apply, both writeapplied. The second write is a self-loop — harmless. The event log may record twomark_appliedevents: acceptable for an audit trail. Verdict: designed. The idempotency comes from the transition semantics, not a lock.- Applied-then-crash before task upsert: status flips but the follow-up task never appears. No retry exists (it's a server action, not a queued job). Verdict: accepted gap — self-healing would mean deriving "needs follow-up task" the way needs-flags are derived, an exercise below.
- Reminder stuck in
sending: crash between claim and send. Visible via status, but nothing re-claims it automatically. Compare discovery's stale-runningsweep — the same pattern exists one module over and hasn't been applied here yet. Verdict: known debt with an in-repo precedent. - Weekly plan regeneration:
generateWeeklyPlandeletes open items then re-inserts from current tasks — but completed items survive (the delete filtersstatus = 'open'). Re-planning never erases evidence of finished work. Verdict: designed, and quietly elegant.
updateApplicationStatus is a classic check-then-act: read status, assert transition, write. Interleave two different transitions (close vs. apply) and the last write wins with no error — a textbook lost update. Why is it tolerable here? Single user, and every path writes an event row, so the audit trail shows what happened even when the column's final value is the race's coin flip. Now imagine 100 users sharing a recruiting pipeline: you'd add optimistic concurrency (WHERE status = $expected on the UPDATE, exactly like the reminder claim). The fix pattern is already in the codebase — recognizing when it must migrate from reminders to statuses is the senior-engineer judgment.
Make it yours
Make status updates race-proof
Rewrite updateApplicationStatus using the claim pattern: UPDATE … SET status = $to WHERE id = $id AND status = $from RETURNING *, and treat zero rows as "the world moved — reload and retry." Update the unit tests for every transition edge.
Hint
The transition assert now runs BEFORE the update (using the caller's believed from), and the WHERE clause enforces it atomically. You've merged the check and the act.
Heal the stuck reminders
Port discovery's stale-sweep to reminders: at the start of each delivery run, reset sending rows older than N minutes back to scheduled. What delivery semantics did you just change — and what new duplicate-email scenario did you create? Write it down before you decide N.
Hint
You moved from at-most-once toward at-least-once: a claim whose send actually succeeded but crashed before marking will be re-sent after N. That's the trade; make it visible in the run's return counts.
Derive the follow-up need
Design "needs follow-up task" as a derived flag (like needs-flags) instead of relying on markApplied's write-time task creation: applied-family status + no open follow-up task + appliedAt older than X days ⇒ surface it. Which failure mode from Section 3 does this erase entirely?
Hint
The applied-then-crash gap disappears: a missing task is no longer a lost side effect, because the need is recomputed from facts on every read. Derivation as self-healing.
The count(…) FILTER (WHERE …) construct powering three needs-facts in one query pass.
Lost updates, read committed, and what the database would and wouldn't have saved you from here.
The read-model idea behind createApplicationReadModel, including Fowler's warning about using the full pattern too eagerly — advice this codebase visibly took.