Database Schema

Arkenos uses PostgreSQL with SQLAlchemy ORM and Alembic migrations.

Dual User Table Design

Arkenos maintains two separate user-related tables. This is intentional:

Table	Owned by	Purpose
`"user"`	Better Auth	Authentication only — email, password hash, sessions, email verification
`"users"`	Arkenos backend	Business data — agents, voice sessions, org membership, API keys all reference this table

Why two tables? The users table acts as an auth-agnostic abstraction layer. If the auth provider changes (e.g., switching from Better Auth to Auth.js or a custom solution), only the sync logic needs updating — all business data, foreign keys, and queries remain untouched. How they sync: When a user signs up via Better Auth and makes their first API request, the backend automatically creates a corresponding users row by reading from Better Auth’s "user" table. This auto-creation happens in verify_session_token() in backend/app/dependencies.py. Organization auto-creation follows the same pattern: Better Auth manages the "organization" table, and the backend auto-creates a corresponding organizations row on first access (see _auto_create_org_from_ba() in dependencies.py).

Tables

instance_settings

Encrypted API key store (instance-level, not per-user). Managed via API Keys in the dashboard.

Column	Type	Description
key	String(100)	Primary key (e.g., `livekit_api_key`)
encrypted_value	Text	AES-256-GCM encrypted value
updated_at	DateTime	Last update time

users

Backend user records, auto-created from Better Auth on first authenticated request.

Column	Type	Description
id	String(36)	Primary key (UUID, generated by backend)
auth_id	String(255)	Maps to Better Auth `"user".id` (unique, indexed)
email	String(255)	User email (unique)
name	String(255)	User display name (nullable)
created_at	DateTime	Account creation time
updated_at	DateTime	Last update time

agents

Voice agent configurations.

Column	Type	Description
id	String(36)	Primary key
user_id	String(36)	Foreign key → users
name	String(100)	Agent display name
description	Text	Agent description (nullable)
type	Enum	STT, LLM, TTS, or PIPELINE
config	JSON	Full agent configuration blob
phone_number	String(50)	Telephony provider number (nullable)
provider_number_sid	String(255)	Provider number SID (nullable)
telephony_provider	String(20)	Telephony provider: “twilio” or “telnyx” (nullable, default: “twilio”)
is_active	Boolean	Soft delete flag
agent_mode	Enum	STANDARD or CUSTOM (default: STANDARD)
storage_path	String(500)	MinIO storage path for custom agents (nullable)
image_tag	String(255)	Docker image tag for custom agents (nullable)
build_status	Enum	NONE, PENDING, BUILDING, READY, FAILED
build_error	Text	Last build error message (nullable)
last_build_at	DateTime	Last build timestamp (nullable)
current_version	Integer	Current code version (default: 0)
deployed_version	Integer	Last deployed version (nullable)
created_at	DateTime	Creation time
updated_at	DateTime	Last update time

The config JSON column stores the complete agent configuration:

{
  "system_prompt": "...",
  "first_message": "...",
  "stt_provider": "assemblyai",
  "voice_id": "resemble-voice-uuid",
  "functions": [
    {
      "name": "get_weather",
      "description": "Get current weather",
      "endpoint": "https://api.example.com/weather",
      "method": "GET",
      "parameters": {...}
    }
  ],
  "webhooks": {
    "pre_call": "https://...",
    "post_call": "https://..."
  }
}

voice_sessions

Call session records.

Column	Type	Description
id	String(36)	Primary key
room_name	String(255)	LiveKit room name (unique, indexed)
user_id	String(36)	Foreign key → users
agent_id	String(36)	Foreign key → agents (nullable)
status	Enum	CREATED, ACTIVE, COMPLETED, FAILED
started_at	DateTime	Call start time (nullable)
ended_at	DateTime	Call end time (nullable)
duration	Integer	Duration in seconds (nullable)
call_direction	Enum	INBOUND or OUTBOUND (nullable)
outbound_phone_number	String(50)	Dialed number for outbound calls (nullable)
call_status	Enum	RINGING, ANSWERED, COMPLETED, FAILED, NO_ANSWER (nullable)
callback_url	String(2048)	Webhook callback URL (nullable)
session_data	JSON	Metadata including call analysis
total_cost	Decimal(12,6)	Total session cost (nullable)
cost_breakdown	JSON	Cost breakdown by provider (nullable)
transferred_to	String(50)	Transfer destination number (nullable)
transfer_type	Enum	WARM or COLD (nullable)
transfer_timestamp	DateTime	When transfer occurred (nullable)
created_at	DateTime	Record creation time
updated_at	DateTime	Last update time

transcripts

Per-message conversation logs.

Column	Type	Description
id	String(36)	Primary key
session_id	String(36)	Foreign key → voice_sessions
content	Text	Message content
speaker	Enum	USER or AGENT
timestamp	DateTime	Message time

usage_events

Granular cost tracking events logged by the agent worker.

Column	Type	Description
id	String(36)	Primary key
session_id	String(36)	Foreign key → voice_sessions
user_id	String(36)	Foreign key → users (indexed)
agent_id	String(36)	Foreign key → agents (nullable, indexed)
provider	String(50)	Service provider (e.g., assemblyai, google, resemble)
event_type	Enum	`stt_minutes`, `llm_tokens`, or `tts_characters`
quantity	Decimal(12,4)	Usage quantity
unit_cost	Decimal(12,8)	Cost per unit
total_cost	Decimal(12,6)	Calculated total cost in USD
created_at	DateTime	Event time (indexed)

agent_files

Files uploaded for custom agents, stored in MinIO.

