213 lines
10 KiB
PL/PgSQL
213 lines
10 KiB
PL/PgSQL
-- =============================================================================
|
|
-- Behavioral Health Intelligence (BHI) Layer - Postgres schema extension
|
|
-- =============================================================================
|
|
-- This file adds BHI tables to the existing `brain` database that the base
|
|
-- Economic Brain agent is creating. DO NOT run until the base Brain schema
|
|
-- is finalized. Then run: psql -d brain -f schemas/bhi_tables.sql
|
|
--
|
|
-- All tables are prefixed `bhi_` to avoid any collision with the base Brain.
|
|
-- Foreign keys are intentionally soft (no REFERENCES) where the target table
|
|
-- belongs to the base Brain, so this file can be applied independently.
|
|
-- =============================================================================
|
|
|
|
BEGIN;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 1. Facilities master table
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_facilities (
|
|
facility_id SERIAL PRIMARY KEY,
|
|
ccn VARCHAR(20), -- CMS Certification Number
|
|
npi VARCHAR(20), -- National Provider Identifier
|
|
name TEXT NOT NULL,
|
|
address TEXT,
|
|
city TEXT,
|
|
state TEXT,
|
|
zip TEXT,
|
|
county_fips TEXT,
|
|
lat DOUBLE PRECISION,
|
|
lon DOUBLE PRECISION,
|
|
facility_type TEXT, -- IPF, PRTF, CMHC, SUD, acute, nursing_home, etc.
|
|
ownership TEXT, -- for-profit, non-profit, gov
|
|
bed_count INT,
|
|
psych_bed_count INT,
|
|
pediatric_psych_bed_count INT,
|
|
adolescent_unit BOOLEAN,
|
|
young_adult_unit BOOLEAN,
|
|
services_offered TEXT[],
|
|
populations_served TEXT[], -- ['adolescent','young_adult','adult','geriatric']
|
|
payment_accepted TEXT[],
|
|
medicaid_accepted BOOLEAN,
|
|
accreditation TEXT,
|
|
opened_date DATE,
|
|
closed_date DATE,
|
|
last_verified DATE,
|
|
source TEXT, -- 'cms_ipfqr','samhsa_locator','nppes', etc.
|
|
source_raw_id INT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_state ON bhi_facilities (state);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_county ON bhi_facilities (county_fips);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_ccn ON bhi_facilities (ccn);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_npi ON bhi_facilities (npi);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_type ON bhi_facilities (facility_type);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_facilities_pops ON bhi_facilities USING GIN (populations_served);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 2. Facility quality measures (IPFQR, Care Compare)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_facility_quality (
|
|
id SERIAL PRIMARY KEY,
|
|
facility_id INT REFERENCES bhi_facilities(facility_id) ON DELETE CASCADE,
|
|
measure_id TEXT, -- e.g. HBIPS-2, SUB-3, SMD, TOB-3
|
|
measure_name TEXT,
|
|
value NUMERIC,
|
|
benchmark NUMERIC,
|
|
period TEXT, -- '2024Q1', 'FY2024'
|
|
reported_at DATE,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_quality_facility ON bhi_facility_quality (facility_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_quality_measure ON bhi_facility_quality (measure_id);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 3. Facility financials from Medicare Cost Reports
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_facility_financials (
|
|
id SERIAL PRIMARY KEY,
|
|
facility_id INT REFERENCES bhi_facilities(facility_id) ON DELETE CASCADE,
|
|
year INT,
|
|
medicare_discharges INT,
|
|
medicaid_discharges INT,
|
|
psych_discharges INT,
|
|
psych_los_avg NUMERIC,
|
|
psych_revenue BIGINT,
|
|
psych_costs BIGINT,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_financials_facility ON bhi_facility_financials (facility_id);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_financials_year ON bhi_facility_financials (year);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 4. Demand indicators (CDC WONDER, BRFSS, YRBSS, IDEA, NSCH)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_demand_indicators (
|
|
id SERIAL PRIMARY KEY,
|
|
geo_type TEXT, -- 'state','county','msa','district'
|
|
geo_code TEXT, -- FIPS or code
|
|
measure TEXT, -- 'suicide_rate','overdose_rate','depression_pct', etc.
|
|
age_bracket TEXT, -- '13-17','18-25','all'
|
|
period TEXT,
|
|
value NUMERIC,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_demand_geo ON bhi_demand_indicators (geo_type, geo_code);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_demand_measure ON bhi_demand_indicators (measure);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_demand_age ON bhi_demand_indicators (age_bracket);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 5. Workforce (BLS OES)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_workforce (
|
|
id SERIAL PRIMARY KEY,
|
|
msa_code TEXT,
|
|
msa_name TEXT,
|
|
occupation_code TEXT, -- SOC code, e.g. 29-1223 (psychiatrists)
|
|
occupation_title TEXT,
|
|
employment INT,
|
|
annual_wage_median NUMERIC,
|
|
annual_wage_mean NUMERIC,
|
|
period TEXT, -- 'May2024'
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_workforce_msa ON bhi_workforce (msa_code);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_workforce_occ ON bhi_workforce (occupation_code);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 6. HRSA HPSA mental health shortage areas
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_shortages (
|
|
id SERIAL PRIMARY KEY,
|
|
hpsa_id TEXT,
|
|
state TEXT,
|
|
county_fips TEXT,
|
|
score INT, -- HPSA score 0-25 (higher = worse shortage)
|
|
population_served INT,
|
|
designated_date DATE,
|
|
withdrawn_date DATE,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_shortages_state ON bhi_shortages (state);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_shortages_county ON bhi_shortages (county_fips);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_shortages_score ON bhi_shortages (score);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 7. State RTF (Residential Treatment Facility) licensing data
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_rtf_licensing (
|
|
id SERIAL PRIMARY KEY,
|
|
state TEXT,
|
|
license_number TEXT,
|
|
facility_name TEXT,
|
|
facility_type TEXT,
|
|
capacity INT,
|
|
populations TEXT[],
|
|
services TEXT[],
|
|
inspection_date DATE,
|
|
violations JSONB,
|
|
status TEXT,
|
|
opened_date DATE,
|
|
closed_date DATE,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_rtf_state ON bhi_rtf_licensing (state);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_rtf_name ON bhi_rtf_licensing (facility_name);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 8. Policy events (Medicaid rules, state legislation)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_policy_events (
|
|
id SERIAL PRIMARY KEY,
|
|
event_type TEXT, -- 'medicaid_rule','state_law','federal_rule'
|
|
state TEXT,
|
|
title TEXT,
|
|
summary TEXT,
|
|
effective_date DATE,
|
|
url TEXT,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_policy_state ON bhi_policy_events (state);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_policy_eff_date ON bhi_policy_events (effective_date);
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 9. Crisis calls / EMS transports (NEMSIS aggregates)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS bhi_crisis_calls (
|
|
id SERIAL PRIMARY KEY,
|
|
state TEXT,
|
|
county_fips TEXT,
|
|
period TEXT,
|
|
call_count INT,
|
|
mental_health_calls INT,
|
|
transport_outcomes JSONB,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_crisis_state ON bhi_crisis_calls (state);
|
|
CREATE INDEX IF NOT EXISTS idx_bhi_crisis_county ON bhi_crisis_calls (county_fips);
|
|
|
|
COMMIT;
|
|
|
|
-- =============================================================================
|
|
-- Verify
|
|
-- =============================================================================
|
|
-- \dt bhi_*
|
|
-- SELECT tablename FROM pg_tables WHERE tablename LIKE 'bhi_%' ORDER BY tablename;
|