Skip to content

Database Schema

Jump to bottom
Jordan edited this page Aug 17, 2026 · 4 revisions

Database Schema

Preserved Qoder snapshot. This deep-dive page is retained so the earlier Wiki work and its source trail are not lost. For the reconciled implementation, Architecture Overview is canonical; references below to retired controllers, listeners, services, tables, or API shapes are historical.

**Referenced Files in This Document** - [2025_01_01_000001_create_ptero_resource_reservations_table.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/database/migrations/2025_01_01_000001_create_ptero_resource_reservations_table.php) - [2026_04_23_000001_add_idempotency_key_to_ptero_resource_reservations.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/database/migrations/2026_04_23_000001_add_idempotency_key_to_ptero_resource_reservations.php) - [2026_04_22_000001_drop_released_from_reservation_status.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/database/migrations/2026_04_22_000001_drop_released_from_reservation_status.php) - [2025_01_01_000003_create_ptero_audit_logs_table.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/database/migrations/2025_01_01_000003_create_ptero_audit_logs_table.php) - [2025_01_01_000004_create_ptero_alert_configs_table.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/database/migrations/2025_01_01_000004_create_ptero_alert_configs_table.php) - [2026_04_26_000001_create_ptero_alert_delivery_log_table.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/database/migrations/2026_04_26_000001_create_ptero_alert_delivery_log_table.php) - [ResourceReservation.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Models/ResourceReservation.php) - [AuditLog.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Models/AuditLog.php) - [AlertConfig.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Models/AlertConfig.php) - [AlertDeliveryLog.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Models/AlertDeliveryLog.php) - [ReservationService.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Services/ReservationService.php) - [AuditLogService.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Services/AuditLogService.php) - [AlertService.php](https://github.com/ObsidianNetwork/dynamic-pterodactyl/blob/6b7f83bda6f7c3fe014d52428b31af1638daa6cc/Services/AlertService.php)

Table of Contents

  1. Introduction
  2. Project Structure
  3. Core Components
  4. Architecture Overview
  5. Detailed Component Analysis
  6. Dependency Analysis
  7. Performance Considerations
  8. Troubleshooting Guide
  9. Conclusion
  10. Appendices

Introduction

This document provides comprehensive data model documentation for the database tables and Eloquent models used by the Dynamic Pterodactyl extension. It focuses on:

  • Resource reservations table for temporary resource holds, TTL expiration, and status transitions
  • Audit logs table for capturing administrative actions and system events
  • Alert configurations schema for capacity monitoring thresholds and notification preferences
  • Entity relationships, field definitions, constraints, indexes, validation rules, business logic, lifecycle management, common queries, and performance considerations for large datasets

Project Structure

The data model spans migrations that define schema, Eloquent models that encapsulate behavior, and services that enforce business rules and orchestrate operations. The key artifacts are:

  • Migrations: define tables, columns, types, constraints, and indexes
  • Models: map to tables, define casts, scopes, and relationships
  • Services: implement reservation lifecycle, audit logging, alerting, and delivery tracking
graph TB
subgraph "Database"
R["ptero_resource_reservations"]
A["ptero_audit_logs"]
C["ptero_alert_configs"]
D["ptero_alert_delivery_log"]
end
subgraph "Models"
MR["ResourceReservation"]
MA["AuditLog"]
MC["AlertConfig"]
MD["AlertDeliveryLog"]
end
subgraph "Services"
RS["ReservationService"]
ALS["AlertService"]
AS["AuditLogService"]
end
RS --> R
RS --> A
ALS --> C
ALS --> D
AS --> A
MR --> R
MA --> A
MC --> C
MD --> D
Loading

Diagram sources

Section sources

Core Components

  • Resource reservations: track temporary holds with token, TTL, and status transitions; include idempotency support and foreign keys to cart, service, and user
  • Audit logs: immutable records of actions with actor context, entity references, change snapshots, and request metadata
  • Alert configurations: per-location or global thresholds for memory/disk utilization with email/webhook notifications and cooldowns
  • Alert delivery log: records of attempted alert deliveries, channels tried/succeeded/failed, and last error

Section sources

Architecture Overview

The data layer is driven by migrations and enforced by Eloquent models and services. ReservationService orchestrates reservation creation, confirmation, cancellation, extension, cleanup, and statistics using pessimistic locking and idempotency. AlertService evaluates thresholds against availability and writes delivery logs. AuditLogService persists audit entries with request context.

sequenceDiagram
participant Client as "Client"
participant RS as "ReservationService"
participant DB as "DB (ptero_resource_reservations)"
participant AL as "AuditLogService"
participant AUD as "ptero_audit_logs"
Client->>RS : create(productId, locationId, resources, cartItemId?, userId?, idempotencyKey?)
RS->>DB : lockForUpdate() pending rows by location
RS->>DB : insert reservation (pending, expires_at)
RS->>AL : safeAudit('created', 'reservation', ...)
AL->>AUD : insert audit entry
RS-->>Client : {token, node_id, expires_at, status}
Loading

Diagram sources

Detailed Component Analysis

Resource Reservations

  • Purpose: Track temporary resource holds with TTL expiration and strict status transitions
  • Status lifecycle: pending → confirmed | expired | cancelled (released removed)
  • Idempotency: supports idempotent creation via unique active key constraint
  • Foreign keys: optional links to cart_items, services, users

Fields and types

  • id: auto-increment primary key
  • token: string(64), unique identifier for the reservation
  • idempotency_key: string(64), nullable; paired with active_idempotency_key for idempotency
  • active_idempotency_key: generated column derived from idempotency_key when status is pending or confirmed
  • cart_item_id: unsigned big integer, nullable, FK to cart_items
  • service_id: unsigned big integer, nullable, FK to services
  • user_id: unsigned big integer, nullable, FK to users
  • node_id: unsigned integer
  • location_id: unsigned integer
  • memory: unsigned integer (MB)
  • disk: unsigned big integer (MB)
  • cpu: unsigned integer (percentage; 100 = 1 core)
  • calculated_price: decimal(10,2)
  • pricing_breakdown: json
  • status: enum(pending, confirmed, expired, cancelled); default pending
  • admin_notes: text, nullable
  • expires_at: timestamp
  • created_at, updated_at: timestamps

Indexes and constraints

  • Unique index on token
  • Composite index on (node_id, status, expires_at)
  • Index on cart_item_id
  • Composite index on (status, expires_at)
  • Index on location_id, status
  • Index on user_id, status
  • Index on created_at
  • FKs to cart_items, services, users with set null on delete
  • Unique constraint on (user_id, active_idempotency_key)
  • Index on (user_id, idempotency_key)

Business rules and validation

  • Creation uses pessimistic locking on pending reservations per location to avoid overbooking
  • TTL computed from configuration; default TTL minutes read from extension settings
  • Confirmation requires pending status and not expired
  • Cancellation requires pending status
  • Extension updates expires_at only for pending reservations
  • Cleanup marks pending past expires_at as expired
  • Idempotency prevents duplicate active reservations for same user and key

Common queries

  • Get by token: select where token
  • Get by cart item: select where cart_item_id and status=pending
  • Query all with filters: status, location_id, node_id, user_id
  • Statistics: group by status, sum revenue, average resources for a period
  • Cleanup: update pending where expires_at < now() to expired

Relationships

  • BelongsTo User
  • BelongsTo Service

Model scopes

  • Pending: status=pending and expires_at > now
  • Expired: status=pending and expires_at <= now
classDiagram
class ResourceReservation {
+string token
+string idempotency_key
+unsignedBigInteger cart_item_id
+unsignedBigInteger service_id
+unsignedBigInteger user_id
+unsignedInteger node_id
+unsignedInteger location_id
+unsignedInteger memory
+unsignedBigInteger disk
+unsignedInteger cpu
+decimal calculated_price
+json pricing_breakdown
+enum status
+text admin_notes
+timestamp expires_at
+timestamps
+scopePending(query)
+scopeExpired(query)
+user()
+service()
}
Loading

Diagram sources

Section sources

Audit Logs

  • Purpose: Capture administrative actions and system events with actor context and change details
  • Fields and types
    • id: auto-increment primary key
    • user_id: unsigned big integer, FK to users cascade delete
    • user_name: string
    • user_email: string
    • action: string (e.g., created, updated, deleted, cancelled)
    • entity_type: string (e.g., pricing_config, reservation, alert_config)
    • entity_id: unsigned big integer
    • entity_name: string, nullable
    • old_values: json, nullable
    • new_values: json, nullable
    • description: text, nullable
    • ip_address: string(45), nullable
    • user_agent: string, nullable
    • created_at: timestamp
  • Indexes
    • Composite on (entity_type, entity_id)
    • Index on user_id
    • Index on created_at
    • Index on action

Business rules and usage

  • Written via AuditLogService with current user context and request metadata
  • Used to record reservation lifecycle events (create, confirm, cancel, extend, batch expire)
  • Supports filtering by entity type/id, user, action, and date range

Common queries

  • getLogs(filters, limit): paginated list with filters
  • getEntityHistory(entityType, entityId): history for a specific entity
flowchart TD
Start([Action Occurs]) --> BuildPayload["Build payload<br/>user, action, entity, values, context"]
BuildPayload --> Insert["Insert into ptero_audit_logs"]
Insert --> End([Done])
Loading

Diagram sources

Section sources

Alert Configurations

  • Purpose: Define capacity monitoring thresholds and notification preferences per location or globally
  • Fields and types
    • id: auto-increment primary key
    • location_id: unsigned integer, nullable (null means global)
    • location_name: string, nullable
    • memory_warning_threshold: unsigned tiny integer, default 80
    • memory_critical_threshold: unsigned tiny integer, default 95
    • disk_warning_threshold: unsigned tiny integer, default 80
    • disk_critical_threshold: unsigned tiny integer, default 95
    • email_notifications: boolean, default true
    • notification_emails: json, nullable (array of emails)
    • webhook_notifications: boolean, default false
    • webhook_url: string, nullable
    • cooldown_minutes: unsigned integer, default 60
    • last_notification_at: timestamp, nullable
    • is_active: boolean, default true
    • created_at, updated_at: timestamps
  • Indexes
    • Index on location_id
    • Index on is_active

Business rules and usage

  • AlertService checks thresholds based on availability and sends notifications if breached
  • Cooldown prevents spamming by limiting frequency via last_notification_at
  • Delivery attempts recorded in ptero_alert_delivery_log

Common queries

  • Global configs: scopeGlobal()
  • Per-location configs: scopeForLocation(locationId)
classDiagram
class AlertConfig {
+unsignedInteger location_id
+string location_name
+unsignedTinyInteger memory_warning_threshold
+unsignedTinyInteger memory_critical_threshold
+unsignedTinyInteger disk_warning_threshold
+unsignedTinyInteger disk_critical_threshold
+boolean email_notifications
+json notification_emails
+boolean webhook_notifications
+string webhook_url
+unsignedInteger cooldown_minutes
+timestamp last_notification_at
+boolean is_active
+timestamps
+scopeGlobal(query)
+scopeForLocation(query, locationId)
+deliveryLogs()
}
Loading

Diagram sources

Section sources

Alert Delivery Log

  • Purpose: Record each attempt to deliver alerts, including channels tried, success/failure, and errors
  • Fields and types
    • id: auto-increment primary key
    • alert_config_id: unsigned big integer, FK to ptero_alert_configs cascade delete
    • trigger_type: enum(capacity_breach, shortfall, state_drift, check_failure)
    • attempted_at: timestamp
    • channels_tried: json array, default []
    • channels_ok: json array, default []
    • channels_failed: json array, default []
    • last_error: text, nullable
    • created_at, updated_at: timestamps

Relationships

  • BelongsTo AlertConfig

Usage

  • AlertService writes delivery logs after attempting email and webhook notifications
  • Events may be dispatched when delivery fails
classDiagram
class AlertDeliveryLog {
+unsignedBigInteger alert_config_id
+enum trigger_type
+timestamp attempted_at
+json channels_tried
+json channels_ok
+json channels_failed
+text last_error
+timestamps
+alertConfig()
}
Loading

Diagram sources

Section sources

Dependency Analysis

  • ReservationService depends on NodeSelectionService to pick nodes and on AuditLogService to record actions
  • AlertService depends on ResourceCalculationService for availability metrics and writes to AlertDeliveryLog
  • AuditLogService writes directly to ptero_audit_logs with request context
  • Models provide scopes and relationships to simplify queries and navigation
graph LR
RS["ReservationService"] --> NS["NodeSelectionService"]
RS --> ALSvc["AuditLogService"]
RS --> RRes["ptero_resource_reservations"]
RS --> Aud["ptero_audit_logs"]
ALS["AlertService"] --> RCS["ResourceCalculationService"]
ALS --> ADel["ptero_alert_delivery_log"]
ALS --> ACfg["ptero_alert_configs"]
Loading

Diagram sources

Section sources

Performance Considerations

  • Use existing indexes for efficient filtering:
    • (node_id, status, expires_at) for node-level pending lookups
    • (status, expires_at) for cleanup and expiry scans
    • (location_id, status) for location-scoped queries
    • (user_id, status) for user-scoped queries
    • (entity_type, entity_id) for audit log retrieval by entity
    • (user_id, idempotency_key) and unique (user_id, active_idempotency_key) for idempotent creates
  • Avoid full table scans by always filtering on indexed columns in admin queries
  • Batch updates for cleanup to minimize round trips
  • For large datasets:
    • Partition or archive audit logs periodically by created_at
    • Consider archiving expired/cancelled reservations
    • Use pagination for admin listings
  • Pessimistic locking with retries mitigates deadlocks during concurrent reservation creation

[No sources needed since this section provides general guidance]

Troubleshooting Guide

  • Duplicate idempotency key errors:
    • Handled by detecting unique constraint violations and returning existing reservation
  • Deadlocks on reservation creation:
    • Retried up to 5 times; ensure application handles transient failures gracefully
  • Alerts not delivered:
    • Check last_notification_at vs cooldown_minutes
    • Inspect ptero_alert_delivery_log for channels_failed and last_error
    • Verify email recipients and webhook URL configuration
  • Audit logs missing:
    • Ensure AuditLogService is invoked for critical actions
    • Validate user context and request metadata availability

Section sources

Conclusion

The data model provides robust support for temporary resource reservations with strong concurrency controls, comprehensive auditability, and configurable capacity alerting. Proper use of indexes, scopes, and service-layer logic ensures scalability and reliability under load.

[No sources needed since this section summarizes without analyzing specific files]

Appendices

Entity Relationship Diagram

erDiagram
USERS ||--o{ PTERO_RESOURCE_RESERVATIONS : "user_id FK"
CART_ITEMS ||--o{ PTERO_RESOURCE_RESERVATIONS : "cart_item_id FK"
SERVICES ||--o{ PTERO_RESOURCE_RESERVATIONS : "service_id FK"
PTERO_ALERT_CONFIGS ||--o{ PTERO_ALERT_DELIVERY_LOG : "alert_config_id FK"
USERS ||--o{ PTERO_AUDIT_LOGS : "user_id FK"
Loading

Diagram sources

Common Queries Reference

  • Create reservation: see ReservationService::create
  • Confirm reservation: see ReservationService::confirm
  • Cancel reservation: see ReservationService::cancel
  • Extend reservation TTL: see ReservationService::extend
  • Cleanup expired: see ReservationService::cleanupExpired
  • Query all with filters: see ReservationService::queryAll
  • Get statistics: see ReservationService::getStatistics
  • Write audit log: see AuditLogService::log
  • Filter audit logs: see AuditLogService::getLogs
  • Evaluate alerts: see AlertService threshold checks

Section sources

DynamicPterodactyl · Dynamic Resource Sliders for Paymenter × Pterodactyl · Reviewed code checkpoint · Publication commits intentionally pin their latest code-bearing predecessor because a Git commit cannot self-reference its unknown object ID.

DynamicPterodactyl

Guides

Architecture

Core Services

API Reference

Database

System

Clone this wiki locally