Skip to main content

​
Introduction to the Unique_Identifiers Table

The unique_identifiers table is designed to store and manage information about individual items or components within the Serial system. This table plays a crucial role in tracking the lifecycle, status, and relationships of uniquely identifiable objects across various processes and operations.

​
Table Structure

The unique_identifiers table is structured to capture comprehensive details about each unique identifier. Here’s a detailed breakdown of its columns:
Column NameData TypeConstraintsDescription
iduuidprimary keyUnique identifier for the record
identifiertextnot nullThe actual unique identifier string
company_iduuidnot null, foreign keyReference to the company owning this identifier
component_iduuidnot null, foreign keyReference to the associated component
created_attimestamp with time zonenot null, default now()Timestamp of record creation
statustextforeign keyCurrent status of the identifier
metadatajsonbFlexible field for additional data
last_updated_attimestamp with time zonedefault now()Timestamp of the last update
completed_attimestamp with time zoneTimestamp when the identifier was marked as completed
work_order_iduuidforeign keyReference to the associated work order
part_number_iduuidforeign keyReference to the associated part number
latitudedouble precisionGeographical latitude of the identifier
longitudedouble precisionGeographical longitude of the identifier
latest_process_entry_iduuidforeign keyReference to the latest process entry for this identifier

​
Usage and Functionality

The unique_identifiers table is designed to be the central repository for tracking individual items throughout their lifecycle. Here are some key points about its usage:
  1. Lifecycle Tracking: The table captures the creation, updates, and completion of identifiers, allowing for comprehensive lifecycle management.
  2. Status Management: The status field, linked to the unique_identifier_status_types table, enables efficient tracking of an identifier’s current state (e.g., WIP, COMPLETE, DEFECTIVE).
  3. Relational Connections: Through various foreign keys, the table establishes relationships with companies, components, work orders, part numbers, and process entries, creating a web of interconnected data.
  4. Geolocation Support: The latitude and longitude fields allow for geographical tracking of identifiers, which can be crucial for logistics and inventory management.
  5. Flexible Metadata: The jsonb metadata field provides a way to store additional, schema-less information about the identifier, accommodating diverse use cases.

​
Notes

  • The table is central to many operations in the Serial system, including the sn_lookup_page function, which retrieves detailed information about an identifier and its genealogy.
  • The latest_process_entry_id field allows for quick access to the most recent process information without the need for complex joins or subqueries.
  • Example usage in TypeScript: 
    interface UniqueIdentifier {
  id: string;
  status: UniqueIdentifierStatus | null;
  company_id: string;
  created_at: TimestamptzString;
  identifier: string;
  completed_at: TimestamptzString | null;
  component_id: string;
  // ... other fields
}
By leveraging the unique_identifiers table, the Serial application can efficiently track, manage, and analyze individual items throughout their lifecycle, providing a robust foundation for inventory management, process tracking, and supply chain operations.