Data dictionary

online-shop · engine: PostgreSQL · generated on 2026-10-12 14:39 with Schevo

62 tables105 relationships391 columns0 errors · 4 validation warnings

cat_attribute public.cat_attribute

Attributes that tell variants apart (size, color, capacity...).

Column	Type	Nullable	Key
attribute_id	`BIGINT`	NOT NULL	PK
name	`VARCHAR(40)`	NOT NULL
unit	`VARCHAR(10)`	yes

Keys and indexes

Primary key cat_attribute_pk (attribute_id)
UNIQUE cat_attribute_name_uk (name)

Referenced by

cat_variant_attribute (attribute_id) cat_attribute_cat_variant_attribute_fk · ON DELETE RESTRICT

cat_brand public.cat_brand

Product brands.

Column	Type	Nullable	Key
brand_id	`BIGINT`	NOT NULL	PK
name	`VARCHAR(80)`	NOT NULL
website	`VARCHAR(200)`	yes
logo	`BYTEA`	yes

Keys and indexes

Primary key cat_brand_pk (brand_id)
UNIQUE cat_brand_name_uk (name)

Referenced by

cat_product (brand_id) cat_brand_cat_product_fk · ON DELETE SET NULL

cat_category public.cat_category

Category tree of the catalog.

Column	Type	Nullable	Default	Key	Comment
category_id	`BIGINT`	NOT NULL		PK
parent_id	`BIGINT`	yes		FK	Parent category; null for root categories.
slug	`VARCHAR(80)`	NOT NULL			URL fragment of the category.
name	`VARCHAR(80)`	NOT NULL
sort_order	`SMALLINT`	NOT NULL	`0`
visible	`BOOLEAN`	NOT NULL	`true`

Keys and indexes

Primary key cat_category_pk (category_id)
UNIQUE cat_category_slug_uk (slug)

References

parent_id → cat_category (category_id) cat_category_cat_category_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

Referenced by

cat_category (parent_id) cat_category_cat_category_fk · ON DELETE RESTRICT
cat_product_category (category_id) cat_category_cat_product_category_fk · ON DELETE CASCADE

cat_image public.cat_image

Images of the product or of a specific variant.

Column	Type	Nullable	Default	Key
image_id	`BIGINT`	NOT NULL		PK
product_id	`BIGINT`	NOT NULL		FK
variant_id	`BIGINT`	yes		FK
url	`VARCHAR(400)`	NOT NULL
alt_text	`VARCHAR(200)`	yes
sort_order	`SMALLINT`	NOT NULL	`0`

Keys and indexes

Primary key cat_image_pk (image_id)

References

product_id → cat_product (product_id) cat_product_cat_image_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
variant_id → cat_variant (variant_id) cat_variant_cat_image_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

cat_price_history public.cat_price_history

Price history of each variant.

Column	Type	Nullable	Key
variant_id	`BIGINT`	NOT NULL	PKFK
valid_from	`TIMESTAMP WITH TIME ZONE`	NOT NULL	PK
valid_until	`TIMESTAMP WITH TIME ZONE`	yes
price	`NUMERIC(12,2)`	NOT NULL
changed_by	`BIGINT`	yes	FK

Keys and indexes

Primary key cat_price_history_pk (variant_id, valid_from)
CHECK cat_price_history_dates_ck (valid_until IS NULL OR valid_until > valid_from)

References

variant_id → cat_variant (variant_id) cat_variant_cat_price_history_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
changed_by → emp_employee (employee_id) emp_employee_cat_price_history_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

cat_product public.cat_product

Product sheet; price and stock are per variant.

