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
//! Postgres repository for databases, tables, columns and row identities;
//! cells are entity properties, written through the properties adapter.
/// The change journal's statements, shared with the cell store's batches.
mod folders;
pub(crate) mod journal;
/// Row identity statements, shared with the cell store's batches.
pub(crate) mod rows;
mod saved_queries;
/// Schema statements of the cell store's batches.
pub(crate) mod schema;
mod sharing;
mod stored;
#[cfg(test)]
mod test;
/// View and card-place statements, shared with the cell store's batches.
pub(crate) mod views;
use std::collections::HashMap;
use models_databases::position::{Position, PositionError, key_between};
use uuid::Uuid;
use macro_user_id::user_id::MacroUserIdStr;
use models_properties::DataType;
use properties::domain::database_definition_writer::{
DatabaseDefinitionWriter, NewDatabaseDefinition,
};
use sqlx::{PgPool, Postgres, Transaction};
use entity_access_db_utils::{AccessLevel, EntityAccessSourceType};
use model_entity::EntityType;
use crate::domain::models::{
CardPosition, Column, ColumnId, ColumnReplacement, CreateDatabase, Database, DatabaseId,
DatabaseView, FirstTable, PropertyDefinitionId, QueryDefinition, QueryId, RowId, RowRef,
SavedQuery, Table, TableId, TableVersion, ViewId,
};
use crate::domain::ports::DatabasesRepo;
/// Errors from the Postgres repository.
#[derive(Debug, thiserror::Error)]
pub enum PgDatabasesRepoError {
/// Underlying database failure.
#[error("database error")]
Sqlx(#[from] sqlx::Error),
/// A domain value could not be encoded as JSON for storage, or a stored
/// one decoded.
#[error("failed to encode or decode stored json")]
Json(#[from] serde_json::Error),
/// A folder id is not a uuid.
#[error("folder id is not a uuid")]
FolderId(#[from] uuid::Error),
/// A stored position is not a fractional key.
#[error("stored position")]
Position(#[from] PositionError),
/// A stored card lane names no option, person or empty lane.
#[error("stored card lane `{0}` names no lane")]
CorruptLane(String),
/// A board stored without a card title belongs to a table with no
/// column to title it by.
#[error("board {0} has no card title and its table no column")]
UntitledBoard(ViewId),
/// The properties domain refused or failed a write.
#[error("properties write failed: {0}")]
Properties(#[source] Box<dyn std::error::Error + Send + Sync>),
/// A stored journal row's kind is not one the journal writes.
#[error("stored change kind `{0}` is not a row change kind")]
CorruptChangeKind(String),
/// This build cannot interpret the persisted journal payload format.
#[error("unsupported database journal payload version {0}")]
UnsupportedJournalPayloadVersion(i32),
/// The canonical team-share writer failed.
#[error("team share: {0}")]
TeamShare(rootcause::Report<share_permission_db_utils::team_share::TeamShareError>),
}
/// The UUIDs of typed ids, for a statement's `ANY($n)`.
pub(crate) fn uuids<Id: Copy + Into<Uuid>>(ids: &[Id]) -> Vec<Uuid> {
ids.iter().map(|id| (*id).into()).collect()
}
/// The positions as stored, for a statement's `UNNEST($n)`.
pub(crate) fn stored_positions(positions: &[Position]) -> Vec<String> {
positions.iter().map(Position::to_string).collect()
}
/// A `database_entities` row, read by `query_as!` and mapped onto [`Database`].
pub(crate) struct DatabaseRecord {
pub(crate) id: Uuid,
pub(crate) name: String,
pub(crate) owner_id: String,
pub(crate) created_at: chrono::DateTime<chrono::Utc>,
pub(crate) trashed_at: Option<chrono::DateTime<chrono::Utc>>,
pub(crate) project_id: Option<String>,
}
impl From<DatabaseRecord> for Database {
fn from(record: DatabaseRecord) -> Self {
Self {
id: DatabaseId::from_uuid(record.id),
name: record.name,
owner_id: record.owner_id,
created_at: record.created_at,
trashed_at: record.trashed_at,
project_id: record.project_id,
}
}
}
/// A `database_tables` row, read by `query_as!` and mapped onto [`Table`].
pub(crate) struct TableRecord {
pub(crate) id: Uuid,
pub(crate) database_id: Uuid,
pub(crate) name: String,
pub(crate) position: String,
pub(crate) version: i64,
}
impl TryFrom<TableRecord> for Table {
type Error = PositionError;
fn try_from(record: TableRecord) -> Result<Self, Self::Error> {
Ok(Self {
id: TableId::from_uuid(record.id),
database_id: DatabaseId::from_uuid(record.database_id),
name: record.name,
position: record.position.parse()?,
version: TableVersion(record.version),
})
}
}
/// Stored table rows as tables.
pub(crate) fn tables_of(records: Vec<TableRecord>) -> Result<Vec<Table>, PositionError> {
records.into_iter().map(Table::try_from).collect()
}
/// The largest stored position of a list, read back.
pub(crate) fn last_position(stored: Option<String>) -> Result<Option<Position>, PositionError> {
stored.map(|key| key.parse()).transpose()
}
/// The position that appends after `last`, the largest one a list has, or
/// starts an empty list. Positions are fractional keys compared as bytes
/// (the columns are `COLLATE "C"`), so the largest is the last.
fn position_after(last: Option<&Position>) -> Result<Position, PositionError> {
key_between(last, None)
}
/// Insert a core database, its app entity, first table and owner grant atomically.
pub(crate) async fn insert_owned_database(
transaction: &mut Transaction<'static, Postgres>,
database_id: DatabaseId,
name: &str,
owner_id: &str,
table_id: TableId,
table_name: &str,
) -> Result<Database, PgDatabasesRepoError> {
let position = position_after(None)?;
let database = insert_owned_entity(transaction, database_id, name, owner_id, None).await?;
sqlx::query!(
r#"
INSERT INTO database_tables (id, database_id, name, position)
VALUES ($1, $2, $3, $4)
"#,
table_id.into_uuid(),
database_id.into_uuid(),
table_name,
position.as_str(),
)
.execute(&mut **transaction)
.await?;
Ok(database)
}
/// Insert a core database with no tables, its app entity and its owner's
/// grant; `created_at` defaults to the transaction's time.
pub(crate) async fn insert_owned_entity(
transaction: &mut Transaction<'static, Postgres>,
database_id: DatabaseId,
name: &str,
owner_id: &str,
created_at: Option<chrono::DateTime<chrono::Utc>>,
) -> Result<Database, PgDatabasesRepoError> {
sqlx::query!(
"INSERT INTO databases (id) VALUES ($1)",
database_id.into_uuid()
)
.execute(&mut **transaction)
.await?;
let database: Database = sqlx::query_as!(
DatabaseRecord,
r#"
INSERT INTO database_entities (database_id, name, user_id, created_at)
VALUES ($1, $2, $3, COALESCE($4::timestamptz, now()))
RETURNING database_id AS id, name, user_id AS owner_id, created_at, trashed_at, project_id
"#,
database_id.into_uuid(),
name,
owner_id,
created_at,
)
.fetch_one(&mut **transaction)
.await?
.into();
entity_access_db_utils::insert_entity_access_row(
transaction,
database_id.as_uuid(),
EntityType::Database,
owner_id,
EntityAccessSourceType::User,
AccessLevel::Owner,
)
.await?;
Ok(database)
}
/// Insert a column placement bound to `definition_id` inside `transaction`;
/// `infer_type` lets its first value settle a plain text column's type.
pub(crate) async fn insert_column(
transaction: &mut Transaction<'static, Postgres>,
column_id: ColumnId,
table_id: TableId,
definition_id: PropertyDefinitionId,
position: &Position,
infer_type: bool,
) -> Result<(), sqlx::Error> {
sqlx::query!(
"INSERT INTO database_columns (id, table_id, property_definition_id, position, infer_type) VALUES ($1, $2, $3, $4, $5)",
column_id.into_uuid(),
table_id.into_uuid(),
definition_id,
position.as_str(),
infer_type,
)
.execute(&mut **transaction)
.await?;
Ok(())
}
/// [`DatabasesRepo`] backed by MacroDB; a new database's title definition is
/// written through the properties domain, in the same transaction.
#[derive(Debug, Clone)]
pub struct PgDatabasesRepo<Properties> {
pool: PgPool,
properties: Properties,
}
impl<Properties> PgDatabasesRepo<Properties> {
/// Create a repository over the pool and the properties writer.
pub fn new(pool: PgPool, properties: Properties) -> Self {
Self { pool, properties }
}
/// Every table of a live database with a derived column, ordered by
/// database and table.
pub async fn tables_with_derived_columns(
&self,
) -> Result<Vec<(DatabaseId, TableId)>, PgDatabasesRepoError> {
let rows = sqlx::query!(
r#"
SELECT DISTINCT t.database_id, t.id
FROM database_columns c
JOIN database_tables t ON t.id = c.table_id
JOIN database_entities d ON d.database_id = t.database_id
WHERE c.config ->> 'kind' = 'derived' AND d.trashed_at IS NULL
ORDER BY t.database_id, t.id
"#
)
.fetch_all(&self.pool)
.await?;
Ok(rows
.into_iter()
.map(|row| {
(
DatabaseId::from_uuid(row.database_id),
TableId::from_uuid(row.id),
)
})
.collect())
}
}
impl<Properties> DatabasesRepo for PgDatabasesRepo<Properties>
where
Properties: DatabaseDefinitionWriter<Transaction = Transaction<'static, Postgres>>
+ Send
+ Sync
+ 'static,
{
type Error = PgDatabasesRepoError;
#[tracing::instrument(err, skip(self))]
async fn views_for_tables(
&self,
table_ids: &[TableId],
) -> Result<Vec<DatabaseView>, Self::Error> {
views::views_for_tables(&self.pool, table_ids).await
}
#[tracing::instrument(err, skip(self))]
async fn view_positions(&self, view_id: ViewId) -> Result<Vec<CardPosition>, Self::Error> {
views::view_positions(&self.pool, view_id).await
}
#[tracing::instrument(err, skip(self, definition))]
async fn save_query(
&self,
database_id: Option<DatabaseId>,
definition: &QueryDefinition,
created_by: &MacroUserIdStr<'_>,
) -> Result<SavedQuery, Self::Error> {
self.insert_query(database_id, definition, created_by.as_ref())
.await
}
#[tracing::instrument(err, skip(self))]
async fn get_query(&self, id: QueryId) -> Result<Option<SavedQuery>, Self::Error> {
self.query_by_id(id).await
}
#[tracing::instrument(err, skip(self, command))]
async fn create_database(
&self,
command: &CreateDatabase,
first_table: FirstTable,
) -> Result<Database, Self::Error> {
let mut transaction = self.pool.begin().await?;
let table_id = TableId::new();
let database = insert_owned_database(
&mut transaction,
DatabaseId::new(),
&command.name,
command.owner_id.as_ref(),
table_id,
first_table.name,
)
.await?;
let title = self
.properties
.create_database_definition_in(
&mut transaction,
NewDatabaseDefinition {
id: macro_uuid::generate_uuid_v7(),
database_id: database.id.into_uuid(),
name: first_table.title_column,
data_type: DataType::String,
is_multi_select: false,
specific_entity_type: None,
options: &[],
},
)
.await
.map_err(|error| PgDatabasesRepoError::Properties(Box::new(error)))?;
insert_column(
&mut transaction,
ColumnId::new(),
table_id,
title.definition.id,
&position_after(None)?,
true,
)
.await?;
transaction.commit().await?;
Ok(database)
}
/// Returns the row whether or not it is trashed; the domain decides what a
/// trashed database means.
#[tracing::instrument(err, skip(self))]
async fn get_database(
&self,
id: DatabaseId,
) -> Result<Option<(Database, Vec<Table>)>, Self::Error> {
let Some(database) = sqlx::query_as!(
DatabaseRecord,
r#"SELECT database_id AS id, name, user_id AS owner_id, created_at, trashed_at, project_id FROM database_entities WHERE database_id = $1"#,
id.into_uuid()
)
.fetch_optional(&self.pool)
.await?
.map(Database::from) else {
return Ok(None);
};
let tables = sqlx::query_as!(
TableRecord,
r#"
SELECT id, database_id, name, position, version
FROM database_tables
WHERE database_id = $1
ORDER BY position
"#,
id.into_uuid()
)
.fetch_all(&self.pool)
.await?;
let tables = tables_of(tables)?;
Ok(Some((database, tables)))
}
#[tracing::instrument(err, skip(self))]
async fn rename_database(&self, id: DatabaseId, name: &str) -> Result<bool, Self::Error> {
let renamed = sqlx::query!(
r#"UPDATE database_entities SET name = $2, updated_at = now() WHERE database_id = $1"#,
id.into_uuid(),
name,
)
.execute(&self.pool)
.await?;
Ok(renamed.rows_affected() == 1)
}
#[tracing::instrument(err, skip(self))]
async fn trash_database(
&self,
id: DatabaseId,
trashed_at: chrono::DateTime<chrono::Utc>,
) -> Result<bool, Self::Error> {
let trashed = sqlx::query!(
r#"UPDATE database_entities SET trashed_at = $2, updated_at = now() WHERE database_id = $1"#,
id.into_uuid(),
trashed_at,
)
.execute(&self.pool)
.await?;
Ok(trashed.rows_affected() == 1)
}
#[tracing::instrument(err, skip(self))]
async fn restore_database(&self, id: DatabaseId) -> Result<bool, Self::Error> {
let restored = sqlx::query!(
r#"UPDATE database_entities SET trashed_at = NULL, updated_at = now() WHERE database_id = $1"#,
id.into_uuid(),
)
.execute(&self.pool)
.await?;
Ok(restored.rows_affected() == 1)
}
#[tracing::instrument(err, skip(self))]
async fn move_database(
&self,
id: DatabaseId,
project_id: Option<&str>,
) -> Result<bool, Self::Error> {
folders::move_database(&self.pool, id, project_id).await
}
/// Deleting the app entity cleans up its owned storage, grants and journal,
/// including the same cleanup used when its owner is deleted.
#[tracing::instrument(err, skip(self))]
async fn delete_database(&self, id: DatabaseId) -> Result<(), Self::Error> {
sqlx::query!(
r#"DELETE FROM database_entities WHERE database_id = $1"#,
id.into_uuid()
)
.execute(&self.pool)
.await?;
Ok(())
}
#[tracing::instrument(err, skip(self, table, column, actor))]
async fn infer_column_type(
&self,
table: &Table,
column: &Column,
definition_id: PropertyDefinitionId,
actor: &crate::domain::journal::JournalActor,
) -> Result<Option<TableVersion>, Self::Error> {
let mut transaction = self.pool.begin().await?;
if !rows::lock_live_database(&mut transaction, table.database_id).await? {
return Ok(None);
}
// Row writers take this same lock before checking versions and cells.
let current = sqlx::query_scalar!(
"SELECT version FROM database_tables WHERE id = $1 AND database_id = $2 FOR UPDATE",
table.id.into_uuid(),
table.database_id.into_uuid(),
)
.fetch_optional(&mut *transaction)
.await?;
if current != Some(table.version.0) {
return Ok(None);
}
let updated = sqlx::query_scalar!(
r#"UPDATE database_columns SET property_definition_id = $4, infer_type = FALSE
WHERE id = $1 AND table_id = $2 AND property_definition_id = $3 AND infer_type
AND NOT EXISTS (
SELECT 1 FROM entity_properties p
JOIN database_rows r ON r.id::text = p.entity_id
WHERE r.table_id = $2 AND p.property_definition_id = $3
AND p.entity_type = 'DATABASE_ROW')
AND EXISTS (SELECT 1 FROM database_entities WHERE database_id = $5 AND trashed_at IS NULL)
RETURNING id"#,
column.id.into_uuid(),
table.id.into_uuid(),
column.property_definition_id,
definition_id,
table.database_id.into_uuid(),
)
.fetch_optional(&mut *transaction)
.await?;
if updated.is_none() {
return Ok(None);
}
let version = rows::bump_table_version(&mut *transaction, table.id).await?;
journal::record(
&mut transaction,
actor,
&[crate::domain::journal::settled_column(
table.database_id,
table.id,
version,
column.id,
)],
)
.await?;
transaction.commit().await?;
Ok(Some(version))
}
#[tracing::instrument(err, skip(self))]
async fn row_refs(&self, table_id: TableId) -> Result<Vec<RowRef>, Self::Error> {
let rows = sqlx::query!(
"SELECT id, position FROM database_rows WHERE table_id = $1 ORDER BY position, id",
table_id.into_uuid(),
)
.fetch_all(&self.pool)
.await?;
Ok(rows
.into_iter()
.map(|row| {
Ok(RowRef {
id: RowId::from_uuid(row.id),
position: row.position.parse()?,
})
})
.collect::<Result<_, PositionError>>()?)
}
#[tracing::instrument(skip(self), err)]
async fn databases_by_ids(&self, ids: &[DatabaseId]) -> Result<Vec<Database>, Self::Error> {
Ok(sqlx::query_as!(
DatabaseRecord,
r#"
SELECT database_id AS id, name, user_id AS owner_id, created_at, trashed_at, project_id
FROM database_entities
WHERE database_id = ANY($1)
ORDER BY created_at
"#,
&uuids(ids),
)
.fetch_all(&self.pool)
.await?
.into_iter()
.map(Database::from)
.collect())
}
#[tracing::instrument(skip(self), err)]
async fn tables_for_databases(
&self,
database_ids: &[DatabaseId],
) -> Result<Vec<Table>, Self::Error> {
let tables = sqlx::query_as!(
TableRecord,
r#"
SELECT id, database_id, name, position, version
FROM database_tables
WHERE database_id = ANY($1)
ORDER BY database_id, position
"#,
&uuids(database_ids),
)
.fetch_all(&self.pool)
.await?;
Ok(tables_of(tables)?)
}
#[tracing::instrument(skip(self), err)]
async fn columns_for_tables(&self, table_ids: &[TableId]) -> Result<Vec<Column>, Self::Error> {
let rows = sqlx::query!(
r#"
SELECT id, table_id, property_definition_id, position, config, display_name, infer_type, nullable,
ARRAY(SELECT capability FROM database_column_protections p WHERE p.column_id = database_columns.id ORDER BY capability) AS "protections!"
FROM database_columns
WHERE table_id = ANY($1)
ORDER BY table_id, position
"#,
&uuids(table_ids),
)
.fetch_all(&self.pool)
.await?;
rows.into_iter()
.map(|row| {
Ok(Column {
protections: row
.protections
.iter()
.map(|value| value.parse())
.collect::<Result<Vec<_>, _>>()
.map_err(|_| {
sqlx::Error::Decode("unknown database column protection".into())
})?,
id: ColumnId::from_uuid(row.id),
table_id: TableId::from_uuid(row.table_id),
property_definition_id: row.property_definition_id,
position: row.position.parse()?,
config: row.config.map(serde_json::from_value).transpose()?,
display_name: row.display_name,
infer_type: row.infer_type,
nullable: row.nullable,
})
})
.collect()
}
#[tracing::instrument(err, skip(self))]
async fn change(
&self,
database_id: DatabaseId,
change: crate::domain::models::ChangeId,
) -> Result<Option<crate::domain::journal::ChangeRecord>, Self::Error> {
let mut connection = self.pool.acquire().await?;
journal::change(&mut connection, database_id, change).await
}
#[tracing::instrument(err, skip(self))]
async fn changes_after(
&self,
table_id: TableId,
version: TableVersion,
) -> Result<Vec<crate::domain::journal::ChangeRecord>, Self::Error> {
let mut connection = self.pool.acquire().await?;
journal::changes_after(&mut connection, table_id, version).await
}
#[tracing::instrument(err, skip(self))]
async fn touches_after(
&self,
table_id: TableId,
version: TableVersion,
) -> Result<Vec<crate::domain::journal::VersionTouches>, Self::Error> {
let mut connection = self.pool.acquire().await?;
journal::touches_after(&mut connection, table_id, version).await
}
#[tracing::instrument(err, skip(self))]
async fn row_history(
&self,
database_id: DatabaseId,
table_id: TableId,
row_id: RowId,
) -> Result<Vec<crate::domain::journal::JournaledRowChange>, Self::Error> {
journal::row_history(&self.pool, database_id, table_id, row_id).await
}
#[tracing::instrument(err, skip(self))]
async fn table_versions(
&self,
table_ids: &[TableId],
) -> Result<HashMap<TableId, TableVersion>, Self::Error> {
let versions = sqlx::query!(
r#"SELECT id, version FROM database_tables WHERE id = ANY($1)"#,
&uuids(table_ids)
)
.fetch_all(&self.pool)
.await?
.into_iter()
.map(|row| (TableId::from_uuid(row.id), TableVersion(row.version)))
.collect();
Ok(versions)
}
}