Production Data Model Review Playbook for Senior Backend Engineers

1. Core Idea

Senior backend engineer tidak hanya menulis repository/entity. Ia harus bisa mereview apakah data model siap untuk production.

Review yang baik menjawab:

Apakah model ini benar secara domain, aman secara lifecycle, jelas ownership-nya, kuat invariant-nya, compatible contract-nya, performa query-nya masuk akal, aman migrasinya, observable saat gagal, dan recoverable saat data drift?

Playbook ini adalah checklist praktis untuk mengevaluasi desain data sebelum merge/release.

Mental model:

Production data model review is where domain correctness, distributed systems, database design, API contracts, security, and operations meet.

2. Review Mindset

Saat review, jangan hanya tanya:

Apakah table/field sudah ada?

Tanya:

Apa business truth yang diwakili?
Siapa owner-nya?
Apa invariant-nya?
Apa lifecycle-nya?
Bagaimana gagal?
Bagaimana retry?
Bagaimana support debug?
Bagaimana migration?
Bagaimana reporting?
Apa yang terjadi 2 tahun dari sekarang?

Senior-level review mencari future incident sebelum incident terjadi.

3. Review Dimensions

Use these dimensions:

1. Domain semantics.
2. Ownership and source-of-truth.
3. Identity and references.
4. Lifecycle and state machine.
5. Invariants and validation.
6. API/DTO/event contract.
7. Persistence and physical design.
8. Concurrency and idempotency.
9. Migration and compatibility.
10. Security/privacy/retention.
11. Observability and supportability.
12. Reconciliation and data quality.
13. Reporting/read model impact.
14. Testing and verification.
15. Operational readiness.

4. Domain Semantics Review

Ask:

• What real-world/business concept does this entity represent?
• Is name precise?
• Is it product offering, product spec, or product instance?
• Is it quote item, order item, or invoice line?
• Is it customer account, billing account, or login account?
• Is it current state, snapshot, history, or projection?
• Is this field derived or manually entered?
• What does null mean?
• What unit/currency/timezone applies?
• Is the definition documented?

Smell:

Field exists because UI needed it, but no one can define business meaning.

5. Ownership Review

Ask:

• Which bounded context owns this data?
• Which service can create/update/delete?
• Which service can only read/project?
• Is external system authoritative?
• Is this duplicate source-of-truth?
• If two systems disagree, who wins?
• How is ownership communicated through API/events?
• Are direct DB writes from other services prevented?

High-risk smell:

Multiple services update same lifecycle status.

6. Identity Review

Ask:

• What is technical primary key?
• What is human business number?
• What is external ID?
• What is legacy ID?
• Is ID tenant/environment scoped?
• Is uniqueness correct?
• Are idempotency keys defined?
• Are source entity/version references preserved?
• Can support search by known reference?

Example:

Order must preserve source_quote_id and source_quote_version.

Without it, quote-to-order traceability breaks.

7. Relationship Review

Ask:

• Is relationship required or optional?
• Is cardinality correct?
• Is parent-child hierarchy needed?
• Is relationship temporal/effective-dated?
• Is FK possible within same DB?
• If cross-service, how is logical reference validated?
• Is reconciliation needed?
• Is cascade delete safe?
• Is historical snapshot needed?

Smell:

foreign key absent because "microservices", but no logical validation/reconciliation exists.

8. Lifecycle Review

Ask:

• What are states?
• What transitions are allowed?
• Which states are terminal?
• Which states are reversible?
• Which transitions require reason?
• Which transitions require approval?
• Which transitions emit events?
• Which transitions trigger billing/fulfillment?
• Is state history stored?
• Is state update command-based or patch-based?

Warning:

PATCH status directly

usually indicates missing domain command.

9. Invariant Review

Ask:

• What must always be true?
• Can database enforce it?
• Can domain service enforce it?
• Can event consumer violate it?
• What happens under concurrency?
• Is there a reconciliation rule if invariant spans systems?
• Are invariants tested?

Examples:

One order per accepted quote version.
Accepted quote immutable.
Active billable product has active charge.
Invoice total equals invoice line totals.
No overlapping current effective-dated characteristic.

10. Validation Review

Ask:

• What is API/DTO validation?
• What is command validation?
• What is domain validation?
• What is DB constraint?
• What is async consumer validation?
• What is external integration validation?
• Are validation errors structured with rule codes?
• Are overrides allowed?
• Are validation results persisted if operationally important?

Do not rely only on frontend validation.

11. API and DTO Review

Ask:

• Is entity exposed directly?
• Are DTOs separated from persistence model?
• Are command endpoints used for state changes?
• Is idempotency supported for create/submit/convert?
• Are error codes stable?
• Are money/date/time formats clear?
• Are sensitive fields hidden/masked?
• Is versioning/deprecation strategy clear?
• Is OpenAPI updated?

Smell:

Response DTO includes all database fields by default.

12. Event Contract Review

Ask:

• What event is emitted?
• Is it domain event or integration event?
• Is event version included?
• Is aggregate version included?
• Are correlation and causation IDs included?
• Are sensitive fields excluded?
• Is consumer idempotency possible?
• Are ordering assumptions documented?
• Is schema compatible?
• Are contract tests updated?

Event should communicate business fact, not internal table dump.

13. Persistence Review

Ask:

• What is table grain?
• Is table hot, append-only, history, or projection?
• Are columns appropriately typed?
• Is money numeric, not float?
• Are timestamps timestamptz where instant?
• Are status/reason codes governed?
• Is JSONB used appropriately?
• Are child/history/audit tables split?
• Is table width reasonable?

Physical design should match workload.

14. Index and Query Review

Ask:

• What are top query patterns?
• Are indexes aligned with filters/sort?
• Is tenant_id included where needed?
• Is unique constraint enforcing business invariant?
• Is partial index useful for active/open rows?
• Are queue/pending rows indexed?
• Is pagination stable?
• Are reporting queries isolated?
• Is query plan tested with realistic data?

Smell:

Dashboard query joins many OLTP tables with no read model.

15. Concurrency Review

Ask:

• Can two users/processes update same aggregate?
• Is optimistic version used?
• Is state transition compare-and-set?
• Is pessimistic lock needed?
• Is external call outside DB transaction?
• Is unique key used for duplicate prevention?
• Are worker queues claimed safely?
• Are stale events ignored?
• Are deadlocks possible?

Concurrency bugs are often correctness bugs, not just performance bugs.

16. Idempotency Review

Ask:

• Can request be retried?
• Can event be delivered twice?
• Can file row be processed twice?
• Can external callback repeat?
• Can job rerun?
• What is idempotency key?
• Is key persisted?
• Does same key/different payload conflict?
• Is DB uniqueness enforcing result?

Critical flows:

• quote acceptance,
• quote-to-order conversion,
• order creation,
• charge activation,
• invoice/export,
• payment callback,
• usage processing.

17. Migration Review

Ask:

• Is schema change additive or breaking?
• Is expand/contract needed?
• Is backfill required?
• Is backfill idempotent?
• What is source of truth?
• What are exceptions?
• How is progress tracked?
• How is rollback/forward-fix handled?
• Are old/new app versions compatible?
• Does DDL lock hot table?
• Are projections/reports affected?

Migration is part of design, not afterthought.

18. Security and Privacy Review

Ask:

• Is this data PII, restricted, commercial-sensitive, or security-sensitive?
• Who can view/update/export?
• Is masking required?
• Is encryption/tokenization required?
• Is access audit required?
• Is field copied to event/cache/search/report?
• Is retention policy defined?
• Is purge/anonymization possible?
• Are lower environments safe?

Sensitive data often leaks through derived copies, not source table.

19. Observability Review

Ask:

• Is correlation ID stored/propagated?
• Is causation ID/event ID available?
• Is business number searchable?
• Is state history stored?
• Are retry/error fields structured?
• Can support see timeline?
• Is workflow/external ID linked?
• Are metrics/logs/traces aligned?
• Is owner/runbook defined for failure?

If support cannot debug it, design is incomplete.

20. Reconciliation Review

Ask:

• What distributed gap can occur?
• What expected vs actual state should be checked?
• Is reconciliation rule defined?
• What severity?
• Who owns mismatch?
• What repair workflow?
• How often run?
• Is result persisted?
• Is alert/dashboard needed?

Examples:

Accepted quote without order.
Fulfilled order item without product instance.
Active product without active charge.
Invoice line missing for billed charge.
Projection stale behind source.

21. Reporting and Analytics Review

Ask:

• Does this affect KPI?
• What is fact grain?
• Is data current-state or historical snapshot?
• What time basis applies?
• Are dimensions historical/current?
• Does it affect revenue, MRR, churn, backlog, installed base?
• Are metric definitions updated?
• Is lineage captured?
• Is reporting query safe?

Operational field changes often break analytics silently.

22. Cache/Search/Projection Review

Ask:

• Is this data cached?
• What is source of truth?
• What is cache key?
• Does key include tenant/scope/version?
• What invalidates it?
• What is TTL?
• Is stale acceptable?
• Is search index updated?
• Is projection version tracked?
• Does purge/anonymization propagate?

Never use cache as command invariant source unless specifically designed.

