Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.
TypeScript agentaiai-agentsall-in-onecrmcrm-systememaillinearmcpmessagingnotesnotionofficerustslackslack-alternativestartupstartup-toolstypescriptworkspace
//! 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(())
}
}