Build it

We will build the five core tables by hand in SQL, watch the constraints work, and then build the same tables with Prisma. No Docker yet? The Practice section below runs real PostgreSQL in your browser, so you can try the constraint rules there first.

1. Start a practice Postgres 16

   Docker is the quickest way. The database runs on your machine only.

   TerminalCopy

   docker run --name pg16 -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:16
   docker exec -it pg16 createdb -U postgres shop
   docker exec -it pg16 psql -U postgres -d shop   # opens a SQL prompt

2. Create the master tables

   Enums for values the code branches on, a lookup table for categories, surrogate ids, business codes as UNIQUE columns, and money in cents.

   SQLCopy

   CREATE TYPE site_format AS ENUM ('STORE', 'PARTNER', 'DC');
   CREATE TYPE pay_method  AS ENUM ('CASH', 'CARD', 'WALLET');
   
   CREATE TABLE site (
     id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     site_code   text        NOT NULL UNIQUE,          -- the code people say: 'S027'
     name        text        NOT NULL,
     format      site_format NOT NULL,
     city        text        NOT NULL,
     is_active   boolean     NOT NULL DEFAULT true,    -- closed stores keep history
     created_at  timestamptz NOT NULL DEFAULT now(),
     updated_at  timestamptz NOT NULL DEFAULT now()
   );
   
   CREATE TABLE category (                             -- a lookup table
     id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     name  text NOT NULL UNIQUE
   );
   
   CREATE TABLE article (
     id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     sku               text        NOT NULL UNIQUE,    -- barcode on the pack
     name              text        NOT NULL,
     category_id       bigint      NOT NULL REFERENCES category (id),
     unit_price_cents  integer     NOT NULL CHECK (unit_price_cents >= 0),  -- 650 = $6.50
     is_active         boolean     NOT NULL DEFAULT true,
     created_at        timestamptz NOT NULL DEFAULT now(),
     updated_at        timestamptz NOT NULL DEFAULT now()
   );

3. Create stock, sale and sale_line

   Composite primary keys for the junction and line tables, a UNIQUE rule that blocks duplicate receipt uploads, and indexes on the foreign key columns.

   SQLCopy

   CREATE TABLE stock (
     site_id     bigint      NOT NULL REFERENCES site (id),
     article_id  bigint      NOT NULL REFERENCES article (id),
     qty         integer     NOT NULL DEFAULT 0 CHECK (qty >= 0),
     updated_at  timestamptz NOT NULL DEFAULT now(),
     PRIMARY KEY (site_id, article_id)            -- one row per site per article
   );
   CREATE INDEX stock_article_id_idx ON stock (article_id);
   
   CREATE TABLE sale (
     id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     site_id       bigint      NOT NULL REFERENCES site (id),
     local_txn_id  text        NOT NULL,        -- receipt number from the POS
     sale_date     date        NOT NULL,        -- business day in the store's zone
     sold_at       timestamptz NOT NULL DEFAULT now(),
     total_cents   integer     NOT NULL CHECK (total_cents >= 0),
     pay_method    pay_method  NOT NULL,
     UNIQUE (site_id, local_txn_id)             -- a retried upload cannot insert twice
   );
   CREATE INDEX sale_site_id_sale_date_idx ON sale (site_id, sale_date);
   
   CREATE TABLE sale_line (
     sale_id           bigint   NOT NULL REFERENCES sale (id) ON DELETE CASCADE,
     line_no           smallint NOT NULL,
     article_id        bigint   NOT NULL REFERENCES article (id),
     qty               integer  NOT NULL CHECK (qty > 0),
     unit_price_cents  integer  NOT NULL,       -- price at the moment of sale
     PRIMARY KEY (sale_id, line_no)
   );
   CREATE INDEX sale_line_article_id_idx ON sale_line (article_id);

4. Keep updated_at honest with a trigger

   This runs inside the database, so it works for Prisma, psql and any other writer. Repeat the CREATE TRIGGER for each table.

   SQLCopy

   CREATE FUNCTION set_updated_at() RETURNS trigger
   LANGUAGE plpgsql AS $$
   BEGIN
     NEW.updated_at := now();
     RETURN NEW;
   END $$;
   
   CREATE TRIGGER site_set_updated_at
     BEFORE UPDATE ON site
     FOR EACH ROW EXECUTE FUNCTION set_updated_at();

5. Try to break the rules

   Insert one store, one category and one article, then try three bad writes. Each one is stopped with a clear message that names the constraint.

   SQLCopy

   INSERT INTO site (site_code, name, format, city) VALUES ('S001', 'Riverside', 'STORE', 'Bayside');
   INSERT INTO category (name) VALUES ('Grocery');
   INSERT INTO article (sku, name, category_id, unit_price_cents) VALUES ('8901234500012', 'Rice 5kg', 1, 650);
   
   INSERT INTO stock (site_id, article_id, qty) VALUES (1, 1, -1);   -- negative stock
   INSERT INTO stock (site_id, article_id, qty) VALUES (999, 1, 5);  -- store does not exist
   INSERT INTO site (site_code, name, format, city) VALUES ('S001', 'Copy', 'STORE', 'Bayside');

   OutputCopy

   ERROR:  new row for relation "stock" violates check constraint "stock_qty_check"
   ERROR:  insert or update on table "stock" violates foreign key constraint "stock_site_id_fkey"
   DETAIL:  Key (site_id)=(999) is not present in table "site".
   ERROR:  duplicate key value violates unique constraint "site_site_code_key"
   DETAIL:  Key (site_code)=(S001) already exists.

