[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","8abab1f3bacc65052d6313ca736f41d897b49791","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_150":151,"_172":173,"_174":-5},"handle","ampomah","name","untitled-2883","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":116,"_118":-5,"_119":32,"_14":120,"_121":122,"_123":122,"_124":125,"_126":125,"_127":125,"_128":122,"_129":122,"_23":130,"_133":134,"_136":137,"_149":20},"id","3c935580-3fb0-11f0-a22a-76b3cceeab13","createdAt",["D",1748868661154],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","bbf70480-3faf-11f0-ad5b-814c56c8abb0","pinnedFileId","915a2390-3fb1-11f0-9cf7-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","f2db78ea-5e82-4343-ad6d-6a67565cc23a","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4eDhacjl5Q1BKdEdYYmNqdFlIQm94QTBpNCJ9","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":97,"_148":95},"version",18,"3c939950-3fb0-11f0-a22a-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":152,"_96":153,"_154":155,"_156":157,"_158":140,"_159":139,"_160":116,"_104":161,"_138":162,"_163":164,"_165":166,"_167":20,"_168":-5,"_98":169},"40450c1c-3fb2-11f0-9cf7-76b3cceeab13","queries.ts","fileId","61bbe57e-3fb1-11f0-a247-76b3cceeab13","parentId","937aaf12-0a9b-4c20-8445-0004cfdc73b6","branchId","branchVersion","latest",["D",1748869526333],1,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type {\n WorkflowTemplate,\n Client,\n WorkflowExecution,\n FeedbackResponse,\n DashboardStats,\n CreateWorkflowRequest,\n CreateClientRequest,\n UpdateClientRequest\n} from \"../../shared/types.ts\";\n\n// Utility function to generate UUIDs\nfunction generateId(): string {\n return crypto.randomUUID();\n}\n\n// Workflow Template Queries\nexport async function createWorkflowTemplate(data: CreateWorkflowRequest): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n \n // Add IDs to steps\n const stepsWithIds = data.steps.map(step => ({\n ...step,\n id: generateId()\n }));\n\n await sqlite.execute(\n `INSERT INTO workflow_templates (id, name, description, service_type, steps, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n [id, data.name, data.description, data.serviceType, JSON.stringify(stepsWithIds), now, now]\n );\n\n return getWorkflowTemplate(id);\n}\n\nexport async function getWorkflowTemplate(id: string): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM workflow_templates WHERE id = ?\",\n [id]\n );\n\n if (result.length === 0) {\n throw new Error(\"Workflow template not found\");\n }\n\n const row = result[0];\n return {\n id: row.id,\n name: row.name,\n description: row.description,\n serviceType: row.service_type,\n steps: JSON.parse(row.steps),\n isActive: Boolean(row.is_active),\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n}\n\nexport async function getAllWorkflowTemplates(): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM workflow_templates ORDER BY created_at DESC\"\n );\n\n if (!Array.isArray(result)) {\n return [];\n }\n\n return result.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n serviceType: row.service_type,\n steps: JSON.parse(row.steps),\n isActive: Boolean(row.is_active),\n createdAt: row.created_at,\n updatedAt: row.updated_at\n }));\n}\n\nexport async function updateWorkflowTemplate(id: string, data: Partial): Promise {\n const now = new Date().toISOString();\n const updates: string[] = [];\n const values: any[] = [];\n\n if (data.name) {\n updates.push(\"name = ?\");\n values.push(data.name);\n }\n if (data.description) {\n updates.push(\"description = ?\");\n values.push(data.description);\n }\n if (data.serviceType) {\n updates.push(\"service_type = ?\");\n values.push(data.serviceType);\n }\n if (data.steps) {\n const stepsWithIds = data.steps.map(step => ({\n ...step,\n id: step.id || generateId()\n }));\n updates.push(\"steps = ?\");\n values.push(JSON.stringify(stepsWithIds));\n }\n\n updates.push(\"updated_at = ?\");\n values.push(now);\n values.push(id);\n\n await sqlite.execute(\n `UPDATE workflow_templates SET ${updates.join(\", \")} WHERE id = ?`,\n values\n );\n\n return getWorkflowTemplate(id);\n}\n\nexport async function deleteWorkflowTemplate(id: string): Promise {\n await sqlite.execute(\"DELETE FROM workflow_templates WHERE id = ?\", [id]);\n}\n\n// Client Queries\nexport async function createClient(data: CreateClientRequest): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO clients (id, name, email, phone, company, service_delivered_at, workflow_id, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [id, data.name, data.email, data.phone || null, data.company || null, data.serviceDeliveredAt, data.workflowId, now, now]\n );\n\n return getClient(id);\n}\n\nexport async function getClient(id: string): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM clients WHERE id = ?\",\n [id]\n );\n\n if (result.length === 0) {\n throw new Error(\"Client not found\");\n }\n\n const row = result[0];\n return {\n id: row.id,\n name: row.name,\n email: row.email,\n phone: row.phone,\n company: row.company,\n serviceDeliveredAt: row.service_delivered_at,\n workflowId: row.workflow_id,\n currentStepIndex: row.current_step_index,\n status: row.status,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n}\n\nexport async function getAllClients(): Promise {\n const result = await sqlite.execute(\n \"SELECT * FROM clients ORDER BY created_at DESC\"\n );\n\n return result.map(row => ({\n id: row.id,\n name: row.name,\n email: row.email,\n phone: row.phone,\n company: row.company,\n serviceDeliveredAt: row.service_delivered_at,\n workflowId: row.workflow_id,\n currentStepIndex: row.current_step_index,\n status: row.status,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n }));\n}\n\nexport async function updateClient(id: string, data: UpdateClientRequest): Promise {\n const now = new Date().toISOString();\n const updates: string[] = [];\n const values: any[] = [];\n\n if (data.name) {\n updates.push(\"name = ?\");\n values.push(data.name);\n }\n if (data.email) {\n updates.push(\"email = ?\");\n values.push(data.email);\n }\n if (data.phone !== undefined) {\n updates.push(\"phone = ?\");\n values.push(data.phone);\n }\n if (data.company !== undefined) {\n updates.push(\"company = ?\");\n values.push(data.company);\n }\n if (data.serviceDeliveredAt) {\n updates.push(\"service_delivered_at = ?\");\n values.push(data.serviceDeliveredAt);\n }\n if (data.workflowId) {\n updates.push(\"workflow_id = ?\");\n values.push(data.workflowId);\n }\n if (data.status) {\n updates.push(\"status = ?\");\n values.push(data.status);\n }\n if (data.currentStepIndex !== undefined) {\n updates.push(\"current_step_index = ?\");\n values.push(data.currentStepIndex);\n }\n\n updates.push(\"updated_at = ?\");\n values.push(now);\n values.push(id);\n\n await sqlite.execute(\n `UPDATE clients SET ${updates.join(\", \")} WHERE id = ?`,\n values\n );\n\n return getClient(id);\n}\n\nexport async function deleteClient(id: string): Promise {\n await sqlite.execute(\"DELETE FROM clients WHERE id = ?\", [id]);\n}\n\n// Workflow Execution Queries\nexport async function createWorkflowExecution(\n clientId: string,\n workflowId: string,\n stepId: string,\n scheduledAt: string\n): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO workflow_executions (id, client_id, workflow_id, step_id, scheduled_at, created_at)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [id, clientId, workflowId, stepId, scheduledAt, now]\n );\n\n const result = await sqlite.execute(\n \"SELECT * FROM workflow_executions WHERE id = ?\",\n [id]\n );\n\n const row = result[0];\n return {\n id: row.id,\n clientId: row.client_id,\n workflowId: row.workflow_id,\n stepId: row.step_id,\n scheduledAt: row.scheduled_at,\n executedAt: row.executed_at,\n status: row.status,\n errorMessage: row.error_message\n };\n}\n\nexport async function getPendingExecutions(): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(\n \"SELECT * FROM workflow_executions WHERE status = 'pending' AND scheduled_at <= ? ORDER BY scheduled_at\",\n [now]\n );\n\n return result.map(row => ({\n id: row.id,\n clientId: row.client_id,\n workflowId: row.workflow_id,\n stepId: row.step_id,\n scheduledAt: row.scheduled_at,\n executedAt: row.executed_at,\n status: row.status,\n errorMessage: row.error_message\n }));\n}\n\nexport async function updateExecutionStatus(\n id: string,\n status: WorkflowExecution['status'],\n errorMessage?: string\n): Promise {\n const now = new Date().toISOString();\n \n if (status === 'sent') {\n await sqlite.execute(\n \"UPDATE workflow_executions SET status = ?, executed_at = ? WHERE id = ?\",\n [status, now, id]\n );\n } else {\n await sqlite.execute(\n \"UPDATE workflow_executions SET status = ?, error_message = ? WHERE id = ?\",\n [status, errorMessage || null, id]\n );\n }\n}\n\n// Feedback Response Queries\nexport async function createFeedbackResponse(\n clientId: string,\n workflowExecutionId: string,\n responses: Record,\n overallRating?: number\n): Promise {\n const id = generateId();\n const now = new Date().toISOString();\n\n await sqlite.execute(\n `INSERT INTO feedback_responses (id, client_id, workflow_execution_id, responses, overall_rating, submitted_at)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [id, clientId, workflowExecutionId, JSON.stringify(responses), overallRating || null, now]\n );\n\n const result = await sqlite.execute(\n \"SELECT * FROM feedback_responses WHERE id = ?\",\n [id]\n );\n\n const row = result[0];\n return {\n id: row.id,\n clientId: row.client_id,\n workflowExecutionId: row.workflow_execution_id,\n responses: JSON.parse(row.responses),\n overallRating: row.overall_rating,\n submittedAt: row.submitted_at\n };\n}\n\n// Dashboard Statistics\nexport async function getDashboardStats(): Promise {\n const [\n totalClientsResult,\n activeWorkflowsResult,\n completedFollowupsResult,\n pendingActionsResult,\n avgRatingResult,\n responseRateResult\n ] = await Promise.all([\n sqlite.execute(\"SELECT COUNT(*) as count FROM clients\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM clients WHERE status = 'active'\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM clients WHERE status = 'completed'\"),\n sqlite.execute(\"SELECT COUNT(*) as count FROM workflow_executions WHERE status = 'pending'\"),\n sqlite.execute(\"SELECT AVG(overall_rating) as avg_rating FROM feedback_responses WHERE overall_rating IS NOT NULL\"),\n sqlite.execute(`\n SELECT \n (SELECT COUNT(*) FROM feedback_responses) * 100.0 / \n NULLIF((SELECT COUNT(*) FROM workflow_executions WHERE status = 'sent'), 0) as response_rate\n `)\n ]);\n\n return {\n totalClients: totalClientsResult[0]?.count || 0,\n activeWorkflows: activeWorkflowsResult[0]?.count || 0,\n completedFollowups: completedFollowupsResult[0]?.count || 0,\n pendingActions: pendingActionsResult[0]?.count || 0,\n averageRating: avgRatingResult[0]?.avg_rating || 0,\n responseRate: responseRateResult[0]?.response_rate || 0\n };\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/ampomah/untitled-2883/code/backend/database/queries.ts","extension","actionData","errors"]