I run a small shop that sells digital files. Two platforms, one local checkout, and a spreadsheet
that I update by hand — which is fine until a payment webhook times out, gets retried, and the same
sale lands twice. You notice it on your best day, three weeks later, when the monthly total looks
better than your bank account does.
So the ledger I run now is one SQLite table. Two decisions do most of the work.
1. Let the database refuse duplicates
Every webhook carries an id from the platform. Make (platform, external_id) unique and the second
delivery fails at the constraint:
CREATE TABLE sales (
id INTEGER PRIMARY KEY AUTOINCREMENT,
platform TEXT NOT NULL,
external_id TEXT NOT NULL,
product TEXT NOT NULL,
amount TEXT NOT NULL,
currency TEXT NOT NULL,
occurred_at TEXT NOT NULL,
UNIQUE(platform, external_id)
);
Then the insert handler is boring, which is what you want:
def record(conn, platform, external_id, product, amount, currency="USD"):
try:
conn.execute(
"INSERT INTO sales (platform, external_id, product, amount, currency, occurred_at)"
" VALUES (?,?,?,?,?,datetime('now'))",
(platform, str(external_id), product, str(amount), currency),
)
conn.commit()
return True # baru
except sqlite3.IntegrityError:
return False # sudah pernah diterima — bukan error
Two details that matter more than the code:
Still return HTTP 200 on the duplicate. A sender that receives an error retries with backoff, so
"smart" duplicate handling that returns 409 turns one retry into ten. The duplicate is your problem,
not theirs.
Deduplicate on the platform's id, not on your own timestamp. Two genuine sales of the same
product by the same buyer can happen in the same minute; one delivery delivered twice cannot.
2. Never let a float touch an amount
0.1 + 0.2 is 0.30000000000000004. In a revenue report that becomes a discrepancy you cannot
explain to yourself at 11pm. Store money as Decimal and persist it as text:
from decimal import Decimal, ROUND_HALF_UP
def money(value) -> str:
return str(Decimal(str(value)).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP))
SQLite has no decimal type, so text is the honest choice. Sum with CAST(amount AS REAL) only for
display, or convert to Decimal in code when the number will be used for anything that matters.
3. Do not add currencies together
A $29 sale and a Rp30.000 sale are not 29,030 of anything. Group by currency:
SELECT platform, product, currency, COUNT(*), SUM(CAST(amount AS REAL)) AS gross
FROM sales
GROUP BY platform, product, currency
ORDER BY gross DESC;
If you must show one number, convert explicitly with a rate you recorded, and label the line with the
rate and the date. Silent conversion is how a report becomes a fiction.
4. Roll up on the sale's timestamp, not on insert time
received_at is when your server heard about it — useful for debugging, wrong for accounting. A sale
that happened at 23:58 on 31 January and arrived at 00:03 on 1 February belongs to January. Keep both
columns; group by occurred_at.
The whole thing
The lite version of the ledger is MIT-licensed, standard library only, ~100 lines:
github.com/shitpicious666-spec/sales-ledger-lite
— includes a selftest.py that proves the duplicate rejection and the monthly isolation against an
in-memory database.
I also ship a paid version with per-platform fee profiles (so the report shows net, not gross), an
HTTP receiver and a daily digest; it is linked from the README. The lite version is enough if all you
need is "do not count twice", and I would rather you read the code than take my word for it.
If you have solved refunds in an append-only ledger differently, I would like to hear it — right now
I record a negative row with its own id, which keeps the history honest but makes "revenue this
month" a sum instead of a constant.
Top comments (0)