Limadata: Profile Signal

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_318":319,"_320":321},"routes/_app.x.$handle.$name",{"_97":96,"_32":45,"_105":87,"_27":99,"_13":14,"_106":-5,"_11":107,"_108":109,"_110":111,"_112":113,"_114":115,"_20":-5,"_116":-5,"_117":-5,"_118":25,"_119":120,"_121":59,"_122":-5,"_123":-5,"_124":125},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":-5,"_67":-5,"_68":-5,"_69":-5,"_70":71,"_72":71,"_73":74,"_75":74,"_76":74,"_77":71,"_78":71,"_79":80,"_81":82,"_83":84,"_85":25},"version",138,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","processor.ts","latestPath","fileId","fc122f8e-a617-11f1-9834-1607ee4eb77e","name","fileType","interval","content","// Layer 2 processor: reads unprocessed payloads from thenetwork.raw_payloads,\n// normalizes into people/companies/profile_changes/new_employee_detections, stamps\n// processed_at. Idempotent (all inserts on conflict do nothing / upsert), so the\n// normalized tables can be truncated and rebuilt from the landing table at any time.\n//\n// Two sources, one set of tables (distinguished by the vendor column):\n// - The Network: schema_version like 'greycroft.%', dispatched on version PREFIX.\n// - Limadata: vendor = 'limadata' (no schema_version), one person per payload with a\n// data.changes diff array. Entity ids are uuid5(NAMESPACE_URL, data.url) so both\n// vendors share the people/profile_changes tables without colliding. The one-shot\n// history backfill (lima_backfill.py, run locally 2026-09-01) uses the SAME mapping:\n// keep them in sync if the rules ever change.\n//\n// All values are bound as text/text[] and cast server-side (::uuid[], ::jsonb[], ...):\n// postgres.js type inference mangles jsonb and array params (double-encodes strings,\n// misbinds arrays), so nothing here relies on it.\nimport postgres from \"npm:postgres\";\n\nconst sql = postgres(Deno.env.get(\"DATABASE_URL\")!, {\n max: 1,\n idle_timeout: 20,\n connect_timeout: 10,\n});\n\nconst CHUNK = 500;\nconst text = (v: unknown) => (v == null || v === \"\" ? null : String(v));\n// payload current_date arrives as MM-DD-YYYY\nconst usDate = (v: unknown) => {\n const m = String(v ?? \"\").match(/^(\\d{2})-(\\d{2})-(\\d{4})$/);\n return m ? `${m[3]}-${m[1]}-${m[2]}` : null;\n};\nconst pathParts = (p: string) => p.split(\"/\").filter(Boolean);\nconst jsonOrNull = (v: unknown) => (v === undefined ? null : JSON.stringify(v));\n\nfunction chunks(rows: T[]): T[][] {\n const out: T[][] = [];\n for (let i = 0; i < rows.length; i += CHUNK) out.push(rows.slice(i, i + CHUNK));\n return out;\n}\n\nasync function upsertPeople(tx: any, people: Map) {\n for (const batch of chunks([...people.values()])) {\n const col = (k: string) => batch.map((p) => p[k]);\n await tx`\n insert into thenetwork.people (entity_id, name, linkedin_url, public_identifier, linkedin_entity_urn, category, last_seen_at, vendor, location)\n select * from unnest(\n ${col(\"entity_id\")}::uuid[], ${col(\"name\")}::text[], ${col(\"linkedin_url\")}::text[],\n ${col(\"public_identifier\")}::text[], ${col(\"linkedin_entity_urn\")}::text[],\n ${col(\"category\")}::text[], ${col(\"last_seen_at\")}::timestamptz[], ${col(\"vendor\")}::text[],\n ${col(\"location\")}::text[])\n on conflict (entity_id) do update set\n name = coalesce(excluded.name, thenetwork.people.name),\n linkedin_url = coalesce(excluded.linkedin_url, thenetwork.people.linkedin_url),\n public_identifier = coalesce(excluded.public_identifier, thenetwork.people.public_identifier),\n linkedin_entity_urn = coalesce(excluded.linkedin_entity_urn, thenetwork.people.linkedin_entity_urn),\n category = coalesce(excluded.category, thenetwork.people.category),\n last_seen_at = greatest(excluded.last_seen_at, thenetwork.people.last_seen_at),\n location = coalesce(excluded.location, thenetwork.people.location)`;\n }\n}\n\nasync function insertChanges(tx: any, rows: any[]) {\n for (const batch of chunks(rows)) {\n const col = (k: string) => batch.map((r) => r[k]);\n await tx`\n insert into thenetwork.profile_changes\n (raw_payload_id, entity_id, observed_on, detected_at, field_path, section, company_hint, change_type, previous_value, current_value, vendor, noise)\n select * from unnest(\n ${col(\"raw_payload_id\")}::bigint[], ${col(\"entity_id\")}::uuid[], ${col(\"observed_on\")}::date[],\n ${col(\"detected_at\")}::timestamptz[], ${col(\"field_path\")}::text[], ${col(\"section\")}::text[],\n ${col(\"company_hint\")}::text[], ${col(\"change_type\")}::text[],\n ${col(\"previous_value\")}::jsonb[], ${col(\"current_value\")}::jsonb[], ${col(\"vendor\")}::text[],\n ${col(\"noise\")}::boolean[])\n on conflict (entity_id, observed_on, field_path, change_type) do nothing`;\n }\n}\n\nasync function processProfileChanges(tx: any, rawId: number, payload: any) {\n const observedOn = usDate(payload.current_date) ??\n (payload.generated_at ?? new Date().toISOString()).slice(0, 10);\n const people = new Map();\n const rows: any[] = [];\n for (const person of payload.changes ?? []) {\n const entityId = text(person.entity_id);\n if (!entityId) continue;\n people.set(entityId, {\n entity_id: entityId,\n name: text(person.name),\n linkedin_url: text(person.linkedin_url),\n public_identifier: text(person.public_identifier),\n linkedin_entity_urn: text(person.linkedin_entity_urn),\n category: text(person.category),\n last_seen_at: text(payload.generated_at),\n vendor: \"the_network\",\n location: text(person.location),\n });\n for (const change of person.changes ?? []) {\n const fieldPath = text(change.field);\n const changeType = text(change.change_type);\n if (!fieldPath || !changeType) continue;\n const parts = pathParts(fieldPath);\n rows.push({\n raw_payload_id: String(rawId),\n entity_id: entityId,\n observed_on: observedOn,\n detected_at: text(change.detected_at),\n field_path: fieldPath,\n section: parts[0] ?? null,\n company_hint: parts[0] === \"experience\" && parts[1] ? parts[1].split(\"@\")[0] : null,\n change_type: changeType,\n previous_value: jsonOrNull(change.previous_value),\n current_value: jsonOrNull(change.current_value),\n vendor: \"the_network\",\n noise: \"false\",\n });\n }\n }\n await upsertPeople(tx, people);\n await insertChanges(tx, rows);\n return { people: people.size, changes: rows.length };\n}\n\nasync function processNetNew(tx: any, rawId: number, payload: any) {\n const people = new Map();\n const companies = new Map();\n const rows: any[] = [];\n for (const emp of payload.employees ?? []) {\n const humanId = text(emp.human_entity_id);\n const companyId = text(emp.company_entity_id);\n const detectedAt = text(emp.detected_at);\n if (!humanId || !companyId || !detectedAt) continue;\n people.set(humanId, {\n entity_id: humanId,\n name: text(emp.human_name),\n linkedin_url: text(emp.linkedin_url),\n public_identifier: text(emp.public_identifier),\n linkedin_entity_urn: text(emp.linkedin_entity_urn),\n category: text(emp.category),\n last_seen_at: text(payload.generated_at),\n vendor: \"the_network\",\n location: text(emp.location),\n });\n companies.set(companyId, {\n company_entity_id: companyId,\n name: text(emp.company_name),\n linkedin_company_id: text(emp.linkedin_company_id),\n last_seen_at: text(payload.generated_at),\n });\n rows.push({\n raw_payload_id: String(rawId),\n company_entity_id: companyId,\n human_entity_id: humanId,\n position: text(emp.position),\n category: text(emp.category),\n detected_at: detectedAt,\n });\n }\n for (const batch of chunks([...companies.values()])) {\n const col = (k: string) => batch.map((c) => c[k]);\n await tx`\n insert into thenetwork.companies (company_entity_id, name, linkedin_company_id, last_seen_at)\n select * from unnest(\n ${col(\"company_entity_id\")}::uuid[], ${col(\"name\")}::text[],\n ${col(\"linkedin_company_id\")}::text[], ${col(\"last_seen_at\")}::timestamptz[])\n on conflict (company_entity_id) do update set\n name = coalesce(excluded.name, thenetwork.companies.name),\n linkedin_company_id = coalesce(excluded.linkedin_company_id, thenetwork.companies.linkedin_company_id),\n last_seen_at = greatest(excluded.last_seen_at, thenetwork.companies.last_seen_at)`;\n }\n await upsertPeople(tx, people);\n for (const batch of chunks(rows)) {\n const col = (k: string) => batch.map((r) => r[k]);\n await tx`\n insert into thenetwork.new_employee_detections\n (raw_payload_id, company_entity_id, human_entity_id, position, category, detected_at)\n select * from unnest(\n ${col(\"raw_payload_id\")}::bigint[], ${col(\"company_entity_id\")}::uuid[], ${col(\"human_entity_id\")}::uuid[],\n ${col(\"position\")}::text[], ${col(\"category\")}::text[], ${col(\"detected_at\")}::timestamptz[])\n on conflict (human_entity_id, company_entity_id, detected_at) do nothing`;\n }\n return { people: people.size, companies: companies.size, detections: rows.length };\n}\n\n// ---- Limadata ----\n\nconst NS_URL = \"6ba7b811-9dad-11d1-80b4-00c04fd430c8\"; // RFC 4122 URL namespace\nasync function uuid5(name: string): Promise {\n const ns = new Uint8Array(NS_URL.replace(/-/g, \"\").match(/../g)!.map((h) => parseInt(h, 16)));\n const data = new Uint8Array([...ns, ...new TextEncoder().encode(name)]);\n const hash = new Uint8Array(await crypto.subtle.digest(\"SHA-1\", data));\n hash[6] = (hash[6] & 0x0f) | 0x50;\n hash[8] = (hash[8] & 0x3f) | 0x80;\n const h = [...hash.slice(0, 16)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n return `${h.slice(0, 8)}-${h.slice(8, 12)}-${h.slice(12, 16)}-${h.slice(16, 20)}-${h.slice(20, 32)}`;\n}\n\nconst slugify = (s: unknown) => {\n const r = String(s ?? \"\").toLowerCase().replace(/[^a-z0-9]+/g, \"_\").replace(/^_+|_+$/g, \"\");\n return r.slice(0, 60) || \"x\";\n};\n\n// Limadata noise: ~96% of its experience diffs are a removed+added pair of the same\n// object differing only in LinkedIn's location relabeling (\"Greater X Area\" ->\n// \"X Metropolitan Area\"). Mark both halves noise=true when the pair is identical\n// after stripping every 'location' key (any depth). Same rule as the 2026-09-01\n// backfill in lima_backfill marking (keep in sync).\nconst stripLoc = (v: any): any => {\n if (Array.isArray(v)) return v.map(stripLoc);\n if (v && typeof v === \"object\") {\n const out: any = {};\n for (const [k, val] of Object.entries(v)) if (k !== \"location\") out[k] = stripLoc(val);\n return out;\n }\n return v;\n};\nconst stableJSON = (v: any): string => {\n if (Array.isArray(v)) return \"[\" + v.map(stableJSON).join(\",\") + \"]\";\n if (v && typeof v === \"object\") {\n return \"{\" + Object.keys(v).sort().map((k) => JSON.stringify(k) + \":\" + stableJSON(v[k])).join(\",\") + \"}\";\n }\n return JSON.stringify(v);\n};\nexport function markLocationNoise(rows: any[]) {\n const byPath = new Map();\n for (const r of rows) {\n if (r.section !== \"experience\") continue;\n if (!byPath.has(r.field_path)) byPath.set(r.field_path, []);\n byPath.get(r.field_path)!.push(r);\n }\n for (const group of byPath.values()) {\n const added = group.find((r) => r.change_type === \"added\");\n const removed = group.find((r) => r.change_type === \"removed\");\n if (!added || !removed) continue;\n if (stableJSON(stripLoc(added._raw)) === stableJSON(stripLoc(removed._raw))) {\n added.noise = \"true\";\n removed.noise = \"true\";\n }\n }\n}\n\nexport async function normLimadata(rawId: number, payload: any) {\n const data = payload?.data ?? {};\n const ts = text(payload?.timestamp);\n const url = text(data.url) ?? text(data.external_id);\n if (!ts) throw new Error(\"limadata: missing timestamp\");\n if (!url || !(\"changes\" in data)) throw new Error(\"limadata: unrecognized shape (no data.url / data.changes)\");\n const entityId = await uuid5(url);\n const after = data.after ?? {};\n const before = data.before ?? {};\n // data.url is urn-based for many profiles; the vanity slug lives in the\n // snapshot's profile_url (see backfill-lima-slugs.ts for the history backfill)\n const profileUrl = text(after.profile_url) ?? text(before.profile_url);\n const slugMatch = profileUrl?.match(/\\/in\\/([^/?#]+)/);\n const slug = slugMatch && !/^AC[ow]/.test(slugMatch[1]) ? slugMatch[1] : null;\n const person = {\n entity_id: entityId,\n name: text(after.full_name) ?? text(before.full_name),\n linkedin_url: url,\n public_identifier: slug,\n linkedin_entity_urn: text(after.urn) ?? text(before.urn),\n category: null,\n last_seen_at: ts,\n vendor: \"limadata\",\n location: text(after.location?.text) ?? text(before.location?.text),\n };\n // company names live only in the snapshot experiences; map linkedin company id -> name\n // so dashboards can resolve the numeric company_hint values\n const companies = new Map();\n for (const exp of [...(after.experiences ?? []), ...(before.experiences ?? [])]) {\n const cid = text(exp?.company_id);\n const cname = text(exp?.company);\n if (!cid || !cname || companies.has(cid)) continue;\n companies.set(cid, {\n company_entity_id: await uuid5(`https://linkedin.com/company/${cid}`),\n name: cname,\n linkedin_company_id: cid,\n last_seen_at: ts,\n vendor: \"limadata\",\n });\n }\n const rows: any[] = [];\n const observedOn = ts.slice(0, 10);\n for (const c of data.changes ?? []) {\n const prop = text(c?.property_name);\n const prev = c?.before ?? null;\n const curr = c?.after ?? null;\n if (!prop || (prev === null && curr === null)) continue;\n const changeType = prev === null ? \"added\" : curr === null ? \"removed\" : \"updated\";\n const obj = curr !== null ? curr : prev;\n let path = \"/\" + prop;\n if (prop === \"experiences\" && obj && typeof obj === \"object\") {\n path = `/experiences/${obj.company_id || \"x\"}@${obj.starts_at || \"\"}@${slugify(obj.title)}`;\n }\n const base = prop.split(/[\\[.]/)[0];\n const section = base === \"experiences\" ? \"experience\" : base;\n const hint = obj && typeof obj === \"object\" ? text(obj.company) ?? text(obj.company_id) : null;\n rows.push({\n raw_payload_id: String(rawId),\n entity_id: entityId,\n observed_on: observedOn,\n detected_at: ts,\n field_path: path,\n section,\n company_hint: hint,\n change_type: changeType,\n previous_value: jsonOrNull(prev ?? undefined) ?? null,\n current_value: jsonOrNull(curr ?? undefined) ?? null,\n vendor: \"limadata\",\n // location.text section changes are noise by definition; experience pairs\n // are re-checked below once both sides of the pair are collected\n noise: section === \"location\" ? \"true\" : \"false\",\n _raw: obj,\n });\n }\n markLocationNoise(rows);\n for (const r of rows) delete r._raw;\n return { person, companies, rows };\n}\n\nasync function processLimadataBatch(batch: any[]) {\n // Normalize in JS first so shape errors are isolated per payload without SQL.\n const people = new Map();\n const companies = new Map();\n const rows: any[] = [];\n const doneIds: string[] = [];\n for (const row of batch) {\n const rawId = Number(row.id);\n try {\n const { person, companies: comps, rows: r } = await normLimadata(rawId, row.payload);\n people.set(person.entity_id, person);\n for (const [cid, c] of comps) if (!companies.has(cid)) companies.set(cid, c);\n rows.push(...r);\n doneIds.push(String(rawId));\n } catch (error) {\n const message = error instanceof Error ? error.message : String(error);\n console.error(`limadata payload ${rawId} failed:`, message);\n await sql`update thenetwork.raw_payloads set processing_error = ${message} where id = ${String(rawId)}::bigint`;\n }\n }\n if (!doneIds.length) return { payloads: 0, changes: 0 };\n await sql.begin(async (tx: any) => {\n for (const batch of chunks([...companies.values()])) {\n const col = (k: string) => batch.map((c) => c[k]);\n await tx`\n insert into thenetwork.companies (company_entity_id, name, linkedin_company_id, last_seen_at, vendor)\n select * from unnest(\n ${col(\"company_entity_id\")}::uuid[], ${col(\"name\")}::text[],\n ${col(\"linkedin_company_id\")}::text[], ${col(\"last_seen_at\")}::timestamptz[], ${col(\"vendor\")}::text[])\n on conflict (company_entity_id) do update set\n name = coalesce(excluded.name, thenetwork.companies.name),\n last_seen_at = greatest(excluded.last_seen_at, thenetwork.companies.last_seen_at)`;\n }\n await upsertPeople(tx, people);\n await insertChanges(tx, rows);\n await tx`update thenetwork.raw_payloads set processed_at = now() where id = any(${doneIds}::bigint[])`;\n });\n return { payloads: doneIds.length, changes: rows.length };\n}\n\nexport default async function () {\n const pending = await sql`\n select id, schema_version, payload from thenetwork.raw_payloads\n where processed_at is null and processing_error is null and schema_version like ${\"greycroft.%\"}\n order by id limit 20`;\n for (const row of pending) {\n const rawId = Number(row.id);\n try {\n const stats = await sql.begin(async (tx: any) => {\n let stats: unknown;\n if (row.schema_version.startsWith(\"greycroft.profile_changes.\")) {\n stats = await processProfileChanges(tx, rawId, row.payload);\n } else if (row.schema_version.startsWith(\"greycroft.company_tracker_validated_net_new.\")) {\n stats = await processNetNew(tx, rawId, row.payload);\n } else {\n throw new Error(`no handler for schema_version ${row.schema_version}`);\n }\n await tx`update thenetwork.raw_payloads set processed_at = now() where id = ${String(rawId)}::bigint`;\n return stats;\n });\n console.log(`processed ${rawId} (${row.schema_version}):`, JSON.stringify(stats));\n } catch (error) {\n const message = error instanceof Error ? error.message : String(error);\n console.error(`payload ${rawId} failed:`, message);\n await sql`update thenetwork.raw_payloads set processing_error = ${message} where id = ${String(rawId)}::bigint`;\n }\n }\n\n const lima = await sql`\n select id, payload from thenetwork.raw_payloads\n where vendor = 'limadata' and processed_at is null and processing_error is null\n order by id limit 300`;\n if (lima.length) {\n const stats = await processLimadataBatch([...lima]);\n console.log(`limadata:`, JSON.stringify(stats));\n }\n console.log(`done: ${pending.length} greycroft + ${lima.length} limadata payload(s) examined`);\n}\n","// Layer 2 processor: reads unprocessed payloads from thenetwork.raw_payloads,\n// normalizes into people/companies/profile_changes/new_employee_detections, stamps\n// processed_at. Idempotent (all inserts on conflict do nothing / upsert), so the\n// normalized tables can be truncated and rebuilt from the landing table at any time.\n//\n// Two sources, one set of tables (distinguished by the vendor column):\n// - The Network: schema_version like 'greycroft.%', dispatched on version PREFIX.\n// - Limadata: vendor = 'limadata' (no schema_version), one person per payload with a\n// data.changes diff array. Entity ids are uuid5(NAMESPACE_URL, data.url) so both\n// vendors share the people/profile_changes tables without colliding. The one-shot\n// history backfill (lima_backfill.py, run locally 2026-09-01) uses the SAME mapping:\n// keep them in sync if the rules ever change.\n//\n// All values are bound as text/text[] and cast server-side (::uuid[], ::jsonb[], ...):\n// postgres.js type inference mangles jsonb and array params (double-encodes strings,\n// misbinds arrays), so nothing here relies on it.\nimport postgres from \"npm:postgres\";\n\nconst sql = postgres(Deno.env.get(\"DATABASE_URL\")!, {\n max: 1,\n idle_timeout: 20,\n connect_timeout: 10,\n});\n\nconst CHUNK = 500;\nconst text = (v: unknown) => (v == null || v === \"\" ? null : String(v));\n// payload current_date arrives as MM-DD-YYYY\nconst usDate = (v: unknown) => {\n const m = String(v ?? \"\").match(/^(\\d{2})-(\\d{2})-(\\d{4})$/);\n return m ? `${m[3]}-${m[1]}-${m[2]}` : null;\n};\nconst pathParts = (p: string) => p.split(\"/\").filter(Boolean);\nconst jsonOrNull = (v: unknown) => (v === undefined ? null : JSON.stringify(v));\n\nfunction chunks(rows: T[]): T[][] {\n const out: T[][] = [];\n for (let i = 0; i < rows.length; i += CHUNK) out.push(rows.slice(i, i + CHUNK));\n return out;\n}\n\nasync function upsertPeople(tx: any, people: Map) {\n for (const batch of chunks([...people.values()])) {\n const col = (k: string) => batch.map((p) => p[k]);\n await tx`\n insert into thenetwork.people (entity_id, name, linkedin_url, public_identifier, linkedin_entity_urn, category, last_seen_at, vendor, location)\n select * from unnest(\n ${col(\"entity_id\")}::uuid[], ${col(\"name\")}::text[], ${col(\"linkedin_url\")}::text[],\n ${col(\"public_identifier\")}::text[], ${col(\"linkedin_entity_urn\")}::text[],\n ${col(\"category\")}::text[], ${col(\"last_seen_at\")}::timestamptz[], ${col(\"vendor\")}::text[],\n ${col(\"location\")}::text[])\n on conflict (entity_id) do update set\n name = coalesce(excluded.name, thenetwork.people.name),\n linkedin_url = coalesce(excluded.linkedin_url, thenetwork.people.linkedin_url),\n public_identifier = coalesce(excluded.public_identifier, thenetwork.people.public_identifier),\n linkedin_entity_urn = coalesce(excluded.linkedin_entity_urn, thenetwork.people.linkedin_entity_urn),\n category = coalesce(excluded.category, thenetwork.people.category),\n last_seen_at = greatest(excluded.last_seen_at, thenetwork.people.last_seen_at),\n location = coalesce(excluded.location, thenetwork.people.location)`;\n }\n}\n\nasync function insertChanges(tx: any, rows: any[]) {\n // GOTCHA (found 2026-09-04, see selftest-noise-live.ts): postgres.js mangles\n // non-text array casts — a string array cast ::boolean[] silently becomes ALL\n // FALSE and ::jsonb[] double-encodes into jsonb string scalars. Only ::text[]\n // binds faithfully, so every column is bound text[] and cast per column in the\n // SELECT list instead.\n for (const batch of chunks(rows)) {\n const col = (k: string) => batch.map((r) => r[k]);\n await tx`\n insert into thenetwork.profile_changes\n (raw_payload_id, entity_id, observed_on, detected_at, field_path, section, company_hint, change_type, previous_value, current_value, vendor, noise)\n select rid::bigint, eid::uuid, obs::date, det::timestamptz, fp, sec, hint, ct,\n pv::jsonb, cv::jsonb, vnd, nz::boolean\n from unnest(\n ${col(\"raw_payload_id\")}::text[], ${col(\"entity_id\")}::text[], ${col(\"observed_on\")}::text[],\n ${col(\"detected_at\")}::text[], ${col(\"field_path\")}::text[], ${col(\"section\")}::text[],\n ${col(\"company_hint\")}::text[], ${col(\"change_type\")}::text[],\n ${col(\"previous_value\")}::text[], ${col(\"current_value\")}::text[], ${col(\"vendor\")}::text[],\n ${col(\"noise\")}::text[])\n as t(rid, eid, obs, det, fp, sec, hint, ct, pv, cv, vnd, nz)\n on conflict (entity_id, observed_on, field_path, change_type) do nothing`;\n }\n}\n\nasync function processProfileChanges(tx: any, rawId: number, payload: any) {\n const observedOn = usDate(payload.current_date) ??\n (payload.generated_at ?? new Date().toISOString()).slice(0, 10);\n const people = new Map();\n const rows: any[] = [];\n for (const person of payload.changes ?? []) {\n const entityId = text(person.entity_id);\n if (!entityId) continue;\n people.set(entityId, {\n entity_id: entityId,\n name: text(person.name),\n linkedin_url: text(person.linkedin_url),\n public_identifier: text(person.public_identifier),\n linkedin_entity_urn: text(person.linkedin_entity_urn),\n category: text(person.category),\n last_seen_at: text(payload.generated_at),\n vendor: \"the_network\",\n location: text(person.location),\n });\n for (const change of person.changes ?? []) {\n const fieldPath = text(change.field);\n const changeType = text(change.change_type);\n if (!fieldPath || !changeType) continue;\n const parts = pathParts(fieldPath);\n rows.push({\n raw_payload_id: String(rawId),\n entity_id: entityId,\n observed_on: observedOn,\n detected_at: text(change.detected_at),\n field_path: fieldPath,\n section: parts[0] ?? null,\n company_hint: parts[0] === \"experience\" && parts[1] ? parts[1].split(\"@\")[0] : null,\n change_type: changeType,\n previous_value: jsonOrNull(change.previous_value),\n current_value: jsonOrNull(change.current_value),\n vendor: \"the_network\",\n noise: \"false\",\n });\n }\n }\n await upsertPeople(tx, people);\n await insertChanges(tx, rows);\n return { people: people.size, changes: rows.length };\n}\n\nasync function processNetNew(tx: any, rawId: number, payload: any) {\n const people = new Map();\n const companies = new Map();\n const rows: any[] = [];\n for (const emp of payload.employees ?? []) {\n const humanId = text(emp.human_entity_id);\n const companyId = text(emp.company_entity_id);\n const detectedAt = text(emp.detected_at);\n if (!humanId || !companyId || !detectedAt) continue;\n people.set(humanId, {\n entity_id: humanId,\n name: text(emp.human_name),\n linkedin_url: text(emp.linkedin_url),\n public_identifier: text(emp.public_identifier),\n linkedin_entity_urn: text(emp.linkedin_entity_urn),\n category: text(emp.category),\n last_seen_at: text(payload.generated_at),\n vendor: \"the_network\",\n location: text(emp.location),\n });\n companies.set(companyId, {\n company_entity_id: companyId,\n name: text(emp.company_name),\n linkedin_company_id: text(emp.linkedin_company_id),\n last_seen_at: text(payload.generated_at),\n });\n rows.push({\n raw_payload_id: String(rawId),\n company_entity_id: companyId,\n human_entity_id: humanId,\n position: text(emp.position),\n category: text(emp.category),\n detected_at: detectedAt,\n });\n }\n for (const batch of chunks([...companies.values()])) {\n const col = (k: string) => batch.map((c) => c[k]);\n await tx`\n insert into thenetwork.companies (company_entity_id, name, linkedin_company_id, last_seen_at)\n select * from unnest(\n ${col(\"company_entity_id\")}::uuid[], ${col(\"name\")}::text[],\n ${col(\"linkedin_company_id\")}::text[], ${col(\"last_seen_at\")}::timestamptz[])\n on conflict (company_entity_id) do update set\n name = coalesce(excluded.name, thenetwork.companies.name),\n linkedin_company_id = coalesce(excluded.linkedin_company_id, thenetwork.companies.linkedin_company_id),\n last_seen_at = greatest(excluded.last_seen_at, thenetwork.companies.last_seen_at)`;\n }\n await upsertPeople(tx, people);\n for (const batch of chunks(rows)) {\n const col = (k: string) => batch.map((r) => r[k]);\n await tx`\n insert into thenetwork.new_employee_detections\n (raw_payload_id, company_entity_id, human_entity_id, position, category, detected_at)\n select * from unnest(\n ${col(\"raw_payload_id\")}::bigint[], ${col(\"company_entity_id\")}::uuid[], ${col(\"human_entity_id\")}::uuid[],\n ${col(\"position\")}::text[], ${col(\"category\")}::text[], ${col(\"detected_at\")}::timestamptz[])\n on conflict (human_entity_id, company_entity_id, detected_at) do nothing`;\n }\n return { people: people.size, companies: companies.size, detections: rows.length };\n}\n\n// ---- Limadata ----\n\nconst NS_URL = \"6ba7b811-9dad-11d1-80b4-00c04fd430c8\"; // RFC 4122 URL namespace\nasync function uuid5(name: string): Promise {\n const ns = new Uint8Array(NS_URL.replace(/-/g, \"\").match(/../g)!.map((h) => parseInt(h, 16)));\n const data = new Uint8Array([...ns, ...new TextEncoder().encode(name)]);\n const hash = new Uint8Array(await crypto.subtle.digest(\"SHA-1\", data));\n hash[6] = (hash[6] & 0x0f) | 0x50;\n hash[8] = (hash[8] & 0x3f) | 0x80;\n const h = [...hash.slice(0, 16)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n return `${h.slice(0, 8)}-${h.slice(8, 12)}-${h.slice(12, 16)}-${h.slice(16, 20)}-${h.slice(20, 32)}`;\n}\n\nconst slugify = (s: unknown) => {\n const r = String(s ?? \"\").toLowerCase().replace(/[^a-z0-9]+/g, \"_\").replace(/^_+|_+$/g, \"\");\n return r.slice(0, 60) || \"x\";\n};\n\n// Limadata noise: ~96% of its experience diffs are a removed+added pair of the same\n// object differing only in LinkedIn's location relabeling (\"Greater X Area\" ->\n// \"X Metropolitan Area\"). Mark both halves noise=true when the pair is identical\n// after stripping every 'location' key (any depth). Same rule as the 2026-09-01\n// backfill in lima_backfill marking (keep in sync).\nconst stripLoc = (v: any): any => {\n if (Array.isArray(v)) return v.map(stripLoc);\n if (v && typeof v === \"object\") {\n const out: any = {};\n for (const [k, val] of Object.entries(v)) if (k !== \"location\") out[k] = stripLoc(val);\n return out;\n }\n return v;\n};\nconst stableJSON = (v: any): string => {\n if (Array.isArray(v)) return \"[\" + v.map(stableJSON).join(\",\") + \"]\";\n if (v && typeof v === \"object\") {\n return \"{\" + Object.keys(v).sort().map((k) => JSON.stringify(k) + \":\" + stableJSON(v[k])).join(\",\") + \"}\";\n }\n return JSON.stringify(v);\n};\nexport function markLocationNoise(rows: any[]) {\n const byPath = new Map();\n for (const r of rows) {\n if (r.section !== \"experience\") continue;\n if (!byPath.has(r.field_path)) byPath.set(r.field_path, []);\n byPath.get(r.field_path)!.push(r);\n }\n for (const group of byPath.values()) {\n const added = group.find((r) => r.change_type === \"added\");\n const removed = group.find((r) => r.change_type === \"removed\");\n if (!added || !removed) continue;\n if (stableJSON(stripLoc(added._raw)) === stableJSON(stripLoc(removed._raw))) {\n added.noise = \"true\";\n removed.noise = \"true\";\n }\n }\n}\n\nexport async function normLimadata(rawId: number, payload: any) {\n const data = payload?.data ?? {};\n const ts = text(payload?.timestamp);\n const url = text(data.url) ?? text(data.external_id);\n if (!ts) throw new Error(\"limadata: missing timestamp\");\n if (!url || !(\"changes\" in data)) throw new Error(\"limadata: unrecognized shape (no data.url / data.changes)\");\n const entityId = await uuid5(url);\n const after = data.after ?? {};\n const before = data.before ?? {};\n // data.url is urn-based for many profiles; the vanity slug lives in the\n // snapshot's profile_url (see backfill-lima-slugs.ts for the history backfill)\n const profileUrl = text(after.profile_url) ?? text(before.profile_url);\n const slugMatch = profileUrl?.match(/\\/in\\/([^/?#]+)/);\n const slug = slugMatch && !/^AC[ow]/.test(slugMatch[1]) ? slugMatch[1] : null;\n const person = {\n entity_id: entityId,\n name: text(after.full_name) ?? text(before.full_name),\n linkedin_url: url,\n public_identifier: slug,\n linkedin_entity_urn: text(after.urn) ?? text(before.urn),\n category: null,\n last_seen_at: ts,\n vendor: \"limadata\",\n location: text(after.location?.text) ?? text(before.location?.text),\n };\n // company names live only in the snapshot experiences; map linkedin company id -> name\n // so dashboards can resolve the numeric company_hint values\n const companies = new Map();\n for (const exp of [...(after.experiences ?? []), ...(before.experiences ?? [])]) {\n const cid = text(exp?.company_id);\n const cname = text(exp?.company);\n if (!cid || !cname || companies.has(cid)) continue;\n companies.set(cid, {\n company_entity_id: await uuid5(`https://linkedin.com/company/${cid}`),\n name: cname,\n linkedin_company_id: cid,\n last_seen_at: ts,\n vendor: \"limadata\",\n });\n }\n const rows: any[] = [];\n const observedOn = ts.slice(0, 10);\n for (const c of data.changes ?? []) {\n const prop = text(c?.property_name);\n const prev = c?.before ?? null;\n const curr = c?.after ?? null;\n if (!prop || (prev === null && curr === null)) continue;\n const changeType = prev === null ? \"added\" : curr === null ? \"removed\" : \"updated\";\n const obj = curr !== null ? curr : prev;\n let path = \"/\" + prop;\n if (prop === \"experiences\" && obj && typeof obj === \"object\") {\n path = `/experiences/${obj.company_id || \"x\"}@${obj.starts_at || \"\"}@${slugify(obj.title)}`;\n }\n const base = prop.split(/[\\[.]/)[0];\n const section = base === \"experiences\" ? \"experience\" : base;\n const hint = obj && typeof obj === \"object\" ? text(obj.company) ?? text(obj.company_id) : null;\n rows.push({\n raw_payload_id: String(rawId),\n entity_id: entityId,\n observed_on: observedOn,\n detected_at: ts,\n field_path: path,\n section,\n company_hint: hint,\n change_type: changeType,\n previous_value: jsonOrNull(prev ?? undefined) ?? null,\n current_value: jsonOrNull(curr ?? undefined) ?? null,\n vendor: \"limadata\",\n // location.text section changes are noise by definition; experience pairs\n // are re-checked below once both sides of the pair are collected\n noise: section === \"location\" ? \"true\" : \"false\",\n _raw: obj,\n });\n }\n markLocationNoise(rows);\n for (const r of rows) delete r._raw;\n return { person, companies, rows };\n}\n\nasync function processLimadataBatch(batch: any[]) {\n // Normalize in JS first so shape errors are isolated per payload without SQL.\n const people = new Map();\n const companies = new Map();\n const rows: any[] = [];\n const doneIds: string[] = [];\n for (const row of batch) {\n const rawId = Number(row.id);\n try {\n const { person, companies: comps, rows: r } = await normLimadata(rawId, row.payload);\n people.set(person.entity_id, person);\n for (const [cid, c] of comps) if (!companies.has(cid)) companies.set(cid, c);\n rows.push(...r);\n doneIds.push(String(rawId));\n } catch (error) {\n const message = error instanceof Error ? error.message : String(error);\n console.error(`limadata payload ${rawId} failed:`, message);\n await sql`update thenetwork.raw_payloads set processing_error = ${message} where id = ${String(rawId)}::bigint`;\n }\n }\n if (!doneIds.length) return { payloads: 0, changes: 0 };\n await sql.begin(async (tx: any) => {\n for (const batch of chunks([...companies.values()])) {\n const col = (k: string) => batch.map((c) => c[k]);\n await tx`\n insert into thenetwork.companies (company_entity_id, name, linkedin_company_id, last_seen_at, vendor)\n select * from unnest(\n ${col(\"company_entity_id\")}::uuid[], ${col(\"name\")}::text[],\n ${col(\"linkedin_company_id\")}::text[], ${col(\"last_seen_at\")}::timestamptz[], ${col(\"vendor\")}::text[])\n on conflict (company_entity_id) do update set\n name = coalesce(excluded.name, thenetwork.companies.name),\n last_seen_at = greatest(excluded.last_seen_at, thenetwork.companies.last_seen_at)`;\n }\n await upsertPeople(tx, people);\n await insertChanges(tx, rows);\n await tx`update thenetwork.raw_payloads set processed_at = now() where id = any(${doneIds}::bigint[])`;\n });\n return { payloads: doneIds.length, changes: rows.length };\n}\n\nexport default async function () {\n const pending = await sql`\n select id, schema_version, payload from thenetwork.raw_payloads\n where processed_at is null and processing_error is null and schema_version like ${\"greycroft.%\"}\n order by id limit 20`;\n for (const row of pending) {\n const rawId = Number(row.id);\n try {\n const stats = await sql.begin(async (tx: any) => {\n let stats: unknown;\n if (row.schema_version.startsWith(\"greycroft.profile_changes.\")) {\n stats = await processProfileChanges(tx, rawId, row.payload);\n } else if (row.schema_version.startsWith(\"greycroft.company_tracker_validated_net_new.\")) {\n stats = await processNetNew(tx, rawId, row.payload);\n } else {\n throw new Error(`no handler for schema_version ${row.schema_version}`);\n }\n await tx`update thenetwork.raw_payloads set processed_at = now() where id = ${String(rawId)}::bigint`;\n return stats;\n });\n console.log(`processed ${rawId} (${row.schema_version}):`, JSON.stringify(stats));\n } catch (error) {\n const message = error instanceof Error ? error.message : String(error);\n console.error(`payload ${rawId} failed:`, message);\n await sql`update thenetwork.raw_payloads set processing_error = ${message} where id = ${String(rawId)}::bigint`;\n }\n }\n\n const lima = await sql`\n select id, payload from thenetwork.raw_payloads\n where vendor = 'limadata' and processed_at is null and processing_error is null\n order by id limit 300`;\n if (lima.length) {\n const stats = await processLimadataBatch([...lima]);\n console.log(`limadata:`, JSON.stringify(stats));\n }\n console.log(`done: ${pending.length} greycroft + ${lima.length} limadata payload(s) examined`);\n}\n","createdAt",["D",1788556715953],"branchVersion","branchId","c987e021-bdd4-47b4-ac1b-6394d01f439a","id","6f48da3c-9820-44c4-a98d-da86a8d32458","raw-payload-webhook",["D",1788273667302],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Raw webhook landing: jsonb into Railway Postgres","imageUrl","a9bbe31c-d4b2-41f1-8950-ffb2490d13ff","pinnedFileId","31548dfe-a613-11f1-9ed1-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019f9680-ede1-71fd-8189-f4ace7fc6bd1","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_97":96,"_104":99},"org",{"_97":96,"_98":99,"_100":99,"_18":55,"_101":102,"_103":25},"branch",{"_13":86,"_32":87,"_43":42,"_88":44,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":45,"_95":96},"isOwned",145,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","androo","handle","displayName","","avatar","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_43":44,"_32":45,"_38":314,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":59,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":-5,"_67":-5,"_68":-5,"_69":-5,"_70":71,"_72":71,"_73":74,"_75":74,"_76":74,"_77":71,"_78":71,"_79":315,"_81":316,"_83":317,"_85":25},"tree",[257,258,259,260,261,262,263,264,265,266,267,268,269,270],"files",[257,258,259,260,261,262,263,264,265,266,267,268,269,270],"counts",{"_246":63,"_247":203,"_248":63,"_249":250,"_251":252},"contents",[126,127,128,129,130,131,132,133,134,135,136,137,138,139],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":239,"_32":240,"_30":241,"_143":-5,"_41":42,"_40":242,"_145":59,"_38":243,"_13":63,"_33":148,"_35":244,"_24":25,"_150":-5,"_27":245},{"_43":231,"_32":232,"_30":233,"_143":-5,"_41":42,"_40":234,"_145":59,"_38":235,"_13":236,"_33":20,"_35":237,"_24":25,"_150":-5,"_27":238},{"_43":223,"_32":224,"_30":225,"_143":-5,"_41":42,"_40":226,"_145":59,"_38":227,"_13":228,"_33":204,"_35":229,"_24":25,"_150":-5,"_27":230},{"_43":215,"_32":216,"_30":217,"_143":-5,"_41":42,"_40":218,"_145":25,"_38":219,"_13":220,"_33":20,"_35":221,"_24":25,"_150":-5,"_27":222},{"_43":207,"_32":208,"_30":209,"_143":-5,"_41":42,"_40":210,"_145":25,"_38":211,"_13":212,"_33":204,"_35":213,"_24":25,"_150":-5,"_27":214},{"_43":199,"_32":200,"_30":57,"_143":-5,"_41":42,"_40":201,"_145":59,"_38":202,"_13":203,"_33":204,"_35":205,"_24":25,"_150":-5,"_27":206},{"_43":195,"_32":28,"_30":31,"_143":-5,"_41":42,"_40":14,"_145":59,"_38":196,"_13":197,"_33":34,"_35":37,"_24":25,"_150":-5,"_27":198},{"_43":188,"_32":189,"_30":190,"_143":-5,"_41":42,"_40":191,"_145":59,"_38":192,"_13":63,"_33":148,"_35":193,"_24":25,"_150":-5,"_27":194},{"_43":181,"_32":182,"_30":183,"_143":-5,"_41":42,"_40":184,"_145":59,"_38":185,"_13":63,"_33":148,"_35":186,"_24":25,"_150":-5,"_27":187},{"_43":174,"_32":175,"_30":176,"_143":-5,"_41":42,"_40":177,"_145":59,"_38":178,"_13":63,"_33":148,"_35":179,"_24":25,"_150":-5,"_27":180},{"_43":166,"_32":167,"_30":168,"_143":-5,"_41":42,"_40":169,"_145":59,"_38":170,"_13":171,"_33":148,"_35":172,"_24":25,"_150":-5,"_27":173},{"_43":159,"_32":160,"_30":161,"_143":-5,"_41":42,"_40":162,"_145":59,"_38":163,"_13":63,"_33":148,"_35":164,"_24":25,"_150":-5,"_27":165},{"_43":152,"_32":153,"_30":154,"_143":-5,"_41":42,"_40":155,"_145":59,"_38":156,"_13":63,"_33":148,"_35":157,"_24":25,"_150":-5,"_27":158},{"_43":140,"_32":141,"_30":142,"_143":-5,"_41":42,"_40":144,"_145":59,"_38":146,"_13":147,"_33":148,"_35":149,"_24":25,"_150":-5,"_27":151},"cd210d48-f836-4062-9669-5f793a4d3485","selftest-ui.ts","766281c8-a63e-11f1-8c17-1607ee4eb77e","parentId",93,"latest",["D",1788378557981],10,"script","// Dev smoke test for the dashboard HTML scripts: runs each page's \n\n\n",[216],"81ee6874-c25f-480e-a30b-51acd7db14a0","dashboard-lima.ts","560e1cec-a63a-11f1-80d0-1607ee4eb77e",130,["D",1788384415311],11,"// Limadata signal dashboard: the vendor-comparison twin of dashboard.ts, scoped to\n// vendor='limadata' rows in the shared Layer 2 tables (people/profile_changes).\n// Limadata has no company tracker, so there are no detections here; the change feed\n// is capped at the latest 6000 rows (~100k total) — stats always cover everything.\n// Read-gated by the same FEED_TOKEN (?key=).\nimport { readFile } from \"https://esm.town/v/std/utils/index.ts\";\nimport postgres from \"npm:postgres\";\n\nconst sql = postgres(Deno.env.get(\"DATABASE_URL\")!, {\n max: 1,\n idle_timeout: 20,\n connect_timeout: 10,\n});\n\n// values go to the browser as real JSON (not truncated jsonb::text) so the UI can\n// render word-level diffs; prune caps long strings and drops image/logo noise\nconst STRIP = new Set([\"logo_url\", \"all_images\", \"profile_url\"]);\nfunction prune(v: any, top = true): any {\n if (typeof v === \"string\") {\n const cap = top ? 1500 : 400;\n return v.length > cap ? v.slice(0, cap) + \" …\" : v;\n }\n if (Array.isArray(v)) return v.slice(0, 12).map((x) => prune(x, false));\n if (v && typeof v === \"object\") {\n const out: Record = {};\n for (const [k, val] of Object.entries(v)) {\n if (STRIP.has(k)) continue;\n out[k] = prune(val, false);\n }\n return out;\n }\n return v;\n}\n\nexport default async function (req: Request): Promise {\n const url = new URL(req.url);\n const token = Deno.env.get(\"FEED_TOKEN\") ?? \"\";\n if (!token || url.searchParams.get(\"key\") !== token) {\n return new Response(\"forbidden\", { status: 403 });\n }\n\n if (url.pathname.endsWith(\"/data.json\")) {\n const [stats] = await sql`select\n (select count(*) from thenetwork.people where vendor = 'limadata')::int as people,\n (select count(distinct company_hint) from thenetwork.profile_changes\n where vendor = 'limadata' and company_hint is not null)::int as companies,\n (select count(*) from thenetwork.profile_changes where vendor = 'limadata' and not noise)::int as changes,\n (select count(*) from thenetwork.profile_changes where vendor = 'limadata' and noise)::int as noise,\n (select count(*) from thenetwork.raw_payloads where vendor = 'limadata')::int as payloads,\n (select max(observed_on) from thenetwork.profile_changes where vendor = 'limadata' and not noise)::text as latest_observed,\n (select max(received_at) from thenetwork.raw_payloads where vendor = 'limadata') as latest_delivery,\n (select count(*) from thenetwork.raw_payloads\n where vendor = 'limadata' and processed_at is null and processing_error is null)::int as pending,\n (select count(*) from thenetwork.raw_payloads\n where vendor = 'limadata' and processing_error is not null)::int as errors,\n (select payload->'watch'->>'name' from thenetwork.raw_payloads\n where vendor = 'limadata' and payload ? 'watch' order by id desc limit 1) as watch_name`;\n\n const sections = await sql`\n select coalesce(section, '(none)') as section, count(*)::int as n\n from thenetwork.profile_changes where vendor = 'limadata' and not noise\n group by 1 order by n desc`;\n\n // company_hint holds Limadata's numeric linkedin company id; resolve names via\n // the companies table (populated from payload snapshot experiences)\n const by_company = await sql`\n select coalesce(co.name, ch.company_hint) as company, count(*)::int as n\n from thenetwork.profile_changes ch\n left join thenetwork.companies co\n on co.vendor = 'limadata' and co.linkedin_company_id = ch.company_hint\n where ch.vendor = 'limadata' and not ch.noise and ch.company_hint is not null\n group by 1 order by n desc limit 14`;\n\n const payloads = await sql`\n select id::text, received_at, payload->'data'->'after'->>'full_name' as person,\n processed_at, processing_error, length(payload::text)::int as bytes\n from thenetwork.raw_payloads\n where vendor = 'limadata'\n order by id desc limit 20`;\n\n const changes = await sql`\n select ch.observed_on::text, ch.detected_at, ch.section,\n coalesce(co.name, ch.company_hint) as company_hint,\n ch.field_path, ch.change_type, ch.previous_value, ch.current_value,\n p.name, p.category as person_category, p.location,\n case when p.public_identifier is not null\n then 'https://www.linkedin.com/in/' || p.public_identifier\n else p.linkedin_url end as linkedin_url\n from thenetwork.profile_changes ch\n join thenetwork.people p using (entity_id)\n left join thenetwork.companies co\n on co.vendor = 'limadata' and co.linkedin_company_id = ch.company_hint\n where ch.vendor = 'limadata' and not ch.noise\n order by ch.detected_at desc nulls last, ch.id desc\n limit 6000`;\n\n return Response.json({\n generated_at: new Date().toISOString(),\n stats,\n sections,\n by_company,\n payloads,\n changes: changes.map((c: any) => ({\n ...c,\n previous_value: prune(c.previous_value),\n current_value: prune(c.current_value),\n })),\n });\n }\n\n return new Response(await readFile(\"/dashboard-lima.html\", import.meta.url), {\n headers: { \"content-type\": \"text/html; charset=utf-8\" },\n });\n}\n",[224],"d90a10c9-0968-4e71-9170-e675a214197c","dashboard-lima.html","7d0ade66-a63a-11f1-9ecb-1607ee4eb77e",108,["D",1788379333604],14,"\n\n\n\n\nLimadata: Profile Signal\n\n\n

