# Profile-scoped prospect conversion: audit and implementation plan

## Scope

This document audits the current Upwork flow where one job can be submitted through multiple Upwork profiles/accounts, and defines the changes required so each submitted profile can be converted into its own prospect. No application code was changed as part of this audit.

## Desired behavior

- A job may have multiple submitted proposal records, one per Upwork profile/account.
- Converting one submitted record creates one prospect linked to that exact submission/account.
- Other submitted records for the same job remain submitted and visible in Proposal Management.
- Each submission can produce at most one prospect. Repeating conversion for that submission is rejected/idempotently reported.
- Prospect lead assignment remains category-aware using the job category. A lead may own multiple prospects; uniqueness applies to the submission-to-prospect relationship, not to the lead.
- Job-level status can remain `prospect` once at least one profile is converted, while remaining submitted profiles are still independently actionable.

## Current flow and root cause

The existing data model already stores profile-specific submissions in `submit_proposals` (`job_post_id`, `upwork_account_id`, `job_proposal_id`, and submission status). `prospects` also stores both `submit_proposal_id` and `upwork_account_id`, and the create form sends the selected `submit_proposal_id`. `ProspectRequest` validates uniqueness by `submit_proposal_id`.

Conversion is nevertheless blocked at job scope in `ProspectService::create`: it rejects when any prospect exists for the job or when the job is already `prospect`/`client`. After converting one submission, it archives every other `SubmitProposal` and every other `JobProposal` on the job, then changes the job status to `prospect`. This is the direct reason the second profile disappears and cannot be converted.

The job-wide archive also erases meaningful state from the still-submitted profile's proposal records. Merely allowing duplicate job prospects without correcting these status transitions would leave the UI and reports inconsistent.

## Required implementation changes

### 1. Conversion invariants and transaction (`ProspectService`)

- Lock and validate the selected `SubmitProposal` and its owning job in one transaction; verify the submission's `job_post_id` matches request `job_post_id`, account/profile exists and is active/eligible, and status is `submitted`.
- Replace job-level duplicate rejection with submission-level duplicate protection. A prospect for another submission of the same job must not block conversion.
- Keep the one-prospect-per-submission invariant enforced in application validation and with a database unique constraint on `prospects.submit_proposal_id` (nullable legacy values require database-specific consideration).
- Convert only the selected submission and corresponding `JobProposal` to prospect/accepted state. Do not archive sibling submissions/proposals as a side effect.
- Set job status to `prospect` when any profile is converted; maintain it while at least one live prospect exists. On prospect deletion/rejection, recompute job state from remaining submitted submissions and prospects rather than assuming one prospect per job.
- Keep all updates, manager history, assignment counters, activity logs, and notifications transactionally consistent. Ensure external notifications/file storage are not duplicated on retry or emitted before commit.

### 2. Status meaning and read/query paths

- Audit `JobPost` status transitions, `JobPostService` eligibility/access checks, status tabs, counts, exports, reports, and dashboard queries. A job in `prospect` status must still expose submitted sibling `JobProposal`/`SubmitProposal` rows for further conversion, while job-level operations that should be blocked can remain blocked.
- The submitted list must be driven by each profile's submission/proposal status (and exclude only the exact converted submission), not by a job-level `prospect` status that hides the entire job.
- Revisit `JobActionMenu` and `JobsTable` logic that treats the job as converted if any proposal is prospect. Determine row/card granularity: display one row per submission/profile or a grouped job with per-profile action controls. Ensure the converted profile shows Prospect and remaining profiles retain Convert to Prospect.
- Confirm proposal detail resources include stable submission identifiers and account/profile metadata in all list and detail responses; the conversion modal must submit the selected submission ID, never infer from the first submission.

### 3. Prospect lookup and lifecycle semantics

- `ProspectRepository::findByJobPost()` currently returns one row and its interface is singular. Replace or supplement it with plural/job-scoped methods, and make all callers explicit about whether they need a prospect for a particular submission, any sibling prospect, or all siblings.
- Audit `ProspectService::delete`, `getSiblingProspects`, restore/reject/convert-to-client behavior, and all `where('job_post_id', ...)->first()` assumptions. These can silently affect only one of multiple profile-specific prospects.
- Define prospect identity in UI and exports as job + profile/account (and submission), so same client/job across two accounts remains distinguishable without duplicating the underlying job.
- Ensure duplicate protection accounts for soft-deleted prospects. Decide whether deleting a prospect frees its submission for reconversion; encode that policy consistently in validation and database uniqueness (partial unique index where supported, or restore/reuse strategy).

### 4. Category-aware lead assignment and authorization

- Continue deriving category from the job and filtering eligible prospect leads by that category in the conversion UI. On the server, validate that the selected lead is an active prospect lead authorized for the job category; frontend filtering alone is insufficient.
- Check multi-category users and category/subcategory mapping semantics, including users assigned to several categories and jobs with missing category.
- Existing lead assignment is per prospect (`prospect_manager_id`) with history per prospect (`prospect_lead_histories`). Preserve this so the same lead may receive several profile-specific prospects, while the same submission cannot create duplicate prospects or duplicate assignment history on retries.
- Verify list access control and lead stats count each prospect once, even where the same job appears for multiple profiles.

