N
Naoto Yamabe
Guest
My fishing app gives points for verified catches. Points decide what you can see on the map. So the first question anyone sensible asks is: what stops the operator, me, from quietly editing the numbers?
The fashionable answer is a blockchain. I used thirteen lines of Python and a dozen lines of SQL instead.
Then, to write this article honestly, I built a verifier and pointed it at production. The hash chain passed. The token ledger passed. The points ledger failed in 21 places, and every one of them was in my own account.
This is the design, the audit, and the bug.
World Fishing Map records a catch like this: you photograph the fish, an AI proposes the species, you confirm, and the server scores it. There are two balances. Fish Tokens reward a trustworthy catch. Angler's Points reward using the app, and they set your tier.
Two decisions were made before any code. The points are never sold. They can never be transferred between users. Once both are true, a distributed ledger solves a problem I do not have. There is no counterparty who needs to verify a transfer without trusting me.
What I do need is narrower. If a row from three months ago changes, somebody should be able to tell. That is tamper evidence, and it is much cheaper than consensus.
Every catch stores two extra columns:
The chain is per user, not global. A global chain would serialise every write in the system behind one row. A per-user chain only serialises one angler against themselves, and an angler lands one fish at a time.
The more interesting decision is what to leave out. The hash covers who, when, where, which species, how many tokens, and the perceptual hash of the photo. It does not cover the status, the notes, or the size.
That is deliberate. A moderator rejecting a catch is a legitimate change. A user deleting their account withdraws their catches, and that is legitimate too. If status were inside the hash, every act of moderation would look like tampering, and I would learn to ignore the alarm. So the chain protects the claim, not the workflow around it.
The balances are a cache. The truth is two ledger tables, one for tokens and one for points. Each row has a delta, a reason, and the balance after it.
A trigger raises an exception before any UPDATE or DELETE. It fires for me too. There is no admin screen that edits a row, because there is no SQL that could.
So every correction is a new row. When a fraudulent catch is removed, the server appends a negative
One rule makes the clawback safe to run twice. The reversal amount is the net sum of deltas for that catch id. Rows that spend tokens, or exchange them, or adjust a balance, carry a NULL catch id. If a spending row pointed at a catch, a later clawback would count it as part of that catch's reward and reverse the wrong amount. That rule is written down in three places, because it is the kind of rule that gets broken by a helpful refactor.
I had never actually replayed any of this against production. For this article I wrote a read-only script that does three things. It walks each angler's catches in order and checks that every
The chain held: 68 of 68, across 8 chains, the longest 44 catches deep. Most of those rows are withdrawn, which is exactly the case the design was for. The status changed and the hashes did not care.
The script also found something I had not designed. The hash covers
The token ledger replayed cleanly: 54 of 54.
The points ledger did not.
Twenty-one rows have a
Here is what happened. In July I zeroed my test account with an admin adjustment of −17,804. Two days later I deleted its test catches. Each deletion ran the clawback, and each clawback tried to reverse points that were already gone.
The clawback has one line of kindness in it. A balance should not go below zero, so it writes
From that row on, the ledger and the balance tell different stories. Replay the rows and my account holds −7,372 points. The stored balance says 2.
Nobody was harmed. It is a test account, and the error is in my disfavour. But the promise in the schema comment, that a balance can be rebuilt from its rows, is false for one account out of 27. The trigger did its job so well that I cannot fix those 21 rows. They will be wrong for as long as the table exists, which is the point of the table.
The lesson is about the floor.
I have not shipped a fix. The obvious one, recording the clamped delta instead, breaks the idempotency rule above, because the net sum for that catch would never reach zero and the next run would claw again. Anything I change will apply to future rows only. That constraint is not an obstacle. It is the design telling me to think before I write.
It does not stop me. I own the database. I can drop the trigger, rewrite a row, recompute every hash after it, and restore the trigger. The chain makes that expensive and the ledger makes it conspicuous, but evidence only counts if a copy of the chain head lives somewhere I cannot reach. Today it does not. Publishing the latest hash per chain on a schedule is a small job, and it is the difference between tamper-evident and tamper-evident to someone other than me.
It does not prove the fish was real. That is a different system: EXIF against the device GPS, perceptual hashes against earlier photos, a land-or-water check. A perfect chain of fraudulent catches is still perfect.
And it does not verify itself. For months I believed both ledgers reconciled, because the design said they would. They did not, and no user, no test and no dashboard had noticed. The only thing that noticed was a script that tried.
Hash the claim, not the workflow. Leave status out so moderation cannot look like tampering.
Make the chain per user unless you truly need one global order.
Put the append-only rule in the database, not in the application, and have CI try to break it.
Treat every clamp, floor and default as a writer. If it changes a number, it needs a row.
Then write the verifier, and run it on the real thing. Mine is under two hundred lines. It was the first time anyone had checked.
World Fishing Map is a fishing log built by one developer in Japan, with a second contributor on the website. It is live on Google Play. The verifier from this article is in the repository as
worldfishingmap.com
The cover illustration was generated with Gemini. Every number and every line of code in the figures is real.
The fashionable answer is a blockchain. I used thirteen lines of Python and a dozen lines of SQL instead.
Then, to write this article honestly, I built a verifier and pointed it at production. The hash chain passed. The token ledger passed. The points ledger failed in 21 places, and every one of them was in my own account.
This is the design, the audit, and the bug.
Why not a chain of blocks
World Fishing Map records a catch like this: you photograph the fish, an AI proposes the species, you confirm, and the server scores it. There are two balances. Fish Tokens reward a trustworthy catch. Angler's Points reward using the app, and they set your tier.
Two decisions were made before any code. The points are never sold. They can never be transferred between users. Once both are true, a distributed ledger solves a problem I do not have. There is no counterparty who needs to verify a transfer without trusting me.
What I do need is narrower. If a row from three months ago changes, somebody should be able to tell. That is tamper evidence, and it is much cheaper than consensus.
One chain per angler
Every catch stores two extra columns:
prev_hash and integrity_hash. The hash covers the facts of the catch and the hash of that angler's previous catch.
The chain is per user, not global. A global chain would serialise every write in the system behind one row. A per-user chain only serialises one angler against themselves, and an angler lands one fish at a time.
The more interesting decision is what to leave out. The hash covers who, when, where, which species, how many tokens, and the perceptual hash of the photo. It does not cover the status, the notes, or the size.
That is deliberate. A moderator rejecting a catch is a legitimate change. A user deleting their account withdraws their catches, and that is legitimate too. If status were inside the hash, every act of moderation would look like tampering, and I would learn to ignore the alarm. So the chain protects the claim, not the workflow around it.
Two ledgers that refuse to be edited
The balances are a cache. The truth is two ledger tables, one for tokens and one for points. Each row has a delta, a reason, and the balance after it.
A trigger raises an exception before any UPDATE or DELETE. It fires for me too. There is no admin screen that edits a row, because there is no SQL that could.
So every correction is a new row. When a fraudulent catch is removed, the server appends a negative
fraud_reversal. When I adjust a balance by hand, that is an admin_adjustment, and the fact that I did it stays in the table forever.One rule makes the clawback safe to run twice. The reversal amount is the net sum of deltas for that catch id. Rows that spend tokens, or exchange them, or adjust a balance, carry a NULL catch id. If a spending row pointed at a catch, a later clawback would count it as part of that catch's reward and reverse the wrong amount. That rule is written down in three places, because it is the kind of rule that gets broken by a helpful refactor.
The audit
I had never actually replayed any of this against production. For this article I wrote a read-only script that does three things. It walks each angler's catches in order and checks that every
prev_hash equals the hash before it. It recomputes every SHA-256 from the stored columns. And it replays both ledgers, row by row.
The chain held: 68 of 68, across 8 chains, the longest 44 catches deep. Most of those rows are withdrawn, which is exactly the case the design was for. The status changed and the hashes did not care.
The script also found something I had not designed. The hash covers
caught_at.isoformat(), including the UTC offset the client sent. PostgreSQL's timestamptz does not keep that offset. To recompute a hash, my verifier has to guess it. All 68 matched at +00:00, because the app happens to send UTC. The server never enforced that. A canonical form that depends on something the database forgets is a bug waiting for a second client.The token ledger replayed cleanly: 54 of 54.
The points ledger did not.
A floor is a second writer
Twenty-one rows have a
balance_after that is not the previous balance plus their own delta. All 21 are fraud_reversal rows. All 21 are mine.
Here is what happened. In July I zeroed my test account with an admin adjustment of −17,804. Two days later I deleted its test catches. Each deletion ran the clawback, and each clawback tried to reverse points that were already gone.
The clawback has one line of kindness in it. A balance should not go below zero, so it writes
max(0, balance - clawed). But the ledger row still records the full negative delta. The row says −250. The balance moved by −84.From that row on, the ledger and the balance tell different stories. Replay the rows and my account holds −7,372 points. The stored balance says 2.
Nobody was harmed. It is a test account, and the error is in my disfavour. But the promise in the schema comment, that a balance can be rebuilt from its rows, is false for one account out of 27. The trigger did its job so well that I cannot fix those 21 rows. They will be wrong for as long as the table exists, which is the point of the table.
The lesson is about the floor.
max(0, …) looks like a guard. In an append-only design it is a second writer: it changes the balance without writing down what it changed. The floor belongs where the balance is displayed, not where it is recorded.I have not shipped a fix. The obvious one, recording the clamped delta instead, breaks the idempotency rule above, because the net sum for that catch would never reach zero and the next run would claw again. Anything I change will apply to future rows only. That constraint is not an obstacle. It is the design telling me to think before I write.
What this does not protect against
It does not stop me. I own the database. I can drop the trigger, rewrite a row, recompute every hash after it, and restore the trigger. The chain makes that expensive and the ledger makes it conspicuous, but evidence only counts if a copy of the chain head lives somewhere I cannot reach. Today it does not. Publishing the latest hash per chain on a schedule is a small job, and it is the difference between tamper-evident and tamper-evident to someone other than me.
It does not prove the fish was real. That is a different system: EXIF against the device GPS, perceptual hashes against earlier photos, a land-or-water check. A perfect chain of fraudulent catches is still perfect.
And it does not verify itself. For months I believed both ledgers reconciled, because the design said they would. They did not, and no user, no test and no dashboard had noticed. The only thing that noticed was a script that tried.
What I would tell you to steal
Hash the claim, not the workflow. Leave status out so moderation cannot look like tampering.
Make the chain per user unless you truly need one global order.
Put the append-only rule in the database, not in the application, and have CI try to break it.
Treat every clamp, floor and default as a writer. If it changes a number, it needs a row.
Then write the verifier, and run it on the real thing. Mine is under two hundred lines. It was the first time anyone had checked.
World Fishing Map is a fishing log built by one developer in Japan, with a second contributor on the website. It is live on Google Play. The verifier from this article is in the repository as
api/tools/verify_catch_chain.py, and it only ever opens a read-only transaction.
世界釣りマップ™ / World Fishing Map™
写真から始まる釣果を世界地図へ。AI魚種候補、潮・天候、場所とともに記録できます。Turn a catch photo into a trusted record with AI suggestions, conditions, and place.
The cover illustration was generated with Gemini. Every number and every line of code in the figures is real.