# DB Tenancy Hardening Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Add a lightweight defense-in-depth filet so a missed app-code tenancy check cannot silently ship — a static guard over entry-point queries plus a fail-closed access primitive with a clean test seam.

**Architecture:** A pure detection function (`findTenancyViolations`) scans entry-point source (Server Actions + route handlers) and flags any per-site-table query lacking a tenancy gate; a bun test runs it over the real tree inside the green gate. Separately, `currentUserCanAccessSite` flips to fail-closed, with a `NODE_ENV==='test'` caller-injection seam so DB suites inject an authenticated caller instead of relying on the old fail-open default (which would otherwise red the suites and tempt the leak-prone `mock.module` workaround).

**Tech Stack:** TypeScript, Bun (test runner), Drizzle ORM, Biome, Next.js 16 App Router.

## Global Constraints

- Runtime/test: **bun**. Green gate: `bun run test:all` (per-directory processes). Must end 0 fail.
- Under `bun test`, `process.env.NODE_ENV === "test"`.
- Lint/format: **biome** — touched files must pass `bunx biome check <files>`.
- Type safety: `bunx tsc --noEmit` clean.
- Commits: **conventional commits**; end each message with `Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>`.
- **No migration, no runtime/perf change** in this branch.
- Per-site tables (trigger set), Drizzle export identifiers: `sites`, `keywords`, `keywordThemes`, `keywordTargets`, `keywordExclusions`, `keywordShortlist`, `backlinks`, `backlinkOutreach`, `domainBacklinks`, `contentItems`, `contentKeywords`, `siteAudits`, `analyticsJobs`, `gscQuerySnapshots`, `gscPageSnapshots`.
- Gate helpers: `assertSiteInActiveOrg`, `assertKeywordInActiveOrg`, `assertContentItemInActiveOrg`, `getActiveOrgId`.

---

## File Structure

- **Create** `src/lib/security/tenancy-guard.ts` — pure detection logic + trigger set + gate set + allowlist (one responsibility: decide if an entry-point file is ungated).
- **Create** `src/lib/security/tenancy-guard.test.ts` — unit tests for the logic + an integration test scanning the real `src` tree.
- **Modify** `src/lib/sites/access.ts` — flip `currentUserCanAccessSite` to fail-closed; add the test-caller seam (`__setTestCaller`/`__clearTestCaller`), export the `Caller` type.
- **Create** `src/lib/sites/access.test.ts` — behaviour tests for the seam + fail-closed.
- **Modify** `src/lib/sites/actions.test.ts`, `src/lib/billing/enforcement.test.ts` — replace the partial `mock.module("./access"|"@/lib/sites/access", …)` with the `__setTestCaller` seam.
- **Modify** `src/lib/sites/assert-site.test.ts`, `src/lib/sites/assert-entities.test.ts`, `src/lib/spec/audit/actions.test.ts` — inject an owner caller via the seam so the real assert path passes under fail-closed.

---

## Task 1: Static tenancy guard

**Files:**
- Create: `src/lib/security/tenancy-guard.ts`
- Test: `src/lib/security/tenancy-guard.test.ts`

**Interfaces:**
- Produces: `PER_SITE_TABLES: readonly string[]`, `GATE_HELPERS: readonly string[]`, `ALLOWLIST: Record<string,string>`, `type SourceFile = { path: string; source: string }`, `type Violation = { path: string; tables: string[] }`, `isEntryPoint(f: SourceFile): boolean`, `findTenancyViolations(files: SourceFile[]): Violation[]`.
- Consumes: nothing (pure + `node:fs` in the test).

- [ ] **Step 1: Write the failing unit tests**

Create `src/lib/security/tenancy-guard.test.ts`:

