[McMini]

Could you provide an example of how you would implement this approach to store Credit/Debit events for an account? Additionally, how would you handle a scenario where there are 30,000 events on the account, and you need to calculate the balance to prevent overdraft?

[hot_gril]

Heh, you found the hard case. You want to add a denormalized table (point #6) specifically for locking on the balance, just cause Postgres/MySQL `serializable` mode is way too slow to rely on. You still keep baseline insert-only credits/debits table(s) that you insert into in the same xact, and all the usual rules apply there.

You can also do this without making such an exception. I used to keep a separate "pending" table that I'd insert into, commit, then check the balance with the pending row included before moving it to non-pending. So two transactions. That worked, problem is it was annoying. Though it was a good solution for debits/credits that involved an async external step that could fail or time out; simply ignore the pending rows that are too old and never got resolved. 30K rows is still small enough to query quickly.

[McMini]

What are the key trade-offs in your approach vs doing event sourcing?