Design Rationale — Search
Non-obvious design choices for the search module — the WHY behind each decision.
Search Module (Locked 2026-06-11)
DR1 — Postgres-native FTS, not an external search engine (Option A)
Decision: Search is implemented as tsvector generated columns + dual GIN indexes (tsvector + pg_trgm) added as additive touches to 6 locked source tables. No external search engine (Typesense, Algolia, OpenSearch) in v1.0.
Why: Three options were evaluated. Option A (Postgres FTS) was selected because: (a) no tenant-isolation risk — Postgres RLS already enforces tenant boundaries; external engines would require per-tenant index namespacing or query-time filtering that is harder to audit; (b) no sync complexity — GENERATED ALWAYS AS ... STORED columns are maintained by the database itself, with zero application code for index maintenance; (c) no new infrastructure dependency in v1.0; (d) SearchService abstraction hides the engine, enabling a future swap without changing callers. Option B (external engine) was rejected: tenant-isolation requires per-tenant index namespace or query-level tenant filter that bypasses DB-layer RLS — a category of isolation bug that has caused cross-tenant data leaks in practice. Option C (separate search tables in search schema) was rejected: reference-don't-copy — searchable data lives in locked source tables; a separate copy would create two sources of truth.
Rejected: Option B (external engine, v1.0) — tenant isolation risk + sync complexity. Option C (separate search.* tables) — violates reference-don't-copy; all searchable data already lives in authoritative source tables.
Guard: SearchService is the abstraction boundary — callers never query search_vector directly. All search routes through SearchService.search(query, {module, tenant_id}). This boundary enables engine swap at scale without touching callers. Every SearchService query MUST include WHERE tenant_id = ? — the GIN index alone does not scope results to a tenant.
DR2 — search_vector as generated column is NOT a copy (reference-don't-copy compatible)
Decision: search_vector tsvector GENERATED ALWAYS AS (...) STORED is a derived index-only column, not a data copy. It does not violate the reference-don't-copy principle.
Why: A GENERATED ALWAYS AS ... STORED column is a deterministic derivation from columns in the same row, maintained exclusively by the database engine. It cannot be written by application code; it cannot diverge from its source columns; it carries no new business information. The reference-don't-copy principle prohibits storing the same business data in two places. search_vector stores no business data — it stores a pre-computed FTS representation for indexing purposes only. The source columns (name, sku, display_name, etc.) remain the authoritative values; search_vector is as derived as a secondary index.
Rejected: A separate search.item_index table (or similar) copying the searchable text fields — that would be a true data copy: separate row, separate RLS, potential divergence, separate retention obligation.
Guard: search_vector columns must always be GENERATED ALWAYS AS (...) STORED — never a plain tsvector column written by application code. Application code must never contain UPDATE inventory.item SET search_vector = ... or equivalent. If the tsvector expression needs to change, it is an ALTER TABLE ... ALTER COLUMN search_vector SET GENERATED ALWAYS AS (new_expression) STORED migration, not an application-layer job.
DR3 — Dual GIN index: tsvector + pg_trgm both required
Decision: Every searchable column set gets BOTH a GIN on (search_vector) index (multi-word FTS, stemming, stop-word handling) AND one or more GIN on (col gin_trgm_ops) indexes (fuzzy/partial/prefix/typo-tolerant). Neither index alone is sufficient.
Why: tsvector FTS handles: multi-word phrases, stemming ("plants" matches "plant"), stop-word filtering, @@ tsquery operators. It does NOT handle: partial-token matches ("JM-00" does not match "JM-001" — the token JM-001 is not the same as JM-00), single-character typos, prefix-only queries on opaque codes. pg_trgm GIN handles: partial substring matches, prefix matches, fuzzy similarity (trigram overlap). It does NOT handle: multi-word phrase semantics, stemming. POS ring-up is the critical failure case: a cashier types a partial SKU ("JM-00") — tsvector returns nothing; trgm returns the match. Both are required for a complete search experience.
Rejected: tsvector-only (misses partial-SKU and typo use cases silently — no error, just empty results). trgm-only (misses multi-word phrase matching and language-level stemming). A single composite GIN (not possible — gin_trgm_ops and tsvector are different opclasses; they cannot share one index).
Guard: Every new FTS touch to a source table MUST add BOTH a GIN on (search_vector) AND at least one GIN on (col gin_trgm_ops). The tsvector GIN alone is insufficient. If a table is added to the search touch manifest in a future iteration, this dual-index pattern is mandatory, not optional. inventory.item_variant has two trgm GINs (sku + name) — 7 trgm GINs total across 6 tables at Search lock.
DR4 — Zero-table schema: search schema locked with no physical tables
Decision: The search schema is locked with zero physical tables. It is delivered entirely as 6 additive FTS touches to locked source tables, plus the SearchService abstraction layer. The schema section in SCHEMA.md is real and locked; it carries no CREATE TABLE statements.
Why: All searchable data lives in locked source tables (inventory.item, inventory.item_variant, crm.customer, purchasing.vendor, orders.order_header, pos.sale). Creating new tables in search schema to hold searchable data would violate reference-don't-copy — the data is already owned by its source module. The search schema is an abstraction boundary and a home for future search infrastructure tables (e.g. saved_search in v1.5, search_session_log), not a data-copying layer. A zero-table schema lock is valid: the schema section documents the architectural decision, the touch manifest, design rationale, and deferred items. Future tables will be added to this schema section when the relevant features are built.
Rejected: Adding a search.item_search_doc table (or similar denormalized copy) — violates reference-don't-copy; adds sync complexity; requires separate RLS. Deferring the lock until a table exists — the architectural decision (Option A, dual-index pattern, SearchService contract, composition notes) is the lock deliverable; the zero-table state is by design, not by incompleteness.
Guard: The search schema is the correct home for any future search-infrastructure tables (saved_search, search_session_log, search_ranking_config). Do not add these tables to source-module schemas. The schema section MUST remain present in SCHEMA.md even while it has zero tables.
DR5 — pgvector / semantic search is AIService, NOT SearchService
Decision: AIService.semanticSearch() over Bedrock embeddings is a separate service from SearchService lexical/trigram search. They are not the same service, not the same index, and not called by the same callers.
Why: Lexical FTS + trigram search (what SearchService does) and semantic/vector search (what AIService.semanticSearch() does) are fundamentally different operations: different index types (GIN on tsvector vs. pgvector HNSW/IVFFlat), different query semantics (exact token/trigram match vs. embedding similarity), different latency profiles, different cost models (tsvector is free; Bedrock embedding calls have per-token cost), and different use cases (ring-up SKU lookup vs. "find products similar to this description"). Conflating them into a single service would mix concerns that have different scaling, cost, and accuracy profiles. AIService already owns Bedrock integration and the ai_request audit log.
Rejected: Adding semantic search to SearchService — wrong abstraction layer; SearchService would need to call Bedrock (AI concern) and manage embedding vectors (vector DB concern). A single UnifiedSearchService wrapping both — premature abstraction; the two search types serve different UX contexts and are not typically called together.
Guard: SearchService never calls Bedrock, never reads or writes pgvector columns. AIService never queries search_vector columns. If a future feature needs both (e.g. "hybrid search"), it composes the two services at the feature layer, not by merging them.
DR6 — Customer-name search for orders/sales routes through CRM, not order/sale tsvector
Decision: Searching for orders or sales by customer name requires a two-step SearchService compose: (1) SearchService.search({module: 'crm', query: name}) → customer_id[]; (2) filter orders.order_header / pos.sale by customer_id IN (...). The search_vector on order_header and pos.sale covers order_number, po_number, and job_reference only — not the customer's name.
Why: Neither orders.order_header nor pos.sale carries a denormalized customer name column. Customer identity is linked only via customer_id FK to crm.customer. Adding a customer_name_snapshot column to these tables would duplicate CRM data (two sources of truth for a customer's name), violate reference-don't-copy, and require a propagation job when names change. The correct design is: CRM owns the customer name; orders/sales reference the customer by ID. Single-pass search_vector @@ query on order_header for a customer name returns nothing silently — no error, just no results. This is a known limitation, not a bug, and must be handled at the service layer.
Rejected: customer_name_snapshot text column on order_header and pos.sale — violates reference-don't-copy; creates stale-name risk; requires propagation when customer name changes. Single-pass tsvector query on orders for customer name — silently returns nothing; incorrect UX.
Guard: SearchService MUST implement the two-step compose for any module query that filters by customer name on a table that has no denormalized name column. This pattern applies to any entity that lacks a name snapshot — check the Touch Manifest in SCHEMA.md § search before writing a single-pass customer-name query against order_header or pos.sale.