```ts
import { describe, expect, test } from "bun:test";
import { readdirSync, readFileSync } from "node:fs";
import { join } from "node:path";
import { findTenancyViolations } from "./tenancy-guard";

describe("findTenancyViolations (unit)", () => {
  test("flags an entry point that queries a per-site table without a gate", () => {
    const v = findTenancyViolations([
      { path: "src/app/x/route.ts", source: `import { db } from "@/lib/db/client";\nawait db.select().from(keywords);` },
    ]);
    expect(v).toHaveLength(1);
    expect(v[0].tables).toContain("keywords");
  });

  test("passes when an assert helper is present", () => {
    const v = findTenancyViolations([
      { path: "src/app/x/route.ts", source: `await assertSiteInActiveOrg(siteId);\nawait db.select().from(keywords);` },
    ]);
    expect(v).toHaveLength(0);
  });

  test("passes an org-scoped write via getActiveOrgId", () => {
    const v = findTenancyViolations([
      { path: "a.ts", source: `"use server";\nconst o = await getActiveOrgId();\nawait db.delete(sites).where(eq(sites.orgId, o));` },
    ]);
    expect(v).toHaveLength(0);
  });

  test("ignores non-entry-point files (internal query modules)", () => {
    const v = findTenancyViolations([
      { path: "src/lib/keywords/queries.ts", source: `await db.select().from(keywords);` },
    ]);
    expect(v).toHaveLength(0);
  });
});

function collectTsFiles(dir: string, out: string[] = []): string[] {
  for (const e of readdirSync(dir, { withFileTypes: true })) {
    const full = join(dir, e.name);
    if (e.isDirectory()) collectTsFiles(full, out);
    else if (/\.(ts|tsx)$/.test(e.name) && !/\.test\.(ts|tsx)$/.test(e.name)) out.push(full);
  }
  return out;
}

describe("repo tenancy guard (integration)", () => {
  test("no entry point queries a per-site table without a gate", () => {
    const files = collectTsFiles("src").map((path) => ({ path, source: readFileSync(path, "utf8") }));
    const violations = findTenancyViolations(files);
    const detail = violations.map((v) => `  ${v.path} -> ${v.tables.join(", ")}`).join("\n");
    expect(
      violations,
      `Ungated per-site queries found. Add the proper assert helper, or (if it has a valid alternate gate) allowlist it in src/lib/security/tenancy-guard.ts:\n${detail}`,
    ).toHaveLength(0);
  });
});
```

- [ ] **Step 2: Run unit tests — verify they fail**

Run: `DB_TESTS=0 bun --conditions=react-server test src/lib/security/tenancy-guard.test.ts`
Expected: FAIL — `Cannot find module './tenancy-guard'`.

- [ ] **Step 3: Implement the detection module**

Create `src/lib/security/tenancy-guard.ts`:

```ts
// Static tenancy guard. Heuristic backstop: flags entry-point files (Server
// Actions + route handlers) that query a per-site table without a tenancy gate.
// Catches "the gate is entirely missing" (the B2/B3 class), NOT subtly-wrong
// scoping. See docs/superpowers/specs/2026-06-22-db-tenancy-hardening-design.md.

export const PER_SITE_TABLES = [
  "sites", "keywords", "keywordThemes", "keywordTargets", "keywordExclusions",
  "keywordShortlist", "backlinks", "backlinkOutreach", "domainBacklinks",
  "contentItems", "contentKeywords", "siteAudits", "analyticsJobs",
  "gscQuerySnapshots", "gscPageSnapshots",
] as const;

export const GATE_HELPERS = [
  "assertSiteInActiveOrg", "assertKeywordInActiveOrg",
  "assertContentItemInActiveOrg", "getActiveOrgId",
] as const;

// Relative path -> reason it legitimately queries a per-site table without an
// assert helper. Adding an entry is a deliberate, reviewed decision (seeded by
// triage in Step 5). Use forward slashes; match the path as produced by the scan.
export const ALLOWLIST: Record<string, string> = {};

export type SourceFile = { path: string; source: string };
export type Violation = { path: string; tables: string[] };

const word = (id: string): RegExp => new RegExp(`\\b${id}\\b`);

export function isEntryPoint(file: SourceFile): boolean {
  if (/[\\/]app[\\/].*[\\/]route\.tsx?$/.test(file.path)) return true;
  return /["']use server["']/.test(file.source);
}

export function findTenancyViolations(files: SourceFile[]): Violation[] {
  const out: Violation[] = [];
  for (const f of files) {
    if (!isEntryPoint(f)) continue;
    if (f.path in ALLOWLIST) continue;
    if (!/\bdb\./.test(f.source)) continue;
    const tables = PER_SITE_TABLES.filter((t) => word(t).test(f.source));
    if (tables.length === 0) continue;
    const gated = GATE_HELPERS.some((g) => word(g).test(f.source));
    if (!gated) out.push({ path: f.path, tables: [...tables] });
  }
  return out;
}
```

- [ ] **Step 4: Run unit tests — verify they pass**

Run: `DB_TESTS=0 bun --conditions=react-server test src/lib/security/tenancy-guard.test.ts -t "(unit)"`
Expected: 4 unit tests PASS. The integration test may still FAIL (real ungated files) — addressed next.

- [ ] **Step 5: Run the integration test and triage hits**

Run: `DB_TESTS=0 bun --conditions=react-server test src/lib/security/tenancy-guard.test.ts -t "integration"`

