CAPEv2 Database Schema: How Analysis Tasks Are Managed and Tracked

CAPEv2 utilizes a SQLAlchemy-driven relational database centered on the tasks table, with the TasksMixIn class providing atomic operations for status transitions, task retrieval, and lifecycle management across distributed workers.

The open-source malware analysis sandbox CAPEv2 (kevoreilly/capev2) persists all operational data in a structured relational schema. At its core, the CAPEv2 database schema orchestrates the entire analysis pipeline—from file submission through sandbox execution to report generation—using a relational model that tracks samples, machines, and task states with full audit capabilities.

Core Tables in the CAPEv2 Database Schema

The schema is defined using SQLAlchemy’s declarative base in lib/cuckoo/common/dist_db.py and specialized model files. All entities inherit from the common Base class to ensure consistent ORM behavior.

The tasks Table

The tasks table is the central entity representing every analysis request. Defined in lib/cuckoo/core/data/task.py, it stores the target file or URL, analysis category (file, URL, PCAP, static), assigned machine, package type, priority, and temporal tracking fields (added_on, started_on, completed_on). It maintains foreign keys to users and supports flexible option strings and Traffic Light Protocol (TLP) markings.

Sample Metadata and Associations

The samples table stores hash-indexed file metadata including MD5, SHA1, SHA256, file type, and size. For archive extractions, the sample_association table links parent samples to child extracts, optionally referencing the specific task_id that processed the child. This design enables correlation between original payloads and unpacked components without duplicating binary data.

Infrastructure and Routing Tables

Analysis capacity is modeled through the machines table, which records physical or virtual guest names, platforms (Windows/Linux), tags, and node affiliations. The node table represents distributed workers in multi-node deployments, while worker_exitnodes manages many-to-many relationships to Tor exit nodes or VPN endpoints. The tags table provides normalized strings for routing decisions, linking to both tasks and machines.

Error Tracking and Guest Metadata

Per-task failures are logged in the errors table (task_id, message) for forensic review. During execution, the guests table maintains a one-to-one mapping of active tasks to running VMs, tracking IP addresses, hostnames, and guest UUIDs as defined in lib/cuckoo/core/data/guests.py.

Task Status Lifecycle and State Transitions

CAPEv2 implements a finite state machine using string constants defined in lib/cuckoo/core/data/task.py (lines 26–36). The status column in the tasks table enforces this progression:

• TASK_PENDING – Awaiting dispatch to an available worker
• TASK_RUNNING – Assigned to a machine, sandbox execution active
• TASK_DISTRIBUTED – Intermediate state in distributed mode
• TASK_COMPLETED – VM execution finished, raw results available
• TASK_REPORTED – Post-processing and report generation finished
• TASK_FAILED_ANALYSIS / TASK_FAILED_PROCESSING / TASK_FAILED_REPORTING – Failure at specific pipeline stages
• TASK_BANNED – Cancelled by user or policy enforcement
• TASK_RECOVERED – Rescheduled after worker crash
• TASK_DISTRIBUTED_COMPLETED – Final distributed-mode confirmation

Transitions between states are handled atomically through the ORM layer to prevent race conditions during concurrent worker access.

Managing Tasks with the TasksMixIn Class

All database operations for task management are encapsulated in the TasksMixIn class located in lib/cuckoo/core/data/tasking.py. This mixin is consumed by the Database class (lib/cuckoo/core/database.py) and utilized by the web UI, REST API, and background workers.

Task Submission and Creation

add_path(file_path, ...) provides a high-level interface for queuing files, automatically creating Sample entries if the hash does not exist and resolving tag strings to database IDs. The method returns the new task.id immediately upon transaction commit.

Atomic Task Retrieval and Status Updates

fetch_task(categories=None) implements an atomic select-and-update pattern: it queries for the highest-priority pending task matching optional category filters, immediately updates the row status to TASK_RUNNING, and returns the populated Task object. This ensures that in distributed deployments, only one worker claims each task.

set_status(task_id, status) and set_task_status(task, status) handle state transitions with automatic timestamp management—setting started_on when transitioning to running and completed_on when finishing.

