3.5 Database Schema & Entity Relationship Diagrams

Overview

This document provides comprehensive database schema documentation for the KMP (Kingdom Management Platform), including detailed Entity Relationship (ER) diagrams, SQL schema definitions, and architectural patterns. The schema implements a sophisticated data model supporting organizational management for SCA (Society for Creative Anachronism) branches with hierarchical data, temporal relationships, role-based access control, and comprehensive 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
• Plugin Extensibility: Standardized patterns for plugin database extensions

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 System Schema & ER Diagrams

Members and Authentication

erDiagram
    members {
        int id PK
        varchar sca_name
        varchar first_name
        varchar last_name
        varchar email_address UK
        varchar password
        varchar membership_number
        date membership_expires_on
        int branch_id FK
        int parent_id FK
        varchar status
        datetime verified_date
        int verified_by FK
        varchar title
        varchar pronouns
        varchar pronunciation
        boolean warrantable
        json additional_info
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    branches {
        int id PK
        varchar name UK
        varchar location
        int parent_id FK
        varchar type
        varchar domain
        text links
        boolean can_have_members
        int lft
        int rght
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    members ||--o{ members : "parent_child"
    members }o--|| branches : "member_branch"
    branches ||--o{ branches : "parent_child"
    members ||--o{ members : "verified_by"

Role-Based Access Control (RBAC)

erDiagram
    roles {
        int id PK
        varchar name UK
        boolean is_system
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    permissions {
        int id PK
        varchar name UK
        boolean require_active_membership
        boolean require_active_background_check
        int require_min_age
        boolean is_system
        boolean is_super_user
        boolean requires_warrant
        varchar scoping_rule
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    roles_permissions {
        int id PK
        int role_id FK
        int permission_id FK
        timestamp created
        int created_by FK
    }
    
    member_roles {
        int id PK
        int member_id FK
        int role_id FK
        int branch_id FK
        varchar entity_type
        int entity_id
        datetime start_on
        datetime expires_on
        int approver_id FK
        int revoker_id FK
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    permission_policies {
        int id PK
        int permission_id FK
        varchar policy_class
        varchar policy_method
    }
    
    roles ||--o{ roles_permissions : "role_permissions"
    permissions ||--o{ roles_permissions : "permission_roles"
    permissions ||--o{ permission_policies : "permission_policies"
    members ||--o{ member_roles : "member_roles"
    roles ||--o{ member_roles : "role_assignments"
    branches ||--o{ member_roles : "branch_roles"
    members ||--o{ member_roles : "approver"
    members ||--o{ member_roles : "revoker"

Warrant System

erDiagram
    warrant_periods {
        int id PK
        date start_date
        date end_date
        datetime created
        int created_by FK
    }
    
    warrant_rosters {
        int id PK
        varchar name
        varchar status
        int approvals_required
        int approval_count
        datetime created
        int created_by FK
        datetime modified
        int modified_by FK
    }
    
    warrant_roster_approvals {
        int id PK
        int warrant_roster_id FK
        int approver_id FK
        datetime approved_on
    }
    
    warrants {
        int id PK
        varchar name
        int member_id FK
        int warrant_roster_id FK
        varchar entity_type
        int entity_id
        int member_role_id FK
        datetime start_on
        datetime expires_on
        datetime approved_date
        int approver_id FK
        varchar revoked_reason
        int revoker_id FK
        varchar status
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
    }
    
    warrant_periods ||--o{ warrants : "warrant_period"
    warrant_rosters ||--o{ warrants : "warrant_roster"
    warrant_rosters ||--o{ warrant_roster_approvals : "roster_approvals"
    members ||--o{ warrant_roster_approvals : "approver"
    members ||--o{ warrants : "warrant_holder"
    member_roles ||--o{ warrants : "warrant_role"
    members ||--o{ warrants : "warrant_approver"
    members ||--o{ warrants : "warrant_revoker"

Notes and Configuration

erDiagram
    notes {
        int id PK
        int author_id FK
        varchar entity_type
        int entity_id
        varchar subject
        text body
        boolean private
        timestamp created
    }
    
    app_settings {
        int id PK
        varchar name UK
        text value
        varchar type
        boolean required
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
    }
    
    queue_processes {
        int id PK
        varchar pid UK
        varchar server UK
        varchar workerkey UK
        tinyint terminate
        datetime created
        datetime modified
    }
    
    queued_jobs {
        int id PK
        varchar job_task
        varchar job_group
        varchar reference
        text data
        varchar status
        int priority
        varchar workerkey
        datetime notbefore
        datetime fetched
        float progress
        datetime completed
        text failure_message
        int attempts
        datetime created
    }
    
    members ||--o{ notes : "note_creator"
    members ||--o{ notes : "note_modifier"

Detailed SQL Schema Definitions

1. Core Member 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(50) 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
• JSON Configuration: Flexible additional_info field for extensibility
• Audit Trail: Complete creation and modification tracking

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)
);

2. Role-Based Access Control (RBAC)

roles Table

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

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)
);

member_roles Table

Temporal role assignments 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 field is virtual (computed from ActiveWindowBaseEntity)
    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_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)
);

Note: The status field is not a physical database column but is computed dynamically by the ActiveWindowBaseEntity class based on the start_on and expires_on dates.

3. Additional Core Tables

app_settings Table

CREATE TABLE app_settings (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    value TEXT NULL,                      -- Always TEXT field
    type VARCHAR(255) NULL,               -- Added via migration
    required BOOLEAN DEFAULT false,       -- Added via migration
    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)
);

notes Table

Polymorphic notes system.

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
    entity_id INT(11) NOT NULL,        -- Renamed from topic_id  
    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)
);

warrants Table

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 VARCHAR(255) NULL,
    revoker_id INT(11) NULL,
    status VARCHAR(20) DEFAULT 'Pending',
    created DATETIME NOT NULL,
    modified DATETIME NULL,
    created_by INT(11) NULL,
    modified_by INT(11) 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),
    
    FOREIGN KEY (member_id) REFERENCES members(id),
    FOREIGN KEY (member_role_id) REFERENCES member_roles(id),
    FOREIGN KEY (warrant_roster_id) REFERENCES warrant_rosters(id)
);

Plugin Schema Extensions

Officers Plugin ER Diagram

Organizational Structure

erDiagram
    officers_departments {
        int id PK
        varchar name UK
        varchar domain
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    officers_offices {
        int id PK
        varchar name UK
        int department_id FK
        boolean requires_warrant
        boolean required_office
        boolean only_one_per_branch
        boolean can_skip_report
        int deputy_to_id FK
        int reports_to_id FK
        int grants_role_id FK
        int term_length
        varchar applicable_branch_types
        varchar default_contact_address
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    officers_officers {
        int id PK
        int member_id FK
        int branch_id FK
        int office_id FK
        int granted_member_role_id FK
        datetime start_on
        datetime expires_on
        varchar status
        text deputy_description
        text revoked_reason
        int revoker_id FK
        int deputy_to_branch_id FK
        int deputy_to_office_id FK
        int reports_to_branch_id FK
        int reports_to_office_id FK
        varchar email_address
        int approver_id FK
        datetime approval_date
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    officers_departments ||--o{ officers_offices : "department_offices"
    officers_offices ||--o{ officers_offices : "deputy_to"
    officers_offices ||--o{ officers_offices : "reports_to"
    roles ||--o{ officers_offices : "grants_role"
    officers_offices ||--o{ officers_officers : "office_assignments"
    members ||--o{ officers_officers : "officer_member"
    branches ||--o{ officers_officers : "officer_branch"
    member_roles ||--o{ officers_officers : "granted_role"
    members ||--o{ officers_officers : "approver"
    members ||--o{ officers_officers : "revoker"
    branches ||--o{ officers_officers : "deputy_to_branch"
    officers_offices ||--o{ officers_officers : "deputy_to_office"
    branches ||--o{ officers_officers : "reports_to_branch"
    officers_offices ||--o{ officers_officers : "reports_to_office"

Awards Plugin ER Diagram

Award Classification System

erDiagram
    awards_domains {
        int id PK
        varchar name UK
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    awards_levels {
        int id PK
        varchar name UK
        int progression_order
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    awards_awards {
        int id PK
        varchar name
        varchar abbreviation
        text description
        text insignia
        text badge
        text charter
        int domain_id FK
        int level_id FK
        int branch_id FK
        text specialties
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    awards_domains ||--o{ awards_awards : "award_domain"
    awards_levels ||--o{ awards_awards : "award_level"
    branches ||--o{ awards_awards : "award_branch"

Recommendation and Event System

erDiagram
    awards_events {
        int id PK
        varchar name
        varchar description
        datetime start_date
        datetime end_date
        tinyint closed
        int branch_id FK
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    awards_recommendations {
        int id PK
        int requester_id FK
        varchar requester_sca_name
        int member_id FK
        varchar member_sca_name
        int award_id FK
        int branch_id FK
        int event_id FK
        text reason
        varchar specialty
        varchar contact_email
        varchar contact_number
        varchar person_to_notify
        varchar call_into_court
        varchar court_availability
        int stack_rank
        varchar state
        datetime state_date
        varchar status
        text close_reason
        text no_action_reason
        datetime given
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    awards_recommendations_events {
        int id PK
        int recommendation_id FK
        int event_id FK
    }
    
    awards_recommendations_states_logs {
        int id PK
        int recommendation_id FK
        varchar from_state
        varchar from_status
        varchar to_state
        varchar to_status
        datetime created
        int created_by FK
    }
    
    branches ||--o{ awards_events : "event_branch"
    members ||--o{ awards_recommendations : "requester"
    members ||--o{ awards_recommendations : "recommended_member"
    awards_awards ||--o{ awards_recommendations : "recommended_award"
    branches ||--o{ awards_recommendations : "recommendation_branch"
    awards_events ||--o{ awards_recommendations : "assigned_event"
    awards_recommendations ||--o{ awards_recommendations_events : "recommendation_events"
    awards_events ||--o{ awards_recommendations_events : "event_recommendations"
    awards_recommendations ||--o{ awards_recommendations_states_logs : "state_changes"

Activities Plugin ER Diagram

Activity Authorization System

erDiagram
    activities_activity_groups {
        int id PK
        varchar name UK
        text description
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    activities_activities {
        int id PK
        varchar name
        int activity_group_id FK
        int grants_role_id FK
        int permission_id FK
        int maximum_age
        int minimum_age
        int num_required_authorizors
        int num_required_renewers
        int term_length
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    activities_authorizations {
        int id PK
        int member_id FK
        int activity_id FK
        int granted_member_role_id FK
        int approval_count
        tinyint is_renewal
        datetime start_on
        datetime expires_on
        varchar status
        varchar revoked_reason
        int revoker_id FK
        timestamp created
    }
    
    activities_authorization_approvals {
        int id PK
        int authorization_id FK
        int approver_id FK
        tinyint approved
        varchar approver_notes
        varchar authorization_token
        datetime requested_on
        datetime responded_on
    }
    
    activities_activity_groups ||--o{ activities_activities : "activity_group"
    roles ||--o{ activities_activities : "grants_role"
    permissions ||--o{ activities_activities : "activity_permission"
    members ||--o{ activities_authorizations : "authorized_member"
    activities_activities ||--o{ activities_authorizations : "activity_authorization"
    member_roles ||--o{ activities_authorizations : "granted_role"
    members ||--o{ activities_authorizations : "revoker"
    activities_authorizations ||--o{ activities_authorization_approvals : "authorization_approvals"
    members ||--o{ activities_authorization_approvals : "approval_member"

System Integration Diagram

Cross-Plugin Relationships

erDiagram
    %% Core System
    members {
        int id PK
        varchar sca_name
        varchar email_address UK
        int branch_id FK
        boolean warrantable
    }
    
    branches {
        int id PK
        varchar name UK
        varchar type
        int parent_id FK
    }
    
    member_roles {
        int id PK
        int member_id FK
        int role_id FK
        int branch_id FK
        datetime start_on
        datetime expires_on
        varchar status
    }
    
    warrants {
        int id PK
        int member_id FK
        varchar entity_type
        int entity_id
        int member_role_id FK
        varchar status
    }
    
    %% Officers Plugin
    officers_officers {
        int id PK
        int member_id FK
        int branch_id FK
        int office_id FK
        int granted_member_role_id FK
        varchar status
    }
    
    %% Awards Plugin
    awards_recommendations {
        int id PK
        int member_id FK
        int award_id FK
        int branch_id FK
        varchar state
    }
    
    %% Activities Plugin
    activities_authorizations {
        int id PK
        int member_id FK
        int activity_id FK
        int granted_member_role_id FK
        varchar status
    }
    
    %% Cross-plugin relationships
    members ||--o{ officers_officers : "officer_assignments"
    members ||--o{ awards_recommendations : "award_recommendations"
    members ||--o{ activities_authorizations : "activity_authorizations"
    branches ||--o{ officers_officers : "officer_branches"
    branches ||--o{ awards_recommendations : "recommendation_branches"
    member_roles ||--o{ officers_officers : "officer_roles"
    member_roles ||--o{ activities_authorizations : "authorization_roles"
    warrants ||--o{ officers_officers : "warrant_entities"
    warrants ||--o{ activities_authorizations : "warrant_entities"

Temporal Relationship Patterns

ActiveWindow Entities

Many entities in KMP use the ActiveWindow pattern for temporal management:

erDiagram
    temporal_entity {
        int id PK
        datetime start_on
        datetime expires_on
        varchar status
        datetime created
        datetime modified
    }
    
    %% Status transitions based on time
    %% new -> upcoming -> current -> expired
    %% new -> upcoming -> current -> revoked

Status Lifecycle:

1. new: Created but not yet active
2. upcoming: Future activation date set
3. current: Currently active (between start_on and expires_on)
4. expired: Past expiration date
5. revoked: Manually terminated before expiration

Audit Trail Pattern

All major entities include comprehensive audit trails:

erDiagram
    auditable_entity {
        int id PK
        datetime created
        datetime modified
        int created_by FK
        int modified_by FK
        datetime deleted
    }
    
    members {
        int id PK
    }
    
    auditable_entity }o--|| members : "created_by"
    auditable_entity }o--|| members : "modified_by"

Polymorphic Relationships

Notes System

The notes system uses polymorphic associations to attach to any entity:

erDiagram
    notes {
        int id PK
        varchar topic_model
        int topic_id
        varchar subject
        text body
        boolean private
    }
    
    members {
        int id PK
    }
    
    awards_recommendations {
        int id PK
    }
    
    officers_officers {
        int id PK
    }
    
    notes }o--|| members : "when topic_model = 'Members'"
    notes }o--|| awards_recommendations : "when topic_model = 'Awards.Recommendations'"
    notes }o--|| officers_officers : "when topic_model = 'Officers.Officers'"

Warrant System

Warrants can be assigned to various entity types:

erDiagram
    warrants {
        int id PK
        varchar entity_type
        int entity_id
        int member_id FK
        varchar status
    }
    
    officers_officers {
        int id PK
    }
    
    activities_authorizations {
        int id PK
    }
    
    warrants }o--|| officers_officers : "when entity_type = 'Officers.Officers'"
    warrants }o--|| activities_authorizations : "when entity_type = 'Activities.Authorizations'"

JSON Field Structures

Branch Links Configuration

{
  "website": "https://branch.example.com",
  "facebook": "https://facebook.com/branchpage",
  "calendar": "https://calendar.google.com/...",
  "newsletter": "https://newsletter.example.com"
}

Member Additional Information

{
  "emergency_contact": {
    "name": "Jane Doe",
    "phone": "555-0123",
    "relationship": "spouse"
  },
  "dietary_restrictions": ["vegetarian", "nut allergy"],
  "interests": ["archery", "cooking", "music"],
  "awards_received": []
}

Office Branch Type Constraints

["Local", "College", "Household"]

Award Specialties

{
  "archery": ["target", "combat", "mounted"],
  "arts_sciences": ["cooking", "scribal", "clothing"],
  "service": ["event_steward", "officer", "teaching"]
}

Database Design Principles

1. Referential Integrity

• All foreign key relationships properly constrained
• Cascade deletes for junction tables
• Soft deletes for entity tables to preserve history

2. Temporal Consistency

• ActiveWindow pattern ensures consistent temporal behavior
• Status fields automatically maintained based on dates
• Lifecycle transitions properly validated

3. Hierarchical Data

• Branch hierarchy using nested set model for efficient queries
• Self-referential relationships for office hierarchies
• Parent-child relationships for member management

4. Extensibility

• JSON fields for flexible configuration
• Polymorphic relationships for cross-cutting concerns
• Plugin-based schema extensions

5. Performance Optimization

• Comprehensive indexing strategy
• Efficient query patterns for common operations
• Caching integration for frequently accessed data

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

Implementation Note: The status field is not stored in the database but is computed dynamically by the ActiveWindowBaseEntity class. Entities that extend this base class (like MemberRole, Officer, Authorization) inherit this virtual status calculation.

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

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
entity_id INT(11) NOT NULL,        // Entity ID

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

Migration Tracking Tables

The system maintains Phinx migration logs for each schema section:

Core Migration Log

• phinxlog: Core system migration tracking (not visible in export - likely filtered)

Plugin Migration Logs

• activities_phinxlog: Activities plugin migration history
• officers_phinxlog: Officers plugin migration history
• Awards and other plugins use similar patterns

These tables track database schema version history and are managed automatically by the Phinx migration framework.

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

---

This comprehensive database schema documentation includes both ER diagrams and detailed SQL definitions for the complete KMP system. The schema reflects the current state of the database as implemented through migrations, with fact-checked accuracy against the actual codebase as of July 2025.