Database Migrations: Hierarchies & Access Control

by Alex Johnson 50 views

Overview

In this comprehensive guide, we delve into the implementation of database migrations for customer hierarchies, leveraging the Closure Table Pattern to accommodate unlimited depth for external customer organizations. This approach ensures a scalable and flexible structure for managing customer relationships, a critical component for any organization dealing with complex customer networks. These database migrations are designed to support an unlimited depth for external customer organizations. The Closure Table Pattern is the cornerstone of this implementation, allowing for efficient querying of hierarchical relationships. This pattern is particularly suitable for scenarios where the depth of the hierarchy is not known in advance, providing the flexibility needed for evolving business structures. Using the Closure Table Pattern allows for efficient retrieval of all descendants or ancestors of a given node, a common requirement in customer hierarchy management. This is achieved by storing all possible paths within the hierarchy, enabling simple and performant queries for hierarchical data.

To further understand the broader context of this migration, it's essential to recognize that it is part of a larger initiative, specifically Epic #228: Flexible Organizational Structure & Multi-Level Hierarchies. This epic aims to overhaul the existing organizational structure to provide greater flexibility and support for multi-level hierarchies, a crucial step in adapting to the dynamic needs of modern businesses. Understanding the context within Epic #228 provides a clearer picture of the strategic goals behind these database migrations. The flexible organizational structure is designed to accommodate the evolving needs of the business, ensuring that the system can adapt to changing customer relationships and internal structures. Multi-level hierarchies are essential for representing complex organizational structures, such as those found in large corporations or multi-national organizations. By implementing these migrations, we are laying the foundation for a more adaptable and scalable system, capable of handling the complexities of modern business environments. This comprehensive approach ensures that the database structure aligns with the business requirements, providing a solid foundation for future growth and innovation.

Before diving into the specifics of the migrations, it is highly recommended to review the architectural blueprint outlined in ADR-007, Section: Database Schema - Customer Hierarchies. This document provides a detailed overview of the rationale behind the chosen schema and the considerations that went into its design. Specifically, the section on Customer Hierarchies for External Organizations offers valuable insights into the motivations and objectives of this implementation. Reviewing ADR-007 provides a deeper understanding of the architectural decisions and the reasoning behind the schema design. This context is crucial for developers and database administrators who will be working with these migrations, ensuring that they understand the underlying principles and can effectively maintain and extend the system. The document also serves as a valuable reference point for future discussions and decisions related to the database schema. By aligning the implementation with the architectural vision, we ensure consistency and coherence across the system. This proactive approach minimizes the risk of misunderstandings and ensures that the database structure effectively supports the business requirements.

Required Migrations

This section details the four critical database migrations required to implement customer hierarchies and access control. Each migration focuses on creating and structuring tables that support the Closure Table Pattern, user access management, and fine-grained object access. These migrations are designed to work in harmony, providing a robust and scalable foundation for managing customer relationships and access permissions. The following migrations will create the necessary tables and relationships to support the new customer hierarchy structure. Each migration is carefully designed to ensure data integrity and optimal performance. By implementing these migrations, we are laying the groundwork for a more flexible and secure system for managing customer access and relationships.

1. customers table

The customers table is the core of the customer hierarchy, storing essential information about each customer. This table includes details such as the customer's name, unique customer number, type, business details, and flexible metadata. The customers table serves as the central repository for customer-related information. Each customer record includes a variety of fields, such as name, customer number, type, address, contact information, and metadata. The flexible metadata field allows for storing additional customer-specific information without requiring schema changes. This adaptability is crucial for accommodating the diverse needs of different customers and business scenarios.

Here's the schema definition:

