Row-Level Policies

## Row-Level Policies — Per-Record Access Control

Every lesson so far used a root or scoped API key, which sees every record on an agent it can reach. Row-level policies add a second, finer-grained layer: which rows within that agent a specific end-user JWT is allowed to see or write, resolved per-request against the caller's own identity.

> Prerequisite: Lesson 58 (User Auth — register, login, magic links). Row policies match against the JWT that add-on issues — you need a real sub claim to filter on.

---

## The mental model

A key (root or scoped) answers *"can this caller reach this endpoint at all?"* A row policy answers a different question, one layer deeper: *"of the rows this endpoint would otherwise return, which ones does this specific end-user get to see?"* The two checks are independent and stack — a scoped key still needs read:agents/orders to query the orders agent at all; a row policy then decides which of those rows a given signed-in user actually receives.

---

## Create a row policy

curl -X POST http://localhost:7475/v1/policies/row \
  -H "Authorization: Bearer spx_root_YOUR_ROOT_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "owner-isolation",
    "agent_id": "orders",
    "cmd": "all",
    "filter_template": { "field": "owner_id", "op": "eq", "value": "$jwt.sub" },
    "priority": 1
  }'

Response (201 Created): `json { "id": "rp_a3f9...", "name": "owner-isolation", "agent_id": "orders", "cmd": "all", "filter_template": { "field": "owner_id", "op": "eq", "value": "$jwt.sub" }, "priority": 1, "created_at_ms": 1758000000000 } `

