3.3 Database Schema Documentation

Overview

The KMP (Kingdom Management Platform) database schema implements a comprehensive data model supporting organizational management for SCA (Society for Creative Anachronism) branches. The schema follows CakePHP conventions and implements advanced patterns for hierarchical data, temporal relationships, role-based access control, and audit trails.

Schema Architecture

Core Design Principles

• Hierarchical Organization: Support for branch hierarchies using nested set model
• Temporal Data Management: ActiveWindow pattern for time-bounded entities
• Role-Based Access Control: Comprehensive RBAC with permissions and policies
• Audit Trail: Complete change tracking with user attribution
• Soft Deletion: Data preservation with logical deletion support
• JSON Field Support: Flexible data structures for complex configurations

Database Technologies

• Primary Database: MySQL/MariaDB with InnoDB engine
• Migration Framework: Phinx migrations via CakePHP
• Schema Management: CakePHP ORM with comprehensive associations
• JSON Support: Native JSON fields with database-level operations
• Session Storage: Database-backed session management

Core Schema Structure

1. Members Management

members Table

Primary entity for member management and authentication.

CREATE TABLE members (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    password VARCHAR(512) NOT NULL,
    sca_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    middle_name VARCHAR(30) NULL,
    last_name VARCHAR(30) NOT NULL,
    street_address VARCHAR(75) NULL,
    city VARCHAR(30) NULL,
    state VARCHAR(2) NULL,
    zip VARCHAR(5) NULL,
    phone_number VARCHAR(15) NULL,
    email_address VARCHAR(50) NOT NULL,
    membership_number VARCHAR(50) NULL,
    membership_expires_on DATE NULL,
    branch_id INT(11) NULL,
    background_check_expires_on DATE NULL,
    status VARCHAR(20) DEFAULT 'active',
    verified_date DATETIME NULL,
    verified_by INT(11) NULL,
    parent_id INT(11) NULL,
    mobile_card_token VARCHAR(255) NULL,
    password_token VARCHAR(255) NULL,
    password_token_expires_on DATETIME NULL,
    last_login DATETIME NULL,
    last_failed_login DATETIME NULL,
    failed_login_attempts INT(2) NULL,
    birth_month INT(11) NULL,
    birth_year INT(11) NULL,
    additional_info VARCHAR(255) NOT NULL DEFAULT '{}',
    membership_card_path VARCHAR(256) NULL,
    title VARCHAR(255) NULL,              -- Added via migration
    pronouns VARCHAR(255) NULL,           -- Added via migration
    pronunciation VARCHAR(255) NULL,      -- Added via migration
    warrantable BOOLEAN DEFAULT false,    -- Added via migration
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_email (email_address),
    INDEX idx_sca_name (sca_name),
    INDEX idx_membership (membership_number),
    INDEX idx_branch (branch_id),
    INDEX idx_parent (parent_id),
    INDEX idx_status (status),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (parent_id) REFERENCES members(id),
    FOREIGN KEY (verified_by) REFERENCES members(id)
);

Key Features:

• Authentication: Password hashing and token-based password reset
• Profile Information: Complete member profiles with contact details
• Hierarchical Support: Parent-child relationships for minors
• Membership Tracking: SCA membership number and expiration dates
• Security Features: Failed login tracking and account verification
• VARCHAR Configuration: Flexible additional_info field for extensibility
• Audit Trail: Complete creation and modification tracking

2. Organizational Structure

branches Table

Hierarchical organizational structure using nested set model.

CREATE TABLE branches (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(128) NOT NULL UNIQUE,
    location VARCHAR(128) NOT NULL,
    parent_id INT(11) NULL,
    links TEXT NULL,                      -- External links and resources 
    can_have_members BOOLEAN NOT NULL DEFAULT true,
    type VARCHAR(50) NULL,                -- Added via migration
    domain VARCHAR(255) NULL,             -- Added via migration
    lft INT(11) NULL,                     -- Nested set left value
    rght INT(11) NULL,                    -- Nested set right value
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_parent (parent_id),
    INDEX idx_lft (lft),
    INDEX idx_rght (rght),
    INDEX idx_type (type),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (parent_id) REFERENCES branches(id)
);

Key Features:

• Nested Set Model: Efficient hierarchical queries with lft/rght values
• Branch Types: Support for Local, College, and other organizational types
• Domain Configuration: Web domain management for multi-site deployments
• Links Management: JSON storage for external resources and websites
• Membership Control: Configurable member assignment capabilities
• Tree Behavior: CakePHP Tree behavior for hierarchy management

