Skip to content

: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.

16.9 KB
Raw Blame
use async_trait::async_trait;
use std::str::from_utf8;

use chrono::{DateTime, TimeZone, Utc};
use metrics::counter;
use personhog_proto::personhog::types::v1::Person;
use sqlx::postgres::PgPool;
use tracing::error;
use uuid::Uuid;

use crate::error::{WriteError, WriteErrorKind};
use crate::store::PersonDb;

/// Allowed target tables for person upserts.
const ALLOWED_TABLES: &[&str] = &["personhog_person_tmp", "posthog_person"];

/// Low-level Postgres execution layer. Runs single upsert statements and
/// classifies sqlx errors. No batching, no parallelism, no retry — those
/// live in the store layer.
pub struct PgStore {
    pool: PgPool,
    upsert_sql: String,
}

#[async_trait]
impl PersonDb for PgStore {
    async fn execute_chunk(&self, chunk: &[Person]) -> Result<(), WriteError> {
        let arrays = prepare_chunk(chunk)?;
        run_upsert(&self.pool, &self.upsert_sql, &arrays, "chunk").await
    }

    async fn execute_row(&self, person: &Person) -> Result<(), WriteError> {
        let arrays = prepare_single(person)?;
        run_upsert(&self.pool, &self.upsert_sql, &arrays, "row").await
    }
}

impl PgStore {
    pub fn new(pool: PgPool, table_name: String) -> Self {
        assert!(
            ALLOWED_TABLES.contains(&table_name.as_str()),
            "PG_TARGET_TABLE must be one of {:?}, got: {table_name}",
            ALLOWED_TABLES
        );

        // Most columns take the record's value outright — the record is the
        // leader's authoritative snapshot and the version guard orders
        // records — but three merge against the existing row instead. The
        // meta columns coalesce because changelog records carry no meta
        // (the leader does not maintain those columns; ingestion's direct
        // writer does), so assignment would erase real values. last_seen_at
        // takes GREATEST — which ignores NULL — because records may predate
        // the field, and the column's invariant is that it only advances.
        let upsert_sql = format!(
            "INSERT INTO {table} (
                id, team_id, uuid, properties, properties_last_updated_at,
                properties_last_operation, created_at, version, is_identified,
                last_seen_at, is_deleted
            )
            SELECT id, team_id, uuid, properties::jsonb,
                   properties_last_updated_at::jsonb, properties_last_operation::jsonb,
                   created_at, version, is_identified, last_seen_at, is_deleted
            FROM UNNEST(
                $1::bigint[], $2::int[], $3::uuid[],
                $4::text[], $5::text[], $6::text[],
                $7::timestamptz[], $8::bigint[], $9::bool[], $10::timestamptz[],
                $11::bool[]
            ) AS u(id, team_id, uuid, properties, properties_last_updated_at,
                   properties_last_operation, created_at, version, is_identified, last_seen_at,
                   is_deleted)
            ON CONFLICT (team_id, id) DO UPDATE SET
                uuid = EXCLUDED.uuid,
                properties = EXCLUDED.properties,
                properties_last_updated_at =
                    COALESCE(EXCLUDED.properties_last_updated_at, {table}.properties_last_updated_at),
                properties_last_operation =
                    COALESCE(EXCLUDED.properties_last_operation, {table}.properties_last_operation),
                created_at = EXCLUDED.created_at,
                version = EXCLUDED.version,
                is_identified = EXCLUDED.is_identified,
                last_seen_at = GREATEST(EXCLUDED.last_seen_at, {table}.last_seen_at),
                is_deleted = EXCLUDED.is_deleted
            WHERE EXCLUDED.version > COALESCE({table}.version, -1)",
            table = table_name
        );

        Self { pool, upsert_sql }
    }
}