Column	Type	Description
id	String(36)	Primary key
agent_id	String(36)	Foreign key → agents (unique with file_path)
file_path	String(500)	Relative file path within agent storage
content_hash	String(64)	SHA-256 hash of file content (nullable)
size_bytes	BigInteger	File size in bytes
mime_type	String(100)	MIME type (nullable)
version	Integer	Current version number
created_at	DateTime	Creation time
updated_at	DateTime	Last update time

agent_file_versions

Version history for custom agent files.

Column	Type	Description
id	String(36)	Primary key
agent_file_id	String(36)	Foreign key → agent_files
version	Integer	Version number
content_hash	String(64)	SHA-256 hash (nullable)
minio_key	String(500)	MinIO object key
size_bytes	BigInteger	File size in bytes
created_at	DateTime	Creation time

agent_containers

Docker container tracking for custom agents.

Column	Type	Description
id	String(36)	Primary key
agent_id	String(36)	Foreign key → agents
session_id	String(36)	Foreign key → voice_sessions (nullable)
container_id	String(100)	Docker container ID (nullable)
container_type	String(50)	Container type (default: “agent”)
status	Enum	PENDING, RUNNING, STOPPED, FAILED
image_tag	String(255)	Docker image tag (nullable)
started_at	DateTime	Container start time (nullable)
stopped_at	DateTime	Container stop time (nullable)
error_message	Text	Error details (nullable)
created_at	DateTime	Creation time

coding_agent_conversations

Chat conversations with the coding agent (per agent, per user).

Column	Type	Description
id	String(36)	Primary key
agent_id	String(36)	Foreign key → agents (indexed)
user_id	String(255)	Auth user ID
title	String(200)	Conversation title (default: “New conversation”)
created_at	DateTime	Creation time
updated_at	DateTime	Last update time

coding_agent_messages

Individual messages within coding agent conversations.

Column	Type	Description
id	String(36)	Primary key
conversation_id	String(36)	Foreign key → coding_agent_conversations (indexed)
role	String(20)	“user” or “assistant”
content	Text	Message content
file_changes	JSON	File changes made by the assistant (nullable)
created_at	DateTime	Creation time

organizations

Multi-tenancy: top-level tenant for resource scoping. Auto-created from Better Auth’s "organization" table on first access.

Column	Type	Description
id	String(36)	Primary key (UUID)
ba_org_id	String(255)	Better Auth organization ID (unique, indexed)
name	String(255)	Organization display name
slug	String(100)	URL-safe slug (unique)
plan	String(50)	Subscription plan (default: “free”)
stripe_customer_id	String(255)	Stripe customer ID (nullable)
stripe_subscription_id	String(255)	Stripe subscription ID (nullable)
usage_limit_minutes	Integer	Monthly usage limit in minutes (default: 100)
created_at	DateTime	Creation time
updated_at	DateTime	Last update time

org_members

Organization membership with roles.

Column	Type	Description
id	String(36)	Primary key
org_id	String(36)	Foreign key → organizations (indexed)
user_id	String(36)	Foreign key → users (indexed)
role	String(20)	Role: “owner”, “admin”, or “member” (default: “member”)
created_at	DateTime	Creation time

org_api_keys

Per-organization encrypted API key storage.

Column	Type	Description
org_id	String(36)	Foreign key → organizations (composite PK)
key_name	String(100)	Key identifier (composite PK)
encrypted_value	Text	AES-256-GCM encrypted value
updated_at	DateTime	Last update time

user_api_keys

Per-user encrypted API key storage (legacy, migrating to org_api_keys).

Column	Type	Description
user_id	String(36)	Foreign key → users (composite PK)
key_name	String(100)	Key identifier (composite PK)
encrypted_value	Text	AES-256-GCM encrypted value
updated_at	DateTime	Last update time

Migrations

Database migrations are managed by Alembic and run automatically on backend startup:

# Run all pending migrations
alembic upgrade head

# Check current migration state
alembic current

# Create a new migration
alembic revision --autogenerate -m "description"

Fresh Deployment Notes

On a fresh deployment, the backend runs alembic upgrade head on startup to create all tables. If migrations fail, the backend will not start — check the logs for migration errors. Startup order matters: In Docker Compose, PostgreSQL must be healthy before the backend starts. The frontend can start independently since Better Auth tables are created via autoMigrate() in the frontend’s auth.ts.

Better Auth Tables

Better Auth manages its own tables ("user", "session", "account", "verification", "organization", "member", "invitation"). These are created automatically by the frontend’s auth configuration. Do not modify these tables via Alembic — they are owned by Better Auth.

Getting Started

Architecture

Configuration

Features

Database Schema

Database Schema

Dual User Table Design

Tables

instance_settings

users

agents

voice_sessions

transcripts

usage_events

agent_files

agent_file_versions

agent_containers

coding_agent_conversations

coding_agent_messages

organizations

org_members

org_api_keys

user_api_keys

Migrations

Fresh Deployment Notes

Better Auth Tables

Getting Started

Architecture

Configuration

Features

​Database Schema

​Dual User Table Design

​Tables

​instance_settings

​users

​agents

​voice_sessions

​transcripts

​usage_events

​agent_files

​agent_file_versions

​agent_containers

​coding_agent_conversations

​coding_agent_messages

​organizations

​org_members

​org_api_keys

​user_api_keys

​Migrations

​Fresh Deployment Notes

​Better Auth Tables

Database Schema

Dual User Table Design

Tables

instance_settings

users

agents

voice_sessions

transcripts

usage_events

agent_files

agent_file_versions

agent_containers

coding_agent_conversations

coding_agent_messages

organizations

org_members

org_api_keys

user_api_keys

Migrations

Fresh Deployment Notes

Better Auth Tables