# Data portal class-derived allowlist — 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:** Let an enrolled portal user browse and download their sub-account's deliverable folders, where "deliverable" is derived per account from that account's own `SCHEMA.md` rather than a hardcoded list.

**Architecture:** The device walks only the exposed folders and full-replaces a D1 `directory` table with metadata (no bytes), so listing survives the device being offline. Download mints a five-minute HMAC-signed link to a new public install route, which re-derives the exposed set itself rather than trusting the path it was handed.

**Tech Stack:** TypeScript, vitest, Cloudflare Pages Functions + D1, Node 22 ESM (`.mjs` with JSDoc types), Hono.

**Spec:** [`../specs/2026-07-20-task-1831-data-portal-class-derived-allowlist-design.md`](../specs/2026-07-20-task-1831-data-portal-class-derived-allowlist-design.md)
**Task:** [`../../../../.tasks/1831-data-portal-exposes-only-uploads-not-the-sub-accounts-folder-tree.md`](../../../../.tasks/1831-data-portal-exposes-only-uploads-not-the-sub-accounts-folder-tree.md)

## Global Constraints

- **Node 22 required.** Every command below assumes `export PATH="$HOME/.nvm/versions/node/v22.22.0/bin:$PATH"`.
- **Test runner is vitest**, invoked from the plugin dir: `cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run <file>`.
- **Baseline is 139 tests across 10 files, all green.** Never let that regress.
- **`npm run typecheck:templates` must stay exit 0.** It typechecks `template/functions/api/*.ts` and `_lib/*.mjs` under `--strict --checkJs`, so every `.mjs` needs JSDoc types.
- **`.mjs` modules in `_lib/` are the shared-code precedent** (`passcode.mjs` is imported by both `bin/portal-enrol.mjs` and the template). Follow it.
- **Never spawn `wrangler` per statement.** That is the Task 1805 defect. All D1 access from the device goes over HTTP with bound parameters.
- **No production import from `platform/ui` into `platform/plugins`.** Only a test does that today (`server/lib/calendar-ics.test.ts:12`) and this sprint does not change it.
- **Signature is not the security boundary.** The install re-derives the exposed dir set on every request. A valid signature over a non-exposed path is still refused.
- **Fail closed on a missing `SCHEMA.md`** — expose nothing. This deliberately inverts `fs-schema-guard.sh:76-78`, which fails open; every place that does it carries that reason in a comment.
- **Log tags:** `[portal-index]` device side, `[data-portal]` portal side, `[portal-fetch]` install side.

---

## File Structure

| File | Status | Responsibility |
|---|---|---|
| `platform/plugins/cloudflare/bin/schema-exposed-dirs.mjs` | create | `SCHEMA.md` text → exposed dir names. Pure. |
| `platform/plugins/cloudflare/bin/d1-http.mjs` | create | D1 over HTTP with bound params |
| `platform/plugins/cloudflare/bin/portal-index-push.mjs` | create | walk exposed dirs, full-replace `directory` rows |
| `platform/plugins/cloudflare/bin/portal-enrol.mjs` | modify | write `accountId` on the `people` row |
| `.../data-portal/schema.sql` | modify | `directory` table, `people.accountId` |
| `.../template/functions/api/_lib/portal-sign.mjs` | create | mint + verify signed links (portal side) |
| `.../template/functions/api/_lib/types.ts` | modify | `PORTAL_FETCH_SECRET`, `PORTAL_INSTALL_ORIGIN` on `PortalEnv` |
| `.../template/functions/api/_lib/session.ts` | modify | `resolveSession` also returns `accountId` |
| `.../template/functions/api/files.ts` | modify | return `entries` beside `files` |
| `.../template/functions/api/download.ts` | modify | indexed-file branch: HEAD pre-flight, then signed link |
| `.../template/portal.js`, `index.html` | modify | breadcrumb folder navigation |
| `platform/ui/server/routes/portal-fetch.ts` | create | verify signature, re-resolve exposure, serve bytes |
| `platform/ui/server/index.ts` | modify | register the subapp |
| `.../scheduling/mcp/src/scripts/check-due-events.ts` | modify | spawn the push hourly |

---

### Task 1: Exposed-dir resolver

The one place that decides which folders a client may see. Pure text in, names out — no filesystem, so it is trivially testable.

**Files:**
- Create: `platform/plugins/cloudflare/bin/schema-exposed-dirs.mjs`
- Test: `platform/plugins/cloudflare/mcp/__tests__/schema-exposed-dirs.test.ts`

**Interfaces:**
- Produces: `resolveExposedDirs(schemaText: string | null): { schemaPresent: boolean, allowed: string[], domainBuckets: string[], pluginOwned: string[], collisions: string[], exposed: string[] }`

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/schema-exposed-dirs.test.ts
import { describe, it, expect } from 'vitest'
import { resolveExposedDirs } from '../../bin/schema-exposed-dirs.mjs'

const ALLOWED = ['projects', 'contacts', 'documents', 'output', 'uploads', 'quotes', 'sites']

function schema(opts: { allowed?: string[]; ontology?: string; pluginOwned?: string } = {}): string {
  const allowed = opts.allowed ?? ALLOWED
  return [
    '# Account schema',
    '',
    '```allowed-top-level',
    ...allowed,
    '```',
    '',
    opts.pluginOwned ?? '',
    '',
    opts.ontology ?? '',
    '',
  ].join('\n')
}

// The generator writes this region with an ASCII hyphen separator
// (account-schema-owned-dirs.py:294).
const ONTOLOGY = [
  '<!-- ontology-buckets:start -->',
  '## Domain entity buckets (from the graph ontology)',
  '',
  '- `quotes/` - one folder per Quotation record.',
  '<!-- ontology-buckets:end -->',
].join('\n')

// The plugin-owned region uses an EM DASH (account-schema-owned-dirs.py:281).
const PLUGIN_OWNED = [
  '<!-- plugin-owned-dirs:start -->',
  '## Plugin-owned top-level workspaces',
  '',
  '- `sites/` — Owned by the cloudflare plugin.',
  '<!-- plugin-owned-dirs:end -->',
].join('\n')

