Reset: use the fresh-database baseline script instead of drop-all-user-tables + replay

[DutchJaFO]

Problem

DropAndRebuildAsync (src/Quotinator.Data/Database/DatabaseInitializer.cs) implements Reset by:

1. Discovering "user tables" via Sql.Schema.GetUserTables (a query that has to know which tables are ordinary domain tables vs. System_-prefixed tables to exclude)
2. DROP TABLE IF EXISTS on every discovered table
3. Replaying the full incremental migration history via ApplyMigrationsAsync(skipOwnBackup: true) to rebuild the schema

Since #143, there is already a one-step consolidated baseline path for genuinely empty databases: DataBaselineSql (Quotinator.Data's own tables) followed by the consumer's SchemaBaseline.Sql (QuotinatorMigrations.Baseline), gated on Sql.Schema.AnyTableExists. Reset does not use this path at all — it always takes the incremental replay branch, because System_SchemaVersion/System_ConsumerSchemaVersion bookkeeping means the database is never "empty" by that check's definition once at least one table survives.

Separately, QuotinatorDatabaseInitializer.OnResetAsync (src/Quotinator.Engine/Database/QuotinatorDatabaseInitializer.cs) calls DropAndRebuildAsync and then immediately calls SeedIfEmptyInternalAsync(connection, effectiveBatches) — so today's Reset always reseeds bundled/imported source data (Quotes, Sources, Characters, People, Genres) as its second step. Reset is not currently "just" a schema rebuild.

Proposal

Two changes to Reset, both in scope for this issue:

1. Use the baseline script, not drop-all-tables + replay. Make Reset drop the entire database (all tables, no GetUserTables exclusion list to maintain) and recreate it in one step via the same baseline script already used for fresh installs, rather than maintaining a growing exclusion list of "tables Reset must not touch" and replaying migration history table-by-table.

   This means Reset would no longer preserve System_-prefixed audit-trail tables (System_AuditEntries, System_ImportConflicts, etc.) — including audit history. That is a deliberate tradeoff: Reset should be treated as a nuclear, all-data-gone operation, not a selective one. This directly reverses the "System_ tables always survive Reset" behavior documented in CLAUDE.md's "No exception-based migration recovery" section and exercised by tests like ResetAsync_AfterInitialise_PreservesExistingAuditEntries. This consequence requires Export audit-trail tables to a dedicated folder before a destructive Reset #249 (export audit-trail tables before a destructive Reset) to ship in the same release as this issue — see "Relationship to existing issues" below.

   This is Reset-only. Reseed (TruncateDataAsync, src/Quotinator.Data/Database/DatabaseInitializer.cs:276) is confirmed to be plain row-level DELETEs against named domain tables — it never drops or rebuilds schema at all, so there is no baseline-script question for Reseed; it is a structurally separate mechanism and is unaffected by this issue.

