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 store with optimistic profile revisions and atomic host reservations.
use crate::domain::{models::*, ports::Repository};
use chrono::{DateTime, Utc};
use sqlx::PgPool;
use uuid::Uuid;
/// Scheduling-owned PostgreSQL repository.
pub struct PostgresRepository {
pool: PgPool,
}
impl PostgresRepository {
/// Use the application's MacroDB pool.
pub fn new(pool: PgPool) -> Self {
Self { pool }
}
}
fn db_error(error: sqlx::Error) -> Error {
if error
.as_database_error()
.is_some_and(|e| e.is_unique_violation() || e.code().as_deref() == Some("23P01"))
{
return Error::Conflict;
}
tracing::error!(error = ?error, "scheduling persistence failed");
Error::Unavailable
}
fn decode<T: serde::de::DeserializeOwned>(value: serde_json::Value) -> Result<T, Error> {
serde_json::from_value(value).map_err(|_| Error::Unavailable)
}
fn encode<T: serde::Serialize>(value: &T) -> Result<serde_json::Value, Error> {
serde_json::to_value(value).map_err(|_| Error::Unavailable)
}
fn status(value: BookingStatus) -> &'static str {
match value {
BookingStatus::Pending => "pending",
BookingStatus::Processing => "processing",
BookingStatus::Confirmed => "confirmed",
BookingStatus::Cancelled => "cancelled",
BookingStatus::Failed => "failed",
}
}
impl Repository for PostgresRepository {
async fn consume_budget(&self, profile: Uuid, budget: PublicBudget) -> Result<(), Error> {
let (key, seconds, limit) = match budget {
PublicBudget::Availability => ("availability", 60.0_f64, 120_i32),
PublicBudget::Booking => ("booking", 3600.0_f64, 30_i32),
};
let allowed = sqlx::query_scalar!(r#"
INSERT INTO scheduling_public_budget (profile_id, kind, window_start, used)
VALUES ($1, $2, now(), 1)
ON CONFLICT (profile_id, kind) DO UPDATE SET
window_start = CASE WHEN scheduling_public_budget.window_start <= now() - make_interval(secs => $3) THEN now() ELSE scheduling_public_budget.window_start END,
used = CASE WHEN scheduling_public_budget.window_start <= now() - make_interval(secs => $3) THEN 1 ELSE scheduling_public_budget.used + 1 END
WHERE scheduling_public_budget.window_start <= now() - make_interval(secs => $3) OR scheduling_public_budget.used < $4
RETURNING used
"#, profile, key, seconds, limit).fetch_optional(&self.pool).await.map_err(db_error)?;
allowed.map(|_| ()).ok_or(Error::RateLimited)
}
async fn claim_recovery(&self) -> Result<Option<BookingRecord>, Error> {
let mut tx = self.pool.begin().await.map_err(db_error)?;
let row = sqlx::query_scalar!("SELECT record FROM scheduling_booking WHERE status IN ('processing', 'failed') AND recovery_at <= now() ORDER BY recovery_at, id FOR UPDATE SKIP LOCKED LIMIT 1")
.fetch_optional(&mut *tx).await.map_err(db_error)?;
let Some(row) = row else {
return Ok(None);
};
let mut record: BookingRecord = decode(row)?;
let operation = record.operation.as_mut().ok_or(Error::Unavailable)?;
operation.attempt = operation.attempt.saturating_add(1);
record.revision = record.revision.saturating_add(1);
operation.retry_at = Utc::now() + chrono::Duration::minutes(5);
let retry_at = operation.retry_at;
record.booking.status = BookingStatus::Processing;
let json = encode(&record)?;
sqlx::query!("UPDATE scheduling_booking SET status = 'processing', record = $2, recovery_at = $3 WHERE id = $1", record.booking.id, json, retry_at)
.execute(&mut *tx).await.map_err(db_error)?;
tx.commit().await.map_err(db_error)?;
Ok(Some(record))
}
async fn profile(&self, id: Uuid) -> Result<Option<OwnedProfile>, Error> {
let row = sqlx::query!("SELECT user_id, team_id, revision, configuration FROM scheduling_profile WHERE id = $1", id).fetch_optional(&self.pool).await.map_err(db_error)?;
row.map(|r| {
let mut profile: Profile = decode(r.configuration)?;
profile.revision = r.revision;
Ok(OwnedProfile {
user_id: r.user_id,
team_id: r.team_id,
profile,
})
})
.transpose()
}
async fn save_profile(&self, owner: OwnedProfile) -> Result<Profile, Error> {
let mut profile = owner.profile;
let json = encode(&profile)?;
let revision = if profile.revision == 0 {
sqlx::query_scalar!("INSERT INTO scheduling_profile (id, user_id, team_id, configuration) VALUES ($1, $2, $3, $4) RETURNING revision", profile.id, owner.user_id, owner.team_id, json).fetch_one(&self.pool).await.map_err(db_error)?
} else {
sqlx::query_scalar!("UPDATE scheduling_profile SET configuration = $2, revision = revision + 1 WHERE id = $1 AND revision = $3 RETURNING revision", profile.id, json, profile.revision).fetch_optional(&self.pool).await.map_err(db_error)?.ok_or(Error::Conflict)?
};
profile.revision = revision;
Ok(profile)
}
async fn bookings(
&self,
profile: Uuid,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<Vec<BookingRecord>, Error> {
let records = sqlx::query_scalar!("SELECT record FROM scheduling_booking WHERE profile_id = $1 AND starts_at >= $2 AND starts_at < $3 ORDER BY starts_at, id LIMIT 5001", profile, start, end).fetch_all(&self.pool).await.map_err(db_error)?;
if records.len() > 5000 {
return Err(Error::Invalid(
"Too many bookings in this date range. Choose a shorter date range.".into(),
));
}
records.into_iter().map(decode).collect()
}
async fn host_booking_counts(
&self,
profile: Uuid,
hosts: &[String],
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<std::collections::BTreeMap<String, i64>, Error> {
let rows = sqlx::query!(
r#"SELECT host AS "host!", count(*) AS "count!"
FROM scheduling_booking
CROSS JOIN LATERAL jsonb_array_elements_text(record->'booking'->'hosts') AS host
WHERE profile_id = $1 AND starts_at >= $2 AND starts_at < $3
AND status <> 'cancelled' AND host = ANY($4)
GROUP BY host"#,
profile,
start,
end,
hosts
)
.fetch_all(&self.pool)
.await
.map_err(db_error)?;
Ok(rows.into_iter().map(|row| (row.host, row.count)).collect())
}
async fn busy(
&self,
hosts: &[String],
start: DateTime<Utc>,
end: DateTime<Utc>,
exclude: Option<Uuid>,
) -> Result<Vec<BusyRange>, Error> {
Ok(sqlx::query!(r#"SELECT user_id, lower(piece) AS "start!", upper(piece) AS "end!" FROM scheduling_host_claim CROSS JOIN LATERAL unnest(occupied) AS piece WHERE user_id = ANY($1) AND piece && tstzrange($2, $3, '[)') AND ($4::uuid IS NULL OR booking_id <> $4)"#, hosts, start, end, exclude).fetch_all(&self.pool).await.map_err(db_error)?.into_iter().map(|r| BusyRange { host: r.user_id, start: r.start, end: r.end }).collect())
}
async fn reserve(
&self,
record: BookingRecord,
limit: Option<u16>,
day_start: DateTime<Utc>,
day_end: DateTime<Utc>,
) -> Result<BookingRecord, Error> {
let mut tx = self.pool.begin().await.map_err(db_error)?;
// Serialize per-profile limits; the exclusion constraint also protects hosts across profiles.
sqlx::query!(
"SELECT id FROM scheduling_profile WHERE id = $1 FOR UPDATE",
record.profile_id
)
.fetch_one(&mut *tx)
.await
.map_err(db_error)?;
if let Some(existing) = sqlx::query_scalar!(
"SELECT record FROM scheduling_booking WHERE request_id = $1",
record.request_id
)
.fetch_optional(&mut *tx)
.await
.map_err(db_error)?
{
let existing: BookingRecord = decode(existing)?;
if existing.profile_id == record.profile_id
&& existing.booking.email == record.booking.email
&& existing.booking.starts_at == record.booking.starts_at
&& existing.booking.event_type_id == record.booking.event_type_id
{
return Ok(existing);
}
return Err(Error::Conflict);
}
if let Some(limit) = limit {
let count = sqlx::query_scalar!("SELECT count(*) FROM scheduling_booking WHERE profile_id = $1 AND event_type_id = $2 AND starts_at >= $3 AND starts_at < $4 AND status <> 'cancelled'", record.profile_id, record.booking.event_type_id, day_start, day_end).fetch_one(&mut *tx).await.map_err(db_error)?.unwrap_or(0);
if count >= i64::from(limit) {
return Err(Error::Conflict);
}
}
let json = encode(&record)?;
sqlx::query!("INSERT INTO scheduling_booking (id, profile_id, request_id, event_type_id, starts_at, ends_at, status, record, recovery_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)", record.booking.id, record.profile_id, record.request_id, record.booking.event_type_id, record.booking.starts_at, record.booking.ends_at, status(record.booking.status), json, record.operation.as_ref().map(|o| o.retry_at)).execute(&mut *tx).await.map_err(db_error)?;
let mut hosts: Vec<_> = record.booking.hosts.iter().collect();
hosts.sort();
for host in hosts {
sqlx::query!("INSERT INTO scheduling_host_claim (booking_id, user_id, occupied) VALUES ($1,$2,tstzmultirange(tstzrange($3,$4,'[)')))", record.booking.id, host, record.busy_start, record.busy_end).execute(&mut *tx).await.map_err(db_error)?;
}
tx.commit().await.map_err(db_error)?;
Ok(record)
}
async fn booking(&self, id: Uuid) -> Result<Option<BookingRecord>, Error> {
sqlx::query_scalar!("SELECT record FROM scheduling_booking WHERE id = $1", id)
.fetch_optional(&self.pool)
.await
.map_err(db_error)?
.map(decode)
.transpose()
}
async fn booking_request(&self, request: Uuid) -> Result<Option<BookingRecord>, Error> {
sqlx::query_scalar!(
"SELECT record FROM scheduling_booking WHERE request_id = $1",
request
)
.fetch_optional(&self.pool)
.await
.map_err(db_error)?
.map(decode)
.transpose()
}
async fn move_booking(
&self,
mut record: BookingRecord,
expected: BookingStatus,
start: DateTime<Utc>,
end: DateTime<Utc>,
) -> Result<BookingRecord, Error> {
let mut tx = self.pool.begin().await.map_err(db_error)?;
sqlx::query!(
"SELECT id FROM scheduling_profile WHERE id = $1 FOR UPDATE",
record.profile_id
)
.fetch_one(&mut *tx)
.await
.map_err(db_error)?;
if let Some(limit) = record.event.daily_limit {
let count = sqlx::query_scalar!("SELECT count(*) FROM scheduling_booking WHERE profile_id = $1 AND event_type_id = $2 AND starts_at >= $3 AND starts_at < $4 AND status <> 'cancelled' AND id <> $5", record.profile_id, record.booking.event_type_id, start, end, record.booking.id).fetch_one(&mut *tx).await.map_err(db_error)?.unwrap_or(0);
if count >= i64::from(limit) {
return Err(Error::Conflict);
}
}
let expected_revision = record.revision.to_string();
record.revision = record.revision.saturating_add(1);
let json = encode(&record)?;
let updated = sqlx::query!("UPDATE scheduling_booking SET starts_at = $2, ends_at = $3, status = 'processing', record = $4, recovery_at = $6 WHERE id = $1 AND status = $5 AND COALESCE(record->>'revision', '0') = $7", record.booking.id, record.booking.starts_at, record.booking.ends_at, json, status(expected), record.operation.as_ref().map(|o| o.retry_at), expected_revision).execute(&mut *tx).await.map_err(db_error)?;
if updated.rows_affected() != 1 {
return Err(Error::Conflict);
}
sqlx::query!("UPDATE scheduling_host_claim SET occupied = occupied + tstzmultirange(tstzrange($2,$3,'[)')) WHERE booking_id = $1", record.booking.id, record.busy_start, record.busy_end).execute(&mut *tx).await.map_err(db_error)?;
tx.commit().await.map_err(db_error)?;
Ok(record)
}
async fn update_booking(
&self,
mut record: BookingRecord,
expected: BookingStatus,
) -> Result<BookingRecord, Error> {
let mut tx = self.pool.begin().await.map_err(db_error)?;
let expected_revision = record.revision.to_string();
record.revision = record.revision.saturating_add(1);
let json = encode(&record)?;
let updated = sqlx::query!(
"UPDATE scheduling_booking SET status = $2, record = $3, recovery_at = $5 WHERE id = $1 AND status = $4 AND COALESCE(record->>'revision', '0') = $8 AND ($4 <> 'processing' OR (record->'operation'->>'id' IS NOT DISTINCT FROM $6 AND record->'operation'->>'attempt' IS NOT DISTINCT FROM $7))",
record.booking.id,
status(record.booking.status),
json,
status(expected),
if matches!(record.booking.status, BookingStatus::Processing | BookingStatus::Failed) { record.operation.as_ref().map(|o| o.retry_at) } else { None },
record.operation.as_ref().map(|o| o.id.to_string()),
record.operation.as_ref().map(|o| o.attempt.to_string()),
expected_revision
)
.execute(&mut *tx)
.await
.map_err(db_error)?;
if updated.rows_affected() != 1 {
return Err(Error::Conflict);
}
if record.booking.status == BookingStatus::Cancelled {
sqlx::query!(
"DELETE FROM scheduling_host_claim WHERE booking_id = $1",
record.booking.id
)
.execute(&mut *tx)
.await
.map_err(db_error)?;
}
if matches!(
record.booking.status,
BookingStatus::Confirmed | BookingStatus::Pending
) {
sqlx::query!("UPDATE scheduling_host_claim SET occupied = tstzmultirange(tstzrange($2,$3,'[)')) WHERE booking_id = $1", record.booking.id, record.busy_start, record.busy_end).execute(&mut *tx).await.map_err(db_error)?;
}
tx.commit().await.map_err(db_error)?;
Ok(record)
}
}
#[cfg(test)]
mod test;