[{"_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","4665abaede64d7a40f12c22a2a13ecb98867c1ee","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,"_147":148,"_170":171,"_172":-5},"handle","excel","name","FloodApp","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":110,"_111":-5,"_12":112,"_113":-5,"_114":20,"_115":-5,"_116":32,"_14":117,"_118":119,"_120":119,"_121":122,"_123":122,"_124":122,"_125":119,"_126":119,"_23":127,"_130":131,"_133":134,"_146":20},"id","719af482-3447-11f0-ae6c-9e149126039e","createdAt",["D",1747614190177],"deletedAt","legacyEnvVars","privacy","public","description","A flood app that notify subscriber ahead of flooding","imageUrl","dcf86e80-3442-11f0-b519-ff88f6bc2fe6","pinnedFileId","pinPreview","screenshotVersion","likeCount","d12e9468-0713-4b2e-926d-7ccecb147081","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_128":129},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeEk0TXdTZ1pDYmt1Y0FqSmFkNWwxMUx6UkMifQ","org",{"_93":94,"_132":129,"_12":112},"avatar","branch",{"_135":136,"_95":43,"_101":137,"_138":102,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},"version",16,"719b260a-3447-11f0-ae6c-9e149126039e","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":149,"_95":150,"_151":152,"_153":154,"_155":137,"_156":157,"_158":159,"_103":160,"_135":32,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"f4cd9bc0-3447-11f0-93a7-9e149126039e","queries.ts","fileId","f4cd9c88-3447-11f0-93a7-9e149126039e","parentId","9cf75c7d-cd49-45df-8942-278f9ddd13bf","branchId","branchVersion",3,"latest",true,["D",1747614410288],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { USERS_TABLE, LOCATIONS_TABLE, ALERTS_TABLE } from \"./migrations.ts\";\n\n// User types\nexport interface User {\n id: number;\n email: string;\n name: string;\n created_at: string;\n}\n\nexport interface UserWithPassword extends User {\n password_hash: string;\n}\n\n// Location types\nexport interface Location {\n id: number;\n user_id: number;\n name: string;\n latitude: number;\n longitude: number;\n created_at: string;\n}\n\n// Alert types\nexport interface Alert {\n id: number;\n user_id: number;\n location_id: number;\n alert_date: string;\n message: string;\n is_sent: boolean;\n created_at: string;\n}\n\n// User queries\nexport async function createUser(email: string, passwordHash: string, name: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${USERS_TABLE} (email, password_hash, name) VALUES (?, ?, ?) RETURNING id`,\n [email, passwordHash, name]\n );\n return result.rows[0].id;\n}\n\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${USERS_TABLE} WHERE email = ?`,\n [email]\n );\n return result.rows[0] || null;\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT id, email, name, created_at FROM ${USERS_TABLE} WHERE id = ?`,\n [id]\n );\n return result.rows[0] || null;\n}\n\n// Location queries\nexport async function createLocation(userId: number, name: string, latitude: number, longitude: number): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${LOCATIONS_TABLE} (user_id, name, latitude, longitude) VALUES (?, ?, ?, ?) RETURNING id`,\n [userId, name, latitude, longitude]\n );\n return result.rows[0].id;\n}\n\nexport async function getLocationsByUserId(userId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${LOCATIONS_TABLE} WHERE user_id = ?`,\n [userId]\n );\n return result.rows;\n}\n\nexport async function getLocationById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${LOCATIONS_TABLE} WHERE id = ?`,\n [id]\n );\n return result.rows[0] || null;\n}\n\nexport async function deleteLocation(id: number, userId: number): Promise {\n const result = await sqlite.execute(\n `DELETE FROM ${LOCATIONS_TABLE} WHERE id = ? AND user_id = ?`,\n [id, userId]\n );\n return result.rowsAffected > 0;\n}\n\n// Alert queries\nexport async function createAlert(userId: number, locationId: number, alertDate: string, message: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${ALERTS_TABLE} (user_id, location_id, alert_date, message) VALUES (?, ?, ?, ?) RETURNING id`,\n [userId, locationId, alertDate, message]\n );\n return result.rows[0].id;\n}\n\nexport async function getAlertsByUserId(userId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${ALERTS_TABLE} WHERE user_id = ? ORDER BY alert_date DESC`,\n [userId]\n );\n return result.rows;\n}\n\nexport async function markAlertAsSent(id: number): Promise {\n const result = await sqlite.execute(\n `UPDATE ${ALERTS_TABLE} SET is_sent = 1 WHERE id = ?`,\n [id]\n );\n return result.rowsAffected > 0;\n}\n\nexport async function getPendingAlerts(): Promise<(Alert & { user_email: string, location_name: string })[]> {\n const result = await sqlite.execute(`\n SELECT a.*, u.email as user_email, l.name as location_name\n FROM ${ALERTS_TABLE} a\n JOIN ${USERS_TABLE} u ON a.user_id = u.id\n JOIN ${LOCATIONS_TABLE} l ON a.location_id = l.id\n WHERE a.is_sent = 0\n `);\n return result.rows;\n}","deleted","label",[168,169,150],"backend","database","permalink","https://www.val.town/x/excel/FloodApp/code/backend/database/queries.ts","extension","actionData","errors"]