Database Schema

Open Assistant uses SQLite (or PostgreSQL in production) to persist conversations, task history, credentials, and system state.

Entity Relationship Diagram

erDiagram
    conversations ||--o{ messages : contains
    conversations ||--o{ agent_tasks : triggers
    conversations ||--o{ conversation_memory : stores
    conversations ||--o{ future_tasks : schedules
    cron_jobs ||--o{ job_executions : has
    future_tasks ||--o{ job_executions : has
    agent_definitions ||--o{ agent_tasks : defines
    artifacts {
        int id PK
        text artifact_id UK
        text title
        text filename
        text rel_path
        text mime_type
        int size
        int is_public
        text secret_hash
        timestamp created_at
        timestamp updated_at
    }

    conversations {
        int id PK
        text conversation_id UK
        text channel
        text contact_identifier
        int context_version
        timestamp last_accessed
        timestamp created_at
        timestamp updated_at
        json metadata
    }

    messages {
        int id PK
        text conversation_id FK
        text message_id UK
        text role
        text content
        int token_count
        boolean is_summary
        boolean is_internal
        timestamp timestamp
        json metadata
    }

    llm_consumption {
        int id PK
        timestamp timestamp
        text provider
        text model
        int prompt_tokens
        int completion_tokens
        int total_tokens
        int cached_tokens
        int reasoning_tokens
        text conversation_id
        json metadata
    }

    agent_definitions {
        int id PK
        text name UK
        text display_name
        text role
        text goal
        text backstory
        json tools
        int priority
        json intent_keywords
        text category
        boolean enabled
        boolean allow_delegation
        timestamp created_at
        timestamp updated_at
    }

    agent_tasks {
        int id PK
        text task_id UK
        text conversation_id FK
        text agent_name
        text action
        json parameters
        text status
        json result
        text error_message
        timestamp created_at
        timestamp started_at
        timestamp completed_at
    }

    cron_jobs {
        int id PK
        text job_id UK
        text name
        text description
        text cron_expression
        text job_type
        text tool_name
        json tool_parameters
        text prompt
        boolean enabled
        timestamp last_run_at
        timestamp next_run_at
        timestamp created_at
        timestamp updated_at
    }

    future_tasks {
        int id PK
        text task_id UK
        text name
        text description
        text conversation_id FK
        timestamp scheduled_time
        text job_type
        text tool_name
        json tool_parameters
        text prompt
        text status
        json result
        text error_message
        timestamp created_at
        timestamp completed_at
    }

    job_executions {
        int id PK
        text job_id FK
        text job_type
        timestamp started_at
        timestamp completed_at
        text status
        json result
        text error_message
        text container_id
    }

    service_credentials {
        int id PK
        text service_name UK
        text credential_type
        text credential_data
        timestamp expires_at
        timestamp created_at
        timestamp updated_at
    }

    service_connections {
        int id PK
        text service_name UK
        text status
        timestamp last_check
        text last_error
        json metadata
        timestamp updated_at
    }

    prompts {
        int id PK
        text key UK
        text value
        text description
        timestamp created_at
        timestamp updated_at
    }

    settings {
        int id PK
        text key UK
        text value
        text value_type
        text description
        text category
        boolean is_required
        boolean is_sensitive
        text validation_regex
        real min_value
        real max_value
        json options
        int display_order
        timestamp updated_at
    }

    audit_log {
        int id PK
        timestamp timestamp
        text event_type
        text service_name
        text agent_name
        text action
        text conversation_id
        json details
        boolean success
        text error_message
        text user_id
        text ip_address
    }

    conversation_memory {
        int id PK
        text conversation_id FK
        text memory_type
        json content
        timestamp created_at
    }

    search_index {
        int id PK
        text source
        text source_id
        text title
        text content
        text content_hash
        blob embedding
        json metadata
        timestamp indexed_at
    }

    schema_migrations {
        int id PK
        text version UK
        timestamp applied_at
    }

Schema Overview

Core Tables

1. Conversations

Stores conversation sessions across all communication channels.

Purpose: Track conversation context and history per channel Key Fields: - conversation_id: Unique identifier for the conversation - channel: Communication channel (whatsapp, webui) - contact_identifier: Phone number or session ID - context_version: Context management version - last_accessed: Last activity timestamp

Relationships: - One-to-many with messages - One-to-many with agent_tasks - One-to-many with conversation_memory

2. Messages

Individual messages within conversations.

Purpose: Store complete conversation history with role-based messages Key Fields: - role: Message source (user, assistant, system) - content: Message text - token_count: Number of tokens in the message (local estimate, used by the conversation-stats UI; not the billing source — see llm_consumption below) - is_summary: Whether this message is a generated summary - is_internal: Transparency rows (system prompt and auxiliary LLM outputs persisted for visibility) are flagged is_internal=1 and excluded from the conversation history re-sent to the LLM on subsequent turns. Billing-neutral. - metadata: Attachments, agent info, etc. (internal rows carry a kind tag such as planner, memory_summary, document; compacted rows carry {"compacted": true, ...})

Relationships: - Many-to-one with conversations

3. LLM Consumption

Per-call LLM token usage ledger, the source of truth for metered billing.

Purpose: Capture the provider's authoritative response.usage (input, output, cached, reasoning tokens) on every LLM call, replacing the legacy SUM(messages.token_count) estimate that only counted visible message text and missed all input/context tokens and auxiliary calls. The platform polls GET /managed/usage, which sums this table by month.

Key Fields: - prompt_tokens / completion_tokens / total_tokens: Real provider usage - cached_tokens / reasoning_tokens: Breakdown when the provider reports it (prompt_tokens_details.cached_tokens, completion_tokens_details.reasoning_tokens) - conversation_id: Optional, for traceability - metadata: Flags such as missing_usage (provider returned no usage), openrouter_cost, baseline (the deploy-time seed bridging the platform's lifetime watermark), and compacted (summary rows produced by the nightly job)

Relationships: - Optional many-to-one with conversations (nullable)

Compaction: A nightly system job collapses rows older than the application.message_retention_days setting (default 90) into one summary row per (year, month), preserving all token totals. The deploy-time baseline row (current-month-dated) bridges the platform's lifetime watermark so new accurate usage is credited immediately instead of being held back by its max() guard.

4. Agent Definitions

Stores CrewAI agent configurations.

Purpose: Define agents with their roles, goals, and tool assignments Key Fields: - name: Unique agent identifier (coordinator, research, communication, writer, file_handler, planner, navigator, system, browser) - role: Agent role description - goal: What the agent aims to achieve - backstory: System prompt / detailed instructions - tools: JSON array of assigned tool names - priority: Selection priority (1-10, higher = selected first) - intent_keywords: JSON array of keywords for skill matching - category: Agent category - allow_delegation: Whether agent can delegate to others

Relationships: - One-to-many with agent_tasks

Default Agents: - system - System introspection and memory management - navigator - Geographic & route planning - file_handler - File management across Nextcloud/OneDrive - writer - Content writing - browser - Interactive web browsing - planner - Calendar and scheduling - communication - Email and messaging - research - Information retrieval and search - coordinator - Task coordination and delegation

See Agent Architecture for detailed agent definitions.

5. Agent Tasks

Tracks tasks executed by specialized agents.

Purpose: Monitor agent execution, results, and errors Key Fields: - agent_name: Which agent handled the task - action: What action was performed - status: pending, running, completed, failed - result: Task output data

Relationships: - Many-to-one with conversations (nullable) - Many-to-one with agent_definitions

Scheduling Tables

6. Cron Jobs

Scheduled recurring tasks with Docker-isolated execution.

Purpose: Define and manage scheduled tasks (tool calls or prompts) Key Fields: - cron_expression: Cron schedule (e.g., "0 9 * * MON") - job_type: 'tool' for direct execution, 'prompt' for agent processing - tool_name: Tool to execute (if job_type='tool') - tool_parameters: JSON parameters for tool - prompt: Prompt to send to coordinator (if job_type='prompt') - enabled: Active/inactive status - next_run_at: Calculated next execution time

Relationships: - One-to-many with job_executions

7. Future Tasks

One-time scheduled tasks.

Purpose: Schedule tasks to execute once at a specific time Key Fields: - name: Human-readable task name - description: Task description - conversation_id: Associated conversation (nullable) - scheduled_time: When to execute - job_type: 'tool' or 'prompt' - status: pending, completed, failed, cancelled - Same tool_name/tool_parameters/prompt fields as cron_jobs

Relationships: - Many-to-one with conversations (nullable) - One-to-many with job_executions

8. Job Executions

Unified execution history for both cron jobs and future tasks.

Purpose: Track execution history for monitoring and debugging Key Fields: - job_id: References cron_jobs.job_id or future_tasks.task_id - job_type: 'cron' or 'future_task' - status: running, success, failed - container_id: Docker container ID (for isolated execution) - result: Execution output - error_message: Failure details

Relationships: - Many-to-one with cron_jobs

Service Integration Tables

9. Service Credentials

Encrypted storage for OAuth tokens and API keys.

Purpose: Securely store authentication credentials Key Fields: - service_name: gmail, outlook, notion, etc. - credential_type: oauth_token, api_key, app_password - credential_data: Encrypted JSON blob - expires_at: Token expiration timestamp

Security: All credential_data is encrypted using Fernet symmetric encryption

10. Service Connections

Track connection status of integrated services.

Purpose: Monitor service health and connectivity Key Fields: - service_name: Service identifier (unique) - status: connected, disconnected, error - last_check: Last health check timestamp - last_error: Most recent error message - metadata: Service-specific info (email, username)

Personalization Tables

11. Prompts

Stores assistant personalization configuration: system prompt, memory, and soul.

Purpose: Enable users to customize the assistant's behavior, knowledge, and personality Key Fields: - key: Prompt identifier (system_prompt_default, system_prompt_custom, memory, soul) - value: Prompt text content - description: Explanation of the prompt's purpose

Default Entries: - system_prompt_default: Base system prompt defining core assistant behavior - system_prompt_custom: User-defined custom instructions added on top of the default - memory: Text-based store for user context (name, preferences, people, places, relations) - soul: Personality and communication style description, shaped by user feedback

System Tables

12. Settings

Application-wide configuration and user preferences.

Purpose: Store dynamic configuration values Key Fields: - key: Setting identifier - value: Setting value (stored as string) - value_type: Type hint for parsing (string, int, bool, json) - category: Setting category (default: 'application') - is_required: Whether the setting is required - is_sensitive: Whether the value is sensitive (e.g., API keys) - validation_regex: Optional regex for value validation - min_value/max_value: Numeric validation bounds - options: JSON array of valid values (for enum-like settings) - display_order: UI ordering hint

13. Audit Log

Complete audit trail of system operations.

Purpose: Security auditing and debugging Key Fields: - event_type: Type of event (api_call, agent_action, etc.) - success: Operation outcome - details: Event-specific data - user_id: Associated user identifier - ip_address: Client IP address

14. Conversation Memory

Stores conversation context and memory for each conversation.

Purpose: Enable context-aware responses with short-term and long-term memory Key Fields: - memory_type: Type of memory (short_term, long_term, facts, working) - content: JSON containing memory data - created_at: When memory was stored

Relationships: - Many-to-one with conversations

Memory Types: - short_term: Recent messages (last 10-20) - long_term: Conversation summaries - facts: Extracted facts (names, preferences, dates) - working: Current task context

15. Artifacts

Durable storage metadata for files persisted via the store_artifact tool.

Purpose: Track generated files (HTML, PDF, DOCX, images, etc.) with visibility and passphrase controls Key Fields: - artifact_id: UUID identifying the artifact (also used as the on-disk directory name) - filename: Original filename - rel_path: Path relative to the artifacts directory (<uuid>/<filename>) - mime_type: Detected MIME type (used for Content-Type when serving) - size: File size in bytes - is_public: 1 = publicly accessible via permanent link; 0 = private (temp-link only) - secret_hash: PBKDF2-SHA256 hash of the passphrase gate (NULL = no gate); the plaintext passphrase is never stored

On-disk layout: data/artifacts/<artifact_id>/<filename>

Relationships: None (standalone table; not tied to a specific conversation)

16. Search Index

Stores searchable content with embeddings for semantic search.

Purpose: Enable semantic search across indexed content Key Fields: - source: Source system (e.g., 'memory', 'file', 'email') - source_id: Unique ID within the source system - title: Content title - content: Full text content - content_hash: Hash for deduplication - embedding: Vector embedding (BLOB) - metadata: Additional source-specific data - indexed_at: When content was indexed

Constraints: Unique on (source, source_id)

17. Schema Migrations

Tracks applied database migrations.

Purpose: Prevent re-running migrations on startup Key Fields: - version: Migration version identifier (unique) - applied_at: When the migration was applied

Data Flow Diagram

flowchart TD
    User[User Request] --> Channel{Channel}
    Channel -->|WhatsApp| WA[WhatsApp Handler]
    Channel -->|Web UI| UI[Web UI]

    WA --> Conv[Create/Get Conversation]
    UI --> Conv

    Conv --> Msg[Store Message]
    Msg --> Orch[Orchestrator]

    Orch --> Agent[Select Agent]
    Agent --> Task[Create Agent Task]
    Task --> Execute[Execute Action]

    Execute --> Service[Call External Service]
    Service --> Cred[(Service Credentials)]
    Service --> Conn[(Service Connections)]

    Execute --> Result[Store Result]
    Result --> Response[Create Response Message]
    Response --> Audit[(Audit Log)]

    Response --> User

Indexing Strategy

High-Traffic Queries

• messages.conversation_id - Frequently queried for conversation history
• agent_tasks.status - Task monitoring queries
• conversations.updated_at - Recent conversations listing
• audit_log.timestamp - Time-based audit queries

Optimization

• Composite indexes on frequently filtered combinations
• Foreign key indexes for join performance
• Timestamp indexes for time-range queries

Data Retention Policies

Table	Retention Policy
artifacts	Indefinite (user-managed via Artifacts tab)
conversations	Indefinite (user-managed)
messages	Indefinite (user-managed); rows older than application.message_retention_days (default 90) are collapsed into one internal summary row per conversation by the nightly compaction job. Token totals are preserved.
llm_consumption	Per-call rows older than application.message_retention_days (default 90) are collapsed into one summary row per (year, month) by the nightly compaction job. Monthly summaries are retained indefinitely (billing needs the trailing 12 months). Token totals are preserved.
agent_tasks	Last 1000 per conversation
cron_job_executions	Last 100 per job
audit_log	30 days
service_credentials	Until revoked
service_connections	Until disconnected
prompts	Indefinite (user-managed)
settings	Indefinite
conversation_memory	Short-term: 20 msgs, Long-term: indefinite
future_tasks	90 days after execution

Encryption

Encrypted Fields

• service_credentials.credential_data - Fernet symmetric encryption

Encryption Key Management

• Key stored in environment variable: ENCRYPTION_KEY
• Generated using: Fernet.generate_key()
• Never committed to version control

Backup Strategy

flowchart LR
    DB[(SQLite DB)] -->|Daily| Backup[Automated Backup]
    Backup --> S3[Backup Storage]
    DB -->|Manual| ManualBackup[Manual Backup]
    ManualBackup --> Local[Local Storage]

Automated Backups: - Frequency: Daily at 3 AM - Retention: Last 7 daily, 4 weekly, 3 monthly - Location: data/backups/

Manual Backups:

python -m src.core.database backup

Database Files

• data/assistant.db - Main application database
• data/jobs.db - APScheduler job store (managed by APScheduler)
• data/backups/ - Database backups directory

Migration Management

Migrations are located in src/core/migrations/ and applied automatically on startup.

Migration Format: {version}_{description}.sql

Example: 043_oss_db_init.sql

Applying Migrations:

python -m src.core.database init

Migration Tracking: schema_migrations table tracks applied migrations

Performance Considerations

SQLite Limitations

• Single writer at a time
• Not suitable for high-concurrency writes
• Consider PostgreSQL for production

Optimization Tips

• Use connection pooling
• Enable WAL mode for better concurrency
• Regular VACUUM operations
• Monitor database size

Migration to PostgreSQL

The schema is designed to be compatible with PostgreSQL. Migration path: 1. Export data from SQLite 2. Update DATABASE_URL to PostgreSQL connection string 3. Run migrations on PostgreSQL 4. Import data

Schema Versioning

Schema changes are tracked through: - Migration files in src/core/migrations/ - schema_migrations table entries - Documentation updates in this file

Related Documentation

• Database Implementation - Python implementation
• Migration Files - SQL migration scripts