fix(economy): quick-selling a squadded card failed with a FOREIGN KEY error

Found while implementing sale settlement, and reproduced before fixing:

  sell_item(pool, "club", "item-x", 250)
    -> Database(SqliteError { code: 787, message: "FOREIGN KEY constraint failed" })

squad_players.owned_card_id is a FK onto owned_cards(id) and db.rs enables
foreign_keys, so DELETEing an item that sits in any lineup is refused outright.
services::economy::sell_item never cleared the lineup, and this is the LIVE FIFA 17
quick-sell path (host economy_store -> econ.sell_item -> POST /economy/sell-item).
Selling a card that happens to be in your squad is ordinary, not an edge case.

sell_item now evicts the item from every lineup first, inside its existing
transaction, so a wrong-owner sale rolls the eviction back with everything else
(asserted, not assumed).

Also folds routes/cards.rs::delete_owned_card into the same authority. It
hand-rolled DELETE + club::add_coins directly on the pool, which had BOTH defects:
no transaction, so a failed credit destroyed the card for nothing, and the same
missing squad eviction. Its response shape is unchanged and the pre-existing route
test still passes.

Tests: selling_a_squadded_item_succeeds_and_frees_the_slot (the repro) and
a_rejected_quick_sell_leaves_the_lineup_intact. Core 55 lib + 123 integration pass,
clippy clean.

Not deployed — production is mid live-test.

migrations/0019_sbc_submissions_club_id.sql

@@ -0,0 +1,11 @@
+-- Issue 1: sbc_submissions was created (0001_initial.sql) without a club_id column,
+-- but the MY CLUB milestone query (routes/club.rs get_milestones) counts
+--   SELECT COUNT(*) FROM sbc_submissions WHERE club_id = ? AND passed = 1
+-- so SQLite errored on the unknown column and the error was swallowed by
+-- `.unwrap_or(0)` -> the `sbcs_completed` milestone always read 0. Add the column
+-- and backfill it from the profile's club so historical submissions count.
+ALTER TABLE sbc_submissions ADD COLUMN club_id TEXT;
+
+UPDATE sbc_submissions
+SET club_id = (SELECT c.id FROM clubs c WHERE c.profile_id = sbc_submissions.profile_id)
+WHERE club_id IS NULL;

src/app.rs

