fraiseql_cli/schema/

database_validator.rs

//! Compile-time database validation for schema definitions.
//!
//! Validates a compiled schema against a live database at three levels:
//! - **L1**: `sql_source` relation exists in the database
//! - **L2**: Columns and JSON column types match
//! - **L3**: JSONB keys exist in sampled rows (best-effort)
//!
//! Each finding carries a [`Severity`]: error-severity drift (a relation that
//! does not exist, a required field's key absent from every sampled row) fails
//! `compile --database` unless `--allow-drift` is passed (#384); warn-severity
//! findings are always advisory.

use std::{
    collections::{HashMap, HashSet},
    fmt,
};

use fraiseql_core::{
    db::{
        DatabaseType,
        introspector::{DatabaseIntrospector, RelationInfo},
    },
    schema::{
        CompiledSchema, FieldType, PaginationOrder, SourceKind, SourceProbe, sql_source_probes,
    },
};

use super::mutation_contract::Severity;

/// Report containing all database validation warnings and discovered metadata.
pub struct DatabaseValidationReport {
    /// All warnings emitted during validation.
    pub warnings:          Vec<DatabaseWarning>,
    /// Native columns discovered per query during L2 validation.
    ///
    /// Key: query name. Value: map of argument name → `PostgreSQL` type string
    /// (e.g. `"uuid"`, `"integer"`, `"text"`).
    ///
    /// Only contains entries for queries that have at least one direct argument
    /// with a matching native column on their `sql_source`.
    pub native_columns:    HashMap<String, HashMap<String, String>>,
    /// Cheaper page orderings discovered per query during L2 validation (#1303).
    ///
    /// Key: query name. Value: the ordering to replace the compiler's offline
    /// answer with.
    ///
    /// Only contains entries for queries whose `pagination_order` the compiler
    /// *derived* — an authored one is never overridden, and a query that declared
    /// `none` is never given one back. The offline derivation is already correct;
    /// this makes it cheap, which is why introspection may sharpen the answer and
    /// may never be required to produce one.
    pub pagination_orders: HashMap<String, PaginationOrder>,
}

/// A single database validation warning.
#[derive(Debug)]
pub enum DatabaseWarning {
    /// L1: `sql_source` relation does not exist.
    MissingRelation {
        /// Name of the query or mutation.
        query_name: String,
        /// The `sql_source` value that was not found.
        sql_source: String,
    },
    /// L1: a mutation's `sql_source` **function** does not exist.
    ///
    /// Distinct from [`MissingRelation`](Self::MissingRelation): a mutation's
    /// `sql_source` names a SQL *function* (the runtime calls
    /// `SELECT * FROM fn(...)`), not a view/table, so it is probed via `pg_proc`
    /// rather than the relation catalog.
    MissingFunction {
        /// Name of the mutation.
        mutation_name: String,
        /// The `sql_source` function that was not found.
        sql_source:    String,
    },
    /// L1: a `@fraiseql.type`'s declared `sql_source` view does not exist (#653).
    ///
    /// Most often an embedded value object the SDK gave a synthesized `v_<name>` source by
    /// naming convention (a view that was never authored). Advisory only — it never fails
    /// the compile; the hard `FRAISEQL_VALIDATE_SQL_SOURCES` gate deliberately does **not**
    /// probe type sources (it would drown in synthesized-source noise), so this warn-grade
    /// path under `--database` is the only place phantom type sources surface.
    MissingTypeSource {
        /// Name of the type.
        type_name:  String,
        /// The `sql_source` view that was not found.
        sql_source: String,
    },
    /// L1: `additional_view` does not exist.
    MissingAdditionalView {
        /// Name of the query.
        query_name: String,
        /// The view name that was not found.
        view_name:  String,
    },
    /// L2: `jsonb_column` does not exist on the relation.
    MissingJsonColumn {
        /// Name of the query.
        query_name:  String,
        /// The `sql_source` relation.
        sql_source:  String,
        /// The missing column name.
        column_name: String,
    },
    /// L2: `jsonb_column` exists but is not a JSON/JSONB type.
    WrongJsonColumnType {
        /// Name of the query.
        query_name:  String,
        /// The `sql_source` relation.
        sql_source:  String,
        /// The column name.
        column_name: String,
        /// The actual SQL data type.
        actual_type: String,
    },
    /// L2: an authored `pagination_order` column does not exist on the relation (#1303).
    ///
    /// The same shape as [`MissingCursorColumn`](Self::MissingCursorColumn) and
    /// error-grade for the same reason: the column is rendered into `ORDER BY`, so
    /// a name the relation does not carry is a query that fails at request time,
    /// on a route that compiled cleanly.
    MissingPaginationColumn {
        /// Name of the query.
        query_name:  String,
        /// The `sql_source` relation.
        sql_source:  String,
        /// The missing column name.
        column_name: String,
    },
    /// L2: no cheaper unique column was found, so pages stay ordered by
    /// `data->>'id'` (#1303).
    ///
    /// Advisory: the ordering is *correct*, and correct is the point — `id` is an
    /// enforced invariant (ADR-0017), so the extraction orders every type. It is
    /// simply the most expensive of the three known spellings, and the relation is
    /// the only place a cheaper one could have come from.
    PaginationIdentityFallback {
        /// Name of the query.
        query_name: String,
        /// The `sql_source` relation.
        sql_source: String,
        /// The `pk_<type>` column that would have been used had it existed.
        pk_column:  String,
    },
    /// L2: the view carries its own `ORDER BY` and the query did not declare
    /// `pagination_order = "none"` (#1303).
    ///
    /// The one case where ordering pages by the entity identity is a *regression*:
    /// a self-ordering view is the compiler's own documented remedy for
    /// `order_by = false`, and a default ordering replaces the author's order
    /// rather than adding to it. Advisory rather than error-grade because
    /// `pg_get_viewdef` cannot tell a top-level `ORDER BY` from one inside a
    /// subquery or window frame, where imposing a page order is entirely correct —
    /// so this reports a suspicion, and the author resolves it by declaring either
    /// way.
    SelfOrderingView {
        /// Name of the query.
        query_name: String,
        /// The `sql_source` view.
        sql_source: String,
    },
    /// L2: `relay_cursor_column` does not exist on the relation.
    MissingCursorColumn {
        /// Name of the query.
        query_name:  String,
        /// The `sql_source` relation.
        sql_source:  String,
        /// The missing cursor column name.
        column_name: String,
    },
    /// L3: a JSON key path is declared but not found in sampled data.
    MissingJsonKey {
        /// Name of the query.
        query_name:     String,
        /// The GraphQL type that declares the field (where to fix it).
        type_name:      String,
        /// The `sql_source` relation.
        sql_source:     String,
        /// The JSON column being sampled.
        json_column:    String,
        /// The GraphQL field name.
        field_name:     String,
        /// The snake_case key looked up in the JSON.
        json_key:       String,
        /// Whether the field is declared non-nullable. A required field's key
        /// cannot legitimately be absent from every sampled row, so this drives
        /// the finding's [`Severity`]: required → error, nullable → warning.
        field_required: bool,
    },
    /// L2: a direct query argument has no matching native column — will fall back to JSONB
    /// extraction.
    ///
    /// For best performance, consider adding a native column with the same name
    /// and an index on the `sql_source` table/view.
    NativeColumnFallback {
        /// Name of the query.
        query_name: String,
        /// The `sql_source` relation.
        sql_source: String,
        /// The argument name that has no matching native column.
        arg_name:   String,
    },
    /// L2: a direct query argument resolves to a native column whose SQL type cannot
    /// cleanly drive the predicate for the argument's GraphQL scalar type (e.g. an
    /// `Int` argument filtering a `uuid` column — `WHERE col = $N` errors or never
    /// matches).
    TypeConvertibility {
        /// Name of the query.
        query_name:   String,
        /// The `sql_source` relation.
        sql_source:   String,
        /// The argument name.
        arg_name:     String,
        /// The argument's declared GraphQL type (e.g. `Int`).
        graphql_type: String,
        /// The native column's SQL type (e.g. `uuid`).
        column_type:  String,
    },
}

