Hybrid Search · SQL KB Pattern · Project 7

AEM Knowledge Agent

Internal knowledge base agent for AEM (Adobe Experience Manager). Answers natural language questions using configurable hybrid search — SQL keyword (ILIKE) + pgvector semantic search combined with adjustable weights. Redis caching reduces LLM calls by 80% on repeated queries.

Anthropic Claude · Neon Postgres · pgvector · Voyage AI · Upstash Redis GitHub ↗

New Concepts This Project

Hybrid search — keyword (ILIKE) + semantic (pgvector) combined with configurable weights. SQL KB pattern — LLM queries structured DB via tools, never writes raw SQL. Configurable search modes — user switches SQL/semantic/hybrid at runtime from UI. Redis caching — same question asked twice skips LLM entirely, 80% cost reduction.

What It Does

"How do I create a page in AEM?"
  → search_knowledge_base() → SQL/vector search → LLM answers ✅

"Who owns /content/site/en/home?"
  → get_page_owner() → SQL lookup → "Sarah Johnson, Marketing" ✅

"I'm getting a 404 error"
  → get_error_guide() → SQL lookup → step-by-step fix ✅

Architecture

User asks question
        ↓
POST /ask
        ↓
Check Redis cache
  HIT  → return instantly ⚡ (FREE)
  MISS → run agent
        ↓
Agent calls tools:
  search_knowledge_base → hybrid search
  get_page_owner        → SQL lookup
  get_error_guide       → SQL lookup
        ↓
LLM reads results → answers in own words
        ↓
Save to Redis cache (TTL: 1 hour)
        ↓
Return answer to UI ✅

Search Modes

🗄️ SQL Only (keyword):
  SELECT * FROM aem_guides
  WHERE content ILIKE '%create page AEM%'
  Fast. Exact matches. No Voyage AI calls. FREE.

🧠 Semantic (pgvector):
  embed(query) → cosine similarity search
  Finds meaning not just words.
  "make a new page" → finds "create page" guide ✅

⚡ Hybrid (default):
  keyword_score  × 0.3
  semantic_score × 0.7
  = final_score
  Best of both worlds ✅

Weights adjustable via UI slider
30/70 → 50/50 → 10/90 etc.

How Hybrid Search Works

User: "make a new page"
        ↓
Keyword search:
  ILIKE '%make%new%page%'
  → finds 1 result (score: 0.4)
  → misses because "make" not in docs ⚠️

Semantic search:
  embed("make a new page")
  → cosine similarity vs all embeddings
  → finds "How to Create a Page" (score: 0.94) ✅

Combine + deduplicate:
  keyword_score  × 0.3 = 0.12
  semantic_score × 0.70 = 0.658
  final_score = 0.778

Top 5 results → LLM → Answer ✅

Without hybrid: keyword alone would miss this query
With hybrid: semantic fills the vocabulary gap ✅

Tool Definitions

[
  {
    "name": "search_knowledge_base",
    "description": "Search AEM docs, how-to guides, workflows, troubleshooting.
      Use for: how-to questions, access requests, workflow questions.",
    "input_schema": {
      "properties": {
        "query":    { "type": "string" },
        "category": { "type": "string",
          "enum": ["AEM Authoring","Access Management","Workflows","Troubleshooting"] }
      }
    }
  },
  {
    "name": "get_page_owner",
    "description": "Look up who owns a specific AEM page path.",
    "input_schema": { "properties": { "page_path": {"type":"string"} } }
  },
  {
    "name": "get_error_guide",
    "description": "Get troubleshooting guide for specific error code (404, 403, 500).",
    "input_schema": { "properties": { "error_code": {"type":"string"} } }
  }
]

Turn-by-Turn LLM Flow

You: "please help me to create a page in AEM"
        ↓
Check Redis → MISS → run agent
        ↓
Turn 1 — LLM Request:
  messages: [{ role: "user", content: "please help me..." }]

Turn 1 — LLM Response:
  stop_reason: "tool_use"
  content: [{
    type:  "tool_use",
    name:  "search_knowledge_base",
    input: { query: "create page AEM" }
  }]
        ↓
YOUR CODE runs SQL + vector search:
  ILIKE '%create page AEM%'  → 2 results
  pgvector cosine similarity → 3 results
  Combined + scored          → top 5
        ↓
Turn 2 — Send results back to LLM:
  messages: [...,
    { role: "user", content: [{
      type:        "tool_result",
      tool_use_id: "tool_001",
      content:     JSON.stringify({ found: true, results: [...] })
    }] }
  ]
        ↓
Turn 2 — LLM reads DB results:
  stop_reason: "end_turn"
  "Here's how to create a page in AEM! 🎉
   1. Navigate to AEM Sites console..."
        ↓
Save answer to Redis (TTL: 1 hour)
        ↓
Same question tomorrow → Redis HIT → instant ⚡

DB Schema

aem_guides (
  id        SERIAL PRIMARY KEY,
  title     TEXT,
  category  TEXT,        -- AEM Authoring / Access Management / etc
  content   TEXT,        -- full guide text
  tags      TEXT[],
  embedding VECTOR(512)  -- Voyage AI voyage-3-lite
)

page_owners (
  page_path   TEXT UNIQUE,  -- /content/site/en/home
  owner_name  TEXT,
  owner_email TEXT,
  team        TEXT
)

error_guides (
  error_code TEXT,          -- 404 / 403 / 500 / REPL_001
  title      TEXT,
  symptoms   TEXT,
  solution   TEXT,
  embedding  VECTOR(512)
)

Redis Caching — How It Works

Cache key = hash(question + searchMode)
  "how do i create a page" + "hybrid:5"
  → "aem-kb:a3f9c2d1"

First request:
  Cache MISS → run agent → LLM call → $0.001
  Save result to Redis (TTL: 1 hour)

Same question again:
  Cache HIT → return instantly → FREE ✅

1000 users ask same question:
  1 LLM call + 999 Redis hits
  = 99.9% cost reduction ✅

Cost Optimization

Naive (no optimization):
  1000 questions/day × $0.001 = $1.00/day

Optimized (Redis cache + SQL first):
  ~200 unique questions × $0.001 = $0.20/day
  800 cache hits = FREE
  = 80% cost reduction ✅

Embedding cost (Voyage AI):
  $0.02 per 1M tokens
  One query = ~50 tokens = $0.000001
  Almost FREE — optimize LLM not embeddings

What's New vs Project 3 (Site Search)

P3 Site Search:
  → Pure semantic search only
  → One fixed mode
  → Product catalog data

P7 AEM Knowledge Agent:
  → Hybrid search (configurable) ✅ NEW
  → SQL tools for structured lookups ✅ NEW
  → Runtime mode switching from UI ✅ NEW
  → Redis answer caching ✅ NEW
  → Real enterprise use case ✅ NEW
  → Multiple table types (guides, owners, errors) ✅ NEW