TLDR: If your database identifies an entity through a relationship, your search query has to traverse that relationship — or it will return nothing for the most natural thing a user would type.

The Setup

I'm building a custom practice management system for my business partner — a law firm client, solo attorney in Salt Lake City.

In a law firm, a matter is a legal case or engagement. It has a name.

And that name looks like this: "REDEEMING YOUR mark." Or "Entity Formation — Phase 2."

Not "my consulting practice — trademark."

Because the client's name lives in a separate client field — a linked record pointing to a Contacts table.

The Problem

My business partner types a client name into the search bar.

Zero results for matters.

Every matter for that client — right there in the database — completely invisible.

What I Tried First

My first instinct was case sensitivity. Maybe LIKE vs ILIKE. Maybe a whitespace trim somewhere upstream.

Nope.

The query was fine. It was looking in the wrong column entirely.

The search was scanning matters.title.

matters.title says "REDEEMING YOUR mark."

It does NOT say the client's name.

The Fix That Actually Worked

The matters table links to contacts through a client_id foreign key.

To match matters by client name, the search has to JOIN through that relationship — not just scan its own row.

SELECT m.*
FROM matters m
LEFT JOIN contacts c ON c.id = m.client_id
WHERE m.title ILIKE '%' || :query || '%'
   OR c.name   ILIKE '%' || :query || '%'

Now the client's name surfaces every matter where they are the linked client.

There was a second wrinkle though.

Multi-word names — "I" — still didn't match after the join. The fix: tokenize the query, split on spaces, AND the tokens together so each word has to hit.

Two commits on the same day. Two layers of the same root cause.

(My business partner runs a solo firm. "I" was one of my own test contacts. It broke immediately. Caught it early — that's the whole point of building the thing yourself.)

Why This Matters

Search has to follow your data model.

If an entity's identity lives in a relationship — matter belongs to client — your search box needs to traverse that relationship. Scanning the row's own columns isn't enough.

This pattern shows up EVERYWHERE once you see it: searching orders by customer name, tickets by company, invoices by project. The row you're looking for isn't always the row that holds the display name.

The question I now ask before writing any search query: where does the identity of this thing actually live in the schema? If the answer is "in a linked record," your WHERE clause has to go there too.

P.S. The conflict-check system in this same app has the exact same shape — flag a new matter if the client name matches an opposing party on a past matter. Same lesson, higher stakes.