How to Oracle SaaS pricing in PostgreSQL

Why How to Oracle SaaS pricing in PostgreSQL is important

-- Customer adds 75 seats of "Analytics Cloud" on a monthly term
INSERT INTO Subscriptions (customer_id, product_id, seats, term, discount_pct)
VALUES (1, 3, 75, 'monthly', 10);

-- Calculate first bill
SELECT calculate_subscription_price(id) AS first_month_charge
FROM   Subscriptions
WHERE  customer_id = 1
ORDER  BY id DESC
LIMIT 1;

-- Core tables
CREATE TABLE Products (
    id            SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    base_price    NUMERIC(12,2) NOT NULL,
    setup_fee     NUMERIC(12,2) DEFAULT 0,
    currency      TEXT DEFAULT 'USD'
);

CREATE TABLE PriceTiers (
    id          SERIAL PRIMARY KEY,
    product_id  INT REFERENCES Products(id),
    min_qty     INT NOT NULL,
    max_qty     INT NOT NULL,
    unit_price  NUMERIC(12,2) NOT NULL,
    valid_from  DATE DEFAULT CURRENT_DATE,
    valid_to    DATE DEFAULT '9999-12-31'
);

CREATE TABLE Customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE Subscriptions (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES Customers(id),
    product_id  INT REFERENCES Products(id),
    seats       INT NOT NULL,
    term        TEXT CHECK (term IN ('monthly','annual')),
    discount_pct NUMERIC(5,2) DEFAULT 0,
    started_at  DATE DEFAULT CURRENT_DATE
);

-- Function to calculate price
CREATE OR REPLACE FUNCTION calculate_subscription_price(p_sub_id INT)
RETURNS NUMERIC(12,2) LANGUAGE plpgsql AS $$
DECLARE
    v_base NUMERIC;
    v_setup NUMERIC;
    v_disc NUMERIC;
BEGIN
    WITH base AS (
        SELECT s.seats, s.discount_pct,
               pt.unit_price, p.setup_fee
        FROM Subscriptions s
        JOIN PriceTiers pt ON pt.product_id = s.product_id
                           AND s.seats BETWEEN pt.min_qty AND pt.max_qty
                           AND CURRENT_DATE BETWEEN pt.valid_from AND pt.valid_to
        JOIN Products p ON p.id = s.product_id
        WHERE s.id = p_sub_id
        LIMIT 1
    )
    SELECT seats * unit_price, setup_fee, discount_pct INTO v_base, v_setup, v_disc FROM base;

    RETURN ROUND((v_base + v_setup) * (1 - v_disc / 100), 2);
END;$$;