Chapter 13 — Data systems

Lesson map

Lesson 13 published. Map, example, EX037.

flowchart TD
  N0["Learning outcomes"]
  N1["13.1 Retention as data"]
  N2["13.2 The query"]
  N3["13.3 Safe deletion"]
  N4["13.4 Backups and replicas"]
  N5["Lab — Records past their retention period"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6
  N6 --> EX["Worked example"]
  EX --> Q["Exercise EX037"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["13.1 Retention as data"]
  N2["13.2 The query"]
  N3["13.3 Safe deletion"]
  N4["13.4 Backups and replicas"]
  N5["Lab — Records past their retention period"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Retention as data]
    |
    v
[13.2 The query]
    |
    v
[13.3 Safe deletion]
    |
    v
[13.4 Backups and replicas]
    |
    v
[Lab — Records past their retention period]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) express retention rules in a database; (2) query records past retention; (3) design deletion that is safe and verifiable.

13.1 Retention as data

Article 5(1)(e) of the GDPR requires storage limitation: personal data must be kept in identifiable form no longer than necessary. Pellmore has a retention policy on paper, but nothing in its databases enforces it. Zara proposes storing the policy as a table, with one row per data category and its retention period. Each record carries its category and creation date. With policy as data, a simple query can find records that should have been deleted, and changes to the policy are versioned like any other data.

13.2 The query

The lab builds 400 synthetic records in SQLite across four categories with retention periods of thirty days for video recordings, 180 days for marketing leads, 365 days for chat logs and 730 days for appointments. On 1 October 2026, 96 of 101 video recordings, 82 of 100 marketing leads, 72 of 101 chat logs and 12 of 98 appointments are past retention: 262 of 400 records in total. Zara presents the result as evidence that the paper policy has never been applied, with the video category the clearest gap. The query uses only stdlib sqlite3, so every classmate can rerun it.

13.3 Safe deletion

Deleting 262 records at once is risky. Some may be needed for legal claims or clinical safety. Zara designs a two-step process: records past retention are first marked and hidden from normal use, a weekly report lists them for review by a designated owner, and after fourteen days unflagged records are deleted. Records subject to a legal hold are excluded automatically. The design balances storage limitation with the need to avoid destroying evidence, and every step is logged for accountability so that deletions can be shown later.

13.4 Backups and replicas

Deleting from the main database does not delete from backups and replicas. Zara documents how backups expire on their own schedule and ensures that restored backups are re-checked against the retention table before use. She also checks that read replicas apply deletions promptly. Prof. Holm points out that regulators generally accept that backups cannot be edited record by record, provided they are protected, expire within a defined time and are not restored without reapplying deletions. Zara records this reasoning in the retention design document.

Lab — Records past their retention period

# SQLite: which synthetic records are past their retention period on 2026-10-01?
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE policy(category TEXT PRIMARY KEY, keep_days INTEGER);
INSERT INTO policy VALUES ('video_recording',30),('chat_log',365),('appointment',730),('marketing_lead',180);
CREATE TABLE rec(id INTEGER, category TEXT, created TEXT);
""")
import random; r = random.Random(2)
cats = ['video_recording', 'chat_log', 'appointment', 'marketing_lead']
for i in range(400):
    db.execute("INSERT INTO rec VALUES (?,?,date('2026-10-01', ?))", (i, r.choice(cats), f"-{r.randint(0, 900)} days"))
q = """SELECT r.category, COUNT(*), SUM(julianday('2026-10-01') - julianday(r.created) > p.keep_days)
       FROM rec r JOIN policy p USING(category) GROUP BY r.category ORDER BY 3 DESC"""
tot = 0
for c, n, over in db.execute(q):
    tot += over; print(f"{c:16s} records {n:3d}  past retention {over:3d}")
print(f"total past retention: {tot} of 400")

Run with python3 lab_retention.py. Verified output:

video_recording  records 101  past retention  96
marketing_lead   records 100  past retention  82
chat_log         records 101  past retention  72
appointment      records  98  past retention  12
total past retention: 262 of 400

Worked example

Zara stores retention policy as a table, finds 262 of 400 synthetic records past retention (96 of 101 video recordings), and designs a mark-review-delete process with legal holds and backup handling.

Synthetic case. Records are synthetic and seeded; GDPR Article 5(1)(e) is real.

Errors and limits

Creation date may not be the right start for retention; some periods start at the end of a relationship.

Exercises

• EX037 — Storage limitation. What does Article 5(1)(e) require?
  Show marking criteria

  Rubric: 3 = data kept identifiable no longer than necessary;; 2 = mentions retention;; 1 = wrong;; 0 = none.
• EX038 — Worst category. Which category has the largest share past retention in the lab?
  Show marking criteria

  Rubric: 3 = video recordings: 96 of 101;; 2 = names video;; 1 = names appointments;; 0 = none.
• EX039 — Backup rule. How are backups handled under the retention design?
  Show marking criteria

  Rubric: 3 = protected, expire on schedule, deletions reapplied on restore;; 2 = two points;; 1 = ignores backups;; 0 = none.

Synthesis

Chapter 13 turns policy into queries. Chapter 14 brings privacy into software engineering.

Continue studying — chapter 13 of 50

← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →