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/// Like [`search_page_ids`] but spans **every status** (drafts /
98/// scheduled / expired too) — for the admin explorer's search, which
99/// must surface unpublished pages. Postgres-only (the public index is
100/// public-status-only); `None` elsewhere → admin keeps its substring path.
101pub async fn search_page_ids_all_statuses(
102    pool: &rustango::sql::Pool,
103    query: &str,
104    limit: i64,
105) -> Option<Vec<i64>> {
106    fts_page_ids(pool, query, limit, false).await
107}
108
109async fn fts_page_ids(
110    pool: &rustango::sql::Pool,
111    query: &str,
112    limit: i64,
113    public_only: bool,
114) -> Option<Vec<i64>> {
115    // Postgres-only path. `as_postgres()` itself only exists under the
116    // `postgres` feature, so the whole body is gated — sqlite / mysql
117    // builds compile to the `None` fallback (caller uses substring).
118    #[cfg(feature = "postgres")]
119    {
120        let pg = pool.as_postgres()?;
121        // #556 — public search returns published + archived (archived
122        // content stays findable); expired/draft/scheduled stay out.
123        let status_clause = if public_only {
124            "status IN ('published','archived') AND "
125        } else {
126            ""
127        };
128        let sql = format!(
129            "SELECT id FROM cms_page, websearch_to_tsquery('english', $1) AS q \
130             WHERE {status_clause}to_tsvector('english', {DOC_SQL}) @@ q \
131             ORDER BY ts_rank(to_tsvector('english', {DOC_SQL}), q) DESC, id ASC \
132             LIMIT $2"
133        );
134        match rustango::sql::sqlx::query_scalar::<_, i64>(&sql)
135            .bind(query)
136            .bind(limit)
137            .fetch_all(pg)
138            .await
139        {
140            Ok(ids) => Some(ids),
141            Err(e) => {
142                tracing::warn!(
143                    target: "rustango_cms::search",
144                    error = %e,
145                    "postgres FTS query failed; falling back to substring search",
146                );
147                None
148            }
149        }
150    }
151    #[cfg(not(feature = "postgres"))]
152    {
153        let _ = (pool, query, limit, public_only);
154        None
155    }
156}
157
158/// Fuzzy "did you mean?" suggestion: the published page title
159/// most trigram-similar to `query` above `threshold` (0.0–1.0), or
160/// `None`. Postgres + the `pg_trgm` extension only — `None` on any
161/// other dialect, when `pg_trgm` isn't installed, on a query error, or
162/// when the closest title is just the query itself.
163///
164/// Callers use it when a search returned no hits, to offer a
165/// spelling-correction prompt.
166pub async fn did_you_mean(
167    pool: &rustango::sql::Pool,
168    query: &str,
169    threshold: f32,
170) -> Option<String> {
171    #[cfg(feature = "postgres")]
172    {
173        let pg = pool.as_postgres()?;
174        // #556 — suggest from public pages (published + archived).
175        let sql = "SELECT title FROM cms_page \
176                   WHERE status IN ('published','archived') AND similarity(title, $1) > $2 \
177                   ORDER BY similarity(title, $1) DESC, title ASC \
178                   LIMIT 1";
179        let title: Option<String> = rustango::sql::sqlx::query_scalar(sql)
180            .bind(query)
181            .bind(threshold)
182            .fetch_optional(pg)
183            .await
184            .ok()
185            .flatten();
186        title.filter(|t| !t.eq_ignore_ascii_case(query.trim()))
187    }
188    #[cfg(not(feature = "postgres"))]
189    {
190        let _ = (pool, query, threshold);
191        None
192    }
193}
194
195/// Name of the expression GIN index backing [`search_page_ids`].
196#[cfg(feature = "postgres")]
197const FTS_INDEX: &str = "cms_page_fts_idx";
198
199/// Create the GIN index that makes [`search_page_ids`] scale — an
200/// expression index over the exact `to_tsvector(...)` the query uses,
201/// so the planner can use it instead of a seq scan. Idempotent
202/// (`IF NOT EXISTS`); Postgres-only (no-op + `Ok` on other dialects).
203/// [`crate::seed::ensure_seeded`] runs it for every tenant at boot.
204///
205/// # Errors
206/// The `CREATE INDEX` failing (e.g. insufficient privileges).
207pub async fn ensure_pg_fts_index(
208    pool: &rustango::sql::Pool,
209) -> Result<(), rustango::sql::sqlx::Error> {
210    #[cfg(feature = "postgres")]
211    {
212        let Some(pg) = pool.as_postgres() else {
213            return Ok(());
214        };
215        // Same expression as the query's `to_tsvector(...)`, so it matches.
216        let sql = format!(
217            "CREATE INDEX IF NOT EXISTS {FTS_INDEX} ON cms_page \
218             USING GIN (to_tsvector('english', {DOC_SQL}))"
219        );
220        rustango::sql::sqlx::query(&sql)
221            .execute(pg)
222            .await
223            .map(|_| ())
224    }
225    #[cfg(not(feature = "postgres"))]
226    {
227        let _ = pool;
228        Ok(())
229    }
230}
231
232#[cfg(all(test, feature = "postgres"))]
233mod tests {
234    use rustango::sql::sqlx;
235
236    /// A Postgres schema of its own for one test, holding a
237    /// throwaway `cms_page` with the columns the search queries read.
238    ///
239    /// These tests used to share `RCMS_TEST_TENANT_URL` — the variable
240    /// other docs point at a real demo database — and all write one
241    /// `cms_page` while running in parallel. They now take their own
242    /// `RCMS_TEST_PG_URL`, run only with `--ignored`, and never touch a
243    /// table outside the scratch schema.
244    struct Scratch {
245        pool: rustango::sql::Pool,
246        admin: sqlx::PgPool,
247        schema: String,
248    }
249
250    impl Scratch {
251        async fn new() -> Self {
252            use std::sync::atomic::{AtomicUsize, Ordering};
253            static N: AtomicUsize = AtomicUsize::new(0);
254            let url = std::env::var("RCMS_TEST_PG_URL")
255                .expect("set RCMS_TEST_PG_URL to a throwaway Postgres database to run these");
256            let schema = format!(
257                "rcms_test_{}_{}",
258                std::process::id(),
259                N.fetch_add(1, Ordering::Relaxed)
260            );
261            let admin = sqlx::PgPool::connect(&url).await.expect("connect");
262            sqlx::query(&format!("CREATE SCHEMA {schema}"))
263                .execute(&admin)
264                .await
265                .expect("create schema");
266            // `public` stays on the path for extensions such as pg_trgm.
267            let sep = if url.contains('?') { '&' } else { '?' };
268            let scoped = format!("{url}{sep}options=-c%20search_path%3D{schema},public");
269            let pool = rustango::sql::Pool::connect(&scoped).await.expect("connect scoped");
270            sqlx::query(
271                "CREATE TABLE cms_page (\
272                 id BIGINT PRIMARY KEY, title TEXT, seo_title TEXT, \
273                 seo_description TEXT, status TEXT)",
274            )
275            .execute(pool.as_postgres().expect("postgres pool"))
276            .await
277            .expect("create cms_page");
278            Self { pool, admin, schema }
279        }
280
281        fn pg(&self) -> &sqlx::PgPool {
282            self.pool.as_postgres().expect("postgres pool")
283        }
284
285        async fn drop(self) {
286            sqlx::query(&format!("DROP SCHEMA {} CASCADE", self.schema))
287                .execute(&self.admin)
288                .await
289                .expect("drop schema");
290        }
291    }
292
293    /// Real-Postgres ranking check. Runs with `--ignored` against
294    /// `RCMS_TEST_PG_URL`, in a [`Scratch`] schema.
295    ///
296    /// Verifies the live `search_page_ids` query end-to-end against a
297    /// throwaway `cms_page` table: a title-match outranks a
298    /// description-only match, and a non-match is excluded.
299    #[tokio::test]
300    #[ignore = "needs RCMS_TEST_PG_URL"]
301    async fn pg_fts_ranks_and_filters() {
302        let scratch = Scratch::new().await;
303        let (pool, pg) = (&scratch.pool, scratch.pg());
304
305        for (id, title, desc) in [
306            (9001_i64, "Rust web framework guide", ""),
307            (
308                9002,
309                "Cooking with cast iron",
310                "a rust-free skillet, mostly",
311            ),
312            (9003, "Knitting patterns", "wool and yarn"),
313        ] {
314            sqlx::query(
315                "INSERT INTO cms_page (id,title,seo_title,seo_description,status) \
316                 VALUES ($1,$2,'',$3,'published')",
317            )
318            .bind(id)
319            .bind(title)
320            .bind(desc)
321            .execute(pg)
322            .await
323            .expect("insert");
324        }
325
326        let ids = super::search_page_ids(pool, "rust", 10)
327            .await
328            .expect("Some on postgres");
329
330        // Both "rust" docs match; the title hit (9001) outranks the
331        // description-only hit (9002); the knitting page (9003) is out.
332        scratch.drop().await;
333        assert!(ids.contains(&9001), "title match present: {ids:?}");
334        assert!(ids.contains(&9002), "description match present: {ids:?}");
335        assert!(!ids.contains(&9003), "non-match excluded: {ids:?}");
336        let pos = |id| ids.iter().position(|x| *x == id);
337        assert!(pos(9001) < pos(9002), "title match ranks first: {ids:?}");
338    }
339
340    /// Real-Postgres `pg_trgm` "did you mean?" check. Same gating
341    /// as [`pg_fts_ranks_and_filters`]; also needs the `pg_trgm`
342    /// extension (created in the test DB).
343    #[tokio::test]
344    #[ignore = "needs RCMS_TEST_PG_URL"]
345    async fn pg_did_you_mean_suggests_near_title() {
346        let scratch = Scratch::new().await;
347        let (pool, pg) = (&scratch.pool, scratch.pg());
348        sqlx::query("CREATE EXTENSION IF NOT EXISTS pg_trgm")
349            .execute(pg)
350            .await
351            .ok();
352        sqlx::query(
353            "INSERT INTO cms_page (id,title,seo_title,seo_description,status) \
354             VALUES (9101,'Accessibility checklist','','','published')",
355        )
356        .execute(pg)
357        .await
358        .expect("insert");
359
360        // Typo "accessibilty" → suggest the real title; nonsense → None.
361        let hit = super::did_you_mean(pool, "accessibilty checklist", 0.3).await;
362        let none = super::did_you_mean(pool, "zxqw nonsense", 0.3).await;
363
364        scratch.drop().await;
365        assert_eq!(
366            hit.as_deref(),
367            Some("Accessibility checklist"),
368            "got {hit:?}"
369        );
370        assert_eq!(none, None, "no near match → no suggestion");
371    }
372
373    /// Real-Postgres GIN index check: the expression index is
374    /// created, idempotently.
375    #[tokio::test]
376    #[ignore = "needs RCMS_TEST_PG_URL"]
377    async fn pg_fts_index_created_idempotently() {
378        let scratch = Scratch::new().await;
379        let (pool, pg) = (&scratch.pool, scratch.pg());
380
381        super::ensure_pg_fts_index(pool)
382            .await
383            .expect("create index");
384        // Second call is a no-op (IF NOT EXISTS) — must not error.
385        super::ensure_pg_fts_index(pool).await.expect("idempotent");
386
387        let name: Option<String> = sqlx::query_scalar(
388            "SELECT indexname FROM pg_indexes WHERE schemaname = $1 AND indexname = $2",
389        )
390        .bind(&scratch.schema)
391        .bind(super::FTS_INDEX)
392        .fetch_optional(pg)
393        .await
394        .expect("query pg_indexes");
395        scratch.drop().await;
396        assert_eq!(name.as_deref(), Some(super::FTS_INDEX));
397    }
398
399    /// Real-Postgres: the all-status variant surfaces drafts that the
400    /// published-only public query excludes (admin search).
401    #[tokio::test]
402    #[ignore = "needs RCMS_TEST_PG_URL"]
403    async fn pg_all_statuses_includes_drafts() {
404        let scratch = Scratch::new().await;
405        let (pool, pg) = (&scratch.pool, scratch.pg());
406        sqlx::query(
407            "INSERT INTO cms_page (id,title,seo_title,seo_description,status) VALUES \
408             (9201,'Draft rust note','','','draft'),\
409             (9202,'Published rust post','','','published')",
410        )
411        .execute(pg)
412        .await
413        .expect("insert");
414
415        let published = super::search_page_ids(pool, "rust", 10).await.unwrap();
416        let all = super::search_page_ids_all_statuses(pool, "rust", 10)
417            .await
418            .unwrap();
419
420        scratch.drop().await;
421        assert!(
422            published.contains(&9202),
423            "published query has the published post"
424        );
425        assert!(
426            !published.contains(&9201),
427            "published query excludes the draft"
428        );
429        assert!(
430            all.contains(&9201) && all.contains(&9202),
431            "all-status has both: {all:?}"
432        );
433    }
434}