# Incremental Indexing - Approach & Design --- ## Problem Right now, indexing is handled by a Logstash pipeline that runs once a night at 04:50 AM (`dev/data/logstash/pipeline/logstash.conf`). It runs a 489-line SQL query with 20 main-level LEFT JOINs and rebuilds the entire index (all \~340k artifacts) from scratch every single run. There's commented-out incremental tracking in the config (`logstash.conf`), but it's not active. What this means in practice: if a curator approves an edit at 10 AM, that edit won't show up in search results until the next morning. That's up to 24 hours of delay. **Goal:** Make approved edits show up in search within minutes, not hours. --- ## TL;DR Transactional Outbox pattern: write a reindex job inside the same DB transaction as the artifact change. A Docker service polls the outbox and pushes to Elasticsearch/OpenSearch. (\~3 second latency) --- ## Proposed Approach: Transactional Outbox ### Why this approach Every change in CDLI flows through `UpdateEventsController::approve()` (`src/Controller/UpdateEventsController.php:314-375`). This method already wraps all the database work in a `BEGIN`/`COMMIT` transaction (lines 349-359). After the commit happens, there's a gap where nothing touches the search index at all. The idea is to add a single `INSERT` into a `reindex_outbox` table _inside_ that existing transaction, right before commit. A separate background process (the "poller") reads from this table and pushes updates to the search engine. Because the outbox insert lives in the same transaction as the actual data change, we get atomicity for free. If the transaction rolls back, the outbox row disappears with it. No ghost jobs, no orphaned reindex requests. ### Why not other approaches? I looked at several alternatives before landing on the Transactional Outbox: | Approach | Problem | |----------|---------| | **Redis queue** | Creates a two-phase problem. `approve()` commits to the DB, then pushes to Redis. If the Redis write fails after commit, the job is silently lost. The DB has the change but search never gets updated. For a research database, that kind of silent data loss isn't acceptable. | | **CDC (Debezium)** | Reads the MariaDB binlog directly, which is technically the most robust option. But it needs Kafka + Debezium + ZooKeeper, which is a lot of infrastructure. | | **Synchronous indexing** | Update the search index directly inside `approve()` before commit. Simple, but if the search engine is slow or down, curators can't approve edits. That's too tight of a coupling. | | **Dual-write** | Write to DB and search engine in `approve()`. No atomicity between the two - if one succeeds and the other fails, you get inconsistent state. | | **CakePHP Queue plugin** | The plugin manages its own tables and doesn't let you write the job inside your transaction. Same two-phase problem as Redis. | ### How it works, step by step ``` 1. Curator clicks "Approve" on an edit 2. approve() starts a transaction -> $event->apply() saves the artifact changes to MariaDB -> INSERT into reindex_outbox (status=pending, artifact_ids=[...]) -> COMMIT <-- all atomic, all or nothing 3. Poller picks up the pending row (SELECT ... FOR UPDATE SKIP LOCKED) 4. ArtifactDocumentBuilder fetches fresh data from DB (batch SQL, ~10 queries per 500 artifacts) 5. SearchIndexer pushes documents via _bulk API 6. Mark outbox row as done ``` **Important note on ES vs OpenSearch:** We will build this against the current Elasticsearch setup first. The outbox, poller, and document builder are all search-agnostic - they produce JSON documents and hand them to a `SearchIndexer` interface. When the OpenSearch migration is ready, we switch the indexer implementation (basically just changing the URL). This also lets us run both side by side for comparison at the end of the project. --- ## Outbox Table Schema ```sql CREATE TABLE reindex_outbox ( id BIGINT AUTO_INCREMENT PRIMARY KEY, entity_type VARCHAR(50) NOT NULL, entity_ids JSON NOT NULL, event_type VARCHAR(50) NOT NULL, metadata JSON NULL, status ENUM('pending','processing','done','failed') DEFAULT 'pending', retry_count TINYINT DEFAULT 0, retry_after DATETIME NULL, created_at DATETIME DEFAULT NOW(), locked_at DATETIME NULL, processed_at DATETIME NULL, error TEXT NULL, INDEX idx_pending (status, created_at) ); ``` A few notes on the design: - `entity_type` is generic on purpose. It supports artifacts now, but also publications, collections, etc. later. We don't have to change the schema when we add new entity types. - `entity_ids` is a JSON array of IDs. For cascades (like a period rename affecting thousands of artifacts), this is left empty and the poller resolves the actual IDs lazily. This keeps the transaction lightweight. - `metadata` stores context for the poller to resolve cascades, e.g. `{"entity_id": 42}` so it knows which period was renamed. - `locked_at` is useful for debugging. If a row gets stuck in `processing`, we can see when it was claimed and by which process. - `idx_pending(status, created_at)` is a composite index that makes the poller's SELECT fast. --- ## Poller Design ### Where it runs The poller runs as a **separate Docker Compose service**, using the same PHP image as the web app but with a different entrypoint: ```yaml opensearch-poller: image: *cake-image command: bin/cake opensearch_poller restart: unless-stopped depends_on: - mariadb - elasticsearch # will change to opensearch after migration healthcheck: test: ["CMD", "bin/cake", "opensearch_poller_health"] interval: 60s ``` This matches how background work already runs in the project. The image processing pipeline is its own container (`scripts`) with a long-running Python process. The poller follows the same pattern, just with PHP. Docker's `restart: unless-stopped` handles crash recovery automatically. ### Why a Docker service instead of cron | | Docker service | Cron | |--|----------------|------| | Latency | \~2-3 seconds | \~30-60 seconds (1-min minimum granularity) | | Crash recovery | Docker auto-restart | Waits for next cron tick | | PHP bootstrap overhead | Once (stays running) | Every single invocation | | Visibility | `docker ps`, `docker logs` | Dig through cron logs | ### How the loop works - **Adaptive backoff:** Starts at 2 seconds. If there's no work, doubles the sleep up to 30 seconds max. Resets back to 2 seconds as soon as work is found. This means almost zero DB load when idle, but fast response when edits come in. - **Row locking:** Uses `SELECT ... FOR UPDATE SKIP LOCKED` to prevent multiple poller instances from grabbing the same row. - **Priority ordering:** Direct curator edits are processed before cascades. If a period rename creates a huge cascade AND a curator approves an unrelated edit at the same time, the curator's edit shows up in seconds while the cascade works through the backlog. - **Retries:** Up to 3 attempts with exponential backoff using the `retry_after` column. On failure, the row stays `pending` but with a future `retry_after` timestamp, so the poller skips it until the delay expires. Intervals: 30 seconds, 2 minutes, 8 minutes. After 3 failures, the row is marked `failed` with the error message stored for debugging. - **Batch size:** Configurable via CLI flag, default 50 outbox rows per grab. ### Health check A simple command that checks if any `pending` row is older than 5 minutes **and** is actually ready to be picked up (i.e. `retry_after` is either NULL or in the past). This avoids false alarms on rows that are just waiting for their backoff to expire (which can take up to \~10 minutes across all 3 retries). If a ready row has been sitting for 5+ minutes without being claimed, something is wrong - the container is marked unhealthy, visible in `docker ps` and can trigger alerts. --- ## Handling Cascades (1000+ artifacts) When a period name, collection name, or provenience location changes, every artifact referencing that entity needs to be reindexed. A single period change could affect tens of thousands of artifacts. ### The approach: lazy fan-out in the poller We don't want to resolve all those artifact IDs inside the `approve()` transaction, because that would make the transaction slow and heavy. Instead: 1. `approve()` inserts **one lightweight outbox row**: `entity_ids=[], event_type=cascade_periods, metadata={"entity_id":42}` 2. The **poller** resolves the actual artifact IDs when it picks up the row 3. Processing happens in chunks of 500 using keyset pagination 4. 100ms pause between chunks to spread the DB load 5. Each chunk uses the `_bulk` API (one HTTP call per 500 documents, not 500 individual calls) ### What the numbers look like for 10,000 affected artifacts ``` 10,000 IDs / 500 per chunk = 20 chunks Each chunk: ~10 batch SQL queries + 1 _bulk HTTP call Total: ~220 SQL queries spread over ~20 seconds ``` For comparison, the current nightly Logstash processes all 340,000 artifacts in one go. This cascade is roughly 3% of that load, and it's spread out over time instead of hitting all at once. ### Deduplication If multiple outbox rows end up referencing the same artifact (say an artifact was edited directly AND its collection was renamed in the same approval), the poller collects all IDs from the batch of outbox rows, deduplicates them, and builds each artifact's document only once. --- ## DB Load Assessment ### Extra load from the outbox | Operation | How often | Impact | |-----------|-----------|--------| | INSERT into outbox (inside the approve transaction) | \~10-200/day | One extra row per approval. Negligible. | | Poller SELECT for pending rows | Every 2-30 seconds | Hits the idx_pending index, returns 0 rows most of the time. Negligible. | | Builder batch SQL during processing | \~20-1000 queries/day | Indexed WHERE IN queries, chunked. | The important point: once this is working and stable, the nightly Logstash full rebuild of 340k artifacts can be removed. The net effect is that total DB load actually _decreases_. ### Validation plan (community bonding) I'll run these queries against production to get real numbers and size the system properly: ```sql -- Daily approval volume over the last 6 months SELECT DATE(approved) AS day, COUNT(*) AS approvals FROM update_events WHERE status = 'approved' AND approved >= DATE_SUB(NOW(), INTERVAL 6 MONTH) GROUP BY DATE(approved); -- How many artifacts per approval (fan-out factor) SELECT ue.id, COUNT(au.id) AS artifacts_per_event FROM update_events ue JOIN artifacts_updates au ON au.update_event_id = ue.id WHERE ue.status = 'approved' GROUP BY ue.id ORDER BY artifacts_per_event DESC LIMIT 50; -- Peak hours SELECT HOUR(approved) AS hour, COUNT(*) AS approvals FROM update_events WHERE status = 'approved' AND approved >= DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP BY HOUR(approved) ORDER BY approvals DESC; ``` --- ## Search-Agnostic Design The whole pipeline up to the final HTTP call doesn't know or care what search engine is on the other end: | Layer | Tied to a search engine? | |-------|--------------------------| | Outbox INSERT in approve() | No, pure MariaDB | | Poller (claim rows, resolve cascades) | No, pure MariaDB | | ArtifactDocumentBuilder | No, PHP + MariaDB producing JSON | | SearchIndexer (the push layer) | **Yes, this is the only coupled point** | The `SearchIndexer` is behind an interface. We'll implement it against Elasticsearch first (since that's what's running now), and add an OpenSearch implementation when we're ready to migrate. This lets us run both side by side for comparison before fully switching over. --- ## Failure Modes & Recovery | What goes wrong | What happens | How it recovers | |-----------------|--------------|-----------------| | Poller crashes | Rows stay in `processing` state | Docker auto-restarts the poller. A watchdog query resets rows stuck in `processing` for more than 10 minutes back to `pending`. | | Elasticsearch/OpenSearch is down | Poller retries, backs off | Rows stay `pending` and drain when the search engine comes back. Approvals keep working normally since the outbox just accumulates. | | Two pollers grab the same row | Shouldn't happen with SKIP LOCKED | Even if it did, PUT is idempotent. Same document overwrites itself. | | Transaction rolls back | Outbox rows vanish with it | This is correct behavior. No cleanup needed. | | Poller is down for hours | Pending rows pile up in the table | Nothing is lost. Poller restarts, drains the backlog. | ### The ultimate fallback: Full Reindex Command If the outbox gets into a bad state, the builder has a bug that produced wrong documents, or the index gets corrupted for any reason: ```bash bin/cake opensearch reindex --entity=artifacts --batch=500 ``` This bypasses the outbox entirely. It reads straight from MariaDB (the source of truth), builds every document from scratch, and pushes to the search engine. It uses the **same ArtifactDocumentBuilder** as the poller, so there's one codepath to maintain, not two. --- ## Estimate | | Time | |--|------| | Development | \~6-7 days. Outbox table + model (\~1 day), hook into approve() for direct changes and cascades (\~1 day), poller command with Docker service, adaptive backoff, FOR UPDATE SKIP LOCKED, retry logic (\~3 days), integration with ArtifactDocumentBuilder and SearchIndexer (\~1-2 days). | | Testing | \~2-3 days. Integration test (approve -\> outbox -\> poller -\> search document), retry/backoff test, cascade fan-out test, health check test, poller-down-then-restart test. | | **Total** | **\~8-10 days** | --- ## Acceptance Criteria - [ ] `reindex_outbox` table created with the schema above - [ ] `approve()` inserts outbox rows inside the existing transaction for direct changes, cascades, and inscription changes - [ ] `OpenSearchPollerCommand` runs as a Docker service, claims rows with `FOR UPDATE SKIP LOCKED` - [ ] Poller processes both Elasticsearch and OpenSearch via the `SearchIndexer` interface - [ ] `ArtifactDocumentBuilder` produces documents matching current Logstash output (field-by-field comparison test) - [ ] Cascade changes (period/collection/provenience rename) trigger reindex of all affected artifacts, processed in chunks - [ ] Failed jobs are retried up to 3 times, then marked `failed` with error message - [ ] Health check detects stuck pending rows older than 5 minutes - [ ] Full reindex command works as fallback (`bin/cake opensearch reindex --entity=artifacts`) - [ ] Integration test: approve an edit -\> verify outbox row created -\> run poller -\> verify search document updated