Audit log: provenance and change history across all entity types

[DutchJaFO]

Add a reusable audit log that tracks who changed what, when, and via which source — across quotes, characters, sources, and persons — without adding columns to those core tables.

Why a separate table

The core tables (Quotes, Characters, Sources, Persons) must stay simple. Audit data grows without bound and has different access patterns (append-only, queried by entity, never updated). A single polymorphic AuditLog table handles all entity types uniformly and can be extended to new entity types with no schema changes.

Table schema

CREATE TABLE AuditLog (
    Id          TEXT NOT NULL PRIMARY KEY,   -- UUID
    EntityType  TEXT NOT NULL,               -- 'quote' | 'character' | 'source' | 'person'
    EntityId    TEXT NOT NULL,               -- UUID of the affected record
    ActorType   TEXT NOT NULL,               -- 'user' | 'enrichment' | 'import' | 'system'
    ActorId     TEXT,                        -- user UUID, provider name ('tmdb'), import batch UUID, or null for system
    Action      TEXT NOT NULL,               -- see action vocabulary below
    Field       TEXT,                        -- nullable; field name for field-level actions
    OldValue    TEXT,                        -- nullable; previous value serialised as string
    NewValue    TEXT,                        -- nullable; new value serialised as string
    OccurredAt  TEXT NOT NULL                -- ISO 8601 UTC
);

CREATE INDEX IX_AuditLog_Entity ON AuditLog (EntityType, EntityId, OccurredAt DESC);

Action vocabulary

