[{"_1":2,"_176":-5,"_177":-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","ff0419d68e57d8cc3e37e5d3da688157c5dd37c6","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,"_149":150,"_173":174,"_175":-5},"handle","cricks_unmixed4u","name","value-sort","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,"_148":20},"id","fa91da02-888f-11f0-8712-0224a6c84d84","createdAt",["D",1756881241396],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","0b86c720-ddc6-11ef-94bf-d1c3ffb48162","pinnedFileId","pinPreview","screenshotVersion","likeCount","7018ec7d-7a8e-4e8f-93d1-ab6a7334ca50","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJzSEJuSGdYT2dQSmprejVnU0VqalpDaWhKUSJ9","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":139,"_140":141,"_142":108,"_143":144,"_145":116,"_146":96,"_147":94},"version",52,"fa94debe-888f-11f0-8712-0224a6c84d84","projectId","forkedBranchId","cb6a155a-43e8-11f0-a2f5-76b3cceeab13","forkedBranchVersion",35,"forkedProjectPrivacy","forkedProjectId","cb69c7ee-43e8-11f0-a2f5-76b3cceeab13","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":151,"_95":152,"_153":154,"_155":156,"_157":136,"_158":159,"_160":161,"_103":162,"_134":163,"_164":165,"_166":167,"_168":20,"_169":-5,"_97":170},"10247198-88a1-11f0-aefd-0224a6c84d84","queries.ts","fileId","778601b6-8893-11f0-b679-0224a6c84d84","parentId","775e8dac-8893-11f0-9e41-0224a6c84d84","branchId","branchVersion",51,"latest",true,["D",1756888579029],6,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport * as z from \"https://esm.sh/zod@3\";\nimport { TABLES } from \"./migrations.ts\";\nimport {\n BudgetAllocation,\n BudgetSession,\n CreateItemReq,\n Id,\n Item,\n List,\n PairwiseSession,\n PairwiseVote,\n RankingRow,\n SubmitPairwiseVoteReq,\n} from \"../../shared/types.ts\";\n\n// Generate UUID v4\nfunction generateUUID(): string {\n return crypto.randomUUID();\n}\n\n// Current ISO timestamp\nfunction now(): string {\n return new Date().toISOString();\n}\n\n// Lists repository\nconst lists = {\n async create(ownerId: string, data: { title: string; description?: string }) {\n const id = generateUUID();\n const timestamp = now();\n\n await sqlite.execute(\n `INSERT INTO ${TABLES.lists} (id, ownerId, title, description, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, ?)`,\n [id, ownerId, data.title, data.description || null, timestamp, timestamp],\n );\n\n return List.parse({\n id,\n ownerId,\n title: data.title,\n description: data.description,\n createdAt: timestamp,\n updatedAt: timestamp,\n });\n },\n\n async byId(id: string) {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.lists} WHERE id = ?`,\n [id],\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return List.parse({\n id: row[0],\n ownerId: row[1],\n title: row[2],\n description: row[3],\n createdAt: row[4],\n updatedAt: row[5],\n });\n },\n\n async byOwner(ownerId: string) {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.lists} WHERE ownerId = ? ORDER BY createdAt DESC`,\n [ownerId],\n );\n\n return result.rows.map((row) =>\n List.parse({\n id: row[0],\n ownerId: row[1],\n title: row[2],\n description: row[3],\n createdAt: row[4],\n updatedAt: row[5],\n })\n );\n },\n};\n\n// Items repository\nconst items = {\n async create(\n data: z.infer & { meta?: Record },\n ) {\n const id = generateUUID();\n const timestamp = now();\n const meta = JSON.stringify(data.meta || {});\n\n await sqlite.execute(\n `INSERT INTO ${TABLES.items} (id, listId, label, meta, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, ?)`,\n [id, data.listId, data.label, meta, timestamp, timestamp],\n );\n\n return Item.parse({\n id,\n listId: data.listId,\n label: data.label,\n meta: data.meta || {},\n createdAt: timestamp,\n updatedAt: timestamp,\n });\n },\n\n async byList(listId: string) {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.items} WHERE listId = ? ORDER BY createdAt ASC`,\n [listId],\n );\n\n return result.rows.map((row) =>\n Item.parse({\n id: row[0],\n listId: row[1],\n label: row[2],\n meta: JSON.parse(row[3] as string),\n createdAt: row[4],\n updatedAt: row[5],\n })\n );\n },\n\n async byId(id: string) {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.items} WHERE id = ?`,\n [id],\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return Item.parse({\n id: row[0],\n listId: row[1],\n label: row[2],\n meta: JSON.parse(row[3] as string),\n createdAt: row[4],\n updatedAt: row[5],\n });\n },\n};\n\n// Pairwise comparison repository\nconst pairwise = {\n async startSession(\n listId: string,\n userId: string,\n strategy: \"random\" | \"balanced\",\n ) {\n const id = generateUUID();\n const timestamp = now();\n\n await sqlite.execute(\n `INSERT INTO ${TABLES.pairwise_sessions} (id, listId, userId, strategy, createdAt) VALUES (?, ?, ?, ?, ?)`,\n [id, listId, userId, strategy, timestamp],\n );\n\n return PairwiseSession.parse({\n id,\n listId,\n userId,\n strategy,\n createdAt: timestamp,\n closedAt: null,\n });\n },\n\n async nextPair(sessionId: string) {\n // Get session info\n const sessionResult = await sqlite.execute(\n `SELECT listId FROM ${TABLES.pairwise_sessions} WHERE id = ?`,\n [sessionId],\n );\n\n if (sessionResult.rows.length === 0) return null;\n const listId = sessionResult.rows[0][0];\n\n // Get all items for this list\n const itemsData = await items.byList(listId as string);\n if (itemsData.length < 2) return null;\n\n // Get already compared pairs for this session\n const votesResult = await sqlite.execute(\n `SELECT leftItemId, rightItemId FROM ${TABLES.pairwise_votes} WHERE sessionId = ?`,\n [sessionId],\n );\n\n const comparedPairs = new Set(\n votesResult.rows.map((row) => `${row[0]}-${row[1]}`),\n );\n\n // Shuffle the items array for randomization\n const shuffledItems = [...itemsData];\n for (let i = shuffledItems.length - 1; i > 0; i--) {\n const j = Math.floor(Math.random() * (i + 1));\n [shuffledItems[i], shuffledItems[j]] = [shuffledItems[j], shuffledItems[i]];\n }\n\n // Find an uncompared pair from shuffled items\n for (let i = 0; i < shuffledItems.length; i++) {\n for (let j = i + 1; j < shuffledItems.length; j++) {\n const left = shuffledItems[i];\n const right = shuffledItems[j];\n const pairKey = `${left.id}-${right.id}`;\n const reversePairKey = `${right.id}-${left.id}`;\n\n if (!comparedPairs.has(pairKey) && !comparedPairs.has(reversePairKey)) {\n // Randomly decide which item goes on left vs right for additional randomization\n const randomOrder = Math.random() < 0.5;\n return randomOrder ? { left, right } : { left: right, right: left };\n }\n }\n }\n\n return null; // All pairs compared\n },\n\n async submitVote(vote: z.infer) {\n const id = generateUUID();\n const timestamp = now();\n\n // Get session to find listId\n const sessionResult = await sqlite.execute(\n `SELECT listId FROM ${TABLES.pairwise_sessions} WHERE id = ?`,\n [vote.sessionId],\n );\n\n const listId = sessionResult.rows[0][0] as string;\n\n await sqlite.execute(\n `INSERT INTO ${TABLES.pairwise_votes} (id, sessionId, listId, leftItemId, rightItemId, winnerItemId, createdAt) VALUES (?, ?, ?, ?, ?, ?, ?)`,\n [\n id,\n vote.sessionId,\n listId,\n vote.leftItemId,\n vote.rightItemId,\n vote.winnerItemId,\n timestamp,\n ],\n );\n },\n\n async ranking(listId: string): Promise {\n // Calculate win rates for each item\n const result = await sqlite.execute(\n `\n SELECT \n i.id as itemId,\n i.label,\n COALESCE(wins.win_count, 0) as wins,\n COALESCE(total.total_count, 0) as total\n FROM ${TABLES.items} i\n LEFT JOIN (\n SELECT winnerItemId, COUNT(*) as win_count\n FROM ${TABLES.pairwise_votes}\n WHERE listId = ?\n GROUP BY winnerItemId\n ) wins ON i.id = wins.winnerItemId\n LEFT JOIN (\n SELECT itemId, COUNT(*) as total_count\n FROM (\n SELECT leftItemId as itemId FROM ${TABLES.pairwise_votes} WHERE listId = ?\n UNION ALL\n SELECT rightItemId as itemId FROM ${TABLES.pairwise_votes} WHERE listId = ?\n )\n GROUP BY itemId\n ) total ON i.id = total.itemId\n WHERE i.listId = ?\n ORDER BY (CAST(wins AS REAL) / CASE WHEN total = 0 THEN 1 ELSE total END) DESC\n `,\n [listId, listId, listId, listId],\n );\n\n return result.rows.map((row) =>\n RankingRow.parse({\n itemId: row[0] as string,\n score: row[3] === 0 ? 0 : (row[2] as number) / (row[3] as number),\n method: \"pairwise\" as const,\n })\n );\n },\n};\n\n// Budget allocation repository\nconst budget = {\n async startSession(listId: string, userId: string, budget: number) {\n const id = generateUUID();\n const timestamp = now();\n\n await sqlite.execute(\n `INSERT INTO ${TABLES.budget_sessions} (id, listId, userId, budget, createdAt) VALUES (?, ?, ?, ?, ?)`,\n [id, listId, userId, budget, timestamp],\n );\n\n return BudgetSession.parse({\n id,\n listId,\n userId,\n budget,\n createdAt: timestamp,\n submittedAt: null,\n });\n },\n\n async getSession(sessionId: string) {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.budget_sessions} WHERE id = ?`,\n [sessionId],\n );\n\n if (result.rows.length === 0) return null;\n\n const row = result.rows[0];\n return BudgetSession.parse({\n id: row[0],\n listId: row[1],\n userId: row[2],\n budget: row[3],\n createdAt: row[4],\n submittedAt: row[5],\n });\n },\n\n async envelope(sessionId: string) {\n const session = await this.getSession(sessionId);\n if (!session) throw new Error(\"Session not found\");\n\n const itemsData = await items.byList(session.listId);\n\n // Get current allocations if any\n const allocResult = await sqlite.execute(\n `SELECT itemId, amount FROM ${TABLES.budget_allocations} WHERE sessionId = ?`,\n [sessionId],\n );\n\n const current = allocResult.rows.map((row) => ({\n itemId: row[0] as string,\n amount: row[1] as number,\n }));\n\n return {\n budget: session.budget,\n items: itemsData,\n current,\n };\n },\n\n async submit(\n sessionId: string,\n allocations: Array<{ itemId: string; amount: number }>,\n ) {\n const timestamp = now();\n\n // Get session to find listId\n const session = await this.getSession(sessionId);\n if (!session) throw new Error(\"Session not found\");\n\n // Use a batch transaction to ensure atomicity\n const batchOps = [\n // Clear existing allocations for this session\n {\n sql: `DELETE FROM ${TABLES.budget_allocations} WHERE sessionId = ?`,\n args: [sessionId],\n },\n // Mark session as submitted\n {\n sql:\n `UPDATE ${TABLES.budget_sessions} SET submittedAt = ? WHERE id = ?`,\n args: [timestamp, sessionId],\n },\n ];\n\n // Add new allocations to the batch\n for (const alloc of allocations) {\n batchOps.push({\n sql:\n `INSERT INTO ${TABLES.budget_allocations} (id, sessionId, listId, itemId, amount, createdAt) VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n generateUUID(),\n sessionId,\n session.listId,\n alloc.itemId,\n alloc.amount,\n timestamp,\n ],\n });\n }\n\n await sqlite.batch(batchOps);\n },\n\n async ranking(listId: string): Promise {\n // Calculate average allocation for each item across all completed sessions\n const result = await sqlite.execute(\n `\n SELECT \n ba.itemId,\n AVG(ba.amount) as avg_amount\n FROM ${TABLES.budget_allocations} ba\n JOIN ${TABLES.budget_sessions} bs ON ba.sessionId = bs.id\n WHERE ba.listId = ? AND bs.submittedAt IS NOT NULL\n GROUP BY ba.itemId\n ORDER BY avg_amount DESC\n `,\n [listId],\n );\n\n return result.rows.map((row) =>\n RankingRow.parse({\n itemId: row[0] as string,\n score: row[1] as number,\n method: \"budget\" as const,\n })\n );\n },\n};\n\n// Sessions repository\nconst sessions = {\n async byUser(userId: string) {\n // Get pairwise sessions with list info\n const pairwiseResult = await sqlite.execute(`\n SELECT \n ps.id, ps.listId, ps.userId, ps.strategy, ps.createdAt, ps.closedAt,\n l.title as listTitle,\n COUNT(pv.id) as voteCount\n FROM ${TABLES.pairwise_sessions} ps\n JOIN ${TABLES.lists} l ON ps.listId = l.id\n LEFT JOIN ${TABLES.pairwise_votes} pv ON ps.id = pv.sessionId\n WHERE ps.userId = ?\n GROUP BY ps.id, ps.listId, ps.userId, ps.strategy, ps.createdAt, ps.closedAt, l.title\n ORDER BY ps.createdAt DESC\n `, [userId]);\n\n // Get budget sessions with list info\n const budgetResult = await sqlite.execute(`\n SELECT \n bs.id, bs.listId, bs.userId, bs.budget, bs.createdAt, bs.submittedAt,\n l.title as listTitle\n FROM ${TABLES.budget_sessions} bs\n JOIN ${TABLES.lists} l ON bs.listId = l.id\n WHERE bs.userId = ?\n ORDER BY bs.createdAt DESC\n `, [userId]);\n\n const pairwiseSessions = pairwiseResult.rows.map(row => ({\n id: row[0] as string,\n type: \"pairwise\" as const,\n listId: row[1] as string,\n listTitle: row[6] as string,\n createdAt: row[4] as string,\n completedAt: row[5] as string | null,\n voteCount: row[7] as number,\n strategy: row[3] as string\n }));\n\n const budgetSessions = budgetResult.rows.map(row => ({\n id: row[0] as string,\n type: \"budget\" as const,\n listId: row[1] as string,\n listTitle: row[6] as string,\n createdAt: row[4] as string,\n completedAt: row[5] as string | null,\n budget: row[3] as number\n }));\n\n // Combine and sort by creation date\n const allSessions = [...pairwiseSessions, ...budgetSessions];\n return allSessions.sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime());\n },\n};\n\nexport const repo = {\n lists,\n items,\n pairwise,\n budget,\n sessions,\n};\n\n","deleted","label",[171,172,152],"backend","database","permalink","https://www.val.town/x/cricks_unmixed4u/value-sort/code/backend/database/queries.ts","extension","actionData","errors"]