Summary

• Update mutable sanction fields when a source entry already exists.
• Delete source entries missing from the latest non-empty snapshot.
• Perform the upserts and stale-row deletion in the same transaction.
• Preserve existing data when a fetch returns no entries.

Implementation

create_sanctions now treats each successful non-empty fetch as the complete snapshot for one source:

• ON CONFLICT (source, source_id) DO UPDATE refreshes target_type, names, positions, listed_on, and remarks.
• A source-scoped delete removes rows whose source_id is absent from the fetched snapshot.
• Both statements execute through the same psycopg connection context, so PostgreSQL commits or rolls them back together.
• Stable database metadata such as id and created_at is not overwritten.
• The existing empty-result guard prevents a failed or unexpectedly empty fetch from wiping stored data.

Validation

Validation was run on 23 July 2026 against a disposable PostgreSQL 17 instance loaded with the real repository schema, not only mocks:

• Ran the two focused PR Keep sanction snapshots in sync #4 unit tests successfully.
• Inserted an initial OFAC snapshot, then applied a second snapshot and verified every mutable field was updated through the real ON CONFLICT statement.
• Verified the existing row UUID and created_at were preserved.
• Verified a missing OFAC row was deleted and a new row was inserted.
• Verified an EU row remained untouched, confirming deletion is source-scoped.
• Passed a subsequent empty OFAC snapshot and confirmed the stored snapshot remained intact.
• Installed a temporary PostgreSQL trigger that deliberately failed the stale-row delete after the upsert. The call raised an error and both the field update and deletion rolled back, demonstrating transaction atomicity.
• Cleanly combined PR Keep sanction snapshots in sync #4 with PR Add Swiss SECO sanctions source #3, ran all six tests, and synchronized the live 7,053-entry active SECO snapshot.
• Corrupted one stored SECO row and added a stale sentinel, then resynchronized. The row was restored, the sentinel was removed, the count returned to 7,053, and stable database metadata was preserved.

These checks execute the actual PostgreSQL SQL through psycopg, including Python-list adaptation for ANY(%s), conflict updates, deletion, and rollback behavior. Together with the live full-size SECO resynchronization, they provide confidence that the snapshot replacement works as intended.

Relationship to PR #3

This branch was created independently from master and changes only worker/db.py plus its dedicated test file. PR #3 changes separate files. GitHub reports both PRs clean and mergeable, and a local three-way merge of both heads completed without conflicts, so they can merge in either order.