# Scoped Database

## Scoped Database

All actions receive a **scoped `db`** instance that automatically enforces security:

| Operation | Org Scoping | Owner Scoping | Soft Delete Filter | Auto-inject on INSERT |
|-----------|-------------|---------------|--------------------|-----------------------|
| `SELECT`  | `WHERE organizationId = ?` | `WHERE ownerId = ?` | `WHERE deletedAt IS NULL` | n/a |
| `INSERT`  | n/a | n/a | n/a | `organizationId`, `ownerId` from ctx |
| `UPDATE`  | `WHERE organizationId = ?` | `WHERE ownerId = ?` | `WHERE deletedAt IS NULL` | n/a |
| `DELETE`  | `WHERE organizationId = ?` | `WHERE ownerId = ?` | `WHERE deletedAt IS NULL` | n/a |

Scoping is duck-typed at runtime — tables with an `organizationId` column get org scoping, tables with `ownerId` get owner scoping, tables with `deletedAt` get soft delete filtering.

```typescript
async execute({ db, ctx, input }) {
  // This query automatically includes WHERE organizationId = ? AND deletedAt IS NULL
  const items = await db.select().from(applications).where(eq(applications.stage, 'interview'));

  // Inserts automatically include organizationId
  await db.insert(applications).values({ candidateId: input.candidateId, jobId: input.jobId, stage: 'applied' });

  return items;
}
```

**Not enforced** in scoped DB (by design):
- **Guards** — actions ARE the authorized way to modify guarded fields
- **Masking** — actions are backend code that may need raw data
- **Access** — already checked before action execution

### Delegated principals and `databaseAccess`

On a Cloudflare + Neon Hyperdrive project, an action can admit a
configured delegated principal with `access.principals`:

```typescript
// features/attendance/actions/confirm.ts
import { z } from "zod";
import { defineAction } from "../.quickback/define-action";

export default defineAction({
  description: "Confirm attendance from an event session",
  path: "/attendance/confirm",
  input: z.object({ response: z.enum(["yes", "no"]) }),
  access: { principals: ["event_delegate"] },
  async execute({ ctx, db, input }) {
    // ctx.principal is guaranteed and narrowed by the generated helper.
    const { eventId, personId } = ctx.principal.claims;
    return confirmAttendance(db, { eventId, personId, response: input.response });
  },
});
```

This is a separate authority lane, not an account impersonation. A delegated
action context has no `userId`, active organization/team, or membership roles.
If access has a `PUBLIC` sibling arm, the generated type includes the anonymous
lane and code must narrow `ctx.principal` before reading it.

For an organization- or team-scoped host feature, the standalone route does
not run the Better Auth `activeOrgId` / `activeTeamId` precondition before a
principal access tree. Authentication and `evaluateAccessPreRecord` still run;
the declared `principals` arm is the authority decision. This avoids turning an
account-session field into an accidental prerequisite for delegated callers.

The generated standalone route reuses that action-specific `execute` context
after its runtime access gates. Account, `event_pass`, and `event_delegate`
actions therefore keep distinct compile-time authority types all the way into
the handler. The route also reuses the action's schema-aware scoped `db` type,
including when an unsafe/service-role client is wrapped before the call. If a
relationship uses `loads` / `exposeAs`, the loaded row type is
intersected with the same authority context: a single or shared hydration
target is required, while targets selected by distinct relationship lanes are
optional and must be narrowed by the handler.

For tables queried by authored actions, `resource.databaseAccess` adds an
independent Postgres RLS policy without adding HTTP surface. It never mounts a
read/CRUD route and never adds an OpenAPI operation. The action remains the API
layer where validation, state transitions, side effects, and business logic
belong; `databaseAccess` is only the database's row-level upper bound.

