[{"_1":2,"_179":-5,"_180":-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","95c2b214125f68b24ec9fda4034953728efbbf27","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,"_153":154,"_176":177,"_178":-5},"handle","shapedlines","name","runway","path","backend/database/queries.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":74,"_112":-5,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":-5,"_118":33,"_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","d5ffaa26-11ff-11f1-b7b3-42dde27851f2","createdAt",["D",1771992592978],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","7ed186b0-c78a-4757-a5b0-12c00ac89afa","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","e9195859-3c6b-4b40-bb3e-c11844477fdf","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yYlc2Q1A2eUFyNEdDeXBud0E0bFJyWlJVUzQifQ","org",{"_95":96,"_136":133,"_12":113,"_137":138},"avatar","tier","pro","branch",{"_141":142,"_97":45,"_103":143,"_144":104,"_145":-5,"_146":-5,"_147":-5,"_148":-5,"_149":-5,"_150":98,"_151":96},"version",23,"d6000d54-11ff-11f1-b7b3-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":155,"_97":156,"_157":158,"_159":160,"_161":143,"_162":163,"_164":165,"_105":166,"_141":33,"_167":168,"_169":170,"_171":20,"_172":-5,"_99":173},"ca57533c-1203-11f1-9a25-42dde27851f2","queries.ts","fileId","ca57540e-1203-11f1-9a25-42dde27851f2","parentId","ca044fac-1203-11f1-bfb6-42dde27851f2","branchId","branchVersion",13,"latest",true,["D",1771994291403],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { Account, Transaction } from \"../../shared/types.ts\";\n\nconst ACCOUNTS = \"runway_accounts\";\nconst TRANSACTIONS = \"runway_transactions\";\nconst SYNC_STATE = \"runway_sync_state\";\n\n// ---------------------------------------------------------------------------\n// Sync state\n// ---------------------------------------------------------------------------\n\nexport async function getSyncState(key: string): Promise {\n const result = await sqlite.execute(\n `SELECT value FROM ${SYNC_STATE} WHERE key = ?`,\n [key],\n );\n return result.rows.length > 0 ? String(result.rows[0][0]) : null;\n}\n\nexport async function setSyncState(key: string, value: string): Promise {\n await sqlite.execute(\n `INSERT OR REPLACE INTO ${SYNC_STATE} (key, value) VALUES (?, ?)`,\n [key, value],\n );\n}\n\n// ---------------------------------------------------------------------------\n// Accounts\n// ---------------------------------------------------------------------------\n\nexport async function upsertAccount(account: Account): Promise {\n await sqlite.execute(\n `INSERT OR REPLACE INTO ${ACCOUNTS}\n (id, name, org_domain, currency, balance, balance_date, excluded)\n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n [\n account.id,\n account.name,\n account.org_domain,\n account.currency,\n account.balance,\n account.balance_date,\n account.excluded ? 1 : 0,\n ],\n );\n}\n\nexport async function getAccounts(): Promise {\n const result = await sqlite.execute(`SELECT * FROM ${ACCOUNTS} ORDER BY name`);\n return result.rows.map((row) => ({\n id: String(row[0]),\n name: String(row[1]),\n org_domain: String(row[2]),\n currency: String(row[3]),\n balance: String(row[4]),\n balance_date: Number(row[5]),\n excluded: Boolean(row[6]),\n }));\n}\n\nexport async function setAccountExcluded(id: string, excluded: boolean): Promise {\n await sqlite.execute(\n `UPDATE ${ACCOUNTS} SET excluded = ? WHERE id = ?`,\n [excluded ? 1 : 0, id],\n );\n}\n\nexport async function getExcludedAccountIds(): Promise> {\n const result = await sqlite.execute(\n `SELECT id FROM ${ACCOUNTS} WHERE excluded = 1`,\n );\n return new Set(result.rows.map((row) => String(row[0])));\n}\n\n// ---------------------------------------------------------------------------\n// Transactions\n// ---------------------------------------------------------------------------\n\nexport async function upsertTransaction(tx: Transaction): Promise {\n await sqlite.execute(\n `INSERT INTO ${TRANSACTIONS}\n (id, account_id, posted, amount, description, payee, notes, category, category_source, is_transfer, pending)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n ON CONFLICT(id) DO UPDATE SET\n account_id = excluded.account_id,\n posted = excluded.posted,\n amount = excluded.amount,\n description = excluded.description,\n payee = CASE WHEN ${TRANSACTIONS}.category_source = 'manual' THEN ${TRANSACTIONS}.payee ELSE excluded.payee END,\n notes = excluded.notes,\n category = CASE WHEN ${TRANSACTIONS}.category_source = 'manual' THEN ${TRANSACTIONS}.category ELSE excluded.category END,\n category_source = CASE WHEN ${TRANSACTIONS}.category_source = 'manual' THEN 'manual' ELSE excluded.category_source END,\n is_transfer = CASE WHEN ${TRANSACTIONS}.category_source = 'manual' THEN ${TRANSACTIONS}.is_transfer ELSE excluded.is_transfer END,\n pending = excluded.pending`,\n [\n tx.id,\n tx.account_id,\n tx.posted,\n tx.amount,\n tx.description,\n tx.payee,\n tx.notes,\n tx.category,\n tx.category_source,\n tx.is_transfer ? 1 : 0,\n tx.pending ? 1 : 0,\n ],\n );\n}\n\nfunction rowToTransaction(row: any[]): Transaction {\n return {\n id: String(row[0]),\n account_id: String(row[1]),\n posted: Number(row[2]),\n amount: String(row[3]),\n description: String(row[4]),\n payee: String(row[5]),\n notes: String(row[6]),\n category: String(row[7]),\n category_source: String(row[8]) as Transaction[\"category_source\"],\n is_transfer: Boolean(row[9]),\n pending: Boolean(row[10]),\n };\n}\n\nexport async function getTransactions(opts: {\n month?: string;\n category?: string;\n search?: string;\n limit?: number;\n offset?: number;\n}): Promise<{ transactions: Transaction[]; total: number }> {\n const conditions: string[] = [];\n const params: any[] = [];\n\n if (opts.month) {\n const [year, month] = opts.month.split(\"-\").map(Number);\n const start = new Date(year, month - 1, 1).getTime() / 1000;\n const end = new Date(year, month, 1).getTime() / 1000;\n conditions.push(\"posted >= ? AND posted < ?\");\n params.push(start, end);\n }\n\n if (opts.category) {\n conditions.push(\"category = ?\");\n params.push(opts.category);\n }\n\n if (opts.search) {\n conditions.push(\"(payee LIKE ? OR description LIKE ?)\");\n const term = `%${opts.search}%`;\n params.push(term, term);\n }\n\n const where = conditions.length > 0 ? `WHERE ${conditions.join(\" AND \")}` : \"\";\n\n const countResult = await sqlite.execute(\n `SELECT COUNT(*) FROM ${TRANSACTIONS} ${where}`,\n params,\n );\n const total = Number(countResult.rows[0][0]);\n\n const limit = opts.limit ?? 200;\n const offset = opts.offset ?? 0;\n const dataResult = await sqlite.execute(\n `SELECT * FROM ${TRANSACTIONS} ${where} ORDER BY posted DESC LIMIT ? OFFSET ?`,\n [...params, limit, offset],\n );\n\n return {\n transactions: dataResult.rows.map(rowToTransaction),\n total,\n };\n}\n\nexport async function updateTransactionCategory(\n id: string,\n category: string,\n isTransfer: boolean,\n): Promise {\n await sqlite.execute(\n `UPDATE ${TRANSACTIONS} SET category = ?, category_source = 'manual', is_transfer = ? WHERE id = ?`,\n [category, isTransfer ? 1 : 0, id],\n );\n}\n\nexport async function getMonthlySpendByCategory(month: string): Promise> {\n const [year, m] = month.split(\"-\").map(Number);\n const start = new Date(year, m - 1, 1).getTime() / 1000;\n const end = new Date(year, m, 1).getTime() / 1000;\n\n const result = await sqlite.execute(\n `SELECT category, SUM(CAST(amount AS REAL)) as total\n FROM ${TRANSACTIONS}\n WHERE posted >= ? AND posted < ? AND is_transfer = 0\n GROUP BY category`,\n [start, end],\n );\n\n const spending: Record = {};\n for (const row of result.rows) {\n spending[String(row[0])] = Number(row[1]);\n }\n return spending;\n}\n\nexport async function getMonthlyIncome(month: string): Promise {\n const [year, m] = month.split(\"-\").map(Number);\n const start = new Date(year, m - 1, 1).getTime() / 1000;\n const end = new Date(year, m, 1).getTime() / 1000;\n\n const result = await sqlite.execute(\n `SELECT SUM(CAST(amount AS REAL))\n FROM ${TRANSACTIONS}\n WHERE posted >= ? AND posted < ? AND category = 'Income'`,\n [start, end],\n );\n\n return result.rows[0][0] ? Number(result.rows[0][0]) : 0;\n}\n","deleted","label",[174,175,156],"backend","database","permalink","https://www.val.town/x/shapedlines/runway/code/backend/database/queries.ts","extension","actionData","errors"]