fraiseql_db/

traits.rs

//! Database adapter trait definitions.
//!
//! The main [`DatabaseAdapter`] trait lives in this file. Supporting types
//! (`RelayPageResult`, `DatabaseCapabilities`, enums, type aliases) are in
//! the `adapter_types` submodule.

mod adapter_types;
mod mutations;
mod relay;

use std::sync::Arc;

pub use adapter_types::*;
use async_trait::async_trait;
use fraiseql_error::{FraiseQLError, Result};
pub use mutations::SupportsMutations;
pub use relay::RelayDatabaseAdapter;

use crate::{
    types::{
        DatabaseType, JsonbValue, PoolMetrics,
        sql_hints::{OrderByClause, SqlProjectionHint},
    },
    where_clause::WhereClause,
};

/// The framework-owned change-log row the mutation executor writes in-txn.
///
/// Carries only the fields the adapter cannot derive from the
/// `app.mutation_response` row it already holds: the DML verb and a NOT-NULL
/// `object_type` fallback. The changed-entity identity + payload (`object_id`,
/// `object_data`, `updated_fields`, `cascade`) are read from the function's own
/// returned row inside the same transaction (see
/// [`DatabaseAdapter::execute_function_call_with_changelog`]).
///
/// This is the Change Spine transactional-outbox contract. Beyond the
/// `object_type`/`modification_type` + changed-entity columns, it stamps the
/// envelope: `tenant_id` (carried here, from `SecurityContext`),
/// `trace_id` (the W3C trace id of the originating request), `schema_version`
/// (the compiled schema's content hash — a per-deployment constant),
/// `trace_context` (the full W3C trace context as JSON), `actor_type` /
/// `acting_for` (the request's actor classification and, for a delegated agent,
/// the underlying human — #390), `commit_time` (`clock_timestamp()` at INSERT),
/// and `seq` (the table's `SEQUENCE` default).
#[derive(Debug, Clone, Copy)]
pub struct ChangeLogWrite<'a> {
    /// NOT-NULL fallback for `object_type` when the row's `entity_type` is NULL.
    /// Sourced from `MutationDefinition.return_type` (always present).
    pub object_type:       &'a str,
    /// The DML verb written to `modification_type` (e.g. `"INSERT"`,
    /// `"UPDATE"`, `"DELETE"`, `"CUSTOM"`), from `MutationOperation`.
    pub modification_type: &'a str,
    /// The tenant partition stamp written to the `tenant_id UUID` column — the
    /// Trinity public-facing identifier, read from `SecurityContext.tenant_id`
    /// at write time and **never** reconstructed from connection / RLS state
    /// (RLS is PG-only; out-of-session spine consumers bypass it, so the row
    /// must carry tenant identity explicitly). `None` (→ SQL NULL) for an
    /// unauthenticated request, a request with no tenant, or a tenant
    /// identifier that is not a UUID.
    pub tenant_id:         Option<uuid::Uuid>,
    /// The W3C trace id of the originating request, written to the `trace_id`
    /// column so an outbox row links back to its distributed trace (the #392
    /// perf tooling surfaces it as the investigation handle). Read from the
    /// request's `traceparent` header at write time; `None` (→ SQL NULL) for a
    /// request without a trace context — e.g. an unauthenticated mutation, which
    /// carries no `SecurityContext` to stamp.
    pub trace_id:          Option<&'a str>,
    /// The compiled schema's version written to the `schema_version` column so an
    /// outbox row records which deployment produced it — the replay /
    /// zero-downtime correctness handle for #378 (reject a row replayed under a
    /// different schema). A per-deployment constant derived from the compiled
    /// schema (`CompiledSchema::content_hash()`), **not** from the request, so it
    /// changes on any schema change. `None` (→ SQL NULL) for producers with no
    /// compiled schema in scope — cooperative external producers (ETL) and the
    /// non-PostgreSQL no-op path.
    pub schema_version:    Option<&'a str>,
    /// The originating request's **full W3C trace context** as a JSON object
    /// (`{version, trace_id, parent_id, trace_flags, tracestate?}`), written to the
    /// `trace_context` JSONB column so a row carries enough to re-propagate /
    /// reconstruct the distributed trace — not just the scalar `trace_id`. Carried
    /// here as pre-serialized JSON **text** (the adapter binds it to the JSONB
    /// column). Built from the request's `traceparent` / `tracestate` headers at
    /// write time; `None` (→ SQL NULL) for a request without a valid trace context,
    /// consistent with `trace_id`.
    pub trace_context:     Option<&'a str>,
    /// The request's actor classification written to the `actor_type` column (the
    /// `snake_case` `ActorType` token: `"human_user"`, `"service_account"`,
    /// `"ai_agent"`, `"system_job"`), from `SecurityContext.actor_type()` at write
    /// time (#390). `None` (→ SQL NULL) for a request with no `SecurityContext` to
    /// stamp (an unauthenticated mutation), or a cooperative external producer.
    pub actor_type:        Option<&'a str>,
    /// For a delegated agent request, the **underlying human** the agent acts for
    /// — the public-facing UUID, written to the `acting_for UUID` column from
    /// `SecurityContext.acting_for()` (#390). Mirrors `tenant_id`'s UUID shape so
    /// it is stamped without a DB lookup. `None` (→ SQL NULL) for a non-delegated
    /// request, an unauthenticated mutation, or a subject that is not UUID-shaped.
    pub acting_for:        Option<uuid::Uuid>,
}

impl<'a> ChangeLogWrite<'a> {
    /// Build a change-log write descriptor with no envelope stamps (`tenant_id`,
    /// `trace_id`, `schema_version`, `trace_context`, `actor_type` and
    /// `acting_for` NULL). Chain [`with_tenant_id`](Self::with_tenant_id) /
    /// [`with_trace_id`](Self::with_trace_id) /
    /// [`with_schema_version`](Self::with_schema_version) /
    /// [`with_trace_context`](Self::with_trace_context) /
    /// [`with_actor_type`](Self::with_actor_type) /
    /// [`with_acting_for`](Self::with_acting_for) to stamp them.
    #[must_use]
    pub const fn new(object_type: &'a str, modification_type: &'a str) -> Self {
        Self {
            object_type,
            modification_type,
            tenant_id: None,
            trace_id: None,
            schema_version: None,
            trace_context: None,
            actor_type: None,
            acting_for: None,
        }
    }