{/* doc-compile: skip — provider prerequisite: `resource.databaseAccess` compiles only on Cloudflare + Neon `connectionMode: "hyperdrive"` (RLS + transaction-local claims), and the gate's harness is D1. The file is otherwise whole and compiles on a Neon project. */}
```typescript
// features/attendance/attendanceEvidence.ts
import { q, defineTable } from '@quickback/compiler';

export const attendanceEvidence = q.table('attendanceEvidence', {
  id:             q.id(),
  eventId:        q.text().required(),
  personId:       q.text().required(),
  organizationId: q.scope('organization'),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(attendanceEvidence, {
  // Ordinary account CRUD remains organization-scoped.
  firewall: [
    { field: "organizationId", equals: "ctx.activeOrgId" },
  ],

  // Authored actions can use their caller-scoped db under one of two exact
  // row lanes. Sibling arms OR; fields in each arm AND.
  databaseAccess: {
    select: {
      or: [
        {
          roles: ["AUTHENTICATED"],
          record: {
            organizationId: { equals: "$ctx.activeOrgId" },
          },
        },
        {
          principals: ["event_delegate"],
          record: {
            eventId: { equals: "$ctx.principal.claims.eventId" },
            personId: { equals: "$ctx.principal.claims.personId" },
          },
        },
      ],
    },
  },
});
```

`databaseAccess` supports `select`, `insert`, `update`, and `delete` with a
purpose-built, SQL-pushable rule language: `principals`, `roles`, recursive
`or`/`and`, and record `equals` against a DB-visible account or principal
context value. Application-only values such as `userRole`, functions, and
request/body fields stay at the action access layer; the compiler refuses them
inside database RLS rather than pretending PostgreSQL can evaluate them.

Every authorization path must contain at least one nonempty record equality.
A principal-only or role-only leaf is rejected; every `or` arm must be bounded.
An `and` group is bounded when at least one child bounds every expansion (for
example, a principal child AND an event-id record child). A node cannot mix
leaf fields with `or`/`and`, because the boolean node is the complete rule.
This mandatory shape prevents an action declaration from accidentally granting
the delegated principal an entire table.

The supported equality sources are `ctx.userId`, `ctx.activeOrgId`,
`ctx.activeTeamId`, `ctx.principal.actor.id`, optional principal session/family
ids, and `ctx.principal.claims.<name>` (with or without a leading `$`). The
record keys are Drizzle/JavaScript table field names; Quickback resolves them
through the table metadata and quotes the exact physical PostgreSQL identifier,
so a field such as `eventId: text("event-id")` is safe. Unknown fields fail the
compile. `ADMIN` and the app-only `INTERNAL` marker are forbidden on this DB
surface, while `SYSADMIN` retains its explicit `cms.sysadmin` prerequisite. The
feature is rejected outside Cloudflare + Neon Hyperdrive because those targets
cannot guarantee request-scoped Postgres claims.

### Scoped relational-query safety

Drizzle's relational query API — `db.query.<table>.findFirst()` /
`findMany()` (including nested `with:` relations) — builds its SQL from the
relations config and your `where` alone, so the scoped wrapper cannot inject
the org/owner/team firewall into it. The scoped `db` therefore exposes the
Drizzle select builder—the path where Quickback can guarantee firewall
injection—and refuses `db.query.<table>` rather than returning unscoped rows:

```typescript
async execute({ db, input }) {
  // ❌ Throws: relational queries aren't org-scoped
  const item = await db.query.media.findFirst({ where: eq(media.id, input.id) });

  // ✅ Scoped builder — firewall conditions injected automatically
  const [item] = await db.select().from(media).where(eq(media.id, input.id));
}
```

For nested reads, compose joins with the scoped select builder. If a
Postgres-specific relational query is genuinely required, make the boundary
explicit with `unsafe: true` and query `unsafeDb.query` with the full scope
predicate in the `WHERE` (for example,
`eq(media.organizationId, ctx.activeOrgId)`).

### Unsafe Mode

Actions that need to bypass scoped DB filters (for example, platform-level support operations) can enable unsafe mode.

Use object form for explicit policy and mandatory audit metadata:

```typescript
// features/admin/actions/crossTenantReport.ts
import { z } from "zod";
import { defineAction } from "../.quickback/define-action";
import { applications } from "../../applications/applications";

export default defineAction({
  description: "Generate cross-org hiring report",
  path: "/admin/cross-tenant-report",
  input: z.object({ startDate: z.string() }),
  access: { roles: ["owner"] },
  unsafe: {
    reason: "Support investigation for enterprise customer",
    adminOnly: true,      // default true
    crossTenant: true,    // default true
    targetScope: "all",   // "all" | "organization"
  },
  async execute({ unsafeDb, input }) {
    // unsafeDb bypasses scoped filters
    const allOrgs = await unsafeDb.select().from(applications);
    return allOrgs;
  },
});
```

