Production Data Incident Investigation and Debugging Playbook

1. Core Idea

Production data incident biasanya muncul sebagai symptom bisnis:

• customer cannot submit quote,
• order stuck,
• product active but not billed,
• customer billed but service inactive,
• invoice amount wrong,
• duplicate order,
• missing product inventory,
• search shows deleted data,
• dashboard KPI mismatch,
• external callback cannot find entity,
• event stuck in DLQ,
• support cannot trace order.

Senior backend engineer harus bisa bergerak dari symptom ke root cause dengan structured investigation.

Mental model:

Debugging enterprise data is not random SQL. It is controlled evidence gathering across source-of-truth, lineage, events, projections, integrations, and operational metadata.

2. Incident First Principles

Saat incident terjadi, tujuan pertama:

1. Protect customer/business.
2. Determine scope and severity.
3. Stop further damage if needed.
4. Preserve evidence.
5. Identify source-of-truth.
6. Find root cause.
7. Apply safe repair/mitigation.
8. Verify correctness.
9. Prevent recurrence.

Jangan langsung update data manual sebelum tahu source, scope, and side effects.

3. Triage Questions

Start with:

• What is the exact symptom?
• Which customer/account/tenant affected?
• Which quote/order/product/invoice IDs?
• When did it start?
• Is it ongoing?
• Is it single entity or many?
• Is money/customer access/security involved?
• Is data wrong in source or only projection/cache/search?
• Did recent deploy/migration/config change happen?
• Are external systems involved?
• Is there a correlation ID?

Triage defines blast radius.

4. Identify Business Object

Collect identifiers:

tenant_id
customer_id / customer_number
account_id / account_number
billing_account_id
quote_id / quote_number / quote_version
order_id / order_number
order_item_id
product_instance_id
service_instance_id
resource_id
charge_id
invoice_id / invoice_number
external_system + external_id
correlation_id

If only external ID is known, use external_reference mapping.

If only business number is known, map to technical ID.

5. Establish Source of Truth

Before comparing, determine authoritative source.

Examples:

Do not trust projection/search/cache as source-of-truth unless designed that way.

6. Build Timeline

Use all available evidence:

• audit,
• status history,
• outbox,
• inbox,
• event logs,
• workflow history,
• integration message,
• external callbacks,
• support timeline,
• file receipt/batch run,
• data repair records,
• deployment/migration timestamps.

Timeline example:

10:00 Quote accepted
10:00 QuoteAccepted outbox created
10:01 Outbox publish failed
10:15 Retry succeeded
10:16 OrderCreated
10:20 Decomposition failed

Timeline turns confusion into sequence.

7. Check Recent Changes

Look for:

• deploys,
• DB migrations,
• config changes,
• catalog publish,
• pricing rule change,
• reference data change,
• feature flag change,
• tenant config change,
• external system release,
• batch job start,
• data repair,
• cache/search rebuild,
• migration/backfill,
• infrastructure incident.

Many data incidents are caused by recent changes.

8. Investigation Pattern: Quote Accepted but No Order

Symptoms:

Quote status ACCEPTED
No product order found

Check:

select id, quote_number, version, status, accepted_at, correlation_id
from quote
where quote_number = :quote_number;

select id, order_number, status, source_quote_id, source_quote_version
from product_order
where source_quote_id = :quote_id
  and source_quote_version = :quote_version;

Then:

• check quote conversion status,
• check outbox QuoteAccepted / QuoteConversionRequested,
• check order service inbox,
• check DLQ,
• check idempotency record,
• check validation exception,
• check recent deploy.

Likely causes:

• outbox stuck,
• consumer failed,
• conversion validation failed,
• duplicate/idempotency conflict,
• incompatible event schema,
• quote version mismatch.

Safe repair:

• retry conversion with same idempotency key,
• replay event only if consumer idempotent,
• do not create order manually without source quote/version.

9. Investigation Pattern: Duplicate Order

Symptoms:

Two orders for same quote/version.

Check:

select source_quote_id, source_quote_version, count(*)
from product_order
where source_quote_id = :quote_id
group by source_quote_id, source_quote_version;

Then inspect:

• unique constraint exists?
• idempotency record exists?
• two requests had different idempotency keys?
• retry happened during timeout?
• migration/backfill created duplicate?
• event replay side effect duplicated?

Root cause likely:

• missing unique key,
• idempotency not persisted,
• eventual consumer side effect not idempotent,
• bug in source_quote_version.

Repair:

• identify valid order,
• cancel/supersede duplicate through domain process,
• prevent billing/fulfillment duplicate,
• add uniqueness/idempotency constraint.

10. Investigation Pattern: Order Stuck

Symptoms:

Order stuck IN_PROGRESS or SUBMITTED too long.

Check:

• order status history,
• order items statuses,
• decomposition run,
• fulfillment tasks,
• workflow instance,
• integration messages,
• fallout records,
• retry metadata,
• owner group/runbook.

Queries:

select *
from order_status_history
where order_id = :order_id
order by transitioned_at desc;

select status, count(*)
from product_order_item
where order_id = :order_id
group by status;

select status, count(*)
from fulfillment_task
where order_id = :order_id
group by status;

Likely causes:

• dependency waiting,
• fulfillment fallout,
• external timeout,
• workflow stuck,
• item status not aggregated,
• task completed but order aggregation missed.

Repair:

• resolve fallout,
• retry task,
• replay completion event,
• recalculate aggregate status,
• verify billing/inventory impact.

11. Investigation Pattern: Product Active but Not Billed

Symptoms:

Product/service active
No active charge
Revenue leakage risk

Check:

select pi.id, pi.status, pi.activation_date, pi.billing_account_id, pi.source_order_item_id
from product_instance pi
where pi.id = :product_instance_id;

select *
from recurring_charge
where product_instance_id = :product_instance_id
order by created_at desc;

Then:

• billing readiness result,
• order item commercial snapshot,
• product billable flag,
• billing account active,
• charge creation outbox/inbox,
• billing integration messages,
• data quality result,
• external billing status.

Likely causes:

• billing trigger failed,
• missing billing account,
• product incorrectly non-billable,
• duplicate key blocked charge,
• charge created but failed external billing activation.

Repair:

• run billing readiness,
• create/retry charge idempotently,
• reconcile external billing,
• calculate revenue impact.

12. Investigation Pattern: Customer Billed but Service Not Active

Symptoms:

Charge/invoice active
Service/product inactive
Customer trust risk

Check:

• charge status/effective date,
• invoice line,
• product/service status,
• fulfillment task,
• activation proof,
• order item status,
• billing trigger source.

Potential root causes:

• billing triggered too early,
• stale event/order state,
• product activation rolled back but charge remained,
• fulfillment failed after billing activation,
• incorrect reconciliation.

Repair:

• pause/terminate charge if needed,
• issue credit/adjustment if billed incorrectly,
• fix fulfillment or product state,
• link incident and customer impact.

Do not silently delete invoice line if already issued; use financial adjustment policy.

13. Investigation Pattern: Invoice Amount Wrong

Questions:

• Is quote price wrong?
• Is order commercial snapshot wrong?
• Is charge wrong?
• Is invoice generation wrong?
• Is tax/discount/proration wrong?
• Is usage/rating wrong?
• Is currency/rounding wrong?
• Is invoice line duplicated?
• Is billing period wrong?

Trace:

invoice_line
  -> charge
  -> product_instance
  -> order_item
  -> quote_item
  -> pricing snapshot / rating rule

Check:

• charge source,
• price rule version,
• discount approval,
• tax calculation,
• proration period,
• usage aggregation,
• currency.

Repair:

• if invoice issued, likely adjustment/credit/debit,
• preserve original,
• correct source going forward,
• reconcile affected invoices.

14. Investigation Pattern: Search/Projection Wrong

Symptoms:

Search says order is active but source says cancelled.
Dashboard stale.

Check:

• source entity version,
• projection version,
• last processed event,
• projection checkpoint,
• indexing status,
• cache key/TTL,
• deletion/anonymization propagation,
• projection rebuild history.

Query:

select *
from projection_checkpoint
where projection_name = :projection_name;

