SetTimes Admin Handbook

For System Administrators

---

Table of Contents

1. Introduction
2. System Architecture
3. User Management
4. Database Administration
5. Security & Access Control
6. Monitoring & Logs
7. Backup & Recovery
8. Performance Optimization
9. Troubleshooting
10. Maintenance Tasks

---

Introduction

This handbook is for system administrators managing the SetTimes platform. It covers:

• System architecture and components
• User and role management
• Database administration
• Security best practices
• Monitoring and maintenance

Prerequisites:

• Access to Cloudflare dashboard
• Database admin credentials
• Understanding of Cloudflare Workers/Pages
• Basic SQL knowledge

---

System Architecture

Technology Stack

Frontend:

• React 19 with Vite 8
• React Router v7
• Tailwind CSS 4
• lucide-react icons
• Cloudflare Pages (hosting)

Backend:

• Cloudflare Workers (API layer)
• Cloudflare Pages Functions
• D1 (SQLite) database
• R2 (Object storage for photos - optional)

Security:

• Session-based authentication
• HTTPOnly cookies
• CSRF protection (double-submit pattern)
• Role-based access control (RBAC)

Architecture Diagram

┌─────────────────────────────────────┐
│   Users (Browsers/Mobile)           │
└──────────────┬──────────────────────┘
               │
               │ HTTPS
               ▼
┌──────────────────────────────────────┐
│   Cloudflare CDN + WAF               │
└──────────────┬───────────────────────┘
               │
      ┌────────┴────────┐
      │                 │
      ▼                 ▼
┌──────────┐     ┌──────────────┐
│ Pages    │     │ Functions    │
│ (Static) │     │ (API Routes) │
└──────────┘     └──────┬───────┘
                        │
                        ▼
                 ┌──────────────┐
                 │  D1 Database │
                 └──────────────┘

Component Responsibilities

Cloudflare Pages (frontend/):

• Serves static React application
• Handles routing (SPA)
• PWA service worker
• Cached assets

Cloudflare Functions (functions/api/):

• REST API endpoints
• Authentication & authorization
• Business logic
• Database queries

D1 Database:

• User accounts & sessions
• Events, venues, bands
• Performance schedules
• Audit logs

---

User Management

Creating User Accounts

Via Invite Codes:

1. Log in to admin panel
2. Go to Users tab
3. Click "Generate Invite Code"
4. Share code with new user
5. User signs up with code

There is no password-based direct creation flow. The admin creates an invite; the recipient signs up with that invite and receives an activation link by email.

Role Hierarchy

Role	Level	Permissions
Admin	3	Full system access, user management
Editor	2	Create/edit events and bands, create venues, archive events
Viewer	1	Read-only access

Permission Model:

• checkPermission(context, requiredRole)
• User role level must be ≥ required level
• Example: Admin (3) ≥ Editor (2) = Access granted

Managing User Roles

Promoting/Demoting Users:

1. Users tab → Select user
2. Click "Edit"
3. Change role dropdown
4. Save changes

Security Note: Only admins can change roles.

Disabling User Accounts

Temporarily Disable:

1. Users tab → Select user
2. Click "Toggle Status"
3. User cannot log in (session terminated)

Permanently Delete:

1. Users tab → Select user
2. Click "Delete" (requires confirmation)
3. User data removed if no API keys are owned by the user

Note: Deletion is rejected with 409 USER_OWNS_API_KEYS when the user owns API keys. Deactivate the user instead; deactivation revokes sessions and keys.

Password Management

Reset User Password:

1. Users tab → Select user
2. Click "Reset Password"
3. System creates a one-time reset token
4. User receives a password-reset link by email
5. User sets a new password through the link

Password Requirements:

• Minimum 12 characters
• Hashed using PBKDF2-SHA256 via the Web Crypto API (not bcrypt — Workers cannot run native bcrypt)

---

Database Administration

D1 Database Structure

Tables:

• users - User accounts
• lucia_sessions - Active server-side sessions
• events - Event definitions
• venues - Performance venues
• band_profiles - Artist/band profiles
• performances - Links a band profile to an event, venue, and set time
• audit_log - Security audit trail
• api_keys - Machine credentials and revocation state
• event_daily_stats - Daily event aggregates
• invite_codes - User invite system

