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.

42.3 KB
Raw Blame
//! PostgreSQL implementation of the [`DocumentRepo`] port.
//!
//! All SQL queries are written directly here (not delegated to `macro_db_client`).

#[cfg(test)]
mod tests;

mod copy;
mod create;
mod edit;
mod legacy_upgrade;
mod markdown_backfill;
mod share;

use document_sub_type::DocumentSubType;
use entity_registry::BotFacts;
use entity_registry_db_utils::{NewEntityRecord, OwnedEntityRegistrar, RegisteredEntityType};
use macro_user_id::{cowlike::CowLike, user_id::MacroUserIdStr};
use model::document::{DocumentBasic, DocumentMetadata};
use model_owner::Owner;
use models_permissions::share_permission::{LinkShare, SharePermissionV2, TeamLinkShareDefault};
use sqlx::PgPool;

use model_entity::{Entity, EntityType};
use sqlx::Row;

use crate::domain::content::{DocumentContent, DocumentContentState};
use crate::domain::models::{
    BranchNameContext, CopyDocumentRepoArgs, CreateDocumentRepoArgs, DocumentError,
    DocumentTeamShare, EditDocumentRepoArgs, EmailImportRepoOutcome, ImportEmailAttachmentRepoArgs,
    OwnerTeam, TeamTaskMetadata, TeamTaskNumber,
};
use crate::domain::ports::DocumentRepo;

pub use markdown_backfill::PgMarkdownBackfillRepo;

/// PostgreSQL-backed document repository.
#[derive(Clone)]
pub struct PgDocumentRepo<B> {
    pool: PgPool,
    registrar: OwnedEntityRegistrar<B>,
}

impl<B: BotFacts + 'static> PgDocumentRepo<B> {
    /// Create a new repository backed by the given connection pool.
    ///
    /// Created and copied documents register their owner grants through
    /// `registrar`.
    pub fn new(pool: PgPool, registrar: OwnedEntityRegistrar<B>) -> Self {
        Self { pool, registrar }
    }

    async fn reused_email_document(
        &self,
        document_id: String,
    ) -> Result<EmailImportRepoOutcome, DocumentError> {
        let metadata = self.get_document_metadata(&document_id).await?;
        Ok(EmailImportRepoOutcome::Reused(metadata))
    }

    async fn reused_linked_email_attachment(
        &self,
        email_attachment_id: uuid::Uuid,
    ) -> Result<EmailImportRepoOutcome, DocumentError> {
        let existing_id =
            create::find_document_id_for_email_attachment(&self.pool, email_attachment_id)
                .await?
                .ok_or_else(|| {
                    sqlx::Error::Protocol(
                        "email attachment already linked but document is missing".into(),
                    )
                })?;
        self.reused_email_document(existing_id).await
    }

    async fn find_reusable_email_document_by_sha(
        &self,
        owner: &str,
        sha: &str,
    ) -> Result<Option<String>, sqlx::Error> {
        let mut conn = self.pool.acquire().await?;
        create::find_live_email_document_id_by_sha(&mut conn, owner, sha).await
    }

    async fn link_existing_email_document(
        &self,
        document_id: &str,
        email_attachment_id: uuid::Uuid,
    ) -> Result<EmailImportRepoOutcome, DocumentError> {
        let mut transaction = self.pool.begin().await?;
        match create::link_document_email(&mut transaction, document_id, email_attachment_id).await
        {
            Ok(()) => {
                transaction.commit().await?;
                self.reused_email_document(document_id.to_string()).await
            }
            Err(sqlx::Error::Database(ref db_err)) if db_err.is_unique_violation() => {
                transaction.rollback().await?;
                self.reused_linked_email_attachment(email_attachment_id)
                    .await
            }
            Err(e) => Err(e.into()),
        }
    }
}

async fn update_document_modified(pool: &PgPool, document_id: &str) -> Result<(), sqlx::Error> {
    sqlx::query!(
        r#"
        UPDATE "Document"
        SET "updatedAt" = NOW()
        WHERE id = $1
        "#,
        document_id,
    )
    .execute(pool)
    .await?;

    Ok(())
}

fn registry_protocol_error(
    error: rootcause::Report<entity_registry_db_utils::EntityRegistryError>,
) -> sqlx::Error {
    sqlx::Error::Protocol(error.to_string())
}