select *
from search_index_document_state
where entity_type = :entity_type
  and entity_id = :entity_id;

Likely causes:

• event missed,
• stale event overwrote projection,
• checkpoint stuck,
• index update failed,
• cache not invalidated,
• purge propagation missed.

Repair:

• rebuild projection/document,
• replay events idempotently,
• fix consumer,
• add stale event/version guard.

15. Investigation Pattern: Event Stuck or Missing

Check:

• outbox row exists?
• outbox status?
• publisher logs?
• broker topic/queue?
• consumer inbox?
• DLQ?
• schema compatibility?
• consumer lag?
• idempotency conflict?

Outbox query:

select event_id, event_type, aggregate_id, status, retry_count, error_message, created_at
from outbox_event
where aggregate_id = :aggregate_id
order by created_at desc;

Inbox query:

select event_id, subscriber_name, status, error_message, processed_at
from inbox_message
where event_id = :event_id;

Repair:

• retry outbox,
• replay event,
• fix schema/consumer,
• avoid duplicate side effects,
• verify downstream state.

16. Investigation Pattern: External Callback Cannot Find Entity

Symptoms:

Billing/OSS callback received but local entity not found.

Check:

• source system code,
• external entity type,
• external ID normalized,
• tenant/environment,
• external_reference record,
• mapping lifecycle,
• duplicate/conflict mapping,
• callback arrived before mapping commit,
• wrong environment callback.

Query:

select *
from external_reference
where source_system = :source_system
  and external_entity_type = :external_entity_type
  and external_id = :external_id
  and status = 'ACTIVE';

Repair:

• do not guess mapping,
• verify external system status,
• create/correct mapping with approval if sensitive,
• reprocess callback idempotently.

17. Investigation Pattern: Data Changed Unexpectedly

Check audit/change history:

select *
from audit_event
where entity_type = :entity_type
  and entity_id = :entity_id
order by occurred_at desc;

Look for:

• actor,
• service account,
• batch job,
• migration,
• repair case,
• workflow,
• event consumer,
• external sync,
• admin operation,
• break-glass session.

If no audit exists, that is itself a gap.

Check database updated_at/updated_by if available.

18. Scope Analysis

Determine affected records.

Use patterns:

• same tenant,
• same customer/account,
• same product offering/version,
• same catalog publish,
• same price rule version,
• same batch run,
• same file receipt,
• same event type/time window,
• same deployment version,
• same external system,
• same migration batch,
• same feature flag.

Example:

select count(*)
from product_instance
where product_offering_id = :offering_id
  and created_at between :start and :end;

Scope drives severity and remediation.

19. Evidence Preservation

Before repair:

• capture current row snapshots,
• export relevant timeline,
• save logs/correlation links,
• preserve file/event payload references,
• record affected IDs,
• create incident/repair case,
• avoid destructive update,
• do not purge evidence.

Evidence supports postmortem and audit.

20. Safe Repair Principles

Repair should be:

• domain-command based if possible,
• idempotent,
• approved for high-risk,
• audited,
• before/after captured,
• linked to incident,
• verified by reconciliation,
• minimal scope,
• reversible/forward-fix planned.

Avoid:

quick SQL update in production with no record

unless emergency and still recorded immediately.

21. Root Cause Categories

Common root causes:

• missing invariant,
• missing DB constraint,
• missing idempotency,
• stale cache/projection,
• event schema incompatibility,
• out-of-order event,
• external mapping error,
• migration/backfill bug,
• manual repair mistake,
• race condition,
• reference data mismatch,
• tenant/environment mix-up,
• wrong metric definition,
• missing retention/purge propagation,
• external system defect.

Root cause should lead to preventive action.

22. Prevention Patterns

A postmortem without preventive model change is incomplete.

23. Debugging SQL Safety

When running SQL in production:

• use read-only first,
• include tenant/customer scope,
• limit result size,
• avoid locking queries,
• avoid unbounded updates,
• use transaction carefully,
• explain/analyze heavy query in safe environment,
• do not expose sensitive data in screenshots,
• save query/result references in incident,
• validate row counts before update.

For repair SQL:

begin;
select count(*) ... -- expected scope
update ... where precise condition;
select count(*) ... -- verify
commit;

But prefer domain repair workflow.

24. Incident Timeline Template

Use:

Incident number:
Severity:
Detected at:
Detected by:
Affected tenant/customer:
Affected entities:
Customer impact:
Financial impact:
Security/privacy impact:
Start time:
End time:
Recent changes:
Initial symptom:
Source of truth checked:
Timeline:
Root cause:
Mitigation:
Repair:
Verification:
Preventive actions:
Open risks:

This structure keeps investigation focused.

25. Useful Investigation Queries

Accepted quote without order:

select q.id, q.quote_number, q.version, q.accepted_at
from quote q
left join product_order o
  on o.source_quote_id = q.id
 and o.source_quote_version = q.version
where q.status = 'ACCEPTED'
  and o.id is null;

Active product without charge:

select pi.id, pi.product_instance_number, pi.activation_date
from product_instance pi
left join recurring_charge rc
  on rc.product_instance_id = pi.id
 and rc.status = 'ACTIVE'
where pi.status = 'ACTIVE'
  and pi.billable = true
  and rc.id is null;

Duplicate external mapping:

select tenant_id, source_system, external_entity_type, external_id, count(*)
from external_reference
where status = 'ACTIVE'
group by tenant_id, source_system, external_entity_type, external_id
having count(*) > 1;

These are illustrative. Adjust to actual schema.

26. Observability Signals

During incident, check:

• error rate by error_code,
• version conflict rate,
• outbox pending age,
• inbox failure count,
• DLQ size,
• consumer lag,
• integration timeout rate,
• workflow stuck count,
• reconciliation mismatch count,
• projection lag,
• cache invalidation failure,
• recent deploy/migration status,
• database lock/deadlock metrics.

Correlate metrics with affected business entities and timeline.

27. Communication

For stakeholders, avoid raw technical noise.

Communicate:

• what is impacted,
• who is impacted,
• whether customer/money/security affected,
• current mitigation,
• data correction plan,
• confidence level,
• next verification step,
• preventive action.

Do not claim resolved until source-of-truth and derived stores are verified.

28. Data Incident Closure Criteria

Close only when:

• source data corrected or confirmed correct,
• derived projections/search/cache corrected,
• external systems reconciled,
• financial/customer impact handled,
• repair audited,
• data quality checks pass,
• monitoring stable,
• root cause documented,
• preventive action created,
• support timeline updated,
• stakeholders informed.

"Code deployed" is not the same as "data incident closed".

29. Failure Modes During Investigation

30. PR Review Checklist

After a data incident, review proposed fix:

• Does it address root cause, not only symptom?
• Does it add invariant/constraint if missing?
• Does it add idempotency if duplicate caused issue?
• Does it add reconciliation if cross-system drift occurred?
• Does it add contract test if schema broke?
• Does it add observability for earlier detection?
• Does it include migration/repair script safely?
• Does it update runbook?
• Does it handle existing bad data?
• Does it verify derived stores/cache/search?
• Does it consider security/privacy impact?
• Does it include regression tests with incident scenario?

31. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Available support timeline tooling.
• Where correlation ID is searchable.
• Outbox/inbox/DLQ dashboards.
• Workflow/Camunda or orchestration history access.
• External integration message tracking.
• Data quality/reconciliation dashboards.
• Manual repair process.
• Incident/postmortem template.
• Production SQL access rules.
• Sensitive data handling rules during incident.
• Known recurring incident categories in Quote & Order.
• Whether support can trace from invoice/order/quote/external ID.

32. Summary

Production data debugging is a disciplined evidence process.

A strong incident playbook includes:

• triage,
• identifier collection,
• source-of-truth confirmation,
• timeline construction,
• recent change review,
• pattern-specific checks,
• event/outbox/inbox investigation,
• external mapping checks,
• scope analysis,
• evidence preservation,
• safe repair,
• root cause classification,
• preventive action,
• verification,
• closure criteria.

The key principle:

Debug data incidents by tracing business truth through lineage, state history, events, integrations, projections, and reconciliation—not by guessing from one table or one log line.