Database Tables

132 tables discovered across supabase/migrations/ (210 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	20260406092809_remote_schema.sql	Defines available ad slots across the application.
ad_rules	20260406092809_remote_schema.sql	Defines where, when, and how an ad is displayed.
ad_special_users	20260406092809_remote_schema.sql	Whitelist of users for special ad viewing modes (Advertiser, Reviewer).
admin_task_reminders	20260406092809_remote_schema.sql	—
ads	20260406092809_remote_schema.sql	Stores individual ad creatives and their metadata.
broker_accounts	20260406092809_remote_schema.sql	Details of user-linked broker accounts, including capital and name.
community_posts	20260406092809_remote_schema.sql	Contains user-generated posts for the community feed, including text, tags, and engagement counts.
daily_psychology_checkins	20260503_051_psychology_checkins_table.sql	—
daily_scheduled_clean_up_tables_list	20260406092809_remote_schema.sql	Configuration table for daily automated database cleanup via Edge Function. Admins manage table cleanup settings here.
direct_messages	20260406092809_remote_schema.sql	—
edge_function_configs	20260406092809_remote_schema.sql	Stores configurations for Supabase Edge Functions, like API keys or parameters.
edge_function_runs	20260511_085_edge_function_monitoring.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	20260406092809_remote_schema.sql	—
finfluencify_announcement_recipients	20260406092809_remote_schema.sql	Tracks read status per student per announcement. Enables read rate calculation, unread tracking, and engagement metrics.
finfluencify_announcements	20260406092809_remote_schema.sql	Main announcements table for broadcasting messages to students. Tracks read status, priority, and notification delivery methods.
finfluencify_certificate_templates	20260408_finfluencify_certificate_templates.sql	Platform-managed certificate HTML templates. Trainers pick one per course.
finfluencify_commission_brackets	20260409_004_finfluencify_commission_brackets.sql	Price-range commission rules. Level 2 in the 4-tier commission resolution hierarchy —
finfluencify_course_analytics	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_schema.sql	Payment verification table. Tracks student payment submissions and trainer verification. Core to Payment ID workflow: (1) Student pays via trainer link, (2) Student submits payment ID to platform, (3) Trainer verifies in gateway, (4) Trainer grants access. Decouples payment processing from platform.
finfluencify_course_promotions	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260409_002_finfluencify_discount_codes.sql	Trainer-managed discount/coupon codes. Validated server-side by the payment initiation function. Students have no direct access to this table.
finfluencify_email_outbox	20260703_165_finfluencify_email_outbox.sql	Durable retryable outbox for live-class transactional email (+ .ics). Lifecycle EFs enqueue; finfluencify-email-outbox-sweep drains to ZeptoMail with bounded backoff and a dead-letter terminal state. Service-role only (RLS: no policies).
finfluencify_engagement_metrics	20260406092809_remote_schema.sql	Daily engagement aggregation per course. Used for dashboard reporting and trend analysis over time.
finfluencify_enquiry_submissions	20260406092809_remote_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	20260409_001_finfluencify_lesson_qa.sql	Single-level threaded Q&A for course lessons. parent_id = NULL for top-level questions, non-NULL for replies.
finfluencify_link_in_bio	20260406092809_remote_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	20260630_151_finfluencify_live_session_invitees.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	20260701_157_finfluencify_live_session_participants.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	20260702_159_finfluencify_live_session_recordings.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	20260701_158_finfluencify_live_session_reminders.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	20260630_150_finfluencify_live_sessions.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	20260409_005_finfluencify_payment_orders.sql	Single source of truth for every Razorpay checkout session on the platform.
finfluencify_plan_audit_log	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_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	20260406092809_remote_schema.sql	Answer choices for polls. Position determines display order. response_count is auto-updated via trigger.
finfluencify_poll_responses	20260406092809_remote_schema.sql	Individual student responses to polls. One row per response, updated via trigger for multiple-choice. Allows anonymous/non-anonymous tracking.
finfluencify_polls	20260406092809_remote_schema.sql	Main poll table for quick engagement feedback from students. Polls are time-bound and track response rates.
finfluencify_special_offers	20260406092809_remote_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	20260406100000_finfluencify_stream_webhook_events.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	20260408_finfluencify_certificate_templates.sql	One row per issued certificate. Created by finfluencify-generate-certificate Edge Function.
finfluencify_students	20260406092809_remote_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	20260406092809_remote_schema.sql	Individual student answers to survey questions. Uses flexible JSONB format to support different question types without multiple columns.
finfluencify_survey_questions	20260406092809_remote_schema.sql	Individual questions within surveys. Supports multiple question types with flexible options, validation rules, and conditional logic.
finfluencify_survey_responses	20260406092809_remote_schema.sql	Survey response sessions (one per student per survey). Tracks completion status, time spent, and progress percentage.
finfluencify_surveys	20260406092809_remote_schema.sql	Main survey table for collecting detailed feedback with multiple questions of various types (single-choice, text, rating, etc.)
finfluencify_trainer_active_plans	20260406092809_remote_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	20260629_149_finfluencify_trainer_zoom_accounts.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	20260701_156_finfluencify_zoom_webhook_events.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	20260406092809_remote_schema.sql	Stores Futures and Options (F&O) segment market holidays fetched from NSE.
giftnifty_status	20260406092809_remote_schema.sql	—
goals	20260406092809_remote_schema.sql	Stores user-defined financial or personal goals with tracking metrics.
habit_entries	20260406092809_remote_schema.sql	Tracks completion status of habits for each user on specific dates.
habits	20260406092809_remote_schema.sql	Defines user-created habits for tracking, including frequency, goals, and type.
IF	20260630_152_finfluencify_live_sessions_rpcs.sql	—
index_expiry_map	20260406092809_remote_schema.sql	Stores index names and their expiry dates for Option Chain data fetching.
index_master	20260406092809_remote_schema.sql	Master table for storing detailed data of various NSE indices.
maintenance_window_configurations	20260406092809_remote_schema.sql	Maintenance window configurations with persistence, audit trail, and soft-delete support. Supports indefinite and time-bounded maintenance notifications.
message_threads	20260406092809_remote_schema.sql	—
mv_refresh_registry	20260423_009_create_mv_refresh_registry.sql	Control table for the refresh-materialized-views Edge Function. Each row represents one materialized view to refresh. The EF queries: SELECT * FROM mv_refresh_registry WHERE enabled ORDER BY refresh_order. Refresh method CONCURRENT requires a UNIQUE index on the MV. F3 fix: EF no longer auto-discovers MVs from pg_matviews.
notification_events	20260513_088_notification_fan_out_architecture.sql	Fan-out broadcast log. Each row represents a single notification event (e.g., a guide
notification_preferences	20260513_088_notification_fan_out_architecture.sql	Per-user, per-category notification delivery preferences.
notifications	20260406092809_remote_schema.sql	—
nse_all_indices	20260406092809_remote_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	20260406092809_remote_schema.sql	Stores time-series data for all stocks traded on NSE, fetched from the live-analysis-stocksTraded API endpoint.
nse_banknifty_contributors	20260406092809_remote_schema.sql	—
nse_banknifty_historical_data_daily	20260406092809_remote_schema.sql	—
nse_equity_daily_snapshot	20260527_119_nse_equity_daily_snapshot.sql	Incremental staging table for mv_equity_daily_eod. One row per (symbol, trade_date).
nse_equity_session_stats	20260527_121_nse_equity_session_stats.sql	Incremental staging table for mv_equity_volume_surges. One row per (symbol, session_date).
nse_finserv_contributors	20260406092809_remote_schema.sql	—
nse_finserv_historical_data_daily	20260406092809_remote_schema.sql	—
nse_index_weightages	20260406092809_remote_schema.sql	DEPRECATED FOR MARKET MOOD ANALYSIS (2026-04-21):
nse_indices	20260406092809_remote_schema.sql	Stores fetched data for NSE market indices.
nse_ipo_current_issue	20260406092809_remote_schema.sql	—
nse_market_status	20260406092809_remote_schema.sql	—
nse_midcap_select_contributors	20260406092809_remote_schema.sql	—
nse_midcap_select_historical_data_daily	20260406092809_remote_schema.sql	—
nse_most_active_equities_by_value	20260406092809_remote_schema.sql	—
nse_most_active_equities_by_volume	20260406092809_remote_schema.sql	—
nse_nifty_next50_contributors	20260406092809_remote_schema.sql	—
nse_nifty_next50_historical_data_daily	20260406092809_remote_schema.sql	—
nse_nifty50_contributors	20260406092809_remote_schema.sql	—
nse_nifty50_historical_data_daily	20260406092809_remote_schema.sql	—
nse_nifty500_contributors	20260406092809_remote_schema.sql	Nifty 500 constituent data polled during market hours (~62.5K rows/day).
nse_nifty500_session_eod	20260527_122_nse_nifty500_session_eod.sql	Incremental staging table for vw_gap_down_stocks and vw_key_level_breachers.
nse_oi_spurts_data	20260406092809_remote_schema.sql	—
nse_volume_gainers	20260406092809_remote_schema.sql	—
option_chain_live	20260406092809_remote_schema.sql	Live option chain data for all indices, all strikes and expiries. Polled during
page_guides	20260406092809_remote_schema.sql	—
plan_quality_scores	20260512_086_plan_quality_scores.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	20260406092809_remote_schema.sql	—
post_likes	20260406092809_remote_schema.sql	—
post_poll_votes	20260406092809_remote_schema.sql	—
profiles	20260406092809_remote_schema.sql	Stores user profile information, including custom fields and admin status.
psychology_builder_archetype_insights	20260504_055_trader_archetype_insights.sql	Stores generated behavioral insights for a user's current profile.
psychology_builder_archetype_profiles	20260504_053_trader_archetype_profiles.sql	One row per user. Stores the current multi-dimensional psychological profile
psychology_builder_archetype_snapshots	20260504_054_trader_archetype_snapshots.sql	Append-only history table. One row per profile version per user.
psychology_builder_bias_acknowledgments	20260505_058_trader_bias_acknowledgments.sql	Persistent bias acknowledgment records.
psychology_journal_entries	20260406092809_remote_schema.sql	Entries for the psychology journal, tracking moods and reflections related to trading.
psychology_modules	20260406092809_remote_schema.sql	—
quick_access_controls	20260406092809_remote_schema.sql	—
reserved_slugs	20260406092809_remote_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_profiles	20260406092809_remote_schema.sql	User-defined risk parameters like max drawdown and risk per trade.
sector_constituents	20260406092809_remote_schema.sql	Stores the constituent stocks for each sectoral index.
session_review_entries	20260505_059_session_review_entries.sql	Post-period reflection and decision-quality entries for all trader and investor
trade_journal_entries	20260510_074_fix_trade_planner_schema.sql	Immutable trade journal — one row per executed trade. Records cannot be deleted. Only today's records may be updated (trade-journal-save EF rejects past-date UPDATE with 403). Linked to trade_planner_plans via trade_plan_id (nullable FK). Financial columns (entry_price, exit_price, pnl, capital_at_risk, brokerage) converted from real to numeric(12,2) in migration 076 to eliminate floating-point rounding errors. RPC access: get_plan_execution_stats() returns linked entries per plan (migration 082).
trade_plan_executions	20260406092809_remote_schema.sql	Linking table that connects executed trades to their planned setups, tracking execution quality and deviations.
trade_plan_lifecycle_log	20260513_090_trade_plan_updates_lifecycle_log.sql	System-generated audit trail of all lifecycle state transitions for
trade_plan_pending_uploads	20260528_115_media_attachments_infrastructure.sql	Tracks R2 chart-image uploads initiated (presigned URL issued) but not yet confirmed (plan not saved).
trade_plan_share_recipients	20260514_094_trade_plan_shares.sql	Explicit access list for private shares. Each row grants a specific registered user
trade_plan_shares	20260514_094_trade_plan_shares.sql	Trade plan share records. Each row represents a sharing link created by a plan owner.
trade_plan_updates	20260513_090_trade_plan_updates_lifecycle_log.sql	Append-only execution log for trade_planner_plans. Each row records one
trade_planner_executions	20260510_074_fix_trade_planner_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	20260510_074_fix_trade_planner_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 additions (migration 077): market_bias, min_reward_risk, idx_tpp_user_date. RPC access layer: get_trade_plans() and get_plan_execution_stats() (migration 082).
trade_plans	20260406092809_remote_schema.sql	Stores user-created trading plans, including market notes and watchlists.
trades	20260406092809_remote_schema.sql	Records individual trade details, including entry/exit prices, PNL, and strategy.
user_badges	20260406092809_remote_schema.sql	Stores badges earned by users for achieving habit milestones.
user_brokers	20260406092809_remote_schema.sql	Lists brokers linked by users to their accounts.
user_feedback	20260406092809_remote_schema.sql	—
user_follows	20260406092809_remote_schema.sql	—
user_psychology_progress	20260406092809_remote_schema.sql	—
user_screener_favorites	20260406092809_remote_schema.sql	—
user_screeners	20260406092809_remote_schema.sql	—
user_trading_rules	20260406092809_remote_schema.sql	—

Generated file

Do not edit by hand. Regenerate with npm run docs:gen after adding migrations.