This document describes the PostgreSQL database schema used by the Sim platform, including all tables, their relationships, and indexing strategies. The schema is managed using Drizzle ORM and supports multi-tenancy, normalized workflow storage, vector embeddings, and comprehensive execution logging.
For information about state management in the frontend, see State Management Architecture. For API endpoints that interact with these tables, see API Layer.
The Sim platform uses PostgreSQL with the pgvector extension for vector embeddings. The schema is defined using Drizzle ORM and managed through versioned migrations. As of the latest migration, there are 160+ migrations tracked in the journal.
Key Technologies:
drizzle-orm/pg-core)Sources: packages/db/schema.ts1-50 packages/db/migrations/meta/_journal.json1-30
The platform implements a three-tier tenancy model: User → Organization → Workspace → Workflow. This design supports both individual users and teams, with billing tracked at both user and workspace levels.
Key Tables:
| Table | Primary Key | Purpose | Key Foreign Keys |
|---|---|---|---|
user | id (text) | User accounts with email, name, Stripe customer ID | - |
session | id (text) | Active sessions with token-based auth | userId → user, activeOrganizationId → organization |
account | id (text) | OAuth accounts (Google, GitHub, etc.) | userId → user |
organization | id (text) | Top-level tenant for teams | - |
member | id (text) | User-organization membership with roles | userId → user, organizationId → organization |
workspace | id (text) | Primary isolation boundary | organizationId → organization |
workspaceEnvironment | id (text) | Environment variables per workspace | workspaceId → workspace |
workspaceBYOKKeys | id (text) | Bring-your-own-key API keys | workspaceId → workspace |
Sources: packages/db/schema.ts32-167 packages/db/schema.ts405-441
Workflows are stored in a normalized structure across multiple tables. This design enables efficient queries during collaborative editing and supports incremental updates without full-state serialization.
workflow packages/db/schema.ts139-168
name, description, coloruserId, workspaceId, folderIdisDeployed, deployedAt, isPublicApirunCount, lastRunAtsortOrder (within folder)variables (JSON, deprecated in favor of workflowVariables table)Indexes:
workflow_user_id_idx on userIdworkflow_workspace_id_idx on workspaceIdworkflow_user_workspace_idx on (userId, workspaceId)workflow_folder_sort_idx on (folderId, sortOrder)workflowBlocks packages/db/schema.ts170-203
id, workflowId, type, namepositionX, positionY, height (decimal for precision)enabled, locked, triggerMode, advancedModehorizontalHandles, isWidesubBlocks (jsonb), outputs (jsonb), data (jsonb)Indexes:
workflow_blocks_workflow_id_idx on workflowIdworkflow_blocks_type_idx on typeworkflowEdges packages/db/schema.ts205-235
id, workflowIdsourceBlockId, targetBlockIdsourceHandle, targetHandle (optional, for conditional routing)Indexes:
workflow_edges_workflow_id_idx on workflowIdworkflow_edges_workflow_source_idx on (workflowId, sourceBlockId)workflow_edges_workflow_target_idx on (workflowId, targetBlockId)workflowSubflows packages/db/schema.ts237-255
'loop' or 'parallel'config (jsonb) - stores loop config or parallel branch dataIndexes:
workflow_subflows_workflow_id_idx on workflowIdworkflow_subflows_workflow_type_idx on (workflowId, type)workflowFolder packages/db/schema.ts111-137
parentId (self-reference), workspaceIdname, color, isExpanded, sortOrderIndexes:
workflow_folder_user_idx on userIdworkflow_folder_workspace_parent_idx on (workspaceId, parentId)workflow_folder_parent_sort_idx on (parentId, sortOrder)Sources: packages/db/schema.ts139-255
The execution system uses immutable logs with versioned snapshots for reproducibility. Each execution references a state snapshot and tracks detailed timing, cost, and status information.
workflowExecutionSnapshots packages/db/schema.ts265-283
stateHash ensures identical states share snapshotsstateData (jsonb) contains full workflow state(workflowId, stateHash) prevents duplicatesIndexes:
workflow_snapshots_workflow_id_idx on workflowIdworkflow_snapshots_hash_idx on stateHashworkflow_snapshots_workflow_hash_idx unique on (workflowId, stateHash)workflow_snapshots_created_at_idx on createdAtworkflowExecutionLogs packages/db/schema.ts285-341
executionId (unique), id (primary key)status ('running', 'pending', 'completed', 'failed', 'cancelled')trigger ('api', 'webhook', 'schedule', 'manual', 'chat')startedAt, endedAt, totalDurationMscost (jsonb) - token counts and pricing by providerfiles (jsonb) - metadata for execution file uploadsstateSnapshotId, deploymentVersionId (nullable)Indexes:
workflow_execution_logs_workflow_id_idx on workflowIdworkflow_execution_logs_execution_id_unique unique on executionIdworkflow_execution_logs_workflow_started_at_idx on (workflowId, startedAt)workflow_execution_logs_workspace_started_at_idx on (workspaceId, startedAt)workflow_execution_logs_running_started_at_idx on startedAt WHERE status = 'running'pausedExecutions packages/db/schema.ts343-366
executionSnapshot (jsonb) - full execution state at pausepausePoints (jsonb) - block IDs where pausedtotalPauseCount, resumedCountstatus, pausedAt, updatedAt, expiresAtmetadata (jsonb) - custom pause contextIndexes:
paused_executions_workflow_id_idx on workflowIdpaused_executions_status_idx on statuspaused_executions_execution_id_unique unique on executionIdresumeQueue packages/db/schema.ts368-393
pausedExecutionId, parentExecutionId, newExecutionIdcontextId - identifies the pause pointresumeInput (jsonb) - data provided on resumestatus ('pending', 'claimed', 'completed', 'failed')queuedAt, claimedAt, completedAtIndexes:
resume_queue_parent_status_idx on (parentExecutionId, status, queuedAt)resume_queue_new_execution_idx on newExecutionIdSources: packages/db/schema.ts265-393
Workflows can be deployed as APIs, webhooks, scheduled jobs, chat interfaces, MCP tools, or templates. The deployment system tracks versions and manages triggers.
workflowDeploymentVersion packages/db/schema.ts
version (integer), isActive (boolean)name, descriptionstateSnapshotId - frozen workflow statecreatedAt, deployedAt, undeployedAtIndexes:
(workflowId, version)(workflowId, isActive)webhook packages/db/schema.ts524-565
path (unique per deployment)provider (e.g., 'whatsapp', 'github'), providerConfig (jsonb)blockId - which trigger block receives webhookcredentialSetId - for provider subscriptionsisActive, failedCount, lastFailedAtIndexes:
path_deployment_unique unique on (path, deploymentVersionId)idx_webhook_on_workflow_id_block_id on (workflowId, blockId)webhook_workflow_deployment_idx on (workflowId, deploymentVersionId)webhook_credential_set_id_idx on credentialSetIdworkflowSchedule packages/db/schema.ts485-522
cronExpression, timezone, nextRunAtlastRanAt, lastQueuedAtstatus ('active', 'disabled'), failedCount, lastFailedAttriggerType ('manual', 'webhook', 'schedule')Indexes:
workflow_schedule_workflow_block_deployment_unique unique on (workflowId, blockId, deploymentVersionId)workflow_schedule_workflow_deployment_idx on (workflowId, deploymentVersionId)apiKey packages/db/schema.ts655-680
type ('personal', 'workspace')key (unique, hashed)name, createdBy, lastUsed, expiresAtworkspaceId, personal keys must notIndexes:
api_key_workspace_type_idx on (workspaceId, type)api_key_user_type_idx on (userId, type)chatDeployment packages/db/schema.ts
identifier (unique URL slug)title, description, customizations (jsonb)authType ('public', 'password', 'email', 'sso'), allowedEmailsoutputConfigs (jsonb) - which block outputs to displayworkflowTemplate packages/db/schema.ts
status ('pending', 'approved', 'rejected')views, starstags (array), categoryTags (array)Sources: packages/db/schema.ts485-680
The credential system supports OAuth flows for 30+ providers with automatic token refresh, webhook subscriptions, and pending draft management during auth flows.
credential packages/db/schema.ts
service (e.g., 'gmail', 'slack', 'airtable')userId, workspaceId (nullable - personal vs workspace)encryptedAccessToken, encryptedRefreshTokenexpiresAt, refreshTokenExpiresAtmetadata (jsonb) - email, channel names, etc.webhookSubscriptionId, webhookUrlIndexes:
(userId, service, workspaceId)(workspaceId, service) for workspace credentialscredentialSet packages/db/schema.ts
name - user-friendly labelworkflowId, workspaceIdcredentialId per setIndexes:
(workflowId, credentialId)workspaceIdpendingCredentialDraft packages/db/schema.ts
tempData (jsonb) - intermediate OAuth dataexpiresAt - auto-cleanup after 1 houraccount packages/db/schema.ts67-93
providerId, accountIdIndexes:
account_user_id_idx on userIdidx_account_on_account_id_provider_id on (accountId, providerId)Sources: packages/db/schema.ts67-93
The knowledge base system stores documents with pgvector embeddings for semantic search. Documents are chunked with metadata for context-aware retrieval.
workflowKnowledge packages/db/schema.ts
fileName, fileUrl, fileSize, mimeTypeprocessingStatus ('pending', 'processing', 'completed', 'failed')chunkCount, uploadedByworkspaceIdIndexes:
workspaceId(workspaceId, processingStatus)workflowKnowledgeChunk packages/db/schema.ts
content (text), chunkIndexembedding - vector(1536) using pgvectormetadata (jsonb) - page numbers, sections, etc.knowledgeIdIndexes:
knowledgeIdembedding for fast similarity searchworkflowKnowledgeAssignment packages/db/schema.ts
(workflowId, knowledgeId)Vector Search Query Pattern:
Sources: packages/db/schema.ts
Usage tracking operates at both user and workspace levels, with separate credit balances for Pro users and team workspaces.
userStats packages/db/schema.ts687-727
totalManualExecutions, totalApiCalls, totalWebhookTriggerstotalScheduledExecutions, totalChatExecutionstotalMcpExecutions, totalA2aExecutionstotalCost, totalTokensUsedcurrentPeriodCost, lastPeriodCostbilledOverageThisPeriod - threshold billing amountsproPeriodCostSnapshot - prevents double-billing when joining teamstotalCopilotCost, currentPeriodCopilotCosttotalCopilotTokens, totalCopilotCallstotalMcpCopilotCalls, totalMcpCopilotCostcreditBalance - pre-purchased credits for Pro userscurrentUsageLimit - spending capstorageUsedBytesbillingBlocked, billingBlockedReason ('payment_failed', 'dispute')workspaceUsage packages/db/schema.ts
userStats but at workspace levelreferralCampaigns packages/db/schema.ts729-747
name, code, UTM parametersbonusCreditAmountisActivereferralAttribution packages/db/schema.ts749-782
userId, organizationId, campaignIdutmSource, utmMedium, utmCampaign, utmContentreferrerUrl, landingPageSources: packages/db/schema.ts687-782
Workspace-level notifications can be sent via webhook, email, or Slack based on execution events.
workspaceNotificationSubscription packages/db/schema.ts576-620
notificationType ('webhook', 'email', 'slack')workflowIds (array), allWorkflows (boolean)levelFilter (array) - 'info', 'error'triggerFilter (array) - 'api', 'webhook', 'schedule', 'manual', 'chat'includeFinalOutput, includeTraceSpansincludeRateLimits, includeUsageDatawebhookConfig (jsonb) - URL, headers, retry configemailRecipients (array)slackConfig (jsonb) - channel, authalertConfig (jsonb) - threshold alertsactive, lastAlertAtIndexes:
workspace_notification_workspace_id_idx on workspaceIdworkspace_notification_active_idx on activeworkspace_notification_type_idx on notificationTypeworkspaceNotificationDelivery packages/db/schema.ts622-653
status ('pending', 'in_progress', 'success', 'failed')attempts, lastAttemptAt, nextAttemptAtresponseStatus, responseBody, errorMessagesubscriptionId, executionIdIndexes:
workspace_notification_delivery_subscription_id_idx on subscriptionIdworkspace_notification_delivery_execution_id_idx on executionIdworkspace_notification_delivery_status_idx on statusworkspace_notification_delivery_next_attempt_idx on nextAttemptAtSources: packages/db/schema.ts576-653
The Model Context Protocol (MCP) and Agent-to-Agent (A2A) systems allow workflows to be exposed as tools in other systems.
workflowMcpServer packages/db/schema.ts
name, url, descriptionserverConfig (jsonb) - authentication, transportworkspaceId, createdByworkflowMcpTool packages/db/schema.ts
toolName, toolDescriptionparameterSchema (jsonb) - JSON Schema for inputsserverId, workflowIdworkspaceIda2aAgent packages/db/schema.ts
agentId, name, descriptionconfig (jsonb) - prompts, capabilitiesisPublished, publishedAtworkflowId, workspaceIdSources: Based on usage in apps/sim/hooks/queries/workflow-mcp-servers.ts21-28 apps/sim/app/workspace/[workspaceId]/w/[workflowId]/components/panel/components/deploy/components/deploy-modal/components/mcp/mcp.tsx:23-28
Users can define JavaScript functions as custom tools for use in agent blocks.
customTools packages/db/schema.ts784-803
title, schema (JSON Schema), code (JavaScript)workspaceId, userId(workspaceId, title) - one tool name per workspaceIndexes:
custom_tools_workspace_id_idx on workspaceIdcustom_tools_workspace_title_unique unique on (workspaceId, title)Sources: packages/db/schema.ts784-803
settings packages/db/schema.ts443-483
theme, autoConnect, snapToGridSize, showActionBartelemetryEnabled, errorNotificationsEnabledemailPreferences (jsonb), billingUsageNotificationsEnabledcopilotEnabledModels (jsonb) - per-model enable/disablecopilotAutoAllowedTools (jsonb) - tools that skip confirmationshowTrainingControls, superUserModeEnabledenvironment packages/db/schema.ts395-403
variables (json)Sources: packages/db/schema.ts395-483
Composite Indexes for Multi-Column Queries:
(userId, workspaceId) on workflow - common access pattern(workflowId, startedAt) on execution logs - time-series queries(workspaceId, parentId) on folders - hierarchical queriesPartial Indexes:
workflow_execution_logs_running_started_at_idx on startedAt WHERE status = 'running' - efficient monitoringUnique Constraints for Deduplication:
(workflowId, stateHash) on snapshots - prevent duplicate state storage(path, deploymentVersionId) on webhooks - unique routingexecutionId on execution logs - idempotencyForeign Key Indexes:
Vector Indexes:
Finding Active Executions:
Workflow Execution History:
Workspace Usage Aggregation:
Sources: packages/db/schema.ts61-64 packages/db/schema.ts162-167 packages/db/schema.ts316-340
Migrations are managed through Drizzle Kit with a versioned journal system. Each migration is tracked in _journal.json with a timestamp, tag, and breakpoint flag.
Migration Journal: packages/db/migrations/meta/_journal.json1-1126
NNNN_adjective_character (e.g., 0000_careless_black_knight)Key Migration Events:
Rollback Support: Each migration includes both up and down SQL scripts in separate files.
Refresh this wiki