Database Schema#

Database design and relationships in Construbot.

Overview#

Database: PostgreSQL 12+

Key model groups:

Users & Auth: User, Company, Customer, NivelAcceso
Contracts: Contrato (hierarchical), Contraparte, Sitio
Financials: Estimate, Concept, Retenciones
Configuration: Account settings, Units

Core Relationships#

Multi-Tenancy Structure#

Customer (1)
└── Company (N)
    └── User (N via M2M)

All business data → Company (FK)

Foreign keys:

Company → Customer
User ↔ Company (ManyToMany through UserCompany)
All business models → Company

Contract Hierarchy#

Contrato (uses django-treebeard)
├── path (Materialized Path)
├── depth
└── numchild

Parent Contract
├── Subcontract 1
│   └── Sub-subcontract 1.1
└── Subcontract 2

Tree operations:

get_children()
get_ancestors()
get_descendants()
move(target, pos)

Contract Relationships#

Contrato
├── company (FK) → Company
├── contraparte (FK) → Contraparte
├── sitio_trabajo (FK) → Sitio
├── destinatario (FK) → Destinatario
└── estimates (reverse FK) ← Estimate

Key Models Reference#

Users & Companies#

Customer:

class Customer(models.Model):
    nombre = CharField(max_length=200)
    slug = SlugField(unique=True)
    activo = BooleanField(default=True)

Company:

class Company(models.Model):
    customer = FK(Customer)
    nombre = CharField(max_length=200)
    slug = SlugField()
    # unique_together = ('customer', 'slug')

User:

class User(AbstractUser):
    email = EmailField(unique=True)  # USERNAME_FIELD
    companies = M2M(Company, through='UserCompany')
    active_company = FK(Company, null=True)
    nivel_acceso = FK(NivelAcceso)

Contracts & Counterparties#

Contrato:

class Contrato(MP_Node):  # Materialized Path tree
    company = FK(Company)
    folio = CharField(max_length=100)
    contraparte = FK(Contraparte)
    monto = DecimalField(max_digits=20, decimal_places=2)
    anticipo = DecimalField(...)  # Advance payment
    # unique_together = ('company', 'folio')

Contraparte:

class Contraparte(models.Model):
    company = FK(Company)
    nombre = CharField(max_length=200)
    tipo = CharField(choices=TIPO_CONTRAPARTE)
    # TIPO: CLIENTE, DESTAJISTA, SUBCONTRATISTA

Sitio:

class Sitio(models.Model):
    company = FK(Company)
    cliente = FK(Contraparte, limit_choices_to={'tipo': 'CLIENTE'})
    nombre = CharField(max_length=200)
    direccion = TextField()

Estimates & Concepts#

Estimate:

class Estimate(models.Model):
    company = FK(Company)
    contrato = FK(Contrato)
    numero_estimacion = IntegerField()
    fecha = DateField()
    monto = DecimalField(...)

Concept:

class Concept(models.Model):
    company = FK(Company)
    contrato = FK(Contrato)
    code = CharField(max_length=50)
    descripcion = TextField()
    unidad = FK(Units)
    precio_unitario = DecimalField(...)
    cantidad = DecimalField(...)

Indexes & Constraints#

Indexes#

Common patterns:

class Contrato(MP_Node):
    class Meta:
        indexes = [
            models.Index(fields=['company', 'folio']),
            models.Index(fields=['company', 'fecha_inicio']),
            models.Index(fields=['path']),  # Tree queries
        ]

Unique Constraints#

Company-scoped uniqueness:

class Meta:
    unique_together = [
        ('company', 'folio'),  # Unique per company
    ]

Database Queries#

Tree Queries#

Hierarchical data:

# Get all descendants
contract.get_descendants()

# Get children only
contract.get_children()

# Get ancestors
contract.get_ancestors()

Migrations#

Key migrations:

Initial: Create all models
django-treebeard: Add path/depth/numchild fields
Company scoping: Add company FK to all models
Unique constraints: Add unique_together

Run migrations:

python manage.py makemigrations
python manage.py migrate

Database Optimization#

Connection Pooling#

# In production settings
DATABASES['default']['CONN_MAX_AGE'] = 600  # 10 minutes

Atomic Transactions#

# Enabled by default
DATABASES['default']['ATOMIC_REQUESTS'] = True

Query Analysis#

# Django Debug Toolbar shows queries
# Or use django-silk for profiling