High-fidelity geospatial search for complex local marketplaces.

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

We built a Hybrid Search Engine. The pipeline extracts live merchant data, embeds semantic intent via Azure OpenAI on ingestion, and stores outputs in a pgvector index.

The Fallback: 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.

The Problem: Mobile maps typically drain battery and spike API costs by fetching data on every micro-movement.

Result: The architecture was designed and tested to lower map-related API costs and ensure smoother scrolling on mid-range devices once deployed.

In the real world, entities rarely fit into neat boxes. A "Coffee Shop" might also be a "Coffee Roaster" (Maker). Instead of creating three separate accounts, we implemented a Polymorphic Schema.

Performance isn't just about server response times; it's about Perceived Latency. The solution employs an Optimistic UI pattern for high-frequency user actions.

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

1. Backend: Atomic Integrity

await prisma.$transaction(async (tx) => {
    // 1. Reserve Entity ID (Atomic)
    const record = await tx.embedding.create({ ... });

    // 2. External Vector Gen (Rollback on Fail)
    const vector = await openai.embeddings.create({ input: text });

    await tx.embedding.update({ ... });
});

2. Frontend: Optimistic UI

const toggleSave = useCallback(async (id) => {
    // 1. Zero Latency 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: Clerk Auth Guard

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
}