User Management

Manage user accounts, permissions, and account-level actions on the BookWish platform.

Overview

As a BookWish administrator, you can view and manage all user accounts, including:

• Searching and finding users
• Viewing user profiles and activity
• Managing user tiers and permissions
• Suspending or banning accounts
• Handling account issues

User Model

Core User Fields

{
  id: string;                   // Unique user ID
  deviceId?: string;            // Device identifier (for guest users)
  email?: string;               // User email (unique)
  passwordHash?: string;        // Hashed password
  displayName?: string;         // Public display name
  username?: string;            // Unique username
  avatarUrl?: string;           // Profile avatar URL
  homeStoreId?: string;         // Favorite/local store
  tier: UserTier;              // User tier/role
  isGuest: boolean;            // Guest account flag

  // Timestamps
  createdAt: DateTime;
  updatedAt: DateTime;
  lastLoginAt?: DateTime;
}

User Tiers

Tier	Description	Permissions
guest	Temporary/anonymous users	Browse only, no account persistence
free	Basic registered users	Create content, wishlists (limited), social features
premium	Paid subscribers	Enhanced features, more wishlists, priority support
bookstore	Store owner accounts	Inventory management, POS, order fulfillment
admin	Platform administrators	All features + admin endpoints

Finding Users

By Username or Email

Database Query:

-- Find by username
SELECT * FROM users WHERE username = 'bookworm42';

-- Find by email
SELECT * FROM users WHERE email = 'user@example.com';

-- Search by partial username
SELECT * FROM users WHERE username LIKE '%book%';

By User ID

Database Query:

SELECT * FROM users WHERE id = 'user_abc123';

By Activity Pattern

Recent Users:

SELECT * FROM users
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

Active Users:

SELECT u.*, COUNT(l.id) as line_count
FROM users u
LEFT JOIN lines l ON u.id = l.user_id
WHERE l.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id
ORDER BY line_count DESC;

By Tier

All Admins:

SELECT * FROM users WHERE tier = 'admin';

All Store Owners:

SELECT * FROM users WHERE tier = 'bookstore';

Viewing User Profile

User Information

Key Details to Review:

• Username and display name
• Email address
• Account creation date
• Last login/activity
• Current tier
• Guest vs. registered account

User Activity

Content Created:

• Lines shared
• Reviews written
• Replies posted
• Likes given

Social Activity:

• Users following
• Followers
• Stores following
• Blocking/muting activity

Shopping Activity:

• Wishlists created
• Orders placed
• Reviews written
• Stock alerts subscribed

Related Records

User's Content:

-- Lines
SELECT * FROM lines WHERE user_id = 'user_id' ORDER BY created_at DESC;

-- Reviews
SELECT * FROM reviews WHERE user_id = 'user_id' ORDER BY created_at DESC;

User's Social Connections:

-- Following
SELECT * FROM follows WHERE follower_id = 'user_id';

-- Followers
SELECT * FROM follows WHERE followed_id = 'user_id';

User Actions

Update User Tier

Change User Tier:

UPDATE users
SET tier = 'premium'
WHERE id = 'user_id';

Common Tier Changes:

• guest → free (account upgrade)
• free → premium (subscription purchase)
• free → bookstore (store owner approval)
• Any → admin (grant admin access)
• premium → free (subscription cancellation)

Update User Profile

Change Username:

UPDATE users
SET username = 'new_username'
WHERE id = 'user_id';

Change Display Name:

UPDATE users
SET display_name = 'New Display Name'
WHERE id = 'user_id';

Update Email:

UPDATE users
SET email = 'newemail@example.com'
WHERE id = 'user_id';

Suspend Account

Temporary Suspension:

While BookWish doesn't currently have a dedicated suspension flag, you can implement suspension by:

1. Change tier to guest (revokes permissions):

UPDATE users
SET tier = 'guest'
WHERE id = 'user_id';

2. Document suspension (in separate tracking system or notes)

3. Hide user content (if appropriate):

UPDATE lines
SET moderation_status = 'hidden'
WHERE user_id = 'user_id';

UPDATE reviews
SET moderation_status = 'hidden'
WHERE user_id = 'user_id';

When to Suspend:

• Multiple guideline violations
• Harassment or abuse
• Spam activities
• Pending investigation
• User request (account lock)

Suspension Communication:

• Email user explaining suspension
• State reason and duration
• Outline steps to reinstate
• Provide appeal process

Ban/Delete Account

Permanent Ban:

1. Deactivate account (set tier to guest):

UPDATE users
SET tier = 'guest'
WHERE id = 'user_id';

2. Hide all content:

UPDATE lines SET moderation_status = 'hidden' WHERE user_id = 'user_id';
UPDATE reviews SET moderation_status = 'hidden' WHERE user_id = 'user_id';

3. Document ban reason (for records)

Hard Delete Account:

Only for severe cases (GDPR requests, legal issues):

-- This will cascade delete related records
-- BE VERY CAREFUL - THIS IS PERMANENT
DELETE FROM users WHERE id = 'user_id';

When to Ban:

• Repeated severe violations
• Illegal activity
• Coordinated abuse
• Ban evasion
• Platform threat

When to Delete:

