Database Setup

Overview

AmbioSys uses PostgreSQL with PostGIS extension for geospatial features. The database is organized under the db_ambiotec schema and includes tables for users, service requests, quotations, customers, payments, and more.

The database setup includes DDL (schema definitions), DML (seed data), and stored procedures for business logic.

Database Requirements

PostgreSQL 14 or higher
PostGIS extension (for geolocation features)
pgcrypto extension (for UUID generation)
At least 2GB storage (10GB+ recommended for production)
Backup capabilities (pg_dump/pg_restore)

Database Architecture

Schema Structure

ambiotec_db
└── db_ambiotec (schema)
    ├── Users & Authentication
    │   ├── users
    │   ├── roles
    │   ├── user_roles
    │   ├── user_status
    │   └── user_access_log
    ├── Service Management
    │   ├── service_requests
    │   ├── request_sanitarios
    │   ├── request_fosas
    │   ├── request_ptar
    │   ├── service_orders
    │   └── service_request_assignments
    ├── Quotations & Sales
    │   ├── service_request_quotations
    │   ├── quotation_acceptances
    │   └── quotation_followups
    ├── Customers
    │   ├── customers
    │   └── company_name
    ├── Products & Catalog
    │   ├── service_type
    │   └── form_ptar_service
    ├── Payments & Credits
    │   ├── (defined in DDL_Payments.sql)
    │   └── (defined in DDL_Creditos.sql)
    ├── Notifications
    │   ├── notifications
    │   ├── notification_deliveries
    │   └── user_device_tokens
    ├── Permissions
    │   ├── modules
    │   ├── module_actions
    │   ├── user_module_permissions
    │   └── user_action_permissions
    └── Storage & Files
        ├── storage_files
        ├── service_request_media
        └── request_ptar_files

Initial Setup

Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib postgis
sudo systemctl start postgresql
sudo systemctl enable postgresql

Create database and user

# Connect as postgres superuser
sudo -u postgres psql

-- Create database
CREATE DATABASE ambiotec_db;

-- Create user
CREATE USER ambiotec_user WITH ENCRYPTED PASSWORD 'your-secure-password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE ambiotec_db TO ambiotec_user;

-- Connect to database
\c ambiotec_db

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO ambiotec_user;

-- Exit
\q

Enable required extensions

psql -U ambiotec_user -d ambiotec_db

-- Enable PostGIS for geolocation
CREATE EXTENSION IF NOT EXISTS postgis;

-- Enable pgcrypto for UUID generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Verify extensions
SELECT extname, extversion FROM pg_extension;

Create schema

-- Create application schema
CREATE SCHEMA IF NOT EXISTS db_ambiotec;

-- Set search path
ALTER DATABASE ambiotec_db SET search_path TO db_ambiotec, public;

-- Grant schema permissions
GRANT ALL ON SCHEMA db_ambiotec TO ambiotec_user;
GRANT ALL ON ALL TABLES IN SCHEMA db_ambiotec TO ambiotec_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA db_ambiotec TO ambiotec_user;

Running DDL Scripts

The database schema is defined in multiple DDL files located in DB/DDL/:

# Navigate to source directory
cd ~/workspace/source

# Run main DDL
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL.sql

# Run additional schemas
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Asignacion.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Catalogo.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Chat.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Creditos.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Customers.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Google.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_HorasExtra.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_MailHistory.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Payments.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_Products.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DDL/DDL_WhatsApp.sql

Loading Seed Data

Run DML scripts

# Load initial data
psql -U ambiotec_user -d ambiotec_db -f DB/DML/DML.sql
psql -U ambiotec_user -d ambiotec_db -f DB/DML/DML_Departments.sql

This inserts:

Default roles (admin, vendedor, gerente, cliente)
System modules and permissions
Customer statuses
Departments and zones (Guatemala regions)

Verify seed data

-- Check roles
SELECT * FROM db_ambiotec.roles;

-- Check modules
SELECT module_name, module_path FROM db_ambiotec.modules;

-- Check departments
SELECT department_name FROM db_ambiotec.departments;

Create admin user (optional)

-- Insert admin user (password: "admin123" hashed with bcrypt)
INSERT INTO db_ambiotec.users (
  username, email, password, first_name, last_name,
  role_id, user_status_id
) VALUES (
  'admin',
  '[email protected]',
  '$2b$10$YourBcryptHashHere',  -- Replace with actual hash
  'System',
  'Administrator',
  2,  -- admin role
  1   -- active status
);

Installing Stored Procedures

