# PSI Data Brain — Machine-Readable Companion
# Structured version of data-brain.md for AI agents and MCP tools.
# Use this to programmatically look up: "Where is [X] data?"
#
# Last updated: 2026-02-26

version: "1.1"
last_updated: "2026-03-09"
description: "PSI data source map — what exists, where it lives, how to access it"

# ─────────────────────────────────────────────────
# DATA SOURCES
# ─────────────────────────────────────────────────
sources:
  psi_analytics_db:
    name: "PSI Analytics Database"
    type: "sqlite"
    location: "\\\\PS-GR-FS01\\Schedule\\SS123\\LEADTIME\\psi_analytics.db"
    repo: "psi-data-pipeline/pipeline/build_analytics_db.py"
    access:
      - method: "direct SQLite read"
        requires: "VPN"
        mode: "WAL (concurrent reads supported)"
    refresh: "nightly at 1 AM ET (GHE Actions on PS-PROXY)"
    tables:
      projects: { rows: 2570, description: "Core project identity" }
      labor_detail: { rows: 1380000, description: "Full tslabor2 for project jobs" }
      department_summary: { rows: 16000, description: "Per-(job, dept) aggregates with P2/P98" }
      redbooks: { rows: 40000, description: "AI-classified quality issues" }
      ecn_detail: { rows: 28000, description: "ECNs with Code field breakdown" }
      ncn_detail: { rows: 18000, description: "Non-conformance notices" }
      purchase_orders: { rows: 13600, description: "PO data matched to projects" }
      work_orders: { rows: 100000, description: "Work orders for project jobs" }
      bom_parts: { rows: 4300000, description: "BOM hierarchy" }
      plc_complexity: { rows: 487, description: "PLC program complexity scores" }
      machine_dna_features: { rows: 3000, description: "Quality prediction features (82 cols)" }
      rd_classification: { rows: 105, description: "R&D project flags" }
    views:
      comprehensive_view: { description: "Joins all tables — replaces flat CSV for queries" }
    answers:
      - "Everything the CSV answers, plus multi-row queries"
      - "ECN breakdown by type per project"
      - "Individual redbook records with AI classification"
      - "Full labor history per employee per project"
      - "BOM hierarchy queries"
    verified: "2026-03-09"

  aftec_erp:
    name: "AFTEC ERP (UniData)"
    type: "database"
    location: "MRP-PROD server (on-premises)"
    access:
      - method: "REST API"
        url: "https://api.progressivesurface.com/api"
        auth: "Azure AD or dev endpoints (no auth)"
      - method: "U2 Toolkit for .NET"
        connection: "Direct UniData connection"
    refresh: "real-time"
    key_tables:
      - "PROJECT.1287"
      - "MACHTYPE.1287"
      - "OPENWO"
      - "INVHIST"
      - "REDBOOK.1287"
    subroutines: 370
    answers:
      - "What is the current status of job X?"
      - "What parts are on this BOM?"
      - "Work order details and status"
    verified: "2026-02-26"

  csv_exports:
    name: "CSV Exports (Daily)"
    type: "flat_files"
    location: "\\\\fs1\\data\\unixshare\\dgwreportfiles\\"
    format: "pipe-delimited CSV"
    access:
      - method: "direct file read"
        requires: "VPN"
    refresh: "daily (AFTEC export jobs)"
    total_records: 9600000
    versioning: false
    versioning_note: "Files are overwritten daily. No historical snapshots."
    files:
      ganttJOB:
        rows: 892
        fields: ["Job", "Dept", "Start", "End", "% Complete"]
        answers: ["Department-level schedule per project"]
      ganttWO:
        rows: 7776
        fields: ["Job", "WO", "Part", "Op", "Start", "End", "% Comp"]
        answers: ["WO-level schedule"]
      openwo:
        rows: 519286
        fields: ["WO", "Job", "MakeDate", "ReleaseDate", "StartDate", "DueDate", "CloseDate", "Status"]
        answers: ["WO cycle times", "Which WOs are late?"]
      tslabor2:
        rows: 2773043
        fields: ["Date", "WE Date", "Dept", "Emp", "Employee Name", "Job", "WO", "Op", "Op Desc", "Labor", "Setup"]
        dept_codes:
          "110E": "Controls Engineering"
          "110M": "Mechanical Engineering"
          "110": "Engineering (general/legacy)"
          "102": "Machine Shop"
          "104": "Weld/Fabrication"
          "106": "Mechanical Assembly"
          "108": "Electrical Assembly"
          "120": "MVI/Startup"
          "122": "Startup/Runoff"
          "135": "Field Service"
          "150": "Supply Chain"
        ce_operation_codes:
          "2055": "CONTROL ADMINISTRATI"
          "2120": "CONTROLS DESIGN"
          "2130": "CONTROL DOCUMENTATIO"
          "2140": "SOFTWARE DESIGN"
          "2150": "SOFTWARE TEST"
          "2151": "CONTROL TRAVEL"
          "2162": "FLOOR ENGINEER"
        answers:
          - "Where are labor hours being spent?"
          - "Engineer workload across projects"
          - "CE labor timeline heatmaps"
          - "Department hours trends over time"
      wiprouteline:
        rows: 927344
        fields: ["Job", "WO", "Op", "PlanStart", "PlanComp", "ActStart", "ActComp", "% Comp"]
        answers: ["Planned vs actual at operation level"]
      wiplabor:
        rows: 253395
        fields: ["Job", "WO", "Part", "Routed hrs", "Actual hrs"]
        answers: ["Hours overrun by department"]
      redbook:
        rows: 50057
        fields: ["RFC#", "Project", "Dept flags (16)", "Days Open", "Ent Date", "Ship Date"]
        answers: ["Quality issue counts", "Resolution times", "Department impact"]
      ecn:
        rows: 28413
        fields: ["ECN#", "Project", "Dept", "Date"]
        answers: ["Engineering change volume per project"]
      purchaseorders:
        rows: 108768
        fields: ["PO", "Line", "Part No", "Vendor", "Promise Date", "Schedule Date", "Confirm Date", "Lead Time"]
        gotchas:
          - "Promise Date is CURRENT (possibly revised), not original"
          - "Schedule Date is 38.6% empty"
          - "Confirm Date is order acknowledgment, NOT delivery"
          - "AFTEC stores 4 promise revisions via VB_PO.LINE.REV1 but only 1 is exported"
        answers: ["Vendor on-time delivery", "PO lead times"]
      pohist:
        rows: 268238
        fields: ["PO", "Line", "Rcv Date", "Qty Received"]
        answers: ["Actual vendor delivery dates"]
      budgetjob:
        rows: 1058
        fields: ["Job", "CE_BUD", "CE_ACT", "CE_PCT", "EA_BUD", "EA_ACT", "MA_BUD", "MA_ACT", "..."]
        dept_columns: ["CE", "EA", "FS", "MA", "MC", "ME", "PROD", "SU", "WE"]
        answers: ["Budget vs actual hours by department per project"]
      jobparts:
        rows: 203277
        fields: ["Job", "Part No"]
        answers: ["Job-to-part BOM linkage"]
      partmaster:
        rows: 420950
        fields: ["Part No", "Mfg LT", "Pur LT", "Cum LT"]
        answers: ["Standard lead times per part"]
      salesorder:
        rows: 351
        fields: ["Order date", "Amount", "Customer"]
        answers: ["When was the order placed?"]
      wc-capacity:
        rows: 31
        fields: ["Work center", "Capacity hours"]
        answers: ["Weekly capacity by work center"]
      capacity:
        rows: 23834
        answers: ["Load vs capacity outlook"]
    verified: "2026-02-26"

  fabric:
    name: "Microsoft Fabric / Power BI — ProgressiveDataSet"
    type: "semantic_model"
    location: "Fabric workspace (John Tymes personal)"
    workspace_id: "4879a6ff-c0c4-4f1a-87b5-638a21e04f5f"
    dataset_id: "51d7822c-8d17-4fb5-b28f-50a4c6fdd19e"
    access:
      - method: "DAX via Azure CLI"
        command: 'az rest --method POST --url "https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/executeQueries" --resource "https://analysis.windows.net/powerbi/api" --body ''{"queries":[{"query":"EVALUATE ..."}],"serializerSettings":{"includeNulls":true}}'''
        auth: "Azure AD (az login)"
    refresh: "daily at 5:00 AM ET"
    tables: 33
    total_rows: 9600000
    versioning: false
    versioning_note: "Full overwrite daily. No change history, no deleted record retention, no diff tracking."
    answers:
      - "Same as CSV files but with DAX aggregation capabilities"
      - "Cross-table joins and summaries"
    verified: "2026-02-26"

  schedule_excel:
    name: "Schedule Excel Files"
    type: "excel_workbooks"
    location: "\\\\PS-GR-FS01\\Schedule\\SS123\\SCHEDULE\\"
    access:
      - method: "PSI.UniData.API"
        endpoints:
          - "GET /api/schedule/dev/summary"
          - "GET /api/schedule/dev/departments"
          - "GET /api/schedule/dev/lds/{jobNumber}"
    refresh: "manual (PMO team)"
    files:
      "Mecheng.xlsm": { dept: "Mech Engineering", path: "ENG/" }
      "CONTROLS.xlsm": { dept: "Controls Engineering", path: "ENG/" }
      "AMACH.xlsm": { dept: "Machine Shop", path: "PROD/" }
      "AWELD.xlsm": { dept: "Weld", path: "PROD/" }
      "AMECH.xlsm": { dept: "Mech Assembly", path: "PROD/" }
      "AELECT.xlsm": { dept: "Elec Assembly", path: "PROD/" }
      "AMVI.xlsm": { dept: "MVI/Startup", path: "PROD/" }
    lds_pattern: "\\\\PS-GR-FS01\\lds\\PROJECT\\{JobNo}\\lds\\{JobNo}lds.xlsm"
    answers:
      - "PMO priorities and deviation"
      - "Department weekly hour allocations"
      - "Per-project milestones and Gantt"
    verified: "2026-02-26"

  project_explorer:
    name: "Project Explorer Dataset"
    type: "xml_export"
    location: "C:\\git\\project-explorer\\public\\RawData\\"
    key_file: "Project1287List.xml"
    records: 3084
    access:
      - method: "direct file read"
    fields:
      - "OrderDate"
      - "ShipDate"
      - "PlannedShipDate"
      - "AccountNo"
      - "MachineOneName"
    answers:
      - "Lead time trends over 25 years"
      - "On-time delivery rate"
      - "Project metadata (customer, machine type)"
    verified: "2026-02-26"

  redbook:
    name: "Redbook Quality Database"
    type: "multi_source"
    sources:
      - "REDBOOK.1287 (UniData)"
      - "redbook.csv (CSV export)"
      - "redbook_coq.db (SQLite)"
    access:
      - method: "REST API"
        endpoints:
          - "GET /api/redbook/rfc/{rfc}"
          - "GET /api/redbook/by-project/{job}"
      - method: "CSV"
      - method: "Streamlit dashboard"
        url: "https://ps-redbook-dashboard.azurewebsites.net"
    records: 50057
    answers:
      - "Quality issue counts per project"
      - "Resolution times"
      - "Cost of quality"
      - "Department assignment/completion"
    verified: "2026-02-26"

  plc_complexity_scores:
    name: "PLC Complexity Scores"
    type: "csv_files"
    location: "C:\\git\\PLC\\"
    files:
      fleet_complexity_scores.csv:
        rows: 487
        fields: ["Job", "Complexity", "TotalRungs", "TagCount", "UdtDepth", "RoutineCount", "ProgramCount", "AvgRungs", "JsrDepth", "TimerDensity", "TonCount", "AcdFile", "LastModified", "Error"]
        description: "Per-project PLC complexity score (0-100) with 7 sub-metric breakdowns"
      fleet_complexity_trend.csv:
        description: "Yearly summary — N, Mean, Median, Min, Max, P25, P75 complexity by ShipYear"
      fleet_complexity_joined.csv:
        rows: 451
        description: "Per-project scores joined to comprehensive_dataset (Job, ShipYear, Customer, MachineType, Complexity, ...)"
    scripts:
      - "C:\\git\\ra-logix-designer-vcs-custom-tools\\scripts\\batch-complexity-scan.py"
      - "C:\\git\\ra-logix-designer-vcs-custom-tools\\scripts\\build-complexity-trend.js"
    refresh: "manual — python batch-complexity-scan.py --resume"
    extraction_paths:
      full: "v29+ ACDs (109 projects) — ExportL5x, all 7 sub-metrics including TagInfo.XML"
      fallback: "v12-v20 ACDs (378 projects) — partial sqlite DB, 5 sub-metrics (tagCount=0, udtDepth=0)"
    scoring_weights:
      rungCount: 0.20
      avgRungs: 0.20
      tagCount: 0.15
      routineCount: 0.15
      udtDepth: 0.10
      jsrDepth: 0.10
      timerDensity: 0.10
    key_finding: "Median complexity 32-35 (2010-2022), then 37→41→48 (2022→2024→2025). P75 jumped 37→55. Max 47→73."
    answers:
      - "How complex is the PLC program for job X?"
      - "Has machine complexity trended up over time?"
      - "Which machine types are most complex?"
      - "What's the complexity distribution for a given ship year?"
    join_key: "Job → comprehensive_dataset.csv Job"
    verified: "2026-03-03"

