New Product Launch (NPL) 7-Stage Patisserie & Beverage Lifecycle (STOP 3 · T1-10 · STAGE-GATE® PLM · 312 SKUs)

CREATE TABLE npl_skus (
    sku_id UUID PRIMARY KEY,
    sku_code VARCHAR(32) UNIQUE NOT NULL,
    name VARCHAR(140) NOT NULL,
    category VARCHAR(48) NOT NULL,
    fssai_dot VARCHAR(12) NOT NULL CHECK (fssai_dot IN ('VEG','EGG','NON_VEG')),
    stage_num SMALLINT NOT NULL DEFAULT 1 CHECK (stage_num BETWEEN 1 AND 7),
    target_price_inr NUMERIC(10,2) NOT NULL,
    sunset_replacement_sku_id UUID REFERENCES npl_skus(sku_id),
    menu_quadrant VARCHAR(16) DEFAULT 'PUZZLE' CHECK (menu_quadrant IN ('STAR','PLOWHORSE','PUZZLE','DOG')),
    status VARCHAR(24) NOT NULL DEFAULT 'IN_PIPELINE'
);

CREATE TABLE npl_subrecipe_bom (
    bom_line_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    tier_level VARCHAR(16) NOT NULL CHECK (tier_level IN ('1_RM','2_WIP','3_BATCH','4_FG')),
    bakers_pct NUMERIC(6,2),
    bake_loss_pct NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    trim_loss_pct NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    net_yield_pct NUMERIC(5,2) NOT NULL DEFAULT 100.00,
    packaging_cost_inr NUMERIC(10,2) NOT NULL DEFAULT 0.00,
    total_food_cost_pct NUMERIC(5,2) NOT NULL,
    cfo_override_approved BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT check_28pct_food_cost CHECK (total_food_cost_pct <= 28.00 OR cfo_override_approved = TRUE)
);

CREATE TABLE npl_food_safety_lab (
    lab_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id) UNIQUE,
    water_activity_aw NUMERIC(4,3) NOT NULL CHECK (water_activity_aw BETWEEN 0.100 AND 0.999),
    ph_level NUMERIC(4,2) NOT NULL,
    storage_class VARCHAR(20) NOT NULL CHECK (storage_class IN ('AMBIENT','CHILLED_2_4C','FROZEN_18C')),
    big9_allergens_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    CONSTRAINT check_aw_cold_chain CHECK (water_activity_aw <= 0.850 OR storage_class IN ('CHILLED_2_4C','FROZEN_18C'))
);

CREATE TABLE npl_sensory_decay_curve (
    panel_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    panellist_count SMALLINT NOT NULL CHECK (panellist_count >= 5),
    mean_hedonic_score NUMERIC(3,2) NOT NULL CHECK (mean_hedonic_score >= 4.00),
    decay_t4h_ok BOOLEAN NOT NULL,
    decay_t12h_ok BOOLEAN NOT NULL,
    decay_t24h_ok BOOLEAN NOT NULL,
    decay_t48h_ok BOOLEAN NOT NULL,
    decay_t72h_ok BOOLEAN NOT NULL,
    approved_shelf_life_hours SMALLINT NOT NULL
);

CREATE TABLE npl_packaging_stress_test (
    stress_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id) UNIQUE,
    reefer_shock_2_4c_pass BOOLEAN NOT NULL,
    bike_30min_vibration_pass BOOLEAN NOT NULL,
    tilt_25deg_braking_pass BOOLEAN NOT NULL,
    lid_smear_zero BOOLEAN NOT NULL,
    board_slide_mm NUMERIC(4,1) NOT NULL CHECK (board_slide_mm <= 2.0)
);

CREATE TABLE npl_post_launch_cohort (
    cohort_id UUID PRIMARY KEY,
    sku_id UUID NOT NULL REFERENCES npl_skus(sku_id),
    window_tag VARCHAR(16) NOT NULL CHECK (window_tag IN ('CANARY_14D','DAY_30','DAY_60','DAY_90')),
    sell_through_pct NUMERIC(5,2) NOT NULL,
    dump_wastage_pct NUMERIC(5,2) NOT NULL,
    p95_ticket_sla_sec SMALLINT NOT NULL,
    actual_cogs_pct NUMERIC(5,2) NOT NULL,
    cohort_verdict VARCHAR(24) NOT NULL CHECK (cohort_verdict IN ('PROMOTE_TO_CORE','HOLD_REFORMULATE','SUNSET_KILL_SWITCH'))
);

-- 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_npl_lifecycle.py` — 100% green across 7-stage progression, 4-tier BOM yield rollup, `a_w > 0.85` cold-chain lock, `< 4.0` sensory block, 30-min bike tilt packaging gate, `> 28%` COGS block, and 14d canary / 30-60-90d kill-switch.