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.

123 KB
Raw Blame
use std::{
    collections::{HashMap, HashSet},
    future::Future,
    sync::Mutex,
    time::{Duration, Instant},
};
use tokio_retry::{
    strategy::{jitter, ExponentialBackoff},
    RetryIf,
};

use crate::database::{
    get_connection_with_metrics, get_writer_connection_with_metrics, pool_names, PostgresRouter,
};
use common_cookieless::COOKIELESS_SENTINEL_VALUE;
use common_database::{PostgresReader, PostgresWriter};
use common_types::{Person, PersonId, TeamId};
use once_cell::sync::Lazy;
use rand::Rng;
use serde_json::Value;
use sha1::{Digest, Sha1};
use sqlx::{
    postgres::{PgConnection, PgRow},
    Acquire, Row,
};
use tokio::time::timeout;
use tracing::{debug, instrument, warn};

// Add thread-local imports for test-specific counter
#[cfg(test)]
use std::cell::RefCell;

use crate::{
    api::{errors::FlagError, types::FlagValue},
    cohorts::cohort_models::CohortId,
    flags::flag_models::FeatureFlagId,
    handler::with_canonical_log,
    metrics::consts::{
        FLAG_COHORT_PROCESSING_TIME, FLAG_COHORT_QUERY_TIME, FLAG_DATABASE_ERROR_COUNTER,
        FLAG_DEFINITION_QUERY_TIME, FLAG_GROUP_PROCESSING_TIME, FLAG_GROUP_QUERY_TIME,
        FLAG_HASH_KEY_QUERY_RESULT, FLAG_HASH_KEY_REPLICA_CHECK, FLAG_HASH_KEY_RETRIES_COUNTER,
        FLAG_PERSON_PROCESSING_TIME, FLAG_PERSON_QUERY_TIME,
    },
    properties::property_models::{OperatorType, PropertyFilter},
};

use super::{flag_group_type_mapping::GroupTypeIndex, flag_matching::FlagEvaluationState};

const LONG_SCALE: u64 = 0xfffffffffffffff;

/// Enough to size a rate nobody has measured, small enough that the extra primary load is noise.
const REPLICA_STALENESS_SAMPLE_RATE: f64 = 0.01;

/// Bounds how long a detached check can occupy the writer pool. Covers connection acquisition as
/// well as the query, so a saturated pool kills the task here rather than at its acquire timeout.
const REPLICA_STALENESS_CHECK_TIMEOUT: Duration = Duration::from_millis(50);

/// Precomputed mapping from property name to its $initial_ equivalent.
/// Source: posthog/taxonomy/taxonomy.py - PERSON_PROPERTIES_ADAPTED_FROM_EVENT + CAMPAIGN_PROPERTIES
static INITIAL_PROPERTY_MAP: Lazy<HashMap<&'static str, &'static str>> = Lazy::new(|| {
    HashMap::from([
        // PERSON_PROPERTIES_ADAPTED_FROM_EVENT
        ("$app_build", "$initial_app_build"),
        ("$app_name", "$initial_app_name"),
        ("$app_namespace", "$initial_app_namespace"),
        ("$app_version", "$initial_app_version"),
        ("$browser", "$initial_browser"),
        ("$browser_version", "$initial_browser_version"),
        ("$device_type", "$initial_device_type"),
        ("$current_url", "$initial_current_url"),
        ("$pathname", "$initial_pathname"),
        ("$os", "$initial_os"),
        ("$os_version", "$initial_os_version"),
        ("$referring_domain", "$initial_referring_domain"),
        ("$referrer", "$initial_referrer"),
        ("$screen_height", "$initial_screen_height"),
        ("$screen_width", "$initial_screen_width"),
        ("$viewport_height", "$initial_viewport_height"),
        ("$viewport_width", "$initial_viewport_width"),
        ("$raw_user_agent", "$initial_raw_user_agent"),
        // CAMPAIGN_PROPERTIES
        ("utm_source", "$initial_utm_source"),
        ("utm_medium", "$initial_utm_medium"),
        ("utm_campaign", "$initial_utm_campaign"),
        ("utm_content", "$initial_utm_content"),
        ("utm_term", "$initial_utm_term"),
        ("gclid", "$initial_gclid"),
        ("gad_source", "$initial_gad_source"),
        ("gclsrc", "$initial_gclsrc"),
        ("dclid", "$initial_dclid"),
        ("gbraid", "$initial_gbraid"),
        ("wbraid", "$initial_wbraid"),
        ("fbclid", "$initial_fbclid"),
        ("msclkid", "$initial_msclkid"),
        ("twclid", "$initial_twclid"),
        ("li_fat_id", "$initial_li_fat_id"),
        ("mc_cid", "$initial_mc_cid"),
        ("igshid", "$initial_igshid"),
        ("ttclid", "$initial_ttclid"),
        ("rdt_cid", "$initial_rdt_cid"),
        ("epik", "$initial_epik"),
        ("qclid", "$initial_qclid"),
        ("sccid", "$initial_sccid"),
        ("oppref", "$initial_oppref"),
        ("bfcid", "$initial_bfcid"),
        ("irclid", "$initial_irclid"),
        ("_kx", "$initial__kx"),
    ])
});

// Replace the static counter with thread-local storage
#[cfg(test)]
thread_local! {
    static FETCH_CALLS: RefCell<u64> = const { RefCell::new(0) };
    static HASH_KEY_OVERRIDE_LOOKUPS: RefCell<u64> = const { RefCell::new(0) };
}

/// Calculates a deterministic hash value between 0 and 1 for a given identifier and salt.
///
/// This function uses SHA1 to generate a hash, then converts the first 15 characters to a number
/// between 0 and 1. The hash is deterministic for the same input values.
///
/// ## Arguments
/// * `prefix` - A prefix to add to the hash key (e.g., "holdout-")
/// * `hashed_identifier` - The main identifier to hash (e.g., user ID)
/// * `salt` - Additional string to make the hash unique (can be empty)
///
/// ## Returns
/// * `f64` - A number between 0 and 1
pub fn calculate_hash(prefix: &str, hashed_identifier: &str, salt: &str) -> Result<f64, FlagError> {
    let hash_key = format!("{prefix}{hashed_identifier}{salt}");
    let hash_value = Sha1::digest(hash_key.as_bytes());
    // We use the first 8 bytes of the hash and shift right by 4 bits
    // This is equivalent to using the first 15 hex characters (7.5 bytes) of the hash
    // as was done in the previous implementation, ensuring consistent feature flag distribution
    let hash_val: u64 = u64::from_be_bytes(hash_value[..8].try_into().unwrap()) >> 4;
    Ok(hash_val as f64 / LONG_SCALE as f64)
}

/// Populates missing `$initial_` properties from their non-initial counterparts.
///
/// This mitigates ingestion lag: `$initial_` properties are set by ingestion upon
/// first seeing a property value, but there can be a delay when writing these properties.
/// Without this, feature flag conditions filtering on `$initial_` properties would not
/// match during this window, even though the current property value exists.
///
/// Property name transformations:
/// - `$browser` -> `$initial_browser`
/// - `utm_source` -> `$initial_utm_source`
pub fn populate_missing_initial_properties(properties: &mut HashMap<String, Value>) {
    let properties_to_add: Vec<(&str, Value)> = properties
        .iter()
        .filter_map(|(key, value)| {
            let initial_key = INITIAL_PROPERTY_MAP.get(key.as_str())?;
            if !properties.contains_key(*initial_key) {
                Some((*initial_key, value.clone()))
            } else {
                None
            }
        })
        .collect();

    for (key, value) in properties_to_add {
        properties.insert(key.to_string(), value);
    }
}

/// Mirrors `$os` and `$os_name` so a flag condition keyed on either property
/// matches when the person row carries only one of them.
///
/// Web SDKs (posthog-js) report the OS as `$os`; mobile SDKs report it as
/// `$os_name`. Ingestion normalizes `$os_name` -> `$os` at write time
/// (`normalizeOsAlias` in nodejs/src/common/utils/event.ts for the event's own
/// `$os`, `personInitialAndUTMProperties` in nodejs/src/common/utils/db/utils.ts
/// for the person row), but that only covers newly-written rows, and it excludes
/// `$is_server` events; historical and un-normalized person rows can
/// carry only one of the two keys. Flag matching does exact-key lookups, so
/// without this a mobile person whose row has only `$os_name` never matches an
/// `$os` filter (and vice versa). The mirror is bidirectional so either filter
/// key works regardless of which key the person row happens to carry.
///
/// Only the missing key is filled — an already-present value is never
/// overwritten, so request overrides keep precedence over their own key.
pub fn populate_os_aliases(properties: &mut HashMap<String, Value>) {
    match (properties.get("$os"), properties.get("$os_name")) {
        (Some(os), None) => {
            properties.insert("$os_name".to_string(), os.clone());
        }
        (None, Some(os_name)) => {
            properties.insert("$os".to_string(), os_name.clone());
        }
        // Both present or both absent: nothing to mirror.
        _ => {}
    }
}

/// Result from the person + static cohort query branch.
struct PersonCohortResult {
    person: Option<Person>,
    /// `None` when person not found (state's cohort_matches stays unset).
    /// `Some(empty map)` when person found but no static cohorts to check.
    /// `Some(results)` when person found and cohorts checked.
    cohort_matches: Option<HashMap<CohortId, bool>>,
}

/// Result from the group properties query branch.
struct GroupResult {
    group_properties: HashMap<GroupTypeIndex, HashMap<String, Value>>,
}

/// Fetch person data and static cohort membership from the persons_reader pool.
///
/// Acquires its own connection. The person query and cohort query run sequentially
/// because cohort lookup depends on the person_id returned by the person query.
async fn fetch_person_and_cohorts(
    reader: &PostgresReader,
    team_id: TeamId,
    distinct_id: &str,
    static_cohort_ids: &[CohortId],
) -> Result<PersonCohortResult, FlagError> {
    let conn_acquisition_start = Instant::now();
    let conn_result = get_connection_with_metrics(
        reader,
        pool_names::PERSONS_READER,
        "fetch_person_properties",
    )
    .await;
    let conn_acquisition_duration = conn_acquisition_start.elapsed();

    let mut conn = match conn_result {
        Ok(conn) => {
            debug!(
                conn_acquisition_ms = conn_acquisition_duration.as_millis(),
                "persons_reader connection acquired for person+cohort query"
            );
            conn
        }
        Err(e) => {
            let (pool_size, pool_idle, pool_in_use) =
                if let Some(stats) = reader.as_ref().get_pool_stats() {
                    (
                        stats.size,
                        stats.num_idle as u32,
                        stats.size.saturating_sub(stats.num_idle as u32),
                    )
                } else {
                    (0, 0, 0)
                };

            warn!(
                conn_acquisition_ms = conn_acquisition_duration.as_millis(),
                pool_size = pool_size,
                pool_idle = pool_idle,
                pool_in_use = pool_in_use,
                error = ?e,
                "Failed to acquire persons_reader connection for person+cohort query"
            );

            return Err(FlagError::from(e));
        }
    };

    let query_labels = [
        ("pool".to_string(), pool_names::PERSONS_READER.to_string()),
        ("team_id".to_string(), team_id.to_string()),
    ];

    // First query: Get person data from the distinct_id (person_id and person_properties)
    // TRICKY: sometimes we don't have a person_id ingested by the time we get a `/flags` request for a given
    // distinct_id. There's two cases for that:
    // 1. there's a race condition between person ingestion and flag evaluation.  In that case, only the first flag request
    // be missing a person id, and all subsequent requests will have a person id.  That means the first flag evaluation could be wrong, but all subsequent ones will be correct.  Not a huge problem.
    // 2. the distinct_id is associated with an anonymous or cookieless user.  In that case, it's fine to not return a person ID and to never return person properties.  This is handled by just
    // returning an empty HashMap for person properties whenever I actually need them, and then obviously any condition that depends on person properties will return false.
    // That's fine though, we shouldn't error out just because we can't find a person ID.
    let person_query_start = Instant::now();
    let person_query_timer = common_metrics::timing_guard(FLAG_PERSON_QUERY_TIME, &query_labels);
    let person = Person::from_distinct_id(&mut conn, team_id, distinct_id).await?;
    person_query_timer.fin();
    let person_query_duration = person_query_start.elapsed();
    with_canonical_log(|log| {
        log.person_queries += 1;
        log.person_query_time_ms += person_query_duration.as_millis() as u64;
    });

    if person_query_duration.as_millis() > 500 {
        warn!(
            duration_ms = person_query_duration.as_millis(),
            distinct_id = distinct_id,
            team_id = team_id,
            sql_summary =
                "SELECT person_id, properties with INNER JOIN from persondistinctid to person",
            "Slow person query detected"
        );
    } else {
        debug!(
            duration_ms = person_query_duration.as_millis(),
            distinct_id = distinct_id,
            team_id = team_id,
            "Person query completed"
        );
    }

    let cohort_matches = if let Some(ref person) = person {
        if !static_cohort_ids.is_empty() {
            let cohort_query = r#"
                    WITH cohort_membership AS (
                        SELECT c.cohort_id,
                               CASE WHEN pc.cohort_id IS NOT NULL THEN true ELSE false END AS is_member
                        FROM unnest($1::integer[]) AS c(cohort_id)
                        LEFT JOIN posthog_cohortpeople AS pc
                          ON pc.person_id = $2
                          AND pc.cohort_id = c.cohort_id
                    )
                    SELECT cohort_id, is_member
                    FROM cohort_membership
                "#;

            let cohort_query_start = Instant::now();
            let cohort_timer = common_metrics::timing_guard(FLAG_COHORT_QUERY_TIME, &query_labels);
            let cohort_rows = sqlx::query(cohort_query)
                .bind(static_cohort_ids)
                .bind(person.id)
                .fetch_all(&mut *conn)
                .await?;
            cohort_timer.fin();
            let cohort_query_duration = cohort_query_start.elapsed();
            with_canonical_log(|log| {
                log.static_cohort_queries += 1;
                log.cohort_query_time_ms += cohort_query_duration.as_millis() as u64;
            });

            if cohort_query_duration.as_millis() > 200 {
                warn!(
                    duration_ms = cohort_query_duration.as_millis(),
                    person_id = person.id,
                    cohort_count = static_cohort_ids.len(),
                    sql_summary =
                        "SELECT cohort membership with LEFT JOIN from UNNEST to cohortpeople",
                    "Slow cohort query detected"
                );
            } else {
                debug!(
                    duration_ms = cohort_query_duration.as_millis(),
                    person_id = person.id,
                    cohort_count = static_cohort_ids.len(),
                    "Cohort query completed"
                );
            }

            let cohort_processing_timer =
                common_metrics::timing_guard(FLAG_COHORT_PROCESSING_TIME, &[]);
            let cohort_results: HashMap<CohortId, bool> = cohort_rows
                .into_iter()
                .map(|row| {
                    let cohort_id: CohortId = row.get("cohort_id");
                    let is_member: bool = row.get("is_member");
                    (cohort_id, is_member)
                })
                .collect();
            cohort_processing_timer.fin();

            Some(cohort_results)
        } else {
            // TRICKY: if there are no static cohorts to check, we want to return an empty map to show that
            // we checked the cohorts and found no matches. I want to differentiate from returning None, which
            // would indicate that that we had an error doing this evaluation in the first place.
            // i.e.: if there are no static cohort ID matches, it means we checked, and if there's None, it means something
            // went wrong.  This is handled in the caller.
            Some(HashMap::new())
        }
    } else {
        None
    };

    Ok(PersonCohortResult {
        person,
        cohort_matches,
    })
}

