# Database Architecture Guide

**Last Updated**: 2026-06-29  
**Change Log**:
- 2026-06-29: Initial version generated after cloning the repository.

---

## 🎯 Overview

The platform uses a hybrid storage architecture:
1. **MySQL (Relational)**: Source metadata, multi-tenant users, custom agent/business configurations, transcripts, CRM lead profiles, and conversation session logs.
2. **Qdrant (Vector)**: Stores embeddings for the RAG documents, segmented by tenant business ID (`bid`).
3. **Redis (Cache)**: Transient session cache, fast embedding lookups, and API rate-limiting trackers.

---

## 📊 Relational Database Schema (MySQL)

### 1. Business Configurations & Auth
- **`businesses`**: Registers business names and primary `bid`.
- **`business_users`**: User records referencing parent business, role configurations, and passwords.
- **`business_pipeline_config`**: Configuration settings per tenant (e.g. default STT provider, languages, audio endpoints).
- **`business_agent_config`**: Stores agent prompt templates, custom assessment weights, and fallback models.

### 2. Call Logs
- **`{bid}_call_records`**: Dynamically provisioned call logs per tenant. Contains:
  - `callid` (Primary Key)
  - `phone`, `duration`, `call_starttime`, `call_endtime`
  - `status` (`pending`, `transcribed`, `done`, `failed`)
  - `transcripts` (Plain text block)
  - `speaker_segments` (JSON blob containing turns, timing, and speakers)
  - `analysis` (JSON blob containing quality checks, sentiment, BANT attributes)

### 3. CRM Lead Sync
- **`crm_leads_cache`**: Cached CRM lead contact numbers, names, and profiles.
- **`crm_lead_activities`**: Logged touchpoints synced from CRMs.
- **`sync_watermarks`**: Keeps track of last processed call ID/timestamp from source systems.

---

## 🧠 Vector Database Schema (Qdrant & Fallback)

### Qdrant Collections
- Collection naming scheme: `mcube_pca_rag_{bid}`.
- Payload includes: `chunk_id`, `source_id`, `text`, and custom metadata sections.
- Distance metric: **Cosine**.

### Relational Fallback Tables
When Qdrant is unavailable, MySQL mimics the search via table schemas:
- **`rag_documents`**: Links chunk sets to original sources.
- **`rag_chunks`**: Houses the text chunk, metadata JSON, and raw float embeddings. Uses a helper query in `rag_handler.py` to compute cosine similarity directly in SQL.
- **`rag_conversations`**: Tracks threads.
- **`rag_messages`**: Logs RAG search history and response steps.