Adding Typo Tolerant Video Title Search With OpenSearch and SQLite FTS5

Our search log had one row I could not stop staring at: strangr things sesaon 5 trailr, submitted 412 times in a single month across eight regions, returning exactly zero results. Not a thin result set — zero. The correctly spelled version of the same query returned 340 videos with a 71% click-through rate. Somewhere in that gap sat a few thousand sessions a month that landed on an empty state and bounced.

I run TrendVidStream, a streaming-discovery site that ingests trending video metadata from eight regions on a staggered cron and serves it from a single SQLite file on shared LiteSpeed hosting, deployed by FTP. Search was SQLite FTS5 and had been for two years. FTS5 is genuinely excellent at what it does. What it does not do is edit distance, and video search is drowning in edit distance: mobile keyboards, transliterated titles, and half the world typing English show names phonetically.

This is what I actually built, what it cost, and the three things I got wrong first.

What FTS5 gives you and where it stops

Here is the search path we shipped for two years. It is not bad code. It is just structurally incapable of the thing I needed.

<?php
declare(strict_types=1);

// app/Search/Fts5Search.php — the version that ran for two years.
final class Fts5Search
{
    public function __construct(private readonly PDO $db) {}

    /** @return list<array{video_id:string,title:string,score:float}> */
    public function search(string $q, int $limit = 40): array
    {
        // FTS5 MATCH is its own grammar; a stray quote kills the statement.
        $terms = preg_split('/\s+/u', trim($q), -1, PREG_SPLIT_NO_EMPTY) ?: [];
        if ($terms === []) {
            return [];
        }

        $match = implode(' ', array_map(
            static fn (string $t): string => '"' . str_replace('"', '""', $t) . '"*',
            $terms
        ));

        $stmt = $this->db->prepare(
            'SELECT v.video_id, v.title, bm25(video_fts, 8.0, 1.0) AS score
               FROM video_fts
               JOIN videos v ON v.rowid = video_fts.rowid
              WHERE video_fts MATCH :m
           ORDER BY score
              LIMIT :lim'
        );
        $stmt->bindValue(':m', $match);
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

// search('season') -> 340 rows in 9ms
// search('sesaon') -> 0 rows. Every time. Forever.

The trailing * gives prefix matching, so seas finds season. That covers people who stop typing. It does nothing for people who type the wrong characters.

I tried the two obvious escapes before renting anything:

• The trigram tokenizer. SQLite ships one, and it enables substring matching and LIKE acceleration. It is not fuzzy matching. season produces the trigrams sea eas aso son; sesaon produces ses esa sao aon. Overlap: zero. A transposition destroys every trigram that spans it, which is exactly the typo class I needed to survive.
• spellfix1. This is the right tool in the SQLite family — it does edit distance and phonetic matching. It is also a loadable extension, and our hosting does not permit sqlite3_load_extension. Dead end on shared hosting, and I was not moving four sites to a VPS to fix search.

So the choice narrowed to: put a real search engine next to the app, or accept the zero-result rate. I went with OpenSearch, single node, and kept FTS5 in place as the fallback. That second half turned out to matter more than the first.

Sizing it before spending money

A thing I have learned the expensive way: measure the corpus before you pick the instance.

• 1.24M video rows across eight regions, deduplicated to 890K unique videos
• Average title length 61 characters; channel names average 18
• Only title, channel, regions, category_id, view_count, published_at need to be searchable or filterable
• Everything else — descriptions, thumbnails, duration — stays in SQLite and gets joined back after we have IDs

That last bullet is the one that keeps the bill small. OpenSearch is an ID resolver here, not a document store. It answers "which 40 video IDs, in what order" and then PHP hydrates those 40 rows from SQLite by primary key, which SQLite does in well under a millisecond. The index ends up around 340MB with one shard and zero replicas, which fits comfortably on a 2GB VPS running one node.

Network placement matters more than instance size. Our web hosts and the OpenSearch node are in the same European metro; cross-continent would have added 90ms to every keystroke on the typeahead endpoint. Access is HTTPS with basic auth plus an IP allowlist for the four origin IPs. There is no public route to port 9200, and there never will be.

The mapping is the whole design

Everything about typo tolerance is decided at index time. Fuzziness at query time only works if the analyzed tokens are shaped to make it work.

<?php
declare(strict_types=1);

// tools/search/create_index.php — run manually on every mapping change.
// Versioned index name + alias, so a reindex is an atomic pointer swap.
$version = 'videos_v3';

$body = [
    'settings' => [
        'index' => [
            'number_of_shards'   => 1,
            'number_of_replicas' => 0,
            'max_ngram_diff'     => 13,
            'refresh_interval'   => '30s', // we are not a live feed
        ],
        'analysis' => [
            'normalizer' => [
                'raw_lower' => [
                    'type'   => 'custom',
                    'filter' => ['lowercase', 'asciifolding'],
                ],
            ],
            'filter' => [
                'edge_2_15' => ['type' => 'edge_ngram', 'min_gram' => 2, 'max_gram' => 15],
            ],
            'analyzer' => [
                'title_folded' => [
                    'tokenizer' => 'standard',
                    'filter'    => ['lowercase', 'asciifolding'],
                ],
                'title_prefix' => [
                    'tokenizer' => 'standard',
                    'filter'    => ['lowercase', 'asciifolding', 'edge_2_15'],
                ],
            ],
        ],
    ],
    'mappings' => [
        'properties' => [
            'title' => [
                'type'     => 'text',
                'analyzer' => 'title_folded',
                'fields'   => [
                    // typeahead only; search_analyzer must NOT re-ngram the query
                    'prefix' => [
                        'type'            => 'text',
                        'analyzer'        => 'title_prefix',
                        'search_analyzer' => 'title_folded',
                    ],
                    'raw' => ['type' => 'keyword', 'normalizer' => 'raw_lower'],
                ],
            ],
            'channel' => [
                'type'     => 'text',
                'analyzer' => 'title_folded',
                'fields'   => ['raw' => ['type' => 'keyword', 'normalizer' => 'raw_lower']],
            ],
            'regions'      => ['type' => 'keyword'],
            'category_id'  => ['type' => 'keyword'],
            'view_count'   => ['type' => 'long'],
            'published_at' => ['type' => 'date'],
        ],
    ],
];

$ch = curl_init(getenv('OS_HOST') . '/' . $version);
curl_setopt_array($ch, [
    CURLOPT_CUSTOMREQUEST  => 'PUT',
    CURLOPT_POSTFIELDS     => json_encode($body, JSON_THROW_ON_ERROR),
    CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
    CURLOPT_USERPWD        => getenv('OS_USER') . ':' . getenv('OS_PASS'),
    CURLOPT_RETURNTRANSFER => true,
]);

echo curl_exec($ch), PHP_EOL;

Three decisions in there are worth defending:

• asciifolding on both the analyzer and the keyword normalizer. Our regions include titles with é, ü, ı, and ñ. Users type the ASCII form roughly 80% of the time. Folding at index time makes pokemon find Pokémon without any fuzziness budget being spent on the accent.
• A separate prefix subfield with a different search_analyzer. If you edge-ngram the query as well as the document, sea expands to s, se, sea and matches essentially everything. Index-side ngrams, query-side plain tokens. This bites people constantly.
• Never run fuzzy queries against the ngram field. Fuzziness over edge-ngrams is a combinatorial explosion and it will find you in production, not in your test set. The ngram field is for typeahead prefix matching only; typo tolerance lives on the plain title field.

The alias is the other half. The index is videos_v3; the app only ever talks to the alias videos. Because our deploy is FTP file sync with no atomic release directory, I cannot coordinate "new code + new index" as one switch. An alias means the reindex finishes, the alias flips in one API call, and the old index gets deleted a day later if nothing screams.

Bulk indexing out of SQLite without blocking the site

The indexer runs as the last step of the existing multi-region cron, after the fetch job has written new videos. It is Python because opensearch-py has a bulk helper that handles chunking and retries and I did not want to reimplement that in PHP.

The critical detail is the read-only URI connection. The cron and the live site share one SQLite file; an indexer that takes a write lock for six minutes is an outage.

#!/usr/bin/env python3
"""tools/search/reindex.py — incremental sync SQLite -> OpenSearch.

Runs as the final step of the regional fetch cron. Read-only on SQLite:
the live site keeps serving while this runs.
"""
import os
import sqlite3
import sys
from opensearchpy import OpenSearch
from opensearchpy.helpers import bulk

DB = os.environ['SQLITE_PATH']
ALIAS = 'videos'
CHECKPOINT = os.path.join(os.path.dirname(DB), '.search_checkpoint')


def read_checkpoint() -> int:
    try:
        with open(CHECKPOINT) as fh:
            return int(fh.read().strip())
    except (OSError, ValueError):
        return 0


def connect_sqlite() -> sqlite3.Connection:
    # mode=ro is the point: no write lock, no contention with the web tier.
    conn = sqlite3.connect(f'file:{DB}?mode=ro', uri=True, timeout=15)
    conn.row_factory = sqlite3.Row
    return conn


def docs(conn: sqlite3.Connection, since: int):
    rows = conn.execute(
        '''SELECT v.video_id, v.title, v.channel_title, v.category_id,
                  v.view_count, v.published_at, v.updated_at,
                  group_concat(DISTINCT r.region_code) AS regions
             FROM videos v
             LEFT JOIN video_regions r ON r.video_id = v.video_id
            WHERE v.updated_at > ?
            GROUP BY v.video_id
            ORDER BY v.updated_at ASC''',
        (since,),
    )

    newest = since
    for row in rows:
        newest = max(newest, row['updated_at'])
        yield {
            '_op_type': 'index',
            '_index': ALIAS,
            '_id': row['video_id'],       # idempotent: reruns overwrite, never duplicate
            '_source': {
                'title': row['title'],
                'channel': row['channel_title'] or '',
                'category_id': str(row['category_id'] or ''),
                'regions': (row['regions'] or '').split(',') if row['regions'] else [],
                'view_count': row['view_count'] or 0,
                'published_at': row['published_at'],
            },
        }

    yield {'_checkpoint': newest}  # sentinel, filtered below


def main() -> int:
    client = OpenSearch(
        hosts=[os.environ['OS_HOST']],
        http_auth=(os.environ['OS_USER'], os.environ['OS_PASS']),
        use_ssl=True,
        timeout=30,
        max_retries=3,
        retry_on_timeout=True,
    )

    since = read_checkpoint()
    newest = since
    conn = connect_sqlite()

    def stream():
        nonlocal newest
        for doc in docs(conn, since):
            if '_checkpoint' in doc:
                newest = doc['_checkpoint']
                continue
            yield doc

    ok, errors = bulk(client, stream(), chunk_size=1000, raise_on_error=False)
    print(f'indexed={ok} errors={len(errors)} since={since} -> {newest}')

    if errors:
        for err in errors[:5]:
            print(err, file=sys.stderr)
        return 1  # do NOT advance the checkpoint on partial failure

    with open(CHECKPOINT, 'w') as fh:
        fh.write(str(newest))
    return 0


if __name__ == '__main__':
    raise SystemExit(main())

Two properties I care about here. First, _id is the video ID, so the operation is idempotent — a cron that runs twice because of an overlapping schedule produces the same index, not duplicates. Second, the checkpoint only advances on a clean run. A partial failure means the next run reprocesses the overlap, which is cheap and correct. The opposite (advance always, lose documents on error) is the bug you find three months later when a video nobody searches for turns out to be missing.

A full rebuild of 890K documents takes about 4 minutes. Incremental runs after a regional fetch touch 2K–15K documents and finish in under 10 seconds.

The query, in layers

Here is where typo tolerance actually happens. This is a bool with several should clauses, each doing one job, scored together.

<?php
declare(strict_types=1);

// app/Search/OpenSearchQuery.php
final class OpenSearchQuery
{
    public static function build(string $q, ?string $region, int $limit): array
    {
        $filter = [];
        if ($region !== null) {
            $filter[] = ['term' => ['regions' => $region]];
        }

        return [
            'size'    => $limit,
            '_source' => false,          // we only want IDs; SQLite hydrates the rest
            'query'   => [
                'function_score' => [
                    'query' => [
                        'bool' => [
                            'filter'               => $filter,
                            'minimum_should_match' => 1,
                            'should' => [
                                // 1. exact normalized title — always wins
                                ['term' => ['title.raw' => ['value' => mb_strtolower($q), 'boost' => 12.0]]],

                                // 2. phrase match, order matters
                                ['match_phrase' => ['title' => ['query' => $q, 'boost' => 6.0, 'slop' => 1]]],

                                // 3. the typo-tolerant leg
                                ['multi_match' => [
                                    'query'                => $q,
                                    'fields'               => ['title^3', 'channel'],
                                    'type'                 => 'best_fields',
                                    'fuzziness'            => 'AUTO:4,7',
                                    'prefix_length'        => 1,
                                    'max_expansions'       => 50,
                                    'minimum_should_match' => '70%',
                                    'boost'                => 2.0,
                                ]],

                                // 4. typeahead prefix, low boost so it never outranks a real match
                                ['match' => ['title.prefix' => ['query' => $q, 'boost' => 0.6]]],
                            ],
                        ],
                    ],
                    'functions' => [
                        ['field_value_factor' => [
                            'field'    => 'view_count',
                            'modifier' => 'log1p',
                            'factor'   => 0.15,
                            'missing'  => 1,
                        ]],
                        ['gauss' => ['published_at' => [
                            'origin' => 'now', 'scale' => '21d', 'offset' => '3d', 'decay' => 0.5,
                        ]]],
                    ],
                    'score_mode' => 'sum',
                    'boost_mode' => 'multiply',
                ],
            ],
        ];
    }
}

The parameters that took the most tuning:

• fuzziness: AUTO:4,7 means terms under 4 characters get no fuzziness, 4–6 characters get edit distance 1, and 7+ get edit distance 2. The default AUTO starts fuzzing at 3 characters, which made the match she, tha, and toe. Video titles are full of short function words. Raising the floor to 4 removed an entire class of nonsense results.
• prefix_length: 1 requires the first character to be exact. Users mistype the middle and end of words far more than the first character, and this single setting cut fuzzy query latency by roughly 60% because it prunes the term dictionary scan immediately.
• minimum_should_match: '70%' on the fuzzy leg. Without it, a five-word query matched documents sharing one fuzzy token, and the result page filled with garbage that scored just high enough to display. Requiring most of the query to land is what makes fuzzy results feel intentional rather than random.
• max_expansions: 50 caps how many index terms each fuzzy term expands to. Uncapped fuzzy matching on a large title corpus is how you discover what a slow query log looks like.
• The recency gauss is site-specific. We are a trending-video site; a match from three weeks ago is worth less than a match from yesterday, and boost_mode: multiply lets relevance stay the primary signal with freshness as a modifier rather than an override.

On strangr things sesaon 5 trailr: clause 1 and 2 contribute nothing, clause 3 does all the work, and the query now returns 287 results with the right trailer at position 2.

Never let search take the site down

This is the part I would keep even if I ripped out OpenSearch tomorrow. FTS5 stayed. It is now the fallback behind a circuit breaker, and the search page is structurally incapable of returning a 500 because the search node is unreachable.

<?php
declare(strict_types=1);

// app/Search/SearchFacade.php
final class SearchFacade
{
    private const FAIL_THRESHOLD = 3;
    private const OPEN_SECONDS   = 60;
    private const TIMEOUT_MS     = 250;

    public function __construct(
        private readonly PDO $db,
        private readonly Fts5Search $fallback,
        private readonly string $host,
        private readonly string $auth,
    ) {}

    public function search(string $q, ?string $region, int $limit = 40): array
    {
        if ($this->breakerOpen()) {
            return $this->fallback->search($q, $limit);
        }

        try {
            $ids = $this->queryOpenSearch($q, $region, $limit);
            $this->recordSuccess();
            return $ids === [] ? [] : $this->hydrate($ids);
        } catch (Throwable $e) {
            $this->recordFailure();
            error_log('search fallback: ' . $e->getMessage());
            return $this->fallback->search($q, $limit);
        }
    }

    /** @return list<string> */
    private function queryOpenSearch(string $q, ?string $region, int $limit): array
    {
        $ch = curl_init($this->host . '/videos/_search');
        curl_setopt_array($ch, [
            CURLOPT_POST           => true,
            CURLOPT_POSTFIELDS     => json_encode(
                OpenSearchQuery::build($q, $region, $limit),
                JSON_THROW_ON_ERROR
            ),
            CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
            CURLOPT_USERPWD        => $this->auth,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS     => self::TIMEOUT_MS,
            CURLOPT_CONNECTTIMEOUT_MS => 120,
        ]);

        $raw  = curl_exec($ch);
        $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        curl_close($ch);

        if ($raw === false || $code !== 200) {
            throw new RuntimeException('opensearch http ' . $code);
        }

        $body = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);

        return array_map(
            static fn (array $hit): string => (string) $hit['_id'],
            $body['hits']['hits'] ?? []
        );
    }

    /** Reorder SQLite rows to match OpenSearch relevance order. */
    private function hydrate(array $ids): array
    {
        $in   = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $this->db->prepare(
            "SELECT video_id, title, thumbnail, duration, channel_title, published_at
               FROM videos WHERE video_id IN ($in)"
        );
        $stmt->execute($ids);

        $byId = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $byId[$row['video_id']] = $row;
        }

        $out = [];
        foreach ($ids as $id) {
            if (isset($byId[$id])) {
                $out[] = $byId[$id];   // preserve relevance order
            }
        }
        return $out;
    }