Action	Trigger
created	Record first written (manual, import, or seed)
updated	Field changed by a user
enriched	Field filled by an enrichment provider
completed	isComplete set to true — signed off (#55)
verified_absent	Field added to noValueKnown — confirmed no value exists (#55)
imported	Record written via bulk import (#45); ActorId = import batch UUID

Actor model

ActorType	ActorId	Scenario
user	User UUID	Manual edit in the management UI
enrichment	Provider name (tmdb, wikidata, etc.)	Enrichment provider filled a field
import	Import batch UUID	Bulk import (#45); batch UUID groups all rows from one import run
system	null	Startup seeding, migrations

The actor model is intentionally forward-compatible: it works before users exist (auth #15) by using ActorType='system' or ActorType='import' as the actor.

Reusable service interface

public interface IAuditLogger
{
    Task LogAsync(AuditEntry entry, CancellationToken ct = default);
    Task<IReadOnlyList<AuditEntry>> GetHistoryAsync(string entityType, string entityId, CancellationToken ct = default);
}

Injected wherever writes happen: write endpoints (#16), import endpoint (#45), enrichment service (#19). All audit writes are fire-and-forget from the caller's perspective — a failed audit write must never roll back the primary operation.

Connections to existing issues

• Schema: record completeness flag and per-field verified-absent markers #55 (completeness flags) — completed and verified_absent actions record who set those flags and when
• Enrichment script: fill missing quote fields using TMDB, Open Library, and Wikidata #19 (enrichment scaffold) — providers call IAuditLogger after filling each field; ActorType='enrichment', ActorId= provider name
• Import endpoint: POST /api/v1/quotes/import #45 (import endpoint) — each import run generates a batch UUID; all rows in the batch share the same ActorId; a single imported entry is written per record
• Auth: API key authentication for write endpoints and MCP #15 (auth) — once users exist, ActorType='user' entries carry a real user UUID; before auth lands, system/import actors are sufficient

Blazor UI: audit history panel

Each entity detail page (quote, character, source, person) gains a collapsible History tab:

• Entries sorted by OccurredAt desc
• Shows: timestamp, actor (user display name or provider name), action, field, old → new value
• completed and verified_absent entries are visually distinguished (e.g. a checkmark badge) — these are sign-off events, not just edits

API

GET /api/v1/quotes/{id}/history (and equivalents for other entity types) — returns the audit log for a single record. Paginated. No auth required for read; write endpoints require auth as usual.

Notes

• Audit log is append-only — no UPDATE or DELETE on AuditLog rows
• OldValue / NewValue are stored as strings; complex fields (e.g. genres array) are serialised as JSON
• The audit log is not included in the export endpoint (Export endpoint: GET /api/v1/quotes/export #47) — it is instance-specific provenance data, not portable quote content

[DutchJaFO]

Scope reconciliation before implementation

This issue's original schema/vocabulary was reconciled across several rounds with the developer before any code was written. Recorded here for the record, matching how #55's reconciliation was posted before its own implementation.

Renamed throughout — AuditLog/ActorType → System_ChangeLog/InitiatedByType:

• ActorType/ActorId would collide with a plausible future Actor table in Quotinator's own domain (the performer who played a Character, distinct from Person). Renamed to InitiatedByType/InitiatedById.
• AuditLog as a name would sit confusingly close to the already-shipped System_AuditEntries once both are Data-owned siblings. Renamed to System_ChangeLog (entity ChangeLogEntry, interfaces IChangeLogWriter/IChangeLogReader).

System_ChangeLog is Data-owned and System_-prefixed, not Engine-owned as first proposed. System_AuditEntries/System_ImportConflicts are already generic, Data-owned tables with free-text entity/operation columns — no hardcoded enum of Quotinator's specific tables. System_ChangeLog follows the identical shape, so it follows the identical ownership rule: Data-owned, System_-prefixed, survives a full Reset (matching System_ImportConflicts's already-shipped behavior of surviving Reset even though the quotes it references are wiped and reseeded with new IDs).

Action vocabulary replaced. The issue's created/updated/enriched/completed/verified_absent/imported mixed CRUD shape with business semantics. Final vocabulary is a closed, generic ChangeAction enum: Created, Modified, SoftDelete, HardDelete. Who/what caused it lives entirely in a separate closed InitiatorType enum: Seed, Import, WriteEndpoint, Enrichment (+ a free-text InitiatedById for the specific batch/route/provider). What the issue called enriched is just Modified + InitiatedByType=Enrichment; completed/verified_absent (#55) will be Modified + InitiatedByType=WriteEndpoint once #55's management UI exists — no dedicated action value needed for either.

Granularity: one row per entity per operation, not one row per changed field. OldValue/NewValue are whole-record JSON snapshots, reusing the same field maps already computed for System_ImportConflicts logging.

This issue wires real logging into the write paths that exist today — startup seeding (InitiatorType.Seed) and the live import endpoint #45 (InitiatorType.Import), including Modified rows for newest-wins/merge-ours/merge-theirs rewrites (not just new inserts) — rather than shipping the interface as inert infrastructure with zero real callers. GET /api/v1/quotes/{id}/history and the Blazor history panel are deferred — nothing in this milestone consumes them yet.

Follow-up filed separately: whether System_-prefixed provenance tables (System_AuditEntries, System_ImportConflicts, System_ChangeLog) should purge rows referencing an entity a Reset just wiped, rather than blanket-surviving as all three do today — a cross-cutting question about already-shipped behavior, not resolved here.

Full detail: docs/milestones/data-import-sources/56-audit-log-plan.md.

[DutchJaFO]

Released in v1.8.0.

#	Status	Requirement	Method	Verification
1	✅	System_ChangeLog table + index created, baseline matches incremental replay, CHECK constraints enforced identically	Unit test	DataOwnedBaseline_And_IncrementalReplay_ProduceIdenticalSystemChangeLogSchema, ...AcceptSameChangeLogCheckConstraintValues
2-4	✅	Created/Modified entries logged correctly during seeding and live import, with correct initiator	Unit test	ConflictResolutionTests.Seed_FreshQuote_WritesCreatedChangeLogRowsWithSeedInitiator and siblings; QuoteImportServiceTests.ImportAsync_FreshDatabase_WritesCreatedChangeLogRowWithImportInitiator and siblings
5	✅	System_ChangeLog survives a full Reset	Unit test	ResetAsync_PreservesExistingChangeLogRows
6	✅	IInitiatorContext/InitiatorContext isolate per async context	Unit test	InitiatorContextTests.ConcurrentAsyncFlows_DoNotSeeEachOthersInitiatorValues
7-11	N/A	WriteEndpoint/Enrichment initiators, SoftDelete/HardDelete actions, history endpoint, Blazor UI panel	N/A	Deferred to #16/#19/v3
12	✅	SystemAuditEntry/SystemImportConflict/SystemChangeLog all inherit RecordBase (ADR 002)	Unit test	Schema-drift tests for all three tables
13-14	✅	Both already-shipped and already-applied-locally tables migrate safely to the new shape, preserving existing rows	Unit test + Live	Confirmed via a copy of a real dev database with migration 3 already applied
15-16	✅	T1/T2 — VS and Docker smoke tests, migration applies cleanly	Live	Confirmed