Database Schema

This document provides a detailed technical overview of the database schema for the tickets table, which is the central data store for the application's ticketing functionality. It covers the table structure, indexing strategies, JPA entity mapping, and conventions for managing timestamps.

This schema is managed via JPA and Hibernate, based on the entity definitions in the source code. For details on the application's database connection settings, refer to the Database Configuration documentation.

Tickets Table

The tickets table stores all information related to a support ticket. The schema is derived directly from the Ticket entity. Given the configuration spring.jpa.hibernate.ddl-auto=update, Hibernate will attempt to automatically update the schema to match the entity definition upon application startup.

Warning: While ddl-auto=update is convenient for development, it is not recommended for production environments. Production schema changes should be managed through controlled migration scripts (e.g., using Flyway or Liquibase) to prevent accidental data loss or corruption.

The following table describes the columns in the tickets table, their corresponding SQL types (for a MySQL database), and constraints.

Column Name	Data Type	Constraints	Description
id	BIGINT	PRIMARY KEY, AUTO_INCREMENT, NOT NULL	The unique identifier for the ticket.
title	VARCHAR(255)	NOT NULL	A concise summary of the ticket's subject.
description	VARCHAR(2000)	NOT NULL	A detailed description of the issue or request.
status	VARCHAR(255)	NOT NULL	The current state of the ticket (e.g., OPEN, IN_PROGRESS). Stored as a string.
priority	VARCHAR(255)	NOT NULL	The urgency of the ticket (e.g., LOW, MEDIUM, HIGH). Stored as a string.
requester_email	VARCHAR(255)	NOT NULL	The email address of the person who created the ticket.
assigned_to	VARCHAR(255)	NULL	The email address of the agent currently assigned to the ticket. Can be null.
created_at	DATETIME(6)	NOT NULL	Timestamp automatically set when the ticket is first created. Not updatable.
updated_at	DATETIME(6)	NOT NULL	Timestamp automatically updated every time the ticket record is modified.
resolved_at	DATETIME(6)	NULL	Timestamp that should be manually set when the ticket is moved to a resolved state.

Indexes

Proper indexing is critical for query performance, especially as the tickets table grows.

• Primary Key Index: An index is automatically created on the id column because it is the primary key. This ensures fast lookups by ticket ID.

• Recommended Query Indexes: The TicketRepository defines several query methods that will benefit from dedicated indexes. To avoid performance degradation and full table scans, it is highly recommended to create indexes on the following columns:

  // From: src/main/java/com/slalom/demo/ticketing/repository/TicketRepository.java
  public interface TicketRepository extends JpaRepository<Ticket, Long> {
  
      // This query will benefit from an index on the `status` column.
      List<Ticket> findByStatus(TicketStatus status);
  
      // This query will benefit from an index on the `requester_email` column.
      List<Ticket> findByRequesterEmail(String requesterEmail);
  
      // This query will benefit from an index on the `assigned_to` column.
      List<Ticket> findByAssignedTo(String assignedTo);
  }

  Recommended SQL for Index Creation:

  CREATE INDEX idx_tickets_status ON tickets(status);
  CREATE INDEX idx_tickets_requester_email ON tickets(requester_email);
  CREATE INDEX idx_tickets_assigned_to ON tickets(assigned_to);

Entity Mapping

The tickets table schema is defined by the Ticket JPA entity. This class uses a combination of Jakarta Persistence and Hibernate annotations to map its fields to database columns. For a higher-level overview of the data model, see the Ticket Model documentation.

// From: src/main/java/com/slalom/demo/ticketing/model/Ticket.java
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;

@Entity // Marks this class as a JPA entity.
@Table(name = "tickets") // Maps this entity to the "tickets" database table.
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Ticket {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // Uses the database's auto-increment feature for the primary key.
    private Long id;

    @NotBlank(message = "Title is required")
    @Column(nullable = false)
    private String title;

    @NotBlank(message = "Description is required")
    @Column(nullable = false, length = 2000) // Specifies a larger column length for the description.
    private String description;

    @NotNull(message = "Status is required")
    @Enumerated(EnumType.STRING) // Stores the enum value as its string name (e.g., "OPEN") instead of an integer.
    @Column(nullable = false)
    private TicketStatus status;

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

    @Column(name = "requester_email", nullable = false) // Maps the camelCase field to a snake_case column name.
    @NotBlank(message = "Requester email is required")
    private String requesterEmail;

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

    // ... timestamp fields ...
}

Key Annotation Explanations:

• @Entity & @Table: These declare the class as a persistence entity and link it to the tickets table.
• @Id & @GeneratedValue: This combination configures the id field as the primary key and instructs the persistence provider to rely on the database's AUTO_INCREMENT mechanism (GenerationType.IDENTITY) to generate key values.
• @Column: This annotation provides explicit mapping details.
  • name = "requester_email": Explicitly maps a Java field (camelCase) to a database column (snake_case), which is a common convention.
  • nullable = false: Enforces a NOT NULL constraint at the database level.
  • length = 2000: Sets the column length for string-based fields.
• @Enumerated(EnumType.STRING): This is a critical design choice. It ensures that the status and priority enums are stored as human-readable strings in the database (e.g., OPEN, HIGH). The alternative, EnumType.ORDINAL, stores the enum's integer position, which is brittle and can lead to data corruption if the enum order changes.
• @NotBlank & @NotNull: These Jakarta Bean Validation annotations provide application-level validation before data is persisted. @NotBlank validates that string fields are not null, empty, or whitespace-only. @NotNull validates that fields are not null. These annotations work in conjunction with @Column(nullable = false) to provide both application-level and database-level validation.

Timestamp Management

The entity uses a combination of automated and manual timestamping to track the lifecycle of a ticket.

// From: src/main/java/com/slalom/demo/ticketing/model/Ticket.java

    @CreationTimestamp // Managed by Hibernate: set once on creation.
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt;

    @UpdateTimestamp // Managed by Hibernate: updated on every modification.
    @Column(name = "updated_at", nullable = false)
    private LocalDateTime updatedAt;

    @Column(name = "resolved_at") // Manually managed by application logic.
    private LocalDateTime resolvedAt;

• createdAt (@CreationTimestamp): This field is automatically populated by Hibernate with the current timestamp only when the entity is first persisted. The updatable = false attribute ensures it cannot be changed on subsequent updates, making it a reliable audit field for creation time.

• updatedAt (@UpdateTimestamp): This field is automatically managed by Hibernate and is updated to the current timestamp every time the entity is modified and saved to the database.

• resolvedAt (Manual Management): This field has no special Hibernate annotation and must be set manually by the application's business logic. It is the developer's responsibility to set this timestamp when a ticket's status is changed to RESOLVED or CLOSED. Forgetting to set this field will result in NULL values for resolved tickets, which could impact reporting and SLAs.

  Example Logic (in a service class):

  public Ticket resolveTicket(Long ticketId) {
      Ticket ticket = ticketRepository.findById(ticketId).orElseThrow();
      ticket.setStatus(TicketStatus.RESOLVED);
      ticket.setResolvedAt(LocalDateTime.now()); // Manually setting the timestamp
      return ticketRepository.save(ticket);
  }

For more information on how data is accessed and manipulated through repositories, see the Data Access Layer documentation.