> ## Documentation Index
> Fetch the complete documentation index at: https://docs.shannon.run/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Complete PostgreSQL database schema reference for Shannon

## Overview

Shannon uses PostgreSQL for persistent storage. The database is organized into two schemas:

* `auth` - Authentication, multi-tenancy, and security
* `public` - Core application tables (tasks, agents, sessions)

**Total Tables**: 25 tables
**Extensions**: uuid-ossp, pg\_trgm, btree\_gin, pgcrypto

## Entity Relationship Diagram

```
┌─────────────────┐
│  auth.tenants   │
└────────┬────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐        ┌──────────────────┐
│   auth.users    │◄───────│  auth.api_keys   │
└────────┬────────┘   N:1  └──────────────────┘
         │
         │ 1:N
         ▼
┌─────────────────┐        ┌──────────────────┐
│   public.users  │◄───────│   sessions       │
└────────┬────────┘   1:N  └──────────────────┘
         │
         │ 1:N
         ▼
┌──────────────────────────┐
│   task_executions        │
│  (workflow_id = PK)      │
└──────────┬───────────────┘
           │
           │ 1:N
           ▼
┌──────────────────────────┐        ┌──────────────────┐
│   agent_executions       │◄───────│  tool_executions │
└──────────┬───────────────┘   1:N  └──────────────────┘
           │
           │ 1:N
           ▼
┌──────────────────────────┐
│     tool_calls           │
└──────────────────────────┘

┌──────────────────────────┐        ┌──────────────────┐
│   event_logs             │        │  token_usage     │
│  (workflow_id)           │        └──────────────────┘
└──────────────────────────┘

┌──────────────────────────┐        ┌───────────────────┐
│  usage_daily_aggregates  │        │  learning_cases   │
└──────────────────────────┘        └───────────────────┘
```

## Schema: `auth` (Authentication & Multi-Tenancy)

### auth.tenants

**Purpose**: Multi-tenant organization management

| Column                 | Type         | Constraints                     | Description                               |
| ---------------------- | ------------ | ------------------------------- | ----------------------------------------- |
| id                     | UUID         | PK, DEFAULT gen\_random\_uuid() | Tenant identifier                         |
| name                   | VARCHAR(255) | NOT NULL                        | Organization name                         |
| slug                   | VARCHAR(100) | UNIQUE, NOT NULL                | URL-safe identifier                       |
| plan                   | VARCHAR(50)  | DEFAULT 'free'                  | Subscription plan (free, pro, enterprise) |
| token\_limit           | INTEGER      | DEFAULT 10000                   | Monthly token allowance                   |
| monthly\_token\_usage  | INTEGER      | DEFAULT 0                       | Current month usage                       |
| rate\_limit\_per\_hour | INTEGER      | DEFAULT 1000                    | API rate limit                            |
| is\_active             | BOOLEAN      | DEFAULT true                    | Tenant status                             |
| created\_at            | TIMESTAMP    | DEFAULT NOW()                   | Creation time                             |
| updated\_at            | TIMESTAMP    | DEFAULT NOW()                   | Last update time                          |
| metadata               | JSONB        | DEFAULT '{}'                    | Additional tenant data                    |

**Indexes**:

* Primary key on `slug` (UNIQUE)

**Example**:

```sql theme={null}
-- Get all active tenants
SELECT id, name, plan, token_limit
FROM auth.tenants
WHERE is_active = true;

-- Check token usage
SELECT name, monthly_token_usage, token_limit,
       (monthly_token_usage::FLOAT / token_limit * 100) as usage_percentage
FROM auth.tenants
WHERE monthly_token_usage > token_limit * 0.8;
```

### auth.users

**Purpose**: User authentication and profile management

| Column              | Type         | Constraints                     | Description               |
| ------------------- | ------------ | ------------------------------- | ------------------------- |
| id                  | UUID         | PK, DEFAULT gen\_random\_uuid() | User identifier           |
| email               | VARCHAR(255) | UNIQUE, NOT NULL                | User email                |
| username            | VARCHAR(100) | UNIQUE, NOT NULL                | Username                  |
| password\_hash      | VARCHAR(255) | NOT NULL                        | Bcrypt password hash      |
| full\_name          | VARCHAR(255) |                                 | Full name                 |
| tenant\_id          | UUID         | FK → auth.tenants, NOT NULL     | Organization              |
| role                | VARCHAR(50)  | DEFAULT 'user'                  | Role (user, admin, owner) |
| is\_active          | BOOLEAN      | DEFAULT true                    | Account status            |
| is\_verified        | BOOLEAN      | DEFAULT false                   | Email verification        |
| email\_verified\_at | TIMESTAMP    |                                 | Verification timestamp    |
| created\_at         | TIMESTAMP    | DEFAULT NOW()                   | Account creation          |
| updated\_at         | TIMESTAMP    | DEFAULT NOW()                   | Last update               |
| last\_login         | TIMESTAMP    |                                 | Last login time           |
| metadata            | JSONB        | DEFAULT '{}'                    | Additional user data      |

