# Feature Specification: Customer Management — SP-04 Re-execution

**Feature Branch**: `005-customer-management-reexec`

**Created**: 2026-06-07

**Status**: Draft

**Input**: User description: "SP-04 Re-execution Guidelines — Customer Management. The previous implementation is treated as a faulty artifact. The current code in the repository must be evaluated against the constitution, BRD, algorithms, schema, and API manifests. If it violates any documented requirement, it must be deleted entirely and rewritten from scratch. No partial patches."

## User Scenarios & Testing *(mandatory)*

### User Story 1 - Customer Creation with Immediate Customer Flag (Priority: P1)

An admin records a new person as a customer by submitting their name, phone number, optional description, and optional avatar. The new client is persisted in the unified `clients` table and is immediately recognizable as a customer — the `"customer"` flag is added to the `client_type_flags` column during the same operation, so the client appears in the customer list from the moment of creation, even before any contract is signed.

**Why this priority**: Customer creation is the entry point for the entire customer domain. Without an explicit and immediate customer flag, the client would be invisible to the listing and downstream flows until a contract is created, which is prohibited by the constitution.

**Independent Test**: Can be fully tested by sending a POST request with valid data and verifying the response is 201, the new record exists in the `clients` table, and `client_type_flags` contains the value `"customer"` upon retrieval — without any further action.

**Acceptance Scenarios**:

1. **Given** valid name and unique phone, **When** admin submits POST `/api/v1/customers`, **Then** system creates a new record in `clients` with `client_type_flags` containing `"customer"` and returns 201 with the customer data
2. **Given** phone number already exists in `clients`, **When** admin submits POST, **Then** system returns 422 validation error indicating phone uniqueness violation
3. **Given** required fields are missing (name or phone), **When** admin submits POST, **Then** system returns 422 validation error with field-specific messages
4. **Given** a customer is created with no avatar, **Then** the `avatar` column remains NULL and no file is created
5. **Given** a customer is created with an avatar image, **Then** the file is uploaded to storage and the relative path is stored in the `avatar` column

---

### User Story 2 - Customer Listing from Materialized View (Priority: P1)

An admin opens the customer list to review all customers, with the goal of identifying who needs collection attention first. The list reads exclusively from the pre-computed `customer_listing_mv` materialized view, is ordered so that customers with the nearest pending installment appear at the top and customers with no remaining installments are pushed to the bottom, and supports search by name or phone, optional time filtering by the date of the customer's first contract, and cursor-based pagination.

**Why this priority**: The customer list is the primary working screen for the admin. Performance and sort order directly affect collection efficiency. Using a live JOIN+GROUP BY query on every request is prohibited because it does not scale; the materialized view provides sub-second response and keeps the sort consistent.

**Independent Test**: Can be fully tested by seeding customers with and without active installments, requesting the list with various filter combinations, and verifying the response is served from the MV, ordered correctly, and paginated using a cursor.

**Acceptance Scenarios**:

1. **Given** customers exist in the system, **When** admin requests GET `/api/v1/customers`, **Then** the list is read from `customer_listing_mv` and ordered by `next_due_date ASC NULLS LAST` (customers with no remaining installments appear last)
2. **Given** a search term, **When** admin requests the list with `?search=...`, **Then** only customers whose name or phone matches the term using case-insensitive partial matching (`ILIKE`) are returned
3. **Given** a date range, **When** admin requests the list with `?from_date=...&to_date=...`, **Then** the system filters by the customer's `first_contract_date` stored in the MV, with no additional live queries required
4. **Given** a paginated result set, **When** admin requests the next page using the returned cursor, **Then** the system uses cursor-based pagination (not offset) and returns a stable, deterministic next page
5. **Given** the default page size, **When** `per_page` is not provided, **Then** the system returns up to 20 records; when provided, the value is honored up to a maximum of 100
6. **Given** the MV was refreshed up to 5 minutes ago, **When** admin requests the list, **Then** the response is acceptable to be served from the slightly stale data (no real-time accuracy required at the list level)

