EZ-Blog Milestone: From YAML to SQLite

EZ-Blog has reached another major milestone. Phase 2 of the project is complete, and SQLite is now the authoritative persistence system for the desktop application.

That sentence sounds deceptively simple.

Replacing a YAML file with a database is easy. Replacing it without changing the behavior of an existing application, losing data, weakening recoverability, or introducing subtle publishing differences is considerably harder.

That was the real objective of Phase 2.

The migration was deliberately incremental. At every stage, the existing YAML implementation remained available as a known-good reference while the SQLite implementation was built beside it, tested against it, and ultimately proven equivalent.

Only after all of that evidence was in place did EZ-Blog actually switch its source of truth to SQLite.

Where We Started

At the end of Phase 1, EZ-Blog had already undergone a major architectural transformation.

The original desktop application had been modularized into clean components for the domain model, publishing engine, persistence layer, desktop interface, and future server application.

The remaining persistence model, however, was still based on the original YAML content file.

That worked well for the standalone desktop application, but it was not a sufficient foundation for the next major objective: allowing EZ-Blog to eventually support browser and mobile editing through a server application.

A real database was needed.

SQLite was selected because it provides transactional database semantics without introducing the operational complexity of a separate database server.

The Phase 2 architecture therefore became:

                EZ-Blog Application
                        |
                Neutral Repository APIs
                        |
              +---------+---------+
              |                   |
              v                   v
         YAML Adapters       SQLite Adapters
                                  |
                                  v
                           SQLite Database

The important architectural decision was that neither the editor nor the publishing engine would know or care whether the content came from YAML or SQLite.

Persistence remained behind neutral repository interfaces.

Proving Behavior Before Replacing It

One of the strongest decisions in the migration was to avoid writing SQLite code and simply assuming that it behaved the same as the YAML implementation.

Instead, we created reusable repository contract tests.

The same behavioral tests were executed against both implementations:

             Repository Contract
                /          \
               /            \
              v              v
           YAML            SQLite

Those contracts covered articles, pages, site configuration, navigation, ordering, lookup behavior, updates, deletion, validation, transactional failure behavior, and persistence across close/reopen boundaries.

This gave us something much stronger than two implementations that merely looked similar.

It gave us executable evidence that they obeyed the same application-level contract.

The SQLite repositories were then implemented incrementally:

• Article persistence
• Page persistence
• Site configuration and navigation
• Multi-repository transaction coordination
• Database lifecycle management
• Locking and failure handling

By the end of that work, the YAML and SQLite persistence paths had converged on the same observable behavior.

Importing the Real Site

The next challenge was migrating the actual EZ-Blog content.

A strict YAML-to-SQLite importer was created rather than treating the existing YAML file as loosely structured input.

Before SQLite receives a single content row, the importer validates the source for conditions such as:

• duplicate or malformed slugs,
• invalid dates,
• malformed entries,
• invalid navigation,
• unresolved page relationships,
• unsupported values,
• and information that could not be represented without loss.

The actual import then occurs as one transaction.

Articles, pages, site configuration, navigation, and import provenance are written together.

If any part fails, the entire operation rolls back.

There is no partially imported EZ-Blog database.

The real canonical site was then imported.

The resulting database contained:

• 99 articles
• 11 menu landing pages
• 59 menu pages
• 9 other pages
• 13 navigation entries

The database passed schema verification, relationship validation, foreign-key checks, SQLite integrity checks, close/reopen verification, and a manual inspection using DB Browser for SQLite.

The Most Important Test: Generate the Entire Website Twice

Database equivalence by itself was not enough.

The purpose of EZ-Blog is not merely to store articles. Its real product is the generated website.

That led to what may be the strongest test in the migration.

The complete site was generated independently from both persistence systems.

                 Canonical YAML
                  /          \
                 /            \
                v              v
           YAML Loader     SQLite Import
                |              |
                v              v
          YAML State      SQLite State
                |              |
                +------+-------+
                       |
                       v
                  Same Publisher
                  Same Templates
                  Same Clock
                    /      \
                   v        v
              YAML Site  SQLite Site
                    \      /
                     compare

There was one complication: the publisher naturally writes generation timestamps.

A small injectable Clock abstraction was therefore introduced so both runs could execute at exactly the same logical time.

That allowed us to perform a genuine byte-for-byte comparison rather than normalize away differences.

The result:

179 generated HTML files were identical.

The verification also checked:

