rckrdmrd a9703b2727 Initial commit - erp-construccion

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

30 KiB

Raw Permalink Blame History

DDL SPECIFICATION: Schema Finance

Version: 1.0.0 Fecha: 2025-12-05 Schema: finance Modulos: MAE-014 (Finanzas y Controlling)

Resumen

Metrica	Valor
Total Tablas	15
ENUMs	7
Funciones	8
Triggers	5
Indices	40+

1. ENUMs

-- Tipos de cuenta contable
CREATE TYPE finance.account_type AS ENUM (
  'asset',           -- Activo
  'liability',       -- Pasivo
  'equity',          -- Capital
  'income',          -- Ingreso
  'expense'          -- Gasto
);

-- Naturaleza de cuenta
CREATE TYPE finance.account_nature AS ENUM (
  'debit',           -- Deudora
  'credit'           -- Acreedora
);

-- Tipos de poliza/entrada contable
CREATE TYPE finance.entry_type AS ENUM (
  'income',          -- Ingreso
  'expense',         -- Egreso
  'journal',         -- Diario
  'transfer',        -- Traspaso
  'adjustment',      -- Ajuste
  'opening',         -- Apertura
  'closing'          -- Cierre
);

-- Estados de pago
CREATE TYPE finance.payment_status AS ENUM (
  'pending',         -- Pendiente
  'partial',         -- Pago parcial
  'paid',            -- Pagado
  'overdue',         -- Vencido
  'cancelled'        -- Cancelado
);

-- Metodos de pago
CREATE TYPE finance.payment_method AS ENUM (
  'cash',            -- Efectivo
  'check',           -- Cheque
  'transfer',        -- Transferencia
  'card',            -- Tarjeta
  'deposit',         -- Deposito
  'other'            -- Otro
);

-- Estados de conciliacion
CREATE TYPE finance.reconciliation_status AS ENUM (
  'pending',         -- Pendiente
  'matched',         -- Conciliado
  'partial',         -- Parcialmente conciliado
  'unmatched'        -- Sin conciliar
);

-- Tipos de movimiento de cash flow
CREATE TYPE finance.cash_flow_type AS ENUM (
  'operating',       -- Operativo
  'investing',       -- Inversion
  'financing'        -- Financiamiento
);

2. Tablas de Catalogo de Cuentas

2.1 chart_of_accounts (Catalogo de Cuentas)

CREATE TABLE finance.chart_of_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  code VARCHAR(30) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,

  -- Clasificacion
  account_type finance.account_type NOT NULL,
  nature finance.account_nature NOT NULL,
  level INTEGER NOT NULL DEFAULT 1,

  -- Jerarquia
  parent_id UUID REFERENCES finance.chart_of_accounts(id),
  full_path VARCHAR(500),  -- Ej: "5000/5100/5101"

  -- Configuracion
  cost_center_required BOOLEAN DEFAULT false,
  project_required BOOLEAN DEFAULT false,
  allows_transactions BOOLEAN DEFAULT true,

  -- Codigos externos
  sap_code VARCHAR(50),
  contpaqi_code VARCHAR(50),
  aspel_code VARCHAR(50),

  -- Estado
  is_active BOOLEAN DEFAULT true,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_accounts_code UNIQUE (tenant_id, code)
);

CREATE INDEX idx_accounts_tenant ON finance.chart_of_accounts(tenant_id);
CREATE INDEX idx_accounts_type ON finance.chart_of_accounts(account_type);
CREATE INDEX idx_accounts_parent ON finance.chart_of_accounts(parent_id);
CREATE INDEX idx_accounts_active ON finance.chart_of_accounts(tenant_id) WHERE is_active = true AND allows_transactions = true;
CREATE INDEX idx_accounts_path ON finance.chart_of_accounts(tenant_id, full_path);

2.2 cost_centers (Centros de Costo)

