Source: config/migrations/20260412-objectbase-data-not-null.js

/**
 * Migration: ObjectBase.Data — fix NULLs and enforce NOT NULL DEFAULT '{}'
 *
 * Root cause of the ER_NET_PACKET_TOO_LARGE corruption incident (2026-04-12):
 * When ObjectBase.Data was NULL the persons endpoint returned it as the literal
 * string "null". The frontend echoed that string back in a subsequent PUT, where
 * it was spread into the request body and then JSON.stringify()ed on every save,
 * producing geometrically growing escaped backslash sequences.
 *
 * This migration:
 *   1. Replaces all NULL and literal 'null' values with '{}' in the current rows.
 *   2. Alters the column to NOT NULL DEFAULT '{}' so the DB rejects future NULLs.
 *
 * Note: system-versioned history rows are not writable; the ALTER TABLE will
 * handle them implicitly by coercing any remaining NULLs in history to the
 * column default during the table rebuild.
 */

export const id = '20260412-objectbase-data-not-null';

export const name = "ObjectBase.Data: fix NULLs and set NOT NULL DEFAULT '{}'";

/**
 * @param {{ query: Function }} api
 */
export const migrate = async ({ query }) => {
    // Fix current (non-historical) rows
    await query(`
        UPDATE ObjectBase
        SET Data = '{}'
        WHERE Data IS NULL OR Data = 'null'
    `, []);

    // Required flag: allows ALTER TABLE to modify the structure of a
    // system-versioned table including its history partition.
    // Without this MariaDB raises ER_ALTER_OPERATION_NOT_SUPPORTED_REASON.
    await query(`SET @@session.system_versioning_alter_history = KEEP`, []);

    // Disable strict mode for this session so that stale ENUM values in
    // history rows (e.g. the 'stage' column) produce silent truncation
    // warnings instead of errors during the full table rebuild.
    await query(`SET @@session.sql_mode = REPLACE(@@session.sql_mode, 'STRICT_TRANS_TABLES', '')`, []);

    // Alter column — with KEEP, MariaDB rebuilds both the current and history
    // partitions, coercing any remaining NULLs in history rows to '{}' as well.
    await query(`
        ALTER TABLE ObjectBase
        MODIFY COLUMN Data longtext NOT NULL DEFAULT '{}'
    `, []);
};