Tech Spec Template

Background

This tech spec is for the "Discount feature", it allows users to configure discounts/pricing tiers on the menu products/carts so they can entice their customers to buy products.

Proposed Solution

1. Data model changes

Here's an outline for the data model changes I would make. This is a rough sketch, so please don't use this in production.

-- Define ENUM type for discount types
CREATE TYPE discount_type AS ENUM ('percentage', 'fixed');

-- Define ENUM type for condition types
CREATE TYPE condition_type AS ENUM ( 'min_quantity', 'product_category', 'product', 'customer_category', 'customer', 'previously_ordered' );

-- Define ENUM type for condition operators
CREATE TYPE condition_operator AS ENUM ('=', '!=', '>', '<', '>=', '<=');

CREATE TABLE public.discounts (
  id SERIAL PRIMARY KEY,
  company_id INTEGER REFERENCES public.companies(id) ON DELETE RESTRICT,
  name VARCHAR(255) NOT NULL,
  discount_type discount_type NOT NULL, -- ENUM type 'percentage' or 'fixed'
  amount NUMERIC(10, 2) NOT NULL,
  valid_from DATE,
  valid_until DATE,
  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.discount_conditions (
  id SERIAL PRIMARY KEY,
  discount_id INTEGER REFERENCES public.discounts(id) ON DELETE CASCADE,
  condition_type condition_type NOT NULL, -- ENUM type 'product_type', 'purchase_history', 'customer_tag'
  condition_operator condition_operator NOT NULL, -- ENUM type for operators
  condition_value VARCHAR(255) NOT NULL
);

CREATE INDEX idx_discounts_active ON public.discounts (active);
CREATE INDEX idx_discount_conditions_discount_id ON public.discount_conditions (discount_id);

Data Migrations

No initial data migrations are necessary as this adds new tables/fields. It would be wise to make a backup before running database (schema) migrations though. Just to be clear, data migrations and database migrations are not the same thing.

Concerns/Questions

Should we have validations/constraints to ensure valid_from is before valid_until?
How do we handle cases where a discount's filter conditions overlap?

Justifications

The structure allows flexibility for adding more filter types in the future without modifying the core table structure.
Indexes are created to optimize query performance for active discounts and filter lookups.

2. Changes on backend endpoints, both new and existing.

New Endpoints

Create a new discount:

POST /api/discounts

Validations:
- name should not be empty.
- discount_type should be either 'percentage' or 'fixed'.
- amount should be a positive number.
- valid_from should be before valid_until.
- company_id must reference an existing company.
Update an existing discount:

PUT /api/discounts/:id

Validations:
- Fields the same as for creating a new discount.
- Ensure the discount exists.
- Ensure the discount belongs to the user's company.
List all discounts:

GET /api/discounts

Validations:
- Ensure the user belongs to a company that has access to the discounts.
Get details of a specific discount:

GET /api/discounts/:id

Validations:
- Ensure the discount exists.
- Ensure the discount belongs to the user's company.
Delete a discount:

DELETE /api/discounts/:id

Validations:
- Ensure the discount exists.
- Ensure the discount belongs to the user's company.

3. Pseudocode for determining what the “best” pricing tier is for a given order item

There is an option to put this pseudocode in one of two places:

You could get all active discounts and do this logic in the client (javascript), which is more scalable.
You could when getting item, also apply this logic to return the best pricing tier for a given item.

NOTE: This is pseudocode and is not a real language, the logic is written on top of my head and might not be 100%.

FUNCTION get_best_discount(order_item, discounts)
    applicable_discounts = []

    FOR discount IN discounts:
        IF NOT discount.active THEN CONTINUE
        IF discount.valid_from IS NOT NULL AND discount.valid_from > TODAY THEN CONTINUE
        IF discount.valid_until IS NOT NULL AND discount.valid_until < TODAY THEN CONTINUE
        IF NOT is_applicable(discount, order_item) THEN CONTINUE

        ADD discount TO applicable_discounts

    IF applicable_discounts IS EMPTY THEN RETURN 0

    best_discount = MAX(applicable_discounts, BY calculate_discount_value(discount, order_item))
    RETURN best_discount
END FUNCTION

FUNCTION is_applicable(discount, order_item)
    FOR condition IN discount.conditions:
        IF NOT check_condition(condition, order_item) THEN RETURN FALSE
    RETURN TRUE
END FUNCTION

FUNCTION check_condition(condition, order_item)
    SWITCH condition.condition_type:
        CASE 'min_quantity':
            IF condition.condition_operator == '>=' AND order_item.quantity < condition.condition_value THEN RETURN FALSE
        CASE 'product_category':
            product_category = get_product_category(order_item.product_id) -- Assume a function to get product category
            IF condition.condition_operator == '=' AND product_category != condition.condition_value THEN RETURN FALSE
        CASE 'product':
            IF condition.condition_operator == '=' AND order_item.product_id != condition.condition_value THEN RETURN FALSE
        CASE 'customer_category':
            customer_category = get_customer_category(order_item.customer_id) -- Assume a function to get customer category
            IF condition.condition_operator == '=' AND customer_category != condition.condition_value THEN RETURN FALSE
        CASE 'customer':
            IF condition.condition_operator == '=' AND order_item.customer_id != condition.condition_value THEN RETURN FALSE
        CASE 'previously_ordered':
            previously_ordered = has_previously_ordered(order_item.customer_id, order_item.product_id) -- Assume a function to check previous orders
            IF condition.condition_operator == '=' AND NOT previously_ordered THEN RETURN FALSE
    RETURN TRUE
END FUNCTION

FUNCTION calculate_discount_value(discount, order_item)
    IF discount.discount_type == 'percentage' THEN
        RETURN order_item.price * (discount.amount / 100)
    ELSE IF discount.discount_type == 'fixed' THEN
        RETURN discount.amount
    RETURN 0
END FUNCTION

4. Pseudocode around the “Claim Additional Discount” button in the checkout page.

### Some functions come from the pseudocode above

FUNCTION claim_additional_discount(order, available_discounts)
    FOR item IN order.items:
        best_discount = get_best_discount(item, available_discounts)
        IF best_discount IS NOT NULL THEN
            apply_discount(item, best_discount)
            show_discount_applied_message(best_discount)
END FUNCTION

FUNCTION apply_discount(order_item, discount)
    discount_value = calculate_discount_value(discount, order_item)
    ADD discount TO order_item.applied_discounts
    order_item.price -= discount_value
END FUNCTION

FUNCTION show_discount_applied_message(discount)
    message = "Discount applied: " + discount.name + " - " + discount.amount + " " + discount.discount_type
    display_message(message)
END FUNCTION

FUNCTION display_message(message)
    PRINT message
END FUNCTION

5. Risks & constraints

(Future) Risks:
- Complexity in managing multiple overlapping discount conditions.
- Potential for performance issues with large data sets.
- Edge cases where discounts may not apply as expected due to complex filter logic.
(Future) Constraints:
- No version history for changes to discounts, potentially leading to audit difficulties. Example: I create discount 1, someone buys something, I now edit discount 1. What is the report going to say? It will link back to discount 1 (the edited one) at this moment and therefore generate a wrong report.

6. Future improvements

Implement discount stacking with priority rules.
Add version history and audit logs for discount changes.
Add reporting capabilities for detailed breakdowns of discount usage and discount effectiveness (do people actually buy more things with certain discounts?).
Optimize performance by caching frequently accessed discount data.

7. Other

Ensure comprehensive documentation is provided for both users and developers.
I would personally run this on a test environment first and let QA's test all features.

TODOs/Open Questions

In a real situation, there would need to be more detail about on what screens/pages need to be edited. Also i'm assuming only the happy path in this spec. I would take in account the unhappy path as well.

schinsue/Tech_spec.md