Skip to content

Unify our log tables into a single table #8635

@jniles

Description

@jniles

We currently have the inventory_log table that provides data on what changed and who changed it in the inventory. See below:

DROP TABLE IF EXISTS `inventory_log`;
CREATE TABLE `inventory_log` (
`uuid` BINARY(16) NOT NULL,
`inventory_uuid` BINARY(16) NOT NULL,
`log_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`text` JSON,
`user_id` SMALLINT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`uuid`),
KEY `inventory_uuid` (`inventory_uuid`),
KEY `user_id` (`user_id`),
CONSTRAINT `inventory_log__user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;

We also have the debtor_group_history table that does a similar thing for the debtor group history.

Similarly, the stock_adjustments table has a similar _log table. We also have a transaction_history table that stores the history of a transaction editing.

We clearly have some issues with data quality (see #8634) and users editing values after they are used. I propose we design a new history table that unifies these operations into a single edit log table. Let's call it modification_history in this issue. In it, we would store:

  1. The table name of the table being updated (e.g. posting_journal, inventory, patient).
  2. The primary key column name of the table in question (e.g. record_uuid, id, etc).
  3. The primary key itself (the value of the id or record_uuid or whatever).
  4. The user who updated it (user_id)
  5. The action (either UPDATE or DELETE).
  6. The timestamp of the update (created_at).
  7. The old_values of each item updated.
  8. The new_values of each item updated.

If we had this information, we could recreate any history we wanted. For example, we could write custom history parsers for each that read the data out of the modification_history where table_name = "XXY" and produce a table of modifications such as:

user action description timestamp
jniles DELETE Deleted account (15):  "DIVERS INCOMES" 3/11/26 09:54
lomamech UPDATE Updated inventory (8dd5ad26-1d27-11f1-8b3c-6b83394eee19): Display name ("Amoxy" -> "Amoxyciline"). 3/11/26 09:54
lomamech UPDATE Updated inventory (8dd5ad26-1d27-11f1-8b3c-6b83394eee19): Price (5 USD -> 3.5 USD) 3/11/26 09:54
jmcameron UPDATE Updated posting_journal (d2380dba-1d27-11f1-94e6-83ee50786666): Description ("Sold the farm" -> "Sold part of the farm"). 3/11/26 09:54

This would allow us to store a standardized description of the history in any language we choose.

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions