Table Schemas

Core Tables

This page documents the main tables in the database with their complete schemas and relationships.

User & Authentication

users

Core user account table with profile information and references.

CREATE TABLE db_ambiotec.users (
    user_id                      SERIAL PRIMARY KEY,
    username                     VARCHAR(50) NOT NULL UNIQUE,
    email                        VARCHAR(100) UNIQUE,
    password                     TEXT NOT NULL,
    date_born                    TIMESTAMPTZ,
    first_name                   VARCHAR(75) NOT NULL DEFAULT '',
    last_name                    VARCHAR(75) NOT NULL DEFAULT '',
    id_document_number           VARCHAR(30) NOT NULL DEFAULT '',
    tax_id                       VARCHAR(15) DEFAULT '',
    mobile_number                VARCHAR(255) NOT NULL DEFAULT '',
    is_blocked                   BOOLEAN NOT NULL DEFAULT FALSE,
    license_id                   INT REFERENCES db_ambiotec.license(license_id),
    document_type_id             INT REFERENCES db_ambiotec.document_type(document_type_id),
    user_status_id               INT REFERENCES db_ambiotec.user_status(user_status_id),
    role_id                      INT REFERENCES db_ambiotec.roles(role_id),
    department_id                INT2 REFERENCES db_ambiotec.departments(department_id)
);

Key Relationships:

References license for driver’s license information
References user_status for account status
References roles for primary role assignment
References departments for organizational structure

roles

System roles defining permission groups.

