Cacao Ixchel -- Relational Traceability and Customer Portal

Client

Marco Aju, Owner and Production Manager at Cacao Ixchel, a small bean-to-bar chocolate maker in Coban, Guatemala. Cacao Ixchel sources cacao from six family farms, processes it in-house, and sells single-origin bars to about thirty specialty shops in the US, Germany, and Japan.

What you're building

Extending the existing inventory and order management system with two features: (1) relational traceability -- connecting farms, batches, products, and orders through foreign keys so Marco can trace a quality issue from a customer complaint through the batch to the source farm and to all affected customers, and (2) a customer self-service order portal where Marco's shops can check their own order status without emailing him.

Tech stack

• Next.js with App Router (SSR/SSG)
• PostgreSQL (relational database with foreign keys)
• React Testing Library + Vitest (testing)
• Tailwind CSS (styling)
• Vercel (deployment)

File structure

materials/
  CLAUDE.md              -- this file
  first-contact.md       -- Marco's follow-up email
  migration-guide.md     -- schema migration reference
  rest-conventions.md    -- REST naming conventions
  seed-data.sql          -- relational test data
  images/                -- generated images (avatar, product photos)

Key materials

• first-contact.md -- Marco's follow-up email describing the traceability and customer portal requests
• migration-guide.md -- reference for schema migrations with foreign keys and ON DELETE behaviours
• rest-conventions.md -- REST naming conventions for relational endpoints
• seed-data.sql -- relational seed data with edge cases (batches with no orders, customers with no orders)

Tickets

1. T01: Schema migration -- add foreign keys connecting batches to farms, products to batches, order_items to orders, orders to customers. Specify ON DELETE for each FK.
2. T02: Seed data -- insert relational test data including edge cases (batch with no orders, customer with no orders).
3. T03: Trace query -- write and test the trace query from batch to farm to affected orders/customers. Verify with INNER JOIN vs LEFT JOIN.
4. T04: Traceability API -- GET /batches/:id/orders, GET /farms/:id/batches. Resource-based URLs.
5. T05: Customer order API -- GET /customers/:id/orders. Relational validation (404 for non-existent customer, empty array for customer with no orders).
6. T06: Order creation validation -- POST /orders validates customer_id and batch_id exist. Returns 400 with message, not 500 with stack trace.
7. T07: Customer portal frontend -- order list page showing own orders with batch provenance. SSR.
8. T08: Traceability admin view -- trace from batch to farm to orders to customers. Admin-only.
9. T09: CORS configuration -- allow customer portal subdomain, restrict all other origins.
10. T10: Relational tests -- test JOINs with complete data, empty relationships, and multiple matches.
11. T11: Structured logging update -- add relational context (batch_id, farm_name, customer_count) to log entries.
12. T12: Deploy and close -- deploy extended system, verify customer portal in production, push to GitHub, update README.

Verification targets

• Every foreign key has an explicit ON DELETE behaviour (CASCADE, RESTRICT, or SET NULL)
• Trace query returns farm-to-customer chain including batches with zero orders (LEFT JOIN)
• Customer portal shows only the authenticated customer's orders
• All REST endpoints use resource-based URLs (/batches/:id/orders, not /getOrdersByBatch)
• Relational validation returns 400 for invalid FK references, not 500
• Relational tests pass including empty-relationship edge cases
• Structured logs include batch_id and farm_name context fields

Commit convention

Commit after each ticket. Use descriptive messages: feat: add foreign keys with cascade behaviours, test: relational edge cases for batch traceability, etc.