Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

8.2 KB
Raw Blame

A small SQL subset, compiled by Macro. Row queries run through the shared SQL engine; schema commands run on the server through the same database service and atomic ops as the UI.

  • Reads: SELECT [DISTINCT] items FROM [database.]table [alias] {[LEFT] JOIN [database.]table [alias] ON a.col = b.col [AND ...]} [WHERE cond] [GROUP BY col] [ORDER BY col|alias|position [ASC|DESC], ...] [LIMIT n [OFFSET m]]. Items are *, columns, or COUNT(*), COUNT(col), SUM(col), AVG(col), MIN(col), MAX(col), each optionally named with AS name; the alias names the result column and can be ordered by. GROUP BY takes one column, and a grouped SELECT lists only that column beside its aggregates. No other expressions or functions, no HAVING, and WHERE compares a column only with a literal.
  • Joins are how tables combine:
    • What joins: each ON pairs a column of the newly joined table with one of a table already in the query, by = (HAS means the same), several pairs joined by AND. Both sides are the same kind: text with text (exact and case-sensitive), number with number, date with date; a relation, person or other entity column with row_id or another entity column, such as macro.people.id. Select columns match only when they share options. Tables may come from different databases, a table may join itself under another alias, and joins chain: SELECT t."Name", d."Name", p.email FROM "Ops"."Tasks" t JOIN "Sales"."Deals" d ON t."Deal" = d.row_id JOIN macro.people p ON d."Owner" = p.id.
    • What comes back: JOIN keeps only rows with a match; LEFT JOIN keeps every row of the earlier tables, with NULL in the joined table's columns where nothing matched. A multi-valued relation or person cell matches once per value, so a task with two assignees gives two rows, and an empty cell matches nothing. Aggregates count the joined rows: SELECT p.name AS person, COUNT(*) AS tasks FROM "Tasks" t JOIN macro.people p ON t."Assignees" = p.id GROUP BY p.name ORDER BY tasks DESC. To filter on a joined table's columns, use JOIN rather than LEFT JOIN.
    • Refused: any ON test but = or HAS, OR in ON, an ON within one table, RIGHT, FULL and comma joins.
  • Row cap: each table a query reads stops at 20,000 rows and is then listed in truncatedTables, so any total over it is partial. Only =, IN and HAS tests on select, person, entity and relation columns (not row_id) narrow what a table reads; other conditions apply to the rows read. A joined table reads only the rows matching the values the earlier rows join on, up to 100 distinct values; past that it reads whole.
  • No subqueries (IN (SELECT ...)): SELECT the ids first, then use them as literals (WHERE row_id IN ('<id>', '<id>')), or JOIN.
  • Conditions: col = | != | < | <= | > | >= literal, col [NOT] IN ('a', 'b'), col [NOT] LIKE '%pat%' (case-insensitive), col IS [NOT] NULL, col [NOT] HAS 'x' (membership in a multi-valued column), combined with AND, OR and parentheses.
  • Literals: 'text' (a quote inside is doubled: 'Wolf''s place'), numbers, TRUE/FALSE, NULL; dates are '2026-08-13' or an ISO date-time.
  • Date ranges: a date_range cell is written as an ISO interval, '2026-08-13/2026-08-20' (one date is a one-day range), and compares to a date: = contains it, != does not, < ends before it, > starts after it, <= starts on or before it, >= ends on or after it. Ranges overlapping August: WHERE "Span" >= '2026-08-01' AND "Span" <= '2026-08-31'. Sorting orders by start, then end.
  • Writes: INSERT INTO table (col, ...) VALUES (...), (...) or INSERT INTO table DEFAULT VALUES; UPDATE table SET col = value, ... WHERE cond; DELETE FROM table WHERE cond. The WHERE is required and takes any condition; WHERE row_id = '<id>' or row_id IN ('<id>', ...) names rows, and every id named must exist: read the ids first. SET col = other_col copies each row's own value of a column of the same kind. A multi-valued cell is written as a list, ['Urgent', 'Backend']; NULL clears a cell.
  • row_id is every row's id. A row-shaped SELECT returns each result row's in rowIds, and an INSERT the new rows' in insertedRowIds; never invent one. A row's title is its first column, and a row the app shows as "Unnamed" has a NULL title: find it with WHERE "Name" IS NULL.
  • Names are display names. Double-quote a table or column name with spaces or punctuation (FROM "Guest List" WHERE "Due Date" < '2026-09-01'); names match case-insensitively, and a miss suggests the closest name. A table may be qualified by its database's name (FROM "Offsite"."Guests").
  • Select and tag columns take option labels ("Status" = 'Going'), never option ids. Only labels the column carries are accepted; add new ones with ALTER TABLE table ALTER COLUMN col ADD OPTIONS ('New label').
  • Relations hold the row ids of another table. Write a list of row ids ("Guests" = ['<row id>']), test with HAS '<row id>', and join with ON i."Guest" = g.row_id: SELECT p."Name" AS party, COUNT(*) AS invites FROM "Invites" i JOIN "Parties" p ON i."Party" = p.row_id GROUP BY p."Name". Never compare a relation to a name.
  • Person and other entity columns hold Macro ids, never names: 'macro|[email protected]' for a person, a list for a multi-valued column. Respect each column's specificEntityType.
  • People: macro.people is everyone the user knows (contacts and teammates) with id, name and email, the viewer included: “me” is the row whose email is the signed-in user's. It is read-only. Find people there, then write their ids: SELECT id, name, email FROM macro.people WHERE name LIKE '%julia%' OR email LIKE '%julia%', then UPDATE "Parties" SET "Host" = 'macro|[email protected]' WHERE row_id = '<id>'. Join to read names or emails: JOIN macro.people p ON t."Owner" = p.id. Use it, not ListTeamMembers, to find people for a database. Never invent a person or an id; when a name matches several people or none, ask.
  • Changing a column's type: ALTER TABLE table ALTER COLUMN col TYPE type, where type is text, number, boolean, date, date_range, link, select, select_number, tag, or entity(USER), entity(DOCUMENT), entity(TASK)…, with [] for several values (select[]). Call DescribeDatabase with includeEditingMetadata: true for the column's safeTypes and checkedTypes. A value that does not fit, or a multi-valued cell a single-valued type would truncate, refuses the statement with counts and examples: fix those values with UPDATE, or add a new column.
  • Schema commands (one statement per call; edit access required):
    • CREATE DATABASE "Sales CRM" creates its starter table with a Name column. Call DescribeDatabase using the returned database ID before editing it; reuse that starter table.
    • ALTER DATABASE "Sales CRM" RENAME TO "Sales" and ALTER DATABASE "Sales" REORDER TABLES (Deals, Companies).
    • CREATE TABLE "Sales".Deals (Name text, Value number, Stage select OPTIONS ('Lead', 'Won'), Owner entity(USER), Company relation(Companies)). Qualify the database or pass databaseId. Requires at least one column; the first is the row title. All columns and options are created atomically.
    • ALTER TABLE Deals RENAME TO Opportunities; DROP TABLE Deals deletes its rows, columns and views, subject to the domain's last-table and dependency protections.
    • ALTER TABLE Deals ADD COLUMN "Close Date" date; ALTER TABLE Deals RENAME COLUMN Value TO Amount; ALTER TABLE Deals DROP COLUMN Amount (deletes its values).
    • ALTER TABLE Deals ALTER COLUMN Stage ADD OPTIONS ('Review', 'Lost'); ALTER TABLE Deals REORDER COLUMNS (Name, Value, Stage) (name every column).
    • relation([database.]table) is a multi-valued reference to that table's rows, accepted when adding or changing a column type.
    • Schema writes return a committed summary and database ID. Read DescribeDatabase for new IDs and names. Never repeat a CREATE just because a later schema read failed. No DROP DATABASE, IF EXISTS, constraints, or multi-statement batches.
  • Saved views: SaveDatabaseView and DeleteDatabaseView manage table/kanban presentation. A board groups by a single-select or single-person column; SaveDatabaseQuery saves live SQL results and charts.
  • Tables you only hold view access on are read-only.