Database Schema

oAI-Web uses two PostgreSQL databases: - aide — main application database (schema v28) - brain — 2nd Brain vector search database (separate instance, pgvector)

Migrations are applied automatically at startup. The migration system is append-only: new migrations are added to the _MIGRATIONS list in server/database.py and run once in order.

---

aide database

schema_version

Tracks which migrations have been applied.

version INTEGER PRIMARY KEY

credentials

Encrypted key-value store for API keys and system settings.

key         TEXT PRIMARY KEY
value_enc   TEXT NOT NULL      -- AES-256-GCM encrypted, base64-encoded
description TEXT
created_at  TEXT NOT NULL
updated_at  TEXT NOT NULL

Well-known keys:

Key	Value
system:anthropic_api_key	Anthropic API key
system:openrouter_api_key	OpenRouter API key
system:openai_api_key	OpenAI API key
system:default_provider	"anthropic", "openrouter", or "openai"
system:session_secret	Auto-generated HMAC signing secret for session cookies
system:max_tool_calls	Runtime limit override
system:max_autonomous_runs_per_hour	Runtime limit override
system:max_concurrent_runs	Concurrency semaphore value
system:paused	"1" when the kill switch is active
system:audit_retention_days	Days to retain audit log entries
system:users_base_folder	Base path for user file folders
system:security_*	Security option flags
system:trusted_proxy_ips	Comma-separated trusted proxy IPs

users

Multi-user authentication.

id            TEXT PRIMARY KEY  -- UUID (stored as TEXT, not UUID type)
username      TEXT UNIQUE NOT NULL
password_hash TEXT NOT NULL      -- Argon2id
role          TEXT NOT NULL      -- 'admin' | 'user'
is_active     BOOLEAN NOT NULL DEFAULT TRUE
totp_secret   TEXT               -- Base32 TOTP secret (NULL = MFA disabled)
email         TEXT               -- Optional, for account recovery
created_at    TEXT NOT NULL
updated_at    TEXT NOT NULL

user_settings

Per-user key-value store (plaintext).

user_id   TEXT NOT NULL REFERENCES users(id)
key       TEXT NOT NULL
value     TEXT
PRIMARY KEY (user_id, key)

Common keys:

Key	Value
personality_soul	Per-user SOUL.md override
personality_user	Per-user USER.md override
brain_auto_approve	"1" if brain tool auto-approved
brain_mcp_key	Per-user Brain MCP API key
pushover_user_key	Per-user Pushover key
anthropic_api_key	Per-user Anthropic API key override
preferred_theme	UI theme preference

audit_log

Append-only tool call log.

id             BIGSERIAL PRIMARY KEY
timestamp      TEXT NOT NULL        -- ISO8601 UTC
session_id     TEXT                  -- NULL for scheduled tasks
tool_name      TEXT NOT NULL
arguments      JSONB
result_summary TEXT
confirmed      BOOLEAN NOT NULL DEFAULT FALSE
task_id        TEXT                  -- NULL for interactive sessions
user_id        TEXT                  -- NULL for system/scheduled

Indexes: timestamp, session_id, tool_name

conversations

Chat history.

id         TEXT PRIMARY KEY  -- session UUID
started_at TEXT
ended_at   TEXT
messages   JSONB             -- array of {role, content, tool_calls?, ...}
task_id    TEXT
user_id    TEXT
title      TEXT              -- derived from first user message
model      TEXT              -- last model used

Note: messages uses JSONB. asyncpg requires manual codec registration to auto-serialize dicts — see database.py::_init_connection().

---

Agent tables

agents

Agent definitions.

id                  TEXT PRIMARY KEY  -- UUID
name                TEXT NOT NULL
description         TEXT
prompt              TEXT NOT NULL
model               TEXT NOT NULL
can_create_subagents BOOLEAN DEFAULT FALSE
allowed_tools       JSONB              -- NULL = all tools; [] also treated as NULL
schedule            TEXT               -- cron expression; NULL = manual only
enabled             BOOLEAN DEFAULT TRUE
max_tool_calls      INTEGER            -- NULL = use system default
prompt_mode         TEXT DEFAULT 'combined'  -- 'combined' | 'system_only' | 'agent_only'
parent_agent_id     TEXT               -- NULL = top-level
created_by          TEXT               -- 'user' or parent agent ID
owner_user_id       TEXT               -- NULL = system agent
created_at          TEXT NOT NULL
updated_at          TEXT NOT NULL

