[{"_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","cissymoore","name","createanappthatallowsuserstopostdailyhabits","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":111,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":117,"_119":-5,"_120":32,"_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","cd786446-3335-11f0-bcb9-9e149126039e","createdAt",["D",1747496662197],"deletedAt","legacyEnvVars","privacy","public","description","A social app for posting daily habits","imageUrl","cba43930-32a7-11f0-994d-f304847156a7","pinnedFileId","38e96ec4-3501-11f0-90c3-9e149126039e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","ae49ea9f-17b3-481e-a41d-ac51192d49af","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4Q0lWakxRNk44WEJxdHpXQnJHT0wwT21qeCJ9","org",{"_94":95,"_136":133,"_12":113},"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",24,"cd789290-3335-11f0-bcb9-9e149126039e","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":153,"_96":154,"_155":156,"_157":158,"_159":141,"_160":161,"_162":117,"_104":163,"_139":32,"_164":165,"_166":167,"_168":20,"_169":-5,"_98":170},"aa2052a4-3337-11f0-911e-9e149126039e","queries.ts","fileId","aa205362-3337-11f0-911e-9e149126039e","parentId","77f5b4eb-a28c-4a95-9de7-6a9652d901e9","branchId","branchVersion",3,"latest",["D",1747497461886],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport {\n USERS_TABLE,\n OPPORTUNITIES_TABLE,\n CHAT_ROOMS_TABLE,\n MESSAGES_TABLE\n} from \"./migrations.ts\";\n\n// Types\nexport interface User {\n id: number;\n username: string;\n display_name: string;\n created_at: number;\n}\n\nexport interface Opportunity {\n id: number;\n title: string;\n description: string;\n category: string;\n estimated_income: string;\n time_commitment: string;\n user_id: number;\n created_at: number;\n user?: User;\n}\n\nexport interface ChatRoom {\n id: number;\n name: string;\n description: string;\n category: string;\n created_at: number;\n message_count?: number;\n}\n\nexport interface Message {\n id: number;\n chat_room_id: number;\n user_id: number;\n content: string;\n created_at: number;\n user?: User;\n}\n\n// User queries\nexport async function createUser(username: string, display_name: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${USERS_TABLE} (username, display_name, created_at) \n VALUES (?, ?, unixepoch()) \n RETURNING *`,\n [username, display_name]\n );\n return result[0] as User;\n}\n\nexport async function getUserByUsername(username: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${USERS_TABLE} WHERE username = ?`,\n [username]\n );\n return result.length > 0 ? (result[0] as User) : null;\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${USERS_TABLE} WHERE id = ?`,\n [id]\n );\n return result.length > 0 ? (result[0] as User) : null;\n}\n\n// Opportunity queries\nexport async function createOpportunity(\n title: string,\n description: string,\n category: string,\n estimated_income: string,\n time_commitment: string,\n user_id: number\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${OPPORTUNITIES_TABLE} \n (title, description, category, estimated_income, time_commitment, user_id, created_at) \n VALUES (?, ?, ?, ?, ?, ?, unixepoch()) \n RETURNING *`,\n [title, description, category, estimated_income, time_commitment, user_id]\n );\n return result[0] as Opportunity;\n}\n\nexport async function getOpportunities(limit = 20, offset = 0): Promise {\n const result = await sqlite.execute(\n `SELECT o.*, u.username, u.display_name\n FROM ${OPPORTUNITIES_TABLE} o\n JOIN ${USERS_TABLE} u ON o.user_id = u.id\n ORDER BY o.created_at DESC\n LIMIT ? OFFSET ?`,\n [limit, offset]\n );\n \n return result.map(row => ({\n id: row.id,\n title: row.title,\n description: row.description,\n category: row.category,\n estimated_income: row.estimated_income,\n time_commitment: row.time_commitment,\n user_id: row.user_id,\n created_at: row.created_at,\n user: {\n id: row.user_id,\n username: row.username,\n display_name: row.display_name,\n created_at: row.created_at\n }\n }));\n}\n\nexport async function getOpportunitiesByCategory(category: string, limit = 20, offset = 0): Promise {\n const result = await sqlite.execute(\n `SELECT o.*, u.username, u.display_name\n FROM ${OPPORTUNITIES_TABLE} o\n JOIN ${USERS_TABLE} u ON o.user_id = u.id\n WHERE o.category = ?\n ORDER BY o.created_at DESC\n LIMIT ? OFFSET ?`,\n [category, limit, offset]\n );\n \n return result.map(row => ({\n id: row.id,\n title: row.title,\n description: row.description,\n category: row.category,\n estimated_income: row.estimated_income,\n time_commitment: row.time_commitment,\n user_id: row.user_id,\n created_at: row.created_at,\n user: {\n id: row.user_id,\n username: row.username,\n display_name: row.display_name,\n created_at: row.created_at\n }\n }));\n}\n\n// Chat room queries\nexport async function getChatRooms(): Promise {\n const result = await sqlite.execute(\n `SELECT r.*, \n (SELECT COUNT(*) FROM ${MESSAGES_TABLE} m WHERE m.chat_room_id = r.id) as message_count\n FROM ${CHAT_ROOMS_TABLE} r\n ORDER BY r.name ASC`\n );\n \n return result.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n category: row.category,\n created_at: row.created_at,\n message_count: row.message_count\n }));\n}\n\nexport async function getChatRoomById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT r.*, \n (SELECT COUNT(*) FROM ${MESSAGES_TABLE} m WHERE m.chat_room_id = r.id) as message_count\n FROM ${CHAT_ROOMS_TABLE} r\n WHERE r.id = ?`,\n [id]\n );\n \n return result.length > 0 ? {\n id: result[0].id,\n name: result[0].name,\n description: result[0].description,\n category: result[0].category,\n created_at: result[0].created_at,\n message_count: result[0].message_count\n } : null;\n}\n\n// Message queries\nexport async function createMessage(chat_room_id: number, user_id: number, content: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${MESSAGES_TABLE} (chat_room_id, user_id, content, created_at) \n VALUES (?, ?, ?, unixepoch()) \n RETURNING *`,\n [chat_room_id, user_id, content]\n );\n return result[0] as Message;\n}\n\nexport async function getMessagesByChatRoom(chat_room_id: number, limit = 50, before_id?: number): Promise {\n let query = `\n SELECT m.*, u.username, u.display_name\n FROM ${MESSAGES_TABLE} m\n JOIN ${USERS_TABLE} u ON m.user_id = u.id\n WHERE m.chat_room_id = ?\n `;\n \n const params: any[] = [chat_room_id];\n \n if (before_id) {\n query += ` AND m.id < ?`;\n params.push(before_id);\n }\n \n query += ` ORDER BY m.created_at DESC LIMIT ?`;\n params.push(limit);\n \n const result = await sqlite.execute(query, params);\n \n return result.map(row => ({\n id: row.id,\n chat_room_id: row.chat_room_id,\n user_id: row.user_id,\n content: row.content,\n created_at: row.created_at,\n user: {\n id: row.user_id,\n username: row.username,\n display_name: row.display_name,\n created_at: row.created_at\n }\n }));\n}","deleted","label",[171,172,154],"backend","database","permalink","https://www.val.town/x/cissymoore/createanappthatallowsuserstopostdailyhabits/code/backend/database/queries.ts","extension","actionData","errors"]