search — Phase 5
Schema locked 2026-06-11. Zero tables — delivered as 6 FTS touches to locked modules. See PROJECT_DECISIONS "Search Module (Locked 2026-06-11)".
Option A — Postgres-native FTS: tsvector generated columns + dual GIN indexes (tsvector + pg_trgm) added as additive touches to 6 locked source tables. No external search engine in v1.0. SearchService abstraction layer hides the implementation — callers never query search_vector directly; they call SearchService.search(query, {module, tenant_id}). This boundary enables a future swap to Typesense / Algolia at scale without touching callers.
Why no new tables: all searchable data lives in locked source tables. A separate search table would duplicate state (reference-don't-copy). The generated search_vector column is an index-only derived column — source rows stay authoritative.
pgvector is separate: AIService.semanticSearch() over Bedrock embeddings, NOT SearchService. Semantic search (vector) and FTS (lexical + trigram) are distinct services with distinct callers.
Tenant isolation: The search_vector GIN and gin_trgm_ops GIN indexes carry no tenant partitioning — they are global-schema indexes. Tenant isolation comes from the existing RLS policies + (tenant_id, ...) composite indexes on each source table. Every SearchService query MUST include a WHERE tenant_id = ? predicate; the GIN index alone does not scope results to a tenant.
Extension: gin_trgm_ops requires the pg_trgm Postgres extension (provisioned in public schema — see docs/PROJECT_DECISIONS.md SCHEMA_CONVENTIONS extension list).
Touch Manifest — additive touches applied 2026-06-11
| Table | search_vector sources |
Trgm GIN on | Notes |
|---|---|---|---|
inventory.item |
name, description, item_type |
name |
Product catalog search; 13 → 14 cols |
inventory.item_variant |
sku, name |
sku, name |
POS ring-up partial-SKU (sku trgm) + fuzzy variant-name search (name trgm — F1 fix at Search lock) |
crm.customer |
display_name, customer_number, company_name |
display_name |
customer_number + company_name nullable; display_name is primary search key |
purchasing.vendor |
name, code, legal_name |
name |
legal_name nullable (B2B / 1099 search); 30 → 31 cols |
orders.order_header |
order_number, po_number, job_reference |
order_number |
No customer name snapshot on order_header — customer linked via customer_id FK only; customer name search routes through CRM; 36 → 37 cols |
pos.sale |
sale_number, po_number, job_reference |
sale_number |
No customer name snapshot on pos.sale — customer linked via customer_id FK only; customer name search routes through CRM; 40 → 41 cols |
Dual index pattern applied to every touch: GIN on (search_vector) (multi-word FTS, stemming, stop-word handling) + GIN on (col gin_trgm_ops) (fuzzy / partial / prefix / typo-tolerant). Both required — tsvector alone misses partial-SKU lookups (e.g. JM-00 → JM-001) and single-char typos. inventory.item_variant has two trgm GINs (sku + name) — 7 trgm GINs total across 6 tables.
SearchService Composition Notes
Two-step customer-name search for orders and sales: pos.sale and orders.order_header carry no denormalized customer-name columns — customer is linked only via customer_id FK. A single-pass search_vector @@ query on these tables for a customer name returns nothing silently. SearchService MUST compose a two-step join for customer-name order/sale lookup:
SearchService.search({module: 'crm', query: customerNameQuery, tenant_id})→customer_id[]SearchService.search({module: 'orders' | 'pos', filter: {customer_id: {IN: ids}}, tenant_id})→ results
This pattern applies to any entity that lacks a name snapshot. The search_vector on order_header / sale covers order_number, po_number, and job_reference only — not the customer's name.
Deferred
| Item | Status |
|---|---|
saved_search table |
v1.5 — add when save-search UX is a product requirement |
| Faceted / aggregation search | v1.5 — facet counts, range buckets, category drilldown; requires query changes beyond search_vector @@ query |
| Consumer plant browse (attribute + zone facets) | Consumer phase — shared.plant_common_name.name_normalized already serves import fuzzy-match; consumer-facing attribute/zone browse deferred with Consumer Layer |
| External engine swap (Typesense / Algolia) | At scale — SearchService is the abstraction layer; swap engine without changing call sites |
| Semantic / pgvector search | AIService.semanticSearch() — not SearchService; see AI module |