# Views

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

A dabadata view is a named, read-only shape of one table: which rows, which columns, a few computed flags, parameters the caller fills in, rows of other tables joined in, and a different shape per role. Clients read it by name; the view decides what comes back.

## What a view is

A view reads one base table with the server's rights, the way an [action](https://dabadata.com/docs/rules/index.md#actions-protecting-counters-capacity-and-stock) writes with them: the table's own read rule is not evaluated. Instead the view's `allow` says who may read it and its `rows` which rows they get. So a table nobody may read directly - `read: "nobody"`, or a [strict table](https://dabadata.com/docs/rules/index.md#strict-tables) - can still be read, in exactly the shape you declared. Writes keep going through actions.

- One base table per view, and up to four [inner joins](https://dabadata.com/docs/views/index.md#joins) that nest a row of another table into each row.
- The base table's [column read rules](https://dabadata.com/docs/rules/index.md#column-read-rules) still apply inside a view.
- Views are part of the definition (`views`), validated with it, and shown by `GET /v1/schema`.

> **Note:** "View" here means a declared view. A **view token** is something else: the 12-hour read-only token the viewer and the console use ([`POST /v1/view-token`](https://dabadata.com/docs/api/index.md#view-token)). This page always says "view token" for those.

## An example

A quiz: questions belong to rounds, nobody reads the questions table directly, and players read the open questions of one round through a view. The host gets more columns.

```ts
export default defineProject({
  name: "quiz",
  roles: ["player", "host"],
  tables: {
    rounds: {
      columns: { title: t.text() },
      rules: { read: "anyone", create: { role: "host" } },
    },
    questions: {
      columns: {
        round: t.ref("rounds"),
        text: t.text(),
        options: t.json({ optional: true }),
        reveal_at: t.datetime(),
        answer: t.text({ read: { any: [{ role: "host" }, { where: "reveal_at <= $now" }] } }),
        published: t.bool({ default: false }),
      },
      rules: { read: "nobody", create: { role: "host" }, update: { role: "host" } },
    },
  },
  views: {
    open_questions: {
      table: "questions",                                   // the base table (joins: see below)
      allow: { role: ["player", "host"] },                  // a rule on the caller (no row)
      rows: { where: "published = true and round = $params.round" },
      params: { round: { type: "ref", table: "rounds" } },
      columns: ["id", "text", "options", "answer", "reveal_at"],
      computed: { revealed: "reveal_at <= $now" },
      filter: ["reveal_at"],
      sort: ["reveal_at"],
      orderBy: "reveal_at",
      desc: false,
      limit: { default: 50, max: 200 },
      roles: {
        host: { rows: "anyone", columns: ["id", "text", "options", "answer", "reveal_at", "created_by"] },
      },
    },
  },
});
```

A player asks for round `r1`:

```bash
curl -G https://dabadata.com/v1/views/open_questions \
  -H "X-Api-Key: <publishable key>" \
  -H "Authorization: Bearer <user token>" \
  --data-urlencode 'params={"round":"r1"}'
```

```json
{
  "rows": [
    { "id": "q2", "text": "2 + 2?", "options": ["3", "4"], "answer": "4",
      "reveal_at": "2026-10-16T18:00:00.000Z", "revealed": true, "_seq": 40 },
    { "id": "q1", "text": "Capital of Peru?", "options": ["Lima", "Cusco"],
      "reveal_at": "2026-10-16T20:00:00.000Z", "revealed": false, "_seq": 41 }
  ],
  "traceId": 512,
  "params": { "round": "r1" }
}
```

The host gets every question of every round (their `rows` is `"anyone"`), with `created_by` and the answer. A caller of another role gets 403. More worked examples - per-role shapes, a leaderboard, the strict version of this game - are on [Access patterns](https://dabadata.com/docs/access-patterns/index.md).

## Definition reference

`views` maps a name to a view. Names match `^[a-z][a-z0-9_]{0,40}$` and share one name space with tables (declared tables, `files_<bucket>`, the profiles `users` table); a view's MCP tool `view_<name>` may not be a table's tool name either.

| Key | Required | Meaning |
| --- | --- | --- |
| `table` | yes | The base table: a table declared in `tables`. |
| `allow` | yes | Who may read the view: a rule on the caller alone, as an action's `allow`. `owner` and conditions naming a column are refused (there is no row); `{ "where": "$user.role != 'banned'" }` or `$now` comparisons are fine. |
| `rows` | yes | Which rows: the full rule grammar over the base table (`anyone`, `authenticated`, `nobody`, `role`, `owner`, `where`, `any`, `all`), and conditions may read `$params.<name>`. `"anyone"` for every row. |
| `join` | no | name -> `{ table, on, columns?, where? }`: up to four inner joins, each nesting one row of another table under its name. See [Joins](https://dabadata.com/docs/views/index.md#joins). |
| `params` | no | name -> `{ type, table?, optional?, default? }`: the actions' param shape plus `default`, a value of the param's type used when the caller gives none. Every param is required unless it is optional or has a default. |
| `columns` | no | The output columns: declared columns of the base table and the system columns `id`, `created_at`, `updated_at`, `created_by`. `id` is always output and `_seq` is added to every row. Left out: every column. |
| `computed` | no | name -> a condition (the [row-condition grammar](https://dabadata.com/docs/rules/index.md#row-conditions)), output as `true` / `false`. It may read every column of the base table (output or not), `$params.*`, `$user.*`, `$now` and `$today`. Booleans only in this version. |
| `filter` | no | The columns a caller's `where` may name. Each must be an output column of every shape of the view. Without it, no `where` is accepted. [`$near`](https://dabadata.com/docs/tables/index.md#locations) is not accepted in a view yet. |
| `sort` | no | The columns a caller's `orderBy` may name, with the same condition. |
| `orderBy`, `desc` | no | The default sort: one column of `sort`, ascending unless `desc: true`. Without them: `created_at`, newest first. |
| `limit` | no | `{ default?, max? }`, integers from 1 to 1000, `default` at most `max`. Missing: default 100 (at most `max`), max 1000. A caller's `limit` above `max` is clamped to it. |
| `roles` | no | role -> `{ rows?, join?, columns?, computed? }`, for a declared role or `anonymous`. Each key given replaces the view's own for callers of that role (a role's `columns` is the whole list, not an addition). |

Any other key is refused, with the list of keys a view takes.

## How a view reads

- **The base table's read rule and its `access` are not evaluated.** `allow` decides who, `rows` which rows (the caller's role shape), at the instant of the call (`$now`).
- **Column read rules still apply.** A column the caller may not read on a row is absent from that row, and so is a computed field that reads such a column on that row. In the example a player gets `answer` only on revealed questions, though the view lists it.
- **Params bind values, not logic.** Explanations and the trace print `$params.round`, not the value; the value is a typed bound parameter in SQL.
- **Bypass identities** (the secret key, view tokens, console sessions) skip `allow`, never `rows` or the projection: they evaluate `rows` as themselves (`$user.id` is `service` for the secret key) and see every column that column rules would hide.
- **Previewing as someone.** The secret key and the console may send `as: { id, role }` (a declared role, or `anonymous` with any id) to read the view exactly as that user: their `allow` decision, rows, shape and column rules.
- **Demo rooms:** a room user's rows are limited to their room, as everywhere.

## Joins

A view can nest rows of other tables into each of its rows: an answer with its question, a prediction with its game, an order line with its product. A join is **inner**: a base row is in the view only when every join finds its row. The joined row comes back under the join's name.

```ts
export default defineProject({
  name: "trivia",
  roles: ["player", "host"],
  tables: {
    questions: {
      access: "views",                       // strict: players read it through views only
      columns: {
        text: t.text(),
        reveal_at: t.datetime(),
        // the host always; players once reveal_at has passed
        answer: t.text({ read: { any: [{ role: "host" }, { where: "reveal_at <= $now" }] } }),
        published: t.bool({ default: false }),
      },
    },
    answers: {
      access: "views",
      columns: { question: t.ref("questions"), player: t.userId(), choice: t.text() },
      unique: [["question", "player"]],
    },
  },
  views: {
    my_answers: {
      table: "answers",
      allow: { role: ["player", "host"] },
      rows: { where: "player = $user.id" },
      join: {
        question: {                           // nested as row.question
          table: "questions",
          on: { id: "$row.question" },        // at most one question per answer: id is unique
          columns: ["text", "reveal_at", "answer"],
          where: "published = true",          // answers on unpublished questions are left out
        },
      },
      columns: ["choice", "created_at"],
      computed: { revealed: "question.reveal_at <= $now", correct: "question.answer = choice" },
    },
  },
});
```

A player before and after a question's `reveal_at`:

```json
{ "id": "a1", "choice": "Cusco", "created_at": "2026-10-16T19:10:00.000Z",
  "question": { "id": "q1", "text": "Capital of Peru?", "reveal_at": "2026-10-16T20:00:00.000Z" },
  "_seq": 77, "revealed": false }

{ "id": "a1", "choice": "Cusco", "created_at": "2026-10-16T19:10:00.000Z",
  "question": { "id": "q1", "text": "Capital of Peru?", "reveal_at": "2026-10-16T20:00:00.000Z", "answer": "Lima" },
  "_seq": 77, "revealed": true, "correct": false }
```

| Key of a join | Meaning |
| --- | --- |
| `table` | A declared table (the base table itself too: another row of it). |
| `on` | Joined column -> `"$row.<base column>"`, `"$user.id"`, `"$user.role"`, `"$params.<name>"` or a literal. At least one entry reads the base row. The joined columns must cover the joined table's `id`, a `unique: true` column or a `unique` column set, so a base row has at most one joined row. |
| `columns` | The nested row's columns; `id` is always in. Left out: every column. |
| `where` | A condition over the joined table's columns (`$user.*` and `$params.*` too). A joined row that fails it counts as no row. |

- **Server rights, column rules kept.** The joined table's read rule and its `access` are not evaluated: a strict table can be joined. Its [column read rules](https://dabadata.com/docs/rules/index.md#column-read-rules) do apply to the nested row, evaluated on the joined row: above, a player gets `question.answer` only once revealed, and `correct` (which reads it) with it.
- **Qualified names.** The view's `rows` and `computed` may read a joined column as `<join>.<column>`: `question.reveal_at <= $now`. Nowhere else - table rules, column rules and a join's own `where` refuse them.
- **One hop, at most four.** A join reads the base row, never another join's row. A role may replace the whole `join` map (`roles.host.join`), like `rows` and `columns`.
- **Demo rooms:** a room user's joined row must be in their room too.
- **One query.** Lists and single rows join in SQL: one query per page, whatever the number of rows.

### Joins live

A subscription to a view with joins gets the nested rows, then: a base row's change with its joined row; a joined row's change as an `update` of every row that shows it (or a `delete` when it stops matching - deleted, unpublished); and, when a joined change could let rows in that the subscription never saw (a question inserted or published), the rows again as `rows` with `reason: "join"` (a burst is one reset). At `reveal_at` the nested `answer` and `revealed` arrive as an `update` with no write. On the hosted realtime tier a joined change makes the view's lanes reload (`{ "type": "resync", "view" }`, at most once a second).

### Pitfalls

- **Uniqueness is required.** `on: { round: "$row.round" }` against a table with many rows per round is refused (`must match id or a unique column set of questions`). Declare the `unique` set you join on; it is also the index the join uses.
- **Inner joins drop rows.** An answer whose question was deleted, unpublished or never existed is not in the view at all. Use `where` for that on purpose; a "show it anyway" (left) join is not available yet.
- **Column rules cannot be joined around.** Joining a table does not show what its column rules hide; write the rule once on the table, not in each view.
- **No filter or sort on joined columns yet.** `filter` and `sort` take base columns (`"question.text": sorting on joined columns is not supported yet`). Copy the value onto the base row when callers must sort by it.
- **No `$now` in a join's `where` yet.** Whether a joined row matches does not change by time; put time in a computed field (`question.reveal_at <= $now`) or in the joined table's column rules.
- **The join's name replaces a column of that name.** Leave the base column (`question`, holding the id) out of `columns`: the nested row carries `id`.

## Routes

The views routes take the credentials of `/v1/db`: a user token, the publishable key alone (anonymous), the secret key, a view token or a console session. Every call is a read, so view tokens and console viewers may use them.

| Route | Takes | Answers |
| --- | --- | --- |
| `GET /v1/views/<name>` | Query: `params` (JSON), `where` (JSON), `orderBy`, `desc`, `limit`; `as` (JSON) for previews. | `{ "rows", "traceId", "params" }` |
| `POST /v1/views/<name>` | Body: `{ params?, where?, orderBy?, desc?, limit?, as? }` | The same. |
| `GET /v1/views/<name>/<id>` | Query: `params`, `as` | `{ "row", "traceId" }` |

`params` in the answer are the checked params: defaults filled in, missing optional ones `null`. `where` is the [filter grammar of `/v1/db`](https://dabadata.com/docs/api/index.md#db-list) (operators, `$and` / `$or`) on the view's `filter` columns; `orderBy` is the `/v1/db` syntax (`"a,-b"`) on its `sort` columns.

## Check order and errors

A call is checked in this order; the first problem answers.

1. **404** `{ "error": "not found", "message": "unknown view \"x\"" }`. A query string or body that is not valid JSON is a 400 after this.
2. **400 `as`:** `as is for the secret key and the console` (anyone else sending it), `as must be { id, role }`, `as.role: unknown role "boss"`, `as.id must be a non-empty string (the user's id)`.
3. **403** from `allow`: `{ "error": "forbidden", "explanation": "view open_questions: denied, role \"viewer\" not in [\"player\",\"host\"]", "traceId" }`.
4. **400 params**, every problem in `messages`, with the actions' wording: `params.round is required (the id of a rounds row)`, `params: unknown param "x"`, `params.round: expected a string`.
5. **400 filter and sort:** `where: column "text" is not a filter of view open_questions (filters: reveal_at)`, `orderBy: column "text" is not sortable in view open_questions (sort: reveal_at)` (`(filters: none)` / `(sort: none)` when the view declares none); then the column read rules' refusals (`where: column "answer" is not readable by this caller`, also for the view's default `orderBy`); then the filter grammar's own errors; `limit must be a positive integer`.
6. The query. A single row that is not in the view, or does not exist: 404 `row "<id>" not found in view open_questions`.

## From the client

```ts
view<R = Row>(name: string, options?: { params?, where?, orderBy?, desc?, limit? }): Promise<{ rows: R[]; traceId: number }>
viewRow<R = Row>(name: string, id: string, options?: { params? }): Promise<{ row: R; traceId: number }>
subscribe<R = Row>(target: string | { view: string; params? }, options?: { where? }, handlers?): Subscription
```

```js
const { rows } = await db.view("open_questions", {
  params: { round: roundId },
  where: { reveal_at: { $lte: "2026-10-16T21:00:00Z" } },
  limit: 20,
});
const { row } = await db.viewRow("open_questions", rows[0].id, { params: { round: roundId } });

const sub = db.subscribe({ view: "open_questions", params: { round: roundId } }, {}, {
  onRows(rows) { questions = new Map(rows.map((r) => [r.id, r])); render(); },
  onChange({ op, row }) {
    if (op === "delete") questions.delete(row.id);
    else questions.set(row.id, row);     // replace: a key missing from the new row is gone
    render();
  },
  onError(err) { console.warn(err.explanation ?? err.message); },
});
// sub.table === "open_questions", sub.view === "open_questions"
```

Refusals reject (or reach `onError`) as a `DabadataError` with the status and texts above: 404 an unknown view, 403 `forbidden` with the explanation, 400 with `e.body.messages`. See [the client reference](https://dabadata.com/docs/client/index.md#view).

## Live

On `/v1/live` a view subscription names `view` instead of `table`:

```json
{ "type": "subscribe", "id": "s1", "view": "open_questions", "params": { "round": "r1" }, "where": { "reveal_at": { "$lte": "2026-10-16T21:00:00Z" } } }
```

- Exactly one of `table` and `view`; both answer `{ "type": "error", "id", "error": "subscribe takes a table or a view, not both" }`. Refusals answer `error` with the routes' messages, or `{ "error": "forbidden", "explanation" }`.
- The first `rows` holds the view's rows, shaped, up to the view's `max`. Then every committed change of the base table goes through the view: an insert or update that passes `rows` (and the subscription's `where`) arrives as the shaped row, with computed fields evaluated at the change's instant; one that stops passing arrives as `delete` with `{ id }`. An update that changes nothing the subscriber sees is not sent.
- Computed fields and `rows` conditions that compare with `$now` / `$today` follow the clock: when `revealed` flips, the row arrives as an `update` with no write, within about a second.
- A definition change sends the rows again (`rows` with `reason: "definition"`); a view that is gone, or no longer allowed for the subscriber, ends the subscription with `error`.

**On the hosted realtime tier** (Centrifugo) a view travels on a lane per role, or on the user's own lane when what they see depends on who they are (`owner`, `$user.id`, a demo room). A lane carries every param value at once, so the server cannot filter by params there. Each `$params.<name>` must therefore bind by equality - `<column> = $params.<name>` as a top-level `and` part of a `where` that is `rows` itself (or an item of a top-level `all`), on a column without a read rule - and computed fields may not read params. The client then keeps a row only when its bound columns equal the params it subscribed with. The example's `published = true and round = $params.round` qualifies. A view whose params are used any other way gets no lane, and its live subscription's `onError` says why:

```
view <name>: the realtime tier carries views whose params bind by equality (<column> = $params.<name>); <why>
```

HTTP reads and `/v1/live` serve such views in full. Views answer no `?since=`: a view lane that lost history loads the view again over HTTP. Details for SDK authors are in the [wire protocol](https://dabadata.com/docs/protocol/index.md#centrifugo).

## MCP

The hosted MCP server has one read-only tool per view, `view_<name>`: always in the developer toolset, and in the user toolset when `allow` could let someone in. Arguments are `{ params?, where?, orderBy?, desc?, limit? }` (`params` required when a param is); `limit` is at most the smaller of the view's `max` and 200. The result is `{ view, rows, params, traceId }` (a view with joins nests its joined rows, and the tool's description names them); refusals are tool errors with the routes' texts (several param messages joined by newlines).

`describe_app` lists each view under `views.<name>` - `{ tool, table, allow, params, columns, computed, filter, sort, limit, access }`, `access` being the allow rule's decision and explanation for the caller - and adds one line of text: `view open_questions(round) over questions: view open_questions: allowed by role: role "player" in ["player","host"]`.

## Schema and trace

`GET /v1/schema` adds `views`. The secret key, view tokens and console sessions get the definitions as declared. End users get each view's caller-facing shape - what their role gets, without the `rows` rule:

```json
"views": {
  "open_questions": {
    "allow": { "role": ["player", "host"] },
    "params": { "round": { "type": "ref", "table": "rounds" } },
    "columns": ["id", "text", "options", "answer", "reveal_at"],
    "computed": ["revealed"],
    "filter": ["reveal_at"], "sort": ["reveal_at"], "orderBy": "reveal_at", "desc": false,
    "limit": { "default": 50, "max": 200 }
  }
}
```

A view with joins adds `"join": { "question": { "columns": ["id", "text", "reveal_at", "answer"] } }`: the nested columns that caller could ever see (a column whose read rule never lets them is left out). The trace explanation names the joins after the rows: `...; rows where player = $user.id and join question(questions.id = row.question, where published = true)`.

Every call writes one [trace](https://dabadata.com/docs/trace/index.md) row: `op: "read"` (`"subscribe"` for a live view subscription), `table` the base table, `values: { view, params }` (plus `as` when previewing), `rule` the view's `allow`, and `rowCount`. The explanation names the allow decision, then the rows:

```
view open_questions: allowed by role: role "player" in ["player","host"]; rows where published = true and round = $params.round
view open_questions: bypass (service key); rows where published = true and round = $params.round
view open_questions: allowed by role: role "host" in ["player","host"]; every row
```

## In the console

The **Views** page lists the environment's views, each with its base table, `allow` and `rows` rules, params, output columns, computed fields, filter, sort and limit, and the shape every role gets. **Run as** reads a view as any user: pick a role, type a user id and the params, and see that user's allow decision, rows, shape and column rules - or the 403 or 400 they would get. It is a real read (it sends `as`), so it lands in the trace; nothing is written. The choice "console (you, no as)" reads it with the console's own bypass.

## Validation messages

A broken view rejects the definition like any other problem, with its place in front (`views.<name>` or `views.<name>.roles.<role>`). Some of the messages:

```
views.x: the name is a table (views and tables share one name space)
views.x: unknown key "k" (expected table, allow, rows, join, params, columns, computed, filter, sort, orderBy, desc, limit, roles)
views.x.table: unknown table "y"
views.x.allow is required (who may read it, e.g. "authenticated" or { "role": ["player"] })
views.x.allow: owner does not apply to views (there is no row)
views.x.rows is required (use "anyone" for every row)
views.x.rows.where: unknown param "z"
views.x.params.p: default "x" does not fit int (expected an integer)
views.x.columns[2]: "salary" is not a column of questions
views.x.computed.text: the name is a column of the view
views.x.roles.y: unknown role "y"
views.x.filter[0]: "owner" is not an output column of every shape of the view
views.x.orderBy: "text" is not in sort (sort: reveal_at)
views.x.limit: default 300 is above max 200
views.x.join: at most 4 joins per view (found 5)
views.x.join.question.on: must match id or a unique column set of questions (unique: id; code)
views.x.join.question.on.id: "$row.choice" is text, the column questions.id is ref(questions)
views.x.join.question.where: $now is not supported inside a join where yet
views.x.join.question: the name is a column of the view
views.x.rows.where: qualified names (quiz.text) are only allowed in views with a join "quiz"
views.x.sort[0]: "question.text": sorting on joined columns is not supported yet
```