Search that finds the message somebody half remembers

People search a chat history for a fragment they half remember, a number, or a person. Covering all three, tolerantly, is what makes a shared inbox worth more than a phone.

Why substring search stops working

LIKE '%tyre%' cannot use a B-tree index, because the index is ordered by prefix and there is no prefix. Every query becomes a sequential scan. It works beautifully in development and falls over the first month a real account is on it.

Full text, with the phone number as a special case

-- Postgres: a generated column, indexed once.
ALTER TABLE messages ADD COLUMN searchable tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', coalesce(text, ''))) STORED;

CREATE INDEX messages_search ON messages USING GIN (searchable);

'simple' rather than 'english' is deliberate where customers write in more than one language: an English stemmer applied to Swahili does damage without adding anything.

What people actually type

• A name, spelled approximately. Trigram similarity on the contact name catches Wanjiku for Wanjiru.
• A number, in any format. Detect and normalise.
• Two words that were near each other. Full text handles it; substring does not.
• A word that is in a photo. It is not in your index and you should say so rather than returning nothing - "no messages match; 12 images in this conversation" is a better answer than silence.

Scope it to what they can see

Search is the easiest place to leak. A member restricted to one number must not find messages from another, and the filter belongs in the query rather than in the code that renders the results - one forgotten branch and a search box becomes an export tool.

Rank by recency, not by relevance

Somebody searching a chat history is nearly always looking for something recent. A perfectly relevant match from 2023 above the one from Tuesday is a search that technically worked.