Reconciliation is a feature, not a support ticket

An advisor called us on a Tuesday because a client's portfolio showed ₹4,18,000 in our dashboard and ₹4,17,600 on the statement the RTA had sent them. Four hundred rupees. On a portfolio that size it rounds to nothing, and it was still the only thing either of them wanted to talk about.

They were right to care. Nobody trusts a number that's close.

The difference turned out to be one scheme valued at the previous day's NAV, because that day's file landed after our valuation job ran and nothing went back to fix it. The fix took an hour. Working out why the system permitted it took considerably longer, and most of what I know about financial back-office software came out of that stretch.

The feed is a series of claims

The mental model that helped most was giving up on "today's NAV" as a fact we fetch. What we have is a stream of assertions from outside parties, each with a time we received it, each capable of being superseded later.

NAV files arrive late. They arrive twice, identical, because somebody re-ran an export. They arrive revised, same scheme and same date, a different number, days after the fact. Once we got a file where the date column used a different format for exactly the rows belonging to one AMC.

So we stopped overwriting anything. NAV history is append-only:

create table nav_history (
  id            bigserial primary key,
  scheme_code   text        not null,
  nav_date      date        not null,
  nav           numeric(18,6) not null,
  source        text        not null,
  received_at   timestamptz not null default now(),
  superseded_by bigint      references nav_history(id)
);

create unique index nav_history_current
  on nav_history (scheme_code, nav_date)
  where superseded_by is null;

A revision inserts a new row and points the old one at it. The partial unique index means there is exactly one live NAV per scheme per date, enforced by the database rather than by whichever import path happened to run. And because the old value is still there, when an advisor asks why a valuation for a past date changed between Monday and Wednesday, there's an answer instead of a shrug.

The same shape applies to nearly every external feed. Prices, corporate actions, KYC status. Every time I've been tempted to store current state and discard what it replaced, I've regretted it inside a quarter.

Imports are all-or-nothing

Every file lands in a staging table first, tagged with a batch id, and nothing touches live tables until the whole batch validates. Half a file applied is the worst outcome available to you: a portfolio that is wrong in a way nobody can see, versus a job that failed loudly and got re-run.

Validation runs in layers, cheapest first.

The file itself: expected row count against the header, the date the file claims to be for, and whether we've already ingested a file with this checksum. That last check has saved us more than anything else in the pipeline. Operations teams re-upload files. They just do.

Then rows: every column parses, dates are real dates, NAV is positive, scheme code exists in our master.

Then the file against itself: no duplicate scheme codes, and where a file carries totals, the totals add up.

Then the file against what we already know. A NAV that has moved more than a threshold since the last known value for that scheme gets flagged rather than rejected. Debt funds don't move 8% overnight, equity funds occasionally do, and a hard reject would have blocked a legitimate import on a bad day. A flag put it in front of somebody who could say yes.

Re-running an import has to be safe. Ours keys on (scheme_code, nav_date, source), does nothing when the incoming value matches, supersedes when it doesn't. That property is what lets you tell operations "just upload it again" without a knot in your stomach.

Numbers

Money is numeric in Postgres. Not float, not double precision, and this isn't a style opinion: 0.1 + 0.2 is why your reconciliation report has a line for ₹0.000001.

The trap on a TypeScript stack sits one layer up. Postgres drivers hand numeric back as a string, deliberately, because a JS number is a float64 and cannot hold what numeric(18,6) holds. If a helper somewhere in your codebase calls Number(row.nav) to make a type error go away, you have thrown the guarantee away at the exact boundary that was protecting you.

We ran decimal arithmetic end to end. Strings out of the database, a decimal library in the service layer, strings over the API, formatting only at the last step in the UI. Every conversion to number is a lint error. That rule looked pedantic when we wrote it and has never cost us anything since.

Units get the same treatment. Mutual fund units carry three or four decimals depending on the AMC, and a rounding rule that's fine for display will slowly desynchronise a holding from the registrar's books over a few hundred transactions.

Backdated transactions, which is where I lost the most time

The obvious design is a holdings table you update as transactions come in. Fast, simple, and it falls apart the first time a transaction arrives late.

Which it will. A purchase executes on the 3rd, the feed carries it on the 6th, and every valuation you computed on the 4th and 5th is now wrong. If holdings are a mutable running total you have no way back. Undoing it means knowing what the balance was before a transaction that hadn't been applied yet, and your schema cannot answer that question.

So transactions are the ledger, holdings are derived, and snapshots are only a cache:

// A backdated transaction invalidates every snapshot from its date forward.
await tx.snapshot.deleteMany({
  where: { folioId, asOf: { gte: txn.tradeDate } },
})
await enqueue('recompute-holdings', { folioId, from: txn.tradeDate })

Recomputation is a job, never a request. Same-day ordering is defined explicitly, purchases before redemptions, then trade time, then transaction id as a tiebreaker, because "the order they arrived in" is not deterministic and two runs that disagree is worse than either one being wrong.

Three things about this that I only learned by getting them wrong.

Two recomputes for the same folio must not run concurrently. Obvious in hindsight. We now take a Postgres advisory lock keyed on the folio id at the top of the job, and a second worker that can't get it re-queues with a short delay rather than waiting on the lock, because holding a connection open to wait is how you find out your pool is smaller than you thought.

Bulk files cause recompute storms. One backdated file with a few thousand transactions across several hundred folios enqueues several hundred recomputes, most of them redundant because the same folio appears eleven times in the file. The import now collects the earliest affected date per folio across the whole batch and enqueues once per folio at the end. Obvious once you've watched a queue depth chart go vertical at 2am.

And statements have to be reproducible. This is the one that genuinely surprised me. An advisor generates a portfolio statement on the 5th and emails the PDF to a client. A backdated transaction lands on the 6th. Regenerate the same statement for the same period and it now shows different numbers, correctly. But the client is holding the first PDF. If you cannot reproduce exactly what you sent, you cannot have that conversation at all. So every generated statement stores the ledger version it was computed from, and regenerating an old statement replays the ledger as it stood at that point rather than as it stands now.

Cost basis is where all this pays for itself. FIFO across a folio with backdated entries and fractional units is not something you can maintain incrementally with any confidence. Derived from an ordered ledger, it's a fold.

Breaks belong on a screen

For a long time reconciliation was an alert. A job compared our computed holdings against the registrar's statement and emailed the tech team when they disagreed.

That's backwards. Breaks aren't exceptions in this domain, they're routine. A transaction the RTA has and we don't. A dividend reinvestment processed a day differently. A unit count off in the fourth decimal from a rounding mismatch. Some need an engineer. Most need an operations person who knows the AMC.

So we made it a screen. A daily run produces break records with a type, both values, the difference and a status, and operations works the queue: acknowledge, annotate, resolve, escalate. The count of open breaks is a number somebody actually owns.

Two things came out of that which I didn't expect. Ops stopped raising tickets for routine breaks because they could simply handle them, so the ones that reached me were genuinely mine. And the break log turned out to be the best bug report I've ever had. The same break type against the same AMC every month is an integration defect, and that pattern is invisible when it arrives one email at a time.

As for the four hundred rupees: valuation no longer runs at a fixed time. It runs when the feed for a scheme's date is complete, and a scheme whose NAV hasn't landed is marked stale in the UI rather than silently carried forward at yesterday's price. Advisors would rather see "awaiting NAV" than a number they'll have to explain later.

Corporate actions deserve their own piece. Splits, bonuses and dividend reinvestments each break a naive holdings model in a slightly different way, and that's a longer story.