Table: gift_aid_tx_flags

Flags and exception statuses used by the Gift Aid workflow for individual transactions.

Purpose

Stores a single, current “flag” for a transaction during Gift Aid processing — for example to mark a transaction as pending review, not eligible, or to be ignored.

Relationships

• church_id → churches.id (ON DELETE CASCADE)
• transaction_id → transactions.id (ON DELETE CASCADE)
• updated_by_user_id → users.id (ON DELETE SET NULL)

Columns

• id — surrogate key (AUTO_INCREMENT).
• church_id — owning church for scope and RBAC enforcement.
• transaction_id — the transaction being flagged.
• flag_status — enum: PENDING, NOT_ELIGIBLE, IGNORE.
• note — optional free text note (max 255).
• updated_by_user_id — user who last changed the flag (nullable).
• updated_at — last update timestamp (auto-updated).

Indexes

• PRIMARY on id
• UNIQUE uniq_tx on transaction_id (enforces one flag row per transaction)
• idx_church on church_id
• idx_flag on flag_status
• idx_updated_by on updated_by_user_id

Constraints

• Foreign keys as listed in Relationships.
• transaction_id must be unique across the table.
• flag_status is limited to the defined enum values.

Operational notes / invariants

• No delete in normal operation: flags should typically be updated, not removed, to keep workflows consistent.
• Church scoping: application code must ensure the church_id matches the transaction’s church.
• Auditability: changes should be mirrored in activity/audit logs as required by governance.

DDL

CREATE TABLE `gift_aid_tx_flags` (
  `id` int UNSIGNED NOT NULL,
  `church_id` int UNSIGNED NOT NULL,
  `transaction_id` int UNSIGNED NOT NULL,
  `flag_status` enum('PENDING','NOT_ELIGIBLE','IGNORE') NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `updated_by_user_id` int UNSIGNED DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `gift_aid_tx_flags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_tx` (`transaction_id`),
  ADD KEY `idx_church` (`church_id`),
  ADD KEY `idx_flag` (`flag_status`),
  ADD KEY `idx_updated_by` (`updated_by_user_id`);

ALTER TABLE `gift_aid_tx_flags`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `gift_aid_tx_flags`
  ADD CONSTRAINT `fk_ga_flags_church` FOREIGN KEY (`church_id`) REFERENCES `churches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ga_flags_tx` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ga_flags_user` FOREIGN KEY (`updated_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;