Table: gift_aid_gasds_entries

GASDS register entries (small donations) recorded separately from the main transactions ledger.

Purpose

Stores individual GASDS entries for a church, including date, payment method (cash/contactless), eligibility tracking, and optional reference text. These entries support Gift Aid claims and reporting.

Relationships

• church_id → churches.id (ON DELETE CASCADE)
• created_by → users.id (ON DELETE SET NULL)

Columns

• id — surrogate key (AUTO_INCREMENT).
• church_id — owning church (scope/RBAC boundary).
• entry_date — date of the GASDS entry.
• method — enum: cash or contactless (default cash).
• amount — amount recorded (decimal 12,2).
• reference — optional descriptive reference (max 255).
• is_eligible — eligibility flag (default true).
• ineligible_reason — optional reason when not eligible.
• created_by — user who created the entry (nullable).
• created_at — creation timestamp.

Indexes

• PRIMARY on id
• idx_ga_gasds_church_date on (church_id, entry_date) (reporting/filtering)
• fk_ga_gasds_user key on created_by

Constraints

• Foreign keys as listed in Relationships.
• method limited to enum values cash/contactless.
• When is_eligible = 0, consider requiring ineligible_reason at application level.

Operational notes / invariants

• Separate register: GASDS entries are claim-register data and may be banked in aggregate.
• Church scoping: always enforce church_id as the scope boundary.
• Eligibility: eligibility is tracked per entry to support reporting and exceptions lists.

DDL

CREATE TABLE `gift_aid_gasds_entries` (
  `id` int UNSIGNED NOT NULL,
  `church_id` int UNSIGNED NOT NULL,
  `entry_date` date NOT NULL,
  `method` enum('cash','contactless') NOT NULL DEFAULT 'cash',
  `amount` decimal(12,2) NOT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `is_eligible` tinyint(1) NOT NULL DEFAULT '1',
  `ineligible_reason` varchar(255) DEFAULT NULL,
  `created_by` int UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `gift_aid_gasds_entries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ga_gasds_church_date` (`church_id`,`entry_date`),
  ADD KEY `fk_ga_gasds_user` (`created_by`);

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

ALTER TABLE `gift_aid_gasds_entries`
  ADD CONSTRAINT `fk_ga_gasds_church` FOREIGN KEY (`church_id`) REFERENCES `churches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ga_gasds_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;