auto-analyst-backend-2

Sleeping

App Files Files Community

auto-analyst-backend-2 / docs /system /database-schema.md

Arslan1997

adding free tools

a036a0c 3 months ago

preview code

Raw History Blame Contribute Delete

12.9 kB

# Auto-Analyst Database Schema Documentation

## 📋 Overview

The Auto-Analyst backend uses a relational database schema designed for scalability and data integrity. The schema supports both **SQLite** (development) and **PostgreSQL** (production) databases through SQLAlchemy ORM.

### **Database Features**
- **User Management** - Authentication and user data
- **Chat System** - Conversation sessions and message history  
- **AI Model Tracking** - Usage analytics and cost monitoring
- **Code Execution** - Code generation and execution tracking
- **Agent Templates** - Customizable AI agent configurations
- **Deep Analysis** - Multi-step analysis reports and results
- **User Feedback** - Rating and feedback system

---

## 🗄️ Database Tables

### **1. Users Table (`users`)**

**Purpose**: Core user authentication and profile management

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `user_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique user identifier |
| `username` | `STRING` | UNIQUE, NOT NULL | User's display name |
| `email` | `STRING` | UNIQUE, NOT NULL | User's email address |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Account creation timestamp |

**Relationships:**
- **One-to-Many**: `chats` (User → Chat sessions)
- **One-to-Many**: `usage_records` (User → Model usage tracking)
- **One-to-Many**: `deep_analysis_reports` (User → Analysis reports)
- **One-to-Many**: `template_preferences` (User → Agent preferences)

---

### **2. Chats Table (`chats`)**

**Purpose**: Conversation sessions and chat organization

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `chat_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique chat session identifier |
| `user_id` | `INTEGER` | FOREIGN KEY → `users.user_id`, CASCADE DELETE | Chat owner (nullable for anonymous) |
| `title` | `STRING` | DEFAULT: 'New Chat' | Human-readable chat title |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Chat creation timestamp |

**Relationships:**
- **Many-to-One**: `user` (Chat → User)
- **One-to-Many**: `messages` (Chat → Messages)
- **One-to-Many**: `usage_records` (Chat → Model usage)

---

### **3. Messages Table (`messages`)**

**Purpose**: Individual messages within chat conversations

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `message_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique message identifier |
| `chat_id` | `INTEGER` | FOREIGN KEY → `chats.chat_id`, CASCADE DELETE | Parent chat session |
| `sender` | `STRING` | NOT NULL | Message sender: 'user' or 'ai' |
| `content` | `TEXT` | NOT NULL | Message content (text/markdown) |
| `timestamp` | `DATETIME` | DEFAULT: UTC NOW | Message creation time |

**Relationships:**
- **Many-to-One**: `chat` (Message → Chat)
- **One-to-One**: `feedback` (Message → Feedback)

---

### **4. Model Usage Table (`model_usage`)**
**Purpose**: AI model usage tracking for analytics and billing
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `usage_id` | `INTEGER` | PRIMARY KEY | Unique usage record identifier |
| `user_id` | `INTEGER` | FOREIGN KEY → `users.user_id`, SET NULL | User who triggered the usage |
| `chat_id` | `INTEGER` | FOREIGN KEY → `chats.chat_id`, SET NULL | Associated chat session |
| `model_name` | `STRING(100)` | NOT NULL | AI model used (e.g., 'gpt-5-mini') |
| `provider` | `STRING(50)` | NOT NULL | Model provider ('openai', 'anthropic', etc.) |
| `prompt_tokens` | `INTEGER` | DEFAULT: 0 | Input tokens consumed |
| `completion_tokens` | `INTEGER` | DEFAULT: 0 | Output tokens generated |
| `total_tokens` | `INTEGER` | DEFAULT: 0 | Total tokens (input + output) |
| `query_size` | `INTEGER` | DEFAULT: 0 | Query size in characters |
| `response_size` | `INTEGER` | DEFAULT: 0 | Response size in characters |
| `cost` | `FLOAT` | DEFAULT: 0.0 | Cost in USD for this usage |
| `timestamp` | `DATETIME` | DEFAULT: UTC NOW | Usage timestamp |
| `is_streaming` | `BOOLEAN` | DEFAULT: FALSE | Whether response was streamed |
| `request_time_ms` | `INTEGER` | DEFAULT: 0 | Request processing time (milliseconds) |
**Relationships:**
- **Many-to-One**: `user` (Usage → User)
- **Many-to-One**: `chat` (Usage → Chat)
---
### **5. Code Executions Table (`code_executions`)**

**Purpose**: Track code generation and execution attempts

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `execution_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique execution identifier |
| `message_id` | `INTEGER` | FOREIGN KEY → `messages.message_id`, CASCADE DELETE | Associated message |
| `chat_id` | `INTEGER` | FOREIGN KEY → `chats.chat_id`, CASCADE DELETE | Parent chat session |
| `user_id` | `INTEGER` | FOREIGN KEY → `users.user_id`, SET NULL | User who triggered execution |
| `initial_code` | `TEXT` | NULLABLE | First version of generated code |
| `latest_code` | `TEXT` | NULLABLE | Most recent code version |
| `is_successful` | `BOOLEAN` | DEFAULT: FALSE | Whether execution succeeded |
| `output` | `TEXT` | NULLABLE | Execution output (including errors) |
| `model_provider` | `STRING(50)` | NULLABLE | AI model provider used |
| `model_name` | `STRING(100)` | NULLABLE | AI model name used |
| `failed_agents` | `TEXT` | NULLABLE | JSON list of failed agent names |
| `error_messages` | `TEXT` | NULLABLE | JSON map of error messages by agent |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Execution creation time |
| `updated_at` | `DATETIME` | DEFAULT: UTC NOW, ON UPDATE | Last update timestamp |