CREATE TABLE finance.cost_centers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  code VARCHAR(30) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,

  -- Jerarquia
  parent_id UUID REFERENCES finance.cost_centers(id),
  level INTEGER DEFAULT 1,

  -- Vinculacion
  project_id UUID REFERENCES construction.projects(id),
  department_id UUID,

  -- Presupuesto
  budget_amount DECIMAL(18,2),
  spent_amount DECIMAL(18,2) DEFAULT 0,

  -- Estado
  is_active BOOLEAN DEFAULT true,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_cost_centers_code UNIQUE (tenant_id, code)
);

CREATE INDEX idx_cost_centers_tenant ON finance.cost_centers(tenant_id);
CREATE INDEX idx_cost_centers_project ON finance.cost_centers(project_id);
CREATE INDEX idx_cost_centers_parent ON finance.cost_centers(parent_id);

3. Tablas de Polizas Contables

3.1 accounting_entries (Polizas)

CREATE TABLE finance.accounting_entries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  entry_number VARCHAR(30) NOT NULL,
  entry_type finance.entry_type NOT NULL,
  entry_date DATE NOT NULL,

  -- Descripcion
  description TEXT NOT NULL,
  reference VARCHAR(100),

  -- Origen
  source_module VARCHAR(50),  -- purchases, estimations, payroll, etc.
  source_id UUID,
  source_reference VARCHAR(100),

  -- Vinculacion
  project_id UUID REFERENCES construction.projects(id),
  cost_center_id UUID REFERENCES finance.cost_centers(id),

  -- Totales
  total_debit DECIMAL(18,2) NOT NULL DEFAULT 0,
  total_credit DECIMAL(18,2) NOT NULL DEFAULT 0,
  is_balanced BOOLEAN DEFAULT false,

  -- Estados
  status VARCHAR(20) DEFAULT 'draft',  -- draft, pending, posted, cancelled
  posted_at TIMESTAMP,
  posted_by UUID REFERENCES core.users(id),

  -- Periodo contable
  fiscal_year INTEGER,
  fiscal_period INTEGER,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_entries_number UNIQUE (tenant_id, entry_number),
  CONSTRAINT chk_entries_balanced CHECK (total_debit = total_credit OR status = 'draft')
);

CREATE INDEX idx_entries_tenant ON finance.accounting_entries(tenant_id);
CREATE INDEX idx_entries_date ON finance.accounting_entries(entry_date);
CREATE INDEX idx_entries_type ON finance.accounting_entries(entry_type);
CREATE INDEX idx_entries_project ON finance.accounting_entries(project_id);
CREATE INDEX idx_entries_status ON finance.accounting_entries(status);
CREATE INDEX idx_entries_period ON finance.accounting_entries(fiscal_year, fiscal_period);
CREATE INDEX idx_entries_source ON finance.accounting_entries(source_module, source_id);

3.2 accounting_entry_lines (Detalle de Polizas)

CREATE TABLE finance.accounting_entry_lines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  entry_id UUID NOT NULL REFERENCES finance.accounting_entries(id) ON DELETE CASCADE,

  -- Linea
  line_number INTEGER NOT NULL,

  -- Cuenta
  account_id UUID NOT NULL REFERENCES finance.chart_of_accounts(id),
  account_code VARCHAR(30) NOT NULL,

  -- Descripcion
  description TEXT,

  -- Montos
  debit_amount DECIMAL(18,2) DEFAULT 0,
  credit_amount DECIMAL(18,2) DEFAULT 0,

  -- Imputacion
  cost_center_id UUID REFERENCES finance.cost_centers(id),
  project_id UUID REFERENCES construction.projects(id),

  -- Tercero
  partner_id UUID,  -- Proveedor o cliente
  partner_name VARCHAR(200),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),

  CONSTRAINT chk_line_amount CHECK (
    (debit_amount > 0 AND credit_amount = 0) OR
    (credit_amount > 0 AND debit_amount = 0)
  )
);

CREATE INDEX idx_entry_lines_entry ON finance.accounting_entry_lines(entry_id);
CREATE INDEX idx_entry_lines_account ON finance.accounting_entry_lines(account_id);
CREATE INDEX idx_entry_lines_cost_center ON finance.accounting_entry_lines(cost_center_id);
CREATE INDEX idx_entry_lines_project ON finance.accounting_entry_lines(project_id);

