Problem RestatementProblem
Microsoft asked: design a database/service that tracks changes to cloud resources (virtual machines, networks, storage accounts, IAM policies) over time, like Azure Resource Graph change history or AWS Config. It must answer:
- "What did resource X look like at time T?"
- "What changed between T1 and T2, and who changed it?"
- "Tell me whenever a network security rule changes" (notifications).
Capturing Changes
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
RP["Resource providers - control plane events"] --> K[("Change events")]
SCAN["Periodic full scans (safety net)"] --> K
K --> PROC["Change processor - normalize, diff, version"]
PROC --> SNAP[("Snapshots store")]
PROC --> DIFF[("Diffs / change log")]
PROC --> IDX[("Index: resource_id, time")]
PROC --> NOTIF["Subscriptions / alerts"]
Q["Query API"] --> SNAP
Q --> DIFF
Q --> IDX- Event-driven: resource providers publish an event on every create, update or delete (with the new state, the actor and the timestamp).
- Periodic full scans: events can be missed, so regularly read the full state of all resources and compare with our latest version. Differences produce "detected change" entries.
Deep Dive — Storing the history of every resourceDeep dive
Users need "what did this look like last Tuesday" and "what changed between these two dates", over resources edited constantly.
Keep only the current state
Update the resource in place.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
R["Resource row - current values only"] --> Q1["What did it look like on Tuesday?"]
Q1 --> NO["No answer"]
R --> Q2["What changed, and who changed it?"]
Q2 --> NO2["No answer"]
R --> INC["An incident caused by a config change - nothing to compare against"]There is no history to query, so the two questions the product exists to answer cannot be answered at all.
A full snapshot per version
Write a complete copy of the resource on every change.
Both questions are answerable now, and the queries are trivial — state at T is the latest snapshot before T. The cost is storage: a 50 KB resource edited two hundred times a day stores 10 MB a day to record changes that usually touch one field, and "what changed" means diffing two large documents at query time.
Periodic snapshots with diffs in between
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
CH["Change"] --> V["New version: (resource_id, version, timestamp, actor, change_type)"]
V --> D["Store a JSON Patch diff - field paths, old and new values"]
V --> S{"Every N versions or once a day?"}
S -->|"yes"| SNAP["Also store a full snapshot"]
Q1["State at T"] --> BASE["Latest snapshot before T"]
BASE --> APPLY["Apply diffs up to T"]
Q2["What changed between T1 and T2"] --> DIFFS["Read the diffs in the window - already the answer"]- Diffs match the shape of the data. Most edits touch a few fields, so storing the delta is a fraction of the cost of a copy — and "what changed" becomes a read rather than a computation.
- Periodic snapshots bound the replay. Without them, reconstructing an old state means applying every diff since creation; with them, it is one snapshot plus a bounded number of patches.
- Normalise before diffing. Sort object keys and ignore volatile fields —
updated_at, generated ids — or every save produces a spurious diff and the history becomes noise that hides the real changes. - Record the actor and the change type on the version row. "What changed" is rarely enough on its own; "who changed it and through which path" is what an investigation needs.
The tuning knob is the snapshot interval: more snapshots mean faster reads and more storage. Set it from how far back people actually query — which is almost always days, not years.
Data ModelData model
resources: resource_id, type, subscription_id, current_version, deleted
versions: resource_id, version, ts, actor, change_type (create|update|delete|detected), snapshot_ref?, diff (JSON Patch)
PRIMARY KEY (resource_id, version); INDEX (resource_id, ts); INDEX (subscription_id, ts)Stored in a scalable store partitioned by resource or subscription (e.g., Cassandra/Cosmos DB), with snapshots in object storage for big resources.
Queries and Notifications
GET /resources/{id}/state?at=2026-09-01T10:00ZGET /resources/{id}/changes?from=&to=GET /subscriptions/{sub}/changes?type=networkSecurityGroup&from=(audit views)- Subscriptions: users register filters (resource type, fields, like
securityRules). The processor publishes matching changes to webhooks or queues.
Reliability
- Events are processed idempotently (by event ID and version) and in order per resource (partition by resource_id).
- Out-of-order events: use the provider's version or ETag or timestamp to place them correctly.
- Retention: keep full history for N days, and compact older history (keep daily snapshots only).
Wrap-UpWrap-up
Capture resource changes from provider events plus periodic full scans, normalize state, and record each change as a new version with actor and time, storing periodic full snapshots plus JSON-Patch diffs. Rebuild "state at T" from the nearest snapshot plus diffs, answer "what changed" from the diff log indexed by resource and time, process events idempotently in per-resource order, and push filtered change notifications to subscribers.