> ## Documentation Index
> Fetch the complete documentation index at: https://docs.serial.okos.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Users

> An in-depth look at the structure and purpose of the `users` table in the Serial database

## 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 Name          | Data Type                | Constraints             | Description                                  |
| -------------------- | ------------------------ | ----------------------- | -------------------------------------------- |
| supabase\_uid        | uuid                     | primary key             | Unique identifier for each user              |
| company\_id          | uuid                     | foreign key, nullable   | Reference to the company the user belongs to |
| created\_at          | timestamp with time zone | not null, default now() | Timestamp of user creation                   |
| is\_active           | boolean                  | not null, default true  | Indicates if the user account is active      |
| email                | text                     | not null                | User's email address                         |
| first\_name          | text                     | nullable                | User's first name                            |
| last\_name           | text                     | nullable                | User's last name                             |
| role                 | text                     | not null, foreign key   | User's role in the system                    |
| default\_station\_id | uuid                     | foreign key, nullable   | Reference to the user's default station      |
| title                | text                     | nullable                | User'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:

  ```sql
  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.