[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63,"_64":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","1f1a009cadcf7353a37b999b92a7f8af633192ea","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_155":156,"_177":178,"_179":-5},"handle","shapedlines","name","loki","path","backend/dedup.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":117,"_119":-5,"_120":33,"_14":121,"_122":-5,"_123":-5,"_124":125,"_126":125,"_127":128,"_129":128,"_130":128,"_131":125,"_132":125,"_23":133,"_136":137,"_141":142,"_154":20},"id","d3df2ae6-4d5b-11f0-b336-76b3cceeab13","createdAt",["D",1750371724044],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","7ed186b0-c78a-4757-a5b0-12c00ac89afa","pinnedFileId","9cf06f84-4d5d-11f0-a672-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","e9195859-3c6b-4b40-bb3e-c11844477fdf","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_134":135},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yYlc2Q1A2eUFyNEdDeXBud0E0bFJyWlJVUzQifQ","org",{"_95":96,"_138":135,"_12":113,"_139":140},"avatar","tier","free","branch",{"_143":144,"_97":45,"_103":145,"_146":104,"_147":-5,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":98,"_153":96},"version",336,"d3df7564-4d5b-11f0-b336-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":157,"_97":158,"_159":160,"_161":162,"_163":145,"_164":165,"_166":117,"_105":167,"_143":168,"_169":170,"_171":172,"_173":20,"_174":-5,"_99":175},"a924a89a-f80e-11f0-89c3-42dde27851f2","dedup.ts","fileId","7d3c1054-f806-11f0-818c-42dde27851f2","parentId","7c759398-f806-11f0-be4c-42dde27851f2","branchId","branchVersion",326,"latest",["D",1769140229936],2,"fileType","script","content","/**\n * Deduplication engine for Loki\n * Handles address normalization and matching to prevent duplicate listings\n */\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\nimport type { Listing, ListingSource, ScrapedListing, AddressFingerprint } from \"../shared/types.ts\";\nimport { rowToObject, rowsToObjects } from \"./db-utils.ts\";\n\nconst TABLE_PREFIX = \"loki_\";\n\n// Street suffix mappings for normalization\nconst STREET_SUFFIXES: Record = {\n \"street\": \"st\",\n \"st.\": \"st\",\n \"avenue\": \"ave\",\n \"ave.\": \"ave\",\n \"boulevard\": \"blvd\",\n \"blvd.\": \"blvd\",\n \"drive\": \"dr\",\n \"dr.\": \"dr\",\n \"lane\": \"ln\",\n \"ln.\": \"ln\",\n \"road\": \"rd\",\n \"rd.\": \"rd\",\n \"court\": \"ct\",\n \"ct.\": \"ct\",\n \"place\": \"pl\",\n \"pl.\": \"pl\",\n \"way\": \"way\",\n \"circle\": \"cir\",\n \"cir.\": \"cir\",\n \"terrace\": \"ter\",\n \"ter.\": \"ter\",\n \"parkway\": \"pkwy\",\n \"pkwy.\": \"pkwy\",\n \"highway\": \"hwy\",\n \"hwy.\": \"hwy\",\n \"square\": \"sq\",\n \"sq.\": \"sq\",\n};\n\n// Unit identifier patterns to standardize\nconst UNIT_PATTERNS = [\n /\\b(?:apt|apartment)\\.?\\s*#?\\s*(\\w+)/i,\n /\\b(?:unit)\\.?\\s*#?\\s*(\\w+)/i,\n /\\b(?:suite|ste)\\.?\\s*#?\\s*(\\w+)/i,\n /\\b(?:floor|fl)\\.?\\s*#?\\s*(\\w+)/i,\n /\\b#\\s*(\\w+)/i,\n /\\bno\\.?\\s*(\\w+)/i,\n];\n\n/**\n * Generate unique ID\n */\nfunction generateId(): string {\n return `${Date.now()}-${Math.random().toString(36).substring(2, 15)}`;\n}\n\n/**\n * Normalize a street suffix\n */\nfunction normalizeStreetSuffix(word: string): string {\n const lower = word.toLowerCase();\n return STREET_SUFFIXES[lower] || lower;\n}\n\n/**\n * Extract and standardize unit number from address\n * Returns the unit identifier or null if none found\n */\nfunction extractUnit(address: string): { unit: string | null; addressWithoutUnit: string } {\n let unit: string | null = null;\n let addressWithoutUnit = address;\n\n for (const pattern of UNIT_PATTERNS) {\n const match = address.match(pattern);\n if (match) {\n unit = match[1].toLowerCase();\n // Remove the unit part from the address\n addressWithoutUnit = address.replace(pattern, \"\").trim();\n break;\n }\n }\n\n // Clean up any leftover punctuation/whitespace\n addressWithoutUnit = addressWithoutUnit.replace(/,\\s*,/g, \",\").replace(/\\s+/g, \" \").trim();\n\n return { unit, addressWithoutUnit };\n}\n\n/**\n * Normalize an address for fingerprint generation\n * - Lowercase\n * - Standardize street suffixes\n * - Extract and standardize unit numbers\n * - Remove extra whitespace and punctuation\n */\nexport function normalizeAddress(address: string): AddressFingerprint {\n // First extract the unit\n const { unit, addressWithoutUnit } = extractUnit(address);\n\n // Normalize the main address\n let normalized = addressWithoutUnit\n .toLowerCase()\n .replace(/[^\\w\\s]/g, \" \") // Remove punctuation except spaces\n .replace(/\\s+/g, \" \") // Normalize whitespace\n .trim();\n\n // Normalize street suffixes\n const words = normalized.split(\" \");\n const normalizedWords = words.map(normalizeStreetSuffix);\n const street = normalizedWords.join(\" \");\n\n // Generate fingerprint: street address + unit (if any)\n const fingerprint = unit ? `${street}|unit ${unit}` : street;\n\n return {\n street,\n unit,\n fingerprint,\n };\n}\n\n/**\n * Calculate distance between two lat/lng points in meters\n * Using Haversine formula\n */\nfunction calculateDistance(lat1: number, lng1: number, lat2: number, lng2: number): number {\n const R = 6371000; // Earth's radius in meters\n const dLat = (lat2 - lat1) * Math.PI / 180;\n const dLng = (lng2 - lng1) * Math.PI / 180;\n const a = Math.sin(dLat / 2) * Math.sin(dLat / 2) +\n Math.cos(lat1 * Math.PI / 180) * Math.cos(lat2 * Math.PI / 180) *\n Math.sin(dLng / 2) * Math.sin(dLng / 2);\n const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));\n return R * c;\n}\n\n// Type for raw listing row from database\ntype ListingRow = {\n id: string;\n address_original: string;\n address_normalized: string;\n unit_number: string | null;\n lat: number | null;\n lng: number | null;\n beds: number | null;\n baths: number | null;\n price: number | null;\n availability: string | null;\n neighborhood: string | null;\n first_seen_at: number;\n last_updated_at: number;\n is_active: number;\n};\n\n/**\n * Find existing listing by fingerprint match\n */\nasync function findByFingerprint(fingerprint: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT id, address_original, address_normalized, unit_number, lat, lng, beds, baths, price, availability, neighborhood, first_seen_at, last_updated_at, is_active FROM ${TABLE_PREFIX}listings WHERE address_normalized = ? LIMIT 1`,\n args: [fingerprint],\n });\n\n if (result.rows.length === 0) return null;\n\n const row = rowToObject(result.rows[0] as unknown[] | Record, result.columns);\n return {\n id: row.id,\n address_original: row.address_original,\n address_normalized: row.address_normalized,\n unit_number: row.unit_number,\n lat: row.lat,\n lng: row.lng,\n beds: row.beds,\n baths: row.baths,\n price: row.price,\n availability: row.availability,\n neighborhood: row.neighborhood,\n first_seen_at: row.first_seen_at,\n last_updated_at: row.last_updated_at,\n is_active: Boolean(row.is_active),\n };\n}\n\n/**\n * Find existing listing by geo proximity and unit match\n * Used as fallback when fingerprint doesn't match but might be same location\n */\nasync function findByGeoProximity(\n lat: number,\n lng: number,\n unit: string | null,\n beds: number | null,\n price: number | null\n): Promise {\n // Get nearby listings (within approximately 100m bounding box)\n const latDelta = 0.001; // ~111 meters\n const lngDelta = 0.001; // ~85 meters at SF latitude\n\n const result = await sqlite.execute({\n sql: `\n SELECT id, address_original, address_normalized, unit_number, lat, lng, beds, baths, price, availability, neighborhood, first_seen_at, last_updated_at, is_active FROM ${TABLE_PREFIX}listings \n WHERE lat BETWEEN ? AND ? \n AND lng BETWEEN ? AND ?\n AND is_active = 1\n `,\n args: [lat - latDelta, lat + latDelta, lng - lngDelta, lng + lngDelta],\n });\n\n const rows = rowsToObjects(result.rows as (unknown[] | Record)[], result.columns);\n\n for (const row of rows) {\n const existingLat = row.lat as number;\n const existingLng = row.lng as number;\n const existingUnit = row.unit_number;\n const existingBeds = row.beds;\n const existingPrice = row.price;\n\n // Check distance (< 50 meters)\n const distance = calculateDistance(lat, lng, existingLat, existingLng);\n if (distance > 50) continue;\n\n // Check unit match (both null or same value)\n if (unit !== existingUnit) continue;\n\n // Check beds match (if both have values)\n if (beds !== null && existingBeds !== null && beds !== existingBeds) continue;\n\n // Check price similarity (within 10% or $200)\n if (price !== null && existingPrice !== null) {\n const priceDiff = Math.abs(price - existingPrice);\n const pricePercent = priceDiff / Math.max(price, existingPrice);\n if (priceDiff > 200 && pricePercent > 0.1) continue;\n }\n\n // Found a match\n return {\n id: row.id,\n address_original: row.address_original,\n address_normalized: row.address_normalized,\n unit_number: existingUnit,\n lat: existingLat,\n lng: existingLng,\n beds: existingBeds,\n baths: row.baths,\n price: existingPrice,\n availability: row.availability,\n neighborhood: row.neighborhood,\n first_seen_at: row.first_seen_at,\n last_updated_at: row.last_updated_at,\n is_active: Boolean(row.is_active),\n };\n }\n\n return null;\n}\n\n/**\n * Check if a source URL already exists\n */\nasync function sourceExists(url: string): Promise {\n const result = await sqlite.execute({\n sql: `SELECT 1 FROM ${TABLE_PREFIX}listing_sources WHERE url = ?`,\n args: [url],\n });\n return result.rows.length > 0;\n}\n\n/**\n * Add a new source to an existing listing\n */\nasync function addSourceToListing(\n listingId: string,\n source: {\n platform: \"zillow\" | \"craigslist\";\n url: string;\n raw_address: string | null;\n }\n): Promise {\n const now = Math.floor(Date.now() / 1000);\n const sourceId = generateId();\n\n await sqlite.execute({\n sql: `\n INSERT INTO ${TABLE_PREFIX}listing_sources \n (id, listing_id, platform, url, raw_address, first_seen_at, last_seen_at)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `,\n args: [sourceId, listingId, source.platform, source.url, source.raw_address, now, now],\n });\n\n // Update listing's last_updated_at and mark as active\n await sqlite.execute({\n sql: `UPDATE ${TABLE_PREFIX}listings SET last_updated_at = ?, is_active = 1 WHERE id = ?`,\n args: [now, listingId],\n });\n}\n\n/**\n * Update source's last_seen_at timestamp\n */\nasync function updateSourceLastSeen(url: string): Promise {\n const now = Math.floor(Date.now() / 1000);\n await sqlite.execute({\n sql: `UPDATE ${TABLE_PREFIX}listing_sources SET last_seen_at = ? WHERE url = ?`,\n args: [now, url],\n });\n}\n\n/**\n * Create a new canonical listing\n */\nasync function createListing(\n scraped: ScrapedListing,\n fingerprint: AddressFingerprint\n): Promise {\n const now = Math.floor(Date.now() / 1000);\n const listingId = generateId();\n\n await sqlite.execute({\n sql: `\n INSERT INTO ${TABLE_PREFIX}listings \n (id, address_original, address_normalized, unit_number, lat, lng, beds, baths, price, availability, first_seen_at, last_updated_at, is_active)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)\n `,\n args: [\n listingId,\n scraped.address,\n fingerprint.fingerprint,\n fingerprint.unit,\n scraped.lat || null,\n scraped.lng || null,\n scraped.beds || null,\n scraped.baths || null,\n scraped.price || null,\n scraped.availability || null,\n now,\n now,\n ],\n });\n\n // Add the source\n await addSourceToListing(listingId, {\n platform: scraped.platform,\n url: scraped.url,\n raw_address: scraped.address,\n });\n\n return {\n id: listingId,\n address_original: scraped.address,\n address_normalized: fingerprint.fingerprint,\n unit_number: fingerprint.unit,\n lat: scraped.lat || null,\n lng: scraped.lng || null,\n beds: scraped.beds || null,\n baths: scraped.baths || null,\n price: scraped.price || null,\n availability: scraped.availability || null,\n neighborhood: null,\n first_seen_at: now,\n last_updated_at: now,\n is_active: true,\n };\n}\n\n/**\n * Process a scraped listing through deduplication\n * Returns the canonical listing (either existing or newly created)\n */\nexport async function processScrapedListing(scraped: ScrapedListing): Promise<{\n listing: Listing;\n isNew: boolean;\n source: \"fingerprint\" | \"geo\" | \"new\";\n}> {\n // Check if this exact URL already exists\n if (await sourceExists(scraped.url)) {\n // Update last_seen_at for the source\n await updateSourceLastSeen(scraped.url);\n \n // Find the listing for this source\n const sourceResult = await sqlite.execute({\n sql: `SELECT listing_id FROM ${TABLE_PREFIX}listing_sources WHERE url = ?`,\n args: [scraped.url],\n });\n \n if (sourceResult.rows.length > 0) {\n // Handle both array and object row formats\n const row = sourceResult.rows[0];\n const listingId = Array.isArray(row) ? row[0] as string : (row as Record).listing_id as string;\n \n if (listingId) {\n const listingResult = await sqlite.execute({\n sql: `SELECT address_normalized FROM ${TABLE_PREFIX}listings WHERE id = ?`,\n args: [listingId],\n });\n \n if (listingResult.rows.length > 0) {\n const listingRow = listingResult.rows[0];\n const addressNormalized = Array.isArray(listingRow) \n ? listingRow[0] as string \n : (listingRow as Record).address_normalized as string;\n \n if (addressNormalized) {\n const listing = await findByFingerprint(addressNormalized);\n if (listing && listing.id) {\n return { listing, isNew: false, source: \"fingerprint\" };\n }\n }\n }\n }\n }\n }\n\n // Normalize the address\n const fingerprint = normalizeAddress(scraped.address);\n\n // Try to find by fingerprint (exact address match)\n let existingListing = await findByFingerprint(fingerprint.fingerprint);\n if (existingListing && existingListing.id) {\n // Add this URL as a new source\n await addSourceToListing(existingListing.id, {\n platform: scraped.platform,\n url: scraped.url,\n raw_address: scraped.address,\n });\n return { listing: existingListing, isNew: false, source: \"fingerprint\" };\n }\n\n // Try to find by geo proximity (if coordinates available)\n if (scraped.lat && scraped.lng) {\n existingListing = await findByGeoProximity(\n scraped.lat,\n scraped.lng,\n fingerprint.unit,\n scraped.beds || null,\n scraped.price || null\n );\n if (existingListing && existingListing.id) {\n // Add this URL as a new source\n await addSourceToListing(existingListing.id, {\n platform: scraped.platform,\n url: scraped.url,\n raw_address: scraped.address,\n });\n return { listing: existingListing, isNew: false, source: \"geo\" };\n }\n }\n\n // No match found, create new listing\n const newListing = await createListing(scraped, fingerprint);\n return { listing: newListing, isNew: true, source: \"new\" };\n}\n\n/**\n * Mark listings as inactive if not seen in X days\n */\nexport async function markInactiveListings(daysThreshold: number = 7): Promise {\n const thresholdTimestamp = Math.floor(Date.now() / 1000) - (daysThreshold * 24 * 60 * 60);\n\n // Find listings where all sources haven't been seen recently\n const result = await sqlite.execute({\n sql: `\n UPDATE ${TABLE_PREFIX}listings \n SET is_active = 0 \n WHERE id IN (\n SELECT l.id \n FROM ${TABLE_PREFIX}listings l\n WHERE l.is_active = 1\n AND NOT EXISTS (\n SELECT 1 FROM ${TABLE_PREFIX}listing_sources s \n WHERE s.listing_id = l.id \n AND s.last_seen_at > ?\n )\n )\n `,\n args: [thresholdTimestamp],\n });\n\n return result.rowsAffected || 0;\n}\n\n/**\n * Batch process multiple scraped listings\n */\nexport async function processBatchListings(listings: ScrapedListing[]): Promise<{\n total: number;\n new: number;\n updated: number;\n errors: number;\n}> {\n const results = {\n total: listings.length,\n new: 0,\n updated: 0,\n errors: 0,\n };\n\n for (const listing of listings) {\n try {\n const { isNew } = await processScrapedListing(listing);\n if (isNew) {\n results.new++;\n } else {\n results.updated++;\n }\n } catch (error) {\n console.error(\"Error processing listing:\", listing.url, error);\n results.errors++;\n }\n }\n\n return results;\n}\n\nexport { findByFingerprint, findByGeoProximity, sourceExists };\n","deleted","label",[176,158],"backend","permalink","https://www.val.town/x/shapedlines/loki/code/backend/dedup.ts","extension","actionData","errors"]