A repeated event ID is not enough to call a request a duplicate A retry arrives with an event ID ...A repeated event ID is not enough to call a request a duplicate A retry arrives with an event ID ...
The network for creativity
Join 1.25M professional creatives like you
Connect with clients, get discovered, and run your business 100% commission-free
Creatives on Contra have earned over $150M and we are just getting started
Back to feedPost
Sebastian's avatar
Sebastian Muñoz
• Sep 7
A repeated event ID is not enough to call a request a duplicate
A retry arrives with an event ID already in the database. Should the importer ignore it? I would compare its contents first. The same ID can carry a changed request, and silently dropping that request loses information worth investigating.
Here is the smallest SQLite example of the problem:
CREATE TABLE requests (id TEXT PRIMARY KEY, body TEXT NOT NULL); INSERT OR IGNORE INTO requests VALUES ('same-id', 'Blue notebook'); INSERT OR IGNORE INTO requests VALUES ('same-id', 'Red notebook'); SELECT * FROM requests; -- same-id | Blue notebook
The unique key prevented a second row. It did not preserve the attempted change. I ran this example alongside a Python event importer to examine the difference. This is a self-initiated experiment with synthetic data, not a client incident.
CHOOSE WHAT COUNTS AS THE SAME REQUEST
The importer accepts three string fields: event_id, email and request. It validates the input, then compares a SHA-256 fingerprint with the one stored for that ID. The fingerprint covers all three fields, serialized with:
json.dumps(event, sort_keys=True, separators=(",", ":"), ensure_ascii=False)
Sorting keys makes JSON object order irrelevant. It does not make every business-equivalent request identical. In a separate test, reversing the keys produced a duplicate; adding a trailing space to the request produced a conflict. Giving the unchanged request a new event ID created another event.
Those results define this importer's identity rule. It recognizes retries under a stable ID, not duplicate orders across different IDs. I would settle that distinction with the event producer before adding normalization: trimming or lowercasing a value can change what the application means by “same.”
KEEP THE DECISION AND THE QUEUED WORK TOGETHER
For a previously unseen valid ID, the importer writes an events row and a matching outbox row inside one BEGIN IMMEDIATE transaction. The outbox has a unique event_id, a foreign key to events, and a LOCAL_PENDING status. No worker sends it anywhere in this experiment.
For an existing ID, equal fingerprints return “duplicate” without another outbox row. Different fingerprints retain the original event and insert a conflict record for review. Repeating that same conflict does not add another record: the conflict table is unique on event_id and attempted_hash.
There is a limitation I would fix before calling this an audit trail: the conflict record stores the attempted hash and a reason, not the changed body. It proves a different fingerprint was observed, but a reviewer needs the original source to inspect the attempted change. A real intake system needs an explicit retention policy for that evidence.
SQLite documents that BEGIN IMMEDIATE starts a write transaction immediately and can fail with SQLITE_BUSY if another connection is already writing. This demo has no busy-retry policy or concurrent-load test. That would need separate work before using the pattern under contention.
READ THE SECOND PASS CAREFULLY
I replayed a fixture of 16 inputs twice on Python 3.14.5 and SQLite 3.50.4. The first pass returned 8 created, 3 duplicate, 2 conflict and 3 rejected. The second returned 0 created, 11 duplicate, 2 conflict and 3 rejected.
Eleven duplicates does not mean eleven duplicate rows. The eight originally new events became duplicates on replay, alongside the three original retries. After either pass, SQL counts were unchanged: 8 events, 8 outbox rows, 2 conflict records and 3 rejection records.
Queries also found zero repeated outbox IDs, zero events without an outbox row, and zero payload mismatches between those tables. These checks support the result for this fixture; they are not a general proof for arbitrary inputs.
BREAK THE TRANSACTION AT THE USEFUL POINT
The failure test deliberately raises an exception after inserting a new event but before inserting its outbox row. The exception handler rolls back. Both counts for that test ID remained zero, and the database counts matched the pre-failure snapshot.
A separate file-backed test closed and reopened the connection, then replayed a committed event. It still returned “duplicate.” That demonstrates the decision survives a connection restart. Neither test simulates a power failure or a process killed during a commit.
I would keep one further boundary explicit: a committed outbox row is pending work, not proof of delivery. A future worker could send successfully and crash before recording success. Handling that case needs a delivery and retry design, usually with a stable key the receiver can recognize. The local replay results say nothing about an external receiver.
My practical choice is to record changed requests as conflicts, then keep event acceptance and local queue creation in one transaction. The useful guarantee is narrow enough to test: replaying the accepted fixture adds no queued work, and the injected application failure leaves neither half of a new event behind.
SQLite references: https://www.sqlite.org/lang_conflict.html and https://www.sqlite.org/lang_transaction.html
Experiment note: synthetic example.com addresses; no network calls or external messages from the test harness. Article and code prepared with AI assistance; reported outputs were verified by executing the examples.
Back to feed
The network for creativity
Join 1.25M professional creatives like you
Connect with clients, get discovered, and run your business 100% commission-free
Creatives on Contra have earned over $150M and we are just getting started