Kintone Backup System - Database Schema

1. Core Tables

-- Users & Authentication
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- Kintone Apps Configuration
CREATE TABLE kintone_apps (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    subdomain VARCHAR(100) NOT NULL,
    app_id BIGINT NOT NULL,
    api_token VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_is_active (is_active)
);

-- Backup Schedules
CREATE TABLE backup_schedules (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    kintone_app_id BIGINT NOT NULL,
    schedule_time TIME NOT NULL,
    timezone VARCHAR(50) DEFAULT 'Asia/Ho_Chi_Minh',
    is_active BOOLEAN DEFAULT TRUE,
    last_run_at TIMESTAMP NULL,
    next_run_at TIMESTAMP NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (kintone_app_id) REFERENCES kintone_apps(id) ON DELETE CASCADE,
    INDEX idx_next_run_at (next_run_at),
    INDEX idx_is_active (is_active)
);

-- Backup Jobs (Queue Jobs Tracking)
CREATE TABLE backup_jobs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    kintone_app_id BIGINT NOT NULL,
    schedule_id BIGINT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    total_records INT DEFAULT 0,
    processed_records INT DEFAULT 0,
    total_files INT DEFAULT 0,
    processed_files INT DEFAULT 0,
    s3_path VARCHAR(500) NULL,
    error_message TEXT NULL,
    metadata JSON NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (kintone_app_id) REFERENCES kintone_apps(id),
    FOREIGN KEY (schedule_id) REFERENCES backup_schedules(id),
    INDEX idx_status (status),
    INDEX idx_kintone_app_id (kintone_app_id),
    INDEX idx_created_at (created_at)
);

-- Backup Logs (Detailed logs for debugging)
CREATE TABLE backup_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    backup_job_id BIGINT NOT NULL,
    level ENUM('info', 'warning', 'error') DEFAULT 'info',
    message TEXT NOT NULL,
    context JSON NULL,
    created_at TIMESTAMP,
    FOREIGN KEY (backup_job_id) REFERENCES backup_jobs(id) ON DELETE CASCADE,
    INDEX idx_backup_job_id (backup_job_id),
    INDEX idx_level (level),
    INDEX idx_created_at (created_at)
);

-- Backup Files (Lưu trữ fileKeys từ records)
CREATE TABLE backup_files (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    backup_job_id BIGINT NOT NULL,
    record_id VARCHAR(100) NOT NULL,
    field_code VARCHAR(100) NOT NULL,
    file_key VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size BIGINT DEFAULT 0,
    content_type VARCHAR(100) NULL,
    status ENUM('pending', 'downloaded', 'failed', 'uploaded') DEFAULT 'pending',
    error_message TEXT NULL,
    local_path VARCHAR(500) NULL,
    s3_path VARCHAR(500) NULL,
    downloaded_at TIMESTAMP NULL,
    uploaded_at TIMESTAMP NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (backup_job_id) REFERENCES backup_jobs(id) ON DELETE CASCADE,
    INDEX idx_backup_job_id (backup_job_id),
    INDEX idx_status (status),
    INDEX idx_file_key (file_key),
    UNIQUE KEY unique_job_file (backup_job_id, file_key)
);

-- API Request Tracking (Monitor daily limits)
CREATE TABLE api_request_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    kintone_app_id BIGINT NOT NULL,
    request_date DATE NOT NULL,
    request_count INT DEFAULT 0,
    last_request_at TIMESTAMP NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (kintone_app_id) REFERENCES kintone_apps(id),
    UNIQUE KEY unique_app_date (kintone_app_id, request_date),
    INDEX idx_request_date (request_date)
);