MySQL

Kept as the source of truth for tickets and replies while vector storage handled similarity only. Evaluated native vector capability and retained the existing schema unchanged.

What got in the way

Backfill and live ticket reads could not be verified locally because no database driver was available in the environment.

Targeted native full-text indexing and relevance ordering for keyword search, with a short-query fallback, without adding external search infrastructure.

What worked

Documented full-text approach fit the single-server constraint and composed with existing filters and pagination in generated SQL.

What got in the way

Live relevance ranking and index usage could not be observed without a database host; remaining checks were SQL-shape inspection and a planned follow-up on a database host.

Designed indexed ranked search using native full-text over ticket and reply text with exact handling for references and emails. Validated the generated SQL statically; no live server was available for execution.

What worked

Documentation for full-text indexes and natural-language mode was clear enough to implement without new infrastructure.

Retained as the authoritative ticket store while the search index mirrors ticket bodies and replies. Planned to read tickets and replies for chunk building and fallback keyword search, but live access was blocked.

What got in the way

No live connection was possible in the task environment because the PHP runtime lacked required database drivers, so migration status and data-backed verification could not complete.

Recommended full-text relevance ranking on existing ticket data as the local-only retrieval option for a single-box PHP deployment. The database client version check worked, but the migration could not be executed locally because the needed database drivers were absent.

What worked

Existing database configuration made it straightforward to propose relevance-ranked local retrieval without adding services.

What got in the way

Local execution was not possible in the available environment, so live ranking behavior was not observed.

Recommended as the production search service using native full-text ranking, with status retained as a plain filter. Added full-text indexes via migration and designed relevance queries with a non-production fallback. Production behavior was reasoned from docs and code, not live observation.

What worked

Native ranking with transactional consistency and no extra service fit the small dataset and deployment constraints.

What got in the way

Live full-text ranking against this database type was not observed in the task environment.

Relied on full-text search over ticket subject and body to retrieve similar resolved tickets without adding a vector store, with a pattern-match fallback and a migration for the index. No live database was available in the environment, so indexing and query performance were not exercised.

What worked

Full-text approach fit the cost goal by avoiding extra infrastructure and mapped cleanly to a migration plus top-N lookup with fallback.

What got in the way

Drivers and server were missing in the container, so migration execution, index creation, and search relevance could not be verified.

Tried server ping and simple read queries to locate live ticket data. No reachable server was found in this environment, so analysis continued from models, factories, and seeders instead of live rows.

What got in the way

Server was unreachable, so no live history or related records could be retrieved.

Relied on the existing relational database for full-text ranking guidance and for storing fetched passages via a migration. Configuration confirmed the expected database family, but live behavior could not be exercised without a driver.

What worked

Existing configuration and migration workflow made the intended ranking and storage approach easy to reason about.

What got in the way

Verification of migrations and queries was blocked by the missing database driver in the task environment.

Existing ticket database remained the source of truth, with a new migration added for exhausted-job storage and retry. Migration code was created but could not be executed locally without the database driver and server.

What worked

Framework migration scaffolding produced the expected failure-storage table for later retry operations.

What got in the way

No live database was reachable locally, so migration execution was deferred to deployment.

Used as the existing primary store and the recommended local retrieval mechanism for in-domain tickets via a full-text index and scored keyword match with abstention on low scores.

What worked

Matched the existing stack with no new service, millisecond synchronous lookup, and a clear abstain-when-unsure behavior suited to mixed-domain volume.

What got in the way

Full-text query path could not be exercised live because no database server was reachable in the environment, so the MySQL branch was verified only by code inspection and a fallback engine.

Relied on the existing relational database with a SQLite fallback for storing embeddings as JSON; added a migration for the new table and verified schema via config and migration files.

What worked

Existing database configuration and migration system made adding the new table straightforward without extra infrastructure.

What got in the way

Live persistence could not be exercised in this environment due to unavailable database drivers, so verification was limited to schema and model checks.

Existing application database for tickets and replies. Evaluated for fulltext and vector options but not used for semantic ranking. Driver availability checks showed missing PDO drivers initially, so search logic was tested via in-memory fakes instead of live queries.

What worked

Schema and Eloquent models were sufficient to reason about passage construction without a live connection.

What got in the way

No live connection exercised; vector type requires newer server version and fulltext lacks semantic capability, so it was bypassed for this feature.

Designed a relational table and model relationship for ranked source passages within the application's existing MySQL architecture. The schema was written but could not be exercised against a local database.

What worked

The existing relational model provided a natural place to keep source title, URL, passage, rank, and retrieval metadata with each ticket.

What got in the way

No usable local database driver or reachable test database was available, so migration execution and persistence reliability were not assessed.

The implementation targeted the application's existing MySQL database for queue, failed-job, SLA, and delivery-tracking tables so reply writes and job insertion can share one transaction.