impl DatabaseWarning {
    /// The severity of this finding under `compile --database` (#384).
    ///
    /// `Error`-severity drift fails the compile (unless `--allow-drift`):
    /// the declared schema names database objects that do not exist or cannot
    /// serve the declared shape, so the first query/mutation against them fails
    /// at runtime. `Warn`-severity findings are advisory:
    ///
    /// - [`MissingTypeSource`](Self::MissingTypeSource) is warn-grade by design (#653): synthesized
    ///   value-object sources would drown a hard gate.
    /// - [`MissingJsonKey`](Self::MissingJsonKey) is sampling-based; a *nullable* field may
    ///   legitimately be unset in every sampled row, so only a **required** field's absent key is
    ///   an error.
    /// - [`NativeColumnFallback`](Self::NativeColumnFallback) is a performance advisory — the JSONB
    ///   path is correct, just slower.
    #[must_use]
    pub const fn severity(&self) -> Severity {
        match self {
            Self::MissingRelation { .. }
            | Self::MissingFunction { .. }
            | Self::MissingAdditionalView { .. }
            | Self::MissingJsonColumn { .. }
            | Self::WrongJsonColumnType { .. }
            | Self::MissingCursorColumn { .. }
            | Self::MissingPaginationColumn { .. }
            | Self::TypeConvertibility { .. } => Severity::Error,
            Self::MissingJsonKey { field_required, .. } => {
                if *field_required {
                    Severity::Error
                } else {
                    Severity::Warn
                }
            },
            Self::MissingTypeSource { .. }
            | Self::NativeColumnFallback { .. }
            | Self::PaginationIdentityFallback { .. }
            | Self::SelfOrderingView { .. } => Severity::Warn,
        }
    }
}

impl fmt::Display for DatabaseWarning {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        match self {
            Self::MissingRelation {
                query_name,
                sql_source,
            } => {
                write!(
                    f,
                    "query `{query_name}`: sql_source `{sql_source}` does not exist in database"
                )
            },
            Self::MissingFunction {
                mutation_name,
                sql_source,
            } => {
                write!(
                    f,
                    "mutation `{mutation_name}`: sql_source function `{sql_source}` does not exist in database"
                )
            },
            Self::MissingTypeSource {
                type_name,
                sql_source,
            } => {
                write!(
                    f,
                    "type `{type_name}`: sql_source `{sql_source}` does not exist in the \
                     database — if `{type_name}` is an embedded value object it should declare \
                     no source (a value object has no independent identity; it is fetched via \
                     its parent)"
                )
            },
            Self::MissingAdditionalView {
                query_name,
                view_name,
            } => {
                write!(
                    f,
                    "query `{query_name}`: additional_view `{view_name}` does not exist in database"
                )
            },
            Self::MissingJsonColumn {
                query_name,
                sql_source,
                column_name,
            } => {
                write!(
                    f,
                    "query `{query_name}`: column `{column_name}` not found on `{sql_source}`"
                )
            },
            Self::WrongJsonColumnType {
                query_name,
                sql_source,
                column_name,
                actual_type,
            } => {
                write!(
                    f,
                    "query `{query_name}`: column `{column_name}` on `{sql_source}` is `{actual_type}`, expected json/jsonb"
                )
            },
            Self::MissingCursorColumn {
                query_name,
                sql_source,
                column_name,
            } => {
                write!(
                    f,
                    "query `{query_name}`: relay cursor column `{column_name}` not found on `{sql_source}`"
                )
            },
            Self::MissingPaginationColumn {
                query_name,
                sql_source,
                column_name,
            } => {
                write!(
                    f,
                    "query `{query_name}`: pagination_order column `{column_name}` not found on \
                     `{sql_source}` — it is rendered into ORDER BY, so this query fails at \
                     request time"
                )
            },
            Self::PaginationIdentityFallback {
                query_name,
                sql_source,
                pk_column,
            } => {
                write!(
                    f,
                    "query `{query_name}`: offset pages are ordered by `data->>'id'` because \
                     `{sql_source}` exposes neither `{pk_column}` nor a native `id` column. \
                     Correct, but the most expensive of the three orderings (measured 7.7x the \
                     unordered plan at 200k rows and a deep offset, against 1.7x for \
                     `{pk_column}`). Expose one of them, or declare pagination_order"
                )
            },
            Self::SelfOrderingView {
                query_name,
                sql_source,
            } => {
                write!(
                    f,
                    "query `{query_name}`: view `{sql_source}` carries its own ORDER BY, and \
                     this query does not declare pagination_order. An offset page will now be \
                     ordered by the entity identity instead, replacing the view's order. \
                     Declare pagination_order = \"none\" to keep it, or a column to confirm \
                     the new one"
                )
            },
            Self::MissingJsonKey {
                query_name,
                type_name,
                sql_source,
                json_column,
                field_name,
                json_key,
                field_required,
            } => {
                let requiredness = if *field_required {
                    "required"
                } else {
                    "nullable"
                };
                write!(
                    f,
                    "query `{query_name}`: {requiredness} field `{type_name}.{field_name}` (key `{json_key}`) not found in `{sql_source}.{json_column}` sample data"
                )
            },
            Self::NativeColumnFallback {
                query_name,
                sql_source,
                arg_name,
            } => {
                write!(
                    f,
                    "query `{query_name}`: argument `{arg_name}` will use JSONB extraction \
                     (`{sql_source}.data->>''{arg_name}''`) — no native column `{arg_name}` found on \
                     `{sql_source}`. Add a native column with an index for O(log n) lookup."
                )
            },
            Self::TypeConvertibility {
                query_name,
                sql_source,
                arg_name,
                graphql_type,
                column_type,
            } => {
                write!(
                    f,
                    "query `{query_name}`: argument `{arg_name}` is `{graphql_type}` but the native column \
                     `{sql_source}.{arg_name}` is `{column_type}` — the predicate may error or never match. \
                     Align the argument type with the column, or store the field in `data` for JSONB extraction."
                )
            },
        }
    }
}

/// Check if a SQL data type represents a JSON column for the given database.
pub(crate) fn is_json_type(data_type: &str, db_type: DatabaseType) -> bool {
    let lower = data_type.to_lowercase();
    match db_type {
        DatabaseType::PostgreSQL => lower == "jsonb" || lower == "json",
    }
}

/// Coarse SQL type families, used to flag argument↔column type mismatches.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum SqlFamily {
    /// Integer/decimal/float numeric types.
    Numeric,
    /// Boolean.
    Boolean,
    /// UUID / `uniqueidentifier`.
    Uuid,
    /// Character/string types.
    Text,
    /// Date/time/interval types.
    Temporal,
    /// `json` / `jsonb`.
    Json,
    /// Anything else (custom domains, enums, arrays, geometry, …) — never flagged.
    Other,
}

/// Classify a (any-dialect) SQL type string into a coarse [`SqlFamily`].
///
/// Strips length/precision and multi-word suffixes (`character varying`,
/// `timestamp with time zone`, `double precision`) down to the leading base word.
fn sql_type_family(sql_type: &str) -> SqlFamily {
    let lower = sql_type.trim().to_lowercase();
    let base = lower.split(['(', ' ', '[']).next().unwrap_or(lower.as_str());
    match base {
        "smallint" | "integer" | "int" | "int2" | "int4" | "int8" | "bigint" | "serial"
        | "bigserial" | "smallserial" | "numeric" | "decimal" | "real" | "double" | "float"
        | "float4" | "float8" | "money" | "mediumint" | "tinyint" => SqlFamily::Numeric,
        "boolean" | "bool" | "bit" => SqlFamily::Boolean,
        "uuid" | "uniqueidentifier" => SqlFamily::Uuid,
        "text" | "varchar" | "char" | "bpchar" | "name" | "citext" | "nvarchar" | "nchar"
        | "character" | "clob" | "string" => SqlFamily::Text,
        "date" | "time" | "timestamp" | "timestamptz" | "timetz" | "datetime" | "datetime2"
        | "interval" | "smalldatetime" => SqlFamily::Temporal,
        "json" | "jsonb" => SqlFamily::Json,
        _ => SqlFamily::Other,
    }
}

