Query Gate

Context & Problem

In enterprise environments, engineering, analytics, and support personnel frequently require direct SQL access to staging or production replicas to diagnose defects or run ad-hoc operational reports. Traditional approaches suffer from critical security and compliance deficiencies:

• Shared Master Credentials: Multiple engineers share high-privilege credentials, making individual accountability impossible in audit trails.
• Unchecked Data Exposure: Sensitive personal information (PII, credit card numbers, health data) is exposed in plain text within query results grids without regulatory masking.
• Audit Gaps on Crashes: Conventional post-execution audit loggers write records after a query finishes. If the query runner crashes, times out, or gets killed, no audit record is preserved.
• Accidental Mass Mutations: Unfiltered UPDATE or DELETE statements run directly against production without dry-run rollback checks or required approval workflows.

The Solution: Query Gate

I designed and engineered Query Gate, a high-performance web-based SQL gateway built on .NET 10 and Blazor Server that brokers governed database interactions across multiple relational engines: Microsoft SQL Server, PostgreSQL, MySQL, and MariaDB.

Key Architectural Pillars

1. Fail-Closed Auditing:

• Query Gate enforces a strict fail-closed security contract. Before any SQL statement touches a target database, an immutable audit entry is written to persistent storage containing user identity, IP address, connection target, and sanitized SQL statement text.
• If writing the audit record fails for any reason (disk full, lock failure, network blip), the query execution is immediately aborted before dispatching to the target engine.

2. Dynamic In-Flight Data Masking:

• Designed a streaming redaction engine supporting major compliance frameworks: GDPR, HIPAA, PCI-DSS, SOC 2, and CCPA.
• Automatically redacts or hashes sensitive columns (Social Security numbers, credit card PANs, patient names, credentials, email addresses) based on granular field metadata and regular expression rules before transmitting rows to the browser grid.

3. Multi-Dialect SQL Gate & Statement Analysis:

• Implemented an engine-specific AST parser that inspects queries prior to execution.
• Blocks dangerous multi-database switching (USE database), cross-server references, and privilege escalation DDL.
• Detects unfiltered UPDATE or DELETE statements and forces user confirmation or approval routing.

4. Estimated Execution Plan Parser:

• Integrates native engine plan compilation (SET SHOWPLAN_XML ON in SQL Server, EXPLAIN (FORMAT JSON) in PostgreSQL, EXPLAIN FORMAT=JSON in MySQL).
• Visualizes operator cost breakdowns, highlights expensive table scans, and flags missing indexes to guide developers toward optimal query patterns.

5. Integrated Monaco Query Workspace:

• Embeds Monaco code editor with live database schema intellisense, multi-tab query workspaces, dry-run transactional rollback support, and multi-format exports (Excel, CSV, JSON, Markdown).

Open-Source Architecture & Agent Skills

Query Gate was built with automated development workflows in mind. It maintains all architectural guidelines and operational runbooks centrally in .github/skills/ following the Agent Skills specification, allowing cross-platform coding agents (Google Antigravity, Claude Code, Cursor, Windsurf) to navigate and maintain the codebase consistently.