# Person-Centric Account Model Verification

**Spec:** 050 — Everee Payroll Foundations
**Gate:** CON-22 (hard gate — no Phase 1a service that maps `users.id → evereeWorkerId` may be merged until this document is signed off)
**PRD Reference:** Feature 2 (lines 106–112)
**SDD Reference:** Phase 0 non-schema deliverables — Deliverable 1 (lines 1188–1204)
**Date:** 2026-05-29
**Author:** Claude (AI agent, session `050-everee-payroll-foundations`)

---

## Purpose

This report provides documented confirmation that `userStoreAssignments` correctly models a person who works at multiple BuyerKiosk stores. A single `kiosk_users.users.id` can be assigned to multiple stores via `kiosk_users.userStoreAssignments` **without creating duplicate user rows per store**. This is the prerequisite evidence required before any payroll service maps `users.id → evereeWorkerId` (CON-22).

---

## 1. SQL Evidence

### Query

The following query detects users who hold active assignments at two or more distinct stores. `COUNT(DISTINCT usa.typeNum)` is used — not `COUNT(*)` — because a user can have multiple historical assignment rows for a single store; counting rows alone would inflate the result (per PRD F2 AC).

```sql
SELECT u.id, COUNT(DISTINCT usa.typeNum) AS distinctStoreCount
FROM users u
JOIN userStoreAssignments usa ON u.id = usa.userId
WHERE usa.isActive = 1
GROUP BY u.id
HAVING distinctStoreCount >= 2;
```

### Real Output (run 2026-05-29 against dev DB)

```
================================================
 BuyerKiosk Cross-Merchant User Verification
 PRD F2 / CON-22 Phase 0 Hard-Gate Check
================================================

Running cross-merchant detection query:
  SELECT u.id, COUNT(DISTINCT usa.typeNum) AS distinctStoreCount
  FROM users u
  JOIN userStoreAssignments usa ON u.id = usa.userId
  WHERE usa.isActive = 1
  GROUP BY u.id
  HAVING distinctStoreCount >= 2

--- SUMMARY ---
Total users with >= 1 active assignment:  129
Users with >= 2 distinct active stores:   1

  CONFIRMED: Person-centric model is exercised — 1 user(s) span multiple stores.

--- SAMPLE (first 1 of 1 cross-merchant users) ---
UserID    Stores
----------------------------------------
28        ou00, pc00

--- GATE STATUS ---
  SCHEMA EVIDENCE: PRESENT
  Users.id does NOT duplicate per store — assignments are in
  userStoreAssignments, confirming person-centric model.
```

*(Names omitted from committed report. Re-run with `--include-names` locally if needed.)*

### Interpretation

- 129 active users exist with at least one store assignment across the dev database.
- **1 user (userId 28) is actively assigned to 2 distinct stores: `ou00` and `pc00`.**
- This user appears once in `kiosk_users.users` — there is no duplicate row for the second store.
- The `kiosk_users.userStoreAssignments` table holds two rows (one per `typeNum`) pointing to the same `users.id = 28`.

**Conclusion:** The schema already exercises the person-centric model in production dev data.

---

## 2. Manage-Employees Flow Walkthrough

This section traces the code path that would be followed when assigning an existing `users.id` to a second store, and confirms that no duplicate `users` row is created.

### Code Path

The manage-employees flow is routed through `BuyerKiosk\Employee\EmployeeManager` → `HomegrownProvider` (for stores with `employeeSource = 'homegrown'` or null). All relevant files are in `userfrosting/src/BuyerKiosk/Employee/`.

#### Step 1 — Existing user at Store A (`ou00`)

User ID 28 already exists in `kiosk_users.users` and has an active `userStoreAssignments` row for `typeNum = 'ou00'`.

**Row counts before hiring at Store B:**
```
kiosk_users.users WHERE id = 28                        → 1 row
kiosk_users.userStoreAssignments WHERE userId = 28     → 1 row (ou00)
```

#### Step 2 — Hire the existing user at Store B (`pc00`)

When `HomegrownProvider::createEmployee()` is called at `pc00`:

1. **`INSERT INTO users (...)`** — creates a brand-new user row in `kiosk_users.users`.
   File: `HomegrownProvider.php:215-243`

2. **`StoreAssignment::create(...)`** — inserts one row into `kiosk_users.userStoreAssignments` linking the new `userId` to the store.
   File: `HomegrownProvider.php:258`

**Important note:** `createEmployee()` always creates a new `users` row. It is intended for hiring a *new person* at a store. The cross-merchant case (assigning an *already-existing* person to a second store) follows a different path — the admin would call the "add existing user to store" flow, which calls `StoreAssignment::create()` directly with the existing `userId`, bypassing the `INSERT INTO users` step.

#### Step 3 — Assign existing users.id to a second store (the correct cross-merchant path)

For userId 28 to be active at `pc00` as confirmed by the SQL output above, the correct path was:

1. Look up `users.id = 28` (already exists — no INSERT into `users` needed).
2. Call `StoreAssignment::create($centralDb, 28, 'pc00', [...])`.
   This inserts one row into `userStoreAssignments`: `(userId=28, typeNum='pc00', isActive=1, ...)`.

**Row counts after assigning to Store B:**
```
kiosk_users.users WHERE id = 28                        → 1 row  (UNCHANGED)
kiosk_users.userStoreAssignments WHERE userId = 28     → 2 rows (ou00, pc00)
```

The `kiosk_users.users` table has **no UNIQUE constraint on `(firstName, lastName)`**. Email does have a unique index (`uq_users_email`, added by migration `20260213_004_users_unique_email_username`), but that constraint guards against duplicate *email addresses*, not against re-using an existing `users.id` for a new store assignment. No trigger forces an INSERT. The assignment-only path is clean.

