coworkunion/chattyness
SHA256
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions

add initial crates and apps

Browse source
This commit is contained in:
Evan Carroll 2026-01-12 15:34:40 -06:00
parent 5c87ba3519
commit 1ca300098f
113 changed files with 28169 additions and 0 deletions
Download patch file Download diff file Expand all files Collapse all files

16
crates/chattyness-db/src/lib.rs Normal file
View file

@ -0,0 +1,16 @@
//! Database module for chattyness.
//!
//! Provides SQLx-based database access with runtime queries.
pub mod models;
pub mod ws_messages;
#[cfg(feature = "ssr")]
pub mod pool;
#[cfg(feature = "ssr")]
pub mod queries;
pub use models::*;
pub use ws_messages::*;
#[cfg(feature = "ssr")]
pub use pool::*;

1697
crates/chattyness-db/src/models.rs Normal file
View file

File diff suppressed because it is too large Load diff

84
crates/chattyness-db/src/pool.rs Normal file
View file

@ -0,0 +1,84 @@
//! Database connection pool and RLS context management.
use std::time::Duration;
use sqlx::{postgres::PgPoolOptions, PgPool};
use uuid::Uuid;
use chattyness_error::AppError;
/// Create a new database connection pool for the owner interface.
///
/// Uses the `chattyness_owner` role which has full database access.
pub async fn create_owner_pool(database_url: &str) -> Result<PgPool, AppError> {
let pool = PgPoolOptions::new()
.max_connections(5)
.acquire_timeout(Duration::from_secs(5))
.connect(database_url)
.await?;
Ok(pool)
}
/// Create a new database connection pool for the public app.
///
/// Uses the `chattyness_app` role which has Row-Level Security (RLS) policies.
pub async fn create_app_pool(database_url: &str) -> Result<PgPool, AppError> {
let pool = PgPoolOptions::new()
.max_connections(10)
.acquire_timeout(Duration::from_secs(5))
.connect(database_url)
.await?;
Ok(pool)
}
/// Set the current user context for Row-Level Security.
///
/// This should be called at the start of each request to enable RLS policies
/// that depend on the current user ID.
pub async fn set_user_context(pool: &PgPool, user_id: Option<Uuid>) -> Result<(), AppError> {
if let Some(id) = user_id {
sqlx::query("SELECT public.set_current_user_id($1)")
.bind(id)
.execute(pool)
.await?;
} else {
// Clear the user context for anonymous requests
sqlx::query("SELECT public.clear_current_user_id()")
.execute(pool)
.await?;
}
Ok(())
}
/// Clear the current user context.
///
/// Called at the end of a request to ensure the connection is clean
/// before returning to the pool.
pub async fn clear_user_context(pool: &PgPool) -> Result<(), AppError> {
sqlx::query("SELECT public.clear_current_user_id()")
.execute(pool)
.await?;
Ok(())
}
/// Set the current guest session context for Row-Level Security.
///
/// This should be called for guest users to enable RLS policies
/// that depend on the current guest session ID.
pub async fn set_guest_context(pool: &PgPool, guest_session_id: Uuid) -> Result<(), AppError> {
sqlx::query("SELECT public.set_current_guest_session_id($1)")
.bind(guest_session_id)
.execute(pool)
.await?;
Ok(())
}
/// Clear the current guest session context.
pub async fn clear_guest_context(pool: &PgPool) -> Result<(), AppError> {
sqlx::query("SELECT public.set_current_guest_session_id(NULL)")
.execute(pool)
.await?;
Ok(())
}

12
crates/chattyness-db/src/queries.rs Normal file
View file

@ -0,0 +1,12 @@
//! Database query modules.
pub mod avatars;
pub mod channel_members;
pub mod guests;
pub mod memberships;
pub mod owner;
pub mod props;
pub mod realms;
pub mod scenes;
pub mod spots;
pub mod users;

201
crates/chattyness-db/src/queries/avatars.rs Normal file
View file

@ -0,0 +1,201 @@
//! Avatar-related database queries.
use sqlx::PgExecutor;
use uuid::Uuid;
use crate::models::{ActiveAvatar, AvatarRenderData};
use chattyness_error::AppError;
/// Get the active avatar for a user in a realm.
pub async fn get_active_avatar<'e>(
executor: impl PgExecutor<'e>,
user_id: Uuid,
realm_id: Uuid,
) -> Result<Option<ActiveAvatar>, AppError> {
let avatar = sqlx::query_as::<_, ActiveAvatar>(
r#"
SELECT user_id, realm_id, avatar_id, current_emotion, updated_at
FROM props.active_avatars
WHERE user_id = $1 AND realm_id = $2
"#,
)
.bind(user_id)
.bind(realm_id)
.fetch_optional(executor)
.await?;
Ok(avatar)
}
/// Set the current emotion for a user in a realm.
/// Returns the full emotion layer (9 asset paths) for the new emotion.
pub async fn set_emotion<'e>(
executor: impl PgExecutor<'e>,
user_id: Uuid,
realm_id: Uuid,
emotion: i16,
) -> Result<[Option<String>; 9], AppError> {
if emotion < 0 || emotion > 9 {
return Err(AppError::Validation("Emotion must be 0-9".to_string()));
}
// Map emotion index to column prefix
let emotion_prefix = match emotion {
0 => "e_neutral",
1 => "e_happy",
2 => "e_sad",
3 => "e_angry",
4 => "e_surprised",
5 => "e_thinking",
6 => "e_laughing",
7 => "e_crying",
8 => "e_love",
9 => "e_confused",
_ => return Err(AppError::Validation("Emotion must be 0-9".to_string())),
};
// Build dynamic query for the specific emotion's 9 positions
let query = format!(
r#"
WITH updated AS (
UPDATE props.active_avatars
SET current_emotion = $3, updated_at = now()
WHERE user_id = $1 AND realm_id = $2
RETURNING avatar_id
)
SELECT
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_0) as p0,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_1) as p1,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_2) as p2,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_3) as p3,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_4) as p4,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_5) as p5,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_6) as p6,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_7) as p7,
(SELECT prop_asset_path FROM props.inventory WHERE id = a.{prefix}_8) as p8
FROM updated u
JOIN props.avatars a ON a.id = u.avatar_id
"#,
prefix = emotion_prefix
);
let result = sqlx::query_as::<_, EmotionLayerRow>(&query)
.bind(user_id)
.bind(realm_id)
.bind(emotion)
.fetch_optional(executor)
.await?;
match result {
Some(row) => Ok([
row.p0, row.p1, row.p2, row.p3, row.p4, row.p5, row.p6, row.p7, row.p8,
]),
None => Err(AppError::NotFound(
"No active avatar for this user in this realm".to_string(),
)),
}
}
/// Row type for emotion layer query.
#[derive(Debug, sqlx::FromRow)]
struct EmotionLayerRow {
p0: Option<String>,
p1: Option<String>,
p2: Option<String>,
p3: Option<String>,
p4: Option<String>,
p5: Option<String>,
p6: Option<String>,
p7: Option<String>,
p8: Option<String>,
}
/// Get render data for a user's avatar in a realm.
///
/// Returns the asset paths for all equipped props in the avatar's current state.
/// This is a simplified version that only returns the center position (position 4)
/// props for skin, clothes, accessories, and current emotion layers.
pub async fn get_avatar_render_data<'e>(
executor: impl PgExecutor<'e>,
user_id: Uuid,
realm_id: Uuid,
) -> Result<AvatarRenderData, AppError> {
// Simplified query: just get position 4 (center) props for each layer
// This covers the common case of simple face avatars
let render_data = sqlx::query_as::<_, SimplifiedAvatarRow>(
r#"
SELECT
a.id as avatar_id,
aa.current_emotion,
-- Skin layer center
skin.prop_asset_path as skin_center,
-- Clothes layer center
clothes.prop_asset_path as clothes_center,
-- Accessories layer center
acc.prop_asset_path as accessories_center,
-- Current emotion layer center (based on current_emotion)
CASE aa.current_emotion
WHEN 0 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_neutral_4)
WHEN 1 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_happy_4)
WHEN 2 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_sad_4)
WHEN 3 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_angry_4)
WHEN 4 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_surprised_4)
WHEN 5 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_thinking_4)
WHEN 6 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_laughing_4)
WHEN 7 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_crying_4)
WHEN 8 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_love_4)
WHEN 9 THEN (SELECT prop_asset_path FROM props.inventory WHERE id = a.e_confused_4)
END as emotion_center
FROM props.active_avatars aa
JOIN props.avatars a ON aa.avatar_id = a.id
LEFT JOIN props.inventory skin ON a.l_skin_4 = skin.id
LEFT JOIN props.inventory clothes ON a.l_clothes_4 = clothes.id
LEFT JOIN props.inventory acc ON a.l_accessories_4 = acc.id
WHERE aa.user_id = $1 AND aa.realm_id = $2
"#,
)
.bind(user_id)
.bind(realm_id)
.fetch_optional(executor)
.await?;
match render_data {
Some(row) => Ok(row.into()),
None => Ok(AvatarRenderData::default()),
}
}
/// Simplified avatar row for center-only rendering.
#[derive(Debug, sqlx::FromRow)]
struct SimplifiedAvatarRow {
avatar_id: Uuid,
current_emotion: i16,
skin_center: Option<String>,
clothes_center: Option<String>,
accessories_center: Option<String>,
emotion_center: Option<String>,
}
impl From<SimplifiedAvatarRow> for AvatarRenderData {
fn from(row: SimplifiedAvatarRow) -> Self {
// For now, only populate position 4 (center)
let mut skin_layer: [Option<String>; 9] = Default::default();
let mut clothes_layer: [Option<String>; 9] = Default::default();
let mut accessories_layer: [Option<String>; 9] = Default::default();
let mut emotion_layer: [Option<String>; 9] = Default::default();
skin_layer[4] = row.skin_center;
clothes_layer[4] = row.clothes_center;
accessories_layer[4] = row.accessories_center;
emotion_layer[4] = row.emotion_center;
Self {
avatar_id: row.avatar_id,
current_emotion: row.current_emotion,
skin_layer,
clothes_layer,
accessories_layer,
emotion_layer,
}
}
}

233
crates/chattyness-db/src/queries/channel_members.rs Normal file
View file

