Database Components

1. Overview

The Event and Membership Administration System uses two database library components to manage data persistence through JPA (Jakarta Persistence API). These components define entity classes and provide repository interfaces for database operations.

2. Component Overview

Component	Entities	Purpose	Status
event-database	65	Primary entity repository for event and membership management	Active
wordpress-database	14	Legacy WordPress entities for backward compatibility	Deprecated (6-month timeline)

Component

Entities

Purpose

Status

event-database

Primary entity repository for event and membership management

Active

wordpress-database

Legacy WordPress entities for backward compatibility

Deprecated (6-month timeline)

3. event-database

The primary database library containing 65 JPA entities organized into six functional categories.

Repository: https://github.com/christhonie/event-database

Maven Coordinates:

<groupId>za.co.idealogic</groupId>
<artifactId>event-database</artifactId>
<version>${revision}</version>

Package: za.co.idealogic.event.domain

3.1. Dependencies

<dependencies>
  <!-- Parent POM -->
  <parent>
    <groupId>za.co.idealogic</groupId>
    <artifactId>event</artifactId>
    <version>${revision}</version>
  </parent>

  <!-- Internal Dependencies -->
  <dependency>
    <groupId>za.co.idealogic</groupId>
    <artifactId>event-common</artifactId>
  </dependency>

  <!-- Temporary: Will be removed after WordPress migration -->
  <dependency>
    <groupId>za.co.idealogic</groupId>
    <artifactId>wordpress-database</artifactId>
  </dependency>

  <!-- Spring Data JPA -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>

  <!-- MySQL Driver (runtime) -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
  </dependency>

  <!-- Bean Validation -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-validation</artifactId>
  </dependency>

  <!-- Lombok (compile-time) -->
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
  </dependency>
</dependencies>

3.2. Entity Categories

The 65 entities in event-database are organized into six functional categories:

Category Breakdown:

Common/Infrastructure (14 entities) - Cross-tenant entities used throughout the system
Event Management (35 entities) - Event-specific functionality including races, results, and participants
Membership Management (5 entities) - Membership tracking and criteria
Process Management (5 entities) - Workflow and business process definitions
Financial Management (7 entities) - Orders, payments, and general ledger

For detailed entity documentation, see Domain Entities.

3.3. Repository Pattern

All entities have corresponding Spring Data JPA repositories:

package za.co.idealogic.event.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import za.co.idealogic.event.domain.Event;
import za.co.idealogic.event.domain.EventStatus;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

public interface EventRepository extends JpaRepository<Event, Long> {

    // Query method derivation
    List<Event> findByOrganisationId(Long organisationId);

    List<Event> findByStartDateBetween(LocalDate start, LocalDate end);

    Optional<Event> findByIdAndOrganisationId(Long id, Long organisationId);

    // Custom JPQL query
    @Query("SELECT e FROM Event e WHERE e.status = :status " +
           "AND e.organisation.id = :orgId " +
           "ORDER BY e.startDate DESC")
    List<Event> findActiveEventsByOrganisation(
        @Param("orgId") Long orgId,
        @Param("status") EventStatus status
    );

    // Native SQL query
    @Query(value = "SELECT * FROM event WHERE " +
                   "start_date BETWEEN :start AND :end " +
                   "AND organisation_id = :orgId",
           nativeQuery = true)
    List<Event> findEventsByDateRange(
        @Param("start") LocalDate start,
        @Param("end") LocalDate end,
        @Param("orgId") Long orgId
    );
}

Repository Features:

Query Method Derivation - Automatically generates queries from method names
Custom JPQL - Write custom queries using Java Persistence Query Language
Native SQL - Use native SQL when needed for complex queries
Pagination - Built-in pagination support via Pageable
Sorting - Built-in sorting support via Sort
Projections - Return DTOs instead of entities

3.4. Entity Base Classes

Common entity features are provided through base classes:

@MappedSuperclass
public abstract class AbstractAuditableEntity {

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

    @CreatedDate
    @Column(nullable = false, updatable = false)
    private Instant createdDate;

    @CreatedBy
    @Column(nullable = false, updatable = false)
    private String createdBy;

    @LastModifiedDate
    @Column(nullable = false)
    private Instant lastModifiedDate;

    @LastModifiedBy
    @Column(nullable = false)
    private String lastModifiedBy;

    @Version
    private Long version;

    // Getters and setters
}

Base Class Features:

Audit Fields - Automatic tracking of created/modified timestamps and users
Optimistic Locking - Version field for concurrency control
ID Generation - Standardized identity generation strategy

3.5. Multi-Tenancy Support

All major entities include organisation scoping:

@Entity
@Table(name = "event")
public class Event extends AbstractAuditableEntity {

    @ManyToOne(optional = false)
    @JoinColumn(name = "organisation_id", nullable = false)
    private Organisation organisation;

    @Column(nullable = false, length = 200)
    private String name;

    @Column(nullable = false)
    private LocalDate startDate;

    @Column(nullable = false)
    private LocalDate endDate;

