# Handoff Report — Database Schema & Migration Strategy for `progression_benchmarks`

**Agent**: `explorer_m1_progression_2`  
**Recipient**: `orchestrator_4` (`6f4a2aa2-4315-4660-8cb7-8352a7220c95`)  
**Date**: 2026-10-01  
**Handoff Type**: Hard (Task Complete)  

---

## 1. Observation

1. **Existing DDL Definition (`server/world/game_design_matrix_schema.py:131-140`)**:
   ```sql
   CREATE TABLE IF NOT EXISTS progression_benchmarks (
       level INTEGER PRIMARY KEY CHECK (level BETWEEN 1 AND 100),
       target_exp INTEGER NOT NULL CHECK (target_exp > 0),
       player_base_hp REAL NOT NULL CHECK (player_base_hp > 0),
       player_benchmark_dps REAL NOT NULL CHECK (player_benchmark_dps > 0),
       monster_base_hp REAL NOT NULL CHECK (monster_base_hp > 0),
       monster_base_dps REAL NOT NULL CHECK (monster_base_dps > 0),
       max_affix_tier_allowed INTEGER NOT NULL CHECK (max_affix_tier_allowed BETWEEN 1 AND 15)
   );
   ```
   The table defines only 7 columns. It lacks:
   - `exp_to_next_level`
   - `cumulative_exp`
   - `death_penalty_ratio`
   - `level_gap_safe_range`
   - `level_gap_penalty_exp`
   - `monster_benchmark_exp`

2. **Foreign Key & View Relationships**:
   - `server/world/game_design_matrix_schema.py:16-162`: No other table holds a `FOREIGN KEY` referencing `progression_benchmarks`.
   - `server/world/game_design_matrix_schema.py:164-224`: Views `v_quest_ecosystem`, `v_zone_summary`, and `v_act_narrative_flow` do not reference `progression_benchmarks`.
   - The table can be dropped, rebuilt, or migrated without cascade dependency issues.

3. **Existing Queries on `progression_benchmarks`**:
   - `server/world/game_design_matrix_service.py:164-176`:
     ```python
     cur.execute("SELECT * FROM progression_benchmarks WHERE level = ?", (level,))
     row = cur.fetchone()
     return ProgressionBenchmarkRow(
         level=row["level"],
         target_exp=row["target_exp"],
         player_base_hp=row["player_base_hp"],
         player_benchmark_dps=row["player_benchmark_dps"],
         monster_base_hp=row["monster_base_hp"],
         monster_base_dps=row["monster_base_dps"],
         max_affix_tier_allowed=row["max_affix_tier_allowed"]
     )
     ```
   - `server/world/game_design_matrix_service.py:241-248`:
     ```python
     cur.execute("SELECT level, target_exp FROM progression_benchmarks ORDER BY level ASC")
     prog_rows = cur.fetchall()
     prev_xp = 0
     for r in prog_rows:
         if r["target_exp"] <= prev_xp:
             violations.append(f"Progression Curve non-monotonic at level {r['level']}: XP {r['target_exp']} <= {prev_xp}.")
         prev_xp = r["target_exp"]
     ```
   - `tests/unit/test_game_design_matrix.py:75-88`:
     ```python
     b_lvl1 = matrix_service.get_level_progression_benchmark(1)
     assert b_lvl1.level == 1
     assert b_lvl1.player_base_hp == 100.0
     assert b_lvl1.max_affix_tier_allowed == 15

     b_lvl85 = matrix_service.get_level_progression_benchmark(85)
     assert b_lvl85.level == 85
     assert b_lvl85.player_base_hp > b_lvl1.player_base_hp
     assert b_lvl85.max_affix_tier_allowed == 1
     ```

4. **Existing Seeder Behavior (`server/world/game_design_matrix_seeder.py:241-256`)**:
   - Executes `cur.execute("DELETE FROM progression_benchmarks")`.
   - Inserts 7 values using a single continuous curve: `target_xp = int(500 * (lvl ** 1.85))`.
   - Produces only $46,163$ delta EXP for Level 99->100 ($1.87\%$ of Level 99), violating the $25-35\%$ soft-wall requirement.

5. **SQLite Table Evolution Limitation**:
   - `_init_database()` in `server/world/game_design_matrix_service.py:71-75` calls `conn.executescript(SQL_CREATE_TABLES)`.
   - `CREATE TABLE IF NOT EXISTS` is a no-op when the table already exists.
   - If `SQL_CREATE_TABLES` is modified without an in-place migration check, existing databases (`data/game_design_matrix.db`) will retain the old schema, causing `sqlite3.OperationalError` upon inserting 13 columns.

---

## 2. Logic Chain

