erp-construccion/docs/04-modelado/database-design/schemas/DDL-SPEC-compliance.md

# DDL SPECIFICATION: Schema Compliance (INFONAVIT)

**Version:** 1.0.0
**Fecha:** 2025-12-05
**Schema:** `compliance`
**Modulos:** MAI-011 (INFONAVIT Cumplimiento)

---

## Resumen

| Metrica | Valor |
|---------|-------|
| Total Tablas | 10 |
| ENUMs | 6 |
| Funciones | 4 |
| Triggers | 3 |
| Indices | 25+ |

---

## 1. ENUMs

```sql
-- Tipos de programa INFONAVIT
CREATE TYPE compliance.program_type AS ENUM (
  'evc',              -- Esquemas de Valor Compartido
  'traditional',      -- Credito tradicional
  'cofinavit',        -- Credito mixto INFONAVIT + bancario
  'infonavit_total',  -- Credito con subsidio
  'apoyo_infonavit',  -- Solo subsidio
  'mejoravit',        -- Mejora de vivienda
  'unamos_creditos'   -- Creditos combinados
);

-- Categorias de requisito
CREATE TYPE compliance.requirement_category AS ENUM (
  'technical',       -- Requisitos tecnicos
  'urban',           -- Requisitos urbanos
  'services',        -- Requisitos de servicios
  'legal',           -- Requisitos legales
  'environmental'    -- Requisitos ambientales
);

-- Estados de requisito
CREATE TYPE compliance.requirement_status AS ENUM (
  'pending',         -- Pendiente
  'in_progress',     -- En proceso
  'compliant',       -- Cumple
  'non_compliant',   -- No cumple
  'not_applicable'   -- No aplica
);

-- Tipos de auditoria
CREATE TYPE compliance.audit_type AS ENUM (
  'initial',         -- Inicial
  'progress',        -- Avance
  'final',           -- Final
  'special',         -- Especial
  'random'           -- Aleatoria
);

-- Estados de auditoria
CREATE TYPE compliance.audit_status AS ENUM (
  'scheduled',       -- Programada
  'in_progress',     -- En curso
  'completed',       -- Completada
  'follow_up',       -- En seguimiento
  'cancelled'        -- Cancelada
);

-- Severidad de hallazgo
CREATE TYPE compliance.finding_severity AS ENUM (
  'minor',           -- Menor (30 dias para corregir)
  'major',           -- Mayor (15 dias para corregir)
  'critical'         -- Critico (7 dias para corregir)
);
```

---

## 2. Tablas

### 2.1 infonavit_programs (Programas INFONAVIT)

```sql
CREATE TABLE compliance.infonavit_programs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  code VARCHAR(20) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  program_type compliance.program_type NOT NULL,

  -- Vigencia
  valid_from DATE,
  valid_to DATE,

  -- Estado
  is_active BOOLEAN DEFAULT true,

  -- Metadata
  requirements_count INTEGER DEFAULT 0,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_programs_code UNIQUE (tenant_id, code)
);

CREATE INDEX idx_programs_tenant ON compliance.infonavit_programs(tenant_id);
CREATE INDEX idx_programs_type ON compliance.infonavit_programs(program_type);
CREATE INDEX idx_programs_active ON compliance.infonavit_programs(tenant_id) WHERE is_active = true;
```

### 2.2 program_requirements (Requisitos por Programa)

```sql
CREATE TABLE compliance.program_requirements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  program_id UUID NOT NULL REFERENCES compliance.infonavit_programs(id),

  -- Identificacion
  code VARCHAR(30) NOT NULL,
  name VARCHAR(300) NOT NULL,
  description TEXT,

  -- Clasificacion
  category compliance.requirement_category NOT NULL,
  subcategory VARCHAR(100),

  -- Evidencia requerida
  evidence_required TEXT[],  -- Lista de tipos de evidencia
  evidence_description TEXT,

  -- Orden y jerarquia
  sort_order INTEGER DEFAULT 0,
  parent_id UUID REFERENCES compliance.program_requirements(id),

  -- Estado
  is_mandatory BOOLEAN DEFAULT true,
  is_active BOOLEAN DEFAULT true,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),

  CONSTRAINT uq_requirements_code UNIQUE (program_id, code)
);

CREATE INDEX idx_requirements_program ON compliance.program_requirements(program_id);
CREATE INDEX idx_requirements_category ON compliance.program_requirements(category);
CREATE INDEX idx_requirements_parent ON compliance.program_requirements(parent_id);
```