### 5. Database changes and data migration

- Add a migration adding a unique constraint/index for non-null `prospects.submit_proposal_id` after auditing and resolving any duplicate existing values.
- Do not add uniqueness on `prospects.job_post_id`; multiple profile-specific prospect rows for one job are now expected.
- If any existing index/constraint or production data enforces job uniqueness outside the initial migration, inspect deployed schema and remove/replace it in a forward-only migration.
- Backfill/repair existing prospects with missing `submit_proposal_id` or `upwork_account_id` from their linked job/proposal where unambiguous. Record unresolved rows for manual remediation; do not guess an account when multiple submissions exist.
- Add/confirm foreign keys for `prospects.submit_proposal_id`, `prospects.upwork_account_id`, and `submit_proposals` links with deliberate delete behavior; review soft-delete semantics before cascading.

### 6. Frontend behavior

- Update submitted job list/card/action state to show profile-level conversion status and actions even when a sibling profile already has a prospect.
- Ensure conversion form selects/shows the exact profile and submission; after success refresh the submitted row and prospect list without hiding remaining profile submissions.
- Display profile/account in Prospect Management list, detail, search/filter, and export so two prospects derived from one job are understandable.
- Handle already-converted submissions, stale tabs, duplicate requests, and concurrent conversions with clear server error messages and a refresh path.

### 7. API/resource/report audit

- Review `ProspectRequest`, `ProspectController`, `ProspectResource`, `ProposalController`, `JobPostResource`, and API routes for selected submission identity, category authorization, and correct status values.
- Review prospect reports, submitted-by statistics, performance reports, exports, notifications, activity logs, and lead assignment counters for assumptions of one prospect per job. Counters should increment once per created prospect and not on retries.
- Keep API errors consistent (422 for invalid/stale conversion; conflict semantics may be considered for concurrent duplicates) and return the created prospect with exact `submit_proposal` and profile/account relations.

## Suggested implementation order

1. Confirm product rules for prospect deletion/reconversion and how the job card should group multiple profiles.
2. Add/repair data integrity migration and inspect existing data before enabling multi-prospect conversion.
3. Refactor the transactional conversion service and job status recomputation.
4. Update backend requests, repositories, resources, list queries, access policy, reports, and exports.
5. Update submitted list/action UI and Prospect Management profile display.
6. Add unit and feature tests below, then run focused backend/frontend verification and the full relevant suite.

## Unit and feature test cases

### `ProspectService` unit/service tests

1. Converts a submitted profile into one prospect with matching `submit_proposal_id`, `upwork_account_id`, writer, template, and sales person.
2. A second submitted profile for the same job converts successfully after the first conversion.
3. Re-converting the same submission is rejected and does not duplicate history, counters, notifications, or activity logs.
4. Conversion preserves sibling `SubmitProposal` and `JobProposal` statuses; only the chosen submission/proposal changes.
5. A submission from another job, a non-submitted submission, or an invalid account is rejected without partial writes.
6. Two concurrent conversion attempts for the same submission result in exactly one prospect.
7. Two concurrent conversions for different submissions of the same job both succeed.
8. Job status remains prospect while any live prospect exists; deleting/rejecting one sibling does not reset it while another exists.
9. Deleting/rejecting the last prospect recomputes job status according to remaining submitted submissions and documented lifecycle policy.
10. Category-mismatched, inactive, or non-prospect-lead users cannot be assigned; eligible category lead can be assigned to multiple profile prospects.
11. Rollback on any failed later step leaves no orphan prospect, altered proposal statuses, leaked assignment counter, or history row.

### Request/repository/resource tests

1. Request validation rejects duplicate `submit_proposal_id` but accepts another submission on the same job.
2. Category and active-role checks are enforced server-side, including category/subcategory edge cases.
3. Prospect job lookup returns multiple sibling prospects or an exact submission match as requested; no method silently returns an arbitrary first row.
4. Resource includes exact account/profile and submission identifiers for prospect list/detail and preserves pagination.
5. Prospect access control and lead filters correctly return multiple prospects tied to the same job without duplicate rows.
6. Soft-deleted prospect behavior matches the chosen reconversion policy and database constraint.

### API/feature and regression tests

1. Submit the same job through two profiles, convert profile A, assert profile B remains in submitted results, then convert B and assert two prospects.
2. Confirm the first conversion does not mark the second profile as archived or remove its action button.
3. Prospect list/detail shows both records with distinct profile names and selected lead assignments.
4. Existing single-profile conversion, direct-message prospect creation, client conversion, deletion, rejection, and reporting flows continue to work.
5. Lead statistics, prospect exports, submitted counts, and performance metrics count at profile/submission granularity as specified.
6. Frontend test: selected profile's submission ID is sent and other profile rows remain after conversion.

## Audit caveats

- Current database constraints in deployed environments may differ from checked-in migrations; inspect actual schema before planning the unique index.
- Job-level status is shared across profiles, so every consumer of that field must be audited before allowing remaining profile submissions to stay actionable.
- This is an audit and implementation plan only. No existing application code or tests were modified or run.
