# Sample Completed Incident: Aerialytic Prod/Latest Data Integrity Incident

Incident ID: `INC-2026-0706-0001`
Title: Prod/latest lead related-row data integrity incident
Category: Data Corruption / Database Incident / Customer Escalation
Environment: `prod/latest` live at `portal.aerialytic.ai`
Date: July 6, 2026
Timezone: America/Toronto
Status: Monitoring / Postmortem in progress

## Executive Summary

On July 6, 2026, Aerialytic identified a production data integrity issue in the prod/latest CRM database. The incident affected related lead records, primarily `lead_homeowners`, `lead_images`, and `activities`. Primary lead/project records still existed, but some portal views failed because application code expected every project lead to have a homeowner/contact row.

The incident was caused by an Enerflo migration/recovery workflow that copied rows between independent databases using integer primary keys and `ON CONFLICT(id) DO UPDATE`. Because the source and destination databases had overlapping integer ID sequences, some existing non-Enerflo rows were overwritten/reassigned by Enerflo rows.

Recovery restored affected related rows from a healthy backup/clone using fresh IDs. Current verified recovery reduced missing project-lead homeowners from `3,961` to `94`, and incident-window missing homeowners from `3,875` to `8`. Major affected customer June leads were verified back to `0` missing homeowners for WOLFRI, SUNVEN, FASTRA, and SUNCHE.

Current evidence does not indicate unauthorized access, external compromise, or data exfiltration. The incident is currently assessed as an internal data integrity failure.

## Impact Assessment

| Category | Severity | Confidence | Assessment |
| --- | --- | --- | --- |
| Availability | Medium | High | Some portal project/lead views returned errors. |
| Integrity | High | Confirmed | Related rows in three tables were overwritten/reassigned. |
| Confidentiality | Low | Medium | No evidence of unauthorized access or exfiltration. Continue review. |
| Customer Experience | High | High | Customers saw missing lead details and/or lead lookup failures. |
| Operational | High | High | Engineering and incident response required live recovery and monitoring. |
| Compliance/Privacy | Medium | Medium | Legal/privacy review recommended due customer data involvement. |

## Affected Systems

- CRM API
- Prod/latest Cloud SQL database `mainlatest05`
- Portal project/lead views
- Tables: `lead_homeowners`, `lead_images`, `activities`

## Timeline Highlights

| Time ET | Event | Evidence |
| --- | --- | --- |
| ~11:00 AM | Migration/recovery workflow reportedly run around this time. | GCP/log investigation, Slack conversation |
| 12:36 PM | Root cause identified as integer ID collision plus `ON CONFLICT(id) DO UPDATE`. | Slack conversation |
| 12:50 PM | Dry-run repair impact shared: thousands of rows recoverable. | Slack conversation |
| 12:55 PM | Pre-repair backup completed; repair started. | Slack conversation |
| 1:15 PM | Repair reported complete; WOLFRI checked as fine. | Slack conversation and DB checks |
| Post-repair | Missing incident-window homeowners reduced from `3,875` to `8`. | Live DB verification |

## Root Cause

Legacy and latest databases were independent systems with overlapping integer primary key sequences. The migration copied rows from legacy to latest with source integer IDs. For tables using serial/bigint IDs, `ON CONFLICT(id) DO UPDATE` did not safely isolate Enerflo rows. When a source Enerflo row used an ID already owned by a non-Enerflo row in latest, the upsert updated the existing non-Enerflo row with Enerflo data.

## Contributing Factors

- Integer IDs were treated as globally safe across independent databases.
- Upsert conflict target used `id` for related tables whose IDs were not globally unique.
- Pre-run validation did not explicitly check for cross-company ID collisions.
- The app assumed `Lead.contact` existed and did not tolerate missing homeowners in `ProjectSerializer.get_name`.
- Recovery tooling initially looked for missing IDs, but the damaged IDs still existed and had been overwritten.

## Recovery Summary

Correct recovery inserted rows from the healthy backup by `lead_id` with fresh IDs, rather than trying to reuse clobbered IDs.

