# Connect AI Salesforce

> Use when querying Salesforce data through CData Connect AI. Covers the Salesforce data model, query patterns, stored procedures, and Salesforce-specific conventions. Composes on top of the connect-ai-base skill.

- Skill: `cdatasoftware/connect-ai-salesforce` (Agent Skill)
- Install (CLI): `npx skillmds@latest add cdatasoftware/connect-ai-salesforce`
- Raw SKILL.md: https://api.skillmd.com/api/skills/cdatasoftware/connect-ai-salesforce/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- License: MIT
- Author: cdatasoftware (https://skillmd.com/u/cdatasoftware)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/cdatasoftware/connect-ai-salesforce

---


# CData Connect AI — Salesforce Skill

## ⚠ Prerequisites — load these first
- [ ] connect-ai-base skill

Before proceeding, locate and read the connect-ai-base skill. If it is not available in the current environment (not loaded, not enabled, or not installed), stop immediately. Do not proceed with the task. Tell the user that the connect-ai-base skill is required and ask them to install and enable it before continuing.

This skill provides Salesforce-specific guidance for querying Salesforce data through CData Connect AI. It composes on top of the `connect-ai-base` skill, which handles the discovery workflow, error recovery, SQL dialect, and query naming convention.

## Precedence

This skill replaces `getInstructions` for the Salesforce driver. Do not call `getInstructions` for Salesforce — the guidance it provides is already incorporated here. Proceed directly to schema discovery (`getTables` / `getColumns`) after identifying the Salesforce connection via `getCatalogs`.

## Schema

Salesforce is a single-schema driver. The schema name is `Salesforce`.

```sql
SELECT * FROM [YourConnection].[Salesforce].[Account] LIMIT 5
```

## Query Process

1. **Identify the connection** — if unknown, call `getCatalogs` and filter for `DRIVER = 'Salesforce'`.
2. **Locate the object** — use `getTables` to find the right Salesforce object. For custom objects, use `tableName: "%__c"`.
3. **Inspect columns** — call `getColumns` before writing any query. For picklist fields, query `PickListValues` to get valid values (see Conventions).
4. **Sample first** — run a `SELECT * … LIMIT 5` to validate structure and values before building complex queries.
5. **Filter on date columns** — Salesforce queries over large objects will time out without date filters; always add a `CreatedDate` or `LastModifiedDate` predicate when the result set could be large.
6. **Add JOINs and aggregations incrementally** — validate each join step before adding the next.

### Fallback when getTables or getProcedures are unavailable

If `getTables` or `getProcedures` are not available (disabled at the connection level or not exposed by the current tool surface), query the system catalog tables directly via `queryData`:

```sql
-- List all tables
SELECT [TableName], [TableType] FROM [YourConnection].[Salesforce].[sys_tables] ORDER BY [TableName]

-- List all stored procedures
SELECT [ProcedureName] FROM [YourConnection].[Salesforce].[sys_procedures] ORDER BY [ProcedureName]

-- List custom objects only
SELECT [TableName] FROM [YourConnection].[Salesforce].[sys_tables] WHERE [TableName] LIKE '%__c' ORDER BY [TableName]
```

These system tables are always available via `queryData` regardless of which discovery tools are enabled.

## Data Model

### Key Tables

- **Account** — Organizations and companies; the central entity most other objects relate to.
- **Contact** — Individual people, linked to Accounts via `AccountId`.
- **Lead** — Unqualified prospects; separate from Contacts until converted via `ConvertLead`.
- **Opportunity** — Sales deals and pipeline; linked to Accounts and optionally Contacts.
- **Case** — Customer service and support tickets; linked to Accounts and Contacts.
- **Campaign** — Marketing campaigns; tracks members, responses, and revenue attribution.
- **PickListValues** — Virtual table exposing all picklist field values for any Salesforce object. Filter by `TableName` and optionally `ColumnName`.

### Custom Objects and Fields

Salesforce orgs frequently contain custom objects and fields using the `__c` suffix:
- **Find custom objects**: call `getTables` with `tableName: "%__c"`
- **Find custom fields on a table**: call `getColumns` on any table and look for column names ending in `__c`

### Key Relationships

| Child table | Join column | Parent table |
|-------------|-------------|--------------|
| Contact | `AccountId` | Account |
| Opportunity | `AccountId` | Account |
| Case | `AccountId` | Account |
| Case | `ContactId` | Contact |
| Campaign | `ParentId` | Campaign (hierarchy) |

## Important Columns

### Account
| Column | Type | Notes |
|--------|------|-------|
| `Id` | VARCHAR | Primary key (readonly) |
| `Name` | VARCHAR | Account/company name |
| `Type` | VARCHAR | Picklist: Customer, Partner, Prospect, etc. |
| `Industry` | VARCHAR | Picklist field |
| `OwnerId` | VARCHAR | ID of the owning user |
| `ParentId` | VARCHAR | Parent account for hierarchies |
| `CreatedDate` | TIMESTAMP | Use for date-range filters |
| `LastModifiedDate` | TIMESTAMP | Use for incremental queries |
| `LastActivityDate` | DATE | Most recent logged activity |
| `BillingCity` / `BillingState` / `BillingCountry` | VARCHAR | Billing address parts |

### Contact
| Column | Type | Notes |
|--------|------|-------|
| `Id` | VARCHAR | Primary key (readonly) |
| `Name` | VARCHAR | Full name (readonly — write `FirstName` / `LastName` separately) |
| `FirstName`, `LastName` | VARCHAR | Writable name parts |
| `AccountId` | VARCHAR | Links Contact to Account |
| `Email` | VARCHAR | Primary email |
| `Title` | VARCHAR | Job title |
| `LastActivityDate` | DATE | Most recent logged activity |
| `ReportsToId` | VARCHAR | Contact hierarchy (manager) |
| `IsEmailBounced` | BOOLEAN | Email deliverability flag (readonly) |

### Opportunity
| Column | Type | Notes |
|--------|------|-------|
| `Id` | VARCHAR | Primary key (readonly) |
| `Name` | VARCHAR | Opportunity name |
| `AccountId` | VARCHAR | Links to Account |
| `StageName` | VARCHAR | Picklist: pipeline stage |
| `Amount` | DECIMAL | Deal value |
| `CloseDate` | DATE | Expected close date |
| `Probability` | DOUBLE | Likelihood to close (%) |
| `IsClosed` | BOOLEAN | Controlled by StageName (readonly) |
| `IsWon` | BOOLEAN | True for Closed Won (readonly) |
| `ForecastCategoryName` | VARCHAR | Forecast roll-up category |
| `LeadSource` | VARCHAR | Originating lead source |
| `ContactId` | VARCHAR | Primary contact on the deal |

### Lead
| Column | Type | Notes |
|--------|------|-------|
| `Id` | VARCHAR | Primary key (readonly) |
| `Name` | VARCHAR | Full name (readonly — write `FirstName` / `LastName` separately) |
| `Company` | VARCHAR | Required for Lead creation |
| `Status` | VARCHAR | Picklist: lead status |
| `LeadSource` | VARCHAR | Origin channel |
| `IsConverted` | BOOLEAN | True after ConvertLead |
| `ConvertedDate` | DATE | Date of conversion (readonly) |
| `ConvertedAccountId` | VARCHAR | Resulting Account (readonly) |
| `ConvertedContactId` | VARCHAR | Resulting Contact (readonly) |
| `ConvertedOpportunityId` | VARCHAR | Resulting Opportunity (readonly) |

### Case
| Column | Type | Notes |
|--------|------|-------|
| `Id` | VARCHAR | Primary key (readonly) |
| `CaseNumber` | VARCHAR | Auto-generated case number (readonly) |
| `AccountId` | VARCHAR | Links to Account |
| `ContactId` | VARCHAR | Links to Contact |
| `Status` | VARCHAR | Picklist |
| `Priority` | VARCHAR | Picklist |
| `Subject` | VARCHAR | Case title |
| `IsClosed` | BOOLEAN | True for closed cases (readonly) |
| `Origin` | VARCHAR | Channel: Email, Web, Phone |

### Campaign
| Column | Type | Notes |
|--------|------|-------|
| `Id` | VARCHAR | Primary key (readonly) |
| `Name` | VARCHAR | Campaign name |
| `Type` | VARCHAR | Picklist: campaign type |
| `Status` | VARCHAR | Picklist |
| `StartDate`, `EndDate` | DATE | Campaign date range |
| `NumberOfLeads` | INTEGER | Auto-maintained count (readonly) |
| `NumberOfOpportunities` | INTEGER | Auto-maintained count (readonly) |
| `AmountWonOpportunities` | DECIMAL | Revenue attributed (readonly) |

### PickListValues
| Column | Type | Notes |
|--------|------|-------|
| `TableName` | VARCHAR | Required filter: the Salesforce object name |
| `ColumnName` | VARCHAR | Optional filter: the field name |
| `PickList_Value` | VARCHAR | Internal API value (use this in WHERE clauses) |
| `PickList_Label` | VARCHAR | UI display label |
| `PickList_IsActive` | BOOLEAN | Whether value is currently selectable |
| `PickList_IsDefault` | BOOLEAN | Whether value is the default |

## Common Query Patterns

### Open opportunities for a specific account
```sql
SELECT [Name], [StageName], [Amount], [CloseDate], [Probability]
FROM [YourConnection].[Salesforce].[Opportunity]
WHERE [AccountId] IN (
  SELECT [Id]
  FROM [YourConnection].[Salesforce].[Account]
  WHERE [Name] LIKE '%Acme%'
)
AND [IsClosed] = 0
ORDER BY [CloseDate] ASC
```

### Contacts with recent activity
```sql
SELECT c.[Name], c.[Email], c.[Title], a.[Name] AS AccountName, c.[LastActivityDate]
FROM [YourConnection].[Salesforce].[Contact] c
LEFT JOIN [YourConnection].[Salesforce].[Account] a ON c.[AccountId] = a.[Id]
WHERE c.[LastActivityDate] >= '2024-01-01'
ORDER BY c.[LastActivityDate] DESC
LIMIT 20
```

### Pipeline summary by stage
```sql
SELECT [StageName], COUNT(*) AS OpportunityCount, SUM([Amount]) AS TotalAmount
FROM [YourConnection].[Salesforce].[Opportunity]
WHERE [IsClosed] = 0
AND [CloseDate] >= '2025-01-01'
GROUP BY [StageName]
ORDER BY TotalAmount DESC
```

### Unconverted leads by source
```sql
SELECT [FirstName], [LastName], [Company], [Email], [Status], [CreatedDate]
FROM [YourConnection].[Salesforce].[Lead]
WHERE [IsConverted] = 0
AND [LeadSource] = 'Web'
AND [CreatedDate] >= '2025-01-01'
ORDER BY [CreatedDate] DESC
```

### Accounts without any open opportunities (NOT EXISTS pattern)
```sql
SELECT [Id], [Name], [Industry], [LastActivityDate]
FROM [YourConnection].[Salesforce].[Account]
WHERE NOT EXISTS (
  SELECT 1 FROM [YourConnection].[Salesforce].[Opportunity] o
  WHERE o.[AccountId] = [Account].[Id] AND o.[IsClosed] = 0
)
AND [LastModifiedDate] >= '2025-01-01'
```

### Look up valid picklist values for a field
```sql
SELECT [PickList_Value], [PickList_Label], [PickList_IsDefault]
FROM [YourConnection].[Salesforce].[PickListValues]
WHERE [TableName] = 'Opportunity'
AND [ColumnName] = 'StageName'
AND [PickList_IsActive] = true
```

### Discover custom objects
Use `getTables` with a wildcard to list all custom objects in the org:
```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "tableName": "%__c"
}
```

## Stored Procedures

### ConvertLead
Converts a Lead into an Account, Contact, and optionally an Opportunity.

Required parameters: `@LeadId`, `@ConvertedStatus`. Before calling, retrieve valid converted status values:
```sql
SELECT [Id], [MasterLabel]
FROM [YourConnection].[Salesforce].[LeadStatus]
WHERE [IsConverted] = true
```

```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "ConvertLead",
  "parameters": {
    "@LeadId": "00Q000000000001",
    "@ConvertedStatus": "Qualified",
    "@DoNotCreateOpportunity": "false",
    "@OpportunityName": "Acme — New Deal"
  }
}
```

To merge into an existing Account and Contact instead of creating new ones, add `@AccountId` and `@ContactId`. Set `@DoNotCreateOpportunity` to `"true"` and omit `@OpportunityName` when no Opportunity is needed.

### GetUpdated
Returns IDs of records updated within a UTC time window for a given object type. Seconds in timestamps are ignored by the Salesforce API.

```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "GetUpdated",
  "parameters": {
    "@ObjectType": "Account",
    "@StartDate": "2025-09-09T13:00:00.000Z",
    "@EndDate": "2025-09-12T13:00:00.000Z"
  }
}
```

### GetDeleted
Returns IDs of records deleted within a UTC time window for a given object type. Same parameters as `GetUpdated`: `@ObjectType`, `@StartDate`, `@EndDate`.

### Search
Executes a full-text search across Salesforce objects. Accepts either a plain search term or a SOSL query (not both).

```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "Search",
  "parameters": {
    "@SearchTerm": "Acme Corporation"
  }
}
```

For a targeted SOSL query:
```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "Search",
  "parameters": {
    "@SOSL": "FIND {Acme} IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, Name, Email)"
  }
}
```

### Merge
Combines up to three records of the same object type into a single master record. The non-master records are deleted after the merge.

```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "Merge",
  "parameters": {
    "@ObjectType": "Contact",
    "@MasterRecordId": "003000000000001",
    "@RecordToMergeIds": "003000000000002,003000000000003"
  }
}
```

### Undelete
Restores records from the Salesforce Recycle Bin. Use `GetDeleted` first to obtain the IDs of the records to restore.

### GetRecordCount
Returns a **cached** record count for a Salesforce object. Results may lag real-time data — use `COUNT(*)` queries for accurate counts.

### GetUserInformation
Returns personal details about the authenticated Salesforce user. Useful for confirming which user and org the active connection is running under. No parameters required.

### GetLimitInfo
Returns API usage and limit details for the Salesforce org. Use before running large or bulk operations to verify sufficient API capacity remains.

### DownloadAttachment

Downloads an attachment and returns its content as base64 in the `FileData` column — set `@Encoding = 'BASE64'` and omit `@FileStream`. Confirmed working in cloud Connect AI environments. (This driver has no `@LocalPath` / `@FileLocation` parameter.)

**Key parameters** — use exactly these names:

| Parameter | Required | Notes |
|-----------|----------|-------|
| `@Id` | Conditional | ID of the specific attachment. Required if `@Name` is not provided. **Do not use `@AttachmentId`** — this parameter does not exist and will cause a runtime error. |
| `@ObjectId` | No | ID of the parent Salesforce object. Use to download all attachments on a record when `@Id` is unknown. |
| `@Name` | No | Filename as alternative to `@Id` |
| `@LightningMode` | No | Set to `'true'` for Lightning Files (ContentDocument); omit for Classic Attachments |
| `@Encoding` | No | Set `'BASE64'` to control the encoding of the returned `FileData` |
| `@FileStream` | No | Output stream — omit in cloud to receive base64 in `FileData` |

```json
{
  "catalogName": "YourConnection",
  "schemaName": "Salesforce",
  "procedureName": "DownloadAttachment",
  "parameters": {
    "@Id": "00P000000000001"
  }
}
```

Response includes `FileData` (base64-encoded content), `FileName`, `Success`, and `FailureMessage`.

### File procedures — cloud compatibility

| Procedure | Cloud status |
|-----------|-------------|
| `DownloadAttachment` | **Confirmed working** — returns base64 content in `FileData` when `@FileStream` is omitted (`@Encoding = 'BASE64'`) |
| `DownloadContentDocument` | Requires validation — may return base64 content without `FileLocation` |
| `DownloadDocument` | Requires validation — may return base64 content without `FileLocation` |
| `UploadAttachment` | Likely non-functional — requires InputStream not available via MCP |
| `UploadContentDocument` | Likely non-functional — requires InputStream not available via MCP |
| `UploadDocument` | Likely non-functional — requires InputStream not available via MCP |

## Toolkit Procedure Calls

When executing stored procedures via a **toolkit** surface (e.g. `salesforce_db_execute_procedure`) rather than the generic MCP `executeProcedure` tool, use the following call shape:

- **`EXEC`** keyword (not `EXECUTE`).
- **Fully-qualified procedure name**: `[Catalog].[Schema].ProcedureName`.
- **Positional parameter names** `@p1`, `@p2`, `@p3`, ... — each `@pN` maps to the parameter at ordinal position N (1-based) as returned by `get_procedure_parameters`. Named parameters (e.g. `@ObjectType = 'Account'`) are also supported, but positional `@pN` is preferred for consistency across procedures.
- **Inline values** in the `sql` string. Parameter binding via a separate `parameters` object is not supported by the toolkit's `execute_procedure` tool.

You can supply any subset of parameters by ordinal — skipped and out-of-order ordinals are tolerated, so the call only needs to mention the parameters relevant to it.

### GetUpdated

Parameter map: `@p1` = `ObjectType`, `@p2` = `StartDate`, `@p3` = `EndDate`.

```json
{
  "sql": "EXEC [YourConnection].[Salesforce].GetUpdated @p1 = 'Account', @p2 = '2026-05-01T00:00:00.000Z', @p3 = '2026-05-15T23:59:59.000Z'"
}
```

### ConvertLead

Parameter map: `@p1` = `AccountId`, `@p2` = `ContactId`, `@p3` = `ConvertedStatus`, `@p4` = `DoNotCreateOpportunity`, `@p5` = `LeadId`, `@p6` = `OpportunityName`, `@p7` = `OverwriteLeadSource`, `@p8` = `OwnerId`, `@p9` = `SendNotificationEmail`.

Required params are `@p3` (`ConvertedStatus`) and `@p5` (`LeadId`); the example below also sets `@p4` (`DoNotCreateOpportunity`):

```json
{
  "sql": "EXEC [YourConnection].[Salesforce].ConvertLead @p3 = 'Closed - Converted', @p5 = '00Q000000000001', @p4 = 'true'"
}
```

### DownloadAttachment

Parameter map: `@p1` = `ObjectId`, `@p2` = `Id`, `@p3` = `Name`, `@p4` = `LightningMode`, `@p5` = `Encoding`, `@p6` = `FileStream`.

Download by attachment ID (`@p2` = `Id`):

```json
{
  "sql": "EXEC [YourConnection].[Salesforce].DownloadAttachment @p2 = '00P000000000001'"
}
```

### DownloadContentDocument

Parameter map: `@p1` = `Id`, `@p2` = `Title`, `@p3` = `LocalPath`, `@p4` = `Encoding`, `@p5` = `FileStream`.

```json
{
  "sql": "EXEC [YourConnection].[Salesforce].DownloadContentDocument @p1 = '069000000000001'"
}
```

The generic MCP `executeProcedure` tool handles catalog resolution and parameter binding internally — this guidance applies only to toolkit `execute_procedure` calls.

## Write Operations

### INSERT example
```sql
INSERT INTO [YourConnection].[Salesforce].[Lead]
  ([FirstName], [LastName], [Company], [Email], [LeadSource])
VALUES
  ('Jane', 'Smith', 'Acme Corp', 'jane@acme.com', 'Web')
```

### UPDATE example
```sql
UPDATE [YourConnection].[Salesforce].[Opportunity]
SET [StageName] = 'Closed Won', [Probability] = 100
WHERE [Id] = '006000000000001'
```

Write access is controlled by the CData Connect AI connection settings. If INSERT, UPDATE, or stored procedure calls return permission errors, guide the user to their Connect AI connection settings and confirm that write access is enabled — connections may be set to readonly mode by default.

## Salesforce-Specific Conventions

- **Leads are separate from Contacts** — a Lead only becomes a Contact after conversion via `ConvertLead`. Do not join Lead and Contact as if they are the same entity; they live in separate tables with different schemas.
- **Date filters prevent timeouts** — always include a `CreatedDate` or `LastModifiedDate` predicate when querying large objects (Account, Contact, Lead, Opportunity). Use explicit date literals (`'2025-01-01'`) instead of `DATEADD()` for best performance in Connect AI.
- **NULL foreign keys are common** — Salesforce allows many FK columns (e.g., `AccountId` on Contact) to be NULL. Use `IS NOT NULL` guards in WHERE clauses and prefer `LEFT JOIN` over `INNER JOIN` when joining related objects.
- **Use NOT EXISTS over complex LEFT JOINs** — for "find records without related records" queries, `NOT EXISTS` is more reliable than `LEFT JOIN … WHERE key IS NULL`.
- **Verify picklist values before filtering** — always query `PickListValues` to confirm valid values before filtering on picklist columns like `StageName`, `Status`, `Priority`, or `LeadSource`. The internal `PickList_Value` (used in WHERE clauses) may differ from the UI label in `PickList_Label`.
- **Custom fields and objects use `__c` suffix** — any column or table name ending in `__c` is a custom extension specific to that org. Use `getTables` with `tableName: "%__c"` to discover custom objects; use `getColumns` and look for `__c` columns to find custom fields on standard objects.
- **`Name` is readonly on Contact and Lead** — write `FirstName` and `LastName` separately; `Name` is a derived computed field and cannot be set directly.
- **`IsClosed` and `IsWon` are readonly on Opportunity** — they are controlled by Salesforce based on the `StageName` picklist value. Update `StageName` directly; do not attempt to set `IsClosed` or `IsWon`.
- **`CaseNumber` is auto-generated and readonly** — do not include it in INSERT statements.
- **Verify COUNT before aggregating** — run a `COUNT(*)` with your intended filters before running expensive aggregations to validate data volume and filter correctness.