Database Tables

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/aluxey/E-Commerce/llms.txt

Use this file to discover all available pages before exploring further.

​

categories

​

Constraints

• Unique: (name, parent_id) - Category names must be unique within the same parent
• Foreign Key: parent_id references categories(id) with ON DELETE CASCADE

​

Indexes

create unique index ux_categories_name_parent on categories (name, parent_id);
create index idx_categories_parent on categories(parent_id);

​

Example

-- Create root category
INSERT INTO categories (name, parent_id) VALUES ('Clothing', NULL);

-- Create subcategory
INSERT INTO categories (name, parent_id) 
VALUES ('Sweaters', (SELECT id FROM categories WHERE name = 'Clothing'));

​

colors

​

Constraints

• Check: hex_code must match pattern ^#([0-9A-Fa-f]{6})$
• Unique: Both code and hex_code must be globally unique

​

Example

INSERT INTO colors (name, code, hex_code)
VALUES ('Navy Blue', 'blue_navy', '#001f3f');

​

users

​

Constraints

• Check: role IN ('client', 'admin')
• Foreign Key: id references auth.users(id) with ON DELETE CASCADE

​

Triggers

• trg_users_updated_at - Automatically updates updated_at on row modification

​

items

​

Constraints

• Check: status IN ('draft', 'active', 'archived')
• Check: pattern_type IS NULL OR pattern_type IN ('rechtsmuster', 'gaensefuesschen')
• Foreign Key: category_id references categories(id) with ON DELETE SET NULL

​

Indexes

create index idx_items_category on items(category_id);
create index idx_items_name on items using gin (to_tsvector('simple', coalesce(name,'')));

​

Triggers

• trg_items_updated_at - Automatically updates updated_at
• ctr_items_require_color - Ensures at least one color is assigned (deferred)

​

item_colors

​

Constraints

• Primary Key: Composite (item_id, color_id)
• Foreign Key: item_id references items(id) with ON DELETE CASCADE
• Foreign Key: color_id references colors(id) with ON DELETE RESTRICT

​

Indexes

create index idx_item_colors_color on item_colors(color_id);

​

Triggers

• ctr_item_colors_require_color - Prevents removing the last color from an item

​

Example

-- Assign multiple colors to an item
INSERT INTO item_colors (item_id, color_id)
VALUES 
  (1, 5),  -- Navy Blue
  (1, 7),  -- Charcoal Grey
  (1, 12); -- Burgundy

​

item_images

​

Constraints

• Foreign Key: item_id references items(id) with ON DELETE CASCADE

​

Indexes

create index idx_item_images_item on item_images(item_id);

​

item_variants

​

Constraints

• Unique: (item_id, coalesce(size, '')) - Prevents duplicate size variants per item
• Foreign Key: item_id references items(id) with ON DELETE CASCADE

​

Indexes

create index idx_variants_item on item_variants(item_id);
create unique index ux_item_variants_combo on item_variants (item_id, coalesce(size,''));
create index idx_item_variants_item_size on item_variants (item_id, size);

​

Example

INSERT INTO item_variants (item_id, sku, size, stock, price)
VALUES 
  (1, 'SWE-NAV-S', 'S', 10, 89.99),
  (1, 'SWE-NAV-M', 'M', 15, 89.99),
  (1, 'SWE-NAV-L', 'L', 8, 94.99);

​

item_ratings

​

Constraints

• Check: rating BETWEEN 1 AND 5
• Unique: (item_id, user_id) - One review per user per item
• Foreign Key: item_id references items(id) with ON DELETE CASCADE
• Foreign Key: user_id references users(id) with ON DELETE CASCADE

​

Indexes

create index idx_ratings_item on item_ratings(item_id);

​

Triggers

• trg_ratings_updated_at - Automatically updates updated_at

​

orders

​

Constraints

• Check: status IN ('pending', 'paid', 'failed', 'canceled', 'shipped', 'refunded')
• Foreign Key: user_id references users(id) with ON DELETE CASCADE

​

Indexes

create index idx_orders_user on orders(user_id);

​

Triggers

• trg_orders_updated_at - Automatically updates updated_at

​

Shipping Address Format

{
  "name": "Jane Doe",
  "line1": "123 Main Street",
  "line2": "Apt 4B",
  "city": "Paris",
  "postal_code": "75001",
  "country": "FR"
}

​

order_items

​

Constraints

• Foreign Key: order_id references orders(id) with ON DELETE CASCADE
• Foreign Key: item_id references items(id) with ON DELETE RESTRICT
• Foreign Key: variant_id references item_variants(id) with ON DELETE RESTRICT

​

Indexes

create index idx_order_items_order on order_items(order_id);
create index idx_order_items_item on order_items(item_id);

​

Customization Format

{
  "color": "Navy Blue",
  "embroidery": {
    "text": "JD",
    "position": "left chest"
  }
}

​

payments

​

Constraints

• Unique: (provider, provider_id) - Prevents duplicate payment records
• Foreign Key: order_id references orders(id) with ON DELETE CASCADE

​

Indexes

create index idx_payments_order on payments(order_id);

​

Triggers

• trg_payments_updated_at - Automatically updates updated_at

​

stripe_events

​

Usage

-- Check if event already processed
INSERT INTO stripe_events (event_id) 
VALUES ('evt_1234567890')
ON CONFLICT (event_id) DO NOTHING
RETURNING event_id;

-- If no rows returned, event was already processed

​

customer_photos

​

Indexes

create index idx_customer_photos_visible 
  on customer_photos(is_visible, position asc)
  where is_visible = true;

​

Example Query

-- Get visible photos in display order
SELECT image_url, position
FROM customer_photos
WHERE is_visible = true
ORDER BY position ASC;