    /// Stamp the tenant partition id (the Trinity public-facing UUID) onto the
    /// outbox row. `None` leaves `tenant_id` NULL — for system / unauthenticated
    /// rows, or a tenant identifier that is not UUID-shaped.
    #[must_use]
    pub const fn with_tenant_id(mut self, tenant_id: Option<uuid::Uuid>) -> Self {
        self.tenant_id = tenant_id;
        self
    }

    /// Stamp the originating request's W3C trace id onto the outbox row. `None`
    /// leaves `trace_id` NULL — for a request with no trace context.
    #[must_use]
    pub const fn with_trace_id(mut self, trace_id: Option<&'a str>) -> Self {
        self.trace_id = trace_id;
        self
    }

    /// Stamp the compiled schema's version (its content hash) onto the outbox
    /// row. `None` leaves `schema_version` NULL — for producers with no compiled
    /// schema in scope (cooperative external producers, the non-PostgreSQL no-op
    /// path).
    #[must_use]
    pub const fn with_schema_version(mut self, schema_version: Option<&'a str>) -> Self {
        self.schema_version = schema_version;
        self
    }

    /// Stamp the originating request's full W3C trace context (pre-serialized JSON
    /// text) onto the outbox row's `trace_context` JSONB column. `None` leaves it
    /// NULL — for a request with no valid trace context, or a non-PostgreSQL
    /// no-op / cooperative producer.
    #[must_use]
    pub const fn with_trace_context(mut self, trace_context: Option<&'a str>) -> Self {
        self.trace_context = trace_context;
        self
    }

    /// Stamp the request's actor classification (the `snake_case` `ActorType`
    /// token) onto the outbox row's `actor_type` column (#390). `None` leaves it
    /// NULL — for an unauthenticated mutation or a cooperative producer.
    #[must_use]
    pub const fn with_actor_type(mut self, actor_type: Option<&'a str>) -> Self {
        self.actor_type = actor_type;
        self
    }

    /// Stamp the delegated user's UUID (the human a delegated agent acts for) onto
    /// the outbox row's `acting_for` column (#390). `None` leaves it NULL — for a
    /// non-delegated request, an unauthenticated mutation, or a non-UUID subject.
    #[must_use]
    pub const fn with_acting_for(mut self, acting_for: Option<uuid::Uuid>) -> Self {
        self.acting_for = acting_for;
        self
    }
}

