Skip to content

Audit columns

The four columns that every audit table adds. An audit table is named audit_ and then the name of the table it protects, such as audit_core_Artists, and it holds a copy of every row that was changed or deleted.

This page is the dictionary for the table. It holds the definition and purpose of every field, and it is where a field is changed.

Fields

Field Key Type Required Allowed values or link Definition Purpose Notes
Audit_ID AUDIT_COLUMNS.AUDIT_ID Whole number Yes The surrogate key of the audit row. It counts up and is never reused. Identifies one recorded change.
Change_Kind AUDIT_COLUMNS.CHANGE_KIND One value from a list Yes Update, Delete Whether the old row was changed or deleted. Tells an undo whether to restore a value or a whole row.
Changed_Time AUDIT_COLUMNS.CHANGED_TIME Date and time (UTC) Yes The moment of the change. Orders the history of a row.
Changed_By AUDIT_COLUMNS.CHANGED_BY Text Yes The program part that made the change: the web interface, the API, the background worker or a migration. Traces a bad change to where it came from.

Constraints

What the database enforces on each field, beyond its type. A field that is not listed has no default, no uniqueness, no range and no reference. Every field marked Required is NOT NULL.

Field Default Unique Range or pattern Refers to
Audit_ID Yes
Change_Kind ref_Change_Kinds
Changed_Time a real instant, YYYY-MM-DDTHH:MM:SSZ

Rules across fields:

  • Every other column of an audit table is copied from the table it protects, with the same name and type, and is never required, so a row that was loaded in a different shape can still be recorded.
  • A column dropped from a protected table stays in its audit table.
  • A trigger on the protected table writes the old row here on every update and delete. Undoing a change means copying the old row back.

The table is audit_<table> (one for every core and research table). Every link refuses a missing target and refuses the deletion of a row that another row still points to.

Choice values

Change_Kind

Value Meaning Purpose
Update The row was changed, and the audit row holds the values before the change.
Delete The row was deleted, and the audit row holds the whole row.