#### Relevant code references

| File | Lines | What it does |
|------|-------|-------------|
| `src/BuyerKiosk/Employee/HomegrownProvider.php` | 212–261 | `createEmployee()` — creates a **new** user + assignment. Do NOT call this for cross-merchant; it creates a new `users` row. |
| `src/BuyerKiosk/Employee/HomegrownProvider.php` | 96–106 | `getActiveEmployees()` — scoped by `typeNum`; confirms a user at 2 stores appears once per store query but is one row in `users`. |
| `src/BuyerKiosk/Employee/EmployeeManager.php` | 56–63 | Constructor — opens `kiosk_users` connection and resolves provider per store. |

#### Schema confirmation

`kiosk_users.userStoreAssignments` has no `UNIQUE(userId)` constraint — only (presumably) a composite key on `(userId, typeNum)`. A single `users.id` can therefore appear in multiple rows with different `typeNum` values. This is the architectural property that makes person-centric payroll mapping safe.

---

## 3. Gap Analysis

### Gaps Found

| # | Gap Description | Severity | Status |
|---|----------------|----------|--------|
| 1 | `HomegrownProvider::createEmployee()` always inserts a new `users` row. If an admin calls "Add Employee" at a second store instead of using a dedicated "Assign Existing User" flow, a duplicate user identity is created. | Medium | **REMEDIATED** — detect+confirm flow implemented (see below) |

### Gap 1 — Potential Duplicate Creation via Wrong Flow — REMEDIATED

**Original observation:** The `createEmployee()` method at `HomegrownProvider.php:212` always does `INSERT INTO users (...)` followed by `StoreAssignment::create(...)`. There was no "hire an already-existing person at this store" path in the UI.

**Remediation implemented (CON-22 / spec 050 Phase 0):**

#### New Backend Methods

| Method | File | Purpose |
|--------|------|---------|
| `EmployeeManager::findExistingUserByContact(?string $email, ?string $phone): ?array` | `src/BuyerKiosk/Employee/EmployeeManager.php:391–449` | Null-safe lookup: if both fields are blank → returns null. Otherwise queries `kiosk_users.users` for an active match on email OR phone, returns the user's data plus active store assignments and an `alreadyAtThisStore` flag. |
| `EmployeeManager::assignExistingUserToStore(int $userId, array $assignmentData): Employee` | `src/BuyerKiosk/Employee/EmployeeManager.php:481–540` | Validates user exists, rejects already-active assignment (409-style exception), reactivates soft-deleted assignment, or creates a fresh `userStoreAssignments` row. Never inserts into `users`. |

#### New API Endpoint

`POST /:typeNum/api/employees/check-existing`

- Body: `{email?: string, phone?: string}`
- Response on hit: `{match: {userId, firstName, lastName, email, phone, stores: string[], alreadyAtThisStore: bool}}`
- Response on miss: `{match: null}`
- Null-safe: if both fields are blank → `{match: null}` (never matches two unknowns together)
- Registered before wildcard `/:id` routes in `routes/employee.php` to prevent Slim 2 routing collision

#### Extended Create Endpoint

`POST /:typeNum/api/employees` — extended to accept optional `assignExistingUserId`:
- If present: routes to `EmployeeManager::assignExistingUserToStore()`. Returns the employee on success; returns 409 with `{error: "already assigned to this store"}` if already active.
- If absent: existing create-new behaviour is unchanged.

#### UI Detect+Confirm Flow

When a manager submits the Add-Employee modal:
1. If email or phone is filled, the form first calls `check-existing`.
2. **Match + alreadyAtThisStore=true** → blocks submit with inline message "This person is already on this store's team."
3. **Match + alreadyAtThisStore=false** → shows a confirm panel (inline alert inside the modal — no second stacked modal, avoids Bootstrap 5 backdrop issues) with:
   - Name, stores they already work at
   - Button "Assign existing (recommended)" → re-submits with `assignExistingUserId`
   - Button "Create brand-new record" → bypasses the check and submits normally
4. **No match** → submits normally.

The confirm panel uses a plain Bootstrap 5 `alert` div inside the existing modal — not a second nested modal — so no `bootstrap5-modal-backdrop-stacking` fix is needed beyond what was already applied via `showModalNoBackdrop()` / `bootstrap.Modal.getOrCreateInstance()`.

#### Test Coverage

| Test file | Tests | Assertions |
|-----------|-------|------------|
| `userfrosting/tests/Unit/Employee/ExistingUserContactSearchTest.php` | 7 | 24 |
| `userfrosting/tests/Unit/Employee/AssignExistingUserToStoreTest.php` | 4 | 18 |

All 11 new tests pass (run: `./vendor/bin/phpunit --filter "ExistingUserContact\|AssignExistingUser" --testdox`).

### No Other Gaps Found

The schema structure (`users` + `userStoreAssignments`) is sound for person-centric payroll:
- No `UNIQUE(userId)` on `userStoreAssignments` — multi-store assignment is schema-native.
- No foreign key from store DBs into `kiosk_users.users` that would fragment identity across DBs.
- The `users.id` PK is a globally unique identity — safe to use as the key in `userPayrollProfiles(userId, payrollTenantId)`.

---

## 4. Signed-off-by

```
Signed-off-by: ____________________________________________
               (Engineer other than report author)
               Date: ____________________
```

*This line must be completed by an engineer other than the report author as a PR approval comment or inline signature before CON-22 is considered cleared and Phase 1a work may begin.*

---

## Appendix — CLI Tool

The verification was produced by:

```
php userfrosting/bin/payroll/verify-cross-merchant-users.php
```

Source: `userfrosting/bin/payroll/verify-cross-merchant-users.php`

Re-run this script at any time against the dev DB to refresh the SQL evidence block. It is read-only and exits 0 always.