fix(backend/store): fix test mocking and reinforce fail-fast approach

- Fix all hybrid_search tests to mock embed_query at import location
- Remove graceful degradation in db.py - fail fast instead
- Add clear comment explaining why we don't use fallback

Why NO graceful degradation:
1. Silent fallbacks hide production issues (search degrades without visibility)
2. Makes testing unclear (tests can pass even when hybrid search is broken)
3. Inconsistent search quality confuses users
4. If embeddings fail, it's a real infrastructure issue that needs fixing

How we prevent failures instead:
- Embedding generation in approval flow (db.py:1545)
- Error logging with logger.error (not warning)
- Clear error messages (ValueError tells exactly what's wrong)
- Proper monitoring/alerting on errors

All tests pass: 9/9 hybrid_search_test.py, db_test.py search tests ✅

.github/workflows/platform-backend-ci.yml

@@ -176,7 +176,7 @@ jobs:
           }
 
       - name: Run Database Migrations
-        run: poetry run prisma migrate dev --name updates
+        run: poetry run prisma migrate deploy
         env:
           DATABASE_URL: ${{ steps.supabase.outputs.DB_URL }}
           DIRECT_URL: ${{ steps.supabase.outputs.DB_URL }}

autogpt_platform/backend/backend/api/features/store/backfill_embeddings.py

@@ -0,0 +1,72 @@
+#!/usr/bin/env python3
+"""
+CLI script to backfill embeddings for store agents.
+
+Usage:
+    poetry run python -m backend.api.features.store.backfill_embeddings [--batch-size N]
+"""
+
+import argparse
+import asyncio
+import sys
+
+import prisma
+
+from backend.api.features.store.embeddings import (
+    backfill_missing_embeddings,
+    get_embedding_stats,
+)
+
+
+async def main(batch_size: int = 100) -> int:
+    """Run the backfill process."""
+    # Initialize Prisma client
+    client = prisma.Prisma()
+    await client.connect()
+    prisma.register(client)
+
+    try:
+        # Get current stats
+        print("Current embedding stats:")
+        stats = await get_embedding_stats()
+        print(f"  Total approved: {stats['total_approved']}")
+        print(f"  With embeddings: {stats['with_embeddings']}")
+        print(f"  Without embeddings: {stats['without_embeddings']}")
+        print(f"  Coverage: {stats['coverage_percent']}%")
+
+        if stats["without_embeddings"] == 0:
+            print("\nAll agents already have embeddings. Nothing to do.")
+            return 0
+
+        # Run backfill
+        print(f"\nBackfilling up to {batch_size} embeddings...")
+        result = await backfill_missing_embeddings(batch_size=batch_size)
+        print(f"  Processed: {result['processed']}")
+        print(f"  Success: {result['success']}")
+        print(f"  Failed: {result['failed']}")
+
+        # Get final stats
+        print("\nFinal embedding stats:")
+        stats = await get_embedding_stats()
+        print(f"  Total approved: {stats['total_approved']}")
+        print(f"  With embeddings: {stats['with_embeddings']}")
+        print(f"  Without embeddings: {stats['without_embeddings']}")
+        print(f"  Coverage: {stats['coverage_percent']}%")
+
+        return 0 if result["failed"] == 0 else 1
+
+    finally:
+        await client.disconnect()
+
+
+if __name__ == "__main__":
+    parser = argparse.ArgumentParser(description="Backfill embeddings for store agents")
+    parser.add_argument(
+        "--batch-size",
+        type=int,
+        default=100,
+        help="Number of embeddings to generate (default: 100)",
+    )
+    args = parser.parse_args()
+
+    sys.exit(asyncio.run(main(batch_size=args.batch_size)))

autogpt_platform/backend/backend/api/features/store/db.py

@@ -1,6 +1,5 @@
 import asyncio
 import logging
-import typing
 from datetime import datetime, timezone
 from typing import Literal
 
@@ -10,7 +9,7 @@ import prisma.errors
 import prisma.models
 import prisma.types
 
