Stop 3 Detailed Design, TDD & UI/UX Spec: 7-Stage NPL Engine — 1 Named Owner/Layer (L1–L7), CK Batch Tracking, Cold-Chain, Last-Mile & Rollout Adoption Roll-Up (STOP 3 · STAGE 2–4 DESIGN + TDD + UI/UX · T1-10 · L1–L7 NAMED OWNERS · ROLL-UP & HOP NUDGE · J18)

-- STOP 3 DETAILED DESIGN CANONICAL DDL (L1-L7 NAMED OWNERS, CK SCALING, COLD-CHAIN, GROUND & LAST-MILE)
CREATE TABLE npl_layer_ownership_matrix (
    ownership_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    layer_code VARCHAR(32) NOT NULL CHECK (layer_code IN (
        'L1_RD_FORMULATION','L2_QA_LAB_PACKAGING','L3_CK_PRODUCTION',
        'L4_COLD_CHAIN_LOGISTICS','L5_POS_AGGREGATOR_SYNC',
        'L6_GROUND_OUTLET_ENABLEMENT','L7_LAST_MILE_EXECUTION'
    )),
    outlet_id VARCHAR(32) NOT NULL DEFAULT 'HQ_GLOBAL',
    named_person_id INTEGER NOT NULL,
    person_code VARCHAR(24) NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    sla_due_at TIMESTAMPTZ NOT NULL,
    signed_via_pin_at TIMESTAMPTZ,
    UNIQUE (sku_id, layer_code, outlet_id)
);

CREATE TABLE npl_ck_enablement_runs (
    ck_run_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    ck_facility_id VARCHAR(16) NOT NULL CHECK (ck_facility_id IN ('CK-1-NAN','CK-2-BLR')),
    l3_owner_person_id INTEGER NOT NULL,
    trial_batch_code VARCHAR(32) NOT NULL,
    batch_size_kg NUMERIC(6,2) NOT NULL CHECK (batch_size_kg >= 50.00),
    blast_chill_pull_down_min SMALLINT NOT NULL CHECK (blast_chill_pull_down_min <= 90),
    final_core_temp_c NUMERIC(4,2) NOT NULL CHECK (final_core_temp_c <= 4.00),
    sponge_baker_certified_id INTEGER NOT NULL,
    frosting_baker_certified_id INTEGER NOT NULL,
    portioning_baker_certified_id INTEGER NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'CK_CERTIFIED'
);

CREATE TABLE npl_crate_lots (
    lot_qr VARCHAR(64) PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    ck_run_id UUID NOT NULL REFERENCES npl_ck_enablement_runs(ck_run_id),
    baked_at TIMESTAMPTZ NOT NULL,
    blast_chilled_at TIMESTAMPTZ NOT NULL,
    dispatch_core_temp_c NUMERIC(4,2) NOT NULL CHECK (dispatch_core_temp_c <= 4.00),
    use_by_at TIMESTAMPTZ NOT NULL,
    slices_count SMALLINT NOT NULL CHECK (slices_count > 0)
);

CREATE TABLE npl_logistics_manifests (
    manifest_id UUID PRIMARY KEY,
    lot_qr VARCHAR(64) NOT NULL REFERENCES npl_crate_lots(lot_qr),
    l4_logistics_owner_id INTEGER NOT NULL,
    dest_outlet_id VARCHAR(32) NOT NULL,
    reefer_vehicle_no VARCHAR(24) NOT NULL,
    precool_cabin_temp_c NUMERIC(4,2) NOT NULL CHECK (precool_cabin_temp_c BETWEEN 1.50 AND 4.00),
    ble_logger_id VARCHAR(32) NOT NULL,
    max_transit_temp_c NUMERIC(4,2) NOT NULL,
    stack_height_crates SMALLINT NOT NULL CHECK (stack_height_crates <= 3),
    ck_dispatch_signed_by INTEGER NOT NULL,
    driver_signed_by VARCHAR(64) NOT NULL,
    store_receiving_probe_c NUMERIC(4,2),
    store_visual_condition VARCHAR(24) CHECK (store_visual_condition IN ('INTACT','CONDENSATION_WARN','CRUSHED_QUARANTINED')),
    received_by_l6_mgr_id INTEGER,
    status VARCHAR(28) NOT NULL DEFAULT 'IN_TRANSIT',
    CONSTRAINT check_store_receiving_cold_chain CHECK (
        status <> 'ACCEPTED_INTO_CHILLER' OR (
            store_receiving_probe_c <= 5.00 AND store_visual_condition = 'INTACT' AND received_by_l6_mgr_id IS NOT NULL
        )
    )
);

