JAVA-290 · Lesson 5 of 12 · 38 min
Building the data layer
▶ Video coming soon A video version of this lesson is on the way.
Sprint 1 began on Thursday, June 1, 2028, and Thierry Dumont took the data layer. It was the right first job for two reasons. Every story the team had written needed something new in the database: a problem kind for the mechanics, a time for cancellations, a check-in time for the counter, a waiting list, staff accounts and a queue of text messages. And data is the part of a system that is hardest to change once real customers depend on it. Code can be replaced in a deployment; a column full of bookings has to be migrated, and a mistake in it can be permanent.
The data layer is also where the team's work touched the existing service most directly. The repair_bookings table had been created by migration V5 in the web services course and given its partial unique index and booking references by V6. The counter app had been sending phone bookings through the API since May. Whatever Thierry changed had to keep working with version 1.5.0 of the code, because blue-green deployment means the previous version runs against the new schema for a while, and a rollback runs it again.
From stories to data
Thierry did not start with tables. He started with the stories and their acceptance criteria and wrote down every fact the software would need to remember, then grouped the facts by the thing they describe. This is ordinary data modeling, the same work as in the databases course, but driven by criteria rather than invented in the abstract, which keeps out data nobody needs.
| Story | Facts to remember | Where |
|---|---|---|
| US-02 book | Problem kind (FLAT, BRAKES, GEARS, WHEEL_TRUE, TUNE_UP), a note, how the booking was made, reminder consent | New columns on repair_bookings |
| US-03 cancel | When a booking was cancelled and by whom (customer, counter) | New columns on repair_bookings |
| US-06 check-in | When the customer arrived; missed appointments | A column and a status value |
| US-07 reminder | Which texts must be sent, when, and whether they were | New table notification_outbox |
| US-08 waiting list | Who wants which day, in what order, and any offer made to them | New table waitlist_entries |
| US-06 staff sign-in | Staff accounts, password hashes, roles | New table staff_users |
| N4 retention | When personal data was removed | A column on repair_bookings and waitlist_entries |
Two facts were deliberately left out. Customers have no accounts, so there is no customer table: a booking carries its own name and phone number, exactly as a phone booking always had. And the team did not store email addresses for new bookings, although the column from V5 existed, because no story needed them; storing data with no purpose is a privacy cost with no benefit. The column stayed for the counter app's sake and would be removed in a later contract step.
Rules in the domain first
Before any SQL, Thierry wrote the domain types that would hold these facts, in the domain package where the booking API's Slot, Booking and SlotCalendar already lived. A value that has rules, such as a phone number or a booking reference, deserves its own type, with the rules in its constructor, so that an invalid value cannot exist anywhere in the program. This was the lesson from the design course of making illegal states unrepresentable, and it pays off most at the edges, where text typed by customers enters the system.
Phone numbers were the most important. On Saturday, May 13, the team had watched a caller spell her number twice, and the website would receive numbers typed in every format people use. The limit of two active bookings per phone only works if the same phone is always written the same way, so every number is normalized to one form, the international E.164 format: a plus sign, the country code 1 for North America, and ten digits.
import java.util.*;
public class Main {
record PhoneNumber(String e164) {
PhoneNumber {
if (!e164.matches("\\+1[2-9]\\d{2}[2-9]\\d{6}")) throw new IllegalArgumentException("not a North American number: " + e164);
}
static Optional<PhoneNumber> parse(String typed) {
String digits = typed.replaceAll("[\\s().+-]", "");
if (!digits.matches("\\d+")) return Optional.empty();
if (digits.length() == 11 && digits.startsWith("1")) digits = digits.substring(1);
if (digits.length() != 10) return Optional.empty();
try { return Optional.of(new PhoneNumber("+1" + digits)); }
catch (IllegalArgumentException e) { return Optional.empty(); }
}
String display() { return "(" + e164.substring(2, 5) + ") " + e164.substring(5, 8) + "-" + e164.substring(8); }
}
public static void main(String[] args) {
for (String typed : List.of("905-555-0142", "(905) 555-0142", "905.555.0142", "1 905 555 0142",
"+1 905-555-0142", "555-0142", "905-555-01425", "905-555-O142", "123-555-0142")) {
System.out.printf("%-18s -> %s%n", typed,
PhoneNumber.parse(typed).map(p -> p.e164() + " shown as " + p.display()).orElse("rejected"));
}
}
}905-555-0142 -> +19055550142 shown as (905) 555-0142 (905) 555-0142 -> +19055550142 shown as (905) 555-0142 905.555.0142 -> +19055550142 shown as (905) 555-0142 1 905 555 0142 -> +19055550142 shown as (905) 555-0142 +1 905-555-0142 -> +19055550142 shown as (905) 555-0142 555-0142 -> rejected 905-555-01425 -> rejected 905-555-O142 -> rejected 123-555-0142 -> rejected
What this code does
The record's compact constructor checks the North American numbering rules, an area code and an exchange that do not start with 0 or 1, so no PhoneNumber with a bad value can be created. The parse method removes the separators people type, drops a leading country code 1, and returns an empty Optional for anything that is not ten digits, including the number typed with a letter O instead of a zero. Every accepted format becomes the same E.164 string, which is what makes the two-bookings limit reliable.
The second type was the booking reference. The API already generated references like K7QF-29XM with SecureRandom; the website would ask customers to type them, from a text message or a piece of paper, so the alphabet matters. The API's alphabet had excluded the letters and digits people confuse, O and 0, I and 1, and the parse method accepts lower case and a missing hyphen, because a customer typing on a phone should not be refused for those.
import java.util.*;
public class Main {
record BookingReference(String value) {
static final String ALPHABET = "23456789ABCDEFGHJKLMNPQRSTUVWXYZ";
BookingReference {
if (!value.matches("[" + ALPHABET + "]{4}-[" + ALPHABET + "]{4}")) throw new IllegalArgumentException("bad reference");
}
static Optional<BookingReference> parse(String typed) {
String s = typed.trim().toUpperCase().replace(" ", "").replace("-", "");
if (s.length() != 8) return Optional.empty();
String candidate = s.substring(0, 4) + "-" + s.substring(4);
try { return Optional.of(new BookingReference(candidate)); }
catch (IllegalArgumentException e) { return Optional.empty(); }
}
}
enum ProblemKind {
FLAT("Flat tire"), BRAKES("Brakes"), GEARS("Gears"), WHEEL_TRUE("Wheel wobbles"), TUNE_UP("Tune-up");
final String label;
ProblemKind(String label) { this.label = label; }
}
public static void main(String[] args) {
for (String t : List.of("K7QF-29XM", "k7qf29xm", " K7QF 29XM ", "K7QF-29XO", "K7QF-29X")) {
System.out.printf("%-12s -> %s%n", "'" + t + "'", BookingReference.parse(t).map(BookingReference::value).orElse("not a reference"));
}
int combinations = (int) Math.pow(BookingReference.ALPHABET.length(), 4);
System.out.println("alphabet " + BookingReference.ALPHABET.length() + " characters; 8 of them give "
+ combinations + " squared, about 1.1 trillion references");
for (ProblemKind k : ProblemKind.values()) System.out.print(k + "=" + k.label + " ");
System.out.println();
}
}'K7QF-29XM' -> K7QF-29XM 'k7qf29xm' -> K7QF-29XM ' K7QF 29XM ' -> K7QF-29XM 'K7QF-29XO' -> not a reference 'K7QF-29X' -> not a reference alphabet 32 characters; 8 of them give 1048576 squared, about 1.1 trillion references FLAT=Flat tire BRAKES=Brakes GEARS=Gears WHEEL_TRUE=Wheel wobbles TUNE_UP=Tune-up
What this code does
The reference type accepts only two groups of four characters from a 32-character alphabet without O, 0, I or 1, while parse forgives case, spaces and a missing hyphen before checking. A reference containing the letter O is rejected even though it looks right, because the generator never produces one. ProblemKind uses the five kinds from the shop's paper repair tickets, each with the label the booking form shows, so that Tamsin's morning list and the tickets use the same words.
The number in the output answered a question Noemi had asked: could someone guess a reference? With about a trillion possible values and a few hundred real bookings, a random guess succeeds roughly once in a few billion tries, and lesson 9 limits how many tries anyone gets. The reference alone is not even enough to see a booking, since US-03.3 also requires the phone number.
Migrations that keep the old code working
With the domain clear, Thierry wrote the migrations. The rule from the deployment course was expand and contract: each migration must work with the code version before it, so that blue-green deployment and rollback are safe. In practice this means that in the first release new columns are added as nullable or with defaults, nothing is renamed and nothing the old code reads is dropped. Contract steps, which remove what is no longer needed, come in a later release, once no running version needs the old shape.
-- V7__booking_site_columns.sql
ALTER TABLE repair_bookings ADD COLUMN problem_kind VARCHAR(12);
ALTER TABLE repair_bookings ADD COLUMN source VARCHAR(10) NOT NULL DEFAULT 'COUNTER';
ALTER TABLE repair_bookings ADD COLUMN reminder_opt_in BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE repair_bookings ADD COLUMN cancelled_at TIMESTAMP;
ALTER TABLE repair_bookings ADD COLUMN cancelled_by VARCHAR(10);
ALTER TABLE repair_bookings ADD COLUMN checked_in_at TIMESTAMP;
ALTER TABLE repair_bookings ADD COLUMN anonymized_at TIMESTAMP;
ALTER TABLE repair_bookings ADD CONSTRAINT repair_bookings_problem_kind
CHECK (problem_kind IN ('FLAT', 'BRAKES', 'GEARS', 'WHEEL_TRUE', 'TUNE_UP'));
ALTER TABLE repair_bookings ADD CONSTRAINT repair_bookings_source
CHECK (source IN ('COUNTER', 'PHONE', 'WEB'));
CREATE INDEX repair_bookings_phone_active ON repair_bookings (phone, start_at)
WHERE status = 'BOOKED';What this code does
Every new column is either nullable or has a default, so the INSERT statements of version 1.5.0, which know nothing about them, still work, and old rows get sensible values. The check constraints accept NULL, which a CHECK treats as passing, so bookings made by the old code without a problem kind are allowed. The partial index serves the limit of two active bookings per phone, counting only rows still BOOKED.
The waiting list got its own table. Its interesting rules are that a phone number may wait for a given day only once, and that the order of waiting is the order of joining, so the creation time is part of the data, not decoration. An offer, made when a start frees up, is a time-limited hold on that start, recorded on the entry.
-- V8__waitlist.sql
CREATE TABLE waitlist_entries (
id BIGSERIAL PRIMARY KEY,
day DATE NOT NULL,
customer VARCHAR(80) NOT NULL,
phone VARCHAR(20) NOT NULL,
created_at TIMESTAMP NOT NULL,
offered_start TIMESTAMP,
offer_expires TIMESTAMP,
status VARCHAR(10) NOT NULL DEFAULT 'WAITING'
CHECK (status IN ('WAITING', 'OFFERED', 'BOOKED', 'EXPIRED', 'LEFT')),
anonymized_at TIMESTAMP
);
CREATE UNIQUE INDEX waitlist_one_per_phone_day ON waitlist_entries (day, phone)
WHERE status IN ('WAITING', 'OFFERED');
CREATE INDEX waitlist_queue ON waitlist_entries (day, created_at) WHERE status = 'WAITING';What this code does
The partial unique index stops the same phone from waiting twice for the same day while letting it join again after an earlier entry ended. The queue index matches the query that finds the next person to offer a start to, the oldest WAITING entry for a day. The status column lists the entry's life: waiting, offered a start, booked it, let the offer expire or left the list.
-- V9__staff_users.sql
CREATE TABLE staff_users (
username VARCHAR(40) PRIMARY KEY,
display_name VARCHAR(80) NOT NULL,
password_hash VARCHAR(200) NOT NULL,
role VARCHAR(10) NOT NULL CHECK (role IN ('COUNTER', 'MECHANIC', 'MANAGER')),
enabled BOOLEAN NOT NULL DEFAULT TRUE,
failed_logins INTEGER NOT NULL DEFAULT 0,
locked_until TIMESTAMP
);
-- V10__notification_outbox.sql
CREATE TABLE notification_outbox (
id BIGSERIAL PRIMARY KEY,
kind VARCHAR(20) NOT NULL,
phone VARCHAR(20) NOT NULL,
body VARCHAR(320) NOT NULL,
due_at TIMESTAMP NOT NULL,
sent_at TIMESTAMP,
attempts INTEGER NOT NULL DEFAULT 0,
booking_id BIGINT REFERENCES repair_bookings(id)
);
CREATE INDEX notification_outbox_due ON notification_outbox (due_at) WHERE sent_at IS NULL;What this code does
Staff users store a password hash, never a password, in a column wide enough for any of Spring Security's encoded formats, and the failed-login count supports the lockout rule from the security review. The outbox implements ADR-004: a booking writes a row in the same transaction, and a scheduled job sends due rows later, so a slow SMS provider can never fail a booking. The partial index keeps the job's query cheap however many sent rows accumulate.
Thierry checked the most important constraint on a copy of the sample data before trusting it. The examples below use SQLite, which supports the same partial unique index, against a small sample of the booking table, so the results are real; the PostgreSQL migrations differ only in their types. The first query shows the bookings for Saturday, June 10, one of them cancelled at the same start as an active one.
SELECT id, reference, start_at, status
FROM repair_bookings
WHERE start_at LIKE '2028-06-10%'
ORDER BY start_at, id;What this code does
Booking 2204 was cancelled at 09:30 and 2205 then took the same start, which the partial index allows because it ignores cancelled rows. That is why the index from V6 is partial: a cancelled booking must give its start back.
| id | reference | start_at | status |
|---|---|---|---|
| 2203 | P3WD-8HKT | 2028-06-10 09:00 | BOOKED |
| 2204 | M9RC-4NVA | 2028-06-10 09:30 | CANCELLED |
| 2205 | T2HX-7QPE | 2028-06-10 09:30 | BOOKED |
| 2206 | W8LB-3FZK | 2028-06-10 11:00 | BOOKED |
INSERT INTO repair_bookings (id, reference, customer, phone, start_at, bike, problem, status)
VALUES (2207, 'H4KP-9TRW', 'Test Customer', '+15555550199', '2028-06-10 09:30', 'Test bike', 'Test', 'BOOKED');What this code does
A second active booking for the 09:30 start is refused by the database itself, even if a bug in the code let it through. This is the second line of defense for the rule that one start holds one bike; the service checks first and gives a friendly message.
Error: UNIQUE constraint failed: repair_bookings.start_atChecking migrations before they run
Migrations are code, and the team reviewed them like code, but some rules are easy to check mechanically. Flyway itself refuses to run if an applied migration file has been edited, because it stores a checksum of each file. Thierry added a check of his own for the expand-and-contract rule: a migration in the same release as the code that needs it may add, but may not drop or rename anything that the previous release still uses. It runs in the build over the new migration files and the list of columns the previous release reads, which the team generated from the JPA entities of the last tag.
import java.util.*;
import java.util.regex.*;
public class Main {
static final Pattern DESTRUCTIVE = Pattern.compile(
"(?i)\\b(DROP\\s+(TABLE|COLUMN)\\s+(\\w+)|RENAME\\s+(COLUMN\\s+)?(\\w+)|ALTER\\s+COLUMN\\s+(\\w+)\\s+(SET\\s+NOT\\s+NULL|TYPE))");
static List<String> problems(String file, String sql, Set<String> usedByPrevious) {
List<String> out = new ArrayList<>();
Matcher m = DESTRUCTIVE.matcher(sql);
while (m.find()) {
String name = m.group(3) != null ? m.group(3) : m.group(5) != null ? m.group(5) : m.group(6);
if (usedByPrevious.contains(name.toLowerCase())) out.add(file + ": '" + m.group(1).trim() + "' breaks the previous release");
}
if (sql.matches("(?is).*ADD\\s+COLUMN\\s+\\w+\\s+\\w+(\\(\\d+\\))?\\s+NOT\\s+NULL(?!\\s+DEFAULT).*"))
out.add(file + ": NOT NULL column without a default breaks the previous release's inserts");
return out;
}
public static void main(String[] args) {
Set<String> used = Set.of("repair_bookings", "customer", "phone", "email", "start_at", "bike", "problem", "status", "reference");
Map<String, String> files = new LinkedHashMap<>();
files.put("V7", "ALTER TABLE repair_bookings ADD COLUMN source VARCHAR(10) NOT NULL DEFAULT 'COUNTER';");
files.put("V8", "CREATE TABLE waitlist_entries (id BIGSERIAL PRIMARY KEY);");
files.put("V9-draft", "ALTER TABLE repair_bookings DROP COLUMN email;");
files.put("V10-draft", "ALTER TABLE repair_bookings ADD COLUMN problem_kind VARCHAR(12) NOT NULL;");
files.put("V11-draft", "ALTER TABLE repair_bookings RENAME COLUMN customer TO customer_name;");
int bad = 0;
for (var e : files.entrySet()) {
List<String> p = problems(e.getKey(), e.getValue(), used);
bad += p.size();
if (p.isEmpty()) System.out.println(e.getKey() + ": compatible with the previous release");
p.forEach(System.out::println);
}
System.out.println(bad + " problem(s)");
}
}V7: compatible with the previous release V8: compatible with the previous release V9-draft: 'DROP COLUMN email' breaks the previous release V10-draft: NOT NULL column without a default breaks the previous release's inserts V11-draft: 'RENAME COLUMN customer' breaks the previous release 3 problem(s)
What this code does
The regular expression finds the statements that can break the previous code, dropping, renaming or tightening a column, and the check reports one only if the previous release still uses that name. A new NOT NULL column without a default is caught separately, because the old code's inserts would leave it empty and fail. V9-draft was Thierry's own first attempt to drop the email column, which the counter app still writes, and it became a contract step for a later release instead.
Mapping to JPA
The booking API mapped repair_bookings to a JPA entity, and the new columns became new fields on it. Hibernate checks at start-up that the entities match the tables Flyway created, with ddl-auto set to validate, so a missing or mistyped column fails the application's start rather than a customer's request. The waiting list got its own entity and Spring Data repository, behind a port in the application package so that the domain and the site adapter never see JPA.
@Entity
@Table(name = "waitlist_entries")
public class WaitlistEntryEntity {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false) private LocalDate day;
@Column(nullable = false, length = 80) private String customer;
@Column(nullable = false, length = 20) private String phone;
@Column(name = "created_at", nullable = false) private LocalDateTime createdAt;
@Column(name = "offered_start") private LocalDateTime offeredStart;
@Column(name = "offer_expires") private LocalDateTime offerExpires;
@Enumerated(EnumType.STRING) @Column(nullable = false, length = 10) private WaitlistStatus status;
@Version private long version;
protected WaitlistEntryEntity() { } // for JPA
WaitlistEntry toDomain() {
return new WaitlistEntry(id, day, customer, new PhoneNumber(phone), createdAt, status, offeredStart, offerExpires);
}
}
interface WaitlistJpaRepository extends JpaRepository<WaitlistEntryEntity, Long> {
Optional<WaitlistEntryEntity> findFirstByDayAndStatusOrderByCreatedAtAsc(LocalDate day, WaitlistStatus status);
long countByDayAndStatus(LocalDate day, WaitlistStatus status);
boolean existsByDayAndPhoneAndStatusIn(LocalDate day, String phone, Collection<WaitlistStatus> statuses);
}What this code does
The entity mirrors the V8 table and stores the status as its name, so the database's check constraint and the Java enum agree. toDomain builds the domain record, and constructing a PhoneNumber there means a corrupt value in the database fails loudly instead of spreading. The derived query methods are named after what the waiting list needs: the next person for a day, how many are waiting and whether a phone is already on the list.
The port and its adapters are the shape the booking API used throughout. The application package declares what it needs from storage in its own terms; the JPA adapter implements it for production; and an in-memory adapter implements it for fast tests and for the examples in these lessons. Writing the in-memory version first, as Thierry did, is also a cheap way to check that the port's methods make sense before any SQL exists.
import java.time.*;
import java.util.*;
public class Main {
enum WaitlistStatus { WAITING, OFFERED, BOOKED, EXPIRED, LEFT }
record WaitlistEntry(long id, LocalDate day, String phone, LocalDateTime createdAt, WaitlistStatus status) {
WaitlistEntry with(WaitlistStatus s) { return new WaitlistEntry(id, day, phone, createdAt, s); }
}
interface Waitlist {
WaitlistEntry join(LocalDate day, String phone, LocalDateTime now);
Optional<WaitlistEntry> next(LocalDate day);
void update(WaitlistEntry e);
int position(long id);
}
static class InMemoryWaitlist implements Waitlist {
private final Map<Long, WaitlistEntry> rows = new TreeMap<>();
private long nextId = 1;
public WaitlistEntry join(LocalDate day, String phone, LocalDateTime now) {
boolean already = rows.values().stream().anyMatch(e -> e.day().equals(day) && e.phone().equals(phone)
&& (e.status() == WaitlistStatus.WAITING || e.status() == WaitlistStatus.OFFERED));
if (already) throw new IllegalStateException("already waiting for " + day);
WaitlistEntry e = new WaitlistEntry(nextId++, day, phone, now, WaitlistStatus.WAITING);
rows.put(e.id(), e);
return e;
}
public Optional<WaitlistEntry> next(LocalDate day) {
return rows.values().stream().filter(e -> e.day().equals(day) && e.status() == WaitlistStatus.WAITING)
.min(Comparator.comparing(WaitlistEntry::createdAt).thenComparing(WaitlistEntry::id));
}
public void update(WaitlistEntry e) { rows.put(e.id(), e); }
public int position(long id) {
WaitlistEntry me = rows.get(id);
return 1 + (int) rows.values().stream().filter(e -> e.day().equals(me.day()) && e.status() == WaitlistStatus.WAITING
&& e.createdAt().isBefore(me.createdAt())).count();
}
}
public static void main(String[] args) {
Waitlist w = new InMemoryWaitlist();
LocalDate sat = LocalDate.of(2028, 6, 24);
LocalDateTime t = LocalDateTime.of(2028, 6, 20, 12, 0);
WaitlistEntry a = w.join(sat, "+15555550117", t);
WaitlistEntry b = w.join(sat, "+15555550163", t.plusMinutes(7));
WaitlistEntry c = w.join(sat, "+15555550188", t.plusMinutes(30));
System.out.println("positions: " + w.position(a.id()) + ", " + w.position(b.id()) + ", " + w.position(c.id()));
try { w.join(sat, "+15555550163", t.plusHours(1)); } catch (IllegalStateException e) { System.out.println("refused: " + e.getMessage()); }
WaitlistEntry first = w.next(sat).orElseThrow();
w.update(first.with(WaitlistStatus.OFFERED));
System.out.println("offered to entry " + first.id() + "; next now " + w.next(sat).map(WaitlistEntry::id).orElse(-1L)
+ "; entry " + c.id() + " is now position " + w.position(c.id()));
}
}positions: 1, 2, 3 refused: already waiting for 2028-06-24 offered to entry 1; next now 2; entry 3 is now position 2
What this code does
The Waitlist interface is the port, written in the application's terms, and InMemoryWaitlist is an adapter that keeps rows in a TreeMap by ID. It enforces the same rule as the partial unique index, one WAITING or OFFERED entry per phone and day, so tests that use it behave like the database. Once the first entry is offered a start it leaves the queue, and the others move up one place, which is the position a customer sees on the waiting page.
Counting what the limit needs
US-04, the limit of two active bookings per phone, needed one query, and the partial index in V7 was designed for it. The query counts bookings for a phone that are still BOOKED and start in the future; a booking in the past, even if nobody updated its status, should not stop someone from booking again, which was criterion US-04.3. The repository method is derived from its name.
public interface BookingRepository extends JpaRepository<BookingEntity, Long> {
@Query("""
select count(b) from BookingEntity b
where b.phone = :phone and b.status = 'BOOKED' and b.startAt > :now
""")
long countActiveForPhone(String phone, LocalDateTime now);
}What this code does
The JPQL query compares the phone in its normalized E.164 form, which is why normalizing at the edge mattered, and passes the current time from the injected Clock rather than calling now() in the query, so tests can fix it. The condition on status matches the partial index's WHERE clause, so PostgreSQL can answer from the index alone. The method replaced the version in 1.5.0, which had not excluded bookings in the past.
Removing personal data on time
N4 says customer names and phone numbers are removed 30 days after the appointment. The team chose to anonymize rather than delete: the booking row stays, with its date, problem kind, source and status, because Odile's weekly figures and Tamsin's planning need history, but the name becomes a placeholder and the phone number is cleared. A nightly job does the work. The rule for which rows qualify has edge cases worth testing: cancelled bookings count from their start time too, a booking that is still BOOKED long after its time is clearly stale, and rows already anonymized must be skipped so the job can run twice safely.
import java.time.*;
import java.util.*;
public class Main {
record Row(long id, LocalDateTime start, String status, String customer, LocalDateTime anonymizedAt) {}
static List<Long> due(List<Row> rows, LocalDate today, int days) {
LocalDateTime cutoff = today.minusDays(days).atStartOfDay();
return rows.stream()
.filter(r -> r.anonymizedAt() == null)
.filter(r -> r.start().isBefore(cutoff))
.map(Row::id).toList();
}
public static void main(String[] args) {
LocalDateTime done = LocalDateTime.of(2028, 6, 1, 2, 0);
List<Row> rows = List.of(
new Row(2101, LocalDateTime.of(2028, 5, 2, 10, 0), "COLLECTED", "Ana", null),
new Row(2102, LocalDateTime.of(2028, 5, 2, 10, 30), "CANCELLED", "Ravi", null),
new Row(2103, LocalDateTime.of(2028, 5, 3, 9, 0), "COLLECTED", "Mei", null),
new Row(2104, LocalDateTime.of(2028, 4, 29, 9, 30), "COLLECTED", "-", done),
new Row(2105, LocalDateTime.of(2028, 4, 15, 11, 0), "BOOKED", "Theo", null),
new Row(2106, LocalDateTime.of(2028, 6, 1, 9, 0), "BOOKED", "Priya", null));
LocalDate today = LocalDate.of(2028, 6, 2);
System.out.println("run on " + today + ", cutoff " + today.minusDays(30) + ": anonymize " + due(rows, today, 30));
System.out.println("run on " + today.plusDays(1) + ": anonymize " + due(rows, today.plusDays(1), 30));
}
}run on 2028-06-02, cutoff 2028-05-03: anonymize [2101, 2102, 2105] run on 2028-06-03: anonymize [2101, 2102, 2103, 2105]
What this code does
The cutoff is midnight at the start of the day 30 days before the run, so a booking qualifies only once a whole 30 days have passed since its day began. Rows that already have an anonymization time are skipped, which makes the job safe to run twice. On June 2 the bookings from May 2 qualify whatever their status, including the stale BOOKED row from April, and the next night's run adds booking 2103 from May 3.
@Scheduled(cron = "0 15 2 * * *", zone = "America/Toronto")
@Transactional
public void anonymizeOldBookings() {
LocalDateTime cutoff = LocalDate.now(clock).minusDays(properties.retentionDays()).atStartOfDay();
int n = jdbc.update("""
update repair_bookings
set customer = 'removed', phone = '', email = null, problem = null, anonymized_at = :now
where anonymized_at is null and start_at < :cutoff
""", Map.of("now", LocalDateTime.now(clock), "cutoff", cutoff));
log.info("retention: anonymized {} bookings before {}", n, cutoff.toLocalDate());
}What this code does
The job runs at 02:15 every night in the shop's time zone and performs one UPDATE, which is much faster than loading entities when many rows qualify. It clears the free-text problem note too, because customers sometimes type personal details there. The log line records only a count and a date, never which customers were affected.
The zone in the annotation, America/Toronto, is the shop's own, so the job runs at a quarter past two in Hamilton whatever time zone the server's clock is set to. Leif asked for one addition: a metric with the number of rows anonymized each night, so that a job that silently stopped working would show up on the dashboard as a flat line where there should be small daily steps.
Data for testing and staging
The deployment course's review had left an uncomfortable finding: staging used a copy of production data, with real customers' phone numbers. Thierry fixed it in this sprint, because the website would add more personal data to staging than ever. Staging and every developer's laptop now get generated test data: bookings with invented names and phone numbers from the 555-01 range, which North American numbering reserves for fiction, spread over the coming weeks in realistic patterns. The generator is seeded, so it produces the same data every time, which makes bugs reproducible.
import java.time.*;
import java.util.*;
public class Main {
static final String[] FIRST = {"Ada", "Bo", "Cleo", "Dev", "Eli", "Fay", "Gus", "Hana", "Ivo", "Jun"};
static final String[] BIKES = {"City bike", "Road bike", "Cargo bike", "Kids' bike", "Mountain bike"};
static final String[] KINDS = {"FLAT", "BRAKES", "GEARS", "WHEEL_TRUE", "TUNE_UP"};
public static void main(String[] args) {
Random rnd = new Random(290);
LocalDate day = LocalDate.of(2028, 6, 13); // a Tuesday
int made = 0;
Map<String, Integer> perKind = new TreeMap<>();
for (int d = 0; d < 5; d++, day = day.plusDays(1)) {
int filled = 0;
for (LocalTime t = LocalTime.of(9, 0); !t.isAfter(LocalTime.of(15, 0)); t = t.plusMinutes(30)) {
if (rnd.nextInt(100) >= 55) continue; // about 55 of 100 starts booked
String kind = KINDS[rnd.nextInt(KINDS.length)];
perKind.merge(kind, 1, Integer::sum);
String phone = String.format("+1555555%04d", 100 + rnd.nextInt(100));
if (made < 3) System.out.println(day + " " + t + " " + FIRST[rnd.nextInt(FIRST.length)] + " Test "
+ phone + " " + BIKES[rnd.nextInt(BIKES.length)] + " " + kind);
made++; filled++;
}
System.out.println(" " + day.getDayOfWeek() + ": " + filled + " of 13 starts booked");
}
System.out.println(made + " bookings; by kind " + perKind);
}
}2028-06-13 09:30 Hana Test +15555550113 City bike TUNE_UP
2028-06-13 10:00 Ivo Test +15555550136 Road bike GEARS
2028-06-13 10:30 Gus Test +15555550140 Mountain bike GEARS
TUESDAY: 8 of 13 starts booked
WEDNESDAY: 6 of 13 starts booked
THURSDAY: 7 of 13 starts booked
FRIDAY: 4 of 13 starts booked
SATURDAY: 7 of 13 starts booked
32 bookings; by kind {BRAKES=7, FLAT=4, GEARS=8, TUNE_UP=7, WHEEL_TRUE=6}What this code does
The seed 290 makes the sequence of random numbers, and therefore the data, identical on every run and every machine. Each of the 13 half-hourly starts from 09:00 to 15:00 is booked with a probability of about 55 in 100, roughly the share Odile reported for a normal June week, and every phone number falls in the reserved 555-0100 to 555-0199 range. Every customer's last name is Test, so a generated booking can never be mistaken for a real one in a screenshot or a log.
By Wednesday, June 14, the first sprint review, the migrations had run on staging through the pipeline, the generated data had replaced the copy of production, and Leif had deleted the old copy and its backups. The walking skeleton walked: Hamza's free-times page read from the new schema, on staging, through Caddy. Odile's only comment on the data layer was that she had not noticed it, which Ingrid said was the best review a data layer can get.
Exercises
Try it yourself
Normalize phone numbers
Complete normalize so that it returns the E.164 form +1 followed by ten digits, or invalid. Remove spaces, hyphens, dots, parentheses and plus signs; allow an optional leading country code 1; reject anything else that is not exactly ten digits, and reject area codes that start with 0 or 1.
import java.util.*;
public class Main {
static String normalize(String typed) {
return typed;
}
public static void main(String[] args) {
for (String t : List.of("555-555-0142", "(555) 555 0142", "+1.555.555.0142", "1-555-555-0142",
"555-0142", "155-555-0142", "555-555-014x"))
System.out.println(t + " -> " + normalize(t));
}
}Try it yourself
Which rows are due for anonymizing?
Each row is id startDate anonymized, where anonymized is yes or no. Complete main so that it prints the IDs of rows not yet anonymized whose start date is strictly before the cutoff, which is today minus days, then the count, as in the expected output.
import java.time.*;
import java.util.*;
public class Main {
public static void main(String[] args) {
List<String> rows = List.of("2101 2028-05-02 no", "2102 2028-05-03 no", "2103 2028-04-20 yes",
"2104 2028-04-30 no", "2105 2028-06-01 no");
LocalDate today = LocalDate.of(2028, 6, 2);
int days = 30;
System.out.println("due: []");
}
}Common mistakes
MistakeDesigning tables from imagination.
InsteadList the facts the acceptance criteria need and group them; leave out data no story requires.
MistakeStoring phone numbers exactly as typed.
InsteadNormalize at the edge into one form, such as E.164, inside a value type that cannot hold an invalid number.
MistakeRenaming or dropping a column in the release that stops using it.
InsteadExpand first with nullable or defaulted columns, and contract in a later release once no running version needs the old shape.
MistakeRelying on the service alone to prevent double bookings.
InsteadBack the rule with a constraint, such as a partial unique index, so a bug cannot break it.
MistakeCopying production data to staging.
InsteadGenerate seeded, obviously fake test data in reserved ranges.
MistakeDeleting rows that reports still need.
InsteadAnonymize personal fields on schedule and keep the non-personal history, with an idempotent job.
Summary and key terms
- Thierry built the data layer in sprint 1 from the facts the acceptance criteria needed, leaving out data with no purpose such as new email addresses.
- Rules live first in domain value types: a PhoneNumber record that normalizes every typed format to E.164 and refuses invalid numbers, a BookingReference with an alphabet that avoids confusable characters, and the five problem kinds from the repair tickets.
- Migrations V7 to V10 add columns to repair_bookings and create the waiting list, staff users and notification outbox tables, all expand-only so version 1.5.0 keeps working during blue-green deployment and rollback; a build check catches drops, renames and NOT NULL columns without defaults.
- Partial unique indexes enforce one active booking per start and one waiting entry per phone and day.
- JPA entities sit behind application ports with in-memory adapters for tests.
- Personal data is anonymized nightly after 30 days by an idempotent job, and staging now uses seeded, generated test data instead of a copy of production.
- Data modeling
- Deciding which facts a system stores and how they are grouped into tables.
- Value type
- A small type, such as a record, whose constructor enforces the rules for a value.
- E.164
- The international phone number format: a plus sign, a country code and the national number.
- Expand and contract
- Changing a schema in compatible steps: add first, switch code, remove in a later release.
- Partial unique index
- A unique index that applies only to rows matching a condition.
- Port
- An interface in the application's own terms for something it needs, such as storage.
- In-memory adapter
- An implementation of a port that keeps data in memory, used in tests.
- Anonymization
- Removing or replacing personal fields while keeping the rest of a record.
- Idempotent
- Giving the same result when run more than once.
- Seeded test data
- Generated data from a fixed random seed, identical on every run.
Practice problems
Work each problem on paper first, then reveal the answer to check.
Why must V7 work with version 1.5.0 of the code?
Show answer
Blue-green deployment runs the previous version against the new schema for a while, and a rollback runs it again.
Why is the phone number normalized before the limit of two bookings is checked?
Show answer
So the same phone typed in different formats counts as one number.
Why does the reference alphabet leave out O, 0, I and 1?
Show answer
Customers confuse them when typing a reference from a message or paper.
Why did the team anonymize bookings instead of deleting them?
Show answer
The weekly figures and planning need the non-personal history, while names and phone numbers must go after 30 days.
What makes the test data generator reproducible?
Show answer
A fixed seed for its random numbers, so every run produces the same bookings.
Lesson quiz · 5 questions
Score 60% or more to complete this lesson.
Create a free account to save your progress, take quizzes and earn a certificate.