onever_drive/database/schema.sql

-- ==============================================================================
-- OnEver Drive — PostgreSQL Production Schema
-- ==============================================================================

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    role VARCHAR(50) DEFAULT 'ADMIN' NOT NULL,
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,
    client_code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    hostname VARCHAR(255),
    os_info VARCHAR(255),
    ip_address VARCHAR(100),
    agent_version VARCHAR(50) DEFAULT '1.0.0' NOT NULL,
    status VARCHAR(50) DEFAULT 'OFFLINE' NOT NULL,
    storage_used_bytes BIGINT DEFAULT 0 NOT NULL,
    storage_quota_bytes BIGINT DEFAULT 107374182400 NOT NULL,
    last_seen_at TIMESTAMP WITH TIME ZONE,
    last_backup_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS client_credentials (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    device_id VARCHAR(100) UNIQUE NOT NULL,
    token_hash VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) DEFAULT 'Primary Windows Agent' NOT NULL,
    is_revoked BOOLEAN DEFAULT FALSE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_used_at TIMESTAMP WITH TIME ZONE
);

CREATE TABLE IF NOT EXISTS registration_codes (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    is_used BOOLEAN DEFAULT FALSE NOT NULL,
    client_name_hint VARCHAR(255),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS backup_jobs (
    id SERIAL PRIMARY KEY,
    job_code VARCHAR(50) UNIQUE NOT NULL,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    name VARCHAR(255) NOT NULL,
    source_path VARCHAR(1024) NOT NULL,
    file_patterns VARCHAR(255) DEFAULT '*.bak,*.mdf' NOT NULL,
    schedule_cron VARCHAR(100) DEFAULT '0 2 * * *' NOT NULL,
    keep_daily INTEGER DEFAULT 7 NOT NULL,
    keep_weekly INTEGER DEFAULT 4 NOT NULL,
    keep_monthly INTEGER DEFAULT 12 NOT NULL,
    min_stable_time_seconds INTEGER DEFAULT 60 NOT NULL,
    status VARCHAR(50) DEFAULT 'IDLE' NOT NULL,
    last_run_at TIMESTAMP WITH TIME ZONE,
    next_run_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS backup_sessions (
    id SERIAL PRIMARY KEY,
    session_code VARCHAR(100) UNIQUE NOT NULL,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    job_id INTEGER REFERENCES backup_jobs(id) ON DELETE CASCADE,
    filename VARCHAR(512) NOT NULL,
    file_size BIGINT NOT NULL,
    chunk_size INTEGER DEFAULT 4194304 NOT NULL,
    total_chunks INTEGER NOT NULL,
    received_chunks_count INTEGER DEFAULT 0 NOT NULL,
    sha256_full VARCHAR(64) NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING' NOT NULL,
    error_message TEXT,
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    completed_at TIMESTAMP WITH TIME ZONE
);

CREATE TABLE IF NOT EXISTS backup_chunks (
    id SERIAL PRIMARY KEY,
    session_id INTEGER REFERENCES backup_sessions(id) ON DELETE CASCADE NOT NULL,
    chunk_index INTEGER NOT NULL,
    chunk_size INTEGER NOT NULL,
    sha256 VARCHAR(64) NOT NULL,
    is_received BOOLEAN DEFAULT FALSE NOT NULL,
    received_at TIMESTAMP WITH TIME ZONE,
    UNIQUE(session_id, chunk_index)
);

CREATE TABLE IF NOT EXISTS backup_files (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    job_id INTEGER REFERENCES backup_jobs(id) ON DELETE CASCADE,
    session_id INTEGER REFERENCES backup_sessions(id) ON DELETE SET NULL,
    filename VARCHAR(512) NOT NULL,
    relative_path VARCHAR(1024) NOT NULL,
    file_size BIGINT NOT NULL,
    sha256 VARCHAR(64) NOT NULL,
    retention_tag VARCHAR(50) DEFAULT 'DAILY' NOT NULL,
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS event_logs (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    severity VARCHAR(50) DEFAULT 'INFO' NOT NULL,
    client_id INTEGER,
    job_id INTEGER,
    user_email VARCHAR(255),
    ip_address VARCHAR(100),
    message TEXT NOT NULL,
    details_json TEXT
);

CREATE INDEX IF NOT EXISTS idx_sessions_client_status ON backup_sessions(client_id, status);
CREATE INDEX IF NOT EXISTS idx_event_logs_timestamp ON event_logs(timestamp);