Messenger/docs/adr/0003-search-ILIKE.md

ADR 0003: Search via ILIKE instead of pg_trgm

• Status: accepted
• Date: 2026-09

Context

Message search must find substring matches across a user's conversations. pg_trgm + GIN gives indexed trigram search, but the extension is not installed on the target system and cannot be created by a non-superuser role (and adding it to the Docker image is an OS-level dependency).

Decision

Search with body ILIKE '%term%' restricted to the user's conversations, ordered by message id descending with a limit.

Consequences

• No extension or superuser requirement; works on any PostgreSQL install.
• Sequential scan is instant for thousands of messages (family scale).
• Scaling path documented: CREATE EXTENSION pg_trgm + GIN index with gin_trgm_ops, keeping the same SQL semantics.