// Sync leads from Zoho CRM into the local SQLite database. Fetches all leads, performs upserts with change detection, and maintains full audit history.
| name | crm-sync |
| description | Sync leads from Zoho CRM into the local SQLite database. Fetches all leads, performs upserts with change detection, and maintains full audit history. |
| allowed-tools | ["Bash","Read","mcp__zoho-crm__ZohoCRM_Get_Records"] |
Synchronise leads from Zoho CRM into the local SQLite database.
raw/crm/crm.dbscripts/crm/db_schema.sqlleads (current state):
leads_history (audit trail):
sync_log (sync metadata):
Ensure the database exists with correct schema:
mkdir -p raw/crm
sqlite3 raw/crm/crm.db < scripts/crm/db_schema.sql
sqlite3 raw/crm/crm.db "INSERT INTO sync_log (started_at, status) VALUES (datetime('now'), 'running'); SELECT last_insert_rowid();"
Save the returned ID for later update.
Use mcp__zoho-crm__ZohoCRM_Get_Records:
Handle pagination if more than one page of results.
For each lead returned from Zoho:
4a. Check if exists:
sqlite3 raw/crm/crm.db "SELECT id, zoho_modified_time FROM leads WHERE id = '{lead_id}';"
4b. If new lead (not in database):
sqlite3 raw/crm/crm.db "
INSERT INTO leads (id, first_name, last_name, company, email, phone, lead_source, description, created_at, updated_at, zoho_modified_time)
VALUES ('{id}', '{first_name}', '{last_name}', '{company}', '{email}', '{phone}', '{lead_source}', '{description}', datetime('now'), datetime('now'), '{modified_time}');
INSERT INTO leads_history (lead_id, first_name, last_name, company, email, phone, lead_source, description, captured_at, change_type)
VALUES ('{id}', '{first_name}', '{last_name}', '{company}', '{email}', '{phone}', '{lead_source}', '{description}', datetime('now'), 'insert');
"
4c. If existing and Zoho version is newer (compare zoho_modified_time):
sqlite3 raw/crm/crm.db "
-- Capture old version to history
INSERT INTO leads_history (lead_id, first_name, last_name, company, email, phone, lead_source, description, captured_at, change_type)
SELECT id, first_name, last_name, company, email, phone, lead_source, description, datetime('now'), 'update'
FROM leads WHERE id = '{id}';
-- Update the lead
UPDATE leads SET
first_name = '{first_name}',
last_name = '{last_name}',
company = '{company}',
email = '{email}',
phone = '{phone}',
lead_source = '{lead_source}',
description = '{description}',
updated_at = datetime('now'),
zoho_modified_time = '{modified_time}'
WHERE id = '{id}';
"
4d. If unchanged (same or older zoho_modified_time): Skip.
On success:
sqlite3 raw/crm/crm.db "
UPDATE sync_log SET
completed_at = datetime('now'),
records_fetched = {fetched},
records_inserted = {inserted},
records_updated = {updated},
status = 'completed'
WHERE id = {sync_id};
"
On failure:
sqlite3 raw/crm/crm.db "
UPDATE sync_log SET
completed_at = datetime('now'),
status = 'failed',
error_message = '{error}'
WHERE id = {sync_id};
"
Output a summary:
| Zoho Field | Database Column |
|---|---|
| id | id |
| First_Name | first_name |
| Last_Name | last_name |
| Company | company |
| Phone | phone |
| Lead_Source | lead_source |
| Description | description |
| Modified_Time | zoho_modified_time |