fix(economy): BEGIN IMMEDIATE for write transactions (concurrency-safe)

Root cause of the fresh-DB multi-connection write failure: economy writes ran in
a DEFERRED transaction (pool.begin() = BEGIN) that read then wrote; SQLite returns
SQLITE_BUSY (code 5, 'database is locked') *immediately* when a deferred tx
upgrades to a write while another holds the write lock, bypassing busy_timeout to
avoid deadlock. Fix: open each write op with BEGIN IMMEDIATE on a dedicated pooled
connection (finish() commits/rolls back), taking the write lock up front so
busy_timeout serializes writers. Reproduction test: 100 fresh DBs x 8 concurrent
grant_reward — was 644/800 failures, now 800/800 succeed with correct final
balance (no lost update). Reads unchanged.

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;

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,32 @@ 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(
             "/collection/:owned_card_id",
             delete(routes::cards::delete_owned_card),
@@ -178,7 +231,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 +252,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 +270,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/economy.rs

@@ -0,0 +1,163 @@
+//! 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 }))
+}

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

@@ -0,0 +1,610 @@
+//! Generic, game-agnostic economy authority.
+//!
+//! Exposes atomic, fail-closed economy operations over Core's existing durable
+//! tables — it does **not** introduce a parallel persistence stack:
+//!
+//!   * currency ledger -> `clubs.coins`
+//!   * owned inventory -> `owned_cards`
+//!   * entitlements    -> `packs` (opaque `definition_id` + consume-once `opened`)
+//!
+//! Every compound operation (purchase, redeem, sell) runs inside a single SQLite
+//! transaction, so a partial failure leaves no balance or inventory drift — the
+//! pool-scoped helpers in [`crate::services::club`] cannot offer that guarantee
+//! because their read/modify/write spans multiple pool round-trips.
+//!
+//! This module is deliberately game-neutral: currency names, entitlement/pack
+//! ids, and per-save item-id sequences are per-game concerns that live in the
+//! adapter which drives these primitives, never here.
+
+use crate::{
+    db::Pool,
+    error::{AppError, AppResult},
+};
+use chrono::Utc;
+use serde::{Deserialize, Serialize};
+use sqlx::SqliteConnection;
+use uuid::Uuid;
+
+/// An instance to place into a club's inventory when an entitlement is redeemed.
+#[derive(Debug, Clone, Serialize, Deserialize)]
+pub struct GrantedItem {
+    /// Caller-minted opaque instance id. The adapter owns the id scheme; Core
+    /// treats it as an opaque unique key.
+    pub item_id: String,
+    /// Definition reference this instance resolves against.
+    pub card_id: String,
+}
+
+/// Outcome of a purchase: the post-debit balance and the new entitlement id.
+#[derive(Debug, Clone, Serialize)]
+pub struct PurchaseReceipt {
+    pub balance: i64,
+    pub entitlement_id: String,
+}
+
+// ---- transaction-scoped primitives -------------------------------------------
+// Each takes a live connection (a transaction, reborrowed) so callers can compose
+// several into one atomic unit. They never commit; the composed public op does.
+
+async fn read_balance(conn: &mut SqliteConnection, club_id: &str) -> AppResult<i64> {
+    sqlx::query_scalar::<_, i64>("SELECT coins FROM clubs WHERE id = ?")
+        .bind(club_id)
+        .fetch_optional(&mut *conn)
+        .await?
+        .ok_or_else(|| AppError::NotFound(format!("club not found: {club_id}")))
+}
+
+async fn debit(conn: &mut SqliteConnection, club_id: &str, amount: i64) -> AppResult<i64> {
+    if amount < 0 {
+        return Err(AppError::BadRequest(
+            "debit amount must be non-negative".into(),
+        ));
+    }
+    let balance = read_balance(conn, club_id).await?;
+    if balance < amount {
+        return Err(AppError::BadRequest(format!(
+            "insufficient balance: have {balance}, need {amount}"
+        )));
+    }
+    let now = Utc::now().to_rfc3339();
+    sqlx::query("UPDATE clubs SET coins = coins - ?, updated_at = ? WHERE id = ?")
+        .bind(amount)
+        .bind(&now)
+        .bind(club_id)
+        .execute(&mut *conn)
+        .await?;
+    Ok(balance - amount)
+}
+
+async fn credit(conn: &mut SqliteConnection, club_id: &str, amount: i64) -> AppResult<i64> {
+    if amount < 0 {
+        return Err(AppError::BadRequest(
+            "credit amount must be non-negative".into(),
+        ));
+    }
+    let balance = read_balance(conn, club_id).await?;
+    let now = Utc::now().to_rfc3339();
+    sqlx::query("UPDATE clubs SET coins = coins + ?, updated_at = ? WHERE id = ?")
+        .bind(amount)
+        .bind(&now)
+        .bind(club_id)
+        .execute(&mut *conn)
+        .await?;
+    Ok(balance + amount)
+}
+
+async fn grant_entitlement(
+    conn: &mut SqliteConnection,
+    club_id: &str,
+    definition_id: &str,
+) -> AppResult<String> {
+    // Ensure the club exists so we never orphan an entitlement.
+    read_balance(conn, club_id).await?;
+    let id = Uuid::new_v4().to_string();
+    let now = Utc::now().to_rfc3339();
+    sqlx::query(
+        "INSERT INTO packs (id, club_id, definition_id, opened, created_at) VALUES (?, ?, ?, 0, ?)",
+    )
+    .bind(&id)
+    .bind(club_id)
+    .bind(definition_id)
+    .bind(&now)
+    .execute(&mut *conn)
+    .await?;
+    Ok(id)
+}
+
+/// Consume an unopened entitlement exactly once, returning its definition ref.
+async fn consume_entitlement(
+    conn: &mut SqliteConnection,
+    club_id: &str,
+    entitlement_id: &str,
+) -> AppResult<String> {
+    let row = sqlx::query_as::<_, (String, i64)>(
+        "SELECT definition_id, opened FROM packs WHERE id = ? AND club_id = ?",
+    )
+    .bind(entitlement_id)
+    .bind(club_id)
+    .fetch_optional(&mut *conn)
+    .await?;
+    let (definition_id, opened) =
+        row.ok_or_else(|| AppError::NotFound(format!("entitlement not found: {entitlement_id}")))?;
+    if opened != 0 {
+        return Err(AppError::Conflict(format!(
+            "entitlement already consumed: {entitlement_id}"
+        )));
+    }
+    sqlx::query("UPDATE packs SET opened = 1 WHERE id = ? AND club_id = ?")
+        .bind(entitlement_id)
+        .bind(club_id)
+        .execute(&mut *conn)
+        .await?;
+    Ok(definition_id)
+}
+
+async fn add_item(
+    conn: &mut SqliteConnection,
+    club_id: &str,
+    item_id: &str,
+    card_id: &str,
+) -> AppResult<()> {
+    let now = Utc::now().to_rfc3339();
+    sqlx::query(
+        "INSERT INTO owned_cards (id, club_id, card_id, is_loan, loan_matches_remaining, acquired_at) \
+         VALUES (?, ?, ?, 0, NULL, ?)",
+    )
+    .bind(item_id)
+    .bind(club_id)
+    .bind(card_id)
+    .bind(&now)
+    .execute(&mut *conn)
+    .await?;
+    Ok(())
+}
+
+async fn remove_item(
+    conn: &mut SqliteConnection,
+    club_id: &str,
+    item_id: &str,
+) -> AppResult<String> {
+    let card_id = sqlx::query_scalar::<_, String>(
+        "SELECT card_id FROM owned_cards WHERE id = ? AND club_id = ?",
+    )
+    .bind(item_id)
+    .bind(club_id)
+    .fetch_optional(&mut *conn)
+    .await?
+    .ok_or_else(|| AppError::NotFound(format!("item not owned by club: {item_id}")))?;
+    sqlx::query("DELETE FROM owned_cards WHERE id = ? AND club_id = ?")
+        .bind(item_id)
+        .bind(club_id)
+        .execute(&mut *conn)
+        .await?;
+    Ok(card_id)
+}
+
+// ---- composed atomic operations ----------------------------------------------
+
+/// Read a club's current currency balance.
+pub async fn balance(pool: &Pool, club_id: &str) -> AppResult<i64> {
+    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}")))
+}
+
+/// One unopened entitlement a club owns.
+#[derive(Debug, Clone, Serialize)]
+pub struct Entitlement {
+    pub id: String,
+    pub definition_id: String,
+}
+
+/// List a club's unconsumed entitlements (opened = 0), oldest first.
+pub async fn list_unopened_entitlements(pool: &Pool, club_id: &str) -> AppResult<Vec<Entitlement>> {
+    let rows = sqlx::query_as::<_, (String, String)>(
+        "SELECT id, definition_id FROM packs WHERE club_id = ? AND opened = 0 ORDER BY created_at ASC, id ASC",
+    )
+    .bind(club_id)
+    .fetch_all(pool)
+    .await?;
+    Ok(rows
+        .into_iter()
+        .map(|(id, definition_id)| Entitlement { id, definition_id })
+        .collect())
+}
+
+/// Commit on `Ok`, roll back on `Err`. Paired with a `BEGIN IMMEDIATE` opened on
+/// the same connection, so the write lock is held for the whole op and a
+/// concurrent writer waits (honoring `busy_timeout`) instead of failing: a
+/// DEFERRED `pool.begin()` upgrades to a write only at the first write, where
+/// SQLite returns SQLITE_BUSY *immediately* (bypassing the busy handler to avoid
+/// deadlock) — the fresh-DB multi-connection write failure.
+async fn finish<T>(conn: &mut SqliteConnection, result: AppResult<T>) -> AppResult<T> {
+    match result {
+        Ok(v) => {
+            sqlx::query("COMMIT").execute(&mut *conn).await?;
+            Ok(v)
+        }
+        Err(e) => {
+            let _ = sqlx::query("ROLLBACK").execute(&mut *conn).await;
+            Err(e)
+        }
+    }
+}
+
+/// Debit `cost` and grant one entitlement, atomically. Fail-closed: if the club
+/// cannot afford `cost`, nothing is debited and no entitlement is created.
+pub async fn purchase_entitlement(
+    pool: &Pool,
+    club_id: &str,
+    cost: i64,
+    definition_id: &str,
+) -> AppResult<PurchaseReceipt> {
+    let mut conn = pool.acquire().await?;
+    sqlx::query("BEGIN IMMEDIATE").execute(&mut *conn).await?;
+    let result = async {
+        let balance = debit(&mut conn, club_id, cost).await?;
+        let entitlement_id = grant_entitlement(&mut conn, club_id, definition_id).await?;
+        Ok(PurchaseReceipt {
+            balance,
+            entitlement_id,
+        })
+    }
+    .await;
+    finish(&mut conn, result).await
+}
+
+/// Debit `cost` and mint one owned item, atomically. Fail-closed: if the club
+/// cannot afford `cost`, nothing is debited and no item is added. This is the
+/// "buy a specific item" primitive (a debit paired with an inventory add), for
+/// synthetic-seller markets where the purchased item is minted rather than
+/// transferred from another owner. Returns the post-debit balance.
+pub async fn purchase_item(
+    pool: &Pool,
+    club_id: &str,
+    cost: i64,
+    item_id: &str,
+    card_id: &str,
+) -> AppResult<i64> {
+    let mut conn = pool.acquire().await?;
+    sqlx::query("BEGIN IMMEDIATE").execute(&mut *conn).await?;
+    let result = async {
+        let balance = debit(&mut conn, club_id, cost).await?;
+        add_item(&mut conn, club_id, item_id, card_id).await?;
+        Ok(balance)
+    }
+    .await;
+    finish(&mut conn, result).await
+}
+
+/// Debit `cost` and mint several owned items, atomically. Fail-closed: if the
+/// club cannot afford `cost`, nothing is debited and no items are added; if any
+/// item insert fails the whole purchase rolls back. This is the "buy + open"
+/// primitive (Store packs that open on purchase): one debit paired with the
+/// minted pack contents. Returns the post-debit balance.
+pub async fn purchase_items(
+    pool: &Pool,
+    club_id: &str,
+    cost: i64,
+    items: &[GrantedItem],
+) -> AppResult<i64> {
+    let mut conn = pool.acquire().await?;
+    sqlx::query("BEGIN IMMEDIATE").execute(&mut *conn).await?;
+    let result = async {
+        let balance = debit(&mut conn, club_id, cost).await?;
+        for item in items {
+            add_item(&mut conn, club_id, &item.item_id, &item.card_id).await?;
+        }
+        Ok(balance)
+    }
+    .await;
+    finish(&mut conn, result).await
+}
+
+/// Consume an entitlement once and add its granted items, atomically. If any
+/// item insert fails (e.g. a colliding instance id) the whole redemption rolls
+/// back — the entitlement stays unconsumed and no items are persisted.
+pub async fn redeem_entitlement(
+    pool: &Pool,
+    club_id: &str,
+    entitlement_id: &str,
+    items: &[GrantedItem],
+) -> AppResult<String> {
+    let mut conn = pool.acquire().await?;
+    sqlx::query("BEGIN IMMEDIATE").execute(&mut *conn).await?;
+    let result = async {
+        let definition_id = consume_entitlement(&mut conn, club_id, entitlement_id).await?;
+        for item in items {
+            add_item(&mut conn, club_id, &item.item_id, &item.card_id).await?;
+        }
+        Ok(definition_id)
+    }
+    .await;
+    finish(&mut conn, result).await
+}
+
+/// Remove an owned item and credit `price`, atomically. Fail-closed: if the item
+/// is not owned by the club nothing is credited.
+pub async fn sell_item(pool: &Pool, club_id: &str, item_id: &str, price: i64) -> AppResult<i64> {
+    let mut conn = pool.acquire().await?;
+    sqlx::query("BEGIN IMMEDIATE").execute(&mut *conn).await?;
+    let result = async {
+        remove_item(&mut conn, club_id, item_id).await?;
+        credit(&mut conn, club_id, price).await
+    }
+    .await;
+    finish(&mut conn, result).await
+}
+
+/// Credit a reward to a club's balance atomically.
+pub async fn grant_reward(pool: &Pool, club_id: &str, amount: i64) -> AppResult<i64> {
+    let mut conn = pool.acquire().await?;
+    sqlx::query("BEGIN IMMEDIATE").execute(&mut *conn).await?;
+    let result = async { credit(&mut conn, club_id, amount).await }.await;
+    finish(&mut conn, result).await
+}
+
+#[cfg(test)]
+mod tests {
+    use super::*;
+
+    const TS: &str = "2026-01-01T00:00:00Z";
+
+    /// In-memory pool with the real schema and one club (1000 coins) owning one
+    /// item (`item-x`). Mirrors the `squad` service test harness.
+    async fn fixture() -> Pool {
+        let pool = sqlx::sqlite::SqlitePoolOptions::new()
+            .connect("sqlite::memory:")
+            .await
+            .expect("in-memory sqlite");
+        sqlx::migrate!("./migrations")
+            .run(&pool)
+            .await
+            .expect("migrations");
+        sqlx::query(
+            "INSERT INTO profiles (id, username, created_at, updated_at) VALUES (?, ?, ?, ?)",
+        )
+        .bind("prof")
+        .bind("prof")
+        .bind(TS)
+        .bind(TS)
+        .execute(&pool)
+        .await
+        .expect("profile");
+        sqlx::query("INSERT INTO clubs (id, profile_id, name, coins, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)")
+            .bind("club")
+            .bind("prof")
+            .bind("club")
+            .bind(1000i64)
+            .bind(TS)
+            .bind(TS)
+            .execute(&pool)
+            .await
+            .expect("club");
+        sqlx::query("INSERT INTO owned_cards (id, club_id, card_id, is_loan, acquired_at) VALUES (?, ?, ?, 0, ?)")
+            .bind("item-x")
+            .bind("club")
+            .bind("def-x")
+            .bind(TS)
+            .execute(&pool)
+            .await
+            .expect("owned card");
+        pool
+    }
+
+    async fn pack_count(pool: &Pool) -> i64 {
+        sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM packs")
+            .fetch_one(pool)
+            .await
+            .unwrap()
+    }
+
+    async fn item_count(pool: &Pool, item_id: &str) -> i64 {
+        sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM owned_cards WHERE id = ?")
+            .bind(item_id)
+            .fetch_one(pool)
+            .await
+            .unwrap()
+    }
+
+    #[tokio::test]
+    async fn balance_reads_seeded_value() {
+        let pool = fixture().await;
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1000);
+        assert!(matches!(
+            balance(&pool, "ghost").await,
+            Err(AppError::NotFound(_))
+        ));
+    }
+
+    #[tokio::test]
+    async fn purchase_debits_and_grants() {
+        let pool = fixture().await;
+        let receipt = purchase_entitlement(&pool, "club", 300, "def-pack")
+            .await
+            .unwrap();
+        assert_eq!(receipt.balance, 700);
+        assert_eq!(balance(&pool, "club").await.unwrap(), 700);
+        assert_eq!(pack_count(&pool).await, 1);
+    }
+
+    #[tokio::test]
+    async fn purchase_insufficient_funds_rolls_back() {
+        let pool = fixture().await;
+        let err = purchase_entitlement(&pool, "club", 5000, "def-pack")
+            .await
+            .unwrap_err();
+        assert!(matches!(err, AppError::BadRequest(_)));
+        // Nothing debited, no entitlement created.
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1000);
+        assert_eq!(pack_count(&pool).await, 0);
+    }
+
+    #[tokio::test]
+    async fn negative_amount_is_rejected() {
+        let pool = fixture().await;
+        assert!(matches!(
+            purchase_entitlement(&pool, "club", -50, "def").await,
+            Err(AppError::BadRequest(_))
+        ));
+        // sell with negative price hits the credit guard and rolls back the removal.
+        assert!(matches!(
+            sell_item(&pool, "club", "item-x", -1).await,
+            Err(AppError::BadRequest(_))
+        ));
+        assert_eq!(item_count(&pool, "item-x").await, 1);
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1000);
+    }
+
+    #[tokio::test]
+    async fn redeem_consumes_once_and_adds_items() {
+        let pool = fixture().await;
+        let ent = purchase_entitlement(&pool, "club", 100, "def-pack")
+            .await
+            .unwrap()
+            .entitlement_id;
+        let items = vec![GrantedItem {
+            item_id: "item-a".into(),
+            card_id: "def-a".into(),
+        }];
+        let def = redeem_entitlement(&pool, "club", &ent, &items)
+            .await
+            .unwrap();
+        assert_eq!(def, "def-pack");
+        assert_eq!(item_count(&pool, "item-a").await, 1);
+        // Second redeem of the same entitlement is rejected; inventory unchanged.
+        let err = redeem_entitlement(&pool, "club", &ent, &items)
+            .await
+            .unwrap_err();
+        assert!(matches!(err, AppError::Conflict(_)));
+        assert_eq!(item_count(&pool, "item-a").await, 1);
+    }
+
+    #[tokio::test]
+    async fn redeem_missing_entitlement_is_not_found() {
+        let pool = fixture().await;
+        assert!(matches!(
+            redeem_entitlement(&pool, "club", "no-such", &[]).await,
+            Err(AppError::NotFound(_))
+        ));
+    }
+
+    #[tokio::test]
+    async fn redeem_partial_failure_rolls_back() {
+        let pool = fixture().await;
+        let ent = purchase_entitlement(&pool, "club", 100, "def-pack")
+            .await
+            .unwrap()
+            .entitlement_id;
+        // Second item collides with the first instance id -> PK violation mid-loop.
+        let items = vec![
+            GrantedItem {
+                item_id: "dup".into(),
+                card_id: "def-a".into(),
+            },
+            GrantedItem {
+                item_id: "dup".into(),
+                card_id: "def-b".into(),
+            },
+        ];
+        let err = redeem_entitlement(&pool, "club", &ent, &items)
+            .await
+            .unwrap_err();
+        assert!(matches!(err, AppError::Database(_)));
+        // Whole redemption rolled back: entitlement still unconsumed, no items added.
+        assert_eq!(item_count(&pool, "dup").await, 0);
+        let def = redeem_entitlement(
+            &pool,
+            "club",
+            &ent,
+            &[GrantedItem {
+                item_id: "dup".into(),
+                card_id: "def-a".into(),
+            }],
+        )
+        .await
+        .unwrap();
+        assert_eq!(def, "def-pack");
+        assert_eq!(item_count(&pool, "dup").await, 1);
+    }
+
+    #[tokio::test]
+    async fn sell_removes_and_credits() {
+        let pool = fixture().await;
+        let new_balance = sell_item(&pool, "club", "item-x", 250).await.unwrap();
+        assert_eq!(new_balance, 1250);
+        assert_eq!(item_count(&pool, "item-x").await, 0);
+        // Selling it again fails; balance is unchanged.
+        assert!(matches!(
+            sell_item(&pool, "club", "item-x", 250).await,
+            Err(AppError::NotFound(_))
+        ));
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1250);
+    }
+
+    #[tokio::test]
+    async fn grant_reward_credits() {
+        let pool = fixture().await;
+        assert_eq!(grant_reward(&pool, "club", 500).await.unwrap(), 1500);
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1500);
+    }
+
+    #[tokio::test]
+    async fn purchase_item_debits_and_mints() {
+        let pool = fixture().await;
+        let bal = purchase_item(&pool, "club", 400, "item-new", "def-new")
+            .await
+            .unwrap();
+        assert_eq!(bal, 600);
+        assert_eq!(balance(&pool, "club").await.unwrap(), 600);
+        assert_eq!(item_count(&pool, "item-new").await, 1);
+    }
+
+    #[tokio::test]
+    async fn purchase_item_insufficient_funds_rolls_back() {
+        let pool = fixture().await;
+        let err = purchase_item(&pool, "club", 9000, "item-new", "def-new")
+            .await
+            .unwrap_err();
+        assert!(matches!(err, AppError::BadRequest(_)));
+        // Nothing debited, no item minted.
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1000);
+        assert_eq!(item_count(&pool, "item-new").await, 0);
+    }
+
+    #[tokio::test]
+    async fn purchase_items_debits_and_mints_all() {
+        let pool = fixture().await;
+        let items = vec![
+            GrantedItem {
+                item_id: "p-1".into(),
+                card_id: "d-1".into(),
+            },
+            GrantedItem {
+                item_id: "p-2".into(),
+                card_id: "d-2".into(),
+            },
+        ];
+        let bal = purchase_items(&pool, "club", 700, &items).await.unwrap();
+        assert_eq!(bal, 300);
+        assert_eq!(item_count(&pool, "p-1").await, 1);
+        assert_eq!(item_count(&pool, "p-2").await, 1);
+    }
+
+    #[tokio::test]
+    async fn purchase_items_insufficient_funds_rolls_back() {
+        let pool = fixture().await;
+        let items = vec![GrantedItem {
+            item_id: "p-1".into(),
+            card_id: "d-1".into(),
+        }];
+        let err = purchase_items(&pool, "club", 9000, &items)
+            .await
+            .unwrap_err();
+        assert!(matches!(err, AppError::BadRequest(_)));
+        assert_eq!(balance(&pool, "club").await.unwrap(), 1000);
+        assert_eq!(item_count(&pool, "p-1").await, 0);
+    }
+}

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/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/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:#}");
+}