/// Fetch group properties from the database.
///
/// Acquires its own connection from the provided reader pool.
/// Independent of person/cohort queries, so it can run concurrently.
///
/// NOTE: `posthog_group` lives in the persons database (same as person/cohort
/// tables), so this uses the same `persons_reader` pool. The two connections
/// acquired in parallel (one for person+cohort, one for groups) both come from
/// the same pool.
async fn fetch_group_properties(
    reader: &PostgresReader,
    team_id: TeamId,
    group_type_to_key: &HashMap<GroupTypeIndex, String>,
) -> Result<GroupResult, FlagError> {
    let conn_acquisition_start = Instant::now();
    let conn_result =
        get_connection_with_metrics(reader, pool_names::PERSONS_READER, "fetch_group_properties")
            .await;
    let conn_acquisition_duration = conn_acquisition_start.elapsed();

    let mut conn = match conn_result {
        Ok(conn) => {
            debug!(
                conn_acquisition_ms = conn_acquisition_duration.as_millis(),
                "persons_reader connection acquired for group query"
            );
            conn
        }
        Err(e) => {
            let (pool_size, pool_idle, pool_in_use) =
                if let Some(stats) = reader.as_ref().get_pool_stats() {
                    (
                        stats.size,
                        stats.num_idle as u32,
                        stats.size.saturating_sub(stats.num_idle as u32),
                    )
                } else {
                    (0, 0, 0)
                };

            warn!(
                conn_acquisition_ms = conn_acquisition_duration.as_millis(),
                pool_size = pool_size,
                pool_idle = pool_idle,
                pool_in_use = pool_in_use,
                error = ?e,
                "Failed to acquire persons_reader connection for group query"
            );

            return Err(FlagError::from(e));
        }
    };

    let query_labels = [
        ("pool".to_string(), pool_names::PERSONS_READER.to_string()),
        ("team_id".to_string(), team_id.to_string()),
    ];

    let group_query = r#"
        SELECT
            g.group_type_index,
            g.group_properties
        FROM posthog_group g
        INNER JOIN UNNEST($2::integer[], $3::text[]) AS t(group_type_index, group_key)
            ON g.group_type_index = t.group_type_index AND g.group_key = t.group_key
        WHERE g.team_id = $1
    "#;

    let (group_type_indexes_vec, group_keys_vec): (Vec<GroupTypeIndex>, Vec<String>) =
        group_type_to_key
            .iter()
            .map(|(&k, v)| (k, v.clone()))
            .unzip();

    let group_query_start = Instant::now();
    let group_query_timer = common_metrics::timing_guard(FLAG_GROUP_QUERY_TIME, &query_labels);
    let groups = sqlx::query(group_query)
        .bind(team_id)
        .bind(&group_type_indexes_vec)
        .bind(&group_keys_vec)
        .fetch_all(&mut *conn)
        .await?;
    group_query_timer.fin();
    let group_query_duration = group_query_start.elapsed();
    with_canonical_log(|log| {
        log.group_queries += 1;
        log.group_query_time_ms += group_query_duration.as_millis() as u64;
    });

    if group_query_duration.as_millis() > 300 {
        warn!(
            duration_ms = group_query_duration.as_millis(),
            team_id = team_id,
            group_pair_count = group_type_to_key.len(),
            sql_summary =
                "SELECT group properties with UNNEST for group_type_index, group_key pairs",
            "Slow group query detected"
        );
    } else {
        debug!(
            duration_ms = group_query_duration.as_millis(),
            team_id = team_id,
            group_pair_count = group_type_to_key.len(),
            result_count = groups.len(),
            "Group query completed"
        );
    }

    let group_processing_timer = common_metrics::timing_guard(FLAG_GROUP_PROCESSING_TIME, &[]);
    let group_properties = groups
        .into_iter()
        .filter_map(|row| {
            let group_type_index: GroupTypeIndex = row.get("group_type_index");
            let properties: Value = row.get("group_properties");

            if let Value::Object(props) = properties {
                Some((group_type_index, props.into_iter().collect()))
            } else {
                None
            }
        })
        .collect();
    group_processing_timer.fin();

    Ok(GroupResult { group_properties })
}

/// Apply person and cohort results to the flag evaluation state.
///
/// `distinct_id` is intentionally not injected here. `evaluate_all_feature_flags`
/// folds `self.distinct_id` into `person_property_overrides` upfront via
/// `merge_distinct_id_into_person_properties`, and overrides extend on top of
/// these DB-loaded properties at merge time, so adding `distinct_id` here would
/// just be overwritten on the way out.
fn apply_person_cohort_to_state(state: &mut FlagEvaluationState, result: PersonCohortResult) {
    let person_processing_timer = common_metrics::timing_guard(FLAG_PERSON_PROCESSING_TIME, &[]);

    if let Some(ref person) = result.person {
        state.set_person_id(person.id);
        state.set_person_uuid(person.uuid);
    }

    if let Some(cohort_matches) = result.cohort_matches {
        state.set_cohort_matches(cohort_matches);
    }

    let mut person_properties: HashMap<String, Value> = if let Some(ref person) = result.person {
        match person.properties.as_object() {
            Some(obj) => obj.iter().map(|(k, v)| (k.clone(), v.clone())).collect(),
            None => HashMap::new(),
        }
    } else {
        HashMap::new()
    };

    // PersonMetadata fields (top-level columns on the persons table) are written under a
    // sentinel prefix to avoid colliding with user-set properties of the same name (e.g.
    // a customer setting `properties.created_at` for their own analytics). The matcher
    // applies the prefix when `filter.prop_type == PersonMetadata` — see `match_property`.
    // The field list lives in `PERSON_METADATA_FIELDS`; each field needs a match arm below
    // mapping it to the persons-table column to read. A field added to that list without an arm
    // here falls through `_ => continue` and is silently never injected, so keep the two in sync.
    if let Some(ref person) = result.person {
        for field in crate::properties::property_matching::PERSON_METADATA_FIELDS {
            let value = match *field {
                "created_at" => Value::String(person.created_at.to_rfc3339()),
                _ => continue,
            };
            person_properties.insert(
                crate::properties::property_matching::person_metadata_key(field),
                value,
            );
        }
    }

    state.set_person_properties(person_properties);
    person_processing_timer.fin();
}

/// Fetch and locally cache all properties for a given distinct ID and team ID.
///
/// This function fetches person, cohort, and group properties and applies them to the
/// evaluation state. When groups are needed, person+cohort and group queries run in
/// parallel via `tokio::try_join!`, each acquiring its own connection from the same pool.
#[instrument(skip_all, fields(
    team_id = %team_id,
    distinct_id = %distinct_id,
    cohort_ids = ?static_cohort_ids,
    group_type_to_key = ?group_type_to_key
))]
pub async fn fetch_and_locally_cache_all_relevant_properties(
    flag_evaluation_state: &mut FlagEvaluationState,
    reader: PostgresReader,
    distinct_id: String,
    team_id: TeamId,
    group_type_to_key: &HashMap<GroupTypeIndex, String>,
    static_cohort_ids: Vec<CohortId>,
) -> Result<(), FlagError> {
    // Add the test-specific counter increment
    #[cfg(test)]
    increment_fetch_calls_count();

    // Track database property fetch in canonical log
    with_canonical_log(|log| log.db_property_fetches += 1);

    // Log pool stats before attempting connections
    if let Some(stats) = reader.as_ref().get_pool_stats() {
        debug!(
            pool_size = stats.size,
            pool_idle = stats.num_idle,
            pool_in_use = stats.size.saturating_sub(stats.num_idle as u32),
            "Connection pool stats before acquiring connection"
        );
    }

    if !group_type_to_key.is_empty() {
        // SAFETY: tokio::try_join! polls both futures on the same task via cooperative
        // scheduling. with_canonical_log borrows a task-local RefCell synchronously (no
        // .await while borrowed), so double-borrow cannot occur. Do NOT refactor this
        // to tokio::spawn: that would run the futures on separate tasks, which do not
        // inherit the CANONICAL_LOG task-local scope (see handler/canonical_log.rs),
        // causing with_canonical_log to silently no-op and drop canonical-log counters.
        let (person_cohort, group) = tokio::try_join!(
            fetch_person_and_cohorts(&reader, team_id, &distinct_id, &static_cohort_ids),
            fetch_group_properties(&reader, team_id, group_type_to_key),
        )
        .inspect_err(|e| track_unretried_db_error(e, db_operations::FETCH_PROPERTIES))?;

        apply_person_cohort_to_state(flag_evaluation_state, person_cohort);
        // Mark every requested index as fetched, not just the ones the query returned a
        // row for. The group query is authoritative for all requested (index, key) pairs,
        // so "no row" means the group genuinely has no properties. Recording that keeps it
        // distinguishable from "prep never ran", which is what
        // `FlagEvaluationState::group_properties_pending` keys on.
        for &idx in group_type_to_key.keys() {
            flag_evaluation_state.mark_group_properties_fetched(idx);
        }
        for (idx, props) in group.group_properties {
            flag_evaluation_state.set_group_properties(idx, props);
        }
    } else {
        let person_cohort =
            fetch_person_and_cohorts(&reader, team_id, &distinct_id, &static_cohort_ids)
                .await
                .inspect_err(|e| track_unretried_db_error(e, db_operations::FETCH_PROPERTIES))?;
        apply_person_cohort_to_state(flag_evaluation_state, person_cohort);
    }

    Ok(())
}

/// Return any locally computable property overrides (non-cohort properties).
/// This returns the subset of overrides that can be computed locally, even if not all flag properties are overridden.
pub fn locally_computable_property_overrides(
    property_overrides: &Option<HashMap<String, Value>>,
    property_filters: &[PropertyFilter],
) -> Option<HashMap<String, Value>> {
    let overrides = property_overrides.as_ref()?;

    // Early return if flag has cohort filters - these require DB lookup
    if has_cohort_filters(property_filters) {
        return None;
    }

    // Only return overrides if they're useful for this flag
    if are_overrides_useful_for_flag(overrides, property_filters) {
        Some(overrides.clone())
    } else {
        None
    }
}

/// Checks if any property filters involve cohorts that require database lookup
fn has_cohort_filters(property_filters: &[PropertyFilter]) -> bool {
    property_filters.iter().any(|prop| prop.is_cohort())
}

/// Determines if the provided overrides contain properties that the flag actually needs
fn are_overrides_useful_for_flag(
    overrides: &HashMap<String, Value>,
    property_filters: &[PropertyFilter],
) -> bool {
    // If flag doesn't need any properties, overrides aren't useful
    if property_filters.is_empty() {
        return false;
    }

    // Check if overrides contain at least one property the flag needs.
    // Use `lookup_key_for` so PersonMetadata filters match on the sentinel-prefixed key rather
    // than the raw key — see the note on `requires_db_property`.
    property_filters.iter().any(|filter| {
        overrides
            .contains_key(crate::properties::property_matching::lookup_key_for(filter).as_ref())
    })
}

/// Classifies whether a FlagError is worth retrying.
///
/// Timeouts are not retried. A second attempt could wait the full acquire and statement timeouts
/// again, which pushes the call past the request timeout. A foreign key violation is retried
/// because it means that a person was deleted during a hash key override write.
fn should_retry_on_error(error: &FlagError) -> bool {
    matches!(
        classify_db_error(error),
        Some(("foreign_key" | "transient", _))
    )
}

/// Check if a FlagError contains a foreign key constraint violation
fn flag_error_is_foreign_key_constraint(error: &FlagError) -> bool {
    match error {
        FlagError::DatabaseError(sqlx_error, _) => {
            common_database::is_foreign_key_constraint_error(sqlx_error)
        }
        _ => false,
    }
}

/// Maps a database-related FlagError to its `(error_type, timeout_subtype)` metric labels,
/// or None for errors that aren't tracked.
fn classify_db_error(error: &FlagError) -> Option<(&'static str, Option<&str>)> {
    let labels = match error {
        FlagError::DatabaseError(sqlx_error, _) => {
            // is_transient_error also accepts the statement timeout 57014. The timeout check
            // therefore runs first, so should_retry_on_error does not retry a timeout.
            let err_type = if common_database::is_foreign_key_constraint_error(sqlx_error) {
                "foreign_key"
            } else if common_database::is_timeout_error(sqlx_error) {
                "timeout"
            } else if common_database::is_transient_error(sqlx_error) {
                "transient"
            } else {
                // Errors reaching this arm were built with context, bypassing
                // From<sqlx::Error>; transient/timeout ones are caught above, so
                // everything left here is genuinely unknown.
                "unknown"
            };
            (err_type, None)
        }
        // Transient faults propagated via `?` and connection-acquisition failures arrive
        // pre-classified with no raw error attached. Without this arm they fall through
        // and vanish from FLAG_DATABASE_ERROR_COUNTER.
        FlagError::DatabaseUnavailable => ("transient", None),
        FlagError::TimeoutError(timeout_type) => {
            let subtype = timeout_type.as_ref().map(|s| s.as_str());
            ("timeout", subtype)
        }
        _ => return None, // Only track database-related errors
    };

    Some(labels)
}

