Skip to content

fix: keep Unicode titles in generated document filenames - #449

Merged
willchen96 merged 2 commits into
open-legal-products:mainfrom
snowyukitty:fix/unicode-generated-filenames
Sep 12, 2026
Merged

willchen96 merged 2 commits into
open-legal-products:mainfrom
snowyukitty:fix/unicode-generated-filenames

Conversation

@snowyukitty

Copy link
Copy Markdown
Contributor

Summary

Generated DOCX, XLSX and PPTX files are named by stripping every character
outside [a-zA-Z0-9 -] from the title, so a title in any script but unaccented
Latin loses its name. This gives all three generators one naming policy that
keeps Unicode letters, marks and numbers, and adds a regression suite for it.

Fixes #448. Two files, +289/-12 at c737c7c.

Why / Motivation

Both naming sites — the inline one in generateDocx and safeGeneratedFilename,
which persistGeneratedFile uses for XLSX and PPTX — apply the same character
class, so on main today:

Title Before After
秘密保持契約書 document.docx 秘密保持契約書.docx
비밀유지계약서 document.docx 비밀유지계약서.docx
Договор document.docx Договор.docx
Σύμβαση document.docx Σύμβαση.docx
عقد document.docx عقد.docx
अनुबंध document.docx अनुबंध.docx
Résumé Rsum.docx Résumé.docx

That one string is the tool result the model reports, the
document_versions.filename row, and the filename inside the signed
/download/:token payload, so for those users the name is document.docx in
the chat message the tool returns, in the document list, and in the file the
browser saves.

Nothing downstream required an ASCII name. buildContentDisposition already
emits a UTF-8 filename* with an ASCII fallback; generatedDocKey derives the
object key from a random id and the extension, so the key never held the title;
the download token is base64url over UTF-8 JSON. And an uploaded
秘密保持契約書.docx already keeps its name — uploadProcessing.ts writes
file.filename into document_versions.filename verbatim. Only the generated
half was lossy.

Changes

  • safeGeneratedFilename keeps Unicode letters, marks and numbers alongside the
    space and hyphen it already allowed. Everything the old class rejected —
    path separators, control and formatting characters, punctuation — is still
    rejected, and an empty result still falls back to document.
  • Normalization is NFC, not NFKC, so e + U+0301 composes to é while
    fullwidth digits and ① stay themselves instead of folding into ASCII.
  • The 64-unit length bound now walks whole grapheme clusters. This only starts
    to matter here: the old class deleted every non-ASCII character before
    truncating, so .slice(0, 64) had nothing multi-unit left to cut. Once the
    stem can hold supplementary-plane letters and combining sequences, a
    code-unit cut can leave an unpaired surrogate — which encodeURIComponent,
    and so the Content-Disposition filename* value, cannot encode — or
    detach a mark from its base.
  • generateDocx calls safeGeneratedFilename instead of repeating it inline,
    so there is one policy rather than two copies of one.
  • New backend/src/lib/chat/tools/__tests__/documentOps.generatedFilename.test.ts
    drives the three real generators and asserts the tool result, the persisted
    version row and the decoded download token carry the same name; that the
    storage key is still independent of the title; that the response header
    carries the UTF-8 name with an ASCII fallback; and that the packages remain
    valid. It uses the real generatedDocKey and the real token signer, because
    a mocked key or URL cannot show whether the name survived them.

