Database

Database architecture

DAF Backend uses a dual-database architecture with two separate PostgreSQL databases:

POS Database

Purpose: Point of Sale operationsConnection: Credential-based per-request poolingAuthentication: User credentials in JWT tokens

E-commerce Database

Purpose: Online shopping operationsConnection: Shared connection poolAuthentication: Application-level user (fixed credentials)

Both databases run on PostgreSQL 12 or higher and are accessed via the pg (node-postgres) library.

Connection strategies

The two systems use fundamentally different approaches to database connections:

POS: Credential-based connections

The POS system creates new connection pools for each request using credentials from the JWT token:

src/config/db_pos.js

const { Pool } = require('pg');
require('dotenv').config();

function getConnectionWithCredentials(user, password) {
  return new Pool({
    host: process.env.POS_HOST,
    port: process.env.POS_PORT,
    database: process.env.POS_NAME,
    user,
    password,
  });
}

module.exports = {
  getConnectionWithCredentials,
};

How it works:

User authenticates

User provides PostgreSQL username and password to /api/pos/auth/login

Credentials stored in JWT

API validates credentials by connecting to database, then stores them in JWT token payload

Request with token

Client sends JWT token in Authorization header for subsequent requests

Pool created per request

Controller extracts credentials from JWT and creates a new connection pool:

src/controllers/pos.cliente.controller.js:6-9

const connectFromJWT = (req) => {
    const { usuario, password } = req.user;
    return getConnectionWithCredentials(usuario, password);
};

Queries executed

Model functions execute queries using the pool

Pool closed

Controller’s finally block ensures pool is closed:

src/controllers/pos.cliente.controller.js:55-59

} catch (error) {
    res.status(500).json({ message: error.message });
} finally {
    await pool.end();
}

Benefits:

Database-level security: PostgreSQL enforces permissions per user
Audit trails: Database logs show actual user performing operations
Fine-grained control: Use PostgreSQL roles and grants for authorization

Trade-offs:

Higher overhead: Creating and destroying pools per request
Must manage cleanup: finally blocks required to prevent connection leaks

E-commerce: Shared connection pool

The e-commerce system uses a single shared connection pool initialized at startup:

src/config/db_ecom.js

const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  host: process.env.EC_HOST,
  port: process.env.EC_PORT,
  database: process.env.EC_NAME,
  user: process.env.EC_USER,
  password: process.env.EC_PASSWORD,
});

const getConnection = () => {
  return pool;
};

module.exports = {
  getConnection,
};

How it works:

Pool initialized at startup

Connection pool is created when the application starts

Controllers get connection

Controllers call getConnection() to access the shared pool:

src/controllers/ecom.carrito.controller.js:16

const pool = await getConnection();

Queries executed

Model functions execute queries, connections are automatically managed by the pool

No cleanup needed

Pool persists for the application lifetime, connections are reused

Benefits:

Better performance: Connections are reused across requests
Simpler code: No pool management in controllers
Optimized for high traffic: Connection pool handles concurrency

Trade-offs:

All queries appear to come from the same database user
Application must enforce authorization logic
No database-level user audit trails

Environment variables

Configure database connections via environment variables in your .env file:

# POS Database Configuration
POS_HOST=localhost
POS_PORT=5432
POS_NAME=daf_pos_db

# Note: POS_USER and POS_PASSWORD are NOT in .env
# They come from user login credentials (stored in JWT)

Security: Never commit your .env file to version control. Use different credentials for development, staging, and production environments.

Database setup

Follow these steps to set up your PostgreSQL databases:

POS Database
E-commerce Database

POS database setup

Create database

CREATE DATABASE daf_pos_db
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8';

Create tables

The POS database includes tables for:

cliente - Customer information
producto - Product catalog
factura - Invoices
proveedor - Suppliers
materiaprima - Raw materials
categoria - Product categories
kardex_mp - Raw material inventory movements
kardex_prod - Product inventory movements
ciudad - Cities
unidadmedida - Units of measurement
transaccion - Transaction types
ordencompra - Purchase orders
estandar - Standard specifications