/// Build bind arrays from a slice of persons. Borrows string data directly
/// from each Person's byte buffers; `PreparedArrays` is tied to the slice's
/// lifetime and must not outlive `persons`.
///
/// Any field that cannot be bound losslessly (malformed uuid, team_id
/// beyond the column's `integer` range, non-UTF-8 JSON bytes, timestamp
/// outside chrono's range) is a `Data` error: the record is unapplyable
/// as produced, which post-admission is an invariant violation the caller
/// must halt on. Nothing is ever dropped or substituted here — a silent
/// repair would diverge PG from the cache and changelog.
fn prepare_chunk(persons: &[Person]) -> Result<PreparedArrays<'_>, WriteError> {
    // Bind order is lock order: the upsert acquires row locks in unnest
    // order, so binding in conflict-key order gives every flush one global
    // lock direction. Concurrent multi-row writers on overlapping persons
    // (the delete saga's unmap, another flush) sort the same way, and
    // sorted-vs-sorted acquisition cannot deadlock. Only the bind order
    // changes — batch composition and ack accounting are untouched.
    let mut sorted: Vec<&Person> = persons.iter().collect();
    sorted.sort_unstable_by_key(|p| (p.team_id, p.id));
    let mut arrays = PreparedArrays::with_capacity(sorted.len());
    for person in sorted {
        push_person(&mut arrays, person)?;
    }
    Ok(arrays)
}

/// Build a single-row `PreparedArrays` for the per-row path.
fn prepare_single(person: &Person) -> Result<PreparedArrays<'_>, WriteError> {
    let mut arrays = PreparedArrays::with_capacity(1);
    push_person(&mut arrays, person)?;
    Ok(arrays)
}

fn push_person<'a>(arrays: &mut PreparedArrays<'a>, p: &'a Person) -> Result<(), WriteError> {
    let unbindable = |field: &str, detail: String| {
        counter!("personhog_writer_unbindable_field_total", "field" => field.to_string())
            .increment(1);
        WriteError {
            message: format!(
                "unbindable {field} for team_id={} person_id={}: {detail}",
                p.team_id, p.id
            ),
            kind: WriteErrorKind::Data,
        }
    };

    let uuid = Uuid::parse_str(&p.uuid).map_err(|e| unbindable("uuid", e.to_string()))?;
    let team_id = i32::try_from(p.team_id)
        .map_err(|_| unbindable("team_id", "exceeds the column's integer range".to_string()))?;
    let properties =
        bytes_to_json_str(&p.properties, "{}").map_err(|e| unbindable("properties", e))?;
    let properties_last_updated_at = bytes_to_optional_json_str(&p.properties_last_updated_at)
        .map_err(|e| unbindable("properties_last_updated_at", e))?;
    let properties_last_operation = bytes_to_optional_json_str(&p.properties_last_operation)
        .map_err(|e| unbindable("properties_last_operation", e))?;
    let created_at = epoch_ms_to_datetime(p.created_at)
        .ok_or_else(|| unbindable("created_at", format!("epoch {} out of range", p.created_at)))?;
    let last_seen_at = match p.last_seen_at {
        None => None,
        Some(ms) => Some(
            epoch_ms_to_datetime(ms)
                .ok_or_else(|| unbindable("last_seen_at", format!("epoch {ms} out of range")))?,
        ),
    };

    arrays.push(
        p.id,
        team_id,
        uuid,
        properties,
        properties_last_updated_at,
        properties_last_operation,
        created_at,
        Some(p.version),
        p.is_identified,
        last_seen_at,
        p.is_deleted,
    );
    Ok(())
}

async fn run_upsert(
    pool: &PgPool,
    sql: &str,
    arrays: &PreparedArrays<'_>,
    mode: &'static str,
) -> Result<(), WriteError> {
    let chunk_size = arrays.ids.len() as u64;
    if chunk_size == 0 {
        return Ok(());
    }

    match sqlx::query(sql)
        .bind(&arrays.ids)
        .bind(&arrays.team_ids)
        .bind(&arrays.uuids)
        .bind(&arrays.properties)
        .bind(&arrays.properties_last_updated_at)
        .bind(&arrays.properties_last_operation)
        .bind(&arrays.created_at)
        .bind(&arrays.versions)
        .bind(&arrays.is_identified)
        .bind(&arrays.last_seen_at)
        .bind(&arrays.is_deleted)
        .execute(pool)
        .await
    {
        Ok(result) => {
            let affected = result.rows_affected();
            counter!("personhog_writer_rows_upserted_total", "mode" => mode).increment(affected);
            let skipped = chunk_size.saturating_sub(affected);
            if skipped > 0 {
                counter!("personhog_writer_rows_version_skipped_total", "mode" => mode)
                    .increment(skipped);
            }
            Ok(())
        }
        Err(e) => {
            let kind = classify_error(&e);
            counter!("personhog_writer_upsert_errors_total", "mode" => mode).increment(1);
            error!(error = %e, error_kind = ?kind, mode, "upsert failed");
            Err(WriteError {
                message: e.to_string(),
                kind,
            })
        }
    }
}

