Impact appears at runtime
Blast radius, silent no-ops, referential failures, and partial state may not be knowable from text alone.
Engineering case study · SQL deployment safety
ProdGuard SQL
A pre-deployment review system for supported T-SQL that combines deterministic analysis, isolated SQL Server execution, contract verification, and one bounded agent review—while keeping a human as the release gate.
1.0000critical-defect F1 on the frozen 12-case corpus
9 / 12cases backed by real SQL Server execution evidence
36 / 36valid structured ProdGuard outputs across three runs
0.0000safe-case false-positive rate in this evaluation
These are experiment results on a small synthetic corpus—not a claim of production accuracy or generalization.
The problem
Reading SQL is not the same as observing its consequences.
A script can look plausible while still changing too many rows, doing nothing, leaving a partial commit, or producing different state on a second run. Those failures depend on intent and database state.
ProdGuard treats review as an evidence problem. The SQL text alone cannot establish whether a full-table change is authorized, whether a lookup will match, or whether a deployment is safe to rerun. The reviewer needs declared intent, structural signals, observed execution, and a clear boundary on what the tool cannot assess.
“Unbounded” is not always wrong
An authorized full-table operation can be correct; a heuristic must not silently replace the deployment contract.
Evidence needs a repeatable shape
Structured inputs, fixed dispositions, and Markdown/JSON reports make each decision inspectable rather than prompt-dependent.
System constraints
The safety boundary shaped the architecture.
The implemented MVP is intentionally narrow. Unknowns are surfaced for human review instead of treated as approval.
Real SQL Server behavior matters
Supported scripts run in an isolated SQL Server container because mocked execution would not prove actual engine behavior.
Intent must be declared
The deployment contract defines expected effects, authorized operations, atomicity, and rerun behavior.
The parser supports a subset
Unrecognized syntax becomes an explicit unsupported node or parse failure. It is never silently dropped and cannot receive PASS.
Execution requires authorization
Without the explicit gate, the runtime returns authorization required and starts no SQL Server container.
Agent output is advisory
The model reviews already-collected evidence. Invalid, truncated, or unsupported output cannot authorize PASS.
No production connection
The evaluated path uses synthetic data, no host mounts or published ports, disabled container networking, and cleanup checks.
Architecture
Build the evidence first. Ask the model last.
The system narrows the agent’s task by producing structured, higher-authority evidence before model review.
-
1 · Inputs
SQL + contract
Change text, authorized scope, expected effects, atomicity, and rerun intent.
-
2 · Parser
Recursive structure
Decomposed identifiers and nested DML; unknown constructs remain explicit.
-
3 · Rules
Deterministic signals
Structural and contract checks produce warnings—not unqualified verdicts.
-
4 · Sandbox
Execute twice
Capture pre-state, first-run impact, postconditions, and rerun behavior.
-
5 · Review
Verifier + one agent
Observed state is checked against the contract; the agent reviews the evidence bundle.
-
6 · Decision
Disposition + report
PASS, WARN, BLOCK, or SANDBOX_ERROR with JSON and Markdown evidence.
Fail closed: unsupported syntax, invalid agent output, or missing required execution evidence never becomes PASS.
Bounded agent: one model call reviews supplied evidence; it does not execute SQL or redefine deployment intent.
Human gate: a person authorizes sandbox execution and remains responsible for the deployment decision.
Engineering decisions
Five choices that define the system.
Each decision trades broader automation for clearer authority, reproducibility, or safer failure.
Execute supported changes twice in disposable SQL Server
- Decision
- Observe first-run effects and second-run state in an isolated, pinned container.
- Why
- Blast radius, lookup misses, partial commits, and idempotency are state-dependent.
- Alternative considered
- Static analysis or mocked database behavior only.
- Trade-off
- Stronger behavioral evidence costs container startup time, runtime complexity, and an authorization gate.
Make the deployment contract authoritative for intent
- Decision
- Compare observed behavior to declared scope and expected effects.
- Why
- A heuristic cannot know whether a broad operation is deliberate.
- Alternative considered
- Infer intent from SQL text or from the model response.
- Trade-off
- Review becomes more defensible, but incomplete contracts produce qualified review instead of easy approval.
Use deterministic tools before one bounded agent
- Decision
- Parse, check, execute, diff, and verify before asking the model to review the evidence.
- Why
- The model receives a smaller task and cannot outrank contract or observed state.
- Alternative considered
- End-to-end prompting or multiple collaborating agents.
- Trade-off
- The approach is easier to audit but depends on explicit rule and parser coverage.
Fail closed on uncertainty
- Decision
- Unsupported syntax, missing required evidence, and invalid model output cannot produce PASS.
- Why
- Silently accepting an unknown is unsafe at a deployment gate.
- Alternative considered
- Best-effort approval, automatic repair, or re-prompting invalid output.
- Trade-off
- More WARN and SANDBOX_ERROR results require human effort, but uncertainty stays visible.
Freeze the evaluation before detector work
- Decision
- Lock cases, contracts, fixtures, labels, prompts, schema, and model settings before comparing systems.
- Why
- Changing the corpus after seeing results would weaken the benchmark.
- Alternative considered
- Iteratively edit cases alongside implementation.
- Trade-off
- Evaluation integrity improves, while the deliberately small corpus remains a visible limitation.
Safety model
A disposition communicates both risk and evidence quality.
PASS is the narrowest outcome. Uncertainty, unsupported coverage, and infrastructure failure remain explicit.
✓ PASS
No blocking violation; required checks completed and observed behavior is consistent with the contract.
! WARN
Human review required: unsupported syntax, incomplete evidence or context, or invalid/unconfirmed agent output.
× BLOCK
A critical contract, integrity, state, atomicity, scope, or idempotency violation is confirmed.
◇ SANDBOX_ERROR
Required execution evidence could not be produced. Infrastructure failure is never translated into PASS.
Human approval is a control, not a footer note.
The gate authorizes one disposable sandbox run. It is not a production credential and does not delegate deployment authority to the model.
Evaluation
A measured improvement on one frozen experiment.
The repository compares a direct-prompt baseline with ProdGuard using the same 12 T-SQL cases, deployment contracts, output schema, model settings, and three runs per system.
| Metric | Baseline | ProdGuard | What it measures |
|---|---|---|---|
| Critical-defect F1 | 0.6833 | 1.0000 | Balance of critical-defect precision and recall |
| Critical precision | 0.8095 | 1.0000 | How often flagged critical defects matched labels |
| Critical recall | 0.5926 | 1.0000 | How many labeled critical defects were found |
| Disposition accuracy | 0.4445 | 1.0000 | Final PASS/WARN/BLOCK classification against labels |
| Safe-case false-positive rate | 0.4445 | 0.0000 | Safe cases incorrectly treated as defective |
| Valid structured outputs | 20 / 36 | 36 / 36 | Responses conforming to the locked result schema |
| Invalid outputs | 16 / 36 | 0 / 36 | Unparseable or truncated responses, scored fail-closed |
12 frozen casesDangerous, compositional, and safe false-positive traps with contracts and synthetic fixtures.
3 runs per system72 logical model calls total; one call per system, case, and run.
9 SQL-backed casesNine cases use real SQL Server evidence; three remain static-only by design.
Interpretation: the result shows that this evidence-led architecture outperformed the locked baseline on this corpus.
It does not establish 100% real-world accuracy, production readiness, or generalization to arbitrary T-SQL. The label review was
performed by an independent model rather than a human domain expert.
Testing and reproduction
Replay the evidence—or regenerate it under a new authorization.
The repository separates fast artifact verification from a full, stateful SQL and model run.
REPLAY
Reads committed results, recomputes metrics, and verifies artifact arithmetic and integrity. It needs no Docker, API access, or human execution approval.
FULL
Regenerates SQL evidence on disposable SQL Server and repeats 72 model calls. It requires Docker, provider access, and explicit human authorization.
- Frozen-input validation: 53 corpus payload files—cases, contracts, fixtures, labels, prompts, schemas, and manifest—are checked for byte identity.
- Authorization self-check: the runtime verifies that live SQL is refused without the gate and that no container is started.
- Sandbox orchestration tests: committed tests cover harness safety and the live execution workflow.
- Behavioral evidence: nine cases record pre-state, first-run changes, postconditions, second-run behavior, container metadata, cleanup, and zero-residue checks.
- Report artifacts: each of the 12 cases has inspectable Markdown and JSON output alongside aggregate baseline, ProdGuard, and comparison results.
- Fail-closed output handling: invalid or truncated model output is counted as invalid and cannot silently become approval.
Failure modes
What Can Go Wrong?
Safety depends as much on handling incomplete knowledge as it does on finding known defect patterns.
Unsupported or misparsed SQL
Dynamic SQL, stored procedure definitions, triggers, cross-database operations, and other unimplemented constructs exceed the MVP boundary.
Mitigation: explicit unsupported nodes or parse failures produce WARN and human review—never PASS.
Missing execution evidence
Container startup, readiness, SQL execution, or artifact collection can fail.
Mitigation: when execution is required but unavailable, the disposition is SANDBOX_ERROR and cleanup is checked.
Invalid or overconfident model response
A model can return truncated JSON, invent reasoning, or propose a verdict unsupported by higher-authority evidence.
Mitigation: schema validation, fail-closed handling, and evidence precedence prevent model output from authorizing an unsupported PASS.
Incomplete deployment contract
Missing scope or expected effects make it harder to distinguish authorized behavior from a defect.
Mitigation: ambiguous intent remains qualified for human review rather than guessed from the SQL.
False positive or false negative
The parser, rules, verifier, labels, fixtures, or model can still be wrong; a small synthetic corpus cannot bound real-world error.
Mitigation: human judgment remains required, artifacts stay inspectable, and broader expert-labeled evaluation is a prerequisite for stronger claims.
Model variation between full runs
The provider returned the requested model, but the evaluation did not have a dated model snapshot or deterministic seed.
Mitigation: lock request parameters, preserve raw outputs, and keep decisive authority in deterministic and observed evidence.
Limitations
What the project does not prove.
These boundaries are part of the design record, not fine print.
- No production-accuracy claim: a 12-case synthetic corpus is too small and too curated to represent arbitrary production T-SQL.
- Narrow language coverage: the MVP supports a defined subset around INSERT, UPDATE, DELETE, validation SELECTs, explicit transactions, simple predicates, and fixed schema constraints.
- Important constructs remain out of scope: stored procedure definitions, triggers, dynamic SQL, linked servers, CLR, SQL Agent jobs, external commands, bulk operations, cross-database references, and MERGE without explicit implementation and tests.
- Execution coverage is partial: nine of twelve evaluation cases have real SQL Server evidence; three are static-only.
- Labels need stronger independent review: the current label audit used an independent model, not a human SQL Server domain expert.
- Model reproducibility is bounded: request parameters are documented, but there was no dated provider snapshot or fixed seed, so model text may vary.
- Human judgment is still required: the tool supports a DBA or technical lead; it does not replace deployment approval, change control, or operational context.
- More validation precedes production use: broader expert-labeled corpora, adversarial and dialect coverage, operational hardening, security review, and deployment-environment validation would be necessary.
- Automatic correction is out of scope: ProdGuard reports evidence and disposition; generating a corrected deployment script is not part of the evaluated system.
Lessons
What I Learned
The most valuable work was deciding what each component is allowed to know—and what it is allowed to decide.
Evidence outranks eloquence
A concise state diff tied to a contract is more useful at a deployment gate than a persuasive but ungrounded explanation.
Intent deserves its own artifact
Separating declared intent from inferred intent prevents a heuristic or model from inventing the success criteria.
Uncertainty needs a product state
WARN and SANDBOX_ERROR make incomplete coverage visible. Collapsing them into pass/fail would hide important operational truth.
Reproduction has two meanings
Fast artifact replay and full evidence regeneration answer different questions and should have different prerequisites.
Small honest benchmarks beat inflated claims
Freezing the corpus and documenting its weakness preserves learning value without pretending the result generalizes.
Human approval belongs in the architecture
If a person must authorize a consequential action, the system should enforce and test that boundary rather than rely on process memory.
Evidence
Inspect the implementation and the receipts.
All project claims on this page trace to the public repository at audited commit 060db7f… or the linked demonstration.
Source and architecture
Implementation, safety model, specification, and provenance for the parser-to-report pipeline.
Frozen inputs and results
Locked methodology, label review, comparison metrics, and the 12 per-case evidence reports.
Runtime and tests
Replay versus full-run instructions, preflight controls, and committed orchestration tests.
Recorded walkthrough
A 4:59 demonstration linked from the project documentation. No verified screenshot set or live public demo was found.
Evidence scope: the ProdGuard source is not present in this portfolio workspace. This case study was audited against the linked public repository; no customer deployment, production usage, certification, or independent human validation is claimed.