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
//! Postgres storage for Focus classifications and the thread reads behind them.
use std::{collections::HashMap, str::FromStr};
use chrono::{DateTime, Duration, Utc};
use macro_user_id::user_id::MacroUserIdStr;
use rootcause::Report;
use sqlx::PgPool;
use uuid::Uuid;
use crate::{
domain::focus::{
CalendarPart, FOCUS_RULE_VERSION, FocusAnswers, FocusCategory, FocusInbox, FocusMail,
FocusMessage, FocusRecord, FocusStore, ImportanceTrend, RECENT_CONTACT_DAYS, SentNote,
StaleThread, StoredClassification, ThreadFocus, change_subject_pattern, importance_at,
},
outbound::invitation_pg,
};
/// Most recent messages read per thread.
const THREAD_MESSAGES: i64 = 200;
/// Characters of each message body read.
const BODY_CHARS: i32 = 6_000;
/// The owner's sent messages read when judging the relationship.
const SENT_NOTES: i64 = 100;
/// Characters of each sent message read.
const SENT_NOTE_CHARS: i32 = 2_000;
/// Focus storage on the Macro database.
#[derive(Clone)]
pub struct FocusPgRepository(pub PgPool);
impl FocusPgRepository {
async fn messages(&self, thread_id: Uuid) -> Result<Vec<FocusMessage>, Report> {
let rows = sqlx::query!(
r#"
SELECT
m.id,
COALESCE(m.internal_date_ts, m.created_at) AS "at!",
m.is_sent,
m.has_attachments,
m.subject,
m.snippet,
LEFT(m.body_text, $2) AS body,
c.email_address AS "from_email?",
COALESCE(m.from_name, c.name) AS from_name,
COALESCE((
SELECT array_agg(rc.email_address ORDER BY rc.email_address)
FROM email_message_recipients r
JOIN email_contacts rc ON rc.id = r.contact_id
WHERE r.message_id = m.id AND r.recipient_type = 'TO'
), '{}') AS "to_emails!",
COALESCE((
SELECT array_agg(rc.email_address ORDER BY rc.email_address)
FROM email_message_recipients r
JOIN email_contacts rc ON rc.id = r.contact_id
WHERE r.message_id = m.id AND r.recipient_type = 'CC'
), '{}') AS "cc_emails!",
EXISTS (
SELECT 1
FROM jsonb_array_elements(
CASE WHEN jsonb_typeof(m.headers_jsonb) = 'array'
THEN m.headers_jsonb ELSE '[]'::jsonb END
) h
WHERE LOWER(h->>'name') IN (
'list-unsubscribe', 'list-id', 'feedback-id', 'x-sg-eid',
'x-ses-outgoing', 'x-mailgun-tag', 'x-campaign'
)
OR (LOWER(h->>'name') = 'precedence'
AND LOWER(h->>'value') IN ('bulk', 'list', 'junk'))
) AS "bulk!",
EXISTS (
SELECT 1 FROM email_message_calendar_invites i
WHERE i.message_id = m.id
) OR EXISTS (
SELECT 1 FROM email_attachments a
WHERE a.message_id = m.id
AND (a.filename ILIKE '%.ics'
OR a.mime_type IN ('text/calendar', 'application/ics'))
) AS "has_calendar!",
(
SELECT h->>'value'
FROM jsonb_array_elements(
CASE WHEN jsonb_typeof(m.headers_jsonb) = 'array'
THEN m.headers_jsonb ELSE '[]'::jsonb END
) h
WHERE LOWER(h->>'name') = 'date'
LIMIT 1
) AS date_header
FROM email_messages m
LEFT JOIN email_contacts c ON c.id = m.from_contact_id
WHERE m.thread_id = $1
AND NOT m.is_draft
AND NOT EXISTS (
SELECT 1 FROM email_message_labels ml
JOIN email_labels l ON l.id = ml.label_id
WHERE ml.message_id = m.id AND l.name = 'TRASH'
)
ORDER BY COALESCE(m.internal_date_ts, m.created_at) DESC, m.id DESC
LIMIT $3
"#,
thread_id,
BODY_CHARS,
THREAD_MESSAGES,
)
.fetch_all(&self.0)
.await?;
let with_calendar = rows
.iter()
.filter(|row| row.has_calendar)
.map(|row| row.id)
.collect::<Vec<_>>();
let invites = if with_calendar.is_empty() {
HashMap::new()
} else {
invitation_pg::load(&self.0, &with_calendar).await?
};
let mut messages = rows
.into_iter()
.map(|row| {
// A calendar file or snapshot nothing could read still is one.
let calendar_part = row.has_calendar.then(|| {
invites
.get(&row.id)
.and_then(|invites| CalendarPart::from_saved(invites, row.at))
.unwrap_or(CalendarPart::Unparsed)
});
FocusMessage {
id: row.id,
at: row.at,
is_sent: row.is_sent,
from_email: row.from_email,
from_name: row.from_name,
to: row.to_emails,
cc: row.cc_emails,
subject: row.subject,
has_attachments: row.has_attachments,
bulk: row.bulk,
calendar_part,
body: row.body,
snippet: row.snippet,
date_header: row.date_header,
}
})
.collect::<Vec<_>>();
messages.reverse();
Ok(messages)
}
async fn sent_notes(
&self,
link_id: Uuid,
recipients: &[String],
) -> Result<Vec<SentNote>, Report> {
if recipients.is_empty() {
return Ok(Vec::new());
}
// Pick the newest messages first and read bodies only for those: a
// close correspondent can have thousands of messages.
let rows = sqlx::query!(
r#"
SELECT newest.recipient AS "recipient!",
COALESCE(newest.internal_date_ts, m.created_at) AS "at!",
LEFT(m.body_text, $3) AS body
FROM (
SELECT LOWER(rc.email_address) AS recipient, m.id, m.internal_date_ts
FROM email_contacts rc
JOIN email_message_recipients r ON r.contact_id = rc.id
JOIN email_messages m ON m.id = r.message_id
WHERE rc.link_id = $1
AND LOWER(rc.email_address) = ANY($2)
AND m.is_sent
AND NOT m.is_draft
ORDER BY m.internal_date_ts DESC NULLS LAST
LIMIT $4
) newest
JOIN email_messages m ON m.id = newest.id
ORDER BY newest.internal_date_ts DESC NULLS LAST
"#,
link_id,
recipients,
SENT_NOTE_CHARS,
SENT_NOTES,
)
.fetch_all(&self.0)
.await?;
Ok(rows
.into_iter()
.map(|row| SentNote {
recipient: row.recipient,
at: row.at,
body: row.body,
})
.collect())
}
}
impl FocusStore for FocusPgRepository {
#[tracing::instrument(skip(self), err)]
async fn thread_inbox(&self, thread_id: Uuid) -> Result<Option<FocusInbox>, Report> {
let Some(row) = sqlx::query!(
r#"
SELECT t.id, t.link_id, t.is_signal, t.inbox_visible, el.macro_id, el.email_address,
f.classified_message_id AS "classified_message_id?",
f.rule_version AS "rule_version?",
f.scores AS "scores?",
f.model AS "model?",
f.due_on AS "due_on?"
FROM email_threads t
JOIN email_links el ON el.id = t.link_id
LEFT JOIN email_thread_focus f ON f.thread_id = t.id
WHERE t.id = $1
"#,
thread_id,
)
.fetch_optional(&self.0)
.await?
else {
return Ok(None);
};
let classified = match (
row.classified_message_id,
row.rule_version,
row.scores,
row.model,
) {
(Some(message_id), Some(rule_version), Some(scores), Some(model)) => {
// Answers that no longer decode just go back to the classifier.
let answers = serde_json::from_value::<FocusAnswers>(scores)
.inspect_err(|_| {
tracing::warn!(thread_id = %row.id, "undecodable focus answers stored");
})
.ok();
Some(StoredClassification {
message_id,
rule_version,
answers,
model,
due_on: row.due_on,
})
}
_ => None,
};
Ok(Some(FocusInbox {
thread_id: row.id,
link_id: row.link_id,
owner: MacroUserIdStr::try_from(row.macro_id)?,
owner_email: row.email_address.to_lowercase(),
is_signal: row.is_signal,
inbox_visible: row.inbox_visible,
classified,
}))
}
#[tracing::instrument(skip(self, inbox), fields(thread_id = %inbox.thread_id), err)]
async fn thread_mail(&self, inbox: &FocusInbox) -> Result<FocusMail, Report> {
let messages = self.messages(inbox.thread_id).await?;
let mut senders = messages
.iter()
.filter(|message| !message.is_sent)
.filter_map(|message| message.from_email.as_deref())
.map(str::to_lowercase)
.filter(|sender| *sender != inbox.owner_email)
.collect::<Vec<_>>();
senders.sort_unstable();
senders.dedup();
let sent_notes = self.sent_notes(inbox.link_id, &senders).await?;
Ok(FocusMail {
messages,
sent_notes,
})
}
#[tracing::instrument(skip(self, record), fields(thread_id = %record.thread_id), err)]
async fn save(&self, record: &FocusRecord) -> Result<bool, Report> {
let scores = serde_json::to_value(record.answers)?;
// A slower classification of an older message never replaces a newer one.
let result = sqlx::query!(
r#"
INSERT INTO email_thread_focus (
thread_id, link_id, classified_message_id, classified_message_ts, is_focus,
category, importance, needs_reply, needs_follow_up, scores, model, classified_at,
rule_version, pinned, awaiting_reply, calendar_hold_until, importance_rate, due_on
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
ON CONFLICT (thread_id) DO UPDATE SET
link_id = EXCLUDED.link_id,
classified_message_id = EXCLUDED.classified_message_id,
classified_message_ts = EXCLUDED.classified_message_ts,
is_focus = EXCLUDED.is_focus,
category = EXCLUDED.category,
importance = EXCLUDED.importance,
needs_reply = EXCLUDED.needs_reply,
needs_follow_up = EXCLUDED.needs_follow_up,
scores = EXCLUDED.scores,
model = EXCLUDED.model,
classified_at = EXCLUDED.classified_at,
rule_version = EXCLUDED.rule_version,
pinned = EXCLUDED.pinned,
awaiting_reply = EXCLUDED.awaiting_reply,
calendar_hold_until = EXCLUDED.calendar_hold_until,
importance_rate = EXCLUDED.importance_rate,
due_on = EXCLUDED.due_on
WHERE email_thread_focus.classified_message_ts <= EXCLUDED.classified_message_ts
"#,
record.thread_id,
record.link_id,
record.classified_message_id,
record.classified_message_ts,
record.verdict.is_focus,
record.verdict.category.to_string(),
i16::from(record.verdict.importance),
record.verdict.needs_reply,
record.verdict.needs_follow_up,
scores,
record.model,
record.classified_at,
FOCUS_RULE_VERSION,
record.verdict.pinned,
record.verdict.awaiting_reply,
record.verdict.calendar_hold_until,
i16::from(record.verdict.importance_rate),
record.due_on,
)
.execute(&self.0)
.await?;
Ok(result.rows_affected() > 0)
}
#[tracing::instrument(skip(self), err)]
async fn stale_threads(
&self,
since: DateTime<Utc>,
domains: &[String],
after: Option<StaleThread>,
limit: i64,
) -> Result<Vec<StaleThread>, Report> {
let rows = sqlx::query!(
r#"
SELECT t.id,
GREATEST(t.latest_inbound_message_ts, t.latest_outbound_message_ts)
AS "latest_at!"
FROM email_links el
JOIN email_threads t ON t.link_id = el.id
LEFT JOIN email_thread_focus f ON f.thread_id = t.id
WHERE LOWER(SPLIT_PART(el.email_address, '@', 2)) = ANY($2)
AND t.inbox_visible
AND t.latest_inbound_message_ts IS NOT NULL
AND GREATEST(t.latest_inbound_message_ts, t.latest_outbound_message_ts) >= $1
AND (
t.is_signal
OR t.latest_outbound_message_ts > t.latest_inbound_message_ts
OR f.rule_version < $4
-- A calendar change. The service judges whether it is the
-- latest message and its event isn't over; a new invite or an
-- RSVP never gets this far.
OR EXISTS (
SELECT 1
FROM email_messages notice
WHERE notice.thread_id = t.id
AND NOT notice.is_sent
AND NOT notice.is_draft
AND (
LOWER(notice.subject) ~ $6
OR EXISTS (
SELECT 1 FROM email_message_calendar_invites i
WHERE i.message_id = notice.id
AND (
i.snapshot->>'method' IN ('cancel', 'counter')
OR (
i.snapshot->>'method' = 'request'
AND i.snapshot->'sequence' > '0'::jsonb
)
)
)
)
)
-- A sender the owner has written to lately. The service judges
-- whether it really is a recent contact; mail no event covers,
-- such as an initial sync, only arrives this way.
OR EXISTS (
SELECT 1
FROM email_messages inbound
JOIN email_message_recipients r ON r.contact_id = inbound.from_contact_id
JOIN email_messages sent ON sent.id = r.message_id
WHERE inbound.thread_id = t.id
AND NOT inbound.is_sent
AND NOT inbound.is_draft
AND sent.is_sent
AND NOT sent.is_draft
AND sent.internal_date_ts >= $5
)
)
AND (
f.thread_id IS NULL
OR f.rule_version < $4
OR f.classified_message_ts
< GREATEST(t.latest_inbound_message_ts, t.latest_outbound_message_ts)
)
AND (
$7::timestamptz IS NULL
OR (GREATEST(t.latest_inbound_message_ts, t.latest_outbound_message_ts), t.id)
< ($7, $8::uuid)
)
ORDER BY GREATEST(t.latest_inbound_message_ts, t.latest_outbound_message_ts) DESC, t.id DESC
LIMIT $3
"#,
since,
domains,
limit,
FOCUS_RULE_VERSION,
since - Duration::days(RECENT_CONTACT_DAYS),
change_subject_pattern(),
after.map(|after| after.latest_at),
after.map(|after| after.thread_id),
)
.fetch_all(&self.0)
.await?;
Ok(rows
.into_iter()
.map(|row| StaleThread {
thread_id: row.id,
latest_at: row.latest_at,
})
.collect())
}
#[tracing::instrument(skip(self), fields(threads = tracing::field::Empty), err)]
async fn focus_thread_ids(
&self,
owner: &MacroUserIdStr<'static>,
since: DateTime<Utc>,
waiting_before: DateTime<Utc>,
now: DateTime<Utc>,
limit: i64,
) -> Result<Vec<Uuid>, Report> {
// The waiting and calendar hold tests here restate `focus_for_threads`;
// change both together.
let thread_ids = sqlx::query_scalar!(
r#"
SELECT f.thread_id
FROM email_thread_focus f
JOIN email_threads t ON t.id = f.thread_id AND t.link_id = f.link_id
-- Who wrote last comes from the mail itself: editing a draft moves
-- the thread's own timestamps. An indexed per-thread top-one probe.
CROSS JOIN LATERAL (
SELECT COALESCE(m.internal_date_ts, m.created_at) AS at, m.is_sent
FROM email_messages m
WHERE m.thread_id = t.id
AND NOT m.is_draft
AND EXISTS (
SELECT 1 FROM email_message_mailbox_facts facts
WHERE facts.id = m.id AND facts.is_present AND NOT facts.in_trash
)
ORDER BY COALESCE(m.internal_date_ts, m.created_at) DESC, m.id DESC
LIMIT 1
) latest
WHERE f.link_id IN (
-- The owner's inboxes, as Soup lists them: their own and linked ones.
SELECT el.id FROM email_links el WHERE el.macro_id = $1
UNION
SELECT mul.link_id FROM macro_user_links mul WHERE mul.primary_macro_id = $1
)
-- Every way in, indexed by email_thread_focus_list_idx and
-- email_thread_focus_read_time_idx.
AND (f.is_focus OR f.awaiting_reply OR f.calendar_hold_until IS NOT NULL)
AND t.inbox_visible
-- Never earlier than the latest mail, so this spares most probes.
AND GREATEST(t.latest_inbound_message_ts, t.latest_outbound_message_ts) >= $2
AND latest.at >= $2
AND (
(f.is_focus AND (t.is_signal OR f.pinned))
OR (
-- Nothing reclassifies a thread as it ages, so the wait is
-- measured here, and a newer reply ends it before the worker
-- gets to the thread.
f.awaiting_reply
AND latest.is_sent
AND latest.at <= $3
)
-- A calendar change holds a thread until its event is over, and
-- a newer message ends that before the worker gets to the thread.
OR (f.calendar_hold_until > $4 AND latest.at <= f.classified_message_ts)
)
-- Newest first, so the limit keeps the newest threads.
ORDER BY latest.at DESC, f.thread_id
LIMIT $5
"#,
owner.as_ref(),
since,
waiting_before,
now,
limit,
)
.fetch_all(&self.0)
.await?;
tracing::Span::current().record("threads", thread_ids.len());
Ok(thread_ids)
}
#[tracing::instrument(
skip(self, thread_ids),
fields(
threads = thread_ids.len(),
waiting = tracing::field::Empty,
calendar_held = tracing::field::Empty,
trending = tracing::field::Empty,
),
err
)]
async fn focus_for_threads(
&self,
owner: &MacroUserIdStr<'static>,
thread_ids: &[Uuid],
waiting_before: DateTime<Utc>,
now: DateTime<Utc>,
) -> Result<HashMap<Uuid, ThreadFocus>, Report> {
// The labels are settled here rather than trusted from the row: who
// wrote last can move before the worker reaches the thread, rows from an
// older rule can carry a follow-up on someone else's message, which reads
// as a reply needed, and a held thread's event can end.
let rows = sqlx::query!(
r#"
SELECT f.thread_id, f.category, f.importance, f.classified_at, f.due_on,
now_state.earned OR now_state.waiting OR now_state.calendar_held
AS "is_focus!",
(now_state.earned OR now_state.calendar_held)
AND (f.needs_reply OR f.needs_follow_up)
AND NOT now_state.owner_latest
AS "needs_reply!",
((now_state.earned OR now_state.calendar_held)
AND f.needs_follow_up
AND now_state.owner_latest)
OR now_state.waiting
AS "needs_follow_up!",
now_state.waiting AS "waiting!",
now_state.calendar_held AS "calendar_held!",
f.classified_message_ts,
f.importance_rate,
now_state.untouched AS "untouched!"
FROM email_thread_focus f
JOIN email_threads t ON t.id = f.thread_id AND t.link_id = f.link_id
LEFT JOIN LATERAL (
SELECT COALESCE(m.internal_date_ts, m.created_at) AS at, m.is_sent
FROM email_messages m
WHERE m.thread_id = t.id
AND NOT m.is_draft
AND EXISTS (
SELECT 1 FROM email_message_mailbox_facts facts
WHERE facts.id = m.id AND facts.is_present AND NOT facts.in_trash
)
ORDER BY COALESCE(m.internal_date_ts, m.created_at) DESC, m.id DESC
LIMIT 1
) latest ON TRUE
CROSS JOIN LATERAL (
SELECT COALESCE(latest.is_sent, false) AS owner_latest,
f.is_focus AND (t.is_signal OR f.pinned) AS earned,
f.awaiting_reply
AND t.inbox_visible
AND COALESCE(latest.is_sent AND latest.at <= $3, false) AS waiting,
t.inbox_visible
AND COALESCE(
f.calendar_hold_until > $4
AND latest.at <= f.classified_message_ts,
false
) AS calendar_held,
COALESCE(latest.at <= f.classified_message_ts, false) AS untouched
) now_state
WHERE f.thread_id = ANY($1)
AND f.link_id IN (
SELECT el.id FROM email_links el WHERE el.macro_id = $2
UNION
SELECT mul.link_id FROM macro_user_links mul WHERE mul.primary_macro_id = $2
)
"#,
thread_ids,
owner.as_ref(),
waiting_before,
now,
)
.fetch_all(&self.0)
.await?;
let span = tracing::Span::current();
span.record("waiting", rows.iter().filter(|row| row.waiting).count());
span.record(
"calendar_held",
rows.iter().filter(|row| row.calendar_held).count(),
);
span.record(
"trending",
rows.iter()
.filter(|row| row.untouched && row.importance_rate != 0)
.count(),
);
Ok(rows
.into_iter()
.filter_map(|row| {
// Calendar in Focus only ever means a change still ahead of its event.
let category = if row.calendar_held {
FocusCategory::Calendar
} else {
FocusCategory::from_str(&row.category)
.inspect_err(|_| {
tracing::warn!(thread_id = %row.thread_id, "unknown focus category stored");
})
.ok()?
};
// The rate judged the classified message, so newer mail stops it
// before the worker gets to the thread.
let rate = if row.untouched {
i8::try_from(row.importance_rate).unwrap_or(0)
} else {
0
};
let importance = importance_at(
u8::try_from(row.importance.clamp(0, 100)).unwrap_or(0),
rate,
row.classified_message_ts,
now,
);
Some((
row.thread_id,
ThreadFocus {
is_focus: row.is_focus,
category,
importance,
needs_reply: row.needs_reply,
needs_follow_up: row.needs_follow_up,
classified_at: row.classified_at,
importance_trend: ImportanceTrend::of(rate, importance),
due_on: row.due_on,
},
))
})
.collect())
}
}
#[cfg(test)]
mod test;