Flyway afterMigrate.sql warnings: missing default values for is_user_admin and order_position

[stanleykc]

Summary

During Docker builds, Flyway's afterMigrate callback generates warnings about missing default values for certain columns. While the application starts correctly, these warnings indicate schema mismatches that should be addressed.

Warnings Observed

WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Field 'is_user_admin' doesn't have a default value (SQL State: HY000 - Error Code: 1364)
WARN  o.f.c.i.s.DefaultSqlScriptExecutor - DB: Field 'order_position' doesn't have a default value (SQL State: HY000 - Error Code: 1364)

Root Cause Analysis

1. is_user_admin Column

• V1 migration creates the column as isUserAdmin (camelCase)
• Hibernate expects is_user_admin (snake_case per naming strategy)
• afterMigrate.sql uses isUserAdmin which may not match the actual column name after Hibernate auto-schema updates

2. order_position Column

• Not defined in any Flyway migration
• Added by Hibernate auto-schema-gen (LIBRE311_AUTO_SCHEMA_GEN=update) based on Service.java entity
• Column created without a DEFAULT value
• afterMigrate.sql INSERT statements for services table don't include this column

Files Affected

• app/src/main/resources/local/afterMigrate.sql
• app/src/main/resources/db/migration/V1__create-initial-schema.sql
• app/src/main/java/app/model/service/Service.java (has orderPosition field)
• app/src/main/java/app/model/jurisdictionuser/JurisdictionUser.java (has isUserAdmin field)

Suggested Fixes

Option A: Update afterMigrate.sql (Quick Fix)

1. Change isUserAdmin to is_user_admin in jurisdiction_user INSERT
2. Add order_position column with value 0 to all services INSERT statements

Option B: Create Flyway Migration (Proper Fix)

1. Create V11__fix_column_defaults.sql to:
   • Add DEFAULT value to order_position column in services table
   • Rename isUserAdmin to is_user_admin (or add column alias)
2. Update afterMigrate.sql accordingly

Impact

• Current: Warnings only, no functional impact
• Risk: If strict SQL mode is enabled, INSERTs could fail

Related

• Branch: N/A (pre-existing issue)
• Discovered during: 010-service-editor-refinement testing