erp-core/docs/04-modelado/database-design

DISEÑO DE BASE DE DATOS - ERP GENÉRICO

Fecha: 2025-11-24 DBMS: PostgreSQL 15+ Patrón: Multi-Schema + RLS Total Schemas: 9 schemas

---

Arquitectura Multi-Schema

Basado en ADR-007 y patrón Gamilit (9 schemas).

Schemas del ERP Genérico

Schema	Propósito	Módulos	Tablas Estimadas
auth	Autenticación y autorización	MGN-001	8
core	Datos maestros	MGN-002, MGN-003	12
financial	Contabilidad	MGN-004	15
inventory	Inventario	MGN-005	10
purchase	Compras	MGN-006	8
sales	Ventas	MGN-007	10
analytics	Contabilidad analítica	MGN-008	6
projects	Proyectos	MGN-011	8
system	Sistema (logs, notifications)	MGN-012, MGN-014	8

Total Tablas: ~85 tablas

---

Convenciones de Nomenclatura

Tablas

• snake_case
• Plural (ej: users, purchase_orders)
• Prefijo de schema en queries (auth.users)

Campos

• snake_case
• Sufijos estándar:
  • _id: Foreign key
  • _at: Timestamp
  • _by: Usuario (creador, modificador)

Constraints

• PK: pk_{table}
• FK: fk_{table}_{referenced_table}
• UNIQUE: uq_{table}_{columns}
• CHECK: chk_{table}_{condition}

---

Patrones Estándar

1. Campos Obligatorios en TODA Tabla

CREATE TABLE schema.table_name (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

  -- Auditoría
  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 específicos
);

2. RLS Policy Estándar

-- 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. Índices Estándar

-- Índice en tenant_id (SIEMPRE)
CREATE INDEX idx_{table}_tenant_id ON schema.table_name(tenant_id);

-- Índice en created_at (para queries ordenadas)
CREATE INDEX idx_{table}_created_at ON schema.table_name(created_at);

-- Índice compuesto para soft delete
CREATE INDEX idx_{table}_active ON schema.table_name(tenant_id, id)
WHERE deleted_at IS NULL;

---

Funciones de Contexto

-- Obtener tenant actual
CREATE FUNCTION get_current_tenant_id()
RETURNS UUID AS $$
BEGIN
  RETURN current_setting('app.current_tenant_id', true)::UUID;
END;
$$ LANGUAGE plpgsql STABLE;

-- Obtener usuario actual
CREATE FUNCTION get_current_user_id()
RETURNS UUID AS $$
BEGIN
  RETURN current_setting('app.current_user_id', true)::UUID;
END;
$$ LANGUAGE plpgsql STABLE;

---

Estructura de Directorio

database-design/
├── README.md (este archivo)
├── database-roadmap.md
├── schemas/
│   ├── auth-schema-ddl.sql (✅ Completado)
│   ├── core-schema-ddl.sql (⏳ Pendiente)
│   ├── financial-schema-ddl.sql (⏳ Pendiente)
│   ├── inventory-schema-ddl.sql (⏳ Pendiente)
│   ├── purchase-schema-ddl.sql (⏳ Pendiente)
│   ├── sales-schema-ddl.sql (⏳ Pendiente)
│   ├── analytics-schema-ddl.sql (⏳ Pendiente)
│   ├── projects-schema-ddl.sql (⏳ Pendiente)
│   └── system-schema-ddl.sql (⏳ Pendiente)
├── migrations/
│   └── (migrations Prisma o TypeORM)
└── seeds/
    └── (datos iniciales por schema)

---

Estrategia de Migración

Herramienta: Prisma ORM

Razón: Basado en ADR-001 (Stack Tecnológico)

Workflow:

1. Schema DDL Manual:

   • Crear DDL completo por schema (auth, core, etc.)
   • Documentar funciones, triggers, RLS

2. Prisma Schema:

   • Generar schema.prisma desde DDL
   • O viceversa: generar DDL desde schema.prisma

3. Migrations:

   • prisma migrate dev para desarrollo
   • prisma migrate deploy para producción

4. Seeds:

   • Scripts de seed por schema
   • Datos iniciales: tenants, roles, permissions, currencies

---

Orden de Implementación

Basado en dependencias entre schemas:

1. auth (CRÍTICO - sin dependencias)

   • Tenants, Users, Roles, Permissions

2. core (datos maestros)

   • Companies, Partners, Currencies, Countries, UoM

3. Schemas transaccionales (en paralelo)

   • financial
   • inventory
   • purchase
   • sales

4. Schemas complementarios

   • analytics (depende de transaccionales)
   • projects (depende de core + analytics)
   • system (depende de todos)

---

Próximos Pasos

1. ✅ Crear DDL completo para schema auth (COMPLETADO)
2. ⏳ Crear DDL para schema core (datos maestros)
3. ⏳ Crear DDL para schemas transaccionales
4. ⏳ Generar Prisma schema desde DDL
5. ⏳ Crear migrations iniciales
6. ⏳ Crear scripts de seed

Ver: schemas/ para DDL detallados por schema Ver: database-roadmap.md para plan completo

---

Referencias

• ADR-001: Stack Tecnológico
• ADR-003: Multi-Tenancy
• ADR-007: Database Design
• Modelos de Dominio