Unsafe cross-tenant actions are generated with:

- Better Auth authentication required (no unauthenticated path)
- sysadmin gate (`ctx.userRole === "sysadmin"`)
- mandatory audit logging on deny/success/error

**Provider note (Neon):** `unsafe.enabled` controls app-layer scoped-query
bypass, while `unsafe.crossTenant` controls database-layer RLS bypass. An
unsafe action with `crossTenant: false` receives a raw Drizzle handle backed by
the request database, so the caller's organization or delegated-principal
claims remain active—including inside `db.transaction()`. Its code must still
provide every intended scope predicate explicitly.

Only an action with `crossTenant: true` acquires the dedicated service-role
handle (`createServiceDb`). That handle sets the `quickback.service_role`
transaction marker instead of caller claims, and generated RLS grants it an
explicit per-table bypass policy. The sysadmin guard and mandatory audit log
therefore protect the same boundary that receives the RLS bypass. The
audit-logging contract is otherwise identical to D1. On Cloudflare, both HTTP
and Hyperdrive are supported. Hyperdrive is the recommended lane when an unsafe
action also needs an interactive transaction. The deprecated Node/Bun WebSocket
mode is refused for unsafe actions because it cannot provide the same
request-scoped service-role boundary.

`PUBLIC` actions also receive mandatory audit logging (same audit table), since unauthenticated endpoints are high-risk by nature.

Without unsafe mode, `unsafeDb` is `undefined` in the executor params.

### Raw SQL Policy

By default, the compiler rejects raw SQL in action code. Use Drizzle query-builder syntax whenever possible.

If a specific action must use raw SQL, opt in explicitly:

```typescript
// features/ledgers/actions/reconcile.ts
import { z } from "zod";
import { sql } from "drizzle-orm";
import { defineAction } from "../.quickback/define-action";

export default defineAction({
  description: "Run custom reconciliation query",
  input: z.object({}),
  access: { roles: ["owner"] },
  allowRawSql: true,
  async execute({ db }) {
    return db.execute(sql`select 1`);
  },
});
```

Without `allowRawSql: true`, compilation fails with a loud error pointing to the action and snippet.

The detector checks for SQL keywords in string arguments, so non-SQL method calls like `headers.get("X-Forwarded-For")` or `map.get("key")` will not trigger false positives.

## Sharing Code Between Actions

For Zod schemas, helpers, or constants used across multiple actions in a
feature, put them under `<feature>/lib/` and import from each action file.
Files under `lib/` are copied verbatim into the generated output.

```
features/applications/
├── applications.ts
├── actions/
│   ├── advance.ts
│   └── reject.ts
└── lib/
    ├── inputs.ts          ← shared Zod schemas
    └── transitions.ts     ← state-machine helpers
```

```typescript
// features/applications/lib/inputs.ts
import { z } from "zod";

export const advanceInput = z.object({
  nextStatus: z.enum(["screening", "interview", "offer"]),
  notes: z.string().max(2000).optional(),
});
```

```typescript
// features/applications/actions/advance.ts
import { defineAction } from "../.quickback/define-action";
import { advanceInput } from "../lib/inputs";

export default defineAction({
  description: "Move forward",
  input: advanceInput,
  access: { roles: ["owner"] },
  async execute({ db, record, input }) { /* … */ },
});
```

### Importing Tables

Action files import tables from their own feature or other features. The compiler generates alias files for each table, so you import by the table's file name:

```typescript
// features/applications/actions/advance.ts
import { eq, and } from "drizzle-orm";

// Same feature — import from parent directory using the table's file name
import { applications } from "../applications";
import { interviewScores } from "../interview-scores";

// Cross-feature — go up to the features directory, then into the other feature
import { candidates } from "../../candidates/candidates";
import { jobs } from "../../jobs/jobs";
```

