# GRAFO DE DEPENDENCIAS ENTRE SCHEMAS Y TABLAS

**Proyecto:** ERP Generic
**Fecha:** 2025-11-24
**Versión:** 1.0.0

---

## 1. Jerarquía de Schemas (Orden de Creación)

```
Nivel 0 (Base - Sin dependencias):
├── prerequisites (extensions, utility functions, composite types)

Nivel 1 (Foundations):
├── auth (tenants, companies, users, roles, permissions, sessions)
├── core (countries, currencies, uom_categories, uom)

Nivel 2 (Dependent on auth + core):
├── core.partners (FK → auth.tenants, auth.companies, auth.users, core.currencies)
├── core.addresses (FK → core.partners, core.countries)
├── core.product_categories (FK → auth.tenants)
├── financial.account_types (Sin FKs externas)

Nivel 3 (Dependent on Nivel 2):
├── analytics (FK → auth, core.partners, financial)
├── financial (FK → auth, core, analytics)
├── inventory (FK → auth, core)

Nivel 4 (Dependent on Nivel 3):
├── purchase (FK → auth, core, financial, inventory, analytics)
├── sales (FK → auth, core, financial, inventory, analytics)

Nivel 5 (Dependent on Nivel 4):
├── projects (FK → auth, core, analytics, inventory)

Nivel 6 (Cross-cutting):
├── system (FK → auth, core, todos los anteriores)
```

---

## 2. Diagrama de Dependencias por Schema

### Auth Schema (Nivel 1)
```
auth.tenants ─────────────────────────────────────────────────────┐
    │                                                              │
    ├── auth.companies (tenant_id, parent_company_id)             │
    │       └── FK: core.currencies(id)                            │
    │       └── FK: core.partners(id) [partner_id]                 │
    │                                                              │
    ├── auth.users (tenant_id)                                     │
    │                                                              │
    ├── auth.roles (tenant_id)                                     │
    │                                                              │
    └── auth.permissions (no tenant_id - global)                   │
                                                                   │
    auth.user_roles ──── FK: auth.users(id), auth.roles(id)       │
    auth.role_permissions ── FK: auth.roles(id), auth.permissions │
    auth.sessions ──── FK: auth.users(id)                          │
    auth.user_companies ── FK: auth.users(id), auth.companies(id) │
    auth.password_resets ── FK: auth.users(id)                     │
```

### Core Schema (Nivel 1-2)
```
Catálogos Base (Sin FKs externas):
├── core.countries
├── core.currencies
├── core.uom_categories
└── core.uom (FK → uom_categories)

Entidades Multi-tenant:
├── core.partners ─── FK: auth.tenants, auth.companies, auth.users,
│                     core.currencies, core.partners(parent)
│
├── core.addresses ─── FK: core.partners, core.countries
│
├── core.product_categories ─── FK: auth.tenants, self(parent_id)
│
├── core.exchange_rates ─── FK: core.currencies (from/to)
│
├── core.sequences ─── FK: auth.tenants, auth.companies
│
├── core.tags ─── FK: auth.tenants
│
├── core.attachments ─── FK: auth.tenants (polimórfico: model, record_id)
│
└── core.notes ─── FK: auth.tenants (polimórfico: model, record_id)
```

### Analytics Schema (Nivel 3)
```
analytics.analytic_plans ─── FK: auth.tenants, auth.companies
    │
    └── analytics.analytic_accounts ─── FK: auth.tenants, auth.companies,
            │                            analytics.analytic_plans,
            │                            self(parent_id), core.partners
            │
            ├── analytics.cost_centers ─── FK: analytics.analytic_accounts,
            │                              auth.users(manager)
            │
            └── analytics.analytic_lines ─── FK: auth.tenants, auth.companies,
                    │                         analytics.analytic_accounts,
                    │                         core.partners, core.currencies,
                    │                         inventory.products
                    │
                    └── analytics.analytic_line_tags ─── FK: analytic_lines,
                                                          analytic_tags

analytics.analytic_tags ─── FK: auth.tenants

analytics.analytic_distributions ─── FK: analytics.analytic_accounts
                                      (polimórfico: source_model, source_id)
```

