Date: 2026-03-26
Status: Planning
Author: Francois van Heyningen
Add version history tracking to ALL decision components using the same pattern as def_decision_tree_history and def_decision_matrix_history. Must be backwards compatible — existing data without versions gets auto-backfilled on first load.
| Component | Main Table | Nodes Table | History Table (new) | Status |
|---|---|---|---|---|
| Decision Tree | def_decision_tree |
def_decision_treenodes |
def_decision_tree_history |
Done |
| Decision Matrix | def_decision_matrix |
def_decision_matrix_axes + _cells |
def_decision_matrix_history |
Done |
| Scorecard | def_scorecard |
def_scorecard_characteristics |
def_scorecard_history |
TODO |
| Workflow | def_workflow |
def_workflow_nodes |
def_workflow_history |
TODO |
| Calculation | def_calculation |
def_calculation_steps |
def_calculation_history |
TODO |
All history tables follow the same structure:
CREATE TABLE IF NOT EXISTS def_{component}_history (
Guid VARCHAR(64) NOT NULL,
{ComponentName} VARCHAR(255) NOT NULL, -- e.g. DecisionName, WorkflowName, ScorecardName
Package VARCHAR(50) NOT NULL,
Module VARCHAR(255) DEFAULT 'Obj{Component}',
Version INT NOT NULL,
ThreeWords VARCHAR(255) DEFAULT NULL,
VersionDate DATETIME DEFAULT NULL,
NodeCount INT DEFAULT 0,
ChangedBy VARCHAR(255) DEFAULT NULL,
ChangeType VARCHAR(50) DEFAULT 'IMPORT',
ChangeNotes TEXT DEFAULT NULL,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ({ComponentName}, Package, Version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Existing records may not have a Version column, or have Version=0/NULL, or have no history record. The system must handle all these cases gracefully.
Each component's read() method gets a _ensure_versioned() call that:
ALTER TABLE ADD COLUMN IF NOT EXISTS Version INT NOT NULL DEFAULT 1def _ensure_versioned(self, component_name: str, package: str):
"""Ensure version tracking exists for this component.
Called from read() — transparent to callers."""
DMC = DecisionMatrixConstants # or relevant constants
# 1. Ensure Version column on main table
self.sql_execute(
f"ALTER TABLE {self.main_table} "
f"ADD COLUMN IF NOT EXISTS Version INT NOT NULL DEFAULT 1"
)
# 2. Ensure history table exists
self._create_table_if_not_exists(self.history_table)
# 3. Check for existing history record
history = self.sql_get_dictionary_list(
f"SELECT Version FROM {self.history_table} "
f"WHERE {self.name_column} = '{component_name}' "
f"AND Package = '{package}'"
)
if not history:
# Get current version from main table
current = self.sql_get_dictionary_list(
f"SELECT Version, COUNT(*) as cnt FROM {self.nodes_table} "
f"WHERE {self.name_column} = '{component_name}' "
f"AND Package = '{package}' "
f"GROUP BY Version"
)
version = current[0].get('Version', 1) if current else 1
node_count = current[0].get('cnt', 0) if current else 0
# Also ensure nodes table has Version column
self.sql_execute(
f"ALTER TABLE {self.nodes_table} "
f"ADD COLUMN IF NOT EXISTS Version INT NOT NULL DEFAULT 1"
)
# Backfill: set Version=1 on any nodes with Version=0 or NULL
self.sql_execute(
f"UPDATE {self.nodes_table} "
f"SET Version = 1 "
f"WHERE {self.name_column} = '{component_name}' "
f"AND Package = '{package}' "
f"AND (Version IS NULL OR Version = 0)"
)
# Create history record
self.sql_execute(
self.queries['insert_version_history'].format(
guid=self.get_uuid('HIST'),
component_name=component_name,
package=package,
version=version,
three_words='Initial backfill',
node_count=node_count,
changed_by='system-backfill',
change_type='BACKFILL',
change_notes='Auto-created from pre-versioning data'
)
)
self.debug(f"Backfilled version history for {component_name} v{version} ({node_count} nodes)")
When backfilling nodes (workflow nodes, scorecard characteristics, etc.):
Version = 1Version = MAX(Version) + 1read() always loads the latest version unless a specific version is requestedCreate ObjVersionMixin in factory.core/ObjVersionMixin.py:
_ensure_versioned(component_name, package) — backfill logic_record_version(component_name, package, version, three_words, node_count, changed_by, change_type, change_notes) — insert history_get_version_history(component_name, package) — list versions_get_latest_version(component_name, package) — get max version numberdef_scorecard_history table_ensure_versioned() to ObjScorecard.read()def_workflow_history table_ensure_versioned() to ObjWorkflow.read()def_calculation_history table_ensure_versioned() to ObjCalculation.read()ObjDecisionSwitch.read() to use the mixin instead of standalone logicdef_decision_tree_history backfill works the same wayread() don't need to know about versioning| File | Purpose |
|---|---|
factory.core/ObjVersionMixin.py |
Shared versioning mixin class |
| File | Change |
|---|---|
factory.core/ObjScorecard.py |
Add mixin, call _ensure_versioned() in read() |
factory.core/ObjScorecard.yaml |
Add history table schema + queries |
factory.core/ObjWorkflow.py |
Add mixin, call _ensure_versioned() in read() |
factory.core/ObjWorkflow.yaml |
Add history table schema + queries |
factory.core/ObjCalculation.py |
Add mixin, call _ensure_versioned() in read() |
factory.core/ObjCalculation.yaml |
Add history table schema + queries |
factory.core/ObjDecisionSwitch.py |
Retrofit to use mixin |
factory.core/ObjDecisionMatrix.py |
Use mixin |
factory.core/ObjConstants.py |
Add history constants for each component |