fn track_db_error(error_type: &str, timeout_subtype: Option<&str>, operation: &str, retried: bool) {
    let mut labels = vec![
        ("error_type".to_string(), error_type.to_string()),
        ("operation".to_string(), operation.to_string()),
        ("retried".to_string(), retried.to_string()),
    ];

    // Add timeout subtype if available
    if let Some(subtype) = timeout_subtype {
        labels.push(("timeout_type".to_string(), subtype.to_string()));
    }

    common_metrics::inc(FLAG_DATABASE_ERROR_COUNTER, &labels, 1);
}

pub(crate) fn track_unretried_db_error(error: &FlagError, operation: &str) {
    if let Some((error_type, timeout_subtype)) = classify_db_error(error) {
        track_db_error(error_type, timeout_subtype, operation, false);
    }
}

/// `operation` label values for persons DB calls. The deadline wrapper in `flag_matching.rs`
/// and the call itself both count errors in `flags_database_error_total` under this label.
/// A mismatched literal splits one operation's errors into two series and fails nothing.
pub(crate) mod db_operations {
    pub const SHOULD_WRITE_HASH_KEY_OVERRIDE: &str = "should_write_hash_key_override";
    pub const SET_HASH_KEY_OVERRIDES: &str = "set_hash_key_overrides";
    pub const GET_HASH_KEY_OVERRIDES: &str = "get_hash_key_overrides";
    pub const FETCH_PROPERTIES: &str = "fetch_properties";
    pub const FETCH_GROUP_TYPE_MAPPING: &str = "fetch_group_type_mapping";
}

/// Records `flags_hash_key_retries_total` and the `retried` label on `flags_database_error_total`
/// for one hash key override call.
///
/// `RetryIf` evaluates its condition before it checks whether any delays remain. The error alone
/// therefore does not tell an attempt whether a retry follows it. The caller can also drop the
/// call during the backoff, for example when the request times out. A failed attempt therefore
/// counts as retried only when the next attempt starts. If the call ends before that, `Drop`
/// records the error as not retried.
struct HashKeyRetryMetrics {
    team_id: TeamId,
    operation: &'static str,
    retries: usize,
    attempts: Mutex<Attempts>,
}

#[derive(Default)]
struct Attempts {
    started: usize,
    error_awaiting_retry: Option<(&'static str, Option<String>)>,
}

impl HashKeyRetryMetrics {
    fn new(team_id: TeamId, operation: &'static str, retries: usize) -> Self {
        Self {
            team_id,
            operation,
            retries,
            attempts: Mutex::default(),
        }
    }

    fn start(&self) {
        let mut attempts = self.attempts.lock().unwrap();
        if attempts.started > 0 {
            common_metrics::inc(
                FLAG_HASH_KEY_RETRIES_COUNTER,
                &[
                    ("team_id".to_string(), self.team_id.to_string()),
                    ("operation".to_string(), self.operation.to_string()),
                ],
                1,
            );
            if let Some((error_type, timeout_subtype)) = attempts.error_awaiting_retry.take() {
                track_db_error(error_type, timeout_subtype.as_deref(), self.operation, true);
            }
        }
        attempts.started += 1;
    }

