This is my database
CREATE SCHEMA idvdev;
------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE idvdev.idv_transaction
(
    idv_transaction_id bigint NOT NULL,
	txn_reference_no varchar(50),
    ack_reference_no varchar(50),
	filenet_reference_no varchar(50),
    source_country varchar(2),
    current_state varchar(50),
    idv_customer_information_id bigint, --- FK
	idv_ekyc_golden_source_id bigint, -- FK
    idv_ekyc_golden_source_payload_id bigint, -- FK
    idv_match_results_id bigint, -- FK
	system_identifier varchar(50),
	response_endpoint_url varchar(50),
	vendor_transaction_id varchar(50),
    created_date TIMESTAMPTZ,
    updated_date TIMESTAMPTZ,
	
    PRIMARY KEY (idv_transaction_id)
);
CREATE UNIQUE INDEX idv_transaction_ack_index ON idvdev.idv_transaction (ack_reference_no);

ALTER TABLE idvdev.idv_transaction
ADD CONSTRAINT idv_transaction_uk UNIQUE (txn_reference_no);

ALTER TABLE idv_transaction ADD COLUMN token_expiry_date TIMESTAMPTZ;
ALTER TABLE idv_transaction ADD COLUMN client_config TEXT;
ALTER TABLE idv_transaction ADD COLUMN qr_code TEXT;
ALTER TABLE idv_transaction ADD COLUMN public_key TEXT;
ALTER TABLE idv_transaction ADD COLUMN product_identifier VARCHAR(1);
ALTER TABLE idv_transaction DROP COLUMN vendor_txn_reference_no;	

CREATE TABLE idvdev.idv_customer_information
(
    idv_customer_information_id bigint NOT NULL,
	txn_reference_no varchar(75),
	channel_reference varchar(75),
	ack_reference_no varchar(75),
    service_type varchar(75),
    source_country varchar(75),
    system_identifier varchar(75),
    device_info varchar(75),
    token_number varchar(75),
    segment_code varchar(75),
    product_identifier varchar(75),
    salutation_code varchar(75),
    firstname varchar(75),
    middlename varchar(75),
    lastname varchar(75),
    resident_status varchar(75),
	full_name varchar(75),
	dob varchar(75),
	document_type varchar(75),
	document_number varchar(75),
	document_image varchar(75),
	document_receive_date TIMESTAMPTZ,
	document_expiry_date TIMESTAMPTZ,
	document_issued_country_code varchar(75),
	document_issued_country_name varchar(75),
	id_photo varchar(75),
	liveliness_image varchar(75),
	relation_code varchar(75),
	relation_first_name varchar(75),
	relation_middle_name varchar(75),
	relation_last_name varchar(75),
	mother_maiden_name varchar(75),
	birth_country varchar(75),
	gender varchar(75),
	marital_status varchar(75),
	signature varchar(75),
	customer_nationality_code varchar(75),
	contact_classification_code varchar(75),
	contact varchar(75),
	country_code_isd varchar(75),
	reference varchar(75),
	address_type varchar(75),
	address_line1 varchar(75),
	address_line2 varchar(75),
	address_line3 varchar(75),
	nearest_land_mark varchar(75),
	city_name varchar(75),
	state varchar(75),
	country_code varchar(75),
	postal_code varchar(75),
	work_type varchar(75),
	customer_occupation varchar(75),
	employer_name varchar(75),
	staff_emp_id varchar(75),
	office_address_line1 varchar(75),
	office_address_line2 varchar(75),
	office_address_line3 varchar(75),
	office_nearest_land_mark varchar(75),
	office_city_name varchar(75),
	office_state varchar(75),
	office_country_code varchar(75),
	office_postal_code varchar(75),
    alias_name varchar(75),
    biometric_image varchar(75),
    otp_number varchar(75), 

    PRIMARY KEY (idv_customer_information_id)
);
CREATE UNIQUE INDEX idv_customer_information_ack_index ON idvdev.idv_customer_information (ack_reference_no);

ALTER TABLE idv_customer_information ADD COLUMN document_registration_date TIMESTAMPTZ;
ALTER TABLE idv_customer_information RENAME COLUMN document_receive_date TO document_received_date;
ALTER TABLE idv_customer_information DROP COLUMN id_photo;

