Soft deletes, audit trails and history tables

Deleting data is rarely one decision. A system may need to hide a record from normal use, prove who changed it, restore it later, retain it for a legal period or remove it permanently. Soft deletes, audit trails and history tables solve different parts of that problem. Treating them as the same feature creates confusing data and weak compliance controls.

Soft deletes hide current records

A soft delete marks a row as deleted instead of removing it. The common shape is a nullable timestamp such as deleted_at, sometimes with deleted_by and delete_reason.

ALTER TABLE users
ADD COLUMN deleted_at timestamp NULL;

Normal application queries then filter out deleted rows.

SELECT *
FROM users
WHERE deleted_at IS NULL;

Soft deletes are useful when users need undo, when related records still refer to the row or when deletion should be reviewed before purge. They are not a complete audit trail because they usually store only the latest deletion state, not every change that happened before it.

Soft deletes have costs

Every query must handle the deletion predicate correctly. Missing deleted_at IS NULL can expose hidden data. Adding it everywhere also makes indexes more important, because active rows are now a subset of the table.

A partial index can help in engines that support it. PostgreSQL, for example, supports partial indexes with an arbitrary WHERE predicate so long as it only references columns of the table being indexed.

CREATE INDEX users_active_email_idx
ON users (email)
WHERE deleted_at IS NULL;

Uniqueness needs deliberate design. If a deleted row keeps its email address, should a new active row be allowed to reuse it? The answer affects unique indexes, restoration behaviour and user support tools.

Soft-deleted data also still exists. It may still be included in backups, analytics exports, search indexes and replicas. Do not present soft delete as permanent erasure.

Audit trails record events

An audit trail records who did what, when, and often why. It is event-shaped rather than state-shaped. A useful audit event includes the actor, action, target, timestamp, request or correlation id, and enough before and after data to explain the change.

Audit trails should be append-only from the application point of view. The system should not update old audit events during normal business flow.

CREATE TABLE audit_events (
  id bigint generated always as identity primary key,
  actor_id bigint NULL,
  action text NOT NULL,
  target_table text NOT NULL,
  target_id text NOT NULL,
  occurred_at timestamp NOT NULL,
  before_data jsonb NULL,
  after_data jsonb NULL
);

Do not rely only on application logs for audit. Logs are often sampled, rotated, redacted or stored for operations rather than evidence. A database audit table or dedicated audit system makes retention and querying explicit.

History tables record versions

A history table stores previous versions of a row. It answers questions such as what this record looked like last Tuesday, or which values changed over time.

Some databases provide system-versioned temporal tables. In SQL Server, a system-versioned temporal table keeps current rows in one table while the Database Engine automatically moves each previous row version into a separate history table on update or delete. These are useful for point-in-time analysis, but they still require retention planning because history can grow quickly.

Manual history tables can work well when the required versioning semantics are simple. They should include validity columns, a stable entity id, the changed data and metadata about the change.

Choose the right mechanism

Use soft deletes when the main requirement is to remove a row from normal application behaviour without immediately purging it.

Use audit trails when the main requirement is accountability. The audit record should explain the action, actor and context even if the current row later changes again.

Use history tables when the main requirement is point-in-time reconstruction or comparison between versions.

Many systems need more than one mechanism. For example, a user deletion can set deleted_at, write an audit event and preserve row versions for a defined retention period.

Plan retention and purge

Retention is part of the design, not a cleanup task to add later. Decide how long soft-deleted rows, audit events and history versions must be kept. Decide who can purge them and how purge is verified.

A purge job should be explicit, batched and observable. It should not accidentally remove records that are still needed for support, billing, legal hold or referential integrity.

For privacy-sensitive data, consider whether fields should be redacted while the audit event remains. Sometimes the system needs to keep proof that an action occurred without keeping the full personal data payload forever.

Protect integrity

Soft deletes can break assumptions in foreign keys and unique constraints. Decide whether child records are also soft-deleted, prevented from referencing deleted parents or allowed to keep historical references.

Audit and history tables should be protected from casual edits. Restrict write permissions, monitor changes and include enough metadata to trace the source of each event.

Conclusion

Soft deletes, audit trails and history tables are related but not interchangeable. Soft deletes control visibility, audit trails prove actions, and history tables preserve versions. A robust design names the requirement first, then chooses the mechanism, indexes the active path and defines retention before the table grows without limit.