Mission 2 Exception scenario discovery practice guide
📥 Practice Input
- •
database-schema.md(DB physical schema definition)
💾 Practice output (Output)
- •
edge-case-analysis.md(Exception scenario report)
1. Story and practice background
On the first night of a startup's successful launch of its MVP product, an unexpected server alarm sounds. Two users pressed the payment button at the same time during the same reservation time slot, and both payments were successful, but only the last payer was overwritten in the database, and the reservation data of the first payer was lost. (Concurrency control exception situation)
In addition, when the DB record of a user who withdrew membership was physically completely deleted (Hard Delete), the past sales data that the user had previously paid was lost. user_id There was also a catastrophic cascading failure where foreign key referential integrity was broken, stopping the execution of daily settlement sales statistics queries.
If you do not carefully predict these abnormal flows or edge cases at the design stage and establish data integrity rules, you will suffer fatal business losses after product deployment. In this mission, based on the constructed data model, we will operate Codex to identify possible data distortions and potential exception cases in advance, and learn techniques to preemptively design response plans.
2. Learning objectives
- Logical design flaws can be detected in defined table schemas and foreign key reference structures.
- Edge case scenarios such as concurrent writes, referential integrity collapse, and transaction rollback failure can be derived.
- You can prepare countermeasures such as soft delete (logical deletion) to defend against hard delete withdrawal.
🛠️Practical guide to follow along
- Create file for practice: Click the download button below
database-schema.mdfileautomation/Save it within the folder. - Codex Verification Delegation: Build by sending the **Codex request prompt** below to the Codex Client prompt input window.
- Confirmation of response design: In the derived report, we review whether specific DB improvement measures, such as duplicate reservation prevention measures (distributed lock/unique constraints) and soft delete logical identifier design, have been established.
Download practice materials
Codex request prompt
Mission 02 Prompt
Please derive edge case exception scenarios for data consistency violations that may occur in the currently defined User, Expert, Reservation, and Payment table structures and suggest improvement measures.
4. Example results
Codex white-boxes the schema logic and derives the following fatal exception scenarios:
| Exception classification | Possible Scenarios (Edge Case) | system impact | Recommended improvement measures |
|---|---|---|---|
| concurrency error | A phenomenon in which two users make a reservation and payment at the same time during the same consultation time and both are judged successful, resulting in duplicate mapping. | fatal | Create complex UNIQUE constraints on DB reserved tables or apply Redis distributed locks |
| referential integrity | A phenomenon in which daily sales settlement statistics queries are broken due to the foreign key relationship being broken due to hard deleting of user records with payment history. | fatal | Changed Soft Delete (logical delete) table design to record is_deleted column and deletion time |
| data inconsistency | External PG payment approval was successful, but it was rolled back due to a network failure at the DB insert stage and the payment record was disassembled. | Normal | Bundle payment transactions into distributed transactions (Saga Pattern) and run a batch verification script for unconfirmed payments |