Industrial production traceability system and method based on a graph database

Abstract

The application provides an industrial production tracing system and method based on a graph database, comprising: inputting an initial tracing code into a pre-constructed graph database, taking product codes as nodes in the graph database, taking material conversion relations corresponding to the product codes between two nodes as edges, forming a tracing link, and storing all process information corresponding to the product codes in the form of a time sequence array in the attributes of the nodes; traversing all nodes associated downstream of the initial tracing code in the tracing link, taking the node with the largest amount of process information as an anchor point, and respectively performing forward tracing and reverse tracing to obtain a complete production process map of the initial tracing code. The traditional SQL recursive query mode is abandoned, the natural path traversal characteristics of the graph database are utilized, cross-material and cross-process rapid tracing is realized, and the problems of high query delay and poor concurrent performance of existing solutions are solved. Starting from any tracing code, the corresponding forward and reverse full-quantity produced process set is obtained at a second level, and the real-time tracing demand is met.

Description

Technical Field

[0001] This invention relates to an industrial production traceability system and method based on graph databases, belonging to the field of digital production technology in industrial manufacturing. Background Technology

[0002] In the field of digital manufacturing execution (MES) in industrial manufacturing, production traceability is a core function for ensuring product quality and achieving full lifecycle management. Its core requirement is to quickly and completely query the entire product flow process from raw material input and processing to finished product output through traceability identifiers such as product codes and material codes, providing data support for quality control, problem identification, and compliance auditing. Currently, mainstream production traceability solutions in the industry are all based on relational database management systems (RDBMS, such as MySQL and Oracle).

[0003] In this traditional approach, tracing production processes requires using product codes as the core, involving frequent join operations across multiple business tables (such as process route tables, key process tables, traceability relationship tables, process record tables, and material ledger tables). Because industrial production processes inherently possess complex network-like relationships—specifically, many-to-many relationships between materials and processes (one material can be used in multiple processes, and one process can consume multiple materials), conversions of product codes at different production stages (e.g., raw material code → semi-finished product code → finished product code), and deep nested parent-child nodes (e.g., a finished product consists of multiple semi-finished products, and each semi-finished product is processed from multiple raw materials)—the flat table structure of traditional relational databases cannot directly map these complex topological relationships. Therefore, traditional solutions require writing complex SQL recursive statements to implement multi-level nested queries when performing end-to-end traceability. As production scale expands and data volume surges (e.g., hundreds of thousands of process records and millions of product codes are generated daily), this model leads to a significant decrease in query efficiency, excessive database load, and even query timeouts. It is difficult to meet the performance requirements of real-time traceability (e.g., quickly locating problematic processes when production lines are abnormal) and high-concurrency queries (e.g., multiple departments simultaneously querying traceability information for different products) in industrial production. At the same time, the complex SQL statements also increase the cost of system development and maintenance.

[0004] The following problems exist in the existing technology: Query performance bottleneck: Relational databases perform too many join operations when processing multi-level BOMs and cross-process paths, resulting in response delays.

[0005] The traceability logic is complex: it requires manual handling of national standard code merging and redundant sorting and filtering logic via SQL, resulting in high development and maintenance costs.

[0006] Data representation is not intuitive: The material flow and process connection in industrial production have a natural "network" characteristic, while the flat table structure of relational databases can only indirectly reflect this relationship through field associations. It cannot intuitively restore the topology of the entire production chain, making it difficult to quickly understand the traceability results and hindering problem investigation. Summary of the Invention

[0007] This invention provides an industrial production traceability system and method based on graph database, which solves the problems disclosed in the background art.

[0008] To solve the above-mentioned technical problems, the technical solution adopted by the present invention is as follows: An industrial production traceability system based on graph database includes: graph database and dynamic anchor point relocation traceability module; The graph database uses product codes as nodes, and the material conversion relationship between two nodes corresponding to product codes as edges to form a traceability link. All process information corresponding to the product code is stored in the attributes of the node in the form of a time-series array. The dynamic anchor point relocation traceability module is used to input the initial traceability code and execute traceability tasks on the traceability link based on the initial traceability code. The traceability process includes: Step 1: Traverse all associated nodes downstream of the initial traceability code in the traceability chain, and take the node with the largest amount of process information as the anchor point; if the node where the initial traceability code is located has the largest amount of process information, then directly use that node as the anchor point, and take the process information of that node as the first traceability result to step 3. Step 2: Starting from the anchor point, perform forward tracing and reverse tracing respectively to obtain the second tracing result; Step 3: Deduplicate and merge the first or second traceability results respectively; Step 4: Fill in the time sequence information of the first or second traceability result after deduplication and merging, and output the corresponding full-dimensional traceability result.

