---
name: Unified AthenaClient SDK
overview: Design and implement a unified AthenaClient SDK with builder pattern that supports multiple database backends (Supabase, PostgreSQL, Scylla, Neon) with a type-safe query builder interface and flexible configuration options.
todos:
  - id: backend-trait
    content: Create backend trait system and enum in src/client/backend.rs
    status: completed
  - id: config-structs
    content: Define configuration structs in src/client/config.rs
    status: completed
  - id: builder-pattern
    content: Implement AthenaClientBuilder with fluent API in src/client/builder.rs
    status: completed
  - id: query-builders
    content: Create unified query builders (Select, Insert, Update, Delete) in src/client/query_builder.rs
    status: completed
  - id: query-translator
    content: Implement SQL and CQL query translators in src/client/translator.rs
    status: completed
  - id: supabase-backend
    content: Wrap HealthAwareSupabaseClient as SupabaseBackend in src/client/backends/supabase.rs
    status: completed
  - id: postgres-backend
    content: Create PostgresBackend using sqlx in src/client/backends/postgres.rs
    status: completed
  - id: scylla-backend
    content: Wrap existing Scylla client as ScyllaBackend in src/client/backends/scylla.rs
    status: completed
  - id: main-client
    content: Implement main AthenaClient with convenience methods in src/client/mod.rs
    status: completed
  - id: error-types
    content: Define client-specific error types in src/client/error.rs
    status: completed
  - id: lib-exports
    content: Add public exports to src/lib.rs
    status: completed
  - id: examples
    content: Create examples showing unified API usage
    status: completed
  - id: documentation
    content: Update README with SDK usage guide and migration instructions
    status: completed
isProject: false
---

# Unified AthenaClient SDK Design

## Architecture Overview

Create a unified client interface that abstracts multiple database backends while preserving backend-specific capabilities. The design follows a builder pattern with a type-safe query builder that works across all backends.

```mermaid
graph TB
    User[User Code] --> Builder[AthenaClientBuilder]
    Builder --> Backend{Backend Type}
    Backend -->|Supabase| SupabaseBackend[SupabaseBackend]
    Backend -->|PostgreSQL| PostgresBackend[PostgresBackend]
    Backend -->|Scylla| ScyllaBackend[ScyllaBackend]
    Backend -->|Neon| NeonBackend[NeonBackend]

    SupabaseBackend --> QueryBuilder[Unified QueryBuilder]
    PostgresBackend --> QueryBuilder
    ScyllaBackend --> QueryBuilder
    NeonBackend --> QueryBuilder

    QueryBuilder --> Executor{Query Executor}
    Executor -->|SQL| SQLExecutor[SQL Executor]
    Executor -->|CQL| CQLExecutor[CQL Executor]
```

## Core Components

### 1. Backend Enum and Trait System

**File: `[src/client/backend.rs](src/client/backend.rs)`**

Define a `Backend` enum and trait that all backends must implement:

```rust
pub enum BackendType {
    Native,
    Supabase,
    Postgrest,
    Scylla,
    Neon,
    PostgreSQL,
}

pub trait DatabaseBackend: Send + Sync {
    async fn execute_query(&self, query: Query) -> Result<QueryResult>;
    async fn health_check(&self) -> Result<HealthStatus>;
    fn backend_type(&self) -> BackendType;
    fn supports_sql(&self) -> bool;
    fn supports_cql(&self) -> bool;
}
```

### 2. Builder Pattern Implementation

**File: `[src/client/builder.rs](src/client/builder.rs)`**

Create a fluent builder API:

