Tables API

Custom data tables allow you to store and manage structured data that agents can query and manipulate.

Table Management

List Tables

GET /orgs/{organizationId}/tables

Query Parameters:

• limit (optional): Number of results (default: 20)
• offset (optional): Pagination offset (default: 0)

Response:

{
  "items": [
    {
      "name": "customers",
      "organizationId": "org-123",
      "description": "Customer information",
      "fields": [
        {
          "name": "name",
          "type": "text",
          "required": true,
          "comments": "Customer full name"
        },
        {
          "name": "email",
          "type": "text",
          "required": true,
          "comments": "Email address"
        },
        {
          "name": "is_active",
          "type": "boolean",
          "required": false,
          "default": true,
          "comments": "Account status"
        },
        {
          "name": "metadata",
          "type": "jsonb",
          "required": false,
          "comments": "Additional data"
        }
      ],
      "creatorUserId": "user-456",
      "createdAt": "2024-01-01T00:00:00Z",
      "updatedAt": "2024-01-01T00:00:00Z"
    }
  ],
  "totalRows": 1,
  "offset": 0
}

Create Table

POST /orgs/{organizationId}/tables

Required Role: Admin or Owner

Required Fields:

• name: Table name (must be unique). Only letters, numbers, and underscores are allowed.
• fields: Array of field schemas (at least one).

Optional Fields:

• description: Table description

Request Body:

{
  "name": "products",
  "description": "Product catalog",
  "fields": [
    {
      "name": "name",
      "type": "text",
      "required": true,
      "comments": "Product name"
    },
    {
      "name": "price",
      "type": "numeric",
      "required": false,
      "default": 0,
      "comments": "Price in USD"
    },
    {
      "name": "in_stock",
      "type": "boolean",
      "required": false,
      "default": true,
      "comments": "Availability status"
    },
    {
      "name": "metadata",
      "type": "jsonb",
      "required": false,
      "comments": "Product metadata"
    },
    {
      "name": "created_at",
      "type": "timestamp",
      "required": false,
      "comments": "Creation timestamp"
    }
  ]
}

Field Types:

• text - Text/string data
• numeric - Numbers (integers and decimals)
• boolean - True/false values
• timestamp - Date and time values
• jsonb - JSON objects (stored as PostgreSQL JSONB)
• reference - The id of a row in another table (BIGINT); carries the foreign key

Field Schema:

• name (required): Field name — only letters, numbers, and underscores are allowed
• type (required): Field type
• required (required): Whether field is required (boolean)
• default (optional): Default value if not provided
• comments (required): Description/comment for the field

Response: Returns created CustomTable object

Get Table

GET /orgs/{organizationId}/tables/{tableName}

Returns table metadata and field schemas.

Response: Returns CustomTable object

Update Table Schema

PUT /orgs/{organizationId}/tables/{tableName}

Required Role: Admin or Owner

Send the desired schema — the full fields array and any uniqueConstraints — and the server diffs it against the live table and applies the difference. The changes it can make are: rename the table, change the description, add a column, drop a column, set or drop required, set or drop a default, add or drop a unique constraint, and add or drop a foreign key.

Request Body:

{
  "name": "products",
  "description": "Product catalog",
  "fields": [
    { "name": "name", "type": "text", "required": true, "comments": "Product name" },
    { "name": "supplier_id", "type": "reference", "required": false, "comments": "Supplier", "fk": { "referencesTable": "suppliers" } }
  ],
  "uniqueConstraints": [{ "columns": ["name"] }],
  "dryRun": true
}

Optional Fields:

• name: New table name (renames the table)
• description: New description
• uniqueConstraints: Desired unique constraints; omitted constraints are dropped
• dryRun: Return the plan without applying it
• allowDestructive: Required to apply a destructive step (dropping a column)
• expectedPlan: Required with allowDestructive — the plan you reviewed (typically from a dryRun call). The server recomputes the plan and refuses to apply anything if it has changed.

Response:

{
  "plan": [{ "kind": "addColumn", "destructive": false, "column": "supplier_id" }],
  "applied": false
}

applied is false for a dry run. When the change is applied, the updated CustomTable comes back as table. A plan containing a destructive step without allowDestructive is rejected with a 409 that carries the plan.

Delete Table

DELETE /orgs/{organizationId}/tables/{tableName}

Required Role: Admin or Owner

Permanently deletes the table and all its data.

Response:

{
  "organizationId": "org-123",
  "tableName": "products"
}

Table Data Operations

List Rows

GET /orgs/{organizationId}/tables/{tableName}/data

Query Parameters:

• limit (optional): Number of rows (default: 20)
• offset (optional): Pagination offset (default: 0)
• orderBy (optional): Sort specification (format: field1:asc,field2:desc)
• filters (optional): Filter specification. Each segment is field:operator:value:join — the trailing join (and/or) is required on every segment, including a single filter. Segments are separated by ||| (recommended) or , (legacy). Prefer ||| because a comma separator collides with comma-separated in/nin values.

Example with Filters and Sorting:

GET /orgs/org-123/tables/products/data?limit=10&offset=0&orderBy=price:desc&filters=in_stock:eq:true:and|||price:gte:100:and

Filter Operators:

• eq - Equal to
• ne - Not equal to
• gt - Greater than
• lt - Less than
• gte - Greater than or equal to
• lte - Less than or equal to
• like - Case-sensitive pattern matching
• ilike - Case-insensitive pattern matching
• in - In list (comma-separated values)
• nin - Not in list

Filter Join (applied between this segment and the previous one, left-to-right with no bracketing; the join on the first segment is ignored):

• and - AND with the preceding condition
• or - OR with the preceding condition

Response:

{
  "items": [
    {
      "id": 1,
      "name": "Premium Widget",
      "price": 299.99,
      "in_stock": true,
      "metadata": {
        "category": "electronics",
        "tags": ["featured", "premium"]
      },
      "created_at": "2024-01-01T00:00:00Z"
    }
  ],
  "totalRows": 45,
  "offset": 0
}

Create Row

POST /orgs/{organizationId}/tables/{tableName}/data

Required Role: Admin or Owner

Request Body:

{
  "data": {
    "name": "New Product",
    "price": 49.99,
    "in_stock": true,
    "metadata": {
      "category": "accessories"
    }
  }
}

Do not include id — it is auto-generated and rejected if supplied. Any key that is not a defined field is rejected with a 400.

Response: Returns the created row (including its generated id)

Update Row

PUT /orgs/{organizationId}/tables/{tableName}/data/{id}

Required Role: Admin or Owner

The {id} parameter is the numeric row ID (auto-generated integer).

Request Body:

{
  "data": {
    "price": 39.99,
    "in_stock": false
  }
}

Only include fields you want to update. Other fields remain unchanged.

Response: Returns the updated row

Delete Row

DELETE /orgs/{organizationId}/tables/{tableName}/data/{id}

Required Role: Admin or Owner

Permanently deletes the row.

Response:

{
  "organizationId": "org-123",
  "tableName": "products",
  "id": 101
}

Bulk Operations

Batch Insert

POST /orgs/{organizationId}/tables/{tableName}/data/batch

Required Role: Admin or Owner

Insert multiple rows in a single request.

Request Body:

{
  "data": [
    {
      "name": "Product 1",
      "price": 10.00,
      "in_stock": true
    },
    {
      "name": "Product 2",
      "price": 20.00,
      "in_stock": true
    },
    {
      "name": "Product 3",
      "price": 30.00,
      "in_stock": false
    }
  ]
}

Rows are validated individually; invalid rows are skipped and reported in errors, while valid rows are still inserted. ids contains the generated integer IDs of the inserted rows.

Response:

{
  "inserted": 3,
  "failed": 0,
  "errors": [],
  "ids": [1, 2, 3]
}

If validation fails for some rows:

{
  "inserted": 2,
  "failed": 1,
  "errors": [
    {
      "index": 1,
      "error": "Invalid record"
    }
  ],
  "ids": [1, 3]
}

Transaction

POST /orgs/{organizationId}/tables/_transaction

Required Role: Admin or Owner (capability tables.manage)

Applies an ordered list of writes across any of the organisation’s tables in one database transaction — either all of them commit or none does. At most 300 writes per request.

Request Body:

{
  "writes": [
    { "op": "assert", "table": "purchase_order", "where": { "id": 42, "version": 3 }, "expectRowCount": 1 },
    { "op": "update", "table": "bin", "where": { "id": 41 }, "increment": { "on_hand": 6 }, "returning": ["on_hand"] },
    { "op": "insert", "table": "stock_ledger", "data": { "qty": 6, "qty_after": { "$ref": 1, "field": "on_hand" } } }
  ]
}

The operations are insert, update, delete and assert. where is an equality map; increment adds to a numeric column atomically; returning gives the affected row back; { "$ref": i, "field": "c" } takes a value from the returned row of write i.

Response:

{
  "ok": true,
  "results": [
    { "index": 0, "op": "assert", "table": "purchase_order", "rowCount": 1 },
    { "index": 1, "op": "update", "table": "bin", "rowCount": 1, "row": { "on_hand": "10" } },
    { "index": 2, "op": "insert", "table": "stock_ledger", "rowCount": 1, "row": { "id": 7 } }
  ]
}

Failure: nothing is applied, and the body names the failing write.

{ "ok": false, "index": 1, "code": "condition_failed", "retryable": false, "message": "update on \"bin\" matched no rows" }