    private function breakerOpen(): bool
    {
        $row = $this->db->query(
            'SELECT fails, opened_at FROM search_breaker WHERE id = 1'
        )->fetch(PDO::FETCH_ASSOC);

        if (!$row || (int) $row['fails'] < self::FAIL_THRESHOLD) {
            return false;
        }
        return (time() - (int) $row['opened_at']) < self::OPEN_SECONDS;
    }

    private function recordFailure(): void
    {
        $this->db->exec(
            'INSERT INTO search_breaker (id, fails, opened_at) VALUES (1, 1, ' . time() . ')
             ON CONFLICT(id) DO UPDATE SET fails = fails + 1, opened_at = ' . time()
        );
    }

    private function recordSuccess(): void
    {
        $this->db->exec('UPDATE search_breaker SET fails = 0 WHERE id = 1');
    }
}

The breaker state lives in SQLite because shared hosting gives us no APCu and no Redis. It is one row and two integers, and the write only happens on state transitions, so it costs nothing in the normal path. The 250ms timeout is deliberately aggressive: if OpenSearch cannot answer in 250ms, FTS5 can answer in 9ms, and a slightly worse result now beats a better result after a spinner.

The FTP deploy story shakes out cleanly with this shape. The application ships as plain files with no Composer vendor tree — the OpenSearch client is 200 lines of cURL. Host and credentials live in the per-site env file that the deploy script already manages. If I push new code that expects a mapping the index does not have yet, the queries fail, the breaker opens, and the site quietly serves FTS5 results until I fix it. That is exactly the failure mode I want from a dumb file-sync deploy.

