Schema: ImportBatches table and provenance link on all entity records

[DutchJaFO]

Add an ImportBatches table to track the origin of every record, and a nullable ImportBatchId foreign key on Quotes, Characters, Sources, and People.

Why ImportBatches, not ImportSources

The domain already has a Sources table (film/book/TV sources — "Forrest Gump", "The Matrix"). ImportBatches is unambiguous: it refers to a single import operation, not a media source.

ImportBatches table

CREATE TABLE ImportBatches (
    Id          TEXT    NOT NULL PRIMARY KEY,  -- UUID
    Name        TEXT    NOT NULL,              -- e.g. "vilaboim/movie-quotes", "Manual import 2026-06-16"
    Type        TEXT    NOT NULL,              -- 'seed' | 'import' | 'system'
    Url         TEXT,                          -- nullable; source URL for seed-type batches
    ImportedAt  TEXT    NOT NULL,              -- ISO 8601 UTC
    ImportedBy  TEXT,                          -- actor ID (user UUID, 'system', or provider name)
    RecordCount INT     NOT NULL DEFAULT 0     -- denormalised; updated after each import run
);

Type values:

Type	Scenario
seed	Seeded from an external dataset (vilaboim, NikhilNamal17); has a Url
import	Bulk import via the import endpoint (#45); ImportedBy = user UUID
system	Startup seeding from the bundled quotes.json

Foreign key on entity tables

Quotes, Characters, Sources, and People each gain one nullable column:

ALTER TABLE Quotes     ADD COLUMN ImportBatchId TEXT REFERENCES ImportBatches(Id);
ALTER TABLE Characters ADD COLUMN ImportBatchId TEXT REFERENCES ImportBatches(Id);
ALTER TABLE Sources    ADD COLUMN ImportBatchId TEXT REFERENCES ImportBatches(Id);
ALTER TABLE People     ADD COLUMN ImportBatchId TEXT REFERENCES ImportBatches(Id);

• NULL = manually created via the management UI with no associated import
• Non-null = record arrived via a specific import batch

Schema version bump required; migration sets ImportBatchId = NULL for all existing rows.

Pre-seeded batches for existing data

On migration, insert one ImportBatch row per known seed source so the existing dataset has traceable provenance:

• { Name: "vilaboim/movie-quotes", Type: "seed", Url: "...", ImportedAt: <migration timestamp> }
• { Name: "NikhilNamal17/popular-movie-quotes", Type: "seed", Url: "...", ImportedAt: <migration timestamp> }

Existing records cannot be retroactively linked to a specific batch (the information was not captured). Leave them as NULL — they are effectively "pre-provenance" records.

Connection to existing issues

• Import endpoint: POST /api/v1/quotes/import #45 (import endpoint) — creates one ImportBatch row per import run; links all written records to that batch via ImportBatchId
• Seed script: deduplication strategy is inconsistent and silent #57 (seed script) — the seed script creates one ImportBatch entry per source dataset it processes; links all records from that source to that batch
• Audit log: provenance and change history across all entity types #56 (audit log) — ImportBatches entries complement the audit log: the audit log records field-level changes, ImportBatches records the origin event
• Schema: ImportBatches table and provenance link on all entity records #58 (targeted reset endpoint — see companion issue) — uses ImportBatchId to scope deletions

Notes

• RecordCount is denormalised for display performance in the UI; updated after each import and after targeted resets
• The ImportBatches table is NOT included in the export endpoint (Export endpoint: GET /api/v1/quotes/export #47) — it is instance-specific provenance data