person v1 distinctID re-use problem

Problem

We have had issues with data quality after distinct ID deletion and reuse^1.

This a typical example scenario:

1. An event with distinct ID X causes a new person A to be created
2. Person A is deleted, which (among other things):
   1. Publishes a row update to Kafka with a synthetic version ID for the distinct ID record, and this update is later consumed by the ClickHouse Kafka engine and applied to the table,
   2. Deletes the person from Postgres, which cascades the deletion to posthog_persondistinctid via the foreign key relationship
3. Another event with distinct ID X causes a new person B to be created, and another distinct ID row update is published to Kafka with a 0 version for the same (team_id, distinct_id) which has no effect due to the deletion record having a higher version

The new distinct ID overrides approach for Persons-on-Events relies on data in the distinct ID Kafka topic being correctly versioned, and faulty versioning can cause problems with distinct ID overrides being missed, such as in this continuation from the previous example:

4. Person B person is merged into another person, which publishes another distinct ID row update with a version that is still liable to have no effect due to the deletion record having a higher version. This also results in an distinct ID override not being created and what would appear to be a bug with Persons-on-Events, but is actually an issue with the underlying distinct ID update data.

We should fix this before enabling person_distinct_id_override writes to avoid carrying over known issues to the new overrides table.

Solution

No more deleting posthog_persondistinctid rows out of Postgres during person deletion (except if that person deletion is also part of a wider whole team deletion.)

Instead, we'll keep the rows for distinct IDs we've seen as tombstone records to preserve the row version when synchronizing the data with between Postgres and ClickHouse.

The posthog_persondistinctid.person_id column will need to be made nullable to represent rows that were previously in use but are no longer associated with a person due to person deletion. This preserves the row version in case of reuse.

Insertion should succeed if there is no existing row for the (team_id, distinct_id) key, or an existing row exists but has a NULL person_id. Insertion should error if a row exists and the person_id is not NULL.

Follow Up Work

• #20523 can be removed/reverted once this issue is fixed.