Tables, schemas, and edge functions used by the authentication system
The Hypnoelp authentication system uses three database tables and one Supabase edge function.
| Table |
Purpose |
Key Fields |
auth.users |
Supabase built-in authentication table |
id, email, encrypted_password, email_confirmed_at, user_metadata |
public.user_profile |
Application user profiles with comprehensive data |
user_id (FK to auth.users), first_name, last_name, email, phone_number, country, created_at, updated_at, lead_source |
public.app_logs |
Application logging for signup tracking |
id, user_id, log_type, message, origin, created_at, updated_at |
| Column |
Type |
Description |
| user_id |
UUID (PK, FK) |
Primary key, references auth.users.id |
| first_name |
TEXT |
User first name |
| last_name |
TEXT |
User last name |
| email |
TEXT |
Normalized lowercase email |
| phone_number |
TEXT |
Phone with country code (e.g. +639686258155) |
| country |
TEXT |
User country (e.g. “Philippines”) |
| lead_source |
TEXT |
How user discovered the app (nullable) |
| created_at |
TIMESTAMPZ |
Account creation time |
| updated_at |
TIMESTAMPZ |
Last profile update time |
| Column |
Type |
Description |
| id |
UUID (PK) |
Primary key |
| user_id |
UUID (FK) |
References auth.users.id |
| log_type |
TEXT |
Type of log entry (e.g. sign_up) |
| message |
TEXT |
Log message describing the event |
| origin |
TEXT |
Always “app” for application logs |
| created_at |
TIMESTAMPZ |
Log entry creation time |
| updated_at |
TIMESTAMPZ |
Last update time for status changes |
| Stage |
Log Message |
Action |
| Initial Signup |
“Waiting for email confirmation” |
INSERT new log entry |
| Email Confirmed |
“Email confirmed and account created successfully” |
UPDATE existing log entry |
| Profile Creation Failed |
“Failed to create user profile: [error details]” |
UPDATE existing log entry |
| Function |
Purpose |
Input |
Action |
delete-unlinked-user |
Clean up auth users without user_profile records |
email address |
Deletes orphaned auth.users record using admin privileges |
- Normal State: Both auth.users record AND user_profile record exist with matching user_id
- Pending State: Only auth.users record exists (unconfirmed email) — user_profile created after confirmation
- Cleanup State: Unlinked auth users (no matching user_profile) are automatically deleted during signup
- Self-Healing: Unlinked user_profiles (no matching auth user) are updated with new auth user_id during signup