# Assistant Database Schema

Understanding the database structure behind Freddy assistants helps you work effectively with the API and understand how data is organized.

## Overview

Assistants use a **normalized database design** where data is split across multiple related tables. This approach provides:

- ✅ **Data Integrity**: Foreign key constraints ensure consistency
- ✅ **Flexibility**: Easy to add new features without restructuring
- ✅ **Performance**: Efficient queries with proper indexing
- ✅ **Maintainability**: Clear separation of concerns


## Core Tables

### 1. `assistants` (Main Table)

The primary table containing core assistant information.


```sql
CREATE TABLE assistants (
  id VARCHAR(255) PRIMARY KEY,           -- asst_{uuid}
  name VARCHAR(255) NOT NULL,
  description TEXT,
  instructions TEXT,                     -- System prompt
  model VARCHAR(100) NOT NULL,           -- e.g., "gpt-4"
  organization_id VARCHAR(255) NOT NULL,
  user_id VARCHAR(255),                  -- Creator
  is_public BOOLEAN DEFAULT false,
  context_strategy VARCHAR(50),          -- "auto", "manual", etc.
  max_context_tokens INTEGER,
  temperature FLOAT,
  top_p FLOAT,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  is_deleted BOOLEAN DEFAULT false,
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);
```

**Key Fields:**

- `id`: Prefixed with `asst_` for easy identification
- `instructions`: The system prompt that defines behavior
- `model`: Which AI model to use
- `organization_id`: Always required (org-level or user-level)
- `user_id`: Nullable (null = org-wide assistant)


### 2. `assistant_tool_configurations`

Stores tool configurations including system tools, MCP tools, and Streamline automations.


```sql
CREATE TABLE assistant_tool_configurations (
  id VARCHAR(255) PRIMARY KEY,           -- atc_{uuid}
  assistant_id VARCHAR(255) UNIQUE NOT NULL,
  system_tools JSON NOT NULL DEFAULT '{}',
  mcp_tools JSON,                        -- Array of MCP config IDs
  streamline_tools JSON,                 -- Array of automation IDs
  
  FOREIGN KEY (assistant_id) REFERENCES assistants(id) ON DELETE CASCADE
);
```

**Structure:**


```json
{
  "system_tools": {
    "file_search": {
      "enabled": true,
      "max_results": 10
    },
    "web_search": {
      "enabled": true,
      "safe_mode": true
    },
    "code_interpreter": {
      "enabled": false
    }
  },
  "mcp_tools": [
    "mcp_abc123",  // MCP configuration IDs
    "mcp_def456"
  ],
  "streamline_tools": [
    "sauto_xyz789"  // Streamline automation IDs
  ]
}
```

**Relationship:** One-to-one with `assistants`

### 3. `assistant_rules`

Links assistants to knowledge rules and guidelines.


```sql
CREATE TABLE assistant_rules (
  id VARCHAR(255) PRIMARY KEY,           -- arule_{uuid}
  assistant_id VARCHAR(255) NOT NULL,
  rule_id VARCHAR(255) NOT NULL,
  order_index INTEGER,                   -- Display order
  created_at TIMESTAMP NOT NULL,
  
  FOREIGN KEY (assistant_id) REFERENCES assistants(id) ON DELETE CASCADE,
  FOREIGN KEY (rule_id) REFERENCES rules(id) ON DELETE CASCADE,
  UNIQUE (assistant_id, rule_id)
);
```

**Relationship:** Many-to-many between `assistants` and `rules`

### 4. `assistant_reasoning`

Configures how the assistant thinks and reasons.


```sql
CREATE TABLE assistant_reasoning (
  id VARCHAR(255) PRIMARY KEY,           -- areason_{uuid}
  assistant_id VARCHAR(255) UNIQUE NOT NULL,
  enabled BOOLEAN DEFAULT false,
  reasoning_type VARCHAR(50),            -- "chain_of_thought", "reflection"
  max_reasoning_tokens INTEGER,
  show_reasoning BOOLEAN DEFAULT false,  -- Show to user?
  configuration JSON,                    -- Type-specific config
  
  FOREIGN KEY (assistant_id) REFERENCES assistants(id) ON DELETE CASCADE
);
```

**Configuration Example:**


```json
{
  "reasoning_type": "chain_of_thought",
  "configuration": {
    "steps": 5,
    "verbosity": "detailed",
    "self_correction": true
  }
}
```

**Relationship:** One-to-one with `assistants`

### 5. `assistant_json_schemas`

Defines structured output formats for the assistant.


```sql
CREATE TABLE assistant_json_schemas (
  id VARCHAR(255) PRIMARY KEY,           -- aschema_{uuid}
  assistant_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  schema JSON NOT NULL,                  -- JSON Schema definition
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP NOT NULL,
  
  FOREIGN KEY (assistant_id) REFERENCES assistants(id) ON DELETE CASCADE
);
```

**Schema Example:**


```json
{
  "type": "object",
  "properties": {
    "summary": {"type": "string"},
    "sentiment": {"type": "string", "enum": ["positive", "negative", "neutral"]},
    "confidence": {"type": "number", "minimum": 0, "maximum": 1}
  },
  "required": ["summary", "sentiment"]
}
```

**Relationship:** One-to-many with `assistants`

### 6. `assistant_vector_stores`

Links assistants to vector stores for semantic search.


