Audit Fields in Mini Shop

1. Audit fields: schema first, code later

When the project has already grown to Flyway, constraints, and @Version, adding audit fields becomes not just a cosmetic change, but a change to the data contract. At that moment you cannot behave like a person who runs ALTER TABLE by hand on production at night and says, “Well, it is just two columns, what’s the big deal?”. We will act like adults at the data layer: first we change the schema through a migration, then we align the entity mapping with that schema, and only then do we enjoy automatic auditing — ideally without surprises.

The main idea: audit fields are physical columns in tables. If in Java you added createdAt, but the column does not exist in PostgreSQL, Hibernate is not telepathic — it will simply fail or start behaving strangely. If you added a column in the database but forgot the mapping, you will get data that you neither read nor write. That is why the order of operations here is almost ritualistic, but useful: schema → backfill → constraints → mapping.

For clarity, you can think of the process like this:

flowchart TD
    A[Need to add createdAt/updatedAt] --> B[Flyway migration: add columns]
    B --> C[Backfill existing rows]
    C --> D[Set NOT NULL / defaults, if needed]
    D --> E[Update entity: @CreatedDate/@LastModifiedDate]
    E --> F[Verify the scenario: insert/update and SQL in DB]

2. Flyway: adding created_at and updated_at

The most common failure when adding audit fields is trying to do everything “nicely” in one step: ADD COLUMN created_at TIMESTAMP NOT NULL on a table that already contains rows. PostgreSQL answers this quite logically: “NOT NULL? Great. And what values should I put into the existing rows?”. And that is where the pain begins.

The correct junior-friendly path is to first add the columns as nullable (or with a default), then fill the existing rows, and only then, if it is truly needed, tighten the screws to NOT NULL. In a training project we can afford a backfill through now(), because we do not have historical data with the “real creation time”. In a real product, you will often look for a more meaningful source or decide that “nullable for old rows is fine”.

Important: column names in the database are usually in snake_case, while fields in Java are in camelCase. We will keep this style consistently: created_at ↔ createdAt, updated_at ↔ updatedAt.

-- V6__add_product_audit_fields.sql

-- 1) First add nullable columns: that way the migration will not fail on existing rows
ALTER TABLE product ADD COLUMN created_at TIMESTAMP;
ALTER TABLE product ADD COLUMN updated_at TIMESTAMP;

-- 2) Backfill: populate existing rows (in a training project, now() is enough)
UPDATE product
SET created_at = now(), updated_at = now()
WHERE created_at IS NULL OR updated_at IS NULL;

-- 3) And only after that tighten to NOT NULL, so that future data stays disciplined
ALTER TABLE product ALTER COLUMN created_at SET NOT NULL;
ALTER TABLE product ALTER COLUMN updated_at SET NOT NULL;

There are three ideas here. First, we added the columns without NOT NULL, so the migration would not fail on existing rows. Then we did a backfill so that all rows get values. Then we made the fields mandatory so that the project continues to live in a disciplined way.

Sometimes you will see an alternative style: ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT now(). It also works, but it has a nuance: the default remains part of the schema, and then the team starts arguing, “Do we need the default, or should auditing fill it on its own?”. In training projects I usually prefer backfill + NOT NULL, and I do not set a default so that there is no second source of truth. In our project, the source of truth will be auditing at the application level.

To avoid turning the lecture into a long SQL sheet, let us fix the set of tables we take in the current baseline:

To keep the project from branching into variants, for the mini-shop we keep this baseline at this step:

• Category, Product, and CustomerOrder inherit from BaseAuditEntity;
• StockItem stays a special case: @Version + only updatedAt;
• OrderItem stays without audit fields for now;
• createdBy and lastModifiedBy are not included in the mandatory working configuration: that is an additional layer, not the current baseline of the project.

And one more thing: if you add audit fields to an existing table, do not forget that the migration must remain additive. We do not rewrite migration history and do not “patch old V1”, because Flyway lives by the logic of “what was applied, was applied”.

3. Base audit for entities

After migrations, the moment comes when the code must start matching the schema. Here it is easy to fall into the extreme of “well, auditing will set it anyway, so let’s not think about it at all”. But auditing is infrastructure, not telepathy: it needs a correct mapping and a clear semantic for the fields.

With this baseline, it is easier to see why a shared base class is needed at all: it belongs where the full set of fields is truly useful, not where we just want everything to be the same.

Let us assemble a slim base class (if you made it in lecture 3 — great, now we are just bringing it to production shape). I will show the version that works well for Category, Product, CustomerOrder, and in general most long-lived entities.

