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.

25.8 KB
Raw Blame
use crate::attachments::provider::upload_filters::{
    ATTACHMENT_MIME_TYPE_FILTERS, ATTACHMENT_MIME_TYPE_FILTERS_WITH_MEDIA,
    ATTACHMENT_WHITELISTED_DOMAINS, DOCUMENT_MIME_TYPES, OCTET_STREAM_DOCUMENT_EXTENSIONS,
};
use models_email::service::attachment::AttachmentUploadMetadata;
use sqlx::types::Uuid;
use sqlx::{Pool, Postgres, Row};

/// fetch attachments for a thread to upload to Macro during the backfill process.
/// all attachments for a thread should be uploaded if any message in the thread meets any of the
/// following criteria:
/// 1. the user sent the message
/// 2. the provider classifies the message as important
/// 3. the message came from someone with the same domain as the user
/// 4. the domain the email was sent from is part of the whitelisted domains
///
/// we also upload attachments for any threads where at least one participant is someone the user has
/// sent a message to in the past. but those attachments are fetched once backfill is complete, in
/// a different call.
#[tracing::instrument(skip(db), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn thread_document_atts_for_backfill(
    db: &Pool<Postgres>,
    thread_id: Uuid,
) -> anyhow::Result<Vec<AttachmentUploadMetadata>> {
    let query = format!(
        r#"
        WITH
        thread_info AS MATERIALIZED (
            SELECT
                t.id AS thread_id,
                t.link_id,
                LOWER(SPLIT_PART(link.email_address, '@', 2)) AS user_domain
            FROM email_threads t
            JOIN email_links link ON link.id = t.link_id
            WHERE t.id = $1
        )
        SELECT
            a.id AS attachment_db_id,
            m.provider_id as email_provider_id,
            a.provider_attachment_id as provider_attachment_id,
            a.filename as filename,
            a.mime_type as mime_type,
            m.internal_date_ts as internal_date_ts,
            m.id as message_db_id,
            m.thread_id as thread_db_id,
            from_contact.email_address as sender_email,
            m.subject as subject
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        JOIN email_contacts from_contact ON m.from_contact_id = from_contact.id
        JOIN thread_info ti ON m.thread_id = ti.thread_id
        WHERE m.thread_id = $1
            AND a.filename IS NOT NULL
            -- attachment mime type filters injected below
            {}
            AND (
                -- condition 1: the thread contains a sent message
                EXISTS (
                    SELECT 1
                    FROM email_messages sent_message
                    WHERE sent_message.thread_id = ti.thread_id
                        AND sent_message.is_sent = true
                )
                -- condition 2: the thread contains provider-important correspondence
                OR EXISTS (
                    SELECT 1
                    FROM email_messages labeled_message
                    JOIN email_message_mailbox_facts mf ON mf.id = labeled_message.id
                    WHERE labeled_message.thread_id = ti.thread_id AND mf.is_present AND NOT mf.in_trash AND mf.provider_is_important
                )
                -- conditions 3 and 4 share one sender pass
                OR EXISTS (
                    SELECT 1
                    FROM email_messages sender_message
                    JOIN email_contacts c ON c.id = sender_message.from_contact_id
                    WHERE sender_message.thread_id = ti.thread_id
                        AND (
                            LOWER(SPLIT_PART(c.email_address, '@', 2)) = ti.user_domain
                            -- whitelisted domain check injected below
                            {}
                        )
                )
            )
        ORDER BY a.id
        "#,
        ATTACHMENT_MIME_TYPE_FILTERS, ATTACHMENT_WHITELISTED_DOMAINS
    );

    let rows = sqlx::query(&query).bind(thread_id).fetch_all(db).await?;

    let attachments = rows
        .into_iter()
        .map(map_row_to_attachment_metadata)
        .collect();

    Ok(attachments)
}

