[{"_1":2,"_182":-5,"_183":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_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":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","436c7f7c8801ac8e84ab78aba420b373f6264c65","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_156":157,"_179":180,"_181":-5},"handle","clintr","name","npb-draft","path","backend/db.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":-5,"_118":33,"_14":119,"_120":121,"_122":123,"_124":-5,"_125":126,"_127":126,"_128":129,"_130":129,"_131":129,"_132":126,"_133":126,"_23":134,"_137":138,"_142":143,"_155":20},"id","4c935dd8-1bc7-11f1-aaf0-42dde27851f2","createdAt",["D",1773067822232],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","019ca01c-a52c-7496-b132-461e941e09a1","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","019ca01c-a52c-7496-b132-485cb9878fcf","tursoName","4c935dd81bc711f1aaf042dde27851f2","tursoHostname","4c935dd81bc711f1aaf042dde27851f2-val-town.aws-us-east-2.turso.io","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_135":136},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBR0owU1VPWkdOd0ZST1RYWGpUR3gyUUZuRSJ9","org",{"_95":96,"_139":136,"_12":113,"_140":141},"avatar","tier","free","branch",{"_144":145,"_97":45,"_103":146,"_147":104,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":98,"_154":96},"version",68,"4c93c020-1bc7-11f1-aaf0-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":158,"_97":159,"_160":161,"_162":163,"_164":146,"_165":166,"_167":168,"_105":169,"_144":170,"_171":172,"_173":174,"_175":20,"_176":-5,"_99":177},"d77c426c-1bcb-11f1-8abf-42dde27851f2","db.ts","fileId","9958478c-1bc7-11f1-b77a-42dde27851f2","parentId","7427c5a0-1bc7-11f1-8bec-42dde27851f2","branchId","branchVersion",53,"latest",true,["D",1773069773256],4,"fileType","script","content","// Database layer for NPB Draft app — Val Town SQLite\n// 2026-03-09\n//\n// Val Town SQLite returns rows as objects (keyed by column name), NOT arrays.\n// All row access uses property names like row.id, row.email, etc.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport async function initDb() {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS managers (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT UNIQUE NOT NULL,\n team_name TEXT NOT NULL,\n session_token TEXT UNIQUE,\n created_at TEXT DEFAULT (datetime('now'))\n )`,\n `CREATE TABLE IF NOT EXISTS picks (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n manager_id INTEGER NOT NULL REFERENCES managers(id),\n player_adp INTEGER NOT NULL,\n rank_order INTEGER NOT NULL,\n UNIQUE(manager_id, player_adp),\n UNIQUE(manager_id, rank_order)\n )`,\n `CREATE TABLE IF NOT EXISTS draft_results (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n seed TEXT NOT NULL,\n result_json TEXT NOT NULL,\n executed_at TEXT DEFAULT (datetime('now'))\n )`,\n ]);\n}\n\n// --- Auth ---\n\nexport async function findOrCreateManager(\n email: string,\n teamName: string,\n): Promise<{ id: number; sessionToken: string }> {\n const normalizedEmail = email.toLowerCase().trim();\n const normalizedTeam = teamName.trim();\n const token = crypto.randomUUID();\n\n await sqlite.execute({\n sql:\n \"INSERT OR IGNORE INTO managers (email, team_name, session_token) VALUES (?, ?, ?)\",\n args: [normalizedEmail, normalizedTeam, token],\n });\n\n await sqlite.execute({\n sql: \"UPDATE managers SET team_name = ?, session_token = ? WHERE email = ?\",\n args: [normalizedTeam, token, normalizedEmail],\n });\n\n const result = await sqlite.execute({\n sql: \"SELECT id FROM managers WHERE email = ?\",\n args: [normalizedEmail],\n });\n\n const row: any = result.rows[0];\n return { id: row.id as number, sessionToken: token };\n}\n\nexport async function getManagerByToken(\n token: string,\n): Promise<{ id: number; email: string; teamName: string } | null> {\n const result = await sqlite.execute({\n sql: \"SELECT id, email, team_name FROM managers WHERE session_token = ?\",\n args: [token],\n });\n if (result.rows.length === 0) return null;\n const row: any = result.rows[0];\n return {\n id: row.id as number,\n email: row.email as string,\n teamName: row.team_name as string,\n };\n}\n\n// --- Picks ---\n\nexport async function savePicks(managerId: number, playerAdps: number[]) {\n const statements: Array<{ sql: string; args: unknown[] }> = [\n { sql: \"DELETE FROM picks WHERE manager_id = ?\", args: [managerId] },\n ];\n\n for (let i = 0; i < playerAdps.length; i++) {\n statements.push({\n sql:\n \"INSERT INTO picks (manager_id, player_adp, rank_order) VALUES (?, ?, ?)\",\n args: [managerId, playerAdps[i], i + 1],\n });\n }\n\n await sqlite.batch(statements);\n}\n\nexport async function getPicks(managerId: number): Promise {\n const result = await sqlite.execute({\n sql:\n \"SELECT player_adp FROM picks WHERE manager_id = ? ORDER BY rank_order\",\n args: [managerId],\n });\n return result.rows.map((r: any) => r.player_adp as number);\n}\n\n// --- Admin ---\n\nexport async function getAllManagers(): Promise<\n Array<{ id: number; email: string; teamName: string; pickCount: number }>\n> {\n const result = await sqlite.execute(`\n SELECT m.id, m.email, m.team_name, COUNT(p.id) as pick_count\n FROM managers m\n LEFT JOIN picks p ON m.id = p.manager_id\n GROUP BY m.id\n ORDER BY m.created_at\n `);\n return result.rows.map((r: any) => ({\n id: r.id as number,\n email: r.email as string,\n teamName: r.team_name as string,\n pickCount: r.pick_count as number,\n }));\n}\n\nexport async function getAllManagersWithPicks(): Promise<\n Array<{ id: number; email: string; teamName: string; rankings: number[] }>\n> {\n const managers = await getAllManagers();\n const picksResult = await sqlite.execute(\n \"SELECT manager_id, player_adp FROM picks ORDER BY manager_id, rank_order\",\n );\n\n const picksMap = new Map();\n for (const row of picksResult.rows) {\n const r: any = row;\n const mid = r.manager_id as number;\n const adp = r.player_adp as number;\n if (!picksMap.has(mid)) picksMap.set(mid, []);\n picksMap.get(mid)!.push(adp);\n }\n\n return managers.map((m) => ({\n id: m.id,\n email: m.email,\n teamName: m.teamName,\n rankings: picksMap.get(m.id) ?? [],\n }));\n}\n\n// --- Draft Results ---\n\nexport async function isDraftExecuted(): Promise {\n const result = await sqlite.execute(\n \"SELECT COUNT(*) as cnt FROM draft_results\",\n );\n const row: any = result.rows[0];\n return (row.cnt as number) > 0;\n}\n\nexport async function saveDraftResult(seed: string, resultJson: string) {\n await sqlite.execute({\n sql: \"INSERT INTO draft_results (seed, result_json) VALUES (?, ?)\",\n args: [seed, resultJson],\n });\n}\n\nexport async function getLatestDraftResult(): Promise<\n {\n seed: string;\n result: unknown;\n executedAt: string;\n } | null\n> {\n const result = await sqlite.execute(\n \"SELECT seed, result_json, executed_at FROM draft_results ORDER BY id DESC LIMIT 1\",\n );\n if (result.rows.length === 0) return null;\n const row: any = result.rows[0];\n return {\n seed: row.seed as string,\n result: JSON.parse(row.result_json as string),\n executedAt: row.executed_at as string,\n };\n}","deleted","label",[178,159],"backend","permalink","https://www.val.town/x/clintr/npb-draft/code/backend/db.ts","extension","actionData","errors"]