Skip to main content

​
Overview

The User Management system provides administrators with tools to monitor user accounts, track activity, manage credits, and view detailed usage analytics across the Studley AI platform.

​
User Database Schema

​
users Table

Core user account information: 
CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name TEXT,
  email TEXT UNIQUE NOT NULL,
  "emailVerified" TIMESTAMP,
  password TEXT NOT NULL,
  image TEXT,
  credits INTEGER DEFAULT 5000,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
user_profiles Table

Extended user profile data: 
CREATE TABLE IF NOT EXISTS public.user_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name text,
  avatar_url text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  UNIQUE(user_id)
);
Studley AI uses a hybrid authentication system with both a custom users table and Supabase auth.users. The user_profiles table links to Supabase auth.

​
User Activity Monitoring

​
Generations Table

Track all AI generations by users: 
CREATE TABLE IF NOT EXISTS generations (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  type TEXT NOT NULL,  -- 'quiz', 'flashcard', 'study_guide', etc.
  topic TEXT NOT NULL,
  content TEXT NOT NULL,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);
Query user generations: 
-- Get all generations by a specific user
SELECT type, topic, "createdAt" 
FROM generations 
WHERE "userId" = 'user-id-here' 
ORDER BY "createdAt" DESC;

-- Count generations by type
SELECT type, COUNT(*) as count 
FROM generations 
WHERE "userId" = 'user-id-here' 
GROUP BY type;

​
User Library Items

Track saved content in user libraries: 
CREATE TABLE IF NOT EXISTS public.user_library_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  folder_id uuid REFERENCES public.user_folders(id) ON DELETE SET NULL,
  item_type text NOT NULL, -- 'quiz', 'flashcard', 'study_guide', 'writing_prompt'
  title text NOT NULL,
  topic text,
  content jsonb NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);
Query user library statistics: 
-- Get user's library summary
SELECT 
  item_type,
  COUNT(*) as total_items,
  MAX(created_at) as last_created
FROM user_library_items
WHERE user_id = 'user-id-here'
GROUP BY item_type;

​
Credit Management

​
Credit Usage Tracking

CREATE TABLE IF NOT EXISTS credit_usage (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  amount INTEGER NOT NULL,
  type TEXT NOT NULL,  -- 'deduction', 'purchase', 'bonus', 'refund'
  description TEXT,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);

​
View User Credits

-- Get user's current credit balance
SELECT credits FROM users WHERE id = 'user-id-here';

-- Get credit usage history
SELECT 
  amount,
  type,
  description,
  "createdAt"
FROM credit_usage
WHERE "userId" = 'user-id-here'
ORDER BY "createdAt" DESC
LIMIT 50;

​
Adjust User Credits

Credit adjustments should be made carefully and documented. Always create corresponding entries in the credit_usage table.
-- Add credits to a user (e.g., bonus or refund)
BEGIN;

UPDATE users 
SET credits = credits + 1000,
    "updatedAt" = CURRENT_TIMESTAMP
WHERE id = 'user-id-here';

INSERT INTO credit_usage ("userId", amount, type, description)
VALUES ('user-id-here', 1000, 'bonus', 'Admin credit bonus - loyalty reward');

COMMIT;

-- Deduct credits (e.g., for violations)
BEGIN;

UPDATE users 
SET credits = credits - 500,
    "updatedAt" = CURRENT_TIMESTAMP
WHERE id = 'user-id-here';

INSERT INTO credit_usage ("userId", amount, type, description)
VALUES ('user-id-here', -500, 'deduction', 'Policy violation penalty');

COMMIT;

​
User Sessions

​
sessions Table

CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "sessionToken" TEXT UNIQUE NOT NULL,
  "userId" TEXT NOT NULL,
  expires TIMESTAMP NOT NULL,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);
View active sessions: 
-- Get all active sessions
SELECT 
  s."userId",
  u.email,
  u.name,
  s.expires,
  s."createdAt"
FROM sessions s
JOIN users u ON s."userId" = u.id
WHERE s.expires > CURRENT_TIMESTAMP
ORDER BY s."createdAt" DESC;
Revoke user session: 
-- Force logout by deleting session
DELETE FROM sessions WHERE "userId" = 'user-id-here';

​
User Analytics Queries

​
Most Active Users

