Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

20.3 KB
Raw Blame
//! Postgres implementation of durable mailbox stream and reconciliation leases.

use crate::domain::mailbox::progress::ImportCompleted;
use crate::domain::mailbox::*;
use email_api_client::domain::models::{
    MailFolder, MailboxChangePage, ProviderId, StreamPosition, StreamToken,
};
use sqlx::PgPool;
use uuid::Uuid;

mod catalog;
mod commands;
mod contacts;
mod credentials;
mod drafts;
mod gmail_history;
mod ingest;
mod lifecycle;
mod progress;
mod projection;
mod settings;
#[cfg(test)]
mod test;
mod watches;

#[derive(Clone)]
pub struct PgMailboxSync {
    pub(super) db: PgPool,
}

impl PgMailboxSync {
    pub fn new(db: PgPool) -> Self {
        Self { db }
    }

    // Serialize only the brief lease-acquisition transactions. Draft and
    // organization workers must atomically observe each other's active leases;
    // no provider IO runs while this lock is held.
    async fn mutation_claim_transaction(
        &self,
    ) -> Result<sqlx::Transaction<'_, sqlx::Postgres>, MailboxError> {
        let mut tx = self.db.begin().await.map_err(db_error)?;
        sqlx::query!("SELECT 1 AS locked FROM pg_advisory_xact_lock(hashtextextended('email.outlook.mutation-claims',0))")
            .fetch_one(&mut *tx).await.map_err(db_error)?;
        Ok(tx)
    }
}

fn db_error(error: sqlx::Error) -> MailboxError {
    MailboxError::Database(DatabaseFailure::from(&error))
}

/// For `email_db_client` calls, which wrap their `sqlx` errors in `anyhow`.
fn wrapped_db_error(error: anyhow::Error) -> MailboxError {
    error
        .chain()
        .find_map(|cause| cause.downcast_ref::<sqlx::Error>())
        .map_or(MailboxError::Persistence, |error| {
            MailboxError::Database(DatabaseFailure::from(error))
        })
}

/// Lets any adapter keep a database error's SQLSTATE without its message.
impl From<&sqlx::Error> for DatabaseFailure {
    fn from(error: &sqlx::Error) -> Self {
        match error {
            sqlx::Error::Database(error) => error.code().map_or(DatabaseFailure::Other, |code| {
                DatabaseFailure::Sqlstate(code.into_owned())
            }),
            sqlx::Error::PoolTimedOut => DatabaseFailure::PoolTimedOut,
            sqlx::Error::Io(_)
            | sqlx::Error::Tls(_)
            | sqlx::Error::Protocol(_)
            | sqlx::Error::PoolClosed
            | sqlx::Error::WorkerCrashed => DatabaseFailure::Connection,
            sqlx::Error::ColumnDecode { .. }
            | sqlx::Error::Decode(_)
            | sqlx::Error::ColumnNotFound(_)
            | sqlx::Error::TypeNotFound { .. } => DatabaseFailure::Decode,
            _ => DatabaseFailure::Other,
        }
    }
}

