Database optimization when queries become the product

Problem

In NepalIPMS, the database is not backstage infrastructure — it is the product experience. Search, deadline engines, matter dashboards, and register sync all compete for query time on D1. As trademark rows passed 130,000 and migrations exceeded 150, unindexed paths that felt fine in development became visible pauses on live desks.

Context

SQLite at the edge is powerful and constrained. You get transactional reliability and FTS5 without managing Postgres clusters — but you also inherit single-writer realities and the need for disciplined schema evolution. Every migration runs against production data firms depend on daily.

Approach

We instrumented slow queries in production first. Optimization followed desk behavior: which filters appear in every search, which joins matter list views touch, which deadline queries run on cron. Indexes were added surgically — composite keys on status + class + owner, covering indexes for list projections, FTS tables maintained at ingest rather than rebuilt nightly.

Architecture

Migrations are versioned and applied through the same deployment pipeline as Workers. Destructive changes pass through expand-contract patterns — add column, backfill, switch reads, drop old. Pagination is mandatory on list endpoints; N+1 fetches eliminated at the API layer. KV caches hot read paths with short TTL. Write paths batch where bulletin imports flood the register.

Result

Search and matter views returned to sub-200ms for typical operations. Migrations shipped without maintenance windows that IP firms cannot afford. The schema remained legible — domain-shaped tables rather than generic key-value sprawl that makes tuning impossible later.

Lesson

Optimize from production query logs tied to user actions, not ORM defaults. At 130k rows, the difference between a good index and a table scan is the difference between software people trust on a client call and software they work around.