Merge pull request 'fix: remove legacy SQLite compatibility code (#122)' (#126) from ai/paper-dynasty-database#122 into main

Reviewed-on: #126
Reviewed-by: Claude Reviewer <cal.corum+claude-reviewer@gmail.com>

CLAUDE.md

@@ -14,7 +14,7 @@ docker build -t paper-dynasty-db .                          # Build image
 ## Architecture
 
 - **Routers**: Domain-based in `app/routers_v2/` (cards, players, teams, packs, stats, gauntlets, scouting)
-- **ORM**: Peewee with PostgreSQL
+- **ORM**: Peewee with PostgreSQL (`pd_master` database via PooledPostgresqlDatabase)
 - **Card images**: Playwright/Chromium renders HTML templates → screenshots (see `routers_v2/players.py`)
 - **Logging**: Rotating files in `logs/database/{date}.log`
 
@@ -42,7 +42,7 @@ docker build -t paper-dynasty-db .                          # Build image
 - **API docs**: `/api/docs` and `/api/redoc`
 
 ### Key Env Vars
-`API_TOKEN`, `LOG_LEVEL`, `DATABASE_TYPE`, `POSTGRES_HOST`, `POSTGRES_DB`, `POSTGRES_USER`, `POSTGRES_PASSWORD`
+`API_TOKEN`, `LOG_LEVEL`, `POSTGRES_HOST`, `POSTGRES_DB`, `POSTGRES_USER`, `POSTGRES_PASSWORD`
 
 ### Common Issues
 - 502 Bad Gateway → API container crashed; check `docker logs pd_api`

app/db_engine.py

@@ -9,40 +9,20 @@ from peewee import *
 from peewee import ModelSelect
 from playhouse.shortcuts import model_to_dict
 
-# Database configuration - supports both SQLite and PostgreSQL
+from playhouse.pool import PooledPostgresqlDatabase
-DATABASE_TYPE = os.environ.get("DATABASE_TYPE", "sqlite")
-# Skip table creation for PostgreSQL (tables already exist in production)
-SKIP_TABLE_CREATION = DATABASE_TYPE.lower() == "postgresql"
 
-if DATABASE_TYPE.lower() == "postgresql":
+db = PooledPostgresqlDatabase(
-    from playhouse.pool import PooledPostgresqlDatabase
+    os.environ.get("POSTGRES_DB", "pd_master"),
-
+    user=os.environ.get("POSTGRES_USER", "pd_admin"),
-    db = PooledPostgresqlDatabase(
+    password=os.environ.get("POSTGRES_PASSWORD"),
-        os.environ.get("POSTGRES_DB", "pd_master"),
+    host=os.environ.get("POSTGRES_HOST", "localhost"),
-        user=os.environ.get("POSTGRES_USER", "pd_admin"),
+    port=int(os.environ.get("POSTGRES_PORT", "5432")),
-        password=os.environ.get("POSTGRES_PASSWORD"),
+    max_connections=20,
-        host=os.environ.get("POSTGRES_HOST", "localhost"),
+    stale_timeout=300,  # 5 minutes
-        port=int(os.environ.get("POSTGRES_PORT", "5432")),
+    timeout=0,
-        max_connections=20,
+    autoconnect=True,
-        stale_timeout=300,  # 5 minutes
+    autorollback=True,  # Automatically rollback failed transactions
-        timeout=0,
+)
-        autoconnect=True,
-        autorollback=True,  # Automatically rollback failed transactions
-    )
-else:
-    # SQLite configuration for local development only.
-    # Production always uses PostgreSQL (see DATABASE_TYPE env var).
-    #
-    # synchronous=0 (OFF): SQLite skips fsync() after every write, maximising
-    # throughput at the cost of durability — a hard crash could corrupt the DB.
-    # This is an acceptable trade-off in dev where data loss is tolerable and
-    # write speed matters.  WAL journal mode reduces (but does not eliminate)
-    # the corruption window by keeping the main database file consistent while
-    # writes land in the WAL file first.
-    db = SqliteDatabase(
-        "storage/pd_master.db",
-        pragmas={"journal_mode": "wal", "cache_size": -1 * 64000, "synchronous": 0},
-    )
 
 # Refractor stat accumulation starts at this season — stats from earlier seasons
 # are excluded from evaluation queries.  Override via REFRACTOR_START_SEASON env var.
@@ -203,10 +183,6 @@ class Current(BaseModel):
         return latest_current
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Current], safe=True)
-
-
 class Rarity(BaseModel):
     value = IntegerField()
     name = CharField(unique=True)
