michangarrito/docs/02-especificaciones/ARQUITECTURA-DATABASE.md

id	type	title	status	created_at	updated_at	simco_version	author	tags
SPEC-ARQUITECTURA-DATABASE	Specification	MiChangarrito - Arquitectura de Base de Datos	Published	2026-01-04	2026-01-10	3.8.0	Equipo MiChangarrito	database postgresql arquitectura multi-tenant rls

MiChangarrito - Arquitectura de Base de Datos

Resumen

• Motor: PostgreSQL 15
• Puerto desarrollo: 5432 (instancia compartida del workspace)
• Base de datos: michangarrito_dev
• Usuario: michangarrito_dev
• Arquitectura: Multi-tenant con Row Level Security (RLS)

---

Schemas

Schema	Proposito	Tablas Principales
public	Tenants y configuracion global	tenants, tenant_configs, tenant_integration_credentials
auth	Autenticacion y usuarios	users, sessions, otp_codes
catalog	Productos y categorias	products, categories, product_templates
sales	Ventas, pagos y CoDi/SPEI	sales, sale_items, payments, daily_closures, codi_transactions
inventory	Stock y movimientos	inventory_movements, stock_alerts
customers	Clientes y fiados	customers, fiados, fiado_payments
orders	Pedidos de clientes	orders, order_items
subscriptions	Planes, tokens y referidos	plans, subscriptions, token_usage, referral_codes
messaging	WhatsApp y notificaciones	conversations, messages, notifications
billing	Facturacion electronica SAT	tax_configs, invoices, invoice_items
marketplace	Marketplace B2B proveedores	suppliers, supplier_products, supplier_orders
integrations	Configuracion de integraciones	integration_configs, integration_logs

Total: 12 schemas, ~49 tablas

---

Schema: public

tenants

Tabla raiz multi-tenant.

CREATE TABLE public.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    business_type VARCHAR(50) NOT NULL, -- abarrotes, comida, fonda, etc.

    -- Contacto
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    zip_code VARCHAR(10),

    -- Configuracion
    timezone VARCHAR(50) DEFAULT 'America/Mexico_City',
    currency VARCHAR(3) DEFAULT 'MXN',
    tax_rate DECIMAL(5,2) DEFAULT 16.00,
    tax_included BOOLEAN DEFAULT true,

    -- WhatsApp
    whatsapp_number VARCHAR(20),
    whatsapp_verified BOOLEAN DEFAULT false,
    uses_platform_number BOOLEAN DEFAULT true,

    -- Suscripcion (referencia)
    current_plan_id UUID,
    subscription_status VARCHAR(20) DEFAULT 'trial', -- trial, active, past_due, cancelled

    -- Estado
    status VARCHAR(20) DEFAULT 'active',
    onboarding_completed BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tenants_slug ON public.tenants(slug);
CREATE INDEX idx_tenants_phone ON public.tenants(phone);
CREATE INDEX idx_tenants_status ON public.tenants(status);

tenant_configs

Configuraciones adicionales por tenant.

CREATE TABLE public.tenant_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Horarios
    opening_hour TIME DEFAULT '08:00',
    closing_hour TIME DEFAULT '22:00',
    working_days INTEGER[] DEFAULT ARRAY[1,2,3,4,5,6], -- 0=domingo

    -- Tickets
    ticket_header TEXT,
    ticket_footer TEXT DEFAULT 'Gracias por su compra',
    print_logo BOOLEAN DEFAULT false,

    -- Notificaciones
    daily_summary_enabled BOOLEAN DEFAULT true,
    daily_summary_time TIME DEFAULT '21:00',
    low_stock_alerts BOOLEAN DEFAULT true,

    -- Fiados
    fiados_enabled BOOLEAN DEFAULT true,
    default_fiado_limit DECIMAL(10,2) DEFAULT 500.00,
    fiado_reminder_days INTEGER DEFAULT 7,

    -- Pedidos
    delivery_enabled BOOLEAN DEFAULT false,
    delivery_fee DECIMAL(10,2) DEFAULT 0.00,
    delivery_radius_km DECIMAL(5,2),

    -- Metodos de pago habilitados
    payment_cash BOOLEAN DEFAULT true,
    payment_card_mercadopago BOOLEAN DEFAULT false,
    payment_card_clip BOOLEAN DEFAULT false,
    payment_codi BOOLEAN DEFAULT false,
    payment_transfer BOOLEAN DEFAULT false,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

tenant_integration_credentials

Credenciales de integraciones externas por tenant.

CREATE TABLE public.tenant_integration_credentials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Tipo de integracion
    integration_type VARCHAR(20) NOT NULL, -- whatsapp, llm, stripe, mercadopago, clip
    provider VARCHAR(50) NOT NULL, -- meta, openai, openrouter, anthropic, ollama, azure_openai

    -- Credenciales (encriptadas)
    credentials JSONB NOT NULL, -- api_key, access_token, etc.

    -- Estado
    is_active BOOLEAN DEFAULT true,
    last_validated_at TIMESTAMPTZ,
    validation_error TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, integration_type, provider)
);

CREATE INDEX idx_integration_credentials_tenant ON public.tenant_integration_credentials(tenant_id);
CREATE INDEX idx_integration_credentials_type ON public.tenant_integration_credentials(integration_type);

tenant_whatsapp_numbers

Numeros de WhatsApp configurados por tenant.

CREATE TABLE public.tenant_whatsapp_numbers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Numero
    phone_number VARCHAR(20) NOT NULL,
    phone_number_id VARCHAR(50), -- ID de Meta
    display_name VARCHAR(100),

    -- Estado
    quality_rating VARCHAR(20), -- green, yellow, red
    is_verified BOOLEAN DEFAULT false,
    is_primary BOOLEAN DEFAULT false,

    -- Configuracion
    webhook_url TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, phone_number)
);

CREATE INDEX idx_whatsapp_numbers_tenant ON public.tenant_whatsapp_numbers(tenant_id);

---

Schema: auth

users

Usuarios del sistema (duenos y empleados).

CREATE TABLE auth.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    name VARCHAR(100) NOT NULL,

    -- Autenticacion
    pin_hash VARCHAR(255), -- PIN de 4 digitos hasheado
    biometric_enabled BOOLEAN DEFAULT false,
    biometric_key TEXT,

    -- Rol
    role VARCHAR(20) NOT NULL DEFAULT 'owner', -- owner, employee, viewer
    permissions JSONB DEFAULT '{}',

    -- Estado
    status VARCHAR(20) DEFAULT 'active',
    last_login_at TIMESTAMPTZ,
    failed_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, phone)
);

CREATE INDEX idx_users_tenant ON auth.users(tenant_id);
CREATE INDEX idx_users_phone ON auth.users(phone);

sessions

Sesiones activas.

CREATE TABLE auth.sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- Token
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255),

    -- Metadata
    device_type VARCHAR(20), -- mobile, web
    device_info JSONB,
    ip_address VARCHAR(45),

    -- Expiracion
    expires_at TIMESTAMPTZ NOT NULL,
    refresh_expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_activity_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sessions_user ON auth.sessions(user_id);
