Source snapshot contract

This contract states what the migration loader enforces for CSV, SQLite, and PostgreSQL across inventory (including --dry-run) and extraction. It is aligned with migration/run_migration.py and the private run-manifest rules.

Evidence classes used below:

• Loader-enforced: checked or configured in migration/run_migration.py (and fail closed when stated).
• Platform open-mode behavior: follows the source engine's documented URI/session semantics once the loader sets those flags; not independently proven by hashing or version markers.
• Operator process: required for row-level consistency; not verified by the loader.

The loader performs full table replacement (write_disposition="replace"). It does not open a transactionally consistent multi-table row snapshot, and it does not prove that source rows stayed unchanged between inventory and load. Manifest fields named source_schema_snapshot / schema fingerprints are schema and mapping identity only (ordered names and, for SQL, normalized type families)—not a stable source-data version.

Supported sources

CSV

• Loader-enforced: UTF-8 headed CSV (BOM-tolerant via utf-8-sig), one entity per file. The filename stem becomes the Bronze table name.
• Loader-enforced: Inventory, extraction, and identity metadata use the same discover_csv_files policy.
• Loader-enforced: Before yield, extraction recomputes the name-level schema fingerprint from the opened header and stops with source_schema_changed_since_preflight if it differs from inventory. That check covers ordered headers only, not file bytes or row values.
• Loader-enforced (identity only): Manifest identity is labeled pre_extraction_file_metadata: filename and filesystem size captured before extraction. Sizes may change before the file is read; no content hash is recorded. Filenames and sizes are identifying metadata—keep manifests private.
• Operator process: Freeze approved input files for the run if byte- or row-level stability matters.

SQLite (mode=ro&immutable=1)

• Loader-enforced: Only an existing regular database file is accepted (sqlite + pysqlite). In-memory sources and URL query overrides fail closed.
• Loader-enforced: The CLI opens the file through SQLite URI mode=ro&immutable=1, verifies PRAGMA query_only=ON, and applies a cooperative 60-second statement progress deadline (see _SQL_STATEMENT_TIMEOUT_MS). The same engine is reused for inventory, preflight, and dlt extraction.
• Platform open-mode behavior: Per SQLite URI rules, immutable=1 tells SQLite to assume the file will not change and therefore not create WAL/SHM sidecars. That is an open-mode assumption, not a loader-verified content freeze or OS lock against external writers. Do not point it at a database that is changing during the run.
• Operator process: Treat the file as an operator-prepared copy: freeze or copy it before inventory, keep that copy stable through extraction, and record how it was produced outside the manifest. The loader does not compare file bytes or a source version marker.
• Loader limit: The progress handler may not interrupt a driver blocked in I/O; single-row materialization is not bounded by the statement deadline.

PostgreSQL

• Loader-enforced: Only postgresql+psycopg:// is accepted. Multi-host URLs and caller options / connect_timeout overrides fail closed.
• Loader-enforced: Connections set and verify default_transaction_read_only=on, a 60-second statement timeout, a 10-second lock timeout, and a 10-second libpq connect timeout. Use a least-privilege SELECT-only role and prefer base tables.
• Loader-enforced: Empty-source preflight is a bounded SELECT 1 … LIMIT 1 per selected table, not a full COUNT(*). Extraction row counts are application-counted yielded rows, not an independent proof of source size.
• Loader-enforced (negative): The loader does not set repeatable read, does not hold one transaction across inventory → preflight → extraction, and does not capture a PostgreSQL snapshot/export marker. A table can change between those steps.
• Operator process: Agree a maintenance window or platform-native snapshot/export with the source owner when consistency matters; verify that marker through the platform's own controls, then extract from that frozen source. Never place raw snapshot IDs in manifests.

Inventory vs extraction

Claim	CSV	SQLite	PostgreSQL	Evidence class
Schema fingerprint binding (names; SQL also typed families)	Yes; rechecked on open header	Yes; rechecked on reflected metadata before dlt extract	Yes; rechecked on reflected metadata before dlt extract	Loader-enforced
Row-level / byte-level freeze across inventory → extract	No	No; operator must keep the opened file unchanged (not verified)	No	Operator process / absent
Manifest proves exact source version loaded	No	No	No	Absent

Schema and mapping fingerprints authorize a reviewed shape; they are not anonymization and do not cover constraints or data values. For SQL, fingerprint recheck pins the reflected column set passed into dlt; it still does not freeze row values for the subsequent SELECT. SQL inventory may incur source query cost for the existence probe; CSV inventory scans files for exact row counts in dry-run reports (real-load inventory skips CSV row counting).

Source mutation decision matrix

Source	Mutation the loader can detect	Mutation it cannot reliably detect	Snapshot claim and operator decision
CSV	An ordered header change visible when extraction opens a file fails closed against the inventory schema fingerprint.	Row edits with the same header; replacement or edits after the pre-extraction filename/size metadata is captured; and changes while an opened file is being read. File size is not a content fingerprint.	No byte- or row-level freeze is loader-guaranteed. Freeze the approved input files for the run, or record that concurrent changes were accepted.
SQLite	A schema change visible during reflected-metadata recheck fails closed against the inventory fingerprint. Read-only + query_only prevent writes on this connection; immutable=1 avoids WAL/SHM sidecars under SQLite's assumption that the file is static.	A changed database file or row contents are not compared against a saved content/version marker. Immutable mode assumes the file will not change externally; it does not prove that assumption.	No loader-verified row snapshot. Treat a stable, operator-prepared copy as the operational snapshot; freeze it from inventory through extraction and record how it was produced outside manifests.
PostgreSQL	A schema change visible during reflected-metadata recheck fails closed against the inventory fingerprint.	Row inserts, updates, or deletes between inventory, existence preflight, and extraction. Read-only settings and statement/lock timeouts do not create one repeatable snapshot across those steps.	No transactionally consistent source snapshot is loader-guaranteed. Arrange a maintenance window or use a platform-native snapshot/export and verify it through that platform's controls.

Choose the row that matches the source and record the operator decision in a restricted operational record. None of these checks proves that a manifest corresponds to an exact set of loaded row values. Keep snapshot identifiers and source values out of the manifest.

Operator records (outside manifests)

Retain in a restricted operational record (ticket, change log, or source-platform audit)—not in migration manifests:

• Snapshot or export identifier / restore point when the platform provides one, plus who verified it and when.
• Maintenance-window agreement when no reliable marker exists, including owner, window, and concurrent-write expectation.
• For SQLite: how the read-only file copy was produced (copy, backup restore) and the path handled under local secret/path policy.
• For CSV: which directory/glob was approved and whether files were frozen for the run.
• Post-run notes that inventory/extraction ran against that agreed boundary (or that concurrent change risk was accepted).

Must not enter manifests

Private run manifests, reconciliation reports, evidence indexes, and acceptance reports must not contain:

• Credentials, passwords, tokens, or connection URLs (MIGRATION_SOURCE_URL stays in the environment; SQL connection_identity is always redacted).
• Raw platform snapshot, backup, or export identifiers.
• Local absolute source paths (CSV identity stores file names and sizes only; SQL stores schema/table names under descriptive_names_not_unique).
• Row values, rejected payload contents, or exception text that could echo source data (failure records keep table, ordinal when available, reason code, and accepted counts only).

Manifests may still hold identifying operational metadata (entity names, pre-extraction sizes, schema/mapping fingerprints, target/namespace identifiers). Keep the migration/run-manifests/ tree private and owner-only.