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
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;