MODULE 13 / 18 · PART 4 · SHIP & SUSTAIN
Data & Databases
Design the schema around access patterns, then prove it with the query plan.
THE SHORT ANSWER
What this module changes.
Schemas outlive applications. Ask what queries the design makes fast, which ones it makes slow, and what migration it will make painful.
Data prompts should always ask about the query plan and the migration path. Schemas outlive applications, so the cost of getting them wrong is measured in years.
Money, phone numbers, identity and audit trails are the schema decisions that hurt later. Store money as integer minor units plus currency, keep transaction references unique, and design retention before the table grows.
THE MODULE PROMPT LIBRARY
20 prompts for data & databases.
Copy any prompt, replace the [bracketed] slots with your own project context, and attach the files the agent needs. Use “Copy all prompts” when you want the full module in one place.
20 copy-ready prompts. Bracket text is a slot for your own context — replace it before you send the prompt.
Design this schema. Then tell me what queries it makes fast and which ones it makes slow.
Why it worksA schema is an access-pattern bet; name the losers up front.
Write the indexes for these queries: [queries]. Justify each index by the query it serves and the write cost it adds.
Why it worksIndexes are not free; every one taxes writes.
Explain this query plan: [plan]. Where is the full scan and why did the planner choose it?
Why it worksThe planner's choice is the ground truth of query performance.
Design the migration for this schema change with zero downtime and backward compatibility.
Why it worksZero-downtime migrations require a specific sequence, not just care.
What is the normalization trade-off here? Show me the normalized design and the denormalized one, with the read/write costs.
Why it worksNormalization is a trade-off, not a rule.
How do we handle concurrent updates to this record? Compare optimistic and pessimistic locking for this case.
Why it worksConcurrency control is a decision with real user-visible consequences.
Design the partitioning strategy for [table] and tell me what queries it breaks.
Why it worksPartitioning helps some queries and hurts others; know which.
What is the retention and archival policy for this data, and how do we implement it without locking the table?
Why it worksUnbounded tables are a slow-motion outage.
Write the data validation rules for this schema and tell me which ones the database can enforce versus the application.
Why it worksPush what you can into the database; it is the last line of defense.
How do we backfill this new column for existing rows without a long lock or a huge transaction?
Why it worksBackfills are where migrations cause production incidents.
Design the audit trail for [entity]: what changes, who made them, and how we query it.
Why it worksAudit requirements arrive late and are expensive to retrofit.
What is the consistency requirement for this read path, and can we serve it from a replica?
Why it worksReplica reads are cheap but stale; state the tolerance explicitly.
Review this schema for future flexibility: what change would require a painful migration?
Why it worksIdentifies the schema decisions that will hurt later.
Design the soft delete strategy for [entity] and tell me how it interacts with unique constraints.
Why it worksSoft deletes break unique indexes in ways that surprise people.
What is the transaction boundary for this operation, and what happens if it fails halfway?
Why it worksTransaction boundaries are correctness decisions.
Propose the read model for [query] and how it stays in sync with the write model.
Why it worksCQRS is often the right answer; the sync mechanism is the hard part.
How do we test this migration against a production-sized dataset before running it for real?
Why it worksMigrations that work on 1000 rows fail on 100 million.
Design the data lineage and provenance tracking for [pipeline].
Why it worksWhen numbers look wrong, lineage is how you find out why.
What is the cost of this query at scale, and how does it grow with data volume?
Why it worksQuery cost growth is what turns a working system into a slow one.
Review this data model for the questions it cannot answer, and propose the minimal change to fix that.
Why it worksData models are judged by the questions they support.
PRACTICE · 20–30 MINUTES
Try it on a project you actually own.
Take one table. Run prompts 1 and 13. Add the missing index or constraint that protects the invariant, and write the query plan before and after.
- Pick one prompt from this module that matches a real task.
- Replace every bracketed slot with context from the feature you are working on now.
- Attach only the files the agent needs. Keep secrets, credentials and personal data out of the chat.
- Ask for the artifact — a spec, diff, test, runbook or decision note — and the evidence that it works.
- Record what changed and what remains unverified in the repository.
Want the same prompts across every module? Search the full prompt library or return to the course overview.