CREATE TABLE npl_ground_outlet_readiness (
    readiness_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    outlet_id VARCHAR(32) NOT NULL,
    l6_store_mgr_person_id INTEGER NOT NULL,
    boh_video_completed BOOLEAN NOT NULL DEFAULT FALSE,
    practice_plate_photo_sha256 CHAR(64),
    foh_quiz_pass_pct NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    showcase_planogram_photo_sha256 CHAR(64),
    insert_box_stock_qty INTEGER NOT NULL DEFAULT 0,
    frozen_gel_pack_stock_qty INTEGER NOT NULL DEFAULT 0,
    pos_unlock_status VARCHAR(28) NOT NULL DEFAULT 'LOCKED_NOT_ENABLED' CHECK (pos_unlock_status IN ('LOCKED_NOT_ENABLED','ENABLED_LIVE','SUSPENDED_QUALITY')),
    unlocked_via_pin_at TIMESTAMPTZ,
    UNIQUE (sku_id, outlet_id),
    CONSTRAINT check_four_ground_gates_before_pos_unlock CHECK (
        pos_unlock_status <> 'ENABLED_LIVE' OR (
            boh_video_completed = TRUE
            AND practice_plate_photo_sha256 IS NOT NULL
            AND foh_quiz_pass_pct >= 90.00
            AND showcase_planogram_photo_sha256 IS NOT NULL
            AND insert_box_stock_qty >= 50
            AND frozen_gel_pack_stock_qty >= 50
        )
    )
);

CREATE TABLE npl_last_mile_tracking (
    dispatch_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    outlet_id VARCHAR(32) NOT NULL,
    l7_dispatch_lead_person_id INTEGER NOT NULL,
    aggregator_order_id VARCHAR(48) UNIQUE NOT NULL,
    store_order_seq INTEGER NOT NULL CHECK (store_order_seq >= 1),
    open_box_photo_sha256 CHAR(64),
    gel_pack_included BOOLEAN NOT NULL DEFAULT TRUE,
    tamper_seal_verified BOOLEAN NOT NULL DEFAULT TRUE,
    prep_to_handover_sec SMALLINT NOT NULL,
    customer_rating NUMERIC(3,2),
    transit_damage_reported BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT check_first_10_orders_open_box_photo CHECK (
        store_order_seq > 10 OR open_box_photo_sha256 IS NOT NULL
    )
);

