Soft Delete with Partial Index

-- Schema
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index — indexes ONLY non-deleted rows (saves space + faster lookups)
CREATE INDEX idx_users_active_email ON users (LOWER(email))
WHERE deleted_at IS NULL;

-- Soft delete = update, not delete
UPDATE users SET deleted_at = NOW() WHERE id = $1;

-- Restore
UPDATE users SET deleted_at = NULL WHERE id = $1;

-- All non-deleted users (most queries do this — easy to forget the filter)
SELECT * FROM users WHERE deleted_at IS NULL;

-- Hide the filter in a view to make it the default
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM active_users WHERE email = 'alice@x.com';

-- Periodic hard-delete sweep — wipe rows soft-deleted > 90 days ago
DELETE FROM users WHERE deleted_at < NOW() - INTERVAL '90 days';