Database Schemas

By Olivier Mironescu Added: 2026-07-09

Database Schemas

Full SQL and field-level documentation for all five core.ai tables.

Table Overview

Core Tables

conversations

    │

    ├── messages               (what was said — sent to LLM)

    ├── trace_events           (how it was produced — UI display only)

    ├── api_calls              (one per LLM request — audit trail)

    │     └── cost_ledger      (one per api_call — financial record)

    └── media                  (file attachments per message)

Supporting Tables

system_prompts               (prompt file catalog)

skills                       (developer-facing prompt+model configs)

presets                      (user-facing conversation templates)

tools                        (tool registry, per project)

1. conversations

The top-level container. Holds settings, defaults, and metadata for a chat thread.

CREATE TABLE conversations (

    id                      UUID PRIMARY KEY,

    project_id              UUID NOT NULL,                   -- FK → core.console projects

    user_id                 INT NOT NULL,                    -- FK → core.auth users



    -- Display

    title                   VARCHAR(500),                    -- auto-generated or user-set

    summary                 TEXT,                            -- rolling summary for context compression



    -- Defaults (overridable per message)

    default_model_slug      VARCHAR(255) NOT NULL,           -- pre-selected model for UI

    system_prompt           TEXT NOT NULL,                   -- base system prompt (with preset pre-prompt folded in)

    system_prompt_fingerprint VARCHAR(64),                   -- SHA-256 hash for cache detection



    -- Tools available in this conversation

    tool_ids                JSON,                            -- [UUID, UUID, ...] referencing tools table



    -- Preset origin

    preset_id               UUID,                            -- FK → presets (nullable)



    -- State

    status                  ENUM('active', 'archived', 'deleted') DEFAULT 'active',

    message_count           INT DEFAULT 0,



    -- Timestamps

    created_at              TIMESTAMP NOT NULL,

    updated_at              TIMESTAMP NOT NULL,

    last_message_at         TIMESTAMP,



    INDEX idx_project_user (project_id, user_id, updated_at),

    INDEX idx_status (status, last_message_at)

);

Field Notes

system_prompt — The full system prompt for this conversation. If created from a preset, the preset's pre_prompt is folded in at creation time. Not a separate message. One system prompt per conversation.

system_prompt_fingerprint — SHA-256 of the system prompt text. Used to detect when the prompt changes (which invalidates provider-side prompt caches).

tool_ids — JSON array of tool UUIDs available in this conversation. Not every conversation needs every tool. The client or preset selects which tools are available.

summary — A rolling summary of the conversation, updated periodically. Used for context window management when the conversation is too long to send in full.

2. messages

The canonical conversation content. These are what get sent to the LLM to rebuild context.

CREATE TABLE messages (

    id                      UUID PRIMARY KEY,

    conversation_id         UUID NOT NULL,                   -- FK → conversations



    -- Role

    role                    ENUM('system', 'user', 'assistant', 'tool') NOT NULL,



    -- Content

    content                 TEXT,                            -- the text (nullable for tool-call-only assistant msgs)

    content_format          ENUM('text', 'markdown') DEFAULT 'text',



    -- Tool linkage (for role = 'tool')

    tool_call_id            UUID,                            -- which call this result belongs to

    tool_name               VARCHAR(128),                    -- denormalized for querying

    tool_status             ENUM('success', 'error', 'timeout'),



    -- Model that generated this message (null for user/tool/system)

    model_slug              VARCHAR(255),



    -- Ordering

    sequence_number         INT NOT NULL,



    -- Timestamps

    created_at              TIMESTAMP NOT NULL,



    INDEX idx_conversation_seq (conversation_id, sequence_number),

    INDEX idx_tool_call (tool_call_id)

);

Field Notes

role — Four roles: system (system prompt), user (human input), assistant (LLM response), tool (tool execution result).

content — Nullable. An assistant message that only contains tool calls has content = NULL. A tool result message has content set to the tool's output.

tool_call_id — Links a tool result message back to the specific tool call in the preceding assistant message. See Conversation Flow for the tool call loop.

model_slug — Set on assistant messages only. Records which model actually generated this response (may differ from default_model_slug on the conversation).

Message Sequence Example

A tool-use cycle produces this message sequence:

seq 1: role='user',      content='What is the weather in Paris?'

seq 2: role='assistant', content=NULL,  model_slug='gpt-4o'          (tool call only)

seq 3: role='tool',      content='{"temp":22}', tool_call_id=<id>, tool_name='web_search', tool_status='success'

