[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_107":108,"_109":110},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","pagosaoutside","name","black-hat-hydrology","path","storage.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":58,"_59":32,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/pagosaoutside/black-hat-hydrology/code/storage.ts","extension","id","21c090ef-62be-4050-8d72-5a81612ee9c3","fileId","cf66eefc-ab9e-11f1-91ac-1607ee4eb77e","parentId","branchId","70817041-1dfa-4778-885e-ac83be02b1d0","branchVersion",145,"latest",true,"createdAt",["D",1788883402631],"version",2,"fileType","script","content","// Val-scoped SQLite keeps this project's records isolated and returns rows as\n// named objects (the legacy account-global database returns arrays).\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport type StoredPrediction = {\n water_year: number;\n question_number: number;\n question_key: string;\n title: string;\n mode: string;\n issue_kind: string;\n issue_due_at: string;\n locked_at: string;\n input_cutoff_at: string;\n model_version: string;\n model_hash: string;\n prediction_json: string;\n inputs_json: string;\n inputs_hash: string;\n source_status_json: string;\n status: string;\n actual_json: string | null;\n score: string | null;\n scored_at: string | null;\n};\n\nexport async function initDb(): Promise {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS bh_seasons (\n water_year INTEGER PRIMARY KEY,\n opened_at TEXT NOT NULL,\n state TEXT NOT NULL DEFAULT 'active',\n model_bundle_version TEXT NOT NULL,\n completed_at TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS bh_predictions (\n water_year INTEGER NOT NULL,\n question_number INTEGER NOT NULL,\n question_key TEXT NOT NULL,\n title TEXT NOT NULL,\n mode TEXT NOT NULL CHECK(mode IN ('shadow','live')),\n issue_kind TEXT NOT NULL,\n issue_due_at TEXT NOT NULL,\n locked_at TEXT NOT NULL,\n input_cutoff_at TEXT NOT NULL,\n model_version TEXT NOT NULL,\n model_hash TEXT NOT NULL,\n prediction_json TEXT NOT NULL,\n inputs_json TEXT NOT NULL,\n inputs_hash TEXT NOT NULL,\n source_status_json TEXT NOT NULL,\n status TEXT NOT NULL DEFAULT 'locked',\n actual_json TEXT,\n score TEXT CHECK(score IN ('hit','miss') OR score IS NULL),\n scored_at TEXT,\n PRIMARY KEY (water_year, question_number, mode)\n )`,\n `CREATE TABLE IF NOT EXISTS bh_runs (\n id TEXT PRIMARY KEY,\n ran_at TEXT NOT NULL,\n mode TEXT NOT NULL,\n success INTEGER NOT NULL,\n message TEXT NOT NULL,\n source_status_json TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS bh_events (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n water_year INTEGER NOT NULL,\n question_number INTEGER,\n event_type TEXT NOT NULL,\n occurred_at TEXT NOT NULL,\n payload_json TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS bh_public_cache (\n cache_key TEXT PRIMARY KEY,\n value_json TEXT NOT NULL,\n updated_at TEXT NOT NULL\n )`,\n `CREATE TRIGGER IF NOT EXISTS bh_locked_prediction_immutable\n BEFORE UPDATE OF issue_due_at, locked_at, input_cutoff_at, model_version, model_hash, prediction_json, inputs_json, inputs_hash\n ON bh_predictions\n BEGIN SELECT RAISE(ABORT, 'Locked Black Hat prediction fields are immutable'); END`,\n ]);\n}\n\nexport async function ensureSeason(\n waterYear: number,\n modelVersion: string,\n openedAt: Date,\n): Promise {\n await sqlite.execute({\n sql:\n `INSERT OR IGNORE INTO bh_seasons(water_year, opened_at, model_bundle_version) VALUES (?, ?, ?)`,\n args: [waterYear, openedAt.toISOString(), modelVersion],\n });\n}\n\nexport async function getPrediction(\n waterYear: number,\n questionNumber: number,\n mode: string,\n): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT * FROM bh_predictions WHERE water_year=? AND question_number=? AND mode=?`,\n args: [waterYear, questionNumber, mode],\n });\n return (result.rows[0] as any) || null;\n}\n\nexport async function lockPrediction(row: StoredPrediction): Promise {\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO bh_predictions(\n water_year, question_number, question_key, title, mode, issue_kind, issue_due_at, locked_at,\n input_cutoff_at, model_version, model_hash, prediction_json, inputs_json, inputs_hash,\n source_status_json, status, actual_json, score, scored_at\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n row.water_year,\n row.question_number,\n row.question_key,\n row.title,\n row.mode,\n row.issue_kind,\n row.issue_due_at,\n row.locked_at,\n row.input_cutoff_at,\n row.model_version,\n row.model_hash,\n row.prediction_json,\n row.inputs_json,\n row.inputs_hash,\n row.source_status_json,\n row.status,\n row.actual_json,\n row.score,\n row.scored_at,\n ],\n });\n return Number(result.rowsAffected) === 1;\n}\n\nexport async function finalizePrediction(\n waterYear: number,\n questionNumber: number,\n mode: string,\n actualJson: string,\n hit: boolean,\n scoredAt: Date,\n): Promise {\n await sqlite.execute({\n sql:\n `UPDATE bh_predictions SET actual_json=?, score=?, status='scored', scored_at=?\n WHERE water_year=? AND question_number=? AND mode=? AND scored_at IS NULL`,\n args: [\n actualJson,\n hit ? \"hit\" : \"miss\",\n scoredAt.toISOString(),\n waterYear,\n questionNumber,\n mode,\n ],\n });\n}\n\nexport async function recordEvent(\n waterYear: number,\n questionNumber: number | null,\n eventType: string,\n occurredAt: Date,\n payload: unknown,\n): Promise {\n await sqlite.execute({\n sql:\n `INSERT INTO bh_events(water_year, question_number, event_type, occurred_at, payload_json) VALUES (?, ?, ?, ?, ?)`,\n args: [\n waterYear,\n questionNumber,\n eventType,\n occurredAt.toISOString(),\n JSON.stringify(payload),\n ],\n });\n}\n\nexport async function recordRun(\n success: boolean,\n mode: string,\n message: string,\n sourceStatus: unknown = null,\n): Promise {\n await sqlite.execute({\n sql:\n `INSERT INTO bh_runs(id, ran_at, mode, success, message, source_status_json) VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n crypto.randomUUID(),\n new Date().toISOString(),\n mode,\n success ? 1 : 0,\n message,\n sourceStatus ? JSON.stringify(sourceStatus) : null,\n ],\n });\n}\n\nexport async function listPredictions(\n waterYear: number,\n mode: string,\n): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT * FROM bh_predictions WHERE water_year=? AND mode=? ORDER BY question_number`,\n args: [waterYear, mode],\n });\n return result.rows as any;\n}\n\nexport async function recentRuns(limit = 10): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT ran_at, mode, success, message, source_status_json FROM bh_runs ORDER BY ran_at DESC LIMIT ?`,\n args: [limit],\n });\n return result.rows as any;\n}\n\nexport async function cachePublicFeed(\n key: string,\n value: unknown,\n): Promise {\n await sqlite.execute({\n sql:\n `INSERT INTO bh_public_cache(cache_key, value_json, updated_at) VALUES (?, ?, ?)\n ON CONFLICT(cache_key) DO UPDATE SET value_json=excluded.value_json, updated_at=excluded.updated_at`,\n args: [key, JSON.stringify(value), new Date().toISOString()],\n });\n}\n\nexport async function getPublicFeed(\n key: string,\n): Promise<{ value: any; updatedAt: string } | null> {\n const result = await sqlite.execute({\n sql: `SELECT value_json, updated_at FROM bh_public_cache WHERE cache_key=?`,\n args: [key],\n });\n const row: any = result.rows[0];\n return row\n ? {\n value: JSON.parse(String(row.value_json)),\n updatedAt: String(row.updated_at),\n }\n : null;\n}","deleted",false,"label",[14],"f84d0eeb-ceea-4772-8eb3-fa7a8cedbc13",["D",1788718960405],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","San Juan River seasonal prediction tracker","imageUrl","userId","66be13f3-dd08-4f8a-aca1-8697006c9a46","pinnedFileId","0210c8ee-aa20-11f1-a516-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","01a077f2-9ad7-764c-8f6b-583a36c43414","tursoName","f84d0eebceea47728eb3fa7a8cedbc13","tursoHostname","f84d0eebceea47728eb3fa7a8cedbc13-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789488693443],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_106":102},"org",{"_9":10,"_99":100,"_101":102,"_55":56,"_103":104,"_105":42},"branch",{"_35":89,"_11":90,"_22":28,"_91":45,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":12,"_98":10},"isOwned",268,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Pagosa Outside","avatar","https://lh3.googleusercontent.com/a/ACg8ocLkPUw3N28PFDWb5so2_Azzb6wOyGiti-gB56AMhHTn6R7IXJA6Ug=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_82":-5,"_140":141,"_142":143,"_144":145,"_146":42,"_147":42,"_148":42,"_149":32,"_150":-5,"_151":-7,"_152":-7,"_153":63,"_154":42,"_155":42,"_156":-5,"_157":42,"_158":42,"_159":42,"_160":-5},"ENV",{"_111":112,"_113":114,"_115":90,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","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","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]