Skip to main content

​
Introduction to the Report_Templates Table

The report_templates table is designed to store and manage various types of report templates within the Serial application. This table plays a crucial role in organizing, versioning, and controlling access to different reporting configurations across the system.

​
Table Structure

The report_templates table is structured to accommodate diverse report types while maintaining version control and access management. Here’s a detailed breakdown of its columns:
Column NameData TypeConstraintsDescription
iduuidnot null, default uuid_generate_v4()Unique identifier for each template
revisionbigintnot nullVersion number of the template
company_iduuidnot nullAssociated company identifier
typetextnot nullType of report template
nametextnot nullName of the template
descriptiontextOptional description of the template
created_attimestamp with time zonenot null, default now()Timestamp of template creation
created_byuuidnot nullUser identifier of the creator
last_edited_attimestamp with time zonedefault now()Timestamp of last edit
last_edited_byuuidUser identifier of the last editor
is_publicbooleannot null, default falseFlag indicating if the template is publicly accessible
shared_withjsonbnot null, default ''::jsonbJSON object storing sharing permissions
configjsonbnot null, default ''::jsonbJSON object storing template configuration
is_latest_revisionbooleannot null, default falseFlag indicating if this is the latest revision

​
Usage and Functionality

The report_templates table is designed to be flexible and secure, accommodating various reporting needs within the Serial application. Here are some key points about its usage:
  1. Version Control: The table implements a revision system, allowing multiple versions of a template to be stored. The revision column, along with the is_latest_revision flag, helps manage different iterations of a template.
  2. Access Control: Through the is_public flag and the shared_with JSON column, the table provides granular control over who can access and use each template. This is further reinforced by row-level security policies.
  3. Template Diversity: The type column allows for different kinds of report templates, such as GridBuilderView, GraphBuilderView, SnLookupPdfTemplate, and Dashboard. The config JSON column stores the specific configuration for each template type.

​
Notes

  • The table uses triggers to automatically increment the revision number and update the last_edited_at timestamp when changes are made.
  • Row-level security is implemented with policies that restrict access based on user roles and company associations. For example: 
    create policy "Insert - Data - SameCompany"
on "public"."report_templates"
as permissive
for insert
to authenticated
with check ((company_id IN ( SELECT privileges.company_id
   FROM privileges
  WHERE (privileges.supabase_uid = auth.uid()))));
  • The table has foreign key constraints linking to the companies and users tables, ensuring data integrity across the database.
By leveraging the report_templates table, the Serial application provides a robust and flexible system for creating, managing, and sharing various types of report templates across different companies and users, while maintaining version control and enforcing proper access restrictions.