Audit Fields in Database Design

Audit fields are standard columns added to tables purely to answer "who changed this, and when" — separate from the business data itself. The most common set is created_at, updated_at, and created_by (sometimes updated_by as well), and they earn their place on nearly every table in a production schema because they are cheap to add and disproportionately valuable when something needs to be debugged, disputed, or reported on later.

Without audit fields, answering a question like "was this price always wrong, or did someone change it yesterday" requires guesswork or external logs that may not exist. With them, the answer is a single query against the table itself.

Why it matters

"When did this change, and who changed it?" is asked in every bug investigation, customer dispute and compliance review. If the answer was never recorded it cannot be recovered afterwards. A few standard columns on every table cost almost nothing and repeatedly save the day.

created_at and updated_at

created_at records the exact moment a row was inserted and should never change afterward; updated_at records the most recent modification and should be refreshed on every update. Both should default to the current timestamp at insert time, and updated_at is best maintained automatically (via a trigger or an ORM hook) rather than relying on every application code path to remember to set it manually — a single forgotten update statement anywhere in the codebase otherwise leaves it stale.

created_by and updated_by

In any system with more than one user or service capable of writing data, created_by (and often updated_by) records which user, account, or service performed the write. This is essential for accountability in collaborative systems — knowing which staff member adjusted a price, which admin deleted a record, or which background job wrote a batch of rows — and it is far harder to reconstruct after the fact than to capture at write time.

Why Audit Fields Matter Beyond Debugging

Audit fields support far more than developer debugging: customer support needs created_at to confirm when an order was placed, finance needs updated_at and updated_by to investigate a disputed price change, and compliance requirements in many industries mandate a traceable record of who modified sensitive data. Because retrofitting audit fields onto a live table with years of existing rows means those historical rows will have incomplete or null audit data, it is far cheaper to include these columns from the very first migration of a table than to add them later.

• created_at — TIMESTAMP, set once at insert, never modified afterward.
• updated_at — TIMESTAMP, refreshed automatically on every update.
• created_by — references the user/service that inserted the row.
• updated_by — references the user/service that made the most recent change.

Example

CREATE TABLE invoices (
    id           BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    amount       DECIMAL(12, 2) NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by   BIGINT NOT NULL REFERENCES users(id),
    updated_by   BIGINT NOT NULL REFERENCES users(id)
);

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_invoices_updated_at
BEFORE UPDATE ON invoices
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Inserting a row stamps created_at and updated_at with the same
timestamp and records the inserting user via created_by. Any later
UPDATE to the row automatically refreshes updated_at through the
trigger regardless of which application code path performed the
update, so it can never be accidentally left stale.

Exercise

Create an articles table with a created_at column that is filled in automatically with the current time and an updated_at column that starts empty. Insert an article, then update its title and set updated_at to the current time in the same statement. Finally check that both timestamps are set and that the update is not earlier than the creation.

Expected output: has_created | updated_ok then 1 | 1

-- TODO: articles (id, title, created_at filled automatically, updated_at)

INSERT INTO articles (id, title) VALUES (1, 'Draft');

-- TODO: change the title to 'Final' and set updated_at to the current time

SELECT created_at IS NOT NULL AS has_created, updated_at >= created_at AS updated_ok
FROM articles;

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);

INSERT INTO articles (id, title) VALUES (1, 'Draft');

UPDATE articles
SET title = 'Final', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

SELECT created_at IS NOT NULL AS has_created, updated_at >= created_at AS updated_ok
FROM articles;

Quick quiz

Interview questions