athena_rs 2.9.2

Database gateway API
Documentation

Athena RS 2.8.1

current version: 2.9.2

Table of Contents

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+ (edition 2024). On Linux, install libssl-dev and pkg-config for openssl-sys.
  • Node.js 18+ if you plan to run the Web Explorer (apps/web) or the TypeScript CLI (cli/).
  • Docker (optional) for the bundled Postgres/Redis services: docker compose up -d postgres redis.

Start the API locally

  1. Populate config.yaml, point each postgres_clients entry to ${ENV_VAR} values, and export the env vars to keep credentials out of version control.
  2. (Optional) start local services with docker compose up -d postgres redis and set POSTGRES_ATHENA_LOGGING_URI="postgres://athena:athena@localhost:5433/athena_logging".
  3. Start the server with cargo run -- --api-only (or cargo run -- server) to boot Actix, load openapi.yaml, and serve /openapi-wss.yaml; the API no longer boots automatically unless --api-only or server is passed.
  4. Use the X-Athena-Client header to select a Postgres pool for /gateway/*, /query/sql, and the schema endpoints; the same header drives pipelines and diagnostics.

Feature flags

CDC (Change Data Capture) is enabled by default. To build without CDC (smaller binary, no WebSocket server or cdc subcommand), use cargo build --no-default-features.

Architecture

Athena follows a three-tier architecture that keeps the HTTP gateway separated from the driver glue and the downstream databases:

  1. API Layer: The Actix Core Service validates headers, shapes requests, starts CLI helpers, and emits the standard response envelope (status, message, data/error).
  2. Driver Layer: Router logic hands off translated queries to either SQLx-managed PostgreSQL pools, the native Scylla driver, or the Supabase REST translator, and each driver tracks health/circuit-breaking before executing.
  3. Database Backends: The driver layer spans PostgreSQL (SQLx), ScyllaDB (native CQL), and Supabase (REST / Supabase RPC).

Client catalog flow

Performance is the first priority in the new client catalog. Athena loads clients once during bootstrap, reconciles config.yaml with the athena_clients table in the athena_logging database, and serves request-time lookups from the local registry instead of round-tripping to Postgres on every request.

flowchart LR
    A["config.yaml"] --> B["Bootstrap"]
    C["athena_logging.athena_clients"] --> B
    B --> D["In-memory client registry"]
    D --> E["X-Athena-Client lookup"]
    E --> F["Gateway route"]
    F --> G["SQLx pool / driver"]

Bootstrap reconciliation

sequenceDiagram
    participant S as Server start
    participant C as config.yaml
    participant L as athena_logging
    participant R as Local registry
    S->>C: Load postgres_clients
    S->>R: Connect legacy config pools
    S->>L: Upsert config-only clients into athena_clients
    L-->>S: Return database-backed clients
    S->>R: Merge active clients and freeze deleted/frozen ones
    R-->>S: Hot-path registry ready

Statistics lineage

flowchart TD
    A["Gateway request"] --> B["gateway_request_log"]
    A --> C["gateway_operation_log"]
    B --> D["client_statistics"]
    C --> D
    C --> E["client_table_statistics"]
    D --> F["/admin/clients/statistics"]
    E --> G["/admin/clients/{client}/statistics"]

Component relationships:

  • The Core Service is the API gateway, CLI host, and cache coordinator.
  • The Web UI (apps/web) consumes the gateway, schema, and registry APIs, and it also ships Athena headers via NEXT_PUBLIC_* env vars.
  • The TypeScript CLI talks directly to Supabase (via get_full_schema_json) to bootstrap seed files, constraints, and Drizzle registries; it does not run through the Actix gateway but outputs SQL/CQL for Athena to execute later.

Request flow:

  1. A client (Web UI, Rust CLI, or HTTP caller) hits /gateway/*//query/sql//pipelines with the required headers.
  2. The API Layer validates the X-Athena-Client, resolves the target backend (Postgres/Scylla/Supabase), and forwards the normalized payload to the Driver Layer.
  3. The Driver Layer translates the request into SQL or CQL, executes it, enforces health/circuit-breakers, and returns rows plus metadata.
  4. The API Layer wraps the return value in the shared response envelope before sending it back.