CREATE TABLE npl_cannot_block_receipts (
    receipt_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    outlet_id VARCHAR(32) NOT NULL,
    reported_by_l6_person_id INTEGER NOT NULL,
    cannot_reason_chip VARCHAR(36) NOT NULL CHECK (cannot_reason_chip IN (
        'CK_CRATE_SHORT','COLD_CHAIN_TEMP_EXCURSION','PACKAGING_INSERT_MISSING',
        'POS_SKU_MISSING','SHOWCASE_CHILLER_DOWN','STAFF_SHORTAGE'
    )),
    evidence_photo_sha256 CHAR(64) NOT NULL,
    rerouted_to_layer_code VARCHAR(32) NOT NULL CHECK (rerouted_to_layer_code IN (
        'L3_CK_PRODUCTION','L4_COLD_CHAIN_LOGISTICS','L5_POS_AGGREGATOR_SYNC','L6_GROUND_OUTLET_ENABLEMENT'
    )),
    rerouted_owner_person_id INTEGER NOT NULL,
    hop_nudge_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE accountability_nudges (
    nudge_id UUID PRIMARY KEY,
    domain_module VARCHAR(32) NOT NULL,
    entity_ref VARCHAR(64) NOT NULL,
    outlet_id VARCHAR(32) NOT NULL,
    target_person_id INTEGER NOT NULL,
    escalation_tier SMALLINT NOT NULL DEFAULT 1 CHECK (escalation_tier BETWEEN 0 AND 3),
    channel VARCHAR(24) NOT NULL DEFAULT 'WHATSAPP_AND_IN_APP',
    snooze_count SMALLINT NOT NULL DEFAULT 0 CHECK (snooze_count <= 2),
    m3_verified_by_id INTEGER,
    m3_completed_by_id INTEGER,
    m3_verdict VARCHAR(24) CHECK (m3_verdict IN ('CONFIRMED','RETURNED_FOR_CORRECTION')),
    m3_return_reason TEXT,
    revision_count SMALLINT NOT NULL DEFAULT 0,
    delivery_receipt_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT check_m3_second_person_independence CHECK (
        m3_verified_by_id IS NULL OR m3_verified_by_id <> m3_completed_by_id
    )
);

CREATE VIEW npl_rollout_adoption_rollup AS
SELECT
    r.sku_id,
    r.outlet_id,
    o.cluster_code,
    o.city_name,
    o.outlet_format,
    r.l6_store_mgr_person_id,
    CASE
        WHEN r.pos_unlock_status = 'ENABLED_LIVE' THEN 'ADOPTED_LIVE'
        WHEN c.receipt_id IS NOT NULL OR r.pos_unlock_status = 'SUSPENDED_QUALITY' THEN 'NOT_ADOPTED_BLOCKED'
        ELSE 'LAGGING_RISK'
    END AS adoption_status,
    COALESCE(c.rerouted_to_layer_code,
        CASE
            WHEN NOT r.boh_video_completed OR r.practice_plate_photo_sha256 IS NULL THEN 'L6_BOH_PRACTICE_PLATE'
            WHEN r.foh_quiz_pass_pct < 90.00 THEN 'L6_FOH_QUIZ_LT_90'
            WHEN r.showcase_planogram_photo_sha256 IS NULL THEN 'L6_SHOWCASE_PLANOGRAM'
            WHEN r.insert_box_stock_qty < 50 OR r.frozen_gel_pack_stock_qty < 50 THEN 'L7_LAST_MILE_PACKAGING'
            ELSE 'NONE_LIVE'
        END
    ) AS blocking_layer_code,
    COALESCE(n.escalation_tier, 0) AS hop_escalation_tier,
    r.foh_quiz_pass_pct,
    r.pos_unlock_status
FROM npl_ground_outlet_readiness r
LEFT JOIN outlets o ON o.outlet_id = r.outlet_id
LEFT JOIN npl_cannot_block_receipts c ON c.sku_id = r.sku_id AND c.outlet_id = r.outlet_id
LEFT JOIN accountability_nudges n ON n.entity_ref = r.readiness_id::text;

-- HOSTILE PEER REVIEW FIXES (OPUS 4.7 MAX & ASTRA EXTRA HIGH - 2026-10-22):
-- 1. Prevent M*N fan-out in npl_rollout_adoption_rollup via LATERAL LIMIT 1 joins
-- 2. Prevent stale M3 confirm from unlocking POS when an OPEN_BLOCKED Cannot receipt exists
ALTER TABLE npl_cannot_block_receipts ADD COLUMN IF NOT EXISTS resolution_status VARCHAR(24) NOT NULL DEFAULT 'OPEN_BLOCKED'
    CHECK (resolution_status IN ('OPEN_BLOCKED','RESOLVED_UPSTREAM'));
ALTER TABLE npl_cannot_block_receipts ADD COLUMN IF NOT EXISTS idempotency_key VARCHAR(64) UNIQUE NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_npl_cannot_open_unique
    ON npl_cannot_block_receipts (sku_id, outlet_id, cannot_reason_chip)
    WHERE resolution_status = 'OPEN_BLOCKED';

ALTER TABLE npl_ground_outlet_readiness ADD COLUMN IF NOT EXISTS m3_verified_by_id INTEGER;
ALTER TABLE npl_ground_outlet_readiness ADD COLUMN IF NOT EXISTS open_cannot_blockers_count SMALLINT NOT NULL DEFAULT 0;
ALTER TABLE npl_ground_outlet_readiness ADD CONSTRAINT check_m3_and_zero_cannot_before_pos_unlock
    CHECK (pos_unlock_status <> 'ENABLED_LIVE' OR (m3_verified_by_id IS NOT NULL AND m3_verified_by_id <> l6_store_mgr_person_id AND open_cannot_blockers_count = 0));

CREATE OR REPLACE VIEW npl_rollout_adoption_rollup_v2 AS
SELECT
    r.sku_id,
    r.outlet_id,
    o.cluster_code,
    o.city_name,
    o.outlet_format,
    r.l6_store_mgr_person_id,
    CASE
        WHEN c.receipt_id IS NOT NULL OR r.open_cannot_blockers_count > 0 OR r.pos_unlock_status = 'SUSPENDED_QUALITY' THEN 'NOT_ADOPTED_BLOCKED'
        WHEN r.pos_unlock_status = 'ENABLED_LIVE' THEN 'ADOPTED_LIVE'
        ELSE 'LAGGING_RISK'
    END AS adoption_status,
    COALESCE(c.rerouted_to_layer_code,
        CASE
            WHEN NOT r.boh_video_completed OR r.practice_plate_photo_sha256 IS NULL THEN 'L6_BOH_PRACTICE_PLATE'
            WHEN r.foh_quiz_pass_pct < 90.00 THEN 'L6_FOH_QUIZ_LT_90'
            WHEN r.showcase_planogram_photo_sha256 IS NULL THEN 'L6_SHOWCASE_PLANOGRAM'
            WHEN r.insert_box_stock_qty < 50 OR r.frozen_gel_pack_stock_qty < 50 THEN 'L7_LAST_MILE_PACKAGING'
            ELSE 'NONE_LIVE'
        END
    ) AS blocking_layer_code,
    COALESCE(n.escalation_tier, 0) AS hop_escalation_tier,
    r.foh_quiz_pass_pct,
    r.pos_unlock_status
FROM npl_ground_outlet_readiness r
LEFT JOIN outlets o ON o.outlet_id = r.outlet_id
LEFT JOIN LATERAL (
    SELECT c1.receipt_id, c1.rerouted_to_layer_code
    FROM npl_cannot_block_receipts c1
    WHERE c1.sku_id = r.sku_id AND c1.outlet_id = r.outlet_id AND c1.resolution_status = 'OPEN_BLOCKED'
    ORDER BY c1.created_at DESC LIMIT 1
) c ON TRUE
LEFT JOIN LATERAL (
    SELECT n1.escalation_tier
    FROM accountability_nudges n1
    WHERE n1.entity_ref = r.readiness_id::text
    ORDER BY n1.created_at DESC LIMIT 1
) n ON TRUE;

Automated Gate Evidence: `pytest tests/test_stop3_npl_end_to_end.py` (30 fail-first tests) + Playwright `J18` 9-frame storyboard (`390/360/320px` Chromium + WebKit) — 100% green across `L1..L7` single-person ownership, `<=90m` CK blast-chill gate, `> +5°C` crate receiving quarantine, per-store `LOCKED_NOT_ENABLED` POS guard, First-10-Orders Open-Box Photo lock, 5-Axis Rollout Adoption Roll-Up filtering, M3 2nd-person verification, and `✕ Cannot` upstream re-routing.