> ## 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.

# Log File Schemas

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

## Introduction to the Log\_File\_Schemas Table

The log\_file\_schemas table is designed to store and manage schema information related to log files for different companies. This table plays a crucial role in the system by maintaining structured representations of log file formats, enabling efficient parsing and analysis of log data.

## Table Structure

The log\_file\_schemas table is structured to support versioning and company-specific schema management. Here's a detailed breakdown of its columns:

| Column Name         | Data Type   | Constraints                          | Description                                         |
| ------------------- | ----------- | ------------------------------------ | --------------------------------------------------- |
| id                  | uuid        | primary key (composite)              | Unique identifier for the schema                    |
| revision            | integer     | primary key (composite), not null    | Version number of the schema                        |
| company\_id         | uuid        | foreign key, not null                | Reference to the company owning the schema          |
| sample\_file\_data  | jsonb       |                                      | JSON representation of the schema structure         |
| sample\_file\_name  | text        | not null                             | Name of the sample file used to generate the schema |
| sample\_file\_id    | uuid        |                                      | Optional reference to the stored sample file        |
| created\_at         | timestamptz | not null, default current\_timestamp | Timestamp of schema creation                        |
| source\_dataset\_id | uuid        | foreign key                          | Reference to the source dataset used for the schema |

## Usage and Functionality

The log\_file\_schemas table is designed to be flexible and support evolving schema definitions. Here are some key points about its usage:

1. **Versioning System**: The table implements a revision system using a composite primary key (id, revision). This allows for multiple versions of a schema to be stored and tracked over time.

2. **Company-Specific Schemas**: Each schema is associated with a specific company through the company\_id foreign key. This ensures that schemas are isolated between different companies using the system.

3. **JSON Schema Storage**: The sample\_file\_data column uses the jsonb data type, allowing for flexible and efficient storage of schema structures in JSON format.

4. **Dataset Linkage**: The source\_dataset\_id column links each schema to its source dataset, enabling traceability and data lineage.

5. **Access Control**: Row-level security policies are implemented to restrict access based on user roles and company affiliation:
   * Only users with the ADMIN role can insert, update, or delete schemas.
   * All authenticated users from the same company can view (select) the schemas.

## Notes

* The table structure has evolved over time, with columns being renamed and added to better suit the application's needs.

* A unique constraint is enforced through a trigger function to ensure that no two rows can have the same source\_dataset\_id with different id values:

  ```sql
  CREATE OR REPLACE FUNCTION check_unique_source_dataset_id()
  RETURNS TRIGGER AS $$
  BEGIN
    IF EXISTS (
      SELECT 1 FROM log_file_schemas
      WHERE source_dataset_id = NEW.source_dataset_id
        AND id != NEW.id
    ) THEN
      RAISE EXCEPTION 'Duplicate source_dataset_id found';
    END IF;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER enforce_unique_source_dataset_id
  BEFORE INSERT OR UPDATE ON log_file_schemas
  FOR EACH ROW EXECUTE FUNCTION check_unique_source_dataset_id();
  ```

* The table is designed to work closely with the datasets table, allowing for a clear relationship between raw data and its corresponding schema.

By leveraging the log\_file\_schemas table, the Serial application can efficiently manage and version log file schemas for multiple companies, enabling robust log parsing and analysis capabilities while maintaining data isolation and access control.