Tracker: mdi-native as a WordPress PHPUnit backend — engine gaps vs. WP core + real plugin SQL (wp-codebox#2487)

## Goal

Make `mdi-native` a viable `database-type` for `wordpress.phpunit` in wp-codebox (Automattic/wp-codebox#2487) — a dependency-free, secret-free, no-process WordPress test backend — with generic SQL and `wpdb` parity for arbitrary WordPress plugins and workloads.

The following sections retain the original audit of a real MySQL-heavy plugin (Extra-Chill/data-machine-events: 109 raw `$wpdb` call sites, 4 `dbDelta` tables, multisite suite) and the WordPress core test lifecycle against `inc/native/` at `8e2b21f` (v0.13) as historical evidence. Grep-verified, not assumed; corrections welcome.

## Historical Baseline: Already Supported

- Bounded equality `JOIN`, `LEFT JOIN`; `GROUP BY`, `HAVING`, `DISTINCT`, `UNION [ALL]`, `LIMIT/OFFSET`
- `IN (SELECT …)`, `NOT IN (SELECT …)`, `EXISTS (SELECT …)` subquery predicates
- `LIKE`, `REGEXP`, `CASE`, `COALESCE`, `GROUP_CONCAT`, `JSON_*`, `SQL_CALC_FOUND_ROWS`/`FOUND_ROWS`
- `SHOW TABLES [LIKE]`, `SHOW [FULL] COLUMNS`, `DESCRIBE`, `SHOW INDEX/KEYS`, `SHOW VARIABLES/STATUS`
- `CREATE TABLE`, `ALTER TABLE` (single-column), `INSERT IGNORE`, `ON DUPLICATE KEY UPDATE`, transactions + `SAVEPOINT`
- Deterministic `markdown_db_native_unsupported_query` errors with a structured `reason` — good; the PHPUnit integration will surface these as a distinct diagnosis cause.

## Historical Baseline: Gaps

- [x] #371 — `SELECT … FOR UPDATE` (accept-and-ignore under single-writer semantics). Blocks: any lock-then-write pattern.
- [x] #372 — scalar-function coverage: `DATE()`, `DATE_ADD/SUB`, `NOW()`, `GREATEST/LEAST`, `IF/IFNULL`, string, math incl. trig. Blocks: date bucketing, geo distance, most `WP_Query` date queries.
- [x] #373 — JOIN shape: arbitrary `ON` predicates, aliased self-joins (`WP_Meta_Query` `mt1/mt2`), derived tables in `FROM`. Blocks: core meta/tax queries; `UNION`-in-`FROM`.
- [x] #374 — table-prefix routing for multisite (`unsupported_table_prefix`). Blocks: every multisite boot and every `wp_codebox_multisite: true` suite.
- [x] #375 — `dbDelta()` round-trip verification (rerun must be a no-op; composite/unique/sub-part keys; `ALTER` diffs). Blocks: plugin schema installs behaving like MariaDB.
- [x] #376 — the coverage/lifecycle probes fail schema validation against current wp-codebox; pin and commit the coverage report so consumers can read the supported surface.

## Historical Baseline: Suggested Order

#376 first (so every other item can be *measured* with the coverage report and shadow verifier instead of grep), then #374 (multisite is a hard wall for the target consumers), then #372 → #373 → #371 → #375.

## Historical Baseline: Measuring Readiness

`MARKDOWN_DB_NATIVE_SHADOW=true` + `tests/probe-native-shadow-report.php` replays a real suite's reads through mdi-native without failing them and reports unsupported/mismatched templates. This was the proposed measurement path before #376 and the later paired consumer evidence; the current paired result is recorded below.

## Historical Landed Engine Repairs

The six engine/probe gaps landed in #384 (commit `d15708f8c8e80e650a7a7e023dce7e9f712a41bb`). Source PRs #378 through #383 are closed as superseded. The integrated candidate passed 45/45 SQL corpus statements and 26/26 WordPress operations, the full smoke suite, SQLite differential, WooCommerce lifecycle, multisite cold reload, and native/MariaDB dbDelta parity. Scalar/subquery boolean composition and combined-result UNION ordering/limits are included.

This was the consumer-suite status when the repair landed. Later paired consumer evidence is recorded below. No release or deployment was performed.

### AI Assistance

GPT-6 Astra (`openai/gpt-6-astra`) via OpenCode coordinated parallel OpenAI GPT-5.6 Terra (`openai/gpt-5.6-terra`) workers, reviewed and corrected candidates, checked Lab evidence, and merged the integrated repair under Chris Huber's direction.

## Current Status and Priority (September 12, 2026)

This tracker now prioritizes generic SQL and `wpdb` parity for arbitrary WordPress plugins and workloads. The earlier corpus and consumer references are historical progress measures, not a limit on the engine contract.

Merged #403 provides the latest paired consumer evidence with the same companion adapter:

| Backend | Passed | Failed | Skipped | Total |
| --- | ---: | ---: | ---: | ---: |
| Native | 1040 | 4 | 9 | 1053 |
| MySQL | 1046 | 0 | 7 | 1053 |

The four remaining native failures require physical `mysqli` connections/handles, which are outside the native-engine contract. Two `smoke-native-table-replace.php` failures predate this work and remain separate parity evidence. Earlier 942/70/32 counts are stale.

Merged #399, #400, #403, #404, #405, and #406 supply the current repair evidence. #370 merged on September 9, 2026; any earlier reference to #370 as current or draft is historical context only.

Performance profiling may proceed in parallel where it preserves or improves parity. The next performance objective, after sufficient parity evidence, is faster and more scalable operation than both SQLite and MySQL. Neither target has been achieved.

### CLI-First Acceptance

CLI is the primary entrypoint. An acceptance CLI invocation must run in a fresh process and measure total time through boot, command setup, query or workload execution, shutdown, and durable write completion. Warm-process diagnostic loops are permitted separately and must be labeled as diagnostics. Results must establish correctness and durability, and separately report cold-cache and warm-cache measurements.

Acceptance evidence must use identical corpus, runtime, iteration, and concurrency settings for comparisons. It must report scaling, peak memory, concurrent-workload behavior, throughput, and tail latency as well as aggregate latency. Representative corpora demonstrate progress without narrowing arbitrary-plugin scope.

The epic's staged local-site rehearsal, cutover, SQLite-removal, and production gates remain intact.

### Current Status AI Assistance

GPT-6 Astra (`openai/gpt-6-astra`) via OpenCode assisted refreshing status and priorities based on source and merged evidence under maintainer direction.

Start with inc/native/ and tests/probe-native-shadow-report.php, then review the paired consumer evidence from merged issues #399, #400, #403, #404, #405, and #406. Use the CLI-first acceptance criteria and current Native/MySQL results as the baseline; done requires broader generic SQL and wpdb parity evidence for arbitrary plugins, including correctness and durability measurements.

Written by the indexing model from the issue text.