Database Configuration

Overview

SASCOP BME SubTec uses PostgreSQL as its primary database. The application supports both local development and production configurations through environment variables.

The database configuration is defined in bme_subtec/settings.py:82-94:

bme_subtec/settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.environ.get('RDS_DB_NAME', 'postgres'),
        'USER': os.environ.get('RDS_USERNAME', 'postgres'),
        'PASSWORD': os.environ.get('RDS_PASSWORD', ''),
        'HOST': os.environ.get('RDS_HOSTNAME', 'localhost'),
        'PORT': os.environ.get('RDS_PORT', '5432'),
        'OPTIONS': {
            'sslmode': 'require',
        }
    }
}

The configuration uses environment variables with sensible defaults for local development.

Environment Variables

Configure these environment variables in your .env file:

Variable	Description	Default	Required
`RDS_DB_NAME`	Database name	`postgres`	Yes
`RDS_USERNAME`	Database user	`postgres`	Yes
`RDS_PASSWORD`	Database password	Empty string	Yes
`RDS_HOSTNAME`	Database host	`localhost`	Yes
`RDS_PORT`	Database port	`5432`	No

Local Development Setup

Create Local Database

For local development, create a PostgreSQL database:

createdb sascop_local

Or using psql:

CREATE DATABASE sascop_local;
CREATE USER sascop_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE sascop_local TO sascop_user;

Configure Environment

Create a .env file with local database settings:

.env

RDS_DB_NAME=sascop_local
RDS_USERNAME=postgres
RDS_PASSWORD=root
RDS_HOSTNAME=localhost
RDS_PORT=5432

Apply Migrations

Run Django migrations to create all necessary tables:

python manage.py migrate

This will create tables for all installed apps:

operaciones - Core operations management
core - Module system and utilities
costa_fuera - Offshore operations (if enabled)
reportes - Reporting system (if enabled)

Alternative Local Configuration

The codebase includes a commented-out alternative for pure local development in bme_subtec/settings.py:97-106:

bme_subtec/settings.py

# Uncomment for local development without environment variables
# DATABASES = {
#     'default': {
#         'ENGINE': 'django.db.backends.postgresql',
#         'NAME': 'sascop_local',       
#         'USER': 'postgres',           
#         'PASSWORD': 'root',    
#         'HOST': 'localhost',         
#         'PORT': '5433',
#     }
# }

This hardcoded configuration is only suitable for local development. Never use it in production.

Production Configuration

For production deployments (AWS RDS, Vercel Postgres, etc.):

SSL Connection

The production configuration requires SSL connections:

'OPTIONS': {
    'sslmode': 'require',
}

Ensure your database provider supports SSL connections.

Set Production Variables

Configure production environment variables in your hosting platform:

RDS_DB_NAME=production_db
RDS_USERNAME=production_user
RDS_PASSWORD=strong_secure_password
RDS_HOSTNAME=your-db-instance.region.rds.amazonaws.com
RDS_PORT=5432

Run Migrations

After deploying, run migrations on the production database:

python manage.py migrate --settings=bme_subtec.settings

Database Models

The application uses several model categories:

Core Models

Module System (core/models.py:4-17):

core/models.py

class Modulo(models.Model):
    """Sistema de módulos"""
    app_name = models.CharField(max_length=50, unique=True)
    nombre = models.CharField(max_length=100)
    descripcion = models.TextField()
    activo = models.BooleanField(default=True)
    orden = models.IntegerField(default=0)
    icono = models.CharField(max_length=50, default='apps')
    
    class Meta:
        db_table = 'core_modulo'

Operations Models

The operaciones app contains several model files:

catalogos_models.py - Catalog entities (Tipo, Frente, Estatus, Sitio, etc.)
pte_models.py - PTE (Project Task Entry) management
ote_models.py - OTE (Work Order) management
produccion_models.py - Production tracking and estimations
registro_actividad_models.py - Activity logging

See Data Models for detailed information.

Database Maintenance

Creating Migrations

When you modify models, create new migrations:

python manage.py makemigrations

For a specific app:

python manage.py makemigrations operaciones

Viewing Migration Status

Check which migrations have been applied:

python manage.py showmigrations

Rolling Back Migrations

To revert to a specific migration:

python manage.py migrate operaciones 0001

Database Shell

Access the database shell for direct queries:

python manage.py dbshell

Connection Pooling

For high-traffic production environments, consider implementing connection pooling using django-db-connection-pool or pgBouncer.

Backup and Restore

Backup Database

pg_dump -h localhost -U postgres sascop_local > backup.sql

Restore Database

psql -h localhost -U postgres sascop_local < backup.sql

Django Fixtures

Export data as JSON fixtures:

python manage.py dumpdata operaciones > operaciones_data.json

Load fixtures:

python manage.py loaddata operaciones_data.json

Troubleshooting

Connection Refused

If you see “connection refused” errors:

Verify PostgreSQL is running:
```
sudo systemctl status postgresql
```
Check if PostgreSQL is listening on the correct port:
```
sudo netstat -plntu | grep postgres
```
Verify pg_hba.conf allows connections from your IP

SSL Connection Issues

For SSL-related errors in production:

Download the RDS certificate bundle (for AWS)
Configure SSL certificate path in database settings
Verify SSL is enabled on your database instance

Performance Issues

Enable query logging to identify slow queries:

LOGGING = {
    'version': 1,
    'handlers': {
        'console': {
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'level': 'DEBUG',
        },
    },
}

Create database indexes for frequently queried fields
Use select_related() and prefetch_related() to optimize queries

Next Steps

Data Models

Explore the complete data model structure

Database Migrations

Learn about managing migrations in production

Setup

Architecture

Deployment

Database Configuration

Overview