```sql
CREATE TABLE assistant_vector_stores (
  id VARCHAR(255) PRIMARY KEY,           -- avs_{uuid}
  assistant_id VARCHAR(255) NOT NULL,
  vector_store_id VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  
  FOREIGN KEY (assistant_id) REFERENCES assistants(id) ON DELETE CASCADE,
  FOREIGN KEY (vector_store_id) REFERENCES vector_stores(id) ON DELETE CASCADE,
  UNIQUE (assistant_id, vector_store_id)
);
```

**Relationship:** Many-to-many between `assistants` and `vector_stores`

### 7. `assistant_rule_attachments`

File attachments associated with assistant rules.


```sql
CREATE TABLE assistant_rule_attachments (
  id VARCHAR(255) PRIMARY KEY,           -- aratt_{uuid}
  assistant_rule_id VARCHAR(255) NOT NULL,
  file_id VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  
  FOREIGN KEY (assistant_rule_id) REFERENCES assistant_rules(id) ON DELETE CASCADE,
  FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE
);
```

**Relationship:** Many-to-many between `assistant_rules` and `files`

## Entity Relationship Diagram


```
┌─────────────────┐
│   assistants    │ (Main table)
└────────┬────────┘
         │
         ├──────────────────────────────────────┐
         │                                      │
         │ 1:1                            1:many│
         ▼                                      ▼
┌────────────────────────┐          ┌──────────────────┐
│assistant_tool_configs  │          │ assistant_rules  │
│                        │          │                  │
│ • system_tools         │          │ • rule_id (FK)   │
│ • mcp_tools []         │          │ • order_index    │
│ • streamline_tools []  │          └──────────────────┘
└────────────────────────┘                   │
                                             │ many:many
         │ 1:1                               ▼
         ▼                            ┌──────────────┐
┌────────────────────┐                │    rules     │
│assistant_reasoning │                └──────────────┘
│                    │
│ • reasoning_type   │                ┌──────────────────────┐
│ • configuration    │                │assistant_rule_attach │
└────────────────────┘                └──────────────────────┘
                                               │
         │ 1:many                              │ many:many
         ▼                                     ▼
┌──────────────────────┐              ┌──────────────┐
│assistant_json_schemas│              │    files     │
└──────────────────────┘              └──────────────┘

         │ many:many
         ▼
┌──────────────────────┐
│assistant_vector_stores│
└──────────────────────┘
         │
         │ many:many
         ▼
┌──────────────────┐
│  vector_stores   │
└──────────────────┘
```

## MCP Tool Integration

MCP tools are stored as an array of MCP configuration IDs in `assistant_tool_configurations.mcp_tools`:


```json
{
  "mcp_tools": [
    "mcp_abc123",  // Custom MCP
    "mcp_def456",  // Personal Connector (Google Drive)
    "mcp_ghi789"   // Streamline Automation
  ]
}
```

The actual MCP configurations are stored in separate tables:


```
mcp_configurations (core config)
├── personal_connectors (OAuth integrations)
└── streamline_automations (workflow automations)
```

## Querying Assistants

### Get Assistant with All Configurations


```python
from sqlalchemy import select
from sqlalchemy.orm import selectinload

# Eager load all relationships
stmt = select(Assistant).where(
    Assistant.id == assistant_id
).options(
    selectinload(Assistant.tool_configuration),
    selectinload(Assistant.reasoning),
    selectinload(Assistant.rules),
    selectinload(Assistant.vector_stores),
    selectinload(Assistant.json_schemas)
)

assistant = await session.execute(stmt).scalar_one()
```

### Get Assistants with Active MCP Tools


```python
from sqlalchemy import select, func

# Find assistants using specific MCP
stmt = select(Assistant).join(
    AssistantToolConfiguration
).where(
    func.jsonb_array_length(
        AssistantToolConfiguration.mcp_tools
    ) > 0
)

assistants = await session.execute(stmt).scalars().all()
```

## Best Practices

### ✅ DO

- **Use foreign keys** for referential integrity
- **Soft delete** with `is_deleted` flag
- **Index frequently queried fields** (organization_id, user_id)
- **Use JSON for flexible configurations** (system_tools, mcp_tools)
- **Eager load relationships** when needed to avoid N+1 queries


### ❌ DON'T

- **Don't store large files** in JSON fields
- **Don't bypass foreign keys** with manual ID management
- **Don't use SELECT *** - specify needed columns
- **Don't forget to cascade deletes** on parent-child relationships
- **Don't mix concerns** - keep tables focused on single responsibility


## Migration Example

When adding new features, create migrations that maintain backward compatibility:


```python
# alembic/versions/xxx_add_assistant_feature.py

def upgrade():
    # Add new column with default
    op.add_column(
        'assistants',
        sa.Column('new_feature', sa.Boolean(), 
                  server_default=sa.text('false'), 
                  nullable=False)
    )
    
    # Create new related table
    op.create_table(
        'assistant_new_feature',
        sa.Column('id', sa.String(255), primary_key=True),
        sa.Column('assistant_id', sa.String(255), nullable=False),
        sa.ForeignKeyConstraint(['assistant_id'], ['assistants.id'], 
                                ondelete='CASCADE')
    )

def downgrade():
    op.drop_table('assistant_new_feature')
    op.drop_column('assistants', 'new_feature')
```

## Related Documentation

- [Assistants Overview](/docs/documentation/assistants/overview)
- [Tool Configuration](/docs/documentation/assistants/tools)