Skip to content

Promotions & Offers

promotions

Table: public.promotions

Column Type Default Description
id uuid PK UUID
title text NOT NULL Campaign name
description text nullable Details
banner_id uuid FK → banners(id) Associated banner
status text 'draft' draft, active, ended, cancelled
start_date timestamptz NOT NULL Campaign start
end_date timestamptz NOT NULL Campaign end
created_by uuid FK → profiles(id) Admin creator
created_at timestamptz now()
updated_at timestamptz now()

offer_products

Table: public.offer_products

Column Type Default Description
id uuid PK UUID
product_id uuid FK → products(id) Offered product
promotion_id uuid FK → promotions(id) Campaign
pharmacy_id uuid FK → sellers(id) Offering seller
discount_type text 'percentage' percentage, fixed
discount_value numeric NOT NULL Discount amount
original_price numeric NOT NULL Price at offer creation
offer_price numeric NOT NULL Computed discounted price
max_quantity int nullable Per-customer limit
is_active boolean true Toggle
created_at timestamptz now()
updated_at timestamptz now()

Indexes

  • idx_offer_products_product_id on offer_products(product_id)
  • idx_offer_products_promotion_id on offer_products(promotion_id)
  • idx_offer_products_active on offer_products(is_active)
  • idx_promotions_status on promotions(status)