Supported database backends:

  • PostgreSQL (SQLx): pooled SQL execution and schema discovery via information_schema.
  • ScyllaDB (native driver): CQL execution against Scylla clusters using the scylla crate and health-aware host tracking.
  • Supabase (REST API): Supabase REST endpoints with RPC helpers, health tracking, and optional override headers (x-supabase-url, x-supabase-key).
                    ┌────────────┐
                    │  Clients   │
                    │ (Web UI,   │
                    │  CLI, Postman) │
                    └──────┬─────┘
                           │
                           ▼
                   ┌─────────────────┐
                   │  Core Service   │
                   │ (Actix Gateway, │
                   │  Cache, CLI)    │
                   └──────┬──────────┘
              ┌────────────┼────────────┐
              │            │            │
              ▼            ▼            ▼
      ┌────────────┐ ┌────────────┐ ┌────────────┐
      │ SQLx       │ │ Scylla     │ │ Supabase   │
      │ PostgreSQL │ │ CQL Driver │ │ REST + RPC │
      └────────────┘ └────────────┘ └────────────┘

Athena

A lightweight Database Gateway that proxies and executes queries across multiple backends (Athena/ScyllaDB, PostgreSQL, and Supabase) with simple caching and routing.

  • Provides REST endpoints for executing SQL against supported drivers
  • Proxies arbitrary requests while handling auth headers and basic response shaping
  • Exposes a small set of utilities for driver execution and routing

API Reference

Gateway Endpoints

POST /gateway/fetch

Fetch data with filtering, pagination, grouping, and aggregations. Send conditions, pagination fields, and the optional group_by / aggregation_* payload. Responses include data.rows plus a post_processing.grouped array when grouping is enabled.

Example request:

POST /gateway/fetch
Headers:
  X-Athena-Client: reporting
  X-User-Id: user-123
  apikey: ${ATHENA_APIKEY}
Body:
{
  "table_name": "events",
  "columns": ["id", "user_id", "value", "created_at"],
  "conditions": [{"eq_column": "status", "eq_value": "active"}],
  "limit": 50,
  "group_by": "user_id",
  "time_granularity": "hour",
  "aggregation_column": "value",
  "aggregation_strategy": "cumulative_sum",
  "aggregation_dedup": true
}

Sample response (success envelope):

{
  "status": "success",
  "message": "Fetched 6 rows",
  "data": {
    "rows": [...],
    "post_processing": {
      "grouped": [
        {"label": "user-1", "rows": [...], "aggregation": 42}
      ]
    }
  }
}

POST /gateway/update

Reuses the same payload as /gateway/fetch but allows updates instead of raw reads. Include columns, conditions, and any aggregation metadata you need.

PUT /gateway/insert

Insert (and optionally upsert) a row. Provide the target table, the insert_body, and update_body to apply when conflicts occur. Required headers include X-Company-Id, X-Organization-Id, and apikey. The optional X-Publish-Event header toggles telemetry events.

Example body:

{
  "table_name": "users",
  "insert_body": { "email": "new@athena.io", "status": "active" },
  "update_body": { "status": "active" }
}

DELETE /gateway/delete

Delete a row by table_name and resource_id. The request must carry X-Company-Id, X-Organization-Id, apikey, and X-Athena-Client.

POST /gateway/query

Execute raw SQL through the selected Postgres pool. The request body is simply { "query": "SELECT ..." }; the X-Athena-Client header selects the pool.

Query & Pipeline Endpoints

POST /query/sql

Dispatch raw SQL to a specific driver. The request requires query, driver (athena, postgresql, or supabase), and db_name (the logical Postgres pool from X-Athena-Client). Responses use the standard envelope and include columns/rows.

Example:

{
  "query": "SELECT COUNT(*) FROM users",
  "driver": "postgresql",
  "db_name": "reporting"
}

POST /pipelines

Run config-driven ETL-like pipelines that follow a source → transform → sink pattern. Supply pipeline to reference a definition from config/pipelines.yaml (see the example_copy entry in the repo) or provide inline source/transform/sink overrides.

Transform options:

  • group_by: column name used for grouping.
  • time_granularity: one of day, hour, or minute.
  • aggregation_column, aggregation_strategy (only cumulative_sum today), and aggregation_dedup.

Example pipeline request:

{
  "pipeline": "example_copy",
  "source": {
    "table_name": "users",
    "columns": ["id", "email"],
    "conditions": []
  },
  "transform": {
    "group_by": "region",
    "aggregation_column": "score",
    "aggregation_strategy": "cumulative_sum"
  },
  "sink": { "table_name": "users_backup" }
}

