Database Integration

This document provides a comprehensive technical overview of the database integration for the IT Ticketing Service. It covers the database schema, data access layer implementation using Spring Data JPA, connection management, migration strategies, and performance considerations.

Database Schema

The application uses a MySQL 8.0 database to persist ticket data. The schema is defined declaratively through the Ticket JPA entity. Hibernate, the underlying JPA provider, generates the DDL (Data Definition Language) based on this entity's annotations.

For a higher-level overview of how the Ticket entity fits into the broader system, refer to the Data Model documentation.

tickets Table Structure

The primary table is tickets, which corresponds to the com.slalom.demo.ticketing.model.Ticket entity.

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

@Entity
@Table(name = "tickets")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Ticket {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

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

    @NotNull(message = "Status is required")
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private TicketStatus status;

    // ... other fields
}

Column Definitions

The following table details the columns in the tickets table, their corresponding Java types, and constraints.

Column Name	SQL Type (Inferred)	Java Type	Constraints & Notes
id	BIGINT	Long	Primary Key, Auto-incrementing (IDENTITY strategy).
title	VARCHAR(255)	String	NOT NULL. Stores the ticket's title.
description	VARCHAR(2000)	String	NOT NULL. A detailed description of the issue. Length is explicitly set to 2000.
status	VARCHAR(255)	TicketStatus	NOT NULL. Stored as a string (e.g., "OPEN", "CLOSED") due to @Enumerated(EnumType.STRING).
priority	VARCHAR(255)	TicketPriority	NOT NULL. Stored as a string (e.g., "HIGH", "LOW") due to @Enumerated(EnumType.STRING).
requester_email	VARCHAR(255)	String	NOT NULL. The email address of the user who created the ticket.
assigned_to	VARCHAR(255)	String	NULLABLE. The user or group the ticket is assigned to.
created_at	DATETIME	LocalDateTime	NOT NULL, updatable=false. Automatically populated on creation via @CreationTimestamp.
updated_at	DATETIME	LocalDateTime	NOT NULL. Automatically populated on creation and updated on every modification via @UpdateTimestamp.
resolved_at	DATETIME	LocalDateTime	NULLABLE. Timestamp for when the ticket was moved to a resolved state. Must be set manually by business logic.

Indexes and Constraints

• Primary Key: The id column is the primary key.
• Not-Null Constraints: Applied to several columns as specified in the table above to ensure data integrity.
• Implicit Indexes: While not explicitly defined in the @Table annotation, the TicketRepository defines query methods that search by status, requesterEmail, and assignedTo. For production environments with large datasets, it is critical to add database indexes on these columns to prevent performance degradation. See the Performance Considerations section for more details.

Spring Data JPA

The application leverages Spring Data JPA to simplify the data access layer. This approach abstracts away boilerplate JDBC and persistence code, allowing developers to focus on business logic.

Entity Mapping

The Ticket.java class is a POJO (Plain Old Java Object) annotated to map it to the tickets database table.

• @Entity: Marks the class as a JPA entity.
• @Table(name = "tickets"): Specifies the database table name.
• @Id and @GeneratedValue(strategy = GenerationType.IDENTITY): Designate the id field as the primary key and configure it to use the database's auto-increment capability. This is ideal for MySQL.
• @Column: Used to customize column mapping, such as setting name, nullable, length, and updatable attributes.
• @Enumerated(EnumType.STRING): Ensures that enum values (TicketStatus, TicketPriority) are persisted as their string names rather than their ordinal integer values, which is more readable and robust against changes in enum order.
• @CreationTimestamp / @UpdateTimestamp: Hibernate annotations that automatically manage the createdAt and updatedAt fields.

Repository Interface Usage

The TicketRepository interface extends JpaRepository, which provides a full set of standard CRUD (Create, Read, Update, Delete) methods without any implementation code.

// src/main/java/com/slalom/demo/ticketing/repository/TicketRepository.java

@Repository
public interface TicketRepository extends JpaRepository<Ticket, Long> {

    // Finds all tickets with a given status
    List<Ticket> findByStatus(TicketStatus status);

    // Finds all tickets submitted by a specific requester
    List<Ticket> findByRequesterEmail(String requesterEmail);

    // Finds all tickets assigned to a specific user/group
    List<Ticket> findByAssignedTo(String assignedTo);
}

Spring Data JPA automatically implements these interface methods at runtime based on their method names. This feature, known as query derivation, parses method names like findByStatus and generates the appropriate JPQL query (SELECT t FROM Ticket t WHERE t.status = ?1). This pattern significantly reduces boilerplate data access code.

Automatic Schema Generation

The application is currently configured for automatic schema management via Hibernate.

# src/main/resources/application.properties

# JPA/Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update

The spring.jpa.hibernate.ddl-auto=update property instructs Hibernate to compare the entity model with the existing database schema on startup and automatically apply any necessary changes (e.g., add new tables or columns).

Warning: This setting is convenient for development and local testing but is highly discouraged for production environments. It has several risks, including potential data loss and an inability to handle complex migrations like column renames. For production, a dedicated database migration tool should be used.

Connection Management

Database connection settings are externalized in application.properties, allowing for easy configuration across different environments.

