Skip to main content

​
Introduction

The Serial codebase utilizes a variety of database functions to manage data, enforce business logic, and generate insights. These functions play a crucial role in maintaining data integrity, performing calculations, and supporting various features of the application. In this article, we’ll explore the different categories of database functions and their purposes within the Serial system. The exact implementation of each function can be found in the supabase dashboard. This is generally easier than trying to find the most recent update in the database migrations.

​
Data Insertion and Validation

Several functions in the Serial codebase are responsible for handling data insertion and ensuring data validity:
  • before_insert_component_process_links: This function likely performs pre-insertion checks or modifications for component-process relationships.
  • before_insert_processes: Handles pre-insertion logic for new processes.
  • check_unique_source_dataset_id: Ensures the uniqueness of source dataset IDs, preventing duplicate entries.
  • insert_process_with_references: Inserts a new process along with its associated references, maintaining data consistency.

​
Data Deletion and Cleanup

The codebase includes functions for removing data and performing cleanup operations:
  • delete_component: Removes a component from the database.
  • delete_process: Deletes a process and potentially its associated data.
  • delete_test_companies: Cleans up test company data, likely used in development or testing environments.

​
Calculation and Aggregation Functions

Several functions perform calculations or aggregate data:
  • calculate_cycle_time_by_component: Computes cycle time metrics for components.
  • calculate_cycle_time_by_process: Calculates cycle time metrics for processes.
  • component_counts: Likely provides count statistics for components.
  • identifier_counts: Aggregates counts related to identifiers.

​
Process and Component Management

The Serial codebase includes functions for managing processes and components:
  • update_component_instance_status: Updates the status of a component instance.
  • update_process_entry: Modifies an existing process entry.
  • update_process_revision: Handles versioning for processes.
  • get_process_with_references: Retrieves a process along with its related data.

​
Reporting and Insights

Several functions are dedicated to generating reports and insights:
  • insight_uid_count_by_process: Provides unique identifier counts per process.
  • insight_yield_by_process: Calculates yield metrics for processes.
  • insight_yield_by_time: Computes yield metrics over time.
  • stations-graph-entires: Generates data for station-related graphs.

​
Utility Functions

The codebase includes various utility functions for specific operations:
  • increment_report_templates_revision: Manages versioning for report templates.
  • increment_work_order_number: Generates or updates work order numbers.
  • update_timestamp: Updates timestamp information, possibly for auditing or tracking purposes.
  • handle_new_user: Performs actions when a new user is added to the system.

​
Conclusion

The database functions in the Serial codebase cover a wide range of operations, from data management and calculations to insights and utility tasks. These functions work together to maintain data integrity, enforce business rules, and provide the necessary functionality for the Serial application. By leveraging these database functions, the Serial system can efficiently manage complex data relationships, perform real-time calculations, and generate valuable insights for its users. As the application evolves, these functions may be updated or expanded to support new features and requirements.