:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
//! The stub-creation transaction: one transaction, five steps, each a
//! function below, called in order by [`create_person_stubs`]:
//!
//! 1. [`insert_or_revive_persons`] — multi-row person insert; a conflict
//! with a tombstoned row revives it, a conflict with a live row is left
//! for step 2.
//! 2. [`fetch_conflict_winners`] — fresh-snapshot fetch of the committed
//! rows behind live conflicts (concurrent winners).
//! 3. [`insert_distinct_id_mappings`] — multi-row distinct id insert;
//! tombstoned mappings revive, live conflicts keep the existing row.
//! 4. [`resolve_stub_outcomes`] — decide Committed/LostRace per stub and
//! undo the rows of stubs that lost their primary mapping.
//! 5. [`enqueue_mapping_rows`] — with outbox writes on, one publish outbox
//! row per mapping the transaction leaves live, at the version the row
//! carries.
//!
//! Cross-cutting invariants:
//! - Every multi-row statement binds arrays sorted by its conflict key —
//! row locks are taken in array order, so concurrent batches touching the
//! same keys in different orders would otherwise deadlock — and deduped by
//! that key, because ON CONFLICT DO UPDATE raises "cannot affect row a
//! second time" on same-command duplicates (the cardinality check fires
//! before the WHERE qual).
//! - Rows created in this transaction are invisible outside it until commit,
//! so step 4 can delete them without anything referencing them.
//! - Revived tombstones predate the transaction and must survive an undo:
//! they are re-tombstoned with another version bump, never deleted.
use std::collections::{HashMap, HashSet};
use chrono::{DateTime, Utc};
use sqlx::Row;
use uuid::Uuid;
use personhog_common::persons::person_uuid;
use crate::config::{IdentityTables, OutboxWrites};
use crate::pools::{IdentityPools, Lane};
use crate::storage::error::StorageResult;
use crate::storage::postgres::{person_columns, person_from_row};
use crate::storage::types::{Person, PersonStub, StubOutcome};
use personhog_common::query_tag;
type Tx<'a> = sqlx::Transaction<'a, sqlx::Postgres>;
/// A person resolved for one (team_id, uuid) conflict key during stub
/// creation, plus how this transaction obtained it — that determines the
/// stub's reported `created` flag and how an undo must treat the row.
struct ResolvedPerson {
person: Person,
/// Inserted (or revived) by this transaction, as opposed to fetched as a
/// concurrent winner's committed row.
created_by_us: bool,
/// The insert revived a tombstoned row rather than creating a fresh one.
/// An undo must re-tombstone it, never hard-delete it.
revived_tombstone: bool,
}
/// Persons keyed by their (team_id, uuid) conflict key.
type PersonsByKey = HashMap<(i64, Uuid), ResolvedPerson>;
/// A mapping row as this transaction left it: its owner and the version
/// the row now carries, which the publish outbox has to repeat exactly.
#[derive(Clone, Copy)]
struct WrittenMapping {
person_id: i64,
version: i64,
}
/// What the multi-row distinct id insert actually wrote.
struct MappingOutcome {
/// (team_id, distinct_id) → the mapping this transaction inserted or
/// revived. Keys that conflicted with a live mapping are absent — the
/// existing mapping stands.
written: HashMap<(i64, String), WrittenMapping>,
/// The subset of `written` that revived a tombstoned mapping; an undo
/// must re-tombstone these instead of deleting them.
revived: HashSet<(i64, String)>,
}
pub(super) async fn create_person_stubs(
pools: &IdentityPools,
tables: &IdentityTables,
stubs: &[PersonStub],
outbox_writes: OutboxWrites,
) -> StorageResult<Vec<StubOutcome>> {
if stubs.is_empty() {
return Ok(Vec::new());
}
// The primary distinct id derives the person uuid, so concurrent creates
// for one distinct id target the same (team_id, uuid) row.
let uuids: Vec<Uuid> = stubs
.iter()
.map(|s| person_uuid(s.team_id, &s.distinct_id))
.collect();
let team_ids: Vec<i32> = stubs.iter().map(|s| s.team_id as i32).collect();
let mut tx = pools.begin(Lane::Heavy).await?;
let mut persons = insert_or_revive_persons(&mut tx, tables, stubs, &team_ids, &uuids).await?;
fetch_conflict_winners(
&mut tx,
&tables.person,
stubs,
&team_ids,
&uuids,
&mut persons,
)
.await?;
let mapping =
insert_distinct_id_mappings(&mut tx, &tables.person_distinct_id, stubs, &uuids, &persons)
.await?;
let (outcomes, undone) =
resolve_stub_outcomes(&mut tx, tables, stubs, &uuids, &persons, &mapping).await?;
if outbox_writes == OutboxWrites::Enabled {
enqueue_mapping_rows(&mut tx, tables, &persons, &mapping, &undone).await?;
}
tx.commit().await?;
Ok(outcomes)
}
/// Step 1: multi-row stub insert. Concurrent creates for one distinct id
/// derive the same uuid, so exactly one insert wins per key; losers fetch
/// the committed winner in a fresh statement snapshot (step 2).
///
/// A conflict with a tombstoned row (same uuidv5 key, previously deleted) is
/// a revival: flip is_deleted, bump the version above the tombstone so
/// ClickHouse collapses toward the new incarnation, and reset properties.
/// A tombstone under a live lifecycle mark stays dead — its saga may still
/// re-drive a release over the revival; the caller sees LostRace and
/// retries. Conflicts with live rows fail the qual and return nothing.
///
/// A returned version of 0 means fresh insert, anything else means revival
/// (xmax can't be read back from a partitioned table).
async fn insert_or_revive_persons(
tx: &mut Tx<'_>,
tables: &IdentityTables,
stubs: &[PersonStub],
team_ids: &[i32],
uuids: &[Uuid],
) -> StorageResult<PersonsByKey> {
let person_table = &tables.person;
// Sorted and deduped by the (team_id, uuid) conflict key — see the
// module invariants.
let mut order: Vec<usize> = (0..stubs.len()).collect();
order.sort_by_key(|&i| (team_ids[i], uuids[i]));
order.dedup_by_key(|&mut i| (team_ids[i], uuids[i]));
let sorted_created_ats: Vec<DateTime<Utc>> =
order.iter().map(|&i| stubs[i].created_at).collect();
let sorted_team_ids: Vec<i32> = order.iter().map(|&i| team_ids[i]).collect();
let sorted_is_identified: Vec<bool> = order.iter().map(|&i| stubs[i].is_identified).collect();
let sorted_uuids: Vec<Uuid> = order.iter().map(|&i| uuids[i]).collect();
let sql = format!(
r#"
INSERT INTO {person_table}
(created_at, properties, properties_last_updated_at, properties_last_operation,
team_id, is_identified, uuid, version, last_seen_at)
SELECT u.created_at, '{{}}'::jsonb, '{{}}'::jsonb, '{{}}'::jsonb,
u.team_id, u.is_identified, u.uuid, 0, date_trunc('hour', u.created_at)
FROM unnest($1::timestamptz[], $2::int[], $3::bool[], $4::uuid[])
AS u(created_at, team_id, is_identified, uuid)
ON CONFLICT (team_id, uuid) DO UPDATE SET
is_deleted = false,
version = COALESCE({person_table}.version, 0) + 1,
properties = '{{}}'::jsonb,
properties_last_updated_at = '{{}}'::jsonb,
properties_last_operation = '{{}}'::jsonb,
created_at = EXCLUDED.created_at,
is_identified = EXCLUDED.is_identified,
last_seen_at = EXCLUDED.last_seen_at
WHERE {person_table}.is_deleted = true
AND NOT EXISTS (
SELECT 1 FROM {lop_table} lop
WHERE lop.team_id = {person_table}.team_id
AND lop.person_id = {person_table}.id
AND lop.mark_active
)
RETURNING {person_cols}
"#,
person_cols = person_columns(person_table),
lop_table = tables.lifecycle_op_person,
);
let inserted = sqlx::query(&query_tag!("stub_create_persons", sql))
.bind(&sorted_created_ats)
.bind(&sorted_team_ids)
.bind(&sorted_is_identified)
.bind(&sorted_uuids)
.fetch_all(&mut **tx)
.await?;
let mut persons = PersonsByKey::with_capacity(inserted.len());
for row in inserted {
let person = person_from_row(&row)?;
let revived_tombstone = person.version != Some(0);
persons.insert(
(person.team_id, person.uuid),
ResolvedPerson {
person,
created_by_us: true,
revived_tombstone,
},
);
}
Ok(persons)
}
/// Step 2: batch-fetch the committed winners for conflicted keys. This must
/// be a separate statement: the insert's snapshot predates a concurrent
/// winner's commit, a fresh statement snapshot sees it.
async fn fetch_conflict_winners(
tx: &mut Tx<'_>,
person_table: &str,
stubs: &[PersonStub],
team_ids: &[i32],
uuids: &[Uuid],
persons: &mut PersonsByKey,
) -> StorageResult<()> {
let conflicted: Vec<usize> = (0..stubs.len())
.filter(|&i| !persons.contains_key(&(stubs[i].team_id, uuids[i])))
.collect();
if conflicted.is_empty() {
return Ok(());
}
let conflicted_teams: Vec<i32> = conflicted.iter().map(|&i| team_ids[i]).collect();
let conflicted_uuids: Vec<Uuid> = conflicted.iter().map(|&i| uuids[i]).collect();
let sql = format!(
r#"
SELECT {person_cols}
FROM {person_table} p
JOIN unnest($1::int[], $2::uuid[]) AS k(team_id, uuid)
ON p.team_id = k.team_id AND p.uuid = k.uuid
WHERE p.is_deleted = false
"#,
person_cols = person_columns("p"),
);
let winners = sqlx::query(&query_tag!("stub_create_conflict_winners", sql))
.bind(&conflicted_teams)
.bind(&conflicted_uuids)
.fetch_all(&mut **tx)
.await?;
for row in winners {
let winner = person_from_row(&row)?;
persons.insert(
(winner.team_id, winner.uuid),
ResolvedPerson {
person: winner,
created_by_us: false,
revived_tombstone: false,
},
);
}
Ok(())
}
/// Step 3: multi-row distinct id insert. The primary distinct id derives the
/// person uuid, so any events it sent before the person existed already used
/// the same uuid — always version 0. Extras always get version 1: without
/// the personless table there is no proof the distinct id never sent events,
/// and version 1 is safe either way (the override it emits is a transient
/// no-op when no events exist).
///
/// A conflict with a tombstoned mapping is a revival: repoint it at the new
/// person and bump the version above the tombstone. Conflicts with live
/// mappings keep the existing row (per-row): an extra that raced into
/// another person's hands is a merge scenario, which is the pipeline's job —
/// never re-pointed here, and not an error (a retry could never succeed).
async fn insert_distinct_id_mappings(
tx: &mut Tx<'_>,
pdi_table: &str,
stubs: &[PersonStub],
uuids: &[Uuid],
persons: &PersonsByKey,
) -> StorageResult<MappingOutcome> {
// (team_id, distinct_id, person_id, version)
let mut pdi_rows: Vec<(i32, String, i64, i64)> = Vec::new();
for (i, stub) in stubs.iter().enumerate() {
let Some(resolved) = persons.get(&(stub.team_id, uuids[i])) else {
continue; // winner vanished; resolved to LostRace in step 4
};
pdi_rows.push((
stub.team_id as i32,
stub.distinct_id.clone(),
resolved.person.id,
0,
));
for extra in &stub.extra_distinct_ids {
pdi_rows.push((stub.team_id as i32, extra.clone(), resolved.person.id, 1));
}
}
// Stable sort + dedup: batch order picks the winner among duplicate keys
// (an extra shared between stubs), and the dedup keeps ON CONFLICT DO
// UPDATE from affecting one row twice in the same command.
pdi_rows.sort_by(|a, b| (a.0, &a.1).cmp(&(b.0, &b.1)));
pdi_rows.dedup_by(|a, b| (a.0, &a.1) == (b.0, &b.1));
let pdi_teams: Vec<i32> = pdi_rows.iter().map(|r| r.0).collect();
let pdi_dids: Vec<String> = pdi_rows.iter().map(|r| r.1.clone()).collect();
let pdi_person_ids: Vec<i64> = pdi_rows.iter().map(|r| r.2).collect();
let pdi_versions: Vec<i64> = pdi_rows.iter().map(|r| r.3).collect();
let mut mapping = MappingOutcome {
written: HashMap::new(),
revived: HashSet::new(),
};
if pdi_dids.is_empty() {
return Ok(mapping);
}
let sql = format!(
r#"
INSERT INTO {pdi_table} (distinct_id, person_id, team_id, version)
SELECT d, p, t, v FROM unnest($1::text[], $2::bigint[], $3::int[], $4::bigint[])
AS u(d, p, t, v)
ON CONFLICT (team_id, distinct_id) DO UPDATE SET
person_id = EXCLUDED.person_id,
version = COALESCE({pdi_table}.version, 0) + 1,
is_deleted = false
WHERE {pdi_table}.is_deleted = true
RETURNING team_id::bigint AS team_id, distinct_id, person_id, version,
(xmax = 0) AS inserted
"#
);
let rows = sqlx::query(&query_tag!("stub_create_mappings", sql))
.bind(&pdi_dids)
.bind(&pdi_person_ids)
.bind(&pdi_teams)
.bind(&pdi_versions)
.fetch_all(&mut **tx)
.await?;
for row in rows {
let team_id: i64 = row.try_get("team_id")?;
let distinct_id: String = row.try_get("distinct_id")?;
let person_id: i64 = row.try_get("person_id")?;
let version: i64 = row.try_get("version")?;
let inserted: bool = row.try_get("inserted")?;
if !inserted {
mapping.revived.insert((team_id, distinct_id.clone()));
}
mapping.written.insert(
(team_id, distinct_id),
WrittenMapping { person_id, version },
);
}
Ok(mapping)
}
/// Step 4: per-stub outcomes. A stub is Committed when its primary distinct
/// id maps to its person; a stub whose primary mapping went elsewhere is a
/// lost race, and if this transaction created its person, the rows are
/// undone (created this transaction, so nothing can reference them) so the
/// stub doesn't linger orphaned. Returns the outcomes and the ids of the
/// persons it undid, whose mappings step 5 must not publish.
async fn resolve_stub_outcomes(
tx: &mut Tx<'_>,
tables: &IdentityTables,
stubs: &[PersonStub],
uuids: &[Uuid],
persons: &PersonsByKey,
mapping: &MappingOutcome,
) -> StorageResult<(Vec<StubOutcome>, HashSet<i64>)> {
let mut outcomes = Vec::with_capacity(stubs.len());
let mut undone: HashSet<i64> = HashSet::new();
for (i, stub) in stubs.iter().enumerate() {
let Some(resolved) = persons.get(&(stub.team_id, uuids[i])) else {
outcomes.push(StubOutcome::LostRace);
continue;
};
let primary_key = (stub.team_id, stub.distinct_id.clone());
if mapping.written.get(&primary_key).map(|w| w.person_id) == Some(resolved.person.id) {
outcomes.push(StubOutcome::Committed {
person: resolved.person.clone(),
created: resolved.created_by_us,
});
continue;
}
if resolved.created_by_us {
undo_created_person(tx, tables, stub.team_id, resolved, mapping).await?;
undone.insert(resolved.person.id);
outcomes.push(StubOutcome::LostRace);
continue;
}
// The person pre-existed and its primary mapping wasn't inserted by
// us — verify the existing mapping points at this person.
let existing_sql = format!(
"SELECT person_id FROM {} WHERE team_id = $1 AND distinct_id = $2 AND is_deleted = false",
tables.person_distinct_id
);
let existing: Option<i64> =
sqlx::query_scalar(&query_tag!("stub_create_existing_mapping", existing_sql))
.bind(stub.team_id as i32)
.bind(&stub.distinct_id)
.fetch_optional(&mut **tx)
.await?;
if existing == Some(resolved.person.id) {
outcomes.push(StubOutcome::Committed {
person: resolved.person.clone(),
created: false,
});
} else {
outcomes.push(StubOutcome::LostRace);
}
}
Ok((outcomes, undone))
}
/// Undo one lost-race stub's rows: its distinct id mappings, then its person
/// row. Rows that revived a tombstone must be re-tombstoned (the tombstone
/// predates this transaction and must survive it), never hard-deleted; the
/// version bumps again so it stays monotonic. Re-tombstoning first lets the
/// fresh-row sweep distinguish them by is_deleted.
async fn undo_created_person(
tx: &mut Tx<'_>,
tables: &IdentityTables,
team_id: i64,
resolved: &ResolvedPerson,
mapping: &MappingOutcome,
) -> StorageResult<()> {
let person_table = &tables.person;
let pdi_table = &tables.person_distinct_id;
let revived_dids: Vec<String> = mapping
.written
.iter()
.filter(|((t, d), w)| {
*t == team_id
&& w.person_id == resolved.person.id
&& mapping.revived.contains(&(*t, d.clone()))
})
.map(|((_, d), _)| d.clone())
.collect();
if !revived_dids.is_empty() {
let retombstone_sql = format!(
r#"
UPDATE {pdi_table}
SET is_deleted = true, version = COALESCE(version, 0) + 1
WHERE team_id = $1 AND distinct_id = ANY($2)
"#
);
sqlx::query(&query_tag!(
"stub_create_undo_retombstone_mappings",
retombstone_sql
))
.bind(team_id as i32)
.bind(&revived_dids)
.execute(&mut **tx)
.await?;
}
let delete_mappings_sql = format!(
"DELETE FROM {pdi_table} WHERE team_id = $1 AND person_id = $2 AND is_deleted = false"
);
sqlx::query(&query_tag!(
"stub_create_undo_delete_mappings",
delete_mappings_sql
))
.bind(team_id as i32)
.bind(resolved.person.id)
.execute(&mut **tx)
.await?;
if resolved.revived_tombstone {
let sql = format!(
r#"
UPDATE {person_table}
SET is_deleted = true, version = COALESCE(version, 0) + 1,
properties = '{{}}'::jsonb
WHERE team_id = $1 AND id = $2
"#
);
sqlx::query(&query_tag!("stub_create_undo_retombstone_person", sql))
.bind(team_id as i32)
.bind(resolved.person.id)
.execute(&mut **tx)
.await?;
} else {
let sql = format!("DELETE FROM {person_table} WHERE team_id = $1 AND id = $2");
sqlx::query(&query_tag!("stub_create_undo_delete_person", sql))
.bind(team_id as i32)
.bind(resolved.person.id)
.execute(&mut **tx)
.await?;
}
Ok(())
}
/// Step 5: one publish outbox row per mapping this transaction leaves live,
/// at the version the row carries. Mappings of a person step 4 undid are
/// deleted or re-tombstoned, as they were before the transaction, so they
/// emit nothing. Sorted like every other multi-row statement; the outbox
/// has no unique key besides its identity, so the insert takes no row lock
/// another transaction waits on.
async fn enqueue_mapping_rows(
tx: &mut Tx<'_>,
tables: &IdentityTables,
persons: &PersonsByKey,
mapping: &MappingOutcome,
undone: &HashSet<i64>,
) -> StorageResult<()> {
let uuids: HashMap<i64, Uuid> = persons
.values()
.map(|r| (r.person.id, r.person.uuid))
.collect();
let mut rows: Vec<(i32, Uuid, &str, i64)> = mapping
.written
.iter()
.filter(|(_, w)| !undone.contains(&w.person_id))
.map(|((team_id, distinct_id), w)| {
let uuid = *uuids
.get(&w.person_id)
.expect("written mappings point at resolved persons");
(*team_id as i32, uuid, distinct_id.as_str(), w.version)
})
.collect();
if rows.is_empty() {
return Ok(());
}
rows.sort_by(|a, b| (a.0, a.2).cmp(&(b.0, b.2)));
let team_ids: Vec<i32> = rows.iter().map(|r| r.0).collect();
let person_uuids: Vec<Uuid> = rows.iter().map(|r| r.1).collect();
let distinct_ids: Vec<&str> = rows.iter().map(|r| r.2).collect();
let versions: Vec<i64> = rows.iter().map(|r| r.3).collect();
let sql = format!(
r#"
INSERT INTO {outbox} (team_id, kind, person_uuid, distinct_id, version)
SELECT t, 'create', u, d, v
FROM unnest($1::int[], $2::uuid[], $3::text[], $4::bigint[]) AS r(t, u, d, v)
"#,
outbox = tables.person_publish_outbox,
);
sqlx::query(&query_tag!("stub_create_enqueue_outbox", sql))
.bind(&team_ids)
.bind(&person_uuids)
.bind(&distinct_ids)
.bind(&versions)
.execute(&mut **tx)
.await?;
Ok(())
}