← back to Stars of Design
feat(starsofdesign): regex pass extracts URLs from stored email signatures
1f43d2b155e006eea93eabeb61762c02be3e4ebc · 2026-05-12 10:39:27 -0700 · Steve Abrams
scrapers/extract-links-from-signatures.js — cheap free pass over every
sod_email_candidates.last_signature blob. Regex pulls all http(s) URLs,
classifies as:
linkedin linkedin.com/in/* OR linkedin.com/company/*
instagram instagram.com/*
houzz houzz.com/*
pinterest pinterest.com/*
facebook facebook.com/*
twitter twitter.com/* or x.com/*
firm-site any URL whose host matches the candidate's email domain
(so jane@studio.com signature with https://studio.com/about
gets recognized as firm-site)
Otherwise dropped (calendar/mailer/asset hosts are noise without curation).
Idempotent via ON CONFLICT DO NOTHING on sod_links. Designer-attached
when sod_designers.primary_email matches the candidate, otherwise
inserted as orphan (designer_id=NULL, firm_id=NULL) waiting to be
attached when the designer's profile gets materialized in a later
sig-extract pass.
First run results:
scanned 158 signatures
inserted 24 new links
by-kind: firm-site=11 twitter=5 instagram=4 facebook=2 houzz=1 pinterest=1
dupes=3
Why this matters — the LLM in extract-from-signatures captures 1-2 URLs
per signature at most (usually just the primary website or the linkedin),
but real designer signatures often include 4-6 URLs across Instagram /
Houzz / personal site / portfolio host / etc. This deterministic pass
sweeps up the rest for free.
Top-40 firm LinkedIns from seed-top-firms.js confirmed still in place
(40 LinkedIn URLs at firm_id level for Gensler/HOK/Kelly Wearstler/etc.).
Designer-level LinkedIn coverage is the next gap — needs Google CSE
keys to resolve via 'site:linkedin.com/in NAME FIRM' search. Steve does
not have GOOGLE_CSE_KEY / GOOGLE_CSE_ID registered in secrets-manager
yet; tick blocked on Steve providing those.
Background drains running:
PID 59491 — asseeninmovies wikidata bg4 (15k movies, popularity-sorted)
PID 59492 — starsofdesign sig-extract pass 4 (limit=200)
Files touched
A scrapers/extract-links-from-signatures.js
Diff
commit 1f43d2b155e006eea93eabeb61762c02be3e4ebc
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 12 10:39:27 2026 -0700
feat(starsofdesign): regex pass extracts URLs from stored email signatures
scrapers/extract-links-from-signatures.js — cheap free pass over every
sod_email_candidates.last_signature blob. Regex pulls all http(s) URLs,
classifies as:
linkedin linkedin.com/in/* OR linkedin.com/company/*
instagram instagram.com/*
houzz houzz.com/*
pinterest pinterest.com/*
facebook facebook.com/*
twitter twitter.com/* or x.com/*
firm-site any URL whose host matches the candidate's email domain
(so jane@studio.com signature with https://studio.com/about
gets recognized as firm-site)
Otherwise dropped (calendar/mailer/asset hosts are noise without curation).
Idempotent via ON CONFLICT DO NOTHING on sod_links. Designer-attached
when sod_designers.primary_email matches the candidate, otherwise
inserted as orphan (designer_id=NULL, firm_id=NULL) waiting to be
attached when the designer's profile gets materialized in a later
sig-extract pass.
First run results:
scanned 158 signatures
inserted 24 new links
by-kind: firm-site=11 twitter=5 instagram=4 facebook=2 houzz=1 pinterest=1
dupes=3
Why this matters — the LLM in extract-from-signatures captures 1-2 URLs
per signature at most (usually just the primary website or the linkedin),
but real designer signatures often include 4-6 URLs across Instagram /
Houzz / personal site / portfolio host / etc. This deterministic pass
sweeps up the rest for free.
Top-40 firm LinkedIns from seed-top-firms.js confirmed still in place
(40 LinkedIn URLs at firm_id level for Gensler/HOK/Kelly Wearstler/etc.).
Designer-level LinkedIn coverage is the next gap — needs Google CSE
keys to resolve via 'site:linkedin.com/in NAME FIRM' search. Steve does
not have GOOGLE_CSE_KEY / GOOGLE_CSE_ID registered in secrets-manager
yet; tick blocked on Steve providing those.
Background drains running:
PID 59491 — asseeninmovies wikidata bg4 (15k movies, popularity-sorted)
PID 59492 — starsofdesign sig-extract pass 4 (limit=200)
---
scrapers/extract-links-from-signatures.js | 105 ++++++++++++++++++++++++++++++
1 file changed, 105 insertions(+)
diff --git a/scrapers/extract-links-from-signatures.js b/scrapers/extract-links-from-signatures.js
new file mode 100644
index 0000000..6bc399f
--- /dev/null
+++ b/scrapers/extract-links-from-signatures.js
@@ -0,0 +1,105 @@
+#!/usr/bin/env node
+'use strict';
+// Free cheap pass — regex-scan every sod_email_candidates.last_signature for
+// URL patterns and store them in sod_links. The LLM in extract-from-signatures
+// often misses URLs (it grabs the obvious linkedin one and skips the firm
+// site, or vice versa), so this catches everything that's actually in the
+// text. Cheap, deterministic, idempotent.
+//
+// Patterns captured:
+// linkedin.com/in/<slug> → kind='linkedin' (personal)
+// linkedin.com/company/<slug> → kind='linkedin' (firm)
+// instagram.com/<slug> → kind='instagram'
+// <something>.com/.net/.studio → kind='firm-site' (if matches the
+// candidate's domain)
+// houzz.com/... → kind='houzz'
+// pinterest.com/... → kind='pinterest'
+//
+// Linked to sod_designers if a row exists matching primary_email; OR to
+// sod_firms via the most-common firm join for that designer. Falls back to
+// "orphan" rows (designer_id=NULL, firm_id=NULL) — those get adopted later
+// when the designer's profile gets materialized.
+
+require('dotenv').config();
+const { Pool } = require('pg');
+
+const pool = new Pool({
+ connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
+ max: 4,
+});
+
+const URL_RE = /https?:\/\/[^\s)\]"'>]+/gi;
+
+function classifyUrl(raw, candidateDomain) {
+ // Strip trailing punctuation that sneaks in from sig text
+ let u = raw.replace(/[.,;:!?\)\]>'"]+$/, '');
+ try { u = new URL(u).toString(); } catch { return null; }
+ const lower = u.toLowerCase();
+ if (lower.includes('linkedin.com/in/')) return { url: u, kind: 'linkedin' };
+ if (lower.includes('linkedin.com/company/')) return { url: u, kind: 'linkedin' };
+ if (lower.includes('instagram.com/')) return { url: u, kind: 'instagram' };
+ if (lower.includes('houzz.com/')) return { url: u, kind: 'houzz' };
+ if (lower.includes('pinterest.com/')) return { url: u, kind: 'pinterest' };
+ if (lower.includes('facebook.com/')) return { url: u, kind: 'facebook' };
+ if (lower.includes('twitter.com/') || lower.includes('x.com/')) return { url: u, kind: 'twitter' };
+ // Firm-site heuristic: if the URL's host matches (or is a subdomain of) the
+ // candidate's email domain, count it as the firm site.
+ if (candidateDomain) {
+ try {
+ const host = new URL(u).hostname.replace(/^www\./, '');
+ if (host === candidateDomain || host.endsWith('.' + candidateDomain)) {
+ return { url: u, kind: 'firm-site' };
+ }
+ } catch {}
+ }
+ // Skip generic links (mailing services, calendar, image hosts, etc.) — too
+ // noisy to surface on a designer profile without curation.
+ return null;
+}
+
+async function main() {
+ const r = await pool.query(`
+ SELECT c.id, c.email, c.domain, c.last_signature, c.notes,
+ d.id AS designer_id
+ FROM sod_email_candidates c
+ LEFT JOIN sod_designers d ON d.primary_email = c.email
+ WHERE c.status = 'is_designer'
+ AND c.last_signature IS NOT NULL
+ AND length(c.last_signature) >= 50`);
+ console.log(`[extract-links] scanning ${r.rows.length} signatures for URLs`);
+
+ let inserted = 0, dupes = 0, classified = {};
+ for (const row of r.rows) {
+ const urls = row.last_signature.match(URL_RE);
+ if (!urls) continue;
+ for (const raw of new Set(urls)) {
+ const c = classifyUrl(raw, row.domain);
+ if (!c) continue;
+ // Find firm_id via the designer's current firm-join, if any
+ let firmId = null;
+ if (row.designer_id) {
+ const fq = await pool.query(
+ `SELECT firm_id FROM sod_designer_firm WHERE designer_id=$1 AND is_current=true LIMIT 1`,
+ [row.designer_id]
+ );
+ firmId = fq.rows[0]?.firm_id || null;
+ }
+ const ins = await pool.query(`
+ INSERT INTO sod_links (designer_id, firm_id, url, kind, added_by)
+ VALUES ($1, $2, $3, $4, 'sig-regex-pass')
+ ON CONFLICT DO NOTHING
+ RETURNING id`,
+ [row.designer_id, firmId, c.url, c.kind]
+ );
+ if (ins.rowCount > 0) {
+ inserted++;
+ classified[c.kind] = (classified[c.kind] || 0) + 1;
+ } else {
+ dupes++;
+ }
+ }
+ }
+ console.log(`[extract-links] inserted=${inserted} dupes=${dupes} by-kind=${JSON.stringify(classified)}`);
+ await pool.end();
+}
+main().catch(e => { console.error('fatal:', e); process.exit(1); });
← feeab56 feat(starsofdesign): /clients route — surface real DW client
·
back to Stars of Design
·
feat(starsofdesign): /feed phone UI — vertical scroll, Like 5927cb6 →