# Tables

Source: https://dabadata.com/docs/tables/ (HTML) - https://dabadata.com/docs/tables/index.md (this page as markdown)

A project is one definition that declares its tables, their columns and who may do what - a file on your own server, JSON sent over the API on a hosted project. dabadata turns it into plain Postgres tables and keeps them in step with the file - without ever deleting your data.

## The project file

A self-hosted server runs the project named by `DABADATA_PROJECT_NAME` and imports its definition from `projects/<name>.ts` at the repository root. A hosted project has no file: you send the same definition as JSON, see [Definitions on a hosted project](https://dabadata.com/docs/tables/index.md#hosted). The file's default export is the result of `defineProject(...)` from `server/src/define.ts`.

`defineProject` does two things only: it validates the definition, throwing one error that lists every problem, and it returns a plain JSON object. There are no functions anywhere in a definition, so it is data - the server stores it with the project and can show it, diff it and hand it to an agent.

```ts
import { defineProject, t } from "../server/src/define";

export default defineProject({
  name: "default",
  roles: ["admin", "editor", "viewer"],
  tables: {
    tasks: {
      columns: {
        title: t.text(),
        done: t.bool({ default: false }),
        owner: t.userId(),
        due: t.date({ optional: true }),
      },
      rules: {
        read: "authenticated",
        create: { role: ["admin", "editor"] },
        update: { any: [{ role: "admin" }, { owner: "owner" }] },
        delete: { role: "admin" },
      },
    },
    comments: {
      columns: {
        task: t.ref("tasks"),
        body: t.text(),
        author: t.userId(),
      },
      rules: {
        read: "authenticated",
        create: "authenticated",
        update: { owner: "author" },
        delete: { any: [{ role: "admin" }, { owner: "author" }] },
      },
    },
  },
});
```

What the helpers produce is plain JSON. This is the start of the `tasks` table as `GET /v1/schema` returns it under `tables`:

```json
{
  "tasks": {
    "columns": {
      "title": { "type": "text" },
      "done": { "type": "bool", "default": false },
      "owner": { "type": "userId" },
      "due": { "type": "date", "optional": true }
    },
    "rules": { "read": "authenticated", "create": { "role": ["admin", "editor"] } }
  }
}
```

Beside `name`, `roles` and `tables`, a definition may carry an optional `auth` block that lists the identity providers the project trusts (Google, Auth0, Clerk, Keycloak, your own JWTs), so clients can sign in with them directly. It is validated with the rest of the file and stays plain JSON; see [Your own identity provider](https://dabadata.com/docs/identity/index.md#providers). The other optional top-level keys are `actions` ([named server-side transactions](https://dabadata.com/docs/rules/index.md#actions-protecting-counters-capacity-and-stock)), `views` ([declared read shapes](https://dabadata.com/docs/views/index.md)), `access` (the default of the tables' [`access`](https://dabadata.com/docs/tables/index.md#table-keys)), `storage`, `trace` and `mcp`.

## Column types

Each helper on `t` returns a plain column object, the JSON form a [hosted project's definition](https://dabadata.com/docs/tables/index.md#hosted) is written in. Options sit beside `type`: `t.text({ optional: true })` is `{ "type": "text", "optional": true }`, and a `ref` to an `events` table is `{ "type": "ref", "table": "events" }`. Values are checked against the type on every insert, update and `where` filter; a value that does not fit is a 400 naming the column.

| Type | Helper | In a JSON definition | Postgres | Value |
| --- | --- | --- | --- | --- |
| `text` | `t.text()` | `{ "type": "text" }` | `text` | A string. |
| `int` | `t.int()` | `{ "type": "int" }` | `bigint` | A whole number within JavaScript's safe integer range. |
| `float` | `t.float()` | `{ "type": "float" }` | `double precision` | A finite number. |
| `bool` | `t.bool()` | `{ "type": "bool" }` | `boolean` | `true` or `false`. |
| `date` | `t.date()` | `{ "type": "date" }` | `date` | A real calendar day as `"YYYY-MM-DD"`. |
| `datetime` | `t.datetime()` | `{ "type": "datetime" }` | `timestamptz` | An ISO 8601 string, e.g. `"2026-10-15T09:30:00Z"`. |
| `json` | `t.json()` | `{ "type": "json" }` | `jsonb` | Any JSON value. |
| `userId` | `t.userId()` | `{ "type": "userId" }` | `text` | The id of an end user. On insert, every `userId` column you leave out is filled with the caller's id. |
| `ref` | `t.ref("tasks")` | `{ "type": "ref", "table": "tasks" }` | `text` | The id of a row in the named table. The table must exist in the definition; dabadata does not add a foreign key, and what a delete of that row does is `onDelete` (below). |
| `location` | `t.location()` | `{ "type": "location" }` | `jsonb` | A point: `{ "lat", "lng" }`. Filter with `$near`, sort by `_distance`, round with `precision`. See [Locations](https://dabadata.com/docs/tables/index.md#locations). |
| `file` | `t.file("avatars")` | `{ "type": "file", "bucket": "avatars" }` | `text` | The id of a file in the named [storage bucket](https://dabadata.com/docs/storage/index.md). A write must name a ready file the caller uploaded; the file a row lets go of is deleted. See [File columns](https://dabadata.com/docs/storage/index.md#file-columns). |

## Column options

Every helper takes an optional options object (for `t.ref` it is the second argument).

| Option | Default | Meaning |
| --- | --- | --- |
| `optional` | `false` | The column may be left out or set to `null`. |
| `default` | none | A literal used when an insert leaves the column out. It must fit the type; that is checked when the definition is validated. |
| `unique` | `false` | No two rows may hold the same value (empty values do not count). A write that would break it answers 409 `{ "error": "conflict", "column" }`. See [Constraints](https://dabadata.com/docs/tables/index.md#constraints). |
| `onDelete` | `"none"` | `ref` columns: `"restrict"`, `"cascade"` or `"setNull"`. See [Ref deletes](https://dabadata.com/docs/tables/index.md#ref-deletes). `file` columns: `"delete"` (their default) or `"keep"` - whether deleting the row deletes its file. |
| `onReplace` | `"delete"` | `file` columns only: whether a new value (or null) deletes the file it replaces, or `"keep"`s it. |
| `precision` | `"exact"` | `location` columns only: `"100m"`, `"1km"` or `"10km"` - how finely end users get it. See [Locations](https://dabadata.com/docs/tables/index.md#locations). |
| `update`, `ops` | none | Column rules: who may update this column, and with which operators. See [Column rules](https://dabadata.com/docs/rules/index.md#column-rules). |
| `read` | none | Column read rule: who may see this column's value, on top of the table's read rule (`t.text({ read: { role: "admin" } })`). A caller it denies gets the row without the key. See [Column read rules](https://dabadata.com/docs/rules/index.md#column-read-rules). |

A column that is neither optional nor has a default is required: an insert without it answers 400 with `missing required column "title"`, and setting it to `null` answers `column "title" may not be null`. These checks live in dabadata, not in Postgres - the columns themselves are created nullable, so adding a required column to a table that already has rows always works.

## Table keys

A table takes these keys, and a column the keys of the options above plus `type` and `table`:

| Key | Meaning |
| --- | --- |
| `columns` | Required. Column name -> column, as above. |
| `rules` | Who may read, create, update and delete rows ([Rules](https://dabadata.com/docs/rules/index.md)). Required on an open table, with an explicit `rules.read`; optional on a strict one. |
| `access` | `"open"` (end users reach the table through its rules) or `"views"` (a [strict table](https://dabadata.com/docs/rules/index.md#strict-tables): end users read it through views and write it through actions only). Default: the project's `access`, else `"open"`. |
| `unique` | Composite unique constraints ([Constraints](https://dabadata.com/docs/tables/index.md#constraints)). |
| `checks` | Named CHECK constraints ([Constraints](https://dabadata.com/docs/tables/index.md#constraints)). |

Any other key is refused, on a table and on a column: a typo such as `reed` or `acess` must not silently weaken a rule.

```
tasks: unknown key "acess" (expected columns, rules, access, unique, checks)
tasks.title: unknown key "reed" (expected type, table, optional, default, unique, onDelete, update, ops, read)
tasks.access: must be "open" or "views"
tasks: no read rule (rules.read must be explicit, even "nobody")
```

## Constraints

Constraints are kept by Postgres, so they hold for every client at once - the one place a rule like "stock never below zero" cannot be raced. Besides `unique` on a column, a table takes:

```ts
products: {
  columns: { name: t.text({ unique: true }), stock: t.int(), shelf: t.text(), slot: t.int() },
  unique: [["shelf", "slot"]],                         // composite: the pair is unique
  checks: { stock_nonneg: "stock >= 0", slot_range: "slot >= 1 and slot <= 40" },
  rules: { ... },
}
```

- **Checks** are `column op value`, `column op column` or `column is null` / `column is not null`, joined by `and` / `or` (parentheses group), with `=`, `!=`, `<`, `<=`, `>`, `>=`. Values are numbers, `'strings'` (also for dates and datetimes) and `true` / `false`, each checked against the column's type; json columns cannot be compared (but can be tested with `is null`). No functions, no subqueries. An empty column passes a comparison (as in SQL), so `lat >= -90 and lat <= 90` already allows an empty `lat`; use `is null` for rules about emptiness itself, such as "both or neither": `(lat is null and lng is null) or (lat is not null and lng is not null)`. Check names follow the column-name pattern.
- A write that breaks one answers 409 `{ "error": "conflict", "check": "stock_nonneg" }` (a composite unique: `"columns": ["shelf", "slot"]`) and changes nothing. Together with an operator (`{ "stock": { "$dec": 1 } }`) a check is the simplest correct stock counter: see [Safe concurrent writes](https://dabadata.com/docs/concurrency/index.md).
- In Postgres they are named `uq_<table>_<columns>` and `ck_<table>_<name>` (cut to 63 bytes with a hash when longer).

## Ref deletes

A `ref` column says what happens to its rows when the row it points at is deleted:

```json
"rsvps": {
  "columns": {
    "event": { "type": "ref", "table": "events", "onDelete": "cascade" },
    "user": { "type": "userId" }
  },
  "rules": { "read": "anyone", "create": "authenticated" }
}
```

In a project file: `event: t.ref("events", { onDelete: "cascade" })`.

| `onDelete` | Deleting an event... |
| --- | --- |
| `"none"` (default) | ...leaves the rows pointing at it as they are. |
| `"restrict"` | ...is refused while rows still point at it: 409 `{ "error": "conflict", "ref": "tickets.event", "message": "rows of tickets still point at it (tickets.event onDelete restrict)" }`. |
| `"cascade"` | ...deletes them too, and whatever cascades from them (replies to replies included). |
| `"setNull"` | ...sets the column to `null`. The column must be `optional`. |

The server does it in the same transaction as the delete - through `/v1/mutate`, `/v1/transact` and actions alike - so either everything happens or nothing does. The deleted or changed rows' own rules are not checked: the cascade is part of the parent's delete, which the caller was allowed to make. Each affected row gets its own [trace](https://dabadata.com/docs/trace/index.md) row (`op: "delete"`, or `"update"` for setNull, with `values._cascade` naming the parent) and its own live change. One delete may change at most 10,000 rows this way; more is a 409 and nothing is deleted - remove them in smaller parts first.

There is still no foreign key underneath: a row inserted at the very moment its parent is deleted can be left behind, and deletes made in the SQL console do not cascade.

## Locations

A `location` column holds a point, `{ "lat": 52.52, "lng": 13.405 }` (lat from -90 to 90, lng from -180 to 180). Find rows near a point with `$near` in `where` - at the top level, combined with any other condition:

```js
const near = await db.query("people", {
  where: { $near: { column: "home", lat: 52.52, lng: 13.405, km: 10 }, open: true },
  orderBy: "_distance",            // nearest first
});
// [{ id, name, home: { lat: 52.5, lng: 13.4 }, _distance: 2.314, ... }]

db.subscribe("people", { where: { $near: { column: "home", lat: 52.52, lng: 13.405, km: 10 } } }, {
  onChange: ({ op, row }) => render(op, row),    // rows coming within 10 km, with _distance
});
```

- Every row a `$near` answers carries `_distance`: kilometres, great-circle, to the metre. `orderBy: "_distance"` (or `"-_distance"`) sorts by it.
- A location column cannot be used in other filters, sorts, `expect`, rule conditions or checks; an update sets it whole.
- `precision`: `"100m"`, `"1km"` or `"10km"` rounds what end users get to 3, 2 or 1 decimals (the secret key and the console see the stored value). Distances are measured from the rounded position, for everyone, so `_distance` and `$near` never tell more than the rounded location.
- Hide the point and still answer distances: give the column a [read rule](https://dabadata.com/docs/rules/index.md#column-read-rules) and a `precision`. Callers who may not read it can still ask `$near` and get `_distance`, without the column. A hidden column without a precision is refused in `$near`, because ever smaller circles would find the exact point.
- Live subscriptions follow it: a row that moves into the circle arrives, one that moves out is removed.
- Not in [views](https://dabadata.com/docs/views/index.md) yet: a view query or subscription with `$near` is a 400 - read the table with `$near` instead. There is no location index yet; every `$near` measures each row the read rule lets through.
- Turning an address into a point (geocoding) stays in your app - for example with [Photon](https://photon.komoot.io/) or [Nominatim](https://nominatim.org/) (OpenStreetMap data; show their attribution and respect their usage policies).

## Implicit columns

Every table also gets four columns you never declare and clients never set:

| Column | Type | Value |
| --- | --- | --- |
| `id` | `text` | A random UUID chosen by the server. Sending an `id` on insert is a 400. |
| `created_at` | `timestamptz` | When the row was inserted. Lists are sorted by it, newest first, unless you choose another order. |
| `updated_at` | `timestamptz` | When the row last changed. |
| `created_by` | `text` | The id of the user who inserted it. |

## Names

- **Tables and columns** match `^[a-z][a-z0-9_]{0,40}$`: lowercase, starting with a letter, up to 41 characters. `id`, `created_at`, `updated_at` and `created_by` are reserved.
- **Roles** match `^[a-z][a-z0-9_]{0,30}$` and are declared once each in `roles`.
- Two roles exist without being declared: `anonymous` (a call with no token) and `service` (a call with the project key). `anonymous` may appear in a role rule; neither may be declared.
- The project's tables live in their own Postgres schema, `p_<project name>`, so `tasks` in the `default` project is `p_default.tasks`. Each environment of a hosted project has a schema the server names for it; the definition's `name` plays no part there.

## What a reload does

The definition is applied when the server starts and on every `POST /v1/schema/reload`. Applying means:

1. Import `projects/<name>.ts` again and validate it. If it is invalid, stop: reload answers 400 with every message and the definition already in force stays. (At start, an invalid file means the server keeps serving the definition it stored last time.)
2. Create the schema `p_<project>` if it is missing.
3. Create every table that is missing.
4. Add every column that is missing.
5. Drop the constraints the definition no longer declares (only the `uq_` / `ck_` ones the server made; a changed check is dropped and added again), then add the new ones. Before adding one the existing rows are counted: if any break it, nothing at all is applied and the answer is a 400 such as `cannot add check products.stock_nonneg (stock >= 0): 1 row violates it`. A dry run lists these steps too (`add unique products(name)`, `add check products.stock_nonneg`, `drop constraint products.uq_products_sku`).

A reload **never drops** a table or a column and **never changes** a column's type; constraints are the one thing it removes. When the database and the file disagree, the difference is reported as drift, in the reload response and in `GET /v1/schema`:

```
table notes exists in db but not in definition
column tasks.priority exists in db but not in definition
column tasks.due is text in db but date (date) in definition
```

Drift is yours to resolve: put the declaration back, or drop, rename or convert what is in the database with a [migrate](https://dabadata.com/docs/tables/index.md#migrate) (or the console's Drift panel). It is plain Postgres, so any tool works too.

> **Note:** Renaming a column in the definition alone is an add plus drift: the new column is created empty and the old one is reported. A [migrate](https://dabadata.com/docs/tables/index.md#migrate) with a `renameColumn` step and the new definition moves the values in the same call.

## Definitions on a hosted project

A [hosted project](https://dabadata.com/docs/projects/index.md) has no file on the server. Its definition is the JSON form shown above - exactly what `defineProject` returns - and you send it with the environment's secret key to [`PUT /v1/schema`](https://dabadata.com/docs/api/index.md#schema-put). `name` may be left out. Live and test each have their own definition, so a change goes to test first and to live when it is ready.

Keep the definition in your repository as a JSON file, say `dabadata.json`, and apply it in two steps.

1. **Dry run.** Validate it and see what would change, without changing anything:

   ```bash
   jq '{definition: ., dryRun: true}' dabadata.json | curl -X PUT https://dabadata.com/v1/schema \
     -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
     -H "Content-Type: application/json" -d @-
   ```

   ```json
   { "ok": true, "plan": ["create table comments", "add column tasks.due"], "drift": [] }
   ```
2. **Apply.** The same call without `dryRun` applies it and stores it as a new version. It is in force for the next call, open subscriptions included:

   ```bash
   jq '{definition: .}' dabadata.json | curl -X PUT https://dabadata.com/v1/schema \
     -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
     -H "Content-Type: application/json" -d @-
   ```

   ```json
   { "ok": true, "applied": ["create table comments", "add column tasks.due"], "drift": [], "version": 3 }
   ```

An invalid definition answers 400 with every message, exactly like a bad file, and nothing changes. Validation is the same as for a file, with the hosted rules for [identity providers](https://dabadata.com/docs/identity/index.md#hosted) on top. Applying works like [a reload](https://dabadata.com/docs/tables/index.md#reload): it creates what is missing, never drops or retypes anything, and reports drift. Resolve drift by declaring the table or column again, or with a [migrate](https://dabadata.com/docs/tables/index.md#migrate): drop, rename or convert.

### Dropping a table

`POST /v1/schema/drop-table` drops a table that is in the database but not in the definition, with its rows. It takes the secret key (or a console session of a developer or above). Ask first with a dry run:

```bash
curl -X POST https://dabadata.com/v1/schema/drop-table \
  -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"table":"tasks","dryRun":true}'
```

```json
{ "ok": true, "dryRun": true, "table": "tasks", "rows": 3 }
```

Then drop it, repeating the name in `confirm`:

```bash
curl -X POST https://dabadata.com/v1/schema/drop-table \
  -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"table":"tasks","confirm":"tasks"}'
```

```json
{ "ok": true, "table": "tasks", "rows": 3, "drift": [] }
```

A declared table, a storage bucket's `files_<bucket>` table and the server's own tables are refused (400); remove a table from the definition and apply first. The drop is recorded in the workspace's audit log (`table.dropped`). It cannot be undone.

### Migrations: drop, rename, change a type

[`POST /v1/schema/migrate`](https://dabadata.com/docs/api/index.md#schema-migrate) makes the changes an apply never makes, then applies the definition, all in one transaction: if any step fails, nothing changes. It takes the secret key (or a console session of a developer or above). The steps run in order:

| Step | What it does |
| --- | --- |
| `{ "dropTable": "old_tasks" }` | Drops a table the definition does not declare, with its rows. |
| `{ "dropColumn": "tasks.legacy" }` | Drops a column the definition does not declare. |
| `{ "renameTable": "tasks", "to": "todos" }` | Renames a table, keeping its rows. When `todos` exists and is empty (an apply made it), it is replaced; with rows, the step is refused. |
| `{ "renameColumn": "tasks.title", "to": "name" }` | Renames a column, keeping its values. An all-empty `tasks.name` is replaced. |
| `{ "changeType": "tasks.score", "to": "int" }` | Converts a column through each value's text form. Rows whose value does not convert block it and are listed. To `location`, each value must be a JSON `{ "lat", "lng" }`. |

Send `definition` to apply a new definition after the steps (and store it as a version); without it the current one is applied again. The definition in force afterwards must not declare a dropped table or column, nor the old name of a renamed one, and must declare a converted column with its new type. So a rename is one call: the step plus the definition with the new name.

Always start with a dry run. It runs the same transaction and rolls it back, so what it reports is exactly what the migrate meets:

```bash
jq '{steps: [{changeType: "tasks.score", to: "int"}], definition: ., dryRun: true}' dabadata.json \
  | curl -X POST https://dabadata.com/v1/schema/migrate \
  -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
  -H "Content-Type: application/json" -d @-
```

```json
{
  "ok": true,
  "dryRun": true,
  "plan": [],
  "blocking": [{ "step": "change type tasks.score text -> int", "rows": 1, "sample": [{ "id": "t2", "value": "n/a" }] }],
  "messages": ["step 1 (change type tasks.score -> int): 1 row does not convert"],
  "drift": ["column tasks.score is text in db but int (bigint) in definition"]
}
```

Fix or clear the blocking values, then send the same body without `dryRun`. Drops and type changes lose data, so they also need `confirm`: the list of their targets.

```json
{ "steps": [{ "dropTable": "old_tasks" }, { "dropColumn": "tasks.legacy" }], "confirm": ["old_tasks", "tasks.legacy"] }
```

```json
{ "ok": true, "applied": ["drop table old_tasks (3 rows)", "drop column tasks.legacy (2 values)"], "drift": [] }
```

The same is in the console - the Drift panel of the Definition page drops, renames into a declared table or column, and converts; a check of an edited definition offers the rename when you renamed a table or column - in the CLI (`dabadata migrate drop-table|drop-column|rename|retype`) and as the agent's `migrate` tool. Every migrate is in the audit log (`schema.migrated`). What a drop removes is gone - the files a dropped [file column](https://dabadata.com/docs/storage/index.md#file-columns) held too, unless another column holds them (the dry run counts them: `drop column people.photo (1 value, 1 file)`). The export holds declared tables only, so copy a drift table's rows with the SQL console first if you may want them.

### Versions and rollback

Every apply is kept as a numbered version per environment. [`GET /v1/schema/versions`](https://dabadata.com/docs/api/index.md#schema-versions) lists them, newest first, and `GET /v1/schema/versions/:n` returns one with its whole definition. To go back, apply an old version again:

```bash
curl -X POST https://dabadata.com/v1/schema/rollback \
  -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"version":2}'
```

A rollback is itself a new version, with the old definition. Like every apply it only adds: a table or column that version 2 did not have stays in the database with its data and shows up as drift, so rolling back never loses rows.

> **Note:** On a self-hosted server, the default project keeps its file: `PUT /v1/schema` and rollback answer 400 there and point you to the file and `POST /v1/schema/reload`. Its versions can still be listed - one per reload, and one per start that found a changed file.