Stored Procedures

Overview

The database includes numerous stored procedures (functions) and triggers to encapsulate business logic, maintain data integrity, and provide reusable query interfaces.

Naming Conventions

Functions: Prefixed with fn_ (e.g., fn_get_customers)
Triggers: Prefixed with trg_ (e.g., trg_product_price_change)
Parameters: Prefixed with p_ (e.g., p_customer_id)
Variables: Prefixed with v_ (e.g., v_total)

Customer Management Functions

fn_get_customers

Retrieves customer list with filtering, pagination, and related data.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_customers(
    p_customer_id INT DEFAULT null,
    p_limit INT DEFAULT null,
    p_offset INT DEFAULT null,
    p_search_term TEXT DEFAULT null
)
RETURNS TABLE (
    customer_id INT,
    customer_name VARCHAR(100),
    customer_legal_name VARCHAR(150),
    customer_tax_id VARCHAR(30),
    customer_email TEXT,
    customer_address TEXT,
    customer_is_blocked BOOLEAN,
    customer_created_at TIMESTAMP,
    customer_notes TEXT,
    customer_contact_origin VARCHAR(50),
    customer_type_id INT,
    customer_type_name VARCHAR(50),
    customer_status_id INT,
    customer_status_name VARCHAR(150),
    customer_primary_contact TEXT,
    customer_primary_phone TEXT,
    default_company_name TEXT,
    default_company_tax_id TEXT,
    default_company_address TEXT,
    default_company_billing_email TEXT,
    default_company_is_active BOOLEAN,
    default_company_is_default BOOLEAN,
    total_count BIGINT
)

Features:

Automatically resolves best email, address, and contact
Includes default company name (razón social)
Supports search across multiple fields
Returns total count for pagination

fn_get_customer_by_id

Retrieves complete customer information in JSONB format.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_customer_by_id(
    p_customer_id INT
)
RETURNS JSONB

Returns:

{
  "customer_id": 123,
  "name": "Company Name",
  "legal_name": "Full Legal Name",
  "tax_id": "12345678-9",
  "primary_email": "[email protected]",
  "primary_phone": "+502 1234-5678",
  "fiscal_address": "1 Avenida 2-3 Zona 4",
  "contacts": [...],
  "customer_type_name": "Empresa",
  "customer_status_name": "Activo"
}

fn_list_customers

Lists customers with optional filters and pagination.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_list_customers(
    p_search TEXT DEFAULT NULL,
    p_customer_type_id INT DEFAULT NULL,
    p_customer_status_id INT DEFAULT NULL,
    p_limit INT DEFAULT 50,
    p_offset INT DEFAULT 0,
    p_include_blocked BOOLEAN DEFAULT FALSE
)
RETURNS TABLE (...)

Usage:

-- Get first 20 active customers
SELECT * FROM db_ambiotec.fn_list_customers(
    p_limit => 20,
    p_include_blocked => FALSE
);

-- Search customers by name or tax ID
SELECT * FROM db_ambiotec.fn_list_customers(
    p_search => 'Acme Corp'
);

fn_upsert_company_name

Creates or updates a company name (razón social) for a customer.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_upsert_company_name(
    p_customer_id INT,
    p_name TEXT,
    p_tax_id TEXT,
    p_company_name_id INT DEFAULT NULL,
    p_billing_email TEXT DEFAULT NULL,
    p_address TEXT DEFAULT NULL,
    p_billing_address TEXT DEFAULT NULL,
    p_is_active BOOLEAN DEFAULT TRUE,
    p_is_default BOOLEAN DEFAULT FALSE
)
RETURNS db_ambiotec.company_name

Features:

Inserts if p_company_name_id is NULL
Updates if p_company_name_id is provided
Automatically sets as default if p_is_default is TRUE

Product Management Functions

fn_generate_product_sku

