deploy/migrations/0107_account_lifecycle.up.sql

Rama main · réplica de lectura del repositorio original.

-- Unified account deletion lifecycle. A deleted account remains as a minimal
-- user tombstone so historical messages keep a stable sender id, while all
-- reusable identity and profile fields are released atomically.
ALTER TABLE public.users
  ADD COLUMN deleted_at timestamp with time zone,
  ADD COLUMN deletion_source text DEFAULT '' NOT NULL,
  ADD COLUMN deletion_reason text DEFAULT '' NOT NULL,
  ADD COLUMN account_delete_at timestamp with time zone;

ALTER TABLE public.account_passwords
  ADD COLUMN password_changed_at timestamp with time zone;

ALTER TABLE public.account_settings
  DROP CONSTRAINT account_settings_account_ttl_days_check,
  ADD CONSTRAINT account_settings_account_ttl_days_check
    CHECK (account_ttl_days BETWEEN 1 AND 3650);

CREATE OR REPLACE FUNCTION public.telesrv_password_changed_at_trigger()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.password_changed_at := CASE WHEN NEW.has_password THEN now() ELSE NULL END;
  ELSIF NEW.has_password IS DISTINCT FROM OLD.has_password
     OR NEW.srp_verifier IS DISTINCT FROM OLD.srp_verifier
     OR NEW.current_algo_salt1 IS DISTINCT FROM OLD.current_algo_salt1
     OR NEW.current_algo_salt2 IS DISTINCT FROM OLD.current_algo_salt2
     OR NEW.current_algo_g IS DISTINCT FROM OLD.current_algo_g
     OR NEW.current_algo_p IS DISTINCT FROM OLD.current_algo_p THEN
    NEW.password_changed_at := CASE WHEN NEW.has_password THEN now() ELSE NULL END;
  ELSE
    NEW.password_changed_at := OLD.password_changed_at;
  END IF;
  RETURN NEW;
END
$$;

UPDATE public.account_passwords
   SET password_changed_at = updated_at
 WHERE has_password = true AND password_changed_at IS NULL;

CREATE TRIGGER account_passwords_changed_at_trigger
BEFORE INSERT OR UPDATE ON public.account_passwords
FOR EACH ROW EXECUTE FUNCTION public.telesrv_password_changed_at_trigger();

ALTER TABLE public.users
  ADD CONSTRAINT users_deletion_state_check CHECK (
    (deleted_at IS NULL AND deletion_source = '' AND deletion_reason = '')
    OR
    (deleted_at IS NOT NULL
      AND deletion_source IN (
        'manual',
        'forgot_password',
        'tos_decline',
        'password_reset_expiry',
        'account_ttl',
        'freeze_expiry'
      )
      AND account_delete_at IS NULL
      AND phone = ''
      AND first_name = ''
      AND last_name = ''
      AND username = ''
      AND country_code = ''
      AND about = ''
      AND verified = false
      AND support = false
      AND premium_expires_at IS NULL
      AND emoji_status_document_id = 0
      AND emoji_status_until = 0
      AND color_set = false
      AND color = 0
      AND color_background_emoji_id = 0
      AND profile_color_set = false
      AND profile_color = 0
      AND profile_color_background_emoji_id = 0
      AND birthday_day = 0
      AND birthday_month = 0
      AND birthday_year = 0
      AND personal_channel_id = 0
      AND last_seen_at = 0
      AND octet_length(deletion_reason) <= 1024)
  );

CREATE INDEX users_account_delete_due_idx
  ON public.users (account_delete_at, id)
  WHERE deleted_at IS NULL AND is_bot = false AND account_delete_at IS NOT NULL;

CREATE TABLE public.account_deletion_requests (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES public.users(id),
  requester_auth_key_id bigint NOT NULL,
  state text DEFAULT 'pending' NOT NULL,
  reason text DEFAULT '' NOT NULL,
  confirm_hash_digest bytea NOT NULL,
  requested_at timestamp with time zone DEFAULT now() NOT NULL,
  execute_at timestamp with time zone NOT NULL,
  completed_at timestamp with time zone,
  updated_at timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT account_deletion_requests_state_check CHECK (
    (state = 'pending' AND completed_at IS NULL)
    OR (state IN ('cancelled', 'executed') AND completed_at IS NOT NULL)
  ),
  CONSTRAINT account_deletion_requests_reason_check CHECK (octet_length(reason) <= 1024),
  CONSTRAINT account_deletion_requests_hash_check CHECK (octet_length(confirm_hash_digest) = 32)
);

CREATE UNIQUE INDEX account_deletion_requests_one_pending_user_idx
  ON public.account_deletion_requests(user_id) WHERE state = 'pending';