• User GDPR/data deletion request
• Legal requirement
• Account never used (cleanup)
• Spam/bot account

User Role Management

Admin Role

Grant Admin Access:

UPDATE users
SET tier = 'admin'
WHERE id = 'trusted_user_id';

Admin Responsibilities:

• Content moderation
• User management
• Report review
• Platform monitoring

Admin Selection Criteria:

• Trusted community member
• Platform knowledge
• Good judgment
• Available and responsive

Bookstore Role

Grant Bookstore Access:

UPDATE users
SET tier = 'bookstore'
WHERE id = 'verified_store_owner_id';

Requirements:

• Verified business
• Store application approved
• Payment processing setup
• Agreed to seller terms

See Store Verification for details.

Premium Role

Grant Premium:

UPDATE users
SET tier = 'premium'
WHERE id = 'user_id';

Typical via:

• Stripe subscription webhook
• Manual promotion (comp subscription)
• Migration from old system

Handling Account Issues

Compromised Account

Steps to Secure:

1. Reset password (force change)
2. Revoke all sessions/tokens
3. Review recent activity
4. Check for unauthorized changes
5. Contact user to confirm

Database Actions:

-- Clear password (forces reset)
UPDATE users
SET password_hash = NULL
WHERE id = 'user_id';

Impersonation

Verify Impersonation:

• Check username similarity
• Review profile details
• Check creation date
• Contact reporter

Action if Confirmed:

1. Suspend impersonator account
2. Notify impersonated user
3. Offer to transfer followers (if applicable)
4. May delete if clear violation

Duplicate Accounts

Verify Duplicates:

• Same email pattern
• Same device ID
• Similar usernames
• Same activity patterns

Resolve:

• Merge if user mistake
• Suspend duplicates if abuse
• Allow if legitimate (family, etc.)

Account Recovery

Lost Access:

1. Verify user identity
2. Check email on file
3. Provide password reset
4. Verify account ownership
5. Update recovery email

Deleted by Mistake:

• Check soft-delete capability
• Restore from backup (if available)
• Recreate manually if necessary
• Apologize for inconvenience

User Analytics

User Growth

Track Registration Rate:

SELECT DATE(created_at) as date,
       COUNT(*) as new_users
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND is_guest = false
GROUP BY DATE(created_at)
ORDER BY date;

Tier Distribution

Current Breakdown:

SELECT tier, COUNT(*) as count
FROM users
GROUP BY tier;

Active Users

Active in Last 30 Days:

SELECT COUNT(DISTINCT u.id) as active_users
FROM users u
WHERE u.id IN (
  SELECT user_id FROM lines WHERE created_at >= NOW() - INTERVAL '30 days'
  UNION
  SELECT user_id FROM reviews WHERE created_at >= NOW() - INTERVAL '30 days'
);

User Retention

30-Day Retention:

SELECT
  DATE_TRUNC('month', created_at) as cohort,
  COUNT(*) as total_users,
  COUNT(CASE WHEN last_login_at >= created_at + INTERVAL '30 days' THEN 1 END) as retained
FROM users
WHERE is_guest = false
GROUP BY cohort
ORDER BY cohort DESC;

Best Practices

User Privacy

Protect User Data:

• Don't share email/personal info
• Respect privacy settings
• Follow GDPR/privacy laws
• Minimize data access
• Log admin actions

Fair Enforcement

Consistent Actions:

• Apply rules equally
• Don't favor popular users
• Document all actions
• Allow appeals
• Review decisions regularly

Communication

With Users:

• Be professional and respectful
• Explain actions clearly
• Provide paths to resolution
• Respond to inquiries promptly
• Document communications

Account Safety

Prevent Abuse:

• Monitor for ban evasion
• Track repeat offenders
• Watch for coordinated attacks
• Protect vulnerable users
• Report illegal activity

Database Queries

Find Problem Users

Users with Multiple Reports:

SELECT u.id, u.username,
       COUNT(r.id) as report_count
FROM users u
JOIN reports r ON r.reported_user_id = u.id
WHERE r.status IN ('action_taken', 'pending')
GROUP BY u.id, u.username
HAVING COUNT(r.id) >= 3
ORDER BY report_count DESC;

Users with Hidden Content:

SELECT u.id, u.username,
       COUNT(DISTINCT l.id) as hidden_lines,
       COUNT(DISTINCT r.id) as hidden_reviews
FROM users u
LEFT JOIN lines l ON u.id = l.user_id AND l.moderation_status = 'hidden'
LEFT JOIN reviews r ON u.id = r.user_id AND r.moderation_status = 'hidden'
GROUP BY u.id, u.username
HAVING COUNT(DISTINCT l.id) + COUNT(DISTINCT r.id) > 0
ORDER BY hidden_lines + hidden_reviews DESC;

Find Inactive Accounts

No Activity in 90 Days:

SELECT * FROM users
WHERE is_guest = false
  AND created_at < NOW() - INTERVAL '90 days'
  AND id NOT IN (
    SELECT user_id FROM lines WHERE created_at >= NOW() - INTERVAL '90 days'
    UNION
    SELECT user_id FROM reviews WHERE created_at >= NOW() - INTERVAL '90 days'
  );

Next Steps

• Store Accounts
• Subscriptions
• Content Moderation