Skip to content
/
Database Schema
Last updated

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.

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.

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:

{
  "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.

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.

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:

{
  "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.

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:

{
  "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.

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.

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:

{
  "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

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

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:

# 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')
Previous page
Next page