Soft delete without breaking sign-ups

Difficulty 3/5Subscribers

New to this? Learn it first: UNIQUE: no two alike, or no two among some New to this? Learn it first: Level 1 boss: the signup system

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 contract

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

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…