AI Search Expanded File Support Phase 3: XLSX Extraction Prompt

Status: Ready for implementation
Priority: High
Dependencies: Expanded file support Phase 1 PDF, Phase 2 DOCX

Prompt

You are working in C:\code\stratofusion on Windows 11 PowerShell. Do not use WSL or bare bash. Use pnpm.

First:
- Run `git status --short` and inspect relevant `git diff`.
- If there are unrelated uncommitted changes, summarize them and commit them separately before starting this phase.
- Do not overwrite, revert, or clean up user changes unless explicitly approved.
- Read README.md and the relevant canonical docs under public/docs before coding.
- Review Phase 1 PDF extraction in src/lib/search/ai/extraction/pdf-text.ts.
- Review Phase 2 DOCX extraction in src/lib/search/ai/extraction/docx-text.ts.
- Follow the same extraction architecture: dedicated parser module, typed parser error, narrow policy routing, existing normalization and byte-limit enforcement.

Goal: implement Expanded File Support Phase 3 for AI Search: deterministic XLSX text extraction for AI indexing only.

Important architecture notes:
- AI indexing does NOT use src/lib/content-extraction.ts.
- The live AI indexing pipeline uses src/lib/search/ai/extraction/extract-document.ts via BackendIndexingRunner.
- Implement XLSX support only in src/lib/search/ai/extraction.
- Preserve existing text-like, PDF, and DOCX extraction behavior.
- Do not implement PPTX, Google Workspace export, RTF, ODT, OCR, charts, images, macros, or legacy XLS in this phase.
- Do not mark XLS/XLSX broadly supported until the XLSX extractor branch is implemented and tested.
- Legacy binary .xls files must remain unsupported unless explicitly approved.

Scope:
1. Add an XLSX extraction dependency only after verifying it works with the repo's Next/Vitest/Node setup.
   - Prefer a deterministic server-side JS library with no external binaries.
   - Candidate libraries:
     - SheetJS `xlsx`: verify current registry version, Apache-2.0 license, type definitions, ESM/CJS import shape, Vitest behavior, maintenance/security posture, and Next build behavior.
     - `exceljs`: acceptable if SheetJS has licensing, maintenance, synchronous-timeout, or bundling issues. Verify MIT license, import shape, types, Vitest behavior, and Next build behavior.
   - Use `pnpm add <package>` only after the isolated verification.
2. Extend src/lib/search/ai/extraction/extract-document.ts so XLSX inputs are parsed as XLSX, not decoded as UTF text.
3. Update src/lib/search/ai/extraction/supported-file-policies.ts to mark XLSX supported only after extraction works.
4. Add focused tests under src/lib/search/ai/extraction.
5. Add BackendIndexingRunner-level coverage in a separate XLSX runner test file.
6. Update README.md, relevant public/docs files, and 9.AI_Search_Plan_Consolidated_Implementation_Spec.md.
7. Run focused verification and commit the phase.

Supported XLSX inputs:
- Extension: .xlsx
- MIME: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
- Parameterized MIME must resolve correctly, for example:
  application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8

Unsupported formats that must remain unsupported:
- .xls and application/vnd.ms-excel
- .ppt, .pptx, .ods, .numbers, .csv changes beyond existing text-like CSV behavior
- Google Workspace native MIME types and exports
- RTF, ODT, media, archives, unknown binary formats
- Embedded images, charts, macros, and OCR

Extraction strategy:
- Extract all visible worksheets in workbook order.
- Skip hidden and very-hidden sheets when the selected parser exposes visibility metadata.
- If visibility metadata is not reliable in the selected parser, document the limitation in code/tests/docs and choose the safest deterministic behavior.
- Concatenate non-empty sheet text using sheet delimiters:

  --- Sheet: SheetName ---
  row cell values

  --- Sheet: NextSheet ---
  row cell values

- Use row-by-row extraction.
- For each row, join non-empty cell values with tab characters.
- Preserve header rows by treating all rows equally; do not add automatic header detection.
- Skip empty cells within rows.
- Skip entirely empty sheets.
- If every visible sheet is empty, the XLSX extractor should return an empty
  string; `extractDocument` should convert that to a non-indexable unsupported
  result with:
  XLSX did not contain extractable text for AI indexing

