You're extending the inventory system for Meridian Dental Laboratories — adding explicit foreign key relationships for lot-to-practice traceability and a practice self-service portal for case status lookup.
The discipline skills: relational data modeling with foreign keys and JOINs, cascade and restrict behaviors, multi-resource REST endpoints that communicate data relationships through their URLs, and relational validation that crosses table boundaries.
The AI-direction lesson: AI defaults to the happy path on relational queries. It generates INNER JOINs without thinking about what happens when one side of the relationship has no data — a materials lot whose cases are still being made silently disappears from the results. Whether that's correct depends on what the query is supposed to mean, and AI doesn't ask. The skill here is catching silent data loss: testing with empty relationships, single matches, and multiple matches before trusting any query AI produces.
Your Role
You are extending a system you already built. The database, the API, the frontend all exist. The tables aren't connected in a way that supports the kind of cross-practice traceability the TGA regulators — and Nicola's dentists — actually need.
You add explicit foreign keys with documented cascade behaviours, write queries that navigate those relationships, and build API endpoints that communicate the data structure through their URLs. You also build a practice-facing portal with scoped access.
The planning pipeline is the same as before — templates, guides, cross-model review. The terrain is new: relational modeling, where a decision about what happens when you delete a materials lot propagates through every case that traces to it.
What's New
Last time you built Nicola'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 with explicit cascade decisions. Foreign keys link cases to materials lots, cases to practices, orders to practices. Each connection has a cascade decision: what happens to the cases when a materials lot is deleted? The database enforces whatever you choose — and gets it wrong if the choice was wrong. TGA traceability bounds the choice on lot deletions.
The API navigates relationships. Instead of flat endpoints that return one table's data, you build endpoints that follow the links: /materials-lots/:id/cases returns every case made from a specific lot across every practice. 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 materials lot whose cases are still in design and haven't been dispatched 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 (explicit foreign keys, JOINs, migrations with existing data).
- React Testing Library + Vitest — continuing.
- Tailwind CSS — continuing.
- Chrome DevTools Performance profiler — continuing.
- Vercel CLI — deployment. Continuing.
Materials
- Nicola's follow-up email — two requests, directly stated. Some gaps you'll need to ask about.
- Migration guide — reference for adding explicit 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 SQL seed with deliberate edge cases: a materials lot with cases in progress but no dispatched orders, a practice with no orders, a shared-incident lot whose cases went to three different practices.
- CLAUDE.md — project governance file with the ticket list, tech stack, and verification targets for the extension work.