@ -0,0 +1,233 @@
//! Channel member queries for user presence in channels.
use sqlx::PgExecutor;
use uuid::Uuid;
use crate::models::{ChannelMember, ChannelMemberInfo};
use chattyness_error::AppError;
/// Join a channel as an authenticated user.
///
/// Creates a channel_members entry with default position (400, 300).
pub async fn join_channel<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
user_id: Uuid,
) -> Result<ChannelMember, AppError> {
let member = sqlx::query_as::<_, ChannelMember>(
r#"
INSERT INTO realm.channel_members (channel_id, user_id, position)
VALUES ($1, $2, ST_SetSRID(ST_MakePoint(400, 300), 0))
ON CONFLICT (channel_id, user_id) DO UPDATE
SET joined_at = now()
RETURNING
id,
channel_id,
user_id,
guest_session_id,
ST_X(position) as position_x,
ST_Y(position) as position_y,
facing_direction,
is_moving,
is_afk,
joined_at,
last_moved_at
"#,
)
.bind(channel_id)
.bind(user_id)
.fetch_one(executor)
.await?;
Ok(member)
}
/// Ensure an active avatar exists for a user in a realm.
/// Uses the user's default avatar (slot 0) if none exists.
pub async fn ensure_active_avatar<'e>(
executor: impl PgExecutor<'e>,
user_id: Uuid,
realm_id: Uuid,
) -> Result<(), AppError> {
sqlx::query(
r#"
INSERT INTO props.active_avatars (user_id, realm_id, avatar_id, current_emotion)
SELECT $1, $2, id, 0
FROM props.avatars
WHERE user_id = $1 AND slot_number = 0
ON CONFLICT (user_id, realm_id) DO NOTHING
"#,
)
.bind(user_id)
.bind(realm_id)
.execute(executor)
.await?;
Ok(())
}
/// Leave a channel.
pub async fn leave_channel<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
user_id: Uuid,
) -> Result<(), AppError> {
sqlx::query(
r#"DELETE FROM realm.channel_members WHERE channel_id = $1 AND user_id = $2"#,
)
.bind(channel_id)
.bind(user_id)
.execute(executor)
.await?;
Ok(())
}
/// Update a user's position in a channel.
pub async fn update_position<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
user_id: Uuid,
x: f64,
y: f64,
) -> Result<(), AppError> {
let result = sqlx::query(
r#"
UPDATE realm.channel_members
SET position = ST_SetSRID(ST_MakePoint($3, $4), 0),
last_moved_at = now(),
is_moving = true
WHERE channel_id = $1 AND user_id = $2
"#,
)
.bind(channel_id)
.bind(user_id)
.bind(x)
.bind(y)
.execute(executor)
.await?;
if result.rows_affected() == 0 {
return Err(AppError::NotFound("Channel member not found".to_string()));
}
Ok(())
}
/// Get all members in a channel with their display info and current emotion.
pub async fn get_channel_members<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
realm_id: Uuid,
) -> Result<Vec<ChannelMemberInfo>, AppError> {
let members = sqlx::query_as::<_, ChannelMemberInfo>(
r#"
SELECT
cm.id,
cm.channel_id,
cm.user_id,
cm.guest_session_id,
COALESCE(u.display_name, gs.guest_name, 'Anonymous') as display_name,
ST_X(cm.position) as position_x,
ST_Y(cm.position) as position_y,
cm.facing_direction,
cm.is_moving,
cm.is_afk,
COALESCE(aa.current_emotion, 0::smallint) as current_emotion,
cm.joined_at
FROM realm.channel_members cm
LEFT JOIN auth.users u ON cm.user_id = u.id
LEFT JOIN auth.guest_sessions gs ON cm.guest_session_id = gs.id
LEFT JOIN props.active_avatars aa ON cm.user_id = aa.user_id AND aa.realm_id = $2
WHERE cm.channel_id = $1
ORDER BY cm.joined_at ASC
"#,
)
.bind(channel_id)
.bind(realm_id)
.fetch_all(executor)
.await?;
Ok(members)
}
/// Get a specific channel member by user ID.
pub async fn get_channel_member<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
user_id: Uuid,
realm_id: Uuid,
) -> Result<Option<ChannelMemberInfo>, AppError> {
let member = sqlx::query_as::<_, ChannelMemberInfo>(
r#"
SELECT
cm.id,
cm.channel_id,
cm.user_id,
cm.guest_session_id,
COALESCE(u.display_name, 'Anonymous') as display_name,
ST_X(cm.position) as position_x,
ST_Y(cm.position) as position_y,
cm.facing_direction,
cm.is_moving,
cm.is_afk,
COALESCE(aa.current_emotion, 0::smallint) as current_emotion,
cm.joined_at
FROM realm.channel_members cm
LEFT JOIN auth.users u ON cm.user_id = u.id
LEFT JOIN props.active_avatars aa ON cm.user_id = aa.user_id AND aa.realm_id = $3
WHERE cm.channel_id = $1 AND cm.user_id = $2
"#,
)
.bind(channel_id)
.bind(user_id)
.bind(realm_id)
.fetch_optional(executor)
.await?;
Ok(member)
}
/// Set a user's moving state to false (called after movement animation completes).
pub async fn set_stopped<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
user_id: Uuid,
) -> Result<(), AppError> {
sqlx::query(
r#"
UPDATE realm.channel_members
SET is_moving = false
WHERE channel_id = $1 AND user_id = $2
"#,
)
.bind(channel_id)
.bind(user_id)
.execute(executor)
.await?;
Ok(())
}
/// Set a user's AFK state.
pub async fn set_afk<'e>(
executor: impl PgExecutor<'e>,
channel_id: Uuid,
user_id: Uuid,
is_afk: bool,
) -> Result<(), AppError> {
sqlx::query(
r#"
UPDATE realm.channel_members
SET is_afk = $3
WHERE channel_id = $1 AND user_id = $2
"#,
)
.bind(channel_id)
.bind(user_id)
.bind(is_afk)
.execute(executor)
.await?;
Ok(())
}

95
crates/chattyness-db/src/queries/guests.rs Normal file
View file

@ -0,0 +1,95 @@
//! Guest session database queries.
use chrono::{DateTime, TimeDelta, Utc};
use sqlx::PgPool;
use uuid::Uuid;
use chattyness_error::AppError;
/// Guest session record.
#[derive(Debug, Clone)]
#[cfg_attr(feature = "ssr", derive(sqlx::FromRow))]
pub struct GuestSession {
pub id: Uuid,
pub guest_name: String,
pub current_realm_id: Option<Uuid>,
pub expires_at: DateTime<Utc>,
pub created_at: DateTime<Utc>,
}
/// Create a new guest session.
///
/// Returns the guest session ID.
pub async fn create_guest_session(
pool: &PgPool,
guest_name: &str,
realm_id: Uuid,
token_hash: &str,
user_agent: Option<&str>,
ip_address: Option<&str>,
expires_at: DateTime<Utc>,
) -> Result<Uuid, AppError> {
let (session_id,): (Uuid,) = sqlx::query_as(
r#"
INSERT INTO auth.guest_sessions (guest_name, token_hash, user_agent, ip_address, current_realm_id, expires_at)
VALUES ($1, $2, $3, $4::inet, $5, $6)
RETURNING id
"#,
)
.bind(guest_name)
.bind(token_hash)
.bind(user_agent)
.bind(ip_address)
.bind(realm_id)
.bind(expires_at)
.fetch_one(pool)
.await?;
Ok(session_id)
}
/// Get a guest session by ID.
pub async fn get_guest_session(pool: &PgPool, session_id: Uuid) -> Result<Option<GuestSession>, AppError> {
let session = sqlx::query_as::<_, GuestSession>(
r#"
SELECT id, guest_name, current_realm_id, expires_at, created_at
FROM auth.guest_sessions
WHERE id = $1 AND expires_at > now()
"#,
)
.bind(session_id)
.fetch_optional(pool)
.await?;
// Update last activity if session exists
if session.is_some() {
sqlx::query("UPDATE auth.guest_sessions SET last_activity_at = now() WHERE id = $1")
.bind(session_id)
.execute(pool)
.await?;
}
Ok(session)
}
/// Delete a guest session.
pub async fn delete_guest_session(pool: &PgPool, session_id: Uuid) -> Result<(), AppError> {
sqlx::query("DELETE FROM auth.guest_sessions WHERE id = $1")
.bind(session_id)
.execute(pool)
.await?;
Ok(())
}
/// Generate a random guest name like "Guest_12345".
pub fn generate_guest_name() -> String {
use rand::Rng;
let mut rng = rand::thread_rng();
let number: u32 = rng.gen_range(10000..100000);
format!("Guest_{}", number)
}
/// Calculate guest session expiry (24 hours from now).
pub fn guest_session_expiry() -> DateTime<Utc> {
Utc::now() + TimeDelta::hours(24)
}

201
crates/chattyness-db/src/queries/memberships.rs Normal file
View file

@ -0,0 +1,201 @@
//! Membership-related database queries.
use sqlx::PgPool;
use uuid::Uuid;
use crate::models::{Membership, MembershipWithRealm, RealmRole, ServerRole};
use chattyness_error::AppError;
/// Get a user's membership in a specific realm.
pub async fn get_user_membership(
pool: &PgPool,
user_id: Uuid,
realm_id: Uuid,
) -> Result<Option<Membership>, AppError> {
let membership = sqlx::query_as::<_, Membership>(
r#"
SELECT
id,
realm_id,
user_id,
role,
nickname,
created_at AS joined_at,
last_visited_at
FROM realm.memberships
WHERE user_id = $1 AND realm_id = $2
"#,
)
.bind(user_id)
.bind(realm_id)
.fetch_optional(pool)
.await?;
Ok(membership)
}
/// Create a new membership (join a realm).
pub async fn create_membership(
pool: &PgPool,
user_id: Uuid,
realm_id: Uuid,
role: RealmRole,
) -> Result<Uuid, AppError> {
let (membership_id,): (Uuid,) = sqlx::query_as(
r#"
INSERT INTO realm.memberships (realm_id, user_id, role)
VALUES ($1, $2, $3)
RETURNING id
"#,
)
.bind(realm_id)
.bind(user_id)
.bind(role)
.fetch_one(pool)
.await?;
// Update member count on the realm
sqlx::query("UPDATE realm.realms SET member_count = member_count + 1 WHERE id = $1")
.bind(realm_id)
.execute(pool)
.await?;
Ok(membership_id)
}
/// Create a new membership using a connection (for RLS support).
pub async fn create_membership_conn(
conn: &mut sqlx::PgConnection,
user_id: Uuid,
realm_id: Uuid,
role: RealmRole,
) -> Result<Uuid, AppError> {
let (membership_id,): (Uuid,) = sqlx::query_as(
r#"
INSERT INTO realm.memberships (realm_id, user_id, role)
VALUES ($1, $2, $3)
RETURNING id
"#,
)
.bind(realm_id)
.bind(user_id)
.bind(role)
.fetch_one(&mut *conn)
.await?;
// Update member count on the realm
sqlx::query("UPDATE realm.realms SET member_count = member_count + 1 WHERE id = $1")
.bind(realm_id)
.execute(&mut *conn)
.await?;
Ok(membership_id)
}
/// Get all realm memberships for a user.
pub async fn get_user_memberships(
pool: &PgPool,
user_id: Uuid,
) -> Result<Vec<MembershipWithRealm>, AppError> {
let memberships = sqlx::query_as::<_, MembershipWithRealm>(
r#"
SELECT
m.id AS membership_id,
m.realm_id,
r.name AS realm_name,
r.slug AS realm_slug,
r.privacy AS realm_privacy,
m.role,
m.nickname,
m.last_visited_at
FROM realm.memberships m
JOIN realm.realms r ON m.realm_id = r.id
WHERE m.user_id = $1
ORDER BY m.last_visited_at DESC NULLS LAST
"#,
)
.bind(user_id)
.fetch_all(pool)
.await?;
Ok(memberships)
}
/// Get a user's server staff role (if any).
pub async fn get_user_staff_role(
pool: &PgPool,
user_id: Uuid,
) -> Result<Option<ServerRole>, AppError> {
let result: Option<(ServerRole,)> = sqlx::query_as(
r#"
SELECT role
FROM server.staff
WHERE user_id = $1
"#,
)
.bind(user_id)
.fetch_optional(pool)
.await?;
Ok(result.map(|(role,)| role))
}
/// Update last visited timestamp for a membership.
pub async fn update_last_visited(
pool: &PgPool,
user_id: Uuid,
realm_id: Uuid,
) -> Result<(), AppError> {
sqlx::query(
r#"
UPDATE realm.memberships
SET last_visited_at = now()
WHERE user_id = $1 AND realm_id = $2
"#,
)
.bind(user_id)
.bind(realm_id)
.execute(pool)
.await?;
Ok(())
}
/// Update last visited timestamp using a connection (for RLS support).
pub async fn update_last_visited_conn(
conn: &mut sqlx::PgConnection,
user_id: Uuid,
realm_id: Uuid,
) -> Result<(), AppError> {
sqlx::query(
r#"
UPDATE realm.memberships
SET last_visited_at = now()
WHERE user_id = $1 AND realm_id = $2
"#,
)
.bind(user_id)
.bind(realm_id)
.execute(conn)
.await?;
Ok(())
}
/// Check if a user is a member of a realm.
pub async fn is_member(pool: &PgPool, user_id: Uuid, realm_id: Uuid) -> Result<bool, AppError> {
let exists: (bool,) = sqlx::query_as(
r#"
SELECT EXISTS(
SELECT 1 FROM realm.memberships
WHERE user_id = $1 AND realm_id = $2
)
"#,
)
.bind(user_id)
.bind(realm_id)
.fetch_one(pool)
.await?;
Ok(exists.0)
}

