172 lines
5.6 KiB
Markdown
172 lines
5.6 KiB
Markdown
# 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
|
|
|
|
```bash
|
|
# 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
|
|
|
|
```bash
|
|
# 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
|
|
|
|
```bash
|
|
# 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
|
|
|
|
- [ADR-007: Database Design](/docs/adr/ADR-007-database-design.md)
|
|
- [Gamilit Database Reference](/shared/reference/gamilit/database/)
|
|
- [Odoo Analysis](/docs/00-analisis-referencias/odoo/)
|