CREATE INDEX idx_sessions_token ON auth.sessions(token_hash);

otp_codes

Codigos OTP para verificacion.

CREATE TABLE auth.otp_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone VARCHAR(20) NOT NULL,

    code VARCHAR(6) NOT NULL,
    purpose VARCHAR(20) NOT NULL, -- login, verify_phone, reset_pin

    attempts INTEGER DEFAULT 0,
    max_attempts INTEGER DEFAULT 3,

    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_otp_phone ON auth.otp_codes(phone, purpose);

---

Schema: catalog

categories

Categorias de productos.

CREATE TABLE catalog.categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    name VARCHAR(50) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(7), -- hex color
    sort_order INTEGER DEFAULT 0,

    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, name)
);

-- RLS
ALTER TABLE catalog.categories ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON catalog.categories
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

products

Catalogo de productos.

CREATE TABLE catalog.products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    category_id UUID REFERENCES catalog.categories(id) ON DELETE SET NULL,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sku VARCHAR(50),
    barcode VARCHAR(50),

    -- Precios
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2), -- precio de compra
    compare_price DECIMAL(10,2), -- precio anterior/tachado

    -- Inventario
    track_inventory BOOLEAN DEFAULT true,
    stock_quantity INTEGER DEFAULT 0,
    low_stock_threshold INTEGER DEFAULT 5,

    -- Presentacion
    unit VARCHAR(20) DEFAULT 'pieza', -- pieza, kg, litro, etc.

    -- Multimedia
    image_url TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'active',
    is_featured BOOLEAN DEFAULT false,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_products_tenant ON catalog.products(tenant_id);
CREATE INDEX idx_products_category ON catalog.products(category_id);
CREATE INDEX idx_products_barcode ON catalog.products(tenant_id, barcode);
CREATE INDEX idx_products_name ON catalog.products USING gin(to_tsvector('spanish', name));

-- RLS
ALTER TABLE catalog.products ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON catalog.products
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

product_templates

Templates de productos por proveedor (Bimbo, Coca-Cola, etc.).

CREATE TABLE catalog.product_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Proveedor
    provider_name VARCHAR(50) NOT NULL, -- bimbo, cocacola, sabritas, etc.
    provider_logo_url TEXT,

    -- Producto
    name VARCHAR(100) NOT NULL,
    description TEXT,
    barcode VARCHAR(50),
    suggested_price DECIMAL(10,2),
    category_suggestion VARCHAR(50),

    -- Presentaciones
    unit VARCHAR(20) DEFAULT 'pieza',

    -- Multimedia
    image_url TEXT,

    -- Metadata
    business_types TEXT[], -- ['abarrotes', 'tienda']
    popularity INTEGER DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_templates_provider ON catalog.product_templates(provider_name);
CREATE INDEX idx_templates_barcode ON catalog.product_templates(barcode);

---

Schema: sales

sales

Registro de ventas.

CREATE TABLE sales.sales (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Numeracion
    ticket_number VARCHAR(20) NOT NULL,

    -- Montos
    subtotal DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL,

    -- Pago
    payment_method VARCHAR(20) NOT NULL, -- cash, card_mercadopago, card_clip, codi, transfer, fiado
    payment_status VARCHAR(20) DEFAULT 'completed', -- pending, completed, refunded
    payment_reference TEXT, -- referencia externa del pago

    -- Efectivo
    cash_received DECIMAL(10,2),
    change_amount DECIMAL(10,2),

    -- Cliente (opcional)
    customer_id UUID REFERENCES customers.customers(id),

    -- Fiado (si aplica)
    is_fiado BOOLEAN DEFAULT false,
    fiado_id UUID,

    -- Usuario que registro
    created_by UUID REFERENCES auth.users(id),

    -- Notas
    notes TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'completed', -- completed, cancelled, refunded
    cancelled_at TIMESTAMPTZ,
    cancelled_reason TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sales_tenant ON sales.sales(tenant_id);
CREATE INDEX idx_sales_ticket ON sales.sales(tenant_id, ticket_number);
CREATE INDEX idx_sales_date ON sales.sales(tenant_id, created_at);
CREATE INDEX idx_sales_customer ON sales.sales(customer_id);

-- RLS
ALTER TABLE sales.sales ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON sales.sales
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

sale_items

Detalle de productos vendidos.

CREATE TABLE sales.sale_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sale_id UUID NOT NULL REFERENCES sales.sales(id) ON DELETE CASCADE,
    product_id UUID REFERENCES catalog.products(id),

    -- Producto (snapshot)
    product_name VARCHAR(100) NOT NULL,
    product_sku VARCHAR(50),

    -- Cantidades
    quantity DECIMAL(10,3) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,

    -- Descuento por item
    discount_amount DECIMAL(10,2) DEFAULT 0,

    -- Total
    subtotal DECIMAL(10,2) NOT NULL,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sale_items_sale ON sales.sale_items(sale_id);
CREATE INDEX idx_sale_items_product ON sales.sale_items(product_id);

payments

Registro de pagos (para pagos parciales o multiples metodos).

CREATE TABLE sales.payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),
    fiado_id UUID, -- Si es pago de fiado
    subscription_id UUID, -- Si es pago de suscripcion

    -- Metodo
    method VARCHAR(20) NOT NULL,
    provider VARCHAR(20), -- mercadopago, clip, stripe, oxxo

    -- Montos
    amount DECIMAL(10,2) NOT NULL,
    fee_amount DECIMAL(10,2) DEFAULT 0, -- comision del proveedor
    net_amount DECIMAL(10,2), -- monto neto

    -- Referencias
    external_id TEXT, -- ID del proveedor
    external_status VARCHAR(20),
    receipt_url TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, completed, failed, refunded

    -- Metadata
    metadata JSONB,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_payments_tenant ON sales.payments(tenant_id);
CREATE INDEX idx_payments_sale ON sales.payments(sale_id);
CREATE INDEX idx_payments_external ON sales.payments(external_id);

daily_closures

Cortes de caja.

CREATE TABLE sales.daily_closures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Periodo
    closure_date DATE NOT NULL,
    opened_at TIMESTAMPTZ,
    closed_at TIMESTAMPTZ,

    -- Montos esperados (calculados)
    expected_cash DECIMAL(10,2) DEFAULT 0,
    expected_card DECIMAL(10,2) DEFAULT 0,
    expected_other DECIMAL(10,2) DEFAULT 0,
    expected_total DECIMAL(10,2) DEFAULT 0,

    -- Montos reales (ingresados)
    actual_cash DECIMAL(10,2),
    actual_card DECIMAL(10,2),
    actual_other DECIMAL(10,2),
    actual_total DECIMAL(10,2),

    -- Diferencia
    cash_difference DECIMAL(10,2),

    -- Resumen
    total_sales INTEGER DEFAULT 0,
    total_cancelled INTEGER DEFAULT 0,
    total_fiados DECIMAL(10,2) DEFAULT 0,

    -- Usuario
    closed_by UUID REFERENCES auth.users(id),
    notes TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'open', -- open, closed

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, closure_date)
);

virtual_accounts

Cuentas CLABE virtuales para recibir SPEI.

CREATE TABLE sales.virtual_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- CLABE
    clabe VARCHAR(18) NOT NULL UNIQUE,
    bank_name VARCHAR(50),
    holder_name VARCHAR(100),

    -- Proveedor
    provider VARCHAR(50) NOT NULL, -- stp, arcus, openpay

    -- Estado
    is_active BOOLEAN DEFAULT true,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_virtual_accounts_tenant ON sales.virtual_accounts(tenant_id);
CREATE INDEX idx_virtual_accounts_clabe ON sales.virtual_accounts(clabe);

codi_transactions

Transacciones de pago CoDi.

CREATE TABLE sales.codi_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),

    -- Referencia
    reference VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    description VARCHAR(100),

    -- QR
    qr_data TEXT NOT NULL,
    qr_url TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, completed, expired, failed

    -- Fechas
    completed_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ NOT NULL,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_codi_transactions_tenant ON sales.codi_transactions(tenant_id);
CREATE INDEX idx_codi_transactions_reference ON sales.codi_transactions(reference);
CREATE INDEX idx_codi_transactions_status ON sales.codi_transactions(status);

spei_transactions

Transacciones SPEI recibidas.

CREATE TABLE sales.spei_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    virtual_account_id UUID REFERENCES sales.virtual_accounts(id),
    sale_id UUID REFERENCES sales.sales(id),

    -- Datos SPEI
    clave_rastreo VARCHAR(30) NOT NULL UNIQUE,
    sender_clabe VARCHAR(18),
    sender_name VARCHAR(100),
    sender_rfc VARCHAR(13),
    amount DECIMAL(10,2) NOT NULL,
    concept VARCHAR(100),

    -- Estado
    status VARCHAR(20) DEFAULT 'received', -- received, processed, returned

    -- Fechas
    received_at TIMESTAMPTZ NOT NULL,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_spei_transactions_tenant ON sales.spei_transactions(tenant_id);
CREATE INDEX idx_spei_transactions_clave ON sales.spei_transactions(clave_rastreo);

payment_config

Configuracion de pagos CoDi/SPEI por tenant.

CREATE TABLE sales.payment_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Habilitacion
    codi_enabled BOOLEAN DEFAULT false,
    spei_enabled BOOLEAN DEFAULT false,

    -- Proveedor
    provider VARCHAR(50), -- stp, arcus, openpay
    credentials JSONB, -- Credenciales encriptadas

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

---

Schema: inventory

inventory_movements

Movimientos de inventario.

CREATE TABLE inventory.inventory_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES catalog.products(id) ON DELETE CASCADE,

    -- Tipo
    movement_type VARCHAR(20) NOT NULL, -- purchase, sale, adjustment, loss, return

    -- Cantidades
    quantity DECIMAL(10,3) NOT NULL, -- positivo o negativo
    previous_stock DECIMAL(10,3) NOT NULL,
    new_stock DECIMAL(10,3) NOT NULL,

    -- Costo (para compras)
    unit_cost DECIMAL(10,2),
    total_cost DECIMAL(10,2),

    -- Referencia
    reference_type VARCHAR(20), -- sale, purchase_order, manual
    reference_id UUID,

    -- Notas
    notes TEXT,

    -- Usuario
    created_by UUID REFERENCES auth.users(id),

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_inventory_tenant ON inventory.inventory_movements(tenant_id);
CREATE INDEX idx_inventory_product ON inventory.inventory_movements(product_id);
CREATE INDEX idx_inventory_date ON inventory.inventory_movements(created_at);

stock_alerts

Alertas de stock bajo.

CREATE TABLE inventory.stock_alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES catalog.products(id) ON DELETE CASCADE,

    -- Niveles
    current_stock INTEGER NOT NULL,
    threshold INTEGER NOT NULL,

    -- Estado
    status VARCHAR(20) DEFAULT 'active', -- active, resolved, ignored

    -- Notificacion
    notified_at TIMESTAMPTZ,
    resolved_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

---

Schema: customers

customers

Clientes del negocio.

CREATE TABLE customers.customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),

    -- Direccion (para entregas)
    address TEXT,
    address_reference TEXT,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),

    -- Fiados
    fiado_enabled BOOLEAN DEFAULT true,
    fiado_limit DECIMAL(10,2),
    current_fiado_balance DECIMAL(10,2) DEFAULT 0,

    -- Estadisticas
    total_purchases DECIMAL(12,2) DEFAULT 0,
    purchase_count INTEGER DEFAULT 0,
    last_purchase_at TIMESTAMPTZ,

    -- WhatsApp
    whatsapp_opt_in BOOLEAN DEFAULT false,

    -- Notas
    notes TEXT,

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

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_customers_tenant ON customers.customers(tenant_id);
CREATE INDEX idx_customers_phone ON customers.customers(tenant_id, phone);
CREATE INDEX idx_customers_name ON customers.customers USING gin(to_tsvector('spanish', name));

-- RLS
ALTER TABLE customers.customers ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON customers.customers
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

fiados

Registro de fiados (creditos a clientes).

