# PLAN AUDIT: EHS Operations Module — TMED-II Phase III

**Plan file:** `~/.claude/plans/zazzy-sparking-moonbeam.md`
**Audit date:** 2026-05-19
**Auditor:** Claude (project-auditor skill, software/manufacturing hybrid)
**Evidence level:** inspected (source code + templates parsed, no runtime)

---

## Rev 1 VERDICT: CAUTION (7 findings: 1 CRITICAL, 3 MAJOR, 2 MODERATE, 1 LOW)

~~The plan is architecturally sound and correctly uses the existing ERP patterns (TenantModel, role mixins, sidebar nav). However, it has a critical template mapping error and several gaps that would cause failures in Phase 1 execution.~~

## Rev 2 VERDICT: SOUND (all 7 prior findings resolved, 4 new LOW findings)

**Re-audit date:** 2026-05-19 | All 7 fixes correctly applied. Plan is implementation-ready.

### Rev 2 Prior Findings Disposition

| ID | Prior Sev | Fixed | Evidence in Plan |
|----|-----------|-------|------------------|
| F-01 | CRIT | YES | Lines 99-108: 4-row blocks, no RFC, dynamic row discovery |
| F-02 | MAJ | YES | Line 182: openpyxl pinned; Step 4b: format verification |
| F-03 | MAJ | YES | Lines 25-27: templates in app; Lines 66-69: Path(__file__) |
| F-04 | MAJ | YES | Line 51: BorbollaWorker standalone; Line 52: borbolla_worker FK |
| F-05 | MOD | YES | Lines 114-124: 7 days, column B, Sunday handling |
| F-06 | MOD | YES | Lines 129-136: Google API client, token.json |
| F-07 | LOW | YES | Lines 87-93: partial roster notes |

### Rev 2 New Findings (all LOW)

| ID | Finding | Fix |
|----|---------|-----|
| F-08 | Context line 5 says "IMSS/CURP/RFC" but template has no RFC | Change to "IMSS/CURP" |
| F-09 | Google API packages not version-pinned | Pin to exact versions in requirements.txt |
| F-10 | Cron uses `/opt/django-ui/app` — verify matches production path | Check during deployment |
| F-11 | Missing EHSActivity days not handled — generator should fill dates regardless | Add fallback logic note |

---

## Findings

### F-01 | CRITICAL | Worker List template cell mapping is WRONG

**Plan says (Step 7):**
> "Fills detailed cards (rows 13+): NAME, IMSS, CURP, RFC per worker"

**Actual template structure** (parsed from `List of workers - example.xlsx`):

```
Row 12: Header — A12=NO, B12=WORKER INFORMATION, D12=ID, E12=PHOTO
Worker 1 block (rows 13-15):
  A13=1, B13=NAME
  B14=IMSS
  B15=ID          <-- NOT "CURP"
Worker 2 block (rows 17-19):
  A17=2, B17=NAME
  B18=IMSS
  B19=CURP        <-- Different label than worker 1!
```

Issues:
1. **No RFC field in the template at all.** Plan says "CURP, RFC" but the template has "ID" for worker 1 and "CURP" for worker 2 — inconsistent labels, and RFC is absent.
2. **Worker blocks are 4 rows apart** (13-16, 17-20), not 3. Row 16 is a blank gap row.
3. **PHOTO column (E12)** requires image embedding, not just cell text. `openpyxl` can do this via `ws.add_image()` but it's significantly more complex than filling cells. Plan's Phase 1 doesn't address this — deferred to Phase 3, but **HTS may reject a worker list without photos**.

**Fix:**
- Correct the cell mapping: each worker block = 4 rows (Name, IMSS, CURP/ID, gap). Start at row 13, increment by 4.
- Remove RFC from the generator spec — template doesn't have it.
- Decide NOW whether photos are required for first submission (May 26). If yes, pull Phase 3 photo embedding into Phase 1.

---

### F-02 | MAJOR | openpyxl is NOT installed — and plan references wrong version

**Plan says (Files to Modify):**
> "Pin `openpyxl==3.1.5`"

