Building search across 130,000 trademark records

Problem

Search in NepalIPMS is not a convenience feature. Paralegals run queries while clients are on the phone: conflict checks, application lookups, class-filtered portfolio views. If a query takes three seconds, the conversation stalls. Our target was sub-200ms for typical lookups on a register of more than 130,000 rows — without standing up a dedicated Elasticsearch cluster from Kathmandu.

Context

Trademark data resists generic full-text treatment. Marks mix Nepali and English. Application numbers arrive with and without slashes. Punctuation sometimes carries legal meaning. Class filters must combine cleanly with free text. The register grew from Department of Industry bulletins and firm-maintained sheets — messy at the edges, authoritative at the core.

Constraints

No dedicated search cluster. Mixed Nepali/English marks. Exact application lookups must not be polluted by fuzzy noise. Latency budget is a human on the phone — not a synthetic benchmark.

Architecture

D1 FTS5 for free text; composite indexes for status, class, owner. Exact application path separate from mark search. KV short-TTL cache for hot portfolios. See why exact matching leads fuzzy.

Implementation

Normalized aggressively at ingest. Classified query patterns early — exact application, mark tokens, owner portfolio — and tuned each path. List views fetch only needed fields. Slow queries logged in production; indexes follow desk behavior.

Result

Typical lookups stay within the latency budget practitioners feel as instant. Search became daily infrastructure across firms running live matters.

What I Learned

130,000 rows is not “big data” — but naive LIKE scans fail, and well-indexed SQLite at the edge wins. Match architecture to the human waiting on the phone.