Skip to main content

rustango_cms/
search.rs

1//! Database full-text search backend.
2//!
3//! Admin + API search has been client-side `LIKE` substring matching —
4//! no relevance ranking, poor scaling. This adds a **Postgres
5//! full-text** path: `to_tsvector` / `ts_rank` with `websearch_to_tsquery`
6//! (which safely parses visitor query syntax — quotes, `OR`, `-term`).
7//!
8//! **Dialect-gated.** [`search_page_ids`] returns `Some(ranked_ids)`
9//! only when the pool is Postgres ([`rustango::sql::Pool::as_postgres`]);
10//! on SQLite / MySQL (or a PG query error) it returns `None`, and the
11//! caller falls back to the existing substring filter. So this is purely
12//! additive — non-PG deployments are unchanged.
13//!
14//! v1 indexes the canonical text (`title` + `seo_title` +
15//! `seo_description`) on the fly — no stored column required, correct
16//! without a migration. A `GIN` index + extension-body indexing (via
17//! the task queue) are follow-ups for scale.
18
19// ---- pluggable backend (Postgres FTS is the built-in; Elasticsearch
20//      is an opt-in external backend) -------------------------------
21
22use std::sync::{Arc, OnceLock};
23
24/// One page's searchable text, handed to an external [`SearchBackend`]
25/// to (re)index. `page_id` is the document id.
26#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
27pub struct SearchDoc {
28    pub page_id: i64,
29    pub title: String,
30    pub seo_title: String,
31    pub seo_description: String,
32    pub url_path: String,
33    /// The page's lifecycle status (`published` / `archived`) so a
34    /// backend can badge archived hits. Defaults to `published` for docs
35    /// indexed before this field existed.
36    #[serde(default = "default_published_status")]
37    pub status: String,
38}
39
40fn default_published_status() -> String {
41    crate::page::PageStatus::Published.as_str().to_owned()
42}
43
44/// An external full-text backend (Elasticsearch, …). When one is
45/// installed via [`set_backend`], it takes priority over the built-in
46/// Postgres path: search goes to the backend, and pages are indexed /
47/// removed through it. Object-safe so it can live behind `Arc<dyn …>`.
48#[async_trait::async_trait]
49pub trait SearchBackend: Send + Sync + 'static {
50    /// Page ids matching `query` for `tenant_slug`, ranked best-first.
51    async fn search(&self, tenant_slug: &str, query: &str, limit: i64) -> Vec<i64>;
52    /// Upsert one page document.
53    async fn index(&self, tenant_slug: &str, doc: &SearchDoc);
54    /// Remove a page document (on unpublish / delete).
55    async fn delete(&self, tenant_slug: &str, page_id: i64);
56}
57
58static BACKEND: OnceLock<Arc<dyn SearchBackend>> = OnceLock::new();
59
60/// Install an external search backend (e.g. Elasticsearch). Call once
61/// at startup. Without it, search uses the built-in Postgres FTS path
62/// ([`search_page_ids`]) / substring fallback.
63pub fn set_backend(backend: Arc<dyn SearchBackend>) {
64    // Set once; a second install would be silently lost otherwise (#697).
65    if BACKEND.set(backend).is_err() {
66        tracing::error!(target: "rustango_cms", "set_backend: a search backend is already installed; this one is ignored");
67    }
68}
69
70/// The installed external backend, if any.
71#[must_use]
72pub fn backend() -> Option<&'static Arc<dyn SearchBackend>> {
73    BACKEND.get()
74}
75
76/// The `to_tsvector` document expression: the page's canonical text,
77/// null-safe. Shared by the live query + the ranking test so they can't
78/// drift.
79#[cfg(feature = "postgres")]
80const DOC_SQL: &str =
81    "coalesce(title,'') || ' ' || coalesce(seo_title,'') || ' ' || coalesce(seo_description,'')";
82
83/// Page ids matching `query`, **ranked by relevance** (best first),
84/// capped at `limit`. `Some` only on Postgres; `None` on any other
85/// dialect or on a query error → the caller uses its substring fallback.
86///
87/// `websearch_to_tsquery` parses `query` as a user search string, so no
88/// escaping is needed and malformed input can't error the parse.
89pub async fn search_page_ids(
90    pool: &rustango::sql::Pool,
91    query: &str,
92    limit: i64,
93) -> Option<Vec<i64>> {
94    fts_page_ids(pool, query, limit, true).await
95}
96
97/// Ranked page ids for a public search: the installed external backend
98/// ([`set_backend`]) when there is one, else [`search_page_ids`]. `None`
99/// means "no ranking here" and the caller keeps its substring match.
100pub async fn ranked_public_page_ids(
101    pool: &rustango::sql::Pool,
102    tenant_slug: &str,
103    query: &str,
104    limit: i64,
105) -> Option<Vec<i64>> {
106    match backend() {
107        Some(b) => Some(b.search(tenant_slug, query, limit).await),
108        None => search_page_ids(pool, query, limit).await,
109    }
110}
111
112/// Like [`search_page_ids`] but spans **every status** (drafts /
113/// scheduled / expired too) — for the admin explorer's search, which
114/// must surface unpublished pages. Postgres-only (the public index is
115/// public-status-only); `None` elsewhere → admin keeps its substring path.
116pub async fn search_page_ids_all_statuses(
117    pool: &rustango::sql::Pool,
118    query: &str,
119    limit: i64,
120) -> Option<Vec<i64>> {
121    fts_page_ids(pool, query, limit, false).await
122}
123
124async fn fts_page_ids(
125    pool: &rustango::sql::Pool,
126    query: &str,
127    limit: i64,
128    public_only: bool,
129) -> Option<Vec<i64>> {
130    // Postgres-only path. `as_postgres()` itself only exists under the
131    // `postgres` feature, so the whole body is gated — sqlite / mysql
132    // builds compile to the `None` fallback (caller uses substring).
133    #[cfg(feature = "postgres")]
134    {
135        let pg = pool.as_postgres()?;
136        // #556 — public search returns published + archived (archived
137        // content stays findable); expired/draft/scheduled stay out.
138        let status_clause = if public_only {
139            "status IN ('published','archived') AND "
140        } else {
141            ""
142        };
143        let sql = format!(
144            "SELECT id FROM cms_page, websearch_to_tsquery('english', $1) AS q \
145             WHERE {status_clause}to_tsvector('english', {DOC_SQL}) @@ q \
146             ORDER BY ts_rank(to_tsvector('english', {DOC_SQL}), q) DESC, id ASC \
147             LIMIT $2"
148        );
149        match rustango::sql::sqlx::query_scalar::<_, i64>(&sql)
150            .bind(query)
151            .bind(limit)
152            .fetch_all(pg)
153            .await
154        {
155            Ok(ids) => Some(ids),
156            Err(e) => {
157                tracing::warn!(
158                    target: "rustango_cms::search",
159                    error = %e,
160                    "postgres FTS query failed; falling back to substring search",
161                );
162                None
163            }
164        }
165    }
166    #[cfg(not(feature = "postgres"))]
167    {
168        let _ = (pool, query, limit, public_only);
169        None
170    }
171}
172
173/// Fuzzy "did you mean?" suggestion: the published page title
174/// most trigram-similar to `query` above `threshold` (0.0–1.0), or
175/// `None`. Postgres + the `pg_trgm` extension only — `None` on any
176/// other dialect, when `pg_trgm` isn't installed, on a query error, or
177/// when the closest title is just the query itself.
178///
179/// Callers use it when a search returned no hits, to offer a
180/// spelling-correction prompt.
181pub async fn did_you_mean(
182    pool: &rustango::sql::Pool,
183    query: &str,
184    threshold: f32,
185) -> Option<String> {
186    #[cfg(feature = "postgres")]
187    {
188        let pg = pool.as_postgres()?;
189        // #556 — suggest from public pages (published + archived).
190        let sql = "SELECT title FROM cms_page \
191                   WHERE status IN ('published','archived') AND similarity(title, $1) > $2 \
192                   ORDER BY similarity(title, $1) DESC, title ASC \
193                   LIMIT 1";
194        let title: Option<String> = rustango::sql::sqlx::query_scalar(sql)
195            .bind(query)
196            .bind(threshold)
197            .fetch_optional(pg)
198            .await
199            .ok()
200            .flatten();
201        title.filter(|t| !t.eq_ignore_ascii_case(query.trim()))
202    }
203    #[cfg(not(feature = "postgres"))]
204    {
205        let _ = (pool, query, threshold);
206        None
207    }
208}
209
210/// Name of the expression GIN index backing [`search_page_ids`].
211#[cfg(feature = "postgres")]
212const FTS_INDEX: &str = "cms_page_fts_idx";
213
214/// Create the GIN index that makes [`search_page_ids`] scale — an
215/// expression index over the exact `to_tsvector(...)` the query uses,
216/// so the planner can use it instead of a seq scan. Idempotent
217/// (`IF NOT EXISTS`); Postgres-only (no-op + `Ok` on other dialects).
218/// [`crate::seed::ensure_seeded`] runs it for every tenant at boot.
219///
220/// # Errors
221/// The `CREATE INDEX` failing (e.g. insufficient privileges).
222pub async fn ensure_pg_fts_index(
223    pool: &rustango::sql::Pool,
224) -> Result<(), rustango::sql::sqlx::Error> {
225    #[cfg(feature = "postgres")]
226    {
227        let Some(pg) = pool.as_postgres() else {
228            return Ok(());
229        };
230        // Same expression as the query's `to_tsvector(...)`, so it matches.
231        let sql = format!(
232            "CREATE INDEX IF NOT EXISTS {FTS_INDEX} ON cms_page \
233             USING GIN (to_tsvector('english', {DOC_SQL}))"
234        );
235        rustango::sql::sqlx::query(&sql)
236            .execute(pg)
237            .await
238            .map(|_| ())
239    }
240    #[cfg(not(feature = "postgres"))]
241    {
242        let _ = pool;
243        Ok(())
244    }
245}
246
247#[cfg(all(test, feature = "postgres"))]
248mod tests {
249    use rustango::sql::sqlx;
250
251    /// A Postgres schema of its own for one test, holding a
252    /// throwaway `cms_page` with the columns the search queries read.
253    ///
254    /// These tests used to share `RCMS_TEST_TENANT_URL` — the variable
255    /// other docs point at a real demo database — and all write one
256    /// `cms_page` while running in parallel. They now take their own
257    /// `RCMS_TEST_PG_URL`, run only with `--ignored`, and never touch a
258    /// table outside the scratch schema.
259    struct Scratch {
260        pool: rustango::sql::Pool,
261        admin: sqlx::PgPool,
262        schema: String,
263    }
264
265    impl Scratch {
266        async fn new() -> Self {
267            use std::sync::atomic::{AtomicUsize, Ordering};
268            static N: AtomicUsize = AtomicUsize::new(0);
269            let url = std::env::var("RCMS_TEST_PG_URL")
270                .expect("set RCMS_TEST_PG_URL to a throwaway Postgres database to run these");
271            let schema = format!(
272                "rcms_test_{}_{}",
273                std::process::id(),
274                N.fetch_add(1, Ordering::Relaxed)
275            );
276            let admin = sqlx::PgPool::connect(&url).await.expect("connect");
277            sqlx::query(&format!("CREATE SCHEMA {schema}"))
278                .execute(&admin)
279                .await
280                .expect("create schema");
281            // `public` stays on the path for extensions such as pg_trgm.
282            let sep = if url.contains('?') { '&' } else { '?' };
283            let scoped = format!("{url}{sep}options=-c%20search_path%3D{schema},public");
284            let pool = rustango::sql::Pool::connect(&scoped).await.expect("connect scoped");
285            sqlx::query(
286                "CREATE TABLE cms_page (\
287                 id BIGINT PRIMARY KEY, title TEXT, seo_title TEXT, \
288                 seo_description TEXT, status TEXT)",
289            )
290            .execute(pool.as_postgres().expect("postgres pool"))
291            .await
292            .expect("create cms_page");
293            Self { pool, admin, schema }
294        }
295
296        fn pg(&self) -> &sqlx::PgPool {
297            self.pool.as_postgres().expect("postgres pool")
298        }
299
300        async fn drop(self) {
301            sqlx::query(&format!("DROP SCHEMA {} CASCADE", self.schema))
302                .execute(&self.admin)
303                .await
304                .expect("drop schema");
305        }
306    }
307
308    /// Real-Postgres ranking check. Runs with `--ignored` against
309    /// `RCMS_TEST_PG_URL`, in a [`Scratch`] schema.
310    ///
311    /// Verifies the live `search_page_ids` query end-to-end against a
312    /// throwaway `cms_page` table: a title-match outranks a
313    /// description-only match, and a non-match is excluded.
314    #[tokio::test]
315    #[ignore = "needs RCMS_TEST_PG_URL"]
316    async fn pg_fts_ranks_and_filters() {
317        let scratch = Scratch::new().await;
318        let (pool, pg) = (&scratch.pool, scratch.pg());
319
320        for (id, title, desc) in [
321            (9001_i64, "Rust web framework guide", ""),
322            (
323                9002,
324                "Cooking with cast iron",
325                "a rust-free skillet, mostly",
326            ),
327            (9003, "Knitting patterns", "wool and yarn"),
328        ] {
329            sqlx::query(
330                "INSERT INTO cms_page (id,title,seo_title,seo_description,status) \
331                 VALUES ($1,$2,'',$3,'published')",
332            )
333            .bind(id)
334            .bind(title)
335            .bind(desc)
336            .execute(pg)
337            .await
338            .expect("insert");
339        }
340
341        let ids = super::search_page_ids(pool, "rust", 10)
342            .await
343            .expect("Some on postgres");
344
345        // Both "rust" docs match; the title hit (9001) outranks the
346        // description-only hit (9002); the knitting page (9003) is out.
347        scratch.drop().await;
348        assert!(ids.contains(&9001), "title match present: {ids:?}");
349        assert!(ids.contains(&9002), "description match present: {ids:?}");
350        assert!(!ids.contains(&9003), "non-match excluded: {ids:?}");
351        let pos = |id| ids.iter().position(|x| *x == id);
352        assert!(pos(9001) < pos(9002), "title match ranks first: {ids:?}");
353    }
354
355    /// Real-Postgres `pg_trgm` "did you mean?" check. Same gating
356    /// as [`pg_fts_ranks_and_filters`]; also needs the `pg_trgm`
357    /// extension (created in the test DB).
358    #[tokio::test]
359    #[ignore = "needs RCMS_TEST_PG_URL"]
360    async fn pg_did_you_mean_suggests_near_title() {
361        let scratch = Scratch::new().await;
362        let (pool, pg) = (&scratch.pool, scratch.pg());
363        sqlx::query("CREATE EXTENSION IF NOT EXISTS pg_trgm")
364            .execute(pg)
365            .await
366            .ok();
367        sqlx::query(
368            "INSERT INTO cms_page (id,title,seo_title,seo_description,status) \
369             VALUES (9101,'Accessibility checklist','','','published')",
370        )
371        .execute(pg)
372        .await
373        .expect("insert");
374
375        // Typo "accessibilty" → suggest the real title; nonsense → None.
376        let hit = super::did_you_mean(pool, "accessibilty checklist", 0.3).await;
377        let none = super::did_you_mean(pool, "zxqw nonsense", 0.3).await;
378
379        scratch.drop().await;
380        assert_eq!(
381            hit.as_deref(),
382            Some("Accessibility checklist"),
383            "got {hit:?}"
384        );
385        assert_eq!(none, None, "no near match → no suggestion");
386    }
387
388    /// Real-Postgres GIN index check: the expression index is
389    /// created, idempotently.
390    #[tokio::test]
391    #[ignore = "needs RCMS_TEST_PG_URL"]
392    async fn pg_fts_index_created_idempotently() {
393        let scratch = Scratch::new().await;
394        let (pool, pg) = (&scratch.pool, scratch.pg());
395
396        super::ensure_pg_fts_index(pool)
397            .await
398            .expect("create index");
399        // Second call is a no-op (IF NOT EXISTS) — must not error.
400        super::ensure_pg_fts_index(pool).await.expect("idempotent");
401
402        let name: Option<String> = sqlx::query_scalar(
403            "SELECT indexname FROM pg_indexes WHERE schemaname = $1 AND indexname = $2",
404        )
405        .bind(&scratch.schema)
406        .bind(super::FTS_INDEX)
407        .fetch_optional(pg)
408        .await
409        .expect("query pg_indexes");
410        scratch.drop().await;
411        assert_eq!(name.as_deref(), Some(super::FTS_INDEX));
412    }
413
414    /// Real-Postgres: the all-status variant surfaces drafts that the
415    /// published-only public query excludes (admin search).
416    #[tokio::test]
417    #[ignore = "needs RCMS_TEST_PG_URL"]
418    async fn pg_all_statuses_includes_drafts() {
419        let scratch = Scratch::new().await;
420        let (pool, pg) = (&scratch.pool, scratch.pg());
421        sqlx::query(
422            "INSERT INTO cms_page (id,title,seo_title,seo_description,status) VALUES \
423             (9201,'Draft rust note','','','draft'),\
424             (9202,'Published rust post','','','published')",
425        )
426        .execute(pg)
427        .await
428        .expect("insert");
429
430        let published = super::search_page_ids(pool, "rust", 10).await.unwrap();
431        let all = super::search_page_ids_all_statuses(pool, "rust", 10)
432            .await
433            .unwrap();
434
435        scratch.drop().await;
436        assert!(
437            published.contains(&9202),
438            "published query has the published post"
439        );
440        assert!(
441            !published.contains(&9201),
442            "published query excludes the draft"
443        );
444        assert!(
445            all.contains(&9201) && all.contains(&9202),
446            "all-status has both: {all:?}"
447        );
448    }
449}