Querying and Administrative Operations

• list_tasks(...) – Flexible query builder supporting status filters, tag searches, user isolation, pagination (limit/offset), and sorting
• view_task(task_id, details=False) – Retrieves single tasks with optional eager loading of related tags, guest data, and sample metadata
• count_tasks(status=None, mid=None) – Returns scalar counts for dashboard metrics or capacity planning
• delete_task(task_id) / delete_tasks(**filters) – Cascading removal of tasks and associated records
• add_error(message, task_id) – Persists sandbox or processing failures linked to specific tasks
• ban_user_tasks(user_id) – Bulk-cancels all pending tasks for a given user (useful for abuse mitigation)
• tasks_reprocess(task_id) – Resets completed tasks to TASK_PENDING for re-analysis without re-uploading samples

Practical Database Interaction Example

The following pattern demonstrates how the web interface and worker daemons interact with the CAPEv2 database schema:

from lib.cuckoo.core.database import Database
from lib.cuckoo.core.data.task import TASK_PENDING, Task

# Initialize DB helper (creates a session factory)

db: Database = Database()

# 1. Submit a new file for analysis

task_id = db.add_path(
    file_path="/samples/malware.exe",
    timeout=300,
    package="exe",
    priority=5,
    tags="malware,exe",
    user_id=42,                # optional – links task to an authenticated user

)

print(f"Task queued – ID {task_id}")

# 2. Worker atomically fetches the next pending task

task: Task = db.fetch_task(categories=["file"])
if task:
    print(f"Worker got task {task.id} – target: {task.target}")

    # ... execute sandbox analysis on task.target ...

    # 3. Mark completion and record statistics

    db.set_status(task.id, "completed")

This same interface is invoked by web/analysis/views.py for the dashboard, web/apiv2/views.py for REST endpoints, utils/dist.py for distributed node coordination, and utils/process.py for post-analysis pipeline updates.

Summary

• The CAPEv2 database schema is implemented in SQLAlchemy with lib/cuckoo/common/dist_db.py defining the declarative base and table relationships
• The tasks table tracks every analysis request through a strict status lifecycle from TASK_PENDING to TASK_REPORTED
• TasksMixIn in lib/cuckoo/core/data/tasking.py provides atomic methods like fetch_task() for race-free job distribution and set_status() for state management
• Supporting tables (samples, machines, errors, guests) normalize metadata and enable distributed processing across multiple nodes
• The schema supports reprocessing via tasks_reprocess() without duplicate sample storage, and bulk operations like ban_user_tasks() for administrative control

Frequently Asked Questions

How does CAPEv2 prevent race conditions when multiple workers fetch tasks simultaneously?

The fetch_task() method in TasksMixIn executes an atomic database query that selects the highest-priority pending task and updates its status to TASK_RUNNING within a single transaction. This ensures that only one worker receives each task ID, preventing duplicate analysis attempts across distributed nodes.

What is the difference between the samples and tasks tables in the CAPEv2 database schema?

The samples table stores static file metadata (hashes, size, type) indexed by cryptographic hashes to avoid binary duplication, while the tasks table represents individual analysis jobs with runtime state (status, timestamps, assigned machine). A single sample hash may be associated with multiple task records over time for re-analysis or different analysis options.

How can I reprocess a completed analysis without uploading the file again?

Invoke the tasks_reprocess(task_id) method from TasksMixIn, which resets the task status from TASK_COMPLETED or TASK_REPORTED back to TASK_PENDING. This preserves the original sample reference and options while queuing the job for fresh execution, as utilized by administrative utilities and the web interface’s “Re-Analyze” feature.

Where are task status constants defined in the CAPEv2 source code?

Status string constants such as TASK_PENDING, TASK_RUNNING, and TASK_FAILED_ANALYSIS are defined as module-level constants in lib/cuckoo/core/data/task.py (lines 26–36). These values are stored in the tasks.status column and referenced throughout the codebase in lib/cuckoo/core/data/tasking.py and the processing utilities.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}