Column	Type	Nullable	Default	Key	Comment
product_id	`BIGINT`	NOT NULL		PK
brand_id	`BIGINT`	yes		FK
sku	`VARCHAR(30)`	NOT NULL
name	`VARCHAR(150)`	NOT NULL
description	`TEXT`	yes
specifications	`JSONB`	yes			Free-form specifications in JSON.
weight_grams	`INTEGER`	yes			Weight including packaging, to compute the shipping rate.
tax_code	`VARCHAR(10)`	NOT NULL	`'standard'`	FK
active	`BOOLEAN`	NOT NULL	`true`
created_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`
updated_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key cat_product_pk (product_id)
UNIQUE cat_product_sku_uk (sku)
Index cat_product_brand_idx (brand_id)
Index cat_product_created_idx (created_at DESC)
CHECK cat_product_weight_grams_check (weight_grams > 0)

References

brand_id → cat_brand (brand_id) cat_brand_cat_product_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
tax_code → pay_tax (code) pay_tax_cat_product_fk · ON DELETE RESTRICT · ON UPDATE CASCADE

Referenced by

cat_image (product_id) cat_product_cat_image_fk · ON DELETE CASCADE
cat_product_category (product_id) cat_product_cat_product_category_fk · ON DELETE CASCADE
cat_product_supplier (product_id) cat_product_cat_product_supplier_fk · ON DELETE CASCADE
cat_variant (product_id) cat_product_cat_variant_fk · ON DELETE CASCADE
mkt_review (product_id) review_product_fk · ON DELETE CASCADE
mkt_wishlist_product (product_id) cat_product_mkt_wishlist_product_fk · ON DELETE CASCADE

cat_product_category public.cat_product_category

Categories each product appears in.

Column	Type	Nullable	Default	Key
product_id	`BIGINT`	NOT NULL		PKFK
category_id	`BIGINT`	NOT NULL		PKFK
is_primary	`BOOLEAN`	NOT NULL	`false`

Keys and indexes

Primary key cat_product_category_pk (product_id, category_id)

References

category_id → cat_category (category_id) cat_category_cat_product_category_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
product_id → cat_product (product_id) cat_product_cat_product_category_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

cat_product_supplier public.cat_product_supplier

Suppliers of each product and their purchase cost.

Column	Type	Nullable	Default	Key
product_id	`BIGINT`	NOT NULL		PKFK
supplier_id	`BIGINT`	NOT NULL		PKFK
supplier_reference	`VARCHAR(40)`	yes
cost	`NUMERIC(12,2)`	NOT NULL
preferred	`BOOLEAN`	NOT NULL	`false`

Keys and indexes

Primary key cat_product_supplier_pk (product_id, supplier_id)
CHECK cat_product_supplier_cost_check (cost >= 0)

References

product_id → cat_product (product_id) cat_product_cat_product_supplier_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
supplier_id → cat_supplier (supplier_id) cat_supplier_cat_product_supplier_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

cat_supplier public.cat_supplier

Suppliers the goods are bought from.

Column	Type	Nullable	Default	Key
supplier_id	`BIGINT`	NOT NULL		PK
legal_name	`VARCHAR(150)`	NOT NULL
tax_id	`VARCHAR(9)`	NOT NULL
email	`EMAIL`	yes
country_code	`CHAR(2)`	NOT NULL		FK
lead_time_days	`SMALLINT`	NOT NULL	`7`

Keys and indexes

Primary key cat_supplier_pk (supplier_id)
UNIQUE cat_supplier_tax_id_uk (tax_id)
CHECK cat_supplier_lead_time_days_check (lead_time_days > 0)

References

country_code → sys_country (code) sys_country_cat_supplier_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

cat_product_supplier (supplier_id) cat_supplier_cat_product_supplier_fk · ON DELETE RESTRICT

cat_variant public.cat_variant

Sellable combination of a product (size, color...), with its SKU and price.

Column	Type	Nullable	Default	Key	Comment
variant_id	`BIGINT`	NOT NULL		PK
product_id	`BIGINT`	NOT NULL		FK
sku	`VARCHAR(40)`	NOT NULL
barcode	`VARCHAR(14)`	yes			EAN-13 or GTIN-14.
price	`NUMERIC(12,2)`	NOT NULL
compare_at_price	`NUMERIC(12,2)`	yes			Previous price shown struck through in offers.
currency_code	`CHAR(3)`	NOT NULL	`'EUR'`	FK
active	`BOOLEAN`	NOT NULL	`true`

Keys and indexes

Primary key cat_variant_pk (variant_id)
UNIQUE cat_variant_sku_uk (sku)
Unique index cat_variant_barcode_idx (barcode)
CHECK cat_variant_compare_at_ck (compare_at_price IS NULL OR compare_at_price > price)
CHECK cat_variant_price_check (price >= 0)

References

product_id → cat_product (product_id) cat_product_cat_variant_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
currency_code → sys_currency (code) sys_currency_cat_variant_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

cat_image (variant_id) cat_variant_cat_image_fk · ON DELETE SET NULL
cat_price_history (variant_id) cat_variant_cat_price_history_fk · ON DELETE CASCADE
cat_variant_attribute (variant_id) cat_variant_cat_variant_attribute_fk · ON DELETE CASCADE
ord_cart_line (variant_id) cat_variant_ord_cart_line_fk · ON DELETE CASCADE
ord_order_line (variant_id) cat_variant_ord_order_line_fk · ON DELETE RESTRICT
wh_movement (variant_id) cat_variant_wh_movement_fk · ON DELETE RESTRICT
wh_stock (variant_id) cat_variant_wh_stock_fk · ON DELETE RESTRICT

cat_variant_attribute public.cat_variant_attribute

Value of each attribute in each variant.

Column	Type	Nullable	Key
variant_id	`BIGINT`	NOT NULL	PKFK
attribute_id	`BIGINT`	NOT NULL	PKFK
value	`VARCHAR(80)`	NOT NULL

Keys and indexes

Primary key cat_variant_attribute_pk (variant_id, attribute_id)

References

attribute_id → cat_attribute (attribute_id) cat_attribute_cat_variant_attribute_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
variant_id → cat_variant (variant_id) cat_variant_cat_variant_attribute_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

cus_address public.cus_address

Address book of each customer.

Column	Type	Nullable	Default	Key	Comment
address_id	`BIGINT`	NOT NULL		PK
customer_id	`BIGINT`	NOT NULL		FK
alias	`VARCHAR(40)`	yes
recipient	`VARCHAR(120)`	NOT NULL
linea1	`VARCHAR(150)`	NOT NULL
linea2	`VARCHAR(150)`	yes
postal_code	`VARCHAR(10)`	NOT NULL
city	`VARCHAR(80)`	NOT NULL
region	`VARCHAR(80)`	yes
country_code	`CHAR(2)`	NOT NULL		FK
phone	`VARCHAR(20)`	yes
is_default	`BOOLEAN`	NOT NULL	`false`		Address proposed by default at checkout.

Keys and indexes

Primary key cus_address_pk (address_id)
Index cus_address_customer_idx (customer_id)

References

customer_id → cus_customer (customer_id) cus_customer_cus_address_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
country_code → sys_country (code) sys_country_cus_address_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

ord_order (shipping_address_id) cus_address_ord_order_fk · ON DELETE RESTRICT
ord_order (billing_address_id) cus_address_ord_order_fk2 · ON DELETE RESTRICT

cus_consent public.cus_consent

GDPR consents granted or withdrawn by the customer.

Column	Type	Nullable	Default	Key
customer_id	`BIGINT`	NOT NULL		PKFK
type	`VARCHAR(30)`	NOT NULL		PK
granted	`BOOLEAN`	NOT NULL
date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`
ip	`INET`	yes

Keys and indexes

Primary key cus_consent_pk (customer_id, type)
CHECK cus_consent_type_check (type IN ('newsletter', 'profiling', 'third_parties'))

References

customer_id → cus_customer (customer_id) cus_customer_cus_consent_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

cus_customer public.cus_customer

Registered customers of the shop.

