RoleForge/app/sql/001_init.sql

create extension if not exists "pgcrypto";

create table if not exists users (
    id uuid primary key default gen_random_uuid(),
    username text unique,
    email text unique not null,
    password_hash text not null,
    role text not null default 'user',
    full_name text not null,
    created_at timestamptz not null default now()
);

create table if not exists password_reset_tokens (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references users(id) on delete cascade,
    token text unique not null,
    expires_at timestamptz not null,
    used_at timestamptz,
    created_at timestamptz not null default now()
);

create table if not exists role_categories (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid references users(id) on delete cascade,
    name text not null,
    created_at timestamptz not null default now(),
    unique (owner_id, name)
);

create table if not exists app_config (
    key text primary key,
    value jsonb not null default '{}'::jsonb,
    updated_at timestamptz not null default now()
);

create table if not exists ansible_roles (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid not null references users(id) on delete cascade,
    name text not null,
    source_type text not null,
    source_ref text not null default '',
    category_id uuid references role_categories(id) on delete set null,
    content jsonb not null default '{}'::jsonb,
    visibility text not null default 'personal' check (visibility in ('public', 'team', 'personal')),
    team_id uuid,
    forked_from_id uuid references ansible_roles(id) on delete set null,
    created_at timestamptz not null default now()
);

create table if not exists role_files (
    id uuid primary key default gen_random_uuid(),
    role_id uuid not null references ansible_roles(id) on delete cascade,
    path text not null,
    content text not null default '',
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    unique (role_id, path)
);

create table if not exists inventories (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid not null references users(id) on delete cascade,
    name text not null,
    inventory_text text not null,
    created_at timestamptz not null default now()
);

create table if not exists playbooks (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid not null references users(id) on delete cascade,
    name text not null,
    description text not null default '',
    playbook_yaml text not null,
    inventory_id uuid not null references inventories(id) on delete cascade,
    role_ids uuid[] not null default '{}',
    is_shared boolean not null default false,
    created_at timestamptz not null default now()
);

create table if not exists jobs (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid not null references users(id) on delete cascade,
    playbook_id uuid not null references playbooks(id) on delete cascade,
    status text not null,
    runtime_mode text not null default 'docker',
    celery_task_id text,
    runner_url text,
    runner_run_id text,
    runner_container_name text,
    runner_last_heartbeat timestamptz,
    extra_vars jsonb not null default '{}'::jsonb,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz not null default now()
);

create table if not exists job_logs (
    id bigserial primary key,
    job_id uuid not null references jobs(id) on delete cascade,
    log_line text not null,
    created_at timestamptz not null default now()
);

create table if not exists test_runs (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid not null references users(id) on delete cascade,
    playbook_id uuid references playbooks(id) on delete cascade,
    role_id uuid references ansible_roles(id) on delete cascade,
    status text not null,
    runtime_mode text not null default 'docker',
    celery_task_id text,
    runner_url text,
    runner_run_id text,
    runner_container_name text,
    runner_last_heartbeat timestamptz,
    hosts_blueprint jsonb not null default '[]'::jsonb,
    extra_vars jsonb not null default '{}'::jsonb,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz not null default now()
);

create table if not exists test_logs (
    id bigserial primary key,
    test_run_id uuid not null references test_runs(id) on delete cascade,
    log_line text not null,
    created_at timestamptz not null default now()
);