ske087/quality_app-v2
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
quality_app-v2/documentation/WAREHOUSE_ROLES_AND_ACCESS_CONTROL.md
Quality App Developer 8de85ca87f feat: Implement warehouse module roles with auto-schema repair and remove module access section 
- Add SchemaVerifier class for automatic database schema verification and repair
- Implement warehouse_manager (Level 75) and warehouse_worker (Level 35) roles
- Add zone-based access control for warehouse workers
- Implement worker-manager binding system with zone filtering
- Add comprehensive database auto-repair on Docker initialization
- Remove Module Access section from user form (role-based access only)
- Add autocomplete attributes to password fields for better UX
- Include detailed documentation for warehouse implementation
- Update initialize_db.py with schema verification as Step 0
2026-01-28 00:46:59 +02:00

499 lines
17 KiB
Markdown
Raw Permalink Blame History

# Warehouse Module: Roles, Access Control & Worker-Manager Binding
## Executive Summary
This document proposes a comprehensive role-based access control (RBAC) system for the warehouse module, including:
1. **New warehouse module roles** (warehouse_manager, warehouse_worker)
2. **Worker-Manager binding model** for hierarchical access control
3. **Granular access control rules** differentiating manager vs worker capabilities
4. **Database schema extensions** to support these features
---
## 1. Current System Architecture
### Existing Roles (in `access_control.py`)
```python
ROLES = {
'superadmin': Level 100 - Full system access (all modules)
'admin': Level 90 - Administrative access (quality, settings)
'manager': Level 70 - Full quality module access
'worker': Level 50 - Limited quality inspections
}
```
### Current Module Structure
- **quality**: Inspections + Reports
- Workers can: View + Create inspections
- Managers can: View, Create, Edit, Delete inspections + Export/Download reports
- **settings**: System configuration
- Only superadmin/admin can access
- **warehouse**: NEW - Boxes management, locations, inventory
---
## 2. Proposed Warehouse Module Roles
### 2.1 New Roles for Warehouse Module
#### **warehouse_manager** (Level 75)
- **Description**: Full warehouse module access - manages operations, inventory, and reports
- **Assigned Modules**: `['quality', 'warehouse']` (if quality access granted)
- **Page Access**: ALL warehouse pages (input + report/analytics)
- **Key Capabilities**:
- Set box locations (input pages)
- Create/manage warehouse locations
- View inventory reports and analytics
- Export warehouse data
- Download reports
- Manage warehouse users/workers
#### **warehouse_worker** (Level 35)
- **Description**: Limited warehouse module access - can only input data, no reports
- **Assigned Modules**: `['warehouse']`
- **Page Access**: INPUT PAGES ONLY
- **Key Capabilities**:
- Set box locations
- Create/update warehouse entries
- View own submitted data
- ✗ Cannot view reports
- ✗ Cannot access analytics
- ✗ Cannot export data
- ✗ Cannot manage other users
---
## 3. Warehouse Module Page Structure
### 3.1 Input Pages (accessible to both manager & worker)
```
/warehouse/ Dashboard/launcher
/warehouse/set-boxes-locations Add/update box inventory
/warehouse/locations Create/manage location codes
/warehouse/set-boxes-locations Quick entry form
```
### 3.2 Report/Analytics Pages (MANAGER ONLY)
```
/warehouse/reports Analytics & reports dashboard
/warehouse/reports/inventory-summary Inventory overview
/warehouse/reports/location-usage Location utilization stats
/warehouse/reports/stock-movements Historical movements
/warehouse/reports/export Data export interface
/warehouse/analytics/trends Trend analysis
```
---
## 4. Worker-Manager Binding Model
### 4.1 Database Schema: `worker_manager_bindings`
```sql
CREATE TABLE worker_manager_bindings (
id INT AUTO_INCREMENT PRIMARY KEY,
manager_id INT NOT NULL, -- warehouse_manager user ID
worker_id INT NOT NULL, -- warehouse_worker user ID
warehouse_zone VARCHAR(100), -- OPTIONAL: restrict worker to specific zone
is_active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY unique_binding (manager_id, worker_id),
FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (worker_id) REFERENCES users(id) ON DELETE CASCADE,
CHECK (manager_id != worker_id) -- Prevent self-binding
);
```
### 4.2 Binding Semantics
- **One-to-Many Relationship**: A manager can oversee multiple workers, but a worker is assigned to ONE primary manager
- **Optional Zone Restriction**: Workers can be confined to specific warehouse zones (e.g., "Zone A", "Cold Storage", "High Shelf")
- **Hierarchy Enforcement**:
- Managers can only view/edit data from their assigned workers
- Workers can only see their own data
- Superadmin/admin can override and see everything
---
## 5. Access Control Rules (MODULE_PERMISSIONS)
### 5.1 Updated Structure for Warehouse Module
```python
MODULE_PERMISSIONS = {
'warehouse': {
'name': 'Warehouse Module',
'sections': {
'input': {
'name': 'Warehouse Data Input',
'actions': {
'view': 'View warehouse input pages',
'create': 'Create warehouse entries',
'edit': 'Edit warehouse entries',
'delete': 'Delete warehouse entries'
},
'superadmin': ['view', 'create', 'edit', 'delete'],
'admin': ['view', 'create', 'edit', 'delete'],
'warehouse_manager': ['view', 'create', 'edit', 'delete'],
'warehouse_worker': ['view', 'create', 'edit'],
'manager': [], # Quality managers don't auto-get warehouse access
'worker': []
},
'reports': {
'name': 'Warehouse Reports & Analytics',
'actions': {
'view': 'View warehouse reports',
'export': 'Export warehouse data',
'download': 'Download reports',
'analytics': 'View analytics'
},
'superadmin': ['view', 'export', 'download', 'analytics'],
'admin': ['view', 'export', 'download', 'analytics'],
'warehouse_manager': ['view', 'export', 'download', 'analytics'],
'warehouse_worker': [], # Workers get NO report access
'manager': [],
'worker': []
},
'locations': {
'name': 'Location Management',
'actions': {
'view': 'View locations',
'create': 'Create locations',
'edit': 'Edit locations',
'delete': 'Delete locations'
},
'superadmin': ['view', 'create', 'edit', 'delete'],
'admin': ['view', 'create', 'edit', 'delete'],
'warehouse_manager': ['view', 'create', 'edit', 'delete'],
'warehouse_worker': ['view'], # Workers can only view existing locations
'manager': [],
'worker': []
},
'management': {
'name': 'Warehouse User Management',
'actions': {
'manage_workers': 'Manage assigned workers',
'manage_zones': 'Manage warehouse zones'
},
'superadmin': ['manage_workers', 'manage_zones'],
'admin': ['manage_workers', 'manage_zones'],
'warehouse_manager': ['manage_workers'], # Can assign/manage workers
'warehouse_worker': [], # No management access
'manager': [],
'worker': []
}
}
}
}
```
---
## 6. Implementation Details
### 6.1 Database Changes Required
1. **Insert new roles** into `roles` table:
```sql
INSERT INTO roles (name, description, level) VALUES
('warehouse_manager', 'Warehouse Manager - Full warehouse module access', 75),
('warehouse_worker', 'Warehouse Worker - Input-only warehouse access', 35);
```
2. **Create worker-manager binding table** (see schema above)
3. **Update user interface** to assign warehouse roles
### 6.2 Code Changes
#### A. Update `access_control.py`
Add new roles:
```python
ROLES = {
# ... existing roles ...
'warehouse_manager': {
'name': 'Warehouse Manager',
'description': 'Full access to warehouse module operations',
'level': 75,
'modules': ['warehouse']
},
'warehouse_worker': {
'name': 'Warehouse Worker',
'description': 'Limited warehouse access - input only, no reports',
'level': 35,
'modules': ['warehouse']
}
}
```
Add warehouse module permissions (see 5.1 above)
#### B. Create route decorators
```python
def can_access_warehouse_reports(f):
"""Only warehouse_manager, admin, superadmin can access reports"""
@wraps(f)
def decorated_function(*args, **kwargs):
user_role = session.get('role', 'worker')
if user_role not in ['superadmin', 'admin', 'warehouse_manager']:
flash('Access denied: Only managers can view warehouse reports', 'error')
return redirect(url_for('warehouse.warehouse_index'))
return f(*args, **kwargs)
return decorated_function
def can_access_warehouse_input(f):
"""warehouse_manager and warehouse_worker can access input pages"""
@wraps(f)
def decorated_function(*args, **kwargs):
user_role = session.get('role', 'worker')
if user_role not in ['superadmin', 'admin', 'warehouse_manager', 'warehouse_worker']:
flash('Access denied: You do not have warehouse access', 'error')
return redirect(url_for('main.dashboard'))
return f(*args, **kwargs)
return decorated_function
```
#### C. Worker data filtering
Workers should only see their own entries. Managers see all their workers' entries.
```python
def get_worker_data_filter(user_id, user_role):
"""Get SQL WHERE clause based on role and worker-manager binding"""
if user_role == 'superadmin' or user_role == 'admin':
return "" # No filter - see everything
if user_role == 'warehouse_manager':
# Get all workers assigned to this manager
cursor = get_db().cursor()
cursor.execute("""
SELECT worker_id FROM worker_manager_bindings
WHERE manager_id = %s AND is_active = 1
""", (user_id,))
worker_ids = [row[0] for row in cursor.fetchall()]
if worker_ids:
return f"AND created_by_user_id IN ({','.join(map(str, worker_ids))})"
return "AND created_by_user_id = %s" % user_id # If no workers, see own data
if user_role == 'warehouse_worker':
return f"AND created_by_user_id = {user_id}" # Only their own data
return ""
```
---
## 7. User Interface Changes
### 7.1 User Creation/Edit Form (`user_form.html`)
Add warehouse role options to the role dropdown:
```html
<!-- Existing roles -->
<option value="superadmin">Super Admin (Level 100)</option>
<option value="admin">Admin (Level 90)</option>
<option value="manager">Manager - Quality (Level 70)</option>
<option value="warehouse_manager">Manager - Warehouse (Level 75)</option>
<option value="worker">Worker - Quality (Level 50)</option>
<option value="warehouse_worker">Worker - Warehouse (Level 35)</option>
```
### 7.2 New Page: Warehouse Worker Assignment
Create `/settings/warehouse-worker-assignment` page:
- Managers can assign workers to themselves
- Shows assigned workers with zone restrictions
- Allows zone-specific filtering
---
## 8. Configuration in `access_control.py`
### 8.1 Complete Updated ROLES Dictionary
```python
ROLES = {
'superadmin': {
'name': 'Super Administrator',
'description': 'Full system access to all modules and features',
'level': 100,
'modules': ['quality', 'settings', 'warehouse']
},
'admin': {
'name': 'Administrator',
'description': 'Administrative access - can manage users and system configuration',
'level': 90,
'modules': ['quality', 'settings', 'warehouse']
},
'manager': {
'name': 'Manager - Quality',
'description': 'Full access to quality module and quality control',
'level': 70,
'modules': ['quality']
},
'warehouse_manager': {
'name': 'Manager - Warehouse',
'description': 'Full access to warehouse module - input and reports',
'level': 75,
'modules': ['warehouse']
},
'worker': {
'name': 'Worker - Quality',
'description': 'Limited access to quality inspections - input only',
'level': 50,
'modules': ['quality']
},
'warehouse_worker': {
'name': 'Worker - Warehouse',
'description': 'Limited access to warehouse - input pages only, no reports',
'level': 35,
'modules': ['warehouse']
}
}
```
---
## 9. Page Access Matrix
| Page | superadmin | admin | manager (quality) | warehouse_manager | worker (quality) | warehouse_worker |
|------|:----------:|:-----:|:-----------------:|:----------------:|:---------------:|:----------------:|
| `/warehouse/` | ✓ | ✓ | ✗ | ✓ | ✗ | ✓ |
| `/warehouse/set-boxes-locations` | ✓ | ✓ | ✗ | ✓ | ✗ | ✓ |
| `/warehouse/locations` | ✓ | ✓ | ✗ | ✓ | ✗ | ✓ |
| `/warehouse/reports` | ✓ | ✓ | ✗ | ✓ | ✗ | ✗ |
| `/warehouse/reports/inventory-summary` | ✓ | ✓ | ✗ | ✓ | ✗ | ✗ |
| `/warehouse/analytics/trends` | ✓ | ✓ | ✗ | ✓ | ✗ | ✗ |
---
## 10. Implementation Roadmap
### Phase 1: Database & Backend (Priority: HIGH)
1. [ ] Add new roles to database (`warehouse_manager`, `warehouse_worker`)
2. [ ] Create `worker_manager_bindings` table
3. [ ] Update `access_control.py` with new ROLES and MODULE_PERMISSIONS
4. [ ] Create access check decorators
5. [ ] Add data filtering logic in warehouse routes
### Phase 2: User Interface (Priority: MEDIUM)
1. [ ] Update `user_form.html` with warehouse role options
2. [ ] Create warehouse worker assignment page
3. [ ] Add worker-manager binding UI
### Phase 3: Warehouse Route Protection (Priority: HIGH)
1. [ ] Add decorators to report routes
2. [ ] Add decorators to input routes
3. [ ] Implement data filtering for workers
### Phase 4: Testing & Validation (Priority: HIGH)
1. [ ] Test manager access to all pages
2. [ ] Test worker access (should block reports)
3. [ ] Test superadmin/admin override
4. [ ] Test worker-manager binding enforcement
---
## 11. Security Considerations
### 11.1 Data Isolation
- Workers MUST NOT see other workers' data
- Workers MUST NOT see any warehouse reports
- Managers see ONLY their assigned workers' data
- Database queries MUST include proper WHERE clauses
### 11.2 Role Verification
- Always verify role in routes, not just in UI
- Use decorators on ALL sensitive routes
- Log access attempts to reports (audit trail)
### 11.3 Cross-Module Access
- Quality managers (role='manager') should NOT auto-get warehouse access
- Warehouse staff should NOT auto-get quality access
- Explicitly assign roles per module
---
## 12. Example SQL Queries
### 12.1 Create New Roles
```sql
INSERT INTO roles (name, description, level) VALUES
('warehouse_manager', 'Warehouse Manager - Full warehouse module access', 75),
('warehouse_worker', 'Warehouse Worker - Input-only warehouse access', 35);
```
### 12.2 Create Worker-Manager Binding Table
```sql
CREATE TABLE IF NOT EXISTS worker_manager_bindings (
id INT AUTO_INCREMENT PRIMARY KEY,
manager_id INT NOT NULL,
worker_id INT NOT NULL,
warehouse_zone VARCHAR(100),
is_active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY unique_binding (manager_id, worker_id),
FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (worker_id) REFERENCES users(id) ON DELETE CASCADE,
CHECK (manager_id != worker_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```
### 12.3 Assign Worker to Manager
```sql
INSERT INTO worker_manager_bindings (manager_id, worker_id, warehouse_zone)
VALUES (5, 12, NULL); -- Manager ID 5 oversees Worker ID 12, all zones
```
### 12.4 Get All Workers for a Manager
```sql
SELECT u.id, u.username, u.full_name, wmb.warehouse_zone
FROM worker_manager_bindings wmb
JOIN users u ON wmb.worker_id = u.id
WHERE wmb.manager_id = ? AND wmb.is_active = 1
ORDER BY u.full_name;
```
---
## 13. Summary Table
| Feature | Current | Proposed |
|---------|---------|----------|
| Warehouse roles | None | 2 new roles |
| Role levels | 4 (super, admin, manager, worker) | 6 (+ warehouse_manager, warehouse_worker) |
| Access control granularity | By role | By role + worker-manager binding |
| Worker data isolation | N/A | Full isolation by worker ID |
| Report access | By role | Managers only |
| Zone restrictions | None | Optional per binding |
| Database tables | 0 warehouse-specific | 1 new table (worker_manager_bindings) |
---
## 14. Benefits of This Model
✅ **Clear Role Hierarchy**: Seven distinct roles with clear separation of concerns
✅ **Worker Data Privacy**: Workers only see their own entries, no cross-worker visibility
✅ **Manager Oversight**: Managers see all their workers' data for supervision
✅ **Scalability**: Supports many workers per manager without access conflicts
✅ **Compliance**: Easily auditable access patterns for compliance/security
✅ **Flexibility**: Zone restrictions enable specialized warehouse areas
✅ **Backward Compatibility**: Existing quality roles remain unchanged
---
## Next Steps
Would you like me to implement:
1. Database schema changes?
2. Updates to `access_control.py`?
3. Warehouse route protections?
4. User interface updates?
5. All of the above?
Please confirm and I'll proceed with implementation.
Reference in New Issue View Git Blame Copy Permalink