Move changelog content to database-backed System_Changelog table #309

Background

Implements ADR 005's
revision and ADR 018's
file-authored system content pattern. Changelog content currently lives entirely in
src/Quotinator.Api/resources/changelog.*.json, compiled into the image — updating it requires a code
commit and a redeploy. This issue moves changelog content to a database-backed, queryable store,
refreshed at startup from the same JSON files relocated to a runtime-accessible location, while keeping
the existing JSON-based read path alive as a fallback.

Revision (2026-08-13) — master/detail, not a JSON blob. Rich content (highlights, added,
changed, fixed, removed, audienceHighlights, issues, cves) is modeled as rows in a single
Kind-discriminated child table (ChangelogLine), not serialized into one JSON blob column — reuses
this project's own AggregateRepository<TParent, TChild> master/detail infrastructure (#75), makes
individual entries directly updatable and queryable via SQL instead of requiring a full-blob rewrite.

Revision (2026-08-13) — separate database, not a table in quotinatordata.db. Per ADR 018's
revision: changelog content has no transactional coupling and no relational link to domain data (unlike
a future Genre-as-table move, which stays in the main database because of its QuoteGenres link), so
this content gets its own SQLite database rather than a System_-prefixed table in the main one — keeps
the main database free of non-domain content, lets the two be updated independently. Default storage
mode is in-memory (shared-cache SQLite, recreated fresh every startup from the JSON files). The
connection string is a single configuration point, so a persistent-file variant can be added later
without a redesign if re-importing every startup turns out to be costly (not built now).

Revision (2026-08-13) — full migration capability, not none. A database recreated fresh every boot
still gets the same migration capability every other database in this project has — a content type
having no current reason to change its schema doesn't mean it never will. This database gets its own
baseline+incremental migration machinery (own migration list, own baseline SQL, own schema-drift parity
test), following Quotinator.Data.Database.DatabaseInitializer's existing pattern rather than reusing
that class directly — see the "Correction" note below. In practice, the in-memory storage mode always
starts genuinely empty, so the "empty database → apply the baseline" rule means every boot takes the
baseline path regardless — the incremental path exists and is tested for when the schema actually
changes (development, or a future persistent-file variant), not exercised by default, but never skipped
as infrastructure.

Scope addition (2026-08-13) — About-page fallback. /about is already on
DatabaseHealthGateMiddleware's exempt-path list — expected to stay reachable during a degraded
database. #293 already fixed the identical failure mode for System_Notification. This issue's
database is separate from the one #293 concerns, but the same failure shape applies if the changelog
import itself fails and its tables never get created. The fallback logs clearly when it triggers
(matching #293's own improved message quality bar) but does not build a user-visible warning UI —
that's #305's job (general DB integrity detection, v1.9.0), not duplicated here.

Correction (2026-08-14, found during implementation) — a new, independent ChangelogDatabaseInitializer
class, not a second instance of DatabaseInitializer. That class hardcodes the main database's own
migration list and baseline SQL as private fields, not constructor parameters — a second instance would
have tried to create Import_Batch/Audit_Entry/System_Notification/etc. in the changelog database
too. ChangelogDatabaseInitializer instead follows the same baseline-vs-incremental pattern
independently. Full reasoning and two unrelated naming-collision incidents found along the way are
recorded in the plan doc's Step 5 notes.

Revision (2026-08-16) — one ADR added to scope; nothing built here is wrong. This issue made
Quotinator the first application in the project with more than one database, and no ADR states what
table-naming rules apply in that situation. ADR 015
answers only for a single namespace — its whole rationale is SQLite's lack of schema qualification
within one — so it neither grants nor denies the unprefixed names this issue's separate database uses.

The gap surfaced while planning #319, when a reader took Changelog/ChangelogLine/
ChangelogSchemaVersion for ADR 015 drift and filed it as a defect. It is not one: ADR 018's "Database
placement" section decides the separation, and ChangelogContentMigrations' own class doc states the
naming consequence. But the rule that makes those names correct is not written down anywhere a reader
would look first, which is why the misreading happened at all.

Two clarifications from the developer (2026-08-16) frame what the ADR has to settle: the changelog
database is effectively a second user-domain database, not a system one, so no existing domain
prefix describes it; and ADR 005 is not wrong — its System_Changelog naming was correct under the
one-database assumption in force when it was written, and simply did not account for an application
having more than one.

The ADR decides the rule and renames nothing — concluding the current names are correct is a valid
outcome. This issue returns from Waiting for release to In progress for it. Every previously
verified requirement stays green; no code changes.

What needs to be done

1. New Quotinator.Data.Connections in-memory shared-cache SQLite connection factory
   (Data Source=file:quotinatorchangelog?mode=memory&cache=shared) plus a singleton keep-alive
   connection held open for the app's lifetime (a shared-cache in-memory database is destroyed when its
   last connection closes) — registered via keyed DI (AddKeyedSingleton<IDbConnectionFactory> ("changelog", ...)), this project's first use of keyed services.