### Financial Schema (Nivel 3)
```
financial.account_types (catálogo base)
    │
    └── financial.accounts ─── FK: auth.tenants, auth.companies,
            │                   financial.account_types, self(parent),
            │                   core.currencies
            │
            └── financial.journals ─── FK: auth.tenants, auth.companies,
                    │                   financial.accounts, core.sequences,
                    │                   core.currencies
                    │
                    └── financial.journal_entries ─── FK: auth.tenants,
                            │                          auth.companies,
                            │                          financial.journals
                            │
                            └── financial.journal_entry_lines ─── FK: journal_entries,
                                                                   financial.accounts,
                                                                   core.partners,
                                                                   core.currencies,
                                                                   analytics.analytic_accounts

financial.fiscal_years ─── FK: auth.tenants, auth.companies
    │
    └── financial.fiscal_periods ─── FK: auth.tenants, financial.fiscal_years

financial.taxes ─── FK: auth.tenants, auth.companies, financial.accounts

financial.payment_terms ─── FK: auth.tenants, auth.companies

financial.invoices ─── FK: auth.tenants, auth.companies, core.partners,
    │                   core.currencies, financial.payment_terms,
    │                   financial.journals, financial.journal_entries
    │
    └── financial.invoice_lines ─── FK: financial.invoices, core.uom,
                                     financial.accounts, analytics.analytic_accounts

financial.payments ─── FK: auth.tenants, auth.companies, core.partners,
    │                   core.currencies, financial.journals,
    │                   financial.journal_entries
    │
    └── financial.payment_invoice ─── FK: financial.payments, financial.invoices

financial.bank_accounts ─── FK: auth.tenants, auth.companies, core.partners,
    │                        core.currencies, financial.journals
    │
    └── financial.reconciliations ─── FK: auth.tenants, auth.companies,
                                       financial.bank_accounts
```

### Inventory Schema (Nivel 3)
```
inventory.products ─── FK: auth.tenants, core.product_categories, core.uom
    │
    ├── inventory.product_variants ─── FK: inventory.products
    │
    └── inventory.lots ─── FK: auth.tenants, inventory.products

inventory.warehouses ─── FK: auth.tenants, auth.companies, core.addresses
    │
    └── inventory.locations ─── FK: auth.tenants, inventory.warehouses,
            │                     self(parent_id)
            │
            └── inventory.stock_quants ─── FK: inventory.products,
                                            inventory.locations,
                                            inventory.lots

inventory.pickings ─── FK: auth.tenants, auth.companies, inventory.locations(x2),
    │                   core.partners
    │
    └── inventory.stock_moves ─── FK: auth.tenants, inventory.products,
                                   core.uom, inventory.locations(x2),
                                   inventory.lots, inventory.pickings,
                                   analytics.analytic_accounts

inventory.inventory_adjustments ─── FK: auth.tenants, auth.companies,
    │                                 inventory.locations
    │
    └── inventory.inventory_adjustment_lines ─── FK: adjustments,
                                                  inventory.products,
                                                  inventory.locations,
                                                  inventory.lots
```

### Purchase Schema (Nivel 4)
```
purchase.purchase_orders ─── FK: auth.tenants, auth.companies, core.partners,
    │                         core.currencies, financial.payment_terms,
    │                         inventory.pickings, financial.invoices
    │
    └── purchase.purchase_order_lines ─── FK: purchase_orders, inventory.products,
                                           core.uom, analytics.analytic_accounts

purchase.rfqs ─── FK: auth.tenants, auth.companies
    │
    └── purchase.rfq_lines ─── FK: rfqs, inventory.products, core.uom

purchase.vendor_pricelists ─── FK: auth.tenants, core.partners,
                                inventory.products, core.currencies

purchase.purchase_agreements ─── FK: auth.tenants, auth.companies, core.partners,
    │                             core.currencies
    │
    └── purchase.purchase_agreement_lines ─── FK: agreements, inventory.products

purchase.vendor_evaluations ─── FK: auth.tenants, auth.companies, core.partners
```

### Sales Schema (Nivel 4)
```
sales.pricelists ─── FK: auth.tenants, auth.companies, core.currencies
    │
    └── sales.pricelist_items ─── FK: pricelists, inventory.products,
                                   core.product_categories

sales.sales_teams ─── FK: auth.tenants, auth.companies, auth.users(leader)
    │
    └── sales.sales_team_members ─── FK: sales_teams, auth.users

sales.customer_groups ─── FK: auth.tenants
    │
    └── sales.customer_group_members ─── FK: customer_groups, core.partners

sales.quotations ─── FK: auth.tenants, auth.companies, core.partners,
    │                 core.currencies, sales.pricelists, auth.users,
    │                 sales.sales_teams, sales.sales_orders
    │
    └── sales.quotation_lines ─── FK: quotations, inventory.products, core.uom

sales.sales_orders ─── FK: auth.tenants, auth.companies, core.partners,
    │                   core.currencies, sales.pricelists, financial.payment_terms,
    │                   auth.users, sales.sales_teams, inventory.pickings
    │
    └── sales.sales_order_lines ─── FK: sales_orders, inventory.products,
                                     core.uom, analytics.analytic_accounts
```

### Projects Schema (Nivel 5)
```
projects.projects ─── FK: auth.tenants, auth.companies, auth.users(manager),
    │                  core.partners, analytics.analytic_accounts
    │
    ├── projects.project_stages ─── FK: auth.tenants, projects
    │
    ├── projects.milestones ─── FK: auth.tenants, projects
    │
    └── projects.tasks ─── FK: auth.tenants, projects, project_stages,
            │               auth.users(assigned), core.partners, self(parent),
            │               milestones
            │
            ├── projects.task_dependencies ─── FK: tasks(x2)
            │
            ├── projects.task_tag_assignments ─── FK: tasks, task_tags
            │
            └── projects.task_checklists ─── FK: tasks

projects.task_tags ─── FK: auth.tenants

projects.timesheets ─── FK: auth.tenants, auth.companies, projects.tasks,
                         projects.projects, auth.users,
                         analytics.analytic_accounts, analytics.analytic_lines

projects.project_templates ─── FK: auth.tenants
```

