Data Normalization

Data Normalization

Overview

This skill defines 8 normalization patterns that can be applied to any ForemanOS Project Brain to fix common data quality issues. Each pattern is idempotent (safe to re-run) and produces a repair plan showing what will change before executing.

The patterns complement the detection layer (data-integrity-watchdog agent, conflict-detection-agent) by providing the fix side of the data health stack.

Prerequisites

Before applying any pattern, the AI must:

1. Read the target JSON file(s) in full
2. Read ${CLAUDE_PLUGIN_ROOT}/skills/project-data/SKILL.md for canonical key names
3. Generate a repair plan listing every proposed change (field path, before value, after value)
4. Present the repair plan to the user for confirmation
5. Apply changes via a single atomic Python script that reads → modifies → writes all affected files
6. Validate the output (re-read and confirm changes applied)

Repair Plan Format

Before any changes, output a table:

| # | File | Path | Before | After | Pattern |
|---|------|------|--------|-------|---------|
| 1 | submittal-log.json | submittal_log[1].status | "submitted" | "SUBMITTED" | N1 |
| 2 | cost-data.json | summary.contingency_remaining | (missing) | 43225 | N6 |

Only proceed after user confirmation.

Pattern N1: Status Standardization

Problem: Mixed case and wording in status fields across different JSON files.

Logic: Each file type has canonical status enums. Normalize all status fields to match.

Canonical Status Values

Normalization Rules

1. Case-insensitive match to canonical value (e.g., "submitted" -> "SUBMITTED" for submittals)
2. Synonym mapping:
   • "approved" / "APPROVED" / "Approved" -> APPROVED
   • "review_pending" / "Review Pending" / "REVIEW PENDING" / "pending_review" -> REVIEW_PENDING
   • "assumed_passed" -> pass (for inspections)
   • "in progress" / "In Progress" / "IN_PROGRESS" -> in_progress
3. If no match found, flag for manual review (do not auto-fix)

Application

# Pseudocode
for entry in submittal_log:
    normalized = normalize_status(entry["status"], SUBMITTAL_STATUSES)
    if normalized != entry["status"]:
        plan.append(change(entry, "status", entry["status"], normalized, "N1"))

Pattern N2: Counter Reconciliation

Problem: Stored scalar counts drift from actual array lengths after edits.

Logic: Recount from source arrays, compare to stored scalar, fix if mismatched.

Known Counter Fields

Application

actual = len(data["documents_loaded"])
stored = data.get("documents_loaded_count", None)
if stored != actual:
    plan.append(change("documents_loaded_count", stored, actual, "N2"))

Pattern N3: Cross-Reference Linking

Problem: Missing bidirectional references between related records across files.

Logic: Match by vendor name, spec section, activity ID, or other shared identifiers. Add link fields.

Link Definitions

Matching Rules

1. Vendor match: Case-insensitive substring match on supplier/vendor name. "Schiller" matches "Schiller Architectural Hardware".
2. Spec section match: Normalize to format "XX XX XX" (spaces, no dashes). "08 11 13" matches "081113".
3. Activity ID match: Exact match on linked_schedule_activity_id or activity_id.
4. Only add links — never remove existing links. If a link field already exists and has a value, skip.

Application

# Match submittals to procurement by vendor
for sub in submittal_log:
    if "procurement_link" not in sub or not sub["procurement_link"]:
        for po in procurement_log:
            if vendor_match(sub.get("vendor",""), po.get("supplier","")):
                sub["procurement_link"] = po["po_number"]
                po.setdefault("submittal_ids", [])
                if sub["submittal_id"] not in po["submittal_ids"]:
                    po["submittal_ids"].append(sub["submittal_id"])
                break

Pattern N4: Field Backfill

Problem: Array entries missing required fields (status, id, type, dates).

Logic: Apply defaults based on existing data context.

Required Fields by File

Application

for i, doc in enumerate(documents_loaded):
    if "id" not in doc:
        doc["id"] = f"DOC-{i+1:03d}"
        plan.append(change(f"documents_loaded[{i}].id", "(missing)", doc["id"], "N4"))
    if "status" not in doc:
        status = infer_status(doc)
        doc["status"] = status
        plan.append(change(f"documents_loaded[{i}].status", "(missing)", status, "N4"))

Pattern N5: Key Schema Compliance

Problem: Wrong key names or missing required top-level keys.

Logic: Check against canonical keys from the project-data skill. Rename or add missing keys.

Canonical Top-Level Keys

Reference: foremanos-core/skills/project-data/references/json-schema-reference.md

Common Renames

Application

