Implementing Search

Learn how to implement powerful search capabilities in Stack9 applications. This guide covers full-text search, filtering, autocomplete, search optimization, Elasticsearch integration, result ranking, and advanced search patterns.

What You'll Build

In this guide, you'll implement a Product Search System with:

Full-text search across multiple fields
Faceted filtering and refinement
Autocomplete/typeahead functionality
Search result ranking and relevance
Performance optimizations
Elasticsearch integration (optional)
Advanced search patterns

Time to complete: 45-60 minutes

Prerequisites

Completed Building a CRUD Application guide
Basic understanding of database queries
Familiarity with REST APIs

Understanding Search in Stack9

Stack9 provides multiple search capabilities:

1. Basic Search (Built-in)

Uses PostgreSQL's full-text search capabilities:

Text matching across fields
Simple filtering
Sorting and pagination
Good for small-medium datasets

2. Advanced Search with Elasticsearch

For large datasets and complex queries:

Full-text search with ranking
Fuzzy matching and typo tolerance
Faceted navigation
Near real-time indexing
Scalable to millions of records

Step 1: Create Searchable Entities

First, create entities optimized for search.

Product Entity with Search Fields

Create src/entities/custom/product.json:

{
  "head": {
    "name": "Product",
    "key": "product",
    "pluralisedName": "products",
    "icon": "ShoppingOutlined",
    "isActive": true
  },
  "fields": [
    {
      "label": "SKU",
      "key": "sku",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "maxLength": 50
      },
      "index": true,
      "searchable": true
    },
    {
      "label": "Product Name",
      "key": "name",
      "type": "TextField",
      "validateRules": {
        "required": true,
        "maxLength": 200
      },
      "index": true,
      "searchable": true,
      "searchWeight": 5
    },
    {
      "label": "Description",
      "key": "description",
      "type": "RichTextEditor",
      "searchable": true,
      "searchWeight": 2
    },
    {
      "label": "Brand",
      "key": "brand_id",
      "type": "SingleDropDown",
      "relationshipOptions": {
        "ref": "brand"
      },
      "typeOptions": {
        "label": "name"
      },
      "index": true
    },
    {
      "label": "Category",
      "key": "category_id",
      "type": "SingleDropDown",
      "relationshipOptions": {
        "ref": "category"
      },
      "typeOptions": {
        "label": "name"
      },
      "validateRules": {
        "required": true
      },
      "index": true
    },
    {
      "label": "Tags",
      "key": "tags",
      "type": "MultiDropDown",
      "relationshipOptions": {
        "ref": "tag"
      },
      "typeOptions": {
        "label": "name"
      }
    },
    {
      "label": "Price",
      "key": "price",
      "type": "NumericField",
      "validateRules": {
        "required": true,
        "min": 0
      },
      "typeOptions": {
        "decimals": 2
      },
      "index": true
    },
    {
      "label": "Stock Quantity",
      "key": "stock_quantity",
      "type": "NumericField",
      "defaultValue": 0,
      "typeOptions": {
        "decimals": 0
      }
    },
    {
      "label": "Status",
      "key": "status",
      "type": "OptionSet",
      "typeOptions": {
        "values": ["Active", "Discontinued", "Out of Stock", "Coming Soon"]
      },
      "defaultValue": "Active",
      "index": true
    },
    {
      "label": "Is Featured",
      "key": "is_featured",
      "type": "Checkbox",
      "defaultValue": false,
      "index": true
    },
    {
      "label": "Rating",
      "key": "rating",
      "type": "NumericField",
      "typeOptions": {
        "decimals": 1,
        "min": 0,
        "max": 5
      }
    },
    {
      "label": "Review Count",
      "key": "review_count",
      "type": "NumericField",
      "defaultValue": 0,
      "typeOptions": {
        "decimals": 0
      }
    }
  ]
}

Key Search Configuration:

index: true - Creates database index for fast filtering
searchable: true - Includes field in full-text search
searchWeight - Higher weight = more important in ranking

Supporting Entities

Create src/entities/custom/brand.json:

{
  "head": {
    "name": "Brand",
    "key": "brand",
    "pluralisedName": "brands"
  },
  "fields": [
    {
      "label": "Name",
      "key": "name",
      "type": "TextField",
      "validateRules": {
        "required": true
      },
      "index": true,
      "searchable": true
    },
    {
      "label": "Description",
      "key": "description",
      "type": "TextField"
    }
  ]
}

Create src/entities/custom/category.json:

{
  "head": {
    "name": "Category",
    "key": "category",
    "pluralisedName": "categories"
  },
  "fields": [
    {
      "label": "Name",
      "key": "name",
      "type": "TextField",
      "validateRules": {
        "required": true
      },
      "index": true
    },
    {
      "label": "Slug",
      "key": "slug",
      "type": "TextField",
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Parent Category",
      "key": "parent_id",
      "type": "SingleDropDown",
      "relationshipOptions": {
        "ref": "category"
      }
    }
  ]
}

Step 2: Implement Basic Search Query

Create src/query-library/searchproducts.json:

{
  "key": "searchproducts",
  "name": "searchProducts",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/product/search",
    "bodyParams": "{\n  \"$select\": [\n    \"id\",\n    \"sku\",\n    \"name\",\n    \"description\",\n    \"brand_id\",\n    \"brand.name\",\n    \"category_id\",\n    \"category.name\",\n    \"price\",\n    \"stock_quantity\",\n    \"status\",\n    \"is_featured\",\n    \"rating\",\n    \"review_count\",\n    \"_created_at\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"$or\": [\n      { \"name\": { \"$ilike\": \"%{{searchTerm}}%\" } },\n      { \"sku\": { \"$ilike\": \"%{{searchTerm}}%\" } },\n      { \"description\": { \"$ilike\": \"%{{searchTerm}}%\" } }\n    ]\n  },\n  \"$withRelated\": [\n    \"brand(notDeleted)\",\n    \"category(notDeleted)\",\n    \"tags(notDeleted)\"\n  ],\n  \"$sort\": {\n    \"is_featured\": \"desc\",\n    \"rating\": \"desc\",\n    \"name\": \"asc\"\n  },\n  \"$limit\": {{limit}},\n  \"$offset\": {{offset}}\n}",
    "queryParams": {}
  },
  "userParams": {
    "searchTerm": "",
    "limit": 20,
    "offset": 0
  }
}

Search Features:

$ilike - Case-insensitive pattern matching
$or - Search across multiple fields
$withRelated - Load related entities
Sorting by featured status, rating, then name
Pagination with limit/offset

Step 3: Add Faceted Filtering

Faceted filtering allows users to narrow search results using multiple criteria. Stack9 provides comprehensive filter support through the query library.

Understanding Filter Types

Before implementing filters, understand the available types:

Filter Type	Use Case	UI Component
`StringCompareValue`	Text matching	Text input with operators
`NumericRangeValue`	Price ranges	Min/max inputs
`DateRangeValue`	Date filtering	Date pickers
`MultiDropDown`	Multiple selection	Dropdown with checkboxes
`BooleanValue`	True/false	Yes/No selector

For complete filter documentation, see Query Filters and Search.

Create src/query-library/searchproductswithfilters.json:

{
  "key": "searchproductswithfilters",
  "name": "searchProductsWithFilters",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/product/search",
    "bodyParams": "{\n  \"$select\": [\n    \"id\",\n    \"sku\",\n    \"name\",\n    \"description\",\n    \"brand_id\",\n    \"brand.name\",\n    \"category_id\",\n    \"category.name\",\n    \"price\",\n    \"stock_quantity\",\n    \"status\",\n    \"is_featured\",\n    \"rating\",\n    \"review_count\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false,\n    \"$and\": [\n      {\n        \"$or\": [\n          { \"name\": { \"$ilike\": \"%{{searchTerm}}%\" } },\n          { \"sku\": { \"$ilike\": \"%{{searchTerm}}%\" } },\n          { \"description\": { \"$ilike\": \"%{{searchTerm}}%\" } }\n        ]\n      },\n      {{#if categoryId}}{ \"category_id\": {{categoryId}} },{{/if}}\n      {{#if brandId}}{ \"brand_id\": {{brandId}} },{{/if}}\n      {{#if status}}{ \"status\": \"{{status}}\" },{{/if}}\n      {{#if minPrice}}{ \"price\": { \"$gte\": {{minPrice}} } },{{/if}}\n      {{#if maxPrice}}{ \"price\": { \"$lte\": {{maxPrice}} } },{{/if}}\n      {{#if minRating}}{ \"rating\": { \"$gte\": {{minRating}} } },{{/if}}\n      {{#if inStock}}{ \"stock_quantity\": { \"$gt\": 0 } },{{/if}}\n      {{#if featured}}{ \"is_featured\": true },{{/if}}\n      { \"id\": { \"$ne\": null } }\n    ]\n  },\n  \"$withRelated\": [\n    \"brand(notDeleted)\",\n    \"category(notDeleted)\",\n    \"tags(notDeleted)\"\n  ],\n  \"$sort\": {\n    {{#if sortBy}}\n      {{#eq sortBy \"price_asc\"}}\"price\": \"asc\"{{/eq}}\n      {{#eq sortBy \"price_desc\"}}\"price\": \"desc\"{{/eq}}\n      {{#eq sortBy \"rating\"}}\"rating\": \"desc\"{{/eq}}\n      {{#eq sortBy \"newest\"}}\"_created_at\": \"desc\"{{/eq}}\n      {{#eq sortBy \"name\"}}\"name\": \"asc\"{{/eq}}\n    {{else}}\n      \"is_featured\": \"desc\",\n      \"rating\": \"desc\"\n    {{/if}}\n  },\n  \"$limit\": {{limit}},\n  \"$offset\": {{offset}}\n}",
    "queryParams": {}
  },
  "userParams": {
    "searchTerm": "",
    "categoryId": null,
    "brandId": null,
    "status": null,
    "minPrice": null,
    "maxPrice": null,
    "minRating": null,
    "inStock": false,
    "featured": false,
    "sortBy": "relevance",
    "limit": 20,
    "offset": 0
  }
}

Filter Types:

Category filter - Filter by category
Brand filter - Filter by brand
Price range - Min/max price filtering
Rating filter - Minimum rating threshold
Stock filter - In-stock only
Status filter - Active, discontinued, etc.
Featured filter - Featured products only
Dynamic sorting - Multiple sort options

Step 4: Create Search Screen

Create src/screens/product_search.json:

