Database schema¶

The PerfShop MySQL 8 database is managed by Flyway. Migrations are located in backend/src/main/resources/db/migration-fr/ and are versioned from V1 to V10. The choice of the migration-fr folder instead of migration is deliberate: this is a schema consolidated in its final state, with no ALTER TABLE history. This approach makes the schema easier to read pedagogically (an instructor can understand it by reading a single file) and speeds up clean deployments.

Sources

backend/src/main/resources/db/migration-fr/V1__schema.sql to V10__data_pedagogique.sql

Overview¶

Migration	Size	Role
`V1__schema.sql`	13 KB	Creation of every table in its final state
`V2__data_users.sql`	3 KB	Initial user accounts (demo agents, test accounts)
`V3__data_informatique.sql`	44 KB	Catalog seed — Computing category (~200 products)
`V4__data_avions.sql`	46 KB	Catalog seed — Aircraft category
`V5__data_voitures.sql`	46 KB	Catalog seed — Cars category
`V6__data_entreprises.sql`	65 KB	Catalog seed — Companies category (valuations)
`V7__data_helicopteres.sql`	29 KB	Catalog seed — Helicopters category
`V8__data_bateaux.sql`	8 KB	Catalog seed — Boats category
`V9__data_motos.sql`	7 KB	Catalog seed — Motorcycles category
`V10__data_pedagogique.sql`	7 KB	Pedagogical infrastructure: 4 products + 5 BAC1-BAC5 agents

The full catalog totals roughly 1,000 products spread across seven heterogeneous categories. This variety is intentional: it allows pedagogical enigmas to exploit distinctive search criteria and load tests to simulate realistic volumes.

Entity-relationship diagram¶