Create database users

Create individual users for each person who will access the POS system:

-- Create a POS user
CREATE USER pos_admin WITH PASSWORD 'secure_password';

-- Grant connection privilege
GRANT CONNECT ON DATABASE daf_pos_db TO pos_admin;

-- Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO pos_admin;

-- Grant sequence permissions (for auto-increment IDs)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pos_admin;

-- Grant execute on stored procedures
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO pos_admin;

Create roles (optional)

Use PostgreSQL roles for fine-grained permissions:

-- Create roles
CREATE ROLE pos_readonly;
CREATE ROLE pos_manager;
CREATE ROLE pos_admin_role;

-- Grant appropriate permissions to each role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pos_readonly;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO pos_manager;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO pos_admin_role;

-- Assign role to user
GRANT pos_manager TO pos_user;

The login endpoint queries user roles:

src/controllers/pos.auth.controller.js:18-24

const result = await pool.query(`
  SELECT r.rolname
  FROM pg_roles r
  JOIN pg_auth_members m ON r.oid = m.roleid
  JOIN pg_roles u ON u.oid = m.member
  WHERE u.rolname = (SELECT CURRENT_USER)
`);

E-commerce database setup

Create database

CREATE DATABASE daf_ecom_db
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8';

Create tables

The e-commerce database includes tables for:

usuario - User accounts (email, password)
cliente - Customer profiles
producto - Product catalog (separate from POS)
carrito - Shopping carts
detalle_carrito - Cart line items
ciudad - Cities/locations
pagos - Payment records

Create application user

Create a single database user for the application:

-- Create e-commerce application user
CREATE USER ecom_user WITH PASSWORD 'your_secure_password';

-- Grant database access
GRANT CONNECT ON DATABASE daf_ecom_db TO ecom_user;

-- Grant all privileges on public schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ecom_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ecom_user;

-- Set default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO ecom_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO ecom_user;

Update .env

Add the credentials to your .env file:

EC_HOST=localhost
EC_PORT=5432
EC_NAME=daf_ecom_db
EC_USER=ecom_user
EC_PASSWORD=your_secure_password

Database schema overview

While complete schemas should be documented separately, here’s an overview of key entities:

POS Schema
E-commerce Schema

POS database entities

Cliente (Customer)

cli_codigo        VARCHAR(10)   PRIMARY KEY
ct_codigo         VARCHAR(10)   FOREIGN KEY → ciudad
cli_nombre        VARCHAR(120)
cli_ruc_ced       VARCHAR(13)   UNIQUE
cli_telefono      VARCHAR(10)
cli_mail          VARCHAR(60)
cli_direccion     VARCHAR(60)
cli_celular       VARCHAR(9)
cli_estado        VARCHAR(3)    DEFAULT 'ACT'
cli_fecha_alta    TIMESTAMP     DEFAULT NOW()

Producto (Product)

prd_codigo        VARCHAR(10)   PRIMARY KEY
prd_nombre        VARCHAR(120)
prd_precio_venta  DECIMAL(10,2)
prd_stock         INTEGER
prd_img           TEXT
prd_estado        VARCHAR(3)    DEFAULT 'ACT'
cat_codigo        VARCHAR(10)   FOREIGN KEY → categoria

Factura (Invoice)

fct_codigo        VARCHAR(10)   PRIMARY KEY
cli_codigo        VARCHAR(10)   FOREIGN KEY → cliente
fct_fecha         DATE
fct_total         DECIMAL(10,2)
fct_estado        VARCHAR(3)

Models use these structures in queries:

src/models/cliente.model.js:58-62

const getClienteByID = async (pool, id) => {
  const query = `SELECT * FROM public.cliente WHERE cli_codigo = $1`;
  const result = await pool.query(query, [id]);
  return result.rows[0];
};

E-commerce database entities

Usuario (User)