{
  "head": {
    "title": "Search Products",
    "key": "product_search",
    "route": "product-search",
    "app": "products",
    "icon": "SearchOutlined"
  },
  "screenType": "listView",
  "listQuery": "searchproductswithfilters",
  "entityKey": "product",
  "searchConfig": {
    "enabled": true,
    "placeholder": "Search products by name, SKU, or description...",
    "debounceMs": 300
  },
  "columnsConfiguration": [
    {
      "field": "sku",
      "label": "SKU",
      "value": "{{sku}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/products/product-detail/{{id}}"
      }
    },
    {
      "field": "name",
      "label": "Product Name",
      "value": "{{name}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/products/product-detail/{{id}}",
        "highlight": true
      }
    },
    {
      "field": "brand",
      "label": "Brand",
      "value": "{{brand.name}}",
      "renderAs": "Text"
    },
    {
      "field": "category",
      "label": "Category",
      "value": "{{category.name}}",
      "renderAs": "Tag",
      "options": {
        "color": "blue"
      }
    },
    {
      "field": "price",
      "label": "Price",
      "value": "{{price}}",
      "renderAs": "Decimal",
      "options": {
        "prefix": "$",
        "afterDecimalPoint": 2
      }
    },
    {
      "field": "rating",
      "label": "Rating",
      "value": "{{rating}}",
      "renderAs": "Decimal",
      "options": {
        "afterDecimalPoint": 1,
        "suffix": " stars"
      }
    },
    {
      "field": "stock_quantity",
      "label": "Stock",
      "value": "{{stock_quantity}}",
      "renderAs": "Number",
      "options": {
        "colorRules": [
          { "condition": "{{stock_quantity}} === 0", "color": "red" },
          { "condition": "{{stock_quantity}} < 10", "color": "orange" },
          { "condition": "{{stock_quantity}} >= 10", "color": "green" }
        ]
      }
    },
    {
      "field": "status",
      "label": "Status",
      "value": "{{status}}",
      "renderAs": "EnumTags",
      "options": {
        "enumColor": {
          "Active": "green",
          "Discontinued": "red",
          "Out of Stock": "orange",
          "Coming Soon": "blue"
        }
      }
    }
  ],
  "filters": [
    {
      "field": "category_id",
      "label": "Category",
      "type": "dropdown",
      "entityKey": "category",
      "allowClear": true
    },
    {
      "field": "brand_id",
      "label": "Brand",
      "type": "dropdown",
      "entityKey": "brand",
      "allowClear": true
    },
    {
      "field": "status",
      "label": "Status",
      "type": "dropdown",
      "options": ["Active", "Discontinued", "Out of Stock", "Coming Soon"],
      "allowClear": true
    },
    {
      "field": "price_range",
      "label": "Price Range",
      "type": "range",
      "minField": "minPrice",
      "maxField": "maxPrice",
      "step": 10
    },
    {
      "field": "min_rating",
      "label": "Minimum Rating",
      "type": "rating",
      "max": 5
    },
    {
      "field": "in_stock",
      "label": "In Stock Only",
      "type": "checkbox"
    },
    {
      "field": "featured",
      "label": "Featured Products",
      "type": "checkbox"
    }
  ],
  "sortOptions": [
    {
      "label": "Relevance",
      "value": "relevance"
    },
    {
      "label": "Price: Low to High",
      "value": "price_asc"
    },
    {
      "label": "Price: High to Low",
      "value": "price_desc"
    },
    {
      "label": "Highest Rated",
      "value": "rating"
    },
    {
      "label": "Newest",
      "value": "newest"
    },
    {
      "label": "Name A-Z",
      "value": "name"
    }
  ]
}

Step 5: Implement Autocomplete/Typeahead

Create an action type for autocomplete suggestions.

Create src/action-types/productAutocomplete.ts:

import { Record, String, Number } from 'runtypes';
import { S9AutomationActionType } from '@april9au/stack9-sdk';

const ProductAutocompleteParams = Record({
  query: String,
  limit: Number.optional(),
});

/**
 * Product Autocomplete
 *
 * Provides fast autocomplete suggestions for product search
 */
export class ProductAutocomplete extends S9AutomationActionType {
  key = 'product_autocomplete';
  name = 'Product Autocomplete';
  description = 'Get autocomplete suggestions for product search';

  async exec(params: any) {
    const { query, limit = 10 } = ProductAutocompleteParams.check(params);
    const { db, logger } = this.context;

    try {
      // Return empty if query too short
      if (query.length < 2) {
        return {
          success: true,
          suggestions: [],
        };
      }

      // Search products
      const products = await db('products')
        .select(
          'id',
          'sku',
          'name',
          'brand_id',
          'price',
          'category_id'
        )
        .leftJoin('brands', 'products.brand_id', 'brands.id')
        .leftJoin('categories', 'products.category_id', 'categories.id')
        .select('brands.name as brand_name')
        .select('categories.name as category_name')
        .where('products._is_deleted', false)
        .where(function() {
          this.where('products.name', 'ilike', `%${query}%`)
            .orWhere('products.sku', 'ilike', `%${query}%`)
            .orWhere('brands.name', 'ilike', `%${query}%`);
        })
        .orderByRaw(`
          CASE
            WHEN products.name ILIKE '${query}%' THEN 1
            WHEN products.sku ILIKE '${query}%' THEN 2
            WHEN products.name ILIKE '%${query}%' THEN 3
            ELSE 4
          END
        `)
        .orderBy('products.is_featured', 'desc')
        .orderBy('products.rating', 'desc')
        .limit(limit);

      // Format suggestions
      const suggestions = products.map(product => ({
        id: product.id,
        type: 'product',
        title: product.name,
        subtitle: `${product.sku} - ${product.brand_name}`,
        category: product.category_name,
        price: product.price,
        url: `/products/product-detail/${product.id}`,
      }));

      // Also get matching categories
      const categories = await db('categories')
        .select('id', 'name', 'slug')
        .where('_is_deleted', false)
        .where('name', 'ilike', `%${query}%`)
        .limit(3);

      const categorySuggestions = categories.map(cat => ({
        id: cat.id,
        type: 'category',
        title: cat.name,
        subtitle: 'Category',
        url: `/products/product-search?category_id=${cat.id}`,
      }));

      // Also get matching brands
      const brands = await db('brands')
        .select('id', 'name')
        .where('_is_deleted', false)
        .where('name', 'ilike', `%${query}%`)
        .limit(3);

      const brandSuggestions = brands.map(brand => ({
        id: brand.id,
        type: 'brand',
        title: brand.name,
        subtitle: 'Brand',
        url: `/products/product-search?brand_id=${brand.id}`,
      }));

      return {
        success: true,
        suggestions: [
          ...categorySuggestions,
          ...brandSuggestions,
          ...suggestions,
        ],
      };
    } catch (error) {
      logger.error(`Autocomplete error: ${error.message}`);
      return {
        success: false,
        suggestions: [],
        error: error.message,
      };
    }
  }
}

