FinHistoryRecordsPlugin

From iDempiere en

Finmatica History Records — Time Machine plug-in for iDempiere

Plugin Info
Status Not Usable. Pull Requests not yet merged
Target iDempiere 13 "Orion" LTS
License GPLv2 (same as iDempiere core)
Repository fin-history-records on GitHub
Author Stefano Coletta ([email protected])
Company Finmatica S.p.A.
Organization Associazione ERP Open source Italia : https://www.erp-opensource.it/

fin-history-records is an OSGi plug-in suite that brings record-level history tracking and time-travel queries to any iDempiere window. Once a table is marked for history mode, every INSERT/UPDATE/DELETE is mirrored on a _HST shadow table together with the validity interval (HSTFromDate, HSTToDate). The Time Machine layer rewrites read queries transparently, so the same UI can show the data as it was at any past point in time, with no Java code change in the consumer.

A "Create Historical Record" toolbar button lets the operator snapshot the state of a row at a given business date — useful for legal archiving, versioned masters (price lists, BOMs, structures, taxes) and audit trails.

Sections
References · Features · Architecture overview · Requirements · Build · Install · Usage · User Guide · Compatibility matrix · Authors · License

References

Features

The plug-in answers a simple question that every long-lived ERP eventually faces:

What did this record look like back then?

When you reprint an invoice issued three years ago, the customer's tax ID must appear as it was on that invoice's date — not as it is today. When you audit a sales order from 2022, you want to see the price list, the discount, and the shipping address that applied at that time, not the latest version. Without history tracking, you only ever see the current state: each UPDATE to the row destroys the previous value forever.

This plug-in adds transparent time-travel to any iDempiere table of your choice.

A worked example — when a customer changes Tax ID

Acme S.p.A. is a customer with C_BPartner_ID = 123 and TaxId = 'IT01234567890'. On 2024-06-01, after a corporate restructuring, Acme's tax ID changes to IT09876543210. You update the record in iDempiere.

Without the plug-in, the table only remembers the current value: 

SELECT TaxId FROM C_BPartner WHERE C_BPartner_ID = 123;
-- → IT09876543210

If you now reprint invoice INV-2024-0021 dated 2024-02-15, the new tax ID appears on it — which is legally wrong: the invoice was issued under the old ID, the new ID didn't exist yet.

With the plug-in installed, after flagging C_BPartner as historicized, the database keeps both versions side by side in a shadow table C_BPartner_HST:

HSTFromDate HSTToDate TaxId
2020-01-01 2024-05-31 IT01234567890
2024-06-01 (null) IT09876543210

The reprint code carries the invoice date and the same identical query is executed, but in time-travel mode: 

SELECT TaxId FROM C_BPartner WHERE C_BPartner_ID = 123;
-- with HistorySelectionData = 2024-02-15
-- → IT01234567890   (the correct historical value)

The application code is unchanged. The plug-in rewrites the SQL on the fly to look up C_BPartner_HST filtered by the validity interval. The same mechanism works automatically on lookups (combo boxes), info windows, reports, callouts, search popups — anywhere in iDempiere where a SELECT runs.

What's in the box

Capability What it does
One-click historicization Flag a table with History Mode = Time Machine in the dictionary. The plug-in creates the shadow _HST table for you and starts mirroring every insert/update/delete automatically.
Transparent time-travel reads Any SELECT on a historicized table is rewritten to return the version valid at the chosen business date. Existing screens, reports and processes work as-is, no migration.
Manual snapshot The Create Historical Record toolbar action lets the operator freeze the current row at a chosen business date. Useful for legal archiving, slow-changing dimensions, versioned masters (price lists, BOMs, tax rates).
Automatic date-source per document A configuration table declares which date drives history reads on each window. E.g. "when opening a Sales Order, resolve all lookups at the order's date, not at today's date."
Read-only protection in past mode When you're looking at historicized data the form goes read-only, so the history can't be corrupted by accident.

What's not yet ported in this community release

The original Finmatica edition includes three extra UI features that have not been ported to this first community release. Workarounds are listed where applicable.

  • Time Machine date-picker panel — a sticky toolbar widget to choose the time-travel date on the fly. Workaround: the date can be set via Java API (HistorySelectionData.setCurrent(date)) or via a custom callout.
  • Verify Change Impact info window — a screen that lists which other records depend on a historicized one. No workaround in this release.
  • Tree Maintenance UI — a tree-aware version-management screen for hierarchical data. No workaround in this release.

