| 1 | -- ITERATION 4 SAFE RESEARCH RESET |
| 2 | -- |
| 3 | -- Preconditions: |
| 4 | -- 1. The research-engine deployment no longer exposes legacy refresh, |
| 5 | -- backfill, portfolio-refresh, refresh-job, or prefetch routes. |
| 6 | -- 2. No Kubernetes Job/CronJob or live request is performing research writes. |
| 7 | -- 3. The verified Nifty/Yahoo historical coverage invariant is 481/481. |
| 8 | -- |
| 9 | -- This deliberately uses ordered DELETE statements. It does not use TRUNCATE, |
| 10 | -- CASCADE, DROP SCHEMA, or any DELETE outside the research schema. |
| 11 | |
| 12 | BEGIN; |
| 13 | |
| 14 | SET LOCAL lock_timeout = '5s'; |
| 15 | SET LOCAL statement_timeout = '10min'; |
| 16 | |
| 17 | -- A writer causes lock acquisition to fail and the transaction to roll back. |
| 18 | LOCK TABLE |
| 19 | research.global_stock_rule_engine_results, |
| 20 | research.research_event_sources, |
| 21 | research.research_events, |
| 22 | research.global_shareholding_snapshot_values, |
| 23 | research.global_shareholding_snapshots, |
| 24 | research.global_financial_facts, |
| 25 | research.global_structured_market_snapshots, |
| 26 | research.research_documents, |
| 27 | research.research_refresh_jobs, |
| 28 | research.research_refresh_runs |
| 29 | IN ACCESS EXCLUSIVE MODE; |
| 30 | |
| 31 | LOCK TABLE |
| 32 | research.flyway_schema_history_research, |
| 33 | research.global_market_price_observations, |
| 34 | research.irfc_financial_facts_backup_20260902, |
| 35 | research.market_trading_calendar_exceptions, |
| 36 | research.market_trading_schedules |
| 37 | IN SHARE MODE; |
| 38 | |
| 39 | -- Prevent concurrent changes to all portfolio-owned identity, provider-mapping, |
| 40 | -- universe, holding, watchlist, broker, and user state while counts are checked. |
| 41 | DO $lock_portfolio$ |
| 42 | DECLARE |
| 43 | table_row record; |
| 44 | BEGIN |
| 45 | FOR table_row IN |
| 46 | SELECT tablename |
| 47 | FROM pg_tables |
| 48 | WHERE schemaname = 'portfolio' |
| 49 | ORDER BY tablename |
| 50 | LOOP |
| 51 | EXECUTE format('LOCK TABLE portfolio.%I IN SHARE MODE', table_row.tablename); |
| 52 | END LOOP; |
| 53 | END |
| 54 | $lock_portfolio$; |
| 55 | |
| 56 | CREATE TEMPORARY TABLE protected_table_counts ( |
| 57 | schema_name text NOT NULL, |
| 58 | table_name text NOT NULL, |
| 59 | row_count bigint NOT NULL, |
| 60 | PRIMARY KEY (schema_name, table_name) |
| 61 | ) ON COMMIT DROP; |
| 62 | |
| 63 | INSERT INTO protected_table_counts (schema_name, table_name, row_count) |
| 64 | VALUES |
| 65 | ('research', 'flyway_schema_history_research', |
| 66 | (SELECT count(*) FROM research.flyway_schema_history_research)), |
| 67 | ('research', 'global_market_price_observations', |
| 68 | (SELECT count(*) FROM research.global_market_price_observations)), |
| 69 | ('research', 'irfc_financial_facts_backup_20260902', |
| 70 | (SELECT count(*) FROM research.irfc_financial_facts_backup_20260902)), |
| 71 | ('research', 'market_trading_calendar_exceptions', |
| 72 | (SELECT count(*) FROM research.market_trading_calendar_exceptions)), |
| 73 | ('research', 'market_trading_schedules', |
| 74 | (SELECT count(*) FROM research.market_trading_schedules)); |
| 75 | |
| 76 | DO $record_portfolio$ |
| 77 | DECLARE |
| 78 | table_row record; |
| 79 | exact_count bigint; |
| 80 | BEGIN |
| 81 | FOR table_row IN |
| 82 | SELECT tablename |
| 83 | FROM pg_tables |
| 84 | WHERE schemaname = 'portfolio' |
| 85 | ORDER BY tablename |
| 86 | LOOP |
| 87 | EXECUTE format('SELECT count(*) FROM portfolio.%I', table_row.tablename) |
| 88 | INTO exact_count; |
| 89 | INSERT INTO protected_table_counts (schema_name, table_name, row_count) |
| 90 | VALUES ('portfolio', table_row.tablename, exact_count); |
| 91 | END LOOP; |
| 92 | END |
| 93 | $record_portfolio$; |
| 94 | |
| 95 | DO $pre_reset_invariants$ |
| 96 | DECLARE |
| 97 | eligible_count bigint; |
| 98 | covered_count bigint; |
| 99 | BEGIN |
| 100 | SELECT count(*) |
| 101 | INTO eligible_count |
| 102 | FROM ( |
| 103 | SELECT DISTINCT universe.instrument_id |
| 104 | FROM portfolio.nifty500_universe AS universe |
| 105 | JOIN portfolio.instrument_provider_mappings AS mapping |
| 106 | ON mapping.instrument_id = universe.instrument_id |
| 107 | WHERE upper(mapping.provider) = 'YAHOO_FINANCE' |
| 108 | AND upper(mapping.status) = 'VERIFIED' |
| 109 | ) AS eligible; |
| 110 | |
| 111 | SELECT count(*) |
| 112 | INTO covered_count |
| 113 | FROM ( |
| 114 | SELECT DISTINCT universe.instrument_id |
| 115 | FROM portfolio.nifty500_universe AS universe |
| 116 | JOIN portfolio.instrument_provider_mappings AS mapping |
| 117 | ON mapping.instrument_id = universe.instrument_id |
| 118 | JOIN research.global_market_price_observations AS observation |
| 119 | ON observation.instrument_id = universe.instrument_id |
| 120 | WHERE upper(mapping.provider) = 'YAHOO_FINANCE' |
| 121 | AND upper(mapping.status) = 'VERIFIED' |
| 122 | ) AS covered; |
| 123 | |
| 124 | IF eligible_count <> 481 OR covered_count <> 481 THEN |
| 125 | RAISE EXCEPTION |
| 126 | 'Reset aborted: verified India history coverage is %/% instead of 481/481', |
| 127 | covered_count, eligible_count; |
| 128 | END IF; |
| 129 | END |
| 130 | $pre_reset_invariants$; |
| 131 | |
| 132 | -- Foreign-key children precede parents. Public facts, provider-derived |
| 133 | -- snapshots, run provenance, and the V1 result cache are intentionally rebuilt. |
| 134 | DELETE FROM research.global_stock_rule_engine_results; |
| 135 | DELETE FROM research.research_event_sources; |
| 136 | DELETE FROM research.global_shareholding_snapshot_values; |
| 137 | DELETE FROM research.global_shareholding_snapshots; |
| 138 | DELETE FROM research.research_events; |
| 139 | DELETE FROM research.global_financial_facts; |
| 140 | DELETE FROM research.global_structured_market_snapshots; |
| 141 | DELETE FROM research.research_documents; |
| 142 | DELETE FROM research.research_refresh_jobs; |
| 143 | DELETE FROM research.research_refresh_runs; |
| 144 | |
| 145 | DO $post_reset_checks$ |
| 146 | DECLARE |
| 147 | protected_row record; |
| 148 | exact_count bigint; |
| 149 | eligible_count bigint; |
| 150 | covered_count bigint; |
| 151 | BEGIN |
| 152 | IF EXISTS (SELECT 1 FROM research.global_stock_rule_engine_results) |
| 153 | OR EXISTS (SELECT 1 FROM research.research_event_sources) |
| 154 | OR EXISTS (SELECT 1 FROM research.global_shareholding_snapshot_values) |
| 155 | OR EXISTS (SELECT 1 FROM research.global_shareholding_snapshots) |
| 156 | OR EXISTS (SELECT 1 FROM research.research_events) |
| 157 | OR EXISTS (SELECT 1 FROM research.global_financial_facts) |
| 158 | OR EXISTS (SELECT 1 FROM research.global_structured_market_snapshots) |
| 159 | OR EXISTS (SELECT 1 FROM research.research_documents) |
| 160 | OR EXISTS (SELECT 1 FROM research.research_refresh_jobs) |
| 161 | OR EXISTS (SELECT 1 FROM research.research_refresh_runs) THEN |
| 162 | RAISE EXCEPTION 'Reset aborted: a rebuildable table is not empty'; |
| 163 | END IF; |
| 164 | |
| 165 | FOR protected_row IN |
| 166 | SELECT schema_name, table_name, row_count |
| 167 | FROM protected_table_counts |
| 168 | ORDER BY schema_name, table_name |
| 169 | LOOP |
| 170 | EXECUTE format( |
| 171 | 'SELECT count(*) FROM %I.%I', |
| 172 | protected_row.schema_name, |
| 173 | protected_row.table_name |
| 174 | ) INTO exact_count; |
| 175 | IF exact_count <> protected_row.row_count THEN |
| 176 | RAISE EXCEPTION |
| 177 | 'Reset aborted: protected table %.% changed from % to % rows', |
| 178 | protected_row.schema_name, |
| 179 | protected_row.table_name, |
| 180 | protected_row.row_count, |
| 181 | exact_count; |
| 182 | END IF; |
| 183 | END LOOP; |
| 184 | |
| 185 | SELECT count(*) |
| 186 | INTO eligible_count |
| 187 | FROM ( |
| 188 | SELECT DISTINCT universe.instrument_id |
| 189 | FROM portfolio.nifty500_universe AS universe |
| 190 | JOIN portfolio.instrument_provider_mappings AS mapping |
| 191 | ON mapping.instrument_id = universe.instrument_id |
| 192 | WHERE upper(mapping.provider) = 'YAHOO_FINANCE' |
| 193 | AND upper(mapping.status) = 'VERIFIED' |
| 194 | ) AS eligible; |
| 195 | |
| 196 | SELECT count(*) |
| 197 | INTO covered_count |
| 198 | FROM ( |
| 199 | SELECT DISTINCT universe.instrument_id |
| 200 | FROM portfolio.nifty500_universe AS universe |
| 201 | JOIN portfolio.instrument_provider_mappings AS mapping |
| 202 | ON mapping.instrument_id = universe.instrument_id |
| 203 | JOIN research.global_market_price_observations AS observation |
| 204 | ON observation.instrument_id = universe.instrument_id |
| 205 | WHERE upper(mapping.provider) = 'YAHOO_FINANCE' |
| 206 | AND upper(mapping.status) = 'VERIFIED' |
| 207 | ) AS covered; |
| 208 | |
| 209 | IF eligible_count <> 481 OR covered_count <> 481 THEN |
| 210 | RAISE EXCEPTION |
| 211 | 'Reset aborted: post-reset India history coverage is %/% instead of 481/481', |
| 212 | covered_count, eligible_count; |
| 213 | END IF; |
| 214 | END |
| 215 | $post_reset_checks$; |
| 216 | |
| 217 | SELECT schema_name, table_name, row_count |
| 218 | FROM protected_table_counts |
| 219 | ORDER BY schema_name, table_name; |
| 220 | |
| 221 | COMMIT; |