Files · Cohere Knowledge Agent for PostgreSQL-Backed Financial Analytics

# Cohere Knowledge Agent for PostgreSQL-Backed Financial Analytics
 
> A conversational knowledge agent that answers financial questions by querying a PostgreSQL-backed knowledge base with semantic caching.
 
A tutorialized reference solution from [reaatech.com](https://reaatech.com), demonstrating how to build production-grade AI systems with the `@reaatech/*` package family.
 
## Problem
 
Financial analysts spend hours manually querying databases and spreadsheets to answer routine questions about transactions, expenses, and cash flow. Traditional BI tools lack conversational interfaces and cannot reason across unstructured descriptions.
 
## Architecture
 
```
User → Next.js UI → Express API → Confidence Router → Embedding → PGVector/PostgreSQL
                                      ↓                     ↓
                                  Cohere LLM ←──────── Semantic Cache
                                      ↓
                              Output Repair → Response → Langfuse Trace
```
 
The request flow:
1. **User message** → Express `POST /api/chat`
2. **Confidence Router** classifies intent (financial vs. unknown)
3. **ROUTE**: embed query → PGVector similarity search → exact/semantic cache lookup
4. **Cache miss**: Cohere generates answer with transaction context as system prompt
5. **Output Repair** validates/fixes LLM response against a Zod schema
6. **Response** returned to user; conversation + trace persisted
 
## REAA Packages
 
| Package | Role |
|---------|------|
| `@reaatech/agent-memory-core` | Memory data structures, `ConversationTurn` type, `withRetry`, `setLogger`/`getLogger` |
| `@reaatech/confidence-router-core` | `DecisionEngine` with threshold-based ROUTE/CLARIFY/FALLBACK decisions |
| `@reaatech/llm-cache` | `CacheEngine` with exact-match + semantic (cosine) caching |
| `@reaatech/structured-repair-core` | `repair`/`repairOutput` to fix malformed LLM JSON outputs |
 
## Third-Party Packages
 
| Package | Purpose |
|---------|---------|
| `cohere-ai` | Cohere `command-a-03-2025` LLM via `CohereClientV2` |
| `fastembed` | Local embedding via `FlagEmbedding` (BGEBaseEN, 384d) |
| `pg` + `pgvector` | PostgreSQL connection pool + vector type registration |
| `drizzle-orm` | Type-safe query builder with `l2Distance` ordering |
| `zod` | Schema validation for config, requests, and LLM output |
| `langfuse` | LLM observability — tracing for generations, cache hits |
| `express` | HTTP server with JSON body parsing |
| `dotenv` | Load `.env` file into `process.env` |
 
## Quick Start
 
```bash
pnpm install
cp .env.example .env
# Edit .env with your credentials
pnpm dev          # starts Express server on :3001
pnpm test         # vitest run with coverage
pnpm typecheck    # tsc --noEmit
pnpm lint         # eslint .
```
 
## API Reference
 
### `POST /api/chat`
 
**Request body:**
```json
{
  "sessionId": "string (required, min 1)",
  "message": "string (required, 1-2000 chars)"
}
```
 
**Response (200):**
```json
// Route hit — Cohere generated answer
{ "type": "answer", "data": { "answer": "...", "confidence": 0.95, "sources": [] } }
 
// Clarification needed
{ "type": "clarification", "message": "Could you provide more detail..." }
 
// Fallback — query not understood
{ "type": "fallback", "message": "I'm not sure how to answer..." }
```
 
**Error responses:**
- `400` — Validation failure (`{ "error": "Validation failed", "details": {...} }`)
- `503` — LLM unavailable (`{ "error": "LLM service temporarily unavailable" }`)
- `500` — Internal error (`{ "error": "Internal server error" }`)
 
### `GET /health`
 
**Response:**
```json
{ "status": "ok" }
```
 
## Environment Variables
 
| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `COHERE_API_KEY` | Yes | — | Cohere API key |
| `DATABASE_URL` | Yes | — | PostgreSQL connection string |
| `PORT` | No | `3001` | Express server port |
| `LANGFUSE_PUBLIC_KEY` | No | — | Langfuse public key |
| `LANGFUSE_SECRET_KEY` | No | — | Langfuse secret key |
| `LANGFUSE_HOST` | No | `https://cloud.langfuse.com` | Langfuse host URL |
| `CACHE_TTL_DEFAULT` | No | `3600` | Default cache TTL (seconds) |
| `SIMILARITY_THRESHOLD` | No | `0.8` | Semantic cache cosine threshold |
| `ROUTE_THRESHOLD` | No | `0.8` | Confidence router route threshold |
| `FALLBACK_THRESHOLD` | No | `0.3` | Confidence router fallback threshold |
 
## Project Structure
 
```
.
├── app/
│   ├── page.tsx                    Chat UI (Next.js client component)
│   ├── layout.tsx                  Root layout
│   ├── globals.css                 Global styles
│   ├── favicon.ico                 Favicon
│   └── page.module.css             Page styles
├── src/
│   ├── index.ts                    Express server entry point
│   ├── lib/
│   │   └── config.ts               Zod-validated environment config
│   ├── api/
│   │   └── chat/
│   │       └── route.ts            POST /api/chat handler
│   └── services/
│       ├── database.ts             Drizzle ORM + pg Pool with pgvector
│       ├── embedding.ts            Fastembed (BGEBaseEN) embedding
│       ├── cohere.ts               CohereClientV2 chat wrapper
│       ├── memory.ts               Conversation context manager
│       ├── confidence.ts           Confidence router DecisionEngine
│       ├── cache.ts                Semantic LLM cache (InMemoryAdapter)
│       ├── repair.ts               Structured output repair (Zod)
│       └── langfuse.ts             Langfuse observability tracing
├── tests/
│   ├── setup.ts                    MSW + vitest global setup
│   ├── mocks/
│   │   ├── handlers.ts             MSW request handlers
│   │   └── server.ts               MSW server instance
│   ├── lib/
│   │   └── config.test.ts
│   ├── services/
│   │   ├── database.test.ts
│   │   ├── embedding.test.ts
│   │   ├── cohere.test.ts
│   │   ├── memory.test.ts
│   │   ├── confidence.test.ts
│   │   ├── cache.test.ts
│   │   ├── repair.test.ts
│   │   └── langfuse.test.ts
│   ├── api/
│   │   └── chat/
│   │       └── route.test.ts
│   └── index.test.ts
├── packages/                       API reference docs for all deps
├── .env.example                    Environment variable template
├── DEV_PLAN.md                     Build plan
├── vitest.config.ts                Vitest configuration
├── tsconfig.json                   TypeScript configuration
└── package.json                    Dependencies and scripts
```
 
## Testing
 
```bash
pnpm test          # Run all tests with coverage
pnpm typecheck     # TypeScript type checking
pnpm lint          # ESLint
```
 
Tests use MSW (Mock Service Worker) to intercept HTTP calls to the Cohere API. Database services are mocked with `vi.mock`. No real external services are called during testing.
 
Coverage thresholds: 90% on lines, branches, functions, and statements across `src/**/*.ts` and `app/**/route.ts`.
 
## License
 
MIT — see [LICENSE](./LICENSE).