---

### User Story 3 - Customer Details with Live Financial Summary (Priority: P1)

An admin drills into a specific customer to see their personal data alongside a real-time financial picture: total scheduled installments, total paid, total remaining, and counts of pending, overdue, and received installments. The personal data comes directly from the `clients` table; the financial summary is computed at request time from the live `installments` table joined to `contracts` (restricted to `active` or `completed` contracts) — not from the materialized view, which is only suitable for the list view.

**Why this priority**: The detail view is where the admin makes operational decisions. Stale financial data here would mislead collection efforts, so accuracy is essential even at the cost of a slightly heavier query.

**Independent Test**: Can be fully tested by paying off or marking installments as overdue, then re-requesting the customer details and confirming the financial summary reflects the latest state.

**Acceptance Scenarios**:

1. **Given** a customer with active or completed contracts, **When** admin requests GET `/api/v1/customers/{id}`, **Then** basic data is read from `clients` and the financial summary is computed live from `installments` joined to `contracts` where `contracts.status IN ('active', 'completed')`
2. **Given** a customer with no active or completed contracts, **When** admin requests details, **Then** the response still returns 200 with the basic data and a financial summary of zeros
3. **Given** a non-existent client id, **When** admin requests details, **Then** system returns 404 not found
4. **Given** the response is returned, **Then** it includes: `total_installment_amount`, `total_collected_amount`, `total_remaining_amount`, `total_pending_installments`, `total_overdue_installments`, `total_received_payments`

---

### User Story 4 - Customer Data Modification Without Immutability (Priority: P1)

An admin updates a customer's name, phone, description, or avatar to keep their record current. Updates succeed even if the customer already has active contracts — the customer entity is not immutable in the way contracts are. The `client_type_flags` column is never modified during a data update, even if the customer is also an agent or investor. When a new avatar is uploaded, the old file is removed from storage.

**Why this priority**: Customer records must be maintainable over time (e.g., phone number change, profile picture update). Blocking updates would create data quality issues across the system.

**Independent Test**: Can be fully tested by updating a customer who has active contracts, then verifying the basic data changed but `client_type_flags` remained untouched, and the old avatar file (if any) was deleted from storage.

**Acceptance Scenarios**:

1. **Given** an existing customer, **When** admin submits PUT with a new name, **Then** the name is updated and the response is 200 with the new data
2. **Given** a customer with active contracts, **When** admin submits PUT to change the phone, **Then** the update succeeds without error
3. **Given** a PUT with a new avatar file, **When** the customer already had an old avatar, **Then** the old file is removed from storage and the new path replaces it in the `avatar` column
4. **Given** a PUT, **Then** the `client_type_flags` column is not modified regardless of the new payload
5. **Given** a non-existent customer id, **When** admin submits PUT, **Then** system returns 404

---

### User Story 5 - Permission Seeding for Customer Domain (Priority: P1)

A one-time setup ensures the three customer-domain permissions (`customers.view`, `customers.create`, `customers.update`) exist in the database and are assigned to the existing Admin user, so the admin's day-to-day workflow continues uninterrupted the moment the feature goes live.

**Why this priority**: Without these permissions attached to the Admin, the admin loses access to the customer endpoints as soon as authorization is enforced. This blocks all other user stories at runtime.

**Independent Test**: Can be fully tested by running the seeding step and verifying the three permission records exist and the Admin user has all three via Spatie's `hasPermissionTo` check.

**Acceptance Scenarios**:

1. **Given** a fresh database, **When** the seeding step is executed, **Then** three permission records exist with the exact names `customers.view`, `customers.create`, `customers.update`
2. **Given** the Admin user exists, **When** the seeding step is executed, **Then** the Admin user has all three customer permissions
3. **Given** a user without the `customers.view` permission, **When** they call GET `/api/v1/customers`, **Then** the system returns 403 Forbidden
4. **Given** a user with `customers.view` only, **When** they call POST `/api/v1/customers`, **Then** the system returns 403 Forbidden

