apps/backend/src/infrastructure/database/migrations/0022_sprint_k_purchasing.sqlSQL
-- Sprint K — suppliers, supplier catalog, purchase orders and receiving.
create table if not exists suppliers (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
name text not null,
legal_name text,
supplier_number text,
email text,
phone text,
website text,
address jsonb not null default '{}'::jsonb,
currency_code char(3),
payment_terms_days integer,
minimum_order_minor bigint,
status text not null default 'active',
notes text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint suppliers_status_check check(status in ('active','inactive','archived'))
);
create unique index if not exists idx_suppliers_org_supplier_number
on suppliers(organization_id, supplier_number)
where supplier_number is not null;
create table if not exists supplier_catalog_items (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
supplier_id uuid not null references suppliers(id) on delete cascade,
stock_item_id uuid not null references stock_items(id) on delete cascade,
supplier_sku text,
supplier_product_name text not null,
purchase_unit_code text not null references units(code) on delete restrict,
units_per_purchase_unit numeric(24,8) not null default 1,
normalized_unit_code text not null references units(code) on delete restrict,
unit_price_minor bigint not null,
currency_code char(3) not null,
tax_rate_basis_points integer,
minimum_order_quantity numeric(24,8),
lead_time_days integer,
is_preferred boolean not null default false,
is_active boolean not null default true,
last_price_at timestamptz not null default now(),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint supplier_catalog_units_positive check(units_per_purchase_unit > 0),
constraint supplier_catalog_price_nonnegative check(unit_price_minor >= 0),
constraint supplier_catalog_unique unique(supplier_id, stock_item_id, purchase_unit_code)
);
create index if not exists idx_supplier_catalog_stock
on supplier_catalog_items(stock_item_id, is_active);
create table if not exists purchase_orders (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
supplier_id uuid not null references suppliers(id) on delete restrict,
po_number text not null,
status text not null default 'draft',
currency_code char(3) not null,
ordered_at timestamptz,
expected_delivery_date date,
submitted_at timestamptz,
confirmed_at timestamptz,
received_at timestamptz,
closed_at timestamptz,
cancelled_at timestamptz,
created_by uuid references user_accounts(id) on delete set null,
notes text,
version bigint not null default 1,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint purchase_orders_status_check check(
status in ('draft','submitted','confirmed','partially_received','received','closed','cancelled')
),
constraint purchase_orders_location_number_unique unique(location_id, po_number)
);
create index if not exists idx_purchase_orders_supplier_status
on purchase_orders(supplier_id, status, created_at);
create table if not exists purchase_order_lines (
id uuid primary key default gen_random_uuid(),
purchase_order_id uuid not null references purchase_orders(id) on delete cascade,
supplier_catalog_item_id uuid references supplier_catalog_items(id) on delete set null,
stock_item_id uuid not null references stock_items(id) on delete restrict,
supplier_sku_snapshot text,
product_name_snapshot text not null,
ordered_quantity numeric(24,8) not null,
purchase_unit_code text not null references units(code) on delete restrict,
units_per_purchase_unit_snapshot numeric(24,8) not null,
normalized_unit_code text not null references units(code) on delete restrict,
unit_price_minor_snapshot bigint not null,
currency_code char(3) not null,
received_quantity numeric(24,8) not null default 0,
overdelivery_allowed boolean not null default false,
notes text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint po_line_qty_positive check(ordered_quantity > 0),
constraint po_line_price_nonnegative check(unit_price_minor_snapshot >= 0)
);
create index if not exists idx_po_lines_order_stock
on purchase_order_lines(purchase_order_id, stock_item_id);
create table if not exists goods_receipts (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
purchase_order_id uuid references purchase_orders(id) on delete set null,
supplier_id uuid not null references suppliers(id) on delete restrict,
receipt_number text,
received_at timestamptz not null default now(),
received_by uuid references user_accounts(id) on delete set null,
notes text,
status text not null default 'posted',
created_at timestamptz not null default now(),
constraint goods_receipt_status_check check(status in ('draft','posted','cancelled'))
);
create index if not exists idx_goods_receipts_po_time
on goods_receipts(purchase_order_id, received_at);
create table if not exists goods_receipt_lines (
id uuid primary key default gen_random_uuid(),
goods_receipt_id uuid not null references goods_receipts(id) on delete cascade,
purchase_order_line_id uuid references purchase_order_lines(id) on delete set null,
stock_item_id uuid not null references stock_items(id) on delete restrict,
storage_location_id uuid not null references storage_locations(id) on delete restrict,
received_quantity numeric(24,8) not null,
purchase_unit_code text not null references units(code) on delete restrict,
units_per_purchase_unit_snapshot numeric(24,8) not null,
normalized_quantity numeric(24,8) not null,
normalized_unit_code text not null references units(code) on delete restrict,
unit_price_minor_snapshot bigint not null,
currency_code char(3) not null,
lot_number text,
best_before_date date,
notes text,
created_at timestamptz not null default now(),
constraint goods_receipt_line_qty_positive check(received_quantity > 0)
);
create index if not exists idx_goods_receipt_lines_stock
on goods_receipt_lines(stock_item_id, created_at);
create table if not exists purchase_suggestions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
stock_item_id uuid not null references stock_items(id) on delete cascade,
supplier_id uuid references suppliers(id) on delete set null,
suggested_quantity numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
reason text not null,
status text not null default 'open',
generated_at timestamptz not null default now(),
resolved_at timestamptz,
constraint purchase_suggestion_status_check check(status in ('open','accepted','dismissed'))
);
create index if not exists idx_purchase_suggestions_location_status
on purchase_suggestions(location_id, status, generated_at);
insert into permissions(permission_key, description) values
('purchasing.read', 'Read suppliers and purchase orders'),
('purchasing.manage_suppliers', 'Manage suppliers and catalogs'),
('purchasing.create_po', 'Create purchase orders'),
('purchasing.submit_po', 'Submit purchase orders'),
('purchasing.receive', 'Receive goods against purchase orders'),
('purchasing.suggestions.read', 'Read purchase suggestions')
on conflict(permission_key) do nothing;