Summary
The importer's documented idempotency key (household_id, type=waste_collection, config.address) exists only in app code — there is no DB unique index. Two concurrent runs (or a run racing the UI create) both see "no existing source" and both insert → duplicate sources, topics, notifications. The insert→createForumTopic→update(topicId) sequence is also not atomic: if updateSource fails after the topic is created, the next run creates a second topic.
Evidence
packages/data-ops/scripts/import-waste-schedule/db-deps.ts:26-48 — findExistingSource matches config->>'address' then inserts; .limit(1) with no ORDER BY.…/importer.ts:61-110 — non-transactional multi-step.packages/data-ops/src/drizzle/schema.ts:81-101 — no unique index on (household_id, type, (config->>'address')).
Proposed failing tests (TDD)
db-deps.db.test.ts (PGLite) — a partial unique index rejects a second waste_collection row with the same (household, address).importer.test.ts — a store mutated between find and insert does not produce a duplicate (retries as update / surfaces conflict).
Fix direction
Add the partial unique index and use ON CONFLICT … DO UPDATE; wrap the topic flow so a failed update is retried/compensated.
Summary
The importer's documented idempotency key
(household_id, type=waste_collection, config.address)exists only in app code — there is no DB unique index. Two concurrent runs (or a run racing the UI create) both see "no existing source" and both insert → duplicate sources, topics, notifications. The insert→createForumTopic→update(topicId) sequence is also not atomic: ifupdateSourcefails after the topic is created, the next run creates a second topic.Evidence
packages/data-ops/scripts/import-waste-schedule/db-deps.ts:26-48—findExistingSourcematchesconfig->>'address'then inserts;.limit(1)with no ORDER BY.…/importer.ts:61-110— non-transactional multi-step.packages/data-ops/src/drizzle/schema.ts:81-101— no unique index on(household_id, type, (config->>'address')).Proposed failing tests (TDD)
db-deps.db.test.ts(PGLite) — a partial unique index rejects a secondwaste_collectionrow with the same(household, address).importer.test.ts— a store mutated between find and insert does not produce a duplicate (retries as update / surfaces conflict).Fix direction
Add the partial unique index and use
ON CONFLICT … DO UPDATE; wrap the topic flow so a failed update is retried/compensated.