Fresh-database baseline schema + Data/Engine migration ownership split

[DutchJaFO]

Problem

1. Every brand-new database replays all 6 migrations in sequence, even though several steps are now pointless for a fresh install (migration002 repairs pre-existing bad data; migration003's pre-seed INSERTs are WHERE EXISTS-guarded no-ops; migration004 creates a table that migration006 immediately renames moments later in the same startup). A fresh database should be created directly at the current schema in one step.

2. Migration ownership is tangled. AuditMigrations.CreateAuditEntriesTable/RenameAuditEntriesToSystemAuditEntries (the SQL text) physically live in Quotinator.Data — but when they run is decided by Quotinator.Engine's QuotinatorMigrations.All, which interleaves them at arbitrary positions among its own domain migrations. Quotinator.Data should own and maintain the scripts for its own tables (System_AuditEntries, System_SchemaVersion, and any future System_* table), and DatabaseInitializer should always apply Data's own scripts first, before the consuming project's scripts — not interleaved, and not controlled by the consumer.

3. Version numbering must stay stable per project. With Data and Engine sharing one counter, "version N" is only meaningful relative to however many Data-owned migrations currently exist — if Data adds a migration later, every one of Engine's migration numbers silently shifts. Separate version-tracking tables fix this: System_SchemaVersion tracks only Data's own migrations, a new System_ConsumerSchemaVersion tracks the consumer's own migrations independently.

Scope

• Quotinator.Data's DatabaseInitializer gets its own fixed, internal DataOwnedMigrations list (not passed via constructor) and its own baseline fragment (DataBaselineSql) for System_AuditEntries — always applied/created first.
• Two separate version tables: System_SchemaVersion (Data's own count) and System_ConsumerSchemaVersion (new — the consumer's own count).
• A fresh (zero-table) database takes a one-step baseline path instead of replaying migration history; an existing database continues through the unchanged incremental path.
• Drift-detection tests ensure the baseline can never silently go stale relative to the numbered migrations, on both the Data side and the Engine side.
• Quotinator.Engine's migration constant names are renumbered to match their actual local position (no more "Migration005 is actually the 4th migration").

Full design: see the plan doc under docs/milestones/data-import-sources/.

Since nothing has shipped in a release yet, existing dev databases picking up this restructuring via a Reset (already a supported admin operation) is acceptable — no upgrade-in-place path is required.

[DutchJaFO]

Verification

Code complete and fully verified 2026-07-03. Build clean (0 warnings/errors), full test suite green (630/630), T1 and T2 both confirmed.

#	Status	Requirement	Method	Verification
1	✅	Data's own migrations always apply before any consumer migration	Unit test	DatabaseInitializerOwnershipTests.DataOwnedMigrations_AlwaysApplyBeforeConsumerMigrations
2	✅	Two independent version tables, each with stable local numbering	Unit test	InitialiseAsync_TrulyEmptyDatabase_TakesBaselinePathNotIncremental, InitialiseAsync_ExistingDatabaseAtVersion3_StillReplaysRemainingConsumerMigrationsIncrementally
3	✅	Fresh (zero-table) database takes the baseline path, not incremental	Unit test	InitialiseAsync_TrulyEmptyDatabase_TakesBaselinePathNotIncremental
4	✅	Existing non-empty database continues through the incremental path unaffected	Unit test	InitialiseAsync_ExistingDatabaseAtVersion3_StillReplaysRemainingConsumerMigrationsIncrementally
5	✅	Baseline schema never silently drifts from the numbered migrations (Data side)	Unit test	DatabaseInitializerOwnershipTests.DataOwnedBaseline_And_IncrementalReplay_ProduceIdenticalSystemAuditEntriesSchema
6	✅	Baseline schema never silently drifts from the numbered migrations (Engine side), including CHECK constraints	Unit test	Baseline_And_IncrementalReplay_ProduceIdenticalEngineSchema, Baseline_And_IncrementalReplay_AcceptSameCheckConstraintValues
7	✅	No baseline defined falls through to full incremental replay	Unit test	ApplyBaselineAsync_NoConsumerBaselineDefined_FallsThroughToIncremental
8	✅	preserveSchemaVersion:true on Reset preserves System_ConsumerSchemaVersion; System_SchemaVersion (Data's own) is never touched by Reset regardless of the flag	Unit test	ResetAsync_PreserveSchemaVersionTrue_KeepsExistingConsumerVersionRows, ResetAsync_AnyParameter_NeverTouchesDataSchemaVersion
9	✅	Build clean	Live	dotnet build --configuration Release — 0 Warning(s), 0 Error(s)
10	✅	All tests pass	Live	dotnet test --configuration Release --verbosity normal — 630/630 passed, 0 warnings
11	✅	T1: fresh dev database creates schema via baseline in one step; both version tables and System_AuditEntries correct; seeding/counts unchanged; /api/v1/version reports the consumer's count	Live	Deleted local quotinatordata.db, restarted. System_SchemaVersion has exactly 1 row (v2); System_ConsumerSchemaVersion has exactly 1 row (v4). 788 quotes, 478 sources, 2 characters seeded correctly.
12	✅	T1: default Reset on an #143-native database completes with no exception at all	Live	POST /api/v1/admin/database/reset → 200. Mandatory pre-reset backup taken; no Data migration phase line; no Exception thrown: anywhere. Verified via Quotinator.Tools.DbInspector: System_SchemaVersion untouched by the reset, System_ConsumerSchemaVersion cleanly cleared and replayed.
13	✅	T2: fresh Docker container shows identical baseline behavior	Live	docker build + fresh container (no pre-existing volume). Baseline path fired identically to T1; smoke tests (/health, /version, /quotes/random, /quotes/search) all passed; POST /admin/database/reset inside the container showed identical no-exception behavior to the local T1 run.
14	✅	A database created before the split (stale combined-counter System_SchemaVersion) fails loudly on ordinary startup rather than self-healing, with the database left unchanged and an explicit Reset as the sanctioned recovery	Unit test	InitialiseAsync_PreSplitCombinedCounterDatabase_FailsSafelyAndRequiresExplicitReset
15	✅	A genuine, unexpected failure during Reset's migration replay restores the pre-reset backup and rethrows, rather than leaving the database partially rebuilt	Unit test	DatabaseInitializerOwnershipTests.ResetAsync_MigrationFailsDuringReplay_RestoresPreResetBackupAndRethrows

Amendment: exception-free migrations

Live T1 testing of the original design exposed Exception thrown: 'Microsoft.Data.Sqlite.SqliteException' firing on every Reset, caught and interpreted via message-matching. This was reworked:

1. Root-cause fix, not a check. DropAndRebuildAsync (Reset) no longer wipes or replays Quotinator.Data's own migration history at all — Data's migrations only ever concern System_-prefixed tables that a Reset never drops in the first place, so the rename collision the old recovery code papered over is now structurally impossible.
2. The separate consumer-migration-3 "duplicate column" anomaly (real historical bug Bug: Reset Database fails with 500 — duplicate column name: ImportBatchId #106) is now a hard failure, not a self-heal — any migration failure backs up first and, on any exception (no type/message filtering), restores that backup and rethrows. An explicit Reset is the only sanctioned recovery.

Also closed a real, previously-unnoticed gap: Reset took no backup at all before this change. CLAUDE.md was corrected — SQLite has no IF EXISTS/IF NOT EXISTS for ALTER TABLE ... RENAME TO/ADD COLUMN at any version.

Full detail: docs/milestones/data-import-sources/143-migration-ownership-baseline-plan.md

[DutchJaFO]

Released in v1.8.0.

#	Status	Requirement	Method	Verification
1	✅	Data's own migrations always apply before any consumer migration	Unit test	DatabaseInitializerOwnershipTests.DataOwnedMigrations_AlwaysApplyBeforeConsumerMigrations
2	✅	Two independent version tables, each with stable local numbering	Unit test	DatabaseInitializerTests.InitialiseAsync_TrulyEmptyDatabase_TakesBaselinePathNotIncremental
3	✅	Fresh (zero-table) database takes the baseline path, not incremental	Unit test	DatabaseInitializerTests.InitialiseAsync_TrulyEmptyDatabase_TakesBaselinePathNotIncremental
4	✅	Existing non-empty database continues through the incremental path unaffected	Unit test	DatabaseInitializerTests.InitialiseAsync_ExistingDatabaseAtVersion3_StillReplaysRemainingConsumerMigrationsIncrementally
5-6	✅	Baseline schema never silently drifts from the numbered migrations (Data + Engine sides)	Unit test	DataOwnedBaseline_And_IncrementalReplay_ProduceIdenticalSystemAuditEntriesSchema, Baseline_And_IncrementalReplay_ProduceIdenticalEngineSchema/...AcceptSameCheckConstraintValues
7	✅	No baseline defined falls through to full incremental replay	Unit test	ApplyBaselineAsync_NoConsumerBaselineDefined_FallsThroughToIncremental
8	✅	preserveSchemaVersion:true preserves consumer version; Data's own is never touched by Reset	Unit test	ResetAsync_PreserveSchemaVersionTrue_KeepsExistingConsumerVersionRows, ResetAsync_AnyParameter_NeverTouchesDataSchemaVersion
9-10	✅	Build/tests clean	Live	Confirmed 2026-07-03
11-13	✅	T1/T2 — fresh DB baseline creation, default Reset with no exception, identical Docker behaviour	Live	Confirmed 2026-07-03
14	✅	A pre-#143 combined-counter database fails loudly rather than self-healing	Unit test	InitialiseAsync_PreSplitCombinedCounterDatabase_FailsSafelyAndRequiresExplicitReset
15	✅	A genuine failure during Reset's migration replay restores the pre-reset backup and rethrows	Unit test	ResetAsync_MigrationFailsDuringReplay_RestoresPreResetBackupAndRethrows