rckrdmrd a9703b2727 Initial commit - erp-construccion

2026-01-04 06:12:06 -06:00

22 KiB

Raw Permalink Blame History

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

-- 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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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

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

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

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

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

-- 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

-- 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

Ultima actualizacion: 2025-12-05

22 KiB Raw Permalink Blame History

DDL SPECIFICATION: Schema Compliance (INFONAVIT)

Resumen

1. ENUMs

2. Tablas

2.1 infonavit_programs (Programas INFONAVIT)

2.2 program_requirements (Requisitos por Programa)

2.3 project_programs (Proyectos en Programas)

2.4 compliance_items (Items de Cumplimiento)

2.5 compliance_evidence (Evidencias)

2.6 audits (Auditorias)

2.7 audit_findings (Hallazgos de Auditoria)

2.8 corrective_actions (Acciones Correctivas)

2.9 compliance_alerts (Alertas de Cumplimiento)

2.10 compliance_history (Historial de Cambios)

3. Funciones

3.1 Calcular Porcentaje de Cumplimiento

3.2 Crear Items de Cumplimiento al Registrar Proyecto

3.3 Actualizar Porcentaje al Cambiar Estado

3.4 Generar Alertas de Vencimiento

4. Row Level Security

5. Seeds Iniciales

Referencias

22 KiB

Raw Permalink Blame History