Batch search index — plan

Status. Pre-Phase-1a planning artifact. Drafted 2026-05-28. Will be folded into openspec/changes/batch-level-inventory-and-transforms/design.md when Phase 1a kicks off.

Purpose. The merge match rule's qualityMetricsFingerprint (spec) handles exact-match lookups (cheap, indexed via B-tree). But buyers want range queries across quality metrics — "show me 7-Suta+ Makhana with moisture 9-12%, handpicked, GI-verified, in Bihar." That's not what JSONB GIN on Postgres scales to.

This document picks the search index, defines the sync pipeline, and specs the query API so Phase 1a's batch model lands ready to be searchable.

Audience. Anyone working on browse-board search, filter rails, or admin batch lookup in Phase 1a or later.

---

1. The problem in one diagram

   Browse Sell Leads board (today, post-CHG-014):
   ════════════════════════════════════════════════

      Query: "7-Suta+ Makhana, moisture 9-12%, GI verified"
                        │
                        ▼
       SELECT * FROM sell_leads sl
       JOIN products p ON sl.product_id = p.id
       WHERE p.taxonomy_leaf_id = ?
         AND p.gi_status = 'GI_TAGGED_VERIFIED'
         AND p.quality_metrics @> '{"moisture": ...}'  ← range query
                                                         on JSONB
                                                         doesn't index well

   At 1k products       : ~50ms  (fine)
   At 10k products      : ~200ms  (slow)
   At 100k products     : ~1.5s  (broken)
   At 1M products       : timeout

   Plus: faceted search (give me counts per moisture bucket) is
   essentially a separate query per bucket — kills the planner.

The fingerprint-based match rule is for the WRITE-side merge decision (single B-tree lookup, fast forever). This index is for the READ-side discovery flow (range queries, faceted aggregations, full-text on leaf names + source party).

2. The choice — Meilisearch

Five candidates evaluated:

Engine	Operational complexity	Indian-region	Cost (small scale)	Range queries	Faceted search	Why not
Elasticsearch	High (cluster ops, JVM tuning, shard rebalancing)	AWS/GCP Mumbai	$$$	✓	✓	Operational weight wrong for Cropto's small team
OpenSearch	High (ES fork)	AWS Mumbai	$$	✓	✓	Same
Meilisearch (chosen)	Low (single binary, sane defaults)	Self-host or Meilisearch Cloud (EU/US)	$	✓	✓	—
Typesense	Low	Self-host or Typesense Cloud	$	✓	✓	Marginal differences vs Meilisearch; lose some BM25 quality
Postgres pg_trgm + JSONB GIN	Zero (already there)	N/A	$0	partial	poor	Won't scale past 100k products

Why Meilisearch:

• Single binary (one Docker container; one Railway service)
• Out-of-the-box typo tolerance + relevance ranking
• Faceted search built in (no Solr-style config files)
• Open-source MIT license; self-host if data residency requires it
• Strong Indian developer community + battle-tested deployments at scale (~10M docs)
• Per-index settings configurable via API; no XML / DSL learning curve

The escape hatch: if Meilisearch ever hits limits, the sync pipeline (see §4) is engine-agnostic. Re-pointing at Elasticsearch / OpenSearch is a config change + adapter swap, not a re-architecture.

3. Document schema — what gets indexed

One Meilisearch document per Batch:

{
  "id": "BCH-2026-0042",
  "userId": "u_ramesh_123",
  "userRole": "farmer",
  "userName": "Ramesh Farms",
  "userLocation": "Darbhanga, Bihar",
  "userTrustTier": "VERIFIED_LITE",

  "leafId": "leaf_7_suta_rasgulla",
  "leafName": "7 Suta (Rasgulla) Makhana",
  "leafChain": "Raw Makhana › Graded › 7 Suta (Rasgulla) Makhana",

  "packingMode": "BULK",
  "packingBulkType": "JUTE_SACK",
  "packingMOQ": 80,

  "giStatus": "GI_TAGGED_VERIFIED",
  "giRegion": "Mithilanchal",

  "sourceParty": "own-farm",   // only present when userRole === 'farmer'
                                // (Decision 20 — role-aware disclosure)

  "currentQtyKg": 200,
  "availableQtyKg": 100,        // currentQtyKg - reservedQtyKg
  "pricePerKg": 1280,           // most recent active sell-lead price

  "acquiredAt": 1718208000,     // unix ts
  "createdAt": 1718294400,

  // qualityMetrics flattened with `metrics.` prefix for filtering:
  "metrics.moisture": 9,
  "metrics.handpicked": true,
  "metrics.grade": "7-suta+",
  "metrics.color": "ivory",
  "metrics.brand_name": "RF Premium",
  // ... and so on per leaf schema

  // Computed fields for fast search:
  "hasActiveSellLead": true,
  "activeSellLeadCount": 1,
  "_geo": { "lat": 26.15, "lng": 85.90 }  // userLocation geocoded
}

