← back to Stars of Design
starsofdesign tick0: PG schema + AI placeholder + scaffold
bde44483d1d02314f24442b649df3a3ba11e5183 · 2026-05-12 07:37:19 -0700 · Steve Abrams
Files touched
A data/schema-pg.sqlA docs/PLAN.mdM package-lock.jsonM package.jsonA public/img/made-with-ai.svgA scrapers/promote-candidates.js
Diff
commit bde44483d1d02314f24442b649df3a3ba11e5183
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue May 12 07:37:19 2026 -0700
starsofdesign tick0: PG schema + AI placeholder + scaffold
---
data/schema-pg.sql | 154 +++++++++++++++++++++++++++++++++++++++++
docs/PLAN.md | 98 ++++++++++++++++++++++++++
package-lock.json | 151 +++++++++++++++++++++++++++++++++++++++-
package.json | 5 +-
public/img/made-with-ai.svg | 33 +++++++++
scrapers/promote-candidates.js | 122 ++++++++++++++++++++++++++++++++
6 files changed, 559 insertions(+), 4 deletions(-)
diff --git a/data/schema-pg.sql b/data/schema-pg.sql
new file mode 100644
index 0000000..cf7f893
--- /dev/null
+++ b/data/schema-pg.sql
@@ -0,0 +1,154 @@
+-- StarsOfDesign — PG schema (alongside the editorial designers.json that
+-- keeps the curated 26-name historic spine). This is the broader
+-- IMDb-Pro-style directory powered by Gmail signals + LinkedIn lookups.
+-- Lives in dw_unified, namespaced sod_*.
+
+CREATE TABLE IF NOT EXISTS sod_designers (
+ id BIGSERIAL PRIMARY KEY,
+ slug TEXT UNIQUE,
+ full_name TEXT NOT NULL,
+ primary_email TEXT,
+ alternate_emails TEXT[],
+ city TEXT,
+ state_or_region TEXT,
+ country TEXT,
+ role TEXT, -- 'Principal' | 'Founder' | 'Senior Designer' | etc.
+ bio TEXT,
+ signature TEXT, -- "midcentury minimalism with hand-thrown ceramics"
+ styles TEXT[],
+ era TEXT, -- only on editorial seed rows
+ era_sort INT,
+ active_years TEXT,
+ -- visual asset policy:
+ headshot_source TEXT DEFAULT 'made-with-ai', -- 'made-with-ai' | 'self-uploaded' | 'wikimedia-cc' | 'press-kit'
+ headshot_url TEXT,
+ -- premium claim tier (IMDb-Pro style):
+ claim_email TEXT,
+ claimed_at TIMESTAMPTZ,
+ premium_tier TEXT, -- NULL=basic | 'verified' | 'flagship'
+ premium_expires_at TIMESTAMPTZ,
+ premium_extras JSONB, -- {portfolio_urls[], press_links[], contact_form_enabled, current_projects[], awards[]}
+ -- featured (editorial seed):
+ is_editorial BOOLEAN DEFAULT false,
+ editorial_data JSONB, -- copy of the designers.json entry when editorial
+ created_at TIMESTAMPTZ DEFAULT NOW(),
+ updated_at TIMESTAMPTZ DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_sod_designers_name ON sod_designers USING gin (to_tsvector('english', full_name));
+CREATE INDEX IF NOT EXISTS idx_sod_designers_city ON sod_designers(city);
+CREATE INDEX IF NOT EXISTS idx_sod_designers_email ON sod_designers(primary_email);
+CREATE INDEX IF NOT EXISTS idx_sod_designers_claim ON sod_designers(premium_tier) WHERE premium_tier IS NOT NULL;
+
+CREATE TABLE IF NOT EXISTS sod_firms (
+ id BIGSERIAL PRIMARY KEY,
+ slug TEXT UNIQUE,
+ name TEXT NOT NULL,
+ city TEXT,
+ state_or_region TEXT,
+ country TEXT,
+ founded_year INT,
+ size_band TEXT, -- 'solo' | '2-5' | '6-20' | '21-50' | '50+'
+ website TEXT,
+ primary_email TEXT,
+ phone TEXT,
+ bio TEXT,
+ styles TEXT[],
+ -- claim:
+ claim_email TEXT,
+ claimed_at TIMESTAMPTZ,
+ premium_tier TEXT,
+ premium_expires_at TIMESTAMPTZ,
+ premium_extras JSONB,
+ logo_source TEXT DEFAULT 'made-with-ai',
+ logo_url TEXT,
+ created_at TIMESTAMPTZ DEFAULT NOW(),
+ updated_at TIMESTAMPTZ DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_sod_firms_name ON sod_firms USING gin (to_tsvector('english', name));
+CREATE INDEX IF NOT EXISTS idx_sod_firms_city ON sod_firms(city);
+
+CREATE TABLE IF NOT EXISTS sod_designer_firm (
+ id BIGSERIAL PRIMARY KEY,
+ designer_id BIGINT NOT NULL REFERENCES sod_designers(id) ON DELETE CASCADE,
+ firm_id BIGINT NOT NULL REFERENCES sod_firms(id) ON DELETE CASCADE,
+ title TEXT, -- 'Principal Designer' | 'Founder' | 'Senior Associate' etc.
+ is_current BOOLEAN DEFAULT true,
+ start_year INT,
+ end_year INT,
+ source TEXT, -- 'gmail-signature' | 'linkedin' | 'manual' | etc.
+ UNIQUE (designer_id, firm_id, title)
+);
+
+-- Links — anywhere the designer or firm appears on the web. LinkedIn,
+-- Instagram, personal site, press write-ups. Collapsed-chip area on the
+-- profile page renders from this.
+CREATE TABLE IF NOT EXISTS sod_links (
+ id BIGSERIAL PRIMARY KEY,
+ designer_id BIGINT REFERENCES sod_designers(id) ON DELETE CASCADE,
+ firm_id BIGINT REFERENCES sod_firms(id) ON DELETE CASCADE,
+ url TEXT NOT NULL,
+ kind TEXT NOT NULL, -- 'linkedin' | 'instagram' | 'firm-site' | 'personal-site' | 'press' | 'twitter' | 'pinterest' | 'houzz' | 'asid' | 'iida' | 'aia'
+ verified_at TIMESTAMPTZ,
+ added_by TEXT, -- 'gmail-ingest' | 'google-cse' | 'manual' | 'self-claimed'
+ created_at TIMESTAMPTZ DEFAULT NOW(),
+ UNIQUE (designer_id, firm_id, kind, url)
+);
+CREATE INDEX IF NOT EXISTS idx_sod_links_designer ON sod_links(designer_id);
+CREATE INDEX IF NOT EXISTS idx_sod_links_firm ON sod_links(firm_id);
+CREATE INDEX IF NOT EXISTS idx_sod_links_kind ON sod_links(kind);
+
+-- Projects / portfolio pieces (visible on premium profiles)
+CREATE TABLE IF NOT EXISTS sod_projects (
+ id BIGSERIAL PRIMARY KEY,
+ designer_id BIGINT REFERENCES sod_designers(id) ON DELETE CASCADE,
+ firm_id BIGINT REFERENCES sod_firms(id) ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ project_type TEXT, -- 'residential' | 'hospitality' | 'commercial' | 'set-design' | 'showhouse'
+ city TEXT,
+ year INT,
+ description TEXT,
+ cover_source TEXT DEFAULT 'made-with-ai',
+ cover_url TEXT,
+ press_url TEXT,
+ is_premium_pick BOOLEAN DEFAULT false,
+ created_at TIMESTAMPTZ DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_sod_projects_designer ON sod_projects(designer_id);
+
+-- Gmail recon — every candidate contact we identified from Steve's inbox,
+-- BEFORE the designer-yes/no triage. Many will be vendors / suppliers /
+-- press / not-actually-designers. LLM triage classifies in a later tick.
+CREATE TABLE IF NOT EXISTS sod_email_candidates (
+ id BIGSERIAL PRIMARY KEY,
+ email TEXT NOT NULL,
+ display_name TEXT,
+ domain TEXT,
+ first_seen TIMESTAMPTZ,
+ last_seen TIMESTAMPTZ,
+ thread_count INT DEFAULT 0,
+ steve_replied INT DEFAULT 0, -- count of threads where Steve replied
+ signal_score REAL, -- composite: replies/sent ratio, domain heuristic, etc.
+ status TEXT DEFAULT 'new', -- 'new' | 'is_designer' | 'is_firm' | 'is_vendor' | 'is_press' | 'is_noise' | 'merged'
+ promoted_designer_id BIGINT REFERENCES sod_designers(id) ON DELETE SET NULL,
+ promoted_firm_id BIGINT REFERENCES sod_firms(id) ON DELETE SET NULL,
+ last_signature TEXT, -- last seen signature block (used for role extraction)
+ notes TEXT,
+ created_at TIMESTAMPTZ DEFAULT NOW(),
+ updated_at TIMESTAMPTZ DEFAULT NOW(),
+ UNIQUE (email)
+);
+CREATE INDEX IF NOT EXISTS idx_sod_candidates_status ON sod_email_candidates(status);
+CREATE INDEX IF NOT EXISTS idx_sod_candidates_signal ON sod_email_candidates(signal_score DESC NULLS LAST);
+
+-- Ingest run audit
+CREATE TABLE IF NOT EXISTS sod_ingest_runs (
+ id BIGSERIAL PRIMARY KEY,
+ run_kind TEXT NOT NULL, -- 'gmail-candidates' | 'gmail-signature-parse' | 'linkedin-resolve' | 'editorial-import' | etc.
+ source TEXT,
+ started_at TIMESTAMPTZ DEFAULT NOW(),
+ finished_at TIMESTAMPTZ,
+ status TEXT NOT NULL DEFAULT 'running',
+ records_in BIGINT,
+ records_out BIGINT,
+ notes TEXT
+);
diff --git a/docs/PLAN.md b/docs/PLAN.md
new file mode 100644
index 0000000..efe15bc
--- /dev/null
+++ b/docs/PLAN.md
@@ -0,0 +1,98 @@
+# StarsOfDesign — IMDb-Pro-class interior-designer directory
+
+## Why
+Steve has thousands of designer-flavored Gmail threads + 26 hand-curated
+editorial profiles already in `data/designers.json`. Goal: stand the
+broad directory on top — pulled from Steve's inbox + LinkedIn + public
+trade rosters — with a **paid premium tier** modeled on IMDb Pro.
+
+## Hard rules
+1. **NEVER copy headshots without permission.** Default to `/img/made-with-ai.svg`. Real photos come from: (a) the claim flow upload, (b) Wikimedia Commons CC, (c) explicit press-kit grant.
+2. **LinkedIn data via public Google search only.** No scraping LinkedIn itself (TOS). Store `linkedin.com/in/<slug>` URLs as references; never crawl the platform.
+3. **Reply-thread signal only for candidate auto-promotion.** A name showing up in Steve's *reply* threads is a real contact. Newsletter-only addresses stay in `sod_email_candidates` until a human / LLM triages.
+4. No email, DNS, destructive ops during the YOLO loop. Reversible per tick.
+
+## Data sources (priority order)
+
+| Source | Auth | Why |
+|---|---|---|
+| Steve's Gmail (steveabramsdesigns@gmail.com) | claude.ai Gmail MCP | reply threads = real contacts |
+| info@designerwallcoverings.com | George agent (Mac2 :9850) | the canonical business inbox; currently 404, needs revival |
+| ASID public directory | scrape | ~25k members, public listing |
+| IIDA member directory | scrape | designer firms |
+| AIA member directory | scrape | residential architects |
+| LinkedIn (public search via Google CSE) | google CSE key in secrets | resolve `linkedin.com/in/` URLs per designer name + firm |
+| Houzz Pro public | scrape | ~3M pro listings with portfolios |
+| Pinterest public boards | embed only | aesthetic signature visualization |
+| Existing data/designers.json | local | 26 editorial historic spine (Dorothy Draper, Sister Parish, Hadley, etc.) |
+
+## Schema (in dw_unified, namespaced sod_*)
+- `sod_designers` — individual person (name, email, city, role, bio, premium tier, made-with-ai or real headshot)
+- `sod_firms` — design firm/studio entity
+- `sod_designer_firm` — many-to-many history (designer X was Principal at firm Y 2018-2023)
+- `sod_links` — every social/portfolio URL per designer/firm (LinkedIn, IG, Houzz, etc.) — collapsed-chip area on the profile page reads from this
+- `sod_projects` — portfolio pieces (residential / hospitality / commercial / showhouse)
+- `sod_email_candidates` — Gmail-extracted raw candidates BEFORE triage
+- `sod_ingest_runs` — audit log per ingest job
+
+## Premium upgrade tier (IMDb Pro analog)
+
+**Free / basic** (anyone shows up here once we find them):
+- Name, primary firm, city, role
+- 1 LinkedIn link (if found via Google), no contact form
+- Made-with-AI headshot placeholder, no portfolio gallery
+- "Claim this profile" CTA
+
+**Verified** ($9.99/mo or $99/yr — claimed + email-verified):
+- Real headshot upload, real firm logo
+- Custom bio, signature line, styles tags
+- Contact form (we proxy email)
+- Up to 6 portfolio entries
+- Verified badge
+
+**Flagship** ($29.99/mo or $299/yr):
+- Unlimited portfolio entries with project case studies
+- Press / award timeline
+- Calendar booking link integration
+- Featured rotation in city/style searches
+- Newsletter-blast inclusion (when Steve runs them)
+
+Stripe checkout already wired in big-red and other DW projects — reuse `STRIPE_SECRET_KEY` from secrets-manager.
+
+## Profile page UX
+
+**Top**: headshot + name + city + primary firm + "claim/edit" CTA
+**Collapsed-chip area** (per Steve's spec) — folds open on click:
+ - **Firm info** chip: name, city, website, founded year, size band
+ - **Links** chip: LinkedIn, Instagram, Houzz, personal site, press articles — pulled from `sod_links`
+ - **Past firms** chip: each prior firm with title + years
+**Body**: bio, signature line, style tags
+**Below the fold**: portfolio gallery (premium) OR claim CTA (basic)
+
+## Tick plan
+
+- **Tick 0 (this tick)** — PG schema, AI placeholder image, plan doc, Gmail ingest scaffold. Run a small recon to validate Gmail access works. **EXTENDS** the existing 26-designer editorial scaffold (don't replace it).
+- **Tick 1** — Gmail bulk ingest: walk Steve's reply threads from last 5 years, populate `sod_email_candidates` with sender email + display name + signal_score. Target 1000-2000 unique candidates.
+- **Tick 2** — LLM (Ollama qwen3:14b) triage on candidates → classify as `is_designer / is_firm / is_vendor / is_press / is_noise`. Promote yes-designers to `sod_designers`.
+- **Tick 3** — Signature-block parser: extract role + city + firm name from each candidate's last seen signature.
+- **Tick 4** — LinkedIn resolver: for each `sod_designers` row, hit Google CSE `site:linkedin.com/in "<name>" "<city>"` and store first result in `sod_links`.
+- **Tick 5** — Firm matcher: extract unique firm names from designers, dedupe, populate `sod_firms`.
+- **Tick 6** — ASID public directory scrape: ~25k members, populate as additional sod_designers (source='asid-public').
+- **Tick 7** — IIDA public scrape.
+- **Tick 8** — Houzz Pro public scrape.
+- **Tick 9** — Profile page rebuild — render `/designer/:slug` with collapsed-chip layout (firm info / links / past firms).
+- **Tick 10** — Claim flow: email verification, Stripe checkout, premium_extras editor.
+- **Tick 11** — info@ Gmail ingest (once George is revived).
+
+## Ports + processes
+- `:9928` — starsofdesign (Mac2 dev, existing)
+- prod: starsofdesign.com on Kamatera, nginx vhost ready, TLS pending DNS flip
+
+## Files
+- `server.js` — Express + EJS, the existing editorial-list scaffold (kept)
+- `routes/public.js` — JSON-backed editorial list (kept)
+- `data/designers.json` — 26 editorial seed entries (kept)
+- `data/schema-pg.sql` — sod_* tables (new)
+- `public/img/made-with-ai.svg` — universal placeholder (new)
+- `scrapers/` — Gmail / LinkedIn / ASID ingest scripts (added per tick)
+- `docs/PLAN.md` — this file
diff --git a/package-lock.json b/package-lock.json
index c645ca5..e5c1646 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -8,10 +8,11 @@
"name": "starsofdesign",
"version": "0.1.0",
"dependencies": {
- "dotenv": "^16.4.5",
+ "dotenv": "^16.6.1",
"ejs": "^3.1.10",
"express": "^4.21.0",
- "morgan": "^1.10.0"
+ "morgan": "^1.10.0",
+ "pg": "^8.20.0"
},
"engines": {
"node": ">=20"
@@ -726,12 +727,140 @@
"integrity": "sha512-A/AGNMFN3c8bOlvV9RreMdrv7jsmF9XIfDeCd87+I8RNg6s78BhJxMu69NEMHBSJFxKidViTEdruRwEk/WIKqA==",
"license": "MIT"
},
+ "node_modules/pg": {
+ "version": "8.20.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.20.0.tgz",
+ "integrity": "sha512-ldhMxz2r8fl/6QkXnBD3CR9/xg694oT6DZQ2s6c/RI28OjtSOpxnPrUCGOBJ46RCUxcWdx3p6kw/xnDHjKvaRA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.12.0",
+ "pg-pool": "^3.13.0",
+ "pg-protocol": "^1.13.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.3.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.3.0.tgz",
+ "integrity": "sha512-6lswVVSztmHiRtD6I8hw4qP/nDm1EJbKMRhf3HCYaqud7frGysPv7FYJ5noZQdhQtN2xJnimfMtvQq21pdbzyQ==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.12.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.12.0.tgz",
+ "integrity": "sha512-U7qg+bpswf3Cs5xLzRqbXbQl85ng0mfSV/J0nnA31MCLgvEaAo7CIhmeyrmJpOr7o+zm0rXK+hNnT5l9RHkCkQ==",
+ "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.13.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.13.0.tgz",
+ "integrity": "sha512-gB+R+Xud1gLFuRD/QgOIgGOBE2KCQPaPwkzBBGC9oG69pHTkhQeIuejVIk3/cnDyX39av2AxomQiyPT13WKHQA==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.13.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.13.0.tgz",
+ "integrity": "sha512-zzdvXfS6v89r6v7OcFCHfHlyG/wvry1ALxZo4LqgUoy7W9xhBDMaqOuMiF3qEV45VqsN6rdlcehHrfDtlCPc8w==",
+ "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/picocolors": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/picocolors/-/picocolors-1.1.1.tgz",
"integrity": "sha512-xceH2snhtb5M9liqDsmEw56le376mTZkEX/jEb/RxNFyegNul7eNslCXP9FDj/Lcu0X8KEyMceP2ntpaHrDEVA==",
"license": "ISC"
},
+ "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/proxy-addr": {
"version": "2.0.7",
"resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.7.tgz",
@@ -933,6 +1062,15 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "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/statuses": {
"version": "2.0.2",
"resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
@@ -990,6 +1128,15 @@
"engines": {
"node": ">= 0.8"
}
+ },
+ "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
index f8d5313..18828ca 100644
--- a/package.json
+++ b/package.json
@@ -9,10 +9,11 @@
"dev": "node server.js"
},
"dependencies": {
- "dotenv": "^16.4.5",
+ "dotenv": "^16.6.1",
"ejs": "^3.1.10",
"express": "^4.21.0",
- "morgan": "^1.10.0"
+ "morgan": "^1.10.0",
+ "pg": "^8.20.0"
},
"engines": {
"node": ">=20"
diff --git a/public/img/made-with-ai.svg b/public/img/made-with-ai.svg
new file mode 100644
index 0000000..b073dc4
--- /dev/null
+++ b/public/img/made-with-ai.svg
@@ -0,0 +1,33 @@
+<!-- StarsOfDesign — universal placeholder. Same policy as AsSeenInMovies:
+ never copy a designer's headshot or firm logo without permission.
+ Uses our "made with AI" stamp until they upload one themselves
+ (via the claim flow) or it's pulled from Wikimedia Commons. -->
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 600 600" preserveAspectRatio="xMidYMid slice" aria-label="Image not available — made with AI">
+ <defs>
+ <linearGradient id="bg" x1="0" y1="0" x2="1" y2="1">
+ <stop offset="0%" stop-color="#1a1714"/>
+ <stop offset="55%" stop-color="#2a2520"/>
+ <stop offset="100%" stop-color="#0d0c0a"/>
+ </linearGradient>
+ <radialGradient id="glow" cx="50%" cy="38%" r="55%">
+ <stop offset="0%" stop-color="#c9a14b" stop-opacity="0.22"/>
+ <stop offset="60%" stop-color="#c9a14b" stop-opacity="0.06"/>
+ <stop offset="100%" stop-color="#c9a14b" stop-opacity="0"/>
+ </radialGradient>
+ </defs>
+ <rect width="600" height="600" fill="url(#bg)"/>
+ <rect width="600" height="600" fill="url(#glow)"/>
+ <g transform="translate(300 320) rotate(-22)" opacity="0.30">
+ <text x="0" y="-90" font-family="ui-monospace,Menlo,monospace" font-size="36" font-weight="800" fill="#c9a14b" text-anchor="middle" letter-spacing="12">MADE WITH AI</text>
+ <text x="0" y="0" font-family="ui-monospace,Menlo,monospace" font-size="36" font-weight="800" fill="#c9a14b" text-anchor="middle" letter-spacing="12" opacity="0.85">MADE WITH AI</text>
+ <text x="0" y="90" font-family="ui-monospace,Menlo,monospace" font-size="36" font-weight="800" fill="#c9a14b" text-anchor="middle" letter-spacing="12" opacity="0.50">MADE WITH AI</text>
+ </g>
+ <g transform="translate(300 300)" fill="none" stroke="#f0ebe3" stroke-width="2.5">
+ <circle r="60" stroke-opacity="0.85"/>
+ <circle r="36" stroke-opacity="0.55"/>
+ <circle r="12" stroke-opacity="0.95" fill="#c9a14b" fill-opacity="0.18"/>
+ </g>
+ <text x="300" y="60" font-family="Cormorant Garamond, Georgia, serif" font-style="italic" font-weight="300" font-size="22" fill="#f0ebe3" text-anchor="middle" opacity="0.75" letter-spacing="3">StarsOfDesign</text>
+ <text x="300" y="540" font-family="-apple-system,BlinkMacSystemFont,Inter,sans-serif" font-size="11" font-weight="700" fill="#c9a14b" text-anchor="middle" letter-spacing="6" opacity="0.85">PLACEHOLDER · MADE WITH AI</text>
+ <text x="300" y="565" font-family="-apple-system,BlinkMacSystemFont,Inter,sans-serif" font-size="9.5" fill="#f0ebe3" text-anchor="middle" opacity="0.45" letter-spacing="2">Claim this profile to upload a real photo.</text>
+</svg>
diff --git a/scrapers/promote-candidates.js b/scrapers/promote-candidates.js
new file mode 100644
index 0000000..cf9d44e
--- /dev/null
+++ b/scrapers/promote-candidates.js
@@ -0,0 +1,122 @@
+#!/usr/bin/env node
+'use strict';
+// Promote high-signal `sod_email_candidates` rows into real `sod_designers`
+// (and `sod_firms` when the firm domain is obvious). Idempotent — running
+// it twice doesn't double-create. Default signal_score floor: 0.6.
+//
+// Usage:
+// node scrapers/promote-candidates.js # promote all >= 0.6
+// node scrapers/promote-candidates.js --min=0.8 # higher bar
+// node scrapers/promote-candidates.js --email=info@nickolsenstyle.com
+
+require('dotenv').config();
+const { Pool } = require('pg');
+
+const args = process.argv.slice(2).reduce((m, a) => {
+ const [k, v] = a.replace(/^--/, '').split('=');
+ m[k] = v ?? true; return m;
+}, {});
+const MIN_SCORE = parseFloat(args.min || '0.6');
+const ONLY_EMAIL = args.email || null;
+
+const pool = new Pool({
+ connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
+ max: 4,
+});
+
+function slugify(s) {
+ return String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 100);
+}
+
+function guessNameAndFirm(displayName, domain) {
+ // "Nick Olsen Style" → name=null, firm="Nick Olsen Style"
+ // "John Smith" → name="John Smith", firm=null
+ // "John Smith — Smith Design" → name="John Smith", firm="Smith Design"
+ if (!displayName) return { person: null, firm: null };
+ const split = displayName.split(/[-—|·]/);
+ if (split.length >= 2) {
+ return { person: split[0].trim(), firm: split.slice(1).join(' ').trim() };
+ }
+ // Heuristic: if domain matches the display name (vs gmail/hotmail/etc), it's a firm domain
+ const personalESPs = ['gmail.com','hotmail.com','yahoo.com','outlook.com','icloud.com','aol.com','me.com'];
+ const domHasName = domain && !personalESPs.includes(domain) && displayName.toLowerCase().includes(domain.split('.')[0].slice(0, 4));
+ if (domHasName || /\bdesign|\bstudio|\binteriors|\bgroup|\bassociates|\barchitect/i.test(displayName)) {
+ return { person: null, firm: displayName };
+ }
+ return { person: displayName, firm: null };
+}
+
+async function main() {
+ let params, where;
+ if (ONLY_EMAIL) { params = [ONLY_EMAIL]; where = `email = $1`; }
+ else { params = [MIN_SCORE]; where = `status='new' AND signal_score >= $1`; }
+ const r = await pool.query(
+ `SELECT * FROM sod_email_candidates WHERE ${where} ORDER BY signal_score DESC NULLS LAST`,
+ params
+ );
+ console.log(`[promote] ${r.rows.length} candidate(s) to promote (min=${MIN_SCORE})`);
+
+ for (const c of r.rows) {
+ const { person, firm } = guessNameAndFirm(c.display_name, c.domain);
+ let personId = null;
+ let firmId = null;
+
+ if (firm) {
+ const firmSlug = slugify(firm);
+ const f = await pool.query(`
+ INSERT INTO sod_firms (slug, name, website, primary_email)
+ VALUES ($1, $2, $3, $4)
+ ON CONFLICT (slug) DO UPDATE SET
+ primary_email = COALESCE(sod_firms.primary_email, EXCLUDED.primary_email)
+ RETURNING id`, [firmSlug, firm, c.domain ? `https://${c.domain}` : null, c.email]);
+ firmId = f.rows[0].id;
+ }
+
+ if (person) {
+ const slug = slugify(person);
+ const p = await pool.query(`
+ INSERT INTO sod_designers (slug, full_name, primary_email)
+ VALUES ($1, $2, $3)
+ ON CONFLICT (slug) DO UPDATE SET
+ primary_email = COALESCE(sod_designers.primary_email, EXCLUDED.primary_email)
+ RETURNING id`, [slug, person, c.email]);
+ personId = p.rows[0].id;
+ if (firmId) {
+ await pool.query(`
+ INSERT INTO sod_designer_firm (designer_id, firm_id, source)
+ VALUES ($1, $2, 'gmail-candidate-promotion')
+ ON CONFLICT DO NOTHING`, [personId, firmId]);
+ }
+ } else if (firm && !person) {
+ // Firm-only candidate: also create a placeholder designer = firm name
+ // so /designer/:slug works even before we know individual names.
+ const slug = slugify(firm);
+ const p = await pool.query(`
+ INSERT INTO sod_designers (slug, full_name, primary_email)
+ VALUES ($1, $2, $3)
+ ON CONFLICT (slug) DO UPDATE SET
+ primary_email = COALESCE(sod_designers.primary_email, EXCLUDED.primary_email)
+ RETURNING id`, [slug + '-firm', firm, c.email]);
+ personId = p.rows[0].id;
+ if (firmId) {
+ await pool.query(`
+ INSERT INTO sod_designer_firm (designer_id, firm_id, title, source)
+ VALUES ($1, $2, 'Firm placeholder', 'gmail-candidate-promotion')
+ ON CONFLICT DO NOTHING`, [personId, firmId]);
+ }
+ }
+
+ await pool.query(`
+ UPDATE sod_email_candidates
+ SET status = $1::text, promoted_designer_id = $2::bigint, promoted_firm_id = $3::bigint, updated_at = NOW()
+ WHERE id = $4::bigint`,
+ [firm && !person ? 'is_firm' : 'is_designer', personId, firmId, c.id]);
+
+ console.log(` ${c.email} → designer=${personId} firm=${firmId} (${person || ''}${person && firm ? ' @ ' : ''}${firm || ''})`);
+ }
+
+ console.log(`[promote] done.`);
+ await pool.end();
+}
+
+main().catch(e => { console.error('fatal:', e); process.exit(1); });
← 70b7912 add /sitemap.xml route + JSON-LD Person schema on designer d
·
back to Stars of Design
·
feat(starsofdesign/tick1): Gmail + Shopify recon → 236 candi 1542658 →