← back to Professional Directory
db/schema.sql
189 lines
-- Professional Directory — Doctors / LA County (v1)
-- Compliance-first: every fact must be traceable to source_url; opt_out flag is honored on every public surface.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;
-- ─── Reference tables ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sources (
id BIGSERIAL PRIMARY KEY,
source_name TEXT UNIQUE NOT NULL,
source_type TEXT, -- bulk | api | crawler
base_url TEXT,
terms_notes TEXT,
allowed_method TEXT, -- bulk_download | api | scrape_with_robots
rate_limit_rps NUMERIC(6,2),
robots_txt_url TEXT,
last_checked_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS specialties (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
taxonomy_code TEXT UNIQUE, -- NUCC code, e.g. 207RC0000X
parent_specialty TEXT
);
-- ─── Core entities ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS professionals (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
first_name TEXT,
last_name TEXT,
middle_name TEXT,
suffix TEXT,
title TEXT, -- MD, DO, PA, NP, ...
license_number TEXT,
license_type TEXT,
license_status TEXT,
license_issue_date DATE,
license_expiration_date DATE,
npi_number VARCHAR(10) UNIQUE,
gender CHAR(1),
medical_school TEXT,
graduation_year INT,
years_experience_estimate INT,
primary_specialty TEXT,
secondary_specialties TEXT[],
bio TEXT,
profile_image_url TEXT,
source_confidence_score NUMERIC(3,2),
opted_out BOOLEAN NOT NULL DEFAULT FALSE,
opted_out_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_professionals_last_first ON professionals(last_name, first_name);
CREATE INDEX IF NOT EXISTS idx_professionals_full_trgm ON professionals USING gin (full_name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_professionals_license ON professionals(license_number);
CREATE INDEX IF NOT EXISTS idx_professionals_optout ON professionals(opted_out);
CREATE TABLE IF NOT EXISTS organizations (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
type TEXT, -- hospital | clinic | private_practice | medical_group | surgery_center | urgent_care
address TEXT,
city TEXT,
state CHAR(2) DEFAULT 'CA',
zip TEXT,
county TEXT,
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
geocoded_at TIMESTAMPTZ,
phone TEXT,
website TEXT,
google_place_id TEXT UNIQUE, -- reserved; not populated in zero-dollar build
rating NUMERIC(3,2), -- reserved
review_count INT, -- reserved
npi_number VARCHAR(10) UNIQUE, -- Type 2 NPI
hcai_id TEXT,
cdph_license TEXT,
opted_out BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_organizations_zip ON organizations(zip);
CREATE INDEX IF NOT EXISTS idx_organizations_city ON organizations(city);
CREATE INDEX IF NOT EXISTS idx_organizations_name_trgm ON organizations USING gin (name gin_trgm_ops);
-- ─── Relationships ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS professional_locations (
id BIGSERIAL PRIMARY KEY,
professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
organization_id BIGINT REFERENCES organizations(id) ON DELETE SET NULL,
role TEXT, -- attending | partner | employed | privileges
address TEXT,
phone TEXT,
accepting_new_patients BOOLEAN,
appointment_url TEXT,
source_url TEXT,
is_primary BOOLEAN NOT NULL DEFAULT FALSE,
last_verified_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_prof_loc_professional ON professional_locations(professional_id);
CREATE INDEX IF NOT EXISTS idx_prof_loc_organization ON professional_locations(organization_id);
CREATE TABLE IF NOT EXISTS professional_specialties (
professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
specialty_id BIGINT REFERENCES specialties(id) ON DELETE CASCADE,
source TEXT,
confidence_score NUMERIC(3,2),
PRIMARY KEY (professional_id, specialty_id)
);
CREATE TABLE IF NOT EXISTS emails (
id BIGSERIAL PRIMARY KEY,
professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
organization_id BIGINT REFERENCES organizations(id) ON DELETE CASCADE,
email CITEXT NOT NULL,
email_type TEXT, -- personal_public | office | appointment | admin | unknown
source_url TEXT,
discovered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_verified_at TIMESTAMPTZ,
verification_status TEXT, -- unverified | smtp_ok | bounced | opt_out
CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS idx_emails_email ON emails(email);
CREATE TABLE IF NOT EXISTS phones (
id BIGSERIAL PRIMARY KEY,
professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
organization_id BIGINT REFERENCES organizations(id) ON DELETE CASCADE,
phone TEXT NOT NULL,
phone_type TEXT, -- office | appointment | fax | answering_service
source_url TEXT,
last_verified_at TIMESTAMPTZ,
CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS idx_phones_phone ON phones(phone);
-- ─── Provenance tables ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS raw_records (
id BIGSERIAL PRIMARY KEY,
source_id BIGINT REFERENCES sources(id),
source_url TEXT,
entity_type TEXT, -- professional | organization | location | …
entity_id BIGINT,
raw_json JSONB,
raw_html_path TEXT,
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
hash TEXT UNIQUE NOT NULL,
http_status INT
);
CREATE INDEX IF NOT EXISTS idx_raw_records_entity ON raw_records(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_raw_records_url ON raw_records(source_url);
CREATE TABLE IF NOT EXISTS scrape_jobs (
id BIGSERIAL PRIMARY KEY,
source_id BIGINT REFERENCES sources(id),
job_label TEXT,
status TEXT NOT NULL DEFAULT 'queued', -- queued | running | done | failed | aborted_compliance
started_at TIMESTAMPTZ,
finished_at TIMESTAMPTZ,
records_found INT NOT NULL DEFAULT 0,
records_inserted INT NOT NULL DEFAULT 0,
records_updated INT NOT NULL DEFAULT 0,
records_skipped INT NOT NULL DEFAULT 0,
error_message TEXT,
checkpoint JSONB
);
CREATE INDEX IF NOT EXISTS idx_scrape_jobs_status ON scrape_jobs(status);
-- ─── updated_at trigger ─────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_professionals_updated ON professionals;
CREATE TRIGGER trg_professionals_updated BEFORE UPDATE ON professionals
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
DROP TRIGGER IF EXISTS trg_organizations_updated ON organizations;
CREATE TRIGGER trg_organizations_updated BEFORE UPDATE ON organizations
FOR EACH ROW EXECUTE FUNCTION set_updated_at();