Planning the Relational Extension

Step 1: Update the PRD

Open the PRD from the last project. It describes the inventory and order management system you already built. Now it needs to include Marco's two new requests: batch traceability and customer self-service order lookup.

Direct Claude to update the PRD with the new requirements. The traceability section should describe the trace chain: given a batch, find the source farm and all orders that used bars from that batch. Given a farm, find all batches. The customer portal section should describe what shops can see (their own orders, batch provenance) and what they cannot (other customers' orders, internal inventory).

Run a cross-model review. Direct a second AI to compare the updated PRD against Marco's email and your clarifying conversation. The reviewer should check: does every request from the email have a corresponding section? Does the PRD add anything Marco didn't ask for?

Step 2: Understand foreign keys

Open materials/migration-guide.md.

A foreign key connects one table to another. When the batches table has a farm_id column that references the farms table, that's a structural claim: every batch comes from a real, existing farm. The database enforces this -- if you try to insert a batch with a farm_id that doesn't exist in the farms table, the database refuses.

The migration guide covers what happens when you delete the referenced row. Three options, each with different consequences:

RESTRICT refuses the delete. You cannot remove Finca Rosario if any batch references it. The provenance data is protected.

CASCADE deletes everything connected. Remove Finca Rosario and all its batches disappear too -- along with all products from those batches and all orders for those products. Destructive.

SET NULL breaks the link but keeps the row. Remove Finca Rosario and the batches stay, but their farm_id becomes NULL. The batches exist but their origin is lost.

There is no neutral choice. Each preserves something and sacrifices something else. For Marco's system, provenance records are critical -- he traces quality issues back to the farm. Think about which behaviour protects that data.

Step 3: Design the migration

Plan the foreign keys you need to add. The existing tables (farms, batches, products, orders) need explicit connections:

• batches.farm_id references farms.id -- every batch comes from a farm
• products.batch_id references batches.id -- every product comes from a batch
• order_items.product_id references products.id -- every line item is a product
• order_items.order_id references orders.id -- every line item belongs to an order
• orders.customer_id references customers.id -- every order belongs to a customer

For each foreign key, decide the ON DELETE behaviour. Write it down with your reasoning. This is a design decision worth recording -- six months from now, someone will need to know why you chose RESTRICT on the farm reference.

These relational concepts -- foreign keys, referential integrity, cascade behaviour -- work the same way in PostgreSQL, MySQL, SQLite, or any relational database. The syntax varies. The model doesn't.

Step 4: Design the REST endpoints

Open materials/rest-conventions.md.

The existing API has flat endpoints: GET /farms, GET /batches, GET /orders. The new endpoints need to navigate relationships. The URL structure should communicate the data model:

• GET /batches/:id/orders -- all orders containing items from a specific batch
• GET /farms/:id/batches -- all batches from a specific farm
• GET /customers/:id/orders -- all orders for a specific customer

Compare these to verb-based alternatives: GET /getOrdersByBatch?id=123. The resource-based URL tells anyone reading the API what the data relationship is. The verb-based URL just describes an action.

Direct Claude to draft the endpoint list with URL patterns, HTTP methods, and expected status codes. Cross-model review: does every endpoint URL communicate a relationship? Are status codes consistent?

Update the ticket breakdown with the new work: migration, relational API endpoints, customer portal, CORS configuration, relational tests, logging update, deployment.