Add schema evolution and migration support for SQLite repositories

[eviltester]

Summary

SQLite repository schema creation is currently basic: the repository can create schema tables and add newly introduced columns, but it does not yet provide a real migration system for model changes such as field rename, field deletion, type changes, relationship changes, or versioned entity model evolution.

We need a future migration capability driven by entity model changes and/or explicit versioning in the entity model.

Current State

• Repository initialization creates SQLite tables from ERSchema.
• Existing entity tables can be extended by adding newly defined fields as columns.
• Relationship tables are created from relationship vectors.
• There is no durable schema metadata describing which Thingifier schema version produced the database.
• There is no migration plan for:
  • renamed fields;
  • removed fields;
  • changed field types;
  • primary key changes;
  • relationship rename/removal/cardinality changes;
  • table rebuilds needed for SQLite constraints;
  • user-defined data transformations.

This is acceptable for the current repository experiment, but it is not enough for long-lived file-backed SQLite databases.

Motivation

File-backed SQLite makes Thingifier data durable, so entity model changes become database migration events. Without migration support, a model change can leave persisted databases in a partially compatible or ambiguous state.

The system should eventually be able to answer:

• What schema version is this SQLite database currently at?
• What entity model version is the application expecting?
• Can the database be migrated automatically?
• Does a migration require a user-supplied transform?
• Should startup fail safely rather than silently running with a mismatched schema?

Proposed Plan

1. Add schema/version metadata

• Add a Thingifier metadata table for repository schema state, e.g. __thingifier_schema.
• Store at least:
  • schema/model version;
  • entity definitions hash or fingerprint;
  • migration history;
  • repository implementation/version metadata.
• Define whether versioning lives in ERSchema, EntityRelModel, repository config, or an explicit migration descriptor.

2. Define migration descriptors

• Introduce an explicit migration concept, such as:
  • RepositoryMigration;
  • SchemaMigration;
  • EntitySchemaMigration;
  • or another name matching the codebase style.
• Support migration steps for common changes:
  • add field;
  • rename field;
  • remove field;
  • change field type with transform;
  • add/remove/rename relationship;
  • rebuild table when SQLite cannot alter constraints in place.
• Keep migrations repository-neutral at the contract level where possible, with SQLite-specific execution underneath.

3. Decide model-change detection strategy

• Compare stored schema metadata with current ERSchema.
• Distinguish safe automatic changes from ambiguous or destructive changes.
• Require explicit migration descriptors for ambiguous changes such as rename/delete/type conversion.
• Fail startup or repository initialization clearly when a persisted database schema does not match and no migration is available.

4. Implement SQLite migration execution

• Use transactions around each migration or migration batch.
• For SQLite table rebuild migrations, follow the safe pattern:
  • create new table;
  • copy/transform data;
  • recreate indexes/relationships/constraints;
  • swap tables;
  • validate row counts/integrity;
  • commit.
• Preserve relationship rows when possible.
• Add backup/export guidance before destructive migrations.

5. Preserve compatibility with in-memory and future repositories

• Keep basic in-memory repository behavior simple.
• Do not require every repository to implement SQLite-style migrations immediately.
• Repository providers should expose whether they support durable migration metadata.
• Java-side schema/version checks can still be useful for all repository types.

6. Add tests

• SQLite file-backed database survives reopen with unchanged schema.
• Adding a field migrates safely and preserves existing rows.
• Renaming a field requires an explicit migration and preserves data when provided.
• Removing a field requires explicit destructive migration approval or descriptor.
• Changing a field type requires explicit transform or fails safely.
• Relationship table migration preserves existing valid relationships where possible.
• Failed migrations rollback cleanly and leave the prior database usable.
• Missing migration metadata is handled deterministically for existing experimental databases.

Acceptance Criteria

• SQLite repositories persist schema/model version metadata in the database.
• Repository initialization detects schema mismatch between the current ERSchema and the persisted SQLite database.
• Safe additive field changes continue to work and are recorded as migrations or schema updates.
• Ambiguous/destructive changes do not happen silently.
• Field rename/delete/type-change scenarios either:
  • run through explicit migration descriptors; or
  • fail with a clear error explaining the required migration.
• SQLite migrations run in transactions and rollback on failure.
• Migration history is queryable or exportable for debugging.
• Existing repository contract tests continue to pass.
• SQLite-specific migration tests cover add, rename, delete, type-change, relationship-change, and failed-migration rollback cases.
• The docs explain the difference between one-shot schema creation and durable schema migration.

Non-Goals

• Implementing a full general-purpose ORM migration framework.
• Automatically guessing whether a deleted field was actually renamed.
• Silently dropping data.
• Requiring all repository implementations to support durable migrations immediately.
• Changing Challenger-specific behavior in this issue.
• Replacing Java validation or SQLite-native validation work tracked separately in Add SQLite-native validation and integrity constraints #30.

Notes

This follows on from the SQLite-backed repository work. The guiding principle should be: one-shot schema creation is fine for new or in-memory databases, but file-backed repositories need explicit schema evolution semantics before they can be considered robust for long-lived persisted data.