Skip to content

Database schema

PerfShop persists its data in MySQL 8 through JPA / Hibernate, with a schema versioned by Flyway (V1 to V38 migrations). This page describes the seven main entities, their relationships, and the schema evolution across migrations.

Source of truth

The diagrams and tables on this page are extracted directly from the @Entity classes of the com.perfshop.entity package and from the SQL files in the backend/src/main/resources/db/migration/ folder. Column names, types and constraints strictly match the code and migrations.

JPA entities

The data model contains seven persistent entities, organized into two islands:

  • The e-commerce island: User, Product, CartItem, Order, OrderItem — this is the classic shop with products, cart and orders.
  • The administration and pedagogical island: AdminUser, PedagogiqueSessionEntity — instructor accounts and student session persistence.
erDiagram
  USERS ||--o{ CART_ITEMS : "adds to cart"
  USERS ||--o{ ORDERS : "places"
  ORDERS ||--o{ ORDER_ITEMS : "contains"
  PRODUCTS ||--o{ CART_ITEMS : "referenced by"
  PRODUCTS ||--o{ ORDER_ITEMS : "sold via"

  USERS {
    BIGINT      id PK
    VARCHAR_100 email UK
    VARCHAR_255 password "BCrypt strength 10"
    VARCHAR_100 first_name
    VARCHAR_100 last_name
    VARCHAR_5   civility "M / Mme / Mx"
    DATE        birth_date
    VARCHAR_20  phone
    TEXT        street
    VARCHAR_5   postal_code "intentionally short — business chaos"
    VARCHAR_100 city
    VARCHAR_100 region
    CHAR_2      country "ISO 3166-1 alpha-2, default FR"
    DATETIME    created_at
    DATETIME    last_login
    BOOLEAN     is_pedagogique "BAC1-5 agent"
  }

  PRODUCTS {
    BIGINT       id PK
    VARCHAR_200  name
    VARCHAR_1000 description
    DECIMAL_10_2 price
    INT          stock
    VARCHAR_50   category
    VARCHAR_500  image_url
    DATETIME     created_at
    DATETIME     updated_at
    BOOLEAN      is_pedagogique "secret journey product"
    BOOLEAN      pedagogique_protected "stock never decremented"
  }

  CART_ITEMS {
    BIGINT       id PK
    BIGINT       user_id FK
    VARCHAR_255  session_id "anonymous cart"
    BIGINT       product_id FK
    INT          quantity
    DECIMAL_10_2 price
    DATETIME     added_at
  }

  ORDERS {
    BIGINT       id PK
    VARCHAR_50   order_number UK
    BIGINT       user_id FK
    DECIMAL_10_2 total_amount
    VARCHAR_20   status "PENDING/CONFIRMED/.../CANCELLED"
    VARCHAR_500  shipping_address
    VARCHAR_50   shipping_method
    VARCHAR_50   payment_method
    VARCHAR_4    card_last4 "last 4 digits only"
    BOOLEAN      cvv_verified
    BOOLEAN      is_test_data "pedagogical agent order"
    DATETIME     created_at
    DATETIME     updated_at
  }

  ORDER_ITEMS {
    BIGINT       id PK
    BIGINT       order_id FK
    BIGINT       product_id FK
    INT          quantity
    DECIMAL_10_2 unit_price
    DECIMAL_10_2 subtotal
  }

Tables outside the e-commerce island

erDiagram
  ADMIN_USERS {
    BIGINT      id PK
    VARCHAR_100 email UK
    VARCHAR_255 password_hash "BCrypt strength 10"
    BOOLEAN     can_access_chaos
    BOOLEAN     can_access_monitoring
    BOOLEAN     can_access_admin
    BOOLEAN     can_access_jmeter
    BOOLEAN     can_access_scripts
    BOOLEAN     is_superadmin "indestructible"
    DATETIME    created_at
  }

  PEDAGOGIQUE_SESSIONS {
    VARCHAR_36  token PK "full UUID"
    VARCHAR_100 alias
    VARCHAR_10  level "bac1 .. bac5"
    BIGINT      joined_at "epoch ms"
    BIGINT      completed_at "0 if in progress"
    INT         current_step
    VARCHAR_10  agent_code "4 hex digits"
    VARCHAR_64  extraction_answer_hash "SHA-256"
    VARCHAR_20  logique_question_indices "ex: 15,10,18,19,14"
    VARCHAR_64  logique_expected_hash "SHA-256"
    TEXT        attempts_json "JSON of attempts per step"
    TIMESTAMP   created_at
  }

  PERFSHOP_LICENSE {
    BIGINT      id PK
    TEXT        license_key
    DATETIME    uploaded_at
  }

No foreign key from pedagogique_sessions to users

Pedagogical sessions are identified only by their UUID token (HTTP header X-Student-Token). No user account is required to join a journey — the student simply clicks the link provided by the instructor. This absence of FK is intentional and documented in the V36 migration.

Class diagram — Java view

For developers reading the code, here is the same information as a UML class diagram with explicit JPA relationships:

classDiagram
  class User {
    +Long id
    +String email
    +String password
    +String firstName
    +String lastName
    +String civility
    +LocalDate birthDate
    +String street
    +String postalCode
    +String city
    +String country
    +LocalDateTime createdAt
    +LocalDateTime lastLogin
    +boolean isPedagogique
    +onCreate()
  }

  class Product {
    +Long id
    +String name
    +String description
    +BigDecimal price
    +Integer stock
    +String category
    +String imageUrl
    +boolean isPedagogique
    +boolean pedagogiqueProtected
    +onCreate()
    +onUpdate()
  }

  class CartItem {
    +Long id
    +User user
    +String sessionId
    +Product product
    +Integer quantity
    +BigDecimal price
    +LocalDateTime addedAt
  }

  class Order {
    +Long id
    +String orderNumber
    +User user
    +BigDecimal totalAmount
    +OrderStatus status
    +String shippingAddress
    +String paymentMethod
    +String cardLast4
    +boolean cvvVerified
    +boolean testData
    +List~OrderItem~ orderItems
  }

  class OrderItem {
    +Long id
    +Order order
    +Product product
    +Integer quantity
    +BigDecimal unitPrice
    +BigDecimal subtotal
  }

  class AdminUser {
    +Long id
    +String email
    +String passwordHash
    +boolean canAccessChaos
    +boolean canAccessMonitoring
    +boolean canAccessAdmin
    +boolean canAccessJmeter
    +boolean canAccessScripts
    +boolean superAdmin
  }

  class PedagogiqueSessionEntity {
    +String token
    +String alias
    +String level
    +long joinedAt
    +long completedAt
    +int currentStep
    +String agentCode
    +String extractionAnswerHash
    +String attemptsJson
    +displayAlias() String
  }

  User "1" --> "*" CartItem : owns
  User "1" --> "*" Order : places
  Order "1" --> "*" OrderItem : contains
  Product "1" --> "*" CartItem : referenced
  Product "1" --> "*" OrderItem : sold

Lombok and JPA annotations

All entities use the Lombok @Data (or @Getter/@Setter) annotations to generate accessors, and standard JPA for the mapping:

Annotation Typical use in PerfShop
@Entity + @Table(name=...) Class ↔ SQL table mapping
@Id + @GeneratedValue(strategy=IDENTITY) Auto-incremented PK
@Column(name=..., length=..., nullable=..., unique=...) Column constraints
@Index(name=..., columnList=...) Index for performance and business chaos
@ManyToOne(fetch=LAZY/EAGER) + @JoinColumn N-to-1 relationships
@OneToMany(mappedBy=..., cascade=ALL, orphanRemoval=true) 1-to-N relationships (Order → OrderItem)
@Enumerated(EnumType.STRING) Storing enums (e.g. OrderStatus) as VARCHAR
@PrePersist / @PreUpdate Hooks on created_at / updated_at
@Serial + Serializable Marking session-portable entities

Notable indexes and constraints

Table Index Justification
users idx_email (UNIQUE) Login lookup + email uniqueness
users idx_country Filtering by country (business chaos A1–A16)
products idx_category Catalog filtering by category
products idx_name Simple full-text search
cart_items idx_user_id, idx_session_id Cart retrieval (logged-in user OR anonymous session)
orders idx_user_id, idx_order_number (UNIQUE), idx_created_at User order history, number lookup, chronological sort
admin_users idx_admin_email (UNIQUE) Admin login and uniqueness
pedagogique_sessions idx_ped_sessions_level, idx_ped_sessions_joined_at, idx_ped_sessions_completed Real-time instructor stats (by level, by time cohort, by state)

Pedagogically 'broken' constraints

Some columns have lengths deliberately set too short to support business chaos scenarios:

  • users.postal_code VARCHAR(5) — the V28__reduce_postal_code_for_chaos.sql migration shrinks this field from 10 to 5 characters. Business chaos at level 2 and above deliberately injects a value longer than 5 characters to trigger a DataTruncation SQLException on the JDBC side.
  • users.country CHAR(2) — strict ISO 3166-1 alpha-2; any invalid value is rejected by ValidationService.validateCountry().

Flyway migrations

The schema is versioned by 38 Flyway migrations in backend/src/main/resources/db/migration/. Flyway is enabled via spring.flyway.enabled=true and baseline-on-migrate=true (see application.yml). Migrations are loaded from classpath:db/migration-${PERFSHOP_LANG:fr} — there are thus two parallel sets of migrations FR and EN for test data (translated product catalog), while sharing the schema migrations.

Chronological view grouped by theme

Theme Migrations Role
Initial schema V1, V2 Creation of the 5 e-commerce tables + minimal test data set
Product catalog — extensions V5, V7, V12, V13, V14, V15, V16, V17, V18, V19, V21, V23, V24 Progressive addition of categories: computers, planes, cars, businesses, helicopters, boats, motorcycles
Product images V6, V8, V20 Migration of image URLs to local paths (/images/products/...)
Category normalization V22, V25 Harmonizing category labels (bateau → bateaux, etc.)
Order status V3 Fix for the OrderStatus enum
Schema — price precision V11 Switch from DECIMAL(8,2) to DECIMAL(10,2)
Extended user profile V9, V10, V26, V27 Adding profile fields (civility, birth date, full address, country), fixing the civility ENUM type
Business chaos — postal code V28 Reducing postal_code to VARCHAR(5) to enable the pedagogical DataTruncation
Security — password hashes V29 Migrating cleartext passwords to BCrypt strength 10
Admin accounts V30, V31 Creating the admin_users table then adding the can_access_jmeter and can_access_scripts rights
License V32 Creating the perfshop_license table
Pedagogical — products and infrastructure V33, V34 Secret pedagogical products + stock protection + BAC1-5 agent accounts
Pedagogical — test data flag V35 is_test_data column on orders (orders from pedagogical agents excluded from default stats)
Pedagogical — persistent sessions V36 pedagogique_sessions table (write-through architecture)
Pedagogical — prefilled agents V37 Inserting BAC1-5 agent profiles (espionage-themed names)
Pedagogical — logic theme V38 logique_question_indices and logique_expected_hash columns (computed at /join, validated server-side)

Naming convention

V<number>__<snake_case_description>.sql

Examples: V1__Initial_schema.sql, V28__reduce_postal_code_for_chaos.sql, V36__add_pedagogique_sessions_table.sql.

Reading migrations in order

Flyway migrations apply in the numerical order of the V<n> prefixes. The chronological progression of the db/migration/ folder tells the actual story of PerfShop's evolution: you can see the move from a simple shop to a multi-level pedagogical platform, with the progressive addition of support for each chaos family.

Connection and pool

The MySQL connection is configured in application.yml:

Setting Value
Driver com.mysql.cj.jdbc.Driver
URL (compose) jdbc:mysql://perfshop-db:3306/perfshop?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
Pool HikariCP, name PerfShopHikariPool
maximum-pool-size 20
minimum-idle 5
connection-timeout 30 s
idle-timeout 10 min
max-lifetime 30 min
ddl-auto validate (Hibernate validates the schema at startup, does not modify it)
dialect org.hibernate.dialect.MySQLDialect
SQL format format_sql=true, use_sql_comments=true
SQL log level WARN (Hibernate SQL and binders set to WARN deliberately, to keep the heap from growing while idle)

The HikariCP pool is exposed as Prometheus metrics through Micrometer (hikaricp_connections_active, hikaricp_connections_idle, hikaricp_connections_max, hikaricp_connections_pending, hikaricp_connections_acquire_seconds). These metrics feed the Student and Instructor Backend dashboards — see observability/dashboards.md.