Responses contain a data array documenting inserted rows along with the pipeline status.

Schema & Utility Endpoints

  • GET /schema/clients: lists clients (the Postgres pools registered under config/clients.json).
  • GET /schema/tables: requires X-Athena-Client; returns table metadata from information_schema.
  • GET /schema/columns: requires X-Athena-Client plus table_name query parameter; returns column definitions.
  • GET /registry and GET /registry/{api_registry_id}: read API registry data from Supabase; include Cache-Control: no-cache to bypass in-memory caching.
  • GET /: health check that reports message, version, backend statuses (athena_api, athena_deadpool, athena_scylladb), and available routes.
  • GET /docs: redirects to the hosted Athena documentation.
  • GET /openapi.yaml: serves the bundled OpenAPI spec used to generate this reference.
  • GET /openapi-wss.yaml: serves the bundled WebSocket OpenAPI contract for /wss/gateway requests.

Authentication & Headers

  • API keys: Provide apikey (or the mirror x-api-key) on every gateway, query, and pipeline request.
  • Routing: X-Athena-Client chooses the Postgres pool. When the client resolves to custom_supabase, also send x-supabase-url and x-supabase-key to hit the supabase instance directly.
  • Audit headers: X-Company-Id and X-Organization-Id are required for insert and delete; optional X-User-Id can be sent for traceability.
  • Optional helpers: X-Strip-Nulls drops null values from the returned payload, X-Publish-Event toggles telemetry events (set to true to emit), and Cache-Control: no-cache skips cached registry responses.
  • Standard response envelope: All endpoints wrap payloads in { "status": "success" | "error", "message": "...", "data": {...}?, "error": "..."? }. Use api_success and api_error helpers in code to produce this schema.

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:

cd cli

npm install

npm run build

Commands:

  • seed (default). Generates SQL CREATE TABLE scripts and optional CQL artifacts. Flags:
    • --sql=<PATH>, --cql=<PATH> (destinations for SQL/CQL files).
    • --if-not-exist-table to wrap tables with CREATE TABLE IF NOT EXISTS.
    • --include-constraints-sql to emit constraint SQL alongside the seeds.
    • --supabase-url, --supabase-key to override credentials.
    • --supabase-config=<PATH> to load a JSON file (supports a database block).
    • --db-config-output=<PATH> to dump the resolved database config.
  • constraints: emit constraint SQL from Supabase schema metadata (supports the same Supabase overrides).
  • table-registry: builds a Drizzle table registry from lib/db/schema.ts. Flags: --registry-schema-path=<PATH> and --registry-path=<PATH>.
  • help: prints the available commands and their flags.

Flags and env vars:

  • resolveSupabaseConfig respects ATHENA_SUPABASE_URL, ATHENA_SUPABASE_ANON_KEY, SUPABASE_URL, and SUPABASE_ANON_KEY.
  • The CLI requires the Supabase RPC get_full_schema_json—make sure the RPC is defined before running seed or constraints.

SDK

The AthenaClient SDK offers a builder-based interface that works across Supabase, PostgreSQL, Scylla, and other backends in a single surface.

Initialization patterns

  • AthenaClient::new(url, key) defaults to the native SQL backend.
  • AthenaClient::new_with_backend(url, key, BackendType) picks a specific backend.
  • AthenaClient::builder() returns an AthenaClientBuilder for granular control (see backend, url, key, ssl, port, database, max_connections, min_connections, connection_timeout, idle_timeout, health_tracking, circuit_breaker_*).

Query builders

use athena_rs::{AthenaClient, BackendType, client::query_builder::OrderDirection};

let client = AthenaClient::builder()
    .backend(BackendType::Supabase)
    .url("https://project.supabase.co")
    .key("service-role-key")
    .health_tracking(true)
    .build()
    .await?;

let rows = client
    .select("users")
    .columns(["id", "email"])
    .where_eq("status", "active")
    .where_gt("created_at", "2025-01-01")
    .order_by("created_at", OrderDirection::Desc)
    .limit(10)
    .offset(0)
    .execute()
    .await?;

client
    .insert("logs")
    .payload(json!({ "message": "SDK initialized" }))
    .execute()
    .await?;

Beyond select, the SDK exposes insert, update, and delete builders. Advanced filters include where_eq, where_gt, where_in, order_by, limit, and offset.

