erp-core/docs/04-modelado/database-design/README.md

# 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

```sql
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

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

```sql
-- Í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

```sql
-- 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/ADR-001-stack-tecnologico.md)
- [ADR-003: Multi-Tenancy](../../adr/ADR-003-multi-tenancy.md)
- [ADR-007: Database Design](../../adr/ADR-007-database-design.md)
- [Modelos de Dominio](../domain-models/)