**Indexes**:

* `idx_users_email` ON (email)
* `idx_users_tenant_id` ON (tenant\_id)

**Example**:

```sql theme={null}
-- Get all users in a tenant
SELECT u.email, u.username, u.role, u.last_login
FROM auth.users u
WHERE u.tenant_id = '...'
  AND u.is_active = true
ORDER BY u.last_login DESC NULLS LAST;

-- Find inactive users
SELECT email, created_at, last_login
FROM auth.users
WHERE last_login < NOW() - INTERVAL '90 days'
   OR last_login IS NULL;
```

### auth.api\_keys

**Purpose**: API key management for programmatic access

| Column                 | Type         | Constraints                                                           | Description                 |
| ---------------------- | ------------ | --------------------------------------------------------------------- | --------------------------- |
| id                     | UUID         | PK, DEFAULT gen\_random\_uuid()                                       | Key identifier              |
| key\_hash              | VARCHAR(255) | UNIQUE, NOT NULL                                                      | SHA256 hash of key          |
| key\_prefix            | VARCHAR(20)  | NOT NULL                                                              | First 8 chars (for display) |
| user\_id               | UUID         | FK → auth.users                                                       | Key owner                   |
| tenant\_id             | UUID         | FK → auth.tenants, NOT NULL                                           | Organization                |
| name                   | VARCHAR(100) | NOT NULL                                                              | Key name/description        |
| description            | TEXT         |                                                                       | Detailed description        |
| scopes                 | TEXT\[]      | DEFAULT ARRAY\['workflows:read', 'workflows:write', 'agents:execute'] | Permissions array           |
| rate\_limit\_per\_hour | INTEGER      | DEFAULT 1000                                                          | Key-specific rate limit     |
| last\_used             | TIMESTAMP    |                                                                       | Last usage timestamp        |
| expires\_at            | TIMESTAMP    |                                                                       | Expiration time             |
| is\_active             | BOOLEAN      | DEFAULT true                                                          | Key status                  |
| created\_at            | TIMESTAMP    | DEFAULT NOW()                                                         | Creation time               |
| metadata               | JSONB        | DEFAULT '{}'                                                          | Additional key data         |

**Indexes**:

* `idx_api_keys_tenant_id` ON (tenant\_id)
* `idx_api_keys_key_prefix` ON (key\_prefix)

**Default Scopes**:

* `workflows:read`: Allow reading workflow/task status and results
* `workflows:write`: Allow submitting and managing workflows/tasks
* `agents:execute`: Allow agent execution

**Example**:

```sql theme={null}
-- List active API keys
SELECT key_prefix, name, last_used, expires_at
FROM auth.api_keys
WHERE is_active = true
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY last_used DESC NULLS LAST;

-- Find unused keys
SELECT key_prefix, name, created_at, last_used
FROM auth.api_keys
WHERE last_used IS NULL
  AND created_at < NOW() - INTERVAL '30 days';

-- Check expiring keys
SELECT key_prefix, name, expires_at
FROM auth.api_keys
WHERE expires_at BETWEEN NOW() AND NOW() + INTERVAL '7 days'
  AND is_active = true;
```

### auth.refresh\_tokens

**Purpose**: JWT refresh token storage and revocation

| Column      | Type         | Constraints                     | Description       |
| ----------- | ------------ | ------------------------------- | ----------------- |
| id          | UUID         | PK, DEFAULT gen\_random\_uuid() | Token identifier  |
| token\_hash | VARCHAR(255) | UNIQUE, NOT NULL                | SHA256 hash       |
| user\_id    | UUID         | FK → auth.users                 | Token owner       |
| tenant\_id  | UUID         | FK → auth.tenants, NOT NULL     | Organization      |
| expires\_at | TIMESTAMP    | NOT NULL                        | Expiration time   |
| revoked     | BOOLEAN      | DEFAULT false                   | Revocation status |
| revoked\_at | TIMESTAMP    |                                 | Revocation time   |
| ip\_address | INET         |                                 | Client IP         |
| user\_agent | TEXT         |                                 | Client user agent |
| created\_at | TIMESTAMP    | DEFAULT NOW()                   | Creation time     |