Create Autocomplete Webhook

Create src/automations/webhook_product_autocomplete.json:

{
  "name": "webhook_product_autocomplete",
  "label": "Product Autocomplete Webhook",
  "description": "Provides autocomplete suggestions for product search",
  "trigger": {
    "type": "webhook",
    "method": "GET",
    "path": "/product-autocomplete"
  },
  "actions": [
    {
      "name": "get_suggestions",
      "actionType": "product_autocomplete",
      "params": {
        "query": "{{trigger.query.q}}",
        "limit": "{{trigger.query.limit}}"
      }
    }
  ]
}

Usage:

# Get autocomplete suggestions
curl "http://localhost:3000/webhooks/product-autocomplete?q=laptop&limit=10"

Step 6: Optimize Search Performance

Add Database Indexes

Create src/migrations/add_search_indexes.sql:

-- Full-text search indexes
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
CREATE INDEX idx_products_description_trgm ON products USING gin(description gin_trgm_ops);
CREATE INDEX idx_products_sku_trgm ON products USING gin(sku gin_trgm_ops);

-- Filter indexes
CREATE INDEX idx_products_category_id ON products(category_id) WHERE _is_deleted = false;
CREATE INDEX idx_products_brand_id ON products(brand_id) WHERE _is_deleted = false;
CREATE INDEX idx_products_status ON products(status) WHERE _is_deleted = false;
CREATE INDEX idx_products_price ON products(price) WHERE _is_deleted = false;
CREATE INDEX idx_products_rating ON products(rating DESC) WHERE _is_deleted = false;

-- Composite indexes for common filter combinations
CREATE INDEX idx_products_category_status ON products(category_id, status) WHERE _is_deleted = false;
CREATE INDEX idx_products_featured_rating ON products(is_featured DESC, rating DESC) WHERE _is_deleted = false;

-- Stock availability index
CREATE INDEX idx_products_in_stock ON products(stock_quantity) WHERE _is_deleted = false AND stock_quantity > 0;

Implement Search Cache

Create src/action-types/cachedProductSearch.ts:

import { Record, String, Number } from 'runtypes';
import { S9AutomationActionType } from '@april9au/stack9-sdk';
import crypto from 'crypto';

/**
 * Cached Product Search
 *
 * Implements caching layer for frequently searched queries
 */
export class CachedProductSearch extends S9AutomationActionType {
  key = 'cached_product_search';
  name = 'Cached Product Search';
  description = 'Product search with result caching';

  private getCacheKey(params: any): string {
    const normalized = JSON.stringify(params, Object.keys(params).sort());
    return crypto.createHash('md5').update(normalized).digest('hex');
  }

  async exec(params: any) {
    const { db, logger, cache } = this.context;

    try {
      // Generate cache key from search parameters
      const cacheKey = `product_search:${this.getCacheKey(params)}`;

      // Check cache first
      const cached = await cache.get(cacheKey);
      if (cached) {
        logger.info('Search results returned from cache');
        return {
          success: true,
          results: JSON.parse(cached),
          cached: true,
        };
      }

      // Execute search query
      const searchTerm = params.searchTerm || '';
      const categoryId = params.categoryId;
      const brandId = params.brandId;
      const minPrice = params.minPrice;
      const maxPrice = params.maxPrice;
      const limit = params.limit || 20;
      const offset = params.offset || 0;

      let query = db('products')
        .select(
          'products.*',
          'brands.name as brand_name',
          'categories.name as category_name'
        )
        .leftJoin('brands', 'products.brand_id', 'brands.id')
        .leftJoin('categories', 'products.category_id', 'categories.id')
        .where('products._is_deleted', false);

      // Apply search term
      if (searchTerm) {
        query = query.where(function() {
          this.where('products.name', 'ilike', `%${searchTerm}%`)
            .orWhere('products.sku', 'ilike', `%${searchTerm}%`)
            .orWhere('products.description', 'ilike', `%${searchTerm}%`);
        });
      }

      // Apply filters
      if (categoryId) {
        query = query.where('products.category_id', categoryId);
      }
      if (brandId) {
        query = query.where('products.brand_id', brandId);
      }
      if (minPrice) {
        query = query.where('products.price', '>=', minPrice);
      }
      if (maxPrice) {
        query = query.where('products.price', '<=', maxPrice);
      }

      // Apply sorting
      query = query
        .orderBy('products.is_featured', 'desc')
        .orderBy('products.rating', 'desc')
        .orderBy('products.name', 'asc');

      // Get total count
      const countQuery = query.clone().clearSelect().clearOrder().count('* as count');
      const [{ count }] = await countQuery;

      // Apply pagination
      const results = await query.limit(limit).offset(offset);

      const response = {
        results,
        total: parseInt(count as string),
        limit,
        offset,
        hasMore: offset + limit < parseInt(count as string),
      };

      // Cache results for 5 minutes
      await cache.set(cacheKey, JSON.stringify(response), 300);

      logger.info(`Search executed, found ${count} results`);

      return {
        success: true,
        ...response,
        cached: false,
      };
    } catch (error) {
      logger.error(`Search error: ${error.message}`);
      throw error;
    }
  }
}

