Delivery Dedup Ledger & Straggler Handling (Part 2)

Building on the Improved Delivery Coverage update from April, this release hardens deduplication and ensures late-arriving signals always reach customers.

Content-based deduplication

Some upstream vendors regenerate a random signal_id on every data drop, making standard ON CONFLICT (signal_id) dedup ineffective — a re-dropped batch would insert as entirely new rows.

We now derive a deterministic content-based ID for affected signal types:

sha256(signal_type | entity_key | detected_date | subtype | canonical(data))

This means:

• Verbatim re-drops of the same data collapse automatically (no duplicates)
• Genuinely distinct same-day/same-entity signals remain separate (the data fingerprint differs)
• Existing rows retain their original IDs — this is forward-only

Affected signal types: website-intelligence (others will be added as needed).

Straggler guarantee

Signals that arrive after a delivery window closes (due to LLM retries, vendor delays, or processing failures) are now tracked in a per-entity ledger. Stragglers are guaranteed to appear in the next delivery cycle rather than being silently dropped.

The ledger tracks:

• Which entities have pending undelivered signals
• How long each signal has been waiting
• Whether the straggler was included in the subsequent delivery

Customer impact

No action required. Customers who already deduplicate on signal_id will see no change in behavior. Customers who don't deduplicate will see fewer accidental duplicates from vendor re-drops.

Delivery format, folder naming, and schema are all unchanged.