How 2 Soft Delete.

In modern systems, data restoration is a real demand, whether due to user error, auditing, or support. The goal of this article is to present the concept of Soft Delete, along with its main strategies in an interactive way, so you can test and choose the best approach for your next project.

The Problem

Imagine a common scenario where a user deletes an important record by mistake. It could be an admin who clicked the wrong button, a client removing data that later becomes critical, or someone cleaning up what they thought was temporary. When there is no strategy for retaining or recovering this data, that simple mistake turns into a real crisis, where you have to stop everything, restore backups in an isolated environment, locate records manually, and reinsert whatever is possible, with the risk of losing everything created after the last database snapshot.

This scenario is a perfect example of Hard Delete in critical tables. Hard Delete is the physical removal of the record, usually done with a direct DELETE command on the table. In short, the data is truly removed from the database and disk. There is no immediate rollback, no audit trail by default, and without external backup, there is no way to recover it.

Here is an example of Hard Delete:

This approach is dangerous for critical tables, but it is the ideal (and native) one for secondary data, temporary records, or quick registrations that do not impact the business.

The Solution

Soft Delete treats deletion as a logical state, not a physical one. For the user, the data is gone, but for the database, it was only marked as hidden. There are two main ways to implement this, which is what we call Soft Delete Patterns.

Logical Delete

The first Soft Delete Pattern is Logical Delete, with the simplest and most common approach. It consists of adding a column in the table itself to signal the record state.

Follow the steps below to understand a Logical Delete flow

As we can see, instead of physically removing the record, we only update the 'archived_at' column. This way, we can distinguish between active records and those that have been archived or deleted.

To implement Logical Delete, just add a new column to your table:

ALTER TABLE tasks
ADD COLUMN archived_at TIMESTAMPTZ DEFAULT NULL;

This field can be a boolean or a timestamp. The important thing is that it clearly indicates whether the record is active or deleted.

The problem appears at scale. Millions of deleted records keep occupying space and dirtying indexes in the main table.

Here is a common challenge of Logical Delete:

99% of the time you only care about active records, yet you're forced to carry the weight of millions of dead rows and remember to filter them in every single query.

Shadow Table

The second Soft Delete Pattern is Shadow Table, which is a more complex solution to solve main table bloat. Here, instead of marking the record, we move it to a secondary archive table.

Follow the steps below to understand the Shadow Table flow

As we can see, when a record is deleted from the 'tasks' table, it is automatically moved to the 'archives' table via a database trigger.

To implement Shadow Table, complexity increases. First, we must create the 'archives' table in our database:

CREATE TABLE archives (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
table_name TEXT NOT NULL,
record_id JSONB NOT NULL,
data JSONB NOT NULL,
caused_by_table TEXT NOT NULL,
caused_by_id JSONB NOT NULL,
archived_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Now we must create the function that will handle the archiving logic:

CREATE OR REPLACE FUNCTION fn_shadow_archive()
RETURNS TRIGGER AS $$
DECLARE
v_cause_table TEXT;
v_cause_id_text TEXT;
BEGIN
v_cause_table := current_setting('app.current_deleter_table', true);
v_cause_id_text := current_setting('app.current_deleter_id', true);

IF (v_cause_table IS NULL OR v_cause_table = '') THEN
v_cause_table := TG_TABLE_NAME;
v_cause_id_text := OLD.id::text;

PERFORM set_config('app.current_deleter_table', v_cause_table, true);
PERFORM set_config('app.current_deleter_id', v_cause_id_text, true);
END IF;

INSERT INTO archives (
table_name,
record_id,
data,
caused_by_table,
caused_by_id
)
VALUES (
TG_TABLE_NAME,
jsonb_build_object('id', OLD.id),
to_jsonb(OLD),
v_cause_table,
jsonb_build_object('id', v_cause_id_text)
);

RETURN OLD;
END;
$$ LANGUAGE plpgsql;

This function is responsible for taking the deleted record and inserting it into the 'archives' table.

Now we just need to create triggers for critical tables. This way, they are protected against Hard Delete. In the example below, the Shadow Table pattern is applied to three tables: users, establishments, and appointments.

CREATE TRIGGER trg_archive_users BEFORE DELETE ON users FOR EACH ROW EXECUTE FUNCTION fn_shadow_archive();
CREATE TRIGGER trg_archive_establishments BEFORE DELETE ON establishments FOR EACH ROW EXECUTE FUNCTION fn_shadow_archive();
CREATE TRIGGER trg_archive_appointments BEFORE DELETE ON appointments FOR EACH ROW EXECUTE FUNCTION fn_shadow_archive();

Nothing is free. While Logical Delete requires only an UPDATE, restoring data in a Shadow Table requires more complex INSERT INTO and SELECT queries, especially when foreign key relationships are involved.

In the example below, we have 3 examples of situations where you must recover deleted data.

The trade-off is direct: recovering data here is not a simple UPDATE. It usually involves gathering context, rebuilding dependencies, and executing a sequence of queries with extra care.

The 3 SELECTs make it clear why modeling record_id and caused_by_id as JSONB is worth it. It creates room for composite key records without forcing the archives schema to change for every new case. The caused_by_table and caused_by_id columns also greatly improve cascade delete traceability, because they make the event source explicit.

Conclusion: Which one to choose?

In practice, the best deletion strategy depends heavily on the product stage and architecture maturity level. There is no single answer, there is a context-aware choice.

For POCs and early stages, Logical Delete usually makes more sense. It provides safety for data recovery with quick implementation, low friction, and without introducing project complexity too early.

For robust MVPs, pilots, and production-ready products, Shadow Table can be much more advantageous. Despite the higher implementation cost, it helps keep the main table cleaner, improves performance predictability, and better organizes the archiving strategy.

There are other soft delete patterns beyond the ones mentioned here. In this article, I brought the most well-known ones that I have used in practice, precisely to share decisions I tested in real scenarios.

Honorable mention to Alex Buchanan, creator of the blog atlas9.dev, who inspired me to build this article.