/// fetch videos and images from a thread for insertion into sfs. we insert them into
/// sfs so we can display thumbnails for them in the FE.
#[tracing::instrument(skip(db), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn thread_media_atts_for_backfill(
    db: &Pool<Postgres>,
    thread_id: Uuid,
) -> anyhow::Result<Vec<AttachmentUploadMetadata>> {
    let query = format!(
        r#"
        SELECT
            a.id AS attachment_db_id,
            m.provider_id as email_provider_id,
            a.provider_attachment_id as provider_attachment_id,
            a.filename as filename,
            a.mime_type as mime_type,
            m.internal_date_ts as internal_date_ts,
            m.id as message_db_id,
            m.thread_id as thread_db_id,
            from_contact.email_address as sender_email,
            m.subject as subject
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        JOIN email_contacts from_contact ON m.from_contact_id = from_contact.id
        WHERE m.thread_id = $1
            -- attachment mime type filters injected below
            AND {}
        ORDER BY a.id
        "#,
        ATTACHMENT_MIME_TYPE_FILTERS_WITH_MEDIA
    );

    let rows = sqlx::query(&query).bind(thread_id).fetch_all(db).await?;

    let attachments = rows
        .into_iter()
        .map(map_row_to_attachment_metadata)
        .collect();

    Ok(attachments)
}

/// Fetch attachment metadata across all threads for the user where at least one participant
/// is someone the user has previously contacted (excluding the user themselves).
/// This is called after email backfill completion to identify additional attachments
/// that should be uploaded based on the user's interaction history. This isn't done at time of thread
/// completion like thread_document_atts_for_backfill because this can only be known at time
/// of job completion, as we don't know everyone the user has sent messages to until all their
/// messages have been backfilled.
///
/// Attachments that already have a live document are left out, using the same check the
/// BackfillAttachment job runs before importing. Imports from
/// fetch_attachment_threads_for_backfill that are still in flight can still overlap; the job's
/// check skips those.
///
/// This reads the whole mailbox once per backfill, so it starts from the mailbox's document
/// attachments and only reads the threads that hold them, instead of every message in every
/// thread with an attachment.
#[tracing::instrument(skip(db), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn fetch_job_attachments_for_backfill(
    db: &Pool<Postgres>,
    link_id: Uuid,
) -> anyhow::Result<Vec<AttachmentUploadMetadata>> {
    let query = format!(
        r#"
        WITH
        -- Attachment rows only exist on messages flagged has_attachments, so
        -- the partial index on that flag finds every document. Carrying the
        -- output columns here avoids reading each message row a second time.
        mailbox_documents AS MATERIALIZED (
            SELECT
                a.id AS attachment_db_id,
                m.provider_id AS email_provider_id,
                a.provider_attachment_id,
                a.filename,
                a.mime_type,
                m.internal_date_ts,
                m.id AS message_db_id,
                m.thread_id AS thread_db_id,
                m.from_contact_id,
                m.subject
            FROM public.email_messages m
            JOIN public.email_attachments a ON a.message_id = m.id
            WHERE m.link_id = $1
                AND m.has_attachments = true
                AND a.filename IS NOT NULL
                AND NOT EXISTS (
                    SELECT 1
                    FROM public.document_email de
                    JOIN public."Document" d ON d.id = de.document_id
                    WHERE de.email_attachment_id = a.id
                        AND d."deletedAt" IS NULL
                )
                -- attachment mime type filters injected below
                {}
        ),
        -- Everyone the user has sent mail to, except the user.
        contacted AS MATERIALIZED (
            SELECT DISTINCT recipient.contact_id
            FROM public.email_messages sent_message
            JOIN public.email_message_recipients recipient ON recipient.message_id = sent_message.id
            WHERE sent_message.link_id = $1
                AND sent_message.is_sent = true
                AND NOT EXISTS (
                    SELECT 1
                    FROM public.email_links l
                    JOIN public.email_contacts c
                        ON c.link_id = l.id
                        AND LOWER(c.email_address) = LOWER(l.email_address)
                    WHERE l.id = $1
                        AND c.id = recipient.contact_id
                )
        ),
        document_threads AS MATERIALIZED (
            SELECT DISTINCT thread_db_id AS thread_id
            FROM mailbox_documents
        ),
        -- Senders come from an index alone; most threads qualify here.
        sender_qualified_threads AS MATERIALIZED (
            SELECT DISTINCT sender_message.thread_id
            FROM document_threads
            JOIN public.email_messages sender_message ON sender_message.thread_id = document_threads.thread_id
            JOIN contacted ON contacted.contact_id = sender_message.from_contact_id
        ),
        -- Recipients need each message row, so only check the threads no
        -- sender qualified.
        qualified_threads AS (
            SELECT thread_id
            FROM sender_qualified_threads

            UNION

            SELECT thread_message.thread_id
            FROM document_threads
            JOIN public.email_messages thread_message ON thread_message.thread_id = document_threads.thread_id
            JOIN public.email_message_recipients recipient ON recipient.message_id = thread_message.id
            JOIN contacted ON contacted.contact_id = recipient.contact_id
            WHERE NOT EXISTS (
                SELECT 1
                FROM sender_qualified_threads
                WHERE sender_qualified_threads.thread_id = document_threads.thread_id
            )
        )

        SELECT
            document.attachment_db_id,
            document.email_provider_id,
            document.provider_attachment_id,
            document.filename,
            document.mime_type,
            document.internal_date_ts,
            document.message_db_id,
            document.thread_db_id,
            from_contact.email_address AS sender_email,
            document.subject
        FROM mailbox_documents document
        JOIN qualified_threads ON qualified_threads.thread_id = document.thread_db_id
        JOIN public.email_contacts from_contact ON from_contact.id = document.from_contact_id
        ORDER BY document.internal_date_ts DESC
        "#,
        ATTACHMENT_MIME_TYPE_FILTERS
    );

    let rows = sqlx::query(&query).bind(link_id).fetch_all(db).await?;

    let attachments = rows
        .into_iter()
        .map(map_row_to_attachment_metadata)
        .collect();

    Ok(attachments)
}

