find_nearest_posts_with_embedding returns slice/PCA results in arbitrary order: reduced-path rerank has no ORDER BY and joins chunks on post_id only

Problem and intended result

In slice and PCA modes, hivesense_app.find_nearest_posts_with_embedding (the paging search, db/search.sql:347) returns posts in arbitrary order instead of nearest first. #63 (closed)'s new embed-slice check (.aidev/run-checks.sh check_paging) caught this on session/hivesense at 5137236. The query was post 900101's own first chunk, embedded with mxbai-embed-xsmall-v1, slice mode, 256 dims:

one-shot order: [900101,900102,900105,900103]
FAIL: find_nearest_posts_with_embedding(_limit => 2) returned [900103,900105], expected [900101,900102]
ok:   find_nearest_posts_with_embedding(_limit => 100) returned [900103,900105,900102,900101]
FAIL: find_nearest_posts_with_embedding(_limit => 100, _start_post_id => 900102) returned [900101], expected [900105,900103]

The post the query came from ranks last. The one-shot variant (what the endpoints call) and the full-vector path of the paging function rank correctly. A small limit therefore returns the wrong posts, and a continuation from _start_post_id returns the wrong remainder. This is the same class of defect as #56 (closed): the slice branch of the paging variant diverges from the full branch.

Root cause

Both reduced-vector branches of the main retrieval loop (db/search.sql, slice at about lines 471-491, PCA at about lines 494-516) rerank like this:

SELECT ann.post_id, ann.chunk_number, (pv.embedding <=> $1)::float4 AS similarity
  FROM ann
  JOIN hivesense_app.posts_vectors pv ON pv.post_id = ann.post_id
  • No ORDER BY. The ann CTE is ordered by ann_dist, but the outer join's row order is unspecified. The FOR rec IN EXECUTE sql loop emits posts in the order the rows arrive, keeping the first chunk it sees per post (seen_ids). So the emitted rank, the _limit cut and the _start_post_id continuation all follow arbitrary order.
  • The join ignores chunk_number. Each ANN row is joined to every chunk of its post, so similarity comes from whichever chunk arrives first rather than from the chunk the ANN step found. Meanwhile ann.chunk_number reports the ANN chunk, which doesn't match.

The full-vector path (ORDER BY similarity, hpv.post_id, about line 573) gets both right.

Required behavior

  1. In both reduced branches of find_nearest_posts_with_embedding, join the rerank on (post_id, chunk_number) and add ORDER BY similarity, ann.post_id to the outer select. Ranking by the full-embedding rerank distance is what the one-shot variant and the full path already do. Keep the parameter layout and the rest of the loop unchanged.
  2. Add check_paging to embed_pca in .aidev/run-checks.sh, as embed-slice and embed-search already have it. The PCA path has the same defect, and today nothing checks it.

Acceptance criteria

  • In slice mode, find_nearest_posts_with_embedding with _limit 2 returns the first two posts of the one-shot order, and the continuation from the second one returns the rest of that order, with no repeats or gaps.
  • The same holds in PCA mode, and embed-pca now runs those checks.
  • Every step of the full slot passes: stack-pins, install, events-id, install-lock, reinstall, embed-search, embed-slice, embed-pca and api-smoke. The stack must be .aidev/test-stack.compose.yml with the slot's arguments as .aidev/project.yaml declares them on this branch.
  • Full-mode results are unchanged.

Verification

The workflow's own run_full_tests runs the slot as approved on aidev/integration, which doesn't yet include #63 (closed)'s new steps. So the gate passing proves nothing about embed-slice or embed-pca. Run the full step list by hand, as .aidev/README.md's "To run the stack checks by hand" section shows, using the step list from .aidev/project.yaml's full slot. Record the junit result for every step in the workflow log. Before the fix, embed-slice fails exactly as shown above. After it, every step must pass.

Integration points

  • db/search.sql hivesense_app.find_nearest_posts_with_embedding — reduced-path rerank joined on (post_id, chunk_number) and ordered by similarity
  • .aidev/run-checks.sh embed_pca — runs check_paging

Scope boundary

No changes to find_nearest_posts_with_embedding_one_shot, the endpoints, or the paging logic's start/limit handling, which is correct once its input is ordered. Do not weaken check_paging.