Back to flagship work
Case Study · Enterprise Knowledge · RBAC RAG + Text-to-SQLAutomated Tests: 90 Passed

FinSight

Deployment model: Docker Compose Full Stack (FastAPI + React + Redis)

3-layer RBAC (0.93 security score, 0 filter leaks), 3-way query expansion + ChromaDB/FTS5/Cohere RRF, sandboxed in-memory DuckDB SQL, <20ms Redis cache, and 90 automated tests.

A full-stack enterprise RAG and Text-to-SQL workspace (FastAPI + React 19 + TypeScript + Docker Compose) where every department's documents and CSV tables stay scoped to authorized roles across 3 access layers, repeat queries replay in under 20ms via role-scoped Redis caching, and dual-track evals + 90 automated tests verify both quality and security.

PythonFastAPIReact 19TypeScriptLangChainGeminiChromaDBSQLite FTS5DuckDBRedisCohere RerankRAGASDocker Compose
FinSight narrated walkthrough frame
Narrated walkthrough previewGitHub source

Repository-Backed Evidence

Scoped Metrics & Verification

Linked to GitHub README
90

Automated Tests Passed

60 backend API & RBAC tests, 5 rate-limiting & role-scoped cache resilience tests, 20 OWASP security regression tests, and 5 frontend NDJSON stream tests.

Inspect verification source
0.93

RBAC Security Score

6 automated RBAC security tests across 6 roles scoring 0.933 overall with zero ChromaDB metadata-filter violations and 100% authorized access accuracy.

Inspect verification source
< 20ms

Role-Scoped Redis Replay

Repeat-query latency under 20ms via SHA-256 role-scoped Redis cache (automatic in-memory fallback), paired with tiered SlowAPI rate limiting and NDJSON streaming.

Inspect verification source
0.84 / 0.894

Answer Relevancy (Dual-Track)

Quota-free pipeline on 14 QA samples (0.840 answer relevancy, 0.817 answer similarity) plus Track 2 RAGAS LLM-as-judge (0.894 answer relevancy, 0.850 faithfulness).

Inspect verification source

System Architecture Topology

End-to-end dataflow and defensive isolation layers from request entry to verified output.

FinSight system architecture diagram

Core Architecture Pillars

Layered engineering safeguards designed so no single model call is the sole boundary.

Pillar 010 Filter Leaks

Pillar 1: 3-Layer Role-Based Access Control (RBAC)

Layer 1 validates HS256 JWTs with per-request DB role re-validation (stale-token defense). Layer 2 blocks cross-department queries before the LLM runs. Layer 3 enforces ChromaDB role metadata filtering and DuckDB table whitelists.

  • Layer 1: JWT HS256 + per-request SQLite role re-validation
  • Layer 2: Pre-LLM department guard (0 token spend on denial)
  • Layer 3: ChromaDB metadata filtering + DuckDB table whitelist
  • 0.93 RBAC security score with zero metadata-filter violations
Pillar 02RRF + Cohere

Pillar 2: Hybrid Retrieval Pipeline (Dense + BM25 + Rerank)

3-way query expansion, concurrent ChromaDB dense vector search and SQLite FTS5 BM25 sparse search, Reciprocal Rank Fusion (k=60), and optional Cohere Rerank v3 — always citing source documents within the user's role.

  • 3-way semantic query expansion in parallel
  • ChromaDB dense cosine + SQLite FTS5 BM25 sparse search
  • Reciprocal Rank Fusion (RRF) candidate merging
  • Optional Cohere rerank-english-v3.0 -> top 6 grounded chunks
Pillar 03OWASP Sandbox

Pillar 3: Query Router & In-Memory DuckDB SQL Sandbox

LLM router classifies queries into RAG, SQL, or greeting with automatic SQL-to-RAG fallback. Generated SQL executes strictly in an in-memory DuckDB sandbox with external access disabled, SELECT-only enforcement, 10s timeout, and 1,000-row cap.

  • duckdb.connect(':memory:', config={'enable_external_access': False})
  • SELECT-only query validation (blocks DDL/DML & host file reads)
  • 10-second interrupt timer, 256MB memory cap & 1,000-row cap
  • Automatic SQL -> RAG fallback on empty results or errors
Pillar 0490 Tests

Pillar 4: Sub-20ms Redis Cache, NDJSON & Dual-Track Evals