---

### Edge Cases

- **Customer with no contracts at all**: Appears in the customer list (because the `"customer"` flag was added at creation) with `next_due_date` = NULL and is pushed to the end of the sorted list.
- **Customer with only paid installments**: `next_due_date` is NULL because there are no pending or overdue installments; the customer is also pushed to the end of the list.
- **Phone number conflict on update**: Returns 422 validation error (uniqueness across `clients`).
- **Avatar update with no prior avatar**: Old file cleanup is skipped; the new file is uploaded and stored.
- **Customer listing with no filters and no search**: Returns all customers, ordered by `next_due_date` ASC NULLS LAST, paginated.
- **Invalid cursor**: The system handles the cursor transparently; if pagination is exhausted, an empty page is returned.
- **Time filter with `from_date` only**: Filters customers whose `first_contract_date >= from_date`; `to_date` filter is not applied.
- **Time filter with `to_date` only**: Filters customers whose `first_contract_date <= to_date`; `from_date` filter is not applied.
- **Time filter exceeding reasonable range**: Both parameters are optional and the list endpoint does not impose a 365-day cap (that limit applies to analytics only); however, `to_date` should not be in the future to remain meaningful.
- **Direct deletion attempt on a Client model**: Even if some code path attempts to delete a client, the model-level protection throws `ClientImmutableException` with an Arabic message and the request is rejected.

## Requirements *(mandatory)*

### Functional Requirements

**Customer Lifecycle (Read & Write)**

- **FR-001**: System MUST allow admins to create a new customer via POST `/api/v1/customers` requiring `name` and `phone` (Syrian local format: 10 digits with an optional leading `0`, e.g., `09XXXXXXXX`), with optional `description` (text, nullable, max:1000 characters) and `avatar` file upload.
- **FR-002**: System MUST create the customer record in the unified `clients` table (no separate customers table), and MUST add the value `"customer"` to the `client_type_flags` JSONB column in the same database operation, even when the customer has no contracts.
- **FR-003**: System MUST allow admins to update customer data via PUT `/api/v1/customers/{id}` for `name`, `phone` (when provided, MUST follow the same Syrian local format as in FR-001), `description` (text, nullable, max:1000 characters), and `avatar`; the `id` refers to the `client_id` in the `clients` table.
- **FR-004**: System MUST permit updates even when the customer has active or completed contracts; the customer entity is not subject to the contract immutability rule.
- **FR-005**: System MUST NOT modify `client_type_flags` during a data update; flag changes occur only via dedicated domain events (contract creation, shares add, etc.).
- **FR-006**: System MUST delete the previous avatar file from storage when a new avatar replaces it. Avatars MUST be image files (jpg, jpeg, png, webp) only, MUST NOT exceed 5 MB, and MUST be stored on the public disk under a dedicated `avatars/` directory using a generated filename; only the relative path is persisted in the `avatar` column.
- **FR-007**: System MUST NOT expose any delete endpoint for customers. If any delete-related code, route, or controller action exists, it MUST be removed.

**Customer Listing**

- **FR-008**: System MUST expose GET `/api/v1/customers` that reads exclusively from the `customer_listing_mv` materialized view; running a live JOIN+GROUP BY query is prohibited.
- **FR-009**: System MUST order the list by `next_due_date ASC NULLS LAST` so customers with no remaining installments appear last, and MUST use a secondary deterministic order (e.g., `client_id`) for stable pagination.
- **FR-010**: System MUST support `search` query parameter that matches name or phone using case-insensitive partial matching (`ILIKE`).
- **FR-011**: System MUST support optional `from_date` and `to_date` query parameters that filter by `first_contract_date` stored in the MV (`>=` and `<=` respectively).
- **FR-012**: System MUST use cursor-based pagination (not offset), with default `per_page` of 20 and maximum of 100, and MUST include `total_remaining_amount` per record.

