fix(fusion): make alert delivery durable and enforce tenant dedup uniqueness - #1482

Summary

Improve Fusion event-pipeline reliability by ensuring that processed events are not acknowledged before durable handling, making cache degradation fail-open, and enforcing tenant-scoped alert deduplication at the database level.

Changes

• Kafka consumer durability

  • Add bounded message-level retries with capped exponential backoff and jitter.
  • Commit explicit per-partition offsets only after durable persistence or successful DLQ capture.
  • Route malformed Kafka values through the normal DLQ or hold path.
  • Improve graceful shutdown so in-flight work is not acknowledged prematurely.

• PostgreSQL alert persistence

  • Verify duplicate outcomes against the authoritative database row.
  • Reconcile deduplication conflicts without treating an unverified result as durable.
  • Add migration 120_alerts_tenant_dedup_unique.sql with a partial unique index on (tenant_id, dedup_hash).
  • Detect pre-existing duplicate pairs without silently deleting or rewriting alerts.

• Cache and archival resilience

  • Treat Redis failures and corrupt cache entries as cache misses where appropriate.
  • Retain and retry failed lake batches within configured bounds.
  • Make fallback event IDs stable across reprocessing, including Kafka-coordinate fallback where needed.

• DLQ and operational safeguards

  • Make DLQ capture failures explicit.
  • Clarify the non-durable behavior of persistence-free mode.
  • Improve Fusion restart chaos-harness guards and outage integration assertions.

Validation

Reported local validation:

• Focused durability, DLQ, alert-sink, lake-writer, and cache-fallback tests: 126 passed.
• Full Fusion suite with the documented pre-existing flaky test deselected: 977 passed, 17 failed, 2 skipped, 1 deselected.
• The 17 failures matched the untouched baseline failure set.
• Python compilation, migration validation checks, and Git diff checks passed.
• Ruff lint and formatting checks passed in the earlier validation run. Ruff was not rerun during the latest verification because the tool was unavailable in that environment.
• Chaos harness self-test passed.

Limitations

• Live PostgreSQL concurrency and migration application were not tested against a real PostgreSQL instance.
• Docker-based chaos tests and live Kafka, Redis, and ClickHouse outage tests were not run.
• Local testing used Python 3.12; CI uses Python 3.11.
• Migration 120 intentionally fails if existing duplicate (tenant_id, dedup_hash) pairs are found. Operators must resolve these deliberately before retrying the migration.
• Use AISOC_MIGRATIONS_STRICT=1 where a migration failure must stop deployment.
• The lake writer remains at-least-once, not exactly-once.
• Persistence-free logging mode is not loss-proof by design.

This PR addresses Fusion event durability and tenant-scoped alert deduplication. It does not claim to resolve every unrelated service-resilience finding in issue #1446.

Deployment notes

Review the migration and rollout ordering before deployment. Deploy the updated sink code before or together with the unique index to avoid spurious DLQ entries from older code encountering the new constraint.

For existing installations where write-locking impact is unacceptable, evaluate an out-of-band CREATE UNIQUE INDEX CONCURRENTLY procedure under the project's migration and operational conventions.

Checklist

• Regression tests added for the changed behavior.
• Existing baseline failures compared against the branch.
• Migration and rollback implications documented.
• Live PostgreSQL concurrency and migration verification.
• Container-backed chaos/integration validation.