Spring Boot JPA Entities, Flyway Migrations & Spring Data Repositories — The Database Layer Done Right (Episode 5)

There is one line in almost every Spring Boot tutorial that runs flawlessly on your laptop and slowly corrupts your database in production:

spring:
  jpa:
    hibernate:
      ddl-auto: update    # works on your laptop, quietly rewrites prod

With ddl-auto: update, Hibernate looks at your entities on every startup and alters the live schema to match them. No migration file. No version. No review. No rollback. Two developers add different fields, deploy in a different order, and now staging and production have quietly diverged — and the day one of those "updates" needs to drop a column, Hibernate will happily take your data with it.

In Episode 5 of Building Instagram's Authentication Backend, we do the opposite. The database is not something Hibernate improvises at boot — it is a versioned artifact, checked into git, owned by Flyway. We build the persistence layer in three deliberate steps: the SQL migration, then the JPA entities, then the Spring Data repositories. This article is the written companion — every column, every annotation, and the handful of places where the real code has honest rough edges worth understanding.

What you'll build

Five files: db/migration/V1__create_auth_tables.sql, entity/User.java, entity/RefreshToken.java, repository/UserRepository.java, and repository/RefreshTokenRepository.java — two new packages (entity/, repository/) plus the first real migration. Note: we still don't run the app here. That comes in Episode 9. This episode is about getting the schema and its mapping exactly right first.

Why the schema comes first

Back in Episode 2 we set one line that decides the entire order of this episode:

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # Flyway owns the schema; Hibernate only checks it

With validate, Hibernate never creates or alters a table. At startup it only checks that your entities agree with the schema Flyway already built — and if an entity claims a column the table doesn't have, the application refuses to start. That is a feature, not an annoyance: it turns a whole class of "the code and the database disagree" bugs into a loud failure at boot instead of a silent one at 2 a.m.

So the schema has to exist before the entities can be validated against it. SQL first, then entities, then repositories. Let's follow that order.

The Flyway migration: V1__create_auth_tables.sql

That filename is a contract. Flyway reads the version (V1), the separator (a double underscore), and the description. It runs each migration exactly once, records it in a flyway_schema_history table, and stores a checksum of the file. Change this file after it has run in an environment and Flyway will refuse to start, because the checksum no longer matches. History is immutable — you never edit V1, you move forward with V2.

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE users (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username         VARCHAR(30)  NOT NULL UNIQUE,
    email            VARCHAR(255) NOT NULL UNIQUE,
    password         VARCHAR(255),
    full_name        VARCHAR(100),
    profile_picture  TEXT,
    role             VARCHAR(20)  NOT NULL DEFAULT 'USER',
    auth_provider    VARCHAR(20)  NOT NULL DEFAULT 'LOCAL',
    provider_id      VARCHAR(255),
    email_verified   BOOLEAN      NOT NULL DEFAULT FALSE,
    account_locked   BOOLEAN      NOT NULL DEFAULT FALSE,
    failed_login_attempts INT     NOT NULL DEFAULT 0,
    lock_expires_at  TIMESTAMP,
    last_login_at    TIMESTAMP,
    created_at       TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMP    NOT NULL DEFAULT NOW()
);

The first line enables pgcrypto, which gives us gen_random_uuid() so Postgres itself generates the primary keys.

And those keys are UUIDs, not auto-incrementing integers — deliberately. Sequential IDs leak information: if your user ID is 1042, anyone who sees it knows you have roughly a thousand users, and can walk /users/1041, /users/1040 to enumerate them. A UUID gives nothing away and is safe to put in a URL.

Notice password is nullable. That is not an oversight. A user who signs up through Google or Facebook never has a password on our side — which is exactly why auth_provider (defaulting to LOCAL) and provider_id exist. Local users get a BCrypt hash; social users get a provider and an external id.

Then the security block: email_verified, account_locked, failed_login_attempts, and lock_expires_at. This is the lockout policy from Episode 1 — five failures, thirty-minute lock — living in the database. Because it is persisted, it survives a restart. An attacker can't clear their own lockout by waiting for you to deploy.

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

