[{"_1":2,"_172":-5,"_173":-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,"_146":147,"_169":170,"_171":-5},"handle","pmaxx","name","untitled-8939","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,"_145":20},"id","0d1f68f4-3333-11f0-9f41-9e149126039e","createdAt",["D",1747495480497],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","aba6df40-31bf-11f0-ac4e-e3462f58c9c4","pinnedFileId","pinPreview","screenshotVersion","likeCount","b516df03-1afb-4b4c-b24c-661c4fbc488b","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4OTJSTExldk5KVEZQd2w4Qm9KVVNuTERuUCJ9","org",{"_93":94,"_131":128,"_12":111},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",18,"0d1fb8ae-3333-11f0-9f41-9e149126039e","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"6156eb54-3333-11f0-9159-9e149126039e","queries.ts","fileId","6156ec1c-3333-11f0-9159-9e149126039e","parentId","46d8a173-1fde-43f0-a791-9483ac3b7f95","branchId","branchVersion",4,"latest",true,["D",1747495621787],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { \n USERS_TABLE, \n ENVELOPES_TABLE, \n TRANSACTIONS_TABLE, \n ACHIEVEMENTS_TABLE \n} from \"./migrations.ts\";\nimport type { User, Envelope, Transaction, Achievement } from \"../../shared/types.ts\";\n\n// User queries\nexport async function createUser(name: string): Promise {\n const userId = crypto.randomUUID();\n \n await sqlite.execute(\n `INSERT INTO ${USERS_TABLE} (id, name, total_balance, unallocated) VALUES (?, ?, 0, 0)`,\n [userId, name]\n );\n \n // Create default achievements for the user\n const defaultAchievements = [\n {\n id: \"first_budget\",\n name: \"Budget Beginner\",\n description: \"Created your first budget envelope\",\n icon: \"🏆\"\n },\n {\n id: \"full_allocation\",\n name: \"Money Master\",\n description: \"Allocated 100% of your money to envelopes\",\n icon: \"💰\"\n },\n {\n id: \"saving_streak\",\n name: \"Saving Superstar\",\n description: \"Saved money for 3 weeks in a row\",\n icon: \"⭐\"\n },\n {\n id: \"csv_import\",\n name: \"Data Wizard\",\n description: \"Successfully imported your first bank statement\",\n icon: \"📊\"\n }\n ];\n \n for (const achievement of defaultAchievements) {\n await sqlite.execute(\n `INSERT INTO ${ACHIEVEMENTS_TABLE} (id, user_id, name, description, icon, unlocked) \n VALUES (?, ?, ?, ?, ?, 0)`,\n [achievement.id, userId, achievement.name, achievement.description, achievement.icon]\n );\n }\n \n return getUserById(userId);\n}\n\nexport async function getUserById(userId: string): Promise {\n const userResult = await sqlite.execute(\n `SELECT * FROM ${USERS_TABLE} WHERE id = ?`,\n [userId]\n );\n \n if (userResult.rows.length === 0) {\n return null;\n }\n \n const user = userResult.rows[0];\n \n // Get envelopes\n const envelopes = await getEnvelopesByUserId(userId);\n \n // Get transactions\n const transactions = await getTransactionsByUserId(userId);\n \n // Get achievements\n const achievements = await getAchievementsByUserId(userId);\n \n return {\n id: user.id,\n name: user.name,\n totalBalance: user.total_balance,\n unallocated: user.unallocated,\n envelopes,\n transactions,\n achievements\n };\n}\n\nexport async function updateUserBalance(userId: string, totalBalance: number, unallocated: number): Promise {\n await sqlite.execute(\n `UPDATE ${USERS_TABLE} SET total_balance = ?, unallocated = ? WHERE id = ?`,\n [totalBalance, unallocated, userId]\n );\n}\n\n// Envelope queries\nexport async function createEnvelope(\n userId: string, \n name: string, \n icon: string, \n color: string, \n budget: number = 0\n): Promise {\n const envelopeId = crypto.randomUUID();\n \n await sqlite.execute(\n `INSERT INTO ${ENVELOPES_TABLE} (id, user_id, name, icon, color, budget, current) \n VALUES (?, ?, ?, ?, ?, ?, 0)`,\n [envelopeId, userId, name, icon, color, budget]\n );\n \n // Check for first budget achievement\n await checkAndUnlockAchievement(userId, \"first_budget\");\n \n return {\n id: envelopeId,\n name,\n icon,\n color,\n budget,\n current: 0\n };\n}\n\nexport async function getEnvelopesByUserId(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${ENVELOPES_TABLE} WHERE user_id = ?`,\n [userId]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n icon: row.icon,\n color: row.color,\n budget: row.budget,\n current: row.current\n }));\n}\n\nexport async function updateEnvelopeAmount(envelopeId: string, amount: number): Promise {\n await sqlite.execute(\n `UPDATE ${ENVELOPES_TABLE} SET current = ? WHERE id = ?`,\n [amount, envelopeId]\n );\n}\n\n// Transaction queries\nexport async function addTransactions(\n userId: string, \n transactions: Omit[]\n): Promise {\n const createdTransactions: Transaction[] = [];\n \n for (const transaction of transactions) {\n const transactionId = crypto.randomUUID();\n \n await sqlite.execute(\n `INSERT INTO ${TRANSACTIONS_TABLE} (id, user_id, date, description, amount, category, processed) \n VALUES (?, ?, ?, ?, ?, ?, 0)`,\n [transactionId, userId, transaction.date, transaction.description, transaction.amount, transaction.category || null]\n );\n \n createdTransactions.push({\n id: transactionId,\n date: transaction.date,\n description: transaction.description,\n amount: transaction.amount,\n category: transaction.category,\n processed: false\n });\n }\n \n // Check for CSV import achievement\n if (createdTransactions.length > 0) {\n await checkAndUnlockAchievement(userId, \"csv_import\");\n }\n \n // Update user's total balance\n const totalAmount = transactions.reduce((sum, t) => sum + t.amount, 0);\n const user = await getUserById(userId);\n \n if (user) {\n const newTotal = user.totalBalance + totalAmount;\n const newUnallocated = user.unallocated + totalAmount;\n await updateUserBalance(userId, newTotal, newUnallocated);\n }\n \n return createdTransactions;\n}\n\nexport async function getTransactionsByUserId(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TRANSACTIONS_TABLE} WHERE user_id = ? ORDER BY date DESC`,\n [userId]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n date: row.date,\n description: row.description,\n amount: row.amount,\n category: row.category,\n processed: Boolean(row.processed)\n }));\n}\n\nexport async function assignTransactionToEnvelope(\n transactionId: string, \n envelopeId: string\n): Promise {\n await sqlite.execute(\n `UPDATE ${TRANSACTIONS_TABLE} SET envelope_id = ?, processed = 1 WHERE id = ?`,\n [envelopeId, transactionId]\n );\n}\n\n// Achievement queries\nexport async function getAchievementsByUserId(userId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${ACHIEVEMENTS_TABLE} WHERE user_id = ?`,\n [userId]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n icon: row.icon,\n unlocked: Boolean(row.unlocked),\n unlockedDate: row.unlocked_date\n }));\n}\n\nexport async function checkAndUnlockAchievement(userId: string, achievementId: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${ACHIEVEMENTS_TABLE} WHERE user_id = ? AND id = ?`,\n [userId, achievementId]\n );\n \n if (result.rows.length === 0 || result.rows[0].unlocked) {\n return false;\n }\n \n await sqlite.execute(\n `UPDATE ${ACHIEVEMENTS_TABLE} SET unlocked = 1, unlocked_date = CURRENT_TIMESTAMP WHERE user_id = ? AND id = ?`,\n [userId, achievementId]\n );\n \n return true;\n}\n\n// Check if all money is allocated (for achievement)\nexport async function checkFullAllocation(userId: string): Promise {\n const user = await getUserById(userId);\n \n if (user && user.unallocated === 0 && user.totalBalance > 0) {\n await checkAndUnlockAchievement(userId, \"full_allocation\");\n }\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/pmaxx/untitled-8939/code/backend/database/queries.ts","extension","actionData","errors"]