python-api/docs/DATABASE.md

# 데이터베이스 설계 문서

## 1. 개요

| DB | 용도 | 연결 방식 |
|----|------|----------|
| **MariaDB** | 사용자, 인증, 디바이스, 알림 규칙, 시스템 설정 | SQLModel + SQLAlchemy (async, aiomysql) |
| **MongoDB** | 디바이스 로그, 텔레메트리, 분석 결과, 알림 | Beanie + Motor (async) |
| **Redis** | 캐싱, 세션, 속도 제한, Celery 브로커 | redis-py (async, hiredis) |

---

## 2. MariaDB 스키마

### 2.1 users

사용자 계정 정보.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK, AUTO_INCREMENT | |
| email | VARCHAR(255) | UNIQUE, INDEX | 로그인 ID |
| hashed_password | VARCHAR(255) | NOT NULL | bcrypt 해시 |
| role | VARCHAR(20) | DEFAULT 'user' | superadmin/admin/manager/user/device |
| is_active | BOOLEAN | DEFAULT TRUE | 계정 활성 여부 |
| is_verified | BOOLEAN | DEFAULT FALSE | 이메일 인증 여부 |
| last_login_at | DATETIME | NULLABLE | 마지막 로그인 |
| is_deleted | BOOLEAN | DEFAULT FALSE | 소프트 삭제 |
| deleted_at | DATETIME | NULLABLE | 삭제 시각 |
| created_at | DATETIME | server_default=NOW() | |
| updated_at | DATETIME | onupdate=NOW() | |

### 2.2 user_profiles

사용자 프로필 (1:1).

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| user_id | INT | FK→users.id, UNIQUE | |
| full_name | VARCHAR(100) | | 이름 |
| phone | VARCHAR(20) | | 전화번호 |
| organization | VARCHAR(100) | | 소속 |
| avatar_url | VARCHAR(500) | | 프로필 이미지 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.3 refresh_tokens

리프레시 토큰 저장소. 토큰 순환(rotation) 방식 사용.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| user_id | INT | FK→users.id, INDEX | |
| token | VARCHAR(500) | UNIQUE, INDEX | JWT 리프레시 토큰 |
| expires_at | DATETIME | NOT NULL | 만료 시각 |
| is_revoked | BOOLEAN | DEFAULT FALSE | 폐기 여부 |
| device_info | VARCHAR(255) | | 접속 디바이스 정보 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.4 oauth_accounts

소셜 로그인 연동 계정.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| user_id | INT | FK→users.id, INDEX | |
| provider | VARCHAR(50) | | google/kakao/naver |
| provider_user_id | VARCHAR(255) | | 제공자 사용자 ID |
| access_token | VARCHAR(500) | | OAuth 액세스 토큰 |
| refresh_token | VARCHAR(500) | | OAuth 리프레시 토큰 |
| expires_at | DATETIME | NULLABLE | 토큰 만료 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.5 devices

IoT 디바이스 정보.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| device_uid | VARCHAR(100) | UNIQUE, INDEX | 디바이스 고유 식별자 |
| name | VARCHAR(100) | | 디바이스 이름 |
| device_type | VARCHAR(50) | | 센서 유형 (temperature, humidity 등) |
| status | VARCHAR(20) | DEFAULT 'offline' | online/offline/error/maintenance |
| firmware_version | VARCHAR(50) | | 펌웨어 버전 |
| ip_address | VARCHAR(45) | | IPv4/IPv6 |
| group_id | INT | FK→device_groups.id, NULLABLE | |
| owner_id | INT | FK→users.id, NULLABLE | |
| last_seen_at | DATETIME | NULLABLE | 마지막 통신 시각 |
| metadata_json | VARCHAR(2000) | DEFAULT '{}' | 추가 메타데이터 |
| is_deleted | BOOLEAN | DEFAULT FALSE | |
| deleted_at | DATETIME | NULLABLE | |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.6 device_groups

디바이스 그룹 (논리적 분류).

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| name | VARCHAR(100) | UNIQUE | 그룹명 |
| description | VARCHAR(500) | | 설명 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.7 alert_rules

알림 발생 조건 규칙.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| name | VARCHAR(100) | | 규칙명 |
| description | VARCHAR(500) | | 설명 |
| metric | VARCHAR(100) | | 감시 메트릭 (temperature, humidity 등) |
| condition | VARCHAR(50) | | 조건 (gt, lt, eq, gte, lte) |
| threshold | FLOAT | | 임계값 |
| severity | VARCHAR(20) | DEFAULT 'warning' | critical/warning/info |
| is_enabled | BOOLEAN | DEFAULT TRUE | |
| device_group_id | INT | FK→device_groups.id, NULLABLE | 대상 그룹 |
| created_by | INT | FK→users.id, NULLABLE | 생성자 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.8 alerts

발생한 알림 이력.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| rule_id | INT | FK→alert_rules.id, NULLABLE | 원인 규칙 |
| device_id | INT | FK→devices.id, NULLABLE | 대상 디바이스 |
| severity | VARCHAR(20) | | critical/warning/info |
| message | VARCHAR(500) | | 알림 메시지 |
| is_acknowledged | BOOLEAN | DEFAULT FALSE | 확인 여부 |
| acknowledged_by | INT | FK→users.id, NULLABLE | 확인한 사용자 |
| acknowledged_at | DATETIME | NULLABLE | 확인 시각 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.9 system_configs

