LedgerCore

A system that records money moving between accounts has one requirement: never be wrong. Whether requests arrive at the same instant, the client retries, or the process dies halfway, money must not appear or vanish and no transfer may be applied twice.

An API (a NestJS 10 modular monolith) handles users and accounts, deposits and withdrawals, transfers, reversals, balances, history and reconciliation. A separate worker process from the same codebase leases outbox events with FOR UPDATE SKIP LOCKED and sends them as HMAC-signed webhooks. Invariants are checked by deferred constraint triggers in PostgreSQL 16, so a bug in application code cannot bypass them. An operations console (React + Vite) shows accounts, transfers and outbox delivery.

View larger

What changed

The balance endpoint did BEGIN READ ONLY → check account → SUM → COMMIT, making four round trips while holding a connection (HTTP p95 141 ms, the SQL itself about 0.3 ms). Under READ COMMITTED each statement takes its own snapshot, so the transaction gave no consistency guarantee anyway. Raising the pool from 20 to 40 helped only 7%, so the pool was not the cause, and EXPLAIN ANALYZE on a 200,000-row database showed the same 55 buffers and the same index for both versions. I replaced it with a single accounts + CROSS JOIN LATERAL (SUM …) statement (keeping the 404) and added a regression test that asserts one database call. Measured at a fixed 100 iter/s, 3 + 3 runs; differences in history and transfer p95 are within noise.

Symptom

A full benchmark produced three 500s and three 409s; the ledger stayed balanced.

Root cause

Postgres raised lock_timeout at 04:00:52.7, but the API logged its response 10 seconds later, with a burst of 500s within 40 ms: the Node process itself had stalled. The cause was logging every request at info level; on Windows, writes to a stdout pipe are synchronous and the PowerShell redirection could not keep up.

Fix

With LOG_LEVEL=warn the same scenario gave 0 faults, 0 409s and 37% more throughput. lock_timeout 3 s, statement_timeout 5 s and idle_in_transaction_session_timeout 10 s released the locks, and timeouts that had returned 500 INTERNAL_ERROR now return 503 DATABASE_TIMEOUT (retryable: true) (8c30dba).

Prevention

Wrote three timeout → 503 tests against a real Postgres and pg-pool, and recorded in the limitations that production needs an async logger or sampling.

Tests

392

Coverage

—

CI

GitHub Actions (커밋 메시지, 포맷, lint, typecheck, Postgres 서비스로 테스트, 빌드; billing 문제로 현재 중단)

Test tools

Vitest, supertest, 실제 PostgreSQL 16 (mock 없음), k6 + bench.ps1

Deployment

iwinv VPS on Docker Compose. Only web (Caddy) sits on the external proxy network, forwarding /v1/* to the API; Postgres and Redis ports are not exposed. deploy.sh: conflict-marker check → git merge --ff-only → build migrate, api and web images with SHA tags (the worker uses the api image) → start the API and worker after migrate succeeds → wait up to 60 s for /v1/health/ready → record only a ready version in APP_TAG and .deploy-history, otherwise print the exact rollback command.

Backups

Cron backs up every Postgres container on the server with pg_dump -Fc daily at 03:00 and keeps 7 days. restore-test.sh restores the latest dump into a scratch database and compares table counts, and for LedgerCore VERIFY_SQL also checks the double-entry invariant: a 68K dump, 14/14 tables, restore in 0.68 s, 20 entries with imbalance=0.

Rollback

rollback.sh returns api, worker and web to the previous (or a given) SHA without building and checks health; it never touches Postgres or Redis, and migrations are not rolled back. Tested in production 2066318 → d10826f → 2066318, ready every time (about 1 s).

Monitoring

postgresql.prod.conf: lock_timeout 3 s, statement_timeout 5 s, idle_in_transaction_session_timeout 10 s, queries slower than 500 ms logged, pg_stat_statements enabled; the database is connected to PG Insight over the monitoring network. /v1/health/ready

• db/migrations/1737000200000_enforce-double-entry-invariant.sql

  Deferred constraint triggers that check debits = credits at COMMIT

• db/migrations/1737000300000_make-ledger-immutable.sql

  Forbids editing and deleting entries in the database itself

• apps/api/src/modules/ledger/infrastructure/transaction.repository.ts

  FOR UPDATE on accounts in id order

• apps/api/src/modules/ledger/application/transfer.service.ts

  Lock, funds check, entries and outbox in one transaction

• apps/api/src/modules/ledger/application/reversal.service.ts

  Reversal that re-reads status after locking (the race fix)

• apps/api/src/modules/ledger/infrastructure/balance.repository.ts

  Single-statement balance read

• apps/api/src/shared/database/transaction.manager.ts

  AsyncLocalStorage transactions with retries on 40001/40P01

• apps/api/src/shared/errors/postgres-error.translator.ts

  SQLSTATE → domain errors, timeout → 503

• apps/api/test/concurrency/transfer-concurrency.test.ts

  Ten concurrency tests

• load-tests/bench.ps1

  Warm-up, three runs, median and validity rules

All 12 load-test runs on the final code were valid, with 0 faults, 0 deadlocks, 0 contention and a balanced ledger after every run. Cutting the balance read to a single statement lowered p95 from 24.9 ms to 13.4 ms in a fixed-rate A/B. Before trusting the numbers I also found and fixed a race where the second of two concurrent reversals returned 422 instead of 409 (ccee39b), tests that never ran on a clean clone because .env.test was gitignored (e0e6884), and __ITER and teardown bugs in the k6 scripts (0f76291).