**Path pattern from `features/{name}/actions/`:**
- Same feature table: `../{table-file-name}` (e.g., `../applications`)
- Other feature table: `../../{other-feature}/{table-file-name}` (e.g., `../../candidates/candidates`)
- Generated lib files: `../../../lib/{module}` (e.g., `../../../lib/realtime`, `../../../lib/webhooks`)

### Executor Parameters

```typescript
import type { Auth } from "better-auth";

interface ActionExecutorParams {
  db: DrizzleDB;           // Scoped database (auto-applies org/owner/soft-delete filters)
  unsafeDb?: DrizzleDB;    // Unscoped database (only available when unsafe mode is enabled)
  ctx: AppContext;         // User context (userId, roles, activeOrgId, activeTeamId, userRole)
  record?: TRecord;        // The record (record-based only, undefined for standalone)
  input: TInput;           // Validated input from Zod schema
  services: TServices;     // Configured integrations (billing, notifications, etc.)
  c: HonoContext;          // Raw Hono context for advanced use
  env: Env;                // Runtime bindings (Cloudflare env / process env). Alias for c.env.
  auth: Auth;              // Better Auth instance — call auth.api.getSession(...) etc. Lazy on Cloudflare.
  whereRecord?: (table) => SQL; // Pre-built WHERE: firewall + soft-delete + eq(table.id, record.id). Record-based actions only.
  whereTransition?: (table) => SQL; // whereRecord plus the transition's "from" precondition. Transition actions only.
}
```

#### `auth` — Better Auth instance

`auth` is the Better Auth instance, ready to call from inside an action
handler. Use it for things like minting an OAuth access token, fetching the
current session in a custom way, or invoking a Better Auth plugin method
that isn't already exposed by the generated middleware.

```typescript
// features/integrations/actions/connectGoogleDrive.ts
import { defineAction } from "../.quickback/define-action";
import { z } from "zod";

export default defineAction({
  description: "Get a fresh Google Drive access token for the signed-in user",
  input: z.object({}),
  access: { roles: ["AUTHENTICATED"] },
  async execute({ auth, ctx, c }) {
    // genericOAuth plugin method — needs a cast since the default-generic
    // `Auth` type doesn't carry plugin-specific API surface.
    const token = await (auth.api as any).getAccessToken({
      headers: c.req.raw.headers,
      body: { providerId: "google", userId: ctx.userId },
    });
    return { accessToken: token.accessToken };
  },
});
```

**Lazy on Cloudflare**: the generated handler emits a `Proxy` so
`createAuth(c.env, c.req.raw.cf)` only runs the first time you read a
property on `auth`. Actions that never touch `auth` pay zero construction
cost. On Bun/Node `auth` is the imported singleton (an IIFE that lazy-loads
the database adapter) wrapped in the same proxy shape — call sites are
identical across runtimes.

**Typing**: `auth` is typed as `Auth` from `better-auth`. The base API
(`getSession`, `signOut`, `signInEmail`, etc.) is fully typed. Plugin-only
methods (`getAccessToken` from `genericOAuth`, admin-plugin methods, etc.)
need a cast at the call site — the default-generic `Auth` doesn't infer
the project's specific plugin set.

#### `whereRecord` — explicit, scope-safe WHERE

For protected-field writes inside record-based actions, prefer
`whereRecord(table)` over `eq(table.id, record.id)`. It returns a Drizzle
expression that AND-merges the resource's firewall and soft-delete predicates
with the record id, so the WHERE is explicit even if a future refactor swaps
`db` back to an unscoped client:

```typescript
// features/applications/actions/advance.ts
import { z } from "zod";
import { defineAction, applications } from "../.quickback/define-action";

export default defineAction({
  description: "Advance an application to the next status.",
  input: z.object({
    nextStatus: z.string(),
    notes: z.string().optional(),
  }),
  access: { roles: ["owner"] },
  async execute({ db, record, input, whereRecord }) {
    const [updated] = await db
      .update(applications)
      .set({
        status: input.nextStatus,
        notes: input.notes ?? record.notes,
      })
      .where(whereRecord!(applications))   // firewall + soft-delete + id, all in one
      .returning();
    return updated;
  },
});
```