impl<B: BotFacts + 'static> DocumentRepo for PgDocumentRepo<B> {
    type Err = sqlx::Error;

    #[tracing::instrument(err, skip(self))]
    async fn get_document_metadata(
        &self,
        document_id: &str,
    ) -> Result<DocumentMetadata, Self::Err> {
        sqlx::query!(
            r#"
            SELECT
                d.id as "document_id",
                d.owner as "owner",
                COALESCE(db.id, di.id) as "document_version_id!",
                d.name as "document_name",
                d."branchedFromId" as "branched_from_id",
                d."branchedFromVersionId" as "branched_from_version_id",
                d."documentFamilyId" as "document_family_id",
                d."createdAt"::timestamptz as "created_at",
                d."updatedAt"::timestamptz as "updated_at",
                d."fileType" as "file_type",
                db.bom_parts as "document_bom?",
                di.modification_data as "modification_data?",
                d."projectId" as "project_id",
                p.name as "project_name?",
                di.sha as "sha?",
                dt.sub_type as "sub_type?: DocumentSubType",
                d."deletedAt"::timestamptz as "deleted_at"
            FROM
                "Document" d
            LEFT JOIN document_sub_type dt ON dt.document_id = d.id
            LEFT JOIN LATERAL (
                SELECT
                    i.id,
                    i.sha,
                    i."createdAt",
                    (
                        SELECT
                            imod."modificationData"
                        FROM
                            "DocumentInstanceModificationData" imod
                        WHERE
                            imod."documentInstanceId" = i.id
                    ) as modification_data,
                    i."updatedAt"
                FROM
                    "DocumentInstance" i
                WHERE
                    i."documentId" = d.id
                ORDER BY
                    i."createdAt" DESC
                LIMIT 1
            ) di ON true
            LEFT JOIN LATERAL (
                SELECT
                    b.id,
                    (
                        SELECT
                            json_agg(
                                json_build_object(
                                    'id', bp.id,
                                    'sha', bp.sha,
                                    'path', bp.path
                                )
                            )
                        FROM
                            "BomPart" bp
                        WHERE
                            bp."documentBomId" = b.id
                    ) as bom_parts
                FROM
                    "DocumentBom" b
                WHERE
                    b."documentId" = d.id
                ORDER BY
                    b."createdAt" DESC
                LIMIT 1
            ) db ON d."fileType" = 'docx'
            LEFT JOIN LATERAL (
                SELECT
                    p.name
                FROM "Project" p
                WHERE p.id = d."projectId"
            ) p ON d."projectId" IS NOT NULL
            WHERE
                d.id = $1
            LIMIT 1
            "#,
            document_id,
        )
        .try_map(|row| {
            Ok(DocumentMetadata {
                document_id: row.document_id,
                document_version_id: row.document_version_id,
                owner: Owner::from_principal_str(&row.owner)
                    .map_err(|e| sqlx::Error::Decode(Box::new(e)))?,
                document_name: row.document_name,
                file_type: row.file_type,
                sha: row.sha,
                project_id: row.project_id,
                project_name: row.project_name,
                branched_from_id: row.branched_from_id,
                branched_from_version_id: row.branched_from_version_id,
                document_family_id: row.document_family_id,
                document_bom: row.document_bom,
                modification_data: row.modification_data,
                created_at: row.created_at,
                updated_at: row.updated_at,
                sub_type: row.sub_type,
                deleted_at: row.deleted_at,
            })
        })
        .fetch_one(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_user_view_location(
        &self,
        user_id: &str,
        document_id: &str,
    ) -> Result<Option<String>, Self::Err> {
        let row = sqlx::query!(
            r#"
            SELECT location
            FROM "UserDocumentViewLocation"
            WHERE user_id = $1 AND document_id = $2
            "#,
            user_id,
            document_id,
        )
        .fetch_optional(&self.pool)
        .await?;

        Ok(row.map(|r| r.location))
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_basic_document(&self, document_id: &str) -> Result<DocumentBasic, Self::Err> {
        sqlx::query!(
            r#"
            SELECT
                d.id as "document_id",
                d.owner,
                d.name as "document_name",
                d."branchedFromId" as "branched_from_id",
                d."branchedFromVersionId" as "branched_from_version_id",
                d."documentFamilyId" as "document_family_id",
                d."fileType" as "file_type",
                dt.sub_type as "sub_type?: DocumentSubType",
                d."projectId" as "project_id",
                d."deletedAt"::timestamptz as "deleted_at"
            FROM
                "Document" d
            LEFT JOIN document_sub_type dt ON dt.document_id = d.id
            WHERE
                d.id = $1
            LIMIT 1
            "#,
            document_id,
        )
        .try_map(|row| {
            Ok(DocumentBasic {
                document_id: row.document_id,
                document_name: row.document_name,
                owner: Owner::from_principal_str(&row.owner)
                    .map_err(|e| sqlx::Error::Decode(Box::new(e)))?,
                file_type: row.file_type,
                sub_type: row.sub_type,
                branched_from_id: row.branched_from_id,
                branched_from_version_id: row.branched_from_version_id,
                document_family_id: row.document_family_id,
                project_id: row.project_id,
                deleted_at: row.deleted_at,
            })
        })
        .fetch_one(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_user_name(
        &self,
        user_id: &str,
    ) -> Result<(Option<String>, Option<String>), Self::Err> {
        let ids = vec![user_id.to_owned()];
        // The name row is created by the first name write, so a user who
        // never set a name has none.
        let row = sqlx::query!(
            r#"
            SELECT 
                u.id as user_profile_id, 
                mui.first_name, 
                mui.last_name
            FROM macro_user_info mui
            JOIN "User" u ON mui.macro_user_id = u.macro_user_id
            WHERE u.id = ANY($1)
        "#,
            &ids
        )
        .fetch_optional(&self.pool)
        .await?;
        Ok(row.map_or((None, None), |row| (row.first_name, row.last_name)))
    }

    #[tracing::instrument(err, skip(self))]
    async fn soft_delete_document(&self, document_id: &str) -> Result<(), Self::Err> {
        let mut transaction = self.pool.begin().await?;

        sqlx::query!(
            r#"
            DELETE FROM "Pin" WHERE "pinnedItemId" = $1 AND "pinnedItemType" = $2
            "#,
            document_id,
            "document",
        )
        .execute(&mut *transaction)
        .await?;

        sqlx::query!(
            r#"
            DELETE FROM "UserHistory" WHERE "itemId" = $1 AND "itemType" = $2
            "#,
            document_id,
            "document",
        )
        .execute(&mut *transaction)
        .await?;

        sqlx::query!(
            r#"
            UPDATE "Document"
            SET "deletedAt" = NOW()
            WHERE id = $1
            "#,
            document_id,
        )
        .execute(&mut *transaction)
        .await?;

        if let Ok(id) = macro_uuid::string_to_uuid(document_id) {
            entity_registry_db_utils::mark_deleted(&mut transaction, id, chrono::Utc::now())
                .await
                .map_err(registry_protocol_error)?;
        }

        transaction.commit().await?;
        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_latest_document_version_id(
        &self,
        document_id: &str,
    ) -> Result<(i64, bool), Self::Err> {
        sqlx::query!(
            r#"
            SELECT
                di.id,
                d.uploaded
            FROM "DocumentInstance" di
            JOIN "Document" d ON di."documentId" = d.id
            WHERE di."documentId" = $1
            ORDER BY di."createdAt" DESC
            LIMIT 1
            "#,
            document_id,
        )
        .map(|row| (row.id, row.uploaded))
        .fetch_one(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_document_version_id(&self, document_id: &str) -> Result<(i64, bool), Self::Err> {
        sqlx::query!(
            r#"
            SELECT
                COALESCE(db.id, di.id) as "id!",
                d.uploaded
            FROM
                "Document" d
            LEFT JOIN LATERAL (
                SELECT
                    i.id
                FROM
                    "DocumentInstance" i
                WHERE
                    i."documentId" = d.id
                ORDER BY
                    i."createdAt" ASC
                LIMIT 1
            ) di ON d."fileType" IS DISTINCT FROM 'docx'
            LEFT JOIN LATERAL (
                SELECT
                    b.id
                FROM
                    "DocumentBom" b
                WHERE
                    b."documentId" = d.id
                ORDER BY
                    b."createdAt" ASC
                LIMIT 1
            ) db ON d."fileType" = 'docx'
            WHERE
                d.id = $1
            LIMIT 1
            "#,
            document_id,
        )
        .map(|row| (row.id, row.uploaded))
        .fetch_one(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_document_shas(&self, document_version_id: i64) -> Result<Vec<String>, Self::Err> {
        sqlx::query!(
            r#"
            SELECT bp.sha
            FROM "BomPart" bp
            WHERE bp."documentBomId" = $1
            "#,
            document_version_id,
        )
        .map(|r| r.sha)
        .fetch_all(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_document_shas_by_document_id(
        &self,
        document_id: &str,
    ) -> Result<Vec<String>, Self::Err> {
        sqlx::query!(
            r#"
            SELECT bp.sha
            FROM "BomPart" bp
            JOIN "DocumentBom" db ON bp."documentBomId" = db.id
            WHERE db."documentId" = $1
            AND db.id = (
                SELECT db_inner.id
                FROM "DocumentBom" db_inner
                WHERE db_inner."documentId" = $1
                ORDER BY db_inner."updatedAt" DESC
                LIMIT 1
            )
            "#,
            document_id,
        )
        .map(|r| r.sha)
        .fetch_all(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_document_text(&self, document_id: &str) -> Result<String, Self::Err> {
        let content = sqlx::query!(
            r#"
            SELECT
                d.content
            FROM
                "DocumentText" d
            WHERE
                d."documentId" = $1
            "#,
            document_id
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(content.content)
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_owner_team(&self, owner: &Owner) -> Result<Option<OwnerTeam>, Self::Err> {
        let row = sqlx::query!(
            r#"
            SELECT t.id AS "team_id!", t.default_link_share AS "default_link_share?: LinkShare"
            FROM owner_team($1) ot
            JOIN team t ON t.id = ot.team_id
            LIMIT 1
            "#,
            owner.principal_id(),
        )
        .fetch_optional(&self.pool)
        .await?;

        Ok(row.map(|row| OwnerTeam {
            team_id: row.team_id,
            default_link_share: TeamLinkShareDefault(row.default_link_share),
        }))
    }

    #[tracing::instrument(err, skip(self, args, share_permission))]
    async fn create_document(
        &self,
        args: CreateDocumentRepoArgs,
        share_permission: SharePermissionV2,
    ) -> Result<DocumentMetadata, DocumentError> {
        let mut transaction = self.pool.begin().await?;
        let metadata =
            create::insert_new_document(&mut transaction, &self.registrar, args, &share_permission)
                .await?;
        transaction.commit().await?;
        Ok(metadata)
    }

    #[tracing::instrument(err, skip(self, args, share_permission))]
    async fn import_email_attachment_document(
        &self,
        args: ImportEmailAttachmentRepoArgs,
        share_permission: SharePermissionV2,
    ) -> Result<EmailImportRepoOutcome, DocumentError> {
        let ImportEmailAttachmentRepoArgs {
            email_attachment_id,
            owner,
            mut document,
        } = args;
        // Imports do not carry task-creation consent, including reuse paths.
        document.share_with_team = false;

        // Unlocked reuse: attachment already linked, or a live email doc with
        // this sha already exists. The advisory lock is only required when a
        // concurrent import might insert the first document for (owner, sha).
        if let Some(existing_id) =
            create::find_document_id_for_email_attachment(&self.pool, email_attachment_id).await?
        {
            return self.reused_email_document(existing_id).await;
        }

        let owner = Owner::User(owner);
        let owner_principal = owner.principal_id();
        if let Some(existing_id) = self
            .find_reusable_email_document_by_sha(&owner_principal, &document.sha)
            .await?
        {
            return self
                .link_existing_email_document(&existing_id, email_attachment_id)
                .await;
        }

        let mut transaction = self.pool.begin().await?;

        if let Some(existing_id) = create::reuse_email_document(
            &mut transaction,
            &owner_principal,
            &document.sha,
            email_attachment_id,
        )
        .await?
        {
            transaction.commit().await?;
            return self.reused_email_document(existing_id).await;
        }

        let metadata = create::insert_new_document(
            &mut transaction,
            &self.registrar,
            CreateDocumentRepoArgs { owner, document },
            &share_permission,
        )
        .await?;

        match create::link_document_email(
            &mut transaction,
            &metadata.document_id,
            email_attachment_id,
        )
        .await
        {
            Ok(()) => {}
            Err(sqlx::Error::Database(ref db_err)) if db_err.is_unique_violation() => {
                transaction.rollback().await?;
                return self
                    .reused_linked_email_attachment(email_attachment_id)
                    .await;
            }
            Err(e) => return Err(e.into()),
        }

        transaction.commit().await?;
        Ok(EmailImportRepoOutcome::Created(metadata))
    }

    #[tracing::instrument(err, skip(self, args))]
    async fn edit_document(&self, args: EditDocumentRepoArgs) -> Result<(), DocumentError> {
        use share_permission_db_utils::team_share;

        let mut transaction = self.pool.begin().await?;
        if let Some(command) = &args.team_share {
            if command.expected().entity.entity_type != EntityType::Document
                || command.expected().entity.entity_id != args.document_id
                || args
                    .share_permission
                    .as_ref()
                    .and_then(|p| p.team_share_access_level)
                    != Some(command.target().map(|grant| grant.level.into()))
            {
                return Err(DocumentError::BadRequest(
                    "team-share command does not match edit".to_string(),
                ));
            }
            team_share::apply(&mut transaction, command)
                .await
                .map_err(share::map_team_share_error)?;
        } else if args
            .share_permission
            .as_ref()
            .is_some_and(|p| p.team_share_access_level.is_some())
        {
            return Err(DocumentError::Unauthorized);
        }

        use crate::domain::models::FileTypeUpdate;
        let file_type_db = args.file_type.map(|update| match update {
            FileTypeUpdate::Set(ft) => Some(ft.to_string()),
            FileTypeUpdate::Clear => None,
        });

        edit::update_document_metadata(
            &mut transaction,
            &args.document_id,
            args.document_name.as_deref(),
            args.project_id.as_deref(),
            file_type_db,
        )
        .await?;

        if let Some(ref share_permission) = args.share_permission {
            edit::update_share_permission(&mut transaction, &args.document_id, share_permission)
                .await?;
        }

        if args.revoke_non_owner_user_access {
            let owner = Owner::from_principal_str(
                &edit::get_document_owner(&mut transaction, &args.document_id).await?,
            )
            .map_err(|e| sqlx::Error::Decode(Box::new(e)))?;
            let owner_principal = owner.principal_id();

            // SAFETY: document IDs are UUID strings.
            let entity_id = macro_uuid::string_to_uuid(&args.document_id).unwrap();

            entity_access_db_utils::remove_non_owner_user_entity_access(
                &mut transaction,
                &entity_id,
                EntityType::Document,
                &owner_principal,
            )
            .await?;
        }

        transaction.commit().await?;
        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn update_upload_job(&self, document_id: &str, job_id: &str) -> Result<(), Self::Err> {
        let result = sqlx::query!(
            r#"
            UPDATE "UploadJob" SET "documentId" = $1 WHERE "jobId" = $2
            "#,
            document_id,
            job_id,
        )
        .execute(&self.pool)
        .await?;

        if result.rows_affected() == 0 {
            return Err(sqlx::Error::RowNotFound);
        }

        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn update_document_modified(&self, document_id: &str) -> Result<(), Self::Err> {
        update_document_modified(&self.pool, document_id).await
    }

    #[tracing::instrument(err, skip(self))]
    async fn update_project_modified(&self, project_id: &str) -> Result<(), Self::Err> {
        sqlx::query!(
            r#"UPDATE "Project" SET "updatedAt" = NOW() WHERE id = $1"#,
            project_id,
        )
        .execute(&self.pool)
        .await?;

        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn extend_project_dates(
        &self,
        project_id: &str,
        created_at: chrono::DateTime<chrono::Utc>,
        updated_at: chrono::DateTime<chrono::Utc>,
    ) -> Result<(), Self::Err> {
        sqlx::query!(
            r#"
            WITH RECURSIVE ancestors AS (
                SELECT id, "parentId" FROM "Project" WHERE id = $1
                UNION
                SELECT p.id, p."parentId"
                FROM "Project" p
                JOIN ancestors a ON p.id = a."parentId"
            )
            UPDATE "Project"
            SET "createdAt" = LEAST("createdAt", $2),
                "updatedAt" = GREATEST("updatedAt", $3)
            WHERE id IN (SELECT id FROM ancestors)
            "#,
            project_id,
            created_at.naive_utc(),
            updated_at.naive_utc(),
        )
        .execute(&self.pool)
        .await?;

        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn delete_document_by_id(&self, document_id: &str) -> Result<(), Self::Err> {
        let mut transaction = self.pool.begin().await?;
        sqlx::query!(r#"DELETE FROM "Document" WHERE id = $1"#, document_id)
            .execute(&mut *transaction)
            .await?;
        if let Ok(id) = macro_uuid::string_to_uuid(document_id) {
            entity_registry_db_utils::delete_entity(&mut transaction, id)
                .await
                .map_err(registry_protocol_error)?;
        }
        transaction.commit().await?;
        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    #[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
    async fn mark_document_uploaded(&self, document_id: &str) -> Result<(), Self::Err> {
        let result = sqlx::query(
            r#"
            UPDATE "Document"
            SET "uploaded" = true,
                "contentState" = 'ready',
                "contentLocation" = COALESCE("contentLocation", 'unknown'),
                "updatedAt" = NOW()
            WHERE id = $1
            "#,
        )
        .bind(document_id)
        .execute(&self.pool)
        .await?;

        if result.rows_affected() == 0 {
            return Err(sqlx::Error::RowNotFound);
        }

        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    #[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
    async fn get_persisted_document_content(
        &self,
        document_id: &str,
    ) -> Result<Option<DocumentContent>, Self::Err> {
        let Some(row) = sqlx::query(
            r#"
            SELECT "contentState", "contentLocation"
            FROM "Document"
            WHERE id = $1
            LIMIT 1
            "#,
        )
        .bind(document_id)
        .fetch_optional(&self.pool)
        .await?
        else {
            return Ok(None);
        };

        let state: Option<String> = row.try_get("contentState")?;
        let location: Option<String> = row.try_get("contentLocation")?;

        Ok(state.and_then(|state| DocumentContent::from_db_columns(&state, location.as_deref())))
    }

    #[tracing::instrument(err, skip(self, content))]
    #[allow(clippy::disallowed_methods, reason = "legacy code. fix later")]
    async fn set_document_content(
        &self,
        document_id: &str,
        content: DocumentContent,
    ) -> Result<(), Self::Err> {
        let uploaded = content.state == DocumentContentState::Ready;
        let result = sqlx::query(
            r#"
            UPDATE "Document"
            SET "uploaded" = $2,
                "contentState" = $3,
                "contentLocation" = $4,
                "updatedAt" = NOW()
            WHERE id = $1
            "#,
        )
        .bind(document_id)
        .bind(uploaded)
        .bind(content.state_db_value())
        .bind(content.location_db_value())
        .execute(&self.pool)
        .await?;

        if result.rows_affected() == 0 {
            return Err(sqlx::Error::RowNotFound);
        }

        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_team_ids_for_user(&self, user_id: &str) -> Result<Vec<uuid::Uuid>, Self::Err> {
        let rows = sqlx::query!(
            r#"
            SELECT team_id
            FROM team_user
            WHERE user_id = $1
            ORDER BY team_id
            "#,
            user_id,
        )
        .fetch_all(&self.pool)
        .await?;

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

    #[tracing::instrument(err, skip(self))]
    async fn get_team_task_metadata(
        &self,
        document_id: &str,
    ) -> Result<Option<TeamTaskMetadata>, Self::Err> {
        let Some(row) = sqlx::query!(
            r#"
            SELECT team_id, task_num
            FROM team_task
            WHERE document_id = $1
            LIMIT 1
            "#,
            document_id,
        )
        .fetch_optional(&self.pool)
        .await?
        else {
            return Ok(None);
        };

        Ok(Some(TeamTaskMetadata {
            team_id: row.team_id,
            task_num: row.task_num,
        }))
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_team_task_number(
        &self,
        document_id: &str,
    ) -> Result<Option<TeamTaskNumber>, Self::Err> {
        sqlx::query_as!(
            TeamTaskNumber,
            r#"
            SELECT t.slug AS "team_slug!", tt.task_num
            FROM team_task tt
            JOIN team t ON t.id = tt.team_id
            WHERE tt.document_id = $1
            "#,
            document_id,
        )
        .fetch_optional(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_document_id_by_team_task_number(
        &self,
        team_id: &uuid::Uuid,
        task_num: i32,
    ) -> Result<Option<String>, Self::Err> {
        sqlx::query_scalar!(
            r#"
            SELECT document_id
            FROM team_task
            WHERE team_id = $1 AND task_num = $2
            "#,
            team_id,
            task_num,
        )
        .fetch_optional(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_branch_name_context(
        &self,
        document_id: &str,
        user_id: &str,
    ) -> Result<BranchNameContext, Self::Err> {
        let row = sqlx::query!(
            r#"
            SELECT
                COALESCE(u.email, NULLIF(split_part(request_user.user_id, '|', 2), ''), request_user.user_id) AS "user_email!",
                gl.github_username AS "github_username?",
                t.slug AS "team_slug?",
                tt.task_num AS "team_task_id?"
            FROM (SELECT $1::text AS user_id) request_user
            LEFT JOIN "User" u ON u.id = request_user.user_id
            LEFT JOIN LATERAL (
                SELECT github_username
                FROM github_links
                WHERE macro_id = request_user.user_id
                ORDER BY updated_at DESC
                LIMIT 1
            ) gl ON true
            LEFT JOIN LATERAL (
                SELECT team_id
                FROM team_user
                WHERE user_id = request_user.user_id
                ORDER BY team_id
                LIMIT 1
            ) tu ON true
            LEFT JOIN team t ON t.id = tu.team_id
            LEFT JOIN team_task tt ON tt.team_id = tu.team_id AND tt.document_id = $2
            "#,
            user_id,
            document_id,
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(BranchNameContext {
            user_email: row.user_email,
            github_username: row.github_username,
            team_slug: row.team_slug,
            team_task_id: row.team_task_id,
        })
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_task_github_pull_request_keys(
        &self,
        task_short_id: &str,
    ) -> Result<Vec<String>, Self::Err> {
        sqlx::query_scalar!(
            r#"
            SELECT github_key
            FROM github_pr_tasks
            WHERE task_id = $1
            ORDER BY created_at ASC, github_key ASC
            "#,
            task_short_id,
        )
        .fetch_all(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self, github_keys), fields(github_keys = github_keys.len()))]
    async fn get_github_pull_request_task_links(
        &self,
        github_keys: &[String],
    ) -> Result<Vec<(String, String)>, Self::Err> {
        let rows = sqlx::query!(
            r#"
            SELECT github_key, task_id
            FROM github_pr_tasks
            WHERE github_key = ANY($1)
            ORDER BY created_at ASC, task_id ASC
            "#,
            github_keys,
        )
        .fetch_all(&self.pool)
        .await?;
        Ok(rows
            .into_iter()
            .map(|row| (row.github_key, row.task_id))
            .collect())
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_team_share_facts(
        &self,
        document_id: &str,
    ) -> Result<models_permissions::share_permission::team_share::TeamShareFacts, DocumentError>
    {
        share::get_team_share_facts(&self.pool, document_id).await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_team_share(&self, document_id: &str) -> Result<DocumentTeamShare, DocumentError> {
        share::get_team_share(&self.pool, document_id).await
    }

    #[tracing::instrument(err, skip(self))]
    async fn set_team_share(
        &self,
        command: models_permissions::share_permission::team_share::AuthorizedTeamShareCommand,
    ) -> Result<DocumentTeamShare, DocumentError> {
        share::set_team_share(&self.pool, command).await
    }

    #[tracing::instrument(err, skip(self, document_ids), fields(skills = document_ids.len()))]
    async fn get_skill_descriptions(
        &self,
        document_ids: &[String],
    ) -> Result<
        std::collections::HashMap<String, crate::domain::skill_description::SkillDescription>,
        Self::Err,
    > {
        let rows = sqlx::query!(
            r#"
            SELECT document_id, description
            FROM skill_description
            WHERE document_id = ANY($1)
            "#,
            document_ids,
        )
        .fetch_all(&self.pool)
        .await?;
        Ok(rows
            .into_iter()
            .map(|row| {
                (
                    row.document_id,
                    crate::domain::skill_description::SkillDescription::from_stored(
                        row.description,
                    ),
                )
            })
            .collect())
    }

    #[tracing::instrument(err, skip(self, description))]
    async fn set_skill_description(
        &self,
        document_id: &str,
        description: &crate::domain::skill_description::SkillDescription,
    ) -> Result<(), Self::Err> {
        sqlx::query!(
            r#"
            INSERT INTO skill_description (document_id, description)
            VALUES ($1, $2)
            ON CONFLICT (document_id)
            DO UPDATE SET description = EXCLUDED.description, updated_at = now()
            "#,
            document_id,
            description.as_str(),
        )
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_document_metadata_at_version(
        &self,
        document_id: &str,
        version_id: i64,
    ) -> Result<DocumentMetadata, Self::Err> {
        sqlx::query!(
            r#"
            SELECT
                d.id as "document_id",
                d.owner as "owner",
                d.name as "document_name",
                COALESCE(di.id, db.id) as "document_version_id!",
                d."branchedFromId" as "branched_from_id",
                d."branchedFromVersionId" as "branched_from_version_id",
                d."documentFamilyId" as "document_family_id",
                d."createdAt"::timestamptz as "created_at",
                d."updatedAt"::timestamptz as "updated_at",
                d."fileType" as "file_type",
                db.bom_parts as "document_bom?",
                di.modification_data as "modification_data?",
                d."projectId" as "project_id",
                p.name as "project_name?",
                di.sha as "sha?",
                dt.sub_type as "sub_type?: DocumentSubType",
                d."deletedAt"::timestamptz as "deleted_at"
            FROM
                "Document" d
            LEFT JOIN document_sub_type dt ON dt.document_id = d.id
            LEFT JOIN LATERAL (
                SELECT
                    i.id,
                    i.sha,
                    i."createdAt",
                    (
                        SELECT
                            imod."modificationData"
                        FROM
                            "DocumentInstanceModificationData" imod
                        WHERE
                            imod."documentInstanceId" = i.id
                    ) as modification_data,
                    i."updatedAt"
                FROM
                    "DocumentInstance" i
                WHERE
                    i."documentId" = d.id
                AND
                    i.id = $2
            ) di ON true
            LEFT JOIN LATERAL (
                SELECT
                    b.id,
                    (
                        SELECT
                            json_agg(
                                json_build_object(
                                    'id', bp.id,
                                    'sha', bp.sha,
                                    'path', bp.path
                                )
                            )
                        FROM
                            "BomPart" bp
                        WHERE
                            bp."documentBomId" = b.id
                    ) as bom_parts
                FROM
                    "DocumentBom" b
                WHERE
                    b."documentId" = d.id
                AND
                    b.id = $2
            ) db ON d."fileType" = 'docx'
            LEFT JOIN LATERAL (
                SELECT
                    p.name
                FROM "Project" p
                WHERE p.id = d."projectId"
            ) p ON d."projectId" IS NOT NULL
            WHERE
                d.id = $1
            LIMIT 1
            "#,
            document_id,
            version_id,
        )
        .try_map(|row| {
            Ok(DocumentMetadata {
                document_id: row.document_id,
                document_version_id: row.document_version_id,
                owner: Owner::from_principal_str(&row.owner)
                    .map_err(|e| sqlx::Error::Decode(Box::new(e)))?,
                document_name: row.document_name,
                file_type: row.file_type,
                sha: row.sha,
                project_id: row.project_id,
                project_name: row.project_name,
                branched_from_id: row.branched_from_id,
                branched_from_version_id: row.branched_from_version_id,
                document_family_id: row.document_family_id,
                document_bom: row.document_bom,
                modification_data: row.modification_data,
                created_at: row.created_at,
                updated_at: row.updated_at,
                sub_type: row.sub_type,
                deleted_at: row.deleted_at,
            })
        })
        .fetch_one(&self.pool)
        .await
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_project_owner(
        &self,
        project_id: &str,
    ) -> Result<MacroUserIdStr<'static>, Self::Err> {
        let row = sqlx::query!(
            r#"SELECT "userId" as user_id FROM "Project" WHERE id = $1"#,
            project_id,
        )
        .fetch_one(&self.pool)
        .await?;

        MacroUserIdStr::parse_from_str(&row.user_id)
            .map(|u| u.into_owned())
            .map_err(|e| sqlx::Error::Decode(Box::new(e)))
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_project_name(&self, project_id: &str) -> Result<String, Self::Err> {
        let row = sqlx::query!(r#"SELECT name FROM "Project" WHERE id = $1"#, project_id,)
            .fetch_one(&self.pool)
            .await?;
        Ok(row.name)
    }

    #[tracing::instrument(err, skip(self))]
    async fn get_project_children(
        &self,
        project_id: &str,
    ) -> Result<Vec<Entity<'static>>, Self::Err> {
        let documents = sqlx::query!(
            r#"SELECT id FROM "Document" WHERE "projectId" = $1 AND "deletedAt" IS NULL"#,
            project_id,
        )
        .fetch_all(&self.pool)
        .await?;

        let sub_projects = sqlx::query!(
            r#"SELECT id FROM "Project" WHERE "parentId" = $1 AND "deletedAt" IS NULL"#,
            project_id,
        )
        .fetch_all(&self.pool)
        .await?;

        let mut children: Vec<Entity<'static>> =
            Vec::with_capacity(documents.len() + sub_projects.len());
        for row in documents {
            children.push(EntityType::Document.with_entity_string(row.id));
        }
        for row in sub_projects {
            children.push(EntityType::Project.with_entity_string(row.id));
        }
        Ok(children)
    }

    #[tracing::instrument(err, skip(self, args, share_permission))]
    async fn copy_document(
        &self,
        args: CopyDocumentRepoArgs,
        share_permission: SharePermissionV2,
    ) -> Result<DocumentMetadata, Self::Err> {
        let CopyDocumentRepoArgs {
            original_document,
            owner,
            document_name,
            file_type,
            team_id,
        } = args;

        let mut transaction = self.pool.begin().await?;

        let document = match file_type {
            Some(model::document::FileType::Docx) => {
                copy::copy_docx_document(
                    &mut transaction,
                    &original_document,
                    &owner,
                    &document_name,
                )
                .await
            }
            _ => {
                copy::copy_non_docx_document(
                    &mut transaction,
                    &original_document,
                    &owner,
                    &document_name,
                )
                .await
            }
        }?;

        let document_id = uuid::Uuid::parse_str(&document.document_id)
            .map_err(|e| sqlx::Error::Decode(Box::new(e)))?;

        if document.sub_type == Some(DocumentSubType::Task)
            && let Some(team_id) = team_id.as_ref()
        {
            create::allocate_team_task_number(&mut transaction, team_id, &document_id).await?;
        }

        create::set_share_permission(&mut transaction, &document_id, &share_permission).await?;

        self.registrar
            .register_owned_entity(
                &mut transaction,
                NewEntityRecord::new(document_id, RegisteredEntityType::Document, owner.clone()),
            )
            .await
            .map_err(registry_protocol_error)?;

        let now = chrono::Utc::now();
        create::insert_history(&mut transaction, &document_id, owner.as_user(), &now).await?;

        transaction.commit().await?;

        Ok(document)
    }

    #[tracing::instrument(err, skip(self))]
    async fn copy_pdf_parts(
        &self,
        new_document_id: &str,
        original_document_id: &str,
    ) -> Result<(), Self::Err> {
        let mut transaction = self.pool.begin().await?;
        copy::copy_pdf_parts(&mut transaction, new_document_id, original_document_id).await?;
        transaction.commit().await?;
        Ok(())
    }
}