Auth & Users

Auth & Users

User authentication, profiles, roles, sessions, and device management.

29 tables in this group.

users

RLS Policies:

• Admins or users can delete profiles — DELETE for {public}
• Admins or users can update profiles — UPDATE for {public}
• Admins or users can view profiles — SELECT for {public}

Indexes:

• users_email_key
• users_pkey

---

user

Better Auth user accounts - main user table for authentication

RLS Policies:

• Users can update their own profile — UPDATE for {public}
• Users can view their own profile — SELECT for {public}

Indexes:

• idx_user_stripe_customer
• idx_user_stripe_subscription
• idx_user_subscription_expires
• idx_user_subscription_status
• idx_user_subscription_tier
• user_email_key
• user_pkey
• user_username_key

---

account

OAuth and social provider linked accounts

RLS Policies:

• Users can delete their own linked accounts — DELETE for {public}
• Users can view their own linked accounts — SELECT for {public}

Indexes:

• account_pkey
• account_provider_provider_account_id_key

---

session

Active user sessions with expiration management

RLS Policies:

• Users can delete their own sessions — DELETE for {public}
• Users can view their own sessions — SELECT for {public}

Indexes:

• idx_session_active
• session_pkey
• session_token_key

---

passkey

WebAuthn/Passkey credentials for passwordless authentication

RLS Policies:

• Users can manage their own passkeys — ALL for {public}

Indexes:

• passkey_credential_id_key
• passkey_pkey

---

magic_link

Magic link tokens for passwordless email authentication

RLS Policies:

• Users can view own magic links — SELECT for {public}

Indexes:

• idx_magic_link_user_id
• magic_link_pkey
• magic_link_token_key

---

verification

Email verification and password reset tokens

RLS Policies:

• Users can view their own verification tokens — SELECT for {public}

Indexes:

• verification_pkey

---

two_factor

Two-factor authentication settings and backup codes

RLS Policies:

• Users can update their own 2FA settings — UPDATE for {public}
• Users can view their own 2FA settings — SELECT for {public}

Indexes:

• two_factor_pkey
• two_factor_user_id_key

---

profiles

User profiles with RLS enabled. Users can only access their own profile unless they are admin/superadmin.

RLS Policies:

• profiles_delete_own — DELETE for {authenticated}
• profiles_insert_own — INSERT for {authenticated}
• profiles_select_own — SELECT for {authenticated}
• profiles_service_role_all — ALL for {service_role}
• profiles_update_own — UPDATE for {authenticated}

Indexes:

• idx_profiles_email
• idx_profiles_id_lookup
• idx_profiles_role
• profiles_pkey
• profiles_username_key

---

user_roles

User role assignments and trading limits for RBAC

RLS Policies:

• Admins can update user roles — UPDATE for {public}
• Users can view their own role — SELECT for {public}

Indexes:

• user_roles_pkey
• user_roles_user_id_key

---

role_permissions

Permission mappings for each role

RLS Policies:

• Everyone can view role permissions — SELECT for {public}

Indexes:

• role_permissions_pkey
• role_permissions_role_permission_key

---

admin_roles

Tracks admin users and their permissions

RLS Policies:

• Admins can view all admin roles — SELECT for {authenticated}
• Super admins can delete admin roles — DELETE for {authenticated}
• Super admins can grant admin roles — INSERT for {authenticated}
• Super admins can update admin roles — UPDATE for {authenticated}

Indexes:

• admin_roles_pkey
• admin_roles_user_id_role_type_key

---

admin_allowlist

Allowlist of email addresses authorized for admin access

RLS Policies:

• Admin allowlist viewable by admins — SELECT for {public}
• Only super admin can delete allowlist — DELETE for {public}
• Only super admin can insert allowlist — INSERT for {public}
• Only super admin can update allowlist — UPDATE for {public}

Indexes:

• admin_allowlist_email_key
• admin_allowlist_pkey

---

user_sessions

RLS Policies:

• rls_user_sessions_delete — DELETE for {public}
• rls_user_sessions_insert — INSERT for {public}
• rls_user_sessions_select — SELECT for {public}
• rls_user_sessions_update — UPDATE for {public}

Indexes:

• idx_user_sessions_user_id
• idx_user_sessions_user_status
• user_sessions_pkey

---

supabase_sessions

RLS Policies:

• supabase_sessions_delete_own — DELETE for {public}
• supabase_sessions_insert_own — INSERT for {public}
• supabase_sessions_select_own — SELECT for {public}
• supabase_sessions_update_own — UPDATE for {public}

Indexes:

• ix_supabase_sessions_user_id
• supabase_sessions_pkey

---

supabase_users

RLS Policies:

• supabase_users_delete_own — DELETE for {public}
• supabase_users_insert_own — INSERT for {public}
• supabase_users_select_own — SELECT for {public}
• supabase_users_update_own — UPDATE for {public}

Indexes:

• ix_supabase_users_email
• supabase_users_pkey

---

