Table: user_role_assignments

Mapping users to roles and scopes.

Purpose

Describe why this table exists and what it stores.

Relationships

• List foreign keys and key joins (if any).

Columns

Indexes

List primary key and secondary indexes (if any).

Constraints

List foreign keys and other constraints (if any).

Operational notes / invariants

• Rules such as “no deletes”, “append-only”, “unique per church/FY”, etc.

DDL

CREATE TABLE `user_role_assignments` (
  `id` int NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `role_id` int NOT NULL,
  `scope_type` enum('global','deanery','church') COLLATE utf8mb4_unicode_ci NOT NULL,
  `scope_id` int DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_by_user_id` int UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;