Use cases

A few concrete shapes where row-level security in SQLite earns its keep, with the policy that expresses each.

Per-user data in a single-file app

A local-first or desktop app (Electron, Tauri, a CLI) that stores everyone's data in one SQLite file, but each profile should only see its own. Protect each table by owner and bind the active profile at startup:

{ "table": "notes",
  "select": "owner_id = rls_ctx('user_id')",
  "insert": "NEW.owner_id = rls_ctx('user_id')",
  "update": { "using": "OLD.owner_id = rls_ctx('user_id')",
              "check": "NEW.owner_id = rls_ctx('user_id')" },
  "delete": "OLD.owner_id = rls_ctx('user_id')" }

Even code paths you forget — an export routine, a sync job — stay scoped.

Multi-tenant SaaS on SQLite

Many small tenants, one database (or one per shard). A tenant_id on every row plus a tenant claim guarantees no query ever crosses tenants, no matter who writes it:

{ "table": "invoices",
  "select": "tenant_id = rls_ctx('tenant_id')",
  "insert": "NEW.tenant_id = rls_ctx('tenant_id')",
  "update": { "using": "OLD.tenant_id = rls_ctx('tenant_id')",
              "check": "NEW.tenant_id = rls_ctx('tenant_id')" },
  "delete": "OLD.tenant_id = rls_ctx('tenant_id')" }

Bind {"tenant_id": 7} from the tenant resolved off the request's subdomain or API key. A bug in one endpoint can't leak another tenant's invoices.

Role-based visibility

Regular users see their own rows; support staff and admins see everything. One expression, no separate "admin query path":

"select": "owner_id = rls_ctx('user_id') OR rls_ctx('role') IN ('admin','support')"

Because the same view serves both, you can't accidentally ship an admin screen that forgets the filter for normal users — there's only one filter.

Team / group membership

Rows belong to a team, and a user belongs to several. Carry the memberships as a claim array and test membership in the policy:

{ "table": "projects",
  "select": "EXISTS (SELECT 1 FROM json_each(rls_ctx('teams')) WHERE value = team)",
  "insert": "EXISTS (SELECT 1 FROM json_each(rls_ctx('teams')) WHERE value = NEW.team)" }

Bind {"user_id": 1, "teams": ["infra", "ml"]}. Membership changes are just different claims — no policy redeploy.

Sharing beyond ownership

Ownership is the default, but a policy can consult a share table for exceptions — documents you own or that have been shared with you:

"select": "owner_id = rls_ctx('user_id') OR id IN (SELECT doc_id FROM shares WHERE user_id = rls_ctx('user_id'))"

(Protect shares too, or keep it readable, depending on whether users should see who else a document is shared with.)

Defense-in-depth behind an ORM

Already filtering in your application or ORM? Row-level security makes that filter the belt and this the suspenders. If an ORM query is mis-scoped, or a raw query slips past the ORM, the database still refuses to return other users' rows. The failure mode changes from "silent leak" to "empty result or loud error."

A shared read-only catalog

Not everything is per-user. A reference table everyone reads but only privileged setup writes:

{ "table": "plans", "select": true }

Omitting the write fields makes it read-only to every guarded connection.

---

The common thread: the rule lives in the database, once, and holds for every path that reaches the table. See Writing policies to compose your own, or a language guide to wire it into your stack.