---

### **6. Message Feedback Table (`message_feedback`)**
**Purpose**: User feedback and model settings for messages
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `feedback_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique feedback identifier |
| `message_id` | `INTEGER` | FOREIGN KEY → `messages.message_id`, CASCADE DELETE | Associated message |
| `rating` | `INTEGER` | NULLABLE | Star rating (1-5 scale) |
| `model_name` | `STRING(100)` | NULLABLE | Model used for this message |
| `model_provider` | `STRING(50)` | NULLABLE | Model provider used |
| `temperature` | `FLOAT` | NULLABLE | Temperature setting used |
| `max_tokens` | `INTEGER` | NULLABLE | Max tokens setting used |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Feedback creation time |
| `updated_at` | `DATETIME` | DEFAULT: UTC NOW, ON UPDATE | Last update timestamp |
**Relationships:**
- **One-to-One**: `message` (Feedback ↔ Message)
---
### **7. Deep Analysis Reports Table (`deep_analysis_reports`)**
**Purpose**: Store comprehensive multi-agent analysis reports
| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `report_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique report identifier |
| `report_uuid` | `STRING(100)` | UNIQUE, NOT NULL | Frontend-generated UUID |
| `user_id` | `INTEGER` | FOREIGN KEY → `users.user_id`, CASCADE DELETE | Report owner |
| `goal` | `TEXT` | NOT NULL | Analysis objective/question |
| `status` | `STRING(20)` | NOT NULL, DEFAULT: 'pending' | Status: 'pending', 'running', 'completed', 'failed' |
| `start_time` | `DATETIME` | DEFAULT: UTC NOW | Analysis start time |
| `end_time` | `DATETIME` | NULLABLE | Analysis completion time |
| `duration_seconds` | `INTEGER` | NULLABLE | Total analysis duration |
| `deep_questions` | `TEXT` | NULLABLE | Generated analytical questions |
| `deep_plan` | `TEXT` | NULLABLE | Analysis execution plan |
| `summaries` | `JSON` | NULLABLE | Array of analysis summaries |
| `analysis_code` | `TEXT` | NULLABLE | Generated Python code |
| `plotly_figures` | `JSON` | NULLABLE | Array of Plotly figure data |
| `synthesis` | `JSON` | NULLABLE | Array of synthesis insights |
| `final_conclusion` | `TEXT` | NULLABLE | Final analysis conclusion |
| `html_report` | `TEXT` | NULLABLE | Complete HTML report |
| `progress_percentage` | `INTEGER` | DEFAULT: 0 | Progress percentage (0-100) |
| `total_tokens_used` | `INTEGER` | DEFAULT: 0 | Total tokens consumed |
| `estimated_cost` | `FLOAT` | DEFAULT: 0.0 | Estimated cost in USD |
| `credits_consumed` | `INTEGER` | DEFAULT: 0 | Credits deducted for analysis |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Report creation time |
| `updated_at` | `DATETIME` | DEFAULT: UTC NOW, ON UPDATE | Last update timestamp |
**Relationships:**
- **Many-to-One**: `user` (Report → User)
---
### **8. Agent Templates Table (`agent_templates`)**