filter_template is an ordinary SaQL filter (Lesson 12-13's operators and AND/OR/NOT all work here) — the only new piece is the $jwt.* placeholder, resolved against the caller's claims at request time, not at policy-creation time.

---

## The three policy types (cmd)

update exists in the schema for forward compatibility but is never evaluated — every SapixDB write is an append, there is no in-place update path to intercept. Use all for full row isolation.

---

## $jwt.* placeholders — and $lookup.* for live data

An unknown $jwt.* path resolves to null (matches nothing) rather than erroring — a typo in a policy fails closed, it doesn't crash the request.

Live policy lookups: $jwt.* is snapshotted at token issuance, so it can lag reality until the token refreshes — wrong for anything where the true current state can change mid-session (e.g. "does this user currently own this business profile"). A lookups entry closes that gap by running a fresh, single-record query against another agent on *every* enforcement:

{
  "name": "listings-by-owned-business",
  "agent_id": "listings",
  "cmd": "select",
  "filter_template": { "field": "business_id", "op": "eq", "value": "$lookup.owned_biz" },
  "lookups": [{
    "name": "owned_biz",
    "agent_id": "businesses",
    "match_field": "owner_id",
    "match_value": "$jwt.sub",
    "return_field": "business_id"
  }]
}

Use $jwt.* for anything where staleness up to the token's expiry is fine (cheaper — no extra query); reach for a lookup only where it would be a real correctness problem. A $lookup.* string can't be chained into another lookup's own match_value (always resolves to null there instead), and a lookup naming the same agent as the policy it belongs to also resolves to null without ever querying — both by design, not omissions.

---

## See it in action

With the owner-isolation policy above active on orders, and Alice's JWT from Lesson 58 (sub: "usr_alice"):

`bash # Alice queries orders — only rows where owner_id == "usr_alice" come back, # even though the underlying agent has orders from every user. curl -X POST http://localhost:7475/v1/agents/orders/query \ -H "Authorization: Bearer eyJ...alice-jwt..." \ -H "Content-Type: application/json" \ -d '{ "type": "scan", "limit": 50 }'

# Alice tries to write an order she doesn't own — rejected. curl -X POST http://localhost:7475/v1/agents/orders/records/json \ -H "Authorization: Bearer eyJ...alice-jwt..." \ -H "Content-Type: application/json" \ -d '{ "data": { "owner_id": "usr_bob", "item": "stolen" } }' # → 403 Forbidden — "row policy 'owner-isolation' denied this write" `

A single-record fetch by hash (GET /v1/agents/orders/records/:hash) returns 404, not 403, when the policy excludes it — this avoids confirming to an unauthorized caller that the record exists at all.

---

## List / get / delete

GET    /v1/policies/row              # → [RowPolicy, ...] (a plain array, across every registered agent)
GET    /v1/policies/row/:id          # → RowPolicy | 404
DELETE /v1/policies/row/:id          # → 204 No Content (idempotent — 204 even if the id doesn't exist)

GET /v1/policies (the combined listing endpoint from other policy types) includes a "row" array alongside "retention" and "acl".

---

## The bypass rule — read this twice

A caller with no JWT sees every row, unfiltered. This is not a bug — root keys and scoped API keys never present a JWT at all, so they fall outside row-policy enforcement entirely by construction, the same way your backend service account in Lesson 34 was never meant to be filtered. Row policies exist to restrict end-user access once you forward their JWT; they do nothing for service-to-service calls.

> This means the guarantee depends on your own backend's discipline. If your backend calls SapixDB with its own API key instead of forwarding the end-user's JWT, row policies never engage — the request looks exactly like a trusted service call. Always forward the user's Authorization: Bearer <jwt> header through to SapixDB when the request should be scoped to that user.

---

## Where this applies (and where it deliberately doesn't)

Row-policy filtering runs on every ordinary read and write path: POST /v1/agents/:id/query (all query types, including indexed fast paths), GET /v1/agents/:id/records/:hash, POST /v1/agents/:id/records*, POST /v1/transact (its claim op gets both Select-side candidate filtering and Insert-side checking on the result, same as POST /v1/agents/:id/claim; a write_if using patch instead of data gets the Insert-side check against the merged result), the per-agent SSE stream (Lesson 40), and durable publications (Lesson 42).

It deliberately does not apply to a handful of raw audit/debug views — the Control Plane dashboard, the HIPAA audit report (Lesson 46), the raw strand-records dump. A "row-filtered audit log" would be a contradiction — those views are supposed to show everything. Instead they require an explicit admin:<resource> scope on the calling key (e.g. admin:control, admin:hipaa) — a key-level check, not a row-level one. If an endpoint 403s and mentions an admin: scope, that's this mechanism, not a row policy.

---

## A related but distinct check: agent-scoped indirection

A few endpoints — scheduled crons (Lesson 37), field indexes (Lesson 18-19), publication subscriptions (Lesson 42) — accept an agent_id inside the request body or query rather than in the URL path. Holding the coarse scope that lets you call the endpoint at all (say, write:crons) is not enough on its own: SapixDB additionally checks read:agents/<source> / write:agents/<target> for the specific agent named inside the request, the same way an ordinary per-tenant call is checked. A key scoped only to write:crons, with no relationship to any agent, cannot create a cron that reads one it has no business touching.

This is a key-scope check, same family as admin:<resource> above — not a row policy, which is JWT-based and per-record. The three mechanisms stack: key scope gets you to the endpoint, agent-scope indirection (where it applies) confirms you're allowed to name that specific agent, and a row policy (if one exists and you presented a JWT) filters which rows you actually see.

---

## Challenge

1. Create an owner-isolation row policy on an agent from an earlier lesson (e.g. users from Lesson 4), using $jwt.sub against an owner_id field.
2. Write two records with different owner_id values using your root key (root bypasses the policy, so both writes succeed regardless of owner_id).
3. Log in as a test user (Lesson 58) whose sub matches one of those owner_id values, and query the agent with that JWT — confirm you only see the matching record.
4. Try writing a record with someone else's owner_id using your own JWT — confirm it's rejected with 403.
5. Delete the policy and re-run your query with the same JWT — confirm you now see every record again.

---

See also: Lesson 58 (issuing the JWTs this lesson filters on), Lesson 12-13 (the filter syntax filter_template reuses), Lesson 34 (key scopes vs. row policies — two different layers).