Schema::create('customers', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->foreignUuid('tenant_id')->constrained('tenant_keys');
    
    // Which internal organizational unit manages this customer?
    $table->foreignUuid('managed_by_organizational_unit_id')
        ->nullable()
        ->references('id')->on('organizational_units');
    
    $table->string('name'); // "Rewe Group", "Rewe Region Nord", "Rewe Markt Hamburg"
    $table->string('customer_number')->unique();
    $table->string('type'); // "corporate", "regional", "local", "custom"
    
    // Business details
    $table->text('address')->nullable();
    $table->string('contact_email')->nullable();
    $table->string('contact_phone')->nullable();
    
    // Flexible metadata
    $table->jsonb('metadata')->nullable(); // { "industry": "retail", "contract_start": "2025-01-01" }
    
    $table->timestamps();
    $table->softDeletes();
    
    $table->index(['tenant_id', 'type']);
    $table->index(['tenant_id', 'managed_by_organizational_unit_id']);
});

Key fields and their purposes:

  • id: A UUID primary key for each customer record, ensuring uniqueness and efficient indexing.
  • tenant_id: A foreign key referencing the tenant_keys table, enforcing tenant isolation and data security.
  • managed_by_organizational_unit_id: A foreign key linking the customer to an internal organizational unit, facilitating internal management and reporting. This field is nullable, allowing for customers not directly managed by an organizational unit.
  • name: The name of the customer, providing a human-readable identifier.
  • customer_number: A unique identifier for the customer, ensuring accurate identification and preventing duplication.
  • type: The type of customer (e.g., corporate, regional, local, custom), enabling categorization and tailored treatment.
  • address, contact_email, contact_phone: Business contact details for the customer.
  • metadata: A JSONB column for storing flexible metadata, allowing for custom attributes without schema modifications. This field provides the flexibility to store additional customer-specific information without requiring schema changes.
  • timestamps: Standard timestamp fields for tracking record creation and modification.
  • softDeletes: Enables soft deletion of customer records, preserving data integrity and auditability.
  • Indexes: Indexes on tenant_id, type, and managed_by_organizational_unit_id for optimized querying.

This table is designed with flexibility and scalability in mind, accommodating various customer types and organizational structures. The use of UUIDs for primary keys ensures uniqueness and efficient indexing, while the JSONB metadata column allows for storing custom attributes without requiring schema changes. The foreign key constraints enforce data integrity and ensure that customer records are properly linked to other relevant tables. The indexes are strategically placed to optimize query performance, particularly for common queries involving tenant isolation and customer type.

2. customer_closures table (Closure Table)

The customer_closures table implements the Closure Table Pattern, a crucial element in managing hierarchical relationships. This table stores all ancestor-descendant relationships within the customer hierarchy, enabling efficient querying of hierarchical data. The Closure Table Pattern is essential for efficiently querying hierarchical relationships. This table stores all possible paths within the customer hierarchy, allowing for simple and performant queries to retrieve all descendants or ancestors of a given customer.

Schema definition:

Schema::create('customer_closures', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->foreignUuid('tenant_id')->constrained('tenant_keys');
    
    $table->foreignUuid('ancestor_id')
        ->references('id')->on('customers')->cascadeOnDelete();
    $table->foreignUuid('descendant_id')
        ->references('id')->on('customers')->cascadeOnDelete();
    
    $table->integer('depth'); // 0 = self, 1 = direct child, 2+ = deeper
    
    $table->timestamps();
    
    $table->unique(['ancestor_id', 'descendant_id'], 'unique_customer_ancestor_descendant');
    $table->index(['tenant_id', 'ancestor_id', 'depth']);
    $table->index(['tenant_id', 'descendant_id', 'depth']);
});

Key fields and their purposes:

  • id: A UUID primary key for each closure record, ensuring uniqueness.
  • tenant_id: A foreign key enforcing tenant isolation.
  • ancestor_id: A foreign key referencing the customers table, representing the ancestor in the hierarchy.
  • descendant_id: A foreign key referencing the customers table, representing the descendant in the hierarchy.
  • depth: An integer representing the depth of the relationship (0 for self, 1 for direct child, 2+ for deeper descendants).
  • timestamps: Standard timestamp fields.
  • Unique Index: A unique index on ancestor_id and descendant_id to prevent duplicate relationships.
  • Indexes: Indexes on tenant_id, ancestor_id, and depth for optimized hierarchical queries.

