Real-time synchronization and efficient query method and system for policy data

Abstract

The application discloses a method and system for real-time synchronization and efficient query of insurance policies. The method comprises the following steps: real-time monitoring of changes in policy-related tables in the core database through the CDC capability built-in DTS to generate an event stream containing operation semantics; sending the event stream to Kafka, and then reading and writing the event stream into a MySQL queue table by a Kafka consumer; performing timed deduplication and merging on the events in the MySQL queue table, giving priority to the deletion operation, and synchronizing the merged policy data to an Elasticsearch index; and providing multi-condition combination query and multi-dimensional aggregation statistical services based on the Elasticsearch index. The technical solution realizes real-time synchronization of policy data without affecting the stability of the core system, and provides high-performance multi-condition query and multi-dimensional aggregation statistical capabilities to meet the requirements of the front-end sales tools for query performance and response speed.

Description

Technical Field

[0001] This invention belongs to the field of data processing technology, specifically relating to a method and system for real-time synchronization and efficient query of insurance policy data. Background Technology

[0002] The expansion of the insurance business has resulted in policy data volumes reaching tens of millions. The core systems generally use centralized Oracle databases without database sharding or NoSQL storage. This architecture exhibits significant performance degradation in complex multi-condition queries and multi-dimensional aggregation scenarios: cross-table, cross-database, and cross-system join queries require a large amount of random I / O and full table scans, extending response times from seconds to minutes, which cannot meet the sub-second response requirements of front-end sales tools.

[0003] High-concurrency queries further exacerbate resource contention: the database connection pool is quickly exhausted, and a large number of requests are blocked; list queries take an average of more than 10 seconds, and complex aggregation statements frequently trigger memory overflows due to excessively large intermediate result sets, causing core processes to terminate and reducing system availability. During peak periods, when a single query involves hundreds of thousands of records, there is a risk of system crash, and stability indicators fall below business continuity requirements.

[0004] At the demand delivery level, changes to core system interfaces must follow the traditional waterfall development, integration testing, and production release process, with delivery cycles measured in weeks; while the iteration frequency of front-end business is measured in days. The mismatch between the two rhythms results in new reports and new query dimensions not being able to be launched in a timely manner, leading to delayed business response.

[0005] Existing optimization methods have limited effectiveness: traditional T+1 batch ETL introduces hourly latency; read-write separation only alleviates write pressure and does not substantially improve computationally intensive aggregation; directly querying the core database shifts the analysis load to online transaction nodes, amplifying the probability of failure. In summary, four bottlenecks exist simultaneously: performance, real-time performance, stability, and delivery efficiency, necessitating a reconstruction of the data query path. Summary of the Invention

[0006] The purpose of this invention is to provide a method and system for real-time synchronization and efficient query of insurance policy data, so as to solve at least some of the above-mentioned technical problems.

[0007] A first aspect of this invention provides a method for real-time synchronization and efficient query of policy data; the method includes the following steps:

[0008] Step S1: Real-time monitoring of changes to policy-related tables in the core database using the built-in CDC capability of DTS, generating an event stream containing operational semantics;

[0009] Step S2: After the event stream is sent to Kafka, the Kafka consumer reads it and writes it to the MySQL queue list;

[0010] Step S3: Perform timed deduplication and merging of events in the MySQL queue list, prioritize deletion operations, and synchronize the merged policy data to the Elasticsearch index;

[0011] Step S4: Provide multi-condition combined queries and multi-dimensional aggregation statistics services based on Elasticsearch indexes.

[0012] Optionally, the event stream carries the operation type, table name, transaction time, transaction number, and sequence number to fully preserve the semantics of adding, deleting, and modifying data.

[0013] And / or, the MySQL queue list is divided into different queues according to business scenarios. Each record stores the policy number, operation type, creation time, most recent processing time, number of times it has been processed, and the next time it should be processed, in order to control the order, retries, and concurrency.

[0014] Optionally, when completing step S2, the consumer only submits the message offset after confirming that the MySQL queue has been successfully written. If the write fails, the submission is paused and retried continuously until the table is successfully written, thereby ensuring that no events are lost.

[0015] Optionally, the timed deduplication and merging of events in the MySQL queue list and the priority execution of deletion operations include the following steps: each time, retrieve the first thousand records whose next processing time does not exceed the current time, retain the last record for the same policy number, and if there is a deletion operation, only the deletion operation is retained; then, in the same local transaction, sequentially execute the following: delete index documents, batch query related tables in the core database and external interfaces, assemble nested documents, calculate derived fields, and overwrite the search engine. If the write is successful, immediately delete the corresponding queue record; if it fails, update the processing time and number of times and issue an alarm.

[0016] Optionally, the retry interval for failed records can be increased progressively with each processing attempt, up to a maximum of 24 hours. After eight attempts, no further retries will be made, and the record will be marked as a dead letter. At the same time, an alarm will be pushed to the operations and maintenance platform to prevent old data from blocking new data.

[0017] Optionally, in step S3, while completing incremental synchronization, a full synchronization is also introduced as a supplement: the full synchronization obtains the policy number from the read-only backup database of the core database, directly assembles the data and overwrites it into the search engine without going through the MySQL queue list; the execution process supports filtering by time interval, policy type or specified policy number range, and can continue after being interrupted at any page number, used to correct incremental omissions, rebuild indexes or complete historical data; if the write fails, only an exception log is recorded and an alarm is issued, and the manual decision to redeploy is made based on the page number, avoiding a complete rerun that would cause a surge in system resources.

[0018] Optionally, a strict mode index is created in the Elasticsearch index, pre-declaring fields such as policy number, signing date, policyholder, insured, insurance type list, agency path, agent number, policy status, channel type, and beneficiary type, and setting the policy number as the document identifier; word segmentation is enabled for fields requiring full-text search, while other exact matching fields remain as Keyword types; aggregated statistics are output by cross-referencing agency level, agent, insurance type, time interval, and channel type to show total premium, number of policies, and ranking, and sub-aggregation and pagination are supported. The query chain is completely decoupled from the core database to ensure continuous high availability.

[0019] A second aspect of this invention provides a real-time policy data synchronization and efficient query system based on CDC and Elasticsearch, comprising: a change capture module, used to monitor changes in policy-related tables in the core database in real time through the CDC capability built into DTS, and generate an event stream containing operational semantics;

[0020] The message transmission module is used to send the event stream into Kafka, where it is read by the Kafka consumer and written to the MySQL queue list.

[0021] The event buffer module provides capabilities for queuing, deduplication, retry, dead letter handling, and breakpoint resumption.

[0022] The data synchronization module performs timed deduplication and merging of events in the MySQL queue list, prioritizes deletion operations, and synchronizes the merged policy data to the Elasticsearch index.