async fn message_has_unclaimed_document_attachment(
    db: &Pool<Postgres>,
    link_id: Uuid,
    message_provider_id: &str,
) -> anyhow::Result<bool> {
    let document_mime_types = DOCUMENT_MIME_TYPES
        .iter()
        .map(|value| (*value).to_owned())
        .collect::<Vec<_>>();
    let octet_stream_document_extensions = OCTET_STREAM_DOCUMENT_EXTENSIONS
        .iter()
        .map(|value| (*value).to_owned())
        .collect::<Vec<_>>();

    let has_candidate = sqlx::query_scalar!(
        r#"
        SELECT EXISTS (
            SELECT 1
            FROM email_attachments a
            JOIN email_messages m ON a.message_id = m.id
            LEFT JOIN document_email de ON de.email_attachment_id = a.id
            WHERE m.link_id = $2
                AND m.provider_id = $1
                AND a.reference_url IS NULL
                AND de.email_attachment_id IS NULL
                AND a.upload_claimed_at IS NULL
                AND a.filename IS NOT NULL
                AND (
                    a.mime_type = ANY($3::text[])
                    OR (
                        a.mime_type = 'application/octet-stream'
                        AND UPPER(SUBSTRING(a.filename FROM '\.([^.]+)$')) = ANY($4::text[])
                    )
                )
        ) AS "has_candidate!"
        "#,
        message_provider_id,
        link_id,
        document_mime_types.as_slice(),
        octet_stream_document_extensions.as_slice(),
    )
    .fetch_one(db)
    .await?;

    Ok(has_candidate)
}