25
crates/chattyness-db/src/queries/owner.rs Normal file
View file

@ -0,0 +1,25 @@
//! Owner-related database queries.
//!
//! These queries are used by the owner interface and require the chattyness_owner role.
//!
//! This module is organized into submodules by domain:
//! - `config`: Server configuration queries
//! - `staff`: Staff management queries
//! - `users`: User management queries
//! - `realms`: Realm management queries
//! - `helpers`: Shared helper functions (password hashing, token generation)
mod config;
mod helpers;
mod realms;
mod staff;
mod users;
// Re-export all public functions for backwards compatibility
pub use config::*;
pub use realms::*;
pub use staff::*;
pub use users::*;
// Re-export helpers for use by other modules if needed
pub use helpers::{generate_random_token, hash_password};

93
crates/chattyness-db/src/queries/owner/config.rs Normal file
View file

@ -0,0 +1,93 @@
//! Server configuration database queries.
//!
//! These queries are used by the owner interface and require the chattyness_owner role.
use sqlx::PgPool;
use uuid::Uuid;
use crate::models::{ServerConfig, UpdateServerConfigRequest};
use chattyness_error::AppError;
/// The fixed UUID for the singleton server config row.
pub fn server_config_id() -> Uuid {
Uuid::parse_str("00000000-0000-0000-0000-000000000001").unwrap()
}
/// Get the server configuration.
pub async fn get_server_config(pool: &PgPool) -> Result<ServerConfig, AppError> {
let config = sqlx::query_as::<_, ServerConfig>(
r#"
SELECT
id,
name,
description,
welcome_message,
max_users_per_channel,
message_rate_limit,
message_rate_window_seconds,
allow_guest_access,
allow_user_uploads,
require_email_verification,
created_at,
updated_at
FROM server.config
WHERE id = $1
"#,
)
.bind(server_config_id())
.fetch_one(pool)
.await?;
Ok(config)
}
/// Update the server configuration.
pub async fn update_server_config(
pool: &PgPool,
req: &UpdateServerConfigRequest,
) -> Result<ServerConfig, AppError> {
let config = sqlx::query_as::<_, ServerConfig>(
r#"
UPDATE server.config
SET
name = $1,
description = $2,
welcome_message = $3,
max_users_per_channel = $4,
message_rate_limit = $5,
message_rate_window_seconds = $6,
allow_guest_access = $7,
allow_user_uploads = $8,
require_email_verification = $9,
updated_at = now()
WHERE id = $10
RETURNING
id,
name,
description,
welcome_message,
max_users_per_channel,
message_rate_limit,
message_rate_window_seconds,
allow_guest_access,
allow_user_uploads,
require_email_verification,
created_at,
updated_at
"#,
)
.bind(&req.name)
.bind(&req.description)
.bind(&req.welcome_message)
.bind(req.max_users_per_channel)
.bind(req.message_rate_limit)
.bind(req.message_rate_window_seconds)
.bind(req.allow_guest_access)
.bind(req.allow_user_uploads)
.bind(req.require_email_verification)
.bind(server_config_id())
.fetch_one(pool)
.await?;
Ok(config)
}

32
crates/chattyness-db/src/queries/owner/helpers.rs Normal file
View file

@ -0,0 +1,32 @@
//! Shared helper functions for owner queries.
use chattyness_error::AppError;
/// Hash a password using argon2.
pub fn hash_password(password: &str) -> Result<String, AppError> {
use argon2::{
password_hash::{rand_core::OsRng, PasswordHasher, SaltString},
Argon2,
};
let salt = SaltString::generate(&mut OsRng);
let argon2 = Argon2::default();
let hash = argon2
.hash_password(password.as_bytes(), &salt)
.map_err(|e| AppError::Internal(format!("Failed to hash password: {}", e)))?;
Ok(hash.to_string())
}
/// Generate a random 50-character alphanumeric token for temporary passwords.
pub fn generate_random_token() -> String {
use rand::Rng;
const CHARSET: &[u8] = b"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
let mut rng = rand::thread_rng();
(0..50)
.map(|_| {
let idx = rng.gen_range(0..CHARSET.len());
CHARSET[idx] as char
})
.collect()
}

385
crates/chattyness-db/src/queries/owner/realms.rs Normal file
View file

