ThetaCoach CRM MCP Server v11.3.0

Local-first sync architecture for 500x faster CRM operations

Claude AI integration for ThetaCoach CRM with instant writes (0-1ms) and background sync to Supabase.

🚀 What's New in v11.3.0

LOCAL-FIRST ARCHITECTURE - 500x Performance Improvement

• Before (v10.x): 200-500ms per write (network latency)
• After (v11.2.0): 0-1ms per write (instant SQLite)
• Claude Flow: 10 updates in 10ms (vs 2-5 seconds network-first)

Key Features:

• ⚡ Write to SQLite first, sync to Supabase in background
• 🔄 Pull from Supabase at start of each MCP call (ensures consistency)
• 🎯 Field-level timestamps for conflict resolution (last-write-wins)
• 📊 Token-limited reads (25k max) prevent Claude slowdown
• 🔁 Background sync queue with retry (exponential backoff)
• ✅ Parallel execution safe (tested with unit tests)

🏗️ Architecture

The Sync Cycle (Every MCP Call)

1. MCP Tool Called
   ↓
2. Sync Supabase → SQLite (~200ms one-time per call)
   ↓
3. Work on SQLite (0-1ms instant)
   ↓
4. Return result to Claude (capped at 25k tokens)
   ↓
5. Background: Sync SQLite → Supabase (async, non-blocking)

Why This Works

Consistency Guarantee:

• Claude makes MCP calls sequentially (one after another)
• By the time Claude finishes processing and makes the next call, background sync has completed
• Each call starts with syncFromSupabase() → SQLite is always fresh

Parallel Execution (Claude Flow):

• When Claude Flow executes MCP calls in parallel, SQLite WAL mode handles it safely
• Tested with 10 parallel writes: 0.04ms/write, no conflicts, 100% success rate
• Field-level timestamps prevent conflicts (different fields = independent updates)
• See test-parallel-execution.mjs for verification

Conflict Resolution

Field-Level Timestamps (Last-Write-Wins):

// Example: Two devices edit same lead
Device A: Updates discovery_notes at timestamp 1000
Device B: Updates discovery_notes at timestamp 2000
Result: Device B wins (newer timestamp)

Device A: Updates discovery_notes at timestamp 1000
Device B: Updates rational_notes at timestamp 1005
Result: Both succeed (different fields, no conflict)

Why This is Acceptable for CRM:

1. Single user per lead (sales rep owns their prospects)
2. Independent fields (discovery vs rational rarely edited simultaneously)
3. Fast sync (background sync completes in 5-10 seconds)
4. Low conflict probability (same field edited on two devices within 10s is rare)

📦 Quick Start (30 seconds)

# 1. Install the MCP server
npm install -D thetacoach-crm-mcp@latest

# 2. Update database schema to v2.6 (CRITICAL!)
# Open: https://thetacoach.biz/ONE-SHOT-CRM-SETUP.sql
# Copy entire file → Paste in Supabase SQL Editor → Run
# This adds field-level timestamps (*_updated_at columns)

# 3. Create .env.local with credentials
cat > .env.local <<EOF
CRM_SUPABASE_URL="https://xxxxx.supabase.co"
CRM_SUPABASE_SERVICE_ROLE_KEY="eyJ..."
EOF

# 4. Register with Claude Code
claude mcp add thetacoach-crm ./node_modules/.bin/thetacoach-crm

# 5. Restart Claude Code (Cmd+Q → reopen)

# 6. Test it!
# In Claude Code, say: "List my CRM leads"

Done! Writes are now instant (0-1ms).

🔄 Sync Strategy Deep Dive

Sync-on-Each-Call Pattern

Every MCP call follows this pattern:

async function handleUpdateCard(args) {
  // STEP 1: Sync from Supabase to SQLite (200ms, ensures fresh data)
  await syncFromSupabase(supabase, db, 'leads', { field: 'email', value: args.email });

  // STEP 2: Write to SQLite (0-1ms, instant)
  writeLocal(db, 'leads', { field: 'email', value: args.email }, {
    discovery_notes: args.discovery_notes,
    rational_notes: args.rational_notes
  });

  // STEP 3: Return immediately (don't wait for background sync)
  return { success: true, latency: '0-1ms' };

  // STEP 4: Background sync happens automatically via SyncQueue
}

