-- ============================================================ -- Benefits Navigator — MySQL Database Schema -- Engine: InnoDB · Charset: utf8mb4 · Collation: utf8mb4_unicode_ci -- ============================================================ CREATE DATABASE IF NOT EXISTS benefits_navigator CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE benefits_navigator; -- ============================================================ -- TABLE: users -- Anonymous sessions are allowed (user_id can be NULL elsewhere) -- ============================================================ CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid VARCHAR(36) NOT NULL UNIQUE COMMENT 'Public-facing UUID', email VARCHAR(255) NULL UNIQUE, phone VARCHAR(20) NULL, first_name VARCHAR(100) NULL, last_name VARCHAR(100) NULL, preferred_language VARCHAR(10) NOT NULL DEFAULT 'en' COMMENT 'BCP47 code: en, es, zh, etc.', state CHAR(2) NULL COMMENT 'Two-letter state code', zip_code VARCHAR(10) NULL, password_hash VARCHAR(255) NULL, is_verified TINYINT(1) NOT NULL DEFAULT 0, last_login_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_email (email), INDEX idx_state (state), INDEX idx_zip (zip_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: federal_poverty_levels -- Updated annually — seed data provided below -- ============================================================ CREATE TABLE federal_poverty_levels ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, year YEAR NOT NULL, household_size TINYINT NOT NULL, annual_amount DECIMAL(10,2) NOT NULL, monthly_amount DECIMAL(10,2) GENERATED ALWAYS AS (annual_amount / 12) STORED, UNIQUE KEY uk_year_size (year, household_size) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Seed: 2025 FPL (48 contiguous states + DC) INSERT INTO federal_poverty_levels (year, household_size, annual_amount) VALUES (2025, 1, 15060.00), (2025, 2, 20440.00), (2025, 3, 25820.00), (2025, 4, 31200.00), (2025, 5, 36580.00), (2025, 6, 41960.00), (2025, 7, 47340.00), (2025, 8, 52720.00); -- ============================================================ -- TABLE: benefits_programs -- Master list of all assistance programs -- ============================================================ CREATE TABLE benefits_programs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, program_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g. SNAP, MEDICAID, WIC', name VARCHAR(255) NOT NULL, short_name VARCHAR(100) NULL, description TEXT NULL, category ENUM( 'food','health','housing','childcare', 'utilities','education','employment','cash','other' ) NOT NULL DEFAULT 'other', program_type ENUM('federal','state','local','nonprofit') NOT NULL DEFAULT 'federal', state CHAR(2) NULL COMMENT 'NULL = federal (available all states)', apply_url VARCHAR(500) NULL, official_site_url VARCHAR(500) NULL, phone_number VARCHAR(30) NULL, income_limit_pct_fpl DECIMAL(6,2) NULL COMMENT '130 means 130% of FPL', max_annual_income_2 DECIMAL(12,2) NULL COMMENT 'Calculated max for 2-person HH (cache)', benefit_period ENUM('monthly','annual','one_time','variable') NOT NULL DEFAULT 'monthly', min_benefit_amount DECIMAL(10,2) NULL, max_benefit_amount DECIMAL(10,2) NULL, priority_level ENUM('apply_now','high','medium','low') NOT NULL DEFAULT 'medium', citizenship_required TINYINT(1) NOT NULL DEFAULT 1, min_household_size TINYINT NOT NULL DEFAULT 1, max_household_size TINYINT NULL, processing_days_min TINYINT NULL, processing_days_max TINYINT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_category (category), INDEX idx_state (state), INDEX idx_active (is_active), INDEX idx_type (program_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Seed: Core Federal Programs INSERT INTO benefits_programs (program_code, name, short_name, category, program_type, apply_url, income_limit_pct_fpl, max_annual_income_2, benefit_period, min_benefit_amount, max_benefit_amount, priority_level, processing_days_min, processing_days_max) VALUES ('SNAP', 'Supplemental Nutrition Assistance Program', 'Basic Food', 'food', 'federal', 'https://www.washingtonconnection.org/WAPPLE/', 130, 26973, 'monthly', 23, 1756, 'apply_now', 7, 30), ('MEDICAID', 'Medicaid / Apple Health', 'Apple Health', 'health', 'federal', 'https://www.washingtonconnection.org/WAPPLE/', 138, NULL, 'monthly', 0, 0, 'apply_now', 1, 45), ('WIC', 'Women, Infants, and Children', 'WIC', 'food', 'federal', 'https://www.doh.wa.gov/YouandYourFamily/WIC', 185, NULL, 'monthly', 50, 500, 'apply_now', 3, 14), ('LIHEAP', 'Low Income Home Energy Assistance Program', 'LIHEAP', 'utilities','federal', 'https://www.commerce.wa.gov/growing-the-economy/energy/low-income-home-energy-assistance/', 150, NULL, 'annual', 200, 1200, 'high', 30, 60), ('CCAP', 'Child Care Assistance Program', 'CCAP', 'childcare','state', 'https://www.washingtonconnection.org/WAPPLE/', 200, NULL, 'monthly', 0, 2000, 'high', 14, 45); -- ============================================================ -- TABLE: program_eligibility_rules -- Structured rules for automated pre-screening -- ============================================================ CREATE TABLE program_eligibility_rules ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, program_id INT UNSIGNED NOT NULL, rule_field VARCHAR(100) NOT NULL COMMENT 'e.g. annual_income, household_size, has_children', operator ENUM('lt','lte','gt','gte','eq','between','in_list','not_null','is_true','is_false') NOT NULL, value_1 VARCHAR(255) NULL COMMENT 'Primary comparison value or JSON array for in_list', value_2 VARCHAR(255) NULL COMMENT 'Upper bound for between operator', is_disqualifier TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = failing this rule disqualifies', description VARCHAR(255) NULL, FOREIGN KEY fk_rule_program (program_id) REFERENCES benefits_programs(id) ON DELETE CASCADE, INDEX idx_program (program_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- SNAP eligibility rules example INSERT INTO program_eligibility_rules (program_id, rule_field, operator, value_1, description) VALUES (1, 'income_pct_fpl', 'lte', '130', 'Gross income must be at or below 130% FPL'), (1, 'household_size', 'gte', '1', 'Must have at least 1 household member'), (1, 'citizenship', 'eq', '"citizen"', 'Must be US citizen or qualified immigrant'); -- ============================================================ -- TABLE: program_documents -- Documents required per program -- ============================================================ CREATE TABLE program_documents ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, program_id INT UNSIGNED NOT NULL, document_slug VARCHAR(100) NOT NULL COMMENT 'e.g. photo_id, pay_stub, proof_of_address', document_name VARCHAR(255) NOT NULL, document_description TEXT NULL, accepted_types JSON NULL COMMENT 'Array of accepted document types', is_required TINYINT(1) NOT NULL DEFAULT 1, sample_pdf_url VARCHAR(500) NULL COMMENT 'URL to a sample filled form PDF', tips TEXT NULL, avoid_list JSON NULL COMMENT 'Array of things to avoid', sort_order TINYINT NOT NULL DEFAULT 0, FOREIGN KEY fk_doc_program (program_id) REFERENCES benefits_programs(id) ON DELETE CASCADE, INDEX idx_program (program_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO program_documents (program_id, document_slug, document_name, document_description, accepted_types, is_required, tips, sort_order) VALUES (1, 'photo_id', 'Photo ID', 'Government-issued photo ID', '["WA Driver License","State ID","Passport","Military ID"]', 1, 'Must not be expired', 1), (1, 'pay_stub', 'Recent pay stubs (last 30 days)', 'Proof of current income for all working household members', '["Pay stub","Bank statement","1099","Employer letter"]', 1, 'Must show gross income amount', 2), (1, 'proof_of_address','Proof of WA residency', 'Document showing your current Washington state address', '["Utility bill","Lease agreement","Bank statement","Official mail"]', 1, 'Must be within last 60 days', 3), (1, 'ssn', 'Social Security numbers', 'SSN for every person in your household', '["SSN card","W-2","Tax return","SSA letter","Medicare card"]', 1, 'The physical card is not required — just the number', 4); -- ============================================================ -- TABLE: eligibility_checks -- Each time a user runs an eligibility assessment -- ============================================================ CREATE TABLE eligibility_checks ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid VARCHAR(36) NOT NULL UNIQUE, user_id INT UNSIGNED NULL, session_token VARCHAR(100) NULL COMMENT 'For anonymous users', state CHAR(2) NOT NULL, zip_code VARCHAR(10) NULL, household_size TINYINT NOT NULL, annual_income DECIMAL(12,2) NOT NULL, monthly_income DECIMAL(10,2) GENERATED ALWAYS AS (annual_income / 12) STORED, employment_status ENUM('employed','unemployed','self_employed','retired','disabled','student') NULL, has_disability TINYINT(1) NOT NULL DEFAULT 0, has_children TINYINT(1) NOT NULL DEFAULT 0, youngest_child_age TINYINT NULL, pregnant TINYINT(1) NOT NULL DEFAULT 0, citizenship_status ENUM('citizen','permanent_resident','visa','undocumented','unknown') DEFAULT 'citizen', rent_monthly DECIMAL(10,2) NULL, utilities_monthly DECIMAL(10,2) NULL, income_pct_fpl DECIMAL(6,2) NULL COMMENT 'Calculated: (annual_income/fpl) * 100', raw_inputs_json JSON NULL COMMENT 'Full original intake data', gpt_prescreen_json JSON NULL COMMENT 'GPT-4o-mini pre-screen result', claude_analysis_json JSON NULL COMMENT 'Full Claude eligibility analysis', total_programs_found TINYINT NOT NULL DEFAULT 0, total_eligible_programs TINYINT NOT NULL DEFAULT 0, estimated_total_monthly DECIMAL(10,2) NULL, ai_summary TEXT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY fk_check_user (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_user (user_id), INDEX idx_state (state), INDEX idx_session (session_token), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: eligibility_results -- Individual program result per eligibility check -- ============================================================ CREATE TABLE eligibility_results ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, eligibility_check_id INT UNSIGNED NOT NULL, program_id INT UNSIGNED NOT NULL, is_eligible TINYINT(1) NOT NULL DEFAULT 0, confidence_score TINYINT NULL COMMENT '0-100 from AI', estimated_benefit_min DECIMAL(10,2) NULL, estimated_benefit_max DECIMAL(10,2) NULL, eligibility_reason TEXT NULL, disqualification_reason TEXT NULL, claude_explanation TEXT NULL, priority_rank TINYINT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY fk_result_check (eligibility_check_id) REFERENCES eligibility_checks(id) ON DELETE CASCADE, FOREIGN KEY fk_result_prog (program_id) REFERENCES benefits_programs(id), UNIQUE KEY uk_check_program (eligibility_check_id, program_id), INDEX idx_check (eligibility_check_id), INDEX idx_eligible (is_eligible) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: applications -- Track user's actual applications to programs -- ============================================================ CREATE TABLE applications ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid VARCHAR(36) NOT NULL UNIQUE, user_id INT UNSIGNED NULL, program_id INT UNSIGNED NOT NULL, eligibility_check_id INT UNSIGNED NULL, status ENUM( 'started', 'docs_gathering', 'submitted', 'pending_review', 'approved', 'denied', 'appealing', 'withdrawn' ) NOT NULL DEFAULT 'started', external_ref_number VARCHAR(100) NULL COMMENT 'Ref number from the official system', docs_ready_count TINYINT NOT NULL DEFAULT 0, docs_total_count TINYINT NOT NULL DEFAULT 0, submitted_at TIMESTAMP NULL, decision_at TIMESTAMP NULL, approved_amount DECIMAL(10,2) NULL, denial_reason TEXT NULL, appeal_filed_at TIMESTAMP NULL, benefit_start_date DATE NULL, notes TEXT NULL, ai_assistance_used TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY fk_app_user (user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY fk_app_program (program_id) REFERENCES benefits_programs(id), FOREIGN KEY fk_app_check (eligibility_check_id) REFERENCES eligibility_checks(id) ON DELETE SET NULL, INDEX idx_user (user_id), INDEX idx_status (status), INDEX idx_program (program_id), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: user_document_checklist -- Track which documents each user has gathered per application -- ============================================================ CREATE TABLE user_document_checklist ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, application_id INT UNSIGNED NOT NULL, program_document_id INT UNSIGNED NOT NULL, is_checked TINYINT(1) NOT NULL DEFAULT 0, checked_at TIMESTAMP NULL, uploaded_file_path VARCHAR(500) NULL, upload_status ENUM('none','pending','uploaded','verified','rejected') NOT NULL DEFAULT 'none', gpt_ocr_result JSON NULL COMMENT 'Extracted fields from document image via GPT-4o', notes VARCHAR(500) NULL, FOREIGN KEY fk_checklist_app (application_id) REFERENCES applications(id) ON DELETE CASCADE, FOREIGN KEY fk_checklist_doc (program_document_id) REFERENCES program_documents(id), UNIQUE KEY uk_app_doc (application_id, program_document_id), INDEX idx_application (application_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: chat_sessions -- One session per user-program conversation -- ============================================================ CREATE TABLE chat_sessions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid VARCHAR(36) NOT NULL UNIQUE, user_id INT UNSIGNED NULL, session_token VARCHAR(100) NULL COMMENT 'For anonymous users', program_id INT UNSIGNED NULL, application_id INT UNSIGNED NULL, ai_provider ENUM('claude','openai','both') NOT NULL DEFAULT 'openai', session_type ENUM('eligibility','guidance','form_help','document_help','general') NOT NULL DEFAULT 'general', context_json JSON NULL COMMENT 'User context snapshot (income, HH size, program, etc.)', message_count SMALLINT NOT NULL DEFAULT 0, started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_active_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY fk_session_user (user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY fk_session_program (program_id) REFERENCES benefits_programs(id) ON DELETE SET NULL, FOREIGN KEY fk_session_app (application_id) REFERENCES applications(id) ON DELETE SET NULL, INDEX idx_user (user_id), INDEX idx_session_token (session_token), INDEX idx_provider (ai_provider) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: chat_messages -- Individual messages in a chat session -- ============================================================ CREATE TABLE chat_messages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id INT UNSIGNED NOT NULL, role ENUM('user','assistant','system') NOT NULL, content TEXT NOT NULL, ai_provider ENUM('claude','openai') NULL, model_used VARCHAR(50) NULL COMMENT 'e.g. claude-sonnet-4-6, gpt-4o-mini', prompt_tokens SMALLINT NULL, completion_tokens SMALLINT NULL, response_ms SMALLINT NULL COMMENT 'API response time in milliseconds', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY fk_msg_session (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE, INDEX idx_session (session_id), INDEX idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: ai_api_logs -- Cost tracking and monitoring for both AIs -- ============================================================ CREATE TABLE ai_api_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, provider ENUM('claude','openai') NOT NULL, endpoint VARCHAR(100) NULL COMMENT 'e.g. eligibility, chat, ocr, translation', model VARCHAR(50) NULL, user_id INT UNSIGNED NULL, session_id INT UNSIGNED NULL, prompt_tokens INT NULL, completion_tokens INT NULL, total_tokens INT GENERATED ALWAYS AS (IFNULL(prompt_tokens,0) + IFNULL(completion_tokens,0)) STORED, cost_usd DECIMAL(10,6) NULL, response_ms SMALLINT NULL, http_status SMALLINT NULL, error_message TEXT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_provider (provider), INDEX idx_user (user_id), INDEX idx_created (created_at), INDEX idx_endpoint (endpoint) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: notifications -- In-app + email + SMS notification queue -- ============================================================ CREATE TABLE notifications ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, application_id INT UNSIGNED NULL, type ENUM( 'status_update','document_required','deadline', 'approved','denied','renewal_due','welcome' ) NOT NULL, channel ENUM('in_app','email','sms') NOT NULL DEFAULT 'in_app', title VARCHAR(255) NOT NULL, body TEXT NOT NULL, action_url VARCHAR(500) NULL, is_read TINYINT(1) NOT NULL DEFAULT 0, sent_at TIMESTAMP NULL, read_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY fk_notif_user (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY fk_notif_app (application_id) REFERENCES applications(id) ON DELETE SET NULL, INDEX idx_user (user_id), INDEX idx_unread (user_id, is_read), INDEX idx_type (type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: translations_cache -- Cache translated content to avoid re-billing translation API -- ============================================================ CREATE TABLE translations_cache ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, source_hash CHAR(64) NOT NULL COMMENT 'SHA-256 of original text', language_code VARCHAR(10) NOT NULL, original_text TEXT NOT NULL, translated_text TEXT NOT NULL, model_used VARCHAR(50) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_hash_lang (source_hash, language_code), INDEX idx_lang (language_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ============================================================ -- TABLE: app_settings -- Runtime configuration without code deploys -- ============================================================ CREATE TABLE app_settings ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(100) NOT NULL UNIQUE, setting_value TEXT NULL, setting_type ENUM('string','integer','boolean','json') NOT NULL DEFAULT 'string', description TEXT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO app_settings (setting_key, setting_value, setting_type, description) VALUES ('claude_enabled', '1', 'boolean', 'Enable Claude API calls'), ('openai_enabled', '1', 'boolean', 'Enable OpenAI API calls'), ('max_chat_messages', '50', 'integer', 'Max messages per chat session'), ('ocr_enabled', '1', 'boolean', 'Allow document image scanning'), ('translation_enabled', '1', 'boolean', 'Show language switcher'), ('supported_languages', '["en","es","zh","ar","tl","vi"]', 'json', 'Available UI languages'), ('fpl_year', '2025', 'integer', 'Which FPL year to use for calculations'), ('session_expiry_days', '30', 'integer', 'Days before anonymous session expires'); -- ============================================================ -- VIEWS -- ============================================================ -- Active programs sorted by category CREATE VIEW v_active_programs AS SELECT bp.id, bp.program_code, bp.name, bp.category, bp.program_type, bp.income_limit_pct_fpl, bp.max_benefit_amount, bp.benefit_period, bp.priority_level, bp.apply_url, bp.processing_days_min, bp.processing_days_max, fpl.annual_amount AS fpl_annual_2024, ROUND(fpl.annual_amount * bp.income_limit_pct_fpl / 100, 2) AS income_limit_2person FROM benefits_programs bp LEFT JOIN federal_poverty_levels fpl ON fpl.year = 2025 AND fpl.household_size = 2 WHERE bp.is_active = 1 ORDER BY FIELD(bp.priority_level,'apply_now','high','medium','low'), bp.category; -- Application status summary per user CREATE VIEW v_user_application_summary AS SELECT u.id AS user_id, u.uuid, u.email, COUNT(a.id) AS total_applications, SUM(a.status = 'approved') AS approved_count, SUM(a.status = 'pending_review') AS pending_count, SUM(a.status = 'denied') AS denied_count, COALESCE(SUM(CASE WHEN a.status = 'approved' THEN a.approved_amount END), 0) AS total_monthly_approved FROM users u LEFT JOIN applications a ON a.user_id = u.id GROUP BY u.id; -- AI cost summary by day CREATE VIEW v_ai_cost_daily AS SELECT DATE(created_at) AS log_date, provider, endpoint, COUNT(*) AS call_count, SUM(total_tokens) AS total_tokens, SUM(cost_usd) AS total_cost_usd FROM ai_api_logs WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY DATE(created_at), provider, endpoint ORDER BY log_date DESC, total_cost_usd DESC; -- ============================================================ -- STORED PROCEDURES -- ============================================================ DELIMITER $$ -- Get eligible programs for a household (automated pre-filter) CREATE PROCEDURE sp_get_eligible_programs( IN p_household_size TINYINT, IN p_annual_income DECIMAL(12,2), IN p_state CHAR(2), IN p_has_children TINYINT(1), IN p_has_disability TINYINT(1) ) BEGIN DECLARE v_fpl_annual DECIMAL(10,2); DECLARE v_income_pct DECIMAL(6,2); -- Get this year's FPL for this household size SELECT annual_amount INTO v_fpl_annual FROM federal_poverty_levels WHERE year = 2025 AND household_size = LEAST(p_household_size, 8) LIMIT 1; SET v_income_pct = IF(v_fpl_annual > 0, (p_annual_income / v_fpl_annual) * 100, 999); SELECT bp.program_code, bp.name, bp.category, bp.max_benefit_amount, bp.apply_url, bp.priority_level, v_income_pct AS calculated_income_pct_fpl, CASE WHEN v_income_pct <= bp.income_limit_pct_fpl THEN 'likely_eligible' WHEN v_income_pct <= bp.income_limit_pct_fpl * 1.1 THEN 'borderline' ELSE 'likely_ineligible' END AS pre_screen_status FROM benefits_programs bp WHERE bp.is_active = 1 AND (bp.state IS NULL OR bp.state = p_state) AND (bp.income_limit_pct_fpl IS NULL OR v_income_pct <= bp.income_limit_pct_fpl * 1.15) ORDER BY FIELD(bp.priority_level,'apply_now','high','medium','low'); END$$ DELIMITER ;