pub(crate) const PROVISION_SQL: &str = r#"-- Athena provisioning script.
--
-- Applies all Athena schema tables to a fresh (or existing) Postgres database.
-- Every statement uses CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS
-- so the script is safe to re-run against an already-provisioned database.
--
-- Usage:
-- psql "$DATABASE_URL" -f sql/provision.sql
-- athena_rs provision --uri "$DATABASE_URL"
-- ---------------------------------------------------------------------------
-- Gateway request / operation logs
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS gateway_request_log (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_at timestamptz NOT NULL DEFAULT now(),
request_id text NOT NULL,
client text,
method text,
path text,
query_string text,
status_code integer,
ipv4 text,
user_agent text,
headers jsonb,
body jsonb,
user_id text,
company_id text,
organization_id text,
api_key_id text,
presented_api_key_public_id text,
presented_api_key_hash text,
presented_api_key_salt text,
api_key_authenticated boolean DEFAULT false,
api_key_authorized boolean DEFAULT false,
api_key_enforced boolean DEFAULT false,
api_key_auth_reason text,
host text,
cached boolean,
time numeric NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS gateway_operation_log (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_at timestamptz NOT NULL DEFAULT now(),
request_id text NOT NULL,
operation text,
table_name text,
client text,
method text,
path text,
status_code integer,
duration_ms bigint,
details jsonb,
time numeric NOT NULL,
error boolean DEFAULT false,
message text,
cache_key text,
PRIMARY KEY (id)
);
-- ---------------------------------------------------------------------------
-- API keys and rights
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_keys (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
api_keys_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
public_id text NOT NULL UNIQUE,
name text NOT NULL,
description text,
client_name text,
key_salt text NOT NULL,
key_hash text NOT NULL,
expires_at timestamptz,
is_active boolean NOT NULL DEFAULT true,
last_used_at timestamptz,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_api_keys_client_name ON api_keys(client_name);
CREATE INDEX IF NOT EXISTS idx_api_keys_expires_at ON api_keys(expires_at);
CREATE TABLE IF NOT EXISTS api_key_rights (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
api_key_rights_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
created_at timestamptz NOT NULL DEFAULT now(),
name text NOT NULL UNIQUE,
description text,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS api_key_right_grants (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
api_key_right_grants_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
api_key_id bigint NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
right_id bigint NOT NULL REFERENCES api_key_rights(id) ON DELETE CASCADE,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (api_key_id, right_id)
);
CREATE TABLE IF NOT EXISTS api_key_config (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
api_key_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
enforce_api_keys boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO api_key_config (api_key_config_id, enforce_api_keys)
VALUES ('00000000-0000-0000-0000-000000000000', false)
ON CONFLICT (api_key_config_id) DO NOTHING;
CREATE TABLE IF NOT EXISTS api_key_client_config (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
api_key_client_config_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
client_name text NOT NULL UNIQUE,
enforce_api_keys boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS api_key_auth_log (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
api_key_auth_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
created_at timestamptz NOT NULL DEFAULT now(),
request_id text NOT NULL,
api_key_id bigint REFERENCES api_keys(id) ON DELETE SET NULL,
api_key_public_id text,
client_name text,
method text NOT NULL,
path text NOT NULL,
presented_key_hash text,
presented_key_salt text,
required_rights jsonb,
granted_rights jsonb,
authenticated boolean NOT NULL DEFAULT false,
authorized boolean NOT NULL DEFAULT false,
enforced boolean NOT NULL DEFAULT false,
failure_reason text,
remote_addr text,
user_agent text,
time timestamptz NOT NULL DEFAULT now(),
UNIQUE(request_id, api_key_public_id)
);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_api_key_id ON api_key_auth_log(api_key_id);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_client_name ON api_key_auth_log(client_name);
CREATE INDEX IF NOT EXISTS idx_api_key_auth_log_time ON api_key_auth_log(time);
-- ---------------------------------------------------------------------------
-- Athena clients catalog
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS athena_clients (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_at timestamptz NOT NULL DEFAULT now(),
athena_client_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
client_name text NOT NULL,
description text,
pg_uri text,
pg_uri_env_var text,
config_uri_template text,
source text NOT NULL DEFAULT 'database',
is_active boolean NOT NULL DEFAULT true,
is_frozen boolean NOT NULL DEFAULT false,
last_synced_from_config_at timestamptz,
last_seen_at timestamptz,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
updated_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz,
CONSTRAINT athena_clients_source_check CHECK (
source IN ('config', 'database', 'merged')
),
CONSTRAINT athena_clients_connection_check CHECK (
pg_uri IS NOT NULL
OR pg_uri_env_var IS NOT NULL
OR config_uri_template IS NOT NULL
)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_athena_clients_client_name_active
ON athena_clients (lower(client_name))
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_athena_clients_source
ON athena_clients (source)
WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_athena_clients_active
ON athena_clients (is_active, is_frozen)
WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS client_statistics (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_at timestamptz NOT NULL DEFAULT now(),
client_name text NOT NULL,
total_requests bigint NOT NULL DEFAULT 0,
successful_requests bigint NOT NULL DEFAULT 0,
failed_requests bigint NOT NULL DEFAULT 0,
total_cached_requests bigint NOT NULL DEFAULT 0,
total_operations bigint NOT NULL DEFAULT 0,
last_request_at timestamptz,
last_operation_at timestamptz,
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT client_statistics_client_name_unique UNIQUE (client_name)
);
CREATE TABLE IF NOT EXISTS client_table_statistics (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
created_at timestamptz NOT NULL DEFAULT now(),
client_name text NOT NULL,
table_name text NOT NULL,
operation text NOT NULL,
total_operations bigint NOT NULL DEFAULT 0,
error_operations bigint NOT NULL DEFAULT 0,
last_operation_at timestamptz,
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT client_table_statistics_unique UNIQUE (client_name, table_name, operation)
);
CREATE INDEX IF NOT EXISTS idx_client_table_statistics_client_name
ON client_table_statistics (client_name);
CREATE INDEX IF NOT EXISTS idx_client_table_statistics_table_name
ON client_table_statistics (table_name);
CREATE TABLE IF NOT EXISTS client_alert_queries (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
client_name text,
summary text,
db_statement text,
elapsed_secs numeric,
slow_threshold_secs numeric,
rows_affected bigint,
rows_returned bigint
);
CREATE INDEX IF NOT EXISTS idx_client_alert_queries_created_at
ON client_alert_queries (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_client_alert_queries_client_name
ON client_alert_queries (client_name)
WHERE client_name IS NOT NULL;
-- ---------------------------------------------------------------------------
-- Query history and saved queries
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.query_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
query TEXT NOT NULL,
client_name TEXT,
status TEXT NOT NULL DEFAULT 'success',
execution_time_ms DOUBLE PRECISION,
error_message TEXT,
row_count INTEGER,
executed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_query_history_client
ON public.query_history(client_name);
CREATE INDEX IF NOT EXISTS idx_query_history_executed
ON public.query_history(executed_at DESC);
CREATE TABLE IF NOT EXISTS public.saved_queries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
description TEXT,
query TEXT NOT NULL,
client_name TEXT,
schema_name TEXT DEFAULT 'public',
tags TEXT[] DEFAULT '{}',
created_by TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_saved_queries_client
ON public.saved_queries(client_name);
CREATE INDEX IF NOT EXISTS idx_saved_queries_created
ON public.saved_queries(created_at DESC);
-- ---------------------------------------------------------------------------
-- UI request log (used by the Web Explorer)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.ui_request_log (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
method TEXT NOT NULL,
path TEXT NOT NULL,
athena_url TEXT NOT NULL,
client_name TEXT,
status_code INTEGER,
duration_ms DOUBLE PRECISION,
error_message TEXT,
request_body JSONB,
response_preview TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ui_request_log_created
ON public.ui_request_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ui_request_log_client
ON public.ui_request_log(client_name);
CREATE INDEX IF NOT EXISTS idx_ui_request_log_path
ON public.ui_request_log(path);
-- ---------------------------------------------------------------------------
-- Frontend intake tables (feedback, organization requests, project requests)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.feedback (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
category text NOT NULL DEFAULT 'issue',
title text,
message text NOT NULL,
page text,
email text,
client_name text,
organization_name text,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
status text NOT NULL DEFAULT 'new'
);
CREATE INDEX IF NOT EXISTS idx_feedback_created_at
ON public.feedback(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_feedback_status
ON public.feedback(status);
CREATE TABLE IF NOT EXISTS public.organization_requests (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
organization_name text NOT NULL,
organization_type text,
plan text,
requested_by text,
notes text,
client_name text,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
status text NOT NULL DEFAULT 'new'
);
CREATE INDEX IF NOT EXISTS idx_organization_requests_created_at
ON public.organization_requests(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_organization_requests_status
ON public.organization_requests(status);
CREATE TABLE IF NOT EXISTS public.project_requests (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
organization_name text,
project_name text NOT NULL,
region text,
enable_data_api boolean NOT NULL DEFAULT true,
enable_automatic_rls boolean NOT NULL DEFAULT false,
requested_by text,
client_name text,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
status text NOT NULL DEFAULT 'new'
);
CREATE INDEX IF NOT EXISTS idx_project_requests_created_at
ON public.project_requests(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_project_requests_status
ON public.project_requests(status);
-- ---------------------------------------------------------------------------
-- Storage profiles for S3-compatible object storage
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.storage_profiles (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
name text NOT NULL,
provider text NOT NULL DEFAULT 'aws-s3',
endpoint text NOT NULL,
region text NOT NULL DEFAULT 'us-east-1',
bucket text NOT NULL,
access_key_id text,
secret_key text,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_storage_profiles_name
ON public.storage_profiles(name);
CREATE INDEX IF NOT EXISTS idx_storage_profiles_created_at
ON public.storage_profiles(created_at DESC);
"#;