Restored rows:

- `lead_homeowners`: `3,867`
- `lead_images`: `7,624`
- `activities`: `30,958`

Post-repair checks:

- Missing project-lead homeowners: `3,961 -> 94`
- Missing incident-window homeowners: `3,875 -> 8`
- WOLFRI/SUNVEN/FASTRA/SUNCHE June missing homeowners: `0`
- Fresh `zipcode` crashes after monitor window: `0`
- Primary-key errors after monitor window: `0`

## Evidence Register

| ID | Category | Description | Source |
| --- | --- | --- | --- |
| EV-0001 | Slack transcript | Root cause and repair coordination | Slack incident/recovery conversation |
| EV-0002 | DB query output | Missing homeowner counts before and after repair | prod/latest DB |
| EV-0003 | Recovery script | Fresh-ID repair script for clobbered rows | Local evidence package |
| EV-0004 | Backup metadata | Healthy backup/clone and pre-repair backup references | Cloud SQL |
| EV-0005 | App error logs | `NoneType` zipcode crash from missing homeowner contact | CRM API logs |

## Customer Communication Template

Subject: Aerialytic portal incident update - July 6, 2026

Hi [Customer Name],

Today, July 6, 2026, Aerialytic identified and resolved a production data integrity issue affecting some recently created lead/project records in the portal. During the incident, some users may have seen missing lead details, missing lead images/activity history, or errors when opening affected records.

What happened:

- A database migration/recovery process wrote related lead data with conflicting internal IDs.
- This affected associated records such as homeowner/contact details, lead images, and activity history for some leads.
- The primary lead/project records were not deleted.

What we did:

- We stopped the workflow, isolated the issue, restored affected related records from a healthy backup using fresh IDs, and verified the main affected customer datasets are accessible again.
- We are continuing additional monitoring and review of a small remaining set of records.

Security and privacy:

- At this time, we have no evidence of unauthorized access, external compromise, or data exfiltration.
- Current evidence points to an internal data-integrity issue related to migration handling.

Customer action:

- No action is required right now.
- If you notice a lead/project that still looks incomplete, send us the lead address or portal URL and we will verify it immediately.

We are sorry for the disruption. We will follow up if our continuing review changes this assessment.

Aerialytic Support

## Action Items

| ID | Priority | Owner | Action | Verification |
| --- | --- | --- | --- | --- |
| ACT-0001 | P0 | Engineering | Remove/ban cross-DB integer-ID upsert for copied related rows. | Migration scripts reject non-UUID PK upsert unless remapped. |
| ACT-0002 | P0 | Engineering | Add preflight collision checks for every migration table. | Dry-run output includes global collision count by table/company. |
| ACT-0003 | P1 | Engineering | Add API guard for missing `Lead.contact`. | Serializer test covers project lead without homeowner. |
| ACT-0004 | P1 | Data/Infra | Preserve recovery scripts, logs, backup references, and DB snapshots. | Evidence register completed with hashes. |
| ACT-0005 | P1 | Security/Privacy | Complete notification/legal assessment. | Signed decision log entry. |
| ACT-0006 | P2 | SRE | Add integrity monitors for lead/project/homeowner relationships. | Alert fires on threshold breach in staging/prod. |

## Regulatory / Notification Assessment Draft

Current evidence points to internal data integrity corruption, not unauthorized access or exfiltration. Because homeowner/contact data was affected, privacy/legal should review:

- Whether corrupted rows were visible to wrong companies/users during the impact window.
- Whether any data was transmitted externally.
- Whether customer contracts require notification for integrity incidents even without breach.
- Whether jurisdiction-specific privacy notification thresholds are triggered.

## Closure Criteria

- Remaining 8 incident-window missing records dispositioned.
- Post-repair Cloud SQL backup completed and logged.
- Evidence package hash recorded.
- Customer/legal notification decision recorded.
- Migration guardrails merged and verified.
- App null-contact guard merged and deployed.