Hybrid Search

@api({ httpMethod: 'POST' })
async searchProducts(query: string) {
  const embedding = await Embedding.embedOne(query, 'voyage', 'query');

  const results = await this.getPuri().raw(`
    SELECT name, description,
      1 - (embedding <=> ?) AS similarity
    FROM products
    ORDER BY embedding <=> ?
    LIMIT 10
  `, [
    JSON.stringify(embedding.embedding),
    JSON.stringify(embedding.embedding),
  ]);

  return results.rows;
}

// migrations/20240101_add_fts.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.table('products', (table) => {
    table.specificType('search_vector', 'tsvector');
  });

  // Generate initial data
  await knex.raw(`
    UPDATE products
    SET search_vector =
      setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
      setweight(to_tsvector('simple', coalesce(description, '')), 'B')
  `);

  // GIN index
  await knex.raw(`
    CREATE INDEX idx_products_search
    ON products USING GIN (search_vector)
  `);
}

export async function up(knex: Knex): Promise<void> {
  // Trigger function
  await knex.raw(`
    CREATE FUNCTION products_search_trigger() RETURNS trigger AS $$
    BEGIN
      NEW.search_vector :=
        setweight(to_tsvector('simple', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.description, '')), 'B');
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
  `);

  // Create trigger
  await knex.raw(`
    CREATE TRIGGER tsvector_update
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION products_search_trigger();
  `);
}

import { BaseModel, api } from "sonamu";
import { Embedding } from "sonamu/vector";

class ProductModelClass extends BaseModel {
  @api({ httpMethod: 'POST' })
  async hybridSearch(
    query: string,
    vectorWeight: number = 0.7,
    ftsWeight: number = 0.3,
    limit: number = 10
  ) {
    // 1. Query embedding
    const embedding = await Embedding.embedOne(query, 'voyage', 'query');

    // 2. Hybrid search SQL
    const results = await this.getPuri().raw(`
      WITH vector_results AS (
        SELECT
          id,
          1 - (embedding <=> ?) AS vector_score
        FROM products
        WHERE embedding IS NOT NULL
      ),
      fts_results AS (
        SELECT
          id,
          ts_rank(search_vector, plainto_tsquery('simple', ?)) AS fts_score
        FROM products
        WHERE search_vector @@ plainto_tsquery('simple', ?)
      )
      SELECT
        p.id,
        p.name,
        p.description,
        p.price,
        COALESCE(v.vector_score, 0) AS vector_score,
        COALESCE(f.fts_score, 0) AS fts_score,
        (COALESCE(v.vector_score, 0) * ? + COALESCE(f.fts_score, 0) * ?) AS hybrid_score
      FROM products p
      LEFT JOIN vector_results v ON p.id = v.id
      LEFT JOIN fts_results f ON p.id = f.id
      WHERE v.id IS NOT NULL OR f.id IS NOT NULL
      ORDER BY hybrid_score DESC
      LIMIT ?
    `, [
      JSON.stringify(embedding.embedding),  // vector
      query,  // fts (1)
      query,  // fts (2)
      vectorWeight,   // 0.7
      ftsWeight,      // 0.3
      limit,
    ]);

    return results.rows.map(row => ({
      id: row.id,
      name: row.name,
      price: row.price,
      vectorScore: parseFloat(row.vector_score.toFixed(3)),
      ftsScore: parseFloat(row.fts_score.toFixed(3)),
      hybridScore: parseFloat(row.hybrid_score.toFixed(3)),
    }));
  }
}

vectorWeight: 0.7,
ftsWeight: 0.3

vectorWeight: 0.9,
ftsWeight: 0.1

vectorWeight: 0.3,
ftsWeight: 0.7

@api({ httpMethod: 'POST' })
async smartSearch(query: string, limit: number = 10) {
  // Auto-adjust based on query length
  let vectorWeight = 0.7;
  let ftsWeight = 0.3;

  if (query.length < 10) {
    // Short query: keyword priority
    // e.g., "MBP", "SKU-123"
    vectorWeight = 0.3;
    ftsWeight = 0.7;
  } else if (query.length > 50) {
    // Long query: semantic priority
    // e.g., "Please recommend a laptop..."
    vectorWeight = 0.8;
    ftsWeight = 0.2;
  }

  return await this.hybridSearch(query, vectorWeight, ftsWeight, limit);
}

