식품영양관리
테이블 (10)
account_login_history (로그인 이력)
- login_history_id (로그인 이력 고유 식별자) — BIGINT — 기본 키 — NOT NULL — 로그인 시도 레코드 식별자
- account_id (계정 고유 식별자) — BIGINT — 외래 키 → accounts.account_id — NOT NULL — 계정 고유 식별자 (내부 서로게이트키)
- login_at (로그인 시도 일시) — DATETIME — NOT NULL — 로그인 시도 일시
- login_ip (로그인 IP) — VARCHAR(45) — NOT NULL — 접속 IP (IPv4/IPv6) - 암호화하지 않음(보안 모니터링/이상탐지 쿼리 성능을 위해 평문 유지)
- user_agent (접속 기기/브라우저 정보) — VARCHAR(255) — 접속 기기/브라우저 정보 (User-Agent)
- login_result (로그인 결과) — ENUM('SUCCESS', 'FAILED') — NOT NULL — 로그인 성공/실패 - Firebase Auth 도입 시 FAILED는 비밀번호 오류가 아니라 "우리 백엔드의 Firebase ID 토큰 검증 실패"를 의미(비밀번호 자체의 성공/실패 시도 이력은 Firebase 콘…
- logout_at (로그아웃 일시) — DATETIME — 로그아웃 일시 (세션 종료 추적용, 없으면 NULL)
account_profiles (개인민감 정보)
- account_id (계정 고유 식별자) — BIGINT — 외래 키 → accounts.account_id — NOT NULL — 계정 고유 식별자 (내부 서로게이트키)
- real_name_encrypted (실명 암호화 저장) — VARBINARY(255) — NOT NULL — 실명 (암호화 저장) - 동의서 서명, 법적 문서용. 화면 표시는 accounts.display_name 사용
- phone_number_encrypted (연락처 암호화저장) — VARBINARY(255) — NOT NULL — 연락처 (암호화 저장) - 본인인증, 이상반응 긴급알림 발송, 계정복구용
- updated_at (최종 수정일시) — DATETIME — NOT NULL — 최종 수정일시
account_subject_access (계정-실험군 접근권한)
- access_id (접근고유 식별자) — BIGINT — 기본 키 — NOT NULL — 접근권한 레코드 식별자
- relationship_type (관계 유형) — ENUM('GUARDIAN', 'SELF') — NOT NULL — 관계 유형: GUARDIAN=보호자가 대리 관리(아동/노인 등 스마트폰 사용이 서툰 피험자 전연령 대상, 부모-자녀 관계로 한정하지 않음), SELF=피험자 본인이 자신에게 접근. 연령(subjects.life_s…
- is_primary_guardian (대표 보호자) — BOOLEAN — NOT NULL — 가족 내 대표 보호자 여부(업무지시서 2장) - 결제/대표 알림 수신 대상. relationship_type=GUARDIAN에서만 의미 있음, 같은 subject_id에 TRUE는 1명만 권장(앱 레벨 검증)
- granted_at (접근권한 부여일시) — DATETIME — NOT NULL — 접근권한 부여일시
- revoked_at (접근권한 회수일시) — DATETIME — 접근권한 회수일시 (NULL이면 현재 유효)
- account_id (계정 고유 식별자) — BIGINT — 외래 키 → accounts.account_id — NOT NULL — 계정 고유 식별자 (내부 서로게이트키)
- subject_id (대상자 고유 식별자) — BIGINT — 외래 키 → subjects.subject_id — NOT NULL — 피험자 내부 서로게이트키 - 절대 화면/API/리포트에 그대로 노출 금지
accounts (계정 정보)
- account_id (계정 고유 식별자) — BIGINT — 기본 키 — NOT NULL — 계정 고유 식별자 (내부 서로게이트키)
- account_type (계정 유형) — ENUM('GUARDIAN', 'FACILITY_STAFF', 'ADMIN', 'SUBJECT_SELF') — NOT NULL — 계정 유형: GUARDIAN=보호자, FACILITY_STAFF=담당자(교사/케이스매니저), ADMIN=관리자, SUBJECT_SELF=성인 피험자 본인
- admin_level (관리자 등급) — ENUM('GENERAL', 'SUPERVISOR') — 관리자 등급 (account_type=ADMIN 일 때만 값 존재). SUPERVISOR=총괄관리자, 마스터 설정화면 등 승인 권한 보유
- login_id (로그인 아이디) — VARCHAR(100) — NOT NULL — 로그인 아이디 (이메일/휴대폰 등 실제 로그인에 쓰는 식별자)
- password_hash (비밀번호 해시값) — VARCHAR(255) — NOT NULL — 비밀번호 해시값 (평문 저장 금지)
- phonenumber_hash (전화번호 해시값) — VARCHAR(255)
- name (이름) — VARCHAR(100) — NOT NULL — 화면에 노출되는 별칭/이름 (실명이 아니어도 됨 - PII는 별도 관리)
- status (계정 상태) — ENUM('ACTIVE', 'SUSPENDED', 'WITHDRAWN') — NOT NULL — 계정 상태: ACTIVE=정상, SUSPENDED=일시정지, WITHDRAWN=탈퇴
- last_login_at (최근 로그인 시간) — DATETIME — 최근 로그인 성공 일시 (화면 표시용 요약값 - 전체 이력은 account_login_history 참조)
- last_login_ip (최근 로그인 IP) — VARCHAR(45) — 최근 로그인 IP (IPv6 포함 45자) - 화면 표시용 요약값
- created_at (계정 생성일시) — DATETIME — NOT NULL — 계정 생성일시
- updated_at (최종 수정일시) — DATETIME — NOT NULL — 최종 수정일시
campaign_staff (캠페인 담당자 정보)
- campaign_staff_id (담당자 고유 식별자) — BIGINT — 기본 키 — NOT NULL — 캠페인-담당자 배정 레코드 식별자
- campaign_id (캠페인 고유 식별자) — BIGINT — 외래 키 → campaigns.campaign_id — NOT NULL — 캠페인 고유 식별자 - 담당자/피험자/가족을 묶는 허브 PK
- account_id (계정 고유 식별자) — BIGINT — 외래 키 → accounts.account_id — NOT NULL — 계정 고유 식별자 (내부 서로게이트키)
- role_in_campaign (역할) — ENUM('FACILITY_TEACHER', 'CASE_MANAGER', 'SUPERVISOR') — NOT NULL — 캠페인 내 역할: FACILITY_TEACHER=현장교사, CASE_MANAGER=케이스매니저, SUPERVISOR=총괄관리자
- assigned_at (부여 날짜) — DATETIME — NOT NULL — 배정일시
- unassigned_at (권한 회수 날짜) — DATETIME — 배정 해제일시 (NULL이면 현재 담당 중 - 이 값이 NULL인 행만 유효한 접근권한으로 취급)
campaign_subjects
- campaign_subject_id (캠페인-피험자 참여 레코드 식별자) — BIGINT — 기본 키 — NOT NULL — 캠페인-피험자 참여 레코드 식별자 - 이후 모든 실데이터(설문응답/일별기록 등)가 참조할 진입점
- campaign_id (캠페인 고유 식별자) — BIGINT — 외래 키 → campaigns.campaign_id — NOT NULL — 캠페인 고유 식별자 - 담당자/피험자/가족을 묶는 허브 PK
- consent_status (동의 상태) — ENUM('PENDING', 'ACTIVE', 'WITHDRAWN', 'EXPIRED', 'COMPLETED') — NOT NULL — 동의 상태 (STD-2026-001 공통 상태코드 기준): PENDING=동의대기, ACTIVE=참여중, WITHDRAWN=철회, EXPIRED=기간만료, COMPLETED=정상종료
- enrolled_at (참여 일시) — DATETIME — NOT NULL — 캠페인 참여 등록일시
- withdrawn_at (철회 일시) — DATETIME — 참여 철회일시 (해당 시 consent_status=WITHDRAWN과 함께 기록)
- dissent_hold (본인의 거부권 행사 여부) — BOOLEAN — NOT NULL — 아동 본인의 거부권(Dissent) 행사 여부 - TRUE면 보호자 동의(consent_status=ACTIVE)와 무관하게 데이터 수집/화면 렌더링을 즉시 차단 (세부알고리즘정의서 7.2절)
- dissent_recorded_at (거부권 행사 기록 일시) — DATETIME — 거부권 행사가 기록된 일시
- baseline_survey_completed_at (최초 NQ 완료 일시) — DATETIME — 최초 전체 NQ 문항(14/21개) 1회 완료 일시. NULL이면 아직 초기 스크리닝 전 - 이 경우 앱은 라운드로빈이 아니라 전체 문항 일괄 입력 화면을 띄워야 함. 값이 채워진 이후부터 일별 알림장에서 라운드로…
campaigns (실증 정보)
- campaign_id (캠페인 고유 식별자) — BIGINT — 기본 키 — NOT NULL — 캠페인 고유 식별자 - 담당자/피험자/가족을 묶는 허브 PK
- campaign_code (캠페인 참여 코드) — VARCHAR(50) — NOT NULL — 성인 본인 또는 보호자가 앱에서 직접 입력하는 참여 코드 (예: DEMO_FREEZE_2026)
- campaign_name (캠페인 표시명) — VARCHAR(200) — NOT NULL — 캠페인 표시명
- start_date (실증 시작일) — DATE — NOT NULL — 캠페인(실증기간) 시작일
- end_date (실증 종료일) — DATE — 캠페인 종료일 (진행 중이면 NULL)
- status (캠페인 상태) — ENUM('PLANNED', 'ACTIVE', 'CLOSED') — NOT NULL — 캠페인 상태: PLANNED=예정, ACTIVE=진행중, CLOSED=종료
- target_enrollment_count (목표 참여 인원) — INT — 목표 참여 인원 (요구사항정의서 기초조사 항목 - 예: 초기 참여군 목표 20명). 대시보드 "목표 대비 진행률" 표시용
- created_at (생성일시) — DATETIME — NOT NULL — 캠페인 등록일시
families (가족 정보)
- family_id (가족 고유 식별자) — BIGINT — 기본 키 — NOT NULL — 가족 고유 식별자
- family_name (가족 명) — VARCHAR(100) — 가족 표시명 (선택 입력, 화면 그룹핑용 - 실제 접근권한 판단에는 사용하지 않음)
- family_code (가족 초대 코드) — VARCHAR(20) — 가족 초대 코드 - 이미 만들어진 가족에 다른 구성원(성인 자가등록자 포함)이 합류할 때 입력. campaign_code와 동일한 방식(코드 입력으로 합류)
- created_at (최초 생성일시) — DATETIME — NOT NULL — 가족 레코드 생성일시
subject_consents
- consent_id — BIGINT — 기본 키 — NOT NULL — 동의 이벤트 고유 일련번호
- campaign_subject_id — BIGINT — NOT NULL — 대상 캠페인 참여 건 FK (family_id/subject_id는 이를 통해 조회 - 원본 스펙의 두 FK를 대체)
- consent_version — VARCHAR(10) — NOT NULL — 동의서 문서 개정 버전 (예: v1.0, v1.1) - 버전이 바뀌면 재동의 이벤트가 새 행으로 추가됨
- consent_type — ENUM('SUBJECT', 'REPRESENTATIVE') — NOT NULL — SUBJECT=피험자 본인 동의, REPRESENTATIVE=보호자 대리 동의
- event_type — ENUM('CONSENT', 'WITHDRAWAL', 'DISSENT', 'DISSENT_LIFTED') — NOT NULL — 이벤트 종류: CONSENT=동의, WITHDRAWAL=철회, DISSENT=아동본인 거부권 행사, DISSENT_LIFTED=거부권 철회(재동의)
- consent_status — ENUM('PENDING', 'ACTIVE', 'WITHDRAWN', 'EXPIRED', 'COMPLETED') — NOT NULL — 이 이벤트 발생 직후의 상태 (STD-2026-001 공통 상태코드) - campaign_subjects.consent_status 캐시 갱신에 사용
- event_actor_account_id — BIGINT — NOT NULL — 이 동의/철회/거부 이벤트를 실제로 실행한 계정 - 보호자 본인 또는 성인 피험자 본인(SUBJECT_SELF). 아동 거부권은 대개 현장 담당자(FACILITY_STAFF)가 대리 기록
- agreed_at — DATETIME — 최초 자발적 동의 완료 일시 (event_type=CONSENT일 때)
- withdrawn_at — DATETIME — 철회 요청 일시 (event_type=WITHDRAWAL일 때)
- withdrawal_reason — TEXT — 철회/거부 사유 (서술형 또는 선택형 코드)
- explainer_name — VARCHAR(100) — 연구 설명자 명의 (시스템 자동 마킹 또는 연구원 ID)
- digital_signature — TEXT — 전자서명 검증 토큰 또는 서명 이미지 Base64 스트링
- access_ip — VARCHAR(45) — NOT NULL — 이벤트 진행 당시 접속 IP
- device_info — VARCHAR(255) — 접속 디바이스 메타데이터 (User-Agent 등)
- created_at — DATETIME — NOT NULL — 이 감사 레코드 자체의 생성일시 - 이후 절대 UPDATE하지 않음(append-only)
subjects (실험군 정보)
- subject_id (대상자 고유 식별자) — BIGINT — 기본 키 — NOT NULL — 피험자 내부 서로게이트키 - 절대 화면/API/리포트에 그대로 노출 금지
- subject_pseudo_code (비식별 코드) — VARCHAR(30) — NOT NULL — 화면/리포트/API에 노출되는 비식별 코드 (예: WH-2026-A7X4) - 실명 대신 사용
- birth_year_month (생년월일) — CHAR(7) — NOT NULL — 생년월(YYYY-MM) - 생년월일 원본 대신 저장, 만나이/생애주기 판별용
- gender (성별) — ENUM('M', 'F') — NOT NULL — 성별
- life_stage_group (생애주기 그룹) — ENUM('INFANT_CHILD', 'ADOLESCENT', 'ADULT', 'ELDERLY') — NOT NULL — 생애주기 그룹 - ADULT/ELDERLY는 본인 로그인(SUBJECT_SELF) 가능성 있음, 그 외는 보호자 대리입력 전제
- created_at (생성일시) — DATETIME — NOT NULL — 피험자 프로필 생성일시
- family_id (가족 고유 식별자) — BIGINT — 외래 키 → families.family_id — NOT NULL — 가족 고유 식별자