## Objective Build a PHP class that takes artifact IDs and produces the same JSON documents that the current Logstash pipeline (artifacts.sql + artifacts.rb) produces. This class is the core of the incremental indexing system. Both the poller and the full reindex command call it. --- ## TL;DR Replace the Logstash Ruby filter (`artifacts.rb`, 289 lines) with a PHP class (`ArtifactDocumentBuilder`) that replicates all 6 transformation stages. The PHP builder works per-artifact (not full-corpus), uses batch SQL for performance, and caches hierarchy data. Tested by comparing its output field-by-field against the current Logstash output. --- ## Why PHP instead of keeping the Ruby script The Ruby script (`dev/data/logstash/resources/artifacts.rb`) only runs inside Logstash. It depends on Logstash's event model (`event.get()`, `event.set()`), Logstash's `mutate { split }` preprocessing, the JDBC input, and the ES output. You can't call it from PHP or run it for a single artifact. For incremental indexing, the poller needs to build a document for specific artifacts the moment they change. That requires a builder in the same language the poller runs in (PHP). The builder also serves the full reindex command, so there's one codepath for document construction, not two. --- ## What the current pipeline does Three components work together today: 1. **artifacts.sql** (489 lines) fetches all data from MariaDB with 20 main-level LEFT JOINs, 4 recursive CTEs for hierarchy paths, and GROUP_CONCAT to pack related data into pipe-separated strings. 2. **logstash.conf mutate { split }** (lines 47-107) splits 37 pipe-separated fields back into arrays before the Ruby filter sees them. 3. **artifacts.rb** (289 lines) transforms those arrays into the final nested document structure with 6 stages. The PHP builder replaces all three. Instead of GROUP_CONCAT + pipe split, it queries each related table separately and gets proper arrays directly from the database. --- ## The 6 Transformation Stages ### Stage 1: Diacritics normalization (artifacts.rb lines 25-40) Strips combining Unicode marks from text fields to produce ASCII versions for search. Uses NFD decomposition, removes diacritics in ranges 0x0300-0x036F, 0x1DC0-0x1DFF, 0xFE20-0xFE2F, then NFC recomposition. **Fields normalized:** - `designation` -\> `designation_ascii` - `dates_referenced` -\> `dates_referenced_ascii` - `provenience` -\> `provenience_ascii` - `written_in` -\> `written_in_ascii` - `archive` -\> `archive_ascii` - `collection` (array) -\> `collection_ascii` (array, each element normalized) **Arabic provenience handling (lines 36-40):** - If `provenience_ar` is NULL: falls back to the English provenience value - If `provenience_ar` has a value: replaces the `(mod. ...)` portion of the English provenience string with the Arabic name. For example, `"Ur (mod. Tell al-Muqayyar)"` becomes `"Ur (mod. تل المقير)"` **PHP equivalent:** `Normalizer::normalize($string, Normalizer::FORM_D)` from the intl extension, then regex to strip the combining mark ranges, then `Normalizer::normalize($result, Normalizer::FORM_C)`. ### Stage 2: ID consolidation (lines 42-69) Builds three arrays from the artifact's various identifiers: - `cdli_id`: starts with P-number (e.g. `P000123`, zero-padded to 6 digits), then adds composite_no and seal_no if present - `all_composite_no`: the artifact's own composite_no plus all witness_composite_no values - `all_seal_no`: the artifact's own seal_no plus all impression_seal_no values **Edge case:** `witness_composite_no` and `impression_seal_no` are already arrays at this point (split by Logstash). In the PHP builder, we fetch them directly as arrays from the `artifacts_composites` and `artifacts_seals` tables. ### Stage 3: Array zipping (lines 71-115) Converts parallel arrays into nested objects. The SQL query packs related data into flat strings using GROUP_CONCAT. Logstash splits them by `|` into parallel arrays. The Ruby filter then zips them together by index. **External resources (lines 71-85):** 3 parallel arrays -\> array of `{external_resource, url, key}` objects. - Edge case: `external_resource_key` can be nil even when the other fields exist (line 81) **Assets (lines 87-115):** 10 parallel arrays -\> array of `{id, type, artifact_aspect, file_format, is_public, annotations, authors, license_id, license_attribution, license_comment}` objects. - `is_public`: converted from string to boolean via `.to_i.zero?` (line 106) - `annotations` and `authors`: each is a semicolon-separated string that gets split into a sub-array (lines 107-108) - These semicolons come from a nested GROUP_CONCAT in the SQL (lines 333, 344) **PHP builder approach:** Skip the GROUP_CONCAT + split entirely. Query each related table with `WHERE artifact_id IN (...)` and get proper arrays. No string packing/unpacking. ### Stage 4: Timestamps and update events (lines 117-146) Zips update event arrays into `{approved, comments, external_resource, authors, authors_ascii}` objects. Also tracks the earliest approved date as `created` and the latest as `modified`. - `authors`: semicolon-separated string split into array (line 131) - `authors_ascii`: same but with diacritics removed (line 132) - `created`: minimum of all approved timestamps (line 135-137) - `modified`: maximum of all approved timestamps (line 139-141) **Important:** These timestamps come from `update_events.approved`, not from `artifacts.created` or `artifacts.updated`. The document's created/modified dates reflect when edits were approved, not when the DB row was created. ### Stage 5: Publication references (lines 148-210) The most field-heavy stage. Zips 16 parallel publication arrays into nested objects with 24 fields each (including 7 ASCII variants). **`make_exact_reference()` logic (lines 14-22):** - If designation or exact_reference is nil: returns nil - If exact_reference starts with `.,:;+-/?[*`: joins directly (e.g. `"BM 015285.23"`) - Otherwise: joins with `, `(e.g. `"BM 015285, p. 45"`) **`primary_publication_designation` (line 204):** the exact_reference of whichever publication has `type = 'primary'`. Only one should exist per artifact. **`publication_publisher` in SQL (lines 408-411):** uses COALESCE to pick from publisher, organization, or school (whichever is non-null). The PHP builder should replicate this logic. **Authors and editors:** semicolon-separated strings split into arrays, plus ASCII variants with diacritics removed. ### Stage 6: ATF parser (lines 212-285) A stateful line-by-line parser that processes ATF (ASCII Transliteration Format) cuneiform text. This is the most complex stage. **Dispatch table:** | Pattern | What it does | |---------|--------------| | `/^@column/` | Sets section marker (e.g. "column 1"), adds to atf_structure | | `/^@/` (other) | Sets surface marker (e.g. "obverse"), resets section, adds to atf_structure, handles surface reuse | | `/^#tr\.ts:/` | Transcription: text after prefix goes to atf_transcription array | | `/^#tr\./` | Translation: language from chars 4-5 (e.g. "en"), text accumulated per language into hash | | `/^[#$]/` | Comment: text after first char goes to atf_comments | | `/^\d/` | Transliteration: extracts line number (e.g. `1'`), line text, builds multiple output fields | **Reuse last surface if empty (line 234):** When a new `@surface` line appears, the parser checks if the previous surface entry has any text yet. If the text is still empty (no transliteration lines were added), it overwrites the surface name instead of creating a new entry. This handles cases like `@obverse` followed immediately by `@column 1` where obverse has no text of its own. **Inline comment extraction (line 264):** Within transliteration lines, inline comments matching `($...$)` are extracted and added to atf_comments. The regex `($.+?$)` captures text between literal `$` signs. **Translation language accumulation (lines 241-249):** Translation lines like `#tr.en: The king spoke` have the language code at chars 4-5. Multiple lines of the same language are concatenated with spaces. The result is a hash `{en: "The king spoke to...", de: "Der König sprach..."}` which gets converted to an array of `{language, text}` objects at lines 270-276. **7 output fields:** 1. `atf_structure` - array of surface/column markers 2. `atf_comments` - array of comment strings 3. `atf_transcription` - array of transcription strings 4. `atf_translation` - nested array: `[{language: "en", text: "..."}, ...]` 5. `atf_transliteration_lines` - nested array: `[{surface, section, line, text}, ...]` 6. `atf_transliteration_surface` - nested array: `[{surface, text}, ...]` 7. `atf_transliteration` - single string: all transliteration text joined with spaces --- ## The 4 Recursive CTEs (hierarchy paths) These are in artifacts.sql, not artifacts.rb. They build full hierarchy paths like `"Tool > Knife"` from self-referential parent-child tables. CakePHP's ORM can't do recursive CTEs, so these run as raw SQL. | Hierarchy | SQL lines | Join table | Output | |-----------|-----------|------------|--------| | **artifact_types** | 106-130 | Direct FK (artifact_type_id) | `"Tool > Knife"` + Arabic | | **materials** | 155-178 | artifacts_materials (many-to-many) | `"Clay > Fired Clay"` + aspect + color, all with Arabic | | **genres** | 227-252 | artifacts_genres (many-to-many) | `"Administrative > Receipt"` + Arabic | | **languages** | 266-291 | artifacts_languages (many-to-many) | `"Sumerian > Neo-Sumerian"` + Arabic | All 4 follow the same pattern: root nodes (parent_id IS NULL) form the base case, recursive step concatenates with `>`. Arabic translations use IFNULL to fall back to English when missing. **PHP builder optimization:** These hierarchies change very rarely. The builder caches them in memory on first load and reuses the cache across all artifacts in a batch. One recursive CTE query per hierarchy, not per artifact. Invalidate the cache when a hierarchy entity change comes through the outbox. --- ## Data Fetching Strategy ### Current approach (Logstash) One massive 489-line SQL query that fetches everything for ALL 340k artifacts at once using 20 LEFT JOINs and GROUP_CONCAT. ### PHP builder approach Separate queries per data type, batched by artifact IDs: ```php public function build(array $artifactIds): array { // ~10 batch queries for 500 artifacts $artifacts = $this->fetchArtifacts($artifactIds); // 1 query $collections = $this->fetchCollections($artifactIds); // 1 query $materials = $this->fetchMaterials($artifactIds); // 1 query $genres = $this->fetchGenres($artifactIds); // 1 query $languages = $this->fetchLanguages($artifactIds); // 1 query $extResources = $this->fetchExternalResources($artifactIds); // 1 query $assets = $this->fetchAssets($artifactIds); // 1 query $publications = $this->fetchPublications($artifactIds); // 1 query $updateEvents = $this->fetchUpdateEvents($artifactIds); // 1 query $inscriptions = $this->fetchInscriptions($artifactIds); // 1 query $composites = $this->fetchComposites($artifactIds); // 1 query $seals = $this->fetchSeals($artifactIds); // 1 query // Hierarchy lookups (cached, not per-artifact) $typeHierarchy = $this->getArtifactTypeHierarchy(); $materialHierarchy = $this->getMaterialHierarchy(); $genreHierarchy = $this->getGenreHierarchy(); $languageHierarchy = $this->getLanguageHierarchy(); // Build documents in PHP (no more DB calls) $documents = []; foreach ($artifacts as $id => $artifact) { $doc = $artifact; $doc += $this->applyDiacritics($artifact); $doc += $this->consolidateIds($artifact, $composites[$id] ?? [], $seals[$id] ?? []); $doc['external_resource'] = $extResources[$id] ?? []; $doc['asset'] = $this->buildAssets($assets[$id] ?? []); $doc['update'] = $this->buildUpdateEvents($updateEvents[$id] ?? []); $doc['publication'] = $this->buildPublications($publications[$id] ?? []); $doc += $this->parseAtf($inscriptions[$id] ?? null); $doc['artifact_type'] = $typeHierarchy[$artifact['artifact_type_id']] ?? null; $doc['artifact_type_ar'] = $typeHierarchyAr[$artifact['artifact_type_id']] ?? null; // ... genre, language, material from cached hierarchies $documents[] = $doc; } return $documents; } ``` Each `fetch*` method runs a single query with `WHERE artifact_id IN (...)` and returns results grouped by artifact_id. For 500 artifacts, this is \~12 indexed queries instead of one 489-line monster query. Each query is simple, predictable, and independently testable. --- ## Handling `retired` artifacts The current Logstash pipeline checks the `retired` field (logstash.conf lines 25-31) and sets the ES action to `delete` instead of `index`. The PHP builder needs to handle this: ```php if ($artifact['retired']) { // Don't build a document, signal deletion to the caller $documents[$id] = ['_action' => 'delete']; } else { // Build the full document $documents[$id] = $this->buildDocument($artifact, ...); } ``` The poller checks this flag and calls DELETE instead of PUT for retired artifacts. --- ## Testing Plan ### Field-by-field comparison against Logstash The primary test: run the existing Logstash pipeline on a set of known artifacts, capture the output as JSON fixtures. Then run the PHP builder on the same artifacts and compare field by field. Any difference is a bug. **Fixture selection criteria:** - Artifacts with multiple collections (tests array zipping) - Artifacts with Arabic translations (tests provenience_ar grafting) - Artifacts with hierarchical types (e.g. "Tool \> Knife" tests recursive CTEs) - Artifacts with multiple publications including a primary (tests make_exact_reference and primary_publication_designation) - Artifacts with multi-surface ATF text (tests the full ATF parser dispatch) - Artifacts with consecutive @surface lines and no text in between (tests the reuse-last-surface edge case) - Artifacts with inline comments in transliteration (tests the `($...$)` extraction) - Artifacts with missing/null fields (tests nil handling throughout) - Retired artifacts (tests the delete action) ### Unit tests per stage Each transformation stage gets its own test file: - `DiacriticsTest.php`: normalization of various scripts (Latin with accents, Arabic) - `IdConsolidationTest.php`: P-number formatting, composite/seal aggregation - `PublicationReferenceTest.php`: make_exact_reference with all punctuation cases - `AtfParserTest.php`: every dispatch branch, surface reuse, inline comments, language accumulation The ATF parser test should cover every line prefix pattern and the reuse-last-surface edge case specifically. ### Integration test Approve an edit on a test artifact -\> verify outbox row -\> run poller -\> compare the OpenSearch document against expected output. --- ## Edge Cases to Watch 1. **Null handling everywhere.** The Ruby filter checks `unless X.nil?` before every operation. The PHP builder must do the same. A publication with nil authors shouldn't crash the builder. 2. **Empty arrays vs null.** Logstash split on a null field returns the original null, not an empty array. The PHP builder should return `[]` (empty array) for fields like collections/materials/genres when no related records exist. 3. **asset_is_public string to boolean.** The SQL returns `is_public` as a string ('0' or '1'). The Ruby filter converts via `.to_i.zero?`. The PHP builder needs `(bool)(int)$value` or similar. 4. **Semicolons inside semicolon-delimited fields.** Asset annotations and publication authors are packed with `;` as separator in the SQL. If an actual annotation or author name contains a semicolon, the split would produce wrong results. The PHP builder avoids this entirely by fetching arrays directly. 5. **GROUP_CONCAT truncation.** MariaDB's `group_concat_max_len` defaults to 1024 bytes. If any GROUP_CONCAT result exceeds this, it gets silently truncated. The current Logstash SQL is vulnerable to this for artifacts with many publications, assets, or update events. The PHP builder avoids this by not using GROUP_CONCAT. 6. **COALESCE for publisher.** The SQL uses `COALESCE(publisher, organization, school, '')` for the publication publisher field (line 409). The PHP builder needs to replicate this fallback chain. 7. **Update event authors format.** The SQL (line 457) packs creator and authors as `CONCAT(creator, ';', authors)`. This means the first element after splitting by `;` is always the creator, followed by other authors. The Ruby filter doesn't treat them differently, but it's worth knowing. 8. **ATF parser trailing spaces.** Line 254 in the Ruby filter strips trailing spaces from transliteration text: `.gsub(/ $/, '')`. Line 244 does the same for translation text. The PHP builder should match this. 9. **Chemical data as boolean.** The SQL returns `1` if chemical data exists for the artifact, NULL if not (lines 356-362). Logstash converts this to boolean (logstash.conf line 43). The PHP builder should output `true` or `false`, not `1` or `null`. --- ## File Structure ``` app/cake/src/Service/ ├── ArtifactDocumentBuilder.php # main class, build() method ├── DocumentBuilderInterface.php # interface shared with other entity builders ├── AtfParser.php # ATF parsing logic extracted into its own class └── DiacriticsHelper.php # Unicode normalization utility app/cake/tests/TestCase/Service/ ├── ArtifactDocumentBuilderTest.php # field-by-field comparison against Logstash ├── AtfParserTest.php # every dispatch branch + edge cases ├── DiacriticsHelperTest.php # normalization tests └── fixtures/ └── logstash_output/ # captured JSON from Logstash for known artifacts ``` The ATF parser and diacritics helper are extracted into separate classes because they're independently testable and reusable. The builder class orchestrates the data fetching and calls these utilities. --- ## Estimate | | Time | |--|------| | Development | \~3 weeks. Data fetching + hierarchy caching (\~1 week), stages 1-5 port (\~1 week), ATF parser port (\~1 week) | | Testing | \~1 week. Capture Logstash fixtures, write comparison tests, unit tests per stage, ATF edge case tests | | **Total** | **\~4 weeks** | The ATF parser is the most complex single piece. The rest of the stages are mostly data restructuring. --- ## Acceptance Criteria - [ ] `ArtifactDocumentBuilder::build(array $ids)` returns JSON documents matching Logstash output - [ ] Batch SQL fetching: \~12 queries per batch, not per artifact - [ ] Hierarchy caching: recursive CTEs run once per batch, not per artifact - [ ] Diacritics normalization matches Ruby's NFD/NFC approach for all fields - [ ] Arabic provenience grafting works correctly (null fallback + mod replacement) - [ ] ID consolidation: P-number zero-padded to 6 digits, composites and seals aggregated - [ ] Publication exact_reference: punctuation joining rule matches Ruby's make_exact_reference() - [ ] primary_publication_designation correctly identified from type='primary' - [ ] COALESCE logic for publisher field replicated (publisher \> organization \> school) - [ ] ATF parser: all 6 dispatch branches working - [ ] ATF parser: reuse-last-surface-if-empty edge case handled - [ ] ATF parser: inline comment extraction from `($...$)` pattern - [ ] ATF parser: translation languages accumulated correctly - [ ] Retired artifacts produce a delete signal instead of a document - [ ] asset_is_public correctly converted from string to boolean - [ ] Field-by-field comparison test passes against Logstash output for a diverse fixture set - [ ] Unit tests for each transformation stage - [ ] Implements DocumentBuilderInterface (same interface as PublicationDocumentBuilder etc.) - [ ] No GROUP_CONCAT used (avoids truncation and semicolon-in-data bugs)