athena_rs 3.3.0

# Athena RS 2.8.1

current version: `3.3.0`
## Table of Contents

- [Features](#features)
- [Overview](#overview)
- [Quickstart](#quickstart)
- [Architecture](#architecture)
- [API Reference](#api-reference)
- [CLI Tools](#cli-tools)
- [SDK](#sdk)
- [Supported Drivers](#supported-drivers)
- [Configuration](#configuration)
- [Realtime Connection Registry](#realtime-connection-registry)
- [WebSocket Gateway](#websocket-gateway)
- [Web Explorer](#web-explorer)
- [Docker](#docker)
- [Testing & Linting](#testing--linting)
- [Contributing](#contributing)

## Features

### 1. Gateway CRUD Operations

Athena exposes five primary gateway endpoints that accept structured JSON payloads and route requests to the appropriate database backend. `POST /gateway/fetch` and `POST /gateway/data` retrieve rows from a named table or view, with support for column selection, filter conditions, ordering, pagination, and optional aggregation. `PUT /gateway/insert` inserts one or more rows and returns the inserted record. `POST /gateway/update` applies field-level changes to rows matching supplied conditions. `DELETE /gateway/delete` removes rows matching conditions. `POST /gateway/query` executes an arbitrary SQL statement and streams the result set back. All five operations share the same `X-Athena-Client` header-based routing mechanism so the caller selects the target database pool per request.

### 2. Multi-Backend Routing

Every gateway request resolves its target backend through the client routing registry. Three backend families are supported. PostgreSQL clients are managed through SQLx connection pools and a secondary deadpool-experimental path for high-concurrency scenarios. ScyllaDB or Apache Cassandra clusters are addressed with native CQL using the Scylla driver. Supabase projects are reached through their REST and RPC interfaces, with a health-aware circuit-breaker wrapping each client so a degraded Supabase project does not block the rest of the gateway.

### 3. Online Client Catalog with In-Memory Registry

Client definitions are stored in two layers. The `config.yaml` file provides bootstrap-time client definitions with connection URIs expressed as environment variable tokens. The `athena_clients` table in the `athena_logging` database extends this with database-backed rows that can be created, updated, frozen, or deleted at runtime without a server restart. Both sources are merged into a local in-memory registry on startup so hot-path request handlers never query the catalog for every call. When a new client is added via `POST /admin/clients`, Athena establishes the pool and inserts the entry into the registry immediately. Frozen or deleted rows are evicted from the registry without requiring a restart.

### 4. Response Post-Processing (Grouping and Aggregation)

Fetch and update responses support an optional post-processing stage controlled by request body fields. When `group_by` is provided alongside optional `time_granularity`, `aggregation_column`, `aggregation_strategy`, and `aggregation_dedup` fields, Athena groups the raw result set in-process and returns a `post_processing.grouped` array. Each entry in the grouped array contains a label, the matching rows, and aggregation metadata such as the computed aggregate value and the deduplication count. Time granularity options allow grouping by minute, hour, day, week, or month.

### 5. Deferred Query Queue

When the admission control rate limiter is triggered or when the caller sets the `X-Athena-Defer: true` header on a gateway query, Athena enqueues the request as a deferred job in the `athena_logging` database. A background worker polls the queue at a configurable interval (`gateway.deferred_query_worker_poll_ms`) and executes each job in turn, retrying up to a configurable maximum (`ATHENA_DEFERRED_MAX_ATTEMPTS`, default 3). The caller can check job status with `GET /gateway/deferred/{request_id}` and resubmit a failed or completed job with `POST /gateway/deferred/{request_id}/requeue`. All five gateway operations (fetch, insert, update, delete, query) are eligible for deferral.

### 6. Admission Control and Rate Limiting

Gateway requests can be gated by a token-bucket rate limiter configured in `config.yaml`. The `gateway.admission_limit_enabled` flag enables the limiter. `gateway.admission_global_requests_per_window` and `gateway.admission_per_client_requests_per_window` set the burst caps at the global and per-client level respectively. `gateway.admission_window_secs` defines the rolling window. When a request exceeds its quota, Athena can either reject it immediately with a 429 response or automatically defer it to the queue by setting `gateway.admission_defer_on_limit_enabled` to true and optionally scoping deferral to specific route prefixes via `gateway.admission_defer_route_prefixes`. All admission events are persisted to the `admission_events` table and can be inspected via `GET /admin/admission-events`.

### 7. In-Process Cache with Optional Redis Write-Through

Athena maintains a local in-memory cache built on the `moka` library. Cache entries are keyed by client name, table name, and request parameters, and are invalidated automatically when a write operation targets the same table. A Redis write-through layer can be enabled by setting `ATHENA_REDIS_URL`, which makes the cache survive server restarts and work across multiple Athena instances pointing at the same Redis instance. TTL management and manual invalidation are available through the internal cache helpers. The `/ping` endpoint also benefits from short-duration caching to reduce logging overhead on frequent health checks.

### 8. API Key Management and Authentication

Athena implements a full API key lifecycle backed by the `api_keys` family of tables in `athena_logging`. Keys are created with `POST /admin/api-keys` and returned as a single-use plaintext credential in the format `ath_{public}.{secret}`; the secret cannot be retrieved after creation. Each key can be scoped to a single `client_name`, given an optional expiry timestamp, and assigned a set of capability rights. Rights are defined with `POST /admin/api-key-rights` and bridged to keys through `api_key_right_grants`. Global enforcement is toggled with `PUT /admin/api-key-config`, and per-client overrides are managed through the `/admin/api-key-clients/{client_name}` endpoints. Every authentication attempt is recorded in `api_key_auth_log` with the request metadata, the presented credential hash, the granted rights, and any failure reason. Keys are presented through any of the `Authorization: Bearer`, `apikey`, `x-api-key`, `x-athena-key` headers, or the `api_key` query parameter.

### 9. Client Statistics and Operation Drilldown

Athena aggregates request and operation counts per registered client and exposes them through the admin API. `GET /admin/clients/statistics` returns a summary across all clients. `GET /admin/clients/{client_name}/statistics` breaks down totals and per-table operation counts for a single client. `GET /admin/clients/{client_name}/statistics/drilldown` lists individual `gateway_operation_log` rows for a specific table and operation type, with optional filters for status, limit, and offset. `POST /admin/clients/statistics/refresh` rebuilds the `client_statistics` and `client_table_statistics` materialized rows from the raw log tables on demand.

### 10. S3-Compatible Backup and Restore

Athena can back up any registered Postgres client to an S3-compatible object store using `pg_dump` in directory format. `POST /admin/backups` launches an async backup job that streams `pg_dump` output and uploads the archive to the configured bucket. `GET /admin/backups` lists stored backup objects. `GET /admin/backups/{key}/download` downloads a backup archive. `POST /admin/backups/{key}/restore` downloads the archive and runs `pg_restore` against the target database. `DELETE /admin/backups/{key}` deletes the stored object. `POST /admin/backups/jobs/{id}/cancel` terminates an in-flight backup or restore worker, marks the job as cancelled in `athena_logging`, and allows the row to be deleted from the UI. Supported storage backends include AWS S3, MinIO, DigitalOcean Spaces, and Hetzner Object Storage. The `pg_dump` and `pg_restore` binaries are resolved from `ATHENA_PG_DUMP_PATH` / `ATHENA_PG_RESTORE_PATH`, from `PATH`, or downloaded automatically when `ATHENA_PG_TOOLS_ALLOW_DOWNLOAD` is not disabled.

### 11. Backup Schedules

Backup runs can be automated through a schedule table. `GET /admin/backups/schedules` lists all defined schedules. `POST /admin/backups/schedules` creates a new schedule by specifying a client name, cron expression, target bucket configuration, and retention policy. `PATCH /admin/backups/schedules/{id}` updates a schedule's parameters or enables and disables it. `DELETE /admin/backups/schedules/{id}` removes a schedule. The background scheduler checks due entries and triggers the same backup path as the manual endpoint.

### 12. Vacuum Health Monitoring

A background collector periodically samples `pg_stat_user_tables` and the transaction ID age from each registered Postgres client. Aggregated snapshots are persisted in `vacuum_health_snapshots` and per-table rows in `vacuum_health_table_stats` in the `athena_logging` database. `GET /admin/vacuum-health` returns the latest stored snapshot for every client. `GET /admin/vacuum-health/{client_name}` returns the detailed snapshot for a single client including per-table bloat estimates and transaction wraparound ages. These endpoints require the static admin key and are intended for monitoring dashboards and operator runbooks.

### 13. Query Optimization Recommendations

Athena analyzes slow query logs and index usage statistics for each registered Postgres client and surfaces optimization recommendations. `POST /admin/clients/{client_name}/query-optimizations/refresh` triggers a fresh analysis pass and persists the results. `GET /admin/clients/{client_name}/query-optimizations` lists recommendations filtered by optional status (pending, applied, dismissed). `GET /admin/clients/{client_name}/query-optimizations/runs` lists the historical analysis runs with timestamps and recommendation counts. `POST /admin/clients/{client_name}/query-optimizations/{recommendation_id}/apply` executes the recommended DDL change (such as creating a missing index) on the target client and records the outcome.

### 14. Schema Provisioning

`POST /admin/provision` applies the Athena logging schema to any reachable Postgres URI or registered client, creating all required tables (`gateway_operation_log`, `api_keys`, `athena_clients`, etc.) idempotently. An optional `register` flag causes the provisioned URI to be added to the runtime client catalog under a given name. `GET /admin/provision/status` checks which Athena schema tables are present on a client. `GET /admin/provision/local/databases` and `POST /admin/provision/local/databases` list and create databases on a locally reachable Postgres server. `POST /admin/provision/local/pipeline` runs a provisioning pipeline for batch database setup.

### 15. Cloud Provider Provisioning

Athena can provision new managed Postgres instances from three cloud providers directly through the admin API. `POST /admin/provision/providers/neon` creates a Neon project, retrieves the connection URI, and optionally registers the client. `POST /admin/provision/providers/railway` creates a Railway project, service, and plugin, retrieves the connection URI from Railway's GraphQL API, and registers the client. `POST /admin/provision/providers/render` creates a Render PostgreSQL service, polls for readiness, and registers the client. All three flows use authenticated provider API tokens supplied in the request body and can target an existing project when an ID is provided.

### 16. Docker Postgres Instance Lifecycle

For local development and test environments, Athena can spin up and manage Docker-based Postgres containers. `POST /admin/provision/instances` launches a new container from a configurable image with specified credentials, port, and volume settings. `GET /admin/provision/instances` lists all managed containers and their status. `GET /admin/provision/instances/{container_name}` inspects a single container. `POST /admin/provision/instances/{container_name}/start` and `/stop` start and stop a named container. `POST /admin/provision/instances/{container_name}/bindings` saves host-port route bindings for public Postgres URIs. `DELETE /admin/provision/instances/{container_name}` removes the container and optionally deregisters the associated client.

### 17. Database Management API (Tables, Columns, Indexes, Extensions)

The `/management` surface exposes DDL operations that are gated by client-bound API key rights rather than the static admin key. `POST /management/tables` creates a new table from a typed column definition, with support for primary keys, unique constraints, not-null, default values, and foreign keys. `PATCH /management/tables/{table_name}` alters an existing table by adding columns, renaming columns, or changing column types. `DELETE /management/tables/{table_name}` drops a table. `DELETE /management/tables/{table_name}/columns/{column_name}` drops a specific column. `POST /management/indexes` creates a B-tree, GIN, GiST, or other index on a named column set. `DELETE /management/indexes/{index_name}` drops an index. `POST /management/extensions/install` installs a Postgres extension (from an allowlist) on the target database. `GET /management/capabilities` reports which management rights the presenting key holds.

### 18. PostgREST-Compatible REST Interface

Athena exposes a `/rest/v1/{table}` surface that mirrors the PostgREST HTTP contract. `GET /rest/v1/{table}` retrieves rows using query-string filters in PostgREST syntax. `POST /rest/v1/{table}` inserts rows. `PATCH /rest/v1/{table}` updates rows matching the supplied query-string filter. `DELETE /rest/v1/{table}` deletes matching rows. This allows tooling built against the Supabase PostgREST interface to work against Athena-managed Postgres databases without modification.

### 19. Pipeline ETL Engine

Athena includes a config-driven ETL pipeline system. `POST /pipelines` executes a pipeline defined inline in the request body or referenced by name from `config/pipelines.yaml`. Each pipeline consists of a source stage (a gateway fetch against any registered client), an optional transform stage (column renaming, value mapping, or row filtering), and a sink stage (a gateway insert into any registered client). `POST /pipelines/simulate` runs the pipeline through the source and transform stages and returns the transformed rows without executing the sink. `GET /pipelines/templates` lists all named pipeline definitions loaded from the pipelines config file. Pipelines reuse the same gateway auth and client routing as direct gateway calls.

### 20. WebSocket Gateway

Athena ships a WebSocket endpoint at `/wss/gateway` that mirrors all five gateway CRUD and query operations over a persistent connection. The WebSocket contract is described in `openapi-wss.yaml` (served at `GET /openapi-wss.yaml`) and follows a request-response envelope pattern with `action` fields (`gateway.fetch`, `gateway.insert`, `gateway.update`, `gateway.delete`, `gateway.query`). A companion endpoint at `GET /wss/info` returns metadata about the WebSocket server including supported actions and protocol version. The Bun-based WebSocket proxy also exposes `/health` and `/ready` endpoints described in the same spec.

### 21. Wildcard Host Routing

Athena supports routing incoming requests to different client registries based on the HTTP `Host` header. When `ATHENA_WILDCARD_HOST_PATTERN` is set to a pattern such as `*.v3.athena-db.com`, any request arriving on a matching subdomain has its prefix label extracted and resolved against the active public route entries. If a matching `public_routes` entry with `active = true` is found, its `client_name` is used automatically, eliminating the need for the caller to include an `X-Athena-Client` header. Wildcard routing can be enabled independently of the pattern with `ATHENA_WILDCARD_HOST_ROUTING_ENABLED=true`.

### 22. Public Routes Dispatch

Named public routes allow external callers to execute gateway operations through a stable URL without knowing internal client names or table structures. `POST /admin/public-routes` creates a route by binding a `route_key` to a `client_name`, `table_name`, and allowed operation type. `PATCH /admin/public-routes/{route_key}` updates the binding. `DELETE /admin/public-routes/{route_key}` soft-deletes the route. `GET /admin/public-routes` lists all route definitions. Incoming requests to `POST /public/{route_key}/{op}` dispatch the gateway operation defined by the route without requiring caller knowledge of the underlying client or table, making it suitable for exposing controlled data access endpoints to third-party consumers.

### 23. Prometheus Metrics

`GET /metrics` returns a Prometheus-format scrape endpoint with counters, histograms, and gauges covering the full operational surface. Exported metrics include total request counts and error rates by route, request duration histograms with 15 configurable latency buckets, in-flight and peak in-flight request gauges per handler, gateway operation counts by client and operation type, deferred queue depth by status (queued, running, failed, completed), oldest queued job age in seconds, deferred queue storage availability, and per-client cluster probe latency and availability. These metrics feed directly into the Prometheus alerting rules documented in the README.

### 24. Schema Introspection

`GET /schema/tables` returns the list of tables visible to the requesting client with column counts and metadata. `GET /schema/columns` returns the column definitions for a specific table including data types, nullability, and default values. `GET /schema/migrations` lists schema migrations tracked in the `schema_migrations` table with applied timestamps. `GET /schema/clients` and `GET /clients` return the set of registered client names visible to the requesting key. These endpoints are intended for tooling that needs to auto-discover the database schema, such as the Web Explorer and the TypeScript CLI.

### 25. SQL Executor

`POST /query/sql` accepts a raw SQL string and executes it against the backend selected by `X-Athena-Client`. The same client routing registry used by the gateway endpoints resolves whether execution goes to a PostgreSQL pool, ScyllaDB, or Supabase. Trailing semicolons are stripped to avoid empty statement errors. `POST /gateway/sql` is an alias that follows the gateway envelope pattern. Parameterized queries use driver-level binding to prevent SQL injection.

### 26. Row Count Endpoint

`POST /query/count` accepts either a fully formed `SELECT COUNT(*)` SQL string or a structured `table_name` plus optional `table_schema` and returns the scalar row count. Results are cached under a `query_count:` prefix key using the same moka/Redis cache as gateway reads, reducing repeated COUNT queries on frequently accessed tables.

### 27. Rust SDK (AthenaClient)

The `AthenaClient` in `src/client/` provides a typed, builder-based interface for calling gateway endpoints from Rust. Callers construct a client with `AthenaClient::builder()`, supply the base URL and API key, and chain typed request builders for fetch, insert, update, delete, SQL, and RPC operations. Filter operators include `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `in`, `like`, `ilike`, and `is` (with `count_exact` on set-returning RPC results). Result shaping accepts `order_by`/`order`, `limit`, and `offset`. The SDK maps directly to the canonical `Gateway::*_PATH` constants and is verified by the end-to-end suite in `tests/sdk_gateway_examples_e2e.rs` covering CRUD paths, filter operators, and result shaping options.

### 28. TypeScript CLI

The `cli/` directory contains a TypeScript command-line tool that generates SQL and CQL schema artifacts from Supabase project metadata. The `seed` command queries the Supabase schema API and emits `CREATE TABLE` SQL and corresponding CQL definitions, with optional inclusion of constraint SQL. The `constraints` command generates only constraint definitions. The `table-registry` command emits Drizzle ORM registry files for use in TypeScript applications. Supabase credentials can be provided on the command line or through `ATHENA_SUPABASE_URL`, `ATHENA_SUPABASE_ANON_KEY`, `SUPABASE_URL`, and `SUPABASE_ANON_KEY` environment variables.

### 29. Web Explorer UI

The `apps/web/` directory contains a Next.js application that provides a visual interface for interacting with Athena-managed databases. The UI connects to any Athena server by setting `NEXT_PUBLIC_ATHENA_BASE_URL` and `NEXT_PUBLIC_ATHENA_CLIENT`. It exposes a data grid for browsing and editing table rows, a query editor for raw SQL, a schema browser for inspecting tables and columns, and backup management screens for triggering and monitoring backup and restore jobs. CSV import is supported through the `papaparse` library. The backup recovery feature flag `NEXT_PUBLIC_ATHENA_BACKUP_AUTO_RECOVERY=1` enables automatic role OID repair during restore.

### 30. OpenAPI Specification Serving

Athena serves its own REST OpenAPI specification at `GET /openapi.yaml` and its WebSocket contract at `GET /openapi-wss.yaml`. Both documents can be imported directly into any OpenAPI-compatible tooling such as Insomnia, Postman, or Swagger UI. The HTML documentation viewer is available at `GET /docs`. The router registry at `GET /router/registry` lists all registered route handlers and their HTTP methods for runtime introspection.

### 31. Client Configuration Overrides

Per-client configuration rows allow operators to tune gateway behavior independently for each registered client without changing the global `config.yaml`. `GET /admin/client-configs` lists all stored configurations. `GET /admin/client-configs/{client_name}` retrieves the configuration for one client. `PUT /admin/client-configs/{client_name}` upserts a configuration row with fields such as pool size limits, timeout overrides, and feature flags. `DELETE /admin/client-configs/{client_name}` removes the override and falls back to global defaults. `POST /admin/client-configs/seed-from-config` propagates the current `config.yaml` client entries into the configuration table for environments that want database-backed overrides alongside file-based bootstrap.

### 32. Read Resilience with Configurable Retry and Timeout

All gateway read operations are wrapped in a resilience policy that enforces a per-operation timeout (`resilience_timeout_secs`, default 30 seconds), a configurable number of retries on transient failures (`resilience_read_max_retries`, default 1), and an exponential initial backoff (`resilience_initial_backoff_ms`, default 100 ms). Write operations use a no-retry policy by default to prevent duplicate side effects. The transient error classifier distinguishes connection resets, pool exhaustion, and network timeouts from permanent errors such as constraint violations and syntax errors, retrying only the former.

### 33. camelCase to snake_case Normalization

When `gateway.force_camel_case_to_snake_case` is enabled in `config.yaml`, Athena transparently normalizes all incoming payload field names from camelCase to snake_case before executing the query. Column names returned by the database can similarly be re-cased for front-end consumers that expect camelCase responses. Sort field names supplied in `sortBy` / `sort_by` objects are normalized through the same path so callers are not required to know the canonical casing convention of the target table.

### 34. Sort Order on Fetch

Gateway fetch requests accept an optional `sort_by` or `sortBy` object in the POST body with `field` and `direction` (asc or desc) properties. Sorting is applied at the database level in the SQLx, deadpool, and Supabase drivers so the database optimizer can use indexes. For the GET `/data` endpoint, sort parameters are accepted as `sort_by` and `sort_direction` query string parameters. Cache keys incorporate sort options so sorted and unsorted responses for the same table do not collide.

### 35. UUID Type Binding

When inserting or updating rows, Athena detects UUID-shaped string values and binds them as the native PostgreSQL `uuid` type in SET and INSERT clauses. In WHERE clause conditions, UUID-shaped values are compared as text to remain compatible with both `uuid` and `text` columns. The CQL parser applies a similar cast in WHERE clauses for ScyllaDB targets. This allows callers to supply UUID values as plain JSON strings without requiring explicit type annotations.

### 36. Audit Logging for Management Operations

All DDL operations executed through the `/management` surface (table creation, column drops, index creation, extension installation) are recorded in a `database_audit_log` table on the target client's database when the audit table is present. Each audit entry captures the actor (derived from the API key), the operation type, the target object name, the generated SQL statement, the timestamp, and the outcome. This provides a tamper-evident history of schema changes for compliance and debugging.

### 37. Gateway Operation Logging

Every gateway CRUD and SQL request is written to `gateway_operation_log` in `athena_logging` with the client name, table name, operation type, duration, row count, cache hit flag, error message (when applicable), and cache key. This log is the source of truth for the client statistics aggregation. The `error`, `message`, and `cache_key` columns in existing rows can be backfilled from the `details` JSON metadata field by an inline migration path when the columns are added to an older installation.

### 38. JDBC URL and Supabase Header Routing

In addition to the `X-Athena-Client` header, callers can supply a full JDBC-style Postgres connection URL through the `x-athena-jdbc-url` header to bypass the client registry and route directly to an arbitrary database. For Supabase-flavored routing, the `x-supabase-url` and `x-supabase-key` headers instruct Athena to construct an ephemeral Supabase client for the request, enabling multi-tenant scenarios where each caller manages its own Supabase project.

### 39. SSL Enforcement API

`POST /api/v2/supabase/ssl_enforcement` forwards an SSL enforcement configuration change to a Supabase project's management API. This endpoint mirrors the Supabase Management API surface so callers can toggle `ssl_enforced` on a project through Athena's unified admin layer rather than calling Supabase directly.

### 40. Health and Readiness Endpoints

`GET /` returns a basic liveness response. `GET /ping` returns a lightweight pong with optional cache. `GET /health` returns a detailed health payload including server version, uptime, registered client count, and cache statistics. `GET /health/cluster` probes all registered Postgres clients in parallel and returns per-client connectivity, latency, and availability status, making it suitable for load balancer health checks and monitoring dashboards.

### 41. Registry Endpoint

`GET /registry` returns the list of all API registry entries created through the registry data layer, with metadata such as name, description, and creation timestamp. `GET /registry/{api_registry_id}` retrieves a single registry entry by its UUID. The registry is a lightweight catalog that external tooling can use to discover available API surfaces hosted on the Athena instance.

### 42. Systemd and Docker Deployment

Athena ships a `scripts/athena.service` systemd unit file for production Linux deployments. The unit file uses an `EnvironmentFile` to load `.env` secrets and passes `ATHENA_CONFIG_PATH` for config override. A `Dockerfile` and `docker-compose.yml` are included for containerized deployments. The Compose file starts Athena alongside a Redis instance and maps the default API port 4052. Redis caching activates automatically when `ATHENA_REDIS_URL` is set to the container's Redis endpoint.

### 43. Configuration Search and Auto-Bootstrap

On startup, Athena searches for `config.yaml` in a platform-specific sequence of directories: `%APPDATA%/athena` and `%LOCALAPPDATA%/athena` on Windows, `$XDG_CONFIG_HOME/athena`, `$HOME/.config/athena`, `$HOME/.athena`, `~/Library/Application Support/athena` on macOS, and the current working directory. If no file is found, the bundled default `config.yaml` is copied into the first writable location before loading. Missing config directories are logged with their full paths so operators can diagnose configuration failures quickly.

### 44. Rust CLI Helpers

The `athena_rs` binary exposes CLI subcommands alongside the HTTP server. The `server` subcommand (or `--api-only` flag) boots the Actix server with optional `--port`, `--config`, and `--pipelines` overrides. The `pipeline` subcommand runs a named or inline pipeline without starting the HTTP server. The `clients` subcommands (`list`, `add`, `remove`, `set-default`) mutate the `config/clients.json` file for reusable connection names. The `fetch` subcommand proxies a single gateway fetch call to a running Athena server. The `diag` subcommand prints OS name, architecture, CPU count, Rust version, hostname, and locale metadata for support and debugging. The `version` subcommand prints the build version string.

### 45. pg_meta Integration

Athena embeds a set of bundled Supabase pg_meta JSON response fixtures (`pg_meta/`) that describe extensions, policies, publications, types, permissions, and organization structures. These fixtures allow the Web Explorer and TypeScript CLI to query schema metadata through Athena without requiring a direct pg_meta sidecar service.

### 46. Multi-Provider Object Storage Abstraction

The backup storage layer is abstracted behind an S3-compatible interface supporting AWS S3, MinIO (self-hosted or via Compose), DigitalOcean Spaces, and Hetzner Object Storage. The active provider is selected by the `ATHENA_BACKUP_S3_ENDPOINT` environment variable. Custom endpoints are sent to the `aws-sdk-s3` client as a fully qualified URL, including port and path prefix, with SSL verification adjusted based on whether the endpoint is a loopback address.

### 47. Benchmark Suite

A `benches/` directory contains Criterion-based micro-benchmarks for the gateway fetch parsing path and SQL builder hot paths. These benchmarks measure the overhead of condition parsing, column normalization, and query construction at the serialization boundary to guide performance tuning of the core gateway loop.

### 48. Deadpool Experimental Pool Path

In addition to the primary SQLx pool, Athena includes an optional deadpool-based connection pool path enabled at compile time. This path supports an `ATHENA_DEADPOOL_WARMUP_TIMEOUT_MS` environment variable (default 800 ms) that controls how long the server waits for initial pool warmup before marking the client as unavailable. The deadpool path is intended for environments with high connection concurrency requirements or slow handshake latency.

### 49. Soft Client Deletion and Graceful Degradation

When a registered client is deleted through `DELETE /admin/clients/{client_name}` or its row is frozen with `PUT /admin/clients/{client_name}/freeze`, the entry is evicted from the in-memory registry. Subsequent requests referencing that client receive a 503 response with a descriptive error rather than an unexpected failure. On startup, if any `config.yaml` client fails to connect, the server logs a warning and continues with the remaining clients. Clients that fail during the database catalog merge phase are not retried under the same name to avoid duplicate failure noise.

### 50. Structured Error Envelopes and Trace IDs

All API responses follow a uniform JSON envelope: `{ "status": "success" | "error", "message": "...", "data": {...}, "error": {...} }`. Error responses from gateway and management handlers include a `trace_id` field generated per request so operators can correlate client-visible errors with server-side log lines. The error classifier in `src/error/` categorizes errors into connection, timeout, constraint, syntax, and not-found classes, and each class maps to the appropriate HTTP status code (503 for connection loss, 504 for timeout, 409 for constraint violation, 400 for syntax error, 404 for missing resource).

## Overview

Athena is a lightweight database gateway that proxies and executes queries across Athena/ScyllaDB, PostgreSQL, and Supabase backends. The Actix-powered API normalizes headers, shapes JSON payloads, and exposes helper utilities so API consumers, dashboards, and embedded tooling all speak the same surface.

Key features:

- **Gateway surface:** `/gateway/fetch`, `/gateway/update`, `/gateway/insert`, `/gateway/delete`, and `/gateway/query` accept structured payloads, route via `X-Athena-Client`, and normalize camelCase / snake_case when configured.
- **Online client catalog:** `x-athena-client` resolution now supports both legacy `config.yaml` entries and database-backed client rows stored in the `athena_logging` database, with a local in-memory registry for hot-path performance.
- **Post-processing:** Fetch/update responses honor optional `group_by`, `time_granularity`, `aggregation_column`, `aggregation_strategy`, and `aggregation_dedup`. When those fields are present, the response includes a `post_processing.grouped` array with labels, rows, and aggregation metadata.
- **SQL executor:** `/query/sql` reroutes raw SQL to Athena/Scylla, PostgreSQL pools (SQLx), or Supabase while sharing the same `X-Athena-Client` routing registry.
- **Proxy helpers:** `proxy_request` forwards arbitrary REST calls with auth header management and caching helpers in `api::cache`.
- **Cache layer:** Built on `moka` with optional Redis write-through and TTL management.
- **Admin control plane:** `/admin/clients*` manages client creation, editing, freeze/unfreeze, removal, and aggregated client statistics traced from the existing gateway log tables; `/admin/provision` and `/admin/backups*` handle schema bootstrap plus S3-based backups/restores.
- **Configuration:** `config.yaml` still bootstraps the server, logging/auth clients, Redis caching, and the camel-to-snake toggle; download the synced HTTP spec via `GET /openapi.yaml` and the WebSocket contract via `GET /openapi-wss.yaml` (covers `/wss/gateway`, `/wss/info`, `/health`, and `/ready` for the Bun proxy) so you can import both into OpenAPI tooling.

## Quickstart

### Prerequisites

- Rust 1.85+
- Docker (optional, for local Postgres/Redis)

### 1) Configure

- Update config.yaml
- Set your Postgres URIs with environment variables

Example local logging DB:

- POSTGRES_ATHENA_LOGGING_URI=postgres://athena:athena@localhost:5433/athena_logging

### 2) Run

```bash
cargo run -- --api-only
```

### 3) Use

- Send X-Athena-Client on gateway requests
- Optional wildcard host routing: set `ATHENA_WILDCARD_HOST_PATTERN=*.v3.athena-db.com` (and optionally `ATHENA_WILDCARD_HOST_ROUTING_ENABLED=true`) and create active `/admin/public-routes` entries so `prefix.v3.athena-db.com` resolves to that route’s `client_name` when no explicit client or JDBC URL header is supplied
- Use apikey for protected endpoints

## SDK

### Remote gateway E2E (mirror3 + athena_logging)

The SDK integration suite in `tests/sdk_gateway_examples_e2e.rs` verifies that Athena client builders and typed gateway requests route correctly into the gateway CRUD/SQL endpoints.

Default E2E target contract:

- `ATHENA_E2E_BASE_URL=https://mirror3.athena-db.com`
- `ATHENA_E2E_CLIENT=athena_logging`
- `ATHENA_E2E_TABLE=athena_e2e_adapter`

Test coverage in that suite:

- CRUD through builders: `insert`, `fetch`, `update`, `delete`
- CRUD through typed requests: `GatewayRequest`, `GatewayFetchRequest`, `GatewayUpdateRequest`, `GatewayDeleteRequest`
- Filters/operators: `eq`, `neq`, `gt`, `lt`, `in`
- Result shaping: `order_by`, `limit`, `offset`
- SQL path: `sql` alias + typed `sql_request`
- Route derivation: validates SDK route mapping against canonical `Gateway::*_PATH` constants
- Current matrix size: `27` E2E tests total (`insert: 5`, `fetch: 8`, `update: 5`, `delete: 5`, `sql: 3`, `routing: 1`)

The suite bootstraps `athena_e2e_adapter` automatically (`CREATE TABLE IF NOT EXISTS ...`) and isolates each run using a unique `run_id`. Cleanup deletes only rows for that test run.

Run only the SDK gateway E2E test file:

```bash
ATHENA_RUN_GATEWAY_SDK_E2E=1 \
ATHENA_E2E_BASE_URL=https://mirror3.athena-db.com \
ATHENA_E2E_CLIENT=athena_logging \
ATHENA_E2E_TABLE=athena_e2e_adapter \
ATHENA_E2E_KEY=<your-athena-key> \
cargo test --test sdk_gateway_examples_e2e -- --nocapture
```

PowerShell equivalent:

```powershell
$env:ATHENA_RUN_GATEWAY_SDK_E2E = "1"
$env:ATHENA_E2E_BASE_URL = "https://mirror3.athena-db.com"
$env:ATHENA_E2E_CLIENT = "athena_logging"
$env:ATHENA_E2E_TABLE = "athena_e2e_adapter"
$env:ATHENA_E2E_KEY = "<your-athena-key>"
cargo test --test sdk_gateway_examples_e2e -- --nocapture
```

Notes:

- `ATHENA_E2E_TABLE` must be a safe SQL identifier (`[A-Za-z_][A-Za-z0-9_]*`).
- Keep `ATHENA_E2E_KEY` scoped to test rights only.
- If API key enforcement is enabled for `athena_logging`, the test key must include gateway rights for fetch/insert/update/delete/query/sql.

## Core Endpoints

### Gateway

- POST /gateway/fetch
- PUT /gateway/insert
- POST /gateway/update
- DELETE /gateway/delete
- POST /gateway/query

### Deferred Queue

- GET /gateway/deferred/{request_id}
- POST /gateway/deferred/{request_id}/requeue

### Health and Specs

- GET /
- GET /ping
- GET /health
- GET /metrics
- GET /openapi.yaml
- GET /openapi-wss.yaml

## Minimal Request Examples

### Deferred query

```http
POST /gateway/query
X-Athena-Client: reporting
X-Athena-Defer: true
Content-Type: application/json

{
  "query": "SELECT now()"
}
```

### Check deferred status

```http
GET /gateway/deferred/REQUEST_ID
```

### Requeue failed or completed job

```http
POST /gateway/deferred/REQUEST_ID/requeue
```

## Config Essentials

In config.yaml, focus on:

- api.port
- postgres_clients
- gateway settings for admission/defer/worker
- logging/auth client names

Useful gateway knobs:

- gateway.admission_limit_enabled
- gateway.admission_global_requests_per_window
- gateway.admission_per_client_requests_per_window
- gateway.admission_window_secs
- gateway.admission_defer_on_limit_enabled
- gateway.admission_defer_route_prefixes
- gateway.deferred_query_worker_enabled
- gateway.deferred_query_worker_poll_ms

## Operational Alerts

Use these Prometheus rules as a baseline:

```yaml
groups:
  - name: athena-deferred-queue
    rules:
      - alert: AthenaDeferredQueueBacklogGrowing
        expr: athena_gateway_deferred_queue_requests{status="queued"} > 200
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Athena deferred queue backlog is high"
          description: "Queued deferred requests stayed above 200 for 5 minutes."

      - alert: AthenaDeferredQueueStuckOldest
        expr: athena_gateway_deferred_queue_oldest_age_seconds > 300
        for: 10m
        labels:
          severity: critical
        annotations:
          summary: "Athena deferred queue appears stuck"
          description: "Oldest queued/running deferred request age exceeded 5 minutes."

      - alert: AthenaDeferredQueueStorageDown
        expr: athena_gateway_deferred_queue_storage_up == 0
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "Athena deferred queue storage unavailable"
          description: "Logging Postgres is unreachable for deferred queue reads."

      - alert: AthenaDeferredQueryFailuresBurst
        expr: increase(athena_gateway_deferred_events_total{kind="gateway_query",status="failed"}[10m]) > 10
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: "Athena deferred query failures are spiking"
          description: "More than 10 deferred query jobs failed in the last 10 minutes."
```

## Common Commands

### API Keys

Athena persists API keys in the `api_keys` family of tables. The master record is `api_keys`, which stores `public_id`, `name`, `description`, an optional `client_name`, and the salt/hash for the secret. Columns like `api_keys_id`, `created_at`, and `updated_at` are generated in the database, and `api_keys_id` is the UUID returned to callers while the internal bigint `id` backs foreign keys. `api_key_rights` names the allowed capability strings, `api_key_right_grants` bridges keys to rights, `api_key_config` keeps global enforcement, `api_key_client_config` holds per-client overrides, and `api_key_auth_log` records every authentication attempt (request metadata, presented hash, granted rights, failure reason, etc.).

All of the `/admin/*` surfaces and the protected `/schema/clients` + `/clients` endpoints require the static admin key stored in the `ATHENA_KEY_12` environment variable. Provide that key through `Authorization: Bearer <key>`, `apikey`, `x-api-key`, `x-athena-key`, or the `?api_key=` query string when invoking these routes.

When you hit `POST /admin/api-keys`, Athena generates a `public_id`, secrets (salt/hash), and returns the plaintext credential as `ath_{public}.{secret}`. Capture the secret at creation time; it cannot be retrieved later. Rights can be assigned at creation or updated later by supplying the `rights` array.

#### Admin API key routes

- `GET /admin/api-keys` – list all API key records plus granted rights.
- `POST /admin/api-keys` – create a new API key by providing `name`, optional `description`, optional `client_name`, optional `expires_at` timestamp, and optional `rights`.
- `PATCH /admin/api-keys/{id}` – adjust an existing key (name, description, client, expiry, active flag, rights).
- `DELETE /admin/api-keys/{id}` – archive the key.
- `GET /admin/api-key-rights` – list every right string that can be granted.
- `POST /admin/api-key-rights` – create a new right (name + optional description).
- `PATCH /admin/api-key-rights/{id}` – rename/describe a right.
- `DELETE /admin/api-key-rights/{id}` – remove a right (cascades via `api_key_right_grants`).
- `GET /admin/api-key-config` – read the global enforcement setting plus the client overrides.
- `PUT /admin/api-key-config` – flip the global `enforce_api_keys` flag.
- `GET /admin/api-key-clients` – list the per-client overrides.
- `PUT /admin/api-key-clients/{client_name}` – upsert a per-client override.
- `DELETE /admin/api-key-clients/{client_name}` – remove a client override and fall back to the global setting.

### Client Catalog Admin Routes

Athena stores scalable client definitions in the `athena_clients` table inside the `athena_logging` database. Each row can reference either a direct `pg_uri` or a `pg_uri_env_var`, and bootstrap will also seed config-only clients into the table so old installs migrate forward without duplicate entries.

- `GET /admin/clients` – list the database catalog plus the current runtime registry snapshot.
- `POST /admin/clients` – create a database-managed client by sending `client_name` and either `pg_uri` or `pg_uri_env_var`.
- `PATCH /admin/clients/{client_name}` – update connection settings, description, or active state.
- `PUT /admin/clients/{client_name}/freeze` – freeze or unfreeze a client without deleting its catalog row.
- `DELETE /admin/clients/{client_name}` – soft-delete a client from the database catalog and remove it from the runtime registry.
- `GET /admin/clients/statistics` – list aggregated request and operation counts per client.
- `POST /admin/clients/statistics/refresh` – rebuild `client_statistics` and `client_table_statistics` from the existing log tables.
- `GET /admin/clients/{client_name}/statistics` – inspect totals plus per-table operation counts for one client.
- `GET /admin/clients/{client_name}/statistics/drilldown` – list `gateway_operation_log` rows for a given `table_name` and `operation` (query params `status`, `limit`, `offset`).

Frozen or deleted database rows are removed from the hot-path registry, so request handlers keep using local memory instead of querying the catalog table for every call.

Every admin response is described in `openapi.yaml`, so use that spec if you need payload samples, response codes, or schema definitions.

Success example:

```json
{
  "status": "success",
  "message": "Fetched 10 rows",
  "data": {"rows": [...]}
}
```

Error example:

```json
{
  "status": "error",
  "message": "Invalid request",
  "error": "Missing table_name"
}
```

## CLI Tools

### Rust CLI

The `athena_rs` binary exposes CLI helpers alongside the HTTP API. Global flags apply to every subcommand:

- `--config` / `--config-path <PATH>` override `config.yaml` (default: `config.yaml`). The same path may be set with `ATHENA_CONFIG_PATH` (CLI wins if both are set).
- `--pipelines <PATH>` overrides the default pipeline definitions file (`config/pipelines.yaml`).
- `--port <PORT>` overrides the API port when booting the server.
- `--api-only` boots the server (same as `server`).

Configuration load order (searched in this order and logged when missing):

1. `%APPDATA%/athena`
2. `%LOCALAPPDATA%/athena`
3. `%USERPROFILE%/.athena`
4. `$XDG_CONFIG_HOME/athena`
5. `$HOME/.config/athena`
6. `$HOME/.athena`
7. `~/Library/Application Support/athena` (macOS)
8. Current working directory

If no config file is found, Athena copies the bundled `config.yaml` into the first writable directory before loading it. Errors list every inspected location so you can troubleshoot missing configs.

Commands:

- `server` (or `--api-only`): start the Actix Web server. Respect `--port`, `--config`, and `--pipelines` overrides.
- `pipeline`: run an inline pipeline with `--client <NAME>`, `--payload-json`, `--payload-file`, and `--pipeline <NAME>` to reference prebuilt definitions.
- `clients` subcommands (`list`, `add`, `remove`, `set-default`): mutate `config/clients.json` that stores reusable Postgres connection names.
- `fetch`: proxy a gateway fetch request to `https://athena-db.com/gateway/fetch` (or the URL supplied via `--url`). Use `--client` to pick a saved client and `--body-json` / `--body-file` to supply the payload.
- `diag`: print OS, architecture, CPU count, Rust version, hostname, and locale metadata.
- `version`: show the build version (`cargo run -- --version` works too).

Systemd: see `scripts/athena.service`. Set `ATHENA_CONFIG_PATH` in `EnvironmentFile` (for example `.env`) or pass `--config <ABSOLUTE_PATH>` in `ExecStart`.

### TypeScript CLI

The CLI in `cli/` generates SQL/CQL artifacts from Supabase schema metadata.

Installation:

```bash
cargo build --release
```

### Test

```bash
cargo test --verbose
```

### Lint and format

```bash
cargo fmt --check
cargo clippy --all-targets --all-features
```

### API only server

```bash
cargo run -- --api-only
```

## Web and CLI

- Web explorer: apps/web
- TypeScript CLI: cli

## Notes

- If a client is missing or offline, Athena keeps running with available clients.
- For custom Supabase routing, include x-supabase-url and x-supabase-key headers.
- OpenAPI contracts are served directly from the API process.