Soft deletes, hard deletes and the audit trail in between

Written By: BrainSoft In Backend

A user clicks Delete on an invoice. The row disappears from the list. Three weeks later finance asks why the invoice number is missing from a report, and support asks what the customer actually removed. If your delete was a plain DELETE FROM invoices, the answer is gone with the row. That is the moment most teams start caring about how deletions work.

The short version: hard delete when the data is genuinely disposable and you have no legal or reporting reason to keep it, soft delete when you need the row back or need to hide it without losing it, and write an append-only audit table when you need to know who changed what and when. Most production systems end up using all three, for different tables.

What a soft delete actually costs you

A soft delete is a timestamp column and a filter you have to remember everywhere. The schema is small:

ALTER TABLE invoices ADD COLUMN deleted_at timestamptz;
CREATE INDEX invoices_active_idx ON invoices (tenant_id) WHERE deleted_at IS NULL;

The partial index matters. Without it, every list query scans deleted rows too, and the table grows forever. The real cost is discipline: every query needs WHERE deleted_at IS NULL, and the one you forget is the one that shows a cancelled order to a customer.

• Unique constraints break. A unique index on email now blocks re-registration after a soft delete. Fix it with a partial unique index on active rows only.
• Foreign keys still point at deleted parents. Decide whether a deleted invoice should block deleting its line items.
• Restores are not free. If the customer changed their email in the meantime, restoring the old row can violate a constraint.
• Storage keeps growing. A retention job that hard-deletes rows older than the policy window is not optional.

I have seen teams add deleted_at and then treat it as a display flag, leaving the row fully visible to joins, exports and reports. That is worse than a hard delete, because now nobody can tell what the state of the data is.

When a hard delete is the right call

Not every table deserves a tombstone. Session tokens, expired cache rows, one-time verification codes, and anything under a data retention policy that says you must not keep it. Keeping personal data you promised to erase is a compliance problem, not a safety net.

For those tables, delete for real and let the database do its job:

DELETE FROM sessions WHERE expires_at < now() - interval '30 days';

Two things make hard deletes safer. First, cascade rules you have actually read: ON DELETE CASCADE can quietly remove a lot of rows, so check what depends on the table before you rely on it. Second, a backup you can restore from. A delete without a tested restore path is a one-way door.

If the data is business-critical but you still want it gone from the main table, the middle ground is moving it to an archive table in the same transaction, then deleting the original. Same effect, one less restore.

The audit trail is a separate table, not a column

A deleted_at column tells you when. It does not tell you who, from where, or what the row looked like before the change. If any of that matters, write an append-only audit table. It should never be updated or deleted from, and the application user should only have INSERT rights on it.

CREATE TABLE audit_log (
  id          bigserial PRIMARY KEY,
  entity      text NOT NULL,
  entity_id   bigint NOT NULL,
  action      text NOT NULL CHECK (action IN ('insert','update','delete')),
  actor_id    bigint,
  changed_at  timestamptz NOT NULL DEFAULT now(),
  before      jsonb,
  after       jsonb
);
CREATE INDEX audit_log_entity_idx ON audit_log (entity, entity_id, changed_at DESC);

Storing full row snapshots as JSONB is blunt but reliable. You can reconstruct any version of a record, and you do not have to migrate the audit table every time the source table gains a column. The trade-off is size, so put a retention window on it and be explicit about what you keep.

Write the audit row in the same transaction as the change. If you do it asynchronously through a queue, you will eventually have changes with no audit entry, and the audit log becomes a hint rather than a record. That is a design decision worth making on purpose, not by accident.

Where the audit trail touches compliance and access control, it is worth having someone review the design. That is the kind of thing we do in our services work, usually alongside the schema review.

Picking per table, not per project

The useful rule is to decide table by table, and write the decision down. For each table ask: does a user ever need this back, does anyone need to see history, and is there a rule that says we must not keep it? Three yes or no answers, and the choice usually makes itself.

• Reference data and configuration: soft delete plus audit. Changes are rare and always worth explaining.
• Financial and order records: never hard delete. Soft delete, audit table, and a retention policy that a lawyer signed off on.
• Tokens, sessions, ephemeral rows: hard delete on a schedule.
• User-generated content: soft delete, so moderation and appeals have something to look at.

Whatever you choose, make the default path in your data layer enforce it. A repository method called delete() that silently does a soft delete will confuse the next person. Name it softDelete() or archive(), and keep a separate, deliberately awkward method for the real thing.

Frequently asked questions

#Backend