The depth field is crucial for determining the level of relationship between customers. A depth of 0 indicates a self-reference, while a depth of 1 represents a direct child relationship. Depths greater than 1 indicate deeper hierarchical connections. The foreign key constraints with cascadeOnDelete() ensure that related closure records are automatically deleted when a customer is deleted, maintaining data integrity. The unique index on ancestor_id and descendant_id prevents duplicate relationships, ensuring the accuracy of the hierarchy. The indexes on tenant_id, ancestor_id, and depth are strategically placed to optimize queries for hierarchical data, such as retrieving all descendants or ancestors of a given customer.

3. customer_user_accesses table (RBAC Integration)

The customer_user_accesses table integrates customer hierarchies with Role-Based Access Control (RBAC). This table defines user access levels to customers, specifying the scope of access and whether descendants are included. This integration ensures that users have the appropriate level of access to customer data, aligning with security and business requirements. This table is crucial for defining user access levels to customer data. It specifies the scope of access and whether descendants are included, ensuring that users have the appropriate level of access based on their roles and responsibilities.

Schema definition:

Schema::create('customer_user_accesses', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->foreignUuid('tenant_id')->constrained('tenant_keys');
    
    $table->foreignUuid('user_id')->constrained('users')->cascadeOnDelete();
    $table->foreignUuid('customer_id')->constrained('customers')->cascadeOnDelete();
    
    // Access level: 'corporate_wide', 'regional', 'local'
    $table->string('access_level');
    
    // Include descendants in scope? (e.g., Corporate user sees all regional/local)
    $table->boolean('include_descendants')->default(true);
    
    $table->timestamps();
    
    $table->unique(['user_id', 'customer_id'], 'unique_user_customer_access');
    $table->index(['tenant_id', 'user_id']);
});

Key fields and their purposes:

  • id: A UUID primary key.
  • tenant_id: A foreign key enforcing tenant isolation.
  • user_id: A foreign key referencing the users table, representing the user granted access.
  • customer_id: A foreign key referencing the customers table, representing the customer to which access is granted.
  • access_level: A string defining the access level (e.g., 'corporate_wide', 'regional', 'local'), allowing for granular control over access permissions.
  • include_descendants: A boolean flag indicating whether access should include descendants in the hierarchy. This flag is essential for propagating access permissions down the customer hierarchy. If set to true, a user with access to a corporate-level customer will also have access to all regional and local customers within that hierarchy.
  • timestamps: Standard timestamp fields.
  • Unique Index: A unique index on user_id and customer_id to prevent duplicate access records.
  • Index: An index on tenant_id and user_id for optimized access queries.

The access_level field allows for defining different levels of access, such as corporate-wide, regional, or local. This granularity enables precise control over user permissions, ensuring that users only have access to the data they need. The include_descendants flag is crucial for managing hierarchical access. When set to true, a user's access to a customer automatically extends to all descendants in the hierarchy. This simplifies access management for corporate users who need visibility across the entire organization. The foreign key constraints ensure data integrity, while the unique index prevents duplicate access records. The index on tenant_id and user_id optimizes queries for retrieving user access permissions within a specific tenant.

4. customer_user_object_accesses table (Fine-Grained Object Access)

The customer_user_object_accesses table provides fine-grained object access control, allowing specific actions to be granted to users for particular objects. This table is crucial for scenarios where granular permissions are required, such as allowing a user to read specific reports or export data. This level of control enhances security and ensures that sensitive data is protected. This table allows for defining specific actions that users can perform on particular objects. This level of granularity is essential for scenarios where users need access to specific data or functionality without granting broader permissions.

Schema definition:

Schema::create('customer_user_object_accesses', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->foreignUuid('tenant_id')->constrained('tenant_keys');
    
    $table->foreignUuid('user_id')->constrained('users')->cascadeOnDelete();
    $table->foreignUuid('object_id')->constrained('objects')->cascadeOnDelete();
    
    // Allowed actions: ["read_guard_book", "read_reports", "export_reports"]
    $table->jsonb('allowed_actions');
    
    $table->timestamps();
    
    $table->unique(['user_id', 'object_id'], 'unique_user_object_access');
    $table->index(['tenant_id', 'user_id']);
});