For more details on application-wide settings, see the Configuration documentation.

Connection Configuration Properties

The core database connection properties are defined as follows:

Production/Remote Configuration:

# src/main/resources/application.properties

# MySQL Database Configuration
spring.datasource.url=jdbc:mysql://legacydb.next26.slalomlab.com:3306/ticketing
spring.datasource.username=ticketing_user
spring.datasource.password=changeme
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# JPA/Hibernate Configuration
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

Local Development Configuration:

For local development, a MySQL 8.0 instance can be provisioned using the provided docker-compose.yml file. When running the application on your local machine (outside of Docker), update the connection URL:

spring.datasource.url=jdbc:mysql://localhost:3306/ticketing
spring.datasource.username=ticketing_user
spring.datasource.password=changeme

The docker-compose configuration creates a MySQL container named ticketing-mysql with:

• Database: ticketing (automatically created)
• User: ticketing_user / Password: changeme
• Root password: password
• Port mapping: 3306:3306
• Persistent volume: mysql-data for data persistence across container restarts

To start the local database:

docker-compose up -d mysql

Property Descriptions:

• spring.datasource.url: The JDBC connection string for the MySQL database. Use localhost:3306 for local development or the remote host for production.
• spring.datasource.username/password: Credentials for database authentication. Note: These should be managed via secure secrets management in production.
• spring.jpa.properties.hibernate.dialect: Specifies the SQL dialect for Hibernate to use, ensuring it generates MySQL 8.0-compatible SQL.

Connection Pooling

Spring Boot automatically configures a JDBC connection pool when spring-boot-starter-data-jpa is on the classpath. By default, it uses HikariCP, a highly efficient and reliable connection pool. The pool manages a set of active database connections, which are reused for subsequent requests. This dramatically improves performance by avoiding the overhead of establishing a new database connection for every transaction. Default settings are generally sufficient, but can be tuned for high-load scenarios (see Performance Considerations).

Transaction Management

Spring Boot provides declarative transaction management. All methods in Spring Data JPA repositories (like TicketRepository) are transactional by default. For business logic in service classes that involves multiple repository calls or state changes, methods should be annotated with @Transactional. This ensures that the group of operations within the method executes as a single atomic unit: either all operations succeed, or all are rolled back in case of an error.

Database Migration

A robust database migration strategy is essential for maintaining schema consistency and integrity across different environments (development, staging, production).

Schema Initialization (Development)

As mentioned, the current strategy relies on spring.jpa.hibernate.ddl-auto=update. This is suitable only for local development where the database can be easily reset.

Data Migration Strategies (Production)

For production, a more controlled approach is required. The recommended practice is to use a dedicated database migration tool like Flyway or Liquibase. These tools offer:

• Version Control: Migration scripts (e.g., V1__create_tickets_table.sql) are versioned and stored in source control alongside the application code.
• Repeatable Migrations: The tool tracks which migrations have been applied to a database and only runs new ones, ensuring every environment reaches the same schema state.
• Rollbacks: Support for rolling back changes if a migration fails.
• Complex Changes: Ability to handle complex changes like data transformations, column renames, and seeding initial data, which ddl-auto cannot.

Adopting one of these tools is a critical step before deploying to production or undertaking a cloud migration. For more context on migration, see the GCP Migration plan.

Performance Considerations

Optimizing database interactions is key to application performance and scalability.

Query Optimization

• Analyze Generated SQL: During development, it's useful to inspect the SQL generated by Hibernate. This can be enabled by setting spring.jpa.show-sql=true in application.properties. This helps identify inefficient queries or potential N+1 problems, especially in services that call repository methods within a loop.
• Use @Query for Complex Logic: While query derivation is convenient, for complex queries involving joins, aggregations, or performance-critical paths, it is better to write the query explicitly using @Query with JPQL or native SQL. This gives you full control over the executed statement.

Index Usage

The performance of SELECT queries depends heavily on proper indexing. Based on the TicketRepository methods, the following columns are candidates for indexing:

• status
• requester_email
• assigned_to

Without indexes, queries on these columns will result in a full table scan, which becomes progressively slower as the tickets table grows. Indexes can be added directly in the entity definition using the @Table annotation's indexes attribute:

@Entity
@Table(name = "tickets", indexes = {
    @Index(name = "idx_ticket_status", columnList = "status"),
    @Index(name = "idx_ticket_requester_email", columnList = "requester_email"),
    @Index(name = "idx_ticket_assigned_to", columnList = "assigned_to")
})
public class Ticket {
    // ...
}

Connection Pool Tuning

The default HikariCP settings are a good starting point, but may require tuning under heavy load. Key properties can be configured in application.properties:

# Example HikariCP tuning properties
# spring.datasource.hikari.maximum-pool-size=20
# spring.datasource.hikari.minimum-idle=5
# spring.datasource.hikari.connection-timeout=30000 # 30 seconds
# spring.datasource.hikari.idle-timeout=600000 # 10 minutes

• maximum-pool-size: The maximum number of connections the pool can hold. This is a critical tuning parameter.
• connection-timeout: The maximum time a client will wait for a connection from the pool.