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
#[cfg(test)]
mod test;
pub mod upload;
pub mod upload_filters;
use crate::parse::db_to_service::map_db_attachment_to_service;
use crate::parse::service_to_db::map_service_attachments_to_db;
use models_email::db::attachment;
use models_email::{db, service};
use sqlx::types::Uuid;
use sqlx::{Executor, PgPool, Pool, Postgres};
use std::collections::HashMap;
/// inserts the metadata for attachments of an email into the database in a batch
#[tracing::instrument(skip(tx, attachments, message_id), err)]
#[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
pub async fn insert_attachments(
tx: &mut sqlx::PgConnection,
message_id: Uuid,
attachments: &mut [service::attachment::Attachment],
) -> anyhow::Result<()> {
let stable_ids=sqlx::query_scalar!(r#"SELECT l.provider='OUTLOOK' AS "stable!" FROM email_messages m JOIN email_links l ON l.id=m.link_id WHERE m.id=$1"#,message_id)
.fetch_one(&mut *tx).await?;
if attachments.is_empty() && !stable_ids {
return Ok(());
}
if stable_ids
&& attachments
.iter()
.any(|attachment| attachment.provider_id.is_none())
{
anyhow::bail!("Outlook attachment snapshot is missing a provider identifier");
}
let db_attachments = map_service_attachments_to_db(attachments, message_id);
let existing_attachments = sqlx::query_as!(
db::attachment::Attachment,
r#"
SELECT ea.id, ea.message_id, ea.provider_attachment_id, ea.filename, ea.mime_type, ea.size_bytes, ea.content_id, eas.sfs_id as "sfs_id?", ea.created_at, ea.reference_url
FROM email_attachments ea LEFT JOIN email_attachments_sfs eas on ea.id = eas.attachment_id
WHERE message_id = $1
"#,
message_id
).fetch_all(&mut *tx).await?;
// Outlook attachment IDs remain stable until replacement. Gmail retains its
// metadata fallback because its attachment fetch IDs may rotate.
let orphaned_attachments: Vec<_> = existing_attachments
.iter()
.filter(|existing| {
!db_attachments.iter().any(|attachment| {
if stable_ids {
existing.provider_attachment_id == attachment.provider_attachment_id
} else {
existing.filename == attachment.filename
&& existing.mime_type == attachment.mime_type
&& existing.size_bytes == attachment.size_bytes
&& existing.content_id == attachment.content_id
&& existing.reference_url == attachment.reference_url
}
})
})
.cloned()
.collect();
// Refresh Outlook metadata without changing the Macro attachment identity.
let new_attachments: Vec<_> = db_attachments
.into_iter()
.filter(|attachment| {
stable_ids
|| !existing_attachments.iter().any(|existing| {
existing.filename == attachment.filename
&& existing.mime_type == attachment.mime_type
&& existing.size_bytes == attachment.size_bytes
&& existing.content_id == attachment.content_id
&& existing.reference_url == attachment.reference_url
})
})
.collect();
// delete orphaned attachments
if !orphaned_attachments.is_empty() {
let orphaned_ids: Vec<Uuid> = orphaned_attachments
.iter()
.map(|orphan| orphan.id)
.collect();
sqlx::query!(
r#"
DELETE FROM email_attachments
WHERE id = ANY($1::uuid[])
"#,
&orphaned_ids
)
.execute(&mut *tx)
.await?;
}
// The thread-level flag only moves when a calendar attachment enters or
// leaves this message's attachment set.
let calendar_set_changed = orphaned_attachments
.iter()
.chain(new_attachments.iter())
.any(|a| is_calendar_attachment(a.filename.as_deref(), a.mime_type.as_deref()));
if new_attachments.is_empty() {
if calendar_set_changed {
sync_thread_calendar_flag(&mut *tx, message_id).await?;
}
return Ok(());
}
let n = new_attachments.len();
let mut attachment_ids_repeated: Vec<Uuid> = Vec::with_capacity(n);
let mut message_ids_repeated: Vec<Uuid> = Vec::with_capacity(n);
let mut provider_attachment_ids: Vec<Option<String>> = Vec::with_capacity(n);
let mut filenames: Vec<Option<String>> = Vec::with_capacity(n);
let mut mime_types: Vec<Option<String>> = Vec::with_capacity(n);
let mut size_bytes_vec: Vec<Option<i64>> = Vec::with_capacity(n);
let mut content_ids: Vec<Option<String>> = Vec::with_capacity(n);
let mut reference_urls: Vec<Option<String>> = Vec::with_capacity(n);
for attachment in new_attachments.into_iter() {
reference_urls.push(attachment.reference_url);
attachment_ids_repeated.push(attachment.id);
message_ids_repeated.push(attachment.message_id);
provider_attachment_ids.push(attachment.provider_attachment_id);
filenames.push(attachment.filename);
mime_types.push(attachment.mime_type);
size_bytes_vec.push(attachment.size_bytes);
content_ids.push(attachment.content_id);
}
// Use query() for unnest as query! has trouble inferring all Option types
sqlx::query(
r#"
WITH input_rows (
id, message_id, provider_attachment_id, filename, mime_type, size_bytes, content_id, reference_url
) AS (
SELECT * FROM unnest(
$1::uuid[], $2::uuid[], $3::text[], $4::varchar[], $5::varchar[], $6::bigint[], $7::varchar[], $8::text[]
)
)
INSERT INTO email_attachments (
id, message_id, provider_attachment_id, filename, mime_type, size_bytes, content_id, reference_url
)
SELECT id, message_id, provider_attachment_id, filename, mime_type, size_bytes, content_id, reference_url
FROM input_rows
ON CONFLICT (message_id, provider_attachment_id)
DO UPDATE SET filename=EXCLUDED.filename,mime_type=EXCLUDED.mime_type,
size_bytes=EXCLUDED.size_bytes,content_id=EXCLUDED.content_id,reference_url=EXCLUDED.reference_url
"#,
)
.bind(&attachment_ids_repeated)
.bind(&message_ids_repeated)
.bind(&provider_attachment_ids)
.bind(&filenames)
.bind(&mime_types)
.bind(&size_bytes_vec)
.bind(&content_ids)
.bind(&reference_urls)
.execute(&mut *tx)
.await?;
if calendar_set_changed {
sync_thread_calendar_flag(&mut *tx, message_id).await?;
}
Ok(())
}
/// Mirrors the SQL calendar-attachment predicate behind the CalendarOnly
/// email filter: `filename ILIKE '%.ics' OR mime_type IN ('text/calendar',
/// 'application/ics')`.
fn is_calendar_attachment(filename: Option<&str>, mime_type: Option<&str>) -> bool {
filename.is_some_and(|f| f.to_ascii_lowercase().ends_with(".ics"))
|| mime_type.is_some_and(|m| m == "text/calendar" || m == "application/ics")
}
/// Resolves the message's thread and recomputes its denormalized
/// `has_calendar_attachment` flag.
async fn sync_thread_calendar_flag(
tx: &mut sqlx::PgConnection,
message_id: Uuid,
) -> anyhow::Result<()> {
let thread_id: Option<Uuid> = sqlx::query_scalar!(
"SELECT thread_id FROM email_messages WHERE id = $1",
message_id
)
.fetch_optional(&mut *tx)
.await?;
if let Some(thread_id) = thread_id {
crate::threads::update::sync_thread_calendar_flag(tx, thread_id).await?;
}
Ok(())
}
/// Fetches attachments for multiple messages and returns a map keyed by message_id
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_db_attachments_in_bulk(
pool: &PgPool,
message_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, Vec<attachment::Attachment>>> {
if message_ids.is_empty() {
return Ok(HashMap::new());
}
let results = sqlx::query_as!(
db::attachment::Attachment,
r#"
SELECT ea.id, ea.message_id, ea.provider_attachment_id, ea.filename, ea.mime_type, ea.size_bytes, ea.content_id, eas.sfs_id as "sfs_id?", ea.created_at, ea.reference_url
FROM email_attachments ea
LEFT JOIN email_attachments_sfs eas ON ea.id = eas.attachment_id
WHERE ea.message_id = ANY($1)
ORDER BY ea.message_id, ea.filename NULLS LAST
"#,
message_ids
)
.fetch_all(pool)
.await?;
let mut attachments_map: HashMap<Uuid, Vec<attachment::Attachment>> = HashMap::new();
for attachment in results {
attachments_map
.entry(attachment.message_id)
.or_default()
.push(attachment);
}
Ok(attachments_map)
}
// deletes all attachments of a given message
#[tracing::instrument(skip(executor), err)]
pub async fn delete_message_attachments<'e, E>(executor: E, message_id: Uuid) -> anyhow::Result<()>
where
E: Executor<'e, Database = Postgres>,
{
sqlx::query!(
r#"DELETE FROM email_attachments WHERE message_id = $1"#,
message_id
)
.execute(executor)
.await?;
Ok(())
}
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_attachment_by_id(
pool: &PgPool,
attachment_id: Uuid,
link_id: Uuid,
) -> anyhow::Result<Option<(service::attachment::Attachment, String)>> {
// Modified query to include message.provider_id and sfs_id
let result = sqlx::query!(
r#"
SELECT
a.id, a.message_id, a.provider_attachment_id,
a.filename, a.mime_type, a.size_bytes,
a.content_id, eas.sfs_id as "sfs_id?", a.created_at, a.reference_url, m.provider_id as "message_provider_id!"
FROM email_attachments a
JOIN email_messages m ON a.message_id = m.id
LEFT JOIN email_attachments_sfs eas ON a.id = eas.attachment_id
WHERE a.id = $1 AND m.link_id = $2
"#,
attachment_id,
link_id
)
.fetch_optional(pool)
.await?;
match result {
Some(record) => {
// Construct the attachment record
let db_att = db::attachment::Attachment {
id: record.id,
message_id: record.message_id,
provider_attachment_id: record.provider_attachment_id,
filename: record.filename,
mime_type: record.mime_type,
size_bytes: record.size_bytes,
content_id: record.content_id,
sfs_id: record.sfs_id,
created_at: record.created_at,
reference_url: record.reference_url,
};
// Map to service attachment and include the message provider ID
let service_att = map_db_attachment_to_service(db_att);
Ok(Some((service_att, record.message_provider_id)))
}
None => Ok(None),
}
}
#[tracing::instrument(skip(db), err)]
pub async fn get_attachments_by_thread_ids(
db: &Pool<Postgres>,
thread_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, Vec<service::attachment::Attachment>>> {
if thread_ids.is_empty() {
return Ok(HashMap::new());
}
// Query all attachments for messages in the provided threads
// Include thread_id in the result set
let attachments = sqlx::query!(
r#"
SELECT
a.id,
a.message_id,
a.provider_attachment_id,
a.filename,
a.mime_type,
a.size_bytes,
a.content_id,
eas.sfs_id as "sfs_id?",
a.created_at, a.reference_url,
m.thread_id
FROM
email_attachments a
JOIN
email_messages m ON a.message_id = m.id
LEFT JOIN
email_attachments_sfs eas ON a.id = eas.attachment_id
WHERE
m.thread_id = ANY($1)
ORDER BY
a.created_at ASC
"#,
thread_ids
)
.fetch_all(db)
.await?;
// Group attachments by thread_id
let mut result: HashMap<Uuid, Vec<service::attachment::Attachment>> = HashMap::new();
for record in attachments {
// Construct db::attachment::Attachment from the query result
let db_attachment = db::attachment::Attachment {
id: record.id,
message_id: record.message_id,
provider_attachment_id: record.provider_attachment_id,
filename: record.filename,
mime_type: record.mime_type,
size_bytes: record.size_bytes,
content_id: record.content_id,
sfs_id: record.sfs_id,
created_at: record.created_at,
reference_url: record.reference_url,
};
// Map the db attachment to a service attachment
let service_attachment = map_db_attachment_to_service(db_attachment);
// Add to the result map
result
.entry(record.thread_id)
.or_default()
.push(service_attachment);
}
Ok(result)
}
/// Get document_id for email attachment if record exists
#[tracing::instrument(skip(db), err)]
pub async fn get_document_id_by_att_id_and_link(
db: &Pool<Postgres>,
link_id: Uuid,
email_attachment_id: Uuid,
) -> anyhow::Result<Option<String>> {
let result = sqlx::query!(
r#"
SELECT document_id
FROM document_email de
INNER JOIN "Document" d on de.document_id = d.id
INNER JOIN email_attachments ea on de.email_attachment_id = ea.id
INNER JOIN email_messages em on ea.message_id = em.id
WHERE em.link_id = $1 AND email_attachment_id = $2 AND d."deletedAt" IS NULL
"#,
link_id,
email_attachment_id,
)
.fetch_optional(db)
.await?;
Ok(result.map(|record| record.document_id))
}
/// The live document linked to an email attachment.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct LinkedAttachmentDocument {
/// The document id.
pub document_id: String,
/// The document's bytes have not finished reaching storage. DOCX is left
/// out: it stays not uploaded until its conversion finishes.
pub awaiting_upload: bool,
}
/// Get the document for email attachment if record exists
#[tracing::instrument(skip(db), err)]
pub async fn get_document_by_att_id(
db: &Pool<Postgres>,
email_attachment_id: Uuid,
) -> anyhow::Result<Option<LinkedAttachmentDocument>> {
let result = sqlx::query!(
r#"
SELECT
document_id,
(NOT d.uploaded AND d."fileType" IS DISTINCT FROM 'docx') AS "awaiting_upload!"
FROM document_email de
INNER JOIN "Document" d on de.document_id = d.id
INNER JOIN email_attachments ea on de.email_attachment_id = ea.id
INNER JOIN email_messages em on ea.message_id = em.id
WHERE email_attachment_id = $1 AND d."deletedAt" IS NULL
"#,
email_attachment_id,
)
.fetch_optional(db)
.await?;
Ok(result.map(|record| LinkedAttachmentDocument {
document_id: record.document_id,
awaiting_upload: record.awaiting_upload,
}))
}
/// Get thread_id and link_id for an email attachment
#[tracing::instrument(skip(db), err)]
pub async fn get_thread_id_for_attachment(
db: &Pool<Postgres>,
attachment_id: Uuid,
) -> anyhow::Result<Option<(Uuid, Uuid)>> {
let result = sqlx::query!(
r#"
SELECT et.id as thread_id, et.link_id
FROM email_attachments ea
INNER JOIN email_messages em ON ea.message_id = em.id
INNER JOIN email_threads et ON em.thread_id = et.id
WHERE ea.id = $1
"#,
attachment_id,
)
.fetch_optional(db)
.await?;
Ok(result.map(|record| (record.thread_id, record.link_id)))
}