**Reality:**
- `openpyxl` is NOT in `/home/borbolla/django-ui-local/requirements.txt`
- It IS installed system-wide (the parse commands above worked with `python3`)
- But the Django production server at `root@100.117.59.75` uses a venv — openpyxl may not be there
- `xlsxwriter==3.2.9` IS in requirements (write-only, can't read templates)
- Latest openpyxl is 3.1.5 — this is correct, but verify it works with the merged cells and formatting in the HTS templates

**Fix:**
- Add `openpyxl==3.1.5` to requirements.txt (plan is correct here)
- After install, verify: `python -c "import openpyxl; wb = openpyxl.load_workbook('...xlsx'); print('OK')"` on the production server
- Test that merged cells, conditional formatting, and column widths survive the load/save cycle — HTS templates have merged headers

---

### F-03 | MAJOR | EHS_TEMPLATES_DIR points to dev path, not production

**Plan says:**
```python
EHS_TEMPLATES_DIR = '/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/formats'
```

**Reality:**
- This path exists on the dev machine (`/home/borbolla/...`)
- Production server is `root@100.117.59.75` with app at `/opt/django-ui/current`
- The clawd project tree does NOT exist on the production server
- The plan's cron command runs on production: `cd /opt/django-ui/app && ... python manage.py generate_ehs_report`

**Fix:**
- Copy Excel templates to `/opt/django-ui/shared/ehs_templates/` on production
- Change setting to: `EHS_TEMPLATES_DIR = os.environ.get('EHS_TEMPLATES_DIR', str(BASE_DIR / 'shared' / 'ehs_templates'))`
- Or bundle templates inside the app: `apps/operations/templates_excel/` and reference via `Path(__file__).parent / 'templates_excel'`
- The second option is better — templates travel with the code, no production path dependency

---

### F-04 | MAJOR | EmployeeEHSProfile links to Django User — but field workers aren't Django users

**Plan says:**
> `EmployeeEHSProfile`: user OneToOne [to Django User]

**Reality:**
- Borbolla field workers (Felipe Rodriguez, Samuel Cenicero, Carlos Medellin) exist in the HR system (`hr_database.db` via Telegram bot), NOT as Django auth Users
- Django Users in this ERP are admin/accounting staff who log into the web UI
- A field supervisor might not have a Django account at all
- The plan's seed data (Step 6) says "Create EmployeeEHSProfile for each with data from ERP" — but there is no `User` object for these people

**Fix options:**
1. **Create Django User accounts** for field workers with `is_active=True, is_staff=False`. They won't log in, but the FK is satisfied. Simple but semantically wrong.
2. **Decouple from User model.** Make `EmployeeEHSProfile` a standalone model with `first_name, last_name, employee_id` — similar structure to `SubcontractorWorker`. This is cleaner: both Borbolla employees and subcontractor workers are just "workers" for EHS purposes.
3. **Use a generic Worker model** that covers both types. The `ProjectWorker` junction table already has nullable FKs — this works, but the underlying models need to be self-contained without requiring Django User.

**Recommendation:** Option 2. Replace `EmployeeEHSProfile(user=OneToOne)` with `BorbollaWorker(first_name, last_name, ...)` using the same fields as `SubcontractorWorker`. Then `ProjectWorker` links to either model. This avoids creating phantom Django User accounts.

---

### F-05 | MODERATE | Weekly Plan template has 7 days (Mon-Sun), plan says Mon-Sat

**Plan says (Step 8):**
> "Fills daily rows (Mon-Sat): Date, Working Time, Manager..."

**Actual template (parsed):**
```
B10: 2024-04-01 (Monday)
B11: 2024-04-02 (Tuesday)
B12: 2024-04-03 (Wednesday)
B13: 2024-04-04 (Thursday)
B14: 2024-04-05 (Friday)
B15: 2024-04-06 (Saturday)
B16: 2024-04-07 (Sunday)    <-- 7th row exists
```

7 rows, not 6. Sunday is included. If the generator only fills Mon-Sat, row B16 will have a stale date from the template (Apr 7, 2024).

**Fix:** Generate 7 rows (Mon-Sun). Sunday can have "No work scheduled" or be left with zeroes for People Required, but the date must be updated to the correct week.

Also: date column is **B** (not A). Plan doesn't specify columns explicitly — the generator must use column B for dates, C for Working Time, D for Manager, etc.

---

### F-06 | MODERATE | Gmail MCP SSE is complex for a management command — simpler path exists

**Plan says (Step 9):**
> "Connects to Gmail MCP SSE at http://100.116.127.87:8080/sse"

**Reality from memory:**
> "Gmail direct API: Use Google API client lib with /home/borbolla/gmail/token.json (simpler than MCP SSE)"

The Gmail MCP requires SSE connection + JSON-RPC protocol. For a management command that sends one email with attachments, the Google API client library (`google-api-python-client`) with `token.json` is dramatically simpler:

```python
from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
# ... build message with attachments, service.users().messages().send()
```

This is already used in `/home/borbolla/clawd/projects/HTS/scripts/send_chang_as_thread_reply.py` — proven pattern.

**Fix:** Use Google API client library directly. Add `google-api-python-client==2.x` and `google-auth==2.x` to requirements.txt. Reference token at a production-accessible path.

---

### F-07 | LOW | Seed data references employees not yet confirmed

**Plan says (Step 6):**
> "Create ProjectWorker entries for known Borbolla employees: Felipe Rodriguez (supervisor), Samuel Cenicero, Carlos Medellin"

**Reality from project audit:**
- Samuel Cenicero: confirmed (rigging, 216K subcontract)
- Felipe Rodriguez: referenced as supervisor — likely confirmed
- Carlos Medellin: not referenced in any masterplan file. Unknown source.
- **Alejandro (utilities crew lead): STILL TBD** per audit finding FN-04
- Belean Industrial (Alex Aguilar): meeting done May 15 but no worker roster received

**Fix:** Seed data should be treated as placeholder. Real worker roster won't be complete until Alejandro confirms and Alex Aguilar provides Belean worker list with IMSS/CURP. Plan should note this dependency.

---

## Verified Claims

| Claim | Status | Evidence |
|---|---|---|
| TenantModel base class exists | VERIFIED | `/home/borbolla/django-ui-local/apps/tenants/models.py` line 48 |
| OperationsRequiredMixin exists | VERIFIED | `/home/borbolla/django-ui-local/apps/core/mixins.py` line 76 |
| HTMX + Bootstrap patterns in services app | VERIFIED | `apps/services/views.py` uses `OperationsRequiredMixin + ListView` |
| Sidebar nav uses `has_any_group` filter | VERIFIED | `templates/base.html` line 136 |
| `apps.accounting` is last in INSTALLED_APPS | VERIFIED | `config/settings.py` line 67 |
| Excel templates exist at specified path | VERIFIED | Both files present in `/project/formats/` |
| Worker List has summary + detailed sections | VERIFIED | Parsed: rows 6-10 summary, rows 12+ detailed cards |
| Weekly Plan has header (rows 1-8) + daily rows (9+) | VERIFIED | Parsed: B1-B8 headers, B9 column headers, B10-B16 daily |
| No `apps/operations/` directory exists | VERIFIED | `ls apps/` shows no operations dir |
| openpyxl not in requirements.txt | VERIFIED | `xlsxwriter` present, `openpyxl` absent |
| 'operations' role exists in ROLE_CHOICES | VERIFIED | `tenants/models.py` line 218 |

---

## Recommendations

1. **Fix F-01 FIRST** — the cell mapping error will produce broken Excel output. Parse both templates programmatically in the generator's `__init__` to discover row/column structure dynamically rather than hardcoding row numbers.

2. **Fix F-04 before models** — choosing between `User` OneToOne vs standalone `BorbollaWorker` model affects the entire schema. Decide now, not after migrations are created.

3. **Bundle templates in app** (F-03) — eliminates production path dependency and keeps templates versioned with code.

4. **Use Google API client** (F-06) — proven pattern, much simpler than MCP SSE for a background command.

5. **Photos question** (F-01) — ask Luis: does the first EHS submission need worker photos? If yes, Phase 1 scope expands significantly.

---

## Audit Metadata

| Field | Value |
|---|---|
| Files inspected | 8 (2 xlsx templates, settings.py, urls.py, requirements.txt, tenants/models.py, core/mixins.py, base.html) |
| Evidence level | inspected |
| Confidence | HIGH |
| Plan verdict | CAUTION — fixable issues, no fundamental architecture problem |
| Estimated fix effort | 1-2 hours to correct plan, then ready for implementation |