Full-Text Search with Claude Code: MeiliSearch and PostgreSQL tsvector

#claudecode #node #meilisearch #postgres

SQL LIKE '%keyword%' = full table scan. At 100k records it's slow. At 1M records it's unusable. Claude Code generates fast full-text search by reading your CLAUDE.md and picking the right engine for your scale.

CLAUDE.md Search Engine Selection Rules

## Search Strategy
- Small dataset (under 100k records): PostgreSQL tsvector
- Medium-large + Japanese text: MeiliSearch
- Large managed (over 10M): Algolia
- Always sync index on DB write (non-blocking)
- Always delete from index on record deletion
- Search must not block the write transaction

Claude Code reads these rules and generates the right implementation automatically.

MeiliSearch Setup Code

Prompt:

Generate full-text search for articles using MeiliSearch.
Follow CLAUDE.md search strategy rules.

Claude Code generates:

import { MeiliSearch } from 'meilisearch';

const client = new MeiliSearch({
  host: process.env.MEILISEARCH_HOST!,
  apiKey: process.env.MEILISEARCH_API_KEY!,
});

async function setupSearchIndex() {
  const index = client.index('articles');

  await index.updateSettings({
    searchableAttributes: ['title', 'body', 'tags', 'authorName'],
    filterableAttributes: ['tenantId', 'published', 'category'],
    sortableAttributes: ['createdAt', 'viewCount'],
  });

  return index;
}

async function indexArticle(article: Article) {
  const index = client.index('articles');
  await index.addDocuments([{
    id: article.id,
    title: article.title,
    body: article.body,
    tags: article.tags,
    authorName: article.author.name,
    tenantId: article.tenantId,
    published: article.published,
    category: article.category,
    createdAt: article.createdAt.toISOString(),
    viewCount: article.viewCount,
  }]);
}

async function removeArticleFromIndex(articleId: string) {
  const index = client.index('articles');
  await index.deleteDocument(articleId);
}

async function searchArticles(query: string, options: SearchOptions) {
  const index = client.index('articles');

  return index.search(query, {
    filter: [`tenantId = ${options.tenantId}`, 'published = true'],
    attributesToHighlight: ['title', 'body'],
    highlightPreTag: '<mark>',
    highlightPostTag: '</mark>',
    limit: options.limit ?? 20,
    offset: options.offset ?? 0,
    sort: options.sort ? [options.sort] : ['createdAt:desc'],
    // Fuzzy matching: 1 typo allowed for words 4+ chars
    typoTolerance: { enabled: true, minWordSizeForTypos: { oneTypo: 4, twoTypos: 8 } },
  });
}

MeiliSearch handles Japanese tokenization, fuzzy matching, and highlighted snippets out of the box.

Non-Blocking Index Sync

The key pattern: index updates happen after the DB write and never block the response:

async function createArticle(data: CreateArticleDto) {
  // 1. Write to DB (blocking — must succeed)
  const article = await articleRepository.create(data);

  // 2. Sync to search index (non-blocking — failure is logged, not thrown)
  indexArticle(article).catch(err => {
    logger.error('Search index sync failed', { articleId: article.id, err });
    // Queue for retry if needed — but don't fail the request
  });

  return article; // Return immediately without waiting for index
}

async function deleteArticle(articleId: string) {
  await articleRepository.delete(articleId);

  // Remove from index non-blocking
  removeArticleFromIndex(articleId).catch(err => {
    logger.error('Search index removal failed', { articleId, err });
  });
}

A MeiliSearch outage never causes your write API to fail.

PostgreSQL tsvector (Simple Cases)

For smaller datasets or when you want to avoid an extra service:

-- Add search vector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);

-- Auto-update trigger
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    to_tsvector('japanese', coalesce(NEW.title, '')) ||
    to_tsvector('japanese', coalesce(NEW.body, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_vector_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION update_search_vector();

Query with Prisma:

async function searchWithTsvector(query: string, tenantId: string) {
  return prisma.$queryRaw<Article[]>`
    SELECT id, title, body,
      ts_rank(search_vector, query) AS rank,
      ts_headline('japanese', body, query) AS snippet
    FROM articles,
      plainto_tsquery('japanese', ${query}) query
    WHERE search_vector @@ query
      AND tenant_id = ${tenantId}::uuid
      AND published = true
    ORDER BY rank DESC
    LIMIT 20
  `;
}

PostgreSQL tsvector gives you Japanese search and ranked results with zero extra infrastructure.

Choosing the Right Engine

Scenario	Engine	Why
Internal tool, < 50k rows	PostgreSQL tsvector	Zero extra service
SaaS, Japanese, fuzzy	MeiliSearch	Typo tolerance, highlights
Global scale, budget	Algolia	Managed, fast globally