# ═══════════════════════════════════════════════════════════════════════════════ # 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"