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.

18.8 KB
Raw Blame
//! Postgres adapters for task duplicate detection.
//!
//! [`PgTaskVectorDb`] implements the embedding crate's [`VectorDb`] over the
//! `task_duplicate_embedding` table (one row per `(document_id, search_key)`
//! field). [`PgTaskMatchRepo`] owns the duplicate match graph
//! (`task_duplicate_match`).

#[cfg(test)]
mod test;

use std::collections::HashMap;

use anyhow::Context;
use async_trait::async_trait;
use embedding::embedding_provider::openai::DIMS;
use embedding::{Content, KeyedEmbedding, LabeledEmbedding, Match, SearchResults, VectorStore};
use sqlx::PgPool;
use uuid::Uuid;

use system_properties::{StatusOption, SystemPropertyKey};

use crate::domain::models::{TaskDedupError, TaskDuplicate, TaskSearchParameters};
use crate::domain::ports::TaskMatchRepo;
use crate::domain::service::ordered_pair;

/// Status option ids for tasks excluded from every dedup surface: a finished or
/// cancelled task is not an actionable duplicate, so it is neither retrieved as
/// a similarity candidate nor shown in the duplicates list.
fn closed_status_option_ids() -> Vec<String> {
    vec![
        StatusOption::COMPLETED_UUID.to_string(),
        StatusOption::CANCELED_UUID.to_string(),
    ]
}

/// Postgres/pgvector implementation of [`VectorDb`] for task embeddings.
///
/// Each task contributes one row per embeddable field (`title`, `body`); the
/// composite primary key `(document_id, search_key)` keeps them distinct.
///
/// Duplicate detection uses a single embedding model everywhere, so no model
/// identifier is stored per row.
#[derive(Clone)]
pub struct PgTaskVectorDb {
    pool: PgPool,
}

impl PgTaskVectorDb {
    /// Creates a store over `pool`.
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }
}

impl VectorStore<DIMS> for PgTaskVectorDb {
    type Error = anyhow::Error;
    type Metadata = String;
    type SearchParameters = TaskSearchParameters;