CREATE TABLE customers.fiados (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    customer_id UUID NOT NULL REFERENCES customers.customers(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),

    -- Monto
    original_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0,
    remaining_amount DECIMAL(10,2) NOT NULL,

    -- Fechas
    due_date DATE,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, partial, paid, overdue, cancelled

    -- Notas
    description TEXT,

    -- Recordatorios
    last_reminder_at TIMESTAMPTZ,
    reminder_count INTEGER DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_fiados_tenant ON customers.fiados(tenant_id);
CREATE INDEX idx_fiados_customer ON customers.fiados(customer_id);
CREATE INDEX idx_fiados_status ON customers.fiados(status);

fiado_payments

Pagos de fiados.

CREATE TABLE customers.fiado_payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    fiado_id UUID NOT NULL REFERENCES customers.fiados(id) ON DELETE CASCADE,

    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL,

    notes TEXT,

    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

---

Schema: orders

orders

Pedidos de clientes (via WhatsApp u otros).

CREATE TABLE orders.orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    customer_id UUID REFERENCES customers.customers(id),

    -- Numeracion
    order_number VARCHAR(20) NOT NULL,

    -- Canal
    channel VARCHAR(20) NOT NULL, -- whatsapp, app, web

    -- Montos
    subtotal DECIMAL(10,2) NOT NULL,
    delivery_fee DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL,

    -- Tipo
    order_type VARCHAR(20) NOT NULL, -- pickup, delivery

    -- Entrega
    delivery_address TEXT,
    delivery_notes TEXT,
    estimated_delivery_at TIMESTAMPTZ,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending',
    -- pending, confirmed, preparing, ready, delivering, completed, cancelled

    -- Pago
    payment_status VARCHAR(20) DEFAULT 'pending', -- pending, paid, refunded
    payment_method VARCHAR(20),

    -- Timestamps
    confirmed_at TIMESTAMPTZ,
    preparing_at TIMESTAMPTZ,
    ready_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    cancelled_reason TEXT,

    -- Notas
    customer_notes TEXT,
    internal_notes TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orders_tenant ON orders.orders(tenant_id);
CREATE INDEX idx_orders_customer ON orders.orders(customer_id);
CREATE INDEX idx_orders_status ON orders.orders(status);
CREATE INDEX idx_orders_date ON orders.orders(created_at);

order_items

Items del pedido.

CREATE TABLE orders.order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders.orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES catalog.products(id),

    -- Producto (snapshot)
    product_name VARCHAR(100) NOT NULL,

    -- Cantidades
    quantity DECIMAL(10,3) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,

    -- Notas especiales
    notes TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

---

Schema: subscriptions

plans

Planes de suscripcion.

CREATE TABLE subscriptions.plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificacion
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL, -- changarrito, tiendita
    description TEXT,

    -- Precio
    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'MXN',

    -- Incluido
    included_tokens INTEGER NOT NULL, -- tokens IA incluidos
    features JSONB, -- lista de features

    -- Limites
    max_products INTEGER,
    max_users INTEGER DEFAULT 1,
    whatsapp_own_number BOOLEAN DEFAULT false,

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

    -- Stripe
    stripe_price_id_monthly VARCHAR(100),
    stripe_price_id_yearly VARCHAR(100),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insertar planes iniciales
INSERT INTO subscriptions.plans (name, code, price_monthly, included_tokens, max_products, features) VALUES
('Changarrito', 'changarrito', 99.00, 500, 100, '{"pos": true, "inventory": true, "reports_basic": true}'),
('Tiendita', 'tiendita', 199.00, 2000, null, '{"pos": true, "inventory": true, "reports_advanced": true, "whatsapp_own": true, "customers": true, "fiados": true}');

subscriptions

Suscripciones activas.

CREATE TABLE subscriptions.subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    plan_id UUID NOT NULL REFERENCES subscriptions.plans(id),

    -- Periodo
    billing_cycle VARCHAR(10) DEFAULT 'monthly', -- monthly, yearly
    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end TIMESTAMPTZ NOT NULL,

    -- Estado
    status VARCHAR(20) DEFAULT 'active', -- trialing, active, past_due, cancelled, paused
    cancel_at_period_end BOOLEAN DEFAULT false,
    cancelled_at TIMESTAMPTZ,

    -- Pagos
    payment_method VARCHAR(20), -- card, oxxo, iap_ios, iap_android

    -- Stripe
    stripe_subscription_id VARCHAR(100),
    stripe_customer_id VARCHAR(100),

    -- Trial
    trial_ends_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_tenant ON subscriptions.subscriptions(tenant_id);
CREATE INDEX idx_subscriptions_stripe ON subscriptions.subscriptions(stripe_subscription_id);

token_packages

Paquetes de tokens para compra.

CREATE TABLE subscriptions.token_packages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    name VARCHAR(50) NOT NULL,
    tokens INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'MXN',

    -- Bonus
    bonus_tokens INTEGER DEFAULT 0,

    -- Stripe
    stripe_price_id VARCHAR(100),

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

    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insertar paquetes
INSERT INTO subscriptions.token_packages (name, tokens, price) VALUES
('Recarga Basica', 1000, 29.00),
('Recarga Plus', 3000, 69.00),
('Recarga Pro', 8000, 149.00),
('Recarga Mega', 20000, 299.00);

token_usage

Consumo de tokens.

CREATE TABLE subscriptions.token_usage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Tokens
    tokens_used INTEGER NOT NULL,

    -- Contexto
    action VARCHAR(50) NOT NULL, -- chat, report, ocr, transcription
    description TEXT,

    -- LLM info
    model VARCHAR(50),
    input_tokens INTEGER,
    output_tokens INTEGER,

    -- Referencia
    reference_type VARCHAR(20),
    reference_id UUID,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_token_usage_tenant ON subscriptions.token_usage(tenant_id);
CREATE INDEX idx_token_usage_date ON subscriptions.token_usage(created_at);

tenant_token_balance

Balance de tokens por tenant.

CREATE TABLE subscriptions.tenant_token_balance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Balance
    available_tokens INTEGER DEFAULT 0,
    used_tokens INTEGER DEFAULT 0,

    -- Ultimo reset (mensual)
    last_reset_at TIMESTAMPTZ,

    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

referral_codes

Codigos de referido por tenant.

CREATE TABLE subscriptions.referral_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Codigo
    code VARCHAR(10) NOT NULL UNIQUE,

    -- Beneficios
    discount_percent INTEGER DEFAULT 10, -- % descuento para referido
    reward_months INTEGER DEFAULT 1, -- Meses gratis para referidor

    -- Limites
    max_uses INTEGER, -- null = sin limite
    current_uses INTEGER DEFAULT 0,

    -- Estado
    is_active BOOLEAN DEFAULT true,
    expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_referral_codes_tenant ON subscriptions.referral_codes(tenant_id);
CREATE INDEX idx_referral_codes_code ON subscriptions.referral_codes(code);

referrals

Registro de referidos.

CREATE TABLE subscriptions.referrals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Relacion
    referrer_tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    referred_tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    code_id UUID NOT NULL REFERENCES subscriptions.referral_codes(id),

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, active, expired, rewarded

    -- Fechas
    referred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    activated_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(referred_tenant_id) -- Un tenant solo puede ser referido una vez
);

CREATE INDEX idx_referrals_referrer ON subscriptions.referrals(referrer_tenant_id);
CREATE INDEX idx_referrals_status ON subscriptions.referrals(status);

referral_rewards

Recompensas otorgadas por referidos.

