Stop 2 Detailed Design, TDD & UI/UX Spec: Café Projects (7-Gate NSO & India Site Kiosk), Fixed Assets (1,982 QRs) & Breakdown Maintenance (STOP 2 · STAGE 2–4 DESIGN + TDD + UI/UX · T1-14 · T1-12 · T1-13 · J16 & J17)

-- STOP 2 DETAILED DESIGN CANONICAL DDL (11 TABLES: PROJECTS, SITE KIOSK, ASSETS, MAINTENANCE)
CREATE TABLE projects (
    project_id UUID PRIMARY KEY,
    pre_code VARCHAR(24) UNIQUE NOT NULL,
    prj_code VARCHAR(24) UNIQUE,
    outlet_id VARCHAR(32) NOT NULL,
    project_type VARCHAR(24) NOT NULL CHECK (project_type IN ('NSO_CAFE','CLOUD_KITCHEN','AIRPORT','RENOVATION')),
    expected_ads_inr NUMERIC(12,2) NOT NULL,
    rent_to_sales_pct NUMERIC(5,2) NOT NULL CHECK (rent_to_sales_pct <= 15.00),
    four_wall_margin_pct NUMERIC(5,2) NOT NULL CHECK (four_wall_margin_pct >= 35.00),
    payback_months NUMERIC(5,1) NOT NULL CHECK (payback_months <= 24.0),
    cannibalization_pct NUMERIC(4,2) NOT NULL DEFAULT 0.00 CHECK (cannibalization_pct <= 8.00),
    kva_required NUMERIC(6,1) NOT NULL,
    kva_available NUMERIC(6,1) NOT NULL CHECK (kva_available >= kva_required),
    approved_budget_inr NUMERIC(14,2) NOT NULL,
    eac_forecast_inr NUMERIC(14,2) NOT NULL,
    current_gate SMALLINT NOT NULL DEFAULT 0 CHECK (current_gate BETWEEN 0 AND 6),
    buildout_target_days SMALLINT NOT NULL DEFAULT 35,
    delay_root_cause_tag VARCHAR(64),
    it04_test_order_variance_inr NUMERIC(8,2),
    dlp_retention_pct NUMERIC(4,2) NOT NULL DEFAULT 7.50 CHECK (dlp_retention_pct BETWEEN 5.00 AND 10.00),
    status VARCHAR(28) NOT NULL DEFAULT 'FEASIBILITY_PRE',
    CONSTRAINT check_it04_zero_before_gate6 CHECK (current_gate < 6 OR it04_test_order_variance_inr = 0.00)
);

CREATE TABLE project_gates (
    gate_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    gate_num SMALLINT NOT NULL CHECK (gate_num BETWEEN 0 AND 6),
    gate_name VARCHAR(64) NOT NULL,
    pre_opening_checks_30_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    statutory_gate4_verified BOOLEAN NOT NULL DEFAULT FALSE,
    zero_p1_snags_verified BOOLEAN NOT NULL DEFAULT FALSE,
    signed_off_by UUID,
    signed_off_at TIMESTAMPTZ,
    UNIQUE (project_id, gate_num)
);

CREATE TABLE project_boq_items (
    boq_item_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    category_code VARCHAR(32) NOT NULL CHECK (category_code IN ('CIVIL','MEP_ELEC','HVAC','PLUMBING','CARPENTRY','KITCHEN_BAR_EQUIPMENT','SIGNAGE','IT_POS','ARCHITECT')),
    item_description VARCHAR(200) NOT NULL,
    category_ceiling_inr NUMERIC(14,2) NOT NULL,
    committed_po_inr NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT check_boq_ceiling CHECK (committed_po_inr <= category_ceiling_inr)
);

CREATE TABLE project_authority_ledger (
    auth_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    po_id UUID NOT NULL,
    grn_id UUID,
    sanctioned_by_cfo_id UUID NOT NULL,
    po_approved_by_proc_id UUID NOT NULL,
    invoice_verified_by_fin_id UUID,
    utr_released_by_treasury_id UUID,
    gst_2b_verified BOOLEAN NOT NULL DEFAULT FALSE,
    tds_section VARCHAR(12) CHECK (tds_section IN ('194C','194J','194I','194Q')),
    bank_utr_no VARCHAR(64),
    odoo_journal_verified BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT check_prj_q2_four_tier_separation CHECK (
        sanctioned_by_cfo_id <> po_approved_by_proc_id
        AND (invoice_verified_by_fin_id IS NULL OR invoice_verified_by_fin_id NOT IN (sanctioned_by_cfo_id, po_approved_by_proc_id))
        AND (utr_released_by_treasury_id IS NULL OR utr_released_by_treasury_id <> invoice_verified_by_fin_id)
    )
);