// migrations/20240101_products.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('products', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.text('description');
    table.string('sku').unique();
    table.decimal('price', 10, 2);

    // Vector + FTS
    table.specificType('embedding', 'vector(1024)');
    table.specificType('search_vector', 'tsvector');

    table.timestamps(true, true);
  });

  // Indexes
  await knex.raw(`
    CREATE INDEX idx_products_embedding
    ON products USING hnsw (embedding vector_cosine_ops)
  `);

  await knex.raw(`
    CREATE INDEX idx_products_search
    ON products USING GIN (search_vector)
  `);

  // FTS trigger
  await knex.raw(`
    CREATE FUNCTION products_search_trigger() RETURNS trigger AS $$
    BEGIN
      NEW.search_vector :=
        setweight(to_tsvector('simple', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.description, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(NEW.sku, '')), 'A');
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER tsvector_update
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION products_search_trigger();
  `);
}

@api({ httpMethod: 'POST' })
async addProduct(
  name: string,
  description: string,
  sku: string,
  price: number
) {
  // Generate embedding
  const embedding = await Embedding.embedOne(
    `${name}\n\n${description}`,
    'voyage',
    'document'
  );

  // Save (search_vector is auto-generated by trigger)
  const product = await this.saveOne({
    name,
    description,
    sku,
    price,
    embedding: embedding.embedding,
  });

  return product;
}

@api({ httpMethod: 'POST' })
async searchProducts(
  query: string,
  filters: {
    minPrice?: number;
    maxPrice?: number;
    category?: string;
  } = {},
  limit: number = 20
) {
  const embedding = await Embedding.embedOne(query, 'voyage', 'query');

  // Filter conditions
  const conditions: string[] = [];
  const params: any[] = [
    JSON.stringify(embedding.embedding),
    query,
    query,
    0.6,  // vectorWeight
    0.4,  // ftsWeight
  ];

  if (filters.minPrice) {
    conditions.push(`p.price >= ?`);
    params.push(filters.minPrice);
  }

  if (filters.maxPrice) {
    conditions.push(`p.price <= ?`);
    params.push(filters.maxPrice);
  }

  if (filters.category) {
    conditions.push(`p.category = ?`);
    params.push(filters.category);
  }

  const whereClause = conditions.length > 0
    ? `AND ${conditions.join(' AND ')}`
    : '';

  params.push(limit);

  const results = await this.getPuri().raw(`
    WITH vector_results AS (
      SELECT
        id,
        1 - (embedding <=> ?) AS vector_score
      FROM products
      WHERE embedding IS NOT NULL
    ),
    fts_results AS (
      SELECT
        id,
        ts_rank(search_vector, plainto_tsquery('simple', ?)) AS fts_score
      FROM products
      WHERE search_vector @@ plainto_tsquery('simple', ?)
    )
    SELECT
      p.id,
      p.name,
      p.description,
      p.sku,
      p.price,
      COALESCE(v.vector_score, 0) AS vector_score,
      COALESCE(f.fts_score, 0) AS fts_score,
      (COALESCE(v.vector_score, 0) * ? + COALESCE(f.fts_score, 0) * ?) AS hybrid_score
    FROM products p
    LEFT JOIN vector_results v ON p.id = v.id
    LEFT JOIN fts_results f ON p.id = f.id
    WHERE (v.id IS NOT NULL OR f.id IS NOT NULL)
      ${whereClause}
    ORDER BY hybrid_score DESC
    LIMIT ?
  `, params);

  return results.rows;
}

// General search
await ProductModel.searchProducts("MacBook Pro");

// With filters
await ProductModel.searchProducts("laptop", {
  minPrice: 1000000,
  maxPrice: 2000000,
  category: "laptop",
});