Soft delete without breaking sign-ups: schema design exercise

A SaaS product lets people delete their account.

Three departments disagree about what "delete" means:

Support must be able to restore an account within 30 days of deletion.
Finance must keep the account record permanently, because invoices reference it.
Growth wants a deleted user's email address to become available for a brand new sign-up immediately, not after 30 days.

Design the schema. An email must be unique among active accounts, and deleting an account must never destroy the record.

The one thing that is fixed

1 relation

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

users
idemaildeleted_at

Your schemaPostgreSQL DDL: tables, constraints, indexes

schema.sql

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT NOT NULL,
  deleted_at TIMESTAMPTZ
);

-- The table is here. Now make the three departments' rules hold.

Tab indents · ⌘/Ctrl + Enter runs

What will be checked

6 tests

····A new visitor can sign up
····Two active accounts cannot share an email
····A deleted account's email frees up immediately
····The deleted account is still on record for Finance
····Only the new account counts as active
····Support can still find the deleted account by its address