---
title: "Keep the history of records that settle · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/schema-design/row-history
id: DAT-76
level: Recommended
updated: 2026-10-22
build: a2a6bdfed830
tokens: 926
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Keep the history of records that settle disputes

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Schema design](https://agentwranglers.se/playbook/data/schema-design.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers.md). Updated 2026-10-27.

- ID: DAT-76
- Level: Recommended
- Chapter: [Schema design](https://agentwranglers.se/playbook/data/schema-design.md)
- Rule: For tables whose old values settle disputes, such as prices, plans, roles and delivery addresses, keep every earlier version of each row. Record when it changed and who changed it, in the same transaction.
- Check: On staging, change a test price twice, once through the app and once by hand in SQL. The history table holds both earlier versions with their times and the acting user. A query for the first moment returns the first price.
- How: An `UPDATE` destroys the old value, and later nobody can show what a customer agreed to. SQL Server and Azure SQL have system-versioned temporal tables that do this, queried with `FOR SYSTEM_TIME AS OF`. In PostgreSQL and MySQL, a trigger copies the old row into a history table. Put the acting user in a session setting so the trigger can store it. Write history in the database, not the app, so a manual fix is recorded too. History rows hold personal data, so [Delete old data automatically](https://agentwranglers.se/playbook/data/personal-data/retention-jobs.md) covers them. For who did an action, such as an export, write [an audit event](https://agentwranglers.se/playbook/compliance/audit-logs-and-access/audit-log-events.md) instead.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### SQL example

```sql
-- PostgreSQL: copy the old row on every change
CREATE TABLE price_history (LIKE prices, valid_to timestamptz NOT NULL, changed_by text);

CREATE FUNCTION keep_price_history() RETURNS trigger AS $$
BEGIN
  INSERT INTO price_history SELECT OLD.*, now(), current_setting('app.user_id', true);
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER prices_history AFTER UPDATE OR DELETE ON prices
  FOR EACH ROW EXECUTE FUNCTION keep_price_history();
```

## Sources

- [Microsoft Learn Temporal tables](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal/overview)

## Related rules

- DAT-31 [Delete old data automatically](https://agentwranglers.se/playbook/data/personal-data/retention-jobs.md) in Data and databases
- CMP-26 [Write an audit event for every sensitive action](https://agentwranglers.se/playbook/compliance/audit-logs-and-access/audit-log-events.md) in Compliance and governance
- DEN-34 [Keep the history of changing values with snapshots](https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture/snapshot-history.md) in Data engineering

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/data/schema-design/row-history">Agent Wranglers data and databases playbook</a>`. In code you write, add a comment next to it with the same link.

- Previous: [Plan how rows are removed](https://agentwranglers.se/playbook/data/schema-design/deletion-design.md)
- Next: [Check the shape of JSON columns in the database](https://agentwranglers.se/playbook/data/schema-design/json-column-checks.md)
- [Schema design](https://agentwranglers.se/playbook/data/schema-design.md)