fix: rendering of avatar thumbnails

crates/chattyness-admin-ui/src/auth/admin_conn.rs

@@ -0,0 +1,315 @@
+//! Admin connection extractor with RLS context.
+//!
+//! Provides database connections that set RLS context when a user is authenticated.
+//! Used by admin API handlers to ensure write operations respect RLS policies.
+//!
+//! - In owner app: No session, uses plain connection (RLS bypassed by chattyness_owner role)
+//! - In user app: Session exists, sets current_user_id() for RLS enforcement
+
+use axum::{
+    Json,
+    extract::FromRequestParts,
+    http::{Request, StatusCode, request::Parts},
+    response::{IntoResponse, Response},
+};
+use sqlx::{PgPool, Postgres, pool::PoolConnection, postgres::PgConnection};
+use std::{
+    future::Future,
+    ops::{Deref, DerefMut},
+    pin::Pin,
+    sync::Arc,
+    task::{Context, Poll},
+};
+use tokio::sync::{Mutex, MutexGuard};
+use tower::{Layer, Service};
+use tower_sessions::Session;
+use uuid::Uuid;
+
+use super::{ADMIN_SESSION_STAFF_ID_KEY, SESSION_USER_ID_KEY};
+use chattyness_error::ErrorResponse;
+
+// =============================================================================
+// Admin Connection Wrapper
+// =============================================================================
+
+struct AdminConnectionInner {
+    conn: Option<PoolConnection<Postgres>>,
+    pool: PgPool,
+    had_user_context: bool,
+}
+
+impl Drop for AdminConnectionInner {
+    fn drop(&mut self) {
+        if let Some(mut conn) = self.conn.take() {
+            // Only clear context if we set it
+            if self.had_user_context {
+                let pool = self.pool.clone();
+                tokio::spawn(async move {
+                    let _ = sqlx::query("SELECT public.set_current_user_id(NULL)")
+                        .execute(&mut *conn)
+                        .await;
+                    drop(conn);
+                    drop(pool);
+                });
+            }
+        }
+    }
+}
+
+/// A database connection with optional RLS user context set.
+#[derive(Clone)]
+pub struct AdminConnection {
+    inner: Arc<Mutex<AdminConnectionInner>>,
+}
+
+impl AdminConnection {
+    fn new(conn: PoolConnection<Postgres>, pool: PgPool, had_user_context: bool) -> Self {
+        Self {
+            inner: Arc::new(Mutex::new(AdminConnectionInner {
+                conn: Some(conn),
+                pool,
+                had_user_context,
+            })),
+        }
+    }
+
+    /// Acquire exclusive access to the admin connection.
+    pub async fn acquire(&self) -> AdminConnGuard<'_> {
+        AdminConnGuard {
+            guard: self.inner.lock().await,
+        }
+    }
+}
+
+/// A guard providing mutable access to the admin database connection.
+pub struct AdminConnGuard<'a> {
+    guard: MutexGuard<'a, AdminConnectionInner>,
+}
+
+impl Deref for AdminConnGuard<'_> {
+    type Target = PgConnection;
+
+    fn deref(&self) -> &Self::Target {
+        self.guard
+            .conn
+            .as_ref()
+            .expect("AdminConnection already consumed")
+            .deref()
+    }
+}
+
+impl DerefMut for AdminConnGuard<'_> {
+    fn deref_mut(&mut self) -> &mut Self::Target {
+        self.guard
+            .conn
+            .as_mut()
+            .expect("AdminConnection already consumed")
+            .deref_mut()
+    }
+}
+
+// =============================================================================
+// Admin Connection Extractor
+// =============================================================================
+
+/// Extractor for an admin database connection with RLS context.
+///
+/// Usage in handlers:
+/// ```ignore
+/// pub async fn create_scene(
+///     admin_conn: AdminConn,
+///     Json(req): Json<CreateSceneRequest>,
+/// ) -> Result<Json<Scene>, AppError> {
+///     let mut conn = admin_conn.0;
+///     let mut guard = conn.acquire().await;
+///     scenes::create_scene(&mut *guard, ...).await
+/// }
+/// ```
+pub struct AdminConn(pub AdminConnection);
+
+impl<S> FromRequestParts<S> for AdminConn
+where
+    S: Send + Sync,
+{
+    type Rejection = AdminConnError;
+
+    async fn from_request_parts(parts: &mut Parts, _state: &S) -> Result<Self, Self::Rejection> {
+        parts
+            .extensions
+            .remove::<AdminConnection>()
+            .map(AdminConn)
+            .ok_or(AdminConnError::NoConnection)
+    }
+}
+
+impl Deref for AdminConn {
+    type Target = AdminConnection;
+
+    fn deref(&self) -> &Self::Target {
+        &self.0
+    }
+}
+
+impl DerefMut for AdminConn {
+    fn deref_mut(&mut self) -> &mut Self::Target {
+        &mut self.0
+    }
+}
+
+/// Errors related to admin connection handling.
+#[derive(Debug)]
+pub enum AdminConnError {
+    NoConnection,
+    DatabaseError(String),
+}
+
+impl IntoResponse for AdminConnError {
+    fn into_response(self) -> Response {
+        let (status, message) = match self {
+            AdminConnError::NoConnection => (
+                StatusCode::INTERNAL_SERVER_ERROR,
+                "Admin connection not available - is AdminConnLayer middleware configured?",
+            ),
+            AdminConnError::DatabaseError(msg) => (
+                StatusCode::INTERNAL_SERVER_ERROR,
+                msg.leak() as &'static str,
+            ),
+        };
+
+        let body = ErrorResponse {
+            error: message.to_string(),
+            code: Some("ADMIN_CONN_ERROR".to_string()),
+        };
+
+        (status, Json(body)).into_response()
+    }
+}
+
+// =============================================================================
+// Admin Connection Middleware Layer
+// =============================================================================
+
+/// Layer that provides admin database connections with RLS context per request.
+///
+/// This middleware:
+/// 1. Checks for user_id in session (staff_id or user_id)
+/// 2. Acquires a connection from the pool
+/// 3. If user_id exists, calls `set_current_user_id($1)` for RLS
+/// 4. Inserts the connection into request extensions
+///
+/// Usage:
+/// ```ignore
+/// let app = Router::new()
+///     .nest("/api/admin", admin_api_router())
+///     .layer(AdminConnLayer::new(pool.clone()))
+///     .layer(session_layer);
+/// ```
+#[derive(Clone)]
+pub struct AdminConnLayer {
+    pool: PgPool,
+}
+
+impl AdminConnLayer {
+    pub fn new(pool: PgPool) -> Self {
+        Self { pool }
+    }
+}
+
+impl<S> Layer<S> for AdminConnLayer {
+    type Service = AdminConnMiddleware<S>;
+
+    fn layer(&self, inner: S) -> Self::Service {
+        AdminConnMiddleware {
+            inner,
+            pool: self.pool.clone(),
+        }
+    }
+}
+
+/// Middleware that sets up admin connections with RLS context per request.
+#[derive(Clone)]
+pub struct AdminConnMiddleware<S> {
+    inner: S,
+    pool: PgPool,
+}
+
+impl<S, B> Service<Request<B>> for AdminConnMiddleware<S>
+where
+    S: Service<Request<B>, Response = Response> + Clone + Send + 'static,
+    S::Future: Send,
+    B: Send + 'static,
+{
+    type Response = Response;
+    type Error = S::Error;
+    type Future = Pin<Box<dyn Future<Output = Result<Self::Response, Self::Error>> + Send>>;
+
+    fn poll_ready(&mut self, cx: &mut Context<'_>) -> Poll<Result<(), Self::Error>> {
+        self.inner.poll_ready(cx)
+    }
+
+    fn call(&mut self, mut request: Request<B>) -> Self::Future {
+        let pool = self.pool.clone();
+        let mut inner = self.inner.clone();
+
+        let session = request.extensions().get::<Session>().cloned();
+
+        Box::pin(async move {
+            let user_id = get_admin_user_id(session).await;
+
+            match acquire_admin_connection(&pool, user_id).await {
+                Ok(admin_conn) => {
+                    request.extensions_mut().insert(admin_conn);
+                    inner.call(request).await
+                }
+                Err(e) => {
+                    tracing::error!("Failed to acquire admin connection: {}", e);
+                    Ok(AdminConnError::DatabaseError(e.to_string()).into_response())
+                }
+            }
+        })
+    }
+}
+
+/// Get user ID from session for RLS context.
+///
+/// Checks in order:
+/// 1. staff_id - server staff member
+/// 2. user_id - realm admin user
+///
+/// Returns None if no session or no user ID (owner app context).
+async fn get_admin_user_id(session: Option<Session>) -> Option<Uuid> {
+    let Some(session) = session else {
+        return None;
+    };
+
+    // Try staff_id first (server staff)
+    if let Ok(Some(staff_id)) = session.get::<Uuid>(ADMIN_SESSION_STAFF_ID_KEY).await {
+        return Some(staff_id);
+    }
+
+    // Try user_id (realm admin)
+    if let Ok(Some(user_id)) = session.get::<Uuid>(SESSION_USER_ID_KEY).await {
+        return Some(user_id);
+    }
+
+    None
+}
+
+/// Acquire a database connection and set RLS context if user_id is provided.
+async fn acquire_admin_connection(
+    pool: &PgPool,
+    user_id: Option<Uuid>,
+) -> Result<AdminConnection, sqlx::Error> {
+    let mut conn = pool.acquire().await?;
+
+    let had_user_context = user_id.is_some();
+
+    if let Some(user_id) = user_id {
+        sqlx::query("SELECT public.set_current_user_id($1)")
+            .bind(user_id)
+            .execute(&mut *conn)
+            .await?;
+    }
+
+    Ok(AdminConnection::new(conn, pool.clone(), had_user_context))
+}

