Database Schema Design for Recurring Subscription Systems
Designs an optimized database schema for a subscription business with SaaS metrics.
Create a complete relational model for a recurring subscription system, including tables, indexes, constraints, and ready-to-use queries for calculating MRR, churn, and LTV.
At a glance
Access
Free prompt
Open to copy without upgrading.
Prompt objective
Create a complete relational model for a recurring subscription system, including tables, indexes, constraints, and ready-to-use queries for calculating MRR, churn, and LTV.
Real use case
EdTech startup CursaJá is migrating from a legacy spreadsheet system to PostgreSQL and needs a data model that supports 20,000 subscribers, multiple plans, trials, upgrades, downgrades, and automatic SaaS metric calculations.
Customize these fields first
Replace the placeholders with your own context before you run the prompt. That usually improves the first output more than adding more instructions later.
Prompt
Design the complete database schema for the subscription system of [COMPANY NAME], a [BUSINESS TYPE] with [NUMBER] subscribers and [NUMBER] plans.\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Part 1 — Schema (DDL):**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nCreate tables with data types, PKs, FKs, constraints, and indexes:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n1. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`plans\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` — id, name, monthly_price, annual_price, features (JSONB), is_active\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n2. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`subscriptions\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` — id, customer_id, plan_id, status (trial/active/paused/cancelled/expired), start_date, end_date, billing_cycle (monthly/annual), current_price\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n3. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`payments\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` — id, subscription_id, amount, due_date, payment_date, status, gateway_id, payment_method\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n4. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`plan_changes\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` — id, subscription_id, previous_plan_id, new_plan_id, change_type (upgrade/downgrade), date, reason\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n5. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`cancellations\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` — id, subscription_id, reason_category, reason_text, date, offered_discount, accepted_retention\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n6. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`customers\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` — registration data with segmentation\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Part 2 — Strategic Indexes:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Indexes for the most common queries (list active, expired, by plan)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Partial index for active subscriptions\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Composite index for reports by period + status\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Justification for each index\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Part 3 — SaaS Metric Queries:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n1. **MRR (Monthly Recurring Revenue):**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - Total MRR, New MRR, Expansion MRR, Contraction MRR, Churned MRR\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n2. **Churn Rate:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - Customer churn and revenue churn (separate)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - Net Revenue Retention (NRR)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n3. **LTV:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - LTV = ARPU / Churn Rate\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - LTV by plan and by acquisition cohort\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n4. **Retention Cohort:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n - Retention matrix by acquisition month\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Part 4 — Procedures/Views:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Materialized view \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`monthly_metrics\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\` with all KPIs\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Procedure for monthly metrics snapshot\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Trigger to log status changes\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nDialect: [PostgreSQL/MySQL]. Include explanatory comments in each CREATE TABLE.
Open directly in an AI — the text is pre-filled:
How to use this prompt
- 1Replace the key placeholders first: COMPANY NAME, BUSINESS TYPE, NUMBER, PostgreSQL/MySQL.
- 2Replace any bracketed placeholders like [this] with your own context.
- 3Add extra background information when you want more tailored results.
- 4Combine multiple prompts in one conversation when you need a richer output.
- 5Save your best-performing prompts so they are easy to reuse later.
Next best step
Open the guide first, then branch only if you still need more.
A guide for technical builders choosing between prompts, coding workflows, and agent-based implementation.
If this prompt is close but not quite right, generate variants next. If the job is recurring, move into the course library after the guide.
Related prompts
View allSales analysis queries with CTEs and window functions
Writes advanced SQL queries using CTEs and window functions for sales performance analysis.
Best for
Create a set of reusable SQL queries that analyze sales by period, product, seller, and region using CTEs (Common Table Expressions) and window functions for rankings and comparisons.
Slow Query Optimization with EXPLAIN ANALYZE and Indexes
Diagnoses and optimizes slow SQL queries using execution plans, strategic indexes, and query rewriting.
Best for
Learn to identify SQL query performance bottlenecks using EXPLAIN ANALYZE, create efficient indexes, and rewrite queries to reduce response time.
Stored Procedures for Automatic Sales Commission Calculation
Creates stored procedures that automate commission calculations with complex tiered rules and bonuses.
Best for
Develop SQL stored procedures that calculate sales commissions considering tiered quotas, product mix bonuses, cancellation clawbacks, and team splits.
BigQuery Queries for E-commerce Conversion Funnel and Attribution Analysis
BigQuery queries to analyze conversion funnels, channel attribution, and browsing behavior in e-commerce.
Best for
Build conversion funnel analysis queries in BigQuery (Google Analytics 4 export) to identify drop-off points, optimize conversion rates, and attribute revenue to acquisition channels.
Explore other prompt categories
Move sideways into adjacent libraries when the current category is not the full answer.
Free browsing stays open. Premium prompts unlock the reusable workflow layer.
Use the guides and role paths to validate the job first. Upgrade when you want the full prompt text, editable premium prompts, and the surrounding course paths in one place.
Free access
- Browse guides, role paths, and category pages.
- Preview prompts before you decide to upgrade.
- Find the right starting point without friction.
Membership access
- Unlock premium prompts and the full copy text.
- See more workflow paths and course connections.
- Keep the reusable templates in one place.
