athena_rs 3.3.0

Database gateway API
Documentation

Athena RS 2.8.1

current version: 3.3.0

Table of Contents

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

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:

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:

$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

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

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

Check deferred status

GET /gateway/deferred/REQUEST_ID

Requeue failed or completed job

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:

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:

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

Error example:

{
  "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:

cargo build --release

Test

cargo test --verbose

Lint and format

cargo fmt --check

cargo clippy --all-targets --all-features

API only server

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.