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