AURA ERD
Tables (19)
analysis_candidates
- candidate_id — BIGINT — Primary key — NOT NULL
- scan_job_id — BIGINT — Foreign key → scan_jobs.scan_job_id — NOT NULL
- item_id — BIGINT — Foreign key → scanned_items.item_id — NOT NULL
- category — ENUM('PROMOTION_MAIL', 'OLD_MAIL', 'DUPLICATE_FILE', 'OLD_DRIVE_FILE', 'LARGE_FILE', 'LOW_VALUE_ATTACHMENT', 'TEMP_OR_BACKUP', 'PROTECTED') — NOT NULL
- risk_level — ENUM('LOW', 'MEDIUM', 'HIGH') — NOT NULL
- priority_score — DECIMAL(5,2) — NOT NULL
- ghost_score — DECIMAL(5,2) — NOT NULL
- is_protected — TINYINT(1) — NOT NULL
- selection_status — ENUM('NONE', 'SELECTED', 'DESELECTED') — NOT NULL
- selection_version — INT — NOT NULL
- estimated_reclaim_bytes — BIGINT — NOT NULL
- ai_provider — VARCHAR(50)
- ai_model_name — VARCHAR(100)
- ai_confidence_score — DECIMAL(5,2)
- semantic_tags_json — JSON
- matched_conditions_json — JSON
- analyzed_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
announcement_reads
- read_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- announcement_id — BIGINT — Foreign key → announcements.announcement_id — NOT NULL
- read_at — DATETIME — NOT NULL
announcements
- announcement_id — BIGINT — Primary key — NOT NULL
- title — VARCHAR(200) — NOT NULL
- category — ENUM('POLICY', 'SERVICE', 'FEATURE') — NOT NULL
- content — TEXT — NOT NULL
- is_pinned — TINYINT(1) — NOT NULL
- published_at — DATETIME — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
carbon_saving_histories
- carbon_history_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id
- history_id — BIGINT — Foreign key → cleanup_histories.history_id — NOT NULL
- reclaimed_bytes — BIGINT — NOT NULL
- estimated_carbon_grams — DECIMAL(12,4) — NOT NULL
- formula_version — VARCHAR(30) — NOT NULL
- calculated_at — DATETIME — NOT NULL
cleanup_histories
- history_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id
- cleanup_job_id — BIGINT — Foreign key → cleanup_jobs.cleanup_job_id — NOT NULL
- scan_job_id — BIGINT — Foreign key → scan_jobs.scan_job_id
- action_type — ENUM('MOVE_TO_TRASH', 'PERMANENT_DELETE', 'EMPTY_TRASH') — NOT NULL
- cleaned_item_count — INT — NOT NULL
- reclaimed_bytes — BIGINT — NOT NULL
- remaining_drive_bytes — BIGINT
- completed_at — DATETIME — NOT NULL
cleanup_job_items
- cleanup_item_id — BIGINT — Primary key — NOT NULL
- cleanup_job_id — BIGINT — Foreign key → cleanup_jobs.cleanup_job_id — NOT NULL
- candidate_id — BIGINT — Foreign key → analysis_candidates.candidate_id
- item_id — BIGINT — Foreign key → scanned_items.item_id
- item_source — ENUM('GMAIL', 'DRIVE') — NOT NULL
- external_item_id — VARCHAR(255) — NOT NULL
- snapshot_item_key — VARCHAR(300) — NOT NULL
- snapshot_title — VARCHAR(500)
- snapshot_size_bytes — BIGINT — NOT NULL
- process_status — ENUM('PENDING', 'SUCCESS', 'FAILED', 'SKIPPED') — NOT NULL
- failure_reason — VARCHAR(500)
- processed_at — DATETIME
- created_at — DATETIME — NOT NULL
cleanup_jobs
- cleanup_job_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- scan_job_id — BIGINT — Foreign key → scan_jobs.scan_job_id
- action_type — ENUM('MOVE_TO_TRASH', 'PERMANENT_DELETE', 'EMPTY_TRASH') — NOT NULL
- job_status — ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'PARTIAL_FAILED', 'CANCELED') — NOT NULL
- selected_mail_count — INT — NOT NULL
- selected_drive_count — INT — NOT NULL
- total_selected_bytes — BIGINT — NOT NULL
- success_item_count — INT — NOT NULL
- failed_item_count — INT — NOT NULL
- progress_percent — DECIMAL(5,2) — NOT NULL
- approved_at — DATETIME — NOT NULL
- completed_at — DATETIME
- error_message — VARCHAR(500)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
fcm_tokens
- fcm_token_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- device_identifier — VARCHAR(150)
- fcm_token — VARCHAR(500) — NOT NULL
- is_active — TINYINT(1) — NOT NULL
- last_used_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
google_permissions
- permission_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- service_type — ENUM('GMAIL', 'DRIVE') — NOT NULL
- permission_status — ENUM('CONNECTED', 'DENIED', 'EXPIRED', 'RECONNECT_REQUIRED', 'DISCONNECTED') — NOT NULL
- scope_text — TEXT
- connected_at — DATETIME
- disconnected_at — DATETIME
- last_checked_at — DATETIME
- updated_at — DATETIME — NOT NULL
monthly_user_statistics
- monthly_stat_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id
- stat_owner_key — VARCHAR(80) — NOT NULL
- stat_year_month — CHAR(7) — NOT NULL
- scan_count — INT — NOT NULL
- cleanup_count — INT — NOT NULL
- trashed_item_count — INT — NOT NULL
- permanently_deleted_item_count — INT — NOT NULL
- reclaimed_bytes — BIGINT — NOT NULL
- estimated_carbon_grams — DECIMAL(12,4) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
notification_settings
- notification_setting_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- is_scan_complete_enabled — TINYINT(1) — NOT NULL
- is_scan_recommend_enabled — TINYINT(1) — NOT NULL
- updated_at — DATETIME — NOT NULL
notifications
- notification_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id
- scan_job_id — BIGINT
- cleanup_job_id — BIGINT
- notification_type — ENUM('SCAN_COMPLETED', 'SCAN_RECOMMENDED', 'DRIVE_STORAGE_LOW', 'CLEANUP_COMPLETED') — NOT NULL
- title — VARCHAR(150) — NOT NULL
- message — VARCHAR(500) — NOT NULL
- target_screen — ENUM('HOME', 'ANALYSIS_SUMMARY', 'STATISTICS', 'CLEANUP_RESULT') — NOT NULL
- is_read — TINYINT(1) — NOT NULL
- sent_at — DATETIME — NOT NULL
- read_at — DATETIME
oauth_tokens
- token_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- encrypted_refresh_token — VARCHAR(1000)
- access_token_expires_at — DATETIME
- scope_text — TEXT
- token_status — ENUM('VALID', 'EXPIRED', 'REVOKED', 'REFRESH_FAILED') — NOT NULL
- last_refreshed_at — DATETIME
- refresh_failed_at — DATETIME
- failure_reason — VARCHAR(500)
- revoked_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
scan_jobs
- scan_job_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id
- setting_id — BIGINT — Foreign key → scan_settings.setting_id
- job_status — ENUM('PENDING', 'SCANNING', 'ANALYZING', 'COMPLETED', 'FAILED', 'CANCELED', 'PARTIAL_FAILED') — NOT NULL
- scan_source — ENUM('MAIL', 'DRIVE_ALL', 'DRIVE_FOLDER', 'MAIL_AND_DRIVE') — NOT NULL
- condition_snapshot_json — JSON — NOT NULL
- progress_percent — DECIMAL(5,2) — NOT NULL
- mail_scanned_count — INT — NOT NULL
- drive_scanned_count — INT — NOT NULL
- candidate_count — INT — NOT NULL
- protected_count — INT — NOT NULL
- estimated_reclaim_bytes — BIGINT — NOT NULL
- error_message — VARCHAR(500)
- started_at — DATETIME
- completed_at — DATETIME
- canceled_at — DATETIME
- deleted_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
scan_settings
- setting_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- scan_source — ENUM('MAIL', 'DRIVE_ALL', 'DRIVE_FOLDER', 'MAIL_AND_DRIVE') — NOT NULL
- drive_folder_id — VARCHAR(255)
- include_subfolders — TINYINT(1) — NOT NULL
- last_opened_before_months — INT
- last_modified_before_months — INT
- created_before_months — INT
- exclude_recent_days — INT — NOT NULL
- include_keywords — JSON
- exclude_keywords — JSON
- file_extensions — JSON
- include_mail_attachment_size — TINYINT(1) — NOT NULL
- apply_recent_conditions — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
scanned_items
- item_id — BIGINT — Primary key — NOT NULL
- scan_job_id — BIGINT — Foreign key → scan_jobs.scan_job_id — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id
- item_source — ENUM('GMAIL', 'DRIVE') — NOT NULL
- external_item_id — VARCHAR(255)
- parent_external_id — VARCHAR(255)
- folder_path — VARCHAR(1000)
- title — VARCHAR(500)
- sender_domain — VARCHAR(255)
- label_text — VARCHAR(500)
- snippet — VARCHAR(500)
- mime_type — VARCHAR(150)
- file_extension — VARCHAR(20)
- size_bytes — BIGINT — NOT NULL
- attachment_size_bytes — BIGINT — NOT NULL
- received_at — DATETIME
- created_time — DATETIME
- modified_time — DATETIME
- last_opened_time — DATETIME
- is_starred — TINYINT(1) — NOT NULL
- is_important — TINYINT(1) — NOT NULL
- has_attachment — TINYINT(1) — NOT NULL
- is_shared — TINYINT(1) — NOT NULL
- md5_checksum — VARCHAR(100)
- owner_email — VARCHAR(150)
- is_trashed — TINYINT(1) — NOT NULL
- trashed_at — DATETIME
- metadata_json — JSON
- deleted_at — DATETIME
- created_at — DATETIME — NOT NULL
user_consents
- consent_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- is_privacy_agreed — TINYINT(1) — NOT NULL
- is_ai_analysis_agreed — TINYINT(1) — NOT NULL
- is_metadata_only_agreed — TINYINT(1) — NOT NULL
- is_user_approval_required_agreed — TINYINT(1) — NOT NULL
- consent_version — VARCHAR(30) — NOT NULL
- consented_at — DATETIME
- updated_at — DATETIME — NOT NULL
user_withdrawals
- withdrawal_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.user_id — NOT NULL
- reason — VARCHAR(500)
- google_disconnected — TINYINT(1) — NOT NULL
- token_deleted — TINYINT(1) — NOT NULL
- scan_data_policy — ENUM('DELETE', 'ANONYMIZE', 'KEEP') — NOT NULL
- history_data_policy — ENUM('DELETE', 'ANONYMIZE', 'KEEP') — NOT NULL
- processed_status — ENUM('PENDING', 'COMPLETED', 'FAILED') — NOT NULL
- failure_reason — VARCHAR(500)
- withdrawn_at — DATETIME — NOT NULL
- processed_at — DATETIME
users
- user_id — BIGINT — Primary key — NOT NULL
- google_provider_id — VARCHAR(100)
- email — VARCHAR(150)
- display_name — VARCHAR(100)
- profile_image_url — VARCHAR(500)
- account_status — ENUM('ACTIVE', 'DISCONNECTED', 'WITHDRAWN') — NOT NULL
- last_login_at — DATETIME
- withdrawn_at — DATETIME
- anonymized_at — DATETIME
- anonymous_user_key — VARCHAR(64)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL