User Management

User management in the database abstraction layer provides comprehensive user lookups, listing, filtering, role-based access control, and user attribution for events and clients.

Overview

User operations provide:

• List & Filter: Search and filter users with pagination
• Role Lookups: Get user roles from Clerk IDs
• ID Resolution: Convert Clerk IDs to internal UUIDs
• User Lookup: Find users by ID or email
• User Attribution: Track who created events/clients
• Authorization: Role-based access control with helper functions

📋 List & Search

Search users across name, email, username with pagination and filtering.

🔐 Role-Based Access

Six-tier role system with authorization helpers for permission checks.

🔗 Clerk Integration

Seamless integration with Clerk authentication. Map Clerk IDs to internal user records.

👤 User Attribution

Track who created events and clients with foreign key relationships.

Core Operations

List Users

Search, filter, and paginate through users with comprehensive query options:

import { getDatabase } from '#libs/database'

const db = getDatabase()

// Get all users (up to 50)
const users = await db.getUsers()

// Search across name, email, and username
const results = await db.getUsers({
  search: 'john',
  limit: 10,
})

// Filter by role
const volunteers = await db.getUsers({
  role: 'volunteer',
})

// Paginate results
const page2 = await db.getUsers({
  limit: 20,
  offset: 20,
})

// Sort by last sign-in
const recent = await db.getUsers({
  order_by: 'last_sign_in_at',
  order_direction: 'desc',
  limit: 10,
})

Available Options:

• search - Text search across name, email, username
• role - Filter by specific role
• email - Exact email match
• clerk_id - Exact Clerk ID match
• limit - Max results (default: 50)
• offset - Skip N records
• fields - Return specific fields only
• order_by - Sort field (created_at, last_sign_in_at, full_name, email)
• order_direction - Sort direction (asc, desc)

Get User by ID

Retrieve a single user by their internal UUID:

const user = await db.getUserById('123e4567-e89b-12d3-a456-426614174000')

if (user) {
  console.log(`Found: ${user.full_name} (${user.email})`)
}

Get User by Email

Retrieve a single user by their email address:

const user = await db.getUserByEmail('user@example.com')

if (user) {
  console.log(`User role: ${user.role}`)
}

Get User Role

Retrieve a user’s role from their Clerk ID:

import { getDatabase } from '#libs/database'

const db = getDatabase()
const role = await db.getUserRole('user_2abc123def456')

if (role) {
  console.log(`User role: ${role}`)
  // role is one of: 'member', 'volunteer', 'team_manager',
  //                 'team_admin', 'admin', 'super_admin'
}

Use Cases:

• Authorization checks
• Feature flags based on role
• Admin panel access control

Get Internal User ID

Convert Clerk ID to internal UUID:

const userId = await db.getUserIdByClerkId('user_2abc123def456')

if (userId) {
  // Use userId for database operations
  const events = await db.getEvents({ created_by: userId })
}

Use Cases:

• Creating events with user attribution
• Querying user-specific data
• Foreign key relationships

Role System

Role Hierarchy

The system uses a six-tier role hierarchy:

Level	Role	Description	Capabilities
1	member	Basic member (default)	View events, create own content
2	volunteer	Volunteer contributor	Member + volunteer-specific features
3	team_manager	Team lead	Volunteer + team management
4	team_admin	Team administrator	Team manager + administrative tasks
6	admin	Administrator	Full system administration
7	super_admin	Super administrator	Unrestricted access + moderation

Role Configuration

Roles are configured in config/roles.config.ts and auto-generated to TypeScript types:

// Auto-generated from config
export type UserRole =
  | 'member'
  | 'volunteer'
  | 'team_manager'
  | 'team_admin'
  | 'admin'
  | 'super_admin'

User Attribution

Events

Track who created events:

import { getDatabase } from '#libs/database'

const db = getDatabase()

// Get user ID from Clerk
const userId = await db.getUserIdByClerkId(locals.userId)

if (userId) {
  // Create event with attribution
  const eventId = await db.insertEvent({
    name: 'Team Meeting',
    event_date: '2025-12-15',
    created_by: userId, // Foreign key to users table
  })
}

Benefits:

• Track event ownership
• Enable “My Events” features
• Support moderation and auditing

Clients

Attribute a client record to the user who created it:

// Get user ID
const userId = await db.getUserIdByClerkId(locals.userId)

// Guard before attributing: an unsynced Clerk user has no internal row yet,
// and created_by is a foreign key into users.
if (userId) {
  const clientId = await db.insertClient({
    nickname: 'John Doe',
    gender: 'prefer_not_to_say',
    created_by: userId,
  })
}

Authorization Patterns

Role-Based Access Control

import { hasAdminPrivileges } from '#utils/user-authorization'

async function canModerateEvents(clerkId: string): Promise<boolean> {
  const db = getDatabase()
  const role = await db.getUserRole(clerkId)

  return hasAdminPrivileges(role)
}

// Usage
if (await canModerateEvents(locals.userId)) {
  // Allow event moderation
}

Permission Helpers

const ROLE_LEVELS: Record<UserRole, number> = {
  member: 1,
  volunteer: 2,
  team_manager: 3,
  team_admin: 4,
  admin: 6,
  super_admin: 7,
}

async function hasMinimumRole(
  clerkId: string,
  minimumRole: UserRole
): Promise<boolean> {
  const db = getDatabase()
  const userRole = await db.getUserRole(clerkId)

  if (!userRole) return false

  return ROLE_LEVELS[userRole] >= ROLE_LEVELS[minimumRole]
}

// Usage
if (await hasMinimumRole(locals.userId, 'team_manager')) {
  // Allow team management features
}

Feature Flags

async function getAvailableFeatures(clerkId: string): Promise<string[]> {
  const db = getDatabase()
  const role = await db.getUserRole(clerkId)

  const features = ['view_events', 'create_events']

  if (role && ROLE_LEVELS[role] >= ROLE_LEVELS.volunteer) {
    features.push('volunteer_dashboard')
  }

  if (role && ROLE_LEVELS[role] >= ROLE_LEVELS.team_manager) {
    features.push('manage_team', 'view_analytics')
  }

  if (role && ROLE_LEVELS[role] >= ROLE_LEVELS.admin) {
    features.push('user_management', 'system_settings')
  }

  if (role === 'super_admin') {
    features.push('moderation', 'delete_any_content')
  }

  return features
}

// Usage in component
const features = await getAvailableFeatures(locals.userId)
const canModerate = features.includes('moderation')

Common Use Cases

Middleware Authentication

src/middleware.ts

import { getDatabase } from '#libs/database'

export async function onRequest({ locals, redirect }, next) {
  if (locals.userId) {
    const db = getDatabase()

    // Get user role for authorization
    const role = await db.getUserRole(locals.userId)
    locals.userRole = role

    // Get internal user ID for database operations
    const userId = await db.getUserIdByClerkId(locals.userId)
    locals.internalUserId = userId
  }

  return next()
}

Admin Panel Protection

src/pages/admin/index.astro

---
import { getDatabase } from '#libs/database'
import { hasAdminPrivileges } from '#utils/user-authorization'

const db = getDatabase()
const role = await db.getUserRole(locals.userId)

if (!hasAdminPrivileges(role)) {
  return Astro.redirect('/unauthorized')
}
---

<div class="admin-panel">
  <!-- Admin content -->
</div>

User Dashboard

src/pages/dashboard/index.astro

---
import { getDatabase } from '#libs/database'

const db = getDatabase()
const userId = await db.getUserIdByClerkId(locals.userId)

if (!userId) {
  return Astro.redirect('/login')
}

// Get user's events
const myEvents = await db.getEvents({
  created_by: userId,
  limit: 20,
})

// Get user role for feature access
const role = await db.getUserRole(locals.userId)
---

<div class="user-dashboard">
  <h1>My Events</h1>
  {myEvents.map(event => (
    <div class="event-card">
      <h3>{event.name}</h3>
      <p>{event.event_date}</p>
    </div>
  ))}

  {role && role !== 'member' && (
    <div class="advanced-features">
      <!-- Role-specific features -->
    </div>
  )}
</div>

Authorization Helpers

The system includes built-in authorization helpers for role-based access control.

hasAdminPrivileges()

Check if a user role has administrative privileges (foundational utility).

import { hasAdminPrivileges } from '#utils/user-authorization'

const db = getDatabase()
const userRole = await db.getUserRole(locals.userId)

if (hasAdminPrivileges(userRole)) {
  // User is admin, super_admin, or team_admin
  // Grant administrative access
}

Returns true for:

• admin - Standard administrator
• super_admin - Super administrator
• team_admin - Team administrator

Use Cases:

• Foundational check used by other authorization helpers
• Quick admin-level permission verification
• Single source of truth for admin role definition

