id: 0011-search-index-in-its-own-database
type: decision
owner: justin
status: accepted
reviewed: 2026-10-16

0011 The search index gets its own D1 database

Context

[[0001-d1-for-shop-data]] put everything in one D1 database, storefront, so checkout could take stock and create an order in one batch. Shoppers now need to search the catalog, and D1 has full text search through SQLite's FTS5, so the obvious place for the index is an FTS5 table next to products.

Two things argue against that:

• A D1 database runs one query at a time. Search is the busiest read the shop will have, since the search box asks on every pause in typing, and each request ranks matches with bm25(). In the same database, those queries wait in the same line as checkout's batches and the expiry cron's writes.
• The index is derived data. It is rebuilt from products whenever the tokenizer changes, and nothing in it needs keeping. In the shop's database, changing it would mean a migration next to the ones that hold orders, and every rebuild would run its deletes and inserts on the database checkout writes to.

Decision

The shop's data stays in one database, storefront, with everything [[0001-d1-for-shop-data]] said about migrations and batches. The search index lives in a second database, storefront-search (binding SEARCH), with its own migrations in search/migrations/. It holds only the text search matches on: a product's id, name and description, for active products.

storefront-api writes the index right after every product write, and POST /admin/search/rebuild refills it from the catalog. A search finds ids in the index and reads the products from storefront. See [[search]].

Consequences

• Search load cannot slow down checkout, and the index can be dropped, retuned and rebuilt without touching the database that holds orders.
• A product write is now two writes in two databases, with no transaction across them. If the index write fails, the merchant gets 503 search_index_failed and sends the same request again; the upsert makes that safe. Anything that drifts anyway is fixed by a rebuild.
• A stale index can only miss a product or return one that no longer matches. It can never show a wrong price or an inactive product, because results always come from the catalog.
• Writes that bypass the API, such as scripts/seed.sql, do not reach the index. Run a rebuild after them.
• Still one writer: only storefront-api binds either database ([[0006-one-writer-for-the-database]]).