3. Role-Based Access Control (RBAC)

roles Table

Core role definitions for permission grouping.

CREATE TABLE roles (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    is_system BOOLEAN NOT NULL DEFAULT false,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_system (is_system),
    INDEX idx_deleted (deleted)
);

permissions Table

Granular permission definitions with validation requirements.

CREATE TABLE permissions (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    require_active_membership BOOLEAN NOT NULL DEFAULT false,
    require_active_background_check BOOLEAN NOT NULL DEFAULT false,
    require_min_age INT(2) NOT NULL DEFAULT 0,
    is_system BOOLEAN NOT NULL DEFAULT false,
    is_super_user BOOLEAN NOT NULL DEFAULT false,
    requires_warrant BOOLEAN NOT NULL DEFAULT false,
    scoping_rule VARCHAR(255) NOT NULL DEFAULT 'Global',  -- Added via migration
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_system (is_system),
    INDEX idx_super_user (is_super_user),
    INDEX idx_warrant (requires_warrant),
    INDEX idx_deleted (deleted)
);

roles_permissions Table

Many-to-many junction for role-permission assignments.

CREATE TABLE roles_permissions (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    role_id INT(11) NOT NULL,
    permission_id INT(11) NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by INT(11) NOT NULL,
    
    UNIQUE KEY unique_role_permission (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE NO ACTION,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE NO ACTION
);

member_roles Table

Temporal role assignments to members with ActiveWindow behavior.

CREATE TABLE member_roles (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    member_id INT(11) NOT NULL,
    role_id INT(11) NOT NULL,
    entity_type VARCHAR(255) NULL,       -- Renamed from granting_model in migration
    entity_id INT(11) NULL,              -- Renamed from granting_id in migration
    branch_id INT(11) NULL,              -- Added via migration for scoping
    start_on DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_on DATETIME NULL,
    status VARCHAR(20) DEFAULT 'new',
    approver_id INT(11) NOT NULL,
    revoker_id INT(11) NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_member (member_id),
    INDEX idx_role (role_id),
    INDEX idx_branch (branch_id),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_status (status),
    INDEX idx_start_on (start_on),
    INDEX idx_expires_on (expires_on),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (approver_id) REFERENCES members(id),
    FOREIGN KEY (revoker_id) REFERENCES members(id)
);

permission_policies Table

Custom authorization policies for fine-grained access control.

CREATE TABLE permission_policies (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    permission_id INT(11) NOT NULL,
    policy_class VARCHAR(255) NOT NULL,
    policy_method VARCHAR(255) NOT NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    
    INDEX idx_permission (permission_id),
    INDEX idx_policy_class (policy_class),
    
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

4. Warrant System

warrant_periods Table

Temporal warrant validity periods.

CREATE TABLE warrant_periods (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created DATETIME NOT NULL,
    created_by INT(11) NULL,
    
    INDEX idx_period (start_date, end_date),
    FOREIGN KEY (created_by) REFERENCES members(id)
);

warrants Table

Individual warrant assignments with entity polymorphism.

CREATE TABLE warrants (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    member_id INT(11) NOT NULL,
    warrant_roster_id INT(11) NOT NULL,
    entity_type VARCHAR(255) NULL,
    entity_id INT(11) NOT NULL,
    member_role_id INT(11) NULL,
    expires_on DATETIME NULL,
    start_on DATETIME NULL,
    approved_date DATETIME NULL,
    approver_id INT(11) NULL,
    revoked_reason TEXT NULL,
    revoker_id INT(11) NULL,
    status VARCHAR(20) DEFAULT 'pending',
    warrant_number VARCHAR(50) NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_member (member_id),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_status (status),
    INDEX idx_start_on (start_on),
    INDEX idx_expires_on (expires_on),
    INDEX idx_warrant_number (warrant_number),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (member_role_id) REFERENCES member_roles(id),
    FOREIGN KEY (approver_id) REFERENCES members(id),
    FOREIGN KEY (revoker_id) REFERENCES members(id)
);

5. Application Configuration

app_settings Table

Centralized application configuration with text storage.

CREATE TABLE app_settings (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    value TEXT NULL,                      -- Always TEXT field (corrected from documentation error)
    type VARCHAR(255) NULL,               -- Added via migration 20241024125311
    required BOOLEAN DEFAULT false,       -- Added via migration 20250108190610
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    
    INDEX idx_name (name),
    INDEX idx_required (required)
);

5. Notes System

notes Table

Polymorphic notes system for entity documentation.

CREATE TABLE notes (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    author_id INT(11) NOT NULL,
    entity_type VARCHAR(255) NULL,     -- Renamed from topic_model in migration
    entity_id INT(11) NOT NULL,        -- Renamed from topic_id in migration  
    subject VARCHAR(255) NULL,
    body TEXT NULL,
    private BOOLEAN NOT NULL DEFAULT false,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_private (private),
    
    FOREIGN KEY (author_id) REFERENCES members(id)
);

Plugin Schema Extensions

Officers Plugin

The Officers plugin extends the core schema with organizational management capabilities:

officers_departments Table

Organizational departments for office categorization.

CREATE TABLE officers_departments (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_deleted (deleted)
);

officers_offices Table

Office position definitions with hierarchical relationships.

CREATE TABLE officers_offices (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    department_id INT(11) NULL,
    requires_warrant BOOLEAN NOT NULL DEFAULT false,
    required_office BOOLEAN NOT NULL DEFAULT false,
    only_one_per_branch BOOLEAN NOT NULL DEFAULT false,
    can_skip_report BOOLEAN NOT NULL DEFAULT false,
    deputy_to_id INT(11) NULL,
    reports_to_id INT(11) NULL,             -- Added via migration 20241231161659
    grants_role_id INT(11) NULL,
    term_length INT(11) NULL,
    applicable_branch_types VARCHAR(255) NULL,  -- Added via migration 20241231161659 (changed from JSON)
    default_contact_address VARCHAR(255) NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_department (department_id),
    INDEX idx_deputy_to (deputy_to_id),
    INDEX idx_reports_to (reports_to_id),
    INDEX idx_grants_role (grants_role_id),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (department_id) REFERENCES officers_departments(id),
    FOREIGN KEY (deputy_to_id) REFERENCES officers_offices(id),
    FOREIGN KEY (reports_to_id) REFERENCES officers_offices(id),
    FOREIGN KEY (grants_role_id) REFERENCES roles(id)
);

Note: The kingdom_only column was removed and applicable_branch_types format was changed from JSON to VARCHAR(255) via migration 20241231161659_RefactorOfficeHierarchy.php.

officers_officers Table

Officer assignments with ActiveWindow temporal management.

CREATE TABLE officers_officers (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    member_id INT(11) NOT NULL,
    branch_id INT(11) NOT NULL,
    office_id INT(11) NOT NULL,
    granted_member_role_id INT(11) NULL,
    expires_on DATETIME NULL,
    start_on DATETIME NULL,
    status VARCHAR(20) DEFAULT 'new',
    deputy_description TEXT NULL,
    revoked_reason TEXT NULL,
    revoker_id INT(11) NULL,
    deputy_to_branch_id INT(11) NULL,
    deputy_to_office_id INT(11) NULL,
    reports_to_branch_id INT(11) NULL,
    reports_to_office_id INT(11) NULL,
    email_address VARCHAR(255) NULL,
    approver_id INT(11) NOT NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_member (member_id),
    INDEX idx_branch (branch_id),
    INDEX idx_office (office_id),
    INDEX idx_status (status),
    INDEX idx_start_on (start_on),
    INDEX idx_expires_on (expires_on),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (office_id) REFERENCES officers_offices(id),
    FOREIGN KEY (granted_member_role_id) REFERENCES member_roles(id),
    FOREIGN KEY (revoker_id) REFERENCES members(id),
    FOREIGN KEY (deputy_to_branch_id) REFERENCES branches(id),
    FOREIGN KEY (deputy_to_office_id) REFERENCES officers_offices(id),
    FOREIGN KEY (reports_to_branch_id) REFERENCES branches(id),
    FOREIGN KEY (reports_to_office_id) REFERENCES officers_offices(id),
    FOREIGN KEY (approver_id) REFERENCES members(id)
);

Awards Plugin

The Awards plugin implements a comprehensive award recommendation and ceremony system:

awards_domains Table

Award categorization domains.

CREATE TABLE awards_domains (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL
);

awards_levels Table

Award precedence hierarchy.

CREATE TABLE awards_levels (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    precedence INT(11) NOT NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_precedence (precedence)
);

awards_awards Table

Award definitions with hierarchical classification.

CREATE TABLE awards_awards (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    abbreviation VARCHAR(10) NULL,
    description TEXT NULL,
    insignia TEXT NULL,
    badge TEXT NULL,
    charter TEXT NULL,
    domain_id INT(11) NOT NULL,
    level_id INT(11) NOT NULL,
    branch_id INT(11) NOT NULL,
    specialties JSON NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_domain (domain_id),
    INDEX idx_level (level_id),
    INDEX idx_branch (branch_id),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (domain_id) REFERENCES awards_domains(id),
    FOREIGN KEY (level_id) REFERENCES awards_levels(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
);

awards_recommendations Table

Award recommendations with workflow state management.

CREATE TABLE awards_recommendations (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    requester_id INT(11) NULL,
    member_id INT(11) NULL,
    award_id INT(11) NOT NULL,
    branch_id INT(11) NOT NULL,
    event_id INT(11) NULL,
    reason TEXT NULL,
    specialty VARCHAR(255) NULL,
    contact_info TEXT NULL,
    member_name VARCHAR(255) NULL,
    stack_rank INT(11) DEFAULT 1000,
    state VARCHAR(50) DEFAULT 'submitted',
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_member (member_id),
    INDEX idx_award (award_id),
    INDEX idx_branch (branch_id),
    INDEX idx_event (event_id),
    INDEX idx_state (state),
    INDEX idx_stack_rank (stack_rank),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (requester_id) REFERENCES members(id),
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (award_id) REFERENCES awards_awards(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (event_id) REFERENCES awards_events(id)
);

Activities Plugin

The Activities plugin manages activity authorizations and approvals:

activities_activity_groups Table

Activity categorization groups.

CREATE TABLE activities_activity_groups (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL
);

activities_activities Table

Activity definitions with role integration.

CREATE TABLE activities_activities (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    activity_group_id INT(11) NOT NULL,
    grants_role_id INT(11) NULL,
    permission_id INT(11) NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_name (name),
    INDEX idx_activity_group (activity_group_id),
    INDEX idx_grants_role (grants_role_id),
    INDEX idx_permission (permission_id),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (activity_group_id) REFERENCES activities_activity_groups(id),
    FOREIGN KEY (grants_role_id) REFERENCES roles(id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

activities_authorizations Table

Authorization assignments with ActiveWindow behavior.

CREATE TABLE activities_authorizations (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    member_id INT(11) NOT NULL,
    activity_id INT(11) NOT NULL,
    granted_member_role_id INT(11) NOT NULL,
    start_on DATETIME NULL,
    expires_on DATETIME NULL,
    status VARCHAR(20) DEFAULT 'new',
    revoked_reason TEXT NULL,
    revoker_id INT(11) NULL,
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) NULL,
    deleted DATETIME NULL,
    
    INDEX idx_member (member_id),
    INDEX idx_activity (activity_id),
    INDEX idx_status (status),
    INDEX idx_start_on (start_on),
    INDEX idx_expires_on (expires_on),
    INDEX idx_deleted (deleted),
    
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (activity_id) REFERENCES activities_activities(id),
    FOREIGN KEY (granted_member_role_id) REFERENCES member_roles(id),
    FOREIGN KEY (revoker_id) REFERENCES members(id)
);

Advanced Schema Patterns

1. ActiveWindow Pattern

Many tables implement the ActiveWindow pattern for temporal data management:

// Common ActiveWindow fields
start_on DATETIME NULL,      // When the record becomes active
expires_on DATETIME NULL,    // When the record expires
status VARCHAR(20) DEFAULT 'new',  // Current lifecycle status

Status Values:

• new: Newly created, not yet active
• current: Currently active (between start_on and expires_on)
• upcoming: Future activation (start_on in future)
• expired: Past expiration (expires_on in past)
• revoked: Manually revoked before expiration

2. Audit Trail Pattern

All major tables include comprehensive audit trails:

// Standard audit fields
created DATETIME NOT NULL,
modified DATETIME NULL,
created_by INT(11) NULL,
modified_by INT(11) NULL,
deleted DATETIME NULL,      // Soft deletion timestamp

3. JSON Field Pattern

Several tables use JSON fields for flexible configuration:

// Examples of JSON field usage
additional_info VARCHAR(255) DEFAULT '{}', // Member additional data
specialties JSON NULL,             // Award specialty classifications

Note: Some fields like links in branches use TEXT format, and some previously JSON fields have been changed to TEXT or VARCHAR for simpler data handling (e.g., applicable_branch_types in officers_offices).

4. Polymorphic Associations

The Notes system and MemberRoles table implement polymorphic associations:

// Examples of polymorphic field usage
entity_type VARCHAR(255) NULL,     // Entity class name (renamed from topic_model)
entity_id INT(11) NOT NULL,        // Entity ID (renamed from topic_id)

Note: Field names were updated in migration 20241225192403_RefactorAgnosticJoinFields.php to use consistent entity_type/entity_id naming across tables.

5. Hierarchical Data

The Branches table uses the Nested Set Model for efficient hierarchy queries:

parent_id INT(11) NULL,           // Direct parent reference
lft INT(11) NULL,                 // Nested set left boundary
rght INT(11) NULL,                // Nested set right boundary

Migration History

The schema has evolved through comprehensive migrations:

1. Initial Schema (20230511170042_Init.php): Core tables and relationships
2. View Permissions (20241001141705_AddViewMembersPermission.php): Permission system enhancement
3. Member Extensions (20241009145957_AddTitlePronounsPronunciationToMembers.php): Profile enhancements
4. Settings Restructure (20241024125311_ChangeAppSettingValueToText.php): Configuration format change
5. Warrant System (20241204160759_Warrants.php): Complete warrant management system
6. Member Warrantability (20241207172311_AddWarrantableToMembers.php): Warrant eligibility tracking
7. Join Field Refactor (20241225192403_RefactorAgnosticJoinFields.php): Association improvements
8. Branch Types (20241231164137_AddTypeToBranches.php): Organizational classification
9. Required Settings (20250108190610_AddRequiredToAppSetting.php): Configuration validation
10. Role Scoping (20250227173909_AddScopeToMemberRoles.php): Role assignment context
11. Branch Domains (20250227230531_AddDomainToBranch.php): Multi-site support
12. Permission Policies (20250328010857_PermissionPolicies.php): Fine-grained authorization
13. Settings Consolidation (20250415203922_ConvertAppSettingsToSingleRecord.php): Configuration optimization

Validation and Constraints

Foreign Key Relationships

The schema implements comprehensive referential integrity:

• Cascade Deletes: Junction tables use CASCADE for cleanup
• Soft Deletes: Entity tables use soft deletion for data preservation
• Optional Relations: Many relationships use NULL for optional associations
• Temporal Constraints: ActiveWindow entities validate date ranges

Unique Constraints

Key uniqueness constraints ensure data integrity:

• Member Email: Unique email addresses for authentication
• Branch Names: Unique branch names for identification
• Role Names: Unique role names for permission management
• Office Names: Unique office names for organizational clarity
• Award Names: Unique award names within classification hierarchy

Index Strategy

Comprehensive indexing supports query performance:

• Primary Keys: Auto-incrementing integers for all entities
• Foreign Keys: All foreign key columns indexed
• Status Fields: Status columns indexed for lifecycle queries
• Date Fields: Temporal columns indexed for ActiveWindow queries
• Search Fields: Names and descriptive fields indexed for lookups
• Composite Indexes: Multi-column indexes for complex queries

Performance Considerations

Query Optimization

• Association Loading: Optimized contain strategies for related data
• Nested Set Queries: Efficient hierarchy traversal for branches
• ActiveWindow Filters: Optimized temporal query patterns
• Permission Lookups: Cached permission resolution strategies

Caching Strategy

• Member Permissions: Cached for authorization performance
• Branch Hierarchies: Cached for organizational queries
• Configuration Settings: In-memory caching for application settings

Data Archival

• Soft Deletion: Preserves audit trails and referential integrity
• Temporal Partitioning: Large tables partitioned by date ranges
• Log Rotation: Audit logs rotated to prevent excessive growth

Security Considerations

Data Protection

• Password Hashing: Secure password storage with salt
• Token Security: Secure random token generation
• Input Sanitization: Database-level constraints and application validation
• Audit Logging: Complete change tracking for sensitive operations

Access Control

• Branch Isolation: Row-level security through branch scoping
• Role-Based Permissions: Granular access control via RBAC
• Temporal Authorization: Time-bounded permission assignments
• Policy-Based Authorization: Custom authorization logic via policies

Future Schema Considerations

Scalability Improvements

• Read Replicas: Database replication for read scaling
• Partitioning: Table partitioning for large datasets
• Archival Strategies: Historical data archival patterns
• Performance Monitoring: Query performance analysis and optimization

Feature Extensibility

• Plugin Schema: Standardized patterns for plugin database extensions
• JSON Schema Evolution: Versioned JSON field schemas
• Multi-Tenancy: Potential multi-tenant architecture patterns
• API Integration: Schema design for external API integration

---

This documentation reflects the current database schema as implemented in the KMP system. For specific implementation details, refer to the migration files in app/config/Migrations/ and the corresponding Table classes in the source code.