Epic: Migration Write Correctness and Non-Empty Database Safety
Summary
Fix write-mode migration so it persists to the intended domain tables, behaves deterministically on rerun, and safely handles non-empty databases without hidden data loss or duplication.
Why This Epic
Current behavior indicates write mode does not materialize data into the expected tables. This is a release blocker.
Non-empty database handling is also undefined. Without a clear contract, reruns can create duplicate records, overwrite valid state, or produce ambiguous review output.
Goals
- Ensure write mode persists validated migration output to canonical target tables.
- Guarantee dry-run and write-run parity for validation and reconciliation logic.
- Define and enforce non-empty database behavior for append, merge, or replace policies.
- Preserve idempotency by source checksum and run scope.
Non-Goals
- Reworking scoring formulas in this epic.
- Redesigning the entire migration UX.
- Supporting every historical source format immediately.
Decision Topics
- Non-empty DB mode contract:
- Append-only snapshots
- Merge/upsert into active records
- Replace within run-scoped partition
- Conflict policy for overlapping participant/race/question records.
- Activation model: write immediately active versus write then explicit promote.
User Stories
Story 1: Trace and document current write-run pipeline gaps
As an engineer, I want an implementation-level trace so no-op write points and table mismatches are explicit.
Story 2: Implement transactional write path to canonical tables
As an operator, I want write runs to persist all intended entities atomically so partial writes cannot corrupt state.
Story 3: Enforce dry-run and write-run parity
As a reviewer, I want the same validation and reconciliation outcomes regardless of mode so sign-off is trustworthy.
Story 4: Add idempotent upsert semantics for reruns
As a platform maintainer, I want repeat writes of the same source checksum to avoid duplicates and drift.
Story 5: Define and enforce non-empty database strategy
As a team lead, I want an explicit policy for existing data so migrations are predictable in live-like environments.
Story 6: Add duplicate detection and conflict reporting
As an operator, I want clear conflict diagnostics when incoming migration rows overlap existing canonical rows.
Story 7: Add safe rollback and compensation for failed writes
As an operator, I want recovery procedures for failed write runs so bad data can be reverted quickly.
Story 8: Add integration tests for write correctness on empty and non-empty DBs
As a QA engineer, I want automated proof that write mode behaves correctly across both scenarios.
Delivery Plan
- Pipeline trace and target-table mapping
- Transactional write implementation
- Non-empty DB policy and conflict handling
- Idempotency and rollback mechanics
- Integration and end-to-end validation
Risks and Mitigations
-
Risk: Hidden legacy assumptions break when enforcing canonical writes.
-
Mitigation: Introduce migration compatibility checks and staged rollout flags.
-
Risk: Non-empty DB policy causes unintended user-visible changes.
-
Mitigation: Require explicit mode selection with confirmation and run preview.
Definition of Done
- Write mode persists to the correct domain tables with transactional integrity.
- Rerunning the same source does not duplicate or drift persisted data.
- Non-empty database behavior is documented, implemented, and tested.
- Rollback/recovery procedures are runbooked and validated.
Epic: Migration Write Correctness and Non-Empty Database Safety
Summary
Fix write-mode migration so it persists to the intended domain tables, behaves deterministically on rerun, and safely handles non-empty databases without hidden data loss or duplication.
Why This Epic
Current behavior indicates write mode does not materialize data into the expected tables. This is a release blocker.
Non-empty database handling is also undefined. Without a clear contract, reruns can create duplicate records, overwrite valid state, or produce ambiguous review output.
Goals
Non-Goals
Decision Topics
User Stories
Story 1: Trace and document current write-run pipeline gaps
As an engineer, I want an implementation-level trace so no-op write points and table mismatches are explicit.
Story 2: Implement transactional write path to canonical tables
As an operator, I want write runs to persist all intended entities atomically so partial writes cannot corrupt state.
Story 3: Enforce dry-run and write-run parity
As a reviewer, I want the same validation and reconciliation outcomes regardless of mode so sign-off is trustworthy.
Story 4: Add idempotent upsert semantics for reruns
As a platform maintainer, I want repeat writes of the same source checksum to avoid duplicates and drift.
Story 5: Define and enforce non-empty database strategy
As a team lead, I want an explicit policy for existing data so migrations are predictable in live-like environments.
Story 6: Add duplicate detection and conflict reporting
As an operator, I want clear conflict diagnostics when incoming migration rows overlap existing canonical rows.
Story 7: Add safe rollback and compensation for failed writes
As an operator, I want recovery procedures for failed write runs so bad data can be reverted quickly.
Story 8: Add integration tests for write correctness on empty and non-empty DBs
As a QA engineer, I want automated proof that write mode behaves correctly across both scenarios.
Delivery Plan
Risks and Mitigations
Risk: Hidden legacy assumptions break when enforcing canonical writes.
Mitigation: Introduce migration compatibility checks and staged rollout flags.
Risk: Non-empty DB policy causes unintended user-visible changes.
Mitigation: Require explicit mode selection with confirmation and run preview.
Definition of Done