**Purpose**: Store predefined AI agent configurations

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `template_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique template identifier |
| `template_name` | `STRING(100)` | UNIQUE, NOT NULL | Internal template name |
| `display_name` | `STRING(200)` | NULLABLE | User-friendly display name |
| `description` | `TEXT` | NOT NULL | Template description |
| `prompt_template` | `TEXT` | NOT NULL | Agent behavior instructions |
| `icon_url` | `STRING(500)` | NULLABLE | Template icon URL |
| `category` | `STRING(50)` | NULLABLE | Template category |
| `is_premium_only` | `BOOLEAN` | DEFAULT: FALSE | Requires premium subscription |
| `variant_type` | `STRING(20)` | DEFAULT: 'individual' | 'planner', 'individual', or 'both' |
| `is_active` | `BOOLEAN` | DEFAULT: TRUE | Template is active/available |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Template creation time |

**Relationships:**
- **One-to-Many**: `user_preferences` (Template → User preferences)

---

### **9. User Template Preferences Table (`user_template_preferences`)**

**Purpose**: Track user preferences and usage for agent templates

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `preference_id` | `INTEGER` | PRIMARY KEY, AUTO INCREMENT | Unique preference identifier |
| `user_id` | `INTEGER` | FOREIGN KEY → `users.user_id`, CASCADE DELETE | User who owns preference |
| `template_id` | `INTEGER` | FOREIGN KEY → `agent_templates.template_id`, CASCADE DELETE | Associated template |
| `is_enabled` | `BOOLEAN` | DEFAULT: TRUE | Whether user has template enabled |
| `usage_count` | `INTEGER` | DEFAULT: 0 | Number of times user used template |
| `last_used_at` | `DATETIME` | NULLABLE | Last time user used template |
| `created_at` | `DATETIME` | DEFAULT: UTC NOW | Preference creation time |

**Relationships:**
- **Many-to-One**: `user` (Preference → User)
- **Many-to-One**: `template` (Preference → Template)

**Constraints:**
- **Unique**: `(user_id, template_id)` - One preference per user per template

---

## 🔗 Entity Relationship Diagram

```
Users (1) ──────────── (Many) Chats
  │                           │
  │                           ├── (Many) Messages
  │                           │      │
  │                           │      └── (1) MessageFeedback
  │                           │
  │                           └── (Many) CodeExecutions
  │
  ├── (Many) ModelUsage
  │
  ├── (Many) DeepAnalysisReports
  │
  └── (Many) UserTemplatePreferences
               │
               └── (Many) AgentTemplates