Why flatten metrics.*: Meilisearch's filter syntax (metrics.moisture >= 9 AND metrics.moisture <= 12) is straightforward on flat fields. Nested object filtering is supported but slower + more verbose.

Why include role-aware fields server-side: the search results page already knows the viewer's identity context. Filtering / hiding sourceParty for non-farmer batches happens in the result-serializer (same pattern as the lead-detail page).

4. Sync pipeline — Postgres → Meilisearch

Two options:

   OPTION A — Prisma middleware (chosen)             OPTION B — Periodic full reindex
   ────────────────────────────────────              ────────────────────────────────
   On every Batch / Product / SellLead write,        Cron job at 02:00 IST: full
   emit a sync event:                                  reindex of all batches.
       sync.batch.upsert(batchId)
       sync.batch.delete(batchId)                    Pros:
                                                       • Simplest possible — no
   Sync worker processes the events:                    Prisma middleware fanout
       fetch batch + product + leaf + user            • Survives bugs (next run heals)
       build the document shape (§3)                 Cons:
       PUT to Meilisearch                              • Up to 24h stale data
                                                       • Doesn't scale past 100k
   Pros:                                                 batches per nightly window
     • Near-real-time (~1s p95)
     • Per-document writes scale linearly         Combined plan: ship Option A as
     • Easy to retry failed writes                primary, keep Option B as a
   Cons:                                          self-healing fallback (weekly
     • Coupling Prisma → sync worker              cron rebuilds the index from
     • Need a queue (BullMQ slot, already         scratch).
       in stack)

Pipeline shape:

   ┌──────────────┐    Prisma middleware    ┌───────────────┐
   │  API write   │ ──────────────────────▶ │ BullMQ queue  │
   │  (any path)  │   on tx commit          │ search-sync   │
   └──────────────┘                         └───────┬───────┘
                                                    │
                                                    ▼
                                            ┌───────────────┐
                                            │  syncWorker   │
                                            │  re-fetches   │
                                            │  full doc,    │
                                            │  upserts to   │
                                            │  Meilisearch  │
                                            └───────────────┘

Prisma middleware intercepts prisma.batch.{create, update, delete}, prisma.product.update, prisma.sellLead.{create, update} and enqueues a sync.batch.upsert(batchId) job. The worker re-fetches all the joined data + builds the doc + PUTs.

Why re-fetch on the worker (vs passing the data through the queue): keeps the queue payload tiny + lets the worker capture relations the middleware doesn't have. Sees the latest version of everything when it runs. Race-safe.

5. Query API — server-side filter translation

The browse-board's FilterRail today builds Prisma WHERE clauses. We replace those with Meilisearch filter strings:

// apps/api/src/lib/batchSearch.ts (Phase 1a)

import { MeiliSearch } from 'meilisearch';

interface BatchSearchInput {
  q?: string;                  // free-text on leaf name / source party / user name
  leafId?: string;
  packingMode?: 'BULK' | 'RETAIL';
  giStatus?: GIStatus;
  state?: string;
  district?: string;
  trustTier?: TrustTier;
  priceMin?: number;
  priceMax?: number;
  // Range filters on quality metrics — one filter per metric:
  metrics?: Record<string, { min?: number; max?: number; eq?: unknown }>;
  page?: number;
  limit?: number;
  sort?: 'price_asc' | 'price_desc' | 'recency_desc' | 'relevance';
}

export async function searchBatches(input: BatchSearchInput) {
  const filters: string[] = [];

  if (input.leafId) filters.push(`leafId = "${input.leafId}"`);
  if (input.packingMode) filters.push(`packingMode = "${input.packingMode}"`);
  if (input.giStatus) filters.push(`giStatus = "${input.giStatus}"`);
  if (input.state) filters.push(`userLocation = "${input.state}"`);  // simplified
  if (input.trustTier) filters.push(`userTrustTier = "${input.trustTier}"`);
  if (input.priceMin !== undefined) filters.push(`pricePerKg >= ${input.priceMin}`);
  if (input.priceMax !== undefined) filters.push(`pricePerKg <= ${input.priceMax}`);
  filters.push(`availableQtyKg > 0`);
  filters.push(`hasActiveSellLead = true`);

  for (const [key, range] of Object.entries(input.metrics ?? {})) {
    const field = `metrics.${key}`;
    if (range.eq !== undefined) filters.push(`${field} = ${JSON.stringify(range.eq)}`);
    if (range.min !== undefined) filters.push(`${field} >= ${range.min}`);
    if (range.max !== undefined) filters.push(`${field} <= ${range.max}`);
  }

  const sort = sortToMeili(input.sort);

  return client.index('batches').search(input.q ?? '', {
    filter: filters.join(' AND '),
    sort,
    limit: input.limit ?? 20,
    offset: ((input.page ?? 1) - 1) * (input.limit ?? 20),
    facets: ['leafId', 'giStatus', 'packingMode', 'userTrustTier', 'state'],
  });
}

