Database Deployment

The Calculus Learning Platform uses PostgreSQL as its relational database, hosting all user data, forum responses, exam submissions, and feedback records.

Current Database Configuration

The application is currently using a PostgreSQL database hosted on Render:

host="dpg-d4f80d7pm1nc73eop5h0-a.oregon-postgres.render.com"
database="database_7vyi"
user="database_7vyi_user"
password="EqKTvvDaJK6Ml5YoVYjnt4rLQmrxYIzo"
port=5432
sslmode="require"

These credentials are exposed in the source code. For production deployments, use environment variables and rotate these credentials immediately.

Database Schema

The database contains the following tables:

Users Table

Stores student and teacher accounts:

CREATE TABLE usuarios (
    email VARCHAR(255) PRIMARY KEY,
    password VARCHAR(255) NOT NULL,
    nombre VARCHAR(255),
    apellidos VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Passwords are currently stored in plaintext. This is a critical security vulnerability. Implement password hashing (bcrypt, argon2) before production use.

Forum Tables

The application has six forum tables for different learning activities:

Forums 1, 2, 4, 6 (Standard Structure)

CREATE TABLE respuestas_foro1 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE respuestas_foro2 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE respuestas_foro4 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    r7 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE respuestas_foro6 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    r7 TEXT DEFAULT '',
    r8 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Each forum has a different number of questions:

Forum 1: 6 questions (r1-r6)
Forum 2: 6 questions (r1-r6)
Forum 4: 7 questions (r1-r7)
Forum 6: 8 questions (r1-r8)

Forum 3 (Complex Table Structure)

Forum 3 has a special structure with a multi-row table (t6) in question 6:

CREATE TABLE respuestas_foro3 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    -- Table 6: 7 rows × 3 columns
    t6_r1_c1 TEXT DEFAULT '', t6_r1_c2 TEXT DEFAULT '', t6_r1_c3 TEXT DEFAULT '',
    t6_r2_c1 TEXT DEFAULT '', t6_r2_c2 TEXT DEFAULT '', t6_r2_c3 TEXT DEFAULT '',
    t6_r3_c1 TEXT DEFAULT '', t6_r3_c2 TEXT DEFAULT '', t6_r3_c3 TEXT DEFAULT '',
    t6_r4_c1 TEXT DEFAULT '', t6_r4_c2 TEXT DEFAULT '', t6_r4_c3 TEXT DEFAULT '',
    t6_r5_c1 TEXT DEFAULT '', t6_r5_c2 TEXT DEFAULT '', t6_r5_c3 TEXT DEFAULT '',
    t6_r6_c1 TEXT DEFAULT '', t6_r6_c2 TEXT DEFAULT '', t6_r6_c3 TEXT DEFAULT '',
    t6_r7_c1 TEXT DEFAULT '', t6_r7_c2 TEXT DEFAULT '', t6_r7_c3 TEXT DEFAULT '',
    r7 TEXT DEFAULT '',
    r8 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Forum 5 (Image Upload Support)

Forum 5 includes binary columns for image storage:

CREATE TABLE respuestas_foro5 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r2 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    imagen_pregunta_3 BYTEA,
    imagen_1 BYTEA,
    imagen_2 BYTEA,
    imagen_3 BYTEA,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Images are stored as BYTEA (binary data) and converted to base64 data URLs when retrieved by the API endpoint /respuestas_en_foro_5.

Exam Tables

CREATE TABLE examen1 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE examen2 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) REFERENCES usuarios(email),
    r1 TEXT DEFAULT '',
    r2 TEXT DEFAULT '',
    r3 TEXT DEFAULT '',
    r4 TEXT DEFAULT '',
    r5 TEXT DEFAULT '',
    r6 TEXT DEFAULT '',
    r7 TEXT DEFAULT '',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Feedback Table

Stores teacher feedback on student activities:

