# ============================================================================ # FASE 3 - INTEGRIDAD Y AUDITORIA COMPLETA # Trading Platform DDL - Database Integrity Specialist Report # ============================================================================ # Generated: 2026-02-05 # Agent: Database Integrity Specialist (Claude Opus 4.6) # Scope: ALL 11 schemas, 101 tables, all FK/constraints/functions/triggers # DDL Source: projects/trading-platform/apps/database/ddl/ # ============================================================================ metadata: task_id: TASK-2026-02-05-ANALISIS-VALIDACION-MODELADO-BD phase: 3 phase_name: "Integridad y Auditoria" generated_at: "2026-02-05T00:00:00Z" agent: "Database Integrity Specialist" model: "claude-opus-4-6" # ============================================================================ # STATISTICS SUMMARY # ============================================================================ statistics: schemas_total: 11 schemas_in_01_schemas_sql: 10 schemas_self_created: 1 # feature_flags creates its own schema in 01-flags.sql schemas_list: - auth - trading - education - financial - investment - portfolio - ml - llm - audit - market_data - feature_flags tables_per_schema: auth: 12 # users, user_profiles, oauth_accounts, sessions, email_verifications, phone_verifications, password_reset_tokens, auth_logs, login_attempts, rate_limiting_config, notifications, user_push_tokens trading: 13 # symbols, watchlists, watchlist_items, bots, orders, positions, trades, signals, trading_metrics, paper_balances, price_alerts, drawing_tools, drawing_templates education: 19 # categories, courses, modules, lessons, enrollments, progress, quizzes, quiz_questions, quiz_attempts, certificates, user_achievements, user_gamification_profile, user_activity_log, course_reviews, videos, review_helpful_votes, instructors, course_tags, course_tag_assignments financial: 11 # wallets, wallet_transactions, subscriptions, invoices, payments, wallet_audit_log, currency_exchange_rates, wallet_limits, customers, payment_methods, refunds investment: 10 # products, risk_questionnaire, accounts, distributions, transactions, withdrawal_requests, daily_performance, distribution_history, distribution_runs, agent_executions portfolio: 5 # portfolios, portfolio_allocations, portfolio_goals, rebalance_history, portfolio_snapshots ml: 12 # models, model_versions, predictions, prediction_outcomes, feature_store, llm_predictions, llm_prediction_outcomes, llm_decisions, risk_events, backtest_runs, llm_signals, prediction_overlays llm: 5 # conversations, messages, user_preferences, user_memory, embeddings audit: 7 # audit_logs, security_events, system_events, trading_audit, api_request_logs, data_access_logs, compliance_logs market_data: 4 # tickers, ohlcv_5m, ohlcv_15m, ohlcv_5m_staging feature_flags: 3 # flags, user_flags, evaluations tables_total: 101 foreign_keys_total: 80 check_constraints_total: 89 unique_constraints_total: 32 functions_total: 36 triggers_total: 46 views_total: 14 enums_total: 42 # ============================================================================ # A. FK VALIDATION MATRIX # ============================================================================ fk_validation_matrix: # ---- AUTH SCHEMA ---- - source: auth.user_profiles.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true notes: "1:1 relationship, UNIQUE on user_id" - source: auth.oauth_accounts.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true - source: auth.sessions.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true - source: auth.email_verifications.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true - source: auth.phone_verifications.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true - source: auth.password_reset_tokens.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true - source: auth.notifications.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true notes: "Inline REFERENCES syntax" - source: auth.user_push_tokens.user_id target: auth.users.id cross_schema: false on_delete: CASCADE valid: true notes: "Inline REFERENCES syntax" # ---- TRADING SCHEMA ---- - source: trading.watchlists.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.watchlist_items.watchlist_id target: trading.watchlists.id cross_schema: false on_delete: CASCADE valid: true - source: trading.watchlist_items.symbol_id target: trading.symbols.id cross_schema: false on_delete: CASCADE valid: true - source: trading.bots.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.orders.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.orders.bot_id target: trading.bots.id cross_schema: false on_delete: SET NULL valid: true - source: trading.orders.symbol_id target: trading.symbols.id cross_schema: false on_delete: NO ACTION valid: true notes: "No ON DELETE specified - defaults to NO ACTION" - source: trading.positions.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.positions.bot_id target: trading.bots.id cross_schema: false on_delete: SET NULL valid: true - source: trading.positions.symbol_id target: trading.symbols.id cross_schema: false on_delete: NO ACTION valid: true notes: "No ON DELETE specified - defaults to NO ACTION" - source: trading.trades.order_id target: trading.orders.id cross_schema: false on_delete: CASCADE valid: true - source: trading.trades.position_id target: trading.positions.id cross_schema: false on_delete: SET NULL valid: true - source: trading.trading_metrics.bot_id target: trading.bots.id cross_schema: false on_delete: CASCADE valid: true - source: trading.paper_balances.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.price_alerts.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.price_alerts.symbol_id target: trading.symbols.id cross_schema: false on_delete: CASCADE valid: true - source: trading.drawing_tools.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: trading.drawing_tools.symbol_id target: trading.symbols.id cross_schema: false on_delete: CASCADE valid: true - source: trading.drawing_templates.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true notes: "user_id is NULLable (NULL = system template)" # ---- EDUCATION SCHEMA ---- - source: education.categories.parent_id target: education.categories.id cross_schema: false on_delete: SET NULL valid: true notes: "Self-referencing FK for hierarchy" - source: education.courses.category_id target: education.categories.id cross_schema: false on_delete: RESTRICT valid: true - source: education.courses.instructor_id target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true notes: "Should eventually reference education.instructors(user_id) - migration pending" - source: education.modules.course_id target: education.courses.id cross_schema: false on_delete: CASCADE valid: true - source: education.modules.unlock_after_module_id target: education.modules.id cross_schema: false on_delete: SET NULL valid: true notes: "Self-referencing FK for module ordering" - source: education.lessons.module_id target: education.modules.id cross_schema: false on_delete: CASCADE valid: true - source: education.enrollments.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.enrollments.course_id target: education.courses.id cross_schema: false on_delete: RESTRICT valid: true - source: education.progress.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.progress.lesson_id target: education.lessons.id cross_schema: false on_delete: CASCADE valid: true - source: education.progress.enrollment_id target: education.enrollments.id cross_schema: false on_delete: CASCADE valid: true - source: education.quizzes.module_id target: education.modules.id cross_schema: false on_delete: CASCADE valid: true notes: "NULLable, mutual exclusion with lesson_id" - source: education.quizzes.lesson_id target: education.lessons.id cross_schema: false on_delete: CASCADE valid: true notes: "NULLable, mutual exclusion with module_id" - source: education.quiz_questions.quiz_id target: education.quizzes.id cross_schema: false on_delete: CASCADE valid: true - source: education.quiz_attempts.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.quiz_attempts.quiz_id target: education.quizzes.id cross_schema: false on_delete: RESTRICT valid: true - source: education.quiz_attempts.enrollment_id target: education.enrollments.id cross_schema: false on_delete: SET NULL valid: true - source: education.certificates.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.certificates.course_id target: education.courses.id cross_schema: false on_delete: RESTRICT valid: true - source: education.certificates.enrollment_id target: education.enrollments.id cross_schema: false on_delete: RESTRICT valid: true - source: education.user_achievements.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.user_achievements.course_id target: education.courses.id cross_schema: false on_delete: SET NULL valid: true notes: "NULLable" - source: education.user_achievements.quiz_id target: education.quizzes.id cross_schema: false on_delete: SET NULL valid: true notes: "NULLable" - source: education.user_gamification_profile.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.user_activity_log.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.user_activity_log.course_id target: education.courses.id cross_schema: false on_delete: SET NULL valid: true - source: education.user_activity_log.lesson_id target: education.lessons.id cross_schema: false on_delete: SET NULL valid: true - source: education.user_activity_log.quiz_id target: education.quizzes.id cross_schema: false on_delete: SET NULL valid: true - source: education.course_reviews.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.course_reviews.course_id target: education.courses.id cross_schema: false on_delete: CASCADE valid: true - source: education.course_reviews.enrollment_id target: education.enrollments.id cross_schema: false on_delete: CASCADE valid: true - source: education.course_reviews.approved_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true notes: "NULLable, no ON DELETE specified" - source: education.videos.course_id target: education.courses.id cross_schema: false on_delete: CASCADE valid: true - source: education.videos.lesson_id target: education.lessons.id cross_schema: false on_delete: SET NULL valid: true notes: "NULLable" - source: education.videos.uploaded_by target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true - source: education.review_helpful_votes.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.review_helpful_votes.review_id target: education.course_reviews.id cross_schema: false on_delete: CASCADE valid: true - source: education.instructors.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: education.course_tag_assignments.course_id target: education.courses.id cross_schema: false on_delete: CASCADE valid: true - source: education.course_tag_assignments.tag_id target: education.course_tags.id cross_schema: false on_delete: CASCADE valid: true - source: education.course_tag_assignments.assigned_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true notes: "NULLable, no ON DELETE specified" # ---- FINANCIAL SCHEMA ---- - source: financial.wallets.user_id target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true - source: financial.wallet_transactions.wallet_id target: financial.wallets.id cross_schema: false on_delete: RESTRICT valid: true - source: financial.wallet_transactions.destination_wallet_id target: financial.wallets.id cross_schema: false on_delete: RESTRICT valid: true notes: "NULLable, for transfers" - source: financial.wallet_transactions.related_transaction_id target: financial.wallet_transactions.id cross_schema: false on_delete: NO ACTION valid: true notes: "Self-referencing, NULLable, for bidirectional transfers" - source: financial.subscriptions.user_id target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true - source: financial.invoices.user_id target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true - source: financial.invoices.subscription_id target: financial.subscriptions.id cross_schema: false on_delete: SET NULL valid: true - source: financial.payments.user_id target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true - source: financial.payments.subscription_id target: financial.subscriptions.id cross_schema: false on_delete: SET NULL valid: true - source: financial.payments.invoice_id target: financial.invoices.id cross_schema: false on_delete: SET NULL valid: true - source: financial.payments.wallet_transaction_id target: financial.wallet_transactions.id cross_schema: false on_delete: SET NULL valid: true - source: financial.wallet_audit_log.wallet_id target: financial.wallets.id cross_schema: false on_delete: CASCADE valid: true - source: financial.wallet_audit_log.actor_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true notes: "NULLable" - source: financial.wallet_audit_log.transaction_id target: financial.wallet_transactions.id cross_schema: false on_delete: SET NULL valid: true - source: financial.wallet_limits.wallet_id target: financial.wallets.id cross_schema: false on_delete: CASCADE valid: true notes: "NULLable, used for wallet-specific limits" - source: financial.customers.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: financial.payment_methods.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: financial.payment_methods.customer_id target: financial.customers.id cross_schema: false on_delete: SET NULL valid: true - source: financial.refunds.payment_id target: financial.payments.id cross_schema: false on_delete: RESTRICT valid: true - source: financial.refunds.requested_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true notes: "NULLable, no ON DELETE specified" - source: financial.refunds.approved_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true notes: "NULLable, no ON DELETE specified" # ---- INVESTMENT SCHEMA ---- - source: investment.risk_questionnaire.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: investment.accounts.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: investment.accounts.product_id target: investment.products.id cross_schema: false on_delete: RESTRICT valid: true - source: investment.accounts.questionnaire_id target: investment.risk_questionnaire.id cross_schema: false on_delete: NO ACTION valid: true notes: "NULLable" - source: investment.distributions.product_id target: investment.products.id cross_schema: false on_delete: RESTRICT valid: true - source: investment.transactions.account_id target: investment.accounts.id cross_schema: false on_delete: CASCADE valid: true - source: investment.transactions.distribution_id target: investment.distributions.id cross_schema: false on_delete: NO ACTION valid: true notes: "NULLable" - source: investment.withdrawal_requests.account_id target: investment.accounts.id cross_schema: false on_delete: RESTRICT valid: true - source: investment.withdrawal_requests.user_id target: auth.users.id cross_schema: true on_delete: RESTRICT valid: true - source: investment.withdrawal_requests.reviewed_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true notes: "NULLable" - source: investment.daily_performance.account_id target: investment.accounts.id cross_schema: false on_delete: CASCADE valid: true - source: investment.daily_performance.product_id target: investment.products.id cross_schema: false on_delete: CASCADE valid: true - source: investment.distribution_history.account_id target: investment.accounts.id cross_schema: false on_delete: CASCADE valid: true - source: investment.distribution_history.product_id target: investment.products.id cross_schema: false on_delete: RESTRICT valid: true - source: investment.agent_executions.account_id target: investment.accounts.id cross_schema: false on_delete: CASCADE valid: true # ---- PORTFOLIO SCHEMA ---- - source: portfolio.portfolios.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: portfolio.portfolio_allocations.portfolio_id target: portfolio.portfolios.id cross_schema: false on_delete: CASCADE valid: true - source: portfolio.portfolio_goals.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: portfolio.portfolio_goals.portfolio_id target: portfolio.portfolios.id cross_schema: false on_delete: SET NULL valid: true - source: portfolio.rebalance_history.portfolio_id target: portfolio.portfolios.id cross_schema: false on_delete: CASCADE valid: true - source: portfolio.portfolio_snapshots.portfolio_id target: portfolio.portfolios.id cross_schema: false on_delete: CASCADE valid: true # ---- ML SCHEMA ---- - source: ml.model_versions.model_id target: ml.models.id cross_schema: false on_delete: CASCADE valid: true - source: ml.predictions.model_id target: ml.models.id cross_schema: false on_delete: CASCADE valid: true - source: ml.predictions.model_version_id target: ml.model_versions.id cross_schema: false on_delete: CASCADE valid: true - source: ml.prediction_outcomes.prediction_id target: ml.predictions.id cross_schema: false on_delete: CASCADE valid: true - source: ml.llm_prediction_outcomes.prediction_id target: ml.llm_predictions.id cross_schema: false on_delete: CASCADE valid: true - source: ml.llm_decisions.prediction_id target: ml.llm_predictions.id cross_schema: false on_delete: SET NULL valid: true - source: ml.prediction_overlays.prediction_id target: ml.predictions.id cross_schema: false on_delete: CASCADE valid: true - source: ml.backtest_runs.user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true # ---- LLM SCHEMA ---- - source: llm.conversations.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: llm.messages.conversation_id target: llm.conversations.id cross_schema: false on_delete: CASCADE valid: true - source: llm.user_preferences.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: llm.user_memory.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: llm.user_memory.source_conversation_id target: llm.conversations.id cross_schema: false on_delete: SET NULL valid: true - source: llm.embeddings.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true notes: "NULLable" # ---- AUDIT SCHEMA ---- - source: audit.audit_logs.user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true - source: audit.security_events.user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true - source: audit.security_events.reviewed_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true - source: audit.trading_audit.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: audit.api_request_logs.user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true - source: audit.data_access_logs.accessor_user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: audit.data_access_logs.target_user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true - source: audit.compliance_logs.user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true - source: audit.compliance_logs.reviewed_by target: auth.users.id cross_schema: true on_delete: NO ACTION valid: true # ---- MARKET_DATA SCHEMA ---- - source: market_data.ohlcv_5m.ticker_id target: market_data.tickers.id cross_schema: false on_delete: NO ACTION valid: true notes: "tickers.id is SERIAL (INTEGER), not UUID" - source: market_data.ohlcv_15m.ticker_id target: market_data.tickers.id cross_schema: false on_delete: NO ACTION valid: true notes: "tickers.id is SERIAL (INTEGER), not UUID" # ---- FEATURE_FLAGS SCHEMA ---- - source: feature_flags.user_flags.user_id target: auth.users.id cross_schema: true on_delete: CASCADE valid: true - source: feature_flags.user_flags.flag_id target: feature_flags.flags.id cross_schema: false on_delete: CASCADE valid: true - source: feature_flags.evaluations.flag_id target: feature_flags.flags.id cross_schema: false on_delete: CASCADE valid: true - source: feature_flags.evaluations.user_id target: auth.users.id cross_schema: true on_delete: SET NULL valid: true # ---- MISSING FKs (Logical relationships without formal FK constraints) ---- missing_fks: - source: auth.auth_logs.user_id target: auth.users.id reason: "Partitioned table - FKs cannot reference partitioned tables in standard PG (or are complex)" recommendation: "Acceptable - partitioned table limitation. Validate at application level." severity: LOW - source: auth.auth_logs.session_id target: auth.sessions.id reason: "No FK defined, logical relationship exists" recommendation: "Acceptable for log tables - session may be deleted but logs should persist" severity: LOW - source: auth.login_attempts.user_id target: auth.users.id reason: "No FK defined. NULLable column - user_id may not be resolved at attempt time" recommendation: "Acceptable - login attempts may happen for nonexistent users" severity: LOW - source: auth.rate_limiting_config.created_by_id target: auth.users.id reason: "No FK defined for audit columns" recommendation: "LOW priority - add FK with ON DELETE SET NULL" severity: LOW - source: auth.rate_limiting_config.updated_by_id target: auth.users.id reason: "No FK defined for audit columns" recommendation: "LOW priority - add FK with ON DELETE SET NULL" severity: LOW - source: auth.users.created_by_id target: auth.users.id reason: "No self-referencing FK for audit columns" recommendation: "LOW priority - add self-referencing FK with ON DELETE SET NULL" severity: LOW - source: auth.users.updated_by_id target: auth.users.id reason: "No self-referencing FK for audit columns" recommendation: "LOW priority - add self-referencing FK with ON DELETE SET NULL" severity: LOW - source: trading.signals.symbol target: trading.symbols.symbol reason: "Uses VARCHAR symbol name instead of UUID FK to symbols table" recommendation: "MEDIUM - Consider adding symbol_id UUID FK for referential integrity" severity: MEDIUM - source: trading.trades.symbol target: trading.symbols.symbol reason: "Uses VARCHAR symbol name instead of UUID FK" recommendation: "MEDIUM - Consider adding symbol_id UUID FK for consistency with orders/positions" severity: MEDIUM - source: ml.feature_store.symbol target: trading.symbols.symbol reason: "Uses VARCHAR, no FK to trading.symbols or market_data.tickers" recommendation: "LOW - ML tables often denormalize for performance" severity: LOW - source: ml.llm_predictions.symbol target: trading.symbols.symbol reason: "Uses VARCHAR, no FK" recommendation: "LOW - ML tables pattern" severity: LOW - source: ml.llm_signals.symbol target: trading.symbols.symbol reason: "Uses VARCHAR, no FK" recommendation: "LOW - ML tables pattern" severity: LOW - source: investment.distribution_runs target: "no user_id FK" reason: "Distribution runs are system-level, no user association" recommendation: "Consider adding initiated_by UUID FK" severity: LOW - source: market_data.ohlcv_5m_staging target: "no ticker_id FK" reason: "Staging table has no FK constraints" recommendation: "Acceptable - staging tables are temporary" severity: LOW # ============================================================================ # B. CONSTRAINTS AUDIT # ============================================================================ constraints_audit: check_constraints: auth: - table: users constraints: - name: valid_email expression: "email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'" meaningful: true - name: failed_attempts_non_negative expression: "failed_login_attempts >= 0" meaningful: true - name: email_verified_at_consistency expression: "email_verified/email_verified_at pair consistency" meaningful: true - name: phone_verified_at_consistency expression: "phone_verified/phone_verified_at pair consistency" meaningful: true - name: mfa_secret_consistency expression: "mfa_enabled requires mfa_secret and method" meaningful: true - table: sessions constraints: - name: valid_session_dates expression: "expires_at > created_at" meaningful: true - name: invalidated_consistency expression: "is_active/invalidated_at pair consistency" meaningful: true - table: email_verifications constraints: - name: valid_expiration expression: "expires_at > created_at" meaningful: true - name: verified_consistency expression: "is_verified/verified_at pair consistency" meaningful: true - table: phone_verifications constraints: - name: valid_expiration expression: "expires_at > created_at" meaningful: true - name: verified_consistency expression: "same as email" meaningful: true - name: valid_attempts expression: "attempts >= 0, attempts <= max_attempts" meaningful: true - table: password_reset_tokens constraints: - name: valid_expiration expression: "expires_at > created_at" meaningful: true - name: used_consistency expression: "is_used/used_at pair consistency" meaningful: true - table: login_attempts constraints: - name: login_attempt_has_identifier expression: "email IS NOT NULL OR user_id IS NOT NULL" meaningful: true - name: failure_reason_consistency expression: "failure requires reason, success has no reason" meaningful: true - table: rate_limiting_config constraints: - name: valid_rate_limits expression: "max_requests > 0, window_seconds > 0" meaningful: true - name: valid_scope expression: "scope IN ('ip','user','email','global')" meaningful: true - table: notifications constraints: - name: valid_priority expression: "IN ('low','normal','high','urgent')" meaningful: true - name: valid_type expression: "CHECK on notification types" meaningful: true - name: valid_icon_type expression: "IN ('success','warning','error','info')" meaningful: true - table: user_push_tokens constraints: - name: valid_platform expression: "IN ('web','ios','android')" meaningful: true trading: - table: paper_balances constraints: - name: chk_balance_consistency expression: "total = available + locked" meaningful: true - name: chk_balance_non_negative expression: "total >= 0, available >= 0, locked >= 0" meaningful: true - name: chk_initial_positive expression: "initial_balance > 0" meaningful: true - table: price_alerts constraints: - name: chk_target_price_positive expression: "target_price > 0 or NULL" meaningful: true - name: chk_percent_threshold_valid expression: "0-100 range or NULL" meaningful: true - name: chk_alert_config expression: "price types need price, percent types need threshold" meaningful: true - table: drawing_tools constraints: - name: drawing_tools_points_not_empty expression: "jsonb_array_length(points) >= 1" meaningful: true - table: drawing_templates constraints: - name: drawing_templates_name_not_empty expression: "length(trim(name)) > 0" meaningful: true education: - table: categories constraints: - name: valid_color_format expression: "color ~ '^#[0-9A-Fa-f]{6}$'" meaningful: true - table: courses constraints: - name: valid_rating expression: "avg_rating >= 0 AND <= 5" meaningful: true - name: valid_price expression: "price_usd >= 0" meaningful: true - table: enrollments constraints: - name: valid_progress expression: "0-100" meaningful: true - name: valid_completion expression: "completed requires completed_at and 100%" meaningful: true - table: progress constraints: - name: valid_watch_percentage expression: "0-100" meaningful: true - name: completion_requires_date expression: "is_completed requires completed_at" meaningful: true - table: quizzes constraints: - name: valid_passing_score expression: "1-100" meaningful: true - name: quiz_association expression: "XOR: module_id or lesson_id" meaningful: true - table: quiz_questions constraints: - name: valid_options expression: "options required for MC/TF/MS types" meaningful: true - table: quiz_attempts constraints: - name: valid_score_percentage expression: "0-100" meaningful: true - table: course_reviews constraints: - name: "(inline) rating CHECK" expression: "rating >= 1 AND rating <= 5" meaningful: true - table: videos constraints: - name: valid_status expression: "IN list of statuses" meaningful: true - name: valid_storage_provider expression: "IN ('s3','r2','cloudflare_stream')" meaningful: true - name: valid_progress expression: "0-100" meaningful: true - name: positive_duration expression: "duration_seconds > 0 or NULL" meaningful: true - name: positive_file_size expression: "file_size_bytes > 0" meaningful: true - table: user_gamification_profile constraints: - name: valid_level expression: "current_level >= 1" meaningful: true - name: valid_xp expression: "total_xp >= 0" meaningful: true - name: valid_streak expression: ">= 0 for both streak fields" meaningful: true - name: valid_avg_score expression: "0-100" meaningful: true - table: course_tags constraints: - name: course_tags_name_not_empty expression: "length(trim(name)) > 0" meaningful: true - name: course_tags_slug_format expression: "slug ~ '^[a-z0-9-]+$'" meaningful: true - name: course_tags_color_format expression: "hex color format" meaningful: true - name: course_tags_usage_positive expression: "usage_count >= 0" meaningful: true - table: lessons constraints: - name: video_fields_required expression: "video type needs url and duration" meaningful: true financial: total_check_constraints: 30 notable: - "wallets: 6 constraints (balance equation, positivity, limits, status)" - "wallet_transactions: 7 constraints (amount, fee, transfer, status)" - "subscriptions: 7 constraints (price, dates, cancel, schedule)" - "invoices: 8 constraints (amounts, dates, status)" - "payments: 6 constraints (amount, refund, status)" - "refunds: 2 constraints (approval, failure)" - "wallet_limits: 4 constraints (XOR scope, min/max)" assessment: "Excellent constraint coverage - most comprehensive in the project" investment: notable: - "risk_questionnaire: score 0-100" - "withdrawal_requests: positive amount, valid fee" - "daily_performance: valid balances, movements, win_rate" - "distribution_history: positive amounts, balance validation" - "distribution_runs: valid counts" portfolio: notable: - "portfolio_allocations: target_percent 0-100, current_percent 0-100" - "portfolio_goals: target > 0, current >= 0, progress 0-100" ml: notable: - "llm_decisions: 6 CHECK constraints for types, actions, risk levels" - "llm_predictions: 5 CHECK constraints for directions, phases, levels" - "llm_prediction_outcomes: 4 CHECK constraints for outcome validation" - "backtest_runs: date range, config format" missing_constraints: - table: auth.notifications missing: "read_at consistency (is_read=true AND read_at IS NOT NULL)" severity: MEDIUM - table: trading.bots missing: "CHECK on max_position_size_pct (0-100), max_daily_loss_pct (0-100), max_drawdown_pct (0-100)" severity: MEDIUM - table: trading.bots missing: "CHECK that current_capital >= 0" severity: HIGH - table: trading.orders missing: "CHECK that quantity > 0, remaining_quantity >= 0" severity: HIGH - table: trading.orders missing: "CHECK consistency: filled_at NOT NULL when status = 'filled'" severity: MEDIUM - table: trading.positions missing: "CHECK that entry_price > 0, entry_quantity > 0, current_quantity >= 0" severity: HIGH - table: trading.signals missing: "CHECK that confidence_score between 0 and 1" severity: MEDIUM - table: trading.trading_metrics missing: "CHECK win_rate 0-100" severity: LOW - table: education.instructors missing: "verified_at consistency with is_verified" severity: LOW - table: market_data.tickers missing: "No constraints on symbol format" severity: LOW unique_constraints: - auth.users.email (UNIQUE) - auth.user_profiles.user_id (UNIQUE) - auth.oauth_accounts.(user_id, provider) (UNIQUE) - auth.oauth_accounts.(provider, provider_account_id) (UNIQUE) - auth.sessions.session_token (UNIQUE) - auth.email_verifications.token (UNIQUE) - auth.password_reset_tokens.token (UNIQUE) - auth.rate_limiting_config.endpoint (UNIQUE) - auth.user_push_tokens.token (UNIQUE) - trading.symbols.symbol (UNIQUE) - trading.watchlists.(user_id, name) (UNIQUE) - trading.watchlist_items.(watchlist_id, symbol_id) (UNIQUE) - trading.trading_metrics.(bot_id, metric_date) (UNIQUE) - trading.paper_balances.(user_id, asset) (UNIQUE) - education.categories.slug (UNIQUE) - education.courses.slug (UNIQUE) - education.modules.(course_id, display_order) (UNIQUE) - education.lessons.(module_id, display_order) (UNIQUE) - education.enrollments.(user_id, course_id) (UNIQUE) - education.progress.(user_id, lesson_id) (UNIQUE) - education.certificates.certificate_number (UNIQUE) - education.certificates.verification_code (UNIQUE) - education.certificates.(user_id, course_id) (UNIQUE) - education.user_gamification_profile.user_id (UNIQUE) - education.course_reviews.(user_id, course_id) (UNIQUE) - education.review_helpful_votes.(user_id, review_id) (UNIQUE) - education.course_tags.slug (UNIQUE) - education.course_tag_assignments.(course_id, tag_id) (UNIQUE) - financial.wallets.(user_id, wallet_type, currency) (UNIQUE) - financial.wallet_transactions.idempotency_key (UNIQUE) - financial.subscriptions.stripe_subscription_id (UNIQUE) - financial.invoices.stripe_invoice_id (UNIQUE) - financial.invoices.invoice_number (UNIQUE) - financial.payments.stripe_payment_intent_id (UNIQUE) - financial.refunds.stripe_refund_id (UNIQUE) - financial.customers.user_id (UNIQUE) - financial.customers.stripe_customer_id (UNIQUE) - financial.payment_methods.stripe_payment_method_id (UNIQUE) - investment.products.code (UNIQUE) - ml.model_versions.(model_id, version) (UNIQUE) - ml.prediction_outcomes.prediction_id (UNIQUE) - ml.llm_prediction_outcomes.prediction_id (UNIQUE) - ml.feature_store.(symbol, timeframe, timestamp) (UNIQUE) - llm.user_preferences.user_id (UNIQUE) - llm.user_memory.(user_id, memory_type, key) (UNIQUE) - feature_flags.flags.code (UNIQUE) - feature_flags.user_flags.(user_id, flag_id) (UNIQUE) # ============================================================================ # C. FUNCTIONS INVENTORY # ============================================================================ functions_inventory: global: - name: public.update_updated_at schema: public parameters: "()" return_type: TRIGGER purpose: "Unified updated_at auto-update function. Replaces schema-specific duplicates." used_by_triggers: - trg_drawing_tools_updated_at (trading.drawing_tools) - trg_drawing_templates_updated_at (trading.drawing_templates) - trg_course_tags_updated_at (education.course_tags) orphaned: false auth: - name: auth.update_updated_at schema: auth parameters: "()" return_type: TRIGGER purpose: "DEPRECATED - Schema-specific updated_at function" used_by_triggers: - trigger_update_users_updated_at - trigger_update_user_profiles_updated_at - trigger_update_oauth_accounts_updated_at - trigger_update_sessions_updated_at - trigger_update_rate_limiting_config_updated_at orphaned: false notes: "Marked DEPRECATED, should migrate to public.update_updated_at()" - name: auth.log_auth_event schema: auth parameters: "(auth_event_type, UUID, CITEXT, INET, TEXT, UUID, BOOLEAN, VARCHAR, JSONB)" return_type: UUID purpose: "Insert auth event into auth_logs table" used_by_triggers: [] orphaned: true notes: "Called from application code, not by any trigger" - name: auth.cleanup_expired_sessions schema: auth parameters: "(INTEGER DEFAULT 1000)" return_type: "TABLE(deleted_count INTEGER, execution_time_ms NUMERIC)" purpose: "Batch delete expired and old inactive sessions" used_by_triggers: [] orphaned: true notes: "Designed for scheduled execution (pg_cron)" - name: auth.create_user_profile schema: auth parameters: "()" return_type: TRIGGER purpose: "Auto-create user_profile on user insert" used_by_triggers: - trigger_create_user_profile (auth.users) orphaned: false trading: - name: trading.calculate_position_pnl schema: trading parameters: "(UUID, DECIMAL DEFAULT NULL)" return_type: JSONB purpose: "Calculate realized/unrealized PnL for a position" used_by_triggers: [] orphaned: true notes: "Utility function called from application code" - name: trading.update_bot_stats schema: trading parameters: "(UUID)" return_type: JSONB purpose: "Recalculate and update bot statistics from positions" used_by_triggers: [] orphaned: true notes: "Utility function called from application code" - name: trading.initialize_paper_balance schema: trading parameters: "(UUID, DECIMAL DEFAULT 10000, VARCHAR DEFAULT 'USDT')" return_type: UUID purpose: "Initialize paper trading balance for user" used_by_triggers: [] orphaned: false notes: "Called by trading.create_user_trading_defaults()" - name: trading.reset_paper_balance schema: trading parameters: "(UUID, VARCHAR DEFAULT 'USDT', DECIMAL DEFAULT NULL)" return_type: BOOLEAN purpose: "Reset paper trading balance to initial value" used_by_triggers: [] orphaned: true notes: "Utility function" - name: trading.update_paper_balance schema: trading parameters: "(UUID, VARCHAR, DECIMAL, VARCHAR)" return_type: BOOLEAN purpose: "Update paper balance (lock/unlock/pnl/deposit/withdrawal)" used_by_triggers: [] orphaned: true notes: "Utility function" - name: trading.create_user_trading_defaults schema: trading parameters: "()" return_type: TRIGGER purpose: "Create default watchlist and paper balance on user creation" used_by_triggers: [] orphaned: true notes: "CRITICAL - Trigger creation is COMMENTED OUT in DDL. Should be activated." - name: trading.update_price_alerts_timestamp schema: trading parameters: "()" return_type: TRIGGER purpose: "Update price_alerts updated_at" used_by_triggers: - trg_price_alerts_updated_at orphaned: false notes: "Should use public.update_updated_at() instead (DUP)" education: - name: education.update_updated_at_column schema: education parameters: "()" return_type: TRIGGER purpose: "DEPRECATED - Schema-specific updated_at function" used_by_triggers: - update_categories_updated_at - update_courses_updated_at - update_modules_updated_at - update_lessons_updated_at - update_enrollments_updated_at - update_progress_updated_at - update_quizzes_updated_at - update_quiz_questions_updated_at - update_user_gamification_profile_updated_at - update_course_reviews_updated_at orphaned: false - name: education.update_updated_at schema: education parameters: "()" return_type: TRIGGER purpose: "Another updated_at function (used by instructors)" used_by_triggers: - trg_instructors_updated_at orphaned: false notes: "Different name from update_updated_at_column - naming inconsistency" - name: education.update_enrollment_progress schema: education parameters: "()" return_type: TRIGGER purpose: "Recalculate enrollment progress when lesson completed" used_by_triggers: - update_enrollment_on_progress (education.progress) orphaned: false - name: education.auto_complete_enrollment schema: education parameters: "()" return_type: TRIGGER purpose: "Auto-complete enrollment when progress reaches 100%" used_by_triggers: - auto_complete_enrollment_trigger (education.enrollments) orphaned: false - name: education.generate_certificate_number schema: education parameters: "()" return_type: TRIGGER purpose: "Auto-generate certificate number and verification code" used_by_triggers: - generate_certificate_number_trigger (education.certificates) orphaned: false - name: education.update_course_rating_stats schema: education parameters: "()" return_type: TRIGGER purpose: "Update avg_rating and total_reviews on course" used_by_triggers: - update_course_rating_on_review_insert - update_course_rating_on_review_update - update_course_rating_on_review_delete orphaned: false - name: education.update_enrollment_count schema: education parameters: "()" return_type: TRIGGER purpose: "Update total_enrollments on course" used_by_triggers: - update_enrollment_count_on_insert - update_enrollment_count_on_delete orphaned: false - name: education.update_user_xp schema: education parameters: "(UUID, INTEGER)" return_type: VOID purpose: "Add XP, recalculate level, create level-up achievement" used_by_triggers: [] orphaned: true notes: "Utility function called from application code" - name: education.update_user_streak schema: education parameters: "(UUID)" return_type: VOID purpose: "Update daily activity streak" used_by_triggers: [] orphaned: false notes: "Called by trigger_update_streak" - name: education.trigger_update_streak schema: education parameters: "()" return_type: TRIGGER purpose: "Wrapper to call update_user_streak from trigger" used_by_triggers: - update_streak_on_activity (education.user_activity_log) orphaned: false - name: education.update_tag_usage_count schema: education parameters: "()" return_type: TRIGGER purpose: "Maintain denormalized usage_count in course_tags" used_by_triggers: - trg_course_tag_usage (education.course_tag_assignments) orphaned: false - name: education.update_videos_updated_at schema: education parameters: "()" return_type: TRIGGER purpose: "Updated_at for videos table" used_by_triggers: - trigger_update_videos_updated_at (education.videos) orphaned: false notes: "Another duplicated updated_at function" - name: education.soft_delete_video schema: education parameters: "(UUID)" return_type: VOID purpose: "Soft delete a video (set deleted_at, status='deleted')" used_by_triggers: [] orphaned: true notes: "Utility function" financial: - name: financial.update_timestamp schema: financial parameters: "()" return_type: TRIGGER purpose: "DEPRECATED - Schema-specific updated_at function" used_by_triggers: - trigger_wallets_updated_at - trigger_transactions_updated_at - trigger_subscriptions_updated_at - trigger_payments_updated_at - trigger_invoices_updated_at - trigger_exchange_rates_updated_at - trigger_wallet_limits_updated_at orphaned: false - name: financial.generate_invoice_number schema: financial parameters: "()" return_type: TRIGGER purpose: "Auto-generate invoice number with format INV-YYYYMM-XXXXXX" used_by_triggers: - trigger_invoice_number (financial.invoices) orphaned: false - name: financial.validate_wallet_balance schema: financial parameters: "()" return_type: TRIGGER purpose: "Ensure balance = available + pending, no negatives" used_by_triggers: - trigger_wallet_balance_validation (financial.wallets) orphaned: false - name: financial.audit_wallet_status_change schema: financial parameters: "()" return_type: TRIGGER purpose: "Log wallet status changes to audit log" used_by_triggers: - trigger_wallet_status_audit (financial.wallets) orphaned: false - name: financial.protect_completed_transactions schema: financial parameters: "()" return_type: TRIGGER purpose: "Prevent modification of completed transactions" used_by_triggers: - trigger_protect_completed_tx (financial.wallet_transactions) orphaned: false - name: financial.set_payment_timestamps schema: financial parameters: "()" return_type: TRIGGER purpose: "Auto-set succeeded_at and failed_at on payments" used_by_triggers: - trigger_payment_timestamps (financial.payments) orphaned: false - name: financial.set_subscription_ended_at schema: financial parameters: "()" return_type: TRIGGER purpose: "Auto-set ended_at when subscription cancelled" used_by_triggers: - trigger_subscription_ended_at (financial.subscriptions) orphaned: false - name: financial.validate_transaction_currency schema: financial parameters: "()" return_type: TRIGGER purpose: "Ensure transaction currency matches wallet currency" used_by_triggers: - trigger_transaction_currency_validation (financial.wallet_transactions) orphaned: false - name: financial.update_wallet_balance schema: financial parameters: "(UUID, DECIMAL, VARCHAR, UUID, UUID, VARCHAR, TEXT, JSONB)" return_type: TABLE purpose: "Safely update wallet balance with audit trail" used_by_triggers: [] orphaned: false notes: "Called by financial.process_transaction()" - name: financial.reserve_wallet_funds schema: financial parameters: "(UUID, DECIMAL, TEXT)" return_type: TABLE purpose: "Move funds from available to pending" used_by_triggers: [] orphaned: true notes: "Utility function" - name: financial.release_wallet_funds schema: financial parameters: "(UUID, DECIMAL, BOOLEAN, TEXT)" return_type: TABLE purpose: "Release reserved funds" used_by_triggers: [] orphaned: true notes: "Utility function" - name: financial.process_transaction schema: financial parameters: "(UUID, transaction_type, DECIMAL, currency_code, ...)" return_type: TABLE purpose: "Create and process wallet transactions atomically" used_by_triggers: [] orphaned: true notes: "Primary transaction processing function" - name: financial.complete_transaction schema: financial parameters: "(UUID)" return_type: TABLE purpose: "Complete a pending transaction" used_by_triggers: [] orphaned: true notes: "Utility function" - name: financial.get_exchange_rate schema: financial parameters: "(currency_code, currency_code, TIMESTAMPTZ)" return_type: "DECIMAL(18,8)" purpose: "Get exchange rate between currencies" used_by_triggers: [] orphaned: false notes: "Called by financial.convert_currency()" - name: financial.convert_currency schema: financial parameters: "(DECIMAL, currency_code, currency_code, TIMESTAMPTZ)" return_type: "DECIMAL(20,8)" purpose: "Convert amount between currencies" used_by_triggers: [] orphaned: true notes: "Utility function" - name: financial.ensure_single_default_payment_method schema: financial parameters: "()" return_type: TRIGGER purpose: "Ensure only one default payment method per user" used_by_triggers: - tr_ensure_single_default_payment_method (financial.payment_methods) orphaned: false - name: financial.check_expired_cards schema: financial parameters: "()" return_type: INTEGER purpose: "Mark expired cards - scheduled function" used_by_triggers: [] orphaned: true notes: "Run monthly via scheduler" investment: - name: investment.generate_withdrawal_request_number schema: investment parameters: "()" return_type: TRIGGER purpose: "Auto-generate withdrawal request number" used_by_triggers: - tr_generate_withdrawal_request_number orphaned: false portfolio: - name: portfolio.update_portfolio_updated_at schema: portfolio parameters: "()" return_type: TRIGGER purpose: "Updated_at for portfolios" used_by_triggers: - trg_portfolios_updated_at orphaned: false notes: "Yet another duplicated updated_at function" - name: portfolio.update_goal_progress schema: portfolio parameters: "()" return_type: TRIGGER purpose: "Recalculate goal progress percentage" used_by_triggers: - trg_portfolio_goals_progress orphaned: false ml: - name: ml.calculate_llm_prediction_accuracy schema: ml parameters: "(VARCHAR, INTEGER)" return_type: TABLE purpose: "Calculate LLM prediction accuracy statistics" used_by_triggers: [] orphaned: true notes: "Analytics function" - name: ml.calculate_llm_prediction_accuracy_by_phase schema: ml parameters: "(VARCHAR, INTEGER)" return_type: TABLE purpose: "Prediction accuracy by AMD phase" used_by_triggers: [] orphaned: true - name: ml.calculate_llm_prediction_accuracy_by_killzone schema: ml parameters: "(VARCHAR, INTEGER)" return_type: TABLE purpose: "Prediction accuracy by killzone" used_by_triggers: [] orphaned: true - name: ml.calculate_llm_prediction_accuracy_by_confluence schema: ml parameters: "(VARCHAR, INTEGER)" return_type: TABLE purpose: "Prediction accuracy by confluence factor count" used_by_triggers: [] orphaned: true - name: ml.get_active_risk_events schema: ml parameters: "(VARCHAR)" return_type: TABLE purpose: "Get active risk events for a symbol" used_by_triggers: [] orphaned: true - name: ml.check_circuit_breaker_status schema: ml parameters: "(VARCHAR)" return_type: TABLE purpose: "Check if circuit breaker is triggered for symbol" used_by_triggers: [] orphaned: true - name: ml.update_prediction_overlays_updated_at schema: ml parameters: "()" return_type: TRIGGER purpose: "Updated_at for prediction_overlays" used_by_triggers: - trigger_prediction_overlays_updated_at orphaned: false - name: ml.update_llm_signals_updated_at schema: ml parameters: "()" return_type: TRIGGER purpose: "Updated_at for llm_signals" used_by_triggers: - trg_llm_signals_updated_at orphaned: false market_data: - name: market_data.aggregate_5m_to_15m schema: market_data parameters: "(VARCHAR, TIMESTAMPTZ)" return_type: VOID purpose: "Aggregate 5-minute OHLCV data into 15-minute candles" used_by_triggers: [] orphaned: true notes: "Utility function for data pipeline" - name: market_data.aggregate_all_15m schema: market_data parameters: "()" return_type: VOID purpose: "Aggregate all tickers from 5m to 15m" used_by_triggers: [] orphaned: true feature_flags: - name: feature_flags.evaluate_flag schema: feature_flags parameters: "(VARCHAR, UUID, JSONB)" return_type: BOOLEAN purpose: "Evaluate if a feature flag is enabled for a user" used_by_triggers: [] orphaned: true notes: "Utility function called from application" - name: feature_flags.update_timestamp schema: feature_flags parameters: "()" return_type: TRIGGER purpose: "DEPRECATED - Schema-specific updated_at" used_by_triggers: - trg_flags_updated_at - trg_user_flags_updated_at orphaned: false # ============================================================================ # D. TRIGGERS INVENTORY # ============================================================================ triggers_inventory: auth: - name: trigger_update_users_updated_at table: auth.users event: BEFORE UPDATE function: auth.update_updated_at() naming_convention: trigger_update_* - name: trigger_update_user_profiles_updated_at table: auth.user_profiles event: BEFORE UPDATE function: auth.update_updated_at() naming_convention: trigger_update_* - name: trigger_update_oauth_accounts_updated_at table: auth.oauth_accounts event: BEFORE UPDATE function: auth.update_updated_at() naming_convention: trigger_update_* - name: trigger_update_sessions_updated_at table: auth.sessions event: BEFORE UPDATE function: auth.update_updated_at() naming_convention: trigger_update_* - name: trigger_update_rate_limiting_config_updated_at table: auth.rate_limiting_config event: BEFORE UPDATE function: auth.update_updated_at() naming_convention: trigger_update_* - name: trigger_create_user_profile table: auth.users event: AFTER INSERT function: auth.create_user_profile() naming_convention: trigger_* trading: - name: trg_price_alerts_updated_at table: trading.price_alerts event: BEFORE UPDATE function: trading.update_price_alerts_timestamp() naming_convention: trg_* - name: trg_drawing_tools_updated_at table: trading.drawing_tools event: BEFORE UPDATE function: public.update_updated_at() naming_convention: trg_* - name: trg_drawing_templates_updated_at table: trading.drawing_templates event: BEFORE UPDATE function: public.update_updated_at() naming_convention: trg_* - name: "(COMMENTED OUT) tr_create_user_trading_defaults" table: auth.users event: AFTER INSERT function: trading.create_user_trading_defaults() naming_convention: tr_* notes: "COMMENTED OUT in DDL - not active" education: - name: update_categories_updated_at table: education.categories event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_courses_updated_at table: education.courses event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_modules_updated_at table: education.modules event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_lessons_updated_at table: education.lessons event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_enrollments_updated_at table: education.enrollments event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_progress_updated_at table: education.progress event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_quizzes_updated_at table: education.quizzes event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_quiz_questions_updated_at table: education.quiz_questions event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_user_gamification_profile_updated_at table: education.user_gamification_profile event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_course_reviews_updated_at table: education.course_reviews event: BEFORE UPDATE function: education.update_updated_at_column() naming_convention: update_* - name: update_enrollment_on_progress table: education.progress event: "AFTER INSERT OR UPDATE WHEN (NEW.is_completed = true)" function: education.update_enrollment_progress() naming_convention: update_* - name: auto_complete_enrollment_trigger table: education.enrollments event: BEFORE UPDATE function: education.auto_complete_enrollment() naming_convention: "*_trigger" - name: generate_certificate_number_trigger table: education.certificates event: BEFORE INSERT function: education.generate_certificate_number() naming_convention: "*_trigger" - name: update_course_rating_on_review_insert table: education.course_reviews event: AFTER INSERT function: education.update_course_rating_stats() naming_convention: update_* - name: update_course_rating_on_review_update table: education.course_reviews event: "AFTER UPDATE WHEN (rating or is_approved changed)" function: education.update_course_rating_stats() naming_convention: update_* - name: update_course_rating_on_review_delete table: education.course_reviews event: AFTER DELETE function: education.update_course_rating_stats() naming_convention: update_* - name: update_enrollment_count_on_insert table: education.enrollments event: AFTER INSERT function: education.update_enrollment_count() naming_convention: update_* - name: update_enrollment_count_on_delete table: education.enrollments event: AFTER DELETE function: education.update_enrollment_count() naming_convention: update_* - name: update_streak_on_activity table: education.user_activity_log event: AFTER INSERT function: education.trigger_update_streak() naming_convention: update_* - name: trigger_update_videos_updated_at table: education.videos event: BEFORE UPDATE function: education.update_videos_updated_at() naming_convention: trigger_* - name: trg_instructors_updated_at table: education.instructors event: BEFORE UPDATE function: education.update_updated_at() naming_convention: trg_* - name: trg_course_tags_updated_at table: education.course_tags event: BEFORE UPDATE function: public.update_updated_at() naming_convention: trg_* - name: trg_course_tag_usage table: education.course_tag_assignments event: AFTER INSERT OR DELETE function: education.update_tag_usage_count() naming_convention: trg_* financial: - name: trigger_wallets_updated_at table: financial.wallets event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_transactions_updated_at table: financial.wallet_transactions event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_subscriptions_updated_at table: financial.subscriptions event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_payments_updated_at table: financial.payments event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_invoices_updated_at table: financial.invoices event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_exchange_rates_updated_at table: financial.currency_exchange_rates event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_wallet_limits_updated_at table: financial.wallet_limits event: BEFORE UPDATE function: financial.update_timestamp() naming_convention: trigger_* - name: trigger_invoice_number table: financial.invoices event: BEFORE INSERT function: financial.generate_invoice_number() naming_convention: trigger_* - name: trigger_wallet_balance_validation table: financial.wallets event: BEFORE INSERT OR UPDATE function: financial.validate_wallet_balance() naming_convention: trigger_* - name: trigger_wallet_status_audit table: financial.wallets event: BEFORE UPDATE function: financial.audit_wallet_status_change() naming_convention: trigger_* - name: trigger_protect_completed_tx table: financial.wallet_transactions event: BEFORE UPDATE function: financial.protect_completed_transactions() naming_convention: trigger_* - name: trigger_payment_timestamps table: financial.payments event: BEFORE UPDATE function: financial.set_payment_timestamps() naming_convention: trigger_* - name: trigger_subscription_ended_at table: financial.subscriptions event: BEFORE UPDATE function: financial.set_subscription_ended_at() naming_convention: trigger_* - name: trigger_transaction_currency_validation table: financial.wallet_transactions event: BEFORE INSERT function: financial.validate_transaction_currency() naming_convention: trigger_* - name: tr_ensure_single_default_payment_method table: financial.payment_methods event: "BEFORE INSERT OR UPDATE OF is_default WHEN (NEW.is_default = TRUE)" function: financial.ensure_single_default_payment_method() naming_convention: tr_* investment: - name: tr_generate_withdrawal_request_number table: investment.withdrawal_requests event: BEFORE INSERT function: investment.generate_withdrawal_request_number() naming_convention: tr_* - name: trg_agent_executions_updated_at table: investment.agent_executions event: BEFORE UPDATE function: public.update_updated_at() naming_convention: trg_* portfolio: - name: trg_portfolios_updated_at table: portfolio.portfolios event: BEFORE UPDATE function: portfolio.update_portfolio_updated_at() naming_convention: trg_* - name: trg_portfolio_allocations_updated_at table: portfolio.portfolio_allocations event: BEFORE UPDATE function: public.update_updated_at() naming_convention: trg_* - name: trg_portfolio_goals_updated_at table: portfolio.portfolio_goals event: BEFORE UPDATE function: public.update_updated_at() naming_convention: trg_* - name: trg_portfolio_goals_progress table: portfolio.portfolio_goals event: BEFORE UPDATE function: portfolio.update_goal_progress() naming_convention: trg_* ml: - name: trigger_prediction_overlays_updated_at table: ml.prediction_overlays event: BEFORE UPDATE function: ml.update_prediction_overlays_updated_at() naming_convention: trigger_* - name: trg_llm_signals_updated_at table: ml.llm_signals event: BEFORE UPDATE function: ml.update_llm_signals_updated_at() naming_convention: trg_* feature_flags: - name: trg_flags_updated_at table: feature_flags.flags event: BEFORE UPDATE function: feature_flags.update_timestamp() naming_convention: trg_* - name: trg_user_flags_updated_at table: feature_flags.user_flags event: BEFORE UPDATE function: feature_flags.update_timestamp() naming_convention: trg_* trigger_naming_analysis: conventions_found: - "trigger_*: Used in auth (5), financial (14), education/videos (1), ml (1)" - "trg_*: Used in trading (3), education/new (3), investment (1), portfolio (3), ml (1), feature_flags (2)" - "update_*: Used in education (12)" - "tr_*: Used in financial (1), investment (1)" - "*_trigger: Used in education (2)" inconsistency_level: HIGH recommendation: "Standardize to trg_ prefix across all schemas" # ============================================================================ # E. INTER-SCHEMA DEPENDENCY MAP # ============================================================================ inter_schema_dependencies: auth_dependents: description: "All schemas that reference auth.users" schemas: - trading: 8 FKs - education: 14 FKs - financial: 8 FKs - investment: 3 FKs - portfolio: 2 FKs - ml: 1 FK - llm: 4 FKs - audit: 9 FKs - feature_flags: 2 FKs total_cross_schema_fks_to_auth: 51 schema_dependency_matrix: auth: [] # No outbound dependencies to other schemas trading: depends_on: [auth] education: depends_on: [auth] financial: depends_on: [auth] investment: depends_on: [auth] portfolio: depends_on: [auth] ml: depends_on: [auth] llm: depends_on: [auth] audit: depends_on: [auth] market_data: [] # No cross-schema FKs feature_flags: depends_on: [auth] circular_dependencies: NONE notes: | All schemas follow a clean star topology with auth as the central hub. No schema depends on another non-auth schema via FK constraints. market_data is completely isolated (no cross-schema FKs at all). weak_relationships: - from: trading.signals.symbol to: trading.symbols.symbol type: "Logical (VARCHAR match, no FK)" recommendation: "Consider adding symbol_id FK for referential integrity" - from: trading.trades.symbol to: trading.symbols.symbol type: "Logical (VARCHAR match, no FK)" recommendation: "Add symbol_id FK - inconsistent with orders/positions which use symbol_id" - from: trading.bots.symbols to: trading.symbols.symbol type: "Logical (VARCHAR[] array, no FK possible)" recommendation: "Consider junction table trading.bot_symbols for proper FK" - from: ml.llm_predictions.symbol to: "trading.symbols or market_data.tickers" type: "Logical (VARCHAR, no FK)" recommendation: "LOW - ML often denormalizes, but consider FK to market_data.tickers" - from: ml.feature_store.symbol to: "trading.symbols or market_data.tickers" type: "Logical (VARCHAR, no FK)" recommendation: "LOW - Same as above" - from: ml.llm_signals.symbol to: "trading.symbols or market_data.tickers" type: "Logical (VARCHAR, no FK)" recommendation: "LOW - Same as above" - from: ml.predictions.symbol to: "trading.symbols or market_data.tickers" type: "Logical (VARCHAR, no FK)" recommendation: "LOW - Same as above" - from: "investment.accounts/transactions" to: "financial.wallets" type: "Logical (investment wallets managed through financial.wallets)" recommendation: "MEDIUM - Consider FK from investment.accounts to financial.wallets" - from: education.courses.instructor_id to: education.instructors.user_id type: "Should reference instructors table instead of auth.users" recommendation: "MEDIUM - Migration pending as documented in 17-instructors.sql" schema_creation_order: recommended: - "1. public (00-extensions.sql, 00-global-types.sql, 00-global-functions.sql)" - "2. 01-schemas.sql (create all schemas)" - "3. auth (00-extensions, 01-enums, tables 01-12, functions, 99-deferred)" - "4. trading (00-enums, tables 01-13, functions)" - "5. education (00-enums, tables 01-19, functions)" - "6. financial (00-enums, tables 01-11, functions, triggers, views)" - "7. investment (00-enums, tables 01-10)" - "8. portfolio (00-enums, tables 01-05)" - "9. ml (00-enums, tables 01-12, functions)" - "10. llm (00-enums, 00-extensions, tables 01-05)" - "11. audit (00-enums, tables 01-07)" - "12. market_data (00-enums, tables 01-04, functions)" - "13. feature_flags (tables/01-flags.sql creates schema + tables)" # ============================================================================ # F. ISSUES FOUND # ============================================================================ issues_summary: critical: count: 2 items: - id: CRIT-001 title: "trading.create_user_trading_defaults trigger is COMMENTED OUT" description: | The trigger that creates default watchlists and paper trading balances for new users is commented out in 04-create_default_watchlist.sql. This means new users will NOT get their default trading setup. affected: "trading.create_user_trading_defaults() / auth.users trigger" recommendation: "Uncomment the trigger or activate it in a post-deploy script" - id: CRIT-002 title: "Missing CHECK constraints on trading.orders and trading.positions" description: | Critical financial columns lack CHECK constraints: - orders.quantity has no > 0 check - orders.remaining_quantity has no >= 0 check - positions.entry_price has no > 0 check - positions.entry_quantity has no > 0 check - positions.current_quantity has no >= 0 check - bots.current_capital has no >= 0 check These could allow invalid data that corrupts PnL calculations. affected: "trading.orders, trading.positions, trading.bots" recommendation: "Add CHECK constraints for all amount/quantity/price columns" high: count: 5 items: - id: HIGH-001 title: "5 duplicated update_updated_at functions across schemas" description: | Despite public.update_updated_at() existing as unified replacement, the following schema-specific duplicates still exist AND are actively used: - auth.update_updated_at() - 5 triggers - education.update_updated_at_column() - 10 triggers - education.update_updated_at() - 1 trigger (different name!) - education.update_videos_updated_at() - 1 trigger - financial.update_timestamp() - 7 triggers - feature_flags.update_timestamp() - 2 triggers - trading.update_price_alerts_timestamp() - 1 trigger - portfolio.update_portfolio_updated_at() - 1 trigger - ml.update_prediction_overlays_updated_at() - 1 trigger - ml.update_llm_signals_updated_at() - 1 trigger Only trading (drawing_tools, drawing_templates), education (course_tags), portfolio (allocations, goals), and investment (agent_executions) use the unified public function. affected: "All schemas" recommendation: "Migrate all triggers to use public.update_updated_at()" - id: HIGH-002 title: "Trigger naming convention highly inconsistent" description: | 5 different naming patterns found: - trigger_* (auth, financial) - trg_* (trading, portfolio, ml, feature_flags) - update_* (education) - tr_* (financial, investment) - *_trigger (education) affected: "All schemas" recommendation: "Standardize to trg_{table}_{event} format" - id: HIGH-003 title: "trading.signals and trading.trades use VARCHAR symbol instead of FK" description: | trading.signals.symbol and trading.trades.symbol use VARCHAR(20) for the symbol name, while trading.orders and trading.positions properly use symbol_id UUID FK to trading.symbols. This inconsistency means: - No referential integrity on signals and trades - Potential for orphaned/invalid symbol references - Inconsistent query patterns across the trading module affected: "trading.signals, trading.trades" recommendation: "Add symbol_id UUID FK column and migrate data" - id: HIGH-004 title: "market_data.tickers uses SERIAL PK while all other tables use UUID" description: | market_data.tickers has id SERIAL PRIMARY KEY while every other table in the entire database uses UUID PKs. This creates a type mismatch that complicates cross-schema queries and any future FK relationships. affected: "market_data.tickers, market_data.ohlcv_5m, market_data.ohlcv_15m" recommendation: "Consider migrating to UUID PK for consistency, or document the exception" - id: HIGH-005 title: "Missing updated_at triggers on several tables" description: | Tables with updated_at column but NO trigger to auto-update: - auth.notifications (no updated_at column either - only created_at) - trading.symbols (has updated_at, no trigger) - trading.bots (has updated_at, no trigger) - trading.orders (has updated_at, no trigger) - trading.positions (has updated_at, no trigger) - trading.watchlists (has updated_at, no trigger) - trading.watchlist_items (has updated_at, no trigger) - trading.signals (no updated_at column) - trading.trading_metrics (has updated_at, no trigger) - trading.paper_balances (has updated_at, no trigger) - investment.products (has updated_at, no trigger) - financial.customers (has updated_at, no trigger) - market_data.tickers (has updated_at, no trigger) affected: "Multiple tables across trading, investment, financial, market_data" recommendation: "Add BEFORE UPDATE triggers using public.update_updated_at()" medium: count: 6 items: - id: MED-001 title: "feature_flags schema not declared in 01-schemas.sql" description: | All other schemas are created in 01-schemas.sql, but feature_flags creates its own schema inline in tables/01-flags.sql. affected: "feature_flags" recommendation: "Add feature_flags to 01-schemas.sql for consistency" - id: MED-002 title: "education.courses.instructor_id references auth.users instead of education.instructors" description: | The instructors table exists but courses still reference auth.users directly. Migration is documented in 17-instructors.sql comments but not executed. affected: "education.courses, education.instructors" recommendation: "Execute the documented migration" - id: MED-003 title: "Inconsistent ON DELETE behavior for audit columns" description: | Columns like approved_by, reviewed_by, requested_by across tables have inconsistent ON DELETE behavior - some have no ON DELETE specified (defaults to NO ACTION), others have SET NULL. Should standardize. affected: "education.course_reviews.approved_by, financial.refunds.requested_by/approved_by, investment.withdrawal_requests.reviewed_by, audit.security_events.reviewed_by" recommendation: "Standardize to ON DELETE SET NULL for all audit/reference columns" - id: MED-004 title: "auth.auth_logs is partitioned but no FK to auth.users" description: | Partitioned tables have limitations with FKs in PostgreSQL. auth_logs.user_id and auth_logs.session_id have no FK constraints. While this is a known PG limitation, there is no application-level validation documented. affected: "auth.auth_logs" recommendation: "Document the constraint limitation and add application-level validation" - id: MED-005 title: "Enum naming conflicts between schemas" description: | financial.transaction_type and investment.transaction_type share the same enum name. Both are marked DEPRECATED with planned rename but migration not yet executed. affected: "financial.transaction_type, investment.transaction_type" recommendation: "Execute the rename migration as documented" - id: MED-006 title: "Deprecated enum trading.timeframe still in use" description: | public.trading_timeframe exists as the unified replacement, but trading.timeframe is still actively used by: trading.bots, trading.signals, trading.drawing_tools, ml.feature_store. Migration documented but not executed. affected: "trading.timeframe, public.trading_timeframe" recommendation: "Execute the timeframe unification migration" low: count: 4 items: - id: LOW-001 title: "auth.users audit columns (created_by_id, updated_by_id) have no FK" description: "Self-referencing FKs not defined for audit trail columns" recommendation: "Add FK with ON DELETE SET NULL" - id: LOW-002 title: "No CHECK constraint on trading.bots percentage fields" description: "max_position_size_pct, max_daily_loss_pct, max_drawdown_pct have no 0-100 range check" recommendation: "Add CHECK constraints" - id: LOW-003 title: "trading.signals.confidence_score lacks range constraint" description: "Documented as 0.0000 to 1.0000 but no CHECK constraint" recommendation: "Add CHECK (confidence_score >= 0 AND confidence_score <= 1)" - id: LOW-004 title: "market_data.ohlcv_5m_staging has no constraints" description: "Staging table has zero constraints - acceptable but could have basic data validation" recommendation: "Optional - add basic NOT NULL constraints on critical columns" total_issues: critical: 2 high: 5 medium: 6 low: 4 total: 17