# Chatbot Database Integration — Detailed Gap Analysis & Fix Plan

**Date:** February 26, 2026  
**Application:** TheSol Chat (Support Board) — Tangerine Arts Studio  
**URLs:** Production: `tangerineartsstudio.com/chatbot/` | Local: `localhost/Chatbot/`

---

## 1. EXECUTIVE SUMMARY

The chatbot's Database Training feature is **not returning real-time data** when users ask questions like "What's tomorrow's schedule?" or "What's my last invoice?". The root cause is a combination of **8 critical issues** spanning broken configs, poor data formatting, stale embeddings, and fundamental architecture limitations.

**Current state:**
- Server has **2 database sources** with **(no tables selected)** — configs are broken/incomplete
- Local has **2 stale entries** (last sync: 2025-11-19) also with empty tables
- Existing embeddings contain **2,129 DB-sourced entries** from **older source IDs** that are no longer linked to any active config
- Embedding text quality is **extremely poor** — raw pipe-delimited values with unreplaced `{placeholders}` and serialized PHP data
- **No real-time database query mechanism exists** — the system is purely snapshot-based
- **Schedule table (`tblschedules_master`) has zero embeddings** — never trained despite being configured in the admin UI

---

## 2. ARCHITECTURE — HOW IT WORKS TODAY

```
                          ┌─────────────────────────────────────┐
                          │     Admin: Database Training UI      │
                          │  (admin.js → AJAX endpoints)         │
                          └──────────────┬──────────────────────┘
                                         │ Save/Sync
                                         ▼
                   ┌──────────────────────────────────────────────┐
                   │  sb_chatbot_database_training_save()         │
                   │  → Saves config to sb_settings table         │
                   │  → Calls sb_chatbot_database_training_process│
                   └──────────────────────┬──────────────────────┘
                                         │
                                         ▼
                   ┌──────────────────────────────────────────────┐
                   │  sb_chatbot_database_training_process()      │
                   │  → Connects to external DB (tangerine_database)
                   │  → SELECT columns FROM table LIMIT 1000     │
                   │  → Converts rows to text paragraphs         │
                   │  → Calls sb_open_ai_embeddings_generate()   │
                   └──────────────────────┬──────────────────────┘
                                         │
                                         ▼
                   ┌──────────────────────────────────────────────┐
                   │  sb_open_ai_embeddings_generate()            │
                   │  → Sends text to OpenAI text-embedding-3-small
                   │  → Stores vectors + text in JSON files      │
                   │  → Path: uploads/embeddings/embeddings-*.json│
                   └──────────────────────────────────────────────┘

                          --- QUERY TIME (User asks question) ---

                   ┌──────────────────────────────────────────────┐
                   │  sb_open_ai_message()                        │
                   │  → Entry point for ALL chatbot responses     │
                   │  → Calls sb_open_ai_embeddings_message()     │
                   └──────────────────────┬──────────────────────┘
                                         │
                                         ▼
                   ┌──────────────────────────────────────────────┐
                   │  sb_open_ai_embeddings_message()             │
                   │  → Generates embedding vector for question   │
                   │  → Loads ALL embedding JSON files            │
                   │  → Cosine similarity search (min_score=0.2)  │
                   │  → Top 10 matches → context for GPT          │
                   │  → GPT generates answer from context         │
                   └──────────────────────────────────────────────┘
```

**Critical limitation:** There is **NO path** where a user's question triggers a live SQL query against the external database. Everything depends on pre-computed embedding snapshots.

---

## 3. ALL IDENTIFIED ISSUES (8 Critical, 4 Major, 3 Minor)

### CRITICAL ISSUES

