Soft Deletes and Audit Trails in Go APIs Compared Fairly

"Delete" is the most overloaded verb in an API. A user clicks a button, and someone needs the row gone, someone needs it back, someone needs to know who did it, and someone in legal needs it truly erased after thirty days. These are four different requirements, and the usual answer to all of them, adding a deleted_at column, solves only the second.

This article separates the needs, compares the two common mechanisms, soft deletes and audit trails, and runs both against a real database to show the places they bite. The lab uses SQLite, and the design carries over to PostgreSQL and MySQL. It follows on from CRUD with a database and links to multi-tenant designs where deletion and tenant offboarding meet. It ran on Go 1.25.6.

What the delete is for

Before picking a mechanism, ask which of these the product needs:

1. Undo. The user (or support) can bring something back.
2. History. Someone can see what a record looked like last month, and who changed it.
3. Accountability. An auditor can ask "who deleted this invoice?" and get an answer.
4. Erasure. Personal data actually leaves the system after a promised period.
5. Referential safety. Rows that point at the deleted row, such as orders that reference a product, must keep working.

Soft deletes serve 1 and 5 directly, and 2 and 3 partly. An audit log serves 2 and 3 well and 1 awkwardly. Neither serves 4 on its own, and one of them can make it harder. Name the requirement first, because most teams that add deleted_at by reflex wanted only the third.

Soft deletes

A soft delete sets a marker, usually deleted_at TIMESTAMP NULL, instead of removing the row. Queries ignore rows where it is set.

What you get: undo is one UPDATE, history of the existence of a row is preserved, and foreign keys from other tables keep pointing at something. What it costs, in roughly the order teams discover it:

Every query needs the filter. Each SELECT, join and count must say deleted_at IS NULL. Forget it once and deleted data reappears in a list, an export, or worse, another user's view. Libraries help: GORM's gorm.DeletedAt type adds the filter to queries automatically, and ent supports it through mixins and hooks. With database/sql and sqlc you write it into each query, or create a view of live rows and query that.

Uniqueness stops meaning what it says. This is the one the lab demonstrates. A column that must be unique, such as an email, cannot carry a plain UNIQUE constraint, or a deleted user's address is blocked forever. The fix is a partial unique index that covers live rows only. PostgreSQL and SQLite support CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL. MySQL does not have partial indexes, so people use a generated column or include deleted_at in a composite unique index, with its own caveat that NULLs do not collide in unique indexes.

Restore can conflict. If the email was reused after the delete, restoring the old row would create a duplicate.

Tables grow forever. Dead rows stay in indexes and in backups. Query plans slow down as the proportion of dead rows rises, unless the index is partial.

Cascades are manual. Deleting a project should hide its tasks. A database ON DELETE CASCADE does nothing for a soft delete, so your code has to walk the children, in a transaction.

It conflicts with erasure. A soft-deleted user is still personal data. Privacy rules that promise deletion are not satisfied by a flag.

Audit trails

An audit trail is an append-only table of events: who did what, to which row, when, and optionally what it looked like before and after. It answers history and accountability without touching the live tables. A delete is a real delete plus one audit row.

What you get: live tables stay small and their constraints stay honest, you can reconstruct an object's history, and the same table covers updates, logins, permission changes and exports. What it costs: undo means rebuilding a row from a before snapshot, which is possible but a feature you have to write, and the log itself needs retention, access control and care about what it stores (personal data in a log is still personal data).

Running both

The lab implements a users table with a soft delete, a partial unique index, and an audit table that is written in the same transaction as each change.

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	_ "modernc.org/sqlite"
)

type Store struct {
	db  *sql.DB
	now func() time.Time
}

type Actor struct{ ID, IP string }

func (s *Store) audit(ctx context.Context, tx *sql.Tx, actor Actor, action, table string, id int64, before, after any) error {
	b, _ := json.Marshal(before)
	a, _ := json.Marshal(after)
	_, err := tx.ExecContext(ctx, `INSERT INTO audit_log (at, actor, ip, action, table_name, row_id, before, after) VALUES (?,?,?,?,?,?,?,?)`,
		s.now().Format(time.RFC3339), actor.ID, actor.IP, action, table, id, string(b), string(a))
	return err
}

type User struct {
	ID    int64  `json:"id"`
	Email string `json:"email"`
	Name  string `json:"name"`
}

// The audit row and the change commit together or not at all.
func (s *Store) CreateUser(ctx context.Context, actor Actor, email, name string) (int64, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()
	res, err := tx.ExecContext(ctx, `INSERT INTO users (email, name) VALUES (?, ?)`, email, name)
	if err != nil {
		return 0, err
	}
	id, _ := res.LastInsertId()
	if err := s.audit(ctx, tx, actor, "create", "users", id, nil, User{id, email, name}); err != nil {
		return 0, err
	}
	return id, tx.Commit()
}

