Profile table

The Profile table represents user profiles in the system. It stores essential user information and preferences.

Database schema

create table public.profile (
  id uuid not null default gen_random_uuid (),
  first_name text null,
  last_name text null,
  phone_number text null,
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone null,
  deleted_at timestamp with time zone null,
  settings jsonb null,
  user_id uuid null,
  email text null,
  avatar text null,
  last_sign_in_at timestamp with time zone null,
  birthdate date null,
  invite_code text null,
  locale text not null default 'en'::text,
  selected_space_id uuid null
)

Key fields

• id: Unique identifier for the profile (UUID)
• first_name, last_name: User's name components
• email: User's email address
• phone_number: User's contact number
• settings: JSON field storing user preferences (includes period_range)
• user_id: Foreign key to auth.users table
• avatar: URL to user's profile picture
• locale: User's preferred language (defaults to 'en')
• selected_space_id: Currently selected space for the user
• invite_code: Unique code for inviting others
• birthdate: User's date of birth
• last_sign_in_at: Timestamp of last authentication

Timestamps

• created_at: Automatically set on record creation
• updated_at: Automatically updated via trigger
• deleted_at: Soft deletion timestamp

Database triggers

1. handle_updated_at: Updates the updated_at timestamp on record modification
2. on_profile_created: Automatically creates a space for new profiles

TypeScript Interface

interface Profile extends Entity {
  avatar?: string;
  email?: string;
  firstName?: string;
  fullName?: string;
  lastName?: string;
  phoneNumber?: string;
  userId?: string;
  settings?: {
    periodRange: Range;
  };
  birthdate?: string;
  lastSignInAt?: string;
  inviteCode?: string;
  locale: string;
  selectedSpaceId?: string;
}

Key operations

The profile system supports several key operations through server actions:

Queries

1. getProfiles(): Retrieves all profiles
2. getProfileByUserId(userId): Finds profile by user ID
3. getProfileByEmail(email): Finds profile by email
4. getProfilePeriodRange(profileId): Gets user's period range preference

Mutations

1. updateProfile(profile): Updates profile information
2. updateProfileField({profileId, fieldName, fieldValue}): Updates a single profile field

Settings management

The profile's settings field is a JSONB column that currently stores:

• periodRange: User's preferred time range for viewing data

Example of updating period range:

UPDATE public.profile
SET settings = jsonb_set(COALESCE(settings, '{}'), '{period_range}', to_jsonb(period_range))
WHERE id = profile_id;

Relationships

• One-to-One with auth.users through user_id
• One-to-Many with space table (a profile can own multiple spaces)
• Referenced in space_member view for space membership information