Database Entities & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• warpgate-db-migrations/src/m00008_users.rs
• warpgate-db-migrations/src/m00007_targets_and_roles.rs
• warpgate-db-migrations/src/m00014_api_tokens.rs
• warpgate-db-migrations/src/m00017_descriptions.rs
• warpgate-db-migrations/src/m00005_create_log_entry.rs
• warpgate-core/src/db/mod.rs
• warpgate/src/commands/setup.rs

Database Entities and Migrations in Warpgate define the persistence layer for user accounts, access control roles, connection targets, and audit logs. The system utilizes sea-orm as the Object-Relational Mapper (ORM) and sea-orm-migration for versioned schema updates.

The database is primarily SQLite by default, though the architecture supports other backends through SQLx. It serves as the authoritative source for user authentication, target configuration, and session history management.

Sources: README.md:95-97, warpgate-core/src/db/mod.rs:20-25

Database Architecture & Schema

The schema is built around a Role-Based Access Control (RBAC) model where Users and Targets are associated via Roles. This relationship is managed through explicit assignment entities.

Entity Relationship Diagram

The following diagram illustrates the core relationships between users, roles, targets, and authentication tokens.

Core Data Models

Entity	Table Name	Key Fields	Description
User	users	id, username, credentials	Represents a person or service account authenticating to Warpgate.
Role	roles	id, name, description	Groups permissions; acts as a bridge between users and targets.
Target	targets	id, name, kind, options	Defines a remote host (SSH, HTTP, MySQL, etc.) to which users connect.
LogEntry	log	id, text, session_id	Audit trails for system events and user sessions.
ApiToken	api_tokens	id, user_id, secret	Long-lived credentials for programmatic API access.

Sources: warpgate-db-migrations/src/m00008_users.rs:10-15, warpgate-db-migrations/src/m00007_targets_and_roles.rs:49-54, warpgate-db-migrations/src/m00005_create_log_entry.rs:8-17

Migration System

Warpgate uses a sequential migration system to evolve the database schema. Each migration implements the MigrationTrait with up and down methods to manage schema changes and data transformations.

Migration Execution Flow

The migration process is triggered during the database connection phase when the application starts or during initial setup.

Initialization & Built-ins

During setup or first run, the system ensures essential entities exist to prevent lockout:

1. Admin Role: A role named warpgate:admin is created and assigned a description ("Built-in admin role").
2. Admin Target: A target of kind WebAdmin is created to allow management via the web UI.
3. Admin User: During warpgate setup, a user named admin is created and linked to the warpgate:admin role.

Sources: warpgate-core/src/db/mod.rs:85-135, warpgate/src/commands/setup.rs:275-320

Log and Session Persistence

The database stores audit logs and session metadata. To prevent unbounded growth, a cleanup routine is provided to prune records based on a retention policy.

Log Entry Structure

pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: Uuid,
    pub text: String,
    pub values: JsonValue,
    pub timestamp: DateTime<Utc>,
    pub session_id: Uuid,
    pub username: Option<String>,
}

Sources: warpgate-db-migrations/src/m00005_create_log_entry.rs:10-17

Cleanup Logic

The cleanup_db function removes expired LogEntry, Recording, and Session entities.

• It calculates a cutoff point using the provided retention duration.
• It deletes log entries where timestamp < cutoff.
• It iterates through sessions that have ended before the cutoff, removing their associated recording files before deleting the database records.

Sources: warpgate-core/src/db/mod.rs:142-178

Target Definitions

Targets are categorized by TargetKind, which dictates the options stored in the options JSON field.

Kind	String Value	Description
Http	"http"	Proxied web services.
MySql	"mysql"	MySQL database connections.
Ssh	"ssh"	SSH remote shell/command access.
WebAdmin	"web_admin"	The internal Warpgate administration interface.

Sources: warpgate-db-migrations/src/m00007_targets_and_roles.rs:22-31

Conclusion

The Database Entities and Migrations module provides a structured, versioned approach to managing Warpgate's state. By leveraging sea-orm and a strict migration path, the system ensures data consistency across updates while providing the flexibility needed for RBAC and detailed audit logging.