CREATE TABLE db_ambiotec.roles (
    role_id SERIAL PRIMARY KEY,
    role_key TEXT NOT NULL UNIQUE,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT NULL,
    created_by INTEGER NULL REFERENCES db_ambiotec.users(user_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Standard Roles:

admin - System administrator
vendedor - Sales representative
gerente - Manager
cliente - Customer
recepcionista - Receptionist
supervisor - Supervisor
operaciones - Operations
cobros - Collections

user_access_log

Tracks user login sessions with device and location information.

CREATE TABLE db_ambiotec.user_access_log (
    access_id        BIGSERIAL PRIMARY KEY,
    user_id          INT NOT NULL REFERENCES db_ambiotec.users(user_id) ON DELETE CASCADE,
    ip_address       INET NOT NULL,
    mac_address      VARCHAR(17),
    platform         VARCHAR(50),
    os_version       VARCHAR(50),
    browser_name     VARCHAR(50),
    browser_version  VARCHAR(20),
    device_type      VARCHAR(30),
    user_agent       TEXT,
    location_country VARCHAR(50),
    location_city    VARCHAR(50),
    login_timestamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_timestamp TIMESTAMP NULL,
    is_successful    BOOLEAN DEFAULT TRUE,
    extra_data       JSONB
);

Customer Management

customers

Master customer table with core business entity information.

CREATE TABLE db_ambiotec.customers (
    customer_id        SERIAL PRIMARY KEY,
    name               VARCHAR(100) NOT NULL,
    legal_name         VARCHAR(150) NOT NULL,
    tax_id             VARCHAR(30),
    is_blocked         BOOLEAN NOT NULL DEFAULT FALSE,
    credit_days        SMALLINT DEFAULT 0,
    credit_limit       NUMERIC(12,2) DEFAULT 0,
    payment_method_id  INT REFERENCES db_ambiotec.payment_methods(payment_method_id),
    billing_frequency_id INT REFERENCES db_ambiotec.billing_frequencies(billing_frequency_id),
    contact_origin     VARCHAR(50),  -- WHATSAPP, WEB_FORM, PHONE, REFERIDO
    notes              TEXT,
    customer_type_id   INT REFERENCES db_ambiotec.customer_types(customer_type_id),
    customer_status_id INT REFERENCES db_ambiotec.customer_status(customer_status_id),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at         TIMESTAMPTZ DEFAULT now()
);

Indexes:

idx_customers_tax_id on tax_id
idx_customers_name on name
idx_customers_legal_name on legal_name

customer_addresses

Multiple addresses per customer with type designation.

CREATE TABLE db_ambiotec.customer_addresses (
    address_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES db_ambiotec.customers(customer_id) ON DELETE CASCADE,
    address_type_id INT REFERENCES db_ambiotec.address_types(address_type_id),
    address_type VARCHAR(30) NOT NULL,  -- FISCAL, BILLING, SERVICE, SHIPPING
    address_line TEXT NOT NULL,
    department_id INT REFERENCES db_ambiotec.departments(department_id),
    municipio_id INT REFERENCES db_ambiotec.municipio(municipio_id),
    zona_id INT REFERENCES db_ambiotec.zona(zona_id),
    postal_code VARCHAR(20),
    reference TEXT,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

customer_contacts

Contact persons associated with customers.

CREATE TABLE db_ambiotec.customer_contacts (
    customer_contact_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES db_ambiotec.customers(customer_id) ON DELETE CASCADE,
    contact_type_id INT REFERENCES db_ambiotec.contact_types(contact_type_id),
    contact_type VARCHAR(30) NOT NULL,  -- PRIMARY, BILLING, PURCHASING, SITE, LEGAL
    full_name VARCHAR(150) NOT NULL,
    position VARCHAR(100),
    email VARCHAR(150),
    phone VARCHAR(50),
    phone_extension VARCHAR(10),
    mobile VARCHAR(50),
    is_whatsapp BOOLEAN DEFAULT FALSE,
    is_decision_maker BOOLEAN DEFAULT FALSE,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

company_name

Legal entities (razones sociales) for customer billing.

CREATE TABLE db_ambiotec.company_name (
    company_name_id SERIAL PRIMARY KEY,
    tax_id varchar NOT NULL,
    name varchar(150) NOT NULL,
    description varchar(150) NOT NULL DEFAULT '',
    is_active bool DEFAULT true NOT NULL,
    billing_email varchar(180),
    is_default bool DEFAULT false NOT NULL,
    address varchar(300) DEFAULT '' NOT NULL,
    billing_address varchar(300) DEFAULT '' NOT NULL,
    credit_days SMALLINT,
    credit_limit NUMERIC(12,2) NOT NULL DEFAULT 0,
    created_at date DEFAULT now() NOT NULL,
    comments varchar(3000) DEFAULT '' NOT NULL,
    updated_at date DEFAULT now() NOT NULL,
    municipio_id int4 DEFAULT 2 NOT NULL REFERENCES db_ambiotec.municipio(municipio_id),
    zona_id int2 DEFAULT 1 NOT NULL REFERENCES db_ambiotec.zona(zona_id),
    department_id int2 DEFAULT 2 NOT NULL REFERENCES db_ambiotec.departments(department_id),
    customer_id int4 NOT NULL REFERENCES db_ambiotec.customers(customer_id)
);

Unique Constraint:

Only one is_default = TRUE per customer_id

Service Requests

service_requests

Main service request table with core information.

CREATE TABLE db_ambiotec.service_requests (
    request_id SERIAL PRIMARY KEY,
    request_uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    customer_id INT REFERENCES db_ambiotec.customers(customer_id),
    service_type INT REFERENCES db_ambiotec.service_type(service_type_id),
    contact_name VARCHAR(100) NOT NULL,
    contact_email VARCHAR(150),
    contact_phone VARCHAR(50),
    whatsapp VARCHAR(50),
    ext VARCHAR(8),
    department_id INT REFERENCES db_ambiotec.departments(department_id),
    address TEXT,
    address_id INT REFERENCES db_ambiotec.service_requests_addresses(address_id),
    company_name VARCHAR(150),
    tax_id VARCHAR(30),
    requested_delivery_date TIMESTAMPTZ,
    approved_delivery_date TIMESTAMPTZ,
    suggested_delivery_date TIMESTAMPTZ,
    comments TEXT,
    current_status INT REFERENCES db_ambiotec.service_requests_status(service_requests_status_id) DEFAULT 0 NOT NULL,
    note_by_manager TEXT,
    created_by INT REFERENCES db_ambiotec.users(user_id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

request_sanitarios

Additional data for sanitarios (portable toilet) service requests.

CREATE TABLE db_ambiotec.request_sanitarios (
    request_id INT PRIMARY KEY REFERENCES db_ambiotec.service_requests(request_id) ON DELETE CASCADE,
    rent_type VARCHAR(50) NOT NULL,  -- dia, semana, quincena, mes
    size VARCHAR(50),
    cabin_type VARCHAR(50),  -- regular, VIP
    cleaning_per_week INT NOT NULL,
    scort_quantity INT NOT NULL,
    truck_access BOOLEAN,
    is_basement BOOLEAN,
    distance_truck_to_cabin DECIMAL(6,2),
    height_truck_to_cabin DECIMAL(6,2),
    cabins JSONB,
    special_notes TEXT,
    photos JSONB,
    place_desc TEXT,
    comment TEXT
);

request_fosas

Additional data for fosas (septic tank) service requests.

CREATE TABLE db_ambiotec.request_fosas (
    request_id INT PRIMARY KEY REFERENCES db_ambiotec.service_requests(request_id) ON DELETE CASCADE,
    water_type VARCHAR(20) CHECK (water_type IN ('ORDINARIA', 'ESPECIAL')),
    estimated_trips INT,
    truck_access BOOLEAN,
    estimated_depth DECIMAL(5, 2),
    estimated_volume DECIMAL(8, 2),
    location_type VARCHAR(100),
    access_difficulty VARCHAR(100),
    is_basement BOOLEAN,
    distance_truck_to_cabin DECIMAL(6,2),
    height_truck_to_cabin DECIMAL(6,2),
    photos JSONB,
    comment TEXT
);

service_request_quotations

Generated quotations for service requests.

CREATE TABLE db_ambiotec.service_request_quotations (
    quotation_id            SERIAL PRIMARY KEY,
    request_id              INT NOT NULL REFERENCES db_ambiotec.service_requests(request_id) ON DELETE CASCADE,
    template_name           TEXT NOT NULL,
    drive_file_id           TEXT NOT NULL,
    drive_web_view_link     TEXT,
    drive_web_content_link  TEXT,
    status                  TEXT NOT NULL DEFAULT 'DRAFT',
    price_service           NUMERIC(10,2),
    price_transport         NUMERIC(10,2),
    created_by              INT REFERENCES db_ambiotec.users(user_id),
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    finalized_at            TIMESTAMPTZ,
    s3_bucket               TEXT,
    s3_key                  TEXT,
    s3_version_id           TEXT,
    s3_url                  TEXT,
    metadata                JSONB DEFAULT '{}'
);

Products

products

Product catalog with automatic SKU generation.

CREATE TABLE db_ambiotec.products (
    product_id SERIAL PRIMARY KEY,
    product_code VARCHAR(50) UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    product_description TEXT,
    category_id INT REFERENCES db_ambiotec.product_categories(category_id),
    unit_of_measure VARCHAR(30) DEFAULT 'unidad',
    base_price NUMERIC(12,2),
    cost_price NUMERIC(12,2),
    currency VARCHAR(3) DEFAULT 'GTQ',
    is_active BOOLEAN DEFAULT TRUE,
    requires_approval BOOLEAN DEFAULT FALSE,
    min_quantity NUMERIC(10,2) DEFAULT 1,
    metadata JSONB DEFAULT '{}',
    created_by INT REFERENCES db_ambiotec.users(user_id),
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

Automatic SKU Generation: Products automatically receive a SKU based on their category prefix (e.g., QUIM-0001, ACC-0023).

product_categories

Hierarchical product categories with SKU prefix assignment.

CREATE TABLE db_ambiotec.product_categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    code_prefix VARCHAR(10) NOT NULL UNIQUE,
    category_description TEXT,
    parent_category_id INT REFERENCES db_ambiotec.product_categories(category_id),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT now()
);

Standard Categories:

CABIN - Cabinas
QUIM - Químicos
ACC - Accesorios
REP - Repuestos
EQUI - Equipos
CONS - Consumibles
SERV - Servicios adicionales

Payments

payments

Payment records with OCR support and validation workflow.

CREATE TABLE db_ambiotec.payments (
    payment_id            SERIAL PRIMARY KEY,
    customer_id           INT NOT NULL REFERENCES db_ambiotec.customers(customer_id) ON DELETE RESTRICT,
    invoice_id            INT,
    amount                NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
    currency              VARCHAR(3) NOT NULL DEFAULT 'GTQ',
    payment_method        VARCHAR(20) NOT NULL CHECK (payment_method IN ('efectivo', 'deposito', 'transferencia', 'cheque', 'tarjeta', 'ach')),
    bank_id               INT,
    reference_code        VARCHAR(100),
    transaction_date      DATE NOT NULL,
    receipt_number        VARCHAR(50),
    status                VARCHAR(20) NOT NULL DEFAULT 'pendiente' 
        CHECK (status IN ('pendiente', 'validado', 'rechazado', 'conciliado', 'aplicado', 'en_proceso_ach')),
    rejection_reason      TEXT,
    notes                 TEXT,
    registered_by         INT NOT NULL REFERENCES db_ambiotec.users(user_id),
    validated_by          INT REFERENCES db_ambiotec.users(user_id),
    validated_at          TIMESTAMPTZ,
    conciliated_by        INT REFERENCES db_ambiotec.users(user_id),
    conciliated_at        TIMESTAMPTZ,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    document_file_id      UUID REFERENCES db_ambiotec.storage_files(file_id),
    ocr_data              JSONB,
    ocr_confidence        NUMERIC(5, 2),
    manual_override       BOOLEAN DEFAULT FALSE,
    metadata              JSONB DEFAULT '{}'
);

File Storage

storage_files

Centralized file metadata with S3 storage references.

CREATE TABLE db_ambiotec.storage_files (
    file_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    s3_url           TEXT,
    bucket_name      TEXT        NOT NULL,
    object_key       TEXT        NOT NULL,
    original_name    TEXT        NOT NULL,
    mime_type        TEXT        NOT NULL,
    size_bytes       BIGINT      NOT NULL CHECK (size_bytes >= 0),
    sha256_hex       TEXT,
    uploaded_by      INT,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    metadata         JSONB       DEFAULT '{}'
);

Unique Constraint:

(bucket_name, object_key) must be unique

Notifications

notifications

Multi-channel notification system.

CREATE TABLE db_ambiotec.notifications (
    notification_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    receiver_user_id    INT NOT NULL REFERENCES db_ambiotec.users(user_id) ON DELETE CASCADE,
    title               TEXT NOT NULL,
    body                TEXT NOT NULL,
    action_url          TEXT,
    data                JSONB       DEFAULT '{}',
    status              VARCHAR(20) DEFAULT 'pending',
    priority            SMALLINT    DEFAULT 0,
    created_by          INT REFERENCES db_ambiotec.users(user_id),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    scheduled_at        TIMESTAMPTZ,
    delivered_at        TIMESTAMPTZ,
    read_at             TIMESTAMPTZ,
    expires_at          TIMESTAMPTZ
);

Table Relationships

Key foreign key relationships in the database:

Next Steps

Stored Procedures

Database functions and triggers

Schema Overview

High-level architecture

Schema

Migrations

Core Tables

User & Authentication

Customer Management

Service Requests

Products

Payments

File Storage

Notifications

Table Relationships

Next Steps

Stored Procedures

Schema Overview

Build docs developers (and LLMs) love

Schema

Migrations

​Core Tables

​User & Authentication

​Customer Management

​Service Requests

​Products

​Payments

​File Storage

​Notifications

​Table Relationships

​Next Steps

Stored Procedures

Schema Overview

Build docs developers (and LLMs) love

Core Tables

User & Authentication

Customer Management

Service Requests

Products

Payments

File Storage

Notifications

Table Relationships

Next Steps