# TheSol Chat Database Production Readiness Report

Date: 2026-04-26  
Database checked: `thesol_chat` on local XAMPP MariaDB 10.4.x  
Goal: Decide whether the existing database can support a production product, or whether schema/data changes are required.

## Final Verdict

The existing database is good enough for local development and current low-volume testing, but it should not be considered production-ready without modifications.

It has a solid base:

- All application tables are InnoDB.
- Core tables have real foreign keys for users, conversations, messages, articles, and user data.
- No orphaned messages, conversations, or user data were found.
- Core message payload/attachment JSON is valid.
- Main custom feature tables exist: embeddings, queue, chat cache, concurrency, OpenAI usage, OTP sessions, audit log, action submissions.

But for a serious product, it needs updates in four areas:

1. Performance indexes for high-traffic queries.
2. Schema hardening for custom features: OTP, queue, OpenAI usage, audit/action submissions.
3. Retention/cleanup strategy for logs, OTP, OpenAI usage, queue, cache, and old conversations.
4. Migration/version discipline so future updates do not silently drift from production.

## Current Database Snapshot

Largest tables:

| Table | Approx Rows | Size | Notes |
|---|---:|---:|---|
| `sb_embeddings` | 1,708 | 28.02 MB | Custom embeddings storage, biggest table |
| `sb_messages` | 10,471+ | 4.02 MB | Main chat messages |
| `sb_users_data` | 12,807 | 3.03 MB | User metadata; important lookup table |
| `sb_users` | 1,476+ | 0.39 MB | Users/leads/admin/bot |
| `sb_conversations` | 1,527 | 0.20 MB | Conversations |
| `sb_openai_usage` | 397-455 | 0.11 MB | Custom OpenAI usage tracking |

Important data health checks:

| Check | Result |
|---|---:|
| Messages without conversation | 0 |
| Messages without user | 0 |
| Conversations without user | 0 |
| User data without user | 0 |
| Duplicate phone values | 0 |
| Invalid message payload JSON | 0 |
| Invalid message attachments JSON | 0 |
| Invalid settings JSON | 2 path-style scalar values only: `admin-icon`, `login-icon` |

So the existing DB is not corrupted. The issue is readiness for product scale and custom feature reliability.

## Production-Blocking Database Changes

### DB-P0-1. Add missing index for phone/user-data lookup

Severity: Critical for WhatsApp/OTP/contact matching at scale  
Current behavior: `sb_users_data` has only `UNIQUE(user_id, slug)`.

Representative query:

```sql
SELECT * FROM sb_users_data WHERE slug='phone' AND value='919028757576' LIMIT 1;
```

Current EXPLAIN result: full table scan over `sb_users_data`.

Why this matters:

- WhatsApp webhook matching uses phone lookup.
- OTP verification uses phone/user extra data.
- Duplicate phone checks scan by slug/value.
- Current row count is only ~12k, but a product can quickly reach hundreds of thousands or millions of user_data rows.

Required migration:

```sql
ALTER TABLE sb_users_data
  ADD INDEX idx_slug_value (slug, value(191));
```

Optional if phone lookup becomes extremely important:

```sql
ALTER TABLE sb_users_data
  ADD INDEX idx_slug_value_user (slug, value(191), user_id);
```

### DB-P0-2. Add message query indexes before production volume

Severity: Critical for admin panel speed at scale  
Current indexes: `user_id`, `conversation_id` only.

Hot query patterns:

- Load all messages in a conversation ordered by ID.
- Get latest message per conversation.
- Get new messages after timestamp/ID.
- Count messages in a conversation.
- Find last non-empty message.

Required migration:

```sql
ALTER TABLE sb_messages
  ADD INDEX idx_conversation_id_id (conversation_id, id),
  ADD INDEX idx_conversation_created_id (conversation_id, creation_time, id),
  ADD INDEX idx_creation_id (creation_time, id),
  ADD INDEX idx_user_conversation (user_id, conversation_id);
```

Why this matters:

Current data is small enough that queries feel fine. At production volume, admin conversation lists and polling can become slow because several queries group/order by conversation and message ID.

### DB-P0-3. Add conversation filtering indexes

Severity: High  
Current indexes: `agent_id`, `user_id`, `(user_id,status_code)`.

Hot query patterns:

- Admin conversation list by status.
- Filter by department/source/tag.
- Routing by agent/department/status.
- Get latest conversation for a user with active status.

Required migration:

```sql
ALTER TABLE sb_conversations
  ADD INDEX idx_status_id (status_code, id),
  ADD INDEX idx_department_status_id (department, status_code, id),
  ADD INDEX idx_source_status_id (source, status_code, id),
  ADD INDEX idx_agent_status_id (agent_id, status_code, id),
  ADD INDEX idx_user_status_id (user_id, status_code, id);
```

Note: `tags LIKE '%...%'` cannot be solved by a normal index. If tags become important, move tags to a normalized `sb_conversation_tags` table.

### DB-P0-4. Fix OpenAI usage schema for system/troubleshoot calls

Severity: High  
Current issue:

Recent PHP logs show `sb_openai_log_usage()` receives empty conversation/user IDs during `open-ai-troubleshoot` and embeddings calls.

The table allows NULL, but code currently tries numeric escaping before insert. Schema should support system-level usage explicitly.

Recommended schema update:

```sql
ALTER TABLE sb_openai_usage
  ADD COLUMN request_context VARCHAR(50) NULL AFTER user_id,
  ADD COLUMN provider_request_id VARCHAR(191) NULL AFTER request_context,
  ADD INDEX idx_context_created (request_context, created_at),
  ADD INDEX idx_model_created (model, created_at);
```

Code must also be changed so missing `conversation_id` / `user_id` are inserted as `NULL`, not empty strings.

### DB-P0-5. Add OTP indexes and secure OTP storage

Severity: High  
Current OTP table exists and works, but is not production-grade.

Live table has:

- `idx_user_conv (user_id, conversation_id)`
- `idx_phone_otp (phone_number, otp_code)`

Missing indexes from upgrade intent:

- `created_at`
- `verified`
- status/time lookup for active OTPs

Required migration:

```sql
ALTER TABLE sb_otp_sessions
  ADD INDEX idx_otp_created (created_at),
  ADD INDEX idx_otp_active_lookup (user_id, conversation_id, verified, created_at, id),
  ADD INDEX idx_otp_user_created (user_id, created_at);
```

Security upgrade:

```sql
ALTER TABLE sb_otp_sessions
  ADD COLUMN otp_hash VARCHAR(255) NULL AFTER otp_code,
  ADD COLUMN verified_at DATETIME NULL AFTER verified;
```

Then update code to store hash instead of plain `otp_code`. After migration and verification, the plain `otp_code` can be removed or retained temporarily only during transition.

## High-Priority Database Improvements

### DB-P1-1. Add queue claim/heartbeat columns

Severity: High for async OpenAI reliability  
Status: Mitigated 2026-04-26. Live DB and new-install DDL now include claim and heartbeat fields.

Applied migration:

```sql
ALTER TABLE sb_queue
  ADD COLUMN claim_id VARCHAR(64) NULL AFTER worker_id,
  ADD COLUMN heartbeat_at DATETIME NULL AFTER claimed_at,
  ADD COLUMN failed_at DATETIME NULL AFTER completed_at,
  ADD INDEX idx_claim (claim_id),
  ADD INDEX idx_status_heartbeat (status, heartbeat_at),
  ADD INDEX idx_status_attempts (status, attempts, priority, created_at);
```

Why:

- The previous worker claimed by updating a row then selecting by `worker_id`.
- A unique `claim_id` makes each claim deterministic.
- `heartbeat_at` prevents long OpenAI jobs from being wrongly reclaimed.
- `failed_at` improves cleanup and reporting.

Validation:

- Fresh-heartbeat processing job was not reclaimed.
- Stale-heartbeat processing job was reclaimed.
- Real worker failure path cleared `worker_id`, `claim_id`, and `heartbeat_at`, and set `failed_at`.

### DB-P1-2. Make concurrency slots safer

Severity: High  
Current table:

- `slot_key`
- `worker_id`
- `acquired_at`

Current risk:

The code does count-then-insert, which is not atomic. Schema can help with observability and cleanup.

Recommended migration:

```sql
ALTER TABLE sb_concurrency
  ADD COLUMN expires_at DATETIME NULL AFTER acquired_at,
  ADD INDEX idx_slot_acquired (slot_key, acquired_at),
  ADD INDEX idx_slot_expires (slot_key, expires_at);
```

Better long-term design:

Use a fixed-slot table or MySQL advisory locks instead of count-then-insert.

### DB-P1-3. Add retention indexes and policy tables

Severity: High for long-running product  
Tables needing cleanup:

- `sb_openai_usage`
- `sb_otp_sessions`
- `sb_queue`
- `sb_chat_cache`
- `sb_embeddings_cache`
- `sb_audit_log`
- public uploads/conversation exports outside DB

Recommended retention rules:

| Data | Suggested Retention |
|---|---:|
| OTP sessions | 30-90 days |
| Queue completed/failed jobs | 7-30 days |
| Chat cache | TTL already exists; cleanup daily |
| Embeddings cache | TTL already exists; cleanup daily |
| OpenAI usage | 12-24 months, aggregate older data monthly |
| Audit log | 12-24 months |
| Raw webhook logs | 7-30 days, masked |

Additional indexes:

```sql
ALTER TABLE sb_audit_log
  ADD INDEX idx_user_created (user_id, created_at),
  ADD INDEX idx_action_created (action, created_at);
```

### DB-P1-4. Add foreign keys for custom tables where appropriate

Severity: Medium-to-High  
Core tables already have foreign keys. Custom tables mostly do not.

Recommended for `sb_action_submissions`:

```sql
ALTER TABLE sb_action_submissions
  ADD INDEX idx_action_created (action_id, created_at);
```

Foreign keys are optional if you want to keep historical action submissions after users/conversations are deleted. If you want strict cleanup:

```sql
ALTER TABLE sb_action_submissions
  ADD CONSTRAINT fk_action_conversation
    FOREIGN KEY (conversation_id) REFERENCES sb_conversations(id) ON DELETE CASCADE,
  ADD CONSTRAINT fk_action_user
    FOREIGN KEY (user_id) REFERENCES sb_users(id) ON DELETE CASCADE;
```

For `sb_openai_usage`, foreign keys are not recommended unless system calls use nullable IDs carefully.

For `sb_queue`, foreign keys are optional because old queue jobs may need to remain for troubleshooting after a conversation is deleted.

### DB-P1-5. Normalize collations

Severity: Medium  
Current collations are mixed:

- `utf8mb4_unicode_ci`
- `utf8mb4_general_ci`
- `utf8mb4_bin` for `sb_messages`

This is not breaking now, but mixed collations can cause join/comparison errors later.

Recommended direction:

- Keep `sb_messages.message` search behavior in mind before changing `sb_messages` collation.
- For new custom tables, standardize on `utf8mb4_unicode_ci` or, better on MySQL 8, `utf8mb4_0900_ai_ci`.
- Do not blindly convert production collations without testing message search and integrations.

## Medium-Priority Improvements

### DB-P2-1. Use larger text columns for AI-heavy fields

Current `TEXT` limit is ~64 KB. AI responses, flow payloads, queue context, webhook payloads, and settings can exceed this.

Recommended migration:

```sql
ALTER TABLE sb_messages
  MODIFY message MEDIUMTEXT NOT NULL,
  MODIFY attachments MEDIUMTEXT NULL,
  MODIFY payload MEDIUMTEXT NULL;

ALTER TABLE sb_queue
  MODIFY message MEDIUMTEXT NULL,
  MODIFY context_data MEDIUMTEXT NULL,
  MODIFY result MEDIUMTEXT NULL,
  MODIFY error MEDIUMTEXT NULL;

ALTER TABLE sb_chat_cache
  MODIFY response MEDIUMTEXT NOT NULL;

ALTER TABLE sb_audit_log
  MODIFY details MEDIUMTEXT NULL;
```

### DB-P2-2. Add a schema migrations table

Current issue:

SQL files exist in `resources/`, but the database does not appear to have a migration/version ledger.

Required table:

```sql
CREATE TABLE IF NOT EXISTS sb_schema_migrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  migration VARCHAR(191) NOT NULL UNIQUE,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checksum VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

Why:

Without this, production schema can drift silently between installs. That is already visible in OTP scripts versus live indexes.

### DB-P2-3. Add product health tables or views

For product operations, add views or admin queries for:

- Failed queue jobs.
- OpenAI usage by day/model/context.
- OTP failures and blocked attempts.
- Webhook failures by provider.
- Slow-growing tables and cleanup candidates.

This is not required for correctness, but it makes the product maintainable.

## What Does Not Need Immediate DB Change

These areas are acceptable right now:

1. Core foreign keys exist and are enforcing cleanup for users, conversations, and messages.
2. `sb_settings.name` is primary key and `version` exists.
3. `sb_embeddings` is already in MySQL with binary vector storage and basic indexes.
4. `sb_queue`, `sb_chat_cache`, and `sb_concurrency` exist.
5. `sb_openai_usage` exists and has basic date/user/conversation indexes.
6. Current row counts are small; the DB is not currently overloaded.

## Recommended Production Migration Pack

Run only after full backup and testing on a copy of production.

```sql
-- 1. Fast user extra lookups, especially phone lookup
ALTER TABLE sb_users_data
  ADD INDEX idx_slug_value (slug, value(191));

