← back to Stars of Design
feat(starsofdesign): areas_served + PII guardrails + sig-extract pass 2
fcd8cd83ae2226d598b945ee03e1fa675db25ea5 · 2026-05-12 10:11:23 -0700 · Steve Abrams
Per Steve's correction 2026-05-12: 'city and state ok, no zip, no
street address, and AREAS THEY SERVE.'
Schema change (idempotent ALTER):
ALTER TABLE sod_designers ADD COLUMN areas_served TEXT[];
ALTER TABLE sod_firms ADD COLUMN areas_served TEXT[];
CREATE INDEX (gin) on each.
LLM extractor prompt rewrite (extract-from-signatures.js SYSTEM):
- Asks for {full_name, firm_name, firm_city, firm_state, firm_country,
firm_role, areas_served[], linkedin_personal, linkedin_company,
website, instagram}.
- Explicit rule: NEVER extract street address, NEVER extract zip,
NEVER extract phone. Phone is opt-in via claim flow, not scraping.
- For areas_served: look for 'Serving:', 'Areas:', 'We cover', or
parenthetical region phrases. Otherwise safely infer from city+state.
- If a signature contains '123 Main Street, Suite 4B, Brooklyn, NY 11201'
we keep 'Brooklyn'/'NY' only — never the street or zip.
Server-side PII cleanup of LLM output (in case the model echoes one
through anyway):
- firmCity scrubbed of any 'N <street> Street/Ave/etc.' patterns
- firmCity scrubbed of zip patterns (\d{5} and \d{5}-\d{4})
- areas_served filtered: no leading digits, no zip-shaped entries,
no <2-char or >60-char entries, dedupe + cap at 12.
Hallucination hardening (carryover from Steve's earlier note about
'Designer Wallcoverings' false firm):
- Reject bare first-name without local-part match: gemma3:12b
repeatedly returns 'Samantha' / 'Steve' / 'Sarah' when it can't find
a real name. New guard requires the first-name's first 4 chars to
appear in the email local-part.
- Reject firms that are just a website ('domaineinteriors.com').
Two-phase candidate select for the pass:
Priority 1 — empty display_name (need name to materialize designer)
Priority 2 — has name but no sod_designers row yet (firm/role enrichment)
Persisted in this pass:
city / state_or_region / country / areas_served on both sod_firms and
sod_designers. Instagram URL gets its own kind='instagram' row in
sod_links (in addition to existing linkedin / firm-site kinds).
Sig-extract pass 2 results (limit=120):
ok=51 withName=9 withFirm=13 skipped=69
9 new real designers materialized after hardening cleanup:
Lynsey Humphrey · Alder & Tweed
Lara Heffernan · Berg Development · Brookfield
Ashley Kruchoski · Katiebassett · Minneapolis
Jerry Sanflippo · Fos Interiors · Palm Springs
Amy L Young · Eric Hedlund Design · Coeur d'Alene
(+ 4 noise rows wiped pre-hardening)
Memory codified: feedback_designer_directory_pii_policy.md.
Ledger now:
sod_designers (real) 24 (was 15)
sod_firms 104 (was 92)
sod_links 86 (was 83)
candidates_real 562
candidates_with_name 427
Background jobs:
PID 29524 asseeninmovies wikidata bg3 (10k more, 7873/290945 done)
Files touched
M scrapers/extract-from-signatures.js
Diff
commit fcd8cd83ae2226d598b945ee03e1fa675db25ea5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 12 10:11:23 2026 -0700
feat(starsofdesign): areas_served + PII guardrails + sig-extract pass 2
Per Steve's correction 2026-05-12: 'city and state ok, no zip, no
street address, and AREAS THEY SERVE.'
Schema change (idempotent ALTER):
ALTER TABLE sod_designers ADD COLUMN areas_served TEXT[];
ALTER TABLE sod_firms ADD COLUMN areas_served TEXT[];
CREATE INDEX (gin) on each.
LLM extractor prompt rewrite (extract-from-signatures.js SYSTEM):
- Asks for {full_name, firm_name, firm_city, firm_state, firm_country,
firm_role, areas_served[], linkedin_personal, linkedin_company,
website, instagram}.
- Explicit rule: NEVER extract street address, NEVER extract zip,
NEVER extract phone. Phone is opt-in via claim flow, not scraping.
- For areas_served: look for 'Serving:', 'Areas:', 'We cover', or
parenthetical region phrases. Otherwise safely infer from city+state.
- If a signature contains '123 Main Street, Suite 4B, Brooklyn, NY 11201'
we keep 'Brooklyn'/'NY' only — never the street or zip.
Server-side PII cleanup of LLM output (in case the model echoes one
through anyway):
- firmCity scrubbed of any 'N <street> Street/Ave/etc.' patterns
- firmCity scrubbed of zip patterns (\d{5} and \d{5}-\d{4})
- areas_served filtered: no leading digits, no zip-shaped entries,
no <2-char or >60-char entries, dedupe + cap at 12.
Hallucination hardening (carryover from Steve's earlier note about
'Designer Wallcoverings' false firm):
- Reject bare first-name without local-part match: gemma3:12b
repeatedly returns 'Samantha' / 'Steve' / 'Sarah' when it can't find
a real name. New guard requires the first-name's first 4 chars to
appear in the email local-part.
- Reject firms that are just a website ('domaineinteriors.com').
Two-phase candidate select for the pass:
Priority 1 — empty display_name (need name to materialize designer)
Priority 2 — has name but no sod_designers row yet (firm/role enrichment)
Persisted in this pass:
city / state_or_region / country / areas_served on both sod_firms and
sod_designers. Instagram URL gets its own kind='instagram' row in
sod_links (in addition to existing linkedin / firm-site kinds).
Sig-extract pass 2 results (limit=120):
ok=51 withName=9 withFirm=13 skipped=69
9 new real designers materialized after hardening cleanup:
Lynsey Humphrey · Alder & Tweed
Lara Heffernan · Berg Development · Brookfield
Ashley Kruchoski · Katiebassett · Minneapolis
Jerry Sanflippo · Fos Interiors · Palm Springs
Amy L Young · Eric Hedlund Design · Coeur d'Alene
(+ 4 noise rows wiped pre-hardening)
Memory codified: feedback_designer_directory_pii_policy.md.
Ledger now:
sod_designers (real) 24 (was 15)
sod_firms 104 (was 92)
sod_links 86 (was 83)
candidates_real 562
candidates_with_name 427
Background jobs:
PID 29524 asseeninmovies wikidata bg3 (10k more, 7873/290945 done)
---
scrapers/extract-from-signatures.js | 105 ++++++++++++++++++++++++++----------
1 file changed, 78 insertions(+), 27 deletions(-)
diff --git a/scrapers/extract-from-signatures.js b/scrapers/extract-from-signatures.js
index 469cb14..ea5beb4 100644
--- a/scrapers/extract-from-signatures.js
+++ b/scrapers/extract-from-signatures.js
@@ -52,20 +52,33 @@ Output STRICT JSON, no preamble:
{
"full_name": "<person's first+last> or null",
"firm_name": "<firm/studio/agency> or null",
- "firm_city": "<city the firm is in> or null",
- "firm_role": "<the person's title> or null (Principal, Senior Designer, Founder, Partner, etc.)",
+ "firm_city": "<city only — no street, no zip> or null",
+ "firm_state": "<state/province two-letter or full name> or null",
+ "firm_country": "<country if non-US> or null",
+ "firm_role": "<the person's title> or null",
+ "areas_served": ["<region>", ...] ,
"linkedin_personal": "<linkedin.com/in/... or null>",
"linkedin_company": "<linkedin.com/company/... or null>",
"website": "<firm primary URL or null>",
- "phone": "<phone (E.164 preferred) or null>"
+ "instagram": "<instagram URL or @handle or null>"
}
Rules:
- Don't invent. If a field isn't clearly in the body, return null.
+- **NEVER extract street address. NEVER extract zip / postal code.** City and
+ state only — Steve's directory PII policy. If the signature contains a
+ street address like "123 Main Street, Suite 4B" — IGNORE the street part,
+ keep only the city/state if present after it.
+- **NEVER extract phone numbers.** Phone is opt-in via the designer's claim
+ flow, not via signature scraping.
+- For areas_served, look for "Serving:", "Areas:", "We cover", or a
+ parenthetical region phrase. Otherwise infer SAFELY from city/state — a
+ Manhattan firm typically serves NYC + Hamptons; an LA firm typically
+ serves LA + Palm Springs. Return [] if you can't infer.
- Skip the email-quoting (lines starting with >).
- Skip footer disclaimers and unsubscribe links.
-- If the body is just a Shopify order notification with no signature, return
- all nulls.
+- If the body is a Shopify order notification with no signature, return all
+ nulls (and areas_served=[]).
- Strip emoji and decorative characters from names.
`;
@@ -168,13 +181,44 @@ async function processCandidate(c) {
// contact is NOT working for DW — that's our own signature inside an
// outgoing message the contact replied to. Refuse.
if (firm && /^Designer\s+Wallcoverings/i.test(firm)) firm = null;
- // GUARD: gemma3:12b occasionally pattern-matches the generic name "Samantha"
- // when it can't find a real one. Refuse if the email doesn't hint at that.
- if (name && /^samantha$/i.test(name) && !/samantha/i.test(c.email)) name = null;
+ // GUARD: gemma3:12b occasionally pattern-matches a generic first name
+ // ("Samantha", "Steve", "Sarah", "Jenny", "Vicky") when it can't find a
+ // real one — usually grabbing DW's outgoing signature embedded in a
+ // reply chain. Refuse a bare first-name unless it actually appears in
+ // the local-part of the email address.
+ if (name) {
+ const isJustFirstName = /^[A-Z][a-z]+$/.test(name);
+ const localPart = c.email.split('@')[0].toLowerCase();
+ const nameLow = name.toLowerCase();
+ if (isJustFirstName && !localPart.includes(nameLow.slice(0, 4))) name = null;
+ }
// GUARD: refuse firm names that are just the LLM's noise output.
if (firm && /^(N\/?A|Unknown|None|-)$/i.test(firm)) firm = null;
+ // GUARD: refuse firm names that are obviously just a website (the LLM
+ // sometimes echoes the domain as the firm name).
+ if (firm && /^[a-z0-9-]+\.(com|net|org|co|io|studio)$/i.test(firm)) firm = null;
+ // GUARD: per directory PII policy [[feedback_designer_directory_pii_policy]]
+ // — strip any street-address-shaped fragment that snuck into firm_city.
+ let firmCity = parsed.firm_city || null;
+ if (firmCity) {
+ firmCity = firmCity
+ .replace(/\b\d+\s+[A-Z][A-Za-z'.\-]+\s+(?:Street|St|Avenue|Ave|Road|Rd|Boulevard|Blvd|Lane|Ln|Drive|Dr|Court|Ct|Place|Pl|Way|Pkwy|Pike|Hwy|Highway|Suite|Ste|Unit|Apt)\b\.?,?\s*/gi, '')
+ .replace(/\b\d{5}(?:-\d{4})?\b/g, '') // zip
+ .trim().replace(/^,|,$/, '').trim();
+ if (firmCity.length < 2) firmCity = null;
+ }
+ const firmState = (parsed.firm_state || null);
+ const firmCountry = parsed.firm_country || null;
+ // areas_served — accept array of strings, dedupe, strip street/zip noise
+ let areas = Array.isArray(parsed.areas_served)
+ ? parsed.areas_served.filter(x => typeof x === 'string').map(s => s.trim())
+ .filter(s => s.length >= 2 && s.length < 60)
+ .filter(s => !/^\d/.test(s)) // no leading digits (addresses)
+ .filter(s => !/\b\d{5}\b/.test(s)) // no zip
+ : [];
+ areas = [...new Set(areas)].slice(0, 12);
- console.log(` ${c.email.padEnd(36)} → name=${name||'-'} firm=${firm||'-'} city=${parsed.firm_city||'-'}`);
+ console.log(` ${c.email.padEnd(36)} → name=${name||'-'} firm=${firm||'-'} city=${firmCity||'-'} state=${firmState||'-'} areas=${areas.length}`);
if (DRY) return { dry: true, parsed };
@@ -193,28 +237,34 @@ async function processCandidate(c) {
if (firm) {
const slug = slugify(firm);
const f = await pool.query(`
- INSERT INTO sod_firms (slug, name, city, primary_email)
- VALUES ($1, $2, $3, $4)
+ INSERT INTO sod_firms (slug, name, city, state_or_region, country, areas_served, primary_email)
+ VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (slug) DO UPDATE SET
- city = COALESCE(sod_firms.city, EXCLUDED.city),
- primary_email = COALESCE(sod_firms.primary_email, EXCLUDED.primary_email),
- updated_at = NOW()
+ city = COALESCE(sod_firms.city, EXCLUDED.city),
+ state_or_region = COALESCE(sod_firms.state_or_region, EXCLUDED.state_or_region),
+ country = COALESCE(sod_firms.country, EXCLUDED.country),
+ areas_served = COALESCE(sod_firms.areas_served, EXCLUDED.areas_served),
+ primary_email = COALESCE(sod_firms.primary_email, EXCLUDED.primary_email),
+ updated_at = NOW()
RETURNING id`,
- [slug, firm, parsed.firm_city, c.email]);
+ [slug, firm, firmCity, firmState, firmCountry, areas.length ? areas : null, c.email]);
firmId = f.rows[0].id;
}
if (name) {
const slug = slugify(name);
const d = await pool.query(`
- INSERT INTO sod_designers (slug, full_name, primary_email, city, role, headshot_source)
- VALUES ($1, $2, $3, $4, $5, 'made-with-ai')
+ INSERT INTO sod_designers (slug, full_name, primary_email, city, state_or_region, country, areas_served, role, 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),
- role = COALESCE(sod_designers.role, EXCLUDED.role),
- updated_at = NOW()
+ 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),
+ country = COALESCE(sod_designers.country, EXCLUDED.country),
+ areas_served = COALESCE(sod_designers.areas_served, EXCLUDED.areas_served),
+ role = COALESCE(sod_designers.role, EXCLUDED.role),
+ updated_at = NOW()
RETURNING id`,
- [slug, name, c.email, parsed.firm_city, parsed.firm_role]);
+ [slug, name, c.email, firmCity, firmState, firmCountry, areas.length ? areas : null, parsed.firm_role]);
designerId = d.rows[0].id;
if (firmId) {
await pool.query(`
@@ -224,11 +274,15 @@ async function processCandidate(c) {
[designerId, firmId, parsed.firm_role || 'Designer']);
}
}
- // 3. Stash any LinkedIn / website URLs
+ // 3. Stash any LinkedIn / website / instagram URLs. Phone deliberately NOT
+ // extracted — per directory PII policy phone is opt-in via claim flow.
+ let igUrl = parsed.instagram || null;
+ if (igUrl && igUrl.startsWith('@')) igUrl = `https://instagram.com/${igUrl.slice(1)}`;
for (const [url, kind] of [
[parsed.linkedin_personal, 'linkedin'],
[parsed.linkedin_company, 'linkedin'],
[parsed.website, 'firm-site'],
+ [igUrl, 'instagram'],
]) {
if (!url || !/^https?:\/\//.test(url)) continue;
if (!designerId && !firmId) continue;
@@ -238,10 +292,7 @@ async function processCandidate(c) {
ON CONFLICT DO NOTHING`,
[designerId, firmId, url, kind]);
}
- if (parsed.phone && designerId) {
- // store phone in sod_designers via bio fallback isn't right; skip for now.
- }
- return { ok: true, name, firm };
+ return { ok: true, name, firm, city: firmCity, state: firmState, areas_count: areas.length };
}
async function main() {
← 7dd41c8 video gallery — 75 verified YouTube videos from AIA/ASID/AD/
·
back to Stars of Design
·
feat(starsofdesign): /clients route — surface real DW client feeab56 →