Files · Vertex AI Knowledge Agent for BigQuery SMB Data Queries

# Vertex AI Knowledge Agent for BigQuery SMB Data Queries
 
> Index business data from BigQuery into a vector knowledge base and let users ask natural language questions, with budget-aware caching and step-by-step answer generation.
 
## Problem
 
SMBs store operational data in BigQuery but lack easy ways to query it in natural language. This recipe builds a knowledge agent that syncs BigQuery tables into a pgvector-powered knowledge base and answers questions using Vertex AI Gemini with caching, budget enforcement, and confidence routing.
 
## Architecture
 
### Sync Pipeline
 
```
BigQuery Table → BigQueryClient (googleapis) → Embedder (VoyageAI) → VectorStore (pgvector)
```
 
The sync pipeline streams rows from BigQuery, converts each row to a text representation, generates embeddings via VoyageAI, and upserts them into a PostgreSQL table with a `vector(1024)` column.
 
### Query Pipeline
 
```
User Query → Budget Check → Cache Lookup → Session Load → Embed + Vector Search → LLM Generation (Vertex AI Gemini) → Confidence Eval → Cost Recording → Cache Store → Response
```
 
## Prerequisites
 
- Google Cloud Platform project with BigQuery and Vertex AI API enabled
- Application Default Credentials configured (`gcloud auth application-default-login`)
- VoyageAI API key
- OpenAI API key (for embeddings in cache and agent-memory)
- PostgreSQL instance with pgvector extension
- Node.js >= 22
 
## Environment Variables
 
| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `GOOGLE_CLOUD_PROJECT` | Yes | — | GCP project ID |
| `GOOGLE_CLOUD_LOCATION` | No | `us-central1` | GCP region |
| `GOOGLE_APPLICATION_CREDENTIALS` | No | — | Path to service account key |
| `VOYAGE_API_KEY` | Yes | — | VoyageAI API key |
| `OPENAI_API_KEY` | Yes | — | OpenAI API key |
| `DATABASE_URL` | Yes | — | PostgreSQL connection string |
| `LANGFUSE_PUBLIC_KEY` | Yes | — | Langfuse public key |
| `LANGFUSE_SECRET_KEY` | Yes | — | Langfuse secret key |
| `LANGFUSE_HOST` | No | `https://cloud.langfuse.com` | Langfuse host |
| `BIGQUERY_DATASET` | Yes | — | BigQuery dataset ID |
| `BIGQUERY_TABLE` | Yes | — | BigQuery table ID |
| `DEFAULT_DAILY_BUDGET` | No | `5.0` | Daily budget per user in USD |
| `LLM_CACHE_SIMILARITY_THRESHOLD` | No | `0.85` | Cosine similarity threshold for cache hits |
| `LLM_CACHE_TTL` | No | `3600` | Cache TTL in seconds |
 
## Quick Start
 
```bash
pnpm install
cp .env.example .env.local
# Edit .env.local with your credentials
pnpm dev
```
 
### Sync BigQuery data
 
```bash
curl -X POST http://localhost:3000/api/bigquery-sync
```
 
### Ask a question
 
```bash
curl -X POST http://localhost:3000/api/query \
  -H "Content-Type: application/json" \
  -d '{"query": "What were our top products last month?", "userId": "user-1"}'
```
 
## API Reference
 
### `POST /api/query`
 
Query the knowledge agent.
 
**Body:**
```json
{
  "query": "string (required, 1-2000 chars)",
  "userId": "string (required)",
  "sessionId": "string (optional)"
}
```
 
**Response:** `QueryResponse` with answer, sources, confidence, and cost.
 
### `POST /api/bigquery-sync`
 
Trigger a sync from BigQuery to the vector store.
 
**Response:** `{ status: "ok", result: SyncResult }`
 
### `GET /api/health`
 
Health check endpoint.
 
**Response:** `{ status: "healthy", timestamp: "ISO date string" }`
 
## Project Structure
 
```
app/
  api/
    query/route.ts        Query endpoint
    bigquery-sync/route.ts Sync endpoint
    health/route.ts        Health check
  page.tsx                 Landing page
src/
  types.ts                 Shared types
  config.ts                Zod-validated config
  instrumentation.ts       Langfuse setup
  index.ts                 App entry
  sync/
    bigquery-client.ts     BigQuery streaming client
    embedder.ts            VoyageAI embedding
    vector-store.ts        pgvector operations
    index.ts               Sync orchestrator
  memory/
    agent-memory-service.ts Agent memory (REAA)
  api/
    llm-service.ts         Vertex AI Gemini
    cache-service.ts       LLM cache (REAA)
    budget-service.ts      Budget controller (REAA)
    confidence-service.ts  Confidence router (REAA)
    session-service.ts     Session continuity (REAA)
    cost-telemetry.ts      Cost tracking (REAA)
    query-service.ts       Query orchestrator
tests/                     Vitest tests
```