불변 감사 원장

DROP TABLE IF EXISTS post_audit_message_bad;
CREATE TABLE post_audit_message_bad (
  log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_name VARCHAR(40) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  message TEXT NOT NULL,
  logged_at DATETIME(6) NOT NULL,
  PRIMARY KEY (log_id)
) ENGINE=InnoDB;

INSERT INTO post_audit_message_bad
  (entity_name, entity_id, message, logged_at)
VALUES
  ('posts', 7001, 'user changed something', '2026-07-15 14:00:00'),
  ('posts', 7001, 'view_count 60 to 90', '2026-07-15 14:05:00');

UPDATE post_audit_message_bad
SET message = 'approved correction'
WHERE log_id = 1;

DELETE FROM post_audit_message_bad
WHERE log_id = 2;

SELECT * FROM post_audit_message_bad;
SELECT log_id FROM post_audit_message_bad
WHERE message LIKE '%title%';

log_id | entity_name | entity_id | message             | logged_at
1      | posts       | 7001      | approved correction | 2026-07-15 14:00:00.000000

DROP PROCEDURE IF EXISTS correct_post_with_audit;
DROP TRIGGER IF EXISTS deny_audit_event_update;
DROP TRIGGER IF EXISTS deny_audit_event_delete;
DROP TRIGGER IF EXISTS deny_audit_field_update;
DROP TRIGGER IF EXISTS deny_audit_field_delete;
DROP TABLE IF EXISTS final_audit_field_change;
DROP TABLE IF EXISTS final_audit_event;

CREATE TABLE final_audit_event (
  event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  request_id BINARY(16) NOT NULL,
  entity_type VARCHAR(40) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  action_code VARCHAR(30) NOT NULL,
  actor_id BIGINT UNSIGNED NOT NULL,
  reason VARCHAR(240) NOT NULL,
  before_state JSON NOT NULL,
  after_state JSON NOT NULL,
  occurred_at DATETIME(6) NOT NULL,
  recorded_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (event_id),
  CONSTRAINT uq_audit_request UNIQUE (request_id),
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_id)
    REFERENCES members (id) ON DELETE RESTRICT,
  CONSTRAINT chk_audit_action CHECK (
    action_code IN ('CREATE','CORRECT','STATUS_CHANGE','SOFT_DELETE','RESTORE')
  ),
  CONSTRAINT chk_audit_reason CHECK (CHAR_LENGTH(TRIM(reason)) >= 10),
  INDEX ix_audit_entity_time
    (entity_type, entity_id, occurred_at DESC, event_id DESC)
) ENGINE=InnoDB;