• identical relative paths,
• identical filename casing,
• identical static assets,
• identical navigation,
• identical article ordering,
• identical metadata,
• identical scripts,
• identical timestamps,
• and identical generated content.

Negative tests were also created to make sure the comparison system really detected missing files, additional files, renamed files, ordering changes, and altered content.

In other words, the test did not merely demonstrate:

SQLite contains the same information.

It demonstrated:

Starting independently from YAML or SQLite produces the same website.

That was a critical milestone.

Backup Before Trust

Before allowing SQLite to become authoritative, EZ-Blog also had to prove that the new database could be recovered.

Phase 2 therefore implemented SQLite-aware backup and isolated restore qualification.

A backup is not considered valid merely because a database file was copied somewhere.

EZ-Blog verifies the backup independently, records hashes and provenance, restores it into a separate location, performs database integrity checks, reads its content through the repositories, and even generates the complete website from the restored database.

Only after those checks succeed is the backup considered qualified.

This means the migration did not merely ask:

Can SQLite store the site?

It also asked:

Can we recover the entire publishing system from a backup and prove that the result still works?

The answer is now yes.

Then We Ran the Actual Desktop Against SQLite

Even all of those automated tests were not considered sufficient.

The existing desktop application was given an explicit SQLite composition mode while YAML remained authoritative.

The application was then exercised manually against a real SQLite database.

Existing content was opened and inspected. Content was edited. Preview and site generation were exercised. The application was closed and restarted, and the SQLite changes were confirmed to persist.

Throughout that trial, the canonical YAML remained unchanged.

There was also deliberately no dual-write mechanism.

The application never attempted to secretly maintain synchronized YAML and SQLite copies. Dual-write systems create exactly the sort of ambiguous source-of-truth problems this migration was designed to avoid.

The Cutover

Only after all of those gates passed did EZ-Blog perform the actual source-of-truth transition.

The production desktop configuration now uses:

C:\Users\micha\AppData\Local\EZ-Blog\site-content.sqlite3

as its authoritative content database.

The original YAML remains preserved as historical and recovery evidence, but it is no longer the active persistence source.

There is no automatic fallback to YAML.

If the configured SQLite database cannot be opened, the application fails explicitly rather than quietly switching to another source and creating ambiguity about where content is being written.

That distinction is important.

A persistence system should have one clearly identifiable authority.

EZ-Blog now does.

Final Verification

Phase 2 closed with another complete verification run.

The final build executed:

426 tests

with:

• 0 failures
• 0 errors
• 0 skipped tests
• 0 SpotBugs findings

The authoritative database independently passed:

• schema-version validation,
• migration-history validation,
• repository reads,
• quick integrity checking,
• full integrity checking,
• foreign-key checking,
• close/reopen validation,
• and sidecar cleanup verification.

The desktop application was then manually launched one final time against SQLite, content was inspected, previews and site regeneration were exercised, and the application was closed and reopened successfully.

The canonical YAML's SHA-256 hash remained unchanged throughout the migration:

2739e4a1d9f3fbd55f6f537e86074606212edc0be16bd53abbd164a3a62a9629

Finally, the completed work was tagged in Git as:

sqlite-persistence-v1

What Phase 2 Really Accomplished

The most important result of Phase 2 is not the presence of a .sqlite3 file.

It is the removal of a major architectural constraint.

Before this phase, EZ-Blog content persistence was inherently tied to a local YAML file.

Now the application has:

• a transactional persistence layer,
• explicit repository contracts,
• schema versioning and migrations,
• atomic multi-repository operations,
• integrity validation,
• deterministic import,
• provenance tracking,
• backup and restore qualification,
• operational database inspection,
• and a persistence architecture capable of supporting both desktop and future server applications.

The desktop application still works.

The generated website is unchanged.

But the foundation underneath both is considerably stronger.

Next: Phase 3

With persistence now stabilized, the next phase can finally build on top of it rather than underneath it.

Phase 3 begins the EZ-Blog server/admin application.

That is the step that starts moving EZ-Blog toward its larger goal: allowing articles to be created and published from a browser—and eventually from a phone—while continuing to use the publishing system and site format that already work.

Phase 1 separated the application into clean architectural components.

Phase 2 gave those components a durable transactional data foundation.

Now the project is ready to start putting that architecture to work.

EZ-Blog has officially moved from a file-backed desktop publisher to a database-backed publishing platform.

And the existing website never had to change to get there.