feat_fts_rank_ordering: Rank-ordered FTS results (`ORDER BY ts_rank DESC`)

[SoundMindsAI]

Why

feat_data_table_primitive shipped filter-only FTS — ?q=foo matches rows where search_vector @@ plainto_tsquery('english', 'foo') is true but orders results by created_at DESC, id DESC (the default cursor key). For long query strings or short result sets the ordering is fine. For ambiguous queries it isn't — a user searching clusters?q=prod against 50 clusters whose names share the token "prod" gets the newest matches first, not the most relevant.

True rank-ordered FTS would ORDER BY ts_rank(search_vector, plainto_tsquery('english', :q)) DESC, created_at DESC, id DESC — but the existing keyset cursor only encodes (created_at, id). A cursor predicate that pages forward on (ts_rank, created_at, id) either needs the cursor to encode the float ts_rank (brittle across pages because floats don't have a stable lexicographic key) or needs offset/limit pagination (banned by api-conventions.md §"Anti-patterns").

Status

• Stage: IDEA
• Priority: Backlog — explicitly held for MVP2 (folder name suffix). Re-evaluate when MVP2 work begins; the tsvector + GIN indexes are already on disk so the actual implementation is small.

Definition of done

Definition of done lives in the linked artifact(s) below — open it before starting.

Artifacts

• Idea: docs/00_overview/planned_features/02_mvp2/feat_fts_rank_ordering/idea.md

How to execute

1. Preflight the idea against the current codebase (catches stale file/line references, obsolete deferral rationale, drifted dependencies):

   /idea-preflight docs/00_overview/planned_features/02_mvp2/feat_fts_rank_ordering/idea.md
   

   The skill applies patches in-place; review with git diff after it runs.

2. Run the full pipeline autonomously (spec → plan → implement → PR):

   /pipeline docs/00_overview/planned_features/02_mvp2/feat_fts_rank_ordering --auto
   

   --auto skips inter-stage approval pauses but keeps all hard quality gates (cross-model review via GPT-5.5 in spec-gen + impl-plan-gen, per-story verification gates, test coverage audit, CI watch, Gemini adjudication, final cross-model review). The PR is opened but NOT merged — you merge it manually after review.

3. If you don't want full autonomy, drop the --auto flag — the pipeline will pause for approval between each stage (spec, plan, implement) so you can review and request changes.

Notes

This issue is part of the MVP2 backlog issue-coverage sweep (2026-06-02) — every active MVP2 folder should have a tracking issue so external contributors can discover the work without grep-ing the planned-features tree. If you pick this up, drop a comment so others don't duplicate; if you find the linked idea/spec stale, run /idea-preflight first to refresh it.