개인정보-컴플라이언스-웹애플리케이션(4) - 테이블(db), (유저,관리자) 백엔드 코드

2025. 1. 24. 18:27프로젝트

728x90

우선 백엔드는 프론트쪽에서 CRUD 작업을 하기위해서 필요한 코드입니다. 그걸 하기 위해서 mysql언어가 필요해서 테이블과 백엔드를 연결하여 사용하면 됩니다.

 

연결은 이런식으로 하면 됩니다.

import mysql from "mysql2/promise";
import dotenv from "dotenv";

dotenv.config(); // .env 파일에서 환경 변수 로드

const pool = mysql.createPool({
  host: process.env.DB_HOST || "localhost",
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
});

export default pool;

 

import mysql from "mysql2/promise"; 

mysql2 라이브러리는 Node.js에서 MySQL 데이터베이스와 연결하고 쿼리를 실행할 수 있도록 돕는 라이브러리입니다.
mysql2/promise를 사용하면 Promise 기반으로 작업할 수 있어 비동기 처리(async/await)를 깔끔하게 구현할 수 있습니다.

 

import dotenv from "dotenv";

dotenv.config();

이코드는 .env 파일에 있는 환경변수를 불러옵니다. 민감한 것들을 .env 파일에 변수로 저장하여 사용하면 됩니다.

 

waitForConnections:
true일 경우, 연결 풀이 꽉 찼을 때 클라이언트가 대기하도록 설정합니다.
connectionLimit:
연결 풀에서 동시에 사용할 수 있는 최대 연결 수를 설정합니다. 여기서는 최대 10개로 설정했습니다.

 

테이블 부터 소개를 하겠습니다.

 

우선 사용자를 담당하는 user테이블

-- 회원 테이블
CREATE TABLE User (
    `id` INT AUTO_INCREMENT PRIMARY KEY, -- 회원 ID
    `institution_name` VARCHAR(255) NOT NULL COMMENT '기관명',
    `institution_address` VARCHAR(255) NOT NULL COMMENT '기관 주소',
    `representative_name` VARCHAR(255) NOT NULL COMMENT '대표자 이름',
    `email` VARCHAR(255) NOT NULL UNIQUE COMMENT '이메일',
    `password` VARCHAR(255) NOT NULL COMMENT '비밀번호',
    `phone_number` VARCHAR(15) NOT NULL COMMENT '전화번호',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '가입 날짜',
    `member_type` VARCHAR(20) NOT NULL DEFAULT '기관회원' COMMENT '회원 유형', -- 고정된 값
    `email_verified` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '이메일 인증 여부',
    `email_token` VARCHAR(255) DEFAULT NULL COMMENT '이메일 인증 토큰',
    `email_token_expiration` DATETIME DEFAULT NULL COMMENT '이메일 토큰 만료 시간',
    `feedback_id` INT DEFAULT NULL COMMENT '피드백 ID'
);

 

그 다음 사용자가 작성한 시스템의 자가진단을 피드백하기위한 역할인 전문가 테이블

-- 전문가회원 테이블 
CREATE TABLE expert (
    id INT NOT NULL AUTO_INCREMENT COMMENT '전문가 ID',
    name VARCHAR(255) NOT NULL COMMENT '전문가 이름',
    institution_name VARCHAR(255) NOT NULL COMMENT '소속 기관명',
    ofcps VARCHAR(255) NOT NULL COMMENT '전문가 직책',
    phone_number VARCHAR(255) NOT NULL COMMENT '전화번호',
    email VARCHAR(255) NOT NULL UNIQUE COMMENT '이메일',
    major_carrea VARCHAR(255) NOT NULL COMMENT '전문 경력',
    password VARCHAR(255) NOT NULL COMMENT '비밀번호',
    PRIMARY KEY (id)
);

 

그 다음은 관리자와 시스템을 매칭시켜주는 역할인 슈퍼유저 테이블입니다.