[0009] The technical effects achieved by the above system are as follows: This invention constructs a hybrid storage and query architecture: breaking through the limitations of the existing single storage architecture, it adopts a hybrid architecture of "graph database + columnar storage big data engine"; the graph database (such as NebulaGraph) is responsible for complex relationship modeling and path traversal, undertaking the core function of full-link traceability; the columnar storage big data engine (such as StarRocks) is responsible for storing detailed production parameters (such as process temperature, pressure, material usage, etc.), and queries are performed in conjunction with the graph database through product code and process code, realizing the separation of "relationship traceability" and "parameter query", which not only ensures traceability performance, but also takes into account the storage needs of massive production parameters, achieving a balance between performance and storage capacity.

[0010] To address the problem of incomplete traceability information caused by fixed anchor points in existing traceability schemes, this invention designs a dynamic anchor point relocation traceability algorithm. After the initial traceability code is input, the algorithm automatically finds the downstream end product code (i.e., the product code with the most complete process and the most associated materials) in the chain through forward traversal (GO 1 TO N(8) STEPS) as the traceability anchor point. Based on this, bidirectional recursion is performed (forward traceability of downstream processing processes and reverse traceability of upstream raw materials / processes) to ensure the integrity of traceability information and avoid the problem of "broken chain".

[0011] Furthermore, step 3 involves deduplication and merging using the SET operation.

[0012] The technical effects achieved by the above system are: simplifying process information, making it easier for users to understand, and reducing data storage.

[0013] Furthermore, the full-dimensional traceability results include: process name, start time, end time, corresponding product code, and material conversion relationship.

[0014] The technical effects achieved by the above system are: supplementing the precise time information of each process, constructing a complete production timeline, and providing more accurate data support for quality inspection and process optimization.

[0015] Furthermore, the time sequence array of process information includes: process code, start timestamp, and end timestamp.

[0016] The technical advantages achieved by the above system are as follows: Unlike the redundant model of "creating one node for each process" in existing graph database traceability schemes, this invention does not create independent graph nodes for each detailed process. Instead, it stores all process details corresponding to a single product code in the attributes of the product node as a time-series array (processCodeList), with each data entry in the array formatted as [process code:start timestamp:end timestamp]. This modeling method significantly reduces the total number of nodes in the graph database (by more than 80%), effectively avoiding the risk of storage explosion caused by a surge in data volume. Furthermore, the built-in operators of the graph database allow for rapid parsing of the time-series array, without affecting the query efficiency of process information.

[0017] Furthermore, the method of using the node with the largest amount of process information as the anchor point in step 1 includes: filtering all downstream product nodes obtained through traversal, including the initial traceability code itself, extracting the time series array of each node, cleaning redundant data through the cleaned_list function, and then using the split function to extract the code of the last process in the time series array of each node. Sort the code of the last process in all time series arrays in descending order of production time, locate the product code of the downstream process with the most information, that is, the product code of the final process with the highest priority and the most complete time series array, and use it as the anchor point.

[0018] The technical effect achieved by the above system is that it can quickly and accurately screen anchor points.

[0019] Furthermore, in step 1, all associated nodes downstream of the initial traceability code are traversed up to 8 levels to adapt to the process hierarchy of industrial production. The number of code-changing processes is set according to the number of code-changing processes, which is the maximum level of the graph data.

[0020] The technical effects achieved by the above system are as follows: This invention provides a high-performance query engine based on graph paths: The high-performance query engine is built using nGQL statements, a special query language for graph databases, which supports 1 to N-step deep penetration traversal. It can directly parse the association relationship between product nodes and edges without complex association operations, and achieve second-level traceability across materials and processes (query response time ≤ 500ms), which is significantly better than traditional SQL recursive queries (response time is usually ≥ 3s).