2. Reset must not reseed non-system tables afterward. Remove the SeedIfEmptyInternalAsync call from OnResetAsync. Reset should leave the database at the empty baseline schema — no bundled/imported quotes, sources, characters, people reimported automatically. Today Reset silently does two things (rebuild schema + reseed data); after this change it does exactly one (rebuild schema to empty). This is now a general project rule, not just this issue's own call — see CLAUDE.md's "Endpoint side-effect policy (Single Responsibility)" (added while planning Should System_-prefixed provenance tables purge rows referencing Reset-wiped entities? #151/ADR 014): an endpoint must not bundle an automatic side effect that silently makes a data-retention decision on the caller's behalf. Bundled quote content is optional, discardable domain data, not fixed reference data — a user resetting the database to start fresh with their own content should never be forced to re-accept the bundled dataset every time.

   Bundled-source seeding stays a fresh-database-only operation. SeedIfEmptyAsync/SeedIfEmptyInternalAsync (loading data/sources/*.json and any configured imports) continues to run only from OnInitialisedAsync — i.e. only when the app starts up against a database that has just been created for the first time. Reset never calls it. This was already the effective behavior for fresh installs; this issue does not change that path, only removes Reset's own separate call into it.

   Exception: pre-loaded system/reference tables, if any exist, are expected to survive Reset — via the baseline, not via a reseed step. If Reset is expected to leave such tables populated, that is a property of the baseline SQL itself (its CREATE TABLE + static INSERT statements), not a runtime reseed call — the same baseline runs identically on fresh-create and on Reset, so anything the baseline inserts is present after either. No separate "is this a system table" branch is needed at Reset time. This is not a caller-facing opt-in/opt-out — per the Single Responsibility policy above, an endpoint never grows a request parameter letting the caller toggle which data survives; a table either structurally needs fixed content (baseline provides it unconditionally) or it doesn't (Reset never reseeds it, unconditionally).

   Today, no such tables exist. Neither Quotinator.Data nor Quotinator.Engine currently defines any pre-loaded, enum-like reference/lookup table — e.g. genres are currently a free-text column with a CHECK constraint, not a lookup table with rows. So in practice, once this issue ships, a manual Reset produces a database with literally zero rows in every non-System_-audit-trail table (no quotes, no sources, and also no reference data, because there is none to have). This is expected, not a gap: the "except for system tables that are seeded" exception has nothing to except right now. If a genuine reference/lookup table is introduced later (see the pending genre-extensible-table idea in project memory — not yet scoped), its fixed rows would be added directly to the baseline SQL at that time, and it would then survive Reset automatically under the rule above, with no changes needed to Reset itself.

Why

• Removes the need to classify every current and future table as "droppable" vs. "protected" — GetUserTables' exclusion logic is exactly the kind of fragile, easy-to-forget list this avoids (miss updating it when adding a new System_-prefixed table and Reset either fails or silently drops something it shouldn't).
• The baseline script is already the source of truth for "what does a correct fresh schema look like" (enforced by the schema-drift tests in docs/database-conventions.md/CLAUDE.md) — reusing it for Reset means there is only one code path that defines "empty database," not two that must be kept in sync.
• Simpler mental model for operators: Reset = start over completely and land on an empty database (plus whatever fixed reference data the schema itself defines), take a backup first (already implemented) if you need the old data. Whether to reseed bundled/imported quote content afterward becomes the operator's explicit next step, not something Reset decides on their behalf.
• Matches the Single Responsibility endpoint-design rule now written down in CLAUDE.md: Reset's one job is rebuilding the schema; whether to also repopulate optional bundled content is a separate concern the endpoint must not silently decide.

Confirmed feasible (verified against current code, no open design question)

• Reseed is unaffected by design, not by choice — confirmed above via TruncateDataAsync.
• The baseline path already leaves the database correctly versioned. ApplyBaselineAsync (src/Quotinator.Data/Database/DatabaseInitializer.cs:485-506) inserts both the System_SchemaVersion row (InsertDataVersion) and the System_ConsumerSchemaVersion row (InsertConsumerVersion) in the same transaction as the baseline DDL. Reusing this path for Reset requires no new version-bookkeeping — a Reset'd database will read as "fully migrated" immediately, same as a fresh install does today.

Relationship to existing issues

• Reseed/reset must preserve System-classified data #141 ("Reseed/reset must preserve System-classified data") established the current preserve-on-reset behavior this issue proposes reversing. If this issue proceeds, Reseed/reset must preserve System-classified data #141's outcome needs to be revisited/superseded, not silently ignored.
• Should System_-prefixed provenance tables purge rows referencing Reset-wiped entities? #151 ("Should System_-prefixed audit-trail tables purge rows referencing Reset-wiped entities?") is now resolved via ADR 014: dangling references within a surviving audit-trail table are permanent by design (relevant to Reseed and to Reset before this issue ships). ADR 014 also confirms that once this issue ships, the audit-trail tables stop surviving Reset entirely — a bigger loss than "dangling rows" — which is what makes Export audit-trail tables to a dedicated folder before a destructive Reset #249 relevant to this issue specifically (see below).
• Export audit-trail tables to a dedicated folder before a destructive Reset #249 ("Export audit-trail tables to a dedicated folder before a destructive Reset") is a release-level gate on this issue, not an implementation-order dependency. Per ADR 014, a tagged release must never ship this issue's destructive-Reset behaviour without Export audit-trail tables to a dedicated folder before a destructive Reset #249's export path already present in that same release. Export audit-trail tables to a dedicated folder before a destructive Reset #249 and this issue can be designed, built, and merged in either order or in parallel.

Known implementation impact (not open questions — just work to do when this is picked up)

• Existing tests asserting System_ tables survive Reset (e.g. ResetAsync_AfterInitialise_PreservesExistingAuditEntries) must be rewritten to assert the opposite, in the same commit as the behavior change.
• Existing tests/callers that assume Reset leaves a populated database (anything asserting quote/source counts after a Reset call) must be found and updated to expect an empty database instead.

Out of scope

Implementing the change itself — this issue is to track the decision and design; no code changes are proposed here.

[DutchJaFO]

Shipped in v1.8.0 and confirmed still in place on current `main`.

#	Status	Requirement	Method	Verification
1	✅	`SeedSystemContentAsync` hook exists, no-op by default, called from `ApplyBaselineAsync` and `DropAndRebuildAsync`	Build	`dotnet build --configuration Release` — 0 warnings, 0 errors; full `dotnet test` — 609/609 passed
2	✅	`data/system/` directory exists, documented, in `Quotinator.slnx`	Manual	`data/system/README.md` present; `Quotinator.slnx` lists it
3	✅	System content survives a fresh baseline-path initialise	Unit test	`SystemReseedConceptTests.SeedSystemContentAsync_AfterFreshInitialise_PopulatesSystemContentTable`
4	✅	System content is (re)populated after `ResetAsync`	Unit test	`SystemReseedConceptTests.SeedSystemContentAsync_AfterReset_RepopulatesSystemContentTable`
5	✅	Standard-reseed-equivalent call does not touch system content	Unit test	`SystemReseedConceptTests.ReseedEquivalentCall_DoesNotInvokeSeedSystemContentAsync`
6	✅	A downstream consumer (not just Quotinator.Data itself) can register system content via the same extension point	Unit test	`UserSystemReseedConceptTests.SeedSystemContentAsync_AfterFreshInitialise_PopulatesUserContentTable` + siblings
7	✅	Reset drops the entire database and rebuilds via baseline	Unit test + Live	`ResetAsync_AfterInitialise_WipesExistingAuditEntries`, `ResetAsync_WipesExistingChangeLogRowsAndDoesNotReseed`, `GetAllTables_ReturnsEveryTableRegardlessOfPrefix`; live Docker: audit `totalCount` 32→1 (self-trace only), quotes 799→0
8	✅	Reset no longer reseeds standard (bundled/user) content	Unit test + Live	`ResetAsync_AfterInitialise_RebuildsSchemaAndDoesNotReseed`; live Docker: `POST /admin/database/reset` returns all-zero counts, `reports:[]`, `/quotes/random` → `200 NoResults`
9	✅	`ResetAsync_AfterInitialise_PreservesExistingAuditEntries` rewritten to assert the opposite	Unit test	`ResetAsync_AfterInitialise_WipesExistingAuditEntries`
10	✅	T1 (Visual Studio, developer)	Live	Developer's own VS run, 2026-08-06: reset requested → rebuilding schema from baseline → stats all zero → reset complete, `200`
11	✅	T2 (Docker)	Live	Full smoke-test §32 sequence run against `quotinator:local`; found and fixed the `LastSeedReport` staleness bug live

Retroactive close — this shipped in v1.8.0 but was never formally closed.