fn classify_error(e: &sqlx::Error) -> WriteErrorKind {
    match e {
        // Waiting on our own pool is backpressure, not database failure —
        // classified apart so the writer retries without escalating.
        sqlx::Error::PoolTimedOut => WriteErrorKind::Saturation,

        sqlx::Error::Io(_) | sqlx::Error::PoolClosed | sqlx::Error::WorkerCrashed => {
            WriteErrorKind::Transient
        }

        sqlx::Error::Database(db_err) => {
            if let Some(code) = db_err.code() {
                let code_str = code.as_ref();
                if code_str == "23514" {
                    if let Some(constraint) = db_err.constraint() {
                        if constraint.contains("check_properties_size") {
                            return WriteErrorKind::PropertiesSizeViolation;
                        }
                    }
                }
                if code_str.starts_with("08")
                    || code_str.starts_with("53")
                    || code_str.starts_with("57")
                    || code_str == "40P01"
                {
                    return WriteErrorKind::Transient;
                }
            }
            WriteErrorKind::Data
        }

        _ => WriteErrorKind::Data,
    }
}

// ── Bind arrays ─────────────────────────────────────────────────

/// Column-oriented bind arrays for the upsert statement. String fields
/// borrow from each Person's byte buffer to avoid per-row allocations; the
/// struct's lifetime is tied to the source slice.
#[derive(Debug)]
struct PreparedArrays<'a> {
    ids: Vec<i64>,
    team_ids: Vec<i32>,
    uuids: Vec<Uuid>,
    properties: Vec<&'a str>,
    properties_last_updated_at: Vec<Option<&'a str>>,
    properties_last_operation: Vec<Option<&'a str>>,
    created_at: Vec<DateTime<Utc>>,
    versions: Vec<Option<i64>>,
    is_identified: Vec<bool>,
    last_seen_at: Vec<Option<DateTime<Utc>>>,
    is_deleted: Vec<bool>,
}

impl<'a> PreparedArrays<'a> {
    fn with_capacity(cap: usize) -> Self {
        Self {
            ids: Vec::with_capacity(cap),
            team_ids: Vec::with_capacity(cap),
            uuids: Vec::with_capacity(cap),
            properties: Vec::with_capacity(cap),
            properties_last_updated_at: Vec::with_capacity(cap),
            properties_last_operation: Vec::with_capacity(cap),
            created_at: Vec::with_capacity(cap),
            versions: Vec::with_capacity(cap),
            is_identified: Vec::with_capacity(cap),
            last_seen_at: Vec::with_capacity(cap),
            is_deleted: Vec::with_capacity(cap),
        }
    }

    #[allow(clippy::too_many_arguments)]
    fn push(
        &mut self,
        id: i64,
        team_id: i32,
        uuid: Uuid,
        properties: &'a str,
        properties_last_updated_at: Option<&'a str>,
        properties_last_operation: Option<&'a str>,
        created_at: DateTime<Utc>,
        version: Option<i64>,
        is_identified: bool,
        last_seen_at: Option<DateTime<Utc>>,
        is_deleted: bool,
    ) {
        self.ids.push(id);
        self.team_ids.push(team_id);
        self.uuids.push(uuid);
        self.properties.push(properties);
        self.properties_last_updated_at
            .push(properties_last_updated_at);
        self.properties_last_operation
            .push(properties_last_operation);
        self.created_at.push(created_at);
        self.versions.push(version);
        self.is_identified.push(is_identified);
        self.last_seen_at.push(last_seen_at);
        self.is_deleted.push(is_deleted);
    }
}

/// Interpret proto bytes as a JSON string. The leader serializes a parsed
/// `serde_json::Value`, so these are valid JSON UTF-8 by construction; the
/// `::jsonb` cast in the statement re-validates at PG. Empty bytes are the
/// proto default and decode to `default`. Invalid UTF-8 is an error, never
/// a substitution.
fn bytes_to_json_str<'a>(bytes: &'a [u8], default: &'static str) -> Result<&'a str, String> {
    if bytes.is_empty() {
        return Ok(default);
    }
    from_utf8(bytes).map_err(|e| format!("non-UTF-8 JSON bytes: {e}"))
}

fn bytes_to_optional_json_str(bytes: &[u8]) -> Result<Option<&str>, String> {
    if bytes.is_empty() {
        return Ok(None);
    }
    from_utf8(bytes)
        .map(Some)
        .map_err(|e| format!("non-UTF-8 JSON bytes: {e}"))
}