-from backend.data.db import query_raw_with_schema, transaction
+from backend.data.db import transaction
 from backend.data.graph import (
     GraphMeta,
     GraphModel,
@@ -30,6 +29,8 @@ from backend.util.settings import Settings
 
 from . import exceptions as store_exceptions
 from . import model as store_model
+from .embeddings import ensure_embedding
+from .hybrid_search import hybrid_search
 
 logger = logging.getLogger(__name__)
 settings = Settings()
@@ -56,99 +57,30 @@ async def get_store_agents(
         f"Getting store agents. featured={featured}, creators={creators}, sorted_by={sorted_by}, search={search_query}, category={category}, page={page}"
     )
 
+    search_used_hybrid = False
+    store_agents: list[store_model.StoreAgent] = []
+    total = 0
+    total_pages = 0
+
     try:
-        # If search_query is provided, use full-text search
+        # If search_query is provided, use hybrid search (embeddings + tsvector)
         if search_query:
-            offset = (page - 1) * page_size
+            # Use hybrid search combining semantic and lexical signals
+            # No fallback - if this fails, it indicates a configuration/infrastructure issue
+            # that needs to be fixed (missing API key, OpenAI API down, etc.)
+            agents, total = await hybrid_search(
+                query=search_query,
+                featured=featured,
+                creators=creators,
+                category=category,
+                sorted_by="relevance",  # Use hybrid scoring for relevance
+                page=page,
+                page_size=page_size,
+            )
+            search_used_hybrid = True
 
-            # Whitelist allowed order_by columns
-            ALLOWED_ORDER_BY = {
-                "rating": "rating DESC, rank DESC",
-                "runs": "runs DESC, rank DESC",
-                "name": "agent_name ASC, rank ASC",
-                "updated_at": "updated_at DESC, rank DESC",
-            }
-
-            # Validate and get order clause
-            if sorted_by and sorted_by in ALLOWED_ORDER_BY:
-                order_by_clause = ALLOWED_ORDER_BY[sorted_by]
-            else:
-                order_by_clause = "updated_at DESC, rank DESC"
-
-            # Build WHERE conditions and parameters list
-            where_parts: list[str] = []
-            params: list[typing.Any] = [search_query]  # $1 - search term
-            param_index = 2  # Start at $2 for next parameter
-
-            # Always filter for available agents
-            where_parts.append("is_available = true")
-
-            if featured:
-                where_parts.append("featured = true")
-
-            if creators and creators:
-                # Use ANY with array parameter
-                where_parts.append(f"creator_username = ANY(${param_index})")
-                params.append(creators)
-                param_index += 1
-
-            if category and category:
-                where_parts.append(f"${param_index} = ANY(categories)")
-                params.append(category)
-                param_index += 1
-
-            sql_where_clause: str = " AND ".join(where_parts) if where_parts else "1=1"
-
-            # Add pagination params
-            params.extend([page_size, offset])
-            limit_param = f"${param_index}"
-            offset_param = f"${param_index + 1}"
-
-            # Execute full-text search query with parameterized values
-            sql_query = f"""
-                SELECT
-                    slug,
-                    agent_name,
-                    agent_image,
-                    creator_username,
-                    creator_avatar,
-                    sub_heading,
-                    description,
-                    runs,
-                    rating,
-                    categories,
-                    featured,
-                    is_available,
-                    updated_at,
-                    ts_rank_cd(search, query) AS rank
-                FROM {{schema_prefix}}"StoreAgent",
-                    plainto_tsquery('english', $1) AS query
-                WHERE {sql_where_clause}
-                    AND search @@ query
-                ORDER BY {order_by_clause}
-                LIMIT {limit_param} OFFSET {offset_param}
-            """
-
-            # Count query for pagination - only uses search term parameter
-            count_query = f"""
-                SELECT COUNT(*) as count
-                FROM {{schema_prefix}}"StoreAgent",
-                    plainto_tsquery('english', $1) AS query
-                WHERE {sql_where_clause}
-                    AND search @@ query
-            """
-
-            # Execute both queries with parameters
-            agents = await query_raw_with_schema(sql_query, *params)
-
-            # For count, use params without pagination (last 2 params)
-            count_params = params[:-2]
-            count_result = await query_raw_with_schema(count_query, *count_params)
-
-            total = count_result[0]["count"] if count_result else 0
+            # Convert hybrid search results (dict format)
             total_pages = (total + page_size - 1) // page_size
-
-            # Convert raw results to StoreAgent models
             store_agents: list[store_model.StoreAgent] = []
             for agent in agents:
                 try:
@@ -167,11 +99,13 @@ async def get_store_agents(
                     )
                     store_agents.append(store_agent)
                 except Exception as e:
-                    logger.error(f"Error parsing Store agent from search results: {e}")
+                    logger.error(
+                        f"Error parsing Store agent from hybrid search results: {e}"
+                    )
                     continue
 
-        else:
-            # Non-search query path (original logic)
+        if not search_used_hybrid:
+            # Fallback path - use basic search or no search
             where_clause: prisma.types.StoreAgentWhereInput = {"is_available": True}
             if featured:
                 where_clause["featured"] = featured
@@ -180,6 +114,14 @@ async def get_store_agents(
             if category:
                 where_clause["categories"] = {"has": category}
 
+            # Add basic text search if search_query provided but hybrid failed
+            if search_query:
+                where_clause["OR"] = [
+                    {"agent_name": {"contains": search_query, "mode": "insensitive"}},
+                    {"sub_heading": {"contains": search_query, "mode": "insensitive"}},
+                    {"description": {"contains": search_query, "mode": "insensitive"}},
+                ]
+
             order_by = []
             if sorted_by == "rating":
                 order_by.append({"rating": "desc"})
@@ -1600,6 +1542,22 @@ async def review_store_submission(
                     },
                 )
 
+            # Generate embedding for approved listing (non-blocking)
+            try:
+                await ensure_embedding(
+                    version_id=store_listing_version_id,
+                    name=store_listing_version.name,
+                    description=store_listing_version.description,
+                    sub_heading=store_listing_version.subHeading,
+                    categories=store_listing_version.categories or [],
+                )
+            except Exception as e:
+                # Don't fail approval if embedding generation fails
+                logger.error(
+                    f"Failed to generate embedding for approved listing "
+                    f"{store_listing_version_id}: {e}"
+                )
+
         # If rejecting an approved agent, update the StoreListing accordingly
         if is_rejecting_approved:
             # Check if there are other approved versions

autogpt_platform/backend/backend/api/features/store/embeddings.py

@@ -0,0 +1,531 @@
+"""
+Unified Content Embeddings Service
+
+Handles generation and storage of OpenAI embeddings for all content types
+(store listings, blocks, documentation, library agents) to enable semantic/hybrid search.
+"""
+
+import asyncio
+import logging
+from typing import Any
+
+import prisma
+from openai import OpenAI
+from prisma.enums import ContentType
+
+from backend.data.db import execute_raw_with_schema, query_raw_with_schema
+from backend.util.json import dumps
+from backend.util.settings import Settings
+
+logger = logging.getLogger(__name__)
+
+
+# OpenAI embedding model configuration
+EMBEDDING_MODEL = "text-embedding-3-small"
+EMBEDDING_DIM = 1536
+
+
+def build_searchable_text(
+    name: str,
+    description: str,
+    sub_heading: str,
+    categories: list[str],
+) -> str:
+    """
+    Build searchable text from listing version fields.
+
+    Combines relevant fields into a single string for embedding.
+    """
+    parts = []
+
+    # Name is important - include it
+    if name:
+        parts.append(name)
+
+    # Sub-heading provides context
+    if sub_heading:
+        parts.append(sub_heading)
+
+    # Description is the main content
+    if description:
+        parts.append(description)
+
+    # Categories help with semantic matching
+    if categories:
+        parts.append(" ".join(categories))
+
+    return " ".join(parts)
+
+
+async def generate_embedding(text: str) -> list[float] | None:
+    """
+    Generate embedding for text using OpenAI API.
+
+    Returns None if embedding generation fails.
+    """
+    try:
+        settings = Settings()
+        api_key = settings.secrets.openai_internal_api_key
+        if not api_key:
+            logger.error("openai_internal_api_key not set, cannot generate embedding")
+            return None
+
+        client = OpenAI(api_key=api_key)
+
+        # Truncate text to avoid token limits (~32k chars for safety)
+        truncated_text = text[:32000]
+
+        response = client.embeddings.create(
+            model=EMBEDDING_MODEL,
+            input=truncated_text,
+        )
+
+        embedding = response.data[0].embedding
+        logger.debug(f"Generated embedding with {len(embedding)} dimensions")
+        return embedding
+
+    except Exception as e:
+        logger.error(f"Failed to generate embedding: {e}")
+        return None
+
+
+async def store_embedding(
+    version_id: str,
+    embedding: list[float],
+    tx: prisma.Prisma | None = None,
+) -> bool:
+    """
+    Store embedding in the database.
+
+    BACKWARD COMPATIBILITY: Maintained for existing store listing usage.
+    Uses raw SQL since Prisma doesn't natively support pgvector.
+    """
+    return await store_content_embedding(
+        content_type=ContentType.STORE_AGENT,
+        content_id=version_id,
+        embedding=embedding,
+        searchable_text="",  # Will be populated from existing data
+        metadata=None,
+        user_id=None,  # Store agents are public
+        tx=tx,
+    )
+
+
+async def store_content_embedding(
+    content_type: ContentType,
+    content_id: str,
+    embedding: list[float],
+    searchable_text: str,
+    metadata: dict | None = None,
+    user_id: str | None = None,
+    tx: prisma.Prisma | None = None,
+) -> bool:
+    """
+    Store embedding in the unified content embeddings table.
+
+    New function for unified content embedding storage.
+    Uses raw SQL since Prisma doesn't natively support pgvector.
+    """
+    try:
+        client = tx if tx else prisma.get_client()
+
+        # Convert embedding to PostgreSQL vector format
+        embedding_str = embedding_to_vector_string(embedding)
+        metadata_json = dumps(metadata or {})
+
+        # Upsert the embedding
+        await execute_raw_with_schema(
+            """
+            INSERT INTO {schema_prefix}"UnifiedContentEmbedding" (
+                "id", "contentType", "contentId", "userId", "embedding", "searchableText", "metadata", "createdAt", "updatedAt"
+            )
+            VALUES (gen_random_uuid()::text, $1::"ContentType", $2, $3, $4::vector, $5, $6::jsonb, NOW(), NOW())
+            ON CONFLICT ("contentType", "contentId", "userId")
+            DO UPDATE SET
+                "embedding" = $4::vector,
+                "searchableText" = $5,
+                "metadata" = $6::jsonb,
+                "updatedAt" = NOW()
+            """,
+            content_type,
+            content_id,
+            user_id,
+            embedding_str,
+            searchable_text,
+            metadata_json,
+            client=client,
+        )
+
+        logger.info(f"Stored embedding for {content_type}:{content_id}")
+        return True
+
+    except Exception as e:
+        logger.error(f"Failed to store embedding for {content_type}:{content_id}: {e}")
+        return False
+
+
+async def get_embedding(version_id: str) -> dict[str, Any] | None:
+    """
+    Retrieve embedding record for a listing version.
+
+    BACKWARD COMPATIBILITY: Maintained for existing store listing usage.
+    Returns dict with storeListingVersionId, embedding, timestamps or None if not found.
+    """
+    result = await get_content_embedding(
+        ContentType.STORE_AGENT, version_id, user_id=None
+    )
+    if result:
+        # Transform to old format for backward compatibility
+        return {
+            "storeListingVersionId": result["contentId"],
+            "embedding": result["embedding"],
+            "createdAt": result["createdAt"],
+            "updatedAt": result["updatedAt"],
+        }
+    return None
+
+
+async def get_content_embedding(
+    content_type: ContentType, content_id: str, user_id: str | None = None
+) -> dict[str, Any] | None:
+    """
+    Retrieve embedding record for any content type.
+
+    New function for unified content embedding retrieval.
+    Returns dict with contentType, contentId, embedding, timestamps or None if not found.
+    """
+    try:
+        result = await query_raw_with_schema(
+            """
+            SELECT
+                "contentType",
+                "contentId",
+                "userId",
+                "embedding"::text as "embedding",
+                "searchableText",
+                "metadata",
+                "createdAt",
+                "updatedAt"
+            FROM {schema_prefix}"UnifiedContentEmbedding"
+            WHERE "contentType" = $1::"ContentType" AND "contentId" = $2 AND ("userId" = $3 OR ($3 IS NULL AND "userId" IS NULL))
+            """,
+            content_type,
+            content_id,
+            user_id,
+        )
+
+        if result and len(result) > 0:
+            return result[0]
+        return None
+
+    except Exception as e:
+        logger.error(f"Failed to get embedding for {content_type}:{content_id}: {e}")
+        return None
+
+
+async def ensure_embedding(
+    version_id: str,
+    name: str,
+    description: str,
+    sub_heading: str,
+    categories: list[str],
+    force: bool = False,
+    tx: prisma.Prisma | None = None,
+) -> bool:
+    """
+    Ensure an embedding exists for the listing version.
+
+    Creates embedding if missing. Use force=True to regenerate.
+    Backward-compatible wrapper for store listings.
+
+    Args:
+        version_id: The StoreListingVersion ID
+        name: Agent name
+        description: Agent description
+        sub_heading: Agent sub-heading
+        categories: Agent categories
+        force: Force regeneration even if embedding exists
+        tx: Optional transaction client
+
+    Returns:
+        True if embedding exists/was created, False on failure
+    """
+    try:
+        # Check if embedding already exists
+        if not force:
+            existing = await get_embedding(version_id)
+            if existing and existing.get("embedding"):
+                logger.debug(f"Embedding for version {version_id} already exists")
+                return True
+
+        # Build searchable text for embedding
+        searchable_text = build_searchable_text(
+            name, description, sub_heading, categories
+        )
+
+        # Generate new embedding
+        embedding = await generate_embedding(searchable_text)
+        if embedding is None:
+            logger.warning(f"Could not generate embedding for version {version_id}")
+            return False
+
+        # Store the embedding with metadata using new function
+        metadata = {
+            "name": name,
+            "subHeading": sub_heading,
+            "categories": categories,
+        }
+        return await store_content_embedding(
+            content_type=ContentType.STORE_AGENT,
+            content_id=version_id,
+            embedding=embedding,
+            searchable_text=searchable_text,
+            metadata=metadata,
+            user_id=None,  # Store agents are public
+            tx=tx,
+        )
+
+    except Exception as e:
+        logger.error(f"Failed to ensure embedding for version {version_id}: {e}")
+        return False
+
+
+async def delete_embedding(version_id: str) -> bool:
+    """
+    Delete embedding for a listing version.
+
+    BACKWARD COMPATIBILITY: Maintained for existing store listing usage.
+    Note: This is usually handled automatically by CASCADE delete,
+    but provided for manual cleanup if needed.
+    """
+    return await delete_content_embedding(ContentType.STORE_AGENT, version_id)
+
+
+async def delete_content_embedding(content_type: ContentType, content_id: str) -> bool:
+    """
+    Delete embedding for any content type.
+
+    New function for unified content embedding deletion.
+    Note: This is usually handled automatically by CASCADE delete,
+    but provided for manual cleanup if needed.
+    """
+    try:
+        client = prisma.get_client()
+
+        await execute_raw_with_schema(
+            """
+            DELETE FROM {schema_prefix}"UnifiedContentEmbedding"
+            WHERE "contentType" = $1::"ContentType" AND "contentId" = $2
+            """,
+            content_type,
+            content_id,
+            client=client,
+        )
+
+        logger.info(f"Deleted embedding for {content_type}:{content_id}")
+        return True
+
+    except Exception as e:
+        logger.error(f"Failed to delete embedding for {content_type}:{content_id}: {e}")
+        return False
+
+
+async def get_embedding_stats() -> dict[str, Any]:
+    """
+    Get statistics about embedding coverage.
+
+    Returns counts of:
+    - Total approved listing versions
+    - Versions with embeddings
+    - Versions without embeddings
+    """
+    try:
+        # Count approved versions
+        approved_result = await query_raw_with_schema(
+            """
+            SELECT COUNT(*) as count
+            FROM {schema_prefix}"StoreListingVersion"
+            WHERE "submissionStatus" = 'APPROVED'
+            AND "isDeleted" = false
+            """
+        )
+        total_approved = approved_result[0]["count"] if approved_result else 0
+
+        # Count versions with embeddings
+        embedded_result = await query_raw_with_schema(
+            """
+            SELECT COUNT(*) as count
+            FROM {schema_prefix}"StoreListingVersion" slv
+            JOIN {schema_prefix}"UnifiedContentEmbedding" uce ON slv.id = uce."contentId" AND uce."contentType" = 'STORE_AGENT'
+            WHERE slv."submissionStatus" = 'APPROVED'
+            AND slv."isDeleted" = false
+            """
+        )
+        with_embeddings = embedded_result[0]["count"] if embedded_result else 0
+
+        return {
+            "total_approved": total_approved,
+            "with_embeddings": with_embeddings,
+            "without_embeddings": total_approved - with_embeddings,
+            "coverage_percent": (
+                round(with_embeddings / total_approved * 100, 1)
+                if total_approved > 0
+                else 0
+            ),
+        }
+
+    except Exception as e:
+        logger.error(f"Failed to get embedding stats: {e}")
+        return {
+            "total_approved": 0,
+            "with_embeddings": 0,
+            "without_embeddings": 0,
+            "coverage_percent": 0,
+            "error": str(e),
+        }
+
+
+async def backfill_missing_embeddings(batch_size: int = 10) -> dict[str, Any]:
+    """
+    Generate embeddings for approved listings that don't have them.
+
+    Args:
+        batch_size: Number of embeddings to generate in one call
+
+    Returns:
+        Dict with success/failure counts
+    """
+    try:
+        # Find approved versions without embeddings
+        missing = await query_raw_with_schema(
+            """
+            SELECT
+                slv.id,
+                slv.name,
+                slv.description,
+                slv."subHeading",
+                slv.categories
+            FROM {schema_prefix}"StoreListingVersion" slv
+            LEFT JOIN {schema_prefix}"UnifiedContentEmbedding" uce
+                ON slv.id = uce."contentId" AND uce."contentType" = 'STORE_AGENT'
+            WHERE slv."submissionStatus" = 'APPROVED'
+            AND slv."isDeleted" = false
+            AND uce."contentId" IS NULL
+            LIMIT $1
+            """,
+            batch_size,
+        )
+
+        if not missing:
+            return {
+                "processed": 0,
+                "success": 0,
+                "failed": 0,
+                "message": "No missing embeddings",
+            }
+
+        # Process embeddings concurrently for better performance
+        embedding_tasks = [
+            ensure_embedding(
+                version_id=row["id"],
+                name=row["name"],
+                description=row["description"],
+                sub_heading=row["subHeading"],
+                categories=row["categories"] or [],
+            )
+            for row in missing
+        ]
+
+        results = await asyncio.gather(*embedding_tasks, return_exceptions=True)
+
+        success = sum(1 for result in results if result is True)
+        failed = len(results) - success
+
+        return {
+            "processed": len(missing),
+            "success": success,
+            "failed": failed,
+            "message": f"Backfilled {success} embeddings, {failed} failed",
+        }
+
+    except Exception as e:
+        logger.error(f"Failed to backfill embeddings: {e}")
+        return {
+            "processed": 0,
+            "success": 0,
+            "failed": 0,
+            "error": str(e),
+        }
+
+
+async def embed_query(query: str) -> list[float] | None:
+    """
+    Generate embedding for a search query.
+
+    Same as generate_embedding but with clearer intent.
+    """
+    return await generate_embedding(query)
+
+
+def embedding_to_vector_string(embedding: list[float]) -> str:
+    """Convert embedding list to PostgreSQL vector string format."""
+    return "[" + ",".join(str(x) for x in embedding) + "]"
+
+
+async def ensure_content_embedding(
+    content_type: ContentType,
+    content_id: str,
+    searchable_text: str,
+    metadata: dict | None = None,
+    user_id: str | None = None,
+    force: bool = False,
+    tx: prisma.Prisma | None = None,
+) -> bool:
+    """
+    Ensure an embedding exists for any content type.
+
+    Generic function for creating embeddings for store agents, blocks, docs, etc.
+
+    Args:
+        content_type: ContentType enum value (STORE_AGENT, BLOCK, etc.)
+        content_id: Unique identifier for the content
+        searchable_text: Combined text for embedding generation
+        metadata: Optional metadata to store with embedding
+        force: Force regeneration even if embedding exists
+        tx: Optional transaction client
+
+    Returns:
+        True if embedding exists/was created, False on failure
+    """
+    try:
+        # Check if embedding already exists
+        if not force:
+            existing = await get_content_embedding(content_type, content_id, user_id)
+            if existing and existing.get("embedding"):
+                logger.debug(
+                    f"Embedding for {content_type}:{content_id} already exists"
+                )
+                return True
+
+        # Generate new embedding
+        embedding = await generate_embedding(searchable_text)
+        if embedding is None:
+            logger.warning(
+                f"Could not generate embedding for {content_type}:{content_id}"
+            )
+            return False
+
+        # Store the embedding
+        return await store_content_embedding(
+            content_type=content_type,
+            content_id=content_id,
+            embedding=embedding,
+            searchable_text=searchable_text,
+            metadata=metadata or {},
+            user_id=user_id,
+            tx=tx,
+        )
+
+    except Exception as e:
+        logger.error(f"Failed to ensure embedding for {content_type}:{content_id}: {e}")
+        return False

autogpt_platform/backend/backend/api/features/store/embeddings_schema_test.py

@@ -0,0 +1,329 @@
+"""
+Integration tests for embeddings with schema handling.
+
+These tests verify that embeddings operations work correctly across different database schemas.
+"""
+
+from unittest.mock import AsyncMock, patch
+
+import pytest
+from prisma.enums import ContentType
+
+from backend.api.features.store import embeddings
+
+# Schema prefix tests removed - functionality moved to db.raw_with_schema() helper
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_store_content_embedding_with_schema():
+    """Test storing embeddings with proper schema handling."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch("prisma.get_client") as mock_get_client:
+            mock_client = AsyncMock()
+            mock_get_client.return_value = mock_client
+
+            result = await embeddings.store_content_embedding(
+                content_type=ContentType.STORE_AGENT,
+                content_id="test-id",
+                embedding=[0.1] * 1536,
+                searchable_text="test text",
+                metadata={"test": "data"},
+                user_id=None,
+            )
+
+            # Verify the query was called
+            assert mock_client.execute_raw.called
+
+            # Get the SQL query that was executed
+            call_args = mock_client.execute_raw.call_args
+            sql_query = call_args[0][0]
+
+            # Verify schema prefix is in the query
+            assert '"platform"."UnifiedContentEmbedding"' in sql_query
+
+            # Verify result
+            assert result is True
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_get_content_embedding_with_schema():
+    """Test retrieving embeddings with proper schema handling."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch("prisma.get_client") as mock_get_client:
+            mock_client = AsyncMock()
+            mock_client.query_raw.return_value = [
+                {
+                    "contentType": "STORE_AGENT",
+                    "contentId": "test-id",
+                    "userId": None,
+                    "embedding": "[0.1, 0.2]",
+                    "searchableText": "test",
+                    "metadata": {},
+                    "createdAt": "2024-01-01",
+                    "updatedAt": "2024-01-01",
+                }
+            ]
+            mock_get_client.return_value = mock_client
+
+            result = await embeddings.get_content_embedding(
+                ContentType.STORE_AGENT,
+                "test-id",
+                user_id=None,
+            )
+
+            # Verify the query was called
+            assert mock_client.query_raw.called
+
+            # Get the SQL query that was executed
+            call_args = mock_client.query_raw.call_args
+            sql_query = call_args[0][0]
+
+            # Verify schema prefix is in the query
+            assert '"platform"."UnifiedContentEmbedding"' in sql_query
+
+            # Verify result
+            assert result is not None
+            assert result["contentId"] == "test-id"
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_delete_content_embedding_with_schema():
+    """Test deleting embeddings with proper schema handling."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch("prisma.get_client") as mock_get_client:
+            mock_client = AsyncMock()
+            mock_get_client.return_value = mock_client
+
+            result = await embeddings.delete_content_embedding(
+                ContentType.STORE_AGENT,
+                "test-id",
+            )
+
+            # Verify the query was called
+            assert mock_client.execute_raw.called
+
+            # Get the SQL query that was executed
+            call_args = mock_client.execute_raw.call_args
+            sql_query = call_args[0][0]
+
+            # Verify schema prefix is in the query
+            assert '"platform"."UnifiedContentEmbedding"' in sql_query
+
+            # Verify result
+            assert result is True
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_get_embedding_stats_with_schema():
+    """Test embedding statistics with proper schema handling."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch("prisma.get_client") as mock_get_client:
+            mock_client = AsyncMock()
+            # Mock both query results
+            mock_client.query_raw.side_effect = [
+                [{"count": 100}],  # total_approved
+                [{"count": 80}],  # with_embeddings
+            ]
+            mock_get_client.return_value = mock_client
+
+            result = await embeddings.get_embedding_stats()
+
+            # Verify both queries were called
+            assert mock_client.query_raw.call_count == 2
+
+            # Get both SQL queries
+            first_call = mock_client.query_raw.call_args_list[0]
+            second_call = mock_client.query_raw.call_args_list[1]
+
+            first_sql = first_call[0][0]
+            second_sql = second_call[0][0]
+
+            # Verify schema prefix in both queries
+            assert '"platform"."StoreListingVersion"' in first_sql
+            assert '"platform"."StoreListingVersion"' in second_sql
+            assert '"platform"."UnifiedContentEmbedding"' in second_sql
+
+            # Verify results
+            assert result["total_approved"] == 100
+            assert result["with_embeddings"] == 80
+            assert result["without_embeddings"] == 20
+            assert result["coverage_percent"] == 80.0
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_backfill_missing_embeddings_with_schema():
+    """Test backfilling embeddings with proper schema handling."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch("prisma.get_client") as mock_get_client:
+            mock_client = AsyncMock()
+            # Mock missing embeddings query
+            mock_client.query_raw.return_value = [
+                {
+                    "id": "version-1",
+                    "name": "Test Agent",
+                    "description": "Test description",
+                    "subHeading": "Test heading",
+                    "categories": ["test"],
+                }
+            ]
+            mock_get_client.return_value = mock_client
+
+            with patch(
+                "backend.api.features.store.embeddings.ensure_embedding"
+            ) as mock_ensure:
+                mock_ensure.return_value = True
+
+                result = await embeddings.backfill_missing_embeddings(batch_size=10)
+
+                # Verify the query was called
+                assert mock_client.query_raw.called
+
+                # Get the SQL query
+                call_args = mock_client.query_raw.call_args
+                sql_query = call_args[0][0]
+
+                # Verify schema prefix in query
+                assert '"platform"."StoreListingVersion"' in sql_query
+                assert '"platform"."UnifiedContentEmbedding"' in sql_query
+
+                # Verify ensure_embedding was called
+                assert mock_ensure.called
+
+                # Verify results
+                assert result["processed"] == 1
+                assert result["success"] == 1
+                assert result["failed"] == 0
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_ensure_content_embedding_with_schema():
+    """Test ensuring embeddings exist with proper schema handling."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch(
+            "backend.api.features.store.embeddings.get_content_embedding"
+        ) as mock_get:
+            # Simulate no existing embedding
+            mock_get.return_value = None
+
+            with patch(
+                "backend.api.features.store.embeddings.generate_embedding"
+            ) as mock_generate:
+                mock_generate.return_value = [0.1] * 1536
+
+                with patch(
+                    "backend.api.features.store.embeddings.store_content_embedding"
+                ) as mock_store:
+                    mock_store.return_value = True
+
+                    result = await embeddings.ensure_content_embedding(
+                        content_type=ContentType.STORE_AGENT,
+                        content_id="test-id",
+                        searchable_text="test text",
+                        metadata={"test": "data"},
+                        user_id=None,
+                        force=False,
+                    )
+
+                    # Verify the flow
+                    assert mock_get.called
+                    assert mock_generate.called
+                    assert mock_store.called
+                    assert result is True
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_backward_compatibility_store_embedding():
+    """Test backward compatibility wrapper for store_embedding."""
+    with patch(
+        "backend.api.features.store.embeddings.store_content_embedding"
+    ) as mock_store:
+        mock_store.return_value = True
+
+        result = await embeddings.store_embedding(
+            version_id="test-version-id",
+            embedding=[0.1] * 1536,
+            tx=None,
+        )
+
+        # Verify it calls the new function with correct parameters
+        assert mock_store.called
+        call_args = mock_store.call_args
+
+        assert call_args[1]["content_type"] == ContentType.STORE_AGENT
+        assert call_args[1]["content_id"] == "test-version-id"
+        assert call_args[1]["user_id"] is None
+        assert result is True
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_backward_compatibility_get_embedding():
+    """Test backward compatibility wrapper for get_embedding."""
+    with patch(
+        "backend.api.features.store.embeddings.get_content_embedding"
+    ) as mock_get:
+        mock_get.return_value = {
+            "contentType": "STORE_AGENT",
+            "contentId": "test-version-id",
+            "embedding": "[0.1, 0.2]",
+            "createdAt": "2024-01-01",
+            "updatedAt": "2024-01-01",
+        }
+
+        result = await embeddings.get_embedding("test-version-id")
+
+        # Verify it calls the new function
+        assert mock_get.called
+
+        # Verify it transforms to old format
+        assert result is not None
+        assert result["storeListingVersionId"] == "test-version-id"
+        assert "embedding" in result
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_schema_handling_error_cases():
+    """Test error handling in schema-aware operations."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch("prisma.get_client") as mock_get_client:
+            mock_client = AsyncMock()
+            mock_client.execute_raw.side_effect = Exception("Database error")
+            mock_get_client.return_value = mock_client
+
+            result = await embeddings.store_content_embedding(
+                content_type=ContentType.STORE_AGENT,
+                content_id="test-id",
+                embedding=[0.1] * 1536,
+                searchable_text="test",
+                metadata=None,
+                user_id=None,
+            )
+
+            # Should return False on error, not raise
+            assert result is False
+
+
+if __name__ == "__main__":
+    pytest.main([__file__, "-v", "-s"])

autogpt_platform/backend/backend/api/features/store/embeddings_test.py

@@ -0,0 +1,359 @@
+from unittest.mock import MagicMock, patch
+
+import prisma
+import pytest
+from prisma import Prisma
+from prisma.enums import ContentType
+
+from backend.api.features.store import embeddings
+
+
+@pytest.fixture(autouse=True)
+async def setup_prisma():
+    """Setup Prisma client for tests."""
+    try:
+        Prisma()
+    except prisma.errors.ClientAlreadyRegisteredError:
+        pass
+    yield
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_build_searchable_text():
+    """Test searchable text building from listing fields."""
+    result = embeddings.build_searchable_text(
+        name="AI Assistant",
+        description="A helpful AI assistant for productivity",
+        sub_heading="Boost your productivity",
+        categories=["AI", "Productivity"],
+    )
+
+    expected = "AI Assistant Boost your productivity A helpful AI assistant for productivity AI Productivity"
+    assert result == expected
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_build_searchable_text_empty_fields():
+    """Test searchable text building with empty fields."""
+    result = embeddings.build_searchable_text(
+        name="", description="Test description", sub_heading="", categories=[]
+    )
+
+    assert result == "Test description"
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.OpenAI")
+async def test_generate_embedding_success(mock_openai_class):
+    """Test successful embedding generation."""
+    # Mock OpenAI response
+    mock_client = MagicMock()
+    mock_response = MagicMock()
+    mock_response.data = [MagicMock()]
+    mock_response.data[0].embedding = [0.1, 0.2, 0.3] * 512  # 1536 dimensions
+    mock_client.embeddings.create.return_value = mock_response
+    mock_openai_class.return_value = mock_client
+
+    with patch("backend.api.features.store.embeddings.Settings") as mock_settings:
+        mock_settings.return_value.secrets.openai_internal_api_key = "test-key"
+
+        result = await embeddings.generate_embedding("test text")
+
+        assert result is not None
+        assert len(result) == 1536
+        assert result[0] == 0.1
+
+        mock_client.embeddings.create.assert_called_once_with(
+            model="text-embedding-3-small", input="test text"
+        )
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.OpenAI")
+async def test_generate_embedding_no_api_key(mock_openai_class):
+    """Test embedding generation without API key."""
+    with patch("backend.api.features.store.embeddings.Settings") as mock_settings:
+        mock_settings.return_value.secrets.openai_internal_api_key = ""
+
+        result = await embeddings.generate_embedding("test text")
+
+        assert result is None
+        mock_openai_class.assert_not_called()
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.OpenAI")
+async def test_generate_embedding_api_error(mock_openai_class):
+    """Test embedding generation with API error."""
+    mock_client = MagicMock()
+    mock_client.embeddings.create.side_effect = Exception("API Error")
+    mock_openai_class.return_value = mock_client
+
+    with patch("backend.api.features.store.embeddings.Settings") as mock_settings:
+        mock_settings.return_value.secrets.openai_internal_api_key = "test-key"
+
+        result = await embeddings.generate_embedding("test text")
+
+        assert result is None
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.OpenAI")
+async def test_generate_embedding_text_truncation(mock_openai_class):
+    """Test that long text is properly truncated."""
+    mock_client = MagicMock()
+    mock_response = MagicMock()
+    mock_response.data = [MagicMock()]
+    mock_response.data[0].embedding = [0.1] * 1536
+    mock_client.embeddings.create.return_value = mock_response
+    mock_openai_class.return_value = mock_client
+
+    # Create text longer than 32k chars
+    long_text = "a" * 35000
+
+    with patch("backend.api.features.store.embeddings.Settings") as mock_settings:
+        mock_settings.return_value.secrets.openai_internal_api_key = "test-key"
+
+        await embeddings.generate_embedding(long_text)
+
+        # Verify truncated text was sent to API
+        call_args = mock_client.embeddings.create.call_args
+        assert len(call_args.kwargs["input"]) == 32000
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_store_embedding_success(mocker):
+    """Test successful embedding storage."""
+    mock_client = mocker.AsyncMock()
+    mock_client.execute_raw = mocker.AsyncMock()
+
+    embedding = [0.1, 0.2, 0.3]
+
+    result = await embeddings.store_embedding(
+        version_id="test-version-id", embedding=embedding, tx=mock_client
+    )
+
+    assert result is True
+    mock_client.execute_raw.assert_called_once()
+    call_args = mock_client.execute_raw.call_args[0]
+    assert "test-version-id" in call_args
+    assert "[0.1,0.2,0.3]" in call_args
+    assert None in call_args  # userId should be None for store agents
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_store_embedding_database_error(mocker):
+    """Test embedding storage with database error."""
+    mock_client = mocker.AsyncMock()
+    mock_client.execute_raw.side_effect = Exception("Database error")
+
+    embedding = [0.1, 0.2, 0.3]
+
+    result = await embeddings.store_embedding(
+        version_id="test-version-id", embedding=embedding, tx=mock_client
+    )
+
+    assert result is False
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_get_embedding_success(mocker):
+    """Test successful embedding retrieval."""
+    mock_client = mocker.AsyncMock()
+    mock_result = [
+        {
+            "contentType": "STORE_AGENT",
+            "contentId": "test-version-id",
+            "embedding": "[0.1,0.2,0.3]",
+            "searchableText": "Test text",
+            "metadata": {},
+            "createdAt": "2024-01-01T00:00:00Z",
+            "updatedAt": "2024-01-01T00:00:00Z",
+        }
+    ]
+    mock_client.query_raw.return_value = mock_result
+
+    with patch("prisma.get_client", return_value=mock_client):
+        result = await embeddings.get_embedding("test-version-id")
+
+        assert result is not None
+        assert result["storeListingVersionId"] == "test-version-id"
+        assert result["embedding"] == "[0.1,0.2,0.3]"
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_get_embedding_not_found(mocker):
+    """Test embedding retrieval when not found."""
+    mock_client = mocker.AsyncMock()
+    mock_client.query_raw.return_value = []
+
+    with patch("prisma.get_client", return_value=mock_client):
+        result = await embeddings.get_embedding("test-version-id")
+
+        assert result is None
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.generate_embedding")
+@patch("backend.api.features.store.embeddings.store_embedding")
+@patch("backend.api.features.store.embeddings.get_embedding")
+async def test_ensure_embedding_already_exists(mock_get, mock_store, mock_generate):
+    """Test ensure_embedding when embedding already exists."""
+    mock_get.return_value = {"embedding": "[0.1,0.2,0.3]"}
+
+    result = await embeddings.ensure_embedding(
+        version_id="test-id",
+        name="Test",
+        description="Test description",
+        sub_heading="Test heading",
+        categories=["test"],
+    )
+
+    assert result is True
+    mock_generate.assert_not_called()
+    mock_store.assert_not_called()
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.generate_embedding")
+@patch("backend.api.features.store.embeddings.store_content_embedding")
+@patch("backend.api.features.store.embeddings.get_embedding")
+async def test_ensure_embedding_create_new(mock_get, mock_store, mock_generate):
+    """Test ensure_embedding creating new embedding."""
+    mock_get.return_value = None
+    mock_generate.return_value = [0.1, 0.2, 0.3]
+    mock_store.return_value = True
+
+    result = await embeddings.ensure_embedding(
+        version_id="test-id",
+        name="Test",
+        description="Test description",
+        sub_heading="Test heading",
+        categories=["test"],
+    )
+
+    assert result is True
+    mock_generate.assert_called_once_with("Test Test heading Test description test")
+    mock_store.assert_called_once_with(
+        content_type=ContentType.STORE_AGENT,
+        content_id="test-id",
+        embedding=[0.1, 0.2, 0.3],
+        searchable_text="Test Test heading Test description test",
+        metadata={"name": "Test", "subHeading": "Test heading", "categories": ["test"]},
+        user_id=None,
+        tx=None,
+    )
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.generate_embedding")
+@patch("backend.api.features.store.embeddings.get_embedding")
+async def test_ensure_embedding_generation_fails(mock_get, mock_generate):
+    """Test ensure_embedding when generation fails."""
+    mock_get.return_value = None
+    mock_generate.return_value = None
+
+    result = await embeddings.ensure_embedding(
+        version_id="test-id",
+        name="Test",
+        description="Test description",
+        sub_heading="Test heading",
+        categories=["test"],
+    )
+
+    assert result is False
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_get_embedding_stats(mocker):
+    """Test embedding statistics retrieval."""
+    mock_client = mocker.AsyncMock()
+
+    # Mock approved count query
+    mock_approved_result = [{"count": 100}]
+    # Mock embedded count query
+    mock_embedded_result = [{"count": 75}]
+
+    mock_client.query_raw.side_effect = [mock_approved_result, mock_embedded_result]
+
+    with patch("prisma.get_client", return_value=mock_client):
+        result = await embeddings.get_embedding_stats()
+
+        assert result["total_approved"] == 100
+        assert result["with_embeddings"] == 75
+        assert result["without_embeddings"] == 25
+        assert result["coverage_percent"] == 75.0
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@patch("backend.api.features.store.embeddings.ensure_embedding")
+async def test_backfill_missing_embeddings_success(mock_ensure, mocker):
+    """Test backfill with successful embedding generation."""
+    mock_client = mocker.AsyncMock()
+
+    # Mock missing embeddings query
+    mock_missing = [
+        {
+            "id": "version-1",
+            "name": "Agent 1",
+            "description": "Description 1",
+            "subHeading": "Heading 1",
+            "categories": ["AI"],
+        },
+        {
+            "id": "version-2",
+            "name": "Agent 2",
+            "description": "Description 2",
+            "subHeading": "Heading 2",
+            "categories": ["Productivity"],
+        },
+    ]
+    mock_client.query_raw.return_value = mock_missing
+
+    # Mock ensure_embedding to succeed for first, fail for second
+    mock_ensure.side_effect = [True, False]
+
+    with patch("prisma.get_client", return_value=mock_client):
+        result = await embeddings.backfill_missing_embeddings(batch_size=5)
+
+        assert result["processed"] == 2
+        assert result["success"] == 1
+        assert result["failed"] == 1
+        assert mock_ensure.call_count == 2
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_backfill_missing_embeddings_no_missing(mocker):
+    """Test backfill when no embeddings are missing."""
+    mock_client = mocker.AsyncMock()
+    mock_client.query_raw.return_value = []
+
+    with patch("prisma.get_client", return_value=mock_client):
+        result = await embeddings.backfill_missing_embeddings(batch_size=5)
+
+        assert result["processed"] == 0
+        assert result["success"] == 0
+        assert result["failed"] == 0
+        assert result["message"] == "No missing embeddings"
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_embedding_to_vector_string():
+    """Test embedding to PostgreSQL vector string conversion."""
+    embedding = [0.1, 0.2, 0.3, -0.4]
+    result = embeddings.embedding_to_vector_string(embedding)
+    assert result == "[0.1,0.2,0.3,-0.4]"
+
+
+@pytest.mark.asyncio(loop_scope="session")
+async def test_embed_query():
+    """Test embed_query function (alias for generate_embedding)."""
+    with patch(
+        "backend.api.features.store.embeddings.generate_embedding"
+    ) as mock_generate:
+        mock_generate.return_value = [0.1, 0.2, 0.3]
+
+        result = await embeddings.embed_query("test query")
+
+        assert result == [0.1, 0.2, 0.3]
+        mock_generate.assert_called_once_with("test query")

autogpt_platform/backend/backend/api/features/store/hybrid_search.py

@@ -0,0 +1,306 @@
+"""
+Hybrid Search for Store Agents
+
+Combines semantic (embedding) search with lexical (tsvector) search
+for improved relevance in marketplace agent discovery.
+"""
+
+import logging
+from dataclasses import dataclass
+from datetime import datetime
+from typing import Any, Literal
+
+from backend.api.features.store.embeddings import (
+    embed_query,
+    embedding_to_vector_string,
+)
+from backend.data.db import query_raw_with_schema
+
+logger = logging.getLogger(__name__)
+
+
+@dataclass
+class HybridSearchWeights:
+    """Weights for combining search signals."""
+
+    semantic: float = 0.35  # Embedding cosine similarity
+    lexical: float = 0.35  # tsvector ts_rank_cd score
+    category: float = 0.20  # Category match boost
+    recency: float = 0.10  # Newer agents ranked higher
+
+
+DEFAULT_WEIGHTS = HybridSearchWeights()
+
+# Minimum relevance score threshold - agents below this are filtered out
+# With weights (0.35 semantic + 0.35 lexical + 0.20 category + 0.10 recency):
+# - 0.20 means at least ~50% semantic match OR strong lexical match required
+# - Ensures only genuinely relevant results are returned
+# - Recency alone (0.10 max) won't pass the threshold
+DEFAULT_MIN_SCORE = 0.20
+
+
+@dataclass
+class HybridSearchResult:
+    """A single search result with score breakdown."""
+
+    slug: str
+    agent_name: str
+    agent_image: str
+    creator_username: str
+    creator_avatar: str
+    sub_heading: str
+    description: str
+    runs: int
+    rating: float
+    categories: list[str]
+    featured: bool
+    is_available: bool
+    updated_at: datetime
+
+    # Score breakdown (for debugging/tuning)
+    combined_score: float
+    semantic_score: float = 0.0
+    lexical_score: float = 0.0
+    category_score: float = 0.0
+    recency_score: float = 0.0
+
+
+async def hybrid_search(
+    query: str,
+    featured: bool = False,
+    creators: list[str] | None = None,
+    category: str | None = None,
+    sorted_by: (
+        Literal["relevance", "rating", "runs", "name", "updated_at"] | None
+    ) = None,
+    page: int = 1,
+    page_size: int = 20,
+    weights: HybridSearchWeights | None = None,
+    min_score: float | None = None,
+) -> tuple[list[dict[str, Any]], int]:
+    """
+    Perform hybrid search combining semantic and lexical signals.
+
+    Args:
+        query: Search query string
+        featured: Filter for featured agents only
+        creators: Filter by creator usernames
+        category: Filter by category
+        sorted_by: Sort order (relevance uses hybrid scoring)
+        page: Page number (1-indexed)
+        page_size: Results per page
+        weights: Custom weights for search signals
+        min_score: Minimum relevance score threshold (0-1). Results below
+                   this score are filtered out. Defaults to DEFAULT_MIN_SCORE.
+
+    Returns:
+        Tuple of (results list, total count). Returns empty list if no
+        results meet the minimum relevance threshold.
+    """
+    if weights is None:
+        weights = DEFAULT_WEIGHTS
+    if min_score is None:
+        min_score = DEFAULT_MIN_SCORE
+
+    offset = (page - 1) * page_size
+
+    # Generate query embedding
+    query_embedding = await embed_query(query)
+
+    # Build WHERE clause conditions
+    where_parts: list[str] = ["sa.is_available = true"]
+    params: list[Any] = []
+    param_index = 1
+
+    # Add search query for lexical matching
+    params.append(query)
+    query_param = f"${param_index}"
+    param_index += 1
+
+    # Add lowercased query for category matching
+    params.append(query.lower())
+    query_lower_param = f"${param_index}"
+    param_index += 1
+
+    if featured:
+        where_parts.append("sa.featured = true")
+
+    if creators:
+        where_parts.append(f"sa.creator_username = ANY(${param_index})")
+        params.append(creators)
+        param_index += 1
+
+    if category:
+        where_parts.append(f"${param_index} = ANY(sa.categories)")
+        params.append(category)
+        param_index += 1
+
+    where_clause = " AND ".join(where_parts)
+
+    # Embedding is required for hybrid search - fail fast if unavailable
+    if query_embedding is None:
+        raise ValueError(
+            "Failed to generate query embedding. Hybrid search requires embeddings. "
+            "Check that openai_internal_api_key is configured and OpenAI API is accessible."
+        )
+
+    # Add embedding parameter
+    embedding_str = embedding_to_vector_string(query_embedding)
+    params.append(embedding_str)
+    embedding_param = f"${param_index}"
+    param_index += 1
+
+    # Optimized hybrid search query:
+    # 1. Direct join to UnifiedContentEmbedding via contentId=storeListingVersionId (no redundant JOINs)
+    # 2. UNION approach (deduplicates agents matching both branches)
+    # 3. COUNT(*) OVER() to get total count in single query
+    # 4. Optimized category matching with EXISTS + unnest
+    # 5. Pre-calculated max lexical score to avoid window function overhead
+    # 6. Simplified recency calculation with linear decay
+    sql_query = f"""
+            WITH candidates AS (
+                -- Lexical matches (uses GIN index on search column)
+                SELECT sa."storeListingVersionId"
+                FROM {{schema_prefix}}"StoreAgent" sa
+                WHERE {where_clause}
+                AND sa.search @@ plainto_tsquery('english', {query_param})
+
+                UNION
+
+                -- Semantic matches (uses HNSW index on embedding)
+                SELECT sa."storeListingVersionId"
+                FROM {{schema_prefix}}"StoreAgent" sa
+                INNER JOIN {{schema_prefix}}"UnifiedContentEmbedding" uce
+                    ON sa."storeListingVersionId" = uce."contentId" AND uce."contentType" = 'STORE_AGENT'
+                WHERE {where_clause}
+            ),
+            search_scores AS (
+                SELECT
+                    sa.slug,
+                    sa.agent_name,
+                    sa.agent_image,
+                    sa.creator_username,
+                    sa.creator_avatar,
+                    sa.sub_heading,
+                    sa.description,
+                    sa.runs,
+                    sa.rating,
+                    sa.categories,
+                    sa.featured,
+                    sa.is_available,
+                    sa.updated_at,
+                    -- Semantic score: cosine similarity (1 - distance)
+                    COALESCE(1 - (uce.embedding <=> {embedding_param}::vector), 0) as semantic_score,
+                    -- Lexical score: ts_rank_cd (will be normalized later)
+                    COALESCE(ts_rank_cd(sa.search, plainto_tsquery('english', {query_param})), 0) as lexical_raw,
+                    -- Category match: optimized with unnest for better performance
+                    CASE
+                        WHEN EXISTS (
+                            SELECT 1 FROM unnest(sa.categories) cat
+                            WHERE LOWER(cat) LIKE '%' || {query_lower_param} || '%'
+                        )
+                        THEN 1.0
+                        ELSE 0.0
+                    END as category_score,
+                    -- Recency score: linear decay over 90 days (simpler than exponential)
+                    GREATEST(0, 1 - EXTRACT(EPOCH FROM (NOW() - sa.updated_at)) / (90 * 24 * 3600)) as recency_score
+                FROM candidates c
+                INNER JOIN {{schema_prefix}}"StoreAgent" sa
+                    ON c."storeListingVersionId" = sa."storeListingVersionId"
+                LEFT JOIN {{schema_prefix}}"UnifiedContentEmbedding" uce
+                    ON sa."storeListingVersionId" = uce."contentId" AND uce."contentType" = 'STORE_AGENT'
+            ),
+            max_lexical AS (
+                SELECT MAX(lexical_raw) as max_val FROM search_scores
+            ),
+            normalized AS (
+                SELECT
+                    ss.*,
+                    -- Normalize lexical score by pre-calculated max
+                    CASE
+                        WHEN ml.max_val > 0
+                        THEN ss.lexical_raw / ml.max_val
+                        ELSE 0
+                    END as lexical_score
+                FROM search_scores ss
+                CROSS JOIN max_lexical ml
+            ),
+            scored AS (
+                SELECT
+                    slug,
+                    agent_name,
+                    agent_image,
+                    creator_username,
+                    creator_avatar,
+                    sub_heading,
+                    description,
+                    runs,
+                    rating,
+                    categories,
+                    featured,
+                    is_available,
+                    updated_at,
+                    semantic_score,
+                    lexical_score,
+                    category_score,
+                    recency_score,
+                    (
+                        {weights.semantic} * semantic_score +
+                        {weights.lexical} * lexical_score +
+                        {weights.category} * category_score +
+                        {weights.recency} * recency_score
+                    ) as combined_score
+                FROM normalized
+            ),
+            filtered AS (
+                SELECT
+                    *,
+                    COUNT(*) OVER () as total_count
+                FROM scored
+                WHERE combined_score >= {min_score}
+            )
+            SELECT * FROM filtered
+            ORDER BY combined_score DESC
+            LIMIT ${param_index} OFFSET ${param_index + 1}
+    """
+
+    # Add pagination params
+    params.extend([page_size, offset])
+
+    try:
+        # Execute search query - includes total_count via window function
+        results = await query_raw_with_schema(sql_query, *params)
+
+        # Extract total count from first result (all rows have same count)
+        total = results[0]["total_count"] if results else 0
+
+        # Remove total_count from results before returning
+        for result in results:
+            result.pop("total_count", None)
+
+        logger.info(
+            f"Hybrid search for '{query}': {len(results)} results, {total} total"
+        )
+
+        return results, total
+
+    except Exception as e:
+        logger.error(f"Hybrid search failed: {e}")
+        raise
+
+
+async def hybrid_search_simple(
+    query: str,
+    page: int = 1,
+    page_size: int = 20,
+) -> tuple[list[dict[str, Any]], int]:
+    """
+    Simplified hybrid search for common use cases.
+
+    Uses default weights and no filters.
+    """
+    return await hybrid_search(
+        query=query,
+        page=page,
+        page_size=page_size,
+    )

autogpt_platform/backend/backend/api/features/store/hybrid_search_test.py

@@ -0,0 +1,322 @@
+"""
+Integration tests for hybrid search with schema handling.
+
+These tests verify that hybrid search works correctly across different database schemas.
+"""
+
+from unittest.mock import patch
+
+import pytest
+
+from backend.api.features.store.hybrid_search import HybridSearchWeights, hybrid_search
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_with_schema_handling():
+    """Test that hybrid search correctly handles database schema prefixes."""
+    # Test with a mock query to ensure schema handling works
+    query = "test agent"
+
+    with patch(
+        "backend.api.features.store.hybrid_search.query_raw_with_schema"
+    ) as mock_query:
+        # Mock the query result
+        mock_query.return_value = [
+            {
+                "slug": "test/agent",
+                "agent_name": "Test Agent",
+                "agent_image": "test.png",
+                "creator_username": "test",
+                "creator_avatar": "avatar.png",
+                "sub_heading": "Test sub-heading",
+                "description": "Test description",
+                "runs": 10,
+                "rating": 4.5,
+                "categories": ["test"],
+                "featured": False,
+                "is_available": True,
+                "updated_at": "2024-01-01T00:00:00Z",
+                "combined_score": 0.8,
+                "semantic_score": 0.7,
+                "lexical_score": 0.6,
+                "category_score": 0.5,
+                "recency_score": 0.4,
+                "total_count": 1,
+            }
+        ]
+
+        with patch(
+            "backend.api.features.store.hybrid_search.embed_query"
+        ) as mock_embed:
+            mock_embed.return_value = [0.1] * 1536  # Mock embedding
+
+            results, total = await hybrid_search(
+                query=query,
+                page=1,
+                page_size=20,
+            )
+
+            # Verify the query was called
+            assert mock_query.called
+            # Verify the SQL template uses schema_prefix placeholder
+            call_args = mock_query.call_args
+            sql_template = call_args[0][0]
+            assert "{schema_prefix}" in sql_template
+
+            # Verify results
+            assert len(results) == 1
+            assert total == 1
+            assert results[0]["slug"] == "test/agent"
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_with_public_schema():
+    """Test hybrid search when using public schema (no prefix needed)."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "public"
+
+        with patch(
+            "backend.api.features.store.hybrid_search.query_raw_with_schema"
+        ) as mock_query:
+            mock_query.return_value = []
+
+            with patch(
+                "backend.api.features.store.hybrid_search.embed_query"
+            ) as mock_embed:
+                mock_embed.return_value = [0.1] * 1536
+
+                results, total = await hybrid_search(
+                    query="test",
+                    page=1,
+                    page_size=20,
+                )
+
+                # Verify the mock was set up correctly
+                assert mock_schema.return_value == "public"
+
+                # Results should work even with empty results
+                assert results == []
+                assert total == 0
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_with_custom_schema():
+    """Test hybrid search when using custom schema (e.g., 'platform')."""
+    with patch("backend.data.db.get_database_schema") as mock_schema:
+        mock_schema.return_value = "platform"
+
+        with patch(
+            "backend.api.features.store.hybrid_search.query_raw_with_schema"
+        ) as mock_query:
+            mock_query.return_value = []
+
+            with patch(
+                "backend.api.features.store.hybrid_search.embed_query"
+            ) as mock_embed:
+                mock_embed.return_value = [0.1] * 1536
+
+                results, total = await hybrid_search(
+                    query="test",
+                    page=1,
+                    page_size=20,
+                )
+
+                # Verify the mock was set up correctly
+                assert mock_schema.return_value == "platform"
+
+                assert results == []
+                assert total == 0
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_without_embeddings():
+    """Test hybrid search fails fast when embeddings are unavailable."""
+    # Patch where the function is used, not where it's defined
+    with patch("backend.api.features.store.hybrid_search.embed_query") as mock_embed:
+        # Simulate embedding failure
+        mock_embed.return_value = None
+
+        # Should raise ValueError with helpful message
+        with pytest.raises(ValueError) as exc_info:
+            await hybrid_search(
+                query="test",
+                page=1,
+                page_size=20,
+            )
+
+        # Verify error message is helpful
+        assert "Failed to generate query embedding" in str(exc_info.value)
+        assert "openai_internal_api_key" in str(exc_info.value)
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_with_filters():
+    """Test hybrid search with various filters."""
+    with patch(
+        "backend.api.features.store.hybrid_search.query_raw_with_schema"
+    ) as mock_query:
+        mock_query.return_value = []
+
+        with patch(
+            "backend.api.features.store.hybrid_search.embed_query"
+        ) as mock_embed:
+            mock_embed.return_value = [0.1] * 1536
+
+            # Test with featured filter
+            results, total = await hybrid_search(
+                query="test",
+                featured=True,
+                creators=["user1", "user2"],
+                category="productivity",
+                page=1,
+                page_size=10,
+            )
+
+            # Verify filters were applied in the query
+            call_args = mock_query.call_args
+            params = call_args[0][1:]  # Skip SQL template
+
+            # Should have query, query_lower, creators array, category
+            assert len(params) >= 4
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_weights():
+    """Test hybrid search with custom weights."""
+    custom_weights = HybridSearchWeights(
+        semantic=0.5,
+        lexical=0.3,
+        category=0.1,
+        recency=0.1,
+    )
+
+    with patch(
+        "backend.api.features.store.hybrid_search.query_raw_with_schema"
+    ) as mock_query:
+        mock_query.return_value = []
+
+        with patch(
+            "backend.api.features.store.hybrid_search.embed_query"
+        ) as mock_embed:
+            mock_embed.return_value = [0.1] * 1536
+
+            results, total = await hybrid_search(
+                query="test",
+                weights=custom_weights,
+                page=1,
+                page_size=20,
+            )
+
+            # Verify custom weights were used in the query
+            call_args = mock_query.call_args
+            sql_template = call_args[0][0]
+
+            # Check that weights appear in the SQL
+            assert "0.5" in sql_template  # semantic weight
+            assert "0.3" in sql_template  # lexical weight
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_min_score_filtering():
+    """Test hybrid search minimum score threshold."""
+    with patch(
+        "backend.api.features.store.hybrid_search.query_raw_with_schema"
+    ) as mock_query:
+        # Return results with varying scores
+        mock_query.return_value = [
+            {
+                "slug": "high-score/agent",
+                "agent_name": "High Score Agent",
+                "combined_score": 0.8,
+                "total_count": 1,
+                # ... other fields
+            }
+        ]
+
+        with patch(
+            "backend.api.features.store.hybrid_search.embed_query"
+        ) as mock_embed:
+            mock_embed.return_value = [0.1] * 1536
+
+            # Test with custom min_score
+            results, total = await hybrid_search(
+                query="test",
+                min_score=0.5,  # High threshold
+                page=1,
+                page_size=20,
+            )
+
+            # Verify min_score was applied in query
+            call_args = mock_query.call_args
+            sql_template = call_args[0][0]
+            assert "combined_score >= 0.5" in sql_template or ">= 0.5" in sql_template
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_pagination():
+    """Test hybrid search pagination."""
+    with patch(
+        "backend.api.features.store.hybrid_search.query_raw_with_schema"
+    ) as mock_query:
+        mock_query.return_value = []
+
+        with patch(
+            "backend.api.features.store.hybrid_search.embed_query"
+        ) as mock_embed:
+            mock_embed.return_value = [0.1] * 1536
+
+            # Test page 2 with page_size 10
+            results, total = await hybrid_search(
+                query="test",
+                page=2,
+                page_size=10,
+            )
+
+            # Verify pagination parameters
+            call_args = mock_query.call_args
+            params = call_args[0]
+
+            # Last two params should be LIMIT and OFFSET
+            limit = params[-2]
+            offset = params[-1]
+
+            assert limit == 10  # page_size
+            assert offset == 10  # (page - 1) * page_size = (2 - 1) * 10
+
+
+@pytest.mark.asyncio(loop_scope="session")
+@pytest.mark.integration
+async def test_hybrid_search_error_handling():
+    """Test hybrid search error handling."""
+    with patch(
+        "backend.api.features.store.hybrid_search.query_raw_with_schema"
+    ) as mock_query:
+        # Simulate database error
+        mock_query.side_effect = Exception("Database connection error")
+
+        with patch(
+            "backend.api.features.store.hybrid_search.embed_query"
+        ) as mock_embed:
+            mock_embed.return_value = [0.1] * 1536
+
+            # Should raise exception
+            with pytest.raises(Exception) as exc_info:
+                await hybrid_search(
+                    query="test",
+                    page=1,
+                    page_size=20,
+                )
+
+            assert "Database connection error" in str(exc_info.value)
+
+
+if __name__ == "__main__":
+    pytest.main([__file__, "-v", "-s"])

