How to Add Full-Text Search With Claude Code (Postgres)

Your app has a search box and it runs ILIKE '%term%' against three columns. It is slow, it cannot rank results, and it misses "running" when the user types "run". You do not need Elasticsearch to fix that. PostgreSQL has real full-text search built in, and Claude Code can wire the whole feature (migration, ranking query, API, and debounced UI) in one session.

What Full-Text Search Actually Needs

A working search feature has four moving parts, and it helps to name them before you ask Claude to build anything.

First, a tsvector column. This stores a preprocessed version of your text: lowercased, stemmed to word roots, with stop words like "the" and "and" removed. "Running" and "runs" both reduce to the lexeme "run", so a search for either finds both.

Second, a GIN index on that column. Without it, every search is a sequential scan over the whole table. With it, Postgres keeps an inverted index (lexeme to row) and lookups stay fast into the millions of rows.

Third, a ranking function. ts_rank scores each matching row by term frequency so the most relevant results sort to the top instead of coming back in insertion order.

Fourth, the plumbing: an API endpoint that runs the query, a UI that debounces keystrokes, and a guarantee that a user only ever sees rows they are allowed to see. That last part is where most hand-rolled search features leak data, so we handle it with row-level security rather than trusting the query.

The rest of this guide builds all four on top of PostgreSQL via Supabase, exposed through oRPC with Zod, and consumed by a React 19 client component in Next.js 16.

The Migration: A Generated tsvector Column

The cleanest way to store the search vector is a generated column. Postgres computes it from your source columns on every insert and update, so it can never drift out of sync and there are no triggers to maintain. The setweight calls tag the title as weight A and the body as weight B, which lets ranking favor a title match over a body match later.

Ask Claude to write this migration, and check that it uses generated always as (...) stored (not a plain column you have to populate yourself):

-- supabase/migrations/20260714000000_add_document_search.sql

alter table documents
  add column search_vector tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) stored;

create index documents_search_idx
  on documents
  using gin (search_vector);

Two details matter here. The coalesce(title, '') guards against null columns, because concatenating a null tsvector produces null and silently drops the row from search. And the 'english' config is a constant, which is what makes the whole expression immutable and therefore legal inside a generated column. If you pass a column as the config instead of a literal, the migration fails.

Run it against your local stack first with the Supabase CLI, then push:

supabase migration up
supabase db push

Weighting and Ranking

The weights you set in the migration only do something when you ask ts_rank to use them. You pass a weight array in the order (D, C, B, A), giving the numeric multiplier for each label. A title match (weight A) counts for much more than a body match (weight B) here.

This is the core query. websearch_to_tsquery parses the user's raw input the way a search engine does, so "quarterly report" -draft becomes an AND of "quarterly" and "report" excluding "draft", and you never have to sanitize the string yourself:

select
  id,
  title,
  ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vector,
          websearch_to_tsquery('english', 'quarterly report')) as rank
from documents
where search_vector @@ websearch_to_tsquery('english', 'quarterly report')
order by rank desc
limit 20;

The @@ operator is the match test: it returns true when the tsvector contains the tsquery. Putting the same websearch_to_tsquery call in both the where clause and the select looks redundant, but Postgres evaluates it once and the GIN index handles the filter. Only the 20 rows that survive get ranked.

websearch_to_tsquery is the function you want for a user-facing box because it never throws on malformed input. Its cousins plainto_tsquery and to_tsquery are stricter: to_tsquery will raise a syntax error if the user types a stray operator, which is not something you want happening on every keystroke.

Making Results RLS-Safe

Here is the part most search tutorials skip. If your app is multi-tenant, a search query that forgets its where org_id = ... clause leaks every tenant's data through the one endpoint that touches every row. Instead of trusting yourself to remember that filter, let row-level security enforce it.

Assume your table already has an RLS policy that scopes reads to the caller's organization:

alter table documents enable row level security;

create policy "members read their org documents"
on documents
for select
using ( org_id = (auth.jwt() ->> 'org_id')::uuid );

Now wrap the search in a database function marked security invoker. That flag means the function runs with the calling user's privileges, so the policy above applies to the function's query exactly as it would to a direct select. Add ts_headline while you are here to return a highlighted snippet with the matched terms wrapped in mark tags:

-- supabase/migrations/20260714000100_search_documents_fn.sql

