Test with ICANN RST tools

[getpinga]

https://webrdapct.icann.org/ and more

[getpinga]

Also the RST OT&E.

[getpinga]

After tests, make escrow and lordn scripts retry on failed upload, like we did with the reporting.

[getpinga]

CREATE TABLE IF NOT EXISTS `registry`.`rde_escrow_deposits` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `deposit_id` VARCHAR(255) NOT NULL,  -- Unique deposit identifier
    **`tld_id` INT UNSIGNED NOT NULL,  -- Foreign key linking to domain_tld.id**
    `deposit_date` DATE NOT NULL,
    `revision` INT UNSIGNED NOT NULL DEFAULT 1,
    `file_name` VARCHAR(255) NOT NULL,
    `file_format` ENUM('XML', 'CSV') NOT NULL,  -- Format of the data file
    `file_size` BIGINT UNSIGNED,
    `checksum` VARCHAR(64),
    `encryption_method` VARCHAR(255),  -- Details about how the file is encrypted
    `deposit_type` ENUM('Full', 'Incremental', 'Differential') NOT NULL,
    `status` ENUM('Deposited', 'Retrieved', 'Failed') NOT NULL DEFAULT 'Deposited',
    `receiver` VARCHAR(255),  -- Escrow agent or receiver of the deposit
    `notes` TEXT,
    `verification_status` ENUM('Verified', 'Failed', 'Pending') DEFAULT 'Pending',  -- Status after the escrow agent verifies the deposit
    `verification_notes` TEXT,  -- Notes or remarks from the verification process
    **UNIQUE (`deposit_id`, `tld_id`),  -- Ensure unique combination of deposit_id and tld_id**
    **FOREIGN KEY (`tld_id`) REFERENCES `domain_tld` (`id`) ON DELETE CASCADE ON UPDATE CASCADE**
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Escrow Deposits';

[getpinga]

Consider adding control panel settings to enable or disable escrow/reporting/etc.

[getpinga]

-- 1. Add the tld_id column
ALTER TABLE `registry`.`rde_escrow_deposits`
ADD COLUMN `tld_id` INT UNSIGNED NOT NULL AFTER `deposit_id`;

-- 2. Drop the existing unique constraint on deposit_id (if it exists)
ALTER TABLE `registry`.`rde_escrow_deposits`
DROP INDEX `deposit_id`,  -- Replace `deposit_id` with the actual name of the unique index if different
ADD UNIQUE INDEX `unique_deposit_tld` (`deposit_id`, `tld_id`);

-- 3. Add the foreign key constraint for tld_id
ALTER TABLE `registry`.`rde_escrow_deposits`
ADD CONSTRAINT `fk_tld_id`
FOREIGN KEY (`tld_id`)
REFERENCES `registry`.`domain_tld` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;