계정 상태와 익명화

DROP TABLE IF EXISTS member_lifecycle_bad;
CREATE TABLE member_lifecycle_bad (
  member_id BIGINT UNSIGNED PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  status VARCHAR(16) NOT NULL,
  CHECK (status IN ('ACTIVE', 'INACTIVE'))
) ENGINE=InnoDB;

INSERT INTO member_lifecycle_bad VALUES
  (1, '[email protected]', 'INACTIVE'),
  (2, '[email protected]', 'INACTIVE');

UPDATE member_lifecycle_bad
SET email = CONCAT('anonymous+', member_id, '@invalid.local')
WHERE status = 'INACTIVE';

SELECT member_id, email FROM member_lifecycle_bad ORDER BY member_id;

member_id | email
1         | [email protected]
2         | [email protected]

DROP PROCEDURE IF EXISTS anonymize_member;
DROP TABLE IF EXISTS member_erasure_request;

ALTER TABLE members
  MODIFY COLUMN status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
  ADD COLUMN suspended_until DATETIME(6) NULL,
  ADD COLUMN suspension_reason VARCHAR(240) NULL,
  ADD COLUMN anonymized_at DATETIME(6) NULL,
  ADD CONSTRAINT chk_members_access CHECK (
    (status = 'ACTIVE' AND suspended_until IS NULL AND suspension_reason IS NULL)
    OR
    (status = 'SUSPENDED' AND suspended_until IS NOT NULL
     AND suspension_reason IS NOT NULL
     AND CHAR_LENGTH(TRIM(suspension_reason)) >= 10)
  ),
  ADD CONSTRAINT chk_members_anonymized
    CHECK (anonymized_at IS NULL OR deleted_at IS NOT NULL),
  ADD INDEX ix_members_suspension (status, suspended_until, id);

CREATE TABLE member_erasure_request (
  erasure_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_id BIGINT UNSIGNED NOT NULL,
  request_status VARCHAR(16) NOT NULL,
  requested_at DATETIME(6) NOT NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME(6) NULL,
  execute_after DATETIME(6) NOT NULL,
  legal_hold_until DATETIME(6) NULL,
  executed_at DATETIME(6) NULL,
  open_slot TINYINT GENERATED ALWAYS AS
    (IF(request_status IN ('PENDING', 'APPROVED'), 1, NULL)) STORED,
  CONSTRAINT uq_erasure_open UNIQUE (member_id, open_slot),
  CONSTRAINT fk_erasure_member FOREIGN KEY (member_id)
    REFERENCES members (id) ON DELETE RESTRICT,
  CONSTRAINT fk_erasure_approver FOREIGN KEY (approved_by)
    REFERENCES members (id) ON DELETE RESTRICT,
  CONSTRAINT chk_erasure_status CHECK
    (request_status IN ('PENDING', 'APPROVED', 'CANCELLED', 'EXECUTED')),
  CONSTRAINT chk_erasure_approval CHECK (
    (request_status = 'PENDING' AND approved_by IS NULL AND approved_at IS NULL)
    OR (request_status IN ('APPROVED', 'EXECUTED')
        AND approved_by IS NOT NULL AND approved_at IS NOT NULL)
    OR request_status = 'CANCELLED'
  ),
  INDEX ix_erasure_due (request_status, execute_after, erasure_id)
) ENGINE=InnoDB;

DELIMITER //
CREATE PROCEDURE anonymize_member(
  IN p_erasure BIGINT UNSIGNED,
  IN p_cutoff DATETIME(6)
)
BEGIN
  DECLARE v_member BIGINT UNSIGNED;
  DECLARE v_execute DATETIME(6);
  DECLARE v_hold DATETIME(6);
  DECLARE v_deleted DATETIME(6);
  DECLARE v_anonymized DATETIME(6);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;
  SELECT member_id, execute_after, legal_hold_until
    INTO v_member, v_execute, v_hold
  FROM member_erasure_request
  WHERE erasure_id = p_erasure AND request_status = 'APPROVED'
  FOR UPDATE;
  IF p_cutoff IS NULL OR v_member IS NULL OR v_execute > p_cutoff
     OR (v_hold IS NOT NULL AND v_hold > p_cutoff) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'erasure not executable';
  END IF;

  SELECT deleted_at, anonymized_at INTO v_deleted, v_anonymized
  FROM members
  WHERE id = v_member
  FOR UPDATE;
  IF v_deleted IS NULL OR v_anonymized IS NOT NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'member lifecycle incompatible';
  END IF;

  UPDATE members
  SET email = CONCAT('anonymous+', id, '@invalid.local'),
      name = '탈퇴한 회원',
      anonymized_at = p_cutoff
  WHERE id = v_member;
  UPDATE member_erasure_request
  SET request_status = 'EXECUTED', executed_at = p_cutoff
  WHERE erasure_id = p_erasure;
  COMMIT;
END//
DELIMITER ;

SET @member_id = (
  SELECT id FROM members
  WHERE deleted_at IS NOT NULL AND anonymized_at IS NULL
  ORDER BY id LIMIT 1
);
INSERT INTO member_erasure_request
  (member_id, request_status, requested_at, approved_by, approved_at, execute_after)
VALUES (@member_id, 'APPROVED', '2026-07-16 09:00:00', @member_id,
        '2026-07-17 09:00:00', '2026-08-16 09:00:00');
SET @erasure_id = LAST_INSERT_ID();
CALL anonymize_member(@erasure_id, '2026-08-16 09:00:00');

SELECT r.request_status, m.email, m.name, m.anonymized_at
FROM member_erasure_request AS r
JOIN members AS m ON m.id = r.member_id
WHERE r.erasure_id = @erasure_id;

CALL anonymize_member(@erasure_id, '2026-08-16 09:00:00');

request_status | email                     | name        | anonymized_at
EXECUTED       | [email protected] | 탈퇴한 회원 | 2026-08-16 09:00:00.000000

ERROR 1644 (45000): erasure not executable

SELECT id AS member_id FROM members
WHERE status = 'SUSPENDED' AND deleted_at IS NULL AND anonymized_at IS NOT NULL;

SELECT r.erasure_id
FROM member_erasure_request AS r
JOIN members AS l ON l.id = r.member_id
WHERE r.request_status = 'EXECUTED'
  AND (l.anonymized_at IS NULL OR r.executed_at <> l.anonymized_at);

SELECT id AS member_id FROM members
WHERE anonymized_at IS NOT NULL
  AND (email <> CONCAT('anonymous+', id, '@invalid.local')
       OR name <> '탈퇴한 회원');

SELECT r.erasure_id FROM member_erasure_request AS r
WHERE r.request_status = 'APPROVED'
  AND r.execute_after <= '2026-08-16 09:00:00'
  AND (r.legal_hold_until IS NULL OR r.legal_hold_until <= '2026-08-16 09:00:00');

SELECT s.id AS post_id FROM posts AS s
LEFT JOIN members AS l ON l.id = s.author_id
WHERE l.id IS NULL;