CREATE UNIQUE INDEX account_deletion_requests_pending_hash_idx
  ON public.account_deletion_requests(confirm_hash_digest) WHERE state = 'pending';
CREATE INDEX account_deletion_requests_due_idx
  ON public.account_deletion_requests(execute_at, id) WHERE state = 'pending';

-- updateUser is not a pts-bearing update. Keep a dedicated durable online-nudge
-- queue so a crash between tombstone commit and best-effort fan-out is recovered.
-- Offline clients converge from authoritative dialog/history hydration instead
-- of an immortal retry queue or invented user pts events.
CREATE TABLE public.account_deletion_notifications (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  target_user_id bigint NOT NULL REFERENCES public.users(id),
  deleted_user_id bigint NOT NULL REFERENCES public.users(id),
  status text DEFAULT 'pending' NOT NULL,
  attempts integer DEFAULT 0 NOT NULL,
  next_attempt_at timestamp with time zone DEFAULT now() NOT NULL,
  lease_until timestamp with time zone,
  last_error text DEFAULT '' NOT NULL,
  created_at timestamp with time zone DEFAULT now() NOT NULL,
  updated_at timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT account_deletion_notifications_status_check
    CHECK (status IN ('pending', 'dispatching', 'delivered')),
  CONSTRAINT account_deletion_notifications_attempts_check CHECK (attempts >= 0),
  CONSTRAINT account_deletion_notifications_not_self_check CHECK (target_user_id <> deleted_user_id),
  UNIQUE (target_user_id, deleted_user_id)
);

CREATE INDEX account_deletion_notifications_ready_idx
  ON public.account_deletion_notifications(next_attempt_at, id)
  WHERE status = 'pending';

-- Account deletion discovers peers that have an inbound dialog row pointing at
-- the deleted user.  The ordinary dialog PK only covers the owner direction;
-- keep the reverse lookup indexed so tombstoning one account never scans every
-- user's dialog table.
CREATE INDEX dialogs_user_peer_reverse_idx
  ON public.dialogs(peer_id, user_id)
  WHERE peer_type = 'user';

CREATE OR REPLACE FUNCTION public.telesrv_account_delete_at(
  p_created_at timestamp with time zone,
  p_last_seen_at bigint,
  p_ttl_days integer
) RETURNS timestamp with time zone
LANGUAGE sql IMMUTABLE AS $$
  SELECT GREATEST(
    p_created_at,
    CASE WHEN p_last_seen_at > 0 THEN to_timestamp(p_last_seen_at) ELSE p_created_at END
  ) + make_interval(days => p_ttl_days)
$$;

CREATE OR REPLACE FUNCTION public.telesrv_users_account_delete_at_trigger()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  v_ttl_days integer;
BEGIN
  IF NEW.deleted_at IS NOT NULL OR NEW.is_bot THEN
    NEW.account_delete_at := NULL;
    RETURN NEW;
  END IF;
  SELECT account_ttl_days INTO v_ttl_days
    FROM public.account_settings WHERE user_id = NEW.id;
  NEW.account_delete_at := public.telesrv_account_delete_at(
    NEW.created_at,
    NEW.last_seen_at,
    COALESCE(v_ttl_days, 365)
  );
  RETURN NEW;
END
$$;

CREATE TRIGGER users_account_delete_at_trigger
BEFORE INSERT OR UPDATE OF last_seen_at, deleted_at, is_bot
ON public.users
FOR EACH ROW EXECUTE FUNCTION public.telesrv_users_account_delete_at_trigger();

CREATE OR REPLACE FUNCTION public.telesrv_account_settings_ttl_trigger()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  v_user_id bigint;
  v_ttl_days integer;
BEGIN
  v_user_id := COALESCE(NEW.user_id, OLD.user_id);
  v_ttl_days := CASE WHEN TG_OP = 'DELETE' THEN 365 ELSE NEW.account_ttl_days END;
  UPDATE public.users
     SET account_delete_at = public.telesrv_account_delete_at(created_at, last_seen_at, v_ttl_days),
         updated_at = now()
   WHERE id = v_user_id AND deleted_at IS NULL AND is_bot = false;
  RETURN COALESCE(NEW, OLD);
END
$$;

CREATE TRIGGER account_settings_ttl_trigger
AFTER INSERT OR UPDATE OF account_ttl_days OR DELETE
ON public.account_settings
FOR EACH ROW EXECUTE FUNCTION public.telesrv_account_settings_ttl_trigger();

UPDATE public.users u
   SET account_delete_at = public.telesrv_account_delete_at(
     u.created_at,
     u.last_seen_at,
     COALESCE((
       SELECT s.account_ttl_days
         FROM public.account_settings s
        WHERE s.user_id = u.id
     ), 365)
   )
 WHERE u.deleted_at IS NULL AND u.is_bot = false;