[{"_1":2,"_174":-5,"_175":-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","a31a1550ad486cf340b818a53bc6ddc8c7db0295","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,"_149":150,"_171":172,"_173":-5},"handle","toowired","name","Prompt_Improver","path","backend/database.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":113,"_114":115,"_116":115,"_117":-5,"_118":32,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","d4959b7c-da57-11ef-a829-e6cdfca9ef9f","createdAt",["D",1737725623533],"deletedAt","legacyEnvVars","privacy","unlisted","description","imageUrl","02bea313-4abc-475e-987f-68da0b110d85","pinnedFileId","d1429bca-8a63-11f0-be6e-0224a6c84d84","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","332725b9-0bd3-42b9-83ec-05fac730cb70","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJ5MkxRcEVHOWxNcEFMQ0d1VHB5S1R0Unh2byJ9","org",{"_93":94,"_134":131,"_12":111},"avatar","branch",{"_137":138,"_95":43,"_101":139,"_140":102,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":96,"_147":94},"version",259,"d49603aa-da57-11ef-a829-e6cdfca9ef9f","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":151,"_95":152,"_153":154,"_155":156,"_157":139,"_158":159,"_160":115,"_103":161,"_137":162,"_163":164,"_165":166,"_167":20,"_168":-5,"_97":169},"7ba3826a-8b12-11f0-9517-0224a6c84d84","database.ts","fileId","8b7245a2-8b0b-11f0-9ac5-0224a6c84d84","parentId","4a9c041a-8a65-11f0-9a51-0224a6c84d84","branchId","branchVersion",237,"latest",["D",1757157194837],1,"fileType","script","content","// database.ts - Val Town SQLite database integration\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// Define interfaces for table rows for type safety\ninterface User {\n id: number;\n username: string;\n email?: string;\n profile: string;\n resources: string;\n preferences: string;\n created_at: string;\n updated_at: string;\n}\n\ninterface PromptHistory {\n id: number;\n user_id: number;\n original_prompt: string;\n improved_prompt: string;\n output_format: string;\n worker_chain?: string;\n metrics?: string;\n tags?: string;\n is_favorite: 0 | 1;\n is_deleted: 0 | 1;\n created_at: string;\n}\n\ninterface PromptTemplate {\n id: number;\n category: string;\n title: string;\n description?: string;\n template: string;\n variables?: string;\n tags?: string;\n use_count: number;\n rating: number;\n is_public: 0 | 1;\n created_by?: number;\n created_at: string;\n}\n\ninterface Session {\n id: string;\n user_id: number;\n data?: string;\n expires_at: string;\n created_at: string;\n}\n\ninterface ApiKey {\n id: string;\n user_id: number;\n key: string;\n created_at: string;\n}\n\nexport class Database {\n private db: any;\n\n constructor() {\n this.db = sqlite;\n this.initializeSchema().catch(console.error);\n }\n\n async initializeSchema(): Promise {\n // Users table\n await this.db.execute(`\n CREATE TABLE IF NOT EXISTS users (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n username TEXT UNIQUE NOT NULL,\n email TEXT UNIQUE,\n profile TEXT DEFAULT 'technical',\n resources TEXT DEFAULT '{}',\n preferences TEXT DEFAULT '{}',\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Prompt history table\n await this.db.execute(`\n CREATE TABLE IF NOT EXISTS prompt_history (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER,\n original_prompt TEXT NOT NULL,\n improved_prompt TEXT NOT NULL,\n output_format TEXT DEFAULT 'text',\n worker_chain TEXT,\n metrics TEXT,\n tags TEXT,\n is_favorite BOOLEAN DEFAULT 0,\n is_deleted BOOLEAN DEFAULT 0,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES users(id)\n )\n `);\n\n // Prompt templates/library\n await this.db.execute(`\n CREATE TABLE IF NOT EXISTS prompt_templates (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n category TEXT NOT NULL,\n title TEXT NOT NULL,\n description TEXT,\n template TEXT NOT NULL,\n variables TEXT,\n tags TEXT,\n use_count INTEGER DEFAULT 0,\n rating REAL DEFAULT 0,\n is_public BOOLEAN DEFAULT 1,\n created_by INTEGER,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (created_by) REFERENCES users(id)\n )\n `);\n\n // User sessions\n await this.db.execute(`\n CREATE TABLE IF NOT EXISTS sessions (\n id TEXT PRIMARY KEY,\n user_id INTEGER NOT NULL,\n data TEXT,\n expires_at DATETIME NOT NULL,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES users(id)\n )\n `);\n\n // Analytics/metrics\n await this.db.execute(`\n CREATE TABLE IF NOT EXISTS analytics (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id INTEGER,\n event_type TEXT NOT NULL,\n event_data TEXT,\n ip_address TEXT,\n user_agent TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES users(id)\n )\n `);\n\n // API Keys table\n await this.db.execute(`\n CREATE TABLE IF NOT EXISTS api_keys (\n id TEXT PRIMARY KEY,\n user_id INTEGER NOT NULL,\n key TEXT UNIQUE NOT NULL,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES users(id)\n )\n `);\n\n // Indexes\n await this.db.execute(`CREATE INDEX IF NOT EXISTS idx_history_user ON prompt_history(user_id)`);\n await this.db.execute(`CREATE INDEX IF NOT EXISTS idx_history_created ON prompt_history(created_at)`);\n await this.db.execute(`CREATE INDEX IF NOT EXISTS idx_templates_category ON prompt_templates(category)`);\n await this.db.execute(`CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at)`);\n }\n\n async createUser(username: string, email: string | null = null, profile: string = 'technical'): Promise {\n try {\n const result = await this.db.execute(\n `INSERT INTO users (username, email, profile) VALUES (?, ?, ?)`,\n [username, email, profile]\n );\n return { id: result.lastInsertRowid, username, email, profile, resources: '{}', preferences: '{}', created_at: new Date().toISOString(), updated_at: new Date().toISOString() };\n } catch (error) {\n if (error.message.includes('UNIQUE')) {\n return this.getUserByUsername(username);\n }\n throw error;\n }\n }\n\n async getUserByUsername(username: string): Promise {\n const result = await this.db.execute(\n `SELECT * FROM users WHERE username = ?`,\n [username]\n );\n return result.rows[0] as User;\n }\n\n async getUserById(userId: number): Promise {\n const result = await this.db.execute(\n `SELECT * FROM users WHERE id = ?`,\n [userId]\n );\n return result.rows[0] as User;\n }\n\n async updateUserProfile(userId: number, profile: string, resources: any | null = null, preferences: any | null = null): Promise {\n const updates: string[] = [];\n const values: any[] = [];\n \n if (profile) {\n updates.push('profile = ?');\n values.push(profile);\n }\n if (resources) {\n updates.push('resources = ?');\n values.push(JSON.stringify(resources));\n }\n if (preferences) {\n updates.push('preferences = ?');\n values.push(JSON.stringify(preferences));\n }\n \n updates.push('updated_at = CURRENT_TIMESTAMP');\n values.push(userId);\n \n await this.db.execute(\n `UPDATE users SET ${updates.join(', ')} WHERE id = ?`,\n values\n );\n }\n\n async savePromptHistory(userId: number, original: string, improved: string, options: any = {}): Promise {\n const {\n outputFormat = 'text',\n workerChain = null,\n metrics = null,\n tags = []\n } = options;\n\n const result = await this.db.execute(\n `INSERT INTO prompt_history \n (user_id, original_prompt, improved_prompt, output_format, worker_chain, metrics, tags) \n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n [\n userId,\n original,\n improved,\n outputFormat,\n workerChain ? JSON.stringify(workerChain) : null,\n metrics ? JSON.stringify(metrics) : null,\n tags.length > 0 ? JSON.stringify(tags) : null\n ]\n );\n\n return { id: result.lastInsertRowid, userId, original, improved };\n }\n\n async getUserHistory(userId: number, limit: number = 50, offset: number = 0): Promise {\n const result = await this.db.execute(\n `SELECT * FROM prompt_history \n WHERE user_id = ? AND is_deleted = 0 \n ORDER BY created_at DESC \n LIMIT ? OFFSET ?`,\n [userId, limit, offset]\n );\n \n return (result.rows as any[]).map(row => ({\n ...row,\n metrics: row.metrics ? JSON.parse(row.metrics) : null,\n tags: row.tags ? JSON.parse(row.tags) : [],\n workerChain: row.worker_chain ? JSON.parse(row.worker_chain) : null\n }));\n }\n\n async searchHistory(userId: number, query: string, limit: number = 20): Promise {\n const result = await this.db.execute(\n `SELECT * FROM prompt_history \n WHERE user_id = ? \n AND is_deleted = 0\n AND (original_prompt LIKE ? OR improved_prompt LIKE ? OR tags LIKE ?)\n ORDER BY created_at DESC \n LIMIT ?`,\n [userId, `%${query}%`, `%${query}%`, `%${query}%`, limit]\n );\n \n return (result.rows as any[]).map(row => ({\n ...row,\n metrics: row.metrics ? JSON.parse(row.metrics) : null,\n tags: row.tags ? JSON.parse(row.tags) : []\n }));\n }\n\n async toggleFavorite(historyId: number, userId: number): Promise {\n await this.db.execute(\n `UPDATE prompt_history \n SET is_favorite = NOT is_favorite \n WHERE id = ? AND user_id = ?`,\n [historyId, userId]\n );\n }\n\n async deleteHistoryItem(historyId: number, userId: number): Promise {\n await this.db.execute(\n `UPDATE prompt_history \n SET is_deleted = 1 \n WHERE id = ? AND user_id = ?`,\n [historyId, userId]\n );\n }\n\n async getFavorites(userId: number): Promise {\n const result = await this.db.execute(\n `SELECT * FROM prompt_history \n WHERE user_id = ? AND is_favorite = 1 AND is_deleted = 0 \n ORDER BY created_at DESC`,\n [userId]\n );\n return result.rows as PromptHistory[];\n }\n\n async getTemplatesByCategory(category: string): Promise {\n const result = await this.db.execute(\n `SELECT * FROM prompt_templates \n WHERE category = ? AND is_public = 1 \n ORDER BY use_count DESC, rating DESC`,\n [category]\n );\n \n return (result.rows as any[]).map(row => ({\n ...row,\n variables: row.variables ? JSON.parse(row.variables) : {},\n tags: row.tags ? JSON.parse(row.tags) : []\n }));\n }\n\n async getPopularTemplates(limit: number = 10): Promise {\n const result = await this.db.execute(\n `SELECT * FROM prompt_templates \n WHERE is_public = 1 \n ORDER BY use_count DESC, rating DESC \n LIMIT ?`,\n [limit]\n );\n return result.rows as PromptTemplate[];\n }\n\n async incrementTemplateUsage(templateId: number): Promise {\n await this.db.execute(\n `UPDATE prompt_templates \n SET use_count = use_count + 1 \n WHERE id = ?`,\n [templateId]\n );\n }\n\n async createTemplate(userId: number, template: any): Promise {\n const {\n category,\n title,\n description,\n templateText,\n variables = {},\n tags = [],\n isPublic = false\n } = template;\n\n const result = await this.db.execute(\n `INSERT INTO prompt_templates \n (category, title, description, template, variables, tags, is_public, created_by) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n category,\n title,\n description,\n templateText,\n JSON.stringify(variables),\n JSON.stringify(tags),\n isPublic ? 1 : 0,\n userId\n ]\n );\n\n return { id: result.lastInsertRowid, ...template };\n }\n\n async createSession(userId: number, sessionId: string | null = null): Promise {\n const id = sessionId || this.generateSessionId();\n const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // 7 days\n \n await this.db.execute(\n `INSERT INTO sessions (id, user_id, expires_at) VALUES (?, ?, ?)`,\n [id, userId, expiresAt.toISOString()]\n );\n \n return { id, userId, expires_at: expiresAt.toISOString(), created_at: new Date().toISOString() };\n }\n\n async getSession(sessionId: string): Promise {\n const result = await this.db.execute(\n `SELECT * FROM sessions \n WHERE id = ? AND expires_at > datetime('now')`,\n [sessionId]\n );\n return result.rows[0] as Session;\n }\n\n async deleteSession(sessionId: string): Promise {\n await this.db.execute(\n `DELETE FROM sessions WHERE id = ?`,\n [sessionId]\n );\n }\n\n async cleanupExpiredSessions(): Promise {\n await this.db.execute(\n `DELETE FROM sessions WHERE expires_at < datetime('now')`\n );\n }\n\n async logEvent(userId: number, eventType: string, eventData: any = {}): Promise {\n await this.db.execute(\n `INSERT INTO analytics (user_id, event_type, event_data) \n VALUES (?, ?, ?)`,\n [userId, eventType, JSON.stringify(eventData)]\n );\n }\n\n async getUserStats(userId: number): Promise {\n const stats = await this.db.execute(\n `SELECT \n COUNT(*) as total_prompts,\n COUNT(CASE WHEN is_favorite = 1 THEN 1 END) as favorites,\n AVG(LENGTH(improved_prompt) - LENGTH(original_prompt)) as avg_improvement,\n MAX(created_at) as last_activity\n FROM prompt_history \n WHERE user_id = ? AND is_deleted = 0`,\n [userId]\n );\n\n const popularFormats = await this.db.execute(\n `SELECT output_format, COUNT(*) as count \n FROM prompt_history \n WHERE user_id = ? \n GROUP BY output_format \n ORDER BY count DESC \n LIMIT 3`,\n [userId]\n );\n\n return {\n ...stats.rows[0],\n popularFormats: popularFormats.rows\n };\n }\n\n generateSessionId(): string {\n return Math.random().toString(36).substring(2) + Date.now().toString(36);\n }\n\n async seedTemplates(): Promise {\n const countResult = await this.db.execute(`SELECT COUNT(*) as count FROM prompt_templates`);\n const count = (countResult.rows[0] as { count: number }).count;\n \n if (count === 0) {\n const templates = [\n {\n category: 'development',\n title: 'RESTful API Endpoint',\n description: 'Design a REST API endpoint with proper HTTP methods',\n template: 'Create a RESTful API endpoint for {resource} that handles {operations}. Include proper HTTP status codes, error handling, and data validation.',\n variables: { resource: 'users', operations: 'CRUD operations' },\n tags: ['api', 'rest', 'backend']\n },\n {\n category: 'development',\n title: 'React Component',\n description: 'Modern React component with hooks',\n template: 'Build a React functional component called {componentName} that {functionality}. Use hooks for state management and include PropTypes.',\n variables: { componentName: 'DataTable', functionality: 'displays paginated data' },\n tags: ['react', 'frontend', 'component']\n },\n {\n category: 'ai_ml',\n title: 'ML Model Training',\n description: 'Train a machine learning model',\n template: 'Train a {modelType} model to predict {target} using {features}. Include data preprocessing, model evaluation, and hyperparameter tuning.',\n variables: { modelType: 'Random Forest', target: 'customer churn', features: 'customer behavior data' },\n tags: ['ml', 'ai', 'model']\n },\n {\n category: 'business',\n title: 'Business Strategy',\n description: 'Develop a business strategy',\n template: 'Create a {timeframe} business strategy for {company} to {objective}. Include market analysis, competitive positioning, and KPIs.',\n variables: { timeframe: '3-year', company: 'our startup', objective: 'expand internationally' },\n tags: ['business', 'strategy', 'planning']\n }\n ];\n\n for (const template of templates) {\n await this.createTemplate(1, { // Assuming user 1 is an admin/system user\n ...template,\n templateText: template.template,\n isPublic: true\n });\n }\n }\n }\n\n async createApiKey(userId: number): Promise {\n const key = `pk_${Math.random().toString(36).substring(2)}`;\n const id = `key_${Math.random().toString(36).substring(2)}`;\n await this.db.execute(\n `INSERT INTO api_keys (id, user_id, key) VALUES (?, ?, ?)`,\n [id, userId, key]\n );\n return { id, user_id: userId, key, created_at: new Date().toISOString() };\n }\n\n async getUserForApiKey(key: string): Promise {\n const result = await this.db.execute(\n `SELECT u.* FROM users u JOIN api_keys ak ON u.id = ak.user_id WHERE ak.key = ?`,\n [key]\n );\n return result.rows.length > 0 ? result.rows[0] as User : null;\n }\n}\n\nconst database = new Database();\nexport default database;","deleted","label",[170,152],"backend","permalink","https://www.val.town/x/toowired/Prompt_Improver/code/backend/database.ts","extension","actionData","errors"]