Column	Type	Nullable	Default	Key	Comment
customer_id	`BIGINT`	NOT NULL		PK
loyalty_points	`INTEGER`	NOT NULL	`0`
public_uuid	`UUID`	NOT NULL	`gen_random_uuid()`		Identifier exposed in URLs and in the API instead of customer_id.
email	`EMAIL`	NOT NULL
name	`VARCHAR(60)`	NOT NULL
last_name	`VARCHAR(100)`	yes
phone	`VARCHAR(20)`	yes
birth_date	`DATE`	yes
language	`CHAR(2)`	NOT NULL	`'en'`
currency_code	`CHAR(3)`	NOT NULL	`'EUR'`	FK
account_manager_id	`BIGINT`	yes		FK	Assigned sales rep (wholesale customers only).
password_hash	`BYTEA`	yes			bcrypt hash of the password.
tags	`TEXT[]`	yes			Free-form tags set by customer service.
registered_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`
deregistered_at	`TIMESTAMP WITH TIME ZONE`	yes

Keys and indexes

Primary key cus_customer_pk (customer_id)
UNIQUE cus_customer_email_uk (email)
UNIQUE cus_customer_uuid_uk (public_uuid)

References

account_manager_id → emp_employee (employee_id) emp_employee_cus_customer_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
currency_code → sys_currency (code) sys_currency_cus_customer_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

cus_address (customer_id) cus_customer_cus_address_fk · ON DELETE CASCADE
cus_consent (customer_id) cus_customer_cus_consent_fk · ON DELETE CASCADE
cus_customer_segment (customer_id) cus_customer_cus_customer_segment_fk · ON DELETE CASCADE
cus_internal_note (customer_id) cus_customer_cus_internal_note_fk · ON DELETE CASCADE
mkt_coupon_use (customer_id) cus_customer_mkt_coupon_use_fk · ON DELETE CASCADE
mkt_review (customer_id) cus_customer_mkt_review_fk · ON DELETE CASCADE
mkt_review_vote (customer_id) cus_customer_mkt_review_vote_fk · ON DELETE CASCADE
mkt_wishlist (customer_id) cus_customer_mkt_wishlist_fk · ON DELETE CASCADE
ord_cart (customer_id) cus_customer_ord_cart_fk · ON DELETE CASCADE
ord_order (customer_id) fk_order_customer · ON DELETE RESTRICT
pay_invoice (customer_id) cus_customer_pay_invoice_fk · ON DELETE RESTRICT
pay_saved_method (customer_id) cus_customer_pay_saved_method_fk · ON DELETE CASCADE

cus_customer_segment public.cus_customer_segment

Customer membership in segments.

Column	Type	Nullable	Default	Key
customer_id	`BIGINT`	NOT NULL		PKFK
segment_id	`BIGINT`	NOT NULL		PKFK
start_date	`DATE`	NOT NULL	`CURRENT_DATE`

Keys and indexes

Primary key cus_customer_segment_pk (customer_id, segment_id)

References

customer_id → cus_customer (customer_id) cus_customer_cus_customer_segment_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
segment_id → cus_segment (segment_id) cus_segment_cus_customer_segment_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

cus_internal_note public.cus_internal_note

Customer service notes, visible to staff only.

Column	Type	Nullable	Default	Key
note_id	`BIGINT`	NOT NULL		PK
customer_id	`BIGINT`	NOT NULL		FK
employee_id	`BIGINT`	NOT NULL		FK
body	`TEXT`	NOT NULL
created_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key cus_internal_note_pk (note_id)

References

customer_id → cus_customer (customer_id) cus_customer_cus_internal_note_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
employee_id → emp_employee (employee_id) emp_employee_cus_internal_note_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

cus_segment public.cus_segment

Customer segments for campaigns (VIP, wholesale, inactive...).

Column	Type	Nullable	Key
segment_id	`BIGINT`	NOT NULL	PK
name	`VARCHAR(40)`	NOT NULL
description	`TEXT`	yes

Keys and indexes

Primary key cus_segment_pk (segment_id)
UNIQUE cus_segment_name_key (name)

Referenced by

cus_customer_segment (segment_id) cus_segment_cus_customer_segment_fk · ON DELETE CASCADE

emp_absence public.emp_absence

Vacations, sick leave and other leave.

Column	Type	Nullable	Key	Comment
absence_id	`BIGINT`	NOT NULL	PK
employee_id	`BIGINT`	NOT NULL	FK
type	`VARCHAR(20)`	NOT NULL
start_date	`DATE`	NOT NULL
end_date	`DATE`	NOT NULL
approved_by	`BIGINT`	yes	FK	Employee who approved the absence.

Keys and indexes

Primary key emp_absence_pk (absence_id)
CHECK emp_absence_dates_ck (end_date >= start_date)
CHECK emp_absence_type_check (type IN ('vacation', 'sick_leave', 'leave'))

References

employee_id → emp_employee (employee_id) emp_employee_emp_absence_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
approved_by → emp_employee (employee_id) emp_employee_emp_absence_fk2 · ON DELETE SET NULL · ON UPDATE NO ACTION

emp_department public.emp_department

Company departments.

Column	Type	Nullable	Key
department_id	`BIGINT`	NOT NULL	PK
name	`VARCHAR(80)`	NOT NULL
cost_center	`VARCHAR(20)`	yes

Keys and indexes

Primary key emp_department_pk (department_id)
UNIQUE emp_department_name_uk (name)

Referenced by

emp_employee (department_id) emp_department_emp_employee_fk · ON DELETE RESTRICT

emp_employee public.emp_employee

Office and warehouse staff.

Column	Type	Nullable	Default	Key	Comment
employee_id	`BIGINT`	NOT NULL		PK
department_id	`BIGINT`	NOT NULL		FK
manager_id	`BIGINT`	yes		FK	Direct manager of the employee.
warehouse_id	`BIGINT`	yes		FK	Warehouse they work at; null for office staff.
national_id	`VARCHAR(9)`	NOT NULL
name	`VARCHAR(60)`	NOT NULL
last_name	`VARCHAR(100)`	NOT NULL
email	`EMAIL`	NOT NULL
hire_date	`DATE`	NOT NULL	`CURRENT_DATE`
leave_date	`DATE`	yes
active	`BOOLEAN`	NOT NULL	`true`

Keys and indexes

Primary key emp_employee_pk (employee_id)
UNIQUE emp_employee_national_id_uk (national_id)
CHECK emp_employee_dates_ck (leave_date IS NULL OR leave_date >= hire_date)

References

department_id → emp_department (department_id) emp_department_emp_employee_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
manager_id → emp_employee (employee_id) emp_employee_emp_employee_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
warehouse_id → wh_warehouse (warehouse_id) wh_warehouse_emp_employee_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

Referenced by

cat_price_history (changed_by) emp_employee_cat_price_history_fk · ON DELETE SET NULL
cus_customer (account_manager_id) emp_employee_cus_customer_fk · ON DELETE SET NULL
cus_internal_note (employee_id) emp_employee_cus_internal_note_fk · ON DELETE RESTRICT
emp_absence (employee_id) emp_employee_emp_absence_fk · ON DELETE CASCADE
emp_absence (approved_by) emp_employee_emp_absence_fk2 · ON DELETE SET NULL
emp_employee (manager_id) emp_employee_emp_employee_fk · ON DELETE SET NULL
emp_employee_shift (employee_id) emp_employee_emp_employee_shift_fk · ON DELETE CASCADE
mkt_campaign (manager_id) emp_employee_mkt_campaign_fk · ON DELETE SET NULL
ord_return (reviewed_by) emp_employee_ord_return_fk · ON DELETE SET NULL
ord_status_history (employee_id) emp_employee_ord_status_history_fk · ON DELETE SET NULL
wh_movement (employee_id) emp_employee_wh_movement_fk · ON DELETE SET NULL
wh_stocktake (manager_id) emp_employee_wh_stocktake_fk · ON DELETE SET NULL
wh_warehouse (manager_id) emp_employee_wh_warehouse_fk · ON DELETE SET NULL

emp_employee_shift public.emp_employee_shift

Rota: which employee works which shift each day.

Column	Type	Nullable	Key
employee_id	`BIGINT`	NOT NULL	PKFK
shift_id	`BIGINT`	NOT NULL	PKFK
date	`DATE`	NOT NULL	PK

Keys and indexes

Primary key emp_employee_shift_pk (employee_id, shift_id, date)

References

employee_id → emp_employee (employee_id) emp_employee_emp_employee_shift_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
shift_id → emp_shift (shift_id) emp_shift_emp_employee_shift_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

emp_shift public.emp_shift

Warehouse work shifts.

Column	Type	Nullable	Key
shift_id	`BIGINT`	NOT NULL	PK
name	`VARCHAR(40)`	NOT NULL
start_time	`TIME`	NOT NULL
end_time	`TIME`	NOT NULL

Keys and indexes

Primary key emp_shift_pk (shift_id)

Referenced by

emp_employee_shift (shift_id) emp_shift_emp_employee_shift_fk · ON DELETE RESTRICT

mkt_campaign public.mkt_campaign

Marketing campaigns.

Column	Type	Nullable	Key
campaign_id	`BIGINT`	NOT NULL	PK
name	`VARCHAR(100)`	NOT NULL
start_date	`DATE`	NOT NULL
end_date	`DATE`	yes
budget	`NUMERIC(12,2)`	yes
channels	`TEXT[]`	yes
manager_id	`BIGINT`	yes	FK

Keys and indexes

Primary key mkt_campaign_pk (campaign_id)
CHECK mkt_campaign_dates_ck (end_date IS NULL OR end_date >= start_date)

References

manager_id → emp_employee (employee_id) emp_employee_mkt_campaign_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

Referenced by

mkt_coupon (campaign_id) mkt_campaign_mkt_coupon_fk · ON DELETE SET NULL
mkt_newsletter_send (campaign_id) mkt_campaign_mkt_newsletter_send_fk · ON DELETE CASCADE

mkt_coupon public.mkt_coupon

Discount codes.

Column	Type	Nullable	Default	Key	Comment
coupon_id	`BIGINT`	NOT NULL		PK
code	`VARCHAR(30)`	NOT NULL
campaign_id	`BIGINT`	yes		FK
type	`VARCHAR(12)`	NOT NULL
value	`NUMERIC(12,2)`	NOT NULL	`0`		Percentage or amount depending on the type; ignored for free_shipping.
minimum_purchase	`NUMERIC(12,2)`	yes
max_uses	`INTEGER`	yes
uses_per_customer	`SMALLINT`	NOT NULL	`1`
valid_from	`TIMESTAMP WITH TIME ZONE`	NOT NULL
valid_until	`TIMESTAMP WITH TIME ZONE`	yes

Keys and indexes

Primary key mkt_coupon_pk (coupon_id)
UNIQUE mkt_coupon_code_uk (code)
CHECK mkt_coupon_percentage_ck (type <> 'percentage' OR value <= 100)
CHECK mkt_coupon_type_check (type IN ('percentage', 'fixed_amount', 'free_shipping'))

References

campaign_id → mkt_campaign (campaign_id) mkt_campaign_mkt_coupon_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

Referenced by

mkt_coupon_use (coupon_id) mkt_coupon_mkt_coupon_use_fk · ON DELETE RESTRICT
ord_cart (coupon_id) mkt_coupon_ord_cart_fk · ON DELETE SET NULL
ord_order (coupon_id) mkt_coupon_ord_order_fk · ON DELETE SET NULL

mkt_coupon_use public.mkt_coupon_use

Orders each coupon has been used in.

Column	Type	Nullable	Default	Key
coupon_id	`BIGINT`	NOT NULL		PKFK
order_id	`BIGINT`	NOT NULL		PKFK
customer_id	`BIGINT`	NOT NULL		FK
used_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key mkt_coupon_use_pk (coupon_id, order_id)
Index mkt_coupon_use_customer_idx (customer_id, coupon_id)

References

customer_id → cus_customer (customer_id) cus_customer_mkt_coupon_use_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
coupon_id → mkt_coupon (coupon_id) mkt_coupon_mkt_coupon_use_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_mkt_coupon_use_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

mkt_newsletter_send public.mkt_newsletter_send

Newsletter sends imported from the mailing tool.

Column	Type	Nullable	Key	Comment
newsletter_send_id	`BIGINT`	NOT NULL	PK
campaign_id	`BIGINT`	NOT NULL	FK
customer_id	`INTEGER`	NOT NULL		INTEGER inherited from the mailing tool (intentional validator warning).
email	`EMAIL`	NOT NULL
sent_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL
opened_at	`TIMESTAMP WITH TIME ZONE`	yes
clicked_at	`TIMESTAMP WITH TIME ZONE`	yes

Keys and indexes

Primary key mkt_newsletter_send_pk (newsletter_send_id)

References

campaign_id → mkt_campaign (campaign_id) mkt_campaign_mkt_newsletter_send_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

mkt_review public.mkt_review

Product reviews written by customers.

Column	Type	Nullable	Default	Key	Comment
review_id	`BIGINT`	NOT NULL		PK
product_id	`BIGINT`	NOT NULL		FK
customer_id	`BIGINT`	NOT NULL		FK
order_id	`BIGINT`	yes		FK
rating	`SMALLINT`	NOT NULL
title	`VARCHAR(120)`	yes
body	`TEXT`	yes
verified	`BOOLEAN`	NOT NULL	`false`		The customer bought the product (there is a linked order).
published	`BOOLEAN`	NOT NULL	`false`
created_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key mkt_review_pk (review_id)
UNIQUE mkt_review_unique_uk (product_id, customer_id)
CHECK mkt_review_rating_check (rating BETWEEN 1 AND 5)

References

customer_id → cus_customer (customer_id) cus_customer_mkt_review_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_mkt_review_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
product_id → cat_product (product_id) review_product_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

Referenced by

mkt_review_vote (review_id) mkt_review_mkt_review_vote_fk · ON DELETE CASCADE

mkt_review_vote public.mkt_review_vote

"Helpful" votes on reviews.

Column	Type	Nullable	Key
review_id	`BIGINT`	NOT NULL	PKFK
customer_id	`BIGINT`	NOT NULL	PKFK
helpful	`BOOLEAN`	NOT NULL

Keys and indexes

Primary key mkt_review_vote_pk (review_id, customer_id)

References

customer_id → cus_customer (customer_id) cus_customer_mkt_review_vote_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
review_id → mkt_review (review_id) mkt_review_mkt_review_vote_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

mkt_wishlist public.mkt_wishlist

Customer wishlists.

Column	Type	Nullable	Default	Key
wishlist_id	`BIGINT`	NOT NULL		PK
customer_id	`BIGINT`	NOT NULL		FK
name	`VARCHAR(60)`	NOT NULL	`'Favorites'`
is_public	`BOOLEAN`	NOT NULL	`false`
created_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key mkt_wishlist_pk (wishlist_id)

References

customer_id → cus_customer (customer_id) cus_customer_mkt_wishlist_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

Referenced by

mkt_wishlist_product (wishlist_id) mkt_wishlist_mkt_wishlist_product_fk · ON DELETE CASCADE

mkt_wishlist_product public.mkt_wishlist_product

Products saved in each wishlist.

Column	Type	Nullable	Default	Key
wishlist_id	`BIGINT`	NOT NULL		PKFK
product_id	`BIGINT`	NOT NULL		PKFK
added_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`
price_drop_alert	`BOOLEAN`	NOT NULL	`false`

