A record of who changed what, that nobody can change

Difficulty 4/5Subscribers

New to this? Learn it first: History: keep every version

HR keeps salaries. Legal keeps asking who changed them.

What the audit has to do:

Every change to an employee's salary leaves a record: the employee, the old amount, the new amount, and when. Nobody should have to remember to write that record. Updating the salary is what creates it.
A change that does not move the salary is not a change and does not deserve a row.
The log is append-only. Once a row is written it cannot be edited and it cannot be deleted. An audit trail that the audited party can rewrite is not evidence of anything.
The salary itself stays editable. This is a log, not a lock.

employees already exists, with salaries in it.

The contract

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

employees
idnamesalary_cents
salary_changes
idemployee_idold_centsnew_centschanged_at

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE employees (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         TEXT NOT NULL,
  salary_cents INTEGER NOT NULL CHECK (salary_cents >= 0)
);

INSERT INTO employees (name, salary_cents) VALUES ('Ana', 500000), ('Bruno', 600000);

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…