← back to Commercialrealestate
crcp: bridge 2.43M assessor roll to DB-less prod via read-only SQLite + honest miss copy
13a73acf5894baa63bdb83ede35aff40e7b8d6d4 · 2026-07-07 13:11:50 -0700 · Steve
- export-assessor-sqlite.js builds data/assessor.sqlite (449MB, indexed on
situs_house_no+situs_street) from cre.assessor_parcel; keyset-paginated, DELETE
journal mode so it's a single self-contained read-only file (no WAL sidecars)
- serve.js /api/property-history: SQLite fallback when Postgres is absent (prod),
DB-first preserved; better-sqlite3 added as dep (npm ci rebuilds it on Linux)
- index.html/mls.html: replace false 'still ingesting' banner (roll landed
2026-06-30) with honest 'no assessor record matched' miss message
- gitignore the 449MB file (ships via rsync, not git)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M .gitignoreM package-lock.jsonM package.jsonM public/index.htmlM public/mls.htmlA scripts/export-assessor-sqlite.jsM scripts/serve.js
Diff
commit 13a73acf5894baa63bdb83ede35aff40e7b8d6d4
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 7 13:11:50 2026 -0700
crcp: bridge 2.43M assessor roll to DB-less prod via read-only SQLite + honest miss copy
- export-assessor-sqlite.js builds data/assessor.sqlite (449MB, indexed on
situs_house_no+situs_street) from cre.assessor_parcel; keyset-paginated, DELETE
journal mode so it's a single self-contained read-only file (no WAL sidecars)
- serve.js /api/property-history: SQLite fallback when Postgres is absent (prod),
DB-first preserved; better-sqlite3 added as dep (npm ci rebuilds it on Linux)
- index.html/mls.html: replace false 'still ingesting' banner (roll landed
2026-06-30) with honest 'no assessor record matched' miss message
- gitignore the 449MB file (ships via rsync, not git)
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
.gitignore | 5 +
package-lock.json | 427 ++++++++++++++++++++++++++++++++++++++
package.json | 1 +
public/index.html | 2 +-
public/mls.html | 2 +-
scripts/export-assessor-sqlite.js | 116 +++++++++++
scripts/serve.js | 24 +++
7 files changed, 575 insertions(+), 2 deletions(-)
diff --git a/.gitignore b/.gitignore
index a049bca..1d368ff 100644
--- a/.gitignore
+++ b/.gitignore
@@ -8,3 +8,8 @@ build/
.next/
data/raw/
data/morning-report.html
+# Assessor SQLite roll (~449MB) — shipped to prod via rsync, NOT git (would bloat history).
+# Rebuild locally with: node scripts/export-assessor-sqlite.js
+data/assessor.sqlite
+data/assessor.sqlite-shm
+data/assessor.sqlite-wal
diff --git a/package-lock.json b/package-lock.json
index d0abae9..c925eee 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -8,6 +8,7 @@
"name": "commercialrealestate",
"version": "0.3.0",
"dependencies": {
+ "better-sqlite3": "^12.11.1",
"express": "^4.22.2",
"pg": "^8.22.0",
"xlsx": "^0.18.5"
@@ -41,6 +42,60 @@
"integrity": "sha512-PCVAQswWemu6UdxsDFFX/+gVeYqKAod3D3UVm91jHwynguOwAvYPhx8nNlM++NqRcK6CxxpUafjmhIdKiHibqg==",
"license": "MIT"
},
+ "node_modules/base64-js": {
+ "version": "1.5.1",
+ "resolved": "https://registry.npmjs.org/base64-js/-/base64-js-1.5.1.tgz",
+ "integrity": "sha512-AKpaYlHn8t4SVbOHCy+b5+KKgvR4vrsD8vbvrbiQJps7fKDTkjkDry6ji0rUJjC0kzbNePLwzxq8iypo41qeWA==",
+ "funding": [
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/feross"
+ },
+ {
+ "type": "patreon",
+ "url": "https://www.patreon.com/feross"
+ },
+ {
+ "type": "consulting",
+ "url": "https://feross.org/support"
+ }
+ ],
+ "license": "MIT"
+ },
+ "node_modules/better-sqlite3": {
+ "version": "12.11.1",
+ "resolved": "https://registry.npmjs.org/better-sqlite3/-/better-sqlite3-12.11.1.tgz",
+ "integrity": "sha512-dq9AtApgg5PGFtBzPFSBl3HZQjHok5gaQCM6zh2Yk0aSmDCs1CbnVI8/HgASQkNKsWFpseIO9beg5xxpYhbIfA==",
+ "hasInstallScript": true,
+ "license": "MIT",
+ "dependencies": {
+ "bindings": "^1.5.0",
+ "prebuild-install": "^7.1.1"
+ },
+ "engines": {
+ "node": "20.x || 22.x || 23.x || 24.x || 25.x || 26.x"
+ }
+ },
+ "node_modules/bindings": {
+ "version": "1.5.0",
+ "resolved": "https://registry.npmjs.org/bindings/-/bindings-1.5.0.tgz",
+ "integrity": "sha512-p2q/t/mhvuOj/UeLlV6566GD/guowlr0hHxClI0W9m7MWYkL1F0hLo+0Aexs9HSPCtR1SXQ0TD3MMKrXZajbiQ==",
+ "license": "MIT",
+ "dependencies": {
+ "file-uri-to-path": "1.0.0"
+ }
+ },
+ "node_modules/bl": {
+ "version": "4.1.0",
+ "resolved": "https://registry.npmjs.org/bl/-/bl-4.1.0.tgz",
+ "integrity": "sha512-1W07cM9gS6DcLperZfFSj+bWLtaPGSOHWhPiGzXmvVJbRLdG82sH/Kn8EtW1VqWVA54AKf2h5k5BbnIbwF3h6w==",
+ "license": "MIT",
+ "dependencies": {
+ "buffer": "^5.5.0",
+ "inherits": "^2.0.4",
+ "readable-stream": "^3.4.0"
+ }
+ },
"node_modules/body-parser": {
"version": "1.20.5",
"resolved": "https://registry.npmjs.org/body-parser/-/body-parser-1.20.5.tgz",
@@ -65,6 +120,30 @@
"npm": "1.2.8000 || >= 1.4.16"
}
},
+ "node_modules/buffer": {
+ "version": "5.7.1",
+ "resolved": "https://registry.npmjs.org/buffer/-/buffer-5.7.1.tgz",
+ "integrity": "sha512-EHcyIPBQ4BSGlvjB16k5KgAJ27CIsHY/2JBmCRReo48y9rQ3MaUzWX3KVlBa4U7MyX02HdVj0K7C3WaB3ju7FQ==",
+ "funding": [
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/feross"
+ },
+ {
+ "type": "patreon",
+ "url": "https://www.patreon.com/feross"
+ },
+ {
+ "type": "consulting",
+ "url": "https://feross.org/support"
+ }
+ ],
+ "license": "MIT",
+ "dependencies": {
+ "base64-js": "^1.3.1",
+ "ieee754": "^1.1.13"
+ }
+ },
"node_modules/bytes": {
"version": "3.1.2",
"resolved": "https://registry.npmjs.org/bytes/-/bytes-3.1.2.tgz",
@@ -116,6 +195,12 @@
"node": ">=0.8"
}
},
+ "node_modules/chownr": {
+ "version": "1.1.4",
+ "resolved": "https://registry.npmjs.org/chownr/-/chownr-1.1.4.tgz",
+ "integrity": "sha512-jJ0bqzaylmJtVnNgzTeSOs8DPavpbYgEr/b0YL8/2GO3xJEhInFmhKMUnEJQjZumK7KXGFhUy89PrsJWlakBVg==",
+ "license": "ISC"
+ },
"node_modules/codepage": {
"version": "1.15.0",
"resolved": "https://registry.npmjs.org/codepage/-/codepage-1.15.0.tgz",
@@ -182,6 +267,30 @@
"ms": "2.0.0"
}
},
+ "node_modules/decompress-response": {
+ "version": "6.0.0",
+ "resolved": "https://registry.npmjs.org/decompress-response/-/decompress-response-6.0.0.tgz",
+ "integrity": "sha512-aW35yZM6Bb/4oJlZncMH2LCoZtJXTRxES17vE3hoRiowU2kWHaJKFkSBDnDR+cm9J+9QhXmREyIfv0pji9ejCQ==",
+ "license": "MIT",
+ "dependencies": {
+ "mimic-response": "^3.1.0"
+ },
+ "engines": {
+ "node": ">=10"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/sindresorhus"
+ }
+ },
+ "node_modules/deep-extend": {
+ "version": "0.6.0",
+ "resolved": "https://registry.npmjs.org/deep-extend/-/deep-extend-0.6.0.tgz",
+ "integrity": "sha512-LOHxIOaPYdHlJRtCQfDIVZtfw/ufM8+rVj649RIHzcm/vGwQRXFt6OPqIFWsm2XEMrNIEtWR64sY1LEKD2vAOA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
"node_modules/depd": {
"version": "2.0.0",
"resolved": "https://registry.npmjs.org/depd/-/depd-2.0.0.tgz",
@@ -201,6 +310,15 @@
"npm": "1.2.8000 || >= 1.4.16"
}
},
+ "node_modules/detect-libc": {
+ "version": "2.1.2",
+ "resolved": "https://registry.npmjs.org/detect-libc/-/detect-libc-2.1.2.tgz",
+ "integrity": "sha512-Btj2BOOO83o3WyH59e8MgXsxEQVcarkUOpEYrubB0urwnN10yQ364rsiByU11nZlqWYZm05i/of7io4mzihBtQ==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=8"
+ }
+ },
"node_modules/dunder-proto": {
"version": "1.0.1",
"resolved": "https://registry.npmjs.org/dunder-proto/-/dunder-proto-1.0.1.tgz",
@@ -230,6 +348,15 @@
"node": ">= 0.8"
}
},
+ "node_modules/end-of-stream": {
+ "version": "1.4.5",
+ "resolved": "https://registry.npmjs.org/end-of-stream/-/end-of-stream-1.4.5.tgz",
+ "integrity": "sha512-ooEGc6HP26xXq/N+GCGOT0JKCLDGrq2bQUZrQ7gyrJiZANJ/8YDTxTpQBXGMn+WbIQXNVpyWymm7KYVICQnyOg==",
+ "license": "MIT",
+ "dependencies": {
+ "once": "^1.4.0"
+ }
+ },
"node_modules/es-define-property": {
"version": "1.0.1",
"resolved": "https://registry.npmjs.org/es-define-property/-/es-define-property-1.0.1.tgz",
@@ -275,6 +402,15 @@
"node": ">= 0.6"
}
},
+ "node_modules/expand-template": {
+ "version": "2.0.3",
+ "resolved": "https://registry.npmjs.org/expand-template/-/expand-template-2.0.3.tgz",
+ "integrity": "sha512-XYfuKMvj4O35f/pOXLObndIRvyQ+/+6AhODh+OKWj9S9498pHHn/IMszH+gt0fBCRWMNfk1ZSp5x3AifmnI2vg==",
+ "license": "(MIT OR WTFPL)",
+ "engines": {
+ "node": ">=6"
+ }
+ },
"node_modules/express": {
"version": "4.22.2",
"resolved": "https://registry.npmjs.org/express/-/express-4.22.2.tgz",
@@ -321,6 +457,12 @@
"url": "https://opencollective.com/express"
}
},
+ "node_modules/file-uri-to-path": {
+ "version": "1.0.0",
+ "resolved": "https://registry.npmjs.org/file-uri-to-path/-/file-uri-to-path-1.0.0.tgz",
+ "integrity": "sha512-0Zt+s3L7Vf1biwWZ29aARiVYLx7iMGnEUl9x33fbB/j3jR81u/O2LbqK+Bm1CDSNDKVtJ/YjwY7TUd5SkeLQLw==",
+ "license": "MIT"
+ },
"node_modules/finalhandler": {
"version": "1.3.2",
"resolved": "https://registry.npmjs.org/finalhandler/-/finalhandler-1.3.2.tgz",
@@ -366,6 +508,12 @@
"node": ">= 0.6"
}
},
+ "node_modules/fs-constants": {
+ "version": "1.0.0",
+ "resolved": "https://registry.npmjs.org/fs-constants/-/fs-constants-1.0.0.tgz",
+ "integrity": "sha512-y6OAwoSIf7FyjMIv94u+b5rdheZEjzR63GTyZJm5qh4Bi+2YgwLCcI/fPFZkL5PSixOt6ZNKm+w+Hfp/Bciwow==",
+ "license": "MIT"
+ },
"node_modules/function-bind": {
"version": "1.1.2",
"resolved": "https://registry.npmjs.org/function-bind/-/function-bind-1.1.2.tgz",
@@ -412,6 +560,12 @@
"node": ">= 0.4"
}
},
+ "node_modules/github-from-package": {
+ "version": "0.0.0",
+ "resolved": "https://registry.npmjs.org/github-from-package/-/github-from-package-0.0.0.tgz",
+ "integrity": "sha512-SyHy3T1v2NUXn29OsWdxmK6RwHD+vkj3v8en8AOBZ1wBQ/hCAQ5bAQTD02kW4W9tUp/3Qh6J8r9EvntiyCmOOw==",
+ "license": "MIT"
+ },
"node_modules/gopd": {
"version": "1.2.0",
"resolved": "https://registry.npmjs.org/gopd/-/gopd-1.2.0.tgz",
@@ -480,12 +634,38 @@
"node": ">=0.10.0"
}
},
+ "node_modules/ieee754": {
+ "version": "1.2.1",
+ "resolved": "https://registry.npmjs.org/ieee754/-/ieee754-1.2.1.tgz",
+ "integrity": "sha512-dcyqhDvX1C46lXZcVqCpK+FtMRQVdIMN6/Df5js2zouUsqG7I6sFxitIC+7KYK29KdXOLHdu9zL4sFnoVQnqaA==",
+ "funding": [
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/feross"
+ },
+ {
+ "type": "patreon",
+ "url": "https://www.patreon.com/feross"
+ },
+ {
+ "type": "consulting",
+ "url": "https://feross.org/support"
+ }
+ ],
+ "license": "BSD-3-Clause"
+ },
"node_modules/inherits": {
"version": "2.0.4",
"resolved": "https://registry.npmjs.org/inherits/-/inherits-2.0.4.tgz",
"integrity": "sha512-k/vGaX4/Yla3WzyMCvTQOXYeIHvqOKtnqBduzTHpzpQZzAskKMhZ2K+EnBiSM9zGSoIFeMpXKxa4dYeZIQqewQ==",
"license": "ISC"
},
+ "node_modules/ini": {
+ "version": "1.3.8",
+ "resolved": "https://registry.npmjs.org/ini/-/ini-1.3.8.tgz",
+ "integrity": "sha512-JV/yugV2uzW5iMRSiZAyDtQd+nxtUnjeLt0acNdw98kKLrvuRVyB80tsREOE7yvGVgalhZ6RNXCmEHkUKBKxew==",
+ "license": "ISC"
+ },
"node_modules/ipaddr.js": {
"version": "1.9.1",
"resolved": "https://registry.npmjs.org/ipaddr.js/-/ipaddr.js-1.9.1.tgz",
@@ -564,12 +744,45 @@
"node": ">= 0.6"
}
},
+ "node_modules/mimic-response": {
+ "version": "3.1.0",
+ "resolved": "https://registry.npmjs.org/mimic-response/-/mimic-response-3.1.0.tgz",
+ "integrity": "sha512-z0yWI+4FDrrweS8Zmt4Ej5HdJmky15+L2e6Wgn3+iK5fWzb6T3fhNFq2+MeTRb064c6Wr4N/wv0DzQTjNzHNGQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=10"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/sindresorhus"
+ }
+ },
+ "node_modules/minimist": {
+ "version": "1.2.8",
+ "resolved": "https://registry.npmjs.org/minimist/-/minimist-1.2.8.tgz",
+ "integrity": "sha512-2yyAR8qBkN3YuheJanUpWC5U3bb5osDywNB8RzDVlDwDHbocAJveqqj1u8+SVD7jkWT4yvsHCpWqqWqAxb0zCA==",
+ "license": "MIT",
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/mkdirp-classic": {
+ "version": "0.5.3",
+ "resolved": "https://registry.npmjs.org/mkdirp-classic/-/mkdirp-classic-0.5.3.tgz",
+ "integrity": "sha512-gKLcREMhtuZRwRAfqP3RFW+TK4JqApVBtOIftVgjuABpAtpxhPGaDcfvbhNvD0B8iD1oUr/txX35NjcaY6Ns/A==",
+ "license": "MIT"
+ },
"node_modules/ms": {
"version": "2.0.0",
"resolved": "https://registry.npmjs.org/ms/-/ms-2.0.0.tgz",
"integrity": "sha512-Tpp60P6IUJDTuOq/5Z8cdskzJujfwqfOTkrwIwj7IRISpnkJnT6SyJ4PCPnGMoFjC9ddhal5KVIYtAt97ix05A==",
"license": "MIT"
},
+ "node_modules/napi-build-utils": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/napi-build-utils/-/napi-build-utils-2.0.0.tgz",
+ "integrity": "sha512-GEbrYkbfF7MoNaoh2iGG84Mnf/WZfB0GdGEsM8wz7Expx/LlWf5U8t9nvJKXSp3qr5IsEbK04cBGhol/KwOsWA==",
+ "license": "MIT"
+ },
"node_modules/negotiator": {
"version": "0.6.3",
"resolved": "https://registry.npmjs.org/negotiator/-/negotiator-0.6.3.tgz",
@@ -579,6 +792,18 @@
"node": ">= 0.6"
}
},
+ "node_modules/node-abi": {
+ "version": "3.94.0",
+ "resolved": "https://registry.npmjs.org/node-abi/-/node-abi-3.94.0.tgz",
+ "integrity": "sha512-W5ZNO5KRPB5TkYmGVD9F6YqhsglXJzE6etpbmT+f6EQElhiX/UTG551cnsRGvLG3fyZEg9HwaDmNmj5nwJ4z9g==",
+ "license": "MIT",
+ "dependencies": {
+ "semver": "^7.3.5"
+ },
+ "engines": {
+ "node": ">=10"
+ }
+ },
"node_modules/object-inspect": {
"version": "1.13.4",
"resolved": "https://registry.npmjs.org/object-inspect/-/object-inspect-1.13.4.tgz",
@@ -603,6 +828,15 @@
"node": ">= 0.8"
}
},
+ "node_modules/once": {
+ "version": "1.4.0",
+ "resolved": "https://registry.npmjs.org/once/-/once-1.4.0.tgz",
+ "integrity": "sha512-lNaJgI+2Q5URQBkccEKHTQOPaXdUxnZZElQTZY0MFUAuaEqe1E+Nyvgdz/aIyNi6Z9MzO5dv1H8n58/GELp3+w==",
+ "license": "ISC",
+ "dependencies": {
+ "wrappy": "1"
+ }
+ },
"node_modules/parseurl": {
"version": "1.3.3",
"resolved": "https://registry.npmjs.org/parseurl/-/parseurl-1.3.3.tgz",
@@ -746,6 +980,33 @@
"node": ">=0.10.0"
}
},
+ "node_modules/prebuild-install": {
+ "version": "7.1.3",
+ "resolved": "https://registry.npmjs.org/prebuild-install/-/prebuild-install-7.1.3.tgz",
+ "integrity": "sha512-8Mf2cbV7x1cXPUILADGI3wuhfqWvtiLA1iclTDbFRZkgRQS0NqsPZphna9V+HyTEadheuPmjaJMsbzKQFOzLug==",
+ "deprecated": "No longer maintained. Please contact the author of the relevant native addon; alternatives are available.",
+ "license": "MIT",
+ "dependencies": {
+ "detect-libc": "^2.0.0",
+ "expand-template": "^2.0.3",
+ "github-from-package": "0.0.0",
+ "minimist": "^1.2.3",
+ "mkdirp-classic": "^0.5.3",
+ "napi-build-utils": "^2.0.0",
+ "node-abi": "^3.3.0",
+ "pump": "^3.0.0",
+ "rc": "^1.2.7",
+ "simple-get": "^4.0.0",
+ "tar-fs": "^2.0.0",
+ "tunnel-agent": "^0.6.0"
+ },
+ "bin": {
+ "prebuild-install": "bin.js"
+ },
+ "engines": {
+ "node": ">=10"
+ }
+ },
"node_modules/proxy-addr": {
"version": "2.0.7",
"resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.7.tgz",
@@ -759,6 +1020,16 @@
"node": ">= 0.10"
}
},
+ "node_modules/pump": {
+ "version": "3.0.4",
+ "resolved": "https://registry.npmjs.org/pump/-/pump-3.0.4.tgz",
+ "integrity": "sha512-VS7sjc6KR7e1ukRFhQSY5LM2uBWAUPiOPa/A3mkKmiMwSmRFUITt0xuj+/lesgnCv+dPIEYlkzrcyXgquIHMcA==",
+ "license": "MIT",
+ "dependencies": {
+ "end-of-stream": "^1.1.0",
+ "once": "^1.3.1"
+ }
+ },
"node_modules/qs": {
"version": "6.15.2",
"resolved": "https://registry.npmjs.org/qs/-/qs-6.15.2.tgz",
@@ -798,6 +1069,35 @@
"node": ">= 0.8"
}
},
+ "node_modules/rc": {
+ "version": "1.2.8",
+ "resolved": "https://registry.npmjs.org/rc/-/rc-1.2.8.tgz",
+ "integrity": "sha512-y3bGgqKj3QBdxLbLkomlohkvsA8gdAiUQlSBJnBhfn+BPxg4bc62d8TcBW15wavDfgexCgccckhcZvywyQYPOw==",
+ "license": "(BSD-2-Clause OR MIT OR Apache-2.0)",
+ "dependencies": {
+ "deep-extend": "^0.6.0",
+ "ini": "~1.3.0",
+ "minimist": "^1.2.0",
+ "strip-json-comments": "~2.0.1"
+ },
+ "bin": {
+ "rc": "cli.js"
+ }
+ },
+ "node_modules/readable-stream": {
+ "version": "3.6.2",
+ "resolved": "https://registry.npmjs.org/readable-stream/-/readable-stream-3.6.2.tgz",
+ "integrity": "sha512-9u/sniCrY3D5WdsERHzHE4G2YCXqoG5FTHUiCC4SIbr6XcLZBY05ya9EKjYek9O5xOAwjGq+1JdGBAS7Q9ScoA==",
+ "license": "MIT",
+ "dependencies": {
+ "inherits": "^2.0.3",
+ "string_decoder": "^1.1.1",
+ "util-deprecate": "^1.0.1"
+ },
+ "engines": {
+ "node": ">= 6"
+ }
+ },
"node_modules/safe-buffer": {
"version": "5.2.1",
"resolved": "https://registry.npmjs.org/safe-buffer/-/safe-buffer-5.2.1.tgz",
@@ -824,6 +1124,18 @@
"integrity": "sha512-YZo3K82SD7Riyi0E1EQPojLz7kpepnSQI9IyPbHHg1XXXevb5dJI7tpyN2ADxGcQbHG7vcyRHk0cbwqcQriUtg==",
"license": "MIT"
},
+ "node_modules/semver": {
+ "version": "7.8.5",
+ "resolved": "https://registry.npmjs.org/semver/-/semver-7.8.5.tgz",
+ "integrity": "sha512-Y7/KDsb8LjooZpwaqGyulO6DQlksgCncchHGk+sZIY4SBvUocMBEFH5Ur1fI4dV+Jvl0w6cjvucaIi40puRioA==",
+ "license": "ISC",
+ "bin": {
+ "semver": "bin/semver.js"
+ },
+ "engines": {
+ "node": ">=10"
+ }
+ },
"node_modules/send": {
"version": "0.19.2",
"resolved": "https://registry.npmjs.org/send/-/send-0.19.2.tgz",
@@ -947,6 +1259,51 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "node_modules/simple-concat": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/simple-concat/-/simple-concat-1.0.1.tgz",
+ "integrity": "sha512-cSFtAPtRhljv69IK0hTVZQ+OfE9nePi/rtJmw5UjHeVyVroEqJXP1sFztKUy1qU+xvz3u/sfYJLa947b7nAN2Q==",
+ "funding": [
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/feross"
+ },
+ {
+ "type": "patreon",
+ "url": "https://www.patreon.com/feross"
+ },
+ {
+ "type": "consulting",
+ "url": "https://feross.org/support"
+ }
+ ],
+ "license": "MIT"
+ },
+ "node_modules/simple-get": {
+ "version": "4.0.1",
+ "resolved": "https://registry.npmjs.org/simple-get/-/simple-get-4.0.1.tgz",
+ "integrity": "sha512-brv7p5WgH0jmQJr1ZDDfKDOSeWWg+OVypG99A/5vYGPqJ6pxiaHLy8nxtFjBA7oMa01ebA9gfh1uMCFqOuXxvA==",
+ "funding": [
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/feross"
+ },
+ {
+ "type": "patreon",
+ "url": "https://www.patreon.com/feross"
+ },
+ {
+ "type": "consulting",
+ "url": "https://feross.org/support"
+ }
+ ],
+ "license": "MIT",
+ "dependencies": {
+ "decompress-response": "^6.0.0",
+ "once": "^1.3.1",
+ "simple-concat": "^1.0.0"
+ }
+ },
"node_modules/split2": {
"version": "4.2.0",
"resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
@@ -977,6 +1334,52 @@
"node": ">= 0.8"
}
},
+ "node_modules/string_decoder": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/string_decoder/-/string_decoder-1.3.0.tgz",
+ "integrity": "sha512-hkRX8U1WjJFd8LsDJ2yQ/wWWxaopEsABU1XfkM8A+j0+85JAGppt16cr1Whg6KIbb4okU6Mql6BOj+uup/wKeA==",
+ "license": "MIT",
+ "dependencies": {
+ "safe-buffer": "~5.2.0"
+ }
+ },
+ "node_modules/strip-json-comments": {
+ "version": "2.0.1",
+ "resolved": "https://registry.npmjs.org/strip-json-comments/-/strip-json-comments-2.0.1.tgz",
+ "integrity": "sha512-4gB8na07fecVVkOI6Rs4e7T6NOTki5EmL7TUduTs6bu3EdnSycntVJ4re8kgZA+wx9IueI2Y11bfbgwtzuE0KQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/tar-fs": {
+ "version": "2.1.5",
+ "resolved": "https://registry.npmjs.org/tar-fs/-/tar-fs-2.1.5.tgz",
+ "integrity": "sha512-OboTd8mmMhZDNPV+UjQcK9yKAatXu2aJ+r1w4im1Otd4M4fl2hwvdoXUxIYHFTHWK/3y3FarBP70v3vwmGlOxw==",
+ "license": "MIT",
+ "dependencies": {
+ "chownr": "^1.1.1",
+ "mkdirp-classic": "^0.5.2",
+ "pump": "^3.0.0",
+ "tar-stream": "^2.1.4"
+ }
+ },
+ "node_modules/tar-stream": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/tar-stream/-/tar-stream-2.2.0.tgz",
+ "integrity": "sha512-ujeqbceABgwMZxEJnk2HDY2DlnUZ+9oEcb1KzTVfYHio0UE6dG71n60d8D2I4qNvleWrrXpmjpt7vZeF1LnMZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "bl": "^4.0.3",
+ "end-of-stream": "^1.4.1",
+ "fs-constants": "^1.0.0",
+ "inherits": "^2.0.3",
+ "readable-stream": "^3.1.1"
+ },
+ "engines": {
+ "node": ">=6"
+ }
+ },
"node_modules/toidentifier": {
"version": "1.0.1",
"resolved": "https://registry.npmjs.org/toidentifier/-/toidentifier-1.0.1.tgz",
@@ -986,6 +1389,18 @@
"node": ">=0.6"
}
},
+ "node_modules/tunnel-agent": {
+ "version": "0.6.0",
+ "resolved": "https://registry.npmjs.org/tunnel-agent/-/tunnel-agent-0.6.0.tgz",
+ "integrity": "sha512-McnNiV1l8RYeY8tBgEpuodCC1mLUdbSN+CYBL7kJsJNInOP8UjDDEwdk6Mw60vdLLrr5NHKZhMAOSrR2NZuQ+w==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "safe-buffer": "^5.0.1"
+ },
+ "engines": {
+ "node": "*"
+ }
+ },
"node_modules/type-is": {
"version": "1.6.18",
"resolved": "https://registry.npmjs.org/type-is/-/type-is-1.6.18.tgz",
@@ -1008,6 +1423,12 @@
"node": ">= 0.8"
}
},
+ "node_modules/util-deprecate": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/util-deprecate/-/util-deprecate-1.0.2.tgz",
+ "integrity": "sha512-EPD5q1uXyFxJpCrLnCc1nHnq3gOa6DZBocAIiI2TaSCA7VCJ1UJDMagCzIkXNsUYfD1daK//LTEQ8xiIbrHtcw==",
+ "license": "MIT"
+ },
"node_modules/utils-merge": {
"version": "1.0.1",
"resolved": "https://registry.npmjs.org/utils-merge/-/utils-merge-1.0.1.tgz",
@@ -1044,6 +1465,12 @@
"node": ">=0.8"
}
},
+ "node_modules/wrappy": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/wrappy/-/wrappy-1.0.2.tgz",
+ "integrity": "sha512-l4Sp/DRseor9wL6EvV2+TuQn63dMkPjZ/sp9XkghTEbV9KlPS1xUsZ3u7/IQO4wxtcFB4bgpQPRcR3QCvezPcQ==",
+ "license": "ISC"
+ },
"node_modules/xlsx": {
"version": "0.18.5",
"resolved": "https://registry.npmjs.org/xlsx/-/xlsx-0.18.5.tgz",
diff --git a/package.json b/package.json
index 955bea0..0cd243a 100644
--- a/package.json
+++ b/package.json
@@ -13,6 +13,7 @@
"serve": "node scripts/serve.js"
},
"dependencies": {
+ "better-sqlite3": "^12.11.1",
"express": "^4.22.2",
"pg": "^8.22.0",
"xlsx": "^0.18.5"
diff --git a/public/index.html b/public/index.html
index 00bd414..ffcbc97 100644
--- a/public/index.html
+++ b/public/index.html
@@ -438,7 +438,7 @@
h+='<div class="sec"><h4>Sold history ('+d.sales.length+')</h4>'+(d.sales.length?d.sales.map(x=>'<div class="phitem"><span>'+esc(x.address)+(x.city?', '+esc(x.city):'')+'</span><span class="m">'+money(x.sold_price)+' · '+String(x.sold_date||'').slice(0,10)+'</span></div>').join(''):'<div class="gate">no sold records on file for this address</div>')+'</div>';
if(d.permits&&d.permits.length) h+='<div class="sec"><h4>Permits ('+d.permits.length+')</h4>'+d.permits.map(p=>'<div class="phitem"><span>'+esc(p.permit_type||p.use_description||'permit')+'</span><span class="m">'+esc(String(p.issue_date||'').slice(0,10))+(p.valuation?' · '+money(p.valuation):'')+'</span></div>').join('')+'</div>';
if(d.events&&d.events.length) h+='<div class="sec"><h4>Place history</h4>'+d.events.map(ev=>'<div class="phitem"><span>'+esc(ev.summary||ev.kind)+'</span><span class="m">'+esc(String(ev.valid_from||'').slice(0,10))+'</span></div>').join('')+'</div>';
- if(!a && !d.sales.length && !(d.permits||[]).length) h+='<div class="gate">No history on file yet for this address. The 2.43M-parcel assessor roll is still ingesting — year built, beds/baths, and assessed value will appear here once it lands.</div>';
+ if(!a && !d.sales.length && !(d.permits||[]).length) h+='<div class="gate">No assessor record matched this address in the LA County parcel roll (2.4M parcels), and no sold or permit history is on file. Try the street number plus street name exactly as it appears on the deed.</div>';
mb.innerHTML=h;
}
// Sold / area history for a lead (per-broker closings aren't in our data; show area sold comps).
diff --git a/public/mls.html b/public/mls.html
index fc9f120..70bc35a 100644
--- a/public/mls.html
+++ b/public/mls.html
@@ -336,7 +336,7 @@ async function propHistory(address,city){
+((a.roll_total_value||a.total_value)?'<span>💰 assessed '+money(a.roll_total_value||a.total_value)+'</span>':'')+'</div></div>'; }
h+='<div class="ph-sec"><h4>Sold history ('+d.sales.length+')</h4>'+(d.sales.length?d.sales.map(x=>'<div class="ph-item"><span>'+esc(x.address)+(x.city?', '+esc(x.city):'')+'</span><span class="ph-mut">'+money(x.sold_price)+' · '+String(x.sold_date||'').slice(0,10)+(x.mls?' · MLS '+esc(x.mls):'')+'</span></div>').join(''):'<div class="ph-mut">no sold records on file for this address</div>')+'</div>';
if(d.permits&&d.permits.length) h+='<div class="ph-sec"><h4>Permits ('+d.permits.length+')</h4>'+d.permits.map(p=>'<div class="ph-item"><span>'+esc(p.permit_type||p.use_description||'permit')+'</span><span class="ph-mut">'+esc(String(p.issue_date||'').slice(0,10))+(p.valuation?' · '+money(p.valuation):'')+'</span></div>').join('')+'</div>';
- if(!a && !d.sales.length && !(d.permits||[]).length) h+='<div class="ph-mut">No history on file yet for this address. The 2.43M-parcel assessor roll is still ingesting — year built, beds/baths, and assessed value will appear here once it lands.</div>';
+ if(!a && !d.sales.length && !(d.permits||[]).length) h+='<div class="ph-mut">No assessor record matched this address in the LA County parcel roll (2.4M parcels), and no sold or permit history is on file. Try the street number plus street name exactly as it appears on the deed.</div>';
mb.innerHTML=h;
}
document.addEventListener('click',e=>{ const cp=e.target.closest('[data-copy]'); if(cp){ copySummary(cp.dataset.copy,cp); return; } const st=e.target.closest('[data-star]'); if(st){ toggleStar(st.dataset.star); st.classList.toggle('on',SHORT.has(st.dataset.star)); updateShortBtn(); if(F.shortlist) render(); return; }
diff --git a/scripts/export-assessor-sqlite.js b/scripts/export-assessor-sqlite.js
new file mode 100644
index 0000000..c1b1907
--- /dev/null
+++ b/scripts/export-assessor-sqlite.js
@@ -0,0 +1,116 @@
+// export-assessor-sqlite.js — dump the LA County assessor parcel roll (public.assessor_parcel in the
+// local `cre` Postgres, ~2.43M rows) to a read-only SQLite file: data/assessor.sqlite.
+//
+// WHY: prod (Kamatera) has no Postgres server, so the live crcp.agentabrams.com serves data-backed
+// pages from static artifacts with a DB->fallback pattern (data/condos-redfin.json, data/sfr-redfin.json).
+// Those are JSON because condos/sfr are only a few thousand rows. The assessor roll is 2.43M rows —
+// a JSON snapshot would be ~1GB and can't be loaded into node memory per request. So the "static
+// artifact" for this dataset is a SQLite file: it ships in the deploy exactly like the JSON snapshots,
+// but better-sqlite3 queries it per-address (situs_house_no + situs_street) with zero DB server.
+// (DTD verdict A, 2026-07-07 — 3/3: SQLite is the only option that delivers the data AND keeps the
+// "ship a file, no server" contract.)
+//
+// Run AFTER the assessor ingest (ingest-assessor.js / fetch-assessor*.js):
+// node scripts/export-assessor-sqlite.js
+//
+// The runtime fallback in scripts/serve.js (/api/address-history) reads the file this produces.
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { Pool } = require('pg');
+const Database = require('better-sqlite3');
+
+const ROOT = path.join(__dirname, '..');
+const OUT = path.join(ROOT, 'data', 'assessor.sqlite');
+const TMP = OUT + '.tmp'; // build to .tmp, atomic-rename at the end
+const BATCH = 50000; // keyset page size — keeps node memory flat
+
+const pool = new Pool({
+ host: process.env.CRE_PGHOST || '/tmp', port: +(process.env.CRE_PGPORT || 5432),
+ database: process.env.CRE_PGDATABASE || 'cre', user: process.env.CRE_PGUSER || process.env.USER || 'stevestudio2',
+});
+
+// The exact projection the /api/address-history endpoint reads back, PLUS the two lookup keys
+// (situs_house_no, situs_street) it filters on. Keeping the SQLite column set aligned with the
+// endpoint's SELECT is what makes the fallback a drop-in for the Postgres row.
+const SELECT = `
+ SELECT ain, situs_house_no, situs_street, property_location,
+ year_built, effective_year_built, sqft_main, bedrooms, bathrooms, units,
+ use_desc1, use_desc2, roll_total_value, roll_land_value, roll_imp_value,
+ roll_year, recording_date
+ FROM assessor_parcel
+ WHERE ain > $1
+ ORDER BY ain
+ LIMIT ${BATCH}`;
+
+(async () => {
+ fs.rmSync(TMP, { force: true });
+ const db = new Database(TMP);
+ // Bulk-load PRAGMAs: no journal / no fsync while building a throwaway file. Reset to safe WAL after.
+ db.pragma('journal_mode = OFF');
+ db.pragma('synchronous = OFF');
+ db.exec(`
+ CREATE TABLE assessor_parcel (
+ ain TEXT PRIMARY KEY,
+ situs_house_no INTEGER,
+ situs_street TEXT,
+ property_location TEXT,
+ year_built INTEGER,
+ effective_year_built INTEGER,
+ sqft_main INTEGER,
+ bedrooms INTEGER,
+ bathrooms INTEGER,
+ units INTEGER,
+ use_desc1 TEXT,
+ use_desc2 TEXT,
+ roll_total_value INTEGER,
+ roll_land_value INTEGER,
+ roll_imp_value INTEGER,
+ roll_year TEXT,
+ recording_date TEXT
+ );`);
+
+ const cols = ['ain','situs_house_no','situs_street','property_location','year_built',
+ 'effective_year_built','sqft_main','bedrooms','bathrooms','units','use_desc1','use_desc2',
+ 'roll_total_value','roll_land_value','roll_imp_value','roll_year','recording_date'];
+ const insert = db.prepare(
+ `INSERT OR IGNORE INTO assessor_parcel (${cols.join(',')}) VALUES (${cols.map(() => '?').join(',')})`);
+ const insertMany = db.transaction((rows) => {
+ for (const r of rows) {
+ insert.run(cols.map(c => {
+ const v = r[c];
+ if (v == null) return null;
+ if (c === 'recording_date') return v instanceof Date ? v.toISOString().slice(0, 10) : String(v);
+ return v; // pg already returns numbers as JS numbers
+ }));
+ }
+ });
+
+ let cursor = ''; // ain is TEXT; '' sorts before any real AIN
+ let total = 0;
+ for (;;) {
+ const { rows } = await pool.query(SELECT, [cursor]);
+ if (!rows.length) break;
+ insertMany(rows);
+ total += rows.length;
+ cursor = rows[rows.length - 1].ain;
+ if (total % (BATCH * 5) === 0) process.stderr.write(` ...${total.toLocaleString()} rows\n`);
+ if (rows.length < BATCH) break; // last (short) page
+ }
+
+ // Address-lookup index: mirrors the endpoint filter (situs_house_no = ? AND situs_street LIKE ?).
+ process.stderr.write(' building index...\n');
+ db.exec('CREATE INDEX idx_assessor_addr ON assessor_parcel (situs_house_no, situs_street)');
+ // DELETE (rollback-journal) mode, NOT WAL: the shipped file is read-only on prod, and WAL would
+ // require writable -wal/-shm sidecars alongside it. DELETE mode leaves ONE self-contained file that
+ // opens cleanly read-only anywhere. VACUUM after to compact.
+ db.pragma('journal_mode = DELETE');
+ db.exec('VACUUM');
+ db.close();
+
+ fs.rmSync(OUT, { force: true });
+ fs.renameSync(TMP, OUT);
+ const mb = (fs.statSync(OUT).size / 1048576).toFixed(1);
+ console.log(JSON.stringify({ rows: total, out: OUT, size_mb: +mb }));
+ await pool.end();
+})().catch(e => { console.error(e.message); try { fs.rmSync(TMP, { force: true }); } catch (_) {} process.exit(1); });
diff --git a/scripts/serve.js b/scripts/serve.js
index f3f5555..b2b9f61 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -9,6 +9,25 @@ const { lookupAddress } = require('./sources/wholivedthere'); // READ-ONLY WhoLi
let brokerdb = null; try { brokerdb = require('./db/brokers-db'); } catch (_) {} // broker graph (cre DB)
const ROOT = path.join(__dirname, '..');
+
+// Assessor roll fallback for DB-less prod (Kamatera has no Postgres). The 2.43M-parcel roll is too
+// large for a JSON snapshot, so it ships as a read-only SQLite file (data/assessor.sqlite, built by
+// scripts/export-assessor-sqlite.js). Opened lazily once — a plain file read, no DB server needed.
+// (DTD verdict A, 2026-07-07.) A missing file or missing native module just leaves assessorSqlite null.
+let assessorSqlite = null;
+try {
+ const p = path.join(ROOT, 'data', 'assessor.sqlite');
+ if (fs.existsSync(p)) {
+ const Database = require('better-sqlite3');
+ const sdb = new Database(p, { readonly: true, fileMustExist: true });
+ assessorSqlite = sdb.prepare(
+ `SELECT property_location, year_built, effective_year_built, sqft_main, bedrooms, bathrooms, units,
+ use_desc1, use_desc2, roll_total_value, roll_land_value, roll_imp_value, roll_year, recording_date
+ FROM assessor_parcel WHERE situs_house_no=? AND situs_street LIKE ?
+ ORDER BY roll_year DESC LIMIT 1`);
+ }
+} catch (_) { assessorSqlite = null; } // native module or file absent -> fallback simply unavailable
+
const PORT = process.env.PORT || 9911;
const app = express();
app.use(express.json({ limit: '256kb' }));
@@ -213,6 +232,11 @@ app.get('/api/property-history', async (req, res) => {
ORDER BY sold_date DESC NULLS LAST LIMIT 25`, [num + '%', '%' + word + '%'])).rows;
} catch (_) {}
}
+ // Assessor fallback (prod has no Postgres): if the DB path didn't populate the parcel, read the
+ // shipped SQLite roll. Same query shape as above; SQLite LIKE is case-insensitive for ASCII.
+ if (!out.assessor && assessorSqlite && num && word) {
+ try { out.assessor = assessorSqlite.get(+num, '%' + word + '%') || null; } catch (_) {}
+ }
try {
const wlt = await lookupAddress(address, city);
if (wlt && wlt.matched) { out.permits = wlt.permits || []; out.events = wlt.events || []; if (!out.assessor && wlt.parcel) out.assessorArchive = wlt.parcel; }
← f572b74 auto-save: 2026-07-07T13:00:27 (1 files) — data/.pre-afterno
·
back to Commercialrealestate
·
chore: v0.3.1 (session close) df42174 →