CREATE TABLE SuperUser (
    id INT NOT NULL AUTO_INCREMENT COMMENT '슈퍼유저 ID',
    name VARCHAR(255) NOT NULL COMMENT '이름',
    email VARCHAR(255) NOT NULL COMMENT '이메일',
    password VARCHAR(255) NOT NULL COMMENT '비밀번호',
    phone_number VARCHAR(255) NOT NULL COMMENT '전화번호',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '가입 날짜',
    PRIMARY KEY (id)
);

 

그 다음 유저가 등록할 수 있는 시스템 테이블 

CREATE TABLE systems (
    `id` INT AUTO_INCREMENT PRIMARY KEY, -- 시스템 ID
    `user_id` INT NOT NULL COMMENT '회원 ID',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '등록 날짜',
    `name` VARCHAR(255) NOT NULL COMMENT '시스템 이름',
    `min_subjects` INT NOT NULL COMMENT '최소 문항 수',
    `max_subjects` INT NOT NULL COMMENT '최대 문항 수',
    `purpose` VARCHAR(255) NOT NULL COMMENT '처리 목적',
    `is_private` BOOLEAN NOT NULL COMMENT '민감 정보 포함 여부',
    `is_unique` BOOLEAN NOT NULL COMMENT '고유 식별 정보 포함 여부',
    `is_resident` BOOLEAN NOT NULL COMMENT '주민등록번호 포함 여부',
    `reason` ENUM('동의', '법적 근거', '기타') NOT NULL COMMENT '수집 근거',
    `assessment_status` ENUM('시작전', '완료') NOT NULL COMMENT '평가 상태',
    `assignment_id` INT DEFAULT NULL COMMENT '담당 ID',
    FOREIGN KEY (`user_id`) REFERENCES User(`id`) ON DELETE CASCADE
);

 

그 다음은 시스템을 등록후 시스템을 자가진단하는데 필요한 테이블입니다.

-- 자가진단 입력 테이블
CREATE TABLE self_assessment (
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '입력 ID',
    `user_id` INT NOT NULL COMMENT '회원 ID',
    `system_id` INT NOT NULL COMMENT '시스템 ID',
    `user_scale` VARCHAR(255) NOT NULL COMMENT '사용자 규모',
    `organization` ENUM('교육기관', '공공기관', '국가기관') NOT NULL COMMENT '공공기관 분류',
    `personal_info_system` ENUM('있음', '없음') NOT NULL COMMENT '개인정보처리 시스템 여부',
    `member_info_homepage` ENUM('있음', '없음') NOT NULL COMMENT '회원정보 홈페이지 여부',
    `external_data_provision` ENUM('있음', '없음') NOT NULL COMMENT '외부정보 제공 여부',
    `cctv_operation` ENUM('운영', '미운영') NOT NULL COMMENT 'CCTV 운영 여부',
    `task_outsourcing` ENUM('있음', '없음') NOT NULL COMMENT '업무 위탁 여부',
    `personal_info_disposal` ENUM('있음', '없음') NOT NULL COMMENT '개인정보 폐기 여부',
    `homepage_privacy` VARCHAR(255) DEFAULT '없음' COMMENT '홈페이지 개인정보 처리 여부',
    `submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '제출 시간',
    FOREIGN KEY (`user_id`) REFERENCES User(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`system_id`) REFERENCES systems(`id`) ON DELETE CASCADE
);

 

그리고 관리자와 시스템을 매칭할 때 필요한 테이블

CREATE TABLE assignment (	
    `id` INT NOT NULL AUTO_INCREMENT COMMENT '담당 ID',
    `expert_id` INT NOT NULL COMMENT '전문가 ID',
    `systems_id` INT NOT NULL COMMENT '시스템 ID',
    `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '할당 날짜',
    `feedback_status` BOOLEAN NOT NULL COMMENT '피드백 완료 여부',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`expert_id`) REFERENCES expert(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`systems_id`) REFERENCES systems(`id`) ON DELETE CASCADE
);

 