Batching & Chunking

Token-Limited Reads (25k Max):

// List leads with automatic token limiting
const leads = batchRead(
  db,
  'SELECT * FROM leads WHERE stage = ? ORDER BY lead_score DESC',
  ['discovery'],
  {
    maxTokens: 25000,
    estimateTokens: (row) => JSON.stringify(row).length / 4 // 1 token ≈ 4 chars
  }
);

// Returns: Array of leads, capped at 25k tokens to prevent Claude slowdown

Why This Matters:

• Battle cards are huge (25k+ tokens each)
• Without capping, reading 100 leads = 2.5M tokens = Claude slowdown
• With capping, reading stops at 25k tokens = instant response

Field Projection (Reduce Tokens):

// Request only the fields you need
const leads = listLeads({
  fields: ['id', 'email', 'name', 'company', 'lead_score', 'stage'],
  limit: 10
});

// Returns: 200 tokens vs 25k tokens (full battle card)

Background Sync Queue

Retry with Exponential Backoff:

class SyncQueue {
  async process() {
    while (this.queue.length > 0) {
      const item = this.queue.shift();
      try {
        await this.syncToSupabase(item);
        // Mark as synced in SQLite
      } catch (error) {
        // Retry with exponential backoff (max 5 retries)
        if (item.retries < 5) {
          const delay = Math.min(1000 * 2 ** item.retries, 60000);
          setTimeout(() => this.add({ ...item, retries: item.retries + 1 }), delay);
        } else {
          // Mark as error in SQLite (_sync_error column)
        }
      }
    }
  }
}

Backoff Schedule:

• Retry 1: 1 second
• Retry 2: 2 seconds
• Retry 3: 4 seconds
• Retry 4: 8 seconds
• Retry 5: 16 seconds
• Max: 60 seconds
• After 5 retries: Give up, log error

🧪 Testing Parallel Execution

Run the included unit test:

node test-parallel-execution.mjs

Test Results:

📊 Test 1: Sequential writes (baseline)
  ⏱️  Time: 0.18ms
  📈 Avg per write: 0.02ms/write

📊 Test 2: Parallel writes (Claude Flow simulation)
  ⏱️  Time: 0.38ms
  📈 Avg per write: 0.04ms/write
  ✅ Successes: 10/10
  ❌ Failures: 0/10

📊 Test 3: Data consistency check
  ✅ All field-level timestamps are valid
  ✅ Sync status correctly marked as pending

📊 Test 4: Concurrent reads during writes
  ⏱️  Time: 0.19ms
  ✍️  Writes: 5
  📖 Reads: 5
  ✅ No deadlocks or blocking

✅ ALL TESTS PASSED - Parallel execution is safe!

Key Insights:

• SQLite WAL mode handles concurrent access correctly
• Field-level timestamps prevent conflicts
• Writes are 0.02-0.04ms each (no blocking)
• Reads don't block writes
• Safe for Claude Flow parallel execution

📋 Database Schema v2.6

Requirements:

• Run ONE-SHOT-CRM-SETUP.sql v2.6 in Supabase
• Download: https://thetacoach.biz/ONE-SHOT-CRM-SETUP.sql
• Safe to re-run (idempotent)

What v2.6 Adds:

-- Field-level timestamps (when each field was last updated)
ALTER TABLE leads ADD COLUMN IF NOT EXISTS discovery_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS rational_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS emotional_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS solution_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS commitment_notes_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS product_positioning_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS outreach_a_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS outreach_b_updated_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS notes_updated_at BIGINT;

-- Sync status columns
ALTER TABLE leads ADD COLUMN IF NOT EXISTS _sync_status TEXT DEFAULT 'synced';
ALTER TABLE leads ADD COLUMN IF NOT EXISTS _last_synced_at BIGINT;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS _sync_error TEXT;

-- Index for performance
CREATE INDEX IF NOT EXISTS idx_leads_sync_status ON leads(_sync_status) WHERE _sync_status = 'pending';

Verify Schema Version:

-- In Supabase SQL Editor
SELECT column_name FROM information_schema.columns
WHERE table_name = 'leads'
AND column_name LIKE '%_updated_at';

-- Should return 9 rows (field timestamps)

📊 SQLite Cache Location

Default:

