Church Cashbook Data Schema Manual

Double-entry transaction record (worked example).

Purpose

Stores each financial entry as a double-entry transaction (debit + credit accounts). The UI can present this as simple “Money in / Money out”, but the database stores proper accounting structure.

Columns

• id (PK) — auto-increment primary key.
• church_id (FK → churches.id) — scopes the transaction to a church.
• tx_date — used for FY filters and reporting.
• description — user-entered narrative (avoid unnecessary personal data).
• amount — DECIMAL(12,2), stored as a positive value; direction is implied by debit/credit account selection.
• debit_account_id / credit_account_id (FK → accounts.id) — the double-entry structure.
• church_category_id (nullable FK → church_categories.id) — category for reporting/budgets.
• receipt_path — stored receipt/document path (may be outside webroot).
• is_voided, void_reason, voided_at — void-not-delete model fields.
• is_cleared, cleared_date — reconciliation tracking fields.
• created_at — timestamp of creation (defaults to current timestamp).

Indexes

• idx_tx_church_date (church_id, tx_date) — primary performance index for church + date/FY queries.
• idx_tx_category (church_category_id) — supports category totals, budgets variance and Gift Aid queries.
• fk_tx_debit, fk_tx_credit — support account-ledger views and integrity.

Constraints

• fk_tx_church with ON DELETE CASCADE — if a church is deleted, its transactions are deleted. This must be governance-locked in the application (deleting churches should not be routine).
• fk_tx_debit, fk_tx_credit — both accounts must exist.
• fk_tx_category — category must exist if supplied.
• chk_tx_cat_xor — CHECK constraint: cat_category_id and cat_heading_id are mutually exclusive; at most one may be non-NULL on any row. Enforced at DB level (MySQL 8.0.16+ / MariaDB 10.2.1+) as a safety net; the application also enforces this invariant in parse_category_value(). Added: migration SCH-02.

Do-not-break invariants

• Application must enforce void-not-delete; voided transactions do not affect totals.
• FY reporting relies on tx_date and opening balances per FY.
• Cross-church access must always be prevented by RBAC scope checks (church_id is the scope anchor).

CREATE TABLE `transactions` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `church_id` int UNSIGNED NOT NULL,
  `tx_date` date NOT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `debit_account_id` int UNSIGNED NOT NULL,
  `credit_account_id` int UNSIGNED NOT NULL,
  `cat_category_id` int UNSIGNED DEFAULT NULL,
  `cat_heading_id` int UNSIGNED DEFAULT NULL,
  `church_category_id` int UNSIGNED DEFAULT NULL,
  `fund_type` varchar(20) NOT NULL DEFAULT 'unrestricted',
  `receipt_path` varchar(255) DEFAULT NULL,
  `is_voided` tinyint(1) NOT NULL DEFAULT '0',
  `void_reason` varchar(255) DEFAULT NULL,
  `voided_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by_user_id` int UNSIGNED DEFAULT NULL,
  `is_cleared` tinyint(1) NOT NULL DEFAULT '0',
  `cleared_date` date DEFAULT NULL,
  CONSTRAINT `chk_tx_cat_xor` CHECK (
    `cat_category_id` IS NULL OR `cat_heading_id` IS NULL
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tx_church_date` (`church_id`,`tx_date`),
  ADD KEY `idx_tx_category` (`church_category_id`),
  ADD KEY `fk_tx_debit` (`debit_account_id`),
  ADD KEY `fk_tx_credit` (`credit_account_id`);

ALTER TABLE `transactions`
  ADD CONSTRAINT `chk_tx_cat_xor` CHECK (`cat_category_id` IS NULL OR `cat_heading_id` IS NULL),
  ADD CONSTRAINT `fk_tx_category` FOREIGN KEY (`church_category_id`) REFERENCES `church_categories` (`id`),
  ADD CONSTRAINT `fk_tx_church` FOREIGN KEY (`church_id`) REFERENCES `churches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_tx_credit` FOREIGN KEY (`credit_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_tx_debit` FOREIGN KEY (`debit_account_id`) REFERENCES `accounts` (`id`);