explanation.txt

For those requirements, a good starting point would be having a SELLERS and PRODUCTS tables.

create table sellers (
  id integer,
  paid_up_to timestamp
);

create table products (
  id integer,
  seller_id integer,
  price integer
);

This solution was designed and tested in PostgreSQL 9.6 (even though it should be portable to most RDBMSs out there),
if you'd like to test it yourself. There is also convenient interface at sqlfiddle.com that can be used to avoid setting up a PostgreSQL instance.

The seller has only an ID and another field that will be used for the rolling payouts. The products table refer to their sellers (seller_id) and have a price. That's it. All tables were kept very slim for demonstrational purposes.

For modeling purchases, will need another table:

create table purchases (
  id integer,
  product_id integer,
  price integer,
  happened_at timestamp
);

A purchase refers to product (via product_id) and happens at some moment in time (happened_at). It also has a price. That allows us to change the price on the product without affecting the purchases. That will be handy if we want to support one-off discounts and remarking products.

Refunds will be modeled as another table:

create table refunds (
  id integer,
  purchase_id integer,
  happened_at timestamp
);

They identify the purchase that is being refunded (purchase_id) and the time when it was refunded (happened_at).

Rolling payouts
---------------

The one field we didn't get into was paid_up_to in the sellers table. It will be updated when we process the payment for the seller. When it comes time for payment, a job will check the last paid_up_to date for the seller and check for any outstanding purchases or refunds. If the total price of purchases is enough for covering the refunds, the payment will be processed and the paid_up_to field will be updated. If we do not have enough purchases to cover for the refunds, we won't pay the seller and won't update the paid_up_to field.

This data model supports many payment strategies. If we want to bulk pay all sellers at the same date, the paid_up_to values will be the same for everyone. If we want to partition the payments according to the days of the week, there paid_up_to values will be a day apart from one seller to another. And if we want to issue an unscheduled payment, we just have to remember to record when it happened.

One limitation we do have has to do with refunds. If the refunds cannot be covered by the purchases, we won't pay the seller. But we also won't be paid back for the refund. We have a business decision to make here. If we decide to give some time for the seller to make some sales, we are able to know how long have we been holding the debt.

Balance
-------

The seller balance can also be calculated from this data model. For payment we considered purchases and refunds up to a certain date. For balance we will consider purchases and refunds _after_ the paid_up_to date. And since a balance is a very common operation, we may want to implement it as a materialized view in our database:

create materialized view
    balances (seller_id, balance)
as
    SELECT
        id,
        (
            SELECT
                coalesce(SUM(purchases.price), 0) AS total_purchases
            FROM
                purchases
            INNER JOIN
                products
            ON
                products.id = purchases.product_id AND
                products.seller_id = sellers.id
            WHERE
                purchases.happened_at > sellers.paid_up_to
        )
        -
        (
            SELECT
                coalesce(SUM(refunded_purchases.price), 0) AS total_refunds
            FROM
                purchases refunded_purchases
            INNER JOIN
                products
            ON
                products.id = refunded_purchases.product_id AND
                products.seller_id = sellers.id
            INNER JOIN
                refunds
            ON
                refunded_purchases.id = refunds.purchase_id AND
                refunds.happened_at > sellers.paid_up_to
        ) AS current_balance
    FROM
        sellers;

Indexes
-------
Our queries will involve a lot of joining and navigating tables. It will be nice to index every table by their foreign keys:

create index on products (seller_id);
create index on purchases (product_id);
create index on refunds (purchase_id);


Code
----

All the code and example data used to design this solution is available at

    https://gist.github.com/thiagoarrais/77e4c594e5c42beddf8bfcf964cfec9a
    
You will also find some sample queries for the payment rollout job there.

purchases.txt

sellers
----------------------------
id       last_paid_at
1        04JAN
2        04JAN
3        --
                            eliminar