4. Tablas de Cuentas por Pagar (AP)

4.1 accounts_payable (Cuentas por Pagar)

CREATE TABLE finance.accounts_payable (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  document_number VARCHAR(50) NOT NULL,
  document_type VARCHAR(30),  -- invoice, credit_note, debit_note

  -- Proveedor
  supplier_id UUID NOT NULL,
  supplier_name VARCHAR(200) NOT NULL,
  supplier_rfc VARCHAR(20),

  -- Origen
  purchase_order_id UUID,
  contract_id UUID,

  -- Montos
  subtotal DECIMAL(18,2) NOT NULL,
  tax_amount DECIMAL(18,2) DEFAULT 0,
  retention_amount DECIMAL(18,2) DEFAULT 0,
  total_amount DECIMAL(18,2) NOT NULL,
  paid_amount DECIMAL(18,2) DEFAULT 0,
  balance DECIMAL(18,2) GENERATED ALWAYS AS (total_amount - paid_amount) STORED,

  -- Fechas
  document_date DATE NOT NULL,
  due_date DATE NOT NULL,
  received_date DATE,

  -- Proyecto
  project_id UUID REFERENCES construction.projects(id),
  cost_center_id UUID REFERENCES finance.cost_centers(id),

  -- Estado
  status finance.payment_status NOT NULL DEFAULT 'pending',

  -- Poliza
  accounting_entry_id UUID REFERENCES finance.accounting_entries(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_ap_document UNIQUE (tenant_id, supplier_id, document_number)
);

CREATE INDEX idx_ap_tenant ON finance.accounts_payable(tenant_id);
CREATE INDEX idx_ap_supplier ON finance.accounts_payable(supplier_id);
CREATE INDEX idx_ap_project ON finance.accounts_payable(project_id);
CREATE INDEX idx_ap_status ON finance.accounts_payable(status);
CREATE INDEX idx_ap_due_date ON finance.accounts_payable(due_date);
CREATE INDEX idx_ap_overdue ON finance.accounts_payable(tenant_id)
  WHERE status IN ('pending', 'partial') AND due_date < CURRENT_DATE;

4.2 ap_payments (Pagos a Proveedores)

CREATE TABLE finance.ap_payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  payable_id UUID NOT NULL REFERENCES finance.accounts_payable(id),

  -- Pago
  payment_number VARCHAR(30) NOT NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(18,2) NOT NULL,

  -- Metodo
  payment_method finance.payment_method NOT NULL,
  bank_account_id UUID REFERENCES finance.bank_accounts(id),
  reference VARCHAR(100),

  -- Estado
  status VARCHAR(20) DEFAULT 'completed',

  -- Poliza
  accounting_entry_id UUID REFERENCES finance.accounting_entries(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_ap_payments_payable ON finance.ap_payments(payable_id);
CREATE INDEX idx_ap_payments_date ON finance.ap_payments(payment_date);
CREATE INDEX idx_ap_payments_bank ON finance.ap_payments(bank_account_id);

5. Tablas de Cuentas por Cobrar (AR)

5.1 accounts_receivable (Cuentas por Cobrar)

CREATE TABLE finance.accounts_receivable (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  document_number VARCHAR(50) NOT NULL,
  document_type VARCHAR(30),  -- invoice, credit_note, estimation

  -- Cliente
  customer_id UUID NOT NULL,
  customer_name VARCHAR(200) NOT NULL,
  customer_rfc VARCHAR(20),

  -- Origen
  estimation_id UUID,
  sale_order_id UUID,
  contract_id UUID,

  -- Montos
  subtotal DECIMAL(18,2) NOT NULL,
  tax_amount DECIMAL(18,2) DEFAULT 0,
  retention_amount DECIMAL(18,2) DEFAULT 0,
  total_amount DECIMAL(18,2) NOT NULL,
  collected_amount DECIMAL(18,2) DEFAULT 0,
  balance DECIMAL(18,2) GENERATED ALWAYS AS (total_amount - collected_amount) STORED,

  -- Fechas
  document_date DATE NOT NULL,
  due_date DATE NOT NULL,

  -- Proyecto
  project_id UUID REFERENCES construction.projects(id),

  -- Estado
  status finance.payment_status NOT NULL DEFAULT 'pending',

  -- Poliza
  accounting_entry_id UUID REFERENCES finance.accounting_entries(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_ar_document UNIQUE (tenant_id, document_number)
);

CREATE INDEX idx_ar_tenant ON finance.accounts_receivable(tenant_id);
CREATE INDEX idx_ar_customer ON finance.accounts_receivable(customer_id);
CREATE INDEX idx_ar_project ON finance.accounts_receivable(project_id);
CREATE INDEX idx_ar_status ON finance.accounts_receivable(status);
CREATE INDEX idx_ar_due_date ON finance.accounts_receivable(due_date);
CREATE INDEX idx_ar_overdue ON finance.accounts_receivable(tenant_id)
  WHERE status IN ('pending', 'partial') AND due_date < CURRENT_DATE;

5.2 ar_collections (Cobros)

CREATE TABLE finance.ar_collections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  receivable_id UUID NOT NULL REFERENCES finance.accounts_receivable(id),

  -- Cobro
  collection_number VARCHAR(30) NOT NULL,
  collection_date DATE NOT NULL,
  amount DECIMAL(18,2) NOT NULL,

  -- Metodo
  payment_method finance.payment_method NOT NULL,
  bank_account_id UUID REFERENCES finance.bank_accounts(id),
  reference VARCHAR(100),

  -- Estado
  status VARCHAR(20) DEFAULT 'completed',

  -- Poliza
  accounting_entry_id UUID REFERENCES finance.accounting_entries(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_ar_collections_receivable ON finance.ar_collections(receivable_id);
CREATE INDEX idx_ar_collections_date ON finance.ar_collections(collection_date);
CREATE INDEX idx_ar_collections_bank ON finance.ar_collections(bank_account_id);

6. Tablas Bancarias y Conciliacion

6.1 bank_accounts (Cuentas Bancarias)

CREATE TABLE finance.bank_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),

  -- Identificacion
  account_number VARCHAR(30) NOT NULL,
  clabe VARCHAR(18),
  name VARCHAR(200) NOT NULL,

  -- Banco
  bank_name VARCHAR(100) NOT NULL,
  bank_code VARCHAR(10),

  -- Tipo
  account_type VARCHAR(30),  -- checking, savings, credit

  -- Moneda
  currency VARCHAR(3) DEFAULT 'MXN',

  -- Saldos
  current_balance DECIMAL(18,2) DEFAULT 0,
  available_balance DECIMAL(18,2) DEFAULT 0,
  last_reconciled_balance DECIMAL(18,2),
  last_reconciled_date DATE,

  -- Cuenta contable
  ledger_account_id UUID REFERENCES finance.chart_of_accounts(id),

  -- Proyecto (si es cuenta dedicada)
  project_id UUID REFERENCES construction.projects(id),

  -- Estado
  is_active BOOLEAN DEFAULT true,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),
  deleted_at TIMESTAMP,

  CONSTRAINT uq_bank_accounts UNIQUE (tenant_id, account_number)
);

CREATE INDEX idx_bank_accounts_tenant ON finance.bank_accounts(tenant_id);
CREATE INDEX idx_bank_accounts_project ON finance.bank_accounts(project_id);
CREATE INDEX idx_bank_accounts_active ON finance.bank_accounts(tenant_id) WHERE is_active = true;

6.2 bank_movements (Movimientos Bancarios)

CREATE TABLE finance.bank_movements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  bank_account_id UUID NOT NULL REFERENCES finance.bank_accounts(id),

  -- Movimiento
  movement_date DATE NOT NULL,
  value_date DATE,
  reference VARCHAR(100),
  description TEXT,

  -- Montos
  debit_amount DECIMAL(18,2) DEFAULT 0,
  credit_amount DECIMAL(18,2) DEFAULT 0,
  balance_after DECIMAL(18,2),

  -- Origen (del estado de cuenta)
  bank_reference VARCHAR(100),
  import_batch_id UUID,

  -- Conciliacion
  reconciliation_status finance.reconciliation_status DEFAULT 'pending',
  reconciled_with_id UUID,  -- ID de pago/cobro conciliado
  reconciled_with_type VARCHAR(30),  -- ap_payment, ar_collection, entry_line
  reconciled_at TIMESTAMP,
  reconciled_by UUID REFERENCES core.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_bank_movements_account ON finance.bank_movements(bank_account_id);
CREATE INDEX idx_bank_movements_date ON finance.bank_movements(movement_date);
CREATE INDEX idx_bank_movements_status ON finance.bank_movements(reconciliation_status);
CREATE INDEX idx_bank_movements_pending ON finance.bank_movements(bank_account_id)
  WHERE reconciliation_status = 'pending';

6.3 bank_reconciliations (Conciliaciones)

CREATE TABLE finance.bank_reconciliations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  bank_account_id UUID NOT NULL REFERENCES finance.bank_accounts(id),

  -- Periodo
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,

  -- Saldos
  bank_opening_balance DECIMAL(18,2) NOT NULL,
  bank_closing_balance DECIMAL(18,2) NOT NULL,
  book_opening_balance DECIMAL(18,2) NOT NULL,
  book_closing_balance DECIMAL(18,2) NOT NULL,

  -- Partidas en conciliacion
  deposits_in_transit DECIMAL(18,2) DEFAULT 0,
  checks_in_transit DECIMAL(18,2) DEFAULT 0,
  bank_errors DECIMAL(18,2) DEFAULT 0,
  book_errors DECIMAL(18,2) DEFAULT 0,

  -- Resultado
  reconciled_balance DECIMAL(18,2),
  difference DECIMAL(18,2),
  is_reconciled BOOLEAN DEFAULT false,

  -- Estado
  status VARCHAR(20) DEFAULT 'draft',  -- draft, in_progress, completed, approved

  -- Aprobacion
  approved_at TIMESTAMP,
  approved_by UUID REFERENCES core.users(id),

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_reconciliations_account ON finance.bank_reconciliations(bank_account_id);
CREATE INDEX idx_reconciliations_period ON finance.bank_reconciliations(period_end);

7. Tablas de Cash Flow

7.1 cash_flow_projections (Proyecciones)

CREATE TABLE finance.cash_flow_projections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  project_id UUID REFERENCES construction.projects(id),

  -- Periodo
  period_date DATE NOT NULL,
  period_type VARCHAR(20) NOT NULL,  -- daily, weekly, monthly

  -- Ingresos proyectados
  projected_income DECIMAL(18,2) DEFAULT 0,
  projected_collections DECIMAL(18,2) DEFAULT 0,

  -- Egresos proyectados
  projected_expenses DECIMAL(18,2) DEFAULT 0,
  projected_payments DECIMAL(18,2) DEFAULT 0,

  -- Reales (actualizados)
  actual_income DECIMAL(18,2) DEFAULT 0,
  actual_expenses DECIMAL(18,2) DEFAULT 0,

  -- Saldos
  opening_balance DECIMAL(18,2) DEFAULT 0,
  projected_closing DECIMAL(18,2) DEFAULT 0,
  actual_closing DECIMAL(18,2),

  -- Clasificacion
  cash_flow_type finance.cash_flow_type DEFAULT 'operating',

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id),

  CONSTRAINT uq_cash_flow_projection UNIQUE (tenant_id, project_id, period_date, period_type)
);

