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
//! Filtered notification edges, limited independently for every requested entity.
//! Each recipient row carries its notification's entity and type, so the
//! primary-entity path reads only the viewer's rows from
//! `idx_user_notification_viewer_entity` instead of the entity's notifications
//! for every user. The secondary and message-id paths match few notifications
//! each and keep their `notification` indexes; the message-id path's `COALESCE`
//! must match `idx_notification_message_id`.
use std::{collections::HashMap, str::FromStr};
use macro_user_id::{cowlike::CowLike, user_id::MacroUserIdStr};
use model_entity::{Entity, EntityType};
use rootcause::Report;
use sqlx::PgPool;
use crate::domain::models::{
NotificationState, UserNotificationRow, entity_query::EntityNotificationQuery,
};
pub(super) async fn get_filtered_entity_notifications(
pool: &PgPool,
user_id: MacroUserIdStr<'_>,
entities: Vec<Entity<'static>>,
query: EntityNotificationQuery,
) -> Result<HashMap<Entity<'static>, Vec<UserNotificationRow<serde_json::Value>>>, Report> {
query.validate()?;
let mut result: HashMap<_, Vec<_>> = entities
.into_iter()
.map(|entity| (entity, Vec::new()))
.collect();
if result.is_empty() || query.states.is_empty() {
return Ok(result);
}
let ids: Vec<_> = result
.keys()
.map(|entity| entity.entity_id.to_string())
.collect();
let types: Vec<_> = result
.keys()
.map(|entity| entity.entity_type.as_ref().to_owned())
.collect();
let limit = query.limit.map(i64::from);
let rows = sqlx::query!(
r#"
WITH requested AS (
SELECT entity_id, entity_type
FROM unnest($2::text[], $3::text[]) AS entities(entity_id, entity_type)
),
-- Scope uses all active notifications, not just the requested states: a
-- seen reply still assigns an unseen root send to its thread row.
-- Build scope evidence once per batch, never rescan history per send.
channel_scopes AS MATERIALIZED (
SELECT DISTINCT n.event_item_id AS channel_id,
CASE WHEN n.notification_event_type = 'channel_mention'
THEN COALESCE(n.metadata->>'messageId', n.metadata->>'message_id')
END AS mentioned_message_id,
COALESCE(n.metadata->>'threadId', n.metadata->>'thread_id') AS thread_id
FROM requested
JOIN user_notification un
ON un.user_id = $1 AND un.deleted_at IS NULL
AND un.event_item_type = 'channel' AND un.event_item_id = requested.entity_id
AND un.state = ANY($9::notification_state[])
AND un.notification_event_type IN ('channel_mention', 'channel_message_reply')
JOIN notification n ON n.id = un.notification_id
WHERE $8::bool AND requested.entity_type = 'channel'
)
SELECT requested.entity_id AS "requested_id!",
requested.entity_type AS "requested_type!",
matched.notification_id AS "notification_id!",
matched.event_item_id AS "event_item_id!",
matched.event_item_type AS "event_item_type!",
matched.notification_event_type AS "notification_event_type!",
matched.sent AS "sent!",
matched.state AS "state!: NotificationState",
matched.created_at AS "created_at!",
matched.viewed_at,
matched.notification_metadata,
matched.sender_id
FROM requested
CROSS JOIN LATERAL (
SELECT un.notification_id, n.event_item_id, n.event_item_type,
n.notification_event_type, un.sent, un.state,
un.created_at::timestamptz AS created_at,
un.seen_at::timestamptz AS viewed_at,
n.metadata AS notification_metadata, n.sender_id
FROM (
SELECT viewer_match.*
FROM (
SELECT un.notification_id, un.sent, un.state, un.created_at, un.seen_at
FROM user_notification un
WHERE un.user_id = $1 AND un.deleted_at IS NULL
AND un.state = ANY($4::notification_state[])
AND un.event_item_type = requested.entity_type
AND un.event_item_id = requested.entity_id
UNION
SELECT un.notification_id, un.sent, un.state, un.created_at, un.seen_at
FROM notification n
CROSS JOIN LATERAL (
SELECT un.notification_id, un.sent, un.state, un.created_at, un.seen_at
FROM user_notification un
WHERE un.user_id = $1 AND un.notification_id = n.id
AND un.deleted_at IS NULL AND un.state = ANY($4::notification_state[])
-- Probe the viewer's row per notification: a plain join
-- can scan the viewer's whole history for each entity.
OFFSET 0
) un
WHERE n.secondary_event_item_type = requested.entity_type
AND n.secondary_event_item_id = requested.entity_id
UNION
SELECT un.notification_id, un.sent, un.state, un.created_at, un.seen_at
FROM notification n
CROSS JOIN LATERAL (
SELECT un.notification_id, un.sent, un.state, un.created_at, un.seen_at
FROM user_notification un
WHERE un.user_id = $1 AND un.notification_id = n.id
AND un.deleted_at IS NULL AND un.state = ANY($4::notification_state[])
OFFSET 0
) un
WHERE requested.entity_type = 'channel_message'
AND COALESCE(n.metadata->>'messageId', n.metadata->>'message_id', '') = requested.entity_id
) viewer_match
ORDER BY viewer_match.created_at DESC, viewer_match.notification_id DESC
-- With no filter on the notification row, the newest matches
-- are the result, so limit before reading notification rows.
LIMIT CASE WHEN cardinality($5::text[]) = 0 AND NOT $7::bool AND NOT $8::bool THEN $6::bigint END
) un
JOIN notification n ON n.id = un.notification_id
WHERE (cardinality($5::text[]) = 0 OR n.notification_event_type = ANY($5))
AND (NOT $7::bool OR (
n.notification_event_type IN ('channel_message_send', 'channel_mention', 'document_mention', 'channel_access_requested')
AND COALESCE(n.metadata->>'threadId', n.metadata->>'thread_id') IS NULL
))
AND (NOT $8::bool OR requested.entity_type <> 'channel' OR (
n.notification_event_type IN ('document_mention', 'channel_access_requested')
OR (n.notification_event_type IN ('channel_message_send', 'channel_message_reply') AND (
-- Mention stacks consume the mention, not its duplicate send/reply.
EXISTS (
SELECT 1 FROM channel_scopes scope
WHERE scope.channel_id = n.event_item_id
AND scope.mentioned_message_id = COALESCE(n.metadata->>'messageId', n.metadata->>'message_id')
)
OR (n.notification_event_type = 'channel_message_send' AND NOT EXISTS (
SELECT 1 FROM channel_scopes scope
WHERE scope.channel_id = n.event_item_id AND scope.thread_id <> ''
AND scope.thread_id = COALESCE(n.metadata->>'messageId', n.metadata->>'message_id')
))
OR (n.notification_event_type = 'channel_message_reply'
AND NULLIF(COALESCE(n.metadata->>'threadId', n.metadata->>'thread_id'), '') IS NULL)
))
OR (n.notification_event_type = 'channel_mention' AND (
COALESCE(n.metadata->>'threadId', n.metadata->>'thread_id') = ''
OR (COALESCE(n.metadata->>'threadId', n.metadata->>'thread_id') IS NULL
AND COALESCE(n.metadata->>'messageId', n.metadata->>'message_id') = ''
AND EXISTS (SELECT 1 FROM channel_scopes scope
WHERE scope.channel_id = n.event_item_id AND scope.thread_id = ''))
))
))
ORDER BY un.created_at DESC, un.notification_id DESC
LIMIT $6
) matched
"#,
user_id.as_ref(), &ids, &types, &query.states as _, &query.event_types, limit,
query.top_level_messages_only, query.channel_feed_only, NotificationState::ACTIVE as _,
).fetch_all(pool).await?;
for row in rows {
let requested =
EntityType::from_str(&row.requested_type)?.with_entity_string(row.requested_id);
let mapped = (|| -> Result<_, Report> {
Ok(UserNotificationRow {
owner_id: user_id.clone().into_owned(),
notification_id: row.notification_id,
notification_event_type: row.notification_event_type,
entity: EntityType::from_str(&row.event_item_type)?
.with_entity_string(row.event_item_id),
sent: row.sent,
state: row.state,
created_at: row.created_at,
viewed_at: row.viewed_at,
updated_at: row.created_at,
deleted_at: None,
notification_metadata: row.notification_metadata,
sender_id: row
.sender_id
.map(|sender| MacroUserIdStr::parse_from_str(&sender).map(CowLike::into_owned))
.transpose()?,
})
})();
match mapped {
Ok(notification) => result.entry(requested).or_default().push(notification),
Err(error) => {
tracing::warn!(error = ?error, notification_id = %row.notification_id, "skipping invalid notification")
}
}
}
Ok(result)
}
#[cfg(test)]
mod test;