rckrdmrd
4c4e27d9ba
🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
858 lines
33 KiB
YAML
858 lines
33 KiB
YAML
# DATABASE_INVENTORY.yml - ERP Core
|
|
# Inventario canonico de objetos de base de datos
|
|
# Ubicacion Canonica: orchestration/inventarios/
|
|
# Ultima actualizacion: 2026-01-07
|
|
|
|
version: "3.0"
|
|
project: erp-core
|
|
updated_at: "2026-01-07"
|
|
updated_by: Backend-Agent (Sprint 6-7)
|
|
|
|
# =============================================================================
|
|
# CONFIGURACION DE MOTOR
|
|
# =============================================================================
|
|
|
|
engine:
|
|
name: PostgreSQL
|
|
version: "15+"
|
|
extensions:
|
|
- name: uuid-ossp
|
|
purpose: Generacion de UUIDs
|
|
- name: pg_trgm
|
|
purpose: Busqueda fuzzy por trigram
|
|
- name: btree_gist
|
|
purpose: Indices GiST para exclusion
|
|
- name: pgcrypto
|
|
purpose: Funciones criptograficas
|
|
|
|
# =============================================================================
|
|
# RESUMEN
|
|
# =============================================================================
|
|
|
|
summary:
|
|
total_schemas: 14
|
|
total_tables: 191
|
|
total_tables_base: 118
|
|
total_tables_extensions: 73
|
|
total_functions: 70
|
|
total_triggers: 100
|
|
total_rls_policies: 102
|
|
total_ddl_files: 20
|
|
implemented_extensions: 6
|
|
documented: 30
|
|
|
|
# =============================================================================
|
|
# POLITICA DE CARGA LIMPIA
|
|
# =============================================================================
|
|
|
|
clean_load_policy:
|
|
directiva: "core/orchestration/directivas/legacy/DIRECTIVA-POLITICA-CARGA-LIMPIA.md"
|
|
principio: "DDL-First - Los archivos DDL son la fuente de verdad"
|
|
prohibiciones:
|
|
- "Ejecutar ALTER TABLE directo en BD sin actualizar DDL"
|
|
- "Crear carpeta migrations/"
|
|
- "Crear archivos fix-*.sql, patch-*.sql, hotfix-*.sql"
|
|
- "Dejar BD y DDL desincronizados"
|
|
|
|
paths:
|
|
db_ddl_path: "database/ddl"
|
|
db_scripts_path: "database/scripts"
|
|
db_seeds_path: "database/seeds"
|
|
db_name: "erp_generic"
|
|
|
|
comandos:
|
|
crear: "./scripts/create-database.sh"
|
|
recrear: "./scripts/reset-database.sh"
|
|
eliminar: "./scripts/drop-database.sh"
|
|
seeds_dev: "./scripts/load-seeds.sh dev"
|
|
seeds_prod: "./scripts/load-seeds.sh prod"
|
|
|
|
# =============================================================================
|
|
# ARCHIVOS DDL IMPLEMENTADOS
|
|
# =============================================================================
|
|
|
|
ddl_files:
|
|
orden_ejecucion:
|
|
1: "00-prerequisites.sql"
|
|
2: "01-auth.sql"
|
|
3: "01-auth-extensions.sql"
|
|
4: "01-auth-mfa-email-verification.sql"
|
|
5: "02-core.sql"
|
|
6: "02-core-extensions.sql"
|
|
7: "03-analytics.sql"
|
|
8: "04-financial.sql"
|
|
9: "05-inventory.sql"
|
|
10: "05-inventory-extensions.sql"
|
|
11: "06-purchase.sql"
|
|
12: "07-sales.sql"
|
|
13: "08-projects.sql"
|
|
14: "09-system.sql"
|
|
15: "09-system-extensions.sql"
|
|
16: "10-billing.sql"
|
|
17: "11-crm.sql"
|
|
18: "12-hr.sql"
|
|
19: "13-audit.sql"
|
|
20: "14-reports.sql"
|
|
|
|
extensiones_nuevas:
|
|
- archivo: "01-auth-extensions.sql"
|
|
fecha: "2025-12-08"
|
|
estado: IMPLEMENTADO
|
|
specs:
|
|
- SPEC-TWO-FACTOR-AUTHENTICATION.md
|
|
- SPEC-SEGURIDAD-API-KEYS-PERMISOS.md
|
|
- SPEC-OAUTH2-SOCIAL-LOGIN.md
|
|
tablas: 16
|
|
funciones: 6
|
|
vistas: 2
|
|
triggers: 3
|
|
detalle:
|
|
groups_herencia: [auth.groups, auth.group_implied, auth.user_groups]
|
|
acl: [auth.models, auth.model_access, auth.record_rules, auth.rule_groups, auth.model_fields, auth.field_permissions]
|
|
api_keys: [auth.api_keys]
|
|
two_factor: [auth.trusted_devices, auth.verification_codes, auth.mfa_audit_log]
|
|
oauth2: [auth.oauth_providers, auth.oauth_user_links, auth.oauth_states]
|
|
|
|
- archivo: "05-inventory-extensions.sql"
|
|
fecha: "2025-12-08"
|
|
estado: IMPLEMENTADO
|
|
specs:
|
|
- SPEC-VALORACION-INVENTARIO.md
|
|
- SPEC-TRAZABILIDAD-LOTES-SERIES.md
|
|
- SPEC-INVENTARIOS-CICLICOS.md
|
|
tablas: 10
|
|
funciones: 7
|
|
vistas: 3
|
|
triggers: 4
|
|
detalle:
|
|
valoracion_svl: [inventory.stock_valuation_layers, inventory.category_stock_accounts, inventory.valuation_settings]
|
|
lotes_series: [inventory.lots, inventory.move_line_consume_rel, inventory.removal_strategies]
|
|
conteos_ciclicos: [inventory.inventory_count_sessions, inventory.inventory_count_lines, inventory.abc_classification_rules, inventory.product_abc_classification]
|
|
|
|
- archivo: "01-auth-mfa-email-verification.sql"
|
|
fecha: "2026-01-07"
|
|
estado: IMPLEMENTADO
|
|
sprint: 5
|
|
specs:
|
|
- Sprint 5: MFA Implementation
|
|
- Sprint 5: Email Verification Flow
|
|
tablas: 2
|
|
detalle:
|
|
mfa: [auth.user_mfa_secrets]
|
|
email_verification: [auth.email_verification_tokens]
|
|
|
|
- archivo: "02-core-extensions.sql"
|
|
fecha: "2026-01-07"
|
|
estado: IMPLEMENTADO
|
|
sprint: 6
|
|
specs:
|
|
- BE-013: Currency Exchange Rates
|
|
tablas: 1
|
|
detalle:
|
|
currency: [core.currency_rates]
|
|
|
|
- archivo: "09-system-extensions.sql"
|
|
fecha: "2026-01-07"
|
|
estado: IMPLEMENTADO
|
|
sprint: 6
|
|
specs:
|
|
- BE-016: Settings Service 3-Level
|
|
tablas: 3
|
|
funciones: 1
|
|
triggers: 3
|
|
detalle:
|
|
settings: [system.system_settings, tenants.tenant_settings, auth.user_preferences]
|
|
caracteristicas:
|
|
- Cascada 3 niveles (User -> Tenant -> System)
|
|
- RLS Policies por tenant y usuario
|
|
- Seed data con configuraciones base
|
|
|
|
- archivo: "13-audit.sql"
|
|
fecha: "2026-01-07"
|
|
estado: IMPLEMENTADO
|
|
sprint: 7
|
|
specs:
|
|
- BE-017: Audit Trail
|
|
- BE-018: Access Logs
|
|
- BE-019: Security Events
|
|
tablas: 3
|
|
funciones: 3
|
|
detalle:
|
|
audit: [audit.audit_logs, audit.access_logs, audit.security_events]
|
|
enums:
|
|
- audit.audit_action (INSERT, UPDATE, DELETE)
|
|
- audit.access_event_type (LOGIN_SUCCESS, LOGIN_FAILED, LOGOUT, etc)
|
|
- audit.security_severity (LOW, MEDIUM, HIGH, CRITICAL)
|
|
caracteristicas:
|
|
- Funciones de limpieza automatica
|
|
- Indices optimizados para consultas
|
|
- Partial indexes para filtros comunes
|
|
|
|
- archivo: "14-reports.sql"
|
|
fecha: "2026-01-07"
|
|
estado: IMPLEMENTADO
|
|
sprint: 8
|
|
specs:
|
|
- RF-REPORT-001: Reportes Predefinidos
|
|
- RF-REPORT-002: Dashboards
|
|
- RF-REPORT-003: Report Builder
|
|
- RF-REPORT-004: Reportes Programados
|
|
- BE-021: DashboardsService
|
|
- BE-022: WidgetsService
|
|
- BE-023: ExportService
|
|
tablas: 12
|
|
funciones: 0
|
|
detalle:
|
|
reportes: [reports.report_definitions, reports.report_executions, reports.report_schedules, reports.report_recipients, reports.schedule_executions, reports.custom_reports]
|
|
dashboards: [reports.dashboards, reports.dashboard_widgets, reports.widget_queries]
|
|
data_model: [reports.data_model_entities, reports.data_model_fields, reports.data_model_relationships]
|
|
enums:
|
|
- reports.report_type (financial, accounting, tax, management, operational, custom)
|
|
- reports.execution_status (pending, running, completed, failed, cancelled)
|
|
- reports.export_format (pdf, xlsx, csv, json, html)
|
|
- reports.delivery_method (none, email, storage, webhook)
|
|
- reports.widget_type (15 tipos)
|
|
- reports.param_type (string, number, date, etc)
|
|
- reports.filter_operator (eq, ne, gt, gte, lt, lte, like, in, etc)
|
|
caracteristicas:
|
|
- RLS policies para aislamiento multi-tenant
|
|
- 15 tipos de widgets soportados
|
|
- Soporte para reportes programados con cron
|
|
- Data model para Report Builder visual
|
|
|
|
# =============================================================================
|
|
# SCHEMAS
|
|
# =============================================================================
|
|
|
|
schemas:
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_AUTH - Autenticacion
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_auth
|
|
description: Autenticacion, sesiones y tokens
|
|
module: MGN-001
|
|
rf: [RF-AUTH-001, RF-AUTH-002, RF-AUTH-003, RF-AUTH-004]
|
|
status: documented
|
|
ddl_file: apps/database/ddl/schemas/core_auth/
|
|
|
|
tables:
|
|
- name: users_auth
|
|
description: Credenciales de autenticacion
|
|
rf: RF-AUTH-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
|
|
- {name: user_id, type: UUID, fk: core_users.users.id}
|
|
- {name: email, type: VARCHAR(255), unique: true}
|
|
- {name: password_hash, type: VARCHAR(255)}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: email_verified_at, type: TIMESTAMPTZ}
|
|
- {name: last_login_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_users_auth_email, columns: [email], unique: true}
|
|
- {name: idx_users_auth_tenant, columns: [tenant_id]}
|
|
rls_policies:
|
|
- {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}
|
|
|
|
- name: sessions
|
|
description: Sesiones activas
|
|
rf: RF-AUTH-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: user_id, type: UUID, fk: users_auth.id}
|
|
- {name: token_hash, type: VARCHAR(255)}
|
|
- {name: ip_address, type: INET}
|
|
- {name: user_agent, type: TEXT}
|
|
- {name: expires_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_sessions_user, columns: [user_id]}
|
|
- {name: idx_sessions_expires, columns: [expires_at]}
|
|
|
|
- name: refresh_tokens
|
|
description: Tokens de refresco JWT
|
|
rf: RF-AUTH-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: user_id, type: UUID, fk: users_auth.id}
|
|
- {name: token_hash, type: VARCHAR(255)}
|
|
- {name: expires_at, type: TIMESTAMPTZ}
|
|
- {name: revoked_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_refresh_tokens_user, columns: [user_id]}
|
|
|
|
- name: password_resets
|
|
description: Tokens de recuperacion de password
|
|
rf: RF-AUTH-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: user_id, type: UUID, fk: users_auth.id}
|
|
- {name: token_hash, type: VARCHAR(255)}
|
|
- {name: expires_at, type: TIMESTAMPTZ}
|
|
- {name: used_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
- name: login_attempts
|
|
description: Intentos de login fallidos (brute force protection)
|
|
rf: RF-AUTH-004
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: email, type: VARCHAR(255)}
|
|
- {name: ip_address, type: INET}
|
|
- {name: attempted_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: success, type: BOOLEAN}
|
|
indexes:
|
|
- {name: idx_login_attempts_email, columns: [email]}
|
|
- {name: idx_login_attempts_ip, columns: [ip_address]}
|
|
|
|
- name: oauth_accounts
|
|
description: Cuentas OAuth vinculadas
|
|
rf: RF-AUTH-005
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: user_id, type: UUID, fk: users_auth.id}
|
|
- {name: provider, type: VARCHAR(50)}
|
|
- {name: provider_user_id, type: VARCHAR(255)}
|
|
- {name: access_token, type: TEXT}
|
|
- {name: refresh_token, type: TEXT}
|
|
- {name: expires_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_oauth_provider_user, columns: [provider, provider_user_id], unique: true}
|
|
|
|
functions:
|
|
- name: validate_password
|
|
params: [user_id UUID, password TEXT]
|
|
returns: BOOLEAN
|
|
rf: RF-AUTH-001
|
|
description: Valida password contra hash
|
|
|
|
- name: cleanup_expired_sessions
|
|
params: []
|
|
returns: INTEGER
|
|
rf: RF-AUTH-002
|
|
description: Elimina sesiones expiradas
|
|
|
|
triggers:
|
|
- name: trg_update_users_auth_timestamp
|
|
table: users_auth
|
|
event: BEFORE UPDATE
|
|
function: update_updated_at()
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_USERS - Usuarios
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_users
|
|
description: Gestion de usuarios y perfiles
|
|
module: MGN-002
|
|
rf: [RF-USERS-001, RF-USERS-002, RF-USERS-003]
|
|
status: documented
|
|
ddl_file: apps/database/ddl/schemas/core_users/
|
|
|
|
tables:
|
|
- name: users
|
|
description: Usuarios del sistema
|
|
rf: RF-USERS-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
|
|
- {name: first_name, type: VARCHAR(100)}
|
|
- {name: last_name, type: VARCHAR(100)}
|
|
- {name: display_name, type: VARCHAR(200)}
|
|
- {name: avatar_url, type: TEXT}
|
|
- {name: phone, type: VARCHAR(20)}
|
|
- {name: timezone, type: VARCHAR(50), default: "'UTC'"}
|
|
- {name: locale, type: VARCHAR(10), default: "'es-MX'"}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: deleted_at, type: TIMESTAMPTZ}
|
|
indexes:
|
|
- {name: idx_users_tenant, columns: [tenant_id]}
|
|
- {name: idx_users_name_search, columns: [first_name, last_name], type: gin_trgm}
|
|
rls_policies:
|
|
- {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}
|
|
|
|
- name: user_profiles
|
|
description: Perfil extendido de usuarios
|
|
rf: RF-USERS-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: user_id, type: UUID, fk: users.id, unique: true}
|
|
- {name: bio, type: TEXT}
|
|
- {name: company, type: VARCHAR(200)}
|
|
- {name: job_title, type: VARCHAR(100)}
|
|
- {name: address, type: JSONB}
|
|
- {name: social_links, type: JSONB}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
- name: user_preferences
|
|
description: Preferencias de usuario
|
|
rf: RF-USERS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: user_id, type: UUID, fk: users.id, unique: true}
|
|
- {name: theme, type: VARCHAR(20), default: "'light'"}
|
|
- {name: notifications_email, type: BOOLEAN, default: true}
|
|
- {name: notifications_push, type: BOOLEAN, default: true}
|
|
- {name: language, type: VARCHAR(10), default: "'es'"}
|
|
- {name: date_format, type: VARCHAR(20), default: "'DD/MM/YYYY'"}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_RBAC - Roles y Permisos
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_rbac
|
|
description: Control de acceso basado en roles
|
|
module: MGN-003
|
|
rf: [RF-RBAC-001, RF-RBAC-002, RF-RBAC-003]
|
|
status: documented
|
|
ddl_file: apps/database/ddl/schemas/core_rbac/
|
|
|
|
tables:
|
|
- name: roles
|
|
description: Definicion de roles
|
|
rf: RF-RBAC-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
|
|
- {name: name, type: VARCHAR(100)}
|
|
- {name: slug, type: VARCHAR(100)}
|
|
- {name: description, type: TEXT}
|
|
- {name: is_system, type: BOOLEAN, default: false}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_roles_tenant_slug, columns: [tenant_id, slug], unique: true}
|
|
rls_policies:
|
|
- {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}
|
|
|
|
- name: permissions
|
|
description: Definicion de permisos
|
|
rf: RF-RBAC-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: module, type: VARCHAR(50)}
|
|
- {name: action, type: VARCHAR(50)}
|
|
- {name: resource, type: VARCHAR(100)}
|
|
- {name: description, type: TEXT}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_permissions_module_action, columns: [module, action, resource], unique: true}
|
|
|
|
- name: role_permissions
|
|
description: Asignacion permisos a roles
|
|
rf: RF-RBAC-002
|
|
columns:
|
|
- {name: role_id, type: UUID, fk: roles.id}
|
|
- {name: permission_id, type: UUID, fk: permissions.id}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
constraints:
|
|
- {type: pk, columns: [role_id, permission_id]}
|
|
|
|
- name: user_roles
|
|
description: Asignacion roles a usuarios
|
|
rf: RF-RBAC-003
|
|
columns:
|
|
- {name: user_id, type: UUID, fk: core_users.users.id}
|
|
- {name: role_id, type: UUID, fk: roles.id}
|
|
- {name: assigned_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: assigned_by, type: UUID, fk: core_users.users.id}
|
|
constraints:
|
|
- {type: pk, columns: [user_id, role_id]}
|
|
|
|
functions:
|
|
- name: user_has_permission
|
|
params: [p_user_id UUID, p_module VARCHAR, p_action VARCHAR, p_resource VARCHAR]
|
|
returns: BOOLEAN
|
|
rf: RF-RBAC-002
|
|
description: Verifica si usuario tiene permiso especifico
|
|
|
|
- name: get_user_permissions
|
|
params: [p_user_id UUID]
|
|
returns: TABLE
|
|
rf: RF-RBAC-002
|
|
description: Retorna todos los permisos del usuario
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_TENANTS - Multi-tenancy
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_tenants
|
|
description: Gestion multi-tenant
|
|
module: MGN-004
|
|
rf: [RF-TENANTS-001, RF-TENANTS-002, RF-TENANTS-003]
|
|
status: documented
|
|
ddl_file: apps/database/ddl/schemas/core_tenants/
|
|
|
|
tables:
|
|
- name: tenants
|
|
description: Organizaciones/empresas
|
|
rf: RF-TENANTS-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: name, type: VARCHAR(200)}
|
|
- {name: slug, type: VARCHAR(100), unique: true}
|
|
- {name: domain, type: VARCHAR(255)}
|
|
- {name: logo_url, type: TEXT}
|
|
- {name: settings, type: JSONB, default: "'{}'"}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: trial_ends_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: deleted_at, type: TIMESTAMPTZ}
|
|
indexes:
|
|
- {name: idx_tenants_slug, columns: [slug], unique: true}
|
|
- {name: idx_tenants_domain, columns: [domain]}
|
|
|
|
- name: tenant_settings
|
|
description: Configuracion por tenant
|
|
rf: RF-TENANTS-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: tenants.id, unique: true}
|
|
- {name: currency, type: VARCHAR(3), default: "'MXN'"}
|
|
- {name: timezone, type: VARCHAR(50), default: "'America/Mexico_City'"}
|
|
- {name: date_format, type: VARCHAR(20), default: "'DD/MM/YYYY'"}
|
|
- {name: fiscal_config, type: JSONB}
|
|
- {name: modules_enabled, type: JSONB}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
- name: plans
|
|
description: Planes de suscripcion
|
|
rf: RF-TENANTS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: name, type: VARCHAR(100)}
|
|
- {name: slug, type: VARCHAR(50), unique: true}
|
|
- {name: description, type: TEXT}
|
|
- {name: price_monthly, type: DECIMAL(10,2)}
|
|
- {name: price_yearly, type: DECIMAL(10,2)}
|
|
- {name: features, type: JSONB}
|
|
- {name: limits, type: JSONB}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
- name: subscriptions
|
|
description: Suscripciones de tenants
|
|
rf: RF-TENANTS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: tenants.id}
|
|
- {name: plan_id, type: UUID, fk: plans.id}
|
|
- {name: status, type: VARCHAR(20)}
|
|
- {name: starts_at, type: TIMESTAMPTZ}
|
|
- {name: ends_at, type: TIMESTAMPTZ}
|
|
- {name: canceled_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_subscriptions_tenant, columns: [tenant_id]}
|
|
- {name: idx_subscriptions_status, columns: [status]}
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_CATALOGS - Catalogos Maestros
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_catalogs
|
|
description: Catalogos maestros compartidos
|
|
module: MGN-005
|
|
rf: [RF-CATALOGS-001, RF-CATALOGS-002, RF-CATALOGS-003]
|
|
status: planned
|
|
ddl_file: apps/database/ddl/schemas/core_catalogs/
|
|
|
|
tables:
|
|
- name: partners
|
|
description: Clientes, proveedores, contactos
|
|
rf: RF-CATALOGS-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
|
|
- {name: name, type: VARCHAR(200)}
|
|
- {name: tax_id, type: VARCHAR(20)}
|
|
- {name: email, type: VARCHAR(255)}
|
|
- {name: phone, type: VARCHAR(20)}
|
|
- {name: is_customer, type: BOOLEAN, default: false}
|
|
- {name: is_supplier, type: BOOLEAN, default: false}
|
|
- {name: is_contact, type: BOOLEAN, default: false}
|
|
- {name: credit_limit, type: DECIMAL(15,2), default: 0}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: deleted_at, type: TIMESTAMPTZ}
|
|
indexes:
|
|
- {name: idx_partners_tenant_tax, columns: [tenant_id, tax_id], unique: true}
|
|
- {name: idx_partners_name_search, columns: [name], type: gin_trgm}
|
|
rls_policies:
|
|
- {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}
|
|
|
|
- name: products
|
|
description: Productos y servicios
|
|
rf: RF-CATALOGS-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
|
|
- {name: sku, type: VARCHAR(50)}
|
|
- {name: name, type: VARCHAR(200)}
|
|
- {name: description, type: TEXT}
|
|
- {name: category_id, type: UUID, fk: categories.id}
|
|
- {name: unit_id, type: UUID, fk: units.id}
|
|
- {name: price, type: DECIMAL(15,4)}
|
|
- {name: cost, type: DECIMAL(15,4)}
|
|
- {name: is_service, type: BOOLEAN, default: false}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_products_tenant_sku, columns: [tenant_id, sku], unique: true}
|
|
|
|
- name: categories
|
|
description: Categorias jerarquicas
|
|
rf: RF-CATALOGS-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
|
|
- {name: parent_id, type: UUID, fk: categories.id}
|
|
- {name: name, type: VARCHAR(100)}
|
|
- {name: slug, type: VARCHAR(100)}
|
|
- {name: path, type: LTREE}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
- name: units
|
|
description: Unidades de medida
|
|
rf: RF-CATALOGS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: code, type: VARCHAR(10)}
|
|
- {name: name, type: VARCHAR(50)}
|
|
- {name: category, type: VARCHAR(20)}
|
|
- {name: is_base, type: BOOLEAN, default: false}
|
|
- {name: conversion_factor, type: DECIMAL(15,6), default: 1}
|
|
|
|
- name: currencies
|
|
description: Monedas
|
|
rf: RF-CATALOGS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: code, type: VARCHAR(3)}
|
|
- {name: name, type: VARCHAR(50)}
|
|
- {name: symbol, type: VARCHAR(5)}
|
|
- {name: decimal_places, type: INTEGER, default: 2}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
|
|
- name: countries
|
|
description: Paises
|
|
rf: RF-CATALOGS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: code, type: VARCHAR(3)}
|
|
- {name: name, type: VARCHAR(100)}
|
|
- {name: phone_code, type: VARCHAR(5)}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
|
|
- name: states
|
|
description: Estados/Provincias
|
|
rf: RF-CATALOGS-003
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: country_id, type: UUID, fk: countries.id}
|
|
- {name: code, type: VARCHAR(10)}
|
|
- {name: name, type: VARCHAR(100)}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_AUDIT - Auditoria
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_audit
|
|
description: Logs de auditoria
|
|
module: MGN-019
|
|
rf: [RF-AUDIT-001, RF-AUDIT-002]
|
|
status: planned
|
|
ddl_file: apps/database/ddl/schemas/core_audit/
|
|
|
|
tables:
|
|
- name: audit_logs
|
|
description: Log de cambios en entidades
|
|
rf: RF-AUDIT-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID}
|
|
- {name: user_id, type: UUID}
|
|
- {name: entity_type, type: VARCHAR(100)}
|
|
- {name: entity_id, type: UUID}
|
|
- {name: action, type: VARCHAR(20)}
|
|
- {name: old_values, type: JSONB}
|
|
- {name: new_values, type: JSONB}
|
|
- {name: ip_address, type: INET}
|
|
- {name: user_agent, type: TEXT}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_audit_tenant_entity, columns: [tenant_id, entity_type, entity_id]}
|
|
- {name: idx_audit_created, columns: [created_at]}
|
|
partitioning:
|
|
type: RANGE
|
|
column: created_at
|
|
interval: MONTHLY
|
|
|
|
- name: activity_logs
|
|
description: Log de actividad de usuarios
|
|
rf: RF-AUDIT-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID}
|
|
- {name: user_id, type: UUID}
|
|
- {name: action, type: VARCHAR(100)}
|
|
- {name: resource, type: VARCHAR(100)}
|
|
- {name: details, type: JSONB}
|
|
- {name: ip_address, type: INET}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CORE_NOTIFICATIONS - Notificaciones
|
|
# ---------------------------------------------------------------------------
|
|
- name: core_notifications
|
|
description: Sistema de notificaciones
|
|
module: MGN-008
|
|
rf: [RF-NOTIF-001, RF-NOTIF-002]
|
|
status: planned
|
|
ddl_file: apps/database/ddl/schemas/core_notifications/
|
|
|
|
tables:
|
|
- name: notifications
|
|
description: Notificaciones de usuarios
|
|
rf: RF-NOTIF-001
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID}
|
|
- {name: user_id, type: UUID}
|
|
- {name: type, type: VARCHAR(50)}
|
|
- {name: title, type: VARCHAR(200)}
|
|
- {name: message, type: TEXT}
|
|
- {name: data, type: JSONB}
|
|
- {name: read_at, type: TIMESTAMPTZ}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
indexes:
|
|
- {name: idx_notifications_user_read, columns: [user_id, read_at]}
|
|
rls_policies:
|
|
- {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}
|
|
|
|
- name: notification_templates
|
|
description: Templates de notificaciones
|
|
rf: RF-NOTIF-002
|
|
columns:
|
|
- {name: id, type: UUID, pk: true}
|
|
- {name: tenant_id, type: UUID}
|
|
- {name: code, type: VARCHAR(50)}
|
|
- {name: name, type: VARCHAR(100)}
|
|
- {name: subject, type: VARCHAR(200)}
|
|
- {name: body, type: TEXT}
|
|
- {name: channels, type: JSONB}
|
|
- {name: is_active, type: BOOLEAN, default: true}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
# =============================================================================
|
|
# CONVENCIONES Y ESTANDARES
|
|
# =============================================================================
|
|
|
|
conventions:
|
|
naming:
|
|
schemas: "core_{nombre}"
|
|
tables: snake_case_plural
|
|
columns: snake_case
|
|
foreign_keys: "fk_{tabla_origen}_{tabla_destino}"
|
|
indexes: "idx_{tabla}_{columnas}"
|
|
primary_keys: "{tabla}_pkey"
|
|
triggers: "trg_{tabla}_{evento}"
|
|
functions: snake_case
|
|
|
|
mandatory_columns:
|
|
- {name: id, type: UUID, description: "Primary key"}
|
|
- {name: tenant_id, type: UUID, description: "Multi-tenant isolation (si aplica)"}
|
|
- {name: created_at, type: TIMESTAMPTZ, default: NOW()}
|
|
- {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
|
|
|
|
soft_delete:
|
|
column: deleted_at
|
|
type: TIMESTAMPTZ
|
|
|
|
rls:
|
|
pattern: tenant_isolation
|
|
context_variable: app.current_tenant_id
|
|
|
|
mandatory_indexes:
|
|
- "idx_{tabla}_tenant_id"
|
|
- "idx_{tabla}_created_at"
|
|
|
|
# =============================================================================
|
|
# HISTORIAL
|
|
# =============================================================================
|
|
|
|
history:
|
|
- date: "2026-01-07"
|
|
action: "Implementacion DDL Sprint 8 - Reports & Dashboards"
|
|
author: Database-Agent
|
|
changes:
|
|
- "Creado 14-reports.sql (12 tablas: reportes, dashboards, data model)"
|
|
- "Actualizado create-database.sh con 20 DDL files"
|
|
- "Validado recreacion completa de BD con --force"
|
|
sprints_cubiertos:
|
|
- Sprint 8: Reports & Dashboards
|
|
specs_cubiertas:
|
|
- RF-REPORT-001: Reportes Predefinidos
|
|
- RF-REPORT-002: Dashboards
|
|
- RF-REPORT-003: Report Builder
|
|
- RF-REPORT-004: Reportes Programados
|
|
tablas_nuevas: 12
|
|
enums_nuevos: 7
|
|
rls_policies_nuevas: 7
|
|
|
|
- date: "2026-01-07"
|
|
action: "Implementacion DDL Sprint 5-7"
|
|
author: Backend-Agent
|
|
changes:
|
|
- "Creado 01-auth-mfa-email-verification.sql (2 tablas: MFA + Email Verification)"
|
|
- "Creado 02-core-extensions.sql (1 tabla: currency_rates)"
|
|
- "Creado 09-system-extensions.sql (3 tablas: settings 3-level cascade)"
|
|
- "Creado 13-audit.sql (3 tablas: audit_logs, access_logs, security_events)"
|
|
- "Creado recreate-database.sh (wrapper para recreacion)"
|
|
- "Actualizado create-database.sh con 19 DDL files"
|
|
- "Validado recreacion completa de BD"
|
|
sprints_cubiertos:
|
|
- Sprint 5: MFA + Email Verification
|
|
- Sprint 6: Catalogs & Settings
|
|
- Sprint 7: Audit & Notifications
|
|
tablas_nuevas: 9
|
|
funciones_nuevas: 4
|
|
triggers_nuevos: 3
|
|
|
|
- date: "2025-12-08"
|
|
action: "Implementacion DDL extensiones Auth e Inventory"
|
|
author: Database-Agent
|
|
changes:
|
|
- "Creado 01-auth-extensions.sql (16 tablas, 6 funciones, 2 vistas)"
|
|
- "Creado 05-inventory-extensions.sql (10 tablas, 7 funciones, 3 vistas)"
|
|
- "Actualizado create-database.sh con orden de ejecucion completo"
|
|
- "Agregada seccion clean_load_policy y ddl_files"
|
|
specs_cubiertas:
|
|
- SPEC-TWO-FACTOR-AUTHENTICATION.md
|
|
- SPEC-SEGURIDAD-API-KEYS-PERMISOS.md
|
|
- SPEC-OAUTH2-SOCIAL-LOGIN.md
|
|
- SPEC-VALORACION-INVENTARIO.md
|
|
- SPEC-TRAZABILIDAD-LOTES-SERIES.md
|
|
- SPEC-INVENTARIOS-CICLICOS.md
|
|
|
|
- date: "2025-12-05"
|
|
action: "Reestructuracion completa siguiendo filosofia GAMILIT"
|
|
author: Requirements-Analyst
|
|
changes:
|
|
- "Agregada trazabilidad RF por tabla"
|
|
- "Documentadas columnas con tipos"
|
|
- "Agregados indices y RLS policies"
|
|
- "Documentadas funciones y triggers"
|