@@ -220,10 +196,6 @@ class Rarity(BaseModel):
         return self.name
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Rarity], safe=True)
-
-
 class Event(BaseModel):
     name = CharField()
     short_desc = CharField(null=True)
@@ -237,10 +209,6 @@ class Event(BaseModel):
         table_name = "event"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Event], safe=True)
-
-
 class Cardset(BaseModel):
     name = CharField()
     description = CharField()
@@ -258,10 +226,6 @@ class Cardset(BaseModel):
         return self.name
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Cardset], safe=True)
-
-
 class MlbPlayer(BaseModel):
     first_name = CharField()
     last_name = CharField()
@@ -276,10 +240,6 @@ class MlbPlayer(BaseModel):
         table_name = "mlbplayer"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([MlbPlayer], safe=True)
-
-
 class Player(BaseModel):
     player_id = IntegerField(primary_key=True)
     p_name = CharField()
@@ -378,10 +338,6 @@ class Player(BaseModel):
         table_name = "player"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Player], safe=True)
-
-
 class Team(BaseModel):
     abbrev = CharField(max_length=20)  # Gauntlet teams use prefixes like "Gauntlet-NCB"
     sname = CharField(max_length=100)
@@ -437,10 +393,6 @@ class Team(BaseModel):
         table_name = "team"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Team], safe=True)
-
-
 class PackType(BaseModel):
     name = CharField()
     card_count = IntegerField()
@@ -453,10 +405,6 @@ class PackType(BaseModel):
         table_name = "packtype"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([PackType], safe=True)
-
-
 class Pack(BaseModel):
     team = ForeignKeyField(Team)
     pack_type = ForeignKeyField(PackType)
@@ -469,10 +417,6 @@ class Pack(BaseModel):
         table_name = "pack"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Pack], safe=True)
-
-
 class Card(BaseModel):
     player = ForeignKeyField(Player, null=True)
     team = ForeignKeyField(Team, null=True)
@@ -495,10 +439,6 @@ class Card(BaseModel):
         table_name = "card"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([Card], safe=True)
-
-
 class Roster(BaseModel):
     team = ForeignKeyField(Team)
     name = CharField()
@@ -728,26 +668,6 @@ class GauntletRun(BaseModel):
         table_name = "gauntletrun"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables(
-        [
-            Roster,
-            RosterSlot,
-            BattingStat,
-            PitchingStat,
-            Result,
-            Award,
-            Paperdex,
-            Reward,
-            GameRewards,
-            Notification,
-            GauntletReward,
-            GauntletRun,
-        ],
-        safe=True,
-    )
-
-
 class BattingCard(BaseModel):
     player = ForeignKeyField(Player)
     variant = IntegerField()