23. Test Review

Ask:

• Are unit tests enough?
• Are integration tests with real DB constraints included?
• Are state machine tests included?
• Are duplicate/retry tests included?
• Are concurrency tests included?
• Are migration/backfill tests included?
• Are contract tests included?
• Are reconciliation tests included?
• Are security/tenant tests included?
• Are golden datasets updated?

Test data should include edge cases and failure modes.

24. Operational Readiness Review

Ask:

• Is runbook updated?
• Are dashboards updated?
• Are alerts defined?
• Are support tools updated?
• Is repair operation needed?
• Is rollback/forward-fix plan documented?
• Are migration steps scripted?
• Is data quality monitor ready?
• Is release sequencing clear?
• Is on-call aware of new failure modes?

Production readiness is part of data design.

25. Review Scoring

A simple scoring model:

For high-risk financial/lifecycle data, require 3 or 4 before release.

26. Quick Red Flags

Stop and investigate if you see:

• "just add nullable column",
• "we will fix with SQL if needed",
• "status can mean both",
• "frontend will hide it",
• "cache should be fine",
• "event includes whole entity",
• "report can join production tables",
• "we do not need idempotency",
• "migration is just update all rows",
• "externalId is enough",
• "we can delete old field now",
• "null means different things depending case",
• "only this service will use it" without contract/ownership proof.

27. Review Comment Examples

Useful review comments:

Can we document whether this is source-of-truth or snapshot? It looks copied from quote to order, but billing may treat it as authoritative.

This create endpoint needs an idempotency key or unique constraint because client retries can create duplicate orders.

This status field appears to mix lifecycle and fulfillment. Can we split or at least define state machine and reporting mapping?

This event includes billing address and margin fields. Please classify and confirm consumers need them; otherwise send references only.

The migration backfill needs progress tracking and idempotency. A single update over all rows may lock the table.

28. Minimal Review Template

Use this for PR/design docs:

## Domain meaning
## Owner/source-of-truth
## Entity/field definitions
## Lifecycle/state transitions
## Invariants/constraints
## API/event/file contract impact
## Persistence/index/query impact
## Idempotency/concurrency
## Migration/backfill
## Security/privacy/retention
## Observability/support timeline
## Reconciliation/data quality
## Reporting/read model impact
## Test plan
## Rollback/forward-fix
## Open questions/internal verification

This template is intentionally repetitive because production failures often come from skipped sections.

29. Senior Engineer Heuristics

Heuristics:

• If it affects money, snapshot and audit it.
• If it crosses service boundary, contract and version it.
• If it can be retried, make it idempotent.
• If it can drift, reconcile it.
• If it can be searched by support, store business reference.
• If it is sensitive, classify before copying.
• If it is historical, do not overwrite it.
• If it is current state, define source-of-truth.
• If it changes state, use command and guard.
• If it is reported, define grain and time basis.
• If it is migrated, backfill in batches and validate.
• If it is cached, define invalidation and freshness.
• If it is manual repair, audit and verify it.

30. PR Review Checklist

Before approving a production data model change, confirm:

• Semantics clear.
• Owner/source-of-truth clear.
• Identity/reference strategy correct.
• Lifecycle/state transitions defined.
• Invariants/constraints enforced.
• DTO/API/event contracts safe.
• Persistence/index design reviewed.
• Idempotency/concurrency addressed.
• Migration/backfill safe.
• Security/privacy classified.
• Retention/purge considered.
• Observability/support trace available.
• Reconciliation/data quality defined.
• Reporting/read model impact known.
• Tests cover edge/failure cases.
• Rollback/forward-fix ready.
• Internal assumptions verified.

31. Internal Verification Checklist

For CSG/team-specific review, verify:

• Internal domain owner for affected entity.
• Internal coding standards for DTO/entity/repository.
• PostgreSQL migration standards.
• Idempotency conventions.
• Event schema/versioning conventions.
• TMF API alignment requirements.
• Security/privacy classification policy.
• Support/operations tooling expectations.
• Data quality/reconciliation framework.
• Reporting/analytics ownership.
• On-prem/cloud deployment constraints.
• Approval process for billing/financial changes.
• Existing incidents/patterns related to this area.

32. Summary

Production data model review is a senior engineering skill.

A strong review checks:

• meaning,
• ownership,
• lifecycle,
• invariants,
• contracts,
• physical design,
• concurrency,
• idempotency,
• migration,
• security,
• observability,
• reconciliation,
• reporting,
• testing,
• operations.

The key principle:

A data model is production-ready only when it is correct under normal use, retries, concurrency, partial failure, schema evolution, security constraints, reporting needs, and operational repair.