feat: apply db optional db indexes at the end of rosetta sync from genesis (#667)
* feat: index_less * feat: add database-aware sync status monitoring with index creation tracking Introduces enhanced sync status reporting that monitors both blockchain synchronization and database index creation progress. This ensures accurate operational readiness reporting for load balancers and external clients. Key changes: - Add IndexCreationMonitor interface with PostgreSQL and H2 implementations - PostgreSQL monitor queries pg_stat_progress_create_index system view - H2 monitor provides NOOP implementation for development/testing - Extract sync status logic into dedicated SyncStatusService - Add SyncStage enum with three stages: SYNCING, APPLYING_INDEXES, LIVE - Update NetworkServiceImpl to use new SyncStatusService Sync status behavior: - SYNCING: Node has not reached blockchain tip yet (synced=false) - APPLYING_INDEXES: Node at tip but indexes being created (synced=false) - LIVE: Fully synced with all indexes applied (synced=true) The service reports synced=true only when both conditions are met: 1. Node reached blockchain tip (within allowed slot delta) 2. No database indexes are currently being created This allows external watchdog services to monitor currentIndex vs targetIndex to trigger index creation, while load balancers use the synced field to determine when the service is ready for production traffic. * refactor: remove optional index migrations for deferred application Removes Flyway migration files containing optional database indices that were previously commented out. These indices will now be applied by an external watchdog service after the node reaches the blockchain tip, rather than during the initial sync process. Removed migration files: - V1.0_2500_0__search_indices.sql (6 search optimization indices) - V1.0_500_0__rosetta_app_transaction.sql (2 transaction indices) - V1.0_600_0__rosetta_app_withdrawal.sql (2 withdrawal indices) - V1.0_900_0__rosetta_app_address_utxo.sql (1 address_utxo index) Rationale: These indices improve query performance but significantly slow down the initial blockchain sync when applied early. By deferring their creation until after sync completion, we achieve faster time-to-tip while still maintaining optimal query performance once the system is operational. The PostgreSQLIndexCreationMonitor tracks index creation progress via pg_stat_progress_create_index, ensuring the sync status accurately reflects the APPLYING_INDEXES stage when the watchdog applies these indices post-sync. Related to sync status monitoring implementation that reports three stages: - SYNCING: Initial blockchain sync - APPLYING_INDEXES: Tip reached, indices being created - LIVE: Fully operational with all indices applied * refactor: use pg_index for monitoring index readiness instead of pg_stat_progress_create_index Refactors the index monitoring system to check pg_index system catalog for index validity and readiness (indisvalid and indisready flags) instead of monitoring active index creation via pg_stat_progress_create_index. Key changes: - PostgreSQLIndexCreationMonitor now queries pg_index joined with pg_class - Checks that ALL required indices exist and have indisvalid=true and indisready=true - Required indices list added to application.yaml (cardano.rosetta.rosetta-indexes) - Only transitions to LIVE state when all 11 required indices are valid and ready This ensures proper state machine flow: SYNCING -> APPLYING_INDEXES -> LIVE and prevents premature transition to LIVE state before external watchdog completes applying the optional performance indices. The previous approach could result in SYNCING -> LIVE -> APPLYING_INDEXES -> LIVE when indices were already applied before the monitoring check ran. * refactor: deduplicate db_indexes configuration to single source Removes duplicate db_indexes list from all profile-specific YAML files (application-online.yaml, application-offline.yaml, application-h2.yaml, application-test.yaml). The list is now defined only once in the base application.yaml and properly inherited by all profiles through Spring Boot's @ConfigurationProperties binding mechanism via RosettaIndexConfig. This eliminates maintenance overhead and ensures consistency across all deployment profiles. Co-Authored-By: Claude Sonnet 4.5 <[email protected]> * tests: added extra test * fix: added more indices and removed silly tests * refactor: extract database indexes to dedicated configuration file with SQL commands Restructures database index configuration to support both index monitoring and external index creation by watchdog services. Changes: - Create dedicated db-indexes.yaml configuration file with 16 database indices - Introduce DbIndex record with 'name' and 'command' fields for each index - Add getIndexNames() and getIndexCommands() helper methods to RosettaIndexConfig - Update PostgreSQLIndexCreationMonitor to use getIndexNames() helper - Move db_indexes list from application.yaml to db-indexes.yaml - Update all tests to work with new configuration structure Benefits: - Separation of concerns: index definitions in dedicated file - Type safety via Java records - External watchdog services can retrieve SQL commands via getIndexCommands() - Single source of truth for both index names and creation commands * feat: add index-applier container for deferred index creation Implements automated index application for the APPLYING_INDEXES sync state. The index-applier container polls the API until the sync stage transitions to APPLYING_INDEXES, then applies all indexes from db-indexes.yaml using CREATE INDEX CONCURRENTLY. Changes: - Add apply-indexes.sh script with idempotent logic and invalid index handling - Add curl, jq, yq dependencies to postgres Dockerfile - Add docker-compose-index-applier.yaml service definition - Include index-applier in main docker-compose.yaml - Add index-management.md documentation in advanced-configuration - Update docker.md deployment guide with index application section The script: - Polls /network/status for APPLYING_INDEXES stage - Detects and drops invalid indexes (from failed CONCURRENTLY operations) - Creates indexes sequentially using CONCURRENTLY to avoid indexer restarts - Reads index definitions from db-indexes.yaml (single source of truth) * fix: exit index-applier when system already synced to prevent infinite wait on restart * fix: apply-indexes.sh schema support, failure tracking, and early exit on synced * chore: bump yaci-store to 2.0.0-rc1 and yaci to 0.4.0 --------- Co-authored-by: Mateusz Czeladka <[email protected]> Co-authored-by: Claude Sonnet 4.5 <[email protected]> Co-authored-by: Lincon Vidal <[email protected]>