Raw execution

  • execute_sql("SELECT ...") for backend-agnostic SQL.
  • execute_cql("SELECT ...") when speaking Cassandra/Scylla.

Configuration objects

  • ConnectionConfig: holds url, optional key, ssl, port, and database.
  • PoolConfig: controls max_connections, min_connections, connection_timeout, and idle_timeout (defaults: 50/5/5s/300s).
  • HealthConfig: toggles health tracking, check_interval, circuit_breaker_threshold, and circuit_breaker_timeout (defaults: enabled, 30s interval, threshold 5, timeout 60s).

Health tracking

The Supabase backend wraps the Supabase client in HealthAwareSupabaseClient, which implements a circuit breaker. Use is_offline() to check if the host is blocked, force_offline(duration) for testing, and reset_health() to clear failures.

Environment initialization

Supabase backends can also be built from the environment:

let backend = SupabaseBackend::from_env("ATHENA_SUPABASE_URL", "ATHENA_SUPABASE_KEY")?;
let client = AthenaClient::builder()
    .backend(BackendType::Supabase)
    .url(backend.config().connection.url.clone())
    .key(backend.config().connection.key.clone().unwrap())
    .build()
    .await?;

Supported backend types

BackendType enumerates: Native, Supabase, Postgrest, Scylla, Neon, and PostgreSQL. /query/sql accepts the same driver names, and the builder exposes new_with_backend_name("supabase") for quick scripts.

Supported Drivers

PostgreSQL driver (SQLx)

Connection strings follow postgres://user:password@host:port/db. The driver uses SQLx pools, honors PoolConfig, and maps X-Athena-Client values to different entries in config/clients.json.

ScyllaDB driver

The Scylla backend (scylla::Session) accepts host/port pairs and executes CQL. Use it for Cassandra-compatible workloads; it supports execute_cql and health tracking via the shared tracker.

Supabase driver

Supabase is backed by supabase_rs and requires a SupabaseConnectionInfo (URL and API key). Use x-supabase-url/x-supabase-key headers to route a gateway request to a custom Supabase project. The driver enforces HTTP retries, circuit-breaking, and exposes is_offline(), force_offline(), and reset_health() helpers.

Driver selection happens either through the driver field in /query/sql or via the SDK builder (backend(BackendType::Scylla)), so the same API surface can switch between Postgres, Scylla, and Supabase at runtime.

Configuration

config.yaml still defines core server settings, Redis caches (ATHENA_REDIS_URL), the athena_logging/auth clients, and any legacy Postgres pools you want to bootstrap from environment variables. Replace literal URIs in postgres_clients with ${ENV_VAR} tokens and export the env vars or the CLI will fail to start.

For scalable installs, use athena_clients in the athena_logging database as the primary client catalog:

  • Config-backed clients are auto-synced into the table on startup if they do not exist yet.
  • Client names are de-duplicated in the catalog, so the same X-Athena-Client cannot exist twice.
  • Request-time routing still happens against the in-memory registry, not the catalog database.
  • config.yaml remains required for server bootstrap and for the logging/auth database, but it no longer needs to carry every client once the catalog is populated.

Pipeline definitions live in config/pipelines.yaml (example_copy demonstrates a source/sink pair) and can be referenced by the Rust CLI pipeline helper or /pipelines API.

Backup recovery strategy

POST /admin/backups now supports an optional recovery_strategy field for autonomous pg_dump recovery on known catalog issues.

{
  "client_name": "athena_logging",
  "label": "pre-migration",
  "recovery_strategy": "repair_missing_role_oids"
}

Supported values:

  • none (default)
  • repair_missing_role_oids: when pg_dump fails with role with OID ... does not exist, Athena attempts best-effort source-catalog owner repair and retries the dump automatically.

Operational note: this strategy requires sufficient privileges on the source Postgres database to update ownership metadata in system catalogs.

Realtime Connection Registry

Athena now maintains a deduplicated, live connection registry in gateway_connection_registry_realtime so operators can track active usage without scanning large historical logs.

Each row represents a live tuple of:

  • client_name
  • ipv4
  • athena_url (derived from host + scheme)
  • API key identity (api_key_public_id and/or api_key_external_id when available)

The request logger updates this table on every request and increments total_hits, while refreshing last_seen_at, last_method, last_path, and last_status_code.

Inactive rows are purged automatically by the API runtime:

  • ATHENA_REALTIME_REGISTRY_PURGE_INTERVAL_SECS (default: 30)
  • ATHENA_REALTIME_REGISTRY_INACTIVE_AFTER_SECS (default: 300)

