Concepts

Tables

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. 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.

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:

{
  "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. The other optional top-level keys are actions (named server-side transactions), views (declared read shapes), access (the default of the tables' access), storage, trace and mcp.

Column types

Each helper on t returns a plain column object, the JSON form a hosted project's definition 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.

Column options

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

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:

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:

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.
• 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:

"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" }).

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 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:

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 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 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 or Nominatim (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:

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 (or the console's Drift panel). It is plain Postgres, so any tool works too.

Definitions on a hosted project

A hosted project 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. 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 on top. Applying works like a 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: 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:

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}'

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

Then drop it, repeating the name in confirm:

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"}'

{ "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 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:

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:

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 @-

{
  "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.

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

{ "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 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 lists them, newest first, and GET /v1/schema/versions/:n returns one with its whole definition. To go back, apply an old version again:

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.