    /// Records the error of the attempt that started last and returns whether `RetryIf` retries it.
    fn fail(&self, error: &FlagError) -> bool {
        let mut attempts = self.attempts.lock().unwrap();
        let will_retry = attempts.started <= self.retries && should_retry_on_error(error);
        if let Some((error_type, timeout_subtype)) = classify_db_error(error) {
            if will_retry {
                attempts.error_awaiting_retry =
                    Some((error_type, timeout_subtype.map(str::to_owned)));
            } else {
                track_db_error(error_type, timeout_subtype, self.operation, false);
            }
        }
        will_retry
    }
}

impl Drop for HashKeyRetryMetrics {
    fn drop(&mut self) {
        let error = self
            .attempts
            .get_mut()
            .ok()
            .and_then(|attempts| attempts.error_awaiting_retry.take());
        if let Some((error_type, timeout_subtype)) = error {
            track_db_error(
                error_type,
                timeout_subtype.as_deref(),
                self.operation,
                false,
            );
        }
    }
}

/// Runs a hash key override call and retries it after each of `retry_delays` while
/// `should_retry_on_error` accepts the error. `log_retry` runs for each error that gets a retry.
///
/// `HashKeyRetryMetrics::fail` is the `RetryIf` condition, so the metrics and the retry come from
/// one decision.
async fn retry_hash_key_call<T, Fut>(
    team_id: TeamId,
    operation: &'static str,
    retry_delays: Vec<Duration>,
    mut attempt: impl FnMut() -> Fut,
    log_retry: impl Fn(&FlagError),
) -> Result<T, FlagError>
where
    Fut: Future<Output = Result<T, FlagError>>,
{
    let metrics = HashKeyRetryMetrics::new(team_id, operation, retry_delays.len());
    RetryIf::spawn(
        retry_delays,
        || {
            metrics.start();
            attempt()
        },
        |e: &FlagError| {
            let will_retry = metrics.fail(e);
            if will_retry {
                log_retry(e);
            }
            will_retry
        },
    )
    .await
}

// Attempts to match a flag condition filter that depends on another flag
// evaluation result to a flag evaluation result
pub fn match_flag_value_to_flag_filter(
    filter: &PropertyFilter,
    flag_evaluation_results: &HashMap<FeatureFlagId, FlagValue>,
) -> bool {
    // Flag dependencies must use the flag_evaluates_to operator
    if filter.operator != Some(OperatorType::FlagEvaluatesTo) {
        tracing::error!(
            "Flag filter operator for property type Flag must be `flag_evaluates_to`, skipping flag value matching: {:?}",
            filter
        );
        return false;
    }

    let Some(flag_id) = filter.get_feature_flag_id() else {
        return false;
    };

    let Some(flag_value) = flag_evaluation_results.get(&flag_id) else {
        return false;
    };

    match filter.value {
        Some(Value::Bool(true)) => flag_value != &FlagValue::Boolean(false),
        Some(Value::Bool(false)) => flag_value == &FlagValue::Boolean(false),
        Some(Value::String(ref s)) => {
            matches!(flag_value, FlagValue::String(flag_str) if flag_str == s)
        }
        _ => false,
    }
}

/// Returns the flag this filter depends on when that flag failed earlier in this request.
/// A flag with a recorded result never counts. An unsupported flag fails, but it is
/// pre-seeded `false`, so its dependents still read it as false. A malformed filter never
/// counts either, because `match_flag_value_to_flag_filter` rejects it for every value.
pub fn failed_flag_dependency(
    filter: &PropertyFilter,
    flag_evaluation_results: &HashMap<FeatureFlagId, FlagValue>,
    failed_flag_ids: &HashSet<FeatureFlagId>,
) -> Option<FeatureFlagId> {
    if failed_flag_ids.is_empty()
        || filter.operator != Some(OperatorType::FlagEvaluatesTo)
        || !matches!(filter.value, Some(Value::Bool(_) | Value::String(_)))
    {
        return None;
    }
    let flag_id = filter.get_feature_flag_id()?;
    (!flag_evaluation_results.contains_key(&flag_id) && failed_flag_ids.contains(&flag_id))
        .then_some(flag_id)
}

/// Retrieves feature flag hash key overrides for a list of distinct IDs with retry logic.
///
/// This function fetches any hash key overrides that have been set for feature flags
/// for the given distinct IDs. It handles priority by giving precedence to the first
/// distinct ID in the list. The operation is retried once (2 total attempts) with
/// exponential backoff on transient database errors.
///
/// `pool_name` labels the connection metrics. Callers pass either the persons reader or the
/// persons writer, and both arrive here as the same type, so only the caller knows which.
pub async fn get_feature_flag_hash_key_overrides(
    reader: PostgresReader,
    pool_name: &'static str,
    persons_writer: PostgresWriter,
    team_id: TeamId,
    distinct_id_and_hash_key_override: Vec<String>,
) -> Result<HashMap<String, String>, FlagError> {
    // Track hash key override lookups for testing
    #[cfg(test)]
    increment_hash_key_override_lookup_count();

    let retry_delays: Vec<Duration> = ExponentialBackoff::from_millis(50)
        .max_delay(Duration::from_millis(300))
        .take(1) // 1 retry = 2 total attempts
        .map(jitter)
        .collect();

    retry_hash_key_call(
        team_id,
        db_operations::GET_HASH_KEY_OVERRIDES,
        retry_delays,
        || {
            try_get_feature_flag_hash_key_overrides(
                &reader,
                pool_name,
                &persons_writer,
                team_id,
                &distinct_id_and_hash_key_override,
            )
        },
        |e| {
            tracing::warn!(
                team_id = %team_id,
                distinct_ids = ?distinct_id_and_hash_key_override,
                error = ?e,
                "Hash key override query failed, will retry"
            );
        },
    )
    .await
}

/// Internal function that performs the actual hash key override retrieval.
/// This is separated to make it easy to retry with tokio-retry.
async fn try_get_feature_flag_hash_key_overrides(
    reader: &PostgresReader,
    pool_name: &'static str,
    persons_writer: &PostgresWriter,
    team_id: TeamId,
    distinct_id_and_hash_key_override: &[String],
) -> Result<HashMap<String, String>, FlagError> {
    let mut feature_flag_hash_key_overrides = HashMap::new();
    let mut conn =
        get_connection_with_metrics(reader, pool_name, "get_feature_flag_hash_key_overrides")
            .await?;

    let query_start = Instant::now();
    let rows = fetch_override_rows(&mut conn, team_id, distinct_id_and_hash_key_override).await?;
    let query_duration = query_start.elapsed();

    // Nothing below needs the reader connection, and this function is on the hottest path.
    drop(conn);

    // The join keeps a person row even when it has no override, so no rows at all means this pool
    // could not see any of the requested distinct IDs. That is a different miss from seeing at
    // least one and finding no override.
    let any_distinct_id_found = !rows.is_empty();

    if query_duration.as_millis() > 200 {
        warn!(
            duration_ms = query_duration.as_millis(),
            team_id = team_id,
            distinct_id_count = distinct_id_and_hash_key_override.len(),
            sql_summary = "SELECT person_id, hash_key overrides with LEFT JOIN",
            "Slow hash override lookup query detected"
        );
    } else {
        debug!(
            duration_ms = query_duration.as_millis(),
            team_id = team_id,
            distinct_id_count = distinct_id_and_hash_key_override.len(),
            "Hash override lookup query completed"
        );
    }

    // Process results to build person mapping and collect any existing overrides
    let mut person_id_to_distinct_id = HashMap::new();
    let mut overrides = Vec::new();

    for row in rows {
        let person_id: PersonId = row.get("person_id");
        let distinct_id: String = row.get("distinct_id");

        person_id_to_distinct_id.insert(person_id, distinct_id);

        // Collect overrides where they exist
        if let Some((feature_flag_key, hash_key)) = row_override(&row) {
            overrides.push((feature_flag_key, hash_key, person_id));
        }
    }

    // Sort and process overrides, with the distinct_id at the start of the array having priority
    // We want the highest priority to go last in sort order, so it's the latest update in the hashmap
    let mut sorted_overrides = overrides;
    if !distinct_id_and_hash_key_override.is_empty() {
        sorted_overrides.sort_by_key(|(_, _, person_id)| {
            if person_id_to_distinct_id.get(person_id)
                == Some(&distinct_id_and_hash_key_override[0])
            {
                std::cmp::Ordering::Greater
            } else {
                std::cmp::Ordering::Less
            }
        });
    }

    for (feature_flag_key, hash_key, _) in sorted_overrides {
        feature_flag_hash_key_overrides.insert(feature_flag_key, hash_key);
    }

    // Track whether query returned overrides to understand cache optimization potential
    let result_label = if feature_flag_hash_key_overrides.is_empty() {
        "empty"
    } else {
        "has_overrides"
    };
    common_metrics::inc(
        FLAG_HASH_KEY_QUERY_RESULT,
        &[("result".to_string(), result_label.to_string())],
        1,
    );

    if pool_name == pool_names::PERSONS_READER
        && feature_flag_hash_key_overrides.is_empty()
        && rand::thread_rng().gen_bool(REPLICA_STALENESS_SAMPLE_RATE)
    {
        // Detached, so a sampled request never waits on the primary. The cost is that the check
        // runs slightly after the served read, so an override that replicates in that gap counts
        // as a disagreement. That inflates the rate rather than hiding lag.
        let persons_writer = persons_writer.clone();
        let distinct_ids = distinct_id_and_hash_key_override.to_vec();
        tokio::spawn(async move {
            check_primary_for_stale_empty(
                &persons_writer,
                team_id,
                &distinct_ids,
                any_distinct_id_found,
            )
            .await;
        });
    }

    Ok(feature_flag_hash_key_overrides)
}

/// Tells apart the two causes of an empty result: the person has no override, or the row has not
/// replicated. Only the second is wrong, and the served metrics cannot separate them.
///
/// The answer is counted and thrown away. Serving it would make this a partial fix, and the rate
/// would then measure the fix.
///
/// Diagnostic. Remove once the rate is known.
async fn check_primary_for_stale_empty(
    persons_writer: &PostgresWriter,
    team_id: TeamId,
    distinct_id_and_hash_key_override: &[String],
    any_distinct_id_found_on_replica: bool,
) {
    let check = primary_has_override(persons_writer, team_id, distinct_id_and_hash_key_override);

    // Both booleans test "any requested distinct ID", not one specific person, so with several
    // distinct IDs (the $anon_distinct_id case) the split between the two disagree buckets is
    // approximate: the replica may have seen one distinct ID while missing the person that owns the
    // primary override. The aggregate disagreement rate is unaffected.
    let outcome = match timeout(REPLICA_STALENESS_CHECK_TIMEOUT, check).await {
        Err(_) => "timeout",
        Ok(Err(_)) => "error",
        Ok(Ok(false)) => "agree",
        Ok(Ok(true)) if any_distinct_id_found_on_replica => "disagree_override_missing",
        Ok(Ok(true)) => "disagree_person_missing",
    };

    common_metrics::inc(
        FLAG_HASH_KEY_REPLICA_CHECK,
        &[("outcome".to_string(), outcome.to_string())],
        1,
    );
}

/// The served read and the staleness check must agree on what counts as an override, or the
/// disagree metrics measure the gap between two tests rather than replica lag.
fn row_override(row: &PgRow) -> Option<(String, String)> {
    let feature_flag_key: String = row.try_get("feature_flag_key").ok()?;
    let hash_key: String = row.try_get("hash_key").ok()?;
    Some((feature_flag_key, hash_key))
}

/// Both the served read and the staleness check run this, so a disagreement between them means
/// the data differed, not the question.
async fn fetch_override_rows(
    conn: &mut PgConnection,
    team_id: TeamId,
    distinct_id_and_hash_key_override: &[String],
) -> Result<Vec<PgRow>, FlagError> {
    // Get person data and their hash key overrides in one query.
    // Every cookieless visitor shares the sentinel, so a stored sentinel would give all of them
    // the same variant. The join skips those rows and still keeps the person row.
    let hash_override_query = r#"
            SELECT
                ppd.person_id,
                ppd.distinct_id,
                fhko.feature_flag_key,
                fhko.hash_key
            FROM posthog_persondistinctid ppd
            LEFT JOIN posthog_featureflaghashkeyoverride fhko
                ON fhko.person_id = ppd.person_id
                AND fhko.team_id = ppd.team_id
                AND fhko.hash_key <> $3
            WHERE ppd.team_id = $1
                AND ppd.distinct_id = ANY($2)
                AND ppd.is_deleted = false
        "#;

    sqlx::query(hash_override_query)
        .bind(team_id)
        .bind(distinct_id_and_hash_key_override)
        .bind(COOKIELESS_SENTINEL_VALUE)
        .fetch_all(&mut *conn)
        .await
        .map_err(FlagError::from)
}

/// The write check and the write both run this query, so they agree on which overrides exist.
/// A stored cookieless sentinel does not count as an override, so the bulk insert replaces it
/// with a real key.
const PERSONS_WITH_EXISTING_OVERRIDES_QUERY: &str = r#"
    SELECT DISTINCT
        p.person_id,
        existing.feature_flag_key
    FROM posthog_persondistinctid p
    LEFT JOIN posthog_featureflaghashkeyoverride existing
        ON existing.person_id = p.person_id AND existing.team_id = p.team_id
        AND existing.hash_key <> $3
    WHERE p.team_id = $1
        AND p.distinct_id = ANY($2)
        AND p.is_deleted = false
        AND EXISTS (SELECT 1 FROM posthog_person WHERE id = p.person_id AND team_id = p.team_id AND is_deleted = false)
"#;

/// Asks the primary whether any override exists for these distinct IDs.
async fn primary_has_override(
    persons_writer: &PostgresWriter,
    team_id: TeamId,
    distinct_id_and_hash_key_override: &[String],
) -> Result<bool, FlagError> {
    let mut conn = get_connection_with_metrics(
        persons_writer,
        pool_names::PERSONS_WRITER,
        // Its own operation name, so these stay out of the served read's metrics.
        "get_feature_flag_hash_key_overrides_replica_check",
    )
    .await?;

    let rows = fetch_override_rows(&mut conn, team_id, distinct_id_and_hash_key_override).await?;

    Ok(rows.iter().any(|row| row_override(row).is_some()))
}

/// Sets feature flag hash key overrides for a list of distinct IDs.
///
/// This function creates hash key overrides for all active feature flags that have
/// experience continuity enabled. It includes retry logic for handling race conditions
/// with person deletions.
pub async fn set_feature_flag_hash_key_overrides(
    router: &PostgresRouter,
    team_id: TeamId,
    distinct_ids: Vec<String>,
    hash_key_override: String,
) -> Result<bool, FlagError> {
    let retry_delays: Vec<Duration> = ExponentialBackoff::from_millis(100)
        .max_delay(Duration::from_millis(300))
        .take(2)
        .map(jitter) // Add jitter to prevent thundering herd
        .collect();

    retry_hash_key_call(
        team_id,
        db_operations::SET_HASH_KEY_OVERRIDES,
        retry_delays,
        || {
            try_set_feature_flag_hash_key_overrides(
                router,
                team_id,
                &distinct_ids,
                &hash_key_override,
            )
        },
        |e| {
            if flag_error_is_foreign_key_constraint(e) {
                tracing::info!(
                    team_id = %team_id,
                    distinct_ids = ?distinct_ids,
                    error = ?e,
                    "Hash key override setting failed due to a person deletion race condition, will retry"
                );
            }
        },
    )
    .await
}

/// Internal function that performs the actual hash key override setting.
/// This is separated to make it easy to retry with tokio-retry.
async fn try_set_feature_flag_hash_key_overrides(
    router: &PostgresRouter,
    team_id: TeamId,
    distinct_ids: &[String],
    hash_key_override: &str,
) -> Result<bool, FlagError> {
    // Get connection from persons writer for the transaction
    let mut persons_conn = get_writer_connection_with_metrics(
        router.get_persons_writer(),
        pool_names::PERSONS_WRITER,
        "set_feature_flag_hash_key_overrides",
    )
    .await?;
    let mut transaction = persons_conn.begin().await?;
    // The foreign key from posthog_featureflaghashkeyoverride to posthog_person is deferred.
    // Postgres checks deferred keys at COMMIT. statement_timeout does not cover COMMIT. When
    // another transaction holds FOR UPDATE on the person row, as a delete does, the commit waits
    // until that transaction ends or the request times out. This statement moves the check into
    // the INSERT, where statement_timeout applies.
    sqlx::query("SET CONSTRAINTS ALL IMMEDIATE")
        .execute(&mut *transaction)
        .await?;

    // Query 1: Get all person data - person_ids + existing overrides + validation (person pool)

    // Query 2: Get all active feature flags with experience continuity (non-person pool)
    let flags_query = r#"
            SELECT flag.key
            FROM posthog_featureflag flag
            JOIN posthog_team team ON flag.team_id = team.id
            WHERE team.id = $1
                AND flag.ensure_experience_continuity = TRUE
                AND flag.active = TRUE
                AND flag.deleted = FALSE
        "#;

    // Query 3: Bulk insert hash key overrides (person pool)
    // A stored sentinel is not a real continuity key, so this replaces it. Any other stored
    // key is one the person bucketed on, so it stays.
    let bulk_insert_query = r#"
            INSERT INTO posthog_featureflaghashkeyoverride (team_id, person_id, feature_flag_key, hash_key)
            SELECT $1, person_id, flag_key, $2
            FROM UNNEST($3::bigint[], $4::text[]) AS t(person_id, flag_key)
            ON CONFLICT (team_id, person_id, feature_flag_key) DO UPDATE
                SET hash_key = EXCLUDED.hash_key
                WHERE posthog_featureflaghashkeyoverride.hash_key = $5
        "#;

    let result: Result<u64, FlagError> = async {
        // Step 1: Get all person data (person_ids + existing overrides + validation)
        let person_query_labels = [
            (
                "query".to_string(),
                "person_data_with_overrides".to_string(),
            ),
            (
                "operation".to_string(),
                db_operations::SET_HASH_KEY_OVERRIDES.to_string(),
            ),
            ("pool".to_string(), pool_names::PERSONS_WRITER.to_string()),
            ("team_id".to_string(), team_id.to_string()),
        ];
        let person_query_start = Instant::now();
        let person_query_timer =
            common_metrics::timing_guard(FLAG_PERSON_QUERY_TIME, &person_query_labels);
        let person_data_rows = sqlx::query(PERSONS_WITH_EXISTING_OVERRIDES_QUERY)
            .bind(team_id)
            .bind(distinct_ids)
            .bind(COOKIELESS_SENTINEL_VALUE)
            .fetch_all(&mut *transaction)
            .await
            .map_err(FlagError::from)?;
        person_query_timer.fin();
        let person_query_duration = person_query_start.elapsed();

        if person_query_duration.as_millis() > 200 {
            warn!(
                duration_ms = person_query_duration.as_millis(),
                team_id = team_id,
                distinct_id_count = distinct_ids.len(),
                sql_summary = "SELECT person_id with LEFT JOIN to existing hash_key overrides",
                "Slow person data query detected in set_hash_key_overrides"
            );
        } else {
            debug!(
                duration_ms = person_query_duration.as_millis(),
                team_id = team_id,
                distinct_id_count = distinct_ids.len(),
                "Person data query completed in set_hash_key_overrides"
            );
        }

        if person_data_rows.is_empty() {
            return Ok(0); // No persons found, nothing to insert
        }

        // Process person data - collect person_ids and existing overrides
        let mut person_ids = HashSet::new();
        let mut existing_overrides = HashSet::new();

        for row in person_data_rows {
            let person_id: i64 = row.get("person_id");
            person_ids.insert(person_id);

            // Handle existing overrides (can be NULL from LEFT JOIN)
            if let Ok(flag_key) = row.try_get::<String, _>("feature_flag_key") {
                existing_overrides.insert((person_id, flag_key));
            }
        }

        let person_ids_vec: Vec<i64> = person_ids.into_iter().collect();

        // Step 2: Get active feature flags (from non-person pool)
        // Get separate connection for non-persons query
        let mut non_persons_conn = get_connection_with_metrics(
            router.get_non_persons_reader(),
            pool_names::NON_PERSONS_READER,
            db_operations::SET_HASH_KEY_OVERRIDES,
        )
        .await
        .map_err(FlagError::from)?;

        let flags_labels = [
            (
                "query".to_string(),
                "active_flags_with_continuity".to_string(),
            ),
            (
                "operation".to_string(),
                db_operations::SET_HASH_KEY_OVERRIDES.to_string(),
            ),
            (
                "pool".to_string(),
                pool_names::NON_PERSONS_READER.to_string(),
            ),
            ("team_id".to_string(), team_id.to_string()),
        ];
        let flags_query_start = Instant::now();
        let flags_query_timer =
            common_metrics::timing_guard(FLAG_DEFINITION_QUERY_TIME, &flags_labels);
        let flag_rows = sqlx::query(flags_query)
            .bind(team_id)
            .fetch_all(&mut *non_persons_conn)
            .await
            .map_err(FlagError::from)?;
        flags_query_timer.fin();
        let flags_query_duration = flags_query_start.elapsed();

        if flags_query_duration.as_millis() > 200 {
            warn!(
                duration_ms = flags_query_duration.as_millis(),
                team_id = team_id,
                sql_summary =
                    "SELECT active feature flags with ensure_experience_continuity = true",
                "Slow active flags query detected in set_hash_key_overrides"
            );
        } else {
            debug!(
                duration_ms = flags_query_duration.as_millis(),
                team_id = team_id,
                "Active flags query completed in set_hash_key_overrides"
            );
        }

        let flag_keys: Vec<String> = flag_rows
            .iter()
            .map(|row| row.get::<String, _>("key"))
            .collect();

        if flag_keys.is_empty() {
            return Ok(0); // No flags to override
        }

        // Step 3: Build values for bulk insert
        // Create all person-flag combinations that need to be inserted
        let values_to_insert: Vec<(i64, String)> = person_ids_vec
            .iter()
            .flat_map(|pid| flag_keys.iter().map(move |fk| (*pid, fk.clone())))
            .filter(|(pid, fk)| {
                // Skip if override already exists
                !existing_overrides.contains(&(*pid, fk.clone()))
            })
            .collect();

        if values_to_insert.is_empty() {
            return Ok(0); // Nothing to insert
        }

        // Separate the tuples into parallel arrays for UNNEST
        let (person_ids_to_insert, flag_keys_to_insert): (Vec<i64>, Vec<String>) =
            values_to_insert.into_iter().unzip();

        // Step 4: Bulk insert (person pool)
        let insert_labels = [
            ("query".to_string(), "bulk_insert_overrides".to_string()),
            (
                "operation".to_string(),
                db_operations::SET_HASH_KEY_OVERRIDES.to_string(),
            ),
            ("pool".to_string(), pool_names::PERSONS_WRITER.to_string()),
            ("team_id".to_string(), team_id.to_string()),
        ];
        let insert_start = Instant::now();
        let insert_timer = common_metrics::timing_guard(FLAG_PERSON_QUERY_TIME, &insert_labels);
        let result = sqlx::query(bulk_insert_query)
            .bind(team_id)
            .bind(hash_key_override)
            .bind(&person_ids_to_insert)
            .bind(&flag_keys_to_insert)
            .bind(COOKIELESS_SENTINEL_VALUE)
            .execute(&mut *transaction)
            .await
            .map_err(FlagError::from)?;
        insert_timer.fin();
        let insert_duration = insert_start.elapsed();

        if insert_duration.as_millis() > 200 {
            warn!(
                duration_ms = insert_duration.as_millis(),
                team_id = team_id,
                row_count = person_ids_to_insert.len(),
                sql_summary = "INSERT INTO hash_key_override with UNNEST bulk insert",
                "Slow bulk insert query detected in set_hash_key_overrides"
            );
        } else {
            debug!(
                duration_ms = insert_duration.as_millis(),
                team_id = team_id,
                row_count = person_ids_to_insert.len(),
                "Bulk insert query completed in set_hash_key_overrides"
            );
        }

        Ok(result.rows_affected())
    }
    .await;

    match result {
        Ok(rows_affected) => {
            // Commit the transaction if successful
            transaction.commit().await.map_err(|e| {
                FlagError::DatabaseError(e, Some("Failed to commit transaction".to_string()))
            })?;
            Ok(rows_affected > 0)
        }
        Err(e) => {
            // Rollback the transaction on error
            transaction.rollback().await.map_err(|e| {
                FlagError::DatabaseError(e, Some("Failed to rollback transaction".to_string()))
            })?;
            Err(e)
        }
    }
}

/// Checks if hash key overrides should be written for a given distinct ID.
///
/// This function determines if there are any active feature flags with experience
/// continuity enabled that don't already have hash key overrides for the given
/// distinct ID.
pub async fn should_write_hash_key_override(
    router: &PostgresRouter,
    team_id: TeamId,
    distinct_id: String,
    hash_key_override: String,
) -> Result<bool, FlagError> {
    let retry_delays: Vec<Duration> = ExponentialBackoff::from_millis(100)
        .max_delay(Duration::from_millis(300))
        .take(2)
        .map(jitter) // Add jitter to prevent thundering herd
        .collect();
    let distinct_ids = vec![distinct_id, hash_key_override];

    retry_hash_key_call(
        team_id,
        db_operations::SHOULD_WRITE_HASH_KEY_OVERRIDE,
        retry_delays,
        || try_should_write_hash_key_override(router, team_id, &distinct_ids),
        |_| {},
    )
    .await
}

/// Internal function that performs the actual hash key override check.
/// This is separated to make it easy to retry with tokio-retry.
async fn try_should_write_hash_key_override(
    router: &PostgresRouter,
    team_id: TeamId,
    distinct_ids: &[String],
) -> Result<bool, FlagError> {
    // Query 1: Get person_ids and existing overrides from person pool in one shot

    // Query 2: Get feature flags from non-person pool
    let flags_query = r#"
        SELECT key FROM posthog_featureflag flag
        JOIN posthog_team team ON flag.team_id = team.id
        WHERE team.id = $1
            AND flag.ensure_experience_continuity = TRUE
            AND flag.active = TRUE
            AND flag.deleted = FALSE
    "#;

    // Log pool states before attempting connections
    let persons_reader_stats = router.get_persons_reader().get_pool_stats();
    let non_persons_reader_stats = router.get_non_persons_reader().get_pool_stats();

    if let (Some(pr_stats), Some(npr_stats)) = (&persons_reader_stats, &non_persons_reader_stats) {
        let pr_utilization =
            (pr_stats.size - pr_stats.num_idle as u32) as f64 / pr_stats.size as f64;
        let npr_utilization =
            (npr_stats.size - npr_stats.num_idle as u32) as f64 / npr_stats.size as f64;

        if pr_utilization > 0.8 || npr_utilization > 0.8 {
            warn!(
                team_id = %team_id,
                distinct_ids = ?distinct_ids,
                persons_reader_pool_size = pr_stats.size,
                persons_reader_idle = pr_stats.num_idle,
                persons_reader_utilization_pct = pr_utilization * 100.0,
                non_persons_reader_pool_size = npr_stats.size,
                non_persons_reader_idle = npr_stats.num_idle,
                non_persons_reader_utilization_pct = npr_utilization * 100.0,
                "High pool utilization before should_write_hash_key_override"
            );
        }
    }

    // Step 1: Get person data and existing overrides (scoped connection)
    let person_data_rows = {
        let persons_conn_start = Instant::now();
        let mut persons_conn = get_connection_with_metrics(
            router.get_persons_reader(),
            pool_names::PERSONS_READER,
            "should_write_check",
        )
        .await
        .map_err(FlagError::from)?;
        let persons_conn_acquisition_time = persons_conn_start.elapsed();

        if persons_conn_acquisition_time > Duration::from_millis(100) {
            warn!(
                team_id = %team_id,
                pool = pool_names::PERSONS_READER,
                acquisition_ms = persons_conn_acquisition_time.as_millis(),
                "Slow connection acquisition from persons_reader pool"
            );
        }

        let person_query_labels = [
            (
                "query".to_string(),
                "person_data_with_overrides".to_string(),
            ),
            ("operation".to_string(), "should_write_check".to_string()),
            ("pool".to_string(), pool_names::PERSONS_READER.to_string()),
            ("team_id".to_string(), team_id.to_string()),
        ];
        let person_query_timer =
            common_metrics::timing_guard(FLAG_PERSON_QUERY_TIME, &person_query_labels);
        let person_data_rows = sqlx::query(PERSONS_WITH_EXISTING_OVERRIDES_QUERY)
            .bind(team_id)
            .bind(distinct_ids)
            .bind(COOKIELESS_SENTINEL_VALUE)
            .fetch_all(&mut *persons_conn)
            .await
            .map_err(|e| {
                // Track timeout errors with detailed context
                if common_database::is_timeout_error(&e) {
                    let timeout_type =
                        common_database::extract_timeout_type(&e).unwrap_or("unknown_timeout");

                    common_metrics::inc(
                        FLAG_DATABASE_ERROR_COUNTER,
                        &[
                            ("error_type".to_string(), "timeout".to_string()),
                            ("timeout_type".to_string(), timeout_type.to_string()),
                            ("pool".to_string(), pool_names::PERSONS_READER.to_string()),
                            (
                                "operation".to_string(),
                                db_operations::SHOULD_WRITE_HASH_KEY_OVERRIDE.to_string(),
                            ),
                        ],
                        1,
                    );

                    warn!(
                        team_id = %team_id,
                        pool = pool_names::PERSONS_READER,
                        timeout_type = timeout_type,
                        error = ?e,
                        "Query timed out on persons_reader pool"
                    );
                }
                FlagError::DatabaseError(e, Some("Failed to fetch person data".to_string()))
            })?;
        person_query_timer.fin();

        person_data_rows
    };

    // If no person_ids found, there's nothing to check
    if person_data_rows.is_empty() {
        return Ok(false);
    }

    // Step 2: Process data without holding any connections
    let existing_flag_keys: HashSet<String> = person_data_rows
        .iter()
        .filter_map(|row| row.try_get::<String, _>("feature_flag_key").ok())
        .collect();

    // Step 3: Get active feature flags with experience continuity (scoped connection)
    let flag_rows = {
        let non_persons_conn_start = Instant::now();
        let mut non_persons_conn = get_connection_with_metrics(
            router.get_non_persons_reader(),
            pool_names::NON_PERSONS_READER,
            "should_write_check",
        )
        .await
        .map_err(FlagError::from)?;
        let non_persons_conn_acquisition_time = non_persons_conn_start.elapsed();

        if non_persons_conn_acquisition_time > Duration::from_millis(100) {
            warn!(
                team_id = %team_id,
                pool = pool_names::NON_PERSONS_READER,
                acquisition_ms = non_persons_conn_acquisition_time.as_millis(),
                "Slow connection acquisition from non_persons_reader pool"
            );
        }

        let flags_labels = [
            (
                "query".to_string(),
                "active_flags_with_continuity".to_string(),
            ),
            ("operation".to_string(), "should_write_check".to_string()),
            (
                "pool".to_string(),
                pool_names::NON_PERSONS_READER.to_string(),
            ),
            ("team_id".to_string(), team_id.to_string()),
        ];
        let flags_query_timer =
            common_metrics::timing_guard(FLAG_DEFINITION_QUERY_TIME, &flags_labels);
        let rows = sqlx::query(flags_query)
            .bind(team_id)
            .fetch_all(&mut *non_persons_conn)
            .await
            .map_err(|e| {
                // Track timeout errors with detailed context
                if common_database::is_timeout_error(&e) {
                    let timeout_type =
                        common_database::extract_timeout_type(&e).unwrap_or("unknown_timeout");

                    common_metrics::inc(
                        FLAG_DATABASE_ERROR_COUNTER,
                        &[
                            ("error_type".to_string(), "timeout".to_string()),
                            ("timeout_type".to_string(), timeout_type.to_string()),
                            (
                                "pool".to_string(),
                                pool_names::NON_PERSONS_READER.to_string(),
                            ),
                            (
                                "operation".to_string(),
                                db_operations::SHOULD_WRITE_HASH_KEY_OVERRIDE.to_string(),
                            ),
                        ],
                        1,
                    );

                    warn!(
                        team_id = %team_id,
                        pool = pool_names::NON_PERSONS_READER,
                        timeout_type = timeout_type,
                        error = ?e,
                        "Query timed out on non_persons_reader pool"
                    );
                }
                FlagError::DatabaseError(e, Some("Failed to fetch flags".to_string()))
            })?;
        flags_query_timer.fin();

        rows
        // Connection automatically released here when non_persons_conn goes out of scope
    };

    // Step 4: Check if there are any flags that don't have overrides
    for row in flag_rows {
        let flag_key: String = row.get("key");
        if !existing_flag_keys.contains(&flag_key) {
            return Ok(true); // Found a flag without override
        }
    }

    Ok::<bool, FlagError>(false) // All flags have overrides
}

#[cfg(test)]
pub fn get_fetch_calls_count() -> u64 {
    FETCH_CALLS.with(|counter| *counter.borrow())
}

#[cfg(test)]
pub fn reset_fetch_calls_count() {
    FETCH_CALLS.with(|counter| *counter.borrow_mut() = 0);
}

#[cfg(test)]
pub fn increment_fetch_calls_count() {
    FETCH_CALLS.with(|counter| *counter.borrow_mut() += 1);
}

#[cfg(test)]
pub fn get_hash_key_override_lookup_count() -> u64 {
    HASH_KEY_OVERRIDE_LOOKUPS.with(|counter| *counter.borrow())
}

#[cfg(test)]
pub fn reset_hash_key_override_lookup_count() {
    HASH_KEY_OVERRIDE_LOOKUPS.with(|counter| *counter.borrow_mut() = 0);
}

#[cfg(test)]
fn increment_hash_key_override_lookup_count() {
    HASH_KEY_OVERRIDE_LOOKUPS.with(|counter| *counter.borrow_mut() += 1);
}

#[cfg(test)]
mod tests {
    use std::sync::atomic::Ordering;

    use common_database::{get_pool_with_config, PoolConfig};
    use futures::FutureExt;
    use metrics_util::debugging::DebuggingRecorder;
    use rstest::rstest;
    use serde_json::json;

    use crate::{
        config::DEFAULT_TEST_CONFIG,
        flags::flag_models::{FeatureFlag, FeatureFlagRow, FlagFilters},
        mock,
        properties::property_models::{OperatorType, PropertyFilter, PropertyType},
        utils::test_utils::{counter_total, CountingFailingClient, TestContext},
    };

    use super::*;

    #[tokio::test]
    async fn test_set_feature_flag_hash_key_overrides_success() {
        let context = TestContext::new(None).await;
        let team = context.insert_new_team(None).await.unwrap();
        let distinct_id = "user2".to_string();

        // Insert person
        context
            .insert_person(team.id, distinct_id.clone(), None)
            .await
            .unwrap();

        // Create a feature flag with ensure_experience_continuity = true
        let flag = mock!(FeatureFlag,
            team_id: team.id,
            filters: FlagFilters {
                groups: vec![],
                ..Default::default()
            },
            ensure_experience_continuity: Some(true)
        );

        // Convert flag to FeatureFlagRow
        let flag_row = mock!(FeatureFlagRow, from: flag);

        // Insert the feature flag into the database
        context.insert_flag(team.id, Some(flag_row)).await.unwrap();

        // Set hash key override
        let router = context.create_postgres_router();
        set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            vec![distinct_id.clone()],
            "hash_key_2".to_string(),
        )
        .await
        .unwrap();

        // Retrieve hash key overrides
        let overrides = context
            .get_feature_flag_hash_key_overrides(team.id, vec![distinct_id.clone()])
            .await
            .unwrap();

        assert_eq!(
            overrides.get("test_flag"),
            Some(&"hash_key_2".to_string()),
            "Hash key override should match the set value"
        );
    }

