Chapter 12 spent several pages arguing that the only guard against assigning the same product to the same category twice is a unique constraint on (category_id, product_id). It rejected a pessimistic read lock on the grounds that a lock delays a conflicting change rather than preventing it, settled on reading current state to classify a batch, and said plainly that the database is the only real arbiter of the race that remains.

That constraint did not exist. Neither did the foreign key with ON DELETE CASCADE that the same chapter called a backstop, nor the BINARY(16) column that the version 7 UUID decision quietly depends on. Three chapters of reasoning had been written against a schema that was still a sentence in a paragraph, and the previous chapter configured Flyway to apply migrations without there being a migration to apply.

Writing it is where several earlier decisions stop being arguments and become something a database will enforce or refuse. A few of them also turn out to have been wrong, or at least incomplete, in ways only visible once the columns have types.

One file, or one per table

Before any of that configuration does anything, there has to be something in classpath:db/migrations, and there are two questions before the SQL: where the file lives and how many files there are.

It lives in dao-impl, with the entities, rather than in the application module that configured Flyway. The application module owns the wiring and says where to look, the module that owns persistence owns the shape of it. A schema in application would mean that the module which knows what a ProductEntity is and the module which knows what the products table is are two different modules, and every column change would be a change in both. classpath: is what makes this work without anyone declaring it: Flyway looks on the classpath and dao-impl is on it, so the application never names the module that holds the migration.

The second question is how many files it should be. One per table is the tempting answer, because it mirrors the code, where each entity has its own class. I went with one, and the reason is that a migration is not a unit of structure, it is a unit of change. These four tables are not four changes. products has a foreign key to product_specifications, category_assignments has foreign keys to two of the others, and that fixes the only order they can be created in. Split into four files, two of them cannot be applied on their own at all and a third can only follow them. Flyway would run the four in sequence and arrive at the same schema, so nothing would break, but the history would now record four events where one happened, and a developer reading a create_products migration in isolation would be looking at a fragment of a decision rather than a decision.

The rule that follows is short enough to state once and then follow for the rest of the project. One file per change, named after the change. The baseline is a single file because the baseline is one change: the schema this application starts life with, which is why it is called V01.00.00__init.sql rather than named after a table. The three-part version is a release number rather than a counter, so a migration's name says which version of the product introduced it, and ordering by filename still gives Flyway the sequence it needs. Everything after it gets its own file, whether it touches one table or four.