prompt_mode values: - combined: agent prompt prepended to standard system prompt (SOUL.md + security rules) - system_only: standard system prompt only; agent prompt sent as the task message - agent_only: agent prompt replaces the entire system prompt (no SOUL.md, no security rules — use with caution)

agent_runs

Execution history.

id            TEXT PRIMARY KEY  -- UUID
agent_id      TEXT NOT NULL REFERENCES agents(id)
started_at    TEXT NOT NULL
ended_at      TEXT
status        TEXT   -- 'running' | 'success' | 'error' | 'stopped'
input_tokens  INTEGER DEFAULT 0
output_tokens INTEGER DEFAULT 0
cost_usd      REAL               -- NULL placeholder
result        TEXT               -- final response text
error         TEXT               -- error message if status='error'

scheduled_tasks

Simple cron tasks (predecessor to agents; still used for lightweight automation).

id          TEXT PRIMARY KEY
name        TEXT NOT NULL
description TEXT
schedule    TEXT
prompt      TEXT NOT NULL
allowed_tools TEXT  -- JSON array
enabled     BOOLEAN DEFAULT TRUE
last_run    TEXT
last_status TEXT
created_at  TEXT
updated_at  TEXT

---

Security tables

email_whitelist

email       TEXT PRIMARY KEY
daily_limit INTEGER DEFAULT 0    -- 0 = unlimited
created_at  TEXT NOT NULL

web_whitelist

Tier 1 trusted domains for the web tool.

domain      TEXT PRIMARY KEY    -- normalized lowercase hostname
note        TEXT
created_at  TEXT NOT NULL

Seeded on first migration: duckduckgo.com, wikipedia.org, weather.met.no, api.met.no, yr.no, timeanddate.com

Subdomain matching is implemented in code: wikipedia.org automatically covers en.wikipedia.org.

filesystem_whitelist

path        TEXT PRIMARY KEY    -- absolute path, symlinks resolved
note        TEXT
created_at  TEXT NOT NULL

browser_approved_domains

Per-user trusted domains for interactive browser operations (skip confirmation).

id            TEXT PRIMARY KEY
owner_user_id TEXT NOT NULL     -- TEXT, not UUID type
domain        TEXT NOT NULL
note          TEXT
created_at    TEXT NOT NULL
UNIQUE(owner_user_id, domain)

---

Integration tables

email_accounts

Multi-account IMAP/SMTP configuration.

id                  TEXT PRIMARY KEY
user_id             TEXT               -- owner user; NULL = global
label               TEXT NOT NULL
account_type        TEXT NOT NULL      -- 'trigger' | 'handling'
imap_host           TEXT
imap_port           INTEGER DEFAULT 993
imap_username       TEXT
imap_password_enc   TEXT               -- AES-GCM encrypted
smtp_host           TEXT
smtp_port           INTEGER DEFAULT 465
smtp_username       TEXT
smtp_password_enc   TEXT
agent_id            TEXT               -- agent to dispatch on trigger
monitored_folders   JSONB              -- NULL = all folders
initial_load_done   BOOLEAN DEFAULT FALSE
initial_load_limit  INTEGER DEFAULT 200
extra_tools         JSONB              -- additional tools for handling accounts
telegram_chat_id    TEXT               -- optional: route to Telegram chat
telegram_keyword    TEXT               -- optional: Telegram /keyword routing
enabled             BOOLEAN DEFAULT TRUE
created_at          TEXT
updated_at          TEXT

email_triggers

id           TEXT PRIMARY KEY
trigger_word TEXT NOT NULL
agent_id     TEXT NOT NULL
account_id   TEXT
user_id      TEXT
enabled      BOOLEAN DEFAULT TRUE
created_at   TEXT

telegram_whitelist