CREATE TABLE feedback (
    id SERIAL PRIMARY KEY,
    email_alumno VARCHAR(255) REFERENCES usuarios(email),
    actividad VARCHAR(100) NOT NULL,
    comentario TEXT NOT NULL,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Setting Up a New Database

Render PostgreSQL

Create a database instance

Log in to Render
Click “New +” → “PostgreSQL”
Choose a name and region
Select the free tier or appropriate plan
Click “Create Database”

Get connection details

After creation, Render provides:

Internal Database URL
External Database URL
Host, Database, User, Password
Port (default: 5432)

Use the External URL for connections from your backend server.

Configure SSL mode

Render requires SSL connections:

sslmode="require"

Database Migration

To migrate your database schema:

Export schema from existing database

pg_dump -h dpg-d4f80d7pm1nc73eop5h0-a.oregon-postgres.render.com \
        -U database_7vyi_user \
        -d database_7vyi \
        --schema-only > schema.sql

Create tables in new database

psql -h your-new-host \
     -U your-new-user \
     -d your-new-database \
     -f schema.sql

Migrate data (optional)

# Export data
pg_dump -h old-host -U old-user -d old-db --data-only > data.sql

# Import data
psql -h new-host -U new-user -d new-db -f data.sql

Connection Configuration

Using Environment Variables

Update your backend to use environment variables:

back/main.py

import os
import psycopg2
from psycopg2.pool import SimpleConnectionPool

# Create connection pool for better performance
db_pool = SimpleConnectionPool(
    1, 20,  # min and max connections
    host=os.getenv("DB_HOST"),
    database=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    port=int(os.getenv("DB_PORT", 5432)),
    sslmode=os.getenv("DB_SSLMODE", "require")
)

def conectar_bd():
    try:
        return db_pool.getconn()
    except Exception as e:
        print(f"Error conectando a la BD: {e}")
        return None

def cerrar_conexion(conn):
    db_pool.putconn(conn)

SSL Mode Configuration

PostgreSQL SSL modes:

Mode	Description	Use Case
`disable`	No SSL	Local development only
`allow`	Try SSL, fallback to non-SSL	Not recommended
`prefer`	Prefer SSL, fallback to non-SSL	Not recommended
`require`	Require SSL, no certificate verification	Render default
`verify-ca`	Require SSL, verify CA certificate	Enhanced security
`verify-full`	Require SSL, verify CA and hostname	Maximum security

For Render databases, use sslmode="require". For production with custom certificates, consider verify-full.

Database Indexes

Add indexes for better query performance:

-- Index on email for faster lookups
CREATE INDEX idx_usuarios_email ON usuarios(email);

CREATE INDEX idx_foro1_email ON respuestas_foro1(email);
CREATE INDEX idx_foro2_email ON respuestas_foro2(email);
CREATE INDEX idx_foro3_email ON respuestas_foro3(email);
CREATE INDEX idx_foro4_email ON respuestas_foro4(email);
CREATE INDEX idx_foro5_email ON respuestas_foro5(email);
CREATE INDEX idx_foro6_email ON respuestas_foro6(email);

CREATE INDEX idx_examen1_email ON examen1(email);
CREATE INDEX idx_examen2_email ON examen2(email);

CREATE INDEX idx_feedback_email ON feedback(email_alumno);
CREATE INDEX idx_feedback_actividad ON feedback(actividad);

-- Index on fecha for faster sorting
CREATE INDEX idx_foro1_fecha ON respuestas_foro1(fecha DESC);
CREATE INDEX idx_foro2_fecha ON respuestas_foro2(fecha DESC);
CREATE INDEX idx_foro3_fecha ON respuestas_foro3(fecha DESC);
CREATE INDEX idx_foro4_fecha ON respuestas_foro4(fecha DESC);
CREATE INDEX idx_foro5_fecha ON respuestas_foro5(fecha DESC);
CREATE INDEX idx_foro6_fecha ON respuestas_foro6(fecha DESC);

Backup and Recovery

Automated Backups (Render)

Render provides automated daily backups on paid plans. To manually backup:

pg_dump -h dpg-d4f80d7pm1nc73eop5h0-a.oregon-postgres.render.com \
        -U database_7vyi_user \
        -d database_7vyi \
        -F c -f backup_$(date +%Y%m%d).dump

Restore from Backup

pg_restore -h your-host \
           -U your-user \
           -d your-database \
           -v backup_20260305.dump

Custom Backup Script

backup.sh

#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)
FILENAME="calculus_db_${DATE}.sql.gz"

pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME | gzip > ${BACKUP_DIR}/${FILENAME}

# Keep only last 7 days
find ${BACKUP_DIR} -name "calculus_db_*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${FILENAME}"

Monitoring and Maintenance

Connection Monitoring

-- Check active connections
SELECT 
    datname,
    usename,
    application_name,
    client_addr,
    state,
    query_start
FROM pg_stat_activity
WHERE datname = 'database_7vyi';

Database Size

-- Check database size
SELECT 
    pg_size_pretty(pg_database_size('database_7vyi')) as db_size;

-- Check table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Vacuum and Analyze

Regularly optimize database performance:

-- Analyze tables for query planner
ANALYZE;

-- Vacuum to reclaim space
VACUUM;

-- Full vacuum (requires table lock)
VACUUM FULL;

Security Considerations

Credential management

Store credentials in environment variables
Use secrets management (AWS Secrets Manager, HashiCorp Vault)
Rotate credentials regularly
Never commit credentials to version control

Password security

Implement password hashing (bcrypt, argon2)
Add salt to prevent rainbow table attacks
Enforce password strength requirements
Consider multi-factor authentication

Access control

Create separate database users for different services
Grant minimum required privileges
Use read-only users for reporting/analytics
Restrict network access with firewall rules

Data encryption

Use SSL/TLS for connections (already required)
Consider encrypting sensitive columns
Enable database encryption at rest (if supported)

Troubleshooting

Connection Refused Errors

Verify the host and port are correct
Check if your IP is whitelisted (some providers restrict access)
Ensure SSL mode matches server requirements

SSL/TLS Errors

# If you see SSL certificate errors, try:
conn = psycopg2.connect(
    ...,
    sslmode="require",
    sslrootcert="/path/to/ca-certificate.crt"  # if needed
)

Performance Issues

Add indexes on frequently queried columns
Use connection pooling to reduce overhead
Analyze slow queries with EXPLAIN ANALYZE
Consider upgrading database instance size

Out of Connections

# Implement connection pooling
from psycopg2.pool import SimpleConnectionPool

pool = SimpleConnectionPool(minconn=1, maxconn=20, ...)

The current database implementation creates a new connection for each request and closes it immediately. For production, implement connection pooling for better performance.

Getting Started

Core Features

User Roles

Deployment

Current Database Configuration

Database Schema

Users Table

Forum Tables

Forums 1, 2, 4, 6 (Standard Structure)

Forum 3 (Complex Table Structure)

Forum 5 (Image Upload Support)

Exam Tables

Feedback Table

Setting Up a New Database

Render PostgreSQL

Other PostgreSQL Providers

Database Migration

Connection Configuration

Using Environment Variables

SSL Mode Configuration

Database Indexes

Backup and Recovery

Automated Backups (Render)

Restore from Backup

Custom Backup Script

Monitoring and Maintenance

Connection Monitoring

Database Size

Vacuum and Analyze

Security Considerations

Troubleshooting

Connection Refused Errors

SSL/TLS Errors

Performance Issues

Out of Connections

Build docs developers (and LLMs) love

Getting Started

Core Features

User Roles

Deployment

​Current Database Configuration

​Database Schema

​Users Table

​Forum Tables

​Forums 1, 2, 4, 6 (Standard Structure)

​Forum 3 (Complex Table Structure)

​Forum 5 (Image Upload Support)

​Exam Tables

​Feedback Table

​Setting Up a New Database

​Render PostgreSQL

​Other PostgreSQL Providers

​Database Migration

​Connection Configuration

​Using Environment Variables

​SSL Mode Configuration

​Database Indexes

​Backup and Recovery

​Automated Backups (Render)

​Restore from Backup

​Custom Backup Script

​Monitoring and Maintenance

​Connection Monitoring

​Database Size

​Vacuum and Analyze

​Security Considerations

​Troubleshooting

​Connection Refused Errors

​SSL/TLS Errors

​Performance Issues

​Out of Connections

Build docs developers (and LLMs) love

Current Database Configuration

Database Schema

Users Table

Forum Tables

Forums 1, 2, 4, 6 (Standard Structure)

Forum 3 (Complex Table Structure)

Forum 5 (Image Upload Support)

Exam Tables

Feedback Table

Setting Up a New Database

Render PostgreSQL

Other PostgreSQL Providers

Database Migration

Connection Configuration

Using Environment Variables

SSL Mode Configuration

Database Indexes

Backup and Recovery

Automated Backups (Render)

Restore from Backup

Custom Backup Script

Monitoring and Maintenance

Connection Monitoring

Database Size

Vacuum and Analyze

Security Considerations

Troubleshooting

Connection Refused Errors

SSL/TLS Errors

Performance Issues

Out of Connections