RBAC SQL Assistant

An enterprise-grade, secure Natural Language to SQL translation and execution assistant. Designed to bridge the gap between business users and database query insights without sacrificing database security, access controls, or auditing compliance.

---

🏗️ Architectural Layout

The system uses a Dual-Database Isolation pattern to keep administrative controls separated from query analysis:

graph TD
    User([User Client]) -->|Next.js Web UI| WebApp[apps/web]
    WebApp -->|Authenticated REST API| API[apps/api Fastify]
    API -->|Manage Users, History, Audits| DB_Internal[(Internal PG DB)]
    API -->|Validate EXPLAIN / Execute SELECT| DB_Target[(Target PG DB)]
    API -->|Schema-Aware Prompt Translation| Ollama[Ollama Local LLM]

Loading

1. Internal Database: Stores system user profiles, role privileges, connection parameters, user query histories, and compliance audit logs.
2. Target Database: The analytics or production database that users query. The assistant only connects to this database to retrieve schemas or run authorized queries.

---

🌟 Key Features

1. Context-Aware Natural Language → SQL

• Translates plain English user prompts into optimized PostgreSQL queries.
• Dynamically injects target schema catalogs (table names, column formats, primary/foreign keys) into the LLM system prompt to ensure syntax-correct schema alignments.
• Safe local rule-based translation fallback kicks in if the local Ollama daemon is unreachable.

2. Multi-Level RBAC Enforcement

Gated role scopes prevent unauthorized database access or mutations:

• Admin: Full access. Manage connection credentials, create/update system users, inspect compliance audit logs, and execute any statement (including DELETE).
• Editor: Can translate prompts, view schemas, and execute SELECT, INSERT, and UPDATE statements.
• Viewer: Read-only access. Can translate prompts, browse schema trees, and execute SELECT queries only.

3. Query Execution Protection

• Pre-Execution Validation: Validates statement syntax and schema mappings prior to execution by running EXPLAIN <sql> on the target Postgres engine. This verifies table and column references without mutating records.
• Injection Gating: Prevents multiple statement execution (blocking ; injections) and filters statements to verify they start with the allowed operation prefix.
• Safety Row Limits: Automatically appends LIMIT 100 to SELECT queries lacking an explicit limit parameter to protect server memory and browser rendering layers.

4. Query History & Compliance Audit Trails

• User Query History: User-specific dashboard logging prompts, compiled SQL queries, operation results, row counts, and latency runtimes.
• Immutable Audit Logging: Fastify middleware that captures system activity—including successful/failed sign-in attempts, user details updates, validation errors, and query executions—recording details alongside client IP addresses.

---

🛠️ Technology Stack

• Frontend: React, Next.js (App Router), Vanilla CSS, Lucide React Icons.
• Backend: Fastify 5.x, TypeScript, Drizzle ORM, Drizzle Kit.
• Authentication: JWT token authorization (@fastify/jwt) and scrypt secure password hashing.
• Database: PostgreSQL (pg-core).
• AI Integrations: Local Ollama JSON API.

---

🚀 Getting Started

Prerequisites

• Node.js (v18+)
• pnpm (v8+)
• PostgreSQL instance running locally (or via Docker)
• Ollama running locally (llama3 model downloaded)

1. Environment Configurations

Create a .env file in apps/api/ mimicking the template below:

PORT=3001
HOST=0.0.0.0
NODE_ENV=development
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/rbac_assistant_internal
TARGET_DATABASE_URL=postgresql://postgres:postgres@localhost:5432/rbac_assistant_target
JWT_SECRET=your-super-secure-jwt-secret-key
OLLAMA_URL=http://localhost:11434
OLLAMA_MODEL=llama3
ADMIN_EMAIL=admin@acme.io
ADMIN_PASSWORD=demo1234

2. Running Locally

# Install dependencies from root
pnpm install

# Start database migrations and seeder (in apps/api)
pnpm --filter api run db:migrate
pnpm --filter api run db:seed

# Start development servers (both frontend and backend)
pnpm run dev

The web client will be available at http://localhost:3000 and the API service at http://localhost:3001.