TL;DR: If your database splits names across columns (first_name / last_name), a full-string ILIKE against either column will never match a two-word search. Tokenize first. Every word becomes its own filter.

The Setup

I'm building a custom practice management system for my business partner's law firm (Supabase + Next.js — replacing their Airtable setup). The app has a contacts list and a matters list, and both needed a search bar.

Straightforward, right?

The Wall

my business partner typed a client's full name into the contacts search.

Zero results.

That client was absolutely in the database. So something was broken, and it had been broken since day one.

What the Old Code Was Doing

The query in src/lib/data.ts was doing something like this:

first_name ILIKE '%[First] [Last]%'
OR last_name ILIKE '%[First] [Last]%'
OR entity_name ILIKE '%[First] [Last]%'

See the problem?

The first_name column contains "[First]". The last_name column contains "[Last]". But the full string "[First] [Last]" doesn't live in either one.

So all three conditions are false. Zero results. Every time.

The Fix That Actually Worked

I wrote a small helper called tokenizedNameSearchClauses.

It takes the search term, splits it on whitespace, then returns one OR-group per token — each token checked against every name column. The caller chains those .or() calls on the Supabase query, which ANDs them together.

So a two-word name like "[First] [Last]" becomes:

  • Token 1: first_name ILIKE '%[First]%' OR last_name ILIKE '%[First]%' OR entity_name ILIKE '%[First]%'
  • Token 2: first_name ILIKE '%[Last]%' OR last_name ILIKE '%[Last]%' OR entity_name ILIKE '%[Last]%'

Both must be true. Every token has to match somewhere in the name fields. Order doesn't matter. Partial is fine.

The client shows up.

There Was a Second Bug

The matters search had a completely separate problem — it only searched matter titles.

So typing part of a client's name returned nothing for some matters — even though one of their older matters had the client's name right in the title.

Wait — the client name IS in the title there. But what about their other matters? The newer ones where the title is just the legal issue and the client name lives in a linked contact row?

Yeah. Totally invisible to search.

The fix: union the title search with a contact lookup — find any matter whose linked client, opposing party, or related party has a name matching the query. Same tokenized logic applied to the contact subquery.

Why This Matters

Every name-search system that splits names across columns needs tokenized search. If you're storing first_name and last_name separately and running a full-string ILIKE against each column, you've already shipped this bug. You just haven't noticed yet.

The fix is small — 22 lines of real change in one file. But it's the kind of thing that silently erodes trust in a tool. Users type a name they know is in the system and get nothing. They assume the data is wrong or lost. They stop trusting the search. They stop using the search.

Tokenize. Every word is its own filter. Problem gone.