Databases API

Databases are typed, queryable collections of records. In AI Software Factory every record is also a page (database_records.pageId is 1:1 with pages.id): each row opens a full page with its own blocks, while its property values live in the database. Databases are embedded in pages via database / linked_database / child_database blocks or exist standalone in the workspace.

Shared conventions (auth, error model, pagination, rate limits): authentication.md.

Permission note: reading a database requires VIEW; creating/updating records and the schema requires EDIT; archiving a database requires FULL_ACCESS (or workspace Admin/Owner). All checks are server-side and inherit the workspace → team → page chain.

Scale note: databases are designed for 100,000+ records. List and query endpoints are always cursor-paginated; the engine evaluates filter/sort/group/aggregates server-side, so clients never fetch a whole database.

---

1. Database Resource

{
  "id": "db_01J9ZKQ7E4F6A8B0C2D4E6F8A0",
  "workspaceId": "wks_01J9ZKP5C9H7J9K1L3M5N7P9Q1",
  "pageId": "pg_01J9ZKQ4F8H5I7J9L1M3N5P7Q9",
  "title": "Projects",
  "icon": "📊",
  "description": "All projects, owners, status, and budgets.",
  "schemaVersion": 4,
  "isArchived": false,
  "recordCount": 128,
  "createdBy": "usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A",
  "createdAt": "2025-01-02T09:20:00.000Z",
  "updatedAt": "2025-01-02T16:02:00.000Z"
}

---

2. Create a Database

POST /databases

• Auth: JWT or API key (scope WRITE).

Request:

{
  "title": "Projects",
  "icon": "📊",
  "description": "All projects, owners, status, and budgets.",
  "pageId": "pg_01J9ZKQ4F8H5I7J9L1M3N5P7Q9",
  "blockId": "blk_01J9ZKQ6D3E5F7A9B1C3D5E7F9",
  "parentId": null,
  "properties": [
    { "name": "Name", "type": "title" },
    { "name": "Status", "type": "select", "config": { "options": [
        { "id": "opt_01J9ZKQ8A1B3C5D7E9F1A3B5C7", "name": "Not Started", "color": "gray" },
        { "id": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8", "name": "In Progress", "color": "blue" },
        { "id": "opt_01J9ZKQ8C3D5E7F9A1B3C5D7E9", "name": "Done", "color": "green" } ] } },
    { "name": "Owner", "type": "person" },
    { "name": "Priority", "type": "select", "config": { "options": [
        { "id": "opt_01J9ZKQ8D4E6F8A0B2C4D6E8F0", "name": "High", "color": "red" },
        { "id": "opt_01J9ZKQ8E5F7A9B1C3D5E7F9A1", "name": "Medium", "color": "yellow" },
        { "id": "opt_01J9ZKQ8F6A8B0C2D4E6F8A0B2", "name": "Low", "color": "gray" } ] } },
    { "name": "Due Date", "type": "date" },
    { "name": "Tags", "type": "multi_select", "config": { "options": [
        { "id": "opt_01J9ZKQ9A7B9C1D3E5F7A9B1C3", "name": "platform", "color": "purple" },
        { "id": "opt_01J9ZKQ9B8C0D2E4F6A8B0C2D4", "name": "customer", "color": "orange" } ] } },
    { "name": "Budget", "type": "number", "config": { "format": "currency", "currency": "USD" } }
  ],
  "views": [
    { "name": "Table view", "type": "table" },
    { "name": "Board", "type": "board", "group": [ { "propertyId": "Status", "direction": "asc" } ] }
  ]
}

Note: when referencing a property you are creating in the same request (e.g. a group by Status), use the name in propertyId — the server resolves names of properties defined in the same payload. For already- existing properties use the prop_… id.

Field	Type	Required	Notes
title	string (0–200)	no	Empty allowed.
icon, description	–	no	As for pages.
pageId	string | null	no	Page to embed the database block into (a database block is created/linked).
blockId	string | null	no	With pageId: the existing database block to bind (created by slash command); the API then fills its content.databaseId.
parentId	string | null	no	Standalone placement (parent page in the workspace tree). Mutually exclusive with pageId+blockId.
properties	array (1–200)	no	Default: a single title property named "Name". See §3 Property Types.
views	array (0–20)	no	Default: one table view. See §5 Views.

Exactly one of pageId or parentId (or neither → workspace root).

201 Created

{
  "data": {
    "id": "db_01J9ZKQ7E4F6A8B0C2D4E6F8A0",
    "workspaceId": "wks_01J9ZKP5C9H7J9K1L3M5N7P9Q1",
    "pageId": "pg_01J9ZKQ4F8H5I7J9L1M3N5P7Q9",
    "title": "Projects",
    "icon": "📊",
    "description": "All projects, owners, status, and budgets.",
    "schemaVersion": 1,
    "isArchived": false,
    "recordCount": 0,
    "properties": [
      { "id": "prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1", "name": "Name", "type": "title", "position": "a0", "isTitle": true, "isVisible": true, "required": false, "config": {} },
      { "id": "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2", "name": "Status", "type": "select", "position": "a1", "isTitle": false, "isVisible": true, "required": false, "config": { "options": [ "…as in request…" ] } }
    ],
    "views": [
      { "id": "view_01J9ZKQ7H7C9D1E3F5A7B9C1D3", "name": "Table view", "type": "table", "position": "a0", "config": {}, "filter": null, "sort": null, "group": null, "pageSize": null }
    ],
    "createdBy": "usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A",
    "createdAt": "2025-01-02T09:20:00.000Z"
  }
}

Errors: 400 VALIDATION_ERROR (bad property config, duplicate property name, no title property), 401 UNAUTHORIZED, 403 FORBIDDEN, 404 NOT_FOUND (page), 409 CONFLICT (two properties named the same after case folding).

Events emitted: database.created (+ page.updated if embedded — the database block's content.databaseId is set).

---

3. Properties

A database has an ordered list of typed properties (database_properties). Exactly one property may have isTitle: true.

3.1 GET /databases/:databaseId

• Auth: JWT or API key (scope READ). VIEW.

200 OK — database object plus properties (ordered by position) and views. Same shape as the 201 above.

Errors: 401 UNAUTHORIZED, 403 FORBIDDEN, 404 NOT_FOUND.

---

3.2 PATCH /databases/:databaseId

Update title/icon/description or archive.

• Auth: JWT or API key (scope WRITE). Schema changes require EDIT.

Request (any subset):

{
  "title": "Projects 2025",
  "icon": "🗂️",
  "archived": false
}

200 OK — updated database object.

Errors: 400 VALIDATION_ERROR, 403 FORBIDDEN, 404 NOT_FOUND, 409 CONFLICT (archived: true requires FULL_ACCESS, else 403).

Events emitted: database.updated.

---

3.3 Property CRUD

Properties are managed per-database.

POST /databases/:databaseId/properties

• Auth: JWT or API key (scope WRITE). EDIT.

Request:

{
  "name": "Start Date",
  "type": "date",
  "position": "a0",
  "isVisible": true,
  "required": false,
  "config": { "includeTime": false },
  "default": null
}

201 Created — the property object:

{
  "data": {
    "id": "prop_01J9ZKQ9C9D1E3F5A7B9C1D3E5",
    "databaseId": "db_01J9ZKQ7E4F6A8B0C2D4E6F8A0",
    "name": "Start Date",
    "type": "date",
    "position": "a0",
    "isTitle": false,
    "isVisible": true,
    "required": false,
    "config": { "includeTime": false },
    "default": null,
    "createdAt": "2025-01-02T10:00:00.000Z"
  }
}

Errors: 400 VALIDATION_ERROR (bad config for type), 403 FORBIDDEN, 404 NOT_FOUND, 409 CONFLICT (duplicate name).

Events emitted: database.updated (schema change; metadata: {change: "property_added"}).

GET /databases/:databaseId/properties

• Auth: JWT or API key (scope READ). VIEW.

200 OK — { data: [ <property objects> ] }, ordered by position. No pagination (property counts are bounded by the 200 cap).

PATCH /databases/:databaseId/properties/:propertyId

• Auth: JWT or API key (scope WRITE). EDIT.

Update name, isVisible, required, position (reorder), and config (merge-patch; for select-like types you may add or rename options — removing an option clears that value from every record that uses it, and the response reports affectedRecords). type is immutable (delete + recreate to change type); isTitle is immutable (use POST /databases/:id/properties/:propertyId/set-title to transfer title-ness, which rejects with 422 if the property is not text-compatible).

Request:

{
  "name": "Status (2025)",
  "position": "a0",
  "config": {
    "options": [
      { "id": "opt_01J9ZKQ8A1B3C5D7E9F1A3B5C7", "name": "Not Started", "color": "gray" },
      { "id": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8", "name": "In Progress", "color": "blue" },
      { "id": "opt_01J9ZKQ8C3D5E7F9A1B3C5D7E9", "name": "Done", "color": "green" },
      { "id": "opt_01J9ZKQA1B3C5D7E9F1A3B5C7", "name": "Blocked", "color": "red" }
    ]
  }
}

200 OK

{
  "data": {
    "id": "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2",
    "name": "Status (2025)",
    "type": "select",
    "position": "a0",
    "config": { "options": [ "…" ] },
    "affectedRecords": 0
  }
}

Errors: 400 VALIDATION_ERROR (unknown option id not being added, invalid formula expression → details: [{field: "config.expression", issue: "formula_parse_error", position: 14}]), 403 FORBIDDEN, 404 NOT_FOUND, 409 CONFLICT (duplicate name).

Events emitted: database.updated (change: "property_updated").

DELETE /databases/:databaseId/properties/:propertyId

• Auth: JWT or API key (scope WRITE). EDIT.
• The title property cannot be deleted (422 UNPROCESSABLE_ENTITY, details: [{issue: "cannot_delete_title_property"}]).
• Deleting a property that rollups/formulas depend on rejects with 422 listing the dependents.
• Related relation/rollup/formula definitions are cascade-removed and the response reports it.

200 OK

{ "data": { "deletedProperty": "prop_01J9ZKQ9C9D1E3F5A7B9C1D3E5", "removedRelations": 0, "removedRollups": 1, "removedFormulas": 0, "affectedRecords": 128 } }

Events emitted: database.updated (change: "property_deleted").

---

3.4 Property Types — Config Shapes

All 24 PropertyType values from prisma/schema.prisma. (The PRODUCT-SPEC §9.1 heading says "22" but enumerates the same set the schema implements; the schema is authoritative.)

default is a full value of the type (see §7 Record values), null when none.

#	type	config shape (example)	Stored value shape
1	title	{}	{ "text": "Atlas migration", "marks": [] } (rich text; exactly one per database, isTitle: true)
2	text	{}	{ "text": "One-line description", "marks": [] }
3	rich_text	{ "maxLines": null }	{ "text": "Longer **rich** body…", "marks": [ … ] }
4	number	{ "format": "number" | "percent" | "currency" | "scientific" | "decimal", "currency": "USD", "precision": 2 }	{ "value": 48250.5 }
5	select	{ "options": [ { "id", "name", "color" } ] } (colors: gray, brown, orange, yellow, green, blue, purple, pink, red, default)	{ "optionId": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8" } (single)
6	multi_select	same options array	{ "optionIds": [ "opt_…", "opt_…" ] }
7	status	{ "groups": [ { "name": "WIP", "optionIds": [ … ], "color": "blue" } ] } — options like select, grouped for board lanes	{ "optionId": "opt_…" } (single; renders in its group's color)
8	date	{ "includeTime": false, "timeZone": null }	{ "start": "2025-04-30T00:00:00.000Z", "end": null, "dateTimeTimezone": null }
9	date_range	{}	{ "start": "2025-04-01T00:00:00.000Z", "end": "2025-04-30T00:00:00.000Z" }
10	person	{}	{ "userIds": [ "usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A" ] }
11	email	{}	{ "value": "ada@example.com" }
12	phone	{ "defaultCountry": "GB" }	{ "value": "+44 20 7946 0958" }
13	url	{}	{ "value": "https://factory.example.com", "title": "Factory" }
14	checkbox	{}	{ "value": true }
15	files	{ "maxFiles": 10, "acceptedMimeTypes": [ "image/*", "application/pdf" ] }	{ "fileIds": [ "fil_01J9ZKP…", "fil_01J9ZKQ…" ] }
16	created_time	{}	read-only; { "value": "2025-01-02T09:21:00.000Z" } (server-set)
17	created_by	{}	read-only; { "userId": "usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A" }
18	last_edited_time	{}	read-only; { "value": "2025-01-02T16:02:00.000Z" }
19	last_edited_by	{}	read-only; { "userId": "usr_01J9ZKQ2V4W6X8Y0Z2A4B6C8D0" }
20	relation	{ "targetDatabaseId": "db_01J9ZKR1D2E4F6A8B0C2D4E6", "cardinality": "one_to_one" | "one_to_many" | "many_to_many", "bidirectional": true, "reverseName": "Related Projects" }	{ "recordIds": [ "rpt_01J9ZKR2E3F5A7B9C1D3E5" ] }
21	rollup	{ "relationPropertyId": "prop_…", "targetPropertyId": "prop_…", "aggregate": "COUNT" | "SUM" | "AVERAGE" | "MIN" | "MAX" | "PERCENT" | "UNIQUE", "filter": <filter tree, see §5.3> }	computed, read-only; e.g. { "value": 4 }, { "values": [ … ] } for UNIQUE
22	formula	{ "expression": "if(prop(\"Status\") == \"Done\", 100, 0)" } — parsed to an AST by the safe evaluator (never JS eval); invalid → 400 VALIDATION_ERROR	computed, read-only; { "value": 100, "type": "number" }
23	button	{ "actions": [ { "type": "set_property", "config": { "propertyId": "prop_…", "value": { "optionId": "opt_…" } } }, { "type": "send_webhook", "config": { "webhookId": "webh_…" } }, { "type": "trigger_automation", "config": { "automationId": "auto_…" } } ] }	not stored per record; null
24	unique_id	{ "prefix": "PRJ-" }	server-generated, read-only; { "value": "PRJ-000007" } (zero-padded, monotonic per database)

Read-only types (created_time, created_by, last_edited_time, last_edited_by, rollup, formula, unique_id, button) reject writes with 422 UNPROCESSABLE_ENTITY (details: [{issue: "read_only_property", property: "…"}]).

Option ids are optional on write, guaranteed on read. Creating or updating a select / multi_select / status property may omit id on one or more config.options entries (the AI generation path emits bare { name, color }). The server then assigns each id-less option a deterministic id derived from its name (slug-style, e.g. To Do → opt-to-do; NFKD-normalized, lowercased, non-alphanumerics → -) and persists the normalized config. A partial update that re-lists an existing option without its id keeps that option's id (custom ids included) so stored optionId values stay resolvable. Record values written with the option name as the optionId are canonicalized to the id at write time; values already stored as names (legacy rows) are resolved to the id at read time (record DTOs, board grouping, filter evaluation, CSV export). Clients may therefore always match a stored optionId against option.id; the server never returns a value whose reference is an unknown option name.

The generator (optionIdForName) and the config backfill (normalizeSelectOptions) live in @factory/shared as the single source of truth — the web app (which cannot depend on @factory/ai) and the API share the exact same function, so a stored name and its canonical id always resolve identically on both sides. Every read boundary normalizes: GET /databases/:id, GET /databases/:id/properties, and the record-open endpoint — the latter two fill the same 60 s column-metadata cache, so both loaders store the same normalized entry (whichever fills first wins the TTL; an id-less entry from one path would have blanked select/status cells in the other's view). The web additionally re-normalizes at its client read boundary (flattenDatabase), so even a stale or legacy option list in an open tab degrades to the name-fallback render instead of a blank cell.

---

4. Relations & Rollups (property-level)

Relations and rollups are created as properties (types 20 and 21 above); no separate endpoints. The server materializes:

• relations rows (typed, cardinality, bidirectional counterpart) when a relation property is created; the reverse property is auto-created on the target database when bidirectional: true.
• relation_links rows when records are linked (via record updates, §7.3).
• rollups rows; rollup values are recomputed asynchronously after the change that affected them (typically < 1 s; a GET right after may still return the previous value — poll the record if you need the fresh value).

Example — create a relation property:

curl -s -X POST https://factory.example.com/api/v1/databases/db_01J9ZKQ7E4F6A8B0C2D4E6F8A0/properties \
  -H "Authorization: Bearer $ACCESS_TOKEN" -H 'Content-Type: application/json' \
  -d '{
    "name": "Tasks",
    "type": "relation",
    "config": {
      "targetDatabaseId": "db_01J9ZKR1D2E4F6A8B0C2D4E6",
      "cardinality": "one_to_many",
      "bidirectional": true,
      "reverseName": "Project"
    }
  }'

---

5. Views

A view is a saved lens on the records: its own filters, sort, group, visible properties, and presentation config. View config is a JSONB free-form object (per-type fields below); filter/sort/group are top-level fields so the query engine can read them uniformly.

5.1 CRUD

Endpoint	Auth	Notes
POST /databases/:databaseId/views	WRITE	Body: { "name", "type" (table|list|board|gallery|calendar|timeline|chart|dashboard|form), "config"?, "filter"?, "sort"?, "group"?, "pageSize"?, "position"? } → 201 view object.
GET /databases/:databaseId/views	READ	{ data: [views] } by position.
PATCH /databases/:databaseId/views/:viewId	WRITE	Merge-patch any of name, config, filter, sort, group, pageSize, position. type immutable (422).
DELETE /databases/:databaseId/views/:viewId	WRITE	204. The last remaining view cannot be deleted (422, details: [{issue: "last_view"}]).

View object:

{
  "id": "view_01J9ZKQ8F5A7B9C1D3E5F7A9B1",
  "databaseId": "db_01J9ZKQ7E4F6A8B0C2D4E6F8A0",
  "name": "Board",
  "type": "board",
  "position": "a1",
  "config": {
    "visibleProperties": ["prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1", "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2"],
    "cardProperties": ["prop_01J9ZKQ7H7C9D1E3F5A7B9C1D3", "prop_01J9ZKQ7I8D0E2F4A6B8C0D2E4"],
    "columnWidth": null,
    "cardLayout": "cover",
    "color": null,
    "density": "comfortable"
  },
  "filter": {
    "op": "and",
    "conditions": [
      { "op": "or", "conditions": [
        { "propertyId": "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2", "op": "equals", "value": { "optionId": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8" } },
        { "propertyId": "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2", "op": "equals", "value": { "optionId": "opt_01J9ZKQ8E5F7A9B1C3D5E7F9A1" } }
      ] },
      { "propertyId": "prop_01J9ZKQ7I8D0E2F4A6B8C0D2E4", "op": "contains_any", "value": { "userIds": ["usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A"] } }
    ]
  },
  "sort": [ { "propertyId": "prop_01J9ZKQ7J9E1F3A5B7C9D1E3F5", "direction": "asc" } ],
  "group": [ { "propertyId": "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2", "direction": "asc" } ],
  "pageSize": null,
  "recordCount": 37,
  "createdAt": "2025-01-02T09:21:00.000Z",
  "updatedAt": "2025-01-02T11:30:00.000Z"
}

Events emitted: database.updated (change: "view_added" | "view_updated" | "view_deleted").

---

5.2 Per-View config Fields

All view types share: visibleProperties (property ids, in order), color, density (compact | comfortable | spacious).

type	Extra config fields
table	columnWidths: { "<propertyId>": 180 }, headerRow: true, zebra: true
list	showIcons: true, subtitlePropertyId: "prop_…"
board	cardProperties: [ids shown on card], cardLayout: "cover" | "title" | "compact", columnWidthPx: 280
gallery	coverPropertyId: "prop_…", cardsPerRow: 4, cardRatio: "16:9"
calendar	startDatePropertyId: "prop_…", endDatePropertyId: "prop_…" (either may be null = end-of-day of start), weekStartsOn: 1, monthDensity: "comfortable"
timeline	startDatePropertyId, endDatePropertyId, scale: "day" | "week" | "month" | "quarter", lanePropertyId (optional swimlane grouping)
chart	chartId: "cht_…" (links a charts row: chartType: bar|line|area|pie|donut|number, axes, aggregation, color, legend — see Product Spec §12)
dashboard	dashboardId: "dash_…" (links a dashboards row with layout widgets)
form	formId: "frm_…" (links a forms row; public URL <host>/forms/<slug>), intro, successMessage, branding

Validation: calendar/timeline require the date properties to exist and be of type date/date_range → else 422 UNPROCESSABLE_ENTITY (details: [{issue: "view_config_invalid", field: "startDatePropertyId"}]).

---

5.3 Filter Trees (JSON Schema)

Filters are nested boolean trees of and / or / not over property conditions — supporting e.g. (Status = In Progress OR Status = Review) AND Owner = Current User.

filter := { "op": "and" | "or" | "not", "conditions": filter[] }
       |  { "propertyId": string, "op": <condition op>, "value": <per-type> }

Condition ops by property kind:

Kind	Ops	value shape
All (general)	is_empty, is_not_empty, is, is_not	is/is_not take a per-type value
text / rich_text / title / email / url	contains, does_not_contain, starts_with, ends_with	{ "text": "atlas" }
number	equals, not_equals, greater_than, less_than, greater_or_equal, less_or_equal	{ "value": 1000 }
select / status	equals, not_equals, contains_any, contains_all	{ "optionId": "opt_…" } / { "optionIds": [ … ] }
multi_select	contains_any, contains_all, is_empty, is_not_empty	{ "optionIds": [ … ] }
date / date_range	equals, before, after, between, on_or_before, on_or_after, is_this_week, is_this_month, within_next_days, is_past, is_future	{ "value": "2025-04-30T00:00:00.000Z" }, between: { "start": "…", "end": "…" }, within_next_days: { "days": 7 }
checkbox	is, is_not	{ "value": true }
person	contains_any, contains_all, is_current_user	{ "userIds": [ … ] } / {}
relation	contains_any, contains_all, is_empty, is_not_empty	{ "recordIds": [ … ] }
files	is_empty, is_not_empty	—
rollup / formula (numeric)	same as number	{ "value": … }

Full example (the one from the product spec):

{
  "op": "and",
  "conditions": [
    {
      "op": "or",
      "conditions": [
        { "propertyId": "prop_status", "op": "equals", "value": { "optionId": "opt_in_progress" } },
        { "propertyId": "prop_status", "op": "equals", "value": { "optionId": "opt_review" } }
      ]
    },
    { "propertyId": "prop_owner", "op": "is_current_user", "value": {} }
  ]
}

Rules: not takes exactly one child; and/or take ≥ 1; leaves require propertyId, op, and a value compatible with the op (422 UNPROCESSABLE_ENTITY with details: [{issue: "invalid_filter", path: "conditions[0].conditions[1]"}] when not).

---

5.4 Sort & Group Shapes

"sort": [ { "propertyId": "prop_…", "direction": "asc" | "desc" } ]
"group": [ { "propertyId": "prop_…", "direction": "asc" | "desc" } ]

• sort: 0–8 entries, stable; ties broken by record position, then id. Sorting by title sorts by the title text.
• group: 0–3 levels; allowed on board, table, list, gallery views (board requires exactly the properties of type select/status/multi_select/ person/date as group keys; one group key per board column set).

---

6. Query DSL

POST /databases/:databaseId/query

The full query endpoint — the same engine that powers view rendering, usable ad-hoc by API clients. One request, fully server-side evaluated.

• Auth: JWT or API key (scope READ). VIEW.

Request:

{
  "select": ["prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1", "prop_01J9ZKQ7G6B8C0D2E4F6A8B0C2", "prop_01J9ZKQ7K0F2A4B6C8D0E2F4A6"],
  "filter": {
    "op": "and",
    "conditions": [
      { "propertyId": "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2", "op": "not_equals", "value": { "optionId": "opt_01J9ZKQ8C3D5E7F9A1B3C5D7E9" } },
      { "propertyId": "prop_01J9ZKQ7L1G3B5C7D9E1F3A5B7", "op": "after", "value": { "value": "2025-01-01T00:00:00.000Z" } }
    ]
  },
  "sort": [ { "propertyId": "prop_01J9ZKQ7L1G3B5C7D9E1F3A5B7", "direction": "desc" } ],
  "group": [ { "propertyId": "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2", "direction": "asc" } ],
  "aggregate": [
    { "propertyId": "prop_01J9ZKQ7M2H4C6D8E0F2A4B6C8", "fn": "SUM" }
  ],
  "includeArchived": false,
  "cursor": null,
  "pageSize": 50
}

Field	Notes
select	Property ids to return per record. Omit → all visible properties. Rollup/formula properties may be selected (computed on the fly, cached for 60 s).
filter	Any valid filter tree (§5.3). Omit → no filter.
sort	As in views. Omit → record position order.
group	Group records; response is nested per group (see below).
aggregate	0–8 entries: { "propertyId", "fn": "COUNT"|"SUM"|"AVERAGE"|"MIN"|"MAX"|"UNIQUE" }. COUNT may use propertyId: "*" for row count. Aggregates respect the filter but ignore group (global over the filtered set) unless perGroup: true.
includeArchived	Default false — archived records are hidden.
cursor, pageSize	pageSize 1–200, default 50.

200 OK — grouped shape when group is set:

{
  "data": {
    "groups": [
      {
        "key": { "propertyId": "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2", "value": { "optionId": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8" }, "label": "In Progress" },
        "count": 12,
        "records": [
          {
            "id": "rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0",
            "pageId": "pg_01J9ZKR4A5B7C9D1E3F5A7B9C1",
            "icon": "🛰️",
            "position": "a0",
            "createdTime": "2025-01-02T09:22:00.000Z",
            "lastEditedTime": "2025-01-02T16:00:00.000Z",
            "properties": {
              "prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1": { "text": "Atlas migration", "marks": [] },
              "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2": { "optionId": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8" },
              "prop_01J9ZKQ7K0F2A4B6C8D0E2F4A6": { "value": 48250.5 }
            }
          }
        ]
      },
      {
        "key": { "propertyId": "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2", "value": { "optionId": "opt_01J9ZKQ8E5F7A9B1C3D5E7F9A1" }, "label": "Medium" },
        "count": 3,
        "records": [ "…" ]
      }
    ],
    "aggregates": { "prop_01J9ZKQ7M2H4C6D8E0F2A4B6C8": 182340.75 },
    "totalCount": 15,
    "nextCursor": "ZGVlcDoxOjpycHRfMDFKSlo…"
  }
}

Without group, data is the flat envelope from §6.1 plus an optional aggregates object.

Errors: 400 VALIDATION_ERROR (bad aggregate fn), 401 UNAUTHORIZED, 403 FORBIDDEN, 404 NOT_FOUND, 422 UNPROCESSABLE_ENTITY (filter tree, sort/group property type mismatch).

Performance: on 100k+ record databases, queries with indexed property filters run < 300 ms p95; unfiltered full scans return pages of 200 at a time and should always pass a pageSize.

Events emitted: none (read-only).

---

6.1 GET /databases/:databaseId/records — View-Rendered Listing

List records with explicit query params (the alternative to the DSL; handy for curl and simple clients). All params are optional:

Param	Type	Notes
viewId	string	Load the view's filter/sort/group/config (then override with explicit params below).
filter	JSON string	URL-encoded filter tree overriding the view filter.
sort	JSON string	URL-encoded [{propertyId, direction}] overriding view sort.
group	JSON string	URL-encoded group spec.
includeArchived	boolean	Default false.
subItemOf	string	Only sub-records of this record (see §7.5).
cursor	string
pageSize	int	1–200, default 50.

200 OK — flat or grouped exactly like the DSL response.

---

7. Records

A record (rpt_…) is a row and a page: pageId is 1:1 with pages.id. You can read/edit the record's blocks via the normal page/block endpoints (GET /pages/:pageId?include=blocks, POST /blocks), and its properties via the record endpoints below.

7.1 Record Object

{
  "id": "rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0",
  "workspaceId": "wks_01J9ZKP5C9H7J9K1L3M5N7P9Q1",
  "databaseId": "db_01J9ZKQ7E4F6A8B0C2D4E6F8A0",
  "pageId": "pg_01J9ZKR4A5B7C9D1E3F5A7B9C1",
  "icon": "🛰️",
  "coverUrl": null,
  "archivedAt": null,
  "isSubItem": false,
  "position": "a0",
  "version": 4,
  "createdBy": "usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A",
  "createdTime": "2025-01-02T09:22:00.000Z",
  "lastEditedBy": "usr_01J9ZKQ2V4W6X8Y0Z2A4B6C8D0",
  "lastEditedTime": "2025-01-02T16:00:00.000Z",
  "properties": {
    "prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1": { "text": "Atlas migration", "marks": [] },
    "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2": { "optionId": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8" },
    "prop_01J9ZKQ7H7C9D1E3F5A7B9C1D3": { "userIds": ["usr_01J9ZKQ2V4W6X8Y0Z2A4B6C8D0"] },
    "prop_01J9ZKQ7I8D0E2F4A6B8C0D2E4": { "value": 48250.5 },
    "prop_01J9ZKQ7J9E1F3A5B7C9D1E3F5": { "start": "2025-03-01T00:00:00.000Z", "end": "2025-04-30T00:00:00.000Z" },
    "prop_01J9ZKQ7K0F2A4B6C8D0E2F4A6": { "optionIds": ["opt_01J9ZKQ9A7B9C1D3E5F7A9B1C3"] }
  }
}

7.2 Create a Record

POST /databases/:databaseId/records

• Auth: JWT or API key (scope WRITE). EDIT on the database.

Request:

{
  "properties": {
    "prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1": { "text": "Borealis rewrite", "marks": [] },
    "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2": { "optionId": "opt_01J9ZKQ8A1B3C5D7E9F1A3B5C7" },
    "prop_01J9ZKQ7H7C9D1E3F5A7B9C1D3": { "userIds": ["usr_01J9ZKQX4V2M8N3P5R7T9W1Y3A"] },
    "prop_01J9ZKQ7J9E1F3A5B7C9D1E3F5": { "start": "2025-02-10T00:00:00.000Z", "end": "2025-05-20T00:00:00.000Z" },
    "prop_01J9ZKQ7M2H4C6D8E0F2A4B6C8": { "recordIds": ["rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0"] }
  },
  "icon": "🌌",
  "position": "a0",
  "parentRecordId": null
}

Field	Notes
properties	Map of propertyId → typed value (shapes in §3.4). The title property is required (400 if missing/empty for a required title). Unknown property ids → 400. Read-only types → 422.
icon, coverUrl	Page-level decoration for the record page.
position	Insertion point in table/board order; default append.
parentRecordId	Create as a sub-record (see §7.5).

201 Created — full record object (as §7.1), with the generated id, pageId, createdTime, and unique_id property values filled in.

Errors: 400 VALIDATION_ERROR (per-property details), 401 UNAUTHORIZED, 403 FORBIDDEN, 404 NOT_FOUND, 422 UNPROCESSABLE_ENTITY (read-only property, relation to a different workspace's record), 429 RATE_LIMITED.

Events emitted: record.created (includes databaseId, pageId, and a snapshot of the property values for webhooks/indexing).

---

7.3 Update a Record

PATCH /records/:recordId

• Auth: JWT or API key (scope WRITE). EDIT on the record's database.

Request — partial property map (only listed properties change):

{
  "properties": {
    "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2": { "optionId": "opt_01J9ZKQ8C3D5E7F9A1B3C5D7E9" },
    "prop_01J9ZKQ7I8D0E2F4A6B8C0D2E4": { "value": 61200.0 },
    "prop_01J9ZKQ7M2H4C6D8E0F2A4B6C8": { "recordIds": ["rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0", "rpt_01J9ZKR5B6C8D0E2F4A6B8C0D2"] }
  },
  "position": "a0V",
  "icon": "🌌",
  "version": 4
}

• Relation updates are sets (the full new set of linked record ids), not increments.
• If-Match: "4" / version: 4 for optimistic concurrency; stale → 409 CONFLICT with the actual version.
• Rollups/formulas in the response are the previous computed values; recompute completes asynchronously (typically < 1 s).

200 OK — full record object with the bumped version, lastEditedTime, lastEditedBy.

Errors: 400 VALIDATION_ERROR, 401 UNAUTHORIZED, 403 FORBIDDEN, 404 NOT_FOUND, 409 CONFLICT (stale version), 422 UNPROCESSABLE_ENTITY (read-only, relation integrity), 429 RATE_LIMITED.

Events emitted: record.updated (payload includes changedPropertyIds and old/new values for changed properties — this is what automation PROPERTY_CHANGED triggers consume).

---

7.4 Read / Archive / Delete a Record

GET /records/:recordId

• Auth: JWT or API key (scope READ). VIEW.
• 200 OK — full record object (page content is not included; fetch GET /pages/:pageId?include=blocks for the page side).
• Errors: 401 UNAUTHORIZED, 403 FORBIDDEN, 404 NOT_FOUND.

POST /records/:recordId/archive

Soft-archive: archivedAt set; hidden from views/queries by default (includeArchived: true to see). The record page is not trashed.

• Auth: WRITE, EDIT. 200 OK — record object.
• Events: record.archived.

POST /records/:recordId/unarchive

• Auth: WRITE, EDIT. 200 OK — record object (archivedAt: null).
• Events: record.unarchived.

DELETE /records/:recordId

Hard-delete the record and its page (blocks, comments cascade).

• Auth: WRITE, EDIT. confirm body field must equal the record id.

Request: { "confirm": "rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0" }

202 Accepted

{ "data": { "id": "rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0", "status": "deleting", "pageId": "pg_01J9ZKR4A5B7C9D1E3F5A7B9C1" } }

Events: record.deleted (audit).

---

7.5 Sub-Records

Records can be nested (a "sub-item" of another record in the same database) — rendered as an indented row in table views. Create with parentRecordId (POST) or update with parentRecordId (PATCH, may be null to detach). Cycles are rejected (422 cycle_detected); max depth 10. List children with GET /databases/:id/records?subItemOf=<recordId>; the record object exposes parentRecordId and subItemCount.

---

8. curl Examples

# Create a database embedded in a page
curl -s -X POST https://factory.example.com/api/v1/databases \
  -H "Authorization: Bearer $ACCESS_TOKEN" -H 'Content-Type: application/json' \
  -d '{
    "title": "Projects",
    "pageId": "pg_01J9ZKQ4F8H5I7J9L1M3N5P7Q9",
    "properties": [
      { "name": "Name", "type": "title" },
      { "name": "Status", "type": "select", "config": { "options": [
          { "id": "opt_01J9ZKQ8A1B3C5D7E9F1A3B5C7", "name": "Not Started", "color": "gray" },
          { "id": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8", "name": "In Progress", "color": "blue" },
          { "id": "opt_01J9ZKQ8C3D5E7F9A1B3C5D7E9", "name": "Done", "color": "green" } ] } },
      { "name": "Owner", "type": "person" }
    ],
    "views": [ { "name": "Table view", "type": "table" }, { "name": "Board", "type": "board" } ]
  }'

# Create a record
curl -s -X POST https://factory.example.com/api/v1/databases/db_01J9ZKQ7E4F6A8B0C2D4E6F8A0/records \
  -H "Authorization: Bearer $ACCESS_TOKEN" -H 'Content-Type: application/json' \
  -d '{ "properties": {
        "prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1": { "text": "Atlas migration", "marks": [] },
        "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2": { "optionId": "opt_01J9ZKQ8B2C4D6E8F0A2B4C6D8" } } }'

# Update one property (optimistic concurrency)
curl -s -X PATCH https://factory.example.com/api/v1/records/rpt_01J9ZKR3F4A6B8C0D2E4F6A8B0 \
  -H "Authorization: Bearer $ACCESS_TOKEN" -H 'Content-Type: application/json' -H 'If-Match: "4"' \
  -d '{ "properties": { "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2": { "optionId": "opt_01J9ZKQ8C3D5E7F9A1B3C5D7E9" } } }'

# Full DSL query: open projects due in the next 30 days, grouped by status
curl -s -X POST https://factory.example.com/api/v1/databases/db_01J9ZKQ7E4F6A8B0C2D4E6F8A0/query \
  -H "Authorization: Bearer $ACCESS_TOKEN" -H 'Content-Type: application/json' \
  -d '{
    "select": ["prop_01J9ZKQ7F5A7B9C1D3E5F7A9B1", "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2", "prop_01J9ZKQ7J9E1F3A5B7C9D1E3F5"],
    "filter": { "op": "and", "conditions": [
      { "propertyId": "prop_01J9ZKQ7J9E1F3A5B7C9D1E3F5", "op": "within_next_days", "value": { "days": 30 } } ] },
    "group": [ { "propertyId": "prop_01J9ZKQ7G6B8O0D2E4F6A8B0C2", "direction": "asc" } ],
    "aggregate": [ { "propertyId": "*", "fn": "COUNT" } ],
    "pageSize": 100
  }'

# View-rendered listing via params (board view)
curl -s "https://factory.example.com/api/v1/databases/db_01J9ZKQ7E4F6A8B0C2D4E6F8A0/records?viewId=view_01J9ZKQ8G6B8C0D2E4F6A8B0C2&pageSize=50" \
  -H "Authorization: Bearer $ACCESS_TOKEN"

9. Events Summary

Operation	Domain event(s)
Create database	database.created (+ page.updated when embedded)
Update / archive database	database.updated
Property add / update / delete	database.updated (change: property_added / property_updated / property_deleted)
View add / update / delete	database.updated (change: view_added / view_updated / view_deleted)
Create record	record.created
Update record	record.updated (with changedPropertyIds + old/new values)
Archive / unarchive record	record.archived / record.unarchived
Delete record	record.deleted (audit)
Query	— (read-only)