/// Fetch and atomically claim attachments for a specific message to upload to Macro.
/// This is called when a new email is inserted for a user. Attachments for the message
/// should be uploaded if any message in the message's thread meets any of the following criteria:
/// 1. the user sent the message
/// 2. the provider classifies the message as important
/// 3. the message came from someone with the same domain as the user
/// 4. the domain the email was sent from is part of the whitelisted domains
/// 5. the user has previously sent a message to any participant in the thread
///
/// The query atomically claims attachments (sets upload_claimed_at) to prevent duplicate
/// uploads from concurrent workers processing the same message.
///
/// For simplicity's sake, conditions 1 2 3 and 4 are evaluated in the first query, and condition 5
/// is evaluated in a separate query. These queries are very similar to fetch_thread_attachments_for_backfill
/// and fetch_job_attachments_for_backfill respectively, except they also verify the attachment
/// doesn't already exist in document_email table.
#[tracing::instrument(skip(db), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn new_email_document_atts(
    db: &Pool<Postgres>,
    link_id: Uuid,
    message_provider_id: &str,
) -> anyhow::Result<Vec<AttachmentUploadMetadata>> {
    if !message_has_unclaimed_document_attachment(db, link_id, message_provider_id).await? {
        return Ok(Vec::new());
    }

    // query for conditions 1-4: claim attachments atomically and return metadata
    let query1 = format!(
        r#"
        WITH
        thread_info AS MATERIALIZED (
            SELECT
                t.id AS thread_id,
                t.link_id,
                LOWER(SPLIT_PART(link.email_address, '@', 2)) AS user_domain
            FROM email_messages target_message
            JOIN email_threads t ON t.id = target_message.thread_id
            JOIN email_links link ON link.id = t.link_id
            WHERE target_message.link_id = $2
                AND target_message.provider_id = $1
        ),
        claimed AS (
            UPDATE email_attachments
            SET upload_claimed_at = NOW()
            WHERE id IN (
                SELECT a.id
                FROM email_attachments a
                JOIN email_messages m ON a.message_id = m.id
                JOIN thread_info ti ON m.thread_id = ti.thread_id
                LEFT JOIN document_email de ON de.email_attachment_id = a.id
                WHERE m.link_id = $2
                    AND m.provider_id = $1
                    AND a.filename IS NOT NULL
                    {}
                    AND de.email_attachment_id IS NULL
                    AND a.upload_claimed_at IS NULL
                    AND (
                        -- condition 1: the thread contains a sent message
                        EXISTS (
                            SELECT 1
                            FROM email_messages sent_message
                            WHERE sent_message.thread_id = ti.thread_id
                                AND sent_message.is_sent = true
                        )
                        -- condition 2: the thread contains provider-important correspondence
                        OR EXISTS (
                            SELECT 1
                            FROM email_messages labeled_message
                    JOIN email_message_mailbox_facts mf ON mf.id = labeled_message.id
                    WHERE labeled_message.thread_id = ti.thread_id AND mf.is_present AND NOT mf.in_trash AND mf.provider_is_important
                        )
                        -- conditions 3 and 4 share one sender pass
                        OR EXISTS (
                            SELECT 1
                            FROM email_messages sender_message
                            JOIN email_contacts c ON c.id = sender_message.from_contact_id
                            WHERE sender_message.thread_id = ti.thread_id
                                AND (
                                    LOWER(SPLIT_PART(c.email_address, '@', 2)) = ti.user_domain
                                    {}
                                )
                        )
                    )
            )
            RETURNING id
        )
        SELECT
            a.id AS attachment_db_id,
            m.provider_id as email_provider_id,
            a.provider_attachment_id as provider_attachment_id,
            a.filename as filename,
            a.mime_type as mime_type,
            m.internal_date_ts as internal_date_ts,
            m.id as message_db_id,
            m.thread_id as thread_db_id,
            from_contact.email_address as sender_email,
            m.subject as subject
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        JOIN email_contacts from_contact ON m.from_contact_id = from_contact.id
        WHERE a.id IN (SELECT id FROM claimed)
        ORDER BY a.id
        "#,
        ATTACHMENT_MIME_TYPE_FILTERS, ATTACHMENT_WHITELISTED_DOMAINS
    );

    let rows = sqlx::query(&query1)
        .bind(message_provider_id)
        .bind(link_id)
        .fetch_all(db)
        .await?;

    let attachments: Vec<AttachmentUploadMetadata> = rows
        .into_iter()
        .map(map_row_to_attachment_metadata)
        .collect();

    // if one or more condition has already been met, return
    if !attachments.is_empty() {
        return Ok(attachments);
    }

    // query for condition 5: claim attachments atomically and return metadata
    let query2 = format!(
        r#"
        WITH
        message_info AS (
            SELECT thread_id
            FROM email_messages
            WHERE link_id = $2
                AND provider_id = $1
        ),
        self_contact AS (
            SELECT c.id
            FROM email_links l
            JOIN email_contacts c
                ON c.link_id = l.id
                AND LOWER(c.email_address) = LOWER(l.email_address)
            WHERE l.id = $2
        ),
        participants AS (
            SELECT em.from_contact_id AS contact_id
            FROM email_messages em
            WHERE em.thread_id = (SELECT thread_id FROM message_info)
                AND em.from_contact_id IS NOT NULL
            UNION
            SELECT emr.contact_id
            FROM email_messages em
            JOIN email_message_recipients emr ON emr.message_id = em.id
            WHERE em.thread_id = (SELECT thread_id FROM message_info)
        ),
        claimed AS (
            UPDATE email_attachments
            SET upload_claimed_at = NOW()
            WHERE id IN (
                SELECT a.id
                FROM email_attachments a
                JOIN email_messages m ON a.message_id = m.id
                LEFT JOIN document_email de ON de.email_attachment_id = a.id
                WHERE m.link_id = $2
                    AND m.provider_id = $1
                    AND de.email_attachment_id IS NULL
                    AND a.upload_claimed_at IS NULL
                    AND a.filename IS NOT NULL
                    {}
                    AND EXISTS (
                        SELECT 1
                        FROM participants p
                        WHERE NOT EXISTS (
                                SELECT 1
                                FROM self_contact
                                WHERE self_contact.id = p.contact_id
                            )
                            AND EXISTS (
                                SELECT 1
                                FROM email_message_recipients emr
                                JOIN email_messages sent_message ON sent_message.id = emr.message_id
                                WHERE emr.contact_id = p.contact_id
                                    AND sent_message.link_id = $2
                                    AND sent_message.is_sent = true
                            )
                    )
            )
            RETURNING id
        )
        SELECT
            a.id AS attachment_db_id,
            m.provider_id as email_provider_id,
            a.provider_attachment_id as provider_attachment_id,
            a.filename as filename,
            a.mime_type as mime_type,
            m.internal_date_ts as internal_date_ts,
            m.id as message_db_id,
            m.thread_id as thread_db_id,
            from_contact.email_address as sender_email,
            m.subject as subject
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        JOIN email_contacts from_contact ON m.from_contact_id = from_contact.id
        WHERE a.id IN (SELECT id FROM claimed)
        ORDER BY a.id
        "#,
        ATTACHMENT_MIME_TYPE_FILTERS
    );

    let rows = sqlx::query(&query2)
        .bind(message_provider_id)
        .bind(link_id)
        .fetch_all(db)
        .await?;

    let attachments = rows
        .into_iter()
        .map(map_row_to_attachment_metadata)
        .collect();

    Ok(attachments)
}

