Documentation
Powered by

# Users Table

Core table for system user management with role-based access control.

# Schema 

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  auth_user_id UUID UNIQUE REFERENCES auth.users(id),
  email TEXT NOT NULL,
  name TEXT,
  role user_role DEFAULT 'client_manager',
  user_role_type TEXT, -- Legacy field
  client_id INTEGER REFERENCES clients(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

# Columns

Column Type Description Constraints
id UUID Primary key NOT NULL, UNIQUE
auth_user_id UUID Reference to Supabase auth user UNIQUE, FK to auth.users
email TEXT User email address NOT NULL
name TEXT User full name -
role user_role User role enum DEFAULT 'client_manager'
user_role_type TEXT Legacy role field Maintained for compatibility
client_id INTEGER Associated client organization FK to clients(id)
created_at TIMESTAMPTZ Creation timestamp DEFAULT NOW()
updated_at TIMESTAMPTZ Last update timestamp DEFAULT NOW()

# User Role Enum 

CREATE TYPE user_role AS ENUM (
  'super_admin',
  'client_admin', 
  'client_manager'
);

# Indexes

Key indexes for performance:

  • idx_users_auth_user_id - Fast auth lookups
  • idx_users_email - Email searches
  • idx_users_client_id - Client filtering
  • idx_users_role - Role-based queries
  • idx_users_auth_role_composite - Composite for RLS policies

# RLS Policies

# SELECT Policy: users_select_final_correct 

-- Users can only see their own record
-- Super admins can see all users
auth.uid() = auth_user_id OR is_super_admin_safe(auth.uid())

# INSERT Policy: users_insert_proper 

-- Super admins can insert any user
-- Client admins can insert users in their client
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND 
 get_user_client_safe(auth.uid()) = NEW.client_id)

# UPDATE Policy: users_update_proper 

-- Users can update their own record
-- Super admins can update any user
-- Client admins can update users in their client
auth.uid() = auth_user_id OR
is_super_admin_safe(auth.uid()) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND
 get_user_client_safe(auth.uid()) = client_id)

# DELETE Policy: users_delete_proper 

-- Super admins can delete any user (except themselves)
-- Client admins can delete users in their client (except themselves)
(is_super_admin_safe(auth.uid()) AND auth.uid() != auth_user_id) OR
(get_user_role_safe(auth.uid()) = 'client_admin' AND
 get_user_client_safe(auth.uid()) = client_id AND
 auth.uid() != auth_user_id)

# Related Functions

# Get User Role 

SELECT get_user_role_safe(auth.uid());
-- Returns: 'super_admin', 'client_admin', or 'client_manager'

# Get User Client 

SELECT get_user_client_safe(auth.uid());
-- Returns: client_id or NULL

# Get User Internal ID 

SELECT get_user_internal_id_safe(auth.uid());
-- Returns: users.id (UUID)

# Usage Examples

# Create a new user (as client admin) 

INSERT INTO users (email, name, role, client_id, auth_user_id)
VALUES ('newuser@example.com', 'New User', 'client_manager', 123, 'auth-uuid');

# Get users in your organization 

SELECT * FROM users WHERE client_id = 123;
-- RLS automatically filters based on your access

# Update user role 

UPDATE users SET role = 'client_admin' WHERE id = 'user-uuid';
-- Only works if you have permission

# Migration Notes

  • The role column was added in migration 001_role_based_access_control
  • Legacy user_role_type maintained for backward compatibility
  • All existing client_admin users were migrated to proper roles
  • RLS-safe functions prevent infinite recursion issues

© 2025 Exit Inc. All rights reserved.