{"slug":"sqlite-search-builder","name":"SQLite Search Builder","version":"1.0.0","updated_at":"2026-10-04T10:59:37.305Z","use_when":"Builds or fixes a site or product search so it finds what the user meant, with SQLite full-text search (FTS5) on D1, Turso or SQLite. Word order, singular or plural, endings, case, punctuation and accents stop mattering (\"cafe\" finds \"Café\", \"tomatoes\" finds \"tomato\", \"red tomato\" finds \"Tomato, red\", \"ab1042\" finds \"AB-1042\"), and the start of a word is enough (\"tom\" finds \"tomatoes\"). Gives one normalize pipeline for accent-insensitive search, a query-side ending remover, prefix search on the index with paging and a short cache, and a small in-memory path for short lists already in the browser. Never loads a whole table to filter it per keystroke. It does not correct typos. Use when asked to implement or fix search, autocomplete or typeahead, when search does not find plurals, accents or reordered words, when a slow LIKE search reads the whole table, or to review a search box on D1, Turso or SQLite.","not_for":"Typo correction or 'did you mean' suggestions: there is no edit distance, so 'tomatoe' will not find 'tomato'. Also not for ranking by relevance (results come newest first), semantic or vector search, or setting up Elasticsearch, Algolia or Meilisearch. Postgres gets a short note only.","languages":["any"],"tags":["sqlite","full-text-search","fts5","cloudflare-d1","turso","autocomplete","search"],"category":"code","category_url":"https://aiskills402.com/categories/code","keywords":["SQLite full-text search","accent-insensitive search","prefix search","autocomplete","slow LIKE search"],"faq":[{"q":"Will the search read the whole table?","a":"No. The default is an FTS5 index searched by word start, ordered by rowid and stopped at a LIMIT, so one request reads about two rows per result shown, and page 50 costs the same as page 1. Loading every item and filtering it in code is allowed only for a list of up to about 2,000 that is already in the browser."},{"q":"Does it work outside English?","a":"The steps are the same for any language: one cleaning function for stored and typed text, a dictionary, at most five ending rules and two length guards. Accents come off Latin letters only, so the Cyrillic 'й' survives. In our Bulgarian catalogue case both models passed with the skill; without it Haiku called the loss of 'й' harmless and gave no Bulgarian rules."},{"q":"Is it worth it with a strong model?","a":"In our twelve cases Claude Sonnet fixed every planted problem with or without the skill, in some cases with other working fixes such as a Porter tokenizer. The gain was on Claude Haiku: twelve of twelve with the skill, seven of twelve without."}],"examples":[{"lang":"en","model":"claude-sonnet-5-5","input_excerpt":"Notes: React shop, Vite, no backend search. The catalog is one JSON file of about 400 products, fetched once when the app starts and kept in a context.\n\ntype Product = { id: string; name: string; sku: string };\n\nCurrent search box code:\nconst results = products.filter((p) => p.name.includes(query));…","output_excerpt":"**Mode A (in memory):** your 400 products are fetched once and held in a context, which is the one case where filtering an array is allowed. This only holds below about 2,000 items (never above 5,000), and only while the list isn't re-fetched per search. If the catalog outgrows that, move to a database with an FTS5 prefix index.…"}],"page_url":"https://aiskills402.com/skills/sqlite-search-builder","markdown_url":"https://aiskills402.com/skills/sqlite-search-builder.md","image_url":"https://cdn.aiskills402.com/og/skills/sqlite-search-builder/5cd5b7be.png","related_url":"https://api.aiskills402.com/v1/skills/sqlite-search-builder/related","purchases_count":null,"tested":{"date":"2026-10-04","strong":{"model":"claude-sonnet-5-5 (Claude Code alias \"sonnet\")","verdict":"Fixed the planted problem in all 11 cases that had one and answered 'No findings.' for the clean search. It moved LIKE '%q%' and a load-the-whole-table route onto an FTS5 index with prefix search and a LIMIT, cut endings from the query only, added a dictionary for irregular plurals, and kept the breve of 'й' in a Bulgarian catalogue. On the product-code case it also stored the code without its dash, so 'ab1042' finds 'AB-1042', and it noticed that one complaint in our case could not happen with the code we showed. In 1 of 12 answers it left out the 3-letter prefix rule, which the skill asks for."},"weak":{"model":"claude-haiku-4-5-20251001 (Claude Code alias \"haiku\")","verdict":"Fixed the planted problem in all 11 cases that had one. It followed the skill's form less closely: five of its answers each left out one part the skill asks for (a debounce, the 2,000-item limit of the in-memory mode, cutting endings from the query only, the 3-letter prefix rule, or storing dictionary forms), and once it described a stem wrongly ('tomat' for 'tomatoes'), though the prefix still matched. On the clean search it answered 'No findings.' once; in a second run it reported a cursor pattern that cannot overflow in practice. With Haiku, check the generated code against the skill's test table."},"note":"Twelve fictional search codes: eleven with one planted problem each (an in-memory search, LIKE '%q%' on every keystroke on D1, plurals in FTS5, word order, accents, irregular plurals, an over-eager stemmer, product codes with dashes, a Bulgarian catalogue, cleaning on one side only, a route that loads the whole table) and one clean. One run per model and case, six cases run again after a fix. The steps in the skill were run on real FTS5 (node:sqlite): 70 checks that take the rules, the SQL and the examples from the skill text, two pages of 18 with no repeats, and a query plan with no full scan. The test found one gap in the skill itself, a code typed without its dash; it was fixed and checked again.","baseline":{"date":"2026-10-04","rows":[{"label":"Cases passed","better":"higher","strong":{"with":{"n":12,"of":12},"without":{"n":12,"of":12}},"weak":{"with":{"n":12,"of":12},"without":{"n":7,"of":12}}}],"note":"One run per model and case, the same checks for both sides; we read every failed answer and widened nine checks that rejected right answers for their wording. Without the skill Sonnet gave other working fixes (a Porter tokenizer, remove_diacritics 2, an exception list). Haiku without it kept LIKE '%q%' on the server, called the loss of 'й' in Bulgarian harmless and gave no Bulgarian rules, offered two fixes for product codes that each left one complaint unsolved, said an over-eager stemmer was fine for 'universe' and 'news', and changed the tokenizer without rebuilding the table."},"report_url":null},"price_usd":"0.01","price_micro":10000,"size_bytes":14844,"sha256":"e29d1f6135238dccbf0c3a580d046c2cec6df0ce59631c2317a234a8f3bab545","outline":["Hard rules","Choose the mode","Normalize (both modes)","Query-side ending remover","Mode B: FTS5 prefix search (D1, Turso, SQLite)","Mode A: list already in memory (limit about 2,000)","Do not","Tests (run before you say done)"],"license":{"summary":"Perpetual, non-exclusive; use and modify for yourself incl. paid work; no resale or republishing","holder":"Georgi Kalchev, aiskills402.com","url":"https://aiskills402.com/docs#license"},"buy_url":"https://api.aiskills402.com/v1/skills/sqlite-search-builder/file","redownload_url_template":"https://api.aiskills402.com/v1/purchases/{token}","mcp_tool":null,"payment":{"protocol":"x402","scheme":"exact","asset":"USDC","selling":true,"network":"base","network_caip2":"eip155:8453","pay_to":"0x8e37022edcf0f21cf3c9f93fee9d4d32519f36f4","facilitator":"cdp"},"seo_title":"SQLite Search Builder: FTS5 Search Skill File","seo_description":"Skill file that builds or fixes SQLite FTS5 search on D1 or Turso. Finds plurals, accents and reordered words, plus prefixes. No typo correction.","versions":[{"version":"1.0.0","date":"2026-10-04","changelog":"# Changelog\n\n## 1.0.0 — 2026-10-04\n\nFirst release: builds or fixes a site or product search that forgives word order, plurals, accents, case, punctuation and partly typed words, on an SQLite FTS5 index (D1, Turso) with prefix search, keyset paging and a short cache, plus an in-memory mode for short lists already in the browser. Every step is written out in words and tables, with a test table to run before calling the search done.\n"}]}