Skip to main content

khive_query/compilers/
sql.rs

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