The facets request returns per-bucket counts — what the FilterRail uses to show counts next to each option ("GI verified (143)", "Bulk (89)", etc.). One call to Meilisearch returns both the page of results AND the facet aggregations.

6. Operational concerns

6.1 Cost

• Self-hosted on Railway: ~$5-10/month for a single Meilisearch instance up to 1M docs.
• Meilisearch Cloud: $30/month for the smallest tier (1M docs, 100k searches/month).
• Cropto starts self-hosted; flips to Cloud when ops time spent on Meilisearch exceeds $30/month worth of engineering time.

6.2 Data residency

Meilisearch self-host on a Mumbai-region Railway instance — same region as Supabase. No data leaves India. Required for any future government-data partnerships (which Cropto's GI integration positions for).

6.3 Reindex window

Full reindex of ~100k batches takes ~10 min single-threaded. Run weekly during the lowest-traffic window (Sunday 03:00 IST). If reindex fails partway, Meilisearch's atomic-swap-of-indexes lets the old index continue serving until the new one completes.

6.4 Failure modes

Failure	Impact	Recovery
Meilisearch down	Browse board falls back to Postgres queries (same WHERE-clause builder, slower at scale)	Restore Meilisearch from snapshot; replay sync queue
Sync worker stuck	Up to 30s sync delay on writes	Restart worker; weekly reindex heals any drift
Document schema mismatch (added a metric field; old docs don't have it)	Filter on the new field returns 0 results for old docs	Reindex on schema change (cheap — runs in 10 min)
Race: write happens during reindex	Either pre- or post-write state in index; eventually consistent	Sync queue catches up within 1s

6.5 Versioning

Schema changes (adding/removing/renaming a field on the indexed document) need a coordinated deploy:

1. Deploy app code that writes both old + new field for ~1 day
2. Trigger reindex
3. Deploy app code that reads only the new field
4. Drop the old field

Same pattern as any DB schema migration. Documented in the Phase 1a runbook.

7. What this plan deliberately does NOT cover

• Full-text search across users / leads / orders — out of scope; that's CHG-014 §6's existing Postgres trigram search. This plan is batch-specific.
• Real-time analytics — different need (aggregations across time). That's the Phase 4 BigQuery/Redshift pipeline (Phase 0 backlog item).
• Auto-complete on search-as-you-type — Phase 1a ships server-side filter-only. If we want type-ahead later, Meilisearch supports it natively (/search?q=mak).
• Sponsored / boosted results — Phase 0 backlog (monetisation item). Meilisearch's boostingRules make this easy when we get there.

8. Phase 1a integration checklist

When Phase 1a kicks off:

• [ ] Stand up Meilisearch on Railway (Mumbai region)
• [ ] Add MEILI_HOST + MEILI_API_KEY to env
• [ ] Add meilisearch npm dep to apps/api/package.json
• [ ] Create apps/api/src/lib/batchSearch.ts with the query API (§5)
• [ ] Create apps/api/src/lib/batchSearchSync.ts with the doc builder + upsert helper
• [ ] Create apps/api/src/workers/searchSyncWorker.ts (consumes BullMQ jobs)
• [ ] Add Prisma middleware to apps/api/src/lib/prisma.ts to enqueue sync jobs
• [ ] Initial full index build script: apps/api/scripts/reindex-batches.ts
• [ ] Weekly cron via scheduledQueue to re-run the full reindex
• [ ] Tests: doc-builder unit tests, sync-worker integration test (real Meilisearch in CI), query API tests
• [ ] Replace Postgres-based filter clauses in BatchSearchResults.tsx / BrowseBoard with calls to the new search service

---

Drafted 2026-05-28. Will fold into Phase 1a's openspec/changes/batch-level-inventory-and-transforms/design.md when that change is scaffolded.