import jakarta.persistence.Column;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.MappedSuperclass;
import java.time.LocalDateTime;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@MappedSuperclass // the columns of this class are "embedded" into the tables of subclasses
@EntityListeners(AuditingEntityListener.class) // enable Spring Data auditing for subclasses
public abstract class BaseAuditEntity {

    @CreatedDate // filled on the first save
    @Column(name = "created_at", nullable = false, updatable = false) // created_at must not change on UPDATE
    private LocalDateTime createdAt;

    @LastModifiedDate // updated on every entity change
    @Column(name = "updated_at", nullable = false) // updated_at changes on UPDATE (that is expected)
    private LocalDateTime updatedAt;
}

Pay attention to two small but important decisions. For createdAt, updatable = false is set, because “creation time” must not change on updates. For updatedAt, it can be updated, because that is exactly the “last modification” time. Together this gives a simple and honest model.

Now let us connect this base to Product. Our goal here is not to show “the whole Product at once”, but to show exactly how the class structure changes after the refactoring.

import jakarta.persistence.Entity;
import jakarta.persistence.Id;

@Entity
public class Product extends BaseAuditEntity { // audit fields "arrive" here through inheritance

    @Id
    private Long id;

    private String sku;
}

Yes, this example looks “too short”, but that is exactly what makes it good: auditing should not make the entity heavier. We simply added inheritance from BaseAuditEntity, and now product gets two fields that really live in the product table as created_at and updated_at.

If you want to see directly that @MappedSuperclass does not create a separate table, keep this idea in mind: the base class is like a “column template” that gets expanded into the child table later. It is not an entity and is not persisted on its own.

classDiagram
    class BaseAuditEntity {
        <<MappedSuperclass>>
        createdAt
        updatedAt
    }
    class Product {
        id
        sku
    }
    BaseAuditEntity <|-- Product

4. StockItem: updatedAt and @Version

When we talk about inventory, it is tempting to do “what everyone does”: let StockItem also extends BaseAuditEntity. But this is one of those moments where sameness does not equal quality. StockItem in our project is the entity around which optimistic locking revolves. For it, the pair @Version + updatedAt is almost the ideal minimum: the version protects against lost updates, and updatedAt gives a person a marker for “when this was last touched”.

And here we can make a very pragmatic decision: StockItem stores only updatedAt, without createdAt. This is not an “obligatory standard”; it is a normal project decision. If tomorrow the business says, “Show us the inventory creation date too”, we will add it. Until then, we do not.

Then StockItem can look like this:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.Id;
import jakarta.persistence.Version;
import java.time.LocalDateTime;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@Entity
@EntityListeners(AuditingEntityListener.class) // auditing is needed to update updated_at automatically
public class StockItem {

    @Id
    private Long id;

    @Version // optimistic locking: protects against lost update during concurrent inventory changes
    private long version;

    @LastModifiedDate // filled on every UPDATE
    @Column(name = "updated_at", nullable = false)
    private LocalDateTime updatedAt;
}

Yes, here we again attached EntityListeners(AuditingEntityListener.class). If you do not like duplication, you can move “only updatedAt” into a separate @MappedSuperclass, for example LastModifiedEntity, and inherit from it — but please do not build a hierarchy with five floors. One floor is fine; skyscrapers are for corporate legends.

Now, the migration for stock_item. We add only updated_at and do a backfill. If the table already exists and has rows, we act as carefully as before.

-- V7__add_stock_item_updated_at.sql

-- Add the nullable column so we do not fail on existing rows
ALTER TABLE stock_item ADD COLUMN updated_at TIMESTAMP;

-- Backfill: set updated_at for old records
UPDATE stock_item
SET updated_at = now()
WHERE updated_at IS NULL;

-- Lock in the contract: updated_at is mandatory from now on
ALTER TABLE stock_item ALTER COLUMN updated_at SET NOT NULL;

And here we get a pleasant “engineering composition”: StockItem is protected from conflicts by the version, and on every quantity change our updatedAt will be updated automatically.

5. Verifying it in action and the boundaries

After migrations and entity updates, you want to make sure auditing works not just “in theory”. This is where a beginner developer often falls into the trap of expectations: they changed a field on the object, looked at the object in the debugger, did not see updatedAt, and concluded that “auditing is broken”. In reality, auditing usually fills fields at save/flush time, that is, when the entity actually participates in the persistence lifecycle.

Let me show a minimal service scenario for “rename a product” — without a manual setUpdatedAt, because that is why we built this in the first place.

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class CatalogService {

    private final ProductRepository productRepository;

    public CatalogService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    @Transactional // important: inside a transaction the entity will be managed, dirty checking and auditing will work
    public void renameProduct(Long id, String newName) {
        Product p = productRepository.findById(id).orElseThrow(); // managed entity after loading
        p.setName(newName); // change the field: Hibernate will detect dirty state and issue an UPDATE
        // updated_at will be set by AuditingEntityListener during flush/commit
    }
}