Six weeks of numbers

• Zero-result rate on search: 11.4% → 2.1%
• Search-result click-through: 43% → 58%
• p95 search latency: 9ms (FTS5) → 38ms (OpenSearch)
• Sessions that ran a second search after an empty first one: down 64%
• Infrastructure cost: $12/month for the node

Search got four times slower and materially better. I would make that trade again — 38ms is invisible next to the video thumbnails loading on the same page, and an empty result set is not.

Three things I would do differently

• Alias from day one. I indexed straight into videos for the first two weeks, then had to take a five-minute search outage to reindex under a new mapping. The alias indirection costs one API call and buys zero-downtime reindexing forever.
• Do not fuzzy-match channel names as aggressively as titles. I originally boosted channel at the same weight as title, and fuzzy channel matches hijacked queries — searching for a show pulled up every video from a channel whose name was one edit away. Dropping channel to unboosted inside best_fields fixed it.
• Log queries with their result count before you need the data. The only reason I could justify this project was two years of search_log rows with a result_count column. Without it I would have been arguing from intuition, and I would have been guessing at which typo classes actually mattered.

If you are sitting on FTS5 and watching a zero-result rate you cannot explain, the honest answer is that no amount of tokenizer tuning gets you edit distance. But you do not have to throw FTS5 away to get it — one small node, a versioned index behind an alias, and a circuit breaker in front turn your existing full-text search into a fallback that costs nothing and saves you on the day the search node reboots.