Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.
TypeScript agentaiai-agentsall-in-onecrmcrm-systememaillinearmcpmessagingnotesnotionofficerustslackslack-alternativestartupstartup-toolstypescriptworkspace
use model_owner::Owner;
use sqlx::{Pool, Postgres, Transaction};
use document_sub_type::DocumentSubType;
use model::document::{BomPart, DocumentBasic, DocumentMetadata};
/// Gets the documents name
#[tracing::instrument(skip(db))]
pub async fn get_document_name(db: &Pool<Postgres>, document_id: &str) -> anyhow::Result<String> {
let document = sqlx::query!(
r#"
SELECT
d.name
FROM "Document" d
WHERE d."id" = $1
"#,
document_id,
)
.map(|row| row.name)
.fetch_one(db)
.await?;
Ok(document)
}
/// Gets the documents latest version id
/// Used to get location for editable documents (js, py)
#[tracing::instrument(skip(db))]
pub async fn get_latest_document_version_id(
db: &Pool<Postgres>,
document_id: &str,
) -> anyhow::Result<(i64, bool)> {
let document = 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(db)
.await?;
Ok(document)
}
/// Gets the documents latest bom version id
/// Used to get location for editable documents (js, py)
#[tracing::instrument(skip(db))]
pub async fn get_latest_document_bom_version_id(
db: &Pool<Postgres>,
document_id: &str,
) -> anyhow::Result<i64> {
let document = sqlx::query!(
r#"
SELECT
db.id
FROM "DocumentBom" db
JOIN "Document" d ON db."documentId" = d.id
WHERE db."documentId" = $1
ORDER BY db."createdAt" DESC
LIMIT 1
"#,
document_id,
)
.map(|row| row.id)
.fetch_one(db)
.await?;
Ok(document)
}
/// Gets the documents oldest version id
/// Used to get location for static documents (pdf, html)
/// Also gets if the document has been uploaded
#[tracing::instrument(skip(db))]
pub async fn get_document_version_id(
db: &Pool<Postgres>,
document_id: &str,
) -> anyhow::Result<(i64, bool)> {
let document = 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(db)
.await?;
Ok(document)
}
pub async fn get_basic_document(
db: &Pool<Postgres>,
document_id: &str,
) -> Result<DocumentBasic, sqlx::Error> {
let document: DocumentBasic = 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(db)
.await?;
Ok(document)
}
pub async fn get_basic_documents(
db: &Pool<Postgres>,
document_ids: &[String],
) -> Result<Vec<DocumentBasic>, sqlx::Error> {
if document_ids.is_empty() {
return Ok(Vec::new());
}
let documents: Vec<DocumentBasic> = 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 = ANY($1)
"#,
document_ids,
)
.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_all(db)
.await?;
Ok(documents)
}
/// Gets the basic document info for deleted documents
pub async fn get_deleted_document_info(
db: &Pool<Postgres>,
document_id: &str,
) -> Result<DocumentBasic, sqlx::Error> {
let document: DocumentBasic = 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
"#,
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(db)
.await?;
Ok(document)
}
/// Gets the metadata for a provided document id.
/// This will also get the MOST recent document instance as part of that metadata.
pub async fn get_document(
db: &Pool<Postgres>,
document_id: &str,
) -> anyhow::Result<DocumentMetadata> {
let document_metadata: DocumentMetadata = 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(db)
.await?;
Ok(document_metadata)
}
/// Gets the metadata for a provided document id and version id.
pub async fn get_document_version(
db: &Pool<Postgres>,
document_id: &str,
document_version_id: i64,
) -> anyhow::Result<DocumentMetadata> {
let document_metadata: DocumentMetadata = 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,
document_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(db)
.await?;
Ok(document_metadata)
}
#[tracing::instrument(skip(db))]
pub async fn get_document_bom(
db: Pool<Postgres>,
document_id: &str,
) -> anyhow::Result<Vec<BomPart>> {
let document_bom_id = sqlx::query!(
r#"
SELECT b.id as "id?"
FROM "DocumentBom" b
WHERE b."documentId" = $1
ORDER BY b."createdAt" DESC
LIMIT 1
"#,
document_id,
)
.fetch_one(&db)
.await?;
let document_bom = sqlx::query_as!(
BomPart,
r#"
SELECT
bp.id as id,
bp.sha as sha,
bp.path as path
FROM
"BomPart" bp
WHERE
bp."documentBomId" = $1
"#,
document_bom_id.id,
)
.fetch_all(&db)
.await?;
Ok(document_bom)
}
/// Gets the documents bom parts for all of it's versions
#[tracing::instrument(skip(db))]
pub async fn get_bom_parts(db: &Pool<Postgres>, document_id: &str) -> anyhow::Result<Vec<BomPart>> {
let bom_parts = sqlx::query_as!(
BomPart,
r#"
SELECT
bp.sha as sha,
bp.path as path,
bp.id as id
FROM "BomPart" bp
JOIN "DocumentBom" db ON bp."documentBomId" = db.id
WHERE db."documentId" = $1;
"#,
document_id,
)
.fetch_all(db)
.await?;
Ok(bom_parts)
}
/// Given a list of document ids, get the bom parts for all of them
#[tracing::instrument(skip(transaction))]
pub async fn get_bom_parts_bulk_tsx(
transaction: &mut Transaction<'_, Postgres>,
document_ids: &[impl ToString + std::fmt::Debug],
) -> anyhow::Result<Vec<BomPart>> {
let document_ids: Vec<String> = document_ids.iter().map(|s| s.to_string()).collect();
let result = sqlx::query_as!(
BomPart,
r#"
SELECT
bp.sha as sha,
bp.path as path,
bp.id as id
FROM "BomPart" bp
JOIN "DocumentBom" db ON bp."documentBomId" = db.id
WHERE db."documentId" = ANY($1);
"#,
&document_ids
)
.fetch_all(transaction.as_mut())
.await?;
Ok(result)
}
pub async fn get_document_sha(
db: Pool<Postgres>,
document_id: &str,
document_version_id: i64,
) -> anyhow::Result<String> {
let document = sqlx::query!(
r#"
SELECT
di.sha
FROM
"DocumentInstance" di
WHERE di."documentId" = $1 AND di.id = $2
LIMIT 1
"#,
document_id,
document_version_id,
)
.fetch_one(&db)
.await?;
Ok(document.sha)
}
#[cfg(test)]
mod tests {
use super::*;
use sqlx::{Pool, Postgres};
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_user_with_documents")))]
async fn test_get_document_owner(pool: Pool<Postgres>) {
// document doesn't exist
let not_found = get_basic_document(&pool, "random-uuid").await;
assert!(not_found.is_err());
assert_eq!(
not_found.err().unwrap().to_string(),
"no rows returned by a query that expected to return at least one row".to_string()
);
// document exists
assert_eq!(
get_basic_document(&pool, "document-one")
.await
.unwrap()
.owner,
Owner::from_principal_str("macro|[email protected]").unwrap()
);
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_user_with_documents")))]
async fn test_get_document(pool: Pool<Postgres>) {
// document doesn't exist
let no_document_found = get_document(&pool, "random-uuid").await;
assert!(no_document_found.is_err());
assert_eq!(
no_document_found.err().unwrap().to_string(),
"no rows returned by a query that expected to return at least one row".to_string()
);
// document exists
let document_metadata = get_document(&pool, "document-one").await.unwrap();
assert_eq!(document_metadata.document_id, "document-one".to_string());
assert_eq!(document_metadata.document_version_id, 1,);
assert_eq!(
document_metadata.document_name,
"test_document_name".to_string()
);
assert_eq!(
document_metadata.owner,
Owner::from_principal_str("macro|[email protected]").unwrap()
);
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_user_with_documents")))]
async fn test_get_document_null_file_type(pool: Pool<Postgres>) {
// document exists
let document_metadata = get_document(&pool, "document-four").await.unwrap();
assert_eq!(document_metadata.document_id, "document-four".to_string());
assert_eq!(document_metadata.document_version_id, 2);
assert_eq!(document_metadata.file_type, None);
assert_eq!(
document_metadata.document_name,
"test_document_name".to_string()
);
assert_eq!(
document_metadata.owner,
Owner::from_principal_str("macro|[email protected]").unwrap()
);
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_user_with_documents")))]
async fn test_get_document_version_null_file_type(pool: Pool<Postgres>) {
// document exists
let document_metadata = get_document_version(&pool, "document-four", 2)
.await
.unwrap();
assert_eq!(document_metadata.document_id, "document-four".to_string());
assert_eq!(document_metadata.document_version_id, 2);
assert_eq!(document_metadata.file_type, None);
assert_eq!(
document_metadata.document_name,
"test_document_name".to_string()
);
assert_eq!(
document_metadata.owner,
Owner::from_principal_str("macro|[email protected]").unwrap()
);
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_user_with_documents")))]
async fn test_get_document_sha(pool: Pool<Postgres>) {
// document doesn't exist
let no_document_found = get_document(&pool, "random-uuid").await;
assert!(no_document_found.is_err());
assert_eq!(
no_document_found.err().unwrap().to_string(),
"no rows returned by a query that expected to return at least one row".to_string()
);
// document exists
let sha = get_document_sha(pool.clone(), "document-one", 1)
.await
.unwrap();
assert_eq!(sha, "sha-one".to_string());
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("docx_example")))]
async fn test_get_document_bom(pool: Pool<Postgres>) {
let no_document_found = get_document_bom(pool.clone(), "random-uuid").await;
assert!(no_document_found.is_err());
assert_eq!(
no_document_found.err().unwrap().to_string(),
"no rows returned by a query that expected to return at least one row".to_string()
);
// document exists
let bom_parts = get_document_bom(pool.clone(), "document-one")
.await
.unwrap();
assert_eq!(
bom_parts,
vec![
BomPart {
id: "b4".to_string(),
sha: "sha-1".to_string(),
path: "path".to_string()
},
BomPart {
id: "b5".to_string(),
sha: "sha-2".to_string(),
path: "path".to_string()
},
BomPart {
id: "b6".to_string(),
sha: "sha-4".to_string(),
path: "path".to_string()
}
]
);
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_versioned_document")))]
async fn test_get_document_version_id(pool: Pool<Postgres>) -> anyhow::Result<()> {
let document_version = get_document_version_id(&pool, "document-one").await?;
assert_eq!(document_version, (1, false));
Ok(())
}
#[sqlx::test(fixtures(path = "../../fixtures", scripts("basic_user_with_documents")))]
async fn test_get_document_version_id_null_file_type(
pool: Pool<Postgres>,
) -> anyhow::Result<()> {
let document_version = get_document_version_id(&pool, "document-four").await?;
assert_eq!(document_version, (2, false));
Ok(())
}
}