17 KiB
17 KiB
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)
-- 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:
- No hay dependencias circulares sin resolver
- Todas las FKs tienen índices correspondientes
- RLS policies aplicadas a todas las tablas multi-tenant
- Triggers updated_at en todas las tablas con updated_at
- Soft delete (deleted_at) consistente donde aplica
Recomendaciones:
- Ejecutar DDL en orden especificado
- Usar
SET CONSTRAINTS ALL DEFERREDpara cargas masivas - Validar FKs después de migración:
SELECT * FROM pg_constraint WHERE NOT convalidated
Generado por: Architecture-Analyst Fecha: 2025-11-24