AmbioSys includes stored procedures for complex business logic:

# Install stored procedures
psql -U ambiotec_user -d ambiotec_db -f DB/Functions_StoredProcedures/SP.sql
psql -U ambiotec_user -d ambiotec_db -f DB/Functions_StoredProcedures/SP_Asignacion.sql
psql -U ambiotec_user -d ambiotec_db -f DB/Functions_StoredProcedures/SP_Auth.sql
psql -U ambiotec_user -d ambiotec_db -f DB/Functions_StoredProcedures/SP_Contacto.sql

Key Stored Procedures:

Assignment logic for service requests
Authentication and authorization helpers
Contact and customer management
Quotation generation workflows

Database Configuration

Connection Pooling

Configure PostgreSQL for optimal connection handling:

# Edit postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

# Connection Settings
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB
min_wal_size = 1GB
max_wal_size = 4GB

Allow Remote Connections

Only enable remote connections if necessary. Use SSL and strong authentication.

# Edit pg_hba.conf
sudo nano /etc/postgresql/14/main/pg_hba.conf

# Allow connections from Docker network
host    ambiotec_db    ambiotec_user    172.18.0.0/16    md5

# Allow specific IP (production server)
host    ambiotec_db    ambiotec_user    203.0.113.10/32  md5

# Edit postgresql.conf
listen_addresses = '*'  # or specific IP

# Restart PostgreSQL
sudo systemctl restart postgresql

Backup and Restore

Create Backup

# Backup entire database
pg_dump -U ambiotec_user -d ambiotec_db -F c -b -v -f "ambiotec_backup_$(date +%Y%m%d_%H%M%S).dump"

# Backup with compression
pg_dump -U ambiotec_user -d ambiotec_db | gzip > "ambiotec_backup_$(date +%Y%m%d).sql.gz"

Automated Backup Script

#!/bin/bash
# backup-database.sh

DB_USER="ambiotec_user"
DB_NAME="ambiotec_db"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/ambiotec_$DATE.dump"

# Create backup directory
mkdir -p $BACKUP_DIR

# Create backup
echo "Creating backup: $BACKUP_FILE"
pg_dump -U $DB_USER -d $DB_NAME -F c -b -v -f "$BACKUP_FILE"

if [ $? -eq 0 ]; then
  echo "✓ Backup created successfully"
  
  # Compress backup
  gzip "$BACKUP_FILE"
  
  # Delete backups older than 30 days
  find $BACKUP_DIR -name "ambiotec_*.dump.gz" -mtime +30 -delete
  
  echo "✓ Old backups cleaned up"
else
  echo "✗ Backup failed"
  exit 1
fi

Schedule Automatic Backups

# Add to crontab
crontab -e

# Daily backup at 2 AM
0 2 * * * /path/to/backup-database.sh >> /var/log/db-backup.log 2>&1

# Weekly full backup (Sundays at 3 AM)
0 3 * * 0 pg_dump -U ambiotec_user -d ambiotec_db -F c -f /backups/weekly_$(date +%Y%m%d).dump

Restore from Backup

# Restore from custom format backup
pg_restore -U ambiotec_user -d ambiotec_db -v ambiotec_backup_20260303.dump

# Clean and restore (WARNING: drops existing data)
pg_restore -U ambiotec_user -d ambiotec_db -c -v ambiotec_backup_20260303.dump

Database Migrations

For schema changes in production:

Create migration file

# Create migration directory
mkdir -p DB/migrations

# Create migration file
cat > DB/migrations/001_add_customer_preferences.sql <<EOF
-- Migration: Add customer preferences table
-- Date: 2026-03-03

BEGIN;

