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;
use crate::parse::db_to_service::map_db_contact_to_service;
use chrono::{DateTime, Utc};
use email_utils::{dedupe_emails, is_generic_email};
use models_email::service::address;
use models_email::{db, service};
use sqlx::PgPool;
use sqlx::types::Uuid;
use std::collections::{HashMap, HashSet};
pub type ThreadContactsMap = HashMap<Uuid, Vec<(String, Option<String>)>>;
/// `(email, name, first_at, last_at)` for a contact's known activity
/// range on a link — what the `populate_crm_for_user` historical seed
/// hands to `PopulateCrmContact`.
pub type CrmContactRange = (String, Option<String>, DateTime<Utc>, DateTime<Utc>);
/// fetch message sender from db
#[tracing::instrument(skip(executor), err)]
pub async fn get_sender_by_message_id<'e, E>(
executor: E,
message_id: Uuid,
) -> anyhow::Result<Option<db::contact::Contact>>
where
E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
let result = sqlx::query_as!(
db::contact::Contact,
r#"
SELECT
c.id,
c.link_id,
c.email_address,
COALESCE(m.from_name, c.name) as "name", -- name from message overrides contact name
c.original_photo_url,
c.sfs_photo_url,
c.created_at,
c.updated_at
FROM email_messages m
INNER JOIN email_contacts c ON c.id = m.from_contact_id
WHERE m.id = $1
AND m.from_contact_id IS NOT NULL
"#,
message_id
)
.fetch_optional(executor)
.await?;
Ok(result)
}
#[tracing::instrument(skip(executor), err)]
pub async fn get_senders_contacts_map<'e, E>(
executor: E,
message_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, db::contact::Contact>>
where
E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
let db_contacts = fetch_sender_contacts_by_message_ids(executor, message_ids).await?;
// Convert Vec<Contact> to HashMap<Uuid, Contact>
let contacts_map = db_contacts.into_iter().map(|c| (c.id, c)).collect();
Ok(contacts_map)
}
// Temporary struct needed because query_as! cannot directly map joined results
// into two separate structs easily within the macro itself.
struct RecipientQueryResult {
message_id: Uuid,
id: Uuid,
link_id: Uuid,
email_address: String,
name: Option<String>,
original_photo_url: Option<String>,
sfs_photo_url: Option<String>,
created_at: DateTime<Utc>,
updated_at: DateTime<Utc>,
recipient_type: db::address::EmailRecipientType,
}
/// One row per sent-message recipient on `link_id`, with the
/// `(email, name, first_at, last_at)` shape ready to fan out to
/// `PopulateCrmContact`. `first_at` / `last_at` are
/// `MIN(internal_date_ts)` / `MAX(internal_date_ts)` over the contact's
/// matching messages — so a single populate job per contact carries
/// the full known activity range. `email` is lowercased; `name` comes
/// from `email_contacts.name`. Messages with `internal_date_ts IS NULL`
/// are filtered out so the MIN/MAX is always non-null for emitted rows.
///
/// Used by the `PopulateCrmForUser` backfill step.
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_sent_message_recipient_contacts_by_link(
pool: &PgPool,
link_id: Uuid,
) -> anyhow::Result<Vec<CrmContactRange>> {
let rows = sqlx::query!(
r#"
SELECT
LOWER(c.email_address) AS "email!",
c.name AS "name",
MIN(m.internal_date_ts) AS "first_at!",
MAX(m.internal_date_ts) AS "last_at!"
FROM email_messages m
JOIN email_message_recipients r ON r.message_id = m.id
JOIN email_contacts c ON c.id = r.contact_id
WHERE m.link_id = $1
AND m.is_sent = true
AND m.internal_date_ts IS NOT NULL
GROUP BY LOWER(c.email_address), c.name
"#,
link_id,
)
.fetch_all(pool)
.await?;
Ok(rows
.into_iter()
.map(|r| (r.email, r.name, r.first_at, r.last_at))
.collect())
}
/// One row per external sender on received messages on `link_id`,
/// with the `(email, name, first_at, last_at)` shape — mirror of
/// [`fetch_sent_message_recipient_contacts_by_link`] for the received
/// direction. Drafts have `from_contact_id = NULL` so they're filtered
/// out automatically. `first_at` / `last_at` are MIN/MAX of
/// `internal_date_ts` over the contact's received messages.
///
/// Used by the `PopulateCrmForUser` backfill step to enumerate
/// external parties the link has received mail from so they can bump
/// `last_interaction` on already-tracked CRM companies (and create
/// contacts at those companies). Received-direction populates never
/// create new `crm_companies` rows — see
/// [`crm::domain::companies_repo::CompaniesRepository::populate_contact`].
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_received_sender_contacts_by_link(
pool: &PgPool,
link_id: Uuid,
) -> anyhow::Result<Vec<CrmContactRange>> {
let rows = sqlx::query!(
r#"
SELECT
LOWER(c.email_address) AS "email!",
c.name AS "name",
MIN(m.internal_date_ts) AS "first_at!",
MAX(m.internal_date_ts) AS "last_at!"
FROM email_messages m
JOIN email_contacts c ON c.id = m.from_contact_id
WHERE m.link_id = $1
AND m.is_sent = false
AND m.from_contact_id IS NOT NULL
AND m.internal_date_ts IS NOT NULL
GROUP BY LOWER(c.email_address), c.name
"#,
link_id,
)
.fetch_all(pool)
.await?;
Ok(rows
.into_iter()
.map(|r| (r.email, r.name, r.first_at, r.last_at))
.collect())
}
/// Returns true iff `link_id` still has at least one message that
/// touches `email` in either direction: a sent message whose
/// recipients (to/cc/bcc) include `email`, **or** a received message
/// whose `from_contact_id` matches `email`. Used by the
/// depopulate-CRM-contact backfill step as the pre-check — sources
/// now track interactions in both directions, so the source row must
/// stay until both directions are gone. `email` is matched
/// case-insensitively.
#[tracing::instrument(skip(pool), err)]
pub async fn link_has_any_message_with(
pool: &PgPool,
link_id: Uuid,
email: &str,
) -> anyhow::Result<bool> {
let normalized = email.to_ascii_lowercase();
sqlx::query_scalar!(
r#"
SELECT EXISTS (
SELECT 1
FROM email_messages m
JOIN email_message_recipients r ON r.message_id = m.id
JOIN email_contacts c ON c.id = r.contact_id
WHERE m.link_id = $1
AND m.is_sent = true
AND c.link_id = $1
AND LOWER(c.email_address) = $2
UNION ALL
SELECT 1
FROM email_messages m
JOIN email_contacts c ON c.id = m.from_contact_id
WHERE m.link_id = $1
AND m.is_sent = false
AND c.link_id = $1
AND LOWER(c.email_address) = $2
LIMIT 1
) AS "exists!"
"#,
link_id,
normalized,
)
.fetch_one(pool)
.await
.map_err(Into::into)
}
/// fetch recipients (to, cc, bcc) from db
#[tracing::instrument(skip(executor), err)]
pub async fn fetch_db_recipients<'e, E>(
executor: E,
message_db_id: Uuid,
) -> anyhow::Result<Vec<(db::contact::Contact, db::address::EmailRecipientType)>>
where
E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
// Fetch address and recipient type together
sqlx::query_as!(
RecipientQueryResult,
r#"
SELECT
mr.message_id,
c.id,
c.link_id,
c.email_address,
COALESCE(mr.name, c.name) as "name", -- name from message overrides contact name
c.original_photo_url,
c.sfs_photo_url,
c.created_at,
c.updated_at,
mr.recipient_type as "recipient_type!: db::address::EmailRecipientType"
FROM email_message_recipients mr
JOIN email_contacts c ON mr.contact_id = c.id
WHERE mr.message_id = $1
ORDER BY mr.recipient_type
"#,
message_db_id
)
.map(|row| {
(
db::contact::Contact {
id: row.id,
link_id: row.link_id,
email_address: Some(row.email_address),
name: row.name,
original_photo_url: row.original_photo_url,
sfs_photo_url: row.sfs_photo_url,
created_at: row.created_at,
updated_at: row.updated_at,
},
row.recipient_type,
)
})
.fetch_all(executor)
.await
.map_err(Into::into)
}
/// Fetches all recipients for a given list of message IDs in a single query
#[tracing::instrument(skip(executor), err)]
pub async fn fetch_db_recipients_in_bulk<'e, E>(
executor: E,
message_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, Vec<(db::contact::Contact, db::address::EmailRecipientType)>>>
where
E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
if message_ids.is_empty() {
return Ok(HashMap::new());
}
let results = sqlx::query_as!(
RecipientQueryResult,
r#"
SELECT
mr.message_id,
c.id,
c.link_id,
c.email_address,
COALESCE(mr.name, c.name) as "name", -- name from message overrides contact name
c.original_photo_url,
c.sfs_photo_url,
c.created_at,
c.updated_at,
mr.recipient_type as "recipient_type!: db::address::EmailRecipientType"
FROM email_messages m
JOIN email_message_recipients mr ON m.id = mr.message_id
JOIN email_contacts c ON mr.contact_id = c.id
WHERE
m.id = ANY($1)
ORDER BY mr.message_id, mr.recipient_type
"#,
message_ids
)
.fetch_all(executor)
.await?;
let mut recipients_map = HashMap::new();
for row in results {
let email_address = db::contact::Contact {
id: row.id,
link_id: row.link_id,
email_address: Some(row.email_address),
original_photo_url: row.original_photo_url,
sfs_photo_url: row.sfs_photo_url,
name: row.name,
created_at: row.created_at,
updated_at: row.updated_at,
};
recipients_map
.entry(row.message_id)
.or_insert_with(Vec::new)
.push((email_address, row.recipient_type));
}
Ok(recipients_map)
}
/// Fetch UUID for a given email address
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_id_by_email(
pool: &mut sqlx::PgConnection,
link_id: Uuid,
email_address: &str,
) -> anyhow::Result<Option<Uuid>> {
sqlx::query_scalar!(
r#"
SELECT id
FROM email_contacts
WHERE LOWER(email_address) = LOWER($1) AND link_id = $2
"#,
email_address,
link_id
)
.fetch_optional(pool)
.await
.map_err(Into::into)
}
/// Fetch contact for a given email address
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_contact_by_email(
pool: &PgPool,
link_id: Uuid,
email_address: &str,
) -> anyhow::Result<Option<service::address::ContactInfo>> {
let contact = sqlx::query_as!(
db::contact::Contact,
r#"
SELECT id, link_id, email_address, name, original_photo_url, sfs_photo_url, created_at, updated_at
FROM email_contacts
WHERE LOWER(email_address) = LOWER($1) AND link_id = $2
"#,
email_address,
link_id
)
.fetch_optional(pool)
.await?;
Ok(contact.and_then(|c| map_db_contact_to_service(Some(c))))
}
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_sender_contact_info(
pool: &PgPool,
message_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, address::ContactInfo>> {
let db_contacts = fetch_sender_contacts_by_message_ids(pool, message_ids).await?;
let mut result = HashMap::with_capacity(db_contacts.len());
for addr in db_contacts {
let id = addr.id;
let contact_info = map_db_contact_to_service(Some(addr));
result.insert(id, contact_info.unwrap()); // always exists since we are wrapping arg in option above
}
Ok(result)
}
#[tracing::instrument(skip(executor), err)]
pub async fn fetch_sender_contacts_by_message_ids<'e, E>(
executor: E,
message_ids: &[Uuid],
) -> anyhow::Result<Vec<db::contact::Contact>>
where
E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
if message_ids.is_empty() {
return Ok(Vec::new());
}
// Create a set of unique IDs to query
let unique_ids: HashSet<Uuid> = message_ids.iter().cloned().collect();
let unique_ids_vec: Vec<Uuid> = unique_ids.into_iter().collect();
// Fetch all email addresses that exist
sqlx::query_as!(
db::contact::Contact,
r#"
SELECT
c.id,
c.link_id,
c.email_address,
COALESCE(m.from_name, c.name) as "name", -- name from message overrides contact name
c.original_photo_url,
c.sfs_photo_url,
c.created_at,
c.updated_at
FROM email_messages m
INNER JOIN email_contacts c ON c.id = m.from_contact_id
WHERE m.id = ANY($1)
AND m.from_contact_id IS NOT NULL
"#,
&unique_ids_vec
)
.fetch_all(executor)
.await
.map_err(Into::into)
}
/// Fetches sender contacts for multiple messages and returns a map keyed by message_id
#[tracing::instrument(skip(executor), err)]
pub async fn fetch_senders_by_message_ids<'e, E>(
executor: E,
message_ids: &[Uuid],
) -> anyhow::Result<HashMap<Uuid, db::contact::Contact>>
where
E: sqlx::Executor<'e, Database = sqlx::Postgres>,
{
if message_ids.is_empty() {
return Ok(HashMap::new());
}
// Temporary struct to capture message_id along with contact data
struct SenderQueryResult {
message_id: Uuid,
id: Uuid,
link_id: Uuid,
email_address: Option<String>,
name: Option<String>,
original_photo_url: Option<String>,
sfs_photo_url: Option<String>,
created_at: chrono::DateTime<chrono::Utc>,
updated_at: chrono::DateTime<chrono::Utc>,
}
let results = sqlx::query_as!(
SenderQueryResult,
r#"
SELECT
m.id as message_id,
c.id,
c.link_id,
c.email_address,
COALESCE(m.from_name, c.name) as "name",
c.original_photo_url,
c.sfs_photo_url,
c.created_at,
c.updated_at
FROM email_messages m
INNER JOIN email_contacts c ON c.id = m.from_contact_id
WHERE m.id = ANY($1)
AND m.from_contact_id IS NOT NULL
"#,
message_ids
)
.fetch_all(executor)
.await?;
let mut senders_map = HashMap::new();
for row in results {
let contact = db::contact::Contact {
id: row.id,
link_id: row.link_id,
email_address: row.email_address,
name: row.name,
original_photo_url: row.original_photo_url,
sfs_photo_url: row.sfs_photo_url,
created_at: row.created_at,
updated_at: row.updated_at,
};
senders_map.insert(row.message_id, contact);
}
Ok(senders_map)
}
/// Fetches all unique sender contacts for given thread IDs, ordered by message creation time.
/// Returns a map of thread_id -> Vec<(email, name)>.
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_contacts_by_thread_ids(
pool: &PgPool,
thread_ids: &[Uuid],
) -> anyhow::Result<ThreadContactsMap> {
if thread_ids.is_empty() {
return Ok(HashMap::new());
}
struct Row {
thread_id: Uuid,
email_address: String,
name: Option<String>,
}
let rows = sqlx::query_as!(
Row,
r#"
SELECT
m.thread_id,
c.email_address as "email_address!",
COALESCE(m.from_name, c.name) as "name"
FROM email_messages m
JOIN email_contacts c ON m.from_contact_id = c.id
WHERE m.thread_id = ANY($1) AND m.from_contact_id IS NOT NULL
ORDER BY m.created_at ASC
"#,
thread_ids
)
.fetch_all(pool)
.await?;
let mut result: ThreadContactsMap = HashMap::new();
for row in rows {
let contacts = result.entry(row.thread_id).or_default();
if !contacts
.iter()
.any(|(e, n)| *e == row.email_address && *n == row.name)
{
contacts.push((row.email_address, row.name));
}
}
Ok(result)
}
/// returns all email addresses and names the passed link has sent emails to.
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_contacts_by_link_id(
pool: &PgPool,
link_id: Uuid,
) -> anyhow::Result<Vec<address::ContactInfoWithInteraction>> {
// Execute the query directly returning service::Contact objects
let db_contacts = sqlx::query_as!(
db::contact::ContactWithInteraction,
r#"
WITH
-- Get all individual interactions with timestamps
LinkMessageTimestamps AS (
SELECT
m.link_id,
mr.contact_id AS contact_address_id,
m.internal_date_ts
FROM
email_messages m
JOIN
email_message_recipients mr ON m.id = mr.message_id
WHERE
m.link_id = $1
AND m.is_sent = TRUE
AND mr.contact_id IS NOT NULL
),
-- Get the latest interaction timestamp for each contact_address_id
LatestContactInteractions AS (
SELECT
lmt.link_id,
lmt.contact_address_id,
MAX(lmt.internal_date_ts) AS last_interaction_ts
FROM
LinkMessageTimestamps lmt
WHERE
lmt.contact_address_id IS NOT NULL
GROUP BY
lmt.link_id,
lmt.contact_address_id
)
-- Final SELECT to join with email_addresses and get details
SELECT
c.email_address as "email_address!",
c.name as "name?",
c.sfs_photo_url as "photo_url?",
lci.last_interaction_ts as "last_interaction!"
FROM
LatestContactInteractions lci
JOIN
email_contacts c ON lci.contact_address_id = c.id
ORDER BY
lci.last_interaction_ts DESC, c.email_address
"#,
link_id
)
.fetch_all(pool)
.await?;
Ok(db_contacts.into_iter().map(Into::into).collect())
}
/// Every non-automated address in the link's synced address book: contacts the
/// user saved in Google plus the "other contacts" Google builds from mail. Unlike
/// [`fetch_contacts_emails_by_link_id`] this does not require the user to have
/// emailed the address.
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_address_book_emails_by_link_id(
pool: &PgPool,
link_id: Uuid,
) -> anyhow::Result<Vec<String>> {
let rows = sqlx::query!(
r#"
SELECT DISTINCT
LOWER(email_address) AS "email!"
FROM
email_contacts
WHERE
link_id = $1
ORDER BY
1
"#,
link_id
)
.fetch_all(pool)
.await?;
Ok(rows
.into_iter()
.map(|row| row.email)
.filter(|email| !is_generic_email(email))
.collect())
}
/// The non-automated addresses the link has sent the most messages to, most
/// emailed first, at most `limit` of them.
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_top_sent_contact_emails_by_link_id(
pool: &PgPool,
link_id: Uuid,
limit: usize,
) -> anyhow::Result<Vec<String>> {
// Automated addresses are dropped after the query, so read a wider window
// than the caller asked for.
let window = i64::try_from(limit.saturating_mul(5)).unwrap_or(i64::MAX);
let rows = sqlx::query!(
r#"
SELECT
LOWER(c.email_address) AS "email!"
FROM
email_messages m
JOIN
email_message_recipients mr ON m.id = mr.message_id
JOIN
email_contacts c ON mr.contact_id = c.id
WHERE
m.link_id = $1
AND m.is_sent = TRUE
AND m.is_draft = FALSE
GROUP BY
LOWER(c.email_address)
ORDER BY
COUNT(DISTINCT m.id) DESC,
LOWER(c.email_address)
LIMIT $2
"#,
link_id,
window
)
.fetch_all(pool)
.await?;
Ok(rows
.into_iter()
.map(|row| row.email)
.filter(|email| !is_generic_email(email))
.take(limit)
.collect())
}
/// returns all non-generic email addresses the passed link has sent emails to.
#[tracing::instrument(skip(pool), err)]
pub async fn fetch_contacts_emails_by_link_id(
pool: &PgPool,
link_id: Uuid,
) -> anyhow::Result<Vec<String>> {
let rows = sqlx::query!(
r#"
SELECT DISTINCT
c.email_address as "email_address!"
FROM
email_messages m
JOIN
email_message_recipients mr ON m.id = mr.message_id
JOIN
email_contacts c ON mr.contact_id = c.id
WHERE
m.link_id = $1
AND m.is_sent = TRUE
AND mr.contact_id IS NOT NULL
ORDER BY
c.email_address
"#,
link_id
)
.fetch_all(pool)
.await?;
let deduped = dedupe_emails(rows.into_iter().map(|row| row.email_address).collect());
Ok(deduped
.into_iter()
.filter(|e| !is_generic_email(e))
.collect())
}