Zentryx

Zentryx is a PostgreSQL security middleware and SOC-style dashboard. It sits between an application and PostgreSQL, inspects SQL before execution, blocks dangerous queries, records query risk context, and gives analysts a dashboard for reviewing alerts, query behavior, fingerprints, and query families.

The goal is to create an application-specific SQL baseline without model training. Developers can register approved SQL templates, Zentryx normalizes and fingerprints them, and unexpected query shapes are treated as new behavior for deeper policy and model analysis.

Gallery Dump

Overview

Alerts Workspace

Administration

What Zentryx Does

• Receives SQL from an application through the middleware API.
• Authenticates the middleware caller with an API key.
• Normalizes SQL by replacing literals with placeholders and standardizing the structure.
• Generates SHA256 fingerprints for exact approved query templates.
• Groups related SQL templates into query families.
• Applies deterministic security policy rules before database execution.
• Uses local heuristic scoring and optional OpenRouter LLM enrichment.
• Executes safe SQL through the guarded /middleware/execute endpoint.
• Blocks destructive, suspicious, or unauthorized SQL before it reaches PostgreSQL.
• Logs scores, alerts, fingerprints, family status, and execution decisions.
• Shows the results in a React + Ant Design SOC dashboard.

System Architecture

flowchart LR
    app["Application"] --> api["Zentryx FastAPI Middleware"]
    api --> auth["API Key Authentication"]
    auth --> normalize["SQL Normalization"]
    normalize --> fp["Exact Fingerprint SHA256"]
    normalize --> fam["Query Family Matching"]
    fp --> decision["Decision Engine"]
    fam --> decision
    decision --> rules["Deterministic Policy Rules"]
    decision --> local["Local Heuristic Score"]
    decision --> llm["OpenRouter Classification"]
    decision --> store{"Persistence Mode"}
    store -->|"known safe"| agg["Aggregate Family Stats"]
    store -->|"new or risky"| detail["Detailed Logs and Scores"]
    detail --> alerts["Alerts"]
    decision -->|"allow"| db["PostgreSQL"]
    decision -->|"block"| blocked["Blocked Response"]
    agg --> dashboard["Zentryx Dashboard"]
    alerts --> dashboard

Loading

Decision priority:

1. Middleware key and client policy checks
2. SQL normalization
3. Exact fingerprint lookup
4. Query family lookup
5. Deterministic policy rules
6. Local heuristic scoring
7. Optional OpenRouter classification
8. Execute or block

Main Features

• Guarded execution: Applications send SQL to /middleware/execute; Zentryx decides whether to run or block it.
• Exact fingerprints: Developers register approved SQL templates during onboarding.
• Query families: Similar predefined query shapes can be approved as a trusted family.
• New query detection: Unknown SQL templates are marked as new behavior and continue through normal policy and model analysis.
• Policy-first blocking: High-risk SQL such as stacked statements, destructive DDL, privilege changes, dangerous functions, and catalog access is handled by deterministic rules.
• LLM support: OpenRouter can enrich classification, but Zentryx still works with local heuristic fallback.
• SOC dashboard: Overview, alerts, query analytics, and administration screens for security review.
• Tenant-ready schema foundation: Metadata and query-family tables are structured for future multi-application or multi-tenant onboarding.

Project Structure

querysentinel/
  api_gateway/        FastAPI middleware security API
  dashboard-react/    React + Ant Design dashboard
  db/                 PostgreSQL/TimescaleDB init scripts and migrations
  docs/               Production refactor notes and screenshots
  tests/              Middleware, fingerprint, and query-family tests
  docker-compose.yml  Local full-stack runtime
  Makefile            Convenience commands
  package.json        React dashboard forwarding scripts

Internal folders and database names still use querysentinel for compatibility. The visible product name is Zentryx.

Quick Start (Local)

Use this when the repository is published or renamed under your GitHub account:

git clone https://github.com/Pickachu19/Zentryx.git
cd Zentryx

Prerequisites

• Git
• Docker Desktop
• Docker Compose
• Node.js 18 or newer, only needed for standalone React dashboard development
• Python 3.12, only needed for running tests outside Docker
• WSL Ubuntu is recommended on Windows

Environment Setup

Create .env in the project root:

DB_PASSWORD=change_me_super_secure_db_pass
JWT_SECRET=change_me_super_secret_jwt_key
DEFAULT_ADMIN_USERNAME=admin
DEFAULT_ADMIN_PASSWORD=admin
OPENROUTER_API_KEY=sk-or-v1-your-openrouter-key
OPENROUTER_MODEL=google/gemini-2.5-flash-lite
OPENROUTER_THRESHOLD=0.72
OPENROUTER_BLOCK_THRESHOLD=0.85
OPENROUTER_FAIL_CLOSED=false
MIDDLEWARE_API_KEY=change_me_querysentinel_middleware_key
MIDDLEWARE_ALLOW_WRITE_THROUGH=false

OpenRouter is optional. If OPENROUTER_API_KEY is empty or unavailable, Zentryx continues with deterministic policy and local heuristic scoring.

Run The Full Project

Start PostgreSQL, the FastAPI middleware, and the dashboard:

docker compose up -d --build

Or use the Makefile shortcut:

make dev

Open:

• API health: http://localhost:8000/health
• API docs: http://localhost:8000/docs
• Dashboard: http://localhost:8501

Default dashboard login:

username: admin
password: admin

Run Only The React Dashboard

Use this mode when you only want to view or edit the UI with mock data:

npm run install:dashboard
npm run dev

Open:

http://localhost:3000

Login with:

admin / admin

Keep the Mock Engine toggle enabled if the API and database are not running.

