ERD

최초 작성: 2026-10-08 | 최종 업데이트: 2026-10-14
API Spec v1 및 ADR-004(기술 스택 확정) 기반으로 전면 재작성

---

인증·사용자

erDiagram
    USER {
        bigint id PK
        string name
        string email
        string password "nullable: 소셜 가입 계정"
        string phone "nullable"
        string publicCode "nullable: GUARDIAN·SUPPORT_WORKER만, P- 또는 S- + 8자"
        enum role "GUARDIAN | SUPPORT_WORKER | INSTITUTION"
        datetime createdAt
    }

    CONSENT {
        bigint id PK
        bigint userId FK
        boolean basicInfo
        boolean sensitiveInfo
        boolean caregiverShare
        boolean marketing "기본 false"
        string termsVersion
        datetime agreedAt
    }

    REFRESH_TOKEN {
        bigint id PK
        bigint userId FK
        string tokenHash
        datetime expiresAt "30일"
        datetime revokedAt "nullable: 로그아웃·강제 만료"
        datetime createdAt
    }

    ORGANIZATION {
        bigint id PK
        bigint ownerId FK "INSTITUTION USER"
        string name
        string organizationCode "고유, AA-999 형식"
        datetime createdAt
    }

    ORGANIZATION_MEMBER {
        bigint id PK
        bigint organizationId FK
        bigint userId FK "SUPPORT_WORKER"
        enum status "ACTIVE | INACTIVE"
        datetime joinedAt
    }

    USER ||--|| CONSENT : ""
    USER ||--o{ REFRESH_TOKEN : ""
    USER ||--o| ORGANIZATION : "기관만"
    ORGANIZATION ||--o{ ORGANIZATION_MEMBER : ""
    USER ||--o{ ORGANIZATION_MEMBER : "지원사만"

---

당사자·보호자·배정

erDiagram
    CARE_RECIPIENT {
        bigint id PK
        string careRecipientNumber "P-XXXXXXXX, 고유"
        string name "최대 50자"
        date birthDate
        enum gender "FEMALE | MALE | NOT_SPECIFIED, nullable"
        string disabilityGrade "nullable, 최대 20자"
        string otherDisabilityType "nullable"
        datetime latestRecordAt "nullable"
        datetime createdAt
    }

    CARE_RECIPIENT_DISABILITY {
        bigint id PK
        bigint careRecipientId FK
        enum disabilityType "INTELLECTUAL | AUTISM | OTHER"
    }

    CARE_GUARDIAN {
        bigint id PK
        bigint careRecipientId FK
        bigint guardianId FK
        enum type "PRIMARY | SECONDARY"
        enum status "INVITED | READY | ACTIVE, nullable: SECONDARY만"
        string invitationToken "nullable: SECONDARY INVITED 상태"
        datetime expiresAt "nullable"
        datetime createdAt
    }

    ORGANIZATION_RECIPIENT_LINK {
        bigint id PK
        bigint organizationId FK
        bigint careRecipientId FK
        enum status "PENDING | ACCEPTED | REJECTED"
        datetime createdAt
    }

    CARE_ASSIGNMENT {
        bigint id PK
        bigint organizationId FK
        bigint careRecipientId FK
        bigint supportWorkerId FK
        string idempotencyKey "중복 배정 방지"
        date startDate
        date endDate "nullable"
        datetime endedAt "nullable: 중도 종료"
        datetime createdAt
    }

    CARE_RECIPIENT ||--o{ CARE_RECIPIENT_DISABILITY : ""
    CARE_RECIPIENT ||--o{ CARE_GUARDIAN : ""
    CARE_RECIPIENT ||--o{ ORGANIZATION_RECIPIENT_LINK : ""
    CARE_RECIPIENT ||--o{ CARE_ASSIGNMENT : ""

---

생활기록·돌봄정보

erDiagram
    DAILY_RECORD {
        bigint id PK
        bigint careRecipientId FK
        bigint authorId FK
        text content "최대 500자"
        enum category "COMMUNICATION | LIFESTYLE | MEAL | PREFERENCE | CHALLENGING_BEHAVIOR | HEALTH | EMERGENCY | OTHER"
        datetime recordedAt
        boolean pinned "기본 false"
        bigint recordRequestId FK "nullable"
        datetime createdAt
        datetime updatedAt
        datetime deletedAt "nullable: soft delete, 30일 복구 가능"
    }

    RECORD_MEDIA {
        bigint id PK
        bigint careRecipientId FK
        bigint uploaderId FK
        string fileName
        enum mediaType "IMAGE | VIDEO | AUDIO"
        bigint sizeBytes "최대 50MB"
        string s3Key
        datetime createdAt
    }

    RECORD_MEDIA_LINK {
        bigint recordId FK
        bigint mediaId FK
    }

    RECORD_REVISION {
        bigint id PK
        bigint recordId FK
        bigint changedById FK
        text content
        enum category "DAILY_RECORD.category와 동일"
        datetime recordedAt
        int revision
        datetime changedAt
    }

    RECORD_SUGGESTION {
        bigint id PK
        bigint recordId FK
        bigint suggestedById FK
        text proposedContent
        enum proposedCategory "DAILY_RECORD.category와 동일"
        text reason "nullable"
        enum status "PENDING | APPROVED | REJECTED"
        datetime createdAt
    }

    CARE_INFO {
        bigint id PK
        bigint careRecipientId FK
        enum category "COMMUNICATION | PREFERENCE | LIFESTYLE | HEALTH_MEDICATION | PRECAUTION | EMERGENCY"
        text content "최대 3000자, nullable"
        datetime updatedAt "nullable"
    }

    DAILY_RECORD ||--o{ RECORD_MEDIA_LINK : ""
    RECORD_MEDIA ||--o{ RECORD_MEDIA_LINK : ""
    DAILY_RECORD ||--o{ RECORD_REVISION : ""
    DAILY_RECORD ||--o{ RECORD_SUGGESTION : ""
    CARE_RECIPIENT ||--o{ CARE_INFO : ""

---

요청·알림·접근 이력

erDiagram
    INFORMATION_REQUEST {
        bigint id PK
        bigint careRecipientId FK
        bigint requestedById FK "SUPPORT_WORKER"
        text message "최대 1000자"
        text reply "nullable"
        enum status "SUBMITTED | FORWARDED | ANSWERED | CLOSED"
        datetime createdAt
        datetime updatedAt
    }

    RECORD_REQUEST {
        bigint id PK
        bigint organizationId FK
        bigint careRecipientId FK
        bigint supportWorkerId FK
        string idempotencyKey
        text message
        bigint completedRecordId FK "nullable"
        enum status "OPEN | COMPLETED | CANCELLED"
        datetime createdAt
    }

    NOTIFICATION {
        bigint id PK
        bigint userId FK
        enum type "RECORD_CREATED | RECORD_UPDATED | ASSIGNMENT_CHANGED | SUGGESTION_RECEIVED | SUGGESTION_DECIDED | INFORMATION_REQUEST_RECEIVED | RECORD_REQUEST_RECEIVED | RECORD_FORWARDED | ORGANIZATION_LINK_REQUESTED"
        text message
        boolean read "기본 false"
        datetime createdAt
    }

    ACCESS_LOG {
        bigint id PK
        bigint actorId FK
        bigint careRecipientId FK
        enum action "READ | CREATE | UPDATE | DELETE"
        string resourceType
        bigint resourceId
        datetime occurredAt
    }

---

핵심 enum 요약

테이블	필드	값
USER	role	GUARDIAN | SUPPORT_WORKER | INSTITUTION
CARE_RECIPIENT	gender	FEMALE | MALE | NOT_SPECIFIED
CARE_RECIPIENT_DISABILITY	disabilityType	INTELLECTUAL | AUTISM | OTHER
DAILY_RECORD	category	COMMUNICATION | LIFESTYLE | MEAL | PREFERENCE | CHALLENGING_BEHAVIOR | HEALTH | EMERGENCY | OTHER
CARE_INFO	category	COMMUNICATION | PREFERENCE | LIFESTYLE | HEALTH_MEDICATION | PRECAUTION | EMERGENCY
CARE_GUARDIAN	status	INVITED | READY | ACTIVE
CARE_ASSIGNMENT	—	startDate/endDate로 기간 관리 (N:M temporal)
RECORD_SUGGESTION	status	PENDING | APPROVED | REJECTED
INFORMATION_REQUEST	status	SUBMITTED | FORWARDED | ANSWERED | CLOSED
RECORD_REQUEST	status	OPEN | COMPLETED | CANCELLED

---

이전 미결 사항 → 해소

항목	결정
돌봄 제공자 ↔ 피돌봄자 관계 1:N vs N:M	CARE_ASSIGNMENT의 startDate/endDate로 N:M temporal 관리
CARE_INFO.content JSON vs 분리	text 단일 컬럼 (카테고리 6개 행으로 분리)
이미지/파일 첨부	RECORD_MEDIA 테이블 추가, AWS S3 저장 (ADR-004)

---

참고

• USER.publicCode: P- 접두사는 GUARDIAN, S- 접두사는 SUPPORT_WORKER
• ORGANIZATION.organizationCode: AA-999 형식 (영문 2자 + 숫자 3자)
• REFRESH_TOKEN.tokenHash: 원문 토큰을 해시하여 저장
• DAILY_RECORD 삭제는 soft delete — deletedAt 기록 후 30일 이내 복구 가능
• RECORD_MEDIA는 기록에 첨부되기 전 단계(업로드 직후)에도 독립 존재 가능