    // Other fields, getters, setters
}

Multi-Tenancy Features:

Organisation foreign key on major entities
Security filters applied at service layer
Repository queries include organisation scope
Data isolation between organisations

3.6. Validation Constraints

Bean Validation annotations ensure data integrity:

@Entity
@Table(name = "event_participant")
public class EventParticipant extends AbstractAuditableEntity {

    @ManyToOne(optional = false)
    @JoinColumn(name = "event_id", nullable = false)
    @NotNull(message = "Event is required")
    private Event event;

    @ManyToOne(optional = false)
    @JoinColumn(name = "person_id", nullable = false)
    @NotNull(message = "Person is required")
    private Person person;

    @Column(nullable = false, length = 50)
    @NotBlank(message = "Status is required")
    private String status;

    @Column(nullable = false)
    @PastOrPresent(message = "Registration date cannot be in the future")
    private LocalDate registrationDate;

    @Min(value = 0, message = "Race number must be positive")
    private Integer raceNumber;

    // Other fields, getters, setters
}

Common Validation Annotations:

@NotNull - Field cannot be null
@NotBlank - String cannot be null or empty
@Size(min, max) - String length constraints
@Min / @Max - Numeric range constraints
@Past / @Future / @PastOrPresent - Date constraints
@Email - Email format validation
@Pattern(regexp) - Regex pattern matching

3.7. Hibernate Configuration

Hibernate is configured through application properties:

# JPA/Hibernate Configuration
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

# Connection Pool (HikariCP)
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000

# Flyway Migration
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true

Key Configuration Options:

ddl-auto=validate - Validates schema against entities (production setting)
show-sql=false - Disable SQL logging in production
batch_size=20 - Batch INSERT/UPDATE operations for performance
HikariCP - High-performance connection pool
Flyway - Database schema version management

4. wordpress-database

Legacy database library containing WordPress and WooCommerce entities for backward compatibility.

Repository: https://github.com/christhonie/wordpress-database

Maven Coordinates:

<groupId>za.co.idealogic</groupId>
<artifactId>wordpress-database</artifactId>
<version>${revision}</version>

Package: za.co.idealogic.wordpress

Deprecation Notice

The wordpress-database component is scheduled for deprecation within 6 months. All WordPress-based user and person data will be migrated to the primary event-database entities.

Migration Timeline:

Months 1-3: Dual-write mode (write to both databases)
Months 3-5: Data migration and validation
Month 6: Complete transition to event-database entities

After migration, the wordpress-database component will be archived.

4.1. Entity Overview

The wordpress-database contains 14 entities:

WordPress Core Entities:

User
UserMeta
Post
PostMeta
Term
TermTaxonomy
TermRelationship

WooCommerce Entities:

WooCommerceOrder (extends Post)
WooCommerceProduct (extends Post)
OrderItem
OrderItemMeta

Custom Entities:

WPAllInOneEvent
WPAllInOneCategory

4.2. Inheritance Hierarchies

The WordPress entities use JPA inheritance strategies:

@Entity
@Table(name = "wp_posts")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "post_type", discriminatorType = DiscriminatorType.STRING)
public abstract class AbstractPost {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "post_title")
    private String postTitle;

    @Column(name = "post_content", columnDefinition = "LONGTEXT")
    private String postContent;

    @Column(name = "post_type", insertable = false, updatable = false)
    private String postType;

    // Other fields, getters, setters
}

@Entity
@DiscriminatorValue("shop_order")
public class WooCommerceOrder extends AbstractPost {

    @Column(name = "order_total")
    private BigDecimal orderTotal;

    @Column(name = "order_currency")
    private String currency;

    // Order-specific fields
}

@Entity
@DiscriminatorValue("product")
public class WooCommerceProduct extends AbstractPost {

    @Column(name = "product_price")
    private BigDecimal price;

    @Column(name = "product_sku")
    private String sku;

    // Product-specific fields
}

Inheritance Strategy: SINGLE_TABLE with discriminator column post_type

4.3. Migration Strategy

The migration from wordpress-database to event-database follows a phased approach:

Phase 1: Dual-Write Mode (Months 1-3)

@Service
@Transactional
public class UserMigrationService {

    private final PersonRepository personRepository;     // event-database
    private final WordPressUserRepository wpUserRepo;    // wordpress-database

    public Person createUser(UserCreateRequest request) {
        // Write to event-database (primary)
        Person person = new Person();
        person.setFirstName(request.getFirstName());
        person.setLastName(request.getLastName());
        person.setEmail(request.getEmail());
        person = personRepository.save(person);

        // Also write to wordpress-database (legacy)
        WordPressUser wpUser = new WordPressUser();
        wpUser.setUserLogin(request.getEmail());
        wpUser.setUserEmail(request.getEmail());
        wpUser.setDisplayName(request.getFirstName() + " " + request.getLastName());
        wpUserRepo.save(wpUser);

        return person;
    }
}

Phase 2: Data Migration (Months 3-5)