그 다음 자가진단 입력 테이블과 연결되어있는 정량문항 테이블입니다

CREATE TABLE quantitative (
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '문항 ID',
    `question_number` INT NOT NULL COMMENT '지표 번호',
    `unit` VARCHAR(50) DEFAULT NULL COMMENT '단위',
    `evaluation_method` ENUM('정량평가', '배점') DEFAULT '정량평가' COMMENT '평가방법',
    `score` DECIMAL(5,2) DEFAULT NULL COMMENT '배점',
    `question` TEXT DEFAULT NULL COMMENT '지표',
    `legal_basis` TEXT DEFAULT NULL COMMENT '근거법령',
    `criteria_and_references` TEXT DEFAULT NULL COMMENT '평가기준, 참고사항, 증빙자료 및 예시',
    `file_upload` VARCHAR(255) DEFAULT NULL COMMENT '파일 경로',
    `response` ENUM('이행', '미이행', '해당없음', '자문 필요') DEFAULT NULL COMMENT '평가 상태',
    `additional_comment` TEXT DEFAULT NULL COMMENT '자문 필요 사항',
    `feedback` TEXT DEFAULT '피드백 없음' COMMENT '피드백',
    `system_id` INT NOT NULL COMMENT '시스템 ID',
    FOREIGN KEY (`system_id`) REFERENCES systems(`id`) ON DELETE CASCADE,
    UNIQUE KEY unique_question (question_number, system_id)
);

 

그 다음 정량 문항 다음에 이어지는 정성 문항 테이블입니다.

CREATE TABLE qualitative (
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '문항 ID',
    `question_number` INT NULL COMMENT '지표 번호',
    `indicator` TEXT NOT NULL COMMENT '지표',
    `indicator_definition` TEXT DEFAULT NULL COMMENT '지표 정의',
    `evaluation_criteria` TEXT DEFAULT NULL COMMENT '평가기준 (착안사항)',
    `reference_info` TEXT DEFAULT NULL COMMENT '참고사항',
    `file_path` VARCHAR(255) DEFAULT NULL COMMENT '파일 업로드 경로',
    `response` ENUM('자문필요', '해당없음') DEFAULT NULL COMMENT '응답 상태',
    `additional_comment` TEXT DEFAULT NULL COMMENT '추가 의견',
    `feedback` TEXT DEFAULT NULL COMMENT '피드백',
    `system_id` INT NOT NULL COMMENT '시스템 ID',
    `user_id` INT NOT NULL COMMENT '사용자 ID',
    FOREIGN KEY (`system_id`) REFERENCES systems(`id`) ON DELETE CASCADE,
    UNIQUE KEY unique_question (question_number, system_id)
);

 

그 다음은 자가진단을 하고 난 후 결과 테이블입니다.

CREATE TABLE assessment_result (
    `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '결과 ID', -- 결과 ID
    `system_id` INT NOT NULL COMMENT '시스템 ID', -- 시스템 ID
    `user_id` INT NOT NULL COMMENT '회원 ID', -- 회원 ID
    `assessment_id` INT NOT NULL COMMENT '자가진단 입력 ID', -- 자가진단 입력 ID
    `score` INT NOT NULL COMMENT '점수', -- 점수
    `feedback_status` ENUM('전문가 자문이 반영되기전입니다', '전문가 자문이 반영되었습니다') NOT NULL COMMENT '피드백 상태', -- 피드백 상태
    `completed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '완료 시간', -- 완료 시간
    `grade` ENUM('S', 'A', 'B', 'C', 'D') NOT NULL COMMENT '등급', -- 등급
    FOREIGN KEY (`system_id`) REFERENCES systems(`id`) ON DELETE CASCADE, -- systems 테이블과 연결
    FOREIGN KEY (`user_id`) REFERENCES User(`id`) ON DELETE CASCADE, -- User 테이블과 연결
    FOREIGN KEY (`assessment_id`) REFERENCES self_assessment(`id`) ON DELETE CASCADE -- 자가진단 입력 연결
);

 