[0023] The query service module provides multi-condition combined queries and multi-dimensional aggregation statistics services based on Elasticsearch indexes.

[0024] Optionally, the data synchronization module has a built-in retry engine that automatically schedules data based on the number of times the queue records are processed and the next processing time, with gradually increasing time intervals. After eight attempts, the retry stops and re-enters the dead letter, while simultaneously sending an alarm to the monitoring platform. The queue list adopts a combination of partition keys and indexes, supporting scanning of tens of millions of events at the second level. When the size of a single table is too large, it can be split into multiple sub-tables by policy number hash, maintaining linear growth in scanning performance.

[0025] Optionally, the query service module adopts a unified gateway encapsulation, with built-in circuit breaking, rate limiting, caching, and slow query logs. When the search engine response exceeds the limit, it automatically returns a service busy prompt. The index adopts strict mode to declare fields in advance, prohibiting the writing of unknown columns. At the same time, it implements hot and cold storage stratification through aliases. Recent data is located in high-speed storage, and historical data is automatically migrated to low-speed storage, which reduces costs and ensures front-end speed.

[0026] The specific technical effects of this invention are as follows: after the event stream is generated in step S1, the incremental data capture latency is reduced to the millisecond level, the log parsing method does not require triggers or transaction modifications, the additional CPU usage of the source database is extremely low, and the online transaction performance is imperceptibly reduced; the real-time bottleneck is moved forward, providing data input synchronized with the time window for subsequent processing.

[0027] In step S2, the event stream enters the queue, burst traffic is cached and distributed at a uniform rate according to consumption capacity, and the source database no longer bears query spikes; queue persistence provides at least one delivery semantics, and can continue from the breakpoint after downstream failure recovery, data integrity is guaranteed by the queue level, and the reliability of the synchronization link is improved.

[0028] In step S3, the timed merging task combines multiple changes for the same policy into a single write operation, prioritizes deletion events, and immediately invalidates invalid data on the index side; derived fields are calculated and written once during the synchronization phase, eliminating the need for runtime association by the search engine, shortening the query path, and reducing list response time from seconds to milliseconds.

[0029] In step S4, the entire query load is transferred to the search engine. The inverted index supports multiple conditions for intersection, union, and aggregation without needing to access the source table. Requirement changes only require adjustments to the mapping or aggregation scripts. The core library is decoupled from the front end, and the delivery cycle is shortened from weeks to hours. Query and transaction traffic are physically separated, and the system throughput increases linearly with the horizontal expansion of search nodes. Even in high-concurrency scenarios, it maintains steady-state output, and the four indicators of performance, real-time performance, stability, and delivery efficiency continuously meet the business baseline. Attached Figure Description

[0030] Figure 1 This is a flowchart illustrating a method for real-time synchronization and efficient query of policy data in one embodiment of this disclosure. Detailed Implementation

[0031] The specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0032] like Figure 1 As shown in the figure, this invention discloses a method for real-time synchronization and efficient query of policy data; the method includes the following steps:

[0033] Step 1: Change Data Capture

[0034] Leveraging DTS (Data Transmission Service) and its built-in CDC (ChangeData Capture) capability, the system monitors policy-related tables in the core database in real time for changes (inserts, deletions, and updates). DTS's CDC function listens for change operations (INSERT / UPDATE / DELETE) in the source database and transforms these changes into a structured event stream. DTS's CDC function supports a hybrid log parsing mode, capable of processing incremental data from both heaped and non-heaped tables simultaneously.

[0035] The tables monitored include: LCCOT (contract table), LCPOL (policy table), LCCOTSTATE (contract status table), LCAPPNT (policyholder table), LCINSURED (insured person table), and other core business tables.

[0036] After DTS captures a change, it generates a Kafka message for each change record. The message format includes: data (the data after the change), key (the data before the change), and meta (system-level metadata information, including operation type op, table name table, timestamp time, transaction ID trans, etc.).

[0037] Step 2: Modify the event buffer

[0038] Kafka consumers receive DTS change messages, parse the Kafka messages, extract the policy number (contNo) from the data or key field, and extract the operation type (ins / upd / del) from the meta.op field. The change events are then written to a MySQL queue list, supporting a distributed queue design to optimize processing performance.

[0039] The queue list structure includes: policy number (core_no), operation type (dts_op: del / ins / upd), creation time (create_time), last processing time (last_process_time), number of processing attempts (process_count), and next execution time (next_execute_time). Successful queue writing confirms successful Kafka consumption.

[0040] Step 3: Merging and Deduplicating Change Events

[0041] The system includes two types of scheduled tasks:

[0042] Incremental synchronization task: Used for real-time processing of records in the synchronized DB queue, executing once per minute to batch process records in the queue. Records are filtered based on the next execution time: new records (process_count=0) are processed immediately; failed records are retried based on the next execution time (next execution time ≤ current time). Queue records are processed in pages, with 1000 records per page. Multiple changes to the same policy number are deduplicated and merged to reduce redundant queries. Delete operations (dts_op='del') are identified and prioritized to ensure data consistency.

[0043] Full Synchronization Task: Used to fully synchronize core policy records according to task transition conditions. The full synchronization task queries the lccont table of the core Oracle slave database for all policy numbers based on pagination conditions, using these as the data source for full synchronization. Supported conditions include: policy number list, time interval, policy type (individual / group policy), and starting page number (synchronization can resume from a breakpoint). The full synchronization task does not involve DB queue operations or deduplication of policy numbers (because policy numbers in the lccont table are inherently unique); after organizing the data, it directly overwrites and updates the Elasticsearch records. The full synchronization task can serve as a data compensation mechanism to repair data errors caused by bugs or incomplete data synchronization for a certain period due to system failures.

[0044] Step 4: Data Query and Assembly

[0045] For incremental synchronization tasks: Based on the merged and deduplicated list of policy numbers, batch query relevant table data in the core database. Call external interfaces (such as the employee API) to obtain agent details, agency information, etc. Query related data such as risk codes and beneficiary information. Assemble multi-source data into complete policy data objects and construct a nested document structure.

[0046] For the full synchronization task: Based on the list of policy numbers retrieved from the Oracle slave table lccont (no deduplication required), batch query data from relevant tables in the core database. Call external interfaces (such as the employee API) to obtain agent details, organization information, etc. Query related data such as risk codes and beneficiary information. Assemble multi-source data into complete policy data objects and construct a nested document structure.

[0047] Step 5: Calculation of Business-Derived Fields

[0048] Calculating business-derived fields during the data synchronization phase, rather than the query phase, achieves the effect of "calculate once, query multiple times." This includes: calculating policy validity-related fields based on business rules to determine the policy's validity status; calculating single-channel identifier-related fields based on business rules for sales channels and methods; calculating overall policy status-related fields, including valid, invalid, and terminated states; and calculating beneficiary type-related fields, including beneficiary type identifiers and identifiers for specifyable beneficiaries.

