Historical table mechanism and managing historical data
1. Overview
This document explains how the Platform stores historical data in a database, how historical (_hst) tables work, and how they update when the primary table structure changes. It details how the system tracks changes, what operations historical tables record, and how they preserve previous data versions.
2. Overview
The Platform allows developers to define tables in XML format using declarative approach. During deployment, Liquibase processes these structures, generates SQL code, and runs it in a PostgreSQL database.
To track historical changes, developers add the attribute ext:historyFlag="true" to a table definition. This automatically creates a historical table (_hst) that records all modifications.
3. Table structure
When a table includes historyFlag="true", Liquibase generates two tables:
-
Primary table (e.g.,
protocols) — stores current data. -
Historical table (
protocols_hst) — captures all changes in the primary table (insertions, updates, deletions).
<createTable tableName="protocols" ext:historyFlag="true">
<column name="protocol_id" type="UUID">
<constraints nullable="false" primaryKey="true" primaryKeyName="pk_protocols"/>
</column>
<column name="protocol_name" type="TEXT">
<constraints nullable="false"/>
</column>
</createTable>CREATE TABLE protocols (
protocol_id UUID PRIMARY KEY,
protocol_name TEXT NOT NULL,
ddm_created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
ddm_created_by TEXT NOT NULL,
ddm_updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
ddm_updated_by TEXT
);CREATE TABLE protocols_hst (
protocol_id UUID NOT NULL,
protocol_name TEXT NOT NULL,
ddm_created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
ddm_created_by TEXT NOT NULL,
ddm_dml_op CHAR(1) NOT NULL,
ddm_system_id UUID NOT NULL,
ddm_application_id UUID NOT NULL,
CONSTRAINT pk_protocols_hst PRIMARY KEY (protocol_id, ddm_created_at)
);4. Historical change mechanism
4.1. Insert operations
When a new record is inserted (INSERT) into the primary table, it does not appear in the historical table. The system starts tracking history only after the first update or deletion.
INSERT INTO protocols (protocol_id, protocol_name)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'Protocol A');4.2. Update operations
When a record is updated (UPDATE), the system copies the previous version into the historical table. The _hst table maintains a complete change history.
UPDATE protocols
SET protocol_name = 'Protocol A - Updated'
WHERE protocol_id = '550e8400-e29b-41d4-a716-446655440000';4.3. Delete operations
When a record is deleted (DELETE), the system moves it to the _hst table before removing it from the primary table.
DELETE FROM protocols
WHERE protocol_id = '550e8400-e29b-41d4-a716-446655440000';5. Preserving historical data when table structures change
Historical tables capture all changes to records, including updates, deletions, and other modifications. This ensures full change history, auditability, transparency, and the ability to restore previous data versions.
The system does not automatically clean up _hst tables. When the primary table structure changes, the system creates a new historical table that stores data in the format valid at the time of modification. This preserves historical data in its original structure.
This approach does not delete historical records; it adapts them to changes in the primary table. As a result, historical data remains accessible and retains the appropriate format, regardless of schema updates.
Although retention policies and data archiving are not currently available, the Platform plans to support these features in the future.
|