Epic: ship a pure-PHP MySQL-compatible canonical database engine

## Goal

Run WordPress and arbitrary plugins through a pure-PHP database engine that preserves MySQL-visible SQL and `wpdb` behavior while using MDI canonical files as authoritative storage. The implementation is database-agnostic and has no SQLite, MySQL, MariaDB, sidecar, wire-protocol, or compiled-runtime dependency.

## Architecture constraints

- Implement the SQL lexer, parser, typed AST, planner, optimizer, and executor in PHP.
- Execute through `WP_Markdown_Native_WPDB`; the native engine does not require or emulate a real `mysqli` server handle.
- Keep SQL grammar, planning, and execution table-neutral. WordPress and plugin semantics enter through typed schemas and canonical storage providers.
- Use canonical options, Markdown content, JSON snapshots, and row partitions as native storage primitives.
- Implement MySQL-compatible coercion, collations, metadata, errors, insert IDs, affected rows, session state, and transaction behavior without linking behavior to a MySQL implementation.
- Expand compatibility from deterministic corpus and shadow evidence, not table-specific parser branches.

## Workstreams

1. Replace the bounded regex recognizer with a pure-PHP tokenizer and typed MySQL AST.
2. Add generic expressions, aliases, joins, boolean predicates, ordering, grouping, aggregation, and limits.
3. Add a typed schema catalog and MySQL-compatible type/collation system.
4. Add canonical indexes and pushdown over options, Markdown posts, snapshots, and row partitions.
5. Add generic DML, auto-increment, constraints, and exact `wpdb` mutation state.
6. Add atomic multi-file transactions, savepoints, locking, journaling, and crash recovery.
7. Add dynamic DDL and plugin table persistence for `dbDelta()` and schema introspection.
8. Add a native `db.php` bootstrap path before every SQLite/PDO check.
9. Differentially verify WordPress install, front end, admin, REST, cron, WP-CLI, multisite, and representative plugins.

## Acceptance criteria

- WordPress installs and boots with PHP alone and no database extension or external database process.
- Arbitrary plugin SQL issued through `wpdb` is parsed and executed by the generic PHP engine.
- Core and plugin DDL/DML/transactions preserve MySQL-visible rows, metadata, errors, insert IDs, affected rows, and session behavior.
- Canonical files remain authoritative and recover after process termination at every transaction boundary.
- Unsupported SQL fails through deterministic MySQL-compatible `wpdb` errors without fallback to SQLite or MySQL.
- The compatibility matrix has no unexplained differential failures for the accepted WordPress/plugin corpus.

## References

- https://github.com/Automattic/markdown-database-integration/pull/227
- https://github.com/Automattic/markdown-database-integration/pull/229
- https://github.com/Automattic/markdown-database-integration/pull/231

## AI assistance

GPT-5.6 Sol via OpenCode helped inspect the existing PHP query and storage contracts and structure the corrected pure-PHP workstreams. Chris Huber explicitly required a pure-PHP, database-agnostic implementation and rejected the prior sidecar direction.

## Staged Acceptance Roadmap

Updated September 12, 2026. The target is a native-only engine, validated on the accepted local site, followed by removal of SQLite support. Production multisite adoption is a separate evidence-based decision. Existing workstreams and compatibility requirements above remain in force.

Current implementation and remaining acceptance are tracked by this epic, #377, and relevant implementing PRs. #370 merged on September 9, and #399, #400, and #403 subsequently repaired native post transactions, multisite transaction journals, and transactional-table capabilities. The paired consumer evidence is recorded below; complete parity and the staged acceptance gates remain open.

### Stage 1: Native Engine Acceptance

- [ ] Verify the complete accepted SQL/wpdb corpus with no unexplained differential failures, including required DDL, DML, constraints, collations, errors and session behavior.
- [ ] Verify transactions, savepoints, concurrent writers, interrupted writes, crash recovery and consistent restoration of canonical state.
- [ ] After parity, beat SQLite and MySQL using CLI-first fresh-process total invocation measurements, identical runtime/corpus settings, verified operation counts, and explicit per-workload regression budgets.
- [ ] Consolidate unproven experiments and retain measured, readable owning-layer improvements.

### Stage 2: Isolated Local-Site Rehearsal

- [ ] Create an isolated copy with the accepted site configuration, canonical content and plugin state.
- [ ] Verify front end, admin/editor, REST, CLI, search/wiki, chat, scheduled jobs and active-plugin workflows using native alone.
- [ ] Verify state completeness, backup restoration, cold restarts, and the supported external-file editing workflow.
- [ ] Record site-specific latency/throughput alongside correctness results.

