-- schema-validation-v2.sql -- Enhanced Database Schema Validation with 100% Coverage -- Citizen Intelligence Agency - Open Source Intelligence Platform -- Generated for PostgreSQL 16 -- -- Purpose: Validates ALL 177 database objects (93 tables + 56 views + 28 materialized views) -- defined in full_schema.sql with comprehensive coverage reporting -- -- Issue: Hack23/cia#7872 - Extend Schema Validation Script -- Related: Hack23/cia#7865 - Schema validation script foundation -- -- *** MAINTENANCE NOTE *** -- The object arrays in this script (tables, views, materialized views) must be kept -- in sync with full_schema.sql. When the schema changes: -- 1. Update the arrays in this script (lines ~56, ~220, ~335) -- 2. Update the arrays in schema-coverage-analysis.sql -- 3. Update the object counts in README-SCHEMA-MAINTENANCE.md -- 4. Run this script to verify 100% coverage -- Last synchronized: 2025-11-24 -- -- Usage: -- psql -U postgres -d cia_dev -f schema-validation-v2.sql > schema_validation_report.txt 2>&1 -- -- Features: -- - Validates ALL 93 base tables from full_schema.sql -- - Validates ALL 56 regular views from full_schema.sql -- - Validates ALL 28 materialized views from full_schema.sql -- - Generates coverage report showing 100% validation -- - Compares full_schema.sql objects vs actual database -- - Identifies missing or extra objects -- - Provides detailed statistics and health metrics \set ECHO queries \timing on -- =========================================================================== -- PART 1: VALIDATION METADATA -- =========================================================================== \echo '' \echo '==========================================================================' \echo '=== SCHEMA VALIDATION V2.0 - 100% COVERAGE ===' \echo '==========================================================================' \echo '' \echo 'Issue: Hack23/cia#7872 - Extend Schema Validation Script' \echo 'Purpose: Validate ALL 177 objects from full_schema.sql' \echo 'Target Coverage: 93 tables + 56 views + 28 materialized views = 177 objects' \echo '' \echo 'Validation started at:' SELECT NOW(); \echo '' -- =========================================================================== -- PART 2: COMPREHENSIVE TABLE VALIDATION (ALL 93 TABLES) -- =========================================================================== \echo '' \echo '==========================================================================' \echo '=== COMPREHENSIVE TABLE VALIDATION (93 TABLES) ===' \echo '==========================================================================' \echo '' DO $$ DECLARE -- *** MAINTENANCE NOTE *** -- This array must be kept in sync with full_schema.sql -- Update this list when tables are added/removed from the schema -- Last verified: 2025-11-24 against full_schema.sql -- Total expected: 93 tables v_all_tables TEXT[] := ARRAY[ 'against_proposal_container', 'against_proposal_data', 'agency', 'application_action_event', 'application_configuration', 'application_session', 'application_view', 'assignment_data', 'assignment_element', 'committee_document_data', 'committee_proposal_component_0', 'committee_proposal_container', 'committee_proposal_data', 'countries_element', 'country_element', 'data_element', 'data_source_content', 'databasechangelog', 'databasechangeloglock', 'detail_data', 'detail_element', 'document_activity_container', 'document_activity_data', 'document_attachment', 'document_attachment_container', 'document_container_element', 'document_content_data', 'document_data', 'document_detail_container', 'document_detail_data', 'document_element', 'document_person_reference_co_0', 'document_person_reference_da_0', 'document_proposal_container', 'document_proposal_data', 'document_reference_container', 'document_reference_data', 'document_status_container', 'domain_portal', 'encrypted_value', 'indicator_element', 'indicators_element', 'jv_commit', 'jv_commit_property', 'jv_global_id', 'jv_snapshot', 'language_content_data', 'language_data', 'operational_information_cont_0', 'performance_indicator_content', 'person_assignment_data', 'person_assignment_element', 'person_container_data', 'person_container_element', 'person_data', 'person_detail_data', 'person_detail_element', 'person_element', 'portal', 'qrtz_blob_triggers', 'qrtz_calendars', 'qrtz_cron_triggers', 'qrtz_fired_triggers', 'qrtz_job_details', 'qrtz_locks', 'qrtz_paused_trigger_grps', 'qrtz_scheduler_state', 'qrtz_simple_triggers', 'qrtz_simprop_triggers', 'qrtz_triggers', 'quality_assurance_content', 'rule_violation', 'sweden_county_data', 'sweden_county_data_container', 'sweden_county_electoral_area', 'sweden_county_electoral_regi_0', 'sweden_county_electoral_regi_1', 'sweden_election_region', 'sweden_election_type', 'sweden_election_type_contain_0', 'sweden_municipality_data', 'sweden_municipality_election_0', 'sweden_parliament_electoral__0', 'sweden_parliament_electoral__1', 'sweden_political_party', 'target_profile_content', 'topic', 'topics', 'user_account', 'user_account_address', 'vote_data', 'vote_meta_data', 'world_bank_data' ]; v_table TEXT; v_count INTEGER; v_col_count INTEGER; v_total_validated INTEGER := 0; v_total_errors INTEGER := 0; v_total_expected INTEGER; BEGIN v_total_expected := array_length(v_all_tables, 1); RAISE NOTICE 'Validating ALL % tables from full_schema.sql', v_total_expected; RAISE NOTICE '================================================================'; RAISE NOTICE ''; FOREACH v_table IN ARRAY v_all_tables LOOP BEGIN -- Get row count EXECUTE format('SELECT COUNT(*) FROM %I', v_table) INTO v_count; -- Get column count SELECT COUNT(*) INTO v_col_count FROM information_schema.columns WHERE table_schema = 'public' AND table_name = v_table; RAISE NOTICE '✓ Table: % | Rows: % | Columns: %', rpad(v_table, 40), lpad(v_count::text, 10), lpad(v_col_count::text, 3); v_total_validated := v_total_validated + 1; EXCEPTION WHEN OTHERS THEN RAISE WARNING '✗ Table: % | ERROR: %', v_table, SQLERRM; v_total_errors := v_total_errors + 1; END; END LOOP; RAISE NOTICE ''; RAISE NOTICE '================================================================'; RAISE NOTICE 'TABLE VALIDATION SUMMARY:'; RAISE NOTICE ' Expected: % tables', v_total_expected; RAISE NOTICE ' Validated: % tables (%.2f%%)', v_total_validated, (v_total_validated::float / v_total_expected * 100); RAISE NOTICE ' Errors: % tables', v_total_errors; RAISE NOTICE '================================================================'; RAISE NOTICE ''; END $$; -- =========================================================================== -- PART 3: COMPREHENSIVE REGULAR VIEW VALIDATION (ALL 56 VIEWS) -- =========================================================================== \echo '' \echo '==========================================================================' \echo '=== COMPREHENSIVE REGULAR VIEW VALIDATION (56 VIEWS) ===' \echo '==========================================================================' \echo '' DO $$ DECLARE -- *** MAINTENANCE NOTE *** -- This array must be kept in sync with full_schema.sql -- Update this list when regular views are added/removed from the schema -- Last verified: 2025-11-24 against full_schema.sql -- Total expected: 56 regular views v_all_views TEXT[] := ARRAY[ 'view_application_action_event_page_annual_summary', 'view_application_action_event_page_daily_summary', 'view_application_action_event_page_element_annual_summary', 'view_application_action_event_page_element_daily_summary', 'view_application_action_event_page_element_hourly_summary', 'view_application_action_event_page_element_weekly_summary', 'view_application_action_event_page_hourly_summary', 'view_application_action_event_page_modes_annual_summary', 'view_application_action_event_page_modes_daily_summary', 'view_application_action_event_page_modes_hourly_summary', 'view_application_action_event_page_modes_weekly_summary', 'view_application_action_event_page_weekly_summary', 'view_audit_author_summary', 'view_audit_data_summary', 'view_committee_productivity', 'view_committee_productivity_matrix', 'view_decision_temporal_trends', 'view_document_data_committee_report_url', 'view_ministry_decision_impact', 'view_ministry_effectiveness_trends', 'view_ministry_productivity_matrix', 'view_ministry_risk_evolution', 'view_party_effectiveness_trends', 'view_party_performance_metrics', 'view_politician_behavioral_trends', 'view_politician_risk_summary', 'view_riksdagen_coalition_alignment_matrix', 'view_riksdagen_committee', 'view_riksdagen_committee_parliament_member_proposal', 'view_riksdagen_committee_role_member', 'view_riksdagen_committee_roles', 'view_riksdagen_crisis_resilience_indicators', 'view_riksdagen_goverment', 'view_riksdagen_goverment_proposals', 'view_riksdagen_goverment_role_member', 'view_riksdagen_goverment_roles', 'view_riksdagen_intelligence_dashboard', 'view_riksdagen_member_proposals', 'view_riksdagen_party', 'view_riksdagen_party_ballot_support_annual_summary', 'view_riksdagen_party_coalation_against_annual_summary', 'view_riksdagen_party_decision_flow', 'view_riksdagen_party_document_summary', 'view_riksdagen_party_member', 'view_riksdagen_party_momentum_analysis', 'view_riksdagen_party_role_member', 'view_riksdagen_party_signatures_document_summary', 'view_riksdagen_party_summary', 'view_riksdagen_person_signed_document_summary', 'view_riksdagen_politician', 'view_riksdagen_politician_ballot_summary', 'view_riksdagen_politician_decision_pattern', 'view_riksdagen_politician_experience_summary', 'view_riksdagen_politician_influence_metrics', 'view_riksdagen_voting_anomaly_detection', 'view_risk_score_evolution' ]; v_view TEXT; v_count INTEGER; v_col_count INTEGER; v_total_validated INTEGER := 0; v_total_errors INTEGER := 0; v_total_expected INTEGER; BEGIN v_total_expected := array_length(v_all_views, 1); RAISE NOTICE 'Validating ALL % regular views from full_schema.sql', v_total_expected; RAISE NOTICE '================================================================'; RAISE NOTICE ''; FOREACH v_view IN ARRAY v_all_views LOOP BEGIN -- Get row count EXECUTE format('SELECT COUNT(*) FROM %I', v_view) INTO v_count; -- Get column count SELECT COUNT(*) INTO v_col_count FROM information_schema.columns WHERE table_schema = 'public' AND table_name = v_view; RAISE NOTICE '✓ View: % | Rows: % | Columns: %', rpad(v_view, 60), lpad(v_count::text, 10), lpad(v_col_count::text, 3); v_total_validated := v_total_validated + 1; EXCEPTION WHEN OTHERS THEN RAISE WARNING '✗ View: % | ERROR: %', v_view, SQLERRM; v_total_errors := v_total_errors + 1; END; END LOOP; RAISE NOTICE ''; RAISE NOTICE '================================================================'; RAISE NOTICE 'REGULAR VIEW VALIDATION SUMMARY:'; RAISE NOTICE ' Expected: % views', v_total_expected; RAISE NOTICE ' Validated: % views (%.2f%%)', v_total_validated, (v_total_validated::float / v_total_expected * 100); RAISE NOTICE ' Errors: % views', v_total_errors; RAISE NOTICE '================================================================'; RAISE NOTICE ''; END $$; -- =========================================================================== -- PART 4: COMPREHENSIVE MATERIALIZED VIEW VALIDATION (ALL 28 MVIEWS) -- =========================================================================== \echo '' \echo '==========================================================================' \echo '=== COMPREHENSIVE MATERIALIZED VIEW VALIDATION (28 MVIEWS) ===' \echo '==========================================================================' \echo '' DO $$ DECLARE -- *** MAINTENANCE NOTE *** -- This array must be kept in sync with full_schema.sql -- Update this list when materialized views are added/removed from the schema -- Last verified: 2025-11-24 against full_schema.sql -- Total expected: 28 materialized views v_all_mviews TEXT[] := ARRAY[ 'view_riksdagen_committee_ballot_decision_party_summary', 'view_riksdagen_committee_ballot_decision_politician_summary', 'view_riksdagen_committee_ballot_decision_summary', 'view_riksdagen_committee_decision_type_org_summary', 'view_riksdagen_committee_decision_type_summary', 'view_riksdagen_committee_decisions', 'view_riksdagen_document_type_daily_summary', 'view_riksdagen_org_document_daily_summary', 'view_riksdagen_party_document_daily_summary', 'view_riksdagen_politician_document', 'view_riksdagen_politician_document_daily_summary', 'view_riksdagen_politician_document_summary', 'view_riksdagen_vote_data_ballot_party_summary', 'view_riksdagen_vote_data_ballot_party_summary_annual', 'view_riksdagen_vote_data_ballot_party_summary_daily', 'view_riksdagen_vote_data_ballot_party_summary_monthly', 'view_riksdagen_vote_data_ballot_party_summary_weekly', 'view_riksdagen_vote_data_ballot_politician_summary', 'view_riksdagen_vote_data_ballot_politician_summary_annual', 'view_riksdagen_vote_data_ballot_politician_summary_daily', 'view_riksdagen_vote_data_ballot_politician_summary_monthly', 'view_riksdagen_vote_data_ballot_politician_summary_weekly', 'view_riksdagen_vote_data_ballot_summary', 'view_riksdagen_vote_data_ballot_summary_annual', 'view_riksdagen_vote_data_ballot_summary_daily', 'view_riksdagen_vote_data_ballot_summary_monthly', 'view_riksdagen_vote_data_ballot_summary_weekly', 'view_worldbank_indicator_data_country_summary' ]; v_mview TEXT; v_count INTEGER; v_col_count INTEGER; v_total_validated INTEGER := 0; v_total_errors INTEGER := 0; v_total_expected INTEGER; BEGIN v_total_expected := array_length(v_all_mviews, 1); RAISE NOTICE 'Validating ALL % materialized views from full_schema.sql', v_total_expected; RAISE NOTICE '================================================================'; RAISE NOTICE ''; FOREACH v_mview IN ARRAY v_all_mviews LOOP BEGIN -- Get row count EXECUTE format('SELECT COUNT(*) FROM %I', v_mview) INTO v_count; -- Get column count SELECT COUNT(*) INTO v_col_count FROM information_schema.columns WHERE table_schema = 'public' AND table_name = v_mview; RAISE NOTICE '✓ MView: % | Rows: % | Columns: %', rpad(v_mview, 60), lpad(v_count::text, 10), lpad(v_col_count::text, 3); v_total_validated := v_total_validated + 1; EXCEPTION WHEN OTHERS THEN RAISE WARNING '✗ MView: % | ERROR: %', v_mview, SQLERRM; v_total_errors := v_total_errors + 1; END; END LOOP; RAISE NOTICE ''; RAISE NOTICE '================================================================'; RAISE NOTICE 'MATERIALIZED VIEW VALIDATION SUMMARY:'; RAISE NOTICE ' Expected: % mviews', v_total_expected; RAISE NOTICE ' Validated: % mviews (%.2f%%)', v_total_validated, (v_total_validated::float / v_total_expected * 100); RAISE NOTICE ' Errors: % mviews', v_total_errors; RAISE NOTICE '================================================================'; RAISE NOTICE ''; END $$; -- =========================================================================== -- PART 5: COVERAGE REPORT - FULL_SCHEMA.SQL VS ACTUAL DATABASE -- =========================================================================== \echo '' \echo '==========================================================================' \echo '=== COVERAGE REPORT: FULL_SCHEMA.SQL VS ACTUAL DATABASE ===' \echo '==========================================================================' \echo '' -- Report: Tables in full_schema.sql vs database \echo '--- TABLES: Checking full_schema.sql (93) vs actual database ---' WITH expected_tables AS ( SELECT unnest(ARRAY[ 'against_proposal_container', 'against_proposal_data', 'agency', 'application_action_event', 'application_configuration', 'application_session', 'application_view', 'assignment_data', 'assignment_element', 'committee_document_data', 'committee_proposal_component_0', 'committee_proposal_container', 'committee_proposal_data', 'countries_element', 'country_element', 'data_element', 'data_source_content', 'databasechangelog', 'databasechangeloglock', 'detail_data', 'detail_element', 'document_activity_container', 'document_activity_data', 'document_attachment', 'document_attachment_container', 'document_container_element', 'document_content_data', 'document_data', 'document_detail_container', 'document_detail_data', 'document_element', 'document_person_reference_co_0', 'document_person_reference_da_0', 'document_proposal_container', 'document_proposal_data', 'document_reference_container', 'document_reference_data', 'document_status_container', 'domain_portal', 'encrypted_value', 'indicator_element', 'indicators_element', 'jv_commit', 'jv_commit_property', 'jv_global_id', 'jv_snapshot', 'language_content_data', 'language_data', 'operational_information_cont_0', 'performance_indicator_content', 'person_assignment_data', 'person_assignment_element', 'person_container_data', 'person_container_element', 'person_data', 'person_detail_data', 'person_detail_element', 'person_element', 'portal', 'qrtz_blob_triggers', 'qrtz_calendars', 'qrtz_cron_triggers', 'qrtz_fired_triggers', 'qrtz_job_details', 'qrtz_locks', 'qrtz_paused_trigger_grps', 'qrtz_scheduler_state', 'qrtz_simple_triggers', 'qrtz_simprop_triggers', 'qrtz_triggers', 'quality_assurance_content', 'rule_violation', 'sweden_county_data', 'sweden_county_data_container', 'sweden_county_electoral_area', 'sweden_county_electoral_regi_0', 'sweden_county_electoral_regi_1', 'sweden_election_region', 'sweden_election_type', 'sweden_election_type_contain_0', 'sweden_municipality_data', 'sweden_municipality_election_0', 'sweden_parliament_electoral__0', 'sweden_parliament_electoral__1', 'sweden_political_party', 'target_profile_content', 'topic', 'topics', 'user_account', 'user_account_address', 'vote_data', 'vote_meta_data', 'world_bank_data' ]) AS table_name ), actual_tables AS ( SELECT tablename AS table_name FROM pg_tables WHERE schemaname = 'public' ) SELECT COALESCE(e.table_name, a.table_name) AS table_name, CASE WHEN e.table_name IS NULL THEN 'EXTRA (in DB, not in schema)' WHEN a.table_name IS NULL THEN 'MISSING (in schema, not in DB)' ELSE 'OK' END AS status FROM expected_tables e FULL OUTER JOIN actual_tables a ON e.table_name = a.table_name WHERE e.table_name IS NULL OR a.table_name IS NULL ORDER BY status, table_name; -- Count coverage WITH expected_tables AS ( SELECT 93 AS expected_count ), actual_match AS ( SELECT COUNT(*) AS matched_count FROM pg_tables WHERE schemaname = 'public' AND tablename IN ( 'against_proposal_container', 'against_proposal_data', 'agency', 'application_action_event', 'application_configuration', 'application_session', 'application_view', 'assignment_data', 'assignment_element', 'committee_document_data', 'committee_proposal_component_0', 'committee_proposal_container', 'committee_proposal_data', 'countries_element', 'country_element', 'data_element', 'data_source_content', 'databasechangelog', 'databasechangeloglock', 'detail_data', 'detail_element', 'document_activity_container', 'document_activity_data', 'document_attachment', 'document_attachment_container', 'document_container_element', 'document_content_data', 'document_data', 'document_detail_container', 'document_detail_data', 'document_element', 'document_person_reference_co_0', 'document_person_reference_da_0', 'document_proposal_container', 'document_proposal_data', 'document_reference_container', 'document_reference_data', 'document_status_container', 'domain_portal', 'encrypted_value', 'indicator_element', 'indicators_element', 'jv_commit', 'jv_commit_property', 'jv_global_id', 'jv_snapshot', 'language_content_data', 'language_data', 'operational_information_cont_0', 'performance_indicator_content', 'person_assignment_data', 'person_assignment_element', 'person_container_data', 'person_container_element', 'person_data', 'person_detail_data', 'person_detail_element', 'person_element', 'portal', 'qrtz_blob_triggers', 'qrtz_calendars', 'qrtz_cron_triggers', 'qrtz_fired_triggers', 'qrtz_job_details', 'qrtz_locks', 'qrtz_paused_trigger_grps', 'qrtz_scheduler_state', 'qrtz_simple_triggers', 'qrtz_simprop_triggers', 'qrtz_triggers', 'quality_assurance_content', 'rule_violation', 'sweden_county_data', 'sweden_county_data_container', 'sweden_county_electoral_area', 'sweden_county_electoral_regi_0', 'sweden_county_electoral_regi_1', 'sweden_election_region', 'sweden_election_type', 'sweden_election_type_contain_0', 'sweden_municipality_data', 'sweden_municipality_election_0', 'sweden_parliament_electoral__0', 'sweden_parliament_electoral__1', 'sweden_political_party', 'target_profile_content', 'topic', 'topics', 'user_account', 'user_account_address', 'vote_data', 'vote_meta_data', 'world_bank_data' ) ) SELECT 'TABLES' AS object_type, expected_count AS expected, matched_count AS found_in_db, ROUND(matched_count::numeric / expected_count * 100, 2) AS coverage_pct FROM expected_tables, actual_match; \echo '' \echo '--- REGULAR VIEWS: Checking full_schema.sql (56) vs actual database ---' WITH expected_views AS ( SELECT unnest(ARRAY[ 'view_application_action_event_page_annual_summary', 'view_application_action_event_page_daily_summary', 'view_application_action_event_page_element_annual_summary', 'view_application_action_event_page_element_daily_summary', 'view_application_action_event_page_element_hourly_summary', 'view_application_action_event_page_element_weekly_summary', 'view_application_action_event_page_hourly_summary', 'view_application_action_event_page_modes_annual_summary', 'view_application_action_event_page_modes_daily_summary', 'view_application_action_event_page_modes_hourly_summary', 'view_application_action_event_page_modes_weekly_summary', 'view_application_action_event_page_weekly_summary', 'view_audit_author_summary', 'view_audit_data_summary', 'view_committee_productivity', 'view_committee_productivity_matrix', 'view_decision_temporal_trends', 'view_document_data_committee_report_url', 'view_ministry_decision_impact', 'view_ministry_effectiveness_trends', 'view_ministry_productivity_matrix', 'view_ministry_risk_evolution', 'view_party_effectiveness_trends', 'view_party_performance_metrics', 'view_politician_behavioral_trends', 'view_politician_risk_summary', 'view_riksdagen_coalition_alignment_matrix', 'view_riksdagen_committee', 'view_riksdagen_committee_parliament_member_proposal', 'view_riksdagen_committee_role_member', 'view_riksdagen_committee_roles', 'view_riksdagen_crisis_resilience_indicators', 'view_riksdagen_goverment', 'view_riksdagen_goverment_proposals', 'view_riksdagen_goverment_role_member', 'view_riksdagen_goverment_roles', 'view_riksdagen_intelligence_dashboard', 'view_riksdagen_member_proposals', 'view_riksdagen_party', 'view_riksdagen_party_ballot_support_annual_summary', 'view_riksdagen_party_coalation_against_annual_summary', 'view_riksdagen_party_decision_flow', 'view_riksdagen_party_document_summary', 'view_riksdagen_party_member', 'view_riksdagen_party_momentum_analysis', 'view_riksdagen_party_role_member', 'view_riksdagen_party_signatures_document_summary', 'view_riksdagen_party_summary', 'view_riksdagen_person_signed_document_summary', 'view_riksdagen_politician', 'view_riksdagen_politician_ballot_summary', 'view_riksdagen_politician_decision_pattern', 'view_riksdagen_politician_experience_summary', 'view_riksdagen_politician_influence_metrics', 'view_riksdagen_voting_anomaly_detection', 'view_risk_score_evolution' ]) AS view_name ), actual_views AS ( SELECT viewname AS view_name FROM pg_views WHERE schemaname = 'public' ) SELECT COALESCE(e.view_name, a.view_name) AS view_name, CASE WHEN e.view_name IS NULL THEN 'EXTRA (in DB, not in schema)' WHEN a.view_name IS NULL THEN 'MISSING (in schema, not in DB)' ELSE 'OK' END AS status FROM expected_views e FULL OUTER JOIN actual_views a ON e.view_name = a.view_name WHERE e.view_name IS NULL OR a.view_name IS NULL ORDER BY status, view_name; -- Count coverage WITH expected_views AS ( SELECT 56 AS expected_count ), actual_match AS ( SELECT COUNT(*) AS matched_count FROM pg_views WHERE schemaname = 'public' AND viewname IN ( 'view_application_action_event_page_annual_summary', 'view_application_action_event_page_daily_summary', 'view_application_action_event_page_element_annual_summary', 'view_application_action_event_page_element_daily_summary', 'view_application_action_event_page_element_hourly_summary', 'view_application_action_event_page_element_weekly_summary', 'view_application_action_event_page_hourly_summary', 'view_application_action_event_page_modes_annual_summary', 'view_application_action_event_page_modes_daily_summary', 'view_application_action_event_page_modes_hourly_summary', 'view_application_action_event_page_modes_weekly_summary', 'view_application_action_event_page_weekly_summary', 'view_audit_author_summary', 'view_audit_data_summary', 'view_committee_productivity', 'view_committee_productivity_matrix', 'view_decision_temporal_trends', 'view_document_data_committee_report_url', 'view_ministry_decision_impact', 'view_ministry_effectiveness_trends', 'view_ministry_productivity_matrix', 'view_ministry_risk_evolution', 'view_party_effectiveness_trends', 'view_party_performance_metrics', 'view_politician_behavioral_trends', 'view_politician_risk_summary', 'view_riksdagen_coalition_alignment_matrix', 'view_riksdagen_committee', 'view_riksdagen_committee_parliament_member_proposal', 'view_riksdagen_committee_role_member', 'view_riksdagen_committee_roles', 'view_riksdagen_crisis_resilience_indicators', 'view_riksdagen_goverment', 'view_riksdagen_goverment_proposals', 'view_riksdagen_goverment_role_member', 'view_riksdagen_goverment_roles', 'view_riksdagen_intelligence_dashboard', 'view_riksdagen_member_proposals', 'view_riksdagen_party', 'view_riksdagen_party_ballot_support_annual_summary', 'view_riksdagen_party_coalation_against_annual_summary', 'view_riksdagen_party_decision_flow', 'view_riksdagen_party_document_summary', 'view_riksdagen_party_member', 'view_riksdagen_party_momentum_analysis', 'view_riksdagen_party_role_member', 'view_riksdagen_party_signatures_document_summary', 'view_riksdagen_party_summary', 'view_riksdagen_person_signed_document_summary', 'view_riksdagen_politician', 'view_riksdagen_politician_ballot_summary', 'view_riksdagen_politician_decision_pattern', 'view_riksdagen_politician_experience_summary', 'view_riksdagen_politician_influence_metrics', 'view_riksdagen_voting_anomaly_detection', 'view_risk_score_evolution' ) ) SELECT 'VIEWS' AS object_type, expected_count AS expected, matched_count AS found_in_db, ROUND(matched_count::numeric / expected_count * 100, 2) AS coverage_pct FROM expected_views, actual_match; \echo '' \echo '--- MATERIALIZED VIEWS: Checking full_schema.sql (28) vs actual database ---' WITH expected_mviews AS ( SELECT unnest(ARRAY[ 'view_riksdagen_committee_ballot_decision_party_summary', 'view_riksdagen_committee_ballot_decision_politician_summary', 'view_riksdagen_committee_ballot_decision_summary', 'view_riksdagen_committee_decision_type_org_summary', 'view_riksdagen_committee_decision_type_summary', 'view_riksdagen_committee_decisions', 'view_riksdagen_document_type_daily_summary', 'view_riksdagen_org_document_daily_summary', 'view_riksdagen_party_document_daily_summary', 'view_riksdagen_politician_document', 'view_riksdagen_politician_document_daily_summary', 'view_riksdagen_politician_document_summary', 'view_riksdagen_vote_data_ballot_party_summary', 'view_riksdagen_vote_data_ballot_party_summary_annual', 'view_riksdagen_vote_data_ballot_party_summary_daily', 'view_riksdagen_vote_data_ballot_party_summary_monthly', 'view_riksdagen_vote_data_ballot_party_summary_weekly', 'view_riksdagen_vote_data_ballot_politician_summary', 'view_riksdagen_vote_data_ballot_politician_summary_annual', 'view_riksdagen_vote_data_ballot_politician_summary_daily', 'view_riksdagen_vote_data_ballot_politician_summary_monthly', 'view_riksdagen_vote_data_ballot_politician_summary_weekly', 'view_riksdagen_vote_data_ballot_summary', 'view_riksdagen_vote_data_ballot_summary_annual', 'view_riksdagen_vote_data_ballot_summary_daily', 'view_riksdagen_vote_data_ballot_summary_monthly', 'view_riksdagen_vote_data_ballot_summary_weekly', 'view_worldbank_indicator_data_country_summary' ]) AS mview_name ), actual_mviews AS ( SELECT matviewname AS mview_name FROM pg_matviews WHERE schemaname = 'public' ) SELECT COALESCE(e.mview_name, a.mview_name) AS mview_name, CASE WHEN e.mview_name IS NULL THEN 'EXTRA (in DB, not in schema)' WHEN a.mview_name IS NULL THEN 'MISSING (in schema, not in DB)' ELSE 'OK' END AS status FROM expected_mviews e FULL OUTER JOIN actual_mviews a ON e.mview_name = a.mview_name WHERE e.mview_name IS NULL OR a.mview_name IS NULL ORDER BY status, mview_name; -- Count coverage WITH expected_mviews AS ( SELECT 28 AS expected_count ), actual_match AS ( SELECT COUNT(*) AS matched_count FROM pg_matviews WHERE schemaname = 'public' AND matviewname IN ( 'view_riksdagen_committee_ballot_decision_party_summary', 'view_riksdagen_committee_ballot_decision_politician_summary', 'view_riksdagen_committee_ballot_decision_summary', 'view_riksdagen_committee_decision_type_org_summary', 'view_riksdagen_committee_decision_type_summary', 'view_riksdagen_committee_decisions', 'view_riksdagen_document_type_daily_summary', 'view_riksdagen_org_document_daily_summary', 'view_riksdagen_party_document_daily_summary', 'view_riksdagen_politician_document', 'view_riksdagen_politician_document_daily_summary', 'view_riksdagen_politician_document_summary', 'view_riksdagen_vote_data_ballot_party_summary', 'view_riksdagen_vote_data_ballot_party_summary_annual', 'view_riksdagen_vote_data_ballot_party_summary_daily', 'view_riksdagen_vote_data_ballot_party_summary_monthly', 'view_riksdagen_vote_data_ballot_party_summary_weekly', 'view_riksdagen_vote_data_ballot_politician_summary', 'view_riksdagen_vote_data_ballot_politician_summary_annual', 'view_riksdagen_vote_data_ballot_politician_summary_daily', 'view_riksdagen_vote_data_ballot_politician_summary_monthly', 'view_riksdagen_vote_data_ballot_politician_summary_weekly', 'view_riksdagen_vote_data_ballot_summary', 'view_riksdagen_vote_data_ballot_summary_annual', 'view_riksdagen_vote_data_ballot_summary_daily', 'view_riksdagen_vote_data_ballot_summary_monthly', 'view_riksdagen_vote_data_ballot_summary_weekly', 'view_worldbank_indicator_data_country_summary' ) ) SELECT 'MATERIALIZED VIEWS' AS object_type, expected_count AS expected, matched_count AS found_in_db, ROUND(matched_count::numeric / expected_count * 100, 2) AS coverage_pct FROM expected_mviews, actual_match; -- =========================================================================== -- PART 6: FINAL COVERAGE SUMMARY -- =========================================================================== \echo '' \echo '==========================================================================' \echo '=== FINAL VALIDATION COVERAGE SUMMARY ===' \echo '==========================================================================' \echo '' DO $$ DECLARE v_table_count INTEGER; v_view_count INTEGER; v_mview_count INTEGER; v_total_validated INTEGER; v_coverage_pct NUMERIC; BEGIN -- Get actual counts SELECT COUNT(*) INTO v_table_count FROM pg_tables WHERE schemaname = 'public'; SELECT COUNT(*) INTO v_view_count FROM pg_views WHERE schemaname = 'public'; SELECT COUNT(*) INTO v_mview_count FROM pg_matviews WHERE schemaname = 'public'; v_total_validated := v_table_count + v_view_count + v_mview_count; v_coverage_pct := ROUND((v_total_validated::numeric / 177) * 100, 2); RAISE NOTICE '╔════════════════════════════════════════════════════════════════╗'; RAISE NOTICE '║ SCHEMA VALIDATION V2.0 REPORT ║'; RAISE NOTICE '╠════════════════════════════════════════════════════════════════╣'; RAISE NOTICE '║ Object Type │ Expected │ Validated │ Coverage ║'; RAISE NOTICE '╠══════════════════════╪══════════╪═══════════╪════════════════╣'; RAISE NOTICE '║ Tables │ % │ % │ % ║', lpad('93', 8), lpad(v_table_count::text, 9), lpad(ROUND((v_table_count::numeric/93)*100, 1)::text || '%', 14); RAISE NOTICE '║ Regular Views │ % │ % │ % ║', lpad('56', 8), lpad(v_view_count::text, 9), lpad(ROUND((v_view_count::numeric/56)*100, 1)::text || '%', 14); RAISE NOTICE '║ Materialized Views │ % │ % │ % ║', lpad('28', 8), lpad(v_mview_count::text, 9), lpad(ROUND((v_mview_count::numeric/28)*100, 1)::text || '%', 14); RAISE NOTICE '╠══════════════════════╪══════════╪═══════════╪════════════════╣'; RAISE NOTICE '║ TOTAL │ % │ % │ % ║', lpad('177', 8), lpad(v_total_validated::text, 9), lpad(v_coverage_pct::text || '%', 14); RAISE NOTICE '╚════════════════════════════════════════════════════════════════╝'; RAISE NOTICE ''; IF v_coverage_pct >= 100 THEN RAISE NOTICE '✓✓✓ SUCCESS: 100%% validation coverage achieved!'; RAISE NOTICE ' All objects from full_schema.sql are validated.'; ELSIF v_coverage_pct >= 95 THEN RAISE NOTICE '✓✓ EXCELLENT: %.2f%% coverage - nearly complete', v_coverage_pct; ELSIF v_coverage_pct >= 90 THEN RAISE NOTICE '✓ GOOD: %.2f%% coverage - minor gaps remain', v_coverage_pct; ELSE RAISE NOTICE '⚠ WARNING: %.2f%% coverage - significant gaps exist', v_coverage_pct; END IF; RAISE NOTICE ''; END $$; \echo '' \echo '==========================================================================' \echo '=== VALIDATION COMPLETED ===' \echo '==========================================================================' \echo '' \echo 'Validation finished at:' SELECT NOW(); \echo '' \echo 'Issue: Hack23/cia#7872 - Extend Schema Validation Script' \echo 'Status: Complete - 100% coverage validation implemented' \echo '' \timing off