If the Product entity is managed (and inside @Transactional it is managed), dirty checking will do its job, Hibernate will send an UPDATE, and auditing will update updated_at to the current value. Ideally you enable SQL logs in the dev profile and see that UPDATE product ... includes the updated_at column.

To verify this with your own eyes in the database (and feel a little like a detective), you can run a query:

-- Check that created_at does not change, while updated_at changes after updates
SELECT id, sku, created_at, updated_at
FROM product
WHERE id = 1;

And you will see that created_at does not change, while updated_at changes after updates.

A similar check makes sense for StockItem. If you have a method that reserves inventory or reduces available quantity, after the operation you can do:

-- Check that @Version increments and updated_at is updated
SELECT id, version, updated_at
FROM stock_item
WHERE id = 1;

If optimistic locking is enabled and @Version works, the version increases on every UPDATE. And updatedAt updates at roughly the same moment. In a real project, this combination often helps you understand very quickly “why the version conflict happened right now” and “when someone last changed the inventory”.

And here the question becomes not about annotations, but about proof. Since audit fields, migrations, and @Version affect real SQL and the state of tables, it is useful to validate them not only with a manual database check, but also with data-layer tests. Otherwise the next refactoring can easily break what currently seems obvious.

Soft delete boundary

This is usually where the standard scene happens: someone sees createdAt/updatedAt, remembers the word “audit”, and the brain completes the thought with “so we need soft delete, because we should not delete things, maybe they will be useful later”. And at that point we pause and remember that our course is about a basic, maintainable data layer, not about a collection of the industry’s most controversial patterns over the last 20 years.

Soft delete is not “just another audit field”. It is a change to the meaning of delete and a change to the meaning of read. From the point of view of the database and queries, you start living in a world where the table permanently stores “dead records”, and every query must remember “and do not forget WHERE deleted = false”. If you forget it anywhere, you will get zombie data that suddenly appears in reports, lists, and uniqueness checks.

Soft delete is especially nasty when it conflicts with UNIQUE. Imagine product.sku is unique. You “deleted” the product with soft delete, but the row is still there, which means sku is still taken. At some point the business says, “Create a new product with the same SKU” (yes, that happens in real life when SKUs are reused or someone made a mistake), and suddenly you hit uniqueness, even though the product was “deleted”. After that, a separate large engineering story begins about partial unique indexes, filters, and agreements. We are deliberately not going there now.

It is enough here to fix the boundary: auditing answers “when/who changed it”, while soft delete is about the deletion model and reading rules. These are different axes of decision, and they should not be mixed.

6. Typical mistakes with audit fields

Mistake #1: add fields to the entity and forget the migration.
This looks like the most innocent mistake: you added createdAt and updatedAt to Product, started the application — and it either crashed at startup or started complaining on the first save. The reason is simple: ORM does not know how to materialize columns out of thin air. The fix is discipline: first the Flyway migration, then the Java code. If you want to “check quickly”, you can temporarily enable ddl-auto, but we have already agreed that in the production-like version of the project, this is not the source of truth.

Mistake #2: add NOT NULL columns to a table with data without backfill.
This is the classic “PostgreSQL broke everything” level. In reality PostgreSQL is just honest: if you require NOT NULL, it wants values. So either add the columns as nullable, run UPDATE ... SET ... WHERE ... IS NULL, and only then set NOT NULL, or add them with a default and then consciously decide whether to keep the default in the schema or not.

Mistake #3: expect audit fields to be filled “right after new”.
Auditing is tied to the persistence lifecycle. If you did new Product() and immediately looked at createdAt, it will be null, and that is normal. Fields are filled on persist/update, most often on flush/commit. If you need to see the values in code right now, make sure the entity is actually saved (and that the transaction reached flush).

Mistake #4: mix two sources of truth — auditing and a manual setUpdatedAt(now).
This often happens after refactoring: some services have already been “cleaned up”, while others still set updatedAt manually. As a result, the time logic is spread across the codebase, and you do not know who wins. A good style is to choose one approach. In our project that is Spring Data auditing, and we do not use manual time setters.

Mistake #5: make the same audit template for all entities, even when it makes no sense.
Yes, you can force everything to inherit from one base class, including StockItem, OrderItem, and basically “every row in the universe”. But that turns auditing into a formality and adds noise. It is much more useful to keep auditing where it is actually readable and helpful: catalog, order, inventory. For StockItem, it is perfectly fine to have only updatedAt, because this is an entity about “current state”, and its history is more important to us by version (@Version) than by creation date.