Source: config/migrations/20260520-embeddings-vector-1536.js

/**
 * Migration: Resize AIEmbeddings.Embedding column from VECTOR(768) to VECTOR(1536)
 *
 * Cohere embed-multilingual-v4.0 uses 1536 dimensions (vs. 768 from OpenAI
 * text-embedding-3-small).  MariaDB cannot ALTER a VECTOR column in-place, so
 * we drop the old column and re-add it with the new size.  All existing
 * embeddings are cleared — they will be regenerated on next entity update.
 *
 * The vector-key index must be dropped before the column can be dropped.
 */

export const id = '20260520-embeddings-vector-1536';

export const name = 'Resize AIEmbeddings.Embedding to VECTOR(1536) for Cohere v4';

/**
 * @param {{ query: Function }} api
 */
export const migrate = async ({ query }) => {
    // 1. Drop the vector-key index (required before dropping the VECTOR column)
    await query(`ALTER TABLE AIEmbeddings DROP INDEX IF EXISTS idx_embedding_vector`, []);

    // 2. Drop the old 768-dim column.  MariaDB DROP COLUMN on a VECTOR is
    //    allowed once the vector-key index has been removed.
    await query(`ALTER TABLE AIEmbeddings DROP COLUMN IF EXISTS Embedding`, []);

    // 3. Re-add with 1536 dimensions
    await query(`ALTER TABLE AIEmbeddings ADD COLUMN Embedding VECTOR(1536) NOT NULL COMMENT 'Vector embedding for similarity search - 1536 dimensions for Cohere embed-multilingual-v4.0' AFTER EmbeddingDimension`, []);

    // 4. Recreate the vector-key index
    await query(`ALTER TABLE AIEmbeddings ADD VECTOR KEY idx_embedding_vector (Embedding)`, []);

    // 5. Reset EmbeddingModel so existing rows are re-embedded on next write.
    //    Old rows with model='text-embedding-3-small' and dim=768 now have an
    //    empty/invalid Embedding.  Setting model to '' triggers a full re-index
    //    because updateEntityEmbedding compares ContentHash + EmbeddingModel.
    await query(`UPDATE AIEmbeddings SET EmbeddingModel = '' WHERE EmbeddingModel != ''`, []);

    console.log('[migration] AIEmbeddings.Embedding resized to VECTOR(1536). Existing embeddings cleared for re-index.');
};