Schema and Database

Step 1: Set up the project

Open a terminal and start Claude Code:

cd ~/dev

claude

Paste the setup prompt:

Create the folder ~/dev/web-dev/cacao-ixchel. Download the project materials from https://learnbydirectingai.dev/materials/webdev/cacao-ixchel/materials.zip and extract them into that folder. Read CLAUDE.md — it's the project governance file. Open VS Code in the project directory. Install the Claude Code extension and the Live Server extension if they aren't already installed.

Claude creates the project folder, downloads materials, initializes a Next.js project with App Router and TypeScript, sets up PostgreSQL with Prisma, installs dependencies, and opens VS Code. Your CLAUDE.md loads automatically -- it gives Claude the full ticket list, tech stack, and project structure.

Step 2: Tour the workspace

Look at what Claude set up. The materials/ directory contains everything you need: CLAUDE.md (governance file), seed-data.json (farm, batch, product, and order data), the API contract, and the templates you used in the previous unit.

Open materials/CLAUDE.md. This is the governance file you created during planning. It lists every ticket, the tech stack, the file structure, and the verification targets. Claude reads this at the start of every task. If the governance file is incomplete or vague, Claude's output will reflect that. The quality of what you planned directly affects the quality of what gets built.

Open materials/seed-data.json. This is realistic data representing Marco's business: six farms with Guatemalan names and cacao varieties, batches at various processing stages, finished chocolate products, and orders from specialty shops in Portland, Berlin, and Tokyo. You'll use this to populate the database once the schema exists.

Step 3: Understand relational databases

Before you direct Claude to build anything, you need a mental model of what you're building.

A relational database stores data in tables. Each table has rows (records) and columns (fields). A schema defines the structure: which tables exist, what columns each table has, what types those columns hold, what constraints apply. A migration is a file that changes the schema -- adding a table, renaming a column, creating a constraint. Migrations run in order and are versioned. Once data exists in production, changing the schema means writing a new migration, not editing the old one.

Your local development database and the production database are different environments. Locally, you have seed data and can reset everything. Production starts empty and accumulates real data. A schema change that works locally -- drop the table and recreate it -- would destroy Marco's actual records in production. This gap between environments is small right now but gets wider as the project progresses.

Step 4: Design the schema

Marco's data has clear relationships. A farm grows cacao. Cacao from a farm goes into a batch. A batch produces products (chocolate bars, drinking chocolate, nibs). Products get allocated to orders. That chain -- farm to batch to product to order -- is the traceability Marco needs.

erDiagram
    farms ||--o{ batches : "supplies cacao to"
    batches ||--o{ products : "produces"
    orders ||--o{ products : "allocates"
    farms {
        int id PK
        string name
        string location
        string cacao_variety
    }
    batches {
        int id PK
        int farm_id FK
        date harvest_date
        string stage
        float weight_kg
        string flavour_profile
    }
    products {
        int id PK
        int batch_id FK
        string name
        string type
        int quantity_available
        int allocated_to_order_id FK
    }
    orders {
        int id PK
        string customer_name
        string country
        string status
        datetime created_at
    }

Direct Claude to create the database schema based on your planning artifacts:

Create the Prisma schema for the Cacao Ixchel database. Four tables: farms, batches, products, orders. A batch belongs to one farm. A product traces to one batch. A product can be allocated to at most one order. Use the relationships from the architecture document. Apply the migration.

Specify the relationships explicitly. "A product can be allocated to at most one order" is a constraint that prevents double-allocation -- the exact problem Marco described. If you don't specify this, Claude may design a many-to-many junction table that allows a product to appear in multiple orders simultaneously. That's technically more flexible, but it's architecturally wrong for Marco's business. The schema is the most expensive decision to change after data exists.

Step 5: Review the schema

Check what Claude generated. AI commonly over-normalizes schemas for small operations -- adding junction tables, intermediate entities, and abstraction layers that create unnecessary complexity for a business with thirty customers and six farms.

Open the Prisma schema file. Verify:

• Does the schema support traceability? Can you trace from a product back to its batch and then to the farm? The foreign key chain should be: products.batch_id references batches.id, batches.farm_id references farms.id.
• Does the schema prevent double-allocation? A product's allocated_to_order_id is nullable -- null means available, a value means allocated. A product can have at most one order.
• Does every table serve at least one of Marco's requirements? If a table exists that doesn't map to a requirement, question whether it's needed.

Direct Claude to cross-check the schema against the PRD:

Compare the Prisma schema against the PRD. List every requirement and the table/column that serves it. Flag any requirement that isn't covered and any table that doesn't serve a requirement.

Step 6: Seed the database

Direct Claude to seed the database with the data from materials/seed-data.json. Six farms, twelve batches across various stages, fifteen products from finished batches, and five orders from specialty shops.

Write a seed script that loads data from materials/seed-data.json into the database. Run it. Show me the counts: how many farms, batches, products, and orders. How many products are available vs allocated.

After the script runs, the database should have: 6 farms, 12 batches, 15 products (9 available, 6 allocated), and 5 orders. The allocated products should reference real orders. The batches should span all four stages -- fermenting, drying, roasting, finished. Only finished batches have products.

Verify the relationships work. Run a query through Claude:

Query the database: show all products from the Lacandon farm that are available (not allocated to any order). Show the product name, batch harvest date, and farm name.

The result should return Lacandon farm products where allocated_to_order_id is null. If the query fails, the schema is missing a foreign key or the seed data has a relationship error. Fix it before moving on -- every unit after this depends on the database working correctly.