The Brief

Marco's inventory system is working. His team uses it every day.

Now he needs two things. A customer in Munich reported an off-flavour in their bars. Marco needs to trace back: which batch, which farm, which other customers got bars from the same batch. Right now he searches through everything manually. That's fine when one customer complains. It's not fine when the answer determines whether thirty other orders need to be recalled.

His shops in Japan and Germany also want to check their own order status instead of emailing him. He spends an hour a day answering "where's my order?" emails. They should be able to look it up themselves -- but they should not see other customers' orders or Marco's internal inventory.

Your Role

You are extending a system you already built. The database, the API, the frontend -- they all exist. But the tables aren't connected in a way that lets Marco trace relationships between them.

You add foreign keys, write queries that navigate those relationships, and build API endpoints that communicate the data structure through their URLs. You build a customer portal where shops see their own orders and nothing else.

The planning pipeline is the same as before -- templates and guides for the artifacts, cross-model review for verification. The terrain is new: relational modelling, where a decision about what happens when you delete a record propagates through every table that references it.

What's New

Last time you built Marco's system from scratch -- schema, API, frontend, security, observability, deployment. You designed the database tables, wrote REST routes, configured CSP and CORS, added structured logging, and deployed to production.

Two things change.

You connect data across tables. Foreign keys link batches to farms, products to batches, orders to customers. Each connection has a cascade decision: what happens to the orders when you delete a customer? The database enforces whatever you choose -- and gets it wrong if the choice was wrong.

The API navigates relationships. Instead of flat endpoints that return one table's data, you build endpoints that follow the links: /batches/123/orders returns all orders that include items from batch 123. The URL tells anyone reading it what the data relationship is.

The hard part is the JOIN. When you query across two tables, the result depends on whether both sides of the relationship have data. A batch with no orders silently disappears from an INNER JOIN. Whether that's correct depends on what the query is supposed to mean -- and AI defaults to INNER JOIN without thinking about it.

Tools

• Claude Code -- AI coding agent, VS Code extension. Primary tool.
• Git and GitHub -- version control, remote repo, issues, project board.
• VS Code with Claude Code extension and ESLint.
• Next.js with App Router -- continuing.
• PostgreSQL -- relational features deepen (foreign keys, JOINs, migrations).
• React Testing Library + Vitest -- continuing.
• Tailwind CSS -- continuing.
• Chrome DevTools Performance profiler -- continuing.
• Vercel CLI -- deployment. Continuing.

Materials

• Marco's follow-up email -- two requests, directly stated. Some gaps you'll need to ask about.
• Migration guide -- reference for adding foreign keys to a database that already has data. Covers CASCADE, RESTRICT, SET NULL, and what to check before and after.
• REST conventions reference -- naming patterns for endpoints that navigate relationships. Good and bad examples.
• Seed data -- relational test data with deliberate edge cases: batches with no orders, customers with no orders, farms with a single batch.
• CLAUDE.md -- project governance file with the ticket list, tech stack, and verification targets for the extension work.