Skip to main content

​
Introduction to the Users Table

The Users table is designed to store and manage user information within the Serial application. This table plays a crucial role in user authentication, authorization, and profile management across the system.

​
Table Structure

The Users table is structured to efficiently store user data and establish relationships with other key entities. Here’s a detailed breakdown of its columns:
Column NameData TypeConstraintsDescription
supabase_uiduuidprimary keyUnique identifier for each user
company_iduuidforeign key, nullableReference to the company the user belongs to
created_attimestamp with time zonenot null, default now()Timestamp of user creation
is_activebooleannot null, default trueIndicates if the user account is active
emailtextnot nullUser’s email address
first_nametextnullableUser’s first name
last_nametextnullableUser’s last name
roletextnot null, foreign keyUser’s role in the system
default_station_iduuidforeign key, nullableReference to the user’s default station
titletextnullableUser’s job title or position

​
Usage and Functionality

The Users table is designed to be the central repository for user information and relationships. Here are some key points about its usage:
  1. User Authentication and Authorization: The table stores essential information used for authenticating users and determining their access rights within the application. The role column, which references the user_types table, is crucial for implementing role-based access control.
  2. Company Association: Users are associated with companies through the company_id foreign key. This allows for organization-specific data segregation and access control.
  3. Default Station Assignment: The default_station_id allows users to have a predefined station, which can be used to streamline workflows or set default views in the application.
  4. Profile Management: With fields like first_name, last_name, email, and title, the table supports comprehensive user profile management.

​
Notes

  • A trigger function named handle_new_user is associated with this table to process new user data: 
    CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
  raw_user_meta_data json;
  raw_app_meta_data json;
  company_id uuid;
  role text;
begin
  raw_user_meta_data := new.raw_user_meta_data;
  raw_app_meta_data := new.raw_app_meta_data;

  company_id := coalesce(
    (raw_user_meta_data ->> 'company_id')::uuid,
    (raw_app_meta_data ->> 'serial_company_id')::uuid,
    '51bbe6c0-c335-4e45-92bd-5d6ed99de5b6'::uuid
  );

  role := coalesce(
    raw_user_meta_data ->> 'role',
    raw_app_meta_data ->> 'serial_role',
    'MEMBER'
  );

  raw_app_meta_data := raw_app_meta_data ||
    json_build_object('serial_company_id', company_id, 'serial_role', role);

  raw_user_meta_data := raw_user_meta_data - 'company_id' - 'role';

  new.raw_app_meta_data = raw_app_meta_data;
  new.raw_user_meta_data = raw_user_meta_data;

  insert into public.users (supabase_uid, company_id, email, first_name, last_name, role, title)
  values (
    new.id,
    company_id,
    new.email,
    raw_user_meta_data ->> 'first_name',
    raw_user_meta_data ->> 'last_name',
    role,
    raw_user_meta_data ->> 'title'
  );

  return new;
end;
$function$
  • The table has undergone migrations, notably from using auth0_uid to supabase_uid as the primary identifier, indicating an evolution in the authentication system.
  • There’s a cascading delete relationship with the auth.users table, ensuring data consistency across the authentication and application layers.
By leveraging the Users table, the Serial application efficiently manages user identities, roles, and relationships, providing a solid foundation for authentication, authorization, and user-centric features throughout the system.