6. Write the same tables in Prisma

   In a fresh NestJS project, run npm i -D prisma, npm i @prisma/client and npx prisma init. Point DATABASE_URL in .env at a new empty database, for example shop_prisma. Models are PascalCase with camelCase fields; @map and @@map keep the real names snake_case.

   PrismaCopy

   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
   }
   generator client {
     provider = "prisma-client-js"
   }
   
   enum SiteFormat {
     STORE
     PARTNER
     DC
     @@map("site_format")
   }
   
   model Site {
     id        BigInt     @id @default(autoincrement())
     siteCode  String     @unique @map("site_code")
     name      String
     format    SiteFormat
     city      String
     isActive  Boolean    @default(true) @map("is_active")
     createdAt DateTime   @default(now()) @map("created_at") @db.Timestamptz(6)
     updatedAt DateTime   @updatedAt @map("updated_at") @db.Timestamptz(6)
     stock     Stock[]
     sales     Sale[]
   
     @@map("site")
   }
   
   model Category {
     id       BigInt    @id @default(autoincrement())
     name     String    @unique
     articles Article[]
   
     @@map("category")
   }

   PrismaCopy

   model Article {
     id             BigInt     @id @default(autoincrement())
     sku            String     @unique
     name           String
     categoryId     BigInt     @map("category_id")
     unitPriceCents Int        @map("unit_price_cents")   // 650 means $6.50
     isActive       Boolean    @default(true) @map("is_active")
     createdAt      DateTime   @default(now()) @map("created_at") @db.Timestamptz(6)
     updatedAt      DateTime   @updatedAt @map("updated_at") @db.Timestamptz(6)
     category       Category   @relation(fields: [categoryId], references: [id])
     stock          Stock[]
     saleLines      SaleLine[]
   
     @@map("article")
   }
   
   model Stock {
     siteId    BigInt   @map("site_id")
     articleId BigInt   @map("article_id")
     qty       Int      @default(0)
     updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamptz(6)
     site      Site     @relation(fields: [siteId], references: [id])
     article   Article  @relation(fields: [articleId], references: [id])
   
     @@id([siteId, articleId])   // composite primary key
     @@index([articleId])        // foreign keys need their own index
     @@map("stock")
   }

   PrismaCopy

   enum PayMethod {
     CASH
     CARD
     WALLET
     @@map("pay_method")
   }
   
   model Sale {
     id         BigInt     @id @default(autoincrement())
     siteId     BigInt     @map("site_id")
     localTxnId String     @map("local_txn_id")
     saleDate   DateTime   @map("sale_date") @db.Date
     soldAt     DateTime   @default(now()) @map("sold_at") @db.Timestamptz(6)
     totalCents Int        @map("total_cents")
     payMethod  PayMethod  @map("pay_method")
     site       Site       @relation(fields: [siteId], references: [id])
     lines      SaleLine[]
   
     @@unique([siteId, localTxnId])
     @@index([siteId, saleDate])
     @@map("sale")
   }
   
   model SaleLine {
     saleId         BigInt  @map("sale_id")
     lineNo         Int     @map("line_no") @db.SmallInt
     articleId      BigInt  @map("article_id")
     qty            Int
     unitPriceCents Int     @map("unit_price_cents")
     sale           Sale    @relation(fields: [saleId], references: [id], onDelete: Cascade)
     article        Article @relation(fields: [articleId], references: [id])
   
     @@id([saleId, lineNo])
     @@index([articleId])
     @@map("sale_line")
   }

7. Create the migration, add what Prisma cannot say, and apply it

   A Prisma schema cannot express CHECK constraints. Create the migration file without applying it, add the checks at the end, then apply. Prisma writes BIGSERIAL for autoincrement(), an older form of identity that behaves the same for you.

   TerminalCopy

   npx prisma migrate dev --create-only --name init   # writes prisma/migrations/<timestamp>_init/migration.sql
   # edit that file: append the CHECK constraints below
   npx prisma migrate dev                              # applies it and regenerates the client

   SQLCopy

   -- appended to the end of migration.sql
   ALTER TABLE "article"   ADD CONSTRAINT article_unit_price_cents_check CHECK (unit_price_cents >= 0);
   ALTER TABLE "stock"     ADD CONSTRAINT stock_qty_check                CHECK (qty >= 0);
   ALTER TABLE "sale"      ADD CONSTRAINT sale_total_cents_check         CHECK (total_cents >= 0);
   ALTER TABLE "sale_line" ADD CONSTRAINT sale_line_qty_check            CHECK (qty > 0);

   Open migration.sql once and read it. It is the real contract with the database, and it should look like steps 2 and 3. Changing the schema without downtime covers how to change these tables later without taking the app down.