PG-first writeback: link 504 Tokiwa staging rows to Shopify draft ids (shopify_product_id + on_shopify)

21d1f0458c0aca717535260e7c15f935c8982938 · 2026-07-26 08:26:12 -0700 · Steve Abrams

Files touched

A scripts/pg-writeback.py

Diff

commit 21d1f0458c0aca717535260e7c15f935c8982938
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 08:26:12 2026 -0700

    PG-first writeback: link 504 Tokiwa staging rows to Shopify draft ids (shopify_product_id + on_shopify)
---
 scripts/pg-writeback.py | 53 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 53 insertions(+)

diff --git a/scripts/pg-writeback.py b/scripts/pg-writeback.py
new file mode 100644
index 0000000..a57875a
--- /dev/null
+++ b/scripts/pg-writeback.py
@@ -0,0 +1,53 @@
+#!/usr/bin/env python3
+"""PostgreSQL-first writeback for the Fentucci Naturals (Tokiwa) draft import.
+
+create-drafts.py records the Shopify product ids to data/created.jsonl only.
+Per Steve's standing rule the staging table must also carry the Shopify id +
+an on_shopify flag so dw_unified knows which staged rows are live on the store.
+
+This is idempotent: adds the two columns if missing, then upserts from
+created.jsonl. Local dw_unified mirror only — no Shopify writes. $0 (local).
+
+Usage:  PGHOST=/tmp python3 scripts/pg-writeback.py
+"""
+import json, os, subprocess, sys
+
+HERE = os.path.dirname(os.path.abspath(__file__))
+ROOT = os.path.dirname(HERE)
+CREATED = os.path.join(ROOT, "data", "created.jsonl")
+os.environ.setdefault("PGHOST", "/tmp")
+
+rows = [json.loads(l) for l in open(CREATED) if l.strip()]
+if not rows:
+    sys.exit("created.jsonl empty — nothing to write back")
+
+# de-dup on sku, last write wins
+by_sku = {r["sku"]: r["product_id"] for r in rows}
+values = ",\n".join(f"('{sku}',{pid})" for sku, pid in sorted(by_sku.items()))
+
+sql = f"""
+BEGIN;
+ALTER TABLE tokiwa_catalog ADD COLUMN IF NOT EXISTS shopify_product_id bigint;
+ALTER TABLE tokiwa_catalog ADD COLUMN IF NOT EXISTS on_shopify boolean NOT NULL DEFAULT false;
+
+UPDATE tokiwa_catalog t SET
+  shopify_product_id = v.pid,
+  on_shopify = true
+FROM (VALUES
+{values}
+) AS v(sku, pid)
+WHERE t.dw_sku = v.sku;
+
+-- report
+\\echo rows now on_shopify:
+SELECT count(*) FROM tokiwa_catalog WHERE on_shopify;
+\\echo staged rows NOT yet on_shopify (should be 0 if import complete):
+SELECT count(*) FROM tokiwa_catalog WHERE NOT on_shopify;
+COMMIT;
+"""
+
+p = subprocess.run(["psql", "-d", "dw_unified", "-v", "ON_ERROR_STOP=1"],
+                   input=sql, text=True, capture_output=True)
+sys.stdout.write(p.stdout)
+sys.stderr.write(p.stderr)
+sys.exit(p.returncode)

← 8cdafc0 auto-save: 2026-07-23T05:18:46 (1 files) — data/created.json · back to Fentucci Naturals · chore: version-up v1.0.2 (session close) — pg-writeback lint bccaed0 →