[{"_1":2,"_176":-5,"_177":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","5f8f00a127b1349c32de089cedf4e10bc7855654","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_151":152,"_173":174,"_175":-5},"handle","pinsar","name","feistyAmberRhinoceros","path","backend/storage/database.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":-5,"_118":119,"_120":32,"_14":121,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":131,"_134":135,"_137":138,"_150":20},"id","2149c16a-277d-11f0-b46e-569c3dd06744","createdAt",["D",1746207883134],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","851f6760-f7af-11ef-9322-0fd6f1c36e70","pinnedFileId","214e03d8-277d-11f0-b46e-569c3dd06744","pinPreview",true,"pinMigrated","screenshotVersion",19,"likeCount","094b6920-183c-466c-985a-fe1516b5d507","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ydG1LWVR3QzZpNUhHQkNmbzF5VlRvOGZweUQifQ","org",{"_94":95,"_136":133,"_12":112},"avatar","branch",{"_139":140,"_96":44,"_102":141,"_142":103,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":-5,"_148":97,"_149":95},"version",27,"214a9ba8-277d-11f0-b46e-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":153,"_96":154,"_155":156,"_157":158,"_159":141,"_160":161,"_162":116,"_104":163,"_139":32,"_164":165,"_166":167,"_168":20,"_169":-5,"_98":170},"50006f38-3a04-11f0-b327-9e149126039e","database.ts","fileId","50006fe2-3a04-11f0-b327-9e149126039e","parentId","da49c6e8-344c-4416-ba9f-7aba56ce0c51","branchId","branchVersion",12,"latest",["D",1748245064492],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Database schema for time-series event storage\nconst EVENTS_TABLE = 'realtime_events_v1';\nconst METRICS_TABLE = 'system_metrics_v1';\nconst AGGREGATES_TABLE = 'event_aggregates_v1';\n\nexport async function initializeDatabase() {\n // Events table - optimized for time-series data\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${EVENTS_TABLE} (\n id TEXT PRIMARY KEY,\n timestamp INTEGER NOT NULL,\n type TEXT NOT NULL,\n source TEXT NOT NULL,\n data TEXT NOT NULL,\n processing_time_ms INTEGER,\n partition_key TEXT,\n created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000)\n )\n `);\n\n // Create index for time-based queries\n await sqlite.execute(`\n CREATE INDEX IF NOT EXISTS idx_events_timestamp \n ON ${EVENTS_TABLE}(timestamp DESC)\n `);\n\n await sqlite.execute(`\n CREATE INDEX IF NOT EXISTS idx_events_type_timestamp \n ON ${EVENTS_TABLE}(type, timestamp DESC)\n `);\n\n // Metrics table for system performance tracking\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${METRICS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n timestamp INTEGER NOT NULL,\n metric_name TEXT NOT NULL,\n metric_value REAL NOT NULL,\n tags TEXT,\n created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000)\n )\n `);\n\n await sqlite.execute(`\n CREATE INDEX IF NOT EXISTS idx_metrics_timestamp \n ON ${METRICS_TABLE}(timestamp DESC)\n `);\n\n // Aggregates table for pre-computed statistics\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${AGGREGATES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n window_start INTEGER NOT NULL,\n window_end INTEGER NOT NULL,\n event_type TEXT,\n event_count INTEGER DEFAULT 0,\n avg_processing_time REAL DEFAULT 0,\n min_processing_time INTEGER DEFAULT 0,\n max_processing_time INTEGER DEFAULT 0,\n p95_processing_time REAL DEFAULT 0,\n p99_processing_time REAL DEFAULT 0,\n error_count INTEGER DEFAULT 0,\n throughput_per_second REAL DEFAULT 0,\n created_at INTEGER DEFAULT (strftime('%s', 'now') * 1000)\n )\n `);\n\n await sqlite.execute(`\n CREATE INDEX IF NOT EXISTS idx_aggregates_window \n ON ${AGGREGATES_TABLE}(window_start DESC, window_end DESC)\n `);\n\n console.log(\"Database initialized successfully\");\n}\n\nexport interface Event {\n id: string;\n timestamp: number;\n type: string;\n source: string;\n data: any;\n processing_time_ms?: number;\n partition_key?: string;\n}\n\nexport interface Metric {\n timestamp: number;\n metric_name: string;\n metric_value: number;\n tags?: Record;\n}\n\nexport interface Aggregate {\n window_start: number;\n window_end: number;\n event_type?: string;\n event_count: number;\n avg_processing_time: number;\n min_processing_time: number;\n max_processing_time: number;\n p95_processing_time: number;\n p99_processing_time: number;\n error_count: number;\n throughput_per_second: number;\n}\n\nexport class EventStorage {\n async storeEvent(event: Event): Promise {\n await sqlite.execute(\n `INSERT INTO ${EVENTS_TABLE} \n (id, timestamp, type, source, data, processing_time_ms, partition_key) \n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n [\n event.id,\n event.timestamp,\n event.type,\n event.source,\n JSON.stringify(event.data),\n event.processing_time_ms || null,\n event.partition_key || null\n ]\n );\n }\n\n async storeBatch(events: Event[]): Promise {\n // Use transaction for batch insert\n const statements = events.map(event => ({\n sql: `INSERT INTO ${EVENTS_TABLE} \n (id, timestamp, type, source, data, processing_time_ms, partition_key) \n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n args: [\n event.id,\n event.timestamp,\n event.type,\n event.source,\n JSON.stringify(event.data),\n event.processing_time_ms || null,\n event.partition_key || null\n ]\n }));\n\n // Execute all statements in a batch\n for (const stmt of statements) {\n await sqlite.execute(stmt.sql, stmt.args);\n }\n }\n\n async getRecentEvents(limit: number = 100): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${EVENTS_TABLE} \n ORDER BY timestamp DESC \n LIMIT ?`,\n [limit]\n );\n\n return result.rows.map(row => ({\n id: row[0] as string,\n timestamp: row[1] as number,\n type: row[2] as string,\n source: row[3] as string,\n data: JSON.parse(row[4] as string),\n processing_time_ms: row[5] as number,\n partition_key: row[6] as string\n }));\n }\n\n async getEventsByTimeRange(startTime: number, endTime: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${EVENTS_TABLE} \n WHERE timestamp BETWEEN ? AND ? \n ORDER BY timestamp DESC`,\n [startTime, endTime]\n );\n\n return result.rows.map(row => ({\n id: row[0] as string,\n timestamp: row[1] as number,\n type: row[2] as string,\n source: row[3] as string,\n data: JSON.parse(row[4] as string),\n processing_time_ms: row[5] as number,\n partition_key: row[6] as string\n }));\n }\n\n async getEventCountByType(hours: number = 1): Promise> {\n const startTime = Date.now() - (hours * 60 * 60 * 1000);\n \n const result = await sqlite.execute(\n `SELECT type, COUNT(*) as count \n FROM ${EVENTS_TABLE} \n WHERE timestamp >= ? \n GROUP BY type`,\n [startTime]\n );\n\n const counts: Record = {};\n result.rows.forEach(row => {\n counts[row[0] as string] = row[1] as number;\n });\n\n return counts;\n }\n}\n\nexport class MetricsStorage {\n async storeMetric(metric: Metric): Promise {\n await sqlite.execute(\n `INSERT INTO ${METRICS_TABLE} (timestamp, metric_name, metric_value, tags) \n VALUES (?, ?, ?, ?)`,\n [\n metric.timestamp,\n metric.metric_name,\n metric.metric_value,\n metric.tags ? JSON.stringify(metric.tags) : null\n ]\n );\n }\n\n async getMetricHistory(metricName: string, hours: number = 1): Promise {\n const startTime = Date.now() - (hours * 60 * 60 * 1000);\n \n const result = await sqlite.execute(\n `SELECT timestamp, metric_name, metric_value, tags \n FROM ${METRICS_TABLE} \n WHERE metric_name = ? AND timestamp >= ? \n ORDER BY timestamp DESC`,\n [metricName, startTime]\n );\n\n return result.rows.map(row => ({\n timestamp: row[0] as number,\n metric_name: row[1] as string,\n metric_value: row[2] as number,\n tags: row[3] ? JSON.parse(row[3] as string) : undefined\n }));\n }\n\n async getLatestMetrics(): Promise> {\n const result = await sqlite.execute(\n `SELECT DISTINCT metric_name, \n FIRST_VALUE(metric_value) OVER (\n PARTITION BY metric_name \n ORDER BY timestamp DESC\n ) as latest_value\n FROM ${METRICS_TABLE} \n WHERE timestamp >= ?`,\n [Date.now() - 60000] // Last minute\n );\n\n const metrics: Record = {};\n result.rows.forEach(row => {\n metrics[row[0] as string] = row[1] as number;\n });\n\n return metrics;\n }\n}\n\nexport class AggregateStorage {\n async storeAggregate(aggregate: Aggregate): Promise {\n await sqlite.execute(\n `INSERT INTO ${AGGREGATES_TABLE} \n (window_start, window_end, event_type, event_count, avg_processing_time, \n min_processing_time, max_processing_time, p95_processing_time, \n p99_processing_time, error_count, throughput_per_second) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n aggregate.window_start,\n aggregate.window_end,\n aggregate.event_type || null,\n aggregate.event_count,\n aggregate.avg_processing_time,\n aggregate.min_processing_time,\n aggregate.max_processing_time,\n aggregate.p95_processing_time,\n aggregate.p99_processing_time,\n aggregate.error_count,\n aggregate.throughput_per_second\n ]\n );\n }\n\n async getAggregateHistory(hours: number = 1): Promise {\n const startTime = Date.now() - (hours * 60 * 60 * 1000);\n \n const result = await sqlite.execute(\n `SELECT window_start, window_end, event_type, event_count, avg_processing_time,\n min_processing_time, max_processing_time, p95_processing_time,\n p99_processing_time, error_count, throughput_per_second\n FROM ${AGGREGATES_TABLE} \n WHERE window_start >= ? \n ORDER BY window_start DESC`,\n [startTime]\n );\n\n return result.rows.map(row => ({\n window_start: row[0] as number,\n window_end: row[1] as number,\n event_type: row[2] as string,\n event_count: row[3] as number,\n avg_processing_time: row[4] as number,\n min_processing_time: row[5] as number,\n max_processing_time: row[6] as number,\n p95_processing_time: row[7] as number,\n p99_processing_time: row[8] as number,\n error_count: row[9] as number,\n throughput_per_second: row[10] as number\n }));\n }\n}","deleted","label",[171,172,154],"backend","storage","permalink","https://www.val.town/x/pinsar/feistyAmberRhinoceros/code/backend/storage/database.ts","extension","actionData","errors"]