rckrdmrd 59f1e3badf Initial commit - erp-core

2026-01-04 06:12:07 -06:00

29 KiB

Raw Permalink Blame History

DDL-SPEC: Schema core_financial

Identificacion

Campo	Valor
Schema	core_financial
Modulo	MGN-010
Version	1.0
Estado	En Diseno
Autor	Requirements-Analyst
Fecha	2025-12-05

Descripcion General

El schema core_financial implementa las bases del sistema contable: plan de cuentas jerarquico, manejo multi-moneda con conversion, periodos fiscales, y asientos contables con partida doble. Sirve como fundamento para modulos financieros de verticales.

RF Cubiertos

RF	Titulo	Tablas
RF-FIN-001	Plan de Cuentas	charts_of_accounts, accounts, account_types
RF-FIN-002	Monedas y Cambio	tenant_currencies, exchange_rates
RF-FIN-003	Periodos	fiscal_years, fiscal_periods
RF-FIN-004	Asientos	journal_entries, journal_lines, cost_centers

Diagrama ER

erDiagram
    charts_of_accounts {
        uuid id PK
        uuid tenant_id FK
        varchar code
        varchar name
        boolean is_active
    }

    account_types {
        uuid id PK
        varchar code UK
        varchar name
        varchar classification
        varchar normal_balance
        int display_order
    }

    accounts {
        uuid id PK
        uuid chart_id FK
        uuid parent_id FK
        uuid account_type_id FK
        varchar code
        varchar name
        int level
        boolean is_detail
        decimal opening_balance
    }

    tenant_currencies {
        uuid id PK
        uuid tenant_id FK
        varchar currency_code FK
        boolean is_base
        boolean is_active
    }

    exchange_rates {
        uuid id PK
        uuid tenant_id FK
        varchar from_currency
        varchar to_currency
        decimal rate
        date effective_date
    }

    fiscal_years {
        uuid id PK
        uuid tenant_id FK
        varchar name
        date start_date
        date end_date
        varchar status
    }

    fiscal_periods {
        uuid id PK
        uuid fiscal_year_id FK
        varchar name
        int period_number
        date start_date
        date end_date
        varchar status
    }

    journal_entries {
        uuid id PK
        uuid tenant_id FK
        uuid fiscal_period_id FK
        varchar entry_number
        date entry_date
        varchar currency_code
        decimal exchange_rate
        varchar status
        text description
    }

    journal_lines {
        uuid id PK
        uuid journal_entry_id FK
        uuid account_id FK
        uuid cost_center_id FK
        decimal debit
        decimal credit
        decimal debit_base
        decimal credit_base
        text description
    }

    cost_centers {
        uuid id PK
        uuid tenant_id FK
        uuid parent_id FK
        varchar code
        varchar name
        boolean is_active
    }

    charts_of_accounts ||--o{ accounts : "contiene"
    accounts ||--o{ accounts : "padre"
    accounts }o--|| account_types : "tipo"
    fiscal_years ||--o{ fiscal_periods : "contiene"
    fiscal_periods ||--o{ journal_entries : "periodo"
    journal_entries ||--o{ journal_lines : "lineas"
    accounts ||--o{ journal_lines : "cuenta"
    cost_centers ||--o{ journal_lines : "centro_costo"

Tablas

1. account_types

Tipos de cuenta predefinidos (global).

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`code`	VARCHAR(20)	NOT NULL	-	Codigo unico
`name`	VARCHAR(100)	NOT NULL	-	Nombre
`classification`	VARCHAR(20)	NOT NULL	-	Clasificacion contable
`normal_balance`	VARCHAR(10)	NOT NULL	-	Saldo normal
`affects_cash_flow`	BOOLEAN	NOT NULL	false	Afecta flujo de caja
`display_order`	INTEGER	NOT NULL	0	Orden en reportes

CREATE TABLE core_financial.account_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    classification VARCHAR(20) NOT NULL,
    normal_balance VARCHAR(10) NOT NULL,
    affects_cash_flow BOOLEAN NOT NULL DEFAULT false,
    display_order INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT chk_classification CHECK (classification IN (
        'asset', 'liability', 'equity', 'revenue', 'expense'
    )),
    CONSTRAINT chk_normal_balance CHECK (normal_balance IN ('debit', 'credit'))
);

-- Seed data
INSERT INTO core_financial.account_types (code, name, classification, normal_balance, display_order) VALUES
('ASSET_CURRENT', 'Activo Circulante', 'asset', 'debit', 1),
('ASSET_FIXED', 'Activo Fijo', 'asset', 'debit', 2),
('ASSET_OTHER', 'Otros Activos', 'asset', 'debit', 3),
('LIABILITY_CURRENT', 'Pasivo Circulante', 'liability', 'credit', 4),
('LIABILITY_LONG', 'Pasivo a Largo Plazo', 'liability', 'credit', 5),
('EQUITY', 'Capital', 'equity', 'credit', 6),
('REVENUE', 'Ingresos', 'revenue', 'credit', 7),
('EXPENSE', 'Gastos', 'expense', 'debit', 8),
('COGS', 'Costo de Ventas', 'expense', 'debit', 9),
('OTHER_INCOME', 'Otros Ingresos', 'revenue', 'credit', 10),
('OTHER_EXPENSE', 'Otros Gastos', 'expense', 'debit', 11);

2. charts_of_accounts

Planes de cuentas por tenant.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`code`	VARCHAR(20)	NOT NULL	-	Codigo
`name`	VARCHAR(255)	NOT NULL	-	Nombre del plan
`description`	TEXT	NULL	-	Descripcion
`is_active`	BOOLEAN	NOT NULL	true	Activo
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion

CREATE TABLE core_financial.charts_of_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_charts_tenant_code UNIQUE (tenant_id, code)
);

-- RLS
ALTER TABLE core_financial.charts_of_accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.charts_of_accounts
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

3. accounts

Cuentas contables jerarquicas.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`chart_id`	UUID	NOT NULL	-	FK a charts_of_accounts
`tenant_id`	UUID	NOT NULL	-	FK a tenants (redundante para RLS)
`parent_id`	UUID	NULL	-	Cuenta padre
`account_type_id`	UUID	NOT NULL	-	FK a account_types
`code`	VARCHAR(20)	NOT NULL	-	Codigo cuenta
`name`	VARCHAR(255)	NOT NULL	-	Nombre
`description`	TEXT	NULL	-	Descripcion
`level`	INTEGER	NOT NULL	1	Nivel jerarquico
`path`	LTREE	NULL	-	Path materializado
`is_detail`	BOOLEAN	NOT NULL	true	Cuenta de detalle
`is_bank`	BOOLEAN	NOT NULL	false	Es cuenta bancaria
`is_cash`	BOOLEAN	NOT NULL	false	Es cuenta de caja
`currency_code`	VARCHAR(3)	NULL	-	Moneda (si especifica)
`opening_balance`	DECIMAL(18,4)	NOT NULL	0	Saldo inicial
`current_balance`	DECIMAL(18,4)	NOT NULL	0	Saldo actual
`is_active`	BOOLEAN	NOT NULL	true	Activa
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE core_financial.accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chart_id UUID NOT NULL REFERENCES core_financial.charts_of_accounts(id),
    tenant_id UUID NOT NULL,
    parent_id UUID REFERENCES core_financial.accounts(id),
    account_type_id UUID NOT NULL REFERENCES core_financial.account_types(id),
    code VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    level INTEGER NOT NULL DEFAULT 1,
    path LTREE,
    is_detail BOOLEAN NOT NULL DEFAULT true,
    is_bank BOOLEAN NOT NULL DEFAULT false,
    is_cash BOOLEAN NOT NULL DEFAULT false,
    currency_code VARCHAR(3),
    opening_balance DECIMAL(18,4) NOT NULL DEFAULT 0,
    current_balance DECIMAL(18,4) NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_accounts_chart_code UNIQUE (chart_id, code),
    CONSTRAINT chk_accounts_level CHECK (level >= 1 AND level <= 10)
);

CREATE INDEX idx_accounts_chart ON core_financial.accounts(chart_id);
CREATE INDEX idx_accounts_parent ON core_financial.accounts(parent_id);
CREATE INDEX idx_accounts_type ON core_financial.accounts(account_type_id);
CREATE INDEX idx_accounts_path ON core_financial.accounts USING GIST (path);
CREATE INDEX idx_accounts_detail ON core_financial.accounts(chart_id, is_detail)
    WHERE is_detail = true;

-- RLS
ALTER TABLE core_financial.accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.accounts
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

4. tenant_currencies

Monedas habilitadas por tenant.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`currency_code`	VARCHAR(3)	NOT NULL	-	FK a currencies
`is_base`	BOOLEAN	NOT NULL	false	Moneda base
`is_active`	BOOLEAN	NOT NULL	true	Activa
`decimal_places`	INTEGER	NOT NULL	2	Decimales
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion

CREATE TABLE core_financial.tenant_currencies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    currency_code VARCHAR(3) NOT NULL,
    is_base BOOLEAN NOT NULL DEFAULT false,
    is_active BOOLEAN NOT NULL DEFAULT true,
    decimal_places INTEGER NOT NULL DEFAULT 2,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_tenant_currencies UNIQUE (tenant_id, currency_code),
    CONSTRAINT fk_tenant_currencies_currency
        FOREIGN KEY (currency_code) REFERENCES core_catalogs.currencies(code)
);

-- Solo una moneda base por tenant
CREATE UNIQUE INDEX idx_tenant_currencies_base ON core_financial.tenant_currencies(tenant_id)
    WHERE is_base = true;

-- RLS
ALTER TABLE core_financial.tenant_currencies ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.tenant_currencies
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

5. exchange_rates

Tipos de cambio historicos.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`from_currency`	VARCHAR(3)	NOT NULL	-	Moneda origen
`to_currency`	VARCHAR(3)	NOT NULL	-	Moneda destino
`rate`	DECIMAL(18,8)	NOT NULL	-	Tasa de cambio
`effective_date`	DATE	NOT NULL	-	Fecha efectiva
`source`	VARCHAR(50)	NULL	-	Fuente del tipo
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
`created_by`	UUID	NULL	-	Usuario creador

CREATE TABLE core_financial.exchange_rates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    from_currency VARCHAR(3) NOT NULL,
    to_currency VARCHAR(3) NOT NULL,
    rate DECIMAL(18,8) NOT NULL,
    effective_date DATE NOT NULL,
    source VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,

    CONSTRAINT uk_exchange_rates UNIQUE (tenant_id, from_currency, to_currency, effective_date),
    CONSTRAINT chk_rate_positive CHECK (rate > 0)
);

CREATE INDEX idx_exchange_rates_lookup ON core_financial.exchange_rates(
    tenant_id, from_currency, to_currency, effective_date DESC
);

-- RLS
ALTER TABLE core_financial.exchange_rates ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.exchange_rates
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

6. fiscal_years

Anos fiscales.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`name`	VARCHAR(50)	NOT NULL	-	Nombre (ej: "2025")
`start_date`	DATE	NOT NULL	-	Fecha inicio
`end_date`	DATE	NOT NULL	-	Fecha fin
`status`	VARCHAR(20)	NOT NULL	'open'	Estado
`closed_at`	TIMESTAMPTZ	NULL	-	Fecha cierre
`closed_by`	UUID	NULL	-	Usuario que cerro
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion

CREATE TABLE core_financial.fiscal_years (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    closed_at TIMESTAMPTZ,
    closed_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_fiscal_years_tenant_name UNIQUE (tenant_id, name),
    CONSTRAINT chk_fiscal_years_dates CHECK (end_date > start_date),
    CONSTRAINT chk_fiscal_years_status CHECK (status IN ('open', 'closing', 'closed'))
);

-- RLS
ALTER TABLE core_financial.fiscal_years ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.fiscal_years
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

7. fiscal_periods

Periodos contables dentro de ano fiscal.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`fiscal_year_id`	UUID	NOT NULL	-	FK a fiscal_years
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`name`	VARCHAR(50)	NOT NULL	-	Nombre periodo
`period_number`	INTEGER	NOT NULL	-	Numero secuencial
`start_date`	DATE	NOT NULL	-	Fecha inicio
`end_date`	DATE	NOT NULL	-	Fecha fin
`status`	VARCHAR(20)	NOT NULL	'open'	Estado
`is_adjustment`	BOOLEAN	NOT NULL	false	Periodo de ajuste
`closed_at`	TIMESTAMPTZ	NULL	-	Fecha cierre
`closed_by`	UUID	NULL	-	Usuario que cerro

CREATE TABLE core_financial.fiscal_periods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiscal_year_id UUID NOT NULL REFERENCES core_financial.fiscal_years(id),
    tenant_id UUID NOT NULL,
    name VARCHAR(50) NOT NULL,
    period_number INTEGER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    is_adjustment BOOLEAN NOT NULL DEFAULT false,
    closed_at TIMESTAMPTZ,
    closed_by UUID,

    CONSTRAINT uk_fiscal_periods_year_number UNIQUE (fiscal_year_id, period_number),
    CONSTRAINT chk_fiscal_periods_dates CHECK (end_date >= start_date),
    CONSTRAINT chk_fiscal_periods_status CHECK (status IN ('open', 'closing', 'closed'))
);

