Building a Broker CRM That Scales Past 100k Traders

A broker CRM at 5,000 traders looks deceptively simple. One Postgres box, a tidy Laravel app, synchronous writes everywhere, and the dashboard loads in under a second. Then the broker runs a good campaign, onboards an IB network, and there are 120,000 funded accounts pushing trade updates from MT4 and MT5. The query that took 40ms now takes 9 seconds, deposits occasionally double-post, and admin reporting times out at daily close. That is not bad luck. It is the predictable result of treating a high-write financial system as CRUD. Here is the architecture we use to get past six figures of active traders without the 3am pages.

Split the write path from the read path early

The single most useful decision is separating the command side (things that change state) from the read models (things people look at). Your client area, IB dashboards, and admin reports do not need the same normalized tables your ledger writes to. They need denormalized, purpose-built views, cheap to read.

In practice a write happens once, against a small authoritative table, and projectors fan that change into read models tuned per consumer. A trader's "account summary" widget reads one flat row with balance, equity, open PnL, and last deposit baked in. No five-way join at request time. When 100k traders refresh the client area every few seconds, a single indexed row lookup versus a 6-table join is the difference between a healthy database and a melting one.

The ledger must be append-only and idempotent

Money movement is where shortcuts bite hardest. We have seen a withdrawal posted twice because a PSP sent two webhooks 200ms apart and the second was not deduplicated. The fix is a discipline, not a patch.

• Append-only: a deposit, withdrawal, credit, or correction is a new immutable row. You never UPDATE a balance in place. Current balance is a projection (a running sum or a periodic snapshot), never the source of truth.
• Idempotent: every write carries an idempotency key (provider transaction id, internal request id) and the ledger has a unique constraint on it. A replayed webhook hits the constraint and is rejected cleanly instead of creating a phantom row.
• Reversals, not edits: a wrong entry is fixed with a compensating entry. The audit trail stays intact, which matters when a regulator asks you to reconstruct what happened on an account.

If you can rebuild every account balance by replaying the ledger and get the same number every time, you have an audit trail. If you cannot, you have a liability.

Treat MT4/MT5 activity as a firehose, not a transaction

Trade opens, closes, margin changes, and balance operations stream in from the MT4/MT5 server constantly, thousands of events per second during volatile sessions. The instinct to write each one synchronously into your main database is what kills these systems.

Push that traffic through a queue. We pull from the manager API (or a bridge) into a durable broker (Redis or a proper message queue), then process with batched, idempotent consumers. This buys three things: the database absorbs writes in batches instead of one round trip per tick, a slow consumer creates backpressure instead of dropped data, and an outage becomes a backlog you drain later rather than a permanent gap in trading history.

One failure mode to plan for: when a consumer falls behind during a news spike, the client area shows stale equity. Decide per surface whether stale-but-fast or slow-but-fresh is acceptable. A trader watching positions can take a one to three second lag if you label the timestamp; the dealing desk's risk view cannot, so it gets its own consumer.

Read models and caching for client area and admin

Once read models exist, caching becomes straightforward instead of a guessing game. Our rules:

• Cache the expensive, slow-changing things (IB hierarchy, symbol metadata, KYC status) with explicit invalidation on the write that changes them, not blind TTLs.
• Keep balances and equity in fast key-value storage updated by the projector, so the client area never touches the ledger for a display value.
• Never let an admin report run ad hoc against live trading tables. Point it at a read replica or reporting projection.

Where the first real bottlenecks show up

They are predictable, and almost always arrive in this order.

Reporting

The first thing to fall over is admin reporting, usually a "deposits and volume by IB by day" query that scans the whole transaction history. The answer is pre-aggregation: rolling rollup tables keyed by day, account, and IB. Reports read rollups, not raw rows, so one that scanned 40 million rows reads a few thousand.

IB and affiliate commission calculations

Multi-tier IB commission is deceptively heavy because a single trade can pay out across a chain of parents. Do not compute it live on the trade event. Queue a job that runs on closed trades, writes entries into the append-only ledger, and is idempotent so a re-run never double-pays. We have watched a naive recursive calc lock tables for minutes during close. Batched and flattened, it runs in seconds.

KYC document storage

Passports, proof of address, and selfies do not belong in your database, nor on the app server's disk. Push them to object storage (S3 or a Supabase Storage style bucket), keep only the reference and verification metadata in Postgres, and serve them through short-lived signed URLs. The database stays lean and compliance gets a clean access trail.

Partitioning, indexing, and what to monitor

Once the transactions and trades tables cross tens of millions of rows, partition them by time (monthly range partitions work well). Old partitions are cheaper to archive and hot queries only touch recent data. Index for actual access patterns (account id plus time descending covers most), and resist indexing every column, since each index is another write you pay for on the firehose.

Watch these, and alert on the trend, not just the threshold:

1. Queue depth and consumer lag per stream. Rising lag is your earliest warning, before users notice.
2. Projection freshness: how far behind each read model is, in seconds.
3. Ledger write rejections from the idempotency constraint. A spike means a provider is misbehaving.
4. Slowest 1 percent of reporting queries, so a new report does not quietly degrade everyone.
5. Replication lag on read replicas, since stale replicas silently corrupt reports.

None of this is exotic. It is the boring, deliberate separation of concerns that lets a broker CRM treat 120,000 traders the way it treated 5,000. Get the ledger, the queues, and the read models right, and scaling becomes a capacity problem you plan for instead of a fire at month end.