Building the schema
🏠The everyday picture
The architect's plan becomes foundations, walls and wiring, built in order, inspected at each stage, with a record of every step.
📘What it really is
-
Write the schema as migrations (Module 58), or a Prisma schema with generated migrations (Module 61). One logical step per migration.
-
Checklist for each table:
- primary key (uuid or identity);
created_at, andupdated_atwhere rows change;- correct types (money in paise or
numeric,timestamptz, text for phones); NOT NULL,UNIQUE,CHECKandDEFAULTconstraints;- foreign keys with deliberate delete rules;
- indexes on foreign keys and common filters (Module 57);
-
soft delete (
deleted_at) where history must be kept. -
Seed data: a script that fills a fresh database with realistic fake data for development and demos (never real people's data). Make it repeatable.
-
Keep
schema.mdupdated alongside: each table's purpose, important rules, and deliberate denormalisations.
🖱️Try it
Write the migrations for your full CRM schema and a seed script producing at least 200 customers, 50 products, 1,000 orders with items and payments. Rebuild the database from nothing with one command and check it.
✅Quick check
Why should a seed script never use real customer data?
Show the answer
Development databases are less protected and widely shared; real personal data there is a privacy risk.
What should every foreign key have, according to this checklist?
Show the answer
A deliberate delete rule and, usually, an index.