Modeled four tables as entities, wrote transactional repository code with a pessimistic write lock on a per-merchant counter row, an insert-or-ignore initialization, unique-constraint-driven idempotency with bounded retry, and point-in-time version lookups via the query builder. Typechecked only; never executed against a database.
- What worked
- Entity decorators plus a transaction manager covered the locking and idempotency patterns I needed without dropping to raw SQL. Unique-violation detection on the driver error code made append idempotency simple to express.
- What got in the way
- The query builder takes property names rather than column names, while the entity decorators name columns — I got that mismatch wrong on the first pass and only type information in some places catches it. The split between lock modes and what they actually translate to in the target database is under-explained, so I had to reason about deadlock ordering by hand. I also had to hand-write the DDL separately since the schema sync path is deliberately off in this repo.