CREATE TABLE `categories` ( `id` BINARY(16) NOT NULL, `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), `created_by_id` BINARY(16) NOT NULL, `updated_by_id` BINARY(16) NOT NULL, `version` INTEGER NOT NULL DEFAULT 0, `name` VARCHAR(255) NOT NULL, `description` VARCHAR(1000) DEFAULT NULL, `status` ENUM ('INACTIVE', 'ACTIVE', 'ARCHIVED') NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `uk_categories_name` UNIQUE (`name`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

That is categories, and its first six columns are the audit base classes rendered into SQL. They repeat verbatim in products, which adds a name, a SKU, a price, a status and a nullable specification_id, and in product_specifications, which adds a dimension and a weight. category_assignments is the exception and carries only three of them, having dropped updated_at, updated_by_id and version along with the base class that declares them, and added the two columns it exists for. The whole file is in the repository at this chapter's tag, and the part of it worth reading inline is the bottom of that last table:

 PRIMARY KEY (`id`), CONSTRAINT `uk_assignments_category_product` UNIQUE (`category_id`, `product_id`), CONSTRAINT `fk_assignments_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`), CONSTRAINT `fk_assignments_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE

Sixteen bytes, in the right order

The identifier column is BINARY(16), which is sixteen bytes rather than the thirty-six a CHAR(36) would take, and this is where the @UuidGenerator(style = VERSION_7) decision from Chapter 12 collects the rest of its payment. In InnoDB the primary key is the clustered index, so rows are physically stored in primary key order and every insert goes wherever its key sorts. A version 4 UUID is random, which means inserts land uniformly across the whole index, and a table larger than the buffer pool starts reading pages from disk in order to write to them. A version 7 UUID carries a millisecond timestamp in its leading bits, so in the byte ordering that BINARY(16) preserves, successive inserts sort next to each other and the writes concentrate on the right-hand edge of the index. Chapter 12 wanted version 7 because in-memory id assignment is what lets Hibernate batch inserts at all. This is the second half of the same decision, and it only holds if the column preserves the ordering, which the hyphenated text form in a CHAR(36) does not.

A specification with a life of its own

The specification lives in its own table rather than as two more columns on products, and that is a modelling decision rather than a normalisation reflex. A specification is a thing with an identity and a life of its own: it is created, it is amended, and it is audited, which is why it carries the same audit columns as everything else here. Flattening it into products would mean a product row that cannot say who last changed its dimensions as distinct from who last changed its price. The cost is a join on every read that needs it and a nullable foreign key, since a product can exist before anyone has measured it.

The uniqueness on specification_id is what makes the relationship actually one to one. A foreign key on its own permits two products to point at the same specification row, and the day that happens, editing one product's weight silently edits another's. The unique constraint forbids it at the only level that can be trusted, and it conveniently supplies the index InnoDB requires on the referencing side of the foreign key, so the constraint earns its keep twice. It also does not get in the way of the nullable column, because MySQL treats each NULL as distinct for uniqueness purposes, so any number of products can be waiting to be measured while no two can share a measurement.

What the schema cannot express is the other half of that relationship. Deleting a product leaves its specification behind as an orphan, because the foreign key points from product to specification and a cascade in that direction would be backwards. There is no ON DELETE clause that fixes this, which is why the association on ProductEntity carries orphanRemoval = true and CascadeType.ALL, leaving Hibernate to issue the second delete. Worth being explicit that this is now a rule living in the mapping rather than in the database, which means a row deleted by hand in a SQL client will leave the orphan the application would have cleaned up. That is an acceptable asymmetry as long as it is a known one.

The column that changes behaviour

The version column is the one addition here that changes behaviour rather than describing it. Paired with a @Version private Integer version on AbstractUpdatableEntity, it gives Hibernate an optimistic lock: every update carries the version it read in its WHERE clause and increments it, so an update that matches no rows means someone else got there first. This is the problem Chapter 12 looked at and declined to solve with a pessimistic read lock, on the grounds that holding a lock only delays a conflicting change rather than preventing it. Optimistic locking makes a narrower and more useful promise. It does not order concurrent operations, it detects a lost update: two requests both read a product at version 3, both compute a new state from it, and without this column the second write silently discards the first. With it, the second write fails and the caller finds out.

That failure needs somewhere to go, and as the exception handler stands at the end of Chapter 11, it does not have one. Spring translates the Hibernate failure into ObjectOptimisticLockingFailureException, which descends from TransientDataAccessException rather than from DataIntegrityViolationException, so the integrity-violation handler that catches the unique-constraint race does not catch this one. It falls through to the Throwable mapping and becomes a 500, which is wrong in the way that matters: the request was not malformed and the server is not broken, the state moved underneath it, and retrying is a reasonable thing for the caller to do. It belongs with the other conflicts, as a 409, and adding it is a two-line change to a class that already has the shape for it. Which base class gets the field is worth deciding on purpose rather than by instinct, and the instinct is to put it next to the identifier so everything in the system has one. A version number is a guard on update, so the concept arrives with the class that introduces updating, exactly as Chapter 10 argued for changeStatus(). One level higher and every creation-only table carries a column that can never hold anything but zero. category_assignments is such a table, which is why it is the only one here without the column, and that absence is the schema agreeing with the code rather than an omission.

There is a consequence of that choice sitting directly on top of Chapter 12's batching. Spring Data decides between persist and merge by looking at the version property first when that property is of a non-primitive type, treating null as new, and falling back to inspecting the identifier when there is no such property. So products and categories are classified by their version, while CategoryAssignmentEntity, having none, is classified by its id. Both land on persist, and that is precisely what keeps saveAllAndFlush batching: a merge would issue a select before every insert, turning a two-hundred-item assignment from four round trips into two hundred selects and two hundred inserts. Nothing would break, no test would fail, and the performance decision from the previous chapter would have quietly gone. Giving the assignment entity a version it does not need, or assigning its id in the transformer before persistence, are both ways to cause that. The field has to be a wrapper rather than a primitive for the same reason. int cannot be null, so Spring Data stops asking the version and falls back to the identifier, which here still happens to answer correctly because Hibernate assigns the id during persistence. The behaviour would be right by accident rather than by design, and the next change to how ids are assigned would quietly take it away.

It is also the only field in these classes with no getter and no setter, and that is intentional rather than an oversight. The annotations in this project sit on fields, so Hibernate reads and writes through reflection and needs neither. What a setter would add is the ability for something in the application to assign a version, and assigning a version is the only way to defeat the lock from the inside: a transformer that mechanically copies every property would eventually copy a number read minutes earlier and turn the check into a comparison with itself. A version is established by persistence, not set by callers, which is the same argument Chapter 10 made for changeStatus() over a status setter. A short comment saying the field exists for Hibernate is worth adding, because a field with no accessors looks unused to every static analyser and to the next person tidying up.

Two things changed behaviour the moment the column was mapped, and neither is in the paragraph above. The first is deletion. The DAO deletes by loading the row and handing the entity to the repository, and once there is a version Hibernate puts it in the DELETE statement's WHERE clause, so a row that changed between the load and the delete now refuses to be deleted. That is the correct answer - you are deleting something that is no longer what you read, but it's a second path to the same unhandled exception, which makes the missing handler slightly more than a convenience.

The second was a bug the column exposed rather than caused. Updating a product used to replace its specification with a freshly constructed entity built from the domain object. With orphanRemoval that already meant a delete and an insert where an update was intended, which was wasteful and worked. With a version it stops working: the new entity carries an identifier that already exists and a version that is null, so it is classified as new, and persist with an occupied identifier is not a quiet failure. A persistence test caught it within minutes of the field being added, which is the first time in this chapter that something failed loudly rather than being found by reading two files side by side. The test was AbstractCrudTestCase.testUpdate, the shared base case from Chapter 7 that every entity inherits, so nothing had been written to look for this.

What it reported was this:

ObjectOptimisticLockingFailureException: Row was already updated or deleted by another transaction for entity [ProductSpecificationEntity with id '01a19b12-...']

Nobody had updated or deleted anything. There was one thread and one transaction. The replacement entity carried an identifier that already existed and a version of null, so the statement Hibernate issued could not match any row, and zero rows updated is indistinguishable, at the layer that counts them, from somebody else having got there first. The message is not wrong so much as it is reporting the only conclusion available to it, which is worth remembering the next time one of these appears in a log: it says a row moved, and it means a statement matched nothing.

The fix is to copy into the specification that was loaded rather than to replace it. The reason it is worth telling is that the schema change did not break the code - it revealed that the code had been describing the wrong operation all along, and that a test had been passing because nothing was checking. What this version protects is narrower than it first appears, the lock travels with the entity inside a single unit of work, so it covers the DAO's own load-then-write and not the wider window between the service reading a product and the service writing it back. Closing that one means carrying the version out through the domain type and into the API, which is a conversation about request headers and belongs to the chapter that builds the REST layer. The column is a prerequisite for it rather than a substitute.

Enums and timestamps, stored on purpose

Both status columns are a MySQL ENUM holding the names from the Java enum, and neither is a VARCHAR nor an ordinal. The ordinal is the easy one to reject, storing ProductStatus.ACTIVE as 2 means the database's meaning of 2 is defined by the declaration order of a Java enum, and inserting a new constant in the middle of that enum silently reinterprets every existing row. Nothing in the database changed and every row now means something else.

A VARCHAR(32) is where I started, and it is the defensible default: it costs a few bytes a row, it is readable in a query result, and it leaves the set of legal values entirely to the application. What it does not do is refuse anything. A column that accepts any thirty-two characters will accept 'ACTIVEE', and the row that results is one no code path can produce and no code path can handle, which makes it the kind of data that is discovered months later by a switch that throws.

The ENUM refuses it. In strict mode the insert is an error rather than a row, and that is worth more here than it would be in most places, because these two enums are not open sets. ProductStatus and CategoryStatus declare transitions in code - canTransitionTo on each constant, which means the legal values are not a list somebody appends to as the business grows but a lifecycle with rules attached. A new status is never only a new string: it is a new set of transitions, a decision about what may become it and what it may become, and work in the service layer. Something that can never be added casually is exactly the kind of thing that can afford to be schema.

The usual objection is the second source of truth, and it holds: the set now exists in the Java enum and in the column definition, and the migration that adds a value has to be deployed before the code that writes it. For a list that genuinely grows that is a tax with nothing to show for it. For a lifecycle it is closer to a feature, because the deployment order it forces is the correct one anyway - the database should know about a state before a request can put a row into it.

The cost is elsewhere, in the comparison semantics. An ENUM column is an index internally, so WHERE status = 2 quietly matches the second member of the list and ORDER BY status sorts by declaration order rather than alphabetically. That is the ordinal hazard reappearing in queries after being rejected in storage, and the only mitigation is the one taken here: declare the values in the same order as the Java enum, so the index order is the lifecycle order and a sort by status at least means something. Anyone writing SQL against these columns by hand should compare against the string.

One asymmetry to know about rather than fix. The persistence tests from Chapter 7 run on H2 with the schema generated from the entities, so the column there is whatever @Enumerated(STRING) produces and not an ENUM at all. The guard this section is about exists in production and nowhere else, and no test touches it. That is acceptable because the application is not the thing the guard protects against - it already writes only valid names, but it does mean the protection is the one decision in this file that the test suite cannot confirm.

DATETIME(3) rather than DATETIME is not cosmetic. MySQL's default temporal precision is whole seconds, so a plain DATETIME column truncates everything below the second without complaint, and Instant carries far more than that. The failure that produces is one of the quieter ones: two rows written a few milliseconds apart come back with identical created_at values, an ordering by creation time becomes non-deterministic, and a test that sorts by timestamp passes or fails depending on how fast the machine is. Three digits is a deliberate stopping point rather than the maximum, because milliseconds are the finest granularity anything in this system actually reasons about, and it is worth knowing that the H2 database behind the persistence tests from Chapter 7 does not truncate the same way, so a timestamp compared across a round trip behaves slightly differently in a test than in production.

The database-side defaults on those columns deserve a note precisely because they almost never fire. Hibernate includes every mapped column in both its inserts and its updates, so the timestamps always arrive with a value, and an explicitly supplied value is also the condition under which MySQL skips ON UPDATE CURRENT_TIMESTAMP(3). They are not redundancy for its own sake: they are what makes a row written outside the application, by a later migration or by someone at a SQL prompt, come out with sane audit timestamps instead of zeroes.

Constraints as the only real arbiter

The unique constraint on (category_id, product_id) is the one Chapter 12 leaned on. That chapter walked through several ways of preventing the same product from being assigned to the same category twice, rejected a pessimistic read lock, and settled on reading current state to classify the batch while letting the database be the only real arbiter of the race that remains. This line is that arbiter, two concurrent requests assigning the same product both read an empty assignment table, both decide the insert is needed, and one of them receives a DataIntegrityViolationException that the handler turns into a 409.

The two foreign keys out of category_assignments are asymmetric. Deleting a product cascades to its assignments, because an assignment to a product that no longer exists is not data, it is a dangling row, and Chapter 12 described this as a backstop rather than a mechanism: the service deletes assignments explicitly, and the cascade covers the paths the service does not own. The category side has no cascade, which means deleting a category with products still assigned to it fails. That is intentional, categories are platform-owned taxonomy and archiving is the operation that retires one, so a delete that quietly took assignments with it would be silent data loss dressed up as convenience. There is one index declared outright, on products.created_at, and it is there because creation time is how a product list is ordered and paged, which is a read pattern rather than a constraint and so has no other line in the file to arrive on.

There is also one index nobody declared and the system depends on anyway. The unassign path queries assignments by product_id alone, which the unique constraint on (category_id, product_id) cannot serve, since a composite index is only usable from its leftmost column. InnoDB requires an index on the referencing columns of every foreign key and creates one silently when the schema does not provide it, so fk_assignments_product leaves a usable product_id index behind as a side effect. This is a MySQL behaviour rather than a general one: PostgreSQL does not index foreign key columns, and the same schema ported there would quietly turn that query into a sequential scan. Depending on it is fine as long as the dependency is known rather than accidental, which is why it is written down here.

A migration that cannot fail is a suggestion

One thing the file does not say is IF NOT EXISTS. It reads as harmless defensiveness, and in a hand-run script it would be. Inside a Flyway migration it undoes work the rest of this chapter paid for. Flyway already guarantees that each migration runs exactly once by recording it in flyway_schema_history, so the only situation in which IF NOT EXISTS has anything to do is one where the table is already there and Flyway does not know about it, which is exactly the situation that should stop the application rather than be smoothed over. The migration would report success, the table would keep whatever shape it already had, and setValidateOnMigrate(true) would have been satisfied by a history entry describing a schema change that never happened. A migration that cannot fail is not a migration, it is a suggestion.

What the schema enforces

Reading the file back, the migration turns out to be where four earlier chapters get audited. The UUID version from Chapter 12 only pays off because the column preserves byte ordering. The batching from the same chapter only survives because one table intentionally lacks a column. The DataIntegrityViolationException handler from Chapter 11 only has something to catch because of one UNIQUE line, and it turns out to be one handler short, because the optimistic lock this migration introduces produces a different exception family that currently becomes a 500.

That last one is the pattern worth taking away. Most of these are not bugs in the sense of something failing: the application starts and a reviewer reading either the Java or the SQL on its own would find nothing wrong. The specification update is the exception, and it is instructive for being one - it had been wrong for several chapters and only became detectable once a column existed that could notice. They are disagreements between two files that are only visible when you put them side by side and ask what each one assumes about the other. A schema is where those assumptions become checkable, which is a good argument for writing it deliberately rather than letting a tool generate it from the entities.

What's Next?

The application starts, the schema exists, and fourteen chapters of code still cannot answer a single request. There is no controller, no mapping, no request body, and the handler from Chapter 11 has nothing to hand a response back to.

Chapter 15 builds the REST layer, and it inherits two unfinished things from this one. The optimistic lock introduced here produces an exception family the handler does not catch, which is a two-line change and a 409. And the version column only protects a single unit of work, so making it protect the window between a client reading a product and writing it back means carrying the version out through the domain type and into the API — which is a conversation about request headers, conditional requests and what a client is expected to send back.

The module boundary gets tested for the first time too. The REST module holds the interfaces and the DTOs and depends on Jackson and the Swagger annotations rather than on spring-boot-starter-web, which is a claim the previous chapter made and this one has no way to check. The first @WebMvcTest is also what finally settles whether declaring @ComponentScan by hand costs the filters that slice tests rely on, a question Part 1 of Chapter 13 reasoned about and left open.

Codebase & Architecture Blueprint

The entire evolutionary architecture of this project is tracked using strict Git tags. To clone the repository and switch exactly to the state established in Chapter 14, use the following link:

Note: All core modules are configured with strict compilation-level boundaries. Compile and run mvn clean install to see the structure in action. Maven version 3.9.* and Java 25 are required.

◀️ Read Chapter 13 Part 3: Bootstrapping the System, Making It Run
▶️ Read Chapter 15: The REST Layer (Coming soon)

Join the Masterclass Journey

This article is part of an ongoing weekly series. If you want to receive every upcoming chapter directly in your inbox, alongside deep-dives, infrastructure architecture diagrams, and premium insights before anyone else, subscribe to my Substack Newsletter here!

Recommended Resources for Java & Spring Boot Engineers

Disclosure: I earn a commission if you purchase through the links below, at no additional cost to you.

If you are preparing for Senior/Lead Java interviews or looking to solidify your Spring Boot & Architecture skills, check out these highly-rated resources from the Javarevisited publication (Use promo code friends20 for an exclusive 20% discount automatically applied at checkout):