Repository Public
1# SP Note (note.spboucher.ai) — code-level audit of the web app + backend23Audited 2026-11-19, read-only. Source of truth: `M4M64c:~/apps/note-spboucher-ai`, branch `main` @ `77ef5f0` (clean tree), package `note-spboucher-ai` 2.0.0.4All paths below are relative to that repo. Line numbers refer to that commit. Source lines are very long (200–1500 chars), so `file:line` often designates a whole function.56Method: the repo (without `.env`, `.git`, `node_modules`, `data/`) was copied to the laptop and read there. Sections 0, 1, 2 (core routes), 10, 11, 12 were read line by line by the lead auditor; sections 3, 8, 9 / 4, 7 / 5, 6 and the route tables of those areas were produced by three sub-auditors reading the same copy (their sections are marked "sub-audit"; spot-checked claims are flagged). Nothing was built, run, restarted or written on the node. Production database: metadata and row COUNTS only.78## 0. Deployment facts that frame every decision910- Stack (package.json): next ^16.4.3, react ^19.3.1, @tiptap/* ^3.32.2, drizzle-orm ^0.45.4, `postgres` ^4 (postgres-js), pg-boss ^12.38, zod ^4.6, @simplewebauthn/server ^14.1, `openai` ^7.47, `@anthropic-ai/sdk` ^1.3, tailwindcss ^4.3 (+ @tailwindcss/postcss), `motion` ^14.9, radix primitives, cmdk, zustand, @tanstack/react-query, react-force-graph-2d, katex, lowlight, jszip, mammoth, pdf-parse, papaparse, turndown, marked, sanitize-html, bibtex-parse-js, pino. TypeScript ^7.0.2. `lint` = `tsc --noEmit` (no ESLint).11- Processes (PM2, verified live): `note-web` (`next start -p 8480`, pid 32840, 7 restarts) and `note-worker` (`tsx src/worker/index.ts`). No `ecosystem.config.*` in the repo.12- Production `.env` variable NAMES: `NODE_ENV PORT APP_URL APP_VERSION DATABASE_URL OPENAI_API_KEY ANTHROPIC_API_KEY DATA_DIR BACKUP_DIR BACKUP_PASSPHRASE BACKUP_OFFSITE BACKUP_KEEP_DAYS LOG_LEVEL TZ`. Read in code but not set in prod: `AUTH_RP_ID`, `AUTH_EXTRA_ORIGINS` (dev only), `APPLE_APP_IDS`, `E2E_BASE_URL`.13- Postgres 17.11 (Homebrew, local, passwordless for the OS user). Extensions present: `vector 0.8.7`, `pg_trgm 1.6`, `unaccent 1.1` (created by `src/db/migrate.ts:6`, not by the SQL migrations). `drizzle.__drizzle_migrations` holds **5 rows** (3 dated 2026-11-01, 1 on 11-17, 1 on 11-18) while `drizzle/meta/_journal.json` lists **3** entries — the first migration was evidently re-applied/re-generated on day one; harmless but the count does not match the folder. NOT investigated further.14- **Production is almost empty** (counts on 2026-11-19): pages 7 (7 live; types `page/notes`, `page/documents`, `research/research`), page_versions 2, attachments 1, tasks 3, tags 0, page_tags 0, databases 0, database_rows 0, references 3, experiments 0, chunks 6, embeddings 6 (model `text-embedding-3-small`), knowledge_edges 8 (all `link/accepted`) vs links 1 (see defect D-12), ai_models 111, ai_providers `openai,anthropic`, ai_conversations 3, ai_executions 0, passkeys 1, sync_tombstones 0. A schema redesign therefore carries almost no data-migration risk.15- Single owner, single workspace by construction: `getWorkspace(userId)` returns the one workspace of the owner (`src/lib/auth/guard.ts:43`), `ownerRow()` = oldest `users` row (`src/lib/auth/sessions.ts:60`). There is no sharing, no roles, no multi-user anything.16- Security headers / CSP in `next.config.ts`: `connect-src 'self'` (the browser can talk only to the app itself — all AI calls are server-side), `frame-src` YouTube/Vimeo only, `script-src 'self' 'unsafe-inline' 'unsafe-eval'`, `worker-src 'self' blob:`, `Permissions-Policy: camera=(), microphone=(self)`. Any new embed (e.g. a remote Mermaid/Excalidraw CDN, a WASM runtime fetched from elsewhere) must be bundled or the CSP changed. `experimental.serverActions.bodySizeLimit = "50mb"`.1718## 1. Schema1920`src/db/schema.ts` (260 lines, 39 tables). Conventions: uuid PKs (`gen_random_uuid()`), timestamptz, soft delete by `deleted_at`, jsonb for flexible content, Drizzle `casing: "snake_case"` (`src/db/index.ts:8`). `vector` custom type is hard-coded `vector(1536)` (`schema.ts:9`).2122### 1.1 Tables (key columns)2324Identity & auth25- `users` — id, username (unique), display_name, email, password_hash (always `'!'` since v2), totp_secret/totp_enabled (dead), preferences jsonb, created_at, updated_at.26- `sessions` — id, user_id→users, token_hash (unique, SHA-256 of the cookie token), user_agent, ip, created_at, last_seen_at, expires_at (idle 30 d sliding), absolute_expires_at (90 d), revoked_at, mfa_passed, credential_id, label (device label), verified_at (step-up freshness), revoked_reason, prev_token_hash + prev_valid_until (60 s rotation grace).27- `login_attempts` — id, ip, username (reused as "kind": `passkey`/`enroll`), success, created_at.28- `webauthn_credentials` — id (text, credential id), user_id, public_key, counter, transports jsonb, device_type, backed_up, label, created_at, last_used_at, aaguid.29- `webauthn_challenges` — id, user_id, challenge, kind (`login|reauth|register|enroll`), created_at, expires_at, session_id, enrollment_id. Single use, bound to an HttpOnly cookie holding `id`.30- `auth_enrollments` — id, purpose (`bootstrap|recovery`), token_hash (unique), revoke_existing, created_at, expires_at, consumed_at, closed_reason.31- `idempotency_keys` — key (PK), session_id, method, path, status, response jsonb, created_at.3233Workspace & pages34- `workspaces` — id, owner_id→users, name, slug (unique), settings jsonb.35- `pages` — id, workspace_id, **parent_id (uuid, NO foreign key, no cycle constraint)**, database_id (set when the page is a database row page; no FK), type (`page|database|research|dashboard`, free text), section (`notes|documents|databases|projects|research`, free text), title, icon, cover, **content jsonb (ProseMirror doc)**, plain_text, `tsv` (generated: `'simple'` config, title weight A + plain_text weight B), properties jsonb, position double, pinned, archived, **version int** (optimistic concurrency), word_count, created_at, updated_at, last_opened_at, deleted_at, **rev bigint** (change feed), inbox_at, capture_kind (`note|tasks|link|photo|file|audio`). Indexes: (workspace_id, inbox_at), (workspace_id, parent_id), GIN tsv, updated_at, database_id, GIN trigram on title, (workspace_id, rev).36- `page_versions` — id, page_id→pages (cascade), version (unique per page; negative time-based numbers for "never applied" copies), title, content, properties, reason, created_at, word_count.3738Databases39- `databases` — id, workspace_id, page_id→pages (host page, cascade), name, description, icon, is_template, template_of, timestamps, deleted_at.40- `database_properties` — id, database_id, name, type (comment lists: `text number date checkbox status select multi_select url file relation rollup formula created_time last_edited_time ai`), config jsonb, position, width.41- `database_rows` — id, database_id, page_id→pages (each row has a page), values jsonb (GIN), position, timestamps, deleted_at.42- `database_views` — id, database_id, name, type (`table board calendar gallery timeline list`), config jsonb, position, is_default.43- `relations` — id, property_id→database_properties, from_row_id, to_row_id (unique triple).4445Attachments, tags, tasks, links46- `attachments` — id, workspace_id, page_id→pages (**ON DELETE SET NULL**), filename, mime, size, sha256, storage_path (content-addressed `aa/bb/<sha256>.<ext>` under `DATA_DIR/uploads`), extracted_text, page_count, meta jsonb, created_at, deleted_at, **rev bigint**.47- `tags` — id, workspace_id, name (unique per workspace), color (default `gray`). `page_tags` — (page_id, tag_id) PK, both cascade.48- `tasks` — id, workspace_id, page_id→pages (set null), title, status (`todo|done` via API), priority (`low|medium|high`), due_at, completed_at, project (free text), timestamps, deleted_at. **No `rev`, no trigger, no tombstone.**49- `inbox_actions` — id, workspace_id, page_id, action, before jsonb, created jsonb, created_at, undone_at (undo journal of inbox triage).50- `links` — id, from_page_id, to_page_id (both cascade), kind (default `link`), unique (from,to,kind).51- `knowledge_edges` — id, workspace_id, from_id, to_id (**no FK to pages**), from_type/to_type (default `page`), kind (comment: `link | backlink | relation | ai_suggested | concept`), status (`suggested|accepted|rejected`), confidence, rationale, model, created_at, decided_at; unique (from_id,to_id,kind). No validity interval, no weight, no source block.52- `sync_tombstones` — (kind, id) PK, workspace_id, rev, deleted_at. kind = `page|attachment`. Written by triggers only.5354Search55- `chunks` — id, workspace_id, source_type (`page|attachment|row`), source_id, page_id, ordinal, **block_id**, heading, content, token_count, content_hash, `tsv` (generated, `'simple'`), unique (source_type, source_id, ordinal).56- `embeddings` — chunk_id (PK→chunks cascade), model, version, embedding `vector(1536)`, HNSW `vector_cosine_ops`.57- `index_state` — (source_type, source_id) PK, content_hash, model, version, indexed_at.5859AI60- `ai_providers` — id (text PK), name, enabled, last_discovery_at, status, status_detail. Rows today: `openai`, `anthropic` (seeded by `src/cli/bootstrap.ts:40`). **No base_url / api-key-reference / kind column.**61- `ai_models` — id (text PK), provider→ai_providers, display_name, category (default `text`), available, enabled, context_window, max_output, input_price, cached_price, output_price, pricing_source, probes jsonb, benchmark jsonb, capabilities jsonb, notes jsonb, discovered_at, probed_at, benchmarked_at. **No creator/vendor/logo column** (provider is the only origin field).62- `ai_conversations` — id, workspace_id, title, page_id, model, routing (default `auto`), pinned, timestamps, deleted_at.63- `ai_messages` — id, conversation_id, role, content, model, provider, `sources` jsonb typed `{ chunkId, pageId, title, excerpt, score }[]`, usage, cost_usd, latency_ms, ttft_ms, meta, created_at.64- `ai_executions` — id, workspace_id, agent, task, model, status (`pending running awaiting_approval approved rejected completed failed`), scopes jsonb, tools_invoked jsonb, documents_accessed jsonb, proposed_changes jsonb, approvals jsonb, outcome, result, error, cost_usd, tokens_in, tokens_out, created/started/finished.65- `ai_usage` — id, workspace_id, provider, model, task, input/output/cached/reasoning tokens, cost_usd, latency_ms, ttft_ms, ok, error, ref_id, created_at.66- `ai_comparisons` — id, workspace_id, prompt, models jsonb, results jsonb.6768Research69- `references` (TS name `researchReferences`) — id, workspace_id, page_id, cite_key (unique per workspace), entry_type, title, authors jsonb, year, journal, doi, url, bibtex, fields jsonb, verified (`user|doi_verified|ai_suggested`), attachment_id→attachments, timestamps, deleted_at.70- `experiments` — id, workspace_id, page_id, name, status (default `planned`), params jsonb, metrics jsonb, dataset_ref, repo_url, notes, started_at, finished_at.71- There is **no table** for papers, research tasks or extracts: see section 7 for where they live.7273Ops74- `audit_logs` — id, user_id, action, target_type, target_id, detail jsonb, ip, created_at.75- `backup_jobs` — id, kind, status, path, size_bytes, sha256, offsite, error, started_at, finished_at.76- `dashboard_layouts` — id, user_id, name, widgets jsonb `{id,type,size,config?}[]`, is_default.77- `settings` — key (PK), value jsonb, updated_at (e.g. `worker.heartbeat`, `ai.daily_budget_usd`).78- pg-boss lives in its own schema `pgboss`.7980### 1.2 Migrations (`drizzle/`, each with a hand-written `.down.sql`)8182- **`0000_init.sql`** (523 lines, 2026-11-01): the 35 original tables — ai_comparisons, ai_conversations, ai_executions, ai_messages, ai_models, ai_providers, ai_usage, attachments, audit_logs, backup_jobs, chunks, dashboard_layouts, database_properties, database_rows, database_views, databases, embeddings, experiments, index_state, knowledge_edges, links, login_attempts, page_tags, page_versions, pages, relations, references, sessions, settings, tags, tasks, users, webauthn_challenges, webauthn_credentials, workspaces — plus all FKs and indexes (GIN tsv, trigram, HNSW).83- **`0001_v2_passkeys_inbox.sql`** (2026-11-17): new tables `auth_enrollments`, `idempotency_keys`, `inbox_actions`; `pages.inbox_at`, `pages.capture_kind`; `sessions.credential_id/label/verified_at/revoked_reason/prev_token_hash/prev_valid_until`; `webauthn_challenges.expires_at/session_id/enrollment_id`; `webauthn_credentials.aaguid`; indexes `idempotency_created_idx`, `inbox_actions_ws_idx`, `pages_inbox_idx`. Data statements: revokes every pre-v2 session (`revoked_reason='legacy-v1'`), deletes all challenges, blanks `users.password_hash`/TOTP.84- **`0002_native_sync.sql`** (2026-11-18): the change feed — see 1.3.8586### 1.3 The `rev` change feed (0002) — exact mechanism8788- One workspace-agnostic sequence `sync_rev_seq` (bigint).89- `rev bigint NOT NULL DEFAULT 0` added to exactly **two tables: `pages` and `attachments`**; back-filled with `nextval`. Indexes `pages_rev_idx (workspace_id, rev)`, `attachments_rev_idx`, `sync_tombstones_rev_idx`.90- Table `sync_tombstones (id, kind, workspace_id, rev, deleted_at)`, PK (kind, id).91- Trigger function `sync_bump_rev()` — `BEFORE INSERT OR UPDATE` on `pages` (arg `'page'`) and `attachments` (arg `'attachment'`), FOR EACH ROW:92 - on UPDATE, returns unchanged when `to_jsonb(NEW) - 'last_opened_at' - 'rev' - 'tsv'` equals the same for OLD (opening a note or a no-op write is not a change; a caller that sets `rev` itself is respected);93 - on INSERT, deletes any tombstone with the same (kind, id) (an id can come back);94 - then `pg_advisory_xact_lock(778201)` and `NEW.rev := nextval('sync_rev_seq')`. The lock makes revisions visible in commit order.95- Trigger function `sync_tombstone()` — `AFTER DELETE` on `pages` and `attachments`: same advisory lock, upserts a tombstone with a fresh `rev`.96- Trigger function `sync_touch_page_tags()` — `AFTER INSERT OR DELETE` on `page_tags`: `UPDATE pages SET rev = nextval(...) WHERE id = pid` (a tag change bumps the page).97- Verified live in production: triggers `pages_sync_rev`, `attachments_sync_rev`, `pages_sync_tombstone`, `attachments_sync_tombstone`, `page_tags_sync_touch`.98- **Not covered by the feed**: tasks, tags as entities (renames/colors), databases / properties / rows / views / relations, references, experiments, knowledge_edges, links, ai_conversations/messages, settings/preferences, dashboard layouts, page_versions. A database row's *page* is in the feed (it is a `pages` row with `database_id`), but its `database_rows.values` is not.99- Caveat: `page_versions` snapshots, `links`/`knowledge_edges` rebuilds and chunk indexing are side effects that do not bump `rev` (correct), but `handleIndex` back-filling block ids (`src/worker/handlers/index-source.ts:6`) does an UPDATE of `content` → bumps `rev` without bumping `version`.100101## 2. API surface102103### 2.0 Cross-cutting rules (apply to every route below unless stated)104105**Gate 1 — `src/proxy.ts`** (Next 16 proxy, Node runtime, matcher = everything except `_next/static|_next/image`). Public paths: `/.well-known/apple-app-site-association`, `/api/health`, `/api/auth/login/(options|verify)`, `/api/auth/enroll/(options|verify)`, `/api/auth/logout`, `/login`, `/enroll`, `/offline`, `/manifest.webmanifest`, `/sw.js`, `/icons/*`, `/_next/*`, `/favicon*`. Everything else requires a valid session resolved from **the cookie only** (`req.cookies.get(SESSION_COOKIE)`, `proxy.ts:31`); `/api/*` without it → `401 {error:"Sign-in required", code:"unauthenticated"}`; pages → 303 to `/login?next=`. `X-Robots-Tag: noindex` everywhere; `/robots.txt` disallows all. Legacy `/gate`, `/unlock`, `/api/gate`, `/api/lock` → 404.106107**Gate 2 — `handler()` in `src/lib/api.ts:21`** wraps nearly every route: `requireUser(req)` → `getWorkspace` → optional `requireFresh` → optional idempotency → error mapping → one pino log line (ids, method, path, status, ms; never bodies). `x-request-id` echoed/generated. Default `cache-control: private, no-store`.108109**How a non-browser client authenticates (native apps)** — there is **no bearer token, no API key, no `Authorization` header anywhere** (grep: zero occurrences). The native apps use exactly the browser mechanism:1101. WebAuthn through AuthenticationServices against the same RP: `/.well-known/apple-app-site-association` returns `{ webcredentials: { apps: ["3YM54G49SN.ai.spboucher.spnotes", "3YM54G49SN.ai.spboucher.spnotes.ios"] } }` (override: `APPLE_APP_IDS`), no `applinks`.1112. `POST /api/auth/login/options` → `{options}` + challenge cookie (`__Host-note_wa`, HttpOnly, SameSite=Strict, 5 min) → `POST /api/auth/login/verify {response, next?}` → `Set-Cookie: __Host-note_session=<256-bit base64url token>; Path=/; HttpOnly; SameSite=Lax; Secure; Max-Age≈90 d`. The client must keep and resend that cookie (dev name: `note_session`). Only the SHA-256 is stored.1123. **Every mutating request (POST/PATCH/PUT/DELETE) must carry `Origin: <APP_URL origin>`** (or `Sec-Fetch-Site: same-origin`): `assertSameOrigin()` (`src/lib/auth/guard.ts:34`) refuses a request with neither header (`403 csrf "Missing Origin header"`), and refuses any other origin. This also applies to the public ceremony routes (`publicRoute`, `src/lib/auth/http.ts:14`) and to logout. A native client therefore forges the Origin header.1134. `User-Agent` matching `^SPNotes/<ver> \((Macintosh|iPhone|iPad)` yields the session label "SP Notes · Mac/iPhone/iPad" (`src/lib/auth/tokens.ts:14`).1145. The token is **rotated at each step-up** (`/api/auth/reauth/verify`); the old one stays valid 60 s. Sessions slide: idle 30 d (refreshed at most every 5 min), hard cap 90 d (`src/lib/auth/config.ts:11-16`).1156. Dev only: `scripts/dev-session.ts` prints a session token for a `*_dev|_test|_scratch` database with a loopback `APP_URL` (no passkey ceremony).116Consequence for the upgrade: there is no scoped/long-lived API credential for extensions, share sheets, CLI, widgets or background refresh; if wanted it must be built.117118**Step-up ("fresh")** — `requireFresh(user)`: passkey verified < 5 min ago (`FRESH_MS`), else `403 {code:"reauth_required"}`. Used by: `PATCH/DELETE /api/auth/passkeys/[id]`, `POST /api/auth/passkeys/options|verify`, `DELETE /api/auth/sessions` (all), `DELETE /api/auth/sessions/[id]` for another device, `DELETE /api/pages/[id]?purge=1`, `DELETE|POST /api/trash`. (Others in the sub-audited areas — export/import/restore — are listed in their tables.)119120**Idempotency** — optional, opt-in by header: a mutating request with `Idempotency-Key: [A-Za-z0-9_-]{8,100}` runs at most once per key; the 2xx JSON response (≤ 512 KB) is stored and replayed with `idempotent-replay: true`; same key on another method/path → `422 idempotency_mismatch`; concurrent duplicate < 60 s → `409 in_progress`; errors and streams are not stored; keys purged after 48 h (`src/lib/api.ts:42-65`). Note: the lookup is by `key` alone (PK), the stored `session_id` is not compared. No route *requires* the header. Separate, built-in idempotency by client-chosen id exists on `POST /api/pages` and `POST /api/attachments` (answer `200` + header `x-existing: 1`).121122**Errors** — `HttpError(status, message, code?, extra?)` → `{error, code?, ...extra}`. Anything else → `500 {error:"Internal error", requestId}`. **A `ZodError` thrown by `uuid.parse(...)` is not an `HttpError`** → 500 (defect D-3). `parseBody` failures → `400 "Validation: …"`.123124### 2.1 Auth & session routes (`src/app/api/auth/**`) — public ones use `publicRoute` (same-origin, rate limit 10 failures/min/IP and 60/min global via `login_attempts`)125126| Method | Path | Purpose / shapes | Requirements |127|---|---|---|---|128| POST | /api/auth/login/options | → `{options}` (usernameless, UV required) + challenge cookie; `409 not_enrolled` if no passkey | public, Origin |129| POST | /api/auth/login/verify | `{response, next?}` → `{ok, next}` + session cookie | public, Origin |130| POST | /api/auth/enroll/options | `{token}` → `{options, purpose, revokeExisting, expiresAt}` | public; needs a CLI-opened enrollment window |131| POST | /api/auth/enroll/verify | `{token, response, label?}` → `{ok, purpose, label}` + session cookie; recovery revokes sessions (+ deletes other passkeys) | public |132| POST | /api/auth/logout | revokes current session, clears cookie, always 200 | Origin |133| GET | /api/auth/state | `{sessionId, fresh, verifiedAt, freshWindowMs, idleDays, absoluteDays, rpId, origins, legacyEnv, enrollmentOpen}` | session |134| POST | /api/auth/reauth/options · /verify | step-up; verify rotates the session token → `{ok, freshForMs}` + new cookie | session |135| GET | /api/auth/passkeys | `[{id, label, createdAt, lastUsedAt, synced, multiDevice, transports, current}]` | session |136| POST | /api/auth/passkeys/options · /verify | add a passkey `{response, label?}` → `{ok, id, label}` | session + fresh |137| PATCH / DELETE | /api/auth/passkeys/[id] | rename `{label}` / revoke (+ its sessions; last passkey → `409 last_passkey`) → `{ok, sessionsRevoked, signedOut}` | session + fresh |138| GET | /api/auth/sessions | `[{id, label, ip, createdAt, lastSeenAt, expiresAt, current}]` | session |139| DELETE | /api/auth/sessions | `{includeCurrent=false}` → `{ok, revoked}` | session + fresh |140| DELETE | /api/auth/sessions/[id] | revoke one → `{ok, current}` | session; fresh if not the current one |141| GET | /api/health | `{ok, db, pgvector, worker, passkeys, time, version}` (worker = heartbeat < 120 s) | public |142| GET | /.well-known/apple-app-site-association | see above | public |143144Passkey device management therefore **exists in full** on the server and in `src/components/settings/SecuritySection.tsx` (list, add, rename, revoke passkeys; list/revoke sessions; revoke all; step-up).145146### 2.2 `/api/sync` — exact contract (`src/app/api/sync/route.ts`, 43 lines)147148`GET /api/sync?since=<rev ≥ 0, default 0>&limit=<1..500, default 200>` — session cookie only; read-only (does not touch `last_opened_at`).149150Response:151```jsonc152{153 "cursor": 123, // send as `since` next time; = head when nothing was cut, else the lowest cut point154 "head": 123, // greatest rev over pages, attachments, tombstones of the workspace at read time155 "more": false, // true → the answer was cut at `limit`; call again immediately156 "reset": false, // true when since > head (database restored from an older backup): client restarts from 0157 "serverTime": "ISO",158 "workspace": { "id": "uuid", "name": "…" },159 "user": { "displayName": "…" }, // absent in the reset answer160 "pages": [{ "id","parentId","databaseId","type","section","title","icon","cover",161 "content": { /* full ProseMirror doc */ }, "properties","position","pinned","archived",162 "version","wordCount","createdAt","updatedAt","lastOpenedAt","deletedAt","inboxAt","captureKind","rev",163 "tags": ["sorted","names"] }], // every state: live, inbox, archived, trashed; NO plainText164 "attachments": [{ "id","pageId","filename","mime","size","sha256","pageCount","createdAt","deletedAt","rev" }], // metadata only; bytes via /api/files/<id>165 "tombstones": [{ "id","kind":"page|attachment","rev","deletedAt" }],166 "tags": [{ "name","color" }] // the WHOLE tag list on every call (not incremental); no id, no count167}168```169Windowing: each list is `rev > since AND rev <= head ORDER BY rev LIMIT limit`; if any list hit the limit, `cursor = min(last rev of each cut list)` and the three lists are trimmed to `rev <= cursor` (nothing skipped). Errors: `400 bad_cursor`. The reset answer has `more: true, cursor: 0` and empty lists.170There is **no push/upload endpoint in `/api/sync`**: writes go through the ordinary routes (`POST /api/pages` with client id, `PATCH /api/pages/[id]` with `baseVersion`, `POST /api/attachments` with client id, `PUT /api/pages/[id]/tags`, …). No websocket/SSE/long-poll for change notification: clients poll.171172### 2.3 Pages (`src/app/api/pages/**`, `src/lib/pages/service.ts`)173174| Method | Path | Request → Response | Notes |175|---|---|---|---|176| GET | /api/pages?view=&limit= | view = `recent` (default; excludes inbox) \| `pinned` \| `trash` \| `archived` \| `section:<name>` (excludes row pages) \| `children:<id>`; limit ≤ 100 (default 20) → array of `{id,title,icon,section,type,parentId,updatedAt,lastOpenedAt,deletedAt,wordCount,pinned,properties,databaseId}` | no content; no pagination cursor; `children:<bad id>` → 500 |177| POST | /api/pages | `{id?: uuid, title?≤500, parentId?, section?: notes\|documents\|databases\|projects\|research, type?: page\|database\|research, content?, icon?≤16, properties?}` → full page row, `201`; if `id` already exists → stored row, `200`, `x-existing: 1` (never overwritten); `409 id_taken` if the id belongs to another workspace; unknown `parentId` → silently set to null | `assertDoc(content)`; block ids added server-side |178| GET | /api/pages/[id] | `?open=1` touches last_opened_at; `?head=1` → `{id,version,updatedAt,deletedAt}`; else full row (incl. `plainText`, `tsv`, `rev`, deleted pages too) + `breadcrumb: [{id,title,icon}]` + `backlinks: [{id,title,icon,section,updatedAt}]` | |179| PATCH | /api/pages/[id] | `{title?, content?, icon?, cover?≤2000, properties?, parentId?, section?: string, position?, pinned?, archived?, type?: string, versionReason?≤100, baseVersion?: int}` → `{id, version, updatedAt, title, wordCount}` | with `baseVersion` and a change to title/content/properties: single-statement compare-and-set on `version`; mismatch → `409 {error, code:"conflict", server:{version,title,content,properties,updatedAt}}`. Without `baseVersion`: last write wins. Trashed page → 404. `section`/`type` unvalidated strings; `parentId` only checked `!== id`. |180| DELETE | /api/pages/[id] | soft delete page + live descendants → `{ok, trashed:n}`; `?purge=1` (fresh) → hard delete | soft delete also deletes chunks |181| POST | /api/pages/[id]?action=restore\|duplicate | restore page + descendants (re-index) / duplicate (title + " (copy)", same parent) → page | |182| POST | /api/pages/[id]/beacon | body = PATCH payload as text (sendBeacon/keepalive) → `{ok, version}`; on conflict the unsaved copy is stored in `page_versions` with a negative version and reason "unsynced copy from a closed tab (not applied)" → `409 {ok:false, code:"conflict", preserved:true}` | |183| POST | /api/pages/[id]/resolve | `{choice: mine\|theirs\|both, serverVersion, mine:{title?, content?}}` → `{id,version,title,content,properties,updatedAt,copyId}`; `both` creates a sibling "… (conflict copy)" | both copies always kept in history |184| GET | /api/pages/[id]/versions | list (≤ 200): `{id,version,title,reason,createdAt,wordCount}`; `?v=<n>` → full snapshot | |185| POST | /api/pages/[id]/versions | `{version}` → restores (current state snapshotted first, reason `restore v<n>`) → `{ok, version}` | no `baseVersion` → overwrites |186| GET / PUT | /api/pages/[id]/tags | → `string[]` / `{tags: string[≤20] (each ≤60)}` → normalized (trim, strip `#`, lower-case, ≤40 chars) set replace → `string[]` | PUT on a trashed page → 404 |187| GET | /api/tree | all live, non-archived, non-row pages (`pageSummary` columns, no content) ordered by position | includes inbox pages (`inboxAt` is returned) |188| DELETE | /api/trash?olderThanDays=N | fresh; purge trashed pages (all when omitted) → `{ok, purged}` | |189| POST | /api/trash | fresh; `{action:"purge-old", days?=30}` → `{ok, purged, remaining, autoPurgeDays}` | not scheduled server-side: purge only happens on request |190191Versioning rules (`updatePage`, `service.ts:43-64`): `version` increments on any change to title/content/properties. A `page_versions` snapshot of the PREVIOUS state is taken when there is none yet, or an explicit `versionReason`, or ≥ 20 versions since the last snapshot, or > 10 min since it; pruned to the 200 most recent. After a content/title change: links + `knowledge_edges(kind=link)` rebuilt from page references in the doc, and an `index-source` job enqueued with `singletonKey page:<id>`, 20 s window, `singletonNextSlot`.192193### 2.4 Attachments & files194195| Method | Path | Request → Response | Notes |196|---|---|---|---|197| POST | /api/attachments | multipart: `file` (≤ 200 MB), `pageId?`, `id?` (client UUID v1–v8) → `201 {id, url:"/api/files/<id>", name, size, mime, hasText, pages}`; existing id → `200` + `x-existing: 1` | blocked mimes: `application/x-msdownload`, `application/x-sh`, `text/html`, `application/javascript`; whole file buffered in memory; text extracted **synchronously** in the request (pdf-parse / mammoth / utf-8 for text-like, `src/lib/files/extract.ts`); unknown pageId → stored with `pageId null` silently; content-addressed storage (dedupe by sha256) |198| GET | /api/attachments?pageId= | ≤ 200 non-deleted: `{id, filename, mime, size, pageId, pageCount, createdAt, hasText, url}` | no pagination |199| DELETE | /api/attachments?id= | soft delete + remove chunks → `{ok}` | id unvalidated; always `{ok:true}` even when nothing matched; file stays on disk forever |200| GET | /api/files/[id] | streams the file; `content-disposition` inline for png/jpeg/gif/webp/svg/pdf/txt/mp4/mp3, else attachment; `cache-control: private, max-age=31536000, immutable`; SVG served with `content-security-policy: script-src 'none'` | **no Range support** (no 206; a whole video/audio/PDF is streamed each time); serves soft-deleted files (D-5); `410` when missing on disk |201202No thumbnail/preview generation, no image resizing, no OCR, no audio transcription, no virus scan. No endpoint to re-attach, rename or restore an attachment.203204### 2.5 Tasks, tags, settings, jobs205206- `GET /api/tasks?done=1` → ≤ 500 tasks `{id,title,status,priority,dueAt,completedAt,project,pageId,createdAt,updatedAt,pageTitle,pageIcon}`, open first, by due date. `POST {title, dueAt? (ISO with offset), priority?: low|medium|high, project?≤100, pageId?}` → 201 row. `PATCH {id, title?, dueAt?, priority?, project?, pageId?, status?: todo|done}` (id in the BODY) → row. `DELETE ?id=` → soft delete. No client-chosen id, no `rev`, no `since` filter, no subtasks, no recurrence, no reminders, no assignee, no link to a block (only to a page). Tasks are a table — they are NOT derived from `taskItem` nodes in documents (the two are unrelated; see section 5/6 sub-audit for the capture "tasks" kind and `components/tasks/parse.ts`).207- `GET /api/tags` → `[{id, name, color, pages}]` (`pages` is always 0 — D-1). No POST/PATCH/DELETE: tags are created implicitly by `PUT /api/pages/[id]/tags`, can never be renamed, recolored, merged or deleted (orphan tags accumulate).208- `GET /api/settings` → `{id, username, displayName, email, preferences, createdAt, workspace:{id,name}, aiBudgetUsd (settings key ai.daily_budget_usd, default 20), sessionId}`. `PATCH {displayName?, email?, preferences?}` (preferences shallow-merged into `users.preferences`, arbitrary keys) → `{displayName,email,preferences}`. `ai.daily_budget_usd` is written by `POST /api/ai/models {action:"budget", usd}` (`src/app/api/ai/models/route.ts:22`), not here.209- `GET /api/settings/audit?limit=≤200` → audit rows of the owner + anonymous ones.210- `GET /api/jobs` → `{worker:{alive,lastBeatAt,version}, queues:[{name, policy:{retryLimit,backoff,expireInSeconds}, created, retry, active, completed, failed}], stuck:[], active:[], failed:[{id,name,at,attempts,error,sourceType,sourceId,executionId,jobId,kind}], deadLetter:n}` (reads `pgboss.job` directly). `POST {action:"retry", name, id}` → `{ok}` (only failed jobs; `409 not_failed`).211212### 2.6 Routes documented in the sub-audited sections213214`/api/ai/*`, `/api/agents*`, `/api/research/*` → section 4/7 tables. `/api/databases*`, `/api/graph*`, `/api/search*`, `/api/capture`, `/api/inbox*`, `/api/dashboard*`, `/api/export`, `/api/import` → section 5/6 tables.215Complete route inventory (72 `route.ts` files under `src/app/api`): agents, agents/[id], ai/action, ai/chat, ai/chat/plan, ai/compare, ai/compare/vote, ai/conversations, ai/models, ai/usage, attachments, auth/* (13), capture, dashboard, dashboard/summary, databases, databases/templates, databases/[id], …/ai, …/apply-template, …/duplicate, …/import, …/properties, …/query, …/rows, …/rows/restore, …/views, export, files/[id], graph, graph/backlinks, graph/neighbors, graph/suggestions, health, import, inbox, inbox/[id], inbox/undo, jobs, pages (6), research/ai, research/experiments(+[id]), research/overview, research/papers, research/references(+[id], [id]/extracts, [id]/links, doi, export.bib, import-bibtex), research/tasks(+[id]), search, search/duplicates, search/index-state, settings, settings/audit, sync, tags, tasks, trash, tree.216App pages (`src/app/(app)`): `/` (dashboard), `/p/[id]`, `/inbox`, `/tasks`, `/search`, `/graph`, `/backlinks`, `/research`, `/ai/agents`, `/ai/compare`, `/ai/models`, `/archive`, `/trash`, `/settings`; outside the shell: `/login`, `/enroll`, `/offline`.217218219---220221# How this file is organised222223| Requested section | Where |224|---|---|225| 0 Deployment facts, 1 Schema, 2 API (cross-cutting rules, auth, sync, pages, attachments/files, tasks, tags, settings, jobs) | first part below (lead auditor) |226| 3 Editor, 8 Offline/PWA, 9 Design system | **Part A** (sub-audit) |227| 4 AI, agents, 7 Research + routes `/api/ai/*`, `/api/agents*`, `/api/research/*` | **Part B** (sub-audit) |228| 5 Knowledge graph, 6 Databases, search, capture/inbox, dashboard, import/export + their routes | **Part C** (sub-audit) |229| 10 Worker/jobs, 11 Tests + scratch environments, 12 Defects (D-1…D-23) | after Part C (lead auditor) |230| 13 Consolidated gap list | end of file |231232Defect numbering: `D-n` (with hyphen) = lead auditor, section 12. Part C has its own list `D1…D24`, `U…` (UI) and import/export items in its section (g) — cited here as `C-D8` etc. Part C's D1/D2 are the same findings as D-1/D-3.233234Verification status:235- Lead-auditor sections: read line by line. Two library behaviours were checked in the INSTALLED packages on the node: Drizzle's unqualified column rendering (D-1) and Tiptap's empty-document fallback (13.6 item 1) — the latter resolves Part A's main `[UNVERIFIED-LIB]` flag. Part A's other `[UNVERIFIED-LIB]` tags (default attrs of stock Tiptap nodes) remain unverified.236- Sub-audits: nothing executed; items they could not verify are listed at the end of each part. Parts of B and C (UI components, `src/lib/import/**`, `src/lib/export/**`) were read by second-level readers and only spot-checked: line numbers there are less certain than elsewhere. Longer raw notes: `/tmp/spnote-next/c-work/{database-ui,other-ui,import-export}.md`.237- Correction to Part A/B/C statements, if any conflict: the lead-auditor sections prevail for auth, sync, pages, attachments, jobs.238- Test counts are `test(` occurrences; no suite was run.239240241---242243# Part A — Editor (§3), Offline/PWA (§8), Design system (§9)244245Source audited: `/tmp/spnote-next/_src` (read-only copy, `package.json` name `note-spboucher-ai` v2.0.0). Nothing was run, built or installed.246All paths are relative to the repo root. `file:N` = line number.247248**Verification legend**249- No tag = read directly in the source.250- **[UNVERIFIED-LIB]** = behaviour/default that lives inside an npm package (Tiptap 3.32, ProseMirror). `node_modules` is absent from the copy, so these are stated from knowledge of Tiptap 3 and must be confirmed against the installed version before relying on them.251252---253254## Section 3 — Editor255256### 3.1 Files257258| File | Lines | Role |259|---|---|---|260| `src/components/editor/Editor.tsx` | 168 | The only editor component (`Editor`), upload helper `uploadFile`, bubble menu, drag handle, link sheet, image zoom sheet |261| `src/components/editor/extensions/nodes.ts` | 52 | Custom nodes `Callout`, `AIBlock`, `FileBlock`, `FootnoteRef`, `Footnote` + extension `BlockId` |262| `src/components/editor/extensions/slash.tsx` | 79 | `slashItems`, `SlashCommand` |263| `src/components/editor/extensions/mention.tsx` | 26 | `PageMention` (configured `@tiptap/extension-mention`) |264| `src/components/editor/extensions/suggestion-ui.tsx` | 50 | `SuggestionList`, `makeRenderer` (ReactRenderer + tippy.js) |265| `src/components/editor/MobileToolbar.tsx` | 78 | Touch formatting bar |266| `src/components/editor/AIInline.tsx` | 168 | AI actions sheet on selection |267| `src/lib/editor/text.ts` | 87 | `PMNode`, `nodeText`, `docText`, `wordCount`, `docBlocks`, `extractPageRefs`, `toMarkdown`, `emptyDoc`, `ensureBlockIds` |268| `src/lib/editor/markdown-client.ts` | 60 | `markdownToDoc` (marked lexer → PM JSON) |269| `src/lib/editor/validate.ts` | 23 | `assertDoc` (server sanity check) |270| `src/components/page/PageView.tsx` | 165 | Page screen: title, editor mount, sync status, conflict UI, version history |271| `src/components/page/PageProperties.tsx` | 18 | Page-level `properties.tags` chips |272| `src/lib/client/page-sync.ts` | 138 | `usePageSync` autosave engine |273| `src/lib/pages/service.ts` | 144 | `createPage`, `updatePage`, `snapshot`, `resolveConflict`, `pruneVersions`, `afterContentChange` |274| `src/app/api/pages/**` | — | `route.ts`, `[id]/route.ts`, `[id]/resolve`, `[id]/versions`, `[id]/beacon`, `[id]/tags` |275276Editor consumers: `PageView.tsx:117` (main), `PageView.tsx:125-126` (conflict compare, read-only ×2), `PageView.tsx:141` (version preview, read-only), `src/components/database/RowPeek.tsx:197` (row notes). `uploadFile` is also imported by `src/lib/client/outbox.ts:4`, `src/components/research/FilesTab.tsx:8`, `src/components/research/ReferenceSheet.tsx:12`.277278There is **no collaborative layer** (no Yjs/Hocuspocus dependency in `package.json`); the document is a single ProseMirror JSON blob in `pages.content` (jsonb, `src/db/schema.ts:83`).279280### 3.2 Schema — extension list exactly as registered281282`Editor.tsx:75-93` (`extensions` memo), in order:2832841. `StarterKit.configure({ codeBlock: false, link: { openOnClick: false, autolink: true, defaultProtocol: "https", HTMLAttributes: { rel: "noopener noreferrer" } }, heading: { levels: [1, 2, 3] } })` — `Editor.tsx:76`2852. `TaskList`, `TaskItem.configure({ nested: true })` (from `@tiptap/extension-list`) — `:77`2863. `Table.configure({ resizable: true, lastColumnResizable: false })`, `TableRow`, `TableHeader`, `TableCell` (from `@tiptap/extension-table`) — `:78`2874. `Image.configure({ inline: false, allowBase64: false })`, `Highlight.configure({ multicolor: false })`, `Subscript`, `Superscript`, `TextAlign.configure({ types: ["heading", "paragraph"] })` — `:79`2885. `Youtube.configure({ nocookie: true, width: 640, height: 360 })` — `:80`2896. `Details.configure({ persist: true, HTMLAttributes: { class: "details" } })`, `DetailsSummary`, `DetailsContent` — `:81`2907. `Placeholder.configure({ placeholder: fn, includeChildren: false })` — heading → "Heading", `detailsSummary` → "Toggle title", else the `placeholder` prop (default `Write, or press “/” for blocks and “@” to link pages…`) — `:82`2918. `CharacterCount`, `Dropcursor.configure({ color: "var(--accent)", width: 2 })`, `Gapcursor`, `TrailingNode` (from `@tiptap/extensions`) — `:83`2929. `CodeBlockLowlight.configure({ lowlight, defaultLanguage: "plaintext" })` with `lowlight = createLowlight(common)` (`:35`) — `:84`29310. `Mathematics.configure({ katexOptions: { throwOnError: false }, inlineOptions: { onClick }, blockOptions: { onClick } })` — click opens `window.prompt("Inline LaTeX" | "Block LaTeX")` then `updateInlineMath` / `updateBlockMath` — `:85`29411. `FileHandler.configure({ allowedMimeTypes: undefined, onDrop, onPaste })` → `insertUploaded` — `:86`29512. `Callout, AIBlock, FileBlock, FootnoteRef, Footnote, BlockId, PageMention` — `:87`29613. `SlashCommand.configure({ ctx: { onAI, onUpload, onNewPage } })` — `:88-92`297298StarterKit nodes/marks: only `codeBlock` is disabled (replaced by `CodeBlockLowlight`, same node name `codeBlock`). Everything else in StarterKit stays enabled. **[UNVERIFIED-LIB]** Tiptap 3 StarterKit also bundles `link`, `underline`, `listKeymap`, `dropcursor`, `gapcursor`, `trailingNode`, `undoRedo`; if so, `Dropcursor`, `Gapcursor` and `TrailingNode` are registered twice (`:76` and `:83`) and Tiptap logs a duplicate-extension warning. `@tiptap/extension-underline`, `-link`, `-placeholder`, `-character-count`, `-bubble-menu`, `-task-item`, `-task-list`, `-table-row/-cell/-header` are listed in `package.json` but **not imported directly** anywhere in `src` (they come through StarterKit / `@tiptap/extensions` / `@tiptap/extension-list` / `@tiptap/extension-table` / `@tiptap/react/menus`).299300`useEditor` options (`Editor.tsx:96`): `content`, `editable`, `immediatelyRender: false`, `autofocus: "end" | false`, `editorProps.attributes` (`class: "ProseMirror"`, `spellcheck`, `aria-label: "Page content"`, `role: "textbox"`, `aria-multiline`), `scrollThreshold`/`scrollMargin` `{ top: 72, bottom: 132 }`, `handleClickOn` (read-only image click → zoom sheet), `handleKeyDown` (⌘/Ctrl+J → AI on selection), `onUpdate → onChange(editor.getJSON())`. **`enableContentCheck` / `onContentError` are absent** (grep over `src`: no match).301302### 3.3 Node types as stored in JSON303304Custom nodes are fully verified from `nodes.ts`. Library node attrs are **[UNVERIFIED-LIB]** except where the repo itself reads/writes them (cited).305306| JSON `type` | Source | Group / content | Attrs (default) | Evidence in repo |307|---|---|---|---|---|308| `doc` | StarterKit | `block+` | — | `text.ts:66` `emptyDoc()` = `{type:"doc",content:[{type:"paragraph"}]}` |309| `paragraph` | StarterKit | block, `inline*` | `textAlign` (TextAlign) **[UNVERIFIED-LIB]**, `id` (BlockId, null) | `Editor.tsx:79`, `nodes.ts:43` |310| `text` | StarterKit | inline | — | — |311| `heading` | StarterKit | block | `level` (1; allowed 1–3), `textAlign`, `id` | `Editor.tsx:76`; `markdown-client.ts:34` clamps `Math.min(3, depth)` |312| `blockquote` | StarterKit | block, `block+` | `id` | — |313| `bulletList` | StarterKit | block | `id` | — |314| `orderedList` | StarterKit | block | `start` (1), `id` | `markdown-client.ts:44`, `export/html.ts:32` |315| `listItem` | StarterKit | — | none (no block id) | `markdown-client.ts:43` |316| `taskList` | `@tiptap/extension-list` | block | `id` | `inbox/service.ts` capture builder |317| `taskItem` | `@tiptap/extension-list` | — | `checked` (false); `nested: true` | `text.ts:50`, `markdown-client.ts:43`, `export/html.ts:33` |318| `codeBlock` | CodeBlockLowlight | block, `text*` | `language` (null; `defaultLanguage: "plaintext"`), `id` | `text.ts:47`, `markdown-client.ts:38` |319| `horizontalRule` | StarterKit | block | none (no block id) | `text.ts:52` |320| `hardBreak` | StarterKit | inline | — | `text.ts:6` |321| `image` | `@tiptap/extension-image` | block (`inline: false`), `allowBase64: false` | `src`, `alt`, `title` (+ `width`/`height` **[UNVERIFIED-LIB]**), `id` | `Editor.tsx:61` inserts `{src, alt, title}` |322| `table` | `@tiptap/extension-table` | block | `id` | — |323| `tableRow` | idem | — | — | `markdown-client.ts:47` |324| `tableHeader` / `tableCell` | idem | `block+` | `colspan` (1), `rowspan` (1), `colwidth` (null) **[UNVERIFIED-LIB]** | `export/html.ts:34` reads `colspan`/`rowspan` |325| `youtube` | `@tiptap/extension-youtube` | block | `src`, `start`, `width`, `height` **[UNVERIFIED-LIB]** | `text.ts:55`, `export/html.ts:38` read `src` |326| `details` | `@tiptap/extension-details` | block | `open` (persisted because `persist: true`), `id` | `slash.tsx:47` inserts `attrs: { open: true }` |327| `detailsSummary` | idem | inline content | — | `slash.tsx:47` |328| `detailsContent` | idem | `block+` | — | `slash.tsx:47` |329| `inlineMath` | `@tiptap/extension-mathematics` | inline atom | `latex` | `slash.tsx:54`, `markdown-client.ts:13,24` |330| `blockMath` | idem | block atom | `latex`, `id` | `slash.tsx:53`, `markdown-client.ts:35,38` |331| `mention` | `@tiptap/extension-mention` (`PageMention`) | inline atom | `id` (page UUID), `label` (page title) | `mention.tsx:14,22`; `text.ts:29` also tolerates `attrs.kind === "user"` (never produced by the client) |332| `callout` | custom `nodes.ts:6-12` | block, `block+`, `defining` | `kind` ("note"; documented values note\|info\|warning\|danger\|ai), `icon` ("💡"), `id` | HTML `div.callout[data-kind][data-icon]`; commands `setCallout`, `toggleCallout` |333| `aiBlock` | custom `nodes.ts:14-19` | block, `block+`, `defining` | `model` (null), `prompt` (null), `at` (null), `id` | HTML `div.ai-block[data-model][data-prompt][data-at]` |334| `fileBlock` | custom `nodes.ts:21-26` | block, `atom`, `draggable` | `src` (null), `name` ("file"), `size` (0), `mime` (""), `attachmentId` (null), `id` | HTML `a.file-block` |335| `footnoteRef` | custom `nodes.ts:28-33` | inline atom | `id` (null) — the footnote number as a string | HTML `sup.footnote-ref[data-id]` |336| `footnote` | custom `nodes.ts:34-39` | block, `inline*` | `id` (null) — footnote number, **not** a block id (`footnote` is not in the BlockId type list) | HTML `div.footnote[data-id]` |337338`text.ts` also tolerates a legacy node name `math` (`text.ts:8`, `:38`; `export/html.ts:20`) — no extension in the editor produces it.339340**Marks** (JSON `marks[].type`): `bold`, `italic`, `strike`, `code`, `underline`, `link` (attrs `href`, plus `target`/`rel`/`class` **[UNVERIFIED-LIB]**; `rel` forced to `noopener noreferrer`), `highlight` (no colour attr, `multicolor: false`), `subscript`, `superscript`. Marks handled by converters: `toMarkdown` handles bold/italic/code/strike/link/highlight only (`text.ts:37`); `docToHtml` handles all nine (`export/html.ts:17`).341342**Absent as schema features:** text colour / font family / font size marks, multi-colour highlight, columns/layout, embed/iframe node other than `youtube`, bookmark/link-preview node, audio/video file node, comment marks, heading levels 4–6.343344**Custom-node HTML round-trip caveat (verified in source, runtime effect not tested):** `FileBlock` attrs (`nodes.ts:23`) and `FootnoteRef`/`Footnote` `id` (`:30`, `:36`) declare no `parseHTML`, while `renderHTML` emits `href` / `data-*`. Stored JSON is unaffected, but an HTML clipboard round-trip (copy/paste, drag) would re-parse those attrs from attributes that are not rendered (`src`, `name`, `id`…) and likely lose them.345346### 3.4 Custom extensions and editor chrome347348- **Mentions / wikilinks** — `mention.tsx`. Trigger `@`, `allowSpaces: true`. Items from `GET /api/search?mode=quick&q=…&limit=8` (`:7`) plus a synthetic `Create page “q”` item (`:9`) which `POST /api/pages {title}` then inserts the mention (`:21`). Inserts `[{type:"mention",attrs:{id,label}},{type:"text",text:" "}]`. Renders `<a class="mention" href="/p/<id>" data-type="mention" data-id data-label>@label</a>`. There is **no `[[` trigger** in the editor; `[[Wiki links]]` exist only as (a) Markdown export syntax for mentions (`text.ts:38`) and (b) Markdown import → mention when the title resolves (`src/lib/import/markdown.ts:58,97`). Backlinks are derived server-side from `mention` nodes and `link` marks whose href matches `/p/<uuid>` (`text.ts:27-31`, `service.ts:100-112`).349- **Math** — `@tiptap/extension-mathematics` + KaTeX (`globals.css:2` imports `katex/dist/katex.min.css`). Editing is by `window.prompt` only (`Editor.tsx:85`, `MobileToolbar.tsx:40`). No inline `$…$` input rule is configured in the repo (**[UNVERIFIED-LIB]** whether the extension ships one). `globals.css:180-181` styles `.Tiptap-mathematics-editor/-render` (class names of the older API — possibly dead CSS, unverified).350- **Callout** — custom, see table. Slash inserts `kind:"note"`; mobile toolbar toggles `kind:"info"` (`MobileToolbar.tsx:40`). **No UI to change `kind` or `icon` after insertion** (absent). CSS variants: `globals.css:171-176` (`info`, `warning`, `danger`, `ai`).351- **Details/toggle** — `@tiptap/extension-details`, `persist: true`.352- **Embeds** — only `youtube` (nocookie, 640×360). The slash item is labelled "Video — YouTube / Vimeo URL" (`slash.tsx:29`) and the CSP allows `player.vimeo.com` frames (`next.config.ts`), but there is **no Vimeo extension**: the handler calls `setYoutubeVideo({ src })` only (`slash.tsx:58`). Generic iframe/embed node: absent.353- **File handler / uploads** — `FileHandler` drop + paste, any MIME. `uploadFile` (`Editor.tsx:40-50`) = `XMLHttpRequest POST /api/attachments` (multipart `file`, optional `pageId`), progress + abort, 413 → "File too large (200 MB max)". Result `Uploaded { id, url, name, size, mime }`. Image MIME → `image` node, otherwise `fileBlock` (`:61`). Upload queue UI with progress/cancel/retry (`:149-152`). Uploads require the network (no offline queue for in-editor uploads).354- **Drag handle** — `@tiptap/extension-drag-handle-react` `DragHandle`, only when `editable && !isTouch` (`Editor.tsx:125-127`), `placement: "left-start"`, CSS `.drag-handle` (`globals.css:185`). No block menu attached to the handle (absent).355- **Bubble menu** — `@tiptap/react/menus` `BubbleMenu`, desktop only; hidden on empty selection, in `codeBlock`, on `image` (`:129`). Buttons: AI, Bold, Italic, Underline, Strike, Highlight, Code, Subscript, Superscript, Link, Heading 2, Bullet list, Quote (`:131-144`).356- **Mobile toolbar** — `MobileToolbar.tsx`, shown when `editable && isTouch && (focused || linkOpen) && !ai` (`Editor.tsx:153`), positioned `bottom: inset` from `useKeyboardInset`; sets `html[data-editing="1"]` (`:17`). Main row: `+` panel toggle, AI (when selection), then contextual: image (View, Delete) / table (+Row, +Col, −Row, −Col, Next, Prev, Header, delete Table, Bold) / code (Copy, Exit block, Text) / default (Bold, Italic, H2, Bullets, Checklist, Outdent/Indent when in list, Link, Quote, Attach); then Undo, Redo, Hide keyboard. Panel "Text": Text, H1, H2, H3, Underline, Strike, Highlight, Inline code. Panel "Insert": Numbered, Quote, Code block, Table 3×3, Callout, Toggle, Divider, Math, Photo (camera capture), Image, File / PDF, Link page (`@`), and "All blocks ( / )".357- **Link editing** — sheet (`Editor.tsx:154-161`), `applyLink` (`:110-117`) prefixes `https://`, refuses `javascript:|vbscript:|data:`.358- **AI inline** — `AIInline.tsx`. Opened by bubble "AI", ⌘J, mobile AI, or slash AI items. Actions (`ACTIONS`, `:26-29`): `rewrite`, `fix`, `summarize`, `expand`, `explain`, `to_list`, `to_tasks`, `table`; plus `translate` (fr, en, es, de, it, pt), `custom` instruction, `continue`, `write`. `AUTO` set (`:32`) also names `shorten`, `formal`, `simplify`, `translate_fr`, `translate_en`, `explain_math`, `action_items` (no UI button for those). Dry-run plan `GET /api/ai/chat/plan?actions=1&chars=N` (`:46`); stream `POST /api/ai/action` via SSE (`:55`) with events `text`/`meta`/`done`/`error` (`confirm_expensive` gate). Result is previewed; apply = one transaction: **Replace** (refused if the selected text changed, `:83`) or **Insert below**; content = `markdownToDoc(out)` wrapped in `aiBlock { model, prompt, at }` (`:80`), except a single-paragraph replace inside one textblock which is inserted inline unwrapped (`:85-86`).359- **Panel → page insertion** — window event `note:insert-markdown` (`PageView.tsx:48`): string → wrapped in `aiBlock { model: "assistant", at }`; `{ markdown, plain: true }` → plain blocks. Emitters: `shell/AIPanel.tsx:20`, `research/LatexSnippets.tsx`, `research/ui.tsx`.360- **Deep link to a block** — `#block-<id>` + optional `?hl=words` (`PageView.tsx:50-54`): scroll to centre + Web Animations flash.361- **CharacterCount** is registered but the word count shown is the server value `page.wordCount` (`PageView.tsx:69`).362- **TextAlign** is registered but **no button sets alignment** anywhere (only the extension's own shortcuts **[UNVERIFIED-LIB]**).363364### 3.5 Slash menu — complete list (`slash.tsx:11-37`, handlers `:38-66`)365366Trigger `/`, `startOfLine: false`, `allowSpaces: false`; filter on title or `keywords`; max 30 (`:68`).367368| Group | Label (hint) | Inserts / does |369|---|---|---|370| Basic | Text (Plain paragraph) | `setParagraph()` |371| Basic | Heading 1 (Section title) | `setHeading({level:1})` |372| Basic | Heading 2 | `setHeading({level:2})` |373| Basic | Heading 3 | `setHeading({level:3})` |374| Basic | Bulleted list | `toggleBulletList()` |375| Basic | Numbered list | `toggleOrderedList()` |376| Basic | To-do list (Checklist) | `toggleTaskList()` |377| Basic | Toggle (Collapsible block) | `details{open:true}` > `detailsSummary["Toggle"]` + `detailsContent[paragraph]` |378| Basic | Quote | `toggleBlockquote()` |379| Basic | Callout (Highlighted note) | `callout{kind:"note",icon:"💡"}` > `paragraph` |380| Basic | Divider | `setHorizontalRule()` |381| Advanced | Code block (Syntax highlighted) | `toggleCodeBlock()` |382| Advanced | Table (3 × 3 with header) | `insertTable({rows:3,cols:3,withHeaderRow:true})` |383| Advanced | Equation (Block LaTeX) | `blockMath{latex:"E = mc^2"}` |384| Advanced | Inline equation | `inlineMath{latex:"x^2"}` |385| Advanced | Footnote | `footnoteRef{id:n}` at cursor + `footnote{id:n}` ("Footnote text") appended at document end; `n` = 1 + count of top-level `footnote` nodes |386| Media | Image (Upload or paste) | file picker `accept="image/*"` → upload → `image` |387| Media | Video (YouTube / Vimeo URL) | `window.prompt` → `setYoutubeVideo({src})` |388| Media | File (Attach PDF, DOCX, …) | file picker `*/*` → upload → `fileBlock` (or `image`) |389| Links | Link to page (Type @ to mention) | inserts the character `@` (opens the mention menu) |390| Links | New sub-page | `POST /api/pages {title:"New page", parentId: pageId, type:"page"}` → inserts a `mention` to it |391| Links | New database (Table / board / calendar) | `POST /api/pages {title:"New database", parentId, type:"database", section:"databases"}` → inserts a `mention` |392| AI | Ask AI to write (Generate content here) | opens `AIInline` mode `write` |393| AI | Continue writing | `AIInline` mode `continue` (auto-runs on full page text) |394| AI | Summarize page | `AIInline` mode `summarize` (auto-runs) |39539625 items. Note: `slash.tsx:12` has `run: undefined` on the first item; execution is via `data: run(id)` (`:68`, `:75`).397398### 3.6 Block ids399400- **Attr name:** `id` (global attribute), rendered as `data-id="<id>"` **and** DOM `id="block-<id>"` (`nodes.ts:43`). `keepOnSplit: false`.401- **Node types that get one (14):** `paragraph, heading, blockquote, codeBlock, bulletList, orderedList, taskList, table, callout, details, aiBlock, blockMath, image, fileBlock` — the list is duplicated in three places that must stay in sync: `nodes.ts:43`, `nodes.ts:45`, `text.ts:69` (`ID_TYPES`).402- **Not covered:** `listItem`, `taskItem`, `tableRow`/cells, `horizontalRule`, `youtube`, `footnote`, `detailsSummary`, `detailsContent`, all inline nodes. So an individual task or list item has **no stable id**.403- **Client side:** `BlockId` ProseMirror plugin `appendTransaction` (`nodes.ts:46-50`): on any `docChanged` transaction, every covered node with a missing **or duplicate** id gets `nanoid(10)`.404- **Server side:** `ensureBlockIds` (`text.ts:76-87`) — same rule (missing, non-string, or duplicate → new id); id = 10 chars from the 64-char alphabet `0-9a-zA-Z_-` using `crypto.getRandomValues` (`rid`, `text.ts:70`). Called in `createPage` (`service.ts:28`) and `updatePage` when `patch.content` is present (`service.ts:49`). A background backfill runs in the index worker, `src/worker/handlers/index-source.ts:6` (`withIds`), writing ids in place **without bumping `version`**.405- **Format:** 10-char URL-safe string on both sides; uniqueness is per document only.406- **Consumers:** `docBlocks` (`text.ts:18-26`) → `chunkDocument` (`src/lib/search/chunk.ts`) stores `blockId` per chunk → search hits link to `/p/<pageId>#block-<blockId>` (`shell/CommandPalette.tsx:40`).407- **Risk noted:** the client compares its pending content with the server copy by `sameJson` (`page-sync.ts:43,96,127`). When the server had to add ids the client did not send (content not produced by the editor), the two differ structurally and an "equal" case can be reported as a conflict. Normal editor saves already carry ids, so this is an edge case.408409### 3.7 Server-side document validation410411**There is NO allow-list validator.** No node-type list, no mark list, no attr schema exists server-side.412413What exists:4144151. **zod at the route:** `content: z.any().optional()` — `src/app/api/pages/route.ts:25` (POST), `src/app/api/pages/[id]/route.ts:16` (PATCH), `[id]/resolve/route.ts:9` (`mine.content`). `title` max 500, `icon` max 16, `cover` max 2000, `properties: z.record(z.string(), z.unknown())`, `baseVersion: int ≥ 0`, `versionReason` max 100. The beacon route does not use zod (`[id]/beacon/route.ts:12-15`, manual key filter + title check).4162. **`assertDoc(doc)`** (`src/lib/editor/validate.ts:7-23`), called by POST, PATCH, resolve, beacon and `src/lib/agents/apply.ts` (2 calls):417 - root must be an object with `type === "doc"` and array `content` → else 400 `bad_document`;418 - limits: `MAX_BYTES = 8 MiB` of `JSON.stringify` (413), `MAX_DEPTH = 60` (400), `MAX_NODES = 200_000` (413);419 - every node: object, `type` a string ≤ 64 chars; `text` (if present) a string; `content` (if present) an array; `marks` (if present) an array of objects with string `type`;420 - URL check: node `attrs.href ?? attrs.src` and mark `attrs.href` must not match `/^\s*(javascript|vbscript|data:text\/html)/i` → 400 "Unsafe URL in document".4213. **Unknown node types / marks / attrs: accepted and stored verbatim** — neither rejected nor stripped. A new block type therefore passes the server today with no change, provided it respects the limits above.4224. Not validated: attr values/shapes, heading level, schema conformance (content expressions), `properties` content, text length per node.423424**Consequences for new block types (important):**425- Server helpers have `default:` fall-through that recurses into `content` (`toMarkdown` `text.ts:61`, `docToHtml` `export/html.ts:44`, `nodeText` `text.ts:10`): an unknown **container** degrades to its children; an unknown **atom** contributes nothing to `plain_text`, word count, Markdown export, print HTML or search chunks. `docBlocks` (`text.ts:22`) only emits chunks for an explicit type list, and `BLOCKS` (`text.ts:12`) decides newline joining.426- New types must be added to the three block-id lists (§3.6) to be linkable/citable.427- **[UNVERIFIED-LIB]** Client-side, Tiptap builds the document with `schema.nodeFromJSON`; with `enableContentCheck` off (it is not set, §3.2) an unknown node type makes content creation fail and Tiptap falls back to an **empty document** with only a console warning. An older client bundle (stale tab, service-worker-cached JS, native app) opening a page that contains a new block type could therefore show it empty, and the next keystroke would autosave that empty doc (the previous state stays recoverable in `page_versions`). This needs confirming against the installed Tiptap and guarding (`enableContentCheck` + `onContentError`, or a schema-version gate) before shipping new node types.428429### 3.8 Autosave, versioning, conflict — end to end430431**Client engine:** `usePageSync(page, hooks)` (`page-sync.ts:34`). States `SyncStatus = "saved" | "saving" | "offline" | "error" | "conflict"` (`:15`).4324331. **Queue.** `PageView` calls `queue({ content })` on every editor update (`PageView.tsx:117`), `queue({ title })` (`:106`), `queue({ icon })`, `queue({ cover })`, `queue({ properties })` (`:111`). `queue` merges into one `pending` patch (`:68`), mirrors it immediately to IndexedDB (`persist` → `putDraft`, `:41`), and arms a **800 ms** debounce timer (`:71`).4342. **Flush.** One request at a time (`inflight`). `PATCH /api/pages/:id` with the pending patch; if the patch contains any of `VERSIONED = ["title","content","properties"]` it adds **`baseVersion: version.current`** (`:55`); timeout 20 s. Payload fields actually sent: any of `title`, `content` (full PM JSON — no diff/steps), `icon`, `cover`, `properties`, plus `baseVersion`. **No `rev`, no `contentText`, no client timestamp** is sent; `plain_text` and `word_count` are computed server-side (`service.ts:49`). PATCH carries no `Idempotency-Key` (only POST gets one by default, `client/api.ts:19-20`).4353. **Success.** Response `{ id, version, updatedAt, title, wordCount }` (`[id]/route.ts:21`); client stores the new version; if nothing new is pending it deletes the draft (`dropDraft`) and shows "Saved"; otherwise re-persists and re-flushes after **400 ms** (`:64`).4364. **Failure handling** (`:58-64`): the patch is merged back into `pending` and persisted, then437 - `409` + `code === "conflict"` + `body.server` → `raiseConflict`;438 - `401` → redirect to login (draft stays on device);439 - status `0` (network/timeout) or `≥ 500` → status `offline` (or `error` for 5xx while online), exponential backoff **3 s → ×2 → max 30 s**, automatic retry;440 - other `4xx` → `blocked`, status `error`, manual Retry only.4415. **Server write** — `updatePage` (`service.ts:43-64`): versioned fields bump `pages.version` by 1 in SQL (`:51`); when `baseVersion` is supplied the check is done twice — an early read (`:53`) and atomically in the `UPDATE … WHERE version = expect` (`:54`); zero rows → conflict. Non-versioned fields (`icon`, `cover`, `parentId`, `section`, `position`, `pinned`, `archived`, `type`, `inboxAt`) do not bump the version and are not concurrency-checked. Callers without `baseVersion` (imports, agents, restore) overwrite but still bump the version.4426. **Conflict response:** **HTTP 409**, body `{ error: "This note was changed in another session", code: "conflict", server: { version, title, content, properties, updatedAt } }` (`service.ts:36,53,55`; `HttpError.extra` is spread into the JSON by `jsonError`, `src/lib/auth/guard.ts:50`).4437. **Client conflict handling** (`raiseConflict`, `:44-48`): if the server copy already equals the pending patch (`sameAsServer`, `:43` — the client's own keepalive save won the race) it silently adopts the server version; otherwise `blocked = true`, status `conflict`, and edits keep accumulating locally. UI (`PageView.tsx:87-95`, compare dialog `:122-130`): **Compare** (two read-only editors side by side), **Keep mine**, **Keep theirs**, **Keep both**. There is **no merge** (no 3-way merge, no OT/CRDT).4448. **Resolve:** `POST /api/pages/:id/resolve { choice, serverVersion, mine: { title?, content? } }` → `resolveConflict` (`service.ts:78-94`):445 - `mine` → snapshot server copy (reason `conflict · other session's copy`) then `updatePage` with `baseVersion = cur.version`;446 - `theirs` → local copy stored as a **negative-version** history row (`unappliedVersion() = -floor(now/1000)`, reason `conflict · this device's copy (not applied)`), page unchanged;447 - `both` → local copy becomes a new sibling page `"<title> (conflict copy)"`, page unchanged; returns `copyId`.448 - If the page moved again (`cur.version !== serverVersion`) → a fresh 409 with the new server copy (`:80`), surfaced as "The note changed again — review the new version." (`page-sync.ts:86`).4499. **Last-chance save** (`lastChance`, `page-sync.ts:113-119`), on `pagehide` and `visibilitychange → hidden`: (a) synchronous `localStorage["note-draft-<pageId>"]` write; (b) IndexedDB persist; (c) if online and body ≤ `BEACON_MAX = 60_000` chars, `fetch POST /api/pages/:id/beacon` with `keepalive: true`, same body + `baseVersion`. Server (`[id]/beacon/route.ts`): only `title, content, icon, cover, properties` accepted; on conflict it never overwrites — identical content → `{ ok: true, duplicate: true }`; otherwise the unsaved copy is stored as a negative-version snapshot (`unsynced copy from a closed tab (not applied)`) and it answers 409 `{ ok:false, code:"conflict", preserved:true }`.45010. **Remote-change detection** (`check`, `:120-131`), on `visibilitychange → visible` and window `focus`: `GET /api/pages/:id?head=1` (8 s timeout) → `{ id, version, updatedAt, deletedAt }`; if the version differs it fetches the full page; with nothing pending it applies the remote copy (`editor.commands.setContent(c.content, { emitUpdate: false })`, `PageView.tsx:33`) and dispatches `note:remote-update` (toast "Updated"); with pending edits it raises a conflict unless equal. **There is no push channel** (no WebSocket/SSE for page changes) and **no polling timer** — a second open tab only notices on focus/visibility.45111. **Draft recovery on mount** (`:92-105`): newest of the IndexedDB draft and the localStorage draft; identical to server → dropped; `baseVersion === page.version` → applied to the editor, toast "Unsaved changes restored", flushed; otherwise → conflict UI.45212. **Other hooks:** `online` event → immediate flush; unmount → flush (`:134`). Before archive / trash / restore-version, `PageView` awaits `sync.flush()` (`:56,80,82`).453454**`page_versions` snapshots** (`src/db/schema.ts:94-98`: `id, page_id, version, title, content, properties, reason, created_at, word_count`; unique `(page_id, version)`):455- Cadence (`service.ts:57-61`), evaluated on every save that changes `content` or `title`: snapshot the **previous** state when there is no positive-version snapshot yet, **or** an explicit `versionReason` is given, **or** `prev.version − lastSnapshot.version ≥ 20`, **or** more than **10 minutes** since the last snapshot. Reason defaults to `autosave`.456- Retention: `pruneVersions` keeps the **200** most recent rows per page by `created_at` (`:95-98`).457- Snapshots are idempotent per version (`onConflictDoNothing`, `:69`).458- List: `GET /api/pages/:id/versions` (≤ 200, newest first: `id, version, title, reason, createdAt, wordCount`); one: `GET …/versions?v=<n>`.459- **Restore:** `POST /api/pages/:id/versions { version }` → `updatePage(title, content, properties)` with `versionReason: "restore v<n>"` and **no `baseVersion`** (so the current state is snapshotted first, the version is bumped, and no conflict check is made). Client: `restore()` flushes, posts, then `window.location.reload()` (`PageView.tsx:56`). Negative "Unapplied copy" entries are restorable the same way.460- No diff view between versions (preview is a read-only editor only): absent.461462**After every content/title save** (`afterContentChange`, `service.ts:100-112`): rebuild `links` rows of kind `link`, upsert/delete `knowledge_edges` of kind `link`, enqueue one index job per page per 20 s (`singletonKey: page:<id>`).463464`pages.rev` (`schema.ts`, bigint, maintained by DB trigger `sync_bump_rev` per the column comment) exists for native-client pull sync; the web autosave does not read or send it.465466### 3.9 Markdown conversion and text extraction467468**PM JSON → Markdown** — `toMarkdown` (`text.ts:34-65`), pure, shared by server and client. Used by `src/lib/export/markdown.ts:52` (`pageMarkdown`, adds YAML front-matter + `# title`), `src/lib/ai/chat.ts`, `src/lib/agents/runner.ts`, `src/lib/agents/tools.ts`, `src/lib/research/ai.ts`.469- Inline: `**bold**`, `*italic*`, `` `code` ``, `~~strike~~`, `[t](href)`, `==highlight==`; `hardBreak` → two spaces + newline; `mention` → `[[label]]`; `inlineMath`/`math` → `$latex$`; `footnoteRef` → `[^id]`.470- Blocks: paragraph, `#`×level heading, `> ` blockquote, fenced `codeBlock` with language, `- ` / `1. ` / `- [x] ` lists (2-space nesting), GFM table (first row as header), `---`, ``, `fileBlock` → `[📎 name](src)`, `youtube` → bare URL, `blockMath` → `$$\n…\n$$`, `callout` → `> [!KIND] icon` + quoted body, `details` → raw `<details><summary>…</summary>`, `aiBlock` → `> [!AI] model` + quoted body, `footnote` → `[^id]: text`; unknown → children.471- **Lossy:** `underline`, `subscript`, `superscript` marks; `textAlign`; block ids; table `colspan`/`rowspan`/`colwidth`; `aiBlock.prompt`/`at`; `details.open`; image `title`.472473**Markdown → PM JSON** — `markdownToDoc(md): PMNode[]` (`markdown-client.ts:56-60`), `marked.lexer(…, { gfm: true })`. Used client-side (`AIInline.tsx:78`, `PageView.tsx:48`, `chat/Chat.tsx`, `ai/compare/ComparePanel.tsx`, `dashboard/widgets.tsx`, `research/ui.tsx`) **and server-side** (`src/lib/import/markdown.ts:68,136`, `src/lib/agents/apply.ts`). There is one implementation for both sides.474- Produces: `heading` (level clamped to 3), `paragraph`, `blockquote`, `codeBlock{language}`, `horizontalRule`, `bulletList`/`orderedList{start}`/`taskList` with `listItem`/`taskItem{checked}`, `table`/`tableRow`/`tableHeader`/`tableCell`, `hardBreak`, `blockMath` (a paragraph that is exactly `$$…$$`, or a ```` ```math ```` fence), `inlineMath` (`$…$` in text, or a code span that is `$…$`); marks `bold`, `italic`, `strike`, `code`, `link{href}`.475- **Not produced** (so `toMarkdown → markdownToDoc` does not round-trip): `callout`, `aiBlock`, `details`, `footnote`/`footnoteRef`, `fileBlock`, `youtube`, `highlight`, `mention`, `image` (inline images become the text `[image: …]`, `:18`); raw HTML becomes literal text (`:17,50`).476- Server import layer `mdToDoc` (`src/lib/import/markdown.ts:63-120`) adds: `` → `image` nodes backed by attachments (paragraphs split around them), `[[Wiki|alias]]` → `mention` when the target resolves else plain text, relative links → `/p/<id>` or `/api/files/<id>`, YAML front-matter and Notion-style header parsing (`parseFrontMatter`, `parseNotionHeader`).477- DOCX import: mammoth → HTML → `turndown` → Markdown → the same path (`src/lib/import/docx.ts`).478479**PM JSON → HTML** (print / "PDF") — `docToHtml` (`src/lib/export/html.ts:14-49`), KaTeX rendered server-side; `GET /api/export?format=pdf&pageId=` returns print-ready HTML (browser print), not a generated PDF file.480481**Chat/AI Markdown rendering** — `src/components/markdown.tsx`: `react-markdown` + `remark-gfm` + `remark-math` + `rehype-katex`.482483**Text extraction** (`text.ts`):484- `nodeText` (`:4-11`): text; `hardBreak` → `\n`; `mention` → `@label`; math → latex; children joined with `\n` when any child is in `BLOCKS` (`:12`), else `""`.485- `docText` (`:14`): `nodeText(doc)`, collapses 3+ newlines, trims → stored in `pages.plain_text` (feeds the generated `tsv` column, `schema.ts:85`).486- `wordCount` (`:15`): Unicode letters/digits regex with inner `' ’ -`.487- `docBlocks` (`:18-26`): flat list `{ id, type, heading, text }`; emits headings and non-empty `paragraph, blockquote, codeBlock, bulletList, orderedList, taskList, table, callout, details, aiBlock, blockMath` as whole units (does not descend into them); `image`, `fileBlock`, `youtube`, `footnote`, `horizontalRule` produce no block.488- `extractPageRefs` (`:27-31`): page ids from `mention` nodes (unless `attrs.kind === "user"`) and `link` hrefs `/p/<uuid>`.489490---491492## Section 8 — Offline / PWA493494### 8.1 Service worker (`public/sw.js`, 34 lines)495496- **Registration:** `src/components/shell/Shell.tsx:41-52` — `navigator.serviceWorker.register("/sw.js")`, **production only** (`process.env.NODE_ENV !== "production"` → skip), inside the authenticated shell (not on `/login`, `/enroll`, `/offline`). Default **scope `/`** (script at the origin root, no `scope` option). `reg.update()` on each `visibilitychange → visible`.497- **Update flow:** a new worker waits; UI banner "A new version is ready." (`Shell.tsx:71`) → `postMessage({ type: "SKIP_WAITING" })` → `controllerchange` → reload only if `window.__noteUpdateAccepted` (`:51`). No `skipWaiting()` at install; `clients.claim()` at activate (`sw.js:21`).498- **Cache name:** a single cache, `VERSION = "note-static-v3"` (`sw.js:9`). `activate` deletes every other cache (`:21`). (`wipeLocalData` also deletes caches prefixed `note-rt-`, `offline.ts:49` — no code creates such a cache: legacy.)499- **Precache** (`SHELL`, `sw.js:10`): `/offline`, `/manifest.webmanifest`, `/icons/icon-192.png`, `/icons/icon-512.png`, `/icons/icon.svg`, `/icons/apple-touch-icon.png` (fetched with `cache: "reload"`), plus every `/_next/static/…` URL found by regex in the `/offline` HTML (best effort, `:18`). `/icons/icon-maskable-512.png` is not precached.500501| Request | Strategy | Evidence |502|---|---|---|503| non-GET, cross-origin | not handled (network) | `sw.js:24-25` |504| `/_next/static/*`, `/icons/*` | **cache-first**, runtime-cached into `note-static-v3` on `res.ok && res.type === "basic"` | `sw.js:11,26-28` |505| navigations (`req.mode === "navigate"`) | **network-only**; on failure → cached `/offline` page, else `503 "Offline"` text | `sw.js:30-31` |506| everything else (API, RSC payloads, `/api/files/*`, manifest at runtime) | untouched, straight to network | `sw.js:33` |507508- **Never cached:** API responses, page HTML, RSC payloads, uploaded files (stated in the header comment `sw.js:3-6` and consistent with the code). API responses are also sent `cache-control: private, no-store` (`src/lib/api.ts:36`).509- **Background Sync / Periodic Sync / Push:** **absent** (no `sync`, `periodicsync` or `push` listener in `sw.js`; no `SyncManager`/`registration.sync` anywhere in `src`). Replay is driven by page events only.510511### 8.2 Manifest (`public/manifest.webmanifest`)512513`id: "/"`, `name: "Note SPBoucher AI"`, `short_name: "Note"`, `start_url: "/"`, `scope: "/"`, `display: "standalone"`, `display_override: ["standalone","minimal-ui"]`, `orientation: "any"`, `background_color` and `theme_color` `#12141c`, `lang: "en"`, `categories: ["productivity"]`. Icons: `icon.svg` (any), `icon-192.png`, `icon-512.png`, `icon-maskable-512.png` (maskable). Shortcuts: "Quick capture" `/?capture=1`, "Search" `/search`, "Inbox" `/inbox`. `/?capture=1` and `/?ai=1` are handled in `Shell.tsx:54`. **Absent:** `share_target`, `file_handlers`, `protocol_handlers`, `screenshots`.514Linked from `src/app/layout.tsx:9`; `appleWebApp { capable: true, statusBarStyle: "black-translucent", title: "Note" }` (`:10`).515516### 8.3 Device storage — exactly what is stored517518**IndexedDB — yes, used.** Database `note-local`, version `1` (`src/lib/client/offline.ts:11,19-20`), two object stores:519520| Store | keyPath | Shape | Written by |521|---|---|---|---|522| `drafts` | `pageId` | `Draft { pageId: string; baseVersion: number; patch: Record<string, unknown>; updatedAt: number }` — `patch` is the pending PATCH body (may hold the full `content` JSON, `title`, `icon`, `cover`, `properties`) | `page-sync.ts:41` (`putDraft`), deleted on confirmed save |523| `outbox` | `id` | `OutboxItem { id: string; kind: "capture"; body: { kind: string; text?: string; url?: string }; files?: File[]; uploadedIds?: string[]; createdAt: number; attempts: number; lastError?: string }` — `id` doubles as the `Idempotency-Key`; `File` objects are stored structurally | `outbox.ts:14` (`enqueueCapture`) |524525- No indexes. Retention 30 days (`RETENTION_MS`, purge at shell start: `purgeExpired`, `Shell.tsx:56`). Not encrypted (rationale in the header comment `offline.ts:7-9`). Erased by `wipeLocalData` (`offline.ts:45-51`) on "Lock this device" (`Sidebar.tsx:37`, `MobileNav.tsx:94`).526- **Not stored in IndexedDB:** pages, the page tree, attachments, search index, databases, settings, tasks. There is **no local copy of any note** that was not being edited.527- The header comment (`offline.ts:5`) mentions "page saves" in the outbox, but the type only allows `kind: "capture"` — page saves live in `drafts`, not `outbox`.528529**localStorage keys**530531| Key | Content | Source |532|---|---|---|533| `note-theme` | `"system" \| "light" \| "dark"` | `store.ts:10`, read by the inline script `layout.tsx:17` |534| `note-ui` (zustand persist) | `{ state: { sidebarOpen, aiWidth, theme }, version }` | `store.ts:11` |535| `note-prefs` | `Prefs { density?, editorFont?, defaultSection?, aiDefaultTask?, aiAlwaysCite? }` | `settings/prefs.ts:4-9` |536| `note-draft-<pageId>` | synchronous copy of a `Draft` (written only on tab hide/unload) | `page-sync.ts:28-31,114` |537| `note-draft-capture` | `{ text, kind }` of the open capture sheet | `CaptureSheet.tsx:15,31-32,65` |538| `note-tree-open` | `Record<id \| "sec:<section>", boolean>` | `Sidebar.tsx:23,25` |539| `db-view-<dbId>` | last view id of a database | `database/DatabaseView.tsx:45,48` |540| new-row draft (`newRowDraftKey(dbId)`) | database new-row draft | `database/NewRowSheet.tsx:20-30` |541| `research-panel-open`, `research-tab-<pageId>` | research panel UI state | `research/ResearchPanel.tsx:29,47,60` |542| search filters (`STORE`) | search page filters | `search/SearchPage.tsx:43-44` |543544`wipeLocalData` removes keys prefixed `note-pending-`, `note-draft-`, `note-search`, and `note-recent-searches` (`offline.ts:47`) — so `note-draft-capture` is wiped, but `note-ui`, `note-prefs`, `note-tree-open`, `db-view-*`, `research-*` are kept. `note-pending-*` has no writer (legacy).545546**sessionStorage:** `note-offline-from` (`auth/OfflineScreen.tsx:6`), `draft:<key>` (`research/ui.tsx:72-74`), search scroll (`search/SearchPage.tsx:56`); all cleared by `wipeLocalData` (`offline.ts:48`).547548**Cache Storage:** only `note-static-v3` (§8.1). React Query cache is in memory only (`providers.tsx:5`: `staleTime 15_000`, `refetchOnWindowFocus: false`, `retry: 1`; no persister).549550### 8.4 What works offline, and what does not551552| Capability | Status | Detail |553|---|---|---|554| Open the app cold while offline | **No** | Navigation is network-only → `/offline` screen (`sw.js:30-31`). The screen says so: "Your notes are not stored on this device, so they cannot be shown without a connection." (`OfflineScreen.tsx:12`); it reloads to the previous URL when `online` fires. |555| Read a note not already on screen | **No** | No local page store; client-side route changes need RSC/API from the network. |556| Keep editing the page **already open** when the network drops | **Yes (partial)** | Edits accumulate in memory + `drafts` store; status "Offline · kept on this device" + Retry; auto-retry with backoff and on `online`. Survives tab close/crash and is replayed on the next online visit to that page (`page-sync.ts:92-105`). The page must be reopened online for the replay — there is no global draft replayer. |557| Create a new page offline | **No** | `POST /api/pages` is a direct `api()` call everywhere (`Shell.tsx:31`, `Sidebar.tsx:27`, `TopBar.tsx:30`, `CommandPalette.tsx:29,37`, `slash.tsx`/`Editor.tsx:91`, `mention.tsx:21`); no queue. (The server does support client-chosen UUIDs for native clients, `api/pages/route.ts:25-29`, but the web client never sends `id`.) |558| Quick capture offline (text / to-do / link / photo / file / voice memo) | **Yes** | `enqueueCapture` writes to `outbox` first, then sends; replay is idempotent (`Idempotency-Key = item.id`, uploaded attachment ids recorded so files are not re-uploaded) (`outbox.ts`). Works only if the shell is already loaded. "Capture and open" is disabled offline (`CaptureSheet.tsx:89`). Without IndexedDB (private mode) it sends directly and reports failure. |559| Sidebar quick-capture field | **No** | `Sidebar.tsx:32` posts `/api/capture` directly, bypassing the outbox. |560| Upload a file/image in the editor offline | **No** | `uploadFile` fails with "Network lost during upload"; manual Retry in the upload list (`Editor.tsx:46,149-152`). |561| Search, AI, databases, tasks, graph, settings offline | **No** | Network only. AI sheet shows "You are offline: nothing can be sent." (`AIInline.tsx:107`). |562| Delete / move / pin / tag offline | **No** | Direct API calls, no queue. |563| Queue replay trigger | Page events only | `flushOutbox()` at shell mount and on `online`, `visibilitychange → visible`, `pageshow` (`Shell.tsx:56`); also from `SyncPill` (`MobileNav.tsx:22`). Oldest first, stops at the first non-fatal failure; a 4xx other than 401/409/429 is "fatal" and is skipped (`outbox.ts:29,38`). |564| Background sync when the app is closed | **No** | No Background Sync API (§8.1). |565| Offline indicators | Yes | `useOnline` (`hooks.ts:6`); `SyncPill` in the mobile header only ("Offline", "Offline · N kept", "N to sync") (`MobileNav.tsx:20-25`); page status pill (`PageView.tsx:64-68`). No desktop-wide offline indicator outside the page status. |566567`src/lib/client/api.ts`: `api()` (JSON fetch, auto `Idempotency-Key` on POST, `x-request-id`, optional timeout, `ApiError { status, code, body }`, status 0 = network, 401 → `toLogin()`), `sse()` (POST + `data:` lines, `[DONE]`), `newKey()`.568569---570571## Section 9 — Design system572573### 9.1 Stack574575- **Tailwind CSS v4** via `@tailwindcss/postcss` (`postcss.config.mjs`: `{ plugins: { "@tailwindcss/postcss": {} } }`). **No `tailwind.config.*` file** — configuration is CSS-first in `src/app/globals.css` (`@import "tailwindcss"`, `@theme { … }`, `@custom-variant dark (&:where(.dark, .dark *))`).576- `class-variance-authority` (only in `ui/button.tsx`), `clsx` + `tailwind-merge` (`cn`, `src/lib/utils.ts:3`), Radix primitives, `cmdk`, `tippy.js` (suggestion popups), `lucide-react`.577- Design name in the CSS comment: "Graphite & Ultramarine" (`globals.css:42`).578579### 9.2 Tokens (`src/app/globals.css`)580581**Colours** — raw custom properties on `:root` (light, `:43-66`) and `.dark` (`:68-91`), aliased to Tailwind colour utilities through `--color-*` in `@theme` (`:11-28`), e.g. `bg-bg`, `text-fg-muted`, `border-line`, `bg-accent-soft`, `text-ai`.582583| Token | Light (`:root`) | Dark (`.dark`) |584|---|---|---|585| `--bg` | `oklch(98.7% 0.003 255)` | `oklch(17.2% 0.013 268)` |586| `--bg-elev` | `oklch(100% 0 0)` | `oklch(21% 0.015 268)` |587| `--bg-sunk` | `oklch(96.3% 0.005 255)` | `oklch(14.6% 0.012 268)` |588| `--bg-side` | `oklch(97.2% 0.005 255)` | `oklch(15.6% 0.013 268)` |589| `--bg-hover` | `oklch(94.6% 0.007 258)` | `oklch(24.5% 0.016 268)` |590| `--fg` | `oklch(21.5% 0.018 265)` | `oklch(94% 0.006 255)` |591| `--fg-muted` | `oklch(46% 0.018 262)` | `oklch(72% 0.014 260)` |592| `--fg-faint` | `oklch(63% 0.014 260)` | `oklch(54% 0.014 262)` |593| `--line` | `oklch(91.8% 0.006 258)` | `oklch(26.5% 0.015 268)` |594| `--line-strong` | `oklch(85.5% 0.009 258)` | `oklch(33.5% 0.018 268)` |595| `--accent` | `oklch(52.5% 0.2 265)` | `oklch(73% 0.14 265)` |596| `--accent-fg` | `oklch(99% 0.003 265)` | `oklch(17% 0.03 265)` |597| `--accent-soft` | `oklch(95.4% 0.032 265)` | `oklch(29% 0.065 265)` |598| `--danger` | `oklch(55% 0.2 25)` | `oklch(69% 0.17 25)` |599| `--success` | `oklch(56% 0.14 155)` | `oklch(73% 0.13 155)` |600| `--warning` | `oklch(70% 0.15 70)` | `oklch(80% 0.14 75)` |601| `--ai` | `oklch(53% 0.21 298)` | `oklch(75% 0.14 298)` |602| `--ai-soft` | `oklch(95.5% 0.035 298)` | `oklch(29% 0.07 298)` |603| `--ring` | `oklch(52.5% 0.2 265 / 0.35)` | `oklch(73% 0.14 265 / 0.4)` |604| `color-scheme` | `light` | `dark` |605606`--ring` has no `--color-ring` alias (used directly in the focus-visible rule, `:100`).607608**Shadows**609610| Token | Light | Dark |611|---|---|---|612| `--shadow-card` | `0 1px 2px oklch(25% 0.03 265 / 0.05), 0 0 0 1px oklch(25% 0.03 265 / 0.02)` | `0 1px 2px oklch(0% 0 0 / 0.35), 0 0 0 1px oklch(100% 0 0 / 0.02)` |613| `--shadow-pop` | `0 16px 40px -12px oklch(25% 0.04 265 / 0.22), 0 3px 8px -2px oklch(25% 0.04 265 / 0.08)` | `0 18px 44px -14px oklch(0% 0 0 / 0.75), 0 0 0 1px oklch(100% 0 0 / 0.05)` |614615Utilities `shadow-card`, `shadow-pop` (`:29-30`).616617**Type scale** (`@theme`, `:31-38`; comment "1.2 minor third, 16 px base for reading, 13 px for chrome")618619| Utility | Size | Line height |620|---|---|---|621| `text-micro` | 0.6875rem (11 px) | 1rem |622| `text-ui` | 0.8125rem (13 px) | 1.25rem |623| `text-body` | 1rem | 1.75rem |624| `text-h3` | 1.25rem | 1.75rem |625| `text-h2` | 1.5rem | 2rem |626| `text-h1` | 1.875rem | 2.375rem |627| `text-display` | 2.5rem | 3rem |628629In practice most chrome uses arbitrary sizes (`text-[13px]`, `text-[10.5px]`, `text-[15px]`, …) rather than these tokens.630631**Radii:** one token, `--radius: 0.5rem` (`:10`). Components use Tailwind's default scale directly (`rounded-md`, `-lg`, `-xl`, `-2xl`, `-3xl`, `-full`). No radius scale tokens.632633**Spacing:** **no custom spacing tokens** — Tailwind default scale plus arbitrary values. Runtime layout variables set by JS on `<html>`: `--kb` (keyboard inset px) and `--vvh` (visual viewport height px) (`src/lib/client/hooks.ts:26`).634635**Motion:** **no duration/easing tokens.** The only animation token is `--animate-shimmer: shimmer 1.6s ease-in-out infinite` (`:39`). Hard-coded animations:636637| Name | Definition | Where |638|---|---|---|639| `fade-up` | `180ms ease-out both`, `opacity 0 → 1`, `translateY(4px) → 0` | `:202-203`; popovers, menus, dialogs, toasts, tooltips, palette, suggestion list |640| `sheet-up` (`.sheet`) | `220ms cubic-bezier(.2,.8,.2,1) both`, `translateY(24px)` + fade; from 640 px up `.sheet` uses `fade-up` | `:213-215` |641| `pulse-soft` | `1.4s ease-in-out infinite`, opacity 1 ↔ .45 | `:204-205` |642| `shimmer` (`.skeleton`) | `1.6s ease-in-out infinite` background slide | `:108-109` |643| sidebar width | `transition-[width] duration-200 ease-out` | `Shell.tsx:61` |644| block highlight flash | Web Animations API, 2600 ms `ease-out` | `PageView.tsx:52` |645646Dead class: `data-[state=open]:animate-in` on the dialog overlay (`ui/primitives.tsx:16`) — no `animate-in` utility/plugin exists in the repo. The `drawer` class on the mobile drawer (`MobileNav.tsx:100`) has no CSS rule: the drawer has **no open/close animation** (only the drag transform).647648**Other global CSS:** `.safe-top` / `.safe-bottom` (`:97-98`), global focus ring `outline: 2px solid var(--ring); outline-offset: 2px` on `:focus-visible` (`:99-102`), thin scrollbars (`:95`), `::selection` = `--accent-soft` (`:96`), `.measure { max-width: 46rem }`, `.tabular`, `.side-label` / `.side-item` (`:113-115`), cover presets `.cover-aurora|ink|paper|dusk|moss|grid` (`:119-124`), `.db-scroll`, `.graph-canvas`, `.note-editor …` rules (`:141-185`, `:216-232`), `.prose-md`, `.cite-chip`, `.chat-md`. Body: `font-feature-settings: "cv11", "ss01", "liga"`, `antialiased`.649650### 9.3 Themes651652- **Two themes:** light (`:root`) and dark (`.dark` class on `<html>`); user choice is three-way: `system | light | dark`. No additional/accent/high-contrast themes (absent).653- **Switch/store:** `useUI().setTheme` (`src/lib/client/store.ts:10`) toggles `document.documentElement.classList.toggle("dark", …)` and writes `localStorage["note-theme"]`; the value is also persisted in the zustand `note-ui` blob. A blocking inline script in `<head>` applies `.dark` before paint (`layout.tsx:17,22`). `Shell.tsx:24` re-applies on mount.654- **UI entry points:** sidebar header menu Light/Dark/System (`Sidebar.tsx:71-72`), Settings → Appearance (`settings/AppearanceSection.tsx:21`), command palette "Dark mode" / "Light mode" (`CommandPalette.tsx:37`; no "System" command).655- Theme is **device-local** ("Stored on this device", `AppearanceSection.tsx:21`), not in `users.preferences`.656- **Gap:** in `system` mode there is no `matchMedia` change listener — an OS theme change is only picked up on the next load.657- `viewport.themeColor`: light `#f9fafc`, dark `#12141c` (`layout.tsx:15`), keyed on `prefers-color-scheme` (not on the manual choice).658- **Other appearance prefs** (`settings/prefs.ts`): `density` `comfortable | compact` (sets `html[data-density]` and root `font-size: 15px` when compact — no CSS rule targets `data-density`), `editorFont` `sans | serif` (injects `<style id="note-prefs-style">` setting `.note-editor .ProseMirror { font-family: var(--font-serif) }`). Stored server-side in `users.preferences` (`PATCH /api/settings`) and mirrored in `localStorage["note-prefs"]`.659660### 9.4 Fonts661662Declared stacks (`globals.css:7-9`):663- `--font-sans: "Inter", ui-sans-serif, system-ui, -apple-system, "Segoe UI", sans-serif`664- `--font-serif: "Source Serif 4", "Iowan Old Style", ui-serif, Georgia, serif`665- `--font-mono: "JetBrains Mono", ui-monospace, SFMono-Regular, Menlo, monospace`666667**No font is loaded by the app:** no `next/font`, no `@font-face`, no font files in `public/`, no external font URL (and the CSP `font-src 'self' data:` would block a CDN). Inter / Source Serif 4 / JetBrains Mono render only if installed on the device; otherwise the system fallbacks apply. The only bundled webfonts are KaTeX's (via `katex/dist/katex.min.css`, `globals.css:2`). `<body className="font-sans">` (`layout.tsx:23`).668669### 9.5 Component primitives (`src/components/ui`)670671| File | Export | Variants / props |672|---|---|---|673| `button.tsx` | `Button`, `buttonVariants` (cva) | `variant`: `default`, `secondary`, `ghost`, `outline`, `danger`, `ai`, `link`; `size`: `default` (h-9), `sm` (h-8), `xs` (h-7), `lg` (h-10), `icon` (size-9), `iconSm` (size-8), `iconXs` (size-7); `asChild` (Radix `Slot`) |674| `input.tsx` | `Input`, `Textarea`, `Label` | no variants |675| `primitives.tsx` | `Dialog`, `DialogTrigger`, `DialogClose`, `DialogContent` | `title`, `description`, `wide` (`max-w-4xl` vs `max-w-lg`) — Radix Dialog |676| | `SheetContent` | bottom sheet on phones, centred dialog from `sm`; `title`, `description`; max height follows `--vvh`; safe-area bottom padding |677| | `Popover`, `PopoverTrigger`, `PopoverAnchor`, `PopoverContent` | `align` ("start"), `sideOffset` (6) |678| | `Tip` | tooltip; `label`, `side` (`top`/`bottom`/`left`/`right`, default bottom), delay 400 ms; wraps its own `TooltipProvider` |679| | `Menu`, `MenuTrigger`, `MenuContent`, `MenuItem`, `MenuSeparator` | Radix DropdownMenu; `MenuItem.danger`; 44 px rows below `md`, 32 px above |680| | `Switch` | Radix Switch |681| | `Tabs`, `TabsList`, `TabsTrigger`, `TabsContent` | Radix Tabs (segmented style) |682| | `Badge` | `tone`: `neutral`, `accent`, `ai`, `success`, `warning`, `danger` |683| | `Kbd`, `Spinner`, `Empty` (`icon`, `title`, `hint`, `action`), `Skeleton` | — |684| `toast.tsx` | `toast(title, opts)`, `Toaster` | `tone`: `neutral`, `success`, `danger`, `ai`; `description`; `action { label, run }`; `duration` (default 4200 ms, 8000 ms with an action); max 3 visible; custom implementation |685686**Absent as shared primitives:** Select, Checkbox, Radio, Avatar, ScrollArea, Separator, ContextMenu, Accordion, Slider, DatePicker, Card, Table, Breadcrumb, Combobox. The packages `@radix-ui/react-avatar`, `-checkbox`, `-context-menu`, `-scroll-area`, `-select`, `-separator`, `-toast` are in `package.json` but imported nowhere in `src` (only `react-dialog`, `-dropdown-menu`, `-popover`, `-slot`, `-switch`, `-tabs`, `-tooltip` are). `sanitize-html` is likewise unused. Feature folders define their own local UI helpers (e.g. `src/components/research/ui.tsx`, `src/components/database/Responsive.tsx`) — not audited here.687688### 9.6 Animation library689690The **`motion` package (`^14.9.0`) is a dependency but is not imported anywhere** — grep for `from "motion…"` / `framer-motion` over `src` returns nothing. All animation is CSS keyframes/transitions (§9.2), Tailwind `animate-spin` / `animate-pulse`, one Web Animations API call (`PageView.tsx:52`), and manual touch-drag transforms (`MobileNav.tsx:86-87`). Drag-and-drop uses `@dnd-kit` (database views, 4 files); the graph uses `react-force-graph-2d`.691692### 9.7 `prefers-reduced-motion`693694One global rule, `globals.css:135-138`: every element and pseudo-element gets `animation-duration: 0.001ms !important; animation-iteration-count: 1 !important; transition-duration: 0.001ms !important; scroll-behavior: auto !important`, and `.skeleton { animation: none }`. No JS-level handling: the Web Animations flash (`PageView.tsx:52`), `scrollIntoView` calls and the force-graph simulation are not gated (no `useReducedMotion`, no `matchMedia("(prefers-reduced-motion…")` in `src`).695696### 9.8 Shell layout697698- **Root** (`src/app/layout.tsx`): `<html lang="en">`, theme script, `<Providers>` (React Query only, `components/providers.tsx`) + `<Toaster />`. Viewport: `viewportFit: "cover"`, `width: device-width`, `initialScale: 1`, **`maximumScale: 1`** (pinch-zoom disabled), `interactiveWidget: "resizes-content"`. `robots` noindex.699- **App layout** (`src/app/(app)/layout.tsx`): server component; `requireUserPage()`, `getWorkspace()`, `listTree()` → `<Shell user workspace initialTree>`. `loading.tsx` = skeleton grid.700- **`Shell`** (`shell/Shell.tsx:59-74`): `div.flex.h-dvh` = desktop `<aside>` sidebar (`w-[17rem]`, collapses to `w-0`, hidden below `md`) + column [`TopBar` (desktop) / `MobileHeader` (phone) → `<main id="main-scroll">` (the scroll container; phone bottom padding `4.5rem + safe-area`) → `MobileNav`] + optional `AIPanel` + `CommandPalette` + `CaptureSheet` + update banner + `ShortcutsDialog` + focus-mode exit button. "Skip to content" link (`:60`). The tree is live via React Query key `["tree"]` → `GET /api/tree`, `staleTime 10_000`.701- **Global shortcuts** (`Shell.tsx:26-35`; ⌘ or Ctrl): `K` palette, `/` AI panel, `\` sidebar, `⇧N` new page, `⇧C` quick capture, `.` focus mode, `?` shortcuts help, `Esc` leaves focus mode (when not inside the editor). `ShortcutsDialog.tsx` lists them (it omits ⌘⇧C). In-editor: ⌘J AI on selection (`Editor.tsx:96`).702- **UI state** (`store.ts`, zustand `useUI`): `sidebarOpen`, `aiOpen`, `aiWidth` (400), `focusMode`, `theme`, `paletteOpen`, `captureOpen`, `drawerOpen`, `aiContext { pageId, selection, conversationId, prefill }`.703704**Sidebar** (`shell/Sidebar.tsx`), top to bottom:7051. Header: app icon + workspace name + user display name (link to `/`); theme menu.7062. "Search ⌘K" button (opens the palette); "Quick capture…" inline field (`POST /api/capture`, kind `link` if URL else `note`).7073. Primary nav: **Home** `/`, **Ask AI** (⌘/), **Inbox** `/inbox`, **Tasks** `/tasks`, **Graph** `/graph`.7084. Scrollable tree (`role="tree"`): **Pinned** (if any); **Databases** (up to 12, `+` creates one); **Workspace** — sections `SECTIONS` (`:15-17`): **Notes**, **Documents**, **Projects**, **Research** (creates `type: "research"`), each collapsible with a root-page count and `+`; nested page items (indent `4 + depth×14` px, expand chevron, hover actions: menu [Add sub-page, Pin/Unpin, Duplicate, Move to trash] and `+`); **Intelligence**: Research `/research`, Backlinks `/backlinks`, Agents `/ai/agents`, AI models `/ai/models`, Compare models `/ai/compare`.7095. Footer icons: Archive `/archive`, Trash `/trash`, Settings `/settings`, Keyboard shortcuts, **Lock** (logout + `wipeLocalData`).710- Tree open state in `localStorage["note-tree-open"]`. **No drag-and-drop reordering of pages in the tree** (no dnd import in `Sidebar.tsx`).711712**TopBar** (desktop, `shell/TopBar.tsx`, `h-12`): sidebar toggle, breadcrumb (page ancestors or route label), "Search or jump to… ⌘K" button (`lg`+; icon only below), New page, Focus mode, Shortcuts, "Ask AI" toggle. (`ROUTE_LABEL` maps `inbox` → "Tasks" at `:11` — a labelling bug; the mobile map is correct.)713714**Command palette** (`shell/CommandPalette.tsx`, `cmdk`), opened by ⌘K / search buttons. Two modes toggled with **Tab** or the badge: `quick` ("Titles", cmdk filtering on, `GET /api/search?mode=quick&q=&limit=12`) and `full` ("Content", `mode=hybrid`, ≥ 3 characters, results carry `chunkId`, `pageId`, `blockId`, `heading`, `semantic` score). Complete command list (quick mode):715716| Group | Command | Action |717|---|---|---|718| Search (when query) | Search content for “q” | → `/search?q=` |719| Create (when query) | New page “q” | `POST /api/pages {title:q}` → open |720| Create (when query) | Ask AI “q” | `openAI({ prefill: q })` |721| Pages | search hits, or the first 8 tree nodes when the query is empty | → `/p/<id>` |722| Go to | Home | `/` |723| Go to | Quick capture | opens the capture sheet |724| Go to | Inbox | `/inbox` |725| Go to | Tasks | `/tasks` |726| Go to | Archive | `/archive` |727| Go to | Knowledge graph | `/graph` |728| Go to | AI models dashboard | `/ai/models` |729| Go to | Compare models | `/ai/compare` |730| Go to | Agents | `/ai/agents` |731| Go to | Trash | `/trash` |732| Go to | Settings | `/settings` |733| Actions | New page (⌘⇧N) | `POST /api/pages {title:""}` → open |734| Actions | New database | `POST /api/pages {title:"New database", type:"database", section:"databases"}` |735| Actions | Open AI assistant (⌘/) | `openAI()` |736| Actions | Dark mode | `setTheme("dark")` |737| Actions | Light mode | `setTheme("light")` |738739Not in the palette: Research, Backlinks, focus mode, toggle sidebar, system theme, shortcuts help, lock, and any page-context or editor command (no "insert block", "export", "version history" …). No recent-commands memory, no extensibility registry (items are hard-coded JSX).740741**Mobile** (`shell/MobileNav.tsx`, below `md`):742- `MobileHeader` (`h = 3rem + safe-area-inset-top`): Back (browser history, else parent/home) with parent/section label, centred route title, `SyncPill`, AI button.743- **Bottom tab bar** `MobileNav` (`.mobile-nav`, fixed, `safe-bottom`): **Home** `/`, **Search** `/search`, centre **+** (quick capture), **Inbox** `/inbox` (badge from `GET /api/inbox?count=1`), **More** (opens the drawer). Hidden while the keyboard is open or the editor is being edited (`globals.css:211`) and in focus mode.744- `MobileDrawer` (Radix Dialog, left, `w-[86vw] max-w-[22rem]`; opens by "More" or a left-edge swipe from the first 20 px; swipe left closes): Favourites (≤ 8), Recent (≤ 5, `GET /api/pages?view=recent&limit=6`), Spaces (Notes, Documents, Projects, Research — one level at a time), Databases (≤ 8) + New database, Tools (Tasks, Research & references, Connections `/backlinks`, Graph, Agents, AI models & costs), Library (Archive, Trash, Settings, Lock this device).745746**Capture sheet** (`shell/CaptureSheet.tsx`): `SheetContent` "Quick capture". Kind radio: **Note / To-do / Link** (auto-switch to Link when the text is a URL). One textarea (⌘/Ctrl+Enter saves). Actions: camera photo (`capture="environment"`), attach file (≤ 200 MB each), **voice memo** (`MediaRecorder`, `audio/mp4` → `.m4a` else `audio/webm` → `.webm`, shown only when supported), **dictation** (`SpeechRecognition` / `webkitSpeechRecognition`, final results appended to the text, shown only when supported), **Capture**, **Capture and open**. Saves through `enqueueCapture` (offline-capable, §8.4). Draft in `localStorage["note-draft-capture"]`. Server kinds: `note | tasks | link | photo | file | audio` (`src/app/api/capture/route.ts:10`).747748**AI panel** (`shell/AIPanel.tsx`): desktop = right `<aside>`, resizable 320–720 px (`aiWidth`); phone = full-screen `role="dialog"` pinned to `bottom: var(--kb)`. Hosts `components/chat/Chat`.749750**Breakpoints:** Tailwind v4 defaults only (no custom breakpoints in `@theme`): `sm` 640, `md` 768, `lg` 1024 (+ `xl`, `2xl` unused in the files read). The **phone/desktop split is `md` (768 px)**: `hidden md:flex` / `md:hidden`, `useIsPhone` = `(max-width: 767.98px)` (`hooks.ts:10`), CSS `@media (max-width: 767.98px)` (`globals.css:223`). `sm` (640) switches bottom sheet ↔ centred dialog. Input-type queries: `(pointer: coarse)` (`useIsTouch`, `hooks.ts:12`; `globals.css:237,252`). One ad-hoc `max-[380px]:` (`PageView.tsx:66`). Page column widths: `max-w-[50rem]` for pages, `max-w-[84rem]` for databases (`PageView.tsx:59`).751752**Safe-area handling:** `viewportFit: "cover"`; `.safe-top` / `.safe-bottom` (`env(safe-area-inset-top|bottom)`) on sidebar, top bar, mobile header, mobile nav, drawer, AI panel, offline screen; `main` bottom padding `calc(4.5rem + env(safe-area-inset-bottom))`; sheet body `pb-[max(1rem,env(safe-area-inset-bottom))]`; toaster `bottom: calc(var(--kb,0px) + 5rem + env(safe-area-inset-bottom))`; update banner `top: calc(env(safe-area-inset-top) + 0.5rem)`. Left/right insets (`safe-area-inset-left/right`) are **not** handled (landscape notch).753**Keyboard handling:** `useKeyboardInset` (`hooks.ts:19-33`) publishes `--kb`, `--vvh`, `html[data-kb="open"]` (threshold 80 px); mobile forms forced to ≥ 16 px (`globals.css:234`).754755**Icon set:** `lucide-react` (^1.66.0) everywhere for UI icons; emoji for page icons, callout icons, default page/database glyphs (📄 🗂 🔬) and the file block (📎); app icon `public/icons/icon.svg` (+ PNG 192/512, maskable 512, apple-touch).756757---758759## Gaps list for the upgrade760761| Item | Status | Evidence |762|---|---|---|763| Drawing block | **absent** | no canvas/drawing lib in `package.json`; no drawing node in `nodes.ts` |764| Diagram / Mermaid block | **absent** | grep `mermaid` over `src` + `package.json`: none; code blocks are highlight-only |765| Data / chart block | **absent** | no chart lib; only hand-written SVG in `src/components/ai/models/Scatter.tsx`, `UsagePanel.tsx` (not editor blocks) |766| Scientific citation block | **partial** | references/BibTeX/DOI/extracts exist outside the editor (`src/lib/research/cite.ts`, `references.ts`, `bibtex.ts`, `/api/research/references/**`, `components/research/*`) and reach the page only as inserted Markdown (`note:insert-markdown`); in the editor only generic `footnoteRef`/`footnote` (`nodes.ts:28-39`). No citation node bound to a reference id, no bibliography block |767| Formula / math | **exists** | `inlineMath`, `blockMath` via `@tiptap/extension-mathematics` + KaTeX (`Editor.tsx:85`); editing by `window.prompt` only, no live editor |768| Annotatable PDF | **absent** | PDFs are a `fileBlock` link opening in a new tab (`nodes.ts:25`); server text extraction only (`pdf-parse`, `src/lib/files/extract.ts`, `src/lib/import/pdf.ts`); no pdf.js viewer, no annotation model |769| File block | **exists** | `fileBlock` atom (`nodes.ts:21-26`), upload with progress (`Editor.tsx:40-72`); no inline preview, name/size only |770| Interactive table | **partial** | Tiptap `table` with resizable columns (`Editor.tsx:78`), row/column ops only in the mobile toolbar (`MobileToolbar.tsx:52-57`); no desktop table menu, no sort/filter/formulas. The full database engine (`src/components/database/**`) is a page type (`type: "database"`), referenced from a page by a `mention` only — not embeddable as a block |771| Enriched tasks | **partial** | `taskItem` has only `checked` (no id, due date, assignee, priority; not in the block-id list). A separate `tasks` table + `/tasks` screen + natural-language parser exist (`src/db/schema.ts:142`, `src/components/tasks/parse.ts`); linkage between editor checkboxes and `tasks` rows not found in the files read (not audited further) |772| Executable code | **absent** | `codeBlock` = lowlight highlighting only; no runtime/sandbox (grep `pyodide|sandbox|runCode|WebContainer`: none) |773| AI result block | **exists** | `aiBlock { model, prompt, at }` (`nodes.ts:14-19`), produced by `AIInline.tsx:80` and `PageView.tsx:48`; static provenance wrapper — no regenerate/refresh, no stored cost/sources |774| Cross-reference / transclusion / synced block | **partial** | page-level `mention` + backlinks + `#block-<id>` deep links exist (`mention.tsx`, `text.ts:27`, `PageView.tsx:50`); no block reference node, no transclusion/embed of another page or block, no synced blocks (grep: none) |775| Infinite canvas | **absent** | no canvas/whiteboard page type; `/graph` is a force-directed knowledge graph (`react-force-graph-2d`, `src/components/graph/GraphCanvas.tsx`), not an editable canvas |776| Full offline (IndexedDB) on web | **partial** | IndexedDB `note-local` holds only unsaved drafts + capture outbox (`src/lib/client/offline.ts`); no local page store, navigation is network-only (`public/sw.js:30-31`), no Background Sync; cannot read or create notes offline |777| Voice notes / transcription | **partial** | voice memo recording (`MediaRecorder`) → audio attachment, and live browser dictation (`SpeechRecognition`) in `CaptureSheet.tsx:38-58`; capture kind `audio` server-side. **No server-side transcription** of recorded audio (grep `transcri|whisper`: only the dictation callback and a model-id exclusion regex in `src/lib/ai/catalog.ts:39`); no audio player block in the editor; no recording from inside the editor. `Permissions-Policy: microphone=(self)` (`next.config.ts`) |778| Command palette | **exists** | `src/components/shell/CommandPalette.tsx` (cmdk): 16 static commands + page search + hybrid content search; no editor/page-context commands, no plugin registry |779| Semantic zoom | **absent** | no zoom/outline-level concept (grep: none); only a focus mode (`Shell.tsx:33`) and collapsible `details` |780| Living / query blocks | **absent** | no query/live block node (grep `queryBlock|liveQuery|dataview`: none); dynamic views exist only in the database page type and in dashboard widgets (`src/components/dashboard/widgets.tsx`) |781782---783784## Could not verify7857861. **Library internals** (`node_modules` absent): exact default attrs of Tiptap 3.32 nodes/marks (`image` width/height, `tableCell` `colwidth`, `youtube` attrs, `textAlign` default, `link` attrs, `mention` extra attrs), the exact StarterKit 3 bundle (duplicate `Dropcursor`/`Gapcursor`/`TrailingNode`), whether Mathematics ships `$…$` input rules, and Tiptap's fallback to an empty document on unknown node types. All tagged **[UNVERIFIED-LIB]**.7872. Nothing was executed: runtime behaviour (SW caching in a real browser, conflict flows, HTML clipboard round-trip of custom nodes) is inferred from source only.7883. Out of the assigned file set and only skimmed or not read: `src/components/chat/**`, `src/components/database/**`, `src/components/research/**` (local UI helpers), `src/lib/files/extract.ts`, `src/app/api/attachments`, `src/app/api/capture` (one line), `src/app/api/tree`, `src/app/api/export`, `src/proxy.ts`, DB triggers in `drizzle/` (e.g. `sync_bump_rev`), and any native-client sync API. The link between editor `taskItem` nodes and the `tasks` table was not traced.789790---791792# Part B — AI, agents, research (code-level inventory)793794Source: `/tmp/spnote-next/_src` (read-only copy). Paths are relative to repo root. "absent" = searched and not found. Items marked **[unverified]** were not read in full. Sections 1 (research UI), 2 (models dashboard), 3 (compare UI), 4 (AISection) of the UI detail come from a delegated full read of the component files; everything in `src/lib/**`, `src/app/api/**`, `src/worker/**`, `Chat.tsx`, `AIInline.tsx`, `ProposalCard.tsx`, `ExecutionDrawer.tsx`, `ExecutionList.tsx` was read directly.795796## 0. Shared route contract (applies to every route below)797798All routes are wrapped by `handler()` (`src/lib/api.ts:22`):799- **Auth**: `requireUser(req)` (`src/lib/auth/guard.ts:13`) → 401 `unauthenticated` without a session. Mutating methods (non GET/HEAD/OPTIONS) also run `assertSameOrigin()` (`guard.ts:33`): `Sec-Fetch-Site` must be `same-origin` and/or `Origin` must be in `allowedOrigins()`, else 403 `csrf`.800- **Workspace**: `getWorkspace(user.id)` (single owner workspace) → `ctx.ws`.801- **Step-up**: only when the route passes `{ fresh: true }` → `requireFresh` → 403 `reauth_required` (passkey verified < 5 min). **None of the AI / agents / research routes pass `fresh`** (absent in all 24 route files). (`/api/export` whole-workspace formats do — outside this part.)802- **Idempotency**: optional. If a POST/PATCH/PUT/DELETE carries `Idempotency-Key` (`^[A-Za-z0-9_-]{8,100}$`), `idempotent()` (`api.ts:44`) stores the 2xx **JSON** response (≤ 512 KB) in `idempotency_keys` and replays it (`idempotent-replay: true`); 409 `in_progress` for a concurrent duplicate < 60 s; 422 `idempotency_mismatch`. **SSE responses are never stored** (not JSON) — the key is released. The client helper `api()` (`src/lib/client/api.ts:19`) adds a key to every POST automatically; whether `sse()` does is **[unverified]**.803- **Errors**: `{ error, code?, ...extra }`; every response gets `x-request-id`, `cache-control: private, no-store`.804- **SSE format** (helper `sse()` in `src/lib/ai/chat.ts:288`): `content-type: text/event-stream`; each event is `data: <JSON>\n\n` (no `event:` names — the discriminator is the JSON `type` field); terminated by `data: [DONE]\n\n`; a thrown generator yields `{type:"error", code:"server_error", retryable:true, message}`.805806---807808## (a) API routes809810### `src/app/api/ai/*`811812| File | Method | Purpose | Request | Response |813|---|---|---|---|---|814| `ai/chat/route.ts` | POST (`maxDuration=600`) | Assistant chat | zod: `conversationId?` uuid\|null, `clientId?` `/^[A-Za-z0-9_-]{8,64}$/`, `message` 1–60 000, `mode?` enum `CHAT_MODES`, `pageId?` uuid, `model?` ≤80, `task?` enum of the 10 `TaskClass`, `useWorkspace?` bool, `confirmExpensive?` bool, `images?` ≤4 × `{mime: image/(png\|jpeg\|webp\|gif), dataBase64 ≤ 8 000 000}`, `scopePageIds?` uuid[] ≤50 | SSE of `ChatEvent` (see below) |815| `ai/chat/plan/route.ts` | GET | Dry run, never calls a provider | query: `mode?`, `action?` (enum `ACTION_IDS`), `actions?="1"`, `pageId?`, `model?`, `chars?` 0–2 000 000, `images?` 0–4 | `actions=1` → `{[action]: ActionPlan}` for the fixed list `rewrite, fix, translate, summarize, expand, explain, to_list, to_tasks, table, custom` (l.14); `action=` → `ActionPlan`; otherwise `ChatPlan` (message simulated as `"x".repeat(min(chars,60000))`) |816| `ai/action/route.ts` | POST (`maxDuration=300`) | Inline action on a selection; writes nothing | zod: `action` enum `ACTION_IDS`, `text` ≤120 000, `instruction?` ≤4000, `model?`, `language?` ≤8, `confirmExpensive?` | Pre-stream errors: 400 `bad_language` (translate without a `TRANSLATE_TARGETS` key), 400 `no_instruction` (custom), 503 `no_model`/`not_configured`, 409 `confirm_expensive` with `{plan}`. Then SSE: `{type:"meta", model, provider, reason, budgetNote}` → `{type:"text", delta}`* → `{type:"done", usage, costUsd, latencyMs}` or `{type:"error", code:"refused"\|"provider_error", retryable:true, message}` |817| `ai/conversations/route.ts` | GET | `?id=` → conversation row + `messages[]` (full `ai_messages` rows incl. `sources`, `meta`); else list (optional `?pageId=`), not deleted, order `pinned desc, updatedAt desc`, limit 100 | — | JSON |818| | PATCH | rename / pin / set model | `{id, title? ≤200, pinned?, model?}` | `{ok:true}` |819| | DELETE | soft delete `?id=`; `&restore=1` undeletes | — | `{ok:true}` |820| `ai/models/route.ts` | GET | Catalogue + routes | `?all=1` includes disabled/unavailable/embedding | `{models[] (ai_models rows, sorted provider then benchmark.mean_score), providers[] (ai_providers rows + `configured`), routes, defaultRoutes, taskLabels, benchmark (settings `ai.benchmark_summary`)}` |821| | POST | Admin actions (audit-logged) | `{action: "discover"\|"toggle"\|"routes"\|"budget", id?, enabled?, routes? (record any), usd? 0–10 000}` | discover → report `{[provider]:{listed,status}}`; toggle → `{ok}` (sets `ai_models.enabled`); routes → upsert settings `ai.routes`; budget → upsert settings `ai.daily_budget_usd` |822| `ai/usage/route.ts` | GET | Usage aggregates | `?days=` (default 30, max 90) | `{days, byModel[{model,provider,calls,errors,inputTokens,outputTokens,costUsd,latency(p50),ttft(p50)}], byDay[{day,costUsd,calls}], byTask[{task,calls,costUsd}], recent[50 ai_usage rows]}`. Note: not filtered by workspace. |823| `ai/compare/route.ts` | POST (`maxDuration=600`) | Run one prompt on N models in parallel (non-streaming `callAI` per model, `maxTokens 6000`, system = `system` or `ASSISTANT_SYSTEM`) | `{prompt 1–60 000, models: string[] 1–6, system? ≤4000}` | SSE: one `{type:"result", model, provider, text, usage, costUsd, latencyMs, ttftMs, refusal}` (or `{…, error}`) per model in completion order, then `{type:"saved", id}` after insert into `ai_comparisons`. No token deltas. No expensive-confirmation, no router (model ids used as given). |824| | GET | `?id=` → one `ai_comparisons` row; else last 50 `{id,prompt,models,createdAt}` | — | JSON |825| `ai/compare/vote/route.ts` | GET | Votes | — | `{votes: {[comparisonId]: {model, at}}, tally: {[model]: wins}}` |826| | POST | "Best answer" vote | `{comparisonId uuid, model ≤120 \| null}` (null removes) | `{ok, votes}`; 404/400 if comparison/model invalid. Stored in **settings key `compare.votes`** (no table). Audit `ai.compare_vote`. |827828`ChatEvent` (`src/lib/ai/chat.ts:59-64`):829- `meta` `{conversationId, clientId, mode, model, provider, reason, task, sources: SourceRef[], weak: SourceRef[], evidence, sent: Sent, budgetNote, replayed}`830- `text` `{delta}`831- `done` `{messageId, content, usage, costUsd, latencyMs, ttftMs, sources (only the cited ones), evidence, invalidCitations: number[], uncited, replayed}`832- `needs_confirmation` `{reason, plan: ChatPlan}` (expensive class without `confirmExpensive`; nothing written or sent)833- `error` `{message, code: not_configured|page_required|not_found|no_model|refused|provider_error|server_error, retryable, conversationId?, alternative?: {model, provider}}`834835Chat-level idempotency is separate from the header: `clientId` — if an assistant message with `meta.clientId` already exists it is replayed with `replayed:true`, no provider call (`chat.ts:153-161`, `findByClientId` l.141).836837### `src/app/api/agents/*`838839| File | Method | Purpose | Request | Response |840|---|---|---|---|---|841| `agents/route.ts` | GET | Catalogue + recent runs; calls `reapStale` | `?limit=` 1–100 (default 40) | `{agents: AgentInfo[], configured, target: {provider, model}\|null, budgetNote, executions[{id, agent, model, status, outcome, error, costUsd, tokensIn, tokensOut, createdAt, startedAt, finishedAt, proposals, pendingProposals, toolCalls}]}` |842| | POST | Start a run (creates `ai_executions` row `pending`, enqueues pg-boss `agent-run` with `singletonKey agent:<id>`) | `{agent ≤40, pageId? uuid\|null, confirmCost?}` | 201 `{id, status}`. Errors: 400 `unknown_agent`, 503 `not_configured`, **409 `confirm_required`** unless `confirmCost:true`, 400 `page_required`, 404 page, **409 `already_running`** (one live run per workspace), 503 `queue_unavailable`. Audit `agent.run`. |843| `agents/[id]/route.ts` | GET | One execution (polled every 2 s by the UI while live) | — | full `ai_executions` row + `contextPageId, agentName, agentIcon, info: AgentInfo, documents[{id,title,icon,deletedAt}]` |844| | POST | Decide / undo / cancel | discriminated union on `action`: `approve {changes: int[] 1–200, confirmDestructive?}`, `reject {changes}`, `undo {changes}`, `cancel` | approve/reject → `{status, applied[], failed[{idx,error}], pending}`; undo → `{status, undone[], failed[]}`; cancel → `{ok}`. 409 `confirm_destructive` `{changes}` for trash proposals without `confirmDestructive`; 409 `not_awaiting`. |845846### `src/app/api/research/*`847848| File | Method | Purpose | Request | Response |849|---|---|---|---|---|850| `research/ai/route.ts` | POST (`maxDuration=300`) | 7 research AI features | `{feature: enum RESEARCH_FEATURES, pageId uuid, referenceIds? uuid[] ≤50, attachmentIds? uuid[] ≤20, text? ≤200 000, instruction? ≤4000, question? ≤4000, latex? ≤20 000, language? "fr"\|"en"\|null, model?}` | SSE `ResearchAIEvent`: `meta {feature, model, provider, reason, context:{references, attachments, sources, pageChars}}`, `text {delta}`, `result {data}` (refcheck, plan only), `done {usage, costUsd, latencyMs}`, `error {message}`. No expensive-confirmation gate here (absent). |851| `research/overview/route.ts` | GET | Working-paper tracker | — | `PaperOverview[]` (`src/lib/research/overview.ts:7`) |852| `research/papers/route.ts` | POST | New research page from `workingPaperTemplate` | `{title? ≤300, status? enum PAPER_STATUSES, venue? ≤200}` | 201 page (`type:"research"`, `section:"research"`, icon 🔬, `properties {status, venue?, tags:["working-paper"]}`) |853| `research/references/route.ts` | GET | List (`?pageId=` or whole library) | — | `references` rows, order `year desc, citeKey asc` |854| | POST | Create (manual) | `refSchema`: `pageId?, citeKey?, entryType?, title 1–1000, authors? string[] ≤100, year? 1000–2100, journal?, doi?, url?, fields? record, verified? user\|doi_verified\|ai_suggested, attachmentId?` | 201 row |855| `research/references/[id]/route.ts` | GET / PATCH / DELETE | Read / partial edit (`fields` = partial BibTeX tag edit, empty removes a tag) / soft delete | PATCH `patchSchema` (same fields, all optional) | row / `{ok}` |856| | POST | `?action=attach {attachmentId\|null}` · `?action=verify` (Crossref re-check) · `?action=restore` | — | row |857| `research/references/[id]/extracts/route.ts` | GET / POST / PATCH / DELETE | Quoted extracts stored in `references.fields.extracts` | POST `{id? uuid (client-chosen = retry-safe), quote 1–8000, page? ≤40, note? ≤4000}`; POST `?restore=1 {extract}`; PATCH `{id, quote?, page?, note?}`; DELETE `?extractId=` | `{extract, extracts[]}` |858| `research/references/[id]/links/route.ts` | GET | Where a reference is used | — | `{attached, citing[], experiments[], pdf}` (`ReferenceLinks`, `references.ts:213`) — "citing" = regex match of the cite key in `pages.plain_text` |859| `research/references/doi/route.ts` | GET (`maxDuration=60`) | Crossref preview, nothing saved; duplicate check first | `?doi=` | `DoiMeta` + `duplicate` |860| | POST | Add by DOI | `{doi 5–200, pageId?}` | 201 new `doi_verified` reference, or 200 `{…, duplicate:true, linked}` |861| `research/references/import-bibtex/route.ts` | POST (`maxDuration=120`) | BibTeX import, multipart (`file`/`text`, `pageId`, `dryRun`) or JSON `{text, pageId?, dryRun?}`; `?dryRun=1`; max 20 MB | — | `{dryRun, parsed, created[], wouldCreate[], skipped[], failed[], counts}` |862| `research/references/export.bib/route.ts` | GET | `.bib` of a page (`?pageId=`) or whole library; `?inline=1` omits attachment header | — | `application/x-bibtex`, `x-export-count`, filename `<slug>.bib` / `library.bib`; audit `export` |863| `research/experiments/route.ts` | GET (`?pageId=`) / POST | Experiments | `expSchema`: `pageId?, name 1–300, status? planned\|running\|done\|failed, params? record, metrics? record, datasetRef?, repoUrl?, notes? ≤20 000` | rows / 201 row |864| `research/experiments/[id]/route.ts` | PATCH / DELETE (hard delete) | — | same fields optional | row / `{ok}` |865| `research/tasks/route.ts` | GET (`?pageId=` required) / POST | Research tasks = rows of `tasks` scoped to a page | single `{pageId, title 1–500, status? todo\|doing\|done, priority? low\|medium\|high, dueAt?, project?}` or bulk `{pageId, items[1–50]}` | rows |866| `research/tasks/[id]/route.ts` | PATCH / DELETE (soft) / POST `?action=restore` | — | — | row / `{ok}` |867868All research mutations write `audit()` entries (ids/keys only).869870---871872## (b) Section 4 — AI873874### Provider adapters — `src/lib/ai/providers.ts`875- SDKs: `openai` ^7.47.0 and `@anthropic-ai/sdk` ^1.3.0 (`package.json:88,20`). Two lazily created singletons `oa()`, `an()` (l.7-8) built with **no explicit key and no baseURL**: `new OpenAI({ maxRetries: 2, timeout: 600 000 })`, `new Anthropic({...})` — i.e. the SDK defaults read `OPENAI_API_KEY` / `ANTHROPIC_API_KEY` from the environment.876- Env names referenced in code: `OPENAI_API_KEY`, `ANTHROPIC_API_KEY` (`catalog.ts:23`, `search/index.ts:75,142`, `api/ai/models/route.ts:14`), `CROSSREF_MAILTO`, `APP_URL` (`research/references.ts:126`). **Base-URL handling: absent** — no `baseURL`, `OPENAI_BASE_URL`, `ANTHROPIC_BASE_URL`, OpenRouter, MLX or Ollama string anywhere in `src/`, `scripts/`, `benchmarks/`, `docs/` (grep).877- `streamAI(o: AICallOptions): AsyncGenerator<AIEvent>` (l.14): a two-way branch `if (o.provider === "openai") {…} else {…Anthropic…}`.878 - OpenAI branch uses the **Responses API** (`oa().responses.create`, `input_text`/`input_image`, `instructions`, `text.format json_schema strict`, function tools `strict:false`, `reasoning.effort` only when `isReasoningOpenAI(model)` = `/^(o\d|gpt-5|gpt-6)/` and not `chat-latest`). It is **not** Chat Completions.879 - Anthropic branch: `an().messages.stream`, system as a cached block (`cache_control: ephemeral`), `output_config.format json_schema`, tools `input_schema`; thinking: models matching `/4-5-\d{8}$/` get `thinking:{type:"enabled", budget_tokens:2048}` when effort ≠ low, others get `thinking:{type:"adaptive"}` + `output_config.effort`.880 - Tool calls are emitted only after the stream ends (`tool_call` events); usage normalised to `AIUsage {inputTokens, outputTokens, cachedTokens, reasoningTokens}`; refusals: OpenAI by `REFUSAL_RE` text heuristic (l.12), Anthropic by `stop_reason === "refusal"`.881- `callAI(o)` (l.64): collects the stream; returns `AIResult` with `costUsd` from the catalogue.882- Types (`src/lib/ai/types.ts`): `Provider = "openai" | "anthropic"` (l.1); `ChatMessage {role: "user"|"assistant", content, images?}` — no tool-result role (agents feed tool results back as user text).883884### Router — `src/lib/ai/router.ts`885- Task classes (`TaskClass`, `types.ts:2`), exactly 10: `chat, quick, write, reason, deep, code, extract, longctx, agent, judge`. Labels in `TASK_LABELS` (l.20).886- `DEFAULT_ROUTES` (l.7-18), each `{primary, fallback, effort, categories, avoid?}`:887888| task | primary | fallback | effort |889|---|---|---|---|890| chat | gpt-6-sol | claude-sonnet-5-5 | medium |891| quick | claude-haiku-5-5 | gpt-5.4-mini | low |892| write | gpt-6-sol | claude-sonnet-4-6 | medium |893| reason | claude-opus-5-5 | gpt-6-astra | high |894| deep | claude-fable-5-1 | gpt-6-astra | high |895| code | gpt-5.3-codex | claude-sonnet-5-5 | medium |896| extract | gpt-5.4-mini | claude-haiku-5-5 | low |897| longctx | gpt-6.1-terra | claude-sonnet-5-5 | medium (avoid: gpt-5.6-terra, gpt-6-luna, claude-fable-5) |898| agent | claude-sonnet-5-5 | gpt-5.4 | medium |899| judge | claude-haiku-5-5 | gpt-5.4-mini | low |900901- Overrides: settings key `ai.routes`, shallow-merged over defaults (`getRoutes`, l.40).902- `route(n: RouteNeeds {task, tools?, schema?, vision?, estTokens?, preferred?})` (l.53): (1) budget downgrade `deep→reason`, `reason→chat` when `overBudget()`; (2) `preferred` if eligible → "user selection"; (3) primary; (4) fallback; (5) best remaining eligible model with a `benchmark.pass_rate`, sorted by mean `benchmark.by_category[c].score` over the route's `categories`, then `total_median_ms`, then `cost_mean`; (6) throws `"No eligible AI model …"`.903- `eligible()` (l.25): `available && enabled && category !== "embedding" && providerConfigured(provider)` **and `probes.text.status === "ok"`**; plus `probes.tool_use` / `structured_output` / `vision` `=== "ok"` when needed; `contextWindow ≥ estTokens × 1.2` when both known. ⇒ a model with empty `probes` is never routable.904- `classifyTask(prompt, ctx)` (l.72): regex heuristic → longctx (>60 000 ctx tokens) / code / reason / deep / write / extract / quick / chat.905- `EMBEDDING_MODEL = "text-embedding-3-small"` (l.19).906907### Model catalogue — `src/lib/ai/catalog.ts`, `src/cli/seed-ai-models.ts`, schema908- Tables (`src/db/schema.ts:190-201`):909 - `ai_providers`: `id` text PK, `name`, `enabled`, `lastDiscoveryAt`, `status`, `statusDetail`. No base URL, no key reference, no kind/type column.910 - `ai_models`: `id` text PK (the provider's model id — globally unique, **not** namespaced by provider), `provider` FK → `ai_providers.id`, `displayName`, `category` (default `text`), `available`, `enabled`, `contextWindow`, `maxOutput`, `inputPrice`, `cachedPrice`, `outputPrice` (USD per 1M tokens), `pricingSource`, `probes` jsonb `{[probe]: {status, detail?, ms?}}`, `benchmark` jsonb, `capabilities` jsonb, `notes` jsonb string[], `discoveredAt`, `probedAt`, `benchmarkedAt`, `createdAt`, `updatedAt`.911 - **Creator/vendor field distinct from provider: absent. Logo/icon field: absent.**912- DB enums/checks on provider: **absent** — `provider` columns are plain `text` (`ai_models.provider`, `ai_messages.provider`, `ai_usage.provider`); no CHECK / `CREATE TYPE` for providers/models in `drizzle/*.sql` (grep). The only DB constraint is the FK `ai_models.provider → ai_providers.id`.913- Seeding: `pnpm seed:ai` → `src/cli/seed-ai-models.ts`: reads `benchmarks/results/discovery.json` (+ optional `summary.json`), inserts providers `openai`/`anthropic` (l.8; also `src/cli/bootstrap.ts:40`), upserts models (enabled only when `verdict === "tested"` or embedding), stores `benchmark {pass_rate, mean_score, judged_mean, ttft_median_ms, total_median_ms, tps_median, cost_mean, by_category, runs, errors}`, writes settings `ai.benchmark_summary`. `benchmarks/results/` is **absent from this copy** (only `benchmarks/prompts`, `benchmarks/scripts/{bench,discover,graders,lib,providers,report}.ts`); scripts `bench:discover|run|report` in `package.json:128-130`. `benchmarks/scripts/*` not read **[unverified]**.914- Live discovery: `refreshDiscovery()` (`catalog.ts:26`), triggered by `POST /api/ai/models {action:"discover"}`: loops `["openai","anthropic"]`, calls `models.list()` on each SDK, flips `available`, inserts unknown ids as `enabled:false` with a note (OpenAI ids filtered by `/^(gpt|o\d|chat-latest)/` minus image/audio/embedding/… names), updates `ai_providers.status`. Pricing/context are **not** discovered live (only from the seed JSON).915- In-process cache: `listModels()` 60 s (`catalog.ts:9`); `invalidateCatalog()`.916- Docs: `docs/ai-provider-discovery.md`, `ai-routing-strategy.md`, `ai-model-comparison.md`, `anthropic-model-benchmarks.md`, `openai-model-benchmarks.md` (generated reports; only the first skimmed).917918### Where a new OpenAI-compatible provider (OpenRouter, local MLX with custom base URL) would plug in919Server (must change):9201. `src/lib/ai/types.ts:1` — `Provider` union.9212. `src/lib/ai/providers.ts:6-8,17,36` — only two clients and an `if openai / else anthropic` branch. The OpenAI branch speaks the **Responses API**; an OpenAI-compatible endpoint (OpenRouter, `mlx_lm.server`) generally needs a new **Chat Completions** branch + a client constructed with `baseURL`/key per provider. No adapter interface/registry exists.9223. `src/lib/ai/catalog.ts:23` `providerConfigured(p)` — binary `openai ? OPENAI_API_KEY : ANTHROPIC_API_KEY` (any other provider is treated as Anthropic). `catalog.ts:29-34` `refreshDiscovery` — hard-coded two-provider loop.9234. "Is AI configured" checks hard-coded to the two providers: `src/lib/ai/chat.ts:122,165,277` (`configured: {openai, anthropic, any}` is part of the `ChatPlan`/`ActionPlan` response shape), `src/app/api/agents/route.ts:23,33`, `src/app/api/ai/models/route.ts:14` (`configured` per provider row).9245. `ai_providers` has no `base_url`/`kind`/key column (schema change), and rows are only seeded for the two (`seed-ai-models.ts:8`, `bootstrap.ts:40`). `seed-ai-models.ts:16` picks `pricingSource` by `provider === "openai"`.9256. Router eligibility requires `probes.text.status === "ok"` (and `tool_use`/`structured_output`/`vision` probes) — new models need probe data or a bypass (`router.ts:28-31`); cost needs `inputPrice`/`outputPrice` or `costUsd()` returns null.9267. `ai_models.id` is the PK across providers — OpenRouter ids (`vendor/model`) fit as text but collide conceptually with direct ids; API zod caps `model` at 80 chars.9278. Embeddings are OpenAI-only regardless: `src/lib/search/embed.ts` (own `new OpenAI()`, hard-coded price `0.02`/1M, `provider:"openai"`), `vector(1536)` in `schema.ts:9`, `semanticOn = providerConfigured("openai")` (`chat.ts:106`), `process.env.OPENAI_API_KEY` gate in `search/index.ts:75`.9289. Minor: `src/worker/handlers/ai-property.ts:54` logs failures as `provider:"openai"`.929930UI (hard-coded, from the delegated read): `src/components/ai/models/format.ts:3,15` (type + `providerTone` colours), `CatalogueTable.tsx:28` (filter buttons All/OpenAI/Anthropic), `RoutingTable.tsx:18` (`["openai","anthropic"]` optgroups — other providers' models would not appear in pickers), `Scatter.tsx:18,39`, `ModelsDashboard.tsx:33` + `Provenance.tsx:3,15` (pricing links), `src/components/ai/compare/ModelPicker.tsx:9,15` (two fixed groups; presets with fixed model ids l.10), `ComparePanel.tsx:20` (default models). Not hard-coded: `ProviderCards.tsx` (iterates `providers`), `Heatmap`, `UsagePanel`, `src/components/settings/AISection.tsx`, and the chat model `<select>` (`Chat.tsx:149`, renders `provider · id` from `/api/ai/models`).931932### Inline AI actions933- Ids: `ACTION_IDS` (`src/lib/ai/chat.ts:271`), 21: `rewrite, fix, translate, summarize, expand, explain, to_list, to_tasks, table, shorten, formal, simplify, translate_fr, translate_en, explain_math, continue, outline, study_notes, action_items, contradictions, custom`.934- Prompts: `src/lib/ai/prompts.ts` — `ACTION_SYSTEMS` (l.9-29; keys: rewrite, shorten, expand, formal, simplify, fix, summarize, translate_fr, translate_en, explain_math, continue, outline, table, study_notes, action_items, contradictions, explain, to_list, to_tasks), `translateSystem(target)` with `TRANSLATE_TARGETS` (fr, en, es, de, it, pt), `custom` uses an inline fallback string in `api/ai/action/route.ts:19`. `INJECTION_GUARD` appended; text wrapped in `<workspace kind="text">`.935- Task class per action: `ACTION_TASK` (`chat.ts:273`): explain_math/contradictions → reason; table/to_list/to_tasks/action_items → extract; fix → quick; custom/explain → chat; all others → write.936- Where offered (`src/components/editor/AIInline.tsx`, mounted in `src/components/editor/Editor.tsx:165`):937 - Picker chips `ACTIONS` (l.26-29): rewrite, fix, summarize, expand, explain, to_list, to_tasks, table; Translate (→ Français, → English, select among 6 `LANGS`); free "custom" instruction.938 - Slash menu (`src/components/editor/extensions/slash.tsx:34-36,62-64`): `ai-write` → mode `write` (custom with empty text), `ai-continue` → `continue`, `ai-summary` → `summarize` (whole page text).939 - Mobile toolbar "AI on selection" (`MobileToolbar.tsx:48` → `openAIForSelection("custom")`, `Editor.tsx:100`). Desktop bubble/toolbar entry points other than these: **[unverified]**.940 - Auto-start set `AUTO` (l.32) also accepts `shorten, formal, simplify, translate_fr, translate_en, explain_math, action_items`, but **no UI entry point for `shorten, formal, simplify, explain_math, outline, study_notes, action_items, contradictions` was found** in the files read (API-only).941- Apply: preview first; `Replace selection` (refused if the doc changed) / `Insert below` / Copy / Discard / Regenerate; result is wrapped in an `aiBlock` node `{model, prompt, at}` (l.80) unless it is a single inline paragraph replacement; one transaction = one undo.942943### Chat944- Storage: `ai_conversations` (`title` = first 80 chars of the first message, `pageId`, `model`, `routing` `auto|manual`, `pinned`, soft `deletedAt`) and `ai_messages` (`role`, `content`, `model`, `provider`, `sources` jsonb, `usage`, `costUsd`, `latencyMs`, `ttftMs`, `meta` jsonb `{mode, clientId, evidence, sent, task, reason, stop, invalidCitations, uncited, pageId}`) — `schema.ts:202-212`. History sent to the model: last `RETRIEVAL.history = 20` messages.945- Modes = the scope options (`CHAT_MODES`, `chat.ts:35`; UI list `MODES` in `src/components/chat/Chat.tsx:32-39`): `notes` (retrieval only), `summarise` / `actions` (open page text only), `contradictions` (page + excerpts from other pages), `draft` (retrieval restricted to `sourceTypes:["attachment"]` + matching `references` rows as a `reference-list` block), `general` (no workspace content). `PAGE_MODES` need `pageId`.946- Other scoping: the API accepts `scopePageIds` (≤50, → `hybridSearch pageIds`), `useWorkspace`, `task`; **the Chat UI sends none of them** (`Chat.tsx:88` sends `conversationId, clientId, message, mode, pageId, model, confirmExpensive, images`). Scope by tag / section / database / date: absent in chat (the `SearchFilters` type supports `section`, `kinds`, `since/until` but chat does not pass them).947- Model selector: plain `<select>` "Auto (measured routing)" + `provider · id` options (`Chat.tsx:148-150`). No logos.948- Mounts: `src/components/shell/AIPanel.tsx` and dashboard widget `<Chat compact />` (`src/components/dashboard/widgets.tsx:139`).949950#### RAG pipeline951- **Chunking** (`src/lib/search/chunk.ts`): `chunkDocument(title, doc, {target:350, max:500, overlap:60})` — tokens approximated as `chars/3.6` (`approxTokens`). Walks `docBlocks(doc)`; a heading flushes the buffer and becomes the chunk's `heading` (and `blockId`); blocks are accumulated until ~350 tokens; a single block > 500 tokens is split by sentence (`chunkLongText`) with ~60-token overlap. Each chunk keeps **one `blockId` = the id of its first block** (or of the heading right after a heading), `ordinal`, `tokenCount`, `contentHash` (sha256, 32 hex). Attachments/plain text: `chunkPlainText` (paragraph-based, same 350/500, `blockId: null`). Empty page → one chunk with the title.952- **Indexing** (`src/lib/search/index.ts:11` `indexSource`): source types `page | attachment | row` (`row` is ignored by the worker handler). Incremental: source hash over `contentHash:blockId:heading`; unchanged chunks keep their embedding; text embedded = `heading + "\n" + content`. Tables `chunks` (with generated `tsv` = `to_tsvector('simple', heading || ' ' || content)`, GIN), `embeddings` (`chunkId` PK, `model`, `version`, `embedding vector(1536)`, HNSW `vector_cosine_ops`), `index_state`.953- **Embeddings**: `text-embedding-3-small`, **1536 dims** (`schema.ts:9`), `EMBEDDING_VERSION = 1`; computed in `src/lib/search/embed.ts` (`embedTexts`, batches of 64, each text cut to 8000 chars) by the **pg-boss worker** (`src/worker/handlers/index-source.ts`, queues `index-source` / `reindex-all`; also assigns block ids via `ensureBlockIds`). The **query** embedding is computed synchronously in the web process inside `hybridSearch` (task `search`).954- **Hybrid search SQL** (`hybridSearch`, `search/index.ts:59-96`): `k = max(30, limit×4)`.955 - FTS: `select c.id, ts_rank_cd(c.tsv, q) … from chunks c left join pages p …, websearch_to_tsquery('simple', $query) q where … and c.tsv @@ q order by r desc limit k`.956 - Vector (if `semantic !== false` and `OPENAI_API_KEY`): `select c.id, 1 - (e.embedding <=> $v::vector) as s from embeddings e join chunks c … where … and e.model = EMBEDDING_MODEL order by e.embedding <=> $v::vector limit k`.957 - Fusion: **Reciprocal Rank Fusion**, `score += 1/(60 + rank)` per list; a vector-only hit is kept only if cosine ≥ `SEARCH_MIN_SEMANTIC = 0.33`.958 - Filters: workspace, `pageIds`, `sourceTypes`, `kinds`, `section`, `since/until`, archived excluded by default, trashed pages excluded. Snippets via `ts_headline` with control-char markers `HL_START/HL_END`. `group` mode collapses per source and appends `searchReferences` (ILIKE/trigram on `references`).959- **Chat retrieval** (`chat.ts:193-210`): `RETRIEVAL = {candidates:12, keep:8, excerptChars:600, minSemantic:0.3, pageChars:40 000, history:20, outputTokens:8000}`. Evidence gate `isSupporting` = `keyword > 0 || semantic ≥ 0.30`. Optional LLM re-rank `rerank()` (route `judge`, JSON schema, 2.5 s budget, only when > 4 supporting hits; usage task `rerank`). In `notes`/`draft` with zero supporting sources **no answering provider is called** (fixed "No supporting … found" message, `evidence:"none"`). Model can emit the `[[INSUFFICIENT]]` sentinel (`prompts.ts:38`).960- Prompt assembly: `<workspace kind="current-page" …>`, `<workspace kind="retrieved-sources">` with `[n] "title" › heading\nexcerpt`, optional `<workspace kind="reference-list">`; system prompt `MODE_SYSTEMS[mode]` (`prompts.ts:41-48`).961962#### Citations963- Structure `SourceRef` (`chat.ts:50`): `{ n, chunkId, pageId|null, title, icon, heading, blockId|null, excerpt (≤600 chars), score (RRF), semantic (cosine, 3 dp), sourceType }`. Persisted in `ai_messages.sources` (the schema's `$type` at `schema.ts:210` lists only `chunkId, pageId, title, excerpt, score` — stale type; actual rows carry the full `SourceRef`).964- Granularity: **chunk-level with one block anchor** (first block of the chunk), not per-sentence/per-block spans; attachment chunks have `blockId: null`.965- Validation: `validateCitations()` (`chat.ts:72`) keeps `[n]` markers in the retrieved set, normalises `[1, 3]` → `[1][3]`, strips unknown ones (reported in `invalidCitations`), skips code spans; `done.sources` = only cited sources; `uncited` flag when none cited.966- Rendering: `src/components/markdown.tsx:10-14` rewrites `[n]` to `#cite-n` links → `<button class="source-ref">`; below the answer, chips `cite-chip` "n · icon title › heading" (`Chat.tsx:248`); weak (below-threshold) passages listed with `~` (l.251). Click → `openSource` (`Chat.tsx:109`): same page → `document.getElementById("block-<blockId>")`, scroll + 2 s highlight; other page → `router.push("/p/<pageId>#block-<blockId>")`; a file without a page → toast only. Block DOM ids come from the `BlockId` extension (`src/components/editor/extensions/nodes.ts:40-45`, `id="block-<id>"`).967- Answer actions: Copy, Insert into the page (`onInsert`), Save as a new note (`POST /api/pages`), in `actions` mode "Create N tasks" (`POST /api/tasks`, with Undo) / "Insert as checklist".968969#### `/api/ai/chat/plan`970See table in (a). `planChat()` (`chat.ts:121`) returns `ChatPlan {configured:{openai,anthropic,any}, mode, task, provider, model, reason, estTokens, estCostUsd, expensive, budget:{budgetUsd,spentUsd,over}, budgetNote, sends:{pageChars,maxExcerpts,embeddedBy,rankedBy}, error}`; `planAction()` (l.276) returns `ActionPlan`. Used by `Chat.tsx:63` (disclosure line "Send = … → provider · model") and `AIInline.tsx:46`. It is a routing/cost dry run — **not** a multi-step task planner.971972### Compare / vote973- API in (a). Table `ai_comparisons` (`schema.ts:227`: `prompt`, `models` jsonb, `results` jsonb). Votes in settings `compare.votes`. UI `/ai/compare` (`src/components/ai/compare/*`): 2–6 models, presets, per-model completion (no token streaming), "unique sentences" highlight (`diff.ts`: word-set overlap < 0.4 vs other answers — not a textual diff), "Mark as best", history list, save result as note. Bypasses the router, the expensive-confirmation gate and the daily budget (only `recordUsage` task `compare`).974975### Usage / cost accounting976- Table `ai_usage` (`schema.ts:222`): `workspaceId?, provider, model, task, inputTokens, outputTokens, cachedTokens, reasoningTokens, costUsd, latencyMs, ttftMs, ok, error, refId, createdAt`. Written by `recordUsage()` (`src/lib/ai/usage.ts:4`) on every call (also failures/aborts). Task labels seen: TaskClass names (chat), `rerank`, `action:<id>`, `compare`, `agent:<id>`, `research:<feature>`, `ai_property`, `embed`, `search`; others in `src/lib/graph/suggest.ts` and `src/app/api/dashboard/summary/route.ts` **[unverified]**.977- Cost: `costUsd(model, usage)` (`catalog.ts:17`) = `((input − cached)×inputPrice + cached×(cachedPrice ?? inputPrice) + output×outputPrice) / 1e6`; null when prices are missing. Embeddings: hard-coded `0.02`/1M. Per-message cost also on `ai_messages.costUsd`; per-run on `ai_executions.costUsd/tokensIn/tokensOut`.978- Budget: one setting `ai.daily_budget_usd` (default 20), rolling 24 h sum over **all** `ai_usage` (not per workspace). Effect: downgrade only (`deep→reason`, `reason→chat`) — **no hard stop**. `budgetStatus()` / `budgetMessage()` surface it.979- Expensive gate (`usage.ts:17-23`): classes `deep, reason, longctx` or est. input > 30 000 tokens require `confirmExpensive` (chat: `needs_confirmation` event; action: 409 `confirm_expensive`). Not applied to research AI, compare, agents (agents have `confirmCost` + per-run caps).980- UI: `/ai/models` `UsagePanel` (spend by day, by task, by model, recent errors, budget input) and Settings → AI (`AISection.tsx`: default task mode pref `aiDefaultTask`, `aiAlwaysCite`, daily budget, routes table). Per-model/monthly/token limits: absent.981982### Other AI consumers (outside the listed dirs)983- `src/worker/handlers/ai-property.ts`: database property type `ai` — route `quick`, `{{Property}}` templating, result written to `database_rows.values`.984- `src/worker/handlers/suggest-links.ts` → `src/lib/graph/suggest.ts` (embedding neighbours + LLM judgement → `knowledge_edges` `ai_suggested`) **[unverified beyond imports]**.985- `src/app/api/dashboard/summary/route.ts`: weekly digest (see gap list) **[only header lines read]**.986987---988989## (c) Agents990991- Model: **fixed catalogue of bounded agents that only propose** (`src/lib/agents/types.ts:1-6`). No free-form prompt, no search tool, no network, no shell.992- Definitions (`AGENTS`, `src/lib/agents/definitions.ts:14-43`), exactly 4:993994| id | name | page | description | tools | limits (overrides of default) |995|---|---|---|---|---|---|996| `sort_inbox` | Sort my inbox | none | "For each item waiting in the inbox, proposes where to file it, which tags to add, or to archive it." | list_inbox, list_tags, read_page, propose_inbox_triage | maxToolCalls 40 |997| `suggest_tags` | Suggest tags | optional | "Proposes tags for the open page, or for your recent untagged pages when no page is chosen." | list_tags, list_candidate_pages, read_page, propose_tags | maxToolCalls 20 |998| `plan_from_page` | Make a plan from this page | required | "Turns the chosen page into a concrete plan: a new sub-page with a checklist, or a checklist added at the end of the page." | read_page, propose_new_page, propose_page_append | maxToolCalls 6, maxTurns 5, maxProposals 2 |999| `check_references` | Check references | none | "Flags references with missing fields or a DOI that is malformed or was never verified, and proposes a fix-up task for each." | list_references, propose_task | maxToolCalls 32, maxTurns 6 |10001001 Default `limits()` (l.11): `maxToolCalls 30, maxTurns 8, maxTokens 120 000, maxCostUsd 0.75, timeoutMs 300 000, maxProposals 30`. Scopes type `Scope` (`types.ts:8`). `AgentInfo` (no prompts) is what the browser gets.1002- Tool allow-list (`TOOLS`, `src/lib/agents/tools.ts:180`), 10 tools:1003 - Read (`proposal:false`): `list_inbox` (≤25 items, 400-char excerpts), `list_tags` (≤300), `list_candidate_pages` (context page or 12 recent untagged pages), `read_page` (only ids in `ctx.readable`; default 12 000 chars, `offset`/`maxChars ≤ 24 000`), `list_references` (≤30 flagged of ≤500, deterministic `referenceIssues()` l.100).1004 - "Write" (`proposal:true`) — all only append a `Proposal` to the run, **none writes to the workspace**: `propose_inbox_triage` (keep+section / archive / trash, ≤3 tags; `destructive` when trash), `propose_tags` (1–4 tags), `propose_new_page` (one per run, Markdown ≤20 000, child of the context page), `propose_page_append` (one per run, Markdown ≤12 000, append only), `propose_task`.1005 - Inputs zod-validated; JSON schema generated with `z.toJSONSchema` (`toolDefs`); `logSafe()` strips note text from the execution log.1006- Runner (`src/lib/agents/runner.ts`, executed by the worker `src/worker/handlers/agent.ts`, queue `agent-run`, `retryLimit 0`, queue timeout 840 s):1007 - Atomic claim `pending → running` (`runExecution` l.58); `reapStale()` fails abandoned rows (never re-runs).1008 - Model: `route({task:"agent", tools:true, preferred: ex.model})` → default `claude-sonnet-5-5`, fallback `gpt-5.4` (`ex.model` is always inserted as null by the API, so no user model choice).1009 - Loop: `for turn = 1..limits.maxTurns` (default **8** turns), `callAI` non-streaming with `maxTokens 4000`; stops on no tool calls, turn cap, tool-call cap, token cap, cost cap; wall-clock timeout aborts. Tool results are sent back as a **user message** `<workspace kind="tool-results"><tool_result name=…>JSON</tool_result>…` (≤14 000 chars each), the assistant turn is summarised as text `[Called tools: …]` — not native tool-result messages.1010 - Persists progress on the row after each turn (also the cancellation check). End status: `awaiting_approval` (≥1 proposal) or `completed`; failures → `failed` with `proposedChanges: []`.1011- Approval / proposal model:1012 - Storage: **single table `ai_executions`** (`schema.ts:213-221`): `agent, task, model, status, scopes, toolsInvoked[] (ToolStep), documentsAccessed[], proposedChanges[] (Proposal), approvals[{change, approved, at}], outcome, result, error, costUsd, tokensIn, tokensOut, createdAt, startedAt, finishedAt`. No separate proposals table. (While pending, `outcome` holds JSON `{pageId}` — `parseMeta`, `runner.ts:39`.)1013 - Execution statuses: `pending, running, awaiting_approval, approved, rejected, completed, failed`. Proposal statuses (`ProposalStatus`): `proposed, applied, rejected, failed, undone`. Proposal kinds (`ProposalKind`): `inbox_triage, tags, new_page, page_edit, task`. `ProposalBase` (`types.ts:26`): `idx, tool, kind, rationale, title, destructive, status, pageId, before: Preview|null, after: Preview, at, decidedAt?, appliedAt?, undoneAt?, error?, result?, undo?` + typed `payload`. `Preview = {label, lines[], markdown?}` built server-side.1014 - Apply (`src/lib/agents/apply.ts`): `decideProposals(ws, userId, executionId, {approve[], reject[], confirmDestructive?})` — requires status `awaiting_approval`; destructive approvals need `confirmDestructive` (409 `confirm_destructive`); each approved index → `applyOne()`; persisted after every change; `summarise()` sets execution status (`awaiting_approval` while any `proposed`, else `approved` if any applied/undone, else `rejected`). `applyOne` per kind: `tags` (snapshot + union via `setPageTags`), `inbox_triage` (snapshot, tags, `triage()` from `src/lib/inbox/service`), `new_page` (`markdownToDoc` → `createPage` with `properties {ai_generated:true, agent_execution}`), `page_edit` (append nodes via `updatePage` with `baseVersion` and `versionReason "before agent addition"`), `task` (insert into `tasks`). `undoProposals()` → `undoOne()` reverses each (page_edit refuses with 409 `changed_since` if the page version moved). Audit entries `agent.proposal_apply|reject|fail|undo`, `agent.run`, `agent.cancel`.1015- UI (`/ai/agents`, `src/components/ai/agents/*`):1016 - `ProposalCard.tsx`: per proposal — kind icon/label, title, status badge, "Why: rationale", **Before panel stacked above After panel** (`Side`, red/green left border; `lines[]` + optional rendered Markdown, max-h 56), "Open the page concerned" link, Approve / Reject (Approve… in danger style when destructive), Undo when applied. It is a before/after preview, **not a line/word diff**.1017 - `ExecutionDrawer.tsx`: polls `GET /api/agents/<id>` every 2 s while live; status badge, cost, tool-call count, duration, Cancel run; Summary (`result` Markdown); proposal list; collapsible "Execution log" (scope reads/proposes, steps with log-safe input → output, documents accessed as links, approvals, cost/tokens/model/timestamps/run id); sticky "Reject all / Approve all (N)"; second-confirmation dialog for trash.1018 - `ExecutionList.tsx`: rows with status labels (`queued, running, to review, reviewed, done, rejected, failed`).1019 - `AgentsConsole.tsx` (launch cards, scope/cost confirmation): **[not read]**.10201021---10221023## (d) Section 7 — Research workspace10241025### Data1026- `references` table (Drizzle name `researchReferences`, `schema.ts:233-239`): `id, workspaceId, pageId?, citeKey (unique per workspace), entryType (default article), title, authors string[], year, journal, doi, url, bibtex (entry as imported), fields jsonb (every BibTeX tag as written + app data `extracts`), verified (user | doi_verified | ai_suggested), attachmentId? → attachments, createdAt, updatedAt, deletedAt`.1027- `experiments` (`schema.ts:240`): `pageId?, name, status (planned|running|done|failed), params jsonb, metrics jsonb, datasetRef, repoUrl, notes, startedAt, finishedAt`.1028- "Papers" = `pages` with `type:"research"`, `section:"research"`; paper metadata lives in `pages.properties` (`PaperMeta`: `status` in `PAPER_STATUSES` = planning, research, development, writing, submitted, published; `venue, deadline, repoUrl, datasets[], tags[]`). No papers table.1029- Research tasks = rows of the general `tasks` table scoped by `pageId` (`src/lib/research/tasks.ts`).1030- Extracts ("highlights") = `references.fields.extracts[]` (`Extract {id, quote, page, note, createdAt, updatedAt?}`, limits `EXTRACT_LIMITS` quote 8000 / note 4000 / page 40 / 500 per reference; `src/lib/research/cite.ts:4-6`). Manually typed/pasted quotes; **PDF highlighting/annotation: absent**.10311032### Services1033- `src/lib/research/references.ts`: CRUD with soft delete/restore; `uniqueKey` (suffix a…z); **DOI lookup = Crossref** `https://api.crossref.org/works/<doi>` (`lookupDoi` l.133, 15 s timeout, User-Agent with `APP_URL` + optional `CROSSREF_MAILTO`), `TYPE_MAP` to BibTeX types, fields volume/number/pages/publisher/abstract/issn/isbn/keywords; `addByDoi`, `findByDoi`, `verifyReference` (fills missing tags only); `attachPdf`; `importBibtex` (dedupe by DOI then cite key, never overwrites, `dryRun`); extracts CRUD; `referenceLinks`. DataCite / OpenAlex / Semantic Scholar / arXiv / PubMed lookups: absent.1034- `src/lib/research/bibtex.ts`: parser on `bibtex-parse-js` ^0.0.24 with per-entry splitting (`splitEntries`), `@string` macro expansion, LaTeX accent cleanup (`cleanLatex`), `makeCiteKey` (`lastnameYEARfirstword`), `formatBibtex` (deterministic tag order), `fieldsOf`, `referenceBibtex`, `bibFile`, `shortCite`. A second Next-free importer exists at `src/lib/import/bibtex.ts` (used by the general import pipeline) **[header only read]**.1035- `src/lib/research/cite.ts` — citation formats: `shortLabel` (author–year "Gorton & Metrick (2019)"), `formatCitation` (**one style: APA-like plain text**, handles book / incollection / inproceedings / theses / article, DOI link), `extractMarkdown` (block quote + `— (citeKey, p. N)`). CSL / Chicago / MLA / RIS / style picker: absent.1036- `src/lib/research/template.ts`: `workingPaperTemplate` (callout, Abstract, 1–6 sections, a `blockMath` example, References note).1037- `src/lib/research/overview.ts`: `researchOverview` (counts of refs / experiments / open & overdue tasks / attachments per research page).1038- Files/PDF: generic attachments (`attachments.extractedText`, `pageCount`); upload via `POST /api/attachments`, open via `/api/files/<id>` in the browser's native viewer; text extraction (deps `pdf-parse` ^2.4.5, `mammoth` ^1.13.0) and indexing as `sourceType:"attachment"` chunks. **Embedded PDF viewer: absent** (`src/components/research/FileActions.tsx` comment l.2-6). Extraction code itself (`src/app/api/attachments`, `src/lib/files`) **[not read]**.10391040### AI features — `src/lib/research/ai.ts` (`RESEARCH_FEATURES`, l.21), 71041| feature | task class | what it does | output |1042|---|---|---|---|1043| `synthesis` | deep | Literature synthesis from page (≤12 000 chars), page references (+abstracts), attached PDF text (≤12 000 chars each), 12 retrieved passages scoped to the page; asks for `\cite{key}` citations and a reference table | streamed Markdown |1044| `compare` | reason | Methodology comparison of ≥2 references/files (PDF text ≤18 000 chars each) | streamed Markdown (table + discussion) |1045| `question` | reason | Research-question refinement (page ≤15 000, refs, 6 passages) | streamed Markdown |1046| `equation` | reason | Explain a LaTeX equation using page notation | streamed Markdown |1047| `draft` | write | Edit a section to journal standard, cite only listed keys, then "Changes" list | streamed Markdown |1048| `refcheck` | reason (JSON schema) | Deterministic extraction of `\cite{…}` (incl. citep/citet/parencite/textcite/autocite…) and Author (Year) strings (`extractCitations`, ≤80), deterministic match (`matchDeterministic`), LLM adjudication of the rest (unknown keys rejected) | one Markdown report + `result {items: CitationHit[], matched, unverified, found}` |1049| `plan` | deep (JSON schema) | Experimental plan `PlanData {title, objective, hypotheses[], design, data[], steps[], tasks[{title, priority, dueInDays}], risks[]}` | Markdown + `result` |10501051Nothing is written by these; usage task `research:<feature>`. In-text retrieval markers `[S1]…` are plain text (no clickable citations, no `SourceRef` returned). No result history (component state only).10521053### UI1054- `/research` → `ResearchOverview` (`src/app/(app)/research/page.tsx`): tabs `papers` "Working papers" (status chips, table: Paper, Status, Venue, Deadline, Refs, Exp., Open tasks, Words, Last activity; "New working paper") and `library` "Library" (all references; Add to the library; "Library .bib").1055- `/p/[id]` when `page.type === "research"` → `ResearchPanel` (`src/components/research/ResearchPanel.tsx:62-72`, mounted from `src/components/page/PageView.tsx:112`). Tabs: `references` "References", `files` "Files", `experiments` "Experiments", `tasks` "Tasks", `ai` "AI". Header: status select, venue, deadline, Git repository, datasets chips (stored in `page.properties`), `LatexSnippets`, link to Overview.1056 - References tab: Add reference (modes `doi` / `bibtex` (file or paste, two-step dry run) / `manual`; `ENTRY_TYPES` article, book, incollection, inproceedings, techreport, phdthesis, mastersthesis, unpublished, misc), filter, copy `\cite{key}`, "Export .bib". `ReferenceSheet`: Copy citation (APA), copy `\cite{…}`, copy BibTeX, Open DOI, Verify (Crossref), Edit, PDF attach/replace/detach, Extracts (add/edit/delete, "Insert as citation" → Markdown block quote into the page, copy), Links (attached page, citing pages, experiments), Delete with undo.1057 - Files tab: multi-upload (`.pdf,.docx,.txt,.md,.csv,.tex,.bib`, 200 MB), Open / Download / Share, "searchable"/"no text" badge.1058 - Experiments tab: name, status, key-value Parameters and Metrics, dataset, repository, notes.1059 - Tasks tab: title, due date, priority, status; overdue counts.1060 - AI tab (`AITab.tsx`): the 7 features above with a language switch (auto/fr/en); result actions "Insert into page", "Save as note" (new page wrapping an `aiBlock`), "Copy"; plan → "Add N tasks" (`POST /api/research/tasks {items}`). No model picker in this tab.10611062### Exports1063- `.bib`: `GET /api/research/references/export.bib` (page or library); also `GET /api/export?format=bib` and `references.bib` inside the workspace ZIP (`src/lib/export/zip.ts:71`; extracts and experiments go to `workspace.json`).1064- "LaTeX snippets" (`src/components/research/LatexSnippets.tsx`): 21 **equation templates** (groups Regression, State space, Expectations, Sums & integrals) inserted as `$$…$$` blocks or copied — not an export.1065- `.tex` / LaTeX document export, CSL/RIS/EndNote export, Word export of a paper: **absent** (`/api/export` formats are `md, zip, json, csv, pdf, html, bib` — `src/app/api/export/route.ts:25`; that route is outside this part and only its header was read).10661067---10681069## (e) Gap list10701071| Capability | Status | Evidence |1072|---|---|---|1073| Ask-my-knowledge with block-level citations | **partial** | Exists: chat mode `notes` with hybrid retrieval, evidence gate, validated `[n]` markers, `SourceRef.blockId`, click-through to `#block-<id>` (`src/lib/ai/chat.ts:50,72,193-263`; `src/components/chat/Chat.tsx:109-117,248`). Limits: anchor = first block of a ~350-token chunk (`src/lib/search/chunk.ts:11-18`), not the exact supporting block/span; attachments have no block anchor; UI cannot scope by pages/tags/section although the API has `scopePageIds`. |1074| Contextual AI actions list | **exists (partial coverage)** | 21 ids in `ACTION_IDS` (`chat.ts:271`), prompts `src/lib/ai/prompts.ts:9-32`; UI offers 8 chips + translate + custom + 3 slash items (`src/components/editor/AIInline.tsx:26-32`, `extensions/slash.tsx:34-36`); 8 ids have no UI entry point found. Static list — not adapted to block type/context. |1075| LaTeX export | **absent** | Export formats `md, zip, json, csv, pdf, html, bib` (`src/app/api/export/route.ts:25`); only `.bib` export (`api/research/references/export.bib/route.ts`) and equation snippet insertion (`LatexSnippets.tsx`). LaTeX is input/rendering only (KaTeX, `blockMath`). |1076| OpenRouter / local providers | **absent** | `Provider = "openai" \| "anthropic"` (`src/lib/ai/types.ts:1`); two SDK clients without `baseURL` (`providers.ts:7-8`); `providerConfigured` binary (`catalog.ts:23`); no `baseURL`/openrouter/mlx string in the repo; `ai_providers` has no URL column (`schema.ts:190`). |1077| Model selector with creator logos | **partial (selector yes, logos/creator no)** | Selectors: `<select>` in `Chat.tsx:148-150`, `compare/ModelPicker.tsx`, `RoutingTable.tsx`, `AISection.tsx`. Only a coloured dot (`providerTone`, `src/components/ai/models/format.ts:15`); no logo asset, no creator/vendor column in `ai_models` (`schema.ts:193-201`). |1078| Voice notes / transcription | **partial (recording + browser dictation; no server transcription)** | `src/components/shell/CaptureSheet.tsx:27-54`: `MediaRecorder` voice memo saved as an attachment (`captureKind "audio"`, `src/lib/inbox/service.ts:13,40`) and Web Speech API dictation (`SpeechRecognition`). No transcription/Whisper call anywhere (grep `transcri` → only a discovery filter regex in `catalog.ts:39`). CaptureSheet read by excerpt only **[partly unverified]**. |1079| Smart inbox AI suggestions | **partial** | Only via the on-demand agent `sort_inbox` (proposals to file/tag/archive/trash, `src/lib/agents/definitions.ts:16-21`, applied through `triage()` in `apply.ts:82-91`). `src/lib/inbox/service.ts` and `src/app/api/inbox` contain no AI call (grep); no automatic/inline suggestions in the inbox UI found. Inbox components not read **[unverified]**. |1080| Daily brief | **absent (weekly digest exists)** | `src/app/api/dashboard/summary/route.ts:10` — on-demand "weekly digest" of the past 7 days, shown by a dashboard widget (`src/components/dashboard/widgets.tsx:123`). No daily/scheduled brief: worker schedules only `backup` and `maintenance` (`src/worker/index.ts:38-39`). Route body beyond the header **[unverified]**. |1081| AI result block in the editor | **exists** | Tiptap node `aiBlock` (`src/components/editor/extensions/nodes.ts:14-19`; attrs `model, prompt, at`; renders `div.ai-block`), produced by `AIInline.apply` (`AIInline.tsx:80`) and research "Save as note"; handled in `src/lib/editor/text.ts`, `src/lib/export/html.ts`. Static provenance wrapper — no regenerate/refresh in place, no stored sources. |1082| Scientific citation block in the editor | **absent** | No citation/reference node in `extensions/nodes.ts` (nodes: Callout, AIBlock, FileBlock, FootnoteRef, Footnote, BlockId; `Editor.tsx:22`). Citations are plain text `\cite{key}` copied from the References tab, or a Markdown block quote from an extract (`cite.ts:48`); the slash item `footnote` merely has keyword "citation" (`slash.tsx:27`). "Citing pages" are found by regex on `plain_text` (`references.ts:224`). |10831084## Not verified / caveats1085- Not read: `src/components/ai/agents/AgentsConsole.tsx`, `benchmarks/scripts/*`, `src/lib/graph/suggest.ts` (imports only), `src/app/api/dashboard/summary/route.ts` (first lines only), `src/lib/import/bibtex.ts` (header), `src/app/api/export/route.ts` (header), `src/components/shell/CaptureSheet.tsx` (excerpts), attachment upload/text-extraction code, inbox UI components, `src/lib/client/api.ts` `sse()` body, docs `ai-*.md` other than the top of `ai-provider-discovery.md`.1086- Desktop entry points to `AIInline` other than slash menu and mobile toolbar (e.g. a bubble menu calling `openAIForSelection(mode)`) were not traced.1087- `benchmarks/results/discovery.json` / `summary.json` are not in the copy, so the actual seeded model list and prices could not be checked; the model ids quoted come from `DEFAULT_ROUTES` and UI presets.1088- Research/models/compare/settings UI details come from a delegated full read of those component files (line numbers as reported by it), not re-checked line by line.1089- No migration SQL was read in full; "no DB enum/check on provider" rests on `schema.ts` plus a grep of `drizzle/*.sql`.10901091---10921093# Part C — Databases, knowledge graph, search, inbox/capture, tasks, tags, dashboard, import/export10941095Source: read-only copy `/tmp/spnote-next/_src` (package.json `"version": "2.0.0"`, next ^16.4.3, drizzle-orm ^0.45.4, pg-boss ^12.38.0, react-force-graph-2d ^1.29.2, papaparse ^5.7.0). Nothing was run, built or installed; `node_modules` is absent from the copy. Paths are relative to the repo root. "absent" means I looked for it and it is not in the code.10961097Verification levels: server side (schema, `src/lib/**`, API routes, worker handlers) was read in full by the author of this part. Component-level sections (marked **[UI]**) and the import/export library section were read in full by delegated readers and are reported from their notes; line numbers there were spot-checked, not all re-read.10981099---11001101## 0. Shared route contract (applies to every route below)11021103Every route file in scope wraps its methods in `handler()` (`src/lib/api.ts:21`). None passes `{ fresh: true }`. **Step-up (`requireFresh`, `src/lib/auth/guard.ts:21`, passkey verified < 5 min, else 403 `reauth_required`) is required only in three cases, all called inline: JSON export, whole-workspace ZIP export, and a JSON-backup import in overwrite mode** (see Import / export below). Every other route in this part needs the session only.11041105- **Auth**: `requireUser(req)` (`src/lib/auth/guard.ts:14`) → 401 `{error:"Sign-in required", code:"unauthenticated"}` without a session.1106- **CSRF**: for non GET/HEAD/OPTIONS, `assertSameOrigin()` (`guard.ts:34`) → 403 code `csrf` (Origin must be in `allowedOrigins()`, or `Sec-Fetch-Site: same-origin`; a request with neither header is refused).1107- **Workspace**: `getWorkspace(user.id)` (`guard.ts:43`) — single owner workspace; every service call is scoped by `ws.id`.1108- **Idempotency**: optional, generic. Any POST/PATCH/PUT/DELETE carrying `Idempotency-Key` (`/^[A-Za-z0-9_-]{8,100}$/`, else 400 `bad_idempotency_key`) is run once per key (`idempotent()`, `api.ts:42`): replay returns the stored JSON with header `idempotent-replay: true`; same key on another method/path → 422 `idempotency_mismatch`; still running (<60 s) → 409 `in_progress`. Only 2xx JSON responses ≤ 512 KB are stored (`MAX_STORED`, `api.ts:11`); errors and non-JSON (CSV/ZIP downloads) are not replayable. No route *requires* the header; `POST /api/capture` documents that clients always send it (`src/app/api/capture/route.ts:7`).1109- **Errors**: `HttpError` → `{error, code?, ...extra}` with its status; **anything else (including `ZodError` from a bare `uuid.parse(...)`, plain `Error`, Postgres errors) → 500 `{error:"Internal error", requestId}`** (`jsonError`, `guard.ts:48-52`). `parseBody()` (`api.ts:67`) converts zod failures into 400 `Validation: …`, but direct `uuid.parse()` calls do not (see defects).1110- Responses get `x-request-id` and default `cache-control: private, no-store` (`api.ts:36`).11111112---11131114## (a) API routes11151116### Databases11171118| Method + path | File | Purpose | Request | Response |1119|---|---|---|---|---|1120| GET `/api/databases` | `src/app/api/databases/route.ts:4` | List databases of the workspace | — | `[{id, pageId, name, icon, isTemplate, templateOf, updatedAt, rowCount}]` ordered by page title (`listDatabases`, `src/lib/databases/service.ts:33`) |1121| GET `/api/databases/templates` | `src/app/api/databases/templates/route.ts:3` | Built-in template catalogue | — | `[{id, name, icon, description, properties: string[]}]` (`templateSummaries`, `src/lib/databases/templates.ts:58`) |1122| GET `/api/databases/[id]` | `src/app/api/databases/[id]/route.ts:7` | Database definition. `[id]` = database id **or host page id**; lazily creates the database for a page of `type="database"` (`ensureDatabase`, `service.ts:16`) | query `format=csv`, `viewId` | JSON `Database` `{id, pageId, name, description, icon, isTemplate, templateOf, properties: Property[], views: View[]}` (`src/lib/databases/types.ts:40`); or `text/csv` attachment `<name>.csv` (view filter/sorts applied, hidden columns removed, **max 1000 rows**) |1123| PATCH `/api/databases/[id]` | same `:15` | Rename / describe / icon / template flag | zod `{name?: string≤500, description?: string≤2000\|null, icon?: string≤16\|null, isTemplate?: boolean}` | `Database`; audit `database.update` |1124| GET `/api/databases/[id]/rows` | `src/app/api/databases/[id]/rows/route.ts:7` | Row refs for relation pickers, or hydrated rows | query `q`, `limit` (≤500, default 50), `full=1`, `offset` | default: `RelationRef[]` `{rowId, pageId, title, icon}` (`listRowsSimple`, `service.ts:176`); `full=1`: `QueryResult` `{rows: RowOut[], total, limit, offset}` |1125| POST `/api/databases/[id]/rows` | same `:13` | Create one row or up to 500 | zod union: `{rows: Row[]≤500}` or strict `Row` = `{title?: string≤500, values?: Record<string, unknown>, position?: number, content?: any, icon?: string≤16\|null}` | 201 `RowOut` or `RowOut[]`; audit `database.row.create` |1126| PATCH `/api/databases/[id]/rows` | same `:21` | Update one / many rows, or reorder | zod `{id?: uuid, values?: Record, updates?: [{id: uuid, values}]≤500, order?: uuid[]≤5000}`; precedence `order` > `updates` > `id` (400 "id required" if none) | `{ok:true}` / `RowOut[]` / `RowOut` |1127| DELETE `/api/databases/[id]/rows` | same `:29` | Soft-delete rows (row + its page) | query `id=` and/or `ids=a,b,c` (bare `uuid.parse`) | `{ok:true, deleted:n}`; audit `database.row.delete` |1128| POST `/api/databases/[id]/rows/restore` | `src/app/api/databases/[id]/rows/restore/route.ts:13` | Undo a row deletion (row + page restored) | zod `{ids: uuid[] 1..500}` | `{ok:true, restored:n}`; audit `database.row.restore` |1129| POST `/api/databases/[id]/query` | `src/app/api/databases/[id]/query/route.ts:8` | Filter / sort / search / paginate | zod `{viewId?: uuid, filter?: Group\|null, sorts?: [{property: string, dir: "asc"\|"desc"}], search?: string≤200, limit?: int 1..1000, offset?: int≥0}`; `Group = {and?: (Rule\|Group)[], or?: (Rule\|Group)[]}`, `Rule = {property: string, op: string, value?: unknown}` (**`op` is a free string**, not checked against `FILTER_OPS`). Explicit fields override the view's config. | `QueryResult` |1130| GET `/api/databases/[id]/query` | same `:14` | Same query via a view; CSV export | query `viewId`, `q`, `format=csv` | `QueryResult` (limit 1000) or `text/csv` attachment `<db>-<view>.csv` |1131| POST `/api/databases/[id]/properties` | `src/app/api/databases/[id]/properties/route.ts:7` | Add a property | zod `{name: string≤200, type: enum PROPERTY_TYPES, config?: Record, width?: int 40..2000\|null, position?: number}` (type `title` refused 400, `service.ts:57`) | 201 `Property`; audit `database.property.create` |1132| PATCH `/api/databases/[id]/properties` | same `:13` | Update one property or reorder | zod `{id?: uuid, name?, type?, config?, width?, position?, order?: uuid[]}` | `Property` or `{ok:true}` |1133| DELETE `/api/databases/[id]/properties` | same `:19` | Delete property (+ its key in every row's `values`, + relation pair) | query `id` (bare `uuid.parse`) | `{ok:true}`; audit `database.property.delete` |1134| GET `/api/databases/[id]/views` | `src/app/api/databases/[id]/views/route.ts:6` | List views | — | `View[]` |1135| POST `…/views` | same `:7` | Create view | zod `{name: string≤200, type: enum VIEW_TYPES, config?: Record}` | 201 `View` |1136| PATCH `…/views` | same `:8` | Update view (config is shallow-merged, `service.ts:198`) | zod `{id: uuid, name?, type?, config?, position?, isDefault?}` | `View` |1137| DELETE `…/views` | same `:9` | Delete view (400 if it is the last one, `service.ts:203`) | query `id` (bare `uuid.parse`) | `{ok:true}` |1138| POST `/api/databases/[id]/duplicate` | `src/app/api/databases/[id]/duplicate/route.ts:5` | Duplicate a database | zod `{withRows?: boolean, title?: string≤500, asTemplate?: boolean, parentId?: uuid\|null}` | 201 `Database`; audit `database.duplicate` |1139| POST `/api/databases/[id]/apply-template` | `src/app/api/databases/[id]/apply-template/route.ts:5` | Apply a built-in template onto a database | zod `{template: string≤50}` | `{applied, properties:n}`; audit `database.template` |1140| POST `/api/databases/[id]/import` | `src/app/api/databases/[id]/import/route.ts:5` | CSV → rows (synchronous) | multipart `file` (≤ 20 MB, else 413) | 201 `{created:n, properties: string[]}` (names of auto-created text columns); audit `database.import` |1141| POST `/api/databases/[id]/ai` | `src/app/api/databases/[id]/ai/route.ts:5` | Queue AI-property computation | zod `{rowId?: uuid, rowIds?: uuid[]≤200, propertyId: uuid}` | `{ok:true, queued:n}` (n = ids requested, not jobs actually accepted) |11421143Auth for all: session + same-origin on mutations; no step-up; idempotency optional.11441145### Graph11461147| Method + path | File | Purpose | Request | Response |1148|---|---|---|---|---|1149| GET `/api/graph` | `src/app/api/graph/route.ts:6` | Whole or neighbourhood graph | query `types=page,database,research,row,attachment,tag` (unknown values dropped), `section`, `since` (ISO date → `pages.updated_at >=`), `pageId`+`depth` (clamped 1..4), `hideOrphans=1`, `suggestions=0` | `GraphData` `{nodes: GraphNode[], edges: GraphEdge[], center, stats:{pages, edges, suggested}}` |1150| GET `/api/graph/neighbors` | `src/app/api/graph/neighbors/route.ts:5` | Mini graph around one page | query `pageId` (uuid, 400 otherwise), `depth` (1..3), `suggestions=0`, `linked=1` | `GraphData`; with `linked=1` also `linked: [{id,title,icon,kind:"link"\|"relation",direction:"out"\|"in"}]` and `suggestions: Suggestion[]` (pending, ≤50) |1151| GET `/api/graph/suggestions` | `src/app/api/graph/suggestions/route.ts:9` | AI link suggestions queue | query `status=suggested\|accepted\|rejected` (default suggested), `pageId` | `{items: Suggestion[]≤200, counts:{suggested,accepted,rejected}}` |1152| POST `/api/graph/suggestions` | same `:18` | Produce suggestions | zod `{action:"suggest", pageId?: uuid\|null, inline?: boolean}` | inline+pageId: `{queued:false, result: SuggestResult}` (synchronous model call); else `{queued:true, jobId}` on queue `suggest-links` (singleton 30 s per page / 300 s per workspace); audit `graph.suggest` |1153| PATCH `/api/graph/suggestions` | same `:31` | Accept / reject / undo | zod `{id: uuid, status:"accepted"\|"rejected"\|"suggested"}` | `{ok, id, status, fromId, toId}`; audit `graph.suggestion.<status>` / `.reverted` |1154| GET `/api/graph/backlinks` | `src/app/api/graph/backlinks/route.ts:7` | Backlink ranking + unlinked mentions | query `q` (title filter), `pageId` (scopes mentions) | `{pages: BacklinkRow[]≤300, mentions: UnlinkedMention[]≤400}` |1155| POST `/api/graph/backlinks` | same `:14` | Turn a mention into a link, or dismiss / undismiss it | zod `{action:"link"\|"dismiss"\|"undismiss", fromId: uuid, toId: uuid}` | `{ok:true}`; audit `backlinks.link` only |11561157### Search11581159| Method + path | File | Purpose | Request | Response |1160|---|---|---|---|---|1161| GET `/api/search` | `src/app/api/search/route.ts:8` | Quick title search or hybrid passage search | query `q` (cut to 500), `mode=quick\|hybrid` (default quick; anything not `quick` is hybrid), `limit` (1..60; default 10 quick / 20 hybrid), `semantic=0`, `kinds=page,entry,file,reference`, legacy `types=page,attachment`, `section` ∈ notes/documents/projects/research/databases (else 400), `since`/`until` `YYYY-MM-DD` (else 400), `archived=1`, `group=1` | quick: `[{id,title,icon,type,section,updatedAt}]`; hybrid: `Hit[]` (shape in section (d)); hybrid with `q` < 2 chars → `[]` |1162| GET `/api/search/index-state` | `src/app/api/search/index-state/route.ts:7` | Index counters + health | — | `{pages, attachments, chunks, embedded, indexed_sources, last_indexed, stale, model, version, health:{pending:[{id,title,icon,updatedAt}]≤50, pendingFiles, failed:[{id,sourceId,sourceType,error,at}], queued, embeddingsConfigured}}` |1163| POST `/api/search/index-state` | same `:12` | Re-index | zod `{scope: "all"\|"pending"}` default `all` | `{ok:true, queued:n}`; `all` → one `reindex-all` job (singleton 120 s); `pending` → one `index-source` job per pending page / failed attachment; audit `search.reindex` |1164| GET `/api/search/duplicates` | `src/app/api/search/duplicates/route.ts:3` | Near-duplicate pages | query `threshold` (default 0.93, unvalidated `Number()`) | `[{a:{id,title,icon,wordCount,updatedAt}, b:{…}, similarity}]` ≤ 50 |11651166### Capture / inbox11671168| Method + path | File | Purpose | Request | Response |1169|---|---|---|---|---|1170| POST `/api/capture` | `src/app/api/capture/route.ts:9` | Create one inbox page | zod `{kind: "note"\|"tasks"\|"link"\|"photo"\|"file"\|"audio", text?: string≤100000, url?: string≤2000, attachmentIds?: uuid[]≤20}` | 201 `{id, title, kind, inboxAt}`; audit `inbox.capture`. Idempotency-Key expected from clients (comment `:7`), not enforced |1171| GET `/api/inbox` | `src/app/api/inbox/route.ts:4` | Captures awaiting triage (newest first, ≤300) | query `count=1` | `{count}` or `[{id, title, icon, kind, inboxAt, updatedAt, wordCount, excerpt, tags: string[], url, image, openTasks}]` |1172| POST `/api/inbox/[id]` | `src/app/api/inbox/[id]/route.ts:12` | Triage one capture | zod discriminated union on `action`: `keep {section?: notes\|documents\|projects\|research, parentId?: uuid\|null}` · `task {dueAt?: ISO datetime with offset\|null, priority?: low\|medium\|high}` · `database {databaseId: uuid}` · `reference` · `archive` · `trash` | `{actionId, summary, created}`; 409 `not_in_inbox` if already triaged; audit `inbox.<action>` |1173| POST `/api/inbox/undo` | `src/app/api/inbox/undo/route.ts:5` | Reverse a triage action | zod `{actionId: uuid}` | `{ok:true, already: boolean}`; audit `inbox.undo` |11741175### Tasks / tags11761177| Method + path | File | Purpose | Request | Response |1178|---|---|---|---|---|1179| GET `/api/tasks` | `src/app/api/tasks/route.ts:12` | Open tasks (+ done with `done=1`), ≤500 | query `done=1` | `[{id,title,status,priority,dueAt,completedAt,project,pageId,createdAt,updatedAt,pageTitle,pageIcon}]`, ordered open first, `due_at asc nulls last`, `created_at desc` |1180| POST `/api/tasks` | same `:19` | Create task | zod `{title: 1..500 (trimmed), dueAt?: ISO datetime with offset\|null, priority?: low\|medium\|high, project?: string≤100\|null, pageId?: uuid\|null}` | 201 task row; audit `task.create` |1181| PATCH `/api/tasks` | same `:25` | Update / complete / reopen | zod `{id: uuid, title?, dueAt?, priority?, project?, pageId?, status?: "todo"\|"done"}` | task row; 404 if missing; audit `task.complete` / `task.reopen` |1182| DELETE `/api/tasks` | same `:40` | Soft delete | query `id` (bare `uuid.parse`) | `{ok:true}`; audit `task.delete` |1183| GET `/api/tags` | `src/app/api/tags/route.ts:3` | All tags with page counts | — | `[{id, name, color, pages}]` ordered by name (`listTags`, `src/lib/inbox/service.ts:106`) — **`pages` is always 0, see section (e) and defect D1** |11841185(Out of the listed directories but relevant: `GET/PUT /api/pages/[id]/tags` in `src/app/api/pages/[id]/tags/route.ts:5,7` reads/replaces a page's tags — body `{tags: string[]≤20, each ≤60}`; this is the only HTTP writer of tags.)11861187### Dashboard11881189| Method + path | File | Purpose | Request | Response |1190|---|---|---|---|---|1191| GET `/api/dashboard` | `src/app/api/dashboard/route.ts:21` | Everything the home widgets need | — | `{layout:{widgets, custom}, user:{displayName, username, preferences}, recent[8], pinned[8], modified[8], projects[8], tasks[≤30 with pageTitle], research:{pages[5], experiments[5], counts:{references, experiments, researchPages}}, graph:{top[≤6 with degree], suggested, edges}, usage:{today:{calls, costUsd, errors}, budgetUsd}, summary, stats:{editedThisWeek}}` |1192| PUT `/api/dashboard` | same `:63` | Save or reset the layout | zod `{widgets?: Widget[]≤40, reset?: boolean}`; `Widget = {id: 1..64, type: enum WIDGET_TYPES, size: "half"\|"wide"\|"third", config?: Record}` | `{widgets, custom}`; audit `dashboard.layout` / `dashboard.reset` |1193| GET `/api/dashboard/summary` | `src/app/api/dashboard/summary/route.ts:13` | Last stored weekly summary | — | settings value `dashboard.summary` or `null` |1194| POST `/api/dashboard/summary` | same `:16` | Generate the weekly AI summary (synchronous model call) | no body | `{text, model, provider, costUsd, latencyMs, at, pages, tasks, reason}`; 400 if nothing edited and no open tasks; 502 on model failure; audit `dashboard.summary` |11951196### Import / export11971198Both files use `handler()` without `{fresh}`, and call `requireFresh(user)` **inline, conditionally** — these are the only step-up requirements in this part. No rate limiting on either route. Reported from the delegated read (**[delegated]**).11991200**`/api/import`** — `src/app/api/import/route.ts` (`maxDuration = 300`, `:24`)12011202| Method | Mode | Request | Response | Step-up |1203|---|---|---|---|---|1204| POST | upload with preview (`:92-110`) | multipart: `file` (required), `kind?` ∈ `KINDS`, `parentId?` (uuid of a live page), `preview=1` (query or field). Caps: 500 MB upload (`LIMITS.upload`, `src/lib/import/validate.ts:23`) + per-kind caps | 200 `{jobId, kind, inline, preview}`; nothing created, file staged, record status `preview` | none |1205| POST | confirm a preview (`:81-90`), `content-type: application/json` | zod `{jobId: uuid, duplicates?: "skip"\|"import"}` | `done` → 200 `{done:true, jobId, pageId, stats}`; `queued/running` → 202 `{jobId, kind}`; `preview` → starts (201 or 202) | `requireFresh` when kind `json` **and** `duplicates="import"` (`:88`) |1206| POST | one-step upload (`:111-113`) | multipart as above + `duplicates`, `sync=1` (force in-request run) | inline run → 201 `{done:true, jobId, kind, pageId, title?, stats, detail?}`; queued → 202 `{jobId, kind}` | same rule (`:111`) |1207| POST | retry (`:71-79`) | query `retry=<jobId>` | as confirm; 409 `in_progress` unless failed / cancelled / stalled; 410 `missing_upload` | same rule (`:76`) |1208| GET | job status / list (`:116-121`) | query `jobId?` | one `publicJob` (`status, progress, done, total, message, kind, filename, size, pageId, stats, error, errorCode, retryable, attempts, maxAttempts, duplicates, parentId, createdAt, updatedAt, …, stalled, canRetry`) or `{jobs:[≤10]}` | none |1209| DELETE | cancel or dismiss (`:123-134`) | query `jobId` | `{ok:true, cancelled:true}` (queued/running; already-created items are kept) or `{ok:true, removed:true}` | none |12101211Inline vs queued: `(kind ∈ {markdown, csv, bibtex, docx, pdf} && size ≤ 2 MB) || sync` runs in the request (`start()`, `:33-61`; `INLINE_KINDS`, `INLINE_MAX`, `src/lib/import/index.ts:14-15`); otherwise pg-boss `import-file`. Error codes: `file_too_large` 413, `bad_upload` 400, `unsupported_type` 415, `type_mismatch` 415, `empty_file` 400, `bad_json` 400, `bad_zip`, `zip64`, `zip_too_many_entries`, `zip_unsafe_path`, `zip_encrypted`, `zip_entry_too_large`, `zip_too_large`, `zip_bomb`, `bad_backup` 400, `bibtex_parse` 400, `cancelled` 409, `missing_upload` 410, `queue_unavailable` 503, `import_failed` 500. Idempotency: optional; note the key is bound to method + path only, so one key reused for the preview POST and the confirm POST replays the preview (`src/lib/api.ts:25,46`).12121213**`/api/export`** — `src/app/api/export/route.ts` (GET only, `:29`)12141215Query: `format` ∈ `FORMATS = md | zip | json | csv | pdf | html | bib` (default `zip`, `:25,30`), `pageId`, `databaseId` (both `uuid.safeParse` → 400), `check=1` (no export; `{ok:true, format, fresh}` — used to pre-flight the step-up), `versions=0` (JSON without page versions), `print=1` (auto-print script).12161217| `format` | Scope | Response | Step-up |1218|---|---|---|---|1219| `zip` | workspace, or subtree with `pageId` | `application/zip` stream, `<slug>-<date>.zip`, header `x-export-report` | `requireFresh` **when no `pageId`** (`sensitive()`, `:27,35-36`) |1220| `json` | workspace only | `application/json`, `<slug>-backup-<date>.json` | `requireFresh` always |1221| `md` | one page (`pageId` required) | `text/markdown`, `<slug>.md` | none |1222| `csv` | one database (`databaseId` or its `pageId`) | `text/csv` with BOM | none |1223| `bib` | all references, or those of a subtree | `application/x-bibtex`, `references.bib` | none (also for the whole workspace) |1224| `html` / `pdf` | one page | **`text/html`** print page, inline | none |12251226Errors: 413 `export_too_large` (ZIP > `EXPORT_MAX_BYTES` or 500 MB), 404 `not_found`.12271228---12291230## (b) Section 6 — Databases12311232### Data model12331234- `databases` (`src/db/schema.ts:101`): `id, workspaceId, pageId` (the host page, `pages.type="database"`), `name, description, icon, isTemplate, templateOf, createdAt, updatedAt, deletedAt`. Name/icon are mirrored from the host page on every read (`getDatabase`, `service.ts:30`).1235- `database_properties` (`schema.ts:107`): `id, databaseId, name, type` (text column, no DB enum/check), `config jsonb`, `position int`, `width int default 180`.1236- `database_rows` (`schema.ts:113`): `id, databaseId, pageId` (every row owns a page), `values jsonb` keyed by **property id**, `position double`, timestamps, `deletedAt`. GIN index `db_rows_values_idx` on `values`.1237- `database_views` (`schema.ts:119`): `id, databaseId, name, type` (default `"table"`), `config jsonb`, `position`, `isDefault`.1238- `relations` (`schema.ts:124`, exported as `relationsTable`): `id, propertyId, fromRowId, toRowId, createdAt`; unique `(propertyId, fromRowId, toRowId)`, index on `toRowId`.1239- Row page: `pages.databaseId` is set for row pages (`schema.ts:79`); row pages are created with `parentId = host page`, `section="databases"`, `type="page"` (`createRow`, `service.ts:146`). The title cell is `pages.title` (also copied into `values[titlePropId]`, `service.ts:135,145`).1240- Cell values: plain JSON in `database_rows.values`, except title (page title), relations (`relations` table), and computed types (never stored — `COMPUTED_TYPES`, `types.ts:4`; `splitValues` skips them, `service.ts:137`). Empty values (`null`, `""`, `[]`) are removed from the JSON (`service.ts:138`). There is no per-cell table and no cell history.12411242### Property types — exact enum and value shape12431244`PROPERTY_TYPES` (`src/lib/databases/types.ts:2`): `"title", "text", "number", "date", "checkbox", "status", "select", "multi_select", "url", "file", "relation", "rollup", "formula", "created_time", "last_edited_time", "ai"`. `READONLY_TYPES` (`types.ts:5`) = rollup, formula, created_time, last_edited_time, ai.12451246| Type | Stored / returned value | Config keys (`PropertyConfig`, `types.ts:15`) |1247|---|---|---|1248| title | string = `pages.title` (hydrated `query.ts:39`); exactly one per database, cannot be created, retyped or deleted (`service.ts:57,77,98`) | — |1249| text, url | string | — |1250| number | number (strings parsed by `numberOf`, `values.ts:10`) | `format`: `"number" \| "integer" \| "percent" \| "currency"` (currency is hard-coded CAD / en-CA, `values.ts:17`) |1251| date | string, `YYYY-MM-DD` or ISO datetime (`parseValue`, `values.ts:85`); single date only — **no date range / end date in the value** | `includeTime` |1252| checkbox | boolean | — |1253| select, status | option **id** (string) | `options: SelectOption[]` `{id, name, color, group?}`; colors `OPTION_COLORS` (`types.ts:10`); status groups `todo \| in_progress \| done`; default status options `DEFAULT_STATUS` (`service.ts:13`) |1254| multi_select | option id[] | `options` |1255| file | `FileRef[]` `{id, url, name, mime?}` (`types.ts:43`) | — |1256| relation | not in `values`; returned as `RelationRef[]` `{rowId, pageId, title, icon}` (`query.ts:42`); written as an array of row ids or refs (`relIds`, `service.ts:124`) | `targetDatabaseId`, `twoWay`, `syncedPropertyId` (on the reverse side), `single` |1257| rollup | computed: number, string[], ISO date, `null` or `{error}` (`rollup()`, `query.ts:51`) | `relationPropertyId`, `targetPropertyId`, `aggregate` ∈ `AGGREGATES` = count, count_values, sum, avg, min, max, unique, show (`types.ts:8`) |1258| formula | computed: string \| number \| boolean \| null \| `{error}` (`evaluate`, `formula.ts:109`; dates become ISO strings, arrays become a joined string) | `expression` |1259| created_time | ISO string from `database_rows.created_at` (`query.ts:40`) | — |1260| last_edited_time | ISO string, later of row `updated_at` and page `updated_at` (`query.ts:41`) | — |1261| ai | string, or `{pending:true, at}` (`AiPending`, `types.ts:46`) while queued, or `{error}` | `prompt` |12621263Type change: `updateProperty` (`service.ts:69`) converts stored values best-effort (`convertValues`, `service.ts:107`: to text/url/ai, number, checkbox, date, select/status, multi_select; anything else becomes null), one UPDATE per row.12641265### View types12661267Schema / API enum: `VIEW_TYPES` = `"table", "board", "calendar", "gallery", "timeline", "list"` (`types.ts:6`; column comment `schema.ts:121`). `ViewConfig` keys (`types.ts:34`): `filter, sorts, groupBy, hidden, order, widths, dateProperty, startProperty, endProperty, coverProperty, scale ("week"|"month")`. The server does not validate config per view type (`createView`, `service.ts:189`).12681269**Implemented in the UI [UI]: all six.** `VIEWS` map in `src/components/database/DatabaseView.tsx:26`; an unknown type falls back to `TableView` (`:118`). Every view renders the same single result set of `useRows` (one `POST …/query` with hard-coded `limit: 500`, `src/components/database/hooks.ts:48`).12701271| View | Component | What works | Config keys read | Not there |1272|---|---|---|---|---|1273| table | `src/components/database/views/TableView.tsx:25` (`GridLayout` `:57`; phone default `CardsLayout` `:30`) | inline cell edit, keyboard navigation (`nav`, `:64-70`), column resize saved per view (`widths`, `:71`), row drag-reorder when no group/sort/search (`:62,72`), multi-select + bulk delete (`:77`), optional grouping | `groupBy, widths, sorts, hidden`, phone-only `mobileLayout, mobileProps` | column drag-reorder (`ViewConfig.order` is never read or written), column footer aggregates, select-all, virtualisation |1274| board | `views/BoardView.tsx:22` | columns per option of the group property (+ "No …" column), drag a card between columns (`:39-50`) with Undo toast; on phones drag is off and a "Move to…" list replaces it (`:60,107-111`) | `groupBy` | ordering inside a column, column reorder/collapse, WIP limits, sub-groups |1275| calendar | `views/CalendarView.tsx:16` (phone: `Agenda` `:99`) | month grid (Monday start), prev/today/next, create on a day (only for a `date` property, `:42`) | `dateProperty` only | **drag to reschedule**, week/day views, multi-day spans (`endProperty` not read), "+N more" is plain text (`:85`) |1276| timeline | `views/TimelineView.tsx:21` (phone: `PhoneList` `:126`) | bars from start to end, today line, week/month scale, colour from the first status/select property | `startProperty, endProperty, scale` | **any drag / resize / create on the chart**, grouping, dependencies, configurable colour |1277| gallery | `views/GalleryView.tsx:11` | card grid, cover = first image of the cover file property | `coverProperty` | grouping, card size, page cover |1278| list | `views/ListView.tsx:11` | one line per row, optional grouping | `groupBy` | inline edit, reorder |12791280View management (`src/components/database/ViewTabs.tsx`): create (`:24-31`, with `defaultConfig` `:105`), rename, change layout, per-type settings, make default, delete (native confirm). Duplicate view and reorder views: absent.12811282### Query engine (`src/lib/databases/query.ts`)12831284- **Where it runs: mostly JavaScript, over the whole database.** `queryRows` (`query.ts:128`) loads every non-deleted row of the database with `loadRaw` (`query.ts:11`, one join `database_rows ⨝ pages`, no LIMIT), hydrates all of them (`hydrate`, `query.ts:27`), then filters, searches, sorts and paginates in memory (`query.ts:133-137`). `total` is the in-memory length.1285- **SQL pushdown** (`sqlFilter`, `query.ts:104`): only when *every* rule of the filter tree is "simple"; otherwise the whole filter runs in JS. Pushed: on `title, text, url, select, status, ai` — `empty`, `not_empty`; `eq`/`neq` (select/status by option id; others case-insensitive); `contains, not_contains, starts_with, ends_with` (ILIKE, not for select/status); on `checkbox` — `eq`/`neq`. Any rule on number, date, multi_select, relation, rollup, formula, file, created/last_edited time, or any other operator, disables pushdown for the whole tree. Even with pushdown, sort/search/pagination stay in JS.1286- **Operators** — `FILTER_OPS` (`types.ts:27`): `eq, neq, contains, not_contains, starts_with, ends_with, empty, not_empty, gt, gte, lt, lte, before, after, on`. Semantics in `matchRule` (`query.ts:69`):1287 - `empty` / `not_empty`: any type (`isEmpty`, `values.ts:9`; pending/error AI or formula values count as empty).1288 - `eq` / `neq`: checkbox (true/"true"/1), select/status (option id or name), multi_select (contains the option), relation (row id or exact title), number / numeric formula, date (same calendar day; the literal `"today"` is accepted, `query.ts:67`), otherwise case-insensitive display string.1289 - `contains` / `not_contains`: multi_select (option id/name), relation (row id or title substring), otherwise display-string substring.1290 - `starts_with` / `ends_with`: display string.1291 - `gt, gte, lt, lte`: numeric, or by day for date / created_time / last_edited_time.1292 - `before, after, on`: always by calendar day (server local time, `day()`, `query.ts:68`).1293 - A rule on an unknown property, or with an unknown operator, matches everything (`query.ts:70,95`).1294- **Filter tree**: nested `{and: [...]}` / `{or: [...]}` groups (`matchFilter`, `query.ts:97`); when a group has both keys, `or` wins.1295- **Search** (`query.ts:134`): substring over title and every property's display value, in JS.1296- **Sorts** (`sortRows`, `query.ts:122`): multi-key, in JS; `sortKey` (`values.ts:68`) — numbers, dates by timestamp, checkbox, select/status by option order, else lowercase display string (`localeCompare`); empty last; ties by `position`.1297- **Grouping**: **absent on the server** — `groupBy` is only a view-config key; no grouped response, no per-group counts/aggregates.1298- **Pagination**: `limit` default 200, max 1000; `offset`; applied by `Array.slice` after the full load (`query.ts:136-137`). No cursor.1299- **View resolution**: `filter`/`sorts` default to the view's config when not given (`query/route.ts:11`; helper `resolveView`, `query.ts:139`, is exported but the routes inline the same logic).13001301### Formulas (`src/lib/databases/formula.ts`)13021303- Hand-written lexer + recursive-descent parser + tree-walking evaluator, no `eval` (`lex` `:8`, `parseFormula` `:21`, `evalNode` `:85`); client-safe; parsed ASTs cached (≤500, `:107`); depth limit 200 (`:86`).1304- Grammar (precedence low → high): ternary `c ? a : b` → `||` / `or` → `&&` / `and` → `!` / `not` → comparison `== != <= >= < >` (`=` accepted as `==`; non-associative) → `+ -` → `* / %` → unary `- +` → `^` → primary. Primaries: number (decimals, exponent), string in `"` or `'` with backslash escapes, `true`, `false`, `null`, parenthesised expression, function call `name(args…)`, bare identifier = property reference by name (`{k:"id"}` → `ctx.prop(name)`, `:89`; only names matching `[A-Za-z_][A-Za-z0-9_]*`; others need `prop("Name")`).1305- Value types `FVal` (`:2`): number, string, boolean, Date, null, array. `+` concatenates when either side is a string; Date ± number is in days; Date − Date gives days (`:98-99`). Division by zero throws.1306- Functions (`FNS`, `:63-83`): `prop, if, ifs, empty, not, and, or, concat, join, length, upper, lower, trim, contains, replace, replaceAll, slice, format, toNumber, test, round, floor, ceil, abs, sqrt, pow, mod, min, max, sum, avg, count, now, today, date, year, month, day, weekday, dateDiff, dateAdd, formatDate, dateBetween` (alias of `dateDiff`), `dateSubtract`. Units for date functions: ms, seconds, minutes, hours, days, weeks (`UNIT`, `:58`) plus months, years.1307- Property references resolve by **name**; formula-to-formula references are evaluated recursively with a per-row memo and a cycle check (`query.ts:46`). Errors return `{error: message}` and never throw.1308- Evaluated on every read, for every row, in `hydrate` (`query.ts:47`); nothing is persisted. `FORMULA_HELP` (`formula.ts:118`) is the in-app cheat sheet.13091310### Rollups — exist (partial)13111312`rollup()` (`query.ts:51`): follows one relation property of the same database (`relationPropertyId`) and aggregates one property of the target rows (`targetPropertyId`) with `count, count_values, show, unique, sum, avg, min, max`; min/max work on dates; checkbox counts as 0/1. Limitation confirmed in code: target rows are raw rows (`targets`, `query.ts:31`), so a rollup over a target property that is itself a relation, rollup or formula reads `t.values[tp.id]`, which is never stored for those types → always empty (`query.ts:56`). Rollups are read-only and computed on read.13131314### Relations13151316- Stored in the `relations` table, one row per (property, from row, to row); never in `values`.1317- One-way by default; **two-way supported**: creating a relation with `config.twoWay` and `targetDatabaseId` also creates a reverse relation property on the target database with `config.syncedPropertyId = <forward id>` (`createProperty`, `service.ts:62-66`; named after the source database, or `"<name> (reverse)"` for a self-relation). Both sides read the same `relations` rows: the forward side by `fromRowId`, the synced side by `toRowId` (`loadRelations`, `query.ts:17-23`; `setRelations`, `service.ts:125`).1318- `config.single` limits to one target (`service.ts:126`).1319- Deleting the forward property deletes the reverse property and the relation rows (`deleteRelationPair`, `service.ts:88`). Changing the target database clears the relation rows (`service.ts:83`).1320- Targets are not checked to belong to `targetDatabaseId` when writing (`setRelations` inserts any row uuid; FK only requires an existing row).1321- Relations between row pages also appear as `relation` edges in the knowledge graph (`src/lib/graph/service.ts:60,64`).13221323### Templates (`src/lib/databases/templates.ts:15`)13241325Three built-in templates, code-defined (not rows in the DB):13261. `research-papers` "Research papers" — Status (status), Domain (multi_select), AI model (select), Year (number/integer), Venue (text), DOI (url), Notes (**ai**, with prompt), Added (created_time); views: table "All papers", board "By status", gallery.13272. `tasks` "Tasks" — Status, Priority (select), Due (date), Project (select), Done (checkbox), Days left (**formula** `if(empty(prop("Due")), "", dateDiff(prop("Due"), today(), "days"))`); views: table (sorted by Due), board, calendar (Due), timeline (start = end = Due).13283. `reading-list` "Reading list" — Type, Status, Author, Link, Rating, Tags (multi_select), Finished on (date), Pitch (**ai**); views: list, table, gallery, board.13291330`applyTemplate` (`templates.ts:45`) renames the title property, adds missing properties (matched by name, case-insensitive), reuses the single default view if its type matches the template's first view, creates the other views, sets the icon if empty. It is additive: it can be applied to a non-empty database. Separately, any database can be flagged `isTemplate` and duplicated (`templateOf` is set on the copy, `service.ts:212`); there is no API that lists user templates other than filtering `GET /api/databases` on `isTemplate`.13311332### AI column13331334- Property type `ai` with `config.prompt`. Trigger: `POST /api/databases/[id]/ai` → `enqueueAi` (`service.ts:181`) writes `{pending:true, at}` into the cell, then enqueues queue `ai-property` (`QUEUES.aiProperty`, `src/lib/jobs/queues.ts:1`) with `singletonKey ai:<rowId>:<propertyId>`, 5 s window. Queue policy: 1 retry after 20 s, 300 s expiry, dead-letter (`queues.ts:19`).1335- Worker `handleAiProperty` (`src/worker/handlers/ai-property.ts:14`): loads row + page title + `plainText`; builds `Row properties:` lines from every non-AI, non-rollup, non-formula property (relation titles resolved); substitutes `{{Property name}}` placeholders in the prompt (`:40`); appends up to 6000 chars of the row page's text (`:41`); wraps the data in `<workspace>` tags; system prompt `SYSTEM` (`:11`) + `INJECTION_GUARD`; model chosen by `route({task:"quick"})` (`:45`); `maxTokens 1200`, timeout 120 s. Result (trimmed, outer quotes stripped) is written into `values[propId]`; refusal or exception writes `{error}`; no prompt → `{error:"No prompt configured"}`. Usage recorded with task `ai_property`.1336- **When recomputed: only on explicit request.** `updateRow`/`createRow` never enqueue (`service.ts:142-162`); CSV import and templates do not either. There is no staleness flag, no "source values changed" detection and no schedule (`src/worker/index.ts:38-39` schedules only backup and maintenance).1337- An `ai` cell is also writable as plain text through the API (`splitValues` only blocks `COMPUTED_TYPES`, which does not include `ai`, `service.ts:137`).13381339- **AI column UI [UI]**: `AiCell` (`src/components/database/cells/Cell.tsx:76-84`) — "Generate" on an empty cell, "Regenerate" on a filled one; one row and one property per call (`DatabaseView.tsx:88-93`); rows are polled every 2.5 s while any loaded value is pending (`hooks.ts:49`). **Whole-column fill: absent in the UI** although the route accepts `rowIds` (≤200). Model choice / auto-run options: absent.13401341### UI side of filters, sorts, grouping, pagination **[UI]**13421343- Filter builder (`src/components/database/FilterBuilder.tsx`): saved with the view (no temporary filter); and/or with one level of nested groups (`:48,53`). Operators offered per family (`OPS`, `:9-14`; family by `kind()`, `:15`): text (title, text, url, formula, ai): contains, not_contains, eq, neq, starts_with, ends_with, empty, not_empty · number (number, rollup): eq, neq, gt, gte, lt, lte, empty, not_empty · date (date, created_time, last_edited_time): on, before, after, empty, not_empty (+ a "today" toggle) · checkbox: eq · select/status: eq, neq, empty, not_empty · multi (multi_select, relation): contains, not_contains, empty, not_empty · file: empty, not_empty. A relation filter value is free text (no row picker). Relative dates other than "today", "between": absent.1344- Sorts (`SortBuilder`, `FilterBuilder.tsx:73-91`): multi-sort saved with the view, any property.1345- **Grouping is computed client-side** on the loaded rows (`groupRows`, `src/components/database/group.ts:4-11`), offered for table / board / list (`GROUPABLE_VIEWS`, `Toolbar.tsx:18`) on select / status / checkbox properties only (`:17`). Group counts are counts of loaded rows. Collapse, group sort, sub-groups, per-group aggregates: absent.1346- Search box → server `search` field, one request per keystroke (no debounce).1347- **Pagination / load more / virtualisation: absent.** Rows beyond 500 are unreachable in the UI; only the table footer hints at it ("N rows · showing M", `TableView.tsx:52,99`).13481349### Row pages, RowPeek **[UI + server]**13501351- **Each row is a page**: `database_rows.page_id` (server), `RowOut.pageId` (`types.ts:42`). `RowPeek` (`src/components/database/RowPeek.tsx:24`; desktop side panel, phone bottom sheet) shows the title, every property as an editable cell, and the page body in the shared `Editor` (`GET`/`PATCH /api/pages/[pageId]`, `:143,152-155`), plus "Open as page" (`/p/<pageId>`, `:85`), "Move to…" (when the view is grouped) and Delete. A row page opened directly shows a `RowBanner` back to its database (`src/components/page/PageView.tsx:109,161`). Row pages get links, backlinks, graph node kind `row`, search kind `entry`, version history of the body.1352- Absent in the peek: comments, value history, previous/next navigation, **duplicate row** (no UI and no endpoint).1353- `NewRowSheet` (`src/components/database/NewRowSheet.tsx:39`): phone-only quick-create with a localStorage draft; desktop "New" creates the row immediately (`DatabaseView.tsx:81`).13541355### Property management **[UI]**13561357`PropertyEditor` (`src/components/database/PropertyEditor.tsx:18`): 15 creatable types (`CREATABLE`, `:16` — all but title); per-type config: options with 9 colours and status groups (`OptionsEditor`, `:65`), number format, date `includeTime`, relation target / two-way / single (`RelationConfig`, `:87`), rollup relation + target property + aggregate (`RollupConfig`, `:98`), formula expression with live preview on the first loaded row and function help (`FormulaConfig`, `:109`), AI prompt with `{{Property}}` hint (`:61`). Rename, type change (with warning), two-step delete (no undo). `PropertyManager` (`PropertyManager.tsx:14`): global drag reorder, per-view show/hide. Duplicate property, option reorder: absent.13581359### Templates, CSV, duplicate in the UI **[UI]**13601361- `TemplateChooser` (`src/components/database/TemplateChooser.tsx:11`) appears automatically on a fresh database (≤1 property and 0 rows, `DatabaseView.tsx:116-117`) and from the toolbar menu; applying to a non-empty database has no confirmation. A database can be flagged as a template from the menu (`Toolbar.tsx:55-61`) but **no UI creates a database from a user-flagged template**.1362- CSV: toolbar "More" → "Import CSV…" (`Toolbar.tsx:34-46,69`; no mapping, preview or progress) and "Export CSV (this view)" (`:68`; the search box value is not passed).1363- "Duplicate (structure)" / "Duplicate with rows" (`Toolbar.tsx:47-54,71-72`).1364- Entry point for a new database: slash command "New database" creates a **sub-page of type `database`** (`src/components/editor/extensions/slash.tsx:33,61`); the database is rendered full-width on its own page (`PageView.tsx:114`).13651366### Bulk actions and undo in the UI **[UI]**13671368- Bulk: multi-select + bulk delete in the table grid only (`TableView.tsx:77,110`). Bulk edit, select-all, shift-range, bulk actions in other views: absent.1369- Undo: row deletion (toast → `POST …/rows/restore`, `DatabaseView.tsx:58-64`) and board moves (`BoardView.tsx:49`). No undo for cell edits, property or view deletion, CSV import, template application; no Ctrl+Z stack.13701371### Mobile handling **[UI]**13721373`src/components/database/Responsive.tsx`: `ResponsivePanel` (`:11-48`) renders a Radix popover on desktop and a bottom sheet (`Dialog` + `SheetContent`) on phones, switching on `useIsPhone()` = `(max-width: 767.98px)` (`src/lib/client/hooks.ts:10`); single breakpoint, no tablet tier. `SheetSection` (`:51-59`) is a titled section for the phone options sheet. Used by view settings, property editor, select / relation / file pickers, "Move to…". Other phone switches via `ctx.isMobile`: one "View options" sheet instead of four toolbar popovers, table → cards by default, calendar → agenda, timeline → list, board → no drag, row detail and new row as sheets.13741375### CSV import / export (server)13761377- Export: `exportCsv` (`src/lib/databases/csv.ts:9`) — UTF-8 BOM, papaparse `unparse`; columns = title + non-hidden properties in display order; dates and numbers raw, files as URLs, everything else as display string. Reached by `GET /api/databases/[id]?format=csv` and `GET /api/databases/[id]/query?format=csv`, **capped at 1000 rows** (`limit: 1000`) with no continuation.1378- Import: `importCsv` (`csv.ts:14`) — papaparse with header row; title column = the column named like the title property, else `name|title|titre|nom`, else the first column; unknown columns are created as `text` properties (no type inference); existing columns parsed by `parseValue` (`values.ts:80`: number, checkbox `true|yes|y|1|x|✓|oui`, date, select/status and multi_select auto-create options, split on `, ; |`); relation columns matched by target row title; rollup/formula/created/last-edited/file columns ignored. One `createRow` per record, synchronously inside the HTTP request, no row limit other than the 20 MB file cap, no transaction, no dry run.13791380### Duplicate13811382`duplicateDatabase` (`service.ts:209`): new host page "<name> (copy)", copies properties (ids remapped inside configs and view configs by string replacement, `remapConfig`, `:208`), views, and with `withRows` every row + its page content + forward relations (self-relations remapped to the new rows). Relation properties lose `twoWay`/`syncedPropertyId` on the copy (`:216`) — copies of the *reverse* side of a two-way relation therefore become independent one-way relations with no data. Single-row duplicate endpoint: **absent** (no route; the UI part below says whether the client emulates it).13831384### Bulk actions / undo (server side)13851386- Bulk endpoints exist: `POST rows {rows:[…]}` (≤500), `PATCH rows {updates:[…]}` (≤500), `PATCH rows {order:[…]}` (≤5000), `DELETE rows ?ids=`, `POST rows/restore {ids}` (≤500), `POST ai {rowIds}` (≤200). Each is a sequential loop of single-row operations, not one statement and not one transaction (`rows/route.ts:16,24`).1387- Undo: only for row deletion (`rows/restore/route.ts`, soft-delete based). No undo log for cell edits, property changes, view changes or CSV imports. Row pages keep page version history (`page_versions`) for their body, not for `values`.13881389---13901391## (c) Section 5 — Knowledge graph13921393### Tables13941395- `links` (`schema.ts:154`): `id, fromPageId, toPageId, kind text default "link", createdAt`; unique `(from, to, kind)`; index on `toPageId`. Values of `kind` actually written: `"link"` (`src/lib/pages/service.ts:106`) and `"ai"` (accepted suggestion, `src/lib/graph/service.ts:118`).1396- `knowledge_edges` (`schema.ts:158`): `id, workspaceId, fromId, toId, fromType default "page", toType default "page", kind, status default "accepted", confidence double, rationale text, model text, createdAt, decidedAt`; unique `(fromId, toId, kind)`. No FK on `fromId`/`toId`.1397- Also read as graph sources: `relations` (database relations), `attachments.page_id`, `tags` + `page_tags`.1398- Unlinked-mention dismissals: a single `settings` row, key `backlinks.dismissed`, JSON array of `"<fromId>:<toId>"` capped at 5000 (`src/lib/graph/backlinks.ts:10,29-33`).13991400### Edge / link kinds14011402- Schema comment for `knowledge_edges.kind` (`schema.ts:161`): `link | backlink | relation | ai_suggested | concept`. **Only `link` and `ai_suggested` are ever written** (`pages/service.ts:107`; `graph/suggest.ts:60`). `backlink`, `relation`, `concept`: no writer anywhere in `src/` — absent in practice. `fromType`/`toType` are always `"page"`.1403- `knowledge_edges.status`: `suggested | accepted | rejected` (`schema.ts:162`).1404- API-level edge kinds (`EdgeKind`, `src/lib/graph/types.ts:3`): `"link" | "relation" | "ai_suggested" | "attachment" | "tag"`; node kinds (`NodeKind`, `types.ts:2`): `"page" | "database" | "research" | "row" | "attachment" | "tag"`.1405- **Typed relations (supports / contradicts / cites / depends-on …): absent.** The only "type" information is the edge source (link, database relation with the property name in `sub`, AI suggestion, attachment, tag).1406- **Temporal attributes: absent** beyond `createdAt` / `decidedAt`. No validity interval, no "as of", no event time. The `since` filter of `/api/graph` filters *nodes* by `pages.updated_at` (`graph/service.ts:47`), not edges.14071408### Link extraction14091410- `extractPageRefs` (`src/lib/editor/text.ts:27`): collects (1) `mention` nodes with `attrs.id` and `attrs.kind !== "user"`, (2) `link` marks whose `href` matches `^/p/<uuid>`. **Wikilink `[[…]]` syntax is not parsed in documents** — `[[label]]` only exists as the Markdown *export* form of a mention (`text.ts:38`); the editor creates mentions with `@` (`src/components/editor/extensions/mention.tsx`, slash item "Link to page", `slash.tsx:31`). Whether Markdown *import* turns `[[…]]` back into mentions is covered in the import section.1411- When: **synchronously on every content/title save**, in `afterContentChange` (`pages/service.ts:100`), called from `createPage` (`:30`) and from `updatePage` when `content` or `title` changed (`:61`). It deletes the page's `links` rows of kind `link`, re-inserts one per valid referenced page of the same workspace (`:102-106`), mirrors them into `knowledge_edges` kind `link` status `accepted` (`:107`) and removes stale mirror rows (`:110`), then enqueues `index-source` for the page (singleton 20 s, `:111`). Not transactional. No worker is involved in link extraction.1412- Block-level anchors: links are page → page only; the source block id of a mention is not stored.14131414### Backlinks14151416- Per page: `backlinks()` (`pages/service.ts:133`) — pages with a `links` row pointing to the page (all kinds), not deleted.1417- Explorer: `backlinkStats` (`graph/backlinks.ts:13`) — pages ranked by incoming count with outgoing and pending-suggestion counts, three correlated sub-selects per page, limit 300, optional title `ilike` filter.1418- **Unlinked mentions** (`unlinkedMentions`, `backlinks.ts:37`): SQL self-join of `pages` × `pages` with `strpos(lower(m.plain_text), lower(t.title)) > 0`, titles ≥ 4 chars and not "Untitled", minus existing links and dismissed pairs; excerpt = 200 chars around the first occurrence; limit 400. `linkMention` (`backlinks.ts:52`) appends a paragraph `"See also " + mention node` at the **end** of the mentioning page (it does not convert the occurrence in place) via `updatePage(..., {versionReason:"backlink"})`.14191420### AI link suggestions14211422- Candidate generation: **embedding similarity** — `nearestPages` (`graph/suggest.ts:19`): up to the first 24 chunk embeddings of the source page, each joined laterally to its 20 nearest chunks of other pages (pgvector `<=>`, HNSW index `embeddings_hnsw_idx`), aggregated per page with `max(sim)`; excludes self, pages already linked in either direction, and any pair that already has an `ai_suggested` edge in any status; threshold `minSim = 0.25`; top `k` (8 for one page, 5 per page in a workspace pass).1423- Confirmation: **LLM** — `suggestLinksForPage` (`suggest.ts:43`): one call, model from `route({task:"quick", schema:true})`, structured output `link_suggestions` (`SCHEMA`, `:38`: per candidate `{id, related, confidence, rationale}`), strict system prompt `SYSTEM` (`:39`), source excerpt 2000 chars, candidate excerpts 1200 chars.1424- Storage: every judged candidate is inserted in `knowledge_edges` with `kind="ai_suggested"`, `confidence`, `rationale` (≤600 chars), `model`; `status="suggested"` when `related && confidence >= 0.35`, else `status="rejected"` so the pair is never proposed again (`suggest.ts:59-60`).1425- UI threshold: `LOW_CONFIDENCE_THRESHOLD = 0.6` (`graph/types.ts:31`) collapses weaker suggestions under "Low confidence".1426- When: **on demand only** — `POST /api/graph/suggestions` (inline for one page, or queue `suggest-links`); workspace pass = the 150 most recently edited pages that have embeddings (`suggestLinksForWorkspace`, `suggest.ts:71`). No schedule and no trigger on save (`worker/index.ts:38-39`). Queue policy: no retry, 600 s expiry (`queues.ts:18`).1427- Accept (`decideSuggestion`, `graph/service.ts:114`): sets `status="accepted"`, `decidedAt`, and inserts `links(from, to, kind="ai")` — so it becomes a real backlink and a `link` edge with `sub:"ai"`; **the document content is not modified** (no mention is inserted). Reject: `status="rejected"`, removes any `ai` link. Undo (`revertSuggestion`, `:127`): back to `suggested`, `decidedAt=null`, `ai` link removed. A pending suggestion disappears from lists once the two pages are linked explicitly (`:103`).14281429### Graph API shapes (`graph/types.ts:8-25`)14301431- `GraphNode`: `{id, kind, title, icon, section, updatedAt, databaseId, degree, in, out}`; attachment nodes have id `att:<uuid>`, tag nodes `tag:<uuid>` with title `#name`.1432- `GraphEdge`: `{id, source, target, kind, status: "accepted"|"suggested", sub, confidence, rationale, model?}`; `sub` = `links.kind` (`"link"` | `"ai"`) for links, the relation property name for relations, the model id for suggestions, the tag colour for tag edges.1433- `GraphData`: `{nodes, edges, center?, stats:{pages, edges, suggested}}`.1434- `buildGraph` (`graph/service.ts:40`): one page query (whole workspace unless `pageId`), then links / relations / knowledge edges in parallel, then attachments and tags; duplicate pairs collapsed per kind; an accepted AI edge already mirrored as a link is drawn once and its rationale/confidence/model are copied onto the link edge (`:66-73`). `degree/in/out` are computed in JS from the returned edges. No server-side layout, clustering, centrality, community or path computation. No pagination or node cap.1435- Neighbourhood: breadth-first over links ∪ relations ∪ non-link knowledge edges, one SQL per hop, depth ≤ 4 (`neighbourhoodIds`, `:30`).14361437### Graph UI **[UI]**14381439- **`GraphExplorer`** (`src/components/graph/GraphExplorer.tsx:30`, route `/graph`): desktop = canvas + toolbar + filter popover + right panel (`"inspector" | "suggestions"`, `:37`); phone = `BacklinksExplorer` list first, canvas as a secondary mode (`:32,90-92`).1440 - Filters — exact list (`:60-73`), all applied server-side through `/api/graph`: node-kind chips (Pages, Databases, Research, Database rows, Attachments, Tags), Section (`notes, documents, databases, projects, research`, `:19`), "Edited within" (All time / 7 / 30 / 90 days / year, `:20`), neighbourhood depth 1–3 (only in local mode; default 2), "Hide orphans", "AI suggestions". Absent: edge-kind filter, tag filter, database filter, free date range.1441 - Search box: client-side substring match on titles of loaded nodes (`:45`), Enter focuses the first match. No content search.1442 - **Local vs global**: global = no `pageId`; local = `pageId` + `depth`, entered by `?focus=`, the Inspector's "Neighbourhood" button, or "Open full graph" on a page; "Whole graph" returns (`:113`).1443 - Layout: one force layout; "Fit to view" and "Freeze layout" only. Layout choices, time slider, clustering, centrality metric, path finding: absent.1444- **`GraphCanvas`** (`src/components/graph/GraphCanvas.tsx:27`): `react-force-graph-2d` loaded dynamically, `ssr:false` (`:13`); custom node painting (`paintNode`, `:60-71`): radius `2.2 + min(10, sqrt(degree)*1.4)` (`:24`), ring look for database/research nodes, dimming of non-neighbours. Colours per node kind `kindColor` (`src/components/graph/colors.ts:18`), per edge `edgeColor` (`:19`); suggested edges dashed; arrowheads only on `link` and `relation` (`:77`). Labels appear from zoom scale ≥ `labelsAt` (default 1.7) or for selected/hovered/highlighted nodes (`:68`). Interactions: click select, hover, drag (unless frozen), zoom 0.15–12, auto-fit once per data key. Positions are cached across refreshes. Edge click: prop exists (`onEdgeClick`, `:20`) but no caller passes it.1445- **`Inspector`** (`src/components/graph/Inspector.tsx:13`): for the selected node — kind, section, degree, in/out, relative update time; buttons Open, Neighbourhood, "Suggest connections with AI"; list of its connections within the loaded graph with badges (AI %, relation property name, "AI ✓") and, for AI edges, rationale + provenance (model, confidence). No content preview, no tags, no accept/reject in place, no edge deletion.1446- **`ConnectionsList`** (`src/components/graph/ConnectionsList.tsx:101`; data from `GET /api/graph/neighbors?pageId&depth=1&linked=1`, `:24`): sections "Links from this page", "Backlinks", "Relations", "Accepted AI links", "Tags and files", then "Suggested by AI — to review" and a per-page suggest button. Rows show a title and a generic reason; **no backlink context snippet**.1447- **`PageGraphCard`** (`src/components/graph/PageGraphCard.tsx:22`): local graph at the bottom of every page (`src/components/page/PageView.tsx:120`) and in the dashboard widget (`src/components/dashboard/widgets.tsx:132`). Depth is a prop defaulting to 1; both call sites use 1, so depth 2/3 is not reachable from this card. Desktop: compact canvas + list toggle + "Open graph" (`/graph?focus=`). Phone: list first, canvas behind "Open full graph".1448- **`BacklinksExplorer`** (`src/components/graph/BacklinksExplorer.tsx:30`, route `/backlinks`): tabs Pages (table: Page, Section, Incoming, Outgoing, AI, Updated, Details) / Unlinked mentions (excerpt with the title highlighted, "Link it", "Dismiss" with undo) / AI suggestions. One title filter applied client-side to the full payload (`:41-42`); no sorting, no pagination. Header shows a count of pages without any link (`:43,82`) but there is no orphan list or filter.1449- **`SuggestionList`** (`src/components/graph/SuggestionList.tsx:181`): card per suggestion — source → target, "Why:" rationale, confidence bar, model, age; suggestions below 0.6 or without confidence are collapsed under "Low confidence" (`isLowConfidence`, `:20`). Accept / Reject = `PATCH /api/graph/suggestions` with optimistic removal (`useDecide`, `:33`); Undo only from the toast (`status:"suggested"`, `:38-45`). Generation per page (inline) or whole workspace (queued, behind a `confirm`). Absent: bulk accept/reject, a history of past decisions, the embedding similarity score.14501451---14521453## (d) Search14541455### Index tables14561457- `chunks` (`schema.ts:173`): `id, workspaceId, sourceType` (comment: `page | attachment | row`; **only `page` and `attachment` are indexed** — `handleIndex` ignores anything else, `src/worker/handlers/index-source.ts:18`; database row *values* are not indexed, only the row page's body), `sourceId, pageId, ordinal, blockId, heading, content, tokenCount, contentHash`, generated `tsv`.1458- `embeddings` (`schema.ts:181`): `chunkId` PK, `model, version, embedding vector(1536)`; HNSW index `vector_cosine_ops`.1459- `index_state` (`schema.ts:185`): per source `contentHash, model, version, indexedAt` — makes indexing incremental.1460- `pages.tsv` (`schema.ts:85`): generated column (title weight A, plain_text weight B) with GIN index `pages_tsv_idx` — **not used by any query** (only `chunks.tsv` is queried, `src/lib/search/index.ts:73`). `pages_title_trgm_idx` (trigram on title) serves quick search.14611462### Indexing14631464- Chunking (`src/lib/search/chunk.ts:8`): follows headings and blocks (`docBlocks`, `editor/text.ts:18`), target ~350 tokens, max 500, 60-token overlap for long blocks; each chunk keeps the `blockId` of its first block and the current heading; token count approximated as chars/3.6. Attachments: paragraph chunking of `extracted_text` (`chunkPlainText`, `:29`).1465- `indexSource` (`search/index.ts:11`): skips when the source hash (chunk hashes + block ids + headings) is unchanged; re-embeds only changed chunks; embeddings from OpenAI through `embedTexts` (`src/lib/search/embed.ts:6`, model `EMBEDDING_MODEL` from `@/lib/ai/router` — the comment at `index.ts:50` names text-embedding-3-small; batches of 64, text cut to 8000 chars).1466- Trigger: queue `index-source` after every page create/update (`pages/service.ts:111`), restore, and manual re-index; `reindex-all` walks every page and attachment sequentially (`index-source.ts:20`). Soft-deleting a page deletes its chunks immediately (`pages/service.ts:116`).14671468### Query14691470- FTS configuration: **`'simple'`** everywhere (`schema.ts:85,178`; `websearch_to_tsquery('simple', …)`, `index.ts:73`). No stemming, no stop words, no language detection, no French/English dictionary, no `unaccent`.1471- `hybridSearch` (`index.ts:59`): keyword leg = `ts_rank_cd` over `chunks.tsv`, top `k = max(30, limit×4)`; semantic leg = cosine over `embeddings` for the embedded query, same `k`, only when `semantic` is not disabled and `OPENAI_API_KEY` is set (failure is logged and ignored); **fusion = reciprocal rank fusion, `1/(60+rank)` per leg** (`:78,80`); a semantic-only hit is kept only if similarity ≥ `SEARCH_MIN_SEMANTIC = 0.33` (`:51,80`). No reranker, no recency/authority boost, no typo tolerance on the passage leg.1472- Filters (`SearchFilters`, `index.ts:48`): `kinds` (page = non-row page, entry = database row page, file = attachment, reference), legacy `sourceTypes`, `section`, `since`/`until` (on `pages.updated_at`, or chunk creation for files), `includeArchived`, `pageIds` (internal callers only), `semantic`, `group`, `limit`. **Not available: tag filter, database/property filter, author, has-task, link-based filters.**1473- Result shape `Hit` (`index.ts:47`): `{chunkId, pageId, sourceType, sourceId, title, icon, heading, blockId, content, score, semantic, keyword, kind, section, archived, updatedAt, snippet, more?, attachmentId}`. **Block-level: yes** — one hit per chunk with `blockId` + `heading`; `snippet` is `ts_headline` with control-character markers `HL_START`/`HL_END` (`\u0001`/`\u0002`, `:52`). With `group=1` hits collapse to the best passage per source with `more` = number of other matching passages, and up to 8 bibliographic references are appended (`searchReferences`, `:98`, `ilike` + trigram on title/cite key/DOI/journal/authors; not embedded).1474- `quickSearch` (`index.ts:109`): title `ilike` + trigram similarity, prefix matches first; empty query returns recently opened pages.1475- `findDuplicates` (`index.ts:115`): pairs of pages having two chunks with cosine similarity ≥ threshold (default 0.93), top 50.1476- `indexHealth` / `indexStats` (`index.ts:131,144`): pending pages (never indexed or changed > 90 s after last indexing), files awaiting indexing, failed `index-source` jobs of the last 7 days read from `pgboss.job`, queued count, whether embeddings are configured.14771478### SearchPage **[UI]** (`src/components/search/SearchPage.tsx:39`, route `/search`)14791480- Request is always `mode=hybrid&limit=40&group=1` (`:47`); the only mode control is the switch "Search by meaning" (`semantic=1|0`, `:70`). No keyword/semantic/hybrid selector.1481- Filters (`:64-74`), stored in `localStorage["note-search-filters"]`: Type (Pages, Database entries, Files, References), Space (All, Notes, Documents, Projects, Research, Databases), Last changed (Any, 7 d, 30 d, year, Custom with From/To), "Include archived pages", Reset. Absent: tag, database/property, sort order, title-only.1482- Results: one card per best passage of a source — icon, title, `› heading`, snippet with `<mark>` highlights (server markers, client fallback, `:25-32`), kind badge, section, relative date, "archived", "by meaning" (semantic-only hit), "+N more passages" (display only, cannot be expanded).1483- Deep link to the block: `/p/<pageId>?hl=<terms>#block-<blockId>` (`hrefOf`, `:33`; consumed in `src/components/page/PageView.tsx:51-52`).1484- Tabs: Results / Index (counters, pending + failed lists, "Index pending (N)", "Rebuild everything"; polls every 8 s while open) / Duplicates (pairs with % similarity, read-only — no merge, dismiss or compare).1485- Absent: pagination or load-more (hard cap 40), recent/saved searches, arrow-key navigation of results. Query is mirrored to the URL; scroll position restored on return.14861487---14881489## (e) Inbox / capture, tasks, tags, dashboard, import / export14901491### Capture and inbox (`src/lib/inbox/service.ts`)14921493- **There is no inbox table.** A capture is an ordinary `pages` row with `inbox_at` set and `capture_kind` (`schema.ts:92`); it is indexed and editable immediately and excluded from the tree until triaged (service comment `:1-4`).1494- Capture payload: `{kind, text?, url?, attachmentIds?}`; kinds `note | tasks | link | photo | file | audio` (`CaptureKind`, `:13`). Behaviour (`capture`, `:18`): `link` requires an http(s) URL (from `url` or the first text line), title = host or first line, body = linked URL + note lines; `tasks` turns each line into an unchecked `taskItem` of one `taskList`; others: first line (≤120 chars) is the title, long/multi-line text becomes paragraphs; attachments become `image` or `fileBlock` nodes and are re-parented to the page. Always `section="notes"`. No URL fetching/unfurling, no OCR/transcription here (audio/photo are stored as attachments only).1495- **Statuses: no status column.** State is derived: in inbox = `inbox_at is not null and archived = false and deleted_at is null` (`listInbox`, `:47`); archived = `archived = true`; trashed = `deleted_at`. `listInbox` adds `tags`, first `url`, first `image`, `openTasks` (unchecked task items) per item.1496- Triage actions (`triage`, `:60`; `TriageAction`, `:58`): `keep` (file into a section, optionally under `parentId`) · `task` (creates one `tasks` row per unchecked task item, or one from the title; the note stays, linked by `tasks.page_id`) · `database` (creates a row in the chosen database with the page's title and content, archives the capture) · `reference` (finds a DOI in title/text → `addByDoi` (Crossref), else a plain reference with the first URL; archives the capture) · `archive` · `trash`. Every action clears `inbox_at` and writes an `inbox_actions` journal row (`schema.ts:148`: `before` page state + `created` ids).1497- Undo (`undoTriage`, `:92`): restores `inbox_at`, `archived`, `section`, `parentId`; soft-deletes created tasks, deletes the created row and reference; idempotent via `undoneAt`. Journal rows are purged after 90 days (`src/worker/handlers/maintenance.ts:15`).1498- "File into an existing page" (append the capture to another page / merge): **absent** — `keep` only re-parents. "Convert to page" is implicit (it already is one).1499- **AI suggestion in the inbox service or routes: absent** (no suggested destination, tags, title or action in `service.ts`, `/api/inbox*`, `/api/capture`). Related but separate: the agents subsystem has an `inbox_triage` proposal kind that applies `triage()` + tags after human approval (`src/lib/agents/apply.ts:82-90`); that code is outside this part and was only skimmed.15001501**Inbox UI [UI]** — `InboxView` (`src/components/inbox/InboxView.tsx:21`, route `/inbox`): rows show kind icon (`KIND`, `:14`), title, excerpt, age, "N to do", link host, `#tags`, thumbnail; a row opens the page. Actions: "File in a space" (notes / documents / projects / research), "Turn into a task" / "Create N tasks", "Add to a database", "Save as a reference", tag editor (`PUT /api/pages/[id]/tags`), Archive, "Move to trash"; every triage toast carries Undo (`POST /api/inbox/undo`, `:33`); tag edits have no undo. The API's `parentId` (file under a page), `dueAt` and `priority` of the `task` action are never sent by this UI. Absent: AI-suggested destination / tags / title, bulk selection, filter by kind, pagination. The capture form is `src/components/shell/CaptureSheet.tsx` (outside the listed directories, **not audited**). `ArchiveView` (`src/components/inbox/ArchiveView.tsx:12`, route `/archive`) lists archived pages (`GET /api/pages?view=archived&limit=100`) with Restore + undo only.15021503**Trash UI [UI]** — `TrashView` (`src/components/trash/TrashView.tsx:17`): trashed pages only (`GET /api/pages?view=trash&limit=100`); restore one (`POST /api/pages/[id]?action=restore`), purge one (`DELETE /api/pages/[id]?purge=1`), empty trash (`DELETE /api/trash`), purge older than 30 days (`POST /api/trash`); the three destructive calls are wrapped in `withFreshAuth` on the client (those routes are outside this part; their server-side step-up was not verified here). Deleted database rows appear as their row pages; deleted tasks have no trash UI.15041505### Tasks15061507- **Tasks are rows of the `tasks` table** (`schema.ts:142`), not derived from documents: `id, workspaceId, pageId (nullable, on delete set null), title, status default "todo", priority default "medium", dueAt, completedAt, project (free text), createdAt, updatedAt, deletedAt`. Index only on `dueAt`.1508- **No link to `taskItem` nodes**: checking a to-do in a document does not touch `tasks`, and completing a task does not check a box. The only bridge is one-shot: inbox triage `task` copies unchecked task items into task rows (`inbox/service.ts:70-72`).1509- Absent fields: description/notes, sub-tasks, recurrence, reminders, assignee, labels/tags, ordering/position, start date, estimate, block anchor (only `pageId`).1510- Status vocabulary differs by path: `/api/tasks` accepts `todo | done` (`tasks/route.ts:26`); the research module uses `todo | doing | done` on the same table (`src/lib/research/tasks.ts:7`); priority `low | medium | high`.1511- API: section (a). Other writers: research tasks (`src/lib/research/tasks.ts`), agents (`agents/apply.ts:107`).15121513**TasksInbox UI [UI]** (`src/components/tasks/TasksInbox.tsx:80`, route `/tasks`): groups Overdue / Today / This week / Later / No date / Done (`GROUPS` `:18`, `groupOf` `:24`); filters Project and Priority (client-side on the loaded ≤500 tasks, `:89`) and "Show done" (`?done=1`); toggle = optimistic `PATCH /api/tasks {id, status}` (`:68,103-104`); inline edit of title, due (datetime-local), priority, project, linked page (picker over `/api/tree`); delete without confirmation or undo (`:106`). Quick-add line parsed live by `parseTaskInput`. Absent: sub-tasks, recurrence, reminders, drag ordering, bulk actions, board/calendar view, text search.15141515**`parse.ts`** (`src/components/tasks/parse.ts:13`, client-only natural-language quick add; English keywords only): priority `!high|!medium|!med|!low` (`:17`), `!`/`!!`/`!!!` (`:18`), `p1|p2|p3` (`:19`); one `#project` (`:21`); time `5pm`, `at 5:30pm` (`:24`), `at 17` (`:25`); relative dates `today|tonight` (18:00), `tomorrow|tmr|tmrw`, `[in] N day(s)|week(s)|month(s)`, `next <weekday>`, `next week`, `next month`, `[this] weekend`, `end of [the] week|month`, bare weekday (`:27-35`); absolute `nov 15[, 2026]`, `15 nov`, `YYYY-MM-DD`, `M/D[/Y]` (`:37-40`); default time 17:00. Output `{title, dueAt, priority, project}`. Absent: `@mentions`, several tags, recurrence, "in 2 hours", French keywords.15161517### Tags15181519- Model: `tags` (`schema.ts:137`: `id, workspaceId, name, color default "gray", createdAt`, unique `(workspaceId, name)`) + `page_tags` (`schema.ts:141`: `(pageId, tagId)` PK). Flat (no hierarchy), page-level only (no block tags, no inline `#tag` extraction from text — `extractPageRefs` does not look at hashtags), names normalised to lowercase, `#` stripped, ≤40 chars, ≤20 per page (`setPageTags`, `inbox/service.ts:109-116`). No rename, merge, delete or colour-edit endpoint (`/api/tags` is GET only). Tags are never deleted when unused.1520- **`GET /api/tags` "counts 0 pages" — cause: `src/lib/inbox/service.ts:107`.**1521 ```ts1522 db.select({ id: schema.tags.id, name: schema.tags.name, color: schema.tags.color,1523 pages: dsql<number>`(select count(*)::int from page_tags pt join pages p on p.id = pt.page_id1524 where pt.tag_id = ${schema.tags.id} and p.deleted_at is null)` })1525 .from(schema.tags).where(eq(schema.tags.workspaceId, wsId)).orderBy(schema.tags.name);1526 ```1527 The select has a single table and no join. In that case drizzle-orm renders column references **inside the selected fields** without the table qualifier (the single-table branch of the pg dialect's `buildSelection` replaces each column chunk of an `sql` field by its bare identifier). `${schema.tags.id}` therefore becomes `"id"`, and the sub-select reads `… where pt.tag_id = "id" …`. Inside the sub-select, an unqualified `id` binds to the nearest scope that has such a column: `pages p` (`page_tags` has no `id`). The predicate is effectively `pt.tag_id = p.id`, which is never true, so every tag reports `pages: 0`. The query raises no error.1528 Supporting evidence in the same codebase: `listDatabases` (`src/lib/databases/service.ts:34`) uses the same correlated-count pattern but its select has an `innerJoin`, so columns stay qualified and `rowCount` works.1529 Status: **confirmed by reading; not executed** (no `node_modules`, no database in this copy). The rendering rule is drizzle-orm's documented single-table behaviour, stated here from knowledge of the library rather than from the installed source. A one-line check on a node settles it: run the query with `.toSQL()` and look for `pt.tag_id = "id"`. Fix direction: write the outer reference as literal SQL (`"tags"."id"`), or compute counts with a `leftJoin` + `groupBy`.1530 Blast radius: `GET /api/tags` only. `/api/sync` also calls `listTags` but keeps only `name` and `color` (`src/app/api/sync/route.ts:41`). No test covers `listTags` (no match for `listTags` or `/api/tags` under `tests/`).15311532### Dashboard15331534- Widgets — exact enum `WIDGET_TYPES` (`src/components/dashboard/types.ts:2`): `quick_capture, recent_pages, pinned, tasks_due, active_projects, research_activity, recently_modified, ai_summary, graph_highlights, ai_chat, usage`. Sizes `half | wide | third`. Default layout `DEFAULT_WIDGETS` (`types.ts:6`) = all of them except `ai_chat`.1535- Layout persistence: `dashboard_layouts` (`schema.ts:253`), one default row per user, `widgets` JSON; `PUT /api/dashboard` saves or resets.1536- `GET /api/dashboard` (`dashboard/route.ts:21`): 15 parallel queries + 5 sequential ones. Data: recent (by `greatest(last_opened_at, updated_at)`), pinned, recently modified, tasks due within 7 days incl. overdue (≤30), active projects (section `projects`, or research pages whose `properties.status` ∈ `PROJECT_STATUSES`, `:10`), research pages/experiments/counts, graph highlights (top 6 pages by degree over accepted `knowledge_edges`, pending-suggestion count, accepted edge count), AI usage today vs `settings["ai.daily_budget_usd"]` (default 20), stored summary, pages edited this week.1537- Summary route: `POST /api/dashboard/summary` (`dashboard/summary/route.ts:16`) — **weekly** digest: up to 40 pages edited in the last 7 days (title + first 400 chars) and up to 40 open tasks, one model call (`route({task:"chat"})`, `maxTokens 1200`, prompt `SYSTEM` `:10` asks for 120–220 words of Markdown), stored in `settings["dashboard.summary"]` (one value for the whole instance, overwritten each time, no history). Manual trigger only; not scheduled.1538- Absent: daily brief, knowledge-health widget, timeline / event-journal widget, inbox widget, per-widget data endpoints (one monolithic GET).15391540**Dashboard UI [UI]** (`src/components/dashboard/Dashboard.tsx:43`, `widgets.tsx:151` `WIDGET_COMPONENTS`): `quick_capture` (title + Markdown → `POST /api/pages`, or "Ask AI instead"; note it creates a normal page, not an inbox capture), `recent_pages`, `pinned`, `tasks_due` (first 10, inline complete), `active_projects`, `research_activity` (3 counters + lists), `recently_modified` (+ "N pages edited this week"), `ai_summary` (Markdown + model/cost, "Summarize my week" / Refresh), `graph_highlights` (`PageGraphCard` of the top page + top 5 + pending suggestions), `ai_chat` (embedded chat), `usage` (spend vs budget). "Customize" mode: drag reorder (`@dnd-kit`), resize third/half/wide, remove, add (duplicates allowed), reset; saved with `PUT /api/dashboard`. The per-widget `config` field is stored but read by no widget.15411542### Import / export15431544Reported from the delegated read of `src/lib/import/**`, `src/lib/export/**`, both routes and `src/worker/handlers/import.ts` (**[delegated]**).15451546**Formats**15471548| Format | Export | Import |1549|---|---|---|1550| Markdown | **yes** — one page with YAML front-matter (`pageMarkdown`, `src/lib/export/markdown.ts:45`; converter `toMarkdown`, `src/lib/editor/text.ts:34`). A subtree as Markdown only inside the ZIP | **yes** — `.md/.markdown/.txt`, `marked` lexer (`src/lib/editor/markdown-client.ts:57`) + hand-written front-matter parser (`src/lib/import/markdown.ts:23`) |1551| ZIP | **yes** — Markdown tree + one CSV per database + `attachments/` + `references.bib` + `workspace.json` + `export-report.json` (`buildZip`, `src/lib/export/zip.ts:41`) | **yes** — `jszip` after an own central-directory check (`importZip`, `src/lib/import/zip.ts:103`) |1552| JSON | **yes** — full backup of 15 tables, format `"note-spboucher-ai/1"` (`workspaceJson`, `src/lib/export/json.ts:8`); attachment *files* not included | **only the app's own backup** (`restoreJson`, `src/lib/import/restore.ts:51`) |1553| CSV | **yes** — one database (`databaseCsv`, `src/lib/export/csv.ts:17`; plus the per-view export in section (b)) | **yes** — a CSV file becomes a **new** database with type inference (`src/lib/import/csv.ts`); CSV into an existing database is the route in section (b) |1554| BibTeX | **yes** (`bibFile`, `src/lib/research/bibtex.ts:140`) | **yes** — `bibtex-parse-js` → `references` rows (`src/lib/import/bibtex.ts:33`) |1555| HTML | **yes, as a print view of one page** (`docToHtml` / `printHtml`, `src/lib/export/html.ts:14,92`) | **absent** |1556| PDF | **absent as a generated file** — `format=pdf` returns the same HTML page; the browser prints it (`src/app/api/export/route.ts:75-84`, `window.print()` in `html.ts:98,101`) | **yes** — text layer only via `pdf-parse`, no OCR (`src/lib/import/pdf.ts:11`): one page with a file block and "Page N" headings |1557| DOCX | **absent** | **yes** — `mammoth` → HTML → `turndown` → Markdown (`src/lib/import/docx.ts:9,13,29`) |1558| **LaTeX** | **absent** — no `.tex` export; LaTeX exists only as math source inside Markdown (`$…$`, `$$…$$`) and as KaTeX input | absent |1559| Notion | absent | **only the "Markdown & CSV" ZIP** — detected by the 32-hex id suffix in names (`NOTION_ID`, `isNotionExport`, `src/lib/import/zip.ts:20,26`); Notion HTML export and API: absent |1560| Obsidian | absent | **only a vault as ZIP** — `.obsidian` folder detected and ignored (`zip.ts:28,64`); `[[wikilinks]]` handled; `![[embeds]]`, inline `#tags`, callouts, block references: absent |1561| `.doc`, ENEX, OPML, Roam/Logseq, EPUB, RTF, XLSX | absent | absent (`.doc` refused explicitly, `src/lib/import/validate.ts:132`) |15621563**Export details**15641565- Scopes: single page (md, html/pdf), subtree (zip, bib), workspace (zip, json, bib), database (csv). Subtree as JSON: absent.1566- ZIP contents: pages as `<path>/<slug>.md`, row pages under their database folder, `<slug>.csv` per database, all attachments of the set, `references.bib`, `workspace.json` manifest (pages without content, databases with properties / views / rows, attachments, references, experiments, tasks, tags — `zip.ts:80-92`), `export-report.json` (`ExportReport`, `zip.ts:21-32`: counts, `complete`, `notIncluded[]`, `unresolved`, `byDesign[]`). Not in a ZIP by design: page versions, knowledge-graph edges, links table, search data, trashed pages (`BY_DESIGN`, `zip.ts:33-39`). Cap 500 MB (`ZIP_MAX_BYTES`, `zip.ts:18`).1567- JSON backup arrays: `pages` (trashed included), `pageVersions`, `databases`, `databaseProperties`, `databaseRows`, `databaseViews`, `relations`, `attachments` (rows only), `tags`, `pageTags`, `tasks`, `links`, `knowledgeEdges`, `references`, `experiments` (`json.ts:21-36`). Not included: attachment files, chunks/embeddings, AI conversations, usage, audit log, sessions, passkeys. No size cap.1568- Markdown conversion handles: paragraph, heading, blockquote, codeBlock, bullet / ordered / task lists, table, horizontalRule, image, fileBlock, youtube, blockMath, callout (`> [!KIND]`), details (raw `<details>`), aiBlock (`> [!AI]`), footnote; marks bold, italic, code, strike, link, highlight (`==…==`); mention → `[[label]]`; inline math → `$…$` (`text.ts:37-61`). Lost: underline / subscript / superscript marks, block ids, ordered-list start, image title, table spans, no escaping of Markdown characters. The HTML export keeps underline/sub/sup and table spans (`html.ts:17-44`).15691570**Import details**15711572- Markdown mapping: front-matter (`title, icon, tags, properties, created, updated, section`; other keys become page properties; `id, type, database, cover` ignored — `import/markdown.ts:155-164`); Notion header lines; **wikilinks `[[Target]]`, `[[Target|Alias]]`, `[[Target#anchor]]` → `mention` nodes when the title or archive path resolves, else plain text** (`WIKI_RE`, `markdown.ts:58,76,97`); relative links → `/p/<id>` or `/api/files/<id>`; images resolved inside a ZIP; GFM task lists and tables; `$…$` / `$$…$$` / ```` ```math ```` → math nodes. Tags go to `properties.tags` — **the `tags` / `page_tags` tables are not written by the importer**, and inline `#hashtags` are not parsed. Not parsed back: highlight, callouts, `<details>`, footnotes; headings deeper than 3 become level 3 (`markdown-client.ts:34`).1573- Validation (`src/lib/import/validate.ts`): per-kind size caps (markdown 20 MB, csv 100, bibtex 20, docx 100, pdf 200, json 500, zip 500, `:24`); magic-byte sniffing vs declared kind; ZIP checks on the central directory before inflation — ≤ 20 000 entries, ≤ 2 GB uncompressed, ≤ 250 MB per entry, ratio ≤ 150 above 50 MB (`zip_bomb`), unsafe paths (absolute, drive letter, `..`, NUL, > 1024 chars) refuse the whole archive, encrypted and ZIP64 refused, symlinks skipped (`validateZip`, `:102-118`); a counting stream guards each member at read time (`readEntry`, `src/lib/import/zip.ts:39-48`). Row / column / page count limits: absent. Schema validation of backup rows: absent.1574- Preview (`previewImport`, `src/lib/import/preview.ts:35`): dry run returning `{kind, detected, filename, size, counts, countsAll, tree[≤400], treeTruncated, duplicates{total, pages[], databases[], references[]}, warnings[], modes{skip, import}}`. Duplicate rules: pages by title + normalised text, databases by name + row count, DOCX/PDF by sha256, references by DOI then cite key, backup pages by id.1575- **Job flow**: route → validate → stage the upload at `$DATA_DIR/imports/<jobId>-<name>` (`stageFile`, `src/lib/import/job.ts:27-32`) → job record. **There is no import-jobs table**: the record is a `settings` row keyed `import.<jobId>` (`importKey`, `src/lib/import/pages.ts:190`), with a per-item ledger in the same table under `import.<jobId>.i.<hash>` (`openLedger`, `pages.ts:43-57`) so a retry does not recreate items. Small files run inline; otherwise queue `import-file` (1 retry after 30 s, 3600 s expiry, dead-letter — `src/lib/jobs/queues.ts:15`) → `handleImport` (`src/worker/handlers/import.ts:19`) → `executeJob` (`job.ts:61`: run id, 30 s heartbeat, progress writes throttled to 500 ms, cancellation check) → `runImport` (`src/lib/import/index.ts:19`) → writers in `src/lib/import/pages.ts` (`createPage :113`, `updatePage :125`, `createDatabase :140`, `addRow :155`, `createAttachment :163`). Statuses (`ImportStatus`, `pages.ts:180`): `preview → queued → running → done | error | cancelled`; `stalled` is derived (no progress for 10 min). No transaction around a run. The importer has **its own copy of link maintenance** (`afterContentChange`, `pages.ts:98-110`) rather than calling `src/lib/pages/service.ts`. Staged files are purged after 24 h (previews), 7 days (finished/failed), 14 days (others) (`purgeStaleImports`, `job.ts:44-54`).1576- **Restore** (`src/lib/import/restore.ts`): from the JSON backup only; 15 tables in dependency order (`RESTORE_TABLES`, `:43`), batches of 200; **additive, never deletes**; **ids kept, no remapping** (only `workspaceId` rewritten). Mode `skip` = `onConflictDoNothing` (adds missing rows); mode `import` = `onConflictDoUpdate` on `id` (overwrites existing rows — the path that needs step-up). Attachment files are not restored; a `reindex-all` job is queued afterwards (`:81`). A ZIP export is re-imported as a plain Markdown archive with new ids — `workspace.json` is ignored (`zip.ts:27,85`), so views, property types and options, relations, tasks and row ids do not come back from a ZIP.15771578---15791580## (f) Gap list15811582Status: **exists** / **partial** / **absent**, with the evidence.15831584| Capability | Status | Evidence |1585|---|---|---|1586| Typed graph relations (supports, contradicts, cites, part-of…) | **absent** | `links.kind` only ever `"link"` / `"ai"` (`src/lib/pages/service.ts:106`, `src/lib/graph/service.ts:118`); `knowledge_edges.kind` only `"link"` / `"ai_suggested"` written (`pages/service.ts:107`, `graph/suggest.ts:60`); `concept`, `relation`, `backlink` exist only in the comment `src/db/schema.ts:161`. Database relations carry a property *name* (`sub`), not a semantic type |1587| Temporal graph relations (valid-from/to, event time) | **absent** | `knowledge_edges` has only `createdAt`, `decidedAt` (`schema.ts:163`); `links` only `createdAt` (`:156`) |1588| Graph view — global | **exists** | `GET /api/graph` without `pageId`; `GraphExplorer.tsx:30` |1589| Graph view — local (page neighbourhood) | **exists** | `pageId` + `depth` ≤ 4 server (`graph/route.ts:10`), 1–3 in UI (`GraphExplorer.tsx:66`); `PageGraphCard` depth 1 |1590| Graph view — project | **partial** | only the Section filter value `projects` (`GraphExplorer.tsx:19,64`); no project entity or project-scoped graph |1591| Graph view — chronological | **absent** | only "Edited within" presets filtering nodes by `updated_at` (`GraphExplorer.tsx:20`, `graph/service.ts:47`); no time axis, slider or replay |1592| Graph view — concept | **absent** | no concept/entity nodes; `NodeKind` has none (`graph/types.ts:2`); no entity extraction anywhere in `src/lib/graph` |1593| Graph view — document | **partial** | node-kind chips (page / database / research / row / attachment) and Section `documents`; no dedicated view |1594| Graph view — reference | **absent** | bibliographic `references` are not graph nodes (`buildGraph` reads pages, attachments, tags only, `graph/service.ts:49,78,82`) |1595| Graph intelligence — central concepts | **partial** | degree only: node radius (`GraphCanvas.tsx:24`), dashboard top 6 by degree (`src/app/api/dashboard/route.ts:44`). No PageRank/betweenness, and nodes are pages, not concepts |1596| Graph intelligence — orphans | **partial** | `hideOrphans` (`graph/service.ts:88`), count "N without any link" (`BacklinksExplorer.tsx:43,82`); no orphan list, filter or remediation |1597| Graph intelligence — contradictions | **absent** | no code in `src/lib/graph/**`; the word appears only in chat/agent prompts (`src/lib/ai/prompts.ts`, outside this part, not read) |1598| Graph intelligence — clusters / communities | **absent** | no match for cluster / community / louvain in `src/` |1599| Graph intelligence — open questions | **absent** | not in graph or dashboard code; phrase appears only in `src/lib/agents/definitions.ts` and AI prompts (outside this part, not read) |1600| Unlinked mentions | **exists** | `unlinkedMentions` (`graph/backlinks.ts:37`), exact-title match only |1601| AI link suggestions with rationale, confidence, provenance, undo | **exists** | `graph/suggest.ts`, `graph/service.ts:114-133`, `SuggestionList.tsx` |1602| Database view — table | **exists** | `views/TableView.tsx` |1603| Database view — board | **exists** | `views/BoardView.tsx` (no in-column ordering) |1604| Database view — calendar | **partial** | `views/CalendarView.tsx`: month only, no drag, no multi-day |1605| Database view — timeline | **partial** | `views/TimelineView.tsx`: read-only bars, no drag/resize |1606| Database view — gallery | **exists** | `views/GalleryView.tsx` |1607| Database view — list | **exists** | `views/ListView.tsx` |1608| Rollups | **partial** | `rollup()` (`src/lib/databases/query.ts:51`), 8 aggregates; cannot roll up a target relation / rollup / formula (`:56`); computed in JS on every read |1609| Formulas | **exists** | `src/lib/databases/formula.ts` (≈45 functions); evaluated in JS per row per read; not sortable/filterable in SQL |1610| Grouping | **partial** | client-side only (`group.ts:4`), one level, select/status/checkbox only, on ≤500 loaded rows; no server grouping or aggregates |1611| Bulk actions | **partial** | API: batch create / update / delete / restore / AI (`rows/route.ts`, `ai/route.ts`); UI: multi-select + bulk delete in the table grid only (`TableView.tsx:77`); no bulk edit |1612| Undo (databases) | **partial** | row deletion (`rows/restore/route.ts`) and board move (`BoardView.tsx:49`) only |1613| AI database properties | **partial** | type `ai` + worker (`src/worker/handlers/ai-property.ts`); manual, one cell at a time in the UI, never recomputed automatically, no staleness, no structured output (text only), no retry button on an errored cell (`cells/Cell.tsx:80-81`) |1614| Knowledge timeline / event journal | **absent** | no event table for knowledge changes. Nearest: `page_versions` (page history, `schema.ts:94`), `audit_logs` (security/ops audit, `schema.ts:247`), `inbox_actions` (triage undo journal, `schema.ts:148`), `pages.rev` change sequence for sync (`schema.ts:91`) |1615| Knowledge health dashboard | **absent** | nearest pieces are scattered: index health (`indexHealth`, `src/lib/search/index.ts:131`, Search → Index tab), duplicates (`findDuplicates`, `:115`), orphan count (`BacklinksExplorer.tsx:43`), unlinked mentions. No stale-page, broken-link, untagged or contradiction metrics; no single screen |1616| Daily brief | **absent** | only the manual weekly digest `POST /api/dashboard/summary` (`dashboard/summary/route.ts:16`), single stored value, not scheduled |1617| Smart inbox AI suggestions | **absent** in inbox service, routes and `InboxView` | `src/lib/inbox/service.ts` has no model call. A separate agent proposal kind `inbox_triage` exists (`src/lib/agents/apply.ts:82-90`) — approval-based batch flow, not inline suggestions on inbox items (agents code is outside this part, skimmed only) |1618| Semantic zoom | **absent** | only label visibility by zoom threshold (`GraphCanvas.tsx:68`); no aggregation or level of detail |1619| Living / query blocks (database or query embedded in a page) | **absent** | custom editor nodes are `callout, aiBlock, fileBlock, footnoteRef, footnote` (`src/components/editor/extensions/nodes.ts:7-35`); slash "New database" creates a sub-page (`slash.tsx:61`); `DatabaseView` is mounted only by `PageView.tsx:114` for pages of type database. No linked view of a database, no saved-query block, no transclusion |1620| Tag management (rename / merge / delete / hierarchy / counts) | **absent / broken** | `/api/tags` GET only; count always 0 (D1) |1621| Task ↔ document to-do synchronisation | **absent** | `tasks` table is independent of `taskItem` nodes; one-shot copy at inbox triage only (`inbox/service.ts:70-72`) |1622| Search: stemming / language awareness | **absent** | `'simple'` config (`schema.ts:85,178`; `search/index.ts:73`) |1623| Search: database row *values* searchable | **absent** | `handleIndex` indexes pages and attachments only (`src/worker/handlers/index-source.ts:18`) |1624| LaTeX export | **absent** | no `.tex` writer in `src/lib/export/**`; `FORMATS` (`src/app/api/export/route.ts:25`) has no such value |1625| PDF export (server-generated) | **absent** | `format=pdf` returns `text/html` (`export/route.ts:75-84`) |1626| DOCX export | **absent** | `mammoth` is import-only |1627| HTML import | **absent** | not in `KINDS` (`src/lib/import/validate.ts:17`) |1628| Lossless ZIP round-trip of databases | **absent** | `workspace.json` ignored on import (`src/lib/import/zip.ts:27,85`); types re-inferred from CSV |1629| Full backup / restore | **partial** | JSON backup + additive restore; attachment files excluded from the JSON, no transaction, no row validation (`src/lib/import/restore.ts`) |16301631---16321633## (g) Confirmed defects16341635All confirmed by reading the cited lines; none was executed. "(UI)" items come from the delegated component read.16361637### Server16381639- **D1 — `GET /api/tags` always returns `pages: 0`.** `src/lib/inbox/service.ts:107`. Correlated sub-select written with `${schema.tags.id}` inside a single-table drizzle select; the column is rendered unqualified and binds to `pages.id` inside the sub-select. Full analysis in section (e) → Tags. Not executed.1640- **D2 — bare `uuid.parse()` turns bad input into HTTP 500 instead of 400.** `ZodError` is not an `HttpError`, so `jsonError` (`src/lib/auth/guard.ts:51`) answers 500 "Internal error". Sites: `src/app/api/databases/[id]/rows/route.ts:30` (DELETE `id`/`ids`), `src/app/api/databases/[id]/properties/route.ts:20` (DELETE `id`, also when missing), `src/app/api/databases/[id]/views/route.ts:9` (DELETE `id`), `src/app/api/tasks/route.ts:41` (DELETE `id`), `src/app/api/inbox/[id]/route.ts:13` (path id). Same pattern just outside this part: `src/app/api/pages/[id]/tags/route.ts:5,7`.1641- **D3 — `GET /api/graph` accepts a non-UUID `pageId`.** `src/app/api/graph/route.ts:13` validates with `/^[0-9a-f-]{36}$/`, which accepts e.g. 36 hyphens or a mis-grouped hex string; the value reaches `::uuid[]` casts (`src/lib/graph/service.ts:20,48`) → Postgres error → 500. (Values such as `att:<uuid>` / `tag:<uuid>` sent by the Inspector's "Neighbourhood" button on non-page nodes fail the regex and silently return the **whole graph** instead — `Inspector.tsx:33`, `GraphExplorer.tsx:74`.) Also `depth` can never be 0: `Number(…) || 1` (`route.ts:10`).1642- **D4 — CSV import without a header row → 500.** `src/lib/databases/csv.ts:17` throws a plain `Error`. A non-multipart body also → 500 (`req.formData()` rejection, `src/app/api/databases/[id]/import/route.ts:6`).1643- **D5 — the database query engine loads and hydrates the whole database on every request.** `src/lib/databases/query.ts:132-137`: no LIMIT in `loadRaw` (`:11-13`); relations, rollups and formulas computed for every row; filter (unless fully pushable), search, sort and pagination in JS. `limit`/`offset` only slice the result. Every grid load, CSV export, and each keystroke of the search box and filter inputs pays this (UI sends one request per keystroke: `Toolbar.tsx:105,126`, `FilterBuilder.tsx:68`).1644- **D6 — per-row write loops (N+1), no transaction.**1645 - `createRow` (`src/lib/databases/service.ts:142-151`) runs `ensureDatabase` + `loadProps` + position `max()` + `createPage` (itself a `max()` query, insert, link sync, index enqueue) + insert + `getRows` (full hydrate) per row; called in a loop by `POST rows {rows}` (`rows/route.ts:16`, ≤500) and by `importCsv` (`csv.ts:31-35`, unbounded row count within 20 MB) **inside the HTTP request**. A failure midway leaves a partial import.1646 - `PATCH rows {updates}` (`rows/route.ts:24`): same per-row cost, ≤500.1647 - `reorderRows` / `reorderProperties` (`service.ts:164-167`, `:102-105`): one UPDATE per id (≤5000 for rows).1648 - `convertValues` (`service.ts:107-118`): one UPDATE per row on a type change.1649 - `deleteRows` (`service.ts:173`): `softDeletePage` per row, errors swallowed (`.catch(() => {})`) — a row can end up deleted while its page is not.1650 - `rows/restore` (`rows/restore/route.ts:22`): `restorePage` per row.1651 - `duplicateDatabase` with rows (`service.ts:221-225`): `createPage` + insert per row.1652- **D7 — partial row reorder corrupts ordering.** `reorderRows` assigns `position = i + 1` to the ids it receives and leaves the others untouched (`service.ts:166`); the table sends only loaded / filtered ids (`TableView.tsx:62,72` — reorder is allowed while a filter is active or when the database has more than 500 rows), so reordered rows collide with the positions of rows that were not sent.1653- **D8 — AI cell can stay "pending" forever.** `enqueueAi` writes `{pending:true}` then calls `enqueue` and ignores its result (`service.ts:184-185`); `enqueue` returns `null` when the queue is unreachable or the job is de-duplicated (`src/lib/jobs/index.ts:25-27`). The route still answers `queued: n` (`ai/route.ts:8`). Nothing clears a stale pending marker, and the UI polls every 2.5 s while one exists (`hooks.ts:49`). Related (UI): an errored AI cell offers neither Generate nor Regenerate (`cells/Cell.tsx:80-81` with `isEmpty` treating `{error}` as empty, `values.ts:9`).1654- **D9 — rollups over computed or relation target properties are always empty.** `query.ts:56` reads `t.values[tp.id]` from raw target rows (`:31`), but relation, rollup and formula values are never stored in `values`. The UI excludes those target types from the picker (`PropertyEditor.tsx:104`), the API does not.1655- **D10 — filter rules are not validated.** `query/route.ts:5`: `op` is any string; an unknown operator or unknown property matches every row (`query.ts:70,95`), so a typo silently disables a filter. In a group carrying both `and` and `or`, `and` is ignored (`query.ts:100-101`; `sqlFilter` `:105` likewise).1656- **D11 — CSV export silently truncates at 1000 rows.** `src/app/api/databases/[id]/route.ts:10`, `query/route.ts:16`.1657- **D12 — `decideSuggestion` does not check the edge kind.** `src/lib/graph/service.ts:115` selects by id + workspace only (unlike `revertSuggestion`, `:128`); a PATCH with the id of a `kind="link"` mirror edge flips its status and inserts or deletes a `links(kind="ai")` row.1658- **D13 — unlinked-mention search is an unindexed self-join.** `src/lib/graph/backlinks.ts:39-46`: `pages × pages` with `strpos(lower(plain_text), lower(title))`, executed on every `GET /api/graph/backlinks` together with `backlinkStats` (three correlated sub-selects per page, `:16-19`), with no pagination; the `LIMIT 400` applies after the join. Dismissals live in one JSON array in `settings`, read-modify-written without a lock (`:29-33`) — concurrent dismissals can lose entries. Matching is plain substring (a short title matches inside longer words).1659- **D14 — `findDuplicates` is a full self-join over all chunk embeddings.** `src/lib/search/index.ts:117-120`: the `a.embedding <=> b.embedding <= x` join predicate cannot use the HNSW index, so cost grows with (number of chunks)². `threshold` is unvalidated `Number()` (`src/app/api/search/duplicates/route.ts:3`); a non-numeric value reaches SQL as NaN (resulting comparison behaviour not tested).1660- **D15 — `quickSearch` and `backlinkStats` do not escape LIKE wildcards.** `src/lib/search/index.ts:111` (`"%" + q + "%"`), `src/lib/graph/backlinks.ts:14`; `%` or `_` in the query act as wildcards (the database code does escape, e.g. `query.ts:115`).1661- **D16 — `pages.tsv` and its GIN index are maintained but never queried.** `schema.ts:85,93`; only `chunks.tsv` is used (`search/index.ts:73`). Write cost with no reader.1662- **D17 — tasks: unknown `pageId` → 500.** `src/app/api/tasks/route.ts:21,33` insert/update `page_id` without checking that the page exists in the workspace (FK violation → 500; a page id from elsewhere would be accepted if it existed). Status vocabularies differ on the same table (`todo|done` here, `todo|doing|done` in `src/lib/research/tasks.ts:7`); `GET /api/tasks` without `done=1` returns `doing` tasks, and the UI toggle sends them to `done` or `todo`.1663- **D18 — inbox triage is not transactional and "database" duplicates content.** `src/lib/inbox/service.ts:60-90`: side effects (tasks, row, reference) are created before the page update and the journal insert, with no transaction — a failure in between leaves created objects with no undo record. The `database` action copies the capture's content into a new row page and archives the original (`:75-76`), leaving two pages with the same body (both indexed, likely to surface in Duplicates).1664- **D19 — link maintenance on save is not atomic.** `afterContentChange` (`src/lib/pages/service.ts:100-111`) deletes and re-inserts `links` and `knowledge_edges` in separate statements outside a transaction, after the page row is already written; a concurrent reader can see a page with no outgoing links, and a crash between statements leaves them missing until the next save.1665- **D20 — relation targets are not validated.** `setRelations` (`service.ts:125-129`) inserts any existing row id regardless of `targetDatabaseId` or workspace; hydration then shows rows of another database (`query.ts:31,42`).1666- **D21 — two-way relation leftovers.** Deleting the *reverse* (synced) property removes only itself (`deleteRelationPair`, `service.ts:90`); the forward property keeps `config.twoWay = true` with no counterpart. Changing a synced property's type keeps the shared relation rows (`:82` → early return at `:90`).1667- **D22 — dashboard "suggested" count disagrees with the review queue.** `src/app/api/dashboard/route.ts:38` counts every `knowledge_edges.status='suggested'`; `suggestionCounts` excludes pairs already linked (`src/lib/graph/service.ts:108-109`).1668- **D23 — `limit` on `GET /api/databases/[id]/rows` is not validated.** `rows/route.ts:8`: `Number("abc")` → NaN → empty result instead of 400; negative values pass `Math.min`.1669- **D24 — user-supplied regex in formulas.** `test(s, regex)` builds `new RegExp()` from the expression (`formula.ts:73`) and runs server-side for every row on every read; a catastrophic pattern blocks the Node event loop. Single-owner app, so self-inflicted only.16701671### UI (from the delegated read; main ones)16721673- **U1** Desktop number cell erases the value on non-numeric input (`src/components/database/cells/Cell.tsx:58`).1674- **U2** Rows beyond 500 unreachable; group counts, calendar, timeline computed on that slice (`hooks.ts:48`).1675- **U3** Stale `total` after delete (`hooks.ts:52,75`); two "default" views in cache until refetch (`hooks.ts:83-84`); every view-config patch (including each column resize) refetches rows (`hooks.ts:84`).1676- **U4** Filter text input: one PATCH + one row refetch per keystroke, with a possible roll-back race (`FilterBuilder.tsx:25,68`).1677- **U5** Grid key handler swallows Enter on in-cell buttons (`views/TableView.tsx:68`).1678- **U6** Calendar "+N more" not clickable (`views/CalendarView.tsx:85`); undated rows capped at 5 in calendar and timeline (`CalendarView.tsx:60-61`, `TimelineView.tsx:73-74`).1679- **U7** Layout change resets view config to defaults (`ViewTabs.tsx:135`).1680- **U8** Template list has no error state → permanent spinner on a fresh database (`TemplateChooser.tsx:18`, `DatabaseView.tsx:139`).1681- **U9** Load failures displayed as empty states: Tasks "Inbox zero" (`TasksInbox.tsx:83,124`), Trash "Trash is empty" (`TrashView.tsx:20,42`), Duplicates tab (`SearchPage.tsx:54,113`); Index tab skeleton forever (`SearchPage.tsx:53,103`).1682- **U10** Search hard cap 40, no pagination (`SearchPage.tsx:47`); archive and trash lists capped at 100 (`ArchiveView.tsx:14`, `TrashView.tsx:20`).1683- **U11** `BacklinksExplorer` filters the whole payload client-side, no pagination (`BacklinksExplorer.tsx:37,41-42`).1684- **U12** Task quick-add false positives: bare `sun|sat|mon…` and "3 days" inside a title become due dates (`src/components/tasks/parse.ts:29,35`); `at 99` accepted (`:25`); "next monday" = "monday" (`:30,35`).1685- **U13** Task delete button invisible on touch, no confirmation or undo (`TasksInbox.tsx:75,106`); due date hidden below `sm` (`:74`).1686- **U14** `GraphCanvas`: `onEdgeClick` never wired, pin-on-drag unreachable (`GraphCanvas.tsx:20,91,95`); `ConnectionsList` shows "No connection yet" for pages with only tag/file edges (`ConnectionsList.tsx:53,106`).16871688Full UI defect lists (30 database items, 47 others) were kept in the working notes `/tmp/spnote-next/c-work/database-ui.md` §12 and `/tmp/spnote-next/c-work/other-ui.md` §8.16891690### Import / export (from the delegated read; main ones)16911692- **I1** Malformed `parentId` → 500: bare `uuid.parse` (`src/app/api/import/route.ts:98`).1693- **I2** Empty CSV → 500 `import_failed` flagged retryable: plain `Error` (`src/lib/import/csv.ts:38`); inside a ZIP one empty `.csv` fails the whole archive although the preview says it will be skipped (`src/lib/import/preview.ts:56` vs `zip.ts:161`).1694- **I3** Restore has no row validation and no transaction (`src/lib/import/restore.ts:18-42,60-74`): malformed rows surface as raw Postgres errors; a failure at table N leaves tables 1..N-1 applied (already overwritten in `import` mode).1695- **I4** Restore conflicts only on `id` (`restore.ts:30,37`): rows with a new id but an existing unique tuple (`links_unique`, `tags_unique`, `references_key_unique`, `relations_unique`, `kedges_unique`, `page_versions_unique`) abort the restore in `import` mode, and in `skip` mode a skipped tag leaves `pageTags` rows pointing at a missing tag id. Link rows get new ids on every save (`src/lib/pages/service.ts:102-106`, confirmed), so this state is normal. Outcome inferred, not executed.1696- **I5** The ZIP manifest `workspace.json` carries the same `format` string as the JSON backup (`src/lib/export/zip.ts:81`, `json.ts:6`) with a different shape; uploaded as a backup it passes `checkBackupFormat` (`restore.ts:45`) and fails later in the database.1697- **I6** Whole upload buffered in memory, twice (`req.formData()` + `arrayBuffer()`, `import/route.ts:94,100`; up to 500 MB); JSON backup parsed as one string for preview and again for import (`src/lib/import/index.ts:39`, `preview.ts:92`); JSON export builds one string with every table and has no size cap (`src/lib/export/json.ts:10-35`).1698- **I7** `loadExportSet` loads all attachments (with `extractedText`), references, experiments, tags and tasks of the workspace for any export, including a single page (`src/lib/export/data.ts:44-55`).1699- **I8** Every import and every preview loads and hashes the `plainText` of every live page for duplicate detection (`src/lib/import/pages.ts:66-67,78`).1700- **I9** CSV-to-new-database import: about 8 sequential statements per row, including a `max(position)` over all siblings (quadratic) (`src/lib/import/csv.ts:58-64`, `pages.ts:113-123,155-162`); ZIP import creates then updates each page, running link maintenance and index enqueue twice (`zip.ts:132-144,175-182`).1701- **I10** Decimal comma: `1,5` is stored as **15** (`src/lib/import/csv.ts:11` + `src/lib/databases/values.ts:10`); long digit strings become lossy JS numbers; a 0/1 column becomes a checkbox (`csv.ts:15-16`); duplicate headers overwrite each other (`csv.ts:48,52`); papaparse `errors` never read (`csv.ts:23`, `src/lib/databases/csv.ts:16`).1702- **I11** Markdown import turns any text with two `$` on a line into inline math — "costs $5 and $10" (`src/lib/editor/markdown-client.ts:24`).1703- **I12** Markdown round-trip losses: underline/sub/sup dropped on export (`src/lib/editor/text.ts:37`); highlight, callout, aiBlock, details, footnotes exported but not parsed back (`text.ts:37,57-60` vs `markdown-client.ts`); headings 4–6 → 3 (`markdown-client.ts:34`); no escaping of Markdown characters on export (`text.ts:37`); front-matter keys with accents or parentheses are exported but skipped on import (`src/lib/import/markdown.ts:30,38`).1704- **I13** ZIP round-trip losses: `created` date lost for pages inside a ZIP (`zip.ts:141` + `markdown.ts:163`); row pages with the same title collide in a `Map` and all but one body are silently dropped (`zip.ts:158`); attachments not referenced in a body are dropped (`zip.ts:184-185`); attachments whose filename contains a space are written unencoded and cannot be matched by `IMG_RE` on import (`src/lib/export/data.ts:108`, `export/markdown.ts:43`, `import/markdown.ts:57`).1705- **I14** Wikilinks / images inside `~~~` fences, indented code and inline code are rewritten by the importer (`import/markdown.ts:66-67,90-104`); the first `# heading` of every Markdown source is removed and leading `Key: value` lines are moved into properties (`parseNotionHeader`, `markdown.ts:45-53`).1706- **I15** Encoding: "text" means "no NUL byte in the first 8192 bytes"; Latin-1 / Windows-1252 files import with replacement characters (`src/lib/import/validate.ts:58-60,140`).1707- **I16** No neutralisation of CSV cells starting with `= + - @` on export (`src/lib/export/csv.ts:25-26`, `src/lib/databases/csv.ts:11`).1708- **I17** Whole-workspace BibTeX export needs no step-up, unlike the other whole-workspace exports (`src/app/api/export/route.ts:27,70-74`) — observed behaviour, intent not documented. The 400 message for a bad `format` omits `html` (`:31`).1709- **I18** Concurrency: two confirm requests can both start the same previewed job (no compare-and-set, `import/route.ts:83-89`); the ledger claim ignores a lost race (`src/lib/import/pages.ts:50-55`).17101711Full list (72 items) in the working notes `/tmp/spnote-next/c-work/import-export.md` §5.171217131714---17151716# Lead-auditor sections 10–1217171718## 10. Worker / jobs17191720`src/worker/index.ts` (pg-boss 12, schema `pgboss`, pool 4; web process uses its own pg-boss client with pool 3 only to enqueue, `src/lib/jobs/index.ts`). All consumers `batchSize: 1`, wrapped by `src/worker/run.ts::wrap` (in-process timeout, one log line with numeric/id-only summary). Policies in `src/lib/jobs/queues.ts`:17211722| Queue (const) | Handler | Payload | Retry / expiry | What it does |1723|---|---|---|---|---|1724| `index-source` (`QUEUES.index`) | `handlers/index-source.ts::handleIndex` | `{sourceType: page\|attachment\|row, sourceId, workspaceId}` | 4 retries, 15 s backoff, expire 300 s | page: back-fills block ids in place if missing (no version bump), then `indexSource` (chunk + embed); deleted → `removeSource`. attachment: indexes `filename + extractedText`. **`row` is ignored** (`{ignored:true}`). Polling 2 s. |1725| `reindex-all` | `handleReindexAll` | `{workspaceId}` | 1 retry, 1 h | sequential re-index of all live pages and attachments |1726| `import-file` | `handlers/import.ts::handleImport` | see section 5/6 | 1 retry, 1 h | staged import job |1727| `backup` | `handlers/backup.ts::handleBackup` | `{kind?, backupJobId?, dryRun?}` | 0 retry, 4 h | spawns `scripts/backup.sh` (pg_dump + uploads → AES-256 tar, sha256, prune, rsync offsite), records `backup_jobs`; fails if `BACKUP_PASSPHRASE` unset. Scheduled `30 3 * * *` America/Toronto. |1728| `agent-run` | `handlers/agent.ts::handleAgentRun` | `{executionId}` | 0 retry, 900 s | `reapStale()` then `runExecution` (atomic pending→running) |1729| `suggest-links` | `handlers/suggest-links.ts` | `{workspaceId, pageId?, k?, limit?}` | 0 retry, 600 s | one page (k=8) or workspace pass (k=5/page) via `src/lib/graph/suggest.ts` |1730| `ai-property` | `handlers/ai-property.ts` | see section 6 | 1 retry, 300 s | computes AI database cells |1731| `maintenance` | `handlers/maintenance.ts` | `{}` | 0 retry, 300 s; scheduled `17 * * * *` | deletes idempotency keys > 48 h, expired challenges, login attempts > 30 d, old enrollments, dead sessions > 90 d, inbox_actions > 90 d; marks backups "running" > 6 h as failed; deletes orphan `chunks` / `index_state` whose page/attachment row no longer exists. **Never purges the trash, never deletes attachment files, never hard-deletes soft-deleted attachments.** |1732| `dead-letter` | — | — | kept 30 d | copy of jobs that exhausted retries |17331734Heartbeat: `settings['worker.heartbeat'] = {at, pid, version}` every 30 s. `enqueue()` swallows errors and returns null (a save succeeds even if the queue is down; the page is then simply not re-indexed until the next edit or a reindex-all).1735No queue exists for: transcription, OCR, thumbnailing, daily brief, graph analytics, scheduled agents, notifications/reminders, embedding of database rows.17361737## 11. Tests and scratch environments17381739Suites (counts by `test(` occurrences, not executed by this audit):1740- Unit, `pnpm test` = `tsx --test --test-force-exit --test-concurrency=1 tests/unit/*.test.ts` (node:test; needs a scratch DB — `tests/helpers/testdb.ts` throws unless the database name ends in `_dev|_test|_scratch`): `webauthn.test.ts` 15, `pages.test.ts` 9, `import-validate.test.ts` 8, `jobs.test.ts` 7, `import-bibtex-roundtrip.test.ts` 5, `import-zip-plan.test.ts` 5, `sync.test.ts` 5, `client-logic.test.ts` 4 → **58 unit tests**. `tests/helpers/softauthn.ts` = software authenticator.1741- E2E, `pnpm test:e2e` (Playwright, Chromium only, 1 worker, base `E2E_BASE_URL` default `http://localhost:8481`): `auth.spec.ts` 10, `mobile.spec.ts` 12, `mobile-data.spec.ts` 3, `desktop.spec.ts` 4 → **29 specs**; projects `auth`, `phone-360` (360×740), `iphone-se` (375×667), `iphone-max` (430×932), `desktop` (1280×800) — mobile specs run 3× → 59 test runs. `global-setup.ts` wipes passkeys/sessions on the TEST db, opens a CLI enrollment window, enrolls with Chrome's virtual authenticator, saves storage state. Manual scripts: `tests/e2e/{debug,live-ai,overflow,probe,shots,shots2}.mjs`, `scripts/smoke.sh`.1742- Not covered by any test (by file inventory): databases (query/formula/views), graph, search/RAG, agents, AI routes, research, export. No component tests. No test asserts `GET /api/tags` counts (the tags test at `tests/unit/pages.test.ts:90` checks names only — which is why D-1 went unnoticed). `tests/unit/sync.test.ts:38` explicitly documents that purging a parent orphans its child ("the child row is orphaned by the model, not deleted").17431744Scratch environments on M4M64c (git worktrees of the same repo; `git worktree list`):17451746| Worktree | Branch @ commit | DB | PORT / APP_URL | DATA_DIR | State on 2026-11-19 |1747|---|---|---|---|---|---|1748| `~/apps/note-spboucher-ai` | `main` @ 77ef5f0 | `note_spboucher` | 8480 / https://note.spboucher.ai | `~/apps/note-spboucher-ai/data` | PM2 note-web + note-worker, online |1749| `~/dev/note-v2` | `v2` @ 65b3e16 (untracked `.spn-schema.mts`) | `note_dev` | 8481 / http://localhost:8481 | `~/dev/note-v2-data` (+ `~/dev/note-v2-backups`) | not listening |1750| `~/dev/note-native` | `native-sync` @ 31b354d | `note_native_dev` | 8482 / http://localhost:8482 | `~/dev/note-native-data` | **still running**: `next-server` pid 31220 bound to 127.0.0.1:8482 since 2026-11-18 14:48, started by hand (not PM2) with output to `~/dev/note-native-data/web.log` and `web.pid`; no worker process seen for it |1751| `~/dev/note-next` | `next` @ 77ef5f0, clean | `note_next_dev` (exists) | 8483 / http://localhost:8483 | `~/dev/note-next-data` (uploads/, backups/) | **already created today 12:30** (not by this audit); nothing listening on 8483 |17521753`.env` variable NAMES in the dev worktrees (identical set in note-v2 and note-native): `DATABASE_URL OPENAI_API_KEY ANTHROPIC_API_KEY BACKUP_PASSPHRASE BACKUP_KEEP_DAYS LOG_LEVEL TZ PORT APP_URL APP_VERSION DATA_DIR BACKUP_DIR BACKUP_OFFSITE` (no `NODE_ENV`, so cookies are named `note_session` / `note_wa` and are not `Secure`; `AUTH_EXTRA_ORIGINS` is honoured only when `NODE_ENV !== "production"`). Documented recipe (`docs/testing.md:14`): worktree outside `~/apps`, own `.env`, `pnpm db:migrate`, `pnpm build`, `next start -p <port>`, `tsx src/worker/index.ts`; native testing gets a session with `tsx scripts/dev-session.ts` (prints a token for the cookie). Rule in `docs/dev-conventions.md:9`: never build or start anything inside `~/apps/note-spboucher-ai`.17541755## 12. Defects / debt confirmed in code17561757Reported by the native team — all six confirmed:17581759- **D-1 `GET /api/tags` counts 0 pages** — `src/lib/inbox/service.ts:107` (`listTags`). The count is a raw correlated subquery: `` dsql`(select count(*)::int from page_tags pt join pages p on p.id = pt.page_id where pt.tag_id = ${schema.tags.id} and p.deleted_at is null)` `` inside `db.select({...}).from(schema.tags)`. In a single-table Drizzle select, an interpolated column is rendered **unqualified** (`"id"`). Inside the subquery, `"id"` binds to the nearest scope — `pages p` (page_tags has no `id`) — so the predicate becomes `pt.tag_id = p.id`, never true → 0. Fix: qualify explicitly (`tags.id` as raw text / `sql.raw`) or use a join + group by. Also affects the `tags` array semantics nowhere else (`/api/sync` drops the count). Rendering rule verified in the installed library (`node_modules/.pnpm/drizzle-orm@0.45.4…/drizzle-orm/pg-core/dialect.js:142-170`, `buildSelection` with `isSingleTable = !joins || joins.length === 0`: `PgColumn` chunks inside an SQL field become a bare `sql.identifier(column)`). The query itself was not executed (production has 0 tags).1760- **D-2 Purging a page leaves its attachments** — `src/lib/pages/service.ts:124-128` (`purgePage`) and `src/app/api/trash/route.ts:8-13` (`purge`) only `DELETE FROM pages`. `attachments.page_id` is `ON DELETE SET NULL` (`schema.ts:133`), so the rows survive with `page_id = null`, the bytes stay in `DATA_DIR/uploads`, the extracted text stays indexed (chunks with `source_type='attachment'` are untouched) and remains reachable by search/RAG and by `/api/files/<id>`. Nothing ever deletes an attachment row or file: grep finds no `unlink`/`rm` on uploads and no `db.delete(schema.attachments)`; the maintenance job does not do it either. Corollary: attachment tombstones can only ever be produced by a workspace cascade, i.e. never.1761- **D-3 Malformed ids answer 500** — `src/lib/api.ts:72` exports `uuid = z.string().uuid()` and 21 route files call `uuid.parse(params.id)` (38 call sites, e.g. `src/app/api/pages/[id]/route.ts:8,15,24,31`, `files/[id]/route.ts:9`, `tasks/route.ts:41`). `parse` throws `ZodError`, which is not an `HttpError`; `handler` (`api.ts:32-35`) logs it and `jsonError` (`guard.ts:51`) returns `500 Internal error`. No `ZodError` handling exists anywhere (grep: 0). Second family: routes that pass the raw string to Postgres with no validation → `invalid input syntax for type uuid` → 500: `GET /api/pages?view=children:<x>` (`pages/route.ts:20`), `DELETE /api/attachments?id=` (`attachments/route.ts:38`, also crashes on a missing id), `GET /api/attachments?pageId=`. (The `src/app/api/databases/[id]/*` routes are NOT affected for the path id: `ensureDatabase` (`src/lib/databases/service.ts:17`) tests a UUID regex and answers 400; their query-string ids are — see section 6 defects D2/D3.) Fix once in `handler`: map `ZodError` → 400 and Postgres `22P02` → 400/404.1762- **D-4 No cycle check on `parentId`** — `src/lib/pages/service.ts:45`: the only guard is `patch.parentId === id`. A→B then B→A is accepted; `parentId` existence/workspace is not checked on PATCH either (POST nulls an unknown parent, `pages/route.ts:30`), and the column has no FK (`schema.ts:78`). Consequences in code: `descendants()` (`service.ts:129`) is a `WITH RECURSIVE … UNION ALL` with no depth/cycle guard → **non-terminating query** on a cycle, reached by `softDeletePage`, `restorePage`, `purgePage`; `breadcrumb()` is bounded (`t.d < 20`) but returns garbage; the cycle disappears from any root-anchored tree UI.1763- **D-5 `/api/files/<id>` serves a soft-deleted file** — `src/app/api/files/[id]/route.ts:10`: the lookup filters on id + workspace only, no `isNull(attachments.deletedAt)`. Combined with `cache-control: … max-age=31536000, immutable` (line 15), a deleted file also stays in every client cache for a year. (Whether to keep serving while the *page* is merely in the trash is a product decision; today the attachment's own `deleted_at` is ignored.)1764- **D-6 Tasks have no change feed** — `tasks` has no `rev` column and no trigger (`drizzle/0002_native_sync.sql` touches only pages, attachments, page_tags); `/api/sync` does not return tasks; `GET /api/tasks` has no `since`/`updatedAfter` filter and hides soft-deleted rows, so a client cannot learn about a deletion; `POST /api/tasks` accepts no client id (offline creation → duplicates on retry unless `Idempotency-Key` is sent). Same gap for every other entity listed in 1.3.17651766Additional defects / debt found during this audit:17671768- **D-7 Purging a parent orphans its children instead of deleting them.** `purgePage` computes the descendant ids but only deletes their *chunks*, then deletes the single root row (`service.ts:125-127`); with no FK on `parent_id` the children stay (trashed, `parent_id` dangling, de-indexed). The unit test documents it (`tests/unit/sync.test.ts:43`). A later restore of such a child shows a top-level page whose index rows were deleted only if a re-index runs (restore does enqueue it). The API doc string says "hard delete"; the user-visible effect of "Delete forever" on a tree is partial.1769- **D-8 Trash purge leaves index rows until the hourly job.** `src/app/api/trash/route.ts:11` deletes pages without deleting chunks (soft delete already removed page chunks, so this matters only for pages re-indexed while trashed); orphans are swept by `handleMaintenance` (`maintenance.ts:17`).1770- **D-9 `softDeletePage` / `restorePage` do not verify the root id.** `service.ts:113-123`: `descendants()` is workspace-scoped but the root id is pushed unconditionally and the UPDATE is `WHERE id IN (...)` with no workspace filter and no existence check → `DELETE /api/pages/<unknown uuid>` answers `{ok:true, trashed:1}`; restore likewise (and restore un-trashes ALL descendants, including ones trashed separately earlier). Harmless with one workspace, wrong as an API contract.1771- **D-10 `touchOpened(id)`** (`service.ts:140`) has no workspace filter (same remark).1772- **D-11 The back-fill of block ids changes `content` without changing `version`** (`src/worker/handlers/index-source.ts:6`): the trigger bumps `rev`, so native clients receive a page whose content differs but whose `version` is the one they already hold; a client that then PATCHes with that `baseVersion` overwrites nothing harmful (ids are re-ensured server-side) but any client-side "same version ⇒ same content" assumption is false.1773- **D-12 `knowledge_edges` has no FK to pages** (`schema.ts:160`): purged pages leave edges behind. Production shows 8 `link/accepted` edges for 1 row in `links` — consistent with stale edges (not inspected further: content was out of scope). `afterContentChange` only cleans edges whose `from_id` is the saved page.1774- **D-13 Server-side document validation is structural only** (`src/lib/editor/validate.ts`): `type:"doc"`, ≤ 8 MB serialized, depth ≤ 60, ≤ 200 000 nodes, each node `type` a string ≤ 64 chars, `text` a string, `marks[].type` a string, and no `javascript:`/`vbscript:`/`data:text/html` in `attrs.href|src` or mark `href`. **There is no allow-list of node types or attrs**: any new block type passes today (good for the upgrade) — and so does any garbage (unknown nodes are stored, then dropped/crash at render depending on Tiptap's schema check on the client). `content` in the zod schemas is `z.any()`.1775- **D-14 Idempotency replay is keyed by `key` only** (`src/lib/api.ts:48`): the stored `session_id` is never compared. Single owner today; a latent cross-session replay otherwise.1776- **D-15 Attachment upload is fully buffered and synchronous**: `Buffer.from(await file.arrayBuffer())` for up to 200 MB plus PDF/DOCX text extraction inside the request (`attachments/route.ts:23-25`); `extractedText` for text-like files is capped at 2 000 000 chars, PDF/DOCX uncapped. No Range on download (2.4). Painful for audio/video/voice notes and annotatable PDFs.1777- **D-16 MIME is taken from the client** (`file.type`), the block-list is 4 types, and `image/svg+xml` is served inline (mitigated by a per-response CSP). No sniffing.1778- **D-17 Free-text enumerations**: `pages.type`, `pages.section` (PATCH accepts any string), `tasks.status`, `knowledge_edges.kind`, `database_properties.type`, `database_views.type` have no CHECK constraint; validity lives only in zod schemas of some routes.1779- **D-18 Full-text search uses the `'simple'` configuration** for both `pages.tsv` and `chunks.tsv` (no stemming, no stop words, no accent folding in the generated column — `unaccent` is installed but not used in the generated expressions): French/English morphological matching relies entirely on the vector side and the title trigram index.1780- **D-19 Dead columns / leftovers**: `users.password_hash` (`'!'`), `totp_secret`, `totp_enabled`, `sessions.mfa_passed`, `.gitignore.bak` committed, `knowledge_edges.kind` values `backlink|relation|concept` documented but (per sub-audit, section 5) not all produced; `handleIndex` accepts `sourceType: "row"` and ignores it (database rows are not embedded as rows).1781- **D-20 Embedding dimension is compiled in** (`vector(1536)`, `schema.ts:9`; HNSW index): switching to a local embedding model with another dimension requires a migration + full re-embed; `embeddings.model/version` and `index_state.model/version` exist to detect staleness.1782- **D-21 `tags` in `/api/sync` are sent whole on every call** and carry no id; tag deletion/rename does not exist at all (2.5).1783- **D-22 `GET /api/pages`, `/api/attachments`, `/api/tasks` have hard caps (100 / 200 / 500) and no cursor**: fine at 7 pages, not a paging contract.1784- **D-23 Trash auto-purge is client-triggered** (`POST /api/trash {action:"purge-old"}` needs a fresh passkey); nothing purges on a schedule.17851786---17871788# 13. Consolidated gap list vs the "SP Note Next Generation" brief17891790Status = exists / partial / absent. Detailed evidence tables are in Part A ("Gaps list for the upgrade"), Part B ("(e) Gap list") and Part C ("(f) Gap list"); this table is the single index. Where two sub-audits judged the same item, the stricter reading is kept and the nuance stated.17911792## 13.1 Block types (editor nodes — `src/components/editor/extensions/nodes.ts`, `Editor.tsx`)17931794| Item | Status | Evidence |1795|---|---|---|1796| Drawing | absent | no drawing node, no canvas library in `package.json` |1797| Diagram / Mermaid | absent | no `mermaid` anywhere; `codeBlock` is lowlight highlighting only |1798| Data / chart | absent | no chart library; only hand-written SVG in `src/components/ai/models/Scatter.tsx`, `UsagePanel.tsx` |1799| Scientific citation | absent as a block (partial as a feature) | no node bound to a `references` row; citations reach a page as plain `\cite{key}` text or a Markdown quote (`src/lib/research/cite.ts:48`); only generic `footnoteRef`/`footnote` nodes; "citing pages" found by regex on `plain_text` (`src/lib/research/references.ts:224`) |1800| Formula / math | exists | `inlineMath`, `blockMath` (`@tiptap/extension-mathematics` + KaTeX); edited through `window.prompt` only (`Editor.tsx:85`) |1801| Annotatable PDF | absent | PDF = `fileBlock` link opened in a new tab; server text extraction only (`src/lib/files/extract.ts`); no pdf.js, no annotation model; `/api/files` has no Range support |1802| File | exists | `fileBlock` atom (`nodes.ts:21-26`), upload with progress; name/size only, no preview |1803| Interactive table | partial | Tiptap `table` with resizable columns; row/column ops only in `MobileToolbar.tsx:52-57`; no sort/filter/formula; a database cannot be embedded in a page |1804| Enriched tasks | partial | `taskItem` has only `checked` (no id — not in the block-id list — no due date/priority); separate `tasks` table + `/tasks` screen + NL parser (`src/components/tasks/parse.ts`); the two are not synchronised (one-shot copy at inbox triage, `src/lib/inbox/service.ts:70-72`) |1805| Executable code | absent | no runtime/sandbox |1806| AI result | exists | `aiBlock {model, prompt, at}` (`nodes.ts:14-19`); static wrapper: no regenerate, no stored sources/cost |1807| Cross-reference / transclusion | partial | page-level `mention` + backlinks + `#block-<id>` deep links; no block-reference node, no embed of a page/block, no synced blocks |18081809## 13.2 Canvas, graph, knowledge intelligence18101811| Item | Status | Evidence |1812|---|---|---|1813| Infinite canvas | absent | no canvas page type; `/graph` is a read-only force graph (`GraphCanvas.tsx`, react-force-graph-2d) |1814| Typed graph relations | absent | only `link` and `ai_suggested` are ever written to `knowledge_edges` (`src/lib/pages/service.ts:107`, `src/lib/graph/suggest.ts:60`); `links.kind` ∈ {`link`,`ai`}; `concept|relation|backlink` exist only in the schema comment |1815| Temporal graph relations | absent | edges carry `created_at`, `decided_at` only |1816| Graph views: global / local | exists | `GET /api/graph` with/without `pageId` + `depth` (`GraphExplorer.tsx`, `PageGraphCard.tsx`) |1817| Graph views: project / document | partial | only a Section filter value and node-kind chips |1818| Graph views: chronological / concept / reference | absent | "Edited within" presets only; no concept/entity nodes (`graph/types.ts:2`); `references` are not graph nodes |1819| Central concepts | partial | degree only (node radius; dashboard top 6 by degree, `src/app/api/dashboard/route.ts:44`) |1820| Orphans | partial | `hideOrphans` + a count; no list or remediation |1821| Contradictions | absent in graph (a chat mode `contradictions` exists in prompts) | no code in `src/lib/graph/**` |1822| Clusters | absent | no clustering code |1823| Open questions | absent | phrase appears only in AI prompts / agent definitions |1824| Semantic zoom | absent | label-visibility threshold only (`GraphCanvas.tsx:68`); nothing in the editor |1825| Living / query blocks | absent | no query node; "New database" slash item creates a sub-page |1826| Knowledge timeline / event journal | absent | nearest: `page_versions`, `audit_logs`, `inbox_actions`, `pages.rev` |1827| Knowledge health dashboard | absent | scattered pieces: index health and duplicates (Search tabs), orphan count, unlinked mentions |1828| Daily brief | absent | on-demand weekly digest only (`src/app/api/dashboard/summary/route.ts`); the worker schedules only backup + maintenance |18291830## 13.3 AI18311832| Item | Status | Evidence |1833|---|---|---|1834| Ask-my-knowledge with block citations | partial | chat mode `notes`: hybrid retrieval (FTS `simple` + HNSW cosine, RRF k=60), evidence gate, server-validated `[n]` markers, `SourceRef {n, chunkId, pageId, title, icon, heading, blockId, excerpt, score, semantic, sourceType}`, chips open `/p/<id>#block-<blockId>`. Limits: anchor = first block of a ~350-token chunk, attachments have no block anchor, UI never sends `scopePageIds` |1835| Contextual AI actions list | exists (static) | 21 ids in `ACTION_IDS` (`src/lib/ai/chat.ts:271`), prompts in `src/lib/ai/prompts.ts`; UI exposes 8 chips + translate + custom + 3 slash items; 8 ids without UI entry; list not adapted to block type |1836| LaTeX export | absent | `FORMATS` = md, zip, json, csv, pdf (really HTML), html, bib (`src/app/api/export/route.ts:25`) |1837| OpenRouter / local providers | absent | `Provider = "openai" \| "anthropic"` (`src/lib/ai/types.ts:1`); SDK clients built without `baseURL` (`providers.ts:7-8`); OpenAI path uses the **Responses API**, not Chat Completions (an OpenAI-compatible MLX/OpenRouter endpoint needs a new adapter, not just a base URL); `ai_providers` has no URL column |1838| Model selector with creator logos | partial | plain `<select>`s (`Chat.tsx:148-150`, `ModelPicker.tsx`, `RoutingTable.tsx`); a coloured dot per provider; no logo asset, no creator column in `ai_models` |1839| Voice notes / transcription | partial | `MediaRecorder` memo → audio attachment and browser `SpeechRecognition` dictation (`src/components/shell/CaptureSheet.tsx`); no server transcription, no audio block |1840| Smart inbox AI suggestions | absent inline (partial via agent) | no model call in `src/lib/inbox/service.ts`; only the on-demand agent `sort_inbox` producing approval proposals |1841| AI database properties | partial | type `ai` + `src/worker/handlers/ai-property.ts`; manual per cell, text only, never recomputed, pending can stick (C-D8) |18421843## 13.4 Databases18441845| Item | Status | Evidence |1846|---|---|---|1847| Views: table, board, gallery, list | exists | `src/components/database/views/{Table,Board,Gallery,List}View.tsx` |1848| Views: calendar, timeline | partial | month only / read-only bars; no drag |1849| Rollups | partial | 8 aggregates, computed in JS per read; empty over relation/rollup/formula targets (`query.ts:56`) |1850| Formulas | exists | `src/lib/databases/formula.ts`, ≈45 functions, JS per row per read |1851| Grouping | partial | client-side, one level, select/status/checkbox, on ≤ 500 loaded rows |1852| Bulk actions | partial | API batches exist; UI = multi-select + delete in the table only |1853| Undo | partial | row delete and board move only (inbox triage has a full undo journal) |18541855## 13.5 Platform18561857| Item | Status | Evidence |1858|---|---|---|1859| Full offline (IndexedDB) on web | partial (thin) | IndexedDB `note-local` v1: stores `drafts` (pending patch per page) and `outbox` (captures) only (`src/lib/client/offline.ts`); SW `note-static-v3` caches static assets, navigations are network-only with `/offline` fallback (`public/sw.js`); cannot read, open or create notes offline; no Background Sync. The web client does not use `/api/sync` at all |1860| Command palette | exists | `src/components/shell/CommandPalette.tsx` (cmdk): 16 static commands + page/content search; no contextual commands |1861| Passkey device management | exists | `/api/auth/passkeys*`, `/api/auth/sessions*`, `src/components/settings/SecuritySection.tsx` (add, rename, revoke, sessions, revoke-all, step-up) |1862| Non-cookie API credential (extensions, widgets, share sheet, CLI) | absent | cookie session + forged `Origin` only (section 2.0) |1863| Change feed for anything but pages/attachments | absent | section 1.3 |1864| Realtime push / collaboration | absent | no websocket/SSE change channel, no Yjs; remote change detected on focus via `?head=1` |18651866## 13.6 Cross-cutting facts the upgrade must plan around (lead auditor's synthesis)186718681. **New block types pass the server today** (structural validator only, D-13) — but **old clients destroy them**: `enableContentCheck` is not set in `Editor.tsx`, Tiptap's default is `false` (installed `@tiptap/core` dist `index.js:6588`), and on an unknown node `createNodeFromContent` logs a warning and returns an EMPTY document (`index.js:596-604`, verified in the installed 3.32.2 source). The 800 ms autosave then PATCHes that empty doc with a valid `baseVersion`. A stale browser tab or an un-updated native build opening a page that contains a new block type can wipe it (the previous state survives only if a `page_versions` snapshot was due). Ship `enableContentCheck` + `onContentError` (read-only on error) BEFORE the first new node type, and give the native apps an "unknown block" passthrough.18692. The block-id type list exists in three places (client `BlockId` plugin, server `ensureBlockIds` in `src/lib/editor/text.ts`, and the text/markdown walkers); every new block type must be added to each, and `listItem`/`taskItem` currently get no id (blocks citations and task enrichment).18703. Markdown round-trip already loses callout, aiBlock, details, footnotes, fileBlock, highlight, underline, sub/sup: Markdown cannot be the interchange format between web and native; the ProseMirror JSON is.18714. Fonts named in CSS (Inter, Source Serif 4, JetBrains Mono) are never loaded and `motion` is installed but unused: the "design system" is tokens + CSS keyframes; there is little to preserve.18725. The database engine filters/sorts/paginates in JS over the whole database and the UI caps at 500 rows; fine at 0 rows in production, a rewrite candidate before native clients depend on it.18736. Production holds 7 pages, 0 databases, 0 tags: data migration is not a constraint; API compatibility with the shipped native apps (`/api/sync` shape, cookie auth, `POST /api/pages` with client id) is.1874