Keep search indexes in step with their source

How to do it

The index is a copy, as Name the source of truth says. Write each index update to an outbox in the same transaction as the change, as Send side effects through an outbox describes. A worker then applies it. Index on these change events, not only in a nightly job. Delete entries by record ID when a record is deleted or unpublished. Store the source's last-modified time with each entry, and show it with AI answers. Run a full rebuild or a compare on a schedule to catch missed events.

Examples

SQL example

SELECT c.document_id FROM chunks c LEFT JOIN documents d ON d.id = c.document_id
WHERE d.id IS NULL OR (c.source_updated_at < d.updated_at AND d.updated_at < now() - interval '15 minutes');
-- PASS when no rows are returned

TypeScript example

documents.on('deleted', async ({ id }) => vectorIndex.deleteWhere({ documentId: id }));

Python example

@documents.on("deleted")
def remove_from_index(event: DocumentDeleted) -> None:
    vector_index.delete_where(document_id=event.id)

How to check

Delete a test record at the source. Within the set time, a search for its unique phrase returns nothing. A nightly job compares record IDs in the source and the index and alerts on any difference.

Sources

• Microsoft Azure Architecture Center on the Transactional Outbox pattern

Related rules

• DAT-44 Name the source of truth in Data and databases
• STK-33 Use PostgreSQL search first and OpenSearch when it falls short in Software choices
• EVT-6 Send side effects through an outbox in Integration and events
• SRH-8 Remove erased records from every index in Search