crates/chattyness-db/src/models.rs

@@ -1988,6 +1988,45 @@ pub struct ServerAvatarSummary {
     pub created_at: DateTime<Utc>,
 }
 
+/// Server avatar with resolved asset paths for rendering.
+///
+/// Used in the avatar store to display avatar thumbnails without needing
+/// additional server queries to resolve prop UUIDs to asset paths.
+#[derive(Debug, Clone, Serialize, Deserialize, Default)]
+pub struct ServerAvatarWithPaths {
+    pub id: Uuid,
+    pub slug: String,
+    pub name: String,
+    pub description: Option<String>,
+    /// Asset paths for skin layer positions 0-8 (None if slot empty)
+    pub skin_layer: [Option<String>; 9],
+    /// Asset paths for clothes layer positions 0-8
+    pub clothes_layer: [Option<String>; 9],
+    /// Asset paths for accessories layer positions 0-8
+    pub accessories_layer: [Option<String>; 9],
+    /// Asset paths for happy emotion layer (e1 - more inviting for store thumbnails)
+    pub emotion_layer: [Option<String>; 9],
+}
+
+/// Realm avatar with resolved asset paths for rendering.
+///
+/// Used in the avatar store to display avatar thumbnails without needing
+/// additional server queries to resolve prop UUIDs to asset paths.
+#[derive(Debug, Clone, Serialize, Deserialize, Default)]
+pub struct RealmAvatarWithPaths {
+    pub id: Uuid,
+    pub name: String,
+    pub description: Option<String>,
+    /// Asset paths for skin layer positions 0-8 (None if slot empty)
+    pub skin_layer: [Option<String>; 9],
+    /// Asset paths for clothes layer positions 0-8
+    pub clothes_layer: [Option<String>; 9],
+    /// Asset paths for accessories layer positions 0-8
+    pub accessories_layer: [Option<String>; 9],
+    /// Asset paths for happy emotion layer (e1 - more inviting for store thumbnails)
+    pub emotion_layer: [Option<String>; 9],
+}
+
 /// Request to create a realm avatar.
 #[derive(Debug, Clone, Serialize, Deserialize, Default)]
 pub struct CreateRealmAvatarRequest {

crates/chattyness-db/src/queries/realm_avatars.rs

@@ -72,6 +72,192 @@ pub async fn list_public_realm_avatars<'e>(
     Ok(avatars)
 }
 
