Salesforce Schema Sync

Relevant source files

The following files were used as context for generating this wiki page:

• convex/orgMetadata.ts
• convex/crons.ts
• README.md
• convex/ai.ts
• convex/http.ts

The Salesforce Schema Sync system is a critical backend process that provides the AI assistant with rich, contextual information about a specific user's Salesforce organization. It discovers custom objects, fields, picklist values, and relationships, enabling the AI to understand and interact with unique Salesforce configurations beyond standard objects. This allows the AI to construct valid SOQL queries and perform operations on custom objects like Invoice__c or sendblue__Message__c.

The system is designed to be both automatic, with a weekly refresh, and manually triggerable via an API endpoint. The synced metadata is stored in the Convex database and consumed by the AI action askSalesforce to build a detailed system prompt for the language model.

Sources: README.md:65-70, convex/ai.ts:192-203, convex/orgMetadata.ts:241-250

Architecture and Data Flow

The schema sync process can be initiated either manually through an API call or automatically via a scheduled cron job. In both cases, the syncFromSalesforce action is executed, which connects to the Salesforce REST API, fetches the schema details, processes the data, and stores it in the orgMetadata table in Convex.

This diagram illustrates the overall data flow for the schema synchronization process.

Metadata Discovery Process

The core of the sync is the syncFromSalesforce internal action. It performs a deep discovery of the Salesforce org's schema.

Sources: convex/orgMetadata.ts:241-432

1. Object Discovery

The process begins by fetching all sObjects from the Salesforce org using the "Describe Global" API endpoint.

• It filters this list to keep only queryable objects.
• It separates objects into two categories:
  1. Standard Objects: A predefined list of important standard objects like Account, Contact, and Opportunity are included.
  2. Custom Objects: All objects where custom is true are collected. System objects ending in __Share, __History, __Feed, __ChangeEvent, or __mdt are excluded.

Sources: convex/orgMetadata.ts:269-302, convex/orgMetadata.ts:8-25

2. Field and Relationship Discovery

For each custom object, the system makes another API call to the "Describe sObject" endpoint to retrieve detailed field metadata.

• It captures all queryable custom fields (__c) and common standard fields (Id, Name, OwnerId, etc.).
• For each field, it stores critical attributes: name, label, type, and inlineHelpText.
• Picklist Values: For fields of type picklist, it captures up to the first 10 active values.
• Relationships: For lookup fields (type: "reference"), it captures the referenceTo object and the relationshipName (e.g., sendblue__Lead__r), which is crucial for the AI to construct relationship queries in SOQL.

Sources: convex/orgMetadata.ts:329-378

3. Data Sampling and Record Counts

To provide the AI with even richer context about how objects are used, the system performs two additional queries for each custom object:

1. Record Count: It runs a SELECT COUNT() FROM <ObjectName> query to determine how many records exist for that object.
2. Sample Fields: If records exist, it fetches the most recent record (ORDER BY CreatedDate DESC LIMIT 1) and identifies which fields are populated (not null). This helps the AI prioritize which fields to use in its responses.

This diagram shows the sequence for describing a single custom object.

Database Schema

The discovered metadata is stored in the orgMetadata table in Convex. Each document represents the schema for a single Salesforce instance, identified by its unique instanceUrl.

orgMetadata Table Schema

Field	Type	Description
instanceUrl	string	The normalized Salesforce instance URL (e.g., https://yourorg.my.salesforce.com). Acts as the unique identifier.
standardObjects	array(object)	A list of important standard objects and their labels.
customObjects	array(object)	A detailed list of custom objects, including their fields, record counts, and sample data.
lastSyncedAt	number	Timestamp of the last successful sync.
syncStatus	string	The current status of the sync (pending, syncing, complete, error).
syncError	string (optional)	The error message if the last sync failed.

Sources: convex/orgMetadata.ts:131-168

This ER diagram shows the structure of the orgMetadata table and its nested custom object details.

Automation and Triggers

The schema sync is designed to run automatically and can also be triggered on demand.

Weekly Cron Job

A cron job is scheduled to run every Sunday at 2 AM UTC. This job executes the syncAllOrgs action, which iterates through all unique Salesforce connections and triggers the syncFromSalesforce action for each one, ensuring the metadata is kept fresh.

// convex/crons.ts:5-7
cron.schedule({
  cronExpression: "0 2 * * SUN", // Every Sunday at 2am UTC
  handler: internal.orgMetadata.syncAllOrgs,
});

Sources: README.md:87, convex/crons.ts:5-7, convex/orgMetadata.ts:434-471

Manual Sync API

A manual sync can be triggered by an administrator via a POST request to an HTTP endpoint. This is useful for initial setup or forcing an immediate refresh after making schema changes in Salesforce.

Endpoint	Method	Description
/api/org/sync-metadata	POST	Manually triggers the metadata sync for the user's organization specified in the request body.
/api/org/metadata	GET	Retrieves the currently stored metadata for a given user's organization.

Sources: README.md:92-95, convex/http.ts:1008-1061

AI Consumption

The primary consumer of the synced metadata is the askSalesforce action, which powers the AI assistant.

Before calling the Claude language model, the action retrieves the org-specific metadata using the getAvailableObjects query. It then formats this data into a detailed customObjectsContext string, which is appended to the main system prompt. This provides the AI with a comprehensive, structured overview of all available custom objects, their fields, types, picklist values, and relationships. This context is critical for the AI to generate accurate and valid SOQL queries for the user's specific org.

This flow diagram shows how the AI action uses the synced metadata.

Summary

The Salesforce Schema Sync is a foundational system in TalkCRM that bridges the gap between a generic AI and a user's specific Salesforce environment. By automatically discovering and refreshing custom object schemas, it empowers the AI to handle complex, org-specific requests without manual configuration. This automated context-building is what allows TalkCRM to support a wide range of Salesforce customizations out-of-the-box, making the voice assistant a powerful and adaptable tool for any sales team.

Sources: README.md:65-70, convex/ai.ts:192-203