Users

​

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:

​

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.