Architecture overview

The plug-in is split in 3 OSGi bundles + 2 core extension points that must exist in org.adempiere.base:

  • ISQLStatementRewriter (PR #3279 — IDEMPIERE-7023) — hook in Convert.java, DB_Oracle and DB_PostgreSQL. Allows a plugin to rewrite any SQL statement before execution.
  • IUIBehaviour (PR #3280 — IDEMPIERE-7024) — hook in MLookup and GridField.isEditable(). Allows a plugin to control lookup cacheability and field/tab editability.

Both extension points are no-op by default: when no service is registered, the core behaves exactly as vanilla iDempiere.

The 3 OSGi bundles:

Bundle Role
it.finmatica.history-records Core: model validator, DB event handler, SQL rewriter implementation, UI behaviour implementation, structure migration (DDL + dictionary).
it.finmatica.history-records.ui.zk UI (ZK): toolbar action Create Historical Record, process CreateHistoryRecord, process factory.
it.finmatica.history-records-feature OSGi feature aggregating both bundles.

Requirements

Without these PRs the plug-in still installs and the toolbar button works, but the transparent Time Machine query rewriting and lookup cache invalidation are disabled.

Build

The current release follows the clone-inside-workspace model. Drop the 3 bundles into an existing iDempiere workspace and build with the standard iDempiere Maven reactor: 

# 1. clone next to org.adempiere.base, NOT inside another folder
cd <your-idempiere-workspace>/
git clone https://github.com/scolettads/fin-history-records.git
mv fin-history-records/it.finmatica.history-records          .
mv fin-history-records/it.finmatica.history-records.ui.zk    .
mv fin-history-records/it.finmatica.history-records-feature  .

# 2. register the 3 modules in the root pom.xml
#    (add <module>it.finmatica.history-records</module>          )
#    (add <module>it.finmatica.history-records.ui.zk</module>    )
#    (add <module>it.finmatica.history-records-feature</module>  )

# 3. add the feature to org.adempiere.server-feature/server.product
#    <feature id="it.finmatica.history-records-feature"/>

# 4. build
./mvnw verify -DskipTests

A standalone Maven build (mvn package directly inside this repo, without the iDempiere reactor) is planned for the next release.

Install

If you have a compiled iDempiere 13 server, the artefacts to drop into plugins/ are: 

plugins/it.finmatica.history-records_13.0.0.<qualifier>.jar
plugins/it.finmatica.history-records.ui.zk_13.0.0.<qualifier>.jar

After restart, run the 2Pack at:

Application Dictionary → System Admin → 2Pack

importing the dictionary export shipped under dictionary/ (work in progress — will be added in a follow-up release together with a default seed configuration).

Usage

  1. Open Application Dictionary → Table & Column, pick a table, and set HST_HistoryMode = 'TimeMachine' (or Storicized).
  2. The plug-in creates the shadow <tablename>_HST automatically.
  3. From any window backed by that table, click the Create Historical Record toolbar button to snapshot the current row at a chosen business date.
  4. With both upstream PRs in place, any subsequent query against the table while HistorySelectionData is set returns the historicized rows for that date instead of the current ones.

Compatibility matrix

Plug-in version iDempiere version DB engines PR-A (#3279) PR-B (#3280) Notes
13.0.0 (this) 13 Orion LTS PG 14+, Oracle 19+ required required first community release

User Guide

2.1 Configure a table for historicization

In Application Dictionary → Table and Column, open the target table (e.g. C_BPartner):

  1. Set the field History Mode (HST_HistoryMode) to one of the supported values (see Appendix A)
  2. Save

2.2 Mark the columns to track

In the Column tab of the same window, browse the columns and tick the checkbox Is Hst Column (HST_IsHstColumn) only on the columns you want to track in time. Other columns will keep the current value only.


Note.gif Note:

The "Is Hst Column" field is automatically displayed only on tables whose History Mode is S (Historicizing) or T (Time Machine) — it stays hidden otherwise to keep the standard Column tab clean.

2.3 Create the History table

Once columns are tagged, create the *_HST companion table:

  1. Same Table and Column window → New record
  2. Table Name = C_BPartner_HST
  3. Access Level = Client + Org
  4. History Mode = None (the _HST table is itself not historicized)
  5. Add only the columns that were tagged as HST_IsHstColumn = Y in the source table, plus the technical columns:
    • C_BPartner_HST_ID (primary key)
    • C_BPartner_ID (foreign key to source)
    • HSTFromDate, HSTToDate (validity interval)
    • HSTActualRecord (List: Y = current, N = past, F = future)
  6. Run process Synchronize Column to create the physical table on the database

2.4 Add a History sub-tab to the source window

Open Window, Tab & Field → Business Partner window and create a new tab:

  • Name = History
  • Table = C_BPartner_HST
  • Tab Level = 1 (sub-tab of the main Business Partner tab)
  • Sequence = a value greater than the main tab's sequence

Then run Create Fields to materialize the visual fields. After a cache reset, the History sub-tab will appear in the Business Partner window.

2.5 The "Create History Record" toolbar button

Once a table is configured, a new toolbar button Create History Record appears on its window.

The button appears only on tables that satisfy both conditions:

  • HST_HistoryMode is S or T
  • A sub-tab pointing to the _HST table exists in the same window

Clicking the button opens a dialog asking for a Date. On confirmation, the plugin:

  1. Reads the current values of the source record
  2. Inserts a new row into the _HST table with HSTFromDate = Date, HSTToDate = NULL, HSTActualRecord = Y
  3. If a previous "current" row existed, closes it by setting HSTToDate = Date - 1 and HSTActualRecord = N
  4. Refreshes the window

The result is a clean historical trail: at any point in time only one row has HSTActualRecord = Y; the rest are past (N) or future (F) versions.

2.6 Working with the History sub-tab

Each row in the History sub-tab represents the state of the record valid in a date interval. The three technical columns drive interpretation:

Column Meaning
HSTFromDate Start date of validity for this snapshot
HSTToDate End date of validity. NULL means still open (no successor defined yet)
HSTActualRecord Y = currently valid snapshot, N = past snapshot, F = future-dated snapshot

What happens when the user edits the main record

When the user saves a change on the main record (e.g. updates TaxID on Business Partner), the plugin updates only the row in the History sub-tab marked as HSTActualRecord = Y — the currently-valid snapshot. Past snapshots (HSTActualRecord = N) are never modified. They remain frozen as they were when the snapshot was sealed. This guarantees that historical documents issued during that interval keep referring to the original values.

Concrete example on C_BPartner:

Action Effect on main record Effect on C_BPartner_HST
User modifies TaxID from IT12345 to IT99999 C_BPartner.TaxID = 'IT99999' The single row with HSTActualRecord = Y is updated: TaxID = 'IT99999'. The previous rows (HSTActualRecord = N) keep IT12345
User clicks "Create History Record" at date 2026-06-01 unchanged A new row is inserted with HSTFromDate = 2026-06-01, HSTToDate = NULL, HSTActualRecord = Y. The previously current row gets HSTToDate = 2026-05-31, HSTActualRecord = N and becomes a sealed past snapshot

Time Machine mode (HST_HistoryMode = 'T'): when the table is configured in Time Machine mode, reading the master table from a historical context automatically resolves the SELECT against the matching _HST row. Reports, dashboards and document generation that re-read the master record at a back-date show the historical values as if they were the current ones. An invoice issued on January 15th, when reprinted in May, still shows the customer's tax ID as it was on January 15th.

Appendix A — HST_HistoryMode values

Value Name When to choose
N None The table has no history; default for all tables out of the box.
S Historicizing Master data with infrequent changes managed by the user. The user explicitly creates snapshots via the toolbar button. Examples: customer master data, product attributes.
T Time Machine High-frequency changes where the application must transparently read past values without the user knowing. Examples: pricelist, tax rate, exchange rate.
L Log Audit-only mode: every UPDATE writes an entry to the _HST table for compliance, but transparent queries are not rewritten.
V View Reserved for database views — the plugin treats the view as a read-only history surface without generating a separate _HST physical table.

Authors & contributors

Special thanks to the iDempiere core team for reviewing the upstream extension-point proposals (IDEMPIERE-7023, IDEMPIERE-7024).

License

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License version 2 as published by the Free Software Foundation. It is identical to the license of the iDempiere core.

Retrieved from "https://wiki.idempiere.org/w-en/index.php?title=FinHistoryRecordsPlugin&oldid=24040"
Cookies help us deliver our services. By using our services, you agree to our use of cookies.
GNU-Lizenz für freie Dokumentation 1.3 oder höher
Powered by MediaWiki