Assistant Database Schema
Copy for LLM
Copy page as Markdown for LLMs
View as Markdown
Open this page as Markdown
Open in ChatGPT
Get insights from ChatGPT
Open in Claude
Get insights from Claude

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-4o"
 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')

Assistant Database SchemaCopyCopy for LLMCopy page as Markdown for LLMsView as MarkdownOpen this page as MarkdownOpen in ChatGPTGet insights from ChatGPTOpen in ClaudeGet insights from Claude

Overview

Core Tables

1. assistants (Main Table)

2. assistant_tool_configurations

3. assistant_rules

4. assistant_reasoning

5. assistant_json_schemas

6. assistant_vector_stores

7. assistant_rule_attachments