MAINT-172: pagination/queries — space-filter SQL fragment inlined across 13+ files

[jfolcini]

Problem: The fragment

(?N IS NULL OR COALESCE(b.page_id, b.id) IN
    (SELECT bp.block_id FROM block_properties bp
     WHERE bp.key = 'space' AND bp.value_ref = ?N))

is duplicated across pagination/{hierarchy,tags,links,undated,agenda,trash,properties}.rs, backlink/{query,grouped}.rs, fts/search.rs, tag_query/query.rs, and commands/{pages,agenda}.rs. The space_filter_clause! macro is referenced in inline comments but unusable because sqlx::query_as! requires a string literal and rejects concat!(). Comments at the call sites instruct future maintainers to "mirror any change" — convention enforcement, not single-source-of-truth.

Why it matters: Real maintenance hotspot. Any change to the filter semantics requires N coordinated edits. A subtle bug (one site forgets the COALESCE) would only be caught by per-site tests.

Fix (design space):

1. build.rs text substitution — generate per-query SQL strings into OUT_DIR from a single canonical fragment, keep query_as! consuming the generated literal.
2. prek hook — fail commit if the canonical fragment text drifts across the 13 sites. Cheap; does not consolidate the source.
3. Migrate the queries off query_as! to runtime sqlx — loses compile-time validation; not preferred.

Cost: M — design + implementation + verifying the 13 sites still produce identical query plans.
Risk: Medium — touching every list query is high blast-radius; needs careful test coverage.
Impact: Medium — eliminates a recurring drift hotspot; closes a long-tail correctness foot-gun.
Decision: Defer until the cost of drift becomes visible (a real bug shipped because one site got out of sync). Until then, the comment-based "mirror any change" convention is acceptable.
Status: Deferred.

---

Migrated from pending/REVIEW-LATER.md (MAINT-172).

[jfolcini]

Decision (maintainer, 2026-05-29): Stay deferred on the build.rs consolidation, but adopt the cheap prek drift-guard hook now — it catches the exact inlined-fragment foot-gun at low cost without the high-blast-radius consolidation. That hook is the actionable slice; the full consolidation waits for a real drift bug.

Recorded from a backlog actionability review.

[jfolcini]

Upstream status check (2026-05-29): sqlx#3388 — the dependency the build.rs/include_str! consolidation is gated on — is still open with zero activity (0 comments, untouched since opened 2024-07-27). The one PR that referenced it (transact-rs/sqlx#4115, a queryx macro attempt) is a closed draft, never merged. No roadmap, no ETA.

Note: the sqlx repo was transferred launchbadge/sqlx → transact-rs/sqlx (org rename, not a fork — same sqlx crate on crates.io). We're now on sqlx 0.9.0 (the space_filter_canonical.rs doc still cites 0.8.6); the LitStr first-arg constraint that blocks concat!()/include_str!() composition persists unchanged in 0.9.0.

Decision stands: remain deferred on the consolidation; the space_filter_canonical.rs parity test is the active drift-guard. Re-evaluate only if sqlx#3388 lands. (Subscribed to the upstream issue for notifications.)

[jfolcini]

Review note (2026-06-10): when consolidating the space-filter fragment, also delete the stale pre-#533 prose — these comments still show the old block_properties(key='space') form while all production SQL is b.space_id: pagination/mod.rs:81-107 (the 'canonical' doc block), fts/search.rs:876-884, tag_query/query.rs:14-19+191-194, backlink/query.rs:48-53 (+ the '// value_ref' bind comment at :286), backlink/grouped.rs:438-446. Verified: SPACE_FILTER_CANONICAL polices the SQL shape but nothing polices the comments — consolidation is the natural moment to fix both.