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` bounds rows after `SKIP` (ADR-008 Amendment 3).
159/// Limits at or below `max_limit` are terminal without a sentinel. Larger or absent
160/// limits request `max_limit + 1` rows so the runtime can detect another page.
161/// See `crates/khive-query/docs/api/sql-compilation.md` for lifecycle details.
162fn effective_limit(
163    requested_limit: Option<usize>,
164    max_limit: usize,
165) -> (usize, Option<TruncationCheck>) {
166    match requested_limit {
167        Some(limit) => {
168            if limit <= max_limit {
169                (limit, None)
170            } else {
171                (
172                    max_limit.saturating_add(1),
173                    Some(TruncationCheck {
174                        max_limit,
175                        requested_limit: Some(limit),
176                    }),
177                )
178            }
179        }
180        None => (
181            max_limit.saturating_add(1),
182            Some(TruncationCheck {
183                max_limit,
184                requested_limit: None,
185            }),
186        ),
187    }
188}
189
190/// Compiles `query` to parameterized, read-only SQL under `opts`.
191///
192/// The returned parameter vector is ordered to match the statement's `?N` placeholders.
193///
194/// # Errors
195///
196/// Returns [`QueryError`] when validation fails, a construct is unsupported,
197/// a projection cannot be lowered, or a bound limit/value is invalid.
198/// See `crates/khive-query/docs/api/sql-compilation.md` for compilation paths.
199pub fn compile(query: &GqlQuery, opts: &CompileOptions) -> Result<CompiledQuery, QueryError> {
200    compile_scoped(query, opts, None)
201}
202
203/// Compiles under a caller-derived message partition before matching and paging.
204///
205/// This is a pure lowering API, not an authorization or actor-resolution boundary.
206/// Namespace filtering and all other [`compile`] behavior remain unchanged.
207///
208/// # Errors
209///
210/// Returns the same validation and lowering errors as [`compile`].
211pub fn compile_with_mailbox(
212    query: &GqlQuery,
213    opts: &CompileOptions,
214    mailbox: &QueryMailboxScope,
215) -> Result<CompiledQuery, QueryError> {
216    compile_scoped(query, opts, Some(mailbox))
217}
218
219fn compile_scoped(
220    query: &GqlQuery,
221    opts: &CompileOptions,
222    mailbox: Option<&QueryMailboxScope>,
223) -> Result<CompiledQuery, QueryError> {
224    if query.pattern.elements.is_empty() {
225        return Err(QueryError::Compile("empty pattern".into()));
226    }
227
228    let mut query = query.clone();
229    let warnings = validate_with_warnings(&mut query)?;
230
231    let mut compiled = if query.pattern.has_variable_length() {
232        if query.pattern.edges().count() > 1 {
233            compile_mixed_length(&query, opts, mailbox)?
234        } else {
235            compile_variable_length(&query, opts, mailbox)?
236        }
237    } else {
238        compile_fixed_length(&query, opts, mailbox)?
239    };
240    compiled.warnings.extend(warnings);
241
242    // Fail closed if a future lowering path accidentally emits a mutation.
243    assert_select_only(&compiled.sql)?;
244
245    Ok(compiled)
246}
247
248/// Enforces the compiler's SELECT/WITH-only invariant; the reader is the security boundary.
249fn assert_select_only(sql: &str) -> Result<(), QueryError> {
250    let first = sql.split_whitespace().next().unwrap_or("").to_uppercase();
251    if first == "SELECT" || first == "WITH" {
252        return Ok(());
253    }
254    Err(read_only_error(QueryError::Compile))
255}
256
257fn in_placeholders(indices: impl IntoIterator<Item = usize>) -> String {
258    indices
259        .into_iter()
260        .map(|index| format!("?{index}"))
261        .collect::<Vec<_>>()
262        .join(", ")
263}
264
265fn namespace_filter(alias: &str, opts: &CompileOptions, params: &mut Vec<QueryValue>) -> String {
266    if opts.scopes.is_empty() {
267        String::new()
268    } else if opts.scopes.len() == 1 {
269        params.push(QueryValue::Text(opts.scopes[0].clone()));
270        format!(" AND {alias}.namespace = ?{}", params.len())
271    } else {
272        let placeholders = in_placeholders(opts.scopes.iter().map(|s| {
273            params.push(QueryValue::Text(s.clone()));
274            params.len()
275        }));
276        format!(" AND {alias}.namespace IN ({})", placeholders)
277    }
278}
279
280/// Maps substrate labels to the union discriminator and granular labels to `kind`.
281fn kind_filter_predicate(alias: &str, kind: &str, params: &mut Vec<QueryValue>) -> String {
282    match kind {
283        "entity" | "note" | "event" | "edge" => {
284            params.push(QueryValue::Text(kind.to_string()));
285            format!("{alias}.substrate_kind = ?{}", params.len())
286        }
287        _ => {
288            params.push(QueryValue::Text(kind.to_string()));
289            format!("{alias}.kind = ?{}", params.len())
290        }
291    }
292}
293
294/// Applies kind filtering to the entity/note observation-target union.
295fn observation_kind_filter_predicate(
296    alias: &str,
297    kind: &str,
298    params: &mut Vec<QueryValue>,
299) -> String {
300    match kind {
301        "entity" | "note" => {
302            params.push(QueryValue::Text(kind.to_string()));
303            format!("{alias}.referent_kind = ?{}", params.len())
304        }
305        _ => {
306            params.push(QueryValue::Text(kind.to_string()));
307            format!("{alias}.kind = ?{}", params.len())
308        }
309    }
310}
311
312/// Compiles typed inline-map equality against a direct or JSON property column.
313/// See `crates/khive-query/docs/api/sql-compilation.md` for storage-class rules.
314fn compile_property_equality(
315    alias: &str,
316    key: &str,
317    value: &ConditionValue,
318    text_column: Option<&str>,
319    params: &mut Vec<QueryValue>,
320) -> Result<String, QueryError> {
321    let is_string = matches!(value, ConditionValue::String(_));
322    match value {
323        ConditionValue::String(s) => params.push(QueryValue::Text(s.clone())),
324        ConditionValue::Integer(n) => params.push(QueryValue::Integer(*n)),
325        ConditionValue::Number(n) => {
326            if !n.is_finite() {
327                return Err(QueryError::InvalidInput(
328                    "non-finite float (NaN or Infinity) is not a valid query parameter".into(),
329                ));
330            }
331            params.push(QueryValue::Float(*n));
332        }
333        ConditionValue::Bool(b) => params.push(QueryValue::Integer(if *b { 1 } else { 0 })),
334        ConditionValue::List(_) | ConditionValue::Null => {
335            return Err(QueryError::Validation(
336                "list and null operands are not valid in inline property maps".into(),
337            ));
338        }
339    }
340    let collate = if is_string { " COLLATE NOCASE" } else { "" };
341    Ok(match text_column {
342        Some(col) => format!("{alias}.{col} = ?{}{collate}", params.len()),
343        None => format!(
344            "json_extract({alias}.properties, '$.{}') = ?{}{collate}",
345            key.replace('\'', "''"),
346            params.len()
347        ),
348    })
349}
350
351/// Returns `(source_indices, target_indices)` for synthetic `observed_as_*` edge endpoints.
352fn synthetic_endpoint_node_indices(
353    elements: &[PatternElement],
354) -> (
355    std::collections::HashSet<usize>,
356    std::collections::HashSet<usize>,
357) {
358    let mut source_set = std::collections::HashSet::new();
359    let mut target_set = std::collections::HashSet::new();
360    let mut node_idx = 0usize;
361    let mut prev_node_idx: Option<usize> = None;
362    for element in elements {
363        match element {
364            PatternElement::Node(_) => {
365                prev_node_idx = Some(node_idx);
366                node_idx += 1;
367            }
368            PatternElement::Edge(ep) => {
369                let has_synthetic = ep.relations.iter().any(|r| is_synthetic(r));
370                if has_synthetic {
371                    if let Some(src_idx) = prev_node_idx {
372                        source_set.insert(src_idx);
373                        // The target is the next node (current node_idx).
374                        target_set.insert(node_idx);
375                    }
376                }
377            }
378        }
379    }
380    (source_set, target_set)
381}
382
383struct NodeCorrelation {
384    id: String,
385    substrate: String,
386}
387
388/// One lowering context owns all block aliases and reuses the caller's bound scope.
389struct AbsenceContext<'a> {
390    opts: &'a CompileOptions,
391    mailbox: Option<&'a MailboxLowering>,
392    nodes: std::collections::HashMap<String, NodeCorrelation>,
393    next_block: usize,
394}
395
396impl<'a> AbsenceContext<'a> {
397    fn new(opts: &'a CompileOptions, mailbox: Option<&'a MailboxLowering>) -> Self {
398        Self {
399            opts,
400            mailbox,
401            nodes: std::collections::HashMap::new(),
402            next_block: 0,
403        }
404    }
405
406    fn bind_node(&mut self, variable: &str, alias: &str, substrate: String) {
407        self.nodes.insert(
408            variable.to_owned(),
409            NodeCorrelation {
410                id: format!("{alias}.id"),
411                substrate,
412            },
413        );
414    }
415
416    fn lower(
417        &mut self,
418        pattern: &MatchPattern,
419        params: &mut Vec<QueryValue>,
420    ) -> Result<String, QueryError> {
421        let block = self.next_block;
422        self.next_block += 1;
423        let mut sources = Vec::new();
424        let mut predicates = Vec::new();
425        let mut node_index = 0usize;
426        let mut edge_index = 0usize;
427        for element in &pattern.elements {
428            match element {
429                PatternElement::Node(node) => {
430                    let alias = format!("absence_{block}_n{node_index}");
431                    sources.push(primary_node_source(&alias, self.mailbox));
432                    predicates.push(format!("{alias}.deleted_at IS NULL"));
433                    let scope = namespace_filter(&alias, self.opts, params);
434                    if !scope.is_empty() {
435                        predicates.push(scope.trim_start_matches(" AND ").to_owned());
436                    }
437                    if let Some(kind) = &node.kind {
438                        predicates.push(kind_filter_predicate(&alias, kind, params));
439                    }
440                    if let Some(entity_type) = &node.entity_type {
441                        params.push(QueryValue::Text(entity_type.clone()));
442                        predicates.push(format!("{alias}.entity_type = ?{}", params.len()));
443                    }
444                    let mut properties: Vec<_> = node.properties.iter().collect();
445                    properties.sort_by_key(|(key, _)| key.as_str());
446                    for (key, value) in properties {
447                        let text_column = if key == "name" { Some("name") } else { None };
448                        predicates.push(compile_property_equality(
449                            &alias,
450                            key,
451                            value,
452                            text_column,
453                            params,
454                        )?);
455                    }
456                    if let Some(correlation) =
457                        node.variable.as_ref().and_then(|v| self.nodes.get(v))
458                    {
459                        predicates.push(format!("{alias}.id = {}", correlation.id));
460                        predicates.push(format!(
461                            "{alias}.substrate_kind = {}",
462                            correlation.substrate,
463                        ));
464                    }
465                    node_index += 1;
466                }
467                PatternElement::Edge(edge) => {
468                    let alias = format!("absence_{block}_e{edge_index}");
469                    let previous = format!("absence_{block}_n{}", node_index - 1);
470                    let next = format!("absence_{block}_n{node_index}");
471                    sources.push(edge_source(&alias, self.mailbox));
472                    predicates.push(format!("{alias}.deleted_at IS NULL"));
473                    let scope = namespace_filter(&alias, self.opts, params);
474                    if !scope.is_empty() {
475                        predicates.push(scope.trim_start_matches(" AND ").to_owned());
476                    }
477                    let outgoing = format!(
478                        "{alias}.source_id = {previous}.id AND {alias}.target_id = {next}.id"
479                    );
480                    let incoming = format!(
481                        "{alias}.target_id = {previous}.id AND {alias}.source_id = {next}.id"
482                    );
483                    predicates.push(match edge.direction {
484                        EdgeDirection::Out => outgoing,
485                        EdgeDirection::In => incoming,
486                        EdgeDirection::Both => format!("(({outgoing}) OR ({incoming}))"),
487                    });
488                    if edge.relations.len() == 1 {
489                        params.push(QueryValue::Text(edge.relations[0].clone()));
490                        predicates.push(format!("{alias}.relation = ?{}", params.len()));
491                    } else if !edge.relations.is_empty() {
492                        let placeholders = in_placeholders(edge.relations.iter().map(|relation| {
493                            params.push(QueryValue::Text(relation.clone()));
494                            params.len()
495                        }));
496                        predicates.push(format!("{alias}.relation IN ({placeholders})"));
497                    }
498                    edge_index += 1;
499                }
500            }
501        }
502        Ok(format!(
503            "NOT EXISTS (SELECT 1 FROM {} WHERE {})",
504            sources.join(", "),
505            predicates.join(" AND "),
506        ))
507    }
508}
509
510fn contains_not_exists(expr: &WhereExpr) -> bool {
511    match expr {
512        WhereExpr::NotExists(_) => true,
513        WhereExpr::And(left, right) | WhereExpr::Or(left, right) => {
514            contains_not_exists(left) || contains_not_exists(right)
515        }
516        WhereExpr::True | WhereExpr::Condition(_) => false,
517    }
518}
519
520fn absence_needs_final_start(
521    expr: &WhereExpr,
522    start_var: Option<&str>,
523    end_var: Option<&str>,
524) -> bool {
525    match expr {
526        WhereExpr::NotExists(pattern) => {
527            pattern.nodes().any(|node| {
528                node.variable
529                    .as_deref()
530                    .is_some_and(|v| Some(v) == start_var)
531            }) && pattern
532                .nodes()
533                .any(|node| node.variable.as_deref().is_some_and(|v| Some(v) == end_var))
534        }
535        WhereExpr::And(left, right) | WhereExpr::Or(left, right) => {
536            absence_needs_final_start(left, start_var, end_var)
537                || absence_needs_final_start(right, start_var, end_var)
538        }
539        WhereExpr::True | WhereExpr::Condition(_) => false,
540    }
541}
542
543/// Compiles fixed-length patterns to a JOIN chain.
544fn compile_fixed_length(
545    query: &GqlQuery,
546    opts: &CompileOptions,
547    mailbox: Option<&QueryMailboxScope>,
548) -> Result<CompiledQuery, QueryError> {
549    let mut params: Vec<QueryValue> = Vec::new();
550    let mailbox = mailbox.map(|scope| MailboxLowering::bind(scope, &mut params));
551    let mailbox = mailbox.as_ref();
552    let mut from_parts: Vec<String> = Vec::new();
553    let mut join_parts: Vec<String> = Vec::new();
554    let mut where_parts: Vec<String> = Vec::new();
555    let mut select_parts: Vec<String> = Vec::new();
556    let mut order_parts: Vec<String> = Vec::new();
557    let mut output_aliases = ProjectionAliases::default();
558    let has_counts = has_binding_counts(query);
559    let result_mode = query.distinct || has_counts;
560    let mut result_values = ResultValues::default();
561
562    let mut node_aliases: Vec<String> = Vec::new();
563    let mut var_to_alias: std::collections::HashMap<String, (String, VarKind)> =
564        std::collections::HashMap::new();
565
566    // Synthetic endpoints bind different substrate sources (issue #468).
567    let (event_source_indices, observation_target_indices) =
568        synthetic_endpoint_node_indices(&query.pattern.elements);
569
570    let mut node_idx = 0usize;
571    let mut edge_idx = 0usize;
572
573    for pattern in query.patterns() {
574        let path_start_node_idx = node_idx;
575        for element in &pattern.elements {
576            match element {
577                PatternElement::Node(np) => {
578                    let alias = format!("n{node_idx}");
579                    node_aliases.push(alias.clone());
580
581                    let is_event_source = event_source_indices.contains(&node_idx);
582                    let is_observation_target = observation_target_indices.contains(&node_idx);
583
584                    // Paging requires a total order over match identities, not caller
585                    // projections. UUIDs can overlap across unioned substrates, so keep
586                    // each union's discriminator ahead of its ID.
587                    if is_event_source {
588                        order_parts.push(format!("{alias}.id"));
589                    } else if is_observation_target {
590                        order_parts.push(format!("{alias}.referent_kind"));
591                        order_parts.push(format!("{alias}.id"));
592                    } else {
593                        order_parts.push(format!("{alias}.substrate_kind"));
594                        order_parts.push(format!("{alias}.id"));
595                    }
596
597                    if node_idx == 0 {
598                        if is_event_source {
599                            from_parts.push(format!("events {alias}"));
600                        } else if !is_observation_target {
601                            from_parts.push(primary_node_source(&alias, mailbox));
602                        }
603                    } else if node_idx == path_start_node_idx {
604                        // Validation permits only canonical comma components. Keep the
605                        // same filtered source on each root before the global identity join.
606                        join_parts.push(format!(
607                            "CROSS JOIN {}",
608                            primary_node_source(&alias, mailbox)
609                        ));
610                    }
611
612                    if is_event_source {
613                        // Events have no `deleted_at`; namespace is filtered directly.
614                        let ns_filter = namespace_filter(&alias, opts, &mut params);
615                        if !ns_filter.is_empty() {
616                            where_parts.push(ns_filter.trim_start_matches(" AND ").to_string());
617                        }
618                        // Bare `event` needs no kind predicate on an event-only source.
619                        if let Some(ref kind) = np.kind {
620                            if kind != "event" {
621                                params.push(QueryValue::Text(kind.clone()));
622                                where_parts.push(format!("{alias}.kind = ?{}", params.len()));
623                            }
624                        }
625                        if np.entity_type.is_some() {
626                            return Err(QueryError::Compile(
627                                "event nodes do not have an entity_type column".into(),
628                            ));
629                        }
630                        if !np.properties.is_empty() {
631                            return Err(QueryError::Compile(
632                                "event nodes do not support inline property filters; \
633                             use a WHERE clause on verb, outcome, or payload fields"
634                                    .into(),
635                            ));
636                        }
637                    } else if is_observation_target {
638                        where_parts.push(format!("{alias}.deleted_at IS NULL"));
639
640                        let ns_filter = namespace_filter(&alias, opts, &mut params);
641                        if !ns_filter.is_empty() {
642                            where_parts.push(ns_filter.trim_start_matches(" AND ").to_string());
643                        }
644
645                        if let Some(ref kind) = np.kind {
646                            where_parts.push(observation_kind_filter_predicate(
647                                &alias,
648                                kind,
649                                &mut params,
650                            ));
651                        }
652
653                        if let Some(ref et) = np.entity_type {
654                            params.push(QueryValue::Text(et.clone()));
655                            where_parts.push(format!("{alias}.entity_type = ?{}", params.len()));
656                        }
657
658                        let mut props: Vec<_> = np.properties.iter().collect();
659                        props.sort_by_key(|(k, _)| k.as_str());
660                        for (key, val) in props {
661                            let text_column = if key == "name" || key == "content" {
662                                Some(key.as_str())
663                            } else {
664                                None
665                            };
666                            where_parts.push(compile_property_equality(
667                                &alias,
668                                key,
669                                val,
670                                text_column,
671                                &mut params,
672                            )?);
673                        }
674                    } else {
675                        where_parts.push(format!("{alias}.deleted_at IS NULL"));
676
677                        let ns_filter = namespace_filter(&alias, opts, &mut params);
678                        if !ns_filter.is_empty() {
679                            where_parts.push(ns_filter.trim_start_matches(" AND ").to_string());
680                        }
681
682                        if let Some(ref kind) = np.kind {
683                            where_parts.push(kind_filter_predicate(&alias, kind, &mut params));
684                        }
685
686                        if let Some(ref et) = np.entity_type {
687                            params.push(QueryValue::Text(et.clone()));
688                            where_parts.push(format!("{alias}.entity_type = ?{}", params.len()));
689                        }
690
691                        let mut props: Vec<_> = np.properties.iter().collect();
692                        props.sort_by_key(|(k, _)| k.as_str());
693                        for (key, val) in props {
694                            let text_column = if key == "name" { Some("name") } else { None };
695                            where_parts.push(compile_property_equality(
696                                &alias,
697                                key,
698                                val,
699                                text_column,
700                                &mut params,
701                            )?);
702                        }
703                    }
704
705                    if let Some(ref var) = np.variable {
706                        let kind = if is_event_source {
707                            VarKind::EventNode
708                        } else if is_observation_target {
709                            VarKind::ObservationTargetNode
710                        } else {
711                            VarKind::Node
712                        };
713                        if let Some((first_alias, _)) = var_to_alias.get(var) {
714                            // A repeated name across paths denotes a typed record identity.
715                            // Retain the first alias for projections and every occurrence's filters.
716                            where_parts.push(format!(
717                                "{alias}.substrate_kind = {first_alias}.substrate_kind"
718                            ));
719                            where_parts.push(format!("{alias}.id = {first_alias}.id"));
720                        } else {
721                            var_to_alias.insert(var.clone(), (alias.clone(), kind));
722                        }
723                    }
724
725                    node_idx += 1;
726                }
727                PatternElement::Edge(ep) => {
728                    let e_alias = format!("e{edge_idx}");
729                    let prev_node = &node_aliases[node_aliases.len() - 1];
730                    let next_alias = format!("n{}", node_idx);
731
732                    // The synthetic and canonical backing tables have no meaningful shared join key.
733                    let has_synthetic = ep.relations.iter().any(|r| is_synthetic(r));
734                    let has_canonical = ep.relations.iter().any(|r| !is_synthetic(r));
735                    if has_synthetic && has_canonical {
736                        return Err(QueryError::Compile(
737                        "cannot mix synthetic observed_as_* relations with canonical edge relations \
738                         in a single edge pattern"
739                            .into(),
740                    ));
741                    }
742
743                    if has_synthetic {
744                        // `(event_id, role, position)` is the observation primary key.
745                        order_parts.push(format!("{e_alias}.event_id"));
746                        order_parts.push(format!("{e_alias}.role"));
747                        order_parts.push(format!("{e_alias}.position"));
748                        // Synthetic projections are always event-to-referent.
749                        if !matches!(ep.direction, EdgeDirection::Out) {
750                            return Err(QueryError::Compile(
751                            "synthetic observed_as_* edges are always event → entity (outbound only)".into(),
752                        ));
753                        }
754                        join_parts.push(format!(
755                        "JOIN event_observations {e_alias} ON {e_alias}.event_id = {prev_node}.id"
756                    ));
757                        let roles: Vec<&'static str> = ep
758                            .relations
759                            .iter()
760                            .filter_map(|r| synthetic_role(r))
761                            .collect();
762                        if roles.len() == 1 {
763                            params.push(QueryValue::Text(roles[0].to_string()));
764                            where_parts.push(format!("{e_alias}.role = ?{}", params.len()));
765                        } else if roles.len() > 1 {
766                            let placeholders = in_placeholders(roles.iter().map(|r| {
767                                params.push(QueryValue::Text(r.to_string()));
768                                params.len()
769                            }));
770                            where_parts.push(format!("{e_alias}.role IN ({})", placeholders));
771                        }
772                        // `referent_kind` prevents equal IDs on different substrates from colliding.
773                        join_parts.push(format!(
774                            "JOIN {} ON {next_alias}.id = {e_alias}.entity_id \
775                         AND {next_alias}.referent_kind = {e_alias}.referent_kind",
776                            observation_target_source(&next_alias, mailbox)
777                        ));
778                        // Enforce the ADR-041 role/substrate matrix.
779                        where_parts.push(format!(
780                        "(({e_alias}.role IN ('candidate', 'selected') AND {e_alias}.referent_kind IN ('entity', 'note')) \
781                          OR ({e_alias}.role = 'target' AND {e_alias}.referent_kind IN ('entity', 'note')) \
782                          OR ({e_alias}.role = 'signal' AND {e_alias}.referent_kind IN ('entity', 'note')))"
783                    ));
784                    } else {
785                        order_parts.push(format!("{e_alias}.id"));
786                        let (source_join, target_join) = match ep.direction {
787                        EdgeDirection::Out => (
788                            format!("{e_alias}.source_id = {prev_node}.id"),
789                            "target_id",
790                        ),
791                        EdgeDirection::In => (
792                            format!("{e_alias}.target_id = {prev_node}.id"),
793                            "source_id",
794                        ),
795                        EdgeDirection::Both => (
796                            format!(
797                                "({e_alias}.source_id = {prev_node}.id OR {e_alias}.target_id = {prev_node}.id)"
798                            ),
799                            "CASE_BOTH",
800                        ),
801                    };
802
803                        let next_join_col = if target_join == "CASE_BOTH" {
804                            format!(
805                            "CASE WHEN {e_alias}.source_id = {prev_node}.id THEN {e_alias}.target_id ELSE {e_alias}.source_id END"
806                        )
807                        } else {
808                            format!("{e_alias}.{target_join}")
809                        };
810
811                        join_parts.push(format!(
812                            "JOIN {} ON {source_join} AND {e_alias}.deleted_at IS NULL",
813                            edge_source(&e_alias, mailbox)
814                        ));
815
816                        let ens_filter = namespace_filter(&e_alias, opts, &mut params);
817                        if !ens_filter.is_empty() {
818                            where_parts.push(ens_filter.trim_start_matches(" AND ").to_string());
819                        }
820
821                        join_parts.push(format!(
822                            "JOIN {} ON {next_alias}.id = {next_join_col}",
823                            primary_node_source(&next_alias, mailbox)
824                        ));
825
826                        if !ep.relations.is_empty() {
827                            if ep.relations.len() == 1 {
828                                params.push(QueryValue::Text(ep.relations[0].clone()));
829                                where_parts.push(format!("{e_alias}.relation = ?{}", params.len()));
830                            } else {
831                                let placeholders = in_placeholders(ep.relations.iter().map(|r| {
832                                    params.push(QueryValue::Text(r.clone()));
833                                    params.len()
834                                }));
835                                where_parts
836                                    .push(format!("{e_alias}.relation IN ({})", placeholders));
837                            }
838                        }
839                    }
840
841                    if let Some(ref var) = ep.variable {
842                        let kind = if has_synthetic {
843                            VarKind::SyntheticEdge
844                        } else {
845                            VarKind::Edge
846                        };
847                        var_to_alias.insert(var.clone(), (e_alias.clone(), kind));
848                    }
849
850                    edge_idx += 1;
851                }
852            }
853        }
854    }
855
856    let mut absence = AbsenceContext::new(opts, mailbox);
857    for (variable, (alias, kind)) in &var_to_alias {
858        let substrate = match kind {
859            VarKind::Node => format!("{alias}.substrate_kind"),
860            VarKind::ObservationTargetNode => format!("{alias}.referent_kind"),
861            VarKind::EventNode => "'event'".to_owned(),
862            VarKind::Edge | VarKind::SyntheticEdge => continue,
863        };
864        absence.bind_node(variable, alias, substrate);
865    }
866    if let Some(where_sql) = compile_where_expr(
867        &query.where_clause,
868        &var_to_alias,
869        &mut params,
870        &mut absence,
871    )? {
872        where_parts.push(where_sql);
873    }
874
875    for item in &query.return_items {
876        let var = item.variable();
877        if let Some((alias, kind)) = var_to_alias.get(var) {
878            match item {
879                ReturnItem::Count(_) => {
880                    // Every fixed binding is mandatory, including synthetic edges
881                    // whose exposed id is NULL but whose observation row exists.
882                    let expression = "COUNT(1)".to_owned();
883                    let output_alias = output_aliases.register(
884                        format!("count_{var}"),
885                        ProjectionIdentity::Count(var.to_owned()),
886                    )?;
887                    select_parts.push(select_projection(&expression, &output_alias, result_mode));
888                    result_values.count(expression);
889                }
890                ReturnItem::Path(_, property) => {
891                    let (root, path) = return_path_parts(property)?;
892                    let expression = return_path_expression(
893                        property,
894                        kind,
895                        &format!("{alias}.{root}"),
896                        &mut params,
897                    )?;
898                    let output_alias = output_aliases.register(
899                        format!("{var}_{root}_{}", path.join("_")),
900                        ProjectionIdentity::Property(var.to_owned(), property.clone()),
901                    )?;
902                    select_parts.push(select_projection(&expression, &output_alias, result_mode));
903                    result_values.property(var, property.clone(), expression);
904                }
905                ReturnItem::Property(_, prop) => {
906                    let expression = scalar_property_expression(prop, kind, alias)?;
907                    let output_alias = output_aliases.register(
908                        format!("{var}_{prop}"),
909                        ProjectionIdentity::Property(
910                            var.to_owned(),
911                            PropertyRef::Field(prop.clone()),
912                        ),
913                    )?;
914                    select_parts.push(select_projection(&expression, &output_alias, result_mode));
915                    result_values.property(var, PropertyRef::Field(prop.clone()), expression);
916                }
917                ReturnItem::Variable(_) => {
918                    let columns: &[(&str, &str)] = match kind {
919                        VarKind::Node => &[
920                            ("id", "id"),
921                            ("namespace", "namespace"),
922                            ("kind", "kind"),
923                            ("entity_type", "entity_type"),
924                            ("name", "name"),
925                            ("properties", "properties"),
926                            ("created_at", "created_at"),
927                            ("updated_at", "updated_at"),
928                        ],
929                        VarKind::ObservationTargetNode => &[
930                            ("id", "id"),
931                            ("namespace", "namespace"),
932                            ("kind", "kind"),
933                            ("entity_type", "entity_type"),
934                            ("status", "status"),
935                            ("content", "content"),
936                            ("salience", "salience"),
937                            ("properties", "properties"),
938                            ("created_at", "created_at"),
939                            ("updated_at", "updated_at"),
940                            ("referent_kind", "referent_kind"),
941                        ],
942                        VarKind::EventNode => &[
943                            ("id", "id"),
944                            ("namespace", "namespace"),
945                            ("verb", "verb"),
946                            ("substrate", "substrate"),
947                            ("actor", "actor"),
948                            ("kind", "kind"),
949                            ("outcome", "outcome"),
950                            ("payload", "payload"),
951                            ("created_at", "created_at"),
952                        ],
953                        VarKind::Edge | VarKind::SyntheticEdge => &[
954                            ("id", "id"),
955                            ("source_id", "source"),
956                            ("target_id", "target"),
957                            ("relation", "relation"),
958                            ("weight", "weight"),
959                        ],
960                    };
961                    for (column, suffix) in columns {
962                        let output_alias = output_aliases.register(
963                            format!("{var}_{suffix}"),
964                            ProjectionIdentity::Property(
965                                var.to_owned(),
966                                PropertyRef::Field((*column).to_owned()),
967                            ),
968                        )?;
969                        let expression = format!("{alias}.{column}");
970                        select_parts.push(select_projection(
971                            &expression,
972                            &output_alias,
973                            result_mode,
974                        ));
975                        result_values.property(
976                            var,
977                            PropertyRef::Field((*column).to_owned()),
978                            expression,
979                        );
980                    }
981                }
982            }
983        } else {
984            return Err(QueryError::Compile(format!(
985                "unknown variable '{var}' in RETURN clause"
986            )));
987        }
988    }
989
990    if result_mode {
991        order_parts = result_values.order(&query.order_by, &mut params)?;
992    } else if !query.order_by.is_empty() {
993        let mut requested = Vec::new();
994        for item in &query.order_by {
995            let (alias, kind) = var_to_alias.get(&item.variable).ok_or_else(|| {
996                QueryError::Compile(format!("unknown variable '{}' in ORDER BY", item.variable))
997            })?;
998            let expression = order_property_expression(&item.property, kind, alias, &mut params)?;
999            requested.push(format!(
1000                "{expression} COLLATE BINARY {}",
1001                order_direction(item.direction)
1002            ));
1003        }
1004        requested.extend(order_parts);
1005        order_parts = requested;
1006    }
1007
1008    let offset_i64 = i64::try_from(query.offset)
1009        .map_err(|_| QueryError::InvalidInput("SKIP exceeds i64::MAX".into()))?;
1010    params.push(QueryValue::Integer(offset_i64));
1011    let offset_param = params.len();
1012
1013    let (limit, truncation_check) = effective_limit(query.limit, opts.max_limit);
1014    let limit_i64 = i64::try_from(limit)
1015        .map_err(|_| QueryError::InvalidInput("limit exceeds i64::MAX".into()))?;
1016    params.push(QueryValue::Integer(limit_i64));
1017    let limit_param = params.len();
1018
1019    let distinct = if query.distinct { "DISTINCT " } else { "" };
1020    let group_by = result_values.group_by(has_counts);
1021    let sql = format!(
1022        "SELECT {distinct}{} FROM {} {} WHERE {}{group_by} ORDER BY {} LIMIT ?{} OFFSET ?{}",
1023        select_parts.join(", "),
1024        from_parts.join(", "),
1025        join_parts.join(" "),
1026        where_parts.join(" AND "),
1027        order_parts.join(", "),
1028        limit_param,
1029        offset_param,
1030    );
1031
1032    Ok(CompiledQuery {
1033        sql,
1034        params,
1035        return_vars: query.return_items.clone(),
1036        warnings: Vec::new(),
1037        truncation_check,
1038    })
1039}
1040
1041/// Compiles a `WhereExpr` while preserving its boolean grouping.
1042fn compile_where_expr(
1043    expr: &WhereExpr,
1044    var_to_alias: &std::collections::HashMap<String, (String, VarKind)>,
1045    params: &mut Vec<QueryValue>,
1046    absence: &mut AbsenceContext<'_>,
1047) -> Result<Option<String>, QueryError> {
1048    match expr {
1049        WhereExpr::True => Ok(None),
1050        WhereExpr::NotExists(pattern) => absence.lower(pattern, params).map(Some),
1051        WhereExpr::Condition(cond) => {
1052            let sql = compile_single_condition(cond, var_to_alias, params)?;
1053            Ok(Some(sql))
1054        }
1055        WhereExpr::And(l, r) => {
1056            let ls = compile_where_expr(l, var_to_alias, params, absence)?;
1057            let rs = compile_where_expr(r, var_to_alias, params, absence)?;
1058            Ok(match (ls, rs) {
1059                (None, None) => None,
1060                (Some(s), None) | (None, Some(s)) => Some(s),
1061                (Some(l), Some(r)) => Some(format!("{l} AND {r}")),
1062            })
1063        }
1064        WhereExpr::Or(l, r) => {
1065            let ls = compile_where_expr(l, var_to_alias, params, absence)?;
1066            let rs = compile_where_expr(r, var_to_alias, params, absence)?;
1067            Ok(match (ls, rs) {
1068                (None, None) => None,
1069                (Some(s), None) => Some(format!("({s} OR 1)")),
1070                (None, Some(s)) => Some(format!("(1 OR {s})")),
1071                (Some(l), Some(r)) => Some(format!("({l} OR {r})")),
1072            })
1073        }
1074    }
1075}
1076
1077fn compile_single_condition(
1078    cond: &Condition,
1079    var_to_alias: &std::collections::HashMap<String, (String, VarKind)>,
1080    params: &mut Vec<QueryValue>,
1081) -> Result<String, QueryError> {
1082    let (alias, kind) = var_to_alias.get(&cond.variable).ok_or_else(|| {
1083        QueryError::Compile(format!(
1084            "unknown variable '{}' in WHERE clause",
1085            cond.variable
1086        ))
1087    })?;
1088
1089    let col_expr = where_property_expression(&cond.property, kind, alias, params)?;
1090
1091    compile_condition_predicate(&col_expr, cond, params)
1092}
1093
1094fn bind_condition_value(
1095    value: &ConditionValue,
1096    params: &mut Vec<QueryValue>,
1097) -> Result<usize, QueryError> {
1098    match value {
1099        ConditionValue::String(s) => params.push(QueryValue::Text(s.clone())),
1100        ConditionValue::Integer(n) => params.push(QueryValue::Integer(*n)),
1101        ConditionValue::Number(n) => {
1102            if !n.is_finite() {
1103                return Err(QueryError::InvalidInput(
1104                    "non-finite float (NaN or Infinity) is not a valid query parameter".into(),
1105                ));
1106            }
1107            params.push(QueryValue::Float(*n));
1108        }
1109        ConditionValue::Bool(b) => {
1110            params.push(QueryValue::Integer(if *b { 1 } else { 0 }));
1111        }
1112        ConditionValue::List(_) | ConditionValue::Null => {
1113            return Err(QueryError::Validation(
1114                "operator requires a scalar value".into(),
1115            ));
1116        }
1117    }
1118    Ok(params.len())
1119}
1120
1121fn compile_condition_predicate(
1122    col_expr: &str,
1123    cond: &Condition,
1124    params: &mut Vec<QueryValue>,
1125) -> Result<String, QueryError> {
1126    match cond.op {
1127        CompareOp::Contains | CompareOp::StartsWith => {
1128            let ConditionValue::String(value) = &cond.value else {
1129                return Err(QueryError::Validation(
1130                    "CONTAINS and STARTS WITH require a string literal".into(),
1131                ));
1132            };
1133            let escaped = khive_types::escape_like_literal(value);
1134            let pattern = if cond.op == CompareOp::Contains {
1135                format!("%{escaped}%")
1136            } else {
1137                format!("{escaped}%")
1138            };
1139            params.push(QueryValue::Text(pattern));
1140            Ok(format!(
1141                "{col_expr} LIKE ?{} COLLATE NOCASE ESCAPE '\\'",
1142                params.len()
1143            ))
1144        }
1145        CompareOp::In => {
1146            let ConditionValue::List(values) = &cond.value else {
1147                return Err(QueryError::Validation("IN requires a list literal".into()));
1148            };
1149            if values.is_empty() {
1150                return Ok("0".into());
1151            }
1152            let has_string = values
1153                .iter()
1154                .any(|value| matches!(value, ConditionValue::String(_)));
1155            let placeholders = values
1156                .iter()
1157                .map(|value| bind_condition_value(value, params))
1158                .collect::<Result<Vec<_>, _>>()?;
1159            let collate = if has_string { " COLLATE NOCASE" } else { "" };
1160            Ok(format!(
1161                "{col_expr}{collate} IN ({})",
1162                in_placeholders(placeholders)
1163            ))
1164        }
1165        CompareOp::IsNotNull => {
1166            if !matches!(cond.value, ConditionValue::Null) {
1167                return Err(QueryError::Validation(
1168                    "IS NOT NULL does not accept a value".into(),
1169                ));
1170            }
1171            Ok(format!("{col_expr} IS NOT NULL"))
1172        }
1173        CompareOp::IsNull => {
1174            if !matches!(cond.value, ConditionValue::Null) {
1175                return Err(QueryError::Validation(
1176                    "IS NULL does not accept a value".into(),
1177                ));
1178            }
1179            Ok(format!("{col_expr} IS NULL"))
1180        }
1181        op => {
1182            let op_str = match op {
1183                CompareOp::Eq => "=",
1184                CompareOp::Neq => "!=",
1185                CompareOp::Gt => ">",
1186                CompareOp::Lt => "<",
1187                CompareOp::Gte => ">=",
1188                CompareOp::Lte => "<=",
1189                CompareOp::Like => "LIKE",
1190                CompareOp::Contains
1191                | CompareOp::StartsWith
1192                | CompareOp::In
1193                | CompareOp::IsNotNull
1194                | CompareOp::IsNull => unreachable!(),
1195            };
1196            let is_string = matches!(cond.value, ConditionValue::String(_));
1197            let param_index = bind_condition_value(&cond.value, params)?;
1198            let collate = if is_string && matches!(op, CompareOp::Eq | CompareOp::Like) {
1199                " COLLATE NOCASE"
1200            } else {
1201                ""
1202            };
1203            Ok(format!("{col_expr} {op_str} ?{param_index}{collate}"))
1204        }
1205    }
1206}
1207
1208fn expr_endpoint_set(
1209    expr: &WhereExpr,
1210    start_var: Option<&str>,
1211    end_var: Option<&str>,
1212    edge_var: Option<&str>,
1213) -> (bool, bool) {
1214    match expr {
1215        WhereExpr::True => (false, false),
1216        WhereExpr::NotExists(pattern) => {
1217            let uses_end = pattern
1218                .nodes()
1219                .any(|node| node.variable.as_deref().is_some_and(|v| Some(v) == end_var));
1220            let uses_start = pattern.nodes().any(|node| {
1221                node.variable
1222                    .as_deref()
1223                    .is_some_and(|v| Some(v) == start_var)
1224            });
1225            // A mixed-correlated atom is indivisible and belongs to the final row.
1226            (uses_start && !uses_end, uses_end)
1227        }
1228        WhereExpr::Condition(c) => {
1229            let is_start = start_var == Some(c.variable.as_str());
1230            let is_end = end_var == Some(c.variable.as_str())
1231                || (edge_var == Some(c.variable.as_str())
1232                    && matches!(&c.property, PropertyRef::MetadataPath(_)));
1233            (is_start, is_end)
1234        }
1235        WhereExpr::And(l, r) | WhereExpr::Or(l, r) => {
1236            let (ls, le) = expr_endpoint_set(l, start_var, end_var, edge_var);
1237            let (rs, re) = expr_endpoint_set(r, start_var, end_var, edge_var);
1238            (ls || rs, le || re)
1239        }
1240    }
1241}
1242
1243/// Rejects OR subtrees spanning the start phase and final node/edge phase.
1244fn reject_or_spanning_endpoints(
1245    expr: &WhereExpr,
1246    start: &NodePattern,
1247    end: &NodePattern,
1248    edge: &EdgePattern,
1249) -> Result<(), QueryError> {
1250    let start_var = start.variable.as_deref();
1251    let end_var = end.variable.as_deref();
1252    let edge_var = edge.variable.as_deref();
1253    reject_or_spanning_impl(expr, start_var, end_var, edge_var)
1254}
1255
1256fn reject_or_spanning_impl(
1257    expr: &WhereExpr,
1258    start_var: Option<&str>,
1259    end_var: Option<&str>,
1260    edge_var: Option<&str>,
1261) -> Result<(), QueryError> {
1262    match expr {
1263        WhereExpr::True | WhereExpr::Condition(_) | WhereExpr::NotExists(_) => Ok(()),
1264        WhereExpr::And(l, r) => {
1265            reject_or_spanning_impl(l, start_var, end_var, edge_var)?;
1266            reject_or_spanning_impl(r, start_var, end_var, edge_var)
1267        }
1268        WhereExpr::Or(l, r) => {
1269            let (l_start, l_end) = expr_endpoint_set(l, start_var, end_var, edge_var);
1270            let (r_start, r_end) = expr_endpoint_set(r, start_var, end_var, edge_var);
1271            let spans_start = l_start || r_start;
1272            let spans_end = l_end || r_end;
1273            if spans_start && spans_end {
1274                return Err(QueryError::Unsupported(
1275                    "WHERE clauses that span the start and final row in a variable-length pattern \
1276                     are not yet supported; rewrite as separate queries or restrict each \
1277                     OR branch to one phase"
1278                        .into(),
1279                ));
1280            }
1281            reject_or_spanning_impl(l, start_var, end_var, edge_var)?;
1282            reject_or_spanning_impl(r, start_var, end_var, edge_var)
1283        }
1284    }
1285}
1286
1287fn compile_var_len_condition(
1288    cond: &Condition,
1289    start_var: Option<&str>,
1290    end_var: Option<&str>,
1291    edge_var: Option<&str>,
1292    params: &mut Vec<QueryValue>,
1293) -> Result<(String, &'static str), QueryError> {
1294    if edge_var == Some(cond.variable.as_str()) {
1295        if let PropertyRef::MetadataPath(path) = &cond.property {
1296            let expression = bound_json_path_expression("t.via_metadata", path, params)?;
1297            // Final-edge predicates share the final node's phase, not its SQL alias.
1298            return Ok((compile_condition_predicate(&expression, cond, params)?, "r"));
1299        }
1300    }
1301    let col_alias = if start_var == Some(cond.variable.as_str()) {
1302        "s"
1303    } else if end_var == Some(cond.variable.as_str()) {
1304        "r"
1305    } else {
1306        return Err(QueryError::Compile(format!(
1307            "variable '{}' in WHERE not supported in variable-length pattern \
1308             (only start/end node variables)",
1309            cond.variable
1310        )));
1311    };
1312
1313    let col_expr = where_property_expression(&cond.property, &VarKind::Node, col_alias, params)?;
1314
1315    let sql = compile_condition_predicate(&col_expr, cond, params)?;
1316    Ok((sql, col_alias))
1317}
1318
1319fn compile_variable_length_absence(
1320    pattern: &MatchPattern,
1321    end_var: Option<&str>,
1322    params: &mut Vec<QueryValue>,
1323    absence: &mut AbsenceContext<'_>,
1324) -> Result<(String, &'static str), QueryError> {
1325    let final_phase = pattern
1326        .nodes()
1327        .any(|node| node.variable.as_deref().is_some_and(|v| Some(v) == end_var));
1328    Ok((
1329        absence.lower(pattern, params)?,
1330        if final_phase { "r" } else { "s" },
1331    ))
1332}
1333
1334/// Routes variable-length predicates to the start or end CTE phase.
1335fn compile_variable_length_where(
1336    expr: &WhereExpr,
1337    (start_var, end_var, edge_var): (Option<&str>, Option<&str>, Option<&str>),
1338    params: &mut Vec<QueryValue>,
1339    start_conditions: &mut Vec<String>,
1340    end_conditions: &mut Vec<String>,
1341    absence: &mut AbsenceContext<'_>,
1342) -> Result<Option<String>, QueryError> {
1343    match expr {
1344        WhereExpr::True => Ok(None),
1345        WhereExpr::NotExists(pattern) => {
1346            let (sql, alias) = compile_variable_length_absence(pattern, end_var, params, absence)?;
1347            if alias == "s" {
1348                start_conditions.push(sql);
1349            } else {
1350                end_conditions.push(sql);
1351            }
1352            Ok(None)
1353        }
1354        WhereExpr::Condition(cond) => {
1355            let (sql, alias) =
1356                compile_var_len_condition(cond, start_var, end_var, edge_var, params)?;
1357            if alias == "s" {
1358                start_conditions.push(sql);
1359            } else {
1360                end_conditions.push(sql);
1361            }
1362            Ok(None)
1363        }
1364        WhereExpr::And(l, r) => {
1365            compile_variable_length_where(
1366                l,
1367                (start_var, end_var, edge_var),
1368                params,
1369                start_conditions,
1370                end_conditions,
1371                absence,
1372            )?;
1373            compile_variable_length_where(
1374                r,
1375                (start_var, end_var, edge_var),
1376                params,
1377                start_conditions,
1378                end_conditions,
1379                absence,
1380            )?;
1381            Ok(None)
1382        }
1383        WhereExpr::Or(l, r) => {
1384            // The prior spanning check guarantees both branches use one endpoint.
1385            let l_sql = compile_variable_length_where_to_sql(
1386                l, start_var, end_var, edge_var, params, absence,
1387            )?;
1388            let r_sql = compile_variable_length_where_to_sql(
1389                r, start_var, end_var, edge_var, params, absence,
1390            )?;
1391            match (l_sql, r_sql) {
1392                (None, None) => {}
1393                (Some((ls, la)), None) => {
1394                    let combined = format!("({ls} OR 1)");
1395                    if la == "s" {
1396                        start_conditions.push(combined);
1397                    } else {
1398                        end_conditions.push(combined);
1399                    }
1400                }
1401                (None, Some((rs, ra))) => {
1402                    let combined = format!("(1 OR {rs})");
1403                    if ra == "s" {
1404                        start_conditions.push(combined);
1405                    } else {
1406                        end_conditions.push(combined);
1407                    }
1408                }
1409                (Some((ls, la)), Some((rs, _ra))) => {
1410                    let combined = format!("({ls} OR {rs})");
1411                    if la == "s" {
1412                        start_conditions.push(combined);
1413                    } else {
1414                        end_conditions.push(combined);
1415                    }
1416                }
1417            }
1418            Ok(None)
1419        }
1420    }
1421}
1422
1423/// Compiles one endpoint-local expression and returns its CTE alias.
1424fn compile_variable_length_where_to_sql(
1425    expr: &WhereExpr,
1426    start_var: Option<&str>,
1427    end_var: Option<&str>,
1428    edge_var: Option<&str>,
1429    params: &mut Vec<QueryValue>,
1430    absence: &mut AbsenceContext<'_>,
1431) -> Result<Option<(String, &'static str)>, QueryError> {
1432    match expr {
1433        WhereExpr::True => Ok(None),
1434        WhereExpr::NotExists(pattern) => {
1435            compile_variable_length_absence(pattern, end_var, params, absence).map(Some)
1436        }
1437        WhereExpr::Condition(cond) => {
1438            let (sql, alias) =
1439                compile_var_len_condition(cond, start_var, end_var, edge_var, params)?;
1440            Ok(Some((sql, alias)))
1441        }
1442        WhereExpr::And(l, r) => {
1443            let ls = compile_variable_length_where_to_sql(
1444                l, start_var, end_var, edge_var, params, absence,
1445            )?;
1446            let rs = compile_variable_length_where_to_sql(
1447                r, start_var, end_var, edge_var, params, absence,
1448            )?;
1449            Ok(match (ls, rs) {
1450                (None, None) => None,
1451                (Some(s), None) | (None, Some(s)) => Some(s),
1452                (Some((lsql, la)), Some((rsql, _))) => Some((format!("{lsql} AND {rsql}"), la)),
1453            })
1454        }
1455        WhereExpr::Or(l, r) => {
1456            let ls = compile_variable_length_where_to_sql(
1457                l, start_var, end_var, edge_var, params, absence,
1458            )?;
1459            let rs = compile_variable_length_where_to_sql(
1460                r, start_var, end_var, edge_var, params, absence,
1461            )?;
1462            Ok(match (ls, rs) {
1463                (None, None) => None,
1464                (Some((sql, alias)), None) => Some((format!("({sql} OR 1)"), alias)),
1465                (None, Some((sql, alias))) => Some((format!("(1 OR {sql})"), alias)),
1466                (Some((lsql, la)), Some((rsql, _))) => Some((format!("({lsql} OR {rsql})"), la)),
1467            })
1468        }
1469    }
1470}
1471
1472fn mixed_node_conditions(
1473    node: &NodePattern,
1474    alias: &str,
1475    opts: &CompileOptions,
1476    params: &mut Vec<QueryValue>,
1477) -> Result<Vec<String>, QueryError> {
1478    let mut conditions = vec![format!("{alias}.deleted_at IS NULL")];
1479    let namespace = namespace_filter(alias, opts, params);
1480    if !namespace.is_empty() {
1481        conditions.push(namespace.trim_start_matches(" AND ").to_owned());
1482    }
1483    if let Some(kind) = &node.kind {
1484        conditions.push(kind_filter_predicate(alias, kind, params));
1485    }
1486    if let Some(entity_type) = &node.entity_type {
1487        params.push(QueryValue::Text(entity_type.clone()));
1488        conditions.push(format!("{alias}.entity_type = ?{}", params.len()));
1489    }
1490    let mut properties: Vec<_> = node.properties.iter().collect();
1491    properties.sort_by_key(|(key, _)| key.as_str());
1492    for (key, value) in properties {
1493        let text_column = if key == "name" { Some("name") } else { None };
1494        conditions.push(compile_property_equality(
1495            alias,
1496            key,
1497            value,
1498            text_column,
1499            params,
1500        )?);
1501    }
1502    Ok(conditions)
1503}
1504
1505fn mixed_segment_cte(
1506    index: usize,
1507    start: &NodePattern,
1508    edge: &EdgePattern,
1509    carries_metadata: bool,
1510    opts: &CompileOptions,
1511    mailbox: Option<&MailboxLowering>,
1512    params: &mut Vec<QueryValue>,
1513) -> Result<String, QueryError> {
1514    let start_conditions = mixed_node_conditions(start, "s", opts, params)?;
1515    let edge_namespace = namespace_filter("e", opts, params);
1516    let next_namespace = namespace_filter("next_node", opts, params);
1517    let relation = if edge.relations.is_empty() {
1518        String::new()
1519    } else {
1520        let placeholders = in_placeholders(edge.relations.iter().map(|relation| {
1521            params.push(QueryValue::Text(relation.clone()));
1522            params.len()
1523        }));
1524        format!(" AND e.relation IN ({placeholders})")
1525    };
1526    params.push(QueryValue::Integer(i64::try_from(edge.max_hops).map_err(
1527        |_| QueryError::InvalidInput("max_depth exceeds i64::MAX".into()),
1528    )?));
1529    let maximum = params.len();
1530    let (edge_join, next_id) = match edge.direction {
1531        EdgeDirection::Out => ("e.source_id = t.current_id", "e.target_id"),
1532        EdgeDirection::In => ("e.target_id = t.current_id", "e.source_id"),
1533        EdgeDirection::Both => (
1534            "(e.source_id = t.current_id OR e.target_id = t.current_id)",
1535            "CASE WHEN e.source_id = t.current_id THEN e.target_id ELSE e.source_id END",
1536        ),
1537    };
1538    let (metadata_column, metadata_seed, metadata_value) = if carries_metadata {
1539        (", metadata", ", NULL", ", e.metadata")
1540    } else {
1541        ("", "", "")
1542    };
1543    // Each segment owns its typed zero seed and visited path. UNION ALL keeps
1544    // distinct routes which later converge on the same final edge countable.
1545    Ok(format!(
1546        "segment_{index}(start_id, current_id, start_substrate, current_substrate, \
1547         depth, path, total_weight, id, relation, weight{metadata_column}) AS (\
1548             SELECT s.id, s.id, s.substrate_kind, s.substrate_kind, 0, s.id, 0.0, \
1549                    NULL, NULL, NULL{metadata_seed} \
1550             FROM primary_nodes s WHERE {start_where} \
1551             UNION ALL \
1552             SELECT t.start_id, {next_id}, t.start_substrate, next_node.substrate_kind, \
1553                    t.depth + 1, t.path || ',' || {next_id}, t.total_weight + e.weight, \
1554                    e.id, e.relation, e.weight{metadata_value} \
1555             FROM segment_{index} t CROSS JOIN {edges} \
1556                 ON {edge_join} AND e.deleted_at IS NULL{edge_namespace}{relation} \
1557             JOIN primary_nodes next_node ON next_node.id = ({next_id}) \
1558                 AND next_node.deleted_at IS NULL{next_namespace} \
1559             WHERE t.depth < ?{maximum} \
1560               AND (t.depth = 0 OR (',' || t.path || ',') NOT LIKE '%,' || {next_id} || ',%')\
1561         )",
1562        start_where = start_conditions.join(" AND "),
1563        edges = edge_source("e", mailbox),
1564    ))
1565}
1566
1567fn compile_mixed_length(
1568    query: &GqlQuery,
1569    opts: &CompileOptions,
1570    mailbox: Option<&QueryMailboxScope>,
1571) -> Result<CompiledQuery, QueryError> {
1572    let mut params = Vec::new();
1573    let mailbox = mailbox.map(|scope| MailboxLowering::bind(scope, &mut params));
1574    let mailbox = mailbox.as_ref();
1575    let nodes: Vec<_> = query.pattern.nodes().collect();
1576    let edges: Vec<_> = query.pattern.edges().collect();
1577    let mut ctes = vec![format!("primary_nodes AS ({})", primary_node_sql(mailbox))];
1578    let mut joins = Vec::new();
1579    let mut conditions = Vec::new();
1580    let mut var_to_alias = std::collections::HashMap::new();
1581    let mut variable_edges = std::collections::HashSet::new();
1582    let mut depths = Vec::new();
1583    let mut weights = Vec::new();
1584
1585    for (index, node) in nodes.iter().enumerate() {
1586        let alias = format!("n{index}");
1587        conditions.extend(mixed_node_conditions(node, &alias, opts, &mut params)?);
1588        if let Some(variable) = &node.variable {
1589            var_to_alias.insert(variable.clone(), (alias, VarKind::Node));
1590        }
1591    }
1592    for (index, edge) in edges.iter().enumerate() {
1593        let previous = format!("n{index}");
1594        let next = format!("n{}", index + 1);
1595        let alias = format!("e{index}");
1596        if edge.min_hops != 1 || edge.max_hops != 1 {
1597            let carries_metadata = edge.variable.as_deref().is_some_and(|variable| {
1598                query.return_items.iter().any(|item| {
1599                    matches!(item, ReturnItem::Property(var, field)
1600                        if var == variable && field == "metadata")
1601                        || matches!(item, ReturnItem::Path(var, PropertyRef::MetadataPath(_))
1602                            if var == variable)
1603                }) || query.where_clause.conditions().any(|condition| {
1604                    condition.variable == variable
1605                        && matches!(&condition.property, PropertyRef::MetadataPath(_))
1606                }) || query.order_by.iter().any(|item| {
1607                    item.variable == variable
1608                        && matches!(&item.property, PropertyRef::MetadataPath(_))
1609                })
1610            });
1611            ctes.push(mixed_segment_cte(
1612                index,
1613                nodes[index],
1614                edge,
1615                carries_metadata,
1616                opts,
1617                mailbox,
1618                &mut params,
1619            )?);
1620            joins.push(format!(
1621                "JOIN segment_{index} {alias} ON {alias}.start_id = {previous}.id \
1622                 AND {alias}.start_substrate = {previous}.substrate_kind"
1623            ));
1624            joins.push(format!(
1625                "JOIN primary_nodes {next} ON {next}.id = {alias}.current_id \
1626                 AND {next}.substrate_kind = {alias}.current_substrate"
1627            ));
1628            if edge.min_hops > 0 {
1629                params.push(QueryValue::Integer(i64::try_from(edge.min_hops).map_err(
1630                    |_| QueryError::InvalidInput("min_depth exceeds i64::MAX".into()),
1631                )?));
1632                conditions.push(format!("{alias}.depth >= ?{}", params.len()));
1633            }
1634            depths.push(format!("{alias}.depth"));
1635            weights.push(format!("{alias}.total_weight"));
1636            if let Some(variable) = &edge.variable {
1637                variable_edges.insert(variable.clone());
1638            }
1639        } else {
1640            let (edge_join, next_id) = match edge.direction {
1641                EdgeDirection::Out => (
1642                    format!("{alias}.source_id = {previous}.id"),
1643                    format!("{alias}.target_id"),
1644                ),
1645                EdgeDirection::In => (
1646                    format!("{alias}.target_id = {previous}.id"),
1647                    format!("{alias}.source_id"),
1648                ),
1649                EdgeDirection::Both => (
1650                    format!("({alias}.source_id = {previous}.id OR {alias}.target_id = {previous}.id)"),
1651                    format!("CASE WHEN {alias}.source_id = {previous}.id THEN {alias}.target_id ELSE {alias}.source_id END"),
1652                ),
1653            };
1654            let namespace = namespace_filter(&alias, opts, &mut params);
1655            joins.push(format!(
1656                "JOIN {} ON {edge_join} AND {alias}.deleted_at IS NULL{namespace}",
1657                edge_source(&alias, mailbox),
1658            ));
1659            joins.push(format!(
1660                "JOIN primary_nodes {next} ON {next}.id = ({next_id})"
1661            ));
1662            if !edge.relations.is_empty() {
1663                let placeholders = in_placeholders(edge.relations.iter().map(|relation| {
1664                    params.push(QueryValue::Text(relation.clone()));
1665                    params.len()
1666                }));
1667                conditions.push(format!("{alias}.relation IN ({placeholders})"));
1668            }
1669        }
1670        if let Some(variable) = &edge.variable {
1671            var_to_alias.insert(variable.clone(), (alias, VarKind::Edge));
1672        }
1673    }
1674
1675    for condition in query.where_clause.conditions() {
1676        if variable_edges.contains(&condition.variable)
1677            && !matches!(&condition.property, PropertyRef::MetadataPath(_))
1678        {
1679            return Err(QueryError::Compile(format!(
1680                "variable-length edge '{}' in WHERE supports only metadata.<path> predicates",
1681                condition.variable,
1682            )));
1683        }
1684    }
1685    let mut absence = AbsenceContext::new(opts, mailbox);
1686    for (variable, (alias, kind)) in &var_to_alias {
1687        if *kind == VarKind::Node {
1688            absence.bind_node(variable, alias, format!("{alias}.substrate_kind"));
1689        }
1690    }
1691    if let Some(predicate) = compile_where_expr(
1692        &query.where_clause,
1693        &var_to_alias,
1694        &mut params,
1695        &mut absence,
1696    )? {
1697        conditions.push(predicate);
1698    }
1699
1700    let has_counts = has_binding_counts(query);
1701    let result_mode = query.distinct || has_counts;
1702    let mut output_aliases = ProjectionAliases::default();
1703    let mut result_values = ResultValues::default();
1704    let mut selections = Vec::new();
1705    for item in &query.return_items {
1706        let variable = item.variable();
1707        let (alias, kind) = var_to_alias.get(variable).ok_or_else(|| {
1708            QueryError::Compile(format!("unknown variable '{variable}' in RETURN clause"))
1709        })?;
1710        let is_variable_edge = variable_edges.contains(variable);
1711        let projections: Vec<(String, PropertyRef, String)> = match item {
1712            ReturnItem::Count(_) => {
1713                let expression = if is_variable_edge {
1714                    format!("COUNT({alias}.id)")
1715                } else {
1716                    "COUNT(1)".to_owned()
1717                };
1718                let output = output_aliases.register(
1719                    format!("count_{variable}"),
1720                    ProjectionIdentity::Count(variable.to_owned()),
1721                )?;
1722                selections.push(select_projection(&expression, &output, result_mode));
1723                result_values.count(expression);
1724                continue;
1725            }
1726            ReturnItem::Path(_, property) => {
1727                let (root, path) = return_path_parts(property)?;
1728                let expression = return_path_expression(
1729                    property,
1730                    kind,
1731                    &format!("{alias}.{root}"),
1732                    &mut params,
1733                )?;
1734                vec![(
1735                    format!("{variable}_{root}_{}", path.join("_")),
1736                    property.clone(),
1737                    expression,
1738                )]
1739            }
1740            ReturnItem::Property(_, property) => {
1741                if is_variable_edge
1742                    && !["id", "relation", "weight", "metadata"].contains(&property.as_str())
1743                {
1744                    return Err(QueryError::Compile(format!(
1745                        "unknown variable-length edge property '{property}' in RETURN projection. Valid: id, relation, weight, metadata"
1746                    )));
1747                }
1748                let column = property_to_column(property, kind)?;
1749                vec![(
1750                    format!("{variable}_{property}"),
1751                    PropertyRef::Field(property.clone()),
1752                    format!("{alias}.{column}"),
1753                )]
1754            }
1755            ReturnItem::Variable(_) => {
1756                let columns: &[(&str, &str)] = if *kind == VarKind::Node {
1757                    &[
1758                        ("id", "id"),
1759                        ("namespace", "namespace"),
1760                        ("kind", "kind"),
1761                        ("entity_type", "entity_type"),
1762                        ("name", "name"),
1763                        ("properties", "properties"),
1764                        ("created_at", "created_at"),
1765                        ("updated_at", "updated_at"),
1766                    ]
1767                } else if is_variable_edge {
1768                    &[("id", "id"), ("relation", "relation"), ("weight", "weight")]
1769                } else {
1770                    &[
1771                        ("id", "id"),
1772                        ("source_id", "source"),
1773                        ("target_id", "target"),
1774                        ("relation", "relation"),
1775                        ("weight", "weight"),
1776                    ]
1777                };
1778                columns
1779                    .iter()
1780                    .map(|(column, suffix)| {
1781                        (
1782                            format!("{variable}_{suffix}"),
1783                            PropertyRef::Field((*column).to_owned()),
1784                            format!("{alias}.{column}"),
1785                        )
1786                    })
1787                    .collect()
1788            }
1789        };
1790        for (output, property, expression) in projections {
1791            let output = output_aliases.register(
1792                output,
1793                ProjectionIdentity::Property(variable.to_owned(), property.clone()),
1794            )?;
1795            selections.push(select_projection(&expression, &output, result_mode));
1796            result_values.property(variable, property, expression);
1797        }
1798    }
1799
1800    let group_by = result_values.group_by(has_counts);
1801    let mut order =
1802        result_values
1803            .order(&query.order_by, &mut params)
1804            .map_err(|error| match error {
1805                QueryError::Unsupported(_) if !result_mode => QueryError::Unsupported(
1806                    "mixed variable-length ORDER BY must be derived from RETURN projections".into(),
1807                ),
1808                other => other,
1809            })?;
1810    if !result_mode {
1811        let depth = format!("({})", depths.join(" + "));
1812        let weight = format!("({})", weights.join(" + "));
1813        let depth_alias = output_aliases.register(
1814            "_depth".into(),
1815            ProjectionIdentity::TraversalMetric("depth"),
1816        )?;
1817        let weight_alias = output_aliases.register(
1818            "_total_weight".into(),
1819            ProjectionIdentity::TraversalMetric("total_weight"),
1820        )?;
1821        selections.push(format!("{depth} AS {depth_alias}"));
1822        selections.push(format!("{weight} AS {weight_alias}"));
1823        // Requested values lead; metrics precede selected-value ties. Expression
1824        // ordering avoids SQLite's case-insensitive output-alias resolution.
1825        order.insert(query.order_by.len(), format!("{weight} DESC"));
1826        order.insert(query.order_by.len(), depth);
1827    }
1828    params.push(QueryValue::Integer(i64::try_from(query.offset).map_err(
1829        |_| QueryError::InvalidInput("SKIP exceeds i64::MAX".into()),
1830    )?));
1831    let offset = params.len();
1832    let (limit, truncation_check) = effective_limit(query.limit, opts.max_limit);
1833    params.push(QueryValue::Integer(i64::try_from(limit).map_err(|_| {
1834        QueryError::InvalidInput("limit exceeds i64::MAX".into())
1835    })?));
1836    let limit = params.len();
1837    let distinct = if query.distinct || !result_mode {
1838        "DISTINCT "
1839    } else {
1840        ""
1841    };
1842    let sql = format!(
1843        "WITH RECURSIVE {} SELECT {distinct}{} FROM primary_nodes n0 {} \
1844         WHERE {}{group_by} ORDER BY {} LIMIT ?{limit} OFFSET ?{offset}",
1845        ctes.join(", "),
1846        selections.join(", "),
1847        joins.join(" "),
1848        conditions.join(" AND "),
1849        order.join(", "),
1850    );
1851    Ok(CompiledQuery {
1852        sql,
1853        params,
1854        return_vars: query.return_items.clone(),
1855        warnings: Vec::new(),
1856        truncation_check,
1857    })
1858}
1859
1860/// Compiles one variable-length edge to a recursive CTE.
1861fn compile_variable_length(
1862    query: &GqlQuery,
1863    opts: &CompileOptions,
1864    mailbox: Option<&QueryMailboxScope>,
1865) -> Result<CompiledQuery, QueryError> {
1866    let mut params: Vec<QueryValue> = Vec::new();
1867    let mailbox = mailbox.map(|scope| MailboxLowering::bind(scope, &mut params));
1868    let mailbox = mailbox.as_ref();
1869    let mut var_to_alias: std::collections::HashMap<String, (String, VarKind)> =
1870        std::collections::HashMap::new();
1871
1872    // Reject extra elements rather than silently dropping part of the pattern.
1873    let nodes: Vec<&NodePattern> = query.pattern.nodes().collect();
1874    let edges: Vec<&EdgePattern> = query.pattern.edges().collect();
1875
1876    if nodes.len() != 2 || edges.len() != 1 || query.pattern.elements.len() != 3 {
1877        return Err(QueryError::Unsupported(
1878            "variable-length patterns must be a single start_node -[*N..M]-> end_node \
1879             (mixed fixed/variable chains are not yet implemented)"
1880                .into(),
1881        ));
1882    }
1883
1884    let start = &nodes[0];
1885    let edge = &edges[0];
1886    let end = &nodes[1];
1887
1888    // event_observations has no recursive path structure.
1889    if edge.relations.iter().any(|r| is_synthetic(r)) {
1890        return Err(QueryError::Unsupported(
1891            "synthetic observed_as_* edges cannot be variable-length; \
1892             use a fixed-length edge pattern instead"
1893                .into(),
1894        ));
1895    }
1896
1897    let carries_metadata = query.return_items.iter().any(|item| {
1898        matches!(item, ReturnItem::Property(var, field)
1899            if edge.variable.as_deref() == Some(var.as_str()) && field == "metadata")
1900            || matches!(item, ReturnItem::Path(var, PropertyRef::MetadataPath(_))
1901                if edge.variable.as_deref() == Some(var.as_str()))
1902    }) || query.where_clause.conditions().any(|condition| {
1903        edge.variable.as_deref() == Some(condition.variable.as_str())
1904            && matches!(&condition.property, PropertyRef::MetadataPath(_))
1905    }) || query.order_by.iter().any(|item| {
1906        edge.variable.as_deref() == Some(item.variable.as_str())
1907            && matches!(&item.property, PropertyRef::MetadataPath(_))
1908    });
1909    let metadata_column = if carries_metadata {
1910        ", via_metadata"
1911    } else {
1912        ""
1913    };
1914    let metadata_value = if carries_metadata { ", e.metadata" } else { "" };
1915
1916    let max_depth = edge.max_hops.min(MAX_DEPTH);
1917    let min_depth = edge.min_hops;
1918    let has_counts = has_binding_counts(query);
1919    let result_mode = query.distinct || has_counts;
1920    // Raw path counts cannot tolerate UUID-only endpoint rejoin fanout. The
1921    // typed seed also serves positive minima, filtered at the final projection.
1922    let typed_traversal = min_depth == 0 || result_mode || contains_not_exists(&query.where_clause);
1923
1924    let mut start_conditions: Vec<String> = vec!["s.deleted_at IS NULL".to_string()];
1925    let ns_filter = namespace_filter("s", opts, &mut params);
1926    if !ns_filter.is_empty() {
1927        start_conditions.push(ns_filter.trim_start_matches(" AND ").to_string());
1928    }
1929
1930    if let Some(ref kind) = start.kind {
1931        start_conditions.push(kind_filter_predicate("s", kind, &mut params));
1932    }
1933    if let Some(ref et) = start.entity_type {
1934        params.push(QueryValue::Text(et.clone()));
1935        start_conditions.push(format!("s.entity_type = ?{}", params.len()));
1936    }
1937    let mut start_props: Vec<_> = start.properties.iter().collect();
1938    start_props.sort_by_key(|(k, _)| k.as_str());
1939    for (key, val) in start_props {
1940        let text_column = if key == "name" { Some("name") } else { None };
1941        start_conditions.push(compile_property_equality(
1942            "s",
1943            key,
1944            val,
1945            text_column,
1946            &mut params,
1947        )?);
1948    }
1949
1950    let mut relation_condition = String::new();
1951    if !edge.relations.is_empty() {
1952        if edge.relations.len() == 1 {
1953            params.push(QueryValue::Text(edge.relations[0].clone()));
1954            relation_condition = format!(" AND e.relation = ?{}", params.len());
1955        } else {
1956            let placeholders = in_placeholders(edge.relations.iter().map(|r| {
1957                params.push(QueryValue::Text(r.clone()));
1958                params.len()
1959            }));
1960            relation_condition = format!(" AND e.relation IN ({})", placeholders);
1961        }
1962    }
1963
1964    let e_ns_filter = namespace_filter("e", opts, &mut params);
1965
1966    let (seed_join, seed_next, recurse_join, recurse_next) = match edge.direction {
1967        EdgeDirection::Out => (
1968            "e.source_id = s.id",
1969            "e.target_id",
1970            "e.source_id = t.current_id",
1971            "e.target_id",
1972        ),
1973        EdgeDirection::In => (
1974            "e.target_id = s.id",
1975            "e.source_id",
1976            "e.target_id = t.current_id",
1977            "e.source_id",
1978        ),
1979        EdgeDirection::Both => (
1980            "(e.source_id = s.id OR e.target_id = s.id)",
1981            "CASE WHEN e.source_id = s.id THEN e.target_id ELSE e.source_id END",
1982            "(e.source_id = t.current_id OR e.target_id = t.current_id)",
1983            "CASE WHEN e.source_id = t.current_id THEN e.target_id ELSE e.source_id END",
1984        ),
1985    };
1986
1987    // Filter every intermediate node so paths cannot cross deleted or out-of-scope rows.
1988    let next_node_ns_filter = namespace_filter("next_node", opts, &mut params);
1989
1990    let max_depth_i64 = i64::try_from(max_depth)
1991        .map_err(|_| QueryError::InvalidInput("max_depth exceeds i64::MAX".into()))?;
1992    params.push(QueryValue::Integer(max_depth_i64));
1993    let depth_param = params.len();
1994
1995    // End filters require `r` even when the end variable is not projected.
1996    let mut end_conditions: Vec<String> = vec!["r.deleted_at IS NULL".to_string()];
1997    let r_ns_filter = namespace_filter("r", opts, &mut params);
1998    if !r_ns_filter.is_empty() {
1999        end_conditions.push(r_ns_filter.trim_start_matches(" AND ").to_string());
2000    }
2001    if let Some(ref kind) = end.kind {
2002        end_conditions.push(kind_filter_predicate("r", kind, &mut params));
2003    }
2004    if let Some(ref et) = end.entity_type {
2005        params.push(QueryValue::Text(et.clone()));
2006        end_conditions.push(format!("r.entity_type = ?{}", params.len()));
2007    }
2008    let mut end_props: Vec<_> = end.properties.iter().collect();
2009    end_props.sort_by_key(|(k, _)| k.as_str());
2010    for (key, val) in end_props {
2011        let text_column = if key == "name" { Some("name") } else { None };
2012        end_conditions.push(compile_property_equality(
2013            "r",
2014            key,
2015            val,
2016            text_column,
2017            &mut params,
2018        )?);
2019    }
2020
2021    reject_or_spanning_endpoints(&query.where_clause, start, end, edge)?;
2022
2023    let mut absence = AbsenceContext::new(opts, mailbox);
2024    for (node, alias) in [(start, "s"), (end, "r")] {
2025        if let Some(variable) = &node.variable {
2026            absence.bind_node(variable, alias, format!("{alias}.substrate_kind"));
2027        }
2028    }
2029    if let Some(where_sql) = compile_variable_length_where(
2030        &query.where_clause,
2031        (
2032            start.variable.as_deref(),
2033            end.variable.as_deref(),
2034            edge.variable.as_deref(),
2035        ),
2036        &mut params,
2037        &mut start_conditions,
2038        &mut end_conditions,
2039        &mut absence,
2040    )? {
2041        // Keep the defensive fallback if a future expression has no endpoint binding.
2042        start_conditions.push(where_sql);
2043    }
2044
2045    if min_depth > 0 {
2046        let min_depth_i64 = i64::try_from(min_depth)
2047            .map_err(|_| QueryError::InvalidInput("min_depth exceeds i64::MAX".into()))?;
2048        params.push(QueryValue::Integer(min_depth_i64));
2049        end_conditions.push(format!("t.depth >= ?{}", params.len()));
2050    }
2051
2052    if let Some(ref var) = start.variable {
2053        var_to_alias.insert(var.clone(), ("s".to_string(), VarKind::Node));
2054    }
2055    if let Some(ref var) = end.variable {
2056        var_to_alias.insert(var.clone(), ("r".to_string(), VarKind::Node));
2057    }
2058    if let Some(ref var) = edge.variable {
2059        var_to_alias.insert(var.clone(), ("e".to_string(), VarKind::Edge));
2060    }
2061
2062    let mut select_parts: Vec<String> = Vec::new();
2063    let mut projection_aliases: Vec<String> = Vec::new();
2064    let mut projection_expressions: Vec<String> = Vec::new();
2065    let has_path_projections = query
2066        .return_items
2067        .iter()
2068        .any(|item| matches!(item, ReturnItem::Path(_, _)));
2069    let mut output_aliases = ProjectionAliases::default();
2070    let mut returned_paths: Vec<(String, PropertyRef, String)> = Vec::new();
2071    let mut returned_columns: std::collections::HashMap<(String, String), String> =
2072        std::collections::HashMap::new();
2073    let mut returned_expressions: std::collections::HashMap<(String, String), String> =
2074        std::collections::HashMap::new();
2075    let mut result_values = ResultValues::default();
2076    let mut has_start = absence_needs_final_start(
2077        &query.where_clause,
2078        start.variable.as_deref(),
2079        end.variable.as_deref(),
2080    );
2081
2082    for item in &query.return_items {
2083        let var = item.variable();
2084        if let Some((_, kind)) = var_to_alias.get(var) {
2085            match item {
2086                ReturnItem::Count(_) => {
2087                    let expression = if *kind == VarKind::Edge {
2088                        // Count occurrences, not distinct final-edge identities;
2089                        // the zero-hop path has no final edge and contributes zero.
2090                        "COUNT(t.via_edge)"
2091                    } else {
2092                        "COUNT(1)"
2093                    }
2094                    .to_owned();
2095                    let output_alias = output_aliases.register(
2096                        format!("count_{var}"),
2097                        ProjectionIdentity::Count(var.to_owned()),
2098                    )?;
2099                    select_parts.push(select_projection(&expression, &output_alias, result_mode));
2100                    result_values.count(expression);
2101                }
2102                ReturnItem::Path(_, property) => {
2103                    let (root, path) = return_path_parts(property)?;
2104                    let column = if *kind == VarKind::Node {
2105                        let table = if start.variable.as_deref() == Some(var) {
2106                            has_start = true;
2107                            "s"
2108                        } else {
2109                            "r"
2110                        };
2111                        format!("{table}.{root}")
2112                    } else {
2113                        "t.via_metadata".to_owned()
2114                    };
2115                    let expression = return_path_expression(property, kind, &column, &mut params)?;
2116                    let output_alias = output_aliases.register(
2117                        format!("{var}_{root}_{}", path.join("_")),
2118                        ProjectionIdentity::Property(var.to_owned(), property.clone()),
2119                    )?;
2120                    select_parts.push(select_projection(&expression, &output_alias, result_mode));
2121                    result_values.property(var, property.clone(), expression.clone());
2122                    returned_paths.push((var.to_owned(), property.clone(), expression.clone()));
2123                    projection_expressions.push(expression);
2124                    projection_aliases.push(output_alias);
2125                }
2126                ReturnItem::Property(_, prop) => {
2127                    let is_start = start.variable.as_deref() == Some(var);
2128                    if matches!(
2129                        kind,
2130                        VarKind::EventNode
2131                            | VarKind::ObservationTargetNode
2132                            | VarKind::SyntheticEdge
2133                    ) {
2134                        return Err(QueryError::Unsupported(
2135                            "synthetic observed_as_* edges cannot be used in variable-length \
2136                             patterns; use a fixed-length edge pattern instead"
2137                                .into(),
2138                        ));
2139                    }
2140                    if *kind == VarKind::Node {
2141                        let tbl = if is_start { "s" } else { "r" };
2142                        if is_start {
2143                            has_start = true;
2144                        }
2145                        let col = property_to_column(prop, kind)?;
2146                        let output_alias = output_aliases.register(
2147                            format!("{var}_{prop}"),
2148                            ProjectionIdentity::Property(
2149                                var.to_owned(),
2150                                PropertyRef::Field(prop.clone()),
2151                            ),
2152                        )?;
2153                        let expression = format!("{tbl}.{col}");
2154                        select_parts.push(select_projection(
2155                            &expression,
2156                            &output_alias,
2157                            result_mode,
2158                        ));
2159                        result_values.property(
2160                            var,
2161                            PropertyRef::Field(prop.clone()),
2162                            expression.clone(),
2163                        );
2164                        returned_expressions
2165                            .insert((var.to_owned(), prop.to_owned()), expression.clone());
2166                        projection_expressions.push(expression);
2167                        returned_columns
2168                            .insert((var.to_owned(), prop.to_owned()), output_alias.clone());
2169                        projection_aliases.push(output_alias);
2170                    } else {
2171                        let col = match prop.as_str() {
2172                            "id" => "via_edge",
2173                            "relation" => "via_relation",
2174                            "weight" => "via_weight",
2175                            "metadata" => "via_metadata",
2176                            _ => {
2177                                return Err(QueryError::Compile(format!(
2178                                    "unknown edge property '{prop}' in RETURN projection. \
2179                                     Valid: id, relation, weight, metadata"
2180                                )));
2181                            }
2182                        };
2183                        let output_alias = output_aliases.register(
2184                            format!("{var}_{prop}"),
2185                            ProjectionIdentity::Property(
2186                                var.to_owned(),
2187                                PropertyRef::Field(prop.clone()),
2188                            ),
2189                        )?;
2190                        let expression = format!("t.{col}");
2191                        select_parts.push(select_projection(
2192                            &expression,
2193                            &output_alias,
2194                            result_mode,
2195                        ));
2196                        result_values.property(
2197                            var,
2198                            PropertyRef::Field(prop.clone()),
2199                            expression.clone(),
2200                        );
2201                        returned_expressions
2202                            .insert((var.to_owned(), prop.to_owned()), expression.clone());
2203                        projection_expressions.push(expression);
2204                        returned_columns
2205                            .insert((var.to_owned(), prop.to_owned()), output_alias.clone());
2206                        projection_aliases.push(output_alias);
2207                    }
2208                }
2209                ReturnItem::Variable(_) => match kind {
2210                    VarKind::Node => {
2211                        let tbl = if start.variable.as_deref() == Some(var) {
2212                            has_start = true;
2213                            "s"
2214                        } else {
2215                            "r"
2216                        };
2217                        for column in [
2218                            "id",
2219                            "namespace",
2220                            "kind",
2221                            "entity_type",
2222                            "name",
2223                            "properties",
2224                            "created_at",
2225                            "updated_at",
2226                        ] {
2227                            let output_alias = output_aliases.register(
2228                                format!("{var}_{column}"),
2229                                ProjectionIdentity::Property(
2230                                    var.to_owned(),
2231                                    PropertyRef::Field(column.to_owned()),
2232                                ),
2233                            )?;
2234                            let expression = format!("{tbl}.{column}");
2235                            select_parts.push(select_projection(
2236                                &expression,
2237                                &output_alias,
2238                                result_mode,
2239                            ));
2240                            result_values.property(
2241                                var,
2242                                PropertyRef::Field(column.to_owned()),
2243                                expression.clone(),
2244                            );
2245                            returned_expressions
2246                                .insert((var.to_owned(), column.to_owned()), expression.clone());
2247                            projection_expressions.push(expression);
2248                            returned_columns
2249                                .insert((var.to_owned(), column.to_owned()), output_alias.clone());
2250                            projection_aliases.push(output_alias);
2251                        }
2252                    }
2253                    VarKind::EventNode
2254                    | VarKind::ObservationTargetNode
2255                    | VarKind::SyntheticEdge => {
2256                        return Err(QueryError::Unsupported(
2257                            "synthetic observed_as_* edges cannot be used in variable-length \
2258                             patterns; use a fixed-length edge pattern instead"
2259                                .into(),
2260                        ));
2261                    }
2262                    VarKind::Edge => {
2263                        for (column, suffix) in [
2264                            ("via_edge", "id"),
2265                            ("via_relation", "relation"),
2266                            ("via_weight", "weight"),
2267                        ] {
2268                            let output_alias = output_aliases.register(
2269                                format!("{var}_{suffix}"),
2270                                ProjectionIdentity::Property(
2271                                    var.to_owned(),
2272                                    PropertyRef::Field(suffix.to_owned()),
2273                                ),
2274                            )?;
2275                            let expression = format!("t.{column}");
2276                            select_parts.push(select_projection(
2277                                &expression,
2278                                &output_alias,
2279                                result_mode,
2280                            ));
2281                            result_values.property(
2282                                var,
2283                                PropertyRef::Field(suffix.to_owned()),
2284                                expression.clone(),
2285                            );
2286                            returned_expressions
2287                                .insert((var.to_owned(), suffix.to_owned()), expression.clone());
2288                            projection_expressions.push(expression);
2289                            returned_columns
2290                                .insert((var.to_owned(), suffix.to_owned()), output_alias.clone());
2291                            projection_aliases.push(output_alias);
2292                        }
2293                    }
2294                },
2295            }
2296        } else {
2297            return Err(QueryError::Compile(format!(
2298                "unknown variable '{var}' in RETURN clause"
2299            )));
2300        }
2301    }
2302
2303    let group_by = result_values.group_by(has_counts);
2304    let result_order = if result_mode {
2305        result_values
2306            .order(&query.order_by, &mut params)?
2307            .join(", ")
2308    } else {
2309        let depth_alias = output_aliases.register(
2310            "_depth".into(),
2311            ProjectionIdentity::TraversalMetric("depth"),
2312        )?;
2313        let total_weight_alias = output_aliases.register(
2314            "_total_weight".into(),
2315            ProjectionIdentity::TraversalMetric("total_weight"),
2316        )?;
2317        select_parts.push(format!("t.depth AS {depth_alias}"));
2318        select_parts.push(format!("t.total_weight AS {total_weight_alias}"));
2319
2320        // This path uses SELECT DISTINCT, so ordering by hidden path identities can
2321        // pick an arbitrary representative for collapsed rows. Ordering by every
2322        // projected alias instead gives a total order over the rows that survive
2323        // DISTINCT while retaining depth/weight as the leading traversal order.
2324        // Keep aliases as data: public AST names may contain SQL delimiters.
2325        projection_aliases.retain(|alias| alias != &depth_alias && alias != &total_weight_alias);
2326        // SQLite resolves output aliases without ASCII case sensitivity. New path
2327        // queries may have distinct case-preserved labels, so order by their actual
2328        // selected expressions. Keep legacy SQL unchanged when no path is returned.
2329        let projection_order = if has_path_projections {
2330            projection_expressions.join(", ")
2331        } else {
2332            projection_aliases.join(", ")
2333        };
2334        let (depth_order, total_weight_order) = if has_path_projections {
2335            ("t.depth", "t.total_weight")
2336        } else {
2337            (depth_alias.as_str(), total_weight_alias.as_str())
2338        };
2339        let projection_order_suffix = if projection_order.is_empty() {
2340            String::new()
2341        } else {
2342            format!(", {projection_order}")
2343        };
2344
2345        let mut requested_order = Vec::new();
2346        for item in &query.order_by {
2347            if let Some((_, _, projected)) =
2348                returned_paths.iter().find(|(variable, property, _)| {
2349                    variable == &item.variable && property == &item.property
2350                })
2351            {
2352                requested_order.push(format!(
2353                    "{projected} COLLATE BINARY {}",
2354                    order_direction(item.direction)
2355                ));
2356                continue;
2357            }
2358            let root = match &item.property {
2359                PropertyRef::Field(field) => field.as_str(),
2360                PropertyRef::JsonPath(_) => "properties",
2361                PropertyRef::MetadataPath(_) => "metadata",
2362            };
2363            let returned_values = if has_path_projections {
2364                &returned_expressions
2365            } else {
2366                &returned_columns
2367            };
2368            let projected = returned_values
2369                .get(&(item.variable.clone(), root.to_owned()))
2370                .ok_or_else(|| {
2371                    QueryError::Unsupported(
2372                        "variable-length ORDER BY must be derived from RETURN projections".into(),
2373                    )
2374                })?;
2375            let expression = match &item.property {
2376                PropertyRef::Field(_) => projected.clone(),
2377                PropertyRef::JsonPath(path) | PropertyRef::MetadataPath(path) => {
2378                    bound_json_path_expression(projected, path, &mut params)?
2379                }
2380            };
2381            requested_order.push(format!(
2382                "{expression} COLLATE BINARY {}",
2383                order_direction(item.direction)
2384            ));
2385        }
2386        let requested_order = if requested_order.is_empty() {
2387            String::new()
2388        } else {
2389            format!("{}, ", requested_order.join(", "))
2390        };
2391
2392        format!(
2393            "{requested_order}{depth_order}, {total_weight_order} DESC{projection_order_suffix}"
2394        )
2395    };
2396    let distinct = if query.distinct || !result_mode {
2397        "DISTINCT "
2398    } else {
2399        ""
2400    };
2401
2402    let offset_i64 = i64::try_from(query.offset)
2403        .map_err(|_| QueryError::InvalidInput("SKIP exceeds i64::MAX".into()))?;
2404    params.push(QueryValue::Integer(offset_i64));
2405    let offset_param = params.len();
2406
2407    let (limit, truncation_check) = effective_limit(query.limit, opts.max_limit);
2408    let limit_i64 = i64::try_from(limit)
2409        .map_err(|_| QueryError::InvalidInput("limit exceeds i64::MAX".into()))?;
2410    params.push(QueryValue::Integer(limit_i64));
2411    let limit_param = params.len();
2412
2413    // `r` is required by end filters; `s` is needed only for a start projection.
2414    let join_start = if has_start {
2415        if typed_traversal {
2416            "JOIN primary_nodes s ON s.id = t.start_id AND s.substrate_kind = t.start_substrate"
2417        } else {
2418            "JOIN primary_nodes s ON s.id = t.start_id"
2419        }
2420    } else {
2421        ""
2422    };
2423    let join_end = if typed_traversal {
2424        "JOIN primary_nodes r ON r.id = t.current_id AND r.substrate_kind = t.current_substrate"
2425    } else {
2426        "JOIN primary_nodes r ON r.id = t.current_id"
2427    };
2428
2429    let next_node_ns_and = if next_node_ns_filter.is_empty() {
2430        String::new()
2431    } else {
2432        format!(" AND {}", next_node_ns_filter.trim_start_matches(" AND "))
2433    };
2434
2435    let sql = if typed_traversal {
2436        // Seed from nodes, not incident edges: an isolated visible node is a
2437        // depth-zero path. Carry substrate identity so equal UUIDs in different
2438        // union members cannot become one another's zero-hop endpoint. The first
2439        // expansion may take a self-loop, as the positive-depth seed does;
2440        // subsequent expansions retain the visited-ID cycle guard.
2441        let metadata_null = if carries_metadata { ", NULL" } else { "" };
2442        format!(
2443            "WITH RECURSIVE primary_nodes AS ({primary_nodes}), \
2444             traverse(start_id, current_id, start_substrate, current_substrate, depth, path, total_weight, via_edge, via_relation, via_weight{metadata_column}) AS (\
2445                 SELECT s.id, s.id, s.substrate_kind, s.substrate_kind, 0, s.id, 0.0, \
2446                        NULL, NULL, NULL{metadata_null} \
2447                 FROM primary_nodes s \
2448                 WHERE {start_where} \
2449                 UNION ALL \
2450                 SELECT t.start_id, {recurse_next}, t.start_substrate, next_node.substrate_kind, \
2451                        t.depth + 1, t.path || ',' || {recurse_next}, \
2452                        t.total_weight + e.weight, \
2453                        e.id, e.relation, e.weight{metadata_value} \
2454                 FROM traverse t CROSS JOIN {edges} \
2455                     ON {recurse_join} AND e.deleted_at IS NULL{e_ns_filter}{relation_condition} \
2456                 JOIN primary_nodes next_node ON next_node.id = ({recurse_next}) \
2457                        AND next_node.deleted_at IS NULL{next_node_ns_and} \
2458                 WHERE t.depth < ?{depth_param} \
2459                   AND (t.depth = 0 OR (',' || t.path || ',') NOT LIKE '%,' || {recurse_next} || ',%') \
2460             ) \
2461             SELECT {distinct}{select_cols} \
2462             FROM traverse t \
2463             {join_start} {join_end} \
2464             WHERE {end_where}{group_by} \
2465             ORDER BY {result_order} \
2466             LIMIT ?{limit_param} OFFSET ?{offset_param}",
2467            primary_nodes = primary_node_sql(mailbox),
2468            edges = edge_source("e", mailbox),
2469            start_where = start_conditions.join(" AND "),
2470            select_cols = select_parts.join(", "),
2471            end_where = end_conditions.join(" AND "),
2472        )
2473    } else {
2474        // Retain the established positive-depth seed and parameter ordering.
2475        format!(
2476            "WITH RECURSIVE primary_nodes AS ({primary_nodes}), \
2477             traverse(start_id, current_id, depth, path, total_weight, via_edge, via_relation, via_weight{metadata_column}) AS (\
2478                 SELECT s.id, {seed_next}, 1, s.id || ',' || {seed_next}, e.weight, \
2479                        e.id, e.relation, e.weight{metadata_value} \
2480                 FROM primary_nodes s \
2481                 JOIN {edges} ON {seed_join} AND e.deleted_at IS NULL{e_ns_filter}{relation_condition} \
2482                 JOIN primary_nodes next_node ON next_node.id = ({seed_next}) \
2483                        AND next_node.deleted_at IS NULL{next_node_ns_and} \
2484                 WHERE {start_where} \
2485                 UNION ALL \
2486                 SELECT t.start_id, {recurse_next}, t.depth + 1, \
2487                        t.path || ',' || {recurse_next}, \
2488                        t.total_weight + e.weight, \
2489                        e.id, e.relation, e.weight{metadata_value} \
2490                 FROM traverse t CROSS JOIN {edges} \
2491                     ON {recurse_join} AND e.deleted_at IS NULL{e_ns_filter}{relation_condition} \
2492                 JOIN primary_nodes next_node ON next_node.id = ({recurse_next}) \
2493                        AND next_node.deleted_at IS NULL{next_node_ns_and} \
2494                 WHERE t.depth < ?{depth_param} \
2495                   AND (',' || t.path || ',') NOT LIKE '%,' || {recurse_next} || ',%' \
2496             ) \
2497             SELECT {distinct}{select_cols} \
2498             FROM traverse t \
2499             {join_start} {join_end} \
2500             WHERE {end_where}{group_by} \
2501             ORDER BY {result_order} \
2502             LIMIT ?{limit_param} OFFSET ?{offset_param}",
2503            primary_nodes = primary_node_sql(mailbox),
2504            edges = edge_source("e", mailbox),
2505            seed_next = seed_next,
2506            seed_join = seed_join,
2507            e_ns_filter = e_ns_filter,
2508            relation_condition = relation_condition,
2509            start_where = start_conditions.join(" AND "),
2510            recurse_next = recurse_next,
2511            recurse_join = recurse_join,
2512            next_node_ns_and = next_node_ns_and,
2513            depth_param = depth_param,
2514            select_cols = select_parts.join(", "),
2515            join_start = join_start,
2516            join_end = join_end,
2517            end_where = end_conditions.join(" AND "),
2518            result_order = result_order,
2519            limit_param = limit_param,
2520            offset_param = offset_param,
2521        )
2522    };
2523
2524    Ok(CompiledQuery {
2525        sql,
2526        params,
2527        return_vars: query.return_items.clone(),
2528        warnings: Vec::new(),
2529        truncation_check,
2530    })
2531}
2532
2533#[derive(Clone, Copy, PartialEq, Eq)]
2534enum VarKind {
2535    Node,
2536    /// Synthetic observation source backed by `events`.
2537    EventNode,
2538    /// Synthetic observation target backed by an entity/note referent.
2539    ObservationTargetNode,
2540    Edge,
2541    /// Observation edges have no canonical graph-edge metadata column.
2542    SyntheticEdge,
2543}
2544
2545const NODE_COLUMNS: &[&str] = &[
2546    "id",
2547    "name",
2548    "kind",
2549    "entity_type",
2550    "namespace",
2551    "description",
2552    "properties",
2553    "created_at",
2554    "updated_at",
2555];
2556/// Projection columns for entity/note observation targets.
2557const OBSERVATION_TARGET_COLUMNS: &[&str] = &[
2558    "id",
2559    "namespace",
2560    "kind",
2561    "entity_type",
2562    "status",
2563    "name",
2564    "content",
2565    "salience",
2566    "decay_factor",
2567    "properties",
2568    "created_at",
2569    "updated_at",
2570    "referent_kind",
2571];
2572/// Projection columns for synthetic event sources.
2573const EVENT_COLUMNS: &[&str] = &[
2574    "id",
2575    "namespace",
2576    "verb",
2577    "substrate",
2578    "actor",
2579    "kind",
2580    "outcome",
2581    "payload",
2582    "duration_us",
2583    "target_id",
2584    "session_id",
2585    "created_at",
2586];
2587const EDGE_COLUMNS: &[&str] = &[
2588    "id",
2589    "source_id",
2590    "target_id",
2591    "relation",
2592    "weight",
2593    "metadata",
2594];
2595const SYNTHETIC_EDGE_COLUMNS: &[&str] = &["id", "source_id", "target_id", "relation", "weight"];
2596const NODE_WHERE_COLUMNS: &[&str] = &[
2597    "id",
2598    "name",
2599    "kind",
2600    "entity_type",
2601    "description",
2602    "created_at",
2603    "updated_at",
2604];
2605const OBSERVATION_TARGET_WHERE_COLUMNS: &[&str] = &[
2606    "id",
2607    "kind",
2608    "entity_type",
2609    "status",
2610    "name",
2611    "content",
2612    "salience",
2613    "decay_factor",
2614    "created_at",
2615    "updated_at",
2616    "referent_kind",
2617];
2618const EVENT_WHERE_COLUMNS: &[&str] = &[
2619    "id",
2620    "verb",
2621    "substrate",
2622    "actor",
2623    "kind",
2624    "outcome",
2625    "payload",
2626    "duration_us",
2627    "target_id",
2628    "session_id",
2629    "created_at",
2630];
2631const EDGE_WHERE_COLUMNS: &[&str] = &["relation", "weight"];
2632
2633fn property_columns(kind: &VarKind) -> (&'static [&'static str], &'static str) {
2634    match kind {
2635        VarKind::Node => (NODE_COLUMNS, "node"),
2636        VarKind::ObservationTargetNode => (OBSERVATION_TARGET_COLUMNS, "observation target"),
2637        VarKind::EventNode => (EVENT_COLUMNS, "event"),
2638        VarKind::Edge => (EDGE_COLUMNS, "edge"),
2639        VarKind::SyntheticEdge => (SYNTHETIC_EDGE_COLUMNS, "edge"),
2640    }
2641}
2642
2643/// Semantic identity excludes parameter positions and generated output labels.
2644#[derive(PartialEq, Eq)]
2645enum ProjectionIdentity {
2646    Property(String, PropertyRef),
2647    Count(String),
2648    TraversalMetric(&'static str),
2649}
2650
2651fn has_binding_counts(query: &GqlQuery) -> bool {
2652    query
2653        .return_items
2654        .iter()
2655        .any(|item| matches!(item, ReturnItem::Count(_)))
2656}
2657
2658fn select_projection(expression: &str, alias: &str, result_mode: bool) -> String {
2659    if result_mode {
2660        // Explicit collation governs DISTINCT as well as grouping and ordering;
2661        // it does not cast numeric, null or JSON-extracted values to text.
2662        format!("{expression} COLLATE BINARY AS {alias}")
2663    } else {
2664        format!("{expression} AS {alias}")
2665    }
2666}
2667
2668/// Actual selected expressions for distinct/grouped results. Public labels are
2669/// never SQL lookup keys: SQLite resolves aliases without ASCII case sensitivity.
2670#[derive(Default)]
2671struct ResultValues {
2672    expressions: Vec<String>,
2673    properties: Vec<(String, PropertyRef, String)>,
2674}
2675
2676impl ResultValues {
2677    fn property(&mut self, variable: &str, property: PropertyRef, expression: String) {
2678        self.expressions.push(expression.clone());
2679        self.properties
2680            .push((variable.to_owned(), property, expression));
2681    }
2682
2683    fn count(&mut self, expression: String) {
2684        self.expressions.push(expression);
2685    }
2686
2687    fn group_by(&self, has_counts: bool) -> String {
2688        if !has_counts || self.properties.is_empty() {
2689            // A counts-only empty match set must still produce one zero row.
2690            return String::new();
2691        }
2692        let keys = self
2693            .properties
2694            .iter()
2695            .map(|(_, _, expression)| format!("{expression} COLLATE BINARY"))
2696            .collect::<Vec<_>>();
2697        format!(" GROUP BY {}", keys.join(", "))
2698    }
2699
2700    fn order(
2701        &self,
2702        items: &[OrderItem],
2703        params: &mut Vec<QueryValue>,
2704    ) -> Result<Vec<String>, QueryError> {
2705        let mut order = Vec::new();
2706        for item in items {
2707            let exact = self.properties.iter().find(|(variable, property, _)| {
2708                variable == &item.variable && property == &item.property
2709            });
2710            let expression = if let Some((_, _, expression)) = exact {
2711                expression.clone()
2712            } else {
2713                let (root, path) = match &item.property {
2714                    PropertyRef::JsonPath(path) => ("properties", path),
2715                    PropertyRef::MetadataPath(path) => ("metadata", path),
2716                    PropertyRef::Field(_) => {
2717                        return Err(QueryError::Unsupported(
2718                            "distinct/grouped ORDER BY must be derived from RETURN projections"
2719                                .into(),
2720                        ));
2721                    }
2722                };
2723                let container = self.properties.iter().find(|(variable, property, _)| {
2724                    variable == &item.variable && property == &PropertyRef::Field(root.to_owned())
2725                });
2726                let (_, _, expression) = container.ok_or_else(|| {
2727                    QueryError::Unsupported(
2728                        "distinct/grouped ORDER BY must be derived from RETURN projections".into(),
2729                    )
2730                })?;
2731                bound_json_path_expression(expression, path, params)?
2732            };
2733            order.push(format!(
2734                "{expression} COLLATE BINARY {}",
2735                order_direction(item.direction)
2736            ));
2737        }
2738        // Only returned values define ties; identities and traversal metrics
2739        // which were not requested cannot split groups or DISTINCT results.
2740        order.extend(
2741            self.expressions
2742                .iter()
2743                .map(|expression| format!("{expression} COLLATE BINARY")),
2744        );
2745        Ok(order)
2746    }
2747}
2748
2749#[derive(Default)]
2750struct ProjectionAliases {
2751    identities: std::collections::HashMap<String, ProjectionIdentity>,
2752}
2753
2754impl ProjectionAliases {
2755    fn register(
2756        &mut self,
2757        alias: String,
2758        identity: ProjectionIdentity,
2759    ) -> Result<String, QueryError> {
2760        if let Some(previous) = self.identities.get(&alias) {
2761            if previous != &identity {
2762                return Err(QueryError::Compile(format!(
2763                    "RETURN projection column name collision: '{alias}' denotes different expressions"
2764                )));
2765            }
2766        } else {
2767            self.identities.insert(alias.clone(), identity);
2768        }
2769        Ok(quote_identifier(&alias))
2770    }
2771}
2772
2773fn return_path_parts(property: &PropertyRef) -> Result<(&'static str, &[String]), QueryError> {
2774    match property {
2775        PropertyRef::JsonPath(path) => Ok(("properties", path)),
2776        PropertyRef::MetadataPath(path) => Ok(("metadata", path)),
2777        PropertyRef::Field(_) => Err(QueryError::Compile(
2778            "RETURN paths require a properties or metadata path".into(),
2779        )),
2780    }
2781}
2782
2783/// The caller supplies a compiler-owned column; the complete JSON path is bound.
2784fn return_path_expression(
2785    property: &PropertyRef,
2786    kind: &VarKind,
2787    column: &str,
2788    params: &mut Vec<QueryValue>,
2789) -> Result<String, QueryError> {
2790    let (_, path) = return_path_parts(property)?;
2791    match property {
2792        PropertyRef::JsonPath(_)
2793            if !matches!(kind, VarKind::Node | VarKind::ObservationTargetNode) =>
2794        {
2795            return Err(QueryError::Compile(
2796                "RETURN properties paths require a node binding with JSON properties".into(),
2797            ));
2798        }
2799        PropertyRef::MetadataPath(_) if *kind != VarKind::Edge => {
2800            return Err(QueryError::Compile(
2801                "metadata paths require a canonical edge binding".into(),
2802            ));
2803        }
2804        _ => {}
2805    }
2806    bound_json_path_expression(column, path, params)
2807}
2808
2809fn property_to_column<'a>(prop: &'a str, kind: &VarKind) -> Result<&'a str, QueryError> {
2810    let (valid, kind_name) = property_columns(kind);
2811    if valid.contains(&prop) {
2812        Ok(prop)
2813    } else {
2814        Err(QueryError::Compile(format!(
2815            "unknown {kind_name} property '{prop}' in RETURN projection. \
2816             Valid: {}",
2817            valid.join(", ")
2818        )))
2819    }
2820}
2821
2822fn scalar_property_expression(
2823    prop: &str,
2824    kind: &VarKind,
2825    alias: &str,
2826) -> Result<String, QueryError> {
2827    let column = property_to_column(prop, kind)?;
2828    if *kind == VarKind::SyntheticEdge && column == "id" {
2829        Ok("NULL".to_owned())
2830    } else {
2831        Ok(format!("{alias}.{column}"))
2832    }
2833}
2834
2835fn where_property_columns(kind: &VarKind) -> (&'static [&'static str], &'static str) {
2836    match kind {
2837        VarKind::Node => (NODE_WHERE_COLUMNS, "node"),
2838        VarKind::ObservationTargetNode => (OBSERVATION_TARGET_WHERE_COLUMNS, "observation target"),
2839        VarKind::EventNode => (EVENT_WHERE_COLUMNS, "event"),
2840        VarKind::Edge => (EDGE_WHERE_COLUMNS, "edge"),
2841        VarKind::SyntheticEdge => (EDGE_WHERE_COLUMNS, "edge"),
2842    }
2843}
2844
2845fn where_property_expression(
2846    property: &PropertyRef,
2847    kind: &VarKind,
2848    alias: &str,
2849    params: &mut Vec<QueryValue>,
2850) -> Result<String, QueryError> {
2851    let (valid, kind_name) = where_property_columns(kind);
2852    match property {
2853        PropertyRef::Field(field) if valid.contains(&field.as_str()) => {
2854            Ok(format!("{alias}.{field}"))
2855        }
2856        PropertyRef::Field(field) if field == "properties" => Err(QueryError::Compile(
2857            "'properties' is reserved as the JSON path root in WHERE clauses; \
2858             use 'properties.<path>'"
2859                .into(),
2860        )),
2861        PropertyRef::Field(field) => Err(QueryError::Compile(format!(
2862            "unknown {kind_name} field '{field}' in WHERE clause. Valid fields: {}; \
2863             JSON properties must use 'properties.<path>'",
2864            valid.join(", ")
2865        ))),
2866        PropertyRef::JsonPath(path) => {
2867            let (projection_columns, _) = property_columns(kind);
2868            if !projection_columns.contains(&"properties") {
2869                return Err(QueryError::Compile(format!(
2870                    "{kind_name} variables do not expose JSON properties in WHERE clauses. \
2871                     Valid fields: {}",
2872                    valid.join(", ")
2873                )));
2874            }
2875            json_path_expression(&format!("{alias}.properties"), path)
2876        }
2877        PropertyRef::MetadataPath(path) => {
2878            if *kind != VarKind::Edge {
2879                return Err(QueryError::Compile(
2880                    "metadata paths require a canonical edge binding".into(),
2881                ));
2882            }
2883            bound_json_path_expression(&format!("{alias}.metadata"), path, params)
2884        }
2885    }
2886}
2887
2888fn validated_json_path(path: &[String]) -> Result<String, QueryError> {
2889    if path.is_empty() {
2890        return Err(QueryError::Compile(
2891            "JSON property path cannot be empty; use a named path below the JSON container".into(),
2892        ));
2893    }
2894    if let Some(segment) = path.iter().find(|segment| {
2895        segment.is_empty() || !segment.chars().all(|ch| ch.is_alphanumeric() || ch == '_')
2896    }) {
2897        return Err(QueryError::Compile(format!(
2898            "invalid JSON property path segment '{segment}'; path segments must be identifiers"
2899        )));
2900    }
2901    Ok(format!("$.{}", path.join(".")))
2902}
2903
2904fn json_path_expression(column: &str, path: &[String]) -> Result<String, QueryError> {
2905    let path = validated_json_path(path)?;
2906    Ok(format!("json_extract({column}, '{path}')"))
2907}
2908
2909fn bound_json_path_expression(
2910    column: &str,
2911    path: &[String],
2912    params: &mut Vec<QueryValue>,
2913) -> Result<String, QueryError> {
2914    params.push(QueryValue::Text(validated_json_path(path)?));
2915    Ok(format!("json_extract({column}, ?{})", params.len()))
2916}
2917
2918fn order_property_expression(
2919    property: &PropertyRef,
2920    kind: &VarKind,
2921    alias: &str,
2922    params: &mut Vec<QueryValue>,
2923) -> Result<String, QueryError> {
2924    match property {
2925        PropertyRef::Field(field) => {
2926            if matches!(field.as_str(), "namespace" | "properties" | "metadata") {
2927                return Err(QueryError::Compile(
2928                    "ORDER BY requires a scalar field or explicit JSON path, not namespace or a JSON container".into(),
2929                ));
2930            }
2931            scalar_property_expression(field, kind, alias)
2932        }
2933        PropertyRef::JsonPath(path) => {
2934            let (columns, kind_name) = property_columns(kind);
2935            if !columns.contains(&"properties") {
2936                return Err(QueryError::Compile(format!(
2937                    "{kind_name} variables do not expose JSON properties in ORDER BY"
2938                )));
2939            }
2940            bound_json_path_expression(&format!("{alias}.properties"), path, params)
2941        }
2942        PropertyRef::MetadataPath(_) => where_property_expression(property, kind, alias, params),
2943    }
2944}
2945
2946fn order_direction(direction: OrderDirection) -> &'static str {
2947    match direction {
2948        OrderDirection::Asc => "ASC",
2949        OrderDirection::Desc => "DESC",
2950    }
2951}
2952
2953// Inline tests exercise private compiler phases without widening the public API.
2954#[cfg(test)]
2955#[path = "sql_tests.rs"]
2956mod tests;