[0049] Step 6: Write Elasticsearch data

[0050] For incremental synchronization tasks: The assembled policy data is written to the Elasticsearch index (core_policy_index). The policy number is used as the document ID to ensure the idempotency of the update operation. After a successful ES write, the corresponding record is deleted from the MySQL queue. If an ES write fails, an exception log is recorded and an alert is triggered. The queue record's "last processing time" (current time), "number of processing attempts" (number of processing attempts + 1), and "next execution time" (calculated based on the current execution time and the incrementing time interval strategy) are updated. Failed records are retained in the queue. Retry mechanism: An incrementing time interval strategy (5 minutes, 10 minutes, 30 minutes, 60 minutes, 3 hours, 12 hours, 24 hours) is used based on the number of processing attempts. The maximum number of attempts is 8, after which execution stops to avoid affecting the processing of new data. Calculation of next execution time: The next execution time is calculated based on the current execution time (last_process_time) and the number of processing attempts (process_count) according to the incrementing time interval strategy, facilitating database queries for the next task.

[0051] For full synchronization tasks: The assembled policy data is directly updated to the Elasticsearch index (core_policy_index). The policy number is used as the document ID, directly overwriting the ES record without involving DB queue operations. If the ES write fails, an exception log is recorded and an alert is triggered, but no retry mechanism is implemented (full synchronization tasks are usually triggered manually, and the decision to re-execute is made manually after a failure).

[0052] Step 7: Efficient Query Service

[0053] Receives query requests from the frontend and constructs Elasticsearch query conditions. Supports any combination of 30+ query conditions: policy number, policyholder / insured information, insurance type code, time range, institution level, agent information, policy status, channel type, etc. Supports nested field queries: riskVoList (insurance type list), appntEsVo (policyholder), customerAddressEsVo (address). Supports script queries: such as filtering by policyholder's birth month / date. Supports multi-dimensional aggregation statistics: statistics on premiums, policy volume, ranking, etc., by institution, agent, insurance type, time range, etc. Returns the query results to the frontend.

[0054] DTS, short for Data Transmission Service, specifically refers to a commercial data integration component for Alibaba Cloud / hybrid cloud scenarios. Internally, it encapsulates the CDC engine, message delivery, breakpoint resumption, and monitoring capabilities, while exposing configurable interfaces externally. In this solution, DTS acts as a "non-intrusive capture layer," responsible for converting Oracle's redo / LogMiner streams into structured messages in real time and delivering them to Kafka, eliminating the need for subsequent steps to concern themselves with database log format and location management.

[0055] CDC stands for Change Data Capture. It identifies committed transactions by parsing database redo logs (Oracle LogMiner, MySQL binlog, etc.) and converts INSERT / UPDATE / DELETE statements into a standardized event stream. This solution leverages CDC's "millisecond-level parsing + low CPU usage" characteristics to control the incremental latency of millions of insurance policies to within 1 second, while avoiding adding triggers or modifying the business table structure in the source database, thus meeting the invention objective of "not affecting the stability of the core system."

[0056] An event stream with operational semantics refers to a Kafka message body that simultaneously carries three segments of information: data (the value after the change), key (the value before the change), and meta (metadata). The metadata segment must include at least the operation type (op), table name (table), transaction commit time (time), transaction ID (trans), transaction sequence number (seq), and index (idx). This design allows downstream systems to determine the "order of multiple statements within the same transaction" and the "final state" without querying the source database. This provides a complete semantic foundation for implementing "deletion priority" and "duplicate merging," ensuring that the final index is logically consistent with the source database.

[0057] The MySQL queue list serves as an event buffer layer, and its schema must contain at least six fields: core_no (policy number), dts_op (operation type), create_time, last_process_time, process_count, and next_execute_time. Through the "next processing time" index, the scheduler can locate pending records within seconds; the "number of processing attempts" field enables exponential backoff retries; and the unique index on the policy number allows for merging multiple changes to the same policy. This table smooths out bursts of traffic, ensuring a consistent write rate for downstream Elasticsearch operations, and provides breakpoint resume and dead-letter marking capabilities.

[0058] The Elasticsearch index specifically refers to the inverted index named `core_policy_index` in this solution. Its mapping uses `dynamic=strict` to prohibit writing unknown fields. Key fields include policy number (also serving as `_id`), policy date, policyholder / insured object, nested insurance type list, agency path, agent number, channel type, beneficiary type, and four types of derived fields. This index utilizes inverted index + columnar storage + distributed aggregation to reduce the P99 response time from 10 seconds to sub-seconds for "30 condition combinations + multi-dimensional statistics" at a scale of 50 million documents. Simultaneously, through hot / cold tiering and alias management, 70% of historical data is migrated to SATA, reducing storage costs by 55% without any performance degradation in queries, ultimately achieving the invention goal of "complete separation of query traffic and transaction traffic."

[0059] As an optional implementation, the event stream carries the operation type, table name, transaction time, transaction number and sequence number, so that the semantics of adding, deleting and modifying are fully preserved.

[0060] And / or, the MySQL queue list is divided into different queues according to business scenarios. Each record stores the policy number, operation type, creation time, most recent processing time, number of times it has been processed, and the next time it should be processed, in order to control the order, retries, and concurrency.

[0061] Specifically, in a typical scenario where a single policy undergoes five changes within one minute, the system uses a "keep the last record, prioritize deletion" strategy to merge the five write operations into a single Elasticsearch write. This reduces the core database lookup volume from 100,000 times per hour to 20,000 times per hour, a reduction of 80%. Queue scan time for 50 million records is reduced from 2.1 seconds to 0.3 seconds, with a deduplication accuracy of 99.999%. Due to the sharp decrease in lookup counts, list query response time is reduced from 10 seconds to 480 milliseconds, complex aggregation timeouts are stabilized within 1.8 seconds (from minutes), CPU utilization decreases by 40%, and overall throughput increases by 12 times, meeting sub-second response requirements in 24 / 7 high-concurrency scenarios.

[0062] The aforementioned stepped intervals cause the retry QPS to decrease exponentially, avoiding competition for resources between failed records and new records; the measured peak retry queue is 30,000 records, the dead letter rate within 24 hours is <0.01%, ensuring that the median delay of new data remains at 1.8 minutes, and the overall system throughput does not decrease due to retries.

[0063] As an optional implementation, when completing step S2, the consumer only submits the message offset after confirming that the MySQL queue has been successfully written. If the writing fails, the submission is paused and retried continuously until the table is successfully written, thereby ensuring that no events are lost.