# ─────────────────────────────────────────────────
# JOIN KEYS
# ─────────────────────────────────────────────────
joins:
  - from: "Any project data"
    to: "openwo"
    key: "JobNumber = openwo.Job"
    note: "Core project-to-WO link"

  - from: "openwo"
    to: "tslabor2"
    key: "WO = tslabor2.WO"
    note: "Track hours per work order"

  - from: "openwo"
    to: "wiprouteline"
    key: "WO = wiprouteline.WO"
    note: "Planned vs actual by operation"

  - from: "project"
    to: "redbook"
    key: "JobNumber = redbook.Project"
    note: "Quality issues per project"

  - from: "project"
    to: "budgetjob"
    key: "JobNumber = budgetjob.Job"
    note: "Budget vs actual by department"

  - from: "project"
    to: "ganttJOB"
    key: "JobNumber = ganttJOB.Job"
    note: "Department-level schedule"

  - from: "parts"
    to: "purchaseorders"
    key: "PartNo = purchaseorders.Part No"
    note: "Supply chain / vendor data"

  - from: "parts"
    to: "partmaster"
    key: "PartNo = partmaster.Part No"
    note: "Standard lead times"

  - from: "employee"
    to: "tslabor2"
    key: "EmpNo = tslabor2.Emp"
    note: "Employee labor history"

  - from: "tslabor2"
    to: "project timeline"
    key: "Job + Date, normalized by OrderDate"
    note: "For timeline heatmaps — when labor occurs relative to project start"