**Indexes**:

* `idx_refresh_tokens_user_id` ON (user\_id)
* `idx_refresh_tokens_token_hash` ON (token\_hash)

**Example**:

```sql theme={null}
-- Revoke all tokens for a user
UPDATE auth.refresh_tokens
SET revoked = true, revoked_at = NOW()
WHERE user_id = '...'
  AND revoked = false;

-- Clean up expired tokens
DELETE FROM auth.refresh_tokens
WHERE expires_at < NOW() - INTERVAL '7 days';
```

### auth.audit\_logs

**Purpose**: Security event audit trail

| Column      | Type         | Constraints                     | Description       |
| ----------- | ------------ | ------------------------------- | ----------------- |
| id          | UUID         | PK, DEFAULT gen\_random\_uuid() | Log entry ID      |
| event\_type | VARCHAR(100) | NOT NULL                        | Event type        |
| user\_id    | UUID         | FK → auth.users                 | User (nullable)   |
| tenant\_id  | UUID         | FK → auth.tenants               | Tenant (nullable) |
| ip\_address | INET         |                                 | Client IP         |
| user\_agent | TEXT         |                                 | Client user agent |
| details     | JSONB        | DEFAULT '{}'                    | Event details     |
| created\_at | TIMESTAMP    | DEFAULT NOW()                   | Event time        |

**Indexes**:

* `idx_audit_logs_user_id` ON (user\_id)
* `idx_audit_logs_tenant_id` ON (tenant\_id)
* `idx_audit_logs_event_type` ON (event\_type)
* `idx_audit_logs_created_at` ON (created\_at)

**Event Types**:

* `login` - User login
* `logout` - User logout
* `api_key_created` - API key generation
* `permission_changed` - Role/permission modification

**Example**:

```sql theme={null}
-- Recent security events
SELECT event_type, user_id, ip_address, created_at
FROM auth.audit_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC
LIMIT 100;

-- Failed login attempts
SELECT user_id, ip_address, COUNT(*) as attempts
FROM auth.audit_logs
WHERE event_type = 'login_failed'
  AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY user_id, ip_address
HAVING COUNT(*) > 5;
```

## Schema: `public` (Core Application)

### users

**Purpose**: Application user profiles (legacy, linked to auth.users)

| Column       | Type         | Constraints                      | Description        |
| ------------ | ------------ | -------------------------------- | ------------------ |
| id           | UUID         | PK, DEFAULT uuid\_generate\_v4() | User identifier    |
| external\_id | VARCHAR(255) | UNIQUE, NOT NULL                 | External system ID |
| email        | VARCHAR(255) |                                  | Email address      |
| tenant\_id   | UUID         |                                  | Tenant reference   |
| metadata     | JSONB        | DEFAULT '{}'                     | User metadata      |
| created\_at  | TIMESTAMPTZ  | DEFAULT NOW()                    | Creation time      |
| updated\_at  | TIMESTAMPTZ  | DEFAULT NOW()                    | Last update        |

**Indexes**:

* `idx_users_tenant_id` ON (tenant\_id)
* `idx_users_external_id` ON (external\_id)

**Note**: This table exists for backward compatibility. New code should use `auth.users`.

### sessions

**Purpose**: User session management and context

| Column        | Type        | Constraints                      | Description              |
| ------------- | ----------- | -------------------------------- | ------------------------ |
| id            | UUID        | PK, DEFAULT uuid\_generate\_v4() | Session identifier       |
| user\_id      | UUID        | FK → users                       | Session owner            |
| tenant\_id    | UUID        |                                  | Tenant reference         |
| context       | JSONB       | DEFAULT '{}'                     | Session context data     |
| token\_budget | INTEGER     | DEFAULT 10000                    | Token allocation         |
| tokens\_used  | INTEGER     | DEFAULT 0                        | Tokens consumed          |
| created\_at   | TIMESTAMPTZ | DEFAULT NOW()                    | Session start            |
| updated\_at   | TIMESTAMPTZ | DEFAULT NOW()                    | Last activity            |
| expires\_at   | TIMESTAMPTZ |                                  | Expiration time          |
| deleted\_at   | TIMESTAMPTZ | DEFAULT NULL                     | Soft delete timestamp    |
| deleted\_by   | UUID        | DEFAULT NULL                     | User who deleted session |

**Indexes**:

* `idx_sessions_user_id` ON (user\_id)
* `idx_sessions_tenant_id` ON (tenant\_id)
* `idx_sessions_expires_at` ON (expires\_at)
* `idx_sessions_external_id` ON ((context->>'external\_id')) WHERE context->>'external\_id' IS NOT NULL
* `idx_sessions_user_external_id` UNIQUE ON (user\_id, (context->>'external\_id')) WHERE context->>'external\_id' IS NOT NULL AND deleted\_at IS NULL
* `idx_sessions_not_deleted` ON (id) WHERE deleted\_at IS NULL
* `idx_sessions_deleted_at` ON (deleted\_at) WHERE deleted\_at IS NOT NULL

**Notes**:

* External IDs (non-UUID session IDs) are stored in `context->>'external_id'` to enable dual-ID lookups
* Soft delete support via `deleted_at` column - sessions with non-NULL `deleted_at` are considered deleted

**Example**:

```sql theme={null}
-- Active sessions
SELECT id, user_id, tokens_used, token_budget, created_at
FROM sessions
WHERE expires_at > NOW()
  OR expires_at IS NULL
ORDER BY created_at DESC;

-- Session token usage
SELECT
    COUNT(*) as session_count,
    AVG(tokens_used) as avg_tokens,
    SUM(tokens_used) as total_tokens
FROM sessions
WHERE created_at > NOW() - INTERVAL '24 hours';
```

### task\_executions

**Purpose**: Task/workflow execution history and metrics

| Column             | Type          | Constraints                      | Description                                |
| ------------------ | ------------- | -------------------------------- | ------------------------------------------ |
| id                 | UUID          | PK, DEFAULT uuid\_generate\_v4() | Task identifier                            |
| workflow\_id       | VARCHAR(255)  | UNIQUE, NOT NULL                 | Temporal workflow ID                       |
| user\_id           | UUID          | FK → users                       | Task creator                               |
| tenant\_id         | UUID          |                                  | Tenant reference                           |
| session\_id        | VARCHAR(255)  |                                  | Session identifier                         |
| query              | TEXT          | NOT NULL                         | Task query/prompt                          |
| mode               | VARCHAR(50)   |                                  | Execution mode (SIMPLE, STANDARD, COMPLEX) |
| status             | VARCHAR(50)   | NOT NULL                         | Task status                                |
| started\_at        | TIMESTAMPTZ   | NOT NULL, DEFAULT NOW()          | Start time                                 |
| completed\_at      | TIMESTAMPTZ   |                                  | Completion time                            |
| result             | TEXT          |                                  | Final result                               |
| response           | JSONB         | DEFAULT '{}'                     | Structured response                        |
| error\_message     | TEXT          |                                  | Error details                              |
| total\_tokens      | INTEGER       | DEFAULT 0                        | Total tokens used                          |
| prompt\_tokens     | INTEGER       | DEFAULT 0                        | Input tokens                               |
| completion\_tokens | INTEGER       | DEFAULT 0                        | Output tokens                              |
| total\_cost\_usd   | DECIMAL(10,6) | DEFAULT 0                        | Total cost                                 |
| duration\_ms       | INTEGER       |                                  | Execution time (ms)                        |
| agents\_used       | INTEGER       | DEFAULT 0                        | Number of agents                           |
| tools\_invoked     | INTEGER       | DEFAULT 0                        | Number of tool calls                       |
| cache\_hits        | INTEGER       | DEFAULT 0                        | Cache hit count                            |
| complexity\_score  | DECIMAL(3,2)  |                                  | Complexity (0.0-1.0)                       |
| metadata           | JSONB         |                                  | Additional metadata                        |
| created\_at        | TIMESTAMPTZ   | DEFAULT NOW()                    | Record creation                            |

**Indexes**:

* `idx_task_user_session` ON (user\_id, session\_id)
* `idx_task_created_at` ON (created\_at DESC)
* `idx_task_status` ON (status)
* `idx_task_workflow_id` ON (workflow\_id)
* `idx_task_executions_tenant_id` ON (tenant\_id)
* `idx_task_executions_session_id` ON (session\_id)

**Status Values**:

* `RUNNING` - Task in progress
* `COMPLETED` - Task finished successfully
* `FAILED` - Task failed with error
* `CANCELLED` - Task cancelled by user

**Example**:

```sql theme={null}
-- Recent tasks for user
SELECT workflow_id, query, status, duration_ms, total_cost_usd
FROM task_executions
WHERE user_id = '...'
ORDER BY started_at DESC
LIMIT 20;

-- Failed tasks analysis
SELECT
    DATE(started_at) as date,
    COUNT(*) as failed_count,
    AVG(duration_ms) as avg_duration
FROM task_executions
WHERE status = 'FAILED'
  AND started_at > NOW() - INTERVAL '7 days'
GROUP BY DATE(started_at)
ORDER BY date DESC;

-- Cost analysis
SELECT
    user_id,
    COUNT(*) as task_count,
    SUM(total_cost_usd) as total_cost,
    AVG(total_cost_usd) as avg_cost,
    SUM(total_tokens) as total_tokens
FROM task_executions
WHERE started_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_cost DESC;
```

### agent\_executions

**Purpose**: Individual agent execution details within tasks

| Column         | Type         | Constraints                      | Description          |
| -------------- | ------------ | -------------------------------- | -------------------- |
| id             | UUID         | PK, DEFAULT uuid\_generate\_v4() | Execution ID         |
| workflow\_id   | VARCHAR(255) |                                  | Temporal workflow ID |
| task\_id       | VARCHAR(255) |                                  | Task identifier      |
| agent\_id      | VARCHAR(255) | NOT NULL                         | Agent identifier     |
| input          | TEXT         | NOT NULL                         | Agent input          |
| output         | TEXT         |                                  | Agent output         |
| state          | VARCHAR(50)  |                                  | FSM state            |
| tokens\_used   | INTEGER      | DEFAULT 0                        | Tokens consumed      |
| model\_used    | VARCHAR(100) |                                  | LLM model            |
| duration\_ms   | INTEGER      |                                  | Execution time       |
| error\_message | TEXT         |                                  | Error details        |
| metadata       | JSONB        | DEFAULT '{}'                     | Additional metadata  |
| strategy       | VARCHAR(100) |                                  | Execution strategy   |
| created\_at    | TIMESTAMPTZ  | DEFAULT NOW()                    | Start time           |
| updated\_at    | TIMESTAMPTZ  | DEFAULT NOW()                    | Last update          |

**Indexes**:

* `idx_agent_executions_workflow_id` ON (workflow\_id)
* `idx_agent_executions_created_at` ON (created\_at DESC)
* `idx_agent_executions_state` ON (state)

**State Values**:

* `IDLE` - Agent idle
* `ANALYZING` - Analyzing input
* `PLANNING` - Planning execution
* `RETRIEVING` - Retrieving information
* `EXECUTING` - Executing task
* `VALIDATING` - Validating results
* `SYNTHESIZING` - Synthesizing response
* `COMPLETED` - Execution complete
* `FAILED` - Execution failed

**Example**:

```sql theme={null}
-- Agent execution chain for a task
SELECT agent_id, state, duration_ms, tokens_used
FROM agent_executions
WHERE workflow_id = '...'
ORDER BY created_at;

-- Agent performance metrics
SELECT
    agent_id,
    COUNT(*) as execution_count,
    AVG(duration_ms) as avg_duration,
    AVG(tokens_used) as avg_tokens
FROM agent_executions
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY agent_id
ORDER BY execution_count DESC;
```

### tool\_executions

**Purpose**: Tool invocation history and performance

| Column               | Type         | Constraints                      | Description            |
| -------------------- | ------------ | -------------------------------- | ---------------------- |
| id                   | UUID         | PK, DEFAULT uuid\_generate\_v4() | Execution ID           |
| agent\_execution\_id | UUID         |                                  | Parent agent execution |
| workflow\_id         | VARCHAR(255) |                                  | Temporal workflow ID   |
| agent\_id            | VARCHAR(255) |                                  | Agent identifier       |
| tool\_name           | VARCHAR(255) | NOT NULL                         | Tool identifier        |
| input\_params        | JSONB        |                                  | Input parameters       |
| output               | TEXT         |                                  | Tool output            |
| success              | BOOLEAN      | DEFAULT true                     | Success status         |
| error                | TEXT         |                                  | Error details          |
| duration\_ms         | INTEGER      |                                  | Execution time         |
| tokens\_consumed     | INTEGER      | DEFAULT 0                        | Tokens used            |
| metadata             | JSONB        | DEFAULT '{}'                     | Additional metadata    |
| created\_at          | TIMESTAMPTZ  | DEFAULT NOW()                    | Creation time          |

**Indexes**:

* `idx_tool_executions_workflow_id` ON (workflow\_id)
* `idx_tool_executions_tool_name` ON (tool\_name)
* `idx_tool_executions_created_at` ON (created\_at DESC)
* `idx_tool_executions_success` ON (success)

