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.

10.3 KB
Raw Blame
//! 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;