2. ChangelogDatabaseInitializer (its own small class, not a second DatabaseInitializer instance:
   migration 1, baseline SQL, schema-drift parity test) creates Changelog/ChangelogLine (no
   System_ prefix — a dedicated single-purpose database has nothing to disambiguate from). Changelog
   = one row per (Language, Version), Version IS NULL for that language's unreleased entry, plus a
   MachineTranslated column found missing during implementation (About.razor renders it). ChangelogLine
   = one row per list item across every list-shaped field, discriminated by Kind (CHECK-constrained
   enum: Highlight/Added/Changed/Fixed/Removed/Issue/Cve/AudienceHighlight), with a
   nullable AudienceKey populated only for AudienceHighlight rows (this is where Changelog highlights: mark specific entries as notification-worthy #307's
   "notification" convention plugs in) and SortOrder to preserve list order.
3. Quotinator.Data takes a new project reference on Quotinator.Changelog (ADR 018) to parse the
   relocated JSON files via its existing IChangelogService/models.
4. Relocate the changelog.*.json files from src/Quotinator.Api/resources/ to data/changelog/
   (new top-level folder, sibling to data/sources/), mirroring exactly how bundled quote sources are
   already read from AppContext.BaseDirectory/data/sources/.
5. New ChangelogSystemContentImporter — reads the JSON files via IChangelogService, flattens each
   release's list fields into ChangelogLine rows, writes via AggregateRepository<Changelog, ChangelogLine>.InsertAsync. Runs once at startup after the tables are created, as a detached
   background task (found during implementation: awaiting it inline delayed the app's own
   startup-complete signal enough to break an existing regression test).
6. New IChangelogReader/ChangelogReader (Quotinator.Data.Repositories) — reads via a
   JoinQueryRepository/IJoinStrategy LEFT JOIN (per ADR 017, never a hand-rolled query), reassembling
   flat rows back into ChangelogRelease/ChangelogUnreleased objects. Falls back to the existing
   IChangelogService (JSON-based, zero DB dependency) when the tables are missing, empty, or a read
   fails. Follows NotificationSummary/notifications crash instead of degrading gracefully when System_Notification doesn't exist yet #293's exact narrow-exception-catch idiom and logs a warning when the fallback triggers.
7. About.razor reads through IChangelogReader, not IChangelogService directly. (Startup notification: import warnings and what's new after upgrade #81's future
   producer will do the same when it's built.)
8. A new ADR stating the table-naming and ownership rules for an application with more than one
   database (added 2026-08-16 — see the Revision above), listed in Quotinator.slnx in the same
   commit. It must account for: the changelog database being a second user-domain database, so
   neither System_ nor an existing Quotinator.Data domain describes it; ADR 015's rationale being
   about disambiguation within one namespace, which a single-domain database does not have; ADR 015's
   second stated purpose still applying, since Sql.cs spans both databases and a reader should be
   able to tell table ownership from a name alone; and ADR 018's "Database placement" section already
   deciding when a separate database is used, which the new ADR references rather than restates.
   Doc-only — no code or schema change.

Expected tests

Test class/method names above were corrected to match what was actually built (documented in the plan
doc); GetDocumentAsync_DatabaseEmpty_FallsBackToFileService was added beyond the original list to
cover a real code path (schema exists, background import not finished yet) found during implementation.
The Starts column is left as originally filed — tests here were written alongside their
implementation rather than tracked individually through a captured red phase, so this project's own
red-green discipline wasn't formally exercised test-by-test this time; noting that honestly rather than
retroactively marking it done.

Definition of done

• All expected tests listed above start red before implementation
• All requirements implemented
• All expected tests pass (green)
• No regression in related tests
• Findings summarised in a closing comment

Full verification detail (including live Docker/T1 confirmation and why one originally-planned T2
Docker fault-injection test was replaced with unit coverage instead) is in the plan doc:
309-system-changelog-table-plan.md.

Every verification row covering the code was ✅ as of 2026-08-14, and this issue reached
Waiting for release then. It returned to In progress on 2026-08-16 when the ADR above was added
to scope; one verification row (the ADR existing and being listed in Quotinator.slnx) is outstanding.
No code changed, and nothing previously verified was found wrong. Closing still happens only after it
ships in a tagged release, per this project's own phase model.