Step 7: Elasticsearch Integration (Optional)

For large-scale search requirements, integrate Elasticsearch.

Configure Elasticsearch Connector

Create src/connectors/elasticsearch.json:

{
  "name": "elasticsearch",
  "type": "rest",
  "baseURL": "http://localhost:9200",
  "headers": {
    "Content-Type": "application/json"
  },
  "auth": {
    "type": "basic",
    "username": "{{env.ELASTICSEARCH_USERNAME}}",
    "password": "{{env.ELASTICSEARCH_PASSWORD}}"
  }
}

Create Elasticsearch Index Mapping

Create src/action-types/createProductIndex.ts:

import { S9AutomationActionType } from '@april9au/stack9-sdk';

/**
 * Create Product Index in Elasticsearch
 */
export class CreateProductIndex extends S9AutomationActionType {
  key = 'create_product_index';
  name = 'Create Product Index';
  description = 'Initialize Elasticsearch index for products';

  async exec(params: any) {
    const { connectors, logger } = this.context;

    try {
      const elasticsearch = connectors['elasticsearch'];

      // Create index with custom mapping
      await elasticsearch.call({
        method: 'PUT',
        path: '/products',
        body: {
          settings: {
            number_of_shards: 1,
            number_of_replicas: 1,
            analysis: {
              analyzer: {
                product_analyzer: {
                  type: 'custom',
                  tokenizer: 'standard',
                  filter: [
                    'lowercase',
                    'asciifolding',
                    'product_synonym',
                    'product_edge_ngram'
                  ]
                }
              },
              filter: {
                product_synonym: {
                  type: 'synonym',
                  synonyms: [
                    'laptop, notebook, computer',
                    'phone, mobile, smartphone',
                    'tv, television'
                  ]
                },
                product_edge_ngram: {
                  type: 'edge_ngram',
                  min_gram: 2,
                  max_gram: 15
                }
              }
            }
          },
          mappings: {
            properties: {
              id: { type: 'integer' },
              sku: {
                type: 'text',
                fields: {
                  keyword: { type: 'keyword' }
                }
              },
              name: {
                type: 'text',
                analyzer: 'product_analyzer',
                fields: {
                  keyword: { type: 'keyword' }
                }
              },
              description: {
                type: 'text',
                analyzer: 'product_analyzer'
              },
              brand_id: { type: 'integer' },
              brand_name: {
                type: 'text',
                fields: {
                  keyword: { type: 'keyword' }
                }
              },
              category_id: { type: 'integer' },
              category_name: {
                type: 'text',
                fields: {
                  keyword: { type: 'keyword' }
                }
              },
              price: { type: 'float' },
              stock_quantity: { type: 'integer' },
              status: { type: 'keyword' },
              is_featured: { type: 'boolean' },
              rating: { type: 'float' },
              review_count: { type: 'integer' },
              tags: { type: 'keyword' },
              created_at: { type: 'date' }
            }
          }
        }
      });

      logger.info('Elasticsearch product index created successfully');

      return {
        success: true,
        message: 'Product index created',
      };
    } catch (error) {
      logger.error(`Error creating index: ${error.message}`);
      throw error;
    }
  }
}

Index Products in Elasticsearch

Create src/action-types/indexProduct.ts:

import { Record, Number } from 'runtypes';
import { S9AutomationActionType } from '@april9au/stack9-sdk';

const IndexProductParams = Record({
  product_id: Number,
});

/**
 * Index Product in Elasticsearch
 */
export class IndexProduct extends S9AutomationActionType {
  key = 'index_product';
  name = 'Index Product';
  description = 'Index a product in Elasticsearch';

  async exec(params: any) {
    const { product_id } = IndexProductParams.check(params);
    const { db, connectors, logger } = this.context;

    try {
      // Fetch product with relationships
      const product = await db('products')
        .select(
          'products.*',
          'brands.name as brand_name',
          'categories.name as category_name'
        )
        .leftJoin('brands', 'products.brand_id', 'brands.id')
        .leftJoin('categories', 'products.category_id', 'categories.id')
        .where('products.id', product_id)
        .where('products._is_deleted', false)
        .first();

      if (!product) {
        return {
          success: false,
          message: 'Product not found',
        };
      }

      // Get tags
      const tags = await db('product_tag')
        .join('tags', 'product_tag.tag_id', 'tags.id')
        .select('tags.name')
        .where('product_tag.product_id', product_id)
        .where('tags._is_deleted', false);

      // Prepare document for Elasticsearch
      const document = {
        id: product.id,
        sku: product.sku,
        name: product.name,
        description: product.description,
        brand_id: product.brand_id,
        brand_name: product.brand_name,
        category_id: product.category_id,
        category_name: product.category_name,
        price: product.price,
        stock_quantity: product.stock_quantity,
        status: product.status,
        is_featured: product.is_featured,
        rating: product.rating,
        review_count: product.review_count,
        tags: tags.map(t => t.name),
        created_at: product._created_at,
      };

      // Index in Elasticsearch
      const elasticsearch = connectors['elasticsearch'];
      await elasticsearch.call({
        method: 'PUT',
        path: `/products/_doc/${product_id}`,
        body: document
      });

      logger.info(`Product ${product_id} indexed in Elasticsearch`);

      return {
        success: true,
        message: 'Product indexed successfully',
      };
    } catch (error) {
      logger.error(`Error indexing product: ${error.message}`);
      throw error;
    }
  }
}