if "construction_activities" in schedule and "activities" not in schedule:
    schedule["activities"] = schedule.pop("construction_activities")
    plan.append(rename("construction_activities", "activities", "N5"))

Pattern N6: Computed Totals

Problem: Missing aggregated values that should be computed from child records.

Logic: Sum from child records, write to parent field.

Computed Fields

Application

used = sum(co["amount"] for co in change_orders if co.get("source") == "contingency")
# Or from known COs:
used = 6775  # Nucor CO#1
remaining = summary["contingency"] - used
if "contingency_used" not in summary:
    summary["contingency_used"] = used
    plan.append(change("summary.contingency_used", "(missing)", used, "N6"))
if "contingency_remaining" not in summary:
    summary["contingency_remaining"] = remaining
    plan.append(change("summary.contingency_remaining", "(missing)", remaining, "N6"))

Pattern N7: Date/Format Normalization

Problem: Inconsistent date formats and number types across files.

Logic: Standardize all dates to ISO 8601, all numeric values as numbers (not strings).

Rules

1. Dates: All date fields must be ISO 8601 format: YYYY-MM-DD

   • "01/22/2026" -> "2026-01-22"
   • "Jan 22, 2026" -> "2026-01-22"
   • "1/22/26" -> "2026-01-22"
   • If ambiguous (e.g., "02/03/26"), prefer US format (MM/DD/YY)

2. Numbers: Numeric fields must be actual numbers, not strings

   • "184500" -> 184500
   • "$184,500" -> 184500
   • "15.5%" -> 0.155 (for rate fields) or 15.5 (for display fields — context-dependent)

3. Booleans: Boolean fields must be actual booleans

   • "true" / "yes" / "Y" -> true
   • "false" / "no" / "N" -> false

Date Fields to Check

Pattern N8: Deduplication

Problem: Duplicate entries in arrays from multiple extraction passes or manual entry.

Logic: Detect by ID field or fuzzy match on description + date. Merge or flag.

Detection Rules

1. Exact ID match: Two entries with same id / submittal_id / po_number -> merge (keep the one with more fields populated)
2. Fuzzy match: Same vendor + same spec_section + dates within 7 days -> flag for review
3. Content match: Same description (case-insensitive, trimmed) + same date -> merge

Merge Strategy

When merging duplicates:

1. Keep the entry with more populated fields
2. Copy any non-null fields from the less-complete entry that are missing in the keeper
3. Record the merge in a _merge_note field: "Merged from duplicate entry on {date}"
4. Never delete — mark the duplicate with _duplicate_of: "{keeper_id}" and remove from the main array

Application

seen = {}
for entry in submittal_log:
    key = entry.get("submittal_id")
    if key in seen:
        # Merge: keep entry with more fields
        keeper = seen[key] if len(seen[key]) >= len(entry) else entry
        donor = entry if keeper is seen[key] else seen[key]
        for k, v in donor.items():
            if k not in keeper or keeper[k] is None:
                keeper[k] = v
        keeper["_merge_note"] = f"Merged duplicate {donor.get('submittal_id')}"
        plan.append(merge(key, "N8"))
    else:
        seen[key] = entry

Execution Order

When running all 8 patterns on a Project Brain:

1. N5 (Schema Compliance) — Fix key names first so other patterns find data
2. N7 (Date/Format) — Normalize types before comparisons
3. N8 (Deduplication) — Remove duplicates before counting
4. N1 (Status Standardization) — Normalize status values
5. N4 (Field Backfill) — Fill missing required fields
6. N2 (Counter Reconciliation) — Recount after dedup and backfill
7. N3 (Cross-Reference Linking) — Link after all entries are clean
8. N6 (Computed Totals) — Compute aggregates last

Integration with /data-health Command

The /data-health command in the foremanos-compliance plugin orchestrates these patterns:

• /data-health scan — Runs detection only (finds issues that N1-N8 would fix)
• /data-health fix — Applies patterns with confirmation
• /data-health report — Generates health score including normalization coverage

Constraints

• Never delete data — Only add, rename, or modify fields
• Always confirm — Present repair plan before any changes
• Atomic writes — All changes to a file happen in one write operation
• Preserve formatting — Use json.dumps(data, indent=2, ensure_ascii=False) for output
• Log changes — After applying, add a _normalization_log entry to project-config.json version_history:

  {
    "date": "2026-02-26",
    "action": "data_normalization",
    "patterns_applied": ["N1", "N3", "N6"],
    "changes_count": 12,
    "files_modified": ["submittal-log.json", "procurement-log.json", "cost-data.json"]
  }