canListAllUsers()

Check if a user role can list all users (admin/super_admin only):

import { canListAllUsers } from '#utils/user-authorization'

// In an API endpoint
export const GET: APIRoute = async ({ locals }) => {
  const db = getDatabase()
  const userRole = await db.getUserRole(locals.userId)

  if (!canListAllUsers(userRole)) {
    return new Response('Forbidden', { status: 403 })
  }

  const users = await db.getUsers()
  return new Response(JSON.stringify({ users }))
}

canViewUser()

Check if a user can view another user’s data (own data or admin):

import { canViewUser } from '#utils/user-authorization'

export const GET: APIRoute = async ({ locals, params }) => {
  const db = getDatabase()
  const userRole = await db.getUserRole(locals.userId)
  const targetUserId = params.id

  if (!canViewUser(locals.userId, targetUserId, userRole)) {
    return new Response('Forbidden', { status: 403 })
  }

  const user = await db.getUserById(targetUserId)
  return new Response(JSON.stringify({ user }))
}

getAllowedUserFields()

Get allowed fields based on permissions:

import { getAllowedUserFields } from '#utils/user-authorization'

const allowedFields = getAllowedUserFields(
  requestingUserId,
  targetUserId,
  userRole
)

// Returns full fields for own data or admin:
// ['id', 'clerk_id', 'email', 'username', 'full_name', 'avatar_url',
//  'role', 'app_metadata', 'last_sign_in_at', 'created_at', 'updated_at']

// Returns public fields for others:
// ['id', 'username', 'full_name', 'avatar_url', 'role']

API Endpoints

The system includes ready-to-use API endpoints for user management.

GET /api/users

List users with filtering (admin only).

Query Parameters:

• search - Text search across name/email/username
• role - Filter by specific role
• limit - Max results (default: 50)
• offset - Pagination offset (default: 0)

Example:

# Search for users
curl "https://your-site.com/api/users?search=john&limit=10"

# Get all volunteers
curl "https://your-site.com/api/users?role=volunteer"

Response:

{
  "users": [...],
  "total": 10,
  "limit": 10,
  "offset": 0
}

GET /api/users/[id]

Get single user by ID (own data or admin).

Example:

curl "https://your-site.com/api/users/123e4567-e89b-12d3-a456-426614174000"

Response:

{
  "user": {
    "id": "...",
    "email": "user@example.com",
    "full_name": "John Doe",
    "role": "volunteer",
    ...
  }
}

Best Practices

1. Always Check Authorization

import { hasAdminPrivileges } from '#utils/user-authorization'

// ✅ GOOD - Check before allowing action
const role = await db.getUserRole(locals.userId)
if (hasAdminPrivileges(role)) {
  await performAdminAction()
}

// ❌ BAD - No authorization check
await performAdminAction() // Anyone can do this!

2. Use Internal User IDs for Database Operations

// ✅ GOOD - Convert Clerk ID to internal UUID
const userId = await db.getUserIdByClerkId(locals.userId)
if (userId) {
  await db.insertEvent({ ..., created_by: userId })
}

// ❌ BAD - Using Clerk ID directly
await db.insertEvent({ ..., created_by: locals.userId }) // Wrong ID type

3. Cache Role Lookups

// ✅ GOOD - Cache in middleware
export async function onRequest({ locals }, next) {
  if (locals.userId) {
    const db = getDatabase()
    locals.userRole = await db.getUserRole(locals.userId)
  }
  return next()
}

// Then use cached value
if (locals.userRole === 'admin') { ... }

// ❌ BAD - Repeated lookups
if (await db.getUserRole(locals.userId) === 'admin') { ... }
if (await db.getUserRole(locals.userId) === 'admin') { ... } // Duplicate query

4. Handle Missing Users

// ✅ GOOD - Check for null
const role = await db.getUserRole(clerkId)
if (role) {
  console.log(`User has role: ${role}`)
} else {
  console.log('User not found in database')
  // Maybe sync from Clerk via webhook
}

// ❌ BAD - Assume user exists
const role = await db.getUserRole(clerkId)
console.log(role.toUpperCase()) // Error if role is null

Next Steps

• Events System - Learn about event user attribution
• Database Architecture - Understand the abstraction layer
• Database Switching - Switch between providers

Technical References

• Database API Reference - Complete API documentation
• Migration 001 - Users table schema
• Migration 006 - Role system