retryable_conflict and timeout come back with retryable: true — send the same request again.

See Database › Several writes in one transaction for the full semantics.

Column Operations

Rename Column

PUT /orgs/{organizationId}/tables/{tableName}/columns/{columnName}

Required Role: Admin or Owner

Request Body:

{
  "newName": "unit_price"
}

newName must be alphanumeric/underscore only and must not be a reserved name (id).

Response: Returns the updated CustomTable object.

Link Column to the Data Dictionary

POST /orgs/{organizationId}/tables/{tableName}/columns/{columnName}/dd-link

Required Role: Admin or Owner

Links (or re-links) a column to a Data Dictionary field. The column’s physical name must equal ddFieldName (rename the column first if it differs).

Request Body:

{
  "ddFieldName": "sale_price"
}

ddFieldName must be lowercase letters, numbers, and underscores.

Response: Returns the updated CustomTable object.

Unlink Column from the Data Dictionary

DELETE /orgs/{organizationId}/tables/{tableName}/columns/{columnName}/dd-link

Required Role: Admin or Owner

Strips the Data Dictionary link while preserving the column and its data. Idempotent — unlinking a column that is not linked returns the table unchanged.

Response: Returns the updated CustomTable object.

Using Tables with Agents

Agents can query and manipulate tables using the database tool.

Example Agent Configuration:

{
  "name": "Inventory Assistant",
  "tools": [
    {
      "toolCode": "database",
      "configuration": {
        "tables": ["products", "inventory"]
      }
    }
  ]
}

The agent can then:

• “Show me all products under $100”
• “Update the price of product 123 to $79.99”
• “Add a new product called ‘Widget Pro’ with price $150”
• “How many products are currently in stock?”

Filter Examples

Every segment carries a trailing join (and/or), even a single filter.

Simple Equality

filters=in_stock:eq:true:and

Multiple Conditions (AND)

filters=in_stock:eq:true:and|||price:gte:100:and

Multiple Conditions (OR)

filters=category:eq:electronics:and|||category:eq:accessories:or

The join on the second segment (or) is what combines the two conditions.

Pattern Matching

filters=name:ilike:widget:and

Matches any product name containing “widget” (case-insensitive). The value is wrapped in %…% automatically.

Numeric Ranges

filters=price:gte:50:and|||price:lte:200:and

Matches prices between $50 and $200

Complex Example

filters=in_stock:eq:true:and|||price:gte:100:and|||category:in:electronics,accessories:and

Note the ||| separator between segments — an in value list uses commas internally, so the comma separator cannot be used here.

Sorting Examples

Single Field Ascending

orderBy=price:asc

Single Field Descending

orderBy=created_at:desc

Multiple Fields

orderBy=in_stock:desc,price:asc

First by in_stock (descending), then by price (ascending)

Best Practices

1. Table Naming - Use lowercase snake_case for table names (e.g., customer_orders)
2. Field Naming - Use lowercase snake_case for field names (e.g., created_at)
3. Required Fields - Mark critical fields as required
4. Default Values - Provide sensible defaults when possible
5. Comments - Always provide descriptive comments for fields
6. JSONB for Flexibility - Use jsonb type for dynamic/nested data
7. Batch Operations - Use batch insert for importing large datasets
8. Indexing - Tables are automatically indexed on the auto-generated id
9. Data Validation - All data is validated against field schemas before insert

Limitations

• No Re-typing: A column’s type is fixed once created — changing it requires recreating the table, which destroys every row. Adding, dropping, renaming and constraining columns are all supported in place.
• Write Operations Are Admin/Owner Only: Reads are available to the Chat role
• Row ID: Each row has an auto-generated numeric id (reserved, not customizable, never supplied on write)
• Joins: A reference column carries a real foreign key to the parent table’s id (enforced by the database, ON DELETE RESTRICT). A row read returns the id and, when the column declares a displayColumn, the parent’s display value as <field>__display (null if the parent row is missing). The row endpoints do not take arbitrary join clauses; for joins across tables, agents and workflows use the custom SQL query tool, which runs read-only SQL against the organisation’s tables
• No Computed Fields: All fields must be explicitly provided

Common Issues

Data Types Details

text

Stores string data. No length limit.

numeric

Stores numbers. Can be integers or decimals.

{ "price": 99.99 }
{ "quantity": 5 }

boolean

Stores true or false.

{ "is_active": true }

timestamp

Stores ISO 8601 formatted timestamps.

{ "created_at": "2024-01-01T12:00:00Z" }

jsonb

Stores JSON objects. Supports nested structures.

{
  "metadata": {
    "tags": ["featured", "sale"],
    "attributes": {
      "color": "blue",
      "size": "large"
    }
  }
}