```rust
pub struct AthenaClientBuilder {
    backend: Option<BackendType>,
    url: Option<String>,
    key: Option<String>,
    // Connection options
    ssl: Option<bool>,
    port: Option<u16>,
    database: Option<String>,
    // Pool options
    max_connections: Option<u32>,
    min_connections: Option<u32>,
    connection_timeout: Option<Duration>,
    // Health tracking
    enable_health_tracking: bool,
    circuit_breaker_threshold: Option<u32>,
}

impl AthenaClientBuilder {
    pub fn new() -> Self { /* ... */ }

    pub fn backend(mut self, backend: BackendType) -> Self { /* ... */ }
    pub fn url(mut self, url: impl Into<String>) -> Self { /* ... */ }
    pub fn key(mut self, key: impl Into<String>) -> Self { /* ... */ }

    // Connection configuration
    pub fn ssl(mut self, enabled: bool) -> Self { /* ... */ }
    pub fn port(mut self, port: u16) -> Self { /* ... */ }
    pub fn database(mut self, name: impl Into<String>) -> Self { /* ... */ }

    // Pool configuration
    pub fn max_connections(mut self, max: u32) -> Self { /* ... */ }
    pub fn min_connections(mut self, min: u32) -> Self { /* ... */ }
    pub fn connection_timeout(mut self, timeout: Duration) -> Self { /* ... */ }

    // Health tracking
    pub fn health_tracking(mut self, enabled: bool) -> Self { /* ... */ }
    pub fn circuit_breaker_threshold(mut self, threshold: u32) -> Self { /* ... */ }

    pub async fn build(self) -> Result<AthenaClient> { /* ... */ }
}
```

### 3. Main Client Interface

**File: `[src/client/mod.rs](src/client/mod.rs)`**

The unified client that delegates to backend implementations:

```rust
pub struct AthenaClient {
    backend: Box<dyn DatabaseBackend>,
    config: ClientConfig,
}

impl AthenaClient {
    pub fn builder() -> AthenaClientBuilder { /* ... */ }

    // Convenience constructors
    pub async fn supabase(url: impl Into<String>, key: impl Into<String>) -> Result<Self> {
        Self::builder()
            .backend(BackendType::Supabase)
            .url(url)
            .key(key)
            .build()
            .await
    }

    pub async fn scylla(url: impl Into<String>, username: impl Into<String>, password: impl Into<String>) -> Result<Self> {
        Self::builder()
            .backend(BackendType::Scylla)
            .url(url)
            .key(password)
            .build()
            .await
    }

    // Query interface
    pub fn select(&self, table: &str) -> SelectBuilder { /* ... */ }
    pub fn insert(&self, table: &str) -> InsertBuilder { /* ... */ }
    pub fn update(&self, table: &str) -> UpdateBuilder { /* ... */ }
    pub fn delete(&self, table: &str) -> DeleteBuilder { /* ... */ }

    // Raw query execution
    pub async fn execute_sql(&self, sql: &str) -> Result<QueryResult> { /* ... */ }
    pub async fn execute_cql(&self, cql: &str) -> Result<QueryResult> { /* ... */ }

    // Health and connection management
    pub async fn health_check(&self) -> Result<HealthStatus> { /* ... */ }
    pub fn is_offline(&self) -> Option<Instant> { /* ... */ }
}
```

### 4. Unified Query Builder

**File: `[src/client/query_builder.rs](src/client/query_builder.rs)`**

Type-safe query builder that translates to backend-specific queries:

```rust
pub struct SelectBuilder<'a> {
    client: &'a AthenaClient,
    table: String,
    columns: Vec<String>,
    conditions: Vec<Condition>,
    order_by: Vec<OrderBy>,
    limit: Option<usize>,
    offset: Option<usize>,
}

impl<'a> SelectBuilder<'a> {
    pub fn columns(mut self, cols: impl IntoIterator<Item = impl Into<String>>) -> Self { /* ... */ }

    // Filtering
    pub fn where_eq(mut self, column: &str, value: impl Into<Value>) -> Self { /* ... */ }
    pub fn where_neq(mut self, column: &str, value: impl Into<Value>) -> Self { /* ... */ }
    pub fn where_gt(mut self, column: &str, value: impl Into<Value>) -> Self { /* ... */ }
    pub fn where_lt(mut self, column: &str, value: impl Into<Value>) -> Self { /* ... */ }
    pub fn where_in(mut self, column: &str, values: Vec<Value>) -> Self { /* ... */ }

    // Ordering and pagination
    pub fn order_by(mut self, column: &str, direction: OrderDirection) -> Self { /* ... */ }
    pub fn limit(mut self, limit: usize) -> Self { /* ... */ }
    pub fn offset(mut self, offset: usize) -> Self { /* ... */ }

    pub async fn execute(self) -> Result<Vec<Value>> { /* ... */ }
}
```