/// Database adapter for executing queries against views.
///
/// This trait abstracts over different database backends (PostgreSQL, MySQL, SQLite, SQL Server).
/// All implementations must support:
/// - Executing parameterized WHERE queries against views
/// - Returning JSONB data from the `data` column
/// - Connection pooling and health checks
/// - Row-level security (RLS) WHERE clauses
///
/// # Architecture
///
/// The adapter is the runtime interface to the database. It receives:
/// - View/table name (e.g., "v_user", "tf_sales")
/// - Parameterized WHERE clauses (AST form, not strings)
/// - Projection hints (for performance optimization)
/// - Pagination parameters (LIMIT/OFFSET)
///
/// And returns:
/// - JSONB rows from the `data` column (most operations)
/// - Arbitrary rows as HashMap (for aggregation queries)
/// - Mutation results from stored procedures
///
/// # Implementing a New Adapter
///
/// To add support for a new database (e.g., Oracle, Snowflake):
///
/// 1. **Create a new module** in `src/db/your_database/`
/// 2. **Implement the trait**:
///
///    ```rust,ignore
///    pub struct YourDatabaseAdapter { /* fields */ }
///
///    #[async_trait]
///    impl DatabaseAdapter for YourDatabaseAdapter {
///        async fn execute_where_query(&self, ...) -> Result<Vec<JsonbValue>> {
///            // 1. Build parameterized SQL from WhereClause AST
///            // 2. Execute with bound parameters (NO string concatenation)
///            // 3. Return JSONB from data column
///        }
///        // Implement other required methods...
///    }
///    ```
/// 3. **Add feature flag** to `Cargo.toml` (e.g., `feature = "your-database"`)
/// 4. **Copy structure from PostgreSQL adapter** — see `src/db/postgres/adapter.rs`
/// 5. **Add tests** in `tests/integration/your_database_test.rs`
///
/// # Security Requirements
///
/// All implementations MUST:
/// - **Never concatenate user input into SQL strings**
/// - **Always use parameterized queries** with bind parameters
/// - **Validate parameter types** before binding
/// - **Preserve RLS WHERE clauses** (never filter them out)
/// - **Return errors, not silently fail** (e.g., connection loss)
///
/// # Connection Management
///
/// - Use a connection pool (recommended: 20 connections default)
/// - Implement `health_check()` for ping-based monitoring
/// - Provide `pool_metrics()` for observability
/// - Handle stale connections gracefully
///
/// # Performance Characteristics
///
/// Expected throughput when properly implemented:
/// - **Simple queries** (single table, no WHERE): 250+ Kelem/s
/// - **Complex queries** (JOINs, multiple conditions): 50+ Kelem/s
/// - **Mutations** (stored procedures): 1-10 RPS (depends on procedure)
/// - **Relay pagination** (keyset cursors): 15-30ms latency
///
/// # Example: PostgreSQL Implementation
///
/// ```rust,ignore
/// use sqlx::postgres::PgPool;
/// use async_trait::async_trait;
///
/// pub struct PostgresAdapter {
///     pool: PgPool,
/// }
///
/// #[async_trait]
/// impl DatabaseAdapter for PostgresAdapter {
///     async fn execute_where_query(
///         &self,
///         view: &str,
///         where_clause: Option<&WhereClause>,
///         limit: Option<u32>,
///         offset: Option<u32>,
///     ) -> Result<Vec<JsonbValue>> {
///         // 1. Build SQL: SELECT data FROM {view} WHERE {where_clause} LIMIT {limit}
///         let mut sql = format!(r#"SELECT data FROM "{}""#, view);
///
///         // 2. Add WHERE clause (converts AST to parameterized SQL)
///         let params = if let Some(where_clause) = where_clause {
///             sql.push_str(" WHERE ");
///             let (where_sql, params) = build_where_sql(where_clause)?;
///             sql.push_str(&where_sql);
///             params
///         } else {
///             vec![]
///         };
///
///         // 3. Add LIMIT and OFFSET
///         if let Some(limit) = limit {
///             sql.push_str(" LIMIT ");
///             sql.push_str(&limit.to_string());
///         }
///         if let Some(offset) = offset {
///             sql.push_str(" OFFSET ");
///             sql.push_str(&offset.to_string());
///         }
///
///         // 4. Execute with bound parameters (NO string interpolation)
///         let rows: Vec<(serde_json::Value,)> = sqlx::query_as(&sql)
///             .bind(&params[0])
///             .bind(&params[1])
///             // ... bind all parameters
///             .fetch_all(&self.pool)
///             .await?;
///
///         // 5. Extract JSONB and return
///         Ok(rows.into_iter().map(|(data,)| data).collect())
///     }
///
///     // Implement other required methods...
/// }
/// ```
///
/// # Example: Basic Usage
///
/// ```rust,no_run
/// use fraiseql_db::{DatabaseAdapter, WhereClause, WhereOperator};
/// use serde_json::json;
///
/// # async fn example(adapter: impl DatabaseAdapter) -> Result<(), Box<dyn std::error::Error>> {
/// // Build WHERE clause (AST, not string)
/// let where_clause = WhereClause::Field {
///     path: vec!["email".to_string()],
///     operator: WhereOperator::Icontains,
///     value: json!("example.com"),
/// };
///
/// // Execute query with parameters
/// let results = adapter
///     .execute_where_query("v_user", Some(&where_clause), Some(10), None, None)
///     .await?;
///
/// println!("Found {} users matching filter", results.len());
/// # Ok(())
/// # }
/// ```
///
/// # See Also
///
/// - `WhereClause` — AST for parameterized WHERE clauses
/// - `RelayDatabaseAdapter` — Optional trait for keyset pagination
/// - `DatabaseCapabilities` — Feature detection for the adapter
/// - [Performance Guide](https://docs.fraiseql.rs/performance/database-adapters.md)
// POLICY: `#[async_trait]` placement for `DatabaseAdapter`
//
// `DatabaseAdapter` is used both generically (`Server<A: DatabaseAdapter>` in axum
// handlers, zero overhead via static dispatch) and dynamically (`Arc<dyn
// DatabaseAdapter + Send + Sync>` in federation, heap-boxed future per call).
//
// `#[async_trait]` is required on:
// - The trait definition (generates `Pin<Box<dyn Future + Send>>` return types)
// - Every `impl DatabaseAdapter for ConcreteType` block (generates the boxing)
// NOT required on callers (they see `Pin<Box<dyn Future + Send>>` from macro output).
//
// Why not native `async fn in trait` (Rust 1.75+)?
// Native dyn async trait does NOT propagate `+ Send` on generated futures. Tokio
// requires futures spawned with `tokio::spawn` to be `Send`. Until Return Type
// Notation (RFC 3425, tracking: github.com/rust-lang/rust/issues/109417) stabilises,
// `async_trait` is the only ergonomic path to `dyn DatabaseAdapter + Send + Sync`.
// Re-evaluate when Rust 1.90+ ships or when RTN is stabilised.
//
// MIGRATION TRACKING: async-trait → native async fn in trait
//
// Current status: BLOCKED on RFC 3425 (Return Type Notation)
// See: https://github.com/rust-lang/rfcs/pull/3425
//      https://github.com/rust-lang/rust/issues/109417
//
// Migration is safe when ALL of the following are true:
// 1. RTN with `+ Send` bounds is stable on rustc (e.g. `fn foo() -> impl Future + Send`)
// 2. FraiseQL MSRV is updated to that stabilising version
// 3. tokio::spawn() works with native dyn async trait objects (futures must be Send)
//
// Scope when criteria are met: 68 files (grep -rn "#\[async_trait\]" crates/)
// Effort: Medium (mostly mechanical — remove macro from impls, adjust trait defs)
// dynosaur was evaluated and rejected: does not propagate + Send (incompatible with Tokio)
#[async_trait]
pub trait DatabaseAdapter: Send + Sync {
    /// Execute a WHERE query against a view and return JSONB rows.
    ///
    /// # Arguments
    ///
    /// * `view` - View name (e.g., "v_user", "v_post")
    /// * `where_clause` - Optional WHERE clause AST
    /// * `limit` - Optional row limit (for pagination)
    /// * `offset` - Optional row offset (for pagination)
    /// * `security_context` - Optional security context for RLS and caching decisions
    ///
    /// # Returns
    ///
    /// Vec of JSONB values from the `data` column.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` on query execution failure.
    /// Returns `FraiseQLError::ConnectionPool` if connection pool is exhausted.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use fraiseql_db::DatabaseAdapter;
    /// # async fn example(adapter: impl DatabaseAdapter) -> Result<(), Box<dyn std::error::Error>> {
    /// // Simple query without WHERE clause
    /// let all_users = adapter
    ///     .execute_where_query("v_user", None, Some(10), Some(0), None)
    ///     .await?;
    /// # Ok(())
    /// # }
    /// ```
    async fn execute_where_query(
        &self,
        view: &str,
        where_clause: Option<&WhereClause>,
        limit: Option<u32>,
        offset: Option<u32>,
        order_by: Option<&[OrderByClause]>,
    ) -> Result<Vec<JsonbValue>>;

