<a id="dads-frameworksqlite-access"></a>

# `@dads-framework/sqlite-access`

Check permissions, list accessible resources, resolve transitive subjects, and commit relationship changes alongside business data in SQLite.

<a id="why-sqlite"></a>

## Why SQLite

Sometimes the whole application fits on one machine or inside an embedded store, and adding a separate authorization cluster creates more moving parts than the product has. Keep relationship facts next to application rows, verify access inside the same `BEGIN IMMEDIATE` write transaction, and drop the outbox.

The adapter runs on Node.js built-in `node:sqlite` (`DatabaseSync`) with zero native addons. Callers can also wrap `better-sqlite3`, Drizzle SQLite, or libSQL behind the `SqliteExecutor` port.

<a id="try-it"></a>

## Try it

Use Node.js 22.5 or later. Install the packages and run the script against an in-memory SQLite database.

```sh
npm install @dads-framework/access @dads-framework/sqlite-access zod
npm install -D tsx
```

Save as `check-sqlite-access.mts` and run `npx tsx check-sqlite-access.mts`.

```ts
import { DatabaseSync } from "node:sqlite";
import { access, accessModel, entityHandle } from "@dads-framework/access";
import { relationshipWrite } from "@dads-framework/access/runtime";
import {
  createNodeSqliteExecutor,
  SqliteAuthorization,
  sqliteRelationshipSchemaMigration,
} from "@dads-framework/sqlite-access";
import { z } from "zod";

const user = entityHandle({ wireName: "user", idSchema: z.string() });
const team = entityHandle({
  wireName: "team",
  idSchema: z.string(),
  kind: "scope",
});
const teamAccess = access(team, {
  roles: { member: user },
  permissions: (roles) => ({ view: [roles.member] }),
});
const model = accessModel([
  access(user, { roles: {}, permissions: () => ({}) }),
  teamAccess,
]);

const database = new DatabaseSync(":memory:");
try {
  database.exec(sqliteRelationshipSchemaMigration.sql);
  const authorization = new SqliteAuthorization({
    model,
    namespace: "app",
    executor: createNodeSqliteExecutor(database),
  });
  const resource = { type: team.wireName, id: "team-1" };
  const subject = { type: user.wireName, id: "alice" };
  const relation = teamAccess.roles.member.roleName;
  const query = {
    resource,
    subject,
    permission: teamAccess.permissions.view.permissionName,
  };

  console.log(await authorization.check(query));
  await authorization.writeRelationships([
    relationshipWrite(resource, relation, subject),
  ]);
  console.log(await authorization.check(query));
  await authorization.deleteRelationships({
    resourceType: resource.type,
    resourceId: resource.id,
    relation,
    subject,
  });
  console.log(await authorization.check(query));
} finally {
  database.close();
}
```

The script prints `false`, `true`, and `false` as membership is granted and revoked.

<a id="protected-writes"></a>

## Protected writes

Use `withProtectedSqliteTransaction({ executor, authorization, now }, work)` when an authorization check guards a write. The helper runs `BEGIN IMMEDIATE`, pins one `nowMs` snapshot for the transaction, and passes `{ executor, authorization }` bound to that transaction.

Check permissions again inside the callback and perform both business table writes and relationship updates through the scoped handles. Both handles expire as soon as the callback returns or throws. On error, the helper rolls back both application rows and relationship changes.

<a id="use-it"></a>

## Use it

Apply `sqliteRelationshipSchemaMigration.sql` (or iterate `sqliteRelationshipSchemaMigration.statements`) during application startup or migrations. The schema creates `dad_authorization_relationship` along with its reverse subject lookup index and partial expiration index.

`SqliteAuthorization` compiles `@dads-framework/access` models into parameterized SQLite queries covering:

*   Direct relations, nested and cyclic `group#member` subject sets, and wildcards (`user:*`).
*   Expiring relationships (`expires_at` epoch milliseconds) evaluated consistently at a single timestamp across reads and updated via upsert.
*   Polymorphic arrows across multiple direct target entity types (`workspace | folder`).
*   Recursive monotonic arrows (such as nested folders) with cycle termination on cyclic graph data.
*   Transitive `lookupSubjects` returning concrete subject IDs and `hasWildcard`.
*   Intersection arrows (`throughAll` / `.all()`) requiring at least one active target and satisfaction across every active target.

<a id="what-it-refuses-to-do"></a>

## What it refuses to do

Compilation rejects missing relation or permission targets, empty expressions, local permission self-cycles, non-monotonic recursive cycles through `excluding` or `throughAll`, and non-linear recursive intersections.

Permission checks and resource lookups require a concrete subject (`id` cannot be empty or `*`, and `relation` must be omitted). Relationship writes reject unknown relations, mismatched subject types, empty identifiers, and invalid `expiresAt` dates. Scoped transaction handles reject all calls after the transaction callback ends.

<a id="contract"></a>

## Contract

`SqliteAuthorization` extends `Authorization` and implements `check`, `checkMany`, `checkEach`, `lookupResources`, `lookupSubjects`, `writeRelationships`, `deleteRelationships`, `relationshipExists`, `listDirectSubjects`, `applyChanges`, and `inTransaction`.

Unknown permission names return `false`, `[]`, or `{ subjects: [], hasWildcard: false }`. Successful writes and deletes return an empty consistency token because SQLite reads observe committed writes directly. Database connection lifecycle and migration execution remain application-owned.

Canonical page: <https://dadsframework.dev/docs/package-sqlite-access>
