Relational API

Step 1: Build the traceability endpoints

The database has foreign keys and the trace query works. Now expose that data through the API.

Direct Claude to build two traceability endpoints:

• GET /batches/:id/orders -- returns all orders containing items from a specific batch, including customer details
• GET /farms/:id/batches -- returns all batches from a specific farm, with product counts

The URL structure communicates the relationship. /batches/3/orders tells anyone reading the API that orders belong to batches. AI commonly generates verb-based URLs like /getOrdersByBatch?id=3 instead -- check the generated routes and redirect if needed.

Both endpoints should return 200 with an empty array when the relationship exists but has no data (a batch with no orders). They should return 404 when the parent resource doesn't exist (a batch ID that's not in the database). The difference matters: 200 with [] means "this batch exists but has no orders." 404 means "this batch doesn't exist."

Step 2: Build the customer order endpoint

GET /customers/:id/orders returns all orders for a specific customer, including item details and batch provenance.

This endpoint needs relational validation. Before querying orders, check whether the customer exists. If customer 999 doesn't exist in the database, the response should be 400 with a clear message: { "error": "Customer 999 does not exist" }. Not a 500 with a stack trace from a database constraint violation.

The difference between catching the problem at the route level (400) and letting it hit the database (500) is the difference between a helpful message and noise. The 400 response tells the API consumer what went wrong and how to fix it. The 500 response exposes database internals and helps no one.

Direct Claude to implement this validation. AI often skips relational validation entirely -- it validates individual fields (is the email formatted correctly?) but not foreign key references (does this customer_id actually exist?). Check the generated code for explicit existence checks before the database query.

Step 3: Implement relational validation on order creation

POST /orders creates a new order. Before inserting anything, the endpoint must verify:

• The customer_id references an existing customer
• The batch_id for each order item references an existing batch with available inventory

If either check fails, return 400 with a message naming the specific problem. If you skip these checks and let the database handle it, the customer_id violation produces a 500 with a foreign key constraint error. The batch availability violation might not produce any error at all -- the database doesn't know about business rules like inventory availability.

Step 4: Cross-model review

Direct a second AI to review the API endpoints. The review should check:

• Are all URLs resource-based? Do they communicate relationships?
• Are status codes consistent? (200 for success, 400 for validation failure, 404 for missing resource)
• Is relational validation present on endpoints that accept foreign key references?
• Are error responses helpful? (clear messages, not stack traces)

If the API interacts with the customer portal on a different subdomain, CORS needs to allow that domain specifically -- not all origins. The CORS configuration from the last project covers same-origin requests. Cross-origin requests from the customer portal subdomain need explicit permission.