import { type SQL, sql } from 'drizzle-orm' import { bigint, boolean, check, customType, decimal, doublePrecision, index, integer, json, jsonb, pgEnum, pgTable, text, timestamp, uniqueIndex, uuid, vector, } from 'drizzle-orm/pg-core' import { DEFAULT_FREE_CREDITS, TAG_SLOTS } from './constants' // Custom tsvector type for full-text search export const tsvector = customType<{ data: string }>({ dataType() { return `tsvector` }, }) export const user = pgTable('user', { id: text('id').primaryKey(), name: text('name').notNull(), email: text('email').notNull().unique(), normalizedEmail: text('normalized_email').unique(), emailVerified: boolean('email_verified').notNull(), image: text('image'), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), stripeCustomerId: text('stripe_customer_id'), role: text('role').default('user'), banned: boolean('banned').default(false), banReason: text('ban_reason'), banExpires: timestamp('ban_expires'), }) export const session = pgTable( 'session', { id: text('id').primaryKey(), expiresAt: timestamp('expires_at').notNull(), token: text('token').notNull().unique(), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), ipAddress: text('ip_address'), userAgent: text('user_agent'), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), activeOrganizationId: text('active_organization_id').references(() => organization.id, { onDelete: 'set null', }), impersonatedBy: text('impersonated_by'), }, (table) => ({ userIdIdx: index('session_user_id_idx').on(table.userId), tokenIdx: index('session_token_idx').on(table.token), }) ) export const account = pgTable( 'account', { id: text('id').primaryKey(), accountId: text('account_id').notNull(), providerId: text('provider_id').notNull(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), accessToken: text('access_token'), refreshToken: text('refresh_token'), idToken: text('id_token'), accessTokenExpiresAt: timestamp('access_token_expires_at'), refreshTokenExpiresAt: timestamp('refresh_token_expires_at'), scope: text('scope'), password: text('password'), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), }, (table) => ({ userIdIdx: index('account_user_id_idx').on(table.userId), accountProviderIdx: index('idx_account_on_account_id_provider_id').on( table.accountId, table.providerId ), }) ) export const verification = pgTable( 'verification', { id: text('id').primaryKey(), identifier: text('identifier').notNull(), value: text('value').notNull(), expiresAt: timestamp('expires_at').notNull(), createdAt: timestamp('created_at'), updatedAt: timestamp('updated_at'), }, (table) => ({ identifierIdx: index('verification_identifier_idx').on(table.identifier), expiresAtIdx: index('verification_expires_at_idx').on(table.expiresAt), }) ) export const workflowFolder = pgTable( 'workflow_folder', { id: text('id').primaryKey(), name: text('name').notNull(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), parentId: text('parent_id'), // Self-reference will be handled by foreign key constraint color: text('color').default('#6B7280'), isExpanded: boolean('is_expanded').notNull().default(true), sortOrder: integer('sort_order').notNull().default(0), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), archivedAt: timestamp('archived_at'), }, (table) => ({ userIdx: index('workflow_folder_user_idx').on(table.userId), workspaceParentIdx: index('workflow_folder_workspace_parent_idx').on( table.workspaceId, table.parentId ), parentSortIdx: index('workflow_folder_parent_sort_idx').on(table.parentId, table.sortOrder), archivedAtIdx: index('workflow_folder_archived_at_idx').on(table.archivedAt), workspaceArchivedAtPartialIdx: index('workflow_folder_workspace_archived_partial_idx') .on(table.workspaceId, table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), }) ) export const workflow = pgTable( 'workflow', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), folderId: text('folder_id').references(() => workflowFolder.id, { onDelete: 'set null' }), sortOrder: integer('sort_order').notNull().default(0), name: text('name').notNull(), description: text('description'), color: text('color').notNull().default('#3972F6'), lastSynced: timestamp('last_synced').notNull(), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), isDeployed: boolean('is_deployed').notNull().default(false), deployedAt: timestamp('deployed_at'), isPublicApi: boolean('is_public_api').notNull().default(false), runCount: integer('run_count').notNull().default(0), lastRunAt: timestamp('last_run_at'), variables: json('variables').default('{}'), archivedAt: timestamp('archived_at'), }, (table) => ({ userIdIdx: index('workflow_user_id_idx').on(table.userId), workspaceIdIdx: index('workflow_workspace_id_idx').on(table.workspaceId), userWorkspaceIdx: index('workflow_user_workspace_idx').on(table.userId, table.workspaceId), workspaceFolderNameUnique: uniqueIndex('workflow_workspace_folder_name_active_unique') .on(table.workspaceId, sql`coalesce(${table.folderId}, '')`, table.name) .where(sql`${table.archivedAt} IS NULL`), folderSortIdx: index('workflow_folder_sort_idx').on(table.folderId, table.sortOrder), archivedAtIdx: index('workflow_archived_at_idx').on(table.archivedAt), workspaceArchivedAtPartialIdx: index('workflow_workspace_archived_partial_idx') .on(table.workspaceId, table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), }) ) export const workflowBlocks = pgTable( 'workflow_blocks', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), type: text('type').notNull(), // 'starter', 'agent', 'api', 'function' name: text('name').notNull(), positionX: decimal('position_x').notNull(), positionY: decimal('position_y').notNull(), enabled: boolean('enabled').notNull().default(true), horizontalHandles: boolean('horizontal_handles').notNull().default(true), isWide: boolean('is_wide').notNull().default(false), advancedMode: boolean('advanced_mode').notNull().default(false), triggerMode: boolean('trigger_mode').notNull().default(false), locked: boolean('locked').notNull().default(false), height: decimal('height').notNull().default('0'), subBlocks: jsonb('sub_blocks').notNull().default('{}'), outputs: jsonb('outputs').notNull().default('{}'), data: jsonb('data').default('{}'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workflowIdIdx: index('workflow_blocks_workflow_id_idx').on(table.workflowId), typeIdx: index('workflow_blocks_type_idx').on(table.type), }) ) export const workflowEdges = pgTable( 'workflow_edges', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), sourceBlockId: text('source_block_id') .notNull() .references(() => workflowBlocks.id, { onDelete: 'cascade' }), targetBlockId: text('target_block_id') .notNull() .references(() => workflowBlocks.id, { onDelete: 'cascade' }), sourceHandle: text('source_handle'), targetHandle: text('target_handle'), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ workflowIdIdx: index('workflow_edges_workflow_id_idx').on(table.workflowId), workflowSourceIdx: index('workflow_edges_workflow_source_idx').on( table.workflowId, table.sourceBlockId ), workflowTargetIdx: index('workflow_edges_workflow_target_idx').on( table.workflowId, table.targetBlockId ), }) ) export const workflowSubflows = pgTable( 'workflow_subflows', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), type: text('type').notNull(), // 'loop' or 'parallel' config: jsonb('config').notNull().default('{}'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workflowIdIdx: index('workflow_subflows_workflow_id_idx').on(table.workflowId), workflowTypeIdx: index('workflow_subflows_workflow_type_idx').on(table.workflowId, table.type), }) ) export const waitlist = pgTable('waitlist', { id: text('id').primaryKey(), email: text('email').notNull().unique(), status: text('status').notNull().default('pending'), // pending, approved, rejected createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }) export const workflowExecutionSnapshots = pgTable( 'workflow_execution_snapshots', { id: text('id').primaryKey(), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'set null' }), stateHash: text('state_hash').notNull(), stateData: jsonb('state_data').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ workflowIdIdx: index('workflow_snapshots_workflow_id_idx').on(table.workflowId), stateHashIdx: index('workflow_snapshots_hash_idx').on(table.stateHash), workflowHashUnique: uniqueIndex('workflow_snapshots_workflow_hash_idx').on( table.workflowId, table.stateHash ), createdAtIdx: index('workflow_snapshots_created_at_idx').on(table.createdAt), }) ) export const workflowExecutionLogs = pgTable( 'workflow_execution_logs', { id: text('id').primaryKey(), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'set null' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), executionId: text('execution_id').notNull(), stateSnapshotId: text('state_snapshot_id') .notNull() .references(() => workflowExecutionSnapshots.id), deploymentVersionId: text('deployment_version_id').references( () => workflowDeploymentVersion.id, { onDelete: 'set null' } ), level: text('level').notNull(), // 'info' | 'error' status: text('status').notNull().default('running'), // 'running' | 'pending' | 'completed' | 'failed' | 'cancelled' trigger: text('trigger').notNull(), // 'api' | 'webhook' | 'schedule' | 'manual' | 'chat' startedAt: timestamp('started_at').notNull(), endedAt: timestamp('ended_at'), totalDurationMs: integer('total_duration_ms'), executionData: jsonb('execution_data').notNull().default('{}'), cost: jsonb('cost'), files: jsonb('files'), // File metadata for execution files createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ workflowIdIdx: index('workflow_execution_logs_workflow_id_idx').on(table.workflowId), stateSnapshotIdIdx: index('workflow_execution_logs_state_snapshot_id_idx').on( table.stateSnapshotId ), deploymentVersionIdIdx: index('workflow_execution_logs_deployment_version_id_idx').on( table.deploymentVersionId ), triggerIdx: index('workflow_execution_logs_trigger_idx').on(table.trigger), levelIdx: index('workflow_execution_logs_level_idx').on(table.level), startedAtIdx: index('workflow_execution_logs_started_at_idx').on(table.startedAt), executionIdUnique: uniqueIndex('workflow_execution_logs_execution_id_unique').on( table.executionId ), workflowStartedAtIdx: index('workflow_execution_logs_workflow_started_at_idx').on( table.workflowId, table.startedAt ), workspaceStartedAtIdx: index('workflow_execution_logs_workspace_started_at_idx').on( table.workspaceId, table.startedAt ), runningStartedAtIdx: index('workflow_execution_logs_running_started_at_idx') .on(table.startedAt) .where(sql`status = 'running'`), }) ) export const pausedExecutions = pgTable( 'paused_executions', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), executionId: text('execution_id').notNull(), executionSnapshot: jsonb('execution_snapshot').notNull(), pausePoints: jsonb('pause_points').notNull(), totalPauseCount: integer('total_pause_count').notNull(), resumedCount: integer('resumed_count').notNull().default(0), status: text('status').notNull().default('paused'), metadata: jsonb('metadata').notNull().default(sql`'{}'::jsonb`), pausedAt: timestamp('paused_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), expiresAt: timestamp('expires_at'), }, (table) => ({ workflowIdx: index('paused_executions_workflow_id_idx').on(table.workflowId), statusIdx: index('paused_executions_status_idx').on(table.status), executionUnique: uniqueIndex('paused_executions_execution_id_unique').on(table.executionId), }) ) export const resumeQueue = pgTable( 'resume_queue', { id: text('id').primaryKey(), pausedExecutionId: text('paused_execution_id') .notNull() .references(() => pausedExecutions.id, { onDelete: 'cascade' }), parentExecutionId: text('parent_execution_id').notNull(), newExecutionId: text('new_execution_id').notNull(), contextId: text('context_id').notNull(), resumeInput: jsonb('resume_input'), status: text('status').notNull().default('pending'), queuedAt: timestamp('queued_at').notNull().defaultNow(), claimedAt: timestamp('claimed_at'), completedAt: timestamp('completed_at'), failureReason: text('failure_reason'), }, (table) => ({ parentStatusIdx: index('resume_queue_parent_status_idx').on( table.parentExecutionId, table.status, table.queuedAt ), newExecutionIdx: index('resume_queue_new_execution_idx').on(table.newExecutionId), }) ) export const environment = pgTable('environment', { id: text('id').primaryKey(), // Use the user id as the key userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }) .unique(), // One environment per user variables: json('variables').notNull(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }) export const workspaceEnvironment = pgTable( 'workspace_environment', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), variables: json('variables').notNull().default('{}'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceUnique: uniqueIndex('workspace_environment_workspace_unique').on(table.workspaceId), }) ) export const workspaceBYOKKeys = pgTable( 'workspace_byok_keys', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), providerId: text('provider_id').notNull(), encryptedApiKey: text('encrypted_api_key').notNull(), createdBy: text('created_by').references(() => user.id, { onDelete: 'set null' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceProviderUnique: uniqueIndex('workspace_byok_provider_unique').on( table.workspaceId, table.providerId ), workspaceIdx: index('workspace_byok_workspace_idx').on(table.workspaceId), }) ) export const settings = pgTable('settings', { id: text('id').primaryKey(), // Use the user id as the key userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }) .unique(), // One settings record per user // General settings theme: text('theme').notNull().default('system'), autoConnect: boolean('auto_connect').notNull().default(true), // Privacy settings telemetryEnabled: boolean('telemetry_enabled').notNull().default(true), // Email preferences emailPreferences: json('email_preferences').notNull().default('{}'), // Billing usage notifications preference billingUsageNotificationsEnabled: boolean('billing_usage_notifications_enabled') .notNull() .default(true), // UI preferences showTrainingControls: boolean('show_training_controls').notNull().default(false), superUserModeEnabled: boolean('super_user_mode_enabled').notNull().default(true), // Notification preferences errorNotificationsEnabled: boolean('error_notifications_enabled').notNull().default(true), // Canvas preferences snapToGridSize: integer('snap_to_grid_size').notNull().default(0), // 0 = off, 10-50 = grid size showActionBar: boolean('show_action_bar').notNull().default(true), // Copilot preferences - maps model_id to enabled/disabled boolean copilotEnabledModels: jsonb('copilot_enabled_models').notNull().default('{}'), // Copilot auto-allowed integration tools - array of tool IDs that can run without confirmation copilotAutoAllowedTools: jsonb('copilot_auto_allowed_tools').notNull().default('[]'), // Workspace navigation lastActiveWorkspaceId: text('last_active_workspace_id'), updatedAt: timestamp('updated_at').notNull().defaultNow(), }) export const workflowSchedule = pgTable( 'workflow_schedule', { id: text('id').primaryKey(), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'cascade' }), deploymentVersionId: text('deployment_version_id').references( () => workflowDeploymentVersion.id, { onDelete: 'cascade' } ), blockId: text('block_id'), cronExpression: text('cron_expression'), nextRunAt: timestamp('next_run_at'), lastRanAt: timestamp('last_ran_at'), lastQueuedAt: timestamp('last_queued_at'), triggerType: text('trigger_type').notNull(), // "manual", "webhook", "schedule" timezone: text('timezone').notNull().default('UTC'), failedCount: integer('failed_count').notNull().default(0), status: text('status').notNull().default('active'), // 'active', 'disabled', or 'completed' lastFailedAt: timestamp('last_failed_at'), sourceType: text('source_type').notNull().default('workflow'), // 'workflow' or 'job' jobTitle: text('job_title'), prompt: text('prompt'), lifecycle: text('lifecycle').notNull().default('persistent'), // 'persistent' or 'until_complete' successCondition: text('success_condition'), maxRuns: integer('max_runs'), runCount: integer('run_count').notNull().default(0), sourceChatId: text('source_chat_id'), sourceTaskName: text('source_task_name'), sourceUserId: text('source_user_id').references(() => user.id, { onDelete: 'cascade' }), sourceWorkspaceId: text('source_workspace_id').references(() => workspace.id, { onDelete: 'cascade', }), jobHistory: jsonb('job_history').$type>(), archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => { return { workflowBlockUnique: uniqueIndex('workflow_schedule_workflow_block_deployment_unique') .on(table.workflowId, table.blockId, table.deploymentVersionId) .where(sql`${table.archivedAt} IS NULL`), workflowDeploymentIdx: index('workflow_schedule_workflow_deployment_idx').on( table.workflowId, table.deploymentVersionId ), archivedAtPartialIdx: index('workflow_schedule_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), } } ) export const jobExecutionLogs = pgTable( 'job_execution_logs', { id: text('id').primaryKey(), scheduleId: text('schedule_id').references(() => workflowSchedule.id, { onDelete: 'set null' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), executionId: text('execution_id').notNull(), level: text('level').notNull(), status: text('status').notNull().default('running'), trigger: text('trigger').notNull(), startedAt: timestamp('started_at').notNull(), endedAt: timestamp('ended_at'), totalDurationMs: integer('total_duration_ms'), executionData: jsonb('execution_data').notNull().default('{}'), cost: jsonb('cost'), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ scheduleIdIdx: index('job_execution_logs_schedule_id_idx').on(table.scheduleId), workspaceStartedAtIdx: index('job_execution_logs_workspace_started_at_idx').on( table.workspaceId, table.startedAt ), executionIdUnique: uniqueIndex('job_execution_logs_execution_id_unique').on(table.executionId), triggerIdx: index('job_execution_logs_trigger_idx').on(table.trigger), }) ) export const webhook = pgTable( 'webhook', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), deploymentVersionId: text('deployment_version_id').references( () => workflowDeploymentVersion.id, { onDelete: 'cascade' } ), blockId: text('block_id'), path: text('path').notNull(), provider: text('provider'), // e.g., "whatsapp", "github", etc. providerConfig: json('provider_config'), // Store provider-specific configuration isActive: boolean('is_active').notNull().default(true), failedCount: integer('failed_count').default(0), // Track consecutive failures lastFailedAt: timestamp('last_failed_at'), // When the webhook last failed credentialSetId: text('credential_set_id').references(() => credentialSet.id, { onDelete: 'set null', }), // For credential set webhooks - enables efficient queries archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => { return { // Ensure webhook paths are unique per deployment version pathIdx: uniqueIndex('path_deployment_unique') .on(table.path, table.deploymentVersionId) .where(sql`${table.archivedAt} IS NULL`), // Optimize queries for webhooks by workflow and block workflowBlockIdx: index('idx_webhook_on_workflow_id_block_id').on( table.workflowId, table.blockId ), workflowDeploymentIdx: index('webhook_workflow_deployment_idx').on( table.workflowId, table.deploymentVersionId ), // Optimize queries for credential set webhooks credentialSetIdIdx: index('webhook_credential_set_id_idx').on(table.credentialSetId), archivedAtPartialIdx: index('webhook_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), } } ) export const notificationTypeEnum = pgEnum('notification_type', ['webhook', 'email', 'slack']) export const notificationDeliveryStatusEnum = pgEnum('notification_delivery_status', [ 'pending', 'in_progress', 'success', 'failed', ]) export const workspaceNotificationSubscription = pgTable( 'workspace_notification_subscription', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), notificationType: notificationTypeEnum('notification_type').notNull(), workflowIds: text('workflow_ids').array().notNull().default(sql`'{}'::text[]`), allWorkflows: boolean('all_workflows').notNull().default(false), levelFilter: text('level_filter') .array() .notNull() .default(sql`ARRAY['info', 'error']::text[]`), triggerFilter: text('trigger_filter') .array() .notNull() .default(sql`ARRAY['api', 'webhook', 'schedule', 'manual', 'chat']::text[]`), includeFinalOutput: boolean('include_final_output').notNull().default(false), includeTraceSpans: boolean('include_trace_spans').notNull().default(false), includeRateLimits: boolean('include_rate_limits').notNull().default(false), includeUsageData: boolean('include_usage_data').notNull().default(false), // Channel-specific configuration webhookConfig: jsonb('webhook_config'), emailRecipients: text('email_recipients').array(), slackConfig: jsonb('slack_config'), // Alert rule configuration (if null, sends on every execution) alertConfig: jsonb('alert_config'), lastAlertAt: timestamp('last_alert_at'), active: boolean('active').notNull().default(true), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceIdIdx: index('workspace_notification_workspace_id_idx').on(table.workspaceId), activeIdx: index('workspace_notification_active_idx').on(table.active), typeIdx: index('workspace_notification_type_idx').on(table.notificationType), }) ) export const workspaceNotificationDelivery = pgTable( 'workspace_notification_delivery', { id: text('id').primaryKey(), subscriptionId: text('subscription_id') .notNull() .references(() => workspaceNotificationSubscription.id, { onDelete: 'cascade' }), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), executionId: text('execution_id').notNull(), status: notificationDeliveryStatusEnum('status').notNull().default('pending'), attempts: integer('attempts').notNull().default(0), lastAttemptAt: timestamp('last_attempt_at'), nextAttemptAt: timestamp('next_attempt_at'), responseStatus: integer('response_status'), responseBody: text('response_body'), errorMessage: text('error_message'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ subscriptionIdIdx: index('workspace_notification_delivery_subscription_id_idx').on( table.subscriptionId ), executionIdIdx: index('workspace_notification_delivery_execution_id_idx').on(table.executionId), statusIdx: index('workspace_notification_delivery_status_idx').on(table.status), nextAttemptIdx: index('workspace_notification_delivery_next_attempt_idx').on( table.nextAttemptAt ), }) ) export const apiKey = pgTable( 'api_key', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), // Only set for workspace keys createdBy: text('created_by').references(() => user.id, { onDelete: 'set null' }), name: text('name').notNull(), key: text('key').notNull().unique(), keyHash: text('key_hash'), type: text('type').notNull().default('personal'), lastUsed: timestamp('last_used'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), expiresAt: timestamp('expires_at'), }, (table) => ({ workspaceTypeCheck: check( 'workspace_type_check', sql`(type = 'workspace' AND workspace_id IS NOT NULL) OR (type = 'personal' AND workspace_id IS NULL)` ), workspaceTypeIdx: index('api_key_workspace_type_idx').on(table.workspaceId, table.type), userTypeIdx: index('api_key_user_type_idx').on(table.userId, table.type), keyHashIdx: uniqueIndex('api_key_key_hash_idx').on(table.keyHash), }) ) export const billingBlockedReasonEnum = pgEnum('billing_blocked_reason', [ 'payment_failed', 'dispute', ]) export const userStats = pgTable('user_stats', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }) .unique(), // One record per user totalManualExecutions: integer('total_manual_executions').notNull().default(0), totalApiCalls: integer('total_api_calls').notNull().default(0), totalWebhookTriggers: integer('total_webhook_triggers').notNull().default(0), totalScheduledExecutions: integer('total_scheduled_executions').notNull().default(0), totalChatExecutions: integer('total_chat_executions').notNull().default(0), totalMcpExecutions: integer('total_mcp_executions').notNull().default(0), totalA2aExecutions: integer('total_a2a_executions').notNull().default(0), totalTokensUsed: bigint('total_tokens_used', { mode: 'number' }).notNull().default(0), totalCost: decimal('total_cost').notNull().default('0'), currentUsageLimit: decimal('current_usage_limit').default(DEFAULT_FREE_CREDITS.toString()), // Default $5 (1,000 credits) for free plan, null for team/enterprise usageLimitUpdatedAt: timestamp('usage_limit_updated_at').defaultNow(), // Billing period tracking currentPeriodCost: decimal('current_period_cost').notNull().default('0'), // Usage in current billing period lastPeriodCost: decimal('last_period_cost').default('0'), // Usage from previous billing period billedOverageThisPeriod: decimal('billed_overage_this_period').notNull().default('0'), // Amount of overage already billed via threshold billing // Pro usage snapshot when joining a team (to prevent double-billing) proPeriodCostSnapshot: decimal('pro_period_cost_snapshot').default('0'), // Snapshot of Pro usage when joining team proPeriodCostSnapshotAt: timestamp('pro_period_cost_snapshot_at'), // When the snapshot was captured (= join moment). Used to cap daily-refresh computation so post-join refresh isn't deducted from pre-join personal Pro usage (and vice-versa for the org's pooled refresh). // Pre-purchased credits (for Pro users only) creditBalance: decimal('credit_balance').notNull().default('0'), // Copilot usage tracking totalCopilotCost: decimal('total_copilot_cost').notNull().default('0'), currentPeriodCopilotCost: decimal('current_period_copilot_cost').notNull().default('0'), lastPeriodCopilotCost: decimal('last_period_copilot_cost').default('0'), totalCopilotTokens: bigint('total_copilot_tokens', { mode: 'number' }).notNull().default(0), totalCopilotCalls: integer('total_copilot_calls').notNull().default(0), // MCP Copilot usage tracking totalMcpCopilotCalls: integer('total_mcp_copilot_calls').notNull().default(0), totalMcpCopilotCost: decimal('total_mcp_copilot_cost').notNull().default('0'), currentPeriodMcpCopilotCost: decimal('current_period_mcp_copilot_cost').notNull().default('0'), // Storage tracking (for free/pro users) storageUsedBytes: bigint('storage_used_bytes', { mode: 'number' }).notNull().default(0), lastActive: timestamp('last_active').notNull().defaultNow(), billingBlocked: boolean('billing_blocked').notNull().default(false), billingBlockedReason: billingBlockedReasonEnum('billing_blocked_reason'), }) export const customTools = pgTable( 'custom_tools', { id: text('id').primaryKey(), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), userId: text('user_id').references(() => user.id, { onDelete: 'set null' }), title: text('title').notNull(), schema: json('schema').notNull(), code: text('code').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceIdIdx: index('custom_tools_workspace_id_idx').on(table.workspaceId), workspaceTitleUnique: uniqueIndex('custom_tools_workspace_title_unique').on( table.workspaceId, table.title ), }) ) export const skill = pgTable( 'skill', { id: text('id').primaryKey(), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), userId: text('user_id').references(() => user.id, { onDelete: 'set null' }), name: text('name').notNull(), description: text('description').notNull(), content: text('content').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceNameUnique: uniqueIndex('skill_workspace_name_unique').on( table.workspaceId, table.name ), }) ) export const subscription = pgTable( 'subscription', { id: text('id').primaryKey(), plan: text('plan').notNull(), referenceId: text('reference_id').notNull(), stripeCustomerId: text('stripe_customer_id'), stripeSubscriptionId: text('stripe_subscription_id'), status: text('status'), periodStart: timestamp('period_start'), periodEnd: timestamp('period_end'), cancelAtPeriodEnd: boolean('cancel_at_period_end'), seats: integer('seats'), trialStart: timestamp('trial_start'), trialEnd: timestamp('trial_end'), metadata: json('metadata'), }, (table) => ({ referenceStatusIdx: index('subscription_reference_status_idx').on( table.referenceId, table.status ), enterpriseMetadataCheck: check( 'check_enterprise_metadata', sql`plan != 'enterprise' OR metadata IS NOT NULL` ), }) ) export const rateLimitBucket = pgTable('rate_limit_bucket', { key: text('key').primaryKey(), tokens: decimal('tokens').notNull(), lastRefillAt: timestamp('last_refill_at').notNull(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }) export const chat = pgTable( 'chat', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), identifier: text('identifier').notNull(), title: text('title').notNull(), description: text('description'), isActive: boolean('is_active').notNull().default(true), customizations: json('customizations').default('{}'), // For UI customization options // Authentication options authType: text('auth_type').notNull().default('public'), // 'public', 'password', 'email', 'sso' password: text('password'), // Stored hashed, populated when authType is 'password' allowedEmails: json('allowed_emails').default('[]'), // Array of allowed emails or domains when authType is 'email' or 'sso' // Output configuration outputConfigs: json('output_configs').default('[]'), // Array of {blockId, path} objects archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => { return { // Ensure identifiers are unique identifierIdx: uniqueIndex('identifier_idx') .on(table.identifier) .where(sql`${table.archivedAt} IS NULL`), archivedAtPartialIdx: index('chat_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), } } ) export const form = pgTable( 'form', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), identifier: text('identifier').notNull(), title: text('title').notNull(), description: text('description'), isActive: boolean('is_active').notNull().default(true), // UI/UX Customizations // { primaryColor, welcomeMessage, thankYouTitle, thankYouMessage, logoUrl } customizations: json('customizations').default('{}'), // Authentication options (following chat pattern) authType: text('auth_type').notNull().default('public'), // 'public', 'password', 'email' password: text('password'), // Stored encrypted, populated when authType is 'password' allowedEmails: json('allowed_emails').default('[]'), // Array of allowed emails or domains // Branding showBranding: boolean('show_branding').notNull().default(true), archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ identifierIdx: uniqueIndex('form_identifier_idx') .on(table.identifier) .where(sql`${table.archivedAt} IS NULL`), workflowIdIdx: index('form_workflow_id_idx').on(table.workflowId), userIdIdx: index('form_user_id_idx').on(table.userId), archivedAtPartialIdx: index('form_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), }) ) export const organization = pgTable('organization', { id: text('id').primaryKey(), name: text('name').notNull(), slug: text('slug').notNull(), logo: text('logo'), metadata: json('metadata'), whitelabelSettings: json('whitelabel_settings').$type<{ brandName?: string logoUrl?: string primaryColor?: string primaryHoverColor?: string accentColor?: string accentHoverColor?: string supportEmail?: string documentationUrl?: string termsUrl?: string privacyUrl?: string hidePoweredBySim?: boolean }>(), dataRetentionSettings: json('data_retention_settings').$type<{ logRetentionHours?: number | null softDeleteRetentionHours?: number | null taskCleanupHours?: number | null }>(), orgUsageLimit: decimal('org_usage_limit'), storageUsedBytes: bigint('storage_used_bytes', { mode: 'number' }).notNull().default(0), departedMemberUsage: decimal('departed_member_usage').notNull().default('0'), creditBalance: decimal('credit_balance').notNull().default('0'), createdAt: timestamp('created_at').defaultNow().notNull(), updatedAt: timestamp('updated_at').defaultNow().notNull(), }) export const member = pgTable( 'member', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), organizationId: text('organization_id') .notNull() .references(() => organization.id, { onDelete: 'cascade' }), role: text('role').notNull(), // 'admin' or 'member' - team-level permissions only createdAt: timestamp('created_at').defaultNow().notNull(), }, (table) => ({ userIdUnique: uniqueIndex('member_user_id_unique').on(table.userId), // Users can only belong to one org organizationIdIdx: index('member_organization_id_idx').on(table.organizationId), }) ) export const invitationKindEnum = pgEnum('invitation_kind', ['organization', 'workspace']) export type InvitationKind = (typeof invitationKindEnum.enumValues)[number] export const invitationStatusEnum = pgEnum('invitation_status', [ 'pending', 'accepted', 'rejected', 'cancelled', 'expired', ]) export type InvitationStatus = (typeof invitationStatusEnum.enumValues)[number] export const invitation = pgTable( 'invitation', { id: text('id').primaryKey(), kind: invitationKindEnum('kind').notNull().default('organization'), email: text('email').notNull(), inviterId: text('inviter_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), organizationId: text('organization_id').references(() => organization.id, { onDelete: 'cascade', }), role: text('role').notNull(), status: invitationStatusEnum('status').notNull().default('pending'), token: text('token').notNull().unique(), expiresAt: timestamp('expires_at').notNull(), createdAt: timestamp('created_at').defaultNow().notNull(), updatedAt: timestamp('updated_at').defaultNow().notNull(), }, (table) => ({ emailIdx: index('invitation_email_idx').on(table.email), organizationIdIdx: index('invitation_organization_id_idx').on(table.organizationId), statusIdx: index('invitation_status_idx').on(table.status), pendingPerOrgEmailUnique: uniqueIndex('invitation_pending_email_org_unique') .on(table.email, table.organizationId) .where(sql`${table.status} = 'pending' AND ${table.organizationId} IS NOT NULL`), }) ) export const workspaceModeEnum = pgEnum('workspace_mode', [ 'personal', 'organization', 'grandfathered_shared', ]) export type WorkspaceMode = (typeof workspaceModeEnum.enumValues)[number] export const workspace = pgTable( 'workspace', { id: text('id').primaryKey(), name: text('name').notNull(), color: text('color').notNull().default('#33C482'), logoUrl: text('logo_url'), ownerId: text('owner_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), organizationId: text('organization_id').references(() => organization.id, { onDelete: 'set null', }), workspaceMode: workspaceModeEnum('workspace_mode').notNull().default('grandfathered_shared'), billedAccountUserId: text('billed_account_user_id') .notNull() .references(() => user.id, { onDelete: 'no action' }), allowPersonalApiKeys: boolean('allow_personal_api_keys').notNull().default(true), inboxEnabled: boolean('inbox_enabled').notNull().default(false), inboxAddress: text('inbox_address'), inboxProviderId: text('inbox_provider_id'), archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ ownerIdIdx: index('workspace_owner_id_idx').on(table.ownerId), organizationIdIdx: index('workspace_organization_id_idx').on(table.organizationId), workspaceModeIdx: index('workspace_mode_idx').on(table.workspaceMode), }) ) export const workspaceFile = pgTable( 'workspace_file', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), name: text('name').notNull(), key: text('key').notNull().unique(), size: integer('size').notNull(), type: text('type').notNull(), uploadedBy: text('uploaded_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), deletedAt: timestamp('deleted_at'), uploadedAt: timestamp('uploaded_at').notNull().defaultNow(), }, (table) => ({ workspaceIdIdx: index('workspace_file_workspace_id_idx').on(table.workspaceId), keyIdx: index('workspace_file_key_idx').on(table.key), deletedAtIdx: index('workspace_file_deleted_at_idx').on(table.deletedAt), workspaceDeletedAtPartialIdx: index('workspace_file_workspace_deleted_partial_idx') .on(table.workspaceId, table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), }) ) export const workspaceFiles = pgTable( 'workspace_files', { id: text('id').primaryKey(), key: text('key').notNull(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), context: text('context').notNull(), // 'workspace', 'mothership', 'copilot', 'chat', 'knowledge-base', 'profile-pictures', 'general', 'execution' chatId: uuid('chat_id').references(() => copilotChats.id, { onDelete: 'cascade' }), originalName: text('original_name').notNull(), contentType: text('content_type').notNull(), size: integer('size').notNull(), deletedAt: timestamp('deleted_at'), uploadedAt: timestamp('uploaded_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ keyActiveUniqueIdx: uniqueIndex('workspace_files_key_active_unique') .on(table.key) .where(sql`${table.deletedAt} IS NULL`), /** One active display name per workspace for workspace-scoped files (VFS / file picker). */ workspaceOriginalNameActiveUnique: uniqueIndex('workspace_files_workspace_name_active_unique') .on(table.workspaceId, table.originalName) .where( sql`${table.deletedAt} IS NULL AND ${table.context} = 'workspace' AND ${table.workspaceId} IS NOT NULL` ), keyIdx: index('workspace_files_key_idx').on(table.key), userIdIdx: index('workspace_files_user_id_idx').on(table.userId), workspaceIdIdx: index('workspace_files_workspace_id_idx').on(table.workspaceId), contextIdx: index('workspace_files_context_idx').on(table.context), chatIdIdx: index('workspace_files_chat_id_idx').on(table.chatId), deletedAtIdx: index('workspace_files_deleted_at_idx').on(table.deletedAt), workspaceDeletedAtPartialIdx: index('workspace_files_workspace_deleted_partial_idx') .on(table.workspaceId, table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), }) ) export const permissionTypeEnum = pgEnum('permission_type', ['admin', 'write', 'read']) export const invitationWorkspaceGrant = pgTable( 'invitation_workspace_grant', { id: text('id').primaryKey(), invitationId: text('invitation_id') .notNull() .references(() => invitation.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), permission: permissionTypeEnum('permission').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ invitationWorkspaceUnique: uniqueIndex('invitation_workspace_grant_unique').on( table.invitationId, table.workspaceId ), workspaceIdIdx: index('invitation_workspace_grant_workspace_id_idx').on(table.workspaceId), }) ) export const permissions = pgTable( 'permissions', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), entityType: text('entity_type').notNull(), // 'workspace', 'workflow', 'organization', etc. entityId: text('entity_id').notNull(), // ID of the workspace, workflow, etc. permissionType: permissionTypeEnum('permission_type').notNull(), // Use enum instead of text createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary access pattern - get all permissions for a user userIdIdx: index('permissions_user_id_idx').on(table.userId), // Entity-based queries - get all users with permissions on an entity entityIdx: index('permissions_entity_idx').on(table.entityType, table.entityId), // User + entity type queries - get user's permissions for all workspaces userEntityTypeIdx: index('permissions_user_entity_type_idx').on(table.userId, table.entityType), // Specific permission checks - does user have specific permission on entity userEntityPermissionIdx: index('permissions_user_entity_permission_idx').on( table.userId, table.entityType, table.permissionType ), // User + specific entity queries - get user's permissions for specific entity userEntityIdx: index('permissions_user_entity_idx').on( table.userId, table.entityType, table.entityId ), // Uniqueness constraint - prevent duplicate permission rows (one permission per user/entity) uniquePermissionConstraint: uniqueIndex('permissions_unique_constraint').on( table.userId, table.entityType, table.entityId ), }) ) export const memory = pgTable( 'memory', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), key: text('key').notNull(), data: jsonb('data').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), deletedAt: timestamp('deleted_at'), }, (table) => { return { keyIdx: index('memory_key_idx').on(table.key), workspaceIdx: index('memory_workspace_idx').on(table.workspaceId), uniqueKeyPerWorkspaceIdx: uniqueIndex('memory_workspace_key_idx').on( table.workspaceId, table.key ), workspaceDeletedAtPartialIdx: index('memory_workspace_deleted_partial_idx') .on(table.workspaceId, table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), } } ) export const knowledgeBase = pgTable( 'knowledge_base', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id').references(() => workspace.id), name: text('name').notNull(), description: text('description'), // Token tracking for usage tokenCount: integer('token_count').notNull().default(0), // Embedding configuration embeddingModel: text('embedding_model').notNull().default('text-embedding-3-small'), embeddingDimension: integer('embedding_dimension').notNull().default(1536), // Chunking configuration stored as JSON for flexibility chunkingConfig: json('chunking_config') .notNull() .default('{"maxSize": 1024, "minSize": 1, "overlap": 200}'), // Soft delete support deletedAt: timestamp('deleted_at'), // Metadata and timestamps createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary access patterns userIdIdx: index('kb_user_id_idx').on(table.userId), workspaceIdIdx: index('kb_workspace_id_idx').on(table.workspaceId), // Composite index for user's workspaces userWorkspaceIdx: index('kb_user_workspace_idx').on(table.userId, table.workspaceId), // Index for soft delete filtering deletedAtIdx: index('kb_deleted_at_idx').on(table.deletedAt), workspaceDeletedAtPartialIdx: index('kb_workspace_deleted_partial_idx') .on(table.workspaceId, table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), /** One active (non-deleted) name per workspace; matches user_table_definitions pattern */ workspaceNameActiveUnique: uniqueIndex('kb_workspace_name_active_unique') .on(table.workspaceId, table.name) .where(sql`${table.deletedAt} IS NULL`), }) ) export const document = pgTable( 'document', { id: text('id').primaryKey(), knowledgeBaseId: text('knowledge_base_id') .notNull() .references(() => knowledgeBase.id, { onDelete: 'cascade' }), // File information filename: text('filename').notNull(), fileUrl: text('file_url').notNull(), fileSize: integer('file_size').notNull(), // Size in bytes mimeType: text('mime_type').notNull(), // e.g., 'application/pdf', 'text/plain' // Content statistics chunkCount: integer('chunk_count').notNull().default(0), tokenCount: integer('token_count').notNull().default(0), characterCount: integer('character_count').notNull().default(0), // Processing status processingStatus: text('processing_status').notNull().default('pending'), // 'pending', 'processing', 'completed', 'failed' processingStartedAt: timestamp('processing_started_at'), processingCompletedAt: timestamp('processing_completed_at'), processingError: text('processing_error'), // Document state enabled: boolean('enabled').notNull().default(true), // Enable/disable from knowledge base archivedAt: timestamp('archived_at'), // Parent KB/workspace archive marker deletedAt: timestamp('deleted_at'), // Soft delete userExcluded: boolean('user_excluded').notNull().default(false), // User explicitly excluded — skip on sync // Document tags for filtering (inherited by all chunks) // Text tags (7 slots) tag1: text('tag1'), tag2: text('tag2'), tag3: text('tag3'), tag4: text('tag4'), tag5: text('tag5'), tag6: text('tag6'), tag7: text('tag7'), // Number tags (5 slots) number1: doublePrecision('number1'), number2: doublePrecision('number2'), number3: doublePrecision('number3'), number4: doublePrecision('number4'), number5: doublePrecision('number5'), // Date tags (2 slots) date1: timestamp('date1'), date2: timestamp('date2'), // Boolean tags (3 slots) boolean1: boolean('boolean1'), boolean2: boolean('boolean2'), boolean3: boolean('boolean3'), // Connector-sourced document fields connectorId: text('connector_id').references(() => knowledgeConnector.id, { onDelete: 'set null', }), externalId: text('external_id'), contentHash: text('content_hash'), sourceUrl: text('source_url'), // Timestamps uploadedAt: timestamp('uploaded_at').notNull().defaultNow(), }, (table) => ({ // Primary access pattern - filter by knowledge base knowledgeBaseIdIdx: index('doc_kb_id_idx').on(table.knowledgeBaseId), // Search by filename filenameIdx: index('doc_filename_idx').on(table.filename), // Processing status filtering processingStatusIdx: index('doc_processing_status_idx').on( table.knowledgeBaseId, table.processingStatus ), // Connector document uniqueness (partial — only non-deleted rows) connectorExternalIdIdx: uniqueIndex('doc_connector_external_id_idx') .on(table.connectorId, table.externalId) .where(sql`${table.deletedAt} IS NULL`), // Sync engine: load all active docs for a connector connectorIdIdx: index('doc_connector_id_idx').on(table.connectorId), archivedAtPartialIdx: index('doc_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), deletedAtPartialIdx: index('doc_deleted_at_partial_idx') .on(table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), // Text tag indexes tag1Idx: index('doc_tag1_idx').on(table.tag1), tag2Idx: index('doc_tag2_idx').on(table.tag2), tag3Idx: index('doc_tag3_idx').on(table.tag3), tag4Idx: index('doc_tag4_idx').on(table.tag4), tag5Idx: index('doc_tag5_idx').on(table.tag5), tag6Idx: index('doc_tag6_idx').on(table.tag6), tag7Idx: index('doc_tag7_idx').on(table.tag7), // Number tag indexes (5 slots) number1Idx: index('doc_number1_idx').on(table.number1), number2Idx: index('doc_number2_idx').on(table.number2), number3Idx: index('doc_number3_idx').on(table.number3), number4Idx: index('doc_number4_idx').on(table.number4), number5Idx: index('doc_number5_idx').on(table.number5), // Date tag indexes (2 slots) date1Idx: index('doc_date1_idx').on(table.date1), date2Idx: index('doc_date2_idx').on(table.date2), // Boolean tag indexes (3 slots) boolean1Idx: index('doc_boolean1_idx').on(table.boolean1), boolean2Idx: index('doc_boolean2_idx').on(table.boolean2), boolean3Idx: index('doc_boolean3_idx').on(table.boolean3), }) ) export const knowledgeBaseTagDefinitions = pgTable( 'knowledge_base_tag_definitions', { id: text('id').primaryKey(), knowledgeBaseId: text('knowledge_base_id') .notNull() .references(() => knowledgeBase.id, { onDelete: 'cascade' }), tagSlot: text('tag_slot', { enum: TAG_SLOTS, }).notNull(), displayName: text('display_name').notNull(), fieldType: text('field_type').notNull().default('text'), // 'text', future: 'date', 'number', 'range' createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Ensure unique tag slot per knowledge base kbTagSlotIdx: uniqueIndex('kb_tag_definitions_kb_slot_idx').on( table.knowledgeBaseId, table.tagSlot ), // Ensure unique display name per knowledge base kbDisplayNameIdx: uniqueIndex('kb_tag_definitions_kb_display_name_idx').on( table.knowledgeBaseId, table.displayName ), // Index for querying by knowledge base kbIdIdx: index('kb_tag_definitions_kb_id_idx').on(table.knowledgeBaseId), }) ) export const embedding = pgTable( 'embedding', { id: text('id').primaryKey(), knowledgeBaseId: text('knowledge_base_id') .notNull() .references(() => knowledgeBase.id, { onDelete: 'cascade' }), documentId: text('document_id') .notNull() .references(() => document.id, { onDelete: 'cascade' }), // Chunk information chunkIndex: integer('chunk_index').notNull(), chunkHash: text('chunk_hash').notNull(), content: text('content').notNull(), contentLength: integer('content_length').notNull(), tokenCount: integer('token_count').notNull(), // Vector embeddings - optimized for text-embedding-3-small with HNSW support embedding: vector('embedding', { dimensions: 1536 }), // For text-embedding-3-small embeddingModel: text('embedding_model').notNull().default('text-embedding-3-small'), // Chunk boundaries and overlap startOffset: integer('start_offset').notNull(), endOffset: integer('end_offset').notNull(), // Tag columns inherited from document for efficient filtering // Text tags (7 slots) tag1: text('tag1'), tag2: text('tag2'), tag3: text('tag3'), tag4: text('tag4'), tag5: text('tag5'), tag6: text('tag6'), tag7: text('tag7'), // Number tags (5 slots) number1: doublePrecision('number1'), number2: doublePrecision('number2'), number3: doublePrecision('number3'), number4: doublePrecision('number4'), number5: doublePrecision('number5'), // Date tags (2 slots) date1: timestamp('date1'), date2: timestamp('date2'), // Boolean tags (3 slots) boolean1: boolean('boolean1'), boolean2: boolean('boolean2'), boolean3: boolean('boolean3'), // Chunk state - enable/disable from knowledge base enabled: boolean('enabled').notNull().default(true), // Full-text search support - generated tsvector column contentTsv: tsvector('content_tsv').generatedAlwaysAs( (): SQL => sql`to_tsvector('english', ${embedding.content})` ), // Timestamps createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary vector search pattern kbIdIdx: index('emb_kb_id_idx').on(table.knowledgeBaseId), // Document-level access docIdIdx: index('emb_doc_id_idx').on(table.documentId), // Chunk ordering within documents docChunkIdx: uniqueIndex('emb_doc_chunk_idx').on(table.documentId, table.chunkIndex), // Model-specific queries for A/B testing or migrations kbModelIdx: index('emb_kb_model_idx').on(table.knowledgeBaseId, table.embeddingModel), // Enabled state filtering indexes (for chunk enable/disable functionality) kbEnabledIdx: index('emb_kb_enabled_idx').on(table.knowledgeBaseId, table.enabled), docEnabledIdx: index('emb_doc_enabled_idx').on(table.documentId, table.enabled), // Vector similarity search indexes (HNSW) - optimized for small embeddings embeddingVectorHnswIdx: index('embedding_vector_hnsw_idx') .using('hnsw', table.embedding.op('vector_cosine_ops')) .with({ m: 16, ef_construction: 64, }), // Text tag indexes tag1Idx: index('emb_tag1_idx').on(table.tag1), tag2Idx: index('emb_tag2_idx').on(table.tag2), tag3Idx: index('emb_tag3_idx').on(table.tag3), tag4Idx: index('emb_tag4_idx').on(table.tag4), tag5Idx: index('emb_tag5_idx').on(table.tag5), tag6Idx: index('emb_tag6_idx').on(table.tag6), tag7Idx: index('emb_tag7_idx').on(table.tag7), // Number tag indexes (5 slots) number1Idx: index('emb_number1_idx').on(table.number1), number2Idx: index('emb_number2_idx').on(table.number2), number3Idx: index('emb_number3_idx').on(table.number3), number4Idx: index('emb_number4_idx').on(table.number4), number5Idx: index('emb_number5_idx').on(table.number5), // Date tag indexes (2 slots) date1Idx: index('emb_date1_idx').on(table.date1), date2Idx: index('emb_date2_idx').on(table.date2), // Boolean tag indexes (3 slots) boolean1Idx: index('emb_boolean1_idx').on(table.boolean1), boolean2Idx: index('emb_boolean2_idx').on(table.boolean2), boolean3Idx: index('emb_boolean3_idx').on(table.boolean3), // Full-text search index contentFtsIdx: index('emb_content_fts_idx').using('gin', table.contentTsv), // Ensure embedding exists (simplified since we only support one model) embeddingNotNullCheck: check('embedding_not_null_check', sql`"embedding" IS NOT NULL`), }) ) export const docsEmbeddings = pgTable( 'docs_embeddings', { chunkId: uuid('chunk_id').primaryKey().defaultRandom(), chunkText: text('chunk_text').notNull(), sourceDocument: text('source_document').notNull(), sourceLink: text('source_link').notNull(), headerText: text('header_text').notNull(), headerLevel: integer('header_level').notNull(), tokenCount: integer('token_count').notNull(), // Vector embedding - optimized for text-embedding-3-small with HNSW support embedding: vector('embedding', { dimensions: 1536 }).notNull(), embeddingModel: text('embedding_model').notNull().default('text-embedding-3-small'), // Metadata for flexible filtering metadata: jsonb('metadata').notNull().default('{}'), // Full-text search support - generated tsvector column chunkTextTsv: tsvector('chunk_text_tsv').generatedAlwaysAs( (): SQL => sql`to_tsvector('english', ${docsEmbeddings.chunkText})` ), // Timestamps createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Source document queries sourceDocumentIdx: index('docs_emb_source_document_idx').on(table.sourceDocument), // Header level filtering headerLevelIdx: index('docs_emb_header_level_idx').on(table.headerLevel), // Combined source and header queries sourceHeaderIdx: index('docs_emb_source_header_idx').on( table.sourceDocument, table.headerLevel ), // Model-specific queries modelIdx: index('docs_emb_model_idx').on(table.embeddingModel), // Timestamp queries createdAtIdx: index('docs_emb_created_at_idx').on(table.createdAt), // Vector similarity search indexes (HNSW) - optimized for documentation embeddings embeddingVectorHnswIdx: index('docs_embedding_vector_hnsw_idx') .using('hnsw', table.embedding.op('vector_cosine_ops')) .with({ m: 16, ef_construction: 64, }), // GIN index for JSONB metadata queries metadataGinIdx: index('docs_emb_metadata_gin_idx').using('gin', table.metadata), // Full-text search index chunkTextFtsIdx: index('docs_emb_chunk_text_fts_idx').using('gin', table.chunkTextTsv), // Constraints embeddingNotNullCheck: check('docs_embedding_not_null_check', sql`"embedding" IS NOT NULL`), headerLevelCheck: check( 'docs_header_level_check', sql`"header_level" >= 1 AND "header_level" <= 6` ), }) ) export const chatTypeEnum = pgEnum('chat_type', ['mothership', 'copilot']) export const copilotChats = pgTable( 'copilot_chats', { id: uuid('id').primaryKey().defaultRandom(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), type: chatTypeEnum('type').notNull().default('copilot'), title: text('title'), messages: jsonb('messages').notNull().default('[]'), model: text('model').notNull().default('claude-3-7-sonnet-latest'), conversationId: text('conversation_id'), previewYaml: text('preview_yaml'), planArtifact: text('plan_artifact'), config: jsonb('config'), resources: jsonb('resources').notNull().default('[]'), lastSeenAt: timestamp('last_seen_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary access patterns userIdIdx: index('copilot_chats_user_id_idx').on(table.userId), workflowIdIdx: index('copilot_chats_workflow_id_idx').on(table.workflowId), userWorkflowIdx: index('copilot_chats_user_workflow_idx').on(table.userId, table.workflowId), // Workspace access pattern userWorkspaceIdx2: index('copilot_chats_user_workspace_idx').on( table.userId, table.workspaceId ), // Ordering indexes createdAtIdx: index('copilot_chats_created_at_idx').on(table.createdAt), updatedAtIdx: index('copilot_chats_updated_at_idx').on(table.updatedAt), }) ) export const copilotWorkflowReadHashes = pgTable( 'copilot_workflow_read_hashes', { id: uuid('id').primaryKey().defaultRandom(), chatId: uuid('chat_id') .notNull() .references(() => copilotChats.id, { onDelete: 'cascade' }), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), hash: text('hash').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ chatIdIdx: index('copilot_workflow_read_hashes_chat_id_idx').on(table.chatId), workflowIdIdx: index('copilot_workflow_read_hashes_workflow_id_idx').on(table.workflowId), chatWorkflowUnique: uniqueIndex('copilot_workflow_read_hashes_chat_workflow_unique').on( table.chatId, table.workflowId ), }) ) export const workflowCheckpoints = pgTable( 'workflow_checkpoints', { id: uuid('id').primaryKey().defaultRandom(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), chatId: uuid('chat_id') .notNull() .references(() => copilotChats.id, { onDelete: 'cascade' }), messageId: text('message_id'), // ID of the user message that triggered this checkpoint workflowState: json('workflow_state').notNull(), // JSON workflow state createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary access patterns userIdIdx: index('workflow_checkpoints_user_id_idx').on(table.userId), workflowIdIdx: index('workflow_checkpoints_workflow_id_idx').on(table.workflowId), chatIdIdx: index('workflow_checkpoints_chat_id_idx').on(table.chatId), messageIdIdx: index('workflow_checkpoints_message_id_idx').on(table.messageId), // Combined indexes for common queries userWorkflowIdx: index('workflow_checkpoints_user_workflow_idx').on( table.userId, table.workflowId ), workflowChatIdx: index('workflow_checkpoints_workflow_chat_idx').on( table.workflowId, table.chatId ), // Ordering indexes createdAtIdx: index('workflow_checkpoints_created_at_idx').on(table.createdAt), chatCreatedAtIdx: index('workflow_checkpoints_chat_created_at_idx').on( table.chatId, table.createdAt ), }) ) export const copilotRunStatusEnum = pgEnum('copilot_run_status', [ 'active', 'paused_waiting_for_tool', 'resuming', 'complete', 'error', 'cancelled', ]) export const copilotAsyncToolStatusEnum = pgEnum('copilot_async_tool_status', [ 'pending', 'running', 'completed', 'failed', 'cancelled', 'delivered', ]) export type CopilotRunStatus = (typeof copilotRunStatusEnum.enumValues)[number] export type CopilotAsyncToolStatus = (typeof copilotAsyncToolStatusEnum.enumValues)[number] export const copilotRuns = pgTable( 'copilot_runs', { id: uuid('id').primaryKey().defaultRandom(), executionId: text('execution_id').notNull(), parentRunId: uuid('parent_run_id'), chatId: uuid('chat_id') .notNull() .references(() => copilotChats.id, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'cascade' }), streamId: text('stream_id').notNull(), agent: text('agent'), model: text('model'), provider: text('provider'), status: copilotRunStatusEnum('status').notNull().default('active'), requestContext: jsonb('request_context').notNull().default('{}'), startedAt: timestamp('started_at').notNull().defaultNow(), completedAt: timestamp('completed_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), error: text('error'), }, (table) => ({ executionIdIdx: index('copilot_runs_execution_id_idx').on(table.executionId), parentRunIdIdx: index('copilot_runs_parent_run_id_idx').on(table.parentRunId), chatIdIdx: index('copilot_runs_chat_id_idx').on(table.chatId), userIdIdx: index('copilot_runs_user_id_idx').on(table.userId), workflowIdIdx: index('copilot_runs_workflow_id_idx').on(table.workflowId), workspaceIdIdx: index('copilot_runs_workspace_id_idx').on(table.workspaceId), statusIdx: index('copilot_runs_status_idx').on(table.status), chatExecutionIdx: index('copilot_runs_chat_execution_idx').on(table.chatId, table.executionId), executionStartedAtIdx: index('copilot_runs_execution_started_at_idx').on( table.executionId, table.startedAt ), streamIdUnique: uniqueIndex('copilot_runs_stream_id_unique').on(table.streamId), }) ) export const copilotRunCheckpoints = pgTable( 'copilot_run_checkpoints', { id: uuid('id').primaryKey().defaultRandom(), runId: uuid('run_id') .notNull() .references(() => copilotRuns.id, { onDelete: 'cascade' }), pendingToolCallId: text('pending_tool_call_id').notNull(), conversationSnapshot: jsonb('conversation_snapshot').notNull().default('{}'), agentState: jsonb('agent_state').notNull().default('{}'), providerRequest: jsonb('provider_request').notNull().default('{}'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ runIdIdx: index('copilot_run_checkpoints_run_id_idx').on(table.runId), pendingToolCallIdIdx: index('copilot_run_checkpoints_pending_tool_call_id_idx').on( table.pendingToolCallId ), runPendingUnique: uniqueIndex('copilot_run_checkpoints_run_pending_tool_unique').on( table.runId, table.pendingToolCallId ), }) ) export const copilotAsyncToolCalls = pgTable( 'copilot_async_tool_calls', { id: uuid('id').primaryKey().defaultRandom(), runId: uuid('run_id') .notNull() .references(() => copilotRuns.id, { onDelete: 'cascade' }), checkpointId: uuid('checkpoint_id').references(() => copilotRunCheckpoints.id, { onDelete: 'cascade', }), toolCallId: text('tool_call_id').notNull(), toolName: text('tool_name').notNull(), args: jsonb('args').notNull().default('{}'), status: copilotAsyncToolStatusEnum('status').notNull().default('pending'), result: jsonb('result'), error: text('error'), claimedAt: timestamp('claimed_at'), claimedBy: text('claimed_by'), completedAt: timestamp('completed_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ runIdIdx: index('copilot_async_tool_calls_run_id_idx').on(table.runId), checkpointIdIdx: index('copilot_async_tool_calls_checkpoint_id_idx').on(table.checkpointId), toolCallIdIdx: index('copilot_async_tool_calls_tool_call_id_idx').on(table.toolCallId), statusIdx: index('copilot_async_tool_calls_status_idx').on(table.status), runStatusIdx: index('copilot_async_tool_calls_run_status_idx').on(table.runId, table.status), toolCallUnique: uniqueIndex('copilot_async_tool_calls_tool_call_id_unique').on( table.toolCallId ), }) ) export const templateStatusEnum = pgEnum('template_status', ['pending', 'approved', 'rejected']) export const templateCreatorTypeEnum = pgEnum('template_creator_type', ['user', 'organization']) export const templateCreators = pgTable( 'template_creators', { id: text('id').primaryKey(), referenceType: templateCreatorTypeEnum('reference_type').notNull(), referenceId: text('reference_id').notNull(), name: text('name').notNull(), profileImageUrl: text('profile_image_url'), details: jsonb('details'), verified: boolean('verified').notNull().default(false), createdBy: text('created_by').references(() => user.id, { onDelete: 'set null' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ referenceUniqueIdx: uniqueIndex('template_creators_reference_idx').on( table.referenceType, table.referenceId ), referenceIdIdx: index('template_creators_reference_id_idx').on(table.referenceId), createdByIdx: index('template_creators_created_by_idx').on(table.createdBy), }) ) export const templates = pgTable( 'templates', { id: text('id').primaryKey(), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'set null' }), name: text('name').notNull(), details: jsonb('details'), creatorId: text('creator_id').references(() => templateCreators.id, { onDelete: 'set null' }), views: integer('views').notNull().default(0), stars: integer('stars').notNull().default(0), status: templateStatusEnum('status').notNull().default('pending'), tags: text('tags').array().notNull().default(sql`'{}'::text[]`), // Array of tags requiredCredentials: jsonb('required_credentials').notNull().default('[]'), // Array of credential requirements state: jsonb('state').notNull(), // Store the workflow state directly ogImageUrl: text('og_image_url'), // Pre-generated OpenGraph image URL createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary access patterns statusIdx: index('templates_status_idx').on(table.status), creatorIdIdx: index('templates_creator_id_idx').on(table.creatorId), // Sorting indexes for popular/trending templates viewsIdx: index('templates_views_idx').on(table.views), starsIdx: index('templates_stars_idx').on(table.stars), // Composite indexes for common queries statusViewsIdx: index('templates_status_views_idx').on(table.status, table.views), statusStarsIdx: index('templates_status_stars_idx').on(table.status, table.stars), // Temporal indexes createdAtIdx: index('templates_created_at_idx').on(table.createdAt), updatedAtIdx: index('templates_updated_at_idx').on(table.updatedAt), }) ) export const templateStars = pgTable( 'template_stars', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), templateId: text('template_id') .notNull() .references(() => templates.id, { onDelete: 'cascade' }), starredAt: timestamp('starred_at').notNull().defaultNow(), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ // Primary access patterns userIdIdx: index('template_stars_user_id_idx').on(table.userId), templateIdIdx: index('template_stars_template_id_idx').on(table.templateId), // Composite indexes for common queries userTemplateIdx: index('template_stars_user_template_idx').on(table.userId, table.templateId), templateUserIdx: index('template_stars_template_user_idx').on(table.templateId, table.userId), // Temporal indexes for analytics starredAtIdx: index('template_stars_starred_at_idx').on(table.starredAt), templateStarredAtIdx: index('template_stars_template_starred_at_idx').on( table.templateId, table.starredAt ), // Uniqueness constraint - prevent duplicate stars uniqueUserTemplateConstraint: uniqueIndex('template_stars_user_template_unique').on( table.userId, table.templateId ), }) ) export const copilotFeedback = pgTable( 'copilot_feedback', { feedbackId: uuid('feedback_id').primaryKey().defaultRandom(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), chatId: uuid('chat_id') .notNull() .references(() => copilotChats.id, { onDelete: 'cascade' }), userQuery: text('user_query').notNull(), agentResponse: text('agent_response').notNull(), isPositive: boolean('is_positive').notNull(), feedback: text('feedback'), // Optional feedback text workflowYaml: text('workflow_yaml'), // Optional workflow YAML if edit/build workflow was triggered createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Access patterns userIdIdx: index('copilot_feedback_user_id_idx').on(table.userId), chatIdIdx: index('copilot_feedback_chat_id_idx').on(table.chatId), userChatIdx: index('copilot_feedback_user_chat_idx').on(table.userId, table.chatId), // Query patterns isPositiveIdx: index('copilot_feedback_is_positive_idx').on(table.isPositive), // Ordering indexes createdAtIdx: index('copilot_feedback_created_at_idx').on(table.createdAt), }) ) // Tracks immutable deployment versions for each workflow export const workflowDeploymentVersion = pgTable( 'workflow_deployment_version', { id: text('id').primaryKey(), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), version: integer('version').notNull(), name: text('name'), description: text('description'), state: json('state').notNull(), isActive: boolean('is_active').notNull().default(false), createdAt: timestamp('created_at').notNull().defaultNow(), createdBy: text('created_by'), }, (table) => ({ workflowVersionUnique: uniqueIndex('workflow_deployment_version_workflow_version_unique').on( table.workflowId, table.version ), workflowActiveIdx: index('workflow_deployment_version_workflow_active_idx').on( table.workflowId, table.isActive ), createdAtIdx: index('workflow_deployment_version_created_at_idx').on(table.createdAt), }) ) // Idempotency keys for preventing duplicate processing across all webhooks and triggers export const idempotencyKey = pgTable( 'idempotency_key', { key: text('key').primaryKey(), result: json('result').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ // Index for cleanup operations by creation time createdAtIdx: index('idempotency_key_created_at_idx').on(table.createdAt), }) ) export const outboxEvent = pgTable( 'outbox_event', { id: text('id').primaryKey(), eventType: text('event_type').notNull(), payload: json('payload').notNull(), status: text('status').notNull().default('pending'), attempts: integer('attempts').notNull().default(0), maxAttempts: integer('max_attempts').notNull().default(10), availableAt: timestamp('available_at').notNull().defaultNow(), lockedAt: timestamp('locked_at'), lastError: text('last_error'), createdAt: timestamp('created_at').notNull().defaultNow(), processedAt: timestamp('processed_at'), }, (table) => ({ statusAvailableIdx: index('outbox_event_status_available_idx').on( table.status, table.availableAt ), lockedAtIdx: index('outbox_event_locked_at_idx').on(table.lockedAt), }) ) export const mcpServers = pgTable( 'mcp_servers', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), // Track who created the server, but workspace owns it createdBy: text('created_by').references(() => user.id, { onDelete: 'set null' }), name: text('name').notNull(), description: text('description'), transport: text('transport').notNull(), url: text('url'), headers: json('headers').default('{}'), timeout: integer('timeout').default(30000), retries: integer('retries').default(3), enabled: boolean('enabled').notNull().default(true), lastConnected: timestamp('last_connected'), connectionStatus: text('connection_status').default('disconnected'), lastError: text('last_error'), statusConfig: jsonb('status_config').default('{}'), toolCount: integer('tool_count').default(0), lastToolsRefresh: timestamp('last_tools_refresh'), totalRequests: integer('total_requests').default(0), lastUsed: timestamp('last_used'), deletedAt: timestamp('deleted_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ // Primary access pattern - active servers by workspace workspaceEnabledIdx: index('mcp_servers_workspace_enabled_idx').on( table.workspaceId, table.enabled ), // Soft delete pattern - workspace + not deleted (partial: only deleted rows) workspaceDeletedIdx: index('mcp_servers_workspace_deleted_partial_idx') .on(table.workspaceId, table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), }) ) // SSO Provider table export const ssoProvider = pgTable( 'sso_provider', { id: text('id').primaryKey(), issuer: text('issuer').notNull(), domain: text('domain').notNull(), oidcConfig: text('oidc_config'), samlConfig: text('saml_config'), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), providerId: text('provider_id').notNull(), organizationId: text('organization_id').references(() => organization.id, { onDelete: 'cascade', }), }, (table) => ({ providerIdIdx: index('sso_provider_provider_id_idx').on(table.providerId), domainIdx: index('sso_provider_domain_idx').on(table.domain), userIdIdx: index('sso_provider_user_id_idx').on(table.userId), organizationIdIdx: index('sso_provider_organization_id_idx').on(table.organizationId), }) ) /** * Workflow MCP Servers - User-created MCP servers that expose workflows as tools. * These servers are accessible by external MCP clients via API key authentication, * or publicly if isPublic is set to true. */ export const workflowMcpServer = pgTable( 'workflow_mcp_server', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), name: text('name').notNull(), description: text('description'), isPublic: boolean('is_public').notNull().default(false), deletedAt: timestamp('deleted_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceIdIdx: index('workflow_mcp_server_workspace_id_idx').on(table.workspaceId), createdByIdx: index('workflow_mcp_server_created_by_idx').on(table.createdBy), deletedAtIdx: index('workflow_mcp_server_deleted_at_idx').on(table.deletedAt), workspaceDeletedAtPartialIdx: index('workflow_mcp_server_workspace_deleted_partial_idx') .on(table.workspaceId, table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), }) ) /** * Workflow MCP Tools - Workflows registered as tools within a Workflow MCP Server. * Each tool maps to a deployed workflow's execute endpoint. */ export const workflowMcpTool = pgTable( 'workflow_mcp_tool', { id: text('id').primaryKey(), serverId: text('server_id') .notNull() .references(() => workflowMcpServer.id, { onDelete: 'cascade' }), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), toolName: text('tool_name').notNull(), toolDescription: text('tool_description'), parameterSchema: json('parameter_schema').notNull().default('{}'), archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ serverIdIdx: index('workflow_mcp_tool_server_id_idx').on(table.serverId), workflowIdIdx: index('workflow_mcp_tool_workflow_id_idx').on(table.workflowId), serverWorkflowUnique: uniqueIndex('workflow_mcp_tool_server_workflow_unique') .on(table.serverId, table.workflowId) .where(sql`${table.archivedAt} IS NULL`), archivedAtPartialIdx: index('workflow_mcp_tool_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), }) ) /** * A2A Task State Enum (v0.2.6) */ export const a2aTaskStatusEnum = pgEnum('a2a_task_status', [ 'submitted', 'working', 'input-required', 'completed', 'failed', 'canceled', 'rejected', 'auth-required', 'unknown', ]) /** * A2A Agents - Workflows exposed as A2A-compatible agents * These agents can be called by external A2A clients */ export const a2aAgent = pgTable( 'a2a_agent', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), workflowId: text('workflow_id') .notNull() .references(() => workflow.id, { onDelete: 'cascade' }), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), /** Agent name (used in Agent Card) */ name: text('name').notNull(), /** Agent description */ description: text('description'), /** Agent version */ version: text('version').notNull().default('1.0.0'), /** Agent capabilities (streaming, pushNotifications, etc.) */ capabilities: jsonb('capabilities').notNull().default('{}'), /** Agent skills derived from workflow */ skills: jsonb('skills').notNull().default('[]'), /** Authentication configuration */ authentication: jsonb('authentication').notNull().default('{}'), /** Agent card signatures for verification (v0.3) */ signatures: jsonb('signatures').default('[]'), /** Whether the agent is published and discoverable */ isPublished: boolean('is_published').notNull().default(false), /** When the agent was published */ publishedAt: timestamp('published_at'), archivedAt: timestamp('archived_at'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workflowIdIdx: index('a2a_agent_workflow_id_idx').on(table.workflowId), createdByIdx: index('a2a_agent_created_by_idx').on(table.createdBy), workspaceWorkflowUnique: uniqueIndex('a2a_agent_workspace_workflow_unique') .on(table.workspaceId, table.workflowId) .where(sql`${table.archivedAt} IS NULL`), archivedAtIdx: index('a2a_agent_archived_at_idx').on(table.archivedAt), workspaceArchivedAtPartialIdx: index('a2a_agent_workspace_archived_partial_idx') .on(table.workspaceId, table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), }) ) /** * A2A Tasks - Tracks task state for A2A agent interactions (v0.3) * Each task represents a conversation/interaction with an agent */ export const a2aTask = pgTable( 'a2a_task', { id: text('id').primaryKey(), agentId: text('agent_id') .notNull() .references(() => a2aAgent.id, { onDelete: 'cascade' }), /** Context ID for multi-turn conversations (maps to API contextId) */ sessionId: text('session_id'), /** Task state */ status: a2aTaskStatusEnum('status').notNull().default('submitted'), /** Message history (maps to API history, array of TaskMessage) */ messages: jsonb('messages').notNull().default('[]'), /** Structured output artifacts */ artifacts: jsonb('artifacts').default('[]'), /** Link to workflow execution */ executionId: text('execution_id'), /** Additional metadata */ metadata: jsonb('metadata').default('{}'), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), completedAt: timestamp('completed_at'), }, (table) => ({ agentIdIdx: index('a2a_task_agent_id_idx').on(table.agentId), sessionIdIdx: index('a2a_task_session_id_idx').on(table.sessionId), statusIdx: index('a2a_task_status_idx').on(table.status), executionIdIdx: index('a2a_task_execution_id_idx').on(table.executionId), createdAtIdx: index('a2a_task_created_at_idx').on(table.createdAt), }) ) /** * A2A Push Notification Config - Webhook configuration for task updates * Stores push notification webhooks for async task updates */ export const a2aPushNotificationConfig = pgTable( 'a2a_push_notification_config', { id: text('id').primaryKey(), taskId: text('task_id') .notNull() .references(() => a2aTask.id, { onDelete: 'cascade' }), /** Webhook URL for notifications */ url: text('url').notNull(), /** Optional token for client-side validation */ token: text('token'), /** Authentication schemes (e.g., ['bearer', 'apiKey']) */ authSchemes: jsonb('auth_schemes').default('[]'), /** Authentication credentials hint */ authCredentials: text('auth_credentials'), /** Whether this config is active */ isActive: boolean('is_active').notNull().default(true), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ taskIdUnique: uniqueIndex('a2a_push_notification_config_task_unique').on(table.taskId), }) ) export const auditLog = pgTable( 'audit_log', { id: text('id').primaryKey(), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'set null' }), actorId: text('actor_id').references(() => user.id, { onDelete: 'set null' }), action: text('action').notNull(), resourceType: text('resource_type').notNull(), resourceId: text('resource_id'), actorName: text('actor_name'), actorEmail: text('actor_email'), resourceName: text('resource_name'), description: text('description'), metadata: jsonb('metadata').default('{}'), ipAddress: text('ip_address'), userAgent: text('user_agent'), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ workspaceCreatedIdx: index('audit_log_workspace_created_idx').on( table.workspaceId, table.createdAt ), actorCreatedIdx: index('audit_log_actor_created_idx').on(table.actorId, table.createdAt), resourceIdx: index('audit_log_resource_idx').on(table.resourceType, table.resourceId), actionIdx: index('audit_log_action_idx').on(table.action), }) ) export const usageLogCategoryEnum = pgEnum('usage_log_category', ['model', 'fixed']) export const usageLogSourceEnum = pgEnum('usage_log_source', [ 'workflow', 'wand', 'copilot', 'workspace-chat', 'mcp_copilot', 'mothership_block', 'knowledge-base', 'voice-input', ]) export const usageLog = pgTable( 'usage_log', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), category: usageLogCategoryEnum('category').notNull(), source: usageLogSourceEnum('source').notNull(), description: text('description').notNull(), metadata: jsonb('metadata'), cost: decimal('cost').notNull(), workspaceId: text('workspace_id').references(() => workspace.id, { onDelete: 'set null' }), workflowId: text('workflow_id').references(() => workflow.id, { onDelete: 'set null' }), executionId: text('execution_id'), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ userCreatedAtIdx: index('usage_log_user_created_at_idx').on(table.userId, table.createdAt), sourceIdx: index('usage_log_source_idx').on(table.source), workspaceIdIdx: index('usage_log_workspace_id_idx').on(table.workspaceId), workflowIdIdx: index('usage_log_workflow_id_idx').on(table.workflowId), workspaceCreatedAtIdx: index('usage_log_workspace_created_at_idx').on( table.workspaceId, table.createdAt ), }) ) export const credentialTypeEnum = pgEnum('credential_type', [ 'oauth', 'env_workspace', 'env_personal', 'service_account', ]) export const credential = pgTable( 'credential', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), type: credentialTypeEnum('type').notNull(), displayName: text('display_name').notNull(), description: text('description'), providerId: text('provider_id'), accountId: text('account_id').references(() => account.id, { onDelete: 'cascade' }), envKey: text('env_key'), envOwnerUserId: text('env_owner_user_id').references(() => user.id, { onDelete: 'cascade' }), encryptedServiceAccountKey: text('encrypted_service_account_key'), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceIdIdx: index('credential_workspace_id_idx').on(table.workspaceId), typeIdx: index('credential_type_idx').on(table.type), providerIdIdx: index('credential_provider_id_idx').on(table.providerId), accountIdIdx: index('credential_account_id_idx').on(table.accountId), envOwnerUserIdIdx: index('credential_env_owner_user_id_idx').on(table.envOwnerUserId), workspaceAccountUnique: uniqueIndex('credential_workspace_account_unique') .on(table.workspaceId, table.accountId) .where(sql`account_id IS NOT NULL`), workspaceEnvUnique: uniqueIndex('credential_workspace_env_unique') .on(table.workspaceId, table.type, table.envKey) .where(sql`type = 'env_workspace'`), workspacePersonalEnvUnique: uniqueIndex('credential_workspace_personal_env_unique') .on(table.workspaceId, table.type, table.envKey, table.envOwnerUserId) .where(sql`type = 'env_personal'`), oauthSourceConstraint: check( 'credential_oauth_source_check', sql`(type <> 'oauth') OR (account_id IS NOT NULL AND provider_id IS NOT NULL)` ), workspaceEnvSourceConstraint: check( 'credential_workspace_env_source_check', sql`(type <> 'env_workspace') OR (env_key IS NOT NULL AND env_owner_user_id IS NULL)` ), personalEnvSourceConstraint: check( 'credential_personal_env_source_check', sql`(type <> 'env_personal') OR (env_key IS NOT NULL AND env_owner_user_id IS NOT NULL)` ), }) ) export const credentialMemberRoleEnum = pgEnum('credential_member_role', ['admin', 'member']) export const credentialMemberStatusEnum = pgEnum('credential_member_status', [ 'active', 'pending', 'revoked', ]) export const credentialMember = pgTable( 'credential_member', { id: text('id').primaryKey(), credentialId: text('credential_id') .notNull() .references(() => credential.id, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), role: credentialMemberRoleEnum('role').notNull().default('member'), status: credentialMemberStatusEnum('status').notNull().default('active'), joinedAt: timestamp('joined_at'), invitedBy: text('invited_by').references(() => user.id, { onDelete: 'set null' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ userIdIdx: index('credential_member_user_id_idx').on(table.userId), roleIdx: index('credential_member_role_idx').on(table.role), statusIdx: index('credential_member_status_idx').on(table.status), uniqueMembership: uniqueIndex('credential_member_unique').on(table.credentialId, table.userId), }) ) export const pendingCredentialDraft = pgTable( 'pending_credential_draft', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), providerId: text('provider_id').notNull(), displayName: text('display_name').notNull(), description: text('description'), credentialId: text('credential_id').references(() => credential.id, { onDelete: 'cascade' }), expiresAt: timestamp('expires_at').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ uniqueDraft: uniqueIndex('pending_draft_user_provider_ws').on( table.userId, table.providerId, table.workspaceId ), }) ) export const credentialSet = pgTable( 'credential_set', { id: text('id').primaryKey(), organizationId: text('organization_id') .notNull() .references(() => organization.id, { onDelete: 'cascade' }), name: text('name').notNull(), description: text('description'), providerId: text('provider_id').notNull(), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ createdByIdx: index('credential_set_created_by_idx').on(table.createdBy), orgNameUnique: uniqueIndex('credential_set_org_name_unique').on( table.organizationId, table.name ), providerIdIdx: index('credential_set_provider_id_idx').on(table.providerId), }) ) export const credentialSetMemberStatusEnum = pgEnum('credential_set_member_status', [ 'active', 'pending', 'revoked', ]) export const credentialSetMember = pgTable( 'credential_set_member', { id: text('id').primaryKey(), credentialSetId: text('credential_set_id') .notNull() .references(() => credentialSet.id, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), status: credentialSetMemberStatusEnum('status').notNull().default('pending'), joinedAt: timestamp('joined_at'), invitedBy: text('invited_by').references(() => user.id, { onDelete: 'set null' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ userIdIdx: index('credential_set_member_user_id_idx').on(table.userId), uniqueMembership: uniqueIndex('credential_set_member_unique').on( table.credentialSetId, table.userId ), statusIdx: index('credential_set_member_status_idx').on(table.status), }) ) export const credentialSetInvitationStatusEnum = pgEnum('credential_set_invitation_status', [ 'pending', 'accepted', 'expired', 'cancelled', ]) export const credentialSetInvitation = pgTable( 'credential_set_invitation', { id: text('id').primaryKey(), credentialSetId: text('credential_set_id') .notNull() .references(() => credentialSet.id, { onDelete: 'cascade' }), email: text('email'), token: text('token').notNull().unique(), invitedBy: text('invited_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), status: credentialSetInvitationStatusEnum('status').notNull().default('pending'), expiresAt: timestamp('expires_at').notNull(), acceptedAt: timestamp('accepted_at'), acceptedByUserId: text('accepted_by_user_id').references(() => user.id, { onDelete: 'set null', }), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ credentialSetIdIdx: index('credential_set_invitation_set_id_idx').on(table.credentialSetId), tokenIdx: index('credential_set_invitation_token_idx').on(table.token), statusIdx: index('credential_set_invitation_status_idx').on(table.status), expiresAtIdx: index('credential_set_invitation_expires_at_idx').on(table.expiresAt), }) ) export const permissionGroup = pgTable( 'permission_group', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), name: text('name').notNull(), description: text('description'), config: jsonb('config').notNull().default('{}'), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), autoAddNewMembers: boolean('auto_add_new_members').notNull().default(false), }, (table) => ({ createdByIdx: index('permission_group_created_by_idx').on(table.createdBy), workspaceNameUnique: uniqueIndex('permission_group_workspace_name_unique').on( table.workspaceId, table.name ), autoAddNewMembersUnique: uniqueIndex('permission_group_workspace_auto_add_unique') .on(table.workspaceId) .where(sql`auto_add_new_members = true`), }) ) export const permissionGroupMember = pgTable( 'permission_group_member', { id: text('id').primaryKey(), permissionGroupId: text('permission_group_id') .notNull() .references(() => permissionGroup.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), assignedBy: text('assigned_by').references(() => user.id, { onDelete: 'set null' }), assignedAt: timestamp('assigned_at').notNull().defaultNow(), }, (table) => ({ permissionGroupIdIdx: index('permission_group_member_group_id_idx').on(table.permissionGroupId), groupUserUnique: uniqueIndex('permission_group_member_group_user_unique').on( table.permissionGroupId, table.userId ), workspaceUserUnique: uniqueIndex('permission_group_member_workspace_user_unique').on( table.workspaceId, table.userId ), }) ) /** * Async Jobs - Queue for background job processing (Redis/DB backends) * Used when trigger.dev is not available for async workflow executions */ export const asyncJobs = pgTable( 'async_jobs', { id: text('id').primaryKey(), type: text('type').notNull(), payload: jsonb('payload').notNull(), status: text('status').notNull().default('pending'), createdAt: timestamp('created_at').notNull().defaultNow(), startedAt: timestamp('started_at'), completedAt: timestamp('completed_at'), runAt: timestamp('run_at'), attempts: integer('attempts').notNull().default(0), maxAttempts: integer('max_attempts').notNull().default(3), error: text('error'), output: jsonb('output'), metadata: jsonb('metadata').notNull().default('{}'), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ statusStartedAtIdx: index('async_jobs_status_started_at_idx').on(table.status, table.startedAt), statusCompletedAtIdx: index('async_jobs_status_completed_at_idx').on( table.status, table.completedAt ), }) ) /** * Knowledge Connector - persistent link to an external source (Confluence, Google Drive, etc.) * that syncs documents into a knowledge base. */ export const knowledgeConnector = pgTable( 'knowledge_connector', { id: text('id').primaryKey(), knowledgeBaseId: text('knowledge_base_id') .notNull() .references(() => knowledgeBase.id, { onDelete: 'cascade' }), connectorType: text('connector_type').notNull(), credentialId: text('credential_id'), encryptedApiKey: text('encrypted_api_key'), sourceConfig: json('source_config').notNull(), syncMode: text('sync_mode').notNull().default('full'), syncIntervalMinutes: integer('sync_interval_minutes').notNull().default(1440), status: text('status').notNull().default('active'), lastSyncAt: timestamp('last_sync_at'), lastSyncError: text('last_sync_error'), lastSyncDocCount: integer('last_sync_doc_count'), nextSyncAt: timestamp('next_sync_at'), consecutiveFailures: integer('consecutive_failures').notNull().default(0), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), archivedAt: timestamp('archived_at'), deletedAt: timestamp('deleted_at'), }, (table) => ({ knowledgeBaseIdIdx: index('kc_knowledge_base_id_idx').on(table.knowledgeBaseId), statusNextSyncIdx: index('kc_status_next_sync_idx').on(table.status, table.nextSyncAt), archivedAtPartialIdx: index('kc_archived_at_partial_idx') .on(table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), deletedAtPartialIdx: index('kc_deleted_at_partial_idx') .on(table.deletedAt) .where(sql`${table.deletedAt} IS NOT NULL`), }) ) /** * Knowledge Connector Sync Log - audit trail for connector sync operations. */ export const knowledgeConnectorSyncLog = pgTable( 'knowledge_connector_sync_log', { id: text('id').primaryKey(), connectorId: text('connector_id') .notNull() .references(() => knowledgeConnector.id, { onDelete: 'cascade' }), status: text('status').notNull(), startedAt: timestamp('started_at').notNull().defaultNow(), completedAt: timestamp('completed_at'), docsAdded: integer('docs_added').notNull().default(0), docsUpdated: integer('docs_updated').notNull().default(0), docsDeleted: integer('docs_deleted').notNull().default(0), docsUnchanged: integer('docs_unchanged').notNull().default(0), docsFailed: integer('docs_failed').notNull().default(0), errorMessage: text('error_message'), }, (table) => ({ connectorIdIdx: index('kcsl_connector_id_idx').on(table.connectorId), }) ) /** * User-defined table definitions * Stores schema and metadata for custom tables created by users */ export const userTableDefinitions = pgTable( 'user_table_definitions', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), name: text('name').notNull(), description: text('description'), /** * @remarks * Stores the table schema definition. Example: { columns: [{ name: string, type: string, required: boolean }] } */ schema: jsonb('schema').notNull(), /** * @remarks * Stores UI-specific metadata separate from the data schema. * Example: { columnWidths: { name: 200, age: 100 } } */ metadata: jsonb('metadata'), maxRows: integer('max_rows').notNull().default(10000), rowCount: integer('row_count').notNull().default(0), archivedAt: timestamp('archived_at'), createdBy: text('created_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), }, (table) => ({ workspaceIdIdx: index('user_table_def_workspace_id_idx').on(table.workspaceId), workspaceNameUnique: uniqueIndex('user_table_def_workspace_name_unique') .on(table.workspaceId, table.name) .where(sql`${table.archivedAt} IS NULL`), archivedAtIdx: index('user_table_def_archived_at_idx').on(table.archivedAt), workspaceArchivedAtPartialIdx: index('user_table_def_workspace_archived_partial_idx') .on(table.workspaceId, table.archivedAt) .where(sql`${table.archivedAt} IS NOT NULL`), }) ) /** * User-defined table rows * Stores actual row data as JSONB for flexible schema */ export const userTableRows = pgTable( 'user_table_rows', { id: text('id').primaryKey(), tableId: text('table_id') .notNull() .references(() => userTableDefinitions.id, { onDelete: 'cascade' }), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), data: jsonb('data').notNull(), position: integer('position').notNull().default(0), createdAt: timestamp('created_at').notNull().defaultNow(), updatedAt: timestamp('updated_at').notNull().defaultNow(), createdBy: text('created_by').references(() => user.id, { onDelete: 'set null' }), }, (table) => ({ tableIdIdx: index('user_table_rows_table_id_idx').on(table.tableId), dataGinIdx: index('user_table_rows_data_gin_idx').using('gin', table.data), workspaceTableIdx: index('user_table_rows_workspace_table_idx').on( table.workspaceId, table.tableId ), tablePositionIdx: index('user_table_rows_table_position_idx').on(table.tableId, table.position), }) ) export const oauthApplication = pgTable( 'oauth_application', { id: text('id').primaryKey(), name: text('name').notNull(), icon: text('icon'), metadata: text('metadata'), clientId: text('client_id').notNull().unique(), clientSecret: text('client_secret'), redirectURLs: text('redirect_urls').notNull(), type: text('type').notNull(), disabled: boolean('disabled').default(false), userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), }, (table) => ({ clientIdIdx: index('oauth_application_client_id_idx').on(table.clientId), }) ) export const oauthAccessToken = pgTable( 'oauth_access_token', { id: text('id').primaryKey(), accessToken: text('access_token').notNull().unique(), refreshToken: text('refresh_token').notNull().unique(), accessTokenExpiresAt: timestamp('access_token_expires_at').notNull(), refreshTokenExpiresAt: timestamp('refresh_token_expires_at').notNull(), clientId: text('client_id') .notNull() .references(() => oauthApplication.clientId, { onDelete: 'cascade' }), userId: text('user_id').references(() => user.id, { onDelete: 'cascade' }), scopes: text('scopes').notNull(), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), }, (table) => ({ accessTokenIdx: index('oauth_access_token_access_token_idx').on(table.accessToken), refreshTokenIdx: index('oauth_access_token_refresh_token_idx').on(table.refreshToken), }) ) export const oauthConsent = pgTable( 'oauth_consent', { id: text('id').primaryKey(), clientId: text('client_id') .notNull() .references(() => oauthApplication.clientId, { onDelete: 'cascade' }), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), scopes: text('scopes').notNull(), createdAt: timestamp('created_at').notNull(), updatedAt: timestamp('updated_at').notNull(), consentGiven: boolean('consent_given').notNull(), }, (table) => ({ userClientIdx: index('oauth_consent_user_client_idx').on(table.userId, table.clientId), }) ) export const jwks = pgTable('jwks', { id: text('id').primaryKey(), publicKey: text('public_key').notNull(), privateKey: text('private_key').notNull(), createdAt: timestamp('created_at').notNull(), }) export const mothershipInboxAllowedSender = pgTable( 'mothership_inbox_allowed_sender', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), email: text('email').notNull(), label: text('label'), addedBy: text('added_by') .notNull() .references(() => user.id, { onDelete: 'cascade' }), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ wsEmailIdx: uniqueIndex('inbox_sender_ws_email_idx').on(table.workspaceId, table.email), }) ) export const mothershipInboxTask = pgTable( 'mothership_inbox_task', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .references(() => workspace.id, { onDelete: 'cascade' }), fromEmail: text('from_email').notNull(), fromName: text('from_name'), subject: text('subject').notNull(), bodyPreview: text('body_preview'), bodyText: text('body_text'), bodyHtml: text('body_html'), emailMessageId: text('email_message_id'), inReplyTo: text('in_reply_to'), responseMessageId: text('response_message_id'), agentmailMessageId: text('agentmail_message_id'), status: text('status').notNull().default('received'), chatId: uuid('chat_id').references(() => copilotChats.id, { onDelete: 'set null' }), triggerJobId: text('trigger_job_id'), resultSummary: text('result_summary'), errorMessage: text('error_message'), rejectionReason: text('rejection_reason'), hasAttachments: boolean('has_attachments').notNull().default(false), ccRecipients: text('cc_recipients'), createdAt: timestamp('created_at').notNull().defaultNow(), processingStartedAt: timestamp('processing_started_at'), completedAt: timestamp('completed_at'), }, (table) => ({ wsCreatedAtIdx: index('inbox_task_ws_created_at_idx').on(table.workspaceId, table.createdAt), wsStatusIdx: index('inbox_task_ws_status_idx').on(table.workspaceId, table.status), responseMsgIdIdx: index('inbox_task_response_msg_id_idx').on(table.responseMessageId), emailMsgIdIdx: index('inbox_task_email_msg_id_idx').on(table.emailMessageId), }) ) export const mothershipInboxWebhook = pgTable('mothership_inbox_webhook', { id: text('id').primaryKey(), workspaceId: text('workspace_id') .notNull() .unique() .references(() => workspace.id, { onDelete: 'cascade' }), webhookId: text('webhook_id').notNull(), secret: text('secret').notNull(), createdAt: timestamp('created_at').notNull().defaultNow(), }) // ─── Sim Academy ───────────────────────────────────────────────────────────── export const academyCertStatusEnum = pgEnum('academy_cert_status', ['active', 'revoked', 'expired']) /** Partner certification records issued on course completion */ export const academyCertificate = pgTable( 'academy_certificate', { id: text('id').primaryKey(), userId: text('user_id') .notNull() .references(() => user.id, { onDelete: 'cascade' }), /** References the file-based course ID from lib/academy/content */ courseId: text('course_id').notNull(), status: academyCertStatusEnum('status').notNull().default('active'), issuedAt: timestamp('issued_at').notNull().defaultNow(), /** Optional expiry for recertification requirements */ expiresAt: timestamp('expires_at'), /** Human-readable unique certificate number, e.g. SIM-2026-00042 */ certificateNumber: text('certificate_number').notNull().unique(), /** Snapshot of name and other metadata at time of issue */ metadata: jsonb('metadata'), createdAt: timestamp('created_at').notNull().defaultNow(), }, (table) => ({ userIdIdx: index('academy_certificate_user_id_idx').on(table.userId), courseIdIdx: index('academy_certificate_course_id_idx').on(table.courseId), userCourseUnique: uniqueIndex('academy_certificate_user_course_unique').on( table.userId, table.courseId ), certNumberIdx: index('academy_certificate_number_idx').on(table.certificateNumber), statusIdx: index('academy_certificate_status_idx').on(table.status), }) )