[{"_1":2,"_172":-5,"_173":-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","e3ca0a77ab07ff2ce3097208ffd6aa0e378d3c47","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,"_169":170,"_171":-5},"handle","Sandra_Ozumba","name","Freelancing","path","backend/database/queries.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","2bc6190e-3f97-11f0-93b1-76b3cceeab13","createdAt",["D",1748857895548],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","b3bfda50-3f94-11f0-b6e9-ab0b2f88b858","pinnedFileId","pinPreview","screenshotVersion","likeCount","494564c9-ef48-479b-8c04-cbb954570bd4","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4d2taaTVINFpVOWRZQjE5NUIyd3V5ZTdyRCJ9","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",22,"2bc67188-3f97-11f0-93b1-76b3cceeab13","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":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"5a5adfde-3f97-11f0-a0fd-76b3cceeab13","queries.ts","fileId","5a5ae0b0-3f97-11f0-a0fd-76b3cceeab13","parentId","fe57faf7-4dbe-4f28-b1d8-01b9ceaad462","branchId","branchVersion",4,"latest",true,["D",1748857973691],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Job, ChatMessage, JobApplication } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(userData: {\n email: string;\n password_hash: string;\n name: string;\n user_type: 'freelancer' | 'client';\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO users (email, password_hash, name, user_type) \n VALUES (?, ?, ?, ?) RETURNING *`,\n [userData.email, userData.password_hash, userData.name, userData.user_type]\n );\n return result[0] as User;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users WHERE email = ?`,\n [email]\n );\n return result[0] as User || null;\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users WHERE id = ?`,\n [id]\n );\n return result[0] as User || null;\n}\n\nexport async function updateUser(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id');\n const values = fields.map(key => updates[key as keyof User]);\n \n if (fields.length === 0) return getUserById(id);\n \n const setClause = fields.map(field => `${field} = ?`).join(', ');\n \n await sqlite.execute(\n `UPDATE users SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [...values, id]\n );\n \n return getUserById(id);\n}\n\nexport async function getFreelancers(limit = 20): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM users WHERE user_type = 'freelancer' ORDER BY created_at DESC LIMIT ?`,\n [limit]\n );\n return result as User[];\n}\n\n// Job queries\nexport async function createJob(jobData: {\n client_id: number;\n title: string;\n description: string;\n budget_min?: number;\n budget_max?: number;\n budget_type: 'fixed' | 'hourly';\n skills_required: string;\n deadline?: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO jobs (client_id, title, description, budget_min, budget_max, budget_type, skills_required, deadline) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *`,\n [\n jobData.client_id,\n jobData.title,\n jobData.description,\n jobData.budget_min || null,\n jobData.budget_max || null,\n jobData.budget_type,\n jobData.skills_required,\n jobData.deadline || null\n ]\n );\n return result[0] as Job;\n}\n\nexport async function getJobs(filters: {\n status?: string;\n client_id?: number;\n limit?: number;\n} = {}): Promise {\n let query = `\n SELECT j.*, u.name as client_name \n FROM jobs j \n JOIN users u ON j.client_id = u.id\n `;\n const params: any[] = [];\n const conditions: string[] = [];\n\n if (filters.status) {\n conditions.push('j.status = ?');\n params.push(filters.status);\n }\n\n if (filters.client_id) {\n conditions.push('j.client_id = ?');\n params.push(filters.client_id);\n }\n\n if (conditions.length > 0) {\n query += ' WHERE ' + conditions.join(' AND ');\n }\n\n query += ' ORDER BY j.created_at DESC';\n\n if (filters.limit) {\n query += ' LIMIT ?';\n params.push(filters.limit);\n }\n\n const result = await sqlite.execute(query, params);\n return result as Job[];\n}\n\nexport async function getJobById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT j.*, u.name as client_name \n FROM jobs j \n JOIN users u ON j.client_id = u.id \n WHERE j.id = ?`,\n [id]\n );\n return result[0] as Job || null;\n}\n\nexport async function updateJob(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => !['id', 'client_name'].includes(key));\n const values = fields.map(key => updates[key as keyof Job]);\n \n if (fields.length === 0) return getJobById(id);\n \n const setClause = fields.map(field => `${field} = ?`).join(', ');\n \n await sqlite.execute(\n `UPDATE jobs SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [...values, id]\n );\n \n return getJobById(id);\n}\n\n// Chat queries\nexport async function getChatMessages(limit = 50): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM chat_messages ORDER BY created_at DESC LIMIT ?`,\n [limit]\n );\n return (result as ChatMessage[]).reverse(); // Return in chronological order\n}\n\nexport async function createChatMessage(messageData: {\n user_id: number;\n user_name: string;\n message: string;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO chat_messages (user_id, user_name, message) \n VALUES (?, ?, ?) RETURNING *`,\n [messageData.user_id, messageData.user_name, messageData.message]\n );\n return result[0] as ChatMessage;\n}\n\n// Job application queries\nexport async function createJobApplication(applicationData: {\n job_id: number;\n freelancer_id: number;\n proposal: string;\n proposed_rate?: number;\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO job_applications (job_id, freelancer_id, proposal, proposed_rate) \n VALUES (?, ?, ?, ?) RETURNING *`,\n [\n applicationData.job_id,\n applicationData.freelancer_id,\n applicationData.proposal,\n applicationData.proposed_rate || null\n ]\n );\n return result[0] as JobApplication;\n}\n\nexport async function getJobApplications(jobId: number): Promise {\n const result = await sqlite.execute(\n `SELECT ja.*, u.name as freelancer_name, u.title as freelancer_title\n FROM job_applications ja\n JOIN users u ON ja.freelancer_id = u.id\n WHERE ja.job_id = ?\n ORDER BY ja.created_at DESC`,\n [jobId]\n );\n return result as JobApplication[];\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/Sandra_Ozumba/Freelancing/code/backend/database/queries.ts","extension","actionData","errors"]