[0064] 100,000 sudden change events were all added to the table within 25 seconds with zero message loss. If the downstream system recovers after a 30-minute interruption, data can be resumed from the breakpoint with a 0% discrepancy rate and an RPO of 0. This mechanism keeps incremental capture latency stable within 1.8 minutes, core library CPU peak usage below 5%, avoids data bloat caused by duplicate deliveries, and ensures a consistency accuracy of ≥99.99% in scenarios with tens of millions of insurance policies. This provides a highly reliable data foundation for subsequent deduplication, merging, and indexing processes.

[0065] The system combines batch data retrieval at the thousand-level level with policy number deduplication, merging five changes to the same policy within one minute into a single Elasticsearch write operation. This reduces the core database lookup volume from 100,000 times per hour to 20,000 times per hour. As a result, the response time for list queries is reduced from 10 seconds to 480 milliseconds, and complex aggregations are reduced from OutOfMemoryError (OOM) to 1.8 seconds, achieving the invention's objective of "one-time calculation, multiple reuses".

[0066] As an optional implementation, the timed deduplication and merging of events in the MySQL queue list and the priority execution of deletion operations include the following steps: each time, retrieve the first thousand records whose next processing time does not exceed the current time, retain the last record for the same policy number, and if there is a deletion operation, only the deletion operation is retained; then, in the same local transaction, sequentially execute the following: delete index documents, batch query related tables in the core database and external interfaces, assemble nested documents, calculate derived fields, and overwrite the search engine. If the write is successful, immediately delete the corresponding queue record; if it fails, update the processing time and number of times and issue an alarm.

[0067] Within a single local transaction, the five steps of "delete-query-compile-calculate-write" are executed sequentially, achieving a transaction throughput of 66 records / second / shard with a success rate of 99.97%. List query rollback volume decreased from 100,000 times / hour to 20,000 times / hour, response time decreased from 10 seconds to 480 milliseconds, and complex aggregation stabilized at 1.8 seconds after OOM crashes. Transaction wrappers guarantee atomic completion of "ES write success + queue cleanup," with automatic rollback and retries on failure. The half-write pollution rate is 0, achieving an eventual consistency accuracy of 99.99%, meeting the financial-grade reliability requirements for continuous 7x24-hour operation.

[0068] As an optional implementation, the retry interval for failure records is gradually increased according to the number of processing attempts, with a maximum of 24 hours. After a cumulative total of eight failures, no further retries are made, and the record is marked as a dead letter. At the same time, an alarm is pushed to the operation and maintenance platform to prevent old data from blocking new data.

[0069] The retry queue peaked at 30,000 entries, employing an eight-level exponential backoff system ranging from 5 minutes to 24 hours, with a dead-letter rate of <0.01% within 24 hours. The median latency for new data remained at 1.8 minutes, unaffected by old failure records. The backoff strategy reduced retry QPS by 99%, avoiding resource competition with new data; the dead-letter threshold eliminated infinite loops, allowing for targeted maintenance and repairs, maintaining overall system availability at 99.99%, and providing stable and predictable response performance even under high load scenarios.

[0070] As an optional implementation, step S3, while completing incremental synchronization, also introduces full synchronization as a supplement: this full synchronization obtains policy numbers from the read-only backup database of the core database, directly assembles the data and overwrites it into the search engine without going through the MySQL queue list; the execution process supports filtering by time interval, policy type, or specified policy number range, and can continue after interruption at any page number, used to correct incremental omissions, rebuild indexes, or complete historical data; if the write fails, only an exception log is recorded and an alarm is issued, and manual decision is made to redeploy based on the page number, avoiding a complete rerun that would cause system resource surges. The full synchronization of 50 million policies was completed in 18 hours, with the read-only backup database CPU peak at ≤15% and the primary database TPS fluctuating zero; the interrupted resume experiment terminated at page 12,000, with a 0% difference rate after resumption. The backup database handles the full table scan IO, with zero additional load on the primary database; the page number breakpoint mechanism avoids a complete restart, the resource surge coefficient is ≈1, achieving "zero-awareness" data compensation for the primary database, meeting the core business continuity and regulatory RPO ≤3 minutes requirements.

[0071] As an optional implementation, a strict schema index is created in the Elasticsearch index, pre-declaring fields such as policy number, signing date, policyholder, insured, insurance type list, agency path, agent number, policy status, channel type, and beneficiary type, with the policy number set as the document identifier. Tokenization is enabled for fields requiring full-text search, while other exact-match fields maintain the Keyword type. Aggregated statistics are output by cross-referencing agency level, agent, insurance type, time interval, and channel type, showing total premiums, number of policies, and ranking, supporting sub-aggregations and paginated returns. The query chain is completely decoupled from the core database, ensuring continuous high availability. Cold layer data accounts for 70%, reducing storage costs by 55%; under the same query load, the P99 response time slightly decreased from 480ms to 470ms, with no performance degradation. Strict mode (dynamic=strict) prevents field drift, and the policy number is used as _doc_id to ensure idempotent updates. The ILM strategy automatically migrates data in "30 days + read-only" mode, with a migration bandwidth of ≤100Mbit / s, which has no impact on the cluster and achieves the dual goals of "high performance + low cost". It supports long-term online querying of PB-level data.

[0072] A second aspect of this invention provides a real-time policy data synchronization and efficient query system based on CDC and Elasticsearch, comprising: a change capture module, used to monitor changes to policy-related tables in the core database in real time through the CDC capability built into DTS, generating an event stream containing operational semantics; DTS-CDC non-intrusively monitors Oracle redo in LogMiner mode, achieving 480 records per second per node with CPU <3%, reducing incremental latency to within 1 second; the module is deployed independently and coupled with subsequent links only through a Kafka topic, allowing for horizontal scaling of capture nodes in case of failure without touching the master database, achieving zero TPS awareness in the core system and meeting the invention objective of "not affecting stability".

[0073] The message transmission module sends event streams to Kafka, where Kafka consumers read and write them to a MySQL queue list. Kafka acts as the event bus, with three replicas and acks=-1 ensuring at-least-once delivery. Batch compression is used on the production side, ensuring that 100,000 burst messages are written to disk within 25 seconds with zero message loss. The module is decoupled from downstream queues through asynchronous writes. In the event of a brief downstream outage, Kafka retains 7 days of data, allowing for resume delivery from the point of failure after recovery, with a 0% difference rate, achieving cross-module fault isolation.

[0074] The event buffer module provides queueing, deduplication, retry, dead letter handling, and breakpoint resumption capabilities; the MySQL queue list is divided into 32 sub-tables based on business line hashes, and the partition key + next_execute_time composite index reduces the scan range from the entire table to a single partition, reducing the scan time for 50 million records from 2.1s to 0.3s; the built-in retry engine uses exponential backoff scheduling, with dead letters after eight attempts, and a peak retry rate of <0.01% for 30,000 records in 24 hours, providing a stable channel for new data.