그 다음은 관리자가 피드백을 남길 수 있는 피드백 테이블입니다.

CREATE TABLE feedback (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '피드백 ID',
    assessment_result_id INT NOT NULL COMMENT '자가진단 결과 ID',
    assignment_id INT NOT NULL COMMENT '담당 시스템 ID',
    feedback_content TEXT NOT NULL COMMENT '피드백 내용',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '피드백 생성 날짜',
    FOREIGN KEY (assessment_result_id) REFERENCES assessment_result(id) ON DELETE CASCADE, -- 자가진단 결과 연결
    FOREIGN KEY (assignment_id) REFERENCES assignment(id) ON DELETE CASCADE -- 담당 시스템 연결
);

 

이렇게 테이블 설정이 끝이 나게 되고 다음은 백엔드 코드를 분석해 보겠습니다.

 

우선 유저 회원가입 부분입니다.

// 회원가입
const register = async (req, res) => {
  const {
    institution_name,
    institution_address,
    representative_name,
    email,
    password,
    phone_number,
  } = req.body;

  console.log("📩 받은 데이터:", req.body); // ✅ 디버깅 로그 추가

  try {
    const [existingUser] = await pool.query(
      "SELECT * FROM User WHERE email = ?",
      [email]
    );
    if (existingUser.length > 0) {
      return res.status(400).json({ message: "이미 사용 중인 이메일입니다." });
    }

    const hashedPassword = await bcrypt.hash(password, 10);
    await pool.query(
      `INSERT INTO User (institution_name, institution_address, representative_name, email, password, phone_number)
        VALUES (?, ?, ?, ?, ?, ?)`,
      [
        institution_name,
        institution_address,
        representative_name,
        email,
        hashedPassword,
        phone_number,
      ]
    );

    res.status(201).json({ message: "회원가입 성공!" });
  } catch (err) {
    console.error("회원가입 실패:", err); // 오류 로그 추가
    res.status(500).json({ message: "회원가입 실패", error: err.message });
  }
};

 