```

---

## 📊 Database Performance

### **Optimized Indexes**

```sql
-- High-performance queries
CREATE INDEX idx_messages_chat_timestamp ON messages(chat_id, timestamp DESC);
CREATE INDEX idx_model_usage_user_time ON model_usage(user_id, timestamp DESC);
CREATE INDEX idx_model_usage_model_time ON model_usage(model_name, timestamp DESC);
CREATE INDEX idx_reports_user_time ON deep_analysis_reports(user_id, created_at DESC);
```

### **Cascade Deletion Rules**

| Parent → Child | Rule | Description |
|----------------|------|-------------|
| `users` → `chats` | CASCADE | Delete all user chats when user deleted |
| `chats` → `messages` | CASCADE | Delete all chat messages when chat deleted |
| `messages` → `feedback` | CASCADE | Delete feedback when message deleted |
| `users` → `model_usage` | SET NULL | Keep usage records for analytics |

---

## 🛡️ Security & Maintenance

### **Data Protection**
- User data isolated by `user_id`
- Sensitive fields require encryption in production
- Automatic cleanup of anonymous data after 90 days

### **Regular Maintenance**
```sql
-- Clean old anonymous chats
DELETE FROM chats WHERE user_id IS NULL AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- Update statistics for query optimization
ANALYZE users, chats, messages, model_usage;
```

---

This schema supports the full Auto-Analyst application with optimized performance, data integrity, and scalability for both development and production environments. 

	# Auto-Analyst Database Schema Documentation

	## 📋 Overview

	The Auto-Analyst backend uses a relational database schema designed for scalability and data integrity. The schema supports both SQLite (development) and PostgreSQL (production) databases through SQLAlchemy ORM.

	### Database Features
	- User Management - Authentication and user data
	- Chat System - Conversation sessions and message history
	- AI Model Tracking - Usage analytics and cost monitoring
	- Code Execution - Code generation and execution tracking
	- Agent Templates - Customizable AI agent configurations
	- Deep Analysis - Multi-step analysis reports and results
	- User Feedback - Rating and feedback system

	---

	## 🗄️ Database Tables

	### 1. Users Table (`users`)

	Purpose: Core user authentication and profile management

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `user_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique user identifier \|
	\| `username` \| `STRING` \| UNIQUE, NOT NULL \| User's display name \|
	\| `email` \| `STRING` \| UNIQUE, NOT NULL \| User's email address \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Account creation timestamp \|

	Relationships:
	- One-to-Many: `chats` (User → Chat sessions)
	- One-to-Many: `usage_records` (User → Model usage tracking)
	- One-to-Many: `deep_analysis_reports` (User → Analysis reports)
	- One-to-Many: `template_preferences` (User → Agent preferences)

	---

	### 2. Chats Table (`chats`)

	Purpose: Conversation sessions and chat organization

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `chat_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique chat session identifier \|
	\| `user_id` \| `INTEGER` \| FOREIGN KEY → `users.user_id`, CASCADE DELETE \| Chat owner (nullable for anonymous) \|
	\| `title` \| `STRING` \| DEFAULT: 'New Chat' \| Human-readable chat title \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Chat creation timestamp \|

	Relationships:
	- Many-to-One: `user` (Chat → User)
	- One-to-Many: `messages` (Chat → Messages)
	- One-to-Many: `usage_records` (Chat → Model usage)

	---

	### 3. Messages Table (`messages`)

	Purpose: Individual messages within chat conversations

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `message_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique message identifier \|
	\| `chat_id` \| `INTEGER` \| FOREIGN KEY → `chats.chat_id`, CASCADE DELETE \| Parent chat session \|
	\| `sender` \| `STRING` \| NOT NULL \| Message sender: 'user' or 'ai' \|
	\| `content` \| `TEXT` \| NOT NULL \| Message content (text/markdown) \|
	\| `timestamp` \| `DATETIME` \| DEFAULT: UTC NOW \| Message creation time \|

	Relationships:
	- Many-to-One: `chat` (Message → Chat)
	- One-to-One: `feedback` (Message → Feedback)

	---

	### 4. Model Usage Table (`model_usage`)

	Purpose: AI model usage tracking for analytics and billing

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `usage_id` \| `INTEGER` \| PRIMARY KEY \| Unique usage record identifier \|
	\| `user_id` \| `INTEGER` \| FOREIGN KEY → `users.user_id`, SET NULL \| User who triggered the usage \|
	\| `chat_id` \| `INTEGER` \| FOREIGN KEY → `chats.chat_id`, SET NULL \| Associated chat session \|
	\| `model_name` \| `STRING(100)` \| NOT NULL \| AI model used (e.g., 'gpt-5-mini') \|
	\| `provider` \| `STRING(50)` \| NOT NULL \| Model provider ('openai', 'anthropic', etc.) \|
	\| `prompt_tokens` \| `INTEGER` \| DEFAULT: 0 \| Input tokens consumed \|
	\| `completion_tokens` \| `INTEGER` \| DEFAULT: 0 \| Output tokens generated \|
	\| `total_tokens` \| `INTEGER` \| DEFAULT: 0 \| Total tokens (input + output) \|
	\| `query_size` \| `INTEGER` \| DEFAULT: 0 \| Query size in characters \|
	\| `response_size` \| `INTEGER` \| DEFAULT: 0 \| Response size in characters \|
	\| `cost` \| `FLOAT` \| DEFAULT: 0.0 \| Cost in USD for this usage \|
	\| `timestamp` \| `DATETIME` \| DEFAULT: UTC NOW \| Usage timestamp \|
	\| `is_streaming` \| `BOOLEAN` \| DEFAULT: FALSE \| Whether response was streamed \|
	\| `request_time_ms` \| `INTEGER` \| DEFAULT: 0 \| Request processing time (milliseconds) \|

	Relationships:
	- Many-to-One: `user` (Usage → User)
	- Many-to-One: `chat` (Usage → Chat)

	---

	### 5. Code Executions Table (`code_executions`)

	Purpose: Track code generation and execution attempts

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `execution_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique execution identifier \|
	\| `message_id` \| `INTEGER` \| FOREIGN KEY → `messages.message_id`, CASCADE DELETE \| Associated message \|
	\| `chat_id` \| `INTEGER` \| FOREIGN KEY → `chats.chat_id`, CASCADE DELETE \| Parent chat session \|
	\| `user_id` \| `INTEGER` \| FOREIGN KEY → `users.user_id`, SET NULL \| User who triggered execution \|
	\| `initial_code` \| `TEXT` \| NULLABLE \| First version of generated code \|
	\| `latest_code` \| `TEXT` \| NULLABLE \| Most recent code version \|
	\| `is_successful` \| `BOOLEAN` \| DEFAULT: FALSE \| Whether execution succeeded \|
	\| `output` \| `TEXT` \| NULLABLE \| Execution output (including errors) \|
	\| `model_provider` \| `STRING(50)` \| NULLABLE \| AI model provider used \|
	\| `model_name` \| `STRING(100)` \| NULLABLE \| AI model name used \|
	\| `failed_agents` \| `TEXT` \| NULLABLE \| JSON list of failed agent names \|
	\| `error_messages` \| `TEXT` \| NULLABLE \| JSON map of error messages by agent \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Execution creation time \|
	\| `updated_at` \| `DATETIME` \| DEFAULT: UTC NOW, ON UPDATE \| Last update timestamp \|

	---

	### 6. Message Feedback Table (`message_feedback`)

	Purpose: User feedback and model settings for messages

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `feedback_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique feedback identifier \|
	\| `message_id` \| `INTEGER` \| FOREIGN KEY → `messages.message_id`, CASCADE DELETE \| Associated message \|
	\| `rating` \| `INTEGER` \| NULLABLE \| Star rating (1-5 scale) \|
	\| `model_name` \| `STRING(100)` \| NULLABLE \| Model used for this message \|
	\| `model_provider` \| `STRING(50)` \| NULLABLE \| Model provider used \|
	\| `temperature` \| `FLOAT` \| NULLABLE \| Temperature setting used \|
	\| `max_tokens` \| `INTEGER` \| NULLABLE \| Max tokens setting used \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Feedback creation time \|
	\| `updated_at` \| `DATETIME` \| DEFAULT: UTC NOW, ON UPDATE \| Last update timestamp \|

	Relationships:
	- One-to-One: `message` (Feedback ↔ Message)

	---

	### 7. Deep Analysis Reports Table (`deep_analysis_reports`)

	Purpose: Store comprehensive multi-agent analysis reports

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `report_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique report identifier \|
	\| `report_uuid` \| `STRING(100)` \| UNIQUE, NOT NULL \| Frontend-generated UUID \|
	\| `user_id` \| `INTEGER` \| FOREIGN KEY → `users.user_id`, CASCADE DELETE \| Report owner \|
	\| `goal` \| `TEXT` \| NOT NULL \| Analysis objective/question \|
	\| `status` \| `STRING(20)` \| NOT NULL, DEFAULT: 'pending' \| Status: 'pending', 'running', 'completed', 'failed' \|
	\| `start_time` \| `DATETIME` \| DEFAULT: UTC NOW \| Analysis start time \|
	\| `end_time` \| `DATETIME` \| NULLABLE \| Analysis completion time \|
	\| `duration_seconds` \| `INTEGER` \| NULLABLE \| Total analysis duration \|
	\| `deep_questions` \| `TEXT` \| NULLABLE \| Generated analytical questions \|
	\| `deep_plan` \| `TEXT` \| NULLABLE \| Analysis execution plan \|
	\| `summaries` \| `JSON` \| NULLABLE \| Array of analysis summaries \|
	\| `analysis_code` \| `TEXT` \| NULLABLE \| Generated Python code \|
	\| `plotly_figures` \| `JSON` \| NULLABLE \| Array of Plotly figure data \|
	\| `synthesis` \| `JSON` \| NULLABLE \| Array of synthesis insights \|
	\| `final_conclusion` \| `TEXT` \| NULLABLE \| Final analysis conclusion \|
	\| `html_report` \| `TEXT` \| NULLABLE \| Complete HTML report \|
	\| `progress_percentage` \| `INTEGER` \| DEFAULT: 0 \| Progress percentage (0-100) \|
	\| `total_tokens_used` \| `INTEGER` \| DEFAULT: 0 \| Total tokens consumed \|
	\| `estimated_cost` \| `FLOAT` \| DEFAULT: 0.0 \| Estimated cost in USD \|
	\| `credits_consumed` \| `INTEGER` \| DEFAULT: 0 \| Credits deducted for analysis \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Report creation time \|
	\| `updated_at` \| `DATETIME` \| DEFAULT: UTC NOW, ON UPDATE \| Last update timestamp \|

	Relationships:
	- Many-to-One: `user` (Report → User)

	---

	### 8. Agent Templates Table (`agent_templates`)

	Purpose: Store predefined AI agent configurations

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `template_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique template identifier \|
	\| `template_name` \| `STRING(100)` \| UNIQUE, NOT NULL \| Internal template name \|
	\| `display_name` \| `STRING(200)` \| NULLABLE \| User-friendly display name \|
	\| `description` \| `TEXT` \| NOT NULL \| Template description \|
	\| `prompt_template` \| `TEXT` \| NOT NULL \| Agent behavior instructions \|
	\| `icon_url` \| `STRING(500)` \| NULLABLE \| Template icon URL \|
	\| `category` \| `STRING(50)` \| NULLABLE \| Template category \|
	\| `is_premium_only` \| `BOOLEAN` \| DEFAULT: FALSE \| Requires premium subscription \|
	\| `variant_type` \| `STRING(20)` \| DEFAULT: 'individual' \| 'planner', 'individual', or 'both' \|
	\| `is_active` \| `BOOLEAN` \| DEFAULT: TRUE \| Template is active/available \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Template creation time \|

	Relationships:
	- One-to-Many: `user_preferences` (Template → User preferences)

	---

	### 9. User Template Preferences Table (`user_template_preferences`)

	Purpose: Track user preferences and usage for agent templates

	\| Column \| Type \| Constraints \| Description \|
	\|--------\|------\|-------------\|-------------\|
	\| `preference_id` \| `INTEGER` \| PRIMARY KEY, AUTO INCREMENT \| Unique preference identifier \|
	\| `user_id` \| `INTEGER` \| FOREIGN KEY → `users.user_id`, CASCADE DELETE \| User who owns preference \|
	\| `template_id` \| `INTEGER` \| FOREIGN KEY → `agent_templates.template_id`, CASCADE DELETE \| Associated template \|
	\| `is_enabled` \| `BOOLEAN` \| DEFAULT: TRUE \| Whether user has template enabled \|
	\| `usage_count` \| `INTEGER` \| DEFAULT: 0 \| Number of times user used template \|
	\| `last_used_at` \| `DATETIME` \| NULLABLE \| Last time user used template \|
	\| `created_at` \| `DATETIME` \| DEFAULT: UTC NOW \| Preference creation time \|

	Relationships:
	- Many-to-One: `user` (Preference → User)
	- Many-to-One: `template` (Preference → Template)

	Constraints:
	- Unique: `(user_id, template_id)` - One preference per user per template

	---

	## 🔗 Entity Relationship Diagram

	```
	Users (1) ──────────── (Many) Chats
	│ │
	│ ├── (Many) Messages
	│ │ │
	│ │ └── (1) MessageFeedback
	│ │
	│ └── (Many) CodeExecutions
	│
	├── (Many) ModelUsage
	│
	├── (Many) DeepAnalysisReports
	│
	└── (Many) UserTemplatePreferences
	│
	└── (Many) AgentTemplates
	```

	---

	## 📊 Database Performance

	### Optimized Indexes

	```sql
	-- High-performance queries
	CREATE INDEX idx_messages_chat_timestamp ON messages(chat_id, timestamp DESC);
	CREATE INDEX idx_model_usage_user_time ON model_usage(user_id, timestamp DESC);
	CREATE INDEX idx_model_usage_model_time ON model_usage(model_name, timestamp DESC);
	CREATE INDEX idx_reports_user_time ON deep_analysis_reports(user_id, created_at DESC);
	```

	### Cascade Deletion Rules

	\| Parent → Child \| Rule \| Description \|
	\|----------------\|------\|-------------\|
	\| `users` → `chats` \| CASCADE \| Delete all user chats when user deleted \|
	\| `chats` → `messages` \| CASCADE \| Delete all chat messages when chat deleted \|
	\| `messages` → `feedback` \| CASCADE \| Delete feedback when message deleted \|
	\| `users` → `model_usage` \| SET NULL \| Keep usage records for analytics \|

	---

	## 🛡️ Security & Maintenance

	### Data Protection
	- User data isolated by `user_id`
	- Sensitive fields require encryption in production
	- Automatic cleanup of anonymous data after 90 days

	### Regular Maintenance
	```sql
	-- Clean old anonymous chats
	DELETE FROM chats WHERE user_id IS NULL AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

	-- Update statistics for query optimization
	ANALYZE users, chats, messages, model_usage;
	```

	---

	This schema supports the full Auto-Analyst application with optimized performance, data integrity, and scalability for both development and production environments.