Database Migrations

Overview

The database uses a migration-based approach for managing schema changes over time. Migration files are stored in the source/DB/Migrations/ directory.

Migration Strategy

File-Based Migrations

Each migration is a standalone SQL file that can be executed independently:

DB/Migrations/
├── add_reminders_module.sql
├── add_license_reminders.sql
├── add_mail_history.sql
├── add_public_name_to_users.sql
├── add_horas_extra_reports.sql
├── insert_sla_policies.sql
└── migrate_data_customers.sql

Naming Convention

Migration files follow a descriptive naming pattern:

add_{feature} - Adds new functionality
insert_{data} - Seeds or inserts data
migrate_{entity} - Data migration from legacy system
No timestamps in filenames (execution order managed manually)

Migration Types

Feature Additions

Migrations that add new modules or features to the system.

Example: add_reminders_module.sql

-- Add the reminders module to the system
INSERT INTO db_ambiotec.modules 
    (module_name, module_path, module_description, is_blocked)
SELECT 
    'recordatorios', 
    '/recordatorios', 
    'Control y gestión de recordatorios programados del sistema', 
    false
WHERE NOT EXISTS (
    SELECT 1 FROM db_ambiotec.modules WHERE module_path = '/recordatorios'
);

-- Grant permissions to admin role
INSERT INTO db_ambiotec.role_module_permissions 
    (role_id, module_id, can_view, is_blocked)
SELECT 2, m.module_id, true, false
FROM db_ambiotec.modules m
WHERE m.module_path = '/recordatorios'
AND NOT EXISTS (
    SELECT 1 FROM db_ambiotec.role_module_permissions rmp 
    WHERE rmp.role_id = 2 AND rmp.module_id = m.module_id
);

-- Verify insertion
SELECT module_id, module_name, module_path 
FROM db_ambiotec.modules 
WHERE module_path = '/recordatorios';

Key Features:

Uses WHERE NOT EXISTS to make migrations idempotent
Includes verification query at the end
Grants permissions to appropriate roles

Schema Modifications

Migrations that alter existing table structures.

Example: add_public_name_to_users.sql

-- Add public_name field for display purposes
ALTER TABLE db_ambiotec.users 
ADD COLUMN IF NOT EXISTS public_name VARCHAR(150);

-- Create computed column function
CREATE OR REPLACE FUNCTION db_ambiotec.get_user_public_name(p_user_id INT)
RETURNS TEXT AS $$
    SELECT COALESCE(
        NULLIF(public_name, ''),
        first_name || ' ' || last_name,
        username
    )
    FROM db_ambiotec.users
    WHERE user_id = p_user_id;
$$ LANGUAGE sql STABLE;

-- Update existing records
UPDATE db_ambiotec.users
SET public_name = first_name || ' ' || last_name
WHERE public_name IS NULL AND first_name IS NOT NULL;

Best Practices:

Use IF NOT EXISTS clauses
Provide default values or update existing records
Create helper functions when needed

Data Seeding

Migrations that insert reference or configuration data.

Example: insert_sla_policies.sql

-- Insert SLA policies for service request tracking
INSERT INTO db_ambiotec.sla_policies 
    (policy_id, policy_name, from_status_id, to_status_id, max_delay)
VALUES
    (1, 'Lead to Ops Review', 0, 2, '2 hours'::interval),
    (2, 'Ops Review to Quotation', 2, 3, '1 day'::interval),
    (3, 'Quotation to Approval', 3, 4, '3 days'::interval)
ON CONFLICT (policy_id) DO UPDATE SET
    policy_name = EXCLUDED.policy_name,
    max_delay = EXCLUDED.max_delay,
    updated_at = now();

Key Points:

Uses ON CONFLICT for upsert behavior
Defines SLA timelines as PostgreSQL intervals
Updates existing policies if schema changes

Data Migration from Legacy Systems

Migrations that transform and import data from old database schemas.

Example: migrate_data_customers.sql

-- Migrate customers from legacy tbl_clientes
INSERT INTO db_ambiotec.customers (
    customer_id,
    name,
    legal_name,
    tax_id,
    is_blocked,
    credit_days,
    credit_limit,
    contact_origin,
    notes,
    created_at,
    updated_at
)
SELECT
    cliente_id,
    cliente_descripcion,
    cliente_descripcion,
    NULL::text,
    cliente_bloqueado,
    cliente_dias_credito,
    0::numeric(12,2),
    NULL::text,
    NULLIF(cliente_incoterm,''),
    cliente_fecha,
    now()
FROM db_controlfac.tbl_clientes
ON CONFLICT (customer_id) DO NOTHING;

-- Migrate company names (razones sociales)
INSERT INTO db_ambiotec.company_name (
    company_name_id,
    customer_id,
    tax_id,
    name,
    description,
    is_active,
    address,
    billing_address,
    credit_days,
    credit_limit,
    created_at,
    updated_at
)
SELECT
    razon_social_id,
    razon_social_cliente_id,
    NULLIF(razon_social_nit,''),
    razon_social_descripcion,
    razon_social_descripcion,
    NOT razon_social_bloqueada,
    razon_social_direccion,
    razon_social_direccion_cobro,
    razon_social_dias_credito,
    razon_social_limite_credito,
    razon_social_fecha,
    razon_social_fecha_actualizado
FROM db_controlfac.tbl_razon_social
ON CONFLICT (company_name_id) DO NOTHING;

Migration Patterns:

Maps old column names to new schema
Handles NULL values with NULLIF and COALESCE
Uses ON CONFLICT DO NOTHING for idempotency
Preserves original IDs when possible

Table Creation

Some migrations create entirely new tables.

Example: add_mail_history.sql

