Skip to content

Database Schema

This page documents the database schema and entity relationships in Arctyk ITSM.

Overview

Arctyk ITSM uses PostgreSQL as its primary database. The schema is organized around core entities: Tickets, Projects, Assets, Users, and Comments.

Core Models

Ticket Model

The central entity in the system.

Fields:

  • id (PK) - Auto-incrementing integer
  • ticket_number - Unique ticket number (auto-generated)
  • title - Ticket title (max 200 chars)
  • description - Full description (TextField with rich text support)
  • status - Current status (CharField, choices)
  • status_category - Derived category (todo/in_progress/done)
  • priority - Priority level (low/medium/high/critical)
  • issue_type - Type (bug/task/story/epic)
  • assignee (FK) - User assigned to ticket
  • reporter (FK) - User who created ticket
  • project (FK) - Associated project
  • SLA Fields (new in v0.6.0):
  • response_target - Target response time (DateTimeField, nullable)
  • resolution_target - Target resolution time (DateTimeField, nullable)
  • Lifecycle Timestamps (new in v0.6.0):
  • created_at - Ticket creation timestamp
  • first_responded_at - When first response/comment was added
  • resolved_at - When ticket moved to resolved status
  • closed_at - When ticket was closed
  • updated_at - Last update timestamp

Comment Model (new in v0.6.0)

Support for ticket discussions with public and internal comments.

Fields:

  • id (PK) - Auto-incrementing integer
  • ticket (FK) - Parent ticket
  • author (FK) - User who created comment
  • content - Comment text (TextField with HTML/rich text support)
  • comment_type - Type of comment (CharField: "public" or "internal")
  • created_at - Comment creation timestamp
  • updated_at - Last update timestamp
  • edited_at - Last edit timestamp (nullable)

Relationships:

  • One-to-many with Ticket (each ticket can have many comments)
  • Foreign key to User (author)
  • Automatic changelog entries via signals

CommentEditHistory Model (new in v0.6.0)

Audit trail for comment edits.

Fields:

  • id (PK) - Auto-incrementing integer
  • comment (FK) - Parent comment
  • edited_by (FK) - User who made the edit
  • previous_content - Content before edit (TextField)
  • edited_at - When edit was made

Relationships:

  • One-to-many with Comment
  • Foreign key to User (edited_by)

Project Model

Organizational grouping for tickets.

Fields:

  • id (PK) - Auto-incrementing integer
  • name - Project name (CharField, max 255 chars)
  • description - Project description (TextField)
  • created_at - Creation timestamp
  • updated_at - Last update timestamp

Asset Model

IT asset inventory tracking.

Fields:

  • id (PK) - Auto-incrementing integer
  • name - Asset name (CharField)
  • asset_type - Type of asset (CharField)
  • serial_number - Serial number (CharField)
  • status - Current status (CharField)
  • created_at - Creation timestamp

User Model

Extended Django User model for authentication.

Fields:

  • Extends Django's default User model
  • Additional profile fields through signal-based extension
  • is_staff - Admin access flag
  • is_superuser - Full permissions flag

Entity Relationships

User (Reporter) ──────┐
                      │
                      ├──> Ticket ──────> Project
                      │
                      └──> Assignee

Ticket ──────────────────> Comment
                              │
                              ├──> CommentEditHistory
                              │
                              └──> Author (User)

Asset ──────────────────> (associated with Projects/Users)

Key Constraints

  • Ticket title is required (NOT NULL)
  • Ticket status must be one of predefined choices
  • Comment content is required (NOT NULL)
  • Comment comment_type defaults to "public"
  • All timestamp fields are auto-managed (created_at, updated_at)
  • Lifecycle timestamps (first_responded_at, resolved_at, closed_at) are nullable and set by business logic

Indexes

Key indexes for performance:

  • Ticket.ticket_number (unique)
  • Ticket.created_at (for filtering/sorting)
  • Ticket.status (for filtering)
  • Comment.ticket_id (for comment retrieval)
  • Comment.created_at (for sorting)
  • CommentEditHistory.comment_id (for edit history)