Appearance
Database Tables
126 tables discovered across supabase/migrations/ (3 migration files). "Defined in" is the first migration that creates the table; later migrations may alter it. Descriptions come from COMMENT ON TABLE. See Database architecture.
| Table | Defined in | Description |
|---|---|---|
ad_placements | 20260708000000_baseline_prod_schema.sql | Defines available ad slots across the application. |
ad_rules | 20260708000000_baseline_prod_schema.sql | Defines where, when, and how an ad is displayed. |
ad_special_users | 20260708000000_baseline_prod_schema.sql | Whitelist of users for special ad viewing modes (Advertiser, Reviewer). |
admin_task_reminders | 20260708000000_baseline_prod_schema.sql | — |
ads | 20260708000000_baseline_prod_schema.sql | Stores individual ad creatives and their metadata. |
community_posts | 20260708000000_baseline_prod_schema.sql | Contains user-generated posts for the community feed, including text, tags, and engagement counts. |
daily_psychology_checkins | 20260708000000_baseline_prod_schema.sql | — |
daily_scheduled_clean_up_tables_list | 20260708000000_baseline_prod_schema.sql | Configuration table for daily automated database cleanup via Edge Function. Admins manage table cleanup settings here. |
direct_messages | 20260708000000_baseline_prod_schema.sql | — |
edge_function_configs | 20260708000000_baseline_prod_schema.sql | Stores configurations for Supabase Edge Functions, like API keys or parameters. |
edge_function_runs | 20260708000000_baseline_prod_schema.sql | Persistent execution log for instrumented Supabase Edge Functions. Each row = one completed invocation. Written by the EF at job completion using the service-role client. Feeds the Edge Functions Monitoring Dashboard. |
feature_flags | 20260708000000_baseline_prod_schema.sql | — |
finfluencify_announcement_recipients | 20260708000000_baseline_prod_schema.sql | Tracks read status per student per announcement. Enables read rate calculation, unread tracking, and engagement metrics. |
finfluencify_announcements | 20260708000000_baseline_prod_schema.sql | Main announcements table for broadcasting messages to students. Tracks read status, priority, and notification delivery methods. |
finfluencify_certificate_templates | 20260708000000_baseline_prod_schema.sql | Platform-managed certificate HTML templates. Trainers pick one per course. Admins manage this catalogue. Variables in html_template use syntax. |
finfluencify_commission_brackets | 20260708000000_baseline_prod_schema.sql | Price-range commission rules. Level 2 in the 4-tier commission resolution hierarchy — evaluated only when the trainer has no personal commission override (Level 1). Prices stored as NUMERIC(10,2) rupees (INR). One row must be marked is_default = true at all times to guarantee full price coverage; the admin UI enforces this by blocking deletion of the default bracket. |
finfluencify_course_analytics | 20260708000000_baseline_prod_schema.sql | Daily analytics fact table. Aggregated course metrics for dashboard reporting and trend analysis. One record per course per day. Used for performance reporting and business analytics. |
finfluencify_course_batches | 20260708000000_baseline_prod_schema.sql | Batch cohorts table. Groups students into time-bound batches for cohort-based courses. Each batch has separate start/end dates and capacity limits. Supports live session scheduling. |
finfluencify_course_enrollments | 20260708000000_baseline_prod_schema.sql | Student enrollments table. Central table for student-course relationships. Tracks payment status, access grants, and progress. Free courses: access_granted immediately. Paid courses: student submits payment ID, trainer verifies and grants access. |
finfluencify_course_inquiries | 20260708000000_baseline_prod_schema.sql | Pre-enrollment leads table. Captures inquiries from prospects before they enroll. Tracks follow-up communication and conversion status. Used for CRM-style lead management. Unregistered users can inquire about courses. |
finfluencify_course_lesson_progress | 20260708000000_baseline_prod_schema.sql | Per-lesson progress table. Fine-grained progress tracking for each student-lesson combination. Stores video watch duration, quiz scores, assignment status, etc. |
finfluencify_course_lessons | 20260708000000_baseline_prod_schema.sql | Individual lessons table. Stores individual lessons within modules. Supports multiple content types: video, document, quiz, assignment, live-session. Tracks progress metrics for each lesson. |
finfluencify_course_modules | 20260708000000_baseline_prod_schema.sql | Course structure/sections table. Organizes courses into logical modules/sections. Each module contains multiple lessons. Supports drag-drop reordering via position field. |
finfluencify_course_payment_verifications | 20260708000000_baseline_prod_schema.sql | Manual payment verification fallback. Used exclusively for enrollments that cannot go through the primary automated Razorpay flow: offline bank transfers, enterprise / bulk deals arranged outside the platform, and admin-granted access overrides. In this flow the student self-reports a payment ID, the trainer manually confirms it in their Razorpay dashboard, and an admin (or the trainer) flips access_granted to true. Access is therefore never instant — it depends on a human taking action. PRIMARY PAYMENT FLOW — finfluencify_payment_orders: All standard online purchases go through the Razorpay checkout integration. finfluencify_payment_orders is the single source of truth for every automated transaction: it is created server-side at checkout initiation, updated by the Razorpay webhook on payment.captured / payment.failed / refund / dispute events, and drives enrollment automatically without any manual step. The payment_flow column on finfluencify_course_enrollments distinguishes the two paths: payment_flow = 'razorpay' (automated) vs 'manual' (this table) vs 'free' (no payment). |
finfluencify_course_promotions | 20260708000000_baseline_prod_schema.sql | Course promotion codes table. Course-specific coupon codes for trainer-run campaigns. Tracks usage per course and enforces quota limits. Independent from plan-wide offers. |
finfluencify_course_reviews_and_ratings | 20260708000000_baseline_prod_schema.sql | Course reviews table. Stores course reviews and ratings from enrolled students. Can be moderated by admins. Ratings feed into course's avg_rating metric (updated by trigger). |
finfluencify_courses | 20260708000000_baseline_prod_schema.sql | Core course metadata table. Stores all information about courses including pricing, approval workflow, SEBI compliance, and analytics. Single course record per trainer. Key features: Approval workflow tracking (draft → review → approved → published), SEBI compliance flags, multiple pricing models, soft deletion via status field. |
finfluencify_discount_codes | 20260708000000_baseline_prod_schema.sql | Trainer-managed discount/coupon codes. Validated server-side by the payment initiation function. Students have no direct access to this table. |
finfluencify_engagement_metrics | 20260708000000_baseline_prod_schema.sql | Daily engagement aggregation per course. Used for dashboard reporting and trend analysis over time. |
finfluencify_enquiry_submissions | 20260708000000_baseline_prod_schema.sql | Visitor lead capture submissions from trainer public pages (/:slug). Rate-limit the INSERT endpoint to prevent spam. Visitor PII is scoped to the trainer owning that slug — RLS prevents cross-trainer reads. Consider CAPTCHA (Cloudflare Turnstile) in Phase 3. |
finfluencify_lesson_qa | 20260708000000_baseline_prod_schema.sql | Single-level threaded Q&A for course lessons. parent_id = NULL for top-level questions, non-NULL for replies. |
finfluencify_link_in_bio | 20260708000000_baseline_prod_schema.sql | FinFluencify Link-in-Bio editable config. One row per trainer. Created on first page save after slug claim. All mutable UI state is JSONB to avoid schema changes for minor fields. theme: slug-format regex only — no fixed IN-list so new theme packs never need a migration. Free: dark|light|soft-pink|forest|ocean|midnight. Premium: gradient-sunset|gradient-aurora|gradient-ocean|gradient-midnight| gradient-neon|glassmorphism|neon-glow|carbon. Fully-custom: "custom". accent_color: named token (orange|blue|green|purple|red|pink|teal|gold) OR any valid CSS 3/6-digit hex string (e.g. #FF6B35). custom_theme: overrides for bg color/gradient, accent gradient, font family, bg image URL, and card style — Linktree-style premium theme scope. The analytics columns (total_views, total_clicks) are written only by the record-page-view Edge Function — not by the client directly. |
finfluencify_live_session_invitees | 20260708000000_baseline_prod_schema.sql | Email-based invitees for finfluencify_live_sessions rows with audience_type='invitees'. Covers both platform users (matched by account email) and external guests with no platform account. |
finfluencify_live_session_participants | 20260708000000_baseline_prod_schema.sql | Live-class attendance, written by finfluencify-zoom-webhook on participant_joined/_left. Idempotent per (session_id, zoom_participant_uuid). SERVICE-ROLE ONLY — RLS denies authenticated access; trainer-facing attendance reads go through a SECURITY DEFINER RPC in the analytics phase. |
finfluencify_live_session_recordings | 20260708000000_baseline_prod_schema.sql | Live-class recording METADATA ONLY (share_url/play_url/passcode/duration/size). The file stays on Zoom's cloud — nothing is downloaded. Written by finfluencify-zoom-webhook on recording.completed. SERVICE-ROLE ONLY — reads go through get_finfluencify_live_session_recordings(). |
finfluencify_live_session_reminders | 20260708000000_baseline_prod_schema.sql | Reminder idempotency ledger. The reminder-sweep cron inserts one row to claim a (session, window, channel) before enqueuing; a UNIQUE violation means the reminder already fired and is skipped. SERVICE-ROLE ONLY. |
finfluencify_live_sessions | 20260708000000_baseline_prod_schema.sql | Trainer-scheduled Zoom live classes. audience_type drives who is invited: standalone (nobody — trainer-only), course (that course's enrolled+access_granted students), invitees (specific emails in finfluencify_live_session_invitees). host_start_url_enc/passcode_enc are encrypted and never exposed to RPC callers. |
finfluencify_payment_orders | 20260708000000_baseline_prod_schema.sql | Single source of truth for every Razorpay checkout session on the platform. One row per checkout attempt (not per enrollment). Created by the payment initiation function when a student initiates checkout. Updated by the Razorpay webhook handler on payment events. Financial records are immutable by design — courses and trainer profiles use ON DELETE RESTRICT to prevent silent deletion of payment history. |
finfluencify_plan_audit_log | 20260708000000_baseline_prod_schema.sql | Plan audit trail table. Immutable audit log of all plan changes. Required for regulatory compliance and dispute resolution. Records: assignments, upgrades, downgrades, discounts, approvals. IMMUTABLE (trigger prevents updates). |
finfluencify_plan_definitions | 20260708000000_baseline_prod_schema.sql | Subscription plan definitions table. Versioned plan tiers for trainers. New versions allow changing limits without affecting existing customers. Versions are soft-deleted (deprecated_at) for historical tracking. Only is_active=true versions with effective_from <= NOW() are current. |
finfluencify_plan_features | 20260708000000_baseline_prod_schema.sql | Feature flags table. Feature enablement configuration for each plan version. Supports enabling/disabling features without database migration. Easy feature experimentation and A/B testing. |
finfluencify_poll_options | 20260708000000_baseline_prod_schema.sql | Answer choices for polls. Position determines display order. response_count is auto-updated via trigger. |
finfluencify_poll_responses | 20260708000000_baseline_prod_schema.sql | Individual student responses to polls. One row per response, updated via trigger for multiple-choice. Allows anonymous/non-anonymous tracking. |
finfluencify_polls | 20260708000000_baseline_prod_schema.sql | Main poll table for quick engagement feedback from students. Polls are time-bound and track response rates. |
finfluencify_special_offers | 20260708000000_baseline_prod_schema.sql | Special promotions and discounts table. Trainer-specific or global discounts. Tracks usage and enforces max_uses limit. Supports various offer types and discount amounts. |
finfluencify_stream_webhook_events | 20260708000000_baseline_prod_schema.sql | Cloudflare Stream webhook event log. Stores every inbound Cloudflare Stream webhook delivery. The UNIQUE constraint on event_id guarantees exactly-once processing even when CF retries on timeout. Rows are not deleted by the application — use a cron job to purge rows older than 90 days. |
finfluencify_student_certificates | 20260708000000_baseline_prod_schema.sql | One row per issued certificate. Created by finfluencify-generate-certificate Edge Function. pdf_url is a signed Supabase Storage URL; re-generate from storage_path when it expires. |
finfluencify_students | 20260708000000_baseline_prod_schema.sql | FinFluencify students/mentees table. Stores basic student information including contact details, enrollment status, and engagement metrics. Follows finfluencify_ prefix pattern. Multi-tenant (per-trainer) data. Supports soft deletion via is_active flag. |
finfluencify_survey_question_responses | 20260708000000_baseline_prod_schema.sql | Individual student answers to survey questions. Uses flexible JSONB format to support different question types without multiple columns. |
finfluencify_survey_questions | 20260708000000_baseline_prod_schema.sql | Individual questions within surveys. Supports multiple question types with flexible options, validation rules, and conditional logic. |
finfluencify_survey_responses | 20260708000000_baseline_prod_schema.sql | Survey response sessions (one per student per survey). Tracks completion status, time spent, and progress percentage. |
finfluencify_surveys | 20260708000000_baseline_prod_schema.sql | Main survey table for collecting detailed feedback with multiple questions of various types (single-choice, text, rating, etc.) |
finfluencify_trainer_active_plans | 20260708000000_baseline_prod_schema.sql | Current trainer subscription assignments. Single active record per trainer. When upgrading/downgrading, old record set to is_active=false and new record created as is_active=true. Supports subscription lifecycle tracking. |
finfluencify_trainer_zoom_accounts | 20260708000000_baseline_prod_schema.sql | Per-trainer Zoom OAuth connection. Stores AES-256-GCM encrypted access/refresh tokens (encrypted by the edge functions with ZOOM_TOKEN_ENC_KEY before storage) and connection status. SERVICE-ROLE ONLY — RLS denies all authenticated access; clients read status via get_finfluencify_zoom_connection_status(). |
finfluencify_zoom_webhook_events | 20260708000000_baseline_prod_schema.sql | Zoom webhook idempotency + audit log. UNIQUE(dedupe_key = SHA-256 of raw body) guarantees exactly-once processing across Zoom redeliveries. Every delivery is recorded before any state change. SERVICE-ROLE ONLY — RLS denies all authenticated access. Purge rows older than ~90 days via cron. |
fo_market_holidays | 20260708000000_baseline_prod_schema.sql | Stores Futures and Options (F&O) segment market holidays fetched from NSE. |
giftnifty_status | 20260708000000_baseline_prod_schema.sql | — |
goals | 20260708000000_baseline_prod_schema.sql | Stores user-defined financial or personal goals with tracking metrics. |
habit_tracker_badges | 20260708000000_baseline_prod_schema.sql | Stores badges earned by users for achieving habit milestones. |
habit_tracker_entries | 20260708000000_baseline_prod_schema.sql | Tracks completion status of habits for each user on specific dates. |
habit_tracker_habits | 20260708000000_baseline_prod_schema.sql | Defines user-created habits for tracking, including frequency, goals, and type. |
index_expiry_map | 20260708000000_baseline_prod_schema.sql | Stores index names and their expiry dates for Option Chain data fetching. |
index_master | 20260708000000_baseline_prod_schema.sql | NSE index master table — primary source for India VIX open/high/low/close/last. Columns include: index_symbol, "open", high, low, "last", previous_close, percent_change, advances, declines, unchanged, timestamp_ist. Authoritative VIX source: WHERE index_symbol = 'INDIA VIX'. Verified: prod query 2026-04-23 returned 'INDIA VIX' (all-caps, not title case). |
maintenance_window_configurations | 20260708000000_baseline_prod_schema.sql | Maintenance window configurations with persistence, audit trail, and soft-delete support. Supports indefinite and time-bounded maintenance notifications. |
message_threads | 20260708000000_baseline_prod_schema.sql | — |
mv_refresh_registry | 20260708000000_baseline_prod_schema.sql | Registry governing the refresh-materialized-views Edge Function cron cycle. Each row = one materialized view refreshed every ~2 minutes during market hours. COLUMNS: refresh_order — execution sequence; lower = refreshed first. Gap by 10s to allow inserting new rows without renumbering. enabled — set FALSE to temporarily pause a view without deleting it. refresh_method — CONCURRENT (non-blocking, requires unique index on MV) or STANDARD (exclusive lock during refresh). Always prefer CONCURRENT for views queried by users during market hours. retry_wait_ms — reserved for future use; currently unused (inter-view sleep was removed in 014 as it added dead time with no benefit). last_refresh* — written back by the EF after each refresh attempt. SCALING — when to add a second cron EF: If mv_refresh.cycle_complete duration_ms consistently exceeds 100s, split into two priority groups by adding a priority_group column (e.g. A/B): Group A (refresh_order 10–70): market-critical intraday MVs, 2-min cron. Group B (refresh_order 80+): screener/analytics MVs, 5-min cron. Create supabase/functions/refresh-materialized-views-b/ using the same index.ts pattern, filtering WHERE priority_group = 'B'. No changes to refresh_single_mv or any MV definitions required. ADDING A NEW MV: INSERT INTO mv_refresh_registry (view_name, refresh_order, refresh_method) VALUES ('public.mv_your_new_view', <next_order>, 'CONCURRENT'); Ensure the MV has a unique index if using CONCURRENT. refresh_order gaps of 10 allow insertion without renumbering existing rows. |
notification_events | 20260708000000_baseline_prod_schema.sql | Fan-out broadcast log. Each row represents a single notification event (e.g., a guide published to all users) that must be delivered to many recipients. The notifications-fanout Edge Function (cron, every 5 min) polls pending rows, pages through profiles, filters by notification_preferences, and batch-inserts individual rows into the notifications table. This decoupled design eliminates the O(N-users) transaction-blocking INSERT that occurred when create_new_guide_notification() wrote directly into notifications inside the guide publish transaction. RLS denies all authenticated access — only the service-role key (used by the fan-out EF) can read and write this table. |
notification_preferences | 20260708000000_baseline_prod_schema.sql | Per-user, per-category notification delivery preferences. Seeded with all 6 categories defaulting to in_app = TRUE for every existing user (and for new users on their first call to get_notification_preferences()). The fan-out worker consults this table before inserting notification rows so that users who have opted out of a category are silently skipped. Email and push columns are reserved for future delivery channels and default to FALSE. |
notifications | 20260708000000_baseline_prod_schema.sql | — |
nse_all_indices | 20260708000000_baseline_prod_schema.sql | NSE index snapshot table (13 rows — major NIFTY indices only). Ingested by the collect-nse-indices Edge Function. DOES NOT contain INDIA VIX. Use index_master for VIX data. Primary key: index_symbol. |
nse_all_stocks_traded | 20260708000000_baseline_prod_schema.sql | Primary NSE stock tick table. All NSE listed symbols, polled during market hours. autovacuum_vacuum_scale_factor tuned to 2% (from 20%) in migration 129 to keep dead tuple bloat below 1–3 days of cleanup deletions instead of 15–30 days. Avoids heap bloat and stale planner statistics from daily cleanup batches. |
nse_banknifty_contributors | 20260708000000_baseline_prod_schema.sql | — |
nse_banknifty_historical_data_daily | 20260708000000_baseline_prod_schema.sql | — |
nse_equity_daily_snapshot | 20260708000000_baseline_prod_schema.sql | Incremental staging table for mv_equity_daily_eod. One row per (symbol, trade_date). Maintained by refresh_equity_daily_snapshot() which upserts only today's data from nse_all_stocks_traded. Replaces the slow DISTINCT ON full-table scan. |
nse_equity_session_stats | 20260708000000_baseline_prod_schema.sql | Incremental staging table for mv_equity_volume_surges. One row per (symbol, session_date). session_volume = MAX(total_traded_volume) observed for the symbol on that calendar day (IST). Maintained by refresh_nse_equity_session_stats() which upserts only today's data. Replaces the expensive 8-session GROUP BY full-scan in the original MV. |
nse_finserv_contributors | 20260708000000_baseline_prod_schema.sql | — |
nse_finserv_historical_data_daily | 20260708000000_baseline_prod_schema.sql | — |
nse_index_weightages | 20260708000000_baseline_prod_schema.sql | DEPRECATED FOR MARKET MOOD ANALYSIS (2026-04-21): This table is no longer used by vw_finserv_mood_analysis (decoupled via migration 20260422_005). BankNifty and NIFTY50 mood views still JOIN this table but are scheduled for ffmc-only rewrites in the post-NIFTY Next 50 alignment sprint, after which this table will have no active consumers in the Market Mood Analysis pipeline. DO NOT DROP without a full cross-codebase consumer audit. Last known active JOIN: vw_banknifty_mood_analysis, vw_nifty50_mood_analysis (both stale — weights reflect last NSE semi-annual rebalancing, not current intraday prices). |
nse_indices | 20260708000000_baseline_prod_schema.sql | Stores fetched data for NSE market indices. |
nse_ipo_current_issue | 20260708000000_baseline_prod_schema.sql | — |
nse_market_status | 20260708000000_baseline_prod_schema.sql | — |
nse_midcap_select_contributors | 20260708000000_baseline_prod_schema.sql | — |
nse_midcap_select_historical_data_daily | 20260708000000_baseline_prod_schema.sql | — |
nse_most_active_equities_by_value | 20260708000000_baseline_prod_schema.sql | — |
nse_most_active_equities_by_volume | 20260708000000_baseline_prod_schema.sql | — |
nse_nifty_next50_contributors | 20260708000000_baseline_prod_schema.sql | — |
nse_nifty_next50_historical_data_daily | 20260708000000_baseline_prod_schema.sql | — |
nse_nifty50_contributors | 20260708000000_baseline_prod_schema.sql | — |
nse_nifty50_historical_data_daily | 20260708000000_baseline_prod_schema.sql | — |
nse_nifty500_contributors | 20260708000000_baseline_prod_schema.sql | Nifty 500 constituent data polled during market hours (~62.5K rows/day). 7-day retention (~437K rows at steady state). autovacuum_vacuum_scale_factor tuned to 2% (from default 20%) in migration 130 — same pattern as nse_all_stocks_traded (migration 129). Prevents stale planner statistics and dead-tuple bloat between daily cleanup batches. |
nse_nifty500_session_eod | 20260708000000_baseline_prod_schema.sql | Incremental staging table for vw_gap_down_stocks and vw_key_level_breachers. One row per (symbol, session_date) — the latest intraday snapshot from nse_nifty500_contributors for each trading day. Maintained by refresh_nifty500_session_eod() which upserts only today's data. Replaces the expensive full-table scans in the two gap/level MVs. |
nse_oi_spurts_data | 20260708000000_baseline_prod_schema.sql | — |
nse_volume_gainers | 20260708000000_baseline_prod_schema.sql | — |
option_chain_live | 20260708000000_baseline_prod_schema.sql | Live option chain data for all indices, all strikes and expiries. Polled during market hours. autovacuum_vacuum_scale_factor tuned to 2% in migration 129 to prevent bloat accumulation between 5-day retention cleanup batches. |
page_guides | 20260708000000_baseline_prod_schema.sql | — |
plan_quality_scores | 20260708000000_baseline_prod_schema.sql | Analytics projection of trade_planner_plans. Pre-computed by Postgres trigger (trig_compute_plan_quality_scores) on every INSERT/UPDATE to trade_planner_plans. Separates the analytics concern from the operational record — all dashboard queries hit this table only; the main plans table is never scanned for aggregate reporting. Consumer: get_planning_intelligence RPC (Phase 7). Not directly exposed to client code. |
post_bookmarks | 20260708000000_baseline_prod_schema.sql | — |
post_likes | 20260708000000_baseline_prod_schema.sql | — |
post_poll_votes | 20260708000000_baseline_prod_schema.sql | — |
profiles | 20260708000000_baseline_prod_schema.sql | Stores user profile information, including custom fields and admin status. |
psychology_builder_archetype_insights | 20260708000000_baseline_prod_schema.sql | Stores generated behavioral insights for a user's current profile. Rows are inserted by psychology-builder-compute-behavioral-signals EF and dismissed via the psychology-builder-save-archetype EF (operation: dismiss_insight). A user sees at most 5 active (non-dismissed) insights at any time. Read via get_psychology_builder_archetype_profile() RPC (returned as a nested array). |
psychology_builder_archetype_profiles | 20260708000000_baseline_prod_schema.sql | One row per user. Stores the current multi-dimensional psychological profile derived from quiz responses (55% weight) and inferred behavioral signals from platform usage patterns (45% weight). Written exclusively by the psychology-builder-save-archetype Edge Function using service role. Never written to directly from client code. Read via get_psychology_builder_archetype_profile() SECURITY DEFINER RPC only. |
psychology_builder_archetype_snapshots | 20260708000000_baseline_prod_schema.sql | Append-only history table. One row per profile version per user. Inserted by psychology-builder-save-archetype EF on each quiz completion or recalibration. Never updated after insert. Powers the Profile Evolution Timeline chart in the UI. Read via get_psychology_builder_archetype_snapshots() SECURITY DEFINER RPC. |
psychology_builder_bias_acknowledgments | 20260708000000_baseline_prod_schema.sql | Persistent bias acknowledgment records. Replaces the localStorage pb_biases_ack pattern. One row per (user_id, bias_id) pair. Writes via psychology-builder-save-archetype EF (acknowledge_bias operation). Reads via get_psychology_builder_bias_acknowledgments RPC. |
psychology_builder_modules | 20260708000000_baseline_prod_schema.sql | — |
psychology_builder_trading_rules | 20260708000000_baseline_prod_schema.sql | — |
psychology_builder_user_progress | 20260708000000_baseline_prod_schema.sql | — |
quick_access_controls | 20260708000000_baseline_prod_schema.sql | — |
reserved_slugs | 20260708000000_baseline_prod_schema.sql | Authoritative reserved-slug policy table. ~736 seed entries across 9 categories. Add new rows via INSERT ... ON CONFLICT DO NOTHING for brand protection, legal holds, or new app routes. Never remove entries — set is_active = FALSE instead to preserve audit trail. This table is the SINGLE SOURCE OF TRUTH for slug reservations. No client-side constants or hardcoded lists should duplicate this data. |
risk_radar_profiles | 20260708000000_baseline_prod_schema.sql | User-defined risk parameters like max drawdown and risk per trade. |
sector_constituents | 20260708000000_baseline_prod_schema.sql | Stores the constituent stocks for each sectoral index. |
session_review_entries | 20260708000000_baseline_prod_schema.sql | Post-period reflection and decision-quality entries for all trader and investor archetypes. One record per user per calendar date. Supports intraday, swing, positional, investor, and periodic review sessions via the session_type column. Writes handled by the session-review-save Edge Function. Phase 7 adds per-trade attribution via session_review_trade_annotations. |
trade_journal_broker_accounts | 20260708000000_baseline_prod_schema.sql | Details of user-linked broker accounts, including capital and name. |
trade_journal_brokers | 20260708000000_baseline_prod_schema.sql | Lists brokers linked by users to their accounts. |
trade_journal_entries | 20260708000000_baseline_prod_schema.sql | Immutable trade journal — one row per executed trade. Migration 076 converted five real-typed financial columns (entry_price, exit_price, pnl, stop_loss_price, target_price) from real (single-precision float, ~7 significant digits) to numeric(12,2) to eliminate rounding errors in P&L, R-multiple computation, and downstream Phase 7 behavioral analytics. risk_rupees and reward_rupees were already numeric. All monetary values are in INR; precision is paisa (₹0.01). Immutability: no DELETE RLS policy (migration 074); EF rejects past-date UPDATE with 403. |
trade_plan_lifecycle_log | 20260708000000_baseline_prod_schema.sql | System-generated audit trail of all lifecycle state transitions for trade_planner_plans rows. Written exclusively by SECURITY DEFINER Edge Functions — no client or trigger writes. No UPDATE or DELETE permitted. |
trade_plan_pending_uploads | 20260708000000_baseline_prod_schema.sql | Tracks R2 chart-image uploads initiated (presigned URL issued) but not yet confirmed (plan not saved). Written by trade-planner-media-upload EF. Deleted by trade-planner-save EF (on confirm) and by trade-planner-media-cleanup cron EF (on 24h stale timeout). YouTube attachments are stored directly in trade_planner_plans.media_attachments — no row here. |
trade_plan_share_recipients | 20260708000000_baseline_prod_schema.sql | Explicit access list for private shares. Each row grants a specific registered user read access to the associated trade_plan_shares record. Created by trade-planner-share EF alongside notification inserts. |
trade_plan_shares | 20260708000000_baseline_prod_schema.sql | Trade plan share records. Each row represents a sharing link created by a plan owner. Tokens are 128-bit random hex strings. Mutations go through the trade-planner-share EF which enforces ownership, scope, and access-control invariants. Revocation sets is_active = false (rows are never deleted). |
trade_plan_updates | 20260708000000_baseline_prod_schema.sql | Append-only execution log for trade_planner_plans. Each row records one trader-authored update during the lifetime of a plan. No UPDATE or DELETE is permitted at any level. |
trade_planner_executions | 20260708000000_baseline_prod_schema.sql | Join table linking each trade_journal_entries row to its parent trade_planner_plans row. Auto-populated by the sync_plan_execution_fn trigger (migration 075) on trade INSERT. uq_tpe_trade_id enforces one execution row per trade — one trade belongs to at most one plan. execution_quality and r_multiple are user-annotated later via the trade-planner-save EF; they are never written by the trigger. Phase 7: execution_quality feeds behavioral analytics. |
trade_planner_plans | 20260708000000_baseline_prod_schema.sql | User trade plans (daily/weekly/monthly). Records are permanent — immutability enforced by RLS (no DELETE policy) and trade-planner-save EF (rejects updates to past plans with 403). is_active column removed in migration 077. Schema v2 (077): market_bias, min_reward_risk, idx_tpp_user_date. RPC layer (082): get_trade_plans(), get_plan_execution_stats(). Schema v3 (085): 13 execution intelligence columns. Platform identity (102): is_hub_published (EF-only), media_attachments. |
user_feedback | 20260708000000_baseline_prod_schema.sql | — |
user_follows | 20260708000000_baseline_prod_schema.sql | — |
user_screener_favorites | 20260708000000_baseline_prod_schema.sql | — |
user_screeners | 20260708000000_baseline_prod_schema.sql | — |
Generated file
Do not edit by hand. Regenerate with npm run docs:gen after adding migrations.