Source: config/migrations/20260329-example.js

/**
 * Migration: Example — shows both SQL and JSON data migration patterns
 *
 * This file serves as a template. Copy it, give it a sequential name like
 * `20260401-my-change.js`, update `id`, `name`, and implement `migrate`.
 *
 * Naming convention:  YYYYMMDD-short-description.js
 * Files run in alphabetical (i.e. date) order, each exactly once.
 */

/** Stable unique identifier — never change this after the migration has been applied */
export const id = '20260329-example';

/** Human-readable description shown in progress output */
export const name = 'Example migration (SQL + JSON data transform)';

/**
 * @param {{ query: Function, transaction: Function, UUID2hex: Function, HEX2uuid: Function }} api
 */
export const migrate = async ({ query, transaction, UUID2hex, HEX2uuid }) => {
    // --- SQL example: add a column idempotently ---
    // await query(`
    //     ALTER TABLE Member ADD COLUMN IF NOT EXISTS NewField varchar(64) DEFAULT NULL
    // `, []);

    // --- JSON data transform example: backfill a new field in Member.Data ---
    // const rows = await query(
    //     `SELECT UID, Data FROM Member WHERE JSON_VALUE(Data, '$.newField') IS NULL`,
    //     [], { cast: ['json'] }
    // );
    // for (const row of rows) {
    //     const updated = { ...row.Data, newField: 'defaultValue' };
    //     await query(`UPDATE Member SET Data = ? WHERE UID = ?`, [JSON.stringify(updated), row.UID]);
    // }

    // --- Combined example: schema change + data backfill in one transaction ---
    // await transaction(async (connection) => {
    //     await connection.query(`ALTER TABLE ObjectBase ADD COLUMN IF NOT EXISTS ExtraFlag tinyint(1) DEFAULT 0`);
    //     await connection.query(`UPDATE ObjectBase SET ExtraFlag = 1 WHERE Type = 'person'`);
    // });
};