저장 구조와 AI 콘텐츠 검수

DROP TABLE IF EXISTS ai_content_draft_bad;
CREATE TABLE ai_content_draft_bad(
 draft_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 author_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(200) NOT NULL,
 content LONGTEXT NOT NULL,
 model_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

INSERT INTO members(email,password_hash,name,status,created_at)
VALUES(
 '[email protected]','not-a-real-hash',
 '자동 게시 위험 예제 작성자','ACTIVE',CURRENT_TIMESTAMP(6)
)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SET @member_id=LAST_INSERT_ID();

DELETE FROM posts
WHERE author_id=@member_id
 AND request_key LIKE 'ai-draft-%';

INSERT INTO ai_content_draft_bad(author_id,title,content,model_name)
VALUES(@member_id,'검증되지 않은 성능 수치',
 '홍길동의 이메일은 [email protected]이며 이 설정은 항상 10배 빠르다.',
 'example-model');

INSERT INTO posts
 (author_id,title,content,status,view_count,created_at,updated_at,published_at,version,request_key)
SELECT author_id,title,content,'PUBLISHED',0,CURRENT_TIMESTAMP(6),CURRENT_TIMESTAMP(6),
 CURRENT_TIMESTAMP(6),0,CONCAT('ai-draft-',draft_id)
FROM ai_content_draft_bad;

candidate review records=0
personal-data check=missing
fact-source check=missing
automatic published posts=1
human approval=0

DROP PROCEDURE IF EXISTS approve_ai_content_candidate;
DROP TRIGGER IF EXISTS freeze_approved_ai_candidate;
DROP TABLE IF EXISTS content_release_item;
DROP TABLE IF EXISTS content_release;
DROP TABLE IF EXISTS ai_review_result;
DROP TABLE IF EXISTS ai_review_rule;
DROP TABLE IF EXISTS ai_content_candidate;

CREATE TABLE ai_content_candidate(
 candidate_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 target_post_id BIGINT UNSIGNED NULL,
 source_request_id VARCHAR(120) NOT NULL,
 model_reference VARCHAR(120) NOT NULL,
 prompt_summary VARCHAR(500) NOT NULL,
 proposed_title VARCHAR(200) NOT NULL,
 proposed_content LONGTEXT NOT NULL,
 content_sha256 BINARY(32) GENERATED ALWAYS AS(
  UNHEX(SHA2(CONCAT(proposed_title,CHAR(0),proposed_content),256))
 ) STORED,
 risk_level VARCHAR(16) NOT NULL,
 review_status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
 submitter_id BIGINT UNSIGNED NOT NULL,
 reviewer_id BIGINT UNSIGNED NULL,
 review_note VARCHAR(500) NULL,
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 reviewed_at DATETIME(6) NULL,
 UNIQUE KEY uq_ai_candidate_source(source_request_id),
 FOREIGN KEY(target_post_id) REFERENCES posts(id) ON DELETE SET NULL,
 FOREIGN KEY(submitter_id) REFERENCES members(id) ON DELETE RESTRICT,
 FOREIGN KEY(reviewer_id) REFERENCES members(id) ON DELETE RESTRICT,
 CHECK(risk_level IN('LOW','MEDIUM','HIGH')),
 CHECK(review_status IN('PENDING','APPROVED','REJECTED'))
) ENGINE=InnoDB;

CREATE TABLE ai_review_rule(
 rule_code VARCHAR(40) PRIMARY KEY,
 description VARCHAR(240) NOT NULL,
 is_required BOOLEAN NOT NULL DEFAULT TRUE,
 CHECK(is_required IN(0,1))
) ENGINE=InnoDB;

INSERT INTO ai_review_rule VALUES
 ('FACT_SOURCE','수치와 사실의 근거를 확인',TRUE),
 ('PERSONAL_DATA','개인정보와 비밀값 노출을 확인',TRUE),
 ('COPYRIGHT','과도한 인용과 권리 침해를 확인',TRUE),
 ('LINK_SAFETY','외부 링크의 목적과 안전성을 확인',TRUE),
 ('HARMFUL_CONTENT','유해하거나 차별적인 표현을 확인',TRUE),
 ('BOARD_SCOPE','게시판 목적과 카테고리 범위를 확인',TRUE),
 ('DUPLICATION','기존 게시글과 실질 중복을 확인',TRUE),
 ('EDITORIAL_STYLE','교재형 문체와 표기 규칙을 확인',TRUE);

CREATE TABLE ai_review_result(
 candidate_id BIGINT UNSIGNED NOT NULL,
 rule_code VARCHAR(40) NOT NULL,
 review_result VARCHAR(16) NOT NULL,
 evidence VARCHAR(1000) NOT NULL,
 reviewed_by BIGINT UNSIGNED NOT NULL,
 reviewed_at DATETIME(6) NOT NULL,
 PRIMARY KEY(candidate_id,rule_code),
 FOREIGN KEY(candidate_id) REFERENCES ai_content_candidate(candidate_id) ON DELETE CASCADE,
 FOREIGN KEY(rule_code) REFERENCES ai_review_rule(rule_code) ON DELETE RESTRICT,
 FOREIGN KEY(reviewed_by) REFERENCES members(id) ON DELETE RESTRICT,
 CHECK(review_result IN('PASS','FAIL','MISSING'))
) ENGINE=InnoDB;

CREATE TABLE content_release(
 release_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 release_name VARCHAR(120) NOT NULL,
 release_status VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
 approved_by BIGINT UNSIGNED NOT NULL,
 approved_at DATETIME(6) NOT NULL,
 UNIQUE KEY uq_content_release_name(release_name),
 FOREIGN KEY(approved_by) REFERENCES members(id) ON DELETE RESTRICT,
 CHECK(release_status IN('DRAFT','READY','APPLIED','WITHDRAWN'))
) ENGINE=InnoDB;

CREATE TABLE content_release_item(
 release_id BIGINT UNSIGNED NOT NULL,
 sequence_no INT UNSIGNED NOT NULL,
 candidate_id BIGINT UNSIGNED NOT NULL,
 approved_content_sha256 BINARY(32) NOT NULL,
 PRIMARY KEY(release_id,sequence_no),
 UNIQUE KEY uq_release_candidate(release_id,candidate_id),
 FOREIGN KEY(release_id) REFERENCES content_release(release_id) ON DELETE RESTRICT,
 FOREIGN KEY(candidate_id) REFERENCES ai_content_candidate(candidate_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER freeze_approved_ai_candidate
BEFORE UPDATE ON ai_content_candidate FOR EACH ROW
BEGIN
 IF OLD.review_status='APPROVED' AND(
   NEW.review_status<>'APPROVED'
   OR NOT(CAST(NEW.proposed_title AS BINARY)<=>CAST(OLD.proposed_title AS BINARY))
   OR NOT(CAST(NEW.proposed_content AS BINARY)<=>CAST(OLD.proposed_content AS BINARY))
   OR NOT(CAST(NEW.source_request_id AS BINARY)<=>CAST(OLD.source_request_id AS BINARY))
  OR NOT(CAST(NEW.model_reference AS BINARY)<=>CAST(OLD.model_reference AS BINARY))
 ) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='approved AI content is immutable';
 END IF;
END//

CREATE PROCEDURE approve_ai_content_candidate(
 IN p_candidate BIGINT UNSIGNED,
 IN p_reviewer BIGINT UNSIGNED,
 IN p_note VARCHAR(500))
BEGIN
 DECLARE v_submitter BIGINT UNSIGNED;
 DECLARE v_status VARCHAR(16);
 DECLARE v_missing INT;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
 START TRANSACTION;
 SELECT submitter_id,review_status INTO v_submitter,v_status
 FROM ai_content_candidate WHERE candidate_id=p_candidate FOR UPDATE;
 IF p_reviewer IS NULL OR v_submitter IS NULL OR v_status<>'PENDING' OR v_submitter=p_reviewer THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='reviewer separation or state invalid';
 END IF;
 SELECT COUNT(*) INTO v_missing
 FROM ai_review_rule r
 LEFT JOIN ai_review_result x
  ON x.candidate_id=p_candidate
  AND x.rule_code=r.rule_code
  AND x.review_result='PASS'
  AND x.reviewed_by<>v_submitter
 WHERE r.is_required=TRUE AND x.rule_code IS NULL;
 IF v_missing<>0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='required content checks not passed';
 END IF;
 UPDATE ai_content_candidate
 SET review_status='APPROVED',reviewer_id=p_reviewer,review_note=p_note,
     reviewed_at=CURRENT_TIMESTAMP(6)
 WHERE candidate_id=p_candidate;
 COMMIT;
END//
DELIMITER ;

required rules=8
passed rules=8
reviewer separation=yes
PASS reviewers separated=yes
approved hash pinned=yes
automatic post publishing=not implemented

INSERT INTO members(email,password_hash,name,status,created_at)
VALUES('[email protected]','not-a-real-hash','AI 제출자','ACTIVE',CURRENT_TIMESTAMP(6))
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SET @submitter_id=LAST_INSERT_ID();

INSERT INTO members(email,password_hash,name,status,created_at)
VALUES('[email protected]','not-a-real-hash','AI 검토자','ACTIVE',CURRENT_TIMESTAMP(6))
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
SET @reviewer_id=LAST_INSERT_ID();

INSERT INTO ai_content_candidate
 (source_request_id,model_reference,prompt_summary,proposed_title,proposed_content,
  risk_level,submitter_id)
VALUES
 ('request-20260718-001','reviewed-model-v1','B+Tree 입문 게시글 초안',
  'B+Tree가 범위 검색에 적합한 이유',
  'B+Tree는 정렬된 키와 균형 높이를 이용해 범위 검색 경로를 제한합니다.',
  'LOW',@submitter_id);
SET @candidate=LAST_INSERT_ID();

INSERT INTO ai_review_result
SELECT @candidate,rule_code,'PASS',
 CONCAT('검토 근거: ',description),@reviewer_id,CURRENT_TIMESTAMP(6)
FROM ai_review_rule;

CALL approve_ai_content_candidate(
 @candidate,@reviewer_id,'출처·개인정보·문체 검수 완료');

INSERT INTO content_release(release_name,release_status,approved_by,approved_at)
VALUES('database-posts-2026-07-18','READY',@reviewer_id,CURRENT_TIMESTAMP(6));
SET @release=LAST_INSERT_ID();
INSERT INTO content_release_item
SELECT @release,1,candidate_id,content_sha256
FROM ai_content_candidate WHERE candidate_id=@candidate AND review_status='APPROVED';

SELECT c.candidate_id,r.rule_code
FROM ai_content_candidate c CROSS JOIN ai_review_rule r
LEFT JOIN ai_review_result x
 ON x.candidate_id=c.candidate_id
 AND x.rule_code=r.rule_code
 AND x.review_result='PASS'
WHERE c.candidate_id=@candidate AND r.is_required=TRUE AND x.rule_code IS NULL;

SELECT x.candidate_id,x.rule_code
FROM ai_review_result x
JOIN ai_content_candidate c ON c.candidate_id=x.candidate_id
WHERE x.candidate_id=@candidate
 AND x.reviewed_by=c.submitter_id;

SELECT i.release_id,i.sequence_no
FROM content_release_item i
JOIN ai_content_candidate c ON c.candidate_id=i.candidate_id
WHERE c.review_status<>'APPROVED'
 OR i.approved_content_sha256<>c.content_sha256;