Soft Deletes and Audit Logs: Keeping History Without Making a Mess

A user deletes a project by mistake and emails support. An admin changes a customer's plan and nobody knows who. An AI agent "cleans up old records" more broadly than intended. Each of these is easier to survive if your app keeps history. The two main tools are soft deletes (hide instead of remove) and audit logs (record who changed what). Both are easy to add badly.

Soft deletes

Instead of DELETE, mark the row:

ALTER TABLE projects ADD COLUMN deleted_at timestamptz;

UPDATE projects SET deleted_at = now() WHERE id = $1;   -- "delete"
UPDATE projects SET deleted_at = NULL  WHERE id = $1;   -- restore

The costs people underestimate

Every query must filter. Miss WHERE deleted_at IS NULL in one list endpoint, report, search index, or export, and deleted data reappears. This is the most common soft-delete bug.
Unique constraints break. A user deletes the project acme, then can't create a new acme because the hidden row still holds the name.
Foreign keys don't cascade. ON DELETE CASCADE never fires on an UPDATE; child rows of a soft-deleted parent stay "live."
Tables grow forever unless you purge.
Privacy law may require actually deleting personal data (below).

Implementing it well

Partial unique indexes — uniqueness only among live rows:

CREATE UNIQUE INDEX projects_slug_live_uniq
  ON projects (workspace_id, slug)
  WHERE deleted_at IS NULL;

Partial indexes for hot queries — so the dead rows don't bloat the index you query most:

CREATE INDEX projects_workspace_live_idx
  ON projects (workspace_id, created_at DESC)
  WHERE deleted_at IS NULL;

Make the filter the default, not a habit:

A view of live rows that the app queries (CREATE VIEW live_projects AS SELECT * FROM projects WHERE deleted_at IS NULL).
Row-level security policies that exclude deleted rows for the app role. (Multi-Tenant Postgres Patterns.)
ORM-level global filters or extensions (Prisma client extensions, Django custom managers, Rails default_scope-style gems) — convenient, but raw SQL bypasses them.

Decide cascade behaviour explicitly — soft-delete children in the same transaction, or treat them as hidden through the parent.

Purge on a schedule — hard-delete rows soft-deleted more than N days ago (30 is common). That gives an "undo" window without keeping data forever. (Your App Needs Background Jobs.)

Alternatives

Archive table — move the row to projects_archive (in a transaction) on delete. Live tables stay clean; restores are explicit.
Status column — when "deleted" is really a business state (archived, cancelled), model the state properly instead.
Just delete, and rely on backups plus an audit log for rare recoveries. Often right for low-value data. (Backups for Beginners.)

The privacy tension

A soft-deleted row is not deleted for data-protection purposes. If a user exercises a right to erasure, you generally need to remove or irreversibly anonymise their personal data — in live tables, soft-deleted rows, search indexes, caches, and (on a reasonable schedule) backups and logs. Design the purge path at the same time as the soft delete. (GDPR Basics for App Builders.)

Audit logs

An audit log answers: who did what, to which record, when, and what changed?

CREATE TABLE audit_log (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  actor_id    bigint,            -- user, admin, or service account
  actor_type  text NOT NULL,     -- 'user' | 'admin' | 'system' | 'agent'
  action      text NOT NULL,     -- 'project.deleted', 'plan.changed'
  entity_type text NOT NULL,
  entity_id   text NOT NULL,
  changes     jsonb,             -- {"plan": ["pro", "team"]}
  request_id  text,
  ip          inet
);
CREATE INDEX audit_entity_idx ON audit_log (entity_type, entity_id, occurred_at DESC);

Two ways to populate it

Application-level events — write an audit row in the same transaction as the change:

await db.transaction(async (tx) => {
  await tx.update(projects).set({ deletedAt: new Date() }).where(eq(projects.id, id))
  await tx.insert(auditLog).values({
    actorId: user.id, actorType: "user", action: "project.deleted",
    entityType: "project", entityId: String(id), requestId,
  })
})

Rich context (who, why, which request) and meaningful action names. Risk: code paths that forget to log.

Database triggers — capture every INSERT/UPDATE/DELETE on audited tables, whatever code ran it:

CREATE FUNCTION audit_trigger() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (actor_id, actor_type, action, entity_type, entity_id, changes)
  VALUES (
    nullif(current_setting('app.user_id', true), '')::bigint,
    coalesce(nullif(current_setting('app.actor_type', true), ''), 'system'),
    lower(TG_OP), TG_TABLE_NAME,
    coalesce(to_jsonb(NEW), to_jsonb(OLD))->>'id',
    jsonb_build_object('old', to_jsonb(OLD), 'new', to_jsonb(NEW))
  );
  RETURN NULL;  -- AFTER trigger: return value is ignored
END $$ LANGUAGE plpgsql;

CREATE TRIGGER projects_audit
  AFTER INSERT OR UPDATE OR DELETE ON projects
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Complete coverage, including manual SQL and scripts. The app sets app.user_id per transaction (SET LOCAL app.user_id = '42') to attribute changes — with a transaction-mode connection pooler, SET LOCAL inside the transaction is the safe form. Storing full OLD/NEW rows is heavy; store diffs or selected columns for large tables.

Many teams use both: triggers as the safety net, application events for business-meaningful actions.

Audit log hygiene

Append-only: the app role gets INSERT but not UPDATE/DELETE on audit_log.
Don't log secrets or unnecessary personal data in changes; redact sensitive columns. (Structured Logging.)
Retention: partition by month and drop old partitions per your policy.
Log AI agent actions distinctly (actor_type = 'agent') — when an agent changes data, you want to know it was the agent.

EasySpawn gives each project a managed Postgres with daily backups, and on-demand backups on the Team plan, so soft deletes and audit logs sit on top of a real recovery path — not instead of one. See pricing or join the waitlist.