Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

23.1 KB
Raw Blame
//! This module provides the implementation for storing frecency data in postgres
use crate::domain::{
    models::{
        AggregateFrecency, AggregateId, EventRecord, EventRecordWithId, FrecencyAction,
        FrecencyData, FrecencyEntity, FrecencyEvent, FrecencyPageRequest, TimestampWeight,
    },
    ports::{AggregateFrecencyStorage, EventRecordStorage, UnprocessedEventsRepo},
};
use chrono::{DateTime, Utc};
use item_filters::ast::EntityFilterAst;
use macro_user_id::{cowlike::CowLike, error::ParseErr, user_id::MacroUserIdStr};
use model_entity::{Entity, EntityType};
use sqlx::{PgPool, Postgres, QueryBuilder, Row, pool::PoolConnection, prelude::FromRow};
use std::{borrow::Cow, collections::VecDeque, str::FromStr};
use thiserror::Error;

mod dynamic;

#[cfg(test)]
mod tests;

#[cfg(test)]
mod dynamic_tests;

/// Concrete implementation of storage ports against a postgres instance
#[derive(Debug, Clone)]
pub struct FrecencyPgStorage {
    pool: PgPool,
}

/// the types of errors that can occur on [FrecencyPgStorage]
#[derive(Debug, Error)]
pub enum FrecencyStorageErr {
    /// there was a sqlx error
    #[error(transparent)]
    Db(#[from] sqlx::Error),
    /// the database contained invalid user id data
    #[error(transparent)]
    UserIdErr(#[from] ParseErr),
    /// encounted an unknown entity type
    #[error(transparent)]
    UnknownEntity(#[from] model_entity::ParseError),
    /// failed to deserialize a type from json
    #[error(transparent)]
    Serde(#[from] serde_json::Error),
    /// The dynamic frecency query cannot represent a GitHub pull request filter.
    #[error("GitHub pull request filters are not supported by frecency queries")]
    UnsupportedGithubPullRequestFilter,
}

impl FrecencyPgStorage {
    /// create a new instance of Self
    pub fn new(pool: PgPool) -> Self {
        FrecencyPgStorage { pool }
    }

    #[tracing::instrument(err, skip(self))]
    async fn static_get_top_entities(
        &self,
        user_id: MacroUserIdStr<'_>,
        from_score: Option<f64>,
        limit: u32,
    ) -> Result<Vec<AggregateFrecency>, FrecencyStorageErr> {
        let rows = sqlx::query!(
            r#"
                SELECT *
                FROM frecency_aggregates
                WHERE user_id = $1 AND ($2::float8 IS NULL OR frecency_score < $2)
                ORDER BY frecency_score DESC
                LIMIT $3
                "#,
            user_id.as_ref(),
            from_score,
            limit as i64
        )
        .fetch_all(&self.pool)
        .await?;

        rows.into_iter()
            .map(|row| {
                let aggregate_row = AggregateRow {
                    entity_id: row.entity_id,
                    entity_type: row.entity_type.parse()?,
                    user_id: row.user_id,
                    event_count: row.event_count as usize,
                    frecency_score: row.frecency_score,
                    first_event: row.first_event,
                    recent_events: serde_json::from_value(row.recent_events)?,
                };
                Ok(aggregate_row.into_aggregate_frecency()?)
            })
            .collect()
    }

    #[tracing::instrument(err, skip(self, filter))]
    async fn dynamic_get_top_entities(
        &self,
        user_id: MacroUserIdStr<'_>,
        from_score: Option<f64>,
        limit: u32,
        filter: EntityFilterAst,
    ) -> Result<Vec<AggregateFrecency>, FrecencyStorageErr> {
        dynamic::dynamic_get_top_entities(&self.pool, user_id, from_score, limit, filter).await
    }
}

struct EventRow<'a, T> {
    /// the pk id in the database
    id: T,
    user_id: Cow<'a, str>,
    entity_type: Cow<'a, str>,
    event_type: Cow<'a, str>,
    timestamp: DateTime<Utc>,
    connection_id: Cow<'a, str>,
    entity_id: Cow<'a, str>,
    /// tracks whether we have calculated the aggregate score for this record yet
    was_processed: bool,
}

impl<'a> EventRow<'a, ()> {
    fn new_from_event_record(event: EventRecord<'a>) -> Self {
        EventRow {
            id: (),
            user_id: event.event.entity.user_id,
            entity_type: Cow::Borrowed(<&'static str>::from(event.event.entity.entity.entity_type)),
            event_type: Cow::Borrowed(<&'static str>::from(event.event.action)),
            timestamp: event.timestamp,
            // connection_id is no longer in FrecencyEvent, use empty string for backwards compatibility
            connection_id: Cow::Borrowed(""),
            entity_id: event.event.entity.entity.entity_id,
            was_processed: false,
        }
    }
}

impl<'a> EventRow<'a, i64> {
    fn into_event_record(self) -> Result<EventRecordWithId<'a, i64>, anyhow::Error> {
        let EventRow {
            id,
            user_id,
            entity_type,
            event_type,
            timestamp,
            connection_id: _, // connection_id is no longer needed
            entity_id,
            was_processed: _,
        } = self;

        Ok(EventRecordWithId {
            event_record: EventRecord {
                event: FrecencyEvent {
                    entity: FrecencyEntity {
                        user_id,
                        entity: EntityType::from_str(&entity_type)?
                            .with_entity_string(entity_id.into_owned()),
                    },
                    action: FrecencyAction::from_str(&event_type)?,
                },
                timestamp,
            },
            id,
        })
    }
}

impl EventRecordStorage for FrecencyPgStorage {
    type Err = sqlx::Error;

    async fn set_event(&self, record: EventRecord<'_>) -> Result<(), Self::Err> {
        let EventRow {
            id: (),
            user_id,
            entity_type,
            event_type,
            timestamp,
            connection_id,
            entity_id,
            was_processed,
        } = EventRow::new_from_event_record(record);

        let _res = sqlx::query!(
            r#"
            INSERT INTO frecency_events (
                user_id,
                entity_type,
                event_type,
                timestamp,
                connection_id,
                entity_id,
                was_processed
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7)
            "#,
            &user_id,
            entity_type.as_ref(),
            event_type.as_ref(),
            timestamp,
            &connection_id,
            &entity_id,
            was_processed
        )
        .execute(&self.pool)
        .await?;

        Ok(())
    }
}

#[derive(FromRow)]
struct AggregateRow {
    entity_id: String,
    #[sqlx(try_from = "String")]
    entity_type: EntityType,
    user_id: String,
    #[sqlx(try_from = "i32")]
    event_count: usize,
    frecency_score: f64,
    first_event: DateTime<Utc>,
    recent_events: sqlx::types::Json<VecDeque<TimestampWeight>>,
}

impl AggregateRow {
    fn into_aggregate_frecency(self) -> Result<AggregateFrecency, ParseErr> {
        let AggregateRow {
            entity_id,
            entity_type,
            user_id,
            event_count,
            frecency_score,
            first_event,
            recent_events,
        } = self;

        Ok(AggregateFrecency {
            id: AggregateId {
                entity: entity_type.with_entity_string(entity_id.to_string()),
                user_id: MacroUserIdStr::parse_from_str(&user_id).map(CowLike::into_owned)?,
            },
            data: FrecencyData {
                event_count,
                frecency_score,
                first_event,
                recent_events: recent_events.0,
            },
        })
    }
}

impl AggregateFrecencyStorage for FrecencyPgStorage {
    type Err = FrecencyStorageErr;

    #[tracing::instrument(err, skip(self, req))]
    async fn get_top_entities(
        &self,
        req: FrecencyPageRequest<'_>,
    ) -> Result<Vec<AggregateFrecency>, Self::Err> {
        let FrecencyPageRequest {
            user_id,
            from_score,
            limit,
            filters,
        } = req;
        if filters
            .as_ref()
            .is_some_and(|filter| filter.github_pull_request_filter.is_some())
        {
            return Err(FrecencyStorageErr::UnsupportedGithubPullRequestFilter);
        }
        match filters {
            None => {
                self.static_get_top_entities(user_id, from_score, limit)
                    .await
            }
            Some(filter) => {
                self.dynamic_get_top_entities(user_id, from_score, limit, filter)
                    .await
            }
        }
    }

    async fn set_aggregate(
        &self,
        frecency: crate::domain::models::AggregateFrecency,
    ) -> Result<(), Self::Err> {
        let entity_type = frecency.id.entity.entity_type.to_string();
        let entity_id = frecency.id.entity.entity_id;
        let recent_events_json = serde_json::to_value(&frecency.data.recent_events)
            .expect("Failed to serialize recent_events");

        sqlx::query!(
            r#"
                INSERT INTO frecency_aggregates (
                    entity_id,
                    entity_type,
                    user_id,
                    event_count,
                    frecency_score,
                    first_event,
                    recent_events
                )
                VALUES ($1, $2, $3, $4, $5, $6, $7)
                ON CONFLICT (user_id, entity_type, entity_id)
                DO UPDATE SET
                    event_count = EXCLUDED.event_count,
                    frecency_score = EXCLUDED.frecency_score,
                    recent_events = EXCLUDED.recent_events
                "#,
            entity_id.as_ref(),
            entity_type,
            frecency.id.user_id.as_ref(),
            frecency.data.event_count as i32,
            frecency.data.frecency_score,
            frecency.data.first_event,
            recent_events_json
        )
        .execute(&self.pool)
        .await?;

        Ok(())
    }

    #[tracing::instrument(err, skip(self, entities))]
    #[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
    async fn get_aggregate_for_user_entities<'a>(
        &self,
        user_id: MacroUserIdStr<'a>,
        entities: &'a [Entity<'a>],
    ) -> Result<Vec<crate::domain::models::AggregateFrecency>, Self::Err> {
        // Build the WHERE conditions for each entity
        let mut conditions = Vec::new();
        let mut params = Vec::new();

        params.push(user_id.as_ref());

        for entity in entities {
            conditions.push(format!(
                "(entity_type = ${} AND entity_id = ${})",
                params.len() + 1,
                params.len() + 2
            ));
            params.push(<&'static str>::from(entity.entity_type));
            params.push(entity.entity_id.as_ref());
        }

        if conditions.is_empty() {
            return Ok(Vec::new());
        }

        // Build and execute dynamic query
        let query = format!(
            r#"
                SELECT 
                    entity_id,
                    entity_type,
                    user_id,
                    event_count,
                    frecency_score,
                    first_event,
                    recent_events
                FROM frecency_aggregates
                WHERE user_id = $1 AND ({})
                ORDER BY frecency_score DESC
                "#,
            conditions.join(" OR ")
        );

        let mut sql_query = sqlx::query(&query);
        for param in params {
            sql_query = sql_query.bind(param);
        }

        let rows = sql_query.fetch_all(&self.pool).await?;

        let out: Result<Vec<_>, _> = rows
            .into_iter()
            .map(|row| {
                let aggregate_row = AggregateRow {
                    entity_id: row.try_get("entity_id").unwrap(),
                    entity_type: row
                        .try_get::<String, _>("entity_type")
                        .unwrap()
                        .parse()
                        .unwrap(),
                    user_id: row.try_get("user_id").unwrap(),
                    event_count: row.try_get::<i32, _>("event_count").unwrap() as usize,
                    frecency_score: row.try_get("frecency_score").unwrap(),
                    first_event: row.try_get("first_event").unwrap(),
                    recent_events: serde_json::from_value(row.try_get("recent_events").unwrap())
                        .unwrap(),
                };
                aggregate_row.into_aggregate_frecency()
            })
            .collect();
        Ok(out?)
    }
}

/// concrete struct which implements [UnprocessedEventsRepo]
///
/// Batches are claimed and committed up front, so no transaction stays open
/// across the aggregation work. Pollers are still serialised, by a
/// session-scoped advisory lock held on `aggregate_lock` for the length of one
/// pass — see [`UnprocessedEventsRepo::get_unprocessed_events`] for why that is
/// load-bearing.
pub struct FrecencyPgProcessor {
    pool: PgPool,
    /// Connection holding the session-level aggregation lock. Occupied only
    /// between a successful claim and its matching `mark_processed`.
    aggregate_lock: tokio::sync::Mutex<Option<PoolConnection<Postgres>>>,
}

// Define a unique lock ID for frecency aggregation
const FRECENCY_POLLER_LOCK_ID: i64 = 999_999_001;

impl FrecencyPgProcessor {
    /// create a new instance of self from a [PgPool]
    pub fn new(pool: PgPool) -> Self {
        FrecencyPgProcessor {
            pool,
            aggregate_lock: tokio::sync::Mutex::new(None),
        }
    }
}

type ExistingEventRow = EventRow<'static, i64>;

/// the types of errors that can occur with the poller
#[derive(Debug, Error)]
pub enum PollerErr {
    /// another poller currently holds the aggregation lock
    #[error("another poller currently holds the frecency aggregation lock")]
    DbLockErr,
    /// failed to acquire the in-process mutex guarding the lock connection
    #[error(transparent)]
    MutexErr(#[from] tokio::sync::TryLockError),
    /// sqlx database error
    #[error(transparent)]
    Sqlx(#[from] sqlx::Error),
    /// error parsing macro user id
    #[error(transparent)]
    ParseErr(#[from] ParseErr),
    /// some other error
    #[error(transparent)]
    OtherErr(#[from] anyhow::Error),
}

/// we need to bind 7 parameters per event we need to process to insert 1 [AggregateFrecency].
/// Due to the u16 max upper bound on query parameters this is the limit for items that can be processed in one batch.
/// Leftover entries just get processed later
static FETCH_LIMIT: u16 = u16::MAX / 7;

impl UnprocessedEventsRepo for FrecencyPgProcessor {
    type Err = PollerErr;
    type EventId = i64;

    /// Take the aggregation lock, then claim a batch of unprocessed events,
    /// marking them processed and committing before returning.
    ///
    /// This previously opened a transaction, took `pg_advisory_xact_lock`, ran
    /// the read, and then stashed the *live* transaction so `mark_processed`
    /// could commit it after aggregation. That left the connection
    /// `idle in transaction` for the whole aggregation pass (20-60s in prod),
    /// which pinned `xmin`, blocked vacuum on `frecency_events`, and held a
    /// pool connection the entire time.
    ///
    /// Claiming up front makes the *transaction* one statement long. The lock
    /// still has to outlive the claim, though, and dropping it would be a
    /// correctness bug rather than a throughput trade: aggregation is a
    /// read-modify-write of `frecency_aggregates`, and disjoint event batches
    /// routinely map onto the same `(user_id, entity_type, entity_id)` key.
    /// Two pollers would each read the same base row, merge their own events
    /// onto it in memory, and then blind-overwrite it in `set_aggregates`
    /// (`SET event_count = EXCLUDED.event_count, ...`) — last writer wins and
    /// the other batch's contribution is silently dropped, permanently, since
    /// its events are already marked processed. `connection_gateway` runs
    /// three replicas in prod, so this is a steady-state race, not a rare one.
    ///
    /// So the lock is now session-scoped instead of transaction-scoped: held
    /// on a dedicated pooled connection across the whole pass and released by
    /// `mark_processed`. Same mutual exclusion as before, without an open
    /// transaction. `FOR UPDATE SKIP LOCKED` on the claim is belt and braces —
    /// it keeps the claim itself correct if this lock is ever narrowed.
    ///
    /// Delivery is at-most-once: a crash between the claim and
    /// `set_aggregates` drops that batch's contribution. Frecency is a ranking
    /// heuristic rather than a ledger, so a lost batch degrades ordering
    /// slightly rather than corrupting state. If that stops being acceptable,
    /// the fix is a `claimed_at` column reset by a sweeper, not a return to
    /// holding the transaction open.
    ///
    /// `ORDER BY id` gives `idx_frecency_events_unprocessed_id` something to
    /// walk; the columns are listed explicitly so adding a column to the table
    /// cannot silently widen this read.
    async fn get_unprocessed_events(
        &self,
    ) -> Result<Vec<EventRecordWithId<'static, i64>>, Self::Err> {
        let mut guard = self.aggregate_lock.try_lock()?;

        // A pass that failed between the claim and mark_processed leaves its
        // connection here, still holding the lock. Release it explicitly
        // rather than trusting pool reset to do it.
        if let Some(mut stale) = guard.take() {
            tracing::warn!("releasing frecency aggregation lock left by a failed pass");
            let _ = sqlx::query!(r#"SELECT pg_advisory_unlock_all()"#)
                .execute(&mut *stale)
                .await
                .inspect_err(|e| {
                    tracing::error!(error = ?e, "failed to release stale frecency lock");
                });
        }

        let mut conn = self.pool.acquire().await?;
        let locked = sqlx::query_scalar!(
            r#"SELECT pg_try_advisory_lock($1)"#,
            FRECENCY_POLLER_LOCK_ID
        )
        .fetch_one(&mut *conn)
        .await?
        .unwrap_or(false);

        if !locked {
            // Nothing was acquired, so returning the connection is enough.
            return Err(PollerErr::DbLockErr);
        }

        // Stash before running anything that can fail, so a mid-pass error
        // leaves the lock reachable for the cleanup above.
        *guard = Some(conn);
        let conn = guard.as_mut().expect("connection was just stashed");

        let claimed: Vec<ExistingEventRow> = sqlx::query_as!(
            ExistingEventRow,
            r#"
            UPDATE frecency_events
            SET was_processed = true
            WHERE id IN (
                SELECT id
                FROM frecency_events
                WHERE was_processed = false
                ORDER BY id
                LIMIT $1
                FOR UPDATE SKIP LOCKED
            )
            RETURNING
                id,
                user_id,
                entity_type,
                event_type,
                timestamp,
                connection_id,
                entity_id,
                was_processed
            "#,
            FETCH_LIMIT as i64
        )
        .fetch_all(&mut **conn)
        .await?;

        claimed
            .into_iter()
            .map(|r| r.into_event_record())
            .collect::<Result<Vec<_>, _>>()
            .map_err(PollerErr::from)
    }

    /// Releases the aggregation lock taken by
    /// [`UnprocessedEventsRepo::get_unprocessed_events`].
    ///
    /// The events themselves were already marked processed by the claim; what
    /// still has to happen at the end of a pass is letting the next poller in,
    /// which must not happen until this pass's `set_aggregates` has landed.
    async fn mark_processed<'a>(
        &self,
        _event: Vec<EventRecordWithId<'a, i64>>,
    ) -> Result<(), Self::Err> {
        let mut guard = self.aggregate_lock.try_lock()?;
        let Some(mut conn) = guard.take() else {
            return Ok(());
        };
        sqlx::query_scalar!(r#"SELECT pg_advisory_unlock($1)"#, FRECENCY_POLLER_LOCK_ID)
            .fetch_one(&mut *conn)
            .await?;
        Ok(())
    }

    async fn get_aggregates_for_users_entities(
        &self,
        aggregates: Vec<AggregateId<'_>>,
    ) -> Result<Vec<AggregateFrecency>, Self::Err> {
        // `push_tuples` renders an empty iterator as `IN ()`, which Postgres
        // rejects as a syntax error. Callers should not reach here with an
        // empty batch, but nothing left to look up has an obvious answer.
        if aggregates.is_empty() {
            return Ok(Vec::new());
        }

        let mut query_builder = QueryBuilder::<Postgres>::new(
            r#"
            SELECT
                entity_id,
                entity_type,
                user_id,
                event_count,
                frecency_score,
                first_event,
                recent_events
            FROM frecency_aggregates
            WHERE
                (user_id, entity_id, entity_type)
            IN 
            "#,
        );

        query_builder.push_tuples(aggregates, |mut b, AggregateId { user_id, entity }| {
            b.push_bind(user_id.as_ref().to_string())
                .push_bind(entity.entity_id)
                .push_bind(<&'static str>::from(entity.entity_type));
        });

        let query = query_builder.build_query_as::<AggregateRow>();

        let output = query.fetch_all(&self.pool).await?;

        let out: Result<Vec<_>, _> = output
            .into_iter()
            .map(AggregateRow::into_aggregate_frecency)
            .collect();

        Ok(out?)
    }

    async fn set_aggregates(&self, aggregates: Vec<AggregateFrecency>) -> Result<(), Self::Err> {
        if aggregates.is_empty() {
            return Ok(());
        }

        let mut query_builder = QueryBuilder::<Postgres>::new(
            r#"
            INSERT INTO frecency_aggregates (
                entity_id,
                entity_type,
                user_id,
                event_count,
                frecency_score,
                first_event,
                recent_events
            )
            "#,
        );

        query_builder.push_values(aggregates, |mut b, aggregate| {
            let entity_type = aggregate.id.entity.entity_type.to_string();
            let entity_id = aggregate.id.entity.entity_id.to_string();
            let recent_events_json = serde_json::to_value(&aggregate.data.recent_events)
                .expect("Failed to serialize recent_events");

            b.push_bind(entity_id)
                .push_bind(entity_type)
                .push_bind(aggregate.id.user_id.as_ref().to_string())
                .push_bind(aggregate.data.event_count as i32)
                .push_bind(aggregate.data.frecency_score)
                .push_bind(aggregate.data.first_event)
                .push_bind(recent_events_json);
        });

        query_builder.push(
            r#"
            ON CONFLICT (user_id, entity_type, entity_id)
            DO UPDATE SET
                event_count = EXCLUDED.event_count,
                frecency_score = EXCLUDED.frecency_score,
                recent_events = EXCLUDED.recent_events
            "#,
        );

        let query = query_builder.build();
        query.execute(&self.pool).await?;

        Ok(())
    }
}