Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE unique_customer_account (
  customer_id   int  -- replace with actual data types
, customer_type int
, model_id      int
, order_count   int NOT NULL DEFAULT 0
, CONSTRAINT unique_customer_account_pkey PRIMARY KEY (customer_id, customer_type, model_id)
);

CREATE TABLE orders (
  order_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, order_info    text NOT NULL   
, customer_id   int NOT NULL
, customer_type int NOT NULL
, model_id      int NOT NULL
, sequence      int NOT NULL DEFAULT 0
, CONSTRAINT order_count_uni  UNIQUE(customer_id, customer_type, model_id, sequence)
);
CREATE OR REPLACE FUNCTION public.trigger_set_order_customer_number()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- this locks the row and makes concurrent transactions targeting
   -- the same unique_customer_account wait till this one is done
   INSERT INTO unique_customer_account AS u
          (    customer_type,     customer_id,     model_id, order_count)
   VALUES (NEW.customer_type, NEW.customer_id, NEW.model_id, 1)
   ON     CONFLICT (customer_type, customer_id, model_id) DO UPDATE  
   SET    order_count = u.order_count + 1  -- no coalesce
   RETURNING u.order_count
   INTO   NEW.sequence;

   RETURN NEW;
END
$func$;

CREATE TRIGGER orders_insbef
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION public.trigger_set_order_customer_number();
INSERT INTO orders (customer_id, customer_type, model_id, order_info) VALUES
  (1,1,1, 'order 1.1')
, (1,1,1, 'order 1.2')
, (1,1,2, 'order 2.1')
, (1,1,1, 'order 1.3')
, (1,1,2, 'order 2.2')
RETURNING *;


order_id
order_info
customer_id
customer_type
model_id
sequence


1
order 1.1
1
1
1
1

2
order 1.2
1
1
1
2

3
order 2.1
1
1
2
1

4
order 1.3
1
1
1
3

5
order 2.2
1
1
2
2


TABLE unique_customer_account;


customer_id
customer_type
model_id
order_count


1
1
1
3

1
1
2
2

order_id	order_info	customer_id	customer_type	model_id	sequence
1	order 1.1	1	1	1	1
2	order 1.2	1	1	1	2
3	order 2.1	1	1	2	1
4	order 1.3	1	1	1	3
5	order 2.2	1	1	2	2