Skip to main content

fraiseql_db/types/
sql_hints.rs

1//! Shared SQL types used across the compiler, schema, and database layers.
2//!
3//! These types are defined here (rather than in `compiler::aggregation` or
4//! `schema`) so that `db/` can import them without creating a dependency on
5//! the compilation or schema layers — a prerequisite for eventually extracting
6//! `db/` into its own crate (`fraiseql-db`).
7
8use fraiseql_error::{FraiseQLError, Result};
9use serde::{Deserialize, Serialize};
10
11use crate::{types::db_types::DatabaseType, utils::to_snake_case};
12
13/// Declared scalar type of a JSON field, used to pick the SQL cast.
14///
15/// A JSON/JSONB extraction is always `text`, so both ORDER BY and WHERE have to
16/// cast it before the database can compare it as anything else. Sorting `"9"`
17/// after `"10"` is the visible symptom for numbers; for WHERE the symptom is a
18/// wrong row set (or a hard cast error).
19///
20/// The variant → SQL-type-name mapping lives in exactly one place —
21/// [`SqlDialect::cast_type_name`] — so ORDER BY and WHERE cannot drift apart.
22///
23/// [`SqlDialect::cast_type_name`]: crate::dialect::SqlDialect::cast_type_name
24#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
25#[non_exhaustive]
26pub enum ScalarFieldType {
27    /// No cast — text comparison (correct for strings, UUIDs, IDs, enum values).
28    #[default]
29    Text,
30    /// Integer (`::bigint` / `CAST(… AS BIGINT)`).
31    Integer,
32    /// Fixed/floating point (`::numeric` / `CAST(… AS DECIMAL(38,12))`).
33    Numeric,
34    /// Boolean (`::boolean`).
35    Boolean,
36    /// Instant (`::timestamptz` / `CAST(… AS DATETIME2)`).
37    DateTime,
38    /// Calendar date (`::date`).
39    Date,
40    /// Wall-clock time (`::time`).
41    Time,
42    /// Identity comparison (`ID`/`UUID`) — **no cast**.
43    ///
44    /// Renders exactly like [`Self::Text`]; the variant exists to mark *which*
45    /// fields are identities, not to introduce a cast. `cast_type_name` returns
46    /// `None` for it deliberately.
47    ///
48    /// Casting would be the obvious repair and is the wrong one:
49    /// `(data->>'id')::uuid` is evaluated **per row**, so one row holding a
50    /// non-UUID identity raises SQLSTATE 22P02 for every query — and `ID` is
51    /// documented as intentionally spanning uuid / integer / text keys
52    /// (`docs/adr/0017-entity-identity-contract.md`), with fixtures holding
53    /// `'user-1'` and a BIGINT pk. Equality is instead made case-insensitive at
54    /// the *literal*, which needs no knowledge of the column's type.
55    Uuid,
56    /// A declared pgvector field — `vector(N)`, `halfvec(N)`, `sparsevec(N)` or
57    /// `bit(N)` (#1117).
58    ///
59    /// Like [`Self::Uuid`], this marks *which* fields are of a kind rather than
60    /// introducing a cast: `cast_type_name` returns `None` for it, so every
61    /// ordinary comparison against a vector field renders exactly as it did.
62    ///
63    /// What it decides is the **operand a distance predicate reads**. The
64    /// storage contract requires the backing view to expose a vector as a native
65    /// column, and `nearest` has always ordered by it; the threshold operators
66    /// could not tell a vector field from any other, so they resolved it through
67    /// `data->>` and re-parsed every row's embedding out of text — 2667 ms
68    /// against the column's 22 ms, on the same 400 rows.
69    ///
70    /// One variant covers all four pgvector types on purpose: which pgvector
71    /// type the *literal* is cast to is decided by the operand's own shape (an
72    /// array is `::vector`, `{1:0.5}/1000` is `::sparsevec`, the binary metrics
73    /// are `::varbit`), and that is unchanged by this. What the schema is being
74    /// asked here is only *"is there a native column to read instead of the
75    /// JSONB text?"*.
76    Vector,
77}
78
79/// ORDER BY clause with optional type and native column information.
80///
81/// The SQL generator uses `field_type` to emit the correct type cast for
82/// JSONB-extracted values, and `native_column` to bypass JSONB extraction
83/// entirely when the view exposes a dedicated typed column.
84///
85/// # Sort correctness by source
86///
87/// | Source | Text fields | Numeric fields | Date fields |
88/// |--------|------------|----------------|-------------|
89/// | JSONB (no cast) | Correct | **Wrong** (lexicographic) | Correct (ISO-8601) |
90/// | JSONB (with cast) | Correct | Correct | Correct |
91/// | Native column | Correct | Correct | Correct + indexable |
92#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
93#[non_exhaustive]
94pub struct OrderByClause {
95    /// Field to order by (GraphQL camelCase name).
96    pub field:           String,
97    /// Sort direction.
98    pub direction:       OrderDirection,
99    /// Field type for SQL cast generation. `Text` (default) means no cast.
100    #[serde(default)]
101    pub field_type:      ScalarFieldType,
102    /// Native column name if the view exposes this field as a typed column.
103    /// When set, ORDER BY uses this column directly instead of JSONB extraction,
104    /// enabling index support and correct typing without casts.
105    #[serde(default, skip_serializing_if = "Option::is_none")]
106    pub native_column:   Option<String>,
107    /// The PostgreSQL type of [`native_column`](Self::native_column) (`int8`, `uuid`, …),
108    /// when the view declares it: a keyset value compared with the column is cast to it
109    /// (#1521). `None` reads it as the field's scalar type.
110    #[serde(default, skip_serializing_if = "Option::is_none")]
111    pub native_type:     Option<String>,
112    /// Whether PostgreSQL proves [`native_column`](Self::native_column) `NOT NULL`, as
113    /// `compile --database` recorded it (#1533). Only such a key lets a relay page seek its
114    /// index with a row comparison: a NULL in a row comparison drops the row silently. Never
115    /// set from a client's `orderBy`.
116    #[serde(default, skip_serializing_if = "std::ops::Not::not")]
117    pub native_not_null: bool,
118    /// Vector-distance ordering (#386): when set, this clause orders by
119    /// `{column} {operator} '{query_vector}'::vector` — the pgvector ANN shape.
120    /// Requires [`native_column`](Self::native_column) (a JSONB-extracted text
121    /// value would defeat every vector index and re-parse per row).
122    #[serde(default, skip_serializing_if = "Option::is_none")]
123    pub vector:          Option<VectorDistanceOrder>,
124    /// Full-text relevance ordering (#1284): when set, this clause orders by
125    /// `ts_rank(to_tsvector(document), websearch_to_tsquery($n))`, where the
126    /// document is built from the searchable fields the same `?search=`
127    /// predicate matched on.
128    ///
129    /// [`field`](Self::field) is **not read** for such a clause — there is no
130    /// single field a relevance rank belongs to — and is empty by construction.
131    #[serde(default, skip_serializing_if = "Option::is_none")]
132    pub relevance:       Option<RelevanceOrder>,
133    /// This clause **is** the entity identity, whatever it is spelled as (#1303).
134    ///
135    /// Set only by [`identity`](Self::identity), which the runtime uses to lower
136    /// the compiled `pagination_order` into an ordering. Read only by the
137    /// tie-breaker, which appends nothing to an ordering that already carries one.
138    ///
139    /// A flag rather than a name comparison because the identity is a *schema*
140    /// fact: it may be `data->>'id'`, a native `id`, or `pk_user`, and this module
141    /// cannot tell the last from any other column. Without the flag the renderer
142    /// would append its own `data->>'id'` on top of a `pk_user` ordering — a
143    /// second, redundant sort key on every paged read of every Trinity view.
144    #[serde(default, skip_serializing_if = "std::ops::Not::not")]
145    pub identity:        bool,
146    /// The collation a text key sorts under (#1512): the request locale's ICU collation
147    /// (`fr-CA-x-icu`), rendered as `… COLLATE "fr-CA-x-icu"`. Set by the runtime, only for a
148    /// text key and only from a configured locale; `None` sorts under the column's own
149    /// collation, as before.
150    #[serde(default, skip_serializing_if = "Option::is_none")]
151    pub collation:       Option<String>,
152    /// A localized key's fallback chain (#1513): the clause sorts the label the chain reads
153    /// from the stored locale map, under [`collation`](Self::collation), rather than the
154    /// map. Set by the runtime from the request locale.
155    #[serde(default, skip_serializing_if = "Option::is_none")]
156    pub localized:       Option<Vec<String>>,
157}
158
159/// The full-text operand of an ORDER BY clause (#1284).
160///
161/// Carries the search text as a *value*, not as SQL: unlike
162/// [`VectorDistanceOrder`], whose literal is built from parsed numbers and is
163/// therefore safe to interpolate, this is arbitrary client input and is bound
164/// as a parameter by the renderer.
165///
166/// # Why the fields are storage keys
167///
168/// The rank has to be computed over the same expression the search predicate
169/// matched on, or the ordering describes a different document from the one that
170/// was searched. `build_fts_where_clause` emits a `WhereClause::Field`, whose
171/// path `WhereClause::from_graphql_json` lowers to the snake_case storage key
172/// and the generator renders as `data->>'key'` — so these are storage keys and
173/// the renderer extracts them the same way.
174#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
175pub struct RelevanceOrder {
176    /// The snake_case storage keys of the searchable fields the rank is computed
177    /// over, in the order the predicate ORs them. Never empty — a rank over no
178    /// document is refused at render time.
179    pub fields: Vec<String>,
180    /// The raw `websearch_to_tsquery` text, exactly as the client sent it.
181    pub query:  String,
182}
183
184/// The vector-distance operand of an ORDER BY clause (#386).
185///
186/// `query_vector` is carried as the *body* of a pgvector text literal
187/// (`[0.1,0.2,…]` for floats, `1011…` for bits) rather than a bind parameter:
188/// it is constructed exclusively by formatting values parsed from the request,
189/// so it can only contain digits, `.`, `-`, `e`, commas and brackets — and the
190/// SQL builder re-validates that character set before interpolating (defence in
191/// depth).
192#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
193pub struct VectorDistanceOrder {
194    /// pgvector distance operator: `<=>` (cosine), `<->` (L2), `<#>` (inner
195    /// product), `<~>` (hamming), `<%>` (jaccard).
196    pub operator:     String,
197    /// The query vector literal body, e.g. `[0.1,0.2,0.3]` or `1011`.
198    pub query_vector: String,
199    /// Which pgvector operand type the literal is cast to (#959).
200    #[serde(default)]
201    pub kind:         VectorOperandKind,
202}
203
204/// The operand type a vector-distance comparison is made in (#959).
205///
206/// The cast is load-bearing on the binary side: `'1011'::bit` is `bit(1)`, so a
207/// length-less cast silently keeps the first bit and reports the distance
208/// between two one-bit values. `varbit` carries whatever width the value has,
209/// which makes a width disagreement `different bit lengths 8 and 4` from the
210/// server rather than a plausible wrong answer.
211#[derive(Debug, Clone, Copy, Default, PartialEq, Eq, Serialize, Deserialize)]
212#[serde(rename_all = "snake_case")]
213#[non_exhaustive]
214pub enum VectorOperandKind {
215    /// Float vector: `'[…]'::vector`, ordered by `<=>` / `<->` / `<#>`.
216    #[default]
217    Float,
218    /// Binary vector: `'1011'::varbit`, ordered by `<~>` / `<%>`.
219    Bit,
220    /// Sparse float vector: `'{1:0.5,7:0.25}/1000'::sparsevec`, same operators
221    /// as [`Self::Float`] (#959).
222    ///
223    /// A sparse literal has no other reading: `'{1:1}/5'::vector` is
224    /// `Vector contents must start with "["`, so this kind is the difference
225    /// between a query and an error.
226    Sparse,
227}
228
229impl VectorOperandKind {
230    /// The pgvector type a literal of this kind is cast to.
231    ///
232    /// `varbit` and not `bit` for the binary kind — see the type's own note.
233    ///
234    /// There is deliberately **no half-precision kind**: a `halfvec` column
235    /// compared against `'[…]'::vector` resolves the literal to `halfvec` and
236    /// uses the same `halfvec_*_ops` index — verified on the rig, where both
237    /// spellings produce the identical plan. A kind that changes nothing the
238    /// server can see would be a distinction the code carries and the database
239    /// ignores.
240    #[must_use]
241    pub const fn cast(self) -> &'static str {
242        match self {
243            Self::Bit => "varbit",
244            Self::Sparse => "sparsevec",
245            _ => "vector",
246        }
247    }
248}
249
250/// Sort direction
251#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
252#[non_exhaustive]
253pub enum OrderDirection {
254    /// Ascending (A-Z, 0-9)
255    Asc,
256    /// Descending (Z-A, 9-0)
257    Desc,
258}
259
260impl OrderDirection {
261    /// Return the SQL keyword for this direction.
262    #[must_use]
263    pub const fn as_sql(self) -> &'static str {
264        match self {
265            Self::Asc => "ASC",
266            Self::Desc => "DESC",
267        }
268    }
269}
270
271impl OrderByClause {
272    /// Create a new `OrderByClause` with default field type (text) and no native column.
273    #[must_use]
274    pub fn new(field: String, direction: OrderDirection) -> Self {
275        Self {
276            field,
277            direction,
278            field_type: ScalarFieldType::default(),
279            native_column: None,
280            native_type: None,
281            native_not_null: false,
282            vector: None,
283            relevance: None,
284            identity: false,
285            collation: None,
286            localized: None,
287        }
288    }
289
290    /// A clause that orders by the entity identity, ascending (#1303).
291    ///
292    /// `native_column` when the relation exposes the identity as a column
293    /// (`id`, `pk_user`), `None` for the JSONB extraction `data->>'id'`. Which
294    /// one is a compile-time decision recorded in `QueryDefinition::pagination_order`;
295    /// this is its lowering.
296    ///
297    /// **ASC unconditionally.** A total order is all this has to be, and which of
298    /// two otherwise-tied rows comes first is not a property any client asked
299    /// about. Matching a requested clause's direction would suggest it is.
300    #[must_use]
301    pub const fn identity(field: String, native_column: Option<String>) -> Self {
302        Self {
303            field,
304            direction: OrderDirection::Asc,
305            field_type: ScalarFieldType::Text,
306            native_column,
307            native_type: None,
308            native_not_null: false,
309            vector: None,
310            relevance: None,
311            identity: true,
312            collation: None,
313            localized: None,
314        }
315    }
316
317    /// A clause that orders by full-text relevance, most relevant first (#1284).
318    ///
319    /// [`field`](Self::field) is left empty deliberately. A relevance rank is
320    /// computed over several fields at once, so naming one of them would be a
321    /// lie, and naming a sentinel like `_relevance` would put the sort kind back
322    /// into a string — which is the defect this constructor exists to replace:
323    /// `[{"_relevance": "desc"}]` type-checked at every layer that touched it
324    /// and failed only in the ORDER BY parser, three layers below the handler
325    /// that wrote it.
326    #[must_use]
327    pub fn by_relevance(relevance: RelevanceOrder) -> Self {
328        Self {
329            field:           String::new(),
330            direction:       OrderDirection::Desc,
331            field_type:      ScalarFieldType::default(),
332            native_column:   None,
333            native_type:     None,
334            native_not_null: false,
335            vector:          None,
336            relevance:       Some(relevance),
337            identity:        false,
338            collation:       None,
339            localized:       None,
340        }
341    }
342
343    /// Whether rendering this clause binds a SQL parameter.
344    ///
345    /// True only for full-text relevance (#1284), whose operand is arbitrary
346    /// client text and is therefore bound rather than interpolated. A SQL
347    /// builder that assembles `ORDER BY` as a bare string — the relay keyset
348    /// query, the fraiseql-wire adapter — asks this before rendering, so that
349    /// "this ordering cannot be expressed here" is a named refusal instead of a
350    /// dropped sort or an escaped literal.
351    #[must_use]
352    pub const fn binds_parameter(&self) -> bool {
353        self.relevance.is_some()
354    }
355
356    /// Convert the GraphQL camelCase field name to the JSONB snake_case storage key.
357    ///
358    /// # Examples
359    ///
360    /// ```
361    /// use fraiseql_db::OrderByClause;
362    /// use fraiseql_db::OrderDirection;
363    ///
364    /// let clause = OrderByClause::new("createdAt".to_string(), OrderDirection::Asc);
365    /// assert_eq!(clause.storage_key(), "created_at");
366    /// ```
367    #[must_use]
368    pub fn storage_key(&self) -> String {
369        to_snake_case(&self.field)
370    }
371
372    /// Validate that a field name matches the GraphQL identifier pattern `[_A-Za-z][_0-9A-Za-z]*`.
373    ///
374    /// This is a security boundary: field names are interpolated into SQL `data->>'field'`
375    /// expressions. Any character outside the GraphQL identifier set must be rejected before
376    /// the `OrderByClause` is constructed.
377    ///
378    /// # Errors
379    ///
380    /// Returns `FraiseQLError::Validation` if the field contains invalid characters.
381    pub fn validate_field_name(field: &str) -> Result<()> {
382        crate::utils::validate_graphql_identifier(field, "orderBy")
383    }
384
385    /// Parse `orderBy` from a GraphQL variables JSON value.
386    ///
387    /// Accepts two formats:
388    /// - Object: `{ "name": "DESC", "created_at": "ASC" }`
389    /// - Array:  `[{ "field": "name", "direction": "DESC" }]`
390    ///
391    /// Direction strings are case-insensitive.
392    ///
393    /// # Errors
394    ///
395    /// Returns `FraiseQLError::Validation` for invalid structure or direction values.
396    pub fn from_graphql_json(value: &serde_json::Value) -> Result<Vec<Self>> {
397        if let Some(obj) = value.as_object() {
398            // Object format: { "name": "DESC", "created_at": "ASC" }
399            obj.iter()
400                .map(|(field, dir_val)| {
401                    let dir_str = dir_val.as_str().ok_or_else(|| FraiseQLError::Validation {
402                        message: format!("orderBy direction for '{field}' must be a string"),
403                        path:    None,
404                    })?;
405                    let direction = match dir_str.to_ascii_uppercase().as_str() {
406                        "ASC" => OrderDirection::Asc,
407                        "DESC" => OrderDirection::Desc,
408                        _ => {
409                            return Err(FraiseQLError::Validation {
410                                message: format!(
411                                    "orderBy direction '{dir_str}' must be ASC or DESC"
412                                ),
413                                path:    None,
414                            });
415                        },
416                    };
417                    Self::validate_field_name(field)?;
418                    Ok(Self::new(field.clone(), direction))
419                })
420                .collect()
421        } else if let Some(arr) = value.as_array() {
422            // Array format: [{ "field": "name", "direction": "DESC" }]
423            arr.iter()
424                .map(|item| {
425                    let obj = item.as_object().ok_or_else(|| FraiseQLError::Validation {
426                        message: "orderBy array items must be objects".to_string(),
427                        path:    None,
428                    })?;
429                    let field = obj
430                        .get("field")
431                        .and_then(|v| v.as_str())
432                        .ok_or_else(|| FraiseQLError::Validation {
433                            message: "orderBy item missing 'field' string".to_string(),
434                            path:    None,
435                        })?
436                        .to_string();
437                    let dir_str = obj.get("direction").and_then(|v| v.as_str()).unwrap_or("ASC");
438                    let direction = match dir_str.to_ascii_uppercase().as_str() {
439                        "ASC" => OrderDirection::Asc,
440                        "DESC" => OrderDirection::Desc,
441                        _ => {
442                            return Err(FraiseQLError::Validation {
443                                message: format!(
444                                    "orderBy direction '{dir_str}' must be ASC or DESC"
445                                ),
446                                path:    None,
447                            });
448                        },
449                    };
450                    Self::validate_field_name(&field)?;
451                    Ok(Self::new(field, direction))
452                })
453                .collect()
454        } else {
455            Err(FraiseQLError::Validation {
456                message: "orderBy must be an object or array".to_string(),
457                path:    None,
458            })
459        }
460    }
461}
462
463/// SQL projection hint for database-specific field projection optimization.
464///
465/// When a type has a large JSONB payload, the compiler can generate
466/// SQL that projects only the requested fields, reducing network payload
467/// and JSON deserialization overhead.
468#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
469#[non_exhaustive]
470pub struct SqlProjectionHint {
471    /// Database type — typed to prevent silent typos (e.g. `"postgresq"`) that
472    /// would cause adapters to silently ignore the hint.
473    pub database: DatabaseType,
474
475    /// The projection SQL template.
476    /// Example for PostgreSQL:
477    /// `jsonb_build_object('id', data->>'id', 'email', data->>'email')`
478    pub projection_template: String,
479
480    /// Estimated reduction in payload size (percentage 0-100).
481    pub estimated_reduction_percent: u32,
482
483    /// The template reads nothing but the row's JSONB document (`data`), so the read may be
484    /// paged before it is projected and each returned row's document detoasted once (#1554).
485    ///
486    /// `false`, the default, when the template also reads a native column or a computed
487    /// expression (a vector distance): the paged subquery exposes only the document, so such
488    /// a read keeps the single-`SELECT` shape. Set by the projection generator, which is the
489    /// only thing that knows what it emitted; never inferred from the SQL text.
490    #[serde(default)]
491    pub reads_only_document: bool,
492}
493
494impl SqlProjectionHint {
495    /// Creates a new `SqlProjectionHint`.
496    #[must_use]
497    pub const fn new(
498        database: DatabaseType,
499        projection_template: String,
500        estimated_reduction_percent: u32,
501    ) -> Self {
502        Self {
503            database,
504            projection_template,
505            estimated_reduction_percent,
506            reads_only_document: false,
507        }
508    }
509
510    /// The same hint, declaring that its template reads only the row's JSONB document
511    /// ([`reads_only_document`](Self::reads_only_document)).
512    #[must_use]
513    pub const fn reading_only_the_document(mut self) -> Self {
514        self.reads_only_document = true;
515        self
516    }
517}
518
519#[cfg(test)]
520mod tests;