schedule-extractor

# Schedule Extractor Extracts tabular schedule data embedded in drawing sheets or spec pages and outputs structured Excel files. Schedules are typically one element among many on a sheet — or the entire sheet may be a schedule. ## First: the mode (both workflows) Before extracting or reconciling, read `.construction/project.yaml`. Present and readable means AgentCM mode: note its `contract_version` and follow `${CLAUDE_PLUGIN_ROOT}/reference/agentcm-mode.md`. Absent means flat-file mode. A listing that shows the file, or `.construction/database.yaml` alone, does not decide it: read the file. ## Workflow ``` Extraction Progress: - [ ] Step 1: Discover schedule locations - [ ] Step 2: Isolate (crop) the schedule region - [ ] Step 3: Extract structured data (pdfplumber + vision) - [ ] Step 4: Validate and clean data - [ ] Step 5: Output to Excel - [ ] Step 6: Ingest to AgentCM (AgentCM mode) - [ ] Step 7: Write a finding ``` ### Step 1: Discover Schedule Locations Use a multi-source discovery approach, checking all available sources: #### Source A — Sheet titles in sheet index (primary, most reliable) Search `.construction/index/sheet_index.yaml` (or the sheet index you've built) for sheets with "SCHEDULE" in the title. Many important schedules occupy an **entire sheet** — the sheet title tells you exactly what it is: - "DOOR SCHEDULE" → entire sheet is a door schedule - "FINISH SCHEDULE" or "ROOM FINISH SCHEDULE" → entire sheet is finishes - "WINDOW SCHEDULE" → entire sheet is windows - "PANEL SCHEDULE" → electrical panel schedule - "FIXTURE SCHEDULE" → plumbing fixtures For dedicated schedule sheets, the entire page is the extraction target — no need to crop. #### Source B — AgentCM's tables (AgentCM mode) In AgentCM mode (`.construction/project.yaml` exists; follow `${CLAUDE_PLUGIN_ROOT}/reference/agentcm-mode.md`), `.construction/INDEX.md`'s Schedules and legends table lists every table AgentCM found, each in its own `.construction/schedules/<sheet>-<n>.tsv`. The file's comment line gives the table's box on the sheet (x y w h, 0 to 1 from the top left) and, for a schedule already ingested, its `schedule_id` (`none` for a table read from the sheet's text alone). For embedded schedules on non-schedule sheets, that box is where to look: `sh .construction/crop.sh <sheet> <x> <y> <w> <h>`. **Note**: A table read from the text is AgentCM's reading, and its box an estimate: treat both as leads, not definitive boundaries. Always verify with vision. #### Source C — Discipline-based heuristics Schedules appear on specific sheet types: - **Door schedule** → typically on A-0.XX or A-8.XX sheets, or a dedicated sheet - **Window schedule** → same sheets as door schedule, or separate - **Room finish schedule** → A-0.XX or interior sheets - **Panel schedule** → E-X.XX electrical sheets - **Fixture schedule** → P-X.XX plumbing sheets - **Equipment schedule** → M-X.XX mechanical sheets #### Source D — Vision scan (fallback) If no index or graph is available: ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/rasterize_page.py" "{pdf_path}" {page} --dpi 150 --output ".construction/skills/schedule-extractor/pages/full_sheet.png" ``` Use vision on the full sheet image: "Identify any tabular schedules on this drawing sheet. Report the approximate bounding box (top-left x,y and bottom-right x,y) as fractions of the image width and height between 0 and 1, the schedule type, and the column headers visible." ### Step 2: Isolate the Schedule Region **For dedicated schedule sheets** (entire page is a schedule): Skip cropping — use the full page. **For embedded schedules** (schedule is one element on a larger sheet): Crop the identified region with padding. The box is the 0–1 fractions from Step 1, so `--normalized` is required; without it the script reads the values as pixels and returns an empty crop: ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/crop_region.py" ".construction/skills/schedule-extractor/pages/full_sheet.png" \ --box {x1},{y1},{x2},{y2} --normalized \ --padding 20 \ --output ".construction/skills/schedule-extractor/pages/schedule_crop.png" ``` Re-rasterize at higher DPI (300) for the cropped region to improve text clarity. `rasterize_page.py --crop` takes percentages, so multiply each fraction by 100 (`0.62,0.08,0.98,0.45` becomes `62,8,98,45`): ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/rasterize_page.py" "{pdf_path}" {page} \ --dpi 300 \ --crop {x1_pct},{y1_pct},{x2_pct},{y2_pct} \ --output ".construction/skills/schedule-extractor/pages/schedule_hires.png" ``` ### Step 3: Extract Structured Data #### In AgentCM mode — the table AgentCM read A table INDEX.md lists under Schedules and legends is already read. Turn its file into the exporter's data with the reader, never with a converter of your own: ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_SKILL_DIR}/scripts/agentcm_schedule.py" ".construction/schedules/<sheet>-<n>.tsv" \ --type door_schedule --data ".construction/skills/schedule-extractor/<sheet>-<n>.json" ``` It prints the header row it took and what it left out. A table read from the sheet's text has its title and header rows at the top: when it took the wrong row, give `--header-row <y>` (the line's position, as the file prints it) and `--header-rows <n>` for a header on several lines. Then verify the table against the drawing with `crop.sh` and the box on the file's comment line (Source B). Where the drawing shows a different value, correct that cell in the data JSON with the Edit tool, and record the correction as `${CLAUDE_PLUGIN_ROOT}/reference/agentcm-mode.md` says. Methods A to C below are for a table AgentCM did not find. Otherwise use a **try → validate → fallback** approach: #### Method A — pdfplumber table extraction (try first) Extract the page's tables with the shared script (its JSON holds `text` and `tables`; add `--bbox x1,y1,x2,y2` in PDF points to limit it to the schedule region): ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/extract_text_region.py" "{pdf_path}" {page} --output ".construction/skills/schedule-extractor/tables.json" ``` If multiple tables are found, select the largest one (most rows with the most columns — schedules are wide). The first row of the selected table contains headers; subsequent rows are data. If no tables are found or results look garbled, fall back to vision (Method B). Do not write pdfplumber code of your own. #### Evaluate Method A — Quality Gate **Check these criteria before proceeding:** 1. **Row count**: Did pdfplumber extract at least 10 data rows? Most schedules have 20-200+ entries. 2. **Column consistency**: Do ≥80% of rows have the same number of columns? Inconsistent columns = parsing error. 3. **Non-empty cells**: Are >50% of cells non-empty? Mostly-empty extraction = parsing failed. 4. **First column validity**: Does the first column contain recognizable IDs (door numbers, room numbers, equipment tags)? **Decision:** - All 4 checks pass → **Method A succeeded**, proceed to Step 4 - Any check fails → **Method A failed**, use Method B below ``` QUALITY GATE: extracted_rows >= 10? YES → check next | NO → use Method B column_consistency >= 80%? YES → check next | NO → use Method B non_empty_cells >= 50%? YES → check next | NO → use Method B first_col_has_valid_ids? YES → use Method A | NO → use Method B ``` #### Method B — Vision extraction (fallback) When pdfplumber fails (common with complex layouts, merged cells, non-standard table lines): 1. **Rasterize** the sheet at 150 DPI for overview: ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/rasterize_page.py" "{pdf_path}" {page} --dpi 150 --output ".construction/skills/schedule-extractor/pages/full_sheet.png" ``` 2. **Use vision** on the full sheet image to read the schedule directly: "This is a construction drawing sheet containing a schedule (tabular data). Extract ALL rows from the schedule as a JSON array of objects. The first row of the table contains column headers — use those as object keys. Each subsequent row is one entry. Preserve exact values including dimensions, abbreviations, and codes. If a cell is empty, use null." 3. For dense schedules with small text, **re-rasterize at 300 DPI** and crop to just the schedule region: ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/rasterize_page.py" "{pdf_path}" {page} --dpi 300 --output ".construction/skills/schedule-extractor/pages/schedule_hires.png" ``` #### Method C — Hybrid (for maximum accuracy) Run both methods and cross-validate: - pdfplumber gives exact text positioning and catches every character - Vision catches content pdfplumber misses (stamps, handwritten marks, non-standard table layouts) - Compare row counts: if they differ by >10%, investigate the discrepancy - Use vision results for rows that pdfplumber returned empty or garbled ### Step 4: Validate and Clean After extraction (by either method): - **Row count check**: Compare extracted count against what's visually present. If off by >10%, re-extract using the other method. - **Column structure**: Verify headers match expected schedule type (door schedule has MARK/SIZE/TYPE/FRAME/HARDWARE; finish schedule has RM.NO/NAME/FLOOR/BASE/WALLS/CLG) - **Merged cell cleanup**: Expand merged header cells (e.g., "WALLS" spanning A/B/C/D sub-columns) - **Normalize dimensions**: `3' - 0"` → `3'-0"` (remove spaces around dashes) - **Flag revisions**: Note any cells within revision clouds or delta markers - **Cross-reference with AgentCM's identifiers**: In AgentCM mode, check each room number and door mark against `.construction/tags.tsv` (`kind` room or door; the lines starting with the number and a tab). Not finding one there proves nothing (INDEX.md says when its room list is incomplete): report it as "not in AgentCM's index" and check the plan sheets' `.construction/sheets/<sheet>.tsv`. ### Step 5: Output to Excel ```bash "${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_SKILL_DIR}/scripts/schedule_to_xlsx.py" \ --data ".construction/skills/schedule-extractor/<sheet>-<n>.json" \ --type door_schedule \ --project "Project Name" \ --sheet "A-0.01" \ --output "Door_Schedule_A-0.01.xlsx" ``` The data JSON is working data: write it (with the Write tool, or the reader in AgentCM mode) under `.construction/skills/schedule-extractor/`, never in the project root or a folder of your own. The workbook is the deliverable: write it in the project folder as the command above shows, never under `.construction/`. For a table AgentCM has already ingested (`schedule_id=<id>` on its file's comment line), add `--schedule-id <id> --sheet-id <the sheet's id>` so the workbook can come back through the round trip. The script creates a formatted Excel workbook with: - Header row with project info and source sheet - Auto-sized columns - Conditional formatting for empty/flagged cells - Source metadata in a separate tab ### Step 6: Ingest to AgentCM (AgentCM mode) After Excel output, send the extracted data to AgentCM's schedule ingest, which stores it and links its rows to rooms. The payload (below) goes in `.construction/skills/schedule-extractor/outbox/schedules-ingest-<sheet>-<YYYYMMDD-HHMMSS>.json`: - **A table from `schedules/` not yet ingested** (`schedule_id=none`): the reader writes it. Run it again with `--payload <that path> --sheet-id <id> --row-entity-type <type> --source-method hybrid --confidence <0 to 1>` (`hybrid`: AgentCM's reading, checked by eye with `crop.sh`), and make any correction from Step 3 in the payload's cell too. - **A table already ingested** (`schedule_id=<id>`): nothing to ingest. The reader refuses, and the PE edits come back through the round trip below. - **A table read by Methods A to C:** write the payload with the Write tool. `sourceMethod` is one of `pdfplumber`, `vision`, `hybrid` or `manual`, and `rowEntityType` one of the types listed below. Send it as `${CLAUDE_PLUGIN_ROOT}/reference/agentcm-mode.md` says under Writing back; if AgentCM is closed, the payload waits there and the PE is told: ```bash # api_url and project_id from .construction/database.yaml curl -sS -X POST "{api_url}/projects/{project_id}/schedules/ingest" \ -H "Content-Type: application/json" \ -d @".construction/skills/schedule-extractor/outbox/schedules-ingest-<sheet>-<YYYYMMDD-HHMMSS>.json" \ -w "\nHTTP %{http_code}\n" ``` **The sheet's id.** `sheetId` is the `id` of the entry in `.construction/index/sheet_index.yaml` whose `sheet_number` is the schedule's sheet. Never guess it from the number. If that file is missing, or its entries have no `id` (an older sheet-splitter wrote a `pages:` list with `filename` and `page_index`), say so and stop: do not ingest. The Excel workbook from Step 5 stands either way. The ingest payload JSON follows this structure: ```json { "sheetId": "<uuid of the source sheet>", "scheduleType": "door", "title": "DOOR SCHEDULE", "rowEntityType": "door", "columns": [ {"key": "MARK", "header": "MARK"}, {"key": "WIDTH", "header": "WIDTH"}, {"key": "HEIGHT", "header": "HEIGHT"}, {"key": "TYPE", "header": "TYPE"}, {"key": "FRAME", "header": "FRAME"}, {"key": "HARDWARE_SET", "header": "HARDWARE SET"}, {"key": "FIRE_RATING", "header": "FIRE RATING"} ], "rows": [ { "entityIdentifier": "2.101A", "cells": {"MARK": "2.101A", "WIDTH": "3'-0\"", "HEIGHT": "7'-0\"", "TYPE": "A", "FRAME": "HM", "HARDWARE_SET": "HW-3", "FIRE_RATING": "1 HR"} } ], "sourceMethod": "pdfplumber", "confidence": 0.95 } ``` **Schedule types and their `rowEntityType`:** - Door schedule → `"door"` (room derived from door number: "2.101A" → Room 2.101) - Window schedule → `"window"` (room derived from window mark) - Finish schedule → `"room"` (entity identifier IS the room number) - Panel schedule → `"equipment"` - Fixture schedule → `"fixture"` (room linked spatially from plan tags) The ingest endpoint automatically: