`db/migrations/0002_seed_sources.sql`

142 lines
-- Seed the sources registry. Idempotent (ON CONFLICT (source_name) DO UPDATE).

INSERT INTO sources (source_name, source_type, base_url, terms_notes, allowed_method, rate_limit_rps, robots_txt_url) VALUES
  -- Stage 1: NPI (THE primary anchor)
  ('NPPES NPI Registry (Bulk)', 'bulk',
    'https://download.cms.gov/nppes/NPI_Files.html',
    'CMS public-domain monthly bulk file of all NPIs. Free, no key. Filter to state=CA + LA County ZIPs.',
    'bulk_download', NULL,
    'https://download.cms.gov/robots.txt'),

  ('NPPES NPI Registry (API)', 'api',
    'https://npiregistry.cms.hhs.gov/api/',
    'Free CMS REST API for ad-hoc lookups (200 records/page max, no key). Use for re-verifies, not bulk loads.',
    'api', 5.0, NULL),

  -- Stage 2: license enrichment
  ('CA Medical Board (MBC) — Access DB', 'bulk',
    'https://www.mbc.ca.gov/Resources/Statistics/License-Lookup-System.aspx',
    'Weekly downloadable Microsoft Access DB of all CA physician licenses. Public records, free.',
    'bulk_download', NULL,
    'https://www.mbc.ca.gov/robots.txt'),

  ('DCA License Search', 'api',
    'https://search.dca.ca.gov/',
    'CA Dept of Consumer Affairs license lookup — covers MD/DO/RN/PA/NP and more. JSON endpoint, polite-use.',
    'api', 1.0,
    'https://search.dca.ca.gov/robots.txt'),

  -- Stage 3: CMS enrichment
  ('CMS Care Compare (Doctors and Clinicians)', 'bulk',
    'https://data.cms.gov/provider-data/dataset/mj5m-pzi6',
    'Public CMS dataset, hospital affiliations + group practice + specialties. Bulk CSV.',
    'bulk_download', NULL, NULL),

  ('CMS Open Payments', 'bulk',
    'https://openpaymentsdata.cms.gov/',
    'Sunshine Act payments to physicians from drug/device makers. Bulk CSV.',
    'bulk_download', NULL, NULL),

  -- Stage 4: facilities
  ('CA HCAI Hospital Annual Utilization', 'bulk',
    'https://hcai.ca.gov/data/healthcare-facility-data/',
    'Department of Health Care Access and Information — hospital licensure + utilization. Bulk CSV.',
    'bulk_download', NULL, NULL),

  ('CDPH Health Facility Information Database', 'bulk',
    'https://data.chhs.ca.gov/dataset/healthcare-facility-locations',
    'CA Dept of Public Health — every licensed health facility in CA. Bulk CSV.',
    'bulk_download', NULL, NULL),

  ('HRSA FQHC Locator', 'api',
    'https://data.hrsa.gov/data/download',
    'Federally Qualified Health Centers — bulk download + API. Free.',
    'bulk_download', NULL, NULL),

  ('CA Secretary of State Business Search', 'api',
    'https://bizfileonline.sos.ca.gov/api/Records/businesssearch',
    'CA SOS business entity search — find professional corps / medical groups by name. Polite-use.',
    'api', 0.5,
    'https://bizfileonline.sos.ca.gov/robots.txt'),

  -- Stage 5: hospital + practice crawlers (one source row per system; respect their robots)
  ('UCLA Health (uclahealth.org)', 'crawler',
    'https://www.uclahealth.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.uclahealth.org/robots.txt'),

  ('Cedars-Sinai (cedars-sinai.org)', 'crawler',
    'https://www.cedars-sinai.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.cedars-sinai.org/robots.txt'),

  ('Kaiser SoCal (mydoctor.kaiserpermanente.org)', 'crawler',
    'https://mydoctor.kaiserpermanente.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://mydoctor.kaiserpermanente.org/robots.txt'),

  ('Keck USC Medicine (keckmedicine.org)', 'crawler',
    'https://www.keckmedicine.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.keckmedicine.org/robots.txt'),

  ('Providence SoCal (providence.org)', 'crawler',
    'https://www.providence.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.providence.org/robots.txt'),

  ('Dignity Health SoCal (dignityhealth.org)', 'crawler',
    'https://www.dignityhealth.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.dignityhealth.org/robots.txt'),

  ('MLK Community Healthcare (mlkch.org)', 'crawler',
    'https://www.mlkch.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.mlkch.org/robots.txt'),

  ('Olive View-UCLA Medical Center (dhs.lacounty.gov/oliveview)', 'crawler',
    'https://dhs.lacounty.gov/olive-view-ucla-medical-center/',
    'LA County DHS — public hospital staff directory. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://dhs.lacounty.gov/robots.txt'),

  ('Harbor-UCLA Medical Center (dhs.lacounty.gov/harbor-ucla)', 'crawler',
    'https://dhs.lacounty.gov/harbor-ucla-medical-center/',
    'LA County DHS — public hospital staff directory. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://dhs.lacounty.gov/robots.txt'),

  ('Children''s Hospital Los Angeles (chla.org)', 'crawler',
    'https://www.chla.org/',
    'Public physician profiles. Robots-aware crawl, 0.5 rps.',
    'scrape_with_robots', 0.5,
    'https://www.chla.org/robots.txt'),

  -- Stage 6: free geocoding
  ('US Census Geocoding API', 'api',
    'https://geocoding.geo.census.gov/geocoder/',
    'Free, no key. Address → lat/lng. Polite-use ~1 rps.',
    'api', 1.0, NULL),

  ('OpenStreetMap Nominatim', 'api',
    'https://nominatim.openstreetmap.org/',
    'Free, no key. Polite-use 1 rps strict, identify with User-Agent.',
    'api', 1.0,
    'https://nominatim.openstreetmap.org/robots.txt')

ON CONFLICT (source_name) DO UPDATE SET
  base_url       = EXCLUDED.base_url,
  terms_notes    = EXCLUDED.terms_notes,
  allowed_method = EXCLUDED.allowed_method,
  rate_limit_rps = EXCLUDED.rate_limit_rps,
  robots_txt_url = EXCLUDED.robots_txt_url,
  last_checked_at = NOW();