-- ============================================================================ -- Phase M6 — ATPD-2352 defect catalogue + per-area tolerance limits -- ============================================================================ -- -- Adapts the essence of ATPD-2352R Section 4.2.3 + Table VII + Glossary -- (Appendix A) for AGG's QC Final Inspection picker. The full military -- spec covers ballistic protection classes, optical tests, environmental -- chambers, and packaging requirements — we adopt only the visual-defect -- catalogue + the 3-zone (Area A/B/C) spatial tolerance model. -- -- Two new tables: -- -- ATPD_DEFECT_TYPES — the 14-row catalogue from Table VII + Appendix A -- Glossary. Each row carries an `assessment_type` enum describing HOW -- the defect is measured: size_mm (gaseous inclusions, digs, stones), -- length_mm (scratches), visibility_distance (crush, lint, surface -- defects, interlayer striae), angle (ream and string), not_allowed -- (delamination, cracking, crazing, clouding, cracks — zero tolerance -- everywhere). `requires_size_input` flags whether the picker UI -- should prompt for a measured size; visibility/angle/not_allowed -- types don't need a numeric input. -- -- ATPD_DEFECT_LIMITS — per-(defect, area) tolerance row. Three rows per -- defect type (one per Area A/B/C) so the picker can resolve the -- limit in one lookup. `limit_value` is the numeric threshold in -- `limit_unit`; `is_allowed=0` rows skip the threshold (Area A on -- every defect, plus the always-disallowed types). -- -- Per CLAUDE.md §15 + invariant 39, neither table uses AUTO_INCREMENT on -- legacy decimal PKs. These are NEW tables so the standard InnoDB AI is -- fine — the legacy row-size budget issue from CRTSALEGOODS / EMPLOYEE -- doesn't apply. -- -- Per CLAUDE.md §11 deploy convention, the migration is idempotent: -- - CREATE TABLE IF NOT EXISTS for both tables. -- - Seed via INSERT ... ON DUPLICATE KEY UPDATE so a re-run refreshes -- the catalogue values without colliding (the `code` column on -- ATPD_DEFECT_TYPES is UNIQUE; the (defect_type_id, area_code) pair -- on ATPD_DEFECT_LIMITS is UNIQUE). -- -- ============================================================================ CREATE TABLE IF NOT EXISTS ATPD_DEFECT_TYPES ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(20) NOT NULL, name VARCHAR(100) NOT NULL, description VARCHAR(500) NULL, assessment_type ENUM('size_mm','length_mm','visibility_distance', 'angle','not_allowed','subjective') NOT NULL, requires_size_input TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ATPD_DEFECT_LIMITS ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, defect_type_id INT UNSIGNED NOT NULL, area_code ENUM('A','B','C') NOT NULL, is_allowed TINYINT(1) NOT NULL DEFAULT 1, limit_value DECIMAL(7,3) NULL, limit_unit VARCHAR(10) NULL, note VARCHAR(500) NULL, PRIMARY KEY (id), UNIQUE KEY uq_defect_area (defect_type_id, area_code), KEY idx_defect_type (defect_type_id), CONSTRAINT fk_atpd_limits_type FOREIGN KEY (defect_type_id) REFERENCES ATPD_DEFECT_TYPES(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------------------------- -- Seed: 14 defect types from ATPD-2352R Table VII + Appendix A Glossary -- ---------------------------------------------------------------------------- INSERT INTO ATPD_DEFECT_TYPES (code, name, description, assessment_type, requires_size_input, sort_order) VALUES ('DELAMINATION', 'Delamination or bond separation', 'Separation of bonded transparent armor into visibly separated layers. Often suppressed by fluid pulled into the space — appears or disappears with humidity. Bond failure indicator.', 'not_allowed', 0, 10), ('CRACKING', 'Cracking, crazing, or clouding', 'Network of microscopic surface cracks visible as rainbow glint; crazing of the interlayer producing smoky / foggy appearance.', 'not_allowed', 0, 20), ('CRACK', 'Crack', 'Break in the glass, ceramic, or plastic oriented in the thickness direction of any layer of the laminate. Reject in all areas.', 'not_allowed', 0, 30), ('BUBBLE', 'Gaseous inclusion (bubble)', 'A gas pocket in the interlayer or between glass and interlayer; or an elongated bubble in the glass. Per ASTM C1036-06 / C1172-09. Measure longest dimension.', 'size_mm', 1, 40), ('OPEN_BUBBLE', 'Open gaseous inclusion (strike face only)', 'A gaseous inclusion that is open to the strike-face surface. Stricter tolerance than a closed bubble because moisture / contaminants can enter.', 'size_mm', 1, 50), ('STONE_KNOT', 'Stones and knots', 'Stone: crystalline inclusion in the glass. Knot: inhomogeneity in the form of a vitreous lump. Measure longest dimension.', 'size_mm', 1, 60), ('DIG', 'Dig', 'Per ASTM C1036-06: deep, short scratch. Measure longest dimension.', 'size_mm', 1, 70), ('INSIDE_DIRT', 'Inside dirt', 'Foreign matter trapped inside the laminate (between layers). Per ASTM C1172-09. Measure longest dimension.', 'size_mm', 1, 80), ('REAM_STRING', 'Ream and string', 'Linear distortion as a result of non-homogeneous layers of flat glass (ream); straight or curled line from slow solution of sand / foreign matter (string). Assessed by viewing angle.', 'angle', 0, 90), ('SCRATCH', 'Scratches and rubs', 'Per ASTM C1036-06: damage in line form caused by an object moved across the glass. Rub: frosted-appearance abrasion. ASTM F428 (glass) / F548 (plastics) grading. Measure length.', 'length_mm', 1, 100), ('CRUSH', 'Crush', 'Per ASTM C1036-06: lightly pitted condition with a dull gray appearance. Visibility-based assessment.', 'visibility_distance', 0, 110), ('LINT_HAIR', 'Lint and hair', 'Lint: short fibers of yarn or fabric trapped within the laminate. Hair: slender pigmented filament. Per ASTM C1172-09. Visibility-based assessment.', 'visibility_distance', 0, 120), ('ORGANIC', 'Insects or other organic matter', 'Organic detritus trapped between layers. Visibility-based assessment.', 'visibility_distance', 0, 130), ('SURFACE', 'Surface defects', 'Defects other than the named inclusion types — anything that hinders vision through the system but doesn''t fit another category. Visibility-based.', 'visibility_distance', 0, 140), ('STRIAE', 'Interlayer striae', 'A cord of low intensity in the interlayer per MIL-HDBK-722. Visibility-based at perpendicular viewing.', 'visibility_distance', 0, 150) ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), assessment_type = VALUES(assessment_type), requires_size_input = VALUES(requires_size_input), sort_order = VALUES(sort_order); -- ---------------------------------------------------------------------------- -- Seed: per-(defect, area) limits from Table VII -- Area A is concealed by frame/gasket — zero defects allowed. -- Area B is within 50mm of the concealed edge — intermediate tolerance. -- Area C is the remaining central area — strictest tolerance. -- ---------------------------------------------------------------------------- -- Helper: re-seed via subquery against the catalogue we just inserted. -- Each row carries (defect_code, area, is_allowed, limit_value, limit_unit, note). -- The subquery resolves defect_type_id by code so the migration stays -- idempotent across re-runs (no hardcoded auto-increment ids). INSERT INTO ATPD_DEFECT_LIMITS (defect_type_id, area_code, is_allowed, limit_value, limit_unit, note) SELECT t.id, x.area_code, x.is_allowed, x.limit_value, x.limit_unit, x.note FROM ATPD_DEFECT_TYPES t JOIN ( -- DELAMINATION — not allowed anywhere SELECT 'DELAMINATION' AS code, 'A' AS area_code, 0 AS is_allowed, NULL AS limit_value, NULL AS limit_unit, 'Not allowed in any area' AS note UNION ALL SELECT 'DELAMINATION','B',0,NULL,NULL,'Not allowed in any area' UNION ALL SELECT 'DELAMINATION','C',0,NULL,NULL,'Not allowed in any area' UNION ALL -- CRACKING / CRAZING / CLOUDING — not allowed anywhere SELECT 'CRACKING','A',0,NULL,NULL,'Not allowed in any area' UNION ALL SELECT 'CRACKING','B',0,NULL,NULL,'Not allowed in any area' UNION ALL SELECT 'CRACKING','C',0,NULL,NULL,'Not allowed in any area' UNION ALL -- CRACK — not allowed anywhere SELECT 'CRACK','A',0,NULL,NULL,'Cracks not allowed in Area A, B, or C' UNION ALL SELECT 'CRACK','B',0,NULL,NULL,'Cracks not allowed in Area A, B, or C' UNION ALL SELECT 'CRACK','C',0,NULL,NULL,'Cracks not allowed in Area A, B, or C' UNION ALL -- BUBBLE — size limit SELECT 'BUBBLE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'BUBBLE','B',1,2.400,'mm','Max 2.4 mm (0.093 in) in Area B' UNION ALL SELECT 'BUBBLE','C',1,1.600,'mm','Max 1.6 mm (0.062 in) in central viewing Area C' UNION ALL -- OPEN BUBBLE — strike face only SELECT 'OPEN_BUBBLE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'OPEN_BUBBLE','B',1,1.600,'mm','Max 1.6 mm (0.062 in) in Area B (strike face only)' UNION ALL SELECT 'OPEN_BUBBLE','C',1,1.200,'mm','Max 1.2 mm (0.045 in) in Area C (strike face only)' UNION ALL -- STONE_KNOT — tightest size tolerance SELECT 'STONE_KNOT','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'STONE_KNOT','B',1,1.200,'mm','Max 1.2 mm (0.045 in) in Area B' UNION ALL SELECT 'STONE_KNOT','C',1,0.800,'mm','Max 0.8 mm (0.031 in) in Area C' UNION ALL -- DIG — size limit SELECT 'DIG','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'DIG','B',1,2.400,'mm','Max 2.4 mm (0.093 in) in Area B' UNION ALL SELECT 'DIG','C',1,1.600,'mm','Max 1.6 mm (0.062 in) in Area C' UNION ALL -- INSIDE_DIRT — same limit both areas SELECT 'INSIDE_DIRT','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'INSIDE_DIRT','B',1,2.400,'mm','Max 2.4 mm (0.093 in)' UNION ALL SELECT 'INSIDE_DIRT','C',1,2.400,'mm','Max 2.4 mm (0.093 in)' UNION ALL -- REAM_STRING — angle-based SELECT 'REAM_STRING','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'REAM_STRING','B',1,30.000,'deg','Not evident at viewing angle > 30 degrees to glass surface, indirect daylight' UNION ALL SELECT 'REAM_STRING','C',1,30.000,'deg','Not evident at viewing angle > 30 degrees to glass surface, indirect daylight' UNION ALL -- SCRATCH — length limit (ASTM F428 grade 6 acceptable, 7 unacceptable) SELECT 'SCRATCH','A',0,NULL,NULL,'Scratches not allowed in concealed edge area; ASTM F428 grade > 6 unacceptable' UNION ALL SELECT 'SCRATCH','B',1,50.800,'mm','Max 2 inches (50.8 mm) in Area B; ASTM F428 grade 6 acceptable' UNION ALL SELECT 'SCRATCH','C',1,25.400,'mm','Max 1 inch (25.4 mm) in Area C; inside-surface scratches NOT permitted; ASTM F428 grade 6 acceptable' UNION ALL -- CRUSH — visibility distance SELECT 'CRUSH','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'CRUSH','B',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) with indirect daylight' UNION ALL SELECT 'CRUSH','C',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) with indirect daylight' UNION ALL -- LINT_HAIR — visibility distance (different per area) SELECT 'LINT_HAIR','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'LINT_HAIR','B',1,0.900,'m','May be noticeable at 0.9 m (3 ft) through patterned background — borderline acceptable' UNION ALL SELECT 'LINT_HAIR','C',1,0.900,'m','Barely noticeable at 0.9 m (3 ft) looking through patterned background, indirect daylight' UNION ALL -- ORGANIC — visibility distance SELECT 'ORGANIC','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'ORGANIC','B',1,0.900,'m','Shall not be detectable at distances > 0.9 m (3 ft) looking through perpendicular, indirect daylight' UNION ALL SELECT 'ORGANIC','C',1,0.900,'m','Shall not be detectable at distances > 0.9 m (3 ft) looking through perpendicular, indirect daylight' UNION ALL -- SURFACE (catch-all) — visibility distance SELECT 'SURFACE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'SURFACE','B',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft)' UNION ALL SELECT 'SURFACE','C',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft)' UNION ALL -- STRIAE — visibility distance SELECT 'STRIAE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL SELECT 'STRIAE','B',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) looking perpendicular, indirect daylight' UNION ALL SELECT 'STRIAE','C',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) looking perpendicular, indirect daylight' ) AS x ON x.code = t.code ON DUPLICATE KEY UPDATE is_allowed = VALUES(is_allowed), limit_value = VALUES(limit_value), limit_unit = VALUES(limit_unit), note = VALUES(note); -- ============================================================================ -- Verification -- ============================================================================ -- -- SELECT t.code, t.name, t.assessment_type, t.requires_size_input, -- l.area_code, l.is_allowed, l.limit_value, l.limit_unit -- FROM ATPD_DEFECT_TYPES t -- JOIN ATPD_DEFECT_LIMITS l ON l.defect_type_id = t.id -- ORDER BY t.sort_order, l.area_code; -- -- Expect 14 defect types × 3 areas = 42 rows. Every defect has -- `is_allowed=0` for Area A. Sized defects (BUBBLE, OPEN_BUBBLE, -- STONE_KNOT, DIG, INSIDE_DIRT, SCRATCH) carry numeric limit_value; -- visibility / angle / not_allowed types carry NULL or contextual -- limit_value (the picker UI consumes assessment_type to decide -- whether to render the size input + tolerance hint). -- -- ============================================================================