Skip to main content

khive_query/compilers/
sql.rs

1//! Parameterized SQL compilation for fixed and variable-length query patterns.
2
3use crate::ast::*;
4use crate::error::QueryError;
5use crate::language::read_only_error;
6use crate::validate::{validate_with_warnings, MAX_DEPTH, SYNTHETIC_RELATIONS};
7
8#[path = "mailbox.rs"]
9mod mailbox;
10use mailbox::MailboxLowering;
11
12fn is_synthetic(rel: &str) -> bool {
13    SYNTHETIC_RELATIONS.contains(&rel)
14}
15
16fn synthetic_role(rel: &str) -> Option<&'static str> {
17    match rel {
18        "observed_as_candidate" => Some("candidate"),
19        "observed_as_selected" => Some("selected"),
20        "observed_as_target" => Some("target"),
21        "observed_as_signal" => Some("signal"),
22        _ => None,
23    }
24}
25
26/// Substrate-agnostic source for canonical edge endpoints (issue #467).
27fn primary_node_sql(mailbox: Option<&MailboxLowering>) -> String {
28    let note_filter = mailbox.map_or_else(String::new, |scope| scope.note_filter("notes"));
29    let edge_filter = mailbox.map_or_else(String::new, |scope| scope.edge_filter("graph_edges"));
30    format!(
31        "\
32    SELECT id, namespace, kind, entity_type, name, description, NULL AS content, \
33           NULL AS status, NULL AS salience, NULL AS decay_factor, properties, \
34           created_at, updated_at, deleted_at, 'entity' AS substrate_kind \
35      FROM entities \
36    UNION ALL \
37    SELECT id, namespace, kind, NULL AS entity_type, name, NULL AS description, \
38           content, status, salience, decay_factor, properties, \
39           created_at, updated_at, deleted_at, 'note' AS substrate_kind \
40      FROM notes{note_filter} \
41    UNION ALL \
42    SELECT id, namespace, kind, NULL AS entity_type, verb AS name, \
43           NULL AS description, NULL AS content, NULL AS status, \
44           NULL AS salience, NULL AS decay_factor, payload AS properties, \
45           created_at, created_at AS updated_at, NULL AS deleted_at, \
46           'event' AS substrate_kind \
47      FROM events \
48    UNION ALL \
49    SELECT id, namespace, relation AS kind, NULL AS entity_type, NULL AS name, \
50           NULL AS description, NULL AS content, NULL AS status, \
51           NULL AS salience, NULL AS decay_factor, metadata AS properties, \
52           created_at, updated_at, deleted_at, 'edge' AS substrate_kind \
53      FROM graph_edges{edge_filter}"
54    )
55}
56
57fn primary_node_source(alias: &str, mailbox: Option<&MailboxLowering>) -> String {
58    format!("({}) {alias}", primary_node_sql(mailbox))
59}
60
61/// Entity/note referent source for synthetic observation targets (issue #468).
62fn observation_target_sql(mailbox: Option<&MailboxLowering>) -> String {
63    let note_filter = mailbox.map_or_else(String::new, |scope| scope.note_filter("notes"));
64    format!(
65        "\
66    SELECT id, namespace, kind, entity_type, name, description, \
67           NULL AS content, NULL AS status, NULL AS salience, \
68           NULL AS decay_factor, properties, created_at, updated_at, \
69           deleted_at, 'entity' AS referent_kind \
70      FROM entities \
71    UNION ALL \
72    SELECT id, namespace, kind, NULL AS entity_type, name, NULL AS description, \
73           content, status, salience, decay_factor, properties, \
74           created_at, updated_at, deleted_at, 'note' AS referent_kind \
75      FROM notes{note_filter}"
76    )
77}
78
79fn observation_target_source(alias: &str, mailbox: Option<&MailboxLowering>) -> String {
80    format!("({}) {alias}", observation_target_sql(mailbox))
81}
82
83fn edge_source(alias: &str, mailbox: Option<&MailboxLowering>) -> String {
84    match mailbox {
85        Some(scope) => format!(
86            "(SELECT * FROM graph_edges{}) {alias}",
87            scope.edge_filter("graph_edges")
88        ),
89        None => format!("graph_edges {alias}"),
90    }
91}
92
93/// Quotes one SQL identifier while preserving the returned column name.
94fn quote_identifier(identifier: &str) -> String {
95    format!("\"{}\"", identifier.replace('"', "\"\""))
96}
97
98/// Parameterized read-only SQL plus the metadata required to decode its result.
99///
100/// See `crates/khive-query/docs/api/sql-compilation.md` for lowering rules.
101#[derive(Debug)]
102pub struct CompiledQuery {
103    /// SQL statement using positional `?N` placeholders.
104    pub sql: String,
105    /// Parameters in placeholder order.
106    pub params: Vec<QueryValue>,
107    /// Caller-requested projections in result-column order.
108    pub return_vars: Vec<ReturnItem>,
109    /// Non-fatal validation or compilation diagnostics.
110    pub warnings: Vec<String>,
111    /// Sentinel metadata when SQL fetches `max_limit + 1` to detect another page.
112    pub truncation_check: Option<TruncationCheck>,
113}
114
115/// Instructions for stripping a row-cap sentinel after execution.
116#[derive(Clone, Copy, Debug, PartialEq, Eq)]
117pub struct TruncationCheck {
118    /// Effective page size to enforce after detecting and removing the sentinel.
119    pub max_limit: usize,
120    /// Explicit caller limit, retained for diagnostics.
121    pub requested_limit: Option<usize>,
122}
123
124/// Caller-derived mailbox partition applied by [`compile_with_mailbox`].
125///
126/// The compiler does not authenticate this scope. The runtime must derive it
127/// from its authorized caller, never from query text or query arguments.
128#[derive(Debug, Clone, PartialEq, Eq)]
129pub struct QueryMailboxScope {
130    /// Exact caller actor label; SQL always binds it as a text parameter.
131    pub actor_id: String,
132    /// Whether the anonymous, non-delegated local caller may see legacy rows.
133    pub legacy_local: bool,
134}
135
136/// Runtime options injected by the caller to scope and cap query execution.
137pub struct CompileOptions {
138    /// Namespace scope; empty means all namespaces.
139    pub scopes: Vec<String>,
140    /// Effective server page size applied against any explicit query limit.
141    ///
142    /// The public handler clamps its requested `page_size` to the hard cap before
143    /// constructing these compiler options.
144    pub max_limit: usize,
145}
146
147impl Default for CompileOptions {
148    fn default() -> Self {
149        Self {
150            scopes: Vec::new(),
151            max_limit: 500,
152        }
153    }
154}
155
156/// Chooses the SQL limit and optional cap-sentinel check (issue #777).
157///
158/// An explicit query-text `LIMIT` is a TOTAL bound across every `SKIP` page, not a
159/// page-local one (ADR-008 §"query LIMIT and page_size composition", docs/guide/api-reference.md).
160/// `offset` is the page's `SKIP` value; the SQL bound is `min(limit - offset, max_limit)`,
161/// and the cap sentinel (`max_limit + 1`) is requested only when another page could still
162/// exist within the query's own limit. An offset at or beyond the limit yields SQL `LIMIT 0`
163/// — a terminal empty page, never an error.
164/// See `crates/khive-query/docs/api/sql-compilation.md` for lifecycle details.
165fn effective_limit(
166    requested_limit: Option<usize>,
167    offset: usize,
168    max_limit: usize,
169) -> (usize, Option<TruncationCheck>) {
170    match requested_limit {
171        Some(limit) => {
172            let remaining = limit.saturating_sub(offset);
173            if remaining <= max_limit {
174                (remaining, None)
175            } else {
176                (
177                    max_limit.saturating_add(1),
178                    Some(TruncationCheck {
179                        max_limit,
180                        requested_limit: Some(limit),
181                    }),
182                )
183            }
184        }
185        None => (
186            max_limit.saturating_add(1),
187            Some(TruncationCheck {
188                max_limit,
189                requested_limit: None,
190            }),
191        ),
192    }
193}
194
195/// Compiles `query` to parameterized, read-only SQL under `opts`.
196///
197/// The returned parameter vector is ordered to match the statement's `?N` placeholders.
198///
199/// # Errors
200///
201/// Returns [`QueryError`] when validation fails, a construct is unsupported,
202/// a projection cannot be lowered, or a bound limit/value is invalid.
203/// See `crates/khive-query/docs/api/sql-compilation.md` for compilation paths.
204pub fn compile(query: &GqlQuery, opts: &CompileOptions) -> Result<CompiledQuery, QueryError> {
205    compile_scoped(query, opts, None)
206}
207
208/// Compiles under a caller-derived message partition before matching and paging.
209///
210/// This is a pure lowering API, not an authorization or actor-resolution boundary.
211/// Namespace filtering and all other [`compile`] behavior remain unchanged.
212///
213/// # Errors
214///
215/// Returns the same validation and lowering errors as [`compile`].
216pub fn compile_with_mailbox(
217    query: &GqlQuery,
218    opts: &CompileOptions,
219    mailbox: &QueryMailboxScope,
220) -> Result<CompiledQuery, QueryError> {
221    compile_scoped(query, opts, Some(mailbox))
222}
223
224fn compile_scoped(
225    query: &GqlQuery,
226    opts: &CompileOptions,
227    mailbox: Option<&QueryMailboxScope>,
228) -> Result<CompiledQuery, QueryError> {
229    if query.pattern.elements.is_empty() {
230        return Err(QueryError::Compile("empty pattern".into()));
231    }
232
233    let mut query = query.clone();
234    let warnings = validate_with_warnings(&mut query)?;
235
236    let mut compiled = if query.pattern.has_variable_length() {
237        compile_variable_length(&query, opts, mailbox)?
238    } else {
239        compile_fixed_length(&query, opts, mailbox)?
240    };
241    compiled.warnings.extend(warnings);
242
243    // Fail closed if a future lowering path accidentally emits a mutation.
244    assert_select_only(&compiled.sql)?;
245
246    Ok(compiled)
247}
248
249/// Enforces the compiler's SELECT/WITH-only invariant; the reader is the security boundary.
250fn assert_select_only(sql: &str) -> Result<(), QueryError> {
251    let first = sql.split_whitespace().next().unwrap_or("").to_uppercase();
252    if first == "SELECT" || first == "WITH" {
253        return Ok(());
254    }
255    Err(read_only_error(QueryError::Compile))
256}
257
258fn in_placeholders(indices: impl IntoIterator<Item = usize>) -> String {
259    indices
260        .into_iter()
261        .map(|index| format!("?{index}"))
262        .collect::<Vec<_>>()
263        .join(", ")
264}
265
266fn namespace_filter(alias: &str, opts: &CompileOptions, params: &mut Vec<QueryValue>) -> String {
267    if opts.scopes.is_empty() {
268        String::new()
269    } else if opts.scopes.len() == 1 {
270        params.push(QueryValue::Text(opts.scopes[0].clone()));
271        format!(" AND {alias}.namespace = ?{}", params.len())
272    } else {
273        let placeholders = in_placeholders(opts.scopes.iter().map(|s| {
274            params.push(QueryValue::Text(s.clone()));
275            params.len()
276        }));
277        format!(" AND {alias}.namespace IN ({})", placeholders)
278    }
279}
280
281/// Maps substrate labels to the union discriminator and granular labels to `kind`.
282fn kind_filter_predicate(alias: &str, kind: &str, params: &mut Vec<QueryValue>) -> String {
283    match kind {
284        "entity" | "note" | "event" | "edge" => {
285            params.push(QueryValue::Text(kind.to_string()));
286            format!("{alias}.substrate_kind = ?{}", params.len())
287        }
288        _ => {
289            params.push(QueryValue::Text(kind.to_string()));
290            format!("{alias}.kind = ?{}", params.len())
291        }
292    }
293}
294
295/// Applies kind filtering to the entity/note observation-target union.
296fn observation_kind_filter_predicate(
297    alias: &str,
298    kind: &str,
299    params: &mut Vec<QueryValue>,
300) -> String {
301    match kind {
302        "entity" | "note" => {
303            params.push(QueryValue::Text(kind.to_string()));
304            format!("{alias}.referent_kind = ?{}", params.len())
305        }
306        _ => {
307            params.push(QueryValue::Text(kind.to_string()));
308            format!("{alias}.kind = ?{}", params.len())
309        }
310    }
311}
312
313/// Compiles typed inline-map equality against a direct or JSON property column.
314/// See `crates/khive-query/docs/api/sql-compilation.md` for storage-class rules.
315fn compile_property_equality(
316    alias: &str,
317    key: &str,
318    value: &ConditionValue,
319    text_column: Option<&str>,
320    params: &mut Vec<QueryValue>,
321) -> Result<String, QueryError> {
322    let is_string = matches!(value, ConditionValue::String(_));
323    match value {
324        ConditionValue::String(s) => params.push(QueryValue::Text(s.clone())),
325        ConditionValue::Integer(n) => params.push(QueryValue::Integer(*n)),
326        ConditionValue::Number(n) => {
327            if !n.is_finite() {
328                return Err(QueryError::InvalidInput(
329                    "non-finite float (NaN or Infinity) is not a valid query parameter".into(),
330                ));
331            }
332            params.push(QueryValue::Float(*n));
333        }
334        ConditionValue::Bool(b) => params.push(QueryValue::Integer(if *b { 1 } else { 0 })),
335        ConditionValue::List(_) | ConditionValue::Null => {
336            return Err(QueryError::Validation(
337                "list and null operands are not valid in inline property maps".into(),
338            ));
339        }
340    }
341    let collate = if is_string { " COLLATE NOCASE" } else { "" };
342    Ok(match text_column {
343        Some(col) => format!("{alias}.{col} = ?{}{collate}", params.len()),
344        None => format!(
345            "json_extract({alias}.properties, '$.{}') = ?{}{collate}",
346            key.replace('\'', "''"),
347            params.len()
348        ),
349    })
350}
351
352/// Returns `(source_indices, target_indices)` for synthetic `observed_as_*` edge endpoints.
353fn synthetic_endpoint_node_indices(
354    elements: &[PatternElement],
355) -> (
356    std::collections::HashSet<usize>,
357    std::collections::HashSet<usize>,
358) {
359    let mut source_set = std::collections::HashSet::new();
360    let mut target_set = std::collections::HashSet::new();
361    let mut node_idx = 0usize;
362    let mut prev_node_idx: Option<usize> = None;
363    for element in elements {
364        match element {
365            PatternElement::Node(_) => {
366                prev_node_idx = Some(node_idx);
367                node_idx += 1;
368            }
369            PatternElement::Edge(ep) => {
370                let has_synthetic = ep.relations.iter().any(|r| is_synthetic(r));
371                if has_synthetic {
372                    if let Some(src_idx) = prev_node_idx {
373                        source_set.insert(src_idx);
374                        // The target is the next node (current node_idx).
375                        target_set.insert(node_idx);
376                    }
377                }
378            }
379        }
380    }
381    (source_set, target_set)
382}
383
384/// Compiles fixed-length patterns to a JOIN chain.
385fn compile_fixed_length(
386    query: &GqlQuery,
387    opts: &CompileOptions,
388    mailbox: Option<&QueryMailboxScope>,
389) -> Result<CompiledQuery, QueryError> {
390    let mut params: Vec<QueryValue> = Vec::new();
391    let mailbox = mailbox.map(|scope| MailboxLowering::bind(scope, &mut params));
392    let mailbox = mailbox.as_ref();
393    let mut from_parts: Vec<String> = Vec::new();
394    let mut join_parts: Vec<String> = Vec::new();
395    let mut where_parts: Vec<String> = Vec::new();
396    let mut select_parts: Vec<String> = Vec::new();
397    let mut order_parts: Vec<String> = Vec::new();
398
399    let mut node_aliases: Vec<String> = Vec::new();
400    let mut var_to_alias: std::collections::HashMap<String, (String, VarKind)> =
401        std::collections::HashMap::new();
402
403    // Synthetic endpoints bind different substrate sources (issue #468).
404    let (event_source_indices, observation_target_indices) =
405        synthetic_endpoint_node_indices(&query.pattern.elements);
406
407    let mut node_idx = 0usize;
408    let mut edge_idx = 0usize;
409
410    for pattern in query.patterns() {
411        let path_start_node_idx = node_idx;
412        for element in &pattern.elements {
413            match element {
414                PatternElement::Node(np) => {
415                    let alias = format!("n{node_idx}");
416                    node_aliases.push(alias.clone());
417
418                    let is_event_source = event_source_indices.contains(&node_idx);
419                    let is_observation_target = observation_target_indices.contains(&node_idx);
420
421                    // Paging requires a total order over match identities, not caller
422                    // projections. UUIDs can overlap across unioned substrates, so keep
423                    // each union's discriminator ahead of its ID.
424                    if is_event_source {
425                        order_parts.push(format!("{alias}.id"));
426                    } else if is_observation_target {
427                        order_parts.push(format!("{alias}.referent_kind"));
428                        order_parts.push(format!("{alias}.id"));
429                    } else {
430                        order_parts.push(format!("{alias}.substrate_kind"));
431                        order_parts.push(format!("{alias}.id"));
432                    }
433
434                    if node_idx == 0 {
435                        if is_event_source {
436                            from_parts.push(format!("events {alias}"));
437                        } else if !is_observation_target {
438                            from_parts.push(primary_node_source(&alias, mailbox));
439                        }
440                    } else if node_idx == path_start_node_idx {
441                        // Validation permits only canonical comma components. Keep the
442                        // same filtered source on each root before the global identity join.
443                        join_parts.push(format!(
444                            "CROSS JOIN {}",
445                            primary_node_source(&alias, mailbox)
446                        ));
447                    }
448
449                    if is_event_source {
450                        // Events have no `deleted_at`; namespace is filtered directly.
451                        let ns_filter = namespace_filter(&alias, opts, &mut params);
452                        if !ns_filter.is_empty() {
453                            where_parts.push(ns_filter.trim_start_matches(" AND ").to_string());
454                        }
455                        // Bare `event` needs no kind predicate on an event-only source.
456                        if let Some(ref kind) = np.kind {
457                            if kind != "event" {
458                                params.push(QueryValue::Text(kind.clone()));
459                                where_parts.push(format!("{alias}.kind = ?{}", params.len()));
460                            }
461                        }
462                        if np.entity_type.is_some() {
463                            return Err(QueryError::Compile(
464                                "event nodes do not have an entity_type column".into(),
465                            ));
466                        }
467                        if !np.properties.is_empty() {
468                            return Err(QueryError::Compile(
469                                "event nodes do not support inline property filters; \
470                             use a WHERE clause on verb, outcome, or payload fields"
471                                    .into(),
472                            ));
473                        }
474                    } else if is_observation_target {
475                        where_parts.push(format!("{alias}.deleted_at IS NULL"));
476
477                        let ns_filter = namespace_filter(&alias, opts, &mut params);
478                        if !ns_filter.is_empty() {
479                            where_parts.push(ns_filter.trim_start_matches(" AND ").to_string());
480                        }
481
482                        if let Some(ref kind) = np.kind {
483                            where_parts.push(observation_kind_filter_predicate(
484                                &alias,
485                                kind,
486                                &mut params,
487                            ));
488                        }
489
490                        if let Some(ref et) = np.entity_type {
491                            params.push(QueryValue::Text(et.clone()));
492                            where_parts.push(format!("{alias}.entity_type = ?{}", params.len()));
493                        }
494
495                        let mut props: Vec<_> = np.properties.iter().collect();
496                        props.sort_by_key(|(k, _)| k.as_str());
497                        for (key, val) in props {
498                            let text_column = if key == "name" || key == "content" {
499                                Some(key.as_str())
500                            } else {
501                                None
502                            };
503                            where_parts.push(compile_property_equality(
504                                &alias,
505                                key,
506                                val,
507                                text_column,
508                                &mut params,
509                            )?);
510                        }
511                    } else {
512                        where_parts.push(format!("{alias}.deleted_at IS NULL"));
513
514                        let ns_filter = namespace_filter(&alias, opts, &mut params);
515                        if !ns_filter.is_empty() {
516                            where_parts.push(ns_filter.trim_start_matches(" AND ").to_string());
517                        }
518
519                        if let Some(ref kind) = np.kind {
520                            where_parts.push(kind_filter_predicate(&alias, kind, &mut params));
521                        }
522
523                        if let Some(ref et) = np.entity_type {
524                            params.push(QueryValue::Text(et.clone()));
525                            where_parts.push(format!("{alias}.entity_type = ?{}", params.len()));
526                        }
527
528                        let mut props: Vec<_> = np.properties.iter().collect();
529                        props.sort_by_key(|(k, _)| k.as_str());
530                        for (key, val) in props {
531                            let text_column = if key == "name" { Some("name") } else { None };
532                            where_parts.push(compile_property_equality(
533                                &alias,
534                                key,
535                                val,
536                                text_column,
537                                &mut params,
538                            )?);
539                        }
540                    }
541
542                    if let Some(ref var) = np.variable {
543                        let kind = if is_event_source {
544                            VarKind::EventNode
545                        } else if is_observation_target {
546                            VarKind::ObservationTargetNode
547                        } else {
548                            VarKind::Node
549                        };
550                        if let Some((first_alias, _)) = var_to_alias.get(var) {
551                            // A repeated name across paths denotes a typed record identity.
552                            // Retain the first alias for projections and every occurrence's filters.
553                            where_parts.push(format!(
554                                "{alias}.substrate_kind = {first_alias}.substrate_kind"
555                            ));
556                            where_parts.push(format!("{alias}.id = {first_alias}.id"));
557                        } else {
558                            var_to_alias.insert(var.clone(), (alias.clone(), kind));
559                        }
560                    }
561
562                    node_idx += 1;
563                }
564                PatternElement::Edge(ep) => {
565                    let e_alias = format!("e{edge_idx}");
566                    let prev_node = &node_aliases[node_aliases.len() - 1];
567                    let next_alias = format!("n{}", node_idx);
568
569                    // The synthetic and canonical backing tables have no meaningful shared join key.
570                    let has_synthetic = ep.relations.iter().any(|r| is_synthetic(r));
571                    let has_canonical = ep.relations.iter().any(|r| !is_synthetic(r));
572                    if has_synthetic && has_canonical {
573                        return Err(QueryError::Compile(
574                        "cannot mix synthetic observed_as_* relations with canonical edge relations \
575                         in a single edge pattern"
576                            .into(),
577                    ));
578                    }
579
580                    if has_synthetic {
581                        // `(event_id, role, position)` is the observation primary key.
582                        order_parts.push(format!("{e_alias}.event_id"));
583                        order_parts.push(format!("{e_alias}.role"));
584                        order_parts.push(format!("{e_alias}.position"));
585                        // Synthetic projections are always event-to-referent.
586                        if !matches!(ep.direction, EdgeDirection::Out) {
587                            return Err(QueryError::Compile(
588                            "synthetic observed_as_* edges are always event → entity (outbound only)".into(),
589                        ));
590                        }
591                        join_parts.push(format!(
592                        "JOIN event_observations {e_alias} ON {e_alias}.event_id = {prev_node}.id"
593                    ));
594                        let roles: Vec<&'static str> = ep
595                            .relations
596                            .iter()
597                            .filter_map(|r| synthetic_role(r))
598                            .collect();
599                        if roles.len() == 1 {
600                            params.push(QueryValue::Text(roles[0].to_string()));
601                            where_parts.push(format!("{e_alias}.role = ?{}", params.len()));
602                        } else if roles.len() > 1 {
603                            let placeholders = in_placeholders(roles.iter().map(|r| {
604                                params.push(QueryValue::Text(r.to_string()));
605                                params.len()
606                            }));
607                            where_parts.push(format!("{e_alias}.role IN ({})", placeholders));
608                        }
609                        // `referent_kind` prevents equal IDs on different substrates from colliding.
610                        join_parts.push(format!(
611                            "JOIN {} ON {next_alias}.id = {e_alias}.entity_id \
612                         AND {next_alias}.referent_kind = {e_alias}.referent_kind",
613                            observation_target_source(&next_alias, mailbox)
614                        ));
615                        // Enforce the ADR-041 role/substrate matrix.
616                        where_parts.push(format!(
617                        "(({e_alias}.role IN ('candidate', 'selected') AND {e_alias}.referent_kind IN ('entity', 'note')) \
618                          OR ({e_alias}.role = 'target' AND {e_alias}.referent_kind IN ('entity', 'note')) \
619                          OR ({e_alias}.role = 'signal' AND {e_alias}.referent_kind IN ('entity', 'note')))"
620                    ));
621                    } else {
622                        order_parts.push(format!("{e_alias}.id"));
623                        let (source_join, target_join) = match ep.direction {
624                        EdgeDirection::Out => (
625                            format!("{e_alias}.source_id = {prev_node}.id"),
626                            "target_id",
627                        ),
628                        EdgeDirection::In => (
629                            format!("{e_alias}.target_id = {prev_node}.id"),
630                            "source_id",
631                        ),
632                        EdgeDirection::Both => (
633                            format!(
634                                "({e_alias}.source_id = {prev_node}.id OR {e_alias}.target_id = {prev_node}.id)"
635                            ),
636                            "CASE_BOTH",
637                        ),
638                    };
639
640                        let next_join_col = if target_join == "CASE_BOTH" {
641                            format!(
642                            "CASE WHEN {e_alias}.source_id = {prev_node}.id THEN {e_alias}.target_id ELSE {e_alias}.source_id END"
643                        )
644                        } else {
645                            format!("{e_alias}.{target_join}")
646                        };
647
648                        join_parts.push(format!(
649                            "JOIN {} ON {source_join} AND {e_alias}.deleted_at IS NULL",
650                            edge_source(&e_alias, mailbox)
651                        ));
652
653                        let ens_filter = namespace_filter(&e_alias, opts, &mut params);
654                        if !ens_filter.is_empty() {
655                            where_parts.push(ens_filter.trim_start_matches(" AND ").to_string());
656                        }
657
658                        join_parts.push(format!(
659                            "JOIN {} ON {next_alias}.id = {next_join_col}",
660                            primary_node_source(&next_alias, mailbox)
661                        ));
662
663                        if !ep.relations.is_empty() {
664                            if ep.relations.len() == 1 {
665                                params.push(QueryValue::Text(ep.relations[0].clone()));
666                                where_parts.push(format!("{e_alias}.relation = ?{}", params.len()));
667                            } else {
668                                let placeholders = in_placeholders(ep.relations.iter().map(|r| {
669                                    params.push(QueryValue::Text(r.clone()));
670                                    params.len()
671                                }));
672                                where_parts
673                                    .push(format!("{e_alias}.relation IN ({})", placeholders));
674                            }
675                        }
676                    }
677
678                    if let Some(ref var) = ep.variable {
679                        let kind = if has_synthetic {
680                            VarKind::SyntheticEdge
681                        } else {
682                            VarKind::Edge
683                        };
684                        var_to_alias.insert(var.clone(), (e_alias.clone(), kind));
685                    }
686
687                    edge_idx += 1;
688                }
689            }
690        }
691    }
692
693    if let Some(where_sql) = compile_where_expr(&query.where_clause, &var_to_alias, &mut params)? {
694        where_parts.push(where_sql);
695    }
696
697    for item in &query.return_items {
698        let var = item.variable();
699        if let Some((alias, kind)) = var_to_alias.get(var) {
700            match item {
701                ReturnItem::Property(_, prop) => {
702                    let col = property_to_column(prop, kind)?;
703                    let output_alias = quote_identifier(&format!("{var}_{prop}"));
704                    select_parts.push(format!("{alias}.{col} AS {output_alias}"));
705                }
706                ReturnItem::Variable(_) => {
707                    let columns: &[(&str, &str)] = match kind {
708                        VarKind::Node => &[
709                            ("id", "id"),
710                            ("namespace", "namespace"),
711                            ("kind", "kind"),
712                            ("entity_type", "entity_type"),
713                            ("name", "name"),
714                            ("properties", "properties"),
715                            ("created_at", "created_at"),
716                            ("updated_at", "updated_at"),
717                        ],
718                        VarKind::ObservationTargetNode => &[
719                            ("id", "id"),
720                            ("namespace", "namespace"),
721                            ("kind", "kind"),
722                            ("entity_type", "entity_type"),
723                            ("status", "status"),
724                            ("content", "content"),
725                            ("salience", "salience"),
726                            ("properties", "properties"),
727                            ("created_at", "created_at"),
728                            ("updated_at", "updated_at"),
729                            ("referent_kind", "referent_kind"),
730                        ],
731                        VarKind::EventNode => &[
732                            ("id", "id"),
733                            ("namespace", "namespace"),
734                            ("verb", "verb"),
735                            ("substrate", "substrate"),
736                            ("actor", "actor"),
737                            ("kind", "kind"),
738                            ("outcome", "outcome"),
739                            ("payload", "payload"),
740                            ("created_at", "created_at"),
741                        ],
742                        VarKind::Edge | VarKind::SyntheticEdge => &[
743                            ("id", "id"),
744                            ("source_id", "source"),
745                            ("target_id", "target"),
746                            ("relation", "relation"),
747                            ("weight", "weight"),
748                        ],
749                    };
750                    for (column, suffix) in columns {
751                        let output_alias = quote_identifier(&format!("{var}_{suffix}"));
752                        select_parts.push(format!("{alias}.{column} AS {output_alias}"));
753                    }
754                }
755            }
756        } else {
757            return Err(QueryError::Compile(format!(
758                "unknown variable '{var}' in RETURN clause"
759            )));
760        }
761    }
762
763    if !query.order_by.is_empty() {
764        let mut requested = Vec::new();
765        for item in &query.order_by {
766            let (alias, kind) = var_to_alias.get(&item.variable).ok_or_else(|| {
767                QueryError::Compile(format!("unknown variable '{}' in ORDER BY", item.variable))
768            })?;
769            let expression = order_property_expression(&item.property, kind, alias, &mut params)?;
770            requested.push(format!(
771                "{expression} COLLATE BINARY {}",
772                order_direction(item.direction)
773            ));
774        }
775        requested.extend(order_parts);
776        order_parts = requested;
777    }
778
779    let offset_i64 = i64::try_from(query.offset)
780        .map_err(|_| QueryError::InvalidInput("SKIP exceeds i64::MAX".into()))?;
781    params.push(QueryValue::Integer(offset_i64));
782    let offset_param = params.len();
783
784    let (limit, truncation_check) = effective_limit(query.limit, query.offset, opts.max_limit);
785    let limit_i64 = i64::try_from(limit)
786        .map_err(|_| QueryError::InvalidInput("limit exceeds i64::MAX".into()))?;
787    params.push(QueryValue::Integer(limit_i64));
788    let limit_param = params.len();
789
790    let sql = format!(
791        "SELECT {} FROM {} {} WHERE {} ORDER BY {} LIMIT ?{} OFFSET ?{}",
792        select_parts.join(", "),
793        from_parts.join(", "),
794        join_parts.join(" "),
795        where_parts.join(" AND "),
796        order_parts.join(", "),
797        limit_param,
798        offset_param,
799    );
800
801    Ok(CompiledQuery {
802        sql,
803        params,
804        return_vars: query.return_items.clone(),
805        warnings: Vec::new(),
806        truncation_check,
807    })
808}
809
810/// Compiles a `WhereExpr` while preserving its boolean grouping.
811fn compile_where_expr(
812    expr: &WhereExpr,
813    var_to_alias: &std::collections::HashMap<String, (String, VarKind)>,
814    params: &mut Vec<QueryValue>,
815) -> Result<Option<String>, QueryError> {
816    match expr {
817        WhereExpr::True => Ok(None),
818        WhereExpr::Condition(cond) => {
819            let sql = compile_single_condition(cond, var_to_alias, params)?;
820            Ok(Some(sql))
821        }
822        WhereExpr::And(l, r) => {
823            let ls = compile_where_expr(l, var_to_alias, params)?;
824            let rs = compile_where_expr(r, var_to_alias, params)?;
825            Ok(match (ls, rs) {
826                (None, None) => None,
827                (Some(s), None) | (None, Some(s)) => Some(s),
828                (Some(l), Some(r)) => Some(format!("{l} AND {r}")),
829            })
830        }
831        WhereExpr::Or(l, r) => {
832            let ls = compile_where_expr(l, var_to_alias, params)?;
833            let rs = compile_where_expr(r, var_to_alias, params)?;
834            Ok(match (ls, rs) {
835                (None, None) => None,
836                (Some(s), None) | (None, Some(s)) => Some(s),
837                (Some(l), Some(r)) => Some(format!("({l} OR {r})")),
838            })
839        }
840    }
841}
842
843fn compile_single_condition(
844    cond: &Condition,
845    var_to_alias: &std::collections::HashMap<String, (String, VarKind)>,
846    params: &mut Vec<QueryValue>,
847) -> Result<String, QueryError> {
848    let (alias, kind) = var_to_alias.get(&cond.variable).ok_or_else(|| {
849        QueryError::Compile(format!(
850            "unknown variable '{}' in WHERE clause",
851            cond.variable
852        ))
853    })?;
854
855    let col_expr = where_property_expression(&cond.property, kind, alias, params)?;
856
857    compile_condition_predicate(&col_expr, cond, params)
858}
859
860fn bind_condition_value(
861    value: &ConditionValue,
862    params: &mut Vec<QueryValue>,
863) -> Result<usize, QueryError> {
864    match value {
865        ConditionValue::String(s) => params.push(QueryValue::Text(s.clone())),
866        ConditionValue::Integer(n) => params.push(QueryValue::Integer(*n)),
867        ConditionValue::Number(n) => {
868            if !n.is_finite() {
869                return Err(QueryError::InvalidInput(
870                    "non-finite float (NaN or Infinity) is not a valid query parameter".into(),
871                ));
872            }
873            params.push(QueryValue::Float(*n));
874        }
875        ConditionValue::Bool(b) => {
876            params.push(QueryValue::Integer(if *b { 1 } else { 0 }));
877        }
878        ConditionValue::List(_) | ConditionValue::Null => {
879            return Err(QueryError::Validation(
880                "operator requires a scalar value".into(),
881            ));
882        }
883    }
884    Ok(params.len())
885}
886
887fn compile_condition_predicate(
888    col_expr: &str,
889    cond: &Condition,
890    params: &mut Vec<QueryValue>,
891) -> Result<String, QueryError> {
892    match cond.op {
893        CompareOp::Contains | CompareOp::StartsWith => {
894            let ConditionValue::String(value) = &cond.value else {
895                return Err(QueryError::Validation(
896                    "CONTAINS and STARTS WITH require a string literal".into(),
897                ));
898            };
899            let escaped = khive_types::escape_like_literal(value);
900            let pattern = if cond.op == CompareOp::Contains {
901                format!("%{escaped}%")
902            } else {
903                format!("{escaped}%")
904            };
905            params.push(QueryValue::Text(pattern));
906            Ok(format!(
907                "{col_expr} LIKE ?{} COLLATE NOCASE ESCAPE '\\'",
908                params.len()
909            ))
910        }
911        CompareOp::In => {
912            let ConditionValue::List(values) = &cond.value else {
913                return Err(QueryError::Validation("IN requires a list literal".into()));
914            };
915            if values.is_empty() {
916                return Ok("0".into());
917            }
918            let has_string = values
919                .iter()
920                .any(|value| matches!(value, ConditionValue::String(_)));
921            let placeholders = values
922                .iter()
923                .map(|value| bind_condition_value(value, params))
924                .collect::<Result<Vec<_>, _>>()?;
925            let collate = if has_string { " COLLATE NOCASE" } else { "" };
926            Ok(format!(
927                "{col_expr}{collate} IN ({})",
928                in_placeholders(placeholders)
929            ))
930        }
931        CompareOp::IsNotNull => {
932            if !matches!(cond.value, ConditionValue::Null) {
933                return Err(QueryError::Validation(
934                    "IS NOT NULL does not accept a value".into(),
935                ));
936            }
937            Ok(format!("{col_expr} IS NOT NULL"))
938        }
939        CompareOp::IsNull => {
940            if !matches!(cond.value, ConditionValue::Null) {
941                return Err(QueryError::Validation(
942                    "IS NULL does not accept a value".into(),
943                ));
944            }
945            Ok(format!("{col_expr} IS NULL"))
946        }
947        op => {
948            let op_str = match op {
949                CompareOp::Eq => "=",
950                CompareOp::Neq => "!=",
951                CompareOp::Gt => ">",
952                CompareOp::Lt => "<",
953                CompareOp::Gte => ">=",
954                CompareOp::Lte => "<=",
955                CompareOp::Like => "LIKE",
956                CompareOp::Contains
957                | CompareOp::StartsWith
958                | CompareOp::In
959                | CompareOp::IsNotNull
960                | CompareOp::IsNull => unreachable!(),
961            };
962            let is_string = matches!(cond.value, ConditionValue::String(_));
963            let param_index = bind_condition_value(&cond.value, params)?;
964            let collate = if is_string && matches!(op, CompareOp::Eq | CompareOp::Like) {
965                " COLLATE NOCASE"
966            } else {
967                ""
968            };
969            Ok(format!("{col_expr} {op_str} ?{param_index}{collate}"))
970        }
971    }
972}
973
974fn expr_endpoint_set(
975    expr: &WhereExpr,
976    start_var: Option<&str>,
977    end_var: Option<&str>,
978    edge_var: Option<&str>,
979) -> (bool, bool) {
980    match expr {
981        WhereExpr::True => (false, false),
982        WhereExpr::Condition(c) => {
983            let is_start = start_var == Some(c.variable.as_str());
984            let is_end = end_var == Some(c.variable.as_str())
985                || (edge_var == Some(c.variable.as_str())
986                    && matches!(&c.property, PropertyRef::MetadataPath(_)));
987            (is_start, is_end)
988        }
989        WhereExpr::And(l, r) | WhereExpr::Or(l, r) => {
990            let (ls, le) = expr_endpoint_set(l, start_var, end_var, edge_var);
991            let (rs, re) = expr_endpoint_set(r, start_var, end_var, edge_var);
992            (ls || rs, le || re)
993        }
994    }
995}
996
997/// Rejects OR subtrees spanning the start phase and final node/edge phase.
998fn reject_or_spanning_endpoints(
999    expr: &WhereExpr,
1000    start: &NodePattern,
1001    end: &NodePattern,
1002    edge: &EdgePattern,
1003) -> Result<(), QueryError> {
1004    let start_var = start.variable.as_deref();
1005    let end_var = end.variable.as_deref();
1006    let edge_var = edge.variable.as_deref();
1007    reject_or_spanning_impl(expr, start_var, end_var, edge_var)
1008}
1009
1010fn reject_or_spanning_impl(
1011    expr: &WhereExpr,
1012    start_var: Option<&str>,
1013    end_var: Option<&str>,
1014    edge_var: Option<&str>,
1015) -> Result<(), QueryError> {
1016    match expr {
1017        WhereExpr::True | WhereExpr::Condition(_) => Ok(()),
1018        WhereExpr::And(l, r) => {
1019            reject_or_spanning_impl(l, start_var, end_var, edge_var)?;
1020            reject_or_spanning_impl(r, start_var, end_var, edge_var)
1021        }
1022        WhereExpr::Or(l, r) => {
1023            let (l_start, l_end) = expr_endpoint_set(l, start_var, end_var, edge_var);
1024            let (r_start, r_end) = expr_endpoint_set(r, start_var, end_var, edge_var);
1025            let spans_start = l_start || r_start;
1026            let spans_end = l_end || r_end;
1027            if spans_start && spans_end {
1028                return Err(QueryError::Unsupported(
1029                    "WHERE clauses that span the start and final row in a variable-length pattern \
1030                     are not yet supported; rewrite as separate queries or restrict each \
1031                     OR branch to one phase"
1032                        .into(),
1033                ));
1034            }
1035            reject_or_spanning_impl(l, start_var, end_var, edge_var)?;
1036            reject_or_spanning_impl(r, start_var, end_var, edge_var)
1037        }
1038    }
1039}
1040
1041fn compile_var_len_condition(
1042    cond: &Condition,
1043    start_var: Option<&str>,
1044    end_var: Option<&str>,
1045    edge_var: Option<&str>,
1046    params: &mut Vec<QueryValue>,
1047) -> Result<(String, &'static str), QueryError> {
1048    if edge_var == Some(cond.variable.as_str()) {
1049        if let PropertyRef::MetadataPath(path) = &cond.property {
1050            let expression = bound_json_path_expression("t.via_metadata", path, params)?;
1051            // Final-edge predicates share the final node's phase, not its SQL alias.
1052            return Ok((compile_condition_predicate(&expression, cond, params)?, "r"));
1053        }
1054    }
1055    let col_alias = if start_var == Some(cond.variable.as_str()) {
1056        "s"
1057    } else if end_var == Some(cond.variable.as_str()) {
1058        "r"
1059    } else {
1060        return Err(QueryError::Compile(format!(
1061            "variable '{}' in WHERE not supported in variable-length pattern \
1062             (only start/end node variables)",
1063            cond.variable
1064        )));
1065    };
1066
1067    let col_expr = where_property_expression(&cond.property, &VarKind::Node, col_alias, params)?;
1068
1069    let sql = compile_condition_predicate(&col_expr, cond, params)?;
1070    Ok((sql, col_alias))
1071}
1072
1073/// Routes variable-length predicates to the start or end CTE phase.
1074fn compile_variable_length_where(
1075    expr: &WhereExpr,
1076    (start_var, end_var, edge_var): (Option<&str>, Option<&str>, Option<&str>),
1077    params: &mut Vec<QueryValue>,
1078    start_conditions: &mut Vec<String>,
1079    end_conditions: &mut Vec<String>,
1080) -> Result<Option<String>, QueryError> {
1081    match expr {
1082        WhereExpr::True => Ok(None),
1083        WhereExpr::Condition(cond) => {
1084            let (sql, alias) =
1085                compile_var_len_condition(cond, start_var, end_var, edge_var, params)?;
1086            if alias == "s" {
1087                start_conditions.push(sql);
1088            } else {
1089                end_conditions.push(sql);
1090            }
1091            Ok(None)
1092        }
1093        WhereExpr::And(l, r) => {
1094            compile_variable_length_where(
1095                l,
1096                (start_var, end_var, edge_var),
1097                params,
1098                start_conditions,
1099                end_conditions,
1100            )?;
1101            compile_variable_length_where(
1102                r,
1103                (start_var, end_var, edge_var),
1104                params,
1105                start_conditions,
1106                end_conditions,
1107            )?;
1108            Ok(None)
1109        }
1110        WhereExpr::Or(l, r) => {
1111            // The prior spanning check guarantees both branches use one endpoint.
1112            let l_sql =
1113                compile_variable_length_where_to_sql(l, start_var, end_var, edge_var, params)?;
1114            let r_sql =
1115                compile_variable_length_where_to_sql(r, start_var, end_var, edge_var, params)?;
1116            match (l_sql, r_sql) {
1117                (None, None) => {}
1118                (Some((ls, la)), None) => {
1119                    if la == "s" {
1120                        start_conditions.push(ls);
1121                    } else {
1122                        end_conditions.push(ls);
1123                    }
1124                }
1125                (None, Some((rs, ra))) => {
1126                    if ra == "s" {
1127                        start_conditions.push(rs);
1128                    } else {
1129                        end_conditions.push(rs);
1130                    }
1131                }
1132                (Some((ls, la)), Some((rs, _ra))) => {
1133                    let combined = format!("({ls} OR {rs})");
1134                    if la == "s" {
1135                        start_conditions.push(combined);
1136                    } else {
1137                        end_conditions.push(combined);
1138                    }
1139                }
1140            }
1141            Ok(None)
1142        }
1143    }
1144}
1145
1146/// Compiles one endpoint-local expression and returns its CTE alias.
1147fn compile_variable_length_where_to_sql(
1148    expr: &WhereExpr,
1149    start_var: Option<&str>,
1150    end_var: Option<&str>,
1151    edge_var: Option<&str>,
1152    params: &mut Vec<QueryValue>,
1153) -> Result<Option<(String, &'static str)>, QueryError> {
1154    match expr {
1155        WhereExpr::True => Ok(None),
1156        WhereExpr::Condition(cond) => {
1157            let (sql, alias) =
1158                compile_var_len_condition(cond, start_var, end_var, edge_var, params)?;
1159            Ok(Some((sql, alias)))
1160        }
1161        WhereExpr::And(l, r) => {
1162            let ls = compile_variable_length_where_to_sql(l, start_var, end_var, edge_var, params)?;
1163            let rs = compile_variable_length_where_to_sql(r, start_var, end_var, edge_var, params)?;
1164            Ok(match (ls, rs) {
1165                (None, None) => None,
1166                (Some(s), None) | (None, Some(s)) => Some(s),
1167                (Some((lsql, la)), Some((rsql, _))) => Some((format!("{lsql} AND {rsql}"), la)),
1168            })
1169        }
1170        WhereExpr::Or(l, r) => {
1171            let ls = compile_variable_length_where_to_sql(l, start_var, end_var, edge_var, params)?;
1172            let rs = compile_variable_length_where_to_sql(r, start_var, end_var, edge_var, params)?;
1173            Ok(match (ls, rs) {
1174                (None, None) => None,
1175                (Some(s), None) | (None, Some(s)) => Some(s),
1176                (Some((lsql, la)), Some((rsql, _))) => Some((format!("({lsql} OR {rsql})"), la)),
1177            })
1178        }
1179    }
1180}
1181
1182/// Compiles one variable-length edge to a recursive CTE.
1183fn compile_variable_length(
1184    query: &GqlQuery,
1185    opts: &CompileOptions,
1186    mailbox: Option<&QueryMailboxScope>,
1187) -> Result<CompiledQuery, QueryError> {
1188    let mut params: Vec<QueryValue> = Vec::new();
1189    let mailbox = mailbox.map(|scope| MailboxLowering::bind(scope, &mut params));
1190    let mailbox = mailbox.as_ref();
1191    let mut var_to_alias: std::collections::HashMap<String, (String, VarKind)> =
1192        std::collections::HashMap::new();
1193
1194    // Reject extra elements rather than silently dropping part of the pattern.
1195    let nodes: Vec<&NodePattern> = query.pattern.nodes().collect();
1196    let edges: Vec<&EdgePattern> = query.pattern.edges().collect();
1197
1198    if nodes.len() != 2 || edges.len() != 1 || query.pattern.elements.len() != 3 {
1199        return Err(QueryError::Unsupported(
1200            "variable-length patterns must be a single start_node -[*N..M]-> end_node \
1201             (mixed fixed/variable chains are not yet implemented)"
1202                .into(),
1203        ));
1204    }
1205
1206    let start = &nodes[0];
1207    let edge = &edges[0];
1208    let end = &nodes[1];
1209
1210    // event_observations has no recursive path structure.
1211    if edge.relations.iter().any(|r| is_synthetic(r)) {
1212        return Err(QueryError::Unsupported(
1213            "synthetic observed_as_* edges cannot be variable-length; \
1214             use a fixed-length edge pattern instead"
1215                .into(),
1216        ));
1217    }
1218
1219    let carries_metadata = query.return_items.iter().any(|item| {
1220        matches!(item, ReturnItem::Property(var, field)
1221            if edge.variable.as_deref() == Some(var.as_str()) && field == "metadata")
1222    }) || query.where_clause.conditions().any(|condition| {
1223        edge.variable.as_deref() == Some(condition.variable.as_str())
1224            && matches!(&condition.property, PropertyRef::MetadataPath(_))
1225    }) || query.order_by.iter().any(|item| {
1226        edge.variable.as_deref() == Some(item.variable.as_str())
1227            && matches!(&item.property, PropertyRef::MetadataPath(_))
1228    });
1229    let metadata_column = if carries_metadata {
1230        ", via_metadata"
1231    } else {
1232        ""
1233    };
1234    let metadata_value = if carries_metadata { ", e.metadata" } else { "" };
1235
1236    let max_depth = edge.max_hops.min(MAX_DEPTH);
1237    let min_depth = edge.min_hops;
1238
1239    let mut start_conditions: Vec<String> = vec!["s.deleted_at IS NULL".to_string()];
1240    let ns_filter = namespace_filter("s", opts, &mut params);
1241    if !ns_filter.is_empty() {
1242        start_conditions.push(ns_filter.trim_start_matches(" AND ").to_string());
1243    }
1244
1245    if let Some(ref kind) = start.kind {
1246        start_conditions.push(kind_filter_predicate("s", kind, &mut params));
1247    }
1248    if let Some(ref et) = start.entity_type {
1249        params.push(QueryValue::Text(et.clone()));
1250        start_conditions.push(format!("s.entity_type = ?{}", params.len()));
1251    }
1252    let mut start_props: Vec<_> = start.properties.iter().collect();
1253    start_props.sort_by_key(|(k, _)| k.as_str());
1254    for (key, val) in start_props {
1255        let text_column = if key == "name" { Some("name") } else { None };
1256        start_conditions.push(compile_property_equality(
1257            "s",
1258            key,
1259            val,
1260            text_column,
1261            &mut params,
1262        )?);
1263    }
1264
1265    let mut relation_condition = String::new();
1266    if !edge.relations.is_empty() {
1267        if edge.relations.len() == 1 {
1268            params.push(QueryValue::Text(edge.relations[0].clone()));
1269            relation_condition = format!(" AND e.relation = ?{}", params.len());
1270        } else {
1271            let placeholders = in_placeholders(edge.relations.iter().map(|r| {
1272                params.push(QueryValue::Text(r.clone()));
1273                params.len()
1274            }));
1275            relation_condition = format!(" AND e.relation IN ({})", placeholders);
1276        }
1277    }
1278
1279    let e_ns_filter = namespace_filter("e", opts, &mut params);
1280
1281    let (seed_join, seed_next, recurse_join, recurse_next) = match edge.direction {
1282        EdgeDirection::Out => (
1283            "e.source_id = s.id",
1284            "e.target_id",
1285            "e.source_id = t.current_id",
1286            "e.target_id",
1287        ),
1288        EdgeDirection::In => (
1289            "e.target_id = s.id",
1290            "e.source_id",
1291            "e.target_id = t.current_id",
1292            "e.source_id",
1293        ),
1294        EdgeDirection::Both => (
1295            "(e.source_id = s.id OR e.target_id = s.id)",
1296            "CASE WHEN e.source_id = s.id THEN e.target_id ELSE e.source_id END",
1297            "(e.source_id = t.current_id OR e.target_id = t.current_id)",
1298            "CASE WHEN e.source_id = t.current_id THEN e.target_id ELSE e.source_id END",
1299        ),
1300    };
1301
1302    // Filter every intermediate node so paths cannot cross deleted or out-of-scope rows.
1303    let next_node_ns_filter = namespace_filter("next_node", opts, &mut params);
1304
1305    let max_depth_i64 = i64::try_from(max_depth)
1306        .map_err(|_| QueryError::InvalidInput("max_depth exceeds i64::MAX".into()))?;
1307    params.push(QueryValue::Integer(max_depth_i64));
1308    let depth_param = params.len();
1309
1310    // End filters require `r` even when the end variable is not projected.
1311    let mut end_conditions: Vec<String> = vec!["r.deleted_at IS NULL".to_string()];
1312    let r_ns_filter = namespace_filter("r", opts, &mut params);
1313    if !r_ns_filter.is_empty() {
1314        end_conditions.push(r_ns_filter.trim_start_matches(" AND ").to_string());
1315    }
1316    if let Some(ref kind) = end.kind {
1317        end_conditions.push(kind_filter_predicate("r", kind, &mut params));
1318    }
1319    if let Some(ref et) = end.entity_type {
1320        params.push(QueryValue::Text(et.clone()));
1321        end_conditions.push(format!("r.entity_type = ?{}", params.len()));
1322    }
1323    let mut end_props: Vec<_> = end.properties.iter().collect();
1324    end_props.sort_by_key(|(k, _)| k.as_str());
1325    for (key, val) in end_props {
1326        let text_column = if key == "name" { Some("name") } else { None };
1327        end_conditions.push(compile_property_equality(
1328            "r",
1329            key,
1330            val,
1331            text_column,
1332            &mut params,
1333        )?);
1334    }
1335
1336    reject_or_spanning_endpoints(&query.where_clause, start, end, edge)?;
1337
1338    if let Some(where_sql) = compile_variable_length_where(
1339        &query.where_clause,
1340        (
1341            start.variable.as_deref(),
1342            end.variable.as_deref(),
1343            edge.variable.as_deref(),
1344        ),
1345        &mut params,
1346        &mut start_conditions,
1347        &mut end_conditions,
1348    )? {
1349        // Keep the defensive fallback if a future expression has no endpoint binding.
1350        start_conditions.push(where_sql);
1351    }
1352
1353    if min_depth > 0 {
1354        let min_depth_i64 = i64::try_from(min_depth)
1355            .map_err(|_| QueryError::InvalidInput("min_depth exceeds i64::MAX".into()))?;
1356        params.push(QueryValue::Integer(min_depth_i64));
1357        end_conditions.push(format!("t.depth >= ?{}", params.len()));
1358    }
1359
1360    if let Some(ref var) = start.variable {
1361        var_to_alias.insert(var.clone(), ("s".to_string(), VarKind::Node));
1362    }
1363    if let Some(ref var) = end.variable {
1364        var_to_alias.insert(var.clone(), ("r".to_string(), VarKind::Node));
1365    }
1366    if let Some(ref var) = edge.variable {
1367        var_to_alias.insert(var.clone(), ("e".to_string(), VarKind::Edge));
1368    }
1369
1370    let mut select_parts: Vec<String> = Vec::new();
1371    let mut projection_aliases: Vec<String> = Vec::new();
1372    let mut returned_columns: std::collections::HashMap<(String, String), String> =
1373        std::collections::HashMap::new();
1374    let mut has_start = false;
1375
1376    for item in &query.return_items {
1377        let var = item.variable();
1378        if let Some((_, kind)) = var_to_alias.get(var) {
1379            match item {
1380                ReturnItem::Property(_, prop) => {
1381                    let is_start = start.variable.as_deref() == Some(var);
1382                    if matches!(
1383                        kind,
1384                        VarKind::EventNode
1385                            | VarKind::ObservationTargetNode
1386                            | VarKind::SyntheticEdge
1387                    ) {
1388                        return Err(QueryError::Unsupported(
1389                            "synthetic observed_as_* edges cannot be used in variable-length \
1390                             patterns; use a fixed-length edge pattern instead"
1391                                .into(),
1392                        ));
1393                    }
1394                    if *kind == VarKind::Node {
1395                        let tbl = if is_start { "s" } else { "r" };
1396                        if is_start {
1397                            has_start = true;
1398                        }
1399                        let col = property_to_column(prop, kind)?;
1400                        let output_alias = quote_identifier(&format!("{var}_{prop}"));
1401                        select_parts.push(format!("{tbl}.{col} AS {output_alias}"));
1402                        returned_columns
1403                            .insert((var.to_owned(), prop.to_owned()), output_alias.clone());
1404                        projection_aliases.push(output_alias);
1405                    } else {
1406                        let col = match prop.as_str() {
1407                            "id" => "via_edge",
1408                            "relation" => "via_relation",
1409                            "weight" => "via_weight",
1410                            "metadata" => "via_metadata",
1411                            _ => {
1412                                return Err(QueryError::Compile(format!(
1413                                    "unknown edge property '{prop}' in RETURN projection. \
1414                                     Valid: id, relation, weight, metadata"
1415                                )));
1416                            }
1417                        };
1418                        let output_alias = quote_identifier(&format!("{var}_{prop}"));
1419                        select_parts.push(format!("t.{col} AS {output_alias}"));
1420                        returned_columns
1421                            .insert((var.to_owned(), prop.to_owned()), output_alias.clone());
1422                        projection_aliases.push(output_alias);
1423                    }
1424                }
1425                ReturnItem::Variable(_) => match kind {
1426                    VarKind::Node => {
1427                        let tbl = if start.variable.as_deref() == Some(var) {
1428                            has_start = true;
1429                            "s"
1430                        } else {
1431                            "r"
1432                        };
1433                        for column in [
1434                            "id",
1435                            "namespace",
1436                            "kind",
1437                            "entity_type",
1438                            "name",
1439                            "properties",
1440                            "created_at",
1441                            "updated_at",
1442                        ] {
1443                            let output_alias = quote_identifier(&format!("{var}_{column}"));
1444                            select_parts.push(format!("{tbl}.{column} AS {output_alias}"));
1445                            returned_columns
1446                                .insert((var.to_owned(), column.to_owned()), output_alias.clone());
1447                            projection_aliases.push(output_alias);
1448                        }
1449                    }
1450                    VarKind::EventNode
1451                    | VarKind::ObservationTargetNode
1452                    | VarKind::SyntheticEdge => {
1453                        return Err(QueryError::Unsupported(
1454                            "synthetic observed_as_* edges cannot be used in variable-length \
1455                             patterns; use a fixed-length edge pattern instead"
1456                                .into(),
1457                        ));
1458                    }
1459                    VarKind::Edge => {
1460                        for (column, suffix) in [
1461                            ("via_edge", "id"),
1462                            ("via_relation", "relation"),
1463                            ("via_weight", "weight"),
1464                        ] {
1465                            let output_alias = quote_identifier(&format!("{var}_{suffix}"));
1466                            select_parts.push(format!("t.{column} AS {output_alias}"));
1467                            returned_columns
1468                                .insert((var.to_owned(), suffix.to_owned()), output_alias.clone());
1469                            projection_aliases.push(output_alias);
1470                        }
1471                    }
1472                },
1473            }
1474        } else {
1475            return Err(QueryError::Compile(format!(
1476                "unknown variable '{var}' in RETURN clause"
1477            )));
1478        }
1479    }
1480
1481    let depth_alias = quote_identifier("_depth");
1482    let total_weight_alias = quote_identifier("_total_weight");
1483    select_parts.push(format!("t.depth AS {depth_alias}"));
1484    select_parts.push(format!("t.total_weight AS {total_weight_alias}"));
1485
1486    // This path uses SELECT DISTINCT, so ordering by hidden path identities can
1487    // pick an arbitrary representative for collapsed rows. Ordering by every
1488    // projected alias instead gives a total order over the rows that survive
1489    // DISTINCT while retaining depth/weight as the leading traversal order.
1490    // Keep aliases as data: public AST names may contain SQL delimiters.
1491    projection_aliases.retain(|alias| alias != &depth_alias && alias != &total_weight_alias);
1492    let projection_order = projection_aliases.join(", ");
1493    let projection_order_suffix = if projection_order.is_empty() {
1494        String::new()
1495    } else {
1496        format!(", {projection_order}")
1497    };
1498
1499    let mut requested_order = Vec::new();
1500    for item in &query.order_by {
1501        let root = match &item.property {
1502            PropertyRef::Field(field) => field.as_str(),
1503            PropertyRef::JsonPath(_) => "properties",
1504            PropertyRef::MetadataPath(_) => "metadata",
1505        };
1506        let projected = returned_columns
1507            .get(&(item.variable.clone(), root.to_owned()))
1508            .ok_or_else(|| {
1509                QueryError::Unsupported(
1510                    "variable-length ORDER BY must be derived from RETURN projections".into(),
1511                )
1512            })?;
1513        let expression = match &item.property {
1514            PropertyRef::Field(_) => projected.clone(),
1515            PropertyRef::JsonPath(path) | PropertyRef::MetadataPath(path) => {
1516                bound_json_path_expression(projected, path, &mut params)?
1517            }
1518        };
1519        requested_order.push(format!(
1520            "{expression} COLLATE BINARY {}",
1521            order_direction(item.direction)
1522        ));
1523    }
1524    let requested_order = if requested_order.is_empty() {
1525        String::new()
1526    } else {
1527        format!("{}, ", requested_order.join(", "))
1528    };
1529
1530    let offset_i64 = i64::try_from(query.offset)
1531        .map_err(|_| QueryError::InvalidInput("SKIP exceeds i64::MAX".into()))?;
1532    params.push(QueryValue::Integer(offset_i64));
1533    let offset_param = params.len();
1534
1535    let (limit, truncation_check) = effective_limit(query.limit, query.offset, opts.max_limit);
1536    let limit_i64 = i64::try_from(limit)
1537        .map_err(|_| QueryError::InvalidInput("limit exceeds i64::MAX".into()))?;
1538    params.push(QueryValue::Integer(limit_i64));
1539    let limit_param = params.len();
1540
1541    // `r` is required by end filters; `s` is needed only for a start projection.
1542    let join_start = if has_start {
1543        "JOIN primary_nodes s ON s.id = t.start_id"
1544    } else {
1545        ""
1546    };
1547    let join_end = "JOIN primary_nodes r ON r.id = t.current_id";
1548
1549    let next_node_ns_and = if next_node_ns_filter.is_empty() {
1550        String::new()
1551    } else {
1552        format!(" AND {}", next_node_ns_filter.trim_start_matches(" AND "))
1553    };
1554
1555    let sql = format!(
1556        "WITH RECURSIVE primary_nodes AS ({primary_nodes}), \
1557         traverse(start_id, current_id, depth, path, total_weight, via_edge, via_relation, via_weight{metadata_column}) AS (\
1558             SELECT s.id, {seed_next}, 1, s.id || ',' || {seed_next}, e.weight, \
1559                    e.id, e.relation, e.weight{metadata_value} \
1560             FROM primary_nodes s \
1561             JOIN {edges} ON {seed_join} AND e.deleted_at IS NULL{e_ns_filter}{relation_condition} \
1562             JOIN primary_nodes next_node ON next_node.id = ({seed_next}) \
1563                    AND next_node.deleted_at IS NULL{next_node_ns_and} \
1564             WHERE {start_where} \
1565             UNION ALL \
1566             SELECT t.start_id, {recurse_next}, t.depth + 1, \
1567                    t.path || ',' || {recurse_next}, \
1568                    t.total_weight + e.weight, \
1569                    e.id, e.relation, e.weight{metadata_value} \
1570             FROM traverse t CROSS JOIN {edges} \
1571                 ON {recurse_join} AND e.deleted_at IS NULL{e_ns_filter}{relation_condition} \
1572             JOIN primary_nodes next_node ON next_node.id = ({recurse_next}) \
1573                    AND next_node.deleted_at IS NULL{next_node_ns_and} \
1574             WHERE t.depth < ?{depth_param} \
1575               AND (',' || t.path || ',') NOT LIKE '%,' || {recurse_next} || ',%' \
1576         ) \
1577         SELECT DISTINCT {select_cols} \
1578         FROM traverse t \
1579         {join_start} {join_end} \
1580         WHERE {end_where} \
1581         ORDER BY {requested_order}{depth_alias}, {total_weight_alias} DESC{projection_order_suffix} \
1582         LIMIT ?{limit_param} OFFSET ?{offset_param}",
1583        primary_nodes = primary_node_sql(mailbox),
1584        edges = edge_source("e", mailbox),
1585        seed_next = seed_next,
1586        seed_join = seed_join,
1587        e_ns_filter = e_ns_filter,
1588        relation_condition = relation_condition,
1589        start_where = start_conditions.join(" AND "),
1590        recurse_next = recurse_next,
1591        recurse_join = recurse_join,
1592        next_node_ns_and = next_node_ns_and,
1593        depth_param = depth_param,
1594        select_cols = select_parts.join(", "),
1595        join_start = join_start,
1596        join_end = join_end,
1597        end_where = end_conditions.join(" AND "),
1598        projection_order_suffix = projection_order_suffix,
1599        limit_param = limit_param,
1600        offset_param = offset_param,
1601    );
1602
1603    Ok(CompiledQuery {
1604        sql,
1605        params,
1606        return_vars: query.return_items.clone(),
1607        warnings: Vec::new(),
1608        truncation_check,
1609    })
1610}
1611
1612#[derive(Clone, Copy, PartialEq, Eq)]
1613enum VarKind {
1614    Node,
1615    /// Synthetic observation source backed by `events`.
1616    EventNode,
1617    /// Synthetic observation target backed by an entity/note referent.
1618    ObservationTargetNode,
1619    Edge,
1620    /// Observation edges have no canonical graph-edge metadata column.
1621    SyntheticEdge,
1622}
1623
1624const NODE_COLUMNS: &[&str] = &[
1625    "id",
1626    "name",
1627    "kind",
1628    "entity_type",
1629    "namespace",
1630    "description",
1631    "properties",
1632    "created_at",
1633    "updated_at",
1634];
1635/// Projection columns for entity/note observation targets.
1636const OBSERVATION_TARGET_COLUMNS: &[&str] = &[
1637    "id",
1638    "namespace",
1639    "kind",
1640    "entity_type",
1641    "status",
1642    "name",
1643    "content",
1644    "salience",
1645    "decay_factor",
1646    "properties",
1647    "created_at",
1648    "updated_at",
1649    "referent_kind",
1650];
1651/// Projection columns for synthetic event sources.
1652const EVENT_COLUMNS: &[&str] = &[
1653    "id",
1654    "namespace",
1655    "verb",
1656    "substrate",
1657    "actor",
1658    "kind",
1659    "outcome",
1660    "payload",
1661    "duration_us",
1662    "target_id",
1663    "session_id",
1664    "created_at",
1665];
1666const EDGE_COLUMNS: &[&str] = &[
1667    "id",
1668    "source_id",
1669    "target_id",
1670    "relation",
1671    "weight",
1672    "metadata",
1673];
1674const SYNTHETIC_EDGE_COLUMNS: &[&str] = &["id", "source_id", "target_id", "relation", "weight"];
1675const NODE_WHERE_COLUMNS: &[&str] = &[
1676    "id",
1677    "name",
1678    "kind",
1679    "entity_type",
1680    "description",
1681    "created_at",
1682    "updated_at",
1683];
1684const OBSERVATION_TARGET_WHERE_COLUMNS: &[&str] = &[
1685    "id",
1686    "kind",
1687    "entity_type",
1688    "status",
1689    "name",
1690    "content",
1691    "salience",
1692    "decay_factor",
1693    "created_at",
1694    "updated_at",
1695    "referent_kind",
1696];
1697const EVENT_WHERE_COLUMNS: &[&str] = &[
1698    "id",
1699    "verb",
1700    "substrate",
1701    "actor",
1702    "kind",
1703    "outcome",
1704    "payload",
1705    "duration_us",
1706    "target_id",
1707    "session_id",
1708    "created_at",
1709];
1710const EDGE_WHERE_COLUMNS: &[&str] = &["relation", "weight"];
1711
1712fn property_columns(kind: &VarKind) -> (&'static [&'static str], &'static str) {
1713    match kind {
1714        VarKind::Node => (NODE_COLUMNS, "node"),
1715        VarKind::ObservationTargetNode => (OBSERVATION_TARGET_COLUMNS, "observation target"),
1716        VarKind::EventNode => (EVENT_COLUMNS, "event"),
1717        VarKind::Edge => (EDGE_COLUMNS, "edge"),
1718        VarKind::SyntheticEdge => (SYNTHETIC_EDGE_COLUMNS, "edge"),
1719    }
1720}
1721
1722fn property_to_column<'a>(prop: &'a str, kind: &VarKind) -> Result<&'a str, QueryError> {
1723    let (valid, kind_name) = property_columns(kind);
1724    if valid.contains(&prop) {
1725        Ok(prop)
1726    } else {
1727        Err(QueryError::Compile(format!(
1728            "unknown {kind_name} property '{prop}' in RETURN projection. \
1729             Valid: {}",
1730            valid.join(", ")
1731        )))
1732    }
1733}
1734
1735fn where_property_columns(kind: &VarKind) -> (&'static [&'static str], &'static str) {
1736    match kind {
1737        VarKind::Node => (NODE_WHERE_COLUMNS, "node"),
1738        VarKind::ObservationTargetNode => (OBSERVATION_TARGET_WHERE_COLUMNS, "observation target"),
1739        VarKind::EventNode => (EVENT_WHERE_COLUMNS, "event"),
1740        VarKind::Edge => (EDGE_WHERE_COLUMNS, "edge"),
1741        VarKind::SyntheticEdge => (EDGE_WHERE_COLUMNS, "edge"),
1742    }
1743}
1744
1745fn where_property_expression(
1746    property: &PropertyRef,
1747    kind: &VarKind,
1748    alias: &str,
1749    params: &mut Vec<QueryValue>,
1750) -> Result<String, QueryError> {
1751    let (valid, kind_name) = where_property_columns(kind);
1752    match property {
1753        PropertyRef::Field(field) if valid.contains(&field.as_str()) => {
1754            Ok(format!("{alias}.{field}"))
1755        }
1756        PropertyRef::Field(field) if field == "properties" => Err(QueryError::Compile(
1757            "'properties' is reserved as the JSON path root in WHERE clauses; \
1758             use 'properties.<path>'"
1759                .into(),
1760        )),
1761        PropertyRef::Field(field) => Err(QueryError::Compile(format!(
1762            "unknown {kind_name} field '{field}' in WHERE clause. Valid fields: {}; \
1763             JSON properties must use 'properties.<path>'",
1764            valid.join(", ")
1765        ))),
1766        PropertyRef::JsonPath(path) => {
1767            let (projection_columns, _) = property_columns(kind);
1768            if !projection_columns.contains(&"properties") {
1769                return Err(QueryError::Compile(format!(
1770                    "{kind_name} variables do not expose JSON properties in WHERE clauses. \
1771                     Valid fields: {}",
1772                    valid.join(", ")
1773                )));
1774            }
1775            json_path_expression(&format!("{alias}.properties"), path)
1776        }
1777        PropertyRef::MetadataPath(path) => {
1778            if *kind != VarKind::Edge {
1779                return Err(QueryError::Compile(
1780                    "metadata paths require a canonical edge binding".into(),
1781                ));
1782            }
1783            bound_json_path_expression(&format!("{alias}.metadata"), path, params)
1784        }
1785    }
1786}
1787
1788fn validated_json_path(path: &[String]) -> Result<String, QueryError> {
1789    if path.is_empty() {
1790        return Err(QueryError::Compile(
1791            "JSON property path cannot be empty; use a named path below the JSON container".into(),
1792        ));
1793    }
1794    if let Some(segment) = path.iter().find(|segment| {
1795        segment.is_empty() || !segment.chars().all(|ch| ch.is_alphanumeric() || ch == '_')
1796    }) {
1797        return Err(QueryError::Compile(format!(
1798            "invalid JSON property path segment '{segment}'; path segments must be identifiers"
1799        )));
1800    }
1801    Ok(format!("$.{}", path.join(".")))
1802}
1803
1804fn json_path_expression(column: &str, path: &[String]) -> Result<String, QueryError> {
1805    let path = validated_json_path(path)?;
1806    Ok(format!("json_extract({column}, '{path}')"))
1807}
1808
1809fn bound_json_path_expression(
1810    column: &str,
1811    path: &[String],
1812    params: &mut Vec<QueryValue>,
1813) -> Result<String, QueryError> {
1814    params.push(QueryValue::Text(validated_json_path(path)?));
1815    Ok(format!("json_extract({column}, ?{})", params.len()))
1816}
1817
1818fn order_property_expression(
1819    property: &PropertyRef,
1820    kind: &VarKind,
1821    alias: &str,
1822    params: &mut Vec<QueryValue>,
1823) -> Result<String, QueryError> {
1824    match property {
1825        PropertyRef::Field(field) => {
1826            if matches!(field.as_str(), "namespace" | "properties" | "metadata") {
1827                return Err(QueryError::Compile(
1828                    "ORDER BY requires a scalar field or explicit JSON path, not namespace or a JSON container".into(),
1829                ));
1830            }
1831            let column = property_to_column(field, kind)?;
1832            Ok(format!("{alias}.{column}"))
1833        }
1834        PropertyRef::JsonPath(path) => {
1835            let (columns, kind_name) = property_columns(kind);
1836            if !columns.contains(&"properties") {
1837                return Err(QueryError::Compile(format!(
1838                    "{kind_name} variables do not expose JSON properties in ORDER BY"
1839                )));
1840            }
1841            bound_json_path_expression(&format!("{alias}.properties"), path, params)
1842        }
1843        PropertyRef::MetadataPath(_) => where_property_expression(property, kind, alias, params),
1844    }
1845}
1846
1847fn order_direction(direction: OrderDirection) -> &'static str {
1848    match direction {
1849        OrderDirection::Asc => "ASC",
1850        OrderDirection::Desc => "DESC",
1851    }
1852}
1853
1854// Inline tests exercise private compiler phases without widening the public API.
1855#[cfg(test)]
1856#[path = "sql_tests.rs"]
1857mod tests;