Keys and indexes

Primary key mkt_wishlist_product_pk (wishlist_id, product_id)

References

product_id → cat_product (product_id) cat_product_mkt_wishlist_product_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
wishlist_id → mkt_wishlist (wishlist_id) mkt_wishlist_mkt_wishlist_product_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

ord_cart public.ord_cart

Open carts, from registered customers or guests.

Column	Type	Nullable	Default	Key	Comment
cart_id	`BIGINT`	NOT NULL		PK
customer_id	`BIGINT`	yes		FK
session_id	`UUID`	NOT NULL			Browser session, for guest carts.
coupon_id	`BIGINT`	yes		FK
created_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`
updated_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key ord_cart_pk (cart_id)
Unique index ord_cart_session_idx (session_id)

References

customer_id → cus_customer (customer_id) cus_customer_ord_cart_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
coupon_id → mkt_coupon (coupon_id) mkt_coupon_ord_cart_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

Referenced by

ord_cart_line (cart_id) ord_cart_ord_cart_line_fk · ON DELETE CASCADE

ord_cart_line public.ord_cart_line

Variants added to each cart.

Column	Type	Nullable	Default	Key
cart_id	`BIGINT`	NOT NULL		PKFK
variant_id	`BIGINT`	NOT NULL		PKFK
quantity	`SMALLINT`	NOT NULL
added_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key ord_cart_line_pk (cart_id, variant_id)
CHECK ord_cart_line_quantity_check (quantity > 0)

References

variant_id → cat_variant (variant_id) cat_variant_ord_cart_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
cart_id → ord_cart (cart_id) ord_cart_ord_cart_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

ord_order public.ord_order

Confirmed orders.

Column	Type	Nullable	Default	Key	Comment
order_id	`BIGINT`	NOT NULL		PK
number	`VARCHAR(20)`	NOT NULL			Number shown to the customer, e.g. NM-2026-000123.
customer_id	`BIGINT`	NOT NULL		FK
shipping_address_id	`BIGINT`	NOT NULL		FK	Address the order is shipped to.
billing_address_id	`BIGINT`	NOT NULL		FK	Address shown on the invoice.
coupon_id	`BIGINT`	yes		FK
status	`ORDER_STATUS`	NOT NULL	`'pending'::order_status`
channel	`VARCHAR(20)`	NOT NULL	`'web'`		web, app, marketplace or phone.
currency_code	`CHAR(3)`	NOT NULL	`'EUR'`	FK
subtotal	`NUMERIC(12,2)`	NOT NULL
discount	`NUMERIC(12,2)`	NOT NULL	`0`
shipping_cost	`NUMERIC(12,2)`	NOT NULL	`0`
taxes	`NUMERIC(12,2)`	NOT NULL
total	`NUMERIC(12,2)`	NOT NULL
notes	`TEXT`	yes
order_date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key ord_order_pk (order_id)
UNIQUE ord_order_number_uk (number)
Index ord_order_customer_idx (customer_id)
Index ord_order_status_idx (status, order_date DESC)
CHECK ord_order_discount_ck (discount <= subtotal)
CHECK ord_order_total_check (total >= 0)

References

shipping_address_id → cus_address (address_id) cus_address_ord_order_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
billing_address_id → cus_address (address_id) cus_address_ord_order_fk2 · ON DELETE RESTRICT · ON UPDATE NO ACTION
customer_id → cus_customer (customer_id) fk_order_customer · ON DELETE RESTRICT · ON UPDATE NO ACTION
coupon_id → mkt_coupon (coupon_id) mkt_coupon_ord_order_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
currency_code → sys_currency (code) sys_currency_ord_order_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

mkt_coupon_use (order_id) ord_order_mkt_coupon_use_fk · ON DELETE CASCADE
mkt_review (order_id) ord_order_mkt_review_fk · ON DELETE SET NULL
ord_order_line (order_id) ord_order_ord_order_line_fk · ON DELETE CASCADE
ord_return (order_id) ord_order_ord_return_fk · ON DELETE RESTRICT
ord_status_history (order_id) ord_order_ord_status_history_fk · ON DELETE CASCADE
pay_invoice (order_id) ord_order_pay_invoice_fk · ON DELETE RESTRICT
pay_payment (order_id) ord_order_pay_payment_fk · ON DELETE RESTRICT
shp_shipment (order_id) ord_order_shp_shipment_fk · ON DELETE CASCADE
wh_movement (order_id) ord_order_wh_movement_fk · ON DELETE SET NULL

ord_order_line public.ord_order_line

Order lines; they keep the price and tax at the time of purchase.

Column	Type	Nullable	Default	Key	Comment
order_id	`BIGINT`	NOT NULL		PKFK
line_no	`SMALLINT`	NOT NULL		PK
variant_id	`BIGINT`	NOT NULL		FK
description	`VARCHAR(200)`	NOT NULL			Product name at the time of purchase.
quantity	`SMALLINT`	NOT NULL
unit_price	`NUMERIC(12,2)`	NOT NULL
discount	`NUMERIC(12,2)`	NOT NULL	`0`
tax_code	`VARCHAR(10)`	NOT NULL		FK
amount	`NUMERIC(12,2)`	NOT NULL

Keys and indexes

Primary key ord_order_line_pk (order_id, line_no)
CHECK ord_order_line_quantity_check (quantity > 0)

References

variant_id → cat_variant (variant_id) cat_variant_ord_order_line_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_ord_order_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
tax_code → pay_tax (code) pay_tax_ord_order_line_fk · ON DELETE RESTRICT · ON UPDATE CASCADE

Referenced by

ord_return_line (order_id, line_no) ord_order_line_ord_return_line_fk · ON DELETE RESTRICT
shp_parcel_line (order_id, line_no) ord_order_line_shp_parcel_line_fk · ON DELETE RESTRICT
wh_reservation (order_id, line_no) ord_order_line_wh_reservation_fk · ON DELETE CASCADE

ord_return public.ord_return

Return requests.

Column	Type	Nullable	Default	Key
return_id	`BIGINT`	NOT NULL		PK
order_id	`BIGINT`	NOT NULL		FK
status	`VARCHAR(15)`	NOT NULL	`'requested'`
requested_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`
reviewed_by	`BIGINT`	yes		FK
shipment_id	`BIGINT`	yes		FK

