Data Model

This document provides a detailed overview of the core data entities, their structures, and their relationships within the IT Ticketing Service. A thorough understanding of this model is essential for developers working on the application's backend, particularly the persistence and business logic layers.

The primary data model is centered around a single entity, Ticket, which is managed using Spring Data JPA.

Ticket Entity

The Ticket entity represents a single IT support request. It is the central object in the application's domain, containing all information related to a ticket's lifecycle, from creation to resolution.

JPA Entity Definition

The Ticket entity is defined in src/main/java/com/slalom/demo/ticketing/model/Ticket.java. It uses Jakarta Persistence API (JPA) annotations for Object-Relational Mapping (ORM) and Lombok annotations to reduce boilerplate code.

package com.slalom.demo.ticketing.model;

import jakarta.persistence.*;
import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import java.time.LocalDateTime;

// Marks this class as a JPA entity, making it manageable by a JPA provider (Hibernate).
@Entity
// Specifies the database table name. Without this, JPA would default to "ticket".
@Table(name = "tickets")
// Lombok: Generates getters, setters, toString(), equals(), and hashCode() methods.
@Data
// Lombok: Generates a no-argument constructor, required by JPA.
@NoArgsConstructor
// Lombok: Generates a constructor with all fields as arguments.
@AllArgsConstructor
// Lombok: Implements the Builder design pattern for easy object creation.
@Builder
public class Ticket {

    // Defines the primary key for the entity.
    @Id
    // Configures the primary key generation strategy. IDENTITY relies on the database's auto-increment column.
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // JSR 380 validation: Ensures the title is not null and contains at least one non-whitespace character.
    @NotBlank(message = "Title is required")
    // JPA: Maps this field to a database column that cannot be null.
    @Column(nullable = false)
    private String title;

    @NotBlank(message = "Description is required")
    @Column(nullable = false, length = 2000)
    private String description;

    // JSR 380 validation: Ensures the status is not null.
    @NotNull(message = "Status is required")
    // JPA: Specifies that the enum should be persisted as a String (e.g., "OPEN") rather than its ordinal value (e.g., 0).
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private TicketStatus status;

    @NotNull(message = "Priority is required")
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private TicketPriority priority;

    // JPA: Explicitly maps the field to a column with a different name.
    @Column(name = "requester_email", nullable = false)
    @NotBlank(message = "Requester email is required")
    private String requesterEmail;

    @Column(name = "assigned_to")
    private String assignedTo;

    // Hibernate: Automatically sets this field to the current timestamp when the entity is first persisted.
    @CreationTimestamp
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt;

    // Hibernate: Automatically updates this field to the current timestamp whenever the entity is updated.
    @UpdateTimestamp
    @Column(name = "updated_at", nullable = false)
    private LocalDateTime updatedAt;

    @Column(name = "resolved_at")
    private LocalDateTime resolvedAt;
}

Fields and Attributes

The following table details each field in the Ticket entity, its purpose, and its validation/database constraints.

Field	Java Type	Database Column	Constraints	Description
id	Long	id	Primary Key, Auto-Increment	The unique identifier for the ticket.
title	String	title	NOT NULL, @NotBlank	A brief, descriptive title for the ticket.
description	String	description	NOT NULL, VARCHAR(2000), @NotBlank	A detailed description of the issue or request.
status	TicketStatus	status	NOT NULL, @NotNull, ENUM (persisted as VARCHAR)	The current status of the ticket (e.g., OPEN, IN_PROGRESS). See Ticket Status section below.
priority	TicketPriority	priority	NOT NULL, @NotNull, ENUM (persisted as VARCHAR)	The priority level of the ticket (e.g., LOW, HIGH). See Ticket Priority section below.
requesterEmail	String	requester_email	NOT NULL, @NotBlank	The email address of the user who created the ticket.
assignedTo	String	assigned_to	NULLABLE	The email address of the support agent assigned to the ticket. Can be null if unassigned.
createdAt	LocalDateTime	created_at	NOT NULL, NOT UPDATABLE	Timestamp automatically set by Hibernate when the ticket is created.
updatedAt	LocalDateTime	updated_at	NOT NULL	Timestamp automatically updated by Hibernate every time the ticket is modified.
resolvedAt	LocalDateTime	resolved_at	NULLABLE	Timestamp that should be manually set by the business logic when the ticket status moves to RESOLVED.

Database Schema

Based on the JPA mappings, the corresponding SQL schema for the tickets table in MySQL is as follows. For more details on the database setup, see the Database Integration documentation.

CREATE TABLE tickets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description VARCHAR(2000) NOT NULL,
    status VARCHAR(255) NOT NULL,
    priority VARCHAR(255) NOT NULL,
    requester_email VARCHAR(255) NOT NULL,
    assigned_to VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    resolved_at DATETIME NULL
);

