# 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:

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

Initial RED result:

```text
18 failed in 1.38s
```

Representative expected failures:

```text
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:

```text
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'
```

```text
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
```

```text
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.

```text
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`.

```text
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.

```text
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:

```text
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:

```text
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:

```text
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:

```text
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

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

```text
.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:

```text
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

Date: 2026-07-23

This section supersedes the earlier statement in this report that Task 4
provided a production `compiled -> tested -> published` transition.

## Boundary correction

- Removed `record_bound_plan_test` and `publish_tested_bound_plan` from
  `DataRuleRepository`. Task 4 exposes no production API that accepts
  caller-authored execution evidence or promotes a plan.
- Physical bound plans still persist only as `compiled`.
- The production Runner remains published-only and rejects compiled plans.
- The real-database integration test publishes only inside its explicitly
  trusted in-memory test repository after a real preflight. This is not a
  production publication surface.
- Task 7 remains responsible for opaque server-created execution evidence and
  the authoritative tested/publication state machine.

## Additional fail-closed corrections

- Integer division (`/`) is removed from PostgreSQL and MySQL V1 capability
  support. Both dialect compilers reject a rule containing division; Polars
  reference capability remains available for its defined decimal semantics.
- Deterministic deduplication rejects a plan if any explicit or appended
  total-order field has an unproven ordering type. JSON and binary regression
  cases now fail compilation.
- The already-applied `20260723_120` migration remains unchanged.
  `20260723_130` pins the database status constraint to
  `compiled`, `published`, and `revoked`; it does not add `tested`.
  Its downgrade raises a clear `RuntimeError` because persisted compiled plans
  make this status-contract migration intentionally forward-only.

## Final TDD and verification evidence

The RED run produced six expected failures:

- production promotion API still present: 1
- PostgreSQL/MySQL division still advertised: 2
- JSON/binary deduplication accepted: 2
- missing explicit forward-only migration downgrade: 1

After the migration-chain correction, final verification was:

```text
Focused expressions/compiler/repository/release/runner/schema:
89 passed

Real PostgreSQL/MySQL integration:
3 passed

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

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

git diff --check:
no output
```