Cloud Resource Change Tracking Database

0) Problem Restatement

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).

1) Capturing Changes

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.

2) Storing History: Snapshots + Diffs

• Every change creates a new version: (resource_id, version, timestamp, actor, change_type).
• Store the full snapshot every N versions (or every day), and diffs (JSON Patch: field paths with old and new values) between them. That saves space, since most changes touch a few fields.
• "State at time T" = the latest snapshot before T + apply the diffs up to T.
• "What changed between T1 and T2" = the diffs in that window (merged).
• Correct diffs: normalize the JSON first (sort keys, ignore volatile fields like lastHeartbeat), so diffs show real changes only.

3) Data 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.

4) Queries and Notifications

• GET /resources/{id}/state?at=2026-09-01T10:00Z
• GET /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.

5) 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).

6) Wrap-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.