purchases                   vvvvvvvvv
----------------------------
id       product_id  price  refunded   happened_at
101               1     10       yes   01JAN
102               1     10        no   02JAN
103               2     10        no   02JAN
104               1     12        no   03JAN
105               1     12       yes   04JAN
106               1     12        no
         
refunds
------------------------------
id       purchase_id       refunded_at
201              101       04JAN
202              105       04JAN

                  
Need to answer:

How to deal with purchases that are refunded AFTER payment.
Where to place the indexes
Why price is "repeated" inside the purchase.

Processar compra:

INSERT INTO purchases (product_id, price, purchased_at) values (:product_id, :price, :purchased_at);

Processar re-embolso:

INSERT INTO refunds (purchase_id, refunded_at) values(:purchase_id, :refunded_at);

Processar pagamento de um vendedor (batch):

SELECT
    SUM(refunded_purchases.price) AS total_refunds
    SUM(outstanding_purchases.price) AS total_purchases
FROM
    sellers
WHERE
    id = :seller_id
INNER JOIN
    refunds, products, purchases AS refunded_purchases
ON
    purchases.id = refunds.purchase_id AND
    products.id = purchase.product_id AND
    sellers.id = products.seller_id AND
    refunds.refunded_at > sellers.paid_up_to AND
    refunds.refunded_at < :pay_up_to
INNER JOIN
    products, purchases AS outstanding_purchases
ON
    products.id = purchase.product_id AND
    sellers.id = products.seller_id AND
    purchases.happened_at > sellers.paid_up_to AND
    purchases.happeed_at < :pay_up_to
    
SE :total_purchases >= :total_refunds
    pagar(total_purchases - total_refunds)
    gravar(:pay_up_to)
CASO_CONTRÁRIO
   # não fazer nada
    
gravar(:pay_up_to):

UPDATE
    sellers
WHERE
    id = :seller_id
SET
    paid_up_to = :pay_up_to

-------------------

Casos de teste:

- E se não tiver nenhuma compra e nenhum refund? Era bom gravar que já tá quite para diminuir a proxima janela de processamento
- Refund antes de ser processado? Precisa contar nos dois totais para um poder zerar o outro.
- Refund depois de ser processado com comprar suficientes para cobrir? Deve deduzir, pagar a diferença e atualizar o paid_up_to. Na próxima rodada não deve mais ser considerada.
- Refund depois de ser processado com compras insuficientes para cobrir? Não deve atualizar o paid_up_to para tentar compensar com as compras da próxima rodada

--------------------------------

How to deal with purchases that are refunded AFTER payment?

Purchases that are refunded before being processes will appear both on the credit and on the debit side when processing payments. One will even out the other. On the other hand, purchases that are refunded after being processed will appear only on the debit side. They will need to be paid for by other purchases.

If they cannot be totally covered by the current purchases, we won't mark the seller as evened out (by not recording the `paid_up_to` date). This will give them a chance to accumulate more purchases until the next time the batch job runs again. There is also the possibility of a seller being unable to pay for refunds in too many subsequent processments. The `paid_up_to` field can be used to detect this cases too; we can check if it is older than (say) one month and use that to charge the seller somehow.

queries.sql

select * from balances;

-- queries for rolling payouts:
-- outstanding refunds
SELECT
    sellers.id,
    SUM(refunded_purchases.price) AS total_refunds
FROM
    sellers
INNER JOIN
    products
ON
    sellers.id = products.seller_id
INNER JOIN
    purchases refunded_purchases
ON
    products.id = refunded_purchases.product_id 
INNER JOIN
    refunds
ON
    refunded_purchases.id = refunds.purchase_id AND
    refunds.happened_at > sellers.paid_up_to AND
    refunds.happened_at < timestamp '2018-08-08 15:00:00'
GROUP BY
    sellers.id;
    
-- outstanding purchases
SELECT
    sellers.id,
    SUM(purchases.price) AS total_purchases
FROM
    sellers
INNER JOIN
    products
ON
    sellers.id = products.seller_id
INNER JOIN
    purchases
