Standardizing Agentic Workflows for Relational Data: Governing AWS Kiro with a Deterministic SQL Validator

AI coding agents are good at drafting SQL and bad at knowing what they are not allowed to do. Left ungoverned, an agent will join on a column that does not exist because it looks plausible, return a DELETE for a read request, copy a DROP TABLE out of an example, or SELECT * over a wide table and return columns nobody asked for.

In a regulated environment, any one of those is a failed control. This project is the control: a deterministic validator between the agent and the database, plus the Kiro specs and steering docs that teach the agent the rules in the first place.

You can explore the complete repository on GitHub: aws-kiro-agentic-database-workflow.

Before / after

Before — agent SQL goes straight to the database. The user prompts, Kiro generates SQL, it runs against PostgreSQL, and the user gets a result or a runtime error. Nothing checks whether the statement was safe, in scope, or even against a real schema.

After — the validator sits between the agent and the database. Kiro drafts SQL, the validator checks it against the schema and the guardrail rule set, failures go back to the agent with the violations named, and only passing SQL reaches a human reviewer. Approved SQL is then handed to the database — nothing here runs agent-generated SQL unattended.

flowchart LR
    U[User prompt] --> K[Kiro agent]
    K -->|drafts SQL| V[Validator: mode + schema + guardrails]
    S[Schema JSON] --> V
    V -->|FAIL + violations| K
    V -->|PASS| R[Human review]
    R -->|approved| D[(RDS PostgreSQL)]

The same validator code runs in two places: as a local CLI and as an AWS Lambda (sql-guardrail-validator) that loads the schema from S3. One rule set, two runtimes, no drift between what you test locally and what runs in the cloud.

The specs and guardrails the agent works under

The .kiro/ directory is the other half of the project — it is how the agent learns the rules before it writes anything:

  • .kiro/specs/agentic-db-workflow/requirements.md — requirements with acceptance criteria.
  • .kiro/specs/agentic-db-workflow/design.md — architecture, the validation pipeline, and the design decisions.
  • .kiro/specs/agentic-db-workflow/tasks.md — the implementation checklist, marked done.
  • .kiro/steering/sql-standards.md — SQL style rules the agent must follow.
  • .kiro/steering/guardrails.md — the guardrail contract: validate before showing SQL, never route around the validator.

The steering docs tell the agent what to do; the validator proves it did it. Neither replaces the human reviewer.

The guardrail rules themselves are deterministic and stdlib-only:

Rule What it does
Mode allowlist read = SELECT/WITH only; ddl = CREATE/ALTER only; write = INSERT/UPDATE/DELETE only
Single statement Stacked queries rejected
Comment hygiene Comments containing SQL keywords rejected (no smuggling)
Schema validation Every table and column must exist in the schema file
System catalogs information_schema / pg_catalog always blocked
Projection width SELECT * rejected unless explicitly allowed
Scoped writes DELETE / UPDATE require a WHERE clause, in every mode
DDL path Additive only (CREATE TABLE / INDEX, ADD COLUMN, RENAME); DROP always blocked

The single source of truth is schema/retail_schema.json — customers, orders, order_items, products, inventory — with the same schema as Postgres DDL plus seed rows in sql/demo_schema.sql.

Failure modes caught

These are not hypothetical. Every row is exercised by the demo script and by the unit tests, with a good/bad example file in the repo for each one:

Failure mode What the agent produced
Hallucinated join A JOIN on a column that does not exist (o.shipping_address)
Hallucinated table A query against a table that is not in the schema (shipments)
Unsafe DDL DROP TABLE presented as part of a read task
Unscoped DELETE DELETE FROM orders with no WHERE
Write as read An UPDATE returned for a read request
Catalog probe UNION against information_schema to enumerate tables
SELECT * over wide tables Unbounded projection
Stacked query A read followed by a DELETE in one submission
Comment smuggling Keywords hidden in a /* */ comment to slip past a naive filter
Destructive DDL ALTER TABLE ... DROP COLUMN (data loss)
Schema drift DDL referencing a table that does not exist, or adding a column that already exists

What these have in common: the first group is the agent guessing at the schema, the second is the agent producing a more powerful statement than the task asked for, and the rest are shape problems — too wide, too many, too sneaky. The validator treats all of them the same way: a deterministic check against the schema file and the rule set, with the violation named in the output.

CLI automation

The validator and the demo run entirely locally with Python 3.10+ and no dependencies — no AWS needed to try it:

# Run the tests (63 tests, stdlib unittest)
python3 -m unittest discover -s tests -v

# Validate one file
python3 src/guardrails/validate_sql.py sql/examples/good/low_stock.sql
python3 src/guardrails/validate_sql.py --mode ddl sql/examples/ddl/good_add_column.sql

# Run the end-to-end demo: validator report over all examples,
# plus an EXPLAIN dry-run if a database is reachable
./scripts/run_workflow.sh

run_workflow.sh prints a pass/fail report (18 checks: 3 good, 10 bad, 3 DDL, 2 write-mode) and exits non-zero if any expectation fails.

Deploy to AWS

Terraform provisions a private-only VPC (10.0.0.0/16, two AZs, no internet gateway), one RDS PostgreSQL 15 instance (db.t3.micro, single-AZ, 20 GB gp3, storage-encrypted, private to the VPC), a versioned, encrypted S3 artifacts bucket holding the schema JSON, and a Python 3.12 Lambda running the same validator code as the CLI, with a least-privilege IAM role (CloudWatch logs + read access to the schema object only — no database access).

cd terraform
terraform init
terraform apply -var="db_password=<redacted>"

A passing query invoked on the deployed Lambda returns HTTP 200 with valid: true; a failing one returns 400 with the violation list. Cleanup is terraform destroy.

What the validator does not catch

Honest scope matters here. The validator proves SQL is safe to run, not that it is right:

  • A query that is valid SQL against real columns but answers the wrong question (wrong join, wrong filter, wrong aggregation).
  • A query that is valid but slow (no EXPLAIN cost check in the validator; the demo's EXPLAIN dry-run is a human eyeball check).
  • Data-dependent problems (a WHERE clause that matches zero rows, or every row).
  • CTE and subquery output columns, which cannot be inferred statically.

These are human-review problems, and the workflow keeps a human in the loop for exactly that reason.

Why this matters for enterprise data teams

This is the same boundary I drew in the agentic compliance pipeline: separate what must be deterministic from what can be probabilistic. The agent is free to be creative about how to answer a question; it is not free to decide what it is allowed to touch. The specs teach it the rules, the validator enforces them, and a human still signs off before anything runs. That is how agentic coding tools become governable in a structured enterprise environment — not by trusting the model more, but by checking it deterministically.