Database Seeding

Overview

Database seeding refers to populating the database with initial reference data, catalogs, and system configurations required for the application to function properly. Seed data is stored in the source/DB/DML/ directory.

Seed Files

DB/DML/
├── DML.sql              # Main seed file with core data
└── DML_Departments.sql  # Geographic data for Guatemala

Core System Data

User Roles

Defines the system roles for access control.

INSERT INTO db_ambiotec.roles 
    (role_id, role_name, role_key, description, created_by, created_at) 
VALUES
    (1, 'vendedor', 'vend', NULL, NULL, '2025-11-01 23:02:54.320'),
    (2, 'admin', 'admin', NULL, NULL, '2025-11-01 23:02:54.320'),
    (3, 'gerente', 'geren', NULL, NULL, '2025-11-01 23:02:54.320'),
    (4, 'cliente', 'client', NULL, NULL, '2025-11-01 23:02:54.320'),
    (5, 'recepcionista', 'recep', NULL, NULL, '2025-11-01 23:02:54.320'),
    (6, 'supervisor', 'superv', NULL, NULL, '2025-11-01 23:02:54.320'),
    (7, 'operaciones', 'ops', NULL, NULL, '2025-11-01 23:02:54.320'),
    (8, 'cobros', 'cobros', NULL, NULL, '2025-11-01 23:02:54.320');

Role Descriptions:

admin - Full system access and configuration
vendedor - Sales representative, manages leads and quotes
gerente - Manager, approves requests and reviews
cliente - Customer portal access
recepcionista - Receptionist, basic data entry
supervisor - Oversees operations
operaciones - Operations team, service fulfillment
cobros - Collections team, payment management

Customer Status

Defines possible customer account states.

INSERT INTO db_ambiotec.customer_status 
    (customer_status_id, customer_status_name)
VALUES
    (1, 'Activo'), 
    (2, 'Inactivo'), 
    (3, 'Suspendido');

Status Meanings:

Activo - Active customer, can place orders
Inactivo - Inactive customer, no recent activity
Suspendido - Suspended, blocked from new orders

User Status

Defines user account states.

INSERT INTO db_ambiotec.user_status 
    (user_status_id, user_status_name)
VALUES
    (1, 'Activo'), 
    (2, 'Inactivo'), 
    (3, 'Suspendido');

License Types

Driver’s license categories for Guatemala.

INSERT INTO db_ambiotec.license_type 
    (license_type_id, license_type_name, license_type_description) 
VALUES
    (1, 'A', 'Para conducir toda clase de vehículos de transporte de carga de más de 3.5 toneladas métricas'),
    (2, 'B', 'Para conducir toda clase de automóviles hasta 3.5 toneladas métricas, pudiendo recibir remuneración'),
    (3, 'C', 'Para conducir automóviles, paneles, microbuses, pick-ups hasta 3.5 toneladas sin remuneración'),
    (4, 'M', 'Para conducir toda clase de motobicicletas y motocicletas'),
    (5, 'E', 'Para conducir maquinaria agrícola e industrial');

License Categories:

Type A - Heavy cargo transport (>3.5 tons)
Type B - Professional driver (≤3.5 tons)
Type C - Personal vehicles (≤3.5 tons)
Type M - Motorcycles
Type E - Agricultural/industrial machinery

Document Types

Valid identification document types.

INSERT INTO db_ambiotec.document_type 
    (document_type_id, document_type_name)
VALUES
    (1, 'DPI'),      -- Documento Personal de Identificación
    (2, 'Pasaporte');

System Modules

Application Modules

Defines the main application modules for the navigation system.

INSERT INTO db_ambiotec.modules 
    (module_id, module_name, module_path, module_description, is_blocked) 
VALUES
    (1, 'clientes', '/clientes', 'Módulo de administración de clientes', false),
    (2, 'ventas', '/ventas', 'Módulo de ventas', false),
    (3, 'chat', '/chat', 'Chat personal para contacto directo con el cliente', false),
    (4, 'perfil', '/perfil', 'Administración de perfil de usuario', false),
    (5, 'permisosConfiguraciones', '/permisos', 'Configuración y modificación de permisos', false),
    (6, 'cotizaciones', '/cotizaciones', 'Cotizaciones realizadas y enviadas', false),
    (7, 'vendedores', '/vendedores', 'Vendedores del sistema', false),
    (8, 'solicitudes', '/solicitudes', 'Rastreo de solicitudes asignadas a vendedores', false),
    (9, 'windowTracking', '/window-tracking', 'Tracker de solicitud de servicio', false),
    (10, 'notificaciones', '/notificaciones', 'Notificaciones del sistema', false),
    (11, 'roles', '/roles', 'Administrar los roles del sistema', false),
    (12, 'notification-groups', '/notification-groups', 'Administrar notificaciones', false),
    (13, 'usuarios', '/usuarios', 'Módulo para administrar usuarios', false),
    (14, 'dashboard', '/dashboard', 'Dashboard general del sistema', false),
    (15, 'sla-policies', '/sla-policies', 'Configuración de políticas de SLA', false),
    (16, 'productos', '/productos', 'Administración de productos', false),
    (17, 'recordatorios', '/recordatorios', 'Control y gestión de recordatorios', false),
    (22, 'logs', '/logs', 'Vista administrativa de logs del sistema', false),
    (23, 'modules', '/modules', 'Administración de módulos', false);

Role-Module Permissions

Assigns module access permissions to roles.

-- Admin role (role_id = 2) gets access to all modules
INSERT INTO db_ambiotec.role_module_permissions 
    (role_id, module_id, can_view, is_blocked)
VALUES
    (2, 1, true, false),  -- clientes
    (2, 2, true, false),  -- ventas
    (2, 3, true, false),  -- chat
    (2, 4, true, false),  -- perfil
    (2, 5, true, false),  -- permisos
    (2, 6, true, false),  -- cotizaciones
    (2, 7, true, false),  -- vendedores
    (2, 8, true, false),  -- solicitudes
    (2, 9, true, false),  -- windowTracking
    (2, 10, true, false); -- notificaciones

-- Manager role (role_id = 3) similar permissions
INSERT INTO db_ambiotec.role_module_permissions 
    (role_id, module_id, can_view, is_blocked)
VALUES
    (3, 1, true, false),
    (3, 2, true, false),
    (3, 3, true, false),
    (3, 4, true, false),
    (3, 5, true, false),
    (3, 6, true, false),
    (3, 7, true, false),
    (3, 8, true, false),
    (3, 9, true, false),
    (3, 10, true, false);

-- Vendedor role (role_id = 1) limited access
INSERT INTO db_ambiotec.role_module_permissions 
    (role_id, module_id, can_view, is_blocked)
VALUES
    (1, 1, true, false);  -- Only clientes module

Service Request Configuration

Service Request Status

Defines the workflow states for service requests.

INSERT INTO db_ambiotec.service_requests_status 
    (service_requests_status_id, service_requests_status_name, service_requests_status_description, service_requests_status_is_blocked) 
VALUES
    (0, 'lead', 'SOLICITUD INICIAL DE SERVICIO', false),
    (1, 'lead', 'SOLICITUD LLENA INICIAL DE SERVICIO', false),
    (2, 'ops', 'EN REVISION DE DISPONIBILIDAD DE SERVICIO', false),
    (3, 'approval', 'APROBACIÓN DE COTIZACIÓN DEL SERVICIO', true),
    (4, 'requestManager', 'SOLICITUD APOYO A GERENCIA', false);

Status Workflow:

lead (0) - Initial service request created
lead (1) - Request form completed
ops (2) - Under operations review for availability
approval (3) - Awaiting quotation approval (blocked)
requestManager (4) - Escalated to manager for approval

Payment Configuration

Banks Catalog

List of banks in Guatemala for payment processing.

INSERT INTO db_ambiotec.banks (bank_code, bank_name, country) 
VALUES
    ('BI', 'Banco Industrial', 'GTQ'),
    ('GT', 'Banco G&T Continental', 'GTQ'),
    ('BAM', 'Banco Agromercantil de Guatemala', 'GTQ'),
    ('REFORMADOR', 'Banco Reformador', 'GTQ'),
    ('AGROMERCANTIL', 'Banco Agromercantil', 'GTQ'),
    ('CITI', 'Citibank Guatemala', 'GTQ'),
    ('CHN', 'Crédito Hipotecario Nacional', 'GTQ'),
    ('ANTIGUA', 'Banco de Antigua', 'GTQ'),
    ('COMERCIO', 'Banco de Comercio', 'GTQ'),
    ('BANRURAL', 'Banco de Desarrollo Rural', 'GTQ'),
    ('BAC', 'Banco de América Central', 'GTQ'),
    ('INTERBANCO', 'InterBanco', 'GTQ'),
    ('VIVIBANCO', 'Vivibanco', 'GTQ'),
    ('BANTRAB', 'Banco de los Trabajadores', 'GTQ'),
    ('PROMERICA', 'Banco Promerica', 'GTQ'),
    ('NA', 'No disponible', 'GTQ')
ON CONFLICT (bank_code) DO NOTHING;

Product Categories

Initial product categories with SKU prefixes.

INSERT INTO db_ambiotec.product_categories 
    (category_id, category_name, code_prefix, category_description, sort_order) 