For EACH path the failure lists, decide:
- **Valid alternate gate** (e.g. the GSC worker route gated by a Bearer `WORKER_API_KEY` constant-time check; a super-admin/god-mode file gated by `assertSuperAdmin`) → add to `ALLOWLIST` with a one-line reason, e.g.:
  ```ts
  export const ALLOWLIST: Record<string, string> = {
    "src/app/api/internal/gsc-token/route.ts": "worker Bearer WORKER_API_KEY; site is the tenant boundary",
  };
  ```
  (Use the EXACT path string the failure prints.)
- **A real gap** (an entry point that should call an assert but doesn't) → FIX it by adding the correct `assertSiteInActiveOrg` / `assertKeywordInActiveOrg` / `assertContentItemInActiveOrg`, mirroring how a sibling action gates the same table. Do NOT allowlist a real gap.
- **A heuristic false positive** (the table identifier appears only in a comment/string, no real query) → allowlist with reason `"false positive: <table> referenced in <comment|string>, no query"`.

Re-run until the integration test PASSES. Keep the allowlist minimal and every entry reasoned.

- [ ] **Step 6: Full gate + biome, then commit**

Run: `bun run test:all` → expect 0 fail. Then `bunx biome check src/lib/security/tenancy-guard.ts src/lib/security/tenancy-guard.test.ts` (apply `--write` if it reformats) and `bunx tsc --noEmit`.

```bash
git add src/lib/security/tenancy-guard.ts src/lib/security/tenancy-guard.test.ts
git commit -m "$(cat <<'MSG'
test(security): static tenancy guard for entry-point queries

A bun test that scans Server Actions + route handlers and fails if a
per-site table is queried without a tenancy gate (assert* helper or
getActiveOrgId), with a reviewed allowlist for valid alternate gates.
Heuristic backstop for the "forgot the gate" class (would have caught
B2/B3); not a replacement for the asserts.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
MSG
)"
```

---

## Task 2: `currentUserCanAccessSite` fail-closed + test-caller seam

**Files:**
- Modify: `src/lib/sites/access.ts`
- Create: `src/lib/sites/access.test.ts`
- Modify: `src/lib/sites/actions.test.ts`, `src/lib/billing/enforcement.test.ts`, `src/lib/sites/assert-site.test.ts`, `src/lib/sites/assert-entities.test.ts`, `src/lib/spec/audit/actions.test.ts`

**Interfaces:**
- Produces (from `access.ts`): `export type Caller = { userId: string; orgId: string; role: string | null }`, `export function __setTestCaller(c: Caller | null): void`, `export function __clearTestCaller(): void`. Behaviour: `currentUserCanAccessSite(siteId)` returns `false` when no caller resolves.
- Consumes: existing `currentCaller`, `isFullAccess`, `hasFullSiteAccess`.

- [ ] **Step 1: Write the failing behaviour test**

Create `src/lib/sites/access.test.ts`:

```ts
import { afterEach, describe, expect, test } from "bun:test";
import { __clearTestCaller, __setTestCaller, currentUserCanAccessSite } from "./access";

afterEach(() => __clearTestCaller());

describe("currentUserCanAccessSite", () => {
  test("fails closed when no caller resolves", async () => {
    expect(await currentUserCanAccessSite("any-site")).toBe(false);
  });

  test("owner caller (via test seam) is granted", async () => {
    __setTestCaller({ userId: "u1", orgId: "o1", role: "owner" });
    expect(await currentUserCanAccessSite("any-site")).toBe(true);
  });
});
```

- [ ] **Step 2: Run it — verify it fails**

Run: `bun --conditions=react-server test src/lib/sites/access.test.ts`
Expected: FAIL — `__setTestCaller` not exported AND the fail-closed test fails (currently returns `true`).

- [ ] **Step 3: Add the seam + flip fail-closed in `access.ts`**

Export the `Caller` type — change `type Caller = {` to `export type Caller = {`.

Add the seam just below the `Caller` type, ABOVE `currentCaller`:

```ts
// Test-only caller injection. Lets DB suites that exercise the real assert path
// supply an authenticated caller WITHOUT process-global mock.module (which leaks
// across sibling test files). No effect outside NODE_ENV==='test'.
let testCallerOverride: { caller: Caller | null } | null = null;
export function __setTestCaller(c: Caller | null): void {
  if (process.env.NODE_ENV !== "test") {
    throw new Error("__setTestCaller is test-only");
  }
  testCallerOverride = { caller: c };
}
export function __clearTestCaller(): void {
  testCallerOverride = null;
}
```

Make `currentCaller` consult the override first — add as the FIRST line inside its `cache(async () => { … })` body:

```ts
const currentCaller = cache(async (): Promise<Caller | null> => {
  if (process.env.NODE_ENV === "test" && testCallerOverride) {
    return testCallerOverride.caller;
  }
  // …existing headers/session resolution unchanged…
```

Flip the fail-open default in `currentUserCanAccessSite`:

```ts
  const c = await currentCaller();
  // Fail closed: no resolvable caller = no trusted session/role, so deny. In a
  // real request the caller always resolves (owner/admin & granted staff pass
  // below). Tests inject a caller via __setTestCaller.
  if (!c) return false;
```

- [ ] **Step 4: Run the behaviour test — verify it passes**

Run: `bun --conditions=react-server test src/lib/sites/access.test.ts`
Expected: 2 tests PASS.

- [ ] **Step 5: Migrate the real-path DB suites to the seam**

In `src/lib/sites/actions.test.ts`: REMOVE the `mock.module("./access", () => ({ … }))` block entirely. Add the import and per-test caller:

```ts
import { __clearTestCaller, __setTestCaller } from "./access";
```
Inside the top-level `describe`, add (or extend `beforeEach`/`afterEach`):
```ts
beforeEach(() => __setTestCaller({ userId: "seed-owner", orgId: "demo-org", role: "owner" }));
afterEach(() => __clearTestCaller());
```

In `src/lib/billing/enforcement.test.ts`: REMOVE the `mock.module("@/lib/sites/access", …)` block. Add:
```ts
import { __clearTestCaller, __setTestCaller } from "@/lib/sites/access";
```
and inside the describe:
```ts
beforeEach(() => __setTestCaller({ userId: "test-enf-user", orgId: "test-enf-org", role: "owner" }));
afterEach(() => __clearTestCaller());
```
(Keep the existing `beforeEach` DB seeding — add these calls alongside it; ensure `beforeEach`/`afterEach` are imported from `bun:test`.)

In `src/lib/sites/assert-site.test.ts`, `src/lib/sites/assert-entities.test.ts`, and `src/lib/spec/audit/actions.test.ts`: add the seam so the real assert path passes under fail-closed. Add to each:
```ts
import { __clearTestCaller, __setTestCaller } from "@/lib/sites/access"; // or "./access" in the sites dir
```
and within the describe:
```ts
beforeEach(() => __setTestCaller({ userId: "test-owner", orgId: "<the org the test seeds>", role: "owner" }));
afterEach(() => __clearTestCaller());
```
Use the SAME org id each test already seeds (e.g. `"demo-org"` in spec/audit). `role: "owner"` short-circuits `currentUserCanAccessSite` to `true`, so the suites no longer depend on the old fail-open default. Ensure `beforeEach`/`afterEach` are imported from `bun:test` in each file.

- [ ] **Step 6: Full gate**

Run: `bun run test:all`
Expected: **0 fail**. If a suite I did not list newly reds with "Site is not shared with you" / a forbidden error, it exercises the real assert path too — apply the same `__setTestCaller` owner seam in its `beforeEach` and re-run. Do NOT reintroduce a partial `mock.module("./access", …)`.

- [ ] **Step 7: biome + tsc + commit**

Run: `bunx biome check` on every touched file (apply `--write` if it reformats) and `bunx tsc --noEmit`.

```bash
git add src/lib/sites/access.ts src/lib/sites/access.test.ts src/lib/sites/actions.test.ts \
  src/lib/billing/enforcement.test.ts src/lib/sites/assert-site.test.ts \
  src/lib/sites/assert-entities.test.ts src/lib/spec/audit/actions.test.ts
git commit -m "$(cat <<'MSG'
fix(security): currentUserCanAccessSite fail-closed + test-caller seam

Flip the access primitive to deny when no caller resolves (was fail-open).
The DB suites that exercise the real assert path now inject an authenticated
owner via a NODE_ENV==='test' seam (__setTestCaller) instead of relying on
the permissive default or a leak-prone partial mock.module("./access").

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
MSG
)"
```

---

## Self-Review

- **Spec coverage:** Component 1 (static guard) → Task 1. Component 2 (B5 fail-closed + DI seam, remove partial `./access` mocks) → Task 2. Deferred items (RLS, GUC, reduced role) → intentionally absent. ✅
- **Placeholder scan:** allowlist entries and the per-suite org ids are seeded by triage/by reading each test's seed — the rule and the exact code shape are given, not a vague "handle it"; the only runtime-discovered values are real file paths/org ids, which cannot be known until the scan/seed runs. ✅
- **Type consistency:** `Caller` shape `{ userId; orgId; role }` is identical in `access.ts`, the seam signature, and every `__setTestCaller(...)` call. `findTenancyViolations`/`isEntryPoint`/`SourceFile`/`Violation` names match between module and test. ✅