Let me be honest about these two lines

Both indexes are redundant. Look back at the table: username and email are already declared NOT NULL UNIQUE, and Postgres automatically builds a B-tree index behind every UNIQUE constraint. So idx_users_email and idx_users_username each create a second, identical index on a column that already has one — double the write cost, double the disk, zero benefit. I left them in the repo on purpose. Deleting them is one of your free PRs below.

The refresh-token table

CREATE TABLE refresh_tokens (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token      VARCHAR(512) NOT NULL UNIQUE,
    user_id    UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at TIMESTAMP    NOT NULL,
    revoked    BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP    NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_refresh_tokens_token ON refresh_tokens(token);
CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);

A refresh token is a long random string (up to 512 chars), UNIQUE so it can't collide. The user_id foreign key uses ON DELETE CASCADE — delete a user and their tokens vanish automatically, no orphan rows. expires_at and revoked are what make rotation and blacklisting possible, straight from the Episode 1 design.

Now watch the difference between these two indexes. idx_refresh_tokens_token is redundant for the same reason as before — token is already UNIQUE. That's the third free index doing nothing. But idx_refresh_tokens_user_id is the real thing: user_id is a plain foreign key, not unique, and we genuinely query "all tokens for this user" when we revoke a whole session. That index earns its place. The lesson isn't "indexes are bad" — it's knowing which ones the database already gave you for free and which ones you actually have to add.

The updated_at trigger

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

A BEFORE UPDATE trigger stamps updated_at on every row change — at the database level, so it fires no matter what issues the UPDATE: Hibernate, a psql session, a future migration. Keep this in mind, because in a moment the User entity is going to do the same job again in Java, and that overlap is worth a conversation.

The User entity

Now the Java side. entity/User.java maps onto the users table — and it does two jobs at once. It is a JPA entity and it implements Spring Security's UserDetails, so the same object that is a database row is also the authenticated principal. That's convenient; it's also a coupling to stay aware of, which is exactly why the DTOs from Episode 4 exist — this object never gets serialized to a client directly.

@Entity
@Table(name = "users", uniqueConstraints = {
        @UniqueConstraint(columnNames = "email"),
        @UniqueConstraint(columnNames = "username")
})
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User implements UserDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @Column(nullable = false, unique = true, length = 30)
    private String username;

    @Column(nullable = false, unique = true)
    private String email;

    private String password;                       // nullable: OAuth users have none

    @Column(name = "full_name", length = 100)
    private String fullName;

    @Column(name = "profile_picture")
    private String profilePicture;

    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    @Builder.Default
    private Role role = Role.USER;

    @Enumerated(EnumType.STRING)
    @Column(name = "auth_provider", nullable = false)
    @Builder.Default
    private AuthProvider authProvider = AuthProvider.LOCAL;

    @Column(name = "email_verified", nullable = false)
    @Builder.Default
    private boolean emailVerified = false;

    @Column(name = "account_locked", nullable = false)
    @Builder.Default
    private boolean accountLocked = false;

    @Column(name = "failed_login_attempts", nullable = false)
    @Builder.Default
    private int failedLoginAttempts = 0;

    @Column(name = "lock_expires_at")
    private LocalDateTime lockExpiresAt;

    @Column(name = "last_login_at")
    private LocalDateTime lastLoginAt;

    @Column(name = "created_at", nullable = false)
    @Builder.Default
    private LocalDateTime createdAt = LocalDateTime.now();

    @Column(name = "updated_at", nullable = false)
    @Builder.Default
    private LocalDateTime updatedAt = LocalDateTime.now();

    @PreUpdate
    public void onUpdate() {
        this.updatedAt = LocalDateTime.now();
    }