Search with Elasticsearch

Create src/action-types/elasticsearchProductSearch.ts:

import { Record, String, Number } from 'runtypes';
import { S9AutomationActionType } from '@april9au/stack9-sdk';

/**
 * Elasticsearch Product Search
 *
 * Advanced search using Elasticsearch with fuzzy matching,
 * boosting, and advanced relevance scoring
 */
export class ElasticsearchProductSearch extends S9AutomationActionType {
  key = 'elasticsearch_product_search';
  name = 'Elasticsearch Product Search';
  description = 'Search products using Elasticsearch';

  async exec(params: any) {
    const { connectors, logger } = this.context;

    try {
      const {
        query = '',
        categoryId,
        brandId,
        minPrice,
        maxPrice,
        status,
        inStock,
        featured,
        from = 0,
        size = 20,
      } = params;

      const elasticsearch = connectors['elasticsearch'];

      // Build Elasticsearch query
      const must: any[] = [];
      const filter: any[] = [];

      // Text search with boosting
      if (query) {
        must.push({
          multi_match: {
            query: query,
            fields: [
              'name^3',           // Name is most important
              'sku^2',            // SKU is important
              'brand_name^2',     // Brand name is important
              'description',      // Description least important
              'category_name'
            ],
            type: 'best_fields',
            fuzziness: 'AUTO',
            prefix_length: 2,
            operator: 'or'
          }
        });
      }

      // Apply filters
      if (categoryId) {
        filter.push({ term: { category_id: categoryId } });
      }
      if (brandId) {
        filter.push({ term: { brand_id: brandId } });
      }
      if (status) {
        filter.push({ term: { status: status } });
      }
      if (featured) {
        filter.push({ term: { is_featured: true } });
      }
      if (inStock) {
        filter.push({ range: { stock_quantity: { gt: 0 } } });
      }

      // Price range filter
      if (minPrice || maxPrice) {
        const priceRange: any = {};
        if (minPrice) priceRange.gte = minPrice;
        if (maxPrice) priceRange.lte = maxPrice;
        filter.push({ range: { price: priceRange } });
      }

      // Build search request
      const searchBody = {
        from,
        size,
        query: {
          bool: {
            must: must.length > 0 ? must : { match_all: {} },
            filter: filter.length > 0 ? filter : undefined,
          }
        },
        sort: [
          { is_featured: { order: 'desc' } },
          '_score',
          { rating: { order: 'desc' } },
          { name: { order: 'asc' } }
        ],
        highlight: {
          fields: {
            name: {},
            description: {}
          }
        },
        aggs: {
          categories: {
            terms: {
              field: 'category_name.keyword',
              size: 20
            }
          },
          brands: {
            terms: {
              field: 'brand_name.keyword',
              size: 20
            }
          },
          price_ranges: {
            range: {
              field: 'price',
              ranges: [
                { to: 50, key: 'Under $50' },
                { from: 50, to: 100, key: '$50 - $100' },
                { from: 100, to: 200, key: '$100 - $200' },
                { from: 200, to: 500, key: '$200 - $500' },
                { from: 500, key: 'Over $500' }
              ]
            }
          },
          avg_rating: {
            avg: { field: 'rating' }
          }
        }
      };

      // Execute search
      const response = await elasticsearch.call({
        method: 'POST',
        path: '/products/_search',
        body: searchBody
      });

      // Format results
      const hits = response.hits.hits;
      const results = hits.map((hit: any) => ({
        ...hit._source,
        score: hit._score,
        highlights: hit.highlight
      }));

      logger.info(`Elasticsearch search found ${response.hits.total.value} results`);

      return {
        success: true,
        results,
        total: response.hits.total.value,
        from,
        size,
        facets: {
          categories: response.aggregations.categories.buckets,
          brands: response.aggregations.brands.buckets,
          priceRanges: response.aggregations.price_ranges.buckets,
          avgRating: response.aggregations.avg_rating.value
        }
      };
    } catch (error) {
      logger.error(`Elasticsearch search error: ${error.message}`);
      throw error;
    }
  }
}

Auto-Index Products on Create/Update

Create src/automations/when_product_changed.json:

{
  "name": "when_product_changed",
  "label": "When Product Changed",
  "description": "Sync product to Elasticsearch on create/update",
  "trigger": {
    "type": "afterCreate,afterUpdate",
    "entity": "product"
  },
  "actions": [
    {
      "name": "index_in_elasticsearch",
      "actionType": "index_product",
      "params": {
        "product_id": "{{trigger.entity.id}}"
      }
    }
  ]
}

Step 8: Advanced Search Patterns

Pattern 1: Multi-Field Weighted Search

// Implement search scoring based on field matches
export class WeightedSearch extends S9AutomationActionType {
  async exec(params: any) {
    const { query } = params;
    const { db } = this.context;

    const results = await db('products')
      .select('*')
      .selectRaw(`
        CASE
          WHEN name ILIKE '${query}%' THEN 100
          WHEN sku = '${query}' THEN 90
          WHEN name ILIKE '%${query}%' THEN 50
          WHEN description ILIKE '%${query}%' THEN 20
          ELSE 10
        END as relevance_score
      `)
      .where(function() {
        this.where('name', 'ilike', `%${query}%`)
          .orWhere('sku', 'ilike', `%${query}%`)
          .orWhere('description', 'ilike', `%${query}%`);
      })
      .orderBy('relevance_score', 'desc')
      .orderBy('is_featured', 'desc')
      .orderBy('rating', 'desc');

    return { success: true, results };
  }
}

