rckrdmrd
6e195553d2
ERP especializado para clínicas veterinarias. Estructura inicial: - database/ (PostgreSQL DDL) - docs/ (Documentación) - orchestration/ (Sistema NEXUS) 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
388 lines
15 KiB
SQL
388 lines
15 KiB
SQL
-- ============================================================================
|
|
-- VETERINARIA SCHEMA - Especialización de ERP-Clínicas
|
|
-- Clínica Veterinaria
|
|
-- ============================================================================
|
|
-- Fecha: 2026-01-04
|
|
-- Versión: 1.0
|
|
-- Hereda de: erp-clinicas FASE-8
|
|
-- ============================================================================
|
|
|
|
-- Schema
|
|
CREATE SCHEMA IF NOT EXISTS veterinaria;
|
|
|
|
-- ============================================================================
|
|
-- ENUMS
|
|
-- ============================================================================
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE veterinaria.sexo_animal AS ENUM ('macho', 'hembra', 'desconocido');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE veterinaria.estado_hospitalizacion AS ENUM (
|
|
'ingresado', 'en_tratamiento', 'estable', 'critico', 'alta', 'fallecido'
|
|
);
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- ============================================================================
|
|
-- CATÁLOGOS
|
|
-- ============================================================================
|
|
|
|
-- Especies
|
|
CREATE TABLE IF NOT EXISTS veterinaria.especies (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
nombre VARCHAR(50) NOT NULL,
|
|
nombre_cientifico VARCHAR(100),
|
|
descripcion TEXT,
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.especies IS 'Catálogo de especies animales';
|
|
|
|
-- Razas
|
|
CREATE TABLE IF NOT EXISTS veterinaria.razas (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
especie_id UUID NOT NULL REFERENCES veterinaria.especies(id) ON DELETE CASCADE,
|
|
nombre VARCHAR(100) NOT NULL,
|
|
descripcion TEXT,
|
|
tamanio_promedio VARCHAR(20), -- 'pequeño', 'mediano', 'grande', 'gigante'
|
|
peso_promedio_kg NUMERIC(5,2),
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.razas IS 'Catálogo de razas por especie';
|
|
|
|
-- Vacunas
|
|
CREATE TABLE IF NOT EXISTS veterinaria.vacunas (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
nombre VARCHAR(100) NOT NULL,
|
|
descripcion TEXT,
|
|
especie_id UUID REFERENCES veterinaria.especies(id),
|
|
laboratorio VARCHAR(100),
|
|
dosis_ml NUMERIC(5,2),
|
|
intervalo_refuerzo_dias INTEGER,
|
|
es_obligatoria BOOLEAN DEFAULT false,
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.vacunas IS 'Catálogo de vacunas veterinarias';
|
|
|
|
-- ============================================================================
|
|
-- TABLAS PRINCIPALES
|
|
-- ============================================================================
|
|
|
|
-- Propietarios (dueños de mascotas)
|
|
CREATE TABLE IF NOT EXISTS veterinaria.propietarios (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
partner_id UUID, -- Referencia opcional a core.partners
|
|
nombre VARCHAR(100) NOT NULL,
|
|
apellidos VARCHAR(100),
|
|
telefono VARCHAR(20),
|
|
telefono_emergencia VARCHAR(20),
|
|
email VARCHAR(100),
|
|
direccion TEXT,
|
|
rfc VARCHAR(13),
|
|
-- Control
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.propietarios IS 'Propietarios/dueños de mascotas';
|
|
|
|
-- Mascotas (pacientes)
|
|
CREATE TABLE IF NOT EXISTS veterinaria.mascotas (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
propietario_id UUID NOT NULL REFERENCES veterinaria.propietarios(id),
|
|
especie_id UUID NOT NULL REFERENCES veterinaria.especies(id),
|
|
raza_id UUID REFERENCES veterinaria.razas(id),
|
|
-- Datos básicos
|
|
nombre VARCHAR(100) NOT NULL,
|
|
sexo veterinaria.sexo_animal DEFAULT 'desconocido',
|
|
fecha_nacimiento DATE,
|
|
edad_aproximada VARCHAR(50), -- "3 años", "6 meses"
|
|
color VARCHAR(50),
|
|
peso_kg NUMERIC(6,2),
|
|
-- Identificación
|
|
numero_chip VARCHAR(50),
|
|
tiene_chip BOOLEAN DEFAULT false,
|
|
-- Estado
|
|
esterilizado BOOLEAN DEFAULT false,
|
|
fecha_esterilizacion DATE,
|
|
-- Notas
|
|
alergias TEXT,
|
|
condiciones_especiales TEXT,
|
|
notas TEXT,
|
|
-- Foto
|
|
foto_url VARCHAR(255),
|
|
-- Control
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.mascotas IS 'Mascotas/pacientes de la clínica veterinaria';
|
|
|
|
-- Cartilla de vacunación
|
|
CREATE TABLE IF NOT EXISTS veterinaria.cartilla_vacunacion (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
mascota_id UUID NOT NULL REFERENCES veterinaria.mascotas(id) ON DELETE CASCADE,
|
|
vacuna_id UUID NOT NULL REFERENCES veterinaria.vacunas(id),
|
|
veterinario_id UUID, -- Referencia a clinica.doctors
|
|
-- Datos de aplicación
|
|
fecha_aplicacion DATE NOT NULL,
|
|
fecha_proximo_refuerzo DATE,
|
|
lote VARCHAR(50),
|
|
laboratorio VARCHAR(100),
|
|
-- Notas
|
|
observaciones TEXT,
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.cartilla_vacunacion IS 'Historial de vacunación de mascotas';
|
|
|
|
-- Desparasitaciones
|
|
CREATE TABLE IF NOT EXISTS veterinaria.desparasitaciones (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
mascota_id UUID NOT NULL REFERENCES veterinaria.mascotas(id) ON DELETE CASCADE,
|
|
veterinario_id UUID,
|
|
-- Datos
|
|
tipo VARCHAR(50) NOT NULL, -- 'interna', 'externa', 'ambas'
|
|
producto VARCHAR(100) NOT NULL,
|
|
dosis VARCHAR(50),
|
|
via_administracion VARCHAR(50),
|
|
fecha_aplicacion DATE NOT NULL,
|
|
fecha_proxima DATE,
|
|
-- Notas
|
|
observaciones TEXT,
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.desparasitaciones IS 'Historial de desparasitaciones';
|
|
|
|
-- Hospitalización
|
|
CREATE TABLE IF NOT EXISTS veterinaria.hospitalizacion (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
mascota_id UUID NOT NULL REFERENCES veterinaria.mascotas(id),
|
|
veterinario_id UUID,
|
|
consultation_id UUID, -- Referencia a clinica.consultations
|
|
-- Datos de ingreso
|
|
fecha_ingreso TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
motivo_ingreso TEXT NOT NULL,
|
|
diagnostico_ingreso TEXT,
|
|
-- Ubicación
|
|
area VARCHAR(50), -- 'jaula_pequena', 'jaula_grande', 'quirofano', 'uci'
|
|
numero_jaula VARCHAR(20),
|
|
-- Estado
|
|
estado veterinaria.estado_hospitalizacion DEFAULT 'ingresado',
|
|
-- Alta
|
|
fecha_alta TIMESTAMPTZ,
|
|
diagnostico_alta TEXT,
|
|
instrucciones_alta TEXT,
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.hospitalizacion IS 'Registro de hospitalizaciones';
|
|
|
|
-- Monitoreo de hospitalización
|
|
CREATE TABLE IF NOT EXISTS veterinaria.hospitalizacion_monitoreo (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
hospitalizacion_id UUID NOT NULL REFERENCES veterinaria.hospitalizacion(id) ON DELETE CASCADE,
|
|
-- Signos vitales
|
|
fecha_hora TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
peso_kg NUMERIC(6,2),
|
|
temperatura NUMERIC(4,1),
|
|
frecuencia_cardiaca INTEGER,
|
|
frecuencia_respiratoria INTEGER,
|
|
-- Alimentación
|
|
comio BOOLEAN,
|
|
bebio_agua BOOLEAN,
|
|
-- Eliminación
|
|
orino BOOLEAN,
|
|
defeco BOOLEAN,
|
|
consistencia_heces VARCHAR(50),
|
|
-- Estado
|
|
estado_animo VARCHAR(50),
|
|
nivel_dolor INTEGER CHECK (nivel_dolor BETWEEN 0 AND 10),
|
|
-- Notas
|
|
observaciones TEXT,
|
|
registrado_por UUID, -- employee_id
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.hospitalizacion_monitoreo IS 'Monitoreo durante hospitalización';
|
|
|
|
-- Servicios de estética
|
|
CREATE TABLE IF NOT EXISTS veterinaria.estetica (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
mascota_id UUID NOT NULL REFERENCES veterinaria.mascotas(id),
|
|
estilista_id UUID, -- employee_id
|
|
-- Servicios
|
|
fecha_servicio TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
servicios TEXT[], -- ['baño', 'corte', 'limpieza_oidos', 'corte_unas']
|
|
tipo_corte VARCHAR(50),
|
|
shampoo_usado VARCHAR(100),
|
|
-- Estado
|
|
estado VARCHAR(20) DEFAULT 'pendiente', -- 'pendiente', 'en_proceso', 'terminado'
|
|
hora_inicio TIME,
|
|
hora_fin TIME,
|
|
-- Notas
|
|
observaciones TEXT,
|
|
observaciones_piel TEXT,
|
|
-- Precio
|
|
precio NUMERIC(10,2),
|
|
-- Control
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE veterinaria.estetica IS 'Servicios de estética/grooming';
|
|
|
|
-- ============================================================================
|
|
-- EXTENSIONES A TABLAS DE ERP-CLINICAS
|
|
-- ============================================================================
|
|
|
|
-- Extensión a clinica.consultations (si existe)
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables
|
|
WHERE table_schema = 'clinica' AND table_name = 'consultations') THEN
|
|
|
|
-- mascota_id
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'clinica' AND table_name = 'consultations'
|
|
AND column_name = 'mascota_id') THEN
|
|
ALTER TABLE clinica.consultations ADD COLUMN mascota_id UUID
|
|
REFERENCES veterinaria.mascotas(id);
|
|
END IF;
|
|
|
|
-- peso_actual
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'clinica' AND table_name = 'consultations'
|
|
AND column_name = 'peso_actual') THEN
|
|
ALTER TABLE clinica.consultations ADD COLUMN peso_actual NUMERIC(6,2);
|
|
END IF;
|
|
|
|
-- temperatura
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'clinica' AND table_name = 'consultations'
|
|
AND column_name = 'temperatura') THEN
|
|
ALTER TABLE clinica.consultations ADD COLUMN temperatura NUMERIC(4,1);
|
|
END IF;
|
|
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ============================================================================
|
|
-- ÍNDICES
|
|
-- ============================================================================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_especies_tenant ON veterinaria.especies(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_razas_tenant ON veterinaria.razas(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_razas_especie ON veterinaria.razas(especie_id);
|
|
CREATE INDEX IF NOT EXISTS idx_vacunas_tenant ON veterinaria.vacunas(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_vacunas_especie ON veterinaria.vacunas(especie_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_propietarios_tenant ON veterinaria.propietarios(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_propietarios_telefono ON veterinaria.propietarios(telefono);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_mascotas_tenant ON veterinaria.mascotas(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_mascotas_propietario ON veterinaria.mascotas(propietario_id);
|
|
CREATE INDEX IF NOT EXISTS idx_mascotas_especie ON veterinaria.mascotas(especie_id);
|
|
CREATE INDEX IF NOT EXISTS idx_mascotas_chip ON veterinaria.mascotas(numero_chip) WHERE numero_chip IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_cartilla_tenant ON veterinaria.cartilla_vacunacion(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cartilla_mascota ON veterinaria.cartilla_vacunacion(mascota_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cartilla_fecha ON veterinaria.cartilla_vacunacion(fecha_proximo_refuerzo);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desparasitaciones_tenant ON veterinaria.desparasitaciones(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_desparasitaciones_mascota ON veterinaria.desparasitaciones(mascota_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_hospitalizacion_tenant ON veterinaria.hospitalizacion(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_hospitalizacion_mascota ON veterinaria.hospitalizacion(mascota_id);
|
|
CREATE INDEX IF NOT EXISTS idx_hospitalizacion_estado ON veterinaria.hospitalizacion(tenant_id, estado);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_hospitalizacion_monitoreo_hosp ON veterinaria.hospitalizacion_monitoreo(hospitalizacion_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_estetica_tenant ON veterinaria.estetica(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_estetica_mascota ON veterinaria.estetica(mascota_id);
|
|
CREATE INDEX IF NOT EXISTS idx_estetica_fecha ON veterinaria.estetica(fecha_servicio);
|
|
|
|
-- ============================================================================
|
|
-- RLS
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE veterinaria.especies ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.razas ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.vacunas ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.propietarios ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.mascotas ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.cartilla_vacunacion ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.desparasitaciones ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.hospitalizacion ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.hospitalizacion_monitoreo ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE veterinaria.estetica ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_especies ON veterinaria.especies;
|
|
CREATE POLICY tenant_isolation_especies ON veterinaria.especies
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_razas ON veterinaria.razas;
|
|
CREATE POLICY tenant_isolation_razas ON veterinaria.razas
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_vacunas ON veterinaria.vacunas;
|
|
CREATE POLICY tenant_isolation_vacunas ON veterinaria.vacunas
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_propietarios ON veterinaria.propietarios;
|
|
CREATE POLICY tenant_isolation_propietarios ON veterinaria.propietarios
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_mascotas ON veterinaria.mascotas;
|
|
CREATE POLICY tenant_isolation_mascotas ON veterinaria.mascotas
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_cartilla ON veterinaria.cartilla_vacunacion;
|
|
CREATE POLICY tenant_isolation_cartilla ON veterinaria.cartilla_vacunacion
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_desparasitaciones ON veterinaria.desparasitaciones;
|
|
CREATE POLICY tenant_isolation_desparasitaciones ON veterinaria.desparasitaciones
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_hospitalizacion ON veterinaria.hospitalizacion;
|
|
CREATE POLICY tenant_isolation_hospitalizacion ON veterinaria.hospitalizacion
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_hosp_monitoreo ON veterinaria.hospitalizacion_monitoreo;
|
|
CREATE POLICY tenant_isolation_hosp_monitoreo ON veterinaria.hospitalizacion_monitoreo
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
DROP POLICY IF EXISTS tenant_isolation_estetica ON veterinaria.estetica;
|
|
CREATE POLICY tenant_isolation_estetica ON veterinaria.estetica
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
|
|
|
|
-- ============================================================================
|
|
-- FIN VETERINARIA SCHEMA
|
|
-- ============================================================================
|