# Search Inconsistency Across Entity Types - Approach & Design ## Problem Artifacts go through Elasticsearch with full search capabilities (30+ facets, simple/advanced query modes, aggregations, access control, transliteration search). But Publications, Collections, Proveniences, and Periods all query MariaDB directly using basic LIKE queries. There's no full-text search, no faceted browsing, no relevance scoring for any of these entities. And no way to do cross-entity search. | Entity | Current Backend | What It Can Do | |--------|-----------------|----------------| | Artifacts | Elasticsearch | 30+ facets, aggregations, simple/advanced search, access control | | Publications | MariaDB | 10+ LIKE filters, author JOIN search, year range, entry_type/journal dropdowns | | Collections | MariaDB | Single LIKE on collection name + alphabetical A-Z filter | | Proveniences | MariaDB | LIKE on name + region dropdown | | Periods | MariaDB | No search at all. Just a paginated list. | **Goal:** Move these entities into Elasticsearch/OpenSearch so they get the same search capabilities as artifacts, and keep them in sync with the database. --- ## TL;DR Create separate ES/OpenSearch indexes for Publications, Collections, Proveniences, and Periods. Reuse the outbox + poller from incremental indexing, same infrastructure, different document builders. Keep existing DB queries as fallback. URL parameter (`?engine=opensearch`) for side-by-side comparison. --- ## What Each Entity Actually Needs ### Publications (highest priority) Publications has the most complex search logic today. `PublicationsController::_getPublicationQuery()` (lines 462-514) dynamically combines 10+ optional filters: LIKE on bibtexkey/title/designation/publisher/series, author search via `innerJoinWith('Authors')` across a join table, year range filtering, entry_type and journal dropdowns, and dynamic LIKE on any table field. This is the entity that benefits the most from a search engine: - Author search currently crosses a join table on every single request - Combining 10 optional LIKE filters generates unpredictable query plans - No relevance scoring (LIKE just returns yes/no, not ranked results) - No facet counts (can't show "Journals: JNES (42), RA (38), ZA (21)") The data model is rich: BelongsTo EntryTypes and Journals, BelongsToMany Authors (with sequence) and Editors (with sequence), 23+ direct fields. There's also a merge feature (`merge()` method, lines 229-349) that combines publications and reassigns artifact associations. ### Collections (moderate priority) Current search is a single LIKE filter on the collection name, plus A-Z alphabetical browsing. But the data model has a lot more that isn't exposed through search today: - Multilingual names via `entities_names` table (Arabic supported, already used in Logstash artifact indexing) - Geographic data: coordinates (WGS84), GADM codes for country/region/district - Classification enums: collection_actor, collection_holding, collection_actor_status, collection_holding_status - License info, `collection_is_private` flag, `is_pinned` flag A search engine would unlock faceted browsing by country and classification type, full-text search across name + description + multilingual names, and eventually geo-search. ### Proveniences (moderate priority) Currently has LIKE search on provenience name plus a region dropdown (built from a subquery that only shows regions with proveniences). View page has a Leaflet map with coordinates, region polygons, and site geoshapes. A search engine would give us faceted browsing by region and dynasty, full-text search across name + description, and the option to add geo-distance queries later using the indexed coordinates. ### Periods (discussion point) No search at all today. Just `find('all')` with pagination at 500 per page. A few hundred records with translation support (Arabic via Translate behavior) and a `sequence` field for ordering. Practical benefit of indexing this is minimal for a dataset this small. But indexing it is cheap (simple document, tiny dataset, reindex finishes in seconds) and gives us consistency across all entities. **Do we want all four indexed for consistency, or skip Periods and revisit later?** --- ## The Approach ### Reuse the outbox + poller infrastructure The incremental indexing system we're building for artifacts is designed to be generic. The `entity_type` column in the outbox already supports any entity. We extend it, not rebuild it. ``` approve() -> outbox INSERT (entity_type='publication') -> poller -> PublicationDocumentBuilder -> SearchIndexer -> ES/OpenSearch ``` Same pipeline, different document builder. No new infrastructure needed. ### Implementation order 1. **Publications** first (most complex, highest value) 2. **Collections** and **Proveniences** next (moderate value, simple builders) 3. **Periods** last (pending discussion with Vedant) ### What stays on MariaDB - **View pages** (single entity detail) still query the DB. They load full entities with all associations, translations, maps, etc. The search engine only replaces listing and search pages. - **Autocomplete endpoints** (like `PublicationsController::search()` returning top 10 matches) stay on DB. Fast enough, not worth the complexity. --- ## Index Designs ### Publications Index ```json { "id": 123, "bibtexkey": "smith2020a", "designation": "CDLI P123456", "title": "Old Babylonian Letters from Sippar", "year": "2020", "series": "Yale Oriental Series", "volume": "15", "pages": "23-45", "publisher": "Yale University Press", "address": "New Haven", "note": "...", "oclc": 12345678, "entry_type": "article", "journal": "Journal of Near Eastern Studies", "authors": [ {"name": "John Smith", "name_ascii": "John Smith", "sequence": 1}, {"name": "María García", "name_ascii": "Maria Garcia", "sequence": 2} ], "editors": [ {"name": "...", "name_ascii": "...", "sequence": 1} ], "designation_ascii": "CDLI P123456", "title_ascii": "Old Babylonian Letters from Sippar" } ``` **Filters/facets:** entry_type (terms), journal (terms), year (range/histogram by decade), has_designation (exists/missing). **Search fields:** bibtexkey, designation, title, author names. Simple mode searches across all of them. Advanced mode allows per-field search. **Document builder (`PublicationDocumentBuilder`):** \~5 batch queries: publications, authors (JOIN authors_publications), editors, entry_types (cached), journals (cached). Applies diacritics normalization for `_ascii` fields. ### Collections Index ```json { "id": 45, "collection": "Vorderasiatisches Museum, Berlin", "collection_ascii": "Vorderasiatisches Museum, Berlin", "description": "...", "is_pinned": true, "collection_is_private": false, "collection_actor": "Museum", "collection_holding": "Museum", "country": "DE", "country_name": "Germany", "location": {"lat": 52.52, "lon": 13.41}, "license_id": "CC-BY-4.0", "names": [ {"name": "Vorderasiatisches Museum", "language": "en"}, {"name": "متحف الشرق الأدنى القديم", "language": "ar"} ] } ``` **Filters/facets:** country (terms), collection_actor (terms), collection_holding (terms), is_pinned (boolean). **Search fields:** collection name, multilingual names (nested), description. ### Proveniences Index ```json { "id": 78, "provenience": "Ur (mod. Tell al-Muqayyar)", "provenience_ascii": "Ur (mod. Tell al-Muqayyar)", "description": "...", "region": "Southern Mesopotamia", "region_id": 5, "location_name": "Tell al-Muqayyar", "location": {"lat": 30.96, "lon": 46.10}, "place_name": "Ur", "archives": ["Royal Cemetery Archive"], "dynasties": ["Ur III", "Old Babylonian"] } ``` **Filters/facets:** region (terms, replaces current dropdown), dynasties (terms, new capability). **Search fields:** provenience name, description, location_name, place_name. ### Periods Index ```json { "id": 12, "period": "Ur III", "period_ar": "أور الثالثة", "sequence": 120, "name": "Third Dynasty of Ur", "time_range": "2112-2004 BC", "rulers": ["Ur-Namma", "Shulgi", "Amar-Sin", "Shu-Sin", "Ibbi-Sin"] } ``` Simplest index. Only worth doing for consistency (see discussion point above). --- ## Keeping Indexes in Sync All four entities are modified through `UpdateEventsController::approve()` -\> `UpdateEvent::apply()` -\> `EntitiesUpdate::apply()`. We extend the outbox logic in `approve()`: ```php foreach ($event->entities_updates as $eu) { $entityTable = $eu->entity_table; // Index the entity itself (for its own index) if (in_array($entityTable, ['publications', 'collections', 'periods', 'proveniences'])) { $this->ReindexOutbox->enqueue($entityTable, [$eu->entity_id], 'direct_change'); } // Cascade: also reindex artifacts that reference this entity if (in_array($entityTable, ['collections', 'periods', 'proveniences', 'publications'])) { $this->ReindexOutbox->enqueue('artifact', [], 'cascade_' . $entityTable, [ 'entity_id' => $eu->entity_id, ]); } } ``` One approval can produce multiple outbox rows: one to update the entity in its own index, and one to cascade-reindex the affected artifacts. The poller dispatches to the right builder based on `entity_type`: ```php $builders = [ 'artifact' => new ArtifactDocumentBuilder($connection), 'publication' => new PublicationDocumentBuilder($connection), 'collection' => new CollectionDocumentBuilder($connection), 'provenience' => new ProvenienceDocumentBuilder($connection), 'period' => new PeriodDocumentBuilder($connection), ]; $builder = $builders[$row['entity_type']]; $documents = $builder->build($entityIds); $this->indexer->putDocuments($row['entity_type'], $documents); ``` --- ## ES (default) vs OpenSearch Query Interface For side-by-side comparison, a URL parameter switches which backend the query hits: ``` /publications -> queries Elasticsearch (default) /publications?engine=opensearch -> queries OpenSearch ``` Implementation: ```php class SearchEngineFactory { public static function create(ServerRequest $request): SearchClient { $engine = $request->getQuery('engine', 'elasticsearch'); return match ($engine) { 'opensearch' => new SearchClient(Configure::read('ServiceUrls.opensearch')), default => new SearchClient(Configure::read('ServiceUrls.elasticsearch')), }; } } ``` The poller writes to both ES and OpenSearch, so both indexes always have the same data. The `?engine=` parameter just controls which one gets queried. --- ## Fallback: What Happens When the Search Engine is Down We keep the existing DB query code and fall back to it if the search engine is unavailable: ```php // PublicationsController::index() try { $query = new PublicationSearchQuery('publications', $client); // ... search engine query $publications = $this->paginate($query); } catch (SearchEngineException $e) { Log::warning('Search engine unavailable, falling back to DB: ' . $e->getMessage()); $query = $this->_getPublicationQuery(); // existing DB query, still works $publications = $this->paginate($query); } ``` Users always get results. When the search engine is down they lose facets and relevance scoring, but the listing still works. Same pattern for all four entities. The full reindex command also serves as a recovery tool: ```bash bin/cake opensearch reindex --entity=publications --batch=500 bin/cake opensearch reindex --entity=collections bin/cake opensearch reindex --entity=proveniences bin/cake opensearch reindex --entity=periods bin/cake opensearch reindex --entity=all ``` For smaller entities (collections, proveniences, periods), the whole reindex finishes in seconds. --- ## Edge Cases ### Publication merge (important: outside approve() flow) `PublicationsController::merge()` (lines 229-349) is the one write path that does NOT go through `UpdateEventsController::approve()`. It's a direct controller action that combines publications and reassigns artifact associations. This means our outbox logic in `approve()` won't catch it. We need to add outbox writes directly inside the merge method: ```php // Inside PublicationsController::merge(), after the merge is committed: $this->ReindexOutbox->enqueue('publication', [$oldPubId], 'delete'); $this->ReindexOutbox->enqueue('publication', [$newPubId], 'direct_change'); $this->ReindexOutbox->enqueue('artifact', $affectedArtifactIds, 'cascade_publications'); ``` This is the only write path we've found that bypasses approve(). To be confirmed with maintainers that there are no others. ### Entity deletion via UpdateEvents `UpdateEvent::apply()` (line 128-129) can DELETE entities when the update is null. The outbox needs a `delete` event_type so the poller knows to remove the document from the index instead of rebuilding it. ### New entity creation `EntitiesUpdate::apply()` can create new entities (when `entity_id` is null). The outbox handles this naturally since the poller reads current DB state. But the new entity's ID is only known after save, so the outbox enqueue must happen after the save completes (still inside the transaction). ### Dual cascade on rename When a period or collection is renamed, we need two outbox entries: 1. Update the entity in its own index (e.g., `entity_type='periods'`) 2. Cascade-reindex all artifacts referencing it (e.g., `entity_type='artifact'`, `event_type='cascade_periods'`) Both are handled by the outbox logic shown in the sync section above. ### Translation changes Periods use the Translate behavior for Arabic. When a period's Arabic name changes, both the period document and all artifacts referencing that period need updating. `EntitiesUpdatesTable::newEntityFromChangedEntity()` (lines 68-79) captures translation changes, so the outbox in `approve()` handles this. Collections store multilingual names via `entities_names` (not the Translate behavior). These are saved as associated records through the same UpdateEvents flow. The `CollectionDocumentBuilder` fetches these and includes them in the document. ### Alphabetical browsing for Collections The current A-Z filter uses `LIKE 'A%'`. In ES/OpenSearch, this becomes a prefix query: ```json {"prefix": {"collection.keyword": {"value": "A"}}} ``` ### Pinned collections The index page shows pinned collections separately at the top. Simplest approach: keep the small DB query for pinned collections (just a handful of records, separate from the search results). No need to complicate the search query for this. ### Region dropdown for Proveniences Currently `ProveniencesCollection.php` builds a dropdown showing only regions that have proveniences (via a subquery). With ES, this comes for free from the `region` terms aggregation, which only returns values that exist in the index. ### URL and API compatibility Current URLs like `/publications?bibtexkey=smith&from=2020` must keep working. The controller reads the same query parameters, just passes them to the search engine query class instead of the ORM. Same query params, same response format. All four entities support JSON/CSV/XML/RDF exports via the `serialize` option. These work on whatever data is in the view variables, so they're backend-agnostic. No changes needed. ### Access control Publications, Collections, Periods, and Proveniences do NOT have `is_public` flags. They're all publicly visible. No access control filtering needed in search queries (unlike artifacts which filter by `is_public`/`is_atf_public`/`are_images_public`). One exception: `collection_is_private` exists on Collections. If this should be filtered from public search, we add it as a default filter in the query class. To be confirmed with maintainers. ### Empty index on first deploy When new indexes are created they're empty. The full reindex command must run before search works. The fallback-to-DB mechanism covers the transition period. --- ## Controller Refactor Pattern Each entity follows the same pattern. Here's Publications as the example: **Before (current DB query):** ```php public function index() { $query = $this->_getPublicationQuery(); $publications = $this->paginate($query); $this->set(compact('publications')); } ``` **After (search engine with DB fallback):** ```php public function index() { try { $client = SearchEngineFactory::create($this->request); $query = (new PublicationSearchQuery('publications', $client)) ->find($queryType, query: $queryData) ->where($filterData) ->orderBy($orderData) ->aggregate($filters); $publications = $this->paginate($query); } catch (SearchEngineException $e) { Log::warning('Falling back to DB: ' . $e->getMessage()); $query = $this->_getPublicationQuery(); $publications = $this->paginate($query); } $this->set(compact('publications')); } ``` The `PublicationSearchQuery` class is much simpler than `ElasticSearchQuery` (no access control, no nested ATF fields, no transliteration search). Each entity gets its own query class rather than trying to extend the 1564-line artifact query class. --- ## Acceptance Criteria - [ ] Publications index created with mapping, document builder, and query class - [ ] Publications listing/search uses ES by default with DB fallback - [ ] Publications facets working: entry_type, journal, year range - [ ] Collections index created with mapping, document builder, and query class - [ ] Collections search supports multilingual names and classification facets - [ ] Proveniences index created with mapping, document builder, and query class - [ ] Proveniences region facet replaces current dropdown (from ES aggregation) - [ ] Periods index created (pending discussion with Vedant on priority) - [ ] All indexes kept in sync via the outbox + poller (same infrastructure as artifacts) - [ ] `?engine=opensearch` parameter switches query backend for side-by-side comparison - [ ] Poller writes to both ES and OpenSearch - [ ] Full reindex command supports all entity types (`--entity=publications`, etc.) - [ ] Publication merge writes to outbox directly (handles bypass of approve() flow) - [ ] Entity deletion produces a delete event in the outbox - [ ] Existing URLs and API exports keep working after migration - [ ] DB fallback works when search engine is down