usr_id            SERIAL        PRIMARY KEY
usr_email         VARCHAR(60)   UNIQUE NOT NULL
usr_password      VARCHAR(60)   NOT NULL (hashed)
cli_codigo        VARCHAR(10)   FOREIGN KEY → cliente
usr_created_at    TIMESTAMP     DEFAULT NOW()

Carrito (Shopping Cart)

crr_codigo        VARCHAR(10)   PRIMARY KEY
cli_codigo        VARCHAR(10)   FOREIGN KEY → cliente
crr_fecha         TIMESTAMP     DEFAULT NOW()
crr_estado        VARCHAR(3)    DEFAULT 'ACT'

Detalle_Carrito (Cart Items)

dcrr_id           SERIAL        PRIMARY KEY
crr_codigo        VARCHAR(10)   FOREIGN KEY → carrito
prd_codigo        VARCHAR(10)   FOREIGN KEY → producto
dcrr_cantidad     INTEGER       DEFAULT 1
dcrr_precio       DECIMAL(10,2)

Producto (Product)

prd_codigo        VARCHAR(10)   PRIMARY KEY
prd_nombre        VARCHAR(120)
prd_descripcion   TEXT
prd_precio        DECIMAL(10,2)
prd_img           TEXT
prd_stock         INTEGER
prd_estado        VARCHAR(3)    DEFAULT 'ACT'

Connection pooling best practices

POS: Always close pools

Use finally blocks to ensure pools are closed:

const pool = connectFromJWT(req);
try {
  // queries
} finally {
  await pool.end();
}

E-commerce: Reuse connections

Don’t create new pools, use the shared instance:

const pool = getConnection();
// No cleanup needed

Use parameterized queries

Always use parameterized queries to prevent SQL injection:

pool.query('SELECT * FROM cliente WHERE cli_codigo = $1', [id])

Handle connection errors

Wrap database operations in try-catch:

try {
  await pool.query(...);
} catch (error) {
  res.status(500).json({ message: error.message });
}

Query patterns

Common database query patterns used throughout the API:

Create with stored procedure

POS uses stored procedures for complex inserts:

src/models/cliente.model.js:2-15

const createCliente = async (pool, data) => {
  const query = `
        CALL sp_crear_cliente($1, $2, $3, $4, $5, $6, $7, $8)
    `;

  const values = [
    data.ct_codigo, null, data.cli_nombre, data.cli_ruc_ced,
    data.cli_telefono, data.cli_mail, data.cli_direccion, data.cli_celular
  ];

  await pool.query(query, values);

  return { ...data, cli_codigo: 'GENERATED_BY_DB', cli_estado: estado };
};

Read with pagination

Paginated queries use LIMIT and OFFSET:

src/models/cliente.model.js:43-55

const getAllClientes = async (pool, page = 1) => {
  const limit = 20;
  const offset = (page - 1) * limit;

  const query = `
        SELECT * FROM public.cliente WHERE cli_estado = '${process.env.ACTIVE_STATUS_INDEPENDENT}'
        ORDER BY cli_nombre ASC
        LIMIT $1 OFFSET $2
    `;

  const result = await pool.query(query, [limit, offset]);
  return result.rows;
};

Update with COALESCE

Partial updates use COALESCE to keep existing values:

src/models/cliente.model.js:18-40

const updateCliente = async (pool, id, data) => {
  const query = `
        UPDATE public.cliente
        SET 
            cli_nombre = COALESCE($1, cli_nombre),
            cli_telefono = COALESCE($2, cli_telefono),
            cli_mail = COALESCE($3, cli_mail),
            cli_direccion = COALESCE($4, cli_direccion),
            cli_celular = COALESCE($5, cli_celular),
            cli_ruc_ced = COALESCE($6, cli_ruc_ced),
            ct_codigo = COALESCE($7, ct_codigo)
        WHERE cli_codigo = $8
        RETURNING *;
    `;

  const values = [
    data.cli_nombre, data.cli_telefono, data.cli_mail,
    data.cli_direccion, data.cli_celular, data.cli_ruc_ced, data.ct_codigo, id
  ];

  const result = await pool.query(query, values);
  return result.rows[0];
};

Soft delete

