# DB 스키마 v1 초안

## 1. 기준

이 문서는 개발 착수 전 확정할 DB v1 초안이다.

기본 DB는 MariaDB 또는 MySQL을 기준으로 한다.

## 2. users

```sql
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(100) NULL,
  position VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(150) NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'user',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
```

## 3. user_aliases

```sql
CREATE TABLE user_aliases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  alias VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_user_aliases_user_id (user_id),
  UNIQUE KEY uq_user_aliases_alias (alias)
);
```

## 4. raw_inputs

```sql
CREATE TABLE raw_inputs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  input_type VARCHAR(20) NOT NULL,
  original_text TEXT NULL,
  transcribed_text TEXT NULL,
  audio_file_path VARCHAR(255) NULL,
  language VARCHAR(20) NOT NULL DEFAULT 'ko',
  submitted_by BIGINT UNSIGNED NULL,
  submitted_at DATETIME NOT NULL,
  processing_status VARCHAR(30) NOT NULL DEFAULT 'received',
  error_message TEXT NULL,
  INDEX idx_raw_inputs_submitted_at (submitted_at),
  INDEX idx_raw_inputs_status (processing_status)
);
```

## 5. tasks

```sql
CREATE TABLE tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  assignee_id BIGINT UNSIGNED NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'new',
  priority VARCHAR(30) NOT NULL DEFAULT 'normal',
  due_date DATE NULL,
  source_input_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  reopened_at DATETIME NULL,
  is_review_required TINYINT(1) NOT NULL DEFAULT 0,
  review_reason TEXT NULL,
  last_update_summary TEXT NULL,
  INDEX idx_tasks_assignee_id (assignee_id),
  INDEX idx_tasks_status (status),
  INDEX idx_tasks_due_date (due_date),
  INDEX idx_tasks_updated_at (updated_at),
  INDEX idx_tasks_review_required (is_review_required)
);
```

## 6. task_updates

```sql
CREATE TABLE task_updates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT UNSIGNED NOT NULL,
  raw_input_id BIGINT UNSIGNED NULL,
  update_type VARCHAR(30) NOT NULL,
  previous_status VARCHAR(30) NULL,
  new_status VARCHAR(30) NULL,
  note TEXT NULL,
  change_json JSON NULL,
  applied_by_type VARCHAR(20) NOT NULL DEFAULT 'user',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_task_updates_task_id (task_id),
  INDEX idx_task_updates_created_at (created_at)
);
```

## 7. ai_analysis_logs

```sql
CREATE TABLE ai_analysis_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  raw_input_id BIGINT UNSIGNED NOT NULL,
  intent VARCHAR(50) NOT NULL,
  confidence DECIMAL(5,4) NOT NULL DEFAULT 0,
  matched_task_id BIGINT UNSIGNED NULL,
  matched_user_id BIGINT UNSIGNED NULL,
  extracted_due_date DATE NULL,
  extracted_priority VARCHAR(30) NULL,
  extracted_status VARCHAR(30) NULL,
  summary TEXT NULL,
  analysis_json JSON NOT NULL,
  needs_human_review TINYINT(1) NOT NULL DEFAULT 0,
  model_name VARCHAR(100) NULL,
  prompt_version VARCHAR(50) NOT NULL DEFAULT 'v1',
  schema_version VARCHAR(50) NOT NULL DEFAULT 'v1',
  created_at DATETIME NOT NULL,
  INDEX idx_ai_analysis_raw_input_id (raw_input_id),
  INDEX idx_ai_analysis_matched_task_id (matched_task_id),
  INDEX idx_ai_analysis_review (needs_human_review)
);
```

## 8. daily_reports

```sql
CREATE TABLE daily_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_date DATE NOT NULL,
  report_type VARCHAR(30) NOT NULL,
  target_user_id BIGINT UNSIGNED NULL,
  title VARCHAR(200) NOT NULL,
  body_text MEDIUMTEXT NOT NULL,
  body_json JSON NOT NULL,
  timezone VARCHAR(50) NOT NULL DEFAULT 'Asia/Seoul',
  status VARCHAR(30) NOT NULL DEFAULT 'generated',
  generated_at DATETIME NOT NULL,
  INDEX idx_daily_reports_date_type (report_date, report_type),
  INDEX idx_daily_reports_target_user (target_user_id)
);
```

## 9. ai_usage_logs

```sql
CREATE TABLE ai_usage_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  feature_name VARCHAR(50) NOT NULL,
  model_name VARCHAR(100) NULL,
  prompt_version VARCHAR(50) NULL,
  input_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  output_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  total_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  success TINYINT(1) NOT NULL DEFAULT 1,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_ai_usage_feature_created (feature_name, created_at),
  INDEX idx_ai_usage_model_created (model_name, created_at)
);
```

## 10. v1 상태값

### tasks.status

- `new`
- `in_progress`
- `waiting`
- `review_required`
- `completed`
- `on_hold`
- `canceled`

### tasks.priority

- `low`
- `normal`
- `high`
- `urgent`

### raw_inputs.processing_status

- `received`
- `transcribed`
- `analyzed`
- `applied`
- `review_required`
- `failed`

### task_updates.applied_by_type

- `user`
- `ai`
- `system`

### daily_reports.status

- `generated`
- `failed`
- `regenerated`