seq 4: role='assistant', content='The weather in Paris is 22°C.', model_slug='gpt-4o'

Messages 2 and 3 are the tool-use round trip. Message 4 is the final answer. All four are sent to the LLM when rebuilding context.

3. trace_events

The execution trace. Captures every step between a user message and the final assistant response. For UI display and debugging — never sent to the LLM.

CREATE TABLE trace_events (

    id                      UUID PRIMARY KEY,

    conversation_id         UUID NOT NULL,                   -- FK → conversations



    -- Which messages this trace connects

    parent_user_message_id  UUID NOT NULL,                   -- the user message that triggered this cycle

    resulting_message_id    UUID,                            -- the assistant message this trace produced (nullable if in-flight)



    -- Event type

    event_type              ENUM(

                                'thinking',

                                'tool_call_request',

                                'tool_call_result',

                                'llm_request',

                                'error',

                                'retry',

                                'model_switch',

                                'context_truncation',

                                'guardrail_block'

                            ) NOT NULL,



    -- Ordering within this question→answer cycle

    sequence_number         INT NOT NULL,



    -- Content (polymorphic by event_type)

    content                 JSON,



    -- UI hints

    is_expandable           BOOLEAN DEFAULT true,



    -- Timing

    started_at              TIMESTAMP NOT NULL,

    completed_at            TIMESTAMP,



    INDEX idx_parent (conversation_id, parent_user_message_id, sequence_number),

    INDEX idx_resulting (resulting_message_id)

);

Event Types

Type	Description	Content Shape

`thinking`	LLM internal reasoning (when provider supports it)	`{"text": "Let me think about..."}`
`tool_call_request`	LLM requested a tool call	`{"call_id": "uuid", "name": "web_search", "arguments": {"q": "..."}}`
`tool_call_result`	Tool returned a result	`{"call_id": "uuid", "status": "success", "result_preview": "...", "result_full": "..."}`
`llm_request`	Raw API call to provider	`{"provider": "openai", "model": "gpt-4o", "input_tokens": 1200, "output_tokens": 300, "thinking_tokens": 0, "cached_tokens": 100, "latency_ms": 850}`
`error`	Something failed	`{"code": "rate_limit", "message": "429 Too Many Requests", "provider": "openai"}`
`retry`	Retrying a failed call	`{"attempt": 2, "max": 3, "delay_ms": 1000, "reason": "rate_limit"}`
`model_switch`	Model changed mid-flow (fallback)	`{"from": "gpt-4o", "to": "gpt-4o-mini", "reason": "fallback"}`
`context_truncation`	Conversation too long, got trimmed	`{"removed_messages": 12, "strategy": "sliding_window"}`
`guardrail_block`	Safety filter triggered	`{"rule": "no_medical_advice", "action": "refused"}`

UI Rendering

Each trace event maps to a collapsed card in the chat interface:

┌─────────────────────────────────────┐

│ 💭 Thinking          [▸ expand]     │  ← thinking event

│ Let me search for current weather   │  ← expanded content

│ data for Paris...                   │

└─────────────────────────────────────┘



┌─────────────────────────────────────┐

│ 🔧 Tool: web_search   [▸ expand]   │  ← tool_call_request

│ Query: "Paris weather today"        │

│                                     │

│ ┌─────────────────────────────────┐ │

│ │ Result: {"temp": 22, ...}       │ │  ← tool_call_result (expanded)

│ └─────────────────────────────────┘ │

└─────────────────────────────────────┘



┌─────────────────────────────────────┐

│ ⚡ LLM call          [▸ expand]    │  ← llm_request

│ Model: gpt-4o · 800→200 tokens     │

│ Latency: 850ms · Cost: $0.0032     │

└─────────────────────────────────────┘

Field Notes

parent_user_message_id — Groups all trace events for a single question→answer cycle. The UI fetches traces by this field to render the timeline between a user message and its assistant response.

resulting_message_id — Links the trace to the assistant message it produced. Nullable because during streaming, the trace starts before the message is finalized.

content.result_full — For tool results, result_preview is shown in the collapsed card; result_full is shown when expanded. Avoids loading large tool outputs unless the user wants to see them.

4. api_calls

One row per LLM API request. Audit trail for debugging, performance monitoring, and billing source.