fn epoch_ms_to_datetime(epoch_ms: i64) -> Option<DateTime<Utc>> {
    Utc.timestamp_millis_opt(epoch_ms).single()
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn classify_pool_timeout_as_saturation() {
        assert!(matches!(
            classify_error(&sqlx::Error::PoolTimedOut),
            WriteErrorKind::Saturation
        ));
    }

    #[test]
    fn classify_io_error_as_transient() {
        let err = sqlx::Error::Io(std::io::Error::new(
            std::io::ErrorKind::ConnectionReset,
            "connection reset",
        ));
        assert!(matches!(classify_error(&err), WriteErrorKind::Transient));
    }

    #[test]
    fn bytes_to_json_str_valid() {
        let s = bytes_to_json_str(b"{\"email\":\"[email protected]\"}", "{}").unwrap();
        assert_eq!(s, "{\"email\":\"[email protected]\"}");
    }

    #[test]
    fn bytes_to_json_str_empty_returns_default() {
        assert_eq!(bytes_to_json_str(b"", "{}").unwrap(), "{}");
    }

    #[test]
    fn bytes_to_json_str_rejects_invalid_utf8() {
        assert!(bytes_to_json_str(&[0xff, 0xfe], "{}").is_err());
    }

    #[test]
    fn bytes_to_optional_json_str_empty_returns_none() {
        assert!(bytes_to_optional_json_str(b"").unwrap().is_none());
    }

    #[test]
    fn bytes_to_optional_json_str_valid() {
        let s = bytes_to_optional_json_str(b"{\"key\":\"val\"}").unwrap();
        assert_eq!(s.unwrap(), "{\"key\":\"val\"}");
    }

    #[test]
    fn epoch_ms_conversion() {
        let dt = epoch_ms_to_datetime(1700000000000).unwrap();
        assert_eq!(dt.timestamp_millis(), 1700000000000);
        assert!(epoch_ms_to_datetime(i64::MAX).is_none());
    }

    #[tokio::test]
    async fn table_name_allowlist_accepts_valid() {
        let pool = PgPool::connect_lazy("postgres://localhost/test").unwrap();
        let _store = PgStore::new(pool.clone(), "personhog_person_tmp".to_string());
        let _store = PgStore::new(pool, "posthog_person".to_string());
    }

    #[tokio::test]
    #[should_panic(expected = "PG_TARGET_TABLE must be one of")]
    async fn table_name_allowlist_rejects_invalid() {
        let pool = PgPool::connect_lazy("postgres://localhost/test").unwrap();
        let _store = PgStore::new(pool, "evil_table; DROP TABLE posthog_person".to_string());
    }

    fn person_with(id: i64, team_id: i64) -> Person {
        Person {
            id,
            team_id,
            uuid: Uuid::new_v4().to_string(),
            version: 1,
            ..Default::default()
        }
    }

    #[test]
    fn prepare_chunk_binds_in_conflict_key_order() {
        // Bind order is the upsert's lock order; a batch bound in buffer
        // order deadlocks against concurrent sorted writers on overlapping
        // rows.
        let persons = [
            person_with(5, 2),
            person_with(3, 1),
            person_with(4, 2),
            person_with(1, 1),
        ];
        let arrays = prepare_chunk(&persons).expect("chunk prepares");
        assert_eq!(arrays.team_ids, vec![1, 1, 2, 2]);
        assert_eq!(arrays.ids, vec![1, 3, 4, 5]);
    }

    #[test]
    fn prepare_chunk_errors_on_out_of_range_team_id() {
        // One unbindable row fails the whole chunk: silently dropping it
        // would permanently diverge PG from the cache and changelog.
        let good = person_with(1, 42);
        let bad = person_with(2, (i32::MAX as i64) + 1);

        let persons = [good, bad];
        let err = prepare_chunk(&persons).expect_err("unbindable row must error");
        assert!(matches!(err.kind, WriteErrorKind::Data));
        assert!(err.message.contains("team_id"));
    }

    #[test]
    fn prepare_single_errors_on_malformed_uuid() {
        let mut bad = person_with(1, 42);
        bad.uuid = "not-a-uuid".to_string();
        let err = prepare_single(&bad).expect_err("malformed uuid must error");
        assert!(matches!(err.kind, WriteErrorKind::Data));
        assert!(err.message.contains("uuid"));
    }
}