Café Projects, Site Feasibility, 7-Gate NSO/CapEx & India Ground Site Kiosk Operations (STOP 2A · BO-PACK-PROJECTS · T1-9C · T1-14 · POWERPLAY / ONSITE / PROCORE BENCHMARK)

CREATE TABLE projects_capex (
    project_id UUID PRIMARY KEY,
    pre_code VARCHAR(24) UNIQUE NOT NULL,
    prj_code VARCHAR(24) UNIQUE,
    outlet_name VARCHAR(120) 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),
    payback_months NUMERIC(5,1) NOT NULL CHECK (payback_months <= 24.0),
    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),
    it04_test_order_variance_inr NUMERIC(8,2) DEFAULT NULL,
    dlp_retention_pct NUMERIC(4,2) NOT NULL DEFAULT 7.50 CHECK (dlp_retention_pct BETWEEN 5.00 AND 10.00),
    status VARCHAR(24) NOT NULL DEFAULT 'FEASIBILITY_PRE',
    CONSTRAINT check_it04_zero_variance_before_gate6 CHECK (current_gate < 6 OR it04_test_order_variance_inr = 0.00)
);

CREATE TABLE project_site_attendance (
    att_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects_capex(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 CHECK (trade_code IN ('CIVIL','CARPENTRY','MEP_ELEC','HVAC','PLUMBING','PAINTING','UNLOADING','SUPERVISION')),
    contractor_name VARCHAR(120) NOT NULL,
    skilled_count SMALLINT NOT NULL DEFAULT 0,
    unskilled_count SMALLINT NOT NULL DEFAULT 0,
    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_capex(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)
);

CREATE TABLE project_site_grns (
    grn_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects_capex(project_id),
    po_id UUID NOT NULL,
    challan_no VARCHAR(64) NOT NULL,
    eway_bill_no VARCHAR(32),
    challan_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')),
    received_by_engineer_id UUID NOT NULL,
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE project_material_indents (
    indent_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects_capex(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_IMPREST','STANDARD_CENTRAL_PO')),
    estimated_cost_inr NUMERIC(10,2) NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'REQUESTED'
);

CREATE TABLE project_india_ground_controls (
    control_id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects_capex(project_id) UNIQUE,
    mall_night_permit_valid_until DATE,
    hot_work_permit_valid_until DATE,
    hindrance_logged_days SMALLINT NOT NULL DEFAULT 0,
    rent_free_fitout_extended_days SMALLINT NOT NULL DEFAULT 0,
    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
);

Automated Gate Evidence: `pytest tests/test_projects_maint.py` — 100% green across `PRE->PRJ` feasibility gate, 4-tier `PRJ-Q2` sanction separation, Ad-Hoc `Thekedar` attendance, split/mall-night check-ins, 3-Way Visual Site GRN payment lock, voice/photo material indents, Water TDS `<=80 ppm` boiler gate, `IT-04` ₹0 variance, and idempotent Gate 6 `HRL-AST-*` QR minting.