    async fn upsert_embeddings<'a>(
        &self,
        metadata: String,
        embeddings: Vec<LabeledEmbedding<'a, DIMS>>,
    ) -> anyhow::Result<()> {
        if embeddings.is_empty() {
            return Ok(());
        }

        let search_keys: Vec<String> = embeddings
            .iter()
            .map(|field| field.search_key.to_string())
            .collect();
        let contents: Vec<String> = embeddings
            .iter()
            .map(|field| field.content.as_ref().to_string())
            .collect();
        let vectors: Vec<String> = embeddings
            .iter()
            .map(|field| vector_sql_literal(&field.embedding))
            .collect();

        sqlx::query!(
            r#"
            INSERT INTO task_duplicate_embedding (document_id, search_key, content, embedding)
            SELECT $1, sk, ct, emb::vector
            FROM unnest($2::text[], $3::text[], $4::text[]) AS t(sk, ct, emb)
            ON CONFLICT (document_id, search_key) DO UPDATE
            SET content = EXCLUDED.content,
                embedding = EXCLUDED.embedding,
                updated_at = NOW()
            "#,
            metadata,
            &search_keys,
            &contents,
            &vectors,
        )
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    async fn cosine_search(
        &self,
        query: Vec<KeyedEmbedding<DIMS>>,
        params: TaskSearchParameters,
    ) -> anyhow::Result<Vec<SearchResults<String, DIMS>>> {
        if query.is_empty() {
            return Ok(Vec::new());
        }

        let query_keys: Vec<String> = query.iter().map(|q| q.search_key.to_string()).collect();
        let query_vectors: Vec<String> = query
            .iter()
            .map(|q| vector_sql_literal(&q.embedding))
            .collect();

        // Each candidate field is scored by its best similarity to ANY query
        // field, giving the full query × stored cross-product (title↔title,
        // title↔body, body↔title, body↔body). Entities are ranked by their best
        // field score and capped at `limit`; all of a kept entity's field rows
        // are returned so the service can reconstruct its text.
        //
        // Iterative index scan keeps recall high despite the owner/team, self-,
        // dismissed-pair, and closed-status filters dropping rows after the
        // HNSW scan.
        // SET LOCAL binds to the transaction's connection, so the search must run
        // in the same transaction to see it.
        let mut tx = self.pool.begin().await?;
        sqlx::query!("SET LOCAL hnsw.iterative_scan = relaxed_order")
            .execute(&mut *tx)
            .await?;
        let rows = sqlx::query!(
            r#"
            WITH query AS (
                SELECT key, vec::vector AS vec
                FROM unnest($1::text[], $2::text[]) AS t(key, vec)
            ),
            scored AS (
                SELECT
                    e.document_id,
                    e.search_key,
                    e.content,
                    e.embedding::text AS embedding_text,
                    MAX(1 - (e.embedding <=> q.vec))::real AS score
                FROM task_duplicate_embedding e
                JOIN "Document" d ON d.id = e.document_id
                JOIN document_sub_type dst ON dst.document_id = d.id AND dst.sub_type = 'task'
                LEFT JOIN team_task tt ON tt.document_id = d.id
                CROSS JOIN query q
                WHERE d."deletedAt" IS NULL
                  AND (
                    d.owner = $3
                    OR ($4::uuid IS NOT NULL AND tt.team_id = $4)
                  )
                  AND ($5::text IS NULL OR e.document_id <> $5)
                  AND (
                    NOT $6
                    OR NOT EXISTS (
                        SELECT 1
                        FROM task_duplicate_match m
                        WHERE m.task_id = LEAST($5, e.document_id)
                          AND m.duplicate_task_id = GREATEST($5, e.document_id)
                          AND m.status = 'dismissed'
                    )
                  )
                  AND NOT EXISTS (
                    SELECT 1
                    FROM entity_properties ep
                    WHERE ep.entity_id = e.document_id
                      AND ep.entity_type = 'TASK'
                      AND ep.property_definition_id = $8
                      AND ep.values->'value' ?| $9::text[]
                  )
                GROUP BY e.document_id, e.search_key, e.content, e.embedding
            ),
            ranked AS (
                SELECT document_id, MAX(score) AS best
                FROM scored
                GROUP BY document_id
                ORDER BY best DESC
                LIMIT $7
            )
            SELECT
                s.document_id AS "document_id!",
                s.search_key AS "search_key!",
                s.content AS "content!",
                s.embedding_text AS "embedding_text!",
                s.score AS "score!"
            FROM scored s
            JOIN ranked r ON r.document_id = s.document_id
            ORDER BY r.best DESC, s.document_id, s.score DESC
            "#,
            &query_keys,
            &query_vectors,
            params.owner,
            params.team_id,
            params.exclude_document_id,
            params.exclude_dismissed,
            params.limit,
            SystemPropertyKey::STATUS_UUID,
            &closed_status_option_ids(),
        )
        .fetch_all(&mut *tx)
        .await?;
        tx.commit().await?;

        // Group the flat (document_id, field) rows into one SearchResults per
        // entity, preserving the best-first order established by the query.
        let mut results: Vec<SearchResults<String, DIMS>> = Vec::new();
        for row in rows {
            let Some(search_key) = search_key_static(&row.search_key) else {
                tracing::warn!(
                    search_key = %row.search_key,
                    document_id = %row.document_id,
                    "skipping task embedding row with unknown search_key"
                );
                continue;
            };
            let embedding = parse_vector(&row.embedding_text)
                .with_context(|| format!("invalid stored embedding for {}", row.document_id))?;
            let matched = Match {
                score: row.score,
                embedding: LabeledEmbedding {
                    search_key,
                    content: Content::Owned(row.content),
                    embedding,
                },
            };
            match results.last_mut() {
                Some(last) if last.metadata == row.document_id => last.matches.push(matched),
                _ => results.push(SearchResults {
                    metadata: row.document_id,
                    matches: vec![matched],
                }),
            }
        }
        Ok(results)
    }
}

/// Postgres-backed duplicate match graph.
#[derive(Clone)]
pub struct PgTaskMatchRepo {
    pool: PgPool,
}

impl PgTaskMatchRepo {
    /// Creates a match repo over `pool`.
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }
}