CREATE TABLE api_calls (

    id                      UUID PRIMARY KEY,

    conversation_id         UUID NOT NULL,                   -- FK → conversations



    -- Link to the assistant message this call produced

    -- (null if the call failed before producing a message)

    message_id              UUID,                            -- FK → messages



    -- Provider details

    provider                ENUM('openai', 'anthropic', 'google') NOT NULL,

    model_slug              VARCHAR(255) NOT NULL,



    -- Token counts

    input_tokens            INT NOT NULL DEFAULT 0,

    output_tokens           INT NOT NULL DEFAULT 0,

    thinking_tokens         INT NOT NULL DEFAULT 0,          -- extended thinking (Anthropic, o1, etc.)

    cached_tokens           INT NOT NULL DEFAULT 0,          -- prompt cache hits



    -- Pre-computed cost (from cost engine at call time)

    cost_credits            INT NOT NULL DEFAULT 0,          -- platform credits

    cost_cents_usd          INT NOT NULL DEFAULT 0,          -- real USD cents (×10000 for precision)



    -- Status

    status                  ENUM('success', 'error', 'timeout', 'cancelled') NOT NULL,

    error_code              VARCHAR(64),

    error_message           TEXT,



    -- Performance

    latency_ms              INT,

    time_to_first_token_ms  INT,                             -- TTFT for streaming



    -- Provider request/response (encrypted at rest, for debugging)

    request_payload_encrypted   BLOB,

    response_payload_encrypted  BLOB,



    -- Timing

    created_at              TIMESTAMP NOT NULL,



    INDEX idx_conversation (conversation_id, created_at),

    INDEX idx_model (model_slug, created_at),

    INDEX idx_status (status, created_at),

    INDEX idx_message (message_id)

);

Field Notes

One row per API call, not per turn. A tool-use cycle produces 2+ API calls (one for the tool call, one for the final answer). Each gets its own row with its own token counts and cost.

message_id — Nullable. If the call fails (error, timeout), no message is produced. If it succeeds, this links to the assistant message.

thinking_tokens — Separate from output tokens. Providers that support extended thinking (Anthropic, OpenAI o1/o3) report thinking tokens separately. They have their own cost rate.

cached_tokens — Subset of input_tokens that hit the provider's prompt cache. Charged at a reduced rate.

cost_cents_usd — Real cost in USD cents × 10000 (i.e., $0.0032 = 32000). This precision avoids floating-point issues. See Cost & Billing.

request_payload_encrypted — The full request sent to the provider, encrypted at rest. For debugging failed calls. Not stored long-term by default (configurable retention).

5. cost_ledger

Financial record. One row per api_call, with cost broken down by category. Used for billing queries, auditing, and quota enforcement.

CREATE TABLE cost_ledger (

    id                      UUID PRIMARY KEY,

    api_call_id             UUID NOT NULL,                   -- FK → api_calls

    conversation_id         UUID NOT NULL,                   -- FK → conversations (denormalized for queries)

    user_id                 INT NOT NULL,                    -- FK → core.auth users (denormalized)

    project_id              UUID NOT NULL,                   -- FK → core.console projects (denormalized)



    -- What was consumed

    model_slug              VARCHAR(255) NOT NULL,

    input_tokens            INT NOT NULL,

    output_tokens           INT NOT NULL,

    thinking_tokens         INT NOT NULL DEFAULT 0,

    cached_tokens           INT NOT NULL DEFAULT 0,



    -- Platform credit costs

    input_cost_credits      INT NOT NULL,

    output_cost_credits     INT NOT NULL,

    thinking_cost_credits   INT NOT NULL,

    cached_cost_credits     INT NOT NULL,

    total_cost_credits      INT NOT NULL,



    -- Real USD costs (for auditing)

    input_cost_cents_usd    INT NOT NULL,                   -- USD cents × 10000

    output_cost_cents_usd   INT NOT NULL,

    thinking_cost_cents_usd INT NOT NULL,

    cached_cost_cents_usd   INT NOT NULL,

    total_cost_cents_usd    INT NOT NULL,



    -- For daily/monthly aggregation

    billing_date            DATE NOT NULL,



    -- Timing

    created_at              TIMESTAMP NOT NULL,



    INDEX idx_user_billing (user_id, billing_date),

    INDEX idx_project_billing (project_id, billing_date),

    INDEX idx_conversation (conversation_id, created_at),

    INDEX idx_api_call (api_call_id),

    INDEX idx_billing_date (billing_date)

);

Field Notes