create or replace function search_documents(search_query text)
returns table (id uuid, title text, snippet text, rank real)
language sql
stable
security invoker
set search_path = public
as $$
  select
    d.id,
    d.title,
    ts_headline(
      'english',
      d.body,
      websearch_to_tsquery('english', search_query),
      'StartSel=<mark>,StopSel=</mark>,MaxFragments=2,MaxWords=25,MinWords=8'
    ) as snippet,
    ts_rank('{0.1, 0.2, 0.4, 1.0}', d.search_vector,
            websearch_to_tsquery('english', search_query)) as rank
  from documents d
  where d.search_vector @@ websearch_to_tsquery('english', search_query)
  order by rank desc
  limit 20;
$$;

Because the function is security invoker and you call it through the user's authenticated session, a user physically cannot get back a row they could not already read. The authorization lives in one policy, not scattered across every query. That is the whole reason to route search through Postgres instead of reimplementing tenant checks in application code.

Exposing Search Through oRPC

oRPC gives you an end-to-end type-safe endpoint: the input is validated by Zod on the server, and the client gets the return type inferred for free. Define the procedure with an input schema, an output schema, and a handler. The handler creates a request-scoped Supabase server client (the one that carries the user's JWT, so RLS applies) and calls the function through .rpc().

The Zod input caps the query length so nobody sends a megabyte of text to the parser, and trims whitespace before it reaches the database:

// lib/orpc/search.ts
import { os } from '@orpc/server'
import { z } from 'zod'
import { createServerClient } from '@/lib/supabase/server'

const SearchResult = z.object({
  id: z.string(),
  title: z.string(),
  snippet: z.string(),
  rank: z.number(),
})

export const searchDocuments = os
  .input(z.object({ query: z.string().trim().min(1).max(100) }))
  .output(z.array(SearchResult))
  .handler(async ({ input }) => {
    const supabase = await createServerClient()

    const { data, error } = await supabase.rpc('search_documents', {
      search_query: input.query,
    })

    if (error) {
      throw new Error(`Search failed: ${error.message}`)
    }

    return data ?? []
  })

Collect your procedures into a router. This is the single object the client will mirror, so its shape is your API surface:

// lib/orpc/router.ts
import { searchDocuments } from './search'

export const router = {
  searchDocuments,
}

Mount the router at a route handler. In Next.js 16 the catch-all segment name goes in the folder, and RPCHandler takes the whole request and returns a Response. The context object is empty here because the Supabase client is created inside the handler, not injected:

// app/rpc/[[...rest]]/route.ts
import { RPCHandler } from '@orpc/server/fetch'
import { router } from '@/lib/orpc/router'

const handler = new RPCHandler(router)

async function handle(request: Request) {
  const { response } = await handler.handle(request, {
    prefix: '/rpc',
    context: {},
  })

  return response ?? new Response('Not found', { status: 404 })
}

export const GET = handle
export const POST = handle

The typed client mirrors the router type without importing any server code, so none of your database logic ships to the browser:

// lib/orpc/client.ts
import { createORPCClient } from '@orpc/client'
import { RPCLink } from '@orpc/client/fetch'
import type { RouterClient } from '@orpc/server'
import type { router } from './router'

const link = new RPCLink({
  url:
    (typeof window !== 'undefined' ? window.location.origin : '') + '/rpc',
})

export const client: RouterClient<typeof router> = createORPCClient(link)

The Debounced Search UI

Firing a request on every keystroke hammers your database and shows results for a query the user already changed. A debounce fixes both: wait until typing pauses for 250ms, then search. And because a slow request can resolve after a newer one, each request carries an AbortController so stale responses get cancelled instead of overwriting fresh ones.

This is a client component. The cleanup function in useEffect clears the pending timer and aborts the in-flight request every time the query changes, which is what makes the debounce and the cancellation work together:

// components/search-box.tsx
'use client'

import { useEffect, useState } from 'react'
import { client } from '@/lib/orpc/client'

type Result = {
  id: string
  title: string
  snippet: string
  rank: number
}

export function SearchBox() {
  const [query, setQuery] = useState('')
  const [results, setResults] = useState<Result[]>([])
  const [loading, setLoading] = useState(false)

  useEffect(() => {
    const trimmed = query.trim()

    if (trimmed.length === 0) {
      setResults([])
      setLoading(false)
      return
    }

    const controller = new AbortController()

    const timer = setTimeout(async () => {
      setLoading(true)
      try {
        const data = await client.searchDocuments(
          { query: trimmed },
          { signal: controller.signal },
        )
        setResults(data)
      } catch (err) {
        if (!controller.signal.aborted) {
          console.error(err)
        }
      } finally {
        if (!controller.signal.aborted) {
          setLoading(false)
        }
      }
    }, 250)

    return () => {
      clearTimeout(timer)
      controller.abort()
    }
  }, [query])

  return (
    <div className="mx-auto max-w-xl">
      <input
        type="search"
        value={query}
        onChange={(e) => setQuery(e.target.value)}
        placeholder="Search documents..."
        className="w-full rounded-lg border px-4 py-2"
      />

      {loading && <p className="mt-2 text-sm text-gray-500">Searching...</p>}

      <ul className="mt-4 space-y-3">
        {results.map((r) => (
          <li key={r.id} className="rounded-lg border p-3">
            <p className="font-medium">{r.title}</p>
            <p
              className="mt-1 text-sm text-gray-600"
              dangerouslySetInnerHTML={{ __html: r.snippet }}
            />
          </li>
        ))}
      </ul>

      {!loading && query.trim().length > 0 && results.length === 0 && (
        <p className="mt-4 text-sm text-gray-500">No results found.</p>
      )}
    </div>
  )
}

One note on the snippet render. ts_headline returns HTML with mark tags, so it needs dangerouslySetInnerHTML to show the highlight. That is safe here because the markup comes from your own database function with a fixed StartSel and StopSel, not from arbitrary user input. If your body column can contain user-authored HTML, sanitize the output before rendering it.

Adding It to a Page

Drop the search box into any Server Component. The component itself is a client island, so the page around it stays a Server Component and renders instantly:

// app/search/page.tsx
import { SearchBox } from '@/components/search-box'

export default function SearchPage() {
  return (
    <main className="py-12">
      <h1 className="mb-6 text-center text-2xl font-bold">Search</h1>
      <SearchBox />
    </main>
  )
}

If you want the search term to live in the URL so results are shareable, read it from searchParams, which is a Promise in Next.js 16 and must be awaited:

// app/search/page.tsx
import { SearchBox } from '@/components/search-box'

interface PageProps {
  searchParams: Promise<{ q?: string }>
}

export default async function SearchPage({ searchParams }: PageProps) {
  const { q } = await searchParams

  return (
    <main className="py-12">
      <h1 className="mb-6 text-center text-2xl font-bold">Search</h1>
      <SearchBox initialQuery={q ?? ''} />
    </main>
  )
}

Wire initialQuery into the component's useState default and push the query into the URL with router.replace on change if you go this route. Claude can add that in a follow-up prompt once the base feature works.

Quality Gates Before You Ship

Two commands catch the mistakes that this feature is prone to: an untyped .rpc() return, a Server Action that leaks the service-role client, a dangerouslySetInnerHTML type mismatch.

Type check with zero errors, then confirm the build:

npx tsc --noEmit
npm run build

Ask Claude to run both and fix what breaks. The type check is where a mismatch between your Zod output schema and the actual search_documents return columns surfaces, before a user ever sees a broken result card. If you renamed a column in the SQL function but not in the SearchResult schema, this is the step that catches it.

One more thing worth verifying by hand: log in as a user in one tenant and search for a term you know exists only in another tenant's data. You should get zero results. If you get a hit, your function is not security invoker or you are calling it with a service-role client that bypasses RLS. Fix that before anything ships.

Where the Code Kit Fits

Claude Code will happily build every piece above in one session. The gap is consistency: remembering to run the type check, catching the RLS leak, keeping the Zod schema and the SQL columns in lockstep across a dozen features. The $29 Code Kit is a harness that sits on top of Claude Code and enforces that loop for you (plan, build, evaluate, test), with quality gates that require zero type errors, zero lint errors, and a clean build before a feature is considered done. It is a one-time $29 purchase with no subscription. Claude Code itself runs on your own paid Anthropic plan, which you bring. The harness just makes sure the search feature you shipped today does not quietly break the next feature you build on Friday.

Posted by @speedy_devv