Keys and indexes

Primary key ord_return_pk (return_id)
CHECK ord_return_status_check (status IN ('requested', 'accepted', 'rejected', 'received', 'refunded'))

References

reviewed_by → emp_employee (employee_id) emp_employee_ord_return_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_ord_return_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
shipment_id → shp_shipment (shipment_id) shp_shipment_ord_return_fk · ON DELETE SET NULL · ON UPDATE NO ACTION

Referenced by

ord_return_line (return_id) ord_return_ord_return_line_fk · ON DELETE CASCADE
pay_refund (return_id) ord_return_pay_refund_fk · ON DELETE SET NULL

ord_return_line public.ord_return_line

Order lines included in a return.

Column	Type	Nullable	Key
return_id	`BIGINT`	NOT NULL	PKFK
order_id	`BIGINT`	NOT NULL	PKFK
line_no	`SMALLINT`	NOT NULL	PKFK
quantity	`SMALLINT`	NOT NULL
reason_id	`BIGINT`	NOT NULL	FK
remarks	`TEXT`	yes

Keys and indexes

Primary key ord_return_line_pk (return_id, order_id, line_no)
CHECK ord_return_line_quantity_check (quantity > 0)

References

order_id, line_no → ord_order_line (order_id, line_no) ord_order_line_ord_return_line_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
return_id → ord_return (return_id) ord_return_ord_return_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
reason_id → ord_return_reason (reason_id) ord_return_reason_ord_return_line_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

ord_return_reason public.ord_return_reason

Catalog of return reasons.

Column	Type	Nullable	Default	Key
reason_id	`BIGINT`	NOT NULL		PK
code	`VARCHAR(20)`	NOT NULL
description	`VARCHAR(150)`	NOT NULL
needs_review	`BOOLEAN`	NOT NULL	`false`

Keys and indexes

Primary key ord_return_reason_pk (reason_id)
UNIQUE ord_return_reason_code_key (code)

Referenced by

ord_return_line (reason_id) ord_return_reason_ord_return_line_fk · ON DELETE RESTRICT

ord_status_history public.ord_status_history

Status changes of each order.

Column	Type	Nullable	Key
order_id	`BIGINT`	NOT NULL	PKFK
changed_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	PK
previous_status	`ORDER_STATUS`	yes
new_status	`ORDER_STATUS`	NOT NULL
employee_id	`BIGINT`	yes	FK
comment	`TEXT`	yes

Keys and indexes

Primary key ord_status_history_pk (order_id, changed_at)

References

employee_id → emp_employee (employee_id) emp_employee_ord_status_history_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_ord_status_history_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

pay_invoice public.pay_invoice

Issued invoices, including corrective ones.

Column	Type	Nullable	Default	Key	Comment
invoice_id	`BIGINT`	NOT NULL		PK
number	`VARCHAR(20)`	NOT NULL	`('F' \|\| nextval('pay_invoice_number_seq'))`
order_id	`BIGINT`	NOT NULL		FK
customer_id	`BIGINT`	NOT NULL		FK
corrected_invoice_id	`BIGINT`	yes		FK	In corrective invoices, the invoice they correct.
issued_at	`DATE`	NOT NULL	`CURRENT_DATE`
taxable_base	`NUMERIC(12,2)`	NOT NULL
tax_amount	`NUMERIC(12,2)`	NOT NULL
total	`NUMERIC(12,2)`	NOT NULL
pdf	`BYTEA`	yes

