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
//! The SQL tools over the adapter, over fakes of the services it runs on.
use std::collections::HashMap;
use chrono::{TimeZone, Utc};
use databases::domain::models::{
Column, ColumnDetail, Database, DatabaseDetail, Table, TableDetail, TableVersion,
};
use models_properties::service::property_definition::PropertyDefinition;
use models_properties::service::property_definition_with_options::PropertyDefinitionWithOptions;
use models_properties::service::property_option::{PropertyOption, PropertyOptionValue};
use models_properties::shared::PropertyOwner;
use std::sync::{Arc, Mutex};
use ai_toolset::schema::generate_validated_input_schema;
use ai_toolset::{AsyncTool, RequestContext, ServiceContext};
use entity_access::domain::models::AccessLevel;
use models_databases::position::Position;
use models_databases::{
CellValue, CellWrite, ColumnId, ColumnResult, DatabaseId, DatabaseOp, OpResult, OptionId,
OptionRef, RowChanges, RowId, RowsChange, RowsResult, TableId,
};
use models_properties::service::property_value::PropertyValue;
use models_properties::shared::DataType;
use uuid::Uuid;
use super::*;
use crate::test_support::{
AppliedOps, FakeAccess, FakeContacts, FakeDatabases, FakeSoup, OWNER, Shared, StoredRow,
VIEWER, World, sql, user,
};
mod saved_queries;
const OFFSITE: DatabaseId = DatabaseId::from_uuid(Uuid::from_u128(0xdb01));
const GUESTS: TableId = TableId::from_uuid(Uuid::from_u128(0x7a01));
const NAME: Uuid = Uuid::from_u128(0xc001);
const STATUS: Uuid = Uuid::from_u128(0xc002);
const STATUS_COLUMN: ColumnId = ColumnId::from_uuid(Uuid::from_u128(0xb002));
const GOING: OptionId = OptionId::from_uuid(Uuid::from_u128(0xa001));
const MAYBE: OptionId = OptionId::from_uuid(Uuid::from_u128(0xa002));
const MARIA: RowId = RowId::from_uuid(Uuid::from_u128(0xe001));
/// `Offsite.Guests` with one guest, the owner's; the viewer can read it.
fn world() -> Shared {
Arc::new(Mutex::new(World {
databases: vec![DatabaseDetail {
database: Database {
id: OFFSITE,
name: "Offsite".into(),
owner_id: OWNER.to_string(),
created_at: Utc::now(),
trashed_at: None,
project_id: None,
},
grant: AccessLevel::Owner,
tables: vec![TableDetail {
table: Table {
id: GUESTS,
database_id: OFFSITE,
name: "Guests".into(),
position: "80".parse::<Position>().unwrap(),
version: TableVersion(1),
},
sql_name: "\"Guests\"".into(),
columns: vec![
ColumnDetail {
column: Column {
protections: vec![],
nullable: true,
id: ColumnId::from_uuid(Uuid::from_u128(0xb001)),
table_id: GUESTS,
property_definition_id: NAME,
position: "80".parse::<Position>().unwrap(),
config: None,
display_name: None,
infer_type: false,
},
sql_name: "\"Name\"".into(),
definition: PropertyDefinitionWithOptions {
definition: PropertyDefinition {
id: NAME,
owner: PropertyOwner::System,
display_name: "Name".into(),
data_type: DataType::String,
is_multi_select: false,
specific_entity_type: None,
created_at: Utc::now(),
updated_at: Utc::now(),
is_system: false,
is_metadata: false,
},
property_options: Vec::new(),
},
writable: true,
shared_outside_database: true,
},
ColumnDetail {
column: Column {
protections: vec![],
nullable: true,
id: STATUS_COLUMN,
table_id: GUESTS,
property_definition_id: STATUS,
position: "8180".parse::<Position>().unwrap(),
config: None,
display_name: None,
infer_type: false,
},
sql_name: "\"Status\"".into(),
definition: PropertyDefinitionWithOptions {
definition: PropertyDefinition {
id: STATUS,
owner: PropertyOwner::System,
display_name: "Status".into(),
data_type: DataType::SelectString,
is_multi_select: false,
specific_entity_type: None,
created_at: Utc::now(),
updated_at: Utc::now(),
is_system: false,
is_metadata: false,
},
property_options: vec![
PropertyOption {
id: GOING.into_uuid(),
property_definition_id: STATUS,
display_order: 0,
value: PropertyOptionValue::String("Going".into()),
color: None,
created_at: Utc::now(),
updated_at: Utc::now(),
},
PropertyOption {
id: MAYBE.into_uuid(),
property_definition_id: STATUS,
display_order: 1,
value: PropertyOptionValue::String("Maybe".into()),
color: None,
created_at: Utc::now(),
updated_at: Utc::now(),
},
],
},
writable: true,
shared_outside_database: true,
},
],
views: Vec::new(),
}],
}],
grants: vec![
(OWNER, OFFSITE, AccessLevel::Owner),
(VIEWER, OFFSITE, AccessLevel::View),
],
rows: vec![StoredRow {
id: MARIA.into_uuid(),
table_id: GUESTS,
database_id: OFFSITE,
position: "80".to_string(),
created_at: Utc.with_ymd_and_hms(2026, 9, 1, 9, 1, 0).unwrap(),
cells: vec![
(NAME, PropertyValue::Str("Maria".into())),
(STATUS, PropertyValue::SelectOption(vec![MAYBE.into_uuid()])),
],
}],
..World::default()
}))
}
#[test]
fn every_tool_schema_is_valid() {
for (name, validated) in [
(
"QueryDatabase",
generate_validated_input_schema::<QueryDatabase>(),
),
(
"SaveDatabaseQuery",
generate_validated_input_schema::<SaveDatabaseQuery>(),
),
] {
assert_eq!(validated.expect("schema should validate").name, name);
}
}
/// The dialect note is the whole reason a model can write correct SQL on the
/// first try, so it has to actually reach the description.
#[test]
fn query_schema_teaches_the_dialect() {
let validated =
generate_validated_input_schema::<QueryDatabase>().expect("schema should validate");
for expected in ["row_id", "HAS", "DescribeDatabase", "macro.people"] {
assert!(
validated.description.contains(expected),
"description is missing {expected}: {}",
validated.description
);
}
}
/// Every tool has to survive being put in a collection — that is where name
/// conflicts and schema rejections actually surface.
#[test]
fn the_toolsets_build_with_their_tools() {
let toolset = databases_sql_toolset::<FakeDatabases, FakeAccess, FakeSoup, FakeContacts>();
assert_eq!(toolset.tools.len(), 2);
for name in ["QueryDatabase", "SaveDatabaseQuery"] {
assert!(toolset.tools.contains_key(name), "missing {name}");
}
assert!(toolset.user_tools.is_empty());
}
#[tokio::test]
async fn a_read_answers_typed_cells_row_ids_and_the_versions_it_read() {
let world = world();
let response = QueryDatabase {
sql: "SELECT \"Name\", \"Status\" FROM \"Guests\"".into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(VIEWER)),
)
.await
.expect("the viewer reads");
assert_eq!(
serde_json::to_value(&response).unwrap(),
serde_json::json!({
"results": [{
"sourceTable": GUESTS,
"columns": [
{"name": "Name", "kind": "text"},
{
"name": "Status",
"kind": "select",
"options": [
{"id": GOING, "label": "Going"},
{"id": MAYBE, "label": "Maybe"},
],
},
],
"rows": [[
{"type": "text", "value": "Maria"},
{"type": "options", "value": [MAYBE]},
]],
"rowIds": [MARIA],
}],
"changesApplied": 0,
"readVersions": [{"tableId": GUESTS, "version": 1}],
"statement": {"kind": "select"},
"summary": "Returned 1 row.",
})
);
}
#[tokio::test]
async fn a_row_id_answers_as_a_row_of_the_table_read_not_an_entity() {
let world = world();
let response = QueryDatabase {
sql: "SELECT row_id, \"Name\" FROM \"Guests\"".into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(VIEWER)),
)
.await
.expect("the viewer reads");
assert_eq!(
serde_json::to_value(&response).unwrap()["results"],
serde_json::json!([{
"sourceTable": GUESTS,
"columns": [
{"name": "row_id", "kind": "row", "relatedTable": GUESTS},
{"name": "Name", "kind": "text"},
],
"rows": [[
{"type": "row", "value": MARIA},
{"type": "text", "value": "Maria"},
]],
"rowIds": [MARIA],
}])
);
}
#[tokio::test]
async fn a_write_runs_as_the_agent_for_the_user_and_guards_its_base_versions() {
let world = world();
world
.lock()
.unwrap()
.op_answers
.push_back(Ok(vec![OpResult::Rows {
table: GUESTS,
table_version: TableVersion(2),
change: RowsResult::Updated { affected: 1 },
}]));
let request: QueryDatabase = serde_json::from_value(serde_json::json!({
"sql": "UPDATE \"Guests\" SET \"Status\" = 'Going' WHERE \"Name\" = 'Maria'",
"databaseId": OFFSITE,
"baseVersions": [{"tableId": GUESTS, "version": 1}],
}))
.unwrap();
let response = request
.call(
ServiceContext(
DatabasesSqlToolContext::new(sql(&world)).with_actor(bot_id::MACRO_AI_BOT_ID),
),
RequestContext::new(user(OWNER)),
)
.await
.expect("the owner writes");
assert_eq!(response.changes_applied, 1);
assert_eq!(response.new_versions, HashMap::from([(GUESTS, 2)]));
assert_eq!(response.summary, "Applied 1 row change.");
assert_eq!(
world.lock().unwrap().applied,
vec![AppliedOps {
database: OFFSITE,
level: AccessLevel::Owner,
acting_bot: Some(bot_id::MACRO_AI_BOT_ID),
ops: vec![DatabaseOp::Rows {
table: GUESTS,
change: RowsChange::Update {
changes: RowChanges::Uniform {
rows: vec![MARIA],
cells: vec![CellWrite {
column: STATUS_COLUMN,
value: CellValue::Options(vec![OptionRef::Label("Going".into())]),
}],
}
},
}],
}]
);
let stale: QueryDatabase = serde_json::from_value(serde_json::json!({
"sql": "UPDATE \"Guests\" SET \"Status\" = 'Going' WHERE \"Name\" = 'Maria'",
"baseVersions": [{"tableId": GUESTS, "version": 0}],
}))
.unwrap();
let error = stale
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(OWNER)),
)
.await
.expect_err("version 0 is stale");
assert!(
error.description.contains("changed underneath"),
"{}",
error.description
);
}
/// The compiler's message is the product's broken-query state: it is what lets
/// a model fix the name and retry, so it has to arrive verbatim.
#[tokio::test]
async fn a_sql_error_reaches_the_model_verbatim() {
let world = world();
let error = QueryDatabase {
sql: "SELECT statuz FROM \"Guests\"".into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(OWNER)),
)
.await
.expect_err("a bad statement is an error");
assert!(
error.description.contains("statuz"),
"{}",
error.description
);
assert!(
error.description.contains("DescribeDatabase"),
"the error should say how to recover: {}",
error.description
);
}
#[tokio::test]
async fn a_view_grant_reads_as_read_only() {
let world = world();
let error = QueryDatabase {
sql: "DELETE FROM \"Guests\" WHERE \"Name\" = 'Maria'".into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(VIEWER)),
)
.await
.expect_err("a view grant does not write");
assert!(
error.description.contains("edit access"),
"{}",
error.description
);
assert!(world.lock().unwrap().applied.is_empty());
}
#[tokio::test]
async fn over_view_only_access_the_tool_refuses_an_owners_write_and_still_reads() {
let world = world();
let error = QueryDatabase {
sql: "DELETE FROM \"Guests\" WHERE \"Name\" = 'Maria'".into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world)).view_only()),
RequestContext::new(user(OWNER)),
)
.await
.expect_err("view-only access never writes");
assert_eq!(
error.description,
"table Guests is read-only. Writes need edit access to the table's database."
);
assert!(world.lock().unwrap().applied.is_empty());
let response = QueryDatabase {
sql: "SELECT COUNT(*) AS guests FROM \"Offsite\".\"Guests\"".into(),
database_id: None,
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world)).view_only()),
RequestContext::new(user(OWNER)),
)
.await
.expect("view-only access reads");
assert_eq!(
response.results[0].rows,
vec![vec![Some(database_sql::fold::Cell::Number(1.0))]]
);
}
#[test]
fn query_response_schema_accepts_omitted_empty_metadata() {
let schema = serde_json::to_value(schemars::schema_for!(QueryDatabaseResponse)).unwrap();
let required = schema["required"].as_array().unwrap();
for omitted in ["insertedRowIds", "newVersions", "truncatedTables"] {
assert!(
!required.contains(&serde_json::json!(omitted)),
"{omitted} is omitted by serialization and must be optional in the frontend schema"
);
}
assert!(required.contains(&serde_json::json!("results")));
}
#[test]
fn query_display_is_optional_and_only_accepts_supported_views() {
let query: QueryDatabase =
serde_json::from_value(serde_json::json!({"sql": "SELECT 1"})).unwrap();
assert!(query.display.is_none());
for (display, expected) in [
("bar", QueryDatabaseDisplay::Bar),
("area", QueryDatabaseDisplay::Area),
("scatter", QueryDatabaseDisplay::Scatter),
] {
let chart: QueryDatabase =
serde_json::from_value(serde_json::json!({"sql": "SELECT 1", "display": display}))
.unwrap();
assert_eq!(chart.display, Some(expected));
}
assert!(
serde_json::from_value::<QueryDatabase>(
serde_json::json!({"sql": "SELECT 1", "display": "unsupported"})
)
.is_err()
);
}
/// A refused write and a table that moved mid-write are not naming
/// mistakes, so the model is not sent to look names up again.
#[tokio::test]
async fn a_refused_write_says_what_was_refused_without_name_advice() {
let world = world();
world.lock().unwrap().op_answers.extend([
Err(databases::domain::models::DatabaseError::InvalidOp(
databases::domain::models::OpRefusal {
op: 0,
row: None,
column: Some(STATUS_COLUMN),
taken: None,
reason: "\"Status\" holds one value; 2 were given".into(),
},
)),
Err(databases::domain::models::DatabaseError::VersionConflict),
]);
let update = || QueryDatabase {
sql: "UPDATE \"Guests\" SET \"Status\" = 'Going' WHERE \"Name\" = 'Maria'".into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
};
let refused = update()
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(OWNER)),
)
.await
.expect_err("the service refuses the write");
assert_eq!(
refused.description,
"The write was refused, so nothing changed: \"Status\" holds one value; 2 were given. \
Fix the statement and retry."
);
let conflicted = update()
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(OWNER)),
)
.await
.expect_err("the table moved");
assert_eq!(
conflicted.description,
format!("Table {GUESTS} changed underneath this statement. Re-read it and retry.")
);
}
/// Each kind of statement names what it wrote, so the chat need not read
/// the SQL to know.
#[tokio::test]
async fn every_statement_kind_names_what_it_wrote() {
let world = world();
world.lock().unwrap().op_answers.extend([
Ok(vec![OpResult::Rows {
table: GUESTS,
table_version: TableVersion(2),
change: RowsResult::Inserted {
rows: vec![RowId::from_uuid(Uuid::from_u128(0xe002))],
},
}]),
Ok(vec![OpResult::Rows {
table: GUESTS,
table_version: TableVersion(3),
change: RowsResult::Updated { affected: 1 },
}]),
Ok(vec![OpResult::Rows {
table: GUESTS,
table_version: TableVersion(4),
change: RowsResult::Deleted { affected: 1 },
}]),
Ok(vec![OpResult::Column {
table: GUESTS,
column: STATUS_COLUMN,
table_version: TableVersion(5),
change: ColumnResult::TypeChanged,
}]),
]);
let statement = async |statement: &str| {
let response = QueryDatabase {
sql: statement.into(),
database_id: Some(OFFSITE),
base_versions: None,
display: None,
}
.call(
ServiceContext(DatabasesSqlToolContext::new(sql(&world))),
RequestContext::new(user(OWNER)),
)
.await
.expect("the owner runs it");
(
serde_json::to_value(&response.statement).unwrap(),
response.summary,
)
};
assert_eq!(
statement("SELECT \"Name\" FROM \"Guests\"").await,
(
serde_json::json!({"kind": "select"}),
"Returned 1 row.".into()
)
);
assert_eq!(
statement("INSERT INTO \"Guests\" (\"Name\") VALUES ('Sam')").await,
(
serde_json::json!({"kind": "insert", "tableId": GUESTS, "tableName": "Guests"}),
"Applied 1 row change.".into()
)
);
assert_eq!(
statement("UPDATE \"Guests\" SET \"Status\" = 'Going' WHERE \"Name\" = 'Maria'").await,
(
serde_json::json!({"kind": "update", "tableId": GUESTS, "tableName": "Guests"}),
"Applied 1 row change.".into()
)
);
assert_eq!(
statement("DELETE FROM \"Guests\" WHERE \"Name\" = 'Maria'").await,
(
serde_json::json!({"kind": "delete", "tableId": GUESTS, "tableName": "Guests"}),
"Applied 1 row change.".into()
)
);
assert_eq!(
statement("ALTER TABLE \"Guests\" ALTER COLUMN \"Status\" TYPE select[]").await,
(
serde_json::json!({
"kind": "alterColumnType",
"tableId": GUESTS,
"tableName": "Guests",
"columnId": STATUS_COLUMN,
"columnName": "Status",
"to": "select[]",
}),
"Changed \"Status\" to select[].".into()
)
);
}