Defining Ingestion Identity Beyond Raw Strings
The same company landed twice because the name was spelled two ways. A content hash over a canonical tuple closes that duplication gap.
TL;DR
Duplicate company rows appeared because the pipeline relied on raw text and sender references instead of semantic identity. The fix normalizes company names and builds a SHA-256 hash from core values, enforced by a database unique constraint with upsert logic. This stops same-source duplicates reliably but does not handle cross-source entity merging, which needs separate resolution.
Two rows for the same company
During the first load into the DemandScope database, one company name appeared twice in slightly different forms: "PT. Contoh Sentosa" and "PT Contoh Sentosa". Both referred to the same entity, yet the system recorded them as two separate facts. The weak point was plain: the pipeline trusted that identical text is always written the same way.
My first guess was to rely on the sender's raw reference column (source_ref) for uniqueness. That assumption collapsed the moment the same source re-sent an observation with a minor spelling variation or a different row order. A raw reference carries pointer identity, not the semantic identity of the fact itself. Duplicate rows slipped through silently, and every analytic number on top of them rotted.
A canonical identity built from values
The fix sits in two new functions in app/domain/signals.py. First, normalize_company: the name is uppercased, non-alphanumeric characters become spaces, legal-form prefixes (PT, CV, PKPK) are stripped repeatedly until none remain, and repeated spaces collapse. The untouched original is kept in a separate column company_name_raw, so no information is lost.
Second, content_hash_for assembles a canonical tuple and hashes it. Python provides the standard interface: the hashlib module with SHA-256 [5]. The result is a 64-character hex string that serves as the observation's identity:
canonical = "|".join([
source_id,
external_ref.strip(),
company_name_normalized.strip(),
signal_type,
event_date.isoformat() if event_date else "",
])
content_hash = hashlib.sha256(canonical.encode("utf-8")).hexdigest()Two observations with equal values always produce the same hash, regardless of arrival order. A small change in a meaningful value (a different company, a different event date) automatically produces a different identity. The details column stores the rest verbatim as JSONB, the signal type is locked through a SignalType StrEnum, and the ingest_runs table records every pipeline execution: how many pages were read, how many items were seen, how many were genuinely new.
The normalization is deliberately forgiving. Legal-form prefixes are removed repeatedly, so nested cases like "PT. CV Contoh Sentosa" reduce to the same key as "CV Contoh Sentosa". Non-alphanumeric separators count as spaces, so dots, dashes, and commas lose their power to distinguish. What survives is what matters: the event date stays inside the tuple, because rulings on different dates are genuinely different facts.
Enforced in the database, not in good intentions
A canonical identity only helps if violating it is impossible. Migration 0002_signals_ingest_runs.py adds UniqueConstraint("source_id", "content_hash") to the signals table. In PostgreSQL, a composite UNIQUE constraint guarantees the combination is unique across the table while each column may repeat; the constraint automatically creates a unique B-tree index [1].
The write path uses INSERT with an ON CONFLICT clause: the documented route for taking an alternative action instead of letting a uniqueness-violation error escape [2]. A conflict means the observation already exists; the old row is updated so the last-seen time advances, or left unchanged. The RETURNING clause then reports the rows actually inserted or updated. This is where the created, updated, and unchanged statuses are born honestly, not guessed by the sender.
The whole chain is pinned by tests. Sixteen tests in this commit lock normalization behavior (repeated prefixes, punctuation, spacing), hash stability for identical input, and the model-level constraints. Without them, a one-character edit to the normalization function could silently double rows, and the leak would surface only when a monthly report is already wrong.
Not a substitute for entity resolution
One boundary needs stating. What a content hash solves is deterministic deduplication against a source that provides a stable per-fact reference. Merging the same entity across many different sources is a different job: OpenSanctions explains that hundreds of source profiles are fused into one stable canonical ID [3], with an extremely low tolerance for wrong matches — entities with the same name and nationality stay apart [4].
The decision I took from this commit: do not shift responsibility. The hash closes the duplication problem inside one source; cross-source matching stays a separate topic that needs blocking, scoring, and human review. Treating both as the same thing is the fastest way to produce data that looks clean and merges wrongly.