@@ -914,19 +834,6 @@ pos_index = ModelIndex(
 CardPosition.add_index(pos_index)
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables(
-        [
-            BattingCard,
-            BattingCardRatings,
-            PitchingCard,
-            PitchingCardRatings,
-            CardPosition,
-        ],
-        safe=True,
-    )
-
-
 class StratGame(BaseModel):
     season = IntegerField()
     game_type = CharField()
@@ -1168,20 +1075,6 @@ class ProcessedGame(BaseModel):
         table_name = "processed_game"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables(
-        [
-            StratGame,
-            StratPlay,
-            Decision,
-            BattingSeasonStats,
-            PitchingSeasonStats,
-            ProcessedGame,
-        ],
-        safe=True,
-    )
-
-
 class ScoutOpportunity(BaseModel):
     pack = ForeignKeyField(Pack, null=True)
     opener_team = ForeignKeyField(Team)
@@ -1213,10 +1106,6 @@ scout_claim_index = ModelIndex(
 ScoutClaim.add_index(scout_claim_index)
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables([ScoutOpportunity, ScoutClaim], safe=True)
-
-
 class RefractorTrack(BaseModel):
     name = CharField(unique=True)
     card_type = CharField()  # 'batter', 'sp', 'rp'
@@ -1277,189 +1166,4 @@ class RefractorBoostAudit(BaseModel):
         table_name = "refractor_boost_audit"
 
 
-if not SKIP_TABLE_CREATION:
-    db.create_tables(
-        [
-            RefractorTrack,
-            RefractorCardState,
-            RefractorBoostAudit,
-        ],
-        safe=True,
-    )
-
-
 db.close()
-
-# scout_db = SqliteDatabase(
-#     'storage/card_creation.db',
-#     pragmas={
-#         'journal_mode': 'wal',
-#         'cache_size': -1 * 64000,
-#         'synchronous': 0
-#     }
-# )
-#
-#
-# class BaseModelScout(Model):
-#     class Meta:
-#         database = scout_db
-#
-#
-# class ScoutCardset(BaseModelScout):
-#     set_title = CharField()
-#     set_subtitle = CharField(null=True)
-#
-#
-# class ScoutPlayer(BaseModelScout):
-#     sba_id = IntegerField(primary_key=True)
-#     name = CharField()
-#     fg_id = IntegerField()
-#     br_id = CharField()
-#     offense_col = IntegerField()
-#     hand = CharField(default='R')
-#
-#
-# scout_db.create_tables([ScoutCardset, ScoutPlayer], safe=True)
-#
-#
-# class BatterRatings(BaseModelScout):
-#     id = CharField(unique=True, primary_key=True)
-#     player = ForeignKeyField(ScoutPlayer)
-#     cardset = ForeignKeyField(ScoutCardset)
-#     vs_hand = FloatField()
-#     is_prep = BooleanField()
-#     homerun = FloatField()
-#     bp_homerun = FloatField()
-#     triple = FloatField()
-#     double_three = FloatField()
-#     double_two = FloatField()
-#     double_pull = FloatField()
-#     single_two = FloatField()
-#     single_one = FloatField()
-#     single_center = FloatField()
-#     bp_single = FloatField()
-#     hbp = FloatField()
-#     walk = FloatField()
-#     strikeout = FloatField()
-#     lineout = FloatField()
-#     popout = FloatField()
-#     flyout_a = FloatField()
-#     flyout_bq = FloatField()
-#     flyout_lf_b = FloatField()
-#     flyout_rf_b = FloatField()
-#     groundout_a = FloatField()
-#     groundout_b = FloatField()
-#     groundout_c = FloatField()
-#     avg = FloatField(null=True)
-#     obp = FloatField(null=True)
-#     slg = FloatField(null=True)
-#
-#
-# class PitcherRatings(BaseModelScout):
-#     id = CharField(unique=True, primary_key=True)
-#     player = ForeignKeyField(ScoutPlayer)
-#     cardset = ForeignKeyField(ScoutCardset)
-#     vs_hand = CharField()
-#     is_prep = BooleanField()
-#     homerun = FloatField()
-#     bp_homerun = FloatField()
-#     triple = FloatField()
-#     double_three = FloatField()
-#     double_two = FloatField()
-#     double_cf = FloatField()
-#     single_two = FloatField()
-#     single_one = FloatField()
-#     single_center = FloatField()
-#     bp_single = FloatField()
-#     hbp = FloatField()
-#     walk = FloatField()
-#     strikeout = FloatField()
-#     fo_slap = FloatField()
-#     fo_center = FloatField()
-#     groundout_a = FloatField()
-#     groundout_b = FloatField()
-#     xcheck_p = FloatField()
-#     xcheck_c = FloatField()
-#     xcheck_1b = FloatField()
-#     xcheck_2b = FloatField()
-#     xcheck_3b = FloatField()
-#     xcheck_ss = FloatField()
-#     xcheck_lf = FloatField()
-#     xcheck_cf = FloatField()
-#     xcheck_rf = FloatField()
-#     avg = FloatField(null=True)
-#     obp = FloatField(null=True)
-#     slg = FloatField(null=True)
-#
-#
-# # scout_db.create_tables([BatterRatings, PitcherRatings], safe=True)
-#
-#
-# class CardColumns(BaseModelScout):
-#     id = CharField(unique=True, primary_key=True)
-#     player = ForeignKeyField(ScoutPlayer)
-#     hand = CharField()
-#     b_ratings = ForeignKeyField(BatterRatings, null=True)
-#     p_ratings = ForeignKeyField(PitcherRatings, null=True)
-#     one_dice = CharField()
-#     one_results = CharField()
-#     one_splits = CharField()
-#     two_dice = CharField()
-#     two_results = CharField()
-#     two_splits = CharField()
-#     three_dice = CharField()
-#     three_results = CharField()
-#     three_splits = CharField()
-#
-#
-# class Position(BaseModelScout):
-#     player = ForeignKeyField(ScoutPlayer)
-#     cardset = ForeignKeyField(ScoutCardset)
-#     position = CharField()
-#     innings = IntegerField()
-#     range = IntegerField()
-#     error = IntegerField()
-#     arm = CharField(null=True)
-#     pb = IntegerField(null=True)
-#     overthrow = IntegerField(null=True)
-#
-#
-# class BatterData(BaseModelScout):
-#     player = ForeignKeyField(ScoutPlayer)
-#     cardset = ForeignKeyField(ScoutCardset)
-#     stealing = CharField()
-#     st_low = IntegerField()
-#     st_high = IntegerField()
-#     st_auto = BooleanField()
-#     st_jump = FloatField()
-#     bunting = CharField(null=True)
-#     hit_and_run = CharField(null=True)
-#     running = CharField()
-#
-#
-# class PitcherData(BaseModelScout):
-#     player = ForeignKeyField(ScoutPlayer)
-#     cardset = ForeignKeyField(ScoutCardset)
-#     balk = IntegerField(null=True)
-#     wild_pitch = IntegerField(null=True)
-#     hold = CharField()
-#     starter_rating = IntegerField()
-#     relief_rating = IntegerField()
-#     closer_rating = IntegerField(null=True)
-#     batting = CharField(null=True)
-#
-#
-# scout_db.create_tables([CardColumns, Position, BatterData, PitcherData], safe=True)
-#
-#
-# class CardOutput(BaseModelScout):
-#     name = CharField()
-#     hand = CharField()
-#     positions = CharField()
-#     stealing = CharField()
-#     bunting = CharField()
-#     hitandrun = CharField()
-#     running = CharField()
-#
-#
-# scout_db.close()

app/db_helpers.py

@@ -1,15 +1,8 @@
 """
-Database helper functions for PostgreSQL compatibility.
+Database helper functions for PostgreSQL upsert operations.
-
-This module provides cross-database compatible upsert operations that work
-with both SQLite and PostgreSQL.
-
-The key difference:
-- SQLite: .on_conflict_replace() works directly
-- PostgreSQL: Requires .on_conflict() with explicit conflict_target and update dict
 
 Usage:
-    from app.db_helpers import upsert_many, DATABASE_TYPE
+    from app.db_helpers import upsert_many
 
     # Instead of:
     Model.insert_many(batch).on_conflict_replace().execute()
@@ -18,13 +11,9 @@ Usage:
     upsert_many(Model, batch, conflict_fields=['field1', 'field2'])
 """
 
-import os
+from typing import Any, Dict, List, Type
-from typing import Any, Dict, List, Type, Union
 
-from peewee import Model, SQL
+from peewee import Model
-
-# Re-export DATABASE_TYPE for convenience
-DATABASE_TYPE = os.environ.get("DATABASE_TYPE", "sqlite").lower()
 
 
 def get_model_fields(model: Type[Model], exclude: List[str] = None) -> List[str]:
@@ -54,14 +43,12 @@ def upsert_many(
     batch_size: int = 100,
 ) -> int:
     """
-    Insert or update multiple records in a database-agnostic way.
+    Insert or update multiple records using PostgreSQL ON CONFLICT.
-
-    Works with both SQLite (on_conflict_replace) and PostgreSQL (on_conflict).
 
     Args:
         model: Peewee Model class
         data: List of dictionaries with field values
-        conflict_fields: Fields that define uniqueness (for PostgreSQL ON CONFLICT)
+        conflict_fields: Fields that define uniqueness (for ON CONFLICT target)
         update_fields: Fields to update on conflict (defaults to all non-conflict fields)
         batch_size: Number of records per batch
 
@@ -93,33 +80,28 @@ def upsert_many(
     for i in range(0, len(data), batch_size):
         batch = data[i : i + batch_size]
 
-        if DATABASE_TYPE == "postgresql":
+        from peewee import EXCLUDED
-            # PostgreSQL: Use ON CONFLICT with explicit target and update
-            from peewee import EXCLUDED
 
-            # Build conflict target - get actual field objects
+        # Build conflict target - get actual field objects
-            conflict_target = [getattr(model, f) for f in conflict_fields]
+        conflict_target = [getattr(model, f) for f in conflict_fields]
 
-            # Build update dict - use column_name for EXCLUDED reference
+        # Build update dict - use column_name for EXCLUDED reference
-            # (ForeignKeyField column names end in _id, e.g., batter -> batter_id)
+        # (ForeignKeyField column names end in _id, e.g., batter -> batter_id)
-            update_dict = {}
+        update_dict = {}
-            for f in update_fields:
+        for f in update_fields:
-                if hasattr(model, f):
+            if hasattr(model, f):
-                    field_obj = getattr(model, f)
+                field_obj = getattr(model, f)
-                    # Get the actual column name from the field
+                # Get the actual column name from the field
-                    col_name = field_obj.column_name
+                col_name = field_obj.column_name
-                    update_dict[field_obj] = EXCLUDED[col_name]
+                update_dict[field_obj] = EXCLUDED[col_name]
 
-            if update_dict:
+        if update_dict:
-                model.insert_many(batch).on_conflict(
+            model.insert_many(batch).on_conflict(
-                    conflict_target=conflict_target, action="update", update=update_dict
+                conflict_target=conflict_target, action="update", update=update_dict
-                ).execute()
+            ).execute()
-            else:
-                # No fields to update, just ignore conflicts
-                model.insert_many(batch).on_conflict_ignore().execute()
         else:
-            # SQLite: Use on_conflict_replace (simpler)
+            # No fields to update, just ignore conflicts
-            model.insert_many(batch).on_conflict_replace().execute()
+            model.insert_many(batch).on_conflict_ignore().execute()
 
         total += len(batch)
 

app/routers_v2/players.py

@@ -1336,7 +1336,7 @@ async def put_players(players: PlayerModel, token: str = Depends(oauth2_scheme))
     logging.debug(f"new_players: {new_players}")
 
     with db.atomic():
-        # Use PostgreSQL-compatible upsert helper (preserves SQLite compatibility)
+        # Use PostgreSQL upsert helper (ON CONFLICT DO UPDATE)
         upsert_players(new_players, batch_size=15)
 
     # sheets.update_all_players(SHEETS_AUTH)

app/routers_v2/refractor.py

@@ -1,13 +1,13 @@
+import os
 from datetime import date
+
 from fastapi import APIRouter, Depends, HTTPException, Query
 import logging
-import os
 from typing import Optional
 
 from ..db_engine import model_to_dict, BattingCard, PitchingCard
 from ..dependencies import oauth2_scheme, valid_token
 from ..services.refractor_init import initialize_card_refractor, _determine_card_type
-from ..services.refractor_service import evaluate_and_boost
 
 logger = logging.getLogger(__name__)
 
@@ -339,6 +339,7 @@ async def evaluate_card(card_id: int, token: str = Depends(oauth2_scheme)):
         raise HTTPException(status_code=401, detail="Unauthorized")
 
     from ..db_engine import Card
+    from ..services.refractor_evaluator import evaluate_card as _evaluate
 
     try:
         card = Card.get_by_id(card_id)
@@ -346,7 +347,7 @@ async def evaluate_card(card_id: int, token: str = Depends(oauth2_scheme)):
         raise HTTPException(status_code=404, detail=f"Card {card_id} not found")
 
     try:
-        result = evaluate_and_boost(card.player_id, card.team_id)
+        result = _evaluate(card.player_id, card.team_id)
     except ValueError as exc:
         raise HTTPException(status_code=404, detail=str(exc))
 
@@ -368,6 +369,8 @@ async def evaluate_game(game_id: int, token: str = Depends(oauth2_scheme)):
         raise HTTPException(status_code=401, detail="Unauthorized")
 
     from ..db_engine import RefractorCardState, Player, StratPlay
+    from ..services.refractor_boost import apply_tier_boost
+    from ..services.refractor_evaluator import evaluate_card
 
     plays = list(StratPlay.select().where(StratPlay.game == game_id))
 
@@ -381,6 +384,8 @@ async def evaluate_game(game_id: int, token: str = Depends(oauth2_scheme)):
     evaluated = 0
     tier_ups = []
 
+    boost_enabled = os.environ.get("REFRACTOR_BOOST_ENABLED", "true").lower() != "false"
+
     for player_id, team_id in pairs:
         try:
             state = RefractorCardState.get_or_none(
@@ -401,11 +406,16 @@ async def evaluate_game(game_id: int, token: str = Depends(oauth2_scheme)):
                     continue
 
             old_tier = state.current_tier
-            result = evaluate_and_boost(player_id, team_id)
+            # Use dry_run=True so that current_tier is NOT written here.
+            # apply_tier_boost() writes current_tier + variant atomically on
+            # tier-up.  If no tier-up occurs, apply_tier_boost is not called
+            # and the tier stays at old_tier (correct behaviour).
+            result = evaluate_card(player_id, team_id, dry_run=True)
             evaluated += 1
 
-            new_tier = result.get("current_tier", old_tier)
+            # Use computed_tier (what the formula says) to detect tier-ups.
-            if new_tier > old_tier:
+            computed_tier = result.get("computed_tier", old_tier)
+            if computed_tier > old_tier:
                 player_name = "Unknown"
                 try:
                     p = Player.get_by_id(player_id)
@@ -413,22 +423,63 @@ async def evaluate_game(game_id: int, token: str = Depends(oauth2_scheme)):
                 except Exception:
                     pass
 
+                # Phase 2: Apply rating boosts for each tier gained.
+                # apply_tier_boost() writes current_tier + variant atomically.
+                # If it fails, current_tier stays at old_tier — automatic retry next game.
+                boost_result = None
+                if not boost_enabled:
+                    # Boost disabled via REFRACTOR_BOOST_ENABLED=false.
+                    # Skip notification — current_tier was not written (dry_run),
+                    # so reporting a tier-up would be a false notification.
+                    continue
+
+                card_type = state.track.card_type if state.track else None
+                if card_type:
+                    last_successful_tier = old_tier
+                    failing_tier = old_tier + 1
+                    try:
+                        for tier in range(old_tier + 1, computed_tier + 1):
+                            failing_tier = tier
+                            boost_result = apply_tier_boost(
+                                player_id, team_id, tier, card_type
+                            )
+                            last_successful_tier = tier
+                    except Exception as boost_exc:
+                        logger.warning(
+                            f"Refractor boost failed for player={player_id} "
+                            f"team={team_id} tier={failing_tier}: {boost_exc}"
+                        )
+                        # Report only the tiers that actually succeeded.
+                        # If none succeeded, skip the tier_up notification entirely.
+                        if last_successful_tier == old_tier:
+                            continue
+                        # At least one intermediate tier was committed; report that.
+                        computed_tier = last_successful_tier
+                else:
+                    # No card_type means no track — skip boost and skip notification.
+                    # A false tier-up notification must not be sent when the boost
+                    # was never applied (current_tier was never written to DB).
+                    logger.warning(
+                        f"Refractor boost skipped for player={player_id} "
+                        f"team={team_id}: no card_type on track"
+                    )
+                    continue
+
                 tier_up_entry = {
                     "player_id": player_id,
                     "team_id": team_id,
                     "player_name": player_name,
                     "old_tier": old_tier,
-                    "new_tier": new_tier,
+                    "new_tier": computed_tier,
                     "current_value": result.get("current_value", 0),
                     "track_name": state.track.name if state.track else "Unknown",
                 }
 
-                variants_created = result.get("variants_created") or []
+                # Non-breaking addition: include boost info when available.
-                if variants_created:
+                if boost_result:
-                    variant_num = variants_created[-1]
+                    variant_num = boost_result.get("variant_created")
                     tier_up_entry["variant_created"] = variant_num
-                    card_type = state.track.card_type if state.track else None
+                    if computed_tier >= 3 and variant_num and card_type:
-                    if new_tier >= 3 and variant_num and card_type:
                         d = date.today().strftime("%Y-%m-%d")
                         api_base = os.environ.get("API_BASE_URL", "").rstrip("/")
                         tier_up_entry["animated_url"] = (

app/services/refractor_service.py

@@ -1,194 +0,0 @@
-"""Refractor service layer — shared orchestration across router endpoints.
-
-Provides ``ensure_variant_cards`` and ``evaluate_and_boost`` so that both
-the evaluate-game endpoint and the manual card-evaluate endpoint share the
-same boost orchestration logic without requiring HTTP round-trips in tests.
-"""
-
-import logging
-import os
-
-from app.db_engine import RefractorCardState, BattingCard, PitchingCard
-from app.services.refractor_boost import apply_tier_boost, compute_variant_hash
-from app.services.refractor_evaluator import evaluate_card
-
-logger = logging.getLogger(__name__)
-
-
-def ensure_variant_cards(
-    player_id: int,
-    team_id: int,
-    target_tier: int | None = None,
-    card_type: str | None = None,
-    *,
-    _state_model=None,
-) -> dict:
-    """Ensure variant cards exist for all tiers up to target_tier.
-
-    Idempotent — safe to call multiple times.  If a variant card already
-    exists for a given tier it is skipped.  Partial failures are tolerated:
-    lower tiers that were committed are reported even if a higher tier fails.
-
-    Args:
-        player_id: Player primary key.
-        team_id: Team primary key.
-        target_tier: Highest tier to ensure variants for.  If None, uses
-            ``state.current_tier`` (backfill mode — creates missing variants
-            for tiers already recorded in the DB).
-        card_type: One of 'batter', 'sp', 'rp'.  If None, derived from
-            ``state.track.card_type``.
-        _state_model: Dependency-injection override for RefractorCardState
-            (used in tests).
-
-    Returns:
-        Dict with keys:
-          - ``current_tier`` (int): highest tier confirmed in the DB after
-            this call (equal to state.current_tier if no new tiers created).
-          - ``variants_created`` (list[int]): variant hashes newly created.
-          - ``boost_results`` (list[dict]): raw return values from
-            apply_tier_boost for each newly created variant.
-    """
-    if _state_model is None:
-        _state_model = RefractorCardState
-
-    state = _state_model.get_or_none(
-        (_state_model.player_id == player_id) & (_state_model.team_id == team_id)
-    )
-    if state is None:
-        return {"current_tier": 0, "variants_created": [], "boost_results": []}
-
-    if target_tier is None:
-        target_tier = state.current_tier
-
-    if target_tier == 0:
-        return {
-            "current_tier": state.current_tier,
-            "variants_created": [],
-            "boost_results": [],
-        }
-
-    # Resolve card_type from track if not provided.
-    resolved_card_type = card_type
-    if resolved_card_type is None:
-        resolved_card_type = state.track.card_type if state.track else None
-    if resolved_card_type is None:
-        logger.warning(
-            "ensure_variant_cards: no card_type for player=%s team=%s — skipping",
-            player_id,
-            team_id,
-        )
-        return {
-            "current_tier": state.current_tier,
-            "variants_created": [],
-            "boost_results": [],
-        }
-
-    # Respect kill switch.
-    boost_enabled = os.environ.get("REFRACTOR_BOOST_ENABLED", "true").lower() != "false"
-    if not boost_enabled:
-        return {
-            "current_tier": state.current_tier,
-            "variants_created": [],
-            "boost_results": [],
-        }
-
-    is_batter = resolved_card_type == "batter"
-    CardModel = BattingCard if is_batter else PitchingCard
-
-    variants_created = []
-    boost_results = []
-    last_successful_tier = state.current_tier
-
-    for tier in range(1, target_tier + 1):
-        variant_hash = compute_variant_hash(player_id, tier)
-        existing = CardModel.get_or_none(
-            (CardModel.player == player_id) & (CardModel.variant == variant_hash)
-        )
-        if existing is not None:
-            # Already exists — idempotent skip.
-            continue
-        try:
-            boost_result = apply_tier_boost(
-                player_id, team_id, tier, resolved_card_type
-            )
-            variants_created.append(variant_hash)
-            boost_results.append(boost_result)
-            last_successful_tier = tier
-        except Exception as exc:
-            logger.warning(
-                "ensure_variant_cards: boost failed for player=%s team=%s tier=%s: %s",
-                player_id,
-                team_id,
-                tier,
-                exc,
-            )
-            # Don't attempt higher tiers if a lower one failed; the missing
-            # lower-tier variant would cause apply_tier_boost to fail for T+1
-            # anyway (it reads from the previous tier's variant as its source).
-            break
-
-    return {
-        "current_tier": last_successful_tier,
-        "variants_created": variants_created,
-        "boost_results": boost_results,
-    }
-
-
-def evaluate_and_boost(
-    player_id: int,
-    team_id: int,
-    *,
-    _state_model=None,
-    _stats_model=None,
-) -> dict:
-    """Full evaluation: recompute tier from career stats, then ensure variant cards exist.
-
-    Combines evaluate_card (dry_run=True) with ensure_variant_cards so that
-    both tier computation and variant-card creation happen in a single call.
-    Handles both tier-up cases (computed_tier > stored) and backfill cases
-    (tier already in DB but variant card was never created).
-
-    Args:
-        player_id: Player primary key.
-        team_id: Team primary key.
-        _state_model: DI override for RefractorCardState (used in tests).
-        _stats_model: DI override for stats model passed to evaluate_card.
-
-    Returns:
-        Dict containing all fields from evaluate_card plus:
-          - ``current_tier`` (int): highest tier confirmed in the DB after
-            this call (may be higher than eval_result["current_tier"] if a
-            tier-up was committed here).
-          - ``variants_created`` (list[int]): variant hashes newly created.
-          - ``boost_results`` (list[dict]): raw boost result dicts.
-
-    Raises:
-        ValueError: If no RefractorCardState exists for (player_id, team_id).
-    """
-    eval_kwargs: dict = {"dry_run": True}
-    if _state_model is not None:
-        eval_kwargs["_state_model"] = _state_model
-    if _stats_model is not None:
-        eval_kwargs["_stats_model"] = _stats_model
-
-    eval_result = evaluate_card(player_id, team_id, **eval_kwargs)
-
-    computed_tier = eval_result.get("computed_tier", 0)
-    stored_tier = eval_result.get("current_tier", 0)
-    # target_tier is the higher of formula result and stored value:
-    # - tier-up case: computed > stored, creates new variant(s)
-    # - backfill case: stored > computed (stale), creates missing variants
-    target_tier = max(computed_tier, stored_tier)
-
-    ensure_kwargs: dict = {"target_tier": target_tier}
-    if _state_model is not None:
-        ensure_kwargs["_state_model"] = _state_model
-
-    ensure_result = ensure_variant_cards(player_id, team_id, **ensure_kwargs)
-
-    return {
-        **eval_result,
-        "current_tier": ensure_result["current_tier"],
-        "variants_created": ensure_result["variants_created"],
-        "boost_results": ensure_result["boost_results"],
-    }

tests/conftest.py

@@ -1,12 +1,9 @@
 """
 Shared test fixtures for the Paper Dynasty database test suite.
 
-Uses in-memory SQLite with foreign_keys pragma enabled. Each test
+Provides dummy PostgreSQL credentials so PooledPostgresqlDatabase can be
-gets a fresh set of tables via the setup_test_db fixture (autouse).
+instantiated during test collection without a real database connection.
-
+Each test module is responsible for binding models to its own test database.
-All models are bound to the in-memory database before table creation
-so that no connection to the real storage/pd_master.db occurs during
-tests.
 """
 
 import os
@@ -14,14 +11,6 @@ import pytest
 import psycopg2
 from peewee import SqliteDatabase
 
-# Set DATABASE_TYPE=postgresql so that the module-level SKIP_TABLE_CREATION
-# flag is True.  This prevents db_engine.py from calling create_tables()
-# against the real storage/pd_master.db during import — those calls would
-# fail if indexes already exist and would also contaminate the dev database.
-# The PooledPostgresqlDatabase object is created but never actually connects
-# because our fixture rebinds all models to an in-memory SQLite db before
-# any query is executed.
-os.environ["DATABASE_TYPE"] = "postgresql"
 # Provide dummy credentials so PooledPostgresqlDatabase can be instantiated
 # without raising a configuration error (it will not actually be used).
 os.environ.setdefault("POSTGRES_PASSWORD", "test-dummy")