### Stage 3: Local-Site Cutover

- [ ] Verify a restorable backup and explicit recovery procedure.
- [ ] Obtain operator authorization, switch the site, and verify cold restart plus all accepted workflows.
- [ ] Complete an agreed normal-use acceptance period with no unresolved data-integrity or compatibility failures.

### Stage 4: Remove SQLite Support

- [ ] Prove native state is complete and restorable from canonical files without hidden SQLite state.
- [ ] Remove SQLite runtime implementation, dependencies, configuration and routing in a separately reviewed change.
- [ ] Provide an explicit transition for existing SQLite-backed installations and verify native install, restore, restart and workload gates after removal.
- [ ] Retain useful differential references in development tooling separately from shipped runtime support.

### Stage 5: Production Multisite Evaluation

- [ ] Verify network/per-site state, shared users, switch_to_blog behavior, site lifecycle, and the accepted production plugin corpus.
- [ ] Replay representative concurrent requests, publishing pipelines and background jobs on an isolated environment; measure sustained throughput, lock contention and tail latency against MySQL.
- [ ] Test consistent online backups/restores, schema changes, process termination, disk exhaustion, and the supported filesystem/deployment topology.
- [ ] Establish operating procedures and assess whether the implementation remains maintainable and simpler in practice.

### Stage 6: Production Adoption Decision

- [ ] Review production evaluation evidence and make an explicit go/no-go decision.
- [ ] If approved, define a reversible canary, integrity checks, rollback criteria and operator ownership before migration.

Local success does not authorize a production migration. Production replacement remains optional and conditional on its own gates.

### Evidence and Tracking Rules

Each stage closes only with linked implementing PRs, exact candidate/runtime identity, reproducible commands, result artifacts, and documented remaining limitations. Use #232, #377, and the relevant implementing PR for active findings and acceptance. Historical artifacts are retained as evidence. Partial tests and single-workload wins do not close a stage.

### Roadmap AI Assistance

GPT-6 Astra through OpenCode inspected this epic and #370, structured the staged gates from the engineering discussion, and updated the tracking descriptions at Chris Huber's direction. The original disclosure above is retained.

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

The primary objective is full SQL and `wpdb` parity for arbitrary WordPress plugins and workloads. Representative corpora are measurement tools for proving progress and finding gaps; they do not bound the supported plugin or workload scope.

The native engine is already selected on an operator local site. This is not a production-adoption decision and does not close any of the staged rehearsal, cutover, SQLite-removal, or production gates above.

Recent merged work includes #399, #400, #403, #404, #405, and #406. In particular, #403 established the current paired consumer result 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 are physical `mysqli` connection/handle expectations. Physical `mysqli` is not a native-engine contract. Two `smoke-native-table-replace.php` failures predate this work and remain on the relevant baseline; they are tracked as separate parity evidence, not claimed fixed. Earlier 942/70/32 figures are stale.

#370 merged on September 9, 2026. Its measurements are retained as historical evidence, not current acceptance.

### Priority and Performance Policy

1. Close generic SQL, `wpdb`, DDL/DML, transaction, recovery, and arbitrary-plugin compatibility gaps first.
2. Profile and improve performance in parallel only where that work preserves or improves parity; performance work must not weaken compatibility requirements or narrow scope to a corpus.
3. After parity evidence supports it, make the engine faster and more scalable than both SQLite and MySQL. Neither performance target has been achieved yet.

### CLI-First Performance Acceptance

The CLI is the primary entrypoint and the performance contract. Every acceptance CLI invocation starts in a fresh process and measures total invocation time: PHP/bootstrap, command setup, query or workload execution, shutdown, and durable completion of writes. It must report correctness and durability alongside timing. Warm-process diagnostic loops are permitted separately and must be labeled as diagnostics rather than fresh-process acceptance results.

Performance evidence must separately report cold-cache and warm-cache runs, with identical corpus, runtime, iteration, and concurrency settings. It must measure scaling, peak memory, concurrent-workload behavior, throughput, and tail latency in addition to aggregate latency. SQLite and MySQL comparisons are performance goals, not accepted results until those complete measurements demonstrate them.

The staged site rehearsal, cutover, SQLite-removal, and production gates above remain required and unchanged.

### 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 the WP_Markdown_Native_WPDB entry point and the current tracking issues #377 plus the implementing pull requests. Choose one explicitly scoped acceptance gate or workstream, then run the relevant SQL/wpdb corpus or CLI measurement described in the roadmap; done requires linked evidence, reproducible results, and documented remaining limitations.

Written by the indexing model from the issue text.