Developer Note: The @CreationTimestamp and @UpdateTimestamp annotations are a Hibernate-specific feature. While convenient, they rely on the application layer. If records are inserted or updated directly in the database, these fields will not be populated automatically unless database-level triggers are also configured.

Ticket Status

The TicketStatus is an enumeration that defines the possible states in a ticket's lifecycle. It is stored as a string in the database to ensure readability and prevent issues if the enum order changes.

// src/main/java/com/slalom/demo/ticketing/model/TicketStatus.java
package com.slalom.demo.ticketing.model;

public enum TicketStatus {
    OPEN,
    IN_PROGRESS,
    WAITING_ON_CUSTOMER,
    RESOLVED,
    CLOSED
}

Status Lifecycle and Transitions

The application's business logic (located in the service layer) should enforce the valid transitions between statuses. The expected lifecycle is as follows:

1. OPEN: The initial state of any newly created ticket.
   • Valid next states: IN_PROGRESS, RESOLVED, CLOSED (if invalid).
2. IN_PROGRESS: An agent has started working on the ticket.
   • Valid next states: WAITING_ON_CUSTOMER, RESOLVED.
3. WAITING_ON_CUSTOMER: The agent requires more information from the requester.
   • Valid next states: IN_PROGRESS.
4. RESOLVED: The work is complete, and the issue is believed to be solved.
   • Valid next states: CLOSED, IN_PROGRESS (if the issue re-occurs).
5. CLOSED: The ticket is finalized and requires no further action. This is a terminal state.

Gotcha: The current implementation does not have a state machine or explicit validation to enforce these transitions. This logic must be carefully implemented in the TicketService class. For example, a ticket should not be able to move from OPEN directly to WAITING_ON_CUSTOMER.

Business Rules for Status Changes

• When a ticket's status is changed to RESOLVED, the resolvedAt timestamp field should be populated with the current time.
• When a ticket is re-opened from RESOLVED back to IN_PROGRESS, the resolvedAt field should be set to null.
• The system may implement an automated process to move tickets from RESOLVED to CLOSED after a predefined period (e.g., 7 days) of inactivity.

Ticket Priority

The TicketPriority enumeration defines the urgency of a ticket, which helps support teams prioritize their work.

// src/main/java/com/slalom/demo/ticketing/model/TicketPriority.java
package com.slalom.demo.ticketing.model;

public enum TicketPriority {
    LOW,
    MEDIUM,
    HIGH,
    CRITICAL
}

Priority Levels

• LOW: Non-urgent requests, such as general questions or minor issues with workarounds.
• MEDIUM: The default priority for most issues that affect a single user's productivity but are not critical.
• HIGH: Issues that significantly impact a user's ability to work and have no immediate workaround.
• CRITICAL: System-wide outages or issues affecting multiple users or business-critical functions.

Impact on Ticket Handling

While not enforced by the data model itself, the priority field is intended to drive business logic:

• SLAs: Higher priority tickets should have shorter response and resolution time targets.
• Escalation: CRITICAL tickets may trigger automated alerts or escalations to management.
• Queueing: In the messaging system, CRITICAL or HIGH priority tickets could be routed to a dedicated high-priority queue for immediate attention.

Entity Relationships

The current data model is intentionally simple and consists of a single, denormalized Ticket entity.

Database Relationships

The Ticket entity has no direct JPA-defined relationships (e.g., @ManyToOne, @OneToMany) with other entities. Fields like requesterEmail and assignedTo are stored as simple String types rather than as foreign keys to a User or Agent table.

Design Rationale: This denormalized approach was chosen for simplicity, which is common in legacy systems or microservices where the service owns its data completely. It avoids the complexity of joins and lazy/eager loading.

Potential Issues:

• Data Integrity: There is no foreign key constraint to ensure that requesterEmail corresponds to a valid user in another system. Typos or changes to user emails can lead to orphaned data.
• Data Redundancy: User information (like an email) is duplicated across all tickets they create.
• Querying Limitations: It is difficult to perform queries such as "show me all tickets assigned to agents in the 'Network' team" without joining on the application side or introducing a more complex data model.

Constraints and Validations

Data integrity is enforced at two levels:

1. Application Level (Bean Validation): Annotations like @NotBlank and @NotNull are used for pre-persistence validation. If an API request contains invalid data, Spring's validation starter will intercept it and return a 400 Bad Request response before the service logic is even executed. For details on error formats, see the API Request/Response Formats documentation.
2. Database Level: The @Column(nullable = false) annotation instructs the JPA provider to generate DDL with NOT NULL constraints. This serves as a final safeguard against null data being persisted, even from sources other than the application API.