**Example**:

```sql theme={null}
-- Tool usage statistics
SELECT
    tool_name,
    COUNT(*) as invocation_count,
    COUNT(CASE WHEN success THEN 1 END) as successful,
    AVG(duration_ms) as avg_duration
FROM tool_executions
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY tool_name
ORDER BY invocation_count DESC;

-- Failed tool executions
SELECT tool_name, error, created_at
FROM tool_executions
WHERE success = false
  AND created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

-- Tool performance
SELECT
    tool_name,
    AVG(duration_ms) as avg_ms,
    MAX(duration_ms) as max_ms,
    STDDEV(duration_ms) as stddev_ms
FROM tool_executions
WHERE success = true
GROUP BY tool_name
ORDER BY avg_ms DESC;
```

### event\_logs

**Purpose**: Streaming event storage for audit and replay

| Column       | Type         | Constraints                      | Description               |
| ------------ | ------------ | -------------------------------- | ------------------------- |
| id           | UUID         | PK, DEFAULT uuid\_generate\_v4() | Event ID                  |
| workflow\_id | VARCHAR(255) | NOT NULL                         | Workflow identifier       |
| task\_id     | UUID         |                                  | Task reference (nullable) |
| type         | VARCHAR(100) | NOT NULL                         | Event type                |
| agent\_id    | VARCHAR(255) |                                  | Agent identifier          |
| message      | TEXT         |                                  | Event message             |
| payload      | JSONB        | DEFAULT '{}'                     | Event payload             |
| timestamp    | TIMESTAMPTZ  | NOT NULL, DEFAULT NOW()          | Event time                |
| seq          | BIGINT       |                                  | Sequence number           |
| stream\_id   | VARCHAR(64)  |                                  | Redis stream ID           |
| created\_at  | TIMESTAMPTZ  | NOT NULL, DEFAULT NOW()          | Record creation           |

**Indexes**:

* `idx_event_logs_workflow_id` ON (workflow\_id)
* `idx_event_logs_task_id` ON (task\_id)
* `idx_event_logs_type` ON (type)
* `idx_event_logs_ts` ON (timestamp DESC)
* `idx_event_logs_seq` ON (workflow\_id, seq)
* `idx_event_logs_workflow_ts` ON (workflow\_id, timestamp DESC)
* `uq_event_logs_wf_type_seq` UNIQUE ON (workflow\_id, type, seq) WHERE seq IS NOT NULL

**Event Types**:

* `task.started` - Task execution started
* `task.completed` - Task execution completed
* `agent.thinking` - Agent processing
* `tool.called` - Tool invocation

**Example**:

```sql theme={null}
-- Get all events for a task
SELECT type, agent_id, message, timestamp
FROM event_logs
WHERE workflow_id = '...'
ORDER BY timestamp;

-- Event frequency analysis
SELECT
    type,
    COUNT(*) as event_count,
    DATE_TRUNC('hour', timestamp) as hour
FROM event_logs
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY type, hour
ORDER BY hour DESC, event_count DESC;
```

### token\_usage

**Purpose**: Detailed token usage tracking per task

| Column             | Type          | Constraints                      | Description   |
| ------------------ | ------------- | -------------------------------- | ------------- |
| id                 | UUID          | PK, DEFAULT uuid\_generate\_v4() | Record ID     |
| user\_id           | UUID          | FK → users                       | User          |
| task\_id           | UUID          | FK → task\_executions            | Task          |
| provider           | VARCHAR(50)   | NOT NULL                         | LLM provider  |
| model              | VARCHAR(255)  | NOT NULL                         | Model name    |
| prompt\_tokens     | INTEGER       | NOT NULL                         | Input tokens  |
| completion\_tokens | INTEGER       | NOT NULL                         | Output tokens |
| total\_tokens      | INTEGER       | NOT NULL                         | Total tokens  |
| cost\_usd          | DECIMAL(10,6) | NOT NULL                         | Cost in USD   |
| created\_at        | TIMESTAMPTZ   | DEFAULT NOW()                    | Record time   |

**Indexes**:

* `idx_token_usage_user_id` ON (user\_id)
* `idx_token_usage_created_at` ON (created\_at DESC)
* `idx_token_usage_provider_model` ON (provider, model)
* `idx_token_usage_task_id` ON (task\_id)

**Example**:

