← back to Stars of Design
data/schema-pg.sql
155 lines
-- 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
);