Pattern 2: Search with Typo Tolerance

// Use PostgreSQL's similarity extension for fuzzy matching
export class FuzzySearch extends S9AutomationActionType {
  async exec(params: any) {
    const { query } = params;
    const { db } = this.context;

    // Enable pg_trgm extension first
    // CREATE EXTENSION IF NOT EXISTS pg_trgm;

    const results = await db('products')
      .select('*')
      .selectRaw(`
        GREATEST(
          similarity(name, ?),
          similarity(sku, ?)
        ) as similarity_score
      `, [query, query])
      .where(function() {
        this.whereRaw('name % ?', [query])
          .orWhereRaw('sku % ?', [query]);
      })
      .orderBy('similarity_score', 'desc')
      .limit(20);

    return { success: true, results };
  }
}

Pattern 3: Search with Synonyms

// Expand search terms with synonyms
export class SynonymSearch extends S9AutomationActionType {
  private synonyms: Record<string, string[]> = {
    'laptop': ['notebook', 'computer', 'macbook'],
    'phone': ['mobile', 'smartphone', 'cell'],
    'tv': ['television', 'screen', 'display'],
  };

  private expandQuery(query: string): string[] {
    const terms = [query];
    const lowerQuery = query.toLowerCase();

    // Add synonyms if found
    for (const [key, values] of Object.entries(this.synonyms)) {
      if (lowerQuery.includes(key)) {
        terms.push(...values);
      }
    }

    return terms;
  }

  async exec(params: any) {
    const { query } = params;
    const { db } = this.context;

    const expandedTerms = this.expandQuery(query);

    const results = await db('products')
      .select('*')
      .where(function() {
        expandedTerms.forEach(term => {
          this.orWhere('name', 'ilike', `%${term}%`)
            .orWhere('description', 'ilike', `%${term}%`);
        });
      })
      .orderBy('is_featured', 'desc')
      .orderBy('rating', 'desc');

    return { success: true, results, expandedTerms };
  }
}

Pattern 4: Category Tree Search

// Search within category and all subcategories
export class CategoryTreeSearch extends S9AutomationActionType {
  async getCategoryTree(categoryId: number, db: any): Promise<number[]> {
    const categories = [categoryId];

    // Recursively get all subcategories
    const getChildren = async (parentId: number) => {
      const children = await db('categories')
        .select('id')
        .where('parent_id', parentId)
        .where('_is_deleted', false);

      for (const child of children) {
        categories.push(child.id);
        await getChildren(child.id);
      }
    };

    await getChildren(categoryId);
    return categories;
  }

  async exec(params: any) {
    const { query, categoryId } = params;
    const { db } = this.context;

    // Get all categories in tree
    const categoryIds = await this.getCategoryTree(categoryId, db);

    const results = await db('products')
      .select('*')
      .whereIn('category_id', categoryIds)
      .where(function() {
        if (query) {
          this.where('name', 'ilike', `%${query}%`)
            .orWhere('description', 'ilike', `%${query}%`);
        }
      })
      .where('_is_deleted', false);

    return { success: true, results, categoryIds };
  }
}

Step 9: Search Analytics

Track search queries and results to improve search relevance.

Create src/entities/custom/search_log.json:

{
  "head": {
    "name": "SearchLog",
    "key": "search_log",
    "pluralisedName": "search_logs"
  },
  "fields": [
    {
      "label": "Query",
      "key": "query",
      "type": "TextField",
      "index": true
    },
    {
      "label": "Results Count",
      "key": "results_count",
      "type": "NumericField"
    },
    {
      "label": "Execution Time (ms)",
      "key": "execution_time",
      "type": "NumericField"
    },
    {
      "label": "User ID",
      "key": "user_id",
      "type": "NumericField",
      "index": true
    },
    {
      "label": "Filters Applied",
      "key": "filters",
      "type": "TextField"
    },
    {
      "label": "Has Results",
      "key": "has_results",
      "type": "Checkbox",
      "index": true
    }
  ]
}

Create src/action-types/logSearch.ts:

import { S9AutomationActionType } from '@april9au/stack9-sdk';

/**
 * Log Search Query
 *
 * Tracks search queries for analytics and optimization
 */
export class LogSearch extends S9AutomationActionType {
  key = 'log_search';
  name = 'Log Search';
  description = 'Log search query for analytics';

  async exec(params: any) {
    const { query, resultsCount, executionTime, filters, userId } = params;
    const { db } = this.context;

    try {
      await db('search_logs').insert({
        query: query,
        results_count: resultsCount,
        execution_time: executionTime,
        user_id: userId,
        filters: JSON.stringify(filters),
        has_results: resultsCount > 0,
        _created_at: new Date(),
        _updated_at: new Date(),
      });

      return {
        success: true,
        message: 'Search logged',
      };
    } catch (error) {
      // Don't fail if logging fails
      this.context.logger.error(`Error logging search: ${error.message}`);
      return {
        success: true,
        message: 'Search logging failed (non-critical)',
      };
    }
  }
}

Best Practices

Performance

Index strategically - Index fields used in WHERE clauses and sorting
Use LIMIT and OFFSET - Always paginate results
**Avoid SELECT *** - Select only needed fields
Cache frequent searches - Cache popular queries
Use connection pooling - Reuse database connections

