Diseño del Esquema
La base de datos utiliza un diseño relacional normalizado con énfasis en la integridad referencial y optimización de consultas.
Tablas Principales
SQL
-- Usuarios del sistema
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'lawyer', 'assistant', 'client')),
is_active BOOLEAN DEFAULT true,
plan_type VARCHAR(50) DEFAULT 'trial',
max_sociedades INTEGER DEFAULT 10,
current_sociedades_count INTEGER DEFAULT 0,
trial_start_date TIMESTAMP,
trial_end_date TIMESTAMP,
subscription_status VARCHAR(50) DEFAULT 'trial',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Clientes (sociedades)
CREATE TABLE clients (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id),
name VARCHAR(255) NOT NULL,
registration_number VARCHAR(100),
entity_type VARCHAR(100),
status VARCHAR(50) DEFAULT 'AL DÍA',
incorporation_date DATE,
-- RUC con formato panameño
ruc VARCHAR(50),
ruc_folio VARCHAR(20),
ruc_periodo_fiscal INTEGER,
ruc_ano_inscripcion INTEGER,
ruc_completo VARCHAR(50) GENERATED ALWAYS AS (
CASE
WHEN ruc_folio IS NOT NULL AND ruc_periodo_fiscal IS NOT NULL AND ruc_ano_inscripcion IS NOT NULL
THEN ruc_folio || '-' || ruc_periodo_fiscal || '-' || ruc_ano_inscripcion
ELSE ruc
END
) STORED,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Personas (directores, beneficiarios)
CREATE TABLE persons (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
identification_number VARCHAR(50) NOT NULL,
identification_type VARCHAR(50) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255),
phone VARCHAR(50),
nationality VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Directores
CREATE TABLE directors (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
person_id UUID REFERENCES persons(id),
appointment_date DATE,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Beneficiarios finales
CREATE TABLE beneficial_owners (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
person_id UUID REFERENCES persons(id),
ownership_percentage DECIMAL(5,2),
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Sistema de Documentos
SQL
-- Documentos
CREATE TABLE entity_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
person_id UUID REFERENCES persons(id),
document_type VARCHAR(100) NOT NULL,
file_name VARCHAR(255) NOT NULL,
file_path VARCHAR(500) NOT NULL,
file_size INTEGER,
upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) DEFAULT 'pending',
reviewed_by UUID REFERENCES users(id),
review_date TIMESTAMP,
review_comments TEXT
);
Sistema de Facturación
SQL
-- Facturas
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id),
invoice_number VARCHAR(50) UNIQUE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
currency VARCHAR(3) DEFAULT 'USD',
status VARCHAR(50) DEFAULT 'pending',
issue_date DATE DEFAULT CURRENT_DATE,
due_date DATE DEFAULT (CURRENT_DATE + INTERVAL '15 days'),
payment_date DATE,
payment_method VARCHAR(50),
payment_reference VARCHAR(100),
pdf_path VARCHAR(500),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Transacciones de balance de clientes
CREATE TABLE balance_transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
transaction_type VARCHAR(10) CHECK (transaction_type IN ('charge', 'payment')),
amount DECIMAL(10,2) NOT NULL,
description TEXT NOT NULL,
due_date DATE,
payment_date DATE,
payment_method VARCHAR(50),
payment_reference VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_by UUID REFERENCES users(id)
);
Índices y Optimizaciones
SQL
-- Índices para optimización de consultas
CREATE INDEX idx_clients_user_id ON clients(user_id);
CREATE INDEX idx_clients_ruc_completo ON clients(ruc_completo);
CREATE INDEX idx_documents_client_id ON entity_documents(client_id);
CREATE INDEX idx_documents_status ON entity_documents(status);
CREATE INDEX idx_balance_client_id ON balance_transactions(client_id);
CREATE INDEX idx_persons_identification ON persons(identification_number);
-- Índice para búsquedas de texto
CREATE INDEX idx_clients_name_trgm ON clients USING gin(name gin_trgm_ops);
CREATE INDEX idx_persons_name_trgm ON persons USING gin((first_name || ' ' || last_name) gin_trgm_ops);
Funciones y Triggers
SQL
-- Función para validar RUC panameño
CREATE OR REPLACE FUNCTION validate_panama_ruc(ruc_text VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
RETURN ruc_text ~ '^[0-9]{6,10}-[1-9]-[0-9]{4,6}$';
END;
$$ LANGUAGE plpgsql;
-- Trigger para actualizar timestamps
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_clients_timestamp
BEFORE UPDATE ON clients
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();