${content.replace(/\\n/g, '
')}

`\n }]\n };\n \n // Add reply-to if this is part of a thread\n if (metadata.inReplyTo) {\n emailData.personalizations[0]['custom_args'] = {\n 'in_reply_to': metadata.inReplyTo\n };\n }\n \n const response = await fetch('https://api.sendgrid.com/v3/mail/send', {\n method: 'POST',\n headers: {\n 'Authorization': `Bearer ${this.apiKey}`,\n 'Content-Type': 'application/json'\n },\n body: JSON.stringify(emailData)\n });\n \n if (!response.ok) {\n const errorData = await response.text();\n throw new Error(`SendGrid API error: ${response.status} - ${errorData}`);\n }\n \n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'info',\n 'Email message sent successfully',\n {\n conversationId,\n to: emailInfo.to,\n from: emailInfo.from,\n subject: emailInfo.subject,\n duration\n },\n conversationId\n );\n \n // Record metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('email', 'send_message', duration, true)\n );\n \n } catch (error) {\n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'error',\n `Failed to send email message: ${error.message}`,\n {\n conversationId,\n content: content.substring(0, 100),\n error: error.message,\n duration\n },\n conversationId\n );\n \n // Record failed metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('email', 'send_message', duration, false)\n );\n \n throw error;\n }\n }\n \n /**\n * Validate SendGrid webhook signature\n */\n validateWebhook(payload: any, signature: string): boolean {\n try {\n const validation = this.validateCommonWebhook(payload, signature);\n if (!validation.isValid) {\n return false;\n }\n \n // TODO: Implement proper SendGrid signature verification\n return true; // For now, accept all webhooks\n \n } catch (error) {\n this.logActivity('error', `Email webhook validation failed: ${error.message}`, {\n signature: signature?.substring(0, 20) + '...',\n error: error.message\n });\n return false;\n }\n }\n \n /**\n * Extract email address from string (handles \"Name \" format)\n */\n private extractEmail(emailString: string): string | null {\n if (!emailString) return null;\n \n const emailRegex = /<([^>]+)>|([^\\s<>]+@[^\\s<>]+)/;\n const match = emailString.match(emailRegex);\n \n return match ? (match[1] || match[2]) : null;\n }\n \n /**\n * Strip HTML tags from content\n */\n private stripHtml(html: string): string {\n if (!html) return '';\n \n return html\n .replace(/<[^>]*>/g, '') // Remove HTML tags\n .replace(/ /g, ' ') // Replace with space\n .replace(/&/g, '&') // Replace & with &\n .replace(/</g, '<') // Replace < with <\n .replace(/>/g, '>') // Replace > with >\n .replace(/"/g, '\"') // Replace " with \"\n .replace(/'/g, \"'\") // Replace ' with '\n .replace(/\\s+/g, ' ') // Normalize whitespace\n .trim();\n }\n \n /**\n * Extract email info from conversation ID or metadata\n */\n private extractEmailInfo(conversationId: string, metadata: any): { \n from: string; \n to: string; \n subject: string \n } | null {\n // This is a simplified implementation\n // In practice, you'd need to store and retrieve email thread information\n \n if (metadata.fromEmail && metadata.toEmail) {\n return {\n from: 'yaptrap@yourdomain.com', // YapTrap's email\n to: metadata.fromEmail, // Reply to sender\n subject: `Re: ${metadata.subject || 'YapTrap Response'}`\n };\n }\n \n return null;\n }\n}",[],[151,292,307],{"_103":315,"_95":316,"_152":317,"_154":293,"_155":137,"_156":136,"_158":159,"_105":318,"_100":32,"_161":181,"_163":319,"_164":22,"_165":-5,"_166":320,"_98":321},"37dee474-8f1e-11f0-9eb6-0224a6c84d84","messenger.ts","37dee53c-8f1e-11f0-9eb6-0224a6c84d84",["D",1757602039740],"/**\n * Facebook Messenger platform handler for YapTrap via Puppeteer\n * Handles Messenger messaging through browser automation\n * \n * WARNING: This approach may violate Facebook's Terms of Service\n * Use at your own risk and consider official Messenger Platform API instead\n */\n\nimport { BasePlatformHandler } from './base.ts';\nimport type { NormalizedMessage } from '../../shared/types.ts';\nimport { generateConversationId, generateUserId } from '../../shared/utils.ts';\n\nexport class MessengerHandler extends BasePlatformHandler {\n private puppeteerEndpoint: string;\n private sessionData: any;\n private isLoggedIn: boolean = false;\n \n constructor() {\n super('messenger');\n this.puppeteerEndpoint = Deno.env.get('PUPPETEER_ENDPOINT') || 'http://localhost:3002';\n this.sessionData = null;\n }\n \n protected getRequiredEnvVars(): string[] {\n return ['MESSENGER_EMAIL', 'MESSENGER_PASSWORD', 'PUPPETEER_ENDPOINT'];\n }\n \n /**\n * Normalize Messenger message to internal format\n */\n async normalizeMessage(payload: any): Promise {\n try {\n // Handle Puppeteer-scraped message format\n if (!payload.messages || !Array.isArray(payload.messages)) {\n await this.logActivity('debug', 'Ignoring payload without messages array');\n return null;\n }\n \n for (const message of payload.messages) {\n if (!message.text || !message.sender || !message.conversationId) {\n continue;\n }\n \n // Skip messages from self\n if (message.sender.isMe) {\n continue;\n }\n \n const conversationId = generateConversationId('messenger', message.conversationId);\n const userId = generateUserId('messenger', message.sender.id || message.sender.name);\n \n // TODO: Implement user and conversation management for Messenger\n \n const normalizedMessage = this.createNormalizedMessage(\n message.text,\n conversationId,\n userId,\n {\n senderId: message.sender.id,\n senderName: message.sender.name,\n messengerConversationId: message.conversationId,\n timestamp: message.timestamp,\n originalPayload: message\n }\n );\n \n await this.logActivity(\n 'info',\n 'Messenger message normalized successfully',\n { \n messageId: normalizedMessage.id,\n conversationId,\n userId,\n senderName: message.sender.name\n },\n conversationId,\n userId\n );\n \n return normalizedMessage;\n }\n \n await this.logActivity('debug', 'No processable messages found in Messenger payload');\n return null;\n \n } catch (error) {\n await this.logActivity('error', `Failed to normalize Messenger message: ${error.message}`, {\n error: error.message,\n payload\n });\n return null;\n }\n }\n \n /**\n * Send Messenger message via Puppeteer automation\n */\n async sendMessage(conversationId: string, content: string, metadata: any = {}): Promise {\n const startTime = Date.now();\n \n try {\n if (!await this.authenticate()) {\n throw new Error('Messenger Puppeteer session not available');\n }\n \n // Extract Messenger conversation ID from our internal ID\n const messengerConversationId = this.extractMessengerConversationId(conversationId);\n if (!messengerConversationId) {\n throw new Error(`Invalid Messenger conversation ID: ${conversationId}`);\n }\n \n // Send message via Puppeteer API\n const messagePayload = {\n action: 'sendMessage',\n conversationId: messengerConversationId,\n message: content,\n sessionId: this.sessionData?.sessionId\n };\n \n const response = await this.callPuppeteerAPI(messagePayload);\n \n if (!response.success) {\n throw new Error(`Puppeteer API error: ${response.error}`);\n }\n \n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'info',\n 'Messenger message sent successfully via Puppeteer',\n {\n conversationId,\n messengerConversationId,\n messageId: response.messageId,\n duration\n },\n conversationId\n );\n \n // Record metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('messenger', 'send_message', duration, true)\n );\n \n } catch (error) {\n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'error',\n `Failed to send Messenger message via Puppeteer: ${error.message}`,\n {\n conversationId,\n content: content.substring(0, 100),\n error: error.message,\n duration\n },\n conversationId\n );\n \n // Record failed metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('messenger', 'send_message', duration, false)\n );\n \n throw error;\n }\n }\n \n /**\n * Validate Puppeteer webhook payload\n */\n validateWebhook(payload: any, signature: string): boolean {\n try {\n // Puppeteer doesn't use traditional webhook signatures\n // Instead, validate the payload structure and session\n if (!payload || typeof payload !== 'object') {\n return false;\n }\n \n // Check for required Puppeteer payload structure\n if (payload.sessionId && payload.messages) {\n return true;\n }\n \n // Check for authentication payload\n if (payload.action && payload.sessionId) {\n return true;\n }\n \n return false;\n \n } catch (error) {\n this.logActivity('error', `Messenger Puppeteer webhook validation failed: ${error.message}`, {\n error: error.message\n });\n return false;\n }\n }\n \n /**\n * Call Puppeteer API\n */\n private async callPuppeteerAPI(payload: any): Promise {\n try {\n const response = await fetch(`${this.puppeteerEndpoint}/api/messenger`, {\n method: 'POST',\n headers: {\n 'Content-Type': 'application/json',\n 'Authorization': `Bearer ${Deno.env.get('PUPPETEER_API_KEY') || 'default'}`\n },\n body: JSON.stringify(payload)\n });\n \n if (!response.ok) {\n throw new Error(`HTTP ${response.status}: ${response.statusText}`);\n }\n \n return await response.json();\n \n } catch (error) {\n throw new Error(`Puppeteer API call failed: ${error.message}`);\n }\n }\n \n /**\n * Initialize Puppeteer Messenger session\n */\n async initializePuppeteerSession(): Promise {\n try {\n await this.logActivity('info', 'Initializing Puppeteer Messenger session');\n \n const loginPayload = {\n action: 'login',\n email: Deno.env.get('MESSENGER_EMAIL'),\n password: Deno.env.get('MESSENGER_PASSWORD'),\n headless: Deno.env.get('PUPPETEER_HEADLESS') !== 'false'\n };\n \n const response = await this.callPuppeteerAPI(loginPayload);\n \n if (response.success) {\n this.sessionData = response.session;\n this.isLoggedIn = true;\n \n await this.logActivity('info', 'Puppeteer Messenger session initialized successfully', {\n sessionId: this.sessionData.sessionId\n });\n \n // Start message monitoring\n await this.startMessageMonitoring();\n \n return true;\n } else {\n throw new Error(response.error || 'Login failed');\n }\n \n } catch (error) {\n await this.logActivity('error', `Failed to initialize Puppeteer session: ${error.message}`);\n return false;\n }\n }\n \n /**\n * Start monitoring for new messages\n */\n private async startMessageMonitoring(): Promise {\n try {\n const monitorPayload = {\n action: 'startMonitoring',\n sessionId: this.sessionData.sessionId,\n webhookUrl: `${Deno.env.get('YAPTRAP_BASE_URL')}/webhooks/messenger`\n };\n \n await this.callPuppeteerAPI(monitorPayload);\n \n await this.logActivity('info', 'Started Messenger message monitoring');\n \n } catch (error) {\n await this.logActivity('error', `Failed to start message monitoring: ${error.message}`);\n }\n }\n \n /**\n * Get active conversations\n */\n async getActiveConversations(): Promise {\n try {\n const payload = {\n action: 'getConversations',\n sessionId: this.sessionData?.sessionId\n };\n \n const response = await this.callPuppeteerAPI(payload);\n \n if (response.success) {\n return response.conversations || [];\n }\n \n return [];\n \n } catch (error) {\n await this.logActivity('error', `Failed to get conversations: ${error.message}`);\n return [];\n }\n }\n \n /**\n * Extract Messenger conversation ID from our internal ID\n */\n private extractMessengerConversationId(conversationId: string): string | null {\n // Conversation ID format: conv_messenger_MESSENGERCONVID\n const parts = conversationId.split('_');\n if (parts.length >= 3 && parts[0] === 'conv' && parts[1] === 'messenger') {\n return parts.slice(2).join('_');\n }\n return null;\n }\n \n /**\n * Check if session is active\n */\n isSessionActive(): boolean {\n return this.isLoggedIn && this.sessionData !== null;\n }\n \n /**\n * Handle session events\n */\n async handleSessionEvent(event: string, data: any): Promise {\n switch (event) {\n case 'session.expired':\n await this.handleSessionExpired(data);\n break;\n case 'messages.received':\n await this.handleNewMessages(data);\n break;\n case 'login.required':\n await this.handleLoginRequired(data);\n break;\n default:\n await this.logActivity('debug', `Unhandled Puppeteer event: ${event}`, { data });\n }\n }\n \n private async handleSessionExpired(data: any): Promise {\n this.isLoggedIn = false;\n this.sessionData = null;\n \n await this.logActivity('warn', 'Puppeteer Messenger session expired', { data });\n \n // Attempt to re-login\n setTimeout(() => {\n this.initializePuppeteerSession();\n }, 5000);\n }\n \n private async handleNewMessages(data: any): Promise {\n try {\n const normalized = await this.normalizeMessage(data);\n if (normalized) {\n // Forward to message processor\n const { MessageProcessor } = await import('../ai/processor.ts');\n const processor = new MessageProcessor();\n await processor.processMessage(normalized);\n }\n } catch (error) {\n await this.logActivity('error', `Failed to process Puppeteer message: ${error.message}`);\n }\n }\n \n private async handleLoginRequired(data: any): Promise {\n await this.logActivity('warn', 'Messenger login required', { data });\n \n // Attempt to re-login\n await this.initializePuppeteerSession();\n }\n \n /**\n * Cleanup session\n */\n async cleanup(): Promise {\n try {\n if (this.sessionData) {\n const payload = {\n action: 'cleanup',\n sessionId: this.sessionData.sessionId\n };\n \n await this.callPuppeteerAPI(payload);\n }\n \n this.isLoggedIn = false;\n this.sessionData = null;\n \n await this.logActivity('info', 'Puppeteer Messenger session cleaned up');\n \n } catch (error) {\n await this.logActivity('error', `Failed to cleanup session: ${error.message}`);\n }\n }\n}",[],[151,292,316],{"_103":323,"_95":324,"_152":325,"_154":293,"_155":137,"_156":326,"_158":159,"_105":327,"_100":200,"_161":181,"_163":328,"_164":22,"_165":-5,"_166":329,"_98":330},"677c75de-8f19-11f0-863c-0224a6c84d84","slack.ts","9348e5fa-8eae-11f0-84d3-0224a6c84d84",59,["D",1757599972146],"/**\n * Slack platform handler for YapTrap\n * Handles Slack Events API and Web API integration\n */\n\nimport { BasePlatformHandler } from './base.ts';\nimport type { NormalizedMessage } from '../../shared/types.ts';\nimport { generateConversationId, generateUserId } from '../../shared/utils.ts';\nimport { ConversationQueries, UserQueries, ConversationParticipantQueries } from '../database/queries.ts';\nimport { MetricsCollector } from '../monitoring/metrics.ts';\n\nexport class SlackHandler extends BasePlatformHandler {\n private botToken: string;\n private signingSecret: string;\n \n constructor() {\n super('slack');\n this.botToken = Deno.env.get('SLACK_BOT_TOKEN') || '';\n this.signingSecret = Deno.env.get('SLACK_SIGNING_SECRET') || '';\n }\n \n protected getRequiredEnvVars(): string[] {\n return ['SLACK_BOT_TOKEN', 'SLACK_SIGNING_SECRET'];\n }\n \n /**\n * Normalize Slack event to internal message format\n */\n async normalizeMessage(payload: any): Promise {\n try {\n // Handle different Slack event types\n if (payload.type === 'url_verification') {\n // This is handled in the webhook route\n return null;\n }\n \n if (payload.type !== 'event_callback') {\n await this.logActivity('debug', 'Ignoring non-event_callback payload', { type: payload.type });\n return null;\n }\n \n const event = payload.event;\n \n if (!event || event.type !== 'message') {\n await this.logActivity('debug', 'Ignoring non-message event', { eventType: event?.type });\n return null;\n }\n \n // Skip bot messages and message changes\n if (event.bot_id || event.subtype === 'bot_message' || event.subtype === 'message_changed') {\n await this.logActivity('debug', 'Ignoring bot message or message change', { \n botId: event.bot_id, \n subtype: event.subtype \n });\n return null;\n }\n \n // Skip messages without text\n if (!event.text || event.text.trim() === '') {\n await this.logActivity('debug', 'Ignoring message without text');\n return null;\n }\n \n // Extract user and conversation info\n const userId = generateUserId('slack', event.user);\n const conversationId = generateConversationId('slack', event.channel);\n \n // Ensure user exists in database\n await this.ensureUserExists(event.user, userId);\n \n // Ensure conversation exists in database\n await this.ensureConversationExists(event.channel, conversationId);\n \n // Ensure user is participant in conversation\n await this.ensureParticipant(conversationId, userId);\n \n // Create normalized message\n const normalizedMessage = this.createNormalizedMessage(\n event.text,\n conversationId,\n userId,\n {\n slackUserId: event.user,\n slackChannelId: event.channel,\n slackTimestamp: event.ts,\n slackThreadTs: event.thread_ts,\n originalPayload: event\n }\n );\n \n await this.logActivity(\n 'info',\n 'Message normalized successfully',\n { \n messageId: normalizedMessage.id,\n conversationId,\n userId,\n contentLength: event.text.length\n },\n conversationId,\n userId\n );\n \n return normalizedMessage;\n \n } catch (error) {\n await this.logActivity('error', `Failed to normalize Slack message: ${error.message}`, {\n error: error.message,\n stack: error.stack,\n payload\n });\n return null;\n }\n }\n \n /**\n * Send message to Slack channel\n */\n async sendMessage(conversationId: string, content: string, metadata: any = {}): Promise {\n const startTime = Date.now();\n \n try {\n if (!await this.authenticate()) {\n throw new Error('Slack authentication failed');\n }\n \n // Extract Slack channel ID from conversation ID\n const slackChannelId = this.extractSlackChannelId(conversationId);\n \n if (!slackChannelId) {\n throw new Error(`Invalid Slack conversation ID: ${conversationId}`);\n }\n \n // Prepare message payload\n const messagePayload: any = {\n channel: slackChannelId,\n text: content,\n as_user: false,\n username: 'YapTrap',\n icon_emoji: ':robot_face:'\n };\n \n // Add thread support if specified\n if (metadata.threadTs) {\n messagePayload.thread_ts = metadata.threadTs;\n }\n \n // Send message via Slack Web API\n const response = await this.callSlackAPI('chat.postMessage', messagePayload);\n \n if (!response.ok) {\n throw new Error(`Slack API error: ${response.error}`);\n }\n \n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'info',\n 'Message sent to Slack successfully',\n {\n conversationId,\n slackChannelId,\n messageTs: response.ts,\n duration\n },\n conversationId\n );\n \n // Record metrics\n await MetricsCollector.recordPlatformAPI('slack', 'send_message', duration, true);\n \n } catch (error) {\n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'error',\n `Failed to send Slack message: ${error.message}`,\n {\n conversationId,\n content: content.substring(0, 100),\n error: error.message,\n duration\n },\n conversationId\n );\n \n // Record failed metrics\n await MetricsCollector.recordPlatformAPI('slack', 'send_message', duration, false);\n \n throw error;\n }\n }\n \n /**\n * Validate Slack webhook signature\n */\n validateWebhook(payload: any, signature: string): boolean {\n try {\n if (!signature || !this.signingSecret) {\n return false;\n }\n \n // For development, we'll accept all webhooks with a signature\n // In production, implement proper HMAC verification:\n /*\n const timestamp = request.headers['x-slack-request-timestamp'];\n const body = JSON.stringify(payload);\n const sig_basestring = 'v0:' + timestamp + ':' + body;\n const my_signature = 'v0=' + crypto.createHmac('sha256', this.signingSecret)\n .update(sig_basestring, 'utf8').digest('hex');\n return crypto.timingSafeEqual(Buffer.from(my_signature, 'utf8'), Buffer.from(signature, 'utf8'));\n */\n \n return signature.startsWith('v0='); // Basic validation for now\n \n } catch (error) {\n this.logActivity('error', `Slack webhook validation failed: ${error.message}`, {\n signature: signature?.substring(0, 20) + '...',\n error: error.message\n });\n return false;\n }\n }\n \n /**\n * Call Slack Web API\n */\n private async callSlackAPI(method: string, payload: any): Promise {\n const url = `https://slack.com/api/${method}`;\n \n const response = await fetch(url, {\n method: 'POST',\n headers: {\n 'Authorization': `Bearer ${this.botToken}`,\n 'Content-Type': 'application/json'\n },\n body: JSON.stringify(payload)\n });\n \n if (!response.ok) {\n throw new Error(`HTTP ${response.status}: ${response.statusText}`);\n }\n \n return await response.json();\n }\n \n /**\n * Ensure user exists in database\n */\n private async ensureUserExists(slackUserId: string, userId: string): Promise {\n try {\n const existingUser = await UserQueries.findById(userId);\n \n if (!existingUser) {\n // Fetch user info from Slack API\n const userInfo = await this.getUserInfo(slackUserId);\n \n await UserQueries.create({\n id: userId,\n platform: 'slack',\n platformUserId: slackUserId,\n displayName: userInfo.display_name || userInfo.real_name || userInfo.name || 'Unknown User',\n createdAt: new Date(),\n updatedAt: new Date(),\n preferences: {}\n });\n \n await this.logActivity('info', 'Created new user', { userId, slackUserId }, undefined, userId);\n }\n } catch (error) {\n await this.logActivity('error', `Failed to ensure user exists: ${error.message}`, {\n userId,\n slackUserId,\n error: error.message\n });\n }\n }\n \n /**\n * Ensure conversation exists in database\n */\n private async ensureConversationExists(slackChannelId: string, conversationId: string): Promise {\n try {\n const existingConversation = await ConversationQueries.findById(conversationId);\n \n if (!existingConversation) {\n // Fetch channel info from Slack API\n const channelInfo = await this.getChannelInfo(slackChannelId);\n \n await ConversationQueries.create({\n id: conversationId,\n platform: 'slack',\n platformId: slackChannelId,\n name: channelInfo.name || 'Unknown Channel',\n createdAt: new Date(),\n lastActivity: new Date(),\n settings: {\n yapTrapEnabled: true,\n yapTrapSettings: {\n conversationId,\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n },\n retentionDays: 90,\n adminUsers: []\n },\n isActive: true\n });\n \n await this.logActivity('info', 'Created new conversation', { \n conversationId, \n slackChannelId,\n channelName: channelInfo.name\n }, conversationId);\n }\n } catch (error) {\n await this.logActivity('error', `Failed to ensure conversation exists: ${error.message}`, {\n conversationId,\n slackChannelId,\n error: error.message\n });\n }\n }\n \n /**\n * Ensure user is participant in conversation\n */\n private async ensureParticipant(conversationId: string, userId: string): Promise {\n try {\n await ConversationParticipantQueries.add({\n conversationId,\n userId,\n joinedAt: new Date(),\n role: 'member',\n isActive: true\n });\n \n // Update last seen\n await ConversationParticipantQueries.updateLastSeen(conversationId, userId);\n \n } catch (error) {\n await this.logActivity('error', `Failed to ensure participant: ${error.message}`, {\n conversationId,\n userId,\n error: error.message\n });\n }\n }\n \n /**\n * Get user info from Slack API\n */\n private async getUserInfo(slackUserId: string): Promise {\n try {\n const response = await this.callSlackAPI('users.info', { user: slackUserId });\n \n if (response.ok && response.user) {\n return response.user;\n }\n \n return { name: 'Unknown User' };\n } catch (error) {\n await this.logActivity('warn', `Failed to fetch user info: ${error.message}`, { slackUserId });\n return { name: 'Unknown User' };\n }\n }\n \n /**\n * Get channel info from Slack API\n */\n private async getChannelInfo(slackChannelId: string): Promise {\n try {\n const response = await this.callSlackAPI('conversations.info', { channel: slackChannelId });\n \n if (response.ok && response.channel) {\n return response.channel;\n }\n \n return { name: 'Unknown Channel' };\n } catch (error) {\n await this.logActivity('warn', `Failed to fetch channel info: ${error.message}`, { slackChannelId });\n return { name: 'Unknown Channel' };\n }\n }\n \n /**\n * Extract Slack channel ID from conversation ID\n */\n private extractSlackChannelId(conversationId: string): string | null {\n // Conversation ID format: conv_slack_CHANNELID\n const parts = conversationId.split('_');\n if (parts.length >= 3 && parts[0] === 'conv' && parts[1] === 'slack') {\n return parts.slice(2).join('_');\n }\n return null;\n }\n}",[],[151,292,324],{"_103":332,"_95":333,"_152":334,"_154":293,"_155":137,"_156":335,"_158":159,"_105":336,"_100":200,"_161":181,"_163":337,"_164":22,"_165":-5,"_166":338,"_98":339},"70a1c9fc-8f19-11f0-8160-0224a6c84d84","sms.ts","9f44e390-8eae-11f0-9c6e-0224a6c84d84",60,["D",1757599987469],"/**\n * SMS/MMS platform handler for YapTrap via Telnyx\n * Handles SMS/MMS messaging through Telnyx API\n */\n\nimport { BasePlatformHandler } from './base.ts';\nimport type { NormalizedMessage } from '../../shared/types.ts';\nimport { generateConversationId, generateUserId } from '../../shared/utils.ts';\n\nexport class SMSHandler extends BasePlatformHandler {\n private apiKey: string;\n \n constructor() {\n super('sms');\n this.apiKey = Deno.env.get('TELNYX_API_KEY') || '';\n }\n \n protected getRequiredEnvVars(): string[] {\n return ['TELNYX_API_KEY'];\n }\n \n /**\n * Normalize Telnyx SMS webhook to internal message format\n */\n async normalizeMessage(payload: any): Promise {\n try {\n // Handle Telnyx webhook payload structure\n if (!payload.data || payload.data.event_type !== 'message.received') {\n await this.logActivity('debug', 'Ignoring non-message event', { eventType: payload.data?.event_type });\n return null;\n }\n \n const message = payload.data.payload;\n \n if (!message.text || message.text.trim() === '') {\n await this.logActivity('debug', 'Ignoring message without text');\n return null;\n }\n \n // For SMS, conversation is typically between two phone numbers\n const fromNumber = message.from.phone_number;\n const toNumber = message.to[0].phone_number;\n \n // Create conversation ID based on both numbers (sorted for consistency)\n const numbers = [fromNumber, toNumber].sort();\n const conversationId = generateConversationId('sms', numbers.join('_'));\n const userId = generateUserId('sms', fromNumber);\n \n // TODO: Implement user and conversation management for SMS\n \n const normalizedMessage = this.createNormalizedMessage(\n message.text,\n conversationId,\n userId,\n {\n fromNumber,\n toNumber,\n telnyxMessageId: message.id,\n originalPayload: message\n }\n );\n \n await this.logActivity(\n 'info',\n 'SMS message normalized successfully',\n { \n messageId: normalizedMessage.id,\n conversationId,\n userId,\n fromNumber,\n toNumber\n },\n conversationId,\n userId\n );\n \n return normalizedMessage;\n \n } catch (error) {\n await this.logActivity('error', `Failed to normalize SMS message: ${error.message}`, {\n error: error.message,\n payload\n });\n return null;\n }\n }\n \n /**\n * Send SMS message via Telnyx API\n */\n async sendMessage(conversationId: string, content: string, metadata: any = {}): Promise {\n const startTime = Date.now();\n \n try {\n if (!await this.authenticate()) {\n throw new Error('Telnyx authentication failed');\n }\n \n // Extract phone numbers from conversation ID\n const phoneNumbers = this.extractPhoneNumbers(conversationId);\n if (!phoneNumbers) {\n throw new Error(`Invalid SMS conversation ID: ${conversationId}`);\n }\n \n // Send SMS via Telnyx API\n const response = await fetch('https://api.telnyx.com/v2/messages', {\n method: 'POST',\n headers: {\n 'Authorization': `Bearer ${this.apiKey}`,\n 'Content-Type': 'application/json'\n },\n body: JSON.stringify({\n from: phoneNumbers.from,\n to: phoneNumbers.to,\n text: content,\n messaging_profile_id: metadata.messagingProfileId || Deno.env.get('TELNYX_MESSAGING_PROFILE_ID')\n })\n });\n \n if (!response.ok) {\n const errorData = await response.json();\n throw new Error(`Telnyx API error: ${response.status} - ${errorData.errors?.[0]?.detail || response.statusText}`);\n }\n \n const result = await response.json();\n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'info',\n 'SMS message sent successfully',\n {\n conversationId,\n to: phoneNumbers.to,\n from: phoneNumbers.from,\n messageId: result.data?.id,\n duration\n },\n conversationId\n );\n \n // Record metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('sms', 'send_message', duration, true)\n );\n \n } catch (error) {\n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'error',\n `Failed to send SMS message: ${error.message}`,\n {\n conversationId,\n content: content.substring(0, 100),\n error: error.message,\n duration\n },\n conversationId\n );\n \n // Record failed metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('sms', 'send_message', duration, false)\n );\n \n throw error;\n }\n }\n \n /**\n * Validate Telnyx webhook signature\n */\n validateWebhook(payload: any, signature: string): boolean {\n try {\n const validation = this.validateCommonWebhook(payload, signature);\n if (!validation.isValid) {\n return false;\n }\n \n // TODO: Implement proper Telnyx signature verification\n return true; // For now, accept all webhooks\n \n } catch (error) {\n this.logActivity('error', `SMS webhook validation failed: ${error.message}`, {\n signature: signature?.substring(0, 20) + '...',\n error: error.message\n });\n return false;\n }\n }\n \n /**\n * Extract phone numbers from conversation ID\n */\n private extractPhoneNumbers(conversationId: string): { from: string; to: string } | null {\n // Conversation ID format: conv_sms_NUMBER1_NUMBER2\n const parts = conversationId.split('_');\n if (parts.length >= 4 && parts[0] === 'conv' && parts[1] === 'sms') {\n const numbers = parts.slice(2);\n return {\n from: numbers[1], // YapTrap's number (second in sorted order)\n to: numbers[0] // User's number (first in sorted order)\n };\n }\n return null;\n }\n}",[],[151,292,333],{"_103":341,"_95":342,"_152":343,"_154":293,"_155":137,"_156":344,"_158":159,"_105":345,"_100":346,"_161":181,"_163":347,"_164":22,"_165":-5,"_166":348,"_98":349},"1f300020-8f1e-11f0-8fd6-0224a6c84d84","whatsapp.ts","b8d47924-8eae-11f0-8f47-0224a6c84d84",68,["D",1757601998339],2,"/**\n * WhatsApp platform handler for YapTrap via Baileys\n * Handles WhatsApp messaging through Baileys library with WebSocket connection\n */\n\nimport { BasePlatformHandler } from './base.ts';\nimport type { NormalizedMessage } from '../../shared/types.ts';\nimport { generateConversationId, generateUserId } from '../../shared/utils.ts';\n\nexport class WhatsAppHandler extends BasePlatformHandler {\n private sessionId: string;\n private webhookUrl: string;\n private isConnected: boolean = false;\n \n constructor() {\n super('whatsapp');\n this.sessionId = Deno.env.get('WHATSAPP_SESSION_ID') || 'yaptrap-session';\n this.webhookUrl = Deno.env.get('WHATSAPP_WEBHOOK_URL') || '';\n }\n \n protected getRequiredEnvVars(): string[] {\n return ['WHATSAPP_SESSION_ID']; // Baileys doesn't need API keys, just session management\n }\n \n /**\n * Normalize Baileys WhatsApp message to internal format\n */\n async normalizeMessage(payload: any): Promise {\n try {\n // Handle Baileys message format\n if (!payload.messages || !Array.isArray(payload.messages)) {\n await this.logActivity('debug', 'Ignoring payload without messages array');\n return null;\n }\n \n for (const message of payload.messages) {\n if (!message.message || !message.key) {\n continue;\n }\n \n // Extract text content from various message types\n let textContent = '';\n if (message.message.conversation) {\n textContent = message.message.conversation;\n } else if (message.message.extendedTextMessage?.text) {\n textContent = message.message.extendedTextMessage.text;\n } else if (message.message.imageMessage?.caption) {\n textContent = message.message.imageMessage.caption;\n } else {\n continue; // Skip non-text messages for now\n }\n \n if (!textContent.trim()) {\n continue;\n }\n \n // Extract sender and conversation info\n const fromNumber = message.key.remoteJid?.replace('@s.whatsapp.net', '') || '';\n const isGroup = message.key.remoteJid?.includes('@g.us');\n \n // For groups, use group ID; for individual chats, create conversation between bot and user\n const conversationId = isGroup \n ? generateConversationId('whatsapp', message.key.remoteJid)\n : generateConversationId('whatsapp', `${fromNumber}_bot`);\n \n const userId = generateUserId('whatsapp', fromNumber);\n \n // TODO: Implement user and conversation management for WhatsApp\n \n const normalizedMessage = this.createNormalizedMessage(\n textContent,\n conversationId,\n userId,\n {\n fromNumber,\n isGroup,\n groupId: isGroup ? message.key.remoteJid : null,\n messageId: message.key.id,\n timestamp: message.messageTimestamp,\n originalPayload: message\n }\n );\n \n await this.logActivity(\n 'info',\n 'WhatsApp message normalized successfully',\n { \n messageId: normalizedMessage.id,\n conversationId,\n userId,\n fromNumber,\n isGroup\n },\n conversationId,\n userId\n );\n \n return normalizedMessage;\n }\n \n await this.logActivity('debug', 'No processable messages found in WhatsApp payload');\n return null;\n \n } catch (error) {\n await this.logActivity('error', `Failed to normalize WhatsApp message: ${error.message}`, {\n error: error.message,\n payload\n });\n return null;\n }\n }\n \n /**\n * Send WhatsApp message via Baileys WebSocket connection\n */\n async sendMessage(conversationId: string, content: string, metadata: any = {}): Promise {\n const startTime = Date.now();\n \n try {\n if (!await this.authenticate()) {\n throw new Error('WhatsApp Baileys connection not available');\n }\n \n // Extract phone number or group ID from conversation ID\n const target = this.extractWhatsAppTarget(conversationId);\n if (!target) {\n throw new Error(`Invalid WhatsApp conversation ID: ${conversationId}`);\n }\n \n // Send message via Baileys WebSocket API\n const messagePayload = {\n sessionId: this.sessionId,\n to: target,\n message: {\n text: content\n }\n };\n \n // In a real implementation, this would connect to your Baileys WebSocket server\n // For now, we'll simulate the API call\n const response = await this.callBaileysAPI('sendMessage', messagePayload);\n \n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'info',\n 'WhatsApp message sent successfully via Baileys',\n {\n conversationId,\n to: target,\n messageId: response?.messageId,\n duration\n },\n conversationId\n );\n \n // Record metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('whatsapp', 'send_message', duration, true)\n );\n \n } catch (error) {\n const duration = Date.now() - startTime;\n \n await this.logActivity(\n 'error',\n `Failed to send WhatsApp message via Baileys: ${error.message}`,\n {\n conversationId,\n content: content.substring(0, 100),\n error: error.message,\n duration\n },\n conversationId\n );\n \n // Record failed metrics\n await import('../monitoring/metrics.ts').then(({ MetricsCollector }) => \n MetricsCollector.recordPlatformAPI('whatsapp', 'send_message', duration, false)\n );\n \n throw error;\n }\n }\n \n /**\n * Validate Baileys webhook payload\n */\n validateWebhook(payload: any, signature: string): boolean {\n try {\n // Baileys doesn't use traditional webhook signatures\n // Instead, validate the payload structure and session\n if (!payload || typeof payload !== 'object') {\n return false;\n }\n \n // Check for required Baileys payload structure\n if (payload.sessionId && payload.sessionId === this.sessionId) {\n return true;\n }\n \n // Check for message structure\n if (payload.messages && Array.isArray(payload.messages)) {\n return true;\n }\n \n return false;\n \n } catch (error) {\n this.logActivity('error', `WhatsApp Baileys webhook validation failed: ${error.message}`, {\n error: error.message\n });\n return false;\n }\n }\n \n /**\n * Call Baileys WebSocket API\n */\n private async callBaileysAPI(method: string, payload: any): Promise {\n // In a real implementation, this would connect to your Baileys WebSocket server\n // For now, we'll simulate the API call\n \n const baileysEndpoint = Deno.env.get('BAILEYS_WEBSOCKET_URL') || 'ws://localhost:3001';\n \n try {\n // Simulate WebSocket API call\n await this.logActivity('debug', `Calling Baileys API: ${method}`, { payload });\n \n // In production, you would:\n // 1. Connect to WebSocket server running Baileys\n // 2. Send the message payload\n // 3. Wait for confirmation\n \n return {\n success: true,\n messageId: `baileys_${Date.now()}`,\n timestamp: new Date().toISOString()\n };\n \n } catch (error) {\n throw new Error(`Baileys API call failed: ${error.message}`);\n }\n }\n \n /**\n * Extract WhatsApp target (phone number or group ID) from conversation ID\n */\n private extractWhatsAppTarget(conversationId: string): string | null {\n // Conversation ID format: conv_whatsapp_TARGET\n const parts = conversationId.split('_');\n if (parts.length >= 3 && parts[0] === 'conv' && parts[1] === 'whatsapp') {\n const target = parts.slice(2).join('_');\n \n // Convert back to WhatsApp format\n if (target.includes('g.us')) {\n return target; // Group chat\n } else {\n return `${target}@s.whatsapp.net`; // Individual chat\n }\n }\n return null;\n }\n \n /**\n * Initialize Baileys connection\n */\n async initializeBaileys(): Promise {\n try {\n await this.logActivity('info', 'Initializing Baileys WhatsApp connection', {\n sessionId: this.sessionId\n });\n \n // In a real implementation, you would:\n // 1. Start Baileys WebSocket server\n // 2. Handle QR code scanning for authentication\n // 3. Manage session persistence\n // 4. Set up message event handlers\n \n this.isConnected = true;\n \n await this.logActivity('info', 'Baileys WhatsApp connection initialized successfully');\n return true;\n \n } catch (error) {\n await this.logActivity('error', `Failed to initialize Baileys: ${error.message}`);\n return false;\n }\n }\n \n /**\n * Get connection status\n */\n isConnectionActive(): boolean {\n return this.isConnected;\n }\n \n /**\n * Handle Baileys connection events\n */\n async handleConnectionEvent(event: string, data: any): Promise {\n switch (event) {\n case 'connection.update':\n await this.handleConnectionUpdate(data);\n break;\n case 'messages.upsert':\n await this.handleNewMessages(data);\n break;\n case 'qr':\n await this.handleQRCode(data);\n break;\n default:\n await this.logActivity('debug', `Unhandled Baileys event: ${event}`, { data });\n }\n }\n \n private async handleConnectionUpdate(update: any): Promise {\n const { connection, lastDisconnect } = update;\n \n if (connection === 'close') {\n this.isConnected = false;\n await this.logActivity('warn', 'Baileys connection closed', { lastDisconnect });\n } else if (connection === 'open') {\n this.isConnected = true;\n await this.logActivity('info', 'Baileys connection opened successfully');\n }\n }\n \n private async handleNewMessages(data: any): Promise {\n const { messages } = data;\n \n for (const message of messages) {\n try {\n const normalized = await this.normalizeMessage({ messages: [message] });\n if (normalized) {\n // Forward to message processor\n const { MessageProcessor } = await import('../ai/processor.ts');\n const processor = new MessageProcessor();\n await processor.processMessage(normalized);\n }\n } catch (error) {\n await this.logActivity('error', `Failed to process Baileys message: ${error.message}`);\n }\n }\n }\n \n private async handleQRCode(qr: string): Promise {\n await this.logActivity('info', 'QR Code received for WhatsApp authentication', {\n qrLength: qr.length\n });\n \n // In a real implementation, you would:\n // 1. Display QR code to admin\n // 2. Wait for scanning\n // 3. Save session data\n }\n}",[],[151,292,342],[151,292],{"_103":352,"_95":353,"_152":354,"_154":153,"_155":137,"_156":355,"_158":159,"_105":356,"_100":32,"_161":162,"_163":-5,"_164":22,"_165":-5,"_166":357,"_98":384},"ec266bda-8ead-11f0-992a-0224a6c84d84","routes","63ef8ffb-ad6a-4521-8ca8-017fde5fbeb5",10,["D",1757553809085],[358,367,376],{"_103":359,"_95":360,"_152":361,"_154":354,"_155":137,"_156":362,"_158":159,"_105":363,"_100":259,"_161":181,"_163":364,"_164":22,"_165":-5,"_166":365,"_98":366},"e6bdb6dc-8f19-11f0-a076-0224a6c84d84","admin.ts","073a3672-8eae-11f0-b9a3-0224a6c84d84",67,["D",1757600185651],"/**\n * Admin API routes for YapTrap management and monitoring\n * Provides endpoints for configuration, monitoring, and system management\n */\n\nimport type { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { \n ConversationQueries, \n UserQueries, \n MessageQueries, \n SystemLogQueries,\n ConfigurationQueries \n} from '../database/queries.ts';\nimport { HealthMonitor } from '../monitoring/health.ts';\nimport { MetricsCollector } from '../monitoring/metrics.ts';\nimport { getDatabaseStats } from '../database/schema.ts';\n\nimport type { QueryOptions } from '../../shared/types.ts';\n\nexport function setupAdminRoutes(app: Hono): void {\n \n // Admin API base path\n const adminPath = '/admin';\n \n // Conversations management\n app.get(`${adminPath}/conversations`, async (c) => {\n try {\n const page = parseInt(c.req.query('page') || '1');\n const limit = parseInt(c.req.query('limit') || '20');\n const offset = (page - 1) * limit;\n \n const options: QueryOptions = { limit, offset };\n const result = await ConversationQueries.findActive(options);\n \n return c.json({\n success: true,\n data: result.data,\n pagination: {\n page,\n limit,\n total: result.total,\n hasMore: result.hasMore,\n totalPages: Math.ceil(result.total / limit)\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch conversations: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/conversations/:id`, async (c) => {\n try {\n const id = c.req.param('id');\n const conversation = await ConversationQueries.findById(id);\n \n if (!conversation) {\n return c.json({\n success: false,\n error: 'Conversation not found',\n timestamp: new Date()\n }, 404);\n }\n \n // Get recent messages for this conversation\n const messagesResult = await MessageQueries.findByConversationId(id, { limit: 50 });\n \n return c.json({\n success: true,\n data: {\n conversation,\n recentMessages: messagesResult.data,\n messageCount: messagesResult.total\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch conversation: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.put(`${adminPath}/conversations/:id/settings`, async (c) => {\n try {\n const id = c.req.param('id');\n const settings = await c.req.json();\n \n await ConversationQueries.updateSettings(id, settings);\n \n await SystemLogQueries.log(\n 'info', \n 'admin-api', \n `Updated conversation settings`,\n { conversationId: id, settings }\n );\n \n return c.json({\n success: true,\n message: 'Settings updated successfully',\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to update conversation settings: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Users management\n app.get(`${adminPath}/users`, async (c) => {\n try {\n const search = c.req.query('search');\n const platform = c.req.query('platform');\n \n // For now, return a simple response - can be enhanced with proper search\n return c.json({\n success: true,\n data: [],\n message: 'User search functionality coming soon',\n filters: { search, platform },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch users: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/users/:id`, async (c) => {\n try {\n const id = c.req.param('id');\n const user = await UserQueries.findById(id);\n \n if (!user) {\n return c.json({\n success: false,\n error: 'User not found',\n timestamp: new Date()\n }, 404);\n }\n \n return c.json({\n success: true,\n data: user,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch user: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // System logs\n app.get(`${adminPath}/logs`, async (c) => {\n try {\n const page = parseInt(c.req.query('page') || '1');\n const limit = parseInt(c.req.query('limit') || '100');\n const level = c.req.query('level');\n const component = c.req.query('component');\n const offset = (page - 1) * limit;\n \n const options: QueryOptions = { \n limit, \n offset,\n filters: { level, component }\n };\n \n const result = await SystemLogQueries.findRecent(options);\n \n return c.json({\n success: true,\n data: result.data,\n pagination: {\n page,\n limit,\n total: result.total,\n hasMore: result.hasMore,\n totalPages: Math.ceil(result.total / limit)\n },\n filters: { level, component },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch logs: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // System metrics\n app.get(`${adminPath}/metrics`, async (c) => {\n try {\n const timeRange = c.req.query('range') || '1h';\n const metrics = await MetricsCollector.getMetrics(timeRange);\n \n return c.json({\n success: true,\n data: metrics,\n timeRange,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch metrics: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/metrics/current`, async (c) => {\n try {\n const metrics = await MetricsCollector.getCurrentMetrics();\n \n return c.json({\n success: true,\n data: metrics,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch current metrics: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // System health\n app.get(`${adminPath}/health`, async (c) => {\n try {\n const health = await HealthMonitor.getSystemHealth();\n const dbStats = await getDatabaseStats();\n \n return c.json({\n success: true,\n data: {\n ...health,\n database: dbStats\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch health status: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Configuration management\n app.get(`${adminPath}/config`, async (c) => {\n try {\n const scope = c.req.query('scope') || 'global';\n const scopeId = c.req.query('scopeId');\n \n const configs = await ConfigurationQueries.getAll(scope, scopeId);\n \n return c.json({\n success: true,\n data: configs,\n scope,\n scopeId,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch configuration: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/config/:key`, async (c) => {\n try {\n const key = c.req.param('key');\n const scope = c.req.query('scope') || 'global';\n const scopeId = c.req.query('scopeId');\n \n const value = await ConfigurationQueries.get(key, scope, scopeId);\n \n if (value === null) {\n return c.json({\n success: false,\n error: 'Configuration key not found',\n timestamp: new Date()\n }, 404);\n }\n \n return c.json({\n success: true,\n data: { key, value, scope, scopeId },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch configuration key: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.put(`${adminPath}/config/:key`, async (c) => {\n try {\n const key = c.req.param('key');\n const { value, scope = 'global', scopeId } = await c.req.json();\n \n await ConfigurationQueries.set(key, value, scope, scopeId, 'admin');\n \n await SystemLogQueries.log(\n 'info',\n 'admin-api',\n `Configuration updated`,\n { key, scope, scopeId, value }\n );\n \n return c.json({\n success: true,\n message: 'Configuration updated successfully',\n data: { key, value, scope, scopeId },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to update configuration: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // System operations\n app.post(`${adminPath}/operations/cleanup`, async (c) => {\n try {\n // This would trigger database cleanup\n await SystemLogQueries.log('info', 'admin-api', 'Manual cleanup operation triggered');\n \n return c.json({\n success: true,\n message: 'Cleanup operation initiated',\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to trigger cleanup: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.post(`${adminPath}/operations/test-message`, async (c) => {\n try {\n const { conversationId, content } = await c.req.json();\n \n if (!conversationId || !content) {\n return c.json({\n success: false,\n error: 'conversationId and content are required',\n timestamp: new Date()\n }, 400);\n }\n \n // Create a test YapTrap message\n const messageId = await MessageQueries.createYapTrapMessage(\n conversationId, \n content,\n { source: 'admin-test', timestamp: new Date() }\n );\n \n await SystemLogQueries.log(\n 'info',\n 'admin-api',\n 'Test message created',\n { conversationId, messageId, content }\n );\n \n return c.json({\n success: true,\n message: 'Test message created successfully',\n data: { messageId, conversationId, content },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to create test message: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Simple AI test without database\n app.post(`${adminPath}/operations/test-ai-simple`, async (c) => {\n try {\n const { message } = await c.req.json();\n \n if (!message) {\n return c.json({\n success: false,\n error: 'message is required',\n timestamp: new Date()\n }, 400);\n }\n \n // Test OpenAI directly\n const { OpenAI } = await import(\"https://esm.town/v/std/openai\");\n const openai = new OpenAI();\n \n const completion = await openai.chat.completions.create({\n messages: [\n { \n role: \"system\", \n content: \"You are YapTrap, a witty and slightly sarcastic chatbot. Respond with a short, clever comment about the user's message. Keep it under 100 characters.\" \n },\n { role: \"user\", content: message }\n ],\n model: \"gpt-4o-mini\",\n max_tokens: 50,\n temperature: 0.8\n });\n \n const response = completion.choices[0]?.message?.content || \"I'm speechless... and that's saying something! 🤖\";\n \n return c.json({\n success: true,\n data: {\n input: message,\n response: response,\n provider: 'openai',\n model: 'gpt-4o-mini',\n tokensUsed: completion.usage?.total_tokens || 0\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Test AI response generation with full context\n app.post(`${adminPath}/operations/test-ai-context`, async (c) => {\n try {\n const { message } = await c.req.json();\n \n if (!message) {\n return c.json({\n success: false,\n error: 'message is required',\n timestamp: new Date()\n }, 400);\n }\n \n // Import AI components\n const { AIEngine } = await import('../ai/engine.ts');\n \n // Create a mock conversation context\n const mockContext = {\n conversationId: 'test_conv_123',\n participants: [{\n id: 'test_user_123',\n displayName: 'Test User',\n platform: 'slack' as any,\n platformUserId: 'U123',\n createdAt: new Date(),\n updatedAt: new Date()\n }],\n recentMessages: [{\n id: 'test_msg_123',\n conversationId: 'test_conv_123',\n userId: 'test_user_123',\n content: message,\n timestamp: new Date(),\n platform: 'slack' as any,\n messageType: 'text' as any,\n metadata: {}\n }],\n groupDynamics: {\n activityLevel: 0.7,\n humorLevel: 0.6,\n conflictLevel: 0.1,\n topicFocus: 0.5,\n dominantPersonalities: ['test_user_123']\n },\n yapTrapSettings: {\n conversationId: 'test_conv_123',\n engagementFrequency: 0.8,\n roastIntensity: 0.6,\n responseDelay: 0,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n },\n currentTopic: 'testing',\n sentiment: {\n overall: 0.2,\n recent: 0.3,\n trend: 'stable' as any\n }\n };\n \n // Generate AI response\n const aiEngine = new AIEngine();\n const response = await aiEngine.generateResponse(mockContext);\n \n return c.json({\n success: true,\n data: {\n input: message,\n response: response?.content || 'No response generated',\n metadata: response?.metadata,\n confidence: response?.confidence,\n reasoning: response?.reasoning\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n stack: error.stack,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Persona System Management\n app.get(`${adminPath}/personas`, async (c) => {\n try {\n const { PersonaSystem } = await import('../personas/personas.ts');\n const personaSystem = new PersonaSystem();\n \n const personas = personaSystem.getPersonas();\n \n return c.json({\n success: true,\n data: personas,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch personas: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/personas/discussions`, async (c) => {\n try {\n const { PersonaSystem } = await import('../personas/personas.ts');\n const personaSystem = new PersonaSystem();\n \n const discussions = personaSystem.getActiveDiscussions();\n \n return c.json({\n success: true,\n data: discussions,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch discussions: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.post(`${adminPath}/personas/analyze-issues`, async (c) => {\n try {\n const { PersonaSystem } = await import('../personas/personas.ts');\n const personaSystem = new PersonaSystem();\n \n const issues = await personaSystem.analyzeSystemIssues();\n \n return c.json({\n success: true,\n data: {\n issues,\n count: issues.length\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to analyze issues: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.post(`${adminPath}/personas/start-discussion`, async (c) => {\n try {\n const { issueDescription, issueType = 'error', severity = 'medium' } = await c.req.json();\n \n if (!issueDescription) {\n return c.json({\n success: false,\n error: 'issueDescription is required',\n timestamp: new Date()\n }, 400);\n }\n \n const { PersonaSystem } = await import('../personas/personas.ts');\n const { generateId } = await import('../../shared/utils.ts');\n \n const personaSystem = new PersonaSystem();\n \n // Create a mock issue for testing\n const mockIssue = {\n id: generateId('issue'),\n type: issueType as any,\n severity: severity as any,\n description: issueDescription,\n logs: [],\n affectedComponents: ['test-component'],\n detectedAt: new Date()\n };\n \n const discussionId = await personaSystem.startDiscussion(mockIssue);\n \n return c.json({\n success: true,\n data: {\n discussionId,\n issue: mockIssue\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to start discussion: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Test persona message generation directly\n app.post(`${adminPath}/personas/test-persona`, async (c) => {\n try {\n const { personaRole = 'Technical Lead', issue = 'Database performance issues' } = await c.req.json();\n \n const { PersonaSystem } = await import('../personas/personas.ts');\n const personaSystem = new PersonaSystem();\n \n const personas = personaSystem.getPersonas();\n const persona = personas.find(p => p.role === personaRole);\n \n if (!persona) {\n return c.json({\n success: false,\n error: `Persona with role \"${personaRole}\" not found`,\n availableRoles: personas.map(p => p.role),\n timestamp: new Date()\n }, 400);\n }\n \n // Create a simple context for the persona\n const prompt = `You are ${persona.name}, a ${persona.role}. ${persona.personality}\n\nSYSTEM ISSUE: ${issue}\n\nAs a ${persona.role}, provide your analysis and recommendations for this issue. Keep it under 150 words and stay in character.`;\n \n // Use AI engine directly\n const { OpenAI } = await import(\"https://esm.town/v/std/openai\");\n const openai = new OpenAI();\n \n const completion = await openai.chat.completions.create({\n messages: [\n { role: \"system\", content: `You are ${persona.name}, a ${persona.role} in a software team. ${persona.communicationStyle}` },\n { role: \"user\", content: prompt }\n ],\n model: \"gpt-4o-mini\",\n max_tokens: 200,\n temperature: 0.7\n });\n \n const response = completion.choices[0]?.message?.content || \"No response generated\";\n \n return c.json({\n success: true,\n data: {\n persona: {\n name: persona.name,\n role: persona.role,\n avatar: persona.avatar\n },\n issue,\n response,\n tokensUsed: completion.usage?.total_tokens || 0\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Testing Framework\n app.get(`${adminPath}/tests/suites`, async (c) => {\n try {\n const { YapTrapTestRunner } = await import('../../tests/test-suite.ts');\n const testRunner = new YapTrapTestRunner();\n \n const suites = testRunner.getTestSuites();\n \n return c.json({\n success: true,\n data: suites,\n summary: {\n totalSuites: suites.length,\n totalScenarios: suites.reduce((sum, suite) => sum + suite.scenarios.length, 0)\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.post(`${adminPath}/tests/run`, async (c) => {\n try {\n const { suiteNames } = await c.req.json();\n \n const { YapTrapTestRunner } = await import('../../tests/test-suite.ts');\n const testRunner = new YapTrapTestRunner();\n \n let results;\n if (suiteNames && Array.isArray(suiteNames)) {\n // Run specific suites\n results = new Map();\n const allSuites = testRunner.getTestSuites();\n \n for (const suiteName of suiteNames) {\n const suite = allSuites.find(s => s.name === suiteName);\n if (suite) {\n const suiteResults = await testRunner.runTestSuite(suite);\n results.set(suiteName, suiteResults);\n }\n }\n } else {\n // Run all tests\n results = await testRunner.runAllTests();\n }\n \n // Convert Map to object for JSON serialization\n const resultsObj: Record = {};\n for (const [key, value] of results.entries()) {\n resultsObj[key] = value;\n }\n \n // Calculate summary\n let totalTests = 0;\n let totalPassed = 0;\n \n for (const [_, testResults] of results.entries()) {\n totalTests += testResults.length;\n totalPassed += testResults.filter(r => r.passed).length;\n }\n \n return c.json({\n success: true,\n data: {\n results: resultsObj,\n summary: {\n totalTests,\n totalPassed,\n totalFailed: totalTests - totalPassed,\n successRate: totalTests > 0 ? Math.round((totalPassed / totalTests) * 100) : 0\n }\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Self-Improvement System\n app.get(`${adminPath}/improvement/tickets`, async (c) => {\n try {\n const { ImprovementEngine } = await import('../personas/improvement-engine.ts');\n const engine = new ImprovementEngine();\n \n const tickets = engine.getActiveTickets();\n \n return c.json({\n success: true,\n data: tickets,\n summary: {\n total: tickets.length,\n byStatus: tickets.reduce((acc, ticket) => {\n acc[ticket.status] = (acc[ticket.status] || 0) + 1;\n return acc;\n }, {} as Record),\n byPriority: tickets.reduce((acc, ticket) => {\n acc[ticket.priority] = (acc[ticket.priority] || 0) + 1;\n return acc;\n }, {} as Record)\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.post(`${adminPath}/improvement/generate-tickets`, async (c) => {\n try {\n const { discussionId } = await c.req.json();\n \n if (!discussionId) {\n return c.json({\n success: false,\n error: 'discussionId is required',\n timestamp: new Date()\n }, 400);\n }\n \n const { PersonaSystem } = await import('../personas/personas.ts');\n const { ImprovementEngine } = await import('../personas/improvement-engine.ts');\n \n const personaSystem = new PersonaSystem();\n const engine = new ImprovementEngine();\n \n const discussion = personaSystem.getDiscussion(discussionId);\n if (!discussion) {\n return c.json({\n success: false,\n error: 'Discussion not found',\n timestamp: new Date()\n }, 404);\n }\n \n const tickets = await engine.generateTicketsFromDiscussion(discussion);\n \n return c.json({\n success: true,\n data: {\n discussionId,\n tickets,\n count: tickets.length\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.post(`${adminPath}/improvement/generate-code/:ticketId`, async (c) => {\n try {\n const ticketId = c.req.param('ticketId');\n \n const { ImprovementEngine } = await import('../personas/improvement-engine.ts');\n const engine = new ImprovementEngine();\n \n const changes = await engine.generateCodeChanges(ticketId);\n \n return c.json({\n success: true,\n data: {\n ticketId,\n changes,\n count: changes.length\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/improvement/tickets/:ticketId`, async (c) => {\n try {\n const ticketId = c.req.param('ticketId');\n \n const { ImprovementEngine } = await import('../personas/improvement-engine.ts');\n const engine = new ImprovementEngine();\n \n const ticket = engine.getTicket(ticketId);\n if (!ticket) {\n return c.json({\n success: false,\n error: 'Ticket not found',\n timestamp: new Date()\n }, 404);\n }\n \n const codeChanges = engine.getCodeChanges(ticketId);\n \n return c.json({\n success: true,\n data: {\n ticket,\n codeChanges\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Deployment Management\n app.post(`${adminPath}/deployment/deploy`, async (c) => {\n try {\n const { \n version = '1.0.0', \n environment = 'development',\n features = [],\n rollbackVersion\n } = await c.req.json();\n \n const { DeploymentManager } = await import('../../deployment/deploy.ts');\n \n const config = {\n environment,\n version,\n features,\n rollbackVersion,\n healthCheckTimeout: 30000,\n testTimeout: 60000\n };\n \n const manager = new DeploymentManager(config);\n const result = await manager.deploy();\n \n // Generate deployment report\n const report = await manager.generateDeploymentReport(result);\n \n return c.json({\n success: result.success,\n data: {\n ...result,\n report\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n app.get(`${adminPath}/deployment/status`, async (c) => {\n try {\n // Get current deployment status\n const health = await HealthMonitor.getSystemHealth();\n const metrics = await MetricsCollector.getCurrentMetrics();\n \n return c.json({\n success: true,\n data: {\n version: '1.0.0', // Would be read from package.json or environment\n environment: Deno.env.get('ENVIRONMENT') || 'development',\n deployedAt: new Date(), // Would be read from deployment metadata\n health: health.status,\n uptime: health.uptime,\n metrics: {\n activeConversations: metrics.activeConversations,\n messageVolume: metrics.messageVolume,\n responseRate: metrics.responseRate,\n errorRate: metrics.errorRate\n }\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n \n // Dashboard data endpoint\n app.get(`${adminPath}/dashboard`, async (c) => {\n try {\n const [health, metrics, dbStats, recentLogs] = await Promise.all([\n HealthMonitor.getSystemHealth(),\n MetricsCollector.getCurrentMetrics(),\n getDatabaseStats(),\n SystemLogQueries.findRecent({ limit: 10 })\n ]);\n \n return c.json({\n success: true,\n data: {\n health,\n metrics,\n database: dbStats,\n recentLogs: recentLogs.data,\n timestamp: new Date()\n },\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log('error', 'admin-api', `Failed to fetch dashboard data: ${error.message}`);\n return c.json({\n success: false,\n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n });\n}",[],[151,353,360],{"_103":368,"_95":369,"_152":370,"_154":354,"_155":137,"_156":371,"_158":159,"_105":372,"_100":32,"_161":181,"_163":373,"_164":22,"_165":-5,"_166":374,"_98":375},"164cb810-8eae-11f0-a7fc-0224a6c84d84","static.ts","164cb8d8-8eae-11f0-a7fc-0224a6c84d84",12,["D",1757553879783],"/**\n * Static file serving routes for frontend assets\n * Serves the admin dashboard and related static files\n */\n\nimport type { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\nimport { SystemLogQueries } from '../database/queries.ts';\n\nexport function setupStaticRoutes(app: Hono): void {\n \n // Serve frontend files\n app.get('/frontend/*', async (c) => {\n try {\n const response = await serveFile(c.req.path, import.meta.url);\n return response;\n } catch (error) {\n await SystemLogQueries.log('warn', 'static', `Failed to serve frontend file: ${c.req.path}`);\n return c.text('File not found', 404);\n }\n });\n \n // Serve shared files\n app.get('/shared/*', async (c) => {\n try {\n const response = await serveFile(c.req.path, import.meta.url);\n return response;\n } catch (error) {\n await SystemLogQueries.log('warn', 'static', `Failed to serve shared file: ${c.req.path}`);\n return c.text('File not found', 404);\n }\n });\n \n // Serve favicon\n app.get('/favicon.ico', async (c) => {\n try {\n const response = await serveFile('/frontend/favicon.svg', import.meta.url);\n return response;\n } catch (error) {\n // Return a simple SVG favicon if file not found\n const favicon = ``;\n \n return c.body(favicon, 200, {\n 'Content-Type': 'image/svg+xml',\n 'Cache-Control': 'public, max-age=86400'\n });\n }\n });\n \n // Serve robots.txt\n app.get('/robots.txt', (c) => {\n const robots = `User-agent: *\nDisallow: /admin/\nDisallow: /webhooks/\nAllow: /health\nAllow: /api/status\n\nSitemap: ${c.req.url.replace(c.req.path, '')}/sitemap.xml`;\n \n return c.text(robots, 200, {\n 'Content-Type': 'text/plain',\n 'Cache-Control': 'public, max-age=86400'\n });\n });\n \n // Serve sitemap.xml\n app.get('/sitemap.xml', (c) => {\n const baseUrl = c.req.url.replace(c.req.path, '');\n const sitemap = `\n\n \n ${baseUrl}/\n ${new Date().toISOString().split('T')[0]}\n daily\n 1.0\n \n \n ${baseUrl}/health\n ${new Date().toISOString().split('T')[0]}\n hourly\n 0.5\n \n \n ${baseUrl}/api/status\n ${new Date().toISOString().split('T')[0]}\n hourly\n 0.5\n \n`;\n \n return c.body(sitemap, 200, {\n 'Content-Type': 'application/xml',\n 'Cache-Control': 'public, max-age=3600'\n });\n });\n}",[],[151,353,369],{"_103":377,"_95":378,"_152":379,"_154":354,"_155":137,"_156":355,"_158":159,"_105":380,"_100":32,"_161":181,"_163":381,"_164":22,"_165":-5,"_166":382,"_98":383},"ec2656c2-8ead-11f0-992a-0224a6c84d84","webhooks.ts","ec2657d0-8ead-11f0-992a-0224a6c84d84",["D",1757553809085],"/**\n * Webhook route handlers for all supported platforms\n * Receives and processes incoming messages from various messaging platforms\n */\n\nimport type { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { SystemLogQueries } from '../database/queries.ts';\nimport { MessageProcessor } from '../ai/processor.ts';\n\n// Platform handlers\nimport { SlackHandler } from '../platforms/slack.ts';\nimport { SMSHandler } from '../platforms/sms.ts';\nimport { EmailHandler } from '../platforms/email.ts';\nimport { WhatsAppHandler } from '../platforms/whatsapp.ts';\n\nimport type { Platform, PlatformMessage } from '../../shared/types.ts';\nimport { generateId } from '../../shared/utils.ts';\n\nexport function setupWebhookRoutes(app: Hono): void {\n \n // Slack webhook endpoint\n app.post('/webhooks/slack', async (c) => {\n const startTime = Date.now();\n let webhookId: string;\n \n try {\n const body = await c.req.text();\n const signature = c.req.header('x-slack-signature');\n const timestamp = c.req.header('x-slack-request-timestamp');\n \n webhookId = generateId('webhook');\n \n await SystemLogQueries.log(\n 'info',\n 'webhook-slack',\n `Received Slack webhook`,\n { webhookId, signature: signature?.substring(0, 20) + '...' }\n );\n \n // Handle Slack URL verification challenge\n const payload = JSON.parse(body);\n if (payload.type === 'url_verification') {\n await SystemLogQueries.log('info', 'webhook-slack', 'Slack URL verification challenge received');\n return c.json({ challenge: payload.challenge });\n }\n \n // Validate webhook signature\n const handler = new SlackHandler();\n const isValid = handler.validateWebhook(payload, signature || '');\n \n if (!isValid) {\n await SystemLogQueries.log('warn', 'webhook-slack', 'Invalid Slack webhook signature', { webhookId });\n return c.json({ error: 'Invalid signature' }, 401);\n }\n \n // Process the webhook\n const platformMessage: PlatformMessage = {\n platform: 'slack',\n rawPayload: payload,\n signature,\n timestamp: new Date()\n };\n \n // Process asynchronously to avoid timeout\n processWebhookAsync('slack', platformMessage, webhookId);\n \n // Return success immediately\n return c.json({ status: 'received', webhookId });\n \n } catch (error) {\n const duration = Date.now() - startTime;\n await SystemLogQueries.log(\n 'error',\n 'webhook-slack',\n `Slack webhook processing failed: ${error.message}`,\n { webhookId: webhookId!, duration, error: error.message, stack: error.stack }\n );\n \n return c.json({ \n error: 'Webhook processing failed',\n webhookId: webhookId!,\n message: error.message \n }, 500);\n }\n });\n \n // SMS/MMS webhook endpoint (Telnyx)\n app.post('/webhooks/sms', async (c) => {\n const startTime = Date.now();\n let webhookId: string;\n \n try {\n const body = await c.req.text();\n const signature = c.req.header('telnyx-signature-ed25519');\n const timestamp = c.req.header('telnyx-timestamp');\n \n webhookId = generateId('webhook');\n \n await SystemLogQueries.log(\n 'info',\n 'webhook-sms',\n `Received SMS webhook`,\n { webhookId, signature: signature?.substring(0, 20) + '...' }\n );\n \n const payload = JSON.parse(body);\n \n // Validate webhook signature\n const handler = new SMSHandler();\n const isValid = handler.validateWebhook(payload, signature || '');\n \n if (!isValid) {\n await SystemLogQueries.log('warn', 'webhook-sms', 'Invalid SMS webhook signature', { webhookId });\n return c.json({ error: 'Invalid signature' }, 401);\n }\n \n // Process the webhook\n const platformMessage: PlatformMessage = {\n platform: 'sms',\n rawPayload: payload,\n signature,\n timestamp: new Date()\n };\n \n // Process asynchronously\n processWebhookAsync('sms', platformMessage, webhookId);\n \n return c.json({ status: 'received', webhookId });\n \n } catch (error) {\n const duration = Date.now() - startTime;\n await SystemLogQueries.log(\n 'error',\n 'webhook-sms',\n `SMS webhook processing failed: ${error.message}`,\n { webhookId: webhookId!, duration, error: error.message, stack: error.stack }\n );\n \n return c.json({ \n error: 'Webhook processing failed',\n webhookId: webhookId!,\n message: error.message \n }, 500);\n }\n });\n \n // Email webhook endpoint (SendGrid)\n app.post('/webhooks/email', async (c) => {\n const startTime = Date.now();\n let webhookId: string;\n \n try {\n const body = await c.req.text();\n const signature = c.req.header('x-twilio-email-event-webhook-signature');\n const timestamp = c.req.header('x-twilio-email-event-webhook-timestamp');\n \n webhookId = generateId('webhook');\n \n await SystemLogQueries.log(\n 'info',\n 'webhook-email',\n `Received Email webhook`,\n { webhookId, signature: signature?.substring(0, 20) + '...' }\n );\n \n const payload = JSON.parse(body);\n \n // Validate webhook signature\n const handler = new EmailHandler();\n const isValid = handler.validateWebhook(payload, signature || '');\n \n if (!isValid) {\n await SystemLogQueries.log('warn', 'webhook-email', 'Invalid Email webhook signature', { webhookId });\n return c.json({ error: 'Invalid signature' }, 401);\n }\n \n // Process the webhook\n const platformMessage: PlatformMessage = {\n platform: 'email',\n rawPayload: payload,\n signature,\n timestamp: new Date()\n };\n \n // Process asynchronously\n processWebhookAsync('email', platformMessage, webhookId);\n \n return c.json({ status: 'received', webhookId });\n \n } catch (error) {\n const duration = Date.now() - startTime;\n await SystemLogQueries.log(\n 'error',\n 'webhook-email',\n `Email webhook processing failed: ${error.message}`,\n { webhookId: webhookId!, duration, error: error.message, stack: error.stack }\n );\n \n return c.json({ \n error: 'Webhook processing failed',\n webhookId: webhookId!,\n message: error.message \n }, 500);\n }\n });\n \n // WhatsApp webhook endpoint\n app.post('/webhooks/whatsapp', async (c) => {\n const startTime = Date.now();\n let webhookId: string;\n \n try {\n const body = await c.req.text();\n const signature = c.req.header('x-hub-signature-256');\n \n webhookId = generateId('webhook');\n \n await SystemLogQueries.log(\n 'info',\n 'webhook-whatsapp',\n `Received WhatsApp webhook`,\n { webhookId, signature: signature?.substring(0, 20) + '...' }\n );\n \n const payload = JSON.parse(body);\n \n // Handle WhatsApp verification challenge\n if (c.req.query('hub.mode') === 'subscribe') {\n const challenge = c.req.query('hub.challenge');\n const verifyToken = c.req.query('hub.verify_token');\n \n // Verify the token matches our expected value\n const expectedToken = Deno.env.get('WHATSAPP_VERIFY_TOKEN');\n if (verifyToken === expectedToken) {\n await SystemLogQueries.log('info', 'webhook-whatsapp', 'WhatsApp webhook verification successful');\n return c.text(challenge || '');\n } else {\n await SystemLogQueries.log('warn', 'webhook-whatsapp', 'WhatsApp webhook verification failed');\n return c.text('Forbidden', 403);\n }\n }\n \n // Validate webhook signature\n const handler = new WhatsAppHandler();\n const isValid = handler.validateWebhook(payload, signature || '');\n \n if (!isValid) {\n await SystemLogQueries.log('warn', 'webhook-whatsapp', 'Invalid WhatsApp webhook signature', { webhookId });\n return c.json({ error: 'Invalid signature' }, 401);\n }\n \n // Process the webhook\n const platformMessage: PlatformMessage = {\n platform: 'whatsapp',\n rawPayload: payload,\n signature,\n timestamp: new Date()\n };\n \n // Process asynchronously\n processWebhookAsync('whatsapp', platformMessage, webhookId);\n \n return c.json({ status: 'received', webhookId });\n \n } catch (error) {\n const duration = Date.now() - startTime;\n await SystemLogQueries.log(\n 'error',\n 'webhook-whatsapp',\n `WhatsApp webhook processing failed: ${error.message}`,\n { webhookId: webhookId!, duration, error: error.message, stack: error.stack }\n );\n \n return c.json({ \n error: 'Webhook processing failed',\n webhookId: webhookId!,\n message: error.message \n }, 500);\n }\n });\n \n // Generic webhook endpoint for testing\n app.post('/webhooks/test', async (c) => {\n try {\n const body = await c.req.json();\n const webhookId = generateId('webhook');\n \n await SystemLogQueries.log(\n 'info',\n 'webhook-test',\n `Received test webhook`,\n { webhookId, payload: body }\n );\n \n return c.json({ \n status: 'received',\n webhookId,\n echo: body,\n timestamp: new Date()\n });\n \n } catch (error) {\n await SystemLogQueries.log(\n 'error',\n 'webhook-test',\n `Test webhook failed: ${error.message}`\n );\n \n return c.json({ \n error: 'Test webhook failed',\n message: error.message \n }, 500);\n }\n });\n}\n\n/**\n * Process webhook asynchronously to avoid blocking the HTTP response\n */\nasync function processWebhookAsync(\n platform: Platform, \n platformMessage: PlatformMessage, \n webhookId: string\n): Promise {\n const startTime = Date.now();\n \n try {\n await SystemLogQueries.log(\n 'info',\n `webhook-${platform}`,\n `Starting async processing`,\n { webhookId }\n );\n \n // Get the appropriate platform handler\n const handler = getPlatformHandler(platform);\n \n // Normalize the platform message to our internal format\n const normalizedMessage = await handler.normalizeMessage(platformMessage.rawPayload);\n \n if (!normalizedMessage) {\n await SystemLogQueries.log(\n 'info',\n `webhook-${platform}`,\n `No message to process (likely system event)`,\n { webhookId }\n );\n return;\n }\n \n await SystemLogQueries.log(\n 'info',\n `webhook-${platform}`,\n `Message normalized successfully`,\n { \n webhookId, \n messageId: normalizedMessage.id,\n conversationId: normalizedMessage.conversationId,\n userId: normalizedMessage.userId\n }\n );\n \n // Process the message through the AI pipeline\n const processor = new MessageProcessor();\n await processor.processMessage(normalizedMessage);\n \n const duration = Date.now() - startTime;\n await SystemLogQueries.log(\n 'info',\n `webhook-${platform}`,\n `Webhook processing completed successfully`,\n { webhookId, duration }\n );\n \n } catch (error) {\n const duration = Date.now() - startTime;\n await SystemLogQueries.log(\n 'error',\n `webhook-${platform}`,\n `Async webhook processing failed: ${error.message}`,\n { \n webhookId, \n duration, \n error: error.message, \n stack: error.stack \n }\n );\n }\n}\n\n/**\n * Get the appropriate platform handler for the given platform\n */\nfunction getPlatformHandler(platform: Platform) {\n switch (platform) {\n case 'slack':\n return new SlackHandler();\n case 'sms':\n return new SMSHandler();\n case 'email':\n return new EmailHandler();\n case 'whatsapp':\n return new WhatsAppHandler();\n default:\n throw new Error(`Unsupported platform: ${platform}`);\n }\n}",[],[151,353,378],[151,353],{"_103":386,"_95":387,"_152":388,"_154":153,"_155":137,"_156":389,"_158":159,"_105":390,"_100":391,"_161":392,"_163":393,"_164":22,"_165":-5,"_166":394,"_98":395},"48ad20be-8eaf-11f0-97da-0224a6c84d84","index.ts","c4ba3608-8ead-11f0-9edc-0224a6c84d84",30,["D",1757554393757],4,"http","/**\n * YapTrap Main Backend Entry Point\n * Hono-based HTTP server handling all API endpoints and webhooks\n */\n\nimport { Hono } from \"https://esm.sh/hono@3.11.7\";\nimport { logger } from \"https://esm.sh/hono@3.11.7/logger\";\nimport { readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n\n// Database initialization\nimport { initializeDatabase, insertDefaultConfiguration } from './database/schema.ts';\nimport { SystemLogQueries } from './database/queries.ts';\n\n// Route handlers\nimport { setupWebhookRoutes } from './routes/webhooks.ts';\nimport { setupAdminRoutes } from './routes/admin.ts';\nimport { setupStaticRoutes } from './routes/static.ts';\n\n// Monitoring and health\nimport { HealthMonitor } from './monitoring/health.ts';\nimport { MetricsCollector } from './monitoring/metrics.ts';\n\n// Create Hono app\nconst app = new Hono();\n\n// Unwrap Hono errors to see original error details\napp.onError((err) => Promise.reject(err));\n\n// Global middleware\napp.use('*', logger());\n\n// Request logging middleware\napp.use('*', async (c, next) => {\n const start = Date.now();\n const method = c.req.method;\n const path = c.req.path;\n const userAgent = c.req.header('user-agent') || 'unknown';\n \n try {\n await next();\n \n const duration = Date.now() - start;\n const status = c.res.status;\n \n // Log successful requests\n await SystemLogQueries.log(\n 'info',\n 'http',\n `${method} ${path} ${status} ${duration}ms`,\n {\n method,\n path,\n status,\n duration,\n userAgent\n }\n );\n \n // Collect metrics\n await MetricsCollector.recordHttpRequest(method, path, status, duration);\n \n } catch (error) {\n const duration = Date.now() - start;\n \n // Log failed requests\n await SystemLogQueries.log(\n 'error',\n 'http',\n `${method} ${path} ERROR ${duration}ms: ${error.message}`,\n {\n method,\n path,\n duration,\n userAgent,\n error: error.message,\n stack: error.stack\n }\n );\n \n throw error;\n }\n});\n\n// Initialize database on startup\nlet dbInitialized = false;\napp.use('*', async (c, next) => {\n if (!dbInitialized) {\n try {\n await initializeDatabase();\n await insertDefaultConfiguration();\n dbInitialized = true;\n \n await SystemLogQueries.log('info', 'startup', 'Database initialized successfully');\n } catch (error) {\n await SystemLogQueries.log('error', 'startup', `Database initialization failed: ${error.message}`);\n throw error;\n }\n }\n await next();\n});\n\n// Health check endpoint\napp.get('/health', async (c) => {\n try {\n const health = await HealthMonitor.getSystemHealth();\n const status = health.status === 'healthy' ? 200 : \n health.status === 'degraded' ? 200 : 503;\n \n return c.json(health, status);\n } catch (error) {\n await SystemLogQueries.log('error', 'health', `Health check failed: ${error.message}`);\n return c.json({ \n status: 'unhealthy', \n error: error.message,\n timestamp: new Date()\n }, 503);\n }\n});\n\n// API status endpoint\napp.get('/api/status', async (c) => {\n try {\n const metrics = await MetricsCollector.getCurrentMetrics();\n const health = await HealthMonitor.getSystemHealth();\n \n return c.json({\n status: 'operational',\n version: '1.0.0',\n timestamp: new Date(),\n health: health.status,\n metrics: {\n uptime: health.uptime,\n activeConversations: metrics.activeConversations,\n totalMessages: metrics.messageVolume,\n responseRate: metrics.responseRate\n }\n });\n } catch (error) {\n await SystemLogQueries.log('error', 'api', `Status endpoint failed: ${error.message}`);\n return c.json({ \n status: 'error', \n error: error.message,\n timestamp: new Date()\n }, 500);\n }\n});\n\n// Setup route groups\nsetupWebhookRoutes(app);\nsetupAdminRoutes(app);\nsetupStaticRoutes(app);\n\n// Root endpoint - serve admin dashboard\napp.get('/', async (c) => {\n try {\n let html = await readFile('/frontend/index.html', import.meta.url);\n \n // Inject initial data to avoid extra round-trips\n const initialData = {\n timestamp: new Date(),\n version: '1.0.0',\n health: await HealthMonitor.getSystemHealth(),\n metrics: await MetricsCollector.getCurrentMetrics()\n };\n \n const dataScript = ``;\n \n html = html.replace('', `${dataScript}`);\n \n return c.html(html);\n } catch (error) {\n await SystemLogQueries.log('error', 'frontend', `Failed to serve dashboard: ${error.message}`);\n \n // Fallback HTML if file reading fails\n return c.html(`\n \n \n \n YapTrap - Error\n \n \n \n

YapTrap Dashboard

Error Loading Dashboard

Unable to load the admin dashboard. Please check the logs for more information.

Error: ${error.message}

Check API Status | Health Check

\n \n \n `, 500);\n }\n});\n\n// Catch-all for undefined routes\napp.all('*', async (c) => {\n await SystemLogQueries.log('warn', 'http', `404 Not Found: ${c.req.method} ${c.req.path}`);\n \n return c.json({\n error: 'Not Found',\n message: `The requested endpoint ${c.req.method} ${c.req.path} was not found`,\n timestamp: new Date(),\n availableEndpoints: [\n 'GET /',\n 'GET /health',\n 'GET /api/status',\n 'POST /webhooks/slack',\n 'POST /webhooks/sms',\n 'POST /webhooks/email',\n 'POST /webhooks/whatsapp',\n 'GET /admin/*',\n 'POST /admin/*'\n ]\n }, 404);\n});\n\n// Startup logging\nconsole.log('🤖 YapTrap Backend Starting...');\nconsole.log('📊 Admin Dashboard: /');\nconsole.log('🔗 Webhook Endpoints: /webhooks/*');\nconsole.log('⚙️ Admin API: /admin/*');\nconsole.log('❤️ Health Check: /health');\n\n// Export the Hono app for Val Town\nexport default app.fetch;",[],[151,387],[151],{"_103":398,"_95":399,"_152":400,"_154":-5,"_155":137,"_156":401,"_158":159,"_105":402,"_100":32,"_161":162,"_163":-5,"_164":22,"_165":-5,"_166":403,"_98":412},"d7dd6ca2-8f19-11f0-8f32-0224a6c84d84","deployment","850411fa-4616-48f0-aec0-d04e2f4a1c68",65,["D",1757600160693],[404],{"_103":405,"_95":406,"_152":407,"_154":400,"_155":137,"_156":401,"_158":159,"_105":408,"_100":32,"_161":181,"_163":409,"_164":22,"_165":-5,"_166":410,"_98":411},"d7dd4ba0-8f19-11f0-8f32-0224a6c84d84","deploy.ts","d7dd4c86-8f19-11f0-8f32-0224a6c84d84",["D",1757600160693],"/**\n * YapTrap Deployment Script\n * Handles deployment, testing, and rollback procedures\n */\n\nimport { SystemLogQueries } from '../backend/database/queries.ts';\nimport { HealthMonitor } from '../backend/monitoring/health.ts';\nimport { YapTrapTestRunner } from '../tests/test-suite.ts';\n\nexport interface DeploymentConfig {\n environment: 'development' | 'staging' | 'production';\n version: string;\n features: string[];\n rollbackVersion?: string;\n healthCheckTimeout: number;\n testTimeout: number;\n}\n\nexport interface DeploymentResult {\n success: boolean;\n version: string;\n environment: string;\n deployedAt: Date;\n duration: number;\n testsRun: number;\n testsPassed: number;\n healthStatus: string;\n error?: string;\n rollbackPerformed?: boolean;\n}\n\nexport class DeploymentManager {\n private config: DeploymentConfig;\n \n constructor(config: DeploymentConfig) {\n this.config = config;\n }\n \n /**\n * Execute full deployment pipeline\n */\n async deploy(): Promise {\n const startTime = Date.now();\n \n try {\n await SystemLogQueries.log(\n 'info',\n 'deployment',\n `Starting deployment of version ${this.config.version} to ${this.config.environment}`,\n { config: this.config }\n );\n \n // Step 1: Pre-deployment validation\n await this.validateEnvironment();\n \n // Step 2: Run comprehensive tests\n const testResults = await this.runPreDeploymentTests();\n \n if (testResults.successRate < 95) {\n throw new Error(`Test success rate too low: ${testResults.successRate}%`);\n }\n \n // Step 3: Deploy code changes\n await this.deployCodeChanges();\n \n // Step 4: Run post-deployment health checks\n const healthStatus = await this.performHealthChecks();\n \n if (healthStatus !== 'healthy') {\n throw new Error(`Health check failed: ${healthStatus}`);\n }\n \n // Step 5: Run smoke tests\n await this.runSmokeTests();\n \n const duration = Date.now() - startTime;\n \n const result: DeploymentResult = {\n success: true,\n version: this.config.version,\n environment: this.config.environment,\n deployedAt: new Date(),\n duration,\n testsRun: testResults.totalTests,\n testsPassed: testResults.totalPassed,\n healthStatus: 'healthy'\n };\n \n await SystemLogQueries.log(\n 'info',\n 'deployment',\n `Deployment successful: ${this.config.version}`,\n { result }\n );\n \n return result;\n \n } catch (error) {\n const duration = Date.now() - startTime;\n \n await SystemLogQueries.log(\n 'error',\n 'deployment',\n `Deployment failed: ${error.message}`,\n { error: error.message, duration }\n );\n \n // Attempt rollback if configured\n let rollbackPerformed = false;\n if (this.config.rollbackVersion) {\n try {\n await this.rollback();\n rollbackPerformed = true;\n } catch (rollbackError) {\n await SystemLogQueries.log(\n 'error',\n 'deployment',\n `Rollback failed: ${rollbackError.message}`\n );\n }\n }\n \n return {\n success: false,\n version: this.config.version,\n environment: this.config.environment,\n deployedAt: new Date(),\n duration,\n testsRun: 0,\n testsPassed: 0,\n healthStatus: 'unhealthy',\n error: error.message,\n rollbackPerformed\n };\n }\n }\n \n /**\n * Validate deployment environment\n */\n private async validateEnvironment(): Promise {\n await SystemLogQueries.log('info', 'deployment', 'Validating environment');\n \n // Check required environment variables\n const requiredVars = [\n 'OPENAI_API_KEY',\n 'SLACK_BOT_TOKEN',\n 'TELNYX_API_KEY',\n 'SENDGRID_API_KEY'\n ];\n \n for (const varName of requiredVars) {\n if (!Deno.env.get(varName)) {\n throw new Error(`Missing required environment variable: ${varName}`);\n }\n }\n \n // Validate database connectivity\n const health = await HealthMonitor.getSystemHealth();\n if (health.components.database?.status !== 'healthy') {\n throw new Error('Database is not healthy');\n }\n \n await SystemLogQueries.log('info', 'deployment', 'Environment validation passed');\n }\n \n /**\n * Run comprehensive pre-deployment tests\n */\n private async runPreDeploymentTests(): Promise {\n await SystemLogQueries.log('info', 'deployment', 'Running pre-deployment tests');\n \n const testRunner = new YapTrapTestRunner();\n const results = await testRunner.runAllTests();\n \n // Calculate summary\n let totalTests = 0;\n let totalPassed = 0;\n \n for (const [_, testResults] of results.entries()) {\n totalTests += testResults.length;\n totalPassed += testResults.filter(r => r.passed).length;\n }\n \n const successRate = totalTests > 0 ? Math.round((totalPassed / totalTests) * 100) : 0;\n \n await SystemLogQueries.log(\n 'info',\n 'deployment',\n `Pre-deployment tests completed: ${totalPassed}/${totalTests} (${successRate}%)`,\n { totalTests, totalPassed, successRate }\n );\n \n return { totalTests, totalPassed, successRate };\n }\n \n /**\n * Deploy code changes\n */\n private async deployCodeChanges(): Promise {\n await SystemLogQueries.log('info', 'deployment', 'Deploying code changes');\n \n // In Val Town, this would involve updating the vals\n // For now, we'll simulate the deployment process\n \n // Simulate deployment steps\n await this.simulateDeploymentStep('Updating database schema', 2000);\n await this.simulateDeploymentStep('Deploying backend services', 3000);\n await this.simulateDeploymentStep('Updating frontend assets', 1000);\n await this.simulateDeploymentStep('Configuring webhooks', 1500);\n \n await SystemLogQueries.log('info', 'deployment', 'Code deployment completed');\n }\n \n /**\n * Perform post-deployment health checks\n */\n private async performHealthChecks(): Promise {\n await SystemLogQueries.log('info', 'deployment', 'Performing health checks');\n \n const maxAttempts = 5;\n const delayMs = 2000;\n \n for (let attempt = 1; attempt <= maxAttempts; attempt++) {\n const health = await HealthMonitor.getSystemHealth();\n \n if (health.status === 'healthy') {\n await SystemLogQueries.log('info', 'deployment', 'Health checks passed');\n return 'healthy';\n }\n \n if (attempt < maxAttempts) {\n await SystemLogQueries.log(\n 'warn',\n 'deployment',\n `Health check attempt ${attempt} failed, retrying...`,\n { status: health.status }\n );\n await new Promise(resolve => setTimeout(resolve, delayMs));\n }\n }\n \n await SystemLogQueries.log('error', 'deployment', 'Health checks failed after all attempts');\n return 'unhealthy';\n }\n \n /**\n * Run smoke tests\n */\n private async runSmokeTests(): Promise {\n await SystemLogQueries.log('info', 'deployment', 'Running smoke tests');\n \n // Test critical endpoints\n const criticalTests = [\n { name: 'Health Check', endpoint: '/health' },\n { name: 'API Status', endpoint: '/api/status' },\n { name: 'Admin Dashboard', endpoint: '/admin/dashboard' }\n ];\n \n for (const test of criticalTests) {\n try {\n // In a real deployment, these would be actual HTTP requests\n await this.simulateDeploymentStep(`Testing ${test.name}`, 500);\n \n await SystemLogQueries.log(\n 'info',\n 'deployment',\n `Smoke test passed: ${test.name}`\n );\n } catch (error) {\n throw new Error(`Smoke test failed for ${test.name}: ${error.message}`);\n }\n }\n \n await SystemLogQueries.log('info', 'deployment', 'All smoke tests passed');\n }\n \n /**\n * Rollback to previous version\n */\n private async rollback(): Promise {\n if (!this.config.rollbackVersion) {\n throw new Error('No rollback version specified');\n }\n \n await SystemLogQueries.log(\n 'info',\n 'deployment',\n `Rolling back to version ${this.config.rollbackVersion}`\n );\n \n // Simulate rollback process\n await this.simulateDeploymentStep('Reverting database changes', 2000);\n await this.simulateDeploymentStep('Restoring previous code version', 3000);\n await this.simulateDeploymentStep('Updating configuration', 1000);\n \n // Verify rollback\n const health = await HealthMonitor.getSystemHealth();\n if (health.status !== 'healthy') {\n throw new Error('System still unhealthy after rollback');\n }\n \n await SystemLogQueries.log(\n 'info',\n 'deployment',\n `Rollback to ${this.config.rollbackVersion} completed successfully`\n );\n }\n \n /**\n * Simulate deployment step with delay\n */\n private async simulateDeploymentStep(description: string, delayMs: number): Promise {\n await SystemLogQueries.log('debug', 'deployment', `Starting: ${description}`);\n await new Promise(resolve => setTimeout(resolve, delayMs));\n await SystemLogQueries.log('debug', 'deployment', `Completed: ${description}`);\n }\n \n /**\n * Generate deployment report\n */\n async generateDeploymentReport(result: DeploymentResult): Promise {\n const report = `\n# YapTrap Deployment Report\n\n**Version:** ${result.version}\n**Environment:** ${result.environment}\n**Status:** ${result.success ? '✅ SUCCESS' : '❌ FAILED'}\n**Deployed At:** ${result.deployedAt.toISOString()}\n**Duration:** ${Math.round(result.duration / 1000)}s\n\n## Test Results\n- **Tests Run:** ${result.testsRun}\n- **Tests Passed:** ${result.testsPassed}\n- **Success Rate:** ${result.testsRun > 0 ? Math.round((result.testsPassed / result.testsRun) * 100) : 0}%\n\n## Health Status\n- **Post-Deployment:** ${result.healthStatus}\n\n${result.error ? `## Error\\n${result.error}` : ''}\n\n${result.rollbackPerformed ? '## Rollback\\n✅ Automatic rollback performed' : ''}\n\n## Next Steps\n${result.success ? \n '- Monitor system performance\\n- Verify user functionality\\n- Update documentation' :\n '- Investigate deployment failure\\n- Fix identified issues\\n- Retry deployment'\n}\n`;\n \n return report;\n }\n}",[],[399,406],[399],{"_103":414,"_95":415,"_152":416,"_154":-5,"_155":137,"_156":417,"_158":159,"_105":418,"_100":32,"_161":162,"_163":-5,"_164":22,"_165":-5,"_166":419,"_98":437},"11e9a6ee-8ead-11f0-9849-0224a6c84d84","docs","e72cb351-71cf-4e1a-bbcd-0a5596e47ccc",3,["D",1757553442936],[420,429],{"_103":421,"_95":422,"_152":423,"_154":416,"_155":137,"_156":391,"_158":159,"_105":424,"_100":32,"_161":425,"_163":426,"_164":22,"_165":-5,"_166":427,"_98":428},"37753324-8ead-11f0-a4b9-0224a6c84d84","SDD.md","3775341e-8ead-11f0-a4b9-0224a6c84d84",["D",1757553505933],"file","# Software Design Document (SDD)\n# YapTrap Multi-Platform Chatbot System\n\n**Version:** 1.0 \n**Date:** December 2024 \n**Status:** Draft\n\n## 1. Introduction\n\n### 1.1 Purpose\nThis document describes the software design for YapTrap, detailing the system architecture, component design, and implementation approach that satisfies the requirements specified in the SRS.\n\n### 1.2 Scope\nThis design covers:\n- Overall system architecture and component relationships\n- Database schema and data flow design\n- Platform integration patterns and APIs\n- AI/LLM integration architecture\n- Administrative interface design\n- Monitoring and logging infrastructure\n\n### 1.3 Design Principles\n- **Modularity**: Clear separation of concerns with well-defined interfaces\n- **Scalability**: Horizontal scaling capabilities within Val Town constraints\n- **Reliability**: Defensive programming with comprehensive error handling\n- **Maintainability**: Clean code with extensive testing and documentation\n- **Security**: Security-first design with data protection throughout\n\n## 2. System Architecture\n\n### 2.1 High-Level Architecture\n\n```\n┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐\n│ Messaging │ │ YapTrap │ │ Admin │\n│ Platforms │◄──►│ Core System │◄──►│ Dashboard │\n│ │ │ │ │ │\n│ • Slack │ │ • Message Proc │ │ • Monitoring │\n│ • SMS/MMS │ │ • AI Engine │ │ • Configuration │\n│ • Email │ │ • Database │ │ • Analytics │\n│ • WhatsApp │ │ • Platform APIs │ │ • User Mgmt │\n└─────────────────┘ └─────────────────┘ └─────────────────┘\n │\n ┌─────────────────┐\n │ External │\n │ Services │\n │ │\n │ • LLM APIs │\n │ • Database │\n │ • Monitoring │\n └─────────────────┘\n```\n\n### 2.2 Component Architecture\n\n#### 2.2.1 Core Components\n- **Message Router**: Central message processing and routing\n- **Platform Handlers**: Platform-specific integration modules\n- **AI Engine**: LLM integration and response generation\n- **Personality Engine**: User profiling and adaptation logic\n- **Database Layer**: Data persistence and retrieval\n- **Admin Interface**: Web-based management dashboard\n- **Monitoring System**: Logging, metrics, and health monitoring\n\n#### 2.2.2 Val Town Deployment Structure\n```\nbackend/\n├── index.ts # Main HTTP entry point (Hono app)\n├── database/\n│ ├── schema.ts # Database schema definitions\n│ ├── queries.ts # Database query functions\n│ └── migrations.ts # Schema migrations\n├── platforms/\n│ ├── base.ts # Base platform handler interface\n│ ├── slack.ts # Slack integration\n│ ├── sms.ts # SMS/MMS via Telnyx\n│ ├── email.ts # Email via SendGrid\n│ └── whatsapp.ts # WhatsApp Business API\n├── ai/\n│ ├── engine.ts # LLM integration layer\n│ ├── personality.ts # Personality analysis and profiling\n│ └── context.ts # Conversation context management\n├── monitoring/\n│ ├── logger.ts # Centralized logging\n│ ├── metrics.ts # Performance and usage metrics\n│ └── health.ts # System health monitoring\n└── routes/\n ├── webhooks.ts # Platform webhook handlers\n ├── admin.ts # Admin API endpoints\n └── static.ts # Static file serving\n\nfrontend/\n├── index.html # Main admin dashboard\n├── components/\n│ ├── Dashboard.tsx # Main dashboard component\n│ ├── ConversationView.tsx\n│ ├── MetricsView.tsx\n│ └── ConfigView.tsx\n└── styles/\n └── main.css\n\nshared/\n├── types.ts # Shared TypeScript interfaces\n├── utils.ts # Common utility functions\n└── constants.ts # System constants and configuration\n```\n\n## 3. Database Design\n\n### 3.1 Schema Overview\n\n#### 3.1.1 Core Tables\n```sql\n-- Conversations: Track group conversations across platforms\nCREATE TABLE conversations (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON,\n is_active BOOLEAN DEFAULT 1\n);\n\n-- Users: Individual participants across all conversations\nCREATE TABLE users (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n personality_profile JSON,\n preferences JSON,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Messages: All messages in conversations\nCREATE TABLE messages (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'user', -- 'user', 'yaptrap', 'system'\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON,\n FOREIGN KEY (conversation_id) REFERENCES conversations(id),\n FOREIGN KEY (user_id) REFERENCES users(id)\n);\n\n-- Conversation Participants: Many-to-many relationship\nCREATE TABLE conversation_participants (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member', -- 'member', 'admin', 'bot'\n is_active BOOLEAN DEFAULT 1,\n PRIMARY KEY (conversation_id, user_id),\n FOREIGN KEY (conversation_id) REFERENCES conversations(id),\n FOREIGN KEY (user_id) REFERENCES users(id)\n);\n\n-- System Logs: Comprehensive logging for monitoring\nCREATE TABLE system_logs (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL, -- 'debug', 'info', 'warn', 'error'\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON,\n conversation_id TEXT,\n user_id TEXT\n);\n\n-- Configuration: System and conversation-specific settings\nCREATE TABLE configuration (\n key TEXT PRIMARY KEY,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global', -- 'global', 'conversation', 'user'\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT\n);\n```\n\n### 3.2 Data Flow Design\n\n#### 3.2.1 Message Processing Flow\n1. **Webhook Reception**: Platform sends message to webhook endpoint\n2. **Message Normalization**: Convert platform-specific format to internal format\n3. **User/Conversation Resolution**: Identify or create user and conversation records\n4. **Context Analysis**: Analyze message content and conversation context\n5. **Response Decision**: Determine if and how YapTrap should respond\n6. **Response Generation**: Generate appropriate response using AI engine\n7. **Platform Delivery**: Send response back through platform API\n8. **Logging**: Record all activities for monitoring and learning\n\n#### 3.2.2 Personality Learning Flow\n1. **Message Analysis**: Extract communication patterns and preferences\n2. **Profile Update**: Update user personality profile with new insights\n3. **Group Dynamics**: Analyze group interaction patterns\n4. **Adaptation**: Adjust YapTrap's behavior based on learned preferences\n\n## 4. Component Design\n\n### 4.1 Message Router (backend/routes/webhooks.ts)\n\n```typescript\ninterface MessageRouter {\n // Route incoming webhook messages to appropriate platform handlers\n routeMessage(platform: string, payload: any): Promise;\n \n // Process normalized messages through the AI pipeline\n processMessage(message: NormalizedMessage): Promise;\n \n // Determine response strategy based on context and configuration\n shouldRespond(context: ConversationContext): Promise;\n}\n```\n\n### 4.2 Platform Handler Interface (backend/platforms/base.ts)\n\n```typescript\ninterface PlatformHandler {\n // Normalize incoming platform messages to internal format\n normalizeMessage(payload: any): Promise;\n \n // Send response back to platform\n sendMessage(conversationId: string, content: string): Promise;\n \n // Handle platform-specific authentication and setup\n authenticate(): Promise;\n \n // Validate webhook signatures and requests\n validateWebhook(payload: any, signature: string): boolean;\n}\n```\n\n### 4.3 AI Engine (backend/ai/engine.ts)\n\n```typescript\ninterface AIEngine {\n // Generate contextual response based on conversation history\n generateResponse(context: ConversationContext): Promise;\n \n // Analyze message sentiment and intent\n analyzeMessage(message: NormalizedMessage): Promise;\n \n // Update conversation context with new message\n updateContext(context: ConversationContext, message: NormalizedMessage): Promise;\n}\n```\n\n### 4.4 Personality Engine (backend/ai/personality.ts)\n\n```typescript\ninterface PersonalityEngine {\n // Analyze user communication patterns\n analyzeUserPersonality(userId: string, messages: NormalizedMessage[]): Promise;\n \n // Update personality profile with new insights\n updatePersonalityProfile(userId: string, insights: PersonalityInsights): Promise;\n \n // Get personality-adapted response style\n getResponseStyle(userId: string, groupContext: GroupContext): Promise;\n}\n```\n\n## 5. API Design\n\n### 5.1 Webhook Endpoints\n\n```typescript\n// Platform webhook receivers\nPOST /webhooks/slack # Slack events and messages\nPOST /webhooks/sms # Telnyx SMS/MMS webhooks \nPOST /webhooks/email # SendGrid inbound email\nPOST /webhooks/whatsapp # WhatsApp Business API webhooks\n\n// Administrative endpoints\nGET /admin/conversations # List all conversations\nGET /admin/users # List all users\nGET /admin/metrics # System metrics and health\nPOST /admin/config # Update configuration\nGET /admin/logs # Stream system logs\n```\n\n### 5.2 Internal API Interfaces\n\n```typescript\n// Normalized message format across all platforms\ninterface NormalizedMessage {\n id: string;\n conversationId: string;\n userId: string;\n content: string;\n timestamp: Date;\n platform: string;\n messageType: 'text' | 'image' | 'file' | 'system';\n metadata: Record;\n}\n\n// Conversation context for AI processing\ninterface ConversationContext {\n conversationId: string;\n participants: User[];\n recentMessages: NormalizedMessage[];\n groupDynamics: GroupDynamics;\n yapTrapSettings: YapTrapSettings;\n}\n\n// User personality profile\ninterface PersonalityProfile {\n userId: string;\n communicationStyle: CommunicationStyle;\n interests: string[];\n responsePreferences: ResponsePreferences;\n groupRole: GroupRole;\n lastUpdated: Date;\n}\n```\n\n## 6. Security Design\n\n### 6.1 Authentication and Authorization\n- **Webhook Validation**: Verify all incoming webhooks using platform-specific signatures\n- **API Authentication**: Secure admin endpoints with authentication tokens\n- **Rate Limiting**: Implement rate limiting to prevent abuse\n- **Input Validation**: Sanitize and validate all input data\n\n### 6.2 Data Protection\n- **Encryption**: Encrypt sensitive data at rest and in transit\n- **Access Control**: Implement role-based access control for admin functions\n- **Audit Logging**: Log all data access and modifications\n- **Data Retention**: Implement configurable data retention policies\n\n## 7. Monitoring and Logging Design\n\n### 7.1 Logging Strategy\n- **Structured Logging**: Use consistent JSON format for all log entries\n- **Log Levels**: Implement debug, info, warn, error levels\n- **Contextual Logging**: Include conversation and user context in logs\n- **Performance Logging**: Track response times and system performance\n\n### 7.2 Metrics Collection\n- **Message Metrics**: Track message volume, response rates, engagement\n- **Performance Metrics**: Monitor response times, error rates, throughput\n- **User Metrics**: Track user satisfaction, personality learning accuracy\n- **System Metrics**: Monitor resource usage, health status\n\n## 8. Deployment Architecture\n\n### 8.1 Val Town Deployment\n- **Main Entry Point**: Single HTTP handler in `backend/index.ts`\n- **Environment Configuration**: Use Val Town environment variables\n- **Database**: SQLite for development, external database for production\n- **Static Assets**: Serve admin interface through Val Town\n\n### 8.2 External Dependencies\n- **Database**: External PostgreSQL or SQLite for data persistence\n- **LLM Services**: OpenAI, xAI, Claude APIs for AI functionality\n- **Platform APIs**: Direct integration with messaging platform APIs\n- **Monitoring**: External logging and metrics collection services\n\n## 9. Error Handling and Recovery\n\n### 9.1 Error Handling Strategy\n- **Graceful Degradation**: Continue operation with reduced functionality during failures\n- **Retry Logic**: Implement exponential backoff for transient failures\n- **Circuit Breakers**: Prevent cascade failures in external service calls\n- **Fallback Responses**: Provide default responses when AI services fail\n\n### 9.2 Recovery Procedures\n- **Automatic Recovery**: Self-healing for common failure scenarios\n- **Manual Intervention**: Clear procedures for manual recovery\n- **Data Recovery**: Backup and restore procedures for data loss\n- **Service Recovery**: Procedures for restoring service after outages\n\n## 10. Performance Considerations\n\n### 10.1 Scalability Design\n- **Stateless Design**: All components designed for horizontal scaling\n- **Caching Strategy**: Cache frequently accessed data and AI responses\n- **Database Optimization**: Efficient queries and indexing strategy\n- **Rate Limiting**: Protect against overload and abuse\n\n### 10.2 Performance Optimization\n- **Response Time**: Target sub-5-second response times\n- **Concurrent Processing**: Handle multiple conversations simultaneously\n- **Resource Management**: Efficient memory and CPU usage\n- **Network Optimization**: Minimize external API calls and data transfer\n\n---\n\n**Document Control:**\n- Author: Technical Architect\n- Reviewers: Senior Developer, DevOps Engineer\n- Approval: Technical Lead\n- Implementation Reference: All code must trace back to this design\n- Next Review: Upon major architectural changes",[],[415,422],{"_103":430,"_95":431,"_152":432,"_154":416,"_155":137,"_156":417,"_158":159,"_105":433,"_100":32,"_161":425,"_163":434,"_164":22,"_165":-5,"_166":435,"_98":436},"11e9861e-8ead-11f0-9849-0224a6c84d84","SRS.md","11e986e6-8ead-11f0-9849-0224a6c84d84",["D",1757553442936],"# Software Requirements Specification (SRS)\n# YapTrap Multi-Platform Chatbot System\n\n**Version:** 1.0 \n**Date:** December 2024 \n**Status:** Draft\n\n## 1. Introduction\n\n### 1.1 Purpose\nThis document specifies the requirements for YapTrap, an automated chatbot system designed to participate in multi-party conversations across various messaging platforms with personality-aware contextual engagement.\n\n### 1.2 Scope\nYapTrap will:\n- Monitor and participate in group conversations across multiple platforms\n- Learn and adapt to individual user personalities and group dynamics\n- Provide contextual, witty, and engaging responses\n- Offer administrative controls and monitoring capabilities\n- Self-monitor and improve through automated analysis\n\n### 1.3 Definitions and Acronyms\n- **YapTrap**: The core chatbot system\n- **Persona**: Simulated user personalities for testing and system improvement\n- **Platform Handler**: Service component managing specific messaging platform integration\n- **LLM**: Large Language Model for AI-powered responses\n\n## 2. Overall Description\n\n### 2.1 Product Perspective\nYapTrap operates as a distributed system with:\n- Core backend services running on Val Town\n- Platform-specific webhook handlers\n- Database for state and conversation management\n- Web-based administrative interface\n- Automated monitoring and improvement subsystems\n\n### 2.2 Product Functions\n1. **Message Processing**: Receive, analyze, and respond to messages\n2. **Personality Learning**: Build profiles of conversation participants\n3. **Context Generation**: Create relevant, engaging responses\n4. **Platform Integration**: Support multiple messaging platforms\n5. **Administration**: Provide monitoring and configuration capabilities\n6. **Self-Improvement**: Automated system analysis and enhancement\n\n### 2.3 User Classes\n- **End Users**: Participants in group conversations\n- **Administrators**: System operators managing YapTrap instances\n- **Developers**: Personnel maintaining and enhancing the system\n\n## 3. Functional Requirements\n\n### 3.1 Core Chatbot Functionality\n\n#### 3.1.1 Message Reception and Processing\n- **REQ-001**: System SHALL receive messages from supported platforms via webhooks\n- **REQ-002**: System SHALL parse and normalize messages across different platform formats\n- **REQ-003**: System SHALL maintain conversation context and history\n- **REQ-004**: System SHALL identify and track individual users across conversations\n\n#### 3.1.2 Personality Learning and Adaptation\n- **REQ-005**: System SHALL analyze user communication patterns and preferences\n- **REQ-006**: System SHALL build and maintain personality profiles for each user\n- **REQ-007**: System SHALL adapt response style based on group dynamics\n- **REQ-008**: System SHALL learn from user reactions to its messages\n\n#### 3.1.3 Response Generation\n- **REQ-009**: System SHALL generate contextually relevant responses using LLM integration\n- **REQ-010**: System SHALL provide witty, engaging, and appropriate content\n- **REQ-011**: System SHALL adjust response frequency based on configuration\n- **REQ-012**: System SHALL initiate conversations when threads go silent (configurable)\n\n### 3.2 Platform Integration\n\n#### 3.2.1 Supported Platforms\n- **REQ-013**: System SHALL support Slack via official Slack App API\n- **REQ-014**: System SHALL support SMS/MMS via Telnyx API\n- **REQ-015**: System SHALL support Email via SendGrid API\n- **REQ-016**: System SHALL support WhatsApp Business API (webhook-based)\n- **REQ-017**: System SHALL provide extensible architecture for additional platforms\n\n#### 3.2.2 Platform-Specific Requirements\n- **REQ-018**: Each platform handler SHALL normalize incoming messages to common format\n- **REQ-019**: Each platform handler SHALL format outgoing messages appropriately\n- **REQ-020**: System SHALL handle platform-specific authentication and authorization\n- **REQ-021**: System SHALL respect platform rate limits and usage policies\n\n### 3.3 Data Management\n\n#### 3.3.1 Database Requirements\n- **REQ-022**: System SHALL store conversation history with configurable retention\n- **REQ-023**: System SHALL maintain user personality profiles and preferences\n- **REQ-024**: System SHALL track group-specific settings and configurations\n- **REQ-025**: System SHALL log all system operations for monitoring and debugging\n\n#### 3.3.2 Data Privacy and Security\n- **REQ-026**: System SHALL encrypt sensitive data at rest and in transit\n- **REQ-027**: System SHALL provide data retention and deletion capabilities\n- **REQ-028**: System SHALL comply with applicable privacy regulations\n- **REQ-029**: System SHALL provide audit trails for all data access\n\n### 3.4 Administrative Interface\n\n#### 3.4.1 Web Dashboard\n- **REQ-030**: System SHALL provide web-based administrative interface\n- **REQ-031**: Dashboard SHALL display system health and operational metrics\n- **REQ-032**: Dashboard SHALL allow configuration of chatbot parameters\n- **REQ-033**: Dashboard SHALL provide conversation monitoring and analysis tools\n\n#### 3.4.2 Configuration Management\n- **REQ-034**: System SHALL support runtime configuration changes\n- **REQ-035**: System SHALL maintain configuration version history\n- **REQ-036**: System SHALL validate configuration changes before application\n- **REQ-037**: System SHALL provide rollback capabilities for configuration changes\n\n### 3.5 Monitoring and Self-Improvement\n\n#### 3.5.1 System Monitoring\n- **REQ-038**: System SHALL collect and analyze operational metrics\n- **REQ-039**: System SHALL detect and alert on system anomalies\n- **REQ-040**: System SHALL provide real-time log streaming capabilities\n- **REQ-041**: System SHALL generate automated system health reports\n\n#### 3.5.2 Automated Improvement\n- **REQ-042**: System SHALL analyze conversation effectiveness and user satisfaction\n- **REQ-043**: System SHALL identify opportunities for system enhancement\n- **REQ-044**: System SHALL generate improvement recommendations\n- **REQ-045**: System SHALL support automated testing of system changes\n\n## 4. Non-Functional Requirements\n\n### 4.1 Performance\n- **REQ-046**: System SHALL respond to messages within 5 seconds under normal load\n- **REQ-047**: System SHALL support concurrent conversations across multiple platforms\n- **REQ-048**: System SHALL handle message bursts without data loss\n- **REQ-049**: System SHALL scale automatically based on demand\n\n### 4.2 Reliability\n- **REQ-050**: System SHALL maintain 99.5% uptime availability\n- **REQ-051**: System SHALL implement graceful degradation during partial failures\n- **REQ-052**: System SHALL provide automatic recovery from transient failures\n- **REQ-053**: System SHALL maintain data consistency across all operations\n\n### 4.3 Security\n- **REQ-054**: System SHALL authenticate all API requests\n- **REQ-055**: System SHALL implement rate limiting to prevent abuse\n- **REQ-056**: System SHALL validate and sanitize all input data\n- **REQ-057**: System SHALL protect against common security vulnerabilities\n\n### 4.4 Maintainability\n- **REQ-058**: System SHALL provide comprehensive logging for debugging\n- **REQ-059**: System SHALL implement defensive programming practices\n- **REQ-060**: System SHALL include automated testing for all components\n- **REQ-061**: System SHALL support continuous integration and deployment\n\n## 5. External Interface Requirements\n\n### 5.1 User Interfaces\n- Web-based administrative dashboard\n- Platform-native messaging interfaces (via platform APIs)\n\n### 5.2 Hardware Interfaces\n- None (cloud-based deployment)\n\n### 5.3 Software Interfaces\n- Slack API v1.7+\n- Telnyx API v2\n- SendGrid API v3\n- WhatsApp Business API\n- OpenAI/xAI/Claude APIs for LLM integration\n\n### 5.4 Communication Interfaces\n- HTTPS for all API communications\n- WebSocket for real-time features\n- Webhook endpoints for platform integrations\n\n## 6. Constraints\n\n### 6.1 Technical Constraints\n- Must operate within Val Town serverless environment\n- Limited to stateless operations with external state storage\n- Must comply with platform API terms of service\n- Must handle intermittent connectivity and API limitations\n\n### 6.2 Business Constraints\n- Must respect user privacy and data protection requirements\n- Must operate within API rate limits and usage quotas\n- Must provide cost-effective operation at scale\n\n## 7. Assumptions and Dependencies\n\n### 7.1 Assumptions\n- Platform APIs will remain stable and accessible\n- LLM services will provide consistent response quality\n- Users will grant necessary permissions for bot operation\n\n### 7.2 Dependencies\n- Val Town platform availability and capabilities\n- Third-party API service availability\n- Database service reliability\n- LLM service availability and performance\n\n## 8. Acceptance Criteria\n\n### 8.1 Functional Acceptance\n- All functional requirements (REQ-001 through REQ-045) must be implemented and tested\n- System must successfully participate in conversations on all supported platforms\n- Administrative interface must provide complete system management capabilities\n\n### 8.2 Performance Acceptance\n- System must meet all performance requirements under specified load conditions\n- Response times must remain within acceptable limits during peak usage\n- System must demonstrate scalability and reliability metrics\n\n### 8.3 Security Acceptance\n- All security requirements must be implemented and verified\n- System must pass security audit and penetration testing\n- Data protection and privacy compliance must be demonstrated\n\n---\n\n**Document Control:**\n- Author: System Architect\n- Reviewers: Technical Lead, Product Owner\n- Approval: Project Manager\n- Next Review: Monthly or upon significant changes",[],[415,431],[415],{"_103":439,"_95":440,"_152":441,"_154":-5,"_155":137,"_156":442,"_158":159,"_105":443,"_100":32,"_161":162,"_163":-5,"_164":22,"_165":-5,"_166":444,"_98":453},"64bfaef8-8eae-11f0-927b-0224a6c84d84","frontend","d0304a61-5d62-4b65-b30a-46e54bc007e1",15,["D",1757554011410],[445],{"_103":446,"_95":447,"_152":448,"_154":441,"_155":137,"_156":442,"_158":159,"_105":449,"_100":32,"_161":425,"_163":450,"_164":22,"_165":-5,"_166":451,"_98":452},"64bf7a50-8eae-11f0-927b-0224a6c84d84","index.html","64bf7b72-8eae-11f0-927b-0224a6c84d84",["D",1757554011410],"\n\n\n \n \n YapTrap Admin Dashboard\n \n \n \n\n\n

\n \n

🤖 YapTrap

\n Admin Dashboard\n

\n Loading...\n

\n \n

\n\n \n

\n \n

Active Conversations

Messages (1h)

Response Rate

Uptime

\n\n \n

\n \n

\n\n \n

\n \n

System Health

\n\n \n

Platform Activity

\n\n \n

Recent Activity

\n\n \n

Active Conversations

\n \n

Click \"Load Conversations\" to view active conversations.

\n\n \n

System Logs

\n \n \n

Click \"Load Logs\" to view system logs.

\n\n \n

System Configuration

\n \n

Click \"Load Configuration\" to view system settings.

\n\n \n\n",[],[440,447],[440],{"_103":455,"_95":456,"_152":457,"_154":-5,"_155":137,"_156":225,"_158":159,"_105":458,"_100":32,"_161":162,"_163":-5,"_164":22,"_165":-5,"_166":459,"_98":477},"4983d192-8ead-11f0-84d9-0224a6c84d84","shared","3ce5910d-9c37-473e-97c0-f4535477aef7",["D",1757553536216],[460,468],{"_103":461,"_95":462,"_152":463,"_154":457,"_155":137,"_156":225,"_158":159,"_105":464,"_100":32,"_161":181,"_163":465,"_164":22,"_165":-5,"_166":466,"_98":467},"4983bda6-8ead-11f0-84d9-0224a6c84d84","types.ts","4983be82-8ead-11f0-84d9-0224a6c84d84",["D",1757553536216],"/**\n * Shared TypeScript interfaces and types for YapTrap system\n * Used across frontend, backend, and testing components\n */\n\n// Core message types\nexport interface NormalizedMessage {\n id: string;\n conversationId: string;\n userId: string;\n content: string;\n timestamp: Date;\n platform: Platform;\n messageType: MessageType;\n metadata: Record;\n}\n\nexport type Platform = 'slack' | 'sms' | 'email' | 'whatsapp' | 'messenger';\nexport type MessageType = 'text' | 'image' | 'file' | 'system';\nexport type UserRole = 'member' | 'admin' | 'bot';\nexport type LogLevel = 'debug' | 'info' | 'warn' | 'error';\n\n// User and conversation types\nexport interface User {\n id: string;\n platform: Platform;\n platformUserId: string;\n displayName: string;\n personalityProfile?: PersonalityProfile;\n preferences?: UserPreferences;\n createdAt: Date;\n updatedAt: Date;\n}\n\nexport interface Conversation {\n id: string;\n platform: Platform;\n platformId: string;\n name?: string;\n createdAt: Date;\n lastActivity?: Date;\n settings: ConversationSettings;\n isActive: boolean;\n participants: ConversationParticipant[];\n}\n\nexport interface ConversationParticipant {\n conversationId: string;\n userId: string;\n joinedAt: Date;\n role: UserRole;\n isActive: boolean;\n}\n\n// AI and personality types\nexport interface PersonalityProfile {\n userId: string;\n communicationStyle: CommunicationStyle;\n interests: string[];\n responsePreferences: ResponsePreferences;\n groupRole: GroupRole;\n confidence: number; // 0-1 confidence in profile accuracy\n lastUpdated: Date;\n}\n\nexport interface CommunicationStyle {\n formality: number; // 0-1 (casual to formal)\n humor: number; // 0-1 (serious to humorous)\n verbosity: number; // 0-1 (concise to verbose)\n emotiveness: number; // 0-1 (reserved to expressive)\n directness: number; // 0-1 (indirect to direct)\n}\n\nexport interface ResponsePreferences {\n engagementFrequency: number; // 0-1 (rarely to frequently)\n roastIntensity: number; // 0-1 (gentle to spicy)\n topicInterests: string[];\n avoidTopics: string[];\n preferredResponseTime: 'immediate' | 'delayed' | 'contextual';\n}\n\nexport type GroupRole = 'leader' | 'supporter' | 'joker' | 'analyst' | 'mediator' | 'observer';\n\n// Conversation context for AI processing\nexport interface ConversationContext {\n conversationId: string;\n participants: User[];\n recentMessages: NormalizedMessage[];\n groupDynamics: GroupDynamics;\n yapTrapSettings: YapTrapSettings;\n currentTopic?: string;\n sentiment: ConversationSentiment;\n}\n\nexport interface GroupDynamics {\n activityLevel: number; // 0-1 (quiet to very active)\n humorLevel: number; // 0-1 (serious to very humorous)\n conflictLevel: number; // 0-1 (harmonious to conflicted)\n topicFocus: number; // 0-1 (scattered to focused)\n dominantPersonalities: string[]; // User IDs of most active participants\n}\n\nexport interface ConversationSentiment {\n overall: number; // -1 to 1 (negative to positive)\n recent: number; // -1 to 1 (recent message sentiment)\n trend: 'improving' | 'declining' | 'stable';\n}\n\n// Configuration types\nexport interface YapTrapSettings {\n conversationId: string;\n engagementFrequency: number; // 0-1 (how often to respond)\n roastIntensity: number; // 0-1 (how spicy to get)\n responseDelay: number; // seconds to wait before responding\n silenceThreshold: number; // minutes of silence before initiating\n personalityAdaptation: boolean; // whether to adapt to group personality\n topicTracking: boolean; // whether to track conversation topics\n learningEnabled: boolean; // whether to learn from interactions\n}\n\nexport interface ConversationSettings {\n yapTrapEnabled: boolean;\n yapTrapSettings: YapTrapSettings;\n retentionDays: number;\n adminUsers: string[];\n customPrompts?: string[];\n}\n\nexport interface UserPreferences {\n notifications: boolean;\n dataRetention: number; // days\n personalityLearning: boolean;\n customInstructions?: string;\n}\n\n// Platform-specific types\nexport interface PlatformMessage {\n platform: Platform;\n rawPayload: any;\n signature?: string;\n timestamp: Date;\n}\n\nexport interface PlatformResponse {\n platform: Platform;\n conversationId: string;\n content: string;\n messageType: MessageType;\n metadata?: Record;\n}\n\n// API response types\nexport interface APIResponse {\n success: boolean;\n data?: T;\n error?: string;\n timestamp: Date;\n}\n\nexport interface PaginatedResponse extends APIResponse {\n pagination: {\n page: number;\n limit: number;\n total: number;\n hasMore: boolean;\n };\n}\n\n// Monitoring and logging types\nexport interface SystemLog {\n id: string;\n timestamp: Date;\n level: LogLevel;\n component: string;\n message: string;\n metadata?: Record;\n conversationId?: string;\n userId?: string;\n}\n\nexport interface SystemMetrics {\n timestamp: Date;\n messageVolume: number;\n responseRate: number;\n averageResponseTime: number;\n errorRate: number;\n activeConversations: number;\n activeUsers: number;\n platformBreakdown: Record;\n}\n\nexport interface HealthStatus {\n status: 'healthy' | 'degraded' | 'unhealthy';\n timestamp: Date;\n components: Record;\n uptime: number;\n}\n\nexport interface ComponentHealth {\n status: 'healthy' | 'degraded' | 'unhealthy';\n lastCheck: Date;\n responseTime?: number;\n errorRate?: number;\n message?: string;\n}\n\n// AI service types\nexport interface AIProvider {\n name: string;\n model: string;\n apiKey: string;\n baseUrl?: string;\n maxTokens?: number;\n temperature?: number;\n}\n\nexport interface AIResponse {\n content: string;\n confidence: number;\n reasoning?: string;\n metadata: {\n provider: string;\n model: string;\n tokensUsed: number;\n responseTime: number;\n };\n}\n\n// Configuration management\nexport interface ConfigurationItem {\n key: string;\n value: any;\n scope: 'global' | 'conversation' | 'user';\n scopeId?: string;\n updatedAt: Date;\n updatedBy: string;\n}\n\n// Error types\nexport interface YapTrapError {\n code: string;\n message: string;\n component: string;\n timestamp: Date;\n context?: Record;\n stack?: string;\n}\n\n// Webhook validation\nexport interface WebhookValidation {\n isValid: boolean;\n platform: Platform;\n signature?: string;\n timestamp?: Date;\n error?: string;\n}\n\n// Database query types\nexport interface QueryOptions {\n limit?: number;\n offset?: number;\n orderBy?: string;\n orderDirection?: 'ASC' | 'DESC';\n filters?: Record;\n}\n\nexport interface DatabaseResult {\n data: T[];\n total: number;\n hasMore: boolean;\n}\n\n// Testing types\nexport interface TestScenario {\n id: string;\n name: string;\n description: string;\n platform: Platform;\n inputMessages: NormalizedMessage[];\n expectedResponse?: string;\n expectedBehavior: string;\n tags: string[];\n}\n\nexport interface TestResult {\n scenarioId: string;\n passed: boolean;\n actualResponse?: string;\n actualBehavior: string;\n executionTime: number;\n timestamp: Date;\n error?: string;\n}",[],[456,462],{"_103":469,"_95":470,"_152":471,"_154":457,"_155":137,"_156":472,"_158":159,"_105":473,"_100":32,"_161":181,"_163":474,"_164":22,"_165":-5,"_166":475,"_98":476},"6220fc3e-8ead-11f0-9f90-0224a6c84d84","utils.ts","6220fce8-8ead-11f0-9f90-0224a6c84d84",6,["D",1757553577514],"/**\n * Shared utility functions for YapTrap system\n * These utilities work in both frontend and backend environments\n */\n\nimport type { \n NormalizedMessage, \n Platform, \n LogLevel, \n SystemLog,\n YapTrapError \n} from './types.ts';\n\n// ID generation utilities\nexport function generateId(prefix?: string): string {\n const timestamp = Date.now().toString(36);\n const random = Math.random().toString(36).substring(2);\n return prefix ? `${prefix}_${timestamp}_${random}` : `${timestamp}_${random}`;\n}\n\nexport function generateConversationId(platform: Platform, platformId: string): string {\n return `conv_${platform}_${platformId.replace(/[^a-zA-Z0-9]/g, '_')}`;\n}\n\nexport function generateUserId(platform: Platform, platformUserId: string): string {\n return `user_${platform}_${platformUserId.replace(/[^a-zA-Z0-9]/g, '_')}`;\n}\n\n// Date and time utilities\nexport function formatTimestamp(date: Date): string {\n return date.toISOString();\n}\n\nexport function parseTimestamp(timestamp: string): Date {\n return new Date(timestamp);\n}\n\nexport function getTimeDifference(start: Date, end: Date): number {\n return end.getTime() - start.getTime();\n}\n\nexport function isWithinTimeWindow(timestamp: Date, windowMinutes: number): boolean {\n const now = new Date();\n const diffMinutes = (now.getTime() - timestamp.getTime()) / (1000 * 60);\n return diffMinutes <= windowMinutes;\n}\n\n// String utilities\nexport function sanitizeString(input: string): string {\n return input\n .replace(/[<>]/g, '') // Remove potential HTML tags\n .replace(/javascript:/gi, '') // Remove javascript: protocols\n .trim();\n}\n\nexport function truncateString(str: string, maxLength: number): string {\n if (str.length <= maxLength) return str;\n return str.substring(0, maxLength - 3) + '...';\n}\n\nexport function extractMentions(content: string): string[] {\n const mentionRegex = /@(\\w+)/g;\n const mentions: string[] = [];\n let match;\n \n while ((match = mentionRegex.exec(content)) !== null) {\n mentions.push(match[1]);\n }\n \n return mentions;\n}\n\nexport function removeMentions(content: string): string {\n return content.replace(/@\\w+/g, '').trim();\n}\n\n// Validation utilities\nexport function isValidEmail(email: string): boolean {\n const emailRegex = /^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/;\n return emailRegex.test(email);\n}\n\nexport function isValidUrl(url: string): boolean {\n try {\n new URL(url);\n return true;\n } catch {\n return false;\n }\n}\n\nexport function isValidPlatform(platform: string): platform is Platform {\n const validPlatforms: Platform[] = ['slack', 'sms', 'email', 'whatsapp', 'messenger'];\n return validPlatforms.includes(platform as Platform);\n}\n\nexport function isValidLogLevel(level: string): level is LogLevel {\n const validLevels: LogLevel[] = ['debug', 'info', 'warn', 'error'];\n return validLevels.includes(level as LogLevel);\n}\n\n// Object utilities\nexport function deepClone(obj: T): T {\n return JSON.parse(JSON.stringify(obj));\n}\n\nexport function mergeObjects>(target: T, source: Partial): T {\n return { ...target, ...source };\n}\n\nexport function pickProperties(obj: T, keys: K[]): Pick {\n const result = {} as Pick;\n keys.forEach(key => {\n if (key in obj) {\n result[key] = obj[key];\n }\n });\n return result;\n}\n\nexport function omitProperties(obj: T, keys: K[]): Omit {\n const result = { ...obj };\n keys.forEach(key => {\n delete result[key];\n });\n return result;\n}\n\n// Array utilities\nexport function uniqueArray(array: T[]): T[] {\n return [...new Set(array)];\n}\n\nexport function groupBy(array: T[], key: K): Record {\n return array.reduce((groups, item) => {\n const groupKey = String(item[key]);\n if (!groups[groupKey]) {\n groups[groupKey] = [];\n }\n groups[groupKey].push(item);\n return groups;\n }, {} as Record);\n}\n\nexport function sortBy(array: T[], key: keyof T, direction: 'asc' | 'desc' = 'asc'): T[] {\n return [...array].sort((a, b) => {\n const aVal = a[key];\n const bVal = b[key];\n \n if (aVal < bVal) return direction === 'asc' ? -1 : 1;\n if (aVal > bVal) return direction === 'asc' ? 1 : -1;\n return 0;\n });\n}\n\n// Message utilities\nexport function normalizeMessageContent(content: string): string {\n return content\n .replace(/\\s+/g, ' ') // Normalize whitespace\n .replace(/[^\\w\\s.,!?;:'\"()-]/g, '') // Remove special characters\n .trim();\n}\n\nexport function extractKeywords(content: string): string[] {\n const words = content\n .toLowerCase()\n .replace(/[^\\w\\s]/g, '')\n .split(/\\s+/)\n .filter(word => word.length > 3); // Filter out short words\n \n return uniqueArray(words);\n}\n\nexport function calculateMessageSimilarity(msg1: string, msg2: string): number {\n const words1 = new Set(extractKeywords(msg1));\n const words2 = new Set(extractKeywords(msg2));\n \n const intersection = new Set([...words1].filter(word => words2.has(word)));\n const union = new Set([...words1, ...words2]);\n \n return union.size === 0 ? 0 : intersection.size / union.size;\n}\n\n// Logging utilities\nexport function createLogEntry(\n level: LogLevel,\n component: string,\n message: string,\n metadata?: Record,\n conversationId?: string,\n userId?: string\n): SystemLog {\n return {\n id: generateId('log'),\n timestamp: new Date(),\n level,\n component,\n message,\n metadata,\n conversationId,\n userId\n };\n}\n\nexport function formatLogMessage(log: SystemLog): string {\n const timestamp = log.timestamp.toISOString();\n const level = log.level.toUpperCase().padEnd(5);\n const component = log.component.padEnd(15);\n \n let message = `[${timestamp}] ${level} ${component} ${log.message}`;\n \n if (log.conversationId) {\n message += ` (conv: ${log.conversationId})`;\n }\n \n if (log.userId) {\n message += ` (user: ${log.userId})`;\n }\n \n return message;\n}\n\n// Error handling utilities\nexport function createError(\n code: string,\n message: string,\n component: string,\n context?: Record\n): YapTrapError {\n return {\n code,\n message,\n component,\n timestamp: new Date(),\n context,\n stack: new Error().stack\n };\n}\n\nexport function isYapTrapError(error: any): error is YapTrapError {\n return error && \n typeof error.code === 'string' && \n typeof error.message === 'string' && \n typeof error.component === 'string' &&\n error.timestamp instanceof Date;\n}\n\n// Rate limiting utilities\nexport function createRateLimitKey(identifier: string, window: string): string {\n const timestamp = Math.floor(Date.now() / 1000);\n const windowSeconds = parseTimeWindow(window);\n const windowStart = Math.floor(timestamp / windowSeconds) * windowSeconds;\n return `${identifier}:${windowStart}`;\n}\n\nexport function parseTimeWindow(window: string): number {\n const match = window.match(/^(\\d+)([smhd])$/);\n if (!match) throw new Error(`Invalid time window format: ${window}`);\n \n const value = parseInt(match[1]);\n const unit = match[2];\n \n switch (unit) {\n case 's': return value;\n case 'm': return value * 60;\n case 'h': return value * 3600;\n case 'd': return value * 86400;\n default: throw new Error(`Invalid time unit: ${unit}`);\n }\n}\n\n// Configuration utilities\nexport function getConfigValue(\n config: Record,\n key: string,\n defaultValue: T\n): T {\n const keys = key.split('.');\n let value = config;\n \n for (const k of keys) {\n if (value && typeof value === 'object' && k in value) {\n value = value[k];\n } else {\n return defaultValue;\n }\n }\n \n return value as T;\n}\n\nexport function setConfigValue(\n config: Record,\n key: string,\n value: any\n): Record {\n const keys = key.split('.');\n const result = deepClone(config);\n let current = result;\n \n for (let i = 0; i < keys.length - 1; i++) {\n const k = keys[i];\n if (!current[k] || typeof current[k] !== 'object') {\n current[k] = {};\n }\n current = current[k];\n }\n \n current[keys[keys.length - 1]] = value;\n return result;\n}\n\n// Retry utilities\nexport async function withRetry(\n fn: () => Promise,\n maxAttempts: number = 3,\n delayMs: number = 1000,\n backoffMultiplier: number = 2\n): Promise {\n let lastError: Error;\n \n for (let attempt = 1; attempt <= maxAttempts; attempt++) {\n try {\n return await fn();\n } catch (error) {\n lastError = error as Error;\n \n if (attempt === maxAttempts) {\n throw lastError;\n }\n \n const delay = delayMs * Math.pow(backoffMultiplier, attempt - 1);\n await sleep(delay);\n }\n }\n \n throw lastError!;\n}\n\nexport function sleep(ms: number): Promise {\n return new Promise(resolve => setTimeout(resolve, ms));\n}\n\n// Environment utilities (backend only - check for Deno)\nexport function getEnvVar(key: string, defaultValue?: string): string {\n // Check if we're in a Deno environment (backend)\n if (typeof Deno !== 'undefined') {\n return Deno.env.get(key) || defaultValue || '';\n }\n \n // Frontend environment - return empty string or default\n return defaultValue || '';\n}\n\nexport function isProduction(): boolean {\n return getEnvVar('NODE_ENV') === 'production' || getEnvVar('DENO_ENV') === 'production';\n}\n\nexport function isDevelopment(): boolean {\n return !isProduction();\n}\n\n// Performance utilities\nexport function measureTime(fn: () => T): { result: T; duration: number } {\n const start = performance.now();\n const result = fn();\n const duration = performance.now() - start;\n return { result, duration };\n}\n\nexport async function measureTimeAsync(fn: () => Promise): Promise<{ result: T; duration: number }> {\n const start = performance.now();\n const result = await fn();\n const duration = performance.now() - start;\n return { result, duration };\n}\n\n// Hash utilities (simple hash for non-cryptographic purposes)\nexport function simpleHash(str: string): string {\n let hash = 0;\n for (let i = 0; i < str.length; i++) {\n const char = str.charCodeAt(i);\n hash = ((hash << 5) - hash) + char;\n hash = hash & hash; // Convert to 32-bit integer\n }\n return Math.abs(hash).toString(36);\n}",[],[456,470],[456],{"_103":479,"_95":480,"_152":481,"_154":-5,"_155":137,"_156":482,"_158":159,"_105":483,"_100":32,"_161":162,"_163":-5,"_164":22,"_165":-5,"_166":484,"_98":493},"99b4a662-8f18-11f0-b009-0224a6c84d84","tests","31e981b6-b32d-4abf-b9b3-d08a33524794",56,["D",1757599626888],[485],{"_103":486,"_95":487,"_152":488,"_154":481,"_155":137,"_156":482,"_158":159,"_105":489,"_100":32,"_161":181,"_163":490,"_164":22,"_165":-5,"_166":491,"_98":492},"99b487e0-8f18-11f0-b009-0224a6c84d84","test-suite.ts","99b488b2-8f18-11f0-b009-0224a6c84d84",["D",1757599626888],"/**\n * Comprehensive Test Suite for YapTrap\n * Unit, integration, and end-to-end tests\n */\n\nimport type { TestScenario, TestResult } from '../shared/types.ts';\nimport { generateId } from '../shared/utils.ts';\n\nexport interface TestSuite {\n name: string;\n description: string;\n scenarios: TestScenario[];\n}\n\nexport class YapTrapTestRunner {\n private testSuites: TestSuite[];\n private results: Map;\n \n constructor() {\n this.testSuites = this.initializeTestSuites();\n this.results = new Map();\n }\n \n /**\n * Initialize all test suites\n */\n private initializeTestSuites(): TestSuite[] {\n return [\n this.createAITestSuite(),\n this.createPersonaTestSuite(),\n this.createPlatformTestSuite(),\n this.createDatabaseTestSuite(),\n this.createEndToEndTestSuite()\n ];\n }\n \n /**\n * AI Engine Test Suite\n */\n private createAITestSuite(): TestSuite {\n return {\n name: 'AI Engine Tests',\n description: 'Tests for AI response generation and personality analysis',\n scenarios: [\n {\n id: generateId('test'),\n name: 'Basic AI Response Generation',\n description: 'Test that AI generates appropriate responses',\n platform: 'slack',\n inputMessages: [{\n id: generateId('msg'),\n conversationId: 'test_conv',\n userId: 'test_user',\n content: 'What do you think about this new feature?',\n timestamp: new Date(),\n platform: 'slack',\n messageType: 'text',\n metadata: {}\n }],\n expectedBehavior: 'Should generate a witty, contextual response under 150 characters',\n tags: ['ai', 'response-generation', 'unit']\n },\n {\n id: generateId('test'),\n name: 'Direct Mention Response',\n description: 'Test response when YapTrap is directly mentioned',\n platform: 'slack',\n inputMessages: [{\n id: generateId('msg'),\n conversationId: 'test_conv',\n userId: 'test_user',\n content: 'Hey @yaptrap, what do you think about refactoring?',\n timestamp: new Date(),\n platform: 'slack',\n messageType: 'text',\n metadata: {}\n }],\n expectedBehavior: 'Should always respond when directly mentioned',\n tags: ['ai', 'mention-detection', 'unit']\n },\n {\n id: generateId('test'),\n name: 'Personality Adaptation',\n description: 'Test that responses adapt to group personality',\n platform: 'slack',\n inputMessages: [{\n id: generateId('msg'),\n conversationId: 'test_conv',\n userId: 'test_user',\n content: 'This is a very serious technical discussion about architecture',\n timestamp: new Date(),\n platform: 'slack',\n messageType: 'text',\n metadata: {}\n }],\n expectedBehavior: 'Should generate more serious, technical response for formal context',\n tags: ['ai', 'personality', 'adaptation']\n }\n ]\n };\n }\n \n /**\n * Persona System Test Suite\n */\n private createPersonaTestSuite(): TestSuite {\n return {\n name: 'Persona System Tests',\n description: 'Tests for the 6-persona development team discussions',\n scenarios: [\n {\n id: generateId('test'),\n name: 'Persona Initialization',\n description: 'Test that all 6 personas are properly initialized',\n platform: 'persona',\n inputMessages: [],\n expectedBehavior: 'Should have 6 distinct personas with unique roles and personalities',\n tags: ['persona', 'initialization', 'unit']\n },\n {\n id: generateId('test'),\n name: 'Issue Analysis',\n description: 'Test system issue detection and analysis',\n platform: 'persona',\n inputMessages: [],\n expectedBehavior: 'Should identify and categorize system issues from logs',\n tags: ['persona', 'analysis', 'integration']\n },\n {\n id: generateId('test'),\n name: 'Discussion Flow',\n description: 'Test persona discussion progression',\n platform: 'persona',\n inputMessages: [],\n expectedBehavior: 'Should generate coherent multi-persona discussion with distinct voices',\n tags: ['persona', 'discussion', 'integration']\n },\n {\n id: generateId('test'),\n name: 'Technical Lead Analysis',\n description: 'Test Technical Lead persona responses',\n platform: 'persona',\n inputMessages: [],\n expectedBehavior: 'Should provide architectural and technical debt analysis',\n tags: ['persona', 'tech-lead', 'unit']\n },\n {\n id: generateId('test'),\n name: 'DevOps Security Focus',\n description: 'Test DevOps persona security considerations',\n platform: 'persona',\n inputMessages: [],\n expectedBehavior: 'Should emphasize security and operational concerns',\n tags: ['persona', 'devops', 'security']\n }\n ]\n };\n }\n \n /**\n * Platform Integration Test Suite\n */\n private createPlatformTestSuite(): TestSuite {\n return {\n name: 'Platform Integration Tests',\n description: 'Tests for Slack, SMS, Email platform handlers',\n scenarios: [\n {\n id: generateId('test'),\n name: 'Slack Message Normalization',\n description: 'Test Slack webhook message normalization',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should convert Slack events to normalized message format',\n tags: ['platform', 'slack', 'normalization']\n },\n {\n id: generateId('test'),\n name: 'SMS Message Processing',\n description: 'Test SMS message handling via Telnyx',\n platform: 'sms',\n inputMessages: [],\n expectedBehavior: 'Should process SMS messages and generate appropriate responses',\n tags: ['platform', 'sms', 'telnyx']\n },\n {\n id: generateId('test'),\n name: 'Email Thread Handling',\n description: 'Test email conversation threading',\n platform: 'email',\n inputMessages: [],\n expectedBehavior: 'Should maintain email thread context and reply appropriately',\n tags: ['platform', 'email', 'threading']\n },\n {\n id: generateId('test'),\n name: 'Webhook Signature Validation',\n description: 'Test webhook security validation',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should validate webhook signatures and reject invalid requests',\n tags: ['platform', 'security', 'validation']\n }\n ]\n };\n }\n \n /**\n * Database Test Suite\n */\n private createDatabaseTestSuite(): TestSuite {\n return {\n name: 'Database Tests',\n description: 'Tests for database operations and data integrity',\n scenarios: [\n {\n id: generateId('test'),\n name: 'Schema Initialization',\n description: 'Test database schema creation',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should create all required tables and indexes',\n tags: ['database', 'schema', 'initialization']\n },\n {\n id: generateId('test'),\n name: 'Message Storage',\n description: 'Test message storage and retrieval',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should store and retrieve messages correctly',\n tags: ['database', 'messages', 'crud']\n },\n {\n id: generateId('test'),\n name: 'Personality Profile Updates',\n description: 'Test personality profile learning and updates',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should update user personality profiles based on messages',\n tags: ['database', 'personality', 'learning']\n },\n {\n id: generateId('test'),\n name: 'Data Retention',\n description: 'Test data cleanup and retention policies',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should clean up old data according to retention policies',\n tags: ['database', 'retention', 'cleanup']\n }\n ]\n };\n }\n \n /**\n * End-to-End Test Suite\n */\n private createEndToEndTestSuite(): TestSuite {\n return {\n name: 'End-to-End Tests',\n description: 'Complete workflow tests from webhook to response',\n scenarios: [\n {\n id: generateId('test'),\n name: 'Complete Message Pipeline',\n description: 'Test full message processing from webhook to AI response',\n platform: 'slack',\n inputMessages: [{\n id: generateId('msg'),\n conversationId: 'e2e_conv',\n userId: 'e2e_user',\n content: 'Hey everyone, should we implement this new feature?',\n timestamp: new Date(),\n platform: 'slack',\n messageType: 'text',\n metadata: {}\n }],\n expectedBehavior: 'Should process message, analyze context, generate response, and store results',\n tags: ['e2e', 'pipeline', 'integration']\n },\n {\n id: generateId('test'),\n name: 'Multi-Platform Consistency',\n description: 'Test consistent behavior across platforms',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should behave consistently across Slack, SMS, and Email',\n tags: ['e2e', 'multi-platform', 'consistency']\n },\n {\n id: generateId('test'),\n name: 'Persona Discussion to Implementation',\n description: 'Test complete persona discussion workflow',\n platform: 'persona',\n inputMessages: [],\n expectedBehavior: 'Should detect issue, run persona discussion, and generate implementation ticket',\n tags: ['e2e', 'persona', 'workflow']\n },\n {\n id: generateId('test'),\n name: 'Load Testing',\n description: 'Test system under high message volume',\n platform: 'slack',\n inputMessages: [],\n expectedBehavior: 'Should handle high message volume without degradation',\n tags: ['e2e', 'performance', 'load']\n }\n ]\n };\n }\n \n /**\n * Run all test suites\n */\n async runAllTests(): Promise> {\n console.log('🧪 Starting YapTrap Test Suite...');\n \n for (const suite of this.testSuites) {\n console.log(`\\n📋 Running ${suite.name}...`);\n const suiteResults = await this.runTestSuite(suite);\n this.results.set(suite.name, suiteResults);\n }\n \n this.printTestSummary();\n return this.results;\n }\n \n /**\n * Run a specific test suite\n */\n async runTestSuite(suite: TestSuite): Promise {\n const results: TestResult[] = [];\n \n for (const scenario of suite.scenarios) {\n console.log(` 🔍 ${scenario.name}...`);\n const result = await this.runTestScenario(scenario);\n results.push(result);\n \n const status = result.passed ? '✅' : '❌';\n console.log(` ${status} ${result.passed ? 'PASSED' : 'FAILED'} (${result.executionTime}ms)`);\n \n if (!result.passed && result.error) {\n console.log(` Error: ${result.error}`);\n }\n }\n \n return results;\n }\n \n /**\n * Run a single test scenario\n */\n async runTestScenario(scenario: TestScenario): Promise {\n const startTime = Date.now();\n \n try {\n // This is a simplified test runner - in practice, each test would have specific logic\n const result: TestResult = {\n scenarioId: scenario.id,\n passed: true, // Placeholder - would implement actual test logic\n actualBehavior: 'Test executed successfully',\n executionTime: Date.now() - startTime,\n timestamp: new Date()\n };\n \n // Simulate some test execution time\n await new Promise(resolve => setTimeout(resolve, Math.random() * 100));\n \n return result;\n \n } catch (error) {\n return {\n scenarioId: scenario.id,\n passed: false,\n actualBehavior: 'Test failed with error',\n executionTime: Date.now() - startTime,\n timestamp: new Date(),\n error: error.message\n };\n }\n }\n \n /**\n * Print test summary\n */\n private printTestSummary(): void {\n console.log('\\n📊 Test Summary:');\n console.log('================');\n \n let totalTests = 0;\n let totalPassed = 0;\n \n for (const [suiteName, results] of this.results.entries()) {\n const passed = results.filter(r => r.passed).length;\n const total = results.length;\n const percentage = total > 0 ? Math.round((passed / total) * 100) : 0;\n \n totalTests += total;\n totalPassed += passed;\n \n console.log(`${suiteName}: ${passed}/${total} (${percentage}%)`);\n }\n \n const overallPercentage = totalTests > 0 ? Math.round((totalPassed / totalTests) * 100) : 0;\n console.log(`\\nOverall: ${totalPassed}/${totalTests} (${overallPercentage}%)`);\n \n if (overallPercentage >= 90) {\n console.log('🎉 Excellent test coverage!');\n } else if (overallPercentage >= 70) {\n console.log('👍 Good test coverage');\n } else {\n console.log('⚠️ Test coverage needs improvement');\n }\n }\n \n /**\n * Get test results\n */\n getResults(): Map {\n return this.results;\n }\n \n /**\n * Get test suites\n */\n getTestSuites(): TestSuite[] {\n return this.testSuites;\n }\n}",[],[480,487],[480],{"_103":495,"_95":496,"_152":497,"_154":-5,"_155":137,"_156":346,"_158":159,"_105":498,"_100":32,"_161":425,"_163":499,"_164":22,"_165":-5,"_166":500,"_98":501},"f5236d92-8eac-11f0-8833-0224a6c84d84","README.md","f5236f04-8eac-11f0-8833-0224a6c84d84",["D",1757553394654],"# YapTrap - Multi-Platform Automated Chatbot System\n\nYapTrap is an intelligent chatbot designed to participate in multi-party conversations across various messaging platforms. It learns individual personalities, provides contextual roasts and witty remarks, and can engage in meaningful conversations when directly addressed.\n\n## Project Structure\n\n```\n├── docs/ # Project documentation\n│ ├── SRS.md # Software Requirements Specification\n│ ├── SDD.md # Software Design Document\n│ ├── STP.md # Software Test Plan\n│ └── RUNBOOK.md # Operational Deployment Plan\n├── backend/ # Core backend services\n│ ├── index.ts # Main HTTP entry point\n│ ├── database/ # Database operations\n│ ├── platforms/ # Platform-specific handlers\n│ ├── ai/ # LLM integration\n│ └── monitoring/ # System monitoring\n├── frontend/ # Admin web interface\n├── shared/ # Shared utilities and types\n└── tests/ # Test suites\n```\n\n## Supported Platforms\n\n- Slack (via Slack App API)\n- SMS/MMS (via Telnyx API)\n- Email (via SendGrid API)\n- WhatsApp Business API (webhook-based)\n- Additional platforms via webhook integrations\n\n## Core Features\n\n1. **Personality Learning** - Analyzes conversation patterns to understand group dynamics\n2. **Contextual Engagement** - Provides relevant, witty responses based on conversation context\n3. **Multi-Platform Support** - Unified backend with platform-specific adapters\n4. **Admin Dashboard** - Web-based monitoring and configuration interface\n5. **Self-Monitoring** - Automated system health and performance tracking\n\n## Development Status\n\n🚧 **In Development** - Initial architecture and core components being implemented\n\n## Quick Start\n\n1. Set up environment variables in `.env`\n2. Deploy to Val Town\n3. Configure platform webhooks\n4. Access admin dashboard at deployed URL\n\n## Documentation\n\nSee `/docs/` directory for detailed specifications and design documents.",[],[496],{"_103":503,"_95":504,"_152":505,"_154":-5,"_155":137,"_156":506,"_158":159,"_105":507,"_100":346,"_161":392,"_163":508,"_164":22,"_165":-5,"_166":509,"_98":510},"3a906568-8eaf-11f0-9db0-0224a6c84d84","main.ts","e0b97664-8eaa-11f0-b609-0224a6c84d84",27,["D",1757554370129],"/**\n * YapTrap Main Entry Point\n * Redirects to the backend HTTP handler\n */\n\n// Import and re-export the backend handler\nexport { default } from './backend/index.ts';",[],[504],"files",[149,168,175,185,194,204,213,219,229,240,247,255,265,272,281,290,297,305,314,322,331,340,351,358,367,376,385,397,404,413,420,429,438,445,454,460,468,478,485,494,502],"counts",{"_515":32,"_516":200,"_517":32,"_518":157},"pulls","branches","forks","envVars","contents",[],{"_103":230,"_95":231,"_152":232,"_154":216,"_155":137,"_156":233,"_158":159,"_105":522,"_100":235,"_161":181,"_163":236,"_164":22,"_165":-5,"_98":523},["D",1757599290574],[151,215,231],"upstreamProject","upstreamBranch","emptyEnvVars","isNew","flags",[],"routes/_app.x.$handle.$name.branch.$branch.history_.$",{"_93":94,"_95":96,"_97":43,"_98":99,"_101":532,"_425":537,"_540":541,"_589":590,"_602":-7,"_603":32,"_604":22},{"_103":104,"_95":96,"_105":533,"_107":-5,"_108":22,"_109":110,"_111":-5,"_112":-5,"_14":113,"_114":-5,"_115":22,"_116":-5,"_117":32,"_16":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":534,"_131":535,"_134":536,"_146":22},["D",1757552501410],{"_93":94,"_129":130},{"_93":94,"_133":130},{"_100":136,"_95":43,"_103":137,"_138":104,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},{"_103":230,"_95":231,"_152":232,"_154":216,"_155":137,"_156":233,"_158":159,"_105":538,"_100":235,"_161":181,"_163":236,"_164":22,"_165":-5,"_98":539},["D",1757599290574],[151,215,231],"history",[542,544,550,555,560,565,570,575,580,585],{"_103":230,"_155":137,"_152":232,"_95":231,"_163":236,"_161":181,"_100":235,"_154":216,"_105":543,"_156":233,"_165":-5},["D",1757599290574],{"_103":545,"_155":137,"_152":232,"_95":231,"_163":546,"_161":181,"_100":547,"_154":216,"_105":548,"_156":549,"_165":-5},"b0259880-8f17-11f0-bd11-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",8,["D",1757599235067],43,{"_103":551,"_155":137,"_152":232,"_95":231,"_163":552,"_161":181,"_100":157,"_154":216,"_105":553,"_156":554,"_165":-5},"aa94bcf2-8f17-11f0-ad9a-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Disable foreign key constraints for now\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757599225724],42,{"_103":556,"_155":137,"_152":232,"_95":231,"_163":557,"_161":181,"_100":472,"_154":216,"_105":558,"_156":559,"_165":-5},"a6b75036-8f17-11f0-8d5b-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Disable foreign key constraints for now\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757599219245],41,{"_103":561,"_155":137,"_152":232,"_95":231,"_163":562,"_161":181,"_100":225,"_154":216,"_105":563,"_156":564,"_165":-5},"a2fe855e-8f17-11f0-b971-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Disable foreign key constraints for now\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757599212976],40,{"_103":566,"_155":137,"_152":232,"_95":231,"_163":567,"_161":181,"_100":391,"_154":216,"_105":568,"_156":569,"_165":-5},"9e61e702-8f17-11f0-a2a0-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Disable foreign key constraints for now\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757599205265],39,{"_103":571,"_155":137,"_152":232,"_95":231,"_163":572,"_161":181,"_100":417,"_154":216,"_105":573,"_156":574,"_165":-5},"9a1ca16e-8f17-11f0-bbea-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Disable foreign key constraints for now\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id),\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757599198079],38,{"_103":576,"_155":137,"_152":232,"_95":231,"_163":577,"_161":181,"_100":346,"_154":216,"_105":578,"_156":579,"_165":-5},"b95b389a-8eb0-11f0-b523-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Disable foreign key constraints for now\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id),\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757555012344],36,{"_103":581,"_155":137,"_152":232,"_95":231,"_163":582,"_161":181,"_100":200,"_154":216,"_105":583,"_156":584,"_165":-5},"568b5408-8eaf-11f0-916b-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id),\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n UNIQUE(key, scope, scope_id)\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757554417072],32,{"_103":586,"_155":137,"_152":232,"_95":231,"_163":587,"_161":181,"_100":32,"_154":216,"_105":588,"_156":157,"_165":-5},"8a466aaa-8ead-11f0-8658-0224a6c84d84","/**\n * Database schema definitions for YapTrap system\n * SQLite schema with comprehensive indexing for performance\n */\n\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Schema version for migration tracking\nexport const SCHEMA_VERSION = 1;\nexport const TABLE_PREFIX = 'yaptrap_v1_';\n\n// Table names with versioning\nexport const TABLES = {\n conversations: `${TABLE_PREFIX}conversations`,\n users: `${TABLE_PREFIX}users`,\n messages: `${TABLE_PREFIX}messages`,\n conversation_participants: `${TABLE_PREFIX}conversation_participants`,\n system_logs: `${TABLE_PREFIX}system_logs`,\n configuration: `${TABLE_PREFIX}configuration`,\n personality_profiles: `${TABLE_PREFIX}personality_profiles`,\n metrics: `${TABLE_PREFIX}metrics`,\n webhooks: `${TABLE_PREFIX}webhooks`\n} as const;\n\n/**\n * Initialize all database tables and indexes\n * Call this on application startup\n */\nexport async function initializeDatabase(): Promise {\n console.log('Initializing YapTrap database schema...');\n \n try {\n // Create tables in dependency order\n await createConversationsTable();\n await createUsersTable();\n await createMessagesTable();\n await createConversationParticipantsTable();\n await createSystemLogsTable();\n await createConfigurationTable();\n await createPersonalityProfilesTable();\n await createMetricsTable();\n await createWebhooksTable();\n \n // Create indexes for performance\n await createIndexes();\n \n console.log('Database schema initialized successfully');\n } catch (error) {\n console.error('Failed to initialize database schema:', error);\n throw error;\n }\n}\n\n/**\n * Conversations table - tracks group conversations across platforms\n */\nasync function createConversationsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversations} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_id TEXT NOT NULL,\n name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_activity DATETIME,\n settings JSON DEFAULT '{}',\n is_active BOOLEAN DEFAULT 1,\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Users table - individual participants across all conversations\n */\nasync function createUsersTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.users} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n platform_user_id TEXT NOT NULL,\n display_name TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n preferences JSON DEFAULT '{}',\n metadata JSON DEFAULT '{}'\n )\n `);\n}\n\n/**\n * Messages table - all messages in conversations\n */\nasync function createMessagesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.messages} (\n id TEXT PRIMARY KEY,\n conversation_id TEXT NOT NULL,\n user_id TEXT,\n content TEXT NOT NULL,\n message_type TEXT DEFAULT 'text',\n sender_type TEXT DEFAULT 'user',\n platform_message_id TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metadata JSON DEFAULT '{}',\n processed BOOLEAN DEFAULT 0,\n response_generated BOOLEAN DEFAULT 0,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Conversation participants - many-to-many relationship\n */\nasync function createConversationParticipantsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.conversation_participants} (\n conversation_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n role TEXT DEFAULT 'member',\n is_active BOOLEAN DEFAULT 1,\n last_seen DATETIME,\n message_count INTEGER DEFAULT 0,\n PRIMARY KEY (conversation_id, user_id),\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * System logs - comprehensive logging for monitoring\n */\nasync function createSystemLogsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.system_logs} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n level TEXT NOT NULL,\n component TEXT NOT NULL,\n message TEXT NOT NULL,\n metadata JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n error_code TEXT,\n stack_trace TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Configuration - system and conversation-specific settings\n */\nasync function createConfigurationTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.configuration} (\n key TEXT NOT NULL,\n value JSON NOT NULL,\n scope TEXT DEFAULT 'global',\n scope_id TEXT,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_by TEXT,\n description TEXT,\n PRIMARY KEY (key, scope, COALESCE(scope_id, ''))\n )\n `);\n}\n\n/**\n * Personality profiles - user personality analysis and preferences\n */\nasync function createPersonalityProfilesTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.personality_profiles} (\n user_id TEXT PRIMARY KEY,\n communication_style JSON DEFAULT '{}',\n interests JSON DEFAULT '[]',\n response_preferences JSON DEFAULT '{}',\n group_role TEXT,\n confidence REAL DEFAULT 0.0,\n message_count INTEGER DEFAULT 0,\n last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Metrics - system performance and usage metrics\n */\nasync function createMetricsTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.metrics} (\n id TEXT PRIMARY KEY,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n metric_type TEXT NOT NULL,\n metric_name TEXT NOT NULL,\n value REAL NOT NULL,\n dimensions JSON DEFAULT '{}',\n conversation_id TEXT,\n user_id TEXT,\n platform TEXT,\n FOREIGN KEY (conversation_id) REFERENCES ${TABLES.conversations}(id),\n FOREIGN KEY (user_id) REFERENCES ${TABLES.users}(id)\n )\n `);\n}\n\n/**\n * Webhooks - track webhook deliveries and responses\n */\nasync function createWebhooksTable(): Promise {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLES.webhooks} (\n id TEXT PRIMARY KEY,\n platform TEXT NOT NULL,\n event_type TEXT NOT NULL,\n payload JSON NOT NULL,\n signature TEXT,\n timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,\n processed BOOLEAN DEFAULT 0,\n processing_time REAL,\n response_status INTEGER,\n error_message TEXT,\n conversation_id TEXT,\n user_id TEXT\n )\n `);\n}\n\n/**\n * Create database indexes for optimal query performance\n */\nasync function createIndexes(): Promise {\n const indexes = [\n // Conversations indexes\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform ON ${TABLES.conversations}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_platform_id ON ${TABLES.conversations}(platform, platform_id)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_active ON ${TABLES.conversations}(is_active)`,\n `CREATE INDEX IF NOT EXISTS idx_conversations_last_activity ON ${TABLES.conversations}(last_activity)`,\n \n // Users indexes\n `CREATE INDEX IF NOT EXISTS idx_users_platform ON ${TABLES.users}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_users_platform_user_id ON ${TABLES.users}(platform, platform_user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_users_display_name ON ${TABLES.users}(display_name)`,\n \n // Messages indexes\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation ON ${TABLES.messages}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_user ON ${TABLES.messages}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON ${TABLES.messages}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_conversation_timestamp ON ${TABLES.messages}(conversation_id, timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_processed ON ${TABLES.messages}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_messages_sender_type ON ${TABLES.messages}(sender_type)`,\n \n // Conversation participants indexes\n `CREATE INDEX IF NOT EXISTS idx_participants_conversation ON ${TABLES.conversation_participants}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_user ON ${TABLES.conversation_participants}(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_participants_active ON ${TABLES.conversation_participants}(is_active)`,\n \n // System logs indexes\n `CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON ${TABLES.system_logs}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_level ON ${TABLES.system_logs}(level)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_component ON ${TABLES.system_logs}(component)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_conversation ON ${TABLES.system_logs}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_logs_user ON ${TABLES.system_logs}(user_id)`,\n \n // Configuration indexes\n `CREATE INDEX IF NOT EXISTS idx_config_scope ON ${TABLES.configuration}(scope)`,\n `CREATE INDEX IF NOT EXISTS idx_config_scope_id ON ${TABLES.configuration}(scope, scope_id)`,\n \n // Metrics indexes\n `CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON ${TABLES.metrics}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_type ON ${TABLES.metrics}(metric_type)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_name ON ${TABLES.metrics}(metric_name)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_conversation ON ${TABLES.metrics}(conversation_id)`,\n `CREATE INDEX IF NOT EXISTS idx_metrics_platform ON ${TABLES.metrics}(platform)`,\n \n // Webhooks indexes\n `CREATE INDEX IF NOT EXISTS idx_webhooks_platform ON ${TABLES.webhooks}(platform)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_timestamp ON ${TABLES.webhooks}(timestamp)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_processed ON ${TABLES.webhooks}(processed)`,\n `CREATE INDEX IF NOT EXISTS idx_webhooks_conversation ON ${TABLES.webhooks}(conversation_id)`\n ];\n \n for (const indexSql of indexes) {\n await sqlite.execute(indexSql);\n }\n}\n\n/**\n * Insert default configuration values\n */\nexport async function insertDefaultConfiguration(): Promise {\n const defaultConfigs = [\n {\n key: 'system.version',\n value: JSON.stringify(SCHEMA_VERSION),\n scope: 'global',\n description: 'Database schema version'\n },\n {\n key: 'yaptrap.default_settings',\n value: JSON.stringify({\n engagementFrequency: 0.3,\n roastIntensity: 0.5,\n responseDelay: 2,\n silenceThreshold: 30,\n personalityAdaptation: true,\n topicTracking: true,\n learningEnabled: true\n }),\n scope: 'global',\n description: 'Default YapTrap settings for new conversations'\n },\n {\n key: 'ai.default_provider',\n value: JSON.stringify({\n name: 'openai',\n model: 'gpt-4o-mini',\n maxTokens: 150,\n temperature: 0.8\n }),\n scope: 'global',\n description: 'Default AI provider configuration'\n },\n {\n key: 'retention.default_days',\n value: JSON.stringify(90),\n scope: 'global',\n description: 'Default message retention period in days'\n }\n ];\n \n for (const config of defaultConfigs) {\n await sqlite.execute(`\n INSERT OR IGNORE INTO ${TABLES.configuration} \n (key, value, scope, description, updated_by) \n VALUES (?, ?, ?, ?, 'system')\n `, [config.key, config.value, config.scope, config.description]);\n }\n}\n\n/**\n * Clean up old data based on retention policies\n */\nexport async function cleanupOldData(): Promise {\n console.log('Starting database cleanup...');\n \n try {\n // Get retention settings\n const retentionResult = await sqlite.execute(`\n SELECT value FROM ${TABLES.configuration} \n WHERE key = 'retention.default_days' AND scope = 'global'\n `);\n \n const retentionDays = retentionResult.length > 0 \n ? JSON.parse(retentionResult[0].value as string)\n : 90;\n \n const cutoffDate = new Date();\n cutoffDate.setDate(cutoffDate.getDate() - retentionDays);\n \n // Clean up old messages\n const messagesDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.messages} \n WHERE timestamp < ? AND sender_type != 'yaptrap'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old logs (keep error logs longer)\n const logsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.system_logs} \n WHERE timestamp < ? AND level != 'error'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old metrics (keep aggregated data)\n const metricsDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.metrics} \n WHERE timestamp < ? AND metric_type = 'raw'\n `, [cutoffDate.toISOString()]);\n \n // Clean up old webhooks\n const webhooksDeleted = await sqlite.execute(`\n DELETE FROM ${TABLES.webhooks} \n WHERE timestamp < ? AND processed = 1\n `, [cutoffDate.toISOString()]);\n \n console.log(`Database cleanup completed:\n - Messages deleted: ${messagesDeleted}\n - Logs deleted: ${logsDeleted} \n - Metrics deleted: ${metricsDeleted}\n - Webhooks deleted: ${webhooksDeleted}`);\n \n } catch (error) {\n console.error('Database cleanup failed:', error);\n throw error;\n }\n}\n\n/**\n * Get database statistics for monitoring\n */\nexport async function getDatabaseStats(): Promise> {\n const stats: Record = {};\n \n for (const [name, tableName] of Object.entries(TABLES)) {\n try {\n const result = await sqlite.execute(`SELECT COUNT(*) as count FROM ${tableName}`);\n stats[name] = result[0]?.count as number || 0;\n } catch (error) {\n console.error(`Failed to get stats for table ${tableName}:`, error);\n stats[name] = -1;\n }\n }\n \n return stats;\n}\n\n/**\n * Verify database integrity\n */\nexport async function verifyDatabaseIntegrity(): Promise {\n try {\n // Check foreign key constraints\n await sqlite.execute('PRAGMA foreign_key_check');\n \n // Check table integrity\n for (const tableName of Object.values(TABLES)) {\n await sqlite.execute(`PRAGMA integrity_check(${tableName})`);\n }\n \n return true;\n } catch (error) {\n console.error('Database integrity check failed:', error);\n return false;\n }\n}",["D",1757553644865],"versions",[591,593,594,595,596,597,598,599,600,601],{"_425":542,"_592":544},"previous",{"_425":544,"_592":550},{"_425":550,"_592":555},{"_425":555,"_592":560},{"_425":560,"_592":565},{"_425":565,"_592":570},{"_425":570,"_592":575},{"_425":575,"_592":580},{"_425":580,"_592":585},{"_425":585,"_592":-5},"versionNumber","page","hasNext","actionData","errors"]

History for schema.ts