How to prevent duplicate database records permanently
Repeatedly processing the same external source should update a single record, not multiply it.
Define record identity first
Re-reading a source is normal. Creating another copy of the same unchanged record on every run is not. Use a stable source identifier when one exists, or document a defensible composite key and normalization rules.
Unique indexes protect concurrent workers
Checking for an existing record in application code is not sufficient when two workers act simultaneously. A database unique constraint forms the final integrity barrier. Upsert operations can then safely update a known record instead of inserting duplicates.
CREATE UNIQUE INDEX IF NOT EXISTS idx_reviews_source
ON comments (source, source_review_id);
INSERT INTO comments (source, source_review_id, body)
VALUES ('google', ?, ?)
ON CONFLICT(source, source_review_id) DO UPDATE SET
body = excluded.body;Make processing outcomes understandable
Records discovered, records discarded and distinct records stored are different metrics. Users should not be asked to interpret processing attempts as business outcomes. Keep a concise reason for each update or rejection.
Handle deletion carefully
A missing result might mean a temporary source outage, not an intentional removal. Track provenance, timestamps and publication state so data can be reviewed before permanent deletion.
Identity rules prevent multiplication
Running the same review collector five times should not turn ten distinct reviews into fifty records. Define record identity first. Use a stable source ID when available, or a carefully justified composite key involving source, property, author, timestamp and normalized content. Identical text is not always proof that two events are the same. Document normalization and matching decisions, and do not merge ambiguous cases automatically. Data quality is broader than deleting rows that happen to look similar.
The database must protect concurrent writes
Two workers can both check for a record, see that it does not exist, and insert duplicates simultaneously. Application-level checks are insufficient without a UNIQUE constraint or index. Where appropriate, use INSERT ... ON CONFLICT to upsert the one canonical record. A stable idempotency key makes retries predictable after network timeouts. These constraints keep identity consistent even when multiple agents or scheduled tasks process the same source concurrently.
Synchronization and deletion pitfalls
A review temporarily missing from an external source is not necessarily deleted. Pagination limits, verification challenges and access failures can produce incomplete results. Track last-seen timestamps, source state and publication status instead of deleting immediately. Verify the evidence and relevant retention requirements before irreversible changes. Show discovered comments, distinct stored comments and rejected records as separate metrics in the admin UI. The number of scraping attempts is not evidence of higher content quality.
Comments
0 real commentsHiding duplicate rows in the UI is insufficient. A unique database constraint and an idempotent upsert need to be designed together.
Illustrative reader note and date; not a real comment.✎ Write a comment