### System Schema (Nivel 6 - Cross-cutting)
```
system.messages ─── FK: auth.tenants, auth.users, self(parent)
                    (polimórfico: model, record_id)

system.message_followers ─── FK: core.partners, auth.users
                              (polimórfico: model, record_id)

system.notifications ─── FK: auth.tenants, auth.users
                          (polimórfico: model, record_id)

system.activities ─── FK: auth.tenants, auth.users(assigned_to, assigned_by)
                       (polimórfico: model, record_id)

system.message_templates ─── FK: auth.tenants

system.email_queue ─── FK: auth.tenants

system.logs ─── FK: auth.tenants, auth.users
                 (polimórfico: model, record_id)

system.reports ─── FK: auth.tenants
    │
    └── system.report_executions ─── FK: auth.tenants, reports

system.dashboards ─── FK: auth.tenants, auth.users
    │
    └── system.dashboard_widgets ─── FK: dashboards

system.field_tracking_config ─── (sin FKs - configuración global)

system.change_log ─── FK: auth.tenants, auth.users
                       (polimórfico: table_schema, table_name, record_id)
```

---

## 3. Orden de Ejecución DDL (Resuelve Dependencias)

```sql
-- FASE 1: Prerequisites (sin dependencias)
\i 00-prerequisites.sql

-- FASE 2: Schema auth (fundacional)
\i 01-auth.sql       -- Solo crea: tenants, roles, permissions, users básico

-- FASE 3: Schema core catálogos (sin FKs a auth.companies)
\i 02-core.sql       -- countries, currencies, uom_categories, uom

-- FASE 4: Schema auth completo (ahora puede referenciar core.currencies)
-- Ya incluido en 01-auth.sql con DEFERRED constraints

-- FASE 5: Core entidades (partners, addresses, etc.)
-- Ya incluido en 02-core.sql

-- FASE 6: Analytics (depende de auth + core)
\i 03-analytics.sql

-- FASE 7: Financial (depende de auth + core + analytics)
\i 04-financial.sql

-- FASE 8: Inventory (depende de auth + core)
\i 05-inventory.sql

-- FASE 9: Purchase (depende de todos los anteriores)
\i 06-purchase.sql

-- FASE 10: Sales (depende de todos los anteriores)
\i 07-sales.sql

-- FASE 11: Projects (depende de auth + core + analytics)
\i 08-projects.sql

-- FASE 12: System (cross-cutting, puede referenciar cualquier tabla)
\i 09-system.sql
```

---

## 4. Dependencias Circulares Identificadas

### Ciclo 1: auth.companies ↔ core.partners
```
auth.companies.partner_id → core.partners(id)
core.partners.company_id → auth.companies(id)
```
**Resolución:** FKs creadas con `DEFERRABLE INITIALLY DEFERRED`

### Ciclo 2: financial.invoices ↔ financial.journal_entries
```
financial.invoices.journal_entry_id → financial.journal_entries(id)
financial.journal_entry_lines.invoice_id → financial.invoices(id) [implícito]
```
**Resolución:** journal_entry_id es nullable, se asigna después de crear el asiento

### Ciclo 3: projects.tasks ↔ projects.milestones
```
projects.tasks.milestone_id → projects.milestones(id)
projects.milestones contiene tasks completadas
```
**Resolución:** milestone_id es nullable

---

## 5. Resumen de Dependencias

| Schema | Depende de | Depende de él |
|--------|------------|---------------|
| auth | - | core, analytics, financial, inventory, purchase, sales, projects, system |
| core | auth | analytics, financial, inventory, purchase, sales, projects, system |
| analytics | auth, core | financial, purchase, sales, projects |
| financial | auth, core, analytics | purchase, sales |
| inventory | auth, core | purchase, sales, projects |
| purchase | auth, core, financial, inventory, analytics | - |
| sales | auth, core, financial, inventory, analytics | - |
| projects | auth, core, analytics | - |
| system | auth, core | - |

---

## 6. Validación de Integridad

### Checks Realizados:
- [x] No hay dependencias circulares sin resolver
- [x] Todas las FKs tienen índices correspondientes
- [x] RLS policies aplicadas a todas las tablas multi-tenant
- [x] Triggers updated_at en todas las tablas con updated_at
- [x] Soft delete (deleted_at) consistente donde aplica

### Recomendaciones:
1. Ejecutar DDL en orden especificado
2. Usar `SET CONSTRAINTS ALL DEFERRED` para cargas masivas
3. Validar FKs después de migración: `SELECT * FROM pg_constraint WHERE NOT convalidated`

---

**Generado por:** Architecture-Analyst
**Fecha:** 2025-11-24