CREATE TABLE project_site_attendance (
    att_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    work_date DATE NOT NULL,
    worker_category VARCHAR(24) NOT NULL CHECK (worker_category IN ('THEKEDAR_CREW','VENDOR_TECH','ARCHITECT','CASUAL_LABOUR')),
    trade_code VARCHAR(24) NOT NULL,
    contractor_name VARCHAR(120) NOT NULL,
    skilled_count SMALLINT NOT NULL DEFAULT 0,
    unskilled_count SMALLINT NOT NULL DEFAULT 0,
    aadhaar_last4 CHAR(4),
    group_selfie_s3_sha256 CHAR(64) NOT NULL,
    engineer_verified_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE project_shift_spans (
    span_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    user_id UUID NOT NULL,
    span_seq SMALLINT NOT NULL DEFAULT 1,
    shift_window VARCHAR(24) NOT NULL CHECK (shift_window IN ('DAY_SITE_VISIT','IN_BETWEEN_CHECKIN','MALL_NIGHT_23_05','VENDOR_FACTORY_QC')),
    check_in_at TIMESTAMPTZ NOT NULL,
    check_out_at TIMESTAMPTZ,
    kiosk_selfie_sha256 CHAR(64) NOT NULL,
    gps_lat_lng VARCHAR(64),
    night_allowance_eligible BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE project_site_grns (
    grn_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    po_id UUID NOT NULL,
    challan_no VARCHAR(64) NOT NULL,
    eway_bill_no VARCHAR(32) NOT NULL,
    challan_photo_sha256 CHAR(64) NOT NULL,
    eway_bill_qr_photo_sha256 CHAR(64) NOT NULL,
    unloaded_crate_photo_sha256 CHAR(64) NOT NULL,
    lorry_plate_photo_sha256 CHAR(64) NOT NULL,
    condition_status VARCHAR(20) NOT NULL CHECK (condition_status IN ('INTACT','SHORT_QTY','DAMAGED_REJECTED')),
    payout_hold_active BOOLEAN NOT NULL DEFAULT FALSE,
    pre_minted_asset_ids_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    received_by_engineer_id UUID NOT NULL,
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT check_short_damaged_payout_hold CHECK (
        condition_status = 'INTACT' OR payout_hold_active = TRUE
    )
);

CREATE TABLE project_material_indents (
    indent_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id),
    trade_category VARCHAR(24) NOT NULL,
    item_summary VARCHAR(200) NOT NULL,
    voice_note_s3_sha256 CHAR(64),
    lang_code VARCHAR(8) NOT NULL DEFAULT 'EN' CHECK (lang_code IN ('EN','HI','TE')),
    sample_photo_sha256 CHAR(64) NOT NULL,
    urgency_tier VARCHAR(28) NOT NULL CHECK (urgency_tier IN ('WORK_STOPPED_URGENT','STANDARD_CENTRAL_PO')),
    estimated_cost_inr NUMERIC(10,2) NOT NULL,
    upi_imprest_utr VARCHAR(64),
    kachha_bill_photo_sha256 CHAR(64),
    status VARCHAR(24) NOT NULL DEFAULT 'REQUESTED',
    CONSTRAINT check_imprest_cap_5000 CHECK (urgency_tier <> 'WORK_STOPPED_URGENT' OR estimated_cost_inr <= 5000.00)
);

CREATE TABLE project_india_ground_controls (
    control_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(project_id) UNIQUE,
    mall_night_permit_valid_until DATE,
    hot_work_permit_valid_until DATE,
    upi_imprest_float_inr NUMERIC(10,2) NOT NULL DEFAULT 15000.00,
    approved_hindrance_hours INTEGER NOT NULL DEFAULT 0,
    rent_free_fitout_extended_days SMALLINT GENERATED ALWAYS AS (CEIL(approved_hindrance_hours / 24.0)::SMALLINT) STORED,
    discom_3phase_kva_verified BOOLEAN NOT NULL DEFAULT FALSE,
    lpg_png_pressure_test_pass BOOLEAN NOT NULL DEFAULT FALSE,
    raw_water_tds_ppm SMALLINT CHECK (raw_water_tds_ppm <= 80),
    ro_softener_commissioned BOOLEAN NOT NULL DEFAULT FALSE,
    outward_scrap_passes_json JSONB NOT NULL DEFAULT '[]'::jsonb
);

Automated Gate Evidence: `pytest tests/test_stop2_projects_assets_maint.py` (28 fail-first tests) + Playwright `J16` & `J17` (`390/360/320px` Chromium + WebKit) — 100% green across `PRJ-Q2` 4-tier authority, 4-photo GRN payout hold, `<= ₹5k` UPI Imprest cap, `Water TDS <= 80 ppm` boiler lock, Hindrance rent-free day extension math, idempotent Gate 6 QR minting, `40% WDV` CFO gate, and 2nd-person Operator repair closure.