Key fields and their purposes:

  • id: A UUID primary key.
  • tenant_id: A foreign key enforcing tenant isolation.
  • user_id: A foreign key referencing the users table.
  • object_id: A foreign key referencing the objects table, representing the object to which access is being granted.
  • allowed_actions: A JSONB column storing an array of allowed actions (e.g., ["read_guard_book", "read_reports", "export_reports"]). The use of JSONB allows for flexible and extensible permission definitions without requiring schema changes.
  • timestamps: Standard timestamp fields.
  • Unique Index: A unique index on user_id and object_id to prevent duplicate access records.
  • Index: An index on tenant_id and user_id for optimized access queries.

The allowed_actions column, stored as JSONB, provides a flexible way to define permissions. This allows for adding new actions without modifying the database schema, making the system highly adaptable to evolving business needs. The foreign key constraints ensure data integrity, and the unique index prevents duplicate access records. The index on tenant_id and user_id optimizes queries for retrieving user object access permissions within a specific tenant.

Database Engine Considerations

Specific database engines may require additional configurations to fully support the features implemented in these migrations. This section outlines considerations for SQLite and PostgreSQL, two popular database systems. These considerations are crucial for ensuring that the migrations function correctly and efficiently in the target environment. Understanding these engine-specific requirements is essential for a successful implementation.

SQLite

SQLite requires enabling recursive Common Table Expression (CTE) support to handle hierarchical queries efficiently. This can be achieved by executing the following SQL statement:

DB::statement('PRAGMA recursive_triggers = ON;');

Enabling recursive triggers is essential for SQLite to properly execute hierarchical queries using CTEs. This setting allows SQLite to handle recursive queries, which are necessary for traversing the customer hierarchy stored in the customer_closures table. Without this setting, hierarchical queries may fail or return incorrect results. It's important to ensure that this statement is executed during the migration process or database initialization to enable the required functionality.

PostgreSQL

PostgreSQL benefits from a CHECK constraint to prevent cycles in the customer hierarchy. This constraint ensures data integrity by preventing the creation of circular relationships, which can lead to infinite loops and incorrect query results. The following SQL statement adds a CHECK constraint to the customer_closures table:

DB::statement('
    ALTER TABLE customer_closures
    ADD CONSTRAINT check_no_cycles
    CHECK (ancestor_id != descendant_id OR depth = 0)
');

This CHECK constraint ensures that an ancestor cannot also be a descendant of itself, except for self-referencing rows where the depth is 0. This constraint is crucial for maintaining the integrity of the customer hierarchy and preventing logical errors. By adding this constraint, we ensure that the database enforces the hierarchical structure and prevents invalid relationships from being created. This proactive measure helps to maintain data quality and ensures the reliability of hierarchical queries.

Acceptance Criteria

The successful implementation of these database migrations is contingent upon meeting the following acceptance criteria. These criteria ensure that the migrations are correctly implemented, data integrity is maintained, and the system functions as expected. Meeting these criteria is essential for a successful deployment and ensures that the new customer hierarchy and access control mechanisms function correctly.

  • [ ] All four migrations created and run successfully: Verifies that the migration files are created and executed without errors.
  • [ ] Foreign key constraints properly defined: Ensures that relationships between tables are correctly enforced, maintaining data integrity.
  • [ ] Indexes created for hierarchical queries: Confirms that indexes are in place to optimize query performance, particularly for hierarchical data retrieval.
  • [ ] Tenant isolation enforced via tenant_id: Validates that data is properly isolated between tenants, ensuring data security and privacy.
  • [ ] Soft deletes enabled for customers: Checks that soft delete functionality is implemented for the customers table, allowing for data recovery and auditability.
  • [ ] Self-referencing rows in closure table (depth = 0): Verifies that the customer_closures table correctly stores self-referencing relationships, essential for the Closure Table Pattern.
  • [ ] Database tests verify:
    • Customer hierarchies are independent from organizational units: Ensures that customer hierarchies and internal organizational structures are managed separately, providing flexibility and avoiding dependencies.
    • Cascading deletes work correctly: Confirms that related records are automatically deleted when a customer is deleted, maintaining data integrity.
    • Customer user access scopes respect hierarchy: Validates that user access permissions are correctly applied based on the customer hierarchy, including the include_descendants flag.
    • allowed_actions jsonb column stores array correctly: Checks that the JSONB column in customer_user_object_accesses correctly stores and retrieves arrays of allowed actions.
    • Tenant isolation prevents cross-tenant customer queries: Ensures that queries are properly scoped to the current tenant, preventing unauthorized access to data.

Testing Strategy

A robust testing strategy is essential to ensure the correctness and reliability of the database migrations. The following example test cases provide a starting point for validating the implementation. These tests cover various aspects of the migrations, including data integrity, hierarchical relationships, and access control. A comprehensive testing strategy is crucial for ensuring that the new customer hierarchy and access control mechanisms function correctly and reliably.

// Example test cases
test('customer closure table stores self-reference')
test('customer hierarchy is independent from internal org structure')
test('customer user access includes descendants when flag is true')
test('customer user object access allows fine-grained permissions')
test('tenant isolation prevents cross-tenant customer queries')

These test cases cover critical aspects of the migrations, such as:

  • Self-reference in the closure table: Ensures that the customer_closures table correctly stores self-referencing relationships, a fundamental aspect of the Closure Table Pattern.
  • Independence of customer hierarchy from internal organizational structure: Validates that customer hierarchies and internal organizational structures are managed independently, providing flexibility and avoiding dependencies.
  • Hierarchical user access: Checks that user access permissions correctly include descendants in the hierarchy when the include_descendants flag is true.
  • Fine-grained object access: Ensures that the customer_user_object_accesses table allows for granting specific actions to users for particular objects.
  • Tenant isolation: Verifies that tenant isolation is properly enforced, preventing cross-tenant data access.

These tests should be expanded to cover a wide range of scenarios and edge cases, ensuring the robustness of the database migrations. Additional tests should include:

  • Testing different depths of customer hierarchies.
  • Validating the performance of hierarchical queries.
  • Testing the impact of cascading deletes on related records.
  • Ensuring that unique constraints are enforced.
  • Testing the behavior of the system under concurrent access.

Notes

This section highlights key considerations and design principles underlying the database migrations. These notes provide additional context and insights into the implementation, aiding in understanding the rationale behind specific decisions. These notes are crucial for developers and database administrators who will be working with these migrations, ensuring that they understand the underlying principles and can effectively maintain and extend the system.

  • Independence: Customer hierarchies are completely separate from internal organizational units. This separation provides flexibility in managing customer relationships independently of internal structures. This design principle allows for evolving customer hierarchies without impacting internal organizational structures, and vice versa. This separation is crucial for accommodating diverse business scenarios and ensuring that the system can adapt to changing customer relationships and internal structures.
  • Management Link: managed_by_organizational_unit_id links customers to internal structure for internal use only (invisible to customer users). This link facilitates internal management and reporting but does not affect customer-facing access or permissions. This field provides a mechanism for internal teams to manage customers without exposing this information to customer users. This separation enhances security and ensures that customer users only see information relevant to their access permissions.
  • Read-Only: Customer users typically have read-only access (guard_book.read, reports.export). This principle minimizes the risk of accidental data modification and ensures data integrity. Read-only access is the default for customer users, aligning with security best practices and minimizing the potential for data breaches or accidental modifications. This principle ensures that customer users can access the information they need without compromising the integrity of the data.
  • Flexibility: allowed_actions as jsonb allows extensibility without schema changes. This approach enables the system to adapt to new requirements without requiring database schema modifications, reducing maintenance overhead and downtime. The use of JSONB for storing allowed actions provides a flexible way to define permissions. This allows for adding new actions without modifying the database schema, making the system highly adaptable to evolving business needs.

Implementation Order: This is Phase 1, Task 2. Should be developed in parallel with #229.

In conclusion, these database migrations lay the groundwork for a flexible and scalable system for managing customer hierarchies and access control. By implementing the Closure Table Pattern and providing fine-grained access control mechanisms, we are ensuring that the system can adapt to evolving business needs and maintain data integrity. Remember to check out this guide on database migration strategies on DigitalOcean for more in-depth information.