#[async_trait]
impl TaskMatchRepo for PgTaskMatchRepo {
    async fn upsert_match(
        &self,
        task_id: &str,
        duplicate_task_id: &str,
        vector_score: f64,
        judge_model: Option<&str>,
        judge_reason: Option<&str>,
    ) -> Result<(), TaskDedupError> {
        let (task_id, duplicate_task_id) = ordered_pair(task_id, duplicate_task_id);
        sqlx::query!(
            r#"
            INSERT INTO task_duplicate_match (
                id,
                task_id,
                duplicate_task_id,
                status,
                vector_score,
                judge_model,
                judge_reason
            )
            VALUES ($1, $2, $3, 'active', $4, $5, $6)
            ON CONFLICT (task_id, duplicate_task_id) DO UPDATE
            SET status = 'active',
                vector_score = EXCLUDED.vector_score,
                judge_model = EXCLUDED.judge_model,
                judge_reason = EXCLUDED.judge_reason,
                updated_at = NOW()
            WHERE task_duplicate_match.status <> 'dismissed'
            "#,
            Uuid::new_v4(),
            task_id,
            duplicate_task_id,
            vector_score,
            judge_model,
            judge_reason,
        )
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    async fn active_duplicate_component(
        &self,
        document_ids: &[String],
    ) -> Result<Vec<String>, TaskDedupError> {
        let rows = sqlx::query!(
            r#"
            WITH RECURSIVE edges(left_id, right_id) AS (
                SELECT m.task_id, m.duplicate_task_id
                FROM task_duplicate_match m
                JOIN "Document" left_document ON left_document.id = m.task_id
                JOIN "Document" right_document ON right_document.id = m.duplicate_task_id
                WHERE m.status = 'active'
                  AND left_document."deletedAt" IS NULL
                  AND right_document."deletedAt" IS NULL
            ),
            component(document_id) AS (
                SELECT DISTINCT unnest($1::text[])
                UNION
                SELECT CASE
                    WHEN edges.left_id = component.document_id THEN edges.right_id
                    ELSE edges.left_id
                END
                FROM edges
                JOIN component
                  ON component.document_id = edges.left_id
                  OR component.document_id = edges.right_id
            )
            SELECT DISTINCT document_id AS "document_id!"
            FROM component
            "#,
            document_ids,
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(rows.into_iter().map(|row| row.document_id).collect())
    }

    async fn trim_matches(&self, document_id: &str, limit: i64) -> Result<(), TaskDedupError> {
        sqlx::query!(
            r#"
            WITH ranked AS (
                SELECT
                    id,
                    ROW_NUMBER() OVER (
                        ORDER BY vector_score DESC, created_at DESC
                    ) AS rn
                FROM task_duplicate_match
                WHERE status = 'active'
                  AND (task_id = $1 OR duplicate_task_id = $1)
            )
            UPDATE task_duplicate_match m
            SET status = 'dismissed', updated_at = NOW()
            FROM ranked r
            WHERE m.id = r.id
              AND r.rn > $2
            "#,
            document_id,
            limit,
        )
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    async fn active_duplicates(
        &self,
        document_id: &str,
    ) -> Result<Vec<TaskDuplicate>, TaskDedupError> {
        let rows = sqlx::query!(
            r#"
            SELECT
                m.id,
                CASE WHEN m.task_id = $1 THEN m.duplicate_task_id ELSE m.task_id END AS "other_task_id!",
                d.name AS "other_task_name!",
                m.vector_score AS "vector_score!",
                m.judge_reason
            FROM task_duplicate_match m
            JOIN "Document" d
              ON d.id = CASE WHEN m.task_id = $1 THEN m.duplicate_task_id ELSE m.task_id END
            WHERE m.status = 'active'
              AND (m.task_id = $1 OR m.duplicate_task_id = $1)
              AND d."deletedAt" IS NULL
              AND NOT EXISTS (
                SELECT 1
                FROM entity_properties ep
                WHERE ep.entity_id = d.id
                  AND ep.entity_type = 'TASK'
                  AND ep.property_definition_id = $2
                  AND ep.values->'value' ?| $3::text[]
              )
            ORDER BY m.vector_score DESC, m.created_at DESC
            LIMIT 10
            "#,
            document_id,
            SystemPropertyKey::STATUS_UUID,
            &closed_status_option_ids(),
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(rows
            .into_iter()
            .map(|row| TaskDuplicate {
                id: row.id,
                task_id: row.other_task_id,
                task_name: row.other_task_name,
                vector_score: row.vector_score,
                judge_reason: row.judge_reason,
            })
            .collect())
    }

    async fn dismiss_match(
        &self,
        document_id: &str,
        match_id: Uuid,
        dismissed_by: &str,
    ) -> Result<bool, TaskDedupError> {
        let rows = sqlx::query!(
            r#"
            UPDATE task_duplicate_match
            SET status = 'dismissed',
                dismissed_by = $3,
                dismissed_at = NOW(),
                updated_at = NOW()
            WHERE id = $1
              AND (task_id = $2 OR duplicate_task_id = $2)
            "#,
            match_id,
            document_id,
            dismissed_by,
        )
        .execute(&self.pool)
        .await?
        .rows_affected();

        Ok(rows > 0)
    }

    async fn match_contains(
        &self,
        document_id: &str,
        match_id: Uuid,
    ) -> Result<bool, TaskDedupError> {
        let exists = sqlx::query_scalar!(
            r#"
            SELECT EXISTS (
                SELECT 1
                FROM task_duplicate_match
                WHERE id = $1
                  AND (task_id = $2 OR duplicate_task_id = $2)
            )
            "#,
            match_id,
            document_id,
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(exists.unwrap_or(false))
    }

    async fn match_document_ids(&self, match_id: Uuid) -> Result<Vec<String>, TaskDedupError> {
        let row = sqlx::query!(
            r#"
            SELECT task_id, duplicate_task_id
            FROM task_duplicate_match
            WHERE id = $1
            "#,
            match_id,
        )
        .fetch_optional(&self.pool)
        .await?;

        Ok(row
            .map(|row| vec![row.task_id, row.duplicate_task_id])
            .unwrap_or_default())
    }

    async fn dismiss_match_by_id(&self, match_id: Uuid) -> Result<(), TaskDedupError> {
        sqlx::query!(
            r#"
            UPDATE task_duplicate_match
            SET status = 'dismissed', updated_at = NOW()
            WHERE id = $1
            "#,
            match_id,
        )
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    async fn task_names(
        &self,
        document_ids: &[String],
    ) -> Result<HashMap<String, String>, TaskDedupError> {
        if document_ids.is_empty() {
            return Ok(HashMap::new());
        }
        let rows = sqlx::query!(
            r#"
            SELECT id, name
            FROM "Document"
            WHERE id = ANY($1)
              AND "deletedAt" IS NULL
            "#,
            document_ids,
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(rows.into_iter().map(|row| (row.id, row.name)).collect())
    }
}

/// Formats an embedding as the pgvector text literal `[a,b,c]` used for the
/// `text::vector` casts in the queries above.
pub fn vector_sql_literal(embedding: &[f32]) -> String {
    let mut literal = String::with_capacity(embedding.len() * 8 + 2);
    literal.push('[');
    for (index, value) in embedding.iter().enumerate() {
        if index > 0 {
            literal.push(',');
        }
        literal.push_str(&value.to_string());
    }
    literal.push(']');
    literal
}

/// Parses a pgvector text literal (`[a,b,c]`) back into a fixed-size embedding.
fn parse_vector(text: &str) -> anyhow::Result<[f32; DIMS]> {
    let inner = text.trim().trim_start_matches('[').trim_end_matches(']');
    let mut embedding = [0.0_f32; DIMS];
    let mut count = 0usize;
    for part in inner.split(',') {
        let part = part.trim();
        if part.is_empty() {
            continue;
        }
        if count >= DIMS {
            anyhow::bail!("embedding has more than {DIMS} dimensions");
        }
        embedding[count] = part
            .parse()
            .with_context(|| format!("invalid embedding component {part:?}"))?;
        count += 1;
    }
    if count != DIMS {
        anyhow::bail!("expected {DIMS} dimensions, got {count}");
    }
    Ok(embedding)
}

/// Maps a `search_key` string read from the database back to one of the known
/// static keys, so it satisfies the `&'static str` [`SearchKey`](embedding::SearchKey).
///
/// Returns `None` for unrecognized keys (e.g. a future field written by a newer
/// build, or bad data). Callers skip those rows rather than leaking the string
/// into `'static`, which would grow the heap permanently on a mixed-version
/// rollout.
fn search_key_static(key: &str) -> Option<&'static str> {
    match key {
        "title" => Some("title"),
        "body" => Some("body"),
        _ => None,
    }
}

// The domain error stays sqlx-free; this adapter owns the mapping so `?`
// works on sqlx results throughout the crate's outbound code. The raw sqlx
// error travels inside the report.
impl From<sqlx::Error> for TaskDedupError {
    fn from(e: sqlx::Error) -> Self {
        Self::Storage(rootcause::report!(e).into_dynamic())
    }
}