Live Middleware Test

Send a normal query through Zentryx:

$body = @{
  db_user = "orders_app"
  application = "orders-api"
  client_ip = "10.0.0.25"
  query_text = "SELECT 1 AS sanity_check"
  mode = "enforce"
  readonly = $true
} | ConvertTo-Json

Invoke-RestMethod `
  -Method Post `
  -Uri "http://localhost:8000/middleware/execute" `
  -Headers @{"X-Zentryx-Key"="change_me_querysentinel_middleware_key"} `
  -ContentType "application/json" `
  -Body $body

Expected result:

execution_status = executed
decision.blocked = false

Send a malicious/destructive query:

$body = @{
  db_user = "orders_app"
  application = "orders-api"
  client_ip = "10.0.0.25"
  query_text = "DROP TABLE customers"
  mode = "enforce"
  readonly = $false
} | ConvertTo-Json

Invoke-RestMethod `
  -Method Post `
  -Uri "http://localhost:8000/middleware/execute" `
  -Headers @{"X-Zentryx-Key"="change_me_querysentinel_middleware_key"} `
  -ContentType "application/json" `
  -Body $body

Expected result:

execution_status = blocked
decision.blocked = true
decision.verdict = malicious

Refresh the dashboard to see the query log, score, and alert.

Backward-compatible middleware headers are still accepted:

X-Zentryx-Key
X-SentraQL-Key
X-QuerySentinel-Key

Use X-Zentryx-Key for new integrations.

Fingerprint Allowlist Workflow

Fingerprints are exact normalized query templates.

Example raw queries:

SELECT * FROM users WHERE id = 42;
SELECT * FROM users WHERE id = 'abc';

Normalized template:

SELECT * FROM USERS WHERE ID = ?

Zentryx generates a SHA256 hash from the normalized SQL.

Fingerprint statuses:

• known_query: approved exact fingerprint exists
• new_query: no approved exact fingerprint exists

Main table:

qs_meta.query_fingerprints

Main API endpoints:

POST   /fingerprints/preview
GET    /fingerprints/
POST   /fingerprints/
PATCH  /fingerprints/{fingerprint_id}/approve
DELETE /fingerprints/{fingerprint_id}

Query Family Workflow

Query families reduce onboarding friction for applications with many static query variants.

These queries should belong to the same family:

SELECT * FROM users WHERE id = 1;
SELECT name, email FROM users WHERE id = 2;

Family shape:

SELECT FROM USERS WHERE ID = ?

For SELECT queries, projection changes are ignored in the family hash. This helps ORM projection variants map to one approved baseline.

Family statuses:

• new_family: observed but not trusted
• known_family: approved and trusted
• unclassified: family could not be classified

Main tables:

qs_meta.query_families
qs.query_family_stats

Main API endpoints:

GET    /query-families/
PATCH  /query-families/{family_id}
PATCH  /query-families/{family_id}/approve
DELETE /query-families/{family_id}

Database Schemas

Transactional tables:

qs.query_logs
qs.anomaly_scores
qs.alerts
qs.query_family_stats

Metadata tables:

qs_meta.analyst_users
qs_meta.middleware_clients
qs_meta.middleware_key_events
qs_meta.query_policy_rules
qs_meta.query_fingerprints
qs_meta.query_families
qs_meta.system_settings

Important query log fields:

query_fingerprint_hash
fingerprint_status
fingerprint_id
query_family_hash
query_family_id
family_status
family_confidence
persistence_mode

Apply Migrations Manually

The main additive production foundation migration is:

db/migrations/001_query_families_production_foundation.sql

Run it manually with:

Get-Content -Raw db\migrations\001_query_families_production_foundation.sql | docker compose exec -T db psql -U qs_user -d querysentinel

Run Tests

From WSL:

cd /home/sumaiya/db_project/querysentinel
. .venv/bin/activate
python -m compileall -q api_gateway dashboard-react tests
pytest -q

Expected result:

11 passed

Tests cover:

• middleware guarded execution
• middleware key enforcement
• fingerprint normalization
• fingerprint matching
• query family grouping
• projection variants sharing a family
• trusted family aggregate mode

Dashboard Sections

• Overview: security posture, protected queries, blocked queries, alert summary, and system health.
• Alerts: triage workspace for critical, high, medium, and low security incidents.
• Query Analytics: query logs, scores, fingerprints, family status, and persistence mode.
• Administration: users, middleware clients, query baselines, fingerprints, families, and model settings.

Production Notes

Read the production refactor plan:

docs/zentryx_production_refactor.md

Completed foundation:

• exact fingerprints
• query families
• family approval workflow
• aggregate family statistics
• schema foundation for tenants
• dashboard baseline administration

Remaining production hardening:

• true detail-log skipping for known safe queries
• behavioral anomaly scoring
• API rate limiting
• full audit logging for admin actions
• stronger JWT/session hardening
• secret management abstraction
• optional Ollama provider
• optional Nvidia NIM provider
• Kubernetes manifests

Important Security Reminder

Before a real deployment:

• Change DB_PASSWORD.
• Change JWT_SECRET.
• Change DEFAULT_ADMIN_PASSWORD.
• Change MIDDLEWARE_API_KEY.
• Use per-application middleware client keys.
• Keep MIDDLEWARE_ALLOW_WRITE_THROUGH=false unless explicitly needed.
• Put the API and dashboard behind TLS.
• Do not expose PostgreSQL directly to the public internet.

Design Principle

Zentryx is not only an SQL firewall. It is a baseline-driven database security layer. Static application queries become an approved behavioral profile, while unexpected SQL continues through policy rules, heuristic scoring, and optional LLM classification.