export const id = '20260626-recreate-transactions-with-data';
export const name = 'Recreate Transactions with Data JSON column, add PERSISTENT GENERATED COLUMNS';
/**
* Drops and recreates Transactions + TransactionLines tables.
*
* The new Transactions table uses a `Data` JSON column as single source of truth.
* Previously separate columns (DocumentNumber, Date, Status, TransactionType,
* TotalAmount, DocumentSequenceNumber, FullTextIndex) are now PERSISTENT
* GENERATED COLUMNS derived from `Data`.
*
* TransactionLines stays unchanged.
*/
export const migrate = async ({ query }) => {
// Step 1: Drop TransactionLines first (depends on Transactions FK)
await query('DROP TABLE IF EXISTS TransactionLines', []);
await query('DROP TABLE IF EXISTS Transactions', []);
// Step 2: Transactions neu — Data + PERSISTENT GENERATED COLUMNS
await query(`CREATE TABLE Transactions (
UID binary(16) NOT NULL,
-- Single Source of Truth
Data longtext NOT NULL DEFAULT '{}',
-- PERSISTENT GENERATED COLUMNS (indiziert, aus Data)
DocumentNumber varchar(50) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.documentNumber'))
) PERSISTENT,
DocumentSequenceNumber int(11) GENERATED ALWAYS AS (
JSON_EXTRACT(Data, '$.documentSequenceNumber')
) PERSISTENT,
\`Date\` date GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.date'))
) PERSISTENT,
Status enum('active','posted','storno') GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.status'))
) PERSISTENT,
TransactionType varchar(64) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.transactionType'))
) PERSISTENT,
TotalAmount bigint(20) GENERATED ALWAYS AS (
JSON_EXTRACT(Data, '$.totalAmount')
) PERSISTENT,
FullTextIndex varchar(512) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.fullTextIndex'))
) PERSISTENT,
Description varchar(255) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.description'))
) PERSISTENT,
ExternalReference varchar(100) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.externalReference'))
) PERSISTENT,
-- CreatedAt (ISO-String aus Data, ORDER BY + UNIX_TIMESTAMP funktionieren via MariaDB-Konvertierung)
CreatedAt varchar(30) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(Data, '$.createdAt'))
) PERSISTENT,
-- UIDUser (nur für manuelle Buchungen, bleibt real)
UIDUser binary(16) DEFAULT NULL,
-- DocumentCount (real, wird von documentService per Subquery gesetzt)
DocumentCount int(11) NOT NULL DEFAULT 0,
-- Fremdschlüssel (binary(16), bleiben reale Spalten)
UIDCostCenter binary(16) DEFAULT NULL,
UIDFiscalYear binary(16) DEFAULT NULL,
UIDOrganization binary(16) DEFAULT NULL,
UIDDefaultAccount binary(16) DEFAULT NULL,
-- UIDOriginalTransaction für Storno-Verkettung (nur storno-TX)
UIDOriginalTransaction binary(16) DEFAULT NULL,
-- IBANHash (binary, bleibt reale Spalte)
IBANHash binary(32) DEFAULT NULL COMMENT 'HMAC-SHA256 IBAN-Hash',
-- System-Versionierung
ValidFrom timestamp(6) GENERATED ALWAYS AS ROW START,
ValidUntil timestamp(6) GENERATED ALWAYS AS ROW END,
TUID varchar(48) GENERATED ALWAYS AS (
lcase(concat_ws('-', hex(substr(UID,5,4)), hex(substr(UID,3,2)),
hex(substr(UID,1,2)), hex(substr(UID,9,2)), hex(substr(UID,11))))
) VIRTUAL,
-- Constraints
PRIMARY KEY (UID, ValidUntil),
UNIQUE KEY idx_tx_unique_document (UIDOrganization, TransactionType, DocumentNumber, ValidUntil),
KEY idx_tx_date (\`Date\`),
KEY idx_tx_fiscalyear (UIDFiscalYear),
KEY idx_tx_org (UIDOrganization),
KEY idx_tx_type (TransactionType),
KEY idx_tx_sequence (UIDOrganization, UIDFiscalYear, TransactionType, DocumentSequenceNumber),
KEY idx_tx_document_number (DocumentNumber),
KEY idx_tx_external_ref (ExternalReference),
KEY idx_tx_ibanhash (IBANHash),
FULLTEXT KEY idx_tx_fulltext (FullTextIndex),
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidUntil)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'2025-01-01 00:00:00' (
PARTITION pminus14 HISTORY ENGINE = InnoDB,
PARTITION pminus13 HISTORY ENGINE = InnoDB,
PARTITION pminus12 HISTORY ENGINE = InnoDB,
PARTITION pminus11 HISTORY ENGINE = InnoDB,
PARTITION pminus10 HISTORY ENGINE = InnoDB,
PARTITION pminus9 HISTORY ENGINE = InnoDB,
PARTITION pminus8 HISTORY ENGINE = InnoDB,
PARTITION pminus7 HISTORY ENGINE = InnoDB,
PARTITION pminus6 HISTORY ENGINE = InnoDB,
PARTITION pminus5 HISTORY ENGINE = InnoDB,
PARTITION pminus4 HISTORY ENGINE = InnoDB,
PARTITION pminus3 HISTORY ENGINE = InnoDB,
PARTITION pminus2 HISTORY ENGINE = InnoDB,
PARTITION pminus1 HISTORY ENGINE = InnoDB,
PARTITION pcurrent CURRENT ENGINE = InnoDB
)`, []);
// Step 3: TransactionLines neu (unverändertes Schema)
await query(`CREATE TABLE TransactionLines (
UID binary(16) NOT NULL,
UIDTransaction binary(16) NOT NULL,
UIDAccount binary(16) DEFAULT NULL,
UIDCostCenter binary(16) DEFAULT NULL,
DebitCredit enum('D','C') NOT NULL,
Amount bigint(20) NOT NULL,
Currency char(3) NOT NULL DEFAULT 'EUR',
TaxRate decimal(5,2) DEFAULT NULL,
TaxCode varchar(10) DEFAULT NULL,
Description varchar(255) DEFAULT NULL,
Sequence int(11) DEFAULT 0,
ValidFrom timestamp(6) GENERATED ALWAYS AS ROW START,
ValidUntil timestamp(6) GENERATED ALWAYS AS ROW END,
TUID varchar(48) GENERATED ALWAYS AS (
lcase(concat_ws('-', hex(substr(UID,5,4)), hex(substr(UID,3,2)),
hex(substr(UID,1,2)), hex(substr(UID,9,2)), hex(substr(UID,11))))
) VIRTUAL,
TUIDTransaction varchar(48) GENERATED ALWAYS AS (
lcase(concat_ws('-', hex(substr(UIDTransaction,5,4)), hex(substr(UIDTransaction,3,2)),
hex(substr(UIDTransaction,1,2)), hex(substr(UIDTransaction,9,2)), hex(substr(UIDTransaction,11))))
) VIRTUAL,
PRIMARY KEY (UID, ValidUntil),
KEY idx_txline_tx (UIDTransaction),
KEY idx_txline_costcenter (UIDCostCenter),
KEY idx_txline_account (UIDAccount),
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidUntil)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'2023-04-06 00:00:00' (
PARTITION pminus14 HISTORY ENGINE = InnoDB,
PARTITION pminus13 HISTORY ENGINE = InnoDB,
PARTITION pminus12 HISTORY ENGINE = InnoDB,
PARTITION pminus11 HISTORY ENGINE = InnoDB,
PARTITION pminus10 HISTORY ENGINE = InnoDB,
PARTITION pminus9 HISTORY ENGINE = InnoDB,
PARTITION pminus8 HISTORY ENGINE = InnoDB,
PARTITION pminus7 HISTORY ENGINE = InnoDB,
PARTITION pminus6 HISTORY ENGINE = InnoDB,
PARTITION pminus5 HISTORY ENGINE = InnoDB,
PARTITION pminus4 HISTORY ENGINE = InnoDB,
PARTITION pminus3 HISTORY ENGINE = InnoDB,
PARTITION pminus2 HISTORY ENGINE = InnoDB,
PARTITION pminus1 HISTORY ENGINE = InnoDB,
PARTITION pcurrent CURRENT ENGINE = InnoDB
)`, []);
};