    /// Execute a WHERE query with SQL field projection optimization.
    ///
    /// Projects only the requested fields at the database level, reducing network payload
    /// and JSON deserialization overhead by **40-55%** based on production measurements.
    ///
    /// This is the primary query execution method for optimized GraphQL queries.
    /// It automatically selects only the fields requested in the GraphQL query, avoiding
    /// unnecessary network transfer and deserialization of unused fields.
    ///
    /// # Automatic Projection
    ///
    /// In most cases, you don't call this directly. The `Executor` automatically:
    /// 1. Determines which fields the GraphQL query requests
    /// 2. Generates a `SqlProjectionHint` using database-specific SQL
    /// 3. Calls this method with the projection hint
    ///
    /// # Arguments
    ///
    /// * `view` - View name (e.g., "v_user", "v_post")
    /// * `projection` - Optional SQL projection hint with field list
    ///   - `Some(hint)`: Use projection to select only requested fields
    ///   - `None`: Falls back to standard query (full JSONB column)
    /// * `where_clause` - Optional WHERE clause AST for filtering
    /// * `limit` - Optional row limit (for pagination)
    ///
    /// # Returns
    ///
    /// Vec of JSONB values, either:
    /// - Full objects (when projection is None)
    /// - Projected objects with only requested fields (when projection is Some)
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` on query execution failure, including:
    /// - Connection pool exhaustion
    /// - SQL execution errors
    /// - Type mismatches
    ///
    /// # Performance Characteristics
    ///
    /// When projection is provided (recommended):
    /// - **Latency**: 40-55% reduction vs full object fetch
    /// - **Network**: 40-55% smaller payload (proportional to unused fields)
    /// - **Throughput**: Maintains 250+ Kelem/s (elements per second)
    /// - **Memory**: Proportional to projected fields only
    ///
    /// Improvement scales with:
    /// - Percentage of unused fields (more unused = more improvement)
    /// - Size of result set (larger sets benefit more)
    /// - Network latency (network-bound queries benefit most)
    ///
    /// When projection is None:
    /// - Behavior identical to `execute_where_query()`
    /// - Returns full JSONB column
    /// - Used for compatibility/debugging
    ///
    /// # Database Support
    ///
    /// | Database | Status | Implementation |
    /// |----------|--------|-----------------|
    /// | PostgreSQL | ✅ Optimized | `jsonb_build_object()` |
    /// | MySQL | ⏳ Fallback | Server-side filtering (planned) |
    /// | SQLite | ⏳ Fallback | Server-side filtering (planned) |
    /// | SQL Server | ⏳ Fallback | Server-side filtering (planned) |
    ///
    /// # Example: Direct Usage (Advanced)
    ///
    /// ```no_run
    /// // Requires: running PostgreSQL database and a DatabaseAdapter implementation.
    /// use fraiseql_db::types::SqlProjectionHint;
    /// use fraiseql_db::traits::DatabaseAdapter;
    /// use fraiseql_db::DatabaseType;
    ///
    /// # async fn example(adapter: &impl DatabaseAdapter) -> Result<(), Box<dyn std::error::Error>> {
    /// let projection = SqlProjectionHint::new(
    ///     DatabaseType::PostgreSQL,
    ///     "jsonb_build_object(\
    ///         'id', data->>'id', \
    ///         'name', data->>'name', \
    ///         'email', data->>'email'\
    ///     )".to_string(),
    ///     75,
    /// );
    ///
    /// let results = adapter
    ///     .execute_with_projection("v_user", Some(&projection), None, Some(100), None, None)
    ///     .await?;
    ///
    /// // results only contain id, name, email fields
    /// // 75% smaller than fetching all fields
    /// # Ok(())
    /// # }
    /// ```
    ///
    /// # Example: Fallback (No Projection)
    ///
    /// ```no_run
    /// // Requires: running PostgreSQL database and a DatabaseAdapter implementation.
    /// # use fraiseql_db::traits::DatabaseAdapter;
    /// # async fn example(adapter: &impl DatabaseAdapter) -> Result<(), Box<dyn std::error::Error>> {
    /// // For debugging or when projection not available
    /// let results = adapter
    ///     .execute_with_projection("v_user", None, None, Some(100), None, None)
    ///     .await?;
    ///
    /// // Equivalent to execute_where_query() - returns full objects
    /// # Ok(())
    /// # }
    /// ```
    ///
    /// # See Also
    ///
    /// - `execute_where_query()` - Standard query without projection
    /// - `SqlProjectionHint` - Structure defining field projection
    /// - [Projection Optimization Guide](https://docs.fraiseql.rs/performance/projection-optimization.md)
    async fn execute_with_projection(
        &self,
        view: &str,
        projection: Option<&SqlProjectionHint>,
        where_clause: Option<&WhereClause>,
        limit: Option<u32>,
        offset: Option<u32>,
        order_by: Option<&[OrderByClause]>,
    ) -> Result<Vec<JsonbValue>>;

    /// Like `execute_where_query` but returns the result wrapped in an `Arc`.
    ///
    /// The default implementation wraps the result of `execute_where_query` in a
    /// fresh `Arc`. `CachedDatabaseAdapter` overrides this to return the cached `Arc`
    /// directly — eliminating the full `Vec<JsonbValue>` clone that the non-`Arc`
    /// path requires on every cache hit.
    ///
    /// Callers on the hot query path should prefer this variant and borrow from the
    /// `Arc` via `&**arc` rather than taking ownership.
    ///
    /// # Errors
    ///
    /// Same errors as `execute_where_query`.
    async fn execute_where_query_arc(
        &self,
        view: &str,
        where_clause: Option<&WhereClause>,
        limit: Option<u32>,
        offset: Option<u32>,
        order_by: Option<&[OrderByClause]>,
    ) -> Result<Arc<Vec<JsonbValue>>> {
        self.execute_where_query(view, where_clause, limit, offset, order_by)
            .await
            .map(Arc::new)
    }

