## Objective Build a set of recovery tools that can rebuild search indexes from scratch when needed. This covers the initial data migration from ES to OpenSearch, recovery from index corruption, and a safety net if the incremental indexing pipeline ever falls out of sync. --- ## TL;DR A `bin/cake opensearch reindex` command that reads from MariaDB (the source of truth) and rebuilds search indexes in batches. Uses the same document builders as the poller, so there's one codepath, not two. Admin progress UI lets maintainers trigger it from the browser and watch it run. --- ## Problem Today there is no way to rebuild the search index from the application. The only reindex mechanism is Logstash, which runs on a hardcoded cron schedule (`50 4 * * *` in logstash.conf:4). To manually trigger it, you have to edit the cron expression in the config file and restart the container (`SEARCH.md` lines 41-54). There's no CLI command, no admin UI, and no way to reindex a specific entity type or a subset of records. There's also no graceful degradation when Elasticsearch goes down. `ElasticSearchQuery.php` makes raw HTTP calls (lines 250-252, 751-753) with no try/catch, no retries, and no fallback. If ES is unavailable, the user sees an unhandled exception. We need: 1. A CLI command for full reindex (for developers and CI) 2. An admin UI to trigger and monitor reindex (for maintainers) 3. Graceful degradation when the search engine is down --- ## Approach ### 1. Full Reindex Command A new CakePHP command: `app/cake/src/Command/OpenSearchReindexCommand.php` ```bash # Reindex all artifacts in batches of 500 bin/cake opensearch_reindex --entity=artifacts --batch=500 # Reindex publications bin/cake opensearch_reindex --entity=publications # Reindex everything bin/cake opensearch_reindex --entity=all # Target a specific search engine bin/cake opensearch_reindex --entity=artifacts --engine=opensearch bin/cake opensearch_reindex --entity=artifacts --engine=elasticsearch bin/cake opensearch_reindex --entity=artifacts --engine=both # default ``` **How it works:** ``` 1. Create a new index with a timestamped name (e.g. artifacts_20260615_120000) 2. Load the mapping template into the new index 3. Query MariaDB for all entity IDs, ordered by ID 4. Process in batches using keyset pagination: - Fetch next 500 IDs where id > last_processed_id - Call the document builder (same one the poller uses) - Push to search engine via _bulk API - Print progress: "Processed 1500/340247 (0.4%)" 5. Swap the index alias: artifacts -> artifacts_20260615_120000 6. Delete the old index ``` **Index aliases for zero-downtime reindex:** Note: index aliases are not currently used anywhere in the CDLI codebase. This is a new pattern. The existing Logstash pipeline writes directly to the `artifacts` index name. As part of this work, we'll need to set up the initial alias so that `artifacts` becomes an alias pointing to `artifacts_v1` (or similar), rather than a direct index name. The reindex command doesn't write directly to the `artifacts` alias. It creates a new index, populates it, then atomically swaps the alias. This means: - Search keeps working against the old index while the new one is building - If the reindex fails halfway, the old index is untouched - Rollback is instant (just point the alias back) ```bash # What the alias swap looks like POST /_aliases { "actions": [ {"remove": {"index": "artifacts_old", "alias": "artifacts"}}, {"add": {"index": "artifacts_20260615_120000", "alias": "artifacts"}} ] } ``` **Checkpointing for resumability:** If the reindex crashes at batch 500 out of 680, we don't want to start over. The command saves progress to a database table (not a temp file, since containers have separate filesystems and `/tmp` doesn't survive restarts): ```sql CREATE TABLE reindex_jobs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, entity VARCHAR(50) NOT NULL, engine VARCHAR(50) NOT NULL, index_name VARCHAR(100) NOT NULL, last_id BIGINT DEFAULT 0, total BIGINT DEFAULT 0, processed BIGINT DEFAULT 0, status ENUM('running','done','failed') DEFAULT 'running', started_at DATETIME DEFAULT NOW(), finished_at DATETIME NULL ); ``` The command updates `last_id` and `processed` after each batch. The admin progress UI reads from this same table. On restart with `--resume`: ```bash bin/cake opensearch_reindex --entity=artifacts --resume # Finds the latest 'running' job for artifacts, picks up from last_id ``` **Same document builders as the poller:** ```php $builders = [ 'artifact' => new ArtifactDocumentBuilder($connection), 'publication' => new PublicationDocumentBuilder($connection), 'collection' => new CollectionDocumentBuilder($connection), 'provenience' => new ProvenienceDocumentBuilder($connection), 'period' => new PeriodDocumentBuilder($connection), ]; $builder = $builders[$entity]; ``` This is the same map the poller uses. One builder class, two callers (poller for incremental, command for full). No duplicate logic. ### 2. Admin Reindex UI An admin page where maintainers can trigger a reindex and watch the progress. The codebase already has a working AJAX progress bar pattern in `Admin/ArtifactAssets/update_index.php` (lines 13-90), so we follow the same approach. **New controller:** `Admin/ReindexController.php` The admin UI follows the same browser-driven batch loop pattern that already exists in `Admin/ArtifactAssets/update_index.php`. The browser drives the loop with sequential AJAX calls, each processing one batch synchronously. No background process, no `exec()`, no Redis queue. This matches how the project already handles long-running admin tasks. ```php // GET /admin/reindex - shows the page with entity dropdown, engine selector, and start button public function index() { // Count total records per entity type for the UI } // POST /admin/reindex/batch - processes one batch and returns progress public function batch() { $entity = $this->request->getData('entity'); $engine = $this->request->getData('engine'); $lastId = (int) $this->request->getData('last_id', 0); $batchSize = 500; $builder = $builders[$entity]; $ids = /* SELECT id FROM {entity} WHERE id > $lastId ORDER BY id LIMIT $batchSize */; if (empty($ids)) { // Done - swap index alias return json: {status: 'done', processed: $total} } $documents = $builder->build($ids); $indexer->bulkPut($newIndexName, $documents); // Update reindex_jobs row with progress return json: {status: 'running', last_id: end($ids), processed: $processed, total: $total} } ``` **Frontend (same pattern as ArtifactAssets/update_index.php lines 44-67):** ```javascript async function reindexBatch(lastId) { const response = await fetch('/admin/reindex/batch', { method: 'POST', body: JSON.stringify({entity, engine, last_id: lastId}), headers: {'X-CSRF-Token': csrfToken, 'Content-Type': 'application/json'} }); const result = await response.json(); // Update Bootstrap progress bar progressBar.width((100 * result.processed / result.total) + '%'); progressBar.text(result.processed + '/' + result.total); if (result.status === 'running') { await reindexBatch(result.last_id); // next batch } else { showAlert('Reindex complete!'); } } ``` Each request processes 500 records and returns. The browser controls the pace. If the tab is closed, the reindex stops (but the partially built index doesn't affect the live alias, and `reindex_jobs` tracks where it stopped for resuming). The CLI command (`bin/cake opensearch_reindex`) does the same work but runs independently in a terminal or Docker container, without a browser. Both use the same builders and `reindex_jobs` table. ### 3. Graceful Degradation When Search Engine is Down Currently `ElasticSearchQuery.php` throws unhandled exceptions when ES is unreachable. We add try/catch around the HTTP calls in the wrapper method (from the performance logging issue): ```php private function executeQuery(string $endpoint, string $body): array { try { $start = microtime(true); $client = new Client(); $response = $client->post($this->getUrl() . $endpoint, $body, ['type' => 'json']); $duration = (microtime(true) - $start) * 1000; $result = $response->getJson(); if (array_key_exists('error', $result)) { throw new SearchEngineException(self::_formatError($result['error'])); } $this->logQuery($endpoint, $duration, $body, $result); return $result; } catch (NetworkException $e) { // Connection refused, DNS failure, timeout Log::error('Search engine unreachable: ' . $e->getMessage()); throw new SearchEngineException('Search is temporarily unavailable', 503, $e); } catch (ClientException $e) { // Client config error (e.g. curl extension not loaded) Log::error('Search engine client error: ' . $e->getMessage()); throw new SearchEngineException('Search configuration error', 500, $e); } } ``` Controllers catch `SearchEngineException` and fall back to DB queries: ```php // SearchController::index() for artifacts try { $query = new ElasticSearchQuery('artifacts', $access); // ... normal search } catch (SearchEngineException $e) { // Show a user-friendly message instead of a stack trace $this->Flash->warning(__('Search is temporarily unavailable. Showing basic results.')); // For artifacts, there's no simple DB fallback (too complex) // Just show the error message } // PublicationsController::index() try { $query = new PublicationSearchQuery('publications', $client); // ... search engine query } catch (SearchEngineException $e) { // Fall back to existing DB query $query = $this->_getPublicationQuery(); $publications = $this->paginate($query); } ``` For artifacts specifically, there's no simple DB fallback (the 489-line SQL query with 54 JOINs isn't something we'd replicate in the controller). The graceful degradation for artifacts is showing a clear error message instead of a stack trace. For publications/collections/proveniences/periods, we can fall back to their existing DB queries since those still exist in the codebase. --- ## What Does NOT Change | Component | Change? | Why | |-----------|---------|-----| | Logstash | No changes | Stays as-is. Serves as an independent safety net until we're confident in the new system. | | Existing search pages | No changes | They keep working. The reindex command just rebuilds what's behind them. | | Document builders | No changes | Reindex command uses the exact same builders the poller uses. | --- ## Risks ### 1. Memory during full reindex of 340k artifacts Processing in batches of 500 keeps memory bounded. The builder fetches 500 artifacts, builds documents, pushes to ES, then discards them. Peak memory is proportional to batch size, not corpus size. ### 2. DB load during full reindex The reindex runs \~10 batch queries per 500 artifacts = \~6800 queries for 340k artifacts, spread over the duration of the reindex. Adding a small delay between batches (100ms) spreads the load further. This is still less than what Logstash does (one massive 489-line query that locks tables). ### 3. Index alias atomicity The alias swap is atomic in both ES and OpenSearch. But if the command crashes before the swap, the new index sits there orphaned. The command should clean up incomplete indexes on startup (delete any index matching `artifacts_*` that isn't currently aliased). ### 4. Reindex during active writes If the poller is processing incremental updates while a full reindex is running, they might write to different indexes (poller writes to current alias, reindex builds a new index). After the alias swap, some incremental updates could be lost. Options: - Pause the poller during full reindex (simplest, safest) - Or accept that the first few minutes after swap might need the poller to catch up from the outbox (outbox rows are still there) Recommendation: pause the poller during full reindex. The reindex command inserts a row into `reindex_jobs` with `status = 'running'` when it starts and sets `status = 'done'` when it finishes. The poller checks this table at the start of each loop cycle and skips processing if any reindex job is currently running. Both the command and the poller share MariaDB, so this works across Docker containers (unlike a lock file in `/tmp` which would be invisible between containers). Outbox rows keep accumulating as pending during this time and drain automatically once the reindex finishes. --- ## Estimate | | Time | |--|------| | Development | \~4-5 days. Reindex command with alias swap and checkpointing (\~3 days), admin UI with progress bar (\~1-2 days). | | Testing | \~2 days. Test full reindex for artifacts and all entity types, test resume after crash, test alias swap, test admin UI progress tracking, test graceful degradation. | | **Total** | **\~6-7 days** | --- ## Acceptance Criteria - [ ] `bin/cake opensearch_reindex --entity=artifacts --batch=500` reindexes all artifacts - [ ] Command supports all entity types: artifacts, publications, collections, proveniences, periods, all - [ ] Command supports `--engine` flag to target elasticsearch, opensearch, or both - [ ] Index aliases used for zero-downtime reindex (create new index, populate, swap alias) - [ ] Checkpointing with `--resume` flag to continue after crash - [ ] Uses the same document builders as the poller (one codepath) - [ ] Uses the `_bulk` API for batch pushing (not individual PUTs) - [ ] Admin page at `/admin/reindex` with entity dropdown, engine selector, and start button - [ ] AJAX progress bar showing processed/total count and percentage - [ ] `SearchEngineException` added with try/catch in the HTTP wrapper - [ ] Publications/Collections/Proveniences/Periods controllers fall back to DB queries on search engine failure - [ ] Artifact search shows user-friendly error message when search engine is down - [ ] Progress printed to console during CLI execution