### 2.3 project_programs (Proyectos en Programas)

```sql
CREATE TABLE compliance.project_programs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  project_id UUID NOT NULL REFERENCES construction.projects(id),
  program_id UUID NOT NULL REFERENCES compliance.infonavit_programs(id),

  -- Registro
  registration_number VARCHAR(50),
  registration_date DATE,

  -- Vigencia
  valid_from DATE,
  valid_to DATE,

  -- Estado de cumplimiento
  compliance_percentage DECIMAL(5,2) DEFAULT 0,
  total_requirements INTEGER DEFAULT 0,
  compliant_count INTEGER DEFAULT 0,
  non_compliant_count INTEGER DEFAULT 0,
  pending_count INTEGER DEFAULT 0,

  -- Estado
  status VARCHAR(20) DEFAULT 'active',

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_project_program UNIQUE (project_id, program_id)
);

CREATE INDEX idx_project_programs_project ON compliance.project_programs(project_id);
CREATE INDEX idx_project_programs_program ON compliance.project_programs(program_id);
```

### 2.4 compliance_items (Items de Cumplimiento)

```sql
CREATE TABLE compliance.compliance_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  project_program_id UUID NOT NULL REFERENCES compliance.project_programs(id),
  requirement_id UUID NOT NULL REFERENCES compliance.program_requirements(id),

  -- Estado
  status compliance.requirement_status NOT NULL DEFAULT 'pending',
  previous_status compliance.requirement_status,

  -- Verificacion
  verified_by UUID REFERENCES core.users(id),
  verified_at TIMESTAMP,
  verification_notes TEXT,

  -- Fechas limite
  due_date DATE,
  completed_date DATE,

  -- Notas
  notes TEXT,
  is_not_applicable BOOLEAN DEFAULT false,
  na_reason TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),

  CONSTRAINT uq_compliance_item UNIQUE (project_program_id, requirement_id)
);

CREATE INDEX idx_compliance_items_project_program ON compliance.compliance_items(project_program_id);
CREATE INDEX idx_compliance_items_requirement ON compliance.compliance_items(requirement_id);
CREATE INDEX idx_compliance_items_status ON compliance.compliance_items(status);
CREATE INDEX idx_compliance_items_pending ON compliance.compliance_items(project_program_id)
  WHERE status IN ('pending', 'in_progress');
```

### 2.5 compliance_evidence (Evidencias)

```sql
CREATE TABLE compliance.compliance_evidence (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  compliance_item_id UUID NOT NULL REFERENCES compliance.compliance_items(id),

  -- Archivo
  file_url TEXT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_size INTEGER,
  mime_type VARCHAR(100),
  thumbnail_url TEXT,

  -- Tipo de evidencia
  evidence_type VARCHAR(50),  -- document, photo, video, certificate
  description TEXT,

  -- Metadata
  taken_at TIMESTAMP,
  location GEOGRAPHY(POINT, 4326),
  metadata JSONB,

  -- Estado
  status VARCHAR(20) DEFAULT 'active',  -- active, replaced, rejected

  -- Revision
  reviewed_by UUID REFERENCES core.users(id),
  reviewed_at TIMESTAMP,
  review_notes TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP
);

CREATE INDEX idx_evidence_compliance_item ON compliance.compliance_evidence(compliance_item_id);
CREATE INDEX idx_evidence_type ON compliance.compliance_evidence(evidence_type);
CREATE INDEX idx_evidence_status ON compliance.compliance_evidence(status);
```

### 2.6 audits (Auditorias)

```sql
CREATE TABLE compliance.audits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  project_id UUID NOT NULL REFERENCES construction.projects(id),

  -- Identificacion
  audit_number VARCHAR(30) NOT NULL,
  audit_type compliance.audit_type NOT NULL,

  -- Programacion
  scheduled_date DATE NOT NULL,
  scheduled_time TIME,
  actual_date DATE,

  -- Auditor
  auditor_name VARCHAR(200),
  auditor_company VARCHAR(200),
  auditor_contact TEXT,

  -- Estado
  status compliance.audit_status NOT NULL DEFAULT 'scheduled',

  -- Resultados
  overall_compliance_percentage DECIMAL(5,2),
  findings_count INTEGER DEFAULT 0,
  critical_findings INTEGER DEFAULT 0,
  major_findings INTEGER DEFAULT 0,
  minor_findings INTEGER DEFAULT 0,

  -- Documentacion
  report_url TEXT,
  notes TEXT,

  -- Cierre
  closed_at TIMESTAMP,
  closed_by UUID REFERENCES core.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_audits_number UNIQUE (tenant_id, audit_number)
);

CREATE INDEX idx_audits_project ON compliance.audits(project_id);
CREATE INDEX idx_audits_status ON compliance.audits(status);
CREATE INDEX idx_audits_date ON compliance.audits(scheduled_date);
CREATE INDEX idx_audits_type ON compliance.audits(audit_type);
```

