[{"_1":2,"_169":-5,"_170":-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","a31a1550ad486cf340b818a53bc6ddc8c7db0295","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,"_148":149,"_166":167,"_168":-5},"handle","maxm","name","wideLib","path","main.tsx","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":107,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":-5,"_114":107,"_115":-5,"_116":-5,"_117":32,"_14":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":128,"_131":132,"_134":135,"_147":20},"id","5ffccbb6-252d-11f0-8a67-569c3dd06744","createdAt",["D",1745953726262],"deletedAt","legacyEnvVars",true,"privacy","public","description","imageUrl","418c1e39-eb48-4f2f-8c97-122ece94631c","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","c1e7642a-74cc-4d69-8652-20abbdeaa94e","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_129":130},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJnTEtkSHlOMWxWZm1GRHdKYUVTNklYM3FpdiJ9","org",{"_93":94,"_133":130,"_12":112},"avatar","branch",{"_136":137,"_95":43,"_101":138,"_139":102,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":96,"_146":94},"version",54,"5ffd17ba-252d-11f0-8a67-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":150,"_95":98,"_151":152,"_153":-5,"_154":138,"_155":137,"_156":107,"_103":157,"_136":158,"_159":160,"_161":162,"_163":20,"_164":-5,"_97":165},"61337b9c-252d-11f0-8a67-569c3dd06744","fileId","5fff8004-252d-11f0-8a67-569c3dd06744","parentId","branchId","branchVersion","latest",["D",1732735173240],53,"fileType","script","content","import { createClient, InsertResult } from \"npm:@clickhouse/client\";\nimport { z } from \"npm:zod\";\nimport { format, getUnixTime } from 'npm:date-fns';\n\nconst schemaVersion = 3;\n\nfunction _client() {\n return createClient({\n url: Deno.env.get(\"WIDE_CLICKHOUSE_URL\"),\n username: \"default\",\n password: Deno.env.get(\"WIDE_CLICKHOUSE_PW\"),\n });\n}\n\nexport async function runMigrations() {\n for (\n const migration of [\n `\nCREATE TABLE IF NOT EXISTS logs_${schemaVersion}\n(\n _tenantId LowCardinality(String) NOT NULL CODEC(ZSTD(1)),\n _timestamp DateTime64(3) NOT NULL CODEC(DoubleDelta, LZ4),\n _now DateTime64(3) NOT NULL CODEC(ZSTD(1)),\n _ttl DateTime NOT NULL,\n _traceId String CODEC(ZSTD(1)),\n _requestId String CODEC(ZSTD(1)),\n _spanId String CODEC(ZSTD(1)),\n _coldStart UInt32 Codec(T64, LZ4),\n _parentSpanId String CODEC(ZSTD(1)),\n _startTime DateTime64(3) CODEC(DoubleDelta, LZ4),\n _endTime DateTime64(3) CODEC(DoubleDelta, LZ4),\n _duration UInt32 Codec(T64, LZ4),\n _source String NOT NULL CODEC(ZSTD(1)),\n \\`string.names\\` Array(String) CODEC(ZSTD(2)),\n \\`string.values\\` Array(String) CODEC(ZSTD(2)),\n \\`number.names\\` Array(String) CODEC(ZSTD(2)),\n \\`number.values\\` Array(Float64) CODEC(ZSTD(2)),\n \\`boolean.names\\` Array(String) CODEC(ZSTD(2)),\n \\`boolean.values\\` Array(UInt8) CODEC(ZSTD(2)),\n INDEX idx_tenant_bloom _tenantId TYPE bloom_filter GRANULARITY 4\n) ENGINE = MergeTree()\n ORDER BY (_now)`,\n ]\n ) {\n try {\n await _client().command({\n query: migration,\n });\n } catch (e) {\n console.error(migration, e);\n throw new Error(`Migration failed: ${e}`);\n }\n }\n}\nexport function parseRows(rows: any[]) {\n const processedRows = rows.map(row => {\n const paths: {\n string: Record;\n number: Record;\n boolean: Record;\n } = {\n string: {},\n number: {},\n boolean: {},\n };\n\n function processObject(obj: any, currentPath = \"\") {\n for (const [key, value] of Object.entries(obj)) {\n const newPath = currentPath ? `${currentPath}.${key}` : key;\n\n if (Array.isArray(value)) {\n continue;\n }\n\n if (typeof value === \"object\" && value !== null) {\n processObject(value, newPath);\n } else {\n const type = typeof value;\n if (type === \"string\" || type === \"number\" || type === \"boolean\") {\n paths[type][newPath] = value as any;\n }\n }\n }\n }\n\n processObject(row);\n return paths;\n });\n\n return processedRows;\n}\n\n// const rows = parseRows([\n// {\n// \"name\": \"John\",\n// \"age\": 30,\n// \"isActive\": true,\n// \"contact\": {\n// \"email\": \"john@example.com\",\n// \"phone\": {\n// \"home\": \"555-1234\",\n// \"mobile\": \"555-5678\",\n// },\n// },\n// \"scores\": [90, 85, 88], // This array will be ignored\n// \"metadata\": {\n// \"lastLogin\": \"2023-01-01\",\n// \"visits\": 42,\n// \"preferences\": {\n// \"darkMode\": true,\n// \"notifications\": false,\n// },\n// },\n// },\n// {\n// \"name\": \"Jane\",\n// \"age\": 28,\n// \"isActive\": false,\n// \"contact\": {\n// \"email\": \"jane@example.com\",\n// \"phone\": {\n// \"home\": \"555-4321\",\n// \"work\": \"555-8765\",\n// },\n// },\n// \"tags\": [\"developer\", \"designer\"], // This array will be ignored\n// \"metadata\": {\n// \"lastLogin\": \"2023-01-02\",\n// \"visits\": 37,\n// \"preferences\": {\n// \"darkMode\": false,\n// \"notifications\": true,\n// },\n// },\n// },\n// ]);\n\nconst writeRowsToClickhouse = async (userId: string, rows: ReturnType): Promise => {\n // Write to ClickHouse\n const now = new Date();\n const ttl = new Date(now.getTime() + (1 * 24 * 60 * 60 * 1000)); // 1 day TTL\n\n const clickhouseRows = rows.map(row => ({\n _tenantId: userId,\n _timestamp: now,\n _now: now,\n _ttl: ttl,\n \"string.names\": Object.keys(row.string),\n \"string.values\": Object.values(row.string),\n \"number.names\": Object.keys(row.number),\n \"number.values\": Object.values(row.number),\n \"boolean.names\": Object.keys(row.boolean),\n \"boolean.values\": Object.values(row.boolean),\n }));\n\n return await _client().insert({\n table: `logs_${schemaVersion}`,\n values: clickhouseRows,\n format: \"JSONEachRow\",\n });\n};\n\n// await writeRowsToClickhouse(rows);\n\n/**\n * write!\n */\nexport async function write(userId: string, data: any[]) {\n return await writeRowsToClickhouse(userId, parseRows(data));\n}\n\n/**\n * fields types\n */\nexport enum FieldType {\n String = \"string\",\n Number = \"number\",\n Boolean = \"boolean\",\n}\n\n/**\n * values!\n */\nexport async function values(\n userId: string,\n { field_name, field_type }: { field_name: string; field_type: FieldType },\n) {\n if (field_type !== FieldType.Boolean && field_type !== FieldType.Number && field_type !== FieldType.String) {\n throw new Error(`Invalid field type: ${field_type}`);\n }\n const queryString = `SELECT DISTINCT\n \\`${field_type}.values\\`[arrayFirst(i ->\n \\`${field_type}.names\\`[i] = {field_name: String},\n arrayEnumerate(\\`${field_type}.names\\`)\n )] as value,\n count(*) as count\nFROM logs_${schemaVersion}\nWHERE _tenantId = {userId: String}\nAND arrayExists(i ->\n \\`${field_type}.names\\`[i] = {field_name: String},\n arrayEnumerate(\\`${field_type}.names\\`)\n)\nGROUP BY value\nORDER BY count DESC; `;\n console.log(queryString, { field_name, userId });\n const query = await _client().query({\n query: queryString,\n format: \"JSONEachRow\",\n query_params: { field_name, userId },\n });\n return ((await query.json()).map((r: any) => ({ ...r, count: Number(r.count) })) as {\n value: string;\n count: number;\n }[]);\n}\n\n/**\n * fields!\n */\nexport async function fields(userId: string, search: string = \"\") {\n return (await (await _client().query({\n query: `\nSELECT \n name AS field_name,\n field_type,\n count() AS count\nFROM \n(\n -- String fields\n SELECT \n arrayJoin(\\`string.names\\`) AS name,\n 'string' AS field_type\n FROM logs_${schemaVersion}\n WHERE name ILIKE {name: String}\n AND _tenantId = {userId: String}\n \n UNION ALL\n \n -- Number fields\n SELECT \n arrayJoin(\\`number.names\\`) AS name,\n 'number' AS field_type\n FROM logs_${schemaVersion}\n WHERE name ILIKE {name: String}\n AND _tenantId = {userId: String}\n \n UNION ALL\n \n -- Boolean fields\n SELECT \n arrayJoin(\\`boolean.names\\`) AS name,\n 'boolean' AS field_type\n FROM logs_${schemaVersion}\n WHERE name ILIKE {name: String}\n AND _tenantId = {userId: String}\n)\nGROUP BY field_name, field_type\n`,\n format: \"JSONEachRow\",\n query_params: { name: search, userId },\n })).json() as any[]).map(r => ({ ...r, count: Number(r.count) })) as {\n field_name: string;\n field_type: FieldType;\n count: number;\n }[];\n}\n\nexport const StringOperator = z.enum([\"equals\", \"contains\", \"startsWith\", \"endsWith\"]);\nexport const NumberOperator = z.enum([\"equals\", \"greaterThan\", \"lessThan\", \"greaterThanOrEqual\", \"lessThanOrEqual\"]);\nexport const BooleanOperator = z.enum([\"equals\"]);\n\nexport const SearchFilter = z.union([\n z.object({\n fieldName: z.string(),\n operator: StringOperator,\n value: z.string(),\n }),\n z.object({\n fieldName: z.string(),\n operator: NumberOperator,\n value: z.number(),\n }),\n z.object({\n fieldName: z.string(),\n operator: BooleanOperator,\n value: z.boolean(),\n }),\n]);\n\nexport type TStringOperator = z.infer;\nexport type TNumberOperator = z.infer;\nexport type TBooleanOperator = z.infer;\nexport type TSearchFilter = z.infer;\n\n/**\n * Get the ClickHouse comparison expression for the given operator\n */\nfunction getComparisonExpression(\n operator: TStringOperator | TNumberOperator | TBooleanOperator,\n valueRef: string,\n): string {\n switch (operator) {\n // String operators\n case \"equals\":\n return `= ${valueRef}`;\n case \"contains\":\n return `LIKE concat('%', ${valueRef}, '%')`;\n case \"startsWith\":\n return `LIKE concat(${valueRef}, '%')`;\n case \"endsWith\":\n return `LIKE concat('%', ${valueRef})`;\n // Number operators\n case \"greaterThan\":\n return `> ${valueRef}`;\n case \"lessThan\":\n return `< ${valueRef}`;\n case \"greaterThanOrEqual\":\n return `>= ${valueRef}`;\n case \"lessThanOrEqual\":\n return `<= ${valueRef}`;\n default:\n throw new Error(`Unsupported operator: ${operator}`);\n }\n}\n\n/**\n * Enhanced search function supporting multiple conditions with different operators\n */\nexport async function search(userId: string, {\n filters,\n start,\n end,\n limit = 100,\n}: {\n filters: TSearchFilter[];\n start: Date | string;\n end: Date | string;\n limit?: number;\n}) {\n // Build the WHERE clause for each condition\n const whereConditions = filters.map((filter, index) => {\n let typeCondition: string;\n let paramType: string;\n\n if (typeof filter.value === \"string\") {\n typeCondition = \"string\";\n paramType = \"String\";\n } else if (typeof filter.value === \"number\") {\n typeCondition = \"number\";\n paramType = \"Float64\";\n } else if (typeof filter.value === \"boolean\") {\n typeCondition = \"boolean\";\n paramType = \"UInt8\";\n } else {\n throw new Error(`Unsupported value type: ${typeof (filter as any).value}`);\n }\n\n const comparisonExpr = getComparisonExpression(\n filter.operator,\n `{value${index}: ${paramType}}`,\n );\n\n return `arrayExists(i ->\n \\`${typeCondition}.names\\`[i] = {field_name${index}: String}\n AND \\`${typeCondition}.values\\`[i] ${comparisonExpr},\n arrayEnumerate(\\`${typeCondition}.names\\`)\n )`;\n });\n\n // Build the query\n const queryString = `\n SELECT\n \\`string.names\\`, \\`string.values\\`,\n \\`number.names\\`, \\`number.values\\`,\n \\`boolean.names\\`, \\`boolean.values\\`\n FROM logs_${schemaVersion}\n WHERE _tenantId = {userId: String}\n ${whereConditions.length > 0 ? `AND ${whereConditions.join(\" AND \")}` : \"\"}\n AND _timestamp >= {start: DateTime64}\n AND _timestamp < {end: DateTime64}\n limit {limit: UInt64}\n `;\n // Build query parameters\n const queryParams: Record = { userId };\n filters.forEach((filter, index) => {\n queryParams[`field_name${index}`] = filter.fieldName;\n queryParams[`value${index}`] = typeof filter.value === \"boolean\"\n ? Number(filter.value)\n : filter.value;\n });\n\n const query = await _client().query({\n query: queryString,\n format: \"JSONEachRow\",\n query_params: {\n ...queryParams,\n limit,\n // Best effort does not seem to work so we do this format dance, generally the api \n // layer validates iso date formats so we are somewhat insulated.\n start: formatDate(typeof start === \"string\" ? new Date(start) : start),\n end: formatDate(typeof end === \"string\" ? new Date(end) : end),\n },\n clickhouse_settings: {\n date_time_input_format: \"best_effort\",\n date_time_output_format: \"iso\",\n },\n });\n\n const results = await query.json() as LogRow[];\n return reconstructObjects(results);\n}\n\n\nfunction formatDate(date: Date) {\n return String(getUnixTime(date) + (date.getMilliseconds() / 1000))\n}\n\ninterface LogRow {\n \"string.names\": string[];\n \"string.values\": string[];\n \"number.names\": string[];\n \"number.values\": number[];\n \"boolean.names\": string[];\n \"boolean.values\": boolean[];\n}\n\nfunction reconstructObjects(\n results: LogRow[],\n) {\n // Reconstruct the original objects from the columnar data\n return results.map((row) => {\n const reconstructed: Record = {};\n const setNestedValue = (obj: any, path: string, value: any) => {\n const parts = path.split(\".\");\n let current = obj;\n for (let i = 0; i < parts.length - 1; i++) {\n if (!(parts[i] in current)) {\n current[parts[i]] = {};\n }\n current = current[parts[i]];\n }\n current[parts[parts.length - 1]] = value;\n };\n\n // Process string fields\n for (let i = 0; i < row[\"string.names\"].length; i++) \n setNestedValue(reconstructed, row[\"string.names\"][i], row[\"string.values\"][i]);\n // Process number fields\n for (let i = 0; i < row[\"number.names\"].length; i++) \n setNestedValue(reconstructed, row[\"number.names\"][i], row[\"number.values\"][i]);\n // Process boolean fields\n for (let i = 0; i < row[\"boolean.names\"].length; i++) \n setNestedValue(reconstructed, row[\"boolean.names\"][i], Boolean(row[\"boolean.values\"][i]));\n\n return reconstructed;\n });\n}","deleted","label",[98],"permalink","https://www.val.town/x/maxm/wideLib/code/main.tsx","extension","actionData","errors"]