`migrations/001_initial_schema.sql`

234 lines
-- Lawyer Professional Directory — Initial Schema
-- Compliance-first design: every fact must be traceable to source_url; opt-out flag respected on every public surface.

BEGIN;

-- ─── Reference / dictionary tables ──────────────────────────────────────────

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 (
                    'official_registry', 'government', 'court', 'bar_association',
                    'firm_website', 'api', 'directory'
                  )),
  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 practice_areas (
  id           BIGSERIAL PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE,
  parent_area  TEXT
);

-- ─── Core entities ──────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS organizations (
  id                BIGSERIAL PRIMARY KEY,
  name              TEXT NOT NULL,
  type              TEXT NOT NULL CHECK (type IN (
                      'law_firm', 'solo_practice', 'legal_aid',
                      'government_office', 'court', 'bar_association'
                    )),
  address           TEXT,
  city              TEXT,
  state             TEXT DEFAULT 'CA',
  zip               TEXT,
  county            TEXT,
  phone             TEXT,
  website           TEXT,
  google_place_id   TEXT UNIQUE,
  rating            NUMERIC(3,1),
  review_count      INTEGER,
  source_url        TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_organizations_name_lower ON organizations (LOWER(name));
CREATE INDEX IF NOT EXISTS idx_organizations_zip ON organizations (zip);
CREATE INDEX IF NOT EXISTS idx_organizations_county ON organizations (county);
CREATE INDEX IF NOT EXISTS idx_organizations_website ON organizations (LOWER(website));

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,
  bar_number                  TEXT UNIQUE,
  license_status              TEXT,
  license_status_date         DATE,
  admission_date              DATE,
  years_experience_estimate   INTEGER,
  law_school                  TEXT,
  graduation_year             INTEGER,
  primary_practice_area       TEXT,
  secondary_practice_areas    TEXT[],
  bio                         TEXT,
  languages                   TEXT[],
  profile_image_url           TEXT,
  source_confidence_score     NUMERIC(4,3) NOT NULL DEFAULT 0.000,
  confidence_tier             TEXT CHECK (confidence_tier IN ('high', 'medium', 'low', 'unverified')),
  opt_out_flag                BOOLEAN NOT NULL DEFAULT FALSE,
  opt_out_reason              TEXT,
  opt_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 (LOWER(last_name), LOWER(first_name));
CREATE INDEX IF NOT EXISTS idx_professionals_status ON professionals (license_status);
CREATE INDEX IF NOT EXISTS idx_professionals_practice ON professionals (primary_practice_area);
CREATE INDEX IF NOT EXISTS idx_professionals_opt_out ON professionals (opt_out_flag);

-- ─── Relationships / multi-valued attributes ────────────────────────────────

CREATE TABLE IF NOT EXISTS professional_locations (
  id                BIGSERIAL PRIMARY KEY,
  professional_id   BIGINT NOT NULL REFERENCES professionals(id) ON DELETE CASCADE,
  organization_id   BIGINT REFERENCES organizations(id) ON DELETE SET NULL,
  role              TEXT,
  address           TEXT,
  phone             TEXT,
  email             TEXT,
  appointment_url   TEXT,
  source_url        TEXT,
  is_primary        BOOLEAN NOT NULL DEFAULT FALSE,
  last_verified_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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_practice_areas (
  professional_id    BIGINT NOT NULL REFERENCES professionals(id) ON DELETE CASCADE,
  practice_area_id   BIGINT NOT NULL REFERENCES practice_areas(id) ON DELETE CASCADE,
  source             TEXT,
  confidence_score   NUMERIC(4,3) NOT NULL DEFAULT 0.000,
  PRIMARY KEY (professional_id, practice_area_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                 TEXT NOT NULL,
  email_type            TEXT NOT NULL CHECK (email_type IN ('public_direct', 'office', 'intake', 'admin', 'unknown')),
  source_url            TEXT,
  discovered_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_verified_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  verification_status   TEXT NOT NULL DEFAULT 'unverified'
                          CHECK (verification_status IN ('unverified', 'syntactically_valid', 'mx_valid', 'bounced', 'opted_out')),
  CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
);

CREATE INDEX IF NOT EXISTS idx_emails_email ON emails (LOWER(email));
CREATE INDEX IF NOT EXISTS idx_emails_professional ON emails (professional_id);
CREATE INDEX IF NOT EXISTS idx_emails_organization ON emails (organization_id);

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 CHECK (phone_type IN ('office', 'direct', 'mobile', 'fax', 'intake', 'unknown')),
  source_url        TEXT,
  last_verified_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
);

CREATE INDEX IF NOT EXISTS idx_phones_professional ON phones (professional_id);
CREATE INDEX IF NOT EXISTS idx_phones_organization ON phones (organization_id);

CREATE TABLE IF NOT EXISTS education (
  id                BIGSERIAL PRIMARY KEY,
  professional_id   BIGINT NOT NULL REFERENCES professionals(id) ON DELETE CASCADE,
  school_name       TEXT NOT NULL,
  degree            TEXT,
  graduation_year   INTEGER,
  source_url        TEXT
);

CREATE INDEX IF NOT EXISTS idx_education_professional ON education (professional_id);

CREATE TABLE IF NOT EXISTS bar_admissions (
  id                BIGSERIAL PRIMARY KEY,
  professional_id   BIGINT NOT NULL REFERENCES professionals(id) ON DELETE CASCADE,
  jurisdiction      TEXT NOT NULL,
  bar_number        TEXT,
  admission_date    DATE,
  status            TEXT,
  source_url        TEXT,
  UNIQUE (professional_id, jurisdiction, bar_number)
);

CREATE INDEX IF NOT EXISTS idx_bar_admissions_professional ON bar_admissions (professional_id);

-- ─── Audit / provenance tables ──────────────────────────────────────────────

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_records_source ON raw_records (source_id);
CREATE INDEX IF NOT EXISTS idx_raw_records_url ON raw_records (source_url);
CREATE INDEX IF NOT EXISTS idx_raw_records_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_found       INTEGER NOT NULL DEFAULT 0,
  records_inserted    INTEGER NOT NULL DEFAULT 0,
  records_updated     INTEGER NOT NULL DEFAULT 0,
  records_skipped     INTEGER NOT NULL DEFAULT 0,
  error_message       TEXT,
  checkpoint          JSONB
);

CREATE INDEX IF NOT EXISTS idx_scrape_jobs_source ON scrape_jobs (source_id);
CREATE INDEX IF NOT EXISTS idx_scrape_jobs_status ON scrape_jobs (status);

-- ─── Trigger: auto-update updated_at ───────────────────────────────────────

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();

COMMIT;