The Search Field That Did Nothing: SQLite FTS5 for a 50K-Row Aggregator

The tool that fails silently is worse than the tool that doesn’t exist. A search field implies something functional is happening behind it.

Dev Signal is an RSS aggregator I built to manage information flow across concurrent technical contexts. I run client engagements and personal projects simultaneously, and staying current through informal reading stopped scaling years ago. The system pulls from 17 sources — engineering blogs, AI company posts, practitioner newsletters, podcast transcripts — into a local SQLite database via a Next.js 14 app deployed on Railway. The premise is simple: when a client conversation needs background, when I half-remember a specific argument from last month, when I need to understand a tradeoff quickly, the search field surfaces it. When search fails, those 50,000 rows might as well not exist.

The ingestion pipeline ran without problems for six months. The retrieval layer failed silently while I was paying attention to everything else.

Accumulation without retrieval

The original search was LIKE '%keyword%' — a full-table scan returning results in insertion order. At 5,000 rows it was workable. At 50,000 it had three compounding failures: no relevance ranking (results arrived in ingestion order), no phrasing variation (“indexing” wouldn’t match “indexes”), and enough cold-read latency that the page appeared broken. A teammate looked at the search field and asked what it was actually doing. I didn’t have a good answer.

The obvious candidates were Algolia and Elasticsearch. I opened Algolia’s docs: API key management, billing dashboard, a data sync pipeline to build and maintain — a whole operational surface for a feature that should take an afternoon. Elasticsearch next, same concerns, larger footprint. I closed both tabs. Dev Signal has five users. The database fits in memory with a 64MB WAL cache. Those services solve the right problem at the wrong scale — they’d relocate the maintenance burden into external infrastructure without removing it. What I actually needed was to read the SQLite documentation I’d been skipping past for three years.

SQLite ships with FTS5. I knew this in the abstract. I’d never used it.

The choice that eliminated the infrastructure problem

FTS5 is a virtual table module built into SQLite — no additional package, no external process, no network round-trip, no credentials to rotate. One statement creates an indexed shadow table with BM25 relevance ranking built in:

CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts
USING fts5(
  title,
  content,
  title_zh,
  content_zh,
  content='articles',
  content_rowid='id'
);

content='articles' designates this a content table. FTS5 stores only the inverted index structure — raw text stays in articles. Storage stays flat as the corpus grows. Initial population is a single INSERT INTO articles_fts SELECT ... FROM articles — 50,000 rows indexed in under a second. After that, triggers keep it synchronized.

Three triggers, not a pipeline

A content table doesn’t self-synchronize when the backing table changes. Three triggers handle it:

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, content, title_zh, content_zh)
  VALUES (new.id, new.title, new.content, new.title_zh, new.content_zh);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  DELETE FROM articles_fts WHERE rowid = old.id;
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  UPDATE articles_fts
  SET title = new.title,
      content = new.content,
      title_zh = new.title_zh,
      content_zh = new.content_zh
  WHERE rowid = old.id;
END;

Verbose SQL. Written once. The real comparison isn’t “triggers vs. a proper search service” — it’s “triggers vs. credential-rotation tickets on a recurring calendar, plus rate limit monitoring, plus a data sync pipeline that drifts at the worst possible moment.” Three SQL blocks, committed and never touched again.

BM25 scores are negative — this catches everyone once

The FTS5 bm25() function scores matches for relevance ranking. One thing the documentation buries: the scores are negative. A score of -5.2 is more relevant than -1.1. ORDER BY bm25(articles_fts) without DESC is correct — most-negative sorts first. I spent twenty minutes staring at reversed results before reading the documentation.

const search = db.prepare(`
  SELECT
    a.id,
    a.title,
    a.published_at,
    a.source,
    bm25(articles_fts) AS relevance_score
  FROM articles_fts
  JOIN articles a ON a.id = articles_fts.rowid
  WHERE articles_fts MATCH ?
  ORDER BY relevance_score
  LIMIT 20
`);

In the production searchArticles implementation, results actually sort by published_at DESC rather than by BM25 score. For a news aggregator this is a deliberate product decision — when I search “observability tooling,” the useful result is last week’s discussion, not the most corpus-wide BM25-relevant article from six months ago. Recency is itself a relevance signal in this context. BM25 ordering remains available for ad hoc queries where freshness matters less than topic centrality.

FTS5 has no fuzzy matching. “Indexing” and “indexes” are different tokens unless you use the prefix operator (index*). For an English technical corpus with consistent terminology, acceptable. For general prose, the ceiling appears quickly.

WAL mode is not optional for concurrent reads

Next.js API routes fire in parallel. A single page load can hit the database with multiple simultaneous queries. SQLite’s default journal mode serializes everything — a write blocks all reads until it completes. Without WAL, concurrent requests queue visibly.

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;  -- 64MB page cache

Set once at connection initialization. WAL separates reads from writes at the page level. synchronous=NORMAL is the standard WAL tradeoff: durable against OS crashes, not against a power loss mid-write. For a single-node aggregator on Railway, acceptable. Not setting this on a Next.js app is the kind of omission that produces mysterious latency spikes under any non-trivial load.

The CJK ceiling

Dev Signal stores DeepL-translated Chinese in title_zh and content_zh. The pipeline runs DeepL’s translation API against English content at ingestion and writes the result into the bilingual fields. FTS5’s default tokenizer splits on whitespace — correct for Latin scripts, non-functional for CJK, where word boundaries don’t correspond to spaces. A Chinese sentence becomes a single unsearchable token.

For a personal tool with five users, this is a documented limitation rather than a blocking defect. A search query that happens to match an exact phrase in the translated text will find results; partial-word matches won’t. A consumer product with real Chinese search requirements needs a CJK-aware tokenizer or a different engine entirely. The ceiling is visible; knowing what you’re working around is part of what makes the tradeoff acceptable.

For a tool at this scale, choosing Elasticsearch isn’t a technology decision — it’s a commitment to own external infrastructure indefinitely. FTS5 already lived in the database I was already running. The work was never about which search engine to pick. It was about actually reading the documentation.