Soft deletes that do not haunt you

It is, provided every query filters on it, which is the part that fails. A partial index plus a view is the version that survives:

CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
CREATE INDEX users_active_idx ON users (id) WHERE deleted_at IS NULL;

Then application code selects from the view and forgetting the filter becomes impossible rather than merely discouraged.

A surrogate id, with a unique constraint on the email. Natural keys change: people change emails, countries change codes, ISBNs get reissued. Every one of those becomes a cascading update across every table that referenced it.