Denormalized fields — user_id, project_id, conversation_id are copied from related tables so that billing queries don't need JOINs. SELECT SUM(total_cost_cents_usd) FROM cost_ledger WHERE user_id = ? AND billing_date = ? is a single-table scan.

Cost categories — Four categories, each with credit and USD columns: input, output, thinking, cached. Cached tokens are a subset of input tokens at a reduced rate.

total_cost_credits = input_cost_credits + output_cost_credits + thinking_cost_credits + cached_cost_credits

total_cost_cents_usd = input_cost_cents_usd + output_cost_cents_usd + thinking_cost_cents_usd + cached_cost_cents_usd

billing_date — The date the call was made (UTC). Used for daily/monthly aggregation. Not a timestamp — a DATE, so GROUP BY billing_date works cleanly.

No cumulative columns. Unlike the old turn_costs design, there are no cumulative denormalized totals. Cumulative costs are computed on read with window functions (SUM() OVER(ORDER BY created_at)) or periodic snapshots. This avoids fragile chain recalculation on delete.

Example Row

{

  "api_call_id": "call-uuid-001",

  "model_slug": "gpt-4o",

  "input_tokens": 1200,

  "output_tokens": 350,

  "thinking_tokens": 0,

  "cached_tokens": 800,

  "input_cost_credits": 400,

  "output_cost_credits": 875,

  "thinking_cost_credits": 0,

  "cached_cost_credits": 4,

  "total_cost_credits": 1279,

  "input_cost_cents_usd": 240,

  "output_cost_cents_usd": 525,

  "thinking_cost_cents_usd": 0,

  "cached_cost_cents_usd": 2,

  "total_cost_cents_usd": 767,

  "billing_date": "2026-07-09"

}

USD Cost Calculation

Each cost_cents_usd field = (tokens × provider_rate_per_token × 10000), stored as an integer.

Example for GPT-4o ($2.50/M input, $10/M output, $1.25/M cached):

input_cost_cents_usd  = (1200 - 800) × 0.0000025 × 10000 = 100  → $0.0100

output_cost_cents_usd = 350 × 0.00001 × 10000               = 3500 → $0.3500

cached_cost_cents_usd = 800 × 0.00000125 × 10000            = 100  → $0.0100

total_cost_cents_usd  = 3700                                  → $0.3700

See Cost & Billing for full pricing tables and quota integration.

Supporting Tables

These tables support the core five above. They handle media attachments, system prompt discovery, developer skills, and user-facing presets.

6. media

Files attached to messages. Uploaded via core.storage, processed via core.hypermedia.

CREATE TABLE media (

    id                  UUID PRIMARY KEY,

    conversation_id     UUID NOT NULL,              -- FK → conversations

    message_id          UUID NOT NULL,              -- FK → messages

    storage_object_id   UUID NOT NULL,              -- FK → core.storage objects

    original_filename   VARCHAR(255) NOT NULL,

    mime_type           VARCHAR(100) NOT NULL,

    size_bytes          BIGINT NOT NULL,

    status              ENUM('uploading', 'processing', 'ready', 'error') DEFAULT 'uploading',

    compressed_url      VARCHAR(500),              -- URL of the compressed/processed file

    error_message       TEXT,

    created_at          TIMESTAMP NOT NULL,

    INDEX idx_message (message_id),

    INDEX idx_conversation (conversation_id)

);

Media Flow

User uploads file → core.storage (get storage_object_id)

Create media record (status = 'uploading')

Send to core.hypermedia for compression/conversion

On completion → update record (status = 'ready', compressed_url)

Original is preserved — never delete the original from storage. Compressed version is a derivative.

On error → status = 'error', error_message set

Blocking: User cannot send a message until all attached media are status = 'ready'. UI shows processing indicator on each media item. Send button disabled while media is processing.

In the LLM prompt: Media is sent to the provider as image content (base64 or URL, depending on provider). The adapter handles encoding.

7. system_prompts

System prompts are stored as files on the filesystem. This table indexes them for discovery and metadata. The actual content is read from the file at runtime.

CREATE TABLE system_prompts (

    id              UUID PRIMARY KEY,

    slug            VARCHAR(100) UNIQUE NOT NULL,   -- "medical-assistant"

    name            VARCHAR(255) NOT NULL,          -- "Medical Assistant"

    file_path       VARCHAR(500) NOT NULL,          -- "/system-prompts/medical-assistant.md"

    description     TEXT,

    is_active       BOOLEAN DEFAULT TRUE,

    created_at      TIMESTAMP NOT NULL

);

