Task 4 report — SQLGlot executable vertical slice

Status

Implemented the deployment-bound SQL vertical slice for PostgreSQL and MySQL. Logical DataFlow release remains a semantic, binding-free reference. Physical dataset bindings are consumed only by BoundSqlPlanService, which selects a registered concrete compiler and persists the resulting plan as compiled. The Runner still requires the exact bound plan and RuleVersion to be published; full test-evidence publication lifecycle hardening remains Task 7.

RED / GREEN evidence

Initial RED command:

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py \
tests/runner/test_rule_sql.py

Initial RED result:

18 failed in 1.38s

Representative expected failures:

ModuleNotFoundError: No module named 'app.core.data_rules.compilers'
ModuleNotFoundError: No module named 'app.runner.rule_sql'

Additional RED checks were added before each hardening fix:

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py::test_repository_persists_bound_plan_only_for_one_deployment_linkage
1 failed in 1.05s
AttributeError: 'DataRuleRepository' object has no attribute 'persist_bound_component_plan'

PYTHONPATH=. .venv/bin/pytest -q \
tests/runner/test_rule_sql.py::test_sqlglot_rule_adapter_rejects_unimplemented_idempotency_strategy \
tests/runner/test_rule_sql.py::test_rule_executor_matches_node_idempotency_to_persisted_component
2 failed in 1.09s

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py::test_repository_persists_bound_plan_only_for_one_deployment_linkage
1 failed in 0.88s
Failed: DID NOT RAISE ValueError

This proved a compiled plan was not yet checked against the physical source and target relations stored for the deployment.

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py::test_sql_compiler_fails_closed_for_unsupported_operations_and_binding_mismatch
1 failed in 0.86s
Failed: DID NOT RAISE ValueError

This proved assert.on_failure=quarantine was being accepted without a quarantine destination, so the compiler was tightened to support only reject.

PYTHONPATH=. .venv/bin/pytest -q \
tests/runner/test_rule_sql.py::test_sqlglot_rule_adapter_verifies_and_executes_one_transaction
1 failed in 0.98s

This proved affected-row counts were not a stable rows_out metric for idempotent upserts. The adapter now counts the compiled accepted-row query inside the same transaction before executing the write.

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py::test_sql_compiler_rejects_unproven_operator_type_semantics
5 failed in 0.90s

This proved text, fill, derive, map, and final output types required additional fail-closed compiler checks.

Focused GREEN after the implementation and hardening:

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py \
tests/runner/test_rule_sql.py \
tests/runner/test_rules.py
33 passed in 0.84s

Related release/repository/bootstrap regression GREEN:

PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_release.py \
tests/core/data_rules/test_data_rule_repository.py \
tests/runner/test_bootstrap.py \
tests/runner/test_rules.py \
tests/core/data_rules/test_execution_contracts.py
49 passed in 0.89s

Real PostgreSQL and MySQL integration

The source containers were already healthy on PostgreSQL port 25432 and MySQL port 23306. The integration test:

creates only task4_rule_source and task4_rule_target;
inserts one accepted and one rejected customer;
compiles and persists a bound plan as compiled;
performs a real dialect preflight and records its successful evidence before transitioning the in-test plan record to published;
executes through RulePlanExecutor;
verifies transformed target rows and exact rows_in, rows_out, and rows_rejected;
repeats the upsert to prove idempotency for both dialects;
mutates the stored plan body without changing its hash and verifies fail-closed execution;
drops only the two Task 4 tables in finally.

Direct dialect integration result:

TEST_DATABASE_URL=postgresql://dataops:dataops-test-password@127.0.0.1:15432/dataops \
PYTHONPATH=. .venv/bin/pytest -q \
tests/integration/test_data_rule_sql_execution.py
2 passed in 0.93s

Required vertical-slice command:

TEST_DATABASE_URL=postgresql://dataops:dataops-test-password@127.0.0.1:15432/dataops \
PYTHONPATH=. .venv/bin/pytest -q \
tests/core/data_rules/test_sql_compiler.py \
tests/runner/test_rule_sql.py \
tests/integration/test_data_rule_sql_execution.py
28 passed in 0.97s

Full verification

PYTHONPATH=. .venv/bin/pytest -q
477 passed, 26 skipped, 59 subtests passed in 4.16s

.venv/bin/ruff check \
app/core/data_rules/compilers \
app/core/data_rules/release.py \
app/core/data_rules/repository.py \
app/runner/rule_sql.py \
app/runner/rules.py \
app/runner/bootstrap.py \
tests/core/data_rules/test_sql_compiler.py \
tests/runner/test_rule_sql.py \
tests/runner/test_rules.py \
tests/integration/test_data_rule_sql_execution.py
All checks passed!

git diff --check produced no output.

Dependency and license

Added and installed sqlglot==30.13.0.
Installed package metadata reports License-Expression: MIT.
Installed package metadata reports Requires-Python: >=3.9.
The compiler provenance stored with bound plans is dataops-sqlglot-30.13.0.

Files

requirements.txt
app/core/data_rules/compilers/__init__.py
app/core/data_rules/compilers/base.py
app/core/data_rules/compilers/sql.py
app/core/data_rules/release.py
app/core/data_rules/repository.py
app/runner/rule_sql.py
app/runner/rules.py
app/runner/bootstrap.py
tests/core/data_rules/test_sql_compiler.py
tests/runner/test_rule_sql.py
tests/runner/test_rules.py
tests/integration/test_data_rule_sql_execution.py
.superpowers/sdd/task-4-report.md