CREATE INDEX idx_cash_flow_tenant ON finance.cash_flow_projections(tenant_id);
CREATE INDEX idx_cash_flow_project ON finance.cash_flow_projections(project_id);
CREATE INDEX idx_cash_flow_date ON finance.cash_flow_projections(period_date);

7.2 cash_flow_items (Detalle de Proyeccion)

CREATE TABLE finance.cash_flow_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES core.tenants(id),
  projection_id UUID NOT NULL REFERENCES finance.cash_flow_projections(id),

  -- Tipo
  item_type VARCHAR(30) NOT NULL,  -- income, expense
  category VARCHAR(50),

  -- Descripcion
  description TEXT NOT NULL,

  -- Origen
  source_type VARCHAR(30),  -- estimation, purchase_order, contract, payroll
  source_id UUID,

  -- Montos
  projected_amount DECIMAL(18,2) NOT NULL,
  actual_amount DECIMAL(18,2),

  -- Fecha esperada
  expected_date DATE,
  actual_date DATE,

  -- Auditoria
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES core.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES core.users(id)
);

CREATE INDEX idx_cash_flow_items_projection ON finance.cash_flow_items(projection_id);
CREATE INDEX idx_cash_flow_items_type ON finance.cash_flow_items(item_type);
CREATE INDEX idx_cash_flow_items_source ON finance.cash_flow_items(source_type, source_id);

8. Funciones

8.1 Calcular Totales de Poliza

CREATE OR REPLACE FUNCTION finance.calculate_entry_totals()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE finance.accounting_entries ae
  SET
    total_debit = (
      SELECT COALESCE(SUM(debit_amount), 0)
      FROM finance.accounting_entry_lines
      WHERE entry_id = ae.id
    ),
    total_credit = (
      SELECT COALESCE(SUM(credit_amount), 0)
      FROM finance.accounting_entry_lines
      WHERE entry_id = ae.id
    ),
    is_balanced = (
      SELECT COALESCE(SUM(debit_amount), 0) = COALESCE(SUM(credit_amount), 0)
      FROM finance.accounting_entry_lines
      WHERE entry_id = ae.id
    ),
    updated_at = CURRENT_TIMESTAMP
  WHERE id = COALESCE(NEW.entry_id, OLD.entry_id);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_calculate_entry_totals
AFTER INSERT OR UPDATE OR DELETE ON finance.accounting_entry_lines
FOR EACH ROW
EXECUTE FUNCTION finance.calculate_entry_totals();

8.2 Actualizar Saldo de Cuenta por Pagar

CREATE OR REPLACE FUNCTION finance.update_ap_balance()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE finance.accounts_payable ap
  SET
    paid_amount = (
      SELECT COALESCE(SUM(amount), 0)
      FROM finance.ap_payments
      WHERE payable_id = ap.id AND status = 'completed'
    ),
    status = CASE
      WHEN paid_amount >= total_amount THEN 'paid'
      WHEN paid_amount > 0 THEN 'partial'
      WHEN due_date < CURRENT_DATE THEN 'overdue'
      ELSE 'pending'
    END,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = COALESCE(NEW.payable_id, OLD.payable_id);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_ap_balance
AFTER INSERT OR UPDATE OR DELETE ON finance.ap_payments
FOR EACH ROW
EXECUTE FUNCTION finance.update_ap_balance();

8.3 Actualizar Saldo de Cuenta por Cobrar

CREATE OR REPLACE FUNCTION finance.update_ar_balance()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE finance.accounts_receivable ar
  SET
    collected_amount = (
      SELECT COALESCE(SUM(amount), 0)
      FROM finance.ar_collections
      WHERE receivable_id = ar.id AND status = 'completed'
    ),
    status = CASE
      WHEN collected_amount >= total_amount THEN 'paid'
      WHEN collected_amount > 0 THEN 'partial'
      WHEN due_date < CURRENT_DATE THEN 'overdue'
      ELSE 'pending'
    END,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = COALESCE(NEW.receivable_id, OLD.receivable_id);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_ar_balance
AFTER INSERT OR UPDATE OR DELETE ON finance.ar_collections
FOR EACH ROW
EXECUTE FUNCTION finance.update_ar_balance();

8.4 Generar Poliza desde Compra

CREATE OR REPLACE FUNCTION finance.create_entry_from_purchase(
  p_tenant_id UUID,
  p_purchase_id UUID,
  p_created_by UUID
)
RETURNS UUID AS $$
DECLARE
  v_entry_id UUID;
  v_purchase RECORD;
  v_entry_number VARCHAR(30);
BEGIN
  -- Obtener datos de la compra
  SELECT * INTO v_purchase
  FROM purchasing.purchase_orders
  WHERE id = p_purchase_id;

  -- Generar numero de poliza
  SELECT 'POL-' || TO_CHAR(CURRENT_DATE, 'YYYY') || '-' ||
         LPAD((COUNT(*) + 1)::TEXT, 6, '0')
  INTO v_entry_number
  FROM finance.accounting_entries
  WHERE tenant_id = p_tenant_id
    AND fiscal_year = EXTRACT(YEAR FROM CURRENT_DATE);

  -- Crear poliza
  INSERT INTO finance.accounting_entries (
    tenant_id, entry_number, entry_type, entry_date,
    description, reference, source_module, source_id,
    project_id, fiscal_year, fiscal_period, created_by
  )
  VALUES (
    p_tenant_id,
    v_entry_number,
    'expense',
    CURRENT_DATE,
    'Compra: ' || v_purchase.po_number,
    v_purchase.po_number,
    'purchases',
    p_purchase_id,
    v_purchase.project_id,
    EXTRACT(YEAR FROM CURRENT_DATE),
    EXTRACT(MONTH FROM CURRENT_DATE),
    p_created_by
  )
  RETURNING id INTO v_entry_id;

  -- TODO: Crear lineas de poliza segun configuracion de cuentas

  RETURN v_entry_id;
END;
$$ LANGUAGE plpgsql;

8.5 Calcular Aging de Cuentas

