A portfolio that shows people money has to be right every time. In MintCollect I never store a mutable balance - the collection is an append-only ledger, and every number you see is derived from it.
Make the math reconstructable
Each acquisition and sale is a row. Net profit comes from FIFO cost-basis lots, not a running total someone can corrupt with a bad write. Writes are idempotent - keyed so a retried request can't double-count - and concurrent edits to the same position take SELECT … FOR UPDATE so two writers can't race the same lot.
If you can't rebuild a balance from first principles, you don't have accounting - you have a number you hope is true.
Keep hot reads off the hot path
A 3.5M-row ledger is too slow to aggregate on every screen, so reads hit pre-aggregated rollup tables instead:
- Rollups are keyed by (account, variant, condition) and upserted on write via
ON CONFLICT. - Hot reads never scan the full ledger - they read the condensed row.
- A full rebuild from the ledger still finishes in seconds, so the rollup is a cache, never the source of truth.