| 1 | -- GENERATED RESET PLAN ONLY. This file was not executed in Iteration 1. |
| 2 | -- Run only after an operator has taken a backup, stopped research writers, and |
| 3 | -- reviewed the live schema inventory in the companion architecture document. |
| 4 | -- This uses DELETE so foreign-key mistakes or future schema dependencies fail |
| 5 | -- the transaction. It deliberately does not use TRUNCATE or CASCADE. |
| 6 | |
| 7 | BEGIN; |
| 8 | |
| 9 | SET LOCAL lock_timeout = '5s'; |
| 10 | SET LOCAL statement_timeout = '5min'; |
| 11 | |
| 12 | -- Fail rather than race a live writer. Protected tables are share-locked so the |
| 13 | -- before/after count check remains meaningful until this transaction commits. |
| 14 | LOCK TABLE |
| 15 | research.research_event_sources, |
| 16 | research.research_events, |
| 17 | research.global_shareholding_snapshot_values, |
| 18 | research.global_shareholding_snapshots, |
| 19 | research.global_financial_facts, |
| 20 | research.research_documents, |
| 21 | research.research_refresh_jobs, |
| 22 | research.research_refresh_runs |
| 23 | IN ACCESS EXCLUSIVE MODE; |
| 24 | |
| 25 | LOCK TABLE |
| 26 | research.flyway_schema_history_research, |
| 27 | research.global_market_price_observations, |
| 28 | research.global_structured_market_snapshots, |
| 29 | research.irfc_financial_facts_backup_20260902, |
| 30 | research.market_trading_calendar_exceptions, |
| 31 | research.market_trading_schedules |
| 32 | IN SHARE MODE; |
| 33 | |
| 34 | -- Record the protected table counts so this transaction can prove they did not |
| 35 | -- change while rebuildable research data was cleared. |
| 36 | CREATE TEMPORARY TABLE protected_research_table_counts ( |
| 37 | table_name TEXT PRIMARY KEY, |
| 38 | row_count BIGINT NOT NULL |
| 39 | ) ON COMMIT DROP; |
| 40 | |
| 41 | INSERT INTO protected_research_table_counts (table_name, row_count) |
| 42 | VALUES |
| 43 | ('flyway_schema_history_research', (SELECT count(*) FROM research.flyway_schema_history_research)), |
| 44 | ('global_market_price_observations', (SELECT count(*) FROM research.global_market_price_observations)), |
| 45 | ('global_structured_market_snapshots', (SELECT count(*) FROM research.global_structured_market_snapshots)), |
| 46 | ('irfc_financial_facts_backup_20260902', (SELECT count(*) FROM research.irfc_financial_facts_backup_20260902)), |
| 47 | ('market_trading_calendar_exceptions', (SELECT count(*) FROM research.market_trading_calendar_exceptions)), |
| 48 | ('market_trading_schedules', (SELECT count(*) FROM research.market_trading_schedules)); |
| 49 | |
| 50 | -- Dependency order: evidence links and child values precede their parent rows. |
| 51 | DELETE FROM research.research_event_sources; |
| 52 | DELETE FROM research.research_events; |
| 53 | DELETE FROM research.global_shareholding_snapshot_values; |
| 54 | DELETE FROM research.global_shareholding_snapshots; |
| 55 | DELETE FROM research.global_financial_facts; |
| 56 | DELETE FROM research.research_documents; |
| 57 | DELETE FROM research.research_refresh_jobs; |
| 58 | DELETE FROM research.research_refresh_runs; |
| 59 | |
| 60 | DO $$ |
| 61 | BEGIN |
| 62 | IF EXISTS (SELECT 1 FROM research.research_event_sources) |
| 63 | OR EXISTS (SELECT 1 FROM research.research_events) |
| 64 | OR EXISTS (SELECT 1 FROM research.global_shareholding_snapshot_values) |
| 65 | OR EXISTS (SELECT 1 FROM research.global_shareholding_snapshots) |
| 66 | OR EXISTS (SELECT 1 FROM research.global_financial_facts) |
| 67 | OR EXISTS (SELECT 1 FROM research.research_documents) |
| 68 | OR EXISTS (SELECT 1 FROM research.research_refresh_jobs) |
| 69 | OR EXISTS (SELECT 1 FROM research.research_refresh_runs) THEN |
| 70 | RAISE EXCEPTION 'Rebuildable research reset did not reach an empty state'; |
| 71 | END IF; |
| 72 | |
| 73 | IF (SELECT row_count FROM protected_research_table_counts WHERE table_name = 'flyway_schema_history_research') |
| 74 | <> (SELECT count(*) FROM research.flyway_schema_history_research) |
| 75 | OR (SELECT row_count FROM protected_research_table_counts WHERE table_name = 'global_market_price_observations') |
| 76 | <> (SELECT count(*) FROM research.global_market_price_observations) |
| 77 | OR (SELECT row_count FROM protected_research_table_counts WHERE table_name = 'global_structured_market_snapshots') |
| 78 | <> (SELECT count(*) FROM research.global_structured_market_snapshots) |
| 79 | OR (SELECT row_count FROM protected_research_table_counts WHERE table_name = 'irfc_financial_facts_backup_20260902') |
| 80 | <> (SELECT count(*) FROM research.irfc_financial_facts_backup_20260902) |
| 81 | OR (SELECT row_count FROM protected_research_table_counts WHERE table_name = 'market_trading_calendar_exceptions') |
| 82 | <> (SELECT count(*) FROM research.market_trading_calendar_exceptions) |
| 83 | OR (SELECT row_count FROM protected_research_table_counts WHERE table_name = 'market_trading_schedules') |
| 84 | <> (SELECT count(*) FROM research.market_trading_schedules) THEN |
| 85 | RAISE EXCEPTION 'A protected research-schema table changed during reset'; |
| 86 | END IF; |
| 87 | END $$; |
| 88 | |
| 89 | COMMIT; |