← back to Animals
migrations/001_initial_schema.sql
457 lines
-- Project: Animals — Initial schema
-- Compliance-first: every fact traceable to source_url; opt_out_flag respected
-- on every public surface. Mirrors the lawyer-directory + professional-directory
-- pattern, but for the animal-care ecosystem.
BEGIN;
-- ─── helper: updated_at trigger fn ──────────────────────────────────────────
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- ─── reference / dictionary ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sources (
id BIGSERIAL PRIMARY KEY,
source_name TEXT NOT NULL UNIQUE,
source_type TEXT NOT NULL CHECK (source_type IN (
'state_vet_board','aaha','akc','ukc','fci',
'petfinder','aspca','spca','ndgaa','ccpdt','apdt',
'pida','ibpsa','veccs','osm','google_places','manual','other'
)),
base_url TEXT NOT NULL,
terms_notes TEXT,
allowed_method TEXT NOT NULL CHECK (allowed_method IN ('api','crawl','manual','denied')),
rate_limit_rps NUMERIC(6,2),
robots_txt_url TEXT,
last_checked_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS species (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE -- 'dog','cat','bird','reptile','small_mammal','fish','exotic','livestock'
);
CREATE TABLE IF NOT EXISTS service_categories (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE, -- 'general_practice','emergency','dental','dermatology','cardiology','oncology','surgery','grooming','training','boarding','daycare','retail','adoption','breeding','vaccination','dental','radiology'
parent TEXT
);
-- ─── breeds (the consumer SEO engine) ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS breeds (
id BIGSERIAL PRIMARY KEY,
species_id BIGINT NOT NULL REFERENCES species(id),
slug TEXT NOT NULL UNIQUE,
common_name TEXT NOT NULL,
alt_names TEXT[],
origin_country TEXT,
recognized_by TEXT[], -- ['AKC','UKC','FCI','TICA',...]
group_name TEXT, -- AKC group: 'Sporting','Hound','Working',etc.
size_class TEXT, -- 'toy','small','medium','large','giant'
weight_lbs_min NUMERIC,
weight_lbs_max NUMERIC,
height_in_min NUMERIC,
height_in_max NUMERIC,
life_span_years_min NUMERIC,
life_span_years_max NUMERIC,
coat_type TEXT,
coat_colors TEXT[],
shedding_level INTEGER CHECK (shedding_level BETWEEN 1 AND 5),
energy_level INTEGER CHECK (energy_level BETWEEN 1 AND 5),
trainability INTEGER CHECK (trainability BETWEEN 1 AND 5),
good_with_kids INTEGER CHECK (good_with_kids BETWEEN 1 AND 5),
good_with_pets INTEGER CHECK (good_with_pets BETWEEN 1 AND 5),
hypoallergenic BOOLEAN,
common_health_issues TEXT[],
description_md TEXT, -- editorial; never autogenerated lies
hero_image_url TEXT, -- public domain only (Wikimedia, etc.)
hero_image_credit TEXT,
source_urls TEXT[],
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_breeds_species ON breeds(species_id);
CREATE INDEX IF NOT EXISTS idx_breeds_size ON breeds(size_class);
CREATE TRIGGER trg_breeds_updated BEFORE UPDATE ON breeds
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- ─── businesses (the unified table for all 9 categories) ───────────────────
-- One row per real-world business location. The `category` column tells you
-- whether it's a vet clinic, groomer, pet store, etc. Maps embed reads from
-- (latitude, longitude). No PostGIS dependency in v1 — we use Haversine in JS.
CREATE TABLE IF NOT EXISTS businesses (
id BIGSERIAL PRIMARY KEY,
category TEXT NOT NULL CHECK (category IN (
'vet_clinic','emergency_vet','specialty_hospital',
'groomer','pet_store','shelter','breeder',
'trainer','boarding','daycare','dog_park','other'
)),
name TEXT NOT NULL,
slug TEXT NOT NULL,
address TEXT,
city TEXT,
state TEXT,
zip TEXT,
county TEXT,
country TEXT DEFAULT 'US',
latitude NUMERIC(10,7),
longitude NUMERIC(10,7),
phone TEXT,
email TEXT,
website TEXT,
google_place_id TEXT UNIQUE,
rating NUMERIC(3,1),
review_count INTEGER,
-- vertical-specific
species_served BIGINT[], -- references species(id)
services_offered BIGINT[], -- references service_categories(id)
is_aaha_accredited BOOLEAN,
hours_json JSONB, -- {mon:[{open:'08:00',close:'18:00'}], ...}
online_booking_url TEXT,
appointment_email TEXT,
-- compliance
source_url TEXT,
source_id BIGINT REFERENCES sources(id) ON DELETE SET NULL,
opt_out_flag BOOLEAN NOT NULL DEFAULT FALSE,
opt_out_reason TEXT,
opt_out_at TIMESTAMPTZ,
verified_at TIMESTAMPTZ, -- last time the owner confirmed the listing
claimed_by BIGINT, -- app_users(id) once they claim
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (category, slug)
);
CREATE INDEX IF NOT EXISTS idx_biz_cat ON businesses(category);
CREATE INDEX IF NOT EXISTS idx_biz_zip ON businesses(zip);
CREATE INDEX IF NOT EXISTS idx_biz_city_st ON businesses(LOWER(city), state);
CREATE INDEX IF NOT EXISTS idx_biz_latlng ON businesses(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_biz_optout ON businesses(opt_out_flag);
CREATE INDEX IF NOT EXISTS idx_biz_website ON businesses(LOWER(website));
CREATE INDEX IF NOT EXISTS idx_biz_name_low ON businesses(LOWER(name));
CREATE TRIGGER trg_biz_updated BEFORE UPDATE ON businesses
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- ─── veterinarians (people, separate from clinics) ─────────────────────────
CREATE TABLE IF NOT EXISTS veterinarians (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
first_name TEXT,
last_name TEXT,
middle_name TEXT,
suffix TEXT,
credentials TEXT, -- 'DVM','VMD','PhD'...
state_license_no TEXT,
license_state TEXT,
license_status TEXT,
license_issued_date DATE,
vet_school TEXT,
graduation_year INTEGER,
primary_specialty TEXT,
secondary_specialties TEXT[],
species_focus BIGINT[],
bio_md TEXT,
profile_image_url TEXT,
source_url TEXT,
source_id BIGINT REFERENCES sources(id) ON DELETE SET NULL,
opt_out_flag BOOLEAN NOT NULL DEFAULT FALSE,
opt_out_reason TEXT,
opt_out_at TIMESTAMPTZ,
confidence_score NUMERIC(4,3) NOT NULL DEFAULT 0.000,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (license_state, state_license_no)
);
CREATE INDEX IF NOT EXISTS idx_vets_name_lower ON veterinarians(LOWER(last_name), LOWER(first_name));
CREATE INDEX IF NOT EXISTS idx_vets_specialty ON veterinarians(primary_specialty);
CREATE INDEX IF NOT EXISTS idx_vets_optout ON veterinarians(opt_out_flag);
CREATE TRIGGER trg_vets_updated BEFORE UPDATE ON veterinarians
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TABLE IF NOT EXISTS vet_business_links (
veterinarian_id BIGINT NOT NULL REFERENCES veterinarians(id) ON DELETE CASCADE,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
role TEXT, -- 'owner','associate','medical_director'
is_primary BOOLEAN NOT NULL DEFAULT FALSE,
source_url TEXT,
PRIMARY KEY (veterinarian_id, business_id)
);
-- ─── breeders / shelters extras ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS breeder_breeds (
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
breed_id BIGINT NOT NULL REFERENCES breeds(id) ON DELETE CASCADE,
PRIMARY KEY (business_id, breed_id)
);
CREATE TABLE IF NOT EXISTS shelter_animals (
id BIGSERIAL PRIMARY KEY,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
external_id TEXT, -- Petfinder ID etc.
name TEXT,
species_id BIGINT REFERENCES species(id),
breed_primary TEXT,
breed_secondary TEXT,
age_class TEXT, -- 'baby','young','adult','senior'
size_class TEXT,
sex TEXT,
spayed_neutered BOOLEAN,
description TEXT,
photo_urls TEXT[],
status TEXT, -- 'available','adopted','foster','hold'
source_url TEXT,
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (business_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_shelter_animals_biz ON shelter_animals(business_id);
-- ─── audit / provenance ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS raw_records (
id BIGSERIAL PRIMARY KEY,
source_id BIGINT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
source_url TEXT NOT NULL,
entity_type TEXT NOT NULL,
entity_id TEXT,
raw_json JSONB,
raw_html_path TEXT,
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
http_status INTEGER,
hash TEXT NOT NULL,
UNIQUE (source_id, hash)
);
CREATE INDEX IF NOT EXISTS idx_raw_source ON raw_records(source_id);
CREATE INDEX IF NOT EXISTS idx_raw_entity ON raw_records(entity_type, entity_id);
CREATE TABLE IF NOT EXISTS scrape_jobs (
id BIGSERIAL PRIMARY KEY,
source_id BIGINT NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
job_label TEXT,
status TEXT NOT NULL DEFAULT 'queued'
CHECK (status IN ('queued','running','completed','failed','aborted_compliance')),
started_at TIMESTAMPTZ,
finished_at TIMESTAMPTZ,
records_seen INTEGER NOT NULL DEFAULT 0,
records_kept INTEGER NOT NULL DEFAULT 0,
notes TEXT
);
-- ─── site_audits + mockups (Rail C upsell engine) ──────────────────────────
CREATE TABLE IF NOT EXISTS site_audits (
id BIGSERIAL PRIMARY KEY,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
audited_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
url TEXT NOT NULL,
final_url TEXT,
status_code INTEGER,
screenshot_path TEXT,
primary_color TEXT,
palette JSONB,
has_https BOOLEAN,
has_favicon BOOLEAN,
has_og_image BOOLEAN,
has_meta_viewport BOOLEAN,
has_analytics BOOLEAN,
has_h1 BOOLEAN,
has_schema_org BOOLEAN,
has_phone_visible BOOLEAN,
has_online_booking BOOLEAN, -- vet-specific signal
page_size_bytes INTEGER,
load_time_ms INTEGER,
font_count INTEGER,
image_count INTEGER,
link_count INTEGER,
marketing_score INTEGER,
suggestions TEXT[]
);
CREATE INDEX IF NOT EXISTS idx_audits_biz ON site_audits(business_id);
CREATE INDEX IF NOT EXISTS idx_audits_score ON site_audits(marketing_score DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_audits_when ON site_audits(audited_at DESC);
CREATE TABLE IF NOT EXISTS site_mockups (
id BIGSERIAL PRIMARY KEY,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
variant TEXT NOT NULL,
template_label TEXT NOT NULL,
screenshot_path TEXT NOT NULL,
html_path TEXT,
generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (business_id, variant)
);
CREATE INDEX IF NOT EXISTS idx_mockups_biz ON site_mockups(business_id);
-- ─── leads (Rail B funnel) ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS leads (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT NOT NULL,
phone TEXT,
category_wanted TEXT NOT NULL, -- mirrors businesses.category
species_wanted TEXT, -- 'dog','cat',...
service_wanted TEXT, -- 'general','emergency','grooming',...
zip TEXT,
city TEXT,
state TEXT,
description TEXT,
budget TEXT,
urgency TEXT, -- 'immediate','within_week','within_month','researching'
consent_to_contact BOOLEAN NOT NULL DEFAULT TRUE,
status TEXT NOT NULL DEFAULT 'new'
CHECK (status IN ('new','matched','contacted','won','lost','spam')),
matched_business_ids BIGINT[],
admin_notes TEXT,
ip TEXT,
user_agent TEXT,
source TEXT, -- 'public_form' | 'partner' | 'organic' | 'paid'
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_cat ON leads(category_wanted);
CREATE INDEX IF NOT EXISTS idx_leads_zip ON leads(zip);
CREATE INDEX IF NOT EXISTS idx_leads_created ON leads(created_at DESC);
CREATE TRIGGER trg_leads_updated BEFORE UPDATE ON leads
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TABLE IF NOT EXISTS lead_charges (
id BIGSERIAL PRIMARY KEY,
lead_id BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
amount_cents INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','charged','refunded','failed')),
stripe_charge_id TEXT,
charged_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- ─── app_users (clinic owner login) ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS app_users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
full_name TEXT,
phone TEXT,
role TEXT NOT NULL DEFAULT 'business_owner'
CHECK (role IN ('business_owner','admin','staff')),
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_login_at TIMESTAMPTZ
);
CREATE TRIGGER trg_app_users_updated BEFORE UPDATE ON app_users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- ─── upgrade_orders (Rail C pay event) ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS upgrade_orders (
id BIGSERIAL PRIMARY KEY,
app_user_id BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
business_id BIGINT REFERENCES businesses(id) ON DELETE SET NULL,
full_name TEXT NOT NULL,
email TEXT NOT NULL,
phone TEXT,
business_name TEXT,
website TEXT,
plan TEXT NOT NULL DEFAULT 'starter_499',
amount_cents INTEGER NOT NULL DEFAULT 49900,
status TEXT NOT NULL DEFAULT 'pending_payment'
CHECK (status IN ('pending_payment','paid','in_production','delivered','refunded','cancelled')),
payment_link TEXT,
stripe_session_id TEXT,
paid_at TIMESTAMPTZ,
delivered_at TIMESTAMPTZ,
notes TEXT,
ip TEXT,
user_agent TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_upgrade_status ON upgrade_orders(status);
CREATE INDEX IF NOT EXISTS idx_upgrade_email ON upgrade_orders(LOWER(email));
CREATE INDEX IF NOT EXISTS idx_upgrade_created ON upgrade_orders(created_at DESC);
CREATE TRIGGER trg_upgrade_updated BEFORE UPDATE ON upgrade_orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- ─── ad engine (Rail A) ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ad_advertisers (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
category TEXT, -- 'pet_food','insurance','retail','service'
contact_email TEXT,
active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS ad_creatives (
id BIGSERIAL PRIMARY KEY,
advertiser_id BIGINT NOT NULL REFERENCES ad_advertisers(id) ON DELETE CASCADE,
slot_size TEXT NOT NULL, -- '728x90','300x250','300x600','native_card'
headline TEXT,
body TEXT,
cta_text TEXT,
cta_url TEXT NOT NULL,
image_url TEXT,
target_keywords TEXT[], -- contextual matching: 'puppy','vaccination','senior_dog',...
target_categories TEXT[], -- match businesses.category
cpm_cents INTEGER, -- what we charge advertiser per 1000 impressions
active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_creatives_active ON ad_creatives(active) WHERE active;
CREATE TABLE IF NOT EXISTS ad_impressions (
id BIGSERIAL PRIMARY KEY,
creative_id BIGINT REFERENCES ad_creatives(id) ON DELETE SET NULL,
page_path TEXT,
ip TEXT,
user_agent TEXT,
ts TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_imp_creative ON ad_impressions(creative_id);
CREATE INDEX IF NOT EXISTS idx_imp_ts ON ad_impressions(ts DESC);
CREATE TABLE IF NOT EXISTS ad_clicks (
id BIGSERIAL PRIMARY KEY,
creative_id BIGINT REFERENCES ad_creatives(id) ON DELETE SET NULL,
page_path TEXT,
ip TEXT,
user_agent TEXT,
ts TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_clk_creative ON ad_clicks(creative_id);
-- ─── domain research (output of /scripts/research_domains.js) ──────────────
CREATE TABLE IF NOT EXISTS domain_candidates (
id BIGSERIAL PRIMARY KEY,
domain TEXT NOT NULL UNIQUE,
available BOOLEAN,
price_usd NUMERIC(10,2),
premium BOOLEAN,
registrar TEXT,
brandable_score INTEGER, -- 1-10 hand-rated
intended_use TEXT, -- 'consumer_directory' | 'b2b_upsell'
notes TEXT,
checked_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMIT;