Concepts

Access patterns

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. A hosted project sends exactly the same thing as JSON with PUT /v1/schema. The grammar behind every rule is on Rules; views have their own page.

Choosing a pattern

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.

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

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

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

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

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

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

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.

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:

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

What each role sees

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

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

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

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

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

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.

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.

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

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

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.

What each role sees

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

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.

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

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.

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

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

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.

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; for the simplest stock counter, a table check plus { "$dec": 1 } is enough, see Safe concurrent writes.

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.