    /// Like `execute_with_projection` but returns the result wrapped in an `Arc`.
    ///
    /// The default implementation wraps the result of `execute_with_projection` in a
    /// fresh `Arc`. `CachedDatabaseAdapter` overrides this to return the cached `Arc`
    /// directly — eliminating the full `Vec<JsonbValue>` clone that the non-`Arc`
    /// path requires on every cache hit.
    ///
    /// Parameters are passed in a `ProjectionRequest` struct (F043) so adapters
    /// and callers cannot misorder them.
    ///
    /// # Errors
    ///
    /// Same errors as `execute_with_projection`.
    async fn execute_with_projection_arc(
        &self,
        request: &ProjectionRequest<'_>,
    ) -> Result<Arc<Vec<JsonbValue>>> {
        self.execute_with_projection(
            request.view,
            request.projection,
            request.where_clause,
            request.limit,
            request.offset,
            request.order_by,
        )
        .await
        .map(Arc::new)
    }

    /// Get database type (for logging/metrics).
    ///
    /// Used to identify which database backend is in use.
    fn database_type(&self) -> DatabaseType;

    /// Health check - verify database connectivity.
    ///
    /// Executes a simple query (e.g., `SELECT 1`) to verify the database is reachable.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` if health check fails.
    async fn health_check(&self) -> Result<()>;

    /// Get connection pool metrics.
    ///
    /// Returns current statistics about the connection pool:
    /// - Total connections
    /// - Idle connections
    /// - Active connections
    /// - Waiting requests
    fn pool_metrics(&self) -> PoolMetrics;

