economic-brain-bhi/docs/integration_plan.md

BHI Layer — Integration Plan

Steps to merge the BHI layer into the base Economic Brain after the base build finishes.

Prereqs (verified before step 1):

• Base Brain is running: psql -d brain -c '\dt' shows core tables including job_runs.
• /home/ubuntu/economic-brain/ contains a working jobs/ directory structure.
• DATABASE_URL env var exported and pointing at the brain Postgres.

---

1. Apply the BHI schema

cd /home/ubuntu/economic-brain-bhi
psql "$DATABASE_URL" -f schemas/bhi_tables.sql
psql "$DATABASE_URL" -c "\dt bhi_*"
# Expect 9 tables: bhi_facilities, bhi_facility_quality, bhi_facility_financials,
# bhi_demand_indicators, bhi_workforce, bhi_shortages, bhi_rtf_licensing,
# bhi_policy_events, bhi_crisis_calls

2. Copy ingestion jobs into the Brain's jobs tree

mkdir -p /home/ubuntu/economic-brain/jobs/bhi
cp /home/ubuntu/economic-brain-bhi/jobs/ingestion/*.py /home/ubuntu/economic-brain/jobs/bhi/
# _common.py is included; it reads DATABASE_URL from env already

Install Python deps if the base Brain doesn't already have them:

pip install requests psycopg2-binary

3. Smoke test every Phase A job (no DB writes)

cd /home/ubuntu/economic-brain/jobs/bhi
for f in cms_ipfqr.py cms_hospital_compare.py cms_nursing_home.py \
         samhsa_locator.py hrsa_hpsa.py nppes.py cdc_brfss.py \
         cdc_yrbss.py cdc_wonder_mortality.py bls_oes.py cms_pos.py \
         samhsa_nssats_nmhss.py idea_part_b.py nsch.py; do
  echo "=== $f ==="
  python3 "$f" test || echo "FAIL: $f"
done

Every job should print OK: and exit 0. If any fail, fix the endpoint/URL in the job file before proceeding.

4. Run jobs in dependency order

# Facilities first (feed bhi_facilities.facility_id FK for quality/financials)
python3 cms_ipfqr.py
python3 cms_hospital_compare.py
python3 cms_nursing_home.py
python3 samhsa_locator.py
python3 cms_pos.py
python3 samhsa_nssats_nmhss.py
python3 nppes.py

# Shortages + demand (independent)
python3 hrsa_hpsa.py
python3 cdc_wonder_mortality.py
python3 cdc_brfss.py
python3 cdc_yrbss.py
python3 idea_part_b.py
python3 nsch.py

# Workforce
python3 bls_oes.py

Monitor job_runs:

SELECT job_name, status, started_at, finished_at, error
FROM job_runs WHERE job_name LIKE 'bhi_%' ORDER BY started_at DESC;

5. Import n8n workflows (scheduled refresh)

Create workflows in n8n (or add to existing scheduler):

Workflow	Cron	Script
BHI: CMS facilities refresh	0 3 * * 1 (weekly Mon 3am)	cms_ipfqr.py, cms_hospital_compare.py, cms_nursing_home.py
BHI: SAMHSA locator refresh	0 4 1 * * (monthly)	samhsa_locator.py
BHI: HRSA HPSA refresh	0 5 * * 2 (weekly Tue 5am)	hrsa_hpsa.py
BHI: CDC demand refresh	0 6 1 * * (monthly)	cdc_brfss.py, cdc_yrbss.py, cdc_wonder_mortality.py
BHI: Workforce refresh	0 7 1 */3 * (quarterly)	bls_oes.py
BHI: CMS POS refresh	0 8 1 */3 * (quarterly)	cms_pos.py

Workflow template: Cron node -> Execute Command (python3 /home/ubuntu/economic-brain/jobs/bhi/<script>.py) -> if non-zero, send alert to Slack / email.

6. Add command center page

Create /home/ubuntu/command-center/pages/brain/behavioral-health.html (or equivalent in the Brain's command-center framework) with sections:

1. Facility map — Leaflet map of bhi_facilities colored by facility_type, filterable by adolescent_unit / young_adult_unit.
2. HPSA heatmap — county-level choropleth of bhi_shortages.score.
3. Demand indicators panel — small multiples of suicide rate, overdose rate, BRFSS depression by state, split by age bracket.
4. Composite ranking table — top 50 opportunities by composite_score (see scoring.md).
5. Recent policy events feed — last 20 rows from bhi_policy_events ordered by effective_date DESC.
6. Job status widget — last run of each bhi_* job from job_runs.

Route: /brain/behavioral-health.

7. Test queries (acceptance smoke tests)

-- Facility count by type
SELECT facility_type, count(*) FROM bhi_facilities GROUP BY 1 ORDER BY 2 DESC;

-- Top 20 worst MH HPSAs
SELECT state, county_fips, score, population_served
FROM bhi_shortages WHERE withdrawn_date IS NULL
ORDER BY score DESC LIMIT 20;

-- Adolescent suicide rates, top states
SELECT geo_code, value FROM bhi_demand_indicators
WHERE measure='suicide_rate' AND age_bracket='13-17'
ORDER BY value DESC LIMIT 20;

-- Counties with IPF but zero adolescent units (cross-check)
SELECT state, count(*) FILTER (WHERE adolescent_unit) AS adolescent_units,
       count(*) AS total
FROM bhi_facilities WHERE facility_type='IPF' GROUP BY state ORDER BY 2 ASC;

-- Workforce shortage: psychiatrists, top wage growth MSAs
SELECT msa_name, annual_wage_median
FROM bhi_workforce WHERE occupation_code='29-1223'
ORDER BY annual_wage_median DESC LIMIT 20;

-- job run health
SELECT job_name, status, count(*)
FROM job_runs WHERE job_name LIKE 'bhi_%'
GROUP BY 1, 2;

If every query returns rows and no job_run shows status='error', the BHI layer is live.

8. Git merge to main Brain repo

cd /home/ubuntu/economic-brain
git checkout -b bhi-layer-merge
cp -r /home/ubuntu/economic-brain-bhi/schemas/bhi_tables.sql schemas/
cp -r /home/ubuntu/economic-brain-bhi/jobs/ingestion/* jobs/bhi/
cp -r /home/ubuntu/economic-brain-bhi/docs/* docs/bhi/
git add schemas/bhi_tables.sql jobs/bhi docs/bhi
git commit -m "Integrate BHI layer"
git push origin bhi-layer-merge
# Open PR for review on Gitea