Changeset 9577c79 for docs/P2-RelationalDesign/RelationalDesignAIUsage.md

Timestamp:

09/16/26 23:37:15 (13 days ago)

Author:

Stefan <trsunovstefan@…>

Branches:

main

Children:

8b447ef

Parents:

df05838

Message:

add reserved_quantity and modify the phases, add v_03.png and v_03.xml for P1

File:

• docs/P2-RelationalDesign/RelationalDesignAIUsage.md (modified) (1 diff)

docs/P2-RelationalDesign/RelationalDesignAIUsage.md

@@ -69 +69 @@
 > against the faculty database. No AI involvement is possible there — it needs a
 > live connection to your assigned database.
+
+### Session 3 — 2026-09-16
+
+Driven by the same design review logged in full in
+[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md#session-3--2026-09-16):
+a sell order had nothing to check `holdings.quantity` against except itself,
+so nothing stopped two sell orders from being granted the same units.
+
+Changes to the P2 artefacts:
+
+- `holdings` gained `reserved_quantity numeric(20,4) NOT NULL DEFAULT 0
+  CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` in
+  `schema_creation.sql`.
+- `v_portfolio` gained `reserved_quantity` and the derived
+  `available_quantity = quantity - reserved_quantity`.
+- [RelationalDesign](RelationalDesign.md) gained a "Reservation and the order
+  lifecycle" section explaining why the check is enforced at the database
+  level rather than trusted to application code, and why it does not conflict
+  with the existing `SELECT … FOR UPDATE` locking on the sell path.
+- `data_load.sql` needed no change — `reserved_quantity` defaults to 0, which
+  is correct for every seeded holding.
+
+Re-run end to end against the live database on `localhost:5433`
+(`-init` then `-load-data`), and against a manually seeded 2 BTC holding to
+reproduce the exact scenario that motivated the change — see
+[UseCase0005Implementation](../P4-Prototype/UseCase0005Implementation.md) for
+the transcript.
+
+**What I decided:** to add the `CHECK` constraint rather than rely on
+`trade.go` alone to keep the reservation consistent — the same reasoning
+already applied to `avg_price NOT NULL` in session 2.