dcdc15e162
- 11-compound-indices.sql: 14 compound indices for common queries - docs/JSONB-SCHEMAS.md: 50+ TypeScript interfaces documenting JSONB fields - DDL-EXECUTION-ORDER.md: Updated to include compound indices step Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
280 lines
8.4 KiB
Markdown
280 lines
8.4 KiB
Markdown
# DDL Execution Order - ERP Construccion
|
|
|
|
**Version:** 1.0.0
|
|
**Fecha:** 2026-02-03
|
|
**Proyecto:** erp-construccion
|
|
|
|
---
|
|
|
|
## Descripcion
|
|
|
|
Este documento define el orden correcto de ejecucion de archivos DDL para crear una base de datos limpia de ERP Construccion. Es fundamental seguir este orden debido a las dependencias entre schemas y tablas.
|
|
|
|
---
|
|
|
|
## Dependencias de Schema
|
|
|
|
### Diagrama de Dependencias
|
|
|
|
```
|
|
erp-core (auth, core, etc.)
|
|
|
|
|
+-- auth.tenants (REQUERIDO)
|
|
+-- auth.users (REQUERIDO)
|
|
|
|
|
v
|
|
erp-construccion
|
|
|
|
|
+-- init-scripts/01-init-database.sql
|
|
| |
|
|
| +-- core_shared (funciones)
|
|
| +-- core.tenants (fallback si erp-core no instalado)
|
|
| +-- core.users (fallback si erp-core no instalado)
|
|
| +-- Schemas: construction, estimates, infonavit, hse, hr, inventory, purchase
|
|
|
|
|
+-- schemas/01-construction-schema-ddl.sql
|
|
| |
|
|
| +-- VERIFICA: auth.tenants, auth.users
|
|
| +-- CREA: Schema construction (24 tablas)
|
|
|
|
|
+-- schemas/02-hr-schema-ddl.sql
|
|
+-- schemas/03-hse-schema-ddl.sql
|
|
+-- schemas/04-estimates-schema-ddl.sql
|
|
+-- schemas/05-infonavit-schema-ddl.sql
|
|
+-- schemas/06-inventory-ext-schema-ddl.sql
|
|
+-- schemas/07-purchase-ext-schema-ddl.sql
|
|
+-- schemas/08-finance-schema-ddl.sql
|
|
+-- schemas/09-assets-schema-ddl.sql
|
|
+-- schemas/10-documents-schema-ddl.sql
|
|
+-- schemas/99-rls-policies.sql
|
|
```
|
|
|
|
---
|
|
|
|
## Orden de Ejecucion
|
|
|
|
### Escenario A: Base de Datos Nueva (sin erp-core)
|
|
|
|
Cuando se crea una BD limpia SIN erp-core instalado previamente:
|
|
|
|
| Paso | Archivo | Descripcion |
|
|
|------|---------|-------------|
|
|
| 1 | `init-scripts/01-init-database.sql` | Extensiones, core_shared, schemas base, tablas fallback |
|
|
| 2 | `schemas/01-construction-schema-ddl.sql` | Schema construction (24 tablas, 7 ENUMs) |
|
|
| 3 | `schemas/02-hr-schema-ddl.sql` | Schema hr extension |
|
|
| 4 | `schemas/03-hse-schema-ddl.sql` | Schema hse (58 tablas, 67 ENUMs) |
|
|
| 5 | `schemas/04-estimates-schema-ddl.sql` | Schema estimates (8 tablas, 4 ENUMs) |
|
|
| 6 | `schemas/05-infonavit-schema-ddl.sql` | Schema infonavit (8 tablas) |
|
|
| 7 | `schemas/06-inventory-ext-schema-ddl.sql` | Extension inventory |
|
|
| 8 | `schemas/07-purchase-ext-schema-ddl.sql` | Extension purchase |
|
|
| 9 | `schemas/08-finance-schema-ddl.sql` | Schema finance (11 tablas, 20 ENUMs) |
|
|
| 10 | `schemas/09-assets-schema-ddl.sql` | Schema assets (11 tablas, 9 ENUMs) |
|
|
| 11 | `schemas/10-documents-schema-ddl.sql` | Schema documents (11 tablas, 8 ENUMs) |
|
|
| 12 | `schemas/11-compound-indices.sql` | Indices compound para queries frecuentes |
|
|
| 13 | `schemas/99-rls-policies.sql` | Politicas RLS para todos los schemas |
|
|
| 14 | `init-scripts/02-payment-terminals.sql` | Terminales de pago (opcional) |
|
|
|
|
### Escenario B: Base de Datos con erp-core (Recomendado)
|
|
|
|
Cuando erp-core YA esta instalado:
|
|
|
|
| Paso | Ubicacion | Archivo | Descripcion |
|
|
|------|-----------|---------|-------------|
|
|
| 1-N | erp-core | `01-auth-profiles.sql` ... `99-rls-erp-modules.sql` | Todos los DDL de erp-core |
|
|
| N+1 | erp-construccion | `init-scripts/01-init-database.sql` | Extensiones PostGIS, funciones |
|
|
| N+2 | erp-construccion | `schemas/01-construction-schema-ddl.sql` | Schema construction |
|
|
| ... | ... | ... | Resto de schemas construccion |
|
|
|
|
---
|
|
|
|
## Verificaciones Pre-Ejecucion
|
|
|
|
El archivo `01-construction-schema-ddl.sql` incluye verificaciones automaticas:
|
|
|
|
```sql
|
|
-- Verificar que ERP-Core esta instalado
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'auth') THEN
|
|
RAISE EXCEPTION 'Schema auth no existe. Ejecutar primero ERP-Core DDL';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'auth' AND tablename = 'tenants') THEN
|
|
RAISE EXCEPTION 'Tabla auth.tenants no existe. ERP-Core debe estar instalado';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'auth' AND tablename = 'users') THEN
|
|
RAISE EXCEPTION 'Tabla auth.users no existe. ERP-Core debe estar instalado';
|
|
END IF;
|
|
END $$;
|
|
```
|
|
|
|
---
|
|
|
|
## Naming de Schemas
|
|
|
|
### erp-core usa
|
|
|
|
| Schema | Descripcion |
|
|
|--------|-------------|
|
|
| `auth` | Autenticacion, tenants, users, roles |
|
|
| `core` | Sucursales, catalogos compartidos |
|
|
| `billing` | Facturacion SaaS |
|
|
| `inventory` | Inventario base |
|
|
| `partners` | Clientes/proveedores |
|
|
| `products` | Productos |
|
|
| `purchases` | Compras |
|
|
| `sales` | Ventas |
|
|
| `financial` | Contabilidad |
|
|
|
|
### erp-construccion usa
|
|
|
|
| Schema | Tipo | Descripcion |
|
|
|--------|------|-------------|
|
|
| `construction` | Propio | Proyectos, frentes, avances (24 tablas) |
|
|
| `estimates` | Propio | Estimaciones, generadores (8 tablas) |
|
|
| `infonavit` | Propio | Procesos INFONAVIT (8 tablas) |
|
|
| `hse` | Propio | Seguridad, salud, ambiente (58 tablas) |
|
|
| `hr` | Extension | RRHH extendido para construccion |
|
|
| `inventory` | Extension | Almacen de obra |
|
|
| `purchase` | Extension | Compras por proyecto |
|
|
| `finance` | Propio | Contabilidad construccion (11 tablas) |
|
|
| `assets` | Propio | Equipos, maquinaria (11 tablas) |
|
|
| `documents` | Propio | Gestion documental (11 tablas) |
|
|
|
|
### Schemas Legacy (Deprecados)
|
|
|
|
Los siguientes schemas se crean en `01-init-database.sql` para compatibilidad pero estan DEPRECADOS:
|
|
|
|
- `auth_management` -> Usar `auth`
|
|
- `project_management` -> Usar `construction`
|
|
- `financial_management` -> Usar `estimates`
|
|
- `purchasing_management` -> Usar `purchase`
|
|
- `inventory_management` -> Usar `inventory`
|
|
- `construction_management` -> Usar `construction`
|
|
- `quality_management` -> Usar `hse` o `construction`
|
|
- `safety_management` -> Usar `hse`
|
|
- `infonavit_management` -> Usar `infonavit`
|
|
|
|
---
|
|
|
|
## Comandos de Ejecucion WSL
|
|
|
|
### Recrear BD Completa (Recomendado)
|
|
|
|
```bash
|
|
# Desde Windows PowerShell
|
|
wsl -d Ubuntu-24.04 -u developer -- bash '/mnt/c/Empresas/ISEM/workspace-v2/projects/erp-construccion/database/drop-and-recreate-database.sh'
|
|
```
|
|
|
|
### Ejecutar Archivos Individuales
|
|
|
|
```bash
|
|
# Variables de conexion
|
|
export PGHOST=localhost
|
|
export PGPORT=5432
|
|
export PGDATABASE=erp_construccion_db
|
|
export PGUSER=erp_admin
|
|
export PGPASSWORD=erp_dev_2026
|
|
|
|
# Ruta base Windows desde WSL
|
|
BASE_PATH="/mnt/c/Empresas/ISEM/workspace-v2/projects/erp-construccion/database"
|
|
|
|
# 1. Inicializacion
|
|
psql -f "$BASE_PATH/init-scripts/01-init-database.sql"
|
|
|
|
# 2. Schemas en orden
|
|
for i in $(seq -w 1 10); do
|
|
f="$BASE_PATH/schemas/${i}-*.sql"
|
|
if ls $f 1>/dev/null 2>&1; then
|
|
psql -f $f
|
|
fi
|
|
done
|
|
|
|
# 3. RLS
|
|
psql -f "$BASE_PATH/schemas/99-rls-policies.sql"
|
|
```
|
|
|
|
### Ejecutar Solo ERP-Core (si separado)
|
|
|
|
```bash
|
|
# Ruta erp-core
|
|
CORE_PATH="/mnt/c/Empresas/ISEM/workspace-v2/projects/erp-core/database/ddl"
|
|
|
|
# Ejecutar todos los DDL de core en orden
|
|
for f in $(ls "$CORE_PATH"/*.sql | sort -V); do
|
|
psql -f "$f"
|
|
done
|
|
```
|
|
|
|
### Verificar Instalacion
|
|
|
|
```sql
|
|
-- Verificar schemas creados
|
|
SELECT schema_name
|
|
FROM information_schema.schemata
|
|
WHERE schema_name IN (
|
|
'auth', 'core', 'construction', 'estimates',
|
|
'infonavit', 'hse', 'hr', 'inventory',
|
|
'purchase', 'finance', 'assets', 'documents'
|
|
)
|
|
ORDER BY schema_name;
|
|
|
|
-- Contar tablas por schema
|
|
SELECT table_schema, COUNT(*) as table_count
|
|
FROM information_schema.tables
|
|
WHERE table_schema IN (
|
|
'auth', 'core', 'construction', 'estimates',
|
|
'infonavit', 'hse', 'hr', 'inventory',
|
|
'purchase', 'finance', 'assets', 'documents'
|
|
)
|
|
GROUP BY table_schema
|
|
ORDER BY table_schema;
|
|
```
|
|
|
|
---
|
|
|
|
## Troubleshooting
|
|
|
|
### Error: "Schema auth no existe"
|
|
|
|
**Causa:** Se ejecuto `01-construction-schema-ddl.sql` sin ejecutar `01-init-database.sql` primero, o sin instalar erp-core.
|
|
|
|
**Solucion:**
|
|
```bash
|
|
# Opcion A: Ejecutar init primero
|
|
psql -f init-scripts/01-init-database.sql
|
|
|
|
# Opcion B: Instalar erp-core completo primero
|
|
```
|
|
|
|
### Error: "Tabla auth.tenants no existe"
|
|
|
|
**Causa:** El init-database.sql usa fallback a `core.tenants` pero el construction DDL espera `auth.tenants`.
|
|
|
|
**Solucion:** El `01-init-database.sql` crea tablas en `core.*` como fallback. Para produccion, se recomienda instalar erp-core completo.
|
|
|
|
### Error: Extension postgis no disponible
|
|
|
|
**Causa:** PostGIS no esta instalado en el servidor PostgreSQL.
|
|
|
|
**Solucion:**
|
|
```bash
|
|
# En Ubuntu/Debian
|
|
sudo apt install postgresql-15-postgis-3
|
|
|
|
# Luego en psql
|
|
CREATE EXTENSION postgis;
|
|
```
|
|
|
|
---
|
|
|
|
## Referencias
|
|
|
|
- `HERENCIA-ERP-CORE.md` - Relacion con erp-core
|
|
- `_MAP.md` - Mapa completo de la BD
|
|
- `VALIDACION-DDL-INVENTARIOS.md` - Validaciones de inventario
|
|
- `../CLAUDE.md` - Instrucciones del proyecto
|
|
|
|
---
|
|
|
|
*Documentacion creada: 2026-02-03 - ST-P0-003*
|