rckrdmrd/erp-core
1
0
Fork 0
Code Issues Pull Requests Actions 1 Packages Projects Releases Wiki Activity
main
erp-core/database/README.md

5.6 KiB
Raw Permalink Blame History

Database - ERP Generic

Version: 1.1.0 Database: PostgreSQL 15+ Schemas: 12 Tables: 118 Last Updated: 2025-12-06

Quick Start

Prerequisites

  • Docker & Docker Compose
  • PostgreSQL 15+ (or use Docker)
  • psql CLI

Setup

# 1. Start PostgreSQL with Docker
docker-compose up -d

# 2. Create database and run migrations
./scripts/create-database.sh

# 3. Load seed data (development)
./scripts/load-seeds.sh dev

Directory Structure

database/
├── ddl/                    # Data Definition Language (SQL schemas)
│   ├── 00-prerequisites.sql   # Extensions, common functions
│   ├── 01-auth.sql            # Authentication, users, roles
│   ├── 02-core.sql            # Partners, catalogs, master data
│   ├── 03-analytics.sql       # Analytic accounting
│   ├── 04-financial.sql       # Accounts, journals, invoices
│   ├── 05-inventory.sql       # Products, stock, warehouses
│   ├── 06-purchase.sql        # Purchase orders, vendors
│   ├── 07-sales.sql           # Sales orders, customers
│   ├── 08-projects.sql        # Projects, tasks, timesheets
│   ├── 09-system.sql          # Messages, notifications, logs
│   ├── 10-billing.sql         # SaaS subscriptions, plans, payments
│   ├── 11-crm.sql             # Leads, opportunities, pipeline
│   └── 12-hr.sql              # Employees, contracts, leaves
├── scripts/                # Shell scripts
│   ├── create-database.sh     # Master creation script
│   ├── drop-database.sh       # Drop database
│   ├── load-seeds.sh          # Load seed data
│   └── reset-database.sh      # Drop and recreate
├── seeds/                  # Initial data
│   ├── dev/                   # Development seeds
│   └── prod/                  # Production seeds
├── migrations/             # Incremental changes (empty by design)
├── docker-compose.yml      # PostgreSQL container
└── .env.example            # Environment variables template

Schemas

Schema Module Tables Description
auth MGN-001 10 Authentication, users, roles, permissions, multi-tenancy
core MGN-002, MGN-003 12 Partners, addresses, currencies, countries, UoM, categories
analytics MGN-008 7 Analytic plans, accounts, distributions, cost centers
financial MGN-004 15 Chart of accounts, journals, entries, invoices, payments
inventory MGN-005 10 Products, warehouses, locations, stock moves, pickings
purchase MGN-006 8 RFQs, purchase orders, vendor pricelists, agreements
sales MGN-007 10 Quotations, sales orders, pricelists, teams
projects MGN-011 10 Projects, tasks, milestones, timesheets
system MGN-012, MGN-014 13 Messages, notifications, activities, logs, reports
billing MGN-015 11 SaaS subscriptions, plans, payments, coupons
crm MGN-009 6 Leads, opportunities, pipeline, activities
hr MGN-010 6 Employees, departments, contracts, leaves

Execution Order

The DDL files must be executed in order due to dependencies:

  1. 00-prerequisites.sql - Extensions, base functions
  2. 01-auth.sql - Base schema (no dependencies)
  3. 02-core.sql - Depends on auth
  4. 03-analytics.sql - Depends on auth, core
  5. 04-financial.sql - Depends on auth, core, analytics
  6. 05-inventory.sql - Depends on auth, core, analytics
  7. 06-purchase.sql - Depends on auth, core, inventory, analytics
  8. 07-sales.sql - Depends on auth, core, inventory, analytics
  9. 08-projects.sql - Depends on auth, core, analytics
  10. 09-system.sql - Depends on auth, core
  11. 10-billing.sql - Depends on auth, core
  12. 11-crm.sql - Depends on auth, core, sales
  13. 12-hr.sql - Depends on auth, core

Features

Multi-Tenancy (RLS)

All transactional tables have:

  • tenant_id column
  • Row Level Security (RLS) policies
  • Context functions: get_current_tenant_id(), get_current_user_id()

Audit Trail

All tables include:

  • created_at, created_by
  • updated_at, updated_by
  • deleted_at, deleted_by (soft delete)

Automatic Triggers

  • updated_at auto-update on all tables
  • Balance validation for journal entries
  • Invoice totals calculation
  • Stock quantity updates

Environment Variables

# Database connection
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=erp_generic
POSTGRES_USER=erp_admin
POSTGRES_PASSWORD=your_secure_password

# Optional
POSTGRES_SCHEMA=public

Commands

# Create database from scratch (DDL only)
./scripts/create-database.sh

# Drop database
./scripts/drop-database.sh

# Reset (drop + create DDL + seeds dev) - RECOMENDADO
./scripts/reset-database.sh              # Pide confirmación
./scripts/reset-database.sh --force      # Sin confirmación (CI/CD)
./scripts/reset-database.sh --no-seeds   # Solo DDL, sin seeds
./scripts/reset-database.sh --env prod   # Seeds de producción

# Load seeds manualmente
./scripts/load-seeds.sh dev   # Development
./scripts/load-seeds.sh prod  # Production

NOTA: No se usan migrations. Ver DIRECTIVA-POLITICA-CARGA-LIMPIA.md para detalles.

Statistics

  • Schemas: 12
  • Tables: 144 (118 base + 26 extensiones)
  • DDL Files: 15
  • Functions: 63
  • Triggers: 92
  • Indexes: 450+
  • RLS Policies: 85+
  • ENUMs: 64
  • Lines of SQL: ~10,000

References