Database Schema

MySQL database design, tables, relationships, and data models for the Grade My Investments platform.

CORE ENTITY RELATIONSHIPS

rendering diagram…

flowchart TB
    ACC[(accounts)]:::data
    JOBS[(jobs)]:::data
    FILES[(files)]:::data
    FOLDERS[(folders)]:::data
    SL[(symbollists)]:::data
    INV[(invoices)]:::data
    CHG[(charges)]:::data
    CLAUD[(claudeanalysisjobs)]:::data
    SCHD[(reportgenerationschedules)]:::data
    NOTIF[(jobnotificationsettings)]:::data

    ACC -->|1-N| JOBS
    ACC -->|1-N| SL
    ACC -->|1-N| INV
    JOBS -->|1-N| FILES
    JOBS -->|1-N| NOTIF
    JOBS -->|via jobcharges| CHG
    JOBS -->|via jobsymbollists| SL
    FILES -->|stored in| FOLDERS
    INV -->|via invoicecharges| CHG
    ACC -->|1-N| CLAUD
    ACC -->|1-N| SCHD

    classDef app fill:#1f2630,stroke:#C0894E,color:#F3EFE8;
    classDef svc fill:#241c14,stroke:#E2B583,color:#F3EFE8;
    classDef data fill:#22201a,stroke:#C0894E,color:#EBD3AE;
    classDef ext fill:#1b1f27,stroke:#5A616B,color:#C4C0B8;
    classDef sec fill:#1d2733,stroke:#5cc8e0,color:#eafaff;

Key relationships across the 67-table MySQL schema.

Entity Relationship Diagram