Schema Location: numbered migrations in migrations/ (with database/setup-complete.sql as the consolidated reference)

Accessing the Database

Via Wrangler CLI:

# List databases
wrangler d1 list

# Execute query
wrangler d1 execute settimes-production-db --command="SELECT * FROM users LIMIT 10"

# Run migration
wrangler d1 execute settimes-production-db --file=./migrations/001_initial_schema.sql

Via Cloudflare Dashboard:

1. Workers & Pages → D1
2. Select database: settimes-production-db
3. Console tab → Run queries

Common Database Queries

Check User Sessions:

SELECT u.email, s.expires_at
FROM lucia_sessions s
JOIN users u ON s.user_id = u.id
WHERE s.expires_at > unixepoch()
ORDER BY s.expires_at DESC;

Event Statistics:

SELECT
  e.name,
  e.date,
  COUNT(DISTINCT p.band_profile_id) as band_count,
  COUNT(DISTINCT p.venue_id) as venue_count
FROM events e
LEFT JOIN performances p ON e.id = p.event_id
GROUP BY e.id
ORDER BY e.date DESC;

Audit Log Review:

SELECT
  al.action,
  u.email,
  al.details,
  al.ip_address,
  al.created_at
FROM audit_log al
JOIN users u ON al.user_id = u.id
WHERE al.created_at > datetime('now', '-7 days')
ORDER BY al.created_at DESC
LIMIT 100;

Database Backups

Automated Backups:

• Cloudflare D1 automatically backs up databases
• Point-in-time recovery available (contact Cloudflare support)

Manual Backup:

# Export entire database
wrangler d1 export settimes-production-db --output=backup-$(date +%Y%m%d).sql

Restore from a recovery point:

# Inspect available recovery points
wrangler d1 time-travel info settimes-production-db

# Restore a confirmed recovery point
wrangler d1 time-travel restore settimes-production-db --timestamp=<ISO-8601-timestamp>

An exported SQL file is a backup artifact, not a live restore procedure. See Cloudflare's D1 Time Travel documentation for the retention window on your plan.

Backup Schedule:

• Daily automated backups (Cloudflare)
• Weekly manual export (recommended)
• Store backups securely off-platform

---

Security & Access Control

Authentication Flow

1. User submits email + password to /api/admin/auth/login
2. Backend verifies credentials (PBKDF2-SHA256 via Web Crypto)
3. Session created in lucia_sessions table
4. Session token returned in HTTPOnly cookie
5. All subsequent requests include cookie
6. Middleware validates session + checks RBAC

Multi-Factor Authentication (MFA)

Admins can enable TOTP-based two-factor authentication on their accounts:

• Setup: In account settings, scan the QR code (or enter the secret) into an authenticator app (Google Authenticator, Authy, 1Password, etc.) and confirm with a 6-digit code. TOTP is HMAC-SHA1 over a 30-second window, computed via the Web Crypto API (functions/utils/totp.js) — no third-party crypto library.
• Backup codes: Enabling MFA reveals one-time backup codes — store them securely. Each works once if the authenticator is unavailable.
• Trusted devices: A device can be marked trusted to skip the MFA prompt there for a limited period; trusted devices can be revoked at any time.
• Enforcement: With MFA enabled, login requires the password and a valid TOTP (or backup) code before a session is issued. On any successful re-authentication, the user's prior sessions are invalidated.

Session Management

Session Configuration:

• Storage: lucia_sessions rows in D1 (direct D1 session manager; expires_at is INTEGER Unix-epoch seconds)
• Admin idle timeout: 15 minutes
• Admin absolute lifetime: 8 hours
• Cookie transport: HttpOnly session cookie (__Host-session_token in production)
• CSRF: Double-submit CSRF cookie plus X-CSRF-Token

Session Cleanup:

-- Remove expired sessions (run daily)
DELETE FROM lucia_sessions WHERE expires_at < strftime('%s', 'now');

Force Logout User:

-- Terminate all sessions for a user
DELETE FROM lucia_sessions WHERE user_id = <user_id>;

CSRF Protection

Implementation: Double-submit cookie pattern

How it works:

1. Server generates CSRF token
2. Token sent as cookie (readable by JS)
3. Client includes token in X-CSRF-Token header
4. Server verifies cookie matches header

Location: functions/utils/csrf.js

Bypass (for testing only):

// Set in request header
X-CSRF-Token: <token_from_cookie>

Audit Logging

What's Logged:

• User logins/logouts
• Event creation/modification/deletion
• Venue/band changes
• Role changes
• Failed login attempts

Audit Log Retention:

• Retention runs automatically through the daily maintenance job.
• Ordinary audit_log rows are retained for one year.
• api_key.request rows are retained for 90 days.

Review Logs Regularly:

-- Failed logins (potential attacks)
SELECT * FROM audit_log
WHERE action LIKE '%login%failed%'
AND created_at > datetime('now', '-1 day');

-- Admin actions
SELECT * FROM audit_log al
JOIN users u ON al.user_id = u.id
WHERE u.role = 'admin'
ORDER BY al.created_at DESC
LIMIT 50;

Security Best Practices

Regular Tasks:

• [ ] Review audit logs weekly
• [ ] Monitor failed login attempts
• [ ] Update dependencies monthly
• [ ] Rotate admin passwords quarterly
• [ ] Review user access permissions

Access Control:

• Principle of least privilege (grant minimum required role)
• Regular access reviews (quarterly)
• Remove inactive users (>90 days)
• Use strong passwords (enforce policy)

---

Monitoring & Logs

Cloudflare Analytics

Access:

1. Cloudflare Dashboard
2. Pages → settimes project
3. Analytics tab

Key Metrics:

• Page views
• Unique visitors
• Request rate
• Error rate (4xx, 5xx)
• Bandwidth usage

Application Logs

View Logs:

# Real-time logs
wrangler tail

# Filter by status code
wrangler tail --status error

Log Locations:

• API Logs: Cloudflare Workers logs (via wrangler tail)
• Audit Logs: D1 audit_log table
• Error Logs: Console errors (browser dev tools)

Performance Monitoring

Core Web Vitals:

• LCP (Largest Contentful Paint): < 2.5s
• FID (First Input Delay): < 100ms
• CLS (Cumulative Layout Shift): < 0.1

Check Performance:

1. Use Lighthouse in Chrome DevTools
2. Run: npm run lighthouse in frontend/
3. Review PageSpeed Insights: npm run psi

Alerting

Set Up Alerts:

1. Cloudflare Dashboard → Notifications
2. Configure alerts for:
3. High error rate (>5%)
4. Traffic spikes (unusual patterns)
5. DDoS attacks
6. SSL certificate expiration

---

Backup & Recovery

Database Backups

Backup Strategy:

• Frequency: Daily automated, weekly manual
• Retention: 30 days rolling
• Storage: Off-platform (S3/R2)

Backup Command:

# Create backup
wrangler d1 export settimes-production-db --output=backups/settimes-$(date +%Y%m%d-%H%M%S).sql

# Compress backup
gzip backups/settimes-*.sql

Restore Database:

# Restore from backup
gunzip backups/settimes-20251119.sql.gz
wrangler d1 execute settimes-production-db --file=backups/settimes-20251119.sql

Disaster Recovery Plan

Scenario: Database Corruption

1. Stop all write operations
2. Assess damage (check audit logs)
3. Restore from latest backup
4. Verify data integrity
5. Resume operations
6. Document incident

Scenario: Cloudflare Outage

1. Check Cloudflare status page
2. Communicate to users (status page)
3. Wait for resolution (no action needed)
4. Verify functionality after recovery

RTO/RPO:

• Recovery Time Objective (RTO): 4 hours
• Recovery Point Objective (RPO): 24 hours

---

Performance Optimization

Frontend Optimization

Build Optimization:

• Vite code splitting
• Tree shaking enabled
• Asset compression (gzip/brotli)
• Image lazy loading
• React.memo for pure components

Cache Strategy:

/assets/*         → 1 year (immutable)
/*.html           → no-cache
/api/*            → no-store
Service Worker    → no-cache

API Optimization

Query Optimization:

• Use indexes on foreign keys
• Avoid N+1 queries
• Limit results (LIMIT clause)
• Cache frequently accessed data

Rate Limiting:

• Implement Cloudflare Rate Limiting
• Login endpoint: 5 requests/minute/IP
• API endpoints: 100 requests/minute/IP

Database Optimization

Indexes:

-- Ensure indexes exist (all are created by the migrations; see database/setup-complete.sql)
CREATE INDEX IF NOT EXISTS idx_lucia_sessions_user_id ON lucia_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_lucia_sessions_expires_at ON lucia_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_performances_event ON performances(event_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_user ON audit_log(user_id);

Cleanup Old Data: Retention cleanup is automatic. Do not manually delete audit rows; the daily job enforces the published retention windows.

---

Troubleshooting

Common Issues

Issue: Users can't log in

Diagnosis:

-- Check if user exists
SELECT * FROM users WHERE email = 'user@example.com';

-- Check if account is active
SELECT is_active FROM users WHERE email = 'user@example.com';

-- Check failed login attempts
SELECT * FROM audit_log
WHERE action LIKE '%login%'
AND details LIKE '%user@example.com%'
ORDER BY created_at DESC LIMIT 10;

Solutions:

• Verify user exists and is active
• Reset password if forgotten
• Check for account lockout (not implemented yet)

---

Issue: Events not appearing on public timeline

Diagnosis:

-- Check event status
SELECT name, date, status FROM events WHERE id = <event_id>;

-- Check if bands assigned
SELECT COUNT(*) FROM performances WHERE event_id = <event_id>;

Solutions:

• Ensure status = 'published' (or 'archived' — archived events stay publicly visible on browse/history surfaces; they're concluded, not hidden. See CLAUDE.md "Public event visibility.")
• Verify bands are assigned to event
• Clear Cloudflare cache

---

Issue: Slow API responses

Diagnosis:

# Check API logs
wrangler tail --status slow

# Check query performance
wrangler d1 execute settimes-production-db --command="EXPLAIN QUERY PLAN <your_query>"

Solutions:

• Add missing indexes
• Optimize slow queries
• Implement caching
• Upgrade D1 plan if needed

---

Debug Mode

Enable Debug Logging:

// In functions/api/_middleware.js
console.log("[DEBUG]", { user, action, details });

View Debug Logs:

wrangler tail --format=pretty

---

Maintenance Tasks

Daily

• [ ] Review error logs
• [ ] Monitor API response times
• [ ] Check failed login attempts

Weekly

• [ ] Review audit logs
• [ ] Export database backup
• [ ] Check disk usage (D1 limits)
• [ ] Review user access

Monthly

• [ ] Update npm dependencies
• [ ] Security audit (run npm audit)
• [ ] Review and optimize slow queries
• [ ] Clean up expired sessions
• [ ] Test disaster recovery plan

Quarterly

• [ ] Rotate admin passwords
• [ ] Review user roles and permissions
• [ ] Archive old audit logs
• [ ] Performance audit (Lighthouse)
• [ ] Update documentation

---

Emergency Contacts

Cloudflare Support:

• Dashboard: cloudflare.com/support
• Enterprise: 24/7 phone support
• Community: community.cloudflare.com

Development Team:

• GitHub: github.com/BreakableHoodie/settimesdotca
• Issues: Create issue on GitHub

---

Appendix

Useful Commands

# Deployments happen by merging to main after CI passes.

# CI applies migrations before deployment.

# Check database size
wrangler d1 info settimes-production-db

# List all users
wrangler d1 execute settimes-production-db --command="SELECT email, role FROM users"

# Force logout all users
wrangler d1 execute settimes-production-db --command="DELETE FROM lucia_sessions"

Environment Variables

The production database is the top-level DB binding in wrangler.toml; runtime secrets are configured in the Pages dashboard.

---

Version: 1.0 Last Updated: 2026-07-04 For: SetTimes.ca Platform

---

Questions? Refer to the other guides or contact the development team.