    #[tokio::test]
    async fn test_set_feature_flag_hash_key_overrides_with_multiple_persons() {
        let context = TestContext::new(None).await;
        let team = context
            .insert_new_team(None)
            .await
            .expect("Failed to insert team");

        // Create 3 persons
        let person1_id = context
            .insert_person(
                team.id,
                "batch_user1".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person1");

        let _person2_id = context
            .insert_person(
                team.id,
                "batch_user2".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person2");

        let _person3_id = context
            .insert_person(
                team.id,
                "batch_user3".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person3");

        // Add additional distinct_id for person1 to test multiple distinct_ids per person
        let mut conn = context
            .get_persons_connection()
            .await
            .expect("Failed to get connection");
        let mut transaction = conn.begin().await.expect("Failed to begin transaction");

        sqlx::query(
            "INSERT INTO posthog_persondistinctid (team_id, person_id, distinct_id, version)
             VALUES ($1, $2, $3, 0)",
        )
        .bind(team.id)
        .bind(person1_id)
        .bind("batch_user1_alt")
        .execute(&mut *transaction)
        .await
        .expect("Failed to add alt distinct_id");

        transaction
            .commit()
            .await
            .expect("Failed to commit transaction");

        // Create 4 feature flags with experience continuity
        for i in 1..=4 {
            let flag_row = FeatureFlagRow {
                id: 10000 + i,
                team_id: team.id,
                name: Some(format!("Batch Test Flag {i}")),
                key: format!("batch_flag_{i}"),
                filters: json!({"groups": [{"rollout_percentage": 50}]}),
                deleted: false,
                active: true,
                ensure_experience_continuity: Some(true),
                version: Some(1),
                evaluation_runtime: None,
                evaluation_tags: None,
                bucketing_identifier: None,
                has_experiment: false,
            };
            context
                .insert_flag(team.id, Some(flag_row))
                .await
                .unwrap_or_else(|_| panic!("Failed to insert flag {i}"));
        }

        // Test batch insert with 4 distinct_ids (3 persons, 1 with alt)
        let distinct_ids = vec![
            "batch_user1".to_string(),
            "batch_user1_alt".to_string(),
            "batch_user2".to_string(),
            "batch_user3".to_string(),
        ];
        let hash_key = "batch_hash_key".to_string();

        let router = context.create_postgres_router();
        let result = set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            distinct_ids.clone(),
            hash_key.clone(),
        )
        .await
        .expect("Failed to set hash key overrides");

        assert!(result, "Should have written overrides");

        // Verify all combinations were created correctly
        // Should create overrides for all distinct_ids
        for distinct_id in &distinct_ids {
            let overrides = context
                .get_feature_flag_hash_key_overrides(team.id, vec![distinct_id.clone()])
                .await
                .unwrap_or_else(|_| panic!("Failed to get overrides for {distinct_id}"));

            assert_eq!(
                overrides.len(),
                4,
                "Should have 4 overrides for distinct_id {distinct_id}"
            );

            for i in 1..=4 {
                let flag_key = format!("batch_flag_{i}");
                assert_eq!(
                    overrides.get(&flag_key),
                    Some(&hash_key),
                    "Override for {flag_key} should be set for distinct_id {distinct_id}"
                );
            }
        }

        // Verify the actual count in the database
        // batch_user1 and batch_user1_alt map to same person, so 3 persons * 4 flags = 12 overrides
        let mut conn = context
            .get_persons_connection()
            .await
            .expect("Failed to get connection");
        let count: i64 = sqlx::query_scalar(
            "SELECT COUNT(*) FROM posthog_featureflaghashkeyoverride
             WHERE team_id = $1 AND hash_key = $2",
        )
        .bind(team.id)
        .bind(&hash_key)
        .fetch_one(&mut *conn)
        .await
        .expect("Failed to count overrides");

        assert_eq!(
            count, 12,
            "Should have exactly 12 overrides in database (3 persons * 4 flags)"
        );
    }

    #[tokio::test]
    async fn test_set_feature_flag_hash_key_overrides_with_with_existing_overrides() {
        let context = TestContext::new(None).await;
        let team = context
            .insert_new_team(None)
            .await
            .expect("Failed to insert team");

        // Create 2 persons
        let person1_id = context
            .insert_person(
                team.id,
                "existing_user1".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person1");

        let _person2_id = context
            .insert_person(
                team.id,
                "existing_user2".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person2");

        // Create 3 flags
        for i in 1..=3 {
            let flag_row = FeatureFlagRow {
                id: 20000 + i,
                team_id: team.id,
                name: Some(format!("Existing Test Flag {i}")),
                key: format!("existing_flag_{i}"),
                filters: json!({"groups": [{"rollout_percentage": 50}]}),
                deleted: false,
                active: true,
                ensure_experience_continuity: Some(true),
                version: Some(1),
                evaluation_runtime: None,
                evaluation_tags: None,
                bucketing_identifier: None,
                has_experiment: false,
            };
            context
                .insert_flag(team.id, Some(flag_row))
                .await
                .unwrap_or_else(|_| panic!("Failed to insert flag {i}"));
        }

        // Manually insert some existing overrides
        let mut conn = context
            .get_persons_connection()
            .await
            .expect("Failed to get connection");
        let mut transaction = conn.begin().await.expect("Failed to begin transaction");

        // Person1 has override for flag 1
        sqlx::query(
            "INSERT INTO posthog_featureflaghashkeyoverride (team_id, person_id, feature_flag_key, hash_key)
             VALUES ($1, $2, $3, $4)",
        )
        .bind(team.id)
        .bind(person1_id)
        .bind("existing_flag_1")
        .bind("old_hash")
        .execute(&mut *transaction)
        .await
        .expect("Failed to insert existing override");

        transaction
            .commit()
            .await
            .expect("Failed to commit transaction");

        // Try batch insert - should only insert the missing combinations
        let distinct_ids = vec!["existing_user1".to_string(), "existing_user2".to_string()];
        let new_hash = "new_batch_hash".to_string();

        let router = context.create_postgres_router();
        let result = set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            distinct_ids.clone(),
            new_hash.clone(),
        )
        .await
        .expect("Failed to set hash key overrides");

        assert!(result, "Should have written new overrides");

        // Verify existing overrides are preserved
        let overrides1 = context
            .get_feature_flag_hash_key_overrides(team.id, vec!["existing_user1".to_string()])
            .await
            .expect("Failed to get overrides");

        assert_eq!(
            overrides1.get("existing_flag_1"),
            Some(&"old_hash".to_string()),
            "Existing override should be preserved"
        );
        assert_eq!(
            overrides1.get("existing_flag_2"),
            Some(&new_hash),
            "New override should be added for flag 2"
        );
        assert_eq!(
            overrides1.get("existing_flag_3"),
            Some(&new_hash),
            "New override should be added for flag 3"
        );

        // Verify the count - should have 6 total (2 existing + 4 new)
        let mut conn = context
            .get_persons_connection()
            .await
            .expect("Failed to get connection");
        let count: i64 = sqlx::query_scalar(
            "SELECT COUNT(*) FROM posthog_featureflaghashkeyoverride WHERE team_id = $1",
        )
        .bind(team.id)
        .fetch_one(&mut *conn)
        .await
        .expect("Failed to count overrides");

        assert_eq!(
            count, 6,
            "Should have 6 total overrides (2 existing + 4 new)"
        );
    }

    #[tokio::test]
    async fn test_set_overrides_filters_inactive_and_deleted_flags() {
        let context = TestContext::new(None).await;
        let team = context
            .insert_new_team(None)
            .await
            .expect("Failed to insert team");

        // Create a person
        context
            .insert_person(
                team.id,
                "filter_test_user".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person");

        // Create various flags with different states
        let active_flag = FeatureFlagRow {
            id: 50001,
            team_id: team.id,
            name: Some("Active Flag".to_string()),
            key: "active_flag".to_string(),
            filters: json!({"groups": [{"rollout_percentage": 50}]}),
            deleted: false,
            active: true,
            ensure_experience_continuity: Some(true),
            version: Some(1),
            evaluation_runtime: None,
            evaluation_tags: None,
            bucketing_identifier: None,
            has_experiment: false,
        };

        let inactive_flag = FeatureFlagRow {
            id: 50002,
            team_id: team.id,
            name: Some("Inactive Flag".to_string()),
            key: "inactive_flag".to_string(),
            filters: json!({"groups": [{"rollout_percentage": 50}]}),
            deleted: false,
            active: false, // NOT active
            ensure_experience_continuity: Some(true),
            version: Some(1),
            evaluation_runtime: None,
            evaluation_tags: None,
            bucketing_identifier: None,
            has_experiment: false,
        };

        let deleted_flag = FeatureFlagRow {
            id: 50003,
            team_id: team.id,
            name: Some("Deleted Flag".to_string()),
            key: "deleted_flag".to_string(),
            filters: json!({"groups": [{"rollout_percentage": 50}]}),
            deleted: true, // Deleted
            active: true,
            ensure_experience_continuity: Some(true),
            version: Some(1),
            evaluation_runtime: None,
            evaluation_tags: None,
            bucketing_identifier: None,
            has_experiment: false,
        };

        let no_continuity_flag = FeatureFlagRow {
            id: 50004,
            team_id: team.id,
            name: Some("No Continuity Flag".to_string()),
            key: "no_continuity_flag".to_string(),
            filters: json!({"groups": [{"rollout_percentage": 50}]}),
            deleted: false,
            active: true,
            ensure_experience_continuity: Some(false), // No experience continuity
            version: Some(1),
            evaluation_runtime: None,
            evaluation_tags: None,
            bucketing_identifier: None,
            has_experiment: false,
        };

        context
            .insert_flag(team.id, Some(active_flag))
            .await
            .expect("Failed to insert active flag");
        context
            .insert_flag(team.id, Some(inactive_flag))
            .await
            .expect("Failed to insert inactive flag");
        context
            .insert_flag(team.id, Some(deleted_flag))
            .await
            .expect("Failed to insert deleted flag");
        context
            .insert_flag(team.id, Some(no_continuity_flag))
            .await
            .expect("Failed to insert no continuity flag");

        // Set overrides
        let router = context.create_postgres_router();
        let result = set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            vec!["filter_test_user".to_string()],
            "filter_hash".to_string(),
        )
        .await
        .expect("Should not error");

        assert!(result, "Should have written override for active flag");

        // Verify only the active flag with experience continuity got an override
        let overrides = context
            .get_feature_flag_hash_key_overrides(team.id, vec!["filter_test_user".to_string()])
            .await
            .expect("Failed to get overrides");

        assert_eq!(overrides.len(), 1, "Should have exactly 1 override");
        assert_eq!(
            overrides.get("active_flag"),
            Some(&"filter_hash".to_string())
        );
        assert_eq!(
            overrides.get("inactive_flag"),
            None,
            "Inactive flag should not have override"
        );
        assert_eq!(
            overrides.get("deleted_flag"),
            None,
            "Deleted flag should not have override"
        );
        assert_eq!(
            overrides.get("no_continuity_flag"),
            None,
            "No continuity flag should not have override"
        );
    }

    #[tokio::test]
    async fn test_should_write_hash_key_override() {
        let context = TestContext::new(None).await;
        let team = context
            .insert_new_team(None)
            .await
            .expect("Failed to insert team");

        // Create a person
        context
            .insert_person(
                team.id,
                "should_write_user".to_string(),
                Some(json!({"email": "[email protected]"})),
            )
            .await
            .expect("Failed to insert person");

        // Create a flag with experience continuity
        let flag_row = FeatureFlagRow {
            id: 60000,
            team_id: team.id,
            name: Some("Should Write Flag".to_string()),
            key: "should_write_flag".to_string(),
            filters: json!({"groups": [{"rollout_percentage": 50}]}),
            deleted: false,
            active: true,
            ensure_experience_continuity: Some(true),
            version: Some(1),
            evaluation_runtime: None,
            evaluation_tags: None,
            bucketing_identifier: None,
            has_experiment: false,
        };
        context
            .insert_flag(team.id, Some(flag_row))
            .await
            .expect("Failed to insert flag");

        // Test 1: Should return true when no overrides exist
        let router = context.create_postgres_router();
        let should_write = should_write_hash_key_override(
            &router,
            team.id,
            "should_write_user".to_string(),
            "hash_key_1".to_string(),
        )
        .await
        .expect("Should not error");

        assert!(should_write, "Should write when no overrides exist");

        // Now set an override
        let router = context.create_postgres_router();
        set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            vec!["should_write_user".to_string()],
            "hash_key_1".to_string(),
        )
        .await
        .expect("Failed to set override");

        // Test 2: Should return false when override exists
        let router = context.create_postgres_router();
        let should_write = should_write_hash_key_override(
            &router,
            team.id,
            "should_write_user".to_string(),
            "hash_key_1".to_string(),
        )
        .await
        .expect("Should not error");

        assert!(
            !should_write,
            "Should not write when override already exists"
        );

        // Test 3: Should return false for non-existent person
        let router = context.create_postgres_router();
        let should_write = should_write_hash_key_override(
            &router,
            team.id,
            "non_existent_user".to_string(),
            "hash_key_2".to_string(),
        )
        .await
        .expect("Should not error");

        assert!(!should_write, "Should not write for non-existent person");
    }

    #[tokio::test]
    async fn test_set_overrides_with_no_persons() {
        let context = TestContext::new(None).await;
        let team = context
            .insert_new_team(None)
            .await
            .expect("Failed to insert team");

        // Create a flag but NO persons
        let flag_row = FeatureFlagRow {
            id: 70000,
            team_id: team.id,
            name: Some("No Person Flag".to_string()),
            key: "no_person_flag".to_string(),
            filters: json!({"groups": [{"rollout_percentage": 50}]}),
            deleted: false,
            active: true,
            ensure_experience_continuity: Some(true),
            version: Some(1),
            evaluation_runtime: None,
            evaluation_tags: None,
            bucketing_identifier: None,
            has_experiment: false,
        };
        context
            .insert_flag(team.id, Some(flag_row))
            .await
            .expect("Failed to insert flag");

        // Try to set overrides for non-existent distinct_ids
        let router = context.create_postgres_router();
        let result = set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            vec![
                "nonexistent_user1".to_string(),
                "nonexistent_user2".to_string(),
            ],
            "some_hash".to_string(),
        )
        .await
        .expect("Should not error even with non-existent users");

        assert!(!result, "Should return false when no persons found");

        // Verify no overrides were created
        let mut conn = context
            .get_persons_connection()
            .await
            .expect("Failed to get connection");
        let count: i64 = sqlx::query_scalar(
            "SELECT COUNT(*) FROM posthog_featureflaghashkeyoverride WHERE team_id = $1",
        )
        .bind(team.id)
        .fetch_one(&mut *conn)
        .await
        .expect("Failed to count overrides");

        assert_eq!(
            count, 0,
            "Should have no overrides when persons don't exist"
        );
    }

    #[rstest]
    #[case(false, None, false)]
    #[case(true, None, false)]
    #[case(true, Some("replica_check_hash_key"), true)]
    #[case(true, Some(COOKIELESS_SENTINEL_VALUE), false)]
    #[tokio::test]
    async fn test_primary_has_override_reports_only_a_real_override(
        #[case] person_exists: bool,
        #[case] stored_hash_key: Option<&str>,
        #[case] expected: bool,
    ) {
        let context = TestContext::new(None).await;
        let team = context.insert_new_team(None).await.unwrap();
        let distinct_id = "replica_check_user".to_string();

        if person_exists {
            context
                .insert_person(team.id, distinct_id.clone(), None)
                .await
                .unwrap();
        }

        if let Some(stored_hash_key) = stored_hash_key {
            let flag = mock!(FeatureFlag,
                team_id: team.id,
                filters: FlagFilters {
                    groups: vec![],
                    ..Default::default()
                },
                ensure_experience_continuity: Some(true)
            );
            let flag_row = mock!(FeatureFlagRow, from: flag);
            context.insert_flag(team.id, Some(flag_row)).await.unwrap();

            let router = context.create_postgres_router();
            set_feature_flag_hash_key_overrides(
                &router,
                team.id,
                vec![distinct_id.clone()],
                stored_hash_key.to_string(),
            )
            .await
            .unwrap();
        }

        let found = primary_has_override(&context.persons_writer, team.id, &[distinct_id])
            .await
            .unwrap();

        // A wrong query here reports no override every time: the check reads healthy and the
        // rate it exists to measure is silently zero.
        assert_eq!(found, expected);
    }

    #[rstest]
    #[case("some_distinct_id", 0.7270002403585725)]
    #[case("test-identifier", 0.4493881716040236)]
    #[case("example_id", 0.9402003475831224)]
    #[case("example_id2", 0.6292740389966519)]
    #[tokio::test]
    async fn test_calculate_hash(#[case] hashed_identifier: &str, #[case] expected_hash: f64) {
        let hash = calculate_hash("holdout-", hashed_identifier, "").unwrap();
        assert!(
            (hash - expected_hash).abs() < f64::EPSILON,
            "Hash {hash} should equal expected value {expected_hash} within floating point precision"
        );
    }

    #[tokio::test]
    async fn test_overrides_locally_computable() {
        let overrides = Some(HashMap::from([
            ("email".to_string(), json!("[email protected]")),
            ("age".to_string(), json!(30)),
        ]));

        // Test case 1: No cohort properties - should return overrides
        let property_filters = vec![
            PropertyFilter {
                key: "email".to_string(),
                value: Some(json!("[email protected]")),
                operator: None,
                prop_type: PropertyType::Person,
                group_type_index: None,
                negation: None,
                compiled_regex: None,
                extra: Default::default(),
            },
            PropertyFilter {
                key: "age".to_string(),
                value: Some(json!(25)),
                operator: Some(OperatorType::Gte),
                prop_type: PropertyType::Person,
                group_type_index: None,
                negation: None,
                compiled_regex: None,
                extra: Default::default(),
            },
        ];

        let result = locally_computable_property_overrides(&overrides, &property_filters);
        assert!(result.is_some());

        // Test case 2: Property filters include cohort - should return None
        let property_filters_with_cohort = vec![
            PropertyFilter {
                key: "email".to_string(),
                value: Some(json!("[email protected]")),
                operator: None,
                prop_type: PropertyType::Person,
                group_type_index: None,
                negation: None,
                compiled_regex: None,
                extra: Default::default(),
            },
            PropertyFilter {
                key: "cohort".to_string(),
                value: Some(json!(1)),
                operator: None,
                prop_type: PropertyType::Cohort,
                group_type_index: None,
                negation: None,
                compiled_regex: None,
                extra: Default::default(),
            },
        ];

        let result =
            locally_computable_property_overrides(&overrides, &property_filters_with_cohort);
        assert!(result.is_none());

        // Test case 3: Empty property filters - should return None (no properties needed)
        let empty_filters = vec![];
        let result = locally_computable_property_overrides(&overrides, &empty_filters);
        assert!(result.is_none());

        // Test case 4: Overrides contain some but not all properties from filters - should return None (no complete overlap)
        let property_filters_extra = vec![
            PropertyFilter {
                key: "email".to_string(),
                value: Some(json!("[email protected]")),
                operator: None,
                prop_type: PropertyType::Person,
                group_type_index: None,
                negation: None,
                compiled_regex: None,
                extra: Default::default(),
            },
            PropertyFilter {
                key: "missing_property".to_string(), // This property is NOT in overrides
                value: Some(json!("some_value")),
                operator: None,
                prop_type: PropertyType::Person,
                group_type_index: None,
                negation: None,
                compiled_regex: None,
                extra: Default::default(),
            },
        ];

        let result = locally_computable_property_overrides(&overrides, &property_filters_extra);
        assert!(result.is_some()); // Should return overrides because there's partial overlap (email)
        let returned_overrides = result.unwrap();
        assert!(returned_overrides.contains_key("email")); // email should be included
        assert!(returned_overrides.contains_key("age")); // age should also be included (all overrides returned)
        assert!(!returned_overrides.contains_key("missing_property")); // missing_property was not in original overrides
        assert_eq!(returned_overrides.len(), 2); // Both email and age should be returned
    }

    #[tokio::test]
    async fn test_person_property_overrides_bug_fix() {
        // This test specifically addresses the bug where person property overrides
        // were ignored if the flag didn't explicitly check for those properties.

        // Simulate sending an override for $feature_enrollment/discussions
        let overrides = Some(HashMap::from([
            ("$feature_enrollment/discussions".to_string(), json!(false)),
            ("email".to_string(), json!("[email protected]")),
        ]));

        // Simulate a flag that only checks for email, not $feature_enrollment/discussions
        let flag_property_filters = vec![PropertyFilter {
            key: "email".to_string(),
            value: Some(json!("[email protected]")),
            operator: Some(OperatorType::Exact),
            prop_type: PropertyType::Person,
            group_type_index: None,
            negation: None,
            compiled_regex: None,
            extra: Default::default(),
        }];

        let result = locally_computable_property_overrides(&overrides, &flag_property_filters);
        assert!(result.is_some(), "Person property overrides should be returned even when flag doesn't check for all override properties");

        let returned_overrides = result.unwrap();
        assert_eq!(
            returned_overrides.get("$feature_enrollment/discussions"),
            Some(&json!(false)),
            "The $feature_enrollment/discussions override should be present"
        );
        assert_eq!(
            returned_overrides.get("email"),
            Some(&json!("[email protected]")),
            "The email override should be present"
        );
    }

    #[rstest]
    #[case("1", json!(true), FlagValue::Boolean(true), true)] // filter value true, flag_value is true, so true
    #[case("1", json!(true), FlagValue::Boolean(false), false)] // filter value true, flag_value is false, so false
    #[case("1", json!(true), FlagValue::String("some-variant".to_string()), true)]
    // filter value true, flag_value is "some-variant", so true (filter value true means flag value can be true or any variant)
    #[case("1", json!(true), FlagValue::String("other-variant".to_string()), true)] // filter value true, flag_value is "other-variant", so true (see above)
    #[case("1", json!(false), FlagValue::Boolean(false), true)] // filter value false, flag_value is false, so true
    #[case("1", json!(false), FlagValue::Boolean(true), false)] // filter value false, flag_value is true, so false
    #[case("1", json!(false), FlagValue::String("some-variant".to_string()), false)] // filter value false, flag_value is "some-variant", so false
    #[case("1", json!("some-variant"), FlagValue::String("some-variant".to_string()), true)] // flag value variant matches filter value variant, so true
    #[case("1", json!("some-variant"), FlagValue::String("other-variant".to_string()), false)] // flag value variant doesn't match filter value variant, so false
    #[case("1", json!("some-variant"), FlagValue::Boolean(true), false)] // even though flag value is true, it doesn't match the filter value variant, so false
    #[case("1", json!("some-variant"), FlagValue::Boolean(false), false)] // flag value is false and doesn't match the filter value variant, so false
    #[case("2", json!(true), FlagValue::Boolean(true), false)] // flag referenced by filter does not exist, so false
    #[tokio::test]
    async fn test_match_flag_filter_value(
        #[case] filter_flag_id: i32,
        #[case] filter_value: Value,
        #[case] flag_value: FlagValue,
        #[case] expected: bool,
    ) {
        let flag_evaluation_results = HashMap::from([(1, flag_value)]);

        let filter = PropertyFilter {
            key: filter_flag_id.to_string(),
            value: Some(filter_value),
            operator: Some(OperatorType::FlagEvaluatesTo),
            prop_type: PropertyType::Flag,
            negation: None,
            group_type_index: None,
            compiled_regex: None,
            extra: Default::default(),
        };

        let result = match_flag_value_to_flag_filter(&filter, &flag_evaluation_results);
        assert_eq!(result, expected);
    }

    #[tokio::test]
    async fn test_match_flag_value_to_flag_filter_returns_false_if_operator_is_not_exact() {
        let flag_evaluation_results = HashMap::from([(1, FlagValue::Boolean(true))]);

        let filter = PropertyFilter {
            key: "1".to_string(),
            value: Some(json!(true)),
            operator: Some(OperatorType::Icontains),
            prop_type: PropertyType::Flag,
            group_type_index: None,
            negation: None,
            compiled_regex: None,
            extra: Default::default(),
        };

        let result = match_flag_value_to_flag_filter(&filter, &flag_evaluation_results);
        assert!(!result);
    }

    #[rstest]
    #[case::timeout(|| sqlx::Error::PoolTimedOut, 1, 1, 1)]
    #[case::transient(|| sqlx::Error::PoolClosed, 2, 3, 3)]
    #[tokio::test(start_paused = true)]
    async fn test_hash_key_override_retries(
        #[case] error: fn() -> sqlx::Error,
        #[case] read_attempts: usize,
        #[case] write_attempts: usize,
        #[case] check_attempts: usize,
    ) {
        let recorder = DebuggingRecorder::new();
        let snapshotter = recorder.snapshotter();
        let _guard = metrics::set_default_local_recorder(&recorder);

        let client = std::sync::Arc::new(CountingFailingClient::new(error));
        let router = PostgresRouter::new(
            client.clone(),
            client.clone(),
            client.clone(),
            client.clone(),
        );
        let calls = || client.calls.swap(0, Ordering::SeqCst);

        let read = get_feature_flag_hash_key_overrides(
            client.clone(),
            pool_names::PERSONS_READER,
            client.clone(),
            1,
            vec!["user".to_string()],
        )
        .await;
        assert!(read.is_err());
        assert_eq!(calls(), read_attempts, "read attempts");

        let write = set_feature_flag_hash_key_overrides(
            &router,
            1,
            vec!["user".to_string()],
            "hash".to_string(),
        )
        .await;
        assert!(write.is_err());
        assert_eq!(calls(), write_attempts, "write attempts");

        let check =
            should_write_hash_key_override(&router, 1, "user".to_string(), "hash".to_string())
                .await;
        assert!(check.is_err());
        assert_eq!(calls(), check_attempts, "check attempts");

        let counter =
            |name: &str, labels: &[(&str, &str)]| counter_total(&snapshotter, name, labels);
        for (operation, attempts) in [
            ("get_hash_key_overrides", read_attempts),
            ("set_hash_key_overrides", write_attempts),
            ("should_write_hash_key_override", check_attempts),
        ] {
            let retries = (attempts - 1) as u64;
            assert_eq!(
                counter(FLAG_HASH_KEY_RETRIES_COUNTER, &[("operation", operation)]),
                retries,
                "{operation} retries"
            );
            assert_eq!(
                counter(
                    FLAG_DATABASE_ERROR_COUNTER,
                    &[("operation", operation), ("retried", "true")]
                ),
                retries,
                "{operation} errors labeled retried"
            );
            assert_eq!(
                counter(
                    FLAG_DATABASE_ERROR_COUNTER,
                    &[("operation", operation), ("retried", "false")]
                ),
                1,
                "{operation} final error labeled not retried"
            );
        }
    }

    #[tokio::test(start_paused = true)]
    async fn test_hash_key_override_dropped_during_backoff_counts_no_retry() {
        let recorder = DebuggingRecorder::new();
        let snapshotter = recorder.snapshotter();
        let _guard = metrics::set_default_local_recorder(&recorder);

        let client = std::sync::Arc::new(CountingFailingClient::new(|| sqlx::Error::PoolClosed));

        let read = get_feature_flag_hash_key_overrides(
            client.clone(),
            pool_names::PERSONS_READER,
            client.clone(),
            1,
            vec!["user".to_string()],
        )
        .now_or_never();

        assert!(read.is_none(), "read finished instead of waiting to retry");
        assert_eq!(client.calls.load(Ordering::SeqCst), 1);
        let counter =
            |name: &str, labels: &[(&str, &str)]| counter_total(&snapshotter, name, labels);
        assert_eq!(counter(FLAG_HASH_KEY_RETRIES_COUNTER, &[]), 0);
        assert_eq!(
            counter(FLAG_DATABASE_ERROR_COUNTER, &[("retried", "true")]),
            0
        );
        assert_eq!(
            counter(FLAG_DATABASE_ERROR_COUNTER, &[("retried", "false")]),
            1
        );
    }

    #[tokio::test]
    async fn test_set_overrides_retries_transient_non_persons_acquire_failure() {
        let context = TestContext::new(None).await;
        let team = context.insert_new_team(None).await.unwrap();
        context
            .insert_person(team.id, "user".to_string(), None)
            .await
            .unwrap();

        let non_persons_reader =
            std::sync::Arc::new(CountingFailingClient::new(|| sqlx::Error::PoolClosed));
        let router = PostgresRouter::new(
            context.persons_reader.clone(),
            context.persons_writer.clone(),
            non_persons_reader.clone(),
            context.non_persons_writer.clone(),
        );

        let result = set_feature_flag_hash_key_overrides(
            &router,
            team.id,
            vec!["user".to_string()],
            "hash".to_string(),
        )
        .await;

        assert!(result.is_err());
        assert_eq!(non_persons_reader.calls.load(Ordering::SeqCst), 3);
    }

    #[tokio::test]
    async fn test_set_overrides_statement_timeout_covers_person_row_lock() {
        let context = TestContext::new(None).await;
        let team = context.insert_new_team(None).await.unwrap();
        let person_id = context
            .insert_person(team.id, "user".to_string(), None)
            .await
            .unwrap();
        let flag = mock!(FeatureFlag,
            team_id: team.id,
            ensure_experience_continuity: Some(true)
        );
        context
            .insert_flag(team.id, Some(mock!(FeatureFlagRow, from: flag)))
            .await
            .unwrap();

        let persons_writer: PostgresWriter = std::sync::Arc::new(
            get_pool_with_config(
                &DEFAULT_TEST_CONFIG.get_persons_write_database_url(),
                PoolConfig {
                    statement_timeout_ms: Some(200),
                    ..PoolConfig::default()
                },
            )
            .unwrap(),
        );
        let router = PostgresRouter::new(
            context.persons_reader.clone(),
            persons_writer,
            context.non_persons_reader.clone(),
            context.non_persons_writer.clone(),
        );

        // FOR UPDATE conflicts with the KEY SHARE lock that the foreign key check takes on the
        // person row.
        let mut lock_conn = context.persons_writer.get_connection().await.unwrap();
        let mut lock_tx = lock_conn.begin().await.unwrap();
        sqlx::query("SELECT id FROM posthog_person WHERE id = $1 FOR UPDATE")
            .bind(person_id)
            .execute(&mut *lock_tx)
            .await
            .unwrap();

        let result = timeout(
            Duration::from_secs(10),
            set_feature_flag_hash_key_overrides(
                &router,
                team.id,
                vec!["user".to_string()],
                "hash".to_string(),
            ),
        )
        .await
        .expect("the write waited for the person row lock instead of timing out");
        lock_tx.rollback().await.unwrap();

        assert!(
            matches!(result, Err(FlagError::TimeoutError(_))),
            "expected a statement timeout, got {result:?}"
        );
    }

    /// `common_database` has the same mock, but it is private to that crate's tests.
    #[derive(Debug)]
    struct MockDbError {
        code: &'static str,
    }

    impl std::fmt::Display for MockDbError {
        fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
            write!(f, "database error {}", self.code)
        }
    }

