[{"_1":2,"_173":-5,"_174":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","ff0419d68e57d8cc3e37e5d3da688157c5dd37c6","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_146":147,"_170":171,"_172":-5},"handle","lantholin","name","YapTrap","path","backend/database/schema.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","e0b6bffa-8eaa-11f0-b609-0224a6c84d84","createdAt",["D",1757552501410],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","01991120-79d9-71bf-ab3a-268b208877c6","pinnedFileId","pinPreview","screenshotVersion","likeCount","01991120-79d9-71bf-ab3a-2b0a78a4a875","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18zMkNlUHBrVWxlV3k1VG50eDloQVRhOU5WQWUifQ","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",69,"e0b72094-8eaa-11f0-b609-0224a6c84d84","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":160,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"d13f169a-8f17-11f0-a80e-0224a6c84d84","schema.ts","fileId","8a466b68-8ead-11f0-8658-0224a6c84d84","parentId","61dfd14b-5447-4628-8179-29172b1d6156","branchId","branchVersion",46,"latest",true,["D",1757599290574],9,"fileType","script","content","/**\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 to avoid issues during development\n await sqlite.execute('PRAGMA foreign_keys = OFF');\n \n // Drop existing tables to ensure clean schema\n const tables = Object.values(TABLES);\n for (const table of tables) {\n try {\n await sqlite.execute(`DROP TABLE IF EXISTS ${table}`);\n } catch (error) {\n console.log(`Note: Could not drop table ${table}:`, error.message);\n }\n }\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}","deleted","label",[168,169,149],"backend","database","permalink","https://www.val.town/x/lantholin/YapTrap/code/backend/database/schema.ts","extension","actionData","errors"]