impl MailboxSyncRepository for PgMailboxSync {
    async fn renew(&self, lease: SyncLease<'_>) -> Result<(), MailboxError> {
        let updated = match lease {
            SyncLease::Catalog(lease) => self.renew_stream(lease.id,lease.lease_id,lease.fence,lease.mailbox).await?,
            SyncLease::Stream(lease) => self.renew_stream(lease.id,lease.lease_id,lease.fence,lease.mailbox).await?,
            SyncLease::Message(lease) => {
                sqlx::query!(r#"
                    UPDATE email_message_reconciliation q SET lease_until = now() + interval '3 minutes'
                    FROM email_links l WHERE q.link_id = $1 AND q.generation = $2 AND q.provider_id = $3
                        AND q.lease_id = $4 AND q.lease_until > now() AND l.id = q.link_id
                        AND l.sync_generation = q.generation AND l.grant_generation = $5 AND l.is_sync_active
                "#,lease.mailbox.link_id,lease.mailbox.sync_generation,lease.provider_id.as_str(),lease.lease_id,lease.mailbox.grant_generation)
                    .execute(&self.db).await.map_err(db_error)?.rows_affected()
            }
        };
        if updated != 1 {
            return Err(MailboxError::Stale);
        }
        Ok(())
    }
    async fn claim_catalog(&self, lease_id: Uuid) -> Result<Option<CatalogLease>, MailboxError> {
        self.claim_catalog_lease(lease_id).await
    }
    async fn commit_catalog(
        &self,
        lease: &CatalogLease,
        folders: &[MailFolder],
    ) -> Result<Option<ImportCompleted>, MailboxError> {
        self.commit_catalog_lease(lease, folders).await
    }
    async fn release_catalog(&self, lease: &CatalogLease, delay: u32) -> Result<(), MailboxError> {
        self.release_catalog_lease(lease, delay).await
    }
    async fn claim_stream(&self, lease_id: Uuid) -> Result<Option<StreamLease>, MailboxError> {
        let row = sqlx::query!(r#"
            WITH candidate AS (
                SELECT s.id FROM email_sync_streams s JOIN email_links l ON l.id = s.link_id
                WHERE l.is_sync_active AND l.provider = 'OUTLOOK' AND s.generation = l.sync_generation
                    AND s.kind = 'mail_folder' AND s.next_run_at <= now()
                    AND (s.lease_until IS NULL OR s.lease_until < now())
                ORDER BY s.next_run_at, s.id LIMIT 1 FOR UPDATE OF s SKIP LOCKED
            )
            UPDATE email_sync_streams s SET lease_id = $1, lease_until = now() + interval '3 minutes', lease_started_at = clock_timestamp(), fence = fence + 1
            FROM candidate c, email_links l WHERE s.id = c.id AND l.id = s.link_id
            RETURNING s.id, s.link_id, s.generation, l.grant_generation, s.scope_id,
                s.position, s.initial_complete, s.fence
        "#, lease_id).fetch_optional(&self.db).await.map_err(db_error)?;
        row.map(|row| {
            Ok(StreamLease {
                id: row.id,
                mailbox: MailboxKey {
                    link_id: row.link_id,
                    sync_generation: row.generation,
                    grant_generation: row.grant_generation,
                },
                folder: ProviderId::new(row.scope_id)?,
                position: row.position.map(StreamToken::new),
                initial_complete: row.initial_complete,
                lease_id,
                fence: row.fence,
            })
        })
        .transpose()
    }

    async fn commit_page(
        &self,
        lease: &StreamLease,
        page: &MailboxChangePage,
    ) -> Result<CommittedPage, MailboxError> {
        let (position, complete) = match &page.position {
            StreamPosition::Continue(position) => (position, false),
            StreamPosition::Checkpoint(position) => (position, true),
        };
        let mut ids: Vec<String> = page
            .changed
            .iter()
            .chain(&page.removed)
            .map(|id| id.as_str().to_owned())
            .collect();
        ids.sort_unstable();
        ids.dedup();
        let mut tx = self.db.begin().await.map_err(db_error)?;
        Self::lock_attachment_completion(&mut tx, lease.mailbox).await?;
        let advanced = sqlx::query!(
            r#"
            UPDATE email_sync_streams s SET position = $4,
                initial_complete = s.initial_complete OR $5,
                last_completed_at = CASE WHEN $5 THEN now() ELSE last_completed_at END,
                next_run_at = CASE WHEN $5 AND (s.notified_at IS NULL OR s.notified_at <= s.lease_started_at)
                    THEN now() + interval '2 minutes' ELSE now() END,
                lease_id = NULL, lease_until = NULL
            FROM email_links l WHERE s.id = $1 AND s.lease_id = $2 AND s.fence = $3
                AND s.lease_until > now() AND s.link_id = l.id AND s.generation = l.sync_generation
                AND l.is_sync_active AND l.grant_generation = $6
        "#,
            lease.id,
            lease.lease_id,
            lease.fence,
            position.expose(),
            complete,
            lease.mailbox.grant_generation
        )
        .execute(&mut *tx)
        .await
        .map_err(db_error)?;
        if advanced.rows_affected() != 1 {
            return Err(MailboxError::Stale);
        }
        let queued = sqlx::query!(
            r#"
            INSERT INTO email_message_reconciliation (link_id,generation,provider_id,is_import)
            SELECT $1,$2,provider_id,$4 FROM unnest($3::text[]) provider_id
            ON CONFLICT (link_id,generation,provider_id) DO UPDATE SET
                revision = email_message_reconciliation.revision + 1,
                is_import = email_message_reconciliation.is_import AND EXCLUDED.is_import,
                available_at = now()
        "#,
            lease.mailbox.link_id,
            lease.mailbox.sync_generation,
            &ids,
            !lease.initial_complete
        )
        .execute(&mut *tx)
        .await
        .map_err(db_error)?
        .rows_affected();
        let import_completed = Self::settle_import(&mut tx, lease.mailbox).await?;
        tx.commit().await.map_err(db_error)?;
        Ok(CommittedPage {
            queued,
            import_completed,
        })
    }

    async fn reset_stream(&self, lease: &StreamLease) -> Result<(), MailboxError> {
        let mut tx = self.db.begin().await.map_err(db_error)?;
        Self::lock_attachment_completion(&mut tx, lease.mailbox).await?;
        let result = sqlx::query!(r#"
            UPDATE email_sync_streams s SET position = NULL,initial_complete = false,attachments_rechecked = false,next_run_at = now(),lease_id = NULL,lease_until = NULL
            FROM email_links l WHERE s.id = $1 AND s.lease_id = $2 AND s.fence = $3
                AND s.lease_until > now() AND l.id = s.link_id AND l.sync_generation = s.generation
                AND l.is_sync_active AND l.grant_generation = $4
        "#,lease.id,lease.lease_id,lease.fence,lease.mailbox.grant_generation).execute(&mut *tx).await.map_err(db_error)?;
        if result.rows_affected() != 1 {
            return Err(MailboxError::Stale);
        }
        // A fresh delta snapshot omits messages deleted while the cursor was
        // expired. Recheck previously known members mailbox-wide so those
        // deletions settle too; a move is never inferred from folder absence.
        sqlx::query!(r#"
            INSERT INTO email_message_reconciliation(link_id,generation,provider_id,is_import)
            SELECT link_id,$2,provider_id,true FROM email_messages
            WHERE link_id = $1 AND provider_folder_id = $3 AND provider_id IS NOT NULL
            ON CONFLICT(link_id,generation,provider_id) DO UPDATE SET revision = email_message_reconciliation.revision + 1,available_at = now()
        "#,lease.mailbox.link_id,lease.mailbox.sync_generation,lease.folder.as_str()).execute(&mut *tx).await.map_err(db_error)?;
        tx.commit().await.map_err(db_error)
    }

    async fn release_stream(&self, lease: &StreamLease, delay: u32) -> Result<(), MailboxError> {
        sqlx::query!(r#"
            UPDATE email_sync_streams SET lease_id = NULL, lease_until = NULL, next_run_at = now() + make_interval(secs => $4)
            WHERE id = $1 AND lease_id = $2 AND fence = $3
        "#,lease.id,lease.lease_id,lease.fence,f64::from(delay)).execute(&self.db).await.map_err(db_error)?;
        Ok(())
    }

    async fn claim_message(&self, lease_id: Uuid) -> Result<Option<MessageLease>, MailboxError> {
        // Naming both `is_import` values lets `available_at <= now()` end the claim index scan.
        // Without it, Postgres before 18 reads every backed-off row on each poll.
        let row = sqlx::query!(r#"
            WITH candidate AS (
                SELECT q.link_id,q.generation,q.provider_id FROM email_message_reconciliation q
                JOIN email_links l ON l.id = q.link_id
                WHERE q.generation = l.sync_generation AND l.is_sync_active AND l.provider = 'OUTLOOK'
                    AND q.is_import IN (false,true)
                    AND q.available_at <= now() AND (q.lease_until IS NULL OR q.lease_until < now())
                ORDER BY q.is_import,q.available_at,q.link_id,q.provider_id LIMIT 1 FOR UPDATE OF q SKIP LOCKED
            )
            UPDATE email_message_reconciliation q SET lease_id = $1, lease_until = now() + interval '3 minutes', attempts = attempts + 1
            FROM candidate c,email_links l
            WHERE q.link_id = c.link_id AND q.generation = c.generation AND q.provider_id = c.provider_id AND l.id = q.link_id
            RETURNING q.link_id,q.generation,l.grant_generation,q.provider_id,q.revision,q.is_import,q.attempts
        "#,lease_id).fetch_optional(&self.db).await.map_err(db_error)?;
        row.map(|row| {
            Ok(MessageLease {
                mailbox: MailboxKey {
                    link_id: row.link_id,
                    sync_generation: row.generation,
                    grant_generation: row.grant_generation,
                },
                provider_id: ProviderId::new(row.provider_id)?,
                revision: row.revision,
                lease_id,
                is_import: row.is_import,
                attempts: row.attempts,
            })
        })
        .transpose()
    }

    async fn folders(&self, mailbox: MailboxKey) -> Result<Vec<MailFolder>, MailboxError> {
        let rows = sqlx::query!(r#"
            SELECT f.provider_id,f.parent_id,f.display_name,f.role FROM email_mailbox_folders f
            JOIN email_links l ON l.id = f.link_id
            WHERE f.link_id = $1 AND l.sync_generation = $2 AND l.is_sync_active AND f.deleted_at IS NULL
        "#,mailbox.link_id,mailbox.sync_generation).fetch_all(&self.db).await.map_err(db_error)?;
        rows.into_iter()
            .map(|row| {
                Ok(MailFolder {
                    id: ProviderId::new(row.provider_id)?,
                    parent_id: row.parent_id.map(ProviderId::new).transpose()?,
                    name: row.display_name,
                    role: serde_json::from_value(serde_json::Value::String(row.role))
                        .map_err(|_| MailboxError::Persistence)?,
                    has_children: false,
                })
            })
            .collect()
    }

    async fn complete_message(
        &self,
        lease: &MessageLease,
    ) -> Result<Option<ImportCompleted>, MailboxError> {
        let mut tx = self.db.begin().await.map_err(db_error)?;
        Self::lock_attachment_completion(&mut tx, lease.mailbox).await?;
        sqlx::query!(r#"
            DELETE FROM email_message_reconciliation WHERE link_id = $1 AND generation = $2 AND provider_id = $3
                AND revision = $4 AND lease_id = $5 AND lease_until > now()
        "#,lease.mailbox.link_id,lease.mailbox.sync_generation,lease.provider_id.as_str(),lease.revision,lease.lease_id)
            .execute(&mut *tx).await.map_err(db_error)?;
        // A new page may have coalesced another wakeup into the leased row.
        sqlx::query!(r#"
            UPDATE email_message_reconciliation SET lease_id = NULL,lease_until = NULL,available_at = now()
            WHERE link_id = $1 AND generation = $2 AND provider_id = $3 AND lease_id = $4
        "#,lease.mailbox.link_id,lease.mailbox.sync_generation,lease.provider_id.as_str(),lease.lease_id)
            .execute(&mut *tx).await.map_err(db_error)?;
        let import_completed = Self::settle_import(&mut tx, lease.mailbox).await?;
        tx.commit().await.map_err(db_error)?;
        Ok(import_completed)
    }

    async fn release_message(&self, lease: &MessageLease, delay: u32) -> Result<(), MailboxError> {
        sqlx::query!(r#"
            UPDATE email_message_reconciliation SET lease_id = NULL,lease_until = NULL,available_at = now() + make_interval(secs => $5)
            WHERE link_id = $1 AND generation = $2 AND provider_id = $3 AND lease_id = $4
        "#,lease.mailbox.link_id,lease.mailbox.sync_generation,lease.provider_id.as_str(),lease.lease_id,f64::from(delay))
            .execute(&self.db).await.map_err(db_error)?;
        Ok(())
    }
}

impl PgMailboxSync {
    async fn renew_stream(
        &self,
        id: Uuid,
        lease_id: Uuid,
        fence: i64,
        mailbox: MailboxKey,
    ) -> Result<u64, MailboxError> {
        Ok(sqlx::query!(
            r#"
            UPDATE email_sync_streams s SET lease_until = now() + interval '3 minutes'
            FROM email_links l WHERE s.id = $1 AND s.lease_id = $2 AND s.fence = $3
                AND s.lease_until > now() AND l.id = s.link_id AND l.sync_generation = s.generation
                AND l.grant_generation = $4 AND l.is_sync_active
        "#,
            id,
            lease_id,
            fence,
            mailbox.grant_generation
        )
        .execute(&self.db)
        .await
        .map_err(db_error)?
        .rows_affected())
    }
}

impl PgMailboxSync {
    async fn lock_attachment_completion(
        tx: &mut sqlx::PgConnection,
        mailbox: MailboxKey,
    ) -> Result<(), MailboxError> {
        let active=sqlx::query!("SELECT id FROM email_links WHERE id=$1 AND sync_generation=$2 AND grant_generation=$3 AND is_sync_active FOR UPDATE",mailbox.link_id,mailbox.sync_generation,mailbox.grant_generation)
            .fetch_optional(&mut *tx).await.map_err(db_error)?;
        if active.is_none() {
            return Err(MailboxError::Stale);
        }
        Ok(())
    }

    // Called under the link lock after each page or reconciliation, so one
    // caller at a time sees the import finish.
    async fn settle_import(
        tx: &mut sqlx::PgConnection,
        mailbox: MailboxKey,
    ) -> Result<Option<ImportCompleted>, MailboxError> {
        let state = sqlx::query!(r#"SELECT
            EXISTS(SELECT 1 FROM email_sync_streams WHERE link_id=$1 AND generation=$2 AND kind='folder_catalog' AND initial_complete) AS "catalog_complete!",
            EXISTS(SELECT 1 FROM email_sync_streams WHERE link_id=$1 AND generation=$2 AND kind='folder_catalog' AND import_completed_at IS NULL) AS "import_open!",
            EXISTS(SELECT 1 FROM email_sync_streams WHERE link_id=$1 AND generation=$2 AND kind='mail_folder' AND NOT attachments_rechecked) AS "recheck_pending!",
            EXISTS(SELECT 1 FROM email_sync_streams WHERE link_id=$1 AND generation=$2 AND kind IN ('mail_folder','folder_catalog') AND NOT initial_complete) AS "streams_incomplete!",
            EXISTS(SELECT 1 FROM email_message_reconciliation WHERE link_id=$1 AND generation=$2) AS "queued!",
            EXISTS(SELECT 1 FROM email_message_reconciliation WHERE link_id=$1 AND generation=$2 AND is_import) AS "imports_queued!"
        "#,mailbox.link_id,mailbox.sync_generation).fetch_one(&mut *tx).await.map_err(db_error)?;
        let enumerated = state.catalog_complete && !state.streams_incomplete;
        // All correspondence is now ingested, regardless of folder/page delivery order.
        if enumerated && state.recheck_pending && !state.queued {
            sqlx::query!(r#"
                INSERT INTO email_projection_outbox(id,link_id,generation,payload)
                SELECT gen_random_uuid(),m.link_id,$2,jsonb_build_object('kind','attachment_recheck','message_id',m.id)
                FROM email_messages m WHERE m.link_id=$1 AND m.provider_id IS NOT NULL AND NOT m.is_draft
                    AND EXISTS(SELECT 1 FROM email_attachments a WHERE a.message_id=m.id AND a.reference_url IS NULL)
            "#,mailbox.link_id,mailbox.sync_generation).execute(&mut *tx).await.map_err(db_error)?;
            sqlx::query!("UPDATE email_sync_streams SET attachments_rechecked=true WHERE link_id=$1 AND generation=$2 AND kind='mail_folder'",mailbox.link_id,mailbox.sync_generation)
                .execute(&mut *tx).await.map_err(db_error)?;
        }
        // The import run is done once every folder is enumerated and no import
        // row is left; live changes may still be queued.
        if !(enumerated && state.import_open && !state.imports_queued) {
            return Ok(None);
        }
        let Some(run) = sqlx::query!(r#"
            UPDATE email_sync_streams SET import_completed_at = now()
            WHERE link_id=$1 AND generation=$2 AND kind='folder_catalog' AND import_completed_at IS NULL
            RETURNING import_started_at, import_completed_at AS "completed_at!"
        "#,mailbox.link_id,mailbox.sync_generation).fetch_optional(&mut *tx).await.map_err(db_error)? else {
            return Ok(None);
        };
        let size = sqlx::query!(r#"SELECT
            (SELECT count(*) FROM email_messages WHERE link_id=$1 AND provider_id IS NOT NULL) AS "messages!",
            (SELECT count(*) FROM email_sync_streams WHERE link_id=$1 AND generation=$2 AND kind='mail_folder') AS "folders!"
        "#,mailbox.link_id,mailbox.sync_generation).fetch_one(&mut *tx).await.map_err(db_error)?;
        Ok(Some(ImportCompleted {
            mailbox,
            started_at: run.import_started_at,
            completed_at: run.completed_at,
            messages: size.messages,
            folders: size.folders,
        }))
    }
}