Database Schema

This page describes the current PostgreSQL schema created by migrations in backend/src/backend/database/migrations. All tables live in the infonchat schema unless noted.

Overview

erDiagram
    USERS ||--o{ AGENTS : owns
    USERS ||--o{ USER_CHATS : owns
    USERS ||--o{ PASSWORD_CREDENTIALS : authenticates
    USERS ||--o{ PROVIDERS : configures
    USERS ||--o{ MCP_SERVERS : registers
    USERS ||--o{ MCP_TOOLS : owns
    USERS ||--o{ MCP_RESOURCES : owns
    USERS ||--o{ TOOL_EMBEDDINGS : indexes
    USERS ||--o{ RESOURCE_EMBEDDINGS : indexes
    USERS ||--o{ EMBEDDING_INDEXES : manages

    AGENTS ||--o{ CHAT_MESSAGES : writes
    USER_CHATS ||--o{ CHAT_MESSAGES : contains
    CHAT_MESSAGES ||--o{ CHAT_MESSAGE_TOOLS : links
    CHAT_MESSAGES ||--o{ CHAT_MESSAGE_RESOURCES : links
    MCP_SERVERS ||--o{ MCP_TOOLS : exposes
    MCP_SERVERS ||--o{ MCP_RESOURCES : exposes
    MCP_RESOURCES ||--o{ RESOURCE_EMBEDDINGS : indexed_by
    MCP_TOOLS ||--o{ TOOL_EMBEDDINGS : indexed_by
    EMBEDDING_INDEXES ||--o{ TOOL_EMBEDDINGS : uses
    EMBEDDING_INDEXES ||--o{ RESOURCE_EMBEDDINGS : uses

    USERS {
        UUID id PK
        TEXT username
        TEXT display_name
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    PASSWORD_CREDENTIALS {
        UUID user_id PK, FK
        TEXT password_hash
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
        TIMESTAMPTZ last_login
    }

    AGENTS {
        UUID id PK
        UUID user_id FK
        TEXT name
        TEXT description
        TEXT system_prompt
        TEXT default_model
        TIMESTAMPTZ created_at
        BOOLEAN is_default
    }

    USER_CHATS {
        UUID id PK
        UUID user_id FK
        TEXT title
        TIMESTAMPTZ created_at
    }

    CHAT_MESSAGES {
        UUID id PK
        UUID chat_id FK
        UUID agent_id FK
        TEXT message_kind
        TEXT role
        JSONB content
        TEXT tool_call_id
        JSONB tool_calls
        JSONB tool_call_results
        INT position
        TIMESTAMPTZ created_at
    }

    CHAT_MESSAGE_TOOLS {
        UUID message_id PK, FK
        UUID tool_id PK, FK
        TIMESTAMPTZ created_at
    }

    CHAT_MESSAGE_RESOURCES {
        UUID id PK
        UUID message_id FK
        UUID resource_id FK
        TEXT uri
        TIMESTAMPTZ created_at
    }

    PROVIDERS {
        UUID id PK
        UUID user_id FK
        TEXT name
        JSONB options
    }

    MCP_SERVERS {
        UUID id PK
        UUID user_id FK
        TEXT name
        TEXT url
        JSONB headers
        JSONB init_message
        TIMESTAMPTZ created_at
    }

    MCP_TOOLS {
        UUID id PK
        UUID user_id FK
        UUID server_id FK
        TEXT name
        JSONB payload
        TIMESTAMPTZ created_at
    }

    MCP_RESOURCES {
        UUID id PK
        UUID user_id FK
        UUID server_id FK
        TEXT uri
        TEXT name
        JSONB payload
        TIMESTAMPTZ created_at
    }

    TOOL_EMBEDDINGS {
        UUID id PK
        UUID user_id FK
        UUID tool_id FK
        UUID index_id FK
        INT embedding_dim
        VECTOR embedding
        BYTEA source_hash
        TIMESTAMPTZ created_at
    }

    RESOURCE_EMBEDDINGS {
        UUID id PK
        UUID user_id FK
        UUID resource_id FK
        UUID index_id FK
        INT embedding_dim
        VECTOR embedding
        BYTEA source_hash
        TIMESTAMPTZ created_at
    }

    EMBEDDING_INDEXES {
        UUID id PK
        UUID user_id FK
        TEXT name
        TEXT model_name
        JSONB source_types
        BOOLEAN enabled
        TIMESTAMPTZ refresh_requested_at
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

Tables

infonchat.users

• Core user record. Uses pgcrypto for gen_random_uuid().

infonchat.password_credentials

• One-to-one credential record for password auth.
• user_id is both PK and FK, with ON DELETE CASCADE.

infonchat.sessions

• Per-user refresh session tracking for long-lived authentication.
• Stores client_name, refresh_token_suffix, hashed refresh token, and timestamps such as last_login, created_at, updated_at, plus optional revoked_at for terminated sessions.

infonchat.agents

• Agent configuration owned by a user.
• is_default indicates the default agent per user (not enforced by a DB constraint).

infonchat.user_chats

• Chat sessions per user.

infonchat.chat_messages

• Normalized message records for a chat.
• agent_id attributes assistant/tool messages to the agent used for that turn.
• message_kind is message or tool_call.
• role is user, assistant, tool, system, or tool_call.

infonchat.chat_message_tools

• Many-to-many link between messages and MCP tools.

infonchat.chat_message_resources

• Many-to-many link between messages and MCP resources.
• Exactly one of resource_id (cataloged) or uri (external) is set.

infonchat.providers

• Per-user provider configuration.
• Uniqueness enforced by (user_id, name).

infonchat.mcp_servers

• Registered MCP servers, including optional headers and init_message JSON.

infonchat.mcp_tools

• Tools available on an MCP server.
• Uniqueness enforced by (server_id, name).

infonchat.mcp_resources

• Resources available on an MCP server.
• Uniqueness enforced by (server_id, uri).

infonchat.tool_embeddings

• Vector embeddings for MCP tools (requires pgvector).
• Linked to embedding_indexes via index_id.
• Indexed by user/index/dim and tool_id, with a latest-by-created_at index.

infonchat.resource_embeddings

• Vector embeddings for MCP resources (requires pgvector).
• Linked to embedding_indexes via index_id.
• Indexed by user/index/dim and resource_id, with a latest-by-created_at index.

infonchat.embedding_indexes

• Per-user embedding index configuration.
• Uniqueness enforced by (user_id, model_name) and (user_id, name).

infonchat.schema_migrations

• Tracks applied migrations; created in backend/src/backend/database/initialise.py.