CREATE TABLE idvdev.idv_ekyc_golden_source
(
    idv_ekyc_golden_source_id bigint NOT NULL,
	idv_ekyc_golden_source_payload_id bigint, --- REFERENCES idvdev.idv_ekyc_golden_source_payload ---FK
	txn_reference_no varchar(50),
	ack_reference_no varchar(50),
    created_date varchar(50),
    error_code varchar(5),
    error_desc varchar(50),
    vendor_txn_reference_no varchar(50),
    validation_result varchar(7),
	salutation_code varchar(75),
    firstname varchar(75),
    middlename varchar(75),
    lastname varchar(75),
    resident_status varchar(75),
	full_name varchar(75),
	dob varchar(75),
	document_type varchar(75),
	document_number varchar(75),
	document_image varchar(75),
	document_receive_date TIMESTAMPTZ,
	document_expiry_date TIMESTAMPTZ,
	document_issued_country_code varchar(75),
	document_issued_country_name varchar(75),
	id_photo varchar(75),
	liveliness_image varchar(75),
	relation_code varchar(75),
	relation_first_name varchar(75),
	relation_middle_name varchar(75),
	relation_last_name varchar(75),
	mother_maiden_name varchar(75),
	birth_country varchar(75),
	gender varchar(75),
	marital_status varchar(75),
	signature varchar(75),
	customer_nationality_code varchar(75),
	contact_classification_code varchar(75),
	contact varchar(75),
	country_code_isd varchar(75),
	reference varchar(75),
	address_type varchar(75),
	address_line1 varchar(75),
	address_line2 varchar(75),
	address_line3 varchar(75),
	nearest_land_mark varchar(75),
	city_name varchar(75),
	state varchar(75),
	country_code varchar(75),
	postal_code varchar(75),
	work_type varchar(75),
	customer_occupation varchar(75),
	employer_name varchar(75),
	staff_emp_id varchar(75),
	office_address_line1 varchar(75),
	office_address_line2 varchar(75),
	office_address_line3 varchar(75),
	office_nearest_land_mark varchar(75),
	office_city_name varchar(75),
	office_state varchar(75),
	office_country_code varchar(75),
	office_postal_code varchar(75),
	selfie_score varchar(50),
	document_score DECIMAL,
	local_name varchar(50),
	result_code varchar(50),
	result_message varchar(50),
	result_status varchar(50),
    PRIMARY KEY (idv_ekyc_golden_source_id)
);
CREATE UNIQUE INDEX idv_ekyc_golden_source_ack_index ON idvdev.idv_ekyc_golden_source (ack_reference_no);


ALTER TABLE idv_ekyc_golden_source ADD COLUMN ekyc_face_result varchar(50),
ADD COLUMN face_liveness_result varchar(50),
ADD COLUMN face_quality_score DECIMAL,
ADD COLUMN doc_edition INTEGER,
ADD COLUMN ekyc_doc_result varchar(50),
ADD COLUMN document_code varchar(50),
ADD COLUMN document_registration_date TIMESTAMPTZ,
ADD COLUMN symbols varchar(50),
ADD COLUMN kineprint_passed varchar(50),
ADD COLUMN kineprint_score DECIMAL,
ADD COLUMN kineprint_threshold INTEGER,
ADD COLUMN multiple_laser_image_passed varchar(50),
ADD COLUMN multiple_laser_image_score DECIMAL,
ADD COLUMN multiple_laser_image_threshold INTEGER,
ADD COLUMN optical_variable_ink_passed varchar(50),
ADD COLUMN optical_variable_ink_score DECIMAL,
ADD COLUMN optical_variable_ink_threshold INTEGER,
ADD COLUMN overall_score DECIMAL,
ADD COLUMN overall_threshold INTEGER,
ADD COLUMN overall_passed varchar(50),
ADD COLUMN other_check varchar(50),
ADD COLUMN tamper_check varchar(50),
ADD COLUMN screen_recapture_check varchar(50),
ADD COLUMN material_check varchar(50),
ADD COLUMN ekyc_risk_result varchar(50),
ADD COLUMN id_network_details varchar(50),
ADD COLUMN strategy_pass_result varchar(50),
ADD COLUMN permanent_resident_status varchar(50),
ADD COLUMN face_match_threshold INTEGER,
ADD COLUMN ekyc_result varchar(50)
ADD COLUMN customer_photo varchar;

ALTER TABLE idv_ekyc_golden_source RENAME COLUMN document_receive_date TO document_received_date;
ALTER TABLE idv_ekyc_golden_source RENAME COLUMN selfie_score TO face_match_score;
ALTER TABLE idv_ekyc_golden_source DROP COLUMN id_photo;
 



CREATE TABLE idvdev.idv_ekyc_golden_source_payload
(
    idv_ekyc_golden_source_payload_id bigint NOT NULL,
	txn_reference_no varchar(50),
	ack_reference_no varchar(50),
    poi_golden_source_data bytea,
    poa_golden_source_data bytea,
    image_match_golden_souce_data bytea,
    fingerprint_match_golden_source_data bytea,
    created_date varchar(50),	
    PRIMARY KEY (idv_ekyc_golden_source_payload_id)
);
CREATE UNIQUE INDEX idv_ekyc_golden_source_payload_ack_index ON idvdev.idv_ekyc_golden_source_payload (ack_reference_no);