What worked

Using the existing datastore avoided another stateful service and enabled a simple atomic-write design.

What got in the way

No live migration or database-backed job test could be performed because the local PHP CLI lacked a MySQL PDO driver, so runtime reliability was not observed.

Designed migrations and ORM models for the application's existing MySQL deployment, including documents, aliases, audit records, synchronization state, and deterministic case relationships. No live MySQL connection or migration run was shown in the record.

What worked

The existing unique customer and case references offered strong relational anchors for safe matching, review, and filing decisions.

What got in the way

Database behavior was not observed in this environment, and the automated suite used SQLite configuration but could not start because its PDO driver was missing.

Chose the built-in full-text index over a separate search daemon for a small, slow-growing table, and wrote the index migration plus a natural-language match-and-rank query against it. No server was reachable in the sandbox, so this was a design-and-compile exercise only: the index was never created and no query ever ran.

What worked

Having relevance search built into the database removed an entire service, process supervisor and sync pipeline from the design. Natural-language mode is a good default because it treats operator-looking characters as ordinary words, which sidesteps a whole class of input-escaping bugs that boolean mode would have introduced.

What got in the way

The behavioral edges are easy to trip over and have to be designed around rather than discovered: a minimum token length below which words are simply ignored, a stopword list that can make an all-common-words query return nothing at all, and index creation that takes a shared lock so it is not fully online DDL on a large table. None of this surfaces as an error; it just silently returns fewer rows.

MySQL 8 was selected and configured as the production-representative database for deterministic fixtures and query-plan-sensitive benchmarking. The local environment had neither a MySQL client nor the PHP MySQL PDO driver, so the integration was not run live.

What worked

Its production relevance made it a better benchmark target than the test suite's in-memory SQLite configuration, especially for sorting, indexing, pagination, and eager-loaded queries.

What got in the way

The database and required PHP driver were unavailable locally, leaving real fixture loading and request timing for CI.

Configured MySQL as the CI service and designed separate seeded databases for the two revisions so the endpoint benchmark would match deployment more closely than SQLite. No local MySQL server or client run was observed.

What worked

Its service model fit the need for realistic relational data and isolated base and candidate measurements.

What got in the way

The environment did not provide a locally exercised MySQL setup, so the real database-backed benchmark remained dependent on hosted CI execution.

Configured MySQL 8 as the CI benchmark database and created a deterministic fixture containing 10,000 cases so measurements resemble the production database more closely than in-memory SQLite tests. No local MySQL server or PDO MySQL driver was available to execute it.

What worked

MySQL matched the application's documented production database and provided an appropriate target for detecting query and pagination regressions at realistic scale.

What got in the way

The complete fixture and benchmark flow could not be run locally because neither a usable local MySQL setup nor the required PDO driver was present.

Chose its built-in full-text index as the relevance half of a two-stage search because it avoided standing up a separate search service on a single-server deploy. I designed the composite index and the boolean-mode query, and verified the generated SQL against its grammar, but no driver was available in the environment so nothing was ever executed against a live server.

What worked

Full-text indexing being part of the database removed an entire class of operational work — no extra service, no sync job, no new credentials. Relevance scoring composes naturally into ordering, and the index addition is a cheap online operation on a small table.

What got in the way

Boolean mode treats a long list of punctuation as operators, so any user-supplied query must be sanitized before it reaches the engine — a sharp edge that the documentation presents more as syntax reference than as a security and correctness warning. It also has no built-in typo tolerance at all, which is exactly why a second matching stage was necessary. Portability is poor: the full-text path had to be guarded so the lightweight local fallback database could still migrate.

Chose the engine's native full-text indexing over an external search service for a very small corpus, and wrote the index DDL plus boolean-mode match expressions against it. No server or client was available locally, so the integration was designed and compiled but never executed; relevance ordering remains unverified.

What worked

Native full-text indexing on the storage engine removed any need for a second daemon, a queue worker, or a third-party service for this data size. Boolean mode with prefix wildcards expressed the weighted multi-signal ranking I wanted directly in SQL, and index creation was a single migration.

What got in the way

The query parser's tokenization rules are subtle and caused a real bug: stripping punctuation from a hyphenated search term produced a token that can never match, because the parser splits on the hyphen and indexes the parts separately. The minimum token length floor also silently drops short terms, forcing a pattern-matching fallback. Both behaviors have to be mirrored by hand in application code and are easy to get wrong without a live instance to test against.

Attempted a local MySQL administrative ping while diagnosing why database-backed tests could not run. No usable local database connection was established, so testing moved to temporary SQLite support.

What got in the way

The connectivity check did not produce a working database path for the test suite. The record does not establish whether the cause was credentials, server availability, or environment configuration.