func (s *Store) SoftDelete(ctx context.Context, actor Actor, id int64) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var u User
	if err := tx.QueryRowContext(ctx, `SELECT id, email, name FROM users WHERE id = ? AND deleted_at IS NULL`, id).Scan(&u.ID, &u.Email, &u.Name); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `UPDATE users SET deleted_at = ? WHERE id = ?`, s.now().Format(time.RFC3339), id); err != nil {
		return err
	}
	if err := s.audit(ctx, tx, actor, "delete", "users", id, u, nil); err != nil {
		return err
	}
	return tx.Commit()
}

func (s *Store) Restore(ctx context.Context, id int64) error {
	_, err := s.db.ExecContext(ctx, `UPDATE users SET deleted_at = NULL WHERE id = ?`, id)
	return err
}

func (s *Store) Count(ctx context.Context, where string) int {
	var n int
	s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users `+where).Scan(&n)
	return n
}

// Purge really removes rows that were soft-deleted before the cutoff.
func (s *Store) Purge(ctx context.Context, cutoff time.Time) (int64, error) {
	res, err := s.db.ExecContext(ctx, `DELETE FROM users WHERE deleted_at IS NOT NULL AND deleted_at < ?`, cutoff.Format(time.RFC3339))
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("sqlite", "file:audit?mode=memory&cache=shared")
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	clock := time.Date(2026, 3, 1, 9, 0, 0, 0, time.UTC)
	s := &Store{db: db, now: func() time.Time { clock = clock.Add(time.Minute); return clock }}
	for _, q := range []string{
		`CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL, name TEXT NOT NULL, deleted_at TEXT)`,
		// A plain UNIQUE(email) would make a deleted user's address unusable forever; the partial index only covers live rows.
		`CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL`,
		`CREATE TABLE audit_log (id INTEGER PRIMARY KEY, at TEXT, actor TEXT, ip TEXT, action TEXT, table_name TEXT, row_id INTEGER, before TEXT, after TEXT)`,
	} {
		if _, err := db.Exec(q); err != nil {
			panic(err)
		}
	}

	admin := Actor{ID: "admin-7", IP: "203.0.113.9"}
	id, _ := s.CreateUser(ctx, admin, "[email protected]", "Ada")
	_, err = s.CreateUser(ctx, admin, "[email protected]", "Ada Again")
	fmt.Println("duplicate live email:       ", err != nil)

	fmt.Println("delete user", id, "->", s.SoftDelete(ctx, admin, id))
	fmt.Println("live / all rows:            ", s.Count(ctx, "WHERE deleted_at IS NULL"), "/", s.Count(ctx, ""))

	id2, err := s.CreateUser(ctx, admin, "[email protected]", "Ada Reborn")
	fmt.Println("same email after delete:    ", id2, err)

	fmt.Println("restore the first user ->    ", s.Restore(ctx, id))
	fmt.Println("live rows with that email:  ", s.Count(ctx, "WHERE email = '[email protected]' AND deleted_at IS NULL"))

	n, _ := s.Purge(ctx, clock.Add(time.Hour))
	fmt.Println("purged:", n)

	rows, _ := db.Query(`SELECT at, actor, action, row_id, before, after FROM audit_log ORDER BY id`)
	defer rows.Close()
	for rows.Next() {
		var at, actor, action, before, after string
		var row int64
		rows.Scan(&at, &actor, &action, &row, &before, &after)
		fmt.Printf("audit %s %s %-6s row=%d before=%s after=%s\n", at[11:16], actor, action, row, before, after)
	}
}

duplicate live email:        true
delete user 1 -> <nil>
live / all rows:             0 / 1
same email after delete:     2 <nil>
restore the first user ->     constraint failed: UNIQUE constraint failed: users.email (2067)
live rows with that email:   1
purged: 1
audit 09:01 admin-7 create row=1 before=null after={"id":1,"email":"[email protected]","name":"Ada"}
audit 09:03 admin-7 delete row=1 before={"id":1,"email":"[email protected]","name":"Ada"} after=null
audit 09:04 admin-7 create row=2 before=null after={"id":2,"email":"[email protected]","name":"Ada Reborn"}

The schema is at the end of main. Notice the unique index is declared with a WHERE clause, so it covers live rows only.

Reading the output

The constraint holds for live rows. The second CreateUser with the same email failed, as it should.

Delete and count. After SoftDelete, the live count is 0 and the total is 1. The row is still there, hidden by a filter you have to remember.

The email can be reused. Creating a user with the same address succeeded and got id 2. Without the partial index this would have been a constraint violation, and support would be writing an apology.

Restore conflicts. Restoring user 1 failed with UNIQUE constraint failed: users.email, and only one live row has that address. The database protected you here, but notice what the application has to decide: restore with a new email? Refuse and explain? Merge into the new account? There is no neutral answer, and "undo" in the product spec probably did not mention it.

Purge. Rows soft-deleted before the cutoff were really removed, 1 row. Without this job a soft delete is a retention policy of forever.

The audit trail. Three rows: a create, a delete and a second create, each with the actor, a timestamp, and before and after JSON. The delete row holds the full prior state, which is enough to rebuild the user. The failed duplicate create and the failed restore left no audit row, because they rolled back or never committed. That is correct: the log describes what happened to the data, not what was attempted. If you also want attempts, write a separate security log.

The audit write belongs in the transaction

The most important detail in the Go code is s.audit(ctx, tx, ...): the audit insert uses the same *sql.Tx as the change. If it were a separate call after the commit, a crash between the two would produce a change with no history, which is the exact failure an audit trail exists to prevent. If it were before the commit but on a different connection, a rollback of the change would leave a log entry for something that never happened.

Two other ways to get the same guarantee are worth knowing. Database triggers write the audit row from inside the database, so even a direct SQL session is covered, at the cost of not knowing the application user unless you pass it in a session variable. Change data capture from the write-ahead log streams every row change to another system, with no application code, and suits large systems that already run the infrastructure.

What to put in an audit row

• Actor: the authenticated user or service, not just an IP. Include the IP and user agent as extra context.
• Action and subject: delete on users row 1. A stable vocabulary beats free text.
• When: use UTC, from the database or one injected clock, as in the lab.
• Before and after: only for the fields that matter. Snapshots of whole rows are simple and take space. Diffs are small and harder to read.
• Request id: ties the row to the log lines and traces of the call. See request IDs and logging.
• Reason, when your product asks users for one.

Do not log secrets, tokens, password hashes or full card numbers, and treat the log as sensitive: restrict who can read it, and apply the same retention rules as the data it describes. Make the table append-only at the permission level. The application role gets INSERT and SELECT, no UPDATE or DELETE, and purging runs under a separate role. For stronger tamper evidence, chain rows by storing a hash of the previous row in each new one, and ship copies to storage the application cannot write.

Making the filter hard to forget in Go

If you go with soft deletes, spend effort so the filter cannot be forgotten, because a missing deleted_at IS NULL is the bug you will actually ship.

Views for live rows. Create CREATE VIEW live_users AS SELECT * FROM users WHERE deleted_at IS NULL and have every read query use the view. The write paths use the table. A reviewer then has one thing to check: reads name the view. This works with plain database/sql, with sqlc and with migrations you already own.

A repository that owns the table. Keep every query for users in one package, so the filter appears in a handful of functions. This is the same advice as in the multi-tenant article for the tenant column, and the two filters travel together: WHERE tenant_id = ? AND deleted_at IS NULL.

Tests that count. A table-driven test that creates a user, deletes it, and calls every read function (get, list, search, count, export) asserting nothing comes back. Add a function to the repository and the test must include it, so keep the list of read methods in the test and check it against the methods of the type with reflect if you want a reminder when someone adds one.

Library support, with care. GORM's gorm.DeletedAt turns Delete into an update and adds the filter to its query builder, but raw SQL and Unscoped() bypass it, which is the escape hatch you must grep for. Whatever you use, know which calls it does not cover.

Time zones and clocks. Store deleted_at in UTC and compare against a cutoff built from an injected clock. The lab uses a fake clock that advances a minute per call, which is why the audit rows show sensible times without sleeping.

Recommendations

Use an audit trail by default for anything that matters to a customer or an auditor: accounts, money, permissions, content.

Use soft delete when undo is a feature. A trash bin the user can empty, a "restore within 30 days" promise. Then design for it: the partial unique indexes, the cascade rules, the restore conflict policy, and the purge job with a retention period you can state.

Prefer a status over a flag when there are more than two states. active, archived, pending_deletion says more than a nullable timestamp, and the transitions can be validated.

Keep deleted data out of the way. If you need long-lived history of deleted rows, move them to an archive table in the same transaction as the delete. The live table stays clean, the archive can have different indexes and retention, and nothing needs a filter.

Decide the cascade story early. What happens to the order lines of a deleted product, the comments on a deleted post, the sessions of a deleted user. Write it down per relationship.

Make erasure a separate, tested operation. A job that finds every place a person's data lives (tables, object storage, caches, search, backups with a documented expiry) and removes it. Test it with a real example.

In the API

The endpoint side is short. DELETE /v1/users/{id} returns 204 on success and the same 204 (or a 404, pick one) on a repeated call, as discussed in idempotency. Restoring is POST /v1/users/{id}:restore or an update of the status, and returns a 409 with a problem document if the conflict from the lab occurs. Listing endpoints exclude deleted rows by default, with an explicit ?include_deleted=true that only privileged callers can use. Remember that a deleted resource's URL should return 404 or 410 consistently, not leak whether it ever existed to unauthorised callers. The database/sql documentation covers the transaction handling the audit pattern depends on, and the PostgreSQL manual on partial indexes explains the index used above.