저장 구조와 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();

START TRANSACTION;

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;

SELECT title, status FROM posts
WHERE author_id = @member_id AND request_key LIKE 'ai-draft-%';

ROLLBACK;

title                    | status
검증되지 않은 성능 수치  | PUBLISHED

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 AS r
  LEFT JOIN ai_review_result AS 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 ;

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 AS c
CROSS JOIN ai_review_rule AS r
LEFT JOIN ai_review_result AS 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 AS x
JOIN ai_content_candidate AS 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 AS i
JOIN ai_content_candidate AS c ON c.candidate_id = i.candidate_id
WHERE c.review_status <> 'APPROVED'
   OR i.approved_content_sha256 <> c.content_sha256;

SELECT c.review_status,
       (SELECT COUNT(*) FROM ai_review_result AS x
        WHERE x.candidate_id = c.candidate_id AND x.review_result = 'PASS') AS passed_rules,
       (SELECT COUNT(*) FROM content_release_item AS i
        WHERE i.candidate_id = c.candidate_id) AS release_items
FROM ai_content_candidate AS c
WHERE c.candidate_id = @candidate;

UPDATE ai_content_candidate
SET proposed_content = '승인 뒤에 몰래 바꾼 본문'
WHERE candidate_id = @candidate;

review_status | passed_rules | release_items
APPROVED      | 8            | 1

ERROR 1644 (45000): approved AI content is immutable