feat(data-layer): introduce Flyway migrations and versioned database schema

Jason Yangrequested to merge
feature/data-layer into main

Overview

This merge request introduces version-controlled database schema management for the project.

Previously, the SQLite schema was initialized directly inside Java code at application startup.
This caused several issues:

  • database structure was not trackable
  • schema changes were difficult to maintain
  • new developers could not reliably rebuild the environment

This MR migrates the project to Flyway-based schema migration.

Changes Included

1. Flyway Migration Support

  • Added Flyway dependency in pom.xml
  • Database schema is now initialized automatically during application startup
  • Schema history is recorded in flyway_schema_history

2. V1 Migration (Initial Schema)

Created V1__init.sql:

  • account
  • sync_record
  • sync_record_detail
  • validation_rule tables
  • seed rule: title is required

This allows the project to start from a completely clean environment.

3. V2 Migration (Schema Evolution)

Created V2__account_fields_and_indexes.sql:

  • Added OAuth-related fields to account
    • client_id
    • client_secret
    • access_token
    • link_status
  • Added important indexes for consistency and performance:
    • unique index on lower(shop_domain)
    • account default index
    • sync_record and sync_record_detail indexes

Result

The system can now:

  • start from an empty environment
  • automatically create the database
  • automatically apply schema upgrades
  • maintain schema version history

Current schema version: v2

How to Test

  1. Delete the database file:
  2. Start the application:
  3. Flyway should automatically run V1 and V2 migrations.

Notes for Reviewers

No application logic was changed.
This MR only affects the infrastructure (database initialization and schema management). Future schema changes will be added as new migration files (V3, V4, ...).

Merge request reports