Make sync writes and checkpoint advancement atomic

[jmcte]

Problem

write_index_records() performs a multi-statement write without a per-record savepoint. A failure after inserting the message can leave partial data committed. On retry, any unique constraint is treated as an idempotent skip, and the checkpoint can advance over an incomplete record.

Separately, mapper issues do not affect SyncResult.success; a missing-required record can be dropped while the batch reports success and advances its cursor.

Reproduced failures

• Invalid attachment metadata inserted the parent message, failed the child insert, then committed the partial message.
• Retry treated the message uniqueness collision as a skip and advanced the checkpoint.
• A batch containing a missing-required record returned success=True and advanced to the supplied cursor.

Relevant code

• src/mailplus_intelligence/index_writer.py:22-58,91-119,215-233
• src/mailplus_intelligence/mapper.py:83-95
• src/mailplus_intelligence/sync.py:41-83
• src/mailplus_intelligence/migrations/001_metadata_schema_v0.sql
• docs/offline-incremental-sync.md
• docs/operational-safety.md

Scope

• Validate normalized records before mutation.
• Use per-record savepoints or an equivalent transaction boundary.
• Distinguish the intended idempotency key from unrelated uniqueness/constraint failures.
• Define fatal mapper rejects versus non-fatal warnings.
• Persist privacy-safe rejected-record information or return a durable quarantine result.
• Advance the checkpoint only when every source record is inserted, intentionally unchanged, or explicitly quarantined.
• Commit record mutations and checkpoint advancement atomically.
• Report inserted, updated, unchanged, rejected, and failed counts separately.

Non-goals

• Do not implement the live adapter in this issue.
• Do not add raw message payloads to reject records or logs.
• Do not silently reinterpret malformed records as successful skips.

Acceptance criteria

• A child-row failure leaves no partial parent or relationship rows.
• Retrying a failed batch can repair the record rather than skip it permanently.
• Only a duplicate of the documented idempotency identity is counted as unchanged.
• Fatal mapper rejects prevent checkpoint advancement.
• Every input record is accounted for in the result.
• Checkpoint and data changes roll back together on failure.
• Dry-run computes the same planned mutations without writing or advancing.
• Tests cover partial failure, retry, duplicate identity, mapper reject, mixed batch, and checkpoint rollback.
• Operational docs describe reject/quarantine and replay behavior.

Validation

PYTHONDONTWRITEBYTECODE=1 PYTHONPATH=src python3.12 -m unittest tests.test_sync tests.test_index_writer tests.test_mapper -v
bash scripts/ci/run-fast-checks.sh

Add a focused regression test matching each reproduced failure above.

Parent

Part of #98.