← back to Stars of Design
feat(starsofdesign/tick1): Gmail + Shopify recon → 236 candidates / 44 designers / 41 firms
1542658f37c17acf13d96ed7ae4acb1887bc04c7 · 2026-05-12 08:02:34 -0700 · Steve Abrams
Two-source ingest into sod_email_candidates per Steve's spec.
Gmail side (scrapers/gmail-recon-ingest.js):
- Sub-agent walked steveabramsdesigns@gmail.com via claude.ai Gmail MCP,
scoping queries to 'from:me newer_than:5y' + 8 design-flavored variants
going back to 2017. 279 reply threads scanned, 203 unique non-Steve
participants extracted to /tmp/sod-candidates.ndjson.
- Drop-list filters noreply/notifications/SaaS-vendor noise (~25 domains).
- Receiver script computes signal_score = reply_ratio*0.85 + domain_bonus
(design/studio/interiors/architect = +0.20).
- 190/203 land at signal >= 0.8.
Shopify side (scrapers/shopify-recon.js) — added per Steve 'also use shoppy database':
- dw_unified.trade_signups (7 rows): verified DW trade-program signups.
Land at signal=0.95, status='is_designer' (auto-promoted, skip triage).
- dw_unified.digital_sample_orders (25 unique): paid sample customers.
Land at signal=0.70, status='new' for later triage.
- designer_representatives.designer_reps (40 rows): manufacturer rep
agencies. Direct-insert into sod_designers + sod_firms (skip candidate
stage entirely — they're already curated) with role='Manufacturer Rep'
and a 'made-with-ai' headshot.
Auto-promotion (existing scrapers/promote-candidates.js, --min=0.85):
- 197/236 candidates flipped to status='is_designer' or 'is_firm'.
- Most Gmail rows have empty display_name (MCP returns bare emails for
many threads); follow-up tick will do get_thread name extraction.
Idempotent: all INSERTs use ON CONFLICT with GREATEST(signal_score) and
LEAST/GREATEST first_seen/last_seen so re-runs only enrich.
Ledger after this tick:
sod_email_candidates 236 (197 promoted, 39 awaiting triage)
sod_designers 44
sod_firms 41
sod_designer_firm 41
sod_links 0 (designer_reps have no URLs/emails on file)
Files touched
A scrapers/gmail-recon-ingest.jsA scrapers/shopify-recon.js
Diff
commit 1542658f37c17acf13d96ed7ae4acb1887bc04c7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 12 08:02:34 2026 -0700
feat(starsofdesign/tick1): Gmail + Shopify recon → 236 candidates / 44 designers / 41 firms
Two-source ingest into sod_email_candidates per Steve's spec.
Gmail side (scrapers/gmail-recon-ingest.js):
- Sub-agent walked steveabramsdesigns@gmail.com via claude.ai Gmail MCP,
scoping queries to 'from:me newer_than:5y' + 8 design-flavored variants
going back to 2017. 279 reply threads scanned, 203 unique non-Steve
participants extracted to /tmp/sod-candidates.ndjson.
- Drop-list filters noreply/notifications/SaaS-vendor noise (~25 domains).
- Receiver script computes signal_score = reply_ratio*0.85 + domain_bonus
(design/studio/interiors/architect = +0.20).
- 190/203 land at signal >= 0.8.
Shopify side (scrapers/shopify-recon.js) — added per Steve 'also use shoppy database':
- dw_unified.trade_signups (7 rows): verified DW trade-program signups.
Land at signal=0.95, status='is_designer' (auto-promoted, skip triage).
- dw_unified.digital_sample_orders (25 unique): paid sample customers.
Land at signal=0.70, status='new' for later triage.
- designer_representatives.designer_reps (40 rows): manufacturer rep
agencies. Direct-insert into sod_designers + sod_firms (skip candidate
stage entirely — they're already curated) with role='Manufacturer Rep'
and a 'made-with-ai' headshot.
Auto-promotion (existing scrapers/promote-candidates.js, --min=0.85):
- 197/236 candidates flipped to status='is_designer' or 'is_firm'.
- Most Gmail rows have empty display_name (MCP returns bare emails for
many threads); follow-up tick will do get_thread name extraction.
Idempotent: all INSERTs use ON CONFLICT with GREATEST(signal_score) and
LEAST/GREATEST first_seen/last_seen so re-runs only enrich.
Ledger after this tick:
sod_email_candidates 236 (197 promoted, 39 awaiting triage)
sod_designers 44
sod_firms 41
sod_designer_firm 41
sod_links 0 (designer_reps have no URLs/emails on file)
---
scrapers/gmail-recon-ingest.js | 136 +++++++++++++++++++++++++++++
scrapers/shopify-recon.js | 191 +++++++++++++++++++++++++++++++++++++++++
2 files changed, 327 insertions(+)
diff --git a/scrapers/gmail-recon-ingest.js b/scrapers/gmail-recon-ingest.js
new file mode 100644
index 0000000..9ad8887
--- /dev/null
+++ b/scrapers/gmail-recon-ingest.js
@@ -0,0 +1,136 @@
+#!/usr/bin/env node
+'use strict';
+// Bulk-upsert sod_email_candidates from Gmail recon batches.
+//
+// Reads NDJSON from stdin (one candidate per line) OR a JSON-array file
+// via --file=<path>. Each record looks like:
+// { email, display_name, domain, first_seen, last_seen,
+// thread_count, steve_replied, signal_score, last_signature, sample_subject }
+//
+// Idempotent. Re-running on the same threads updates rather than dup-creates.
+// Signal score is *taken from input* (calculator lives in the producer);
+// this script only persists.
+
+require('dotenv').config();
+const { Pool } = require('pg');
+const fs = require('fs');
+const readline = require('readline');
+
+const args = process.argv.slice(2).reduce((m, a) => {
+ const [k, v] = a.replace(/^--/, '').split('=');
+ m[k] = v ?? true; return m;
+}, {});
+const FILE = args.file || null;
+const SOURCE = args.source || 'gmail-recon';
+const DRY = !!args.dry;
+
+const pool = new Pool({
+ connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
+ max: 4,
+});
+
+const PERSONAL_ESPS = new Set(['gmail.com','hotmail.com','yahoo.com','outlook.com','icloud.com','aol.com','me.com','msn.com','protonmail.com','live.com','comcast.net','att.net','verizon.net']);
+const KNOWN_NOISE = new Set(['noreply','no-reply','donotreply','do-not-reply','support','help','info','contact','hello','team','newsletter','press','marketing','sales','accounts','billing','notifications','alert','alerts','update','updates','mailer-daemon','postmaster','reply','automated']);
+
+function domainBonus(domain, localpart) {
+ if (!domain) return 0;
+ if (KNOWN_NOISE.has(localpart)) return -0.25;
+ if (PERSONAL_ESPS.has(domain)) return 0.0;
+ // Strong designer-flavored TLDs/hosts get a bump
+ if (/(design|studio|interiors|architect|ateliers?|stylist|hospitality|hospitalitygroup)/i.test(domain)) return 0.20;
+ if (/(asid|iida|aia|nkba|kbis|nycxdesign|wallpaperz?|holland|sherwin|kravet|schumacher|cowtan|romo|maharam|knoll|herman)/i.test(domain)) return 0.05;
+ return 0.0;
+}
+
+async function upsert(rec) {
+ const email = String(rec.email || '').toLowerCase().trim();
+ if (!email || !email.includes('@')) return { skipped: true };
+ const [local, dom] = email.split('@');
+ const domain = (rec.domain || dom).toLowerCase();
+ const localpart = local.toLowerCase();
+ const displayName = rec.display_name || null;
+ const firstSeen = rec.first_seen || null;
+ const lastSeen = rec.last_seen || null;
+ const tc = Math.max(1, parseInt(rec.thread_count || 1, 10));
+ const sr = Math.max(0, parseInt(rec.steve_replied || 0, 10));
+ const lastSig = rec.last_signature || null;
+ const sampleSubject = rec.sample_subject || null;
+
+ // signal = base reply-ratio (capped) + domain bonus
+ const baseRatio = sr / Math.max(1, tc);
+ const score = Math.max(0, Math.min(1, baseRatio * 0.85 + domainBonus(domain, localpart) + (sr > 0 ? 0.10 : 0)));
+
+ if (DRY) {
+ console.log(JSON.stringify({ email, displayName, domain, tc, sr, score: score.toFixed(3) }));
+ return { dry: true };
+ }
+
+ // Merge counts on conflict; never decrease counts.
+ await pool.query(`
+ INSERT INTO sod_email_candidates
+ (email, display_name, domain, first_seen, last_seen, thread_count, steve_replied,
+ signal_score, last_signature, notes)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
+ ON CONFLICT (email) DO UPDATE SET
+ display_name = COALESCE(EXCLUDED.display_name, sod_email_candidates.display_name),
+ first_seen = LEAST(COALESCE(sod_email_candidates.first_seen, EXCLUDED.first_seen), EXCLUDED.first_seen),
+ last_seen = GREATEST(COALESCE(sod_email_candidates.last_seen, EXCLUDED.last_seen), EXCLUDED.last_seen),
+ thread_count = sod_email_candidates.thread_count + EXCLUDED.thread_count,
+ steve_replied = sod_email_candidates.steve_replied + EXCLUDED.steve_replied,
+ signal_score = GREATEST(COALESCE(sod_email_candidates.signal_score, 0), EXCLUDED.signal_score),
+ last_signature = COALESCE(EXCLUDED.last_signature, sod_email_candidates.last_signature),
+ notes = COALESCE(NULLIF(EXCLUDED.notes,''), sod_email_candidates.notes),
+ updated_at = NOW()
+ `, [email, displayName, domain, firstSeen, lastSeen, tc, sr, score, lastSig, sampleSubject]);
+ return { ok: true, score };
+}
+
+async function processStream(stream) {
+ let runId = null;
+ if (!DRY) {
+ const r = await pool.query(
+ `INSERT INTO sod_ingest_runs (run_kind, source, status) VALUES ('gmail-candidates', $1, 'running') RETURNING id`,
+ [SOURCE]
+ );
+ runId = r.rows[0].id;
+ }
+ const rl = readline.createInterface({ input: stream, crlfDelay: Infinity });
+ let n = 0, ok = 0, skipped = 0, errs = 0;
+ for await (const line of rl) {
+ const s = line.trim();
+ if (!s || s.startsWith('#')) continue;
+ n++;
+ try {
+ const rec = JSON.parse(s);
+ const r = await upsert(rec);
+ if (r.ok || r.dry) ok++; else skipped++;
+ } catch (e) {
+ errs++;
+ console.error(`line ${n}: ${e.message}`);
+ }
+ if (n % 100 === 0) console.log(` …${n} processed (ok=${ok} skip=${skipped} err=${errs})`);
+ }
+ if (runId) {
+ await pool.query(
+ `UPDATE sod_ingest_runs SET status='ok', finished_at=NOW(), records_in=$1, records_out=$2, notes=$3 WHERE id=$4`,
+ [n, ok, `skip=${skipped} err=${errs}`, runId]
+ );
+ }
+ console.log(`[gmail-recon-ingest] done. processed=${n} ok=${ok} skip=${skipped} err=${errs}`);
+ await pool.end();
+}
+
+(async function main() {
+ if (FILE && fs.existsSync(FILE)) {
+ const ext = FILE.endsWith('.json') ? 'array' : 'ndjson';
+ if (ext === 'array') {
+ const arr = JSON.parse(fs.readFileSync(FILE, 'utf8'));
+ const tmp = require('stream').Readable.from(arr.map(r => JSON.stringify(r) + '\n').join(''));
+ await processStream(tmp);
+ } else {
+ await processStream(fs.createReadStream(FILE));
+ }
+ } else {
+ await processStream(process.stdin);
+ }
+})().catch(e => { console.error('fatal:', e); process.exit(1); });
diff --git a/scrapers/shopify-recon.js b/scrapers/shopify-recon.js
new file mode 100644
index 0000000..38b979c
--- /dev/null
+++ b/scrapers/shopify-recon.js
@@ -0,0 +1,191 @@
+#!/usr/bin/env node
+'use strict';
+// Pull designer-adjacent contacts from Steve's Shopify-side databases into
+// the StarsOfDesign directory. Three sources, three signal tiers:
+//
+// 1. dw_unified.trade_signups
+// — verified DW trade-program signups (architect/interior_designer/stager).
+// — Highest signal: every row is a self-identified design pro.
+// — Lands in sod_email_candidates with status='is_designer' (auto-promoted)
+// and signal_score=0.95; promote-candidates.js will then create the
+// sod_designers + sod_firms rows.
+// 2. dw_unified.digital_sample_orders
+// — paid DW customers who ordered digital samples. Could be designers,
+// could be DIY homeowners. Mid signal (0.70).
+// — Land in sod_email_candidates with status='new', awaiting triage.
+// 3. designer_representatives.designer_reps (separate DB)
+// — manufacturer rep agencies that already have name+agency+territories.
+// — Don't go into sod_email_candidates (they're not candidate-stage —
+// they're vetted). Direct-insert into sod_designers + sod_firms with
+// a 'rep' role tag.
+//
+// Idempotent. Re-running merges via ON CONFLICT.
+
+require('dotenv').config();
+const { Pool } = require('pg');
+
+const dw = new Pool({
+ connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
+ max: 4,
+});
+const reps = new Pool({
+ connectionString: 'postgresql:///designer_representatives?host=/tmp&user=stevestudio2',
+ max: 2,
+});
+
+function slugify(s) {
+ return String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 100);
+}
+
+async function runStart(kind, source) {
+ const r = await dw.query(
+ `INSERT INTO sod_ingest_runs (run_kind, source, status) VALUES ($1, $2, 'running') RETURNING id`,
+ [kind, source]
+ );
+ return r.rows[0].id;
+}
+async function runDone(id, ok, inN, note) {
+ await dw.query(
+ `UPDATE sod_ingest_runs SET status=$1, finished_at=NOW(), records_in=$2, records_out=$3, notes=$4 WHERE id=$5`,
+ [ok ? 'ok' : 'err', inN, ok ? inN : 0, note || null, id]
+ );
+}
+
+// ── Source 1: trade_signups → sod_email_candidates (auto-promoted to designer)
+async function importTradeSignups() {
+ const runId = await runStart('shopify-trade-signups', 'dw_unified.trade_signups');
+ const r = await dw.query(`SELECT name, email, company, profession, license, status, created_at FROM trade_signups`);
+ let ok = 0;
+ for (const t of r.rows) {
+ const email = String(t.email || '').toLowerCase().trim();
+ if (!email.includes('@')) continue;
+ const domain = email.split('@')[1];
+ const note = `dw_trade_signup · ${t.profession || 'unknown'} · ${t.status || 'approved'}${t.license ? ' · lic ' + t.license : ''}${t.company ? ' · ' + t.company : ''}`;
+ await dw.query(`
+ INSERT INTO sod_email_candidates
+ (email, display_name, domain, first_seen, last_seen, thread_count, steve_replied,
+ signal_score, status, notes)
+ VALUES ($1,$2,$3,$4,$4,0,0,0.95,'is_designer',$5)
+ ON CONFLICT (email) DO UPDATE SET
+ display_name = COALESCE(EXCLUDED.display_name, sod_email_candidates.display_name),
+ signal_score = GREATEST(sod_email_candidates.signal_score, EXCLUDED.signal_score),
+ status = CASE WHEN sod_email_candidates.status = 'new' THEN 'is_designer' ELSE sod_email_candidates.status END,
+ notes = CONCAT_WS(' || ', NULLIF(sod_email_candidates.notes,''), EXCLUDED.notes),
+ updated_at = NOW()
+ `, [email, t.name, domain, t.created_at, note]);
+ ok++;
+ }
+ await runDone(runId, true, ok, `trade signups: ${ok}/${r.rows.length}`);
+ console.log(`[trade_signups] ${ok}/${r.rows.length} rows`);
+}
+
+// ── Source 2: digital_sample_orders → sod_email_candidates (mid signal)
+async function importDigitalSampleOrders() {
+ const runId = await runStart('shopify-digital-samples', 'dw_unified.digital_sample_orders');
+ const r = await dw.query(`
+ SELECT customer_email, MAX(customer_name) AS customer_name,
+ MIN(detected_at) AS first_seen, MAX(detected_at) AS last_seen,
+ count(*) AS order_count,
+ array_agg(DISTINCT unnest_skus) FILTER (WHERE unnest_skus IS NOT NULL) AS skus
+ FROM digital_sample_orders, LATERAL unnest(COALESCE(dig_skus,'{}')) AS unnest_skus
+ WHERE customer_email IS NOT NULL AND customer_email <> ''
+ GROUP BY customer_email`);
+ let ok = 0;
+ for (const c of r.rows) {
+ const email = String(c.customer_email || '').toLowerCase().trim();
+ if (!email.includes('@')) continue;
+ const domain = email.split('@')[1];
+ const note = `dw_sample_orders · ${c.order_count} order(s)${c.skus ? ' · ' + c.skus.slice(0,5).join(',') : ''}`;
+ await dw.query(`
+ INSERT INTO sod_email_candidates
+ (email, display_name, domain, first_seen, last_seen, thread_count, steve_replied,
+ signal_score, status, notes)
+ VALUES ($1,$2,$3,$4,$5,$6,0,0.70,'new',$7)
+ ON CONFLICT (email) DO UPDATE SET
+ display_name = COALESCE(EXCLUDED.display_name, sod_email_candidates.display_name),
+ thread_count = sod_email_candidates.thread_count + EXCLUDED.thread_count,
+ first_seen = LEAST(sod_email_candidates.first_seen, EXCLUDED.first_seen),
+ last_seen = GREATEST(sod_email_candidates.last_seen, EXCLUDED.last_seen),
+ signal_score = GREATEST(sod_email_candidates.signal_score, EXCLUDED.signal_score),
+ notes = CONCAT_WS(' || ', NULLIF(sod_email_candidates.notes,''), EXCLUDED.notes),
+ updated_at = NOW()
+ `, [email, c.customer_name, domain, c.first_seen, c.last_seen, c.order_count, note]);
+ ok++;
+ }
+ await runDone(runId, true, ok, `digital sample orders: ${ok}/${r.rows.length}`);
+ console.log(`[digital_sample_orders] ${ok}/${r.rows.length} unique customers`);
+}
+
+// ── Source 3: designer_reps (separate DB) → sod_designers + sod_firms (direct curated rows)
+async function importDesignerReps() {
+ const runId = await runStart('shopify-designer-reps', 'designer_representatives.designer_reps');
+ const r = await reps.query(`
+ SELECT slug, name, agency, city, region, email, phone, website_url, bio,
+ territories, lines, vertical, is_claimed
+ FROM designer_reps
+ WHERE is_published = true`);
+ let ok = 0, firmsCreated = 0, designersCreated = 0;
+ for (const rep of r.rows) {
+ // Firm/agency first
+ let firmId = null;
+ if (rep.agency) {
+ const firmSlug = slugify(rep.agency);
+ const f = await dw.query(`
+ INSERT INTO sod_firms (slug, name, city, state_or_region, primary_email, website, bio, styles)
+ VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
+ ON CONFLICT (slug) DO UPDATE SET
+ primary_email = COALESCE(sod_firms.primary_email, EXCLUDED.primary_email),
+ website = COALESCE(sod_firms.website, EXCLUDED.website),
+ bio = COALESCE(sod_firms.bio, EXCLUDED.bio),
+ updated_at = NOW()
+ RETURNING id, (xmax = 0) AS inserted`,
+ [firmSlug, rep.agency, rep.city, rep.region, rep.email, rep.website_url, rep.bio, rep.lines || []]);
+ firmId = f.rows[0].id;
+ if (f.rows[0].inserted) firmsCreated++;
+ }
+ // Designer (treat rep as a designer-adjacent contact with role='rep')
+ if (rep.name) {
+ const designerSlug = slugify(rep.name) + '-rep';
+ const d = await dw.query(`
+ INSERT INTO sod_designers (slug, full_name, primary_email, city, state_or_region, role, bio, styles, headshot_source)
+ VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'made-with-ai')
+ ON CONFLICT (slug) DO UPDATE SET
+ primary_email = COALESCE(sod_designers.primary_email, EXCLUDED.primary_email),
+ city = COALESCE(sod_designers.city, EXCLUDED.city),
+ state_or_region = COALESCE(sod_designers.state_or_region, EXCLUDED.state_or_region),
+ role = COALESCE(sod_designers.role, EXCLUDED.role),
+ bio = COALESCE(sod_designers.bio, EXCLUDED.bio),
+ updated_at = NOW()
+ RETURNING id, (xmax = 0) AS inserted`,
+ [designerSlug, rep.name, rep.email, rep.city, rep.region, 'Manufacturer Rep', rep.bio, rep.lines || []]);
+ const designerId = d.rows[0].id;
+ if (d.rows[0].inserted) designersCreated++;
+ if (firmId) {
+ await dw.query(`
+ INSERT INTO sod_designer_firm (designer_id, firm_id, title, is_current, source)
+ VALUES ($1, $2, 'Manufacturer Rep', true, 'designer-reps-import')
+ ON CONFLICT DO NOTHING`, [designerId, firmId]);
+ }
+ // Add website + email as sod_links if present
+ if (rep.website_url) {
+ await dw.query(`
+ INSERT INTO sod_links (designer_id, firm_id, url, kind, added_by)
+ VALUES ($1, $2, $3, 'firm-site', 'designer-reps-import')
+ ON CONFLICT DO NOTHING`, [designerId, firmId, rep.website_url]);
+ }
+ }
+ ok++;
+ }
+ await runDone(runId, true, ok, `reps: ${ok}/${r.rows.length} · firms+${firmsCreated} · designers+${designersCreated}`);
+ console.log(`[designer_reps] ${ok}/${r.rows.length} reps · firms+${firmsCreated} · designers+${designersCreated}`);
+}
+
+async function main() {
+ await importTradeSignups();
+ await importDigitalSampleOrders();
+ await importDesignerReps();
+ await dw.end();
+ await reps.end();
+}
+
+main().catch(e => { console.error('fatal:', e); process.exit(1); });
← bde4448 starsofdesign tick0: PG schema + AI placeholder + scaffold
·
back to Stars of Design
·
feat(starsofdesign/tick2): local-LLM triage on remaining can fed7183 →