Soft Deletes

CRMBuilder does not permanently delete most records. Instead, a deleted_at timestamp is set on the row. The record stays in the database but must be excluded from every query.

The pattern

-- A soft-deleted record looks like this:
SELECT id, first_name, deleted_at FROM contacts WHERE id = 101;
-- id | first_name | deleted_at
-- 101 | Jordan     | 2025-11-03 14:22:00

To delete a record:

UPDATE contacts
SET deleted_at = NOW()
WHERE id = :id AND id_company = :id_company;

To restore a record:

UPDATE contacts
SET deleted_at = NULL
WHERE id = :id AND id_company = :id_company;

The critical rule

Every SELECT on a soft-delete-enabled table must include WHERE deleted_at IS NULL.

Missing this clause silently returns deleted records. There is no error — the query succeeds and the data looks valid. This is the most common source of ghost records appearing in the UI.

-- Wrong — returns deleted contacts
SELECT * FROM contacts WHERE id_company = :id_company;

-- Correct
SELECT * FROM contacts
WHERE id_company = :id_company AND deleted_at IS NULL;

Which tables use soft deletes

Any table with a deleted_at column follows this pattern. Check the Schema Explorer before querying an unfamiliar table. Common soft-delete tables:

Table
contacts
deals
companies
campaigns_items
automations

Soft deletes in JOINs

The gotcha compounds in joins — both sides need the clause:

-- Wrong — a deleted deal can still JOIN to an active contact
SELECT c.first_name, d.title
FROM contacts c
JOIN deals d ON d.id_contact = c.id
WHERE c.id_company = :id_company
  AND c.deleted_at IS NULL;

-- Correct — filter both tables
SELECT c.first_name, d.title
FROM contacts c
JOIN deals d ON d.id_contact = c.id
  AND d.deleted_at IS NULL
WHERE c.id_company = :id_company
  AND c.deleted_at IS NULL;

Permanent deletes

A small number of tables (lookup tables, log tables) use hard deletes with no deleted_at column. Check the Schema Explorer — if there is no deleted_at column, use DELETE FROM as normal.

Querying deleted records intentionally

Trash/restore UIs need to show deleted records. In that case, explicitly filter for them:

-- Show only deleted contacts for a restore UI
SELECT * FROM contacts
WHERE id_company = :id_company
  AND deleted_at IS NOT NULL
ORDER BY deleted_at DESC;