계정 상태와 익명화

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,'suspended@example.com','INACTIVE'),
 (2,'withdrawn@example.com','INACTIVE');
UPDATE member_lifecycle_bad
SET email=CONCAT('anonymous+',id,'@invalid.local')
WHERE status='INACTIVE';
SELECT member_id,email FROM member_lifecycle_bad ORDER BY member_id;

inactive rows selected=2
suspended member anonymized=yes
withdrawn member anonymized=yes
retention cutoff checked=no
approval evidence checked=no

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 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 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');

erasure status=EXECUTED
email=anonymous+<member_id>@invalid.local
name=탈퇴한 회원
historical post orphans=0
re-execution=SIGNAL 45000

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 r JOIN members 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 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 s LEFT JOIN members l ON l.id=s.author_id
WHERE l.id IS NULL;