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 models_email::email::{db, service};
use sqlx::types::Uuid;
use sqlx::{Executor, PgPool, Postgres};
#[tracing::instrument(skip(pool), err)]
pub async fn insert_message_label(
pool: &PgPool,
message_id: Uuid,
provider_label_id: &str,
link_id: Uuid,
) -> anyhow::Result<()> {
if provider_label_id.is_empty() {
anyhow::bail!("Provider label ID cannot be empty");
}
let result = sqlx::query!(
r#"
WITH label_lookup AS (
SELECT id FROM email_labels
WHERE link_id = $2 AND provider_label_id = $3
)
INSERT INTO email_message_labels (message_id, label_id)
SELECT $1, id FROM label_lookup
ON CONFLICT (message_id, label_id) DO NOTHING
RETURNING (SELECT COUNT(*) FROM label_lookup) AS label_found
"#,
message_id,
link_id,
provider_label_id
)
.fetch_optional(pool)
.await?;
// Check if the label was found
match result {
Some(record) if record.label_found.unwrap_or(0) > 0 => Ok(()),
// the label we were trying to insert a relation for doesn't exist. this shouldn't happen
_ => {
tracing::warn!(
message_id = %message_id,
provider_label_id = %provider_label_id,
link_id = %link_id,
"No message label found to create relation for"
);
anyhow::bail!(
"No label found for link_id {} with provider_label_id {}",
link_id,
provider_label_id
)
}
}
}
/// Inserts a label for multiple messages at once
#[tracing::instrument(skip(executor), err)]
pub async fn insert_message_labels_batch<'e, E>(
executor: E,
message_ids: &Vec<Uuid>,
provider_label_id: &str,
link_id: Uuid,
) -> anyhow::Result<usize>
where
E: Executor<'e, Database = Postgres>,
{
if message_ids.is_empty() {
return Ok(0);
}
if provider_label_id.is_empty() {
anyhow::bail!("Provider label ID cannot be empty");
}
let result = sqlx::query!(
r#"
INSERT INTO email_message_labels (message_id, label_id)
SELECT
unnested_message_id,
l.id
FROM
UNNEST($1::uuid[]) AS t(unnested_message_id)
CROSS JOIN
email_labels l
WHERE
l.link_id = $2 AND l.provider_label_id = $3
ON CONFLICT (message_id, label_id) DO NOTHING
"#,
message_ids,
link_id,
provider_label_id
)
.execute(executor)
.await?;
let rows_affected = result.rows_affected() as usize;
Ok(rows_affected)
}
/// Inserts or updates labels for a user.
/// Labels are shared across messages, so this should be done separately from inserting message_labels
/// to avoid deadlock issues.
#[tracing::instrument(skip(pool, service_labels), fields(label_count = service_labels.len()), err)]
pub async fn insert_or_update_labels(
pool: &PgPool,
mut service_labels: Vec<service::label::Label>,
) -> anyhow::Result<()> {
if service_labels.is_empty() {
return Ok(());
}
// need to generate before conversion
for service_label in service_labels.iter_mut() {
if service_label.id.is_none() {
service_label.id = Some(macro_uuid::generate_uuid_v7());
}
}
let db_labels: Vec<db::label::Label> = service_labels.into_iter().map(Into::into).collect();
// Extract data for batch insert
let label_db_ids: Vec<Uuid> = db_labels.iter().map(|label| label.id).collect();
let link_ids: Vec<Uuid> = db_labels.iter().map(|label| label.link_id).collect();
let provider_label_ids: Vec<String> = db_labels
.iter()
.map(|label| label.provider_label_id.clone())
.collect();
let names: Vec<String> = db_labels.iter().map(|label| label.name.clone()).collect();
// Extract enum values
let message_list_visibilities: Vec<db::label::MessageListVisibility> = db_labels
.iter()
.map(|label| label.message_list_visibility)
.collect();
let label_list_visibilities: Vec<db::label::LabelListVisibility> = db_labels
.iter()
.map(|label| label.label_list_visibility)
.collect();
let types: Vec<db::label::LabelType> = db_labels.iter().map(|label| label.type_).collect();
// Insert/update labels with all fields
sqlx::query!(
r#"
WITH input_rows (
id,
link_id,
provider_label_id,
name,
message_list_visibility,
label_list_visibility,
type
) AS (
SELECT * FROM unnest(
$1::uuid[],
$2::uuid[],
$3::text[],
$4::text[],
$5::email_message_list_visibility_enum[],
$6::email_label_list_visibility_enum[],
$7::email_label_type_enum[]
)
)
INSERT INTO email_labels (
id,
link_id,
provider_label_id,
name,
message_list_visibility,
label_list_visibility,
type
)
SELECT
id,
link_id,
provider_label_id,
name,
message_list_visibility,
label_list_visibility,
type
FROM input_rows
ON CONFLICT (link_id, provider_label_id) DO UPDATE
SET
name = EXCLUDED.name,
message_list_visibility = EXCLUDED.message_list_visibility,
label_list_visibility = EXCLUDED.label_list_visibility,
type = EXCLUDED.type
"#,
&label_db_ids,
&link_ids,
&provider_label_ids,
&names,
&message_list_visibilities as _,
&label_list_visibilities as _,
&types as _
)
.execute(pool)
.await?;
Ok(())
}
#[tracing::instrument(skip(pool, service_label), err)]
pub async fn insert_label(
pool: &PgPool,
mut service_label: service::label::Label,
) -> anyhow::Result<service::label::Label> {
if service_label.id.is_none() {
service_label.id = Some(macro_uuid::generate_uuid_v7());
}
// Convert to DB model
let db_label: db::label::Label = service_label.clone().into();
// Insert the label into the database
let inserted_label = sqlx::query_as!(
db::label::Label,
r#"
INSERT INTO email_labels (
id,
link_id,
provider_label_id,
name,
message_list_visibility,
label_list_visibility,
type
)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (link_id, provider_label_id) DO UPDATE
SET
name = EXCLUDED.name,
message_list_visibility = EXCLUDED.message_list_visibility,
label_list_visibility = EXCLUDED.label_list_visibility,
type = EXCLUDED.type
RETURNING
id,
link_id,
provider_label_id,
name,
created_at,
message_list_visibility as "message_list_visibility: _",
label_list_visibility as "label_list_visibility: _",
type as "type_: _"
"#,
db_label.id,
db_label.link_id,
db_label.provider_label_id,
db_label.name,
db_label.message_list_visibility as _,
db_label.label_list_visibility as _,
db_label.type_ as _
)
.fetch_one(pool)
.await?;
let populated_service_label: service::label::Label = inserted_label.into();
Ok(populated_service_label)
}
/// inserts the labels of an email into the database in a batch. Labels in
/// `pending_provider_label_ids` keep their current state on the message.
pub async fn insert_message_labels(
tx: &mut sqlx::PgConnection,
link_id: Uuid,
message_id: Uuid,
provider_label_ids: &[String],
delete_old: bool,
pending_provider_label_ids: &[String],
) -> anyhow::Result<()> {
if provider_label_ids.is_empty() && !delete_old {
return Ok(());
}
// get back label ids to use in junction table insert
#[derive(Debug)]
struct LabelMapping {
id: Uuid,
provider_label_id: String,
}
let label_mappings: Vec<LabelMapping> = sqlx::query_as!(
LabelMapping,
r#"
SELECT id, provider_label_id
FROM email_labels
WHERE link_id = $1 AND provider_label_id = ANY($2)
"#,
link_id,
&provider_label_ids
)
.fetch_all(&mut *tx)
.await?;
// labels can be missing if they were created after the last label sync; skip them
if label_mappings.len() != provider_label_ids.len() {
let found: std::collections::HashSet<&str> = label_mappings
.iter()
.map(|m| m.provider_label_id.as_str())
.collect();
let missing: Vec<&String> = provider_label_ids
.iter()
.filter(|id| !found.contains(id.as_str()))
.collect();
tracing::warn!(
link_id = %link_id,
message_id = %message_id,
missing_provider_label_ids = ?missing,
"Some message labels not found in database, skipping them"
);
}
let label_db_ids: Vec<Uuid> = label_mappings
.iter()
.filter(|m| !pending_provider_label_ids.contains(&m.provider_label_id))
.map(|m| m.id)
.collect();
// with delete_old, records that don't match the ones to insert are removed in case we are
// doing an upsert and some of the old ones got removed. An unchanged set deletes and inserts
// nothing.
sqlx::query!(
r#"
WITH removed AS (
DELETE FROM email_message_labels
WHERE $3::bool
AND message_id = $1::uuid
AND label_id NOT IN (SELECT UNNEST($2::uuid[]))
AND label_id NOT IN (
SELECT id FROM email_labels
WHERE link_id = $4 AND provider_label_id = ANY($5::text[])
)
)
INSERT INTO email_message_labels (message_id, label_id)
SELECT $1, label_id FROM UNNEST($2::uuid[]) AS t(label_id)
ON CONFLICT (message_id, label_id) DO NOTHING
"#,
message_id,
&label_db_ids,
delete_old,
link_id,
pending_provider_label_ids
)
.execute(&mut *tx)
.await?;
Ok(())
}