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

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?

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?

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

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

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

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

3.3 KiB Raw Permalink Blame History