Reference · /learn/
Issue 09A ledger that refused its own author, a process that would not die, and a page that shipped broken and returned 200 the whole time.
Issue 08 ended with the numbers proved and published, and one claim underneath them: five holdings, 20 % each, over whatever 365-day window happened to be downloaded. No shares, no purchase, no dates — an assertion typed into Python. This letter is what happened when that assertion was replaced by a record of the actual trades, and everything else was worked out from it.
Where this is going
A position that is a question rather than a stored number, an append-only rule that caught me ninety seconds after I wrote it and then turned out to have a hole in it, purchase dates taken from racing-car numbers and made to meet a trading calendar, five holdings collapsed into the three currency risks they actually are, a reconciliation that only works once you stop measuring in percentages, a one-day lag whose absence no total would ever reveal, and a deployment that was correct, blank, and returning 200 all at once.
The plan for this version was written in dependency order: backend, persistence, authentication, buy dates, ledger, with two “product ideas” listed underneath. That is the right way to record decisions and the wrong way to build them. The two cheapest items were at the bottom, and the stated dependency between the backend and the database was not real — a ledger is Python and SQL, and can be built and tested with no HTTP anywhere near it.
So I built the ledger first, in Postgres, with hand-written SQL. Postgres because it was already running for the guestbook. Hand-written SQL because positions are a query is the idea worth learning here, and a library that writes the query for you hides exactly the part worth reading.
Laying the fire
01ledger
schema.sql · ledger.py
Nothing stores a position. Ask what I held in June and the ledger adds up the buys, subtracts the sells, and tells you.
Four tables, the constraints, and the append-only triggers. The shape is the part worth keeping: units held of a ticker on a date are the buys minus the sells dated on or before that day, computed when asked. A figure for last June is recomputed from the trades that existed in June, so entering a backdated trade legitimately changes history. That is the ledger working, not a bug.
Trades on or before that date
Therefore held
Drag it across 6 April and watch Adidas halve and Richemont jump. Equity legs only: the cash legs are real in the database, but this page has no cash balances it could show without inventing them.
The shortcut is to treat cash as a number on the side. Do that and money vanishes:
sell on Monday, buy on Thursday, and for three days the portfolio is quietly worth
less than it is. So cash is CASH.GBP, CASH.EUR and the
rest, and buying a share debits it implicitly. Nobody types a cash balance, so
nobody can type one wrongly. An FX conversion is two rows sharing a
leg_group: withdrawing sterling and depositing euros is one act, and
neither leg means anything alone.
Equities are BUY and SELL; cash is DEPOSIT
and WITHDRAW. One word for both would hide the difference between an
investment decision and a funding one. The pairing is enforced by a composite
foreign key, so a row cannot claim that Adidas is cash.
You cannot buy 0.37 of a share. Rounding five purchases down leaves
£193.05 in sterling — real money, and a total that ignores it is
wrong. It has a useful consequence too: because units are whole numbers,
units × price has no rounding left in it, so every foreign cash
balance lands on exactly zero and the leftover can only ever appear in the base
currency.
Money and units are stored as exact decimals, never as floating point. A ledger that does not add up exactly is not a ledger. The returns maths stays on floats — that is measurement rather than accounting — and one named function marks where you cross from one to the other.
Sixty-eight checks, in two layers. The first builds a tiny ledger by hand inside a
transaction that is always rolled back, so exact assertions run against the real
constraints and the real triggers. The second seeds the sample portfolio and checks
invariants. Then I broke the date boundary on purpose — <= to
< — and seven checks went red before I put it back.
02the rule
schema.sql · the append-only triggers
A ledger you can edit is not a ledger. So the rule lives in the database, not in everyone remembering.
Rows can be added, never changed or deleted. Its first test was not one I planned.
The script that fills the ledger cleared the table first, with
DELETE. The database refused it:
“transactions is append-only: DELETE rejected on row 1. Post a reversing entry instead.”
The rule working perfectly, on the person who had just written it.
The more interesting part came next, and nothing automatic found it. It came from asking what else could empty a table.
DELETE removes rows one at a time, so a per-row guard sees each one
go. TRUNCATE discards the whole contents in a single operation and
never looks at individual rows — so the guard never fires. One statement, and
a table advertised as append-only empties without complaint.
No rule at all is honest — you know to be careful. A rule with a gap reads
as protection while providing none, and everyone downstream relies on it. The
second guard exists for that reason and is tested, not because
TRUNCATE is likely but because believing you are protected is
what makes the gap dangerous.
Reseeding still has to break the rule, so it does so out loud: a function that disables both guards by name, says why, and puts them back. Breaking a rule deliberately and visibly is fine. A rule that can be broken without anyone noticing is not.
Well alight
03dates
seed_portfolio.py
An arbitrary choice can be a memorable one. It still has to meet a trading calendar.
The portfolio needed a purchase date, and Alice's idea was to take it from the driver's car number. George Russell is 63, which reads as the 6th of March. It works, and it needed a rule, because otherwise the joke only survives by luck.
#63 Russell -> 2026-03-06 (Friday) a trading day #44 Hamilton -> 2026-04-04 (Saturday) markets shut #12 Antonelli -> 2026-02-01 (Sunday) markets shut
So the number proposes a date and the calendar disposes: a trade lands on the first day the market was actually open on or after it. Forward, never back — a purchase cannot predate the instruction to make it. Numbers that do not read as a date at all raise an error rather than being nudged to something plausible.
The mid-period switch uses 44 deliberately, because it lands on a Saturday and rolls to Monday 6 April. The rule is exercised by the sample data rather than sitting there untested.
A trade is priced at that day's exchange-rate snapshot, because that is the only rate this project has: the FX source publishes one figure a day, and real fills happen at moments nobody here can see. The rate is stored on the trade row, so the figure stays reproducible after the source has moved on — and the approximation is stated rather than assumed away.
04exposure
currency_exposure.py
The dashboard flagged five holdings one at a time. The portfolio does not hold five currency risks. It holds three.
And the euro was 60 % of the money. Three euro holdings each down 1.06 % is not three findings; it is one finding reported three times, and the thing that makes it matter — the concentration — is invisible in that view.
The −1.5 % limit tests how far a currency moved. It could instead have tested what that movement cost the portfolio, which sounds more useful.
I did not make it, for two reasons. It would have changed what a documented number means while leaving it looking identical, so anyone comparing this month's report to last month's would be comparing different things without being told. And nothing would ever have breached again: the largest contribution was −0.64 % against a −1.5 % limit. A threshold that cannot fire is decoration.
A forward struck on day one fixes the rate on the money that was there then, so it removes the currency return and nothing else. A hedge adjusted as the position moves also covers the currency movement on the gain the asset made along the way.
The gap between those two figures is exactly the cross term, which §05 derives. That the number is available at all is the clearest payoff yet from a V1 decision to report it separately rather than fold it into the currency figure.
ccy forward_50% rebalanced_50% forward_100% rebalanced_100% EUR 36,198 36,385 72,397 72,770 CHF 17,571 24,642 35,143 49,283 USD 20,939 20,152 41,878 40,303
Richemont is up a lot, so its cross term is large and the adjusted hedge is worth £14k more. PVH fell, so its cross term is positive and the adjusted hedge is worth £1.5k less — hedging a currency that rose shows as a cost, because the hedge gives up the upside too.
The cost of the hedge itself is not netted off, and the page says so rather than burying it. Pricing a forward needs the interest-rate difference between two currencies, and the FX source publishes exchange rates only. Not estimated, not faked.
Banked for the night
05the centre
valuation.py
Written down in advance as the most dangerous decision left — the one most likely to produce a number that is wrong and looks convincing.
It turned out to be real, and narrower than I feared. A return has two causes, the share and the currency, and they multiply rather than add. Over one period that is fine. Over 124 days chained together, does the split still hold?
For one holding, yes — exactly. Chaining daily returns multiplies out so that the share's chain times the currency's chain equals the total's chain. The identity survives any number of days with nothing given up.
For the whole portfolio, no. Each day's figure is a weighted blend across five holdings, and the average of a product is not the product of the averages. This is a real problem with an academic literature of correction algorithms attached to it.
The way out is not an algorithm. It is a change of unit.
Percentages compound, so they do not add. Money adds. Work out, each day and for each holding, how many pounds came from the share moving and how many from the rate moving — and those two, plus the part belonging to neither, sum to the day's total exactly. By expansion, not by approximation.
local = u (p1 - p0) r0 the share moved, at yesterday's rate fx = u p0 (r1 - r0) the rate moved, on yesterday's value cross = u (p1 - p0) (r1 - r0) the part belonging to neither total = u (p1 r1 - p0 r0) local + fx + cross = total exactly - no scaling factor
The pounds reconcile at every setting, on both tabs.
Add 124 days and five holdings and it still reconciles, because only addition happened. Percentages are then quoted as a share of the opening value and labelled as contributions, which is what they are.
| Holding | Units | Bought | Return since | P&L | Share now |
|---|---|---|---|---|---|
ADS.DE | 16,470 | 6 Mar | +6.41 % | £31,035 | 9.5 % |
PUM.DE | 205,983 | 6 Mar | +10.38 % | £415,230 | 19.8 % |
CFR.SW | 43,305 | 6 Mar | +27.70 % | £1,661,023 | 33.9 % |
PVH | 82,211 | 6 Mar | +18.81 % | £752,256 | 21.3 % |
MBG.DE | 90,142 | 6 Mar | −13.75 % | −£549,891 | 15.5 % |
| Portfolio | +11.55 % | £2,309,653 | 100.0 % |
opening 20,000,000.02 -> closing 22,309,652.69
local 2,835,895.49
fx -522,013.23
cross -4,229.60
sum 2,309,652.66 total 2,309,652.67
components reconcile to the total
Weights are now derived, and they drift. Adidas fell to 9.5 % of the book, Richemont grew to 33.9 %. Nobody typed those. The “positions are start-of-period” caveat carried since V1 is dead.
The return is labelled rather than assumed. With one subscription and no money in or out since, the time-weighted and money-weighted returns are the same number. They diverge the moment the client pays more in or takes some out, and the page says which it is showing rather than claiming a distinction the data has not yet earned.
The lesson generalises past this project. When a quantity refuses to add up, it is sometimes because the arithmetic is wrong and sometimes because it is being measured in the wrong unit. Reaching for a correction factor before working out which is how a fudge gets built into a foundation.
06timing
test_valuation.py
Get this backwards and every total still reconciles. The numbers are simply slightly wrong, every day, in a way no sum will reveal.
Trades execute at the closing price. So the shares that earned a day's movement are the ones held at yesterday's close — today's purchase was struck at the very price used to value it, and cannot have earned the move that got there.
I removed the lag on purpose to see what broke, and exactly two checks failed — both of them the timing ones.
The precision is the useful part. A suite going uniformly red says something is wrong somewhere. Two failures in the two places that should fail says the tests are pointed at the thing you meant.
A day on which the price goes 100 → 104. Adidas was held from yesterday at 5,000 units and another 1,000 were bought today, at today’s close of 104. Puma sat at 8,000 units and did nothing.
Adidas shows +6.41 % and £31,035. Those look inconsistent and are both right. The percentage is what one share did since it was bought. The money is what the holding actually earned — and half of it was sold in April, so half stopped participating. They answer different questions, so the page says so in words rather than leaving the reader to work out which is which.
07shipping
app.py · deploy.sh
Correct data, blank page, and HTTP 200 the whole time.
Why was the portfolio page frozen when the Issue 08 panel is live? Because one asks a route on the server every time somebody opens it, and the other read a file frozen at deploy time. The mechanism already existed — it had just never been pointed at this page.
The web service ran on the system Python, which has the web libraries but not pandas, numpy or yfinance — and the whole calculation core needs all three. So the service had to move to a private library folder with both sets.
Two safeguards, because that one process serves the entire website. The portfolio code is imported on the first request rather than at startup, so a broken import becomes an error on one route instead of a site that will not start. And I ran the complete app under the new interpreter on a spare port first, checking every existing route, before anything changed.
The restart itself was refused and handed to Alice to run, which is the right outcome for a command that restarts the service behind a whole website. Five routes went up: the whole portfolio computed on request, what was held on a given date, the ledger itself, and a health check that says which half is broken if either is.
A test server started earlier survived its kill. The next one could
not bind to the port, so the checks silently hit the old process —
and passed.
It was visible only because a call that should have been cold reported
cached: true, age 290s. A number that made no sense for a process
seconds old. Nothing failed; something was merely odd, and the odd thing was the
only evidence.
Then the dashboard was rebuilt to show the new figures, tested, and deployed. It was broken for real visitors.
A running program loads its code once and keeps it in memory. The server had been restarted before the newest section of the calculation was written, so it went on handing out the older shape of data — perfectly correct, just missing a block the new page assumed would be there.
The page hit the gap and stopped. The HTML downloaded fine, the status code was 200, and the screen was empty.
I caught it about two minutes later by rendering the deployed page against the live data before calling the work finished. Not by a test — every test passed, because they ran against a document that did have the block.
A page must never depend on a service having been restarted. “Remember to restart” is not a fix; it is a hope.
The page now treats every section it did not write itself as optional and draws whatever arrived. A missing block means one section is skipped and everything else still shows. There is a test that deletes those blocks and checks the page survives.
On the new basis, two currencies breach the −1.5 % threshold — the franc at −4.59 % and the dollar at −1.81 %, together 55 % of the book. Nothing breached on the old basis. The period is five and a half months rather than a year, and it is a different story. The flagging code has now fired on real data rather than only on test data.
A test caught something else here worth recording: the currency weights did not add to 100 %. The cause was real — cash had no row. The leftover sterling was in the portfolio total but not in the currency table, and an earlier printout had rounded the gap away. I fixed the table, not the test.
Three of six faults were caught by something automatic. Three were caught only because I looked at the thing itself.
The session ended with 299 checks across five suites, all passing. Four times I broke a working piece of code on purpose to confirm the tests would notice.
| Suite | Checks | Broken on purpose to prove it bites |
|---|---|---|
test_flags.py | 39 | — inherited from V1 |
test_currency.py | 50 | dropped the weight from the contribution → 3 failed |
test_ledger.py | 68 | changed <= to < on the date boundary → 7 failed |
test_valuation.py | 66 | removed the one-day lag on units → exactly 2 failed |
test_dashboard.js | 76 | deleted blocks from a document → regression test added |
The honest tally:
| Fault | Found by |
|---|---|
Seed script used DELETE on an append-only table | a database rule, automatically |
TRUNCATE bypassed that rule entirely | reasoning about what else could empty a table |
A test server survived its kill; checks hit a stale process | a cache age that made no sense |
| Page deployed expecting data the server was not serving | rendering the deployed page against live data |
| Currency weights did not add to 100 % (cash had no row) | a test |
| Two numbers split from their units across a line break | a test |
Tests are very good at confirming that what you thought of still works. They are no help at all with what you did not think of — and every one of the three inspection-caught faults above was in that second category. A test cannot check a case its author never imagined, which is why 299 of them still let a blank page reach production. None of which is an argument for fewer tests — it is an argument for making inspection a separate step from running them.
Worth saying plainly: there is no continuous integration here. All 299 run by hand, which means they run when I remember to run them.