PostgreSQL

Inventory and warehouse database schema

An inventory schema for multi-warehouse operations: SKUs, bins, on-hand quantities per bin, an immutable stock_movement log, and physical counts that reconcile against the log.

Start for free

Database schema previewInventory and warehouse database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: Inventory and warehouse database schema.

An inventory schema for multi-warehouse operations: SKUs, bins, on-hand quantities per bin, an immutable stock_movement log, and physical counts that reconcile against the log.

Include these tables: sku, warehouse, bin, on_hand, stock_movement, physical_count.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- On-hand quantities are denormalized for fast reads.
- stock_movement is the source of truth (append-only).
- Periodic counts reconcile on_hand against the movement log.

Patterns

- on_hand denormalized for O(1) reads.
- stock_movement append-only - never UPDATE.
- Reconciliation job compares physical_count against stock_movement.

Tables

The schema, table by table

sku

DBML - sku

Table sku {
  id     uuid pk
  code   text unique not null
  name   text not null
  unit   text default `\'each\'`
}

warehouse

DBML - warehouse

Table warehouse {
  id uuid pk
  name text not null
  country_code text not null
}

bin

DBML - bin

Table bin {
  id           uuid pk
  warehouse_id uuid [ref: > warehouse.id, not null]
  code         text not null
}

on_hand

Denormalized current stock per (sku, bin).

DBML - on_hand

Table on_hand {
  sku_id   uuid [ref: > sku.id]
  bin_id   uuid [ref: > bin.id]
  quantity int not null default 0
  pk (sku_id, bin_id)
}

stock_movement

Append-only log of every quantity change.

DBML - stock_movement

Table stock_movement {
  id        uuid pk
  sku_id    uuid [ref: > sku.id, not null]
  from_bin  uuid [ref: > bin.id]
  to_bin    uuid [ref: > bin.id]
  quantity  int not null
  reason    text not null   // receive, ship, transfer, adjust
  reference text
  created_at timestamptz default `now()`
}

physical_count

DBML - physical_count

Table physical_count {
  id           uuid pk
  warehouse_id uuid [ref: > warehouse.id]
  counted_at   timestamptz default `now()`
  sku_id       uuid [ref: > sku.id]
  bin_id       uuid [ref: > bin.id]
  counted_qty  int not null
}

Relationships

From	To	Type
warehouse	bin	1:N
sku	on_hand	1:N
bin	on_hand	1:N
sku	stock_movement	1:N
warehouse	physical_count	1:N

Notes from the field

Use CHECK (quantity >= 0) on on_hand for safety.
A receive is a movement with NULL from_bin; a ship is NULL to_bin.
Aggregate stock_movement into materialised views for reports if needed.

Frequently asked

Why both on_hand and stock_movement?

on_hand is for fast UI reads, stock_movement is the auditable source of truth. A periodic reconciliation catches drift.

How do transfers between bins work?

A single stock_movement row with both from_bin and to_bin. The app updates two on_hand rows atomically.

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.