Skip to main content

​
Database Information

The system uses PostgreSQL with the schema db_ambiotec for all application data. The database architecture follows a normalized design with clear separation of concerns.

​
Core Schema Structure

​
Primary Schema

CREATE SCHEMA db_ambiotec;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis;
The database uses two PostgreSQL extensions:
  • pgcrypto: For UUID generation and cryptographic functions
  • postgis: For geographic data types and spatial operations

​
Main Entity Groups

The database is organized into several logical domains:

​
User Management

Core user management including authentication, roles, and permissions.Key Tables:
  • users - User accounts and profile information
  • roles - System roles (admin, vendedor, gerente, etc.)
  • user_roles - Many-to-many relationship between users and roles
  • user_status - User account status (active, inactive, suspended)
  • user_access_log - Login/logout tracking with device information
  • user_api_usage - API endpoint usage tracking
Granular module and action-based permission system.Key Tables:
  • modules - Application modules (clientes, ventas, chat, etc.)
  • module_actions - Actions within each module
  • user_module_permissions - Per-user module access
  • user_action_permissions - Per-user action permissions
  • role_module_permissions - Default module permissions per role
  • permission_versions - Cache versioning for permission changes

​
Customer Management

Normalized customer data structure supporting multiple addresses, contacts, and documents.Key Tables:
  • customers - Master customer table with commercial entity data
  • customer_addresses - Multiple addresses per customer (fiscal, billing, service, shipping)
  • customer_contacts - Contact persons with roles (primary, billing, purchasing, site)
  • customer_documents - Document storage with expiration tracking (DPI, RTU, contracts)
  • company_name - Multiple legal entities (razones sociales) per customer
Flexible contact information supporting multiple phone numbers and emails.Key Tables:
  • contact_phones - Multiple phone numbers per contact
  • contact_emails - Multiple email addresses per contact
  • contact_logs - Interaction history with customers

​
Service Requests

Service request management with type-specific data and workflow.Key Tables:
  • service_requests - Main service request table
  • service_type - Types of services (sanitarios, fosas, PTAR, etc.)
  • service_requests_status - Request workflow states
  • service_requests_addresses - Geocoded service location addresses
  • request_sanitarios - Sanitarios-specific data (rental details)
  • request_fosas - Fosas-specific data (water type, volume)
  • request_ptar - PTAR-specific data (treatment plant design)
  • request_others - Generic service requests
Complete tracking of request lifecycle from lead to completion.Key Tables:
  • service_request_logs - Detailed change history
  • service_request_status_log - Status change tracking
  • service_request_state_timeline - SLA tracking with policy deadlines
  • service_request_assignments - Vendor assignment history
  • service_request_manager_review - Manager approval process
Quotation generation and customer acceptance workflow.Key Tables:
  • service_request_quotations - Generated quotations with Drive/S3 links
  • quotation_followups - Sales followup tracking
  • quotation_acceptances - Formal acceptance with customer data snapshot
  • service_request_media - Attached files and media

​
Products & Catalog

Product catalog with automatic SKU generation and pricing history.Key Tables:
  • product_categories - Hierarchical product categories with SKU prefixes
  • products - Master product catalog with pricing
  • request_products - Products associated with service requests
  • product_price_history - Audit trail of price changes

​
Payments

Payment tracking with OCR support and validation workflow.Key Tables:
  • payments - Main payment records with OCR metadata
  • payment_status_history - Status change audit trail
  • payment_logs - Detailed operation logs
  • banks - Bank catalog for Guatemala

​
Notifications

Multi-channel notification system (web, push, email).Key Tables:
  • notifications - Core notification records
  • notification_channels - Delivery channels (web, push, email)
  • notification_deliveries - Per-channel delivery tracking
  • user_device_tokens - Device registration for push notifications
  • notification_groups - Predefined notification recipient groups
  • notification_group_members - Group membership

​
Geographic Data

Guatemala-specific geographic hierarchy.Key Tables:
  • departments - Departments (departamentos)
  • municipio - Municipalities within departments
  • zona - Zones for address specification

​
Supporting Tables

S3-backed file storage with metadata.Key Tables:
  • storage_files - File metadata and S3 references
  • service_request_media - Request-specific media files
  • form_ptar_service_files - PTAR form attachments
System monitoring and incident tracking.Key Tables:
  • extortion_incidents - Security incident tracking
  • reported_system_cases - Bug/error reports from public pages
  • assignment_notifications - Assignment event tracking

​
Design Principles

​
Normalization

The schema follows database normalization principles to reduce redundancy:
  • Customer data is separated into multiple related tables
  • Contact information uses dedicated tables for phones and emails
  • Address data is normalized with geographic hierarchies

​
Audit Trails

Most core tables include audit tracking:
  • Change logs for critical data (service requests, payments, etc.)
  • Status history tables for workflow tracking
  • Timestamp fields (created_at, updated_at) on all major tables

​
Soft Deletes

Many tables use flags instead of physical deletion:
  • is_blocked for users and customers
  • is_active for contacts, addresses, and documents
  • Preserves historical data and relationships

​
JSONB Metadata

Flexible metadata storage using PostgreSQL JSONB:
  • metadata columns for extensible data without schema changes
  • ocr_data for unstructured OCR results
  • extra_data for logging and tracking

​
Geographic Support

PostGIS integration for location-based features:
  • geography(POINT, 4326) for address coordinates
  • Spatial indexing for distance-based queries

​
Naming Conventions

  • Tables: Lowercase with underscores, prefixed with entity domain
  • Primary Keys: {table}_id (e.g., customer_id, request_id)
  • Foreign Keys: Match referenced primary key name
  • Boolean Fields: Prefixed with is_ or has_
  • Timestamp Fields: Suffixed with _at (e.g., created_at, updated_at)
  • Stored Procedures: Prefixed with fn_ for functions, trg_ for triggers

​
Next Steps

Tables Reference

Detailed table schemas and relationships

Stored Procedures

Database functions and procedures

Migrations

Database migration strategy

Seeding Data

Initial data and catalogs

Build docs developers (and LLMs) love

Get started for freeTalk to us