Implementing Soft Deletes

Soft deletes allow you to "delete" records without actually removing them from the database. Instead, records are marked as deleted and filtered out of normal queries. This enables data recovery, audit trails, and undo functionality.

Schema Design

Add a nullable timestamp column to track when records were deleted:

db.execute ("CREATE TABLE documents (id INTEGER PRIMARY KEY, name TEXT NOT NULL, content TEXT, deleted_at TEXT DEFAULT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP)")

Soft Delete a Record

Instead of DELETE, UPDATE the deleted_at timestamp:

-- Soft delete a document
db.update ("documents")
    .set ("deleted_at", datetime.formatted_out ("yyyy-mm-dd hh:mi:ss"))
    .where ("id = ?", doc_id)
    .execute

-- Or use SQLite's built-in timestamp
db.execute_with_args ("UPDATE documents SET deleted_at = datetime('now') WHERE id = ?", <<doc_id>>)

Query Active Records Only

Use the active_only scope to automatically filter out deleted records:

-- Only returns records where deleted_at IS NULL
result := db.select_from ("documents")
    .columns ("id, name, created_at")
    .active_only
    .order_by ("name")
    .execute

across result as doc loop
    print (doc.string_value ("name") + "%N")
end

Query Deleted Records (Trash View)

Use deleted_only to show only soft-deleted records:

-- Show items in trash
result := db.select_from ("documents")
    .columns ("id, name, deleted_at")
    .deleted_only
    .order_by ("deleted_at DESC")  -- Most recently deleted first
    .execute

print ("Trash (%N items):%N")
across result as doc loop
    print ("  - " + doc.string_value ("name") + " (deleted " + doc.string_value ("deleted_at") + ")%N")
end

Query All Records (Admin View)

Use with_deleted to include both active and deleted records:

-- Admin view: show everything
result := db.select_from ("documents")
    .columns ("id, name, deleted_at")
    .with_deleted
    .execute

across result as doc loop
    if attached doc.string_value_or_void ("deleted_at") as deleted then
        print (doc.string_value ("name") + " [DELETED]%N")
    else
        print (doc.string_value ("name") + "%N")
    end
end

Restore a Soft-Deleted Record

Simply set deleted_at back to NULL:

-- Restore from trash
db.update ("documents")
    .set_null ("deleted_at")
    .where ("id = ?", doc_id)
    .execute

Permanently Delete (Hard Delete)

When you really need to remove data (GDPR, storage cleanup, etc.):

-- Hard delete items in trash older than 30 days
db.delete_from ("documents")
    .where ("deleted_at IS NOT NULL AND deleted_at < datetime('now', '-30 days')")
    .execute

print ("Purged " + db.changes.out + " old items from trash%N")

Custom Column Name

If your schema uses a different column name:

-- Table uses 'archived_at' instead of 'deleted_at'
result := db.select_from ("posts")
    .set_soft_delete_column ("archived_at")
    .active_only  -- Now checks archived_at IS NULL
    .execute

Combining with Other Filters

Soft delete scopes combine with other WHERE conditions:

-- Active documents in a specific folder
result := db.select_from ("documents")
    .active_only
    .where ("folder_id = ?", folder_id)
    .order_by ("name")
    .execute

-- Recently deleted documents by user
result := db.select_from ("documents")
    .deleted_only
    .where ("created_by = ?", user_id)
    .where ("deleted_at > datetime('now', '-7 days')")
    .execute

Best Practices

Complete Example: Document Manager

class DOCUMENT_MANAGER

feature -- Access

    all_documents: SIMPLE_SQL_RESULT
            -- All active documents
        do
            Result := db.select_from ("documents").active_only.order_by ("name").execute
        end

    trash: SIMPLE_SQL_RESULT
            -- Documents in trash
        do
            Result := db.select_from ("documents").deleted_only.order_by ("deleted_at DESC").execute
        end

feature -- Operations

    delete (a_id: INTEGER_64)
            -- Soft delete a document
        do
            db.execute_with_args ("UPDATE documents SET deleted_at = datetime('now') WHERE id = ?", <<a_id>>)
        end

    restore (a_id: INTEGER_64)
            -- Restore from trash
        do
            db.update ("documents").set_null ("deleted_at").where ("id = ?", a_id).execute
        end

    empty_trash
            -- Permanently delete all trashed documents
        do
            db.delete_from ("documents").where ("deleted_at IS NOT NULL").execute
        end

    purge_old_trash (a_days: INTEGER)
            -- Delete items in trash older than `a_days`
        do
            db.execute_with_args ("DELETE FROM documents WHERE deleted_at IS NOT NULL AND deleted_at < datetime('now', '-' || ? || ' days')", <<a_days>>)
        end

end