Role-scoped Redis cache (in-memory fallback) replays repeat queries under 20ms over NDJSON streams, defended by SlowAPI rate limits (/login 5/min, /upload-docs 10/min, /chat 30/min) and measured via RAGAS + quota-free eval pipelines.

  • Role-scoped Redis cache (`finsight:query:{role}:{digest}`) < 20ms
  • Tiered SlowAPI rate limiting & NDJSON token streaming to React 19
  • RetryingEmbeddings (10× backoff) + startup DB self-healing
  • 90 automated tests + RAGAS & quota-free 14-sample eval pipelines

The Problem & Root Cause

Enterprise knowledge lives across siloed department reports and structured CSV spreadsheets. Off-the-shelf chat wrappers either hallucinate spreadsheet math or rely on prompt instructions for access control — risking payroll and financial leakage.

Engineering Contribution

Built the entire vertical slice packaged with Docker Compose: FastAPI backend with 3-layer RBAC, 3-way query expansion + ChromaDB/FTS5/Cohere hybrid retrieval, LLM query router with automatic SQL-to-RAG fallback, isolated in-memory DuckDB SQL sandbox, role-scoped Redis cache (<20ms replay), tiered SlowAPI rate limiting, React 19 NDJSON streaming client, dual-track RAGAS + quota-free evals, and 90 automated tests.

Explore the Workflow

Step through concrete scenarios to see how the system checks boundaries, reproduces failures, or degrades under quota pressure.

Illustrative example

HR user: ‘Summarize the employee onboarding policy.’

Step 1 / 4

Check access

Verify JWT HS256 signature and re-validate current role in SQLite.

HR role confirmed -> authorized HR + General scopes

Architecture Decision Records (ADRs)

Key design choices, technical rationale, and accepted trade-offs behind the implementation.

ADR-01Engineering Trade-off

Enforce department access in three layers before generation

Per-request JWT role re-validation, pre-LLM department guards, and ChromaDB/DuckDB store filters ensure unauthorized queries never reach the model or restricted data.

Why this decision:Eliminates prompt-injection bypasses over cross-department data and cuts token spend on rejected requests to zero.
Trade-off accepted:Requires maintaining phrase calibration alongside database permissions for shared terms like 'revenue'.
ADR-02Engineering Trade-off

Hybrid ChromaDB + SQLite FTS5 fused via Reciprocal Rank Fusion

Combines dense semantic search with stdlib SQLite FTS5 BM25 keyword ranks using RRF (k=60), followed by optional Cohere reranking.

Why this decision:Dense vectors capture paraphrases while BM25 catches exact acronyms and IDs without extra database infrastructure.
Trade-off accepted:Executes parallel dense and sparse searches per query and keeps both indexes synchronized on upload.
ADR-03Engineering Trade-off

Isolated in-memory DuckDB sandbox for CSV analytics

Copies only authorized CSV tables into an in-memory DuckDB connection (`enable_external_access=False`, SELECT-only, 10s timeout, 1,000-row cap) with automatic SQL-to-RAG fallback.

Why this decision:Resolves structured aggregations deterministically instead of hallucinating numbers, without exposing host files.
Trade-off accepted:Caps single-query output at 1,000 rows and keeps analytical state process-local for single-node Docker deployment.

Walkthrough & Repository

Watch the narrated system walkthrough below, or clone the repository to run the complete Docker Compose Full Stack (FastAPI + React + Redis) setup and automated test suite locally.

Full source code, Docker Compose config, test suites & README on GitHubRead repository docs

Limits & Production Lessons

System Boundaries & Known Limitations

  • The background indexer, evaluation lock, SQLite WAL DB, and DuckDB instance are process-local; multi-node horizontal scaling requires externalizing those services.
  • The DuckDB sandbox enforces a 1,000-row result cap and 10-second interrupt timer against runaway aggregations.
  • Shared general terms (such as 'revenue' appearing in general company docs) require explicit phrase calibration, documented transparently in the RBAC test suite.

What This System Demonstrates

Getting enterprise AI into real workflows comes down to the unglamorous systems work: per-request access validation, sandboxed SQL execution, role-scoped caching, and 90 automated tests that prove boundaries hold.

Roles & Technical Collaboration

Hiring a GenAI Engineer or tackling a similar AI architecture?

I’m open to full-time remote GenAI & AI Systems Engineering roles, as well as focused architecture and evaluation engagements.