Cardano Updates

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)

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

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

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]>

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.

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.

Removes the V1.0_1500_0__rosetta_app_error_workaround.sql migration file
that was creating an error table as a workaround for yaci-store not
creating it automatically.

Co-authored-by: Mateusz Czeladka <[email protected]>

Removes the V1.0_1500_0__rosetta_app_error_workaround.sql migration file
that was creating an error table as a workaround for yaci-store not
creating it automatically.

Co-authored-by: Mateusz Czeladka <[email protected]>

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

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.

Co-authored-by: Mateusz Czeladka <[email protected]>