YG_FT/backend/app/db/sql/001_platform_runtime.sql

CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    display_name TEXT NOT NULL,
    role TEXT NOT NULL,
    status TEXT NOT NULL,
    permissions TEXT NOT NULL,
    create_time TEXT NOT NULL,
    last_login TEXT,
    protected INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS models (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL,
    purpose TEXT NOT NULL,
    model_source TEXT NOT NULL,
    description TEXT,
    path TEXT,
    api_url TEXT,
    api_key TEXT,
    online_model_name TEXT,
    create_time TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS trained_models (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    train_methods TEXT NOT NULL,
    base_model_path TEXT,
    create_time TEXT NOT NULL,
    merged INTEGER NOT NULL DEFAULT 0,
    merging INTEGER NOT NULL DEFAULT 0,
    merged_path TEXT
);

CREATE TABLE IF NOT EXISTS datasets (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL,
    storage_type TEXT NOT NULL,
    source TEXT NOT NULL,
    task_id TEXT,
    size TEXT,
    count INTEGER NOT NULL DEFAULT 0,
    description TEXT,
    create_time TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dataset_files (
    id TEXT PRIMARY KEY,
    dataset_id TEXT NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    size TEXT,
    content TEXT NOT NULL,
    active_version_id TEXT NOT NULL,
    versions TEXT NOT NULL,
    create_time TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS compute_nodes (
    id TEXT PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    api_base_url TEXT NOT NULL,
    file_gateway_url TEXT NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1,
    scheduler_status TEXT NOT NULL,
    scheduler_weight INTEGER NOT NULL DEFAULT 100,
    tags TEXT NOT NULL,
    gpu_count INTEGER NOT NULL DEFAULT 0,
    current_running_jobs INTEGER NOT NULL DEFAULT 0,
    max_parallel_jobs INTEGER NOT NULL DEFAULT 2,
    data_root TEXT NOT NULL,
    model_root TEXT NOT NULL,
    log_root TEXT NOT NULL,
    last_health_check_at TEXT,
    health_detail TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS gpus (
    id TEXT PRIMARY KEY,
    node_id TEXT NOT NULL REFERENCES compute_nodes(id) ON DELETE CASCADE,
    gpu_index INTEGER NOT NULL,
    uuid TEXT NOT NULL,
    name TEXT NOT NULL,
    memory_total_gb DOUBLE PRECISION NOT NULL,
    power_limit_w DOUBLE PRECISION NOT NULL,
    base_temperature INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS fine_tune_tasks (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    payload TEXT NOT NULL,
    status TEXT NOT NULL,
    progress INTEGER NOT NULL DEFAULT 0,
    process_id INTEGER,
    create_time TEXT NOT NULL,
    start_time TEXT,
    completed_at TEXT,
    compute_node_id TEXT REFERENCES compute_nodes(id) ON DELETE SET NULL,
    gpus TEXT NOT NULL,
    sync_job_id TEXT
);

CREATE TABLE IF NOT EXISTS resource_replicas (
    id TEXT PRIMARY KEY,
    node_id TEXT NOT NULL REFERENCES compute_nodes(id) ON DELETE CASCADE,
    resource_type TEXT NOT NULL,
    resource_id TEXT NOT NULL,
    local_path TEXT NOT NULL,
    status TEXT NOT NULL,
    sync_status TEXT NOT NULL,
    create_time TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS resource_sync_jobs (
    id TEXT PRIMARY KEY,
    target_node_id TEXT NOT NULL,
    resources TEXT NOT NULL,
    status TEXT NOT NULL,
    progress INTEGER NOT NULL DEFAULT 0,
    create_time TEXT NOT NULL,
    completed_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_fine_tune_status ON fine_tune_tasks(status);
CREATE INDEX IF NOT EXISTS idx_dataset_files_dataset ON dataset_files(dataset_id);
CREATE INDEX IF NOT EXISTS idx_gpus_node ON gpus(node_id);
CREATE INDEX IF NOT EXISTS idx_replicas_resource ON resource_replicas(resource_type, resource_id);