# Access patterns

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

Worked examples of who sees what with dabadata's rules, views and actions: friends' score predictions that appear when the game starts, the same predictions locked down, drafts, scheduled content, secret columns, teams, per-role shapes, a leaderboard and a project that mixes it all. Each one shows the definition, what every role gets back, what live subscribers receive, and the mistakes to avoid.

The examples are project files written with `defineProject` and the `t.*` column helpers, as on [Tables](https://dabadata.com/docs/tables/index.md#project-file). A hosted project sends exactly the same thing as JSON with `PUT /v1/schema`. The grammar behind every rule is on [Rules](https://dabadata.com/docs/rules/index.md); views have [their own page](https://dabadata.com/docs/views/index.md).

## Choosing a pattern

| Tool | Use it when | What it protects |
| --- | --- | --- |
| Plain rule<br>`"authenticated"`, `{ role }`, `{ owner }` | Who may read or write depends only on who is calling, or on whose row it is. | Whole rows, per caller. |
| Row condition<br>`{ where: "..." }` | Visibility depends on the row's values or on the time: published or draft, scheduled, expired. | Whole rows, per row and per instant. |
| Relation<br>`{ via: { table, match } }` | Access follows membership in another table: team members read their team's rows, editors change them. | Whole rows (or one column), per caller and per membership. |
| Column read rule<br>`columns.<c>.read` | A row is visible but one of its values is not, for some callers or until some time: a prediction, internal notes, a key. | Single values. The key is left out of the row on every surface. |
| View<br>`views.<name>` | Clients should read a fixed shape: chosen columns, computed flags, parameters, a row of another table joined in (a prediction with its game), a different shape per role, or a table nobody reads directly. | The shape of what is read. Read-only. |
| Strict table<br>`access: "views"` | End users must never touch the table directly: reads through views, writes through actions only. | The whole table, on every surface. For anything where a leak costs money or fairness. |
| Action<br>`actions.<name>` | A write has meaning beyond who may make it: a counter, stock, a deadline, one answer per player. | The integrity of writes. |

They combine. A column read rule keeps working inside a view; a view and an action are how end users reach a strict table; a row condition can sit in a table rule, a column rule or a view's `rows`. Start with the least that answers the question, and move a table to strict when its data is worth protecting from your own client code too.

## 1. Friends' predictions, revealed at kick-off

**The problem.** A group of friends plays a prediction game over a tournament. Before each game every player predicts the score. Until the game starts a player sees their own prediction, and that a friend has predicted, but not what the friend predicted. At kick-off every prediction on that game appears on every open screen at once, without anyone writing anything. Nobody - the organizer included - may see a prediction early, whether through a list, a filter, the trace or the live socket.

```ts
// your own prediction always; everyone's once the game has started
const hiddenUntilStart = { any: [{ owner: "player" }, { where: "starts_at <= $now" }] };

export default defineProject({
  name: "tournament",
  roles: ["player", "organizer"],
  tables: {
    games: {
      columns: {
        home: t.text(),
        away: t.text(),
        starts_at: t.datetime(),
        home_score: t.int({ optional: true }),
        away_score: t.int({ optional: true }),
      },
      rules: {
        read: "authenticated",
        create: { role: "organizer" },
        update: { role: "organizer" },
        delete: { role: "organizer" },
      },
    },
    predictions: {
      columns: {
        game: t.ref("games", { onDelete: "cascade" }),
        player: t.userId(),
        starts_at: t.datetime(),             // the game's start, copied onto the prediction
        home_goals: t.int({ read: hiddenUntilStart }),
        away_goals: t.int({ read: hiddenUntilStart }),
      },
      unique: [["game", "player"]],          // one prediction per player and game
      rules: {
        read: "authenticated",
        create: { all: [{ owner: "player" }, { where: "starts_at > $now" }] },
        update: { all: [{ owner: "player" }, { where: "starts_at > $now" }] },
      },
    },
  },
});
```

The prediction carries its game's `starts_at` because a rule reads its own row: `$now` is not supported inside a `via` yet, so a rule cannot ask "has this prediction's game started?" of the `games` table.

### What each role sees

At 19:00 UTC, game `g1` starts at 20:00 and game `g2` started at 18:00. Noa (`u7`) has predicted `g1`; Dana (`u4`) has predicted both. `GET /v1/db/predictions` answers Noa:

```json
{ "rows": [
    { "id": "p1", "game": "g1", "player": "u7", "starts_at": "2026-10-16T20:00:00.000Z",
      "home_goals": 2, "away_goals": 1, ... },
    { "id": "p2", "game": "g1", "player": "u4", "starts_at": "2026-10-16T20:00:00.000Z", ... },
    { "id": "p3", "game": "g2", "player": "u4", "starts_at": "2026-10-16T18:00:00.000Z",
      "home_goals": 0, "away_goals": 0, ... }
  ], "traceId": 112 }
```

| Caller | Own prediction on g1 (starts 20:00) | A friend's prediction on g1 | Any prediction on g2 (started 18:00) |
| --- | --- | --- | --- |
| player | Everything. | Every column but `home_goals` and `away_goals`: the keys are absent, not null. | Everything. |
| organizer | The same as a player. The rule has no role exception, so whoever organizes the tournament cannot look early either. |   |   |
| anonymous | No rows: the table's read rule wants a signed-in user. |   |   |
| secret key, console, view token | Everything (a bypass). |   |   |

Ask the simulator to see it decided, at any instant you like (the console's Rules page has the same form, with **Evaluate at**):

```bash
curl -X POST https://dabadata.com/v1/simulate \
  -H "Authorization: Bearer $DABADATA_SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"user":{"id":"u7","role":"player"},"table":"predictions","op":"read","row":"p2","now":"2026-10-16T19:00:00Z"}'
```

```json
{
  "decision": "allowed",
  "explanation": "read predictions: allowed by authenticated: user \"u7\" is authenticated",
  "rule": "authenticated",
  "columns": {
    "home_goals": {
      "decision": "denied",
      "explanation": "read predictions.home_goals: denied, any[0] row.player \"u4\" != user \"u7\"; any[1] where(starts_at <= $now): row.starts_at \"2026-10-16T20:00:00.000Z\" <= now \"2026-10-16T19:00:00.000Z\" is false"
    },
    "away_goals": {
      "decision": "denied",
      "explanation": "read predictions.away_goals: denied, any[0] row.player \"u4\" != user \"u7\"; any[1] where(starts_at <= $now): row.starts_at \"2026-10-16T20:00:00.000Z\" <= now \"2026-10-16T19:00:00.000Z\" is false"
    }
  },
  "now": "2026-10-16T19:00:00.000Z"
}
```

### Live

Noa is subscribed to `predictions` and holds Dana's `p2` without its goals. Within about a second of 20:00 the server sends the whole row again, now with the goals - no write happened:

```json
{ "type": "change", "id": "s1", "op": "update",
  "row": { "id": "p2", "game": "g1", "player": "u4", "starts_at": "2026-10-16T20:00:00.000Z", "home_goals": 0, "away_goals": 3, ... } }
```

The client replaces the row it holds (replace, not merge: a key missing from a new row is gone). Noa gets no message for her own `p1` at 20:00 - nothing changed for her. Before the start, a new prediction reaches the other players as an `insert` without its goals, so they see that a friend has predicted. When Dana changes only her goals before the start, the others get nothing: a change of a column they cannot see is not sent, so nobody learns she changed her mind. A player's `?since=` catch-up on this table answers `{ "reset": true, "cursor" }` (load the rows again), because time moves values in and out without a new `_seq`. See [Live subscriptions](https://dabadata.com/docs/live/index.md#time).

### Pitfalls

- **The start time on a prediction is the player's word.** A create rule reads the values being inserted, so a player who writes a later `starts_at` than the game's keeps their prediction hidden after kick-off and can still change it while the game is played. No rule can compare the prediction with its game's row at an instant. The next pattern closes this gap.
- **A filter or a sort on `home_goals` is refused** for players, even for their own predictions and for games that have started: 400 `where: column "home_goals" is not readable by this caller` (and `orderBy:`, `expect:`, `ifAbsent:` the same). Otherwise `where: { home_goals: 2 }` would show who predicted what by which rows come back. Count and compare on the client, after the goals have arrived.
- **Keep the prediction out of every other column.** No `note` column that says "2-1" without its own read rule, and no `unique` on the goals: a unique conflict names its column, so a write could probe for values.
- **Use `$now`, never the client's clock.** The rule reads the server's instant; a time the client sends is the player's to change.
- Players who may read the trace (`trace.read`) get its rows with `home_goals` and `away_goals` left out of `values`, `before` and `after`, and explanations print `row.home_goals (hidden)`.

## 2. The same predictions in strict mode

**The problem.** In pattern 1 the `predictions` table is open, so its rules have to trust what the client writes, including the start time. A strict table closes `predictions` to end users entirely: friends read predictions through a view and make them through actions that check the game's start under a row lock, at the server's instant. `games` stays an open table, so one project mixes both.

```ts
const hiddenUntilStart = { any: [{ owner: "player" }, { where: "starts_at <= $now" }] };

export default defineProject({
  name: "tournament",
  roles: ["player", "organizer"],
  tables: {
    games: {                                 // open: plain rules, as in pattern 1
      columns: {
        home: t.text(),
        away: t.text(),
        starts_at: t.datetime(),
        home_score: t.int({ optional: true }),
        away_score: t.int({ optional: true }),
      },
      rules: {
        read: "authenticated",
        create: { role: "organizer" },
        update: { role: "organizer" },
        delete: { role: "organizer" },
      },
    },
    predictions: {
      access: "views",                       // end users: the view and actions below, nothing else
      columns: {
        game: t.ref("games", { onDelete: "cascade" }),
        player: t.userId(),
        player_name: t.text(),
        starts_at: t.datetime(),             // the game's start, checked by predict
        home_goals: t.int({ read: hiddenUntilStart }),
        away_goals: t.int({ read: hiddenUntilStart }),
      },
      unique: [["game", "player"]],          // one prediction per player and game
      // no rules: end users never meet them
    },
  },
  views: {
    game_predictions: {
      table: "predictions",
      allow: "authenticated",
      rows: { where: "game = $params.game" },
      params: { game: { type: "ref", table: "games" } },
      // `player` stays out: friends see names, not user ids
      columns: ["id", "player_name", "home_goals", "away_goals", "created_at"],
      computed: { is_mine: "player = $user.id" },
      sort: ["created_at"],
      orderBy: "created_at",
    },
    my_predictions: {
      table: "predictions",
      allow: "authenticated",
      rows: { where: "player = $user.id" },
      // each prediction with its game nested: the teams, the kick-off and - once played - the score
      join: { game: { table: "games", on: { id: "$row.game" }, columns: ["home", "away", "starts_at", "home_score", "away_score"] } },
      columns: ["id", "home_goals", "away_goals", "created_at"],
      computed: {
        started: "game.starts_at <= $now",
        exact: "game.home_score = home_goals and game.away_score = away_goals",
      },
    },
  },
  actions: {
    predict: {
      allow: "authenticated",
      params: {
        game: { type: "ref", table: "games" }, starts_at: { type: "datetime" },
        home_goals: { type: "int" }, away_goals: { type: "int" },
      },
      steps: [
        // still open? decided under a row lock, at the server's instant
        { op: "check", table: "games", id: "$params.game", expect: { starts_at: { $gt: "$now" } } },
        // the start the client sent is the game's own
        { op: "check", table: "games", id: "$params.game", expect: { starts_at: "$params.starts_at" } },
        { op: "insert", table: "predictions", values: {
          game: "$params.game", player: "$user.id", player_name: "$user.name", starts_at: "$params.starts_at",
          home_goals: "$params.home_goals", away_goals: "$params.away_goals",
        } },
      ],
    },
    change_prediction: {
      allow: "authenticated",
      params: { prediction: { type: "ref", table: "predictions" }, home_goals: { type: "int" }, away_goals: { type: "int" } },
      steps: [
        // your own prediction, and its game has not started
        { op: "check", table: "predictions", id: "$params.prediction", expect: { player: "$user.id", starts_at: { $gt: "$now" } } },
        { op: "update", table: "predictions", id: "$params.prediction", values: { home_goals: "$params.home_goals", away_goals: "$params.away_goals" } },
      ],
    },
  },
});
```

The client already holds the game from its `games` list, so predicting is one call:

```js
await db.action("predict", { game: game.id, starts_at: game.starts_at, home_goals: 2, away_goals: 1 });
```

### What each role sees

| Who, and the call | Answer |
| --- | --- |
| Any end user:<br>`GET /v1/db/predictions` | 403 `read predictions: denied, predictions is read through views only (access: views)` |
| Any end user:<br>`POST /v1/mutate` into `predictions` | 403 `insert predictions: denied, predictions is written through actions only (access: views)` |
| player or organizer:<br>`db.view("game_predictions", { game })` | Every prediction on that game: `id`, `player_name`, `created_at`, `is_mine`; `home_goals` and `away_goals` on their own prediction, and on everyone's once the game has started. |
| anonymous:<br>the same view | 403 `view game_predictions: denied, anonymous is not authenticated` |
| player or organizer:<br>`db.view("my_predictions")` | Their own predictions, each with its game nested under `game` (`home`, `away`, `starts_at`, the score once entered), `started`, and `exact` once the score is in. A prediction whose game was deleted is not there (the join is inner). |
| player or organizer:<br>`db.action("predict", ...)` | OK the first time; 409 at `index: 0` once the game has started; 409 at `index: 1` when `starts_at` is not the game's; 409 at `index: 2` (the unique pair) for a second prediction on the same game. |
| player or organizer:<br>`db.action("change_prediction", ...)` | OK on their own prediction before the start; 409 at `index: 0` on someone else's prediction or after the start. |

Noa's view rows for `g1` at 19:00, and Dana's row after 20:00:

```json
{ "id": "p1", "player_name": "Noa", "home_goals": 2, "away_goals": 1, "created_at": "2026-10-16T17:05:12.000Z", "is_mine": true, "_seq": 41 }
{ "id": "p2", "player_name": "Dana", "created_at": "2026-10-16T18:31:40.000Z", "is_mine": false, "_seq": 42 }

{ "id": "p2", "player_name": "Dana", "home_goals": 0, "away_goals": 3, "created_at": "2026-10-16T18:31:40.000Z", "is_mine": false, "_seq": 42 }
```

Noa's `my_predictions` row once `g1` has been played and the organizer has entered the score:

```json
{ "id": "p1", "home_goals": 2, "away_goals": 1, "created_at": "2026-10-16T17:05:12.000Z",
  "game": { "id": "g1", "home": "Lions", "away": "Hawks", "starts_at": "2026-10-16T20:00:00.000Z", "home_score": 2, "away_score": 1 },
  "_seq": 41, "started": true, "exact": true }
```

The secret key and the console read and write `predictions` directly as before; for end users the table simply is not there. `GET /v1/schema` for a player lists `games` only, and the view in the shape that player gets; the MCP user toolset has `games_*` tools but no `predictions_*` tools, and reaches predictions through `view_game_predictions`, `action_predict` and `action_change_prediction`.

### Live

`db.subscribe({ view: "game_predictions", params: { game } }, {}, handlers)` receives the shaped rows, then every change of `predictions` that passes the view. A friend's new prediction arrives with their name and no goals; at `starts_at` the goals of every prediction appear as an `update` with no write, within about a second. A table subscription to `predictions` from a player is refused with the strict 403 text. `my_predictions` follows the games it joins too: when the organizer enters a score, each prediction on that game arrives as an `update` with the new `game` and `exact`, and `started` flips at kick-off with no write. The view works on the hosted realtime tier too, because its only param binds by equality (`game = $params.game`).

### Pitfalls and good habits

- **The deadline lives in the actions**, compared with the server's `$now` under a row lock: a prediction that arrives one millisecond after kick-off fails at step 0, whatever the client's clock says.
- **The start time is checked, not trusted.** An action step cannot copy a value from one row into another, so the client sends the game's `starts_at` and step 1 proves it is the game's own. A wrong or stale one is a 409 at `index: 1`: read the game again and resend.
- **Set the start time before predictions open.** Each prediction keeps the start it was made with, so moving a game later does not move the reveal of predictions already made.
- **"Once" is a unique constraint**, not a read-then-write in the client: two predictions sent at the same moment cannot both land.
- **Forgetting `access` fails loudly.** A table without `access: "views"` needs `rules.read`, so a forgotten line is a validation error (`predictions: no read rule (rules.read must be explicit, even "nobody")`) rather than an open table.
- **Join instead of copying for display.** `my_predictions` shows the teams and the score from `games` through a [join](https://dabadata.com/docs/views/index.md#joins); only what a rule must read on the row itself (`starts_at`, for the column rule) is copied onto the prediction. A join reads with the server's rights, and the joined table's own column rules still apply to the nested row.
- The column rule on the goals still applies inside the view: the view does not have to repeat it. It reads the whole row before the view shapes it, so `player` can stay out of the view's columns and the owner part of the rule still works.
- `trace.read` readers see trace rows of strict tables too (redacted by column read rules only). Give `trace.read` to nobody who plays.

## 3. Drafts and published posts

**The problem.** Everyone reads published posts; a draft is visible to its author only.

```ts
export default defineProject({
  name: "blog",
  roles: ["member", "editor"],
  tables: {
    posts: {
      columns: {
        title: t.text(),
        body: t.text(),
        status: t.text({ default: "draft" }),
        author: t.userId(),
      },
      checks: { status_known: "status = 'draft' or status = 'published'" },
      rules: {
        read: { any: [{ where: "status = 'published'" }, { owner: "author" }] },
        create: "authenticated",
        update: { any: [{ owner: "author" }, { all: [{ role: "editor" }, { where: "status = 'published'" }] }] },
        delete: { owner: "author" },
      },
    },
  },
});
```

### What each role sees

| Caller | A list reads |
| --- | --- |
| member `u7` | `read posts: allowed, rows where status = 'published' or author == "u7"` |
| anonymous | `read posts: allowed, rows where status = 'published'` |

Opening someone else's draft by id answers 403 with the reason:

```
read posts: denied, any[0] where(status = 'published'): row.status "draft" = 'published' is false; any[1] row.author "u3" != user "u7"
```

Editors may fix typos in published posts but not touch someone else's draft:

```
update posts: denied, any[0] row.author "u3" != user "u9"; any[1] all[1] where(status = 'published'): row.status "draft" = 'published' is false
```

### Live

When the author publishes (`status` becomes `'published'`), every other subscriber receives the post as an `update` - treat an update of an unknown id as an insert. Unpublishing sends them a `delete` with `{ id }`; the author keeps the row.

### Pitfalls

- A write answers with the row only when the writer may read it afterwards; otherwise the answer is `{ "id": "..." }`. Here every writer can, but a rule that lets someone write what they cannot read gets exactly that.
- Use a check (or actions) to keep `status` to known values: a typo like `'publised'` would silently make a post private.
- Lists turn the rule into SQL: `status = 'published' or author = $1`. On a large table, index `status` and `author` (below, [best practices](https://dabadata.com/docs/access-patterns/index.md#best-practices)).

## 4. Scheduled and expiring content

**The problem.** A notice appears at `publish_at` and disappears at `expires_at`, if it has one. Editors see everything, including what is scheduled.

```ts
export default defineProject({
  name: "noticeboard",
  roles: ["member", "editor"],
  tables: {
    notices: {
      columns: {
        title: t.text(),
        body: t.text(),
        publish_at: t.datetime(),
        expires_at: t.datetime({ optional: true }),
      },
      rules: {
        read: {
          any: [
            { role: "editor" },
            { where: "publish_at <= $now and (expires_at is null or expires_at > $now)" },
          ],
        },
        create: { role: "editor" },
        update: { role: "editor" },
        delete: { role: "editor" },
      },
    },
  },
});
```

### What each role sees

An editor gets every row. Anyone else gets `read notices: allowed, rows where publish_at <= $now and (expires_at is null or expires_at > $now)`, evaluated at the instant of the call. A notice scheduled for 20:00, opened at 19:00:

```
read notices: denied, any[0] role "member" not in ["editor"]; any[1] where(publish_at <= $now and (expires_at is null or expires_at > $now)): row.publish_at "2026-10-16T20:00:00.000Z" <= now "2026-10-16T19:00:00.000Z" is false
```

### Live

At `publish_at` the notice arrives to subscribers as an `update` (new to them); at `expires_at` it leaves as a `delete`. Both come within about a second of the instant, with no write and no trace row (the realtime log records a `fanout` with detail `time`). Editors get no time messages: nothing changes for them.

### Pitfalls

- A `datetime` column compares with `$now`, a `date` column with `$today` (the UTC date): `due >= $today`. Mixing them is refused (`column "publish_at" is datetime; compare it with $now`).
- `expires_at > $now` hides the notice from the very instant it expires; `>=` would keep it for that one millisecond. Datetimes compare at millisecond precision; write them with a zone (`Z` or an offset).
- Do not add a `published` flag that a job flips: the condition is exact at every call, and live clients follow it without one.
- Index `publish_at` when the table grows: every list filters on it.

## 5. Secret columns

**The problem.** A row is shared, but some of its values are not: a webhook URL only its owner may see, notes for admins, an API key no client may ever read back.

```ts
export default defineProject({
  name: "hooks",
  roles: ["member", "admin"],
  tables: {
    integrations: {
      columns: {
        name: t.text(),
        owner: t.userId(),
        webhook_url: t.text({ read: { owner: "owner" } }),
        internal_notes: t.text({ optional: true, read: { role: "admin" } }),
        api_key: t.text({ optional: true, read: "nobody" }),
      },
      rules: {
        read: "authenticated",
        create: "authenticated",
        update: { any: [{ role: "admin" }, { owner: "owner" }] },
        delete: { role: "admin" },
      },
    },
  },
});
```

### What each role sees

| Caller | `name`, `owner` | `webhook_url` | `internal_notes` | `api_key` |
| --- | --- | --- | --- | --- |
| member, someone else's row | yes | - | - | - |
| member, own row | yes | yes | - | - |
| admin | yes | own rows only | yes | - |
| secret key, console, view token | yes | yes | yes | yes |

A dash is an absent key. The owner may set `api_key` when creating the row; the answer to that write leaves it out. Your own backend reads it with the secret key, and so do action steps, which run with the server's rights (the rows an action returns are projected for the caller again).

### Pitfalls

- `read: "nobody"` hides a value from end users. It is not encryption: the secret key, the console, view tokens and SQL see it, and it sits in Postgres like any column. Encrypt in your backend what must be unreadable even to you.
- **Passwords do not belong here.** When dabadata hosts your users' accounts ([sign-in for your app's users](https://dabadata.com/docs/end-user-auth/index.md)), it keeps their passwords itself, never in your tables, so there is no password column to hide. Do not add one.
- A filter on a hidden column is refused - even for the owner on their own row, because a row-dependent rule is never "readable" ahead of time: `where: column "webhook_url" is not readable by this caller`. Filter on visible columns.
- MCP `describe_app` leaves out columns a caller can never see (`api_key` for everyone, `internal_notes` for members); the rules themselves stay visible.
- The trace stores what was written. Callers who read it through `trace.read` get those values removed; the secret key and the console see them.

## 6. Teams and workspaces

**The problem.** People work in teams. Every member of a team reads its tasks and may add tasks to it; only the team's editors change or delete them; nobody sees another team's tasks. Membership lives in its own table, with a role per member.

```ts
const membership = { table: "team_members", match: { team: "$row.team", user: "$user.id" } };
const editors = { ...membership, where: "role = 'editor'" };

export default defineProject({
  name: "teams",
  roles: ["member", "admin"],
  tables: {
    teams: {
      columns: { name: t.text() },
      rules: { read: "authenticated", create: { role: "admin" } },
    },
    team_members: {
      columns: { team: t.ref("teams", { onDelete: "cascade" }), user: t.userId(), role: t.text({ default: "member" }) },
      unique: [["team", "user"]],
      checks: { known_role: "role = 'member' or role = 'editor'" },
      // Members see their own memberships; only admins add or remove people.
      rules: { read: { owner: "user" }, create: { role: "admin" }, update: { role: "admin" }, delete: { role: "admin" } },
    },
    tasks: {
      columns: { team: t.ref("teams", { onDelete: "cascade" }), title: t.text(), done: t.bool({ default: false }) },
      rules: {
        read: { via: membership },
        create: { via: membership },
        update: { via: editors },
        delete: { via: editors },
      },
    },
  },
});
```

A `via` rule holds when a row of `table` exists whose `match` columns hold the given values: `$row.team` is the task's own `team` (on create, the value being inserted), `$user.id` the caller. Its `where` is a condition on the membership row. The full grammar is on [Rules: relations](https://dabadata.com/docs/rules/index.md#relations).

### What each role sees

Say `u1` is an editor of team `t1`, `u2` a plain member of `t1`, and `u3` an editor of `t2`.

| Caller | Reads | Writes |
| --- | --- | --- |
| editor `u1` | The tasks of `t1`: `read tasks: allowed, rows where via team_members(team = row.team, user = "u1")` | Adds, edits and deletes tasks of `t1`. |
| member `u2` | The tasks of `t1`. | Adds tasks to `t1`; an edit is refused (below). |
| editor `u3` | Only `t2`'s tasks. Opening a `t1` task by id answers 403. | Cannot add to `t1`. |
| anonymous | Nothing. | Nothing. |

The refusals name the lookup that failed:

```
read tasks: denied, via team_members(team = row.team "t1", user = user "u3"): no matching row
create tasks: denied, via team_members(team = values.team "t1", user = user "u3"): no matching row
update tasks: denied, via team_members(team = row.team "t1", user = user "u2", where role = 'editor'): no matching row
read tasks: denied, via team_members(team = row.team "t1", user = user null): anonymous has no id
```

An allowed read says `read tasks: allowed by via team_members(team = row.team "t1", user = user "u1"): a matching row exists`. Removing someone from a team takes effect on their next request: the lookup runs at every request (for a write, inside its transaction).

### Live

Being added to a team makes its tasks appear: the new member's open subscriptions on `tasks` receive their rows again, `{ "type": "rows", "id", "rows", "reason": "relation" }`, within about 50 ms. Being removed sends the rows again without that team's tasks. No other user receives anything. A task moved to another team (its `team` changes) is an ordinary change: a `delete` for the old team's members, an `update` for the new team's. On the realtime tier the reload arrives as `{ "type": "resync", "table": "tasks" }` on the user's own lane; the client reloads by itself.

### Pitfalls

- **Index the lookup.** Every read of `tasks` asks `team_members` for `(team, user)`. `unique: [["team", "user"]]` gives that lookup its index and keeps one membership per person and team.
- **The membership table's own rules are not consulted.** The lookup reads `team_members` with the server's rights, so whoever may write it decides who is in a team. Protect it with its own rules (here: admins only), or make it strict (`access: "views"`) and add people through an action that checks the inviter is an editor of the team. A strict membership table works for `via` as well as an open one.
- **One hop.** A relation follows one table. "Members of the project of this task's milestone" needs the project on the task (copy it in when the task is created, from an action) - a `via` does not chain.
- **`$row.*` goes in `match`**, conditions on the membership row in `where`; `$now` is not supported inside `where` yet (keep time conditions on the task: `all: [{ via: membership }, { where: "due_at > $now" }]`).
- A column rule can use a relation too (`notes: t.text({ read: { via: editors } })`), but such a column may not be filtered or sorted on by end users: it is not readable ahead of time.

## 7. Same table, different shapes per role

**The problem.** One staff table. Everyone may look people up; managers also see the office; HR sees salary and the latest review.

```ts
export default defineProject({
  name: "staff",
  roles: ["member", "manager", "hr"],
  tables: {
    employees: {
      access: "views",
      columns: {
        name: t.text(),
        team: t.text(),
        title: t.text(),
        office: t.text({ optional: true }),
        salary: t.int({ optional: true, read: { role: "hr" } }),
        review: t.text({ optional: true, read: { role: "hr" } }),
      },
    },
  },
  views: {
    directory: {
      table: "employees",
      allow: "authenticated",
      rows: "anyone",
      columns: ["id", "name", "team", "title"],
      filter: ["team"],
      sort: ["name"],
      orderBy: "name",
      roles: {
        manager: { columns: ["id", "name", "team", "title", "office"] },
        hr: { columns: ["id", "name", "team", "title", "office", "salary", "review"] },
      },
    },
  },
});
```

### What each role sees

| Role | `db.view("directory", { where: { team: "design" } })` rows |
| --- | --- |
| member | `{ id, name, team, title, _seq }` |
| manager | `{ id, name, team, title, office, _seq }` |
| hr | `{ id, name, team, title, office, salary, review, _seq }` |
| anonymous | 403 `view directory: denied, anonymous is not authenticated` |

`GET /v1/schema` shows each caller the shape they get (their columns, computed names, filters, sorts and limits - not the `rows` rule), so a client can build its table headers from it. In the console, the Views page shows every role's shape side by side and runs the view as any user.

### Live

Each subscriber gets rows in their own shape. A raise changes `salary` only, so members and managers receive nothing - nothing they can see changed - while HR gets the update.

### Pitfalls

- A role's `columns` replaces the view's list; it does not add to it. Write every column out.
- `filter` and `sort` columns must be in every shape: `views.directory.filter[0]: "salary" is not an output column of every shape of the view`.
- A view shapes only the view. The column read rules on `salary` and `review` protect every other surface too (the trace, actions' results, the table itself if it ever becomes open); keep both.

## 8. A public leaderboard with computed fields

**The problem.** Anyone may see the top scores, sorted. A signed-in player should see their own line highlighted - without the board publishing every player's user id.

```ts
export default defineProject({
  name: "arcade",
  roles: ["player"],
  tables: {
    scores: {
      access: "views",                  // written by your game server with the secret key
      columns: {
        player: t.userId(),
        player_name: t.text(),
        score: t.int({ default: 0 }),
      },
    },
  },
  views: {
    leaderboard: {
      table: "scores",
      allow: "anyone",
      rows: "anyone",
      columns: ["id", "player_name", "score"],
      computed: { is_me: "player = $user.id" },
      sort: ["score"],
      orderBy: "score",
      desc: true,
      limit: { default: 10, max: 100 },
    },
  },
});
```

### What each role sees

```json
{ "rows": [
    { "id": "s4", "player_name": "Dana", "score": 980, "is_me": false, "_seq": 77 },
    { "id": "s9", "player_name": "Noa",  "score": 940, "is_me": true,  "_seq": 75 }
  ], "params": {}, "traceId": 301 }
```

That is the answer for the player behind `s9`. Anonymous visitors get the same rows with `is_me: false` everywhere (`$user.id` is null for them, and a comparison with null is false). `player` is not an output column, yet `is_me` can read it: computed fields see every column of the base table.

### Live

Subscribers get each new or changed score in the board's shape, with `is_me` evaluated for them. Because what a subscriber sees depends on who they are, the realtime tier carries this view on each signed-in user's own lane (anonymous visitors share one).

### Pitfalls

- Scores are worth cheating for: keep the table strict and write it from your game server, or through an action whose steps decide the points. A client that may write its own score will.
- Computed fields are booleans in this version: flags like `is_me`, `revealed`, `expired`.
- `limit` above the view's `max` is clamped to it, so a client cannot pull the whole table.

## 9. A mixed project: strict where it matters

**The problem.** A shop with chat and profiles that work fine on plain rules, and orders and payments that must never be reachable from client code. Strictness is per table: **each table chooses, and strict and plain tables live side by side in one project.**

```ts
export default defineProject({
  name: "shop",
  roles: ["customer", "staff"],
  access: "open",                       // the project default (also what you get without it)
  tables: {
    profiles: {
      columns: { user: t.userId({ unique: true }), display_name: t.text() },
      rules: { read: "authenticated", create: "authenticated", update: { owner: "user" } },
    },
    chat: {
      columns: { body: t.text(), author: t.userId() },
      rules: {
        read: "authenticated",
        create: "authenticated",
        delete: { any: [{ role: "staff" }, { owner: "author" }] },
      },
    },
    orders: {
      access: "views",                  // strict: created by your checkout backend
      columns: { customer: t.userId(), total_cents: t.int(), status: t.text({ default: "new" }) },
    },
    payments: {
      access: "views",
      columns: { order: t.ref("orders"), amount_cents: t.int(), provider_ref: t.text({ read: "nobody" }) },
    },
  },
  views: {
    my_orders: {
      table: "orders",
      allow: { role: "customer" },
      rows: { owner: "customer" },
      columns: ["total_cents", "status", "created_at"],
    },
  },
  actions: {
    cancel_order: {
      allow: { role: "customer" },
      params: { order: { type: "ref", table: "orders" } },
      steps: [
        { op: "update", table: "orders", id: "$params.order", values: { status: "cancelled" },
          expect: { customer: "$user.id", status: "new" } },
      ],
    },
  },
});
```

The inverse works as well: make strict the default and open only what is harmless.

```ts
export default defineProject({
  name: "clinic",
  roles: ["patient", "doctor"],
  access: "views",                      // every table strict unless it says otherwise
  tables: {
    appointments: {
      columns: { patient: t.userId(), doctor: t.userId(), starts_at: t.datetime(), notes: t.text({ optional: true }) },
    },
    faq: {
      access: "open",                   // the one table end users read directly
      columns: { question: t.text(), answer: t.text() },
      rules: { read: "anyone" },
    },
  },
  views: {
    my_appointments: {
      table: "appointments",
      allow: "authenticated",
      rows: { any: [{ owner: "patient" }, { owner: "doctor" }] },
      columns: ["patient", "doctor", "starts_at"],
      roles: { doctor: { columns: ["patient", "doctor", "starts_at", "notes"] } },
    },
  },
});
```

- A table's own `access` wins over the project's; without either it is `"open"`. A storage bucket's `files_<bucket>` table and the profiles `users` table are always open (their own rules decide).
- Views and actions may read and write any table, strict or not, and an open table may `ref` a strict one.
- Turning a table strict ends end users' open subscriptions of it at once (they get the strict `error`); view subscriptions over it reload and keep working.
- The secret key, console sessions, view tokens, action steps and views are never affected by `access`. It is about end users only.

## 10. Counters, capacity and stock

When the problem is what a write means rather than who makes it - seats left, stock, a vote count - neither a rule nor a view is the answer: declare an action and give clients no direct write to the counter. The full worked example (RSVPs against a capacity, fifty clicks for thirty seats) is in [Rules: actions](https://dabadata.com/docs/rules/index.md#actions-protecting-counters-capacity-and-stock); for the simplest stock counter, a table check plus `{ "$dec": 1 }` is enough, see [Safe concurrent writes](https://dabadata.com/docs/concurrency/index.md).

## Best practices

- **Least privilege.** Start from `"nobody"` or a strict table and open what a screen needs. A missing write rule already means nobody.
- **Explicit read rules.** Every open table states its `read`; every secret value has a column `read` rule. Never rely on a client not showing a field.
- **Prefer server time.** Compare with `$now` and `$today` in rules and `"$now"` in action steps. A time the client sends is the client's to choose.
- **Test before you ship.** The console's Rules page has a matrix and a simulator; set **Evaluate at** to see a rule at a future instant (`POST /v1/simulate` with `now`). The Views page runs a view as any user. The MCP `simulate` tool does the same for an agent.
- **Watch the trace.** Every decision is recorded with its explanation; a surprise "allowed" is the first thing to look for. Time-driven pushes show up in the realtime log.
- **Index what rules filter on.** Row conditions and owner rules become the WHERE clause of every list. A definition declares no plain indexes; on a self-hosted server add them in Postgres (`create index on p_blog.posts (status)`); a `unique` already is one.
- **Do not filter on hidden columns.** It is refused, by design. Filter and sort on visible columns, and declare a view's `filter` and `sort` from them.
- **Keep secrets out of unique constraints** and out of other columns: a unique conflict names its column, and a copy has no read rule.
- **Use strict tables for anything where a leak costs money or fairness**: answers, scores, payments, personal data. Reads go through views, writes through actions, and nothing else reaches the table.
- **Put meaning into actions.** Deadlines, "once per person", counters and stock belong in an action's steps, under a row lock, not in client code.