    impl std::error::Error for MockDbError {}

    impl sqlx::error::DatabaseError for MockDbError {
        fn message(&self) -> &str {
            "mock database error"
        }

        fn code(&self) -> Option<std::borrow::Cow<'_, str>> {
            Some(self.code.into())
        }

        fn as_error(&self) -> &(dyn std::error::Error + Send + Sync + 'static) {
            self
        }

        fn as_error_mut(&mut self) -> &mut (dyn std::error::Error + Send + Sync + 'static) {
            self
        }

        fn into_error(self: Box<Self>) -> Box<dyn std::error::Error + Send + Sync + 'static> {
            self
        }

        fn kind(&self) -> sqlx::error::ErrorKind {
            sqlx::error::ErrorKind::Other
        }
    }

    #[tokio::test]
    async fn test_should_retry_on_error() {
        use sqlx::Error as SqlxError;

        // PoolTimedOut goes through From<sqlx::Error> → is_timeout_error() → TimeoutError,
        // so it never arrives as DatabaseError in production. Verify the real conversion path.
        let pool_timeout: FlagError = SqlxError::PoolTimedOut.into();
        assert!(
            matches!(pool_timeout, FlagError::TimeoutError(Some(ref t)) if t == "pool_timeout")
        );
        assert!(!should_retry_on_error(&pool_timeout));

        let pool_closed_error = FlagError::DatabaseError(SqlxError::PoolClosed, None);
        assert!(should_retry_on_error(&pool_closed_error));

        let io_error = FlagError::DatabaseError(
            SqlxError::Io(std::io::Error::new(
                std::io::ErrorKind::ConnectionRefused,
                "connection refused",
            )),
            None,
        );
        assert!(should_retry_on_error(&io_error));

        // Test Protocol errors with connection issues
        let protocol_connection_error =
            FlagError::DatabaseError(SqlxError::Protocol("connection lost".to_string()), None);
        assert!(should_retry_on_error(&protocol_connection_error));

        let protocol_timeout_error =
            FlagError::DatabaseError(SqlxError::Protocol("operation timeout".to_string()), None);
        assert!(!should_retry_on_error(&protocol_timeout_error));

        let wrapped_statement_timeout = FlagError::DatabaseError(
            SqlxError::from(MockDbError { code: "57014" }),
            Some("Failed to fetch person data".to_string()),
        );
        assert!(!should_retry_on_error(&wrapped_statement_timeout));

        let foreign_key_violation: FlagError =
            SqlxError::from(MockDbError { code: "23503" }).into();
        assert!(flag_error_is_foreign_key_constraint(&foreign_key_violation));
        assert!(should_retry_on_error(&foreign_key_violation));

        // Test that configuration errors don't trigger retries
        let config_error = FlagError::DatabaseError(
            SqlxError::Configuration("invalid connection string".into()),
            None,
        );
        assert!(!should_retry_on_error(&config_error));

        let column_error = FlagError::DatabaseError(
            SqlxError::ColumnNotFound("missing_column".to_string()),
            None,
        );
        assert!(!should_retry_on_error(&column_error));

        // Test that other error types don't trigger retries
        let timeout_error_variant = FlagError::TimeoutError(None);
        assert!(!should_retry_on_error(&timeout_error_variant));

        let missing_id_error = FlagError::MissingDistinctId;
        assert!(!should_retry_on_error(&missing_id_error));

        let row_not_found_error = FlagError::RowNotFound;
        assert!(!should_retry_on_error(&row_not_found_error));

        // Transient errors propagated via From<sqlx::Error> arrive as DatabaseUnavailable
        // (a retryable 503); the retry loop must still retry them.
        let pool_closed_via_conversion: FlagError = SqlxError::PoolClosed.into();
        assert!(matches!(
            pool_closed_via_conversion,
            FlagError::DatabaseUnavailable
        ));
        assert!(should_retry_on_error(&pool_closed_via_conversion));
    }

    #[test]
    fn test_classify_db_error_covers_pre_classified_errors() {
        use sqlx::Error as SqlxError;

        // Transient sqlx errors now arrive pre-classified as DatabaseUnavailable. They must
        // still be tracked, or FLAG_DATABASE_ERROR_COUNTER loses the transient bucket during
        // exactly the DB blips it exists to surface.
        let transient_via_conversion: FlagError = SqlxError::PoolClosed.into();
        assert_eq!(
            classify_db_error(&transient_via_conversion),
            Some(("transient", None))
        );
        assert_eq!(
            classify_db_error(&FlagError::DatabaseUnavailable),
            Some(("transient", None))
        );

        // Errors built with context bypass From<sqlx::Error> and are classified from the
        // raw error they still carry.
        let contextual = FlagError::DatabaseError(
            SqlxError::ColumnNotFound("missing".to_string()),
            Some("Failed to fetch flags".to_string()),
        );
        assert_eq!(classify_db_error(&contextual), Some(("unknown", None)));

        let contextual_statement_timeout = FlagError::DatabaseError(
            SqlxError::from(MockDbError { code: "57014" }),
            Some("Failed to fetch person data".to_string()),
        );
        assert_eq!(
            classify_db_error(&contextual_statement_timeout),
            Some(("timeout", None))
        );

        let timeout = FlagError::TimeoutError(Some("pool_timeout".to_string()));
        assert_eq!(
            classify_db_error(&timeout),
            Some(("timeout", Some("pool_timeout")))
        );

        // Non-database errors stay untracked.
        assert_eq!(classify_db_error(&FlagError::MissingDistinctId), None);
    }

    #[test]
    fn test_populate_missing_initial_properties_adds_missing_initial_for_tracked_props() {
        let mut properties = HashMap::from([
            ("$browser".to_string(), json!("Chrome")),
            ("$os".to_string(), json!("iOS")),
            ("utm_source".to_string(), json!("google")),
            ("gclid".to_string(), json!("abc123")),
            ("regular_prop".to_string(), json!("value")),
        ]);

        populate_missing_initial_properties(&mut properties);

        // $-prefixed tracked properties get $initial_ versions
        assert_eq!(properties.get("$initial_browser"), Some(&json!("Chrome")));
        assert_eq!(properties.get("$initial_os"), Some(&json!("iOS")));
        // Non-$-prefixed tracked properties also get $initial_ versions
        assert_eq!(
            properties.get("$initial_utm_source"),
            Some(&json!("google"))
        );
        assert_eq!(properties.get("$initial_gclid"), Some(&json!("abc123")));
        // Original properties still exist
        assert_eq!(properties.get("$browser"), Some(&json!("Chrome")));
        assert_eq!(properties.get("$os"), Some(&json!("iOS")));
        assert_eq!(properties.get("utm_source"), Some(&json!("google")));
        // Regular props unchanged, no $initial_ created
        assert_eq!(properties.get("regular_prop"), Some(&json!("value")));
        assert!(!properties.contains_key("$initial_regular_prop"));
    }

    #[test]
    fn test_populate_missing_initial_properties_ignores_untracked_dollar_props() {
        let mut properties = HashMap::from([
            ("$session_id".to_string(), json!("sess_123")),
            ("$timestamp".to_string(), json!("2024-01-01")),
            ("$random_prop".to_string(), json!("value")),
        ]);

        populate_missing_initial_properties(&mut properties);

        // These $-prefixed props are NOT in PROPERTIES_WITH_INITIAL_TRACKING
        assert!(!properties.contains_key("$initial_session_id"));
        assert!(!properties.contains_key("$initial_timestamp"));
        assert!(!properties.contains_key("$initial_random_prop"));
        assert_eq!(properties.len(), 3);
    }

    #[test]
    fn test_populate_missing_initial_properties_preserves_existing_initial() {
        let mut properties = HashMap::from([
            ("$browser".to_string(), json!("Firefox")),
            ("$initial_browser".to_string(), json!("Chrome")), // Already exists with different value
        ]);

        populate_missing_initial_properties(&mut properties);

        // Existing $initial_ should NOT be overwritten
        assert_eq!(properties.get("$initial_browser"), Some(&json!("Chrome")));
        assert_eq!(properties.get("$browser"), Some(&json!("Firefox")));
    }

    #[test]
    fn test_populate_os_aliases_fills_os_from_os_name() {
        let mut properties = HashMap::from([("$os_name".to_string(), json!("Android"))]);

        populate_os_aliases(&mut properties);

        assert_eq!(properties.get("$os"), Some(&json!("Android")));
        assert_eq!(properties.get("$os_name"), Some(&json!("Android")));
    }

    #[test]
    fn test_populate_os_aliases_fills_os_name_from_os() {
        let mut properties = HashMap::from([("$os".to_string(), json!("iOS"))]);

        populate_os_aliases(&mut properties);

        assert_eq!(properties.get("$os_name"), Some(&json!("iOS")));
        assert_eq!(properties.get("$os"), Some(&json!("iOS")));
    }

    #[test]
    fn test_populate_os_aliases_preserves_both_when_present() {
        let mut properties = HashMap::from([
            ("$os".to_string(), json!("iOS")),
            ("$os_name".to_string(), json!("iPadOS")),
        ]);

        populate_os_aliases(&mut properties);

        // Neither value is overwritten when both keys already exist.
        assert_eq!(properties.get("$os"), Some(&json!("iOS")));
        assert_eq!(properties.get("$os_name"), Some(&json!("iPadOS")));
    }

    #[test]
    fn test_populate_os_aliases_noop_when_neither_present() {
        let mut properties = HashMap::from([("$browser".to_string(), json!("Chrome"))]);

        populate_os_aliases(&mut properties);

        assert!(!properties.contains_key("$os"));
        assert!(!properties.contains_key("$os_name"));
        assert_eq!(properties.len(), 1);
    }

    #[test]
    fn test_populate_os_aliases_then_initial_backfills_initial_os() {
        // Mobile person row carries only $os_name; the alias should let $initial_os
        // get backfilled by populate_missing_initial_properties.
        let mut properties = HashMap::from([("$os_name".to_string(), json!("Android"))]);

        populate_os_aliases(&mut properties);
        populate_missing_initial_properties(&mut properties);

        assert_eq!(properties.get("$os"), Some(&json!("Android")));
        assert_eq!(properties.get("$initial_os"), Some(&json!("Android")));
    }

    #[test]
    fn test_populate_missing_initial_properties_handles_campaign_properties() {
        let mut properties = HashMap::from([
            ("utm_source".to_string(), json!("newsletter")),
            ("utm_medium".to_string(), json!("email")),
            ("fbclid".to_string(), json!("fb_123")),
            ("msclkid".to_string(), json!("ms_456")),
        ]);

        populate_missing_initial_properties(&mut properties);

        assert_eq!(
            properties.get("$initial_utm_source"),
            Some(&json!("newsletter"))
        );
        assert_eq!(properties.get("$initial_utm_medium"), Some(&json!("email")));
        assert_eq!(properties.get("$initial_fbclid"), Some(&json!("fb_123")));
        assert_eq!(properties.get("$initial_msclkid"), Some(&json!("ms_456")));
    }

    #[test]
    fn test_populate_missing_initial_properties_empty_properties() {
        let mut properties = HashMap::new();

        populate_missing_initial_properties(&mut properties);

        assert!(properties.is_empty());
    }

    #[test]
    fn test_populate_missing_initial_properties_no_tracked_props() {
        let mut properties = HashMap::from([
            ("email".to_string(), json!("[email protected]")),
            ("name".to_string(), json!("Test User")),
            ("custom_prop".to_string(), json!("custom_value")),
        ]);

        populate_missing_initial_properties(&mut properties);

        // No changes should be made - none of these are tracked
        assert_eq!(properties.len(), 3);
        assert!(!properties.contains_key("$initial_email"));
        assert!(!properties.contains_key("$initial_name"));
    }

    #[test]
    fn test_apply_person_cohort_to_state_injects_person_metadata_sentinel_key() {
        use crate::properties::property_matching::person_metadata_key;
        use chrono::{TimeZone, Utc};
        use uuid::Uuid;

        let created_at = Utc.with_ymd_and_hms(2024, 1, 15, 9, 30, 0).unwrap();
        // Capture the expected RFC3339 value before `person` is moved into the result.
        let expected = created_at.to_rfc3339();

        let person = Person {
            id: 1,
            created_at,
            team_id: 1,
            uuid: Uuid::new_v4(),
            properties: json!({}),
            is_identified: true,
            is_user_id: None,
            version: Some(0),
        };

        let mut state = FlagEvaluationState::default();
        let result = PersonCohortResult {
            person: Some(person),
            cohort_matches: None,
        };

        apply_person_cohort_to_state(&mut state, result);

        // The injection arm writes Person.created_at under the sentinel prefix so that
        // person_metadata filters resolve against it (see match_property). If this arm
        // regresses, the filter silently matches nobody.
        let props = state
            .get_person_properties()
            .expect("person properties should be set");
        assert_eq!(
            props.get(&person_metadata_key("created_at")),
            Some(&Value::String(expected))
        );
    }
}