/// Whether a query argument's GraphQL type can cleanly drive an equality predicate
/// against a native column of `sql_type`.
///
/// Conservative by design: only the strongly-typed scalars (`Int`, `Float`,
/// `Decimal`, `Boolean`, `UUID`) can flag a mismatch, and only against a *known*
/// incompatible column family. Permissive scalars (`String`, `ID`, date/time, `JSON`,
/// custom scalars) and non-scalar references never warn — the runtime binds them as
/// text-coercible parameters, and `ID` intentionally spans uuid / integer / text keys.
fn arg_type_convertible(field_type: &FieldType, sql_type: &str) -> bool {
    // A list filter (`[ID!]`) drives the predicate with its element type.
    let base = match field_type {
        FieldType::List(inner) => inner.as_ref(),
        other => other,
    };
    let family = sql_type_family(sql_type);
    // Unknown column family → don't second-guess it.
    if family == SqlFamily::Other {
        return true;
    }
    match base {
        FieldType::Int | FieldType::Float | FieldType::Decimal => family == SqlFamily::Numeric,
        FieldType::Boolean => family == SqlFamily::Boolean,
        FieldType::Uuid => matches!(family, SqlFamily::Uuid | SqlFamily::Text),
        _ => true,
    }
}

/// Split a potentially schema-qualified name into (optional_schema, name).
fn split_schema_qualified(sql_source: &str) -> (Option<&str>, &str) {
    match sql_source.split_once('.') {
        Some((schema, table)) => (Some(schema), table),
        None => (None, sql_source),
    }
}

/// Check if a relation exists in the relation lookup maps.
fn relation_exists(
    schema_qualified: &HashMap<(String, String), RelationInfo>,
    unqualified: &HashMap<String, Vec<String>>,
    sql_source: &str,
) -> bool {
    let (schema, name) = split_schema_qualified(sql_source);
    if let Some(s) = schema {
        schema_qualified.contains_key(&(s.to_string(), name.to_string()))
    } else {
        unqualified.contains_key(name)
    }
}

/// L1 relation existence with runtime-faithful resolution.
///
/// For a **schema-qualified** source, probes via `to_regclass` on the
/// case-sensitively-quoted identifier (search_path-independent — exactly how the
/// runtime resolves it), so a mixed-case relation in an off-`search_path` schema
/// is not falsely flagged. Falls back to the relation map for **bare** names
/// (search_path-scoped, also matching the runtime) or when the connector cannot
/// probe directly (non-Postgres).
async fn relation_source_exists(
    introspector: &impl DatabaseIntrospector,
    schema_qualified: &HashMap<(String, String), RelationInfo>,
    unqualified: &HashMap<String, Vec<String>>,
    sql_source: &str,
) -> fraiseql_core::Result<bool> {
    let (schema, name) = split_schema_qualified(sql_source);
    if let Some(s) = schema {
        if let Some(exists) = introspector.qualified_relation_exists(s, name).await? {
            return Ok(exists);
        }
    }
    // Bare name, or a connector that can't probe → relation-map lookup.
    Ok(relation_exists(schema_qualified, unqualified, sql_source))
}

/// L1 function existence for a mutation's `sql_source`.
///
/// A mutation's `sql_source` names a SQL *function*, so it is probed via `pg_proc`
/// (not the relation catalog). When the connector cannot probe functions
/// (non-Postgres), returns `true` — the check is skipped rather than false-failing.
async fn function_source_exists(
    introspector: &impl DatabaseIntrospector,
    sql_source: &str,
) -> fraiseql_core::Result<bool> {
    let (schema, name) = split_schema_qualified(sql_source);
    Ok(introspector.function_exists(schema, name).await?.unwrap_or(true))
}