erDiagram
  users ||--o{ cart_items    : "owns"
  users ||--o{ orders        : "places"
  products ||--o{ cart_items : "referenced by"
  products ||--o{ order_items: "referenced by"
  orders ||--o{ order_items  : "contains"

  users {
    BIGINT id PK
    VARCHAR email UK
    VARCHAR password "BCrypt $2b$10$"
    VARCHAR first_name
    VARCHAR last_name
    VARCHAR civility "M | Mme | Mx"
    DATE birth_date
    VARCHAR phone
    TEXT street
    VARCHAR postal_code "VARCHAR(5) — Pedagogical Chaos N2+ target"
    VARCHAR city
    VARCHAR region
    VARCHAR country "ISO2, default FR"
    DATETIME created_at
    DATETIME last_login
    BOOLEAN is_pedagogique "agents hidden from UI"
  }

  products {
    BIGINT id PK
    VARCHAR name
    VARCHAR description
    DECIMAL price "DECIMAL(18,2)"
    INT stock
    VARCHAR category
    VARCHAR image_url
    DATETIME created_at
    DATETIME updated_at
    BOOLEAN is_pedagogique "products hidden from UI"
    BOOLEAN pedagogique_protected "stock not decremented"
  }

  cart_items {
    BIGINT id PK
    BIGINT user_id FK
    VARCHAR session_id "anonymous cart"
    BIGINT product_id FK
    INT quantity
    DECIMAL price
    DATETIME added_at
  }

  orders {
    BIGINT id PK
    VARCHAR order_number UK
    BIGINT user_id FK
    DECIMAL total_amount
    ENUM status "PENDING..CANCELLED"
    VARCHAR shipping_address
    VARCHAR shipping_method
    VARCHAR payment_method
    VARCHAR card_last4 "never the CVV"
    TINYINT cvv_verified
    BOOLEAN is_test_data "agent orders excluded"
    DATETIME created_at
    DATETIME updated_at
  }

  order_items {
    BIGINT id PK
    BIGINT order_id FK
    BIGINT product_id FK
    INT quantity
    DECIMAL unit_price
    DECIMAL subtotal
  }

  admin_users {
    BIGINT id PK
    VARCHAR email UK
    VARCHAR password_hash "BCrypt"
    BOOLEAN can_access_chaos
    BOOLEAN can_access_monitoring
    BOOLEAN can_access_admin
    BOOLEAN can_access_jmeter
    BOOLEAN can_access_scripts
    BOOLEAN is_superadmin
    DATETIME created_at
  }

  perfshop_license {
    VARCHAR id PK "always current"
    TEXT license_key
    VARCHAR plan "functional | performance | enterprise"
    VARCHAR holder
    DATE issued_at
    DATE expires_at "NULL = unlimited"
    DATETIME activated_at
    TINYINT revoked
  }

  pedagogique_sessions {
    VARCHAR token PK "UUID"
    VARCHAR alias
    VARCHAR level "bac1..bac5"
    BIGINT joined_at "epoch ms"
    BIGINT completed_at
    INT current_step
    VARCHAR agent_code
    VARCHAR extraction_answer_hash
    VARCHAR logique_question_indices
    VARCHAR logique_expected_hash
    TEXT attempts_json
    TIMESTAMP created_at
  }

The admin_users, perfshop_license, and pedagogique_sessions tables are intentionally detached from the main graph — they handle cross-cutting aspects (instructor authentication, license, pedagogical journey) that do not fit into the e-commerce model.

V1 — Complete schema¶

V1__schema.sql creates all tables in their final state. No ALTER TABLE is run afterwards. This consolidation is a deliberate rewrite of the historical schema (which had 38 incremental migrations) to offer students a clearer read.

Table `users`¶

Identifies the shop's customers. Contains classic fields (email, BCrypt password, last name, first name) enriched by Business Chaos extensions:

civility: VARCHAR(5) and not ENUM — Hibernate validates the schema against VARCHAR. The business constraint (M, Mme, Mx) is enforced on the application side by ValidationService.
birth_date: date of birth, used by Business Chaos for rules like the loyalty discount.
Decomposed address: street, postal_code, city, region, country (instead of a single address field). This breakdown is required by several business chaos cases.
postal_code limited to 5 characters: intentional limit. Pedagogical Chaos Level 2+ intentionally tries to insert a longer value, which triggers a MySQL DataTruncation observable in logs and metrics — a pedagogical demonstration of a "silent bug under chaos".
country: ISO2, default FR.
is_pedagogique: flag that identifies the "agent" accounts created by V10. These accounts are hidden in the admin UI and their orders are excluded from statistics by default.

Indexes: idx_email (unique), idx_country, idx_is_pedagogique.

Table `products`¶

E-commerce catalog. The pedagogical extensions:

price: DECIMAL(18,2) — extended precision for company valuations (billions). Standard catalog products remain at normal precision.
is_pedagogique: products hidden in the admin UI — the 4 products created by V10 that students use to solve enigmas.
pedagogique_protected: flag that prevents OrderService from decrementing the stock on order creation. Used on products whose enigmas depend on the exact stock (e.g. Basic USB-C Cable 1m has stock=60, enigma BAC1-2 computes 8 × 60 = 480).

Indexes: idx_category, idx_name, idx_prod_pedagogique.

Table `cart_items`¶

Cart. Supports two cases:

Authenticated cart — user_id populated, session_id null
Anonymous cart — session_id populated, user_id null

The FK on user_id allows NULL to support anonymous carts. On login, CartService migrates items from the anonymous cart to the user account.

Table `orders`¶

Order. The status is a real MySQL ENUM with six values: PENDING, CONFIRMED, PROCESSING, SHIPPED, DELIVERED, CANCELLED.

Fields dedicated to payment traceability without ever storing sensitive data:

card_last4: last 4 digits of the card (user display)
cvv_verified: boolean — has the CVV been verified? The real CVV is never stored

The is_test_data field marks orders generated by pedagogical agents. By default, they are excluded from stats displayed in the admin UI, to avoid polluting reports with training orders.

Table `order_items`¶

Order line. quantity, unit_price and subtotal capture the amount at the time of ordering (later catalog prices do not affect it).

Table `admin_users`¶

Instructor accounts. Five rights flags (can_access_chaos, can_access_monitoring, can_access_admin, can_access_jmeter, can_access_scripts) and an is_superadmin flag. The superadmin enjoys hard-coded protections in AdminUserService — see Admin authentication.

No INSERT in V1: superadmin bootstrap is handled by AdminUserService @EventListener(ApplicationReadyEvent) from the PERFSHOP_ADMIN_EMAIL and PERFSHOP_ADMIN_PASSWORD environment variables. This separation avoids having a BCrypt hash hard-coded in the migration SQL.

Table `perfshop_license`¶

Stores the active license. Constraint id = 'current': a single row at a time. Upserts from LicenseService.activateLicense() do an UPDATE first then an INSERT if the row does not exist. A CHECK constraint validates the plan. See License system.

Table `pedagogique_sessions`¶

Individual sessions of the pedagogical journey. Identified by a token UUID — no FK to users because sessions do not require a user account. Write-through architecture: the database is always the source of truth, and an optional memory cache can be enabled on the fly by the instructor (see Pedagogical journey concept and architecture).

Two logic columns (logique_question_indices, logique_expected_hash) store the logic questions drawn once at /join — this eliminates any front/back desynchronization.

The joined_at and completed_at timestamps are in epoch milliseconds (BIGINT), consistent with Instant.toEpochMilli() on the Java side.

attempts_json is a TEXT (without DEFAULT — MySQL 8 strict mode forbids it on TEXT) that stores a flat JSON { "bac1-0": 2, "bac1-1": 1, … } counting attempts per step.

V2 — User data¶

Creates demonstration accounts: a few test users with varied profiles (civility, date of birth, addresses in several countries) to feed functional test scenarios and business chaos cases.

V3 through V9 — Catalog seeds¶

Seven migrations, one per major catalog category:

Migration	Category	Approximate volume
V3	Computing	~200 products (computers, peripherals, components, storage, network…)
V4	Aircraft	~150 products
V5	Cars	~150 products
V6	Companies	~230 products (with DECIMAL(18,2) valuations)
V7	Helicopters	~170 products
V8	Boats	~50 products
V9	Motorcycles	~50 products

The total catalog sits around 1,000 products. This volume is calibrated to:

Fit comfortably in JVM memory with a default heap
Make a Performance Chaos visible (slow pagination, missing index)
Offer enough diversity for the search criteria of pedagogical enigmas

Product images are served locally from the backend container (/images/...) to avoid any external dependency.

V10 — Pedagogical infrastructure¶

This migration is critical and contains untouchable elements tied to the enigmas. It combines four dimensions:

1. Four pedagogical products¶

These products have is_pedagogique = TRUE and pedagogique_protected = TRUE. They are invisible in the admin UI and their stocks are never decremented. Each product is carefully built to serve a specific enigma:

Product	Price	Stock	Enigma
Basic USB-C Cable 1m	€22.00	60	BAC1-1 (single price filter), BAC1-2 (stock × 8 bits)
CODE Pro USB-C Adapter	€64.00	9999	BAC2-3 (CODE search), BAC2-4 (2⁶ = 64)
Lorem NVMe SSD 1TB PCIe 4.0	€255.00	32	BAC4-2 (hex ASCII header), BAC4-3 (0xFF = ff)
Rainbow RGB Braided Cable 2m	€34.99	9999	BAC5-2 (ROT13 "Follow the rainbow")

Prices and stocks are referenced exactly by the enigmas. Any modification breaks the journeys — the V10 migration contains an explicit warning on this point.

2. Five BAC1 to BAC5 agent accounts¶

Emails are agent.bac1@perfshop.io to agent.bac5@perfshop.io. The BCrypt hashes are untouchable — they correspond to passwords that each journey reveals to the student:

Agent	Password	Hint
`agent.bac1`	`Cable22Go`	Given in clear at step 7 of BAC1
`agent.bac2`	`Code64Exp6`	Built by deduction at steps 4 and 5 of BAC2
`agent.bac3`	`Docker8080Get`	Base64-encoded in the statement of step 15 of BAC3
`agent.bac4`	`Lorem255FF42`	Base64-encoded in the statement of step 19 of BAC4
`agent.bac5`	`Rsa3Xor51Pi14`	Built by computation (XOR + Pi) in BAC5

Students reuse these credentials to authenticate on the shop in the final step of their journey and trigger the success page.

3. Complete agent profiles¶

Each agent receives a full spy-themed profile (borrowed from V37 of the historical schema): codename inspired by espionage or cryptography characters, French address, phone number built from symbolic numbers, themed postal code.

Agent	Name	City	Postal code
BAC1	Jason Bourne	Paris	75007
BAC2	Alan Enigma	Lyon	69001
BAC3	Lisbeth Docker	Bordeaux	33000
BAC4	Ethan Lorem	Toulouse	31000
BAC5	Ada Cipher	Nantes	44000

4. `pedagogique_protected` flags on standard products¶

Three products from the standard catalog (Basic USB Keyboard, Basic Optical Mouse, DDR4 16GB Kit (2x8GB)) receive the pedagogique_protected = TRUE flag. Their stock is no longer decremented by OrderService. These products are used in BAC3 enigmas (keyboards and mice) and BAC5 (DDR4 kit).

For the DDR4 kit, the stock is forced to 9999 because BAC5-22/23 enigmas don't compute on its stock. For the other two, the real stock is preserved because the enigmas depend on it.

Indexes and performance¶

Indexes placed in V1__schema.sql reflect the queries expected by the Spring Boot services:

Table	Index	Target query
`users`	`idx_email` (unique)	Login, email lookup
`users`	`idx_country`	Business chaos country filters
`users`	`idx_is_pedagogique`	Agent exclusion in the admin UI
`products`	`idx_category`	Category filter in the catalog
`products`	`idx_name`	Approximate text search
`products`	`idx_prod_pedagogique`	Pedagogical product exclusion
`cart_items`	`idx_user_id`	User cart retrieval
`cart_items`	`idx_session_id`	Anonymous cart retrieval
`orders`	`idx_order_number` (unique)	Lookup by order number
`orders`	`idx_user_id`	Order history
`orders`	`idx_created_at`	Chronological sort and time-based stats
`orders`	`idx_is_test_data`	Fast exclusion of test orders
`admin_users`	`idx_admin_email` (unique)	Admin login
`pedagogique_sessions`	`idx_ped_sessions_level`	Per-level stats
`pedagogique_sessions`	`idx_ped_sessions_joined_at`	Sort by age, purge
`pedagogique_sessions`	`idx_ped_sessions_completed`	Completed sessions filter

Encoding and engine¶

All tables use:

Engine: InnoDB (transactions, FK constraints)
Charset: utf8mb4
Collation: utf8mb4_unicode_ci

Choosing utf8mb4 (and not utf8) is mandatory to support emojis in product descriptions and user names. MySQL treats utf8 as a legacy 3-byte encoding.

Bootstrapping an empty instance¶

On first startup, Flyway runs all V1 to V10 migrations in order. After V10, the database contains:

A users table with the V2 test accounts plus the 5 pedagogical agents
A products table with ~1,000 products from the V3-V9 catalogs plus the 4 V10 pedagogical products
Empty tables: cart_items, orders, order_items, pedagogique_sessions, perfshop_license
An empty admin_users table — the superadmin is created right after by Spring (ApplicationReadyEvent)

No manual intervention is needed: the platform is immediately usable after docker compose up.