02ab2caf26
- 00-auth-base.sql: Extracted auth.tenants+users from recreate-database.sh - 03b-core-companies.sql: DDL for auth.companies entity - 21b-inventory-extended.sql: 7 new tables for inventory entities without DDL - 24-invoices.sql: billing→operations schema to resolve duplication - 27/28/29-cfdi: Track existing CFDI DDL files - recreate-database.sh: Updated ddl_files array (17→43 entries) Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
96 lines
3.9 KiB
PL/PgSQL
96 lines
3.9 KiB
PL/PgSQL
-- =============================================================
|
|
-- ARCHIVO: 03b-core-companies.sql
|
|
-- DESCRIPCION: Tabla de empresas/companies del tenant
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-02-05
|
|
-- DEPENDE DE: 00-auth-base.sql (auth.tenants, auth.users)
|
|
-- 20-core-catalogs.sql (core.currencies)
|
|
-- 16-partners.sql (partners.partners)
|
|
-- NOTA: La entidad TypeORM usa schema 'auth'. Las FK en
|
|
-- 45-hr.sql apuntan a core.companies (inconsistencia
|
|
-- conocida a corregir en tarea separada).
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- SCHEMA: auth (si no existe)
|
|
-- =====================
|
|
CREATE SCHEMA IF NOT EXISTS auth;
|
|
|
|
-- =====================
|
|
-- TABLA: companies
|
|
-- Empresas registradas dentro de un tenant (multi-empresa)
|
|
-- Soporta jerarquia de empresas (parent_company_id)
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS auth.companies (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(255) NOT NULL,
|
|
legal_name VARCHAR(255),
|
|
tax_id VARCHAR(50),
|
|
|
|
-- Relaciones
|
|
currency_id UUID REFERENCES core.currencies(id) ON DELETE SET NULL,
|
|
parent_company_id UUID REFERENCES auth.companies(id) ON DELETE SET NULL,
|
|
partner_id UUID REFERENCES partners.partners(id) ON DELETE SET NULL,
|
|
|
|
-- Configuracion
|
|
settings JSONB DEFAULT '{}',
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- =====================
|
|
-- INDICES
|
|
-- =====================
|
|
CREATE INDEX IF NOT EXISTS idx_companies_tenant_id ON auth.companies(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_companies_parent_company_id ON auth.companies(parent_company_id);
|
|
CREATE INDEX IF NOT EXISTS idx_companies_active ON auth.companies(tenant_id) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_companies_tax_id ON auth.companies(tax_id);
|
|
CREATE INDEX IF NOT EXISTS idx_companies_currency ON auth.companies(currency_id);
|
|
CREATE INDEX IF NOT EXISTS idx_companies_partner ON auth.companies(partner_id);
|
|
|
|
-- =====================
|
|
-- RLS POLICIES
|
|
-- =====================
|
|
ALTER TABLE auth.companies ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY tenant_isolation_companies ON auth.companies
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- =====================
|
|
-- TRIGGER: updated_at automatico
|
|
-- =====================
|
|
CREATE OR REPLACE FUNCTION auth.update_companies_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at := CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_companies_updated_at
|
|
BEFORE UPDATE ON auth.companies
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION auth.update_companies_updated_at();
|
|
|
|
-- =====================
|
|
-- COMENTARIOS
|
|
-- =====================
|
|
COMMENT ON TABLE auth.companies IS 'Empresas registradas dentro de un tenant. Soporta jerarquia via parent_company_id.';
|
|
COMMENT ON COLUMN auth.companies.tenant_id IS 'Tenant al que pertenece la empresa';
|
|
COMMENT ON COLUMN auth.companies.name IS 'Nombre comercial de la empresa';
|
|
COMMENT ON COLUMN auth.companies.legal_name IS 'Razon social / nombre legal';
|
|
COMMENT ON COLUMN auth.companies.tax_id IS 'RFC o identificador fiscal';
|
|
COMMENT ON COLUMN auth.companies.currency_id IS 'Moneda principal de la empresa (ref core.currencies)';
|
|
COMMENT ON COLUMN auth.companies.parent_company_id IS 'Empresa padre para jerarquia corporativa';
|
|
COMMENT ON COLUMN auth.companies.partner_id IS 'Partner asociado a la empresa (ref partners.partners)';
|
|
COMMENT ON COLUMN auth.companies.settings IS 'Configuraciones especificas de la empresa en formato JSON';
|