/// Validate a compiled schema against a live database.
///
/// Performs three levels of validation:
/// - **L1**: Checks that `sql_source` relations exist
/// - **L2**: Checks column existence and JSON column types
/// - **L3**: Checks JSONB key existence via sampling
///
/// All diagnostics are warnings — the report never causes compilation to fail.
///
/// # Errors
///
/// Returns `FraiseQLError` if database introspection queries fail.
pub async fn validate_schema_against_database(
    schema: &CompiledSchema,
    introspector: &impl DatabaseIntrospector,
) -> fraiseql_core::Result<DatabaseValidationReport> {
    // Auto-wired argument names excluded from direct-arg native column detection.
    // Must stay in sync with AUTO_PARAM_NAMES in fraiseql-core/runtime/executor/query.rs.
    const AUTO_PARAM_NAMES: &[&str] = &[
        "where", "limit", "offset", "orderBy", "first", "last", "after", "before",
    ];

    let mut warnings = Vec::new();
    let mut native_columns: HashMap<String, HashMap<String, String>> = HashMap::new();
    let mut pagination_orders: HashMap<String, PaginationOrder> = HashMap::new();
    let db_type = introspector.database_type();

    // L1: Build relation lookup maps
    let relations = introspector.list_relations().await?;
    let (schema_qualified, unqualified) = build_relation_maps(&relations);

    // Validate queries
    for query in &schema.queries {
        if let Some(ref source) = query.sql_source {
            // L1: Check relation exists (qualified → to_regclass verbatim, bare → map).
            if !relation_source_exists(introspector, &schema_qualified, &unqualified, source)
                .await?
            {
                warnings.push(DatabaseWarning::MissingRelation {
                    query_name: query.name.clone(),
                    sql_source: source.clone(),
                });
                continue; // Skip L2/L3 if relation doesn't exist
            }

            // L2: Get columns for the relation.
            // Pass the full source (possibly schema-qualified like "benchmark.tv_post") so
            // the introspector can use the explicit schema when present.
            let columns = introspector.get_columns(source).await?;
            let column_map: HashMap<String, String> =
                columns.into_iter().map(|(name, dtype, _)| (name, dtype)).collect();

            // L2: Check jsonb_column
            let jsonb_col = &query.jsonb_column;
            if !jsonb_col.is_empty() {
                if let Some(actual_type) = column_map.get(jsonb_col) {
                    if !is_json_type(actual_type, db_type) {
                        warnings.push(DatabaseWarning::WrongJsonColumnType {
                            query_name:  query.name.clone(),
                            sql_source:  source.clone(),
                            column_name: jsonb_col.clone(),
                            actual_type: actual_type.clone(),
                        });
                    }
                } else {
                    warnings.push(DatabaseWarning::MissingJsonColumn {
                        query_name:  query.name.clone(),
                        sql_source:  source.clone(),
                        column_name: jsonb_col.clone(),
                    });
                }
            }

            // L2: Check relay_cursor_column
            if query.relay {
                if let Some(ref cursor_col) = query.relay_cursor_column {
                    if !column_map.contains_key(cursor_col) {
                        warnings.push(DatabaseWarning::MissingCursorColumn {
                            query_name:  query.name.clone(),
                            sql_source:  source.clone(),
                            column_name: cursor_col.clone(),
                        });
                    }
                }
            }

            // L2: pagination_order (#1303). Three questions, in the order the
            // compiled value answers them: a declared column must exist; a derived
            // JSONB identity may be sharpened to a cheaper native one; and either
            // way, a view that orders itself is about to have that order replaced.
            match &query.pagination_order {
                Some(PaginationOrder::Column(col)) if !column_map.contains_key(col) => {
                    warnings.push(DatabaseWarning::MissingPaginationColumn {
                        query_name:  query.name.clone(),
                        sql_source:  source.clone(),
                        column_name: col.clone(),
                    });
                },
                Some(PaginationOrder::Column(_)) => {},
                Some(PaginationOrder::JsonIdentity) => {
                    let pk = pk_column_name(&query.return_type);
                    if let Some(col) = cheapest_identity_column(&pk, &column_map) {
                        pagination_orders.insert(query.name.clone(), PaginationOrder::Column(col));
                    } else {
                        warnings.push(DatabaseWarning::PaginationIdentityFallback {
                            query_name: query.name.clone(),
                            sql_source: source.clone(),
                            pk_column:  pk,
                        });
                    }
                },
                None => {},
            }

            // A view that orders itself is the one shape a default page ordering
            // makes worse, and the author is the only one who can adjudicate it —
            // so it is reported wherever a default is about to apply, whether the
            // ordering came from the author or from the compiler. A query that
            // declared `none` has already answered and is silent.
            if query.pagination_order.is_some()
                && introspector
                    .get_view_definition(source)
                    .await?
                    .is_some_and(|body| orders_itself(&body))
            {
                warnings.push(DatabaseWarning::SelfOrderingView {
                    query_name: query.name.clone(),
                    sql_source: source.clone(),
                });
            }

            // L3: Sample JSON keys if jsonb_column is valid JSON type
            if !jsonb_col.is_empty() {
                let json_type_ok =
                    column_map.get(jsonb_col).is_some_and(|t| is_json_type(t, db_type));

                if json_type_ok {
                    validate_json_keys(
                        schema,
                        query,
                        source,
                        jsonb_col,
                        introspector,
                        source, // pass full schema-qualified source for sample queries
                        &mut warnings,
                    )
                    .await?;
                }
            }

            // L2: Detect native columns for direct (non-auto-param) arguments AND
            // inject params. Both are filtered against the view at runtime, so a name
            // that matches a real column must use the native-column path
            // (`WHERE col = $N`) rather than the JSONB fallback (`data->>'name'`).
            let direct_args: Vec<&str> = query
                .arguments
                .iter()
                .filter(|a| !AUTO_PARAM_NAMES.contains(&a.name.as_str()))
                .map(|a| a.name.as_str())
                .collect();

            let (query_native, arg_fallbacks) = detect_query_native_columns(
                &direct_args,
                query.inject_params.keys().map(String::as_str),
                &column_map,
            );
            for arg_name in arg_fallbacks {
                warnings.push(DatabaseWarning::NativeColumnFallback {
                    query_name: query.name.clone(),
                    sql_source: source.clone(),
                    arg_name,
                });
            }

            // L2 (type-convertibility): a direct argument that resolved to a native
            // column whose SQL type cannot cleanly drive the predicate is a likely
            // authoring bug (e.g. an `Int` argument filtering a `uuid` column).
            for arg in &query.arguments {
                if let Some(col_type) = query_native.get(&arg.name) {
                    if !arg_type_convertible(&arg.arg_type, col_type) {
                        warnings.push(DatabaseWarning::TypeConvertibility {
                            query_name:   query.name.clone(),
                            sql_source:   source.clone(),
                            arg_name:     arg.name.clone(),
                            graphql_type: arg.arg_type.to_graphql_string(),
                            column_type:  col_type.clone(),
                        });
                    }
                }
            }

            if !query_native.is_empty() {
                native_columns.insert(query.name.clone(), query_native);
            }

            // L1: Check additional_views (relations, same resolution as sql_source).
            for view in &query.additional_views {
                if !relation_source_exists(introspector, &schema_qualified, &unqualified, view)
                    .await?
                {
                    warnings.push(DatabaseWarning::MissingAdditionalView {
                        query_name: query.name.clone(),
                        view_name:  view.clone(),
                    });
                }
            }
        }
    }

    // Validate mutations (L1 only). A mutation's `sql_source` is a FUNCTION, not a
    // relation — probe `pg_proc`, not the relation catalog (#485). Mirrors the
    // shared `fraiseql_core::schema::sql_source_probes` Relation/Function split.
    for mutation in &schema.mutations {
        if let Some(ref source) = mutation.sql_source {
            if !function_source_exists(introspector, source).await? {
                warnings.push(DatabaseWarning::MissingFunction {
                    mutation_name: mutation.name.clone(),
                    sql_source:    source.clone(),
                });
            }
        }
    }

    // Validate type sources (L1 only, warn-grade). A `@fraiseql.type`'s `sql_source` is a
    // view/table; a declared source that does not exist is usually an embedded value object
    // the SDK synthesized a source for (#653). Surface it at its origin — never failing —
    // instead of letting it resurface later as an unrelated cascade `id` error. Unlike
    // queries/mutations, type sources are not on the shared `sql_source_probes` work-list,
    // so the hard `FRAISEQL_VALIDATE_SQL_SOURCES` gate does not flag them; this is the only
    // check that does. Synthetic types (empty source) and error types are skipped.
    //
    // This set INTENTIONALLY DIVERGES from `is_queryable_entity` (the cascade classifier).
    // After #665 that predicate ALSO excludes framework-`internal` projections, but this
    // existence check must NOT: a missing `v_entity_change_log` / `v_transport_checkpoint`
    // is exactly #569's failure mode ("no install path; every mutation fails on a fresh
    // stack"), so validating the change-log views is precisely the point. Do NOT DRY the
    // two predicates together — the `internal` exemption belongs to cascade classification
    // only. The `validator_checks_internal_projection_view_existence` tripwire test pins
    // this divergence so a future "unification" cannot silently delete the #569 signal.
    for ty in &schema.types {
        let source = ty.sql_source.as_str();
        if ty.is_error || source.is_empty() {
            continue;
        }
        if !relation_source_exists(introspector, &schema_qualified, &unqualified, source).await? {
            warnings.push(DatabaseWarning::MissingTypeSource {
                type_name:  ty.name.to_string(),
                sql_source: source.to_string(),
            });
        }
    }

    Ok(DatabaseValidationReport {
        warnings,
        native_columns,
        pagination_orders,
    })
}