```sql theme={null}
-- Token usage by provider
SELECT
    provider,
    model,
    COUNT(*) as call_count,
    SUM(total_tokens) as total_tokens,
    SUM(cost_usd) as total_cost
FROM token_usage
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY provider, model
ORDER BY total_cost DESC;

-- Daily token trend
SELECT
    DATE(created_at) as date,
    SUM(total_tokens) as tokens,
    SUM(cost_usd) as cost
FROM token_usage
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date;
```

### usage\_daily\_aggregates

**Purpose**: Pre-computed daily usage statistics

| Column             | Type          | Constraints                      | Description             |
| ------------------ | ------------- | -------------------------------- | ----------------------- |
| id                 | UUID          | PK, DEFAULT uuid\_generate\_v4() | Record ID               |
| user\_id           | UUID          | FK → users                       | User                    |
| date               | DATE          | NOT NULL                         | Aggregation date        |
| total\_tasks       | INTEGER       | DEFAULT 0                        | Total task count        |
| successful\_tasks  | INTEGER       | DEFAULT 0                        | Successful count        |
| failed\_tasks      | INTEGER       | DEFAULT 0                        | Failed count            |
| total\_tokens      | INTEGER       | DEFAULT 0                        | Total tokens            |
| total\_cost\_usd   | DECIMAL(10,6) | DEFAULT 0                        | Total cost              |
| model\_usage       | JSONB         |                                  | Model distribution      |
| tools\_invoked     | INTEGER       | DEFAULT 0                        | Tool invocation count   |
| tool\_distribution | JSONB         |                                  | Tool usage distribution |
| avg\_duration\_ms  | INTEGER       |                                  | Average task duration   |
| cache\_hit\_rate   | DECIMAL(3,2)  |                                  | Cache hit percentage    |
| created\_at        | TIMESTAMPTZ   | DEFAULT NOW()                    | Record time             |

**Indexes**:

* `idx_usage_daily_user_date` ON (user\_id, date)
* `idx_usage_daily_date` ON (date DESC)

**Unique Constraint**: (user\_id, date)

**Example**:

```sql theme={null}
-- User usage summary
SELECT date, total_tasks, successful_tasks, total_cost_usd
FROM usage_daily_aggregates
WHERE user_id = '...'
  AND date > CURRENT_DATE - INTERVAL '30 days'
ORDER BY date DESC;

-- Aggregate metrics
SELECT
    SUM(total_tasks) as total_tasks,
    SUM(total_cost_usd) as total_cost,
    AVG(cache_hit_rate) as avg_cache_rate
FROM usage_daily_aggregates
WHERE date > CURRENT_DATE - INTERVAL '7 days';
```

## Additional Tables

### tool\_calls

**Purpose**: Legacy tool call tracking (use tool\_executions instead)

### prompts

**Purpose**: Prompt versioning and A/B testing

### learning\_cases

**Purpose**: Reinforcement learning case storage

### session\_archives

**Purpose**: Long-term session snapshots from Redis

### audit\_logs (public)

**Purpose**: Application audit trail (separate from auth.audit\_logs)

## Database Functions

### update\_updated\_at\_column()

**Purpose**: Automatically update `updated_at` on row changes

**Usage**: Attached as trigger to `users` and `sessions` tables

```sql theme={null}
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
```

### update\_daily\_aggregate(user\_id, date)

**Purpose**: Update or create daily usage aggregate for a user

**Usage**: Called automatically via trigger on task completion

```sql theme={null}
-- Manual call
SELECT update_daily_aggregate(
    '00000000-0000-0000-0000-000000000002',
    '2025-10-22'
);
```

### trigger\_update\_daily\_aggregate()

**Purpose**: Trigger function to update aggregates on task status changes

**Usage**: Automatically fires on task\_executions INSERT/UPDATE

## Sample Queries

### Task Analytics

```sql theme={null}
-- Task success rate by mode
SELECT
    mode,
    COUNT(*) as total,
    COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) as completed,
    ROUND(100.0 * COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) / COUNT(*), 2) as success_rate
FROM task_executions
WHERE started_at > NOW() - INTERVAL '7 days'
GROUP BY mode;

-- Average execution time by complexity
SELECT
    CASE
        WHEN complexity_score < 0.3 THEN 'Low'
        WHEN complexity_score < 0.7 THEN 'Medium'
        ELSE 'High'
    END as complexity,
    COUNT(*) as task_count,
    AVG(duration_ms) as avg_ms,
    AVG(total_cost_usd) as avg_cost
FROM task_executions
WHERE complexity_score IS NOT NULL
GROUP BY complexity;
```

### Cost Analysis