This keeps the registry realtime and small, unlike gateway_request_log, which is historical and append-only.

Schema setup

  • Fresh installs: included in sql/provision.sql.
  • Existing installs: apply sql/migrations/add_gateway_connection_registry_realtime.sql.

Example queries

List currently active entries (last 5 minutes):

SELECT
  client_name,
  ipv4,
  athena_url,
  COALESCE(api_key_public_id, api_key_external_id, 'ANON') AS api_key_identity,
  total_hits,
  first_seen_at,
  last_seen_at,
  last_method,
  last_path,
  last_status_code
FROM gateway_connection_registry_realtime
WHERE last_seen_at >= now() - interval '5 minutes'
ORDER BY last_seen_at DESC;

Count active connections per Athena client:

SELECT client_name, COUNT(*) AS active_connections
FROM gateway_connection_registry_realtime
WHERE last_seen_at >= now() - interval '5 minutes'
GROUP BY client_name
ORDER BY active_connections DESC;

Historical log retention policy

gateway_request_log and gateway_operation_log are historical event streams and are expected to grow continuously. Keep these tables bounded with a scheduled retention cleanup so they do not impact query performance or storage costs.

Example: keep only the last 30 days of history.

DELETE FROM gateway_request_log
WHERE created_at < now() - interval '30 days';

DELETE FROM gateway_operation_log
WHERE created_at < now() - interval '30 days';

For large datasets, run purges in batches to reduce lock pressure:

WITH to_delete AS (
  SELECT id
  FROM gateway_request_log
  WHERE created_at < now() - interval '30 days'
  ORDER BY id
  LIMIT 10000
)
DELETE FROM gateway_request_log
WHERE id IN (SELECT id FROM to_delete);

Recommended operational pattern:

  • Run batch purges every few minutes (or nightly for low-volume deployments).
  • Keep the realtime table (gateway_connection_registry_realtime) short-lived via the inactivity purge env vars.
  • Keep historical tables on explicit retention windows (for example 7, 30, or 90 days) based on compliance and debugging needs.

WebSocket Gateway

src/wss/ contains the first-pass WebSocket gateway contract. The Bun proxy in apps/wss-gateway serves /openapi-wss.yaml, /wss/info, and the upgrade endpoint at /wss/gateway, plus /health and /ready probes. The request/response envelopes mirror the HTTP gateway actions (gateway.fetch, gateway.insert, gateway.update, gateway.delete, gateway.query) and return connection metadata on join (started_at, api_base) alongside upstream responses.

Use openapi-wss.yaml when documenting or prototyping clients that want to multiplex Athena gateway operations over a persistent WebSocket connection; it includes examples for the connection ack, upstream proxy responses, and timeout/error envelopes.

Web Explorer

The Next.js app in apps/web hosts the Athena Explorer UI (apps/web/app/page.tsx). Install and run it locally with:

cd apps/web

npm install

npm run dev

Point the UI at https://athena-db.com or override the backend with NEXT_PUBLIC_ATHENA_BASE_URL. Provide NEXT_PUBLIC_ATHENA_CLIENT, NEXT_PUBLIC_ATHENA_USER_ID, NEXT_PUBLIC_ATHENA_COMPANY_ID, and NEXT_PUBLIC_ATHENA_ORGANIZATION_ID so the frontend can issue insert/delete requests. The datagrid relies on /schema/clients, /schema/tables, /schema/columns, /gateway/fetch, /query/sql, and the gateway insert/delete endpoints to render table data, schema metadata, and DDL actions.

Docker

  1. Pre-pull the optional Redis cache image: docker pull redis:8.2.4-alpine3.22.
  2. Run docker compose up --build to start the athena API and redis cache together.
  3. Athena listens on http://localhost:4052; caching activates when you set ATHENA_REDIS_URL=redis://redis:6379 in the environment.
  4. Stop everything with docker compose down; the redis-data volume preserves cache contents between runs.

Testing & Linting

Rust API checks:

cargo fmt --check

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

cargo test --verbose

Web Explorer linting:

cd apps/web

npm run lint

Contributing

Please open issues for bugs or feature requests. Contributions are welcome via pull requests—run cargo fmt/npm run lint where appropriate, add tests if possible, and describe your changes in the PR. Check .github/workflows for the automated checks that run on each branch and follow the repository conventions.