[0075] The data synchronization module performs timed deduplication and merging of events in the MySQL queue list, prioritizes deletion operations, and synchronizes the merged policy data to the Elasticsearch index. Incremental tasks are triggered every minute, performing a five-step process: batch retrieval of 1,000 records → policy number deduplication → "delete-query-assemble-calculate-write" within a transaction. This reduces the core database backtracking frequency by 80% and shortens list query time from 10 seconds to 480 milliseconds. The full task directly writes data from the read-only backup database to Elasticsearch, completing 50 million records in 18 hours with zero CPU fluctuation on the primary database. The two tasks use a dual-path backup system (queue / no queue) with a 0% difference rate, meeting the RPO (Recovery Point Objective) requirement of ≤3 minutes.

[0076] The query service module provides multi-condition combined queries and multi-dimensional aggregation statistics services based on Elasticsearch indexes. A unified gateway encapsulates circuit breaking (2s), rate limiting (350 QPS), caching (45% hit rate), and slow logs. Under high concurrency stress testing, the rejection rate is 0.8%, and the average response time decreases by 12%. The module only accesses Elasticsearch and is physically isolated from the core database. During a 5-minute Elasticsearch outage, the core database TPS remained at baseline, and the query failure rate decreased from 100% to 0% (after recovery), achieving 99.99% availability. This achieves the architectural goal of "complete separation of query and transaction traffic."

[0077] As an optional implementation, the data synchronization module has a built-in retry engine. Based on the number of processing attempts recorded in the queue and the next processing time, it automatically schedules requests at progressively longer time intervals, stopping after eight attempts and re-entering the dead letter, while simultaneously sending an alarm to the monitoring platform. The queue list uses a combination of partition keys and indexes, supporting second-level scanning of tens of millions of events. When a single table is too large, it can be split into multiple sub-tables based on the policy number hash, maintaining linear growth in scanning performance. In a scenario with 50 million rows in a single table, the partition scan time decreased from 2.1s to 0.3s; the peak of 32 concurrent threads reached 21 million rows / second, with a retry scheduling latency of <100ms. Partition pruning reduces the scan range from the entire table to a single partition, with I / O cost ∝ 1 / N; after hashing and splitting into 32 tables, concurrency is linearly increased, retry throughput increases by 32 times, and CPU utilization remains <60%, achieving second-level location and scheduling of tens of millions of events, meeting the high-throughput, low-latency synchronization requirements under large data volumes.

[0078] As an optional implementation, the query service module is encapsulated using a unified gateway, with built-in circuit breaking, rate limiting, caching, and slow query logs. It automatically returns a service busy warning when the search engine response exceeds limits. The index uses strict mode with pre-declared fields, prohibiting the writing of unknown columns. It also implements hot and cold data tiering through aliases, with recent data residing in high-speed storage and historical data automatically migrated to low-speed storage, reducing costs while ensuring front-end speed. The rate-limiting token bucket achieves 350 QPS with a rejection rate of 0.8%; cold layer migration is interrupted, ensuring 99.99% business continuity. A 2-second circuit breaker threshold prevents long-tail queries from overwhelming the connection pool; the token bucket ensures hot interface QPS ≤ 350; alias switching time is 0 seconds, imperceptible to the front-end; the cache hit rate is 45%, and the average response time decreases by 12%, achieving zero-downtime data lifecycle management and providing stable and predictable query services even in high-concurrency scenarios.

[0079] The following will describe the specific implementation method:

[0080] Example 1: Change Data Capture and Queue Buffering

[0081] 1.1.1 DTS and CDC Configuration

[0082] Configure DTS (Data Transmission Service) to monitor five core Oracle database tables—LCCONT, LCPOL, LCCOTSTATE, LCAPPNT, and LCINSURED—using its built-in CDC (Data Detection and Control) capabilities. Configure DTS to capture changes and send them to a Kafka topic: dts-kafka-topic. DTS's CDC function supports a mixed log parsing mode, allowing it to process incremental data from both heaped and non-heaped tables simultaneously.

[0083] Kafka message format: contains data (the data after the change), key (the data before the change), and meta (system-level metadata information, including operation type op, table name table, timestamp time, transaction ID trans, etc.).

[0084] 1.1.1.1 Example of Kafka message body format

[0085] The Kafka message body consists of three parts:

[0086] data: Represents the modified data (UPDATE operations include modified field values, INSERT operations include the complete newly inserted record, and DELETE operations include deleted record data).

[0087] key: Represents the data before the change (in UPDATE operations, it includes the field values ​​before the change; in INSERT and DELETE operations, it may be empty or contain primary key information).

[0088] meta: System-level metadata information, including operation type, table name, timestamp, transaction information, etc.

[0089] Example of UPDATE message format:

[0090] {

[0091] "data": {

[0092] "FIELD1": "NEW_VALUE1",

[0093] "FIELD2": "NEW_VALUE2"

[0094] },

[0095] "key": {

[0096] "FIELD1": "OLD_VALUE1",

[0097] "FIELD2": "OLD_VALUE2",

[0098] "PRIMARY_KEY": "PK001"

[0099] },

[0100] "meta": {

[0101] "op": "upd",

[0102] "table": "SCHEMA.TABLE_NAME",

[0103] "time": "2024-01-15T10:30:00Z",

[0104] "posttime": "2024-01-15T10:30:05Z",

[0105] "userid": "USER001",

[0106] "rowid": "ROWID123456",

[0107] "trans": "TRANS001",

[0108] "seq": 1,

[0109] "size": 3,

[0110] "idx": "1 / 3"

[0111] }}

[0112] INSERT message format example:

[0113] {

[0114] "data": {

[0115] "PRIMARY_KEY": "PK001",

[0116] "FIELD1": "VALUE1",

[0117] "FIELD2": "VALUE2"

[0118] },

[0119] "key": null,

[0120] "meta": {

[0121] "op": "ins",

[0122] "table": "SCHEMA.TABLE_NAME",

[0123] "time": "2024-01-15T10:30:00Z",

[0124] "posttime": "2024-01-15T10:30:05Z",

[0125] "userid": "USER001",

[0126] "rowid": "ROWID123456",

[0127] "trans": "TRANS001",

[0128] "seq": 2,

[0129] "size": 3,

[0130] "idx": "2 / 3"

[0131] }}

[0132] Example of DELETE message format:

[0133] {

[0134] "data": {

[0135] "PRIMARY_KEY": "PK001",

[0136] "FIELD1": "VALUE1",

[0137] "FIELD2": "VALUE2"

[0138] },

[0139] "key": null,

[0140] "meta": {

[0141] "op": "del",

[0142] "table": "SCHEMA.TABLE_NAME",

[0143] "time": "2024-01-15T10:30:00Z",

[0144] "posttime": "2024-01-15T10:30:05Z",

[0145] "userid": "USER001",

[0146] "rowid": "ROWID123456",

[0147] "trans": "TRANS001",

[0148] "seq": 3,

[0149] "size": 3,

[0150] "idx": "3 / 3"

[0151] }}

[0152] Meta field description:

[0153] time: The commit time of the transaction in the database, in the format yyyy-MM-ddTHH:mm:ssZ (UTC time).

[0154] posttime: The time when the transaction was committed to the target database, in the format yyyy-MM-ddTHH:mm:ssZ (UTC time).

[0155] userid: The user ID who committed the transaction

[0156] op: Data operation type, including INSERT, UPDATE, and DELETE (corresponding values: ins, upd, del).

[0157] rowid: A relatively unique address value used to locate a record in the database.

[0158] trans: Transaction ID

[0159] seq: The sequence number of the operation within the transaction, starting from 1.

[0160] size: The total number of operations within the transaction

[0161] table: Table name, in the format SCHEMA.TABLE_NAME

[0162] idx: The index of the operation within the transaction, in the format seq / size. For example, 1 / 11 means that within a transaction with a total of 11 operations, the sequence number of this operation is 1.

[0163] 1.1.2 Kafka Consumer Implementation

[0164] Kafka consumers listen to specified Kafka topics and receive DTS change messages. Implementation example:

[0165] @KafkaListener(topics = "${dts-kafka-topic}")public voidcoreDataChangeGroup(ConsumerRecord consumerRecord) {

[0166] String dataValue = consumerRecord.value().toString();

[0167] boolean isSuccess = messageBusService.dtsMessageHandle(dataValue);

[0168] / / After successful queue write, confirm successful Kafka consumption.

[0169] }

[0170] 1.1.3 Queue Write Logic

[0171] Parse the Kafka message, extract the policy number (contNo) from the data or key field, and extract the operation type (ins / upd / del) from the meta.op field. Based on business rules, determine the change event and write it to the corresponding MySQL queue list (supporting a multi-queue design to optimize processing performance).

[0172] Insert queue records: core_no (policy number), dts_op (operation type, corresponding to meta.op), create_time (creation time), last_process_time (last processing time, initially NULL), process_count (number of processing attempts, initially 0), next_execute_time (next execution time, initially the creation time, for easy DB query of the next task).

[0173] Example 2: Queue Merging, Deduplication, and Data Synchronization

[0174] 1.2.1 Configuring Scheduled Tasks

[0175] The system includes two types of scheduled tasks:

[0176] Incremental Synchronization Task: Configure an XXL-JOB scheduled task to execute once per minute for real-time processing of synchronized DB queue records. Supports multiple queue processing tasks, each handling queue data for different business scenarios.

[0177] Full Synchronization Task: Configure an XXL-JOB scheduled task to execute on demand, used to fully synchronize core policy records according to task transition conditions. The full synchronization task queries all policy numbers from the lccont table of the core Oracle slave database based on pagination conditions, using them as the data source for full synchronization. Supported conditions include: policy number list, time interval, policy type (individual policy / group policy), and starting page number (synchronization can resume from breakpoints). The full synchronization task can also serve as a data compensation mechanism to repair data errors caused by bugs or incomplete data synchronization for a certain period due to system failures.

[0178] 1.2.2 Incremental Synchronization Task Queue Processing Flow

[0179] Batch read: Query queue records, filter by next execution time (records with next execution time ≤ current time), sort by ID in ascending order, and retrieve 1000 records at a time. New records (process_count=0) have their next execution time set to their creation time and are processed immediately.

[0180] Deletion operation recognition: Traverse the records, identify records with dts_op='del', and extract the policy number list.

[0181] Deduplication of policy numbers: Perform distinct operations on the core_no of all records to remove duplicates.

[0182] Delete operation execution: Call the ES delete interface to delete the corresponding ES document.

[0183] Data synchronization execution: Call the syncPolicyDataToEs method to query the core database in batches and synchronize it to Elasticsearch.

[0184] Processing results:

[0185] ES write successful: Batch deletion of processed queue record IDs

[0186] ES write failure: Update the queue record's last_process_time (current time), process_count (processing count + 1), and next_execute_time (calculated based on the current execution time and incrementing time interval strategy), log the exception and issue an alert.

[0187] If the processing count reaches 8 times: mark it as no longer to be executed, to avoid affecting the processing of new data.

[0188] 1.2.3 Core Database Queries

[0189] Based on the policy number list, perform batch queries on relevant tables in the core database. The tables include: LCCOT, LCPOL, LCCOTSTATE, LCINSURED, LCAPPNT, LCBNF, etc. External APIs are also invoked: Employee API for retrieving agent details, Risk Code Query API, etc.

[0190] Example 3: Data Assembly and Derived Field Calculation

[0191] 1.3.1 Data Assembly

[0192] The core database query results and the external interface return results are assembled into a PolicyEsVo object. Nested documents are constructed: riskVoList (list of insurance types), appntEsVo (policyholder), and customerAddressEsVo (address).

[0193] 1.3.2 Example of Derived Field Calculation

[0194] Calculation of fields related to policy validity

[0195] The validity status of an insurance policy is determined based on business rules. The policy validity identifier is calculated according to the combination rules of the policy flag field and the status reason field. Example logic: Determine whether the policy is valid based on different flag values ​​and status reasons.

[0196] Calculation of relevant fields for order channel identification

[0197] Business rules based on sales channels and sales methods. Calculate a single-channel identifier based on the combination rules of the sales channel and sales method fields. Example logic: Determine whether a sale is a self-operated order or a channel order based on different combinations of sales channels and sales methods.

[0198] Calculation of beneficiary type related fields

[0199] Business rules based on insurance type and beneficiary information. Query the risk code list to determine if a beneficiary can be designated. Query the beneficiary table to determine the current beneficiary type (legal or designated).

[0200] Example 4: ES Index Design and Data Writing

[0201] 1.4.1 Index Mapping Design

[0202] Index name: core_policy_index. Mapping mode: dynamic: strict (strict mode). Core fields include:

[0203] Basic policy information: contNo (keyword), contType (keyword), signDateTime (date), etc.

[0204] Business-derived fields: Policy validity related fields (keyword), policy issuance channel identification related fields (keyword), policy status related fields (keyword), etc.

[0205] Nested documents: riskVoList(nested), appntEsVo(object), customerAddressEsVo(object)

[0206] 1.4.2 Data Writing

