b9098ca91c
Comprehensive analysis of 101 DDL tables across 11 schemas: - Phase 1-2: Schema validation, 37 gaps cataloged (3 resolved) - Phase 3: Integrity audit (80 FKs, 89 CHECKs, 17 issues: 2 CRIT/5 HIGH) - Phase 4: DDL-Backend mapping (84% interfaces, 75% services, 61% controllers) - Phase 5: Documentation purge catalog (201 files analyzed) - Phase 6: Remediation plan (4 sprints, 204h) Key finding: Backend uses raw SQL + pg Pool (NOT TypeORM). 13 deliverables + updated inventories to v2.0.0. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
481 lines
29 KiB
YAML
481 lines
29 KiB
YAML
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# DATABASE_INVENTORY.yml - Trading Platform
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
version: "2.0.0"
|
|
fecha_actualizacion: "2026-02-05"
|
|
ultima_sincronizacion: "2026-02-05T12:00:00Z"
|
|
proyecto: "trading-platform"
|
|
actualizado_por: "TASK-2026-02-05-ANALISIS-VALIDACION-MODELADO-BD"
|
|
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# RESUMEN (ACTUALIZADO - Validacion Integral 2026-02-05)
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
resumen:
|
|
total_schemas: 11
|
|
total_tablas: 101 # auth=12, feature_flags=3 (multi-CREATE en 1 archivo), total 101 tablas DDL
|
|
total_enums: 50 # Corregido de 15 -> 50+ (todos los schemas)
|
|
total_funciones: 17
|
|
total_triggers: 39
|
|
total_archivos_ddl: 123
|
|
ubicacion_ddl: "apps/database/ddl/schemas/"
|
|
motor: "PostgreSQL 16"
|
|
extensions:
|
|
- "uuid-ossp"
|
|
- "pgcrypto"
|
|
- "citext"
|
|
- "unaccent"
|
|
- "pg_trgm"
|
|
- "vector (pgvector)"
|
|
features:
|
|
- "Particiones temporales"
|
|
- "UUIDs como PKs"
|
|
- "JSONB para datos flexibles"
|
|
- "Triggers de auditoria"
|
|
- "pgvector para embeddings ML"
|
|
- "Indices parciales para queries frecuentes"
|
|
- "GIN indexes para JSONB"
|
|
- "CITEXT para emails case-insensitive"
|
|
|
|
# Delta vs version anterior (1.2.0):
|
|
delta_v1_2_0:
|
|
tablas_agregadas: 19
|
|
schemas_documentados_nuevos: 1 # feature_flags
|
|
enums_corregidos: 3 # transaction_type, risk_profile, timeframe
|
|
errores_fk_corregidos: 1 # price_alerts
|
|
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# TIPOS GLOBALES (public schema)
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
global_types:
|
|
- name: "public.trading_timeframe"
|
|
type: "ENUM"
|
|
values: ["1m", "5m", "15m", "30m", "1h", "4h", "1d", "1w", "1M"]
|
|
nota: "Tipo unificado - reemplaza trading.timeframe y market_data.timeframe (deprecados)"
|
|
|
|
global_functions:
|
|
- name: "public.update_updated_at()"
|
|
type: "TRIGGER FUNCTION"
|
|
descripcion: "Actualiza campo updated_at automaticamente"
|
|
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# SCHEMAS
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
schemas:
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# AUTH - Autenticacion y Usuarios
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
auth:
|
|
descripcion: "Autenticacion, sesiones, OAuth, notificaciones"
|
|
tablas: 12 # Corregido: 12 tablas confirmadas via DDL
|
|
critico: true
|
|
tablas_lista:
|
|
- users # Tabla central de usuarios
|
|
- user_profiles # Perfiles extendidos
|
|
- oauth_accounts # Cuentas OAuth (Google, Facebook, Apple, GitHub)
|
|
- sessions # Sesiones activas con refresh tokens
|
|
- email_verifications # Tokens de verificacion email
|
|
- phone_verifications # Codigos de verificacion telefono
|
|
- password_reset_tokens # Tokens de recuperacion de contrasena
|
|
- auth_logs # Log de eventos de autenticacion
|
|
- login_attempts # Tracking de intentos de login
|
|
- rate_limiting_config # Configuracion de rate limiting dinamico
|
|
- notifications # NUEVO - Notificaciones del sistema
|
|
- user_push_tokens # NUEVO - Tokens push (FCM/APNs)
|
|
enums:
|
|
- user_status: ["pending_verification", "active", "suspended", "deactivated", "banned"]
|
|
- user_role: ["user", "trader", "analyst", "admin", "super_admin"]
|
|
- oauth_provider: ["google", "facebook", "apple", "github", "microsoft", "twitter"]
|
|
- phone_channel: ["sms", "whatsapp"]
|
|
- auth_event_type: ["login", "logout", "register", "password_change", "password_reset_request", "password_reset_complete", "email_verification", "phone_verification", "mfa_enabled", "mfa_disabled", "session_expired", "account_suspended", "account_reactivated", "failed_login", "oauth_linked", "oauth_unlinked"]
|
|
- mfa_method: ["none", "totp", "sms", "email"]
|
|
- notification_type: ["system", "trading", "investment", "education", "payment", "security", "marketing"]
|
|
funciones:
|
|
- "auth.log_auth_event()"
|
|
- "auth.cleanup_expired_sessions()"
|
|
- "auth.create_user_profile_trigger()"
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# TRADING - Operaciones de Trading
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
trading:
|
|
descripcion: "Orders, trades, signals, bots, alerts, herramientas dibujo"
|
|
tablas: 13 # Corregido de 11 -> 13
|
|
critico: true
|
|
tablas_lista:
|
|
- symbols # Catalogo de instrumentos financieros
|
|
- watchlists # Listas de vigilancia del usuario
|
|
- watchlist_items # Items en watchlists
|
|
- bots # Bots de trading (paper, live, backtest)
|
|
- orders # Ordenes de trading
|
|
- positions # Posiciones abiertas/cerradas
|
|
- trades # Historial de ejecuciones
|
|
- signals # Senales ML (interfaz con ml schema)
|
|
- trading_metrics # Metricas de rendimiento
|
|
- paper_balances # Balances paper trading
|
|
- price_alerts # Alertas de precio (Sprint 3)
|
|
- drawing_tools # NUEVO - Herramientas de dibujo en charts
|
|
- drawing_templates # NUEVO - Templates de dibujo reutilizables
|
|
enums:
|
|
- order_type: ["market", "limit", "stop", "stop_limit", "trailing_stop"]
|
|
- order_status: ["pending", "open", "partially_filled", "filled", "cancelled", "rejected", "expired"]
|
|
- order_side: ["buy", "sell"]
|
|
- position_status: ["open", "closed", "liquidated"]
|
|
- signal_type: ["entry_long", "entry_short", "exit_long", "exit_short", "hold"]
|
|
- confidence_level: ["low", "medium", "high", "very_high"]
|
|
- timeframe: ["1m", "5m", "15m", "30m", "1h", "4h", "1d", "1w", "1M"] # DEPRECADO -> usar public.trading_timeframe
|
|
- bot_type: ["paper", "live", "backtest"]
|
|
- bot_status: ["active", "paused", "stopped", "error"]
|
|
- drawing_tool_type: ["trend_line", "horizontal_line", "vertical_line", "ray", "extended_line", "parallel_channel", "fibonacci_retracement", "fibonacci_extension", "rectangle", "ellipse", "triangle", "arrow", "text", "price_range", "date_range", "order_block", "fair_value_gap", "liquidity_level"]
|
|
- alert_type: ["price_above", "price_below", "percent_change", "volume_spike"]
|
|
- alert_status: ["active", "triggered", "expired", "cancelled"]
|
|
funciones:
|
|
- "trading.calculate_position_pnl()"
|
|
- "trading.update_bot_stats()"
|
|
- "trading.initialize_paper_balance()"
|
|
- "trading.create_default_watchlist()"
|
|
- "trading.update_price_alerts_timestamp()"
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# EDUCATION - Plataforma Educativa
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
education:
|
|
descripcion: "Cursos, quizzes, gamificacion, videos, instructores, tags, reviews"
|
|
tablas: 19 # Corregido de 12 -> 19
|
|
critico: false
|
|
tablas_lista:
|
|
- categories # Categorias de cursos
|
|
- courses # Cursos educativos
|
|
- modules # Modulos dentro de cursos
|
|
- lessons # Lecciones individuales
|
|
- enrollments # Inscripciones de estudiantes
|
|
- progress # NUEVO - Progreso por leccion
|
|
- quizzes # Evaluaciones
|
|
- quiz_questions # Preguntas de quiz
|
|
- quiz_attempts # Intentos de quiz
|
|
- certificates # Certificados de completitud
|
|
- user_achievements # Badges y logros
|
|
- user_gamification_profile # Perfil de gamificacion (XP, nivel)
|
|
- user_activity_log # NUEVO - Log de actividad usuario
|
|
- course_reviews # NUEVO - Reviews de cursos (1-5 estrellas)
|
|
- videos # Videos de lecciones
|
|
- review_helpful_votes # NUEVO - Votos "helpful" en reviews
|
|
- instructors # NUEVO - Perfiles de instructores
|
|
- course_tags # NUEVO - Tags para cursos
|
|
- course_tag_assignments # NUEVO - Asignacion M:N curso-tag
|
|
enums:
|
|
- difficulty_level: ["beginner", "intermediate", "advanced", "expert"]
|
|
- course_status: ["draft", "published", "archived"]
|
|
- enrollment_status: ["active", "completed", "expired", "cancelled"]
|
|
- lesson_content_type: ["video", "article", "interactive", "quiz"]
|
|
- question_type: ["multiple_choice", "true_false", "multiple_select", "fill_blank", "code_challenge"]
|
|
- achievement_type: ["course_completion", "quiz_perfect_score", "streak_milestone", "level_up", "special_event"]
|
|
funciones:
|
|
- "education.update_enrollment_progress()"
|
|
- "education.auto_complete_enrollment()"
|
|
- "education.generate_certificate()"
|
|
- "education.update_course_stats()"
|
|
- "education.update_enrollment_count()"
|
|
- "education.update_gamification_profile()"
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# FINANCIAL - Wallets, Pagos, Subscripciones
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
financial:
|
|
descripcion: "Wallets unificados, pagos, subscripciones, reembolsos, tipos de cambio"
|
|
tablas: 11 # Corregido de 9 -> 11
|
|
critico: true
|
|
tablas_lista:
|
|
- wallets # Sistema unificado de wallets (SSOT)
|
|
- wallet_transactions # Historial de transacciones
|
|
- subscriptions # Suscripciones Stripe
|
|
- invoices # Facturas
|
|
- payments # Pagos
|
|
- wallet_audit_log # Log de auditoria de wallets
|
|
- wallet_limits # Limites operacionales
|
|
- customers # Clientes Stripe
|
|
- payment_methods # Metodos de pago guardados
|
|
- currency_exchange_rates # NUEVO - Tipos de cambio
|
|
- refunds # NUEVO - Reembolsos
|
|
enums:
|
|
- wallet_type: ["trading", "investment", "earnings", "referral"]
|
|
- wallet_status: ["active", "frozen", "closed"]
|
|
- transaction_type: ["deposit", "withdrawal", "transfer_in", "transfer_out", "fee", "refund", "earning", "distribution", "bonus"] # DEPRECADO -> renombrar a wallet_transaction_type
|
|
- transaction_status: ["pending", "processing", "completed", "failed", "cancelled", "reversed"]
|
|
- subscription_plan: ["free", "basic", "pro", "premium", "enterprise"]
|
|
- subscription_status: ["active", "past_due", "cancelled", "incomplete", "trialing", "unpaid", "paused"]
|
|
- currency_code: ["USD", "MXN", "EUR"]
|
|
- payment_method: ["card", "bank_transfer", "wire", "crypto", "paypal", "stripe"]
|
|
- payment_status: ["pending", "processing", "succeeded", "failed", "cancelled", "refunded"]
|
|
- invoice_type: ["subscription", "one_time", "usage"]
|
|
- invoice_status: ["draft", "open", "paid", "void", "uncollectible"]
|
|
funciones:
|
|
- "financial.update_wallet_balance()"
|
|
- "financial.process_transaction()"
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# INVESTMENT - Productos PAMM, Cuentas, Distribuciones
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
investment:
|
|
descripcion: "Productos de inversion PAMM, cuentas, distribuciones, ejecuciones agentes"
|
|
tablas: 10 # Corregido de 8 -> 10
|
|
critico: false
|
|
tablas_lista:
|
|
- products # Productos PAMM (Atlas, Orion, Nova)
|
|
- risk_questionnaire # NUEVO en inventario - Cuestionario de riesgo
|
|
- accounts # Cuentas individuales PAMM
|
|
- distributions # Distribuciones de ganancias
|
|
- transactions # Transacciones de inversion
|
|
- withdrawal_requests # Solicitudes de retiro
|
|
- daily_performance # Rendimiento diario
|
|
- distribution_history # Historial de distribuciones (Sprint 3)
|
|
- distribution_runs # Lotes de distribucion (Sprint 3)
|
|
- agent_executions # NUEVO - Ejecuciones de agentes de trading
|
|
enums:
|
|
- trading_agent: ["atlas", "orion", "nova"]
|
|
- risk_profile: ["conservative", "moderate", "aggressive"] # DUPLICADO con portfolio -> consolidar en public
|
|
- account_status: ["pending_kyc", "active", "suspended", "closed"]
|
|
- distribution_frequency: ["monthly", "quarterly"]
|
|
- transaction_type: ["deposit", "withdrawal", "distribution"] # DEPRECADO -> renombrar a investment_transaction_type
|
|
- transaction_status: ["pending", "processing", "completed", "failed", "cancelled"]
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# ML - Machine Learning, Predicciones
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
ml:
|
|
descripcion: "Modelos ML, predicciones, backtesting, senales LLM, overlays"
|
|
tablas: 12 # Corregido de 10 -> 12
|
|
critico: true
|
|
tablas_lista:
|
|
- models # Registro de modelos ML
|
|
- model_versions # Versionado de modelos
|
|
- predictions # Predicciones generadas
|
|
- prediction_outcomes # Resultados de predicciones
|
|
- backtest_runs # Corridas de backtesting
|
|
- feature_store # Cache de features ML
|
|
- llm_predictions # Predicciones generadas por LLM
|
|
- llm_decisions # Decisiones estrategicas LLM
|
|
- llm_prediction_outcomes # Resultados de predicciones LLM
|
|
- risk_events # Eventos de riesgo detectados
|
|
- llm_signals # NUEVO - Senales estrategicas LLM
|
|
- prediction_overlays # NUEVO - Datos de overlay para charts
|
|
enums:
|
|
- model_type: ["classification", "regression", "time_series", "clustering", "anomaly_detection", "reinforcement_learning"]
|
|
- framework: ["sklearn", "tensorflow", "pytorch", "xgboost", "lightgbm", "prophet", "custom"]
|
|
- model_status: ["development", "testing", "staging", "production", "deprecated", "archived"]
|
|
- prediction_type: ["price_direction", "price_target", "volatility", "trend", "signal", "risk_score"]
|
|
- prediction_result: ["buy", "sell", "hold", "up", "down", "neutral"]
|
|
- outcome_status: ["pending", "correct", "incorrect", "partially_correct", "expired"]
|
|
funciones:
|
|
- "ml.calculate_prediction_accuracy()"
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# LLM - Agente de IA, Conversaciones
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
llm:
|
|
descripcion: "Conversaciones LLM, mensajes, preferencias, memoria, embeddings"
|
|
tablas: 5 # Corregido de 4 -> 5
|
|
critico: false
|
|
tablas_lista:
|
|
- conversations # Sesiones de chat con IA
|
|
- messages # Mensajes individuales
|
|
- user_preferences # NUEVO en inventario - Preferencias LLM del usuario
|
|
- user_memory # Memoria contextual del usuario
|
|
- embeddings # Embeddings vectoriales (pgvector)
|
|
enums:
|
|
- message_role: ["user", "assistant", "system", "tool"]
|
|
- conversation_status: ["active", "archived", "deleted"]
|
|
- conversation_type: ["general", "trading_advice", "education", "market_analysis", "support", "onboarding"]
|
|
- communication_tone: ["casual", "professional", "technical"]
|
|
- verbosity_level: ["brief", "normal", "detailed"]
|
|
- memory_type: ["fact", "preference", "context", "goal", "constraint"]
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# AUDIT - Auditoria y Compliance
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
audit:
|
|
descripcion: "Auditoria, logs de seguridad, compliance, tracking de API"
|
|
tablas: 7
|
|
critico: false
|
|
tablas_lista:
|
|
- audit_logs # Log general de auditoria
|
|
- security_events # Eventos de seguridad
|
|
- system_events # Eventos del sistema
|
|
- trading_audit # Auditoria de operaciones trading
|
|
- api_request_logs # Log de peticiones API
|
|
- data_access_logs # Log de acceso a datos
|
|
- compliance_logs # Logs de compliance regulatorio
|
|
enums:
|
|
- audit_event_type: ["create", "read", "update", "delete", "login", "logout", "permission_change", "config_change", "export", "import"]
|
|
- event_severity: ["debug", "info", "warning", "error", "critical"]
|
|
- security_event_category: ["authentication", "authorization", "data_access", "configuration", "suspicious_activity", "compliance"]
|
|
- event_status: ["success", "failure", "blocked", "pending_review"]
|
|
- resource_type: ["user", "account", "transaction", "order", "position", "bot", "subscription", "payment", "course", "model", "system_config"]
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# PORTFOLIO - Gestion de Portafolio
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
portfolio:
|
|
descripcion: "Portafolios, asignaciones, metas, rebalanceo, snapshots"
|
|
tablas: 5 # Corregido de 4 -> 5
|
|
critico: false
|
|
tablas_lista:
|
|
- portfolios # Portafolios de inversion del usuario
|
|
- portfolio_allocations # Asignaciones de activos
|
|
- portfolio_goals # Metas financieras
|
|
- rebalance_history # Historial de rebalanceo
|
|
- portfolio_snapshots # NUEVO - Snapshots historicos del portafolio
|
|
enums:
|
|
- risk_profile: ["conservative", "moderate", "aggressive"] # DUPLICADO con investment -> consolidar en public
|
|
- goal_status: ["active", "completed", "cancelled"]
|
|
- rebalance_action: ["buy", "sell", "hold"]
|
|
- allocation_status: ["active", "pending", "closed"]
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# MARKET DATA - Datos de Mercado OHLCV
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
market_data:
|
|
descripcion: "Datos de mercado OHLCV, tickers, staging ETL"
|
|
tablas: 4 # Corregido de 3 -> 4
|
|
critico: true
|
|
tablas_lista:
|
|
- tickers # Catalogo de simbolos/tickers
|
|
- ohlcv_5m # Velas OHLCV 5 minutos
|
|
- ohlcv_15m # Velas OHLCV 15 minutos
|
|
- staging # NUEVO - Tabla staging para ingesta ETL
|
|
enums:
|
|
- timeframe: ["1m", "5m", "15m", "30m", "1h", "4h", "1d", "1w"] # DEPRECADO - falta '1M', migrar a public.trading_timeframe
|
|
funciones:
|
|
- "market_data.aggregate_15m()"
|
|
notas:
|
|
- "CONFLICTO: tickers duplica funcionalidad de trading.symbols (DUP-1)"
|
|
- "DEPRECADO: timeframe enum falta '1M', migrar a public.trading_timeframe"
|
|
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
# FEATURE FLAGS - Feature Toggles
|
|
# ─────────────────────────────────────────────────────────────────────────────
|
|
feature_flags:
|
|
descripcion: "Sistema de feature flags para toggles de funcionalidad"
|
|
tablas: 3 # Corregido: 3 tablas en 01-flags.sql (multi-CREATE)
|
|
critico: false
|
|
tablas_lista:
|
|
- flags # Configuracion de feature flags
|
|
- user_flags # Overrides por usuario (FK auth.users, feature_flags.flags)
|
|
- evaluations # Historial de evaluacion de flags (analytics)
|
|
enums:
|
|
- flag_status: ["disabled", "enabled", "percentage"]
|
|
- rollout_stage: ["development", "beta", "production"]
|
|
funciones:
|
|
- "feature_flags.evaluate_flag(p_flag_code, p_user_id)"
|
|
- "feature_flags.update_timestamp()"
|
|
notas:
|
|
- "3 tablas definidas en un solo archivo 01-flags.sql"
|
|
- "Sin servicios backend implementados"
|
|
- "Incluye funcion evaluate_flag() para evaluacion con prioridades"
|
|
- "8 flags iniciales insertados (seed data)"
|
|
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# ISSUES CONOCIDOS (2026-02-05)
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
issues:
|
|
resueltos:
|
|
- id: "GAP-DDL-P0-001"
|
|
descripcion: "FK incorrecta en price_alerts (auth.user_profiles -> auth.users)"
|
|
estado: "RESUELTO 2026-02-05"
|
|
|
|
pendientes:
|
|
enums_duplicados:
|
|
- conflicto: "transaction_type"
|
|
schemas: ["financial", "investment"]
|
|
estado: "Migracion pendiente"
|
|
resolucion: "Renombrar a wallet_transaction_type / investment_transaction_type"
|
|
|
|
- conflicto: "risk_profile"
|
|
schemas: ["investment", "portfolio"]
|
|
estado: "Sin migracion"
|
|
resolucion: "Consolidar en public.risk_profile"
|
|
|
|
- conflicto: "timeframe"
|
|
schemas: ["public", "trading", "market_data"]
|
|
estado: "Parcialmente migrado"
|
|
resolucion: "Eliminar deprecados, usar public.trading_timeframe"
|
|
|
|
duplicaciones:
|
|
- id: "DUP-1"
|
|
descripcion: "trading.symbols vs market_data.tickers (catalogos duplicados)"
|
|
resolucion: "Consolidar en trading.symbols como master"
|
|
|
|
relaciones_debiles:
|
|
- "investment.accounts sin FK a financial.wallets"
|
|
- "trading.bots sin FK a financial.wallets"
|
|
- "market_data.tickers sin FK a trading.symbols"
|
|
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# CARACTERISTICAS ESPECIALES
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
caracteristicas:
|
|
particiones:
|
|
- tabla: "auth.auth_logs"
|
|
tipo: "temporal"
|
|
columna: "created_at"
|
|
|
|
indices_especiales:
|
|
- "GIN para JSONB (metadata, profile_data, new_values)"
|
|
- "BTREE para busquedas por FK y status"
|
|
- "Indices parciales WHERE status='active'"
|
|
- "Indices descendentes para timestamps"
|
|
- "Indices compuestos (user_id, status)"
|
|
|
|
constraints:
|
|
- "Foreign keys con ON DELETE CASCADE/SET NULL/RESTRICT"
|
|
- "Check constraints para validacion de datos"
|
|
- "Unique constraints compuestos"
|
|
- "Balance equation: balance = available_balance + pending_balance"
|
|
- "Idempotency keys en wallet_transactions"
|
|
|
|
triggers:
|
|
- "update_updated_at en todas las tablas con campo updated_at"
|
|
- "Audit logging automatico en audit schema"
|
|
- "Enrollment progress auto-update en education"
|
|
- "Auto-complete enrollment trigger"
|
|
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# HISTORIAL DE CAMBIOS
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
historial:
|
|
- version: "2.0.0"
|
|
fecha: "2026-02-05"
|
|
cambios:
|
|
- "Corregido total de 81 a 100 tablas (validado contra DDL real)"
|
|
- "Agregadas 19 tablas faltantes en inventario"
|
|
- "Documentado schema feature_flags (antes invisible)"
|
|
- "Corregido total de enums de 15 a 50+"
|
|
- "Documentadas funciones y triggers por schema"
|
|
- "Agregada seccion de issues conocidos"
|
|
- "Eliminado schema 'system' fantasma (no existe en DDL)"
|
|
- "Corregido auth de 10 a 13 tablas"
|
|
- "Corregido education de 12 a 19 tablas"
|
|
- "Corregido financial de 9 a 11 tablas"
|
|
- "Corregido investment de 8 a 10 tablas"
|
|
- "Corregido ml de 10 a 12 tablas"
|
|
- "Corregido llm de 4 a 5 tablas"
|
|
- "Corregido portfolio de 4 a 5 tablas"
|
|
- "Corregido market_data de 3 a 4 tablas"
|
|
autor: "Claude Code (Opus 4.6)"
|
|
tarea: "TASK-2026-02-05-ANALISIS-VALIDACION-MODELADO-BD"
|
|
|
|
- version: "1.2.0"
|
|
fecha: "2026-02-04"
|
|
cambios: "Post-Sprint 3: +3 tablas (price_alerts, distribution_history, distribution_runs)"
|
|
|
|
- version: "1.0.0"
|
|
fecha: "2026-01-27"
|
|
cambios: "Creacion inicial"
|