Soft Delete with Filtered Indexes

-- Add soft delete column
ALTER TABLE users ADD COLUMN deleted_at timestamptz DEFAULT NULL;

-- Partial index: only index active rows for performance
CREATE INDEX idx_users_active_email
  ON users (email)
  WHERE deleted_at IS NULL;

-- View for active records (use this in your app)
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

-- Soft delete function
CREATE FUNCTION soft_delete(table_name text, row_id int)
RETURNS void AS $$
BEGIN
  EXECUTE format(
    'UPDATE %I SET deleted_at = NOW() WHERE id = $1 AND deleted_at IS NULL',
    table_name
  ) USING row_id;
END;
$$ LANGUAGE plpgsql;

-- Restore a soft-deleted row
UPDATE users SET deleted_at = NULL WHERE id = 42;

-- Hard delete old soft-deleted records (cleanup job)
DELETE FROM users
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL '90 days';

-- Usage:
-- SELECT * FROM active_users WHERE email = 'ada@example.com';
-- SELECT soft_delete('users', 42);