-- Slice 8c-CustomerRetention · Customer-side retention releases

/* One row per release event (partial or full) recorded on a Works project's IPC-retention
   pool. This is what a customer paid us back on the contractual retention line — usually
   at practical-completion halfway (50%) and end-of-DLP (final 50%). */
CREATE TABLE IF NOT EXISTS `works_customer_retention_releases` (
    `c0d`              INT AUTO_INCREMENT PRIMARY KEY,
    `project_id`       INT           NOT NULL,
    `released_on`      DATE          NOT NULL,
    `amount`           DECIMAL(20,2) NOT NULL DEFAULT 0,
    `matched_receipt_id` INT         NOT NULL DEFAULT 0,   -- optional link to `receipts` row
    `notes`            VARCHAR(500)  NOT NULL DEFAULT '',
    `released_by`      INT           NOT NULL DEFAULT 0,
    `released_by_name` VARCHAR(120)  NOT NULL DEFAULT '',
    `created_on`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted`          TINYINT(1)    NOT NULL DEFAULT 0,
    KEY `ix_project` (`project_id`, `deleted`),
    KEY `ix_released_on` (`released_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