A few things worth pausing on. The Lombok stack is @Getter/@Setter/@Builder with an explicit @NoArgsConstructor and @AllArgsConstructor, plus @Builder.Default on every defaulted field. This is deliberately not @Data + @Builder. @Builder quietly suppresses the no-args constructor, and JPA requires a no-arg constructor to hydrate entities from rows — so we declare it back explicitly. (Same trap we hit with DTOs in Episode 4, different consequence.)

The field mapping is clean and idiomatic: camelCase Java, snake_case columns via @Column(name = "...") — fullName to full_name, createdAt to created_at. Remember this, because RefreshToken is about to do it differently. The id uses @GeneratedValue(strategy = GenerationType.UUID) — native Hibernate 6 UUID generation, matching the database default.

One thing that isn't here: provider_id is in the SQL but there's no providerId field on the entity yet — an OAuth2 placeholder waiting for the code that will use it. That doesn't break validate: Hibernate checks that every mapped field has a matching column; it does not object to extra columns the entity simply ignores. A column is allowed to arrive before the code that reads it.

And there's the overlap I promised: @PreUpdate onUpdate() sets updatedAt before every update — the same job as the database trigger. For a Hibernate-driven update, both run. That's the trade-off, and it's defensible as belt-and-suspenders: the trigger covers updates that never touch Hibernate (a raw SQL fix, another service), while @PreUpdate keeps the in-memory entity's timestamp correct without a re-read. Just know it's two mechanisms, not one, so you're never surprised.

Where UserDetails gets clever (and slightly dangerous)

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        return List.of(new SimpleGrantedAuthority("ROLE_" + role.name()));
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        if (!accountLocked) return true;
        if (lockExpiresAt != null && LocalDateTime.now().isAfter(lockExpiresAt)) {
            this.accountLocked = false;
            this.failedLoginAttempts = 0;
            this.lockExpiresAt = null;
            return true;
        }
        return false;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return emailVerified;
    }

    public enum Role { USER, ADMIN }

    public enum AuthProvider { LOCAL, GOOGLE, FACEBOOK }

getAuthorities() maps the role enum to ROLE_USER or ROLE_ADMIN. isEnabled() ties "enabled" to emailVerified — so an unverified user can pass a password check but is still treated as disabled until they verify. isAccountNonExpired and isCredentialsNonExpired are hardcoded true (no expiry policy yet).

The honest one: isAccountNonLocked() mutates the entity

When a lock has expired, this getter clears accountLocked, failedLoginAttempts, and lockExpiresAt in memory and returns true — a self-healing lock. Clever. But does that reset actually persist? Only if this runs inside an active transaction where Hibernate dirty-checks and flushes the entity. Spring Security calls isAccountNonLocked() during authentication, which is typically not a writable transaction — so the in-memory reset usually evaporates, and the row still says locked = true. The login succeeds (the method returned true), but the next attempt re-reads a stale locked row and has to expire it all over again. It works — just not for the reason it looks like it does. The robust fix is to reset the lock explicitly in your auth service, not as a side-effect of a getter.

The RefreshToken entity

@Entity
@Table(name = "refresh_tokens")
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class RefreshToken {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @Column(nullable = false, unique = true)
    private String token;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;

    @Column(nullable = false, name = "expires_at")
    private LocalDateTime expires_at;          // snake_case field - see the callout

    @Column(nullable = false)
    @Builder.Default
    private boolean revoked = false;

    @Column(updatable = false, name = "created_at")
    @Builder.Default
    private LocalDateTime created_at = LocalDateTime.now();

    public boolean isExpired() {
        return LocalDateTime.now().isAfter(expires_at);
    }
}

A @ManyToOne(fetch = FetchType.LAZY) to User with an explicit @JoinColumn("user_id"): many tokens per user, loaded lazily so fetching a token doesn't drag the whole user in behind it. isExpired() just compares now to the expiry.

Spot the gap: these field names