**Customer Details**

- **FR-013**: System MUST expose GET `/api/v1/customers/{id}` that reads basic data directly from the `clients` table.
- **FR-014**: System MUST compute the financial summary at request time via a live query on `installments` joined to `contracts` where `contracts.status IN ('active', 'completed')`; the materialized view MUST NOT be used for the detail financial summary.
- **FR-015**: System MUST return, in the financial summary: `total_installment_amount`, `total_collected_amount` (status = paid), `total_remaining_amount` (status IN pending/overdue), `total_pending_installments`, `total_overdue_installments`, `total_received_payments` (count of paid).

**Authorization & Permissions**

- **FR-016**: System MUST register three permissions: `customers.view`, `customers.create`, `customers.update`.
- **FR-017**: System MUST assign all three customer permissions to the existing Admin user so the admin's workflow continues uninterrupted.
- **FR-018**: System MUST enforce `auth:sanctum` middleware and the corresponding `permissions:customers.<action>` middleware on every customer route.

**Architecture & Code Organization**

- **FR-019**: System MUST use a Service layer for all business logic; controllers MUST be thin orchestration only; models MUST NOT contain business logic.
- **FR-020**: System MUST validate every request via a dedicated FormRequest class; no inline validation in controllers.
- **FR-021**: System MUST produce all API responses via the helpers in `ApiResponseHelper`; controllers and services MUST NOT return raw `JsonResource` instances, custom response arrays, or inline `response()->json()` calls.
- **FR-022**: System MUST update `client_type_flags` using `save()` so model events fire; `saveQuietly()` is prohibited for this column.
- **FR-023**: System MUST locate customer routes in `app/Domains/Customer/Routes/v1/api.php` and load them via `CustomerServiceProvider` registered in `bootstrap/providers.php`; `routes/api.php` MUST remain empty.
- **FR-024**: System MUST use Model Scopes (or similar reusable query builders) for search and filtering; controllers MUST NOT execute raw queries.

**Model-Level Protection**

- **FR-025**: The Client model MUST override `delete()`, `destroy()`, and `boot()` to throw `ClientImmutableException`; no code path may physically or soft-delete a client.

**Exceptions**

- **FR-026**: System MUST define `ClientImmutableException` in `app/Exceptions/Business/`, register it in `ExceptionMappings.php`, and use Arabic messages.

**Routing**

- **FR-027**: The global `routes/api.php` file MUST contain only a single guidance comment pointing to Domain Service Providers. No route definitions are permitted in this file. All customer routes MUST reside in `app/Domains/Customer/Routes/v1/api.php`.

### Key Entities *(include if feature involves data)*

- **Client (Unified Human Entity)**: A single human being tracked in the system, stored in the `clients` table. The same table holds customers, agents, and investors, distinguished only by entries in the JSONB `client_type_flags` column. Attributes used by this feature: `id`, `name`, `phone`, `description`, `avatar`, `client_type_flags` (e.g., `["customer"]`), `created_at`, `updated_at`.
- **Contract**: An agreement between a customer and the company that generates installments. Identifies which contracts belong to a given customer and is filtered to `active` or `completed` for financial calculations.
- **Installment**: A scheduled payment obligation linked to a contract. Carries `due_date` and `status` (pending, paid, overdue) used for both the materialized view aggregations and the live financial summary.
- **Customer Listing Materialized View (`customer_listing_mv`)**: A pre-computed, read-only view that exposes per-customer aggregates needed by the list endpoint: `client_id`, `name`, `phone`, `avatar`, `first_contract_date`, `next_due_date`, `total_installment_amount`, `total_collected_amount`, `total_remaining_amount`, `total_pending_installments`, `total_overdue_installments`. Refreshed every 5 minutes.
- **Customer Permission**: A Spatie permission record named `customers.view`, `customers.create`, or `customers.update`, attached to the Admin role/user.

