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
//! 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(())
}
}