Similar builders for `InsertBuilder`, `UpdateBuilder`, and `DeleteBuilder`.

### 5. Backend Implementations

**File: `[src/client/backends/supabase.rs](src/client/backends/supabase.rs)`**

Wrap existing `HealthAwareSupabaseClient` to implement `DatabaseBackend` trait:

```rust
pub struct SupabaseBackend {
    inner: HealthAwareSupabaseClient,
}

impl DatabaseBackend for SupabaseBackend {
    async fn execute_query(&self, query: Query) -> Result<QueryResult> {
        // Translate Query to Supabase SDK calls
    }

    fn backend_type(&self) -> BackendType {
        BackendType::Supabase
    }

    fn supports_sql(&self) -> bool { true }
    fn supports_cql(&self) -> bool { false }
}
```

**File: `[src/client/backends/scylla.rs](src/client/backends/scylla.rs)`**

Wrap existing Scylla client logic:

```rust
pub struct ScyllaBackend {
    session: Arc<Session>,
    info: ScyllaConnectionInfo,
    tracker: &'static HostHealthTracker<SystemClock>,
}

impl DatabaseBackend for ScyllaBackend {
    async fn execute_query(&self, query: Query) -> Result<QueryResult> {
        // Translate Query to CQL
    }

    fn backend_type(&self) -> BackendType {
        BackendType::Scylla
    }

    fn supports_sql(&self) -> bool { false }
    fn supports_cql(&self) -> bool { true }
}
```

**File: `[src/client/backends/postgres.rs](src/client/backends/postgres.rs)`**

Use existing `PostgresClientRegistry` or create direct connection:

```rust
pub struct PostgresBackend {
    pool: PgPool,
}

impl DatabaseBackend for PostgresBackend {
    async fn execute_query(&self, query: Query) -> Result<QueryResult> {
        // Translate Query to SQL
    }

    fn backend_type(&self) -> BackendType {
        BackendType::PostgreSQL
    }

    fn supports_sql(&self) -> bool { true }
    fn supports_cql(&self) -> bool { false }
}
```

### 6. Configuration System

**File: `[src/client/config.rs](src/client/config.rs)`**

Backend-specific configuration that can be loaded from YAML or environment:

```rust
pub struct ClientConfig {
    pub backend_type: BackendType,
    pub connection: ConnectionConfig,
    pub pool: PoolConfig,
    pub health: HealthConfig,
}

pub struct ConnectionConfig {
    pub url: String,
    pub key: Option<String>,
    pub ssl: bool,
    pub port: Option<u16>,
    pub database: Option<String>,
}

pub struct PoolConfig {
    pub max_connections: u32,
    pub min_connections: u32,
    pub connection_timeout: Duration,
    pub idle_timeout: Duration,
}

pub struct HealthConfig {
    pub enabled: bool,
    pub check_interval: Duration,
    pub circuit_breaker_threshold: u32,
    pub circuit_breaker_timeout: Duration,
}
```

### 7. Query Translation Layer

**File: `[src/client/translator.rs](src/client/translator.rs)`**

Translate unified query builder to backend-specific queries:

```rust
pub trait QueryTranslator {
    fn translate_select(&self, builder: &SelectBuilder) -> String;
    fn translate_insert(&self, builder: &InsertBuilder) -> String;
    fn translate_update(&self, builder: &UpdateBuilder) -> String;
    fn translate_delete(&self, builder: &DeleteBuilder) -> String;
}

pub struct SqlTranslator;
pub struct CqlTranslator;

impl QueryTranslator for SqlTranslator {
    fn translate_select(&self, builder: &SelectBuilder) -> String {
        // Generate SQL SELECT
    }
}

impl QueryTranslator for CqlTranslator {
    fn translate_select(&self, builder: &SelectBuilder) -> String {
        // Generate CQL SELECT with Scylla-specific syntax
    }
}
```

## Usage Examples

### Basic Usage

```rust
use athena_rs::AthenaClient;

// Supabase
let client = AthenaClient::builder()
    .backend(BackendType::Supabase)
    .url("https://xxx.supabase.co")
    .key("your-key")
    .build()
    .await?;

// Or convenience method
let client = AthenaClient::supabase("https://xxx.supabase.co", "your-key").await?;

// Query
let users = client.select("users")
    .columns(["id", "email", "name"])
    .where_eq("status", "active")
    .order_by("created_at", OrderDirection::Desc)
    .limit(10)
    .execute()
    .await?;
```

### Advanced Configuration

```rust
let client = AthenaClient::builder()
    .backend(BackendType::Scylla)
    .url("127.0.0.1:9042")
    .key("password")
    .ssl(true)
    .port(9042)
    .max_connections(100)
    .min_connections(10)
    .connection_timeout(Duration::from_secs(5))
    .health_tracking(true)
    .circuit_breaker_threshold(5)
    .build()
    .await?;
```

### Backend-Specific Queries

```rust
// For SQL backends
let result = client.execute_sql("SELECT * FROM users WHERE id = $1").await?;

// For CQL backends
let result = client.execute_cql("SELECT * FROM users WHERE id = ?").await?;
```

## File Structure

```
src/
├── client/
│   ├── mod.rs              # Main AthenaClient
│   ├── builder.rs          # AthenaClientBuilder
│   ├── backend.rs          # Backend trait and enum
│   ├── config.rs           # Configuration structs
│   ├── query_builder.rs    # Unified query builders
│   ├── translator.rs       # Query translation layer
│   ├── backends/
│   │   ├── mod.rs
│   │   ├── supabase.rs     # SupabaseBackend
│   │   ├── postgres.rs     # PostgresBackend
│   │   ├── scylla.rs       # ScyllaBackend
│   │   └── neon.rs         # NeonBackend
│   └── error.rs            # Client-specific errors
├── drivers/                # Keep existing drivers
│   ├── supabase/
│   ├── postgresql/
│   └── scylla/
└── lib.rs                  # Export AthenaClient
```

## Migration Strategy

1. Keep existing driver implementations in `src/drivers/` unchanged
2. Create new `src/client/` module with unified interface
3. Backend implementations wrap existing drivers
4. Add re-exports in `src/lib.rs`:

```rust
   pub use client::{AthenaClient, AthenaClientBuilder, BackendType};


```

1. Update examples to show both old and new API
2. Update README with migration guide

## Benefits

- **Unified Interface**: Single API across all backends
- **Type Safety**: Compile-time query validation where possible
- **Flexibility**: Builder pattern allows granular configuration
- **Backward Compatible**: Existing drivers remain unchanged
- **Extensible**: Easy to add new backends by implementing trait
- **Health Tracking**: Built-in circuit breaker for all backends
- **Performance**: Zero-cost abstractions where possible

## Implementation Notes

- Use `async_trait` for `DatabaseBackend` trait
- Leverage existing health tracking from `src/drivers/scylla/health.rs`
- Query builder should validate at build time when possible
- Connection pooling should be configurable per backend
- Error types should preserve backend-specific error information
- Consider adding connection string parsing for convenience