describe('resolveExposedDirs', () => {
  it('exposes ontology buckets plus output', () => {
    const r = resolveExposedDirs(schema({ ontology: ONTOLOGY }))
    expect(r.exposed).toEqual(['output', 'quotes'])
    expect(r.schemaPresent).toBe(true)
  })

  it('exposes output alone when there is no ontology region', () => {
    expect(resolveExposedDirs(schema()).exposed).toEqual(['output'])
  })

  it('drops a bucket that is absent from the allowed block', () => {
    const r = resolveExposedDirs(schema({ allowed: ['output', 'documents'], ontology: ONTOLOGY }))
    expect(r.exposed).toEqual(['output'])
  })

  // The contract that fails silently if got wrong: an em dash in the ontology
  // region must NOT parse, because the generator never writes one there.
  it('does not parse an em-dash separator as a domain bucket', () => {
    const emDash = ONTOLOGY.replace('- one folder per', '— one folder per')
    expect(resolveExposedDirs(schema({ ontology: emDash })).domainBuckets).toEqual([])
  })

  it('never exposes operator-data or upload buckets', () => {
    const r = resolveExposedDirs(schema({ ontology: ONTOLOGY }))
    for (const d of ['documents', 'contacts', 'projects', 'uploads']) {
      expect(r.exposed).not.toContain(d)
    }
  })

  it('reports a plugin-owned collision and keeps the dir out of exposed', () => {
    const collide = ONTOLOGY.replace('`quotes/`', '`sites/`').replace('Quotation', 'Site')
    const r = resolveExposedDirs(schema({ ontology: collide, pluginOwned: PLUGIN_OWNED }))
    expect(r.collisions).toEqual(['sites'])
    expect(r.exposed).toEqual(['output'])
  })

  it('fails closed on a missing schema', () => {
    const r = resolveExposedDirs(null)
    expect(r.schemaPresent).toBe(false)
    expect(r.exposed).toEqual([])
  })

  it('fails closed when the allowed block is absent', () => {
    const r = resolveExposedDirs('# no fence here')
    expect(r.exposed).toEqual([])
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
export PATH="$HOME/.nvm/versions/node/v22.22.0/bin:$PATH"
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/schema-exposed-dirs.test.ts
```

Expected: FAIL — `Failed to resolve import "../../bin/schema-exposed-dirs.mjs"`.

- [ ] **Step 3: Write the implementation**

```js
// platform/plugins/cloudflare/bin/schema-exposed-dirs.mjs
// Which folders of an account may a portal client see?
//
// Not a list in this file. The answer is derived per account from that
// account's own SCHEMA.md, which is generated by
// platform/scripts/lib/account-schema-owned-dirs.py: one bucket per top-level
// entity of the brand's vertical ontology, plus whatever plugins claim, inside
// a closed allowed-top-level set.
//
// Exposed = ontology buckets + `output`, intersected with the allowed set.
// Ontology buckets hold per-entity artifacts ("File a job's artifacts under its
// entity folder", the region's own prose); `output` is the delivery convention
// (doc-source-resolve.ts:14 calls it agent-authored). Operator-data buckets,
// uploads, plugin workspaces and platform-internal dirs are in neither source
// and so are never exposed.

const ONT_START = '<!-- ontology-buckets:start -->'
const ONT_END = '<!-- ontology-buckets:end -->'
const OWNED_START = '<!-- plugin-owned-dirs:start -->'
const OWNED_END = '<!-- plugin-owned-dirs:end -->'

/** The delivery convention, always exposed when the account allows it. */
export const ALWAYS_EXPOSED = 'output'

/**
 * The generator writes the ontology region with an ASCII hyphen and the
 * plugin-owned region with an em dash (account-schema-owned-dirs.py:294 vs
 * :281). Matching the wrong one yields zero buckets and reads as "this account
 * has no deliverables", which is why each region has its own pattern.
 */
const DOMAIN_LINE = /^- `([^`/]+)\/` - one folder per /
const OWNED_LINE = /^- `([^`/]+)\/` — /

/** @param {string} text @param {string} start @param {string} end */
function region(text, start, end) {
  const i = text.indexOf(start)
  if (i === -1) return null
  const j = text.indexOf(end, i)
  return text.slice(i, j === -1 ? text.length : j)
}

/** @param {string} text @returns {string[] | null} */
function allowedBlockOrNull(text) {
  const lines = text.split('\n')
  const start = lines.findIndex((l) => l.trim() === '```allowed-top-level')
  if (start === -1) return null
  for (let j = start + 1; j < lines.length; j++) {
    if (lines[j].trim() === '```') {
      return lines.slice(start + 1, j).map((l) => l.trim()).filter(Boolean)
    }
  }
  return null
}

/**
 * @param {string | null} schemaText Contents of accounts/<id>/SCHEMA.md, or null when absent.
 * @returns {{ schemaPresent: boolean, allowed: string[], domainBuckets: string[],
 *            pluginOwned: string[], collisions: string[], exposed: string[] }}
 */
export function resolveExposedDirs(schemaText) {
  const empty = {
    schemaPresent: false,
    allowed: /** @type {string[]} */ ([]),
    domainBuckets: /** @type {string[]} */ ([]),
    pluginOwned: /** @type {string[]} */ ([]),
    collisions: /** @type {string[]} */ ([]),
    exposed: /** @type {string[]} */ ([]),
  }
  // Fail CLOSED. fs-schema-guard.sh:76-78 fails open on a missing schema so an
  // unseeded legacy account is not write-blocked; a read surface facing a
  // client must do the opposite. Do not "fix" this to match the guard.
  if (typeof schemaText !== 'string' || schemaText.length === 0) return empty

  const allowed = allowedBlockOrNull(schemaText)
  if (allowed === null) return { ...empty, schemaPresent: true }

  const ont = region(schemaText, ONT_START, ONT_END)
  const owned = region(schemaText, OWNED_START, OWNED_END)

  /** @param {string | null} text @param {RegExp} re */
  const dirsIn = (text, re) => {
    if (!text) return []
    /** @type {string[]} */ const out = []
    for (const line of text.split('\n')) {
      const m = re.exec(line.trim())
      if (m && !out.includes(m[1])) out.push(m[1])
    }
    return out
  }

  const domainBuckets = dirsIn(ont, DOMAIN_LINE)
  const pluginOwned = dirsIn(owned, OWNED_LINE)

  // The generator omits a plugin-claimed dir from the ontology region
  // (account-schema-owned-dirs.py:287), so this should be empty in practice.
  // When it is not, the bucket silently vanishes from the client's view — the
  // caller logs it rather than guessing which side should win.
  const collisions = domainBuckets.filter((d) => pluginOwned.includes(d))

  const candidates = [ALWAYS_EXPOSED, ...domainBuckets.filter((d) => !collisions.includes(d))]
  const exposed = [...new Set(candidates)].filter((d) => allowed.includes(d)).sort()

  return { schemaPresent: true, allowed, domainBuckets, pluginOwned, collisions, exposed }
}
```

- [ ] **Step 4: Run test to verify it passes**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/schema-exposed-dirs.test.ts
```

Expected: PASS, 7 tests.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/bin/schema-exposed-dirs.mjs platform/plugins/cloudflare/mcp/__tests__/schema-exposed-dirs.test.ts
git commit -m "feat(data-portal): derive exposed dirs from the account's own SCHEMA.md"
```

---

### Task 2: Signed-link minting and verification (portal side)

**Files:**
- Create: `platform/plugins/cloudflare/skills/data-portal/template/functions/api/_lib/portal-sign.mjs`
- Test: `platform/plugins/cloudflare/mcp/__tests__/portal-sign.test.ts`

**Interfaces:**
- Consumes: nothing.
- Produces:
  - `SIGNED_TTL_MS: number` (300000)
  - `signPath(secret: string, accountId: string, relPath: string, expiresAtMs: number): Promise<string>` — hex HMAC-SHA256
  - `verifyPath(secret, accountId, relPath, expiresAtMs, signature, nowMs): Promise<boolean>`
  - `buildSignedUrl(origin: string, secret: string, accountId: string, relPath: string, expiresAtMs: number): Promise<string>`

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/portal-sign.test.ts
import { describe, it, expect } from 'vitest'
import {
  SIGNED_TTL_MS,
  signPath,
  verifyPath,
  buildSignedUrl,
} from '../../skills/data-portal/template/functions/api/_lib/portal-sign.mjs'

const SECRET = 'test-secret-value'
const ACC = '098a18a3-1741-447f-9778-65470645d57d'
const NOW = 1_800_000_000_000

describe('portal-sign', () => {
  it('round-trips a signature', async () => {
    const exp = NOW + SIGNED_TTL_MS
    const sig = await signPath(SECRET, ACC, 'quotes/CR2969/quote.pdf', exp)
    expect(await verifyPath(SECRET, ACC, 'quotes/CR2969/quote.pdf', exp, sig, NOW)).toBe(true)
  })

  it('refuses an expired signature', async () => {
    const exp = NOW - 1
    const sig = await signPath(SECRET, ACC, 'output/a.pdf', exp)
    expect(await verifyPath(SECRET, ACC, 'output/a.pdf', exp, sig, NOW)).toBe(false)
  })

  it('refuses a tampered path', async () => {
    const exp = NOW + SIGNED_TTL_MS
    const sig = await signPath(SECRET, ACC, 'output/a.pdf', exp)
    expect(await verifyPath(SECRET, ACC, 'documents/private.pdf', exp, sig, NOW)).toBe(false)
  })

  it('refuses a tampered account', async () => {
    const exp = NOW + SIGNED_TTL_MS
    const sig = await signPath(SECRET, ACC, 'output/a.pdf', exp)
    expect(await verifyPath(SECRET, 'other-account', 'output/a.pdf', exp, sig, NOW)).toBe(false)
  })

  it('refuses a tampered expiry', async () => {
    const exp = NOW + SIGNED_TTL_MS
    const sig = await signPath(SECRET, ACC, 'output/a.pdf', exp)
    expect(await verifyPath(SECRET, ACC, 'output/a.pdf', exp + 60_000, sig, NOW)).toBe(false)
  })

  it('refuses a signature made with a different secret', async () => {
    const exp = NOW + SIGNED_TTL_MS
    const sig = await signPath('other-secret', ACC, 'output/a.pdf', exp)
    expect(await verifyPath(SECRET, ACC, 'output/a.pdf', exp, sig, NOW)).toBe(false)
  })

  it('builds a url carrying account, path, expiry and signature', async () => {
    const url = new URL(await buildSignedUrl('https://install.example', SECRET, ACC, 'output/a b.pdf', NOW + 1000))
    expect(url.pathname).toBe('/api/portal/fetch')
    expect(url.searchParams.get('accountId')).toBe(ACC)
    expect(url.searchParams.get('path')).toBe('output/a b.pdf')
    expect(url.searchParams.get('exp')).toBe(String(NOW + 1000))
    expect(url.searchParams.get('sig')).toMatch(/^[0-9a-f]{64}$/)
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-sign.test.ts
```

Expected: FAIL — module not found.

- [ ] **Step 3: Write the implementation**

```js
// platform/plugins/cloudflare/skills/data-portal/template/functions/api/_lib/portal-sign.mjs
// Signed links for device-held files.
//
// The portal holds the account's folder INDEX but not its bytes, so a download
// has to reach the install. It does that with a link the install can verify
// rather than a shared secret in a header: the secret never travels, and each
// grant covers one file for five minutes.
//
// The signature proves the request came from the portal. It is NOT the security
// boundary — the install re-derives the account's exposed folder set on every
// request, so a validly-signed link for a non-exposed path is still refused.
// Keep both checks; neither is redundant.
//
// Web Crypto only. Cloudflare Workers and Node 22 both provide it, so the
// install can verify with an identical algorithm without this module crossing
// the plugin boundary into platform/ui.

/** Five minutes. Long enough for a click to become a download, short enough
 *  that a leaked link is worthless by the time it is found in a log. */
export const SIGNED_TTL_MS = 300_000

/** @param {string} accountId @param {string} relPath @param {number} expiresAtMs */
function message(accountId, relPath, expiresAtMs) {
  // Newline-separated so no field can absorb another: an accountId ending in a
  // path fragment cannot forge a different (accountId, relPath) pair.
  return `${accountId}\n${relPath}\n${expiresAtMs}`
}

/** @param {string} secret */
async function keyFor(secret) {
  return crypto.subtle.importKey(
    'raw',
    new TextEncoder().encode(secret),
    { name: 'HMAC', hash: 'SHA-256' },
    false,
    ['sign'],
  )
}

/**
 * @param {string} secret @param {string} accountId @param {string} relPath
 * @param {number} expiresAtMs @returns {Promise<string>} lowercase hex
 */
export async function signPath(secret, accountId, relPath, expiresAtMs) {
  const key = await keyFor(secret)
  const mac = await crypto.subtle.sign(
    'HMAC',
    key,
    new TextEncoder().encode(message(accountId, relPath, expiresAtMs)),
  )
  return [...new Uint8Array(mac)].map((b) => b.toString(16).padStart(2, '0')).join('')
}

/** Length-independent, value-constant comparison. A `===` on the hex would leak
 *  the matching prefix length through timing.
 *  @param {string} a @param {string} b */
function constantTimeEqual(a, b) {
  if (a.length !== b.length) return false
  let diff = 0
  for (let i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i)
  return diff === 0
}

/**
 * @param {string} secret @param {string} accountId @param {string} relPath
 * @param {number} expiresAtMs @param {string} signature @param {number} nowMs
 * @returns {Promise<boolean>}
 */
export async function verifyPath(secret, accountId, relPath, expiresAtMs, signature, nowMs) {
  if (!secret || !signature) return false
  if (!Number.isFinite(expiresAtMs) || expiresAtMs <= nowMs) return false
  const expected = await signPath(secret, accountId, relPath, expiresAtMs)
  return constantTimeEqual(expected, signature)
}

/**
 * @param {string} origin @param {string} secret @param {string} accountId
 * @param {string} relPath @param {number} expiresAtMs @returns {Promise<string>}
 */
export async function buildSignedUrl(origin, secret, accountId, relPath, expiresAtMs) {
  const sig = await signPath(secret, accountId, relPath, expiresAtMs)
  const url = new URL('/api/portal/fetch', origin)
  url.searchParams.set('accountId', accountId)
  url.searchParams.set('path', relPath)
  url.searchParams.set('exp', String(expiresAtMs))
  url.searchParams.set('sig', sig)
  return url.toString()
}
```

- [ ] **Step 4: Run test and typecheck**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-sign.test.ts && npm run typecheck:templates
```

Expected: PASS, 7 tests; typecheck exit 0.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/skills/data-portal/template/functions/api/_lib/portal-sign.mjs platform/plugins/cloudflare/mcp/__tests__/portal-sign.test.ts
git commit -m "feat(data-portal): five-minute signed links for device-held files"
```

---

### Task 3: D1 schema and enrolment binding

**Files:**
- Modify: `platform/plugins/cloudflare/skills/data-portal/schema.sql`
- Modify: `platform/plugins/cloudflare/bin/portal-enrol.mjs`
- Test: `platform/plugins/cloudflare/mcp/__tests__/portal-enrol.test.ts` (extend)

**Interfaces:**
- Produces: `directory(accountId, relPath, sizeBytes, modifiedAt, indexedAt)` with `UNIQUE (accountId, relPath)`; `people.accountId`; `portal-enrol.mjs --account <uuid>`.

- [ ] **Step 1: Write the failing test** (append to the existing describe block)

```ts
// platform/plugins/cloudflare/mcp/__tests__/portal-enrol.test.ts — append
it('emits accountId in the INSERT and the upsert', () => {
  const out = runEnrol(['--owner', 'alice', '--name', 'Alice', '--account', 'acc-1'])
  expect(out.stdout).toContain("INSERT INTO people (ownerId, name, salt, hash, createdAt, accountId)")
  expect(out.stdout).toContain("'acc-1'")
  expect(out.stdout).toContain('accountId=excluded.accountId')
})

it('refuses enrolment with no --account, because a person with no account sees nothing', () => {
  const out = runEnrol(['--owner', 'alice', '--name', 'Alice'])
  expect(out.status).not.toBe(0)
  expect(out.stderr).toContain('--account')
})
```

If `runEnrol` does not already exist in that file, add it:

```ts
import { spawnSync } from 'node:child_process'
import { resolve } from 'node:path'

function runEnrol(args: string[]) {
  const script = resolve(__dirname, '../../bin/portal-enrol.mjs')
  const r = spawnSync(process.execPath, [script, ...args], { encoding: 'utf8' })
  return { stdout: r.stdout ?? '', stderr: r.stderr ?? '', status: r.status }
}
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-enrol.test.ts
```

Expected: FAIL — the INSERT has no `accountId`, and a missing `--account` currently exits 0.

- [ ] **Step 3a: Add the schema**

Append to `platform/plugins/cloudflare/skills/data-portal/schema.sql`:

```sql
-- One row per file the device has published to this portal. Metadata only: the
-- bytes stay on the device and are fetched on demand through a signed link, so
-- the folder tree still renders when the device is offline.
--
-- Written by a full replace per account (delete-all then insert), which is why
-- there is no tombstone column: a file deleted or renamed on the device simply
-- stops being inserted on the next cycle.
CREATE TABLE IF NOT EXISTS directory (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  accountId  TEXT NOT NULL,
  relPath    TEXT NOT NULL,
  sizeBytes  INTEGER NOT NULL,
  modifiedAt TEXT NOT NULL,
  indexedAt  TEXT NOT NULL,
  UNIQUE (accountId, relPath)
);

CREATE INDEX IF NOT EXISTS directory_account ON directory (accountId);
```

Then change the `people` table in the same file to carry the account:

```sql
CREATE TABLE IF NOT EXISTS people (
  ownerId    TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  salt       TEXT NOT NULL,
  hash       TEXT NOT NULL,
  createdAt  TEXT NOT NULL,
  accountId  TEXT NOT NULL DEFAULT ''
);
```

Add this note directly above that table, matching the file's existing habit of recording migration hazards inline:

```sql
-- `accountId` binds a person to the sub-account whose folders they may read
-- (Task 1831). It carries a DEFAULT so IF NOT EXISTS stays a no-op on a table
-- that predates it — but an existing table will NOT gain the column, and a
-- person whose row has the default empty value sees an empty folder list. Enrol
-- those people again; the upsert on ownerId makes that a re-run, not a migration.
```

- [ ] **Step 3b: Update the enrolment script**

In `platform/plugins/cloudflare/bin/portal-enrol.mjs`, after the existing `--name` handling, add:

```js
const accountId = arg('--account')
// A person with no account resolves to an empty folder list, which is
// indistinguishable from "this account has no deliverables". Refuse at
// enrolment rather than mint a row that fails silently later.
if (!accountId) die('--account <accountId> is required')
if (!/^[A-Za-z0-9-]+$/.test(accountId)) die('--account must be an account id')
```

Replace the emitted statement with:

```js
console.log(
  `INSERT INTO people (ownerId, name, salt, hash, createdAt, accountId) VALUES ('${ownerId}', '${sqlName}', '${saltHex}', '${hash}', '${createdAt}', '${accountId}') ON CONFLICT(ownerId) DO UPDATE SET name=excluded.name, salt=excluded.salt, hash=excluded.hash, accountId=excluded.accountId;`,
)
```

Update the usage comment at the top of the file to include `--account <accountId>`.

- [ ] **Step 4: Run tests**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-enrol.test.ts
```

Expected: PASS, including the pre-existing enrolment tests.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/skills/data-portal/schema.sql platform/plugins/cloudflare/bin/portal-enrol.mjs platform/plugins/cloudflare/mcp/__tests__/portal-enrol.test.ts
git commit -m "feat(data-portal): directory table and per-person account binding"
```

---

### Task 4: D1 over HTTP with bound parameters

The cloudflare plugin owns Cloudflare, so its D1 client lives here. `cf-exec.ts:256` spawns `wrangler` per call and takes raw SQL — the Task 1805 defect plus an injection risk on filenames — so it is not reused. Convergence is a follow-up (Task 1834).

**Files:**
- Create: `platform/plugins/cloudflare/bin/d1-http.mjs`
- Test: `platform/plugins/cloudflare/mcp/__tests__/d1-http.test.ts`

**Interfaces:**
- Produces: `createD1Client({ accountId, token, dbName, fetchFn? }): { query(sql: string, params?: unknown[]): Promise<Record<string, unknown>[]>, batch(statements: {sql: string, params?: unknown[]}[]): Promise<void>, readonly statements: number }`

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/d1-http.test.ts
import { describe, it, expect } from 'vitest'
import { createD1Client } from '../../bin/d1-http.mjs'

type Call = { url: string; init: { method?: string; body?: string; headers?: Record<string, string> } }

function fakeFetch(calls: Call[], results: unknown[] = [{ results: [] }]) {
  return async (url: string, init: Call['init']) => {
    calls.push({ url, init })
    if (url.includes('?name=')) {
      return { ok: true, status: 200, json: async () => ({ result: [{ name: 'portal-db', uuid: 'db-uuid' }] }) }
    }
    return { ok: true, status: 200, json: async () => ({ result: results }) }
  }
}

describe('createD1Client', () => {
  it('resolves the database by exact name then queries by uuid', async () => {
    const calls: Call[] = []
    const c = createD1Client({ accountId: 'acc', token: 't', dbName: 'portal-db', fetchFn: fakeFetch(calls) as never })
    await c.query('SELECT 1')
    expect(calls[0].url).toContain('/accounts/acc/d1/database?name=portal-db')
    expect(calls[1].url).toContain('/d1/database/db-uuid/query')
    expect(calls[1].init.headers?.Authorization).toBe('Bearer t')
  })

  it('sends values as params, never interpolated into sql', async () => {
    const calls: Call[] = []
    const c = createD1Client({ accountId: 'acc', token: 't', dbName: 'portal-db', fetchFn: fakeFetch(calls) as never })
    await c.query('INSERT INTO directory (relPath) VALUES (?)', ["o'brien.pdf"])
    const body = JSON.parse(calls[1].init.body ?? '{}')
    expect(body.sql).toBe('INSERT INTO directory (relPath) VALUES (?)')
    expect(body.params).toEqual(["o'brien.pdf"])
  })

  it('caches the name lookup across statements', async () => {
    const calls: Call[] = []
    const c = createD1Client({ accountId: 'acc', token: 't', dbName: 'portal-db', fetchFn: fakeFetch(calls) as never })
    await c.query('SELECT 1')
    await c.query('SELECT 2')
    expect(calls.filter((x) => x.url.includes('?name=')).length).toBe(1)
    expect(c.statements).toBe(2)
  })

  it('throws with the response body when the api fails', async () => {
    const fetchFn = async () => ({ ok: false, status: 403, json: async () => ({ errors: [{ message: 'nope' }] }) })
    const c = createD1Client({ accountId: 'acc', token: 't', dbName: 'portal-db', fetchFn: fetchFn as never })
    await expect(c.query('SELECT 1')).rejects.toThrow(/403|nope/)
  })

  it('refuses a database whose name only nearly matches', async () => {
    const fetchFn = async (url: string) => ({
      ok: true, status: 200,
      json: async () => (url.includes('?name=') ? { result: [{ name: 'portal-db-old', uuid: 'x' }] } : { result: [] }),
    })
    const c = createD1Client({ accountId: 'acc', token: 't', dbName: 'portal-db', fetchFn: fetchFn as never })
    await expect(c.query('SELECT 1')).rejects.toThrow(/no D1 database named/)
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/d1-http.test.ts
```

Expected: FAIL — module not found.

- [ ] **Step 3: Write the implementation**

```js
// platform/plugins/cloudflare/bin/d1-http.mjs
// D1 over the Cloudflare HTTP API, with bound parameters.
//
// Not `wrangler d1 execute`: that spawns a process per statement, which is the
// CPU-burn defect Task 1805 fixed, and it takes a raw SQL string, which would
// mean interpolating filenames into SQL. The index push writes one row per file,
// so both matter here.
//
// storage-broker's cf-exec.d1Query still spawns wrangler. Converging the two is
// Task 1834; this client does not depend on it.

const API = 'https://api.cloudflare.com/client/v4'

/** @param {unknown} parsed @param {number} status @param {string} label */
function assertOk(parsed, status, label) {
  const errs = /** @type {{errors?: {message?: string}[]}} */ (parsed)?.errors
  const detail = Array.isArray(errs) && errs.length ? errs.map((e) => e?.message).join('; ') : ''
  // Never discard the body — a bare status turns every failure into the same
  // unactionable line.
  throw new Error(`${label} failed: HTTP ${status}${detail ? ` — ${detail}` : ''}`)
}

/**
 * @param {{ accountId: string, token: string, dbName: string,
 *           fetchFn?: (url: string, init: object) => Promise<{ ok: boolean, status: number, json: () => Promise<unknown> }> }} opts
 */
export function createD1Client(opts) {
  const fetchFn = opts.fetchFn ?? /** @type {never} */ (globalThis.fetch)
  const headers = { Authorization: `Bearer ${opts.token}`, 'Content-Type': 'application/json' }
  /** @type {string | undefined} */ let databaseId
  let statements = 0

  async function resolveId() {
    if (databaseId !== undefined) return databaseId
    const url = `${API}/accounts/${opts.accountId}/d1/database?name=${encodeURIComponent(opts.dbName)}`
    const res = await fetchFn(url, { method: 'GET', headers })
    const parsed = await res.json()
    if (!res.ok) assertOk(parsed, res.status, 'd1 database lookup')
    const list = /** @type {{result?: {name?: string, uuid?: string}[]}} */ (parsed).result ?? []
    // Exact name match, never "the first result": `?name=` is a filter whose
    // matching rule is not documented, so position would risk running this
    // account's statements against a near-named database.
    const hit = list.find((d) => d.name === opts.dbName)
    if (!hit || typeof hit.uuid !== 'string') {
      throw new Error(`no D1 database named "${opts.dbName}" in account ${opts.accountId}`)
    }
    databaseId = hit.uuid
    return databaseId
  }

  /** @param {string} sql @param {unknown[]} [params] */
  async function query(sql, params = []) {
    const id = await resolveId()
    statements++
    const res = await fetchFn(`${API}/accounts/${opts.accountId}/d1/database/${id}/query`, {
      method: 'POST',
      headers,
      body: JSON.stringify({ sql, params }),
    })
    const parsed = await res.json()
    if (!res.ok) assertOk(parsed, res.status, 'd1 query')
    const first = (/** @type {{result?: {results?: unknown}[]}} */ (parsed).result ?? [])[0]
    return /** @type {Record<string, unknown>[]} */ (first?.results ?? [])
  }

  return {
    query,
    /** @param {{sql: string, params?: unknown[]}[]} stmts */
    async batch(stmts) {
      for (const s of stmts) await query(s.sql, s.params ?? [])
    },
    get statements() {
      return statements
    },
  }
}
```

- [ ] **Step 4: Run test to verify it passes**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/d1-http.test.ts
```

Expected: PASS, 5 tests.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/bin/d1-http.mjs platform/plugins/cloudflare/mcp/__tests__/d1-http.test.ts
git commit -m "feat(data-portal): D1 http client with bound params for the index push"
```

---

### Task 5: The index push

**Files:**
- Create: `platform/plugins/cloudflare/bin/portal-index-push.mjs`
- Test: `platform/plugins/cloudflare/mcp/__tests__/portal-index-push.test.ts`

**Interfaces:**
- Consumes: `resolveExposedDirs` (Task 1), `createD1Client` (Task 4).
- Produces: `walkExposed(accountDir: string, exposed: string[]): Promise<{relPath: string, sizeBytes: number, modifiedAt: string}[]>` and `pushAccount({ accountDir, accountId, dbName, client, log, nowIso }): Promise<{ exposed: string[], rows: number, schemaPresent: boolean }>`

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/portal-index-push.test.ts
import { describe, it, expect, beforeEach, afterEach } from 'vitest'
import { mkdtempSync, mkdirSync, writeFileSync, rmSync } from 'node:fs'
import { tmpdir } from 'node:os'
import { join } from 'node:path'
import { walkExposed, pushAccount } from '../../bin/portal-index-push.mjs'

let dir: string

function file(rel: string, body = 'x') {
  const abs = join(dir, rel)
  mkdirSync(join(abs, '..'), { recursive: true })
  writeFileSync(abs, body)
}

const SCHEMA = [
  '```allowed-top-level',
  'output', 'quotes', 'documents', 'uploads',
  '```',
  '',
  '<!-- ontology-buckets:start -->',
  '- `quotes/` - one folder per Quotation record.',
  '<!-- ontology-buckets:end -->',
].join('\n')

beforeEach(() => {
  dir = mkdtempSync(join(tmpdir(), 'portal-push-'))
  writeFileSync(join(dir, 'SCHEMA.md'), SCHEMA)
  file('output/report.pdf')
  file('quotes/CR2969/quote.pdf')
  file('quotes/CR2974/quote.pdf')
  file('documents/private.pdf')
  file('uploads/client-sent.pdf')
  file('.claude/settings.json')
  file('output/.uploads-tmp/partial.bin')
})
afterEach(() => rmSync(dir, { recursive: true, force: true }))

function fakeClient() {
  const calls: { sql: string; params: unknown[] }[] = []
  return {
    calls,
    async query(sql: string, params: unknown[] = []) {
      calls.push({ sql, params })
      if (/SELECT COUNT/i.test(sql)) return [{ n: calls.filter((c) => /INSERT/i.test(c.sql)).length }]
      return []
    },
    async batch(stmts: { sql: string; params?: unknown[] }[]) {
      for (const s of stmts) await this.query(s.sql, s.params ?? [])
    },
    statements: 0,
  }
}

describe('walkExposed', () => {
  it('returns files only from exposed dirs, at any depth', async () => {
    const rows = await walkExposed(dir, ['output', 'quotes'])
    const paths = rows.map((r) => r.relPath).sort()
    expect(paths).toEqual(['output/report.pdf', 'quotes/CR2969/quote.pdf', 'quotes/CR2974/quote.pdf'])
  })

  it('never returns operator data, uploads, dot dirs or upload temps', async () => {
    const paths = (await walkExposed(dir, ['output', 'quotes'])).map((r) => r.relPath)
    expect(paths.some((p) => p.startsWith('documents/'))).toBe(false)
    expect(paths.some((p) => p.startsWith('uploads/'))).toBe(false)
    expect(paths.some((p) => p.includes('.uploads-tmp'))).toBe(false)
    expect(paths.some((p) => p.includes('.claude'))).toBe(false)
  })

  it('returns nothing for a dir that does not exist', async () => {
    expect(await walkExposed(dir, ['nope'])).toEqual([])
  })

  it('carries size and an iso modified time', async () => {
    const row = (await walkExposed(dir, ['output']))[0]
    expect(row.sizeBytes).toBe(1)
    expect(row.modifiedAt).toMatch(/^\d{4}-\d{2}-\d{2}T/)
  })
})

describe('pushAccount', () => {
  it('deletes the account rows then inserts the current walk', async () => {
    const c = fakeClient()
    const r = await pushAccount({ accountDir: dir, accountId: 'acc-1', dbName: 'db', client: c as never, log: () => {}, nowIso: '2026-07-20T00:00:00.000Z' })
    expect(r.exposed).toEqual(['output', 'quotes'])
    expect(r.rows).toBe(3)
    expect(/DELETE FROM directory/i.test(c.calls[0].sql)).toBe(true)
    expect(c.calls[0].params).toEqual(['acc-1'])
    expect(c.calls.filter((x) => /INSERT INTO directory/i.test(x.sql)).length).toBe(3)
  })

  it('exposes nothing and writes nothing when the schema is missing', async () => {
    rmSync(join(dir, 'SCHEMA.md'))
    const c = fakeClient()
    const r = await pushAccount({ accountDir: dir, accountId: 'acc-1', dbName: 'db', client: c as never, log: () => {}, nowIso: 'now' })
    expect(r.schemaPresent).toBe(false)
    expect(r.exposed).toEqual([])
    expect(c.calls.length).toBe(0)
  })

  it('logs resolve, replace and verify lines', async () => {
    const lines: string[] = []
    const c = fakeClient()
    await pushAccount({ accountDir: dir, accountId: 'acc-1', dbName: 'db', client: c as never, log: (l: string) => lines.push(l), nowIso: 'now' })
    expect(lines.some((l) => l.includes('op=resolve') && l.includes('exposed=output,quotes'))).toBe(true)
    expect(lines.some((l) => l.includes('op=replace') && l.includes('inserted=3'))).toBe(true)
    expect(lines.some((l) => l.includes('op=verify'))).toBe(true)
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-index-push.test.ts
```

Expected: FAIL — module not found.

- [ ] **Step 3: Write the implementation**

```js
// platform/plugins/cloudflare/bin/portal-index-push.mjs
// Publish a sub-account's deliverable file INDEX to its portal's D1.
//
// Metadata only. The bytes stay on the device and are fetched on demand through
// a signed link, which is what lets the portal render the folder tree while the
// device is offline.
//
// Full replace per account rather than a diff: a delete or a rename on the
// device converges on the next cycle with no tombstone protocol to get wrong.
//
// Runnable standalone as well as from the heartbeat, because the heartbeat spawn
// this copies has never been observed running on a device (Task 1828).
//
//   node portal-index-push.mjs --account-dir <dir> --account <id> --db <name> --token <cf-token>

import { readFile, readdir, stat } from 'node:fs/promises'
import { join, resolve } from 'node:path'
import { resolveExposedDirs } from './schema-exposed-dirs.mjs'
import { createD1Client } from './d1-http.mjs'

const TAG = '[portal-index]'

/**
 * Files under `exposed` dirs, at any depth. Dot-prefixed entries are skipped at
 * every level: `.uploads-tmp/` holds in-flight upload partials (files.ts:25) and
 * must never be published as a deliverable.
 * @param {string} accountDir @param {string[]} exposed
 * @returns {Promise<{relPath: string, sizeBytes: number, modifiedAt: string}[]>}
 */
export async function walkExposed(accountDir, exposed) {
  /** @type {{relPath: string, sizeBytes: number, modifiedAt: string}[]} */
  const out = []

  /** @param {string} rel */
  async function walk(rel) {
    /** @type {import('node:fs').Dirent[]} */ let entries
    try {
      entries = await readdir(join(accountDir, rel), { withFileTypes: true })
    } catch {
      return // absent or unreadable dir contributes nothing
    }
    for (const e of entries) {
      if (e.name.startsWith('.')) continue
      const childRel = `${rel}/${e.name}`
      if (e.isDirectory()) {
        await walk(childRel)
      } else if (e.isFile()) {
        try {
          const st = await stat(join(accountDir, childRel))
          out.push({ relPath: childRel, sizeBytes: st.size, modifiedAt: st.mtime.toISOString() })
        } catch {
          // vanished mid-walk; the next cycle settles it
        }
      }
    }
  }

  for (const dir of exposed) await walk(dir)
  return out
}

/**
 * @param {{ accountDir: string, accountId: string, dbName: string,
 *           client: { query: (sql: string, params?: unknown[]) => Promise<Record<string, unknown>[]> },
 *           log: (line: string) => void, nowIso: string }} opts
 * @returns {Promise<{ exposed: string[], rows: number, schemaPresent: boolean }>}
 */
export async function pushAccount(opts) {
  const { accountDir, accountId, client, log, nowIso } = opts

  /** @type {string | null} */ let schemaText = null
  try {
    schemaText = await readFile(join(accountDir, 'SCHEMA.md'), 'utf8')
  } catch {
    schemaText = null
  }

  const r = resolveExposedDirs(schemaText)
  log(
    `${TAG} op=resolve account=${accountId} schemaPresent=${r.schemaPresent} allowed=${r.allowed.length} ` +
      `domainBuckets=${r.domainBuckets.join(',') || 'none'} exposed=${r.exposed.join(',') || 'none'}`,
  )
  // A dir the generator gave to a plugin never reaches the ontology region
  // (account-schema-owned-dirs.py:287), so this is normally silent. When it is
  // not, a bucket has silently vanished from the client's view and nothing else
  // would say so.
  for (const c of r.collisions) {
    log(`${TAG} op=collision account=${accountId} dir=${c} claimedBy=plugin-owned`)
  }

  // Fail closed: no schema means expose nothing, and writing nothing is the
  // correct outcome — NOT a full delete, which would look identical to a
  // successful empty publish.
  if (!r.schemaPresent || r.exposed.length === 0) return { exposed: r.exposed, rows: 0, schemaPresent: r.schemaPresent }

  const rows = await walkExposed(accountDir, r.exposed)

  await client.query('DELETE FROM directory WHERE accountId = ?', [accountId])
  for (const row of rows) {
    await client.query(
      'INSERT INTO directory (accountId, relPath, sizeBytes, modifiedAt, indexedAt) VALUES (?, ?, ?, ?, ?)',
      [accountId, row.relPath, row.sizeBytes, row.modifiedAt, nowIso],
    )
  }
  log(`${TAG} op=replace account=${accountId} deleted=all inserted=${rows.length}`)

  // Read back rather than trusting the write: "issued the inserts" is not "the
  // rows are there", and a half-applied batch is otherwise invisible.
  const check = await client.query('SELECT COUNT(*) AS n FROM directory WHERE accountId = ?', [accountId])
  const after = Number(check[0]?.n ?? -1)
  log(`${TAG} op=verify account=${accountId} rowsAfter=${after} expected=${rows.length}`)

  return { exposed: r.exposed, rows: rows.length, schemaPresent: true }
}

/** @param {string} flag */
function arg(flag) {
  const i = process.argv.indexOf(flag)
  return i === -1 ? undefined : process.argv[i + 1]
}

// Only runs when invoked directly, so the module stays importable by tests.
if (process.argv[1] && resolve(process.argv[1]).endsWith('portal-index-push.mjs')) {
  const accountDir = arg('--account-dir')
  const accountId = arg('--account')
  const dbName = arg('--db')
  const token = arg('--token') ?? process.env.CLOUDFLARE_API_TOKEN
  if (!accountDir || !accountId || !dbName || !token) {
    console.error(`${TAG} op=usage --account-dir <dir> --account <id> --db <name> --token <cf-token>`)
    process.exit(1)
  }
  const client = createD1Client({ accountId, token, dbName })
  pushAccount({ accountDir, accountId, dbName, client, log: (l) => console.error(l), nowIso: new Date().toISOString() })
    .then((r) => {
      console.error(`${TAG} op=done account=${accountId} rows=${r.rows}`)
    })
    .catch((err) => {
      console.error(`${TAG} op=failed account=${accountId} err="${err instanceof Error ? err.message : String(err)}"`)
      process.exit(1)
    })
}
```

- [ ] **Step 4: Run test to verify it passes**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-index-push.test.ts
```

Expected: PASS, 7 tests.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/bin/portal-index-push.mjs platform/plugins/cloudflare/mcp/__tests__/portal-index-push.test.ts
git commit -m "feat(data-portal): device-side index push over exposed dirs only"
```

---

### Task 6: Portal listing returns folder entries

**Files:**
- Modify: `.../template/functions/api/_lib/session.ts` (`resolveSession` returns `accountId`)
- Modify: `.../template/functions/api/files.ts`
- Test: `platform/plugins/cloudflare/mcp/__tests__/portal-directory-listing.test.ts`

**Interfaces:**
- Consumes: `directory` table (Task 3), `people.accountId` (Task 3).
- Produces: `processFiles(sessionId, env, log, nowMs, prefix?)` returning payload `{ ok, files, entries }` where `entries: { name: string, kind: 'file' | 'directory', relPath: string, sizeBytes: number | null, modifiedAt: string | null }[]`.

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/portal-directory-listing.test.ts
import { describe, it, expect } from 'vitest'
import { processFiles } from '../../skills/data-portal/template/functions/api/files'
import type { PortalEnv } from '../../skills/data-portal/template/functions/api/_lib/types'

const DIRECTORY = [
  { accountId: 'acc-a', relPath: 'output/report.pdf', sizeBytes: 10, modifiedAt: '2026-07-20T10:00:00.000Z' },
  { accountId: 'acc-a', relPath: 'quotes/CR2969/quote.pdf', sizeBytes: 20, modifiedAt: '2026-07-20T11:00:00.000Z' },
  { accountId: 'acc-a', relPath: 'quotes/CR2974/quote.pdf', sizeBytes: 30, modifiedAt: '2026-07-20T12:00:00.000Z' },
  { accountId: 'acc-b', relPath: 'output/secret.pdf', sizeBytes: 40, modifiedAt: '2026-07-20T13:00:00.000Z' },
]

function makeEnv(sessions: Record<string, { ownerId: string; accountId: string }>) {
  return {
    DB: {
      prepare(query: string) {
        let bound: unknown[] = []
        const stmt = {
          bind(...v: unknown[]) { bound = v; return stmt },
          async run() { return { meta: { changes: 0 } } },
          async all<T>() {
            if (/FROM directory/i.test(query)) {
              return { results: DIRECTORY.filter((d) => d.accountId === bound[0]) as T[] }
            }
            return { results: [] as T[] }
          },
          async first<T>() {
            if (/FROM sessions/i.test(query)) return (sessions[bound[0] as string] ?? null) as T | null
            return null as T | null
          },
        }
        return stmt
      },
    },
  } as unknown as PortalEnv
}

const SESSIONS = { 'sess-a': { ownerId: 'alice', accountId: 'acc-a' } }

describe('portal directory listing', () => {
  it('synthesises top-level folders from row paths', async () => {
    const r = await processFiles('sess-a', makeEnv(SESSIONS), () => {}, 0, '')
    const entries = r.payload.entries as { name: string; kind: string }[]
    expect(entries.map((e) => e.name).sort()).toEqual(['output', 'quotes'])
    expect(entries.every((e) => e.kind === 'directory')).toBe(true)
  })

  it('lists folders inside a prefix', async () => {
    const r = await processFiles('sess-a', makeEnv(SESSIONS), () => {}, 0, 'quotes')
    const entries = r.payload.entries as { name: string; kind: string }[]
    expect(entries.map((e) => e.name).sort()).toEqual(['CR2969', 'CR2974'])
  })

  it('lists files inside a leaf folder with size and time', async () => {
    const r = await processFiles('sess-a', makeEnv(SESSIONS), () => {}, 0, 'quotes/CR2969')
    const entries = r.payload.entries as { name: string; kind: string; relPath: string; sizeBytes: number }[]
    expect(entries).toEqual([
      { name: 'quote.pdf', kind: 'file', relPath: 'quotes/CR2969/quote.pdf', sizeBytes: 20, modifiedAt: '2026-07-20T11:00:00.000Z' },
    ])
  })

  it('never leaks another account rows', async () => {
    const r = await processFiles('sess-a', makeEnv(SESSIONS), () => {}, 0, '')
    expect(JSON.stringify(r.payload)).not.toContain('secret.pdf')
  })

  it('returns an empty entries list when the person has no account bound', async () => {
    const env = makeEnv({ 'sess-a': { ownerId: 'alice', accountId: '' } })
    const r = await processFiles('sess-a', env, () => {}, 0, '')
    expect(r.status).toBe(200)
    expect(r.payload.entries).toEqual([])
  })

  it('logs the account and the prefix', async () => {
    const lines: string[] = []
    await processFiles('sess-a', makeEnv(SESSIONS), (l) => lines.push(l), 0, 'quotes')
    expect(lines.some((l) => l.includes('account=acc-a') && l.includes('prefix=quotes'))).toBe(true)
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-directory-listing.test.ts
```

Expected: FAIL — `processFiles` takes four arguments and returns no `entries`.

- [ ] **Step 3a: Widen `resolveSession`**

In `_lib/session.ts`, change the query and return type so the session carries the account it may read:

```ts
export async function resolveSession(
  db: D1Database,
  sessionId: string,
  nowMs: number,
): Promise<{ ownerId: string; accountId: string } | null> {
  if (!sessionId) return null
  const row = await db
    .prepare(
      `SELECT s.ownerId AS ownerId, p.accountId AS accountId
         FROM sessions s
         JOIN people p ON p.ownerId = s.ownerId
        WHERE s.sessionId = ?
          AND s.expiresAt > ?
          AND s.pcCheck = substr(p.hash, 1, 16)`,
    )
    .bind(sessionId, nowMs)
    .first<{ ownerId: string; accountId: string }>()
  if (!row || typeof row.ownerId !== 'string') return null
  // A row predating Task 1831 has no accountId. Treat it as "no folders", never
  // as "all folders".
  return { ownerId: row.ownerId, accountId: typeof row.accountId === 'string' ? row.accountId : '' }
}
```

- [ ] **Step 3b: Return entries from `files.ts`**

Replace the body of `processFiles` and add the folder synthesis:

```ts
interface DirRow {
  relPath: string
  sizeBytes: number
  modifiedAt: string
}

export interface DirEntry {
  name: string
  kind: 'file' | 'directory'
  relPath: string
  sizeBytes: number | null
  modifiedAt: string | null
}

/**
 * Collapse flat `directory` rows into one level of listing under `prefix`.
 * Folders are synthesised from path segments rather than stored, so the device
 * publishes files alone and an empty folder simply does not exist.
 */
export function entriesUnder(rows: DirRow[], prefix: string): DirEntry[] {
  const base = prefix ? `${prefix}/` : ''
  const dirs = new Map<string, DirEntry>()
  const files: DirEntry[] = []
  for (const row of rows) {
    if (base && !row.relPath.startsWith(base)) continue
    const rest = row.relPath.slice(base.length)
    if (!rest) continue
    const slash = rest.indexOf('/')
    if (slash === -1) {
      files.push({
        name: rest,
        kind: 'file',
        relPath: row.relPath,
        sizeBytes: row.sizeBytes,
        modifiedAt: row.modifiedAt,
      })
    } else {
      const name = rest.slice(0, slash)
      if (!dirs.has(name)) {
        dirs.set(name, { name, kind: 'directory', relPath: `${base}${name}`, sizeBytes: null, modifiedAt: null })
      }
    }
  }
  const dirList = [...dirs.values()].sort((a, b) => a.name.localeCompare(b.name))
  files.sort((a, b) => (b.modifiedAt ?? '').localeCompare(a.modifiedAt ?? '') || a.name.localeCompare(b.name))
  return [...dirList, ...files]
}

export async function processFiles(
  sessionId: string,
  env: PortalEnv,
  log: Logger,
  nowMs: number,
  prefix = '',
): Promise<Handler> {
  const session = await resolveSession(env.DB, sessionId, nowMs)
  if (!session) {
    log('[data-portal] op=list owner=none result=denied')
    return { status: 401, payload: { ok: false, error: 'denied' } }
  }
  // Scoped by ownerId, not by key prefix: the owner is the authority.
  const rows = await env.DB.prepare(
    'SELECT fileId, filename, objectKey, size, uploadedAt, ingested FROM manifest WHERE ownerId = ? ORDER BY uploadedAt DESC',
  )
    .bind(session.ownerId)
    .all<Record<string, unknown>>()
  const files = rows.results ?? []

  // No account bound means no folders. An enrolment predating Task 1831 lands
  // here; the missing `account=` field in the log line is what says so.
  let entries: DirEntry[] = []
  if (session.accountId) {
    const dir = await env.DB.prepare(
      'SELECT relPath, sizeBytes, modifiedAt FROM directory WHERE accountId = ?',
    )
      .bind(session.accountId)
      .all<DirRow>()
    entries = entriesUnder(dir.results ?? [], prefix)
  }

  log(
    `[data-portal] op=list owner=${q(session.ownerId)} account=${q(session.accountId)} ` +
      `prefix=${q(prefix)} files=${files.length} entries=${entries.length} result=ok`,
  )
  return { status: 200, payload: { ok: true, files, entries } }
}
```

Then thread the prefix through the route handler:

```ts
export async function onRequestGet(context: PagesContext): Promise<Response> {
  const sessionId = readSessionCookie(context.request.headers.get('cookie')) ?? ''
  const prefix = new URL(context.request.url).searchParams.get('prefix') ?? ''
  const { status, payload } = await processFiles(
    sessionId,
    context.env,
    (line) => console.log(line),
    Date.now(),
    prefix,
  )
  return Response.json(payload, { status })
}
```

- [ ] **Step 4: Run the new test and the existing suite**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run && npm run typecheck:templates
```

Expected: all files pass; the pre-existing `file-routes.test.ts` still green (its fake `first()` returns `{ownerId}` with no `accountId`, which the widened resolver tolerates); typecheck exit 0.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/skills/data-portal/template/functions/api/_lib/session.ts platform/plugins/cloudflare/skills/data-portal/template/functions/api/files.ts platform/plugins/cloudflare/mcp/__tests__/portal-directory-listing.test.ts
git commit -m "feat(data-portal): list the account's indexed folders beside uploads"
```

---

### Task 7: Download branch for indexed files

**Files:**
- Modify: `.../template/functions/api/_lib/types.ts`
- Modify: `.../template/functions/api/download.ts`
- Test: `platform/plugins/cloudflare/mcp/__tests__/portal-indexed-download.test.ts`

**Interfaces:**
- Consumes: `buildSignedUrl`, `SIGNED_TTL_MS` (Task 2); `resolveSession` returning `accountId` (Task 6).
- Produces: `processIndexedDownload(sessionId, relPath, env, log, nowMs, fetchFn): Promise<Handler & { url?: string }>`.

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/portal-indexed-download.test.ts
import { describe, it, expect } from 'vitest'
import { processIndexedDownload } from '../../skills/data-portal/template/functions/api/download'
import type { PortalEnv } from '../../skills/data-portal/template/functions/api/_lib/types'

const ROW = { relPath: 'quotes/CR2969/quote.pdf', sizeBytes: 20, modifiedAt: '2026-07-20T11:00:00.000Z' }

function makeEnv(rows = [ROW], accountId = 'acc-a') {
  return {
    DB: {
      prepare(query: string) {
        let bound: unknown[] = []
        const stmt = {
          bind(...v: unknown[]) { bound = v; return stmt },
          async run() { return { meta: { changes: 0 } } },
          async all<T>() { return { results: [] as T[] } },
          async first<T>() {
            if (/FROM sessions/i.test(query)) {
              return (bound[0] === 'sess-a' ? { ownerId: 'alice', accountId } : null) as T | null
            }
            if (/FROM directory/i.test(query)) {
              return (rows.find((r) => r.relPath === bound[1]) ?? null) as T | null
            }
            return null as T | null
          },
        }
        return stmt
      },
    },
    PORTAL_FETCH_SECRET: 'secret',
    PORTAL_INSTALL_ORIGIN: 'https://install.example',
  } as unknown as PortalEnv
}

const okHead = async () => ({ ok: true, status: 200 })
const deadHead = async () => { throw new Error('ECONNREFUSED') }

describe('processIndexedDownload', () => {
  it('returns a signed url for an indexed file when the device answers', async () => {
    const r = await processIndexedDownload('sess-a', ROW.relPath, makeEnv(), () => {}, 1000, okHead as never)
    expect(r.status).toBe(200)
    const url = new URL(r.url as string)
    expect(url.origin).toBe('https://install.example')
    expect(url.searchParams.get('path')).toBe(ROW.relPath)
    expect(url.searchParams.get('sig')).toMatch(/^[0-9a-f]{64}$/)
    expect(Number(url.searchParams.get('exp'))).toBeGreaterThan(1000)
  })

  it('reports device-offline distinguishably when the head fails', async () => {
    const r = await processIndexedDownload('sess-a', ROW.relPath, makeEnv(), () => {}, 1000, deadHead as never)
    expect(r.status).toBe(503)
    expect(r.payload.error).toBe('device-offline')
    expect(r.url).toBeUndefined()
  })

  it('404s a path that is not in the index, without contacting the device', async () => {
    let touched = false
    const spy = async () => { touched = true; return { ok: true, status: 200 } }
    const r = await processIndexedDownload('sess-a', 'documents/private.pdf', makeEnv(), () => {}, 1000, spy as never)
    expect(r.status).toBe(404)
    expect(touched).toBe(false)
  })

  it('401s without a session', async () => {
    const r = await processIndexedDownload('nope', ROW.relPath, makeEnv(), () => {}, 1000, okHead as never)
    expect(r.status).toBe(401)
  })

  it('404s when the person has no account bound', async () => {
    const r = await processIndexedDownload('sess-a', ROW.relPath, makeEnv([ROW], ''), () => {}, 1000, okHead as never)
    expect(r.status).toBe(404)
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/portal-indexed-download.test.ts
```

Expected: FAIL — `processIndexedDownload` is not exported.

- [ ] **Step 3a: Extend `PortalEnv`**

In `_lib/types.ts`, add to the `PortalEnv` interface:

```ts
export interface PortalEnv {
  DB: D1Database
  BUCKET: R2Bucket
  /** Shared with the install; signs download links. Never sent to the client. */
  PORTAL_FETCH_SECRET?: string
  /** The install's public origin, e.g. https://<tunnel-hostname>. */
  PORTAL_INSTALL_ORIGIN?: string
}
```

- [ ] **Step 3b: Add the indexed branch to `download.ts`**

Append to `download.ts` (leaving `processDownload` untouched, so the R2 upload path cannot regress):

```ts
import { buildSignedUrl, SIGNED_TTL_MS } from './_lib/portal-sign.mjs'

type HeadFetch = (url: string, init: { method: string }) => Promise<{ ok: boolean; status: number }>

interface IndexedDownloadResult extends Handler {
  url?: string
}

/**
 * Hand back a short-lived signed link to a file the DEVICE holds.
 *
 * The index is checked first, so a path the device never published costs no
 * network call — and `documents/` is not in the index by construction, which is
 * why a client cannot name their way into it.
 *
 * The device is probed before the link is minted. Without that, an offline
 * device produces an opaque browser failure and the client has no idea why;
 * with it they get "device offline" and can try later. The probe-then-fetch
 * window is real but small, and losing it degrades to exactly the opaque
 * failure this avoids in the common case.
 */
export async function processIndexedDownload(
  sessionId: string,
  relPath: string,
  env: PortalEnv,
  log: Logger,
  nowMs: number,
  fetchFn: HeadFetch,
): Promise<IndexedDownloadResult> {
  const session = await resolveSession(env.DB, sessionId, nowMs)
  if (!session) {
    log('[data-portal] op=fetch-through owner=none result=denied')
    return { status: 401, payload: { ok: false, error: 'denied' } }
  }
  if (!session.accountId) {
    log(`[data-portal] op=fetch-through owner=${q(session.ownerId)} result=no-account`)
    return { status: 404, payload: { ok: false, error: 'not found' } }
  }
  const row = await env.DB.prepare(
    'SELECT relPath FROM directory WHERE accountId = ? AND relPath = ?',
  )
    .bind(session.accountId, relPath)
    .first<{ relPath: string }>()
  if (!row) {
    log(`[data-portal] op=fetch-through owner=${q(session.ownerId)} path=${q(relPath)} result=absent`)
    return { status: 404, payload: { ok: false, error: 'not found' } }
  }

  const secret = env.PORTAL_FETCH_SECRET
  const origin = env.PORTAL_INSTALL_ORIGIN
  if (!secret || !origin) {
    log(`[data-portal] op=fetch-through owner=${q(session.ownerId)} result=unconfigured`)
    return { status: 503, payload: { ok: false, error: 'device-offline' } }
  }

  const expiresAt = nowMs + SIGNED_TTL_MS
  const url = await buildSignedUrl(origin, secret, session.accountId, relPath, expiresAt)

  const started = Date.now()
  try {
    const head = await fetchFn(url, { method: 'HEAD' })
    if (!head.ok) {
      log(
        `[data-portal] op=fetch-through owner=${q(session.ownerId)} path=${q(relPath)} ` +
          `status=${head.status} ms=${Date.now() - started} result=device-error`,
      )
      return { status: 503, payload: { ok: false, error: 'device-offline' } }
    }
  } catch (err) {
    log(
      `[data-portal] op=fetch-through-failed owner=${q(session.ownerId)} path=${q(relPath)} ` +
        `err=${q(err instanceof Error ? err.message : String(err))} result=device-offline`,
    )
    return { status: 503, payload: { ok: false, error: 'device-offline' } }
  }

  log(
    `[data-portal] op=fetch-through owner=${q(session.ownerId)} path=${q(relPath)} ` +
      `status=200 ms=${Date.now() - started} result=ok`,
  )
  return { status: 200, payload: { ok: true, url }, url }
}
```

Extend `onRequestGet` to branch on which parameter arrived:

```ts
export async function onRequestGet(context: PagesContext): Promise<Response> {
  const sessionId = readSessionCookie(context.request.headers.get('cookie')) ?? ''
  const params = new URL(context.request.url).searchParams
  const indexedPath = params.get('path')
  if (indexedPath) {
    const { status, payload } = await processIndexedDownload(
      sessionId,
      indexedPath,
      context.env,
      (line) => console.log(line),
      Date.now(),
      fetch as never,
    )
    return Response.json(payload, { status })
  }
  const key = params.get('key') ?? ''
  // ... existing R2 body, unchanged
}
```

- [ ] **Step 4: Run tests and typecheck**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run && npm run typecheck:templates
```

Expected: all green; typecheck exit 0.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare/skills/data-portal/template/functions/api/download.ts platform/plugins/cloudflare/skills/data-portal/template/functions/api/_lib/types.ts platform/plugins/cloudflare/mcp/__tests__/portal-indexed-download.test.ts
git commit -m "feat(data-portal): signed-link download for device-held files"
```

---

### Task 8: Install-side fetch route

**Files:**
- Create: `platform/ui/server/routes/portal-fetch.ts`
- Modify: `platform/ui/server/index.ts` (import + `SUBAPP_MANIFEST` entry)
- Test: `platform/ui/server/routes/__tests__/portal-fetch.test.ts`

**Interfaces:**
- Consumes: the signed-link format from Task 2 (`accountId`, `path`, `exp`, `sig`), `resolveExposedDirs` (Task 1).
- Produces: `GET /api/portal/fetch`; exported `verifySignature(secret, accountId, relPath, expiresAtMs, sig, nowMs): Promise<boolean>` for the parity test.

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

```ts
// platform/ui/server/routes/__tests__/portal-fetch.test.ts
import { describe, it, expect, vi } from 'vitest'
import { mkdtempSync, mkdirSync, writeFileSync, rmSync } from 'node:fs'
import { tmpdir } from 'node:os'
import { join } from 'node:path'
import { verifySignature, resolveFetchTarget } from '../portal-fetch'
import { signPath } from '../../../../plugins/cloudflare/skills/data-portal/template/functions/api/_lib/portal-sign.mjs'

const SECRET = 'shared'
const ACC = 'acc-a'
const NOW = 1_800_000_000_000

const SCHEMA = [
  '```allowed-top-level', 'output', 'quotes', 'documents', '```', '',
  '<!-- ontology-buckets:start -->',
  '- `quotes/` - one folder per Quotation record.',
  '<!-- ontology-buckets:end -->',
].join('\n')

function accountDir() {
  const d = mkdtempSync(join(tmpdir(), 'portal-fetch-'))
  writeFileSync(join(d, 'SCHEMA.md'), SCHEMA)
  mkdirSync(join(d, 'quotes/CR2969'), { recursive: true })
  writeFileSync(join(d, 'quotes/CR2969/quote.pdf'), 'PDF')
  mkdirSync(join(d, 'documents'), { recursive: true })
  writeFileSync(join(d, 'documents/private.pdf'), 'SECRET')
  return d
}

describe('portal-fetch signature parity', () => {
  // The two ends deliberately do not share a module. This is the only place a
  // drift between them could hide.
  it('accepts a signature minted by the portal module', async () => {
    const exp = NOW + 300_000
    const sig = await signPath(SECRET, ACC, 'quotes/CR2969/quote.pdf', exp)
    expect(await verifySignature(SECRET, ACC, 'quotes/CR2969/quote.pdf', exp, sig, NOW)).toBe(true)
  })

  it('rejects an expired or tampered signature', async () => {
    const sig = await signPath(SECRET, ACC, 'quotes/CR2969/quote.pdf', NOW - 1)
    expect(await verifySignature(SECRET, ACC, 'quotes/CR2969/quote.pdf', NOW - 1, sig, NOW)).toBe(false)
    const good = await signPath(SECRET, ACC, 'quotes/CR2969/quote.pdf', NOW + 300_000)
    expect(await verifySignature(SECRET, ACC, 'documents/private.pdf', NOW + 300_000, good, NOW)).toBe(false)
  })
})

describe('resolveFetchTarget', () => {
  it('serves a file under an exposed dir', async () => {
    const d = accountDir()
    const r = await resolveFetchTarget(d, 'quotes/CR2969/quote.pdf')
    expect(r.ok).toBe(true)
    rmSync(d, { recursive: true, force: true })
  })

  // The security boundary: a VALID signature over a non-exposed path is still
  // refused, because the install re-derives the exposed set itself.
  it('refuses a non-exposed dir even though the file exists', async () => {
    const d = accountDir()
    const r = await resolveFetchTarget(d, 'documents/private.pdf')
    expect(r.ok).toBe(false)
    expect(r.reason).toBe('not-exposed')
    rmSync(d, { recursive: true, force: true })
  })

  it('refuses traversal', async () => {
    const d = accountDir()
    for (const p of ['../../etc/passwd', 'quotes/../../x', '/etc/passwd']) {
      const r = await resolveFetchTarget(d, p)
      expect(r.ok).toBe(false)
    }
    rmSync(d, { recursive: true, force: true })
  })

  it('refuses everything when the schema is missing', async () => {
    const d = accountDir()
    rmSync(join(d, 'SCHEMA.md'))
    const r = await resolveFetchTarget(d, 'quotes/CR2969/quote.pdf')
    expect(r.ok).toBe(false)
    expect(r.reason).toBe('not-exposed')
    rmSync(d, { recursive: true, force: true })
  })

  it('misses a path that is exposed but absent', async () => {
    const d = accountDir()
    const r = await resolveFetchTarget(d, 'quotes/CR9999/nope.pdf')
    expect(r.ok).toBe(false)
    expect(r.reason).toBe('enoent')
    rmSync(d, { recursive: true, force: true })
  })
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
export PATH="$HOME/.nvm/versions/node/v22.22.0/bin:$PATH"
cd platform/ui && node_modules/.bin/vitest run server/routes/__tests__/portal-fetch.test.ts
```

Expected: FAIL — `../portal-fetch` does not exist.

- [ ] **Step 3a: Write the route**

```ts
// platform/ui/server/routes/portal-fetch.ts
// Serve one device-held file to a data portal, on a signed link.
//
// The portal holds an INDEX of the account's deliverable folders but not the
// bytes (Task 1831). When a client clicks a file, the portal mints a link
// signed with the account's shared secret and the browser lands here.
//
// TWO checks, and neither is redundant:
//   1. the signature proves the request came from the portal;
//   2. the exposed-dir set is RE-DERIVED here from the account's own SCHEMA.md,
//      so a validly-signed link for `documents/private.pdf` is still refused.
// The second is the security boundary. If the portal is ever wrong about what
// it may offer, this is what contains it.

import { Hono } from 'hono'
import { createReadStream } from 'node:fs'
import { stat, readFile } from 'node:fs/promises'
import { join, resolve, sep } from 'node:path'
import { Readable } from 'node:stream'
import type { AppEnv } from './_env'
import { resolveExposedDirs } from '../../../plugins/cloudflare/bin/schema-exposed-dirs.mjs'
import { DATA_ROOT } from '../../app/lib/data-path'
import { detectMimeType } from '../../app/lib/attachments'

const app = new Hono<AppEnv>()
const TAG = '[portal-fetch]'

/** Same algorithm as the portal's `_lib/portal-sign.mjs`. The two ends do not
 *  share a module — no production code in platform/ui imports from
 *  platform/plugins — so a parity test asserts they agree. */
export async function verifySignature(
  secret: string,
  accountId: string,
  relPath: string,
  expiresAtMs: number,
  signature: string,
  nowMs: number,
): Promise<boolean> {
  if (!secret || !signature) return false
  if (!Number.isFinite(expiresAtMs) || expiresAtMs <= nowMs) return false
  const key = await crypto.subtle.importKey(
    'raw',
    new TextEncoder().encode(secret),
    { name: 'HMAC', hash: 'SHA-256' },
    false,
    ['sign'],
  )
  const mac = await crypto.subtle.sign(
    'HMAC',
    key,
    new TextEncoder().encode(`${accountId}\n${relPath}\n${expiresAtMs}`),
  )
  const expected = [...new Uint8Array(mac)].map((b) => b.toString(16).padStart(2, '0')).join('')
  if (expected.length !== signature.length) return false
  let diff = 0
  for (let i = 0; i < expected.length; i++) diff |= expected.charCodeAt(i) ^ signature.charCodeAt(i)
  return diff === 0
}

export type FetchTarget =
  | { ok: true; absolute: string; sizeBytes: number }
  | { ok: false; reason: 'not-exposed' | 'enoent' }

/**
 * Resolve an account-relative path to a servable file, refusing anything not
 * under a currently-exposed dir. Containment is checked on the resolved path,
 * so `..` cannot escape regardless of how it is spelled.
 */
export async function resolveFetchTarget(accountDir: string, relPath: string): Promise<FetchTarget> {
  let schemaText: string | null = null
  try {
    schemaText = await readFile(join(accountDir, 'SCHEMA.md'), 'utf8')
  } catch {
    schemaText = null // fail closed — see resolveExposedDirs
  }
  const { exposed } = resolveExposedDirs(schemaText)
  if (exposed.length === 0) return { ok: false, reason: 'not-exposed' }

  const absolute = resolve(accountDir, relPath)
  const root = resolve(accountDir)
  if (absolute !== root && !absolute.startsWith(root + sep)) return { ok: false, reason: 'not-exposed' }

  const rel = absolute.slice(root.length + 1)
  const top = rel.split(sep)[0]
  if (!exposed.includes(top)) return { ok: false, reason: 'not-exposed' }

  try {
    const st = await stat(absolute)
    if (!st.isFile()) return { ok: false, reason: 'enoent' }
    return { ok: true, absolute, sizeBytes: st.size }
  } catch {
    return { ok: false, reason: 'enoent' }
  }
}

async function handle(c: Parameters<Parameters<typeof app.get>[1]>[0], headOnly: boolean) {
  const accountId = c.req.query('accountId') ?? ''
  const relPath = c.req.query('path') ?? ''
  const exp = Number(c.req.query('exp') ?? '0')
  const sig = c.req.query('sig') ?? ''
  console.log(`${TAG} op=request account=${accountId} path="${relPath}"`)

  const secret = process.env.PORTAL_FETCH_SECRET ?? ''
  if (!(await verifySignature(secret, accountId, relPath, exp, sig, Date.now()))) {
    console.error(`${TAG} op=denied account=${accountId} reason=secret`)
    return c.json({ ok: false, error: 'denied' }, 401)
  }

  const accountDir = resolve(DATA_ROOT, 'accounts', accountId)
  const target = await resolveFetchTarget(accountDir, relPath)
  if (!target.ok) {
    console.error(`${TAG} op=${target.reason === 'enoent' ? 'miss' : 'denied'} account=${accountId} reason=${target.reason}`)
    return c.json({ ok: false, error: 'not found' }, 404)
  }
  if (headOnly) {
    console.log(`${TAG} op=served account=${accountId} bytes=0 head=true`)
    return c.body(null, 200)
  }
  console.log(`${TAG} op=served account=${accountId} bytes=${target.sizeBytes}`)
  return new Response(Readable.toWeb(createReadStream(target.absolute)) as ReadableStream, {
    status: 200,
    headers: {
      'content-type': detectMimeType(target.absolute) ?? 'application/octet-stream',
      'content-disposition': `attachment; filename*=UTF-8''${encodeURIComponent(relPath.split('/').pop() ?? 'file')}`,
      'x-content-type-options': 'nosniff',
      'cache-control': 'private, no-store',
    },
  })
}

app.get('/fetch', (c) => handle(c, false))
app.on('HEAD', '/fetch', (c) => handle(c, true))

export default app
```

- [ ] **Step 3b: Register the subapp**

In `platform/ui/server/index.ts`, add the import beside the other route imports:

```ts
import portalFetchRoutes from './routes/portal-fetch'
```

and the manifest entry inside `SUBAPP_MANIFEST` (after the `/api/calendar` line):

```ts
  { prefix: '/api/portal', file: 'server/routes/portal-fetch.ts', subapp: portalFetchRoutes },
```

- [ ] **Step 4: Run tests**

```bash
cd platform/ui && node_modules/.bin/vitest run server/routes/__tests__/portal-fetch.test.ts server/__tests__/shell-route-registration.test.ts
```

Expected: PASS. If `shell-route-registration.test.ts` asserts an exhaustive manifest list, add `/api/portal` to it.

- [ ] **Step 5: Commit**

```bash
git add platform/ui/server/routes/portal-fetch.ts platform/ui/server/index.ts platform/ui/server/routes/__tests__/portal-fetch.test.ts
git commit -m "feat(data-portal): install route serving device files on a signed link"
```

---

### Task 9: Portal folder navigation

**Files:**
- Modify: `.../data-portal/template/portal.js`
- Modify: `.../data-portal/template/index.html`

No test: the portal front end is a static page with no harness in this repo. It is exercised by hand at deploy time, which the SKILL's done-gate already covers.

- [ ] **Step 1: Add the breadcrumb container to `index.html`**

Insert directly above the existing file-list element:

```html
<nav id="crumbs" class="crumbs" aria-label="Folder path"></nav>
<ul id="entries" class="entries"></ul>
```

- [ ] **Step 2: Add navigation to `portal.js`**

```js
// Folder navigation over the account's published index (Task 1831). `prefix`
// is the folder currently open; '' is the root.
let prefix = ''

function renderCrumbs() {
  const parts = prefix ? prefix.split('/') : []
  const crumbs = document.getElementById('crumbs')
  crumbs.replaceChildren()
  const mk = (label, target) => {
    const a = document.createElement('button')
    a.type = 'button'
    a.className = 'crumb'
    a.textContent = label
    a.addEventListener('click', () => { prefix = target; void loadEntries() })
    return a
  }
  crumbs.append(mk('Files', ''))
  parts.forEach((part, i) => {
    crumbs.append(document.createTextNode(' / '))
    crumbs.append(mk(part, parts.slice(0, i + 1).join('/')))
  })
}

async function loadEntries() {
  const res = await fetch(`/api/files?prefix=${encodeURIComponent(prefix)}`, { credentials: 'same-origin' })
  const data = await res.json()
  renderCrumbs()
  const list = document.getElementById('entries')
  list.replaceChildren()
  for (const entry of data.entries ?? []) {
    const li = document.createElement('li')
    const btn = document.createElement('button')
    btn.type = 'button'
    btn.textContent = entry.kind === 'directory' ? `📁 ${entry.name}` : entry.name
    btn.addEventListener('click', () => {
      if (entry.kind === 'directory') { prefix = entry.relPath; void loadEntries() }
      else void openIndexedFile(entry.relPath)
    })
    li.append(btn)
    list.append(li)
  }
}

// The portal checks the device is reachable before handing over the link, so a
// down device says so plainly instead of failing as a broken download.
async function openIndexedFile(relPath) {
  const res = await fetch(`/api/download?path=${encodeURIComponent(relPath)}`, { credentials: 'same-origin' })
  const data = await res.json()
  if (res.status === 503) {
    showError('That file lives on the device, which is not reachable right now. Try again shortly.')
    return
  }
  if (!res.ok || !data.url) {
    showError('That file could not be opened.')
    return
  }
  window.location.assign(data.url)
}
```

Call `void loadEntries()` wherever the existing upload list is first loaded, so both render on sign-in. If `showError` does not already exist in `portal.js`, reuse whatever the upload flow calls to surface a failure message rather than adding a second mechanism.

- [ ] **Step 3: Verify the template still typechecks**

```bash
cd platform/plugins/cloudflare/mcp && npm run typecheck:templates
```

Expected: exit 0 (the script covers `functions/api`, not `portal.js`; this confirms nothing else broke).

- [ ] **Step 4: Commit**

```bash
git add platform/plugins/cloudflare/skills/data-portal/template/portal.js platform/plugins/cloudflare/skills/data-portal/template/index.html
git commit -m "feat(data-portal): folder navigation in the portal ui"
```

---

### Task 10: Assembly, heartbeat wiring, and docs

**Files:**
- Modify: `.../data-portal/SKILL.md`
- Modify: `.../data-portal/template/wrangler.toml`
- Modify: `platform/plugins/cloudflare/PLUGIN.md`
- Modify: `platform/plugins/scheduling/mcp/src/scripts/check-due-events.ts`
- Modify: `.docs/` and `platform/plugins/docs/references/`
- Test: `platform/plugins/cloudflare/mcp/__tests__/template-config.test.ts` (extend)

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

```ts
// platform/plugins/cloudflare/mcp/__tests__/template-config.test.ts — append
it('declares the two portal-fetch bindings in wrangler.toml', () => {
  const toml = readFileSync(
    resolve(__dirname, '../../skills/data-portal/template/wrangler.toml'),
    'utf8',
  )
  expect(toml).toContain('PORTAL_INSTALL_ORIGIN')
  expect(toml).toContain('__PORTAL_INSTALL_ORIGIN__')
})

it('SKILL.md tells the assembler to set the fetch secret and origin', () => {
  const skill = readFileSync(resolve(__dirname, '../../skills/data-portal/SKILL.md'), 'utf8')
  expect(skill).toContain('PORTAL_FETCH_SECRET')
  expect(skill).toContain('portal-index-push.mjs')
})
```

- [ ] **Step 2: Run test to verify it fails**

```bash
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run __tests__/template-config.test.ts
```

Expected: FAIL on both.

- [ ] **Step 3a: Add the origin placeholder to `wrangler.toml`**

```toml
[vars]
PORTAL_INSTALL_ORIGIN = "__PORTAL_INSTALL_ORIGIN__"
```

`PORTAL_FETCH_SECRET` is deliberately NOT in `wrangler.toml` — it is a secret, set with `wrangler pages secret put`, never committed to the assembled tree.

- [ ] **Step 3b: Document assembly in `SKILL.md`**

Add a section after "Assemble the canonical tree":

````markdown
## Bind the portal to the device

The portal shows the account's deliverable folders, which means it needs two
things the template cannot know: where the install is, and a secret it shares
with it.

Fill `__PORTAL_INSTALL_ORIGIN__` in `wrangler.toml` with the install's public
origin (`https://<tunnel-hostname>`), then set the shared secret on both ends:

```bash
SECRET=$(node -e 'console.log(crypto.randomUUID().replace(/-/g,"")+crypto.randomUUID().replace(/-/g,""))')
# device side — the install reads it from its own environment
echo "PORTAL_FETCH_SECRET=$SECRET" >> <accountDir>/secrets.env
# portal side
npx wrangler pages secret put PORTAL_FETCH_SECRET --project-name <project>
```

The secret never travels. The portal signs one file path with a five-minute
expiry and the install verifies it. The install then re-derives which folders
are exposed from the account's own `SCHEMA.md`, so a signed link for a folder
that is not a deliverable is refused regardless — the signature is not the
security boundary, that check is.

Enrolment now binds each person to the account whose folders they may read:

```bash
node platform/plugins/cloudflare/bin/portal-enrol.mjs --owner <ownerId> \
  --name <name> --account <accountId> | npx wrangler d1 execute <portalDbName> --remote --file=-
```

Anyone enrolled before this existed has no account bound and sees an empty
folder list. Re-run enrolment for them; the upsert makes it a re-run, not a
migration.

## Publish the folder index

The device publishes file metadata — names, sizes, times, never bytes — so the
tree still renders when the device is offline:

```bash
node platform/plugins/cloudflare/bin/portal-index-push.mjs \
  --account-dir <accountDir> --account <accountId> --db <portalDbName> --token <cfToken>
```

The heartbeat runs this hourly. Run it by hand after assembly so the portal has
something to show on the client's first sign-in.
````

- [ ] **Step 3c: Wire the heartbeat**

In `check-due-events.ts`, beside `maybeRunSentSweep`, add the same shape:

```ts
const PORTAL_INDEX_STATE_FILE = resolve(CONFIG_DIR, "portal-index-push-state.json");
const PORTAL_INDEX_INTERVAL_MS = 3_600_000;

/**
 * Publish each portal-serving account's folder index, at most hourly.
 *
 * Spawned, not imported, for the same reason as the sent-copy sweep: this
 * script lives in the cloudflare plugin and reaches Cloudflare's API, which
 * does not belong inside the scheduling build.
 *
 * Only accounts carrying a data-portal.json have a portal, so the absence of
 * that file is the enrolment signal — not a config flag to keep in sync.
 */
function maybeRunPortalIndexPush(platformRoot: string): void {
  let prev: { lastRunMs?: number } | undefined;
  try {
    prev = JSON.parse(readFileSync(PORTAL_INDEX_STATE_FILE, "utf8"));
  } catch {
    prev = undefined;
  }
  const now = Date.now();
  if (prev?.lastRunMs && now - prev.lastRunMs < PORTAL_INDEX_INTERVAL_MS) return;

  const script = resolve(platformRoot, "plugins/cloudflare/bin/portal-index-push.mjs");
  if (!existsSync(script)) {
    console.error(`[portal-index] skipped: ${script} not found`);
    return;
  }
  try {
    writeFileSync(PORTAL_INDEX_STATE_FILE, JSON.stringify({ lastRunMs: now }));
  } catch (err) {
    console.error(`[portal-index] state write failed: ${err instanceof Error ? err.message : String(err)}`);
    return;
  }
  spawn(process.execPath, [script, "--all-accounts"], {
    env: { ...process.env, PLATFORM_ROOT: platformRoot },
    stdio: ["ignore", "inherit", "inherit"],
    detached: true,
  }).unref();
}
```

and call it in the same guarded block as the sent sweep:

```ts
    try {
      maybeRunPortalIndexPush(process.env.PLATFORM_ROOT!);
    } catch (err) {
      console.error(`[portal-index] spawn failed: ${err instanceof Error ? err.message : String(err)}`);
    }
```

Then add `--all-accounts` handling to `portal-index-push.mjs`'s CLI block: enumerate `DATA_ROOT/accounts/*/data-portal.json`, and for each, read `portalDbName` from it and call `pushAccount`. Accounts with no `data-portal.json` are skipped silently — they have no portal.

- [ ] **Step 3d: Documentation**

- `platform/plugins/cloudflare/PLUGIN.md` — describe the index push, the two bindings, and the `[portal-index]` / `[portal-fetch]` log tags.
- `.docs/` — a page describing the class-derived allowlist and why the resolver is the single decision point.
- `platform/plugins/docs/references/` — the client-facing description of what the portal shows. **No task numbers in this file**: the leak gate refuses task citations anywhere under `platform/plugins`, which is Task 1830.

- [ ] **Step 4: Run the whole surface**

```bash
export PATH="$HOME/.nvm/versions/node/v22.22.0/bin:$PATH"
cd platform/plugins/cloudflare/mcp && ../../../node_modules/.bin/vitest run && npm run typecheck:templates
cd ../../../ui && node_modules/.bin/vitest run server/routes/__tests__/portal-fetch.test.ts
cd ../plugins/scheduling/mcp && ../../../node_modules/.bin/tsc --noEmit -p tsconfig.json
```

Expected: all green; typecheck exit 0.

- [ ] **Step 5: Commit**

```bash
git add platform/plugins/cloudflare platform/plugins/scheduling .docs
git commit -m "feat(data-portal): assembly bindings, hourly index push, docs"
```

---

## Self-Review

**Spec coverage.** Resolver → Task 1. Signed links → Task 2. D1 schema and enrolment binding → Task 3. D1 transport → Task 4. Index push and walker → Task 5. Listing → Task 6. Download branch with offline pre-flight → Task 7. Install route with re-derived exposure → Task 8. Portal UI → Task 9. Assembly, heartbeat, docs → Task 10. Every spec section maps to a task.

**Type consistency.** `resolveExposedDirs` returns the same six-field object in Tasks 1, 5 and 8. `signPath`/`verifyPath` parameter order is identical in Tasks 2, 7 and 8. `DirEntry` is defined once in Task 6 and consumed by Task 9. `pushAccount` and `walkExposed` signatures match between Task 5's implementation and its tests.

**Known gaps, deliberate.**
- Task 9 has no automated test. The portal front end has no harness in this repo and adding one is a larger change than the feature.
- The heartbeat spawn in Task 10 copies a mechanism that has never been observed running on a device (Task 1828). The push is runnable standalone so it stays operable regardless, and the SKILL tells the assembler to run it by hand once.
- `cf-exec.d1Query` still spawns `wrangler`. Converging it with the new client is a follow-up, to be filed at land as Task 1834 alongside Task 1812.