## Success Criteria *(mandatory)*

### Measurable Outcomes

- **SC-001**: An admin can create a new customer and receive a 201 response within 500 milliseconds; the new record exists in `clients` with `client_type_flags` containing `"customer"`, verifiable in the same database transaction.
- **SC-002**: The customer list endpoint returns the first page within 1 second for at least 10,000 customers, ordered by `next_due_date ASC NULLS LAST`, served from the materialized view.
- **SC-003**: Time filtering on the customer list works through the MV's `first_contract_date` field; no additional JOINs or subqueries are issued to satisfy the filter.
- **SC-004**: The customer details endpoint returns financial aggregates that match a direct SQL computation against `installments` and `contracts` for the same customer; verified by equality of values, not by code path.
- **SC-005**: Updating a customer who has one or more active or completed contracts succeeds and returns 200 with the updated record; `client_type_flags` is unchanged after the update.
- **SC-006**: No route, controller method, or service method that performs or invokes deletion of a customer exists in the codebase. Any attempt to delete a client via the model results in `ClientImmutableException` being thrown.
- **SC-007**: Every customer endpoint returns 403 Forbidden to an authenticated user who lacks the corresponding permission, and 200/201 to a user who has it.
- **SC-008**: After seeding, the existing Admin user can perform every customer operation (list, view, create, update) without any role reconfiguration step.
- **SC-009**: All responses — success and error — are produced exclusively via the helpers in `ApiResponseHelper` and follow the unified response format.
- **SC-010**: A repository-wide check for `saveQuietly()` on any code path that updates `client_type_flags` returns zero matches.

## Clarifications

### Session 2026-06-07

- Q: What phone number validation format should the customer FormRequest enforce? → A: Syrian local format — 10 digits with an optional leading `0` (e.g., `09XXXXXXXX`); enforced on both create and update when phone is provided.
- Q: What constraints should apply to the avatar upload? → A: Images only (jpg, jpeg, png, webp), max 5 MB, stored on the public disk under `avatars/` with a generated filename; only the relative path is persisted.
- Q: How should the admin retrieve the generated export file? → A: The export feature is removed from this spec entirely. User Story 6, the `customers.export` permission, the `POST /api/v1/customers/export` route, the export FormRequest/Job/Service, the `Export Job` entity, the export-related success criterion, and the `maatwebsite/excel` / `barryvdh/laravel-dompdf` assumption are all removed. Only `customers.view`, `customers.create`, and `customers.update` are in scope.

## Assumptions

- The unified `clients` table, the `client_type_flags` JSONB column, and the `customer_listing_mv` materialized view (including its required indexes) are already provisioned in the database from SP-02 / SP-03 and will not be redefined in this spec.
- Sanctum authentication, the Spatie permission package, and the `auth:sanctum` + `permissions:` middleware stack are already wired and available (SP-01 / SP-03).
- The Admin user is the only existing privileged user at the time of re-execution; assigning the three customer permissions to "the current Admin" means attaching them to whichever user record holds the admin role in the system. Role/UI management is out of scope and belongs to SP-01.
- The `ClientImmutableException` does not yet exist (it is a known omission from the previous execution) and will be created and registered as part of this work.
- The existing Customer domain code in the repository is considered untrusted for this re-execution: every file in the Customer domain will be evaluated against the requirements and either retained, deleted, or rewritten.
- Error messages produced for this domain are in Arabic, per the constitution.
- The 5-minute MV refresh window is acceptable; no real-time accuracy is required for the list endpoint.
- The 365-day analytics range cap does NOT apply to the customer list endpoint (it applies only to analytics, per the constitution).
- All monetary and date handling continues to follow the existing project standards (`bcmath`, `DECIMAL(10,2)`, `Asia/Damascus` timezone).