Mission 2 Exception Scenario Discovery Lab Guide
📥 Lab input
- •
database-schema.md(DB physical schema doc)
💾 Lab output
- •
edge-case-analysis.md(exception scenario report)
1. Story and lab background
On the first night after a startup successfully launches its MVP, an unexpected server alarm goes off. Two users pressed the pay button at the same time for the same booking slot; both payments succeeded, but the database was overwritten by the last payer, and the first payer's booking data was lost. (A concurrency control exception)
Also, when the DB record of a user who deleted their account was physically removed (Hard Delete), the user_id foreign key integrity on that user's past sales data broke, causing a terrible chain failure that stopped the daily sales settlement queries.
If you don't carefully predict abnormal flows and exceptions (Edge Cases) like these at the design stage and set data integrity rules, you'll suffer serious business losses after release. In this mission, you'll learn to use Codex to find all possible data distortions and potential exception cases based on the data model ahead of time, and design responses in advance.
2. Learning goals
- Detect logical design flaws in the defined table schema and foreign key structure.
- Derive Edge Case scenarios like concurrent writes, broken referential integrity and failed transaction rollbacks.
- Prepare responses such as Soft Delete (logical delete) to guard against Hard Delete on account deletion.
🛠️ Follow-along lab guide
- Create the lab file: press the download button below and save
database-schema.mdin theautomation/folder. - Hand the check to Codex: send the **Codex request prompt** below in the Codex Client prompt box and build.
- Check the response design: review whether the report sets specific DB improvements like double-booking prevention (distributed lock/Unique constraint) and Soft Delete flag design.
Download lab materials
Codex request prompt
Mission 02 Prompt
Derive Edge Case exception scenarios that break data consistency in the currently defined User, Expert, Reservation and Payment table structure, and suggest improvements.
4. Example output
Codex dissects the schema logic as a white box and derives serious exception scenarios like these.
| Exception type | Possible scenario (Edge Case) | System impact | Recommended improvement |
|---|---|---|---|
| Concurrency error | Two users pay for the same session slot at the same time, both are judged successful, and they're mapped twice | Critical | Create a composite UNIQUE constraint on the DB booking table or use a Redis distributed lock |
| Referential integrity | Hard Deleting a User record with payment history breaks the foreign key and breaks daily sales settlement queries | Critical | Change the table design to Soft Delete (logical delete) with an is_deleted column and deletion time |
| Data mismatch | External PG payment approval succeeds, but our DB insert rolls back because of a network failure and the payment record vanishes | Normal | Wrap the payment in a distributed transaction (Saga Pattern) and run a batch check script for unconfirmed payments |