-- 2. Message hot-path indexes
ALTER TABLE sb_messages
  ADD INDEX idx_conversation_id_id (conversation_id, id),
  ADD INDEX idx_conversation_created_id (conversation_id, creation_time, id),
  ADD INDEX idx_creation_id (creation_time, id),
  ADD INDEX idx_user_conversation (user_id, conversation_id);

-- 3. Conversation filtering/routing indexes
ALTER TABLE sb_conversations
  ADD INDEX idx_status_id (status_code, id),
  ADD INDEX idx_department_status_id (department, status_code, id),
  ADD INDEX idx_source_status_id (source, status_code, id),
  ADD INDEX idx_agent_status_id (agent_id, status_code, id),
  ADD INDEX idx_user_status_id (user_id, status_code, id);

-- 4. OTP production indexes and future secure storage
ALTER TABLE sb_otp_sessions
  ADD INDEX idx_otp_created (created_at),
  ADD INDEX idx_otp_active_lookup (user_id, conversation_id, verified, created_at, id),
  ADD INDEX idx_otp_user_created (user_id, created_at),
  ADD COLUMN otp_hash VARCHAR(255) NULL AFTER otp_code,
  ADD COLUMN verified_at DATETIME NULL AFTER verified;

-- 5. OpenAI usage context/reporting support
ALTER TABLE sb_openai_usage
  ADD COLUMN request_context VARCHAR(50) NULL AFTER user_id,
  ADD COLUMN provider_request_id VARCHAR(191) NULL AFTER request_context,
  ADD INDEX idx_context_created (request_context, created_at),
  ADD INDEX idx_model_created (model, created_at);

-- 6. Queue reliability support
ALTER TABLE sb_queue
  ADD COLUMN claim_id VARCHAR(64) NULL AFTER worker_id,
  ADD COLUMN heartbeat_at DATETIME NULL AFTER claimed_at,
  ADD COLUMN failed_at DATETIME NULL AFTER completed_at,
  ADD INDEX idx_claim (claim_id),
  ADD INDEX idx_status_heartbeat (status, heartbeat_at),
  ADD INDEX idx_status_attempts (status, attempts, priority, created_at);

-- 7. Concurrency observability
ALTER TABLE sb_concurrency
  ADD COLUMN expires_at DATETIME NULL AFTER acquired_at,
  ADD INDEX idx_slot_acquired (slot_key, acquired_at),
  ADD INDEX idx_slot_expires (slot_key, expires_at);

-- 8. Audit reporting
ALTER TABLE sb_audit_log
  ADD INDEX idx_user_created (user_id, created_at),
  ADD INDEX idx_action_created (action, created_at);

-- 9. Schema migration ledger
CREATE TABLE IF NOT EXISTS sb_schema_migrations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  migration VARCHAR(191) NOT NULL UNIQUE,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  checksum VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## Required Code Changes Alongside DB Changes

Do not apply the schema changes alone and assume the product is ready. Some DB changes need code updates:

1. `sb_openai_log_usage()` must insert `NULL` for missing conversation/user IDs and populate `request_context`.
2. OTP code must write and verify `otp_hash` before plain `otp_code` is removed.
3. Queue worker must generate/use `claim_id` and update `heartbeat_at`.
4. Concurrency limiter must use a safer atomic pattern; the extra indexes alone do not make it atomic.
5. Cleanup cron must remove old OTP rows, expired caches, stale concurrency slots, and old queue rows.
6. Deployment process must record applied migrations in `sb_schema_migrations`.

## Backup and Safe Rollout Plan

1. Export full DB backup:

```powershell
& 'D:\xampp\mysql\bin\mysqldump.exe' -uroot -prootpassword123 -P3306 thesol_chat > backups\thesol_chat_before_prod_db_migration.sql
```

2. Apply migrations to a staging copy first.
3. Run smoke tests:

- Admin login.
- Save settings.
- Load conversations.
- Send message.
- WhatsApp webhook lookup by phone.
- OpenAI troubleshoot.
- OTP request and OTP verify.
- Queue worker one-job run.

4. Check slow query log after migration.
5. Apply to production during maintenance window.

## Bottom Line

Can the existing database handle everything as-is?

No, not for a production-grade product.

Can the existing database be upgraded safely?

Yes. The base is healthy: core foreign keys exist, current data is not orphaned, and custom tables already exist. The required work is mostly additive: indexes, a few nullable columns, migration tracking, retention, and matching code changes for OpenAI/OTP/queue.

Recommended decision:

Do not rebuild the database from scratch. Keep the existing database, but apply a controlled production migration pack and update the code paths that depend on the new columns.
