Decouple canonical persistence from SQLite and add database backend adapters

[chubes4]

Direction

Decouple MDI's canonical file-backed persistence model from its SQLite query/index implementation while preserving SQLite as the complete reference backend.

The prior SQLite-only product decision recorded in this issue has changed. MDI should support additional database backends through explicit capabilities without duplicating canonical storage, reconstruction, reconciliation, or persistence logic.

Architecture

Markdown / JSON / schema files
              ↕
canonical persistence + reconciliation core
              ↕
normalized backend contract and capabilities
          ↙                     ↘
SQLite adapter              MySQL adapters

The canonical core owns file formats, storage routing, manifests, hashes, persistence policy, normalized logical mutations, reconciliation decisions, and changed-path receipts.

Backend adapters own query/result behavior, transactions, schema and table introspection, hydration, mutation capture, and backend-specific runtime optimizations.

Product Shape

Authority, backend, and persistence scope are independent dimensions:

• SQLite mirror and full primary remain supported without configuration or behavior changes.
• MySQL content-primary makes configured posts, meta, and terms file-authoritative on normal WordPress installations.
• MySQL full-primary adds database-level mutation capture for options, users, comments, schemas, and arbitrary plugin tables where the required database boundary is available.
• Backend capabilities determine supported guarantees; mode names do not imply unavailable behavior.

Compatibility Gate

SQLite compatibility is a separate proof obligation. The program preserves:

• existing Markdown, JSON, schema SQL, manifest, and index formats;
• mirror and primary behavior;
• cold/warm reconstruction;
• lazy content and search behavior;
• explicit/shutdown flush semantics;
• CLI, abilities, response schemas, diagnostics, and observability identifiers;
• released and trunk SQLite Integration compatibility.

Tracked Work

• Define backend capabilities and freeze the SQLite compatibility contract #172 defines backend capabilities and freezes the executable SQLite compatibility contract.
• Extract backend-neutral canonical persistence and mutation projection #175 extracts backend-neutral canonical persistence and mutation projection after Define backend capabilities and freeze the SQLite compatibility contract #172.
• Move the current SQLite runtime behind a backend adapter #174 moves the current SQLite runtime behind the backend adapter after Define backend capabilities and freeze the SQLite compatibility contract #172 and Extract backend-neutral canonical persistence and mutation projection #175.
• Add pluggable content layout profiles #176 adds pluggable content layout profiles; it can proceed independently against the canonical storage contract and must integrate with Add backend-neutral three-way reconciliation with plan/apply #171.
• Add backend-neutral three-way reconciliation with plan/apply #171 adds backend-neutral three-way reconciliation with plan/apply after Extract backend-neutral canonical persistence and mutation projection #175, consuming Add pluggable content layout profiles #176 for path identity.
• Add a MySQL content-primary backend #170 adds MySQL content-primary support after Define backend capabilities and freeze the SQLite compatibility contract #172, Extract backend-neutral canonical persistence and mutation projection #175, Add pluggable content layout profiles #176, and Add backend-neutral three-way reconciliation with plan/apply #171; Move the current SQLite runtime behind a backend adapter #174 proves SQLite preservation before this backend ships.
• Add full-primary MySQL mutation capture through a delegating database backend #173 adds later full-primary MySQL mutation capture after Add a MySQL content-primary backend #170.

Existing related work:

• Support MySQL-backed markdown import/export mode #106 delivered MySQL-compatible explicit import/export.
• Add constrained-runtime primary storage and explicit flush contract #125 delivered explicit primary-runtime flush and path receipts via Add constrained primary runtime and explicit flush contract #126 and Return canonical changes from explicit flushes #128.
• Generated wiki posts can disappear after primary-mode reload #99 tracks a durability failure that must become a backend contract regression fixture.
• Architecture: punt all git concerns to DMC GitSync #61 keeps Git and remote merge policy outside MDI.

Program Acceptance Criteria

1. Canonical persistence services contain no SQLite/PDO type dependency.
2. Current SQLite stores and callers remain compatible without migration.
3. SQLite passes the complete backend contract as the reference implementation.
4. MySQL content-primary reconstructs and round-trips managed WordPress content on a real MySQL/MariaDB runtime.
5. Unsupported backend capabilities fail closed and are visible through doctor/health output.
6. The same reconciliation and path-layout contracts work across supported backends.

AI Assistance

OpenAI GPT-5.6 Sol via OpenCode was used to inspect the current MDI implementation, historical issues, and compatibility surface and draft this revised tracker. Chris Huber changed the product direction, directed the architecture, and remains responsible for the proposal.

[chubes4]

Closing this.

After exploring the design more, a MySQL backend isn't worth the duplication. The plugin's value is the "SQLite as ephemeral query engine, files as truth" architecture — that's Studio/Playground-native by design, and it's already the canonical path for Intelligence and the Automattic-internal tooling that actually uses this plugin.

A MySQL mode would:

• Serve a narrow audience (sites that want the markdown layer but won't migrate to SQLite)
• Compromise the philosophical clarity (MySQL would hold meta/terms/options persistently, creating a split-truth model the SQLite mode elegantly avoids)
• Double the maintenance surface (every future feature — auto-ID assignment Auto-assign IDs for new .md files without id: frontmatter #42, FTS5 FTS5 search index for post_content #43, file watching — would need "does this work in both modes?")
• Produce a worse version of the architecture, dressed up as compatibility

The right answer for sites that want this storage model is to run them on SQLite. That's a deployment decision, not something the plugin should paper over.

Leaving this open as a parking lot doesn't serve anyone — reopening is cheap if the calculus changes. For now, SQLite-only stays the contract.

[chubes4]

Reopened because the product direction has explicitly changed: SQLite remains fully supported, but MDI's canonical persistence model should no longer require SQLite. The issue body now records the revised backend-capability architecture and compatibility gate. Child issues will track the implementation sequence. AI assistance: OpenAI GPT-5.6 Sol via OpenCode was used to inspect the current code and historical issue context and draft this update; Chris Huber directed and approved the architectural change.

[chubes4]

First independent wave (#172 and #176) was dispatched through Homeboy but blocked before provider execution by Extra-Chill/homeboy#12117. Fanout evidence: . No MDI code changed. Dependent waves remain correctly undispatched until the control-plane repair lands and the prerequisites complete. AI assistance: OpenAI GPT-5.6 Sol via OpenCode executed and diagnosed the workflow; Chris Huber directed the cook.

[chubes4]

Correction to the preceding comment: the shell consumed the inline fanout ID while posting it.

The first independent wave (#172 and #176) was dispatched through Homeboy but blocked before provider execution by Extra-Chill/homeboy#12117.

Fanout evidence: cook-batch-markdown-database-integration-issue-172-2-d5a5569081b2.

No MDI code changed. Dependent waves remain correctly undispatched until the control-plane repair lands and the prerequisites complete.

AI assistance: OpenAI GPT-5.6 Sol via OpenCode executed and diagnosed the workflow; Chris Huber directed the cook.