@ -0,0 +1,385 @@
//! Realm management database queries.
//!
//! These queries are used by the owner interface and require the chattyness_owner role.
use sqlx::PgPool;
use uuid::Uuid;
use crate::models::{
OwnerCreateRealmRequest, RealmDetail, RealmListItem, RealmPrivacy, UpdateRealmRequest,
};
use chattyness_error::AppError;
use super::helpers::{generate_random_token, hash_password};
/// List all realms with owner info (for admin interface).
pub async fn list_realms_with_owner(
pool: &PgPool,
limit: i64,
offset: i64,
) -> Result<Vec<RealmListItem>, AppError> {
let realms = sqlx::query_as::<_, RealmListItem>(
r#"
SELECT
r.id,
r.name,
r.slug,
r.tagline,
r.privacy,
r.is_nsfw,
r.owner_id,
u.username as owner_username,
r.member_count,
r.current_user_count,
r.created_at
FROM realm.realms r
JOIN auth.users u ON r.owner_id = u.id
ORDER BY r.created_at DESC
LIMIT $1 OFFSET $2
"#,
)
.bind(limit)
.bind(offset)
.fetch_all(pool)
.await?;
Ok(realms)
}
/// Search realms by name or slug.
pub async fn search_realms(
pool: &PgPool,
query: &str,
limit: i64,
) -> Result<Vec<RealmListItem>, AppError> {
let pattern = format!("%{}%", query);
let realms = sqlx::query_as::<_, RealmListItem>(
r#"
SELECT
r.id,
r.name,
r.slug,
r.tagline,
r.privacy,
r.is_nsfw,
r.owner_id,
u.username as owner_username,
r.member_count,
r.current_user_count,
r.created_at
FROM realm.realms r
JOIN auth.users u ON r.owner_id = u.id
WHERE
r.name ILIKE $1
OR r.slug ILIKE $1
ORDER BY
CASE
WHEN r.slug ILIKE $1 THEN 1
WHEN r.name ILIKE $1 THEN 2
ELSE 3
END,
r.name
LIMIT $2
"#,
)
.bind(&pattern)
.bind(limit)
.fetch_all(pool)
.await?;
Ok(realms)
}
/// Create a new realm with an existing user as owner.
#[allow(clippy::too_many_arguments)]
pub async fn create_realm(
pool: &PgPool,
owner_id: Uuid,
name: &str,
slug: &str,
description: Option<&str>,
tagline: Option<&str>,
privacy: RealmPrivacy,
is_nsfw: bool,
max_users: i32,
allow_guest_access: bool,
theme_color: Option<&str>,
) -> Result<Uuid, AppError> {
// Start a transaction
let mut tx = pool.begin().await?;
// Create the realm
let realm_id = sqlx::query_scalar::<_, Uuid>(
r#"
INSERT INTO realm.realms (
name, slug, description, tagline, owner_id,
privacy, is_nsfw, max_users, allow_guest_access, theme_color
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
RETURNING id
"#,
)
.bind(name)
.bind(slug)
.bind(description)
.bind(tagline)
.bind(owner_id)
.bind(privacy)
.bind(is_nsfw)
.bind(max_users)
.bind(allow_guest_access)
.bind(theme_color)
.fetch_one(&mut *tx)
.await?;
// Add owner as realm member with owner role
sqlx::query(
r#"
INSERT INTO realm.memberships (realm_id, user_id, role)
VALUES ($1, $2, 'owner')
"#,
)
.bind(realm_id)
.bind(owner_id)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok(realm_id)
}
/// Create a realm with a new user as owner (atomically).
/// Returns (realm_id, user_id, plaintext_token) - the token should be shown to the server owner.
pub async fn create_realm_with_new_owner(
pool: &PgPool,
req: &OwnerCreateRealmRequest,
) -> Result<(Uuid, Uuid, String), AppError> {
let new_owner = req
.new_owner
.as_ref()
.ok_or_else(|| AppError::Validation("new_owner is required".to_string()))?;
// Generate a random token as the temporary password
let token = generate_random_token();
let password_hash = hash_password(&token)?;
// Start a transaction
let mut tx = pool.begin().await?;
// Create the user with force_pw_reset = true
let user_id = sqlx::query_scalar::<_, Uuid>(
r#"
INSERT INTO auth.users (username, email, display_name, password_hash, force_pw_reset)
VALUES ($1, $2, $3, $4, true)
RETURNING id
"#,
)
.bind(&new_owner.username)
.bind(&new_owner.email)
.bind(&new_owner.display_name)
.bind(&password_hash)
.fetch_one(&mut *tx)
.await?;
// Create the realm
let realm_id = sqlx::query_scalar::<_, Uuid>(
r#"
INSERT INTO realm.realms (
name, slug, description, tagline, owner_id,
privacy, is_nsfw, max_users, allow_guest_access, theme_color
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
RETURNING id
"#,
)
.bind(&req.name)
.bind(&req.slug)
.bind(&req.description)
.bind(&req.tagline)
.bind(user_id)
.bind(req.privacy)
.bind(req.is_nsfw)
.bind(req.max_users)
.bind(req.allow_guest_access)
.bind(&req.theme_color)
.fetch_one(&mut *tx)
.await?;
// Add owner as realm member with owner role
sqlx::query(
r#"
INSERT INTO realm.memberships (realm_id, user_id, role)
VALUES ($1, $2, 'owner')
"#,
)
.bind(realm_id)
.bind(user_id)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok((realm_id, user_id, token))
}
/// Get a realm by slug with full details for editing.
pub async fn get_realm_by_slug(pool: &PgPool, slug: &str) -> Result<RealmDetail, AppError> {
let realm = sqlx::query_as::<_, RealmDetail>(
r#"
SELECT
r.id,
r.name,
r.slug,
r.description,
r.tagline,
r.owner_id,
u.username as owner_username,
u.display_name as owner_display_name,
r.privacy,
r.is_nsfw,
r.min_reputation_tier,
r.theme_color,
r.banner_image_path,
r.thumbnail_path,
r.max_users,
r.allow_guest_access,
r.member_count,
r.current_user_count,
r.created_at,
r.updated_at
FROM realm.realms r
JOIN auth.users u ON r.owner_id = u.id
WHERE r.slug = $1
"#,
)
.bind(slug)
.fetch_optional(pool)
.await?
.ok_or_else(|| AppError::NotFound(format!("Realm with slug '{}' not found", slug)))?;
Ok(realm)
}
/// Update a realm's settings.
pub async fn update_realm(
pool: &PgPool,
realm_id: Uuid,
req: &UpdateRealmRequest,
) -> Result<RealmDetail, AppError> {
// Update the realm
sqlx::query(
r#"
UPDATE realm.realms
SET
name = $1,
description = $2,
tagline = $3,
privacy = $4,
is_nsfw = $5,
max_users = $6,
allow_guest_access = $7,
theme_color = $8,
updated_at = now()
WHERE id = $9
"#,
)
.bind(&req.name)
.bind(&req.description)
.bind(&req.tagline)
.bind(req.privacy)
.bind(req.is_nsfw)
.bind(req.max_users)
.bind(req.allow_guest_access)
.bind(&req.theme_color)
.bind(realm_id)
.execute(pool)
.await?;
// Fetch and return the updated realm
let realm = sqlx::query_as::<_, RealmDetail>(
r#"
SELECT
r.id,
r.name,
r.slug,
r.description,
r.tagline,
r.owner_id,
u.username as owner_username,
u.display_name as owner_display_name,
r.privacy,
r.is_nsfw,
r.min_reputation_tier,
r.theme_color,
r.banner_image_path,
r.thumbnail_path,
r.max_users,
r.allow_guest_access,
r.member_count,
r.current_user_count,
r.created_at,
r.updated_at
FROM realm.realms r
JOIN auth.users u ON r.owner_id = u.id
WHERE r.id = $1
"#,
)
.bind(realm_id)
.fetch_one(pool)
.await?;
Ok(realm)
}
/// Transfer realm ownership to a different user.
pub async fn transfer_realm_ownership(
pool: &PgPool,
realm_id: Uuid,
new_owner_id: Uuid,
) -> Result<(), AppError> {
let mut tx = pool.begin().await?;
// Get current owner_id
let current_owner_id =
sqlx::query_scalar::<_, Uuid>(r#"SELECT owner_id FROM realm.realms WHERE id = $1"#)
.bind(realm_id)
.fetch_one(&mut *tx)
.await?;
// Update realm owner
sqlx::query(r#"UPDATE realm.realms SET owner_id = $1, updated_at = now() WHERE id = $2"#)
.bind(new_owner_id)
.bind(realm_id)
.execute(&mut *tx)
.await?;
// Update old owner's membership role to member (or remove?)
sqlx::query(
r#"
UPDATE realm.memberships
SET role = 'moderator'
WHERE realm_id = $1 AND user_id = $2
"#,
)
.bind(realm_id)
.bind(current_owner_id)
.execute(&mut *tx)
.await?;
// Ensure new owner has membership with owner role
sqlx::query(
r#"
INSERT INTO realm.memberships (realm_id, user_id, role)
VALUES ($1, $2, 'owner')
ON CONFLICT (realm_id, user_id) DO UPDATE SET role = 'owner'
"#,
)
.bind(realm_id)
.bind(new_owner_id)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok(())
}

111
crates/chattyness-db/src/queries/owner/staff.rs Normal file
View file

@ -0,0 +1,111 @@
//! Staff management database queries.
//!
//! These queries are used by the owner interface and require the chattyness_owner role.
use sqlx::PgPool;
use uuid::Uuid;
use crate::models::{ServerRole, StaffMember};
use chattyness_error::AppError;
/// Get all staff members.
pub async fn get_all_staff(pool: &PgPool) -> Result<Vec<StaffMember>, AppError> {
let staff = sqlx::query_as::<_, StaffMember>(
r#"
SELECT
s.user_id,
u.username,
u.display_name,
u.email,
s.role,
s.appointed_by,
s.appointed_at
FROM server.staff s
JOIN auth.users u ON s.user_id = u.id
ORDER BY
CASE s.role
WHEN 'owner' THEN 1
WHEN 'admin' THEN 2
WHEN 'moderator' THEN 3
END,
s.appointed_at DESC
"#,
)
.fetch_all(pool)
.await?;
Ok(staff)
}
/// Get staff members by role.
pub async fn get_staff_by_role(
pool: &PgPool,
role: ServerRole,
) -> Result<Vec<StaffMember>, AppError> {
let staff = sqlx::query_as::<_, StaffMember>(
r#"
SELECT
s.user_id,
u.username,
u.display_name,
u.email,
s.role,
s.appointed_by,
s.appointed_at
FROM server.staff s
JOIN auth.users u ON s.user_id = u.id
WHERE s.role = $1
ORDER BY s.appointed_at DESC
"#,
)
.bind(role)
.fetch_all(pool)
.await?;
Ok(staff)
}
/// Create a staff member (promote existing user).
pub async fn create_staff(
pool: &PgPool,
user_id: Uuid,
role: ServerRole,
appointed_by: Option<Uuid>,
) -> Result<StaffMember, AppError> {
let staff = sqlx::query_as::<_, StaffMember>(
r#"
WITH inserted AS (
INSERT INTO server.staff (user_id, role, appointed_by)
VALUES ($1, $2, $3)
RETURNING user_id, role, appointed_by, appointed_at
)
SELECT
i.user_id,
u.username,
u.display_name,
u.email,
i.role,
i.appointed_by,
i.appointed_at
FROM inserted i
JOIN auth.users u ON i.user_id = u.id
"#,
)
.bind(user_id)
.bind(role)
.bind(appointed_by)
.fetch_one(pool)
.await?;
Ok(staff)
}
/// Remove a staff member.
pub async fn delete_staff(pool: &PgPool, user_id: Uuid) -> Result<(), AppError> {
sqlx::query("DELETE FROM server.staff WHERE user_id = $1")
.bind(user_id)
.execute(pool)
.await?;
Ok(())
}

346
crates/chattyness-db/src/queries/owner/users.rs Normal file
View file

@ -0,0 +1,346 @@
//! User management database queries.
//!
//! These queries are used by the owner interface and require the chattyness_owner role.
use sqlx::PgPool;
use uuid::Uuid;
use crate::models::{
AccountStatus, CreateUserRequest, NewUserData, RealmRole, RealmSummary, UserDetail,
UserListItem, UserRealmMembership,
};
use chattyness_error::AppError;
use super::helpers::{generate_random_token, hash_password};
/// List all users with pagination.
pub async fn list_users(
pool: &PgPool,
limit: i64,
offset: i64,
) -> Result<Vec<UserListItem>, AppError> {
let users = sqlx::query_as::<_, UserListItem>(
r#"
SELECT
u.id,
u.username,
u.display_name,
u.email,
u.status,
u.reputation_tier,
s.role as staff_role,
u.created_at,
u.last_seen_at
FROM auth.users u
LEFT JOIN server.staff s ON u.id = s.user_id
ORDER BY u.created_at DESC
LIMIT $1 OFFSET $2
"#,
)
.bind(limit)
.bind(offset)
.fetch_all(pool)
.await?;
Ok(users)
}
/// Search users by username, email, or display_name.
pub async fn search_users(
pool: &PgPool,
query: &str,
limit: i64,
) -> Result<Vec<UserListItem>, AppError> {
let pattern = format!("%{}%", query);
let users = sqlx::query_as::<_, UserListItem>(
r#"
SELECT
u.id,
u.username,
u.display_name,
u.email,
u.status,
u.reputation_tier,
s.role as staff_role,
u.created_at,
u.last_seen_at
FROM auth.users u
LEFT JOIN server.staff s ON u.id = s.user_id
WHERE
u.username ILIKE $1
OR u.email ILIKE $1
OR u.display_name ILIKE $1
ORDER BY
CASE
WHEN u.username ILIKE $1 THEN 1
WHEN u.display_name ILIKE $1 THEN 2
ELSE 3
END,
u.username
LIMIT $2
"#,
)
.bind(&pattern)
.bind(limit)
.fetch_all(pool)
.await?;
Ok(users)
}
/// Get full user detail by ID.
pub async fn get_user_detail(pool: &PgPool, user_id: Uuid) -> Result<UserDetail, AppError> {
let user = sqlx::query_as::<_, UserDetail>(
r#"
SELECT
u.id,
u.username,
u.email,
u.display_name,
u.bio,
u.avatar_url,
u.reputation_tier,
u.status,
u.email_verified,
s.role as staff_role,
u.created_at,
u.updated_at,
u.last_seen_at
FROM auth.users u
LEFT JOIN server.staff s ON u.id = s.user_id
WHERE u.id = $1
"#,
)
.bind(user_id)
.fetch_one(pool)
.await?;
Ok(user)
}
/// Update a user's account status.
pub async fn update_user_status(
pool: &PgPool,
user_id: Uuid,
status: AccountStatus,
) -> Result<UserDetail, AppError> {
// First update the status
sqlx::query(
r#"
UPDATE auth.users
SET status = $1, updated_at = now()
WHERE id = $2
"#,
)
.bind(status)
.bind(user_id)
.execute(pool)
.await?;
// Then return the updated user detail
get_user_detail(pool, user_id).await
}
/// Create a new user account with a random temporary password.
/// Returns (user_id, plaintext_token) - the token should be shown to the server owner.
pub async fn create_user(pool: &PgPool, data: &NewUserData) -> Result<(Uuid, String), AppError> {
// Generate a random token as the temporary password
let token = generate_random_token();
let password_hash = hash_password(&token)?;
let user_id = sqlx::query_scalar::<_, Uuid>(
r#"
INSERT INTO auth.users (username, email, display_name, password_hash, force_pw_reset)
VALUES ($1, $2, $3, $4, true)
RETURNING id
"#,
)
.bind(&data.username)
.bind(&data.email)
.bind(&data.display_name)
.bind(&password_hash)
.fetch_one(pool)
.await?;
Ok((user_id, token))
}
/// Reset a user's password to a random token.
/// Returns the plaintext token (to show to the server owner).
pub async fn reset_user_password(pool: &PgPool, user_id: Uuid) -> Result<String, AppError> {
let token = generate_random_token();
let password_hash = hash_password(&token)?;
sqlx::query(
r#"
UPDATE auth.users
SET password_hash = $1, force_pw_reset = true, updated_at = now()
WHERE id = $2
"#,
)
.bind(&password_hash)
.bind(user_id)
.execute(pool)
.await?;
Ok(token)
}
/// Get all realm memberships for a user.
pub async fn get_user_realms(
pool: &PgPool,
user_id: Uuid,
) -> Result<Vec<UserRealmMembership>, AppError> {
let memberships = sqlx::query_as::<_, UserRealmMembership>(
r#"
SELECT
m.realm_id,
r.name as realm_name,
r.slug as realm_slug,
m.role,
m.nickname,
m.created_at as joined_at,
m.last_visited_at
FROM realm.memberships m
JOIN realm.realms r ON m.realm_id = r.id
WHERE m.user_id = $1
ORDER BY m.last_visited_at DESC NULLS LAST, m.created_at DESC
"#,
)
.bind(user_id)
.fetch_all(pool)
.await?;
Ok(memberships)
}
/// Add a user to a realm.
pub async fn add_user_to_realm(
pool: &PgPool,
user_id: Uuid,
realm_id: Uuid,
role: RealmRole,
) -> Result<(), AppError> {
sqlx::query(
r#"
INSERT INTO realm.memberships (realm_id, user_id, role)
VALUES ($1, $2, $3)
ON CONFLICT (realm_id, user_id) DO UPDATE SET role = $3
"#,
)
.bind(realm_id)
.bind(user_id)
.bind(role)
.execute(pool)
.await?;
Ok(())
}
/// Remove a user from a realm.
pub async fn remove_user_from_realm(
pool: &PgPool,
user_id: Uuid,
realm_id: Uuid,
) -> Result<(), AppError> {
sqlx::query(
r#"
DELETE FROM realm.memberships
WHERE realm_id = $1 AND user_id = $2
"#,
)
.bind(realm_id)
.bind(user_id)
.execute(pool)
.await?;
Ok(())
}
/// List all realms (for dropdown selection).
pub async fn list_all_realms(pool: &PgPool) -> Result<Vec<RealmSummary>, AppError> {
let realms = sqlx::query_as::<_, RealmSummary>(
r#"
SELECT
id,
name,
slug,
tagline,
privacy,
is_nsfw,
thumbnail_path,
member_count,
current_user_count
FROM realm.realms
ORDER BY name
"#,
)
.fetch_all(pool)
.await?;
Ok(realms)
}
/// Create a new user with optional staff role (atomically).
/// Returns (user_id, plaintext_token) - the token should be shown to the server owner.
pub async fn create_user_with_staff(
pool: &PgPool,
req: &CreateUserRequest,
) -> Result<(Uuid, String), AppError> {
// Generate a random token as the temporary password
let token = generate_random_token();
let password_hash = hash_password(&token)?;
// Start a transaction if we need to also create staff record
if let Some(staff_role) = req.staff_role {
let mut tx = pool.begin().await?;
// Create the user with force_pw_reset = true
let user_id = sqlx::query_scalar::<_, Uuid>(
r#"
INSERT INTO auth.users (username, email, display_name, password_hash, force_pw_reset)
VALUES ($1, $2, $3, $4, true)
RETURNING id
"#,
)
.bind(&req.username)
.bind(&req.email)
.bind(&req.display_name)
.bind(&password_hash)
.fetch_one(&mut *tx)
.await?;
// Create the staff record
sqlx::query(
r#"
INSERT INTO server.staff (user_id, role)
VALUES ($1, $2)
"#,
)
.bind(user_id)
.bind(staff_role)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok((user_id, token))
} else {
// Just create the user with force_pw_reset = true
let user_id = sqlx::query_scalar::<_, Uuid>(
r#"
INSERT INTO auth.users (username, email, display_name, password_hash, force_pw_reset)
VALUES ($1, $2, $3, $4, true)
RETURNING id
"#,
)
.bind(&req.username)
.bind(&req.email)
.bind(&req.display_name)
.bind(&password_hash)
.fetch_one(pool)
.await?;
Ok((user_id, token))
}
}

180
crates/chattyness-db/src/queries/props.rs Normal file
View file

@ -0,0 +1,180 @@
//! Props-related database queries.
use sqlx::PgExecutor;
use uuid::Uuid;
use crate::models::{CreateServerPropRequest, ServerProp, ServerPropSummary};
use chattyness_error::AppError;
/// List all server props.
pub async fn list_server_props<'e>(
executor: impl PgExecutor<'e>,
) -> Result<Vec<ServerPropSummary>, AppError> {
let props = sqlx::query_as::<_, ServerPropSummary>(
r#"
SELECT
id,
name,
slug,
asset_path,
default_layer,
is_active,
created_at
FROM server.props
ORDER BY name ASC
"#,
)
.fetch_all(executor)
.await?;
Ok(props)
}
/// Get a server prop by ID.
pub async fn get_server_prop_by_id<'e>(
executor: impl PgExecutor<'e>,
prop_id: Uuid,
) -> Result<Option<ServerProp>, AppError> {
let prop = sqlx::query_as::<_, ServerProp>(
r#"
SELECT
id,
name,
slug,
description,
tags,
asset_path,
thumbnail_path,
default_layer,
default_emotion,
default_position,
is_unique,
is_transferable,
is_portable,
is_active,
available_from,
available_until,
created_by,
created_at,
updated_at
FROM server.props
WHERE id = $1
"#,
)
.bind(prop_id)
.fetch_optional(executor)
.await?;
Ok(prop)
}
/// Check if a prop slug is available.
pub async fn is_prop_slug_available<'e>(
executor: impl PgExecutor<'e>,
slug: &str,
) -> Result<bool, AppError> {
let exists: (bool,) =
sqlx::query_as(r#"SELECT EXISTS(SELECT 1 FROM server.props WHERE slug = $1)"#)
.bind(slug)
.fetch_one(executor)
.await?;
Ok(!exists.0)
}
/// Create a new server prop.
pub async fn create_server_prop<'e>(
executor: impl PgExecutor<'e>,
req: &CreateServerPropRequest,
asset_path: &str,
created_by: Option<Uuid>,
) -> Result<ServerProp, AppError> {
let slug = req.slug_or_generate();
// Positioning: either content layer OR emotion layer OR neither (all NULL)
// Database constraint enforces mutual exclusivity
let (default_layer, default_emotion, default_position) =
if req.default_layer.is_some() {
// Content layer prop
(
req.default_layer.map(|l| l.to_string()),
None,
Some(req.default_position.unwrap_or(4)), // Default to center position
)
} else if req.default_emotion.is_some() {
// Emotion layer prop
(
None,
req.default_emotion.map(|e| e.to_string()),
Some(req.default_position.unwrap_or(4)), // Default to center position
)
} else {
// Non-avatar prop
(None, None, None)
};
let prop = sqlx::query_as::<_, ServerProp>(
r#"
INSERT INTO server.props (
name, slug, description, tags, asset_path,
default_layer, default_emotion, default_position,
created_by
)
VALUES (
$1, $2, $3, $4, $5,
$6::props.avatar_layer, $7::props.emotion_state, $8,
$9
)
RETURNING
id,
name,
slug,
description,
tags,
asset_path,
thumbnail_path,
default_layer,
default_emotion,
default_position,
is_unique,
is_transferable,
is_portable,
is_active,
available_from,
available_until,
created_by,
created_at,
updated_at
"#,
)
.bind(&req.name)
.bind(&slug)
.bind(&req.description)
.bind(&req.tags)
.bind(asset_path)
.bind(&default_layer)
.bind(&default_emotion)
.bind(default_position)
.bind(created_by)
.fetch_one(executor)
.await?;
Ok(prop)
}
/// Delete a server prop.
pub async fn delete_server_prop<'e>(
executor: impl PgExecutor<'e>,
prop_id: Uuid,
) -> Result<(), AppError> {
let result = sqlx::query(r#"DELETE FROM server.props WHERE id = $1"#)
.bind(prop_id)
.execute(executor)
.await?;
if result.rows_affected() == 0 {
return Err(AppError::NotFound("Prop not found".to_string()));
}
Ok(())
}

228
crates/chattyness-db/src/queries/realms.rs Normal file
View file

@ -0,0 +1,228 @@
//! Realm-related database queries.
use sqlx::{PgExecutor, PgPool};
use uuid::Uuid;
use crate::models::{CreateRealmRequest, Realm, RealmSummary};
use chattyness_error::AppError;
/// Create a new realm.
pub async fn create_realm(
pool: &PgPool,
owner_id: Uuid,
req: &CreateRealmRequest,
) -> Result<Realm, AppError> {
let privacy_str = req.privacy.as_str();
let realm = sqlx::query_as::<_, Realm>(
r#"
INSERT INTO realm.realms (
name, slug, description, tagline, owner_id,
privacy, is_nsfw, max_users, allow_guest_access, theme_color
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
RETURNING
id,
name,
slug,
description,
tagline,
owner_id,
privacy,
is_nsfw,
min_reputation_tier,
theme_color,
banner_image_path,
thumbnail_path,
max_users,
allow_guest_access,
default_scene_id,
member_count,
current_user_count,
created_at,
updated_at
"#,
)
.bind(&req.name)
.bind(&req.slug)
.bind(&req.description)
.bind(&req.tagline)
.bind(owner_id)
.bind(privacy_str)
.bind(req.is_nsfw)
.bind(req.max_users)
.bind(req.allow_guest_access)
.bind(&req.theme_color)
.fetch_one(pool)
.await?;
Ok(realm)
}
/// Check if a realm slug is available.
pub async fn is_slug_available(pool: &PgPool, slug: &str) -> Result<bool, AppError> {
let exists: (bool,) = sqlx::query_as(
r#"SELECT EXISTS(SELECT 1 FROM realm.realms WHERE slug = $1)"#,
)
.bind(slug)
.fetch_one(pool)
.await?;
Ok(!exists.0)
}
/// Get a realm by its slug.
pub async fn get_realm_by_slug<'e>(
executor: impl PgExecutor<'e>,
slug: &str,
) -> Result<Option<Realm>, AppError> {
let realm = sqlx::query_as::<_, Realm>(
r#"
SELECT
id,
name,
slug,
description,
tagline,
owner_id,
privacy,
is_nsfw,
min_reputation_tier,
theme_color,
banner_image_path,
thumbnail_path,
max_users,
allow_guest_access,
default_scene_id,
member_count,
current_user_count,
created_at,
updated_at
FROM realm.realms
WHERE slug = $1
"#,
)
.bind(slug)
.fetch_optional(executor)
.await?;
Ok(realm)
}
/// Get a realm by its ID.
pub async fn get_realm_by_id(pool: &PgPool, id: Uuid) -> Result<Option<Realm>, AppError> {
let realm = sqlx::query_as::<_, Realm>(
r#"
SELECT
id,
name,
slug,
description,
tagline,
owner_id,
privacy,
is_nsfw,
min_reputation_tier,
theme_color,
banner_image_path,
thumbnail_path,
max_users,
allow_guest_access,
default_scene_id,
member_count,
current_user_count,
created_at,
updated_at
FROM realm.realms
WHERE id = $1
"#,
)
.bind(id)
.fetch_optional(pool)
.await?;
Ok(realm)
}
/// List public realms.
pub async fn list_public_realms(
pool: &PgPool,
include_nsfw: bool,
limit: i64,
offset: i64,
) -> Result<Vec<RealmSummary>, AppError> {
let realms = if include_nsfw {
sqlx::query_as::<_, RealmSummary>(
r#"
SELECT
id,
name,
slug,
tagline,
privacy,
is_nsfw,
thumbnail_path,
member_count,
current_user_count
FROM realm.realms
WHERE privacy = 'public'
ORDER BY current_user_count DESC, member_count DESC
LIMIT $1 OFFSET $2
"#,
)
.bind(limit)
.bind(offset)
.fetch_all(pool)
.await?
} else {
sqlx::query_as::<_, RealmSummary>(
r#"
SELECT
id,
name,
slug,
tagline,
privacy,
is_nsfw,
thumbnail_path,
member_count,
current_user_count
FROM realm.realms
WHERE privacy = 'public' AND is_nsfw = false
ORDER BY current_user_count DESC, member_count DESC
LIMIT $1 OFFSET $2
"#,
)
.bind(limit)
.bind(offset)
.fetch_all(pool)
.await?
};
Ok(realms)
}
/// Get realms owned by a user.
pub async fn get_user_realms(pool: &PgPool, user_id: Uuid) -> Result<Vec<RealmSummary>, AppError> {
let realms = sqlx::query_as::<_, RealmSummary>(
r#"
SELECT
id,
name,
slug,
tagline,
privacy,
is_nsfw,
thumbnail_path,
member_count,
current_user_count
FROM realm.realms
WHERE owner_id = $1
ORDER BY created_at DESC
"#,
)
.bind(user_id)
.fetch_all(pool)
.await?;
Ok(realms)
}

442
crates/chattyness-db/src/queries/scenes.rs Normal file
View file

@ -0,0 +1,442 @@
//! Scene-related database queries.
use sqlx::PgExecutor;
use uuid::Uuid;
use crate::models::{CreateSceneRequest, Scene, SceneSummary, UpdateSceneRequest};
use chattyness_error::AppError;
/// List all scenes for a realm.
pub async fn list_scenes_for_realm<'e>(
executor: impl PgExecutor<'e>,
realm_id: Uuid,
) -> Result<Vec<SceneSummary>, AppError> {
let scenes = sqlx::query_as::<_, SceneSummary>(
r#"
SELECT
id,
name,
slug,
sort_order,
is_entry_point,
is_hidden,
background_color,
background_image_path
FROM realm.scenes
WHERE realm_id = $1
ORDER BY sort_order ASC, name ASC
"#,
)
.bind(realm_id)
.fetch_all(executor)
.await?;
Ok(scenes)
}
/// Get a scene by its ID.
pub async fn get_scene_by_id<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
) -> Result<Option<Scene>, AppError> {
let scene = sqlx::query_as::<_, Scene>(
r#"
SELECT
id,
realm_id,
name,
slug,
description,
background_image_path,
background_color,
ST_AsText(bounds) as bounds_wkt,
dimension_mode,
ambient_audio_id,
ambient_volume,
sort_order,
is_entry_point,
is_hidden,
created_at,
updated_at
FROM realm.scenes
WHERE id = $1
"#,
)
.bind(scene_id)
.fetch_optional(executor)
.await?;
Ok(scene)
}
/// Get a scene by realm ID and slug.
pub async fn get_scene_by_slug<'e>(
executor: impl PgExecutor<'e>,
realm_id: Uuid,
slug: &str,
) -> Result<Option<Scene>, AppError> {
let scene = sqlx::query_as::<_, Scene>(
r#"
SELECT
id,
realm_id,
name,
slug,
description,
background_image_path,
background_color,
ST_AsText(bounds) as bounds_wkt,
dimension_mode,
ambient_audio_id,
ambient_volume,
sort_order,
is_entry_point,
is_hidden,
created_at,
updated_at
FROM realm.scenes
WHERE realm_id = $1 AND slug = $2
"#,
)
.bind(realm_id)
.bind(slug)
.fetch_optional(executor)
.await?;
Ok(scene)
}
/// Check if a scene slug is available within a realm.
pub async fn is_scene_slug_available<'e>(
executor: impl PgExecutor<'e>,
realm_id: Uuid,
slug: &str,
) -> Result<bool, AppError> {
let exists: (bool,) =
sqlx::query_as(r#"SELECT EXISTS(SELECT 1 FROM realm.scenes WHERE realm_id = $1 AND slug = $2)"#)
.bind(realm_id)
.bind(slug)
.fetch_one(executor)
.await?;
Ok(!exists.0)
}
/// Create a new scene.
pub async fn create_scene<'e>(
executor: impl PgExecutor<'e>,
realm_id: Uuid,
req: &CreateSceneRequest,
) -> Result<Scene, AppError> {
let bounds_wkt = req
.bounds_wkt
.as_deref()
.unwrap_or("POLYGON((0 0, 800 0, 800 600, 0 600, 0 0))");
let dimension_mode = req.dimension_mode.unwrap_or_default().to_string();
let sort_order = req.sort_order.unwrap_or(0);
let is_entry_point = req.is_entry_point.unwrap_or(false);
let is_hidden = req.is_hidden.unwrap_or(false);
let scene = sqlx::query_as::<_, Scene>(
r#"
INSERT INTO realm.scenes (
realm_id, name, slug, description,
background_image_path, background_color,
bounds, dimension_mode,
sort_order, is_entry_point, is_hidden
)
VALUES (
$1, $2, $3, $4,
$5, $6,
ST_GeomFromText($7, 0), $8::realm.dimension_mode,
$9, $10, $11
)
RETURNING
id,
realm_id,
name,
slug,
description,
background_image_path,
background_color,
ST_AsText(bounds) as bounds_wkt,
dimension_mode,
ambient_audio_id,
ambient_volume,
sort_order,
is_entry_point,
is_hidden,
created_at,
updated_at
"#,
)
.bind(realm_id)
.bind(&req.name)
.bind(&req.slug)
.bind(&req.description)
.bind(&req.background_image_path)
.bind(&req.background_color)
.bind(bounds_wkt)
.bind(&dimension_mode)
.bind(sort_order)
.bind(is_entry_point)
.bind(is_hidden)
.fetch_one(executor)
.await?;
Ok(scene)
}
/// Create a new scene with a specific ID.
///
/// This is used when we need to know the scene ID before creating it
/// (e.g., for storing background images in the correct path).
pub async fn create_scene_with_id<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
realm_id: Uuid,
req: &CreateSceneRequest,
) -> Result<Scene, AppError> {
let bounds_wkt = req
.bounds_wkt
.as_deref()
.unwrap_or("POLYGON((0 0, 800 0, 800 600, 0 600, 0 0))");
let dimension_mode = req.dimension_mode.unwrap_or_default().to_string();
let sort_order = req.sort_order.unwrap_or(0);
let is_entry_point = req.is_entry_point.unwrap_or(false);
let is_hidden = req.is_hidden.unwrap_or(false);
let scene = sqlx::query_as::<_, Scene>(
r#"
INSERT INTO realm.scenes (
id, realm_id, name, slug, description,
background_image_path, background_color,
bounds, dimension_mode,
sort_order, is_entry_point, is_hidden
)
VALUES (
$1, $2, $3, $4, $5,
$6, $7,
ST_GeomFromText($8, 0), $9::realm.dimension_mode,
$10, $11, $12
)
RETURNING
id,
realm_id,
name,
slug,
description,
background_image_path,
background_color,
ST_AsText(bounds) as bounds_wkt,
dimension_mode,
ambient_audio_id,
ambient_volume,
sort_order,
is_entry_point,
is_hidden,
created_at,
updated_at
"#,
)
.bind(scene_id)
.bind(realm_id)
.bind(&req.name)
.bind(&req.slug)
.bind(&req.description)
.bind(&req.background_image_path)
.bind(&req.background_color)
.bind(bounds_wkt)
.bind(&dimension_mode)
.bind(sort_order)
.bind(is_entry_point)
.bind(is_hidden)
.fetch_one(executor)
.await?;
Ok(scene)
}
/// Update a scene.
pub async fn update_scene<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
req: &UpdateSceneRequest,
) -> Result<Scene, AppError> {
// Build dynamic update query
let mut set_clauses = Vec::new();
let mut param_idx = 2; // $1 is scene_id
if req.name.is_some() {
set_clauses.push(format!("name = ${}", param_idx));
param_idx += 1;
}
if req.description.is_some() {
set_clauses.push(format!("description = ${}", param_idx));
param_idx += 1;
}
if req.background_image_path.is_some() {
set_clauses.push(format!("background_image_path = ${}", param_idx));
param_idx += 1;
}
if req.background_color.is_some() {
set_clauses.push(format!("background_color = ${}", param_idx));
param_idx += 1;
}
if req.bounds_wkt.is_some() {
set_clauses.push(format!("bounds = ST_GeomFromText(${}, 0)", param_idx));
param_idx += 1;
}
if req.dimension_mode.is_some() {
set_clauses.push(format!("dimension_mode = ${}::realm.dimension_mode", param_idx));
param_idx += 1;
}
if req.sort_order.is_some() {
set_clauses.push(format!("sort_order = ${}", param_idx));
param_idx += 1;
}
if req.is_entry_point.is_some() {
set_clauses.push(format!("is_entry_point = ${}", param_idx));
param_idx += 1;
}
if req.is_hidden.is_some() {
set_clauses.push(format!("is_hidden = ${}", param_idx));
}
// If no updates, just return the current scene
let query = if set_clauses.is_empty() {
r#"SELECT id, realm_id, name, slug, description, background_image_path,
background_color, ST_AsText(bounds) as bounds_wkt, dimension_mode,
ambient_audio_id, ambient_volume, sort_order, is_entry_point,
is_hidden, created_at, updated_at
FROM realm.scenes WHERE id = $1"#.to_string()
} else {
set_clauses.push("updated_at = now()".to_string());
format!(
r#"UPDATE realm.scenes SET {}
WHERE id = $1
RETURNING id, realm_id, name, slug, description, background_image_path,
background_color, ST_AsText(bounds) as bounds_wkt, dimension_mode,
ambient_audio_id, ambient_volume, sort_order, is_entry_point,
is_hidden, created_at, updated_at"#,
set_clauses.join(", ")
)
};
let mut query_builder = sqlx::query_as::<_, Scene>(&query).bind(scene_id);
if let Some(ref name) = req.name {
query_builder = query_builder.bind(name);
}
if let Some(ref description) = req.description {
query_builder = query_builder.bind(description);
}
if let Some(ref background_image_path) = req.background_image_path {
query_builder = query_builder.bind(background_image_path);
}
if let Some(ref background_color) = req.background_color {
query_builder = query_builder.bind(background_color);
}
if let Some(ref bounds_wkt) = req.bounds_wkt {
query_builder = query_builder.bind(bounds_wkt);
}
if let Some(ref dimension_mode) = req.dimension_mode {
query_builder = query_builder.bind(dimension_mode.to_string());
}
if let Some(sort_order) = req.sort_order {
query_builder = query_builder.bind(sort_order);
}
if let Some(is_entry_point) = req.is_entry_point {
query_builder = query_builder.bind(is_entry_point);
}
if let Some(is_hidden) = req.is_hidden {
query_builder = query_builder.bind(is_hidden);
}
let scene = query_builder
.fetch_optional(executor)
.await?
.ok_or_else(|| AppError::NotFound("Scene not found".to_string()))?;
Ok(scene)
}
/// Delete a scene.
pub async fn delete_scene<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
) -> Result<(), AppError> {
let result = sqlx::query(r#"DELETE FROM realm.scenes WHERE id = $1"#)
.bind(scene_id)
.execute(executor)
.await?;
if result.rows_affected() == 0 {
return Err(AppError::NotFound("Scene not found".to_string()));
}
Ok(())
}
/// Get the next sort order for a new scene in a realm.
pub async fn get_next_sort_order<'e>(
executor: impl PgExecutor<'e>,
realm_id: Uuid,
) -> Result<i32, AppError> {
let result: (Option<i32>,) = sqlx::query_as(
r#"SELECT MAX(sort_order) FROM realm.scenes WHERE realm_id = $1"#,
)
.bind(realm_id)
.fetch_one(executor)
.await?;
Ok(result.0.unwrap_or(0) + 1)
}
/// Get the entry scene for a realm.
///
/// Returns the scene in this priority order:
/// 1. The scene specified by `default_scene_id` on the realm (if provided and exists)
/// 2. The first scene marked as `is_entry_point`
/// 3. The first scene by sort_order
pub async fn get_entry_scene_for_realm<'e>(
executor: impl PgExecutor<'e>,
realm_id: Uuid,
default_scene_id: Option<Uuid>,
) -> Result<Option<Scene>, AppError> {
// Use a single query that handles the priority in SQL
let scene = sqlx::query_as::<_, Scene>(
r#"
SELECT
id,
realm_id,
name,
slug,
description,
background_image_path,
background_color,
ST_AsText(bounds) as bounds_wkt,
dimension_mode,
ambient_audio_id,
ambient_volume,
sort_order,
is_entry_point,
is_hidden,
created_at,
updated_at
FROM realm.scenes
WHERE realm_id = $1 AND is_hidden = false
ORDER BY
CASE WHEN id = $2 THEN 0 ELSE 1 END,
is_entry_point DESC,
sort_order ASC
LIMIT 1
"#,
)
.bind(realm_id)
.bind(default_scene_id)
.fetch_optional(executor)
.await?;
Ok(scene)
}

