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