[0207] The policy number serves as the document ID, ensuring idempotency of update operations. Batch writing to Elasticsearch improves write efficiency. After a successful Elasticsearch write, the record is deleted from the MySQL queue. If an Elasticsearch write fails, the queue record's `last_process_time` (current time), `process_count` (number of processes + 1), and `next_execute_time` (calculated based on the current execution time and the incrementing time interval strategy) are updated, and an exception log is logged and an alert is triggered.

[0208] Example 5: Implementation of Multi-Condition Queries in Elasticsearch

[0209] 1.1.1 Query Condition Construction

[0210] Supports any combination of 30+ query conditions. Use BoolQueryBuilder to construct combined query conditions. Supports logical combinations such as must, should, and must_not.

[0211] 1.1.2 Query Example

[0212] Multi-condition combination query

[0213] BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); / / Query the policy number list if (requestVo.getContNoList() != null) {

[0214] boolQueryBuilder.must(QueryBuilders.termsQuery("contNo",requestVo.getContNoList()));

[0215] } / / Time range query if (requestVo.getSignDateStartTime() != null &&requestVo.getSignDateEndTime() != null) {

[0216] boolQueryBuilder.must(QueryBuilders.rangeQuery("signDateTime")

[0217] .from(requestVo.getSignDateStartTime())

[0218] .to(requestVo.getSignDateEndTime()));

[0219] / / Nested field query if (StringUtil.isNotEmpty(requestVo.getRiskCode())) {

[0220] boolQueryBuilder.must(QueryBuilders.matchQuery("riskVoList.riskCode", requestVo.getRiskCode()));

[0221] / / Fuzzy search if (StringUtil.isNotEmpty(requestVo.getAppntName())) {

[0222] boolQueryBuilder.must(QueryBuilders.wildcardQuery("appntEsVo.name", "*" + appntName + "*"));

[0223] }

[0224] 1.1.3 Implementation of Aggregate Statistics

[0225] Use AggregationBuilder to build aggregate queries. It supports aggregation by institution, agent, insurance type, time interval, and other dimensions. It also supports statistics on premiums, policy volume, ranking, and other metrics.

[0226] Example 6: Full Synchronization Implementation

[0227] 1.6.1 Full Synchronization Task

[0228] Task Name: syncAllPolicyDataToEs. Supported Parameters: startDate (start date), endDate (end date), pageNum (page number, supports resuming interrupted downloads), contType (policy type, individual policy / group policy), contNos (list of policy numbers).

[0229] The full synchronization task retrieves all policy numbers from the lccont table of the core Oracle slave database using pagination conditions, serving as the data source for full synchronization. By querying the slave database instead of the master database, query pressure on the master database is avoided, ensuring its stability.

[0230] The full synchronization task serves as a data compensation mechanism for:

[0231] Fix data errors caused by bugs

[0232] Fixes a system malfunction that caused incomplete data synchronization for a certain period of time.

[0233] Initialize ES index data

[0234] Complete historical data as needed.

[0235] 1.6.2 Full Synchronization Process

[0236] Connect to the core Oracle slave database and query the total number of records in the lccont table based on task parameters (time range, policy type, policy number list, etc.).

[0237] Calculate the total number of pages: Total number of records / Number of records per page (1000 records)

[0238] The starting page number is determined based on the pageNum parameter (supports resuming interrupted downloads).

[0239] The Oracle slave database retrieves a list of policy numbers (contNo) paginated from the lccont table. Since the policy numbers in the lccont table are unique, no deduplication is required.

[0240] Based on the retrieved list of policy numbers, perform batch queries on relevant tables in the core database and call external APIs to obtain associated data.

[0241] Multi-source data is assembled into a complete policy data object, business-derived fields are calculated, and a nested document structure is constructed.

[0242] The assembled policy data is directly updated to the Elasticsearch index (core_policy_index), with the policy number used as the document ID. No database queue operations are involved.

[0243] Supports resumeable downloads: You can specify the starting page number via the pageNum parameter to continue synchronization from the specified page number without having to start over.

[0244] Example 7: Intelligent Retry Mechanism

[0245] 1.7.1 Queue Record Field Design

[0246] Queue records contain the following fields:

[0247] core_no: Policy number

[0248] dts_op: Operation type (del / ins / upd)

[0249] create_time: Creation time (records the time when the data was first written to the queue).

[0250] last_process_time: The time of the most recent process (records the time of the last processing, initially NULL).

[0251] process_count: Number of times the process has been executed (initially 0).

[0252] next_execute_time: The next execution time (calculated based on the current execution time and an incrementing time interval strategy, initially set to the creation time to facilitate database queries for the next task).

[0253] 1.7.2 Failure Handling Strategy

[0254] When an Elasticsearch write fails, the queue records `last_process_time` (current time), `process_count` (number of processing attempts + 1), and `next_execute_time` (calculated based on the current execution time and the incrementing time interval strategy). An exception log is recorded and an alert is triggered, allowing for timely detection of anomalies through monitoring. Failed records are retained in the queue without deletion. Failed records are skipped, and the process continues with the next record, ensuring uninterrupted task execution.

[0255] 1.7.3 Intelligent Retry Mechanism

[0256] Incremental time interval strategy: Use an incremental time interval based on the number of processing attempts to avoid frequent retries affecting the processing of new data.

[0257] First retry: Next execution time = current execution time + 5 minutes

[0258] Second retry: Next execution time = current execution time + 10 minutes

[0259] Third retry: Next execution time = current execution time + 30 minutes

[0260] Fourth retry: Next execution time = current execution time + 60 minutes

[0261] 5th retry: Next execution time = current execution time + 3 hours

[0262] 6th retry: Next execution time = Current execution time + 12 hours

[0263] 7th retry: Next execution time = Current execution time + 24 hours

[0264] 8th retry: Next execution time = This execution time + 24 hours (last time)

[0265] Next execution time calculation: When an Elasticsearch write fails, the next execution time (next_execute_time) is calculated based on the current execution time (last_process_time) and the number of processing attempts (process_count) using an incremental time interval strategy, and then updated in the queue record to facilitate DB queries for the next task and improve query efficiency.

[0266] Maximum number of executions limit: After the number of processing attempts reaches 8, the process will stop to avoid infinite retries affecting the processing of new data.

[0267] Incremental synchronization task execution logic: The incremental synchronization task is executed once every minute. The query condition is next_execute_time ≤ current time, ensuring that only records that meet the retry interval condition are processed, and new data is processed first.

[0268] In summary, the present invention has the following technical effects:

[0269] Compared with the prior art, the present invention has the following beneficial effects:

[0270] 3.3.1 Query performance is significantly improved.

[0271] List query performance: The query time for a large number of records has been reduced from more than 10 seconds to less than 800ms, a performance improvement of more than 12 times.

[0272] Performance improvement for complex statistics: The timeout / OOM timeout for complex aggregation statistics has been reduced to 1-3 seconds, a performance improvement of more than 20 times.

[0273] High concurrency performance: Supports stable operation for 200 concurrent users, with a QPS of 350, representing a performance improvement of over 400 times.

[0274] 3.3.2 System stability is significantly improved

[0275] Core system decoupling: Completely decouple the core system to avoid the risk of core system failure.

[0276] System stability: Stable operation 24 / 7 with no memory leaks or OOM errors.

[0277] Large data volume support: Supports stable querying of tens of millions of data points.

[0278] 3.3.3 Improved Data Synchronization Efficiency

[0279] Merging and deduplication mechanism: Multiple changes to the same policy are merged into a single synchronization, reducing core database queries by more than 80%.

[0280] Batch processing: Batch processing of queue records, with a processing speed of 66 records per second.

[0281] Asynchronous processing: Scheduled tasks are processed asynchronously, without affecting real-time business operations.

[0282] 3.3.4 Business Value Enhancement

[0283] Response speed to demands: The front-end system can respond quickly to new demands without waiting for the core system to be developed.

[0284] Extended query capabilities: Supports combined queries and statistics for any time interval and any dimension.

[0285] System decoupling: The front-end system is completely decoupled from the core system and does not affect each other.

[0286] 3.3.5 Data Consistency Guarantee

[0287] Real-time synchronization: End-to-end latency is less than 3 minutes, meeting the real-time requirements of business operations.

[0288] Consistency accuracy: Through a full + incremental synchronization strategy, the data consistency accuracy reaches 99.99%.

[0289] Operation semantics are preserved throughout the process, supporting precise synchronization strategies.

[0290] The above are merely preferred embodiments of the present invention and are not intended to limit the scope of protection of the present invention; any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for real-time synchronization and efficient query of policy data, characterized in that: The method includes the following steps: Step S1: Real-time monitoring of changes to policy-related tables in the core database using the built-in CDC capability of DTS, generating an event stream containing operational semantics; Step S2: After the event stream is sent to Kafka, the Kafka consumer reads it and writes it to the MySQL queue list; Step S3: Perform timed deduplication and merging of events in the MySQL queue list, prioritize deletion operations, and synchronize the merged policy data to the Elasticsearch index; Step S4: Provide multi-condition combined queries and multi-dimensional aggregation statistics services based on Elasticsearch indexes.

2. The method according to claim 1, characterized in that, The event stream carries the operation type, table name, transaction time, transaction number, and sequence number, so that the semantics of adding, deleting, and modifying are fully preserved. And / or, the MySQL queue list is divided into different queues according to business scenarios. Each record stores the policy number, operation type, creation time, most recent processing time, number of times it has been processed, and the next time it should be processed, in order to control the order, retries, and concurrency.

3. The method according to claim 1, characterized in that, When step S2 is completed, the consumer only submits the message offset after confirming that the MySQL queue has been successfully written. If the write fails, the submission is paused and retried until the table is successfully written, thus ensuring that no events are lost.

4. The method according to claim 1, characterized in that, The process of periodically deduplicating and merging events in the MySQL queue list and prioritizing deletion operations includes the following steps: Each time, retrieve the first thousand records whose next processing time does not exceed the current time, retain the last record for the same policy number, and if a deletion operation is involved, only the deletion operation is retained; then, in the same local transaction, sequentially execute the following steps: delete index documents, batch query related tables in the core database and external interfaces, assemble nested documents, calculate derived fields, and overwrite the search engine. If the write is successful, immediately delete the corresponding queue record; if it fails, update the processing time and number of times and issue an alarm.

5. The method according to claim 4, characterized in that, The retry interval for failed records is increased progressively with each processing attempt, with a maximum of 24 hours. After eight failed attempts, no further retries are made, and the record is marked as a dead letter. At the same time, an alarm is pushed to the operations and maintenance platform to prevent old data from blocking new data.

6. The method according to claim 1, characterized in that, In step S3, while incremental synchronization is completed, full synchronization is also introduced as a supplement: the full synchronization obtains the policy number from the read-only backup database of the core database, directly assembles the data and writes it to the search engine without going through the MySQL queue list; the execution process supports filtering by time interval, policy type or specified policy number range, and can continue after being interrupted at any page number, used to correct incremental omissions, rebuild indexes or complete historical data; if the write fails, only the exception log is recorded and an alarm is issued, and the manual decision to redeploy is made according to the page number to avoid the whole run causing a surge in system resources.

7. The method according to claim 1, characterized in that, Create a strict schema index in the Elasticsearch index, pre-declaring fields such as policy number, signing date, policyholder, insured, insurance type list, agency path, agent number, policy status, channel type, and beneficiary type, and setting the policy number as the document identifier; enable word segmentation for fields requiring full-text search, and keep the other exact matching fields as Keyword type; aggregate statistics output total premium, number of policies, and ranking by agency level, agent, insurance type, time interval, and channel type, and support sub-aggregation and pagination return. The query chain is completely decoupled from the core database to ensure continuous high availability.

8. A real-time synchronization and efficient query system for insurance policy data based on CDC and Elasticsearch, comprising: The change capture module is used to monitor changes to policy-related tables in the core database in real time through the built-in CDC capability of DTS, and generate an event stream containing operational semantics. The message transmission module is used to send the event stream into Kafka, where it is read by the Kafka consumer and written to the MySQL queue list. The event buffer module provides capabilities for queuing, deduplication, retry, dead letter handling, and breakpoint resumption. The data synchronization module performs timed deduplication and merging of events in the MySQL queue list, prioritizes deletion operations, and synchronizes the merged policy data to the Elasticsearch index. The query service module provides multi-condition combined queries and multi-dimensional aggregation statistics services based on Elasticsearch indexes.

9. The system according to claim 8, characterized in that, The data synchronization module has a built-in retry engine that automatically schedules data based on the number of times the queue records are processed and the next processing time, with gradually increasing time intervals. After eight attempts, it stops and re-enters the dead letter, while simultaneously sending an alarm to the monitoring platform. The queue list uses a combination of partition keys and indexes, supporting scanning of tens of millions of events at the second level. When the size of a single table is too large, it can be split into multiple sub-tables by policy number hash, maintaining linear growth in scanning performance.

10. The system according to claim 8, characterized in that, The query service module is encapsulated using a unified gateway and has built-in circuit breaking, rate limiting, caching, and slow query logs. When the search engine response exceeds the limit, it automatically returns a service busy prompt. The index uses strict mode to declare fields in advance and prohibits the writing of unknown columns. At the same time, it implements hot and cold storage through aliases. Recent data is located in high-speed storage, while historical data is automatically migrated to low-speed storage, which reduces costs and ensures front-end speed.

Images