Convex Database Schema
Relevant source files
The following files were used as context for generating this wiki page:The Convex database is the backend backbone for TalkCRM, providing a real-time, schema-enforced data layer. It is responsible for storing all application state, including user information, Salesforce authentication credentials, conversation logs, AI agent sessions, and synced Salesforce metadata. The schema is defined in TypeScript using defineSchema and defineTable from the Convex server SDK, ensuring type safety and data consistency across the application.
This document outlines the structure of the database, detailing each table, its fields, and its relationships with other tables. The schema is designed to be multi-tenant, with most data linked to a specific user, enabling personalized experiences and secure data isolation. The real-time capabilities of Convex are leveraged to provide live updates to the frontend dashboard, such as displaying agent activity as it happens.
Sources: convex/schema.ts, PROJECT_DESCRIPTION.md:65-92
Entity Relationship Diagram
The following diagram provides a high-level overview of the main tables in the Convex database and their relationships.
This diagram illustrates the central role of the users table, which links to various feature-specific data tables.
Sources: convex/schema.ts:5-394
Core Tables
User Management
This set of tables manages user identity, authentication, and contact information. It supports both standard email/password auth via Convex Auth and phone number verification for caller ID authentication.
Sources: convex/schema.ts:7-47
users
Stores information about each TalkCRM user. It is extended from the standard @convex-dev/auth tables.
| Field | Type | Description |
|---|---|---|
email | v.string() | User's unique email address. Indexed. |
name | v.optional(v.string()) | User's full name. |
image | v.optional(v.string()) | URL for the user's profile picture from OAuth. |
verifiedPhones | v.optional(v.array(v.string())) | An array of E.164 formatted phone numbers for caller ID auth. Indexed. |
primaryPhone | v.optional(v.string()) | The main phone number for SMS notifications. |
status | v.optional(v.union(...)) | Account status: "active", "suspended", or "pending". |
tier | v.optional(v.union(...)) | Subscription tier: "free", "starter", "pro", "enterprise". |
Sources: convex/schema.ts:11-28
phoneVerifications
Temporarily stores verification codes sent to users' phones. Records in this table are expected to have a short lifespan.
| Field | Type | Description |
|---|---|---|
phone | v.string() | E.164 formatted phone number being verified. Indexed. |
code | v.string() | The 6-digit verification code. |
userId | v.optional(v.id("users")) | The user to associate the phone with if they already exist. |
expiresAt | v.number() | Timestamp for when the code expires. Indexed for auto-cleanup. |
attempts | v.number() | Number of failed verification attempts. |
Sources: convex/schema.ts:31-47
Salesforce Integration
These tables manage the connection to each user's Salesforce organization, including OAuth credentials and synced metadata, which is crucial for the AI's contextual understanding.
Sources: convex/schema.ts:49-130
This diagram shows how a user's salesforceAuth record connects to both the org-specific orgCredentials and the cached orgMetadata.
Sources: convex/schema.ts:54-130
salesforceAuth
Stores per-user Salesforce OAuth credentials, linking a TalkCRM user to their Salesforce identity.
| Field | Type | Description |
|---|---|---|
userId | v.id("users") | Foreign key linking to the users table. Indexed. |
accessToken | v.string() | The access token for making Salesforce API calls. |
refreshToken | v.string() | The refresh token to obtain a new access token. |
instanceUrl | v.string() | The user's specific Salesforce instance URL. |
expiresAt | v.number() | Timestamp for when the access token expires. |
salesforceUserId | v.optional(v.string()) | The Salesforce User ID for this connection. |
Sources: convex/schema.ts:54-61
orgCredentials
Stores per-org Connected App credentials, enabling multi-tenant OAuth flows.
| Field | Type | Description |
|---|---|---|
instanceUrl | v.string() | The base URL of the Salesforce instance. Indexed. |
consumerKey | v.string() | The consumer key for the Salesforce Connected App. |
consumerSecret | v.string() | The consumer secret for the Salesforce Connected App. |
Sources: convex/schema.ts:64-71
orgMetadata
Caches Salesforce organization schema information, such as available standard and custom objects, fields, and record counts. This data provides rich context to the AI assistant. The syncFromSalesforce action in convex/orgMetadata.ts is responsible for populating this table.
| Field | Type | Description |
|---|---|---|
instanceUrl | v.string() | The Salesforce instance URL. Indexed. |
standardObjects | v.array(v.object({...})) | An array of standard Salesforce objects available in the org. |
customObjects | v.array(v.object({...})) | An array of custom objects with rich metadata, including fields, descriptions, and sample data. |
lastSyncedAt | v.number() | Timestamp of the last successful sync. |
syncStatus | v.union(...) | The current sync status: "pending", "syncing", "complete", or "error". |
syncError | v.optional(v.string()) | Error message if the last sync failed. |
Sources: convex/schema.ts:74-130, convex/orgMetadata.ts:133-281
Conversation and Activity Logging
These tables provide a comprehensive audit trail of all interactions with the system, including voice calls, tool usage, and real-time agent activity.
Sources: convex/schema.ts:132-205
conversations
Logs all voice conversations, capturing metadata from Twilio and ElevenLabs, as well as outcomes and summaries.
| Field | Type | Description |
|---|---|---|
conversationId | v.string() | The Twilio Call SID. Indexed. |
elevenlabsConversationId | v.optional(v.string()) | The corresponding ID from ElevenLabs. Indexed. |
userId | v.optional(v.id("users")) | The user who initiated the call. Indexed. |
status | v.union(...) | The call status: "active", "completed", or "failed". |
transcript | v.optional(v.string()) | The full transcript of the call. |
summary | v.optional(v.string()) | An AI-generated summary of the call. |
salesforceRecordsAccessed | v.array(v.string()) | A list of Salesforce record IDs accessed during the call. |
salesforceRecordsModified | v.array(v.string()) | A list of Salesforce record IDs created or updated. |
Sources: convex/schema.ts:136-180
toolCalls
Logs every individual tool executed by the AI agent during a conversation for debugging and auditing.
| Field | Type | Description |
|---|---|---|
conversationId | v.string() | The parent conversation ID. Indexed. |
toolName | v.string() | The name of the tool that was called (e.g., search_salesforce). |
input | v.string() | JSON stringified input parameters for the tool. |
output | v.string() | JSON stringified output from the tool. |
success | v.boolean() | Whether the tool call executed successfully. |
durationMs | v.number() | The execution time of the tool in milliseconds. |
Sources: convex/schema.ts:193-205
agentActivities
A real-time feed of the AI agent's actions, used to provide live updates on the frontend dashboard. Records are temporary and expire.
| Field | Type | Description |
|---|---|---|
type | v.union(...) | The type of activity (e.g., "thinking", "searching", "updating"). |
message | v.string() | A human-readable description of the activity. |
recordId | v.optional(v.string()) | A related Salesforce record ID for navigation. |
conversationId | v.optional(v.string()) | The associated conversation. |
expiresAt | v.number() | Timestamp for auto-cleanup. Indexed. |
Sources: convex/schema.ts:208-226
Feature-Specific Tables
Deal Coaching
The coachingSessions table supports the "Deal Coach" feature, where sales reps can practice deal scenarios with an AI persona.
Sources: convex/dealCoach.ts, convex/schema.ts:228-261
coachingSessions
Stores a complete record of a deal coaching session, including the context, interactions, and AI-generated feedback.
| Field | Type | Description |
|---|---|---|
opportunityId | v.string() | The Salesforce Opportunity ID being practiced. Indexed. |
userId | v.optional(v.id("users")) | The user participating in the session. Indexed. |
personaType | v.string() | The type of AI persona (e.g., "skeptical_cfo"). |
dealContext | v.object({...}) | A snapshot of the deal's state (amount, stage, etc.). |
status | v.string() | The session status: "active" or "completed". |
interactions | v.array(v.object({...})) | An array of user and assistant messages. |
score | v.optional(v.number()) | An AI-generated score for the user's performance. |
feedback | v.optional(v.string()) | AI-generated qualitative feedback. |
Sources: convex/schema.ts:232-261
AI Agent Sessions
These tables maintain the context and history for chat-based interactions with the AI assistant across different channels (web, SMS, etc.).
Sources: convex/schema.ts:291-356
agentSessions
Maintains the state and context for a continuous conversation with a user.
| Field | Type | Description |
|---|---|---|
userId | v.id("users") | The user associated with the session. Indexed. |
channel | v.union(...) | The communication channel: "web", "voice", "sms", "api". |
status | v.union(...) | The session status: "active", "completed", "expired". |
contextSummary | v.optional(v.string()) | A compressed summary for long-running conversations. |
salesforceContext | v.optional(v.object({...})) | Cached Salesforce data relevant to the session. |
expiresAt | v.optional(v.number()) | Timestamp for auto-expiring inactive sessions. Indexed. |
Sources: convex/schema.ts:295-333
agentMessages
Stores individual messages within an agentSession.
| Field | Type | Description |
|---|---|---|
sessionId | v.id("agentSessions") | The parent session ID. Indexed. |
userId | v.id("users") | The user who sent or received the message. Indexed. |
role | v.union(...) | The message author: "user", "assistant", or "system". |
content | v.string() | The text content of the message. |
toolCalls | v.optional(v.array(v.object({...}))) | Metadata about any tool calls made by the assistant in this turn. |
Sources: convex/schema.ts:336-356
Slack Integration
This group of tables manages the integration with Slack, allowing for notifications and interactions within a user's Slack workspace.
Sources: convex/schema.ts:358-456
slackInstallations
Stores information for each Slack workspace that has installed the TalkCRM app.
| Field | Type | Description |
|---|---|---|
userId | v.id("users") | The TalkCRM user who installed the app. Indexed. |
teamId | v.string() | The Slack workspace ID. Indexed. |
botToken | v.string() | The xoxb- token for the bot to post messages. |
isActive | v.boolean() | Whether the installation is currently active. |
Sources: convex/schema.ts:362-376
slackChannelMappings
Configures which notifications are sent to which Slack channels for a given user.
| Field | Type | Description |
|---|---|---|
installationId | v.id("slackInstallations") | The parent Slack installation. Indexed. |
channelId | v.string() | The Slack channel ID. Indexed. |
purpose | v.union(...) | The type of notification for this channel (e.g., "deal_alerts", "call_summaries"). Indexed. |
isActive | v.boolean() | Whether this mapping is active. |
Sources: convex/schema.ts:379-401
Conclusion
The TalkCRM database schema, built on Convex, is a well-structured and comprehensive data model that underpins the entire application. It effectively manages multi-tenancy, stateful AI conversations, and complex integrations with third-party services like Salesforce and Slack. The use of typed schemas, indexes, and clear relationships between tables ensures data integrity and performance. The design heavily supports the core product features, from real-time activity feeds to providing rich, dynamic context for the AI, making it a critical component of the TalkCRM architecture.
Sources: convex/schema.ts, PROJECT_DESCRIPTION.md:65-92