autogpt_platform/backend/backend/data/db.py

@@ -108,21 +108,84 @@ def get_database_schema() -> str:
     return query_params.get("schema", "public")
 
 
-async def query_raw_with_schema(query_template: str, *args) -> list[dict]:
-    """Execute raw SQL query with proper schema handling."""
+async def _raw_with_schema(
+    query_template: str,
+    *args,
+    execute: bool = False,
+    client: Prisma | None = None,
+) -> list[dict] | int:
+    """Internal: Execute raw SQL with proper schema handling.
+
+    Use query_raw_with_schema() or execute_raw_with_schema() instead.
+
+    Args:
+        query_template: SQL query with {schema_prefix} placeholder
+        *args: Query parameters
+        execute: If False, executes SELECT query. If True, executes INSERT/UPDATE/DELETE.
+        client: Optional Prisma client for transactions (only used when execute=True).
+
+    Returns:
+        - list[dict] if execute=False (query results)
+        - int if execute=True (number of affected rows)
+    """
     schema = get_database_schema()
     schema_prefix = f'"{schema}".' if schema != "public" else ""
     formatted_query = query_template.format(schema_prefix=schema_prefix)
 
     import prisma as prisma_module
 
-    result = await prisma_module.get_client().query_raw(
-        formatted_query, *args  # type: ignore
-    )
+    db_client = client if client else prisma_module.get_client()
+
+    if execute:
+        result = await db_client.execute_raw(formatted_query, *args)  # type: ignore
+    else:
+        result = await db_client.query_raw(formatted_query, *args)  # type: ignore
 
     return result
 
 