1. **From Observation 1**: To support the mathematical model (Level Gap Penalty $\eta(\Delta) = \exp(-0.60 \times (\Delta - 5))$, Tiered Death Penalty 0% to 25%, and monster base scaling), `progression_benchmarks` must be extended with:
   - `exp_to_next_level INTEGER NOT NULL CHECK (exp_to_next_level >= 0)` (allowing 0 at Level 100 max cap)
   - `cumulative_exp INTEGER NOT NULL CHECK (cumulative_exp > 0)`
   - `death_penalty_ratio REAL NOT NULL DEFAULT 0.0 CHECK (death_penalty_ratio BETWEEN 0.0 AND 1.0)`
   - `level_gap_safe_range INTEGER NOT NULL DEFAULT 5 CHECK (level_gap_safe_range >= 0)`
   - `level_gap_penalty_exp REAL NOT NULL DEFAULT 0.60 CHECK (level_gap_penalty_exp > 0)`
   - `monster_benchmark_exp INTEGER NOT NULL DEFAULT 25 CHECK (monster_benchmark_exp > 0)`

2. **From Observation 3**: The monotonicity validation in `validate_game_design_integrity()` requires `target_exp` to strictly increase at every level ($L=1..100$). Therefore, `target_exp` must continue to represent cumulative experience. By maintaining `target_exp = cumulative_exp`, 100% backward compatibility is preserved for both `validate_game_design_integrity()` and `test_level_progression_benchmark()`.

3. **From Observation 2 & 5**: Because `progression_benchmarks` is not referenced by foreign keys or views, and stores deterministic canonical design seed data (not player save state), automatic migration in `GameDesignMatrixService._init_database()` via table rebuild or schema check (`PRAGMA table_info`) is 100% safe, idempotent, and non-destructive.

4. **From Observation 4**: The seeder in `game_design_matrix_seeder.py` must be upgraded to compute the 7-segment piecewise exponential curve, ensuring:
   - Level 1-20 cumulative EXP is $< 0.1\%$ ($0.0217\%$).
   - Level 99->100 delta EXP is $\ge 30\%$ of Level 1-99 accumulated EXP ($35.0\%$) and $25.93\%$ of lifetime EXP.
   - Level 100 delta EXP is $0$.

---

## 3. Caveats

- **Read-Only Scope**: In accordance with the Teamwork Explorer directive, no source code or database files were modified during this investigation.
- **Max Level Death Penalty**: At Level 100, `exp_to_next_level` is 0. `death_penalty_ratio` is set to `0.0` at Level 100 because the character has reached max level and cannot lose EXP or de-level. At Level 99 (progressing towards 100), `death_penalty_ratio` is set to `0.25`.

---

## 4. Conclusion

The exact 13-column schema, DDL, backwards compatibility strategy, and migration approach for Milestone M1 are fully analyzed and verified. 
Worker M1 can directly implement the proposed changes across:
1. `server/world/game_design_matrix_schema.py` (DDL update)
2. `server/world/game_design_matrix_types.py` (`ProgressionBenchmarkRow` dataclass update)
3. `server/world/game_design_matrix_service.py` (`get_level_progression_benchmark` and automatic migration in `_init_database`)
4. `server/world/game_design_matrix_seeder.py` (7-segment piecewise curve calculation and 13-column insert)
5. `migrations/001_extend_progression_benchmarks.sql` (canonical migration script)

Detailed specifications and drop-in code snippets are documented in `c:\Projects\FreeExile\.agents\teamwork\explorer_m1_progression_2\report.md`.

---

## 5. Verification Method

To independently verify the implementation after Worker M1 completes the code changes:

1. **Run Game Design Matrix Linter with Auto-Sync**:
   ```bash
   python tools/lint/verify_game_design_matrix.py --sync
   ```
   *Expected Output*: Status `[PASS]`, 0 violations.

2. **Verify Database Table Schema & Content**:
   ```bash
   python -c "import sqlite3; conn = sqlite3.connect('data/game_design_matrix.db'); cur = conn.cursor(); cur.execute('PRAGMA table_info(progression_benchmarks)'); print([c[1] for c in cur.fetchall()]); cur.execute('SELECT level, target_exp, exp_to_next_level, cumulative_exp, death_penalty_ratio FROM progression_benchmarks WHERE level IN (1, 20, 60, 80, 90, 99, 100)'); print(cur.fetchall())"
   ```
   *Verification criteria*:
   - List of columns has all 13 columns.
   - Level 1: `exp_to_next_level = 525`, `death_penalty_ratio = 0.0`.
   - Level 99: `death_penalty_ratio = 0.25`, `exp_to_next_level` is $\approx 1.76\text{ Billion}$ ($\ge 30\%$ of 1-99).
   - Level 100: `exp_to_next_level = 0`.

3. **Run Unit Tests**:
   ```bash
   pytest tests/unit/test_game_design_matrix.py
   ```
   *Expected Output*: 100% PASS.

4. **Code & Doc Hygiene Check**:
   ```bash
   python tools/lint/check_code_and_doc_hygiene.py --strict
   ```
   *Expected Output*: 100% PASS.