Trigger function that automatically generates SKU codes for new products.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_generate_product_sku()
RETURNS TRIGGER AS $$
DECLARE
    v_prefix VARCHAR(10);
    v_next_seq INT;
    v_sku VARCHAR(50);
BEGIN
    -- Get category prefix
    IF NEW.category_id IS NOT NULL THEN
        SELECT code_prefix INTO v_prefix
        FROM db_ambiotec.product_categories
        WHERE category_id = NEW.category_id;
    END IF;
    
    -- Default to 'OTR' if no category
    IF v_prefix IS NULL THEN
        v_prefix := 'OTR';
    END IF;
    
    -- Generate next sequential number
    SELECT COALESCE(MAX(
        CASE 
            WHEN product_code ~ ('^' || v_prefix || '-[0-9]+$')
            THEN CAST(SUBSTRING(product_code FROM v_prefix || '-([0-9]+)$') AS INT)
            ELSE 0
        END
    ), 0) + 1
    INTO v_next_seq
    FROM db_ambiotec.products
    WHERE product_code LIKE v_prefix || '-%';
    
    -- Format as PREFIX-0001
    v_sku := v_prefix || '-' || LPAD(v_next_seq::TEXT, 4, '0');
    NEW.product_code := v_sku;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Example Output:

Category “Químicos” with prefix “QUIM” → QUIM-0001, QUIM-0002, etc.
Category “Accesorios” with prefix “ACC” → ACC-0001, ACC-0002, etc.

fn_log_product_price_change

Trigger function that logs price changes to history table.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_log_product_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.base_price IS DISTINCT FROM NEW.base_price THEN
        INSERT INTO db_ambiotec.product_price_history 
            (product_id, old_price, new_price, change_reason)
        VALUES 
            (NEW.product_id, OLD.base_price, NEW.base_price, 'Actualización de precio');
    END IF;
    
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

fn_get_request_products_total

Calculates the total cost of products for a service request.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_request_products_total(
    p_request_id INT
)
RETURNS NUMERIC AS $$
DECLARE
    v_total NUMERIC(12,2);
BEGIN
    SELECT COALESCE(SUM(total_price), 0)
    INTO v_total
    FROM db_ambiotec.request_products
    WHERE request_id = p_request_id
      AND status != 'cancelled';
    
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT db_ambiotec.fn_get_request_products_total(123);
-- Returns: 1250.00

Catalog Functions

fn_get_service_types

Returns all service types available in the system.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_service_types()
RETURNS SETOF db_ambiotec.service_type
LANGUAGE sql
AS $$
    SELECT * FROM db_ambiotec.service_type;
$$;

fn_get_roles

Returns all roles in JSONB format.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_roles()
RETURNS JSONB
LANGUAGE sql
AS $$
    SELECT jsonb_build_object(
        'roles', COALESCE(
            (SELECT jsonb_agg(to_jsonb(r) - 'created_at' - 'updated_at' ORDER BY r.role_name)
             FROM db_ambiotec.roles r),
            '[]'::jsonb
        )
    );
$$;

fn_form_catalogs_editUser

Returns all catalog data needed for user edit forms.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_form_catalogs_editUser()
RETURNS jsonb

Returns:

{
  "roles": [...],
  "user_status": [...],
  "document_type": [...],
  "license_type": [...],
  "customer_status": [...],
  "customer_types": [...],
  "departments": [...]
}

fn_get_edit_client_catalog

Returns catalog data for client edit forms including sellers and phones.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_edit_client_catalog(
    p_client_id INT DEFAULT NULL
)
RETURNS jsonb

Returns:

Departments
Active sellers (users with role_id = 1)
Customer types and statuses
Existing phones and emails for the client (if p_client_id provided)

Audit & Logging Functions

fn_get_all_logs

Retrieves comprehensive system logs in JSONB format.

CREATE OR REPLACE FUNCTION db_ambiotec.fn_get_all_logs()
RETURNS jsonb

Returns logs for:

service_request_logs - Request data changes
service_request_status_log - Status changes
service_request_state_timeline - SLA timeline
service_order_status_log - Order status changes
user_access_log - Login/logout events
user_api_usage - API endpoint usage

Limit: Returns last 500 entries per log type

log_payment_status_change

Trigger function that automatically logs payment status changes.

CREATE OR REPLACE FUNCTION db_ambiotec.log_payment_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO db_ambiotec.payment_status_history 
            (payment_id, old_status, new_status, changed_by, reason)
        VALUES 
            (NEW.payment_id, OLD.status, NEW.status, NEW.validated_by, NEW.rejection_reason);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Utility Functions

update_payment_updated_at

Trigger function to automatically update updated_at timestamp.

CREATE OR REPLACE FUNCTION db_ambiotec.update_payment_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Applied to:

payments table
Similar pattern used across other tables

fn_sync_telefono_boleta

Legacy trigger for synchronizing phone numbers between related tables.

CREATE OR REPLACE FUNCTION db_controlfac.fn_sync_telefono_boleta()
RETURNS trigger AS $$
BEGIN
    IF NEW.boleta_direccion_encargado_contacto IS DISTINCT FROM OLD.boleta_direccion_encargado_contacto THEN
        UPDATE db_controlfac.tbl_boletas b
        SET boleta_encargado_telefono = NEW.boleta_direccion_encargado_contacto
        WHERE b.boleta_id = NEW.boleta_direccion_boleta_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Trigger Assignments

Product Triggers

-- Auto-generate SKU on insert
CREATE TRIGGER trg_generate_product_sku
    BEFORE INSERT ON db_ambiotec.products
    FOR EACH ROW
    EXECUTE FUNCTION db_ambiotec.fn_generate_product_sku();

-- Log price changes on update
CREATE TRIGGER trg_product_price_change
    BEFORE UPDATE ON db_ambiotec.products
    FOR EACH ROW
    EXECUTE FUNCTION db_ambiotec.fn_log_product_price_change();

Payment Triggers

-- Update timestamp
CREATE TRIGGER trg_payments_updated_at
    BEFORE UPDATE ON db_ambiotec.payments
    FOR EACH ROW
    EXECUTE FUNCTION db_ambiotec.update_payment_updated_at();

-- Log status changes
CREATE TRIGGER trg_payment_status_history
    AFTER UPDATE ON db_ambiotec.payments
    FOR EACH ROW
    EXECUTE FUNCTION db_ambiotec.log_payment_status_change();

Best Practices

Security

Some functions use SECURITY DEFINER to run with elevated privileges. Use this carefully and validate all inputs.

Performance

Functions that return large datasets support pagination parameters (p_limit, p_offset) to prevent memory issues.

Error Handling

Functions validate inputs and return NULL or empty results rather than throwing errors for invalid IDs.

JSONB Returns

Functions returning JSONB are convenient for API endpoints but may need parsing in application code.

Schema

Migrations

Stored Procedures

Overview

Naming Conventions

Customer Management Functions

Product Management Functions

Catalog Functions

Audit & Logging Functions

Utility Functions

Trigger Assignments

Product Triggers

Payment Triggers

Best Practices

Security

Performance

Error Handling

JSONB Returns

Next Steps

Table Schemas

Migrations

Build docs developers (and LLMs) love

Schema

Migrations

​Overview

​Naming Conventions

​Customer Management Functions

​Product Management Functions

​Catalog Functions

​Audit & Logging Functions

​Utility Functions

​Trigger Assignments

​Product Triggers

​Payment Triggers

​Best Practices

Security

Performance

Error Handling

JSONB Returns

​Next Steps

Table Schemas

Migrations

Build docs developers (and LLMs) love

Overview

Naming Conventions

Customer Management Functions

Product Management Functions

Catalog Functions

Audit & Logging Functions

Utility Functions

Trigger Assignments

Product Triggers

Payment Triggers

Best Practices

Next Steps