Soft Delete in Synced Databases: Do It Right

Deleting a row from a database is final. In a single application, that may be fine. In synced systems, it causes problems: the sync cannot tell the other system about a record that no longer exists, reports lose history, and a mistaken delete cannot be undone without a backup restore. Soft delete is an alternative: instead of removing a row, you mark it as deleted and hide it from normal use. It solves real problems, but it creates new ones around unique keys, queries that forget the filter and legal obligations to actually erase personal data. This guide explains how to implement soft delete in synced databases, how to sync deletions and when a hard delete is still required.

What soft delete means

With soft delete, each table that supports it gets a marker, usually a deleted_at timestamp or an is_deleted flag, often with who deleted the row and why. Deleting a record sets the marker. Normal queries exclude marked rows, so users no longer see them, but the data remains for history, auditing, recovery and sync.

A timestamp is generally more useful than a simple flag, because it records when the delete happened and lets a sync or a report find rows deleted since a given time.

Why synced systems need it

Syncs usually work by detecting changes and passing them on. A hard-deleted row leaves nothing to detect unless you capture the delete at the moment it happens. Soft delete turns a delete into an ordinary update, which every change-detection method can see:

• Timestamp-based syncs can find rows whose deleted_at changed since the last run
• Change data capture sees an update rather than relying on delete events
• Reconciliation can compare deleted records on both sides instead of guessing why a row is missing

It also lets you handle the conflicting case where one system deletes a record while the other updates it, by holding the record for review rather than losing the update. Our guide to sync conflict resolution covers rules for that situation.

Soft delete and unique keys

One technical problem to plan for is uniqueness. If email addresses must be unique, and a contact is soft-deleted, a new contact with the same email will clash with the hidden row. PostgreSQL offers a clean solution in partial indexes. Its documentation describes creating a unique index over a subset of a table, which enforces uniqueness among the rows that satisfy the index predicate without constraining those that do not (PostgreSQL: Partial indexes). A unique index on email where deleted_at is null enforces uniqueness only among active rows.

Decide also what happens when a deleted record is recreated. Restoring the old row keeps its history and links; creating a new one keeps a clean break. Choose per entity and document it.

Make queries filter consistently

The risk with soft delete is that some query forgets the filter and shows deleted data, or worse, counts it in a total. Reduce that risk:

• Provide a view of active rows for each soft-deleted table and use it by default
• Use your application framework's support for default query filters where available
• Test reports and exports against data that includes deleted rows, to catch missing filters
• Make including deleted rows an explicit choice in code, such as a clearly named method

Indexes limited to active rows can also stay smaller than full-table indexes, which helps when deleted rows accumulate.

Sync deletions as events

When a record is soft-deleted in one system, decide what the other system should do. Options include soft-deleting the linked record, archiving it, removing it from active lists only or leaving it and flagging it for review. The right choice depends on ownership: if the CRM owns contacts, a delete there might archive the contact in the marketing tool but leave invoices in accounting untouched. Write these rules down per entity, and make the sync apply them consistently.

Worked example: hypothetical deletion rules

The table below shows hypothetical deletion rules for a CRM, an accounting system and a marketing tool. It illustrates the decisions involved, not recommended settings.

RecordDeleted inEffect in other systemsHard delete whenContactCRMArchived in marketing tool; unchanged in accountingErasure request approved, or retention period endsCustomer accountCRMFlag for review in accounting; never deleted automaticallyRetention period ends and no open transactionsInvoiceAccountingStatus shown as void in CRMNot deleted; financial records retained per policyMarketing list memberMarketing toolOpt-out synced to CRMErasure request approved

When soft delete is not enough

Soft delete keeps data. That conflicts with obligations to erase it. The UK Information Commissioner's Office explains that under Article 17 of the UK GDPR individuals have the right to have personal data erased, also known as the right to be forgotten, that the right is not absolute and applies only in certain circumstances, and that organisations have one month to respond to a request (ICO: Right to erasure). Other jurisdictions have their own rules. A soft-deleted row that still holds a person's details is not erased.

Plan for genuine deletion alongside soft delete:

• A process to hard delete or anonymise personal data when an erasure request is approved, in every synced system
• Scheduled purges of soft-deleted rows once their retention period ends
• Coverage of backups, logs and audit records according to your policy and legal advice

Our guide to CRM data retention explains how to set retention periods and choose between archiving, anonymising and deleting.

Explain deletes to users

Users need to know what deleting a record means in each system. If deleting a contact in the CRM only archives it, and the record can be restored for a period, say so in the interface or training material, along with who can restore it and for how long. Clear expectations prevent duplicate re-entry of records people think are gone and surprise when an archived record reappears after a restore.

Keep an audit trail of deletes

Record who deleted each record, when and why, both for soft deletes and for later hard deletes. When a hard delete removes personal data, keep a minimal record that the deletion happened, without the deleted details, so you can show that requests were handled.

Frequently Asked Questions

What is a soft delete?

Marking a row as deleted, usually with a timestamp, instead of removing it. The row is hidden from normal use but remains for history, recovery and sync.

How do I keep unique constraints working with soft delete?

In PostgreSQL, use a partial unique index that applies only to active rows, for example where the deleted timestamp is null.

Does soft delete satisfy data erasure requests?

No. A soft-deleted row still holds the data. Approved erasure requests need a genuine delete or anonymisation in every system that holds the data.

How should deletions sync between systems?

Treat a soft delete as an event and apply per-entity rules in each target, based on which system owns the record.

Handle deletions safely across systems

We design deletion, retention and sync rules so records disappear where they should and history survives where it must. See our real-time CRM and database synchronization service, or talk to us about soft delete in synced systems.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.