### 2.7 audit_findings (Hallazgos de Auditoria)

```sql
CREATE TABLE compliance.audit_findings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  audit_id UUID NOT NULL REFERENCES compliance.audits(id),
  compliance_item_id UUID REFERENCES compliance.compliance_items(id),

  -- Identificacion
  finding_number VARCHAR(30) NOT NULL,
  severity compliance.finding_severity NOT NULL,

  -- Descripcion
  title VARCHAR(300) NOT NULL,
  description TEXT NOT NULL,
  evidence_refs TEXT[],  -- Referencias a evidencias

  -- Requisito incumplido
  requirement_code VARCHAR(30),
  requirement_name VARCHAR(300),

  -- Responsable
  responsible_name VARCHAR(200),
  responsible_area VARCHAR(100),

  -- Fechas
  due_date DATE NOT NULL,
  resolution_date DATE,

  -- Estado
  status VARCHAR(20) DEFAULT 'open',  -- open, in_progress, resolved, verified, closed

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),

  CONSTRAINT uq_findings_number UNIQUE (audit_id, finding_number)
);

CREATE INDEX idx_findings_audit ON compliance.audit_findings(audit_id);
CREATE INDEX idx_findings_compliance_item ON compliance.audit_findings(compliance_item_id);
CREATE INDEX idx_findings_severity ON compliance.audit_findings(severity);
CREATE INDEX idx_findings_status ON compliance.audit_findings(status);
CREATE INDEX idx_findings_open ON compliance.audit_findings(audit_id) WHERE status NOT IN ('verified', 'closed');
```

### 2.8 corrective_actions (Acciones Correctivas)

```sql
CREATE TABLE compliance.corrective_actions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  finding_id UUID NOT NULL REFERENCES compliance.audit_findings(id),

  -- Identificacion
  action_number VARCHAR(30) NOT NULL,

  -- Accion
  description TEXT NOT NULL,
  action_type VARCHAR(50),  -- correction, prevention, improvement

  -- Asignacion
  assigned_to UUID REFERENCES core.users(id),
  assigned_to_name VARCHAR(200),
  assigned_at TIMESTAMP,

  -- Fechas
  due_date DATE NOT NULL,
  completed_date DATE,

  -- Estado
  status VARCHAR(20) DEFAULT 'pending',  -- pending, in_progress, completed, verified

  -- Verificacion
  verified_by UUID REFERENCES core.users(id),
  verified_at TIMESTAMP,
  verification_notes TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_corrective_finding ON compliance.corrective_actions(finding_id);
CREATE INDEX idx_corrective_assigned ON compliance.corrective_actions(assigned_to);
CREATE INDEX idx_corrective_status ON compliance.corrective_actions(status);
CREATE INDEX idx_corrective_due ON compliance.corrective_actions(due_date) WHERE status NOT IN ('completed', 'verified');
```

### 2.9 compliance_alerts (Alertas de Cumplimiento)

```sql
CREATE TABLE compliance.compliance_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  project_id UUID NOT NULL REFERENCES construction.projects(id),

  -- Tipo de alerta
  alert_type VARCHAR(50) NOT NULL,  -- requirement_due, audit_scheduled, finding_due, low_compliance
  severity VARCHAR(20) NOT NULL,    -- info, warning, critical

  -- Referencia
  reference_type VARCHAR(50),       -- compliance_item, audit, finding
  reference_id UUID,

  -- Contenido
  title VARCHAR(300) NOT NULL,
  message TEXT NOT NULL,

  -- Destinatarios
  recipients UUID[],

  -- Estado
  status VARCHAR(20) DEFAULT 'active',  -- active, acknowledged, dismissed
  acknowledged_by UUID REFERENCES core.users(id),
  acknowledged_at TIMESTAMP,

  -- Vigencia
  expires_at TIMESTAMP,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_alerts_project ON compliance.compliance_alerts(project_id);
CREATE INDEX idx_alerts_type ON compliance.compliance_alerts(alert_type);
CREATE INDEX idx_alerts_status ON compliance.compliance_alerts(status);
CREATE INDEX idx_alerts_active ON compliance.compliance_alerts(project_id) WHERE status = 'active';
```