/// Fetch and atomically claim videos and inline images for a new email for insertion into sfs.
/// We insert them into sfs so we can display thumbnails for them in the FE.
///
/// The query atomically claims attachments (sets upload_claimed_at) to prevent duplicate
/// uploads from concurrent workers processing the same message.
#[tracing::instrument(skip(db), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn new_email_media_atts(
    db: &Pool<Postgres>,
    link_id: Uuid,
    message_provider_id: &str,
) -> anyhow::Result<Vec<AttachmentUploadMetadata>> {
    let query = format!(
        r#"
        WITH claimed AS (
            UPDATE email_attachments
            SET upload_claimed_at = NOW()
            WHERE id IN (
                SELECT a.id
                FROM email_attachments a
                JOIN email_messages m ON a.message_id = m.id
                LEFT JOIN email_attachments_sfs eas ON eas.attachment_id = a.id
                WHERE m.link_id = $2
                    AND m.provider_id = $1
                    AND {}
                    AND eas.attachment_id IS NULL
                    AND a.upload_claimed_at IS NULL
            )
            RETURNING id
        )
        SELECT
            a.id AS attachment_db_id,
            m.provider_id as email_provider_id,
            a.provider_attachment_id as provider_attachment_id,
            a.filename as filename,
            a.mime_type as mime_type,
            m.internal_date_ts as internal_date_ts,
            m.id as message_db_id,
            m.thread_id as thread_db_id,
            from_contact.email_address as sender_email,
            m.subject as subject
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        JOIN email_contacts from_contact ON m.from_contact_id = from_contact.id
        WHERE a.id IN (SELECT id FROM claimed)
        ORDER BY a.id
        "#,
        ATTACHMENT_MIME_TYPE_FILTERS_WITH_MEDIA
    );

    let rows = sqlx::query(&query)
        .bind(message_provider_id)
        .bind(link_id)
        .fetch_all(db)
        .await?;

    let attachments = rows
        .into_iter()
        .map(map_row_to_attachment_metadata)
        .collect();

    Ok(attachments)
}