Filesystem layout:

/system-prompts/

├── default.md

├── medical-assistant.md

├── code-reviewer.md

└── creative-writer.md

Note: Conversations store the system prompt as inline TEXT (with preset pre-prompt folded in). This table is a catalog for discovery — it's not directly referenced by conversations via FK.

8. skills

Developer-facing configurations that combine a system prompt with model settings. Used by the platform internally, not exposed to end users.

CREATE TABLE skills (

    id                  UUID PRIMARY KEY,

    name                VARCHAR(255) NOT NULL,

    slug                VARCHAR(100) UNIQUE NOT NULL,

    system_prompt_id    UUID NOT NULL,              -- FK → system_prompts

    default_model_slug  VARCHAR(255) NOT NULL,

    default_temperature DECIMAL(3, 2) DEFAULT 0.7,

    default_max_tokens  INT DEFAULT 4096,

    description         TEXT,

    is_active           BOOLEAN DEFAULT TRUE,

    created_at          TIMESTAMP NOT NULL

);

9. presets

User-facing conversation templates. Like Google Gemini Gems or Perplexity Spaces. When a user selects a preset, the conversation is preconfigured with the preset's settings.

CREATE TABLE presets (

    id              UUID PRIMARY KEY,

    name            VARCHAR(255) NOT NULL,          -- "Clinical Reasoning"

    slug            VARCHAR(100) UNIQUE NOT NULL,   -- "clinical-reasoning"

    avatar          VARCHAR(50) NOT NULL,           -- enum or predefined set identifier

    description     TEXT,

    pre_prompt      TEXT NOT NULL,                  -- folded into system prompt at conversation creation

    prompt_template TEXT,                           -- prefills the textarea

    default_model_slug VARCHAR(255) NOT NULL,       -- which model to use

    tool_ids        JSON,                           -- tools available when using this preset

    is_active       BOOLEAN DEFAULT TRUE,

    created_at      TIMESTAMP NOT NULL

);

When a user selects a preset:

New conversation created with preset_id and default_model_slug from preset

Preset's pre_prompt is folded into conversations.system_prompt (appended after base system prompt)

Textarea prefilled with prompt_template — user can edit or send as-is

Pre-prompt is NOT a separate message — it's part of the system prompt

Presets vs Skills:

Skills — developer-facing. System prompts with model config. For the platform.

Presets — user-facing. Avatars, descriptions, pre-prompts, prompt templates. For end users.

Different audiences, different tables, different UI.

10. tools (registry)

Server-side tools available for LLM tool use. Registered per project.

CREATE TABLE tools (

    id                  UUID PRIMARY KEY,

    project_id          UUID NOT NULL,              -- FK → core.console projects

    name                VARCHAR(128) NOT NULL,      -- "web_search"

    description         TEXT,                       -- shown to LLM

    parameters_schema   JSON,                       -- JSON Schema for arguments

    handler_type        ENUM('http', 'function', 'plugin') NOT NULL,

    handler_config      JSON,                       -- URL for http, code ref for function, etc.

    is_active           BOOLEAN DEFAULT TRUE,

    created_at          TIMESTAMP NOT NULL,

    UNIQUE(project_id, name)

);

Tools are scoped to conversations via conversations.tool_ids (JSON array). Not every conversation needs every tool.

Table Relationship Diagram

conversations

    │

    ├── messages               (what was said)

    ├── trace_events           (how it was produced)

    ├── api_calls              (one per LLM request)

    │     └── cost_ledger      (one per api_call)

    └── media                  (file attachments per message)



system_prompts                 (prompt catalog, no FK from conversations)

skills                         (developer configs, FK → system_prompts)

presets                        (user templates, referenced by conversations.preset_id)

tools                          (tool registry, referenced by conversations.tool_ids)

Provider Model — How messages are translated to provider formats

Conversation Flow — How these tables are used during a request

Cost & Billing — Pricing, credit rates, quota enforcement

Core Docs

Database Schemas

Database Schemas

Table Overview

Core Tables

Supporting Tables

1. conversations

Field Notes

2. messages

Field Notes

Message Sequence Example

3. trace_events

Event Types

UI Rendering

Field Notes

4. api_calls

Field Notes

5. cost_ledger

Field Notes

Example Row

USD Cost Calculation

Supporting Tables

6. media

Media Flow

7. system_prompts

8. skills

9. presets

10. tools (registry)

Table Relationship Diagram

Related