[{"_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","64602b05c9a02becc3d4aac52b7346fd8c14b4dc","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,"_153":154,"_172":173,"_174":-5},"handle","dcm31","name","roommate-activities","path","db.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":111,"_112":-5,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":-5,"_118":32,"_14":119,"_120":-5,"_121":-5,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":131,"_134":135,"_139":140,"_152":20},"id","a42b5964-ebcb-11f0-8e01-42dde27851f2","createdAt",["D",1767792031366],"deletedAt","legacyEnvVars","privacy","public","description","Plan activities with your roommates","imageUrl","9319c175-2593-4f88-ba21-04f2ef7b1c28","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","d663a026-6449-42f6-9156-1d4a2d64d38c","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_132":133},"profileImageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/260ec866-bb5d-4641-4bfe-526cca896a00/square512","org",{"_94":95,"_136":133,"_12":113,"_137":138},"avatar","tier","pro","branch",{"_141":142,"_96":44,"_102":143,"_144":103,"_145":-5,"_146":-5,"_147":-5,"_148":-5,"_149":-5,"_150":97,"_151":95},"version",4,"a42bc1e2-ebcb-11f0-8e01-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":155,"_96":99,"_156":157,"_158":-5,"_159":143,"_160":161,"_162":163,"_104":164,"_141":32,"_165":166,"_167":168,"_169":20,"_170":-5,"_98":171},"b1de5f70-ebcb-11f0-8c78-42dde27851f2","fileId","b1de602e-ebcb-11f0-8c78-42dde27851f2","parentId","branchId","branchVersion",1,"latest",true,["D",1767792054345],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nconst ROOMMATES_TABLE = \"roommate_activities_roommates_1\";\nconst ACTIVITIES_TABLE = \"roommate_activities_activities_1\";\nconst VOTES_TABLE = \"roommate_activities_votes_1\";\n\nawait createTables();\n\nexport interface Roommate {\n id: number;\n name: string;\n emoji: string;\n created_at: number;\n}\n\nexport interface Activity {\n id: number;\n title: string;\n description: string;\n proposed_by: number;\n activity_date: number;\n location: string;\n created_at: number;\n}\n\nexport interface Vote {\n id: number;\n activity_id: number;\n roommate_id: number;\n status: \"in\" | \"maybe\" | \"out\";\n created_at: number;\n}\n\nexport interface ActivityWithVotes extends Activity {\n proposer_name: string;\n proposer_emoji: string;\n votes: Array<{\n roommate_id: number;\n roommate_name: string;\n roommate_emoji: string;\n status: \"in\" | \"maybe\" | \"out\";\n }>;\n}\n\n// Roommate operations\nexport async function addRoommate(name: string, emoji: string): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${ROOMMATES_TABLE} (name, emoji, created_at) VALUES (?, ?, ?)`,\n [name, emoji, Date.now()]\n );\n return Number(result.lastInsertRowid);\n}\n\nexport async function getAllRoommates(): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${ROOMMATES_TABLE} ORDER BY name`);\n return rowsToObjects(result);\n}\n\nexport async function deleteRoommate(id: number): Promise {\n await sqlite.execute(`DELETE FROM ${VOTES_TABLE} WHERE roommate_id = ?`, [id]);\n await sqlite.execute(`DELETE FROM ${ACTIVITIES_TABLE} WHERE proposed_by = ?`, [id]);\n await sqlite.execute(`DELETE FROM ${ROOMMATES_TABLE} WHERE id = ?`, [id]);\n}\n\n// Activity operations\nexport async function addActivity(\n title: string,\n description: string,\n proposedBy: number,\n activityDate: number,\n location: string\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${ACTIVITIES_TABLE} (title, description, proposed_by, activity_date, location, created_at)\n VALUES (?, ?, ?, ?, ?, ?)`,\n [title, description, proposedBy, activityDate, location, Date.now()]\n );\n return Number(result.lastInsertRowid);\n}\n\nexport async function getAllActivities(): Promise {\n const activities = await sqlite.execute(\n `SELECT a.*, r.name as proposer_name, r.emoji as proposer_emoji\n FROM ${ACTIVITIES_TABLE} a\n JOIN ${ROOMMATES_TABLE} r ON a.proposed_by = r.id\n ORDER BY a.activity_date ASC`\n );\n\n const activitiesData = rowsToObjects(activities);\n\n const result: ActivityWithVotes[] = [];\n for (const activity of activitiesData) {\n const votes = await sqlite.execute(\n `SELECT v.*, r.name as roommate_name, r.emoji as roommate_emoji\n FROM ${VOTES_TABLE} v\n JOIN ${ROOMMATES_TABLE} r ON v.roommate_id = r.id\n WHERE v.activity_id = ?`,\n [activity.id]\n );\n result.push({\n ...activity,\n votes: rowsToObjects<{\n roommate_id: number;\n roommate_name: string;\n roommate_emoji: string;\n status: \"in\" | \"maybe\" | \"out\";\n }>(votes),\n });\n }\n return result;\n}\n\nexport async function getActivityById(id: number): Promise {\n const activities = await sqlite.execute(\n `SELECT a.*, r.name as proposer_name, r.emoji as proposer_emoji\n FROM ${ACTIVITIES_TABLE} a\n JOIN ${ROOMMATES_TABLE} r ON a.proposed_by = r.id\n WHERE a.id = ?`,\n [id]\n );\n const activitiesData = rowsToObjects(activities);\n if (activitiesData.length === 0) return null;\n\n const activity = activitiesData[0];\n const votes = await sqlite.execute(\n `SELECT v.*, r.name as roommate_name, r.emoji as roommate_emoji\n FROM ${VOTES_TABLE} v\n JOIN ${ROOMMATES_TABLE} r ON v.roommate_id = r.id\n WHERE v.activity_id = ?`,\n [id]\n );\n\n return {\n ...activity,\n votes: rowsToObjects<{\n roommate_id: number;\n roommate_name: string;\n roommate_emoji: string;\n status: \"in\" | \"maybe\" | \"out\";\n }>(votes),\n };\n}\n\nexport async function deleteActivity(id: number): Promise {\n await sqlite.execute(`DELETE FROM ${VOTES_TABLE} WHERE activity_id = ?`, [id]);\n await sqlite.execute(`DELETE FROM ${ACTIVITIES_TABLE} WHERE id = ?`, [id]);\n}\n\n// Vote operations\nexport async function setVote(activityId: number, roommateId: number, status: \"in\" | \"maybe\" | \"out\"): Promise {\n // Upsert: delete existing vote and insert new one\n await sqlite.execute(\n `DELETE FROM ${VOTES_TABLE} WHERE activity_id = ? AND roommate_id = ?`,\n [activityId, roommateId]\n );\n await sqlite.execute(\n `INSERT INTO ${VOTES_TABLE} (activity_id, roommate_id, status, created_at) VALUES (?, ?, ?, ?)`,\n [activityId, roommateId, status, Date.now()]\n );\n}\n\nexport async function removeVote(activityId: number, roommateId: number): Promise {\n await sqlite.execute(\n `DELETE FROM ${VOTES_TABLE} WHERE activity_id = ? AND roommate_id = ?`,\n [activityId, roommateId]\n );\n}\n\n// Utility\nfunction rowsToObjects(result: { columns: string[]; rows: unknown[][] }): T[] {\n return result.rows.map((row) => {\n const obj: Record = {};\n result.columns.forEach((col, idx) => {\n obj[col] = row[idx];\n });\n return obj as T;\n });\n}\n\nasync function createTables() {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${ROOMMATES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n emoji TEXT NOT NULL,\n created_at INTEGER NOT NULL\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${ACTIVITIES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n title TEXT NOT NULL,\n description TEXT,\n proposed_by INTEGER NOT NULL,\n activity_date INTEGER NOT NULL,\n location TEXT,\n created_at INTEGER NOT NULL,\n FOREIGN KEY (proposed_by) REFERENCES ${ROOMMATES_TABLE}(id)\n )\n `);\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${VOTES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n activity_id INTEGER NOT NULL,\n roommate_id INTEGER NOT NULL,\n status TEXT NOT NULL CHECK (status IN ('in', 'maybe', 'out')),\n created_at INTEGER NOT NULL,\n FOREIGN KEY (activity_id) REFERENCES ${ACTIVITIES_TABLE}(id),\n FOREIGN KEY (roommate_id) REFERENCES ${ROOMMATES_TABLE}(id),\n UNIQUE(activity_id, roommate_id)\n )\n `);\n}\n","deleted","label",[99],"permalink","https://www.val.town/x/dcm31/roommate-activities/code/db.ts","extension","actionData","errors"]