/DB

Soft deletes

Keep deleted rows for audit and recovery by marking them with a timestamp instead of removing them, then filter them out of every read.

updated 5 Jun 20261 min readv0.3.2View as Markdown

A soft delete marks a row as removed instead of issuing DELETE, which preserves history and makes recovery trivial. The pattern is a nullable timestamp column plus a predicate on every read.

Add the column

[Table("tasks")]
public partial class TaskItem
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public required string Title { get; set; }

    public DateTime? DeletedAt { get; set; }   // null = live, set = soft-deleted
}

A nullable property maps to a nullable column, so existing rows default to NULL (live).

Delete by stamping, read by filtering

"Deleting" is an update that sets the timestamp:

public Task SoftDeleteAsync(Guid id) =>
    db.ExecuteAsync(async d =>
    {
        var task = await d.Tasks.FirstOrDefaultAsync(t => t.Id == id);
        if (task is null) return;
        task.DeletedAt = DateTime.UtcNow;
        await d.Tasks.UpdateAsync(task);
    });

Every read filters out the deleted rows:

public Task<List<TaskItem>> LiveTasksAsync(Guid projectId) =>
    db.ExecuteAsync(d => d.Tasks.ToListAsync(t => t.ProjectId == projectId && t.DeletedAt == null));

Restoring a row is the same update with DeletedAt = null.

TIP
Centralize the DeletedAt == null predicate in a repository or helper so no query forgets it. A partial index, CREATE INDEX ON tasks (project_id) WHERE deleted_at IS NULL, keeps live-row lookups fast while the dead rows stay out of the index.

To purge old soft-deleted rows for good, run a real delete on a schedule:

public Task<int> PurgeAsync(DateTime olderThan) =>
    db.ExecuteAsync(d => d.Tasks.DeleteAsync(t => t.DeletedAt != null && t.DeletedAt < olderThan));