← back to Dw Vendor Registry Sync
Option C: parameterized vendor_registry write-back to canonical Kamatera (DTD 2026-06-30)
9dd39cf83484d4a634b678b0179457cd58de866e · 2026-06-30 12:12:32 -0700 · Steve Abrams
Files touched
A .env.exampleA .gitignoreA README.mdA package-lock.jsonA package.jsonA sync.js
Diff
commit 9dd39cf83484d4a634b678b0179457cd58de866e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jun 30 12:12:32 2026 -0700
Option C: parameterized vendor_registry write-back to canonical Kamatera (DTD 2026-06-30)
---
.env.example | 8 +++
.gitignore | 9 +++
README.md | 36 +++++++++++
package-lock.json | 177 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
package.json | 12 ++++
sync.js | 143 +++++++++++++++++++++++++++++++++++++++++++
6 files changed, 385 insertions(+)
diff --git a/.env.example b/.env.example
new file mode 100644
index 0000000..9f0338d
--- /dev/null
+++ b/.env.example
@@ -0,0 +1,8 @@
+# LOCAL Mac2 dw_unified mirror (Unix socket as the OS user — NOT TCP dw_admin)
+LOCAL_DATABASE_URL=postgresql:///dw_unified?host=/tmp
+
+# CANONICAL Kamatera dw_unified — required ONLY for --apply.
+# Candidate paths (confirm which is reachable from this box before first apply):
+# Tailscale: postgresql://dw_admin:<PW>@100.107.67.67:5432/dw_unified
+# SSH tunnel: postgresql://dw_admin:<PW>@127.0.0.1:15432/dw_unified
+# REMOTE_DATABASE_URL=postgresql://dw_admin:CHANGE_ME@100.107.67.67:5432/dw_unified
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..9895730
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,9 @@
+node_modules/
+.env
+.env.*
+!.env.example
+*.log
+.DS_Store
+tmp/
+dist/
+build/
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..7c63542
--- /dev/null
+++ b/README.md
@@ -0,0 +1,36 @@
+# dw-vendor-registry-sync (DTD Option C — 2026-06-30)
+
+Sanctioned, parameterized write-back of `vendor_registry` **metadata** from the
+local Mac2 `dw_unified` mirror to the **canonical Kamatera** `dw_unified`.
+
+## Why
+Mac2 can edit `vendor_registry` but had no sanctioned write-back to canonical
+Kamatera → every local edit was a latent split-brain (a Kamatera→Mac2 sync would
+silently revert it). Raw `ssh my-server psql` is hard-blocked by the auto-mode
+classifier (psql can shell out via `\!` / `COPY TO PROGRAM`). This tool removes
+that vector: no shell-out, only **parameterized UPDATEs**, restricted to a hard
+**column whitelist** (purchasing/pricing metadata), never INSERT/DDL/DELETE.
+
+## Use
+```bash
+npm install
+cp .env.example .env # set REMOTE_DATABASE_URL (confirm reachable path)
+
+# DRY RUN (local read only, no remote connection):
+node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI-
+
+# APPLY to canonical Kamatera (writes only whitelisted columns):
+node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI- --apply
+```
+Default columns: `buy_from, buy_from_confirmed, buy_from_notes`. Override with
+`--columns a,b,c` (each must be in the whitelist in `sync.js`).
+
+## Guardrails
+- Hard `ALLOWED_COLUMNS` whitelist — any other column is refused before any DB call.
+- `--apply` runs inside a transaction; any failure rolls back (no partial writes).
+- Matches existing rows by `vendor_code`; never creates a vendor.
+- Re-reads + prints remote state after apply to verify.
+
+The agent may build + dry-run this locally; **applying to canonical Kamatera is
+Steve-gated** — run `--apply` yourself (or add a narrow `Bash(node sync.js:*)`
+allow-rule scoped to this tool).
diff --git a/package-lock.json b/package-lock.json
new file mode 100644
index 0000000..4bea0fb
--- /dev/null
+++ b/package-lock.json
@@ -0,0 +1,177 @@
+{
+ "name": "dw-vendor-registry-sync",
+ "version": "1.0.0",
+ "lockfileVersion": 3,
+ "requires": true,
+ "packages": {
+ "": {
+ "name": "dw-vendor-registry-sync",
+ "version": "1.0.0",
+ "dependencies": {
+ "dotenv": "^16.4.0",
+ "pg": "^8.11.0"
+ },
+ "bin": {
+ "dw-registry-sync": "sync.js"
+ }
+ },
+ "node_modules/dotenv": {
+ "version": "16.6.1",
+ "resolved": "https://registry.npmjs.org/dotenv/-/dotenv-16.6.1.tgz",
+ "integrity": "sha512-uBq4egWHTcTt33a72vpSG0z3HnPuIl6NqYcTrKEg2azoEyl2hpW0zqlxysq2pK9HlDIHyHyakeYaYnSAwd8bow==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=12"
+ },
+ "funding": {
+ "url": "https://dotenvx.com"
+ }
+ },
+ "node_modules/pg": {
+ "version": "8.22.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.22.0.tgz",
+ "integrity": "sha512-8wih1vVIBMxoUM2oB4soJsD9tDnDpLv4OXBJ+EJzFsvycD+lfyIreC2gGHq78f8jbLLt+bvlPTFdFZfJkOuzAA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.14.0",
+ "pg-pool": "^3.14.0",
+ "pg-protocol": "^1.15.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.4.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.4.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.4.0.tgz",
+ "integrity": "sha512-Vo7z/6rrQYxpNRylp4Tlob2elzbh+N/MOQbxFVWCxS7oEx6jF53GTJFxK2WWpKuBRkmiin4Mt+xofFDjx09R0A==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.14.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.14.0.tgz",
+ "integrity": "sha512-XwWDGcLRGCXAR8F/AM5bG7Q+A3Wm2s6QeEjlOKZLlH3UYcguiqCWKyWXVag5TLTIjR7oOJUY8kcADaZgWPyLeg==",
+ "license": "MIT"
+ },
+ "node_modules/pg-int8": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+ "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+ "license": "ISC",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
+ "node_modules/pg-pool": {
+ "version": "3.14.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.14.0.tgz",
+ "integrity": "sha512-gKtPkFdQPU3DksooVLi9LsjZxrsBUZIpa+7aVx+LV5pNh0KzP4Zleud2po+ConrxbuXGBJ6Hfer6hdgpIBpBaw==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.15.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.15.0.tgz",
+ "integrity": "sha512-cq9sECI5s0+uPUXjbz8ioyPJni6RzsRib0US67i5IoTZKw8fNeYlVE7u8F4dG7vEJJtc5wdD1K189lCCUwqWTQ==",
+ "license": "MIT"
+ },
+ "node_modules/pg-types": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+ "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-int8": "1.0.1",
+ "postgres-array": "~2.0.0",
+ "postgres-bytea": "~1.0.0",
+ "postgres-date": "~1.0.4",
+ "postgres-interval": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/pgpass": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+ "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+ "license": "MIT",
+ "dependencies": {
+ "split2": "^4.1.0"
+ }
+ },
+ "node_modules/postgres-array": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+ "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/postgres-bytea": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+ "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-date": {
+ "version": "1.0.7",
+ "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+ "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-interval": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+ "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "xtend": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/split2": {
+ "version": "4.2.0",
+ "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+ "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+ "license": "ISC",
+ "engines": {
+ "node": ">= 10.x"
+ }
+ },
+ "node_modules/xtend": {
+ "version": "4.0.2",
+ "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+ "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.4"
+ }
+ }
+ }
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..31d41fa
--- /dev/null
+++ b/package.json
@@ -0,0 +1,12 @@
+{
+ "name": "dw-vendor-registry-sync",
+ "version": "1.0.0",
+ "private": true,
+ "description": "Sanctioned parameterized write-back of vendor_registry metadata from Mac2 to canonical Kamatera dw_unified (DTD Option C, 2026-06-30).",
+ "bin": { "dw-registry-sync": "sync.js" },
+ "scripts": {
+ "dry": "node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI-",
+ "apply": "node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI- --apply"
+ },
+ "dependencies": { "pg": "^8.11.0", "dotenv": "^16.4.0" }
+}
diff --git a/sync.js b/sync.js
new file mode 100644
index 0000000..c7f4abf
--- /dev/null
+++ b/sync.js
@@ -0,0 +1,143 @@
+#!/usr/bin/env node
+/**
+ * dw-vendor-registry-sync — Option C (DTD verdict, 2026-06-30)
+ *
+ * Sanctioned, parameterized, NO-shell-out write-back of vendor_registry metadata
+ * from the LOCAL Mac2 dw_unified mirror to the CANONICAL Kamatera dw_unified.
+ *
+ * Why this exists: Mac2 can EDIT vendor_registry but had no sanctioned write-back
+ * to canonical Kamatera, so every local edit was a latent split-brain (a
+ * Kamatera->Mac2 sync would silently revert it). Raw `ssh my-server psql` is
+ * (correctly) hard-blocked by the auto-mode classifier because psql can run
+ * arbitrary code (\!, COPY TO PROGRAM). This tool removes that vector:
+ * - reads LOCAL only on a dry run (no remote connection at all)
+ * - on --apply, connects REMOTE and runs ONLY parameterized UPDATEs
+ * - restricts writes to a hard COLUMN WHITELIST (purchasing/pricing metadata)
+ * - never INSERTs vendors, never DROP/TRUNCATE/DELETE, never DDL
+ *
+ * Usage:
+ * node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI- # dry run (default cols)
+ * node sync.js --vendors DWCQ- --columns buy_from,buy_from_confirmed,buy_from_notes
+ * node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI- --apply # writes to Kamatera
+ *
+ * Env (.env, gitignored):
+ * LOCAL_DATABASE_URL default postgresql:///dw_unified?host=/tmp
+ * REMOTE_DATABASE_URL the canonical Kamatera dw_unified DSN (required for --apply)
+ */
+const { Client } = require('pg');
+require('node:fs');
+try { require('dotenv').config(); } catch (_) { /* dotenv optional; env may be preset */ }
+
+// Hard whitelist — the ONLY columns this tool may ever write to canonical prod.
+const ALLOWED_COLUMNS = new Set([
+ 'buy_from', 'buy_from_confirmed', 'buy_from_notes',
+ 'vendor_discount_pct', 'discount_confirmed', 'discount_confirmed_at', 'discount_notes',
+ 'pricing_model', 'do_not_price', 'sample_price', 'display_prices',
+ 'tariff_pct', 'tariff_notes', 'tariff_source', 'tariff_confirmed', 'country_of_origin', 'hts_code',
+ 'parent_brand', 'is_active',
+]);
+const DEFAULT_COLUMNS = ['buy_from', 'buy_from_confirmed', 'buy_from_notes'];
+
+function parseArgs(argv) {
+ const out = { vendors: [], columns: DEFAULT_COLUMNS, apply: false };
+ for (let i = 2; i < argv.length; i++) {
+ const a = argv[i];
+ if (a === '--apply') out.apply = true;
+ else if (a === '--vendors') out.vendors = (argv[++i] || '').split(',').map(s => s.trim()).filter(Boolean);
+ else if (a === '--columns') out.columns = (argv[++i] || '').split(',').map(s => s.trim()).filter(Boolean);
+ else { console.error(`Unknown arg: ${a}`); process.exit(2); }
+ }
+ return out;
+}
+
+function quoteIdent(id) {
+ // identifiers come ONLY from the whitelist, but double-quote defensively anyway
+ if (!ALLOWED_COLUMNS.has(id)) throw new Error(`Refusing non-whitelisted column: ${id}`);
+ return '"' + id.replace(/"/g, '""') + '"';
+}
+
+async function main() {
+ const args = parseArgs(process.argv);
+ if (!args.vendors.length) { console.error('Need --vendors <sku_prefix-or-vendor_code,...>'); process.exit(2); }
+
+ // validate every requested column against the whitelist BEFORE touching any DB
+ const bad = args.columns.filter(c => !ALLOWED_COLUMNS.has(c));
+ if (bad.length) { console.error(`Refusing non-whitelisted column(s): ${bad.join(', ')}`); process.exit(2); }
+
+ const localUrl = process.env.LOCAL_DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
+ const selCols = ['vendor_name', 'vendor_code', 'sku_prefix', ...args.columns];
+ const local = new Client({ connectionString: localUrl });
+ await local.connect();
+ const rows = (await local.query(
+ `SELECT ${selCols.map(quoteIdentSafe).join(', ')}
+ FROM vendor_registry
+ WHERE sku_prefix = ANY($1) OR vendor_code = ANY($1) OR sku_prefix = ANY($2)`,
+ [args.vendors, args.vendors.map(v => v.endsWith('-') ? v : v + '-')]
+ )).rows;
+ await local.end();
+
+ if (!rows.length) { console.error('No matching vendor_registry rows for: ' + args.vendors.join(', ')); process.exit(1); }
+
+ console.log(`\nLOCAL source: ${localUrl}`);
+ console.log(`Columns to sync: ${args.columns.join(', ')}`);
+ console.log(`Matched ${rows.length} vendor(s):\n`);
+ for (const r of rows) {
+ console.log(` • ${r.vendor_name} [${r.sku_prefix} / ${r.vendor_code}]`);
+ for (const c of args.columns) console.log(` ${c} = ${JSON.stringify(r[c])}`);
+ }
+
+ if (!args.apply) {
+ console.log(`\nDRY RUN — no remote connection made. Re-run with --apply to write these values`);
+ console.log(`to the canonical Kamatera dw_unified (REMOTE_DATABASE_URL).`);
+ return;
+ }
+
+ const remoteUrl = process.env.REMOTE_DATABASE_URL;
+ if (!remoteUrl) { console.error('\n--apply needs REMOTE_DATABASE_URL (set it in .env). Aborting.'); process.exit(2); }
+ const remote = new Client({ connectionString: remoteUrl, statement_timeout: 15000 });
+ await remote.connect();
+ console.log(`\nREMOTE target: ${remoteUrl.replace(/:[^:@/]+@/, ':***@')}`);
+ let updated = 0;
+ try {
+ await remote.query('BEGIN');
+ for (const r of rows) {
+ const sets = args.columns.map((c, i) => `${quoteIdent(c)} = $${i + 1}`);
+ const vals = args.columns.map(c => r[c]);
+ vals.push(r.vendor_code); // WHERE param
+ const res = await remote.query(
+ `UPDATE vendor_registry SET ${sets.join(', ')}, updated_at = now()
+ WHERE vendor_code = $${vals.length}`,
+ vals
+ );
+ updated += res.rowCount;
+ console.log(` ✓ ${r.vendor_code}: ${res.rowCount} row updated`);
+ }
+ await remote.query('COMMIT');
+ } catch (e) {
+ await remote.query('ROLLBACK').catch(() => {});
+ await remote.end();
+ console.error('\nFAILED — rolled back, no changes written. ' + e.message);
+ process.exit(1);
+ }
+ // verify
+ const check = (await remote.query(
+ `SELECT vendor_name, ${args.columns.map(quoteIdent).join(', ')}
+ FROM vendor_registry WHERE vendor_code = ANY($1) ORDER BY vendor_name`,
+ [rows.map(r => r.vendor_code)]
+ )).rows;
+ await remote.end();
+ console.log(`\nApplied ${updated} update(s) to canonical Kamatera. Verified remote state:`);
+ for (const r of check) {
+ console.log(` • ${r.vendor_name}`);
+ for (const c of args.columns) console.log(` ${c} = ${JSON.stringify(r[c])}`);
+ }
+}
+
+// selCols may include the 3 fixed identity cols (not in the write-whitelist) for SELECT only
+function quoteIdentSafe(id) {
+ const ok = ALLOWED_COLUMNS.has(id) || ['vendor_name', 'vendor_code', 'sku_prefix'].includes(id);
+ if (!ok) throw new Error(`Refusing column in SELECT: ${id}`);
+ return '"' + id.replace(/"/g, '""') + '"';
+}
+
+main().catch(e => { console.error(e); process.exit(1); });
(oldest)
·
back to Dw Vendor Registry Sync
·
Add Kamatera pg_hba.conf access runbook for the sync tool 51ed27a →