[{"_1":2,"_373":-5,"_374":-5},"loaderData",{"_3":4,"_91":92,"_360":361,"_372":-5},"root",{"_5":6,"_32":33,"_58":59,"_89":90},"ctx",{"_7":8,"_18":19,"_20":-5,"_21":22,"_23":24,"_25":26,"_27":26,"_28":29,"_30":31},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5},"sessionClaims","sessionId","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","environmentVariableKeys",[],"user","preferences_keybindings","default","preferences_theme","light","preferences_fixers",false,"preferences_copilot","preferences_model","claude-3-5-haiku-20241022","notification_count",0,"ENV",{"_34":35,"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","89d401ac5276a0a4fea3e2c212bc6091a552bac7","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","API_URL","https://api.val.town","WORKER_FINGERPRINT","d0746fd0512e0614e3f717607f0da4cd","clerkState",{"_60":61},"__internal_clerk_state",{"_62":63,"_64":43,"_65":66,"_67":66,"_68":26,"_69":66,"_70":66,"_71":66,"_72":66,"_73":66,"_74":66,"_75":66,"_76":66,"_77":78,"_87":26,"_88":26},"__clerk_ssr_state",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__signInForceRedirectUrl","__signUpForceRedirectUrl","__signInFallbackRedirectUrl","__signUpFallbackRedirectUrl","__clerk_debug",{"_79":26,"_80":66,"_81":82,"_83":66,"_84":43,"_85":26,"_86":66},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","remixVersion",2,"routes/_app.v.$handle.$name",{"_93":94,"_95":96,"_94":97,"_355":-5,"_356":-5,"_357":358,"_129":130,"_137":138,"_100":101,"_98":99,"_118":-7,"_50":51,"_359":26},"type","val","author","418c1e39-eb48-4f2f-8c97-122ece94631c",{"_98":99,"_95":96,"_100":101,"_102":103,"_104":-5,"_105":106,"_107":66,"_108":109,"_110":111,"_112":26,"_113":111,"_114":115,"_116":117,"_118":119,"_120":26,"_121":31,"_122":31,"_123":90,"_124":31,"_125":31,"_126":31,"_127":-5,"_20":128,"_133":-5,"_134":-7,"_135":136,"_137":138,"_139":140},"name","wideLib","valNameId","95367274-a9d5-11ef-97a8-e6cdfca9ef9f","parentFolderId","25db2002-a929-11ef-8ad4-e6cdfca9ef9f","isOwned","privacy","public","readme","valType","script","currentVersion","886be71e-acf4-11ef-a6ac-e6cdfca9ef9f","legacyPrivate","id","code","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}","createdAt",["D",1732735173240],"version",53,"currentUserHasLiked","likeCount","forkCount","referenceCount","dependencyCount","commentCount","prCount","mergedPr",{"_129":130,"_131":132},"handle","maxm","profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJnTEtkSHlOMWxWZm1GRHdKYUVTNklYM3FpdiJ9","forkedVal","interval","slug","maxm/wideLib","description","// const rows = parseRows([","versions",[141,144,148,152,156,160,164,168,172,176,180,184,188,192,196,200,204,208,212,216,220,224,228,232,236,240,244,248,252,257,261,265,269,273,277,281,285,289,293,297,301,305,309,313,317,321,325,329,333,337,341,345,348,352],{"_118":119,"_142":-5,"_116":143,"_113":111},"prId",["D",1732735173240],{"_118":145,"_142":-5,"_116":146,"_113":147},52,["D",1732735114985],"65b8fc5c-acf4-11ef-ab3f-e6cdfca9ef9f",{"_118":149,"_142":-5,"_116":150,"_113":151},51,["D",1732735110857],"633c7760-acf4-11ef-8fbc-e6cdfca9ef9f",{"_118":153,"_142":-5,"_116":154,"_113":155},50,["D",1732735089553],"56897d1a-acf4-11ef-8fbc-e6cdfca9ef9f",{"_118":157,"_142":-5,"_116":158,"_113":159},49,["D",1732735066542],"48d3c2ca-acf4-11ef-97bc-e6cdfca9ef9f",{"_118":161,"_142":-5,"_116":162,"_113":163},48,["D",1732722603042],"43ff1b9a-acd7-11ef-8dc0-e6cdfca9ef9f",{"_118":165,"_142":-5,"_116":166,"_113":167},47,["D",1732722301288],"90240586-acd6-11ef-ba2e-e6cdfca9ef9f",{"_118":169,"_142":-5,"_116":170,"_113":171},46,["D",1732722283689],"85a6db4c-acd6-11ef-a3a0-e6cdfca9ef9f",{"_118":173,"_142":-5,"_116":174,"_113":175},45,["D",1732722134789],"2ce6082a-acd6-11ef-8c11-e6cdfca9ef9f",{"_118":177,"_142":-5,"_116":178,"_113":179},44,["D",1732722118867],"23680744-acd6-11ef-ae87-e6cdfca9ef9f",{"_118":181,"_142":-5,"_116":182,"_113":183},43,["D",1732722025588],"ebcec6e2-acd5-11ef-9e66-e6cdfca9ef9f",{"_118":185,"_142":-5,"_116":186,"_113":187},42,["D",1732722004621],"df4fc1b4-acd5-11ef-a497-e6cdfca9ef9f",{"_118":189,"_142":-5,"_116":190,"_113":191},41,["D",1732721957618],"c34bf29e-acd5-11ef-9f1a-e6cdfca9ef9f",{"_118":193,"_142":-5,"_116":194,"_113":195},40,["D",1732721947081],"bd0384ce-acd5-11ef-8c11-e6cdfca9ef9f",{"_118":197,"_142":-5,"_116":198,"_113":199},39,["D",1732721598860],"ed75096c-acd4-11ef-a497-e6cdfca9ef9f",{"_118":201,"_142":-5,"_116":202,"_113":203},38,["D",1732721268286],"286c3460-acd4-11ef-af9f-e6cdfca9ef9f",{"_118":205,"_142":-5,"_116":206,"_113":207},37,["D",1732721226363],"0f709faa-acd4-11ef-bec9-e6cdfca9ef9f",{"_118":209,"_142":-5,"_116":210,"_113":211},36,["D",1732720052134],"5389eb04-acd1-11ef-9898-e6cdfca9ef9f",{"_118":213,"_142":-5,"_116":214,"_113":215},35,["D",1732720051397],"531a0cc6-acd1-11ef-a00c-e6cdfca9ef9f",{"_118":217,"_142":-5,"_116":218,"_113":219},34,["D",1732719374677],"bfbde430-accf-11ef-9cde-e6cdfca9ef9f",{"_118":221,"_142":-5,"_116":222,"_113":223},33,["D",1732719199553],"575c136c-accf-11ef-9b49-e6cdfca9ef9f",{"_118":225,"_142":-5,"_116":226,"_113":227},32,["D",1732718488885],"afc58a9e-accd-11ef-b455-e6cdfca9ef9f",{"_118":229,"_142":-5,"_116":230,"_113":231},31,["D",1732718487068],"aeb00e2c-accd-11ef-b397-e6cdfca9ef9f",{"_118":233,"_142":-5,"_116":234,"_113":235},30,["D",1732718481046],"ab1853f0-accd-11ef-b455-e6cdfca9ef9f",{"_118":237,"_142":-5,"_116":238,"_113":239},29,["D",1732718452387],"9a039fca-accd-11ef-8c28-e6cdfca9ef9f",{"_118":241,"_142":-5,"_116":242,"_113":243},28,["D",1732718437790],"91506926-accd-11ef-b50b-e6cdfca9ef9f",{"_118":245,"_142":-5,"_116":246,"_113":247},27,["D",1732716610954],"506f0fc4-acc9-11ef-93e3-e6cdfca9ef9f",{"_118":249,"_142":-5,"_116":250,"_113":251},26,["D",1732716605951],"4d73b9a0-acc9-11ef-acd6-e6cdfca9ef9f",{"_118":253,"_142":254,"_116":255,"_113":256},25,"06aee58c-ac59-11ef-9f5d-e6cdfca9ef9f",["D",1732668386860],"08a13642-ac59-11ef-b403-e6cdfca9ef9f",{"_118":258,"_142":-5,"_116":259,"_113":260},24,["D",1732665624842],"9a58b60c-ac52-11ef-b7a1-e6cdfca9ef9f",{"_118":262,"_142":-5,"_116":263,"_113":264},23,["D",1732665253299],"bce31a6a-ac51-11ef-aabb-e6cdfca9ef9f",{"_118":266,"_142":-5,"_116":267,"_113":268},22,["D",1732665244334],"b78ba122-ac51-11ef-9529-e6cdfca9ef9f",{"_118":270,"_142":-5,"_116":271,"_113":272},21,["D",1732665204414],"9fc10276-ac51-11ef-9529-e6cdfca9ef9f",{"_118":274,"_142":-5,"_116":275,"_113":276},20,["D",1732401717073],"250ee96a-a9ec-11ef-85d5-e6cdfca9ef9f",{"_118":278,"_142":-5,"_116":279,"_113":280},19,["D",1732401713394],"22dd5f64-a9ec-11ef-ba69-e6cdfca9ef9f",{"_118":282,"_142":-5,"_116":283,"_113":284},18,["D",1732401704417],"1d82b140-a9ec-11ef-bdcb-e6cdfca9ef9f",{"_118":286,"_142":-5,"_116":287,"_113":288},17,["D",1732401662920],"04c6b7dc-a9ec-11ef-86df-e6cdfca9ef9f",{"_118":290,"_142":-5,"_116":291,"_113":292},16,["D",1732401644054],"f98a1972-a9eb-11ef-bc05-e6cdfca9ef9f",{"_118":294,"_142":-5,"_116":295,"_113":296},15,["D",1732401637000],"f553c056-a9eb-11ef-86df-e6cdfca9ef9f",{"_118":298,"_142":-5,"_116":299,"_113":300},14,["D",1732401600675],"dfacf22c-a9eb-11ef-86df-e6cdfca9ef9f",{"_118":302,"_142":-5,"_116":303,"_113":304},13,["D",1732401580748],"d3cc5be6-a9eb-11ef-9758-e6cdfca9ef9f",{"_118":306,"_142":-5,"_116":307,"_113":308},12,["D",1732401405042],"6b121186-a9eb-11ef-a733-e6cdfca9ef9f",{"_118":310,"_142":-5,"_116":311,"_113":312},11,["D",1732401295552],"29cfb4ee-a9eb-11ef-85df-e6cdfca9ef9f",{"_118":314,"_142":-5,"_116":315,"_113":316},10,["D",1732401282521],"220bb7d0-a9eb-11ef-b7ef-e6cdfca9ef9f",{"_118":318,"_142":-5,"_116":319,"_113":320},9,["D",1732401274092],"1d0507aa-a9eb-11ef-97bf-e6cdfca9ef9f",{"_118":322,"_142":-5,"_116":323,"_113":324},8,["D",1732401260177],"14b9ef5c-a9eb-11ef-b1b0-e6cdfca9ef9f",{"_118":326,"_142":-5,"_116":327,"_113":328},7,["D",1732401237240],"070dea20-a9eb-11ef-a8c8-e6cdfca9ef9f",{"_118":330,"_142":-5,"_116":331,"_113":332},6,["D",1732401131223],"c7dd9bf2-a9ea-11ef-840e-e6cdfca9ef9f",{"_118":334,"_142":-5,"_116":335,"_113":336},5,["D",1732401130313],"c7519670-a9ea-11ef-a8c8-e6cdfca9ef9f",{"_118":338,"_142":-5,"_116":339,"_113":340},4,["D",1732401127110],"c568dce2-a9ea-11ef-b7ca-e6cdfca9ef9f",{"_118":342,"_142":-5,"_116":343,"_113":344},3,["D",1732392100924],"c162d158-a9d5-11ef-a183-e6cdfca9ef9f",{"_118":90,"_142":-5,"_116":346,"_113":347},["D",1732392074786],"b1ce74fe-a9d5-11ef-b16b-e6cdfca9ef9f",{"_118":349,"_142":-5,"_116":350,"_113":351},1,["D",1732392039171],"9c9433a8-a9d5-11ef-9d58-e6cdfca9ef9f",{"_118":31,"_142":-5,"_116":353,"_113":354},["D",1732392026816],"95387ad8-a9d5-11ef-97a8-e6cdfca9ef9f","recentThread","breadcrumbs","comments",[],"isOwner","routes/_app.v.$handle.$name.versions.$(version)",{"_93":362,"_363":364,"_94":365},"single","redirect","/v/maxm/wideLib/versions",{"_113":175,"_114":366,"_118":173,"_116":367,"_368":99,"_100":101,"_108":109,"_129":130,"_11":96,"_369":-5,"_370":-5,"_371":-5,"_359":26,"_135":136},"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 field_name: z.string(),\n value: z.string(),\n operator: StringOperator,\n }),\n z.object({\n field_name: z.string(),\n value: z.number(),\n operator: NumberOperator,\n }),\n z.object({\n field_name: z.string(),\n value: z.boolean(),\n operator: BooleanOperator,\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.field_name;\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 layer validates iso date formats so we should generally be processing those.\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\n // Helper function to set nested object value\n const setNestedValue = (obj: any, path: string, value: any) => {\n const parts = path.split(\".\");\n let current = obj;\n\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\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 }\n\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 }\n\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\n return reconstructed;\n });\n}",["D",1732722134789],"valName","deletedAt","messageId","threadId","routes/_app","actionData","errors"]

wideLib

Version 45