Logical deletion updates status instead of deleting rows:

src/models/cliente.model.js:76-80

const deleteCliente = async (pool, id) => {
  const query = `UPDATE public.cliente SET cli_estado = '${process.env.INACTIVE_STATUS_INDEPENDENT}', cli_fecha_alta = CURRENT_TIMESTAMP WHERE cli_codigo = $1`;
  await pool.query(query, [id]);
  return true;
};

Search with ILIKE

Case-insensitive search using ILIKE:

src/models/cliente.model.js:65-73

const getClienteByName = async (pool, name) => {
  const query = `
        SELECT * FROM public.cliente 
        WHERE cli_nombre ILIKE $1 
        LIMIT 10
    `;
  const result = await pool.query(query, [`%${name}%`]);
  return result.rows;
};

Common database issues

Connection refused errors

Error: ECONNREFUSED or Connection refusedCauses:

PostgreSQL is not running
Wrong host or port in .env
Firewall blocking connection

Solutions:

# Check if PostgreSQL is running
pg_isready -h localhost -p 5432

# Start PostgreSQL (varies by OS)
sudo systemctl start postgresql  # Linux
brew services start postgresql   # macOS

# Verify port
psql -h localhost -p 5432 -U postgres -c "SELECT version();"

Authentication failed

Error: password authentication failed for userCauses:

Incorrect username or password
User doesn’t exist in database
pg_hba.conf doesn’t allow password authentication

Solutions:

-- Check if user exists
SELECT * FROM pg_roles WHERE rolname = 'your_user';

-- Reset password
ALTER USER your_user WITH PASSWORD 'new_password';

Check pg_hba.conf allows md5 or scram-sha-256 authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5

Permission denied errors

Error: permission denied for table or must be owner of tableCauses:

Database user lacks required privileges

Solutions:

-- Grant all privileges on a table
GRANT ALL PRIVILEGES ON TABLE cliente TO pos_user;

-- Grant all privileges on all tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO pos_user;

-- Grant sequence usage (for auto-increment)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO pos_user;

Too many connections

Error: sorry, too many clients alreadyCauses:

Not closing POS connection pools
PostgreSQL max_connections limit reached

Solutions:

Ensure all controllers have await pool.end() in finally blocks
Increase PostgreSQL max_connections in postgresql.conf:
```
max_connections = 200
```
Check for connection leaks:
```
SELECT COUNT(*) FROM pg_stat_activity;
```

Next steps

Authentication

Learn how JWT credentials enable database-level security

Error Handling

Handle database errors gracefully

POS API Reference

Explore all database-backed endpoints

Architecture

Understand how databases fit into the architecture

Get Started

Core Concepts

Database architecture

POS Database

E-commerce Database

Connection strategies

POS: Credential-based connections

E-commerce: Shared connection pool

Environment variables

Database setup

POS database setup

E-commerce database setup

Database schema overview

POS database entities

E-commerce database entities

Connection pooling best practices

POS: Always close pools

E-commerce: Reuse connections

Use parameterized queries

Handle connection errors

Query patterns

Common database issues

Next steps

Authentication

Error Handling

POS API Reference

Architecture

Build docs developers (and LLMs) love

Get Started

Core Concepts

​Database architecture

POS Database

E-commerce Database

​Connection strategies

​POS: Credential-based connections

​E-commerce: Shared connection pool

​Environment variables

​Database setup

​POS database setup

​E-commerce database setup

​Database schema overview

​POS database entities

​E-commerce database entities

​Connection pooling best practices

POS: Always close pools

E-commerce: Reuse connections

Use parameterized queries

Handle connection errors

​Query patterns

​Common database issues

​Next steps

Authentication

Error Handling

POS API Reference

Architecture

Build docs developers (and LLMs) love

Database architecture

Connection strategies

POS: Credential-based connections

E-commerce: Shared connection pool

Environment variables

Database setup

POS database setup

E-commerce database setup

Database schema overview

POS database entities

E-commerce database entities

Connection pooling best practices

Query patterns

Common database issues

Next steps