Feature 6: Database Schema & Validation

Purpose: This guide provides step-by-step instructions for implementing database schema validation and DDL generation workflow.

Dependencies: This is a critical infrastructure enhancement. No dependencies on other Epic 2 features.

Related Documents:

• Boilerplate Enhancements Solution Architecture - System design overview
• Boilerplate Enhancements Epic - Capability definition

---

Overview

What This Guide Covers

Database Schema & Validation ensures database schema matches JPA entities and prevents schema drift:

• V3 schema synchronization migration
• Hibernate ddl-auto: validate configuration
• DDL generation Spring profile
• DDL generation Gradle task

What's Included:

• Flyway migration file (V3__sync_entity_schema.sql)
• application.yml configuration changes
• build.gradle task configuration
• Schema validation workflow

What's NOT Included:

• ❌ New entity creation (this syncs existing entities)
• ❌ Business logic changes
• ❌ Other migrations (V4, V5, V6 are AI-specific and out of scope)

---

Prerequisites

• Springular boilerplate setup
• Flyway configured and working
• Hibernate/JPA entities exist
• Understanding of Flyway migrations
• Understanding of Hibernate schema validation

---

Implementation Steps

Step 1: Create V3 Schema Synchronization Migration

File: server/src/main/resources/db/migration/V3__sync_entity_schema.sql

Purpose: This migration ensures the database schema matches JPA entity expectations by:

• Modifying column types to match entity field types
• Adding missing constraints (unique, foreign keys)
• Adding missing indexes
• Ensuring constraint names match entity expectations

Key Points:

• ✅ Idempotent operations (PostgreSQL will ignore duplicate index/constraint creation)
• ✅ Uses entity-expected constraint names
• ✅ Syncs column types to VARCHAR(255) where entities expect String
• ✅ Adds indexes for performance

Create migration file:

-- V3: Sync database schema with JPA entity expectations
-- This migration ensures the database schema matches entity field types, constraints, and indexes.

-- Modify column types to match entity expectations
ALTER TABLE users 
    ALTER COLUMN registered_provider_name TYPE VARCHAR(255),
    ALTER COLUMN registered_provider_id TYPE VARCHAR(255);

ALTER TABLE user_roles 
    ALTER COLUMN role TYPE VARCHAR(255);

-- Add unique constraint on password_reset_token.user_id
-- Note: PostgreSQL will ignore this if constraint already exists
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'uc_password_reset_token_user_id'
    ) THEN
        ALTER TABLE password_reset_token 
            ADD CONSTRAINT uc_password_reset_token_user_id UNIQUE (user_id);
    END IF;
END $$;

-- Update constraint names to match Hibernate-generated entity expectations
-- Note: Hibernate generates constraint names like FK83nsrttkwkb6ym0anu051mtxn
-- These names may change if entities are regenerated, but ensure exact match with current entity expectations

-- password_reset_token foreign keys
ALTER TABLE password_reset_token 
    DROP CONSTRAINT IF EXISTS fk_password_reset_user;

ALTER TABLE password_reset_token 
    ADD CONSTRAINT FK83nsrttkwkb6ym0anu051mtxn 
    FOREIGN KEY (user_id) REFERENCES users;

-- refresh_tokens foreign keys
ALTER TABLE refresh_tokens 
    DROP CONSTRAINT IF EXISTS fk_refresh_token_user;

ALTER TABLE refresh_tokens 
    ADD CONSTRAINT FK1lih5y2npsf8u5o3vhdb9y0os 
    FOREIGN KEY (user_id) REFERENCES users;

-- user_roles constraints
-- Note: user_roles constraints are already correct in V1 (fk_user_roles_user_id, fk_user_roles_role)
-- Only update if entity expectations require different constraint names
-- The user_roles table uses role (not role_id) and references roles(name) (not roles(id))
-- V1 already has: FOREIGN KEY (role) REFERENCES roles(name) ON DELETE CASCADE

-- Add missing indexes (idempotent - PostgreSQL ignores if exists)
CREATE INDEX IF NOT EXISTS idx_password_reset_token_user_id 
    ON password_reset_token(user_id);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token 
    ON refresh_tokens(token);

Important Notes:

• This migration assumes V1 and V2 migrations already exist
• Index creation is idempotent (IF NOT EXISTS)
• Constraint recreation ensures names match entity expectations
• Column type changes use ALTER COLUMN ... TYPE (safe for compatible types)

---

Step 2: Configure Hibernate ddl-auto: validate

File: server/src/main/resources/application.yml

Purpose: Change Hibernate from update to validate mode to enforce Flyway as single source of truth.

Key Points:

• ✅ Prevents Hibernate from modifying schema in production
• ✅ Catches schema mismatches at startup
• ✅ Enforces migration-based schema management
• ✅ Critical for production safety

Find and update:

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # Changed from 'update' to 'validate'

Before (dangerous in production):

spring:
  jpa:
    hibernate:
      ddl-auto: update  # ❌ Hibernate modifies schema automatically

After (production-safe):

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # ✅ Hibernate only validates, doesn't modify

Impact:

• Hibernate will validate entity mappings against database schema at startup
• Schema changes must be done via Flyway migrations (not auto-generated)
• Startup will fail if schema doesn't match entity expectations

---

Step 3: Add DDL Generation Spring Profile

File: server/src/main/resources/application.yml

Purpose: Add a Spring profile for generating DDL from JPA entities (developer workflow tool).

Key Points:

• ✅ Generates DDL from entities (no database changes)
• ✅ Outputs to file for review
• ✅ Disables Flyway for this profile
• ✅ Uses JPA schema generation

Add profile section:

---
spring:
  config:
    activate:
      on-profile: 'ddl-generate'
  
  jpa:
    hibernate:
      ddl-auto: none  # No database modifications
    properties:
      # Generate DDL files from entities
      jakarta.persistence.schema-generation.scripts.action: create
      jakarta.persistence.schema-generation.scripts.create-target: build/generated-schema/create.sql
      jakarta.persistence.schema-generation.scripts.create-source: metadata
      hibernate.show_sql: true
      hibernate.format_sql: true

  flyway:
    enabled: false  # Don't run migrations

Usage:

./gradlew bootRun --args='--spring.profiles.active=ddl-generate'

Output: build/generated-schema/create.sql (DDL statements for all entities)

Configuration Explanation:

• ddl-auto: none - No database modifications (generates files only)
• jakarta.persistence.schema-generation.scripts.action: create - Generate DDL scripts
• jakarta.persistence.schema-generation.scripts.create-target - Output file path
• jakarta.persistence.schema-generation.scripts.create-source: metadata - Use entity metadata
• hibernate.show_sql: true - Log SQL statements
• hibernate.format_sql: true - Format SQL for readability

Purpose:

• Review what schema entities expect
• Compare with Flyway migrations
• Validate entity-to-schema mapping

---

Step 4: Add DDL Generation Gradle Task

File: server/build.gradle

Purpose: Add a Gradle task that documents the DDL generation workflow.

Key Points:

• ✅ Documents the workflow in build system
• ✅ Provides clear instructions
• ✅ Improves developer experience

Add task (at the end of build.gradle, outside any block):

// DDL Generation Task
tasks.register('showDDLInstructions') {
    group = 'documentation'
    description = 'Shows instructions for generating DDL from JPA entities'
    
    doLast {
        println """
        ============================================
        DDL Generation Instructions
        ============================================
        
        To generate DDL from JPA entities:
        
        1. Run: ./gradlew bootRun --args='--spring.profiles.active=ddl-generate'
        
        2. Check output: build/generated-schema/create.sql
        
        3. Compare with Flyway migrations to ensure alignment
        
        4. Update V3__sync_entity_schema.sql if schema differs
        
        Note: This generates DDL for review only. Schema changes must be done via Flyway migrations.
        
        ============================================
        """
    }
}

Usage:

./gradlew showDDLInstructions

Purpose: Makes the workflow discoverable and documented in the build system.

---

Verification

Step 1: Verify Migration Runs Successfully

Run Flyway migration:

./gradlew bootRun

Expected Result:

• Application starts successfully
• V3 migration executes without errors
• Database schema matches entity expectations

Check Migration Status:

SELECT * FROM flyway_schema_history ORDER BY installed_rank;

Verify V3 migration is recorded.

---

Step 2: Verify Schema Validation Works

Test with mismatched schema (temporary test):

1. Manually modify a column type in database:

   ALTER TABLE users ALTER COLUMN registered_provider_name TYPE TEXT;

2. Start application:

   ./gradlew bootRun

3. Expected: Application should fail at startup with schema validation error

4. Revert the change:

   ALTER TABLE users ALTER COLUMN registered_provider_name TYPE VARCHAR(255);

5. Application should start successfully

---

Step 3: Verify DDL Generation Works

Run DDL generation:

./gradlew bootRun --args='--spring.profiles.active=ddl-generate'

Expected Result:

• Application runs (or exits after generation)
• File created: build/generated-schema/create.sql
• File contains DDL statements for all entities

Review generated DDL:

• Compare with Flyway migrations
• Verify entity expectations match database schema
• Update V3 migration if discrepancies found

---

Step 4: Verify Gradle Task Works

Run documentation task:

./gradlew showDDLInstructions

Expected Result:

• Task executes successfully
• Instructions printed to console

---

Testing

Unit Tests

No unit tests required for this feature (configuration and migration files).

Integration Tests

Verify migration applies correctly:

• Existing integration tests should continue to pass
• V3 migration should not break existing tests
• Schema validation should catch mismatches

Recommended Test:

• Run all existing tests
• Verify no schema-related errors
• Verify entities can be persisted and retrieved

---

Troubleshooting

Migration Fails with Constraint Already Exists

Issue: Migration fails because constraint/index already exists.

Solution: The migration uses IF NOT EXISTS and DO $$ BEGIN ... END $$ blocks for idempotency. If you still see errors, check:

• Are constraint/index names exactly matching?
• Is the migration running against a fresh database?

Schema Validation Fails at Startup

Issue: Application fails to start with schema validation error.

Solution:

1. Check the error message for specific mismatches
2. Review V3 migration for missing changes
3. Compare generated DDL with Flyway migrations
4. Update V3 migration if needed
5. Ensure all migrations (V1, V2, V3) are applied

DDL Generation Produces Different Schema

Issue: Generated DDL doesn't match Flyway migrations.

Solution:

1. Review entity field types and annotations
2. Compare with V3 migration
3. Update V3 migration to match entity expectations
4. Ensure migration is idempotent

---

Next Steps

After completing this guide:

1. ✅ Production Infrastructure - Connection pooling, Actuator, JacksonConfiguration
2. ✅ Code Quality & Developer Experience - Checkstyle, logging configuration
3. ✅ Code Generation Infrastructure - OpenAPI Generator foundational setup
4. ✅ Test Infrastructure - Test profile configuration

---

Summary

This implementation provides:

• ✅ Schema Validation: Hibernate validates schema matches entities
• ✅ Schema Sync Migration: V3 migration ensures schema matches entity expectations
• ✅ DDL Generation Workflow: Developers can review entity schema expectations
• ✅ Production Safety: Prevents accidental schema changes

All changes are critical for production safety and should be implemented first before other enhancements.