    /// Execute raw SQL query and return rows as JSON objects.
    ///
    /// Used for aggregation queries where we need full row data, not just JSONB column.
    ///
    /// # Security Warning
    ///
    /// This method executes arbitrary SQL. **NEVER** pass untrusted input directly to this method.
    /// Always:
    /// - Use parameterized queries with bound parameters
    /// - Validate and sanitize SQL templates before execution
    /// - Only execute SQL generated by the FraiseQL compiler
    /// - Log SQL execution for audit trails
    ///
    /// # Arguments
    ///
    /// * `sql` - Raw SQL query to execute (must be safe/trusted)
    ///
    /// # Returns
    ///
    /// Vec of rows, where each row is a HashMap of column name to JSON value.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` on query execution failure.
    ///
    /// # Example
    ///
    /// ```rust,no_run
    /// # use fraiseql_db::DatabaseAdapter;
    /// # async fn example(adapter: impl DatabaseAdapter) -> Result<(), Box<dyn std::error::Error>> {
    /// // Safe: SQL generated by FraiseQL compiler
    /// let sql = "SELECT category, SUM(revenue) as total FROM tf_sales GROUP BY category";
    /// let rows = adapter.execute_raw_query(sql).await?;
    /// for row in rows {
    ///     println!("Category: {}, Total: {}", row["category"], row["total"]);
    /// }
    /// # Ok(())
    /// # }
    /// ```
    async fn execute_raw_query(
        &self,
        sql: &str,
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>>;

    /// Execute a row-shaped query against a view, returning typed column values.
    ///
    /// Used by the gRPC transport for protobuf encoding of query results.
    /// The default implementation delegates to `execute_raw_query` and converts
    /// JSON results to `ColumnValue` vectors.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` if the adapter returns an error.
    async fn execute_row_query(
        &self,
        view_name: &str,
        columns: &[crate::types::ColumnSpec],
        where_sql: Option<&str>,
        order_by: Option<&str>,
        limit: Option<u32>,
        offset: Option<u32>,
    ) -> Result<Vec<Vec<crate::types::ColumnValue>>> {
        use crate::types::ColumnValue;

        let mut sql = format!("SELECT * FROM \"{view_name}\"");
        if let Some(w) = where_sql {
            sql.push_str(" WHERE ");
            sql.push_str(w);
        }
        if let Some(ob) = order_by {
            sql.push_str(" ORDER BY ");
            sql.push_str(ob);
        }
        if let Some(l) = limit {
            use std::fmt::Write;
            let _ = write!(sql, " LIMIT {l}");
        }
        if let Some(o) = offset {
            use std::fmt::Write;
            let _ = write!(sql, " OFFSET {o}");
        }

        let results = self.execute_raw_query(&sql).await?;

        Ok(results
            .iter()
            .map(|row| {
                columns
                    .iter()
                    .map(|col| {
                        row.get(&col.name).map_or(ColumnValue::Null, |v| match v {
                            serde_json::Value::Null => ColumnValue::Null,
                            serde_json::Value::Bool(b) => ColumnValue::Boolean(*b),
                            serde_json::Value::Number(n) => {
                                if let Some(i) = n.as_i64() {
                                    ColumnValue::Int64(i)
                                } else if let Some(f) = n.as_f64() {
                                    ColumnValue::Float64(f)
                                } else {
                                    ColumnValue::Text(n.to_string())
                                }
                            },
                            serde_json::Value::String(s) => ColumnValue::Text(s.clone()),
                            other => ColumnValue::Json(other.to_string()),
                        })
                    })
                    .collect()
            })
            .collect())
    }

    /// Execute a parameterized aggregate SQL query (GROUP BY / HAVING / window).
    ///
    /// `sql` contains `$N` (PostgreSQL), `?` (MySQL / SQLite), or `@P1` (SQL Server)
    /// placeholders for string and array values; numeric and NULL values may be inlined.
    /// `params` are the corresponding values in placeholder order.
    ///
    /// Unlike `execute_raw_query`, this method accepts bind parameters so that
    /// user-supplied filter values never appear as string literals in the SQL text,
    /// eliminating the injection risk that `escape_sql_string` mitigated previously.
    ///
    /// # Arguments
    ///
    /// * `sql` - SQL with placeholders generated by
    ///   `AggregationSqlGenerator::generate_parameterized`
    /// * `params` - Bind parameters in placeholder order
    ///
    /// # Returns
    ///
    /// Vec of rows, where each row is a `HashMap` of column name to JSON value.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` on execution failure.
    /// Returns `FraiseQLError::Database` on adapters that do not support raw SQL
    /// (e.g., `FraiseWireAdapter`).
    async fn execute_parameterized_aggregate(
        &self,
        sql: &str,
        params: &[serde_json::Value],
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>>;

    /// Connection-affine variant of
    /// [`execute_parameterized_aggregate`](Self::execute_parameterized_aggregate).
    ///
    /// Applies `session_vars` transaction-locally on the same connection that
    /// runs the aggregate, so aggregate views backed by `current_setting()` RLS
    /// observe the configured values (fixes #329 for the aggregate path). See
    /// [`execute_function_call_with_session`](Self::execute_function_call_with_session)
    /// for the non-PostgreSQL default behaviour.
    ///
    /// # Errors
    ///
    /// Same errors as [`execute_parameterized_aggregate`](Self::execute_parameterized_aggregate);
    /// additionally returns `FraiseQLError::Database` if `set_config` fails.
    async fn execute_parameterized_aggregate_with_session(
        &self,
        sql: &str,
        params: &[serde_json::Value],
        _session_vars: &[(&str, &str)],
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        self.execute_parameterized_aggregate(sql, params).await
    }

    /// Execute a database function call and return all columns as rows.
    ///
    /// Builds `SELECT * FROM {function_name}($1, $2, ...)` with one positional placeholder per
    /// argument, executes it with the provided JSON values, and returns each result row as a
    /// `HashMap<column_name, json_value>`.
    ///
    /// Used by the mutation execution pathway to call stored procedures that return the
    /// `app.mutation_response` composite type
    /// `(status, message, entity_id, entity_type, entity jsonb, updated_fields text[],
    ///   cascade jsonb, metadata jsonb)`.
    ///
    /// # Arguments
    ///
    /// * `function_name` - Fully-qualified function name (e.g. `fn_create_machine`)
    /// * `args` - Positional JSON arguments passed as `$1, $2, …` bind parameters
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` on query execution failure.
    /// Returns `FraiseQLError::Unsupported` on adapters that do not support mutations
    /// (default implementation — see [`SupportsMutations`]).
    async fn execute_function_call(
        &self,
        function_name: &str,
        _args: &[serde_json::Value],
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        Err(FraiseQLError::Unsupported {
            message: format!(
                "Mutations via function calls are not supported by this adapter. \
                 Function '{function_name}' cannot be executed. \
                 Use PostgreSQL, MySQL, or SQL Server for mutation support."
            ),
        })
    }

    /// Returns `true` if this adapter supports GraphQL mutation operations.
    ///
    /// **This is the authoritative mutation gate.** The executor checks this method
    /// before dispatching any mutation. Adapters that return `false` will cause
    /// mutations to fail with a clear `FraiseQLError::Validation` diagnostic instead
    /// of silently calling the unsupported `execute_function_call` default.
    ///
    /// Override to return `false` for read-only adapters (e.g., `SqliteAdapter`,
    /// `FraiseWireAdapter`). The compile-time [`SupportsMutations`] marker trait
    /// complements this runtime check — see its documentation for the distinction.
    ///
    /// # Default
    ///
    /// Returns `true`. All adapters are assumed mutation-capable unless they override
    /// this method.
    fn supports_mutations(&self) -> bool {
        true
    }

    /// Bump fact table version counters after a successful mutation.
    ///
    /// Called by the executor when a mutation definition declares
    /// `invalidates_fact_tables`. For each listed table the version counter is
    /// incremented so that subsequent aggregation queries miss the cache and
    /// re-fetch fresh data.
    ///
    /// The default implementation is a **no-op**: adapters that are not cache-
    /// aware (e.g. `PostgresAdapter`, `SqliteAdapter`) simply return `Ok(())`.
    /// `CachedDatabaseAdapter` overrides this to call `bump_tf_version($1)` for
    /// every `FactTableVersionStrategy::VersionTable` table and update the
    /// in-process version cache.
    ///
    /// # Arguments
    ///
    /// * `tables` - Fact table names declared by the mutation (validated SQL identifiers; originate
    ///   from `MutationDefinition.invalidates_fact_tables`)
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` if the version-bump SQL function fails.
    async fn bump_fact_table_versions(&self, _tables: &[String]) -> Result<()> {
        Ok(())
    }

    /// Invalidate cached query results for the specified views.
    ///
    /// Called by the executor after a mutation succeeds, so that stale cache
    /// entries reading from modified views are evicted. The default
    /// implementation is a no-op; `CachedDatabaseAdapter` overrides this.
    ///
    /// View names are passed as `&[ViewName]` so the wrapper's `Arc<str>`
    /// backing is preserved across the call. Callers that hold a `String`
    /// can convert in place with `ViewName::from(...)`.
    ///
    /// # Returns
    ///
    /// The number of cache entries evicted.
    async fn invalidate_views(&self, _views: &[crate::ViewName]) -> Result<u64> {
        Ok(0)
    }

    /// Evict cache entries that contain the given entity UUID.
    ///
    /// Called by the executor after a successful UPDATE or DELETE mutation when
    /// the `mutation_response` includes an `entity_id`. Only cache entries whose
    /// entity-ID index contains the given UUID are removed; unrelated entries
    /// remain warm.
    ///
    /// The default implementation is a no-op. `CachedDatabaseAdapter` overrides
    /// this to perform the selective eviction.
    ///
    /// # Returns
    ///
    /// The number of cache entries evicted.
    async fn invalidate_by_entity(&self, _entity_type: &str, _entity_id: &str) -> Result<u64> {
        Ok(0)
    }

    /// Evict only list (multi-row) cache entries for the given views.
    ///
    /// Called by the executor after a successful CREATE mutation. Unlike
    /// `invalidate_views()`, this preserves single-entity point-lookup entries
    /// that are unaffected by the newly created entity.
    ///
    /// The default implementation delegates to `invalidate_views()` (safe
    /// fallback for adapters without a `list_index`).  `CachedDatabaseAdapter`
    /// overrides this to use the dedicated `list_index` for precise eviction.
    ///
    /// # Returns
    ///
    /// The number of cache entries evicted.
    async fn invalidate_list_queries(&self, views: &[crate::ViewName]) -> Result<u64> {
        self.invalidate_views(views).await
    }

    /// Get database capabilities.
    ///
    /// Returns information about what features this database supports,
    /// including collation strategies and limitations.
    ///
    /// # Returns
    ///
    /// `DatabaseCapabilities` describing supported features.
    fn capabilities(&self) -> DatabaseCapabilities {
        DatabaseCapabilities::from_database_type(self.database_type())
    }

    /// Run the database's `EXPLAIN` on a SQL statement without executing it.
    ///
    /// Returns a JSON representation of the query plan. The format is
    /// database-specific (e.g. PostgreSQL returns JSON, SQLite returns rows).
    ///
    /// The default implementation returns `Unsupported`.
    async fn explain_query(
        &self,
        _sql: &str,
        _params: &[serde_json::Value],
    ) -> Result<serde_json::Value> {
        Err(fraiseql_error::FraiseQLError::Unsupported {
            message: "EXPLAIN not available for this database adapter".to_string(),
        })
    }

    /// Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` against a view with the
    /// same parameterized WHERE clause that `execute_where_query` would use.
    ///
    /// Unlike `explain_query`, this method uses **real bound parameters** and
    /// **actually executes the query** (ANALYZE mode), so the plan reflects
    /// PostgreSQL's runtime statistics for the given filter values.
    ///
    /// Only PostgreSQL supports this; other adapters return
    /// `FraiseQLError::Unsupported` by default.
    ///
    /// # Arguments
    ///
    /// * `view` - View name (e.g., "v_user")
    /// * `where_clause` - Optional filter (same as `execute_where_query`)
    /// * `limit` - Optional row limit
    /// * `offset` - Optional row offset
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` on execution failure.
    /// Returns `FraiseQLError::Unsupported` for non-PostgreSQL adapters.
    async fn explain_where_query(
        &self,
        _view: &str,
        _where_clause: Option<&WhereClause>,
        _limit: Option<u32>,
        _offset: Option<u32>,
    ) -> Result<serde_json::Value> {
        Err(fraiseql_error::FraiseQLError::Unsupported {
            message: "EXPLAIN ANALYZE is not available for this database adapter. \
                      Only PostgreSQL supports explain_where_query."
                .to_string(),
        })
    }

    /// Returns the mutation strategy used by this adapter.
    ///
    /// The default is `FunctionCall` (stored procedures). Adapters that generate
    /// direct SQL (e.g., SQLite) override this to return `DirectSql`.
    fn mutation_strategy(&self) -> MutationStrategy {
        MutationStrategy::FunctionCall
    }

    /// Execute a database function call after pinning session variables on the
    /// **same connection** within the **same transaction** as the call.
    ///
    /// This is the connection-affine variant of
    /// [`execute_function_call`](Self::execute_function_call): the `set_config(..., true)`
    /// calls and the `SELECT * FROM fn(...)` call share one pooled connection inside one
    /// transaction, so transaction-local GUCs are visible to the function body (fixes #329).
    ///
    /// Adapters that do not support session variables (MySQL, SQLite, SQL
    /// Server, mocks) inherit the default implementation, which silently drops
    /// `session_vars` and delegates to [`execute_function_call`](Self::execute_function_call) —
    /// safe, because those backends never applied session variables in the first
    /// place.
    ///
    /// # Arguments
    ///
    /// * `function_name` - Fully-qualified function name
    /// * `args` - Positional JSON arguments passed as `$1, $2, …`
    /// * `session_vars` - `(setting_name, value)` pairs applied with `SELECT set_config(name,
    ///   value, true)` before the function call. Pass `&[]` when no session variables are
    ///   configured.
    ///
    /// # Errors
    ///
    /// Same as [`execute_function_call`](Self::execute_function_call); additionally returns
    /// `FraiseQLError::Database` if `set_config` fails on any pair.
    async fn execute_function_call_with_session(
        &self,
        function_name: &str,
        args: &[serde_json::Value],
        _session_vars: &[(&str, &str)],
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        // Default: ignore session_vars and delegate. Safe for non-PostgreSQL
        // adapters, which never applied session variables in the first place.
        self.execute_function_call(function_name, args).await
    }

    /// Connection-affine variant of
    /// [`execute_function_call_with_session`](Self::execute_function_call_with_session)
    /// that **also writes one `core.tb_entity_change_log` row in the same
    /// transaction** as the mutation function — the Change Spine transactional
    /// outbox.
    ///
    /// When `changelog` is `Some`, the framework owns the change-log write: a
    /// single statement runs the function and INSERTs the outbox row atomically
    /// on the same connection, so `fraiseql.started_at` (set txn-locally for the
    /// `duration_ms` computation) is visible and a crash leaves neither the
    /// mutation nor the log row. The changed-entity columns are read from the
    /// function's own `app.mutation_response` row; only the DML verb and a
    /// NOT-NULL `object_type` fallback are threaded in via [`ChangeLogWrite`].
    /// The row is written only for an effective change (`succeeded` AND
    /// `state_changed`).
    ///
    /// When `changelog` is `None`, behaviour is identical to
    /// [`execute_function_call_with_session`](Self::execute_function_call_with_session).
    ///
    /// PostgreSQL, MySQL, and SQL Server each override this with a real in-txn
    /// write. PostgreSQL runs one `MATERIALIZED` CTE that calls the function and
    /// INSERTs the outbox row atomically; MySQL and SQL Server cannot reference a
    /// `CALL`/`EXEC` result set in a following `INSERT … SELECT`, so they open a
    /// transaction, parse the `app.mutation_response` row in Rust, and INSERT the
    /// outbox row (via [`crate::changelog::build_changelog_insert_sql`]) on the same
    /// connection before commit. On those two dialects `duration_ms` / `started_at`
    /// are legitimately NULL (no request-scoped DB clock).
    ///
    /// SQLite (read-only) and mocks inherit the default below, which drops
    /// `changelog` and delegates — so those mutations still run, they just write no
    /// outbox row.
    ///
    /// # Errors
    ///
    /// Same as
    /// [`execute_function_call_with_session`](Self::execute_function_call_with_session);
    /// additionally returns `FraiseQLError::Database` if the outbox INSERT fails
    /// (e.g. the contract migration has not been applied).
    async fn execute_function_call_with_changelog(
        &self,
        function_name: &str,
        args: &[serde_json::Value],
        session_vars: &[(&str, &str)],
        _changelog: Option<&ChangeLogWrite<'_>>,
    ) -> Result<Vec<std::collections::HashMap<String, serde_json::Value>>> {
        // Default: ignore the change-log write and delegate. SQLite (read-only) and
        // mocks keep this no-op; PostgreSQL / MySQL / SQL Server override it.
        self.execute_function_call_with_session(function_name, args, session_vars).await
    }

    /// Connection-affine variant of [`execute_where_query_arc`](Self::execute_where_query_arc).
    ///
    /// Applies `session_vars` transaction-locally on the same connection that
    /// runs the read, so PostgreSQL Row-Level-Security policies backed by
    /// `current_setting()` see the configured values (fixes #329). See
    /// [`execute_function_call_with_session`](Self::execute_function_call_with_session) for the
    /// rationale and the non-PostgreSQL default behaviour.
    ///
    /// # Errors
    ///
    /// Same errors as [`execute_where_query_arc`](Self::execute_where_query_arc); additionally
    /// returns `FraiseQLError::Database` if `set_config` fails on any pair.
    async fn execute_where_query_arc_with_session(
        &self,
        view: &str,
        where_clause: Option<&WhereClause>,
        limit: Option<u32>,
        offset: Option<u32>,
        order_by: Option<&[OrderByClause]>,
        _session_vars: &[(&str, &str)],
    ) -> Result<Arc<Vec<JsonbValue>>> {
        self.execute_where_query_arc(view, where_clause, limit, offset, order_by).await
    }

    /// Connection-affine variant of
    /// [`execute_with_projection_arc`](Self::execute_with_projection_arc).
    ///
    /// See [`execute_where_query_arc_with_session`](Self::execute_where_query_arc_with_session) for
    /// the rationale.
    ///
    /// # Errors
    ///
    /// Same errors as [`execute_with_projection_arc`](Self::execute_with_projection_arc);
    /// additionally returns `FraiseQLError::Database` if `set_config` fails on any pair.
    async fn execute_with_projection_arc_with_session(
        &self,
        request: &ProjectionRequest<'_>,
        _session_vars: &[(&str, &str)],
    ) -> Result<Arc<Vec<JsonbValue>>> {
        self.execute_with_projection_arc(request).await
    }

    /// Execute a direct SQL mutation (INSERT/UPDATE/DELETE) and return the
    /// mutation response rows as JSON objects.
    ///
    /// Only adapters using `MutationStrategy::DirectSql` need to override this.
    /// The default implementation returns `Unsupported`.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Unsupported` by default.
    /// Returns `FraiseQLError::Database` on SQL execution failure.
    /// Returns `FraiseQLError::Validation` on invalid mutation parameters.
    async fn execute_direct_mutation(
        &self,
        _ctx: &DirectMutationContext<'_>,
    ) -> Result<Vec<serde_json::Value>> {
        Err(FraiseQLError::Unsupported {
            message: "Direct SQL mutations are not supported by this adapter. \
                      Use execute_function_call for stored-procedure mutations."
                .to_string(),
        })
    }

    /// Retrieve query performance statistics from the database.
    ///
    /// Returns the top-N queries ordered by total execution time (descending).
    /// The exact data source depends on the backend:
    /// - PostgreSQL: `pg_stat_statements` (requires extension)
    /// - MySQL: `performance_schema.events_statements_summary_by_digest`
    /// - SQL Server: `sys.dm_exec_query_stats`
    /// - SQLite / Wire: empty (no stats available)
    ///
    /// # Arguments
    ///
    /// * `limit` - Maximum number of entries to return.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` if the stats query fails.
    async fn query_stats(&self, _limit: u32) -> Result<Vec<crate::types::QueryStatEntry>> {
        Ok(vec![])
    }

    /// Retrieve statistics for a single query by its ID.
    ///
    /// The default implementation fetches up to 1000 entries via
    /// [`query_stats`](Self::query_stats) and filters client-side.
    /// Backends with efficient single-query lookup (PostgreSQL, SQL Server)
    /// should override with a `WHERE` clause.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Database` if the underlying query fails.
    async fn query_stats_by_id(&self, id: &str) -> Result<Option<crate::types::QueryStatEntry>> {
        let stats = self.query_stats(1000).await?;
        Ok(stats.into_iter().find(|e| e.query_id == id))
    }

    /// Reset query performance statistics.
    ///
    /// Only PostgreSQL supports this (via `pg_stat_statements_reset()`).
    /// All other adapters return `Unsupported`.
    ///
    /// # Errors
    ///
    /// Returns `FraiseQLError::Unsupported` for adapters that cannot reset stats.
    /// Returns `FraiseQLError::Database` if the reset command fails.
    async fn reset_query_stats(&self) -> Result<()> {
        Err(FraiseQLError::Unsupported {
            message: "Query stats reset is not supported by this database adapter".to_string(),
        })
    }

    /// Notify the adapter that the schema has changed.
    ///
    /// Called during hot-reload after the new schema has been validated.
    /// Adapters that maintain schema-dependent state (e.g. cache keyed by schema
    /// version) should clear or rebuild that state here.
    ///
    /// The default implementation is a no-op.
    fn on_schema_reload(&self) {}
}

#[cfg(test)]
mod tests;