CREATE TABLE subscriptions.referral_rewards (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    referral_id UUID NOT NULL REFERENCES subscriptions.referrals(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Tipo de recompensa
    reward_type VARCHAR(20) NOT NULL, -- free_month, discount

    -- Valor
    value DECIMAL(10,2) NOT NULL, -- Meses o porcentaje

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, applied, expired

    -- Fechas
    applied_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_referral_rewards_tenant ON subscriptions.referral_rewards(tenant_id);
CREATE INDEX idx_referral_rewards_status ON subscriptions.referral_rewards(status);

---

Schema: messaging

conversations

Conversaciones de WhatsApp.

CREATE TABLE messaging.conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES public.tenants(id), -- null si es plataforma

    -- Participante
    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(100),

    -- Tipo
    conversation_type VARCHAR(20) NOT NULL, -- owner, customer, support, onboarding

    -- Estado
    status VARCHAR(20) DEFAULT 'active', -- active, archived, blocked

    -- Ultimo mensaje
    last_message_at TIMESTAMPTZ,
    last_message_preview TEXT,
    unread_count INTEGER DEFAULT 0,

    -- WhatsApp
    wa_conversation_id VARCHAR(100),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_conversations_tenant ON messaging.conversations(tenant_id);
CREATE INDEX idx_conversations_phone ON messaging.conversations(phone_number);

messages

Mensajes individuales.

CREATE TABLE messaging.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES messaging.conversations(id) ON DELETE CASCADE,

    -- Direccion
    direction VARCHAR(10) NOT NULL, -- inbound, outbound

    -- Contenido
    message_type VARCHAR(20) NOT NULL, -- text, image, audio, video, document, location
    content TEXT,
    media_url TEXT,
    media_mime_type VARCHAR(50),

    -- LLM (si fue procesado)
    processed_by_llm BOOLEAN DEFAULT false,
    llm_response_id UUID,
    tokens_used INTEGER,

    -- WhatsApp
    wa_message_id VARCHAR(100),
    wa_status VARCHAR(20), -- sent, delivered, read, failed
    wa_timestamp TIMESTAMPTZ,

    -- Error
    error_code VARCHAR(20),
    error_message TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation ON messaging.messages(conversation_id);
CREATE INDEX idx_messages_wa ON messaging.messages(wa_message_id);

notifications

Notificaciones push y WhatsApp.

CREATE TABLE messaging.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(id),

    -- Tipo
    notification_type VARCHAR(50) NOT NULL, -- low_stock, new_order, fiado_reminder, daily_summary

    -- Canales
    channels TEXT[] NOT NULL, -- ['push', 'whatsapp']

    -- Contenido
    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    data JSONB,

    -- Estado por canal
    push_sent BOOLEAN DEFAULT false,
    push_sent_at TIMESTAMPTZ,
    whatsapp_sent BOOLEAN DEFAULT false,
    whatsapp_sent_at TIMESTAMPTZ,

    -- Lectura
    read_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_notifications_tenant ON messaging.notifications(tenant_id);
CREATE INDEX idx_notifications_user ON messaging.notifications(user_id);

---

Schema: billing

tax_configs

Configuracion fiscal por tenant (RFC, CSD, PAC).

CREATE TABLE billing.tax_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Datos fiscales
    rfc VARCHAR(13) NOT NULL,
    razon_social VARCHAR(200) NOT NULL,
    regimen_fiscal VARCHAR(10) NOT NULL, -- Codigo SAT
    codigo_postal VARCHAR(5) NOT NULL,

    -- Certificado de Sello Digital (CSD)
    csd_cer TEXT, -- Certificado en base64
    csd_key TEXT, -- Llave privada encriptada
    csd_password TEXT, -- Password encriptado

    -- PAC
    pac_provider VARCHAR(50), -- facturapi, sw_sapien, etc
    pac_credentials JSONB, -- Credenciales del PAC

    -- Estado
    is_active BOOLEAN DEFAULT true,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id)
);

invoices

Facturas CFDI emitidas.

CREATE TABLE billing.invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    sale_id UUID REFERENCES sales.sales(id),

    -- Folio
    folio VARCHAR(20) NOT NULL,
    serie VARCHAR(10),
    uuid_fiscal VARCHAR(36), -- UUID del SAT

    -- Tipo
    tipo_comprobante VARCHAR(1) NOT NULL DEFAULT 'I', -- I=Ingreso, E=Egreso, T=Traslado

    -- Receptor
    receptor_rfc VARCHAR(13) NOT NULL,
    receptor_nombre VARCHAR(200) NOT NULL,
    receptor_regimen VARCHAR(10),
    receptor_uso_cfdi VARCHAR(10) NOT NULL, -- G03, etc

    -- Montos
    subtotal DECIMAL(12,2) NOT NULL,
    total_impuestos DECIMAL(12,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,

    -- Pago
    moneda VARCHAR(3) DEFAULT 'MXN',
    tipo_cambio DECIMAL(10,4) DEFAULT 1,
    forma_pago VARCHAR(2) NOT NULL, -- 01=Efectivo, 04=Tarjeta, 03=Transferencia
    metodo_pago VARCHAR(3) NOT NULL, -- PUE o PPD

    -- XML
    xml_content TEXT,
    pdf_url TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'draft', -- draft, stamped, cancelled, sent

    -- Cancelacion
    cancelled_at TIMESTAMPTZ,
    cancellation_reason VARCHAR(2),
    cancellation_uuid VARCHAR(36),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, serie, folio)
);

CREATE INDEX idx_invoices_tenant ON billing.invoices(tenant_id);
CREATE INDEX idx_invoices_uuid ON billing.invoices(uuid_fiscal);
CREATE INDEX idx_invoices_status ON billing.invoices(status);

invoice_items

Items de factura.

CREATE TABLE billing.invoice_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES billing.invoices(id) ON DELETE CASCADE,

    -- Producto SAT
    clave_prod_serv VARCHAR(8) NOT NULL, -- Clave SAT
    clave_unidad VARCHAR(3) NOT NULL, -- H87=Pieza, KGM=Kg, etc
    descripcion VARCHAR(500) NOT NULL,

    -- Cantidades
    cantidad DECIMAL(10,3) NOT NULL,
    valor_unitario DECIMAL(12,4) NOT NULL,
    importe DECIMAL(12,2) NOT NULL,

    -- Descuento
    descuento DECIMAL(12,2) DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoice_items_invoice ON billing.invoice_items(invoice_id);

invoice_item_taxes

Impuestos por item de factura.

CREATE TABLE billing.invoice_item_taxes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_item_id UUID NOT NULL REFERENCES billing.invoice_items(id) ON DELETE CASCADE,

    -- Tipo
    tipo VARCHAR(10) NOT NULL, -- traslado o retencion
    impuesto VARCHAR(3) NOT NULL, -- 002=IVA, 003=IEPS
    tipo_factor VARCHAR(10) NOT NULL, -- Tasa, Cuota, Exento

    -- Valores
    tasa_o_cuota DECIMAL(6,4) NOT NULL, -- 0.16 para IVA 16%
    importe DECIMAL(12,2) NOT NULL,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

invoice_history

Historial de cambios en facturas (auditoria).

CREATE TABLE billing.invoice_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES billing.invoices(id) ON DELETE CASCADE,

    -- Accion
    action VARCHAR(20) NOT NULL, -- created, stamped, cancelled, sent

    -- Detalles
    details JSONB,

    -- Usuario
    performed_by UUID REFERENCES auth.users(id),

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoice_history_invoice ON billing.invoice_history(invoice_id);

---

Schema: marketplace

suppliers

Proveedores/distribuidores del marketplace B2B.

CREATE TABLE marketplace.suppliers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificacion
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    logo_url TEXT,

    -- Contacto
    contact_phone VARCHAR(20),
    contact_email VARCHAR(100),
    contact_name VARCHAR(100),

    -- Direccion
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    zip_codes_served TEXT[], -- CPs que atiende

    -- Condiciones
    min_order_amount DECIMAL(10,2) DEFAULT 0,
    delivery_days INTEGER DEFAULT 3,
    delivery_fee DECIMAL(10,2) DEFAULT 0,

    -- Metricas
    rating DECIMAL(3,2) DEFAULT 0,
    total_orders INTEGER DEFAULT 0,
    total_reviews INTEGER DEFAULT 0,

    -- Estado
    is_verified BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_suppliers_slug ON marketplace.suppliers(slug);
CREATE INDEX idx_suppliers_active ON marketplace.suppliers(is_active);
CREATE INDEX idx_suppliers_zip ON marketplace.suppliers USING gin(zip_codes_served);

