Agent QueryGate

A security broker between AI agents and MySQL databases. Provides policy-based guardrails, audit logging, and a REST API with MCP wrapper so AI agents can query and modify MySQL safely.

Features

Policy-based access control -- default-deny with per-table, per-column, and per-operation rules
Audit logging -- every query logged with before/after data diffs, optional agent-supplied reasoning
Audit review system -- AI or human reviewers can flag audit entries with severity levels and notes
Agent roles -- executor agents run queries, auditor agents review audit trails
MCP integration -- Model Context Protocol server for Claude and other AI agents
REST API -- standard HTTP endpoints any agent or client can use
Admin dashboard -- React SPA with dark theme for managing users, databases, agents, and policies
Multi-tenant user management -- superadmin / admin / user role hierarchy
Database credential encryption -- AES-256-GCM encryption for stored credentials
SQL injection prevention -- AST-based SQL parsing with blocked keyword detection
Row limit enforcement -- policies can cap how many rows a write operation affects
WHERE clause requirements -- force agents to include WHERE on UPDATE/DELETE
Value validation -- per-column rules on write values (enum, regex pattern, min/max, not-null), checked before and after execution

Quick Start

Prerequisites

Node.js 20+
MySQL 8.0+

Setup

# 1. Clone and install dependencies
git clone https://github.com/ekoindia/agent-querygate.git
cd agent-querygate
npm install && cd frontend && npm install && cd ..

# 2. Configure environment
cp .env.example .env
# Edit .env -- set JWT_SECRET and ENCRYPTION_KEY to secure random values

# 3. Create the admin database
mysql -u root -e "CREATE DATABASE querygate_admin"

# 4. Run migrations
npm run db:migrate

# 5. Start the backend API (port 3000) -- serves REST API; in dev the UI comes from Vite
npm run dev

# 6. In a separate terminal, start the Vite frontend dev server (port 5173)
#    HMR + proxies /api and /admin/api calls to the backend on 3000
npm run dev:frontend

Open http://localhost:5173/setup to create the initial superadmin account.

In development use 5173, not 3000. Port 3000 serves the last vite build output, which is stale (or empty) while you are working on the frontend. See Running the App for the full dev-vs-production model.

Running the App

The backend and frontend are separate processes. Which ones you run depends on whether you are developing or deploying.

Development

Run both, in two terminals:

# Terminal 1 -- backend API (run db:migrate once first)
npm run db:migrate   # only when migrations are pending
npm run dev          # Hono API on :3000, hot reload

# Terminal 2 -- frontend dev server
npm run dev:frontend # Vite on :5173, HMR, proxies API -> :3000

Open http://localhost:5173.

The Vite dev server (5173) serves the UI with hot module reload and forwards every /api and /admin/api request to the backend on 3000. Port 3000 also serves a UI, but only the last vite build output -- always use 5173 while developing the frontend.

Production

No Vite. Build once, then run a single server:

npm run build   # compiles backend to dist/ and builds the SPA into frontend/dist/
npm run start   # one server on :3000 serving the REST API AND the built SPA

Open http://localhost:3000. The Vite dev server (5173) is not used in production.

Port reference

Port	Process	When	Serves
3000	Backend (`npm run dev` / `npm run start`)	dev + prod	REST API; built SPA from `frontend/dist`
5173	Vite (`npm run dev:frontend`)	dev only	SPA with HMR; proxies `/api`, `/admin/api` -> 3000

Architecture

graph TD

    subgraph Executor Agents
        EA[Executor Agents<br><i>Claude, custom</i>]
        EM[MCP Clients<br><i>Claude Desktop</i>]
    end

    subgraph Auditor Agents
        AA[Auditor Agents<br><i>Claude, custom</i>]
        AM[MCP Clients<br><i>Claude Desktop</i>]
    end

    subgraph Hono Server
        SP[SQL Parser]
        PE[Policy Engine<br><i>table/column/op access<br>rules + validation</i>]
        AL[Audit Logger]
    end

    TDB[(Target MySQL<br>Databases)]
    ADB[(Admin DB<br>MySQL)]
    ADM[Admin Users<br>React SPA]

    EA -- "REST API<br>POST /query<br>POST /execute" --> SP
    EM -- "MCP/stdio<br>mysql_query<br>mysql_execute" --> SP

    AA -- "REST API<br>GET /audit/*<br>POST /reviews" --> AL
    AM -- "MCP/stdio<br>audit_*" --> AL

    SP --> PE
    PE --> AL
    AL -- "encrypted connections" --> TDB
    AL --> ADB
    ADM -- "JWT auth" --> SP

