284 lines
15 KiB
Markdown
284 lines
15 KiB
Markdown
# _MAP - Database Schemas
|
|
|
|
> Índice de navegación para los esquemas de base de datos
|
|
>
|
|
> **ACTUALIZADO:** 2025-12-06 - Estructura DDL completada según análisis de requisitos
|
|
> **Política:** Carga Limpia (DDL-First) - Ver DIRECTIVA-POLITICA-CARGA-LIMPIA.md
|
|
> **Total:** 63 tablas, 91 archivos SQL
|
|
|
|
## Estructura de Archivos
|
|
|
|
```
|
|
apps/database/
|
|
├── ddl/
|
|
│ └── schemas/
|
|
│ ├── auth/ # 10 tablas - Autenticación y usuarios
|
|
│ ├── education/ # 14 tablas - Plataforma educativa
|
|
│ ├── trading/ # 9 tablas - Trading y Paper Engine
|
|
│ ├── investment/ # 5 tablas - Cuentas PAMM
|
|
│ ├── financial/ # 8 tablas - Pagos, wallets unificadas
|
|
│ ├── ml/ # 5 tablas - Machine Learning
|
|
│ ├── llm/ # 5 tablas - LLM Agent
|
|
│ └── audit/ # 7 tablas - Auditoría y compliance
|
|
├── seeds/
|
|
│ ├── prod/ # Seeds de producción
|
|
│ └── dev/ # Seeds de desarrollo
|
|
└── scripts/
|
|
├── create-database.sh
|
|
└── drop-and-recreate-database.sh
|
|
```
|
|
|
|
## Resumen de Schemas
|
|
|
|
| Schema | Propósito | Tablas | Funciones | Estado |
|
|
|--------|-----------|--------|-----------|--------|
|
|
| auth | Autenticación y usuarios | 10 | 4 | ✅ Completado |
|
|
| education | Plataforma educativa | 14 | 8 | ✅ Completado |
|
|
| trading | Trading y Paper Engine | 9 | 2 | ✅ Completado |
|
|
| investment | Cuentas PAMM | 5 | - | ✅ Completado |
|
|
| financial | Wallets unificadas, pagos | 8 | 4 | ✅ Completado |
|
|
| ml | Machine Learning signals | 5 | - | ✅ Completado |
|
|
| llm | LLM Agent, RAG | 5 | - | ✅ Completado |
|
|
| audit | Logs, compliance | 7 | - | ✅ Completado |
|
|
|
|
---
|
|
|
|
## Detalle por Schema
|
|
|
|
### auth (10 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `users` | 01-users.sql | Usuarios base del sistema |
|
|
| `user_profiles` | 02-user_profiles.sql | Información extendida |
|
|
| `oauth_accounts` | 03-oauth_accounts.sql | Cuentas OAuth (Google, GitHub) |
|
|
| `sessions` | 04-sessions.sql | Sesiones activas |
|
|
| `email_verifications` | 05-email_verifications.sql | Verificación de email |
|
|
| `phone_verifications` | 06-phone_verifications.sql | Verificación de teléfono |
|
|
| `password_reset_tokens` | 07-password_reset_tokens.sql | Tokens de reset |
|
|
| `auth_logs` | 08-auth_logs.sql | Log de autenticación |
|
|
| `login_attempts` | 09-login_attempts.sql | **NUEVO** - Control de intentos |
|
|
| `rate_limiting_config` | 10-rate_limiting_config.sql | **NUEVO** - Configuración rate limit |
|
|
|
|
**Funciones:** update_updated_at, log_auth_event, cleanup_expired_sessions, create_user_profile_trigger
|
|
|
|
### education (14 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `categories` | 01-categories.sql | Categorías de cursos |
|
|
| `courses` | 02-courses.sql | Cursos de trading |
|
|
| `modules` | 03-modules.sql | Módulos de cursos |
|
|
| `lessons` | 04-lessons.sql | Lecciones individuales |
|
|
| `enrollments` | 05-enrollments.sql | Inscripciones |
|
|
| `progress` | 06-progress.sql | Progreso por lección |
|
|
| `quizzes` | 07-quizzes.sql | Cuestionarios |
|
|
| `quiz_questions` | 08-quiz_questions.sql | Preguntas de quiz |
|
|
| `quiz_attempts` | 09-quiz_attempts.sql | Intentos de quiz |
|
|
| `certificates` | 10-certificates.sql | Certificados emitidos |
|
|
| `user_achievements` | 11-user_achievements.sql | Logros de usuarios |
|
|
| `user_gamification_profile` | 12-user_gamification_profile.sql | **NUEVO** - Perfil gamificación |
|
|
| `user_activity_log` | 13-user_activity_log.sql | **NUEVO** - Log de actividad |
|
|
| `course_reviews` | 14-course_reviews.sql | **NUEVO** - Reseñas de cursos |
|
|
|
|
**Funciones:** update_updated_at, update_enrollment_progress, auto_complete_enrollment, generate_certificate, update_course_stats, update_enrollment_count, update_gamification_profile, views
|
|
|
|
### trading (9 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `symbols` | 01-symbols.sql | Instrumentos financieros |
|
|
| `watchlists` | 02-watchlists.sql | Listas de seguimiento |
|
|
| `watchlist_items` | 03-watchlist_items.sql | Items en watchlists |
|
|
| `bots` | 04-bots.sql | Agentes de trading (Atlas, Orion, Nova) |
|
|
| `orders` | 05-orders.sql | Órdenes de trading |
|
|
| `positions` | 06-positions.sql | Posiciones abiertas/cerradas |
|
|
| `trades` | 07-trades.sql | Historial de trades |
|
|
| `signals` | 08-signals.sql | **INTERFAZ ML** - Señales generadas |
|
|
| `trading_metrics` | 09-trading_metrics.sql | Métricas de rendimiento |
|
|
|
|
**Funciones:** calculate_position_pnl, update_bot_stats
|
|
|
|
> **Nota:** La tabla `signals` sirve como interfaz entre Trading (consume) y ML (produce), resolviendo la dependencia circular.
|
|
|
|
### investment (5 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `products` | 01-products.sql | Productos PAMM |
|
|
| `accounts` | 02-accounts.sql | Cuentas de inversión |
|
|
| `transactions` | 03-transactions.sql | Movimientos de cuenta |
|
|
| `distributions` | 04-distributions.sql | Distribución de ganancias (80/20) |
|
|
| `risk_questionnaire` | 05-risk_questionnaire.sql | **NUEVO** - Cuestionario de riesgo |
|
|
|
|
> **Delimitación:** Este schema solo maneja PAMM. Portfolio personal se gestiona desde dashboard (OQI-008).
|
|
|
|
### financial (8 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `wallets` | 01-wallets.sql | **UNIFICADO** - Wallets del sistema |
|
|
| `wallet_transactions` | 02-wallet_transactions.sql | Transacciones de wallet |
|
|
| `subscriptions` | 03-subscriptions.sql | Suscripciones activas |
|
|
| `payments` | 04-payments.sql | Pagos procesados (Stripe) |
|
|
| `invoices` | 05-invoices.sql | Facturas |
|
|
| `wallet_audit_log` | 06-wallet_audit_log.sql | **NUEVO** - Auditoría de wallets |
|
|
| `currency_exchange_rates` | 07-currency_exchange_rates.sql | **NUEVO** - Tipos de cambio USD/MXN |
|
|
| `wallet_limits` | 08-wallet_limits.sql | **NUEVO** - Límites de operación |
|
|
|
|
**Funciones:** update_wallet_balance, process_transaction, triggers, views
|
|
|
|
> **Decisión:** USD como moneda principal, MXN mediante conversión. Ver DEC-001.
|
|
|
|
### ml (5 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `models` | 01-models.sql | Registro de modelos ML |
|
|
| `model_versions` | 02-model_versions.sql | **NUEVO** - Versionado de modelos |
|
|
| `predictions` | 03-predictions.sql | Predicciones generadas |
|
|
| `prediction_outcomes` | 04-prediction_outcomes.sql | Resultados de predicciones |
|
|
| `feature_store` | 05-feature_store.sql | Features pre-calculadas |
|
|
|
|
### llm (5 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `conversations` | 01-conversations.sql | Conversaciones con LLM |
|
|
| `messages` | 02-messages.sql | Mensajes de conversación |
|
|
| `user_preferences` | 03-user_preferences.sql | **NUEVO** - Preferencias de usuario |
|
|
| `user_memory` | 04-user_memory.sql | **NUEVO** - Memoria persistente |
|
|
| `embeddings` | 05-embeddings.sql | RAG - Embeddings (pgvector) |
|
|
|
|
> **Decisión:** pgvector para embeddings. Ver DEC-004.
|
|
|
|
### audit (7 tablas)
|
|
|
|
| Tabla | Archivo | Descripción |
|
|
|-------|---------|-------------|
|
|
| `audit_logs` | 01-audit_logs.sql | Log general de auditoría |
|
|
| `security_events` | 02-security_events.sql | Eventos de seguridad |
|
|
| `system_events` | 03-system_events.sql | Eventos del sistema |
|
|
| `trading_audit` | 04-trading_audit.sql | Auditoría de trading |
|
|
| `api_request_logs` | 05-api_request_logs.sql | Logs de API |
|
|
| `data_access_logs` | 06-data_access_logs.sql | Acceso a datos sensibles (GDPR) |
|
|
| `compliance_logs` | 07-compliance_logs.sql | Cumplimiento regulatorio |
|
|
|
|
---
|
|
|
|
## Diagrama de Relaciones
|
|
|
|
```
|
|
┌─────────────────────────────────────────────────────────────────────┐
|
|
│ auth │
|
|
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────┐ │
|
|
│ │ users │──│ profiles │ │ sessions │ │ oauth_accounts │ │
|
|
│ └────┬─────┘ └──────────┘ └──────────┘ └──────────────────┘ │
|
|
│ │ │
|
|
└───────┼─────────────────────────────────────────────────────────────┘
|
|
│
|
|
├──────────────────────────┬──────────────────────────┐
|
|
│ │ │
|
|
▼ ▼ ▼
|
|
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
|
|
│ education │ │ trading │ │ investment │
|
|
│ ┌───────────┐ │ │ ┌───────────┐ │ │ ┌───────────┐ │
|
|
│ │ courses │ │ │ │ bots │─┼──────────┼─│ products │ │
|
|
│ └─────┬─────┘ │ │ └─────┬─────┘ │ │ └─────┬─────┘ │
|
|
│ │ │ │ │ │ │ │ │
|
|
│ ┌─────▼─────┐ │ │ ┌─────▼─────┐ │ │ ┌─────▼─────┐ │
|
|
│ │ lessons │ │ │ │ signals◄──┼─┼── ml │ │ accounts │ │
|
|
│ └───────────┘ │ │ └─────┬─────┘ │ │ └─────┬─────┘ │
|
|
│ │ │ │ │ │ │ │
|
|
│ ┌───────────┐ │ │ ┌─────▼─────┐ │ │ ┌─────▼─────┐ │
|
|
│ │enrollments│ │ │ │ positions │─┼──────────┼─│transactions│ │
|
|
│ └───────────┘ │ │ └───────────┘ │ │ └───────────┘ │
|
|
└───────────────┘ └───────────────┘ └───────────────┘
|
|
│ │ │
|
|
│ │ │
|
|
└──────────────────────────┼──────────────────────────┘
|
|
│
|
|
▼
|
|
┌───────────────┐
|
|
│ financial │
|
|
│ ┌───────────┐ │
|
|
│ │ wallets │◄──── UNIFICADO
|
|
│ └─────┬─────┘ │
|
|
│ │ │
|
|
│ ┌─────▼─────┐ │
|
|
│ │ payments │ │
|
|
│ └───────────┘ │
|
|
│ │
|
|
│ ┌───────────┐ │
|
|
│ │subscript. │ │
|
|
│ └───────────┘ │
|
|
└───────────────┘
|
|
|
|
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
|
|
│ ml │ │ llm │ │ audit │
|
|
│ ┌───────────┐ │ │ ┌───────────┐ │ │ ┌───────────┐ │
|
|
│ │ models │ │ │ │ conversat.│ │ │ │audit_logs │ │
|
|
│ └─────┬─────┘ │ │ └─────┬─────┘ │ │ └───────────┘ │
|
|
│ │ │ │ │ │ │ │
|
|
│ ┌─────▼─────┐ │ │ ┌─────▼─────┐ │ │ ┌───────────┐ │
|
|
│ │predictions├─┼──────────┼─│ embeddings│ │ │ │sec_events │ │
|
|
│ └───────────┘ │ signals │ └───────────┘ │ │ └───────────┘ │
|
|
└───────────────┘ └───────────────┘ └───────────────┘
|
|
```
|
|
|
|
---
|
|
|
|
## Orden de Ejecución (create-database.sh)
|
|
|
|
```bash
|
|
1. Extensions (uuid-ossp, pgcrypto, pg_trgm, btree_gin, vector)
|
|
2. Schemas (auth, education, financial, trading, investment, ml, llm, audit)
|
|
3. DDL por schema en orden de dependencia:
|
|
- auth (sin dependencias)
|
|
- education (depende de auth)
|
|
- financial (depende de auth)
|
|
- trading (depende de auth)
|
|
- investment(depende de auth, trading)
|
|
- ml (depende de auth, trading)
|
|
- llm (depende de auth)
|
|
- audit (depende de auth)
|
|
4. Seeds (prod o dev)
|
|
```
|
|
|
|
---
|
|
|
|
## Comandos de Uso
|
|
|
|
```bash
|
|
# Crear base de datos nueva
|
|
./scripts/create-database.sh
|
|
|
|
# Eliminar y recrear (desarrollo)
|
|
./scripts/drop-and-recreate-database.sh
|
|
|
|
# Solo cargar seeds
|
|
./scripts/create-database.sh --seeds-only --env dev
|
|
```
|
|
|
|
---
|
|
|
|
## Notas Importantes
|
|
|
|
- **TIMESTAMPTZ**: Todas las columnas de fecha usan timezone (DEC-007)
|
|
- **gen_random_uuid()**: Para generación de UUIDs (no uuid_generate_v4())
|
|
- **Row Level Security (RLS)**: Implementar en fase de seguridad
|
|
- **Particionamiento**: Considerar para `predictions`, `audit_logs` en producción
|
|
- **Backups**: Configurar backup incremental cada 6 horas
|
|
|
|
---
|
|
|
|
## Referencias
|
|
|
|
- [DIRECTIVA-POLITICA-CARGA-LIMPIA.md](../DIRECTIVA-POLITICA-CARGA-LIMPIA.md)
|
|
- [DECISIONES-ARQUITECTONICAS.md](../../docs/99-analisis/DECISIONES-ARQUITECTONICAS.md)
|
|
- [PLAN-IMPLEMENTACION-CORRECCIONES.md](../../docs/99-analisis/PLAN-IMPLEMENTACION-CORRECCIONES.md)
|
|
|
|
---
|
|
*Última actualización: 2025-12-06*
|
|
*Generado por Requirements-Analyst Agent*
|