[{"_1":2,"_176":-5,"_177":-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,"_151":152,"_173":174,"_175":-5},"handle","Hesbon","name","SalesManagement","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":120,"_14":121,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":131,"_134":135,"_137":138,"_150":20},"id","ed3f6470-3fb3-11f0-b224-76b3cceeab13","createdAt",["D",1748870246051],"deletedAt","legacyEnvVars","privacy","unlisted","description","imageUrl","2efb4380-3fb3-11f0-ad5b-814c56c8abb0","pinnedFileId","bb444b10-3fb4-11f0-bf37-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",1,"1fa2f238-16bc-42cd-98c9-67fea07c2b02","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHhCYXZDY2NoZG5ISTVMTHpCM1FCUXcxYkIifQ","org",{"_94":95,"_136":133,"_12":112},"avatar","branch",{"_139":140,"_96":44,"_102":141,"_142":103,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":-5,"_148":97,"_149":95},"version",17,"ed3fcd98-3fb3-11f0-b224-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":153,"_96":154,"_155":156,"_157":158,"_159":141,"_160":161,"_162":116,"_104":163,"_139":32,"_164":165,"_166":167,"_168":20,"_169":-5,"_98":170},"8253ece8-3fb4-11f0-bee2-76b3cceeab13","queries.ts","fileId","8253ed9c-3fb4-11f0-bee2-76b3cceeab13","parentId","3ff2bef5-5f67-4429-bfdf-7c017849a78d","branchId","branchVersion",4,"latest",["D",1748870496158],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { CONTACTS_TABLE, EVENTS_TABLE, GOALS_TABLE } from \"./migrations.ts\";\nimport type { Contact, NetworkingEvent, NetworkingGoal, ContactFormData, EventFormData, GoalFormData } from \"../../shared/types.ts\";\n\n// Contact queries\nexport async function getAllContacts(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${CONTACTS_TABLE} \n ORDER BY updated_at DESC\n `);\n return result.rows as Contact[];\n}\n\nexport async function getContactById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${CONTACTS_TABLE} WHERE id = ?\n `, [id]);\n return result.rows[0] as Contact || null;\n}\n\nexport async function createContact(data: ContactFormData): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(`\n INSERT INTO ${CONTACTS_TABLE} (\n name, email, phone, company, position, industry, met_at, met_date,\n connection_strength, notes, follow_up_date, linkedin_url, twitter_handle,\n tags, created_at, updated_at\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n data.name, data.email, data.phone, data.company, data.position,\n data.industry, data.met_at, data.met_date, data.connection_strength,\n data.notes, data.follow_up_date, data.linkedin_url, data.twitter_handle,\n data.tags, now, now\n ]);\n\n return await getContactById(result.lastInsertRowId!);\n}\n\nexport async function updateContact(id: number, data: Partial): Promise {\n const now = new Date().toISOString();\n const fields = Object.keys(data).map(key => `${key} = ?`).join(', ');\n const values = [...Object.values(data), now, id];\n\n await sqlite.execute(`\n UPDATE ${CONTACTS_TABLE} \n SET ${fields}, updated_at = ?\n WHERE id = ?\n `, values);\n\n return await getContactById(id);\n}\n\nexport async function deleteContact(id: number): Promise {\n await sqlite.execute(`DELETE FROM ${CONTACTS_TABLE} WHERE id = ?`, [id]);\n}\n\nexport async function getContactsNeedingFollowUp(): Promise {\n const today = new Date().toISOString().split('T')[0];\n const result = await sqlite.execute(`\n SELECT * FROM ${CONTACTS_TABLE} \n WHERE follow_up_date <= ? AND follow_up_completed = FALSE\n ORDER BY follow_up_date ASC\n `, [today]);\n return result.rows as Contact[];\n}\n\nexport async function markFollowUpCompleted(id: number): Promise {\n const now = new Date().toISOString();\n await sqlite.execute(`\n UPDATE ${CONTACTS_TABLE} \n SET follow_up_completed = TRUE, updated_at = ?\n WHERE id = ?\n `, [now, id]);\n}\n\n// Event queries\nexport async function getAllEvents(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${EVENTS_TABLE} \n ORDER BY date DESC\n `);\n return result.rows as NetworkingEvent[];\n}\n\nexport async function getEventById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${EVENTS_TABLE} WHERE id = ?\n `, [id]);\n return result.rows[0] as NetworkingEvent || null;\n}\n\nexport async function createEvent(data: EventFormData): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(`\n INSERT INTO ${EVENTS_TABLE} (name, date, location, type, description, created_at)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [data.name, data.date, data.location, data.type, data.description, now]);\n\n return await getEventById(result.lastInsertRowId!);\n}\n\nexport async function updateEventContactCount(eventName: string): Promise {\n await sqlite.execute(`\n UPDATE ${EVENTS_TABLE} \n SET contacts_met = (\n SELECT COUNT(*) FROM ${CONTACTS_TABLE} \n WHERE met_at = ?\n )\n WHERE name = ?\n `, [eventName, eventName]);\n}\n\n// Goal queries\nexport async function getAllGoals(): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${GOALS_TABLE} \n ORDER BY created_at DESC\n `);\n return result.rows as NetworkingGoal[];\n}\n\nexport async function createGoal(data: GoalFormData): Promise {\n const now = new Date().toISOString();\n const result = await sqlite.execute(`\n INSERT INTO ${GOALS_TABLE} (title, description, target_number, deadline, goal_type, created_at)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [data.title, data.description, data.target_number, data.deadline, data.goal_type, now]);\n\n return await getGoalById(result.lastInsertRowId!);\n}\n\nexport async function getGoalById(id: number): Promise {\n const result = await sqlite.execute(`\n SELECT * FROM ${GOALS_TABLE} WHERE id = ?\n `, [id]);\n return result.rows[0] as NetworkingGoal || null;\n}\n\nexport async function updateGoalProgress(): Promise {\n // Update contact-based goals\n await sqlite.execute(`\n UPDATE ${GOALS_TABLE} \n SET current_progress = (\n SELECT COUNT(*) FROM ${CONTACTS_TABLE}\n WHERE created_at >= ${GOALS_TABLE}.created_at\n )\n WHERE goal_type = 'contacts'\n `);\n\n // Update event-based goals\n await sqlite.execute(`\n UPDATE ${GOALS_TABLE} \n SET current_progress = (\n SELECT COUNT(*) FROM ${EVENTS_TABLE}\n WHERE created_at >= ${GOALS_TABLE}.created_at\n )\n WHERE goal_type = 'events'\n `);\n\n // Update follow-up goals\n await sqlite.execute(`\n UPDATE ${GOALS_TABLE} \n SET current_progress = (\n SELECT COUNT(*) FROM ${CONTACTS_TABLE}\n WHERE follow_up_completed = TRUE \n AND updated_at >= ${GOALS_TABLE}.created_at\n )\n WHERE goal_type = 'follow_ups'\n `);\n\n // Mark completed goals\n await sqlite.execute(`\n UPDATE ${GOALS_TABLE} \n SET completed = TRUE\n WHERE current_progress >= target_number AND completed = FALSE\n `);\n}\n\n// Dashboard stats\nexport async function getDashboardStats() {\n const totalContacts = await sqlite.execute(`SELECT COUNT(*) as count FROM ${CONTACTS_TABLE}`);\n const totalEvents = await sqlite.execute(`SELECT COUNT(*) as count FROM ${EVENTS_TABLE}`);\n const pendingFollowUps = await sqlite.execute(`\n SELECT COUNT(*) as count FROM ${CONTACTS_TABLE} \n WHERE follow_up_date <= date('now') AND follow_up_completed = FALSE\n `);\n const activeGoals = await sqlite.execute(`\n SELECT COUNT(*) as count FROM ${GOALS_TABLE} \n WHERE completed = FALSE\n `);\n\n return {\n totalContacts: totalContacts.rows[0].count,\n totalEvents: totalEvents.rows[0].count,\n pendingFollowUps: pendingFollowUps.rows[0].count,\n activeGoals: activeGoals.rows[0].count\n };\n}","deleted","label",[171,172,154],"backend","database","permalink","https://www.val.town/x/Hesbon/SalesManagement/code/backend/database/queries.ts","extension","actionData","errors"]