Phase 1 wave inventory generator for Workbook_Redesign_2026. Given 3–4 legacy Compstat workbooks, produce Docs/wave_<letter>_inventory.md with sheet list, last-6-month schemas, _mom + aux tables, validation gaps, macro audit, flat-schema mapping.
Phase 1 recipe. Produces the per-wave Markdown inventory that downstream skills (/standardize-compstat-wb, etc.) read. Enforces scope discipline — always last 6 monthly sheets + _mom + aux tables, never more. This is what keeps a Cowork session from exploding the context window.
01_Legacy_Copies/)If any required input is missing, stop and ask — do not guess workbook paths or wave letter. If Docs/wave_<letter>_inventory.md already exists, offer update vs regenerate before re-reading binaries.
These rules come from the Workbook_Redesign_2026 project CLAUDE.md (Redesign-Specific Context), not from ai_enhancement/CLAUDE.md or SCRPA CLAUDE.md.
01_Legacy_Copies/_mom + aux tables. Do not expand scope.pd.read_excel(..., sheet_name=None) or openpyxl read-only.Derive the 6-month window deterministically:
datetime.date.today().25_NOV through 26_APR.chief_monthly uses 25_NOV, 26_01, …csb_monthly uses _23_06, 25_11, 26_01, …patrol_monthly uses day-of-month columns inside month sheetsYY_MM or YY_MMM), then keep only the last 6 matches sorted chronologically.For each workbook in the wave:
from openpyxl import load_workbook
wb = load_workbook(path, read_only=True, data_only=True)
Also pd.read_excel(path, sheet_name=None, dtype=str) for a first-pass schema.
List every sheet, flag which are:
YY_MM/YY_MMM pattern) — keep last 6 only per scope rule._mom / MoM / MonthOverMonth variants — include one (the active one).Categories, Raw_Input, AI_Context_Reference, named lookup tables) — include up to 4.<Unit>_Annual_Summary_Table) — note but don't deep-inspect.For each of the last 6 monthly sheets:
'01' in some months, '1' in others)_mom schemaMM-YY strings).pReportMonth mandate.For each aux table:
Check and report:
Event field: FK'd to a Categories table, or unbound?Date columns: format-locked (mm/dd/yyyy)?02_Legacy_M_Code/<unit>/*.m — note which queries exist..xlsm)For each metric currently tracked in this workbook, propose the mapping to canonical:
| Legacy Location | Proposed MetricGroup | Proposed Metric | Notes |
|---|---|---|---|
25_NOV!A5:AF5 (Traffic Stops row) | "Patrol Activity" | "Traffic Stops" | day-of-month columns → melt to Date |
_mom!B2:AR2 (rolling total) | — | — | Drop — derivable from flat source |
If the workbook is one of {Patrol, CSB, Community Engagement, STACP}, call out explicitly that it must land on the unified Community Outreach schema (see CLAUDE.md Redesign-Specific Context). Compare its current metrics to the canonical unified set.
If the workbook is patrol_monthly.xlsm, surface a REMOVED section noting that Summons is not carried into the redesign (DAX-joined from summons_slim_for_powerbi.csv instead). List the Summons-related sheets/tables as "out of scope for redesign."
Single Markdown file: Docs/wave_<letter>_inventory.md
Structure:
// 🕒 <ISO timestamp>
// # Workbook_Redesign_2026/Docs/wave_<letter>_inventory.md
// # Author: R. A. Carucci
// # Purpose: Phase 1 inventory of Wave <letter> workbooks (<list>) — columns, dtypes, validation gaps, macro audit, proposed flat-schema mapping.
# Wave <letter> Inventory — HPD Compstat Workbook Redesign
**Scope:** <workbook list>
**Date cutoff:** <today>. Active 6-month window = <YY_MMM> → <YY_MMM>.
---
## 1. File-by-File Breakdown
### 1.1 <workbook_filename>
(sections 2–11 per workbook)
### 1.2 <next workbook>
...
---
## 2. Cross-Workbook Observations
- Shared metric naming drift (e.g., "Traffic Stops" vs "Traffic Stop")
- Community Outreach cluster reconciliation (if this wave touches the cluster)
- Naming convention violations
- Macros that exist in multiple workbooks with similar purpose
---
## 3. Proposed Redesign Order
Rank the wave's workbooks by redesign complexity (simplest first). Flag any blockers (missing data, corrupt sheets, unresolved external connections).
---
## 4. Checklist for Phase 2
- [ ] Produce canonical metric catalog for each workbook
- [ ] Resolve naming drift across Community Outreach cluster (if applicable)
- [ ] Capture validation-rule catalog
- [ ] Decide macro dispositions (abandon / PQ / DAX / out-of-scope)
_mom 44-column sheet — just schema + header row.Docs/wave_<letter>_inventory.md first — offer to update vs. regenerate).Workbook paths, 01_Legacy_Copies/, 02_Legacy_M_Code/, and Docs/wave_<letter>_inventory.md are relative to the Workbook_Redesign_2026 repository root (sibling to other OneDrive projects such as 00_dev), not to ai_enhancement. Resolve paths using the active project root ($CLAUDE_PROJECT_DIR) for that repo.
If Workbook_Redesign_2026 is not the open project, ask the user for the repo or workbook paths before inventorying.
/preflight-export (for Data_Ingest exports, not WBs)/standardize-compstat-wb (per-WB redesign; reads this inventory)/apply-s2-s3-s4 (applied during Phase 2 per metric)