VALUES
    (1, 'Cabinas', 'CABIN', 'Cabinas y accesorios en alquiler de sanitarios', 1),
    (2, 'Químicos', 'QUIM', 'Productos químicos para tratamiento', 2),
    (3, 'Accesorios', 'ACC', 'Accesorios para sanitarios y equipos', 3),
    (4, 'Repuestos', 'REP', 'Repuestos y partes de reemplazo', 4),
    (5, 'Equipos', 'EQUI', 'Equipos y maquinaria', 5),
    (6, 'Consumibles', 'CONS', 'Productos de uso frecuente', 6),
    (7, 'Servicios adicionales', 'SERV', 'Servicios complementarios', 7),
    (8, 'Otros', 'OTR', 'Productos sin categoría específica', 99)
ON CONFLICT (category_name) DO NOTHING;

Category Structure:

Each category has a unique SKU prefix for automatic code generation
sort_order determines display order in UI
“Otros” category acts as fallback with high sort order

Notification System

Notification Channels

Defines available notification delivery channels.

INSERT INTO db_ambiotec.notification_channels 
    (channel_key, description) 
VALUES
    ('web',  'Notificación in-app/websocket'),
    ('push', 'Push notification (FCM/APNS/Windows)'),
    ('email','Correo electrónico')
ON CONFLICT (channel_key) DO NOTHING;

Geographic Data

The DML_Departments.sql file contains geographic data for Guatemala:

-- Departments (22 departments of Guatemala)
INSERT INTO db_ambiotec.departments (department_id, department_name) VALUES
    (1, 'Guatemala'),
    (2, 'Alta Verapaz'),
    (3, 'Baja Verapaz'),
    (4, 'Chimaltenango'),
    (5, 'Chiquimula'),
    -- ... and 17 more departments

-- Municipalities within each department
INSERT INTO db_ambiotec.municipio 
    (municipio_id, municipio_descripcion, municipio_departamento_id) 
VALUES
    (1, 'Guatemala', 1),
    (2, 'Santa Catarina Pinula', 1),
    (3, 'San José Pinula', 1),
    -- ... hundreds more municipalities

-- Zones for address specification
INSERT INTO db_ambiotec.zona (zona_id, zona_descripcion) VALUES
    (1, 'Zona 1'),
    (2, 'Zona 2'),
    -- ... up to Zona 25

Running Seed Scripts

Order of Execution

DML_Departments.sql - Geographic data (no dependencies)
DML.sql - Core system data (depends on departments)

Execution Command

# Connect to database
psql -U postgres -d ambiotec_db

# Run seed files in order
\i DB/DML/DML_Departments.sql
\i DB/DML/DML.sql

Verification

After seeding, verify the data:

-- Check roles
SELECT * FROM db_ambiotec.roles ORDER BY role_id;

-- Check modules
SELECT module_id, module_name, module_path 
FROM db_ambiotec.modules 
ORDER BY module_id;

-- Check departments
SELECT COUNT(*) FROM db_ambiotec.departments;  -- Should be 22

-- Check municipalities
SELECT COUNT(*) FROM db_ambiotec.municipio;    -- Should be 340

-- Check role permissions
SELECT r.role_name, COUNT(*) as module_count
FROM db_ambiotec.roles r
JOIN db_ambiotec.role_module_permissions rmp ON rmp.role_id = r.role_id
GROUP BY r.role_name
ORDER BY r.role_name;

Best Practices

Idempotent Seeds

Use ON CONFLICT DO NOTHING or WHERE NOT EXISTS to make seed scripts rerunnable.

Reference IDs

Explicitly set IDs for reference data to ensure consistency across environments.

Version Control

Keep seed files in version control alongside migrations.

Environment-Specific

Some seed data may differ between development, staging, and production.

Environment-Specific Seeds

Development

-- Create test users
INSERT INTO db_ambiotec.users (username, email, password, ...)
VALUES ('test_admin', '[email protected]', 'hashed_password', ...);

Production

-- Only essential reference data
-- No test users or sample data

Schema

Migrations

Overview

Seed Files

Core System Data

System Modules

Service Request Configuration

Payment Configuration

Product Categories

Notification System

Geographic Data

Running Seed Scripts

Order of Execution

Execution Command

Verification

Best Practices

Idempotent Seeds

Reference IDs

Version Control

Environment-Specific

Environment-Specific Seeds

Development

Production

Next Steps

Migrations

Schema Overview

Build docs developers (and LLMs) love

Schema

Migrations

​Overview

​Seed Files

​Core System Data

​System Modules

​Service Request Configuration

​Payment Configuration

​Product Categories

​Notification System

​Geographic Data

​Running Seed Scripts

​Order of Execution

​Execution Command

​Verification

​Best Practices

Idempotent Seeds

Reference IDs

Version Control

Environment-Specific

​Environment-Specific Seeds

​Development

​Production

​Next Steps

Migrations

Schema Overview

Build docs developers (and LLMs) love

Overview

Seed Files

Core System Data

System Modules

Service Request Configuration

Payment Configuration

Product Categories

Notification System

Geographic Data

Running Seed Scripts

Order of Execution

Execution Command

Verification

Best Practices

Environment-Specific Seeds

Development

Production

Next Steps