supplier_products

Catalogo de productos de proveedores.

CREATE TABLE marketplace.supplier_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id) ON DELETE CASCADE,

    -- Producto
    name VARCHAR(200) NOT NULL,
    description TEXT,
    sku VARCHAR(50),
    barcode VARCHAR(50),

    -- Precios
    price DECIMAL(10,2) NOT NULL, -- Precio mayoreo
    suggested_retail_price DECIMAL(10,2), -- Precio sugerido

    -- Presentacion
    unit VARCHAR(20) DEFAULT 'pieza',
    min_quantity INTEGER DEFAULT 1,

    -- Categoria
    category VARCHAR(50),

    -- Imagen
    image_url TEXT,

    -- Estado
    is_available BOOLEAN DEFAULT true,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_supplier_products_supplier ON marketplace.supplier_products(supplier_id);
CREATE INDEX idx_supplier_products_barcode ON marketplace.supplier_products(barcode);
CREATE INDEX idx_supplier_products_available ON marketplace.supplier_products(is_available);

supplier_orders

Pedidos a proveedores.

CREATE TABLE marketplace.supplier_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id),

    -- Numero
    order_number VARCHAR(20) NOT NULL,

    -- Montos
    subtotal DECIMAL(12,2) NOT NULL,
    delivery_fee DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, confirmed, shipped, delivered, cancelled

    -- Entrega
    shipping_address TEXT NOT NULL,
    shipping_notes TEXT,
    estimated_delivery DATE,
    delivered_at TIMESTAMPTZ,

    -- Notas
    notes TEXT,

    -- Cancelacion
    cancelled_at TIMESTAMPTZ,
    cancellation_reason TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_supplier_orders_tenant ON marketplace.supplier_orders(tenant_id);
CREATE INDEX idx_supplier_orders_supplier ON marketplace.supplier_orders(supplier_id);
CREATE INDEX idx_supplier_orders_status ON marketplace.supplier_orders(status);

supplier_order_items

Items de pedidos a proveedores.

CREATE TABLE marketplace.supplier_order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES marketplace.supplier_orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES marketplace.supplier_products(id),

    -- Producto (snapshot)
    product_name VARCHAR(200) NOT NULL,
    product_sku VARCHAR(50),

    -- Cantidades
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_supplier_order_items_order ON marketplace.supplier_order_items(order_id);

supplier_reviews

Resenas de proveedores.

CREATE TABLE marketplace.supplier_reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id) ON DELETE CASCADE,
    order_id UUID REFERENCES marketplace.supplier_orders(id),

    -- Calificacion
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, order_id) -- Una resena por orden
);

CREATE INDEX idx_supplier_reviews_supplier ON marketplace.supplier_reviews(supplier_id);

supplier_favorites

Proveedores favoritos por tenant.

CREATE TABLE marketplace.supplier_favorites (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id) ON DELETE CASCADE,

    created_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, supplier_id)
);

CREATE INDEX idx_supplier_favorites_tenant ON marketplace.supplier_favorites(tenant_id);

---

Schema: integrations

integration_configs

Configuraciones de integraciones por tenant.

CREATE TABLE integrations.integration_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Tipo de integracion
    integration_type VARCHAR(50) NOT NULL, -- llm, payments, whatsapp, sat, etc.
    provider VARCHAR(50) NOT NULL, -- openrouter, stripe, meta, etc.

    -- Configuracion
    config JSONB NOT NULL DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    priority INTEGER DEFAULT 0, -- para fallback ordering

    -- Limites y uso
    rate_limit_per_minute INTEGER,
    daily_limit INTEGER,
    monthly_limit INTEGER,
    current_daily_usage INTEGER DEFAULT 0,
    current_monthly_usage INTEGER DEFAULT 0,

    -- Timestamps
    last_used_at TIMESTAMPTZ,
    last_reset_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, integration_type, provider)
);

CREATE INDEX idx_integration_configs_tenant ON integrations.integration_configs(tenant_id);
CREATE INDEX idx_integration_configs_type ON integrations.integration_configs(integration_type);

integration_logs

Logs de uso de integraciones para auditoria.

CREATE TABLE integrations.integration_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    -- Referencia a config
    integration_config_id UUID REFERENCES integrations.integration_configs(id),
    integration_type VARCHAR(50) NOT NULL,
    provider VARCHAR(50) NOT NULL,

    -- Detalles de la llamada
    request_type VARCHAR(100), -- chat_completion, send_message, create_invoice, etc.
    request_data JSONB,
    response_data JSONB,

    -- Resultado
    status VARCHAR(20) NOT NULL, -- success, error, timeout, rate_limited
    error_message TEXT,

    -- Metricas
    latency_ms INTEGER,
    tokens_used INTEGER, -- para LLM
    cost_cents INTEGER, -- costo estimado

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_integration_logs_tenant ON integrations.integration_logs(tenant_id);
CREATE INDEX idx_integration_logs_type ON integrations.integration_logs(integration_type);
CREATE INDEX idx_integration_logs_created ON integrations.integration_logs(created_at);

---

Funciones Utiles

Generador de numeros de ticket

CREATE OR REPLACE FUNCTION sales.generate_ticket_number(p_tenant_id UUID)
RETURNS VARCHAR(20) AS $$
DECLARE
    v_date TEXT;
    v_sequence INTEGER;
    v_ticket VARCHAR(20);
BEGIN
    v_date := TO_CHAR(CURRENT_DATE, 'YYMMDD');

    SELECT COALESCE(MAX(
        CAST(SUBSTRING(ticket_number FROM 8) AS INTEGER)
    ), 0) + 1
    INTO v_sequence
    FROM sales.sales
    WHERE tenant_id = p_tenant_id
      AND ticket_number LIKE v_date || '-%';

    v_ticket := v_date || '-' || LPAD(v_sequence::TEXT, 4, '0');

    RETURN v_ticket;
END;
$$ LANGUAGE plpgsql;

Trigger de actualizacion

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Aplicar a todas las tablas relevantes
CREATE TRIGGER update_tenants_updated_at
    BEFORE UPDATE ON public.tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- (repetir para otras tablas)

Funcion de balance de fiados

CREATE OR REPLACE FUNCTION customers.update_customer_fiado_balance()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE customers.customers
    SET current_fiado_balance = (
        SELECT COALESCE(SUM(remaining_amount), 0)
        FROM customers.fiados
        WHERE customer_id = COALESCE(NEW.customer_id, OLD.customer_id)
          AND status IN ('pending', 'partial', 'overdue')
    )
    WHERE id = COALESCE(NEW.customer_id, OLD.customer_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_fiado_balance
    AFTER INSERT OR UPDATE OR DELETE ON customers.fiados
    FOR EACH ROW EXECUTE FUNCTION customers.update_customer_fiado_balance();

Funciones de Sistema de Referidos

-- Generador de codigo de referido unico
CREATE OR REPLACE FUNCTION subscriptions.generate_referral_code(p_tenant_id UUID)
RETURNS VARCHAR(10) AS $$
DECLARE
    v_code VARCHAR(10);
    v_exists BOOLEAN;
BEGIN
    LOOP
        -- Genera codigo alfanumerico de 8 caracteres
        v_code := UPPER(SUBSTRING(MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT) FROM 1 FOR 8));

        -- Verifica que no exista
        SELECT EXISTS(
            SELECT 1 FROM subscriptions.referral_codes
            WHERE code = v_code
        ) INTO v_exists;

        EXIT WHEN NOT v_exists;
    END LOOP;

    RETURN v_code;
