# ============================================================================ # INVENTARIO COMPLETO DE OBJETOS DE BASE DE DATOS # ERP GENERIC - PostgreSQL 15+ # ============================================================================ # Fecha: 2025-12-09 (Actualizado) # Propósito: Inventario exhaustivo de todos los objetos de BD extraídos de DDL # Schemas: auth, core, analytics, financial, inventory, purchase, sales, projects, system, billing, crm, hr # Total: 12 schemas, 144 tablas # ============================================================================ prerequisites: extensions: - uuid-ossp - pgcrypto - pg_trgm - unaccent composite_types: - name: money_amount attributes: - amount: NUMERIC(15,2) - currency_code: CHAR(3) - name: address_components attributes: - street: VARCHAR(255) - street2: VARCHAR(255) - city: VARCHAR(100) - state: VARCHAR(100) - zip: VARCHAR(20) - country_code: CHAR(2) functions: - name: update_updated_at_column purpose: "Generic trigger function to auto-update updated_at timestamp on row modification" - name: normalize_search_text purpose: "Normalize text for search by removing accents and converting to lowercase" - name: generate_random_code purpose: "Generate random alphanumeric code of specified length (default 8)" - name: is_valid_email purpose: "Validate email format using regex" - name: is_valid_phone purpose: "Validate phone number format (at least 7 digits)" - name: clean_phone purpose: "Remove non-numeric characters from phone number" - name: calculate_age purpose: "Calculate age in years from birthdate" - name: get_fiscal_year_start purpose: "Get the start date of fiscal year for a given date" - name: round_currency purpose: "Round numeric value to specified decimal places (default 2 for currency)" # ============================================================================ # SCHEMA: auth # ============================================================================ auth: enums: - name: user_status values: [active, inactive, suspended, pending_verification] - name: tenant_status values: [active, suspended, trial, cancelled] - name: session_status values: [active, expired, revoked] - name: permission_action values: [create, read, update, delete, approve, cancel, export] tables: - name: tenants columns: [id, name, subdomain, schema_name, status, settings, plan, max_users, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: [] - name: companies columns: [id, tenant_id, name, legal_name, tax_id, currency_id, parent_company_id, partner_id, settings, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: parent_company_id references: auth.companies(id) - column: currency_id references: core.currencies(id) - column: partner_id references: core.partners(id) - name: users columns: [id, tenant_id, email, password_hash, full_name, avatar_url, status, is_superuser, email_verified_at, last_login_at, last_login_ip, login_count, language, timezone, settings, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: roles columns: [id, tenant_id, name, code, description, is_system, color, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: permissions columns: [id, resource, action, description, module, created_at] foreign_keys: [] - name: user_roles columns: [user_id, role_id, assigned_at, assigned_by] foreign_keys: - column: user_id references: auth.users(id) - column: role_id references: auth.roles(id) - name: role_permissions columns: [role_id, permission_id, granted_at, granted_by] foreign_keys: - column: role_id references: auth.roles(id) - column: permission_id references: auth.permissions(id) - name: sessions columns: [id, user_id, token, refresh_token, status, expires_at, refresh_expires_at, ip_address, user_agent, device_info, created_at, revoked_at, revoked_reason] foreign_keys: - column: user_id references: auth.users(id) - name: user_companies columns: [user_id, company_id, is_default, assigned_at] foreign_keys: - column: user_id references: auth.users(id) - column: company_id references: auth.companies(id) - name: password_resets columns: [id, user_id, token, expires_at, used_at, ip_address, created_at] foreign_keys: - column: user_id references: auth.users(id) functions: - name: get_current_tenant_id purpose: "Obtiene el tenant_id del contexto actual" - name: get_current_user_id purpose: "Obtiene el user_id del contexto actual" - name: get_current_company_id purpose: "Obtiene el company_id del contexto actual" - name: user_has_permission purpose: "Verifica si un usuario tiene un permiso específico" - name: clean_expired_sessions purpose: "Limpia sesiones expiradas (ejecutar periódicamente)" - name: update_updated_at_column purpose: "Actualiza updated_at y updated_by al modificar registros" - name: validate_tenant_has_admin purpose: "Valida que tenant tenga al menos 1 admin antes de eliminar role" - name: auto_expire_session purpose: "Auto-marca sesión como expirada si pasa expires_at" triggers: - name: trg_tenants_updated_at table: tenants - name: trg_companies_updated_at table: companies - name: trg_users_updated_at table: users - name: trg_roles_updated_at table: roles - name: trg_validate_tenant_has_admin table: user_roles - name: trg_auto_expire_session table: sessions indexes: - name: idx_tenants_subdomain table: tenants columns: [subdomain] - name: idx_tenants_status table: tenants columns: [status] - name: idx_companies_tenant_id table: companies columns: [tenant_id] - name: idx_companies_parent_company_id table: companies columns: [parent_company_id] - name: idx_users_tenant_id table: users columns: [tenant_id] - name: idx_users_email table: users columns: [email] - name: idx_users_status table: users columns: [status] - name: idx_roles_tenant_id table: roles columns: [tenant_id] - name: idx_roles_code table: roles columns: [code] - name: idx_sessions_user_id table: sessions columns: [user_id] - name: idx_sessions_token table: sessions columns: [token] rls_policies: - name: tenant_isolation_companies table: companies - name: tenant_isolation_users table: users - name: tenant_isolation_roles table: roles views: - name: user_permissions_view purpose: "Vista de permisos efectivos por usuario" - name: active_sessions_view purpose: "Vista de sesiones activas con tiempo restante" # ============================================================================ # SCHEMA: core # ============================================================================ core: enums: - name: partner_type values: [person, company] - name: partner_category values: [customer, supplier, employee, contact, other] - name: address_type values: [billing, shipping, contact, other] - name: uom_type values: [reference, bigger, smaller] tables: - name: countries columns: [id, code, name, phone_code, currency_code, created_at] foreign_keys: [] - name: currencies columns: [id, code, name, symbol, decimals, rounding, active, created_at] foreign_keys: [] - name: exchange_rates columns: [id, from_currency_id, to_currency_id, rate, date, created_at] foreign_keys: - column: from_currency_id references: core.currencies(id) - column: to_currency_id references: core.currencies(id) - name: uom_categories columns: [id, name, description, created_at] foreign_keys: [] - name: uom columns: [id, category_id, name, code, uom_type, factor, rounding, active, created_at] foreign_keys: - column: category_id references: core.uom_categories(id) - name: partners columns: [id, tenant_id, name, legal_name, partner_type, is_customer, is_supplier, is_employee, is_company, email, phone, mobile, website, tax_id, company_id, parent_id, user_id, payment_term_id, pricelist_id, language, currency_id, notes, internal_notes, active, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: parent_id references: core.partners(id) - column: user_id references: auth.users(id) - column: currency_id references: core.currencies(id) - name: addresses columns: [id, partner_id, address_type, street, street2, city, state, zip_code, country_id, is_default, active, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: partner_id references: core.partners(id) - column: country_id references: core.countries(id) - name: product_categories columns: [id, tenant_id, name, code, parent_id, full_path, notes, active, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: parent_id references: core.product_categories(id) - name: tags columns: [id, tenant_id, name, color, model, description, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: sequences columns: [id, tenant_id, company_id, code, name, prefix, suffix, next_number, padding, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - name: attachments columns: [id, tenant_id, model, record_id, filename, mimetype, size_bytes, url, description, is_public, created_at, created_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: notes columns: [id, tenant_id, model, record_id, subject, content, is_pinned, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) functions: - name: generate_next_sequence purpose: "Genera el siguiente número de secuencia para un código dado" - name: update_product_category_path purpose: "Actualiza el path completo de la categoría al crear/actualizar" - name: get_exchange_rate purpose: "Obtiene la tasa de cambio entre dos monedas en una fecha específica" triggers: - name: trg_partners_updated_at table: partners - name: trg_addresses_updated_at table: addresses - name: trg_product_categories_updated_at table: product_categories - name: trg_product_categories_update_path table: product_categories - name: trg_notes_updated_at table: notes indexes: - name: idx_countries_code table: countries columns: [code] - name: idx_currencies_code table: currencies columns: [code] - name: idx_partners_tenant_id table: partners columns: [tenant_id] - name: idx_partners_name table: partners columns: [name] - name: idx_partners_email table: partners columns: [email] - name: idx_partners_is_customer table: partners columns: [tenant_id, is_customer] - name: idx_partners_is_supplier table: partners columns: [tenant_id, is_supplier] - name: idx_product_categories_tenant_id table: product_categories columns: [tenant_id] - name: idx_sequences_tenant_id table: sequences columns: [tenant_id] rls_policies: - name: tenant_isolation_partners table: partners - name: tenant_isolation_product_categories table: product_categories - name: tenant_isolation_tags table: tags - name: tenant_isolation_sequences table: sequences - name: tenant_isolation_attachments table: attachments - name: tenant_isolation_notes table: notes views: - name: customers_view purpose: "Vista de partners que son clientes" - name: suppliers_view purpose: "Vista de partners que son proveedores" - name: employees_view purpose: "Vista de partners que son empleados" # ============================================================================ # SCHEMA: analytics # ============================================================================ analytics: enums: - name: account_type values: [project, department, cost_center, customer, product, other] - name: line_type values: [expense, income, timesheet] - name: account_status values: [active, inactive, closed] tables: - name: analytic_plans columns: [id, tenant_id, company_id, name, description, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - name: analytic_accounts columns: [id, tenant_id, company_id, plan_id, name, code, account_type, parent_id, full_path, partner_id, budget, status, date_start, date_end, description, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: plan_id references: analytics.analytic_plans(id) - column: parent_id references: analytics.analytic_accounts(id) - column: partner_id references: core.partners(id) - name: analytic_tags columns: [id, tenant_id, name, color, description, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: cost_centers columns: [id, tenant_id, company_id, name, code, analytic_account_id, manager_id, budget_monthly, budget_annual, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - column: manager_id references: auth.users(id) - name: analytic_lines columns: [id, tenant_id, company_id, analytic_account_id, date, amount, unit_amount, line_type, product_id, employee_id, partner_id, name, description, source_model, source_id, source_document, currency_id, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - column: product_id references: inventory.products(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - name: analytic_line_tags columns: [analytic_line_id, analytic_tag_id, created_at] foreign_keys: - column: analytic_line_id references: analytics.analytic_lines(id) - column: analytic_tag_id references: analytics.analytic_tags(id) - name: analytic_distributions columns: [id, source_model, source_id, analytic_account_id, percentage, amount, created_at] foreign_keys: - column: analytic_account_id references: analytics.analytic_accounts(id) functions: - name: update_analytic_account_path purpose: "Actualiza el path completo de la cuenta analítica" - name: get_analytic_balance purpose: "Obtiene el balance de una cuenta analítica en un período" - name: validate_distribution_100_percent purpose: "Valida que la distribución analítica no exceda el 100%" - name: create_analytic_line_from_invoice purpose: "Crea una línea analítica a partir de una línea de factura" triggers: - name: trg_analytic_plans_updated_at table: analytic_plans - name: trg_analytic_accounts_updated_at table: analytic_accounts - name: trg_analytic_accounts_update_path table: analytic_accounts - name: trg_cost_centers_updated_at table: cost_centers - name: trg_analytic_distributions_validate_100 table: analytic_distributions indexes: - name: idx_analytic_plans_tenant_id table: analytic_plans columns: [tenant_id] - name: idx_analytic_accounts_tenant_id table: analytic_accounts columns: [tenant_id] - name: idx_analytic_accounts_company_id table: analytic_accounts columns: [company_id] - name: idx_analytic_lines_tenant_id table: analytic_lines columns: [tenant_id] - name: idx_analytic_lines_analytic_account_id table: analytic_lines columns: [analytic_account_id] - name: idx_cost_centers_tenant_id table: cost_centers columns: [tenant_id] rls_policies: - name: tenant_isolation_analytic_plans table: analytic_plans - name: tenant_isolation_analytic_accounts table: analytic_accounts - name: tenant_isolation_analytic_tags table: analytic_tags - name: tenant_isolation_cost_centers table: cost_centers - name: tenant_isolation_analytic_lines table: analytic_lines views: - name: analytic_balance_view purpose: "Vista de balance analítico por cuenta con presupuesto vs real" # ============================================================================ # SCHEMA: financial # ============================================================================ financial: enums: - name: account_type values: [asset, liability, equity, revenue, expense] - name: journal_type values: [sale, purchase, bank, cash, general] - name: entry_status values: [draft, posted, cancelled] - name: invoice_type values: [customer, supplier] - name: invoice_status values: [draft, open, paid, cancelled] - name: payment_type values: [inbound, outbound] - name: payment_method values: [cash, bank_transfer, check, card, other] - name: payment_status values: [draft, posted, reconciled, cancelled] - name: tax_type values: [sales, purchase, all] - name: fiscal_period_status values: [open, closed] tables: - name: account_types columns: [id, code, name, account_type, description, created_at] foreign_keys: [] - name: accounts columns: [id, tenant_id, company_id, code, name, account_type_id, parent_id, currency_id, is_reconcilable, is_deprecated, notes, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: account_type_id references: financial.account_types(id) - column: parent_id references: financial.accounts(id) - column: currency_id references: core.currencies(id) - name: journals columns: [id, tenant_id, company_id, name, code, journal_type, default_account_id, sequence_id, currency_id, active, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: default_account_id references: financial.accounts(id) - column: sequence_id references: core.sequences(id) - column: currency_id references: core.currencies(id) - name: fiscal_years columns: [id, tenant_id, company_id, name, code, start_date, end_date, status, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - name: fiscal_periods columns: [id, tenant_id, fiscal_year_id, name, code, start_date, end_date, status, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: fiscal_year_id references: financial.fiscal_years(id) - name: journal_entries columns: [id, tenant_id, company_id, journal_id, name, ref, date, status, notes, created_at, created_by, updated_at, updated_by, posted_at, posted_by, cancelled_at, cancelled_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: journal_id references: financial.journals(id) - name: journal_entry_lines columns: [id, entry_id, account_id, partner_id, debit, credit, analytic_account_id, description, ref, currency_id, amount_currency, created_at] foreign_keys: - column: entry_id references: financial.journal_entries(id) - column: account_id references: financial.accounts(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - name: taxes columns: [id, tenant_id, company_id, name, code, rate, tax_type, account_id, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: account_id references: financial.accounts(id) - name: payment_terms columns: [id, tenant_id, company_id, name, code, terms, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - name: invoices columns: [id, tenant_id, company_id, partner_id, invoice_type, number, ref, invoice_date, due_date, currency_id, amount_untaxed, amount_tax, amount_total, amount_paid, amount_residual, status, payment_term_id, journal_id, journal_entry_id, notes, created_at, created_by, updated_at, updated_by, validated_at, validated_by, cancelled_at, cancelled_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - column: payment_term_id references: financial.payment_terms(id) - column: journal_id references: financial.journals(id) - column: journal_entry_id references: financial.journal_entries(id) - name: invoice_lines columns: [id, invoice_id, product_id, description, quantity, uom_id, price_unit, tax_ids, amount_untaxed, amount_tax, amount_total, account_id, analytic_account_id, created_at, updated_at] foreign_keys: - column: invoice_id references: financial.invoices(id) - column: uom_id references: core.uom(id) - column: account_id references: financial.accounts(id) - name: payments columns: [id, tenant_id, company_id, partner_id, payment_type, payment_method, amount, currency_id, payment_date, ref, status, journal_id, journal_entry_id, notes, created_at, created_by, updated_at, updated_by, posted_at, posted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - column: journal_id references: financial.journals(id) - column: journal_entry_id references: financial.journal_entries(id) - name: payment_invoice columns: [payment_id, invoice_id, amount, created_at] foreign_keys: - column: payment_id references: financial.payments(id) - column: invoice_id references: financial.invoices(id) - name: bank_accounts columns: [id, tenant_id, company_id, partner_id, bank_name, account_number, account_holder, currency_id, journal_id, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - column: journal_id references: financial.journals(id) - name: reconciliations columns: [id, tenant_id, company_id, bank_account_id, start_date, end_date, balance_start, balance_end_real, balance_end_computed, reconciled_line_ids, status, created_at, created_by, updated_at, updated_by, validated_at, validated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: bank_account_id references: financial.bank_accounts(id) functions: - name: validate_entry_balance purpose: "Valida que un asiento contable esté balanceado (debit = credit)" - name: post_journal_entry purpose: "Contabiliza un asiento contable después de validar su balance" - name: calculate_invoice_totals purpose: "Calcula los totales de una factura a partir de sus líneas" - name: update_invoice_paid_amount purpose: "Actualiza el monto pagado y estado de una factura" triggers: - name: trg_accounts_updated_at table: accounts - name: trg_journals_updated_at table: journals - name: trg_journal_entries_updated_at table: journal_entries - name: trg_invoices_updated_at table: invoices - name: trg_payments_updated_at table: payments - name: trg_journal_entries_validate_balance table: journal_entries - name: trg_invoice_lines_update_totals table: invoice_lines - name: trg_payment_invoice_update_paid table: payment_invoice - name: track_invoice_changes table: invoices - name: track_journal_entry_changes table: journal_entries indexes: - name: idx_accounts_tenant_id table: accounts columns: [tenant_id] - name: idx_accounts_company_id table: accounts columns: [company_id] - name: idx_journals_tenant_id table: journals columns: [tenant_id] - name: idx_journal_entries_tenant_id table: journal_entries columns: [tenant_id] - name: idx_invoices_tenant_id table: invoices columns: [tenant_id] - name: idx_invoices_partner_id table: invoices columns: [partner_id] - name: idx_invoices_status table: invoices columns: [status] - name: idx_payments_tenant_id table: payments columns: [tenant_id] rls_policies: - name: tenant_isolation_accounts table: accounts - name: tenant_isolation_journals table: journals - name: tenant_isolation_fiscal_years table: fiscal_years - name: tenant_isolation_fiscal_periods table: fiscal_periods - name: tenant_isolation_journal_entries table: journal_entries - name: tenant_isolation_taxes table: taxes - name: tenant_isolation_payment_terms table: payment_terms - name: tenant_isolation_invoices table: invoices - name: tenant_isolation_payments table: payments - name: tenant_isolation_bank_accounts table: bank_accounts - name: tenant_isolation_reconciliations table: reconciliations views: [] # ============================================================================ # SCHEMA: inventory # ============================================================================ inventory: enums: - name: product_type values: [storable, consumable, service] - name: tracking_type values: [none, lot, serial] - name: location_type values: [internal, customer, supplier, inventory, production, transit] - name: picking_type values: [incoming, outgoing, internal] - name: move_status values: [draft, confirmed, assigned, done, cancelled] - name: valuation_method values: [fifo, average, standard] tables: - name: products columns: [id, tenant_id, name, code, barcode, description, product_type, tracking, category_id, uom_id, purchase_uom_id, cost_price, list_price, valuation_method, is_storable, weight, volume, can_be_sold, can_be_purchased, image_url, active, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: category_id references: core.product_categories(id) - column: uom_id references: core.uom(id) - column: purchase_uom_id references: core.uom(id) - name: product_variants columns: [id, product_template_id, attribute_values, name, code, barcode, price_extra, active, created_at, created_by] foreign_keys: - column: product_template_id references: inventory.products(id) - name: warehouses columns: [id, tenant_id, company_id, name, code, address_id, is_default, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: address_id references: core.addresses(id) - name: locations columns: [id, tenant_id, warehouse_id, name, complete_name, location_type, parent_id, is_scrap_location, is_return_location, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: warehouse_id references: inventory.warehouses(id) - column: parent_id references: inventory.locations(id) - name: stock_quants columns: [id, product_id, location_id, lot_id, quantity, reserved_quantity, available_quantity, cost, created_at, updated_at] foreign_keys: - column: product_id references: inventory.products(id) - column: location_id references: inventory.locations(id) - column: lot_id references: inventory.lots(id) - name: lots columns: [id, tenant_id, product_id, name, ref, manufacture_date, expiration_date, removal_date, alert_date, notes, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: product_id references: inventory.products(id) - name: pickings columns: [id, tenant_id, company_id, name, picking_type, location_id, location_dest_id, partner_id, scheduled_date, date_done, origin, status, notes, created_at, created_by, updated_at, updated_by, validated_at, validated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: location_id references: inventory.locations(id) - column: location_dest_id references: inventory.locations(id) - column: partner_id references: core.partners(id) - name: stock_moves columns: [id, tenant_id, product_id, product_uom_id, location_id, location_dest_id, product_qty, quantity_done, lot_id, picking_id, origin, ref, status, date_expected, date, price_unit, analytic_account_id, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: product_id references: inventory.products(id) - column: product_uom_id references: core.uom(id) - column: location_id references: inventory.locations(id) - column: location_dest_id references: inventory.locations(id) - column: lot_id references: inventory.lots(id) - column: picking_id references: inventory.pickings(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - name: inventory_adjustments columns: [id, tenant_id, company_id, name, location_id, date, status, notes, created_at, created_by, updated_at, updated_by, validated_at, validated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: location_id references: inventory.locations(id) - name: inventory_adjustment_lines columns: [id, adjustment_id, product_id, location_id, lot_id, theoretical_qty, counted_qty, difference_qty, created_at] foreign_keys: - column: adjustment_id references: inventory.inventory_adjustments(id) - column: product_id references: inventory.products(id) - column: location_id references: inventory.locations(id) - column: lot_id references: inventory.lots(id) functions: - name: update_stock_quant purpose: "Actualiza la cantidad en stock de un producto en una ubicación" - name: reserve_quantity purpose: "Reserva cantidad de un producto en una ubicación" - name: get_product_stock purpose: "Obtiene el stock disponible de un producto por ubicación" - name: process_stock_move purpose: "Procesa un movimiento de inventario y actualiza los quants" - name: update_location_complete_name purpose: "Actualiza el nombre completo de la ubicación" triggers: - name: trg_products_updated_at table: products - name: trg_warehouses_updated_at table: warehouses - name: trg_locations_updated_at table: locations - name: trg_locations_update_complete_name table: locations - name: trg_pickings_updated_at table: pickings - name: trg_stock_moves_updated_at table: stock_moves - name: track_stock_move_changes table: stock_moves indexes: - name: idx_products_tenant_id table: products columns: [tenant_id] - name: idx_products_code table: products columns: [code] - name: idx_products_barcode table: products columns: [barcode] - name: idx_warehouses_tenant_id table: warehouses columns: [tenant_id] - name: idx_locations_tenant_id table: locations columns: [tenant_id] - name: idx_stock_quants_product_id table: stock_quants columns: [product_id] - name: idx_pickings_tenant_id table: pickings columns: [tenant_id] - name: idx_stock_moves_tenant_id table: stock_moves columns: [tenant_id] rls_policies: - name: tenant_isolation_products table: products - name: tenant_isolation_warehouses table: warehouses - name: tenant_isolation_locations table: locations - name: tenant_isolation_lots table: lots - name: tenant_isolation_pickings table: pickings - name: tenant_isolation_stock_moves table: stock_moves - name: tenant_isolation_inventory_adjustments table: inventory_adjustments views: - name: stock_by_product_view purpose: "Vista de stock disponible por producto y ubicación" # ============================================================================ # SCHEMA: purchase # ============================================================================ purchase: enums: - name: order_status values: [draft, sent, confirmed, received, billed, cancelled] - name: rfq_status values: [draft, sent, responded, accepted, rejected, cancelled] - name: agreement_type values: [price, discount, blanket] tables: - name: purchase_orders columns: [id, tenant_id, company_id, name, ref, partner_id, order_date, expected_date, effective_date, currency_id, payment_term_id, amount_untaxed, amount_tax, amount_total, status, receipt_status, invoice_status, picking_id, invoice_id, notes, created_at, created_by, updated_at, updated_by, confirmed_at, confirmed_by, cancelled_at, cancelled_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - column: payment_term_id references: financial.payment_terms(id) - column: picking_id references: inventory.pickings(id) - column: invoice_id references: financial.invoices(id) - name: purchase_order_lines columns: [id, order_id, product_id, description, quantity, qty_received, qty_invoiced, uom_id, price_unit, discount, tax_ids, amount_untaxed, amount_tax, amount_total, expected_date, analytic_account_id, created_at, updated_at] foreign_keys: - column: order_id references: purchase.purchase_orders(id) - column: product_id references: inventory.products(id) - column: uom_id references: core.uom(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - name: rfqs columns: [id, tenant_id, company_id, name, partner_ids, request_date, deadline_date, response_date, status, description, notes, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - name: rfq_lines columns: [id, rfq_id, product_id, description, quantity, uom_id, created_at] foreign_keys: - column: rfq_id references: purchase.rfqs(id) - column: product_id references: inventory.products(id) - column: uom_id references: core.uom(id) - name: vendor_pricelists columns: [id, tenant_id, partner_id, product_id, price, currency_id, min_quantity, valid_from, valid_to, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: partner_id references: core.partners(id) - column: product_id references: inventory.products(id) - column: currency_id references: core.currencies(id) - name: purchase_agreements columns: [id, tenant_id, company_id, name, code, agreement_type, partner_id, start_date, end_date, amount_max, currency_id, is_active, terms, notes, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - name: purchase_agreement_lines columns: [id, agreement_id, product_id, quantity, qty_ordered, price_unit, discount, created_at] foreign_keys: - column: agreement_id references: purchase.purchase_agreements(id) - column: product_id references: inventory.products(id) - name: vendor_evaluations columns: [id, tenant_id, company_id, partner_id, evaluation_date, period_start, period_end, quality_rating, delivery_rating, service_rating, price_rating, overall_rating, on_time_delivery_rate, defect_rate, comments, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) functions: - name: calculate_purchase_order_totals purpose: "Calcula los totales de una orden de compra" - name: create_picking_from_po purpose: "Crea un picking de recepción a partir de una orden de compra" triggers: - name: trg_purchase_orders_updated_at table: purchase_orders - name: trg_rfqs_updated_at table: rfqs - name: trg_vendor_pricelists_updated_at table: vendor_pricelists - name: trg_purchase_order_lines_update_totals table: purchase_order_lines - name: track_purchase_order_changes table: purchase_orders indexes: - name: idx_purchase_orders_tenant_id table: purchase_orders columns: [tenant_id] - name: idx_purchase_orders_partner_id table: purchase_orders columns: [partner_id] - name: idx_purchase_orders_status table: purchase_orders columns: [status] - name: idx_rfqs_tenant_id table: rfqs columns: [tenant_id] - name: idx_vendor_pricelists_tenant_id table: vendor_pricelists columns: [tenant_id] rls_policies: - name: tenant_isolation_purchase_orders table: purchase_orders - name: tenant_isolation_rfqs table: rfqs - name: tenant_isolation_vendor_pricelists table: vendor_pricelists - name: tenant_isolation_purchase_agreements table: purchase_agreements - name: tenant_isolation_vendor_evaluations table: vendor_evaluations views: [] # ============================================================================ # SCHEMA: sales # ============================================================================ sales: enums: - name: order_status values: [draft, sent, sale, done, cancelled] - name: quotation_status values: [draft, sent, approved, rejected, converted, expired] - name: invoice_policy values: [order, delivery] - name: delivery_status values: [pending, partial, delivered] - name: invoice_status values: [pending, partial, invoiced] tables: - name: sales_orders columns: [id, tenant_id, company_id, name, client_order_ref, partner_id, order_date, validity_date, commitment_date, currency_id, pricelist_id, payment_term_id, user_id, sales_team_id, amount_untaxed, amount_tax, amount_total, status, invoice_status, delivery_status, invoice_policy, picking_id, notes, terms_conditions, signature, signature_date, signature_ip, created_at, created_by, updated_at, updated_by, confirmed_at, confirmed_by, cancelled_at, cancelled_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - column: pricelist_id references: sales.pricelists(id) - column: payment_term_id references: financial.payment_terms(id) - column: user_id references: auth.users(id) - column: sales_team_id references: sales.sales_teams(id) - column: picking_id references: inventory.pickings(id) - name: sales_order_lines columns: [id, order_id, product_id, description, quantity, qty_delivered, qty_invoiced, uom_id, price_unit, discount, tax_ids, amount_untaxed, amount_tax, amount_total, analytic_account_id, created_at, updated_at] foreign_keys: - column: order_id references: sales.sales_orders(id) - column: product_id references: inventory.products(id) - column: uom_id references: core.uom(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - name: quotations columns: [id, tenant_id, company_id, name, partner_id, quotation_date, validity_date, currency_id, pricelist_id, user_id, sales_team_id, amount_untaxed, amount_tax, amount_total, status, sale_order_id, notes, terms_conditions, signature, signature_date, signature_ip, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: currency_id references: core.currencies(id) - column: pricelist_id references: sales.pricelists(id) - column: user_id references: auth.users(id) - column: sales_team_id references: sales.sales_teams(id) - column: sale_order_id references: sales.sales_orders(id) - name: quotation_lines columns: [id, quotation_id, product_id, description, quantity, uom_id, price_unit, discount, tax_ids, amount_untaxed, amount_tax, amount_total, created_at] foreign_keys: - column: quotation_id references: sales.quotations(id) - column: product_id references: inventory.products(id) - column: uom_id references: core.uom(id) - name: pricelists columns: [id, tenant_id, company_id, name, currency_id, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: currency_id references: core.currencies(id) - name: pricelist_items columns: [id, pricelist_id, product_id, product_category_id, price, min_quantity, valid_from, valid_to, active, created_at, created_by] foreign_keys: - column: pricelist_id references: sales.pricelists(id) - column: product_id references: inventory.products(id) - column: product_category_id references: core.product_categories(id) - name: customer_groups columns: [id, tenant_id, name, description, discount_percentage, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: customer_group_members columns: [customer_group_id, partner_id, joined_at] foreign_keys: - column: customer_group_id references: sales.customer_groups(id) - column: partner_id references: core.partners(id) - name: sales_teams columns: [id, tenant_id, company_id, name, code, team_leader_id, target_monthly, target_annual, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: team_leader_id references: auth.users(id) - name: sales_team_members columns: [sales_team_id, user_id, joined_at] foreign_keys: - column: sales_team_id references: sales.sales_teams(id) - column: user_id references: auth.users(id) functions: - name: calculate_sales_order_totals purpose: "Calcula los totales de una orden de venta" - name: calculate_quotation_totals purpose: "Calcula los totales de una cotización" - name: convert_quotation_to_order purpose: "Convierte una cotización aprobada en orden de venta" triggers: - name: trg_sales_orders_updated_at table: sales_orders - name: trg_quotations_updated_at table: quotations - name: trg_pricelists_updated_at table: pricelists - name: trg_sales_order_lines_update_totals table: sales_order_lines - name: trg_quotation_lines_update_totals table: quotation_lines - name: track_sales_order_changes table: sales_orders indexes: - name: idx_sales_orders_tenant_id table: sales_orders columns: [tenant_id] - name: idx_sales_orders_partner_id table: sales_orders columns: [partner_id] - name: idx_sales_orders_status table: sales_orders columns: [status] - name: idx_quotations_tenant_id table: quotations columns: [tenant_id] - name: idx_pricelists_tenant_id table: pricelists columns: [tenant_id] rls_policies: - name: tenant_isolation_sales_orders table: sales_orders - name: tenant_isolation_quotations table: quotations - name: tenant_isolation_pricelists table: pricelists - name: tenant_isolation_customer_groups table: customer_groups - name: tenant_isolation_sales_teams table: sales_teams views: [] # ============================================================================ # SCHEMA: projects # ============================================================================ projects: enums: - name: project_status values: [draft, active, completed, cancelled, on_hold] - name: privacy_type values: [public, private, followers] - name: task_status values: [todo, in_progress, review, done, cancelled] - name: task_priority values: [low, normal, high, urgent] - name: dependency_type values: [finish_to_start, start_to_start, finish_to_finish, start_to_finish] - name: milestone_status values: [pending, completed] tables: - name: projects columns: [id, tenant_id, company_id, name, code, description, manager_id, partner_id, analytic_account_id, date_start, date_end, status, privacy, allow_timesheets, color, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: manager_id references: auth.users(id) - column: partner_id references: core.partners(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - name: project_stages columns: [id, tenant_id, project_id, name, sequence, is_closed, fold, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: project_id references: projects.projects(id) - name: tasks columns: [id, tenant_id, project_id, stage_id, name, description, assigned_to, partner_id, parent_id, date_start, date_deadline, planned_hours, actual_hours, progress, priority, status, milestone_id, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: project_id references: projects.projects(id) - column: stage_id references: projects.project_stages(id) - column: assigned_to references: auth.users(id) - column: partner_id references: core.partners(id) - column: parent_id references: projects.tasks(id) - column: milestone_id references: projects.milestones(id) - name: milestones columns: [id, tenant_id, project_id, name, description, target_date, status, created_at, created_by, updated_at, updated_by, completed_at, completed_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: project_id references: projects.projects(id) - name: task_dependencies columns: [id, task_id, depends_on_id, dependency_type, created_at] foreign_keys: - column: task_id references: projects.tasks(id) - column: depends_on_id references: projects.tasks(id) - name: task_tags columns: [id, tenant_id, name, color, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: task_tag_assignments columns: [task_id, tag_id, created_at] foreign_keys: - column: task_id references: projects.tasks(id) - column: tag_id references: projects.task_tags(id) - name: timesheets columns: [id, tenant_id, company_id, task_id, project_id, employee_id, user_id, date, hours, description, analytic_account_id, analytic_line_id, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: task_id references: projects.tasks(id) - column: project_id references: projects.projects(id) - column: user_id references: auth.users(id) - column: analytic_account_id references: analytics.analytic_accounts(id) - column: analytic_line_id references: analytics.analytic_lines(id) - name: task_checklists columns: [id, task_id, item_name, is_completed, sequence, created_at, completed_at, completed_by] foreign_keys: - column: task_id references: projects.tasks(id) - name: project_templates columns: [id, tenant_id, name, description, template_data, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) functions: - name: update_task_actual_hours purpose: "Actualiza las horas reales de una tarea al cambiar timesheets" - name: check_task_dependencies purpose: "Verifica si todas las dependencias de una tarea están completadas" - name: prevent_circular_dependencies purpose: "Previene la creación de dependencias circulares entre tareas" triggers: - name: trg_projects_updated_at table: projects - name: trg_tasks_updated_at table: tasks - name: trg_milestones_updated_at table: milestones - name: trg_timesheets_updated_at table: timesheets - name: trg_timesheets_update_task_hours table: timesheets - name: trg_task_dependencies_prevent_circular table: task_dependencies - name: track_project_changes table: projects indexes: - name: idx_projects_tenant_id table: projects columns: [tenant_id] - name: idx_projects_company_id table: projects columns: [company_id] - name: idx_tasks_tenant_id table: tasks columns: [tenant_id] - name: idx_tasks_project_id table: tasks columns: [project_id] - name: idx_tasks_assigned_to table: tasks columns: [assigned_to] - name: idx_timesheets_tenant_id table: timesheets columns: [tenant_id] rls_policies: - name: tenant_isolation_projects table: projects - name: tenant_isolation_project_stages table: project_stages - name: tenant_isolation_tasks table: tasks - name: tenant_isolation_milestones table: milestones - name: tenant_isolation_task_tags table: task_tags - name: tenant_isolation_timesheets table: timesheets - name: tenant_isolation_project_templates table: project_templates views: [] # ============================================================================ # SCHEMA: system # ============================================================================ system: enums: - name: message_type values: [comment, note, email, notification, system] - name: notification_status values: [pending, sent, read, failed] - name: activity_type values: [call, meeting, email, todo, follow_up, custom] - name: activity_status values: [planned, done, cancelled, overdue] - name: email_status values: [draft, queued, sending, sent, failed, bounced] - name: log_level values: [debug, info, warning, error, critical] - name: report_format values: [pdf, excel, csv, html] tables: - name: messages columns: [id, tenant_id, model, record_id, message_type, subject, body, author_id, author_name, author_email, email_from, reply_to, message_id, parent_id, attachment_ids, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: author_id references: auth.users(id) - column: parent_id references: system.messages(id) - name: message_followers columns: [id, model, record_id, partner_id, user_id, email_notifications, created_at] foreign_keys: - column: partner_id references: core.partners(id) - column: user_id references: auth.users(id) - name: notifications columns: [id, tenant_id, user_id, title, message, url, model, record_id, status, read_at, created_at, sent_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: user_id references: auth.users(id) - name: activities columns: [id, tenant_id, model, record_id, activity_type, summary, description, assigned_to, assigned_by, due_date, due_time, status, created_at, created_by, completed_at, completed_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: assigned_to references: auth.users(id) - column: assigned_by references: auth.users(id) - name: message_templates columns: [id, tenant_id, name, model, subject, body_html, body_text, email_from, reply_to, cc, bcc, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: email_queue columns: [id, tenant_id, email_to, email_cc, email_bcc, subject, body_html, body_text, email_from, reply_to, attachment_ids, status, attempts, max_attempts, error_message, message_id, opened_at, clicked_at, created_at, scheduled_at, sent_at, failed_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: logs columns: [id, tenant_id, level, logger, message, stack_trace, user_id, ip_address, user_agent, request_id, model, record_id, metadata, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: user_id references: auth.users(id) - name: reports columns: [id, tenant_id, name, code, description, model, report_type, query_template, template_file, default_format, is_public, active, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: report_executions columns: [id, tenant_id, report_id, parameters, format, file_url, file_size, error_message, status, created_at, created_by, started_at, completed_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: report_id references: system.reports(id) - name: dashboards columns: [id, tenant_id, name, description, layout, is_default, user_id, is_public, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: user_id references: auth.users(id) - name: dashboard_widgets columns: [id, dashboard_id, widget_type, title, config, position, data_source, query_params, refresh_interval, created_at, updated_at] foreign_keys: - column: dashboard_id references: system.dashboards(id) - name: field_tracking_config columns: [id, table_schema, table_name, field_name, track_changes, field_type, display_label, created_at] foreign_keys: [] - name: change_log columns: [id, tenant_id, table_schema, table_name, record_id, changed_by, changed_at, change_type, field_name, field_label, old_value, new_value, change_context, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: changed_by references: auth.users(id) functions: - name: notify_followers purpose: "Notifica a los seguidores de un registro cuando hay un nuevo mensaje" - name: mark_activities_as_overdue purpose: "Marca actividades vencidas como overdue (ejecutar diariamente)" - name: clean_old_logs purpose: "Limpia logs antiguos (mantener solo críticos)" - name: track_field_changes purpose: "Función trigger para trackear cambios automáticamente según configuración en field_tracking_config (patrón mail.thread de Odoo)" triggers: - name: trg_messages_updated_at table: messages - name: trg_message_templates_updated_at table: message_templates - name: trg_reports_updated_at table: reports - name: trg_dashboards_updated_at table: dashboards - name: trg_dashboard_widgets_updated_at table: dashboard_widgets indexes: - name: idx_messages_tenant_id table: messages columns: [tenant_id] - name: idx_messages_model_record table: messages columns: [model, record_id] - name: idx_notifications_tenant_id table: notifications columns: [tenant_id] - name: idx_notifications_user_id table: notifications columns: [user_id] - name: idx_activities_tenant_id table: activities columns: [tenant_id] - name: idx_logs_tenant_id table: logs columns: [tenant_id] - name: idx_logs_level table: logs columns: [level] - name: idx_change_log_tenant_id table: change_log columns: [tenant_id] - name: idx_change_log_record table: change_log columns: [table_schema, table_name, record_id] rls_policies: - name: tenant_isolation_messages table: messages - name: tenant_isolation_notifications table: notifications - name: tenant_isolation_activities table: activities - name: tenant_isolation_message_templates table: message_templates - name: tenant_isolation_logs table: logs - name: tenant_isolation_reports table: reports - name: tenant_isolation_report_executions table: report_executions - name: tenant_isolation_dashboards table: dashboards - name: tenant_isolation_change_log table: change_log views: [] # ============================================================================ # RESUMEN DE INVENTARIO (ACTUALIZADO 2025-12-09) # ============================================================================ summary: total_schemas: 12 total_enums: 49 total_tables: 144 total_functions: 52 total_triggers: 95 total_indexes: 350+ total_rls_policies: 80+ total_views: 8 schemas: - name: prerequisites tables: 0 functions: 9 types: 2 - name: auth tables: 26 # 10 (auth.sql) + 16 (auth-extensions.sql) functions: 10 enums: 4 - name: core tables: 12 functions: 3 enums: 4 - name: analytics tables: 7 functions: 4 enums: 3 - name: financial tables: 15 functions: 4 enums: 10 - name: inventory tables: 20 # 10 (inventory.sql) + 10 (inventory-extensions.sql) functions: 8 enums: 6 - name: purchase tables: 8 functions: 2 enums: 3 - name: sales tables: 10 functions: 3 enums: 5 - name: projects tables: 10 functions: 3 enums: 6 - name: system tables: 13 functions: 4 enums: 7 - name: billing tables: 11 functions: 3 enums: 5 - name: crm tables: 6 functions: 0 enums: 4 - name: hr tables: 6 functions: 0 enums: 5 # ============================================================================ # SCHEMA: billing (NUEVO - SaaS/Multi-tenant) # DDL: 10-billing.sql - 11 tablas # ============================================================================ billing: enums: - name: subscription_status values: [trialing, active, past_due, paused, cancelled, suspended, expired] - name: billing_cycle values: [monthly, quarterly, semi_annual, annual] - name: payment_method_type values: [card, bank_transfer, paypal, oxxo, spei, other] - name: invoice_status values: [draft, open, paid, void, uncollectible] - name: payment_status values: [pending, processing, succeeded, failed, cancelled, refunded] tables: - name: subscription_plans columns: [id, code, name, description, price_monthly, price_yearly, currency_code, max_users, max_companies, max_storage_gb, max_api_calls_month, features, is_active, is_public, is_default, trial_days, sort_order, created_at, created_by, updated_at, updated_by] foreign_keys: [] note: "Planes globales (no por tenant)" - name: tenant_owners columns: [id, tenant_id, user_id, ownership_type, billing_email, billing_phone, billing_name, created_at, created_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: user_id references: auth.users(id) - name: subscriptions columns: [id, tenant_id, plan_id, status, billing_cycle, trial_start_at, trial_end_at, current_period_start, current_period_end, cancelled_at, cancel_at_period_end, paused_at, discount_percent, coupon_code, stripe_subscription_id, stripe_customer_id, created_at, created_by, updated_at, updated_by] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: plan_id references: billing.subscription_plans(id) - name: payment_methods columns: [id, tenant_id, type, is_default, card_last_four, card_brand, card_exp_month, card_exp_year, billing_name, billing_email, billing_address_line1, billing_address_line2, billing_city, billing_state, billing_postal_code, billing_country, stripe_payment_method_id, created_at, created_by, updated_at, deleted_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: invoices columns: [id, tenant_id, subscription_id, invoice_number, status, period_start, period_end, due_date, paid_at, voided_at, subtotal, tax_amount, discount_amount, total, amount_paid, amount_due, currency_code, customer_name, customer_tax_id, customer_email, customer_address, pdf_url, cfdi_uuid, cfdi_xml_url, stripe_invoice_id, notes, created_at, created_by, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: subscription_id references: billing.subscriptions(id) - name: invoice_lines columns: [id, invoice_id, description, quantity, unit_price, amount, period_start, period_end, created_at] foreign_keys: - column: invoice_id references: billing.invoices(id) - name: payments columns: [id, tenant_id, invoice_id, payment_method_id, amount, currency_code, status, paid_at, failed_at, refunded_at, failure_reason, failure_code, transaction_id, stripe_payment_intent_id, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: invoice_id references: billing.invoices(id) - column: payment_method_id references: billing.payment_methods(id) - name: usage_records columns: [id, tenant_id, subscription_id, metric_type, quantity, billing_period, recorded_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: subscription_id references: billing.subscriptions(id) - name: coupons columns: [id, code, name, description, discount_type, discount_value, currency_code, max_redemptions, max_redemptions_per_tenant, redemptions_count, valid_from, valid_until, applicable_plans, is_active, created_at, created_by] foreign_keys: [] - name: coupon_redemptions columns: [id, coupon_id, tenant_id, subscription_id, redeemed_at, redeemed_by] foreign_keys: - column: coupon_id references: billing.coupons(id) - column: tenant_id references: auth.tenants(id) - column: subscription_id references: billing.subscriptions(id) - name: subscription_history columns: [id, subscription_id, event_type, previous_plan_id, new_plan_id, previous_status, new_status, metadata, notes, created_at, created_by] foreign_keys: - column: subscription_id references: billing.subscriptions(id) - column: previous_plan_id references: billing.subscription_plans(id) - column: new_plan_id references: billing.subscription_plans(id) functions: - name: get_tenant_plan purpose: "Obtiene información del plan actual de un tenant" - name: can_add_user purpose: "Verifica si el tenant puede agregar más usuarios según su plan" - name: has_feature purpose: "Verifica si una feature está habilitada para el tenant" rls_policies: [] note: "Sin RLS - gestionado a nivel aplicación por razones de seguridad" # ============================================================================ # SCHEMA: crm (NUEVO - Customer Relationship Management) # DDL: 11-crm.sql - 6 tablas # ============================================================================ crm: enums: - name: lead_status values: [new, contacted, qualified, converted, lost] - name: opportunity_status values: [open, won, lost] - name: activity_type values: [call, email, meeting, task, note] - name: lead_source values: [website, phone, email, referral, social_media, advertising, event, other] tables: - name: lead_stages columns: [id, tenant_id, name, sequence, is_won, probability, requirements, active, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: opportunity_stages columns: [id, tenant_id, name, sequence, is_won, probability, requirements, active, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: lost_reasons columns: [id, tenant_id, name, description, active, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: leads columns: [id, tenant_id, company_id, name, ref, contact_name, email, phone, mobile, website, company_name, job_position, industry, employee_count, annual_revenue, street, city, state, zip, country, stage_id, status, user_id, sales_team_id, source, campaign_id, medium, priority, probability, expected_revenue, date_open, date_closed, date_deadline, date_last_activity, partner_id, opportunity_id, lost_reason_id, lost_notes, description, notes, tags, created_by, updated_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: stage_id references: crm.lead_stages(id) - column: user_id references: auth.users(id) - column: sales_team_id references: sales.sales_teams(id) - column: partner_id references: core.partners(id) - column: lost_reason_id references: crm.lost_reasons(id) - name: opportunities columns: [id, tenant_id, company_id, name, ref, partner_id, contact_name, email, phone, stage_id, status, user_id, sales_team_id, priority, probability, expected_revenue, recurring_revenue, recurring_plan, date_deadline, date_closed, date_last_activity, lead_id, source, campaign_id, medium, lost_reason_id, lost_notes, quotation_id, order_id, description, notes, tags, created_by, updated_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: partner_id references: core.partners(id) - column: stage_id references: crm.opportunity_stages(id) - column: user_id references: auth.users(id) - column: sales_team_id references: sales.sales_teams(id) - column: lead_id references: crm.leads(id) - column: lost_reason_id references: crm.lost_reasons(id) - column: quotation_id references: sales.quotations(id) - column: order_id references: sales.sales_orders(id) - name: activities columns: [id, tenant_id, res_model, res_id, activity_type, summary, description, date_deadline, date_done, user_id, assigned_to, done, created_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: user_id references: auth.users(id) - column: assigned_to references: auth.users(id) rls_policies: - name: tenant_isolation_lead_stages table: lead_stages - name: tenant_isolation_opportunity_stages table: opportunity_stages - name: tenant_isolation_lost_reasons table: lost_reasons - name: tenant_isolation_leads table: leads - name: tenant_isolation_opportunities table: opportunities - name: tenant_isolation_crm_activities table: activities # ============================================================================ # SCHEMA: hr (NUEVO - Human Resources) # DDL: 12-hr.sql - 6 tablas # ============================================================================ hr: enums: - name: contract_status values: [draft, active, expired, terminated, cancelled] - name: contract_type values: [permanent, temporary, contractor, internship, part_time] - name: leave_status values: [draft, submitted, approved, rejected, cancelled] - name: leave_type values: [vacation, sick, personal, maternity, paternity, bereavement, unpaid, other] - name: employee_status values: [active, inactive, on_leave, terminated] tables: - name: departments columns: [id, tenant_id, company_id, name, code, parent_id, manager_id, description, color, active, created_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: parent_id references: hr.departments(id) - column: manager_id references: hr.employees(id) - name: job_positions columns: [id, tenant_id, name, department_id, description, requirements, responsibilities, min_salary, max_salary, active, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: department_id references: hr.departments(id) - name: employees columns: [id, tenant_id, company_id, employee_number, first_name, last_name, middle_name, user_id, birth_date, gender, marital_status, nationality, identification_id, identification_type, social_security_number, tax_id, email, work_email, phone, work_phone, mobile, emergency_contact, emergency_phone, street, city, state, zip, country, department_id, job_position_id, manager_id, hire_date, termination_date, status, bank_name, bank_account, bank_clabe, photo_url, notes, created_by, updated_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: user_id references: auth.users(id) - column: department_id references: hr.departments(id) - column: job_position_id references: hr.job_positions(id) - column: manager_id references: hr.employees(id) - name: contracts columns: [id, tenant_id, company_id, employee_id, name, reference, contract_type, status, job_position_id, department_id, date_start, date_end, trial_date_end, wage, wage_type, currency_id, resource_calendar_id, hours_per_week, vacation_days, christmas_bonus_days, document_url, notes, created_by, updated_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: employee_id references: hr.employees(id) - column: job_position_id references: hr.job_positions(id) - column: department_id references: hr.departments(id) - column: currency_id references: core.currencies(id) - name: leave_types columns: [id, tenant_id, name, code, leave_type, requires_approval, max_days, is_paid, color, active, created_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - name: leaves columns: [id, tenant_id, company_id, employee_id, leave_type_id, name, date_from, date_to, number_of_days, status, description, approved_by, approved_at, rejection_reason, created_by, updated_by, created_at, updated_at] foreign_keys: - column: tenant_id references: auth.tenants(id) - column: company_id references: auth.companies(id) - column: employee_id references: hr.employees(id) - column: leave_type_id references: hr.leave_types(id) - column: approved_by references: auth.users(id) rls_policies: - name: tenant_isolation_departments table: departments - name: tenant_isolation_job_positions table: job_positions - name: tenant_isolation_employees table: employees - name: tenant_isolation_contracts table: contracts - name: tenant_isolation_leave_types table: leave_types - name: tenant_isolation_leaves table: leaves # ============================================================================ # SCHEMA: auth (EXTENSIONES - auth-extensions.sql) # DDL: 01-auth-extensions.sql - 16 tablas adicionales # ============================================================================ auth_extensions: note: "Estas tablas complementan el schema auth base (01-auth.sql)" tables: - name: groups purpose: "Grupos de usuarios para permisos" - name: group_implied purpose: "Herencia entre grupos" - name: user_groups purpose: "Asignación usuarios a grupos" - name: models purpose: "Registro de modelos del sistema" - name: model_access purpose: "Permisos CRUD por modelo y grupo" - name: record_rules purpose: "Reglas de acceso a nivel registro (domain filters)" - name: rule_groups purpose: "Asignación reglas a grupos" - name: model_fields purpose: "Campos de modelos" - name: field_permissions purpose: "Permisos a nivel campo" - name: api_keys purpose: "API Keys para autenticación" - name: trusted_devices purpose: "Dispositivos de confianza para 2FA" - name: verification_codes purpose: "Códigos de verificación (2FA, email)" - name: mfa_audit_log purpose: "Auditoría de operaciones MFA" - name: oauth_providers purpose: "Proveedores OAuth2 configurados" - name: oauth_user_links purpose: "Vinculación usuarios con cuentas OAuth" - name: oauth_states purpose: "Estados temporales para flow OAuth" # ============================================================================ # SCHEMA: inventory (EXTENSIONES - inventory-extensions.sql) # DDL: 05-inventory-extensions.sql - 10 tablas adicionales # ============================================================================ inventory_extensions: note: "Estas tablas complementan el schema inventory base (05-inventory.sql)" tables: - name: stock_valuation_layers purpose: "Capas de valoración FIFO/AVCO" - name: category_stock_accounts purpose: "Cuentas contables por categoría de producto" - name: valuation_settings purpose: "Configuración de valoración por empresa" - name: lots purpose: "Lotes de productos (trazabilidad)" - name: stock_move_consume_rel purpose: "Relación movimientos produce/consume" - name: removal_strategies purpose: "Estrategias de remoción (FIFO/LIFO/AVCO)" - name: inventory_count_sessions purpose: "Sesiones de conteo cíclico" - name: inventory_count_lines purpose: "Líneas de conteo" - name: abc_classification_rules purpose: "Reglas de clasificación ABC" - name: product_abc_classification purpose: "Clasificación ABC de productos" # ============================================================================ # FIN DEL INVENTARIO # Última actualización: 2025-12-09 # Total: 12 schemas, 144 tablas # ============================================================================