歡迎來到第二十三天！生產環境資料庫切忌手動在 Console 執行 SQL，必須引入 Flyway 資料庫版本控制，確保正式 DB 與開發環境的 Schema 100% 同步。

1. Flyway SQL Migration 腳本實作 (V1__Create_Initial_Schema.sql)

在 Ktor 專案 src/main/resources/db/migration/ 目錄下建立遷移檔：

-- V1__Create_Initial_Schema.sql

-- 建立會員主表
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    line_user_id VARCHAR(64) UNIQUE NOT NULL,
    display_name VARCHAR(128) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 建立遊戲狀態與擺設 JSONB 表
CREATE TABLE IF NOT EXISTS game_state (
    user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    coins BIGINT NOT NULL DEFAULT 0,
    gems BIGINT NOT NULL DEFAULT 0,
    room_layout_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    last_harvest_time BIGINT NOT NULL DEFAULT (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000)
);

-- 建立 GIN 索引最佳化 JSONB 搜尋效能
CREATE INDEX IF NOT EXISTS idx_game_state_room_layout_gin 
ON game_state USING gin (room_layout_json);

2. Ktor 初始化時自動執行 Flyway Migration

// DatabaseFactory.kt
object DatabaseFactory {
    fun init(config: ApplicationConfig) {
        val hikariConfig = HikariConfig().apply {
            jdbcUrl = config.property("db.jdbcUrl").getString()
            username = config.property("db.user").getString()
            password = config.property("db.password").getString()
            maximumPoolSize = 10
            isAutoCommit = false
            transactionIsolation = "TRANSACTION_REPEATABLE_READ"
        }

        val dataSource = HikariDataSource(hikariConfig)

        // 自動執行 Flyway Migration
        val flyway = Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration")
            .baselineOnMigrate(true)
            .load()
        flyway.migrate()

        Database.connect(dataSource)
    }
}

💡 鐵人賽小知識：為什麼 PostgreSQL JSONB 欄位一定要建立 GIN 索引？

Tech Tip：傳統 B-Tree 索引只能針對單一數值或字串進行比對。而 GIN (Generalized Inverted Index) 索引是專為 JSONB 設計的倒排索引。建立 GIN 索引後，當執行 WHERE room_layout_json @> '[{"itemId": "chair_01"}]' 搜尋大廳擺設時，PostgreSQL 無需全表掃描（Seq Scan），可在毫秒內完成檢索！