ON
    products.id = purchases.product_id AND
    purchases.happened_at > sellers.paid_up_to AND
    purchases.happened_at < timestamp '2018-08-08 15:00:00'
GROUP BY
    sellers.id;

schema.sql

create table sellers (
  id integer,
  paid_up_to timestamp
);

create table products (
  id integer,
  seller_id integer,
  price integer
);

create table purchases (
  id integer,
  product_id integer,
  price integer,
  happened_at timestamp
);

create table refunds (
  id integer,
  purchase_id integer,
  happened_at timestamp
);

create index on products (seller_id);
create index on purchases (product_id);
create index on refunds (purchase_id);

create materialized view
    balances (seller_id, balance)
as
    SELECT
        id,
        (
            SELECT
                coalesce(SUM(purchases.price), 0) AS total_purchases
            FROM
                purchases
            INNER JOIN
                products
            ON
                products.id = purchases.product_id AND
                products.seller_id = sellers.id
            WHERE
                purchases.happened_at > sellers.paid_up_to
        )
        -
        (
            SELECT
                coalesce(SUM(refunded_purchases.price), 0) AS total_refunds
            FROM
                purchases refunded_purchases
            INNER JOIN
                products
            ON
                products.id = refunded_purchases.product_id AND
                products.seller_id = sellers.id
            INNER JOIN
                refunds
            ON
                refunded_purchases.id = refunds.purchase_id AND
                refunds.happened_at > sellers.paid_up_to
        ) AS current_balance
    FROM
        sellers;
    
insert into sellers (id, paid_up_to) values (1, timestamp '1980-01-01');
insert into sellers (id, paid_up_to) values (2, timestamp '2018-08-01 15:00:00');
insert into sellers (id, paid_up_to) values (3, timestamp '2018-08-01 15:00:00');

insert into products (id, seller_id, price) values (101, 1, 8000);
insert into products (id, seller_id, price) values (102, 1, 2000);
insert into products (id, seller_id, price) values (103, 1, 10000);
insert into products (id, seller_id, price) values (104, 1, 7000);
insert into products (id, seller_id, price) values (105, 2, 13000);
insert into products (id, seller_id, price) values (106, 2, 17000);
insert into products (id, seller_id, price) values (107, 3, 1000);
insert into products (id, seller_id, price) values (108, 3, 500);

insert into purchases (
  id, product_id, price, happened_at)
values 
  (201, 105, 13000, timestamp '2018-07-30 09:55:28.449');

insert into purchases (
  id, product_id, price, happened_at)
values 
  (202, 105, 13000, timestamp '2018-07-31 19:47:52.327');

insert into refunds (
  id, purchase_id, happened_at)
values
  (301, 202, timestamp '2018-07-31 22:26:42.843');

insert into purchases (
  id, product_id, price, happened_at)
values 
  (203, 103, 10000, timestamp '2018-08-01 14:56:39.546');

insert into purchases (
  id, product_id, price, happened_at)
values 
  (204, 102, 2000, timestamp '2018-08-01 14:58:20.187');
  
-- processing ---------------

insert into refunds (
  id, purchase_id, happened_at)
values
  (302, 201, timestamp '2018-08-02 23:04:21.446');

insert into refunds (
  id, purchase_id, happened_at)
values
  (303, 204, timestamp '2018-08-08 13:06:37.332');
  
------ processing -------------

insert into purchases (
  id, product_id, price, happened_at)
values 
  (205, 106, 17000, timestamp '2018-08-08 17:27:31.882');

insert into purchases (
  id, product_id, price, happened_at)
values 
  (206, 105, 13000, timestamp '2018-08-08 21:12:29.128');

insert into purchases (
  id, product_id, price, happened_at)
values 
  (207, 101, 8000, timestamp '2018-08-09 02:16:33.764');  

insert into purchases (
  id, product_id, price, happened_at)
values 
  (208, 106, 17000, timestamp '2018-08-09 04:57:16.956');
  
REFRESH MATERIALIZED VIEW balances;