CREATE TABLE final_audit_field_change (
  event_id BIGINT UNSIGNED NOT NULL,
  field_path VARCHAR(120) NOT NULL,
  before_value JSON NOT NULL,
  after_value JSON NOT NULL,
  PRIMARY KEY (event_id, field_path),
  CONSTRAINT fk_audit_field_event FOREIGN KEY (event_id)
    REFERENCES final_audit_event (event_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER deny_audit_event_update
BEFORE UPDATE ON final_audit_event FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit event is immutable';
END//
CREATE TRIGGER deny_audit_event_delete
BEFORE DELETE ON final_audit_event FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit event is immutable';
END//
CREATE TRIGGER deny_audit_field_update
BEFORE UPDATE ON final_audit_field_change FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit field change is immutable';
END//
CREATE TRIGGER deny_audit_field_delete
BEFORE DELETE ON final_audit_field_change FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit field change is immutable';
END//

CREATE PROCEDURE correct_post_with_audit (
  IN p_request_id BINARY(16),
  IN p_post_id BIGINT UNSIGNED,
  IN p_actor_id BIGINT UNSIGNED,
  IN p_new_title VARCHAR(160),
  IN p_reason VARCHAR(240),
  IN p_occurred_at DATETIME(6)
)
proc: BEGIN
  DECLARE v_event_id BIGINT UNSIGNED;
  DECLARE v_old_title VARCHAR(160);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;
  SELECT event_id INTO v_event_id
  FROM final_audit_event
  WHERE request_id = p_request_id
  FOR UPDATE;

  IF v_event_id IS NOT NULL THEN
    COMMIT;
    SELECT v_event_id AS event_id, 'REPLAY' AS outcome;
    LEAVE proc;
  END IF;

  SELECT title INTO v_old_title
  FROM posts
  WHERE id = p_post_id
  FOR UPDATE;

  IF v_old_title IS NULL OR CHAR_LENGTH(TRIM(p_reason)) < 10 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid audited correction';
  END IF;

  UPDATE posts
  SET title = p_new_title
  WHERE id = p_post_id;

  INSERT INTO final_audit_event
    (request_id, entity_type, entity_id, action_code, actor_id,
     reason, before_state, after_state, occurred_at)
  VALUES
    (p_request_id, 'posts', p_post_id, 'CORRECT', p_actor_id,
     p_reason, JSON_OBJECT('title', v_old_title),
     JSON_OBJECT('title', p_new_title), p_occurred_at);
  SET v_event_id = LAST_INSERT_ID();

  INSERT INTO final_audit_field_change
    (event_id, field_path, before_value, after_value)
  VALUES
    (v_event_id, '$.title', JSON_QUOTE(v_old_title), JSON_QUOTE(p_new_title));
  COMMIT;
  SELECT v_event_id AS event_id, 'APPLIED' AS outcome;
END//
DELIMITER ;

SET @post_id = (SELECT id FROM posts ORDER BY id LIMIT 1);
SET @author_id = (SELECT author_id FROM posts WHERE id = @post_id);
SET @audit_request = UUID_TO_BIN('55555555-5555-4555-8555-555555555555');
CALL correct_post_with_audit(
  @audit_request, @post_id, @author_id,
  'append-only 감사 실습', '운영자가 원본 검수 기록을 확인한 제목 정정',
  '2026-07-15 14:00:00.000000'
);
CALL correct_post_with_audit(
  @audit_request, @post_id, @author_id,
  '재전송에서 무시될 제목', '응답 손실 뒤 동일 요청을 그대로 재전송',
  '2026-07-15 14:00:00.000000'
);

SELECT
  (SELECT COUNT(*) FROM final_audit_event
   WHERE request_id = @audit_request) AS events,
  (SELECT COUNT(*) FROM final_audit_field_change AS f
   JOIN final_audit_event AS e ON e.event_id = f.event_id
   WHERE e.request_id = @audit_request) AS field_rows,
  (SELECT title FROM posts WHERE id = @post_id) AS current_title;

event_id | outcome
1        | APPLIED

event_id | outcome
1        | REPLAY

events | field_rows | current_title
1      | 1          | append-only 감사 실습

UPDATE final_audit_event
SET reason = '나중에 바꾼 사유'
WHERE request_id = @audit_request;

DELETE FROM final_audit_event
WHERE request_id = @audit_request;

UPDATE final_audit_field_change
SET after_value = JSON_QUOTE('나중에 바꾼 제목')
WHERE event_id = (
 SELECT event_id FROM final_audit_event WHERE request_id = @audit_request
) AND field_path = '$.title';

DELETE FROM final_audit_field_change
WHERE event_id = (
 SELECT event_id FROM final_audit_event WHERE request_id = @audit_request
) AND field_path = '$.title';

SELECT e.event_id
FROM final_audit_event AS e
LEFT JOIN final_audit_field_change AS f ON f.event_id = e.event_id
WHERE e.before_state <> e.after_state
GROUP BY e.event_id
HAVING COUNT(f.field_path) = 0;

SELECT e.event_id, f.field_path
FROM final_audit_event AS e
JOIN final_audit_field_change AS f ON f.event_id = e.event_id
WHERE NOT (JSON_EXTRACT(e.before_state, f.field_path) <=> f.before_value)
   OR NOT (JSON_EXTRACT(e.after_state, f.field_path) <=> f.after_value);

SELECT e.event_id
FROM final_audit_event AS e
LEFT JOIN posts AS s
  ON e.entity_type = 'posts' AND s.id = e.entity_id
WHERE e.entity_type = 'posts' AND s.id IS NULL;

SELECT HEX(request_id), COUNT(*) AS copies
FROM final_audit_event
GROUP BY request_id
HAVING COUNT(*) <> 1;

ERROR 1644 (45000): audit event is immutable
ERROR 1644 (45000): audit event is immutable
ERROR 1644 (45000): audit field change is immutable
ERROR 1644 (45000): audit field change is immutable