324
crates/chattyness-db/src/queries/spots.rs Normal file
View file

@ -0,0 +1,324 @@
//! Spot-related database queries.
use sqlx::PgExecutor;
use uuid::Uuid;
use crate::models::{CreateSpotRequest, Spot, SpotSummary, UpdateSpotRequest};
use chattyness_error::AppError;
/// List all spots for a scene.
pub async fn list_spots_for_scene<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
) -> Result<Vec<SpotSummary>, AppError> {
let spots = sqlx::query_as::<_, SpotSummary>(
r#"
SELECT
id,
name,
slug,
spot_type,
ST_AsText(region) as region_wkt,
sort_order,
is_visible,
is_active
FROM realm.spots
WHERE scene_id = $1
ORDER BY sort_order ASC, name ASC NULLS LAST
"#,
)
.bind(scene_id)
.fetch_all(executor)
.await?;
Ok(spots)
}
/// Get a spot by its ID.
pub async fn get_spot_by_id<'e>(
executor: impl PgExecutor<'e>,
spot_id: Uuid,
) -> Result<Option<Spot>, AppError> {
let spot = sqlx::query_as::<_, Spot>(
r#"
SELECT
id,
scene_id,
name,
slug,
ST_AsText(region) as region_wkt,
spot_type,
destination_scene_id,
ST_AsText(destination_position) as destination_position_wkt,
current_state,
sort_order,
is_visible,
is_active,
created_at,
updated_at
FROM realm.spots
WHERE id = $1
"#,
)
.bind(spot_id)
.fetch_optional(executor)
.await?;
Ok(spot)
}
/// Get a spot by scene ID and slug.
pub async fn get_spot_by_slug<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
slug: &str,
) -> Result<Option<Spot>, AppError> {
let spot = sqlx::query_as::<_, Spot>(
r#"
SELECT
id,
scene_id,
name,
slug,
ST_AsText(region) as region_wkt,
spot_type,
destination_scene_id,
ST_AsText(destination_position) as destination_position_wkt,
current_state,
sort_order,
is_visible,
is_active,
created_at,
updated_at
FROM realm.spots
WHERE scene_id = $1 AND slug = $2
"#,
)
.bind(scene_id)
.bind(slug)
.fetch_optional(executor)
.await?;
Ok(spot)
}
/// Check if a spot slug is available within a scene.
pub async fn is_spot_slug_available<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
slug: &str,
) -> Result<bool, AppError> {
let exists: (bool,) =
sqlx::query_as(r#"SELECT EXISTS(SELECT 1 FROM realm.spots WHERE scene_id = $1 AND slug = $2)"#)
.bind(scene_id)
.bind(slug)
.fetch_one(executor)
.await?;
Ok(!exists.0)
}
/// Create a new spot.
pub async fn create_spot<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
req: &CreateSpotRequest,
) -> Result<Spot, AppError> {
let spot_type = req.spot_type.unwrap_or_default().to_string();
let sort_order = req.sort_order.unwrap_or(0);
let is_visible = req.is_visible.unwrap_or(true);
let is_active = req.is_active.unwrap_or(true);
let spot = sqlx::query_as::<_, Spot>(
r#"
INSERT INTO realm.spots (
scene_id, name, slug,
region, spot_type,
destination_scene_id, destination_position,
sort_order, is_visible, is_active
)
VALUES (
$1, $2, $3,
ST_GeomFromText($4, 0), $5::realm.spot_type,
$6, CASE WHEN $7 IS NOT NULL THEN ST_GeomFromText($7, 0) ELSE NULL END,
$8, $9, $10
)
RETURNING
id,
scene_id,
name,
slug,
ST_AsText(region) as region_wkt,
spot_type,
destination_scene_id,
ST_AsText(destination_position) as destination_position_wkt,
current_state,
sort_order,
is_visible,
is_active,
created_at,
updated_at
"#,
)
.bind(scene_id)
.bind(&req.name)
.bind(&req.slug)
.bind(&req.region_wkt)
.bind(&spot_type)
.bind(req.destination_scene_id)
.bind(&req.destination_position_wkt)
.bind(sort_order)
.bind(is_visible)
.bind(is_active)
.fetch_one(executor)
.await?;
Ok(spot)
}
/// Update a spot.
pub async fn update_spot<'e>(
executor: impl PgExecutor<'e>,
spot_id: Uuid,
req: &UpdateSpotRequest,
) -> Result<Spot, AppError> {
// Build dynamic update query
let mut set_clauses = Vec::new();
let mut param_idx = 2; // $1 is spot_id
if req.name.is_some() {
set_clauses.push(format!("name = ${}", param_idx));
param_idx += 1;
}
if req.slug.is_some() {
set_clauses.push(format!("slug = ${}", param_idx));
param_idx += 1;
}
if req.region_wkt.is_some() {
set_clauses.push(format!("region = ST_GeomFromText(${}, 0)", param_idx));
param_idx += 1;
}
if req.spot_type.is_some() {
set_clauses.push(format!("spot_type = ${}::realm.spot_type", param_idx));
param_idx += 1;
}
if req.destination_scene_id.is_some() {
set_clauses.push(format!("destination_scene_id = ${}", param_idx));
param_idx += 1;
}
if req.destination_position_wkt.is_some() {
set_clauses.push(format!(
"destination_position = CASE WHEN ${} IS NOT NULL THEN ST_GeomFromText(${}, 0) ELSE NULL END",
param_idx, param_idx
));
param_idx += 1;
}
if req.current_state.is_some() {
set_clauses.push(format!("current_state = ${}", param_idx));
param_idx += 1;
}
if req.sort_order.is_some() {
set_clauses.push(format!("sort_order = ${}", param_idx));
param_idx += 1;
}
if req.is_visible.is_some() {
set_clauses.push(format!("is_visible = ${}", param_idx));
param_idx += 1;
}
if req.is_active.is_some() {
set_clauses.push(format!("is_active = ${}", param_idx));
}
// If no updates, just return the current spot via SELECT
let query = if set_clauses.is_empty() {
r#"SELECT id, scene_id, name, slug, ST_AsText(region) as region_wkt,
spot_type, destination_scene_id,
ST_AsText(destination_position) as destination_position_wkt,
current_state, sort_order, is_visible, is_active,
created_at, updated_at
FROM realm.spots WHERE id = $1"#.to_string()
} else {
set_clauses.push("updated_at = now()".to_string());
format!(
r#"UPDATE realm.spots SET {}
WHERE id = $1
RETURNING id, scene_id, name, slug, ST_AsText(region) as region_wkt,
spot_type, destination_scene_id,
ST_AsText(destination_position) as destination_position_wkt,
current_state, sort_order, is_visible, is_active,
created_at, updated_at"#,
set_clauses.join(", ")
)
};
let mut query_builder = sqlx::query_as::<_, Spot>(&query).bind(spot_id);
if let Some(ref name) = req.name {
query_builder = query_builder.bind(name);
}
if let Some(ref slug) = req.slug {
query_builder = query_builder.bind(slug);
}
if let Some(ref region_wkt) = req.region_wkt {
query_builder = query_builder.bind(region_wkt);
}
if let Some(ref spot_type) = req.spot_type {
query_builder = query_builder.bind(spot_type.to_string());
}
if let Some(destination_scene_id) = req.destination_scene_id {
query_builder = query_builder.bind(destination_scene_id);
}
if let Some(ref destination_position_wkt) = req.destination_position_wkt {
query_builder = query_builder.bind(destination_position_wkt);
}
if let Some(current_state) = req.current_state {
query_builder = query_builder.bind(current_state);
}
if let Some(sort_order) = req.sort_order {
query_builder = query_builder.bind(sort_order);
}
if let Some(is_visible) = req.is_visible {
query_builder = query_builder.bind(is_visible);
}
if let Some(is_active) = req.is_active {
query_builder = query_builder.bind(is_active);
}
let spot = query_builder
.fetch_optional(executor)
.await?
.ok_or_else(|| AppError::NotFound("Spot not found".to_string()))?;
Ok(spot)
}
/// Delete a spot.
pub async fn delete_spot<'e>(
executor: impl PgExecutor<'e>,
spot_id: Uuid,
) -> Result<(), AppError> {
let result = sqlx::query(r#"DELETE FROM realm.spots WHERE id = $1"#)
.bind(spot_id)
.execute(executor)
.await?;
if result.rows_affected() == 0 {
return Err(AppError::NotFound("Spot not found".to_string()));
}
Ok(())
}
/// Get the next sort order for a new spot in a scene.
pub async fn get_next_sort_order<'e>(
executor: impl PgExecutor<'e>,
scene_id: Uuid,
) -> Result<i32, AppError> {
let result: (Option<i32>,) =
sqlx::query_as(r#"SELECT MAX(sort_order) FROM realm.spots WHERE scene_id = $1"#)
.bind(scene_id)
.fetch_one(executor)
.await?;
Ok(result.0.unwrap_or(0) + 1)
}

493
crates/chattyness-db/src/queries/users.rs Normal file
View file

@ -0,0 +1,493 @@
//! User-related database queries.
use sqlx::PgPool;
use uuid::Uuid;
use crate::models::{StaffMember, User, UserWithAuth};
use chattyness_error::AppError;
/// Get a user by their ID.
pub async fn get_user_by_id(pool: &PgPool, id: Uuid) -> Result<Option<User>, AppError> {
let user = sqlx::query_as::<_, User>(
r#"
SELECT
id,
username,
email,
display_name,
bio,
avatar_url,
reputation_tier,
status,
email_verified,
created_at,
updated_at
FROM auth.users
WHERE id = $1 AND status = 'active'
"#,
)
.bind(id)
.fetch_optional(pool)
.await?;
Ok(user)
}
/// Get a user by their username.
pub async fn get_user_by_username(pool: &PgPool, username: &str) -> Result<Option<User>, AppError> {
let user = sqlx::query_as::<_, User>(
r#"
SELECT
id,
username,
email,
display_name,
bio,
avatar_url,
reputation_tier,
status,
email_verified,
created_at,
updated_at
FROM auth.users
WHERE username = $1 AND status = 'active'
"#,
)
.bind(username)
.fetch_optional(pool)
.await?;
Ok(user)
}
/// Get a user by their email.
pub async fn get_user_by_email(pool: &PgPool, email: &str) -> Result<Option<User>, AppError> {
let user = sqlx::query_as::<_, User>(
r#"
SELECT
id,
username,
email,
display_name,
bio,
avatar_url,
reputation_tier,
status,
email_verified,
created_at,
updated_at
FROM auth.users
WHERE lower(email) = lower($1) AND status = 'active'
"#,
)
.bind(email)
.fetch_optional(pool)
.await?;
Ok(user)
}
/// Row type for password verification query.
#[derive(sqlx::FromRow)]
struct PasswordRow {
id: Uuid,
password_hash: Option<String>,
}
/// Verify a user's password and return the user if valid.
pub async fn verify_password(
pool: &PgPool,
username: &str,
password: &str,
) -> Result<Option<User>, AppError> {
// First get the password hash
let row = sqlx::query_as::<_, PasswordRow>(
r#"
SELECT id, password_hash
FROM auth.users
WHERE username = $1 AND status = 'active' AND auth_provider = 'local'
"#,
)
.bind(username)
.fetch_optional(pool)
.await?;
let Some(row) = row else {
return Ok(None);
};
let Some(ref password_hash) = row.password_hash else {
return Ok(None);
};
// Verify the password using argon2
use argon2::{Argon2, PasswordHash, PasswordVerifier};
let parsed_hash = PasswordHash::new(password_hash)
.map_err(|e| AppError::Internal(format!("Invalid password hash: {}", e)))?;
if Argon2::default()
.verify_password(password.as_bytes(), &parsed_hash)
.is_err()
{
return Ok(None);
}
// Password is valid, fetch the full user
get_user_by_id(pool, row.id).await
}
/// Create a new user session.
pub async fn create_session(
pool: &PgPool,
user_id: Uuid,
token_hash: &str,
user_agent: Option<&str>,
ip_address: Option<std::net::IpAddr>,
expires_at: chrono::DateTime<chrono::Utc>,
) -> Result<Uuid, AppError> {
let ip_str = ip_address.map(|ip| ip.to_string());
let session_id: (Uuid,) = sqlx::query_as(
r#"
INSERT INTO auth.sessions (user_id, token_hash, user_agent, ip_address, expires_at)
VALUES ($1, $2, $3, $4::inet, $5)
RETURNING id
"#,
)
.bind(user_id)
.bind(token_hash)
.bind(user_agent)
.bind(ip_str)
.bind(expires_at)
.fetch_one(pool)
.await?;
Ok(session_id.0)
}
/// Get a user by their session token hash.
pub async fn get_user_by_session(
pool: &PgPool,
token_hash: &str,
) -> Result<Option<User>, AppError> {
let user = sqlx::query_as::<_, User>(
r#"
SELECT
u.id,
u.username,
u.email,
u.display_name,
u.bio,
u.avatar_url,
u.reputation_tier,
u.status,
u.email_verified,
u.created_at,
u.updated_at
FROM auth.users u
JOIN auth.sessions s ON u.id = s.user_id
WHERE s.token_hash = $1
AND s.expires_at > now()
AND u.status = 'active'
"#,
)
.bind(token_hash)
.fetch_optional(pool)
.await?;
// Update last activity
if user.is_some() {
sqlx::query("UPDATE auth.sessions SET last_activity_at = now() WHERE token_hash = $1")
.bind(token_hash)
.execute(pool)
.await?;
}
Ok(user)
}
/// Delete a session by token hash.
pub async fn delete_session(pool: &PgPool, token_hash: &str) -> Result<(), AppError> {
sqlx::query("DELETE FROM auth.sessions WHERE token_hash = $1")
.bind(token_hash)
.execute(pool)
.await?;
Ok(())
}
/// Update a user's last seen timestamp.
pub async fn update_last_seen(pool: &PgPool, user_id: Uuid) -> Result<(), AppError> {
sqlx::query("UPDATE auth.users SET last_seen_at = now() WHERE id = $1")
.bind(user_id)
.execute(pool)
.await?;
Ok(())
}
/// Get a user with auth fields for login verification.
pub async fn get_user_with_auth(
pool: &PgPool,
username: &str,
) -> Result<Option<UserWithAuth>, AppError> {
let user = sqlx::query_as::<_, UserWithAuth>(
r#"
SELECT
id,
username,
email,
display_name,
avatar_url,
status,
force_pw_reset,
password_hash
FROM auth.users
WHERE username = $1 AND auth_provider = 'local'
"#,
)
.bind(username)
.fetch_optional(pool)
.await?;
Ok(user)
}
/// Verify password and return user with auth info.
pub async fn verify_password_with_reset_flag(
pool: &PgPool,
username: &str,
password: &str,
) -> Result<Option<UserWithAuth>, AppError> {
let user = get_user_with_auth(pool, username).await?;
let Some(user) = user else {
return Ok(None);
};
let Some(ref password_hash) = user.password_hash else {
return Ok(None);
};
// Verify the password using argon2
use argon2::{Argon2, PasswordHash, PasswordVerifier};
let parsed_hash = PasswordHash::new(password_hash)
.map_err(|e| AppError::Internal(format!("Invalid password hash: {}", e)))?;
if Argon2::default()
.verify_password(password.as_bytes(), &parsed_hash)
.is_err()
{
return Ok(None);
}
Ok(Some(user))
}
/// Update a user's password.
pub async fn update_password(
pool: &PgPool,
user_id: Uuid,
new_password: &str,
) -> Result<(), AppError> {
use argon2::{
password_hash::{rand_core::OsRng, SaltString},
Argon2, PasswordHasher,
};
let salt = SaltString::generate(&mut OsRng);
let argon2 = Argon2::default();
let password_hash = argon2
.hash_password(new_password.as_bytes(), &salt)
.map_err(|e| AppError::Internal(format!("Failed to hash password: {}", e)))?
.to_string();
sqlx::query(
r#"
UPDATE auth.users
SET password_hash = $1, force_pw_reset = false, updated_at = now()
WHERE id = $2
"#,
)
.bind(&password_hash)
.bind(user_id)
.execute(pool)
.await?;
Ok(())
}
/// Update a user's password using a connection (for RLS support).
pub async fn update_password_conn(
conn: &mut sqlx::PgConnection,
user_id: Uuid,
new_password: &str,
) -> Result<(), AppError> {
use argon2::{
password_hash::{rand_core::OsRng, SaltString},
Argon2, PasswordHasher,
};
let salt = SaltString::generate(&mut OsRng);
let argon2 = Argon2::default();
let password_hash = argon2
.hash_password(new_password.as_bytes(), &salt)
.map_err(|e| AppError::Internal(format!("Failed to hash password: {}", e)))?
.to_string();
sqlx::query(
r#"
UPDATE auth.users
SET password_hash = $1, force_pw_reset = false, updated_at = now()
WHERE id = $2
"#,
)
.bind(&password_hash)
.bind(user_id)
.execute(conn)
.await?;
Ok(())
}
/// Clear the force_pw_reset flag for a user.
pub async fn clear_force_pw_reset(pool: &PgPool, user_id: Uuid) -> Result<(), AppError> {
sqlx::query(
r#"
UPDATE auth.users
SET force_pw_reset = false, updated_at = now()
WHERE id = $1
"#,
)
.bind(user_id)
.execute(pool)
.await?;
Ok(())
}
/// Check if a username already exists.
pub async fn username_exists(pool: &PgPool, username: &str) -> Result<bool, AppError> {
let (exists,): (bool,) = sqlx::query_as(
r#"
SELECT EXISTS(SELECT 1 FROM auth.users WHERE username = $1)
"#,
)
.bind(username)
.fetch_one(pool)
.await?;
Ok(exists)
}
/// Check if an email already exists.
pub async fn email_exists(pool: &PgPool, email: &str) -> Result<bool, AppError> {
let (exists,): (bool,) = sqlx::query_as(
r#"
SELECT EXISTS(SELECT 1 FROM auth.users WHERE lower(email) = lower($1))
"#,
)
.bind(email)
.fetch_one(pool)
.await?;
Ok(exists)
}
/// Create a new user with hashed password.
pub async fn create_user(
pool: &PgPool,
username: &str,
email: Option<&str>,
display_name: &str,
password: &str,
) -> Result<Uuid, AppError> {
use argon2::{
password_hash::{rand_core::OsRng, SaltString},
Argon2, PasswordHasher,
};
let salt = SaltString::generate(&mut OsRng);
let argon2 = Argon2::default();
let password_hash = argon2
.hash_password(password.as_bytes(), &salt)
.map_err(|e| AppError::Internal(format!("Failed to hash password: {}", e)))?
.to_string();
let (user_id,): (Uuid,) = sqlx::query_as(
r#"
INSERT INTO auth.users (username, email, password_hash, display_name, auth_provider, status)
VALUES ($1, $2, $3, $4, 'local', 'active')
RETURNING id
"#,
)
.bind(username)
.bind(email)
.bind(&password_hash)
.bind(display_name)
.fetch_one(pool)
.await?;
Ok(user_id)
}
/// Create a new user using a connection (for RLS support).
pub async fn create_user_conn(
conn: &mut sqlx::PgConnection,
username: &str,
email: Option<&str>,
display_name: &str,
password: &str,
) -> Result<Uuid, AppError> {
use argon2::{
password_hash::{rand_core::OsRng, SaltString},
Argon2, PasswordHasher,
};
let salt = SaltString::generate(&mut OsRng);
let argon2 = Argon2::default();
let password_hash = argon2
.hash_password(password.as_bytes(), &salt)
.map_err(|e| AppError::Internal(format!("Failed to hash password: {}", e)))?
.to_string();
let (user_id,): (Uuid,) = sqlx::query_as(
r#"
INSERT INTO auth.users (username, email, password_hash, display_name, auth_provider, status)
VALUES ($1, $2, $3, $4, 'local', 'active')
RETURNING id
"#,
)
.bind(username)
.bind(email)
.bind(&password_hash)
.bind(display_name)
.fetch_one(conn)
.await?;
Ok(user_id)
}
/// Get a staff member by their user ID.
///
/// Returns the staff member with their user info joined.
pub async fn get_staff_member(pool: &PgPool, user_id: Uuid) -> Result<Option<StaffMember>, AppError> {
let staff = sqlx::query_as::<_, StaffMember>(
r#"
SELECT
s.user_id,
u.username,
u.display_name,
u.email,
s.role,
s.appointed_by,
s.appointed_at
FROM server.staff s
JOIN auth.users u ON s.user_id = u.id
WHERE s.user_id = $1 AND u.status = 'active'
"#,
)
.bind(user_id)
.fetch_optional(pool)
.await?;
Ok(staff)
}

92
crates/chattyness-db/src/ws_messages.rs Normal file
View file

@ -0,0 +1,92 @@
//! WebSocket message protocol for channel presence.
//!
//! Shared message types used by both server and WASM client.
use serde::{Deserialize, Serialize};
use uuid::Uuid;
use crate::models::{ChannelMemberInfo, ChannelMemberWithAvatar};
/// Client-to-server WebSocket messages.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(tag = "type", rename_all = "snake_case")]
pub enum ClientMessage {
/// Update position in the channel.
UpdatePosition {
/// X coordinate in scene space.
x: f64,
/// Y coordinate in scene space.
y: f64,
},
/// Update emotion (0-9).
UpdateEmotion {
/// Emotion slot (0-9, keyboard: e0-e9).
emotion: u8,
},
/// Ping to keep connection alive.
Ping,
}
/// Server-to-client WebSocket messages.
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(tag = "type", rename_all = "snake_case")]
pub enum ServerMessage {
/// Welcome message with initial state after connection.
Welcome {
/// This user's member info.
member: ChannelMemberInfo,
/// All current members with avatars.
members: Vec<ChannelMemberWithAvatar>,
},
/// A member joined the channel.
MemberJoined {
/// The member that joined.
member: ChannelMemberWithAvatar,
},
/// A member left the channel.
MemberLeft {
/// User ID (if authenticated user).
user_id: Option<Uuid>,
/// Guest session ID (if guest).
guest_session_id: Option<Uuid>,
},
/// A member updated their position.
PositionUpdated {
/// User ID (if authenticated user).
user_id: Option<Uuid>,
/// Guest session ID (if guest).
guest_session_id: Option<Uuid>,
/// New X coordinate.
x: f64,
/// New Y coordinate.
y: f64,
},
/// A member changed their emotion.
EmotionUpdated {
/// User ID (if authenticated user).
user_id: Option<Uuid>,
/// Guest session ID (if guest).
guest_session_id: Option<Uuid>,
/// New emotion slot (0-9).
emotion: u8,
/// Asset paths for all 9 positions of the new emotion layer.
emotion_layer: [Option<String>; 9],
},
/// Pong response to client ping.
Pong,
/// Error message.
Error {
/// Error code.
code: String,
/// Error message.
message: String,
},
}