Fix: dedupe rows by conflict key within upsert batch (last-wins)

4c680aa7db44e96b55645d16ddcd2c2c600cc90e · 2026-08-10 22:47:55 -0700 · steve

Postgres rejects INSERT..ON CONFLICT touching the same key twice per statement.
Code-enforcement (apno) and WeHo film (permit_no) repeat keys within a page.
Surfaced during full backfill; all sources now load clean.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M src/db.js

Diff

commit 4c680aa7db44e96b55645d16ddcd2c2c600cc90e
Author: steve <steve@designerwallcoverings.com>
Date:   Mon Aug 10 22:47:55 2026 -0700

    Fix: dedupe rows by conflict key within upsert batch (last-wins)
    
    Postgres rejects INSERT..ON CONFLICT touching the same key twice per statement.
    Code-enforcement (apno) and WeHo film (permit_no) repeat keys within a page.
    Surfaced during full backfill; all sources now load clean.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/db.js | 11 +++++++++++
 1 file changed, 11 insertions(+)

diff --git a/src/db.js b/src/db.js
index 529903c..c334957 100644
--- a/src/db.js
+++ b/src/db.js
@@ -24,6 +24,17 @@ export async function q(text, params) {
 // Returns the number of rows sent.
 export async function upsert(table, conflict, rows) {
   if (!rows.length) return 0;
+
+  // Postgres rejects an INSERT ... ON CONFLICT that targets the same conflict key
+  // twice in one statement ("cannot affect row a second time"). Some datasets
+  // repeat a key within a single page (e.g. one code-enforcement apno or one film
+  // permit_no spanning multiple rows), so collapse by conflict key first — last wins.
+  if (rows.length > 1) {
+    const seen = new Map();
+    for (const r of rows) seen.set(conflict.map((k) => r[k]).join(''), r);
+    if (seen.size !== rows.length) rows = [...seen.values()];
+  }
+
   const cols = Object.keys(rows[0]);
   const updatable = cols.filter((c) => !conflict.includes(c));
   const setClause = updatable.length

← 899b4fc LA Socrata/ArcGIS public-data ingester → realestate DB (TK-1 · back to La Socrata Ingester · assessor --full now scans all roll years (matches docs); def b045794 →