id       TEXT PRIMARY KEY
chat_id  TEXT NOT NULL
user_id  TEXT               -- NULL = global whitelist entry
label    TEXT
UNIQUE(chat_id, user_id) NULLS NOT DISTINCT

telegram_triggers

id           TEXT PRIMARY KEY
trigger_word TEXT NOT NULL
agent_id     TEXT NOT NULL
user_id      TEXT
enabled      BOOLEAN DEFAULT TRUE
created_at   TEXT

webhook_endpoints

Inbound webhook endpoints that can trigger agents via HTTP POST.

id               TEXT PRIMARY KEY
name             TEXT NOT NULL
token            TEXT NOT NULL UNIQUE    -- used in URL: /webhook/{token}
agent_id         TEXT
owner_user_id    TEXT
allow_get        BOOLEAN DEFAULT FALSE
enabled          BOOLEAN DEFAULT TRUE
trigger_count    INTEGER DEFAULT 0
last_triggered_at TEXT
created_at       TEXT

webhook_targets

Outbound webhook destinations.

id            TEXT PRIMARY KEY
name          TEXT NOT NULL
url           TEXT NOT NULL
secret_header TEXT               -- optional header name + value for auth
owner_user_id TEXT
enabled       BOOLEAN DEFAULT TRUE
created_at    TEXT

mcp_servers

MCP client configurations.

id          TEXT PRIMARY KEY
name        TEXT NOT NULL      -- used for tool namespacing
url         TEXT NOT NULL
transport   TEXT DEFAULT 'sse'
api_key_enc TEXT               -- encrypted
headers_enc TEXT               -- encrypted (additional headers)
user_id     TEXT               -- NULL = global (admin-only)
enabled     BOOLEAN DEFAULT TRUE
created_at  TEXT

---

Monitoring tables

watched_pages

CSS-selector page change monitors.

id                  TEXT PRIMARY KEY
url                 TEXT NOT NULL
schedule            TEXT NOT NULL     -- cron expression
css_selector        TEXT              -- NULL = full page hash
last_content_hash   TEXT
last_changed_at     TEXT
last_checked_at     TEXT
agent_id            TEXT
notification_mode   TEXT DEFAULT 'agent'  -- 'agent' | 'push'
owner_user_id       TEXT
enabled             BOOLEAN DEFAULT TRUE
created_at          TEXT

rss_feeds

RSS/Atom feed monitors.

id                TEXT PRIMARY KEY
url               TEXT NOT NULL
schedule          TEXT NOT NULL
agent_id          TEXT
seen_item_ids     JSONB DEFAULT '[]'
max_items_per_run INTEGER DEFAULT 5
last_fetched_at   TEXT
last_etag         TEXT             -- HTTP ETag for conditional fetching
last_modified     TEXT             -- HTTP Last-Modified header
notification_mode TEXT DEFAULT 'agent'
owner_user_id     TEXT
enabled           BOOLEAN DEFAULT TRUE
created_at        TEXT

mfa_challenges

TOTP second-factor login sessions.

id         TEXT PRIMARY KEY
token      TEXT UNIQUE NOT NULL
user_id    TEXT NOT NULL
next_url   TEXT DEFAULT '/'
created_at TEXT NOT NULL
expires_at TEXT NOT NULL
attempts   INTEGER DEFAULT 0

---

brain database

The brain database is separate and uses the pgvector extension.

documents

document_id TEXT PRIMARY KEY
content     TEXT NOT NULL
embedding   vector(1536)     -- OpenAI text-embedding-3-small dimensions
metadata    JSONB
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()

Index: ivfflat or hnsw on embedding for approximate nearest-neighbour search.

---

asyncpg JSONB gotcha

asyncpg does not automatically serialize Python dicts to PostgreSQL JSONB. Every connection must register custom codecs:

await conn.set_type_codec("jsonb", encoder=json.dumps, decoder=json.loads, schema="pg_catalog")
await conn.set_type_codec("json",  encoder=json.dumps, decoder=json.loads, schema="pg_catalog")

This is done in database.py::_init_connection() which is passed to asyncpg.create_pool(init=_init_connection).