@@ -172,6 +172,36 @@ pub async fn build(pool: Pool, cfg: Config) -> Result<Router> {
         .route("/cards", get(routes::cards::get_cards))
         .route("/cards/:card_id", get(routes::cards::get_card))
         .route("/collection", get(routes::cards::get_collection))
+        .route("/economy/balance", get(routes::economy::get_balance))
+        .route(
+            "/economy/entitlements",
+            get(routes::economy::get_entitlements),
+        )
+        .route(
+            "/economy/purchase-entitlement",
+            post(routes::economy::post_purchase_entitlement),
+        )
+        .route(
+            "/economy/redeem-entitlement",
+            post(routes::economy::post_redeem_entitlement),
+        )
+        .route("/economy/sell-item", post(routes::economy::post_sell_item))
+        .route(
+            "/economy/grant-reward",
+            post(routes::economy::post_grant_reward),
+        )
+        .route(
+            "/economy/purchase-item",
+            post(routes::economy::post_purchase_item),
+        )
+        .route(
+            "/economy/purchase-items",
+            post(routes::economy::post_purchase_items),
+        )
+        .route(
+            "/economy/settle-sale",
+            post(routes::economy::post_settle_sale),
+        )
         .route(
             "/collection/:owned_card_id",
             delete(routes::cards::delete_owned_card),

src/db.rs

@@ -1,24 +1,40 @@
 use anyhow::Result;
 use sqlx::{
-    sqlite::{SqliteConnectOptions, SqlitePoolOptions},
-    SqlitePool,
+    sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions},
+    ConnectOptions, Connection, SqlitePool,
 };
 use std::str::FromStr;
+use std::time::Duration;
 use tracing::info;
 
 pub type Pool = SqlitePool;
 
 pub async fn init_pool(database_url: &str, max_connections: u32) -> Result<Pool> {
     info!("Connecting to database: {}", database_url);
-    let opts = SqliteConnectOptions::from_str(database_url)?.create_if_missing(true);
+    // Per-connection options so EVERY pooled connection gets them: WAL for
+    // reader/writer concurrency, foreign keys on, and a busy_timeout so a
+    // transient SQLITE_BUSY under concurrent access waits-and-retries.
+    let opts = SqliteConnectOptions::from_str(database_url)?
+        .create_if_missing(true)
+        .journal_mode(SqliteJournalMode::Wal)
+        .foreign_keys(true)
+        .busy_timeout(Duration::from_secs(5));
+    // Establish WAL on the file via ONE connection BEFORE the pool opens.
+    // Switching a fresh DB to WAL is a one-time file-level change; letting
+    // several pooled connections do it concurrently at warm-up races that
+    // switch and can surface a spurious lock. Serialize it here so every
+    // pooled connection thereafter only re-asserts an already-WAL file.
+    {
+        let mut conn = opts.clone().connect().await?;
+        sqlx::query("PRAGMA journal_mode=WAL")
+            .execute(&mut conn)
+            .await?;
+        conn.close().await?;
+    }
     let pool = SqlitePoolOptions::new()
         .max_connections(max_connections)
         .connect_with(opts)
         .await?;
-    sqlx::query("PRAGMA journal_mode=WAL")
-        .execute(&pool)
-        .await?;
-    sqlx::query("PRAGMA foreign_keys=ON").execute(&pool).await?;
     Ok(pool)
 }
 

src/routes/cards.rs

@@ -12,6 +12,7 @@ use crate::{
     models::card::OwnedCard,
     services::{
         club as club_svc,
+        economy as economy_svc,
         inventory::{self, OwnedItemQuery, OwnedItemView},
         profile as profile_svc,
     },
@@ -184,12 +185,11 @@ pub async fn delete_owned_card(
 
     let coins = quick_sell_coins(card.overall);
 
-    sqlx::query("DELETE FROM owned_cards WHERE id = ?")
-        .bind(&owned_card_id)
-        .execute(&state.pool)
-        .await?;
-
-    club_svc::add_coins(&state.pool, &club.id, coins).await?;
+    // Delegate to the economy authority rather than hand-rolling DELETE + add_coins:
+    // that pair ran on the pool with NO transaction (a failed credit left the card
+    // destroyed for nothing) and it skipped `squad_players`, whose FK onto
+    // `owned_cards(id)` made quick-selling a squadded card fail with SQLite 787.
+    economy_svc::sell_item(&state.pool, &club.id, &owned_card_id, coins).await?;
 
     Ok(Json(json!({
         "quick_sold": owned_card_id,

src/routes/economy.rs

@@ -0,0 +1,220 @@
+//! Generic economy HTTP boundary.
+//!
+//! Exposes [`crate::services::economy`] over the same game-scoped active-profile
+//! resolution every other Core route uses ([`GameId`] header → active profile →
+//! club). The caller (a game host) never supplies a club id; Core maps the game
+//! to its authoritative club, so there is no cross-club economy access. Every
+//! op is a single durable SQLite transaction in the service layer.
+//!
+//! This surface is deliberately game-neutral: no currency names, pack ids, or
+//! wire semantics — those live in the game host/adapter.
+
+use axum::{extract::State, Json};
+use serde::{Deserialize, Serialize};
+
+use crate::{
+    app::AppState,
+    error::AppResult,
+    extractors::GameId,
+    services::{club as club_svc, economy, economy::GrantedItem, profile as profile_svc},
+};
+
+/// Resolve the game-scoped active profile's club id.
+async fn resolve_club(state: &AppState, game: &GameId) -> AppResult<String> {
+    let profile = profile_svc::get_active_profile(&state.pool, game.as_str()).await?;
+    let club = club_svc::get_club_by_profile(&state.pool, &profile.id).await?;
+    Ok(club.id)
+}
+
+#[derive(Serialize)]
+pub struct BalanceResponse {
+    pub balance: i64,
+}
+
+/// `GET /economy/balance` — the club's currency balance.
+pub async fn get_balance(
+    State(state): State<AppState>,
+    game: GameId,
+) -> AppResult<Json<BalanceResponse>> {
+    let club = resolve_club(&state, &game).await?;
+    let balance = economy::balance(&state.pool, &club).await?;
+    Ok(Json(BalanceResponse { balance }))
+}
+
+/// `GET /economy/entitlements` — the club's unconsumed entitlements.
+pub async fn get_entitlements(
+    State(state): State<AppState>,
+    game: GameId,
+) -> AppResult<Json<Vec<economy::Entitlement>>> {
+    let club = resolve_club(&state, &game).await?;
+    Ok(Json(
+        economy::list_unopened_entitlements(&state.pool, &club).await?,
+    ))
+}
+
+#[derive(Deserialize)]
+pub struct PurchaseEntitlementRequest {
+    pub cost: i64,
+    pub definition_id: String,
+}
+
+/// `POST /economy/purchase-entitlement` — atomic debit + grant.
+pub async fn post_purchase_entitlement(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<PurchaseEntitlementRequest>,
+) -> AppResult<Json<economy::PurchaseReceipt>> {
+    let club = resolve_club(&state, &game).await?;
+    Ok(Json(
+        economy::purchase_entitlement(&state.pool, &club, req.cost, &req.definition_id).await?,
+    ))
+}
+
+#[derive(Deserialize)]
+pub struct RedeemEntitlementRequest {
+    pub entitlement_id: String,
+    pub items: Vec<GrantedItem>,
+}
+
+#[derive(Serialize)]
+pub struct RedeemEntitlementResponse {
+    pub definition_id: String,
+}
+
+/// `POST /economy/redeem-entitlement` — atomic consume-once + add items.
+pub async fn post_redeem_entitlement(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<RedeemEntitlementRequest>,
+) -> AppResult<Json<RedeemEntitlementResponse>> {
+    let club = resolve_club(&state, &game).await?;
+    let definition_id =
+        economy::redeem_entitlement(&state.pool, &club, &req.entitlement_id, &req.items).await?;
+    Ok(Json(RedeemEntitlementResponse { definition_id }))
+}
+
+#[derive(Deserialize)]
+pub struct SellItemRequest {
+    pub item_id: String,
+    pub price: i64,
+}
+
+/// `POST /economy/sell-item` — atomic remove + credit.
+pub async fn post_sell_item(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<SellItemRequest>,
+) -> AppResult<Json<BalanceResponse>> {
+    let club = resolve_club(&state, &game).await?;
+    let balance = economy::sell_item(&state.pool, &club, &req.item_id, req.price).await?;
+    Ok(Json(BalanceResponse { balance }))
+}
+
+#[derive(Deserialize)]
+pub struct GrantRewardRequest {
+    pub amount: i64,
+}
+
+/// `POST /economy/grant-reward` — atomic credit.
+pub async fn post_grant_reward(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<GrantRewardRequest>,
+) -> AppResult<Json<BalanceResponse>> {
+    let club = resolve_club(&state, &game).await?;
+    let balance = economy::grant_reward(&state.pool, &club, req.amount).await?;
+    Ok(Json(BalanceResponse { balance }))
+}
+
+#[derive(Deserialize)]
+pub struct PurchaseItemRequest {
+    pub cost: i64,
+    pub item_id: String,
+    pub card_id: String,
+}
+
+/// `POST /economy/purchase-item` — atomic debit + mint item.
+pub async fn post_purchase_item(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<PurchaseItemRequest>,
+) -> AppResult<Json<BalanceResponse>> {
+    let club = resolve_club(&state, &game).await?;
+    let balance =
+        economy::purchase_item(&state.pool, &club, req.cost, &req.item_id, &req.card_id).await?;
+    Ok(Json(BalanceResponse { balance }))
+}
+
+#[derive(Deserialize)]
+pub struct PurchaseItemsRequest {
+    pub cost: i64,
+    pub items: Vec<GrantedItem>,
+}
+
+/// `POST /economy/purchase-items` — atomic debit + mint several items.
+pub async fn post_purchase_items(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<PurchaseItemsRequest>,
+) -> AppResult<Json<BalanceResponse>> {
+    let club = resolve_club(&state, &game).await?;
+    let balance = economy::purchase_items(&state.pool, &club, req.cost, &req.items).await?;
+    Ok(Json(BalanceResponse { balance }))
+}
+
+/// `POST /economy/settle-sale` request.
+///
+/// This is the ONE economy route that names clubs explicitly, and it has to: a
+/// market sale has two sides, and the module's active-profile resolution can only
+/// ever describe one. Both are optional and default to the game-scoped active
+/// club, so the single-player case stays as terse as every other route:
+///
+///   * `seller_club_id` omitted -> the active club is the seller (it listed the
+///     item), which is the production shape.
+///   * `buyer_club_id` omitted -> the counterparty is OUTSIDE the modelled
+///     economy: no balance is debited and the item leaves the inventory. It does
+///     NOT silently fall back to the active club, because that would settle a sale
+///     between a club and itself.
+#[derive(Deserialize)]
+pub struct SettleSaleRequest {
+    /// The authoritative owned-item instance changing hands.
+    pub item_id: String,
+    /// What the buyer pays. The fee is withheld from this, never added to it.
+    pub gross: i64,
+    /// Withheld from the seller and destroyed. The RATE is a per-game policy the
+    /// caller owns; Core only checks `0 <= fee <= gross`.
+    pub fee: i64,
+    #[serde(default)]
+    pub seller_club_id: Option<String>,
+    #[serde(default)]
+    pub buyer_club_id: Option<String>,
+}
+
+/// `POST /economy/settle-sale` — atomically debit the buyer, transfer the existing
+/// item, credit the seller net of the fee, and destroy the fee.
+pub async fn post_settle_sale(
+    State(state): State<AppState>,
+    game: GameId,
+    Json(req): Json<SettleSaleRequest>,
+) -> AppResult<Json<economy::SaleReceipt>> {
+    let seller = match req.seller_club_id {
+        Some(id) => id,
+        None => resolve_club(&state, &game).await?,
+    };
+    let buyer = match req.buyer_club_id.as_deref() {
+        Some(id) => economy::SaleBuyer::Club(id),
+        None => economy::SaleBuyer::Outside,
+    };
+    let receipt = economy::settle_sale(
+        &state.pool,
+        &req.item_id,
+        &seller,
+        buyer,
+        economy::SaleTerms {
+            gross: req.gross,
+            fee: req.fee,
+        },
+    )
+    .await?;
+    Ok(Json(receipt))
+}

src/routes/mod.rs

@@ -4,6 +4,7 @@ pub mod cards;
 pub mod club;
 pub mod division;
 pub mod draft;
+pub mod economy;
 pub mod fut_champs;
 pub mod events;
 pub mod health;

src/services/checkin.rs

@@ -44,7 +44,7 @@ pub async fn get_status(pool: &Pool, profile_id: &str) -> AppResult<CheckinStatu
             let last_day = &last_at[..10]; // YYYY-MM-DD
             let available = last_day != today.as_str();
             let next_streak = compute_next_streak(last_streak, &last_at);
-            let idx = ((next_streak - 1) % 7) as usize;
+            let idx = (next_streak - 1).rem_euclid(7) as usize;
             Ok(CheckinStatus {
                 available,
                 streak_day: if available { next_streak } else { last_streak },
@@ -83,19 +83,17 @@ pub async fn claim(
     }
 
     let last_streak = row.as_ref().map(|(s, last_at)| compute_next_streak(*s, last_at)).unwrap_or(1);
-    let idx = ((last_streak - 1) % 7) as usize;
+    let idx = (last_streak - 1).rem_euclid(7) as usize;
     let coins = STREAK_COINS[idx];
     let pack_def = if idx == 6 { Some(STREAK_7_PACK) } else { None };
 
-    club::add_coins(pool, club_id, coins).await?;
-    if let Some(def) = pack_def {
-        let _ = pack::grant_pack(pool, club_id, def).await;
-    }
-
+    // Atomically claim today's check-in: the INSERT lands only if no row exists for
+    // today, so two concurrent claims cannot both pay out (was a check-then-act race).
     let now = chrono::Utc::now().to_rfc3339();
-    sqlx::query(
+    let inserted = sqlx::query(
         "INSERT INTO daily_checkins (id, profile_id, club_id, streak_day, coins_awarded, pack_granted, checked_in_at) \
-         VALUES (?, ?, ?, ?, ?, ?, ?)",
+         SELECT ?, ?, ?, ?, ?, ?, ? \
+         WHERE NOT EXISTS (SELECT 1 FROM daily_checkins WHERE profile_id = ? AND substr(checked_in_at, 1, 10) = ?)",
     )
     .bind(Uuid::new_v4().to_string())
     .bind(profile_id)
@@ -104,8 +102,26 @@ pub async fn claim(
     .bind(coins)
     .bind(pack_def)
     .bind(&now)
+    .bind(profile_id)
+    .bind(&today)
     .execute(pool)
-    .await?;
+    .await?
+    .rows_affected();
+
+    if inserted == 0 {
+        // A concurrent claim already recorded today's check-in — do not pay out again.
+        return Ok(CheckinResult {
+            coins_awarded: 0,
+            pack_awarded: None,
+            new_streak: last_streak,
+            already_claimed: true,
+        });
+    }
+
+    club::add_coins(pool, club_id, coins).await?;
+    if let Some(def) = pack_def {
+        let _ = pack::grant_pack(pool, club_id, def).await;
+    }
 
     Ok(CheckinResult {
         coins_awarded: coins,

src/services/club.rs

@@ -86,24 +86,43 @@ pub async fn add_coins(pool: &Pool, club_id: &str, amount: i64) -> AppResult<i64
 }
 
 pub async fn spend_coins(pool: &Pool, club_id: &str, amount: i64) -> AppResult<i64> {
-    let balance = sqlx::query_scalar::<_, i64>("SELECT coins FROM clubs WHERE id = ?")
-        .bind(club_id)
-        .fetch_one(pool)
-        .await?;
+    if amount < 0 {
+        return Err(AppError::BadRequest(format!(
+            "cannot spend a negative amount: {amount}"
+        )));
+    }
 
-    if balance < amount {
+    let now = Utc::now();
+    // Atomic compare-and-swap: the `coins >= ?` guard makes the debit conditional in a
+    // single statement, so two concurrent spends can never both pass a stale balance
+    // check and drive coins negative (the old SELECT-then-UPDATE was a TOCTOU race).
+    let affected = sqlx::query(
+        "UPDATE clubs SET coins = coins - ?, updated_at = ? WHERE id = ? AND coins >= ?",
+    )
+    .bind(amount)
+    .bind(now)
+    .bind(club_id)
+    .bind(amount)
+    .execute(pool)
+    .await?
+    .rows_affected();
+
+    if affected == 0 {
+        // No row updated: the club is missing, or it could not afford the debit.
+        // Disambiguate so callers keep the NotFound vs BadRequest distinction.
+        let balance = sqlx::query_scalar::<_, i64>("SELECT coins FROM clubs WHERE id = ?")
+            .bind(club_id)
+            .fetch_optional(pool)
+            .await?
+            .ok_or_else(|| AppError::NotFound(format!("club not found: {club_id}")))?;
         return Err(AppError::BadRequest(format!(
             "insufficient coins: have {balance}, need {amount}"
         )));
     }
 
-    let now = Utc::now();
-    sqlx::query("UPDATE clubs SET coins = coins - ?, updated_at = ? WHERE id = ?")
-        .bind(amount)
-        .bind(now)
+    let new_balance = sqlx::query_scalar::<_, i64>("SELECT coins FROM clubs WHERE id = ?")
         .bind(club_id)
-        .execute(pool)
+        .fetch_one(pool)
         .await?;
-
-    Ok(balance - amount)
+    Ok(new_balance)
 }

src/services/import.rs

@@ -51,6 +51,13 @@ pub struct ImportOwnedCard {
     pub card_id: String,
 }
 
+#[derive(Debug, Deserialize)]
+pub struct ImportEntitlement {
+    /// Opaque definition reference for one unconsumed entitlement (e.g. a pack
+    /// id as text). Core stores it verbatim; it never interprets the value.
+    pub definition_id: String,
+}
+
 #[derive(Debug, Deserialize)]
 pub struct ImportSlot {
     pub owned_item_id: String,
@@ -93,6 +100,9 @@ pub struct ProfileImportRequest {
     pub owned: Vec<ImportOwnedCard>,
     #[serde(default)]
     pub squad: Option<ImportSquad>,
+    /// Unconsumed entitlements to seed (e.g. from a source's unopened packs).
+    #[serde(default)]
+    pub entitlements: Vec<ImportEntitlement>,
 }
 
 #[derive(Debug, Serialize, PartialEq, Eq)]
@@ -244,6 +254,19 @@ pub async fn apply_profile_import(
         .with_context(|| format!("insert owned_card {}", o.owned_item_id))?;
     }
 
+    for e in &req.entitlements {
+        sqlx::query(
+            "INSERT INTO packs (id, club_id, definition_id, opened, created_at) VALUES (?, ?, ?, 0, ?)",
+        )
+        .bind(Uuid::new_v4().to_string())
+        .bind(&club_id)
+        .bind(&e.definition_id)
+        .bind(&now)
+        .execute(&mut *tx)
+        .await
+        .with_context(|| format!("insert entitlement {}", e.definition_id))?;
+    }
+
     let mut squad_slots = 0usize;
     if let Some(sq) = &req.squad {
         let squad_id = Uuid::new_v4().to_string();

src/services/market.rs

@@ -141,12 +141,23 @@ pub async fn buy_listing(
     .await?
     .ok_or_else(|| AppError::NotFound("listing not found or already sold".into()))?;
 
-    club::spend_coins(pool, club_id, listing.price).await?;
-
-    sqlx::query("UPDATE market_listings SET sold = 1 WHERE id = ?")
+    // Atomically claim the listing (flip sold 0->1) before charging, so two concurrent
+    // buyers cannot both mint the same card. If the debit then fails, release the claim.
+    let claimed = sqlx::query("UPDATE market_listings SET sold = 1 WHERE id = ? AND sold = 0")
         .bind(&listing.id)
         .execute(pool)
-        .await?;
+        .await?
+        .rows_affected();
+    if claimed == 0 {
+        return Err(AppError::NotFound("listing not found or already sold".into()));
+    }
+    if let Err(e) = club::spend_coins(pool, club_id, listing.price).await {
+        let _ = sqlx::query("UPDATE market_listings SET sold = 0 WHERE id = ?")
+            .bind(&listing.id)
+            .execute(pool)
+            .await;
+        return Err(e);
+    }
 
     let owned_id = Uuid::new_v4().to_string();
     sqlx::query(
@@ -206,10 +217,17 @@ pub async fn sell_card(
     .await?
     .ok_or_else(|| AppError::NotFound("owned card not found".into()))?;
 
-    sqlx::query("DELETE FROM owned_cards WHERE id = ?")
+    // Atomically claim the card: guard the DELETE with the owner + rows_affected so two
+    // concurrent sells of the same card cannot both credit (double payout).
+    let deleted = sqlx::query("DELETE FROM owned_cards WHERE id = ? AND club_id = ?")
         .bind(&req.owned_card_id)
+        .bind(club_id)
         .execute(pool)
-        .await?;
+        .await?
+        .rows_affected();
+    if deleted == 0 {
+        return Err(AppError::NotFound("owned card not found".into()));
+    }
 
     let coins = (req.price as f64 * 0.4) as i64;
     let new_balance = club::add_coins(pool, club_id, coins).await?;

src/services/mod.rs

@@ -3,6 +3,7 @@ pub mod card_db;
 pub mod checkin;
 pub mod club;
 pub mod draft;
+pub mod economy;
 pub mod event;
 pub mod fut_champs;
 pub mod game_ext;

src/services/pack.rs

@@ -71,7 +71,17 @@ pub async fn open_pack(
     .await?
     .ok_or_else(|| AppError::NotFound(format!("pack {pack_id} not found")))?;
 
-    if pack.opened {
+    // Atomically claim the pack before minting any cards: only one concurrent opener
+    // flips opened 0->1, so a double-open cannot mint the reward twice (duplication).
+    let claimed = sqlx::query(
+        "UPDATE packs SET opened = 1 WHERE id = ? AND club_id = ? AND opened = 0",
+    )
+    .bind(pack_id)
+    .bind(club_id)
+    .execute(pool)
+    .await?
+    .rows_affected();
+    if claimed == 0 {
         return Err(AppError::BadRequest("pack already opened".into()));
     }
 
@@ -128,7 +138,7 @@ pub async fn open_pack(
         .unwrap_or_default();
     let now = chrono::Utc::now().to_rfc3339();
 
-    sqlx::query("UPDATE packs SET opened = 1, opened_cards = ?, opened_at = ? WHERE id = ?")
+    sqlx::query("UPDATE packs SET opened_cards = ?, opened_at = ? WHERE id = ?")
         .bind(&card_ids_json)
         .bind(&now)
         .bind(pack_id)

src/services/sbc.rs

@@ -12,6 +12,10 @@ use anyhow::Context;
 use std::path::Path;
 use uuid::Uuid;
 
+/// Upper bound on cards in one SBC submission (a real squad SBC is 11; consumables
+/// push it higher, but 30 is well beyond any legitimate challenge and caps a DoS).
+const MAX_SBC_CARDS: usize = 30;
+
 pub fn load_sbc_definitions(data_dir: &str) -> anyhow::Result<Vec<SbcDefinition>> {
     let dir = Path::new(data_dir).join("sbcs");
     let mut defs = Vec::new();
@@ -46,6 +50,26 @@ pub async fn submit_sbc(
         .find(|d| d.id == req.sbc_id)
         .ok_or_else(|| AppError::NotFound(format!("SBC {} not found", req.sbc_id)))?;
 
+    // Reject duplicate owned-card ids and bound the list length. A repeated id would
+    // resolve the SAME owned card N times (each fetch succeeds), so `validate_sbc`
+    // counts it toward the squad size and passes, while the DELETE loop removes it
+    // only once — i.e. any SBC satisfiable with a single duplicated card = free
+    // reward. An unbounded list is also a cheap DoS.
+    if req.owned_card_ids.len() > MAX_SBC_CARDS {
+        return Err(AppError::BadRequest(format!(
+            "too many cards in submission ({}, max {MAX_SBC_CARDS})",
+            req.owned_card_ids.len()
+        )));
+    }
+    {
+        let mut seen = std::collections::HashSet::with_capacity(req.owned_card_ids.len());
+        if let Some(dup) = req.owned_card_ids.iter().find(|id| !seen.insert(*id)) {
+            return Err(AppError::BadRequest(format!(
+                "duplicate card in submission: {dup}"
+            )));
+        }
+    }
+
     // Resolve cards from DB
     let mut cards: Vec<CardDefinition> = Vec::new();
     for owned_id in &req.owned_card_ids {
@@ -79,10 +103,11 @@ pub async fn submit_sbc(
         let sub_id = Uuid::new_v4().to_string();
         let card_ids_json = serde_json::to_string(&req.owned_card_ids)?;
         sqlx::query(
-            "INSERT INTO sbc_submissions (id, profile_id, sbc_id, submitted_card_ids, passed, submitted_at) VALUES (?, ?, ?, ?, 1, ?)"
+            "INSERT INTO sbc_submissions (id, profile_id, club_id, sbc_id, submitted_card_ids, passed, submitted_at) VALUES (?, ?, ?, ?, ?, 1, ?)"
         )
         .bind(&sub_id)
         .bind(profile_id)
+        .bind(club_id)
         .bind(&req.sbc_id)
         .bind(&card_ids_json)
         .bind(chrono::Utc::now().to_rfc3339())

src/services/season.rs

@@ -1,6 +1,6 @@
 use crate::{
     db::Pool,
-    error::AppResult,
+    error::{AppError, AppResult},
     models::season::{Season, SeasonEndSummary, SeasonHistoryEntry, SeasonResult},
     services::{club, pack},
 };
@@ -20,7 +20,9 @@ pub async fn get_or_create(pool: &Pool, profile_id: &str) -> AppResult<Season> {
     .bind(&now)
     .execute(pool)
     .await?;
-    Ok(fetch(pool, profile_id).await?.expect("just inserted"))
+    fetch(pool, profile_id)
+        .await?
+        .ok_or_else(|| AppError::Internal(anyhow::anyhow!("season row missing immediately after insert")))
 }
 
 async fn fetch(pool: &Pool, profile_id: &str) -> AppResult<Option<Season>> {
@@ -66,7 +68,9 @@ pub async fn record_match(
     .execute(pool)
     .await?;
 
-    let season = fetch(pool, profile_id).await?.expect("season must exist");
+    let season = fetch(pool, profile_id)
+        .await?
+        .ok_or_else(|| AppError::Internal(anyhow::anyhow!("season row missing after record_match update")))?;
 
     if !season.is_complete() {
         return Ok((season, None));
@@ -141,7 +145,9 @@ pub async fn record_match(
         pack_awarded: pack_id.map(String::from),
     };
 
-    let updated = fetch(pool, profile_id).await?.expect("season must exist");
+    let updated = fetch(pool, profile_id)
+        .await?
+        .ok_or_else(|| AppError::Internal(anyhow::anyhow!("season row missing after season rollover")))?;
     Ok((updated, Some(summary)))
 }
 

tests/concurrency_repro.rs

@@ -0,0 +1,65 @@
+//! Reproduction for the fresh-DB multi-connection warm-up write failure.
+//! Forces several pooled connections to open concurrently on a brand-new DB and
+//! captures the ACTUAL sqlx/SQLite error (not the service's generic string).
+
+use openfut_core::db::{init_pool, run_migrations};
+use openfut_core::services::economy;
+
+async fn seed_club(pool: &sqlx::SqlitePool) {
+    sqlx::query(
+        "INSERT INTO profiles (id, username, created_at, updated_at) VALUES ('p','p','t','t')",
+    )
+    .execute(pool)
+    .await
+    .unwrap();
+    sqlx::query("INSERT INTO clubs (id, profile_id, name, coins, created_at, updated_at) VALUES ('c','p','c',100000,'t','t')")
+        .execute(pool)
+        .await
+        .unwrap();
+}
+
+#[tokio::test(flavor = "multi_thread", worker_threads = 8)]
+async fn fresh_db_multiconn_concurrent_writes() {
+    let base = std::env::temp_dir().join(format!("ofut-cc-{}", std::process::id()));
+    std::fs::create_dir_all(&base).unwrap();
+    let iters = 100usize;
+    let mut failures = 0usize;
+    let mut first_err = String::new();
+    for i in 0..iters {
+        let url = format!("sqlite://{}/db{i}.db", base.display());
+        let pool = init_pool(&url, 5).await.expect("init_pool");
+        run_migrations(&pool).await.expect("migrations");
+        seed_club(&pool).await;
+        // Fire concurrent credits to force several connections to warm up at once
+        // on the brand-new DB, then a write — the harness's failing shape.
+        let mut handles = Vec::new();
+        for _ in 0..8 {
+            let p = pool.clone();
+            handles.push(tokio::spawn(async move {
+                economy::grant_reward(&p, "c", 1).await
+            }));
+        }
+        for h in handles {
+            match h.await.unwrap() {
+                Ok(_) => {}
+                Err(e) => {
+                    failures += 1;
+                    if first_err.is_empty() {
+                        first_err = format!("{e:?}");
+                    }
+                }
+            }
+        }
+        // Serialization correctness: 8 concurrent +1 credits, no lost update.
+        let bal = economy::balance(&pool, "c").await.unwrap();
+        assert_eq!(bal, 100_008, "iter {i}: lost update under concurrency");
+        pool.close().await;
+    }
+    std::fs::remove_dir_all(&base).ok();
+    assert_eq!(
+        failures,
+        0,
+        "{failures}/{} iterations had a write failure; first error: {first_err}",
+        iters * 8
+    );
+}

tests/import_service_test.rs

@@ -4,8 +4,8 @@
 
 use openfut_core::services::card_db::CardDb;
 use openfut_core::services::import::{
-    apply_profile_import, ImportClub, ImportExtension, ImportOwnedCard, ImportProfile, ImportSlot,
-    ImportSquad, ProfileImportRequest,
+    apply_profile_import, ImportClub, ImportEntitlement, ImportExtension, ImportOwnedCard,
+    ImportProfile, ImportSlot, ImportSquad, ProfileImportRequest,
 };
 
 async fn fresh_pool() -> sqlx::SqlitePool {
@@ -79,6 +79,7 @@ fn request(
         },
         owned,
         squad,
+        entitlements: Vec::new(),
     }
 }
 
@@ -128,6 +129,33 @@ async fn imports_profile_club_owned_and_squad_in_one_shot() {
     assert_eq!(stored_import_fp, "fp-happy");
 }
 
+#[tokio::test]
+async fn imports_entitlements_seeds_unopened_packs() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let ids = valid_ids(2);
+    let ow = owned(&ids);
+    let mut req = request("g_ent", "fp-ent", ow, None);
+    req.entitlements = vec![
+        ImportEntitlement {
+            definition_id: "70".into(),
+        },
+        ImportEntitlement {
+            definition_id: "70".into(),
+        },
+    ];
+    apply_profile_import(&pool, &db, &req)
+        .await
+        .expect("import");
+    // Two unconsumed entitlements seeded into packs (opened = 0).
+    assert_eq!(count(&pool, "packs").await, 2);
+    let unopened: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM packs WHERE opened = 0")
+        .fetch_one(&pool)
+        .await
+        .unwrap();
+    assert_eq!(unopened, 2);
+}
+
 #[tokio::test]
 async fn rerun_same_fingerprint_is_idempotent_noop() {
     let pool = fresh_pool().await;