#### ISSUE #1: Broken Source Configs — No Tables, No DB Credentials
**Files:** `apps/dialogflow/functions.php` (line 5453-5520)  
**Evidence:** Database query shows stored config:
```json
[
  {"id":"db-tangerine","name":"Tangerine","database":"tangerine_database","tables":"","status":"active","last_sync":"2025-11-19 11:18"},
  {"id":"db-8a92671ab6b585f6151de5dfbe0edbad","name":"Tangerine","database":"tangerine_database","tables":"","status":"active","last_sync":"2025-11-19 11:18"}
]
```
**Problems:**
- `tables` is empty string `""` — no tables configured
- Missing `db_host`, `db_user`, `db_password`, `db_name`, `db_port` fields
- `sync_interval` is missing (defaults to 24 but can't sync without DB credentials)
- These entries appear in the admin UI as "(no tables selected)" 
- The sync function calls `sb_chatbot_database_training_process()` which immediately fails because `$config['db_host']` doesn't exist
- **Impact:** Auto-sync is completely broken. Manual sync fails silently.

**Root Cause:** The configs were likely created with an older version of the save function, or the configs were manually edited. The `sb_chatbot_database_training_save()` function correctly saves all fields, but these 2 entries were saved without them.

#### ISSUE #2: Orphaned Embeddings — Active Data from Dead Sources
**Evidence:** 
```
Embedding sources in DB:         Config sources in DB:
db-38d701f30c6540eeecdd7003...   db-tangerine (no tables)
db-1d82d8e4c7c24d28977a30...     db-8a92671ab6b585f6151d... (no tables)
db-8a92671ab6b585f6151d...
db-tangerine
```
- Source `db-38d701f30c6540eeecdd7003556aae1e` has **657 embeddings** but does NOT exist in the active config
- Source `db-1d82d8e4c7c24d28977a30e04c388bef` has **720 embeddings** but does NOT exist in the active config
- These orphaned embeddings are still being searched by the chatbot, containing stale/incorrect data
- **Impact:** Chatbot gives answers from data that may be months old and can't be refreshed

#### ISSUE #3: Catastrophically Poor Embedding Text Quality
**File:** `apps/dialogflow/functions.php` (line 5414-5430)  
**Evidence — actual stored embedding text:**
```
{id} | Choreography Workshop with Sagar Tiruwa | Choreography Workshop with Sagar Tiruwa | 
{product_category_id} | 974.58 | 0.00 | {up_to_seat} | 0 | 2025-05-09 | 2025-05-09 | 
2025-05-04 | 2025-05-10 | 50 | 1-2-3 | a:2:{i:0;s:9:"CGST|9.00";i:1;s:9:"SGST|9.00";} | ...
```

**Sub-problems:**
1. **Unreplaced template placeholders** (`{id}`, `{product_category_id}`, `{up_to_seat}`) — template references columns not in the SELECT list
2. **No column names** in the default format — just pipe-delimited raw values
3. **Serialized PHP data** polluting text: `a:2:{i:0;s:9:"CGST|9.00";...}`
4. **Integer values silently dropped** — `is_string($value)` check on line 5427 excludes numeric columns like `id`, price, seat count
5. **No semantic context** — GPT sees `974.58 | 0.00 | 0` instead of `"Price: ₹974.58, Discount: ₹0.00, Available Seats: 50"`
6. **Impact:** Cosine similarity between user question "What's the schedule for tomorrow?" and `{id} | 974.58 | 0 | 2025-05-09` is extremely low → no match → "I don't know"

#### ISSUE #4: No `tblschedules_master` Embeddings Exist
**Evidence:** Embedding source scan shows:
```
db-38d701f30c6540eeecdd7003556aae1e-tblproduct_master: 657 embeddings
db-1d82d8e4c7c24d28977a30e04c388bef-tblinvoices: 720 embeddings
db-8a92671ab6b585f6151de5dfbe0edbad-tblinvoices: 720 embeddings
db-tangerine-tblproduct_master: 32 embeddings
```
- **Zero embeddings from `tblschedules_master`, `tblleads`, `tblclients`, `tblinvoicepaymentrecords`**
- The production admin showed these tables configured, but they exist under source IDs that were likely deleted or never properly trained
- **Impact:** Schedule queries are impossible to answer from database data

#### ISSUE #5: No Real-Time Database Query Mechanism
**File:** `apps/dialogflow/functions.php` (line 1690, 3182)  
The entire pipeline is:
1. Pre-compute embeddings from DB snapshot
2. At query time, search ONLY the pre-computed embeddings
3. Send matched text as context to GPT

**There is NO code path that:**
- Detects a question needs live data
- Constructs and executes a SQL query against the external DB
- Falls back to live queries when embedding search fails

**Impact:** Even if embeddings are perfectly synced, data is always `sync_interval` hours behind.

#### ISSUE #6: Date Filter Silently Discards Past Data
**File:** `apps/dialogflow/functions.php` (line 5405-5410)
```php
if (in_array('startdate', $columns)) {
    $where_clause = " WHERE `startdate` >= CURDATE()";
} else if (in_array('show_startdate', $columns)) {
    $where_clause = " WHERE `show_startdate` >= CURDATE()";
}
```
- This hardcoded filter only gets future-dated records
- If `tblschedules_master` has a `startdate` column, records before today are excluded
- Users asking "What was last week's schedule?" get no results
- **Impact:** Historical data queries always fail

#### ISSUE #7: Broken AJAX Endpoint for Direct Processing
**File:** `include/ajax.php` (line 397-398)
```php
case 'chatbot-database-training-process':
    return sb_json_response(sb_chatbot_database_training_process($_POST['source_id']));
```
- Function signature: `sb_chatbot_database_training_process($source_id, $config)` — requires 2 args
- AJAX only passes 1 argument (`$_POST['source_id']`)
- This causes a **PHP fatal error** if this endpoint is called
- **Impact:** Manual re-processing from admin UI is broken

#### ISSUE #8: Cron Job Depends on Web Traffic
**File:** `include/functions.php` (line 1840-1871)
```php
function sb_cron_jobs() {
    // Database Training Auto-Sync
    if (defined('SB_DIALOGFLOW') && sb_chatbot_active(false, true)) {
        sb_chatbot_database_training_sync();
    }
}
```
- `sb_cron_jobs()` is only triggered when someone visits the admin panel
- If nobody visits for 8 hours, no auto-sync happens for 8 hours
- **Impact:** "Every 1 hour" sync interval is unreliable

### MAJOR ISSUES

#### ISSUE #9: Row Limit Truncation (1000 default)
**File:** `apps/dialogflow/functions.php` (line 5400)
- Each table limited to `LIMIT 1000` rows
- If `tblproduct_master` has 5000 products, 80% are invisible to the chatbot

#### ISSUE #10: Embedding Accumulation Without Cleanup
**File:** `apps/dialogflow/functions.php` (line 2943-2960)
- When sync runs, duplicate detection compares exact text matches
- If a product's price changes, OLD embedding stays (different text = not a duplicate)
- Result: Both old and new data exist, potentially confusing GPT

#### ISSUE #11: Bug in JSON Decode (line 2887)
```php
$paragraphs_or_string == $paragraphs_or_string_;  // ASSIGNMENT SHOULD BE = NOT ==
```
- Uses comparison `==` instead of assignment `=`
- The decoded JSON is never actually used — falls through to the else branch

#### ISSUE #12: `last_sync` Stored as String vs Timestamp
- Some entries store `last_sync` as `"2025-11-19 11:18"` (string)
- Sync function does `intval($config['last_sync'])` which returns `2025` for string dates
- Result: `time() - 2025 > interval * 3600` is always true → sync fires every time (if it had credentials)

### MINOR ISSUES

#### ISSUE #13: Invoice Data Has No Customer Context
- Invoice embeddings show: `490 | 0 |  | 564 |  | 490 | INV- | 1 | 2025-08-23...`
- No customer name, no product name, no human-readable invoice number
- Even if matched, GPT can't create a meaningful answer from this

#### ISSUE #14: No Per-User Data Filtering
- When user asks "What's my last invoice?", there's no mechanism to:
  - Identify the current user
  - Filter embeddings by user/customer ID
  - Query the DB for user-specific data

#### ISSUE #15: Sync Interval UI Shows "every 24h" but Config Has No Value
- Admin UI shows "every 24h" because `get_sources()` defaults to 24
- Actual stored config has no `sync_interval` field

---

## 4. DATA FLOW GAP MAP

```
User asks: "What is tomorrow's schedule?"
    │
    ├─► sb_open_ai_message() called
    │
    ├─► sb_open_ai_embeddings_message() searches ALL embedding files
    │       │
    │       ├─► Looks in 37 JSON files (2129 DB embeddings)
    │       │
    │       ├─► ❌ No tblschedules_master data exists (ISSUE #4)
    │       │
    │       ├─► tblproduct_master data exists but:
    │       │     ├─► ❌ Text is "{id} | Workshop Name | 974.58 | 2025-05-09..." (ISSUE #3)
    │       │     ├─► ❌ No column names, no "schedule" word (poor semantic match)
    │       │     ├─► ❌ Date 2026-02-27 might not exist in embeddings (ISSUE #6)
    │       │     └─► ❌ Cosine similarity likely below 0.2 threshold
    │       │
    │       └─► Returns: no matches found (score too low)
    │
    ├─► Falls through to GPT with NO context
    │
    └─► GPT says: "I'm sorry, there are no classes scheduled..."
```

---

## 5. WHAT EXISTS vs WHAT'S NEEDED

| Feature | Current State | Needed |
|---------|--------------|--------|
| DB Connection Config | ❌ Broken (missing credentials) | ✅ Complete config with all fields |
| Table Selection | ❌ Empty strings stored | ✅ Tables with columns properly saved |
| Data Text Quality | ❌ Raw pipes, unreplaced placeholders | ✅ Human-readable sentences |
| Schedule Data | ❌ Zero embeddings | ✅ Full schedule data embedded |
| Invoice Data | ❌ No customer context | ✅ Joined with customer names |
| Auto-Sync | ❌ Broken (no creds, string dates) | ✅ Reliable hourly sync |
| Sync on Data Change | ❌ Not implemented | ✅ Trigger on DB changes |
| Real-time Queries | ❌ Not implemented | ✅ Live SQL for time-sensitive queries |
| User-Specific Data | ❌ Not implemented | ✅ Filter by logged-in user |
| Stale Data Cleanup | ❌ Accumulates old data | ✅ Delete old embeddings before re-sync |
| Date-Aware Responses | ❌ Hardcoded CURDATE filter | ✅ Configurable date handling |
| Production Training | ❌ Zero working sources on prod | ✅ All tables properly trained |

---

## 6. FIX PLAN — PRIORITIZED

### Phase 1: IMMEDIATE FIXES (Fix what's broken)

#### Fix 1A: Fix Data Text Formatting in `sb_chatbot_database_training_process()`
**File:** `apps/dialogflow/functions.php` (line 5414-5430)  
**Problem:** `is_string($value)` drops numeric columns; no column names in output  
**Fix:** Replace the text generation loop:
```php
// CURRENT (broken):
if (is_string($value) && strlen($value) > 0) {
    $text_parts[] = $key . ': ' . $value;
}

// FIXED:
if ($value !== null && $value !== '' && strlen(strval($value)) > 0) {
    // Skip serialized PHP data
    if (is_string($value) && (strpos($value, 'a:') === 0 || strpos($value, 's:') === 0)) continue;
    // Skip default dates
    if ($value === '0000-00-00' || $value === '0000-00-00 00:00:00') continue;
    $text_parts[] = $key . ': ' . strval($value);
}
```

#### Fix 1B: Fix Template Placeholder Handling
**Problem:** Unreplaced `{column_name}` placeholders remain as literal text  
**Fix:** After template replacement, strip any remaining `{...}` placeholders:
```php
// After the foreach replacement loop:
$text = preg_replace('/\{[a-z_]+\}/i', '', $text);
$text = preg_replace('/\s*\|\s*\|\s*/', ' | ', $text); // consolidate empty pipes
$text = trim($text, ' |');
```

#### Fix 1C: Fix the AJAX Endpoint
**File:** `include/ajax.php` (line 397-398)  
**Fix:** Load config from saved sources:
```php
case 'chatbot-database-training-process':
    $source_id = $_POST['source_id'];
    $sources = sb_get_external_setting('database-training-sources', []);
    $config = isset($sources[$source_id]) ? $sources[$source_id] : [];
    return sb_json_response(sb_chatbot_database_training_process($source_id, $config));
```

#### Fix 1D: Fix the JSON Decode Bug
**File:** `apps/dialogflow/functions.php` (line 2887)
```php
// CURRENT (bug):
$paragraphs_or_string == $paragraphs_or_string_;
// FIX:
$paragraphs_or_string = $paragraphs_or_string_;
```

#### Fix 1E: Fix `last_sync` Inconsistency
Ensure `last_sync` is always stored as Unix timestamp (integer), not date string.

### Phase 2: DATA QUALITY IMPROVEMENTS

#### Fix 2A: Smart Text Generation with Column Names
Generate human-readable text from DB rows:
```
"Class: Choreography Workshop with Tanvi Mehra. Type: Dance. Date: February 27, 2026. 
Time: 7:00 AM - 8:30 AM. Price: ₹974.58. Available Seats: 50. Venue: Tangerine Arts Studio."
```
Instead of:
```
{id} | Choreography Workshop | 974.58 | 0.00 | {up_to_seat} | 0 | 2025-05-09 | ...
```

#### Fix 2B: Clean Old Embeddings Before Re-Sync
Before generating new embeddings, delete all existing embeddings for that source:
```php
// In sb_chatbot_database_training_process(), before generating new embeddings:
sb_open_ai_embeddings_delete([$source_id]);
```

#### Fix 2C: Remove Hardcoded Date Filter
Make the WHERE clause configurable per table instead of hardcoded:
```php
$where_clause = isset($table['where_clause']) ? $table['where_clause'] : '';
```

### Phase 3: REAL-TIME DATA LAYER (New Feature)

#### Fix 3A: Implement OpenAI Function Calling for Live DB Queries
Add "tools" to the OpenAI API call that let GPT execute parameterized SQL queries:
```php
// Register database query tool in sb_open_ai_message():
$tools[] = [
    'type' => 'function',
    'function' => [
        'name' => 'query_database',
        'description' => 'Query the CRM database for real-time data like schedules, invoices, clients',
        'parameters' => [
            'type' => 'object',
            'properties' => [
                'query_type' => ['type' => 'string', 'enum' => ['schedule', 'invoice', 'client', 'product']],
                'date' => ['type' => 'string', 'description' => 'Date in YYYY-MM-DD format'],
                'name' => ['type' => 'string', 'description' => 'Person or class name to search']
            ]
        ]
    ]
];
```

When GPT calls the tool, execute a safe parameterized query against the external DB and return results.

#### Fix 3B: Implement System Cron Job
Set up a real cron job (not dependent on web traffic):
```bash
# Linux crontab:
*/30 * * * * curl -s "https://tangerineartsstudio.com/chatbot/include/ajax.php" -d "function=chatbot-database-training-sync" > /dev/null
```

### Phase 4: ADVANCED FEATURES

#### Fix 4A: User-Specific Data Queries
- Identify logged-in user from conversation
- Filter invoice/client queries by customer ID
- Pass user context to the real-time query tool

#### Fix 4B: Intelligent Date Understanding
- When user says "tomorrow", convert to `2026-02-27`
- Pass date context to embedding search and/or live query

---

## 7. FUNCTION REFERENCE TABLE

| Line | Function | File | Purpose |
|------|----------|------|---------|
| 5361 | `sb_chatbot_database_training_process()` | dialogflow/functions.php | Core: reads DB → text → embeddings |
| 5453 | `sb_chatbot_database_training_save()` | dialogflow/functions.php | Save config + trigger training |
| 5519 | `sb_chatbot_database_training_delete()` | dialogflow/functions.php | Delete source + embeddings |
| 5575 | `sb_chatbot_database_training_delete_all()` | dialogflow/functions.php | Delete all sources |
| 5610 | `sb_chatbot_database_training_get_sources()` | dialogflow/functions.php | List sources for admin UI |
| 5685 | `sb_chatbot_database_training_sync()` | dialogflow/functions.php | Auto-sync (called by cron) |
| 5749 | `sb_chatbot_database_training_sync_manual()` | dialogflow/functions.php | Manual sync from UI |
| 5810 | `sb_chatbot_database_training_sync_status()` | dialogflow/functions.php | Check sync status |
| 2881 | `sb_open_ai_embeddings_generate()` | dialogflow/functions.php | Send text to OpenAI → save vectors |
| 3182 | `sb_open_ai_embeddings_message()` | dialogflow/functions.php | Search embeddings → build context |
| 1400 | `sb_open_ai_message()` | dialogflow/functions.php | Main chatbot response entry point |
| 2825 | `sb_open_ai_embeddings_delete()` | dialogflow/functions.php | Delete embedding files |
| 1840 | `sb_cron_jobs()` | include/functions.php | Cron trigger including DB sync |
| 393-420 | AJAX endpoints | include/ajax.php | All database training HTTP endpoints |

---

## 8. IMMEDIATE ACTION ITEMS

1. **Delete the 2 broken source configs** from `database-training-sources`
2. **Clean orphaned embedding files** (from dead source IDs `db-38d70...` and `db-1d82d...`)
3. **Apply code fixes** (Phase 1: text formatting, AJAX endpoint, JSON bug)
4. **Re-configure database sources** properly with full credentials and table selections
5. **Re-train** with corrected text formatting
6. **Test** by asking schedule/invoice questions
7. **Deploy** to production server

---

## 9. PRODUCTION vs LOCAL COMPARISON

| Aspect | Production (tangerineartsstudio.com) | Local (localhost) |
|--------|--------------------------------------|-------------------|
| Sources in admin | 5 entries with tables shown | 2 entries (no tables) |
| Sources in DB | Unknown (likely same or similar) | 2 broken entries |
| Embeddings | Possibly more (separate server) | 2129 DB-sourced |
| Last sync | 2026-02-26 14:04 (from screenshot) | 2025-11-19 (3 months ago) |
| Schedule data | Exists in DB | Not in embeddings |
| Working? | No (wrong answers) | No (stale + broken) |

**Note:** The production server screenshot (from the first image) showed 5 properly configured sources with tables (`tblproduct_master`, `tblleads,tblschedules_master`, `tblclients`, `tblinvoicepaymentrecords`, `tblinvoices`). But even on production, the chatbot gives wrong answers because of Issues #3 (text quality), #5 (no real-time queries), and #6 (date filtering).

---

*End of Analysis*
