Search Index Worker

Project roadmap

1. 01

   Design the pipeline

   ~2h

   Write DESIGN.md: the outbox table (id, entity type and id, change type, version or timestamp, created at and processed at), how events are written in the same transaction as the change (with model events or an observer), the search documents table (entity, title, body, a tsvector column, filters and the version indexed), how workers claim events (in order, several at a time with SKIP LOCKED), and how a full reindex swaps to a new index. Done when: DESIGN.md covers events, processing, ordering and reindexing.

2. 02

   Record changes in an outbox

   ~2h

   Create the outbox and search documents tables with migrations. Add a trait for indexed models that writes an outbox row on created, updated and deleted, inside the same database transaction as the change (use DB::afterCommit where needed). Done when: every change to an indexed model creates exactly one outbox row, and a rolled-back change creates none.

3. 03

   Process events with queued workers

   ~3h

   Write a dispatcher command that claims batches of unprocessed outbox rows with SELECT ... FOR UPDATE SKIP LOCKED and dispatches jobs. Each job loads the entity's current data (or handles its deletion), builds the search document, and upserts it only if its version is newer than the one already indexed, so events processed twice or out of order never overwrite newer data. Mark rows processed after success, and retry failures with backoff. Done when: changes appear in the search index within seconds, even with several workers running.

4. 04

   Build the search

   ~2h

   Store a weighted tsvector (title weighted higher than body) with a GIN index, and build a search endpoint using websearch_to_tsquery, ts_rank for ordering, ts_headline for highlighted snippets, filters (type and date) and pagination. Done when: searches return relevant, highlighted results that reflect recent changes.

5. 05

   Reindex without downtime

   ~2h

   Add an artisan command that rebuilds the whole index into a new table in batches (using chunkById), while the workers keep applying new events to both the old and the new table, then swaps the tables in a transaction and removes the old one. Done when: a full reindex completes while changes keep flowing, and searches never return errors during the swap.

6. 06

   Monitor the pipeline

   ~1.5h

   Expose a status page or command showing the outbox backlog, the age of the oldest unprocessed event, events processed per minute and failed jobs. Warn when the lag grows beyond a threshold. Clean up processed outbox rows after a few days. Done when: the status shows the pipeline's health, and old rows are cleaned up.

7. 07

   Test it

   ~2.5h

   Write feature tests with a PostgreSQL test database: outbox rows written on commit and not on rollback, documents built and deleted correctly, older events never overwriting newer documents, parallel workers not processing the same event, search ranking and snippets, and reindexing while changes happen. Done when: php artisan test passes.

Resources

• ArticleTransactional outbox pattern
• DocsLaravel: queues
• DocsPostgreSQL: full-text search
• DocsPostgreSQL: SELECT ... FOR UPDATE SKIP LOCKED
• DocsLaravel: Eloquent events and observers