### 2.10 compliance_history (Historial de Cambios)

```sql
CREATE TABLE compliance.compliance_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Referencia
  entity_type VARCHAR(50) NOT NULL,  -- compliance_item, finding, corrective_action
  entity_id UUID NOT NULL,

  -- Cambio
  action VARCHAR(50) NOT NULL,       -- status_change, evidence_added, verified, etc
  previous_value JSONB,
  new_value JSONB,
  change_reason TEXT,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_history_entity ON compliance.compliance_history(entity_type, entity_id);
CREATE INDEX idx_history_date ON compliance.compliance_history(created_at);
```

---

## 3. Funciones

### 3.1 Calcular Porcentaje de Cumplimiento

```sql
CREATE OR REPLACE FUNCTION compliance.calculate_compliance_percentage(p_project_program_id UUID)
RETURNS VOID AS $$
DECLARE
  v_total INTEGER;
  v_compliant INTEGER;
  v_non_compliant INTEGER;
  v_pending INTEGER;
  v_percentage DECIMAL(5,2);
BEGIN
  -- Contar por estado
  SELECT
    COUNT(*) FILTER (WHERE status != 'not_applicable'),
    COUNT(*) FILTER (WHERE status = 'compliant'),
    COUNT(*) FILTER (WHERE status = 'non_compliant'),
    COUNT(*) FILTER (WHERE status IN ('pending', 'in_progress'))
  INTO v_total, v_compliant, v_non_compliant, v_pending
  FROM compliance.compliance_items
  WHERE project_program_id = p_project_program_id;

  -- Calcular porcentaje
  IF v_total > 0 THEN
    v_percentage := (v_compliant::DECIMAL / v_total) * 100;
  ELSE
    v_percentage := 0;
  END IF;

  -- Actualizar project_program
  UPDATE compliance.project_programs
  SET
    total_requirements = v_total,
    compliant_count = v_compliant,
    non_compliant_count = v_non_compliant,
    pending_count = v_pending,
    compliance_percentage = v_percentage,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = p_project_program_id;
END;
$$ LANGUAGE plpgsql;
```

### 3.2 Crear Items de Cumplimiento al Registrar Proyecto

```sql
CREATE OR REPLACE FUNCTION compliance.create_compliance_items_for_project()
RETURNS TRIGGER AS $$
BEGIN
  -- Crear items para cada requisito del programa
  INSERT INTO compliance.compliance_items (
    tenant_id, project_program_id, requirement_id, status, created_by
  )
  SELECT
    NEW.tenant_id,
    NEW.id,
    pr.id,
    'pending',
    NEW.created_by
  FROM compliance.program_requirements pr
  WHERE pr.program_id = NEW.program_id
    AND pr.is_active = true;

  -- Calcular porcentaje inicial
  PERFORM compliance.calculate_compliance_percentage(NEW.id);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_create_compliance_items
AFTER INSERT ON compliance.project_programs
FOR EACH ROW
EXECUTE FUNCTION compliance.create_compliance_items_for_project();
```

### 3.3 Actualizar Porcentaje al Cambiar Estado

```sql
CREATE OR REPLACE FUNCTION compliance.on_compliance_item_change()
RETURNS TRIGGER AS $$
BEGIN
  -- Registrar historial
  INSERT INTO compliance.compliance_history (
    tenant_id, entity_type, entity_id, action,
    previous_value, new_value, created_by
  )
  VALUES (
    NEW.tenant_id,
    'compliance_item',
    NEW.id,
    'status_change',
    jsonb_build_object('status', OLD.status),
    jsonb_build_object('status', NEW.status),
    NEW.updated_by
  );

  -- Recalcular porcentaje
  PERFORM compliance.calculate_compliance_percentage(NEW.project_program_id);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_compliance_item_change
AFTER UPDATE OF status ON compliance.compliance_items
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION compliance.on_compliance_item_change();
```

### 3.4 Generar Alertas de Vencimiento

```sql
CREATE OR REPLACE FUNCTION compliance.generate_due_date_alerts()
RETURNS INTEGER AS $$
DECLARE
  v_count INTEGER := 0;
BEGIN
  -- Alertas para requisitos proximos a vencer (7 dias)
  INSERT INTO compliance.compliance_alerts (
    tenant_id, project_id, alert_type, severity,
    reference_type, reference_id, title, message, expires_at
  )
  SELECT
    ci.tenant_id,
    pp.project_id,
    'requirement_due',
    CASE
      WHEN ci.due_date < CURRENT_DATE THEN 'critical'
      WHEN ci.due_date <= CURRENT_DATE + INTERVAL '3 days' THEN 'warning'
      ELSE 'info'
    END,
    'compliance_item',
    ci.id,
    'Requisito proximo a vencer: ' || pr.name,
    format('El requisito %s vence el %s', pr.code, ci.due_date::TEXT),
    ci.due_date + INTERVAL '1 day'
  FROM compliance.compliance_items ci
  JOIN compliance.project_programs pp ON pp.id = ci.project_program_id
  JOIN compliance.program_requirements pr ON pr.id = ci.requirement_id
  WHERE ci.status IN ('pending', 'in_progress')
    AND ci.due_date IS NOT NULL
    AND ci.due_date <= CURRENT_DATE + INTERVAL '7 days'
    AND NOT EXISTS (
      SELECT 1 FROM compliance.compliance_alerts ca
      WHERE ca.reference_id = ci.id
        AND ca.alert_type = 'requirement_due'
        AND ca.status = 'active'
    );

  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$ LANGUAGE plpgsql;
```

---

## 4. Row Level Security

```sql
-- Habilitar RLS en todas las tablas
ALTER TABLE compliance.infonavit_programs ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.program_requirements ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.project_programs ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.compliance_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.compliance_evidence ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.audits ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.audit_findings ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.corrective_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.compliance_alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance.compliance_history ENABLE ROW LEVEL SECURITY;

-- Crear politicas de aislamiento por tenant
DO $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'compliance'
  LOOP
    EXECUTE format('
      CREATE POLICY tenant_isolation ON compliance.%I
      USING (tenant_id = current_setting(''app.current_tenant_id'')::uuid)
    ', t);
  END LOOP;
END $$;
```

---

## 5. Seeds Iniciales

```sql
-- Programas INFONAVIT base
INSERT INTO compliance.infonavit_programs (tenant_id, code, name, program_type, is_active)
VALUES
  ('{{TENANT_ID}}', 'EVC', 'Esquemas de Valor Compartido', 'evc', true),
  ('{{TENANT_ID}}', 'TRAD', 'Credito Tradicional', 'traditional', true),
  ('{{TENANT_ID}}', 'COFI', 'Cofinavit', 'cofinavit', true),
  ('{{TENANT_ID}}', 'TOTAL', 'INFONAVIT Total', 'infonavit_total', true),
  ('{{TENANT_ID}}', 'APOYO', 'Apoyo INFONAVIT', 'apoyo_infonavit', true);

-- Requisitos tecnicos base (ejemplo para EVC)
INSERT INTO compliance.program_requirements (tenant_id, program_id, code, name, category, is_mandatory)
SELECT
  '{{TENANT_ID}}',
  p.id,
  code,
  name,
  'technical'::compliance.requirement_category,
  true
FROM compliance.infonavit_programs p
CROSS JOIN (VALUES
  ('TEC-001', 'Superficie construida minima 38 m2'),
  ('TEC-002', 'Altura minima de entrepiso 2.30 m'),
  ('TEC-003', 'Recamara principal minimo 7 m2'),
  ('TEC-004', 'Bano completo con regadera'),
  ('TEC-005', 'Cocina con espacio para estufa y refrigerador'),
  ('TEC-006', 'Sistema electrico con centro de carga'),
  ('TEC-007', 'Sistema hidraulico con tinaco'),
  ('TEC-008', 'Sistema sanitario conectado a drenaje'),
  ('TEC-009', 'Canceleria completa en ventanas'),
  ('TEC-010', 'Puertas de acceso con chapa de seguridad')
) AS reqs(code, name)
WHERE p.code = 'EVC';
```

---

## Referencias

- [MAI-011: INFONAVIT Cumplimiento](../../02-definicion-modulos/MAI-011-infonavit-cumplimiento/)
- [EPIC-MAI-011](../../08-epicas/EPIC-MAI-011-infonavit.md)
- [ADR-007: Database Design](../../97-adr/ADR-007-database-design.md)

---

*Ultima actualizacion: 2025-12-05*