Architecture

Architecture

High-level overview

flowchart TB
    Client["AI client / Admin browser"]

    subgraph Instances["hs-sql-agent instances"]
        API["ASP.NET Core REST + MCP"]
        IP["IP rate-limit middleware"]
        Auth["MCP key authentication"]
        Key["MCP-key rate-limit middleware"]
        Tools["SqlAgentTool / CustomToolProxy"]
        Policy["Security policy runtime state"]
        SQLLimit["SQL concurrency limiter"]
        API --> IP --> Auth --> Key --> Tools
        Policy --> Key
        Policy --> SQLLimit --> Tools
    end

    AdminDB[("Admin/Auth DB\nSQLite or PostgreSQL")]
    Redis[("Redis\noptional distributed state")]
    UserDB[("User database")]

    Client --> API
    API --> AdminDB
    Tools --> UserDB
    IP <--> Redis
    Key <--> Redis
    Policy <--> Redis
    Policy <--> AdminDB
    SQLLimit <--> Redis

Loading

Backend modules

Module	Responsibility
Common	Shared interfaces, models, and cryptography
Infrastructure	Memory/Redis cache implementations
Admin.Service	MCP keys, audit, database definitions, semantic layer, custom tools, and security policy
Auth.Service	Members, roles, permissions, JWT, and token revocation
SqlAgent.Service	SQL parsing, validation, dialect strategies, and execution
HsSqlAgent.Server	DI registration, middleware, controllers, MCP tools, rate limits, policy sync, and concurrency limits
HsSqlAgent.PostgresMigrations	Provider-specific PostgreSQL Admin/Auth migrations
ToolBox	Executable entrypoint and configuration binding

Admin database providers

The same AdminContext and AuthContext entity models support two providers:

Provider	Intended topology	Migrations
SQLite	One application instance	Existing SQLite migration chains
PostgreSQL	Multiple replicas/shared state	Provider-specific Admin/Auth baselines

PostgreSQL separates the histories into __AdminMigrationsHistory and __AuthMigrationsHistory. The runtime migrates Auth before Admin.

The Admin database is not the user database queried by AI tools. User database support remains SQLite, PostgreSQL, MySQL, SQL Server, Oracle, and Firebird.

MCP request flow

Client request to /mcp
  -> IP rate limiter (pre-authentication)
  -> MCP access-key authentication
  -> MCP-key rate limiter
  -> request normalization
  -> MCP session/tool filtering
  -> SQL concurrency lease
  -> parse and validate SQL
  -> execute against the selected user database
  -> audit event

IP and key partitions share IRequestRateLimiter:

• MemoryRequestRateLimiter stores fixed-window counters in one process.
• RedisRequestRateLimiter executes an atomic Lua counter/expiry operation across replicas.

Changing a key policy changes the Redis counter identity, so a new permit/window policy is effective immediately rather than inheriting an incompatible old counter.

Security policy consistency

SecurityPolicySettings is authoritative in the Admin database. Each instance keeps a cloned in-memory runtime value for fast request-path reads.

When Redis sync is enabled:

1. The Admin update is committed to the database.
2. The local runtime state is updated.
3. The policy snapshot is stored in Redis and published.
4. Other instances apply newer messages.
5. Periodic Admin DB polling repairs missed notifications.

Messages and poll results only replace an older UpdatedAt value, preventing stale state from overwriting a newer policy.

SQL concurrency

The SQL concurrency limiter guards every built-in and custom SQL execution path.

• Memory leases count active SQL operations inside one process.
• Redis leases use a sorted set scored by expiry time and Redis server time.
• Each lease has a unique token and renews until disposed.
• Expired leases are removed atomically during acquisition, recovering capacity after an instance crash.

With Redis, MaxConcurrentSql is a cluster-wide maximum. With Memory, it is a per-instance maximum.

Technology stack

Area	Technology
Runtime	.NET 10 / ASP.NET Core
ORM	EF Core 10, SQLite, Npgsql PostgreSQL provider
Distributed coordination	StackExchange.Redis + Lua
MCP	ModelContextProtocol.AspNetCore
Authentication	JWT Bearer + BCrypt
Frontend	Nuxt 4 SPA, Vue 3, Tailwind CSS
Testing	xUnit v3, Vitest, Testcontainers

See Security Governance, Configuration, and Distributed Deployment.