246 lines
7.2 KiB
Markdown
246 lines
7.2 KiB
Markdown
# DISENO DE BASE DE DATOS - ERP CONSTRUCCION
|
|
|
|
**Fecha:** 2025-12-05
|
|
**DBMS:** PostgreSQL 15+ con PostGIS
|
|
**Patron:** Multi-Schema + RLS
|
|
**Total Schemas:** 12 schemas
|
|
**Alineado con:** ERP Generico v1.0
|
|
|
|
---
|
|
|
|
## Arquitectura Multi-Schema
|
|
|
|
Basado en ADR-007 y patron Gamilit. Extiende los schemas del ERP Generico con schemas especificos para construccion.
|
|
|
|
### Schemas del ERP Construccion
|
|
|
|
| Schema | Proposito | Modulos | Tablas | DDL Spec | Origen |
|
|
|--------|-----------|---------|--------|----------|--------|
|
|
| `auth` | Autenticacion y autorizacion | MAI-001, MAI-013 | 10 | Generico | GENERICO |
|
|
| `core` | Datos maestros | MAI-001 | 15 | Generico | GENERICO |
|
|
| `construction` | Obras, lotes, prototipos, avances | MAI-002, MAI-003, MAI-005 | 25 | [✓](schemas/DDL-SPEC-construction.md) | ESPECIFICO |
|
|
| `compliance` | Cumplimiento INFONAVIT | MAI-011 | 10 | [✓](schemas/DDL-SPEC-compliance.md) | ESPECIFICO |
|
|
| `finance` | Finanzas y Controlling | MAE-014 | 15 | [✓](schemas/DDL-SPEC-finance.md) | ESPECIFICO |
|
|
| `assets` | Activos y Maquinaria | MAE-015 | 12 | [✓](schemas/DDL-SPEC-assets.md) | ESPECIFICO |
|
|
| `documents` | Gestion Documental (DMS) | MAE-016 | 10 | [✓](schemas/DDL-SPEC-documents.md) | ESPECIFICO |
|
|
| `estimates` | Estimaciones y facturacion | MAI-008 | 12 | Pendiente | ESPECIFICO |
|
|
| `inventory` | Inventarios y almacenes | MAI-004 | 12 | Pendiente | GENERICO+EXT |
|
|
| `purchase` | Compras y requisiciones | MAI-004 | 10 | Pendiente | GENERICO+EXT |
|
|
| `hr` | RRHH, asistencias, destajo | MAI-007 | 10 | Pendiente | GENERICO+EXT |
|
|
| `quality` | Calidad y Postventa | MAI-009 | 8 | Pendiente | ESPECIFICO |
|
|
|
|
**Total Tablas:** ~139 tablas
|
|
**DDL Specs Completadas:** 5/12 (42%)
|
|
**Reutilizacion del ERP Generico:** 55%
|
|
|
|
---
|
|
|
|
## Convenciones de Nomenclatura
|
|
|
|
### Tablas
|
|
- snake_case
|
|
- Plural (ej: `fraccionamientos`, `lotes`, `estimaciones`)
|
|
- Prefijo de schema en queries (`construction.lotes`)
|
|
|
|
### Campos
|
|
- snake_case
|
|
- Sufijos estandar:
|
|
- `_id`: Foreign key
|
|
- `_at`: Timestamp
|
|
- `_by`: Usuario (creador, modificador)
|
|
- `_code`: Codigo unico de negocio (ej: `lote_code`)
|
|
|
|
### Constraints
|
|
- PK: `pk_{table}`
|
|
- FK: `fk_{table}_{referenced_table}`
|
|
- UNIQUE: `uq_{table}_{columns}`
|
|
- CHECK: `chk_{table}_{condition}`
|
|
|
|
---
|
|
|
|
## Patrones Estandar
|
|
|
|
### 1. Campos Obligatorios en TODA Tabla
|
|
|
|
```sql
|
|
CREATE TABLE schema.table_name (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
|
|
-- Soft delete
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
-- ... campos especificos
|
|
);
|
|
```
|
|
|
|
### 2. RLS Policy Estandar
|
|
|
|
```sql
|
|
-- Habilitar RLS
|
|
ALTER TABLE schema.table_name ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Policy: Solo ver datos de su tenant
|
|
CREATE POLICY tenant_isolation_policy
|
|
ON schema.table_name
|
|
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
|
|
```
|
|
|
|
### 3. Indices Estandar
|
|
|
|
```sql
|
|
-- Indice en tenant_id (SIEMPRE)
|
|
CREATE INDEX idx_{table}_tenant_id ON schema.table_name(tenant_id);
|
|
|
|
-- Indice en created_at (para queries ordenadas)
|
|
CREATE INDEX idx_{table}_created_at ON schema.table_name(created_at);
|
|
|
|
-- Indice compuesto para soft delete
|
|
CREATE INDEX idx_{table}_active ON schema.table_name(tenant_id, id)
|
|
WHERE deleted_at IS NULL;
|
|
```
|
|
|
|
---
|
|
|
|
## Estructura de Directorio
|
|
|
|
```
|
|
database-design/
|
|
+-- README.md (este archivo)
|
|
+-- database-roadmap.md
|
|
+-- schemas/
|
|
| +-- DDL-SPEC-construction.md [✓] MAI-002, MAI-003, MAI-005 (25 tablas)
|
|
| +-- DDL-SPEC-compliance.md [✓] MAI-011 INFONAVIT (10 tablas)
|
|
| +-- DDL-SPEC-finance.md [✓] MAE-014 Finanzas (15 tablas)
|
|
| +-- DDL-SPEC-assets.md [✓] MAE-015 Activos (12 tablas)
|
|
| +-- DDL-SPEC-documents.md [✓] MAE-016 DMS (10 tablas)
|
|
| +-- DDL-SPEC-estimates.md [ ] MAI-008 Estimaciones (pendiente)
|
|
| +-- DDL-SPEC-purchasing.md [ ] MAI-004 Compras (pendiente)
|
|
| +-- DDL-SPEC-hr.md [ ] MAI-007 RRHH (pendiente)
|
|
| +-- DDL-SPEC-quality.md [ ] MAI-009 Calidad (pendiente)
|
|
+-- migrations/ (no usado - ADR-011)
|
|
+-- seeds/
|
|
+-- construction-seeds.sql
|
|
```
|
|
|
|
---
|
|
|
|
## Estrategia de Base de Datos
|
|
|
|
### ADR-011: Database Clean Load Strategy
|
|
|
|
**NO SE USAN MIGRATIONS.** En su lugar:
|
|
|
|
1. **DDL Files:** Scripts completos por schema
|
|
2. **Reset Script:** `scripts/reset-database.sh`
|
|
3. **Seeds:** Datos iniciales por schema y ambiente
|
|
|
|
### Orden de Implementacion
|
|
|
|
Basado en dependencias entre schemas:
|
|
|
|
1. **auth** (CRITICO - sin dependencias)
|
|
- Referencia: ERP Generico
|
|
- Extensiones: roles construccion (director, residente, almacenista)
|
|
|
|
2. **core** (datos maestros)
|
|
- Referencia: ERP Generico
|
|
- Extensiones: unidades de medida construccion
|
|
|
|
3. **construction** (ESPECIFICO - depende de auth, core)
|
|
- Fraccionamientos, etapas, manzanas, lotes
|
|
- Torres, niveles, departamentos
|
|
- Prototipos, presupuestos, avances
|
|
|
|
4. **estimates** (ESPECIFICO - depende de construction)
|
|
- Estimaciones, generadores, anticipos, retenciones
|
|
|
|
5. **infonavit** (ESPECIFICO - depende de construction)
|
|
- Registros INFONAVIT, actas, reportes
|
|
|
|
6. **inventory** (GENERICO + extension)
|
|
- Almacen por proyecto
|
|
- Requisiciones de obra
|
|
|
|
7. **purchase** (GENERICO + extension)
|
|
- Requisiciones de obra
|
|
- Ordenes por proyecto
|
|
|
|
8. **hr** (GENERICO + extension)
|
|
- Asistencias GPS
|
|
- Destajo
|
|
|
|
---
|
|
|
|
## Dependencias entre Schemas
|
|
|
|
```
|
|
auth (base)
|
|
|
|
|
+-- core (datos maestros)
|
|
| |
|
|
| +-- construction (proyectos/obras)
|
|
| | |
|
|
| | +-- estimates (estimaciones)
|
|
| | +-- infonavit (cumplimiento)
|
|
| |
|
|
| +-- inventory (inventarios)
|
|
| | |
|
|
| | +-- purchase (compras)
|
|
| |
|
|
| +-- hr (recursos humanos)
|
|
```
|
|
|
|
---
|
|
|
|
## Proximos Pasos
|
|
|
|
### Completados
|
|
1. [x] Crear estructura database-design/ (Sprint 2.1)
|
|
2. [x] DDL-SPEC-construction.md - Proyectos, Presupuestos, Control de Obra
|
|
3. [x] DDL-SPEC-compliance.md - INFONAVIT Cumplimiento
|
|
4. [x] DDL-SPEC-finance.md - Finanzas y Controlling
|
|
5. [x] DDL-SPEC-assets.md - Activos y Maquinaria
|
|
6. [x] DDL-SPEC-documents.md - Gestion Documental
|
|
|
|
### Pendientes
|
|
7. [ ] DDL-SPEC-estimates.md - Estimaciones y Facturacion
|
|
8. [ ] DDL-SPEC-purchasing.md - Compras y Requisiciones
|
|
9. [ ] DDL-SPEC-hr.md - RRHH y Asistencias
|
|
10. [ ] DDL-SPEC-quality.md - Calidad y Postventa
|
|
11. [ ] Crear INVENTARIO-OBJETOS-BD.yml
|
|
|
|
---
|
|
|
|
## Resumen de Objetos de Base de Datos
|
|
|
|
| Schema | Tablas | ENUMs | Funciones | Triggers | RLS |
|
|
|--------|--------|-------|-----------|----------|-----|
|
|
| construction | 25 | 8 | 6 | 4 | ✓ |
|
|
| compliance | 10 | 6 | 4 | 3 | ✓ |
|
|
| finance | 15 | 7 | 8 | 5 | ✓ |
|
|
| assets | 12 | 5 | 5 | 4 | ✓ |
|
|
| documents | 10 | 5 | 4 | 3 | ✓ |
|
|
| **Total** | **72** | **31** | **27** | **19** | ✓ |
|
|
|
|
---
|
|
|
|
## Referencias
|
|
|
|
- [ADR-007: Database Design](../../adr/ADR-007-database-design.md)
|
|
- [ADR-011: Database Clean Load](../../adr/ADR-011-database-clean-load-strategy.md)
|
|
- [ERP Generico Database Design](/projects/erp-generic/docs/02-modelado/database-design/)
|
|
- [Mapeo MAI-MGN](../../00-analisis-referencias/MAPEO-MAI-TO-MGN.md)
|
|
|
|
---
|
|
|
|
**Ultima actualizacion:** 2025-12-05
|
|
**Version:** 1.1.0
|