+async def query_raw_with_schema(query_template: str, *args) -> list[dict]:
+    """Execute raw SQL SELECT query with proper schema handling.
+
+    Args:
+        query_template: SQL query with {schema_prefix} placeholder
+        *args: Query parameters
+
+    Returns:
+        List of result rows as dictionaries
+
+    Example:
+        results = await query_raw_with_schema(
+            'SELECT * FROM {schema_prefix}"User" WHERE id = $1',
+            user_id
+        )
+    """
+    return await _raw_with_schema(query_template, *args, execute=False)  # type: ignore
+
+
+async def execute_raw_with_schema(
+    query_template: str, *args, client: Prisma | None = None
+) -> int:
+    """Execute raw SQL command (INSERT/UPDATE/DELETE) with proper schema handling.
+
+    Args:
+        query_template: SQL query with {schema_prefix} placeholder
+        *args: Query parameters
+        client: Optional Prisma client for transactions
+
+    Returns:
+        Number of affected rows
+
+    Example:
+        await execute_raw_with_schema(
+            'INSERT INTO {schema_prefix}"User" (id, name) VALUES ($1, $2)',
+            user_id, name,
+            client=tx  # Optional transaction client
+        )
+    """
+    return await _raw_with_schema(query_template, *args, execute=True, client=client)  # type: ignore
+
+
 class BaseDbModel(BaseModel):
     id: str = Field(default_factory=lambda: str(uuid4()))
 

