CRM UI patterns

Soft deletes, statuses, timestamps, pagination - the boring stuff that makes a CRM feel real instead of like a homework schema.

Soft delete

Hard DELETE is fine for junk scratch data. For contacts and deals, product folks usually want undo / audit / "who removed this?".

-- "delete" a contact
UPDATE contacts
SET deleted_at = datetime('now'),
    updated_at = datetime('now')
WHERE id = 3
  AND deleted_at IS NULL;

Every list query then filters:

WHERE deleted_at IS NULL

Restore:

UPDATE contacts
SET deleted_at = NULL,
    updated_at = datetime('now')
WHERE id = 3;

FE: a "Show deleted" toggle is just dropping that filter (plus maybe a badge).

Status / stage as a state machine

Deals use stage. Moving a card on the board:

UPDATE deals
SET stage = 'proposal',
    updated_at = datetime('now')
WHERE id = 2
  AND deleted_at IS NULL
  AND stage = 'qualified';  -- optional: only allow expected transitions

Won / lost often also stamp a closed date (we kept the schema small - you'd add closed_at in a v2).

Timestamps

Column	Job
created_at	when the row appeared
updated_at	last meaningful change (touch on edit / stage move)
deleted_at	soft delete marker

UI copy: "Updated 2 hours ago" → read updated_at, format in the client.

Remember to bump updated_at in your UPDATEs - databases won't do it for free unless you add triggers.

Pagination that matches the list page

// FE
const page = 2
const pageSize = 20
const offset = (page - 1) * pageSize

SELECT id, name, email, company_id
FROM contacts
WHERE deleted_at IS NULL
ORDER BY name
LIMIT 20 OFFSET 20;

Total count for "Page 2 of 14":

SELECT COUNT(*) AS total
FROM contacts
WHERE deleted_at IS NULL;

Two queries is normal. Some APIs return { data, total }.

Filtering the list

SELECT c.id, c.name, c.email, c.company_id
FROM contacts AS c
WHERE c.deleted_at IS NULL
  AND (:company_id IS NULL OR c.company_id = :company_id)
  AND (:q IS NULL OR c.name LIKE '%' || :q || '%' OR c.email LIKE '%' || :q || '%')
ORDER BY c.name
LIMIT :limit OFFSET :offset;

That's your filter bar: company select + search box + page controls. Dynamic filters in app code usually build the WHERE pieces carefully (still with binds).

Ownership

"Only show me my deals" is just another filter:

AND owner_id = :current_user_id

Admin "show all" drops it. Permissions are an app-layer story; SQL just needs the column.

Empty states are query results too

SELECT COUNT(*) FROM deals
WHERE owner_id = :uid AND deleted_at IS NULL AND stage NOT IN ('won', 'lost');

Zero → render the empty state illustration. Don't special-case it in the database - special-case it in the UI.

As you can see, once the schema is honest about soft deletes and ownership, the React/Vue bits get boring in a good way.

One more chapter: What APIs hide.