Chapter 13 — Data systems
Lesson map
Lesson 13 published. Map, example, EX026.
flowchart TD
N0["Learning outcomes and connection"]
N1["13.1 Transactions, consistency and logs"]
N2["13.2 Backups, RPO and RTO"]
N3["13.3 Restoration order"]
N4["13.5 Integrity checks before reconnecting"]
N5["Chapter synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5
N5 --> EX["Worked example"]
EX --> Q["Exercise EX026"]
Logical drawing
flowchart LR
N0["Learning outcomes and connection"]
N1["13.1 Transactions, consistency and logs"]
N2["13.2 Backups, RPO and RTO"]
N3["13.3 Restoration order"]
N4["13.5 Integrity checks before reconnecting"]
N5["Chapter synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX026"]
[Learning outcomes and connection]
|
v
[13.1 Transactions, consistency and logs]
|
v
[13.2 Backups, RPO and RTO]
|
v
[13.3 Restoration order]
|
v
[13.5 Integrity checks before reconnecting]
|
v
[Chapter synthesis]
|
v
[Worked example] --> [Exercise EX026]
Learning outcomes and connection
After this chapter you can (1) treat databases and stores both as evidence sources and as services to restore; (2) use transactions, schemas and checksums to detect integrity loss; (3) relate backups to recovery point and recovery time objectives; and (4) derive a restoration order from dependencies and check it against objectives. It prepares Chapters 22, 28, 35 and 47.
13.1 Transactions, consistency and logs
A transactional database guarantees that a committed change is durable and that concurrent transactions do not see each other's partial work. It achieves durability with a write-ahead log, which is also evidence: it records changes in order and may reveal what an attacker modified even after rows were changed back. Schema constraints (foreign keys, check constraints) catch some corruption; application-level checksums on critical records catch more. Codd (1970) introduced the relational model whose integrity rules most case stores still rely on.
13.2 Backups, RPO and RTO
The recovery point objective (RPO) is the maximum acceptable data loss, measured in time; the recovery time objective (RTO) is the maximum acceptable time to restore service (NIST SP 800-34 Rev. 1). A backup's age at the moment of failure is the data loss you will suffer; if backups run every 6 h, an RPO of 4 h is not met for a random failure time. Backups must be protected from the same compromise that hits production — offline, immutable or under separate credentials — and tested by restore, not by job success. NIST SP 800-184 treats recovery planning and testing as a continuous discipline.
13.3 Restoration order
Components depend on each other: the case API needs the case database, the evidence store and the identity service. A topological sort of the dependency graph gives a valid order; the critical path — the longest dependent chain — gives the minimum time with unlimited parallel teams.
In Lab 13 the critical path runs identity → evidence store → case API → portal, adding 1 + 3 + 0.5 + 0.5 = 5.0 h. The queue and case database restores fit beside it on a second team, which is why EX026 also reaches 5.0 h. With a single team, the same restores take 7.5 h because nothing runs in parallel.
13.4 Worked example (synthetic): HVSS case platform after ransomware
"""Lab 13 (synthetic): restoration order from dependencies; data loss against recovery point objectives."""
from graphlib import TopologicalSorter
deps = {"identity": set(), "case_db": {"identity"}, "evidence_store": {"identity"},
"queue": set(), "case_api": {"case_db", "evidence_store", "queue"}, "portal": {"case_api", "identity"}}
restore_h = {"identity": 1.0, "case_db": 2.0, "evidence_store": 3.0, "queue": 0.5, "case_api": 0.5, "portal": 0.5}
last_backup_age_h = {"identity": 2, "case_db": 6, "evidence_store": 1, "queue": 0, "case_api": 0, "portal": 0}
rpo_h = {"identity": 4, "case_db": 4, "evidence_store": 1, "queue": 0, "case_api": 0, "portal": 0}
order = list(TopologicalSorter(deps).static_order())
print("restore order:", " -> ".join(order))
done = {}
for s in order: # one team, sequential; a component may start when its dependencies are done
start = max([done[d] for d in deps[s]], default=0.0)
start = max(start, max(done.values(), default=0.0))
done[s] = start + restore_h[s]
flag = "MISSES RPO" if last_backup_age_h[s] > rpo_h[s] else "ok"
print(f"{s:15s} ready at {done[s]:4.1f} h data loss {last_backup_age_h[s]} h vs RPO {rpo_h[s]} h {flag}")
# two teams: critical path length
cp = {}
for s in order: cp[s] = max([cp[d] for d in deps[s]], default=0.0) + restore_h[s]
print("critical path (unlimited teams): portal ready at", cp["portal"], "h")
Expected output:
restore order: identity -> queue -> case_db -> evidence_store -> case_api -> portal
identity ready at 1.0 h data loss 2 h vs RPO 4 h ok
queue ready at 1.5 h data loss 0 h vs RPO 0 h ok
case_db ready at 3.5 h data loss 6 h vs RPO 4 h MISSES RPO
evidence_store ready at 6.5 h data loss 1 h vs RPO 1 h ok
case_api ready at 7.0 h data loss 0 h vs RPO 0 h ok
portal ready at 7.5 h data loss 0 h vs RPO 0 h ok
critical path (unlimited teams): portal ready at 5.0 h
Two findings. First, the case database's last clean backup is 6 h old against a 4 h RPO: the objective is missed before restoration begins, and the gap must be filled from other sources (write-ahead logs replicated elsewhere, email notifications, the queue's retained messages) or accepted and recorded. Second, one team needs 7.5 h; with parallel teams the critical path is 5 h (identity → evidence store → case API → portal). Whether 5 h meets the RTO is a staffing decision that the model makes explicit.
13.5 Integrity checks before reconnecting
Restoring from a backup taken after the intruder arrived restores the intruder. Before reconnecting: compare restored data with checksums or record counts from an independent source; scan for known persistence (new administrator accounts, triggers, stored procedures); and verify the backup timestamp precedes the earliest evidence of compromise — which itself is uncertain (Chapter 3).
For the HVSS case database, the 6 h old backup has to pass these checks before the case API reconnects. Its timestamp must precede the earliest evidence of the ransomware intrusion, allowing for clock uncertainty. Its record counts must match an independent source such as the queue's retained messages. It must also be scanned for administrator accounts or triggers that were not present in earlier backups.
13.6 Errors and limits
- Equating backup job success with restorability.
- Backups reachable with production administrator credentials.
- Restoring in an order that violates dependencies.
- Ignoring the RPO gap until after restoration.
Lab 13 exposes the last error in this list directly, before any restore begins. The case database misses its RPO by two hours before any restore work starts, so the gap must be filled or accepted while choosing sources. If that is discovered only after the portal is back at 7.5 h, the replicated write-ahead logs or retained queue messages may have expired. Restoring the case API before the evidence store would also break the dependency order the lab computes.
Exercises — chapter 13
EX025 — RPO for a schedule. Objective: relate backup frequency to RPO. Inputs: backups every 6 h taking 30 min, failures uniformly random in time. Steps: compute worst-case and expected data loss (age of last completed backup); state the backup interval needed for a 4 h worst-case RPO. Expected evidence: numbers. Rubric: 3 = worst 6.5 h, expected 3.5 h, interval ≤ 3.5 h; 2 = one error; 1 = method; 0 = none.
EX026 — Two teams. Objective: schedule restoration. Inputs: Lab 13 data, two teams. Steps: produce a schedule respecting dependencies and give the portal-ready time. Expected evidence: schedule table. Rubric: 3 = 5.0 h schedule (team 1 identity, evidence store, case API, portal; team 2 queue, then case DB after identity) with dependency check; 2 = valid but slower; 1 = violates a dependency; 0 = none.
Chapter synthesis
Data systems are evidence and service at once. Use transactions, logs and checksums for integrity; back up against your own compromise; test restores; derive restoration order from dependencies; and compare data loss with RPO before, not after, recovery.
Continue studying — chapter 13 of 50
← Chapter 12 · Course chapter index · Chapter 14 →
Continue to the next authored lesson.