Keys and indexes

Primary key pay_invoice_pk (invoice_id)
UNIQUE pay_invoice_number_uk (number)
CHECK pay_invoice_total_ck (total = taxable_base + tax_amount)

References

customer_id → cus_customer (customer_id) cus_customer_pay_invoice_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_pay_invoice_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
corrected_invoice_id → pay_invoice (invoice_id) pay_invoice_pay_invoice_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

Referenced by

pay_invoice (corrected_invoice_id) pay_invoice_pay_invoice_fk · ON DELETE RESTRICT
pay_invoice_line (invoice_id) pay_invoice_pay_invoice_line_fk · ON DELETE CASCADE

pay_invoice_line public.pay_invoice_line

Items of each invoice.

Column	Type	Nullable	Key
invoice_id	`BIGINT`	NOT NULL	PKFK
line_no	`SMALLINT`	NOT NULL	PK
item_name	`VARCHAR(200)`	NOT NULL
quantity	`SMALLINT`	NOT NULL
unit_price	`NUMERIC(12,2)`	NOT NULL
tax_code	`VARCHAR(10)`	NOT NULL	FK
amount	`NUMERIC(12,2)`	NOT NULL

Keys and indexes

Primary key pay_invoice_line_pk (invoice_id, line_no)

References

invoice_id → pay_invoice (invoice_id) pay_invoice_pay_invoice_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
tax_code → pay_tax (code) pay_tax_pay_invoice_line_fk · ON DELETE RESTRICT · ON UPDATE CASCADE

pay_payment public.pay_payment

Payment attempts for an order.

Column	Type	Nullable	Default	Key	Comment
payment_id	`BIGINT`	NOT NULL		PK
order_id	`BIGINT`	NOT NULL		FK
method	`PAYMENT_METHOD`	NOT NULL
saved_method_id	`BIGINT`	yes		FK
amount	`NUMERIC(12,2)`	NOT NULL
currency_code	`CHAR(3)`	NOT NULL		FK
status	`VARCHAR(12)`	NOT NULL	`'pending'`
external_transaction	`VARCHAR(100)`	yes
gateway_response	`JSONB`	yes			Full response of the payment gateway, for support.
date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key pay_payment_pk (payment_id)
Index pay_payment_order_idx (order_id)
CHECK pay_payment_amount_check (amount > 0)
CHECK pay_payment_status_check (status IN ('pending', 'authorized', 'captured', 'failed', 'voided'))

References

order_id → ord_order (order_id) ord_order_pay_payment_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
saved_method_id → pay_saved_method (saved_method_id) pay_saved_method_pay_payment_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
currency_code → sys_currency (code) sys_currency_pay_payment_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

pay_refund (payment_id) pay_payment_pay_refund_fk · ON DELETE RESTRICT

pay_refund public.pay_refund

Money returned against a payment.

Column	Type	Nullable	Default	Key
refund_id	`BIGINT`	NOT NULL		PK
payment_id	`BIGINT`	NOT NULL		FK
return_id	`BIGINT`	yes		FK
amount	`NUMERIC(12,2)`	NOT NULL
reason	`TEXT`	yes
date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key pay_refund_pk (refund_id)
CHECK pay_refund_amount_check (amount > 0)

References

return_id → ord_return (return_id) ord_return_pay_refund_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
payment_id → pay_payment (payment_id) pay_payment_pay_refund_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

pay_saved_method public.pay_saved_method

Payment methods tokenized by the gateway; the card is never stored.

Column	Type	Nullable	Default	Key
saved_method_id	`BIGINT`	NOT NULL		PK
customer_id	`BIGINT`	NOT NULL		FK
type	`PAYMENT_METHOD`	NOT NULL
token	`VARCHAR(100)`	NOT NULL
last_digits	`CHAR(4)`	yes
expires_on	`DATE`	yes
is_default	`BOOLEAN`	NOT NULL	`false`

Keys and indexes

Primary key pay_saved_method_pk (saved_method_id)
UNIQUE pay_saved_method_token_uk (token)

References

customer_id → cus_customer (customer_id) cus_customer_pay_saved_method_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

Referenced by

pay_payment (saved_method_id) pay_saved_method_pay_payment_fk · ON DELETE SET NULL

pay_tax public.pay_tax

Applicable VAT rates (standard, reduced, super-reduced, exempt).

Column	Type	Nullable	Key
code	`VARCHAR(10)`	NOT NULL	PK
percentage	`NUMERIC(5,2)`	NOT NULL
description	`VARCHAR(100)`	yes

Keys and indexes

Primary key pay_tax_pkey (code)
CHECK pay_tax_percentage_check (percentage >= 0)

Referenced by

cat_product (tax_code) pay_tax_cat_product_fk · ON DELETE RESTRICT
ord_order_line (tax_code) pay_tax_ord_order_line_fk · ON DELETE RESTRICT
pay_invoice_line (tax_code) pay_tax_pay_invoice_line_fk · ON DELETE RESTRICT

shp_carrier public.shp_carrier

Courier companies.

Column	Type	Nullable	Default	Key
carrier_id	`BIGINT`	NOT NULL		PK
name	`VARCHAR(80)`	NOT NULL
tracking_url	`VARCHAR(300)`	yes
api_config	`JSONB`	yes
active	`BOOLEAN`	NOT NULL	`true`

Keys and indexes

Primary key shp_carrier_pk (carrier_id)
UNIQUE shp_carrier_name_uk (name)

Referenced by

shp_pickup_point (carrier_id) shp_carrier_shp_pickup_point_fk · ON DELETE CASCADE
shp_rate (carrier_id) shp_carrier_shp_rate_fk · ON DELETE CASCADE
shp_shipment (carrier_id) shp_carrier_shp_shipment_fk · ON DELETE RESTRICT

shp_parcel public.shp_parcel

Boxes that make up a shipment.

Column	Type	Nullable	Key
parcel_id	`BIGINT`	NOT NULL	PK
shipment_id	`BIGINT`	NOT NULL	FK
number	`SMALLINT`	NOT NULL
weight_grams	`INTEGER`	yes
dimensions_cm	`VARCHAR(20)`	yes
label	`BYTEA`	yes

Keys and indexes

Primary key shp_parcel_pk (parcel_id)
UNIQUE shp_parcel_number_uk (shipment_id, number)

References

shipment_id → shp_shipment (shipment_id) shp_shipment_shp_parcel_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

Referenced by

shp_parcel_line (parcel_id) shp_parcel_shp_parcel_line_fk · ON DELETE CASCADE

shp_parcel_line public.shp_parcel_line

Which order lines go in each parcel.

Column	Type	Nullable	Key
parcel_id	`BIGINT`	NOT NULL	PKFK
order_id	`BIGINT`	NOT NULL	PKFK
line_no	`SMALLINT`	NOT NULL	PKFK
quantity	`SMALLINT`	NOT NULL

Keys and indexes

Primary key shp_parcel_line_pk (parcel_id, order_id, line_no)
CHECK shp_parcel_line_quantity_check (quantity > 0)

References

order_id, line_no → ord_order_line (order_id, line_no) ord_order_line_shp_parcel_line_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
parcel_id → shp_parcel (parcel_id) shp_parcel_shp_parcel_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

shp_pickup_point public.shp_pickup_point

Pickup points (stores, lockers) of each carrier.

