Ten retries must not create ten rows: idempotent SQL writes
Solve duplicate records with stable identity, uniqueness constraints, UPSERT behavior and safe retry boundaries.
Duplicates are often an identity problem
A client may time out after the server has committed the record, then retry the same request. Disabling a button helps usability but is not a correctness guarantee. Start by defining the identity of an event: for imported reviews, prefer a stable source identifier; otherwise consider a carefully designed combination of normalized content, author and date. Hashing the text alone can wrongly merge independent comments by different people.
Database constraints form the final boundary
A UNIQUE index or primary key prevents concurrent requests from creating duplicate rows for the same identity. SQLite's INSERT ... ON CONFLICT syntax makes conflict handling explicit. In this example, an existing source event is updated instead of duplicated. The chosen key must genuinely represent one logical event rather than just a convenient field.
CREATE TABLE import_events (
source_id TEXT PRIMARY KEY,
status TEXT NOT NULL,
updated_at TEXT NOT NULL
);
INSERT INTO import_events(source_id, status, updated_at)
VALUES ('src-138', 'processed', '2026-11-16')
ON CONFLICT(source_id) DO UPDATE SET
status=excluded.status, updated_at=excluded.updated_at;UPSERT does not make every side effect idempotent
Writing one database row does not automatically deduplicate email delivery, R2 uploads or outbound API calls. Side effects require their own operation identifier, a transactional outbox pattern or checks against previously completed work. Operational logs and business records should be linked so that retries do not inflate successful-event counts.
Historical cleanup requires referential safety
Before consolidating old duplicates, produce an audit showing why candidate records represent the same event. Deleting only a parent row may damage linked images, comments and publication state. Use backups, verified matching, atomic updates and post-change checks; keep ambiguous cases out of automatic merges.
Comments
0 real commentsNo real reader comments yet.
✎ Write a comment