CREATE TABLE db_ambiotec.customer_preferences (
  preference_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES db_ambiotec.customers(customer_id),
  preference_key VARCHAR(100) NOT NULL,
  preference_value TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_customer_preferences_customer 
  ON db_ambiotec.customer_preferences(customer_id);

COMMIT;
EOF

Test migration

# Test on development database
psql -U ambiotec_user -d ambiotec_dev -f DB/migrations/001_add_customer_preferences.sql

# Verify changes
psql -U ambiotec_user -d ambiotec_dev -c "\d db_ambiotec.customer_preferences"

Apply to production

# Backup first!
pg_dump -U ambiotec_user -d ambiotec_db -F c -f pre_migration_backup.dump

# Apply migration
psql -U ambiotec_user -d ambiotec_db -f DB/migrations/001_add_customer_preferences.sql

# Verify
psql -U ambiotec_user -d ambiotec_db -c "SELECT COUNT(*) FROM db_ambiotec.customer_preferences"

Monitoring and Maintenance

Check Database Size

-- Database size
SELECT 
  pg_size_pretty(pg_database_size('ambiotec_db')) as database_size;

-- Table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'db_ambiotec'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

Active Connections

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

-- Kill idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'ambiotec_db'
  AND state = 'idle'
  AND query_start < NOW() - INTERVAL '1 hour';

Vacuum and Analyze

-- Vacuum all tables (reclaim storage)
VACUUM ANALYZE;

-- Vacuum specific table
VACUUM ANALYZE db_ambiotec.service_requests;

-- Full vacuum (requires exclusive lock)
VACUUM FULL ANALYZE db_ambiotec.user_access_log;

Index Maintenance

-- Find missing indexes
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'db_ambiotec'
  AND n_distinct > 100
ORDER BY n_distinct DESC;

-- Rebuild indexes
REINDEX TABLE db_ambiotec.service_requests;

-- Rebuild all indexes in schema
REINDEX SCHEMA db_ambiotec;

Troubleshooting

Connection Issues

# Test from command line
psql -U ambiotec_user -h localhost -d ambiotec_db -c "SELECT 1"

# Test from Docker container
docker-compose exec backend psql -h localhost -U ambiotec_user -d ambiotec_db

Permission Errors

-- Grant all permissions to user
GRANT ALL PRIVILEGES ON DATABASE ambiotec_db TO ambiotec_user;
GRANT ALL ON SCHEMA db_ambiotec TO ambiotec_user;
GRANT ALL ON ALL TABLES IN SCHEMA db_ambiotec TO ambiotec_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA db_ambiotec TO ambiotec_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA db_ambiotec TO ambiotec_user;

-- Set default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA db_ambiotec
  GRANT ALL ON TABLES TO ambiotec_user;

Performance Issues

-- Find slow queries
SELECT 
  pid,
  now() - query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 10;

-- Explain query
EXPLAIN ANALYZE
SELECT * FROM db_ambiotec.service_requests
WHERE current_status = 1
ORDER BY created_at DESC
LIMIT 50;

Data Corruption

# Check for corruption
psql -U ambiotec_user -d ambiotec_db -c "SELECT pg_database.datname, pg_database_size(pg_database.datname) FROM pg_database;"

# Repair corruption (if found)
pg_resetwal /var/lib/postgresql/14/main

# Restore from backup if severe
pg_restore -U ambiotec_user -d ambiotec_db -c -v backup.dump

Get Started

Deployment

Core Features

User Management

Overview

Database Requirements

Database Architecture

Schema Structure

Initial Setup

Running DDL Scripts

Loading Seed Data

Installing Stored Procedures

Database Configuration

Connection Pooling

Allow Remote Connections

Backup and Restore

Create Backup

Automated Backup Script

Schedule Automatic Backups

Restore from Backup

Database Migrations

Monitoring and Maintenance

Check Database Size

Active Connections

Vacuum and Analyze

Index Maintenance

Troubleshooting

Connection Issues

Permission Errors

Performance Issues

Data Corruption

Next Steps

Docker Setup

Environment Variables

Build docs developers (and LLMs) love

Get Started

Deployment

Core Features

User Management

​Overview

​Database Requirements

​Database Architecture

​Schema Structure

​Initial Setup

​Running DDL Scripts

​Loading Seed Data

​Installing Stored Procedures

​Database Configuration

​Connection Pooling

​Allow Remote Connections

​Backup and Restore

​Create Backup

​Automated Backup Script

​Schedule Automatic Backups

​Restore from Backup

​Database Migrations

​Monitoring and Maintenance

​Check Database Size

​Active Connections

​Vacuum and Analyze

​Index Maintenance

​Troubleshooting

​Connection Issues

​Permission Errors

​Performance Issues

​Data Corruption

​Next Steps

Docker Setup

Environment Variables

Build docs developers (and LLMs) love

Overview

Database Requirements

Database Architecture

Schema Structure

Initial Setup

Running DDL Scripts

Loading Seed Data

Installing Stored Procedures

Database Configuration

Connection Pooling

Allow Remote Connections

Backup and Restore

Create Backup

Automated Backup Script

Schedule Automatic Backups

Restore from Backup

Database Migrations

Monitoring and Maintenance

Check Database Size

Active Connections

Vacuum and Analyze

Index Maintenance

Troubleshooting

Connection Issues

Permission Errors

Performance Issues

Data Corruption

Next Steps