Stop 1 Detailed Design, TDD & UI/UX Spec: Standalone Legal & Compliance App (STOP 1 · T1-8A/B · T1-9 · O-038 · SK-1..15 · TC-1..14)

-- Stop 1 Detailed Design DDL: 8 Core Compliance & Legal Objects (migrations/004_compliance_legal_standalone.sql)
CREATE SCHEMA IF NOT EXISTS compliance;

CREATE TABLE compliance.legal_entities (
    entity_code VARCHAR(32) PRIMARY KEY,
    legal_name VARCHAR(160) NOT NULL,
    cin VARCHAR(32) NOT NULL UNIQUE,
    gstin VARCHAR(20) NOT NULL,
    pan VARCHAR(16) NOT NULL,
    registered_address TEXT NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE'
);

CREATE TABLE compliance.s3_vault_assets (
    vault_asset_id UUID PRIMARY KEY,
    s3_key VARCHAR(512) NOT NULL UNIQUE,
    sha256_hex CHAR(64) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size_bytes BIGINT NOT NULL,
    lifecycle_state VARCHAR(24) NOT NULL DEFAULT 'ACTIVE'
        CHECK (lifecycle_state IN ('ACTIVE','EXPIRING_SOON','EXPIRED','SUPERSEDED','RECYCLED')),
    superseded_by_asset_id UUID REFERENCES compliance.s3_vault_assets(vault_asset_id),
    uploaded_by VARCHAR(80) NOT NULL,
    uploaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE compliance.outlet_profiles (
    outlet_code VARCHAR(32) PRIMARY KEY,
    entity_code VARCHAR(32) NOT NULL REFERENCES compliance.legal_entities(entity_code),
    outlet_format VARCHAR(24) NOT NULL CHECK (outlet_format IN ('CAFE','COMMISSARY','KIOSK','HQ')),
    inspector_mode_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    overall_health VARCHAR(16) NOT NULL DEFAULT 'GOOD' CHECK (overall_health IN ('GOOD','CHECK','ACT_NOW'))
);

CREATE TABLE compliance.requirement_templates (
    category_code VARCHAR(40) PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    is_launch_blocker BOOLEAN NOT NULL DEFAULT FALSE,
    renewal_lead_days INT NOT NULL DEFAULT 90
);

CREATE TABLE compliance.licence_records (
    licence_id UUID PRIMARY KEY,
    outlet_code VARCHAR(32) NOT NULL REFERENCES compliance.outlet_profiles(outlet_code),
    category_code VARCHAR(40) NOT NULL REFERENCES compliance.requirement_templates(category_code),
    licence_no VARCHAR(128) NOT NULL,
    issuing_authority VARCHAR(120) NOT NULL,
    valid_from DATE NOT NULL,
    valid_until DATE NOT NULL,
    radar_bucket VARCHAR(16) NOT NULL DEFAULT 'VALID'
        CHECK (radar_bucket IN ('VALID','90D','60D','30D','15D','7D','EXPIRED')),
    vault_asset_id UUID NOT NULL REFERENCES compliance.s3_vault_assets(vault_asset_id),
    UNIQUE (outlet_code, category_code, licence_no)
);

CREATE TABLE compliance.trademark_ip_records (
    tm_id UUID PRIMARY KEY,
    entity_code VARCHAR(32) NOT NULL REFERENCES compliance.legal_entities(entity_code),
    application_no VARCHAR(64) NOT NULL UNIQUE,
    mark_title VARCHAR(160) NOT NULL,
    nice_class VARCHAR(16) NOT NULL,
    valid_until DATE NOT NULL,
    registry_status VARCHAR(32) NOT NULL DEFAULT 'REGISTERED',
    vault_asset_id UUID NOT NULL REFERENCES compliance.s3_vault_assets(vault_asset_id)
);

CREATE TABLE compliance.commercial_leases (
    lease_id UUID PRIMARY KEY,
    outlet_code VARCHAR(32) NOT NULL REFERENCES compliance.outlet_profiles(outlet_code),
    landlord_name VARCHAR(160) NOT NULL,
    monthly_rent_inr NUMERIC(12,2) NOT NULL,
    security_deposit_inr NUMERIC(12,2) NOT NULL,
    lock_in_until DATE,
    escalation_pct NUMERIC(5,2) NOT NULL DEFAULT 15.00,
    next_escalation_date DATE,
    lease_end_date DATE NOT NULL,
    vault_asset_id UUID NOT NULL REFERENCES compliance.s3_vault_assets(vault_asset_id)
);

CREATE TABLE compliance.legal_notice_cases (
    case_id UUID PRIMARY KEY,
    case_ref_no VARCHAR(64) NOT NULL UNIQUE,
    outlet_code VARCHAR(32) REFERENCES compliance.outlet_profiles(outlet_code),
    authority_or_forum VARCHAR(160) NOT NULL,
    severity VARCHAR(24) NOT NULL CHECK (severity IN ('P0_SHOW_CAUSE','P1_NOTICE','P2_QUERY')),
    hearing_or_reply_due DATE NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'OPEN_ACTION_REQUIRED',
    vault_asset_id UUID NOT NULL REFERENCES compliance.s3_vault_assets(vault_asset_id)
);

Automated Gate Evidence: Intake Approved by Owner (19:33 IST) -> Detailed 8-Object Schema, 22-Test Fail-First TDD Spec (tests/unit/test_t1_8_compliance.py + Playwright 320..1440px), and SK-1..15 2-Screen UI/UX Spec ready for Owner Sign-Off.