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/0018_profile_import_fingerprint.sql

@@ -0,0 +1,10 @@
+-- Generic provenance/rerun-identity token for a transactionally imported profile.
+--
+-- Set by the generic profile-import path (services::import). A NULL value means
+-- the profile was created by normal gameplay / dev seeding, not an import, and
+-- MUST NOT be silently clobbered by an import targeting the same game. A
+-- matching token on a re-run is an idempotent no-op; a differing token against
+-- an already-imported game fails until an explicit update mode exists.
+--
+-- Core never interprets the token's structure; the importer adapter chooses it.
+ALTER TABLE profiles ADD COLUMN import_fingerprint TEXT;

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

@@ -46,7 +46,34 @@ pub async fn build(pool: Pool, cfg: Config) -> Result<Router> {
     for game in &cfg.dev_content_games {
         card_db.load_game_dev(&cfg.data_dir, game)?;
     }
+    for pack in &cfg.content_packs {
+        card_db.load_pack(pack)?;
+    }
     let card_db = Arc::new(card_db);
+
+    // Content preflight: every owned card MUST reference a loaded CardDefinition.
+    // A real profile with owned players but missing definitions fails LOUDLY here
+    // rather than silently serving an empty /collection. Empty owned_cards (fresh
+    // DB, tests) passes. A SINGLE missing definition is caught, not only the
+    // zero-loaded case.
+    {
+        let referenced: Vec<String> =
+            sqlx::query_scalar("SELECT DISTINCT card_id FROM owned_cards")
+                .fetch_all(&pool)
+                .await?;
+        let missing: Vec<String> = referenced
+            .into_iter()
+            .filter(|id| card_db.get(id).is_none())
+            .collect();
+        if !missing.is_empty() {
+            let sample: Vec<&String> = missing.iter().take(5).collect();
+            anyhow::bail!(
+                "content preflight failed: {} owned card(s) reference CardDefinitionId(s) not loaded (e.g. {:?}). Load the production content pack via OPENFUT_CONTENT_PACKS.",
+                missing.len(),
+                sample
+            );
+        }
+    }
     let pack_defs = Arc::new(load_pack_definitions(&cfg.data_dir)?);
     let obj_defs = Arc::new(load_objective_definitions(&cfg.data_dir)?);
     let sbc_defs = Arc::new(load_sbc_definitions(&cfg.data_dir)?);
@@ -145,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),
@@ -178,7 +235,10 @@ pub async fn build(pool: Pool, cfg: Config) -> Result<Router> {
         .route("/squads/:squad_id", get(routes::squad::get_squad_by_id))
         .route("/squads/:squad_id", delete(routes::squad::delete_squad))
         .route("/objectives", get(routes::objectives::get_objectives))
-        .route("/objectives/:objective_id", get(routes::objectives::get_objective))
+        .route(
+            "/objectives/:objective_id",
+            get(routes::objectives::get_objective),
+        )
         .route(
             "/objectives/claim",
             post(routes::objectives::post_claim_objective),
@@ -196,7 +256,10 @@ pub async fn build(pool: Pool, cfg: Config) -> Result<Router> {
         .route("/market", get(routes::market::get_market))
         .route("/market/buy", post(routes::market::post_market_buy))
         .route("/market/sell", post(routes::market::post_market_sell))
-        .route("/market/trade-history", get(routes::market::get_trade_history))
+        .route(
+            "/market/trade-history",
+            get(routes::market::get_trade_history),
+        )
         .route("/market/refresh", post(routes::market::post_market_refresh))
         .route("/market/my-listings", get(routes::market::get_my_listings))
         .route(
@@ -211,15 +274,36 @@ pub async fn build(pool: Pool, cfg: Config) -> Result<Router> {
         .route("/settings", get(routes::settings::get_settings))
         .route("/settings", put(routes::settings::put_settings))
         .route("/division", get(routes::division::get_division))
-        .route("/division/history", get(routes::division::get_division_history))
-        .route("/division/leaderboard", get(routes::division::get_division_leaderboard))
+        .route(
+            "/division/history",
+            get(routes::division::get_division_history),
+        )
+        .route(
+            "/division/leaderboard",
+            get(routes::division::get_division_leaderboard),
+        )
         .route("/achievements", get(routes::achievements::get_achievements))
-        .route("/notifications", get(routes::notifications::get_notifications))
-        .route("/notifications/read-all", post(routes::notifications::mark_all_notifications_read))
-        .route("/notifications/:id/read", patch(routes::notifications::mark_notification_read))
+        .route(
+            "/notifications",
+            get(routes::notifications::get_notifications),
+        )
+        .route(
+            "/notifications/read-all",
+            post(routes::notifications::mark_all_notifications_read),
+        )
+        .route(
+            "/notifications/:id/read",
+            patch(routes::notifications::mark_notification_read),
+        )
         .route("/fut-champs", get(routes::fut_champs::get_fut_champs))
-        .route("/fut-champs/start", post(routes::fut_champs::post_start_fut_champs))
-        .route("/fut-champs/history", get(routes::fut_champs::get_champs_history))
+        .route(
+            "/fut-champs/start",
+            post(routes::fut_champs::post_start_fut_champs),
+        )
+        .route(
+            "/fut-champs/history",
+            get(routes::fut_champs::get_champs_history),
+        )
         .route(
             "/fut-champs/:session_id/result",
             post(routes::fut_champs::post_champs_result),

src/config.rs

@@ -1,4 +1,5 @@
 use anyhow::Result;
+use std::path::PathBuf;
 
 #[derive(Debug, Clone)]
 pub struct Config {
@@ -10,6 +11,11 @@ pub struct Config {
     /// loaded IN ADDITION to the default `data/cards` catalog. Empty by default —
     /// default/test content is never affected unless a game is named here.
     pub dev_content_games: Vec<String>,
+    /// Explicit PRODUCTION content pack file paths (each a `CardDefinition[]`
+    /// JSON), loaded IN ADDITION to `data/cards` and any dev pack. This is the
+    /// production real-profile content path — deliberately NOT gated behind the
+    /// dev-only `dev_content_games`.
+    pub content_packs: Vec<PathBuf>,
 }
 
 impl Config {
@@ -33,6 +39,16 @@ impl Config {
                         .collect()
                 })
                 .unwrap_or_default(),
+            content_packs: std::env::var("OPENFUT_CONTENT_PACKS")
+                .ok()
+                .map(|v| {
+                    v.split(',')
+                        .map(str::trim)
+                        .filter(|s| !s.is_empty())
+                        .map(PathBuf::from)
+                        .collect()
+                })
+                .unwrap_or_default(),
         })
     }
 }

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/lib.rs

@@ -22,6 +22,7 @@ pub async fn build_app(pool: db::Pool, data_dir: &str) -> Result<Router> {
         data_dir: data_dir.to_string(),
         max_connections: 1,
         dev_content_games: Vec::new(),
+        content_packs: Vec::new(),
     };
     app::build(pool, cfg).await
 }

src/main.rs

@@ -1,4 +1,4 @@
-use anyhow::Result;
+use anyhow::{Context, Result};
 use openfut_core::{config, db, seed};
 use tracing::info;
 use tracing_subscriber::{layer::SubscriberExt, util::SubscriberInitExt, EnvFilter};
@@ -12,7 +12,9 @@ async fn main() -> Result<()> {
             EnvFilter::try_from_default_env()
                 .unwrap_or_else(|_| "openfut_core=debug,tower_http=debug".into()),
         )
-        .with(tracing_subscriber::fmt::layer())
+        // Diagnostics on stderr so stdout carries only machine output (the
+        // `import`/`seed-dev` subcommands print a clean JSON result there).
+        .with(tracing_subscriber::fmt::layer().with_writer(std::io::stderr))
         .init();
 
     let cfg = config::Config::from_env()?;
@@ -30,6 +32,31 @@ async fn main() -> Result<()> {
         return Ok(());
     }
 
+    // Opt-in generic import subcommand: `openfut-core import <request.json>`.
+    // Reads a GAME-AGNOSTIC ProfileImportRequest (the importer adapter translates
+    // FIFA17 source data into it), loads production content packs, runs preflight,
+    // and applies one all-or-nothing transaction. FIFA17 semantics live entirely
+    // in the adapter; Core only sees opaque ids + opaque extension bytes.
+    if std::env::args().nth(1).as_deref() == Some("import") {
+        let path = std::env::args()
+            .nth(2)
+            .context("usage: openfut-core import <request.json>")?;
+        let pool = db::init_pool(&cfg.database_url, cfg.max_connections).await?;
+        db::run_migrations(&pool).await?;
+        let mut card_db = openfut_core::services::card_db::CardDb::load(&cfg.data_dir)?;
+        for pack in &cfg.content_packs {
+            card_db.load_pack(pack)?;
+        }
+        let raw = std::fs::read_to_string(&path)
+            .with_context(|| format!("read import request {path}"))?;
+        let req: openfut_core::services::import::ProfileImportRequest =
+            serde_json::from_str(&raw).context("parse import request JSON")?;
+        let outcome =
+            openfut_core::services::import::apply_profile_import(&pool, &card_db, &req).await?;
+        println!("{}", serde_json::to_string_pretty(&outcome)?);
+        return Ok(());
+    }
+
     info!("OpenFUT Core starting on {}", cfg.listen_addr);
 
     let pool = db::init_pool(&cfg.database_url, cfg.max_connections).await?;

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/card_db.rs

@@ -62,6 +62,23 @@ impl CardDb {
         Ok(n)
     }
 
+    /// Merge an explicit PRODUCTION content pack file (a single
+    /// `CardDefinition[]`). Unlike [`CardDb::load_game_dev`] this takes a direct
+    /// path (the real-profile import emits one) and is the production content
+    /// path — not gated behind dev content. Returns the number merged.
+    pub fn load_pack(&mut self, path: &Path) -> Result<usize> {
+        let content = std::fs::read_to_string(path)
+            .with_context(|| format!("reading content pack {path:?}"))?;
+        let batch: Vec<CardDefinition> =
+            serde_json::from_str(&content).with_context(|| format!("parsing {path:?}"))?;
+        let n = batch.len();
+        for card in batch {
+            self.cards.insert(card.id.clone(), card);
+        }
+        tracing::info!("Loaded {} production card definitions from {:?}", n, path);
+        Ok(n)
+    }
+
     pub fn get(&self, id: &str) -> Option<&CardDefinition> {
         self.cards.get(id)
     }

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

@@ -0,0 +1,341 @@
+//! Generic, game-agnostic transactional profile import.
+//!
+//! Core installs a profile + club + owned cards + canonical squad + one opaque
+//! game extension in a SINGLE all-or-nothing SQLite transaction, stamped with a
+//! generic `source_fingerprint` provenance token. Core NEVER interprets FIFA17
+//! wire ids, resourceIds, `nextItemId`, or the extension payload — the
+//! `openfut-import-fifa17` adapter reads the Python profile, chooses every
+//! `CardDefinitionId` and every opaque `OwnedItemId`, builds the squad
+//! extension bytes, and hands Core this generic request.
+//!
+//! Invariants enforced here:
+//! - Definition preflight: every incoming `card_id` MUST already resolve in the
+//!   loaded production content, so the transaction never creates ownership
+//!   pointing at absent content.
+//! - Squad all-or-nothing: every active-squad `owned_item_id` MUST be among the
+//!   imported ownership set before the transaction begins.
+//! - Rerun identity: identical `source_fingerprint` against an already-imported
+//!   game is an idempotent no-op; a differing token fails; a pre-existing
+//!   non-imported profile is never clobbered.
+//! - The whole thing commits together or not at all.
+
+use crate::db::Pool;
+use crate::models::game_ext::{MAX_EXT_NAMESPACE_LEN, MAX_EXT_PAYLOAD_BYTES};
+use crate::services::card_db::CardDb;
+use crate::services::squad::squad_fingerprint;
+use anyhow::{bail, Context, Result};
+use chrono::Utc;
+use serde::{Deserialize, Serialize};
+use std::collections::HashSet;
+use uuid::Uuid;
+
+#[derive(Debug, Deserialize)]
+pub struct ImportProfile {
+    pub username: String,
+    pub game_id: String,
+}
+
+#[derive(Debug, Deserialize)]
+pub struct ImportClub {
+    pub name: String,
+    #[serde(default)]
+    pub coins: i64,
+}
+
+#[derive(Debug, Deserialize)]
+pub struct ImportOwnedCard {
+    /// Opaque, stable Core OwnedItemId chosen by the adapter. Core never parses
+    /// why it is stable — it is a primary key, nothing more.
+    pub owned_item_id: String,
+    /// CardDefinitionId that MUST resolve in loaded production content.
+    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,
+    pub position_index: i64,
+    #[serde(default)]
+    pub is_captain: bool,
+    #[serde(default)]
+    pub is_on_bench: bool,
+}
+
+#[derive(Debug, Deserialize)]
+pub struct ImportExtension {
+    /// Opaque adapter key, e.g. "fifa17.squad.v1".
+    pub namespace: String,
+    /// Adapter payload version (distinct from DB storage schema).
+    pub schema_version: i64,
+    /// Uninterpreted bytes-as-text. Core enforces only generic size bounds.
+    pub payload: String,
+}
+
+#[derive(Debug, Deserialize)]
+pub struct ImportSquad {
+    pub formation: String,
+    #[serde(default = "default_squad_name")]
+    pub name: String,
+    pub slots: Vec<ImportSlot>,
+    pub extension: ImportExtension,
+}
+
+fn default_squad_name() -> String {
+    "My Squad".to_string()
+}
+
+#[derive(Debug, Deserialize)]
+pub struct ProfileImportRequest {
+    /// Generic provenance/rerun-identity token. Core stores it verbatim.
+    pub source_fingerprint: String,
+    pub profile: ImportProfile,
+    pub club: ImportClub,
+    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)]
+#[serde(tag = "outcome", rename_all = "snake_case")]
+pub enum ImportOutcome {
+    /// A fresh import committed.
+    Imported { owned: usize, squad_slots: usize },
+    /// The same fingerprint was already imported for this game — no-op.
+    AlreadyImported,
+}
+
+/// Apply a generic transactional profile import. See module docs for invariants.
+pub async fn apply_profile_import(
+    pool: &Pool,
+    card_db: &CardDb,
+    req: &ProfileImportRequest,
+) -> Result<ImportOutcome> {
+    // ── 0. generic input validation (no writes) ──
+    if req.source_fingerprint.trim().is_empty() {
+        bail!("source_fingerprint must be non-empty");
+    }
+    if req.owned.is_empty() {
+        bail!("import request has zero owned cards; refusing to import an empty profile");
+    }
+
+    // ── 1. rerun identity / single-profile-per-game ──
+    let existing: Option<(String, Option<String>)> = sqlx::query_as(
+        "SELECT id, import_fingerprint FROM profiles \
+         WHERE game_id = ? ORDER BY created_at ASC LIMIT 1",
+    )
+    .bind(&req.profile.game_id)
+    .fetch_optional(pool)
+    .await?;
+    if let Some((_id, fp)) = existing {
+        match fp {
+            Some(fp) if fp == req.source_fingerprint => return Ok(ImportOutcome::AlreadyImported),
+            Some(fp) => bail!(
+                "game '{}' already imported from a different source (stored fingerprint {fp}, \
+                 incoming {}); refusing to overwrite without an explicit update mode",
+                req.profile.game_id,
+                req.source_fingerprint
+            ),
+            None => bail!(
+                "game '{}' already has a non-imported profile; refusing to clobber it",
+                req.profile.game_id
+            ),
+        }
+    }
+
+    // ── 2. definition preflight: every card_id MUST resolve in loaded content ──
+    let mut missing: Vec<&str> = req
+        .owned
+        .iter()
+        .filter(|o| card_db.get(&o.card_id).is_none())
+        .map(|o| o.card_id.as_str())
+        .collect();
+    if !missing.is_empty() {
+        missing.sort_unstable();
+        missing.dedup();
+        let sample = &missing[..missing.len().min(5)];
+        bail!(
+            "definition preflight failed: {} owned card(s) reference CardDefinitionId(s) not in \
+             loaded content (e.g. {sample:?}); refusing to create ownership pointing at absent content",
+            missing.len()
+        );
+    }
+
+    // ── 3. owned-item-id uniqueness ──
+    let mut owned_ids: HashSet<&str> = HashSet::with_capacity(req.owned.len());
+    for o in &req.owned {
+        if !owned_ids.insert(o.owned_item_id.as_str()) {
+            bail!(
+                "duplicate OwnedItemId in import request: {}",
+                o.owned_item_id
+            );
+        }
+    }
+
+    // ── 4. squad all-or-nothing + generic extension bounds (no writes) ──
+    if let Some(sq) = &req.squad {
+        let ns_len = sq.extension.namespace.len();
+        if ns_len == 0 || ns_len > MAX_EXT_NAMESPACE_LEN {
+            bail!(
+                "extension namespace length {ns_len} out of bounds (1..={MAX_EXT_NAMESPACE_LEN})"
+            );
+        }
+        if sq.extension.payload.len() > MAX_EXT_PAYLOAD_BYTES {
+            bail!(
+                "extension payload {} bytes exceeds MAX_EXT_PAYLOAD_BYTES ({MAX_EXT_PAYLOAD_BYTES})",
+                sq.extension.payload.len()
+            );
+        }
+        for slot in &sq.slots {
+            if !owned_ids.contains(slot.owned_item_id.as_str()) {
+                bail!(
+                    "active squad references OwnedItemId {} not present in imported ownership set; \
+                     squad import is all-or-nothing",
+                    slot.owned_item_id
+                );
+            }
+        }
+    }
+
+    // ── 5. single transaction: everything commits together or not at all ──
+    let now = Utc::now().to_rfc3339();
+    let profile_id = Uuid::new_v4().to_string();
+    let club_id = Uuid::new_v4().to_string();
+    let mut tx = pool.begin().await?;
+
+    sqlx::query(
+        "INSERT INTO profiles (id, username, level, xp, game_id, created_at, updated_at, import_fingerprint) \
+         VALUES (?, ?, 1, 0, ?, ?, ?, ?)",
+    )
+    .bind(&profile_id)
+    .bind(&req.profile.username)
+    .bind(&req.profile.game_id)
+    .bind(&now)
+    .bind(&now)
+    .bind(&req.source_fingerprint)
+    .execute(&mut *tx)
+    .await
+    .context("insert profile")?;
+
+    sqlx::query(
+        "INSERT INTO clubs (id, profile_id, name, coins, level, created_at, updated_at) \
+         VALUES (?, ?, ?, ?, 1, ?, ?)",
+    )
+    .bind(&club_id)
+    .bind(&profile_id)
+    .bind(&req.club.name)
+    .bind(req.club.coins)
+    .bind(&now)
+    .bind(&now)
+    .execute(&mut *tx)
+    .await
+    .context("insert club")?;
+
+    for o in &req.owned {
+        sqlx::query(
+            "INSERT INTO owned_cards (id, club_id, card_id, is_loan, loan_matches_remaining, acquired_at) \
+             VALUES (?, ?, ?, 0, NULL, ?)",
+        )
+        .bind(&o.owned_item_id)
+        .bind(&club_id)
+        .bind(&o.card_id)
+        .bind(&now)
+        .execute(&mut *tx)
+        .await
+        .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();
+        sqlx::query(
+            "INSERT INTO squads (id, club_id, name, formation, created_at, updated_at) \
+             VALUES (?, ?, ?, ?, ?, ?)",
+        )
+        .bind(&squad_id)
+        .bind(&club_id)
+        .bind(&sq.name)
+        .bind(&sq.formation)
+        .bind(&now)
+        .bind(&now)
+        .execute(&mut *tx)
+        .await
+        .context("insert squad")?;
+
+        for slot in &sq.slots {
+            sqlx::query(
+                "INSERT INTO squad_players (id, squad_id, owned_card_id, position_index, is_captain, is_on_bench) \
+                 VALUES (?, ?, ?, ?, ?, ?)",
+            )
+            .bind(Uuid::new_v4().to_string())
+            .bind(&squad_id)
+            .bind(&slot.owned_item_id)
+            .bind(slot.position_index)
+            .bind(slot.is_captain)
+            .bind(slot.is_on_bench)
+            .execute(&mut *tx)
+            .await
+            .context("insert squad_player")?;
+        }
+        squad_slots = sq.slots.len();
+
+        // Core computes the canonical fingerprint over the COMMITTED squad — never
+        // an adapter-supplied value — and persists the opaque extension atomically
+        // in the same tx, exactly as the live squad-write path does.
+        let canonical_fingerprint = squad_fingerprint(
+            &squad_id,
+            &sq.formation,
+            sq.slots.iter().map(|s| {
+                (
+                    s.position_index,
+                    s.owned_item_id.as_str(),
+                    s.is_captain,
+                    s.is_on_bench,
+                )
+            }),
+        );
+        sqlx::query(
+            "INSERT OR REPLACE INTO game_entity_ext \
+             (game_id, entity_kind, entity_id, namespace, schema_version, canonical_fingerprint, payload, updated_at) \
+             VALUES (?, 'squad', ?, ?, ?, ?, ?, ?)",
+        )
+        .bind(&req.profile.game_id)
+        .bind(&squad_id)
+        .bind(&sq.extension.namespace)
+        .bind(sq.extension.schema_version)
+        .bind(&canonical_fingerprint)
+        .bind(&sq.extension.payload)
+        .bind(&now)
+        .execute(&mut *tx)
+        .await
+        .context("insert game_entity_ext")?;
+    }
+
+    tx.commit().await?;
+    Ok(ImportOutcome::Imported {
+        owned: req.owned.len(),
+        squad_slots,
+    })
+}

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

@@ -2,19 +2,21 @@ pub mod achievement;
 pub mod card_db;
 pub mod checkin;
 pub mod club;
-pub mod notification;
 pub mod draft;
+pub mod economy;
 pub mod event;
 pub mod fut_champs;
 pub mod game_ext;
+pub mod import;
 pub mod inventory;
-pub mod season;
 pub mod market;
 pub mod match_service;
+pub mod notification;
 pub mod objective;
 pub mod pack;
 pub mod profile;
 pub mod sbc;
+pub mod season;
 pub mod settings;
 pub mod squad;
 pub mod squad_rules;

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)))
 }
 

