← back to Gracie Internal
auto-save: 2026-07-15T13:06:41 (1 files) — lib/
5866b3a3d4a110f99660b2eb9b71c68e11e884e4 · 2026-07-15 13:06:56 -0700 · Steve Abrams
Files touched
Diff
commit 5866b3a3d4a110f99660b2eb9b71c68e11e884e4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jul 15 13:06:56 2026 -0700
auto-save: 2026-07-15T13:06:41 (1 files) — lib/
---
lib/vendor-requests.js | 222 +++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 222 insertions(+)
diff --git a/lib/vendor-requests.js b/lib/vendor-requests.js
new file mode 100644
index 0000000..cdf81fe
--- /dev/null
+++ b/lib/vendor-requests.js
@@ -0,0 +1,222 @@
+/* Internal purchasing requests — shared module for every *.designerwallcoverings.com
+ internal vendor PDP (astek, reidwitlin, quadrille, sangetsu, …).
+
+ POST /api/request → create REQ-YYMMDD-<id> in dw_unified.vendor_requests;
+ stock/price requests AUTO-EMAIL the vendor via
+ george-gmail (:9850) when a vendor email is on file.
+ POST /api/request/preview → compose the exact email (no insert, no send).
+ GET /api/requests → recent requests for this vendor (internal queue view).
+
+ Hard rules baked in:
+ - NEVER include a client name — emails reference our account number + REQ only.
+ - Vendor emails go out ONLY on an explicit user click behind Basic Auth; the
+ george external-send guard token is passed because that click IS the approval.
+ - No vendor email on file → request is still logged (status no_email) and the
+ caller is told to phone the vendor instead. */
+const fs = require('fs');
+const os = require('os');
+const path = require('path');
+const http = require('http');
+const { Pool } = require('pg');
+
+const GEORGE = process.env.GEORGE_URL || 'http://127.0.0.1:9850';
+const FROM = 'steve@designerwallcoverings.com';
+
+function envFrom(file, key) {
+ try { const m = fs.readFileSync(file, 'utf8').match(new RegExp('^' + key + '=(.+)$', 'm')); return m ? m[1].replace(/^["']|["']$/g, '').trim() : ''; }
+ catch { return ''; }
+}
+// Resolve a value from the first source that has it: an explicit process env var,
+// then each candidate .env file. Makes the lib portable across Mac (~/Projects/...)
+// and Kamatera, where george lives at ~/DW-Agents/gmail-agent — the old single
+// hard-coded path returned '' there, so the send token was empty and george
+// rejected every request (status email_failed).
+function firstEnv(key, files) {
+ if (process.env[key]) return process.env[key];
+ for (const f of files) { const v = envFrom(f, key); if (v) return v; }
+ return '';
+}
+const HOME = os.homedir();
+const GEORGE_ENVS = [
+ path.join(HOME, 'Projects/george-gmail/.env'), // Mac dev
+ path.join(HOME, 'DW-Agents/gmail-agent/.env'), // Kamatera prod
+];
+const SEND_TOKEN = firstEnv('GEORGE_EXTERNAL_SEND_TOKEN', GEORGE_ENVS);
+const PGPASS = firstEnv('DW_ADMIN_DB_PASSWORD', [path.join(HOME, 'Projects/secrets-manager/.env')]);
+// george basic auth — mirror george's own resolution: GEORGE_BASIC_AUTH from its
+// .env when set, else the historical fallback (admin + empty password).
+const GEORGE_AUTH = firstEnv('GEORGE_BASIC_AUTH', GEORGE_ENVS) || 'admin:';
+
+let pool = null;
+function db() {
+ if (!pool) pool = new Pool({ host: '127.0.0.1', port: 5432, user: 'dw_admin', database: 'dw_unified', password: PGPASS, max: 3 });
+ return pool;
+}
+
+// Self-bootstrap the table so a fresh DB (Kamatera, mac2, a new sister site)
+// never throws `relation "vendor_requests" does not exist` on the first click.
+// Idempotent (IF NOT EXISTS) and memoized — the DDL runs at most once per process.
+let schemaReady = null;
+function ensureSchema() {
+ if (!schemaReady) {
+ schemaReady = db().query(`
+ CREATE TABLE IF NOT EXISTS vendor_requests (
+ id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
+ req_no text,
+ req_type text,
+ vendor_code text,
+ vendor_name text,
+ vendor_email text,
+ account_number text,
+ dw_sku text,
+ mfr_sku text,
+ product_title text,
+ qty text,
+ note text,
+ requested_by text,
+ status text,
+ email_subject text,
+ email_body text,
+ email_message_id text,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ sent_at timestamptz
+ );
+ CREATE INDEX IF NOT EXISTS vendor_requests_vendor_code_id_idx
+ ON vendor_requests (vendor_code, id DESC);
+ `).catch(e => { schemaReady = null; console.error('[vendor-requests] ensureSchema failed:', e.message); throw e; });
+ }
+ return schemaReady;
+}
+
+const esc = s => String(s == null ? '' : s).replace(/[&<>]/g, c => ({ '&': '&', '<': '<', '>': '>' }[c]));
+
+// ---- email composition — polite, account-number-only, client-anonymous ----
+// vendor.buy_from = distributor that carries the line (e.g. Artmura via NewWall):
+// the email greets the distributor; the line name stays in the subject/item.
+function composeEmail(type, vendor, item, reqNo) {
+ const who = vendor.buy_from || vendor.name;
+ const acct = vendor.account_number || null;
+ const ask = type === 'stock'
+ ? 'Could you please check current stock availability on the following item?'
+ : 'Could you please confirm our current net / wholesale cost (and any applicable discount) on the following item?';
+ // HARD RULE (Steve 2026-07-02): outbound emails NEVER mention our internal
+ // (DW) SKU numbers — manufacturer SKU + REQ number only.
+ const subjectHead = type === 'stock' ? 'Stock check' : 'Pricing request';
+ const subjectSku = item.mfr_sku ? ` (${item.mfr_sku})` : '';
+ const subject = `${subjectHead} — ${item.title || item.mfr_sku || 'item inquiry'}${subjectSku}${acct ? ` — Acct #${acct}` : ''} — ${reqNo}`;
+ const rows = [
+ ['Pattern', item.title], ['Item / SKU', item.mfr_sku],
+ ['Quantity', item.qty || (type === 'stock' ? 'to be confirmed' : null)],
+ ['Note', item.note],
+ ].filter(r => r[1]);
+ const body = [
+ `<p>Hello ${esc(who)} team,</p>`,
+ `<p>${ask}</p>`,
+ `<p>${rows.map(r => ` <b>${esc(r[0])}:</b> ${esc(r[1])}`).join('<br>')}</p>`,
+ `<p>This is for Designer Wallcoverings${acct ? `, account #${esc(acct)}` : ''}. Please reference <b>${reqNo}</b> in your reply.</p>`,
+ `<p>Thank you very much — we appreciate your help!</p>`,
+ `<p>Warm regards,<br><br>Designer Wallcoverings — Purchasing<br>${FROM}${acct ? `<br>Account #${esc(acct)}` : ''} · Ref ${reqNo}</p>`,
+ ].join('\n');
+ return { subject, body };
+}
+
+function georgeSend({ to, subject, body }) {
+ return new Promise((resolve) => {
+ const payload = JSON.stringify({ account: 'steve-office', from: FROM, to, subject, body, source: 'vendor-pdp' });
+ const u = new URL(GEORGE + '/api/send');
+ const req = http.request({ hostname: u.hostname, port: u.port, path: u.pathname, method: 'POST',
+ headers: { 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(payload), 'X-Send-Approval': SEND_TOKEN,
+ 'Authorization': 'Basic ' + Buffer.from(GEORGE_AUTH).toString('base64') } }, res => {
+ let d = ''; res.on('data', c => d += c);
+ res.on('end', () => { try { resolve({ status: res.statusCode, ...JSON.parse(d) }); } catch { resolve({ status: res.statusCode, error: d.slice(0, 300) }); } });
+ });
+ req.on('error', e => resolve({ status: 0, error: e.message }));
+ req.setTimeout(15000, () => { req.destroy(); resolve({ status: 0, error: 'george timeout' }); });
+ req.end(payload);
+ });
+}
+
+function reqNoFor(id, createdAt) {
+ const d = new Date(createdAt);
+ const ymd = d.getFullYear().toString().slice(2) + String(d.getMonth() + 1).padStart(2, '0') + String(d.getDate()).padStart(2, '0');
+ return `REQ-${ymd}-${id}`;
+}
+
+function mountVendorRequests(app, { vendorCode, getVendor, dataDir }) {
+ // warm the schema on mount so the table exists before the first request lands
+ ensureSchema().catch(() => {});
+
+ // exact email preview — same composer the send path uses; nothing persisted
+ app.post('/api/request/preview', (req, res) => {
+ const { type, dw_sku, mfr_sku, title, qty, note } = req.body || {};
+ if (!['stock', 'price'].includes(type)) return res.status(400).json({ ok: false, error: 'preview is for stock/price only' });
+ const vendor = getVendor() || {};
+ const { subject, body } = composeEmail(type, vendor, { dw_sku, mfr_sku, title, qty, note }, 'REQ-(assigned on send)');
+ res.json({ ok: true, to: vendor.email || null, from: FROM, subject, body, no_email: !vendor.email, phone: vendor.phone || null });
+ });
+
+ app.post('/api/request', async (req, res) => {
+ const { dw_sku, mfr_sku, title, qty, note, name } = req.body || {};
+ const type = ['memo', 'stock', 'price'].includes(req.body && req.body.type) ? req.body.type : 'memo';
+ if (!dw_sku && !mfr_sku) return res.status(400).json({ ok: false, error: 'sku required' });
+ const vendor = getVendor() || {};
+ try {
+ await ensureSchema(); // guarantees the table on a cold process (no more "relation does not exist")
+ const ins = await db().query(
+ `INSERT INTO vendor_requests (req_type, vendor_code, vendor_name, vendor_email, account_number,
+ dw_sku, mfr_sku, product_title, qty, note, requested_by, status)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,'open') RETURNING id, created_at`,
+ [type, vendorCode, vendor.name || null, vendor.email || null, vendor.account_number || null,
+ dw_sku || null, mfr_sku || null, title || null, qty || null, note || null, name || FROM]);
+ const { id, created_at } = ins.rows[0];
+ const reqNo = reqNoFor(id, created_at);
+
+ let status = 'open', emailed = false, message, mail = null;
+ if (type === 'memo') {
+ message = `${reqNo} logged — purchasing will order the memo sample.`;
+ } else if (!vendor.email) {
+ status = 'no_email';
+ message = `${reqNo} logged. No vendor email on file — call ${vendor.buy_from || vendor.name || 'the vendor'}${vendor.phone ? ' at ' + vendor.phone : ''}.`;
+ } else {
+ mail = composeEmail(type, vendor, { dw_sku, mfr_sku, title, qty, note }, reqNo);
+ const to = process.env.REQUEST_EMAIL_OVERRIDE || vendor.email; // override = smoke-test only
+ const sent = await georgeSend({ to, ...mail });
+ if (sent.success) {
+ status = 'emailed'; emailed = true;
+ message = `${reqNo} sent to ${vendor.buy_from || vendor.name} (${to}).`;
+ } else {
+ status = 'email_failed';
+ message = `${reqNo} logged, but the email failed (${sent.error || 'status ' + sent.status}) — call ${vendor.phone || 'the vendor'}.`;
+ }
+ await db().query(
+ `UPDATE vendor_requests SET req_no=$1, status=$2, email_subject=$3, email_body=$4,
+ email_message_id=$5, sent_at=CASE WHEN $2='emailed' THEN now() ELSE NULL END WHERE id=$6`,
+ [reqNo, status, mail.subject, mail.body, sent.messageId || null, id]);
+ }
+ if (type === 'memo' || status === 'no_email') {
+ await db().query(`UPDATE vendor_requests SET req_no=$1, status=$2 WHERE id=$3`, [reqNo, status, id]);
+ }
+ // local trail, same file the old inquiry endpoint used
+ try {
+ fs.appendFileSync(path.join(dataDir, 'inquiries.jsonl'),
+ JSON.stringify({ at: new Date().toISOString(), req_no: reqNo, type, status, sku: dw_sku || mfr_sku, qty: qty || '', note: note || '' }) + '\n');
+ } catch {}
+ res.json({ ok: true, req_no: reqNo, status, emailed, message });
+ } catch (e) {
+ console.error('[vendor-requests]', e.message);
+ res.status(500).json({ ok: false, error: 'request failed: ' + e.message });
+ }
+ });
+
+ app.get('/api/requests', async (_req, res) => {
+ try {
+ await ensureSchema();
+ const { rows } = await db().query(
+ `SELECT req_no, req_type, status, dw_sku, mfr_sku, product_title, qty, created_at, sent_at
+ FROM vendor_requests WHERE vendor_code=$1 ORDER BY id DESC LIMIT 50`, [vendorCode]);
+ res.json({ requests: rows });
+ } catch (e) { res.status(500).json({ error: e.message }); }
+ });
+}
+
+module.exports = { mountVendorRequests, composeEmail };
← e78260f Revert "Add Activation Calendar tab (go-live date+time per s
·
back to Gracie Internal
·
gracie-internal: add Memo/Stock/Price chip actions (shared v 69f55eb →