Column	Type	Nullable	Key
pickup_point_id	`BIGINT`	NOT NULL	PK
carrier_id	`BIGINT`	NOT NULL	FK
code	`VARCHAR(20)`	NOT NULL
name	`VARCHAR(120)`	NOT NULL
address	`VARCHAR(200)`	NOT NULL
postal_code	`VARCHAR(10)`	NOT NULL
country_code	`CHAR(2)`	NOT NULL	FK
latitude	`NUMERIC(9,6)`	yes
longitude	`NUMERIC(9,6)`	yes

Keys and indexes

Primary key shp_pickup_point_pk (pickup_point_id)
UNIQUE shp_pickup_point_code_uk (carrier_id, code)

References

carrier_id → shp_carrier (carrier_id) shp_carrier_shp_pickup_point_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
country_code → sys_country (code) sys_country_shp_pickup_point_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

shp_shipment (pickup_point_id) shp_pickup_point_shp_shipment_fk · ON DELETE SET NULL

shp_rate public.shp_rate

Rates by carrier, country and weight band.

Column	Type	Nullable	Key
rate_id	`BIGINT`	NOT NULL	PK
carrier_id	`BIGINT`	NOT NULL	FK
country_code	`CHAR(2)`	NOT NULL	FK
max_weight_grams	`INTEGER`	NOT NULL
price	`NUMERIC(12,2)`	NOT NULL
term_days	`SMALLINT`	yes

Keys and indexes

Primary key shp_rate_pk (rate_id)
UNIQUE shp_rate_band_uk (carrier_id, country_code, max_weight_grams)

References

carrier_id → shp_carrier (carrier_id) shp_carrier_shp_rate_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
country_code → sys_country (code) sys_country_shp_rate_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

shp_shipment public.shp_shipment

Shipments of an order, outbound or return.

Column	Type	Nullable	Default	Key	Comment
shipment_id	`BIGINT`	NOT NULL		PK
order_id	`BIGINT`	NOT NULL		FK
carrier_id	`BIGINT`	NOT NULL		FK
warehouse_id	`BIGINT`	NOT NULL		FK
pickup_point_id	`BIGINT`	yes		FK
direction	`VARCHAR(10)`	NOT NULL	`'outbound'`		outbound = to the customer; return = back to the warehouse.
tracking_code	`VARCHAR(40)`	yes
cost	`NUMERIC(12,2)`	yes
sent_at	`TIMESTAMP WITH TIME ZONE`	yes
delivered_at	`TIMESTAMP WITH TIME ZONE`	yes

Keys and indexes

Primary key shp_shipment_pk (shipment_id)
Index shp_shipment_order_idx (order_id)
Unique index shp_shipment_tracking_idx (carrier_id, tracking_code)
CHECK shp_shipment_dates_ck (delivered_at IS NULL OR delivered_at >= sent_at)
CHECK shp_shipment_direction_check (direction IN ('outbound', 'return'))

References

order_id → ord_order (order_id) ord_order_shp_shipment_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
carrier_id → shp_carrier (carrier_id) shp_carrier_shp_shipment_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
pickup_point_id → shp_pickup_point (pickup_point_id) shp_pickup_point_shp_shipment_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
warehouse_id → wh_warehouse (warehouse_id) wh_warehouse_shp_shipment_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

Referenced by

ord_return (shipment_id) shp_shipment_ord_return_fk · ON DELETE SET NULL
shp_parcel (shipment_id) shp_shipment_shp_parcel_fk · ON DELETE CASCADE
shp_tracking_event (shipment_id) shp_shipment_shp_tracking_event_fk · ON DELETE CASCADE

shp_tracking_event public.shp_tracking_event

Tracking events received from the carrier.

Column	Type	Nullable	Key
shipment_id	`BIGINT`	NOT NULL	PKFK
date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	PK
status	`VARCHAR(30)`	NOT NULL
location	`VARCHAR(120)`	yes
detail	`JSONB`	yes

Keys and indexes

Primary key shp_tracking_event_pk (shipment_id, date)

References

shipment_id → shp_shipment (shipment_id) shp_shipment_shp_tracking_event_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

sys_audit_log public.sys_audit_log

Change log written by triggers; it has no FKs so it never blocks deletes.

Column	Type	Nullable	Default	Key	Comment
audit_id	`BIGINT`	NOT NULL	`nextval('sys_audit_log_seq')`	PK
table_name	`VARCHAR(63)`	NOT NULL
record_id	`VARCHAR(100)`	NOT NULL			Primary key of the affected record, as text.
operation	`CHAR(1)`	NOT NULL			I = insert, U = update, D = delete.
data_before	`JSONB`	yes
data_after	`JSONB`	yes
username	`VARCHAR(100)`	NOT NULL
ip	`INET`	yes
date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key sys_audit_log_pk (audit_id)
Index sys_audit_log_date_idx (date DESC)
Index sys_audit_log_record_idx (table_name, record_id)
CHECK sys_audit_log_operation_check (operation IN ('I', 'U', 'D'))

sys_country public.sys_country

ISO 3166-1 alpha-2 countries the shop sells to or buys from.

Column	Type	Nullable	Default	Key
code	`CHAR(2)`	NOT NULL		PK
name	`VARCHAR(80)`	NOT NULL
phone_prefix	`VARCHAR(5)`	yes
in_eu	`BOOLEAN`	NOT NULL	`false`

Keys and indexes

Primary key sys_country_pk (code)

Referenced by

cat_supplier (country_code) sys_country_cat_supplier_fk · ON DELETE NO ACTION
cus_address (country_code) sys_country_cus_address_fk · ON DELETE NO ACTION
shp_pickup_point (country_code) sys_country_shp_pickup_point_fk · ON DELETE NO ACTION
shp_rate (country_code) sys_country_shp_rate_fk · ON DELETE NO ACTION
wh_warehouse (country_code) sys_country_wh_warehouse_fk · ON DELETE NO ACTION

sys_currency public.sys_currency

ISO 4217 currencies accepted in prices and payments.

Column	Type	Nullable	Default	Key
code	`CHAR(3)`	NOT NULL		PK
name	`VARCHAR(40)`	NOT NULL
symbol	`VARCHAR(5)`	NOT NULL
decimals	`SMALLINT`	NOT NULL	`2`

Keys and indexes

Primary key sys_currency_pk (code)
CHECK sys_currency_decimals_ck (decimals BETWEEN 0 AND 4)

Referenced by

cat_variant (currency_code) sys_currency_cat_variant_fk · ON DELETE NO ACTION
cus_customer (currency_code) sys_currency_cus_customer_fk · ON DELETE NO ACTION
ord_order (currency_code) sys_currency_ord_order_fk · ON DELETE NO ACTION
pay_payment (currency_code) sys_currency_pay_payment_fk · ON DELETE NO ACTION

sys_setting public.sys_setting

Shop settings as JSON key/value pairs.

Column	Type	Nullable	Default	Key
key	`VARCHAR(100)`	NOT NULL		PK
value	`JSONB`	NOT NULL
description	`TEXT`	yes
updated_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key sys_setting_pk (key)

wh_location public.wh_location

Slots in a warehouse (aisle, shelf, level).

Column	Type	Nullable	Default	Key
location_id	`BIGINT`	NOT NULL		PK
warehouse_id	`BIGINT`	NOT NULL		FK
aisle	`VARCHAR(5)`	NOT NULL
shelf	`VARCHAR(5)`	NOT NULL
level	`SMALLINT`	NOT NULL
type	`VARCHAR(15)`	NOT NULL	`'picking'`

Keys and indexes

Primary key wh_location_pk (location_id)
UNIQUE wh_location_position_uk (warehouse_id, aisle, shelf, level)
CHECK wh_location_type_check (type IN ('picking', 'reserve', 'dock'))

References

warehouse_id → wh_warehouse (warehouse_id) wh_warehouse_wh_location_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

Referenced by

wh_movement (source_location_id) wh_location_wh_movement_fk · ON DELETE RESTRICT
wh_movement (target_location_id) wh_location_wh_movement_fk2 · ON DELETE RESTRICT
wh_stock (location_id) wh_location_wh_stock_fk · ON DELETE RESTRICT

wh_movement public.wh_movement

Stock receipts, issues, transfers and adjustments.

Column	Type	Nullable	Default	Key	Comment
movement_id	`BIGINT`	NOT NULL		PK
variant_id	`BIGINT`	NOT NULL		FK
source_location_id	`BIGINT`	yes		FK	Null for goods receipts.
target_location_id	`BIGINT`	yes		FK	Null for goods issues.
quantity	`INTEGER`	NOT NULL
type	`VARCHAR(15)`	NOT NULL
employee_id	`BIGINT`	yes		FK
order_id	`BIGINT`	yes		FK
date	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key wh_movement_pk (movement_id)
Index wh_movement_variant_idx (variant_id, date DESC)
CHECK wh_movement_location_ck (source_location_id IS NOT NULL OR target_location_id IS NOT NULL)
CHECK wh_movement_quantity_check (quantity > 0)
CHECK wh_movement_type_check (type IN ('inbound', 'outbound', 'transfer', 'adjustment'))

References

variant_id → cat_variant (variant_id) cat_variant_wh_movement_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
employee_id → emp_employee (employee_id) emp_employee_wh_movement_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
order_id → ord_order (order_id) ord_order_wh_movement_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
source_location_id → wh_location (location_id) wh_location_wh_movement_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
target_location_id → wh_location (location_id) wh_location_wh_movement_fk2 · ON DELETE RESTRICT · ON UPDATE NO ACTION

wh_reservation public.wh_reservation

Stock set aside for an order line until it is picked.

Column	Type	Nullable	Key
reservation_id	`BIGINT`	NOT NULL	PK
order_id	`BIGINT`	NOT NULL	FK
line_no	`SMALLINT`	NOT NULL	FK
variant_id	`BIGINT`	NOT NULL	FK
location_id	`BIGINT`	NOT NULL	FK
quantity	`INTEGER`	NOT NULL
expires_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL

Keys and indexes

Primary key wh_reservation_pk (reservation_id)
CHECK wh_reservation_quantity_check (quantity > 0)

References

order_id, line_no → ord_order_line (order_id, line_no) ord_order_line_wh_reservation_fk · ON DELETE CASCADE · ON UPDATE NO ACTION
variant_id, location_id → wh_stock (variant_id, location_id) wh_stock_wh_reservation_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

wh_stock public.wh_stock

Units of each variant in each location.

Column	Type	Nullable	Default	Key	Comment
variant_id	`BIGINT`	NOT NULL		PKFK
location_id	`BIGINT`	NOT NULL		PKFK
quantity	`INTEGER`	NOT NULL	`0`
reserved	`INTEGER`	NOT NULL	`0`		Units set aside for orders not yet picked.
minimum	`INTEGER`	yes			Below this quantity a restock is proposed.
updated_at	`TIMESTAMP WITH TIME ZONE`	NOT NULL	`now()`

Keys and indexes

Primary key wh_stock_pk (variant_id, location_id)
Index wh_stock_location_idx (location_id)
CHECK wh_stock_quantity_check (quantity >= 0)
CHECK wh_stock_reserved_ck (reserved BETWEEN 0 AND quantity)

References

variant_id → cat_variant (variant_id) cat_variant_wh_stock_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
location_id → wh_location (location_id) wh_location_wh_stock_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

Referenced by

wh_reservation (variant_id, location_id) wh_stock_wh_reservation_fk · ON DELETE RESTRICT
wh_stocktake_line (variant_id, location_id) wh_stock_wh_stocktake_line_fk · ON DELETE RESTRICT

wh_stocktake public.wh_stocktake

Physical stock counts of a warehouse.

Column	Type	Nullable	Default	Key
stocktake_id	`BIGINT`	NOT NULL		PK
warehouse_id	`BIGINT`	NOT NULL		FK
date	`DATE`	NOT NULL
status	`VARCHAR(12)`	NOT NULL	`'open'`
manager_id	`BIGINT`	yes		FK

Keys and indexes

Primary key wh_stocktake_pk (stocktake_id)
CHECK wh_stocktake_status_check (status IN ('open', 'closed', 'voided'))

References

manager_id → emp_employee (employee_id) emp_employee_wh_stocktake_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
warehouse_id → wh_warehouse (warehouse_id) wh_warehouse_wh_stocktake_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION

Referenced by

wh_stocktake_line (stocktake_id) wh_stocktake_wh_stocktake_line_fk · ON DELETE CASCADE

wh_stocktake_line public.wh_stocktake_line

Count result per variant and location.

Column	Type	Nullable	Key
stocktake_id	`BIGINT`	NOT NULL	PKFK
variant_id	`BIGINT`	NOT NULL	PKFK
location_id	`BIGINT`	NOT NULL	PKFK
system_quantity	`INTEGER`	NOT NULL
counted_quantity	`INTEGER`	yes

Keys and indexes

Primary key wh_stocktake_line_pk (stocktake_id, variant_id, location_id)

References

variant_id, location_id → wh_stock (variant_id, location_id) wh_stock_wh_stocktake_line_fk · ON DELETE RESTRICT · ON UPDATE NO ACTION
stocktake_id → wh_stocktake (stocktake_id) wh_stocktake_wh_stocktake_line_fk · ON DELETE CASCADE · ON UPDATE NO ACTION

wh_warehouse public.wh_warehouse

Own warehouses orders are shipped from.

Column	Type	Nullable	Default	Key
warehouse_id	`BIGINT`	NOT NULL		PK
code	`VARCHAR(10)`	NOT NULL
name	`VARCHAR(80)`	NOT NULL
address	`VARCHAR(200)`	NOT NULL
country_code	`CHAR(2)`	NOT NULL		FK
manager_id	`BIGINT`	yes		FK
active	`BOOLEAN`	NOT NULL	`true`

Keys and indexes

Primary key wh_warehouse_pk (warehouse_id)
UNIQUE wh_warehouse_code_uk (code)

References

manager_id → emp_employee (employee_id) emp_employee_wh_warehouse_fk · ON DELETE SET NULL · ON UPDATE NO ACTION
country_code → sys_country (code) sys_country_wh_warehouse_fk · ON DELETE NO ACTION · ON UPDATE CASCADE

Referenced by

emp_employee (warehouse_id) wh_warehouse_emp_employee_fk · ON DELETE SET NULL
shp_shipment (warehouse_id) wh_warehouse_shp_shipment_fk · ON DELETE RESTRICT
wh_location (warehouse_id) wh_warehouse_wh_location_fk · ON DELETE CASCADE
wh_stocktake (warehouse_id) wh_warehouse_wh_stocktake_fk · ON DELETE RESTRICT