[0021] Furthermore, in the traceability link, the edges through which the traceability code flows are marked with a first color, and the nodes through which the traceability code passes are marked with a second color.

[0022] The technical effects achieved by the above system are: intuitively restoring the topology of the entire production chain, making it easier for users to quickly understand the traceability results, and facilitating problem investigation.

[0023] A second aspect of this invention provides an industrial production traceability method based on graph databases: The initial traceability code is input into a pre-built graph database, which uses product codes as nodes and material conversion relationships corresponding to product codes between two nodes as edges to form traceability links. All process information corresponding to the product code is stored in the attributes of the node in the form of a time-series array. The tracing process, which uses a pre-built dynamic anchor relocation tracing algorithm to perform tracing tasks on the tracing link based on the initial tracing code, includes: Step 1: Traverse all associated nodes downstream of the initial traceability code in the traceability chain, and take the node with the largest amount of process information as the anchor point; if the node where the initial traceability code is located has the largest amount of process information, then directly use that node as the anchor point, and take the process information of that node as the first traceability result to step 3. Step 2: Starting from the anchor point, perform forward tracing and reverse tracing respectively to obtain the second tracing result; Step 3: Deduplicate and merge the first or second traceability results respectively; Step 4: Fill in the time sequence information of the first or second traceability result after deduplication and merging, and output the corresponding full-dimensional traceability result.

[0024] The technical effect achieved by the above method is that the method of the present invention can query all product codes corresponding to a specified process, and is used for batch tracing of all related products of a certain process.

[0025] The beneficial effects achieved by this invention are as follows: (1) Improve cross-process traceability efficiency: Abandon the traditional SQL recursive query mode, and use the natural path traversal characteristics of graph database to realize fast traceability across materials and processes, and solve the problems of high query latency and poor concurrency performance of existing solutions.

[0026] (2) Simplify the data model: Construct a graph model with product code as the core node and material conversion relationship as the edge, which directly maps the network topology of industrial production, eliminating the need for complex SQL recursion and reducing system development and maintenance costs.

[0027] (3) Optimize the entire chain response: Starting from any traceability code (raw material code, semi-finished product code, finished product code), the corresponding forward (downstream processing) and reverse (upstream raw material / process) full set of produced processes can be obtained in seconds to meet the real-time traceability requirements. Attached Figure Description

[0028] Figure 1 This is a schematic diagram of the graph database nodes and edges in this invention; Figure 2 This is a schematic diagram of the complete production process output by the present invention; Figure 3 This is a schematic diagram of step 1 of the traceability process of the present invention; Figure 4 This is a schematic diagram of step 2 of the traceability process of the present invention; Figure 5 This is a schematic diagram of steps 3 and 4 of the traceability process of the present invention. Detailed Implementation

[0029] The present invention will be further described below with reference to the accompanying drawings. The following embodiments are only used to more clearly illustrate the technical solution of the present invention, and should not be used to limit the scope of protection of the present invention. Example 1

[0030] This invention provides an industrial production traceability method based on a graph database. Firstly, it is based on a pre-constructed graph database, such as... Figure 1As shown, the graph database uses a single product code as a node, and all the corresponding process details are stored in the attributes of the product node in the form of a time-series array (processCodeList). The material conversion relationship between two nodes corresponding to the product codes is used as an edge, thus forming a traceability link in the graph database. A high-performance query engine is built using the graph database-specific query language nGQL, which supports 1 to N-step depth-through traversal and can directly parse the association relationship between product nodes and edges.

[0031] The process of constructing the graph database includes: The node label is uniformly set to `productcode`, used to identify all types of products (raw materials, semi-finished products, and finished products), eliminating the need to distinguish between node types and simplifying model complexity. A unique product identifier (i.e., product code, such as raw material code A001, semi-finished product code B001, and finished product code C001) serves as the unique primary key of the node. The time-series array (`processCodeList`) is stored in the format of [process code: start timestamp: end timestamp], recording all processes experienced by the product code and their corresponding time information (e.g., [P01:2025-11-09 03:45:34, P02:2025-11-09 04:45:34], where P01 is the process code, and 2025-11-09 03:45:34 is the completion time of the product code in process P01). By using a "single node carrying multiple processes" approach, the number of nodes is reduced, avoiding storage explosion; the time-series array format facilitates rapid parsing of the time sequence and relationships of processes, aligning with the temporal characteristics of industrial production.