Cell value rules:
- Extract displayed/result values, not formula syntax.
- For formula cells, index the cached/displayed result when present.
- Do not index formula expressions such as =SUM(A1:A10).
- Skip formula error cells such as #REF! or #DIV/0! rather than indexing error noise.
- Strings: extract as-is.
- Numbers: convert deterministically to strings.
- Booleans: convert deterministically to strings.
- Dates: convert to ISO 8601 date text when the parser exposes a Date object reliably; otherwise use the parser's deterministic display text.
- Empty/null/undefined cells: skip.
- Prefer direct cell inspection plus an explicit formatter if `sheet_to_json(..., raw: false)` cannot satisfy these rules precisely.

Error and result semantics:
- Current extractDocument result types are only `success` and `unsupported`.
- Known non-indexable XLSX cases should return `unsupported` when cleanly identifiable:
  - empty/no extractable text
  - password-protected/encrypted files
- Parser failures should throw a sanitized `XlsxParseError` extending the shared `ExtractionError`; BackendIndexingRunner will mark the item failed with that message.
- Corrupt XLSX parser failure message:
  Spreadsheet appears to be corrupt. Please re-save the file and try again.
- Password/encrypted unsupported message:
  Spreadsheet is password-protected. Please provide an unlocked copy.
- Parser timeout failure message:
  XLSX text extraction timed out after {timeoutMs} ms
- Do not leak file contents, sheet values, paths outside metadata, or raw parser internals in failure reasons.

Limits and byte handling:
- Keep DEFAULT_MAX_EXTRACTED_BYTES behavior unless there is a strong reason to change it.
- Enforce source byte limit before parsing using the existing extractDocument path.
- Normalize extracted text through normalizeExtractedText().
- Enforce extracted UTF-8 text byte limit after parsing using the existing extractDocument behavior.
- Do not truncate and succeed in this phase. Oversized extracted text should fail clearly, consistent with PDF/DOCX/text behavior.

Timeout requirement:
- Add DEFAULT_XLSX_EXTRACTION_TIMEOUT_MS = 10_000.
- Make timeout internally configurable/testable.
- Important: SheetJS parsing is synchronous. A normal Promise.race timeout does not interrupt CPU-bound parsing once it starts.
- If using SheetJS:
  - either implement a real bounded parser path, such as worker-thread based parsing, or
  - document the timeout as best-effort and do not claim hard cancellation.
- If using an async parser path, verify the timeout test proves the intended behavior.

Suggested design:
- Add src/lib/search/ai/extraction/xlsx-text.ts:
  - DEFAULT_XLSX_EXTRACTION_TIMEOUT_MS
  - XlsxExtractionError and XlsxParseError
  - XlsxExtractionFailureCode = "encrypted" | "parse_failed" | "timeout" | "unknown"
  - ExtractXlsxTextOptions with timeoutMs and any test-only parser injection needed
  - isXlsxPolicy(policy)
  - extractXlsxText(bytes, options)
- Add src/lib/search/ai/extraction/xlsx-test-support.ts for generated minimal fixtures.
- Keep parser-specific complexity inside xlsx-text.ts.
- Do not route text-like, PDF, or DOCX formats through XLSX logic.
- Add the XLSX branch in extract-document.ts after DOCX/PDF branches and before text fallback.
- Catch XlsxExtractionError with code "encrypted" in extractDocument and return unsupported.
- Let parse_failed and timeout errors throw so BackendIndexingRunner marks the item failed.
- Add `// Reason:` comments only for non-obvious tradeoffs, especially parser timeout limitations or visible-sheet handling.
- Keep every file under the repo's 500 LOC rule.

Tests to add/update:

1. supported-file-policies.test.ts
- XLSX supported by .xlsx extension.
- XLSX supported by OpenXML spreadsheet MIME.
- XLSX supported by parameterized OpenXML spreadsheet MIME.
- Legacy .xls remains unsupported.
- Existing unsupported presentation and Google Workspace formats remain unsupported.
- PDF remains supported.
- DOCX remains supported.
- Existing text-like formats still pass.
- getV1SupportedFilePolicies includes xlsx only after implementation works.

2. extract-document.test.ts
- Extracts text from a simple single-sheet XLSX.
- Extracts text from multiple visible sheets and includes sheet name delimiters.
- Skips hidden sheets if the selected parser exposes reliable hidden-sheet metadata.
- Does not decode raw XLSX zip bytes as UTF text.
- Enforces source maxBytes before parsing.
- Enforces extracted text byte limit after parsing by rejecting with the existing extracted-size error.
- Handles corrupt XLSX by rejecting with sanitized XlsxParseError text.
- Handles empty XLSX as empty extractor output that `extractDocument` converts
  to unsupported with the no-extractable-text reason.
- Handles password-protected/encrypted XLSX as unsupported. If generating a real encrypted fixture is impractical, add a testability hook and simulate the parser encryption error.
- Preserves metadata on successful XLSX extraction.
- Handles formula errors gracefully by skipping them.
- Handles mixed cell types: strings, numbers, booleans, dates, empty cells.
- Respects the configured timeout according to the chosen parser design. Do not fake a hard timeout if the parser cannot be interrupted.
- Existing UTF/text tests still pass.
- Existing PDF tests still pass.
- Existing DOCX tests still pass.

3. xlsx-test-support.ts
- Generate small XLSX fixtures without large binary files.
- Suggested helpers:
  - createMinimalXlsxBytes(sheets: { name: string; rows: unknown[][]; hidden?: boolean }[]): Buffer
  - createEmptyXlsxBytes(): Buffer
  - createCorruptXlsxBytes(): Buffer
- It is acceptable to use the selected XLSX library to generate fixtures, but extraction tests must still exercise real extraction behavior.

4. backend-indexing-runner-xlsx.test.ts
- XLSX item with extractable text is chunked, embedded, and upserted with correct metadata.
- Empty/non-indexable XLSX is skipped clearly without vector upsert.
- Corrupt XLSX is failed clearly without vector upsert.
- Multi-sheet XLSX text reaches the chunker; do not require exact chunk count unless the test controls chunking deterministically.

Docs to update:
- README.md:
  - AI indexing supports deterministic text-like formats, PDFs with embedded text, DOCX with extractable text, and XLSX spreadsheets with extractable cell values.
- public/docs/SEARCH_FEATURES.md:
  - Add XLSX support.
  - Document visible-sheet extraction, sheet delimiters, cell values not formula syntax, and no legacy .xls.
  - Document no charts/images/macros/OCR.
- public/docs/PRIVACY_ARCHITECTURE.md:
  - Mention XLSX cell values from visible sheets may be extracted into text chunks and embedded when AI indexing is enabled.
- public/docs/API_REFERENCE.md:
  - Update AI indexing supported file type language.
- 9.AI_Search_Plan_Consolidated_Implementation_Spec.md:
  - Update expanded file support Phase 3 status.
  - Update existing support language. If no support matrix exists, do not invent one unless it improves clarity and stays concise.
- public/docs/prompts/INDEX.md:
  - Add this prompt document link only if prompt docs are indexed there.

Verification:
Run at minimum:

pnpm test -- src/lib/search/ai/extraction
pnpm test -- src/lib/search/ai/indexing/backend-indexing-runner.test.ts
pnpm test -- src/lib/search/ai/indexing/backend-indexing-runner-pdf.test.ts
pnpm test -- src/lib/search/ai/indexing/backend-indexing-runner-docx.test.ts
pnpm test -- src/lib/search/ai/indexing/backend-indexing-runner-xlsx.test.ts
pnpm typecheck
pnpm exec eslint --config .eslintrc.json src/lib/search/ai/extraction
pnpm exec eslint --config .eslintrc.json src/lib/search/ai/indexing/backend-indexing-runner-xlsx.test.ts

If package or bundling changes are nontrivial, also run:

pnpm build

Deliverable:
- Commit the changes with a clear message, for example:
  feat: add xlsx ai indexing extraction
- Summarize changed files, including new files and dependency changes.
- Summarize exactly which XLSX cases are supported:
  - .xlsx extension
  - OpenXML spreadsheet MIME
  - visible sheets
  - row-by-row cell values
  - mixed primitive cell types
  - cached/displayed formula results when present
- Summarize skipped/unsupported cases:
  - empty XLSX
  - password-protected/encrypted XLSX
  - legacy .xls
  - hidden sheets if intentionally skipped
  - charts/images/macros/OCR
  - Google Workspace exports
- Summarize failed cases:
  - corrupt XLSX
  - parser timeout
  - source bytes over maxBytes
  - extracted text over maxBytes
- Include verification command results.
- Call out remaining risks:
  - parser fidelity for complex spreadsheets
  - visible-sheet metadata reliability
  - formula cached result availability
  - date conversion ambiguity
  - password-protected detection relying on parser signals
  - synchronous parser timeout limitations if using SheetJS
  - large workbooks increasing chunks, embedding cost, and indexing latency
  - no OCR, image, chart, macro, or legacy XLS support