Loading signal data…

\n\n\n\n",[232],"4c19816a-d9dc-458a-9ea4-4f0120112117","backfill-lima-slugs.ts","befb4802-a715-11f1-a8ab-1607ee4eb77e",133,["D",1788384726534],"// One-off backfill: limadata people.public_identifier from the vanity slug in the\n// raw payloads' after/before.profile_url (data.url is urn-based for many profiles).\n// Matches the extraction now in processor.ts normLimadata(). Idempotent.\nimport postgres from \"npm:postgres\";\n\nconst sql = postgres(Deno.env.get(\"DATABASE_URL\")!, { max: 1, idle_timeout: 5, connect_timeout: 10 });\n\nconst res = await sql`\n update thenetwork.people p\n set public_identifier = s.slug\n from (\n select distinct on (coalesce(payload->'data'->>'url', payload->'data'->>'external_id'))\n coalesce(payload->'data'->>'url', payload->'data'->>'external_id') as url,\n substring(coalesce(payload->'data'->'after'->>'profile_url',\n payload->'data'->'before'->>'profile_url') from '/in/([^/?#]+)') as slug\n from thenetwork.raw_payloads\n where vendor = 'limadata'\n and coalesce(payload->'data'->'after'->>'profile_url',\n payload->'data'->'before'->>'profile_url') ~ '/in/'\n order by 1, id desc\n ) s\n where p.vendor = 'limadata' and p.public_identifier is null\n and p.linkedin_url = s.url\n and s.slug is not null and s.slug !~ '^AC[ow]'`;\nconsole.log(\"people updated:\", res.count);\n\nconst [left] = await sql`\n select count(*)::int as n from thenetwork.people\n where vendor = 'limadata' and public_identifier is null`;\nconsole.log(\"limadata people still without a slug:\", left.n);\nawait sql.end();\n",[240],"pulls","branches","forks","envVars",{"_253":255,"_256":63},"connections",{"_253":63,"_254":63},"count","unfilledCount",4,"emptyCount",{"_43":239,"_32":240,"_30":241,"_143":-5,"_41":42,"_40":242,"_145":59,"_38":311,"_13":63,"_33":148,"_35":244,"_24":25,"_150":-5,"_272":312,"_27":313},{"_43":231,"_32":232,"_30":233,"_143":-5,"_41":42,"_40":234,"_145":59,"_38":308,"_13":236,"_33":20,"_35":237,"_24":25,"_150":-5,"_272":309,"_27":310},{"_43":223,"_32":224,"_30":225,"_143":-5,"_41":42,"_40":226,"_145":59,"_38":305,"_13":228,"_33":204,"_35":229,"_24":25,"_150":-5,"_272":306,"_27":307},{"_43":215,"_32":216,"_30":217,"_143":-5,"_41":42,"_40":218,"_145":25,"_38":302,"_13":220,"_33":20,"_35":221,"_24":25,"_150":-5,"_272":303,"_27":304},{"_43":207,"_32":208,"_30":209,"_143":-5,"_41":42,"_40":210,"_145":25,"_38":299,"_13":212,"_33":204,"_35":213,"_24":25,"_150":-5,"_272":300,"_27":301},{"_43":199,"_32":200,"_30":57,"_143":-5,"_41":42,"_40":201,"_145":59,"_38":296,"_13":203,"_33":204,"_35":205,"_24":25,"_150":-5,"_272":297,"_27":298},{"_43":195,"_32":28,"_30":31,"_143":-5,"_41":42,"_40":14,"_145":59,"_38":293,"_13":197,"_33":34,"_35":37,"_24":25,"_150":-5,"_272":294,"_27":295},{"_43":188,"_32":189,"_30":190,"_143":-5,"_41":42,"_40":191,"_145":59,"_38":290,"_13":63,"_33":148,"_35":193,"_24":25,"_150":-5,"_272":291,"_27":292},{"_43":181,"_32":182,"_30":183,"_143":-5,"_41":42,"_40":184,"_145":59,"_38":287,"_13":63,"_33":148,"_35":186,"_24":25,"_150":-5,"_272":288,"_27":289},{"_43":174,"_32":175,"_30":176,"_143":-5,"_41":42,"_40":177,"_145":59,"_38":284,"_13":63,"_33":148,"_35":179,"_24":25,"_150":-5,"_272":285,"_27":286},{"_43":166,"_32":167,"_30":168,"_143":-5,"_41":42,"_40":169,"_145":59,"_38":281,"_13":171,"_33":148,"_35":172,"_24":25,"_150":-5,"_272":282,"_27":283},{"_43":159,"_32":160,"_30":161,"_143":-5,"_41":42,"_40":162,"_145":59,"_38":278,"_13":63,"_33":148,"_35":164,"_24":25,"_150":-5,"_272":279,"_27":280},{"_43":152,"_32":153,"_30":154,"_143":-5,"_41":42,"_40":155,"_145":59,"_38":275,"_13":63,"_33":148,"_35":157,"_24":25,"_150":-5,"_272":276,"_27":277},{"_43":140,"_32":141,"_30":142,"_143":-5,"_41":42,"_40":144,"_145":59,"_38":271,"_13":147,"_33":148,"_35":149,"_24":25,"_150":-5,"_272":273,"_27":274},["D",1788378557981],"children",[],[141],["D",1788378137760],[],[153],["D",1788296086727],[],[160],["D",1788556685208],[],[167],["D",1788384447548],[],[175],["D",1788381362561],[],[182],["D",1788381861334],[],[189],["D",1788556715953],[],[28],["D",1788275057394],[],[200],["D",1788384398373],[],[208],["D",1788383487766],[],[216],["D",1788384415311],[],[224],["D",1788379333604],[],[232],["D",1788384726534],[],[240],["D",1788273667302],{"_97":96,"_104":99},{"_97":96,"_98":99,"_100":99,"_18":55,"_101":102,"_103":25},{"_13":86,"_32":87,"_43":42,"_88":44,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":45,"_95":96},"ctx",{"_79":-5,"_123":351,"_352":353,"_354":355,"_356":25,"_357":25,"_358":25,"_359":59,"_360":-5,"_361":-7,"_362":-7,"_363":63,"_364":25,"_365":25,"_366":-5,"_367":25,"_368":25,"_369":25,"_370":-5},"ENV",{"_322":323,"_324":325,"_326":87,"_327":328,"_329":330,"_331":332,"_333":334,"_335":336,"_337":338,"_339":340,"_341":342,"_343":344,"_345":346,"_347":348,"_349":350},"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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v138