/// The Trinity primary-key column a type's relation is expected to expose.
///
/// The same derivation `relay_cursor_column` uses (`User` → `pk_user`), and
/// deliberately so: when a relation carries it, both pagination families cut
/// their pages on the same column, which is ADR-0017's "one identity, consumed
/// uniformly, not re-derived per subsystem".
fn pk_column_name(return_type: &str) -> String {
    format!("pk_{}", fraiseql_core::utils::to_snake_case(return_type))
}

/// The cheapest native column that totally orders this relation, if it has one.
///
/// `pk_<type>` before `id`, which is the opposite of the order the two are
/// usually reached in and is what the measurements say: against PostgreSQL 16
/// over 200 000 rows at a deep offset, the `pk` BIGINT index scan costs 1.7x the
/// unordered plan and the `id` UUID one 4.2x. Both beat the JSONB extraction's
/// 7.7x, so either is worth taking.
///
/// Only the *name* is checked, never the type: a column that orders the rows is
/// all a total order needs, and a `pk_`/`id` column that is not unique is a
/// broken view rather than a case to code around.
fn cheapest_identity_column(pk: &str, columns: &HashMap<String, String>) -> Option<String> {
    if columns.contains_key(pk) {
        return Some(pk.to_string());
    }
    columns.contains_key("id").then(|| "id".to_string())
}

/// Does this view body carry its own `ORDER BY`?
///
/// Deliberately lexical, and deliberately not exact. `pg_get_viewdef` returns
/// normalised SQL, so `ORDER BY` appears upper-cased and space-separated when it
/// appears at all — but it appears for a window frame and a subquery too, where a
/// page ordering on the outer read is entirely correct. The finding this drives is
/// therefore advisory and says what it saw; a stricter parser would trade a
/// question the author can answer in a second for a dependency on parsing SQL.
fn orders_itself(view_body: &str) -> bool {
    view_body.contains("ORDER BY")
}