# ─────────────────────────────────────────────────
# QUESTION INDEX
# Lookup: "I need to answer [question] — which source?"
# ─────────────────────────────────────────────────
question_index:
  "lead time trend":
    primary: "project_explorer"
    field: "OrderDate → ShipDate"
    supporting: ["fabric (budgetjob)"]

  "vendor on-time delivery":
    primary: "csv_exports.purchaseorders"
    field: "Promise Date vs pohist.Rcv Date"
    gotcha: "Promise Date may be revised. No original date in CSV."

  "department bottleneck":
    primary: "csv_exports.ganttJOB"
    field: "Start/End per dept, % Complete"
    supporting: ["csv_exports.tslabor2"]

  "CE labor heatmap":
    primary: "csv_exports.tslabor2"
    filter: "Dept = '110E'"
    normalize_by: "project_explorer OrderDate"
    operations: ["CONTROLS DESIGN", "SOFTWARE DESIGN", "FLOOR ENGINEER"]

  "budget vs actual":
    primary: "fabric.budgetjob"
    field: "CE_BUD, CE_ACT, CE_PCT (and per dept)"

  "work order cycle time":
    primary: "csv_exports.openwo"
    field: "MakeDate → CloseDate, DueDate for lateness"

  "quality issues per project":
    primary: "redbook"
    field: "Project, count of RFCs"

  "engineer workload":
    primary: "csv_exports.tslabor2"
    field: "Employee Name, Job, Hours"
    group_by: "Emp across multiple Jobs"

  "capacity vs load":
    primary: "csv_exports.wc-capacity"
    supporting: ["csv_exports.capacity"]
    limitation: "Only 31 rows — limited formal capacity planning"

  "plc complexity for job":
    primary: "plc_complexity_scores.fleet_complexity_scores.csv"
    field: "Job column, Complexity score (0-100)"
    supporting: ["plc_complexity_scores.fleet_complexity_joined.csv"]

  "machine complexity trend over time":
    primary: "plc_complexity_scores.fleet_complexity_trend.csv"
    field: "ShipYear, Median, P75, N"
    supporting: ["plc_complexity_scores.fleet_complexity_joined.csv"]
    key_finding: "Median 32-35 (2010-2022) → 48 (2025); P75 37 → 55"

  "good vs bad project":
    sources: ["fabric.budgetjob", "csv_exports.tslabor2", "redbook", "csv_exports.purchaseorders"]
    indicators:
      - "CE budget % (good: 25%, bad: 190%)"
      - "RFC count (good: 22-37, bad: 50-174)"
      - "SOFTWARE DESIGN hours (good: 12, bad: 306)"
      - "FLOOR ENGINEER hours (good: 0.8, bad: 22)"

# ─────────────────────────────────────────────────
# KNOWN GAPS
# ─────────────────────────────────────────────────
known_gaps:
  - id: "no_versioning"
    description: "No versioning in Fabric/CSV — can't track field changes over time"
    impact: "high"
    workaround: "Implement daily CSV snapshot archiving"

  - id: "po_promise_current_only"
    description: "PO promise date is current (possibly revised), not original"
    impact: "high"
    workaround: "Expose VB_PO.LINE.REV1 via API for 4-date history"

  - id: "no_sw_complexity_metric"
    description: "No CE software complexity metric at proposal stage"
    impact: "medium"
    status: "RESOLVED 2026-03-03"
    resolution: "PLC complexity pipeline scored 487 ACDs. fleet_complexity_scores.csv has 7-sub-metric scores per project. See plc_complexity_scores source."

  - id: "limited_capacity_data"
    description: "Only 31 rows in wc-capacity"
    impact: "medium"
    workaround: "Supplement with PMO floor space data"

  - id: "schedule_date_empty"
    description: "purchaseorders.Schedule Date is 38.6% empty"
    impact: "low"
    workaround: "Use Promise Date + pohist Rcv Date as primary OTD measure"
