# Cursor Prompt — Archive Every EN Census Pull as a Document on the Group

**Goal:** Every time a broker pulls an EN census into Plansight, also persist a snapshot of the raw census payload (as JSON and as CSV) into the group's documents (S3) — so there's always an auditable record of "what EN said the census was on date X." Compliance-grade audit trail with effectively zero behavior change.

**Repo:** `Plansight APP` (Laravel) · package: `integrations/EmployeeNavigator/` and `App\Helpers\S3GroupFile`

**Effort:** Small. ~50 lines of code in one file plus tests. One commit.

---

## Background

When a user clicks "pull census from EN," `integrations/EmployeeNavigator/Controller.php` (around line 440) iterates the response, transforms employees + dependents into Plansight people rows, and feeds them into the quote/RFP. The raw EN response is then garbage-collected — there's no persistent record of what EN actually returned.

Brokers occasionally need to prove "this is what EN said the census was on Day X" when an enrollment dispute comes up. Today they'd have to re-pull and hope nothing changed in the meantime. With a snapshot, they always have the immutable file from the moment of the pull.

The infrastructure already exists: `App\Helpers\S3GroupFile` writes to the `s3_groupFiles` disk and creates a `GroupFile` DB record. Existing usage is per-broker file uploads; we'd add a programmatic call from the census pull path.

---

## What to add

After a successful EN census pull (in `integrations/EmployeeNavigator/Controller.php` around line 440, immediately after the response is parsed), write two files into the group's documents:

1. **`EN_Census_<YYYY-MM-DD>_<HHMM>.json`** — raw response body, exactly what EN returned, prettified for human readability
2. **`EN_Census_<YYYY-MM-DD>_<HHMM>.csv`** — flat employees + dependents table for at-a-glance review (one row per person, dependent rows linked to employee by `employeeIdentifier`). Columns at minimum: `employeeIdentifier`, `relationship`, `firstName`, `firstInitialLastName`, `birthDate`, `sex`, `homeAddress.postalCode`, `tobaccoUseInformation.useTobacco`, `employmentInformation.benefitHireDate`, `employmentInformation.jobTitle`, `employmentInformation.benefitWorkState`, `companyStructureMapping.eligibilityClass`, `companyStructureMapping.division`, `employmentCompensation.annualBenefitSalary.annualBenefitSalary`

Both files land in a per-group **`EN Census Snapshots`** folder (auto-created on first pull).

---

## Implementation sketch

### 1. Folder helper — auto-create the snapshots folder if missing

In `integrations/EmployeeNavigator/Controller.php` (or a small helper class), before the file write:

```php
private function getOrCreateSnapshotFolderId(string $brokerageId, string $groupId): string
{
    $existing = GroupFile::where('groupId', $groupId)
        ->where('type', 'folder')
        ->where('name', 'EN Census Snapshots')
        ->where('parent', 'root')
        ->first();
    if ($existing) {
        return $existing->id;
    }
    return S3GroupFile::createFolder($brokerageId, $groupId, 'EN Census Snapshots', 'root');
}
```

### 2. Snapshot writer — programmatic version of `S3GroupFile::createFile`

`S3GroupFile::createFile` expects an UploadedFile-shaped object (it calls `$fileObj->storeAs(...)`). For programmatically-generated content, mirror the existing `GroupFileCopyJob.php:108` pattern that uses `Storage::disk('s3_groupFiles')->putFileAs(...)` directly, then create the `GroupFile` row by hand:

```php
private function archiveCensusSnapshot(
    string $brokerageId,
    string $groupId,
    array $rawResponse
): void {
    $folderId = $this->getOrCreateSnapshotFolderId($brokerageId, $groupId);
    $stamp = Carbon::now()->format('Y-m-d_Hi');

    foreach ([
        ['ext' => 'json', 'mime' => 'application/json',
         'body' => json_encode($rawResponse, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES)],
        ['ext' => 'csv',  'mime' => 'text/csv',
         'body' => $this->renderCensusCsv($rawResponse)],
    ] as $artifact) {
        $fileId = UID::uid();
        $filePath = $brokerageId . '/' . $groupId;
        $fullPath = $filePath . '/' . $fileId;

        Storage::disk('s3_groupFiles')->put($fullPath, $artifact['body'], [
            'ContentType' => $artifact['mime'],
        ]);

        GroupFile::create([
            'id' => UID::uid(),
            'groupId' => $groupId,
            'brokerageId' => $brokerageId,
            'type' => 'file',
            'size' => strlen($artifact['body']),
            'mimeType' => $artifact['mime'],
            'name' => "EN_Census_{$stamp}.{$artifact['ext']}",
            'parent' => $folderId,
            'fileId' => $fileId,
            'filePath' => $filePath,
            'file' => $fullPath,
        ]);
    }
}
```

### 3. CSV renderer — flatten people for human review

```php
private function renderCensusCsv(array $rawResponse): string
{
    $rows = [];
    $headers = [
        'employeeIdentifier','relationship','firstName','firstInitialLastName',
        'birthDate','sex','postalCode','tobacco',
        'benefitHireDate','jobTitle','benefitWorkState',
        'eligibilityClass','division','department','businessUnit','officeLocation',
        'annualBenefitSalary','annualBenefitSalaryEffectiveDate',
    ];
    $rows[] = $headers;

    foreach (data_get($rawResponse, 'response.employees', []) as $emp) {
        $rows[] = $this->csvRowForPerson($emp, 'Employee');
        foreach (data_get($emp, 'dependents', []) as $dep) {
            $depRow = $this->csvRowForPerson($dep, data_get($dep, 'demographics.relationship', 'Dependent'));
            $depRow[0] = data_get($emp, 'employeeIdentifier');  // link dep to employee
            $rows[] = $depRow;
        }
    }

    $out = fopen('php://temp', 'r+');
    foreach ($rows as $r) fputcsv($out, $r);
    rewind($out);
    return stream_get_contents($out);
}
```

