[{"_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","backup2-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/backup2-storage.ts","extension","id","df81ab01-bb46-4f81-9cee-81ddf782b407","fileId","e05769d6-ab9d-11f1-8b5e-1607ee4eb77e","parentId","branchId","70817041-1dfa-4778-885e-ac83be02b1d0","branchVersion",142,"latest",true,"createdAt",["D",1788883399233],"version",3,"fileType","script","content","import { CONFIG } from \"./config.ts\";\nimport {\n addDays,\n firstMondayOfSeptember,\n isoDay,\n mountainCalendarDate,\n waterYear,\n} from \"./dates.ts\";\nimport type { BasinSnapshot, FlowPoint, Milestones, Snotel } from \"./types.ts\";\n\nconst average = (values: unknown[]): number | null => {\n const valid = values.map(Number).filter(Number.isFinite);\n return valid.length\n ? valid.reduce((sum, value) => sum + value, 0) / valid.length\n : null;\n};\n\nasync function fetchJson(url: string, timeout = 20_000): Promise {\n const controller = new AbortController();\n const timer = setTimeout(() => controller.abort(), timeout);\n try {\n const response = await fetch(url, {\n signal: controller.signal,\n headers: { Accept: \"application/json\" },\n });\n if (!response.ok) {\n const parsed = new URL(url);\n throw new Error(\n `${parsed.hostname}${parsed.pathname} returned ${response.status}`,\n );\n }\n return await response.json();\n } finally {\n clearTimeout(timer);\n }\n}\n\nfunction parseUsgs(payload: any, cutoffAt: Date): FlowPoint[] {\n const series = payload?.value?.timeSeries?.[0]?.values?.[0]?.value || [];\n return series.map((item: any) => ({\n date: new Date(item.dateTime),\n value: Number(item.value),\n }))\n .filter((item: FlowPoint) =>\n !Number.isNaN(item.date.getTime()) && item.date <= cutoffAt &&\n Number.isFinite(item.value) && item.value >= 0\n )\n .sort((a: FlowPoint, b: FlowPoint) => a.date.getTime() - b.date.getTime());\n}\n\nfunction parseSnotel(payload: any, cutoffAt: Date): Snotel {\n const output: Snotel = {};\n for (const station of payload || []) {\n const stationId = String(station.stationTriplet);\n output[stationId] ||= {};\n for (const series of station.data || []) {\n const code = series.stationElement?.elementCode;\n if (!code) continue;\n output[stationId][code] = (series.values || []).map((item: any) => ({\n date: new Date(`${item.date}T12:00:00Z`),\n value: Number(item.value),\n median: item.median === null || item.median === undefined\n ? null\n : Number(item.median),\n })).filter((item: any) =>\n !Number.isNaN(item.date.getTime()) && item.date <= cutoffAt &&\n Number.isFinite(item.value)\n );\n }\n }\n return output;\n}\n\nfunction latestAtOrBefore(\n points: T[],\n date: Date,\n): T | null {\n const eligible = (points || []).filter((point) => point.date <= date);\n return eligible.length ? eligible[eligible.length - 1] : null;\n}\n\nfunction stationAverage(\n snotel: Snotel,\n element: string,\n date: Date,\n field: \"value\" | \"median\" = \"value\",\n): number | null {\n return average(\n Object.values(snotel).map((station) =>\n (latestAtOrBefore(station[element] || [], date) as any)?.[field]\n ),\n );\n}\n\nfunction waterYearDay(date: Date): number {\n const year = date.getUTCMonth() >= 9\n ? date.getUTCFullYear()\n : date.getUTCFullYear() - 1;\n return Math.round(\n (Date.UTC(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate()) -\n Date.UTC(year, 9, 1)) / 86_400_000,\n );\n}\n\nfunction buildFeatures(\n flowDaily: FlowPoint[],\n currentFlow: number,\n snotel: Snotel,\n now: Date,\n): Record {\n const date7 = addDays(now, -7);\n const date30 = addDays(now, -30);\n const recent7 = flowDaily.filter((point) =>\n point.date >= date7 && point.date <= now\n ).map((point) => point.value);\n const recent30 = flowDaily.filter((point) =>\n point.date >= date30 && point.date <= now\n ).map((point) => point.value);\n const flow7Ago = latestAtOrBefore(flowDaily, date7)?.value ?? currentFlow;\n const flowMean7 = average(recent7) ?? currentFlow;\n const flowMean30 = average(recent30) ?? flowMean7;\n const swe = stationAverage(snotel, \"WTEQ\", now) ?? 0;\n const sweMedian = stationAverage(snotel, \"WTEQ\", now, \"median\") ?? 0;\n const swe7Ago = stationAverage(snotel, \"WTEQ\", date7) ?? swe;\n const sweRatio = sweMedian > 0.05\n ? Math.min(3, Math.max(0, swe / sweMedian))\n : swe <= 0.05\n ? 0\n : 3;\n const stationIds = Object.keys(snotel);\n const temperatures: number[] = [];\n for (const id of stationIds) {\n for (const point of snotel[id].TAVG || []) {\n if (point.date >= date7 && point.date <= now) {\n temperatures.push(point.value);\n }\n }\n }\n const temp7 = average(temperatures) ?? 32;\n const precipitationChange = (startDate: Date) =>\n average(stationIds.map((id) => {\n const current = latestAtOrBefore(snotel[id].PREC || [], now)?.value;\n const earlier = latestAtOrBefore(snotel[id].PREC || [], startDate)?.value;\n return Number.isFinite(current) && Number.isFinite(earlier)\n ? Math.max(0, Number(current) - Number(earlier))\n : null;\n })) ?? 0;\n const precip7 = precipitationChange(date7);\n const precip30 = precipitationChange(date30);\n const snowPresent = swe > 0.05 ? 1 : 0;\n const angle = 2 * Math.PI * waterYearDay(now) / 365.25;\n return {\n log_flow: Math.log1p(currentFlow),\n log_flow_mean_7d: Math.log1p(flowMean7),\n log_flow_mean_30d: Math.log1p(flowMean30),\n relative_flow_change_7d: (currentFlow - flow7Ago) /\n (Math.abs(flowMean7) + 30),\n swe_in: swe,\n swe_ratio: sweRatio,\n swe_change_7d: swe - swe7Ago,\n temperature_scaled: (temp7 - 32) / 20,\n prec_total_7d: precip7,\n prec_total_30d: precip30,\n snow_present: snowPresent,\n season_sin: Math.sin(angle),\n season_cos: Math.cos(angle),\n season_sin_2: Math.sin(2 * angle),\n season_cos_2: Math.cos(2 * angle),\n melt_pressure: swe * Math.max(temp7 - 32, 0) / 100,\n rain_without_snow: precip7 * (1 - snowPresent),\n current_flow: currentFlow,\n swe_median: sweMedian,\n temp_7d: temp7,\n precip_7d: precip7,\n };\n}\n\nasync function fetchInstantaneousPeak(\n dailyFlow: FlowPoint[],\n runoffYear: number,\n cutoffAt: Date,\n): Promise {\n const start = new Date(Date.UTC(runoffYear, 3, 1));\n const end = new Date(\n Math.min(cutoffAt.getTime(), Date.UTC(runoffYear, 6, 31, 23, 59)),\n );\n const candidate = dailyFlow.filter((item) =>\n item.date >= start && item.date <= end\n )\n .reduce(\n (best, item) => !best || item.value > best.value ? item : best,\n null,\n );\n if (!candidate) return null;\n const url =\n `https://waterservices.usgs.gov/nwis/iv/?format=json&sites=${CONFIG.usgsSite}&startDT=${\n isoDay(addDays(candidate.date, -1))\n }&endDT=${\n isoDay(addDays(candidate.date, 2))\n }¶meterCd=00060&siteStatus=all`;\n const values = parseUsgs(await fetchJson(url), cutoffAt);\n return values.reduce(\n (best, item) => !best || item.value > best.value ? item : best,\n null,\n );\n}\n\nfunction findTubingOpening(\n series: FlowPoint[],\n after: Date | null,\n): Date | null {\n if (!after) return null;\n const ordered = series.filter((item) => item.date > after).sort((a, b) =>\n a.date.getTime() - b.date.getTime()\n );\n for (let index = 0; index < ordered.length; index += 1) {\n const endPoint = ordered[index];\n const startTime = endPoint.date.getTime() - 24 * 3_600_000;\n const window = ordered.filter((item) =>\n item.date.getTime() >= startTime && item.date <= endPoint.date\n );\n if (window.length < 4) continue;\n if (\n window.at(-1)!.date.getTime() - window[0].date.getTime() < 23 * 3_600_000\n ) continue;\n if (window.every((item) => item.value >= 30 && item.value < 400)) {\n return endPoint.date;\n }\n }\n return null;\n}\n\nfunction deriveMilestones(\n dailyFlow: FlowPoint[],\n instantaneous: FlowPoint[],\n snotel: Snotel,\n current: FlowPoint,\n peakInstant: FlowPoint | null,\n): Milestones {\n const runoffYear = waterYear(current.date);\n const waterYearStart = new Date(Date.UTC(runoffYear - 1, 9, 1, 12));\n const springStart = new Date(Date.UTC(runoffYear, 2, 1, 12));\n const peakWindowStart = new Date(Date.UTC(runoffYear, 3, 1, 12));\n const peakWindowEnd = new Date(Date.UTC(runoffYear, 6, 31, 23, 59));\n const recreationStart = new Date(Date.UTC(runoffYear, 4, 1, 12));\n const laborDay = firstMondayOfSeptember(runoffYear);\n laborDay.setUTCHours(23, 59, 59, 999);\n const waterYearDaily = dailyFlow.filter((item) =>\n item.date >= waterYearStart && item.date <= current.date\n );\n const springDaily = waterYearDaily.filter((item) => item.date >= springStart);\n const first400 = springDaily.find((item) => item.value >= 400) || null;\n const peakDaily = springDaily.filter((item) =>\n item.date >= peakWindowStart && item.date <= peakWindowEnd\n )\n .reduce(\n (best, item) => !best || item.value > best.value ? item : best,\n null,\n );\n const daysSincePeak = peakDaily\n ? Math.floor(\n (current.date.getTime() - peakDaily.date.getTime()) / 86_400_000,\n )\n : 0;\n const dropFromPeak = peakDaily?.value\n ? 1 - current.value / peakDaily.value\n : 0;\n const peakFlowLikelyPassed = Boolean(\n peakDaily && daysSincePeak >= 6 && dropFromPeak >= 0.18,\n );\n let tubingForecastTrigger: FlowPoint | null = null;\n if (peakDaily) {\n const peakIndex = springDaily.findIndex((item) =>\n isoDay(item.date) === isoDay(peakDaily.date) &&\n item.value === peakDaily.value\n );\n for (\n let index = Math.max(2, peakIndex + 1);\n index < springDaily.length;\n index += 1\n ) {\n const item = springDaily[index];\n const fallingThreeDay = item.value < springDaily[index - 2].value;\n const eligible = peakDaily.value >= 800\n ? item.value < 800\n : peakFlowLikelyPassed;\n if (eligible && fallingThreeDay) {\n tubingForecastTrigger = item;\n break;\n }\n }\n }\n const dailyTubingFallback = peakDaily\n ? springDaily.find((item) =>\n item.date > peakDaily.date && item.value >= 30 && item.value < 400\n )?.date || null\n : null;\n const tubingOpening =\n findTubingOpening(instantaneous, peakDaily?.date || null) ||\n dailyTubingFallback;\n const seasonDaily = waterYearDaily.filter((item) =>\n item.date >= recreationStart && item.date <= laborDay &&\n item.date <= current.date\n );\n const counts = {\n p300: seasonDaily.filter((item) => item.value >= 300).length,\n p600: seasonDaily.filter((item) => item.value >= 600).length,\n p800: seasonDaily.filter((item) => item.value >= 800).length,\n tubing:\n seasonDaily.filter((item) => item.value >= 30 && item.value < 400).length,\n };\n const strengthDays = waterYearDaily.filter((item) =>\n item.date >= recreationStart &&\n item.date <= new Date(Date.UTC(runoffYear, 5, 30, 23, 59))\n );\n const strength = average(strengthDays.map((item) => item.value));\n const snowDates = [\n ...new Set(\n Object.values(snotel).flatMap((station) =>\n (station.WTEQ || []).map((item) => isoDay(item.date))\n ),\n ),\n ]\n .map((value) => new Date(`${value}T12:00:00Z`)).filter((date) =>\n date >= waterYearStart && date <= current.date\n ).sort((a, b) => a.getTime() - b.getTime());\n const snowSeries = snowDates.map((date) => ({\n date,\n value: stationAverage(snotel, \"WTEQ\", date),\n }))\n .filter((item): item is { date: Date; value: number } =>\n Number.isFinite(item.value)\n );\n let persistentSnow = null;\n for (let index = 0; index <= snowSeries.length - 7; index += 1) {\n const window = snowSeries.slice(index, index + 7);\n if (window[0].value >= 1 && window.every((item) => item.value >= 0.5)) {\n persistentSnow = window[0];\n break;\n }\n }\n const peakSwe = snowSeries.reduce<{ date: Date; value: number } | null>(\n (best, item) => !best || item.value > best.value ? item : best,\n null,\n );\n const daysSincePeakSwe = peakSwe\n ? Math.floor((current.date.getTime() - peakSwe.date.getTime()) / 86_400_000)\n : 0;\n const currentSwe = snowSeries.at(-1)?.value || 0;\n const peakSweLikelyPassed = Boolean(\n peakSwe && peakSwe.value >= 1 && daysSincePeakSwe >= 14 &&\n currentSwe <= peakSwe.value * 0.7,\n );\n return {\n runoffYear,\n laborDay,\n first400,\n peakDaily,\n peakInstant,\n peakFlowLikelyPassed,\n tubingForecastTrigger,\n tubingOpening,\n counts,\n strength,\n persistentSnow,\n peakSwe,\n peakSweLikelyPassed,\n };\n}\n\nexport async function fetchBasinSnapshot(\n cutoffAt = new Date(),\n): Promise {\n const runoffYear = waterYear(cutoffAt);\n // Models use 7- and 30-day history. Starting 45 days before the water year\n // also prevents the October 1 lock from running with an empty daily series.\n const waterYearStart = new Date(Date.UTC(runoffYear - 1, 9, 1, 12));\n const start = isoDay(addDays(waterYearStart, -45));\n const end = isoDay(cutoffAt);\n const dailyUrl =\n `https://waterservices.usgs.gov/nwis/dv/?format=json&sites=${CONFIG.usgsSite}&startDT=${start}&endDT=${end}¶meterCd=00060&statCd=00003&siteStatus=all`;\n const instantUrl =\n `https://waterservices.usgs.gov/nwis/iv/?format=json&sites=${CONFIG.usgsSite}&period=P60D¶meterCd=00060&siteStatus=all`;\n const stationParam = encodeURIComponent(CONFIG.snotelStations.join(\",\"));\n const snotelUrl =\n `https://wcc.sc.egov.usda.gov/awdbRestApi/services/v1/data?stationTriplets=${stationParam}&elements=WTEQ%2CSNWD%2CPREC%2CTAVG&duration=DAILY&beginDate=${start}&endDate=${end}&periodRef=END¢ralTendencyType=MEDIAN&returnFlags=false&returnOriginalValues=false&returnSuspectData=false`;\n const [dailyPayload, instantPayload, snowPayload] = await Promise.all([\n fetchJson(dailyUrl),\n fetchJson(instantUrl),\n fetchJson(snotelUrl),\n ]);\n const dailyFlow = parseUsgs(dailyPayload, cutoffAt);\n const instantaneous = parseUsgs(instantPayload, cutoffAt);\n if (!dailyFlow.length || !instantaneous.length) {\n throw new Error(\"The Pagosa USGS gauge did not return enough data\");\n }\n const snotel = parseSnotel(snowPayload, cutoffAt);\n if (!Object.keys(snotel).length) {\n throw new Error(\"The headwater SNOTEL stations did not return enough data\");\n }\n const latestSnotelAt = Object.values(snotel)\n .flatMap((station) => Object.values(station).flat())\n .reduce(\n (latest, point) => !latest || point.date > latest ? point.date : latest,\n null,\n );\n const current = instantaneous.at(-1)!;\n const features = buildFeatures(\n dailyFlow,\n current.value,\n snotel,\n current.date,\n );\n const peakInstant = await fetchInstantaneousPeak(\n dailyFlow,\n runoffYear,\n cutoffAt,\n ).catch(() => null);\n const milestones = deriveMilestones(\n dailyFlow,\n instantaneous,\n snotel,\n current,\n peakInstant,\n );\n return {\n capturedAt: new Date(),\n cutoffAt,\n current,\n instantaneous,\n dailyFlow,\n snotel,\n features,\n milestones,\n sourceStatus: {\n usgs_site: CONFIG.usgsSite,\n usgs_latest_at: current.date.toISOString(),\n usgs_daily_points: dailyFlow.length,\n usgs_instant_points: instantaneous.length,\n snotel_stations: Object.keys(snotel),\n snotel_latest_day: latestSnotelAt ? isoDay(latestSnotelAt) : null,\n },\n };\n}\n// 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"]