CLIENT ENGAGEMENT · ARCHITECTURE CASE STUDY · AUSTIN, TX

AI search for messy marketplace queries.

ScoutLocal needed search that could understand vague queries like “vibey coffee spot” without losing real marketplace facts such as location, hours, and merchant data.

Semantic matchingSQL fact groundingpgvector search

Discuss a search build Read build notes

CASE STUDY AT A GLANCE

ProblemKeyword filters created dead ends for natural-language marketplace searches.

InterventionCombine semantic embeddings with deterministic SQL and geospatial constraints.

ResultMore forgiving discovery while keeping answers grounded in marketplace data.

Next.js 15 NestJS PostgreSQL Azure OpenAI Clerk

AT A GLANCE

Hybrid semantic + SQL search for messy queries
Geospatial fetching designed to reduce wasted map requests
Polymorphic schema + optimistic UI patterns
Clerk identity integration for merchant role separation

PERFORMANCE CONSIDERATIONS

Cache hits

Reuse computed embeddings

Reuse stored vectors in PostgreSQL instead of generating an embedding for every repeated query.

Cache misses

Generate only when needed

A cache miss calls the embedding service, making the external request explicit in the search path.

Controlled fallback

Prefer deterministic results

If semantic matching is not confident enough, the system falls back to structured SQL search.

BUILT FOR

SaaS Product Teams

Needing better search/recommendations without hiring a full-time specialist or rebuilding the product from scratch.

Marketplaces

Dealing with overlapping entities (merchants, events) and heavy read-traffic.

Scaling Startups

Trying to scale past MVP without letting technical debt in search crush your velocity.

THE SCENARIO & THE CONSTRAINT

Natural-Language Intent Meets SQL Fact Grounding

The Scenario: ScoutLocal's marketplace needed to accommodate "vague" human searches (e.g., "vibey coffee spot for reading") rather than strict SQL category dropdowns.

The Constraint Existing relational search could not handle natural-language nuance on its own. A raw LLM call in the search path would add latency and risk inventing venues. The safer pattern was semantic matching for intent, then deterministic filters for facts.

HYBRID PIPELINE ARCHITECTURE

PostgreSQL Vector Search & Strict Fallback

We built a Hybrid Search Engine. The pipeline extracts live merchant data, embeds semantic intent via Azure OpenAI (`text-embedding-3-small`) on ingestion, and computes cosine similarity directly in PostgreSQL via a PL/pgSQL function.

01 / USER INTENT "Vibey coffee" Query Vector Embedding via Azure OpenAI

02 / SEMANTIC MATCH PostgreSQL cosine_similarity Native PL/pgSQL Similarity Computation

03 / SQL VALIDATE Deterministic Constraints Prisma filters by location/hours

The Fallback Mechanism If the vector search returns results with a semantic confidence score below our tuned threshold, the engine automatically falls back to a strict SQL ILIKE search. This prevents the system from guessing and returns a deterministic empty state if required.

IMPLEMENTATION SPOTLIGHT

Implementation Patterns

Below are the core engineering patterns used to keep vectors and entities in sync, avoiding "ghost" records and race conditions.

1. Backend: Atomic Integrity

api/embeddings/sync.service.ts

// 1. Reserve Entity ID (Atomic & Fast)
const record = await prisma.embedding.create({ data: { status: 'PENDING', content: text } });

// 2. External Vector Gen (Network I/O Outside DB Lock)
const response = await openai.embeddings.create({ input: text });
const vector = response.data[0].embedding;

// 3. Save Vector (Separate Fast Write)
await prisma.embedding.update({ 
  where: { id: record.id }, 
  data: { status: 'READY', vector } 
});

2. Frontend: Optimistic UI Pattern

hooks/useSavedItems.ts

const toggleSave = useCallback(async (id) => {
    // 1. Immediate UI Update
    setSavedIds((prev) => new Set(prev).add(id));

    try {
        await api.post(`/user/saved/${id}`);
    } catch (err) {
        // 2. Self-Healing Rollback
        setSavedIds((prev) => { ... });
        toast.error("Sync failed");
    }
}, []);

3. Identity & Auth Guard

app/api/search/route.ts

export async function POST(req: Request) {
  const { userId } = auth(); // Clerk Identity
  if (!userId) return new Response("Unauthorized", { status: 401 });
  
  // Proceed with secure search/sync
  // Merchant isolation is enforced by userId
}

NEXT STEPS

Want this kind of search or product build?

MVP Build Path

If you are building a product with search, user accounts, data workflows, and launch constraints, start with the MVP path.

Product scope and user path
Data model and search requirements

View MVP Build Path

Search or Workflow Build

$4,000 - $8,000 / Fixed

A focused 2-4 week build for one search, routing, reporting, dashboard, or automation system wired into your existing tools.

One critical workflow mapped
Focused changes implemented
Full code handoff & docs

Discuss Build Plan