Data Dictionary
Comprehensive field definitions for all tables and columns in the PSI analytics system.
redbooks Table
The main table containing quality issue records with AI classification.
Identification Fields
| Field | Type | Description |
|---|
| Unique_ID | String | Primary key (ID + project combination) |
| ID | Integer | Redbook ticket number |
| Project | String | Normalized project/job number |
| Customer | String | Customer name from project metadata |
| Industry | String | Industry classification |
Timing Fields
| Field | Type | Description |
|---|
| Ent_Date | Date | Entry/opened date |
| Comp_Date | Date | Completion/closed date |
| First_Action_Date | Date | Date of first action taken |
| Ship_Date | Date | Project ship date (planned) |
| Ship_Year | Integer | Year project shipped |
| Days_Before_Ship | Integer | Days between entry and ship (negative = post-ship) |
| Ship_Timing_Category | String | Detection timing category |
| Resolution_Days | Integer | Days from open to close |
People Fields
| Field | Type | Description |
|---|
| Opener_Name | String | Name of person who opened ticket |
| Opener_Dept | String | Department of opener |
| Closer_Name | String | Name of person who closed ticket (often unavailable) |
| Mech_Engineer_Name | String | Project mechanical engineer |
| Controls_Engineer_Name | String | Project controls engineer |
| Proposal_Engineer_Name | String | Project proposal engineer |
Content Fields
| Field | Type | Description |
|---|
| Problem | Text | Problem description |
| Cause | Text | Activity log/cause notes |
| Solution | Text | Resolution description |
| Solution_Clean | Text | Parsed activity log |
| Drawing | String | Related drawing number |
| Drawing_Description | String | Drawing description |
| Priority | String | Priority level (1-High to 4-None) |
| Runoff | Binary | Runoff-related indicator |
AI Classification Fields
| Field | Type | Description |
|---|
| AI_Category | String | AI-classified category |
| AI_Severity | String | Critical/Major/Moderate/Minor |
| AI_RootCause | String | AI-classified root cause |
| AI_Confidence | Float | Confidence score (0-1) |
| AI_Reasoning | Text | AI explanation of classification |
AI Hour Estimate Fields
| Field | Type | Description |
|---|
| Hours_Mech_Engineering | Float | Mechanical engineering hours |
| Hours_Controls_Engineering | Float | Controls engineering hours |
| Hours_Mech_Assembly | Float | Mechanical assembly hours |
| Hours_Elec_Assembly | Float | Electrical assembly hours |
| Hours_Machine_Weld | Float | Machining/welding hours |
| Hours_MVI | Float | MVI/testing hours |
| Hours_Service | Float | Field service hours |
| Hours_Total | Float | Total estimated hours |
Cost Fields
| Field | Type | Description |
|---|
| Cost_Mech_Eng | Float | Mech engineering labor cost |
| Cost_Controls_Eng | Float | Controls engineering labor cost |
| Cost_Mech_Assembly | Float | Mech assembly labor cost |
| Cost_Elec_Assembly | Float | Elec assembly labor cost |
| Cost_Machine_Weld | Float | Machining/welding labor cost |
| Cost_MVI | Float | MVI labor cost |
| Cost_Service | Float | Field service labor cost |
| Cost_Labor_Total | Float | Total labor cost |
| Cost_Material_Est | Float | Estimated material cost |
| Cost_Total | Float | Total quality cost (labor + material) |
Material Impact Fields
| Field | Type | Description |
|---|
| Has_Material_Cost | Boolean | Whether issue involves material cost |
| Material_Level | String | Low/Medium/High material impact |
| Involves_Rework | Boolean | Requires rework |
| Involves_New_Parts | Boolean | Requires new parts |
| Involves_Scrap | Boolean | Involves scrapped material |
ECN/NCN Fields
| Field | Type | Description |
|---|
| Has_ECN | Boolean | Has associated ECN (from raw data) |
| Has_NCN | Boolean | Has associated NCN (from raw data) |
| ECN_Number | String | ECN number if applicable |
| NCN_Number | String | NCN number if applicable |
| Is_ECN_Final | Boolean | Final ECN determination |
| ECN_Source | String | ”Raw Data”, “AI Inferred”, or “None” |
| ECN_Conflict | Boolean | Raw data conflicts with AI |
Project Enrichment Fields
| Field | Type | Description |
|---|
| Process_Type_1 | String | Primary process type |
| Process_Type_2 | String | Secondary process type |
| Machine_Type_1 | String | Primary machine type |
| Project_Value | Float | Project order value |
| Is_Retrofit | Boolean | Retrofit vs new build |
Product Linkage Fields
| Field | Type | Description |
|---|
| Product_Number | String | PHYS.-level part number |
| Product_Name | String | Part description |
| Product_Class_ID | String | Product class identifier |
| Product_Class_Name | String | Product class name (345 categories) |
| Product_Deployment_Count | Integer | Number of projects using this product |
Design Reuse Fields
| Field | Type | Description |
|---|
| Deployment_Number | Integer | 1st, 2nd, 3rd deployment of product |
| Is_First_Time | Boolean | True if Deployment_Number = 1 |
| Is_Recurring_Escape | Boolean | Product+root cause on multiple projects |
product_deployment_history Table
Tracks where each physical part has been deployed.
| Field | Type | Description |
|---|
| part_number | String | Part number |
| description | String | Part description |
| gtcode | String | Group technology code |
| project | String | Project number |
| ship_date | Date | Project ship date |
| deployment_number | Integer | Nth deployment of this part |
| is_first_deployment | Boolean | First time deployed |
| total_deployments | Integer | Total projects using this part |
project_reuse_metrics Table
Per-project reuse statistics.
| Field | Type | Description |
|---|
| project | String | Project number |
| ship_date | Date | Project ship date |
| level | String | GTCode level (PHYS., MODULE, etc.) |
| total_parts | Integer | Total parts at this level |
| first_time_parts | Integer | Parts deployed for first time |
| reused_parts | Integer | Parts previously deployed |
| reuse_rate | Float | Percentage of reused parts |
part_quality_metrics Table
Quality risk scoring for individual parts.
| Field | Type | Description |
|---|
| physical_part | String | Part number |
| description | String | Part description |
| product_class | String | Product class name |
| issue_count | Integer | Number of associated redbooks |
| projects_with_issues | Integer | Projects where issues occurred |
| total_quality_cost | Float | Total cost of issues |
| deployment_count | Integer | Number of deployments |
| issue_rate | Float | Issues per deployment |
| dominant_root_cause | String | Most common root cause |
| first_time_issues | Integer | Issues on first deployment |
| first_time_issue_rate | Float | First-time issue rate |
| quality_risk_score | Float | 0-100 risk score |
Calculated Fields Reference
Detection Timing Categories
| Category | Calculation |
|---|
| Early (>90 days before ship) | Days_Before_Ship > 90 |
| Mid-Build (30-90 days) | 30 < Days_Before_Ship ⇐ 90 |
| Late Build (<30 days) | 0 < Days_Before_Ship ⇐ 30 |
| Near/At Ship | Days_Before_Ship == 0 |
| Post-Ship (Field) | Days_Before_Ship < 0 |
Cost Calculations
Cost_Labor_Total = Sum(Hours_* × Rate_* × Calibration × Stage_Multiplier)
Cost_Total = Cost_Labor_Total + Cost_Material_Est
Stage_Multiplier based on Ship_Timing_Category:
- Early: 1.0x
- Mid-Build: 1.25x
- Late Build: 1.5x
- Near/At Ship: 2.0x
- Post-Ship: 2.0x
Labor Rates (defaults):
- Engineering: $150/hr
- Shop: $125/hr
Quality % Calculation
Project Quality % = (Sum of Cost_Total for project) / Project_Value × 100
Quality % Target: <2%
Quality Risk Score
quality_risk_score = weighted combination of:
- issue_rate (issues per deployment)
- projects_with_issues (project spread)
- total_quality_cost (cost impact)
- first_time_issue_rate
Scale: 0-100 (higher = higher risk)
Related Pages
Last updated: February 2026*