src/services/squad.rs

@@ -497,7 +497,7 @@ pub async fn replace_squad_with_extension(
 /// computed; non-cryptographic (FNV-1a-64) — a stale-extension guard, not a
 /// security boundary. The encoding is sorted + delimited so it never depends on
 /// row/iteration order.
-fn squad_fingerprint<'a>(
+pub(crate) fn squad_fingerprint<'a>(
     squad_id: &str,
     formation: &str,
     slots: impl Iterator<Item = (i64, &'a str, bool, bool)>,

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/content_preflight_test.rs

@@ -0,0 +1,104 @@
+//! Content preflight: a real profile with owned players but a missing
+//! CardDefinition must fail startup LOUDLY, never serve a silent empty club.
+
+use axum::{
+    body::Body,
+    http::{Request, StatusCode},
+};
+use openfut_core::services::card_db::CardDb;
+use tower::ServiceExt;
+
+async fn fresh_pool() -> sqlx::SqlitePool {
+    let p = sqlx::sqlite::SqlitePoolOptions::new()
+        .max_connections(1)
+        .connect("sqlite::memory:")
+        .await
+        .expect("in-memory sqlite");
+    sqlx::migrate!("./migrations")
+        .run(&p)
+        .await
+        .expect("migrations");
+    p
+}
+
+async fn create_profile(app: &axum::Router) {
+    let resp = app
+        .clone()
+        .oneshot(
+            Request::builder()
+                .method("POST")
+                .uri("/auth/local")
+                .header("content-type", "application/json")
+                .body(Body::from(r#"{"username":"CAGE"}"#))
+                .unwrap(),
+        )
+        .await
+        .unwrap();
+    assert_eq!(
+        resp.status(),
+        StatusCode::OK,
+        "auth/local should create a profile+club"
+    );
+}
+
+async fn insert_owned(pool: &sqlx::SqlitePool, id: &str, club_id: &str, card_id: &str) {
+    sqlx::query(
+        "INSERT INTO owned_cards (id, club_id, card_id, is_loan, loan_matches_remaining, acquired_at) \
+         VALUES (?, ?, ?, 0, NULL, ?)",
+    )
+    .bind(id)
+    .bind(club_id)
+    .bind(card_id)
+    .bind("2026-01-01T00:00:00Z")
+    .execute(pool)
+    .await
+    .unwrap();
+}
+
+#[tokio::test]
+async fn preflight_fails_on_owned_card_missing_definition() {
+    let pool = fresh_pool().await;
+    // first build is fine: no owned cards yet.
+    let app = openfut_core::build_app(pool.clone(), "data").await.unwrap();
+    create_profile(&app).await;
+    let club: String = sqlx::query_scalar("SELECT id FROM clubs LIMIT 1")
+        .fetch_one(&pool)
+        .await
+        .unwrap();
+    insert_owned(&pool, "oc-bogus", &club, "fifa17_definitely_missing_999999").await;
+
+    // second build must now fail preflight: one owned card references a def that
+    // is not loaded — must not silently serve an empty collection.
+    let err = openfut_core::build_app(pool.clone(), "data")
+        .await
+        .expect_err("preflight must fail on a missing definition");
+    let msg = format!("{err:#}");
+    assert!(
+        msg.contains("content preflight failed"),
+        "unexpected error: {msg}"
+    );
+}
+
+#[tokio::test]
+async fn preflight_passes_when_owned_card_definition_is_loaded() {
+    let pool = fresh_pool().await;
+    // pick a definition that IS in the default data/cards catalog.
+    let valid_id = CardDb::load("data")
+        .unwrap()
+        .all()
+        .first()
+        .map(|c| c.id.clone())
+        .expect("data/cards must be non-empty");
+
+    let app = openfut_core::build_app(pool.clone(), "data").await.unwrap();
+    create_profile(&app).await;
+    let club: String = sqlx::query_scalar("SELECT id FROM clubs LIMIT 1")
+        .fetch_one(&pool)
+        .await
+        .unwrap();
+    insert_owned(&pool, "oc-valid", &club, &valid_id).await;
+
+    let _app = openfut_core::build_app(pool.clone(), "data")
+        .await
+        .expect("preflight passes when the owned card's definition is loaded");
+}

tests/import_service_test.rs

@@ -0,0 +1,284 @@
+//! Generic transactional profile import (services::import). These also serve as
+//! the Core-level half of the migration mutation battery: each hostile input is
+//! rejected BEFORE any partial write, and re-runs converge instead of duplicating.
+
+use openfut_core::services::card_db::CardDb;
+use openfut_core::services::import::{
+    apply_profile_import, ImportClub, ImportEntitlement, ImportExtension, ImportOwnedCard,
+    ImportProfile, ImportSlot, ImportSquad, ProfileImportRequest,
+};
+
+async fn fresh_pool() -> sqlx::SqlitePool {
+    let p = sqlx::sqlite::SqlitePoolOptions::new()
+        .max_connections(1)
+        .connect("sqlite::memory:")
+        .await
+        .expect("in-memory sqlite");
+    sqlx::migrate!("./migrations")
+        .run(&p)
+        .await
+        .expect("migrations");
+    p
+}
+
+fn valid_ids(n: usize) -> Vec<String> {
+    let db = CardDb::load("data").expect("load data catalog");
+    let ids: Vec<String> = db.all().iter().take(n).map(|c| c.id.clone()).collect();
+    assert!(ids.len() >= n, "data catalog too small for test");
+    ids
+}
+
+fn owned(ids: &[String]) -> Vec<ImportOwnedCard> {
+    ids.iter()
+        .enumerate()
+        .map(|(i, id)| ImportOwnedCard {
+            owned_item_id: format!("oc-{i}"),
+            card_id: id.clone(),
+        })
+        .collect()
+}
+
+fn squad_over(owned: &[ImportOwnedCard]) -> ImportSquad {
+    ImportSquad {
+        formation: "f433".into(),
+        name: "OpenFUT".into(),
+        slots: owned
+            .iter()
+            .take(3)
+            .enumerate()
+            .map(|(i, o)| ImportSlot {
+                owned_item_id: o.owned_item_id.clone(),
+                position_index: i as i64,
+                is_captain: i == 0,
+                is_on_bench: false,
+            })
+            .collect(),
+        extension: ImportExtension {
+            namespace: "fifa17.squad.v1".into(),
+            schema_version: 1,
+            payload: r#"{"custom":[]}"#.into(),
+        },
+    }
+}
+
+fn request(
+    game: &str,
+    fp: &str,
+    owned: Vec<ImportOwnedCard>,
+    squad: Option<ImportSquad>,
+) -> ProfileImportRequest {
+    ProfileImportRequest {
+        source_fingerprint: fp.into(),
+        profile: ImportProfile {
+            username: format!("CAGE-{game}"),
+            game_id: game.into(),
+        },
+        club: ImportClub {
+            name: "OpenFUT".into(),
+            coins: 28_112_944,
+        },
+        owned,
+        squad,
+        entitlements: Vec::new(),
+    }
+}
+
+async fn count(pool: &sqlx::SqlitePool, table: &str) -> i64 {
+    sqlx::query_scalar(&format!("SELECT COUNT(*) FROM {table}"))
+        .fetch_one(pool)
+        .await
+        .unwrap()
+}
+
+#[tokio::test]
+async fn imports_profile_club_owned_and_squad_in_one_shot() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let ids = valid_ids(5);
+    let ow = owned(&ids);
+    let sq = squad_over(&ow);
+    let req = request("g_happy", "fp-happy", ow, Some(sq));
+
+    let out = apply_profile_import(&pool, &db, &req)
+        .await
+        .expect("import");
+    assert!(matches!(
+        out,
+        openfut_core::services::import::ImportOutcome::Imported {
+            owned: 5,
+            squad_slots: 3
+        }
+    ));
+
+    assert_eq!(count(&pool, "profiles").await, 1);
+    assert_eq!(count(&pool, "clubs").await, 1);
+    assert_eq!(count(&pool, "owned_cards").await, 5);
+    assert_eq!(count(&pool, "squad_players").await, 3);
+    // opaque extension persisted with a Core-computed fingerprint.
+    let fp: String =
+        sqlx::query_scalar("SELECT canonical_fingerprint FROM game_entity_ext LIMIT 1")
+            .fetch_one(&pool)
+            .await
+            .unwrap();
+    assert_eq!(fp.len(), 16, "16-hex FNV fingerprint");
+    let stored_import_fp: String =
+        sqlx::query_scalar("SELECT import_fingerprint FROM profiles LIMIT 1")
+            .fetch_one(&pool)
+            .await
+            .unwrap();
+    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;
+    let db = CardDb::load("data").unwrap();
+    let ids = valid_ids(4);
+    let mk = || {
+        request(
+            "g_rerun",
+            "fp-x",
+            owned(&ids),
+            Some(squad_over(&owned(&ids))),
+        )
+    };
+
+    apply_profile_import(&pool, &db, &mk())
+        .await
+        .expect("first");
+    let out = apply_profile_import(&pool, &db, &mk())
+        .await
+        .expect("second");
+    assert_eq!(
+        out,
+        openfut_core::services::import::ImportOutcome::AlreadyImported
+    );
+    // no duplication.
+    assert_eq!(count(&pool, "profiles").await, 1);
+    assert_eq!(count(&pool, "owned_cards").await, 4);
+}
+
+#[tokio::test]
+async fn different_fingerprint_on_imported_game_fails() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let ids = valid_ids(3);
+    apply_profile_import(&pool, &db, &request("g_diff", "fp-a", owned(&ids), None))
+        .await
+        .expect("first");
+    let err = apply_profile_import(&pool, &db, &request("g_diff", "fp-b", owned(&ids), None))
+        .await
+        .expect_err("second, different fingerprint");
+    assert!(format!("{err:#}").contains("different source"), "{err:#}");
+    assert_eq!(count(&pool, "profiles").await, 1);
+}
+
+#[tokio::test]
+async fn missing_definition_fails_preflight_with_no_writes() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let mut ow = owned(&valid_ids(2));
+    ow.push(ImportOwnedCard {
+        owned_item_id: "oc-bad".into(),
+        card_id: "fifa17_definitely_absent_999999".into(),
+    });
+    let err = apply_profile_import(&pool, &db, &request("g_miss", "fp", ow, None))
+        .await
+        .expect_err("missing definition must fail");
+    assert!(
+        format!("{err:#}").contains("definition preflight failed"),
+        "{err:#}"
+    );
+    // preflight is before the tx: nothing was written.
+    assert_eq!(count(&pool, "profiles").await, 0);
+    assert_eq!(count(&pool, "owned_cards").await, 0);
+}
+
+#[tokio::test]
+async fn squad_slot_not_in_ownership_fails() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let ids = valid_ids(3);
+    let ow = owned(&ids);
+    let mut sq = squad_over(&ow);
+    sq.slots[1].owned_item_id = "oc-not-owned".into();
+    let err = apply_profile_import(&pool, &db, &request("g_sq", "fp", ow, Some(sq)))
+        .await
+        .expect_err("squad slot not owned must fail");
+    assert!(format!("{err:#}").contains("all-or-nothing"), "{err:#}");
+    assert_eq!(count(&pool, "profiles").await, 0);
+}
+
+#[tokio::test]
+async fn duplicate_owned_item_id_fails() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let ids = valid_ids(2);
+    let mut ow = owned(&ids);
+    ow[1].owned_item_id = ow[0].owned_item_id.clone();
+    let err = apply_profile_import(&pool, &db, &request("g_dup", "fp", ow, None))
+        .await
+        .expect_err("duplicate OwnedItemId must fail");
+    assert!(
+        format!("{err:#}").contains("duplicate OwnedItemId"),
+        "{err:#}"
+    );
+    assert_eq!(count(&pool, "profiles").await, 0);
+}
+
+#[tokio::test]
+async fn non_imported_profile_is_not_clobbered() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    // simulate a gameplay/dev profile with NO import_fingerprint for this game.
+    sqlx::query(
+        "INSERT INTO profiles (id, username, level, xp, game_id, created_at, updated_at) \
+         VALUES ('p0','someone',1,0,'g_clobber','2026-01-01T00:00:00Z','2026-01-01T00:00:00Z')",
+    )
+    .execute(&pool)
+    .await
+    .unwrap();
+    let ids = valid_ids(2);
+    let err = apply_profile_import(&pool, &db, &request("g_clobber", "fp", owned(&ids), None))
+        .await
+        .expect_err("must refuse to clobber a non-imported profile");
+    assert!(format!("{err:#}").contains("non-imported"), "{err:#}");
+    assert_eq!(count(&pool, "owned_cards").await, 0);
+}
+
+#[tokio::test]
+async fn empty_owned_fails() {
+    let pool = fresh_pool().await;
+    let db = CardDb::load("data").unwrap();
+    let err = apply_profile_import(&pool, &db, &request("g_empty", "fp", vec![], None))
+        .await
+        .expect_err("empty owned must fail");
+    assert!(format!("{err:#}").contains("zero owned cards"), "{err:#}");
+}