/// Build lookup maps from the list of relations.
fn build_relation_maps(
    relations: &[RelationInfo],
) -> (HashMap<(String, String), RelationInfo>, HashMap<String, Vec<String>>) {
    let mut schema_qualified = HashMap::new();
    let mut unqualified: HashMap<String, Vec<String>> = HashMap::new();

    for rel in relations {
        schema_qualified.insert((rel.schema.clone(), rel.name.clone()), rel.clone());
        unqualified.entry(rel.name.clone()).or_default().push(rel.schema.clone());
    }

    (schema_qualified, unqualified)
}

/// Find every declared `sql_source` that is **not backed** by a live database
/// object, over the shared [`sql_source_probes`] work-list (#487).
///
/// Resolution mirrors the runtime exactly: a query relation by `to_regclass`
/// (qualified, verbatim) or the search_path-scoped relation map (bare); a mutation
/// function via `pg_proc`. Returns the unbacked probes in declaration order — an
/// empty vec means every source is backed. This is the existence gate the
/// `validate --against-db` CLI and the opt-in server boot check share, so the two
/// agree on "backed" by construction.
///
/// When the connector cannot probe a kind (non-Postgres), that source is treated
/// as backed (skipped) rather than false-failed.
///
/// # Errors
///
/// Returns an error if database introspection fails.
pub async fn find_unbacked_sources(
    schema: &CompiledSchema,
    introspector: &impl DatabaseIntrospector,
) -> fraiseql_core::Result<Vec<SourceProbe>> {
    let relations = introspector.list_relations().await?;
    let (schema_qualified, unqualified) = build_relation_maps(&relations);

    let mut unbacked = Vec::new();
    for probe in sql_source_probes(schema) {
        let exists = match probe.kind {
            SourceKind::Function => introspector
                .function_exists(probe.schema.as_deref(), &probe.name)
                .await?
                .unwrap_or(true),
            SourceKind::Relation => {
                if let Some(s) = &probe.schema {
                    match introspector.qualified_relation_exists(s, &probe.name).await? {
                        Some(exists) => exists,
                        None => schema_qualified.contains_key(&(s.clone(), probe.name.clone())),
                    }
                } else {
                    unqualified.contains_key(&probe.name)
                }
            },
        };
        if !exists {
            unbacked.push(probe);
        }
    }
    Ok(unbacked)
}

/// Validate JSON keys in sampled data for L3 checking.
async fn validate_json_keys(
    schema: &CompiledSchema,
    query: &fraiseql_core::schema::QueryDefinition,
    source: &str,
    jsonb_col: &str,
    introspector: &impl DatabaseIntrospector,
    table_name: &str,
    warnings: &mut Vec<DatabaseWarning>,
) -> fraiseql_core::Result<()> {
    let samples = introspector.get_sample_json_rows(table_name, jsonb_col, 5).await?;

    if samples.is_empty() {
        return Ok(());
    }

    // Merge all top-level keys from sampled rows
    let mut all_keys = HashSet::new();
    for sample in &samples {
        if let serde_json::Value::Object(map) = sample {
            for key in map.keys() {
                all_keys.insert(key.clone());
            }
        }
    }

    if all_keys.is_empty() {
        return Ok(());
    }

    // Find the type definition for this query's return type
    let type_def = schema.types.iter().find(|t| t.name.as_str() == query.return_type);

    if let Some(type_def) = type_def {
        for field in &type_def.fields {
            let field_str = field.name.as_str();
            // Use the canonical acronym/digit-aware caser the runtime queries with
            // (`httpResponse` → `http_response`, not `h_t_t_p_response`), so the
            // L3 JSON-key check never cries wolf on acronym/digit fields (#485).
            let json_key = fraiseql_core::utils::to_snake_case(field_str);
            // Skip fields that are top-level columns (not from JSONB)
            // Convention: fields like "id", "pk_*", "fk_*" are columns, not JSON keys
            if field_str == "id" || field_str.starts_with("pk_") || field_str.starts_with("fk_") {
                continue;
            }
            if !all_keys.contains(&json_key) && !all_keys.contains(field_str) {
                warnings.push(DatabaseWarning::MissingJsonKey {
                    query_name: query.name.clone(),
                    type_name: type_def.name.as_str().to_string(),
                    sql_source: source.to_string(),
                    json_column: jsonb_col.to_string(),
                    field_name: field_str.to_string(),
                    json_key,
                    field_required: !field.nullable,
                });
            }
        }
    }

    Ok(())
}

/// Enum dispatch for database introspectors.
///
/// Uses enum dispatch instead of `Box<dyn DatabaseIntrospector>` because the
/// trait uses `async_fn_in_trait` and cannot be object-safe.
pub enum AnyIntrospector {
    /// `PostgreSQL` introspector.
    Postgres(fraiseql_core::db::PostgresIntrospector),
}

impl DatabaseIntrospector for AnyIntrospector {
    async fn list_fact_tables(&self) -> fraiseql_core::Result<Vec<String>> {
        match self {
            Self::Postgres(i) => i.list_fact_tables().await,
        }
    }

    async fn get_columns(
        &self,
        table_name: &str,
    ) -> fraiseql_core::Result<Vec<(String, String, bool)>> {
        match self {
            Self::Postgres(i) => i.get_columns(table_name).await,
        }
    }

    async fn get_indexed_columns(&self, table_name: &str) -> fraiseql_core::Result<Vec<String>> {
        match self {
            Self::Postgres(i) => i.get_indexed_columns(table_name).await,
        }
    }