The audit DB wrapper hard-stamps `modifiedAt`/`modifiedBy` on every
`.set()` call, so handlers don't write them. The same wrapper hard-stamps
`createdAt`/`createdBy`/`modifiedAt`/`modifiedBy` on every `.values()` —
and on the conflict branch of an `onConflictDoUpdate({...})` upsert.
Caller-supplied audit fields are dropped on the floor — the audit log
always reflects the real actor and the real moment.

Who the stamped actor is depends on the verified principal, never on
anything the handler passes:

| Caller | `createdBy` / `modifiedBy` value |
|---|---|
| Signed-in user | the Better Auth `userId` |
| Delegated principal (API key, service) | `actor:<type>:<id>` |
| Scope-token principal (no account) | `scope:<kind>:<subjectId>` |
| Cron schedule via `withInternalContext` | `system:cron-<name>` |
| Queue handler via `withInternalContext` | `system:queue-<name>` |
| No verified caller | the write **throws** (fail closed) — system-side code that must write outside a request uses the raw client and owns its own provenance |

Don't hand-build audit timestamps as a "just in case" fallback: on the
wrapped `db` they're dead code, and the idiom is a portability trap —
Postgres audit columns take `Date` objects while SQLite takes ISO strings
(the wrapper picks the right one per dialect).

`whereRecord` is undefined for standalone actions (they have no record), so
the parameter is typed as optional. `whereTransition` extends `whereRecord`
with the transition's "from" precondition for transition actions.

#### Reaching Better Auth tables (`authDb`)

The scoped `db` is the **features** database. Better Auth tables never appear
on it — that is by design. `AUTH_DB` is a separate Drizzle client.

**Caller identity — use `ctx`, do not query `AUTH_DB`.** `ctx.userId`,
`ctx.activeOrgId`, `ctx.roles`, and `ctx.userRole` are already on the request.

To check that a *submitted* user id is an org member (assignee-style
features), use `c.get("authDb")` plus the generated `getOrgMemberRole` helper:

```typescript
// features/projects/actions/assign.ts — validate a userId is an org member
import { z } from "zod";
import { eq } from "drizzle-orm";
import { ActionError } from "@quickback/compiler";
import { defineAction, projects } from "../.quickback/define-action";
import { getOrgMemberRole } from "../../../lib/org-access";

export default defineAction({
  description: "Assign a project to an org member",
  input: z.object({ assigneeId: z.string() }),
  access: { roles: ["member", "admin"] },
  async execute({ db, record, input, ctx, c, whereRecord }) {
    // getOrgMemberRole(authDb, userId, organizationId) → role string | null
    const role = await getOrgMemberRole(c.get("authDb"), input.assigneeId, ctx.activeOrgId!);
    if (!role) {
      throw new ActionError("Assignee is not a member of this organization", "NOT_A_MEMBER", 400);
    }
    const [updated] = await db
      .update(projects)
      .set({ assigneeId: input.assigneeId })
      .where(whereRecord!(projects))
      .returning();
    return updated;
  },
});
```

`c.get("authDb")` is the Drizzle connection to the auth database (in dual-DB
mode it's a separate D1 from your features). That is the native membership
check.

For a richer query, use the same `authDb` and import tables from
`../../../auth/schema` — from an **`actions/` file**. On D1 the tables are
`user`, `organization`, and `member` — not `organization_membership`. (Neon
exports the Better Auth CLI's plural names: `users`, `organizations`,
`members`.)

| Surface | AUTH_DB handle | Notes |
|---|---|---|
| Action `execute` | `c.get("authDb")` | Import schema from `../../../auth/schema` |
| [Account hooks](/ui/account/hooks) | `{ authDb, authSchema }` injected | Use the injected namespace — do not invent a path to `auth/schema` |
| Trigger `handler:` | **none** | Args are `{ ctx, db, env }` — `db` is features only. There is no `c`. Prefer `ctx` for the acting user. Do not import `../../auth/schema` from the table file to reach AUTH_DB; that path used to compile and then fail package-mode wrangler. Check another user's membership in an action. |