ChatGPT Prompt for App Architecture
Design a complete database schema and data strategy for a newsletter platform serving the fintech industry.
More prompts for App Architecture.
Generate a comprehensive system design document covering architecture, data flow, scalability, and deployment strategy.
Design a complete serverless architecture on AWS Lambda, Vercel, or Cloudflare Workers with infrastructure-as-code.
Design a multi-layer caching architecture with cache invalidation, TTL policies, and consistency guarantees.
Design a complete database schema and data strategy for a analytics dashboard serving the parenting industry.
Design a RESTful or GraphQL API for a restaurant ordering system using SQL with best practices and documentation.
Design a complete system architecture for a healthcare patient portal built with Django + PostgreSQL for real-estate.
You are an expert database architect with deep expertise in data architecture. Design a production-grade database architecture for a newsletter platform in the fintech industry. **Application:** newsletter platform **Industry:** fintech **Database type:** MySQL (relational) **Expected data volume:** large (50-500GB) ## Requirements Analysis - Identify the core entities and their relationships - List the primary read patterns (what queries run most often) - List the primary write patterns (what data changes most often) - Define consistency requirements (strong vs. eventual for each entity) ## Schema Design Design the complete schema with: - All tables/collections with their fields, types, and constraints - Primary keys, foreign keys, and unique constraints - Indexes (primary, secondary, composite) with justification for each - Enum types or lookup tables for categorical data - Soft delete vs. hard delete strategy per entity ## Relationships and Data Integrity - One-to-one, one-to-many, and many-to-many relationships - Cascade rules for updates and deletes - Referential integrity enforcement approach - How to handle circular dependencies if any exist ## Query Optimization - Design the 10 most common queries and their execution plans - Index strategy to support these queries efficiently - Denormalization decisions (what to denormalize and why) - Materialized views or computed columns for complex aggregations ## Scaling Plan - Partitioning or sharding strategy for large (50-500GB) scale - Read replica configuration and routing logic - Connection pooling settings and recommendations - Archival strategy for old data ## Migration and Evolution - Initial migration script structure - Versioning strategy for schema changes - Zero-downtime migration approach for each type of change - Seed data approach for development and staging environments ## Backup and Recovery - Backup frequency and retention policy - Point-in-time recovery configuration - Cross-region replication for disaster recovery - Tested recovery procedure outline Present your output in a clear, organized structure with headers (##), subheaders (###), and bullet points. Use bold for key terms.