(Implementer's call on whether the CSV-cell mapping deserves its own `csvRowForPerson()` helper or stays inline.)

### 4. Wire it in

In the existing census-pull handler in `Controller.php` (around line 440, right after `$response->response->employees` is iterated successfully but before the function returns):

```php
try {
    $this->archiveCensusSnapshot(
        $group->brokerageId,
        $group->id,
        json_decode(json_encode($response), true)
    );
} catch (\Throwable $e) {
    // Snapshot failure must NOT break the census pull — log and continue.
    Log::warning('EN census snapshot failed: ' . $e->getMessage(), [
        'groupId' => $group->id,
    ]);
}
```

The `try/catch` is load-bearing: a snapshot write failure should never block the broker's census pull from completing.

---

## Tests to add

1. **Unit:** Given a fixture EN census response, `renderCensusCsv()` produces a CSV with the expected header row + one row per person (employees + dependents). Dependent rows have the parent employee's `employeeIdentifier` in column 0.
2. **Unit:** `getOrCreateSnapshotFolderId()` returns the same folder id when called twice for the same group.
3. **Integration (manual):** Pull a real census in a sandbox group; confirm two files appear in `EN Census Snapshots` folder named `EN_Census_<date>_<time>.json` and `.csv`. Open the JSON — should match what EN returned. Open the CSV in Excel — should be readable.
4. **Failure-mode:** Mock `Storage::disk('s3_groupFiles')->put()` to throw. The census pull itself should still succeed; only the warning log should appear.

---

## Non-goals

- **No retention policy.** Snapshots accumulate. If a group does 50 census pulls, they'll see 50 pairs of files. A retention/pruning policy can come later (delete snapshots older than 12 months, or keep only the last N per RFP).
- **No PII redaction.** Snapshots contain real employee data — same as the existing census flow. This is internal-only and behind broker auth.
- **No diff view.** "What changed since the last snapshot" would be a great future feature but is out of scope for this commit.
- **No retroactive backfill.** Past pulls aren't reconstructable; this only captures pulls from this commit forward.

---

## Rollback

Remove the `archiveCensusSnapshot()` call from `Controller.php`. Existing `GroupFile` rows + S3 objects remain (no destructive cleanup) — they'd just stop being added to. If the folder noise is a problem, manually delete via the existing GroupFile delete path.

---

## Cursor prompt — copy/paste

```
Implement the EN census snapshot archive described in
Plansight Dev/Apps/integrations.plansight.com/cursor-prompts/en-archive-census-snapshot.md.

Goal: every time the EN census-pull handler in
integrations/EmployeeNavigator/Controller.php (around line 440) successfully receives a
response from EN, also persist two snapshot files to the group's documents:
  - EN_Census_<YYYY-MM-DD>_<HHMM>.json (raw response, prettified)
  - EN_Census_<YYYY-MM-DD>_<HHMM>.csv (flat people table)
Both land in a per-group "EN Census Snapshots" folder, auto-created on first pull.

Use the existing App\Helpers\S3GroupFile::createFolder for the folder, but write the file
content programmatically (the existing createFile() expects an UploadedFile shape; we have
a generated string). Mirror the GroupFileCopyJob.php:108 pattern: Storage::disk('s3_groupFiles')
->put(path, body, ['ContentType' => mime]) plus a manual GroupFile::create() with the
same fields S3GroupFile::createFile populates (id, groupId, brokerageId, type='file', size,
mimeType, name, parent, fileId, filePath, file).

CSV columns: employeeIdentifier, relationship, firstName, firstInitialLastName, birthDate, sex,
postalCode, tobacco, benefitHireDate, jobTitle, benefitWorkState, eligibilityClass, division,
department, businessUnit, officeLocation, annualBenefitSalary, annualBenefitSalaryEffectiveDate.
Dependent rows use the parent employee's employeeIdentifier in column 0.

CRITICAL: wrap the snapshot call in try/catch. A snapshot failure must NOT break the broker's
census pull. Log warning and continue.

Add tests:
- Unit test for the CSV renderer using a fixture EN response
- Unit test confirming getOrCreateSnapshotFolderId is idempotent
- Failure-mode test: when Storage::put() throws, the outer census handler still succeeds

Do NOT touch the existing census-to-people transform logic. The snapshot is purely a
side-effect of the existing flow — same data path, additional persistence step at the end.

When done, summarize: which file(s) you changed, the exact line in Controller.php where the
archive call was inserted, and any deviations from the spec (e.g. if you added a helper
class instead of inlining the methods).
```

---

## After this ships

- Update `employee-navigator.html` audit section: move gap #11 to a "Recently shipped" block, or add a green-badged version noting the capability is now live.
- Optional follow-up: surface a "View census snapshots" button on the group's documents tab UI that filters to the snapshot folder.
- Optional follow-up: a retention command (`php artisan en:prune-census-snapshots --older-than=365d`) to keep storage costs predictable.