시스템 설정 키-값 저장소.

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| key | VARCHAR(100) | UNIQUE, INDEX | 설정 키 |
| value | VARCHAR(2000) | | 설정 값 |
| description | VARCHAR(500) | | 설명 |
| is_secret | BOOLEAN | DEFAULT FALSE | 비밀 값 여부 |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

### 2.10 audit_logs

감사 로그 (변경 이력 추적).

| 컬럼 | 타입 | 제약 | 설명 |
|------|------|------|------|
| id | INT | PK | |
| user_id | INT | FK→users.id, NULLABLE | 행위자 |
| action | VARCHAR(100) | | 액션 (create, update, delete, login 등) |
| resource_type | VARCHAR(50) | | 대상 리소스 타입 |
| resource_id | VARCHAR(50) | | 대상 리소스 ID |
| details | VARCHAR(2000) | DEFAULT '{}' | 변경 상세 (JSON) |
| ip_address | VARCHAR(45) | | 요청 IP |
| created_at | DATETIME | | |
| updated_at | DATETIME | | |

---

## 3. MariaDB ER 다이어그램

```
users ──────────┬──── 1:1 ──── user_profiles
                │
                ├──── 1:N ──── refresh_tokens
                │
                ├──── 1:N ──── oauth_accounts
                │
                ├──── 1:N ──── devices (owner_id)
                │
                ├──── 1:N ──── alert_rules (created_by)
                │
                ├──── 1:N ──── alerts (acknowledged_by)
                │
                └──── 1:N ──── audit_logs (user_id)

device_groups ──┬──── 1:N ──── devices (group_id)
                │
                └──── 1:N ──── alert_rules (device_group_id)

alert_rules ────┬──── 1:N ──── alerts (rule_id)

devices ────────┴──── 1:N ──── alerts (device_id)
```

---

## 4. MongoDB 컬렉션

### 4.1 device_logs

디바이스 이벤트 로그. TTL 인덱스로 90일 후 자동 삭제.

```json
{
  "_id": "ObjectId",
  "device_id": "sensor-temp-001",
  "event_type": "status_change",
  "payload": {
    "status": "online",
    "reason": "boot"
  },
  "ip_address": "192.168.1.100",
  "timestamp": "2025-01-15T12:00:00Z"
}
```

**인덱스:**
- `device_id` (단일)
- `event_type` (단일)
- `timestamp` (내림차순)

### 4.2 telemetry_data

디바이스 센서 측정 데이터 (시계열).

```json
{
  "_id": "ObjectId",
  "device_id": "sensor-temp-001",
  "metrics": {
    "temperature": 23.5,
    "humidity": 45.2,
    "pressure": 1013.25
  },
  "timestamp": "2025-01-15T12:05:00Z"
}
```

**인덱스:**
- `device_id` (단일)
- `timestamp` (내림차순)
- `(device_id, timestamp)` (복합, 범위 쿼리 최적화)

### 4.3 analytics_results

분석 수행 결과 저장.

```json
{
  "_id": "ObjectId",
  "analysis_type": "daily_telemetry",
  "parameters": {
    "date": "2025-01-14"
  },
  "result": {
    "count": 1440,
    "avg_value": 23.8
  },
  "device_id": "sensor-temp-001",
  "period_start": "2025-01-14T00:00:00Z",
  "period_end": "2025-01-15T00:00:00Z",
  "created_at": "2025-01-15T01:05:00Z"
}
```

**인덱스:**
- `analysis_type` (단일)
- `device_id` (단일)
- `created_at` (내림차순)

### 4.4 notifications

사용자별 알림 메시지.

```json
{
  "_id": "ObjectId",
  "user_id": 1,
  "title": "디바이스 오프라인 알림",
  "message": "sensor-temp-001이 오프라인 상태입니다.",
  "notification_type": "warning",
  "is_read": false,
  "read_at": null,
  "created_at": "2025-01-15T12:10:00Z"
}
```

**인덱스:**
- `user_id` (단일)
- `(user_id, is_read)` (복합, 읽지 않은 알림 조회)
- `created_at` (내림차순)

---

## 5. Redis 사용 패턴

| 용도 | 키 패턴 | TTL | 설명 |
|------|---------|-----|------|
| 속도 제한 | `rate_limit:{ip}` | 60초 | IP별 요청 카운터 |
| Celery 브로커 | redis://...6379/1 | - | 태스크 큐 |
| Celery 결과 | redis://...6379/2 | - | 태스크 결과 저장 |

---

## 6. 마이그레이션

Alembic으로 MariaDB 스키마를 관리한다.

```bash
# 마이그레이션 생성
alembic revision --autogenerate -m "description"

# 마이그레이션 적용
alembic upgrade head

# 롤백
alembic downgrade -1

# 현재 리비전 확인
alembic current

# 히스토리 확인
alembic history
```

MongoDB는 스키마리스이므로 별도 마이그레이션이 불필요하다. 인덱스는 Beanie 모델의 `Settings.indexes`로 앱 시작 시 자동 생성된다.