[0032] The edge label is uniformly named outin, which is used to describe the material conversion relationship between two product nodes (i.e., the output code of the previous process is used as the input code of the next process).

[0033] outcode: Output code, which is the ID of the starting node of the edge (the output product code of the previous code transformation process).

[0034] incode: Input code, i.e., the ID of the endpoint node of the edge (the input product code for the next code-changing process).

[0035] By directly linking product nodes in adjacent production stages using outcode and incode, no additional association tables are needed. This intuitively presents the network relationship of material flow and provides a foundation for path traversal.

[0036] like Figures 3-5 As shown, based on the graph database constructed above, product production graph traceability is performed through the following process; Step 1: Determine the anchor point using a dynamic anchor point relocation and tracing algorithm; After the user enters the initial traceability code (product code), the forward traversal is executed through the nGQL statement: GO 1 TO N(8) STEPS FROM initial traceability code OVER outin YIELD id($$) AS downstreamCode, traversing all associated product nodes downstream of the initial traceability code (maximum traversal of 8 levels, adapting to the conventional process level of industrial production, which can be set according to the number of code-changing processes in the MES system, that is, the number of code-changing processes is the maximum level of the graph data, and the number of graph data traversal steps will not exceed this number).

[0037] All downstream product nodes obtained through iteration (including the initial traceability code itself) are filtered, and the time sequence array (processCodeList) attribute of each node is extracted. Redundant data is cleaned using the cleaned_list function (invalid and duplicate processes are removed), and then the split function is used to extract the last process code of each node (the code of the last process in the time sequence array).

[0038] All end-process codes are sorted in descending order of production time. The "product code of the process with the most downstream information" is located, that is, the product code of the end process with the highest priority and the most complete time sequence array (processCodeList), which serves as the core anchor point for subsequent traceability (this anchor point is the product code corresponding to the current last process in the traceability code product flow process).

[0039] Special case handling: If the initial traceability code is in its own time sequence array (processCodeList), and the final process has the highest priority (i.e., it is itself the product code of the largest downstream process), then the initial traceability code is used directly as the anchor point, and step 3 is entered.

[0040] Step 2: Bidirectional path topology reconstruction; Backtracking: Starting from the downstream maximum process product code, the process is traversed along the outin relationship to extract the set of processes involved upstream of the downstream maximum process product code and remove duplicates, thus obtaining a complete production process map of the traceability code.

[0041] Step 3: Status merging and process activation; The system uses the SET operation to deduplicate and merge the process sets obtained from the above reverse, forward, and current node matching (MATCH). For example... Figure 2 As shown, the front end highlights the "blue" processes (processes through which all product codes upstream and downstream of the traceability code have flowed) based on the merged process list, and uses the current product code's time sequence array (processCodeList) to cover the "orange" production status (processes that the traceability code itself has gone through).

[0042] Step 4: Complete the timing information by supplementing the precise timing information of each process and constructing a complete production timing diagram to provide more accurate data support for quality inspection and process optimization.

[0043] For each process merged in step 3, the nGQL statement `MATCH (p:productcode) WHERE process code IN p.processCodeList RETURN id(p), p.processCodeList` is used to match the product node corresponding to that process. The start and end timestamps corresponding to that process in the time sequence array (processCodeList) are parsed, and the timestamps are converted to a standard time format (such as YYYY-MM-DD HH:MM:SS). This is then associated with the corresponding process, ultimately constructing a complete production time sequence graph, which includes full-dimensional traceability results such as "process name, start time, end time, corresponding product code, and material conversion relationship". Example 2

[0044] Based on the operational logic of the above method, the present invention also provides an industrial production traceability system based on graph database, which is used to query all product codes corresponding to a specified process (such as querying all raw material, semi-finished product, and finished product codes corresponding to process P01) and to trace all related products of a certain process in batches.

[0045] Querying a graph database using nGQL statements, with a specific example: GO 1 TO 8 STEPS FROM "A001" OVER outin WHERE properties($$).processCodeList is not null AND properties($$).processCodeList contains "P01" YIELD id($$) as product_code | ORDER BY $-.product_code No need to join multiple tables, filter directly by node attribute, query response time ≤300ms.

