60 lines
3.3 KiB
Markdown
60 lines
3.3 KiB
Markdown
# PLAN - Dynamic and Globally Unique Project Codes
|
|
|
|
We are addressing a database integrity constraint violation where duplicate project codes (e.g., `PRJ-2026-002`) are generated when multiple contractors (tenants) create projects. This happens because the project code generator queries the highest project code using a query scoped by `TenantScope`, making other tenants' project codes invisible to it.
|
|
|
|
## Project Type
|
|
WEB / BACKEND (Laravel / MySQL)
|
|
|
|
## User Review Required
|
|
|
|
> [!IMPORTANT]
|
|
> The unique constraint on the `projects.code` column is global, whereas the model queries are filtered by `TenantScope`. The proposed fix will bypass `TenantScope` specifically for finding the next sequential project code.
|
|
|
|
## Open Questions
|
|
|
|
> [!WARNING]
|
|
> 1. **Code Format Configuration**: Currently, project codes are generated using the hardcoded pattern `PRJ-{year}-{sequential_number}` (e.g., `PRJ-2026-001`). Should we continue with this pattern, or do you want to support tenant-specific custom prefixes or formats in the future?
|
|
> 2. **Yearly Reset**: The current logic resets the sequence number to `001` at the start of each year (i.e. `PRJ-{year}-001`). Should we preserve this yearly reset behavior, or should the counter increment continuously?
|
|
> 3. **Other Code Scopes**: Are there other entities in the system (like Warehouses, Bid Packages, or Daily Reports) where code uniqueness is enforced globally but generation is scoped to the tenant, potentially causing similar issues?
|
|
|
|
## Proposed Changes
|
|
|
|
---
|
|
|
|
### User Management Module
|
|
|
|
#### [MODIFY] [2026_05_23_102200_add_contractor_to_user_type_enum_in_users_table.php](file:///c:/laragon/www/gsb-cons/Modules/UserManagement/database/migrations/2026_05_23_102200_add_contractor_to_user_type_enum_in_users_table.php)
|
|
- Wrap raw MySQL statement in a check for SQLite `DB::getDriverName() !== 'sqlite'` to ensure local/CI tests do not fail on SQLite syntax.
|
|
|
|
---
|
|
|
|
### Project Management Module
|
|
|
|
#### [MODIFY] [Project.php](file:///c:/laragon/www/gsb-cons/Modules/ProjectManagement/app/Models/Project.php)
|
|
- In the `booted` method, modify the code generation inside the `static::creating` callback:
|
|
Change `static::withTrashed()` to `static::withoutGlobalScopes()->withTrashed()` when querying the last code.
|
|
|
|
#### [MODIFY] [TenantScopeTest.php](file:///c:/laragon/www/gsb-cons/tests/Feature/TenantScopeTest.php)
|
|
- Add a new test case `test_project_code_generation_is_globally_unique_across_tenants()` that creates projects for different contractors without specifying a code, and asserts that they auto-increment correctly and do not crash due to uniqueness violations.
|
|
|
|
---
|
|
|
|
## Verification Plan
|
|
|
|
### Automated Tests
|
|
- Run `TenantScopeTest` using the explicit PHP path:
|
|
`C:\laragon\bin\php\php-8.2.30-Win32-vs16-x64\php.exe artisan test --filter TenantScopeTest`
|
|
- Ensure all 6 tests (5 existing + 1 new) pass successfully.
|
|
|
|
### Manual Verification
|
|
- We can run seeders or simulate multi-tenant creation to verify that no duplicate codes are generated.
|
|
|
|
---
|
|
|
|
## Phase X: Final Verification
|
|
|
|
- [ ] Lint & Type Check: `npm run lint` or PHP equivalent check
|
|
- [ ] Security Scan: `python .agent/skills/vulnerability-scanner/scripts/security_scan.py .`
|
|
- [ ] Build Verification: Tests passing
|
|
- [ ] Runtime Verification: Local creation works without duplicate key crashes
|