# 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/)