%% ASCII version (visible only in raw markdown):
%%                                   +------------------+
%%                                   |   Target MySQL   |
%%                                   |   Databases      |
%%                                   +--------+---------+
%%                                            ^
%%                                            | encrypted connections
%%                                            |
%% +---------------+    REST API     +--------+---------+     +-----------+
%% |  Executor     +---------------->|   Hono Server    +---->| Admin DB  |
%% |  Agents       |  POST /query    |                  |     | (MySQL)   |
%% |  (Claude,     |  POST /execute  |  +------------+  |     +-----------+
%% |   custom)     |                 |  | SQL Parser |  |
%% +---------------+    MCP/stdio    |  +------------+  |
%% |  MCP Clients  +---------------->|  | Policy     |  |
%% |  (Claude      |  mysql_query    |  | Engine     |  |
%% |   Desktop)    |  mysql_execute  |  +------------+  |
%% +---------------+                 |  | Audit      |  |
%%                                   |  | Logger     |  |
%% +---------------+    REST API     |  +------------+  |
%% |  Auditor      +---------------->|                  |
%% |  Agents       |  GET /audit/*   |                  |
%% |  (Claude,     |  POST /reviews  |                  |
%% |   custom)     |                 |                  |
%% +---------------+    MCP/stdio    |                  |
%% |  MCP Clients  +---------------->|                  |
%% |  (Claude      |  audit_*        +--------+---------+
%% |   Desktop)    |                          ^
%% +---------------+                          | JWT auth
%%                                            |
%%                                   +--------+---------+
%%                                   |   Admin Users    |
%%                                   |   (React SPA)    |
%%                                   +------------------+

Tech Stack

Layer	Technology
Backend	Hono, Drizzle ORM, mysql2, node-sql-parser, Zod, JWT
Frontend	React 19, Vite 8, shadcn/ui, Tailwind CSS v4
Auth	bcryptjs (passwords), JWT (sessions), API keys
MCP	@modelcontextprotocol/sdk (stdio transport)
Crypto	Node crypto (AES-256-GCM)
Testing	Vitest

Project Structure

agent-querygate/
+-- src/
|   +-- index.ts                 # Hono server entry point
|   +-- config.ts                # Env config with Zod validation
|   +-- auth/
|   |   +-- api-key.ts           # API key generation and verification
|   |   +-- jwt.ts               # JWT token management
|   |   +-- middleware.ts         # Auth middleware (admin + agent)
|   |   +-- password.ts          # bcrypt password hashing
|   +-- db/
|   |   +-- schema.ts            # Drizzle ORM schema (users, agents, policies, audit)
|   |   +-- connection.ts        # Database connection factory
|   |   +-- migrate.ts           # Migration runner
|   +-- policy/
|   |   +-- engine.ts            # Policy evaluation (default-deny)
|   |   +-- sql-parser.ts        # AST-based SQL parsing
|   |   +-- blocked-keywords.ts  # Dangerous keyword detection
|   +-- query/
|   |   +-- executor.ts          # Read/write query execution
|   |   +-- pool-manager.ts      # Connection pool per target DB
|   |   +-- snapshot.ts          # Before/after data snapshots
|   +-- audit/
|   |   +-- logger.ts            # Audit log writer
|   +-- mcp/
|   |   +-- server.ts            # MCP server wrapping REST API
|   +-- routes/
|   |   +-- agent/               # Agent-facing endpoints (executor + auditor)
|   |   +-- admin/               # Admin dashboard endpoints
|   +-- lib/
|       +-- crypto.ts            # AES-256-GCM encrypt/decrypt
|       +-- errors.ts            # Typed error classes
|       +-- types.ts             # Shared type definitions
+-- frontend/                    # React SPA (shadcn/ui, dark theme)
+-- drizzle/                     # Generated migration SQL files
+-- tests/                       # Vitest unit tests
+-- drizzle.config.ts            # Drizzle Kit config
+-- vitest.config.ts             # Vitest config

API Overview

Agent API

All agent endpoints require an X-API-Key header.

Executor agents (query/mutate data):

Method	Endpoint	Description
POST	`/api/v1/query`	Execute a read-only SELECT query
POST	`/api/v1/execute`	Execute a write (INSERT/UPDATE/DELETE)
GET	`/api/v1/tables`	List tables the agent can access
GET	`/api/v1/tables/:name/schema`	Describe a table's columns
GET	`/api/v1/health`	Agent connection health check

Auditor agents (review audit trails):

Method	Endpoint	Description
GET	`/api/v1/audit/logs`	Search and list audit logs
GET	`/api/v1/audit/logs/:id`	Get audit log detail with reviews
POST	`/api/v1/audit/reviews`	Flag an audit log entry
GET	`/api/v1/audit/reviews`	List reviews created by this auditor

Admin API

All admin endpoints require a JWT Authorization: Bearer <token> header (except auth).

Method	Endpoint	Description
POST	`/admin/api/auth/setup`	Create initial superadmin
GET	`/admin/api/auth/setup-status`	Check whether setup is complete
POST	`/admin/api/auth/login`	Authenticate and receive JWT
POST	`/admin/api/auth/logout`	Invalidate the current session
GET	`/admin/api/auth/me`	Get the current authenticated user
GET	`/admin/api/users`	List users
POST	`/admin/api/users`	Create user (admin+ only)
PUT	`/admin/api/users/:id`	Update a user
DELETE	`/admin/api/users/:id`	Delete a user
GET	`/admin/api/databases`	List registered databases
POST	`/admin/api/databases`	Register a target database
GET	`/admin/api/databases/:id`	Get a single registered database
PUT	`/admin/api/databases/:id`	Update a registered database
DELETE	`/admin/api/databases/:id`	Delete a registered database
POST	`/admin/api/databases/:id/test-connection`	Test connectivity to a target DB
GET	`/admin/api/databases/:id/introspect`	Introspect a target DB's tables/columns
GET	`/admin/api/agents`	List agents (with assigned databases)
POST	`/admin/api/agents`	Create agent with role (returns API key)
GET	`/admin/api/agents/:id`	Get a single agent
PUT	`/admin/api/agents/:id`	Update an agent
DELETE	`/admin/api/agents/:id`	Delete an agent
POST	`/admin/api/agents/:id/regenerate-key`	Regenerate an agent's API key
GET	`/admin/api/agents/:id/databases`	List databases an agent can access
PUT	`/admin/api/agents/:id/databases`	Replace an agent's full database-access set (bulk)
POST	`/admin/api/agents/:id/databases/:dbId`	Grant an agent access to a database
DELETE	`/admin/api/agents/:id/databases/:dbId`	Revoke an agent's database access
GET	`/admin/api/agents/:agentId/databases/:dbId/policies`	List policies for an agent on a database
POST	`/admin/api/agents/:agentId/databases/:dbId/policies`	Create an access policy
PUT	`/admin/api/policies/:id`	Update an access policy
DELETE	`/admin/api/policies/:id`	Delete an access policy
GET	`/admin/api/audit`	Query audit logs
GET	`/admin/api/audit/export`	Export audit logs
GET	`/admin/api/audit/:id`	Get an audit log entry's detail
GET	`/admin/api/audit/:id/reviews`	List reviews for an audit log
POST	`/admin/api/audit/:id/reviews`	Create a review on an audit log
GET	`/admin/api/dashboard/stats`	Dashboard statistics

MCP Tools

The MCP server wraps the REST API for use with Claude Desktop and other MCP-compatible clients. It connects via stdio transport. Tools registered depend on the agent role.

Executor tools (default):

Tool	Description
`mysql_query`	Execute a read-only SQL SELECT query
`mysql_execute`	Execute a write SQL statement (INSERT/UPDATE/DELETE)
`mysql_list_tables`	List all tables the agent has access to
`mysql_describe_table`	Get column schema for a specific table
`mysql_health`	Check agent connection health status

Auditor tools (when AQG_AGENT_ROLE=auditor):

Tool	Description
`audit_list_logs`	Search and list audit logs with filters
`audit_get_log`	Get a single audit log with its reviews
`audit_create_review`	Flag an audit log entry with severity and notes
`audit_list_reviews`	List reviews created by this auditor
`mysql_health`	Check agent connection health status

Claude Desktop Configuration

Add to your claude_desktop_config.json:

{
	"mcpServers": {
		"agent-querygate": {
			"command": "node",
			"args": ["path/to/agent-querygate/dist/mcp/server.js"],
			"env": {
				"AQG_BASE_URL": "http://localhost:3000",
				"AQG_API_KEY": "your-agent-api-key",
				"AQG_AGENT_ROLE": "executor"
			}
		}
	}
}

Set AQG_AGENT_ROLE to auditor for an auditor agent's MCP server.

Documentation

Guide	Description
Getting Started	First-time setup walkthrough
API Reference	Complete endpoint documentation
MCP Integration	Connecting AI agents via MCP
Admin Guide	Managing users, databases, and policies
Security	Encryption, auth, and threat model
Architecture	System design and data flow
Development	Contributing and local development

Environment Variables

Variable	Description	Default
`ADMIN_DB_HOST`	Admin database host	`localhost`
`ADMIN_DB_PORT`	Admin database port	`3306`
`ADMIN_DB_NAME`	Admin database name	`querygate_admin`
`ADMIN_DB_USER`	Admin database user	`root`
`ADMIN_DB_PASSWORD`	Admin database password	(empty)
`JWT_SECRET`	Secret for signing JWTs (min 16 chars)	required
`ENCRYPTION_KEY`	Secret string (min 32 chars) for DB-password encryption; SHA-256-derived to a 32-byte AES-256 key	required
`PORT`	Server listen port	`3000`
`NODE_ENV`	Environment mode	`development`
`ALLOWED_ORIGINS`	Comma-separated CORS origins (pin in prod)	`http://localhost:5173`

Scripts

Script	Command	Description
`npm run dev`	`tsx watch --env-file=.env src/index.ts`	Start backend in dev mode with hot reload
`npm run dev:frontend`	`cd frontend && npm run dev`	Start frontend dev server (Vite)
`npm run build`	`tsc + vite build`	Build backend and frontend for production
`npm run start`	`node --env-file=.env dist/index.js`	Run production build
`npm run test`	`vitest run`	Run test suite
`npm run test:watch`	`vitest`	Run tests in watch mode
`npm run db:generate`	`drizzle-kit generate`	Generate migration files from schema
`npm run db:migrate`	`tsx --env-file=.env src/db/migrate.ts`	Apply pending migrations
`npm run db:studio`	`drizzle-kit studio`	Open Drizzle Studio GUI

Backend scripts load .env via Node's native --env-file flag (no dotenv dependency). The frontend (dev:frontend) needs no .env: Vite loads its own frontend/.env* files and only ever sees VITE_-prefixed values, never backend secrets.

License

Released under the MIT License.

Security

To report a vulnerability, see SECURITY.md. Do not open public issues for security reports.

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
deploy		deploy
docs		docs
drizzle/migrations		drizzle/migrations
frontend		frontend
src		src
tests		tests
.dockerignore		.dockerignore
.env.docker.example		.env.docker.example
.env.example		.env.example
.gitignore		.gitignore
Dockerfile		Dockerfile
LICENSE		LICENSE
README.md		README.md
SECURITY.md		SECURITY.md
docker-compose.yml		docker-compose.yml
docker-entrypoint.sh		docker-entrypoint.sh
drizzle.config.ts		drizzle.config.ts
package-lock.json		package-lock.json
package.json		package.json
tsconfig.json		tsconfig.json
tsconfig.node.json		tsconfig.node.json
vitest.config.ts		vitest.config.ts

Navigation Menu

Repository navigation

About

Repository files navigation

Agent QueryGate

Features

Quick Start

Prerequisites

Setup

Running the App

Development

Production

Port reference

Architecture

Tech Stack

Project Structure

API Overview

Agent API

Admin API

MCP Tools

Claude Desktop Configuration

Documentation

Environment Variables

Scripts

License

Security

About

Releases

Packages

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

Agent QueryGate

Features

Quick Start

Prerequisites

Setup

Running the App

Development

Production

Port reference

Architecture

Tech Stack

Project Structure

API Overview

Agent API

Admin API

MCP Tools

Claude Desktop Configuration

Documentation

Environment Variables

Scripts

License

Security

About

Resources

Code of conduct

Contributing

Security policy

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages