Design a resource change tracking database
Company: Microsoft
Role: Software Engineer
Category: System Design
Difficulty: medium
Interview Round: Onsite
## Scenario
You are building part of an Azure-like cloud control plane. Resources (VMs, networks, storage accounts, etc.) are stored as JSON documents and are updated frequently.
## Task
Design a **Resource Change Database** / system that:
1. Captures every change to a resource (create/update/delete).
2. Emits a **notification/event** after a change occurs.
3. Downstream consumers can use the notification to **query the “diff”** (what changed between the old and new versions) and store that diff for later auditing/search.
## Requirements
- **Correctness:** Diffs must correspond to the actual committed change.
- **Ordering:** For a given resource, changes should be processed in order.
- **Idempotency:** Duplicate notifications should not create duplicate diff records.
- **Query:** Support querying change history by resource ID and time range; optionally filter by fields changed.
- **Scale:** Assume high write volume (control-plane scale) and many resource types.
## Clarifications you may decide
- How diffs are represented (JSON Patch, field-level records, full before/after snapshots).
- Retention policy (e.g., keep 90 days of diffs).
- Multi-region requirements and latency SLOs.
Quick Answer: This question evaluates system design and distributed-systems competencies focused on change-data capture, event-driven architecture, correctness of diffs, ordering, idempotency, auditing, and scalable modeling of JSON-based resources.