# CAPEv2 Database Schema: How Analysis Tasks Are Managed and Tracked

> Explore the CAPEv2 database schema and learn how its SQLAlchemy design manages and tracks analysis tasks. Discover atomic operations for task lifecycle management.

- Repository: [Kevin O'Reilly/capev2](https://github.com/kevoreilly/capev2)
- Tags: database-schema
- Published: 2026-03-05

---

**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`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/lib/cuckoo/core/data/tasking.py). This mixin is consumed by the `Database` class ([`lib/cuckoo/core/database.py`](https://github.com/kevoreilly/capev2/blob/main/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:

```python
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`](https://github.com/kevoreilly/capev2/blob/main/web/analysis/views.py) for the dashboard, [`web/apiv2/views.py`](https://github.com/kevoreilly/capev2/blob/main/web/apiv2/views.py) for REST endpoints, [`utils/dist.py`](https://github.com/kevoreilly/capev2/blob/main/utils/dist.py) for distributed node coordination, and [`utils/process.py`](https://github.com/kevoreilly/capev2/blob/main/utils/process.py) for post-analysis pipeline updates.

## Summary

- The **CAPEv2 database schema** is implemented in SQLAlchemy with [`lib/cuckoo/common/dist_db.py`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/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`](https://github.com/kevoreilly/capev2/blob/main/lib/cuckoo/core/data/tasking.py) and the processing utilities.