Tables & Database

Creating Tables

Tables live inside a base, which is an account-level resource — not something you create from inside a single app.

1. Open the Base Editor from your dashboard (or your app's Config → Base) and open — or create — a base
2. Click New Table and give it a name (e.g., "Orders", "Products")
3. Add fields (columns) with the appropriate type
4. Make sure the base is linked to your app in Config → Base
5. Set access rules for who can read, create, update, and delete rows

Field Types

Lookups, Rollups & Conditional Aggregation

Link fields connect rows across tables. Lookup fields show values from the connected records, and Rollup fields calculate across them — always up to date, computed by the database itself. Full guide: Linked Records, Lookup & Rollup.

Rollup COUNT counts linked records (including blanks); lookup COUNT and COUNT_FILLED count non-empty values. MEDIAN and COUNT_UNIQUE are real database aggregations, not client-side approximations.

// rollupConfig with a filter over LINKED-table fields —
// only matching linked rows are aggregated
{
  relationshipFieldId: "<link field id>",
  sourceFieldId: "<Daily Send Limit>",
  aggregation: "SUM",
  filter: {
    logic: "and",
    conditions: [{ fieldId: "<Purpose>", operator: "equals", value: "Outreach" }]
  }
}

Conditions use the same operators as view filters and combine with and/or logic. Lookups accept the same filter too — e.g. "emails of linked contacts where Role is Billing".

Field Behaviors — Defaults, Unique & Links

Formulas

Formula fields calculate a value per row from other fields in the same table. Reference fields by name in curly braces:

{Price} * {Quantity}
IF({Status} = 'active', 'Live', 'Paused')
{First Name} & ' ' & {Last Name}
ROUND(DATETIME_DIFF({Due Date}, TODAY(), 'days'), 0)

Over 45 functions are available:

SQL Queries

Ask the AI to query your base with real SQL — joins, GROUP BY, aggregates — or use it yourself through the API. Tables and columns go by their names:

SELECT c."Business Name", SUM(o."Amount") AS total
FROM "Customers" c
JOIN "Orders" o ON o.id = ANY(...)
GROUP BY c."Business Name"
ORDER BY total DESC

Where you can use it

• Asking the AI in the builder — it runs tables_query against your base
• Backend functions — ctx.tables.query(sql, params), server-side code you write
• Not from published-app pages — deliberately. SQL runs with owner-level access, so it is never exposed to site visitors; page code uses the SDK (entities.*), which goes through the access-rule engine. To give visitors a SQL-powered report, wrap the query in a backend function and put access rules on the function

Scope & security

• Scoped to ONE base you own — ownership is checked before anything runs, and the query can only see that base's tables. It does not and cannot expose the platform database, other users' data, or even your other bases
• Table names in your SQL are rewritten to internal per-base views before execution — physical tables, system catalogs, and schema-qualified names are unreachable by construction, not by filtering
• Read-only SELECT only: one statement, writes rejected, the transaction itself is READ ONLY as a backstop
• Dangerous/system functions are deny-listed; secret and auth-system fields never become columns at all
• 4-second timeout per query

Columns

• Every table also exposes id, createdAt and updatedAt
• Lookup and rollup columns are queryable — computed live by the database
• Formula columns are queryable too — values are stored on the row and recalculated automatically when inputs change. The one exception: formulas using NOW()/TODAY() read as empty (a stored "now" would always be wrong) and are listed under unavailableColumns.
• Results are capped at 2,000 rows per query

SDK Operations

Use the SDK in your published app pages to interact with tables:

// Import from the SDK
import { entities } from '../api/sdk';

// List rows (sort, limit, offset)
const rows = await entities.Products.list('-createdAt', 20);

// Get a single row by ID
const product = await entities.Products.get(rowId);

// Create a row — returns { success: true, id }, NOT the full row
const { id } = await entities.Products.create({
  Name: 'Widget', Price: 9.99, InStock: true
});

// Update a row
await entities.Products.update(rowId, { Price: 12.99 });

// Delete a row
await entities.Products.delete(rowId);

// Filter rows
const results = await entities.Products.filter({
  Name: { $contains: 'widget' }
});

For the full list of methods including bulk operations and upsert, see ctx.tables reference. Note: ctx.tables in backend functions is creator-scoped only, not the full access-rule engine — has_role/has_entitlement/field-level rules are not applied there the way they are for these page-level SDK calls.

Filtering & Sorting

The same filter language works everywhere — entities.<Table>.filter() in pages, ctx.tables.filterRows() in backend functions, and the AI's table tools. Top-level conditions AND together; use $or for alternatives:

const rows = await entities.Orders.filter({
  Status: 'active',                 // plain value = equals
  Total: { $between: [100, 500] },  // operators compose
  $or: [                            // groups nest arbitrarily
    { Priority: { $gte: 8 } },
    { Tags: { $contains: 'urgent' } },
  ],
}, {
  limit: 50,
  offset: 0,
  sort: '-Total', // any FIELD NAME or rowOrder/createdAt/updatedAt; "-" = descending
});

Sorting

• Sort by rowOrder (manual grid order), createdAt, updatedAt — or any field name: sort: '-Total'
• Numbers sort numerically, text alphabetically (case-insensitive), checkboxes false→true; blank cells always sort last
• Scalar lookup/rollup values and self-contained formulas sort server-side too
• Not sortable: link fields (sort by a rollup over them instead), NOW()/TODAY() formulas, list aggregations
• An unknown sort key returns a clear 400 — it is never silently ignored

There's no separate order option — direction is encoded in the sort string itself.

Import & Export

• Import matches columns by field NAME, applies default values, validates types/enums per row, and enforces unique fields atomically — an import containing duplicates is rejected whole, never partially applied
• Values for computed fields (formula, lookup, rollup) in an import are skipped and reported — they are recalculated, not stored
• Export produces CSV/JSON with linked records resolved from the relation table (the source of truth), not stale cell arrays

Limits

Every limit fails loudly with a clear error — no operation silently truncates or partially applies.

Views

Create saved views of your table with pre-configured filters, sorting, and visible columns. Views help you organize your data without changing the underlying table.