-- Top 10 users by generation count
SELECT 
  u.id,
  u.email,
  u.name,
  COUNT(g.id) as total_generations
FROM users u
LEFT JOIN generations g ON u.id = g."userId"
GROUP BY u.id, u.email, u.name
ORDER BY total_generations DESC
LIMIT 10;

​
Recent Sign-ups

-- Users who signed up in the last 7 days
SELECT 
  id,
  email,
  name,
  credits,
  "createdAt"
FROM users
WHERE "createdAt" > CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY "createdAt" DESC;

​
Inactive Users

-- Users who haven't generated content in 30 days
SELECT 
  u.id,
  u.email,
  u.name,
  MAX(g."createdAt") as last_generation
FROM users u
LEFT JOIN generations g ON u.id = g."userId"
GROUP BY u.id, u.email, u.name
HAVING MAX(g."createdAt") < CURRENT_TIMESTAMP - INTERVAL '30 days'
  OR MAX(g."createdAt") IS NULL;

​
Credit Distribution

-- Credit balance distribution
SELECT 
  CASE 
    WHEN credits = 0 THEN 'Empty'
    WHEN credits < 1000 THEN 'Low (1-999)'
    WHEN credits < 5000 THEN 'Medium (1K-5K)'
    WHEN credits < 10000 THEN 'High (5K-10K)'
    ELSE 'Very High (10K+)'
  END as credit_range,
  COUNT(*) as user_count
FROM users
GROUP BY credit_range
ORDER BY MIN(credits);

​
Find User by Email

SELECT * FROM users WHERE email ILIKE '%search-term%';

​
Find User by Name

SELECT * FROM users WHERE name ILIKE '%search-term%';

​
Comprehensive User Profile

-- Get complete user information
SELECT 
  u.id,
  u.email,
  u.name,
  u.credits,
  u."emailVerified",
  u."createdAt",
  COUNT(DISTINCT g.id) as total_generations,
  COUNT(DISTINCT l.id) as library_items,
  COUNT(DISTINCT f.id) as folders
FROM users u
LEFT JOIN generations g ON u.id = g."userId"
LEFT JOIN user_library_items l ON u.id::uuid = l.user_id
LEFT JOIN user_folders f ON u.id::uuid = f.user_id
WHERE u.email = '[email protected]'
GROUP BY u.id, u.email, u.name, u.credits, u."emailVerified", u."createdAt";

​
Clever Integration Users

For schools using Clever integration: 
CREATE TABLE clever_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  clever_id TEXT UNIQUE NOT NULL,
  district_id TEXT,
  school_id TEXT,
  role TEXT NOT NULL,  -- 'teacher', 'student'
  raw_data JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
Query Clever users: 
-- Get all Clever-authenticated users
SELECT 
  u.email,
  u.name,
  c.role,
  c.district_id,
  c.school_id
FROM clever_accounts c
JOIN users u ON c.user_id = u.id
ORDER BY c.created_at DESC;

​
User Deletion

User deletion is permanent and cascades to all related records. Always backup data before deletion.

​
Complete User Deletion

-- Delete user and all associated data
-- Cascades automatically to:
-- - sessions
-- - credit_usage  
-- - generations
-- - user_library_items
-- - user_folders
-- - accounts
DELETE FROM users WHERE id = 'user-id-here';

​
Soft Delete (Disable Account)

Consider implementing a soft delete by adding an active column: 
-- Add active column (one-time migration)
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true;

-- Disable account instead of deleting
UPDATE users SET active = false WHERE id = 'user-id-here';

-- Modify queries to exclude inactive users
SELECT * FROM users WHERE active = true;

​
Security Considerations

Privacy and Security:
  • Never display passwords (they are hashed)
  • Limit access to user email addresses
  • Audit all admin actions on user accounts
  • Follow GDPR and data privacy regulations
  • Implement proper logging for user data access

​
View Email Verification Status

-- Check which users have verified emails
SELECT 
  email,
  name,
  "emailVerified" IS NOT NULL as is_verified,
  "emailVerified" as verified_at
FROM users
ORDER BY "createdAt" DESC;

Admin Dashboard

Return to admin dashboard overview

Database Schema

Complete database table reference

Content Moderation

Monitor and moderate user content

RLS Policies

Understanding Row Level Security

Build docs developers (and LLMs) love

Get started for freeTalk to us