Tradeoffs & risks

  • ASCII behaviour is unchanged, including ---, the empty-title and
    punctuation-only fallbacks, the fixed caller-supplied extension, and 64-unit
    truncation of a long ASCII title. Those cases are pinned by tests that pass on
    main as well.
  • Format characters (Cf), including ZWJ and ZWNJ, remain excluded. Some
    scripts use them orthographically; preserving them safely needs its own
    design, so this patch does not claim universal orthographic fidelity.
  • The ASCII fallback in Content-Disposition becomes a row of underscores for a
    fully non-Latin name. That is what the existing sanitizeDispositionFilename
    already does for uploaded files with the same names, and filename* is what
    browsers use.
  • Out of scope, same assumption, different behaviour: normalizeHeadingText in
    the same file also reduces to [a-zA-Z0-9], so the "first heading repeats the
    title" suppression in generateDocx never fires for a non-Latin title. Worth
    a separate change; it is not a filename question.
  • Coordination: [Quality] Repo-wide audit fixes: error containment, drifted forks, silent failures (stacked on #294→#295) #356 also removes the inline DOCX naming block as part of a
    wider refactor. It does not change the character class. Whichever lands
    second needs a small conflict resolution in that hunk; I am happy to rebase.

How verified

Node 22, from backend/, against base 8b3466fc04bf8cad623238a71fca1fda3fc89fde.

Regression arm — the new tests applied alone to unmodified main:

npx vitest run src/lib/chat/tools/__tests__/documentOps.generatedFilename.test.ts
  Tests  16 failed | 11 passed (27)
  → expected 'document.docx' to be '秘密保持契約書.docx'

The 11 that already pass there are the control: the ASCII/fallback
compatibility cases, the title-independent storage key, and package validity.

One assertion cannot be proved that way. The straddling-surrogate case passes
on main for the wrong reason — the old class deletes U+20000 before
truncating, so nothing multi-unit was ever left to cut. It guards the new code
instead, and a mutant proves it does: replacing the grapheme walk with
allowed.slice(0, 64) yields a 64-unit stem ending in an unpaired surrogate,
encodeURIComponent on the resulting filename throws URI malformed, and that
assertion fails.
The combining-mark case next to it does fail on unmodified main.

With this patch:

npx vitest run src/lib/chat/tools/__tests__/documentOps.generatedFilename.test.ts
  Tests  27 passed (27)

npm test
  Test Files  112 passed | 6 skipped (118)
       Tests  1410 passed | 39 skipped (1449)

npm run build      # tsc, clean

npm test on unmodified main is 111 passed | 6 skipped files and
1383 passed | 39 skipped tests, so the difference is exactly the 27 added
tests and nothing else. npx prettier --check is clean on the new test file;
documentOps.ts is not Prettier-clean on main and this patch does not change
that either way.

Not run: the Playwright suite (npm run test:e2e) and the gated
npm run test:stack, neither of which covers this path. No LLM calls, live
Supabase or real documents were needed; the test uses synthetic content.

Checklist

  • Ran the relevant build/test command for the area changed.
  • Reviewed git diff and removed unrelated changes.
  • Updated docs / env examples if setup, config, or behavior changed — no
    doc or env example describes generated filenames, so there was nothing to
    update.
  • No secrets, API keys, real documents, or .env files committed.

generate_docx, generate_xlsx and generate_pptx named their output by
stripping every character outside [a-zA-Z0-9 -] from the title, so a title
written in any script but unaccented Latin became "document.docx" in the
tool result, in the document_versions row, and in the signed download
token. Nothing downstream needed an ASCII name: the download header already
emits a UTF-8 filename* with an ASCII fallback, the object key is derived
from a random id, and an uploaded file with the same name keeps it.

Give all three generators one naming policy. It keeps Unicode letters,
marks and numbers alongside the space and hyphen the old class already
allowed, normalizes with NFC rather than NFKC so fullwidth and circled
characters are not folded into ASCII, and bounds the 64-unit stem on whole
grapheme clusters so truncation cannot leave an unpaired surrogate.
Everything the old class rejected stays rejected and the "document"
fallback is unchanged. generateDocx now calls the shared
safeGeneratedFilename instead of repeating it inline.
@CLAassistant

CLAassistant commented Sep 8, 2026 •

Copy link
Copy Markdown

CLA assistant check
All committers have signed the CLA.

@willchen96
willchen96 merged commit 5939702 into open-legal-products:main Sep 12, 2026
14 checks passed
@willchen96

Copy link
Copy Markdown
Collaborator

Thanks @snowyukitty once again!

amal66 added a commit to amal66/mike that referenced this pull request Sep 15, 2026
…with service layers

WHY THIS MATTERS
The backend's HTTP handlers were monoliths: routes/tabular.ts (2,389 lines),
routes/user.ts (2,131), routes/projects.ts (1,438), routes/documents.ts
(1,264), routes/workflows.ts (1,447), routes/wordChat.ts (1,266),
routes/uploadSessions.ts (964) — each interleaving request parsing, access
checks, storage IO and business logic inline across a dozen unrelated
handlers. In a monolith every change lands in a file whose blast radius is
unclear, business logic can only be exercised through a live HTTP stack, and
a new contributor cannot tell which lines are "the endpoint" and which are
"the feature". Small files with one concern each are reviewable; 1,500-line
route files are not.

WHAT IS A SERVICE LAYER
A service layer separates WHAT the application does from HOW it is reached.
The route (HTTP layer) owns request parsing, validation, and mapping results
onto status codes; the service owns business logic and data access, takes
its database handle as an explicit parameter (`db: Db`), returns typed
results (`ServiceResult<T>` or a module-local union) instead of writing to
`res`, and never touches the request or response. That inversion is what
makes logic unit-testable (call the function with a fake db — no server),
reusable (the extraction worker calls the same functions the SSE route
calls), and safe to change (the compiler knows every result shape a route
must handle).

HOW IT WORKS
- Every HTTP surface now lives in src/modules/<domain>/: a thin
  <name>.routes.ts plus a service layer. Sixteen modules: audit, auth, chat,
  documents, downloads, library, models, project-chat, projects,
  quick-actions, source-documents, tabular, uploads, user, word-chat,
  workflows. src/routes/ is gone.
- Large domains split the service into topic files behind a facade
  (<name>.service.ts) that re-exports BY NAME, so intra-module helpers cannot
  leak and outside code (other modules, workers, jobs, app.ts) has exactly
  one door. documents: access/versions/download/edits; projects:
  crud/folders/documents/chats/overview; user: profile/mfa/apiKeys/
  apiKeyStore/settings/mcp/account/export; tabular: reviews/rows/cells/
  chats/extract/extractRow/generate/generateStream/prompt/overview; uploads:
  manifest/access/sessions/processing; workflows: addons/name/overview.
- Route files no longer query the database: tabular.routes.ts went from
  2,035 lines and 46 inline queries to 779 lines and none; every other
  routes file is at zero. Request-shape validation and SSE stream loops
  (header flush, LLM stream, abort handling, message persistence) stay in
  the routes file — stream lifetime is an HTTP concern. tabular.
  generateStream.ts is the one sanctioned service file that takes `res`,
  because two routes share its stream; its header says so.
- Domain logic that lived in src/lib/ moves into its module when nothing
  that stays in lib/ imports it: userSettings and userApiKeys → user
  (settings, apiKeyStore); uploadSessions and uploadProcessing → uploads;
  projectsOverview → projects; workflowsOverview and workflowName →
  workflows; tabularReviewsOverview → tabular; chatTitle → chat. Their
  tests travel with them. lib/ keeps infrastructure (storage, queues,
  llm, mcp, http helpers) and the cross-domain primitives that the durable
  job handlers (lib/dbq) and the assistant engine (lib/chat) import —
  access, audit, documentTypes, documentVersions, modelSelection,
  routerModels, userLookup, workflowCatalog. The rule that decided each
  case is written down in docs/backend-architecture.md.
- Every file takes `Db` from lib/supabase.ts instead of redeclaring it, and
  new failure results use lib/serviceResult.ts.
- Four moved console.error sites that put a user-controlled filename in
  format-string position (a CodeQL js/tainted-format-string finding) now
  pass the filename as a structured argument, the convention open-legal-products#294 set.

PURE MOTION, VERIFIED
- A script imports the real src/app.ts and walks the live Express router:
  222 mounted endpoints, byte-identical to main including registration
  order (order matters: /create must beat /:chatId).
- Every change main made to the moved files after this branch's previous
  base — the direct-upload session protocol replacing multipart uploads,
  workflow reference files becoming document-backed assets, transparent
  tables in the profile, workflow_id + canEdit gates on document access,
  the streaming download-zip with folder selection — was located and
  re-homed hunk by hunk in its module.
- tsc clean; the full backend suite runs 1,209 passed | 34 skipped,
  including the route-level integration suites that drive the real app,
  plus 60-odd new service-level unit tests (fake db, no server).
- Zero new dependencies. No logging framework, no validation framework, no
  observability hooks — organization only, so each future concern can be
  its own decision.

REBASE NOTE (2026-09-14, onto main aba3cfc)
Main moved 38 commits between the September 3 base and this rebase, and 22
of the files this commit deletes or relocates changed underneath it
(3,688 insertions across routes/chat, projectChat, projects, tabular,
workflows, user, documents, uploadSessions, wordChat, auth). Every one of
those hunks was re-homed in its module, main's behaviour byte-for-byte:
- Organization access (open-legal-products#426): the project role ladder (`can(role, …)`,
  `creatorScopedAllowed`, `checkWorkflowAccess`, `ensureChatAccess`),
  `org_id` tenant stamping via `resolveContentOrgId`, nullable `user_id`,
  `access_role`/`is_owner` in detail responses, the `/people` and `/access`
  grant endpoints on projects, chats, reviews and workflows, deny
  overrides, directory search — in the projects, chat, project-chat,
  tabular, workflows, quick-actions, documents and uploads services.
- Scoped memory (open-legal-products#451): `beginMemoryConversationTurn` in every
  `prepare*Stream`, consolidation scheduling and lease release in the
  four chat routes, `author_user_id`/`inputMessageId` on user messages,
  the profile's `memory_curator_model` / `project_memory_default`,
  `DELETE /user/memories`.
- SSO (open-legal-products#435/open-legal-products#458): `startSsoSignIn` behind the auth facade.
- `/file` downloads (open-legal-products#456/open-legal-products#304) replacing `/docx`, Unicode filenames
  (open-legal-products#449), connector error mapping and the 409 for re-authorization
  (open-legal-products#346), the open-legal-products#461 assistant-history fix.
- Two NEW modules for surfaces main added: `modules/orgs` (routes +
  facade over lib/orgs; `sendOrgFailure` moved to lib/orgFailure.ts so
  the user module can share it) and `modules/memory` (routes + a service
  that resolves the memory context and wraps lib/memory/files).
- One new `lib → modules` edge: main's memory curator (a job handler)
  reads model settings, now through the user facade; allowlisted next to
  the stale-work sweeper with the same follow-up.
Verified on the rebased tree: tsc clean; 261 mounted endpoints identical
to main including registration order; the full backend suite runs 1,714
passed | 39 skipped, including main's org-access, memory, SSO, `/file`
and connector suites unchanged.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE
amal66 added a commit to amal66/mike that referenced this pull request Sep 16, 2026
…with service layers

WHY THIS MATTERS
The backend's HTTP handlers were monoliths: routes/tabular.ts (2,389 lines),
routes/user.ts (2,131), routes/projects.ts (1,438), routes/documents.ts
(1,264), routes/workflows.ts (1,447), routes/wordChat.ts (1,266),
routes/uploadSessions.ts (964) — each interleaving request parsing, access
checks, storage IO and business logic inline across a dozen unrelated
handlers. In a monolith every change lands in a file whose blast radius is
unclear, business logic can only be exercised through a live HTTP stack, and
a new contributor cannot tell which lines are "the endpoint" and which are
"the feature". Small files with one concern each are reviewable; 1,500-line
route files are not.

WHAT IS A SERVICE LAYER
A service layer separates WHAT the application does from HOW it is reached.
The route (HTTP layer) owns request parsing, validation, and mapping results
onto status codes; the service owns business logic and data access, takes
its database handle as an explicit parameter (`db: Db`), returns typed
results (`ServiceResult<T>` or a module-local union) instead of writing to
`res`, and never touches the request or response. That inversion is what
makes logic unit-testable (call the function with a fake db — no server),
reusable (the extraction worker calls the same functions the SSE route
calls), and safe to change (the compiler knows every result shape a route
must handle).

HOW IT WORKS
- Every HTTP surface now lives in src/modules/<domain>/: a thin
  <name>.routes.ts plus a service layer. Sixteen modules: audit, auth, chat,
  documents, downloads, library, models, project-chat, projects,
  quick-actions, source-documents, tabular, uploads, user, word-chat,
  workflows. src/routes/ is gone.
- Large domains split the service into topic files behind a facade
  (<name>.service.ts) that re-exports BY NAME, so intra-module helpers cannot
  leak and outside code (other modules, workers, jobs, app.ts) has exactly
  one door. documents: access/versions/download/edits; projects:
  crud/folders/documents/chats/overview; user: profile/mfa/apiKeys/
  apiKeyStore/settings/mcp/account/export; tabular: reviews/rows/cells/
  chats/extract/extractRow/generate/generateStream/prompt/overview; uploads:
  manifest/access/sessions/processing; workflows: addons/name/overview.
- Route files no longer query the database: tabular.routes.ts went from
  2,035 lines and 46 inline queries to 779 lines and none; every other
  routes file is at zero. Request-shape validation and SSE stream loops
  (header flush, LLM stream, abort handling, message persistence) stay in
  the routes file — stream lifetime is an HTTP concern. tabular.
  generateStream.ts is the one sanctioned service file that takes `res`,
  because two routes share its stream; its header says so.
- Domain logic that lived in src/lib/ moves into its module when nothing
  that stays in lib/ imports it: userSettings and userApiKeys → user
  (settings, apiKeyStore); uploadSessions and uploadProcessing → uploads;
  projectsOverview → projects; workflowsOverview and workflowName →
  workflows; tabularReviewsOverview → tabular; chatTitle → chat. Their
  tests travel with them. lib/ keeps infrastructure (storage, queues,
  llm, mcp, http helpers) and the cross-domain primitives that the durable
  job handlers (lib/dbq) and the assistant engine (lib/chat) import —
  access, audit, documentTypes, documentVersions, modelSelection,
  routerModels, userLookup, workflowCatalog. The rule that decided each
  case is written down in docs/backend-architecture.md.
- Every file takes `Db` from lib/supabase.ts instead of redeclaring it, and
  new failure results use lib/serviceResult.ts.
- Four moved console.error sites that put a user-controlled filename in
  format-string position (a CodeQL js/tainted-format-string finding) now
  pass the filename as a structured argument, the convention open-legal-products#294 set.

PURE MOTION, VERIFIED
- A script imports the real src/app.ts and walks the live Express router:
  222 mounted endpoints, byte-identical to main including registration
  order (order matters: /create must beat /:chatId).
- Every change main made to the moved files after this branch's previous
  base — the direct-upload session protocol replacing multipart uploads,
  workflow reference files becoming document-backed assets, transparent
  tables in the profile, workflow_id + canEdit gates on document access,
  the streaming download-zip with folder selection — was located and
  re-homed hunk by hunk in its module.
- tsc clean; the full backend suite runs 1,209 passed | 34 skipped,
  including the route-level integration suites that drive the real app,
  plus 60-odd new service-level unit tests (fake db, no server).
- Zero new dependencies. No logging framework, no validation framework, no
  observability hooks — organization only, so each future concern can be
  its own decision.

REBASE NOTE (2026-09-14, onto main aba3cfc)
Main moved 38 commits between the September 3 base and this rebase, and 22
of the files this commit deletes or relocates changed underneath it
(3,688 insertions across routes/chat, projectChat, projects, tabular,
workflows, user, documents, uploadSessions, wordChat, auth). Every one of
those hunks was re-homed in its module, main's behaviour byte-for-byte:
- Organization access (open-legal-products#426): the project role ladder (`can(role, …)`,
  `creatorScopedAllowed`, `checkWorkflowAccess`, `ensureChatAccess`),
  `org_id` tenant stamping via `resolveContentOrgId`, nullable `user_id`,
  `access_role`/`is_owner` in detail responses, the `/people` and `/access`
  grant endpoints on projects, chats, reviews and workflows, deny
  overrides, directory search — in the projects, chat, project-chat,
  tabular, workflows, quick-actions, documents and uploads services.
- Scoped memory (open-legal-products#451): `beginMemoryConversationTurn` in every
  `prepare*Stream`, consolidation scheduling and lease release in the
  four chat routes, `author_user_id`/`inputMessageId` on user messages,
  the profile's `memory_curator_model` / `project_memory_default`,
  `DELETE /user/memories`.
- SSO (open-legal-products#435/open-legal-products#458): `startSsoSignIn` behind the auth facade.
- `/file` downloads (open-legal-products#456/open-legal-products#304) replacing `/docx`, Unicode filenames
  (open-legal-products#449), connector error mapping and the 409 for re-authorization
  (open-legal-products#346), the open-legal-products#461 assistant-history fix.
- Two NEW modules for surfaces main added: `modules/orgs` (routes +
  facade over lib/orgs; `sendOrgFailure` moved to lib/orgFailure.ts so
  the user module can share it) and `modules/memory` (routes + a service
  that resolves the memory context and wraps lib/memory/files).
- One new `lib → modules` edge: main's memory curator (a job handler)
  reads model settings, now through the user facade; allowlisted next to
  the stale-work sweeper with the same follow-up.
Verified on the rebased tree: tsc clean; 261 mounted endpoints identical
to main including registration order; the full backend suite runs 1,714
passed | 39 skipped, including main's org-access, memory, SSO, `/file`
and connector suites unchanged.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE
willchen96 added a commit that referenced this pull request Sep 17, 2026
…e, and enforced module boundaries (#295)

* refactor(backend): one Db type and one service-result contract for every module

WHY THIS MATTERS
A service layer is only as consistent as the two things every service
function shares: how it receives the database, and how it reports failure.
Before this commit the backend had 30 private copies of
`type Db = ReturnType<typeof createServerSupabase>` (one per file that
needed it) and about twenty different failure shapes — `{ok:false; status}`,
`{ok:false; kind:"db_error"}`, `{ok:false; detail}`, `{ok:false; error}` —
each mapped onto HTTP by hand in its own routes file. Nothing was wrong with
any single copy; the cost is that a reader cannot tell which differences
are meaningful, and every new module reinvents the mapping.

WHAT IS A RESULT CONTRACT
Instead of throwing or writing to `res`, a service function RETURNS a value
that says either "here is the data" or "here is why not". A discriminated
union (`{ok:true; data}` | `{ok:false; kind; detail}`) lets the compiler
check that the route handles every case, and a single mapping function
turns `kind` into a status code — so the policy "authorization gaps are
404, not 403, so a foreign id never leaks" is written once and inherited.

HOW IT WORKS
- `lib/supabase.ts` exports `Db`, the handle every service takes first.
  `createServerSupabase()` is typed to return it.
- `lib/serviceResult.ts` defines `ServiceResult<T>`, `ServiceFailure`
  (`validation`→400, `forbidden`→403, `not_found`→404, `conflict`→409,
  `unavailable`→503, `error`→500 via `sendInternalError`, so raw errors are
  logged with the request id and never sent to clients), the constructors
  `ok`/`failure`/`internalFailure`, and `sendServiceFailure(res, f)`.
  `code` is emitted before `detail`, matching the body shape the existing
  handlers produce.
- The unit test pins the status table and proves an internal error's
  message never reaches the response body.

The following commit adopts both across the module tree; modules whose
failure vocabulary predates this file keep their local kinds where a
response needs a status the shared table does not name (422, 502, ...).

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC

* refactor(backend): decompose route monoliths into per-domain modules with service layers

WHY THIS MATTERS
The backend's HTTP handlers were monoliths: routes/tabular.ts (2,389 lines),
routes/user.ts (2,131), routes/projects.ts (1,438), routes/documents.ts
(1,264), routes/workflows.ts (1,447), routes/wordChat.ts (1,266),
routes/uploadSessions.ts (964) — each interleaving request parsing, access
checks, storage IO and business logic inline across a dozen unrelated
handlers. In a monolith every change lands in a file whose blast radius is
unclear, business logic can only be exercised through a live HTTP stack, and
a new contributor cannot tell which lines are "the endpoint" and which are
"the feature". Small files with one concern each are reviewable; 1,500-line
route files are not.

WHAT IS A SERVICE LAYER
A service layer separates WHAT the application does from HOW it is reached.
The route (HTTP layer) owns request parsing, validation, and mapping results
onto status codes; the service owns business logic and data access, takes
its database handle as an explicit parameter (`db: Db`), returns typed
results (`ServiceResult<T>` or a module-local union) instead of writing to
`res`, and never touches the request or response. That inversion is what
makes logic unit-testable (call the function with a fake db — no server),
reusable (the extraction worker calls the same functions the SSE route
calls), and safe to change (the compiler knows every result shape a route
must handle).

HOW IT WORKS
- Every HTTP surface now lives in src/modules/<domain>/: a thin
  <name>.routes.ts plus a service layer. Sixteen modules: audit, auth, chat,
  documents, downloads, library, models, project-chat, projects,
  quick-actions, source-documents, tabular, uploads, user, word-chat,
  workflows. src/routes/ is gone.
- Large domains split the service into topic files behind a facade
  (<name>.service.ts) that re-exports BY NAME, so intra-module helpers cannot
  leak and outside code (other modules, workers, jobs, app.ts) has exactly
  one door. documents: access/versions/download/edits; projects:
  crud/folders/documents/chats/overview; user: profile/mfa/apiKeys/
  apiKeyStore/settings/mcp/account/export; tabular: reviews/rows/cells/
  chats/extract/extractRow/generate/generateStream/prompt/overview; uploads:
  manifest/access/sessions/processing; workflows: addons/name/overview.
- Route files no longer query the database: tabular.routes.ts went from
  2,035 lines and 46 inline queries to 779 lines and none; every other
  routes file is at zero. Request-shape validation and SSE stream loops
  (header flush, LLM stream, abort handling, message persistence) stay in
  the routes file — stream lifetime is an HTTP concern. tabular.
  generateStream.ts is the one sanctioned service file that takes `res`,
  because two routes share its stream; its header says so.
- Domain logic that lived in src/lib/ moves into its module when nothing
  that stays in lib/ imports it: userSettings and userApiKeys → user
  (settings, apiKeyStore); uploadSessions and uploadProcessing → uploads;
  projectsOverview → projects; workflowsOverview and workflowName →
  workflows; tabularReviewsOverview → tabular; chatTitle → chat. Their
  tests travel with them. lib/ keeps infrastructure (storage, queues,
  llm, mcp, http helpers) and the cross-domain primitives that the durable
  job handlers (lib/dbq) and the assistant engine (lib/chat) import —
  access, audit, documentTypes, documentVersions, modelSelection,
  routerModels, userLookup, workflowCatalog. The rule that decided each
  case is written down in docs/backend-architecture.md.
- Every file takes `Db` from lib/supabase.ts instead of redeclaring it, and
  new failure results use lib/serviceResult.ts.
- Four moved console.error sites that put a user-controlled filename in
  format-string position (a CodeQL js/tainted-format-string finding) now
  pass the filename as a structured argument, the convention #294 set.

PURE MOTION, VERIFIED
- A script imports the real src/app.ts and walks the live Express router:
  222 mounted endpoints, byte-identical to main including registration
  order (order matters: /create must beat /:chatId).
- Every change main made to the moved files after this branch's previous
  base — the direct-upload session protocol replacing multipart uploads,
  workflow reference files becoming document-backed assets, transparent
  tables in the profile, workflow_id + canEdit gates on document access,
  the streaming download-zip with folder selection — was located and
  re-homed hunk by hunk in its module.
- tsc clean; the full backend suite runs 1,209 passed | 34 skipped,
  including the route-level integration suites that drive the real app,
  plus 60-odd new service-level unit tests (fake db, no server).
- Zero new dependencies. No logging framework, no validation framework, no
  observability hooks — organization only, so each future concern can be
  its own decision.

REBASE NOTE (2026-09-14, onto main aba3cfca)
Main moved 38 commits between the September 3 base and this rebase, and 22
of the files this commit deletes or relocates changed underneath it
(3,688 insertions across routes/chat, projectChat, projects, tabular,
workflows, user, documents, uploadSessions, wordChat, auth). Every one of
those hunks was re-homed in its module, main's behaviour byte-for-byte:
- Organization access (#426): the project role ladder (`can(role, …)`,
  `creatorScopedAllowed`, `checkWorkflowAccess`, `ensureChatAccess`),
  `org_id` tenant stamping via `resolveContentOrgId`, nullable `user_id`,
  `access_role`/`is_owner` in detail responses, the `/people` and `/access`
  grant endpoints on projects, chats, reviews and workflows, deny
  overrides, directory search — in the projects, chat, project-chat,
  tabular, workflows, quick-actions, documents and uploads services.
- Scoped memory (#451): `beginMemoryConversationTurn` in every
  `prepare*Stream`, consolidation scheduling and lease release in the
  four chat routes, `author_user_id`/`inputMessageId` on user messages,
  the profile's `memory_curator_model` / `project_memory_default`,
  `DELETE /user/memories`.
- SSO (#435/#458): `startSsoSignIn` behind the auth facade.
- `/file` downloads (#456/#304) replacing `/docx`, Unicode filenames
  (#449), connector error mapping and the 409 for re-authorization
  (#346), the #461 assistant-history fix.
- Two NEW modules for surfaces main added: `modules/orgs` (routes +
  facade over lib/orgs; `sendOrgFailure` moved to lib/orgFailure.ts so
  the user module can share it) and `modules/memory` (routes + a service
  that resolves the memory context and wraps lib/memory/files).
- One new `lib → modules` edge: main's memory curator (a job handler)
  reads model settings, now through the user facade; allowlisted next to
  the stale-work sweeper with the same follow-up.
Verified on the rebased tree: tsc clean; 261 mounted endpoints identical
to main including registration order; the full backend suite runs 1,714
passed | 39 skipped, including main's org-access, memory, SSO, `/file`
and connector suites unchanged.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* test(architecture): make the module layering rules executable

WHY THIS MATTERS
A layout convention that lives only in a markdown file decays at the speed
of the next deadline. The previous revision of this branch stated
"routes never query the database" and "lib never imports modules" — and
shipped a 2,035-line routes file with 46 inline queries, because nothing
checked. The way mature codebases keep an architecture is a fitness
function: a test that reads the dependency graph and fails when a boundary
is crossed, so the rule is enforced by CI, not by reviewer memory.

WHAT IS AN ARCHITECTURE FITNESS TEST
It walks every TypeScript file under src/, extracts the import
specifiers, resolves the relative ones, and asserts properties of the
resulting graph. It needs no lint plugin or dependency-cruiser — a
directory walk and a regex are enough — so it runs with the ordinary unit
suite and has no install cost.

HOW IT WORKS — seven rules, each with an explicit allowlist
1. lib/ never imports modules/ (one documented edge: the stale-work
   sweeper reaching the tabular facade).
2. A module is imported from outside only through its facade; the only
   other entry point is *.routes.ts, and only app.ts may import it (that is
   how a router gets mounted).
3. Inside a module only *.routes.ts may import express or write to res
   (one documented exception: tabular.generateStream.ts).
4. Route files do not query the database — counted per file as a ratchet
   with an empty baseline, so any inline `.from(`/`.rpc(` fails and a
   stale baseline entry fails too.
5. Facades re-export by name, never `export *`.
6. middleware/ never imports modules/.
7. src/routes/ does not exist.
Plus a structural check: every module has exactly one *.service.ts facade
and at least one *.routes.ts.

Adding to an allowlist is a reviewable decision that needs a comment; the
test text tells the reader which rule and which file, so a failure is a
diagnosis, not a puzzle.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC

* test(coverage): widen the ratchet to src/modules and re-floor to the measured tree

WHY THIS MATTERS
The coverage gate's include was src/lib/** only. Moving handlers and the
tabular extraction core into src/modules/ would have silently removed
thousands of lines from the measured set — the percentages would rise
without a single new test, which is exactly the failure mode a ratchet
exists to prevent, and every future route/service line would be
permanently unmeasured.

HOW IT WORKS
The include is now src/lib/** + src/modules/**, so the gate measures
everything the backend ships. Widening the denominator would normally push
the floors DOWN; here the module split's new service-level unit tests
(tabular reviews/cells/chats/generate, uploads access/manifest/processing,
chat and project-chat services, workflow add-ons, models) lifted the
measurement well past main's 52/46/53/54, so the floors go UP — just below
the number, so CI still fails on any drop. Floors only rise: when you add
tests, raise them in the same PR.

Rebase note (2026-09-14): on main aba3cfca the measured tree is 61.34%
statements / 52.31% branches / 66.28% functions / 63.97% lines — main's
org-access, scoped-memory and connector suites now exercise the modules —
so the floors are 61/52/66/63 instead of the 55/47/57/57 measured on the
September 3 tree.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* docs: the module layout is the backend convention

WHY THIS MATTERS
AGENTS.md is the first file every contributor and coding agent reads; it
still described handlers living in backend/src/routes/, which no longer
exists. A layout that is not written down where people look is not a
convention, it is folklore.

HOW IT WORKS
- docs/backend-architecture.md is the reference: the directory map, module
  anatomy (routes / facade / topic files / shared / tests), the service
  contract with a worked example, the seven layering rules and the test
  that enforces them, what stays in the lib/ shared kernel and the concrete
  reason (the durable-job handlers and the assistant engine import it),
  the named follow-ups (job handlers into modules, lib/chat into
  modules/chat, the stale-work edge, migrating older result shapes), and a
  checklist for adding a domain.
- AGENTS.md's Backend Structure section states the contract in the
  imperative and points at the doc and the test.
- docs/README.md indexes the new page; docs/testing-coverage.md lists the
  real test locations; the SSE load-test comment points at the module.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_015rqMYqb5Tj7z1DdGJ5VcQC

* fix(backend): contain async route errors in the un-modularized routes

Express 4 does not forward async handler rejections, and the routes that #295
left outside modules/ were unguarded — a throw in any of them meant an
unhandled rejection and a socket held open until client timeout. Only the
workflows router defended itself. Its asyncRoute wrapper is now a shared
middleware (middleware/asyncRoute.ts) applied to audit, quickActions,
sourceDocuments, wordChat and workflowAddons, so a rejection reaches
app.ts's handleUnhandledError instead of nothing.

routerErrorHandler now only attributes the failure to a router in the log and
delegates the response to handleUnhandledError. Answering with a
router-specific 500 body would have contradicted the internal-error contract
main established: body-parser's 400/413 keep their own status and code,
everything else is the opaque {code:"internal_error"} body.

Also fixed in those route files, where errors were being silently converted to
success or not-found:
- quickActions: DELETE returned 204 with no row matched (now selects the
  deleted ids and 404s when none); DB failures on the workflow lookup read as
  404 (now 500, via the now-exported workflows.service.resolveWorkflowAccess
  instead of a drifted private copy).
- workflowAddons: the import response hand-rebuilt the workflow shape and had
  drifted from GET /workflows/:id on four fields — now uses the exported
  withDatabaseWorkflow; a failed add-on lookup answered 404 (now 500).
- audit: lib/auditExport's private project-access helper duplicated
  lib/access.listAccessibleProjectIds and had drifted from it (it re-counted
  the caller's own shared projects and used a different `contains` encoding).
- sourceDocuments: a missing or rejected CourtListener token answered 502,
  sending the user to check a service that was fine; credential failures now
  answer 400 with a fixed message that never echoes the upstream body.

The history page's surface filter learns the "word" label, so the durable Word
chat audit rows #294 introduced (surface "word") are filterable rather than
showing a raw key.

Rebase note (2026-09-14): re-expressed onto the rebased #295 (ec0ac372) and
main aba3cfca.

- Express is 5.x on main now, so a rejected async handler already reaches
  next(err) by itself: asyncRoute is no longer what keeps the socket from
  hanging. It stays because it is the one place the contract is written down,
  and because it pins the request type — a bare Request defaults to
  ParamsDictionary, where req.params.x is `string | string[]`.
- The five routers this commit named are modules now, and four module routers
  (workflows, workflow-addons, quick-actions, uploads) each carried a private
  copy of the wrapper. The shared middleware replaces all four and is adopted
  by every module routes file: 152 handlers across nineteen files, plus the
  memory module's four shared handlers, and routerErrorHandler("[tag]") on all
  twenty routers.
- Delegating the response to handleUnhandledError is a no-op on the wire here:
  main's protectInternalErrorResponses already rewrites any 5xx body to the
  opaque internal_error body, so the three router-specific 500 details were
  never reaching a client. What goes away is the
  [http/sanitized-internal-error] log line they tripped on every failure.
- Two sites that did not exist in August were wrapped for the same reason:
  documents' edits accept/reject were sync handlers calling
  `void handleEditResolution(...)`, a floating promise Express cannot see.
- quickActions: exporting workflows.service.resolveWorkflowAccess was dropped.
  The drift it existed to remove is gone — #295's rebuild routes both the
  quick-actions check and the workflows one through lib/access
  .checkWorkflowAccess — and resolveWorkflowAccess swallows a failed lookup the
  same way, so it would not have carried the fix. What was still missing is
  fixed where it lives: canAccessWorkflow returns a lookup result, and a
  database failure is a 500 instead of "Workflow not found". The DELETE 404 and
  the sort_order validation port as written.
- workflowAddons: still valid. importWorkflowAddon returns the inserted row and
  the route serializes it through the facade's withDatabaseWorkflow, so the 201
  body is GET /workflows/:id's shape; a failed add-on lookup answers 500.
- audit: the private helper had drifted differently by now — it counts every
  project the caller owns without running the organization access check, and
  never admits an organization project reachable through membership. Replaced
  with lib/access.listAccessibleProjectIds all the same; the mocks in both
  audit suites are re-derived against main's query shape (personal projects
  .is("org_id", null), project_access_grants, org_members) rather than the
  shared_with `contains` encoding the original hunk patched.
- sourceDocuments: still valid, expressed as a `credentials` failure kind in
  the module's result union because the route no longer catches. The "one
  Supabase client for the whole request" half of that hunk is kept.

Verified: tsc clean; 261 mounted endpoints byte-identical to main including
registration order; backend suite 1,714 passed | 39 skipped.

Co-Authored-By: Claude Fable 5 <[email protected]>
Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* refactor(backend): collapse module copy-paste that had already diverged into bugs

The per-domain modules were built by copying rather than sharing, and the
copies were drifting independently. Two of those drifts were live bugs:

- Document rename returned HTTP 200 with a synthesized new name even when
  the rename failed: the real rename lives on document_versions.filename,
  and BOTH the projects and library copies never destructured that update's
  error. An RLS denial or null current_version_id showed the UI a rename
  that never persisted. Both now check the error, 404 on a versionless doc,
  and read the row back instead of synthesizing it.
- The projects upload path was a ~130-line fork of createDocumentFromUpload
  and had already dropped three response fields the shared pipeline sets —
  and the durable-conversion work had to be written into both copies.
  Deleted; the shared pipeline gained `surface` and `folderId` params so
  project uploads still audit as surface "project" and still land in the
  project folder the route resolved.

Consolidations (each was implemented 2-8 times with local variations):
suffix validation -> lib/documentTypes.parseAllowedSuffix (8 sites),
countPdfPages -> lib/pdfjs (2 verbatim copies), project-chat's inline SSE
setup -> the existing lib/chat openAssistantSse, workflows' private devLog ->
the one in lib/chat/types.

Version-file cleanup was NOT consolidated into a lib/storage helper: the
durable storage.cleanup job introduced with the queue work already replaced
those fire-and-forget deletes, and routing them back through a best-effort
helper would reintroduce the leak it fixed.

Correctness in the same files:
- tabular: review DELETE, chat delete, and chat rename returned success
  without matching a row (a collaborator 'deleting' a shared review got 204
  for a no-op) — now .select("id") + 404; the chat SSE handler fetched user
  model settings (a profile read + key decrypt) twice per new chat; one
  call site built a second Supabase client by omitting db.
- folder moves: the ancestor walk that blocks moving a folder into its own
  descendant had no cycle guard in EITHER module, so two interleaved moves
  could persist a cycle and any later walk spun forever holding a request
  slot.
- user module's errorMessage() JSON.stringify()d whole PostgREST error
  objects into client-facing detail text; an unrecognised value now falls
  back to a generic message instead of dumping request context.

Regenerate-cell keeps the lease-based ordering the queue work settled on
(claim the generation, stamp the cell, hand the lease to the worker); the
pending-then-generating reordering this commit originally carried was
written against the older sweep, which the lease model has since replaced.

Rebase note (2026-09-14): re-expressed onto the rebased #295 (ec0ac372) and
main aba3cfca. Roughly half of this commit had already been overtaken.

Still applied, unchanged in substance:
- The document rename bug, in BOTH copies. Neither had been touched; both now
  404 a versionless document, check the document_versions update's error, and
  return the stored filename instead of the requested one. The projects side
  gained a `db_error` result kind so the route answers 500 through
  sendInternalError rather than folding it into the 400.
- The folder-move cycle guard, in both projects.folders.ts and
  library.service.ts. (library.service.ts already uses a visited set in two
  other walks, which is what the third one was missing.)
- user.shared.errorMessage no longer JSON.stringify()s a PostgREST object into
  client-facing text.
- project-chat's inline SSE setup now goes through lib/chat's openAssistantSse,
  which grew a write-after-end guard since — so this also fixes a real
  ERR_STREAM_WRITE_AFTER_END path the copy still had.
- The omitted-`db` call site, now in modules/tabular/tabular.prompt.ts.
  draftColumnPrompt takes `db: Db` first, like every other service function.

Dropped as obsolete or already done:
- The projects multipart upload fork and documents.upload.ts do not exist:
  main replaced multipart with upload sessions, and
  modules/uploads/uploads.processing.ts is the one pipeline. It already derives
  `surface` and the folder id from the session, so the `surface`/`folderId`
  params this commit added have nothing to add.
- countPdfPages is already in lib/pdfjs. The one remaining copy in
  uploads.processing.ts is a deliberate file-path/streaming variant, not a
  duplicate, and is left alone.
- workflows' private devLog is already the one from lib/chat/types.
- parseAllowedSuffix is dropped: the eight validation sites collapsed to one
  (uploads.manifest.ts) when uploads became sessions, and a helper with a
  single caller is not a consolidation. Only `documentSuffix` lands, at the
  three sites whose semantics actually match; the other six extension readers
  in the tree differ on purpose and are left alone.
- tabular review DELETE and chat delete no longer need `.select("id")`: #295's
  rebuild puts an explicit access check (ensureReviewAccess /
  ensureReviewChatWriteAccess) in front of both, so the silent-no-op-204 case
  is already impossible, and the surrounding comments argue against adding
  further filters. Chat rename already reads the row back and 404s.
- The double getUserModelSettings in the tabular chat SSE handler is already a
  single call.

Verified: tsc clean; 261 mounted endpoints byte-identical to main; backend
suite 1,714 passed | 39 skipped.

Co-Authored-By: Claude Fable 5 <[email protected]>
Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* fix(backend/lib): citation merge, event-loop stalls, and provider drift

Lib-level bugs, each user-visible:

- CourtListener bulk->API citation fallback merged results POSITIONALLY
  (fallbackRows.shift() per slot), assuming one API row per submitted
  citation. The API returns one row per citation it detects in the text, so
  a 0- or 2-row response shifted every later verdict onto the wrong
  citation. The merge is now keyed by normalized citation string.
- crypto.scryptSync ran on every encrypt/decrypt (~40ms each, measured);
  getUserModelSettings decrypts up to 5 keys per chat request, i.e. ~200ms
  of blocked event loop per request. Derived keys are memoized per
  (secret, salt) — the secret is constant for the process.
- generateDocx's persistence tail was a drifted copy of persistGeneratedFile:
  generated .docx files were the only type that never got a PDF rendition.
  The shared helper now accepts docx and the copy is gone (it also fixes a
  byteOffset-ignoring buffer slice).

Drift/hygiene in the same layer:
- CLAUDE_API_KEY: accepted as an env fallback by the key-status endpoint but
  not by the provider layer — the UI showed a green key, then requests failed
  "not configured". requiredKey now carries an alias table, so the two stay in
  sync where they are actually read.
- Overview RPC builders: projects normalized the email before the
  case-sensitive shared_with containment check; tabular and workflows passed
  it raw (masked only by middleware lowercasing). All normalized.
- Raw LLM stream logging (privileged client document text, for a legal
  product) is hard-disabled under NODE_ENV=production and its env vars are
  documented with an explicit warning.
- One lib/log.ts replaces six byte-identical devLog copies and adds an
  always-on tagged logError; lib/supabase exports the Db type 22 files
  redeclare.
- Stale-work sweep bounded (limit 500): the scan was unbounded and each cell
  costs a job lookup. The sweep's own decision procedure is unchanged — it
  still refuses to act while a generation lease is held, checks the row job
  before the cell job, and writes only through finalizeCell.
- .env.example: 31 vars were read in code but undocumented, including four
  secrets and all 12 RATE_LIMIT_* knobs — all documented now.
- Dead code: docxTrackedChanges' unreferenced _internal test exports,
  logRawOpinionPayload, an unused module-load SYSTEM_PROMPT build, mcp
  sha256Base64Url.

The Ollama abort-shape and the OpenAI/Ollama reader-cancellation fixes are
gone with their targets: those hand-written adapters have been replaced by the
AI SDK, which owns stream cancellation and raises a recognised AbortError.

Rebase note (2026-09-14): re-expressed onto the rebased #295 (ec0ac372) and
main aba3cfca. Everything named above still applies except where noted.

- Relocations only: lib/userApiKeys.ts is modules/user/user.apiKeyStore.ts
  (scrypt memoization), lib/tabularReviewsOverview.ts is
  modules/tabular/tabular.overview.ts and lib/workflowsOverview.ts is
  modules/workflows/workflows.overview.ts. The RPC builders survived main's
  organization rewrite unchanged, so the email normalization lands as written.
  It is now belt-and-braces rather than a fix: the current RPCs compare
  lower(...) on both sides in SQL, so the raw email was no longer reaching a
  case-sensitive containment check. Normalizing at the one place that builds
  the arguments is still how the three stay the same.
- `lib/supabase exports Db` is dropped: #295 already exports it, and every
  file this commit would have edited already imports it.
- The lib/log.ts consolidation is smaller than it was: #295's module split had
  already collapsed six devLog copies to three (middleware/auth.ts,
  lib/courtlistener.ts, and the exported one in lib/chat/types.ts). All three
  now come from lib/log.ts; lib/chat/types.ts re-exports it so the chat tree's
  importers are untouched. logError is new either way — nothing in the tree
  defined one.
- The scrypt memoization applies twice, not once: lib/mcp/client.ts grew the
  same per-call scryptSync since, on a path that runs for every connector tool
  call.
- generateDocx's persistence tail is re-expressed rather than replayed: the
  shared persistGeneratedFile has since grown the ASYNC_DOCUMENT_CONVERSION
  deferred-rendition branch, so widening its extension union to include docx
  gives generated .docx files the queue-backed rendition too, not just the
  synchronous one.
- lib/llm/providers.ts is main's AI SDK rewrite; the alias table is re-derived
  against its requiredKey() rather than the old adapter's. The desync it fixes
  is intact — user.apiKeyStore.envApiKey("claude") still accepts
  CLAUDE_API_KEY and the provider layer still did not.
- Each dead-code removal was re-checked against main: docxTrackedChanges'
  _internal/_fastDiff/_nsAttrs, courtlistener's logRawOpinionPayload, the
  module-load SYSTEM_PROMPT build (its only consumer was the test that
  asserted it equalled buildSystemPrompt(true)), and mcp's sha256Base64Url are
  all still present and still unreferenced.
- .env.example is merged, not replaced: main has re-done that file since, so
  the RATE_LIMIT_AUTH_*, upload-session, SSO and connector blocks are already
  there. What is added is what is still undocumented — CLAUDE_API_KEY, the
  OLLAMA_* trio, COURTLISTENER_BULK_DATA_ENABLED,
  WORKFLOW_CONTRIBUTIONS_ENABLED, MCP_CONNECTORS_ENCRYPTION_SECRET, the twelve
  general/chat/upload/export/delete rate-limit knobs, TRUST_PROXY_HOPS, the
  two raw-stream-log vars, and the three SUPABASE_TEST_* vars.

Verified: tsc clean; 261 mounted endpoints byte-identical to main; backend
suite 1,713 passed | 39 skipped (one fewer than before: the SYSTEM_PROMPT
test went with the constant).

Co-Authored-By: Claude Fable 5 <[email protected]>
Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* test(backend)+ci: type-check the tests, share the mocks, measure what matters

Backend tests were excluded from tsc and vitest strips types without checking
them, so they were never type-checked at all — latent type errors had
accumulated (wrong-arity stubs whose tuple lookups could never be checked,
implicit-any rpc rows, node16 import specifiers). New tsconfig.test.json + a
typecheck:test script wired into CI; all of them fixed, including the ones in
the queue/worker/dbq suites that landed with the durable-queue work.

The integration suites each hand-rolled the same 72-line Supabase
query-builder mock — three copies byte-identical down to the indentation —
plus six identical requireAuth mocks. Extracted to __tests__/helpers/ (the
divergent forks in other suites are left for a follow-up). The shared mock
keeps the `operations` / `rpcCalls` recorders the tabular suites use to pin
the generation-lease protocol's ordering. Test expectation updates for this
branch's behavior changes (tabular delete 404s, audit access via lib/access)
ride along here.

Coverage measured only src/lib/** — routes, modules, workers and middleware
could drop to 0% with CI green. Scope widened to src/** (test files excluded)
and the floors re-derived from a measurement of THIS tree: 47.56% statements /
40.86% branches / 49.78% functions / 49.20% lines, with the ratchet set ~2
points under at 45/38/47/47. docs/testing-coverage.md updated to match.

e2e: shared helpers.ts (selectClaudeModel and the PDF fixture path were copied
per spec), a default expect.timeout of 10s, and the swallowed networkidle
waits replaced with element-visibility waits — networkidle against an app with
SSE/polling never settles, and .catch(() => {}) made the following assertions
race instead of fail.

package.json drops the fast-diff dependency orphaned by the previous commit
(both lockfiles updated). The generated-workflows drift check this commit
originally trimmed is gone entirely: main removed that CI job when the
workflow catalog moved into the database.

Rebase note (2026-09-14): re-expressed onto the rebased #295 (ec0ac372) and
main aba3cfca.

- tsconfig.test.json + typecheck:test + the CI step land as written, but the
  error list is main's, not August's: 78 type errors across 19 suites, most of
  them in files that did not exist when this commit was written (the memory
  curator, orgs pagination, MCP OAuth, the chats-overview stack suite). They
  fall into the same four shapes the original found and are fixed the same
  way: dynamic `import()` specifiers that need the node16 ".js" extension,
  zero-arity `vi.fn()` stubs whose recorded call tuple is empty so every
  `mock.calls[0][0]` assertion type-checked against nothing, `...args:
  unknown[]` spreads into fixed-arity mocks, and fixtures that had fallen
  behind the row/settings types they claim to be (UserModelSettings gained
  memory_curator_model and last_selected_reasoning_level; MemoryFileRow's
  content columns). `types` in tsconfig.test.json mirrors the base config
  rather than adding "multer", which main no longer has.
- The shared mocks are adopted only where a suite still hand-rolls the
  identical block: projects.routes and workflows.routes take both helpers;
  tabular.routes takes authMock only. Its Supabase stub has diverged into a
  different thing — per-table result QUEUES (a route that reads a row and then
  deletes it needs two answers from one table), an `updates` recorder, and a
  tabular_reviews model default — and collapsing it into the shared one would
  have deleted the assertions that pin the generation-lease ordering. The
  suites main added since keep their own forks, as the original left the other
  divergent ones.
- Coverage: #295's own commit already widened the include to src/lib/** +
  src/modules/** with floors 61/52/66/63. Widening further to src/** adds the
  workers, jobs, middleware and app wiring, which only the stack suites touch,
  so the measurement drops slightly rather than rising: re-measured today at
  60.59% statements / 51.98% branches / 64.77% functions / 63.18% lines, with
  the floors set just under at 60/51/64/63. docs/testing-coverage.md carries
  the same numbers in both places it states them.
- fast-diff is still an unreferenced dependency on main, so the drop stands.
  Both lockfiles were edited in place (the dependency line plus its one entry)
  rather than regenerated, so nothing else moves. Note bun.lock is already
  stale against package.json on main — it still records express ^4.21.2 — and
  this commit does not touch that.
- The e2e hunks apply except for createProject: NewProjectModal is a
  three-step wizard now (Details / Access / Add Documents) and the submit is a
  named "Create project" button, so the shared helper carries main's version,
  not August's. selectClaudeModel and the PDF fixture path were unchanged, so
  those extractions are byte-for-byte. The playwright expect timeout, the
  networkidle removals and the generated-workflows note land as written.

Verified: tsc clean on both tsconfigs (`npm run build`, `npm run
typecheck:test`); backend suite 1,713 passed | 39 skipped; coverage gate
passes at the new floors; the architecture fitness test passes; 261 mounted
endpoints byte-identical to main.

Co-Authored-By: Claude Fable 5 <[email protected]>
Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* fix(frontend): one upload path, one SSE reader, and guarded stream lifecycles

Five upload endpoints threw new Error(await response.text()) instead of
MikeApiError, so isMfaRequiredError was unconditionally false for them — a
403 mfa_verification_required on upload showed the user raw JSON instead
of opening the MFA popup. All seven copy-pasted multipart blocks now go
through one apiUploadRequest helper with the real error contract, and a
regression test pins the MFA path. downloadDocumentsZip had the same
defect.

The SSE wire format was parsed by three hand-rolled inline loops that had
already drifted: only one flushed the decoder and trailing buffer on
stream close, so the other two silently dropped the final frame whenever
the stream ended without a trailing newline; one swallowed every malformed
frame in a bare catch. Replaced with one readSseFrames async generator
(CRLF handling, decoder flush, [DONE] semantics, reader.cancel on early
exit) with 10 transport tests. The 12 existing useAssistantChat SSE tests
pass unchanged.

Stream lifecycles had no ownership discipline: useAssistantChat never
aborted on unmount and a second send overwrote the shared AbortController
and events ref while the first loop kept appending — two responses could
interleave into one message. TRChatPanel's 16ms drip interval and reader
survived unmount (the panel is conditionally mounted), and loading another
chat let old frames rewrite the new chat's messages. Both now use a
generation counter that stales out superseded turns, abort the previous
stream on entry, and clean up on unmount.

Auth/profile context values were rebuilt every render, re-rendering every
consumer on every provider render. Both are memoized now, so a consumer
re-renders when the value it reads actually changes.

Dead code: useGenerateChatTitle (titles arrive via the chat_title SSE
frame), five unreferenced mikeApi endpoints, eight needlessly-exported
types.

Co-Authored-By: Claude Fable 5 <[email protected]>

Rebase note (2026-09-14): the "one upload path" half is dropped. Main has
since replaced every multipart upload with pre-signed upload sessions
(uploadFilesWithSession); mikeApi.ts no longer constructs a FormData
anywhere, uploadWorkflowReferenceFile/replaceWorkflowReferenceFile are
gone entirely, downloadDocumentsZip already throws MikeApiError, and the
"multipart upload endpoints" describe block is already deleted from
mikeApi.test.ts — so apiUploadRequest and its MFA regression test have no
subject left. The SSE reader, the stream-lifecycle guards, the memoized
context values and the dead-code removals are re-applied on main's code;
each removed symbol was re-verified unreferenced on main first.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* fix(frontend): component correctness, keyboard access, and dead weight

Effect and updater misuse that produced real misbehavior:
- AskInputPopup ran submit() from a useEffect with NO dependency array,
  guarded only by a flag — a double network turn under StrictMode. The
  submit now fires from the interaction handlers, with a pendingSkipId
  threaded through so skipping the last item serializes as skipped rather
  than as an empty answer.
- The project chat page called three sibling setters from INSIDE its
  setTabs updater (updaters must be pure; StrictMode double-invokes them),
  fetched the project twice on every mount because its mutation-signature
  guard was always truthy, and carried a reloadingDocIds map that was only
  ever deleted from — the whole doc-reload wiring was dead and is gone.
- PdfView cleared shared DOM/refs then appended across per-page awaits
  with six ungated call sites — two overlapping renders (rapid zoom)
  interleaved pages and corrupted the highlight index. Renders now carry a
  generation stamp and cancel the previous pdfjs render task.
- NewTRModal snapshotted fixedProjectDocs into state on open; the parent
  passes [] until the project loads, so opening early left the picker
  permanently empty. The list is now derived from the prop.
- selectAllMatching in all three pagination hooks had try/finally with no
  catch and every call site void'ed it — a failed ids fetch was an
  unhandled rejection and a checkbox that silently did nothing.
- Sidebar persistence read one state but depended on another, storing a
  value one toggle stale.

TableRow rendered a div with onClick and no keyboard affordance — every
row-based screen (19 usages) was keyboard-unreachable. Fixed once in the
primitive: role=button, tabIndex, Enter/Space, with a target check so
nested controls don't double-fire.

Dead code verified and removed: ProjectPickerModal, AddColumnModal's
never-passed editing props and their unreachable branches, unused icon and
citation-helper exports (three list entries turned out to be live internal
helpers — un-exported instead of deleted).

Co-Authored-By: Claude Fable 5 <[email protected]>

Rebase note (2026-09-14): re-applied on a main that had rewritten several of
these files. Still-present bugs re-fixed on main's code: AskInputPopup's
dependency-array-less auto-submit effect (main had grown multi_choice items in
the meantime — kept), the project chat page's setters-inside-setTabs and
always-truthy mutation-signature guard, PdfView's ungated per-page renders (main
already had the generation stamp, so only the render-task cancel and the extra
isStale() checks were needed), NewTRModal's fixedProjectDocs snapshot, the three
selectAllMatching hooks' missing catch, TableRow's missing keyboard affordance
and the sidebar-persistence stale-state write. Every removal was re-verified
unreferenced on main first: ProjectPickerModal, the AddColumnModal editing props
(main routes column editing through WFEditColumnModal), pillUtils' parsePills /
PillSegment, getPresetPrompt, the Open*FolderSvgIcon exports, NAME_COL_W and
ProjectAssistantTable's allChatsSelected/someChatsSelected. The last pair is now
also dropped from the two ProjectAssistantTable tests main added, which passed
props the component never read. The tsconfig.json hunk is dropped: it was
formatting-only churn, and main's "paths" block has since grown the @mike/*
aliases.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* fix(word-addin): truthful edit cards, fail-closed privacy, and real UI sharing

Three user-facing bugs in the tracked-edit flow:
- mutationApplied was set AFTER the context.sync() that executes the queued
  insertText, so a post-mutation sync failure reported "Word couldn't apply
  this change" for an edit that IS in the document — and the user
  re-applied it. The flag now flips before the sync, and that failure mode
  reports applied-but-unmanaged instead of error.
- Clicking View on an edit the user had already accepted in Word's Review
  tab DELETED the bookmark anchor and demoted the card to historical — a
  read-only action destroying data. Reveal is now non-destructive; the
  resolve/release path keeps sole ownership of deletion. A new e2e test
  pins this (verified red with the old behavior).
- A failed OfficeRuntime.storage read left chat storage at the "cloud"
  initial value — silently inverting an explicit "local, never persist my
  chats server-side" privacy choice. It now fails closed to local: a wrong
  guess of local is recoverable, a wrong guess of cloud is not.

Shared-UI drift, authored in rather than accumulated (the tokens never
matched the frontend, the catalog shipped incomplete):
- The webpack define was a fourth copy of the default model id, drifting
  from modelCatalog.ts's DEFAULT_MODEL_ID the moment either moved; it is a
  deployment override only now, empty by default.
- Four design tokens differed from the frontend under identical names; the
  composer lost its max-height cap and its accessible button names.
- mike-icon was a byte-identical 320-line copy — now an @mike/* alias like
  the rest of the shared layer; the dead, drifted button.tsx fork is gone.
  (input/tab-pill/toggle-switch stay vendored: their diffs are not purely
  mechanical, and aliasing them would drag the web app's @/ convention
  into the add-in build.)
- CI path filter claimed the add-in was self-contained, but the bundle
  includes frontend/src/shared/ui — shared-UI changes now trigger the
  add-in checks. The three e2e-live scripts got npm entries and typecheck
  coverage so they stop rotting invisibly.

Full add-in e2e suite: 149/149 on chromium and webkit.

Co-Authored-By: Claude Fable 5 <[email protected]>

Rebase note (2026-09-14): the mike-icon aliasing is dropped — main already
aliases it as @mike/mike-icon-ui in both webpack.config.js and tsconfig.json,
and word-addin/src/shared/ui/mike-icon.tsx is gone. The unreferenced
--color-azure token is dropped too: the add-in's tokens.css already carries the
overridden blue scale the frontend uses for Mike azure, and nothing in either
app reads that name. Everything else applied onto main's build config unchanged;
the three behavioural fixes, the four token corrections, the composer cap and
button names, the button.tsx removal (no importers left), the CI path filter and
the e2e-live npm/typecheck entries are all re-applied.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* test(frontend): a failing reader.cancel() must not eat the frames already read

The shared SSE reader cancels the underlying body reader during cleanup.
On a connection the server already tore down, cancel() rejects — and that
rejection is cleanup noise, not a result: the frames the consumer already
received are the outcome of the read. Pin that contract with a test that
mocks a rejecting cancel() and asserts the parsed frames still surface.

(Follow-through on the "one SSE reader" commit in this stack, which
centralized every stream consumer on readSseFrames.)

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01W5hmSY9CMhLRMPw7UkTrGE

* refactor(backend): share document, folder, and chat service operations

* refactor: centralize document lifecycle and finish module boundaries

* fix: preserve document cleanup across worker failures and disabled queues

* fix(frontend): stop aborting the answer when the tabular chat panel goes away

WHY THIS MATTERS
The backend treats a closed response socket as a user cancellation: in
lib/chat/routeStreaming.ts `res.on("close")` stops the model and persists
whatever text arrived so far, labelled "Cancelled by user.". So any abort
the client fires for a reason other than "the user pressed Stop" silently
destroys an answer the user was waiting for — and the destruction is
permanent, because the truncated row is what reload shows.

WHAT WAS WRONG
This PR's stream-lifecycle commit added an abort to the tabular review chat
panel (TRChatPanel) on unmount, New chat, Load chat and Submit. On main only
the Stop button ever aborted. Live A/B on production builds
(.claude/pr295-evidence/reports/04-F2-assistant-chat.md,
f2-02d-fast-navigate-{pr,main}.*) showed the same abort in the assistant
hook truncating a 457-character answer to a 117-byte "Cancelled by user."
row; the panel shared the pattern.

HOW THE FIX WORKS
Leaving the panel now *detaches* the stream instead of cancelling it: the
generation counter is still bumped (a stream nobody owns must not repaint
someone else's message list), but the AbortController is left alone and the
frame loop `continue`s instead of `break`ing — breaking out of the
for-await cancels the reader, which closes the socket and would truncate
the answer just as an abort does. TRChatPanel.handleCancel() — the Stop
control — still aborts and still renders "Cancelled by user.".

The assistant hook half of this fix is no longer in this commit: after the
rebase onto main f024fe01, main's rewritten useAssistantChat (#483) owns
that code, and its own thread-switch abort is fixed on main by PR #486
("leaving a thread detaches the answer instead of cancelling it"). The
hook regression test that lived here moved to #486 with that fix.

HOW IT WAS VERIFIED
The tabular suites (36 tests), tsc --noEmit and eslint on the touched file.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01Qj1MMk8Dbtx8P7PKQHyJA8

* fix(frontend): always release the select-all flag when its request ends

WHY THIS MATTERS
`selectingAll` disables the header select-all checkbox while the ids
request is running. If it is never lowered, the checkbox stays disabled for
the rest of the session: clicking it does nothing, no request is issued,
and the only way back is a full page reload. Select-all is how a user acts
on a filtered set larger than one page, so the whole bulk-action path on
/workflows and /tabular-reviews is dead until then.

WHAT WAS WRONG
This PR gated the `finally` on the *list* request version:
`if (requestVersion === requestVersionRef.current) setSelectingAllRequest(false)`.
That ref belongs to the list query and is bumped by the list-reset effect,
so typing in the search box (or changing scope/sort) while the ids request
was in flight meant the version no longer matched when the response landed
and the flag was never cleared. Live A/B on production builds
(.claude/pr295-evidence/reports/06-FR-frontend-ab.md,
fr-01-selectall-latch-pr.log vs fr-01-selectall-latch-main.log): on this PR
the checkbox stayed `disabled=""` through clearing the search and a second
click issued no ids request at all; on main it recovered as soon as the
response arrived.

HOW THE FIX WORKS
Select-all now has its own counter, `selectAllRequestRef`, bumped by each
selectAllMatching call. The `finally` clears the flag when the call that
raised it is still the newest select-all, no matter where the list query
has moved to — only a newer select-all keeps it up, because that one owns
the flag now. The staleness guard on the *result* is untouched: the
`requestVersion !== requestVersionRef.current` early return still stops ids
fetched for a previous query from being applied to the new list.

HOW IT WAS VERIFIED
One regression test per hook — "clears selectingAll when the search changes
while the ids request is in flight" in
usePaginatedWorkflows.test.ts (new), usePaginatedProjects.test.ts (new) and
usePaginatedTabularReviews.test.ts — holds the ids promise open, re-renders
with a new search, then resolves it and asserts `selectingAll` is false and
the stale ids were not applied. All three fail against the pre-fix hooks.
Ran the three hook suites (10 tests), tsc --noEmit and eslint.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01Qj1MMk8Dbtx8P7PKQHyJA8

* fix(frontend): keep clickable table rows out of the tab order

WHY THIS MATTERS
Every list in the app (projects, workflows, documents, reviews) is built
from TableRow. Turning each row into a focusable button changes how the
whole product is navigated: a 30-row page grows from 60 tab stops to 90,
Space no longer scrolls the list but opens whatever row happens to be
focused, and a screen reader announces each row as one unnamed button that
contains a checkbox and another button — nested interactives, with the
row's accessible name being its concatenated text.

WHAT WAS WRONG
This PR gave TableRow `role="button"`, `tabIndex={0}` and Enter/Space
activation whenever an `onClick` was passed. Live A/B on production builds
(.claude/pr295-evidence/reports/06-FR-frontend-ab.md,
fr-02-row-tabstops-pr.log vs fr-02-row-tabstops-main.log): 30 row divs with
role=button and tabIndex=0 on this PR versus 0 on main, 3 tab stops per row
versus 2, and Space on a focused row opened the "Select workflow" dialog
instead of paging. Rows have always been a mouse shortcut on top of
controls that are themselves reachable — the row's own actions are the
checkbox and the row-actions menu button inside it.

HOW THE FIX WORKS
Reverts TableRow to main's shape: onClick, onKeyDown, role and tabIndex are
no longer intercepted, so they flow through `{...props}` exactly as before
and a plain onClick row carries no role, no tabIndex and no key handling.
The file is now byte-identical to origin/main. Making rows keyboard
operable is a worthwhile change, but it needs a design that does not nest
the checkbox and menu button inside a row-level button — it belongs in its
own PR, not in a no-behavior-change refactor.

HOW IT WAS VERIFIED
New test in TablePrimitive.test.tsx, "leaves a clickable row out of the tab
order and the a11y tree": asserts a row rendered with onClick has no role
and no tabindex attribute, exposes no button role, and still fires onClick
on a real user-event click. It fails against the pre-fix component. No
existing test pinned the row-button behavior. Ran the TablePrimitive suite
(18 tests), tsc --noEmit and eslint.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01Qj1MMk8Dbtx8P7PKQHyJA8

* fix(frontend): persist the sidebar state that is actually rendered

WHY THIS MATTERS
The sidebar toggle is on every page of the app. When the stored value and
the rendered sidebar disagree, the first click after a reload appears to do
nothing — the user has to click twice to collapse a sidebar that is plainly
open — which reads as a broken button rather than a preference.

WHAT WAS WRONG
The layout keeps two states: `isSidebarOpen` (what AppSidebar renders) and
`isSidebarOpenDesktop` (what the toggle flips). Only the first has a
mount-time value on desktop: its initializer always starts open. This PR
switched the persistence effect to write `isSidebarOpenDesktop` while
leaving the initializer and the render on `isSidebarOpen`, so a stored
"false" came back as a desktop preference the screen did not reflect. Live
A/B on production builds (.claude/pr295-evidence/reports/06-FR-frontend-ab.md,
fr-03-sidebar-toggle-pr.log vs fr-03-sidebar-toggle-main.log): on this PR,
collapse then reload rendered the sidebar OPEN with "false" stored, and the
first toggle click only flipped the hidden preference back to "true" —
still open, a visible no-op — with the second click finally collapsing. On
main the same click collapsed it.

HOW THE FIX WORKS
Restores main's line: the effect persists `isSidebarOpen`, the value on
screen, so whatever a later mount restores agrees with what it renders and
the first click always moves the sidebar. Remembering a collapsed sidebar
across reloads is a real improvement, but it needs the mount path to apply
the stored value as well — that is a separate change, and the comment now
says so. The rest of this PR's hunk in the file (the memoized context
values and the extracted setSidebarOpen callback) is kept.

HOW IT WAS VERIFIED
Two new tests in layout.mobileHeader.test.tsx under "desktop sidebar
persistence": "stores the state the sidebar is rendered with" (stored
"false" + rendered open must persist "true") and "collapses on the first
toggle click after a remount" (mount, unmount, re-mount reading the stored
key back, then one click must collapse). Both fail against the pre-fix
layout. Ran the layout suite (5 tests), tsc --noEmit and eslint: the file
is back to main's single pre-existing exhaustive-deps warning (0 errors),
which main reports identically.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01Qj1MMk8Dbtx8P7PKQHyJA8

* fix(backend): drain trigger-written document cleanup in seconds, not hours

WHY THIS MATTERS
Deleting a document, a project or an account is the moment a user is told
their bytes are gone. The 204 is a promise, and the object store is where it
is kept. If the queue that owns the deletes drains at five rows a minute, the
promise is a lie for as long as the backlog lasts — and the bigger the delete,
the longer the lie.

WHAT WAS WRONG
Centralizing the lifecycle moved cleanup from "one storage.cleanup job per
operation, carrying every key" to "one document.cleanup row per mutated
version, written by a database trigger". Two consequences compounded:

  * Nothing hands those rows to BullMQ. enqueueDbJob delivers the rows IT
    inserts (the transactional-outbox half), but a trigger-written row has no
    application code holding its id, so the only collector left is the poller
    — which idles at 60s precisely BECAUSE Redis deployments expect delivery
    to be instant. lib/memory/schedule.ts:115 already had the answer for its
    own trigger; the cleanup rows never got it.
  * The poller claims CLAIM_BATCH = 5 rows per tick.

Measured (lc-01-drain.log): deleting a project with 20 documents produced 20
rows and took 20.8s to drain at the 5s no-Redis cadence, where main finished
the same delete with a single 40-key job in about 3s. Extrapolated to a real
matter — 1,000 documents times 3 versions — that is ~50 minutes without Redis
and about TEN HOURS with it.

HOW THE FIX WORKS
Both halves, because either alone still leaves a slow path.

  1. Coalescing (documents.cleanupJobs.ts). When handleDocumentCleanup runs
     for a claimed row it now claims the rest of the pending cleanup backlog
     itself — up to 500 rows — through claim_db_jobs, which this migration
     teaches an optional p_kind filter. Every claimed key joins one
     deduplicated pass: one precompute-liveness probe, one live-reference
     lookup (now chunked, since a coalesced run carries far more keys than one
     row's worth), one delete sweep. Each row then gets its OWN outcome,
     written under the same id+status+attempts+claimed_at fence the runner
     uses, so a key that keeps rejecting deletes retries only the row that
     asked for it and a reclaim by another runner is never overwritten. If the
     run cannot proceed at all — a deferral, a failed lookup — every row we
     claimed is handed back to `pending` rather than left to age out.
  2. Delivery (lib/dbq/enqueue.ts, requestDocumentCleanupDelivery). The
     completion path every delete already calls now asks BullMQ to pick up a
     handful of pending cleanup rows. A handful is enough: the first one to
     run coalesces the rest. Claims are idempotent — a duplicate delivery
     matches zero rows — so over-delivering is free, and the whole call is
     best-effort: on failure the poll backstop still runs them.

The per-version trigger contract is untouched: the database still writes one
row per mutated version, and that row is still the durable retry record. Only
who picks them up, and how many at a time, changed. A database without the
kind-filtered claim (code ahead of migration) logs and drains one row per job:
slower, never wrong.

HOW IT WAS VERIFIED
New unit tests in documents.cleanupJobs.test.ts: N pending rows drain in one
handler run with one deduplicated delete sweep and one fenced `done` write per
row; a key that fails retries only its own row; a deferral hands every claimed
row back with its attempt refunded; a claim RPC that rejects the kind filter
falls back to single-row draining; and a delete asks Redis to deliver the
trigger's rows. The migration was applied twice to a fresh local Postgres
(exit 0 both times) and both call shapes — claim_db_jobs(5, 600) from an older
backend and the named 3-argument form — resolve.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01Qj1MMk8Dbtx8P7PKQHyJA8

* fix(backend): stop inline cleanup from failing the delete it is cleaning up after

WHY THIS MATTERS
A delete route's job is to remove the rows the user asked to be gone. Cleaning
up the bytes behind them is follow-up work, and it has its own durable record
— the db_jobs row the version trigger wrote inside the deleting transaction.
When follow-up work is allowed to fail the request, the user sees an error for
an operation that actually succeeded, and on the erasure path the cascade
stops partway through: content erased, account still usable.

WHAT WAS WRONG
The queue-disabled fallback (DB_JOBS_ENABLED=false) replaced helpers that
never threw with ones that throw three different ways:
`assertStorageConfigured()` up front in the capture, `DbJobDeferredError` when
a precompute job holds the bytes, and `document_cleanup_failed:N` when a
delete is rejected. Those throws reach the route.

Measured with a proxy failing storage DELETE (backend-dead.log,
lc-04-inline-fail.*, backend-delfail.log):

  * DELETE /single-documents/:id — 500, row gone. Main: 204.
  * DELETE /projects/:id — 500, project and documents gone. Main: 204.
  * DELETE /user/account — 500; documents and projects erased; the auth.users
    row still present and RE-LOGIN SUCCEEDS. Main: 204, auth row gone, login
    refused. The throw at user.dataCleanup.ts:886 skips the orphan sweep, the
    organization settlement, and the caller's auth.admin.deleteUser.

An unconfigured object store was worse than a broken one: the capture asserts
before the delete runs, so the rows were not deleted either.

HOW THE FIX WORKS
Both inline halves become best-effort, and they say why in the code:

  * captureInlineDocumentCleanup wraps its whole body. It runs BEFORE the rows
    are deleted, so anything it throws cancels a delete the caller already
    authorized. A missing object store or a failed lookup now costs the inline
    snapshot and nothing else.
  * captureInlineVersionUpdateCleanup does the same for a version replace.
  * completeInlineDocumentCleanup catches whatever handleDocumentCleanup
    throws and logs it through logError. The job form keeps throwing — that is
    how the runner learns to retry — but the request form does not.

Nothing is dropped by doing this. The trigger's db_jobs row is still pending
and still the durable retry record, so a runner (or the next release with the
runner re-enabled) removes the bytes later. Account deletion now reaches
auth.admin.deleteUser again, which is the whole point of ordering data first
and auth last. Note what is deliberately NOT relaxed: deleteUserExportArtifacts
still throws, because each object under exports/ is a full copy of the account
and its listing is the last enumeration of those objects anywhere.

HOW IT WAS VERIFIED
documents.cleanupJobs.test.ts: an inline delete that the object store rejects
resolves instead of throwing; an unconfigured object store yields an empty
capture and a clean completion with no deletes attempted; a failed snapshot
lookup returns empty rather than propagating. userDataCleanup.test.ts: with
deleteFile rejecting every call, deleteUserAccountData now RESOLVES and the
steps after cleanup still run (documents and projects reduced to the rows
another user owns) — the regression that left a deleted account able to log
in. The suite already runs with DB_JOBS_ENABLED=false, so these exercise the
same path the live replication used. Existing assertions that the export-
artifact failure still propagates are untouched and still pass.

Co-Authored-By: Claude Fable 5.1 <[email protected]>
Claude-Session: https://claude.ai/code/session_01Qj1MMk8Dbtx8P7PKQHyJA8

* fix(backend): make both halves of the lifecycle rollout survivable

WHY THIS MATTERS
Code and migrations never land at the same instant, so every deployment
crosses one of two windows. This subsystem was unsafe in both, and in opposite
ways: one window produces a hot loop that starves the queue, the other deletes
user data while silently leaking the bytes behind it. Neither announced
itself.

WHAT WAS WRONG
(a) Migration before code. The new claim revives `failed` cleanup rows
unconditionally — no run_at gate — and rewrites max_attempts to INT_MAX. An
older runner meeting `document.cleanup` marks it "unknown job kind" and fails
it again immediately, so the claim picks the same rows up on the very next
tick. Measured against a main-version runner on the migrated database
(scratchpad/main3002.log): 20 trigger rows climbed to attempts 25 in two
minutes and monopolized the five-row claim batches for the whole window, while
main's own storage.cleanup job sat unstarted for over tw…
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Generated DOCX/XLSX/PPTX filenames drop every non-ASCII character in the title

3 participants