CREATE TABLE idvdev.idv_match_result
(
    idv_match_result_id bigint NOT NULL,
    ack_reference_no varchar(50),
    txn_reference_no varchar(50),
    overall_status varchar(7),
	verification_date_time varchar(50),
    document_verification_result varchar(50),
	name_of_verification varchar(50),
	date_of_birth_verification varchar(50),
	id_number_verification varchar(50),
	nationality_verification varchar(50),
    facial_match_result varchar(50),
    document_facial_failure_reason varchar(50),
    poi_match_status varchar(50),
    poi_failure_reason varchar(50),
    poa_match_status varchar(50),
    poa_failure_reason varchar(50),
    PRIMARY KEY (idv_match_result_id)
);
CREATE UNIQUE INDEX idv_match_result_ack_index ON idvdev.idv_match_result (ack_reference_no);
------------------------------------------------------------------------------------------------------------------------------
 alter table idvdev.idv_transaction
   add constraint idv_ekyc_golden_source_payload_id
   foreign key (idv_ekyc_golden_source_payload_id) 
   references idvdev.idv_ekyc_golden_source_payload (idv_ekyc_golden_source_payload_id);
	
 alter table idvdev.idv_transaction
   add constraint idv_ekyc_golden_source_id
   foreign key (idv_ekyc_golden_source_id) 
   references idvdev.idv_ekyc_golden_source(idv_ekyc_golden_source_id);
	
 alter table idvdev.idv_transaction
   add constraint idv_match_results_id
   foreign key (idv_match_results_id) 
   references idvdev.idv_match_result (idv_match_result_id);

 alter table idvdev.idv_transaction
   add constraint idv_customer_information_id
   foreign key (idv_customer_information_id) 
   references idvdev.idv_customer_information (idv_customer_information_id);
------------------------------------------------------------------------------------------------------------------------------
 alter table idvdev.idv_ekyc_golden_source
   add constraint idv_ekyc_golden_source_payload_id
   foreign key (idv_ekyc_golden_source_payload_id) 
   references idvdev.idv_ekyc_golden_source_payload (idv_ekyc_golden_source_payload_id);
------------------------------------------------------------------------------------------------------------------------------
CREATE SEQUENCE idvdev.IDV_TRANSACTION_SEQ INCREMENT 1 START 1;
CREATE SEQUENCE idvdev.IDV_CUSTOMER_INFORMATION_SEQ INCREMENT 1 START 1;
CREATE SEQUENCE idvdev.IDV_EKYC_GOLDEN_SOURCE_PAYLOAD_SEQ INCREMENT 1 START 1;
CREATE SEQUENCE idvdev.IDV_EKYC_GOLDEN_SOURCE_SEQ INCREMENT 1 START 1;
CREATE SEQUENCE idvdev.IDV_MATCH_RESULT_SEQ INCREMENT 1 START 1;

Now I need the sql script for ,
1. creating partitions + creating tracket table
2. creating backup + purge + updating tracker table

Context solution for reference
IDV Database Archival Solution

IMPLEMENTATION
1. Database Partitioning
For each IDV table, create a backup table with identical schema. This will be done for all 4 tables:
idv_transaction, idv_ekyc_golden_source, idv_ekyc_golden_source_payload, idv_match_results

Example:
CREATE TABLE idv_transaction (          // main table
    ...schema...
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE idv_transaction_backup (   // backup table
    ...schema...
    backup_created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

2. Tracker Table
Create a centralized backup service tracking table. We are opting for a tracker table because we do not have any configuration management system like Zookeeper for decentralized tracking.
CREATE TABLE idv_db_state (
    last_backup TIMESTAMP, 
    last_purge TIMESTAMP, 
    last_success_update TIMESTAMP
);

3. Backup Service
A service is implemented in Spring, which will be executed periodically through a Control-M job. The Backup Service will include the following methods:
- backup(): Inserts records into the backup table for the condition WHERE created_at < NOW() - INTERVAL '180 days'. This will be a batch job based on the defined BATCH_SIZE.
- purgeOldRecords(): Deletes records from the main table for the condition WHERE created_at < NOW() - INTERVAL '180 days'.
- validateBackup(): Validates backup success and updates the idv_db_state table.

4. Batch Processing
BATCH_SIZE is initially set to 1000 rows. After testing the impact on system performance, we can incrementally increase it to 5000 or 10000 rows, depending on resource utilization and observed performance.

5. Implementing as a Control-M Job
To integrate the Backup Service with Control-M, we will need to set up jobs that execute our Spring application. Here's how you can implement this:

Create a Control-M Job Definition:
Job Name: Specify a clear name for the job (e.g., IDV_Backup_Job).
Command: Set the command to run your Spring application or a specific JAR file. You can use a shell command to execute the desired operation:
	java -jar your-application.jar backup
	Parameters: If you have different tasks (e.g., backup and purge), set parameters to indicate which method to run when the job is executed. For example:
	
To run the backup: java -jar your-application.jar backup
To run the purge: java -jar your-application.jar purge

Scheduling Options:
Define the schedule for the job in Control-M (e.g., daily at 1 AM for backups, daily at 2 AM for purging) based on operational requirements.