Fix TODO_BUGS audit findings: Postgres bugs, logic fixes, DRY/KISS cleanup

#33 in Riparion/riparion-cms — merged 2026-06-01

Works the TODO_BUGS.md checklist through to completion.

🔴 Critical (Postgres-only — latent, since the PG CI test only applies migrations and runs no query path)

• subscribers: decode confirmed as bool (BOOLEAN on PG / INTEGER on SQLite), not i64, and compare as a bool — unbreaks the subscribe/confirm flow on Postgres.
• analytics: cast viewed_at via to_char(...) so the views-over-time query decodes into DailyViews.day: String on PG (::date → NaiveDate errored).
• posts: title sort routed through a new dialect::ci_order (LOWER() on PG, COLLATE NOCASE on SQLite) — PG has no nocase collation.

🟡 Medium

• set_post_tags_db wraps delete + inserts in one transaction (no partial/empty tags).
• media "Copy URL" passes the URL via dioxus.recv instead of interpolating into JS source (quote/backslash/newline-safe).
• inline chart embed auto-scales to the data's real min/max with a zero-aware baseline — fixes blank/negative bars and squashed small-magnitude data.

🟢 Low

• search allows facet-only browsing when the query is empty (new browse_posts_db) instead of returning 0 results with active facets.
• og:url no longer double-slashes for site-relative page paths.
• documented the intentional fire-and-forget record_view / add_reaction calls.

DRY / KISS

• DRY-1: shared OgHead + absolute_url/absolute_image (new reader::head); PostHead/PageHead are thin wrappers.
• DRY-2: one escape_html/escape_attr/escape_xml (new crate::escape).
• DRY-3: dialect::published_at_on_insert/_on_update for the publish CASE.
• DRY-4: SQL sites interpolate STATUS_PUBLISHED/STATUS_DRAFT, not bare literals.
• DRY-5 (scoped): shared classify_slug_insert + MAX_SLUG_ATTEMPTS; kept separate slug derivation to preserve page (parent_id, slug) uniqueness semantics.
• DRY-6/7: shared facet_clause and a use_page_reset_on hook.
• KISS-1 (scoped): shared StatusSelect, optional_url, save_then_navigate; left the divergent featured-image fields alone (post = full media picker, page = bare input).
• KISS-2: would_cycle is now a single materialized-path prefix test, removing the only raw SQL outside db/.

Verification

• cargo fmt clean
• cargo clippy clean on server+sqlite, server+postgres, and wasm web (no new warnings vs. baseline)
• 26 sqlite tests pass

🤖 Generated with Claude Code