expires_at and created_at are snake_case — but they're Java fields, which should be camelCase (expiresAt, createdAt), exactly like User does it. It compiles and maps fine because @Column(name = "...") is explicit, but it breaks Java convention and it leaks: look at the repository query coming up — WHERE rt.expires_at < :now. JPQL references the field name, so the non-idiomatic naming propagates into every query that touches it. One rename fixes the entity and the JPQL together. Great first PR.

The repositories: queries without SQL

A Spring Data repository is just an interface that extends JpaRepository<Entity, IdType>. That alone gives you save, findById, findAll, delete, paging and sorting — for free. On top of that, Spring generates queries from method names.

@Repository
public interface UserRepository extends JpaRepository<User, UUID> {

    Optional<User> findByEmail(String email);

    boolean existsByEmail(String email);

    boolean existsByUsername(String username);
}

Spring parses findByEmail, existsByEmail, existsByUsername and writes the SQL for you. The existsBy* methods return a boolean without loading the row — precisely what you want for "is this username already taken?" during registration, instead of fetching a whole User just to check. (There's deliberately no findByUsername yet — login currently resolves by email. Adding username-login is another easy contribution.)

@Repository
public interface RefreshTokenRepository extends JpaRepository<RefreshToken, UUID> {

    Optional<RefreshToken> findByToken(String token);

    @Modifying
    @Query("UPDATE RefreshToken rt SET rt.revoked = true WHERE rt.user = :user")
    void revokeAllUserTokens(User user);

    @Modifying
    @Query("DELETE FROM RefreshToken rt WHERE rt.expires_at < :now OR rt.revoked = true")
    void deleteExpiredAndRevokedTokens(LocalDateTime now);
}

findByToken is derived. The two @Modifying @Query methods are hand-written JPQL for bulk work: revoke every token a user holds (log-out-everywhere), and prune the expired-or-revoked rows (a cleanup job). @Modifying tells Spring the query writes rather than reads — call these from a @Transactional method or the change won't commit. And there it is again: rt.expires_at, the naming leak from the entity, sitting right in your query text.

Your three free PRs (contributors get a shout-out)

1) Drop the three redundant indexes — idx_users_email, idx_users_username, idx_refresh_tokens_token — since each column is already UNIQUE (keep idx_refresh_tokens_user_id). 2) Rename RefreshToken's expires_at/created_at to camelCase and update the JPQL. 3) Add findByUsername when username-login lands. The repo link is at the bottom.

Common questions

Why not just use ddl-auto=update?

Because it works right up until it doesn't. There's no version history, no rollback, no code review of schema changes, and silent drift between environments — and some "updates" (dropping or retyping a column) can destroy data on the way. validate plus Flyway migrations is the production-standard answer: every change is a reviewed, versioned, run-once file.

Why UUID primary keys instead of auto-increment?

Two reasons. Enumeration: sequential IDs let anyone guess neighbouring records and estimate your total user count. And safety in URLs: a UUID exposes nothing, so it can appear in an API path without leaking business data. Postgres generates them for us via gen_random_uuid().

Is this compatible with Spring Boot 3?

Almost entirely. The JPA/Hibernate 6 annotations, jakarta.persistence, Spring Data, and the Flyway setup are identical between Spring Boot 3 and 4. GenerationType.UUID needs Hibernate 6, which ships with Spring Boot 3.0 onward — so you're covered on both.

---

Watch Episode 5

Get the code

The full migration, both entities, and both repositories live on GitHub: sprashantofficial/insta-auth-service — a star genuinely helps the series reach more developers, and it's where you'll open those PRs.

Go deeper:

• 🎓 The full course on Udemy — the entire build, start to finish, with the OAuth2, MFA and deployment work that doesn't fit on YouTube.
• ☕ Book a 1:1 on Topmate — code review, architecture, or career guidance from a working Tech Lead.
• ▶️ Subscribe on YouTube — new episodes every Monday and Thursday.

One for the comments, because I go back and forth on it myself: do you let your JPA @Entity implement UserDetails the way we did here, or do you keep a separate principal object and map to it? And — did you spot all three redundant indexes before I pointed them out?