• ~/.thetacoach-crm/cache-{hash}.db
• Each Supabase URL gets isolated cache (prevents cross-project contamination)
• Example: https://abc.supabase.co → cache-abc12345.db

Custom:

export CRM_SQLITE_PATH=/custom/path/to/cache.db

Multi-Project Support:

• Each project folder has its own .env.local with Supabase credentials
• MCP searches upward for .env.local (like Git searching for .git)
• Cache switches automatically based on Supabase URL
• No Claude Code restart needed when switching projects

🔧 MCP Tools Reference

crm-list-leads - List Leads with Token Limiting

Token-aware pagination:

// Default: Summary fields only (reduces tokens)
crm-list-leads({ limit: 10, offset: 0 })

// Custom fields: Request only what you need
crm-list-leads({
  fields: ['id', 'email', 'name', 'company', 'lead_score', 'stage'],
  limit: 20,
  offset: 0,
  stage: 'discovery',
  min_score: 70
})

Automatic token capping:

• Reads stop at 25k tokens
• Prevents Claude slowdown
• Returns { leads: [...], capped: true/false }

crm-update-card - Update Battle Card (Instant)

crm-update-card({
  email: '[email protected]',
  discovery_notes: 'Full discovery section text...',
  rational_notes: 'ROI calculations...',
  emotional_notes: 'Personal stakes...'
})

// Returns: { success: true, latency: '0-1ms', email: '[email protected]' }

What Happens:

1. Sync from Supabase (200ms)
2. Write to SQLite (0-1ms) with field-level timestamps
3. Return immediately
4. Background sync queues the update to Supabase

crm-update-checklist - Update Phase Checklist

crm-update-checklist({
  email: '[email protected]',
  phase: 'discovery',
  items: [
    'ASK: Of your 30 portfolio companies, what % fail? (hear: 20-30%)',
    'SHARE: Pattern I see across portfolio-backed founders...',
    'CALCULATE TOGETHER: 1 company saved = $500K portfolio value'
  ]
})

// Returns: Instantly updated checklist with auto-generated IDs

Affordance System:

• Automatically sorts bullets by usefulness (personalization × impact × phase)
• Only Tier 1 (ultra-specific) and Tier 2 (context-specific) items
• No generic Tier 3/4 items (waste of checklist space)

🚨 Troubleshooting

Issue: "Writes are slow" or "Still seeing 200ms latency"

Fix: You're running old version or schema not updated

# 1. Verify package version
npm list thetacoach-crm-mcp  # Must show v11.2.0+

# 2. Verify schema has sync columns
# In Supabase SQL Editor:
SELECT column_name FROM information_schema.columns
WHERE table_name = 'leads' AND column_name = '_sync_status';
# Should return 1 row

# 3. If either is wrong, update both:
npm install thetacoach-crm-mcp@latest
# Run SQL: https://thetacoach.biz/ONE-SHOT-CRM-SETUP.sql
# Restart Claude Code

Issue: "Column does not exist" errors

Fix: Database schema is out of date

# Run the LATEST SQL from the website (not an old copy):
open https://thetacoach.biz/ONE-SHOT-CRM-SETUP.sql

# Copy ENTIRE file → Paste in Supabase SQL Editor → Run
# Safe to re-run (idempotent, only adds missing columns)

Issue: "Sync not working" or "Changes not appearing in Supabase"

Debug sync status:

// In Claude, say: "CRM sync status"
// Should show:
{
  pending: 0,
  status: 'synced',
  lastSync: '2025-11-04T21:45:00Z'
}

Check:

1. Network connectivity (can you access Supabase?)
2. Service role key is correct in .env.local
3. Local SQLite cache exists: ls ~/.thetacoach-crm/

📖 Documentation

• CHANGELOG.md - Release notes for v11.2.0
• IMPLEMENTATION-GUIDE.md - Architecture deep dive (350 lines)
• test-parallel-execution.mjs - Unit tests for parallel safety
• migrations/ - SQLite migration scripts

🔗 Links

• SQL Schema: https://thetacoach.biz/ONE-SHOT-CRM-SETUP.sql
• GitHub: https://github.com/wiber/thetadrivencoach/tree/main/packages/thetacoach-crm-mcp
• Support: https://github.com/wiber/thetadrivencoach/issues

📜 License

MIT

v11.3.0 - Local-first sync for 500x faster CRM operations