Search Quality

Weight fields appropriately - Name > SKU > Description
Handle typos - Use fuzzy matching (pg_trgm or Elasticsearch)
Support synonyms - Expand search terms
Boost popular items - Factor in rating, reviews, sales
Show relevant filters - Display only filters with results

User Experience

Debounce search input - Wait 300ms after typing stops
Show autocomplete quickly - Return suggestions fast
Highlight matches - Bold matched text in results
Display facet counts - Show result count per filter
Remember search state - Preserve filters in URL

Analytics

Track queries - Log all search queries
Monitor zero-result searches - Find gaps in inventory
Analyze popular searches - Optimize for common queries
Track click-through rate - Measure search relevance
A/B test ranking - Experiment with different algorithms

Troubleshooting

Slow Search Queries

Problem: Search taking too long

Solutions:

Check indexes exist: \d products in psql
Analyze query plan: EXPLAIN ANALYZE SELECT ...
Add missing indexes on filter fields
Reduce number of JOINs
Consider Elasticsearch for large datasets

No Results Found

Problem: Search returns empty results

Solutions:

Check search term spelling
Verify data exists in database
Test with simpler queries
Check case sensitivity (ilike vs like)
Ensure _is_deleted = false filter is correct

Autocomplete Not Working

Problem: Autocomplete suggestions not appearing

Solutions:

Check webhook is registered
Verify minimum query length (usually 2 chars)
Check API endpoint is accessible
Look for JavaScript errors in browser console
Verify debounce delay is not too long

Elasticsearch Connection Failed

Problem: Cannot connect to Elasticsearch

Solutions:

Verify Elasticsearch is running: curl localhost:9200
Check credentials in connector configuration
Verify network connectivity
Check firewall rules
Review Elasticsearch logs

Irrelevant Results

Problem: Search returns irrelevant products

Solutions:

Adjust field weights (boost important fields)
Improve ranking algorithm
Add synonyms for common terms
Filter out discontinued/out-of-stock products
Factor in popularity metrics (rating, sales)

Next Steps

You've implemented comprehensive search functionality! Continue learning:

Performance Optimization - Optimize search performance
Building Workflows - Automate search indexing
Custom Queries - Advanced query patterns
Elasticsearch Documentation - Deep dive into Elasticsearch

Summary

In this guide, you learned how to:

Implement basic full-text search with PostgreSQL
Add faceted filtering with multiple criteria
Create autocomplete/typeahead functionality
Optimize search performance with indexes and caching
Integrate Elasticsearch for advanced search
Implement custom ranking algorithms
Track search analytics
Handle edge cases and troubleshooting

Search capabilities implemented:

Full-text search across multiple fields
Category and brand filtering
Price range filtering
Rating and stock filters
Multiple sort options
Fast autocomplete suggestions
Result highlighting
Faceted navigation
Search caching
Elasticsearch integration (optional)

What You'll Build​

Prerequisites​

Understanding Search in Stack9​

1. Basic Search (Built-in)​

2. Advanced Search with Elasticsearch​

Step 1: Create Searchable Entities​

Product Entity with Search Fields​

Supporting Entities​

Step 2: Implement Basic Search Query​

Step 3: Add Faceted Filtering​

Understanding Filter Types​

Step 4: Create Search Screen​

Step 5: Implement Autocomplete/Typeahead​

Create Autocomplete Webhook​

Step 6: Optimize Search Performance​

Add Database Indexes​

Implement Search Cache​

Step 7: Elasticsearch Integration (Optional)​

Configure Elasticsearch Connector​

Create Elasticsearch Index Mapping​

Index Products in Elasticsearch​

Search with Elasticsearch​

Auto-Index Products on Create/Update​

Step 8: Advanced Search Patterns​

Pattern 1: Multi-Field Weighted Search​

Pattern 2: Search with Typo Tolerance​

Pattern 3: Search with Synonyms​

Pattern 4: Category Tree Search​

Step 9: Search Analytics​

Best Practices​

Performance​

Search Quality​

User Experience​

Analytics​

Troubleshooting​

Slow Search Queries​

No Results Found​

Autocomplete Not Working​

Elasticsearch Connection Failed​

Irrelevant Results​

Next Steps​

Summary​

What You'll Build

Prerequisites

Understanding Search in Stack9

1. Basic Search (Built-in)

2. Advanced Search with Elasticsearch

Step 1: Create Searchable Entities

Product Entity with Search Fields

Supporting Entities

Step 2: Implement Basic Search Query

Step 3: Add Faceted Filtering

Understanding Filter Types

Step 4: Create Search Screen

Step 5: Implement Autocomplete/Typeahead

Create Autocomplete Webhook

Step 6: Optimize Search Performance

Add Database Indexes

Implement Search Cache

Step 7: Elasticsearch Integration (Optional)

Configure Elasticsearch Connector

Create Elasticsearch Index Mapping

Index Products in Elasticsearch

Search with Elasticsearch

Auto-Index Products on Create/Update

Step 8: Advanced Search Patterns

Pattern 1: Multi-Field Weighted Search

Pattern 2: Search with Typo Tolerance

Pattern 3: Search with Synonyms

Pattern 4: Category Tree Search

Step 9: Search Analytics

Best Practices

Performance

Search Quality

User Experience

Analytics

Troubleshooting

Slow Search Queries

No Results Found

Autocomplete Not Working

Elasticsearch Connection Failed

Irrelevant Results

Next Steps

Summary