Grade My Investments Database Entity Relationship Diagram (Crow's Foot Notation) — 67 tables

Complete Database Tables (67)

Account & User Management

Table Name	Purpose	Key Relationships
`accounts`	User account management with profile, contact info, and IsFreeAccount flag	One-to-many with Jobs, Files, SymbolLists, Payments
`accountemailaddresses`	Multiple email addresses per account	References Accounts
`accountemailvalidationtokens`	Email validation token management	References Accounts
`accountsmsnumbers`	SMS phone numbers for notifications	References Accounts
`accountfolders`	Links accounts to their folders	References Accounts and Folders
`adminusers`	Admin user management with Azure AD integration and role-based access	Standalone admin authentication
`loginhistories`	User login tracking with IP, location, and device info	References Accounts
`creditcards`	Tokenized credit card storage via Stripe	References Accounts

Report Generation & Jobs

Table Name	Purpose	Key Relationships
`jobs`	Report generation job tracking with status, priority, processing metadata, and FmpApiCallCount	References Accounts, JobStatusTypes
`jobstatustypes`	Job processing status definitions (Queued, Processing, Complete, etc.)	Referenced by Jobs
`jobsymbollists`	Links jobs to symbol lists for report generation	References Jobs and SymbolLists
`jobfolders`	Links jobs to output folders	References Jobs and Folders
`jobcharges`	Links jobs to their billing charges	References Jobs, Charges, Accounts
`jobnotificationsettings`	Job completion notification preferences	References Jobs
`jobnotificationsaccountemailaddresses`	Email addresses for job completion notifications	References JobNotificationSettings, AccountEmailAddresses
`jobnotificationsaccountsmsnumbers`	SMS numbers for job completion notifications	References JobNotificationSettings, AccountSmsNumbers
`reportgenerationschedules`	Scheduled/recurring report generation with cron-like configuration. Stores TimeZoneId (IANA) to convert user local time to UTC for accurate scheduling.	References Accounts and Jobs

Symbol Lists & Templates

Table Name	Purpose	Key Relationships
`symbollists`	Named collections of stock symbols for analysis	References Accounts
`symbollistitems`	Individual stock symbols within a list	References SymbolLists
`symbollisttemplates`	Pre-built symbol list templates (system-provided or customer-created)	References Accounts (creator), has IsSystemProvided flag
`symbollisttemplateitems`	Individual stock symbols within a template	References SymbolListTemplates

Files & Storage

Table Name	Purpose	Key Relationships
`files`	Generated report files with Azure Blob Storage references	References Jobs, Folders, Accounts, CouponCodes
`filedownloads`	File download tracking and analytics	References Files
`folders`	Hierarchical file organization with parent/sibling structure	Self-referencing parent/sibling structure
`downloads`	Bulk download packaging and delivery (ZIP bundles)	References Accounts and Files

Portfolio & Position Reports

Table Name	Purpose	Key Relationships
`positionreports`	Portfolio position tracking reports with name and description	References Accounts
`positionreportlistitems`	Individual stock positions (symbol, shares, cost basis) within a report	References PositionReports

AI Analysis

Table Name	Purpose	Key Relationships
`claudeanalysisjobs`	Claude AI analysis job tracking with status, model selection, and cost	References Accounts
`claudeanalysisrequests`	Individual file analysis requests within a Claude analysis job	References ClaudeAnalysisJobs, Files
`reportcomparisonjobs`	Report comparison job tracking with status and date range parameters	References Accounts
`reportcomparisonjobsymbollists`	Links report comparison jobs to symbol lists being compared	References ReportComparisonJobs and SymbolLists

Billing & Payments

Table Name	Purpose	Key Relationships
`charges`	Service usage charges with amount, description, billing period, InternalCost, and ChargeType	References Accounts, linked to JobCharges and InvoiceCharges
`invoices`	Monthly billing invoices with status and payment tracking	References Accounts
`invoicecharges`	Links itemized charges to invoices	References Invoices and Charges
`payments`	Payment transaction records processed via Stripe	References Accounts
`billingruns`	Monthly billing process execution tracking	Tracks billing cycle status and timing
`servicepricings`	Service pricing configuration, rate management, and CostPerUnit tracking	System-wide pricing rules
`couponcodes`	Promotional codes and free trial management	Referenced by CouponUsage and Files
`couponusage`	Tracks coupon code redemption per account	References Accounts, CouponCodes, Jobs
`failedpaymentreconciliations`	Tracks failed Stripe payments for manual reconciliation	References Accounts
`operationalexpenses`	Operational expense tracking with Category, Description, Amount, Frequency, ExpenseDate, ExpenseYear, ExpenseMonth for cost and margin analytics	Standalone expense management

Stripe Integration

Table Name	Purpose	Key Relationships
`stripewebhookevents`	Idempotent Stripe webhook event processing log	Standalone event tracking
`striperefunds`	Stripe refund transaction records	References Accounts
`stripedisputes`	Stripe payment dispute/chargeback tracking	References Accounts

Notifications & Messaging

Table Name	Purpose	Key Relationships
`emailstosend`	Email notification queue with retry support and SendGrid integration	Async email processing
`smsstosend`	SMS notification queue with retry support and Twilio integration	Async SMS processing
`adminnotificationemailaddresses`	Admin email addresses for system notification alerts	Standalone admin configuration

Platform & Content Management

Table Name	Purpose	Key Relationships
`supportrequests`	Customer support tickets with priority, status, and admin response tracking	References Accounts
`featurerequests`	User-submitted feature ideas with category, status workflow, and admin notes	References Accounts
`platformfeedback`	Smile/frown sentiment feedback from any page, supports public and authenticated users	Optionally references Accounts
`bugreports`	Structured bug reports with severity, steps to reproduce, and admin triage status	Optionally references Accounts
`knowledgebasearticles`	Self-service help center articles with categories, slugs, and publish/draft status	Standalone content management
`announcements`	Platform-wide announcements with start/end dates and severity levels	Standalone, polled by client apps
`changelogentries`	Version history and release notes with publish/draft workflow	Standalone content management
`maintenancewindows`	Scheduled maintenance periods with start/end times and user-facing descriptions	Standalone, polled by client apps
`emailpreferences`	Per-account email opt-in/opt-out preferences for different notification categories	References Accounts (unique constraint)
`blogposts`	Blog articles with rich content, categories, slugs, view counts, and publish/draft status	Standalone content management

SEO & Public Ticker Pages

Table	Purpose	Key Relationships
`publictickers`	Curated tickers published as free SEO grade pages (/grade/{TICKER}), with the cached generation snapshot: grade, grade date, headline stats JSON, Claude summary, and per-ticker generation status	Standalone; unique Ticker; written by SeoPageBuilder
`publictickergroups`	Named ticker groups/indexes (S&P 500, Nasdaq, Dow Jones from FMP constituents; Russell 2000 via IWM ETF holdings; manual lists), with source type and sync timestamps	Standalone; unique Slug
`publictickergroupmembers`	Membership of tickers in groups — drives the /grade/ landing page sections and related-ticker internal links	References PublicTickerGroups (cascade delete, unique group+ticker)
`publictickerrefreshjobs`	Queue for page-regeneration runs: admin "Refresh & Republish" button and the monthly auto-check enqueue; the SeoPageBuilder VM service polls, executes, and records success/failure counts	Standalone job queue

Logging & Monitoring

Table Name	Purpose	Key Relationships
`activitylogentries`	Tracks user activity events with type, name, detail, and app source	References Accounts
`auditlogs`	Tracks entity changes with old/new values for compliance	References Accounts
`criticalerrorlogs`	Logs critical errors from background processors with resolution tracking	Standalone, indexed by ServiceName and IsResolved
`systemhealthchecks`	System health monitoring records with disk, memory, CPU, database, API connectivity, and background service status metrics	Standalone, written by HealthMonitor background service

Entity Relationships

Core Relationships

Account → Jobs: One account can have multiple report generation jobs
Job → Files: Each job generates multiple files (Excel, JSON)
Job → JobSymbolLists → SymbolLists: Jobs are linked to symbol lists for report generation
Folder → Files: Files are organized in a hierarchical folder structure
Account → AccountFolders → Folders: Each account has its own folder structure
SymbolList → SymbolListItems: Stock symbols grouped in lists for analysis
SymbolListTemplate → SymbolListTemplateItems: Pre-built template symbol lists (system or customer)

AI & Comparison

Account → ClaudeAnalysisJobs → ClaudeAnalysisRequests: AI analysis jobs with individual file analysis requests
Account → ReportComparisonJobs → ReportComparisonJobSymbolLists: Report comparison jobs linked to symbol lists for period-over-period analysis

Portfolio

Account → PositionReports → PositionReportListItems: Portfolio position tracking with individual holdings

Billing & Payments

Account → Payments: Payment transactions processed via Stripe
Account → CreditCards: Tokenized payment methods on file
Invoice → InvoiceCharges → Charges: Monthly billing with itemized charges
Job → JobCharges → Charges: Per-job billing charge tracking
Account → CouponUsage → CouponCodes: Promotional code redemption tracking

Notifications

Job → JobNotificationSettings → Email/SMS Addresses: Per-job notification routing to email and SMS
Account → ReportGenerationSchedules: Scheduled recurring report generation

Platform & Content

Account → SupportRequests: Customer support tickets with admin response workflow
Account → FeatureRequests: User-submitted feature ideas with status tracking
Account → BugReports: Structured bug reports with severity and admin triage
Account → PlatformFeedback: Smile/frown sentiment feedback (also supports anonymous)
Account → EmailPreferences: Per-account notification opt-in/opt-out settings (unique constraint)
KnowledgeBaseArticles, Announcements, ChangelogEntries, MaintenanceWindows, BlogPosts: Standalone content management entities with publish/draft workflow
PublicTickers, PublicTickerGroups, PublicTickerGroupMembers, PublicTickerRefreshJobs: SEO ticker-page curation — groups sync membership from FMP index constituents (or IWM holdings for Russell 2000), tickers cache their generated grade snapshot, and the refresh-job queue coordinates the SeoPageBuilder VM service

Auditing & Activity

Account → ActivityLogEntries: User activity tracking across app sources
Account → AuditLogs: Entity change tracking with old/new values
Account → LoginHistories: Login activity tracking with geolocation

Key Fields & Constraints

Primary Keys

AccountID (accounts)
JobID (jobs)
FileID (files)
FolderID (folders)
SymbolListID (symbollists)
SymbolListTemplateID (symbollisttemplates)
PositionReportID (positionreports)
ClaudeAnalysisJobID (claudeanalysisjobs)
ReportComparisonJobID (reportcomparisonjobs)
InvoiceID (invoices)
ChargeID (charges)
ActivityLogEntryID (activitylogentries)
SystemHealthCheckID (systemhealthchecks)

Common Fields

CreatedDateTime (audit timestamp)
ModifiedDateTime (last update tracking)
IsDeleted (soft delete pattern)
IsActive (active/inactive status)
AccountID (multi-tenant foreign key)

Database Technology

Azure Database for MySQL Flexible Server
MySQL 8.0+
InnoDB Storage Engine
UTF8MB4 Character Set
ACID Transactions
EF Core with Pomelo MySQL Provider

Schema Statistics

67 tables
8 account/user tables
9 job/report tables
4 symbol list tables
4 file/storage tables
4 AI analysis tables
9 billing/payment tables
3 Stripe integration tables
3 notification tables
10 platform/content tables
4 logging/monitoring tables
4 SEO ticker-page tables
2 portfolio tables

Data Patterns

Soft Delete Pattern
Audit Trail (Created/Modified dates)
Hierarchical Data (Folders)
Multi-tenant (AccountID)
Audit Logging (AuditLogs)
Activity Logging (ActivityLogEntries)
Login History Tracking
Idempotent Event Processing (Stripe)
Async Queue Pattern (Email/SMS)
System Health Monitoring
Publish/Draft Content Workflow
Upsert (Email Preferences)
Slug-Based URL Routing

Billing & Payments

Stripe Integration
Webhook Event Tracking
Refund Management
Dispute Handling
Failed Payment Reconciliation
Coupon Code System
Monthly Invoice Generation

Performance Features

Indexed Foreign Keys
Composite Indexes
Query Optimization
Connection Pooling
AsNoTracking for Read Queries