CREATE OR REPLACE FUNCTION finance.calculate_ap_aging(p_tenant_id UUID, p_as_of_date DATE DEFAULT CURRENT_DATE)
RETURNS TABLE (
  supplier_id UUID,
  supplier_name VARCHAR,
  current_amount DECIMAL,
  days_1_30 DECIMAL,
  days_31_60 DECIMAL,
  days_61_90 DECIMAL,
  days_over_90 DECIMAL,
  total_amount DECIMAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    ap.supplier_id,
    ap.supplier_name,
    SUM(CASE WHEN ap.due_date >= p_as_of_date THEN ap.balance ELSE 0 END) as current_amount,
    SUM(CASE WHEN ap.due_date BETWEEN p_as_of_date - 30 AND p_as_of_date - 1 THEN ap.balance ELSE 0 END) as days_1_30,
    SUM(CASE WHEN ap.due_date BETWEEN p_as_of_date - 60 AND p_as_of_date - 31 THEN ap.balance ELSE 0 END) as days_31_60,
    SUM(CASE WHEN ap.due_date BETWEEN p_as_of_date - 90 AND p_as_of_date - 61 THEN ap.balance ELSE 0 END) as days_61_90,
    SUM(CASE WHEN ap.due_date < p_as_of_date - 90 THEN ap.balance ELSE 0 END) as days_over_90,
    SUM(ap.balance) as total_amount
  FROM finance.accounts_payable ap
  WHERE ap.tenant_id = p_tenant_id
    AND ap.status IN ('pending', 'partial', 'overdue')
    AND ap.deleted_at IS NULL
  GROUP BY ap.supplier_id, ap.supplier_name;
END;
$$ LANGUAGE plpgsql;

9. Row Level Security

-- Habilitar RLS en todas las tablas
ALTER TABLE finance.chart_of_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.cost_centers ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.accounting_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.accounting_entry_lines ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.accounts_payable ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.ap_payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.accounts_receivable ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.ar_collections ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.bank_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.bank_movements ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.bank_reconciliations ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.cash_flow_projections ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance.cash_flow_items ENABLE ROW LEVEL SECURITY;

-- Crear politicas de aislamiento por tenant
DO $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'finance'
  LOOP
    EXECUTE format('
      CREATE POLICY tenant_isolation ON finance.%I
      USING (tenant_id = current_setting(''app.current_tenant_id'')::uuid)
    ', t);
  END LOOP;
END $$;

10. Seeds Iniciales

-- Catalogo de cuentas base
INSERT INTO finance.chart_of_accounts (tenant_id, code, name, account_type, nature, level)
VALUES
  -- Activos
  ('{{TENANT_ID}}', '1000', 'ACTIVO', 'asset', 'debit', 1),
  ('{{TENANT_ID}}', '1100', 'Activo Circulante', 'asset', 'debit', 2),
  ('{{TENANT_ID}}', '1101', 'Bancos', 'asset', 'debit', 3),
  ('{{TENANT_ID}}', '1102', 'Cuentas por Cobrar', 'asset', 'debit', 3),
  ('{{TENANT_ID}}', '1103', 'Inventarios', 'asset', 'debit', 3),
  -- Pasivos
  ('{{TENANT_ID}}', '2000', 'PASIVO', 'liability', 'credit', 1),
  ('{{TENANT_ID}}', '2100', 'Pasivo a Corto Plazo', 'liability', 'credit', 2),
  ('{{TENANT_ID}}', '2101', 'Proveedores', 'liability', 'credit', 3),
  ('{{TENANT_ID}}', '2102', 'Acreedores Diversos', 'liability', 'credit', 3),
  -- Capital
  ('{{TENANT_ID}}', '3000', 'CAPITAL', 'equity', 'credit', 1),
  ('{{TENANT_ID}}', '3101', 'Capital Social', 'equity', 'credit', 2),
  -- Ingresos
  ('{{TENANT_ID}}', '4000', 'INGRESOS', 'income', 'credit', 1),
  ('{{TENANT_ID}}', '4101', 'Ingresos por Obra', 'income', 'credit', 2),
  -- Gastos
  ('{{TENANT_ID}}', '5000', 'GASTOS', 'expense', 'debit', 1),
  ('{{TENANT_ID}}', '5100', 'Costo de Ventas', 'expense', 'debit', 2),
  ('{{TENANT_ID}}', '5101', 'Materiales', 'expense', 'debit', 3),
  ('{{TENANT_ID}}', '5102', 'Mano de Obra', 'expense', 'debit', 3),
  ('{{TENANT_ID}}', '5103', 'Subcontratos', 'expense', 'debit', 3);

Referencias

Ultima actualizacion: 2025-12-05

30 KiB Raw Permalink Blame History

DDL SPECIFICATION: Schema Finance

Resumen

1. ENUMs

2. Tablas de Catalogo de Cuentas

2.1 chart_of_accounts (Catalogo de Cuentas)

2.2 cost_centers (Centros de Costo)

3. Tablas de Polizas Contables

3.1 accounting_entries (Polizas)

3.2 accounting_entry_lines (Detalle de Polizas)

4. Tablas de Cuentas por Pagar (AP)

4.1 accounts_payable (Cuentas por Pagar)

4.2 ap_payments (Pagos a Proveedores)

5. Tablas de Cuentas por Cobrar (AR)

5.1 accounts_receivable (Cuentas por Cobrar)

5.2 ar_collections (Cobros)

6. Tablas Bancarias y Conciliacion

6.1 bank_accounts (Cuentas Bancarias)

6.2 bank_movements (Movimientos Bancarios)

6.3 bank_reconciliations (Conciliaciones)

7. Tablas de Cash Flow

7.1 cash_flow_projections (Proyecciones)

7.2 cash_flow_items (Detalle de Proyeccion)

8. Funciones

8.1 Calcular Totales de Poliza

8.2 Actualizar Saldo de Cuenta por Pagar

8.3 Actualizar Saldo de Cuenta por Cobrar

8.4 Generar Poliza desde Compra

8.5 Calcular Aging de Cuentas

9. Row Level Security

10. Seeds Iniciales

Referencias

30 KiB

Raw Permalink Blame History