-- Hierarchical jurisdictions (country > state > county > city)
jurisdictions (
id, name, jurisdiction_type, -- 'country', 'state', 'county', 'city'
parent_id, -- FK to parent jurisdiction (e.g., Texas -> USA)
iso_code, population, gdp_per_capita,
constitution_type, -- constraints on policy space
data_quality_score, -- how complete is our policy inventory?
latitude, longitude, ...
)
-- Policy types (canonical definitions)
policy_types (
id, name, policy_category_id, policy_type,
is_continuous, typical_onset_delay_days,
typical_duration_of_effect_years, canonical_text, ...
)
-- Current policy inventory by jurisdiction
jurisdiction_policies (
jurisdiction_id, policy_type_id,
has_policy BOOLEAN,
policy_strength, -- e.g., tobacco tax amount, not just yes/no
implementation_date,
policy_details_json,
data_source, last_verified
)
-- Outcome variables (welfare metrics)
outcome_variables (
id, name, category, valence,
data_source, data_frequency, ...
)
-- Outcome measurements (time series of welfare metrics)
outcome_measurements (
jurisdiction_id, outcome_variable_id, measurement_date,
value, confidence_interval_low, confidence_interval_high, ...
)
-- Policy recommendations (generated output)
policy_recommendations (
jurisdiction_id, policy_type_id,
recommendation_type, -- 'enact', 'replace', 'repeal', 'maintain'
-- 'enact': new policy (jurisdiction doesn't have)
-- 'replace': modify existing policy (change level or approach)
-- 'repeal': remove policy entirely
-- 'maintain': keep current policy (evidence-optimal)
current_status, -- what they have now (NULL if nothing)
recommended_target, -- what evidence suggests (for replace/enact with level)
expected_effect, expected_effect_unit,
monetized_benefit_annual,
evidence_grade, priority_score,
blocking_factors, -- 'constitutional', 'federal_preemption', 'political', etc.
similar_jurisdictions, -- jurisdictions that adopted this successfully
-- Jurisdictional level guidance
minimum_effective_level, -- 'city', 'county', 'state', 'federal'
recommended_level, -- lowest effective level for max data collection
-- Tracking for feedback loop
tracking_metric, -- primary outcome to measure
tracking_data_source, -- where to get data
tracking_frequency, -- 'annual', 'quarterly', etc.
tracking_baseline_method, -- 'pre_implementation_3yr_avg', etc.
last_generated
)