Who Looked?
A client asks who opened their contract last month. One record can be changed without a trace, and doesn't count the bot reading it. The other can only be added to, and any change shows.
01Try it
A client asks who opened their contract last month. Two records answer. Add to them, then try to make one entry disappear and see which record gives it away.
Open the contract, let the bot read it, then try to hide an entry. Each log here is your own copy.
…
…
Real: the two logs, the database rules, the seals and the bot. September is made up for the lab; what you click is added for real. In real life Tidepool keeps the receipt, not the studio.
What happened every time
I tried hiding every entry in four different ways. Green means the careful record gave it away. Pink means it didn't.
What actually happened
The bot reads files nobody records
In Chapter 2 the bot read Tidepool's contract to answer other clients 3 times. A record that only counts people opening the file shows none of that.
One record can be quietly changed
Anyone with full access can delete an entry, and the list still looks complete. There's nothing to compare it with.
The app itself can only add
The app tried to delete the entry 40 times and the database said no every time. Only someone with full access could change it.
Caught 35 of 40
Someone with full access could make the change, but the seals gave it away 35 times out of 40, even when they redid all the seals, because Tidepool already had the last one.
What it missed: 5
It missed changes to entries made after the last report went out. Until someone outside holds the newest seal, the newest entries can still be rewritten. Send it often.
The code
What the two approaches do, in one sentence. The code itself is one click away.
The careless version writes down people downloading the file, in a table anyone with full access can edit. The careful one writes every read, the bot's too, links each entry to the one before, and only lets the app add.
Show the code anyway
// in the Harbor download handlerawait sql( "INSERT INTO access_log (at, actor, action) VALUES (now(), $1, 'opened')", [user.name],);// GRANT SELECT, INSERT ON log_careful TO audit_writer; -- no UPDATE, no DELETEawait withTx(async (q) => { await q("SET LOCAL ROLE audit_writer"); const [last] = await q("SELECT seq, hash FROM log_careful ORDER BY seq DESC LIMIT 1"); const seq = (last?.seq ?? 0) + 1, prev = last?.hash ?? GENESIS; const hash = sha256(prev + "\n" + JSON.stringify([seq, at, actor, action, detail])); await q("INSERT INTO log_careful VALUES ($1, $2, $3, $4, $5, $6, $7)", [seq, at, actor, action, detail, prev, hash]);});// the bot, after search, for every file it pulled infor (const doc of hits.map((h) => h.doc)) await logRead(doc, "Northwind bot", `to answer ${client}`);Still curious?
A few more questions people ask. Open what you need.
Can't someone just redo all the seals?
Yes. That's why the newest seal goes to the client with every report. Redo the seals and they no longer match the copy the client has. Anything newer than that copy isn't protected yet.
Why does the bot count?
Because when the bot reads a file to answer someone, that file can end up in front of them. Chapter 2 shows how. If nobody writes it down, nobody can find out later.
Does the seal hide anything?
No. Anyone allowed can still read the record. The seals don't hide it, they make any change show.
Is this what the rules ask for?
Security audits ask for records of who opened what, and for proof nobody can quietly change them. This is one way. It doesn't make anyone certified. The record itself is about people too, so it shouldn't be kept forever.
What do real systems use?
The same ideas: the app can only add, and the newest seal is kept somewhere the company itself can't change.
Who checks the result?
The same check as in the demo, run in code on every entry and every kind of change. My first test run had a bug: some changes didn't actually change anything. I fixed it and ran it again.
Not sure this could happen to you?
That's what the call is for. Tell me how your product works, and I'll tell you what applies to you and what I'd check first.