trusted_devices

Stores trusted device information for "remember this device" MFA functionality. Devices are trusted for 30 days.

RLS Policies:

• Users can create their own trusted devices — INSERT for {public}
• Users can delete their own trusted devices — DELETE for {public}
• Users can update their own trusted devices — UPDATE for {public}
• Users can view their own trusted devices — SELECT for {public}

Indexes:

• idx_trusted_devices_expiry
• idx_trusted_devices_expiry_cleanup
• idx_trusted_devices_lookup
• idx_trusted_devices_user_fingerprint
• trusted_devices_pkey
• trusted_devices_user_id_device_fingerprint_key

---

signup_attempts

Log of all signup attempts for rate limiting and fraud detection

RLS Policies:

• Admins can view signup attempts — SELECT for {public}
• Auth admin can insert signup attempts — INSERT for {supabase_auth_admin}

Indexes:

• signup_attempts_pkey

---

password_history

Password history for rotation policy enforcement

RLS Policies:

• Users can view own password history — SELECT for {public}

Indexes:

• password_history_pkey

---

password_failed_verification_attempts

Track failed password attempts for rate limiting and account protection

RLS Policies:

• Admins or users can view password failures — SELECT for {public}
• Auth admin can insert password failures — INSERT for {supabase_auth_admin}

Indexes:

• idx_password_failed_verification_attempts_user_id
• password_failed_verification_attempts_pkey

---

user_preferences

RLS Policies:

• Users can manage their own preferences — ALL for {public}

Indexes:

• user_preferences_pkey

---

user_permissions

User-specific permission overrides

RLS Policies:

• Admins can delete user permissions — DELETE for {public}
• Admins can insert user permissions — INSERT for {public}
• Admins can update user permissions — UPDATE for {public}
• Admins or users can view permissions — SELECT for {public}

Indexes:

• idx_user_permissions_granted_by
• idx_user_permissions_user_id
• user_permissions_pkey
• user_permissions_user_id_permission_key

---

user_views

Stores user-specific view configurations and preferences

RLS Policies:

• Users can create their own views — INSERT for {public}
• Users can delete their own views — DELETE for {public}
• Users can update their own views — UPDATE for {public}
• Users can view their own views — SELECT for {public}

Indexes:

• user_views_pkey
• user_views_user_id_view_name_key

---

user_notifications

In-app notifications shown in the notification center

RLS Policies:

• Service role can create notifications — INSERT for {service_role}
• Users can update their own notifications — UPDATE for {public}
• Users can view their own notifications — SELECT for {public}

Indexes:

• idx_user_notifications_created_at
• idx_user_notifications_read_at
• idx_user_notifications_type
• idx_user_notifications_user_id
• idx_user_notifications_user_unread
• user_notifications_pkey

---

push_subscriptions

Web Push notification subscriptions for browser notifications

RLS Policies:

• Service role can manage push subscriptions — ALL for {service_role}
• Users can create their own push subscriptions — INSERT for {public}
• Users can delete their own push subscriptions — DELETE for {public}
• Users can update their own push subscriptions — UPDATE for {public}
• Users can view their own push subscriptions — SELECT for {public}

Indexes:

• idx_push_subscriptions_created_at
• idx_push_subscriptions_endpoint
• idx_push_subscriptions_user_id
• push_subscriptions_endpoint_key
• push_subscriptions_pkey

---

user_devices

Tracks user devices for security, device trust, and suspicious activity detection

RLS Policies:

• Admins can manage all devices — ALL for {public}
• Service role can insert devices — INSERT for {public}
• Users can update own devices — UPDATE for {public}
• Users can view own devices — SELECT for {public}

Indexes:

• idx_user_devices_fingerprint
• idx_user_devices_is_trusted
• idx_user_devices_last_seen
• idx_user_devices_suspicious
• idx_user_devices_user_id
• unique_user_device
• user_devices_pkey

---

device_login_audit

Comprehensive audit log of all authentication attempts with device and location tracking

RLS Policies:

• Admins can view all audit logs — SELECT for {public}
• Service role can insert device audit logs — INSERT for {public}
• Users can view own audit logs — SELECT for {public}

Indexes:

• device_login_audit_pkey
• idx_device_login_audit_device_id
• idx_device_login_audit_ip
• idx_device_login_audit_new_device
• idx_device_login_audit_session
• idx_device_login_audit_status
• idx_device_login_audit_suspicious
• idx_device_login_audit_user_id

---

role_change_log

Audit log for role changes

RLS Policies:

• Admins or users can view role changes — SELECT for {public}

Indexes:

• idx_role_change_log_changed_by
• role_change_log_pkey

---

allowed_email_domains

Whitelist of allowed email domains (optional - if empty, all non-blocked domains allowed)

RLS Policies:

• Admins can manage allowed email domains — ALL for {public}

Indexes:

• allowed_email_domains_domain_key
• allowed_email_domains_pkey
• idx_allowed_email_domains_added_by

---