+use crate::models::RealmAvatarWithPaths;
+
+/// Row type for realm avatar with paths query.
+#[derive(Debug, sqlx::FromRow)]
+struct RealmAvatarWithPathsRow {
+    id: Uuid,
+    name: String,
+    description: Option<String>,
+    // Skin layer paths
+    skin_0: Option<String>,
+    skin_1: Option<String>,
+    skin_2: Option<String>,
+    skin_3: Option<String>,
+    skin_4: Option<String>,
+    skin_5: Option<String>,
+    skin_6: Option<String>,
+    skin_7: Option<String>,
+    skin_8: Option<String>,
+    // Clothes layer paths
+    clothes_0: Option<String>,
+    clothes_1: Option<String>,
+    clothes_2: Option<String>,
+    clothes_3: Option<String>,
+    clothes_4: Option<String>,
+    clothes_5: Option<String>,
+    clothes_6: Option<String>,
+    clothes_7: Option<String>,
+    clothes_8: Option<String>,
+    // Accessories layer paths
+    accessories_0: Option<String>,
+    accessories_1: Option<String>,
+    accessories_2: Option<String>,
+    accessories_3: Option<String>,
+    accessories_4: Option<String>,
+    accessories_5: Option<String>,
+    accessories_6: Option<String>,
+    accessories_7: Option<String>,
+    accessories_8: Option<String>,
+    // Happy emotion layer paths (e1 - more inviting for store display)
+    emotion_0: Option<String>,
+    emotion_1: Option<String>,
+    emotion_2: Option<String>,
+    emotion_3: Option<String>,
+    emotion_4: Option<String>,
+    emotion_5: Option<String>,
+    emotion_6: Option<String>,
+    emotion_7: Option<String>,
+    emotion_8: Option<String>,
+}
+
+impl From<RealmAvatarWithPathsRow> for RealmAvatarWithPaths {
+    fn from(row: RealmAvatarWithPathsRow) -> Self {
+        Self {
+            id: row.id,
+            name: row.name,
+            description: row.description,
+            skin_layer: [
+                row.skin_0, row.skin_1, row.skin_2,
+                row.skin_3, row.skin_4, row.skin_5,
+                row.skin_6, row.skin_7, row.skin_8,
+            ],
+            clothes_layer: [
+                row.clothes_0, row.clothes_1, row.clothes_2,
+                row.clothes_3, row.clothes_4, row.clothes_5,
+                row.clothes_6, row.clothes_7, row.clothes_8,
+            ],
+            accessories_layer: [
+                row.accessories_0, row.accessories_1, row.accessories_2,
+                row.accessories_3, row.accessories_4, row.accessories_5,
+                row.accessories_6, row.accessories_7, row.accessories_8,
+            ],
+            emotion_layer: [
+                row.emotion_0, row.emotion_1, row.emotion_2,
+                row.emotion_3, row.emotion_4, row.emotion_5,
+                row.emotion_6, row.emotion_7, row.emotion_8,
+            ],
+        }
+    }
+}
+
+/// List all active public realm avatars with resolved asset paths.
+///
+/// Joins with the props table to resolve prop UUIDs to asset paths,
+/// suitable for client-side rendering without additional lookups.
+pub async fn list_public_realm_avatars_with_paths<'e>(
+    executor: impl PgExecutor<'e>,
+    realm_id: Uuid,
+) -> Result<Vec<RealmAvatarWithPaths>, AppError> {
+    let rows = sqlx::query_as::<_, RealmAvatarWithPathsRow>(
+        r#"
+        SELECT
+            a.id,
+            a.name,
+            a.description,
+            -- Skin layer
+            p_skin_0.asset_path AS skin_0,
+            p_skin_1.asset_path AS skin_1,
+            p_skin_2.asset_path AS skin_2,
+            p_skin_3.asset_path AS skin_3,
+            p_skin_4.asset_path AS skin_4,
+            p_skin_5.asset_path AS skin_5,
+            p_skin_6.asset_path AS skin_6,
+            p_skin_7.asset_path AS skin_7,
+            p_skin_8.asset_path AS skin_8,
+            -- Clothes layer
+            p_clothes_0.asset_path AS clothes_0,
+            p_clothes_1.asset_path AS clothes_1,
+            p_clothes_2.asset_path AS clothes_2,
+            p_clothes_3.asset_path AS clothes_3,
+            p_clothes_4.asset_path AS clothes_4,
+            p_clothes_5.asset_path AS clothes_5,
+            p_clothes_6.asset_path AS clothes_6,
+            p_clothes_7.asset_path AS clothes_7,
+            p_clothes_8.asset_path AS clothes_8,
+            -- Accessories layer
+            p_acc_0.asset_path AS accessories_0,
+            p_acc_1.asset_path AS accessories_1,
+            p_acc_2.asset_path AS accessories_2,
+            p_acc_3.asset_path AS accessories_3,
+            p_acc_4.asset_path AS accessories_4,
+            p_acc_5.asset_path AS accessories_5,
+            p_acc_6.asset_path AS accessories_6,
+            p_acc_7.asset_path AS accessories_7,
+            p_acc_8.asset_path AS accessories_8,
+            -- Happy emotion layer (e1 - more inviting for store display)
+            p_emo_0.asset_path AS emotion_0,
+            p_emo_1.asset_path AS emotion_1,
+            p_emo_2.asset_path AS emotion_2,
+            p_emo_3.asset_path AS emotion_3,
+            p_emo_4.asset_path AS emotion_4,
+            p_emo_5.asset_path AS emotion_5,
+            p_emo_6.asset_path AS emotion_6,
+            p_emo_7.asset_path AS emotion_7,
+            p_emo_8.asset_path AS emotion_8
+        FROM realm.avatars a
+        -- Skin layer joins
+        LEFT JOIN realm.props p_skin_0 ON a.l_skin_0 = p_skin_0.id
+        LEFT JOIN realm.props p_skin_1 ON a.l_skin_1 = p_skin_1.id
+        LEFT JOIN realm.props p_skin_2 ON a.l_skin_2 = p_skin_2.id
+        LEFT JOIN realm.props p_skin_3 ON a.l_skin_3 = p_skin_3.id
+        LEFT JOIN realm.props p_skin_4 ON a.l_skin_4 = p_skin_4.id
+        LEFT JOIN realm.props p_skin_5 ON a.l_skin_5 = p_skin_5.id
+        LEFT JOIN realm.props p_skin_6 ON a.l_skin_6 = p_skin_6.id
+        LEFT JOIN realm.props p_skin_7 ON a.l_skin_7 = p_skin_7.id
+        LEFT JOIN realm.props p_skin_8 ON a.l_skin_8 = p_skin_8.id
+        -- Clothes layer joins
+        LEFT JOIN realm.props p_clothes_0 ON a.l_clothes_0 = p_clothes_0.id
+        LEFT JOIN realm.props p_clothes_1 ON a.l_clothes_1 = p_clothes_1.id
+        LEFT JOIN realm.props p_clothes_2 ON a.l_clothes_2 = p_clothes_2.id
+        LEFT JOIN realm.props p_clothes_3 ON a.l_clothes_3 = p_clothes_3.id
+        LEFT JOIN realm.props p_clothes_4 ON a.l_clothes_4 = p_clothes_4.id
+        LEFT JOIN realm.props p_clothes_5 ON a.l_clothes_5 = p_clothes_5.id
+        LEFT JOIN realm.props p_clothes_6 ON a.l_clothes_6 = p_clothes_6.id
+        LEFT JOIN realm.props p_clothes_7 ON a.l_clothes_7 = p_clothes_7.id
+        LEFT JOIN realm.props p_clothes_8 ON a.l_clothes_8 = p_clothes_8.id
+        -- Accessories layer joins
+        LEFT JOIN realm.props p_acc_0 ON a.l_accessories_0 = p_acc_0.id
+        LEFT JOIN realm.props p_acc_1 ON a.l_accessories_1 = p_acc_1.id
+        LEFT JOIN realm.props p_acc_2 ON a.l_accessories_2 = p_acc_2.id
+        LEFT JOIN realm.props p_acc_3 ON a.l_accessories_3 = p_acc_3.id
+        LEFT JOIN realm.props p_acc_4 ON a.l_accessories_4 = p_acc_4.id
+        LEFT JOIN realm.props p_acc_5 ON a.l_accessories_5 = p_acc_5.id
+        LEFT JOIN realm.props p_acc_6 ON a.l_accessories_6 = p_acc_6.id
+        LEFT JOIN realm.props p_acc_7 ON a.l_accessories_7 = p_acc_7.id
+        LEFT JOIN realm.props p_acc_8 ON a.l_accessories_8 = p_acc_8.id
+        -- Happy emotion layer joins (e1 - more inviting for store display)
+        LEFT JOIN realm.props p_emo_0 ON a.e_happy_0 = p_emo_0.id
+        LEFT JOIN realm.props p_emo_1 ON a.e_happy_1 = p_emo_1.id
+        LEFT JOIN realm.props p_emo_2 ON a.e_happy_2 = p_emo_2.id
+        LEFT JOIN realm.props p_emo_3 ON a.e_happy_3 = p_emo_3.id
+        LEFT JOIN realm.props p_emo_4 ON a.e_happy_4 = p_emo_4.id
+        LEFT JOIN realm.props p_emo_5 ON a.e_happy_5 = p_emo_5.id
+        LEFT JOIN realm.props p_emo_6 ON a.e_happy_6 = p_emo_6.id
+        LEFT JOIN realm.props p_emo_7 ON a.e_happy_7 = p_emo_7.id
+        LEFT JOIN realm.props p_emo_8 ON a.e_happy_8 = p_emo_8.id
+        WHERE a.realm_id = $1 AND a.is_active = true AND a.is_public = true
+        ORDER BY a.name ASC
+        "#,
+    )
+    .bind(realm_id)
+    .fetch_all(executor)
+    .await?;
+
+    Ok(rows.into_iter().map(RealmAvatarWithPaths::from).collect())
+}
+
 /// Row type for prop asset lookup.
 #[derive(Debug, sqlx::FromRow)]
 struct PropAssetRow {

crates/chattyness-db/src/queries/server_avatars.rs

@@ -68,6 +68,193 @@ pub async fn list_public_server_avatars<'e>(
     Ok(avatars)
 }
 