autogpt_platform/backend/migrations/20260109181714_add_docs_embedding/migration.sql

@@ -0,0 +1,41 @@
+-- CreateExtension
+-- Create pgvector extension in the current schema (determined by DATABASE_URL schema parameter)
+-- This works with both "public" (CI/default) and "platform" (production) schemas
+CREATE EXTENSION IF NOT EXISTS "vector";
+
+-- CreateEnum
+CREATE TYPE "ContentType" AS ENUM ('STORE_AGENT', 'BLOCK', 'INTEGRATION', 'DOCUMENTATION', 'LIBRARY_AGENT');
+
+-- CreateTable
+CREATE TABLE "UnifiedContentEmbedding" (
+    "id" TEXT NOT NULL,
+    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "updatedAt" TIMESTAMP(3) NOT NULL,
+    "contentType" "ContentType" NOT NULL,
+    "contentId" TEXT NOT NULL,
+    "userId" TEXT,
+    "embedding" vector(1536) NOT NULL,
+    "searchableText" TEXT NOT NULL,
+    "metadata" JSONB NOT NULL DEFAULT '{}',
+
+    CONSTRAINT "UnifiedContentEmbedding_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateIndex
+CREATE INDEX "UnifiedContentEmbedding_contentType_idx" ON "UnifiedContentEmbedding"("contentType");
+
+-- CreateIndex
+CREATE INDEX "UnifiedContentEmbedding_userId_idx" ON "UnifiedContentEmbedding"("userId");
+
+-- CreateIndex
+CREATE INDEX "UnifiedContentEmbedding_contentType_userId_idx" ON "UnifiedContentEmbedding"("contentType", "userId");
+
+-- CreateIndex
+-- NULLS NOT DISTINCT ensures only one public (NULL userId) embedding per contentType+contentId
+-- Requires PostgreSQL 15+. Supabase uses PostgreSQL 15+.
+CREATE UNIQUE INDEX "UnifiedContentEmbedding_contentType_contentId_userId_key" ON "UnifiedContentEmbedding"("contentType", "contentId", "userId") NULLS NOT DISTINCT;
+
+-- CreateIndex
+-- HNSW index for fast vector similarity search on embeddings
+-- Uses cosine distance operator (<=>), which matches the query in hybrid_search.py
+CREATE INDEX "UnifiedContentEmbedding_embedding_idx" ON "UnifiedContentEmbedding" USING hnsw ("embedding" vector_cosine_ops);

autogpt_platform/backend/migrations/20260112173500_add_supabase_extensions_to_platform_schema/migration.sql

@@ -0,0 +1,23 @@
+-- -- Acknowledge Supabase-managed extensions to prevent drift warnings
+-- -- These extensions are pre-installed by Supabase in specific schemas
+-- -- This migration just documents their existence for Prisma's migration history
+
+-- -- Note: These schemas and extensions are created by Supabase, not by this migration
+-- -- Using IF NOT EXISTS ensures this migration is safe to run multiple times
+
+CREATE SCHEMA IF NOT EXISTS "extensions";
+CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";
+CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";
+CREATE EXTENSION IF NOT EXISTS "pg_net" WITH SCHEMA "extensions";
+CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";
+
+CREATE SCHEMA IF NOT EXISTS "graphql";
+CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";
+
+CREATE SCHEMA IF NOT EXISTS "pgsodium";
+CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";
+
+CREATE SCHEMA IF NOT EXISTS "vault";
+CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";
+

autogpt_platform/backend/schema.prisma

@@ -1,14 +1,15 @@
 datasource db {
-  provider  = "postgresql"
-  url       = env("DATABASE_URL")
-  directUrl = env("DIRECT_URL")
+  provider   = "postgresql"
+  url        = env("DATABASE_URL")
+  directUrl  = env("DIRECT_URL")
+  extensions = [pgvector(map: "vector")]
 }
 
 generator client {
   provider               = "prisma-client-py"
   recursive_type_depth   = -1
   interface              = "asyncio"
-  previewFeatures        = ["views", "fullTextSearch"]
+  previewFeatures        = ["views", "fullTextSearch", "postgresqlExtensions"]
   partial_type_generator = "backend/data/partial_types.py"
 }
 
@@ -127,8 +128,8 @@ model BuilderSearchHistory {
   updatedAt DateTime @default(now()) @updatedAt
 
   searchQuery String
-  filter      String[]  @default([])
-  byCreator   String[]  @default([])
+  filter      String[] @default([])
+  byCreator   String[] @default([])
 
   userId String
   User   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
@@ -721,26 +722,25 @@ view StoreAgent {
   storeListingVersionId String
   updated_at            DateTime
 
-  slug                String
-  agent_name          String
-  agent_video         String?
-  agent_output_demo   String?
-  agent_image         String[]
+  slug              String
+  agent_name        String
+  agent_video       String?
+  agent_output_demo String?
+  agent_image       String[]
 
-  featured         Boolean                  @default(false)
-  creator_username String?
-  creator_avatar   String?
-  sub_heading      String
-  description      String
-  categories       String[]
-  search           Unsupported("tsvector")? @default(dbgenerated("''::tsvector"))
-  runs                Int
-  rating              Float
-  versions            String[]
-  agentGraphVersions  String[]
-  agentGraphId        String
-  is_available        Boolean                  @default(true)
-  useForOnboarding    Boolean                  @default(false)
+  featured           Boolean  @default(false)
+  creator_username   String?
+  creator_avatar     String?
+  sub_heading        String
+  description        String
+  categories         String[]
+  runs               Int
+  rating             Float
+  versions           String[]
+  agentGraphVersions String[]
+  agentGraphId       String
+  is_available       Boolean  @default(true)
+  useForOnboarding   Boolean  @default(false)
 
   // Materialized views used (refreshed every 15 minutes via pg_cron):
   // - mv_agent_run_counts - Pre-aggregated agent execution counts by agentGraphId
@@ -856,14 +856,14 @@ model StoreListingVersion {
   AgentGraph        AgentGraph @relation(fields: [agentGraphId, agentGraphVersion], references: [id, version])
 
   // Content fields
-  name                String
-  subHeading          String
-  videoUrl            String?
-  agentOutputDemoUrl  String?
-  imageUrls           String[]
-  description         String
-  instructions        String?
-  categories          String[]
+  name               String
+  subHeading         String
+  videoUrl           String?
+  agentOutputDemoUrl String?
+  imageUrls          String[]
+  description        String
+  instructions       String?
+  categories         String[]
 
   isFeatured Boolean @default(false)
 
@@ -899,6 +899,9 @@ model StoreListingVersion {
   // Reviews for this specific version
   Reviews StoreListingReview[]
 
+  // Note: Embeddings now stored in UnifiedContentEmbedding table
+  // Use contentType=STORE_AGENT and contentId=storeListingVersionId
+
   @@unique([storeListingId, version])
   @@index([storeListingId, submissionStatus, isAvailable])
   @@index([submissionStatus])
@@ -906,6 +909,42 @@ model StoreListingVersion {
   @@index([agentGraphId, agentGraphVersion]) // Non-unique index for efficient lookups
 }
 
+// Content type enum for unified search across store agents, blocks, docs
+// Note: BLOCK/INTEGRATION are file-based (Python classes), not DB records
+// DOCUMENTATION are file-based (.md files), not DB records  
+// Only STORE_AGENT and LIBRARY_AGENT are stored in database
+enum ContentType {
+  STORE_AGENT // Database: StoreListingVersion
+  BLOCK // File-based: Python classes in /backend/blocks/
+  INTEGRATION // File-based: Python classes (blocks with credentials)
+  DOCUMENTATION // File-based: .md/.mdx files
+  LIBRARY_AGENT // Database: User's personal agents
+}
+
+// Unified embeddings table for all searchable content types
+// Supports both public content (userId=null) and user-specific content (userId=userID)
+model UnifiedContentEmbedding {
+  id        String   @id @default(uuid())
+  createdAt DateTime @default(now())
+  updatedAt DateTime @updatedAt
+
+  // Content identification
+  contentType ContentType
+  contentId   String // DB ID (storeListingVersionId) or file identifier (block.id, file_path)
+  userId      String? // NULL for public content (store, blocks, docs), userId for private content (library agents)
+
+  // Search data
+  embedding      Unsupported("vector(1536)") // pgvector embedding (extension in platform schema)
+  searchableText String // Combined text for search and fallback
+  metadata       Json                        @default("{}") // Content-specific metadata
+
+  @@unique([contentType, contentId, userId], map: "UnifiedContentEmbedding_contentType_contentId_userId_key")
+  @@index([contentType])
+  @@index([userId])
+  @@index([contentType, userId])
+  @@index([embedding], map: "UnifiedContentEmbedding_embedding_idx")
+}
+
 model StoreListingReview {
   id        String   @id @default(uuid())
   createdAt DateTime @default(now())
@@ -998,16 +1037,16 @@ model OAuthApplication {
   updatedAt DateTime @updatedAt
 
   // Application metadata
-  name               String
-  description        String?
-  logoUrl            String? // URL to app logo stored in GCS
-  clientId           String @unique
-  clientSecret       String // Hashed with Scrypt (same as API keys)
-  clientSecretSalt   String // Salt for Scrypt hashing
+  name             String
+  description      String?
+  logoUrl          String? // URL to app logo stored in GCS
+  clientId         String  @unique
+  clientSecret     String // Hashed with Scrypt (same as API keys)
+  clientSecretSalt String // Salt for Scrypt hashing
 
   // OAuth configuration
   redirectUris String[] // Allowed callback URLs
-  grantTypes   String[] @default(["authorization_code", "refresh_token"])
+  grantTypes   String[]           @default(["authorization_code", "refresh_token"])
   scopes       APIKeyPermission[] // Which permissions the app can request
 
   // Application management