@Service
public class DataMigrationService {

    public void migrateWordPressUsers() {
        List<WordPressUser> wpUsers = wpUserRepo.findAll();

        for (WordPressUser wpUser : wpUsers) {
            // Check if already migrated
            Optional<Person> existing = personRepository
                .findByEmail(wpUser.getUserEmail());

            if (existing.isEmpty()) {
                // Migrate to Person entity
                Person person = new Person();
                person.setEmail(wpUser.getUserEmail());
                // Map other fields...
                personRepository.save(person);
            }
        }
    }
}

Phase 3: Cutover (Month 6)

Remove wordpress-database dependency from event-database
Remove wordpress-database dependency from admin-service
Update parent POM to remove wordpress-database from dependency management
Archive wordpress-database repository

5. Database Schema Management

5.1. Flyway Migrations

Database schema changes are managed using Flyway migrations:

Migration File Structure:

src/main/resources/db/migration/
├── V1_0__initial_schema.sql
├── V1_1__add_event_category_table.sql
├── V1_2__add_race_type_config.sql
└── V2_0__add_membership_tables.sql

Migration File Naming Convention:

V{version}__Database library components for JPA entity management in the Event and Membership Administration System.sql
Version uses underscore separator (e.g., V1_0, V1_1, V2_0)
Description uses double underscore separator
Description uses snake_case

Example Migration:

-- V1_1__add_event_category_table.sql

CREATE TABLE event_category (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT NOT NULL,
    category_id BIGINT,
    min_age INT,
    max_age INT,
    gender VARCHAR(10),
    created_date TIMESTAMP NOT NULL,
    created_by VARCHAR(100) NOT NULL,
    last_modified_date TIMESTAMP NOT NULL,
    last_modified_by VARCHAR(100) NOT NULL,
    version BIGINT NOT NULL DEFAULT 0,

    CONSTRAINT fk_event_category_event
        FOREIGN KEY (event_id) REFERENCES event(id),
    CONSTRAINT fk_event_category_category
        FOREIGN KEY (category_id) REFERENCES category(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_event_category_event ON event_category(event_id);
CREATE INDEX idx_event_category_category ON event_category(category_id);

5.2. Schema Validation

In production, Hibernate validates the schema against entity definitions:

spring.jpa.hibernate.ddl-auto=validate

Validation Rules:

Entity class name must match table name (or @Table(name) annotation)
Field names must match column names (or @Column(name) annotation)
Relationships must match foreign keys
Constraints must match validation annotations

6. Performance Optimization

6.1. Query Optimization

Use JOIN FETCH for associations:

@Query("SELECT e FROM Event e " +
       "LEFT JOIN FETCH e.categories " +
       "LEFT JOIN FETCH e.raceTypes " +
       "WHERE e.organisation.id = :orgId")
List<Event> findEventsWithCategories(@Param("orgId") Long orgId);

Use pagination for large result sets:

Page<Event> findByOrganisationId(Long organisationId, Pageable pageable);

Use projections for selective field loading:

public interface EventSummary {
    Long getId();
    String getName();
    LocalDate getStartDate();
}

List<EventSummary> findByOrganisationId(Long organisationId);

6.2. Caching Strategy

Entity-level caching:

@Entity
@Table(name = "category")
@Cacheable
@org.hibernate.annotations.Cache(
    usage = CacheConcurrencyStrategy.READ_WRITE
)
public class Category extends AbstractAuditableEntity {
    // Entity fields
}

Query caching:

@Query("SELECT c FROM Category c WHERE c.organisation.id = :orgId")
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
List<Category> findByOrganisation(@Param("orgId") Long orgId);

6.3. Batch Operations

Batch inserts/updates:

@Service
@Transactional
public class BulkOperationService {

    public void createParticipants(List<ParticipantRequest> requests) {
        List<EventParticipant> participants = requests.stream()
            .map(this::mapToEntity)
            .collect(Collectors.toList());

        // Hibernate will batch these operations
        participantRepository.saveAll(participants);
        participantRepository.flush();
    }
}

Configuration for batching:

spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

7. Testing

7.1. Repository Testing with Testcontainers

@DataJpaTest
@Testcontainers
@ActiveProfiles("test")
public class EventRepositoryTest {

    @Container
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @Autowired
    private EventRepository eventRepository;

    @DynamicPropertySource
    static void properties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", mysql::getJdbcUrl);
        registry.add("spring.datasource.username", mysql::getUsername);
        registry.add("spring.datasource.password", mysql::getPassword);
    }

    @Test
    void shouldFindEventsByOrganisation() {
        // Given
        Event event = new Event();
        event.setName("Test Event");
        event.setOrganisation(createTestOrganisation());
        eventRepository.save(event);

        // When
        List<Event> events = eventRepository
            .findByOrganisationId(event.getOrganisation().getId());

        // Then
        assertThat(events).hasSize(1);
        assertThat(events.get(0).getName()).isEqualTo("Test Event");
    }
}