const register = async (req, res) => {
  const {
    institution_name,
    institution_address,
    representative_name,
    email,
    password,
    phone_number,
  } = req.body;

  console.log("📩 받은 데이터:", req.body); // ✅ 디버깅 로그 추가

 

이 부분은 req.body를 통해서 프론트 코드에서 입력한 값이 저장되는 부분입니다.

 

const [existingUser] = await pool.query(
  "SELECT * FROM User WHERE email = ?",
  [email]
);
if (existingUser.length > 0) {
  return res.status(400).json({ message: "이미 사용 중인 이메일입니다." });
}

 

SQL 쿼리: SELECT * FROM User WHERE email = ?는 이메일이 이미 존재하는지 확인하는 쿼리입니다.
?는 값이 안전하게 삽입되도록 하는 플레이스홀더입니다(SQL 인젝션 방지).
중복 확인: 결과 배열(existingUser)에 데이터가 있다면, 이메일이 이미 사용 중이라는 응답을 반환합니다.

 

const hashedPassword = await bcrypt.hash(password, 10);

 

bcrypt.hash: 사용자가 입력한 비밀번호를 **해시(hash)**로 암호화합니다.

 

await pool.query(
  `INSERT INTO User (institution_name, institution_address, representative_name, email, password, phone_number)
    VALUES (?, ?, ?, ?, ?, ?)`,
  [
    institution_name,
    institution_address,
    representative_name,
    email,
    hashedPassword,
    phone_number,
  ]
);

 

사용자 데이터 삽입하는 코드입니다.

 

다음은 로그인 코드입니다.

// 로그인
const login = async (req, res) => {
  const { email, password } = req.body;

  try {
    const [user] = await pool.query("SELECT * FROM User WHERE email = ?", [
      email,
    ]);
    if (!user || user.length === 0) {
      return res
        .status(400)
        .json({ message: "이메일 또는 비밀번호가 잘못되었습니다." });
    }

    const isMatch = await bcrypt.compare(password, user[0].password);
    if (!isMatch) {
      return res
        .status(400)
        .json({ message: "이메일 또는 비밀번호가 잘못되었습니다." });
    }

    req.session.user = {
      id: user[0].id,
      email: user[0].email,
      name: user[0].representative_name,
      member_type: "user",
    };

    res.status(200).json({
      resultCode: "S-1",
      message: "로그인 성공",
      data: req.session.user,
    });
  } catch (error) {
    console.error("❌ [EXPERT LOGIN] 로그인 오류:", error);
    res
      .status(500)
      .json({ resultCode: "F-1", msg: "서버 에러 발생", error: error.message });
  }
};

 

const login = async (req, res) => {
  const { email, password } = req.body;

 

마찬가지로 req로 프론트쪽에서 입력한 값을 불러옵니다.

const [user] = await pool.query("SELECT * FROM User WHERE email = ?", [email]);
if (!user || user.length === 0) {
  return res
    .status(400)
    .json({ message: "이메일 또는 비밀번호가 잘못되었습니다." });
}

 

SQL 쿼리: 이메일로 User 테이블에서 사용자를 조회합니다.
에러 처리: 사용자가 존재하지 않으면 상태 코드 400을 반환합니다.

const isMatch = await bcrypt.compare(password, user[0].password);
if (!isMatch) {
  return res
    .status(400)
    .json({ message: "이메일 또는 비밀번호가 잘못되었습니다." });
}

 

bcrypt.compare: 사용자가 입력한 비밀번호와 데이터베이스에 저장된 해시된 비밀번호를 비교합니다.
비밀번호 불일치: 불일치 시 400 응답을 반환합니다.

req.session.user = {
  id: user[0].id,
  email: user[0].email,
  name: user[0].representative_name,
  member_type: "user",
};

 

로그인 성공 시 사용자 정보를 세션에 저장합니다. 이는 이후 인증된 요청에서 사용됩니다.

 

다음은  로그아웃 코드입니다.

const logout = (req, res) => {
  req.session.destroy((err) => {
    if (err) {
      return res.status(500).json({ message: "로그아웃 실패" });
    }
    res.clearCookie("connect.sid");
    res.status(200).json({ message: "로그아웃 성공" });
  });
};

 

req.session.destroy: 현재 세션을 삭제합니다.
쿠키 삭제: 클라이언트의 세션 쿠키를 제거합니다.
성공적으로 세션을 삭제하면 200 응답을 반환합니다.

 

다음은 사용자 정보 조회 코드입니다.

// 사용자 정보 가져오기
const getUserInfo = (req, res) => {
  if (!req.session || !req.session.user) {
    return res.status(401).json({ message: "로그인이 필요합니다." });
  }
  res.status(200).json({ user: req.session.user });
};

 

인증 확인: 세션에 사용자 정보가 없으면 401(권한 없음) 응답을 반환합니다.
사용자 정보 반환: 세션에 저장된 사용자 정보를 반환합니다.

 

경로는 

// 기관회원 라우트
app.post("/register", register);
app.post("/login", login);
app.post("/logout", logout);
app.get("/user", requireAuth, getUserInfo);

 

이런식으로 해주면 됩니다.

다음에는 관리자(전문가) 백엔드 코드를 다뤄보겠습니다.

마찬가지로 유저와 같은 부분이니까 어렵지 않을겁니다.

 

import express from "express";
import bcrypt from "bcrypt";
import pool from "../db/connection.js"; // DB 연결

const router = express.Router();

// 🔹 전문가 회원가입
const registerExpert = async (req, res) => {
  const {
    email,
    name,
    institution_name,
    ofcps,
    phone_number,
    major_carrea,
    password,
  } = req.body;

  if (
    !email ||
    !name ||
    !institution_name ||
    !ofcps ||
    !phone_number ||
    !password
  ) {
    return res
      .status(400)
      .json({ resultCode: "F-1", msg: "필수 입력 값이 누락되었습니다." });
  }

  try {
    // 이메일 중복 확인
    const [existingUser] = await pool.query(
      "SELECT * FROM expert WHERE email = ?",
      [email]
    );
    if (existingUser.length > 0) {
      return res
        .status(400)
        .json({ resultCode: "F-2", msg: "이미 가입된 이메일입니다." });
    }

    // 비밀번호 해싱 후 저장
    const hashedPassword = await bcrypt.hash(password, 10);
    await pool.query(
      `INSERT INTO expert (name, institution_name, ofcps, phone_number, email, major_carrea, password) 
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [
        name,
        institution_name,
        ofcps,
        phone_number,
        email,
        major_carrea,
        hashedPassword,
      ]
    );

    const [newUser] = await pool.query(
      "SELECT id, name, email FROM expert WHERE email = ?",
      [email]
    );

    res
      .status(201)
      .json({ resultCode: "S-1", msg: "회원가입 성공", data: newUser[0] });
  } catch (error) {
    console.error("회원가입 오류:", error);
    res
      .status(500)
      .json({ resultCode: "F-1", msg: "서버 에러 발생", error: error.message });
  }
};

const loginExpert = async (req, res) => {
  const { email, password } = req.body;

  if (!email || !password) {
    return res
      .status(400)
      .json({ resultCode: "F-1", msg: "이메일과 비밀번호를 입력해주세요." });
  }

  try {
    console.log("🔍 [EXPERT LOGIN] 로그인 시도 이메일:", email); // ✅ 디버깅 로그 추가
    const [rows] = await pool.query("SELECT * FROM expert WHERE email = ?", [
      email,
    ]);

    if (!rows || rows.length === 0) {
      console.log("⚠️ [EXPERT LOGIN] 이메일을 찾을 수 없음:", email); // ✅ 디버깅 로그 추가
      return res.status(400).json({
        resultCode: "F-2",
        msg: "이메일 또는 비밀번호가 잘못되었습니다.",
      });
    }

    const expert = rows[0];
    console.log("✅ [EXPERT LOGIN] 찾은 전문가 데이터:", expert); // ✅ 디버깅 로그 추가

    // 비밀번호 확인
    const isMatch = await bcrypt.compare(password, expert.password);
    if (!isMatch) {
      console.log("❌ [EXPERT LOGIN] 비밀번호 불일치:", email); // ✅ 디버깅 로그 추가
      return res.status(400).json({
        resultCode: "F-2",
        msg: "이메일 또는 비밀번호가 잘못되었습니다.",
      });
    }

    // 세션 저장
    req.session.expert = {
      id: expert.id,
      email: expert.email,
      name: expert.name,
      member_type: "expert",
    };
    console.log("✅ [EXPERT LOGIN] 세션 저장 완료:", req.session.expert); // ✅ 디버깅 로그 추가

    res.status(200).json({
      resultCode: "S-1",
      msg: "로그인 성공",
      data: req.session.expert,
    });
  } catch (error) {
    console.error("❌ [EXPERT LOGIN] 로그인 오류:", error);
    res
      .status(500)
      .json({ resultCode: "F-1", msg: "서버 에러 발생", error: error.message });
  }
};
// 🔹 전문가 로그아웃
const logoutExpert = (req, res) => {
  req.session.destroy((err) => {
    if (err) {
      return res.status(500).json({ resultCode: "F-1", msg: "로그아웃 실패" });
    }
    res.clearCookie("connect.sid");
    res.status(200).json({ resultCode: "S-1", msg: "로그아웃 성공" });
  });
};

// 🔹 전문가 정보 가져오기 (로그인 상태 유지)
const getExpertInfo = (req, res) => {
  if (!req.session || !req.session.expert) {
    return res
      .status(401)
      .json({ resultCode: "F-1", msg: "로그인이 필요합니다." });
  }
  res.status(200).json({ resultCode: "S-1", expert: req.session.expert });
};