Problem Statement
The workflow helps teams design and review a scalable Postgres schema for relationship and interaction data such as comments, reactions, and mentions. It transforms inputs like entity types, expected queries, scale, retention, and privacy constraints into a complete technical deliverable. The goal is to provide a normalized, efficient, and privacy‑aware database design with indexes, rationale, query examples, and a migration strategy.
Steps
Ingest Project Inputs
INGESTCollect entity types, key queries, expected scale, retention windows, and privacy constraints. Parse and normalize the input into structured fields used throughout the workflow.
Analyze Entities and Relationships
EXECUTEInterpret all provided entities such as comments, reactions, mentions, and map their relationships. Identify required metadata fields, foreign keys, and optional denormalized fields.
Draft Tables and Index Strategy
EXECUTEGenerate proposed table definitions, indexes, and normalization approach. Include guidance on BLOB vs indexed fields and highlight any denormalization tradeoffs.
Validate Against Key Queries
EXECUTEEvaluate the proposed schema by running the key read/write patterns conceptually and ensuring indexes and joins satisfy latency requirements. Flag any performance or privacy issues.
Generate Partitioning and Migration Plan
EXECUTEPropose a partitioning, retention, or archival approach if needed. Provide a migration plan for evolving from an existing schema to the new design.
Prompt Configurations
Postgres Schema Drafting Prompt
You are a senior database architect. Produce a technical, decisive, scalable Postgres schema design using the inputs below. Include: (1) proposed tables with fields, types, and constraints; (2) indexes and rationale; (3) normalization vs denormalization justification; (4) partitioning/archival strategy if warranted; (5) query examples for key access patterns; (6) a migration plan from the current schema. Follow best practices, avoid inventing data fields not present in the input, and base the design only on the entities and constraints provided. Tone should be technical and authoritative.
Example Inputs/Outputs
Entities: comments, reactions, mentions. Key queries: fetch comments for a post sorted by time; count reactions per comment; list mentions for a user. Scale: 50M comments/day. Retention: reactions retain 2 years, comments retain 5 years. Privacy: user deletion requires anonymizing comment author. Current schema: single comments table with no reaction table.
Tables: comments, reactions, mentions, plus comment_reaction_counts (optional denormalized). Indexes: btree on (post_id, created_at), (comment_id), (user_id). Partitioning: time-based monthly partitions for comments and reactions. Rationale: high‑volume write patterns demand partitioning for retention-based pruning. Query examples: SELECT * FROM comments WHERE post_id=? ORDER BY created_at DESC LIMIT 50; SELECT COUNT(*) FROM reactions WHERE comment_id=?; SELECT * FROM mentions WHERE mentioned_user_id=? LIMIT 100. Migration plan: backfill reactions from event logs, split comments table into partitions, replay updates using dual‑write period.