Skip to main content

​
Introduction to the Companies Table

The Companies table is designed to store and manage essential information about organizations using the Serial application. This table serves as a cornerstone for the entire database structure, forming the basis for user associations, data ownership, and access control across the system.

​
Table Structure

The Companies table is structured to efficiently capture key company details while allowing for flexible configuration. Here’s a detailed breakdown of its columns:
Column NameData TypeConstraintsDescription
iduuidprimary key, not null, default uuid_generate_v4()Unique identifier for each company
created_attimestamp with time zonenot null, default now()Timestamp of when the company record was created
nametextnot nullThe name of the company
is_activebooleannot null, default falseIndicates whether the company is currently active in the system
configjsonbnot null, default ''JSON object storing company-specific configuration settings

​
Usage and Functionality

The Companies table is designed to be the central reference point for company-related data and permissions. Here are some key points about its usage:
  1. Data Ownership and Access Control: The company_id is used extensively throughout other tables (such as users, components, processes) to associate data with specific companies. This forms the basis for row-level security policies that ensure users can only access data belonging to their company.
  2. Flexible Configuration: The config column, using JSONB type, allows for storing a wide range of company-specific settings without needing to alter the table structure. This can include flags for features like use_operator_pins, allow_wip_linking, or enable_location_tracking.
  3. User Management: New users are initially associated with a “NULL COMPANY” before being properly assigned, facilitating a smooth onboarding process.

​
Notes

  • The Companies table implements row-level security, ensuring that even at the database level, data access is restricted based on user permissions and company associations.
  • There’s a utility function to delete test companies, which can be useful in development and testing environments: 
    CREATE OR REPLACE FUNCTION delete_test_companies()
RETURNS void AS $$
BEGIN
  DELETE FROM public.companies WHERE name LIKE 'test-company%';
END;
$$ LANGUAGE plpgsql;
  • The is_active flag allows for soft-deactivation of companies without deleting their data, which can be crucial for maintaining historical records or temporarily suspending access.
By leveraging the Companies table, the Serial application establishes a robust foundation for multi-tenancy, ensuring data isolation between different organizations while providing flexible configuration options to cater to diverse company needs.