```sql theme={null}
-- Top cost users
SELECT
    u.email,
    COUNT(t.id) as task_count,
    SUM(t.total_cost_usd) as total_cost,
    AVG(t.total_cost_usd) as avg_cost
FROM task_executions t
JOIN users u ON t.user_id = u.id
WHERE t.started_at > NOW() - INTERVAL '30 days'
GROUP BY u.email
ORDER BY total_cost DESC
LIMIT 10;

-- Cost breakdown by model
SELECT
    tu.provider,
    tu.model,
    COUNT(*) as usage_count,
    SUM(tu.total_tokens) as total_tokens,
    SUM(tu.cost_usd) as total_cost,
    AVG(tu.cost_usd) as avg_cost_per_call
FROM token_usage tu
WHERE tu.created_at > NOW() - INTERVAL '7 days'
GROUP BY tu.provider, tu.model
ORDER BY total_cost DESC;
```

### Performance Monitoring

```sql theme={null}
-- Slow queries (tasks > 60 seconds)
SELECT
    workflow_id,
    query,
    duration_ms,
    agents_used,
    tools_invoked
FROM task_executions
WHERE duration_ms > 60000
  AND started_at > NOW() - INTERVAL '24 hours'
ORDER BY duration_ms DESC;

-- Tool execution success rate
SELECT
    tool_name,
    COUNT(*) as total,
    COUNT(CASE WHEN success THEN 1 END) as successful,
    ROUND(100.0 * COUNT(CASE WHEN success THEN 1 END) / COUNT(*), 2) as success_rate,
    AVG(duration_ms) as avg_duration
FROM tool_executions
GROUP BY tool_name
ORDER BY total DESC;
```

## Best Practices

### 1. Indexing

**Always use indexes for**:

* Foreign key columns (user\_id, tenant\_id, task\_execution\_id, etc.)
* Frequently filtered columns (status, created\_at, expires\_at)
* JSONB queries (context->>'external\_id')
* Time-based queries (created\_at DESC)

**Current index coverage**: Excellent (all foreign keys and common queries indexed)

### 2. Partitioning

For high-volume tables, consider partitioning:

```sql theme={null}
-- Monthly partitioning for task_executions
CREATE TABLE task_executions_2025_10 PARTITION OF task_executions
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
```

### 3. Data Retention

**Recommended policies**:

* `event_logs`: 90 days (high volume, archivable)
* `task_executions`: 1 year (archive older)
* `audit_logs`: 2 years (compliance)
* `token_usage`: Aggregate to daily, archive raw data after 90 days

**Cleanup script**:

```sql theme={null}
-- Delete old events (run monthly)
DELETE FROM event_logs
WHERE created_at < NOW() - INTERVAL '90 days';

-- Archive old tasks (run annually)
INSERT INTO task_executions_archive
SELECT * FROM task_executions
WHERE started_at < NOW() - INTERVAL '1 year';

DELETE FROM task_executions
WHERE started_at < NOW() - INTERVAL '1 year';
```

### 4. Query Optimization

**Use EXPLAIN ANALYZE**:

```sql theme={null}
EXPLAIN ANALYZE
SELECT * FROM task_executions
WHERE user_id = '...'
  AND started_at > NOW() - INTERVAL '7 days';
```

**Optimize with covering indexes**:

```sql theme={null}
CREATE INDEX idx_task_user_date_status
ON task_executions(user_id, started_at, status)
INCLUDE (total_cost_usd, duration_ms);
```

### 5. Connection Pooling

**Configuration** (already configured in Shannon):

```bash theme={null}
DB_MAX_OPEN_CONNS=50
DB_MAX_IDLE_CONNS=10
```

## Maintenance

### Vacuum and Analyze

```sql theme={null}
-- Manual vacuum (run weekly)
VACUUM ANALYZE task_executions;
VACUUM ANALYZE event_logs;

-- Configure autovacuum
ALTER TABLE task_executions SET (autovacuum_vacuum_threshold = 1000);
```

### Statistics

```sql theme={null}
-- Update statistics
ANALYZE task_executions;

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
```

## Related Documentation

<CardGroup cols={2}>
  <Card title="Environment Variables" icon="gear" href="/en/deployment/environment-variables">
    Database configuration
  </Card>

  <Card title="Performance Tuning" icon="gauge" href="/en/deployment/performance-tuning">
    Query optimization
  </Card>

  <Card title="Monitoring" icon="chart-line" href="/en/deployment/monitoring">
    Database monitoring
  </Card>
</CardGroup>