+use crate::models::ServerAvatarWithPaths;
+
+/// Row type for server avatar with paths query.
+#[derive(Debug, sqlx::FromRow)]
+struct ServerAvatarWithPathsRow {
+    id: Uuid,
+    slug: String,
+    name: String,
+    description: Option<String>,
+    // Skin layer paths
+    skin_0: Option<String>,
+    skin_1: Option<String>,
+    skin_2: Option<String>,
+    skin_3: Option<String>,
+    skin_4: Option<String>,
+    skin_5: Option<String>,
+    skin_6: Option<String>,
+    skin_7: Option<String>,
+    skin_8: Option<String>,
+    // Clothes layer paths
+    clothes_0: Option<String>,
+    clothes_1: Option<String>,
+    clothes_2: Option<String>,
+    clothes_3: Option<String>,
+    clothes_4: Option<String>,
+    clothes_5: Option<String>,
+    clothes_6: Option<String>,
+    clothes_7: Option<String>,
+    clothes_8: Option<String>,
+    // Accessories layer paths
+    accessories_0: Option<String>,
+    accessories_1: Option<String>,
+    accessories_2: Option<String>,
+    accessories_3: Option<String>,
+    accessories_4: Option<String>,
+    accessories_5: Option<String>,
+    accessories_6: Option<String>,
+    accessories_7: Option<String>,
+    accessories_8: Option<String>,
+    // Happy emotion layer paths (e1 - more inviting for store display)
+    emotion_0: Option<String>,
+    emotion_1: Option<String>,
+    emotion_2: Option<String>,
+    emotion_3: Option<String>,
+    emotion_4: Option<String>,
+    emotion_5: Option<String>,
+    emotion_6: Option<String>,
+    emotion_7: Option<String>,
+    emotion_8: Option<String>,
+}
+
+impl From<ServerAvatarWithPathsRow> for ServerAvatarWithPaths {
+    fn from(row: ServerAvatarWithPathsRow) -> Self {
+        Self {
+            id: row.id,
+            slug: row.slug,
+            name: row.name,
+            description: row.description,
+            skin_layer: [
+                row.skin_0, row.skin_1, row.skin_2,
+                row.skin_3, row.skin_4, row.skin_5,
+                row.skin_6, row.skin_7, row.skin_8,
+            ],
+            clothes_layer: [
+                row.clothes_0, row.clothes_1, row.clothes_2,
+                row.clothes_3, row.clothes_4, row.clothes_5,
+                row.clothes_6, row.clothes_7, row.clothes_8,
+            ],
+            accessories_layer: [
+                row.accessories_0, row.accessories_1, row.accessories_2,
+                row.accessories_3, row.accessories_4, row.accessories_5,
+                row.accessories_6, row.accessories_7, row.accessories_8,
+            ],
+            emotion_layer: [
+                row.emotion_0, row.emotion_1, row.emotion_2,
+                row.emotion_3, row.emotion_4, row.emotion_5,
+                row.emotion_6, row.emotion_7, row.emotion_8,
+            ],
+        }
+    }
+}
+
+/// List all active public server avatars with resolved asset paths.
+///
+/// Joins with the props table to resolve prop UUIDs to asset paths,
+/// suitable for client-side rendering without additional lookups.
+pub async fn list_public_server_avatars_with_paths<'e>(
+    executor: impl PgExecutor<'e>,
+) -> Result<Vec<ServerAvatarWithPaths>, AppError> {
+    let rows = sqlx::query_as::<_, ServerAvatarWithPathsRow>(
+        r#"
+        SELECT
+            a.id,
+            a.slug,
+            a.name,
+            a.description,
+            -- Skin layer
+            p_skin_0.asset_path AS skin_0,
+            p_skin_1.asset_path AS skin_1,
+            p_skin_2.asset_path AS skin_2,
+            p_skin_3.asset_path AS skin_3,
+            p_skin_4.asset_path AS skin_4,
+            p_skin_5.asset_path AS skin_5,
+            p_skin_6.asset_path AS skin_6,
+            p_skin_7.asset_path AS skin_7,
+            p_skin_8.asset_path AS skin_8,
+            -- Clothes layer
+            p_clothes_0.asset_path AS clothes_0,
+            p_clothes_1.asset_path AS clothes_1,
+            p_clothes_2.asset_path AS clothes_2,
+            p_clothes_3.asset_path AS clothes_3,
+            p_clothes_4.asset_path AS clothes_4,
+            p_clothes_5.asset_path AS clothes_5,
+            p_clothes_6.asset_path AS clothes_6,
+            p_clothes_7.asset_path AS clothes_7,
+            p_clothes_8.asset_path AS clothes_8,
+            -- Accessories layer
+            p_acc_0.asset_path AS accessories_0,
+            p_acc_1.asset_path AS accessories_1,
+            p_acc_2.asset_path AS accessories_2,
+            p_acc_3.asset_path AS accessories_3,
+            p_acc_4.asset_path AS accessories_4,
+            p_acc_5.asset_path AS accessories_5,
+            p_acc_6.asset_path AS accessories_6,
+            p_acc_7.asset_path AS accessories_7,
+            p_acc_8.asset_path AS accessories_8,
+            -- Happy emotion layer (e1 - more inviting for store display)
+            p_emo_0.asset_path AS emotion_0,
+            p_emo_1.asset_path AS emotion_1,
+            p_emo_2.asset_path AS emotion_2,
+            p_emo_3.asset_path AS emotion_3,
+            p_emo_4.asset_path AS emotion_4,
+            p_emo_5.asset_path AS emotion_5,
+            p_emo_6.asset_path AS emotion_6,
+            p_emo_7.asset_path AS emotion_7,
+            p_emo_8.asset_path AS emotion_8
+        FROM server.avatars a
+        -- Skin layer joins
+        LEFT JOIN server.props p_skin_0 ON a.l_skin_0 = p_skin_0.id
+        LEFT JOIN server.props p_skin_1 ON a.l_skin_1 = p_skin_1.id
+        LEFT JOIN server.props p_skin_2 ON a.l_skin_2 = p_skin_2.id
+        LEFT JOIN server.props p_skin_3 ON a.l_skin_3 = p_skin_3.id
+        LEFT JOIN server.props p_skin_4 ON a.l_skin_4 = p_skin_4.id
+        LEFT JOIN server.props p_skin_5 ON a.l_skin_5 = p_skin_5.id
+        LEFT JOIN server.props p_skin_6 ON a.l_skin_6 = p_skin_6.id
+        LEFT JOIN server.props p_skin_7 ON a.l_skin_7 = p_skin_7.id
+        LEFT JOIN server.props p_skin_8 ON a.l_skin_8 = p_skin_8.id
+        -- Clothes layer joins
+        LEFT JOIN server.props p_clothes_0 ON a.l_clothes_0 = p_clothes_0.id
+        LEFT JOIN server.props p_clothes_1 ON a.l_clothes_1 = p_clothes_1.id
+        LEFT JOIN server.props p_clothes_2 ON a.l_clothes_2 = p_clothes_2.id
+        LEFT JOIN server.props p_clothes_3 ON a.l_clothes_3 = p_clothes_3.id
+        LEFT JOIN server.props p_clothes_4 ON a.l_clothes_4 = p_clothes_4.id
+        LEFT JOIN server.props p_clothes_5 ON a.l_clothes_5 = p_clothes_5.id
+        LEFT JOIN server.props p_clothes_6 ON a.l_clothes_6 = p_clothes_6.id
+        LEFT JOIN server.props p_clothes_7 ON a.l_clothes_7 = p_clothes_7.id
+        LEFT JOIN server.props p_clothes_8 ON a.l_clothes_8 = p_clothes_8.id
+        -- Accessories layer joins
+        LEFT JOIN server.props p_acc_0 ON a.l_accessories_0 = p_acc_0.id
+        LEFT JOIN server.props p_acc_1 ON a.l_accessories_1 = p_acc_1.id
+        LEFT JOIN server.props p_acc_2 ON a.l_accessories_2 = p_acc_2.id
+        LEFT JOIN server.props p_acc_3 ON a.l_accessories_3 = p_acc_3.id
+        LEFT JOIN server.props p_acc_4 ON a.l_accessories_4 = p_acc_4.id
+        LEFT JOIN server.props p_acc_5 ON a.l_accessories_5 = p_acc_5.id
+        LEFT JOIN server.props p_acc_6 ON a.l_accessories_6 = p_acc_6.id
+        LEFT JOIN server.props p_acc_7 ON a.l_accessories_7 = p_acc_7.id
+        LEFT JOIN server.props p_acc_8 ON a.l_accessories_8 = p_acc_8.id
+        -- Happy emotion layer joins (e1 - more inviting for store display)
+        LEFT JOIN server.props p_emo_0 ON a.e_happy_0 = p_emo_0.id
+        LEFT JOIN server.props p_emo_1 ON a.e_happy_1 = p_emo_1.id
+        LEFT JOIN server.props p_emo_2 ON a.e_happy_2 = p_emo_2.id
+        LEFT JOIN server.props p_emo_3 ON a.e_happy_3 = p_emo_3.id
+        LEFT JOIN server.props p_emo_4 ON a.e_happy_4 = p_emo_4.id
+        LEFT JOIN server.props p_emo_5 ON a.e_happy_5 = p_emo_5.id
+        LEFT JOIN server.props p_emo_6 ON a.e_happy_6 = p_emo_6.id
+        LEFT JOIN server.props p_emo_7 ON a.e_happy_7 = p_emo_7.id
+        LEFT JOIN server.props p_emo_8 ON a.e_happy_8 = p_emo_8.id
+        WHERE a.is_active = true AND a.is_public = true
+        ORDER BY a.name ASC
+        "#,
+    )
+    .fetch_all(executor)
+    .await?;
+
+    Ok(rows.into_iter().map(ServerAvatarWithPaths::from).collect())
+}
+
 /// Row type for prop asset lookup.
 #[derive(Debug, sqlx::FromRow)]
 struct PropAssetRow {

crates/chattyness-user-ui/src/api/avatars.rs

@@ -7,7 +7,7 @@ use axum::Json;
 use axum::extract::Path;
 
 use chattyness_db::{
-    models::{AssignSlotRequest, AvatarWithPaths, ClearSlotRequest, RealmAvatar, ServerAvatar},
+    models::{AssignSlotRequest, AvatarWithPaths, ClearSlotRequest, RealmAvatarWithPaths, ServerAvatarWithPaths},
     queries::{avatars, realm_avatars, realms, server_avatars},
 };
 use chattyness_error::AppError;
@@ -135,33 +135,35 @@ pub async fn clear_slot(
 // Avatar Store Endpoints
 // =============================================================================
 
-/// List public server avatars.
+/// List public server avatars with resolved paths.
 ///
 /// GET /api/server/avatars
 ///
 /// Returns all public, active server avatars that users can select from.
+/// Includes resolved asset paths for client-side rendering.
 pub async fn list_server_avatars(
     State(pool): State<PgPool>,
-) -> Result<Json<Vec<ServerAvatar>>, AppError> {
+) -> Result<Json<Vec<ServerAvatarWithPaths>>, AppError> {
-    let avatars = server_avatars::list_public_server_avatars(&pool).await?;
+    let avatars = server_avatars::list_public_server_avatars_with_paths(&pool).await?;
     Ok(Json(avatars))
 }
 
-/// List public realm avatars.
+/// List public realm avatars with resolved paths.
 ///
 /// GET /api/realms/{slug}/avatars
 ///
 /// Returns all public, active realm avatars for the specified realm.
+/// Includes resolved asset paths for client-side rendering.
 pub async fn list_realm_avatars(
     State(pool): State<PgPool>,
     Path(slug): Path<String>,
-) -> Result<Json<Vec<RealmAvatar>>, AppError> {
+) -> Result<Json<Vec<RealmAvatarWithPaths>>, AppError> {
     // Get realm
     let realm = realms::get_realm_by_slug(&pool, &slug)
         .await?
         .ok_or_else(|| AppError::NotFound(format!("Realm '{}' not found", slug)))?;
 
-    let avatars = realm_avatars::list_public_realm_avatars(&pool, realm.id).await?;
+    let avatars = realm_avatars::list_public_realm_avatars_with_paths(&pool, realm.id).await?;
     Ok(Json(avatars))
 }
 

crates/chattyness-user-ui/src/components.rs

@@ -3,6 +3,7 @@
 pub mod avatar_canvas;
 pub mod avatar_editor;
 pub mod avatar_store;
+pub mod avatar_thumbnail;
 pub mod chat;
 pub mod chat_types;
 pub mod context_menu;
@@ -30,6 +31,7 @@ pub mod ws_client;
 pub use avatar_canvas::*;
 pub use avatar_editor::*;
 pub use avatar_store::*;
+pub use avatar_thumbnail::*;
 pub use chat::*;
 pub use chat_types::*;
 pub use context_menu::*;

crates/chattyness-user-ui/src/components/avatar_store.rs

@@ -5,8 +5,9 @@
 use leptos::prelude::*;
 use uuid::Uuid;
 
-use chattyness_db::models::{RealmAvatar, ServerAvatar};
+use chattyness_db::models::{RealmAvatarWithPaths, ServerAvatarWithPaths};
 
+use super::avatar_thumbnail::AvatarThumbnail;
 use super::modals::{GuestLockedOverlay, Modal};
 use super::tabs::{Tab, TabBar};
 
@@ -40,13 +41,13 @@ pub fn AvatarStorePopup(
     let (active_tab, set_active_tab) = signal("server");
 
     // Server avatars state
-    let (server_avatars, set_server_avatars) = signal(Vec::<ServerAvatar>::new());
+    let (server_avatars, set_server_avatars) = signal(Vec::<ServerAvatarWithPaths>::new());
     let (server_loading, set_server_loading) = signal(false);
     let (server_error, set_server_error) = signal(Option::<String>::None);
     let (selected_server_avatar, set_selected_server_avatar) = signal(Option::<Uuid>::None);
 
     // Realm avatars state
-    let (realm_avatars, set_realm_avatars) = signal(Vec::<RealmAvatar>::new());
+    let (realm_avatars, set_realm_avatars) = signal(Vec::<RealmAvatarWithPaths>::new());
     let (realm_loading, set_realm_loading) = signal(false);
     let (realm_error, set_realm_error) = signal(Option::<String>::None);
     let (selected_realm_avatar, set_selected_realm_avatar) = signal(Option::<Uuid>::None);
@@ -88,7 +89,7 @@ pub fn AvatarStorePopup(
                 let response = Request::get("/api/server/avatars").send().await;
                 match response {
                     Ok(resp) if resp.ok() => {
-                        if let Ok(data) = resp.json::<Vec<ServerAvatar>>().await {
+                        if let Ok(data) = resp.json::<Vec<ServerAvatarWithPaths>>().await {
                             set_server_avatars.set(data);
                             set_server_loaded.set(true);
                         } else {
@@ -136,7 +137,7 @@ pub fn AvatarStorePopup(
                     .await;
                 match response {
                     Ok(resp) if resp.ok() => {
-                        if let Ok(data) = resp.json::<Vec<RealmAvatar>>().await {
+                        if let Ok(data) = resp.json::<Vec<RealmAvatarWithPaths>>().await {
                             set_realm_avatars.set(data);
                             set_realm_loaded.set(true);
                         } else {
@@ -298,7 +299,10 @@ pub fn AvatarStorePopup(
                                 id: a.id,
                                 name: a.name,
                                 description: a.description,
-                                thumbnail_path: a.thumbnail_path,
+                                skin_layer: a.skin_layer,
+                                clothes_layer: a.clothes_layer,
+                                accessories_layer: a.accessories_layer,
+                                emotion_layer: a.emotion_layer,
                             }).collect())
                             loading=server_loading
                             error=server_error
@@ -321,7 +325,10 @@ pub fn AvatarStorePopup(
                                 id: a.id,
                                 name: a.name,
                                 description: a.description,
-                                thumbnail_path: a.thumbnail_path,
+                                skin_layer: a.skin_layer,
+                                clothes_layer: a.clothes_layer,
+                                accessories_layer: a.accessories_layer,
+                                emotion_layer: a.emotion_layer,
                             }).collect())
                             loading=realm_loading
                             error=realm_error
@@ -347,13 +354,20 @@ pub fn AvatarStorePopup(
     }
 }
 
-/// Simplified avatar info for the grid.
+/// Simplified avatar info for the grid with resolved paths for rendering.
 #[derive(Clone)]
 struct AvatarInfo {
     id: Uuid,
     name: String,
     description: Option<String>,
-    thumbnail_path: Option<String>,
+    /// Asset paths for skin layer positions 0-8
+    skin_layer: [Option<String>; 9],
+    /// Asset paths for clothes layer positions 0-8
+    clothes_layer: [Option<String>; 9],
+    /// Asset paths for accessories layer positions 0-8
+    accessories_layer: [Option<String>; 9],
+    /// Asset paths for emotion layer positions 0-8
+    emotion_layer: [Option<String>; 9],
 }
 
 /// Avatars tab content with selection functionality.
@@ -415,15 +429,30 @@ fn AvatarsTab(
                             let avatar_id = avatar.id;
                             let avatar_name = avatar.name.clone();
                             let is_selected = move || selected_id.get() == Some(avatar_id);
-                            let thumbnail_url = avatar.thumbnail_path.clone()
+
-                                .map(|p| format!("/assets/{}", p))
+                            // Create signals for the layer data
-                                .unwrap_or_else(|| "/static/placeholder-avatar.svg".to_string());
+                            let skin_layer = Signal::derive({
+                                let layers = avatar.skin_layer.clone();
+                                move || layers.clone()
+                            });
+                            let clothes_layer = Signal::derive({
+                                let layers = avatar.clothes_layer.clone();
+                                move || layers.clone()
+                            });
+                            let accessories_layer = Signal::derive({
+                                let layers = avatar.accessories_layer.clone();
+                                move || layers.clone()
+                            });
+                            let emotion_layer = Signal::derive({
+                                let layers = avatar.emotion_layer.clone();
+                                move || layers.clone()
+                            });
 
                             view! {
                                 <button
                                     type="button"
                                     class=move || format!(
-                                        "aspect-square rounded-lg border-2 transition-all p-2 focus:outline-none focus:ring-2 focus:ring-blue-500 {}",
+                                        "aspect-square rounded-lg border-2 transition-all p-1 focus:outline-none focus:ring-2 focus:ring-blue-500 {}",
                                         if is_selected() {
                                             "border-blue-500 bg-blue-900/30"
                                         } else {
@@ -437,10 +466,12 @@ fn AvatarsTab(
                                     aria-selected=is_selected
                                     aria-label=avatar_name
                                 >
-                                    <img
+                                    <AvatarThumbnail
-                                        src=thumbnail_url
+                                        skin_layer=skin_layer
-                                        alt=""
+                                        clothes_layer=clothes_layer
-                                        class="w-full h-full object-contain rounded"
+                                        accessories_layer=accessories_layer
+                                        emotion_layer=emotion_layer
+                                        size=72
                                     />
                                 </button>
                             }

crates/chattyness-user-ui/src/components/avatar_thumbnail.rs

@@ -0,0 +1,145 @@
+//! Avatar thumbnail component for rendering avatar previews.
+//!
+//! A simplified canvas-based component for rendering avatars in the
+//! avatar store grid. Uses the same layer compositing as the main
+//! avatar renderer but at thumbnail size.
+
+use leptos::prelude::*;
+use leptos::web_sys;
+
+/// Avatar thumbnail component for the avatar store.
+///
+/// Renders a small preview of an avatar using canvas compositing.
+/// Takes layer paths directly as props.
+///
+/// Props:
+/// - `skin_layer`: Asset paths for skin layer positions 0-8
+/// - `clothes_layer`: Asset paths for clothes layer positions 0-8
+/// - `accessories_layer`: Asset paths for accessories layer positions 0-8
+/// - `emotion_layer`: Asset paths for emotion layer positions 0-8
+/// - `size`: Optional canvas size in pixels (default: 80)
+#[component]
+pub fn AvatarThumbnail(
+    #[prop(into)] skin_layer: Signal<[Option<String>; 9]>,
+    #[prop(into)] clothes_layer: Signal<[Option<String>; 9]>,
+    #[prop(into)] accessories_layer: Signal<[Option<String>; 9]>,
+    #[prop(into)] emotion_layer: Signal<[Option<String>; 9]>,
+    #[prop(default = 80)] size: u32,
+) -> impl IntoView {
+    let canvas_ref = NodeRef::<leptos::html::Canvas>::new();
+    let cell_size = size / 3;
+
+    #[cfg(feature = "hydrate")]
+    {
+        use std::cell::RefCell;
+        use std::collections::HashMap;
+        use std::rc::Rc;
+        use wasm_bindgen::JsCast;
+        use wasm_bindgen::closure::Closure;
+
+        use crate::utils::normalize_asset_path;
+
+        // Image cache for this thumbnail
+        let image_cache: Rc<RefCell<HashMap<String, web_sys::HtmlImageElement>>> =
+            Rc::new(RefCell::new(HashMap::new()));
+
+        // Redraw trigger - incremented when images load
+        let (redraw_trigger, set_redraw_trigger) = signal(0u32);
+
+        Effect::new(move |_| {
+            // Subscribe to redraw trigger
+            let _ = redraw_trigger.get();
+
+            let Some(canvas) = canvas_ref.get() else {
+                return;
+            };
+
+            let skin = skin_layer.get();
+            let clothes = clothes_layer.get();
+            let accessories = accessories_layer.get();
+            let emotion = emotion_layer.get();
+
+            let canvas_el: &web_sys::HtmlCanvasElement = &canvas;
+            canvas_el.set_width(size);
+            canvas_el.set_height(size);
+
+            let Ok(Some(ctx)) = canvas_el.get_context("2d") else {
+                return;
+            };
+            let ctx: web_sys::CanvasRenderingContext2d = ctx.dyn_into().unwrap();
+
+            // Clear canvas
+            ctx.clear_rect(0.0, 0.0, size as f64, size as f64);
+
+            // Draw background
+            ctx.set_fill_style_str("#374151");
+            ctx.fill_rect(0.0, 0.0, size as f64, size as f64);
+
+            // Helper to load and draw an image at a grid position
+            let draw_at_position =
+                |path: &str,
+                 pos: usize,
+                 cache: &Rc<RefCell<HashMap<String, web_sys::HtmlImageElement>>>,
+                 ctx: &web_sys::CanvasRenderingContext2d| {
+                    let normalized_path = normalize_asset_path(path);
+                    let mut cache_borrow = cache.borrow_mut();
+                    let row = pos / 3;
+                    let col = pos % 3;
+                    let x = (col * cell_size as usize) as f64;
+                    let y = (row * cell_size as usize) as f64;
+                    let sz = cell_size as f64;
+
+                    if let Some(img) = cache_borrow.get(&normalized_path) {
+                        if img.complete() && img.natural_width() > 0 {
+                            let _ = ctx.draw_image_with_html_image_element_and_dw_and_dh(
+                                img, x, y, sz, sz,
+                            );
+                        }
+                    } else {
+                        let img = web_sys::HtmlImageElement::new().unwrap();
+                        let trigger = set_redraw_trigger;
+                        let onload = Closure::once(Box::new(move || {
+                            trigger.update(|v| *v += 1);
+                        }) as Box<dyn FnOnce()>);
+                        img.set_onload(Some(onload.as_ref().unchecked_ref()));
+                        onload.forget();
+                        img.set_src(&normalized_path);
+                        cache_borrow.insert(normalized_path, img);
+                    }
+                };
+
+            // Draw layers in order: skin -> clothes -> accessories -> emotion
+            for (pos, path) in skin.iter().enumerate() {
+                if let Some(p) = path {
+                    draw_at_position(p, pos, &image_cache, &ctx);
+                }
+            }
+
+            for (pos, path) in clothes.iter().enumerate() {
+                if let Some(p) = path {
+                    draw_at_position(p, pos, &image_cache, &ctx);
+                }
+            }
+
+            for (pos, path) in accessories.iter().enumerate() {
+                if let Some(p) = path {
+                    draw_at_position(p, pos, &image_cache, &ctx);
+                }
+            }
+
+            for (pos, path) in emotion.iter().enumerate() {
+                if let Some(p) = path {
+                    draw_at_position(p, pos, &image_cache, &ctx);
+                }
+            }
+        });
+    }
+
+    view! {
+        <canvas
+            node_ref=canvas_ref
+            style=format!("width: {}px; height: {}px;", size, size)
+            class="rounded"
+        />
+    }
+}