    fn database_type(&self) -> DatabaseType {
        match self {
            Self::Postgres(i) => i.database_type(),
        }
    }

    async fn get_sample_jsonb(
        &self,
        table_name: &str,
        column_name: &str,
    ) -> fraiseql_core::Result<Option<serde_json::Value>> {
        match self {
            Self::Postgres(i) => i.get_sample_jsonb(table_name, column_name).await,
        }
    }

    async fn list_relations(&self) -> fraiseql_core::Result<Vec<fraiseql_core::db::RelationInfo>> {
        match self {
            Self::Postgres(i) => i.list_relations().await,
        }
    }

    async fn get_sample_json_rows(
        &self,
        table_name: &str,
        column_name: &str,
        limit: usize,
    ) -> fraiseql_core::Result<Vec<serde_json::Value>> {
        match self {
            Self::Postgres(i) => i.get_sample_json_rows(table_name, column_name, limit).await,
        }
    }

    async fn function_exists(
        &self,
        schema: Option<&str>,
        name: &str,
    ) -> fraiseql_core::Result<Option<bool>> {
        // Must delegate (not inherit the trait default) so the Postgres impl is
        // actually used — otherwise every variant would return `None`.
        match self {
            Self::Postgres(i) => i.function_exists(schema, name).await,
        }
    }

    async fn qualified_relation_exists(
        &self,
        schema: &str,
        name: &str,
    ) -> fraiseql_core::Result<Option<bool>> {
        match self {
            Self::Postgres(i) => i.qualified_relation_exists(schema, name).await,
        }
    }

    async fn get_view_definition(&self, relation: &str) -> fraiseql_core::Result<Option<String>> {
        match self {
            Self::Postgres(i) => i.get_view_definition(relation).await,
        }
    }
}

/// Create an introspector from a database URL.
///
/// Detects the database type from the URL scheme and creates the appropriate
/// introspector with a connection pool.
///
/// # Errors
///
/// Returns error if the URL scheme is unrecognized or the connection pool
/// cannot be created.
#[allow(clippy::unused_async)] // Reason: callers always .await this; feature-gated branches do use await
pub async fn create_introspector(db_url: &str) -> anyhow::Result<AnyIntrospector> {
    if db_url.starts_with("postgres") {
        use deadpool_postgres::{Config, ManagerConfig, RecyclingMethod, Runtime};
        use tokio_postgres::NoTls;

        let mut cfg = Config::new();
        cfg.url = Some(db_url.to_string());
        cfg.manager = Some(ManagerConfig {
            recycling_method: RecyclingMethod::Fast,
        });
        cfg.pool = Some(deadpool_postgres::PoolConfig::new(2));

        let pool = cfg
            .create_pool(Some(Runtime::Tokio1), NoTls)
            .map_err(|e| anyhow::anyhow!("Failed to create PostgreSQL pool: {e}"))?;

        Ok(AnyIntrospector::Postgres(fraiseql_core::db::PostgresIntrospector::new(pool)))
    } else if db_url.starts_with("mysql")
        || db_url.starts_with("mariadb")
        || db_url.starts_with("sqlite")
        || db_url.starts_with("mssql")
        || db_url.starts_with("server=")
        || std::path::Path::new(db_url)
            .extension()
            .is_some_and(|ext| ext.eq_ignore_ascii_case("db") || ext.eq_ignore_ascii_case("sqlite"))
    {
        anyhow::bail!(
            "This database URL targets an engine whose support was removed: FraiseQL is \
             PostgreSQL-only (G2 decision on #374; the non-PostgreSQL dialects were never \
             production-correct, see #721/#799). Use a postgresql:// URL."
        )
    } else {
        anyhow::bail!("Unrecognized database URL scheme: {db_url}")
    }
}

/// Build a query's native-column map from its explicit (non-auto-param) arguments
/// and its inject-param names, consulting the introspected `column_map`.
///
/// A name that matches a real view column is rendered as a native-column predicate
/// (`WHERE col = $N`) at runtime; a name with no matching column falls back to the
/// JSONB path (`data->>'name'`). inject params were previously omitted here, so an
/// inject-scoped list query against a view that keeps e.g. `tenant_id` as a real
/// column (and not inside `data`) rendered `data->>'tenant_id'` and matched no rows.
///
/// Returns the native-column map plus the explicit-arg names that did NOT resolve to
/// a column — the caller emits a `NativeColumnFallback` warning for each. Inject-param
/// misses are intentionally silent: a claim may legitimately live in the `data` JSONB.
fn detect_query_native_columns<'a>(
    direct_arg_names: &[&str],
    inject_param_names: impl Iterator<Item = &'a str>,
    column_map: &HashMap<String, String>,
) -> (HashMap<String, String>, Vec<String>) {
    let mut native: HashMap<String, String> = HashMap::new();
    let mut arg_fallbacks: Vec<String> = Vec::new();

    for arg in direct_arg_names {
        if let Some(col_type) = column_map.get(*arg) {
            native.insert((*arg).to_string(), col_type.clone());
        } else {
            arg_fallbacks.push((*arg).to_string());
        }
    }

    for name in inject_param_names {
        if let Some(col_type) = column_map.get(name) {
            native.insert(name.to_string(), col_type.clone());
        }
    }

    (native, arg_fallbacks)
}

#[cfg(test)]
#[path = "database_validator_tests.rs"]
mod database_validator_tests;