-- Create mail history tracking table
CREATE TABLE IF NOT EXISTS db_ambiotec.mail_history (
    mail_id BIGSERIAL PRIMARY KEY,
    recipient_email TEXT NOT NULL,
    recipient_user_id INT REFERENCES db_ambiotec.users(user_id),
    subject TEXT NOT NULL,
    body_html TEXT,
    body_text TEXT,
    from_address TEXT NOT NULL,
    reply_to TEXT,
    cc_addresses TEXT[],
    bcc_addresses TEXT[],
    attachments JSONB,
    status VARCHAR(20) DEFAULT 'queued',
    sent_at TIMESTAMPTZ,
    error_message TEXT,
    provider VARCHAR(50),
    provider_message_id TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_mail_history_recipient 
    ON db_ambiotec.mail_history(recipient_email);
CREATE INDEX IF NOT EXISTS idx_mail_history_status 
    ON db_ambiotec.mail_history(status);
CREATE INDEX IF NOT EXISTS idx_mail_history_sent 
    ON db_ambiotec.mail_history(sent_at);

-- Add comment
COMMENT ON TABLE db_ambiotec.mail_history IS 
    'Email delivery tracking with status and provider information';

Execution Order

Migrations should generally be executed in this order:

Schema modifications - ALTER TABLE statements
Table creation - New tables and indexes
Data seeding - Reference data and catalogs
Feature additions - Module and permission setup
Data migrations - Import from legacy systems

Idempotency

All migrations should be idempotent (safe to run multiple times):

Table/Column Creation

CREATE TABLE IF NOT EXISTS ...
ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...

Data Insertion

-- Check existence before insert
INSERT INTO ... 
WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...);

-- Or use upsert
INSERT INTO ... 
ON CONFLICT (...) DO UPDATE SET ...;

Function/Trigger Creation

CREATE OR REPLACE FUNCTION ...
DROP TRIGGER IF EXISTS ... ON ...;
CREATE TRIGGER ...

Running Migrations

Manual Execution

Migrations are typically run manually via psql:

# Connect to database
psql -U postgres -d ambiotec_db

# Execute migration
\i /path/to/DB/Migrations/add_reminders_module.sql

# Verify results
SELECT * FROM db_ambiotec.modules WHERE module_path = '/recordatorios';

Batch Execution

For multiple migrations:

# Execute all migrations in order
for file in DB/Migrations/*.sql; do
    echo "Running $file..."
    psql -U postgres -d ambiotec_db -f "$file"
done

Rollback Strategy

While migrations don’t include automatic rollback scripts, you can create reverse migrations:

-- Original: add_reminders_module.sql
INSERT INTO db_ambiotec.modules ...

-- Rollback: remove_reminders_module.sql
DELETE FROM db_ambiotec.modules WHERE module_path = '/recordatorios';

Best Practices

Test First

Always test migrations on a development/staging database before production.

Make Idempotent

Use IF NOT EXISTS, ON CONFLICT, and WHERE NOT EXISTS clauses.

Document Changes

Include comments explaining why the migration is needed.

Backup Data

Take a database backup before running migrations in production.

Version Control

Migration files are version controlled in the Git repository:

source/DB/Migrations/
├── README.md (optional)
└── *.sql migration files

Commit messages should describe:

What the migration does
Why it’s needed
Any dependencies or prerequisites

Migration Tracking

Consider creating a migration tracking table:

CREATE TABLE IF NOT EXISTS db_ambiotec.schema_migrations (
    migration_id SERIAL PRIMARY KEY,
    filename TEXT NOT NULL UNIQUE,
    executed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    executed_by TEXT,
    checksum TEXT
);

Then track each migration:

INSERT INTO db_ambiotec.schema_migrations (filename, executed_by)
VALUES ('add_reminders_module.sql', current_user)
ON CONFLICT (filename) DO NOTHING;

Schema

Migrations

Database Migrations

Overview

Migration Strategy

File-Based Migrations

Naming Convention

Migration Types

Example: add_reminders_module.sql

Example: add_public_name_to_users.sql

Example: insert_sla_policies.sql

Example: migrate_data_customers.sql

Example: add_mail_history.sql

Execution Order

Idempotency

Table/Column Creation

Data Insertion

Function/Trigger Creation

Running Migrations

Manual Execution

Batch Execution

Rollback Strategy

Best Practices

Test First

Make Idempotent

Document Changes

Backup Data

Version Control

Migration Tracking

Next Steps

Seeding Data

Table Schemas

Build docs developers (and LLMs) love

Schema

Migrations

​Overview

​Migration Strategy

​File-Based Migrations

​Naming Convention

​Migration Types

​Example: add_reminders_module.sql

​Example: add_public_name_to_users.sql

​Example: insert_sla_policies.sql

​Example: migrate_data_customers.sql

​Example: add_mail_history.sql

​Execution Order

​Idempotency

​Table/Column Creation

​Data Insertion

​Function/Trigger Creation

​Running Migrations

​Manual Execution

​Batch Execution

​Rollback Strategy

​Best Practices

Test First

Make Idempotent

Document Changes

Backup Data

​Version Control

​Migration Tracking

​Next Steps

Seeding Data

Table Schemas

Build docs developers (and LLMs) love

Overview

Migration Strategy

File-Based Migrations

Naming Convention

Migration Types

Example: add_reminders_module.sql

Example: add_public_name_to_users.sql

Example: insert_sla_policies.sql

Example: migrate_data_customers.sql

Example: add_mail_history.sql

Execution Order

Idempotency

Table/Column Creation

Data Insertion

Function/Trigger Creation

Running Migrations

Manual Execution

Batch Execution

Rollback Strategy

Best Practices

Version Control

Migration Tracking

Next Steps