laterite_admin/
picker.rs

1//! The reference-picker seam: a generic field picks a value from a data source
2//! (another table, a hierarchy) without the framework knowing the source.
3//!
4//! A [`PickerSource`] is a domain service the picker field and its QUERY
5//! endpoints call: it owns no route and returns [`PickerNode`]s, never HTML.
6//! Sources are contributed to a [`PickerRegistry`] and selected by a field's
7//! `{"source": "vendor.name"}` option. [`TableSource`] is a built-in source over
8//! a single table, so a simple reference needs no code; a hierarchy or a computed
9//! label implements the trait directly.
10//!
11//! Search and resolve are exposed as two routes under `{admin}/pickers/{source}`,
12//! served with the QUERY method (reads carry no CSRF token; the session cookie
13//! and, optionally, a per-source permission gate them).
14
15use std::collections::HashMap;
16use std::sync::Arc;
17
18use axum::body::Bytes;
19use axum::extract::{Path, State};
20use axum::http::{header, Method, StatusCode};
21use axum::response::{IntoResponse, Response};
22use axum::{Extension, Json};
23use laterite_auth::AuthenticatedUser;
24use laterite_core::query::{bind_values, build as to_sql, text_cast};
25use laterite_core::strata::async_trait;
26use laterite_core::{AnyRowExt, Db};
27use sea_query::{Alias, Expr, Func, LikeExpr, Query, SelectStatement};
28use serde::{Deserialize, Serialize};
29
30use crate::sql::{like_escape, valid_ident};
31use crate::AdminState;
32
33/// One candidate or resolved node. `id` is a string: the generic form layer
34/// treats stored values as text, and a JSON number would corrupt an integer id
35/// past 2^53 in the browser, so a picker id is never serialised as a number.
36#[derive(Debug, Clone, Serialize)]
37pub struct PickerNode {
38    pub id: String,
39    pub label: String,
40    #[serde(skip_serializing_if = "Option::is_none")]
41    pub hint: Option<String>,
42}
43
44/// A picker source failed to search or resolve.
45#[derive(Debug, thiserror::Error)]
46#[error("picker source error: {0}")]
47pub struct PickerError(pub String);
48
49/// A source of picker candidates: a domain service the picker field and its
50/// endpoints call. Implementors build SQL in a scoped block and drop the builder
51/// before awaiting (sea-query identifiers are not `Send`), as the store queries
52/// elsewhere do.
53#[async_trait]
54pub trait PickerSource: Send + Sync + 'static {
55    /// Candidates matching `q`, capped at `limit` (the caller clamps it).
56    async fn search(&self, db: &Db, q: &str, limit: u32) -> Result<Vec<PickerNode>, PickerError>;
57    /// The node a stored `id` refers to, or `None` if it no longer exists.
58    async fn resolve(&self, db: &Db, id: &str) -> Result<Option<PickerNode>, PickerError>;
59}
60
61/// A registered source: its dotted `vendor.name` key, the source, and an optional
62/// permission gating its endpoints (a source over sensitive data sets one).
63pub struct PickerSourceReg {
64    pub name: String,
65    pub source: Arc<dyn PickerSource>,
66    pub permission: Option<String>,
67}
68
69impl PickerSourceReg {
70    /// A source readable by any signed-in operator.
71    pub fn new(name: impl Into<String>, source: Arc<dyn PickerSource>) -> Self {
72        Self {
73            name: name.into(),
74            source,
75            permission: None,
76        }
77    }
78    /// Gates this source's endpoints on a permission.
79    pub fn require(mut self, permission: impl Into<String>) -> Self {
80        self.permission = Some(permission.into());
81        self
82    }
83}
84
85/// The picker-source registry, keyed by source name.
86pub type PickerRegistry = HashMap<String, PickerSourceReg>;
87
88/// A built-in [`PickerSource`] over a single table: candidates are rows whose
89/// `label_col` matches (case-insensitive substring), and a stored id resolves by
90/// `id_col`. `hint_col`, when set, supplies each node's disambiguating hint. A
91/// hierarchy or a computed label implements [`PickerSource`] directly instead.
92pub struct TableSource {
93    table: String,
94    id_col: String,
95    label_col: String,
96    hint_col: Option<String>,
97}
98
99impl TableSource {
100    pub fn new(
101        table: impl Into<String>,
102        id_col: impl Into<String>,
103        label_col: impl Into<String>,
104    ) -> Self {
105        Self {
106            table: table.into(),
107            id_col: id_col.into(),
108            label_col: label_col.into(),
109            hint_col: None,
110        }
111    }
112
113    /// Sets the column supplying each node's disambiguating hint.
114    pub fn with_hint(mut self, hint_col: impl Into<String>) -> Self {
115        self.hint_col = Some(hint_col.into());
116        self
117    }
118
119    /// Guards the descriptor's identifiers (defence in depth; sea-query quotes
120    /// them regardless).
121    fn idents_valid(&self) -> bool {
122        valid_ident(&self.table)
123            && valid_ident(&self.id_col)
124            && valid_ident(&self.label_col)
125            && self.hint_col.as_deref().is_none_or(valid_ident)
126    }
127
128    /// The `id`, `label`, and (when configured) `hint` columns cast to text, from
129    /// the table. The where clause and limit are added per query.
130    fn base_select(&self, cast: &str) -> SelectStatement {
131        let mut sel = Query::select();
132        sel.expr_as(
133            Expr::col(Alias::new(&self.id_col)).cast_as(Alias::new(cast)),
134            Alias::new("id"),
135        );
136        sel.expr_as(
137            Expr::col(Alias::new(&self.label_col)).cast_as(Alias::new(cast)),
138            Alias::new("label"),
139        );
140        if let Some(hint) = &self.hint_col {
141            sel.expr_as(
142                Expr::col(Alias::new(hint)).cast_as(Alias::new(cast)),
143                Alias::new("hint"),
144            );
145        }
146        sel.from(Alias::new(&self.table));
147        sel
148    }
149}
150
151#[async_trait]
152impl PickerSource for TableSource {
153    async fn search(&self, db: &Db, q: &str, limit: u32) -> Result<Vec<PickerNode>, PickerError> {
154        if !self.idents_valid() {
155            return Err(PickerError("invalid source columns".into()));
156        }
157        let has_hint = self.hint_col.is_some();
158        let (sql, values) = {
159            let cast = text_cast(db.backend);
160            let mut sel = self.base_select(cast);
161            // Escape the user's term so its %/_ match literally, with an explicit
162            // ESCAPE char (SQLite has no default) for cross-backend behaviour.
163            let pattern = format!("%{}%", like_escape(&q.to_lowercase()));
164            sel.and_where(
165                Expr::expr(Func::lower(Expr::col(Alias::new(&self.label_col))))
166                    .like(LikeExpr::new(pattern).escape('\\')),
167            );
168            sel.limit(limit as u64);
169            to_sql(db.backend, sel)
170        };
171        let rows = bind_values(sqlx::query(&sql), values)
172            .fetch_all(&db.pool)
173            .await
174            .map_err(|e| PickerError(e.to_string()))?;
175        Ok(rows
176            .iter()
177            .map(|r| PickerNode {
178                id: r.get_text_opt("id").ok().flatten().unwrap_or_default(),
179                label: r.get_text_opt("label").ok().flatten().unwrap_or_default(),
180                hint: has_hint
181                    .then(|| r.get_text_opt("hint").ok().flatten())
182                    .flatten(),
183            })
184            .collect())
185    }
186
187    async fn resolve(&self, db: &Db, id: &str) -> Result<Option<PickerNode>, PickerError> {
188        if !self.idents_valid() {
189            return Err(PickerError("invalid source columns".into()));
190        }
191        let has_hint = self.hint_col.is_some();
192        let (sql, values) = {
193            let cast = text_cast(db.backend);
194            let mut sel = self.base_select(cast);
195            sel.and_where(
196                Expr::col(Alias::new(&self.id_col))
197                    .cast_as(Alias::new(cast))
198                    .eq(id),
199            );
200            sel.limit(1);
201            to_sql(db.backend, sel)
202        };
203        let row = bind_values(sqlx::query(&sql), values)
204            .fetch_optional(&db.pool)
205            .await
206            .map_err(|e| PickerError(e.to_string()))?;
207        Ok(row.map(|r| PickerNode {
208            id: r.get_text_opt("id").ok().flatten().unwrap_or_default(),
209            label: r.get_text_opt("label").ok().flatten().unwrap_or_default(),
210            hint: has_hint
211                .then(|| r.get_text_opt("hint").ok().flatten())
212                .flatten(),
213        }))
214    }
215}
216
217// The QUERY endpoints. The wire contract (frozen here and consumed by the
218// picker widget): search takes `{"q","limit"?}` and returns `{"items":[...]}`;
219// resolve takes `{"id"}` and returns `{"item": node|null}`. Unknown source is
220// 404, a non-QUERY method is 405, and both responses are `no-store`.
221
222#[derive(Deserialize)]
223struct SearchReq {
224    #[serde(default)]
225    q: String,
226    #[serde(default)]
227    limit: Option<u32>,
228}
229
230#[derive(Deserialize)]
231struct ResolveReq {
232    #[serde(default)]
233    id: String,
234}
235
236#[derive(Serialize)]
237struct SearchResp {
238    items: Vec<PickerNode>,
239}
240
241#[derive(Serialize)]
242struct ResolveResp {
243    item: Option<PickerNode>,
244}
245
246const DEFAULT_LIMIT: u32 = 20;
247const MAX_LIMIT: u32 = 50;
248
249pub(crate) async fn search(
250    method: Method,
251    State(state): State<AdminState>,
252    Extension(user): Extension<AuthenticatedUser>,
253    Path(source): Path<String>,
254    body: Bytes,
255) -> Response {
256    let reg = match guard(&method, &state, &user, &source) {
257        Ok(reg) => reg,
258        Err(resp) => return *resp,
259    };
260    let req: SearchReq = match serde_json::from_slice(&body) {
261        Ok(req) => req,
262        Err(_) => return json_no_store(StatusCode::BAD_REQUEST, &err("invalid request body")),
263    };
264    let limit = req.limit.unwrap_or(DEFAULT_LIMIT).clamp(1, MAX_LIMIT);
265    match reg.source.search(&state.db, req.q.trim(), limit).await {
266        Ok(items) => json_no_store(StatusCode::OK, &SearchResp { items }),
267        Err(_) => json_no_store(StatusCode::INTERNAL_SERVER_ERROR, &err("search failed")),
268    }
269}
270
271pub(crate) async fn resolve(
272    method: Method,
273    State(state): State<AdminState>,
274    Extension(user): Extension<AuthenticatedUser>,
275    Path(source): Path<String>,
276    body: Bytes,
277) -> Response {
278    let reg = match guard(&method, &state, &user, &source) {
279        Ok(reg) => reg,
280        Err(resp) => return *resp,
281    };
282    let req: ResolveReq = match serde_json::from_slice(&body) {
283        Ok(req) => req,
284        Err(_) => return json_no_store(StatusCode::BAD_REQUEST, &err("invalid request body")),
285    };
286    match reg.source.resolve(&state.db, req.id.trim()).await {
287        Ok(item) => json_no_store(StatusCode::OK, &ResolveResp { item }),
288        Err(_) => json_no_store(StatusCode::INTERNAL_SERVER_ERROR, &err("resolve failed")),
289    }
290}
291
292/// Enforces QUERY, resolves the named source, and checks its permission. Returns
293/// the ready response on any failure so the handler just early-returns it.
294fn guard<'a>(
295    method: &Method,
296    state: &'a AdminState,
297    user: &AuthenticatedUser,
298    source: &str,
299) -> Result<&'a PickerSourceReg, Box<Response>> {
300    if method.as_str() != "QUERY" {
301        return Err(Box::new(json_no_store(
302            StatusCode::METHOD_NOT_ALLOWED,
303            &err("use the QUERY method"),
304        )));
305    }
306    let reg = state.pickers.get(source).ok_or_else(|| {
307        Box::new(json_no_store(
308            StatusCode::NOT_FOUND,
309            &err("unknown picker source"),
310        ))
311    })?;
312    if let Some(permission) = &reg.permission {
313        if !user.permissions.allows(permission) {
314            return Err(Box::new(json_no_store(
315                StatusCode::FORBIDDEN,
316                &err("forbidden"),
317            )));
318        }
319    }
320    Ok(reg)
321}
322
323fn json_no_store<T: Serialize>(status: StatusCode, body: &T) -> Response {
324    (status, [(header::CACHE_CONTROL, "no-store")], Json(body)).into_response()
325}
326
327fn err(message: &str) -> serde_json::Value {
328    serde_json::json!({ "error": message })
329}
330
331#[cfg(test)]
332mod tests {
333    use super::*;
334    use laterite_core::strata::{
335        async_trait as strata_async_trait, ColumnDef, CoreResult, Migration, MigrationSet, Schema,
336        Table,
337    };
338    use laterite_core::testing::{connect_test, TestGuard};
339
340    struct CreatePlaces;
341    #[strata_async_trait(?Send)]
342    impl Migration for CreatePlaces {
343        fn name(&self) -> &str {
344            "0001_create_places"
345        }
346        async fn up(&self, s: &mut Schema<'_>) -> CoreResult<()> {
347            s.exec(
348                Table::create()
349                    .table(Alias::new("places"))
350                    .if_not_exists()
351                    .col(
352                        ColumnDef::new(Alias::new("id"))
353                            .big_integer()
354                            .not_null()
355                            .auto_increment()
356                            .primary_key(),
357                    )
358                    .col(ColumnDef::new(Alias::new("name")).text().not_null())
359                    .col(ColumnDef::new(Alias::new("region")).text().not_null())
360                    .to_owned(),
361            )
362            .await
363        }
364    }
365
366    async fn test_db() -> (Db, TestGuard) {
367        let places = MigrationSet::new("test.places", vec![Box::new(CreatePlaces)]);
368        connect_test(&[places]).await
369    }
370
371    async fn insert(db: &Db, name: &str, region: &str) {
372        let (sql, values) = to_sql(
373            db.backend,
374            Query::insert()
375                .into_table(Alias::new("places"))
376                .columns([Alias::new("name"), Alias::new("region")])
377                .values_panic([name.to_string().into(), region.to_string().into()])
378                .to_owned(),
379        );
380        bind_values(sqlx::query(&sql), values)
381            .execute(&db.pool)
382            .await
383            .unwrap();
384    }
385
386    #[tokio::test]
387    async fn table_source_searches_case_insensitively_and_resolves_by_id() {
388        let (db, _guard) = test_db().await;
389        insert(&db, "Kigali", "Rwanda").await;
390        insert(&db, "Kampala", "Uganda").await;
391        let source = TableSource::new("places", "id", "name").with_hint("region");
392
393        // Case-insensitive substring: `kig` finds `Kigali`, not `Kampala`.
394        let hits = source.search(&db, "kig", 10).await.unwrap();
395        assert_eq!(hits.len(), 1);
396        assert_eq!(hits[0].label, "Kigali");
397        assert_eq!(hits[0].hint.as_deref(), Some("Rwanda"));
398
399        // The stored id round-trips to its node.
400        let node = source
401            .resolve(&db, &hits[0].id)
402            .await
403            .unwrap()
404            .expect("id resolves");
405        assert_eq!(node.label, "Kigali");
406
407        // A missing id resolves to None (a deleted referent).
408        assert!(source.resolve(&db, "99999").await.unwrap().is_none());
409
410        // LIKE wildcards in the search term match literally, not as wildcards.
411        insert(&db, "A_B", "Zone").await;
412        insert(&db, "AXB", "Zone").await;
413        // The `_` is literal: `a_b` matches `A_B` only, never `AXB`.
414        let underscore = source.search(&db, "a_b", 10).await.unwrap();
415        assert_eq!(underscore.len(), 1);
416        assert_eq!(underscore[0].label, "A_B");
417        // `%` is literal too: no row contains it, so it matches nothing (not all rows).
418        assert!(source.search(&db, "%", 10).await.unwrap().is_empty());
419    }
420}
laterite_admin/picker.rs

laterite_admin/
picker.rs