/// Releases an attachment's upload claim so the next claiming query picks it up again.
#[tracing::instrument(skip(db), err)]
pub async fn release_upload_claim(db: &Pool<Postgres>, attachment_id: Uuid) -> anyhow::Result<()> {
    sqlx::query!(
        "UPDATE email_attachments SET upload_claimed_at = NULL WHERE id = $1",
        attachment_id
    )
    .execute(db)
    .await?;

    Ok(())
}

pub async fn fetch_attachment_upload_metadata_by_id(
    db: &Pool<Postgres>,
    attachment_id: Uuid,
) -> anyhow::Result<Option<AttachmentUploadMetadata>> {
    let row = sqlx::query_as!(
        AttachmentUploadMetadata,
        r#"
        SELECT
            a.id AS attachment_db_id,
            m.provider_id as "email_provider_id!",
            a.provider_attachment_id as "provider_attachment_id!",
            a.filename as "filename?",
            a.mime_type as "mime_type!",
            m.internal_date_ts as "internal_date_ts!",
            m.id as message_db_id,
            m.thread_id as thread_db_id,
            from_contact.email_address as sender_email,
            m.subject as subject
        FROM email_attachments a
        JOIN email_messages m ON a.message_id = m.id
        JOIN email_contacts from_contact ON m.from_contact_id = from_contact.id
        JOIN email_threads t ON m.thread_id = t.id
        WHERE a.id = $1
        "#,
        attachment_id
    )
    .fetch_optional(db)
    .await?;

    Ok(row)
}

/// Helper function to map a database row to AttachmentUploadMetadata
fn map_row_to_attachment_metadata(row: sqlx::postgres::PgRow) -> AttachmentUploadMetadata {
    AttachmentUploadMetadata {
        attachment_db_id: row.get("attachment_db_id"),
        email_provider_id: row.get("email_provider_id"),
        provider_attachment_id: row.get("provider_attachment_id"),
        filename: row.get("filename"),
        mime_type: row.get("mime_type"),
        internal_date_ts: row.get("internal_date_ts"),
        message_db_id: row.get("message_db_id"),
        thread_db_id: row.get("thread_db_id"),
        sender_email: row.get("sender_email"),
        subject: row.get("subject"),
    }
}

#[cfg(test)]
mod test;