END;
$$ LANGUAGE plpgsql;

-- Estadisticas de referidos por tenant
CREATE OR REPLACE FUNCTION subscriptions.get_referral_stats(p_tenant_id UUID)
RETURNS TABLE(
    total_codes INTEGER,
    active_codes INTEGER,
    total_referrals INTEGER,
    successful_referrals INTEGER,
    total_rewards_pending DECIMAL,
    total_rewards_paid DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(DISTINCT rc.id)::INTEGER as total_codes,
        COUNT(DISTINCT rc.id) FILTER (WHERE rc.is_active)::INTEGER as active_codes,
        COUNT(DISTINCT r.id)::INTEGER as total_referrals,
        COUNT(DISTINCT r.id) FILTER (WHERE r.status = 'converted')::INTEGER as successful_referrals,
        COALESCE(SUM(rr.reward_amount) FILTER (WHERE rr.status = 'pending'), 0) as total_rewards_pending,
        COALESCE(SUM(rr.reward_amount) FILTER (WHERE rr.status = 'paid'), 0) as total_rewards_paid
    FROM subscriptions.referral_codes rc
    LEFT JOIN subscriptions.referrals r ON r.referral_code_id = rc.id
    LEFT JOIN subscriptions.referral_rewards rr ON rr.referral_id = r.id
    WHERE rc.tenant_id = p_tenant_id;
END;
$$ LANGUAGE plpgsql;

Funciones de CoDi/SPEI

-- Generador de referencia CoDi
CREATE OR REPLACE FUNCTION sales.generate_codi_reference(p_tenant_id UUID)
RETURNS VARCHAR(20) AS $$
DECLARE
    v_date TEXT;
    v_sequence INTEGER;
    v_reference VARCHAR(20);
BEGIN
    v_date := TO_CHAR(NOW(), 'YYYYMMDD');

    SELECT COALESCE(MAX(
        CAST(SUBSTRING(codi_reference FROM 10) AS INTEGER)
    ), 0) + 1
    INTO v_sequence
    FROM sales.codi_transactions
    WHERE tenant_id = p_tenant_id
      AND codi_reference LIKE 'CODI' || v_date || '%';

    v_reference := 'CODI' || v_date || LPAD(v_sequence::TEXT, 6, '0');

    RETURN v_reference;
END;
$$ LANGUAGE plpgsql;

-- Resumen de transacciones CoDi/SPEI
CREATE OR REPLACE FUNCTION sales.get_codi_spei_summary(
    p_tenant_id UUID,
    p_start_date TIMESTAMPTZ DEFAULT NOW() - INTERVAL '30 days',
    p_end_date TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    total_codi_transactions INTEGER,
    total_codi_amount DECIMAL,
    total_spei_transactions INTEGER,
    total_spei_amount DECIMAL,
    pending_transactions INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(ct.id)::INTEGER as total_codi_transactions,
        COALESCE(SUM(ct.amount) FILTER (WHERE ct.status = 'completed'), 0) as total_codi_amount,
        COUNT(st.id)::INTEGER as total_spei_transactions,
        COALESCE(SUM(st.amount) FILTER (WHERE st.status = 'completed'), 0) as total_spei_amount,
        (COUNT(ct.id) FILTER (WHERE ct.status = 'pending') +
         COUNT(st.id) FILTER (WHERE st.status = 'pending'))::INTEGER as pending_transactions
    FROM sales.codi_transactions ct
    FULL OUTER JOIN sales.spei_transactions st ON st.tenant_id = ct.tenant_id
    WHERE (ct.tenant_id = p_tenant_id OR st.tenant_id = p_tenant_id)
      AND (ct.created_at BETWEEN p_start_date AND p_end_date
           OR st.created_at BETWEEN p_start_date AND p_end_date);
END;
$$ LANGUAGE plpgsql;

Funciones de Facturacion (CFDI 4.0)

-- Obtener siguiente folio de factura
CREATE OR REPLACE FUNCTION billing.get_next_invoice_folio(
    p_tenant_id UUID,
    p_serie VARCHAR DEFAULT 'A'
)
RETURNS VARCHAR(20) AS $$
DECLARE
    v_sequence INTEGER;
    v_folio VARCHAR(20);
BEGIN
    SELECT COALESCE(MAX(
        CAST(SUBSTRING(folio FROM 2) AS INTEGER)
    ), 0) + 1
    INTO v_sequence
    FROM billing.invoices
    WHERE tenant_id = p_tenant_id
      AND serie = p_serie;

    v_folio := p_serie || LPAD(v_sequence::TEXT, 8, '0');

    RETURN v_folio;
END;
$$ LANGUAGE plpgsql;

-- Resumen de facturacion por periodo
CREATE OR REPLACE FUNCTION billing.get_invoice_summary(
    p_tenant_id UUID,
    p_start_date TIMESTAMPTZ DEFAULT DATE_TRUNC('month', NOW()),
    p_end_date TIMESTAMPTZ DEFAULT NOW()
)
RETURNS TABLE(
    total_invoices INTEGER,
    total_subtotal DECIMAL,
    total_iva DECIMAL,
    total_amount DECIMAL,
    cancelled_invoices INTEGER,
    pending_timbrado INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::INTEGER as total_invoices,
        COALESCE(SUM(subtotal), 0) as total_subtotal,
        COALESCE(SUM(total_tax), 0) as total_iva,
        COALESCE(SUM(total_amount), 0) as total_amount,
        COUNT(*) FILTER (WHERE status = 'cancelled')::INTEGER as cancelled_invoices,
        COUNT(*) FILTER (WHERE status = 'pending')::INTEGER as pending_timbrado
    FROM billing.invoices
    WHERE tenant_id = p_tenant_id
      AND created_at BETWEEN p_start_date AND p_end_date;
END;
$$ LANGUAGE plpgsql;

Funciones de Marketplace

-- Actualizar rating de proveedor
CREATE OR REPLACE FUNCTION marketplace.update_supplier_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE marketplace.suppliers
    SET
        rating = (
            SELECT COALESCE(AVG(sr.rating), 0)
            FROM marketplace.supplier_reviews sr
            WHERE sr.supplier_id = COALESCE(NEW.supplier_id, OLD.supplier_id)
        ),
        reviews_count = (
            SELECT COUNT(*)
            FROM marketplace.supplier_reviews sr
            WHERE sr.supplier_id = COALESCE(NEW.supplier_id, OLD.supplier_id)
        )
    WHERE id = COALESCE(NEW.supplier_id, OLD.supplier_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_supplier_rating
    AFTER INSERT OR UPDATE OR DELETE ON marketplace.supplier_reviews
    FOR EACH ROW EXECUTE FUNCTION marketplace.update_supplier_rating();

-- Actualizar contador de ordenes de proveedor
CREATE OR REPLACE FUNCTION marketplace.update_supplier_orders_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE marketplace.suppliers
    SET orders_count = (
        SELECT COUNT(*)
        FROM marketplace.supplier_orders so
        WHERE so.supplier_id = COALESCE(NEW.supplier_id, OLD.supplier_id)
          AND so.status = 'completed'
    )
    WHERE id = COALESCE(NEW.supplier_id, OLD.supplier_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_supplier_orders_count
    AFTER INSERT OR UPDATE ON marketplace.supplier_orders
    FOR EACH ROW EXECUTE FUNCTION marketplace.update_supplier_orders_count();

-- Buscar proveedores por zona geografica
CREATE OR REPLACE FUNCTION marketplace.find_suppliers_by_zone(
    p_state VARCHAR,
    p_city VARCHAR DEFAULT NULL,
    p_category VARCHAR DEFAULT NULL
)
RETURNS TABLE(
    supplier_id UUID,
    business_name VARCHAR,
    category VARCHAR,
    rating DECIMAL,
    delivery_days INTEGER,
    minimum_order DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.id as supplier_id,
        s.business_name,
        s.category,
        s.rating,
        s.delivery_days,
        s.minimum_order
    FROM marketplace.suppliers s
    WHERE s.is_active = TRUE
      AND s.is_verified = TRUE
      AND (p_state = ANY(s.delivery_zones) OR 'Nacional' = ANY(s.delivery_zones))
      AND (p_category IS NULL OR s.category = p_category)
    ORDER BY s.rating DESC, s.orders_count DESC;
END;
$$ LANGUAGE plpgsql;

-- Estadisticas generales del marketplace
CREATE OR REPLACE FUNCTION marketplace.get_marketplace_stats()
RETURNS TABLE(
    total_suppliers INTEGER,
    verified_suppliers INTEGER,
    total_products INTEGER,
    total_orders INTEGER,
    total_volume DECIMAL,
    avg_rating DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        (SELECT COUNT(*)::INTEGER FROM marketplace.suppliers WHERE is_active),
        (SELECT COUNT(*)::INTEGER FROM marketplace.suppliers WHERE is_verified),
        (SELECT COUNT(*)::INTEGER FROM marketplace.supplier_products WHERE is_active),
        (SELECT COUNT(*)::INTEGER FROM marketplace.supplier_orders),
        (SELECT COALESCE(SUM(total_amount), 0) FROM marketplace.supplier_orders WHERE status = 'completed'),
        (SELECT COALESCE(AVG(rating), 0) FROM marketplace.suppliers WHERE is_verified);
END;
$$ LANGUAGE plpgsql;

---

Indices Adicionales para Performance

-- Ventas por fecha (reportes)
CREATE INDEX idx_sales_tenant_date ON sales.sales(tenant_id, DATE(created_at));

-- Productos mas vendidos
CREATE INDEX idx_sale_items_product_count ON sales.sale_items(product_id);

-- Fiados vencidos
CREATE INDEX idx_fiados_overdue ON customers.fiados(tenant_id, due_date)
    WHERE status IN ('pending', 'partial');

-- Busqueda de productos por nombre
CREATE INDEX idx_products_search ON catalog.products
    USING gin(to_tsvector('spanish', name || ' ' || COALESCE(description, '')));

---

Row Level Security (RLS)

Todas las tablas que manejan datos de tenant tienen RLS habilitado.

-- Configurar tenant en cada request
SET app.current_tenant = 'uuid-del-tenant';

-- Ejemplo de policy
CREATE POLICY tenant_isolation ON catalog.products
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant')::UUID)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);

---

Extensiones PostgreSQL

Extension	Proposito
uuid-ossp	Generacion de UUIDs
pgcrypto	Encriptacion de datos sensibles
pg_trgm	Busqueda por similitud (fuzzy search)
btree_gin	Indices GIN para busquedas combinadas

---

Convenciones

Columnas Comunes

Todas las tablas siguen estas convenciones:

Columna	Tipo	Descripcion
id	UUID PRIMARY KEY	Identificador unico
tenant_id	UUID NOT NULL	Referencia al tenant (multi-tenant)
created_at	TIMESTAMPTZ DEFAULT NOW()	Fecha de creacion
updated_at	TIMESTAMPTZ DEFAULT NOW()	Fecha de ultima modificacion
is_active	BOOLEAN DEFAULT TRUE	Estado activo/inactivo

Indices Estandar

• PK en id
• Indice en tenant_id para todas las tablas multi-tenant
• Indices en Foreign Keys
• Indices en columnas de busqueda frecuente
• Indices GIN para busqueda full-text

Triggers Estandar

• set_updated_at: Actualiza updated_at automaticamente en cada UPDATE
• update_customer_fiado_balance: Recalcula balance de fiados del cliente

---

Conexion

Desarrollo

Host:     localhost
Puerto:   5432
Database: michangarrito_dev
Usuario:  michangarrito_dev
Password: (ver .env)

Produccion

Host:     (configurar en .env)
Puerto:   5432
Database: michangarrito
Usuario:  michangarrito
SSL:      Requerido

---

Scripts de Base de Datos

Script	Ubicacion	Descripcion
create-database.sh	database/scripts/	Crear BD desde cero
recreate-database.sh	database/scripts/	Recrear BD (destructivo)
drop-and-recreate-database.sh	database/scripts/	Eliminar y recrear BD

Orden de Ejecucion de Schemas

00-extensions.sql    -> Extensiones PostgreSQL
01-schemas.sql       -> Creacion de schemas (11 schemas)
02-functions.sql     -> Funciones utilitarias
03-public.sql        -> Schema public (tenants, tenant_settings, tenant_integration_credentials, tenant_whatsapp_numbers)
04-auth.sql          -> Schema auth
05-catalog.sql       -> Schema catalog
06-sales.sql         -> Schema sales (incluye virtual_accounts, codi_transactions, spei_transactions, payment_config)
07-inventory.sql     -> Schema inventory
08-customers.sql     -> Schema customers
09-orders.sql        -> Schema orders
10-subscriptions.sql -> Schema subscriptions (incluye referral_codes, referrals, referral_rewards)
11-messaging.sql     -> Schema messaging
12-integrations.sql  -> Schema integrations
13-billing.sql       -> Schema billing (tax_configs, invoices, invoice_items, invoice_item_taxes, invoice_history)
14-marketplace.sql   -> Schema marketplace (suppliers, supplier_products, supplier_orders, supplier_order_items, supplier_reviews, supplier_favorites)
15-rls-policies.sql  -> Politicas RLS para todos los schemas
16-seed-data.sql     -> Datos iniciales (planes, configuraciones)

---

Referencias

• Environment Inventory
• Contexto del Proyecto
• Database Inventory
• Especificacion de Componentes

---

Version: 2.2.0 Fecha: 2026-01-10 Actualizado: Schema integrations agregado - 12 schemas, ~49 tablas completas