Self-review

All user identifiers are converted to quoted SQLGlot Identifier nodes only after the closed identifier check. RuleSpec values become compiler-owned named placeholders and never enter SQL text.
The compiler validates the published RuleVersion hash, server-owned input and output snapshots, binding IDs, same datasource, relation kinds, access modes, dialect, concrete timezone/collation/rounding/regex capabilities, and final output field types.
The first executable operator slice covers cast, trim-only normalize_text, portable regex_replace, type-compatible fill_null, typed filter, typed derive, reject-only assert, deterministic deduplicate, and string map_values. Unsupported or semantically unprovable variants fail closed.
Bound-plan persistence re-parses the plan and proves its source relation, target relation, datasource UID, dialect, binding IDs, and schema hashes match one deployment owning the logical component.
Runner execution re-validates the closed plan and SQL AST, exact hash, RuleVersion ID, datasource dialect/capabilities, write authorization, and persisted component idempotency. Only AST-built upsert is implemented; the other declared orchestration strategies fail closed at this adapter.
Raw input count, accepted output count, and the AST-built upsert execute within one datasource transaction. Known commit failures return not_committed; commit ambiguity returns unknown.
The incorrect quality_check -> SQL write adapter alias was removed. SqlGlotQualityPlanAdapter explicitly fails closed until a read-only quality plan contract is implemented.

Concerns / deferred work

Production publication and revocation of compiled bound plans, including durable test evidence and approval transitions, remain Task 7. This task deliberately persists only compiled and does not claim tested/published production state.
Only same-datasource table/view-to-table SQL pushdown is supported. Cross source, lookup joins, aggregate, mask, lower/upper Unicode normalization, quarantine writes, and non-append output bindings fail closed.
partition_replace and deduplication_key remain valid orchestration contract values but are not executable by this SQL adapter; only an exact persisted upsert binding is accepted.
The datasource definition must carry server-owned sql_rule_capabilities. Existing deployments without those concrete capabilities will fail closed until Task 9 binds and validates them.
The explicit quality adapter is intentionally non-executable. A separate read-only quality plan and evidence contract is needed before quality nodes can run.

Task 4 review-fix pass — canonical provenance and publication safety

Date: 2026-07-23

Outcome

All critical, important, and identifier-limit review findings were corrected. The bound SQL compiler now accepts only canonical identifiers at its public service boundary. RuleVersion, schema snapshots, deployment bindings, and the server SQL capability profile are loaded through one repository join before compilation. Caller-provided RuleSpec, schema, binding, or capability objects are no longer accepted.

The plan attests the exact compiler version, RuleSpec hash, input/output schema snapshot IDs and hashes, and deployment binding IDs. Persistence compares those attestations against the canonical database rows. The runner loads and rechecks the same canonical RuleVersion and snapshots before dispatch.

Logical release plans now persist as compiled, and released package plan references explicitly identify themselves as plan_kind=semantic and status=compiled. Physical plans use the explicit lifecycle compiled -> tested -> published; only successful integration_preflight evidence can produce tested, and only an attested tested plan whose RuleVersion remains published can be published. The runner rejects both compiled and tested plans.

Correctness and security changes

PostgreSQL upsert now proves that the requested idempotency column is an exact single-column PRIMARY KEY or UNIQUE constraint using live server metadata.
MySQL upsert proves the same exact key and rejects any alternate unique index, preventing ON DUPLICATE KEY UPDATE from merging a different logical row through another unique collision path.
SQL casts accept only on_error=fail; reject, quarantine, and warn fail closed.
Final schema compatibility is exact. Numeric type changes require an explicit cast.
Deduplication extends the requested ordering with every remaining projected field and explicit null ordering, yielding a deterministic total value order; fully identical rows remain equivalent.
The runner requires the exact supported dataops-sqlglot-30.13.0 attestation and validates a closed INSERT-SELECT SQLGlot AST subset. Anonymous and non-allowlisted functions, joins, CTEs, set operations, and other out-of-compiler nodes are rejected even if a malicious publisher recomputes the plan hash.
PostgreSQL identifiers are bounded to 63 UTF-8 bytes and MySQL identifiers to 64 UTF-8 bytes for schemas, tables, schema fields, expression fields, step columns, and derived/deduplication fields.
A new migration adds tested to the durable execution-plan status constraint.

TDD evidence

The initial RED run produced the expected failures for the old caller-object API, canonical component-to-rule mismatch, and function-bearing plan. Additional regression tests cover non-fail cast actions, exact numeric types, total deduplication order, UTF-8 identifier byte limits, logical compiled status, registry availability before release allocation, canonical runner attestation tampering, and lifecycle evidence.

Final verification:

Focused compiler/repository/release/runner plus real database integration:
63 passed

Real PostgreSQL/MySQL execution and MySQL uniqueness failure cases:
3 passed

Full repository suite:
496 passed, 26 skipped, 59 subtests passed

Ruff on all changed Python and migration files:
All checks passed!

git diff --check:
no output

Remaining bounded concerns

The server capability profile is intentionally a closed policy profile for the two supported dialects (C/POSIX for PostgreSQL and utf8mb4_0900_bin/ICU for MySQL). A deployment with different concrete datasource capabilities fails closed at runner dispatch.
Only same-datasource table/view-to-table SQL pushdown and exact-key upsert are executable in this slice. Other write strategies and the read-only quality executor remain fail-closed.

Task 4 final review correction — compiled-only production boundary