#!/usr/bin/env bun /** * backfill-address-bounds — one-off data script (LOCAL DB -> SQL file). * * Reads a LOCAL SQLite copy of the D1 database, resolves every address to the * bound of its region that contains its coordinates (lowest priority wins, * exactly like `resolveRegionAndBound` in src/lib/region-geo.ts), and emits a * plain SQL file: * * UPDATE addresses SET bound_id = 2 WHERE id = 123; * * Run the generated file against the remote DB yourself: * * wrangler d1 execute --remote \ * --config wrangler.prod.toml --file address-bound-updates.sql * * Usage (from the repo root): * * bun run address_util/backfill-address-bounds.ts * bun run address_util/backfill-address-bounds.ts --db apps/backend/dumps/copy.sqlite \ * --out /tmp/address-bound-updates.sql * bun run address_util/backfill-address-bounds.ts --include-vague * bun run address_util/backfill-address-bounds.ts --keep-outside * * Options: * --db Local SQLite file. Default: auto-discovers the D1 file * under .wrangler/state/v3/d1/miniflare-D1DatabaseObject. * --out Output .sql file. Default: address_util/address-bound-updates.sql * --include-vague Also resolve addresses whose coordinates are the migration's * fabricated region centre (vague_backfilled_coords = 1). * --keep-outside Do not touch addresses that fall outside every polygon. * Default: set bound_id = NULL for them. * --help Show this help. * * Nothing is written to the database: the only side effect is the .sql file. */ import { Database } from 'bun:sqlite' import { existsSync, mkdirSync, readdirSync, writeFileSync } from 'node:fs' import { dirname, join, resolve } from 'node:path' import { booleanPointInPolygon } from '@turf/boolean-point-in-polygon' import { point as turfPoint, polygon as turfPolygon } from '@turf/helpers' const USAGE = `backfill-address-bounds — emit UPDATE statements for addresses.bound_id Usage: bun run address_util/backfill-address-bounds.ts [options] Options: --db Local SQLite file (default: auto-discover .wrangler D1 file) --out Output .sql file (default: address_util/address-bound-updates.sql) --include-vague Include vague_backfilled_coords = 1 rows --keep-outside Leave addresses that fall outside all polygons untouched --help Show this help ` const argv = process.argv.slice(2) const has = (name: string) => argv.includes(`--${name}`) if (has('help') || has('h')) { console.log(USAGE) process.exit(0) } function option(name: string): string | undefined { const eq = argv.find((arg) => arg.startsWith(`--${name}=`)) if (eq) return eq.slice(name.length + 3) const index = argv.indexOf(`--${name}`) if (index !== -1 && argv[index + 1] && !argv[index + 1].startsWith('--')) { return argv[index + 1] } return undefined } const REPO_ROOT = resolve(import.meta.dir, '..') const BACKEND_DIR = join(REPO_ROOT, 'apps/backend') const includeVague = has('include-vague') const keepOutside = has('keep-outside') /* -------------------------------------------------------------------------- */ /* Resolve the local DB + output paths */ /* -------------------------------------------------------------------------- */ function discoverLocalDb(): string { const d1Dir = join(BACKEND_DIR, '.wrangler/state/v3/d1/miniflare-D1DatabaseObject') if (!existsSync(d1Dir)) { throw new Error(`No local D1 directory at ${d1Dir}. Pass --db .`) } const files = readdirSync(d1Dir).filter( (file) => file.endsWith('.sqlite') && !file.startsWith('metadata') ) for (const file of files) { const fullPath = join(d1Dir, file) try { // Opened read-write: a WAL database cannot be opened read-only when the // `-shm` file is absent (server stopped). This script never writes. const db = new Database(fullPath) const columns = db .query("PRAGMA table_info('addresses')") .all() as Array<{ name: string }> db.close() const names = new Set(columns.map((column) => column.name)) if (names.has('bound_id') && names.has('vague_backfilled_coords')) { return fullPath } } catch { // not a database this script can use; try the next one } } throw new Error( `No local D1 SQLite file with addresses.bound_id + vague_backfilled_coords under ${d1Dir}. ` + `Pass --db . Files found: ${files.join(', ') || '(none)'}` ) } const dbPath = resolve(option('db') ?? discoverLocalDb()) const outPath = resolve(option('out') ?? join(import.meta.dir, 'address-bound-updates.sql')) if (!existsSync(dbPath)) { throw new Error(`SQLite file not found: ${dbPath}`) } /* -------------------------------------------------------------------------- */ /* Load rows */ /* -------------------------------------------------------------------------- */ interface BoundRow { id: number region_id: number priority: number geojson: string | null } interface AddressRow { id: number region_id: number | null latitude: number | null longitude: number | null bound_id: number | null vague: number } const db = new Database(dbPath) const addressColumns = ( db.query("PRAGMA table_info('addresses')").all() as Array<{ name: string }> ).map((column) => column.name) for (const required of ['id', 'region_id', 'latitude', 'longitude', 'bound_id']) { if (!addressColumns.includes(required)) { throw new Error( `addresses.${required} is missing in ${dbPath}. This script expects the regionalized schema.` ) } } const hasVagueColumn = addressColumns.includes('vague_backfilled_coords') const bounds = db .query( `SELECT id, region_id, CAST(priority AS REAL) AS priority, geojson FROM region_bounds WHERE geojson IS NOT NULL ORDER BY region_id ASC, priority ASC, id ASC` ) .all() as BoundRow[] const addresses = db .query( `SELECT id, region_id, latitude, longitude, bound_id, ${ hasVagueColumn ? 'vague_backfilled_coords' : '0' } AS vague FROM addresses ORDER BY id ASC` ) .all() as AddressRow[] db.close() /* -------------------------------------------------------------------------- */ /* Turn every bound into turf polygons (identical to boundToPolygons) */ /* -------------------------------------------------------------------------- */ type TurfPolygon = ReturnType interface PreparedBound { id: number regionId: number priority: number polygons: TurfPolygon[] } let invalidGeometries = 0 function polygonsOf(geojson: string): TurfPolygon[] { const polygons: TurfPolygon[] = [] let parsed: any try { parsed = JSON.parse(geojson) } catch { invalidGeometries += 1 return polygons } for (const feature of parsed?.features ?? []) { const geometry = feature?.geometry if (!geometry) continue if (geometry.type === 'Polygon') { polygons.push(turfPolygon(geometry.coordinates)) } else if (geometry.type === 'MultiPolygon') { for (const coordinates of geometry.coordinates) { polygons.push(turfPolygon(coordinates)) } } } return polygons } const boundsByRegion = new Map() for (const bound of bounds) { const prepared: PreparedBound = { id: bound.id, regionId: bound.region_id, priority: Number(bound.priority), polygons: polygonsOf(bound.geojson!), } if (prepared.polygons.length === 0) continue const list = boundsByRegion.get(prepared.regionId) ?? [] list.push(prepared) boundsByRegion.set(prepared.regionId, list) } for (const list of boundsByRegion.values()) { list.sort((a, b) => a.priority - b.priority || a.id - b.id) } /* -------------------------------------------------------------------------- */ /* Resolve + build the UPDATE statements */ /* -------------------------------------------------------------------------- */ type Resolution = | { kind: 'bound'; boundId: number } | { kind: 'outside' } | { kind: 'no-bounds' } function resolveBound(address: AddressRow): Resolution { if (address.region_id == null) return { kind: 'no-bounds' } const candidates = boundsByRegion.get(address.region_id) if (!candidates || candidates.length === 0) return { kind: 'no-bounds' } const location = turfPoint([address.longitude!, address.latitude!]) for (const bound of candidates) { if (bound.polygons.some((poly) => booleanPointInPolygon(location, poly))) { return { kind: 'bound', boundId: bound.id } } } return { kind: 'outside' } } const updates: string[] = [] const stats = { total: addresses.length, skippedNoCoords: 0, skippedVague: 0, skippedNoBounds: 0, unchanged: 0, outside: 0, outsideKept: 0, bound1: 0, bound2: 0, boundOther: 0, } const perBound = new Map() for (const address of addresses) { if (address.latitude == null || address.longitude == null) { stats.skippedNoCoords += 1 continue } if (address.vague === 1 && !includeVague) { stats.skippedVague += 1 continue } const resolution = resolveBound(address) if (resolution.kind === 'no-bounds') { stats.skippedNoBounds += 1 continue } if (resolution.kind === 'outside') { stats.outside += 1 if (keepOutside) { stats.outsideKept += 1 continue } if (address.bound_id == null) { stats.unchanged += 1 continue } updates.push(`UPDATE addresses SET bound_id = NULL WHERE id = ${address.id};`) continue } if (address.bound_id === resolution.boundId) { stats.unchanged += 1 continue } if (resolution.boundId === 1) stats.bound1 += 1 else if (resolution.boundId === 2) stats.bound2 += 1 else stats.boundOther += 1 const list = perBound.get(resolution.boundId) ?? [] list.push(address.id) perBound.set(resolution.boundId, list) updates.push( `UPDATE addresses SET bound_id = ${resolution.boundId} WHERE id = ${address.id};` ) } /* -------------------------------------------------------------------------- */ /* Emit the SQL file */ /* -------------------------------------------------------------------------- */ const generatedAt = new Date().toISOString() const outsideMode = keepOutside ? 'kept as-is' : 'set to NULL' const header = [ '-- Generated by address_util/backfill-address-bounds.ts', `-- generated_at: ${generatedAt}`, `-- source_db: ${dbPath}`, `-- thresholds: vague_backfilled_coords excluded=${!includeVague}, outside=${outsideMode}`, `-- addresses: total=${stats.total} no_coords=${stats.skippedNoCoords} vague=${stats.skippedVague} no_region_bounds=${stats.skippedNoBounds}`, `-- unchanged: ${stats.unchanged}`, `-- outside: ${stats.outside}${keepOutside ? ` (kept ${stats.outsideKept})` : ''}`, `-- updates: ${updates.length}`, `-- resolved: bound=${stats.bound1 + stats.bound2 + stats.boundOther} (1:${stats.bound1}, 2:${stats.bound2}, other:${stats.boundOther})`, '--', '-- Run against the remote DB with the generated file:', '-- wrangler d1 execute --remote --config wrangler.prod.toml --file ', '--', '', ].join('\n') const body = updates.length > 0 ? updates.join('\n') + '\n' : '-- nothing to update\n' mkdirSync(dirname(outPath), { recursive: true }) writeFileSync(outPath, header + body, 'utf8') /* -------------------------------------------------------------------------- */ /* Report */ /* -------------------------------------------------------------------------- */ console.log(`source db : ${dbPath}`) console.log(`output : ${outPath}`) console.log( `addresses : ${stats.total} (no coords ${stats.skippedNoCoords}, vague ${stats.skippedVague}, no region bounds ${stats.skippedNoBounds})` ) console.log(`outside : ${stats.outside} (${outsideMode})`) console.log(`unchanged : ${stats.unchanged}`) console.log( `updates : ${updates.length}` + (perBound.size ? ` (${[...perBound.entries()] .sort((a, b) => a[0] - b[0]) .map(([boundId, ids]) => `bound ${boundId}: ${ids.length}`) .join(', ')})` : '') ) if (invalidGeometries > 0) { console.warn(`warning : ${invalidGeometries} bound geometry row(s) were not valid JSON; skipped.`) } console.log('') console.log('Apply it with:') console.log( ` wrangler d1 execute --remote --config wrangler.prod.toml --file "${outPath}"` )