CREATE INDEX idx_fiscal_periods_year ON core_financial.fiscal_periods(fiscal_year_id);
CREATE INDEX idx_fiscal_periods_date ON core_financial.fiscal_periods(tenant_id, start_date, end_date);
CREATE INDEX idx_fiscal_periods_open ON core_financial.fiscal_periods(tenant_id, status)
    WHERE status = 'open';

-- RLS
ALTER TABLE core_financial.fiscal_periods ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.fiscal_periods
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

8. cost_centers

Centros de costo.

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`parent_id`	UUID	NULL	-	Centro padre
`code`	VARCHAR(20)	NOT NULL	-	Codigo
`name`	VARCHAR(255)	NOT NULL	-	Nombre
`level`	INTEGER	NOT NULL	1	Nivel
`is_active`	BOOLEAN	NOT NULL	true	Activo
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion

CREATE TABLE core_financial.cost_centers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    parent_id UUID REFERENCES core_financial.cost_centers(id),
    code VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    level INTEGER NOT NULL DEFAULT 1,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_cost_centers_tenant_code UNIQUE (tenant_id, code)
);

-- RLS
ALTER TABLE core_financial.cost_centers ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.cost_centers
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

9. journal_entries

Asientos contables (cabecera).

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK a tenants
`fiscal_period_id`	UUID	NOT NULL	-	FK a fiscal_periods
`entry_number`	VARCHAR(20)	NOT NULL	-	Numero secuencial
`entry_date`	DATE	NOT NULL	-	Fecha contable
`currency_code`	VARCHAR(3)	NOT NULL	-	Moneda
`exchange_rate`	DECIMAL(18,8)	NOT NULL	1	Tipo cambio a base
`reference`	VARCHAR(100)	NULL	-	Referencia externa
`description`	TEXT	NOT NULL	-	Descripcion
`source_module`	VARCHAR(50)	NULL	-	Modulo origen
`source_document_id`	UUID	NULL	-	Documento origen
`status`	VARCHAR(20)	NOT NULL	'draft'	Estado
`total_debit`	DECIMAL(18,4)	NOT NULL	0	Total debito
`total_credit`	DECIMAL(18,4)	NOT NULL	0	Total credito
`posted_at`	TIMESTAMPTZ	NULL	-	Fecha contabilizado
`posted_by`	UUID	NULL	-	Usuario que contabilizo
`reversed_by`	UUID	NULL	-	Asiento reverso
`created_by`	UUID	NOT NULL	-	Usuario creador
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha actualizacion

CREATE TABLE core_financial.journal_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    fiscal_period_id UUID NOT NULL REFERENCES core_financial.fiscal_periods(id),
    entry_number VARCHAR(20) NOT NULL,
    entry_date DATE NOT NULL,
    currency_code VARCHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,8) NOT NULL DEFAULT 1,
    reference VARCHAR(100),
    description TEXT NOT NULL,
    source_module VARCHAR(50),
    source_document_id UUID,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    total_debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    posted_at TIMESTAMPTZ,
    posted_by UUID,
    reversed_by UUID REFERENCES core_financial.journal_entries(id),
    created_by UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_journal_entries_number UNIQUE (tenant_id, entry_number),
    CONSTRAINT chk_journal_status CHECK (status IN ('draft', 'posted', 'reversed')),
    CONSTRAINT chk_journal_balance CHECK (
        status = 'draft' OR (total_debit = total_credit AND total_debit > 0)
    )
);

CREATE INDEX idx_journal_entries_period ON core_financial.journal_entries(fiscal_period_id);
CREATE INDEX idx_journal_entries_date ON core_financial.journal_entries(tenant_id, entry_date);
CREATE INDEX idx_journal_entries_status ON core_financial.journal_entries(status);
CREATE INDEX idx_journal_entries_source ON core_financial.journal_entries(source_module, source_document_id);

-- RLS
ALTER TABLE core_financial.journal_entries ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core_financial.journal_entries
    FOR ALL USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

10. journal_lines

Lineas de asiento (partida doble).

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`journal_entry_id`	UUID	NOT NULL	-	FK a journal_entries
`line_number`	INTEGER	NOT NULL	-	Numero de linea
`account_id`	UUID	NOT NULL	-	FK a accounts
`cost_center_id`	UUID	NULL	-	FK a cost_centers
`debit`	DECIMAL(18,4)	NOT NULL	0	Monto debito (moneda doc)
`credit`	DECIMAL(18,4)	NOT NULL	0	Monto credito (moneda doc)
`debit_base`	DECIMAL(18,4)	NOT NULL	0	Debito en moneda base
`credit_base`	DECIMAL(18,4)	NOT NULL	0	Credito en moneda base
`description`	TEXT	NULL	-	Descripcion linea
`reference`	VARCHAR(100)	NULL	-	Referencia

CREATE TABLE core_financial.journal_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    journal_entry_id UUID NOT NULL REFERENCES core_financial.journal_entries(id) ON DELETE CASCADE,
    line_number INTEGER NOT NULL,
    account_id UUID NOT NULL REFERENCES core_financial.accounts(id),
    cost_center_id UUID REFERENCES core_financial.cost_centers(id),
    debit DECIMAL(18,4) NOT NULL DEFAULT 0,
    credit DECIMAL(18,4) NOT NULL DEFAULT 0,
    debit_base DECIMAL(18,4) NOT NULL DEFAULT 0,
    credit_base DECIMAL(18,4) NOT NULL DEFAULT 0,
    description TEXT,
    reference VARCHAR(100),

    CONSTRAINT uk_journal_lines_entry_line UNIQUE (journal_entry_id, line_number),
    CONSTRAINT chk_journal_lines_amounts CHECK (
        (debit > 0 AND credit = 0) OR (debit = 0 AND credit > 0) OR (debit = 0 AND credit = 0)
    )
);

CREATE INDEX idx_journal_lines_entry ON core_financial.journal_lines(journal_entry_id);
CREATE INDEX idx_journal_lines_account ON core_financial.journal_lines(account_id);
CREATE INDEX idx_journal_lines_cost_center ON core_financial.journal_lines(cost_center_id);

Funciones de Utilidad

Obtener Tipo de Cambio

CREATE OR REPLACE FUNCTION core_financial.get_exchange_rate(
    p_tenant_id UUID,
    p_from_currency VARCHAR,
    p_to_currency VARCHAR,
    p_date DATE DEFAULT CURRENT_DATE
) RETURNS DECIMAL(18,8) AS $$
DECLARE
    v_rate DECIMAL(18,8);
BEGIN
    IF p_from_currency = p_to_currency THEN
        RETURN 1;
    END IF;

    -- Buscar tasa directa
    SELECT rate INTO v_rate
    FROM core_financial.exchange_rates
    WHERE tenant_id = p_tenant_id
    AND from_currency = p_from_currency
    AND to_currency = p_to_currency
    AND effective_date <= p_date
    ORDER BY effective_date DESC
    LIMIT 1;

    IF v_rate IS NOT NULL THEN
        RETURN v_rate;
    END IF;

    -- Buscar tasa inversa
    SELECT 1.0 / rate INTO v_rate
    FROM core_financial.exchange_rates
    WHERE tenant_id = p_tenant_id
    AND from_currency = p_to_currency
    AND to_currency = p_from_currency
    AND effective_date <= p_date
    ORDER BY effective_date DESC
    LIMIT 1;

    IF v_rate IS NOT NULL THEN
        RETURN v_rate;
    END IF;

    RAISE EXCEPTION 'Exchange rate not found for % to % on %', p_from_currency, p_to_currency, p_date;
END;
$$ LANGUAGE plpgsql STABLE;

Obtener Periodo para Fecha

CREATE OR REPLACE FUNCTION core_financial.get_period_for_date(
    p_tenant_id UUID,
    p_date DATE
) RETURNS UUID AS $$
DECLARE
    v_period_id UUID;
BEGIN
    SELECT id INTO v_period_id
    FROM core_financial.fiscal_periods
    WHERE tenant_id = p_tenant_id
    AND p_date BETWEEN start_date AND end_date
    AND status = 'open'
    LIMIT 1;

    IF v_period_id IS NULL THEN
        RAISE EXCEPTION 'No open period found for date %', p_date;
    END IF;

    RETURN v_period_id;
END;
$$ LANGUAGE plpgsql STABLE;

Contabilizar Asiento

CREATE OR REPLACE FUNCTION core_financial.post_journal_entry(
    p_entry_id UUID,
    p_user_id UUID
) RETURNS BOOLEAN AS $$
DECLARE
    v_entry RECORD;
    v_line RECORD;
    v_period_status VARCHAR;
BEGIN
    -- Obtener asiento
    SELECT * INTO v_entry
    FROM core_financial.journal_entries
    WHERE id = p_entry_id FOR UPDATE;

    IF v_entry IS NULL THEN
        RAISE EXCEPTION 'Journal entry not found';
    END IF;

    IF v_entry.status != 'draft' THEN
        RAISE EXCEPTION 'Entry is not in draft status';
    END IF;

    -- Verificar periodo abierto
    SELECT status INTO v_period_status
    FROM core_financial.fiscal_periods
    WHERE id = v_entry.fiscal_period_id;

    IF v_period_status != 'open' THEN
        RAISE EXCEPTION 'Fiscal period is not open';
    END IF;

    -- Verificar balance
    IF v_entry.total_debit != v_entry.total_credit THEN
        RAISE EXCEPTION 'Entry is not balanced: debit=% credit=%',
            v_entry.total_debit, v_entry.total_credit;
    END IF;

    -- Actualizar saldos de cuentas
    FOR v_line IN
        SELECT jl.*, a.current_balance, at.normal_balance
        FROM core_financial.journal_lines jl
        JOIN core_financial.accounts a ON a.id = jl.account_id
        JOIN core_financial.account_types at ON at.id = a.account_type_id
        WHERE jl.journal_entry_id = p_entry_id
    LOOP
        UPDATE core_financial.accounts
        SET current_balance = current_balance +
            CASE WHEN v_line.normal_balance = 'debit'
                THEN v_line.debit_base - v_line.credit_base
                ELSE v_line.credit_base - v_line.debit_base
            END,
            updated_at = NOW()
        WHERE id = v_line.account_id;
    END LOOP;

    -- Marcar como contabilizado
    UPDATE core_financial.journal_entries
    SET status = 'posted',
        posted_at = NOW(),
        posted_by = p_user_id
    WHERE id = p_entry_id;

    RETURN true;
END;
$$ LANGUAGE plpgsql;

Obtener Saldo de Cuenta por Periodo

CREATE OR REPLACE FUNCTION core_financial.get_account_balance(
    p_account_id UUID,
    p_as_of_date DATE DEFAULT NULL
) RETURNS TABLE (
    debit_total DECIMAL(18,4),
    credit_total DECIMAL(18,4),
    balance DECIMAL(18,4)
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COALESCE(SUM(jl.debit_base), 0) as debit_total,
        COALESCE(SUM(jl.credit_base), 0) as credit_total,
        COALESCE(SUM(jl.debit_base - jl.credit_base), 0) as balance
    FROM core_financial.journal_lines jl
    JOIN core_financial.journal_entries je ON je.id = jl.journal_entry_id
    WHERE jl.account_id = p_account_id
    AND je.status = 'posted'
    AND (p_as_of_date IS NULL OR je.entry_date <= p_as_of_date);
END;
$$ LANGUAGE plpgsql STABLE;

Seed Data

Crear Plan de Cuentas Base

CREATE OR REPLACE FUNCTION core_financial.create_default_chart(
    p_tenant_id UUID
) RETURNS UUID AS $$
DECLARE
    v_chart_id UUID;
    v_type_id UUID;
BEGIN
    -- Crear chart
    INSERT INTO core_financial.charts_of_accounts (tenant_id, code, name)
    VALUES (p_tenant_id, 'PRINCIPAL', 'Plan de Cuentas Principal')
    RETURNING id INTO v_chart_id;

    -- Cuentas principales
    -- Activos
    SELECT id INTO v_type_id FROM core_financial.account_types WHERE code = 'ASSET_CURRENT';
    INSERT INTO core_financial.accounts (chart_id, tenant_id, account_type_id, code, name, level, is_detail)
    VALUES
    (v_chart_id, p_tenant_id, v_type_id, '1', 'ACTIVO', 1, false),
    (v_chart_id, p_tenant_id, v_type_id, '1.1', 'Activo Circulante', 2, false),
    (v_chart_id, p_tenant_id, v_type_id, '1.1.1', 'Caja y Bancos', 3, false),
    (v_chart_id, p_tenant_id, v_type_id, '1.1.1.1', 'Caja General', 4, true),
    (v_chart_id, p_tenant_id, v_type_id, '1.1.1.2', 'Bancos', 4, true),
    (v_chart_id, p_tenant_id, v_type_id, '1.1.2', 'Cuentas por Cobrar', 3, true);

    -- Pasivos
    SELECT id INTO v_type_id FROM core_financial.account_types WHERE code = 'LIABILITY_CURRENT';
    INSERT INTO core_financial.accounts (chart_id, tenant_id, account_type_id, code, name, level, is_detail)
    VALUES
    (v_chart_id, p_tenant_id, v_type_id, '2', 'PASIVO', 1, false),
    (v_chart_id, p_tenant_id, v_type_id, '2.1', 'Pasivo Circulante', 2, false),
    (v_chart_id, p_tenant_id, v_type_id, '2.1.1', 'Cuentas por Pagar', 3, true);

    -- Capital
    SELECT id INTO v_type_id FROM core_financial.account_types WHERE code = 'EQUITY';
    INSERT INTO core_financial.accounts (chart_id, tenant_id, account_type_id, code, name, level, is_detail)
    VALUES
    (v_chart_id, p_tenant_id, v_type_id, '3', 'CAPITAL', 1, false),
    (v_chart_id, p_tenant_id, v_type_id, '3.1', 'Capital Social', 2, true);

    -- Ingresos
    SELECT id INTO v_type_id FROM core_financial.account_types WHERE code = 'REVENUE';
    INSERT INTO core_financial.accounts (chart_id, tenant_id, account_type_id, code, name, level, is_detail)
    VALUES
    (v_chart_id, p_tenant_id, v_type_id, '4', 'INGRESOS', 1, false),
    (v_chart_id, p_tenant_id, v_type_id, '4.1', 'Ingresos Operativos', 2, true);

    -- Gastos
    SELECT id INTO v_type_id FROM core_financial.account_types WHERE code = 'EXPENSE';
    INSERT INTO core_financial.accounts (chart_id, tenant_id, account_type_id, code, name, level, is_detail)
    VALUES
    (v_chart_id, p_tenant_id, v_type_id, '5', 'GASTOS', 1, false),
    (v_chart_id, p_tenant_id, v_type_id, '5.1', 'Gastos Operativos', 2, true);

    RETURN v_chart_id;
END;
$$ LANGUAGE plpgsql;

Historial

Version	Fecha	Autor	Cambios
1.0	2025-12-05	Requirements-Analyst	Creacion inicial

29 KiB Raw Permalink Blame History