[0046] The graph database utilizes the high-performance OLAP analysis database Starrocks to store relevant production parameter information, separating the graph data from the specific parameter information. Actual queries can be performed using product codes and process-related queries, reducing the pressure on the graph database. While ensuring the main traceability graph function, it can stably and quickly guarantee the query of production process parameter information.

[0047] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the technical principles of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

[0048] The above are merely embodiments of the present invention and are not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention are included within the scope of the claims of the present invention pending approval.

Claims

1. An industrial production traceability system based on graph database, characterized in that, include: Graph database and dynamic anchor point relocation and tracing module; The graph database uses product codes as nodes, and the material conversion relationship between two nodes corresponding to product codes as edges to form a traceability link. All process information corresponding to the product code is stored in the attributes of the node in the form of a time-series array. The dynamic anchor point relocation traceability module is used to input the initial traceability code and execute traceability tasks on the traceability link based on the initial traceability code. The traceability process includes: Step 1: Traverse all associated nodes downstream of the initial traceability code in the traceability chain, and take the node with the largest amount of process information as the anchor point; if the node where the initial traceability code is located has the largest amount of process information, then directly use that node as the anchor point, and take the process information of that node as the first traceability result to step 3. Step 2: Starting from the anchor point, perform forward tracing and reverse tracing respectively to obtain the second tracing result; Step 3: Deduplicate and merge the first or second traceability results respectively; Step 4: Fill in the time sequence information of the first or second traceability result after deduplication and merging, and output the corresponding full-dimensional traceability result.

2. The industrial production traceability system based on graph database according to claim 1, characterized in that, Step 3 uses the SET operation to remove duplicates and merge them.

3. The industrial production traceability system based on graph database according to claim 1, characterized in that, The full-dimensional traceability results include: process name, start time, end time, corresponding product code, and material conversion relationship.

4. The industrial production traceability system based on graph database according to claim 1, characterized in that, The time sequence array of process information includes: process code, start timestamp, and end timestamp.

5. The industrial production traceability system based on graph database according to claim 1, characterized in that, The method of using the node with the largest amount of process information as the anchor point in step 1 includes: filtering all downstream product nodes obtained by traversal, including the initial traceability code itself, extracting the time series array of each node, cleaning redundant data through the cleaned_list function, and then using the split function to extract the code of the last process in the time series array of each node. Sort the code of the last process in all time series arrays in descending order of production time, locate the product code of the downstream process with the most information, that is, the product code of the final process with the highest priority and the most complete time series array, and use it as the anchor point.

6. The industrial production traceability system based on graph database according to claim 1, characterized in that, Step 1 involves traversing all associated nodes downstream of the initial traceability code, up to a maximum of 8 levels, to adapt to the process hierarchy of industrial production. This is set according to the number of code-changing processes, which is the maximum level of the graph data.

7. The industrial production traceability system based on graph database according to claim 1, characterized in that, In the traceability link, the edges through which the traceability code flows are marked with a first color, and the nodes through which the traceability code passes are marked with a second color.

8. An industrial production traceability method based on graph databases, characterized in that: The initial traceability code is input into a pre-built graph database, which uses product codes as nodes and material conversion relationships corresponding to product codes between two nodes as edges to form traceability links. All process information corresponding to the product code is stored in the attributes of the node in the form of a time-series array. The tracing process, which uses a pre-built dynamic anchor relocation tracing algorithm to perform tracing tasks on the tracing link based on the initial tracing code, includes: Step 1: Traverse all associated nodes downstream of the initial traceability code in the traceability chain, and take the node with the largest amount of process information as the anchor point; if the node where the initial traceability code is located has the largest amount of process information, then directly use that node as the anchor point, and take the process information of that node as the first traceability result to step 3. Step 2: Starting from the anchor point, perform forward tracing and reverse tracing respectively to obtain the second tracing result; Step 3: Deduplicate and merge the first or second traceability results respectively; Step 4: Fill in the time sequence information of the first or second traceability result after deduplication and merging, and output the corresponding full-dimensional traceability result.

Images