erp-construccion/docs/02-definicion-modulos/MAI-006-calidad/especificaciones/ET-CAL-001-database.md

DDL-SPEC: Schema quality_management

Identificacion

Campo	Valor
Schema	quality_management
Modulo	MAI-006
Vertical	Construccion
Version	1.0
Estado	En Diseno
Autor	Database-Agent
Fecha	2025-12-06

---

Descripcion General

El schema quality_management gestiona el control de calidad en proyectos de construccion, incluyendo inspecciones, no conformidades, acciones correctivas, pruebas de laboratorio y certificaciones.

RF Cubiertos

RF	Titulo	Tablas
RF-CAL-001	Inspecciones de Calidad	inspections, inspection_items
RF-CAL-002	No Conformidades	non_conformities, corrective_actions
RF-CAL-003	Pruebas de Laboratorio	lab_tests
RF-CAL-004	Certificaciones	certifications

---

Diagrama ER

erDiagram
    inspections {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        varchar inspection_code
        varchar inspection_type
        varchar title
        text description
        uuid inspector_id FK
        date inspection_date
        time inspection_time
        varchar status
        varchar result
        jsonb checklist
        jsonb attachments
        text observations
        uuid approved_by FK
        timestamptz approved_at
        timestamptz created_at
        timestamptz updated_at
    }

    inspection_items {
        uuid id PK
        uuid tenant_id FK
        uuid inspection_id FK
        int item_number
        varchar item_type
        varchar description
        varchar requirement
        varchar verification_method
        varchar result
        varchar conformity
        text observations
        jsonb measurements
        jsonb attachments
        timestamptz created_at
    }

    non_conformities {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        uuid inspection_id FK
        varchar nc_code
        varchar severity
        varchar category
        varchar title
        text description
        varchar location
        text root_cause
        varchar status
        uuid detected_by FK
        timestamptz detected_at
        uuid assigned_to FK
        date deadline
        uuid closed_by FK
        timestamptz closed_at
        jsonb attachments
        timestamptz created_at
        timestamptz updated_at
    }

    corrective_actions {
        uuid id PK
        uuid tenant_id FK
        uuid non_conformity_id FK
        int action_number
        varchar action_type
        text description
        uuid responsible_id FK
        date planned_date
        date completed_date
        varchar status
        text verification_method
        text results
        jsonb evidence
        timestamptz created_at
        timestamptz updated_at
    }

    lab_tests {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        uuid inspection_id FK
        varchar test_code
        varchar test_type
        varchar material_type
        varchar sample_id
        date sample_date
        varchar location
        uuid lab_id FK
        uuid technician_id FK
        date test_date
        varchar status
        varchar result
        jsonb test_parameters
        jsonb test_results
        boolean meets_spec
        text observations
        jsonb attachments
        timestamptz created_at
        timestamptz updated_at
    }

    certifications {
        uuid id PK
        uuid tenant_id FK
        uuid project_id FK
        varchar cert_code
        varchar cert_type
        varchar title
        varchar issuing_body
        date issue_date
        date expiry_date
        varchar status
        text scope
        jsonb requirements
        jsonb documents
        uuid verified_by FK
        timestamptz verified_at
        jsonb attachments
        timestamptz created_at
        timestamptz updated_at
    }

    inspections ||--o{ inspection_items : "contiene"
    inspections ||--o{ non_conformities : "genera"
    non_conformities ||--o{ corrective_actions : "requiere"
    inspections ||--o{ lab_tests : "solicita"

---

Tablas

1. inspections

Registro de inspecciones de calidad realizadas.

Columna	Tipo	Nullable	Default	Descripcion
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	-	FK a tenants
project_id	UUID	NOT NULL	-	FK a projects
inspection_code	VARCHAR(50)	NOT NULL	-	Codigo unico de inspeccion
inspection_type	VARCHAR(50)	NOT NULL	-	Tipo de inspeccion (ENUM)
title	VARCHAR(255)	NOT NULL	-	Titulo de la inspeccion
description	TEXT	NULL	-	Descripcion detallada
inspector_id	UUID	NOT NULL	-	FK a users (inspector)
inspection_date	DATE	NOT NULL	-	Fecha de inspeccion
inspection_time	TIME	NULL	-	Hora de inspeccion
location	VARCHAR(255)	NULL	-	Ubicacion en obra
status	VARCHAR(20)	NOT NULL	'scheduled'	Estado (ENUM)
result	VARCHAR(20)	NULL	-	Resultado (ENUM)
checklist	JSONB	NULL	'{}'	Checklist de verificacion
attachments	JSONB	NULL	'[]'	Fotos, documentos
observations	TEXT	NULL	-	Observaciones generales
approved_by	UUID	NULL	-	FK a users (aprobador)
approved_at	TIMESTAMPTZ	NULL	-	Fecha aprobacion
created_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion
created_by	UUID	NOT NULL	-	FK a users
updated_by	UUID	NULL	-	FK a users
is_active	BOOLEAN	NOT NULL	true	Soft delete
deleted_at	TIMESTAMPTZ	NULL	-	Fecha eliminacion
deleted_by	UUID	NULL	-	FK a users

-- ============================================================================
-- Schema: quality_management
-- Tabla: inspections
-- Descripcion: Inspecciones de calidad en proyectos de construccion
-- Modulo: MAI-006
-- ============================================================================

-- ENUM: Tipos de inspeccion
CREATE TYPE quality_management.inspection_type AS ENUM (
    'initial',              -- Inspeccion inicial
    'progress',             -- Inspeccion de avance
    'final',                -- Inspeccion final
    'structural',           -- Inspeccion estructural
    'electrical',           -- Inspeccion electrica
    'plumbing',             -- Inspeccion de plomeria
    'hvac',                 -- Inspeccion HVAC
    'safety',               -- Inspeccion de seguridad
    'environmental',        -- Inspeccion ambiental
    'quality_control',      -- Control de calidad
    'material_reception',   -- Recepcion de materiales
    'equipment',            -- Inspeccion de equipos
    'finishing'             -- Inspeccion de acabados
);

-- ENUM: Estados de inspeccion
CREATE TYPE quality_management.inspection_status AS ENUM (
    'scheduled',    -- Programada
    'in_progress',  -- En progreso
    'completed',    -- Completada
    'approved',     -- Aprobada
    'rejected',     -- Rechazada
    'cancelled'     -- Cancelada
);

-- ENUM: Resultados de inspeccion
CREATE TYPE quality_management.inspection_result AS ENUM (
    'passed',           -- Aprobada
    'failed',           -- Rechazada
    'conditional',      -- Condicional
    'pending_review'    -- Pendiente de revision
);

-- Crear tabla
CREATE TABLE IF NOT EXISTS quality_management.inspections (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL,
    inspector_id UUID NOT NULL,

    -- Datos de negocio
    inspection_code VARCHAR(50) NOT NULL,
    inspection_type quality_management.inspection_type NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    inspection_date DATE NOT NULL,
    inspection_time TIME,
    location VARCHAR(255),
    status quality_management.inspection_status NOT NULL DEFAULT 'scheduled',
    result quality_management.inspection_result,

    -- Datos estructurados
    checklist JSONB DEFAULT '{}',
    attachments JSONB DEFAULT '[]',
    observations TEXT,

    -- Aprobacion
    approved_by UUID,
    approved_at TIMESTAMPTZ,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,
    updated_by UUID,

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    -- Constraints
    CONSTRAINT uq_inspections_code_tenant UNIQUE (tenant_id, inspection_code),
    CONSTRAINT chk_inspections_approval CHECK (
        (approved_by IS NULL AND approved_at IS NULL) OR
        (approved_by IS NOT NULL AND approved_at IS NOT NULL)
    )
);

-- Comentarios
COMMENT ON TABLE quality_management.inspections IS 'Inspecciones de calidad en proyectos de construccion';
COMMENT ON COLUMN quality_management.inspections.inspection_code IS 'Codigo unico de inspeccion por tenant';
COMMENT ON COLUMN quality_management.inspections.inspection_type IS 'Tipo de inspeccion realizada';
COMMENT ON COLUMN quality_management.inspections.status IS 'Estado actual de la inspeccion';
COMMENT ON COLUMN quality_management.inspections.result IS 'Resultado de la inspeccion';
COMMENT ON COLUMN quality_management.inspections.checklist IS 'Lista de verificacion en formato JSON';

-- ============================================================================
-- INDICES
-- ============================================================================

CREATE INDEX idx_inspections_tenant ON quality_management.inspections(tenant_id);
CREATE INDEX idx_inspections_project ON quality_management.inspections(project_id, inspection_date DESC);
CREATE INDEX idx_inspections_inspector ON quality_management.inspections(inspector_id, inspection_date DESC);
CREATE INDEX idx_inspections_code ON quality_management.inspections(inspection_code);
CREATE INDEX idx_inspections_status ON quality_management.inspections(status) WHERE is_active = true;
CREATE INDEX idx_inspections_type ON quality_management.inspections(inspection_type, inspection_date DESC);
CREATE INDEX idx_inspections_date ON quality_management.inspections(inspection_date DESC);
CREATE INDEX idx_inspections_result ON quality_management.inspections(result) WHERE result IS NOT NULL;

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE quality_management.inspections ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON quality_management.inspections
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON quality_management.inspections
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON quality_management.inspections
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON quality_management.inspections
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

CREATE TRIGGER trg_inspections_update_timestamp
    BEFORE UPDATE ON quality_management.inspections
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

---

2. inspection_items

Items individuales de cada inspeccion.

Columna	Tipo	Nullable	Default	Descripcion
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	-	FK a tenants
inspection_id	UUID	NOT NULL	-	FK a inspections
item_number	INTEGER	NOT NULL	-	Numero de item
item_type	VARCHAR(50)	NOT NULL	-	Tipo de item
description	VARCHAR(500)	NOT NULL	-	Descripcion del item
requirement	VARCHAR(500)	NOT NULL	-	Requisito a verificar
verification_method	VARCHAR(100)	NOT NULL	-	Metodo de verificacion
result	VARCHAR(20)	NULL	-	Resultado del item
conformity	VARCHAR(20)	NULL	-	Conformidad (ENUM)
observations	TEXT	NULL	-	Observaciones
measurements	JSONB	NULL	'{}'	Mediciones realizadas
attachments	JSONB	NULL	'[]'	Evidencias
created_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
created_by	UUID	NOT NULL	-	FK a users

-- ============================================================================
-- Tabla: inspection_items
-- Descripcion: Items individuales de cada inspeccion
-- ============================================================================

-- ENUM: Conformidad
CREATE TYPE quality_management.conformity_status AS ENUM (
    'conforming',       -- Conforme
    'non_conforming',   -- No conforme
    'partially',        -- Parcialmente conforme
    'not_applicable'    -- No aplica
);

CREATE TABLE IF NOT EXISTS quality_management.inspection_items (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    inspection_id UUID NOT NULL REFERENCES quality_management.inspections(id) ON DELETE CASCADE,

    -- Datos de negocio
    item_number INTEGER NOT NULL,
    item_type VARCHAR(50) NOT NULL,
    description VARCHAR(500) NOT NULL,
    requirement VARCHAR(500) NOT NULL,
    verification_method VARCHAR(100) NOT NULL,
    result VARCHAR(20),
    conformity quality_management.conformity_status,
    observations TEXT,

    -- Datos estructurados
    measurements JSONB DEFAULT '{}',
    attachments JSONB DEFAULT '[]',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,

    -- Constraints
    CONSTRAINT uq_inspection_items_number UNIQUE (inspection_id, item_number)
);

-- Comentarios
COMMENT ON TABLE quality_management.inspection_items IS 'Items individuales de verificacion en inspecciones';
COMMENT ON COLUMN quality_management.inspection_items.item_number IS 'Numero secuencial del item dentro de la inspeccion';
COMMENT ON COLUMN quality_management.inspection_items.conformity IS 'Estado de conformidad del item';
COMMENT ON COLUMN quality_management.inspection_items.measurements IS 'Mediciones y valores registrados en formato JSON';

-- Indices
CREATE INDEX idx_inspection_items_tenant ON quality_management.inspection_items(tenant_id);
CREATE INDEX idx_inspection_items_inspection ON quality_management.inspection_items(inspection_id, item_number);
CREATE INDEX idx_inspection_items_conformity ON quality_management.inspection_items(conformity);

-- RLS
ALTER TABLE quality_management.inspection_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON quality_management.inspection_items
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON quality_management.inspection_items
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON quality_management.inspection_items
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON quality_management.inspection_items
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

---

3. non_conformities

No conformidades detectadas durante inspecciones.

Columna	Tipo	Nullable	Default	Descripcion
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	-	FK a tenants
project_id	UUID	NOT NULL	-	FK a projects
inspection_id	UUID	NULL	-	FK a inspections
nc_code	VARCHAR(50)	NOT NULL	-	Codigo de NC
severity	VARCHAR(20)	NOT NULL	-	Severidad (ENUM)
category	VARCHAR(50)	NOT NULL	-	Categoria
title	VARCHAR(255)	NOT NULL	-	Titulo
description	TEXT	NOT NULL	-	Descripcion detallada
location	VARCHAR(255)	NOT NULL	-	Ubicacion
root_cause	TEXT	NULL	-	Causa raiz
status	VARCHAR(20)	NOT NULL	'open'	Estado (ENUM)
detected_by	UUID	NOT NULL	-	FK a users
detected_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha deteccion
assigned_to	UUID	NULL	-	FK a users
deadline	DATE	NULL	-	Fecha limite
closed_by	UUID	NULL	-	FK a users
closed_at	TIMESTAMPTZ	NULL	-	Fecha cierre
attachments	JSONB	NULL	'[]'	Evidencias
created_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion
created_by	UUID	NOT NULL	-	FK a users
updated_by	UUID	NULL	-	FK a users
is_active	BOOLEAN	NOT NULL	true	Soft delete
deleted_at	TIMESTAMPTZ	NULL	-	Fecha eliminacion
deleted_by	UUID	NULL	-	FK a users

-- ============================================================================
-- Tabla: non_conformities
-- Descripcion: No conformidades detectadas
-- ============================================================================

-- ENUM: Severidad de NC
CREATE TYPE quality_management.nc_severity AS ENUM (
    'critical',     -- Critica
    'major',        -- Mayor
    'minor',        -- Menor
    'observation'   -- Observacion
);

-- ENUM: Estados de NC
CREATE TYPE quality_management.nc_status AS ENUM (
    'open',             -- Abierta
    'assigned',         -- Asignada
    'in_progress',      -- En progreso
    'pending_review',   -- Pendiente revision
    'closed',           -- Cerrada
    'rejected'          -- Rechazada
);

CREATE TABLE IF NOT EXISTS quality_management.non_conformities (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL,
    inspection_id UUID REFERENCES quality_management.inspections(id),

    -- Datos de negocio
    nc_code VARCHAR(50) NOT NULL,
    severity quality_management.nc_severity NOT NULL,
    category VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    location VARCHAR(255) NOT NULL,
    root_cause TEXT,
    status quality_management.nc_status NOT NULL DEFAULT 'open',

    -- Gestion
    detected_by UUID NOT NULL,
    detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    assigned_to UUID,
    deadline DATE,
    closed_by UUID,
    closed_at TIMESTAMPTZ,

    -- Datos estructurados
    attachments JSONB DEFAULT '[]',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,
    updated_by UUID,

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    -- Constraints
    CONSTRAINT uq_nc_code_tenant UNIQUE (tenant_id, nc_code),
    CONSTRAINT chk_nc_closure CHECK (
        (closed_by IS NULL AND closed_at IS NULL) OR
        (closed_by IS NOT NULL AND closed_at IS NOT NULL)
    )
);

-- Comentarios
COMMENT ON TABLE quality_management.non_conformities IS 'No conformidades detectadas en inspecciones';
COMMENT ON COLUMN quality_management.non_conformities.nc_code IS 'Codigo unico de no conformidad';
COMMENT ON COLUMN quality_management.non_conformities.severity IS 'Nivel de severidad de la NC';
COMMENT ON COLUMN quality_management.non_conformities.status IS 'Estado actual de la NC';

-- Indices
CREATE INDEX idx_nc_tenant ON quality_management.non_conformities(tenant_id);
CREATE INDEX idx_nc_project ON quality_management.non_conformities(project_id, detected_at DESC);
CREATE INDEX idx_nc_inspection ON quality_management.non_conformities(inspection_id);
CREATE INDEX idx_nc_code ON quality_management.non_conformities(nc_code);
CREATE INDEX idx_nc_status ON quality_management.non_conformities(status) WHERE is_active = true;
CREATE INDEX idx_nc_severity ON quality_management.non_conformities(severity, detected_at DESC);
CREATE INDEX idx_nc_assigned ON quality_management.non_conformities(assigned_to) WHERE status IN ('assigned', 'in_progress');
CREATE INDEX idx_nc_overdue ON quality_management.non_conformities(deadline)
    WHERE status NOT IN ('closed', 'rejected') AND deadline < CURRENT_DATE;

-- RLS
ALTER TABLE quality_management.non_conformities ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON quality_management.non_conformities
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON quality_management.non_conformities
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON quality_management.non_conformities
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON quality_management.non_conformities
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Triggers
CREATE TRIGGER trg_nc_update_timestamp
    BEFORE UPDATE ON quality_management.non_conformities
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

---

4. corrective_actions

Acciones correctivas para resolver no conformidades.

Columna	Tipo	Nullable	Default	Descripcion
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	-	FK a tenants
non_conformity_id	UUID	NOT NULL	-	FK a non_conformities
action_number	INTEGER	NOT NULL	-	Numero de accion
action_type	VARCHAR(50)	NOT NULL	-	Tipo de accion (ENUM)
description	TEXT	NOT NULL	-	Descripcion
responsible_id	UUID	NOT NULL	-	FK a users
planned_date	DATE	NOT NULL	-	Fecha planificada
completed_date	DATE	NULL	-	Fecha completada
status	VARCHAR(20)	NOT NULL	'planned'	Estado (ENUM)
verification_method	TEXT	NULL	-	Metodo de verificacion
results	TEXT	NULL	-	Resultados obtenidos
evidence	JSONB	NULL	'[]'	Evidencias
created_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion
created_by	UUID	NOT NULL	-	FK a users
updated_by	UUID	NULL	-	FK a users

-- ============================================================================
-- Tabla: corrective_actions
-- Descripcion: Acciones correctivas para NCs
-- ============================================================================

-- ENUM: Tipos de accion correctiva
CREATE TYPE quality_management.action_type AS ENUM (
    'immediate',        -- Accion inmediata
    'corrective',       -- Accion correctiva
    'preventive',       -- Accion preventiva
    'improvement'       -- Mejora
);

-- ENUM: Estados de accion
CREATE TYPE quality_management.action_status AS ENUM (
    'planned',      -- Planificada
    'in_progress',  -- En progreso
    'completed',    -- Completada
    'verified',     -- Verificada
    'rejected'      -- Rechazada
);

CREATE TABLE IF NOT EXISTS quality_management.corrective_actions (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    non_conformity_id UUID NOT NULL REFERENCES quality_management.non_conformities(id) ON DELETE CASCADE,

    -- Datos de negocio
    action_number INTEGER NOT NULL,
    action_type quality_management.action_type NOT NULL,
    description TEXT NOT NULL,
    responsible_id UUID NOT NULL,
    planned_date DATE NOT NULL,
    completed_date DATE,
    status quality_management.action_status NOT NULL DEFAULT 'planned',
    verification_method TEXT,
    results TEXT,

    -- Datos estructurados
    evidence JSONB DEFAULT '[]',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,
    updated_by UUID,

    -- Constraints
    CONSTRAINT uq_actions_number UNIQUE (non_conformity_id, action_number),
    CONSTRAINT chk_action_completion CHECK (
        (completed_date IS NULL) OR
        (completed_date >= planned_date)
    )
);

-- Comentarios
COMMENT ON TABLE quality_management.corrective_actions IS 'Acciones correctivas para resolver no conformidades';
COMMENT ON COLUMN quality_management.corrective_actions.action_type IS 'Tipo de accion: inmediata, correctiva, preventiva o mejora';
COMMENT ON COLUMN quality_management.corrective_actions.status IS 'Estado actual de la accion';

-- Indices
CREATE INDEX idx_actions_tenant ON quality_management.corrective_actions(tenant_id);
CREATE INDEX idx_actions_nc ON quality_management.corrective_actions(non_conformity_id, action_number);
CREATE INDEX idx_actions_responsible ON quality_management.corrective_actions(responsible_id, status);
CREATE INDEX idx_actions_status ON quality_management.corrective_actions(status);
CREATE INDEX idx_actions_planned ON quality_management.corrective_actions(planned_date)
    WHERE status IN ('planned', 'in_progress');

-- RLS
ALTER TABLE quality_management.corrective_actions ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON quality_management.corrective_actions
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON quality_management.corrective_actions
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON quality_management.corrective_actions
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON quality_management.corrective_actions
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Triggers
CREATE TRIGGER trg_actions_update_timestamp
    BEFORE UPDATE ON quality_management.corrective_actions
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

---

5. lab_tests

Pruebas de laboratorio de materiales.

Columna	Tipo	Nullable	Default	Descripcion
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	-	FK a tenants
project_id	UUID	NOT NULL	-	FK a projects
inspection_id	UUID	NULL	-	FK a inspections
test_code	VARCHAR(50)	NOT NULL	-	Codigo de prueba
test_type	VARCHAR(50)	NOT NULL	-	Tipo de prueba (ENUM)
material_type	VARCHAR(50)	NOT NULL	-	Tipo de material
sample_id	VARCHAR(50)	NOT NULL	-	ID de muestra
sample_date	DATE	NOT NULL	-	Fecha de muestreo
location	VARCHAR(255)	NOT NULL	-	Ubicacion de muestreo
lab_id	UUID	NULL	-	FK a laboratorio
technician_id	UUID	NULL	-	FK a users (tecnico)
test_date	DATE	NULL	-	Fecha de prueba
status	VARCHAR(20)	NOT NULL	'pending'	Estado (ENUM)
result	VARCHAR(20)	NULL	-	Resultado (ENUM)
test_parameters	JSONB	NULL	'{}'	Parametros de prueba
test_results	JSONB	NULL	'{}'	Resultados detallados
meets_spec	BOOLEAN	NULL	-	Cumple especificacion
observations	TEXT	NULL	-	Observaciones
attachments	JSONB	NULL	'[]'	Certificados, reportes
created_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion
created_by	UUID	NOT NULL	-	FK a users
updated_by	UUID	NULL	-	FK a users
is_active	BOOLEAN	NOT NULL	true	Soft delete
deleted_at	TIMESTAMPTZ	NULL	-	Fecha eliminacion
deleted_by	UUID	NULL	-	FK a users

-- ============================================================================
-- Tabla: lab_tests
-- Descripcion: Pruebas de laboratorio de materiales
-- ============================================================================

-- ENUM: Tipos de prueba
CREATE TYPE quality_management.lab_test_type AS ENUM (
    'concrete_compression',     -- Compresion de concreto
    'concrete_slump',           -- Revenimiento
    'steel_tensile',            -- Tension de acero
    'soil_compaction',          -- Compactacion de suelo
    'soil_bearing',             -- Capacidad portante
    'asphalt_density',          -- Densidad de asfalto
    'mortar_compression',       -- Compresion de mortero
    'brick_absorption',         -- Absorcion de ladrillo
    'aggregate_gradation',      -- Granulometria
    'chemical_analysis',        -- Analisis quimico
    'moisture_content',         -- Contenido de humedad
    'other'                     -- Otra
);

-- ENUM: Estados de prueba
CREATE TYPE quality_management.lab_test_status AS ENUM (
    'pending',          -- Pendiente
    'sample_collected', -- Muestra recolectada
    'in_lab',           -- En laboratorio
    'testing',          -- En prueba
    'completed',        -- Completada
    'certified'         -- Certificada
);

-- ENUM: Resultados de prueba
CREATE TYPE quality_management.lab_test_result AS ENUM (
    'passed',       -- Aprobada
    'failed',       -- Rechazada
    'conditional',  -- Condicional
    'retest'        -- Re-ensayo
);

CREATE TABLE IF NOT EXISTS quality_management.lab_tests (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL,
    inspection_id UUID REFERENCES quality_management.inspections(id),
    lab_id UUID,
    technician_id UUID,

    -- Datos de negocio
    test_code VARCHAR(50) NOT NULL,
    test_type quality_management.lab_test_type NOT NULL,
    material_type VARCHAR(50) NOT NULL,
    sample_id VARCHAR(50) NOT NULL,
    sample_date DATE NOT NULL,
    location VARCHAR(255) NOT NULL,
    test_date DATE,
    status quality_management.lab_test_status NOT NULL DEFAULT 'pending',
    result quality_management.lab_test_result,
    meets_spec BOOLEAN,
    observations TEXT,

    -- Datos estructurados
    test_parameters JSONB DEFAULT '{}',
    test_results JSONB DEFAULT '{}',
    attachments JSONB DEFAULT '[]',

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,
    updated_by UUID,

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    -- Constraints
    CONSTRAINT uq_lab_tests_code_tenant UNIQUE (tenant_id, test_code),
    CONSTRAINT chk_lab_test_dates CHECK (
        (test_date IS NULL) OR (test_date >= sample_date)
    )
);

-- Comentarios
COMMENT ON TABLE quality_management.lab_tests IS 'Pruebas de laboratorio de materiales de construccion';
COMMENT ON COLUMN quality_management.lab_tests.test_type IS 'Tipo de prueba de laboratorio';
COMMENT ON COLUMN quality_management.lab_tests.test_parameters IS 'Parametros de la prueba en formato JSON';
COMMENT ON COLUMN quality_management.lab_tests.test_results IS 'Resultados detallados en formato JSON';

-- Indices
CREATE INDEX idx_lab_tests_tenant ON quality_management.lab_tests(tenant_id);
CREATE INDEX idx_lab_tests_project ON quality_management.lab_tests(project_id, sample_date DESC);
CREATE INDEX idx_lab_tests_inspection ON quality_management.lab_tests(inspection_id);
CREATE INDEX idx_lab_tests_code ON quality_management.lab_tests(test_code);
CREATE INDEX idx_lab_tests_status ON quality_management.lab_tests(status) WHERE is_active = true;
CREATE INDEX idx_lab_tests_type ON quality_management.lab_tests(test_type, sample_date DESC);
CREATE INDEX idx_lab_tests_result ON quality_management.lab_tests(result) WHERE result IS NOT NULL;
CREATE INDEX idx_lab_tests_failed ON quality_management.lab_tests(project_id)
    WHERE result = 'failed' AND is_active = true;

-- RLS
ALTER TABLE quality_management.lab_tests ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON quality_management.lab_tests
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON quality_management.lab_tests
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON quality_management.lab_tests
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON quality_management.lab_tests
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Triggers
CREATE TRIGGER trg_lab_tests_update_timestamp
    BEFORE UPDATE ON quality_management.lab_tests
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

---

6. certifications

Certificaciones de calidad de proyectos.

Columna	Tipo	Nullable	Default	Descripcion
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	-	FK a tenants
project_id	UUID	NOT NULL	-	FK a projects
cert_code	VARCHAR(50)	NOT NULL	-	Codigo de certificacion
cert_type	VARCHAR(50)	NOT NULL	-	Tipo de certificacion (ENUM)
title	VARCHAR(255)	NOT NULL	-	Titulo
issuing_body	VARCHAR(255)	NOT NULL	-	Organismo emisor
issue_date	DATE	NOT NULL	-	Fecha emision
expiry_date	DATE	NULL	-	Fecha vencimiento
status	VARCHAR(20)	NOT NULL	'pending'	Estado (ENUM)
scope	TEXT	NULL	-	Alcance
requirements	JSONB	NULL	'{}'	Requisitos
documents	JSONB	NULL	'[]'	Documentos
verified_by	UUID	NULL	-	FK a users
verified_at	TIMESTAMPTZ	NULL	-	Fecha verificacion
attachments	JSONB	NULL	'[]'	Certificados
created_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
updated_at	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion
created_by	UUID	NOT NULL	-	FK a users
updated_by	UUID	NULL	-	FK a users
is_active	BOOLEAN	NOT NULL	true	Soft delete
deleted_at	TIMESTAMPTZ	NULL	-	Fecha eliminacion
deleted_by	UUID	NULL	-	FK a users

-- ============================================================================
-- Tabla: certifications
-- Descripcion: Certificaciones de calidad
-- ============================================================================

-- ENUM: Tipos de certificacion
CREATE TYPE quality_management.certification_type AS ENUM (
    'iso_9001',             -- ISO 9001
    'iso_14001',            -- ISO 14001
    'iso_45001',            -- ISO 45001
    'leed',                 -- LEED
    'breeam',               -- BREEAM
    'well',                 -- WELL Building
    'edge',                 -- EDGE
    'project_completion',   -- Finalizacion de proyecto
    'structural',           -- Estructural
    'electrical',           -- Electrica
    'plumbing',             -- Sanitaria
    'fire_safety',          -- Contra incendios
    'seismic',              -- Sismica
    'environmental',        -- Ambiental
    'other'                 -- Otra
);

-- ENUM: Estados de certificacion
CREATE TYPE quality_management.certification_status AS ENUM (
    'pending',      -- Pendiente
    'in_process',   -- En proceso
    'issued',       -- Emitida
    'expired',      -- Vencida
    'revoked'       -- Revocada
);

CREATE TABLE IF NOT EXISTS quality_management.certifications (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL,

    -- Datos de negocio
    cert_code VARCHAR(50) NOT NULL,
    cert_type quality_management.certification_type NOT NULL,
    title VARCHAR(255) NOT NULL,
    issuing_body VARCHAR(255) NOT NULL,
    issue_date DATE NOT NULL,
    expiry_date DATE,
    status quality_management.certification_status NOT NULL DEFAULT 'pending',
    scope TEXT,

    -- Datos estructurados
    requirements JSONB DEFAULT '{}',
    documents JSONB DEFAULT '[]',
    attachments JSONB DEFAULT '[]',

    -- Verificacion
    verified_by UUID,
    verified_at TIMESTAMPTZ,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,
    updated_by UUID,

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    -- Constraints
    CONSTRAINT uq_certifications_code_tenant UNIQUE (tenant_id, cert_code),
    CONSTRAINT chk_cert_dates CHECK (
        (expiry_date IS NULL) OR (expiry_date > issue_date)
    )
);

-- Comentarios
COMMENT ON TABLE quality_management.certifications IS 'Certificaciones de calidad de proyectos';
COMMENT ON COLUMN quality_management.certifications.cert_type IS 'Tipo de certificacion';
COMMENT ON COLUMN quality_management.certifications.issuing_body IS 'Organismo o entidad emisora';
COMMENT ON COLUMN quality_management.certifications.requirements IS 'Requisitos de la certificacion en formato JSON';

-- Indices
CREATE INDEX idx_certifications_tenant ON quality_management.certifications(tenant_id);
CREATE INDEX idx_certifications_project ON quality_management.certifications(project_id, issue_date DESC);
CREATE INDEX idx_certifications_code ON quality_management.certifications(cert_code);
CREATE INDEX idx_certifications_status ON quality_management.certifications(status) WHERE is_active = true;
CREATE INDEX idx_certifications_type ON quality_management.certifications(cert_type, issue_date DESC);
CREATE INDEX idx_certifications_expiry ON quality_management.certifications(expiry_date)
    WHERE expiry_date IS NOT NULL AND status = 'issued';

-- RLS
ALTER TABLE quality_management.certifications ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON quality_management.certifications
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON quality_management.certifications
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON quality_management.certifications
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON quality_management.certifications
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Triggers
CREATE TRIGGER trg_certifications_update_timestamp
    BEFORE UPDATE ON quality_management.certifications
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

---

Funciones y Triggers de Flujo

Funcion: Auto-transicion de NC

-- ============================================================================
-- Funcion: Actualizar estado de NC basado en acciones correctivas
-- ============================================================================

CREATE OR REPLACE FUNCTION quality_management.update_nc_status_on_actions()
RETURNS TRIGGER AS $$
DECLARE
    v_total_actions INTEGER;
    v_completed_actions INTEGER;
BEGIN
    -- Contar acciones totales y completadas
    SELECT
        COUNT(*),
        COUNT(*) FILTER (WHERE status IN ('completed', 'verified'))
    INTO v_total_actions, v_completed_actions
    FROM quality_management.corrective_actions
    WHERE non_conformity_id = COALESCE(NEW.non_conformity_id, OLD.non_conformity_id)
    AND (SELECT is_active FROM quality_management.non_conformities WHERE id = COALESCE(NEW.non_conformity_id, OLD.non_conformity_id));

    -- Si todas las acciones estan completadas, cambiar NC a pending_review
    IF v_total_actions > 0 AND v_total_actions = v_completed_actions THEN
        UPDATE quality_management.non_conformities
        SET status = 'pending_review',
            updated_at = NOW()
        WHERE id = COALESCE(NEW.non_conformity_id, OLD.non_conformity_id)
        AND status = 'in_progress';
    END IF;

    -- Si alguna accion esta en progreso, cambiar NC a in_progress
    IF EXISTS (
        SELECT 1 FROM quality_management.corrective_actions
        WHERE non_conformity_id = COALESCE(NEW.non_conformity_id, OLD.non_conformity_id)
        AND status = 'in_progress'
    ) THEN
        UPDATE quality_management.non_conformities
        SET status = 'in_progress',
            updated_at = NOW()
        WHERE id = COALESCE(NEW.non_conformity_id, OLD.non_conformity_id)
        AND status = 'assigned';
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Trigger para actualizar NC cuando cambian las acciones
CREATE TRIGGER trg_update_nc_on_action_change
    AFTER INSERT OR UPDATE OR DELETE ON quality_management.corrective_actions
    FOR EACH ROW
    EXECUTE FUNCTION quality_management.update_nc_status_on_actions();

COMMENT ON FUNCTION quality_management.update_nc_status_on_actions() IS
    'Actualiza automaticamente el estado de la NC basado en el progreso de sus acciones correctivas';

Funcion: Validar resultado de inspeccion

-- ============================================================================
-- Funcion: Validar y actualizar resultado de inspeccion
-- ============================================================================

CREATE OR REPLACE FUNCTION quality_management.update_inspection_result()
RETURNS TRIGGER AS $$
DECLARE
    v_total_items INTEGER;
    v_non_conforming_items INTEGER;
    v_new_result quality_management.inspection_result;
BEGIN
    -- Solo ejecutar cuando se completa la inspeccion
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        -- Contar items conformes y no conformes
        SELECT
            COUNT(*),
            COUNT(*) FILTER (WHERE conformity = 'non_conforming')
        INTO v_total_items, v_non_conforming_items
        FROM quality_management.inspection_items
        WHERE inspection_id = NEW.id;

        -- Determinar resultado
        IF v_total_items = 0 THEN
            v_new_result := 'pending_review';
        ELSIF v_non_conforming_items = 0 THEN
            v_new_result := 'passed';
        ELSIF v_non_conforming_items < v_total_items THEN
            v_new_result := 'conditional';
        ELSE
            v_new_result := 'failed';
        END IF;

        NEW.result := v_new_result;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger para validar resultado
CREATE TRIGGER trg_validate_inspection_result
    BEFORE UPDATE ON quality_management.inspections
    FOR EACH ROW
    EXECUTE FUNCTION quality_management.update_inspection_result();

COMMENT ON FUNCTION quality_management.update_inspection_result() IS
    'Calcula automaticamente el resultado de la inspeccion basado en los items verificados';

Funcion: Notificar NC criticas

-- ============================================================================
-- Funcion: Crear alerta para NCs criticas
-- ============================================================================

CREATE OR REPLACE FUNCTION quality_management.notify_critical_nc()
RETURNS TRIGGER AS $$
BEGIN
    -- Si la NC es critica o mayor, crear notificacion
    IF NEW.severity IN ('critical', 'major') THEN
        -- Insertar en tabla de notificaciones (asumiendo que existe)
        -- Esta funcion se implementaria con la integracion del modulo de notificaciones
        PERFORM pg_notify(
            'quality_alert',
            json_build_object(
                'type', 'critical_nc',
                'nc_id', NEW.id,
                'nc_code', NEW.nc_code,
                'severity', NEW.severity,
                'project_id', NEW.project_id,
                'tenant_id', NEW.tenant_id
            )::text
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger para notificar
CREATE TRIGGER trg_notify_critical_nc
    AFTER INSERT ON quality_management.non_conformities
    FOR EACH ROW
    EXECUTE FUNCTION quality_management.notify_critical_nc();

COMMENT ON FUNCTION quality_management.notify_critical_nc() IS
    'Envia notificacion cuando se crea una NC critica o mayor';

---

Vistas de Consulta

Vista: Resumen de inspecciones por proyecto

CREATE OR REPLACE VIEW quality_management.v_inspection_summary AS
SELECT
    i.tenant_id,
    i.project_id,
    i.inspection_type,
    COUNT(*) as total_inspections,
    COUNT(*) FILTER (WHERE i.status = 'completed') as completed,
    COUNT(*) FILTER (WHERE i.status = 'approved') as approved,
    COUNT(*) FILTER (WHERE i.result = 'passed') as passed,
    COUNT(*) FILTER (WHERE i.result = 'failed') as failed,
    COUNT(*) FILTER (WHERE i.result = 'conditional') as conditional,
    MAX(i.inspection_date) as last_inspection_date
FROM quality_management.inspections i
WHERE i.is_active = true
GROUP BY i.tenant_id, i.project_id, i.inspection_type;

COMMENT ON VIEW quality_management.v_inspection_summary IS
    'Resumen de inspecciones agrupadas por proyecto y tipo';

Vista: NCs abiertas por severidad

CREATE OR REPLACE VIEW quality_management.v_open_nc_by_severity AS
SELECT
    nc.tenant_id,
    nc.project_id,
    nc.severity,
    COUNT(*) as total_nc,
    COUNT(*) FILTER (WHERE nc.status = 'open') as open,
    COUNT(*) FILTER (WHERE nc.status = 'assigned') as assigned,
    COUNT(*) FILTER (WHERE nc.status = 'in_progress') as in_progress,
    COUNT(*) FILTER (WHERE nc.deadline < CURRENT_DATE) as overdue,
    MIN(nc.detected_at) as oldest_nc_date,
    AVG(EXTRACT(EPOCH FROM (COALESCE(nc.closed_at, NOW()) - nc.detected_at))/86400)::INTEGER as avg_days_to_close
FROM quality_management.non_conformities nc
WHERE nc.is_active = true
AND nc.status NOT IN ('closed', 'rejected')
GROUP BY nc.tenant_id, nc.project_id, nc.severity;

COMMENT ON VIEW quality_management.v_open_nc_by_severity IS
    'NCs abiertas agrupadas por proyecto y severidad con metricas';

Vista: Pruebas de laboratorio pendientes

CREATE OR REPLACE VIEW quality_management.v_pending_lab_tests AS
SELECT
    lt.id,
    lt.tenant_id,
    lt.project_id,
    lt.test_code,
    lt.test_type,
    lt.material_type,
    lt.sample_date,
    lt.status,
    CURRENT_DATE - lt.sample_date as days_pending,
    lt.location,
    lt.technician_id
FROM quality_management.lab_tests lt
WHERE lt.is_active = true
AND lt.status NOT IN ('completed', 'certified')
ORDER BY lt.sample_date ASC;

COMMENT ON VIEW quality_management.v_pending_lab_tests IS
    'Pruebas de laboratorio pendientes ordenadas por antiguedad';

---

Indices de Performance

-- Indices adicionales para reportes y analytics
CREATE INDEX idx_inspections_project_type_date ON quality_management.inspections(project_id, inspection_type, inspection_date DESC)
    WHERE is_active = true;

CREATE INDEX idx_nc_project_severity_status ON quality_management.non_conformities(project_id, severity, status)
    WHERE is_active = true;

CREATE INDEX idx_lab_tests_project_type_date ON quality_management.lab_tests(project_id, test_type, sample_date DESC)
    WHERE is_active = true;

-- Indice para busqueda de texto en descripciones (usando trigram)
CREATE INDEX idx_nc_description_trgm ON quality_management.non_conformities
    USING gin (description gin_trgm_ops)
    WHERE is_active = true;

---

Consideraciones de Performance

Tabla	Volumen Esperado	Estrategia
inspections	Medio (miles/proyecto)	Indices por proyecto y fecha
inspection_items	Alto (decenas/inspeccion)	Cascade delete, indices compuestos
non_conformities	Medio-bajo	Indices por severidad y estado
corrective_actions	Medio	Indices por responsable y estado
lab_tests	Medio	Indices por tipo y fecha
certifications	Bajo	Indices simples

---

Validaciones Pre-Commit

• Todas las tablas tienen tenant_id
• RLS habilitado en todas las tablas
• 4 policies RLS creadas (SELECT, INSERT, UPDATE, DELETE)
• Indices en tenant_id
• Columnas de auditoria completas
• Comentarios en tablas y columnas
• ENUMs definidos para tipos y estados
• Triggers para flujo de NC
• Funciones de utilidad documentadas
• Vistas de consulta creadas
• Nomenclatura correcta

---

Historial

Version	Fecha	Autor	Cambios
1.0	2025-12-06	Database-Agent	Creacion inicial del schema quality_management