# Read-only paginated records: correctness and hardening patterns

Use this reference for read-only dashboards that combine SQLite/FTS, cursor pagination, document viewers, browser history, and local-file streaming.

## FTS: one public result per document

An FTS virtual table usually has one row per chunk. Joining it directly to documents and applying `LIMIT` paginates chunks, not documents. Correct shape:

1. Build `raw_hits` in SQL with document metadata, chunk number, `bm25`, and snippet.
2. Reduce before `LIMIT` to one best row per document: minimum rank, then minimum chunk number as deterministic tie-breaker.
3. Apply keyset continuation and `ORDER BY search_rank ASC, document_id DESC` to the reduced document rows.
4. Reuse this query for the document list and global search; do not maintain a second chunk-level search path.
5. Regression-test complete traversal, not just page 1/2: 150+ documents, several 3+ chunk documents, the same term in multiple chunks, no duplicate IDs, no omissions.

For textual sorts, the cursor predicate and `ORDER BY` must use the same collation. If ordering uses `COLLATE NOCASE`, both primary comparisons and equality in the tie-break clause must use `COLLATE NOCASE` too.

## Signed keyset cursors

Bind the opaque cursor to all query-shaping inputs: search mode, query, filters, sort and direction. Cursor payload should carry the primary keyset value and stable ID, then be authenticated with HMAC or a server-side equivalent. Reject malformed, tampered, or cross-query cursors with controlled 400 responses. Avoid putting search text, paths, or internal IDs in cleartext cursor output.

## Chronological comparison and bounded summaries

For time-series comparisons, group by canonical parameter plus unit, sort chronologically, and map each item to its immediate predecessor. Never use “first earlier row” unless ordering is explicit and proven.

Apply report ranges in SQL before source limits. For “latest per parameter,” first select the latest observation within each parameter/unit group, then sort those winners globally. Expose truncation and completeness per report section.

Do not derive a future plan from a generic recent-event page. Query planned events separately by effective planned date; query administered/missed/corrected events by event date.

## Paginated document viewer

Consume detail `next_cursor`. Preserve loaded sections and append subsequent pages. To open a full-text hit outside page 1, fetch successive bounded pages until the target section appears, then scroll it into view and focus a `tabindex=-1` section container. Render database values only with `textContent` or text nodes.

## Browser history and request races

- Initial restoration and `popstate` must not call `pushState`.
- User actions may push; restoration writes nothing. If a parent router already pushed, replace that same entry rather than adding another.
- Store filters and page cursor in history state.
- Label replacement pagination explicitly (for example, “Next page”); only say “load more” if rows are actually appended.
- Increment a monotonically increasing request generation for every navigation.
- Check the generation after session readiness, after fetch, after JSON parsing, and immediately before every live-DOM mutation.
- Prefer building detached DOM and committing it once after the final generation check.
- Add a browser test where an old request is delayed and a newer tab response wins.
- Add a repeated-back test that proves the user can leave the feature.

## Safe local-file streaming

Use one fail-closed probe as the source of truth for list, detail, HEAD, and GET. A nonempty database path is never availability. The public contract should expose bounded enums (for example, content `available | not_reviewed | no_extracted_content | unavailable` and original `available | not_reviewed | missing | blocked | unsupported`) while keeping paths and filenames server-side. Apply the review gate first: an unreviewed document remains visible as metadata but must report `not_reviewed` even if its file exists.

Open every path component relative to an allowlisted root using directory descriptors and `O_NOFOLLOW`. Open the final component with `O_NONBLOCK` as well, then use `fstat` to reject FIFO, device, directory, oversize, or other non-regular files without blocking. Determine MIME from magic bytes on the opened descriptor, rewind, and stream bounded blocks from that pinned descriptor. Close list/detail probes after deriving status. HEAD sends headers only, but HEAD is not a capability token: GET must reopen and rerun every root, symlink, type, size, and magic check so a file exchange between requests fails closed.

Validate explicitly configured roots at startup: absolute, normalized, existing directory, and not a symlink. If several roots are supported, preserve the strongest failure encountered (`blocked`/`unsupported`) rather than allowing a later `missing` result to erase it. Keep user-facing download names generic.

Automate distinct tests for final/intermediate symlinks, FIFO, oversize, unsupported magic, traversal/outside-root paths, missing files, an actual path exchange immediately before open, successful HEAD followed by file replacement and rejected GET, invalid session cookie, cross-site request, and client disconnect during a multi-block stream. Include an HTTP/browser group with a disposable database and real temporary PDF/image files; mocked JSON alone does not prove original delivery. In evidence, label only executed cases as `tested`; keep code-review conclusions under a separate `review_only` field.

## Release evidence caveat

A file inside a commit cannot contain that same commit's literal SHA without creating a self-reference. Use an explicit symbolic value such as `git:HEAD-after-release-commit` in committed evidence, then report and remotely verify the literal SHA after commit/push. Never claim the committed evidence contains a literal final SHA when it does not.