How to model soft deletes in PostgreSQL without breaking unique constraints?

No comments have been posted yet, so there isn't yet a converged answer to summarize. Here's a partial answer based on common, well-established PostgreSQL practice for this exact problem, but treat it as a starting point to validate against your own constraints, not a definitive fix:

What's ruled out

• A plain UNIQUE constraint on the key column(s) alone doesn't work once you add deleted_at, since Postgres will still enforce uniqueness across soft-deleted and live rows.
• Just adding deleted_at to a composite unique constraint (e.g. UNIQUE(key, deleted_at)) is fragile: NULL values are treated as distinct in standard unique constraints, so it can work for "all active rows have deleted_at = NULL", but it's easy to get subtly wrong if you ever set deleted_at to a non-null sentinel or allow multiple deletes.

Likely cleanest approach: partial unique index

Enforce uniqueness only among "live" (not-deleted) rows using a partial index:

CREATE UNIQUE INDEX uniq_active_key
ON your_table (key_column)
WHERE deleted_at IS NULL;

This lets you have unlimited soft-deleted rows with the same key_column, while still guaranteeing no two active rows collide. This is the standard pattern for this problem.

Things to double check before adopting it

• Make sure your ORM/migration tool supports partial indexes cleanly (some don't generate them by default).
• If the key is composite, include all relevant columns: WHERE deleted_at IS NULL still applies, just extend the column list.
• Consider whether you actually need to preserve history of soft-deleted rows with the same key, or whether an alternative (moving deleted rows to an archive table) suits your case better — that avoids the constraint problem entirely but changes your query model.

Since there's no further discussion yet to confirm edge cases (e.g., behavior with concurrent inserts/deletes, or NULL-key handling), it's worth testing this against your actual schema and expected concurrency patterns before finalizing.

The issue is a stale closure. Move the fetch into a function and include a cleanup flag:

useEffect(() => {
  let alive = true;
  fetch('/api/items')
    .then(r => r.json())
    .then(d => { if (alive) setItems(d); });
  return () => { alive = false; };
}, []);

If items is still empty, check the Network tab — the endpoint may be returning { data: [...] } rather than an array.

Use a partial unique index that ignores soft-deleted rows:

CREATE UNIQUE INDEX users_email_active
  ON users (email) WHERE deleted_at IS NULL;

Now soft-deleted rows don't collide with new ones.