Integer Money and Row Level Security in Postgres

#Why money is never a float

Open any JavaScript console and add 0.1 and 0.2. You get 0.30000000000000004. Computers store most decimal fractions as binary approximations, and when you add thousands of them the errors become visible rupees. For a shop that reconciles its cash drawer every night, a drawer that is off by a few paisa is a real problem.

RetailFlow, a multi tenant point of sale for South Asian shops, stores every amount as an integer in the smallest unit. Rs 12.50 is 1250 paisa. Addition, subtraction and comparison are then exact, and the drawer reconciles to the paisa before anyone goes home.

create table sale_items (
  id          bigint generated always as identity primary key,
  sale_id     bigint not null references sales(id),
  product_id  bigint not null references products(id),
  quantity    integer not null check (quantity > 0),
  unit_price  bigint  not null check (unit_price >= 0),  -- paisa
  line_total  bigint  not null check (line_total >= 0)   -- paisa
);

Rounding has to happen somewhere, for example when a percentage discount or a weighted average cost does not divide evenly. Pick the place and the rule once, write it down, and apply it in one function. The mistake is rounding in five different places.

#One function, one transaction

A sale touches several things at once: stock goes down, money comes in, a ledger entry is written, an invoice number is issued. If your app does these as separate calls, a crash in the middle leaves the shelf and the drawer disagreeing, and nobody can say which is right.

In RetailFlow a sale is one database function. This is a simplified sketch of the idea, with helper names of my own. A function in Postgres runs inside a single transaction, so every step happens or none does.

create function complete_sale(p_shop uuid, p_items jsonb, p_paid bigint)
returns bigint
language plpgsql
security definer
set search_path = public
as $$
declare
  v_sale bigint;
  v_total bigint;
begin
  if not has_permission(p_shop, 'sales.create') then
    raise exception 'not allowed';
  end if;

  select coalesce(sum((i->>'quantity')::int * (i->>'unit_price')::bigint), 0)
    into v_total
    from jsonb_array_elements(p_items) as i;

  insert into sales (shop_id, total, paid, invoice_no)
  values (p_shop, v_total, p_paid, next_invoice_no(p_shop))
  returning id into v_sale;

  -- insert sale_items, decrement stock, write the ledger row here.
  -- Any error rolls back everything above.

  return v_sale;
end;
$$;

Two lines deserve attention. security definer makes the function run with its owner’s rights, which is how it can write tables the caller cannot touch, so the permission check at the top is not optional. And set search_path = public stops a malicious schema from hijacking the names the function uses.

#Tenants isolated by the database

RetailFlow serves many shops from one database. If isolation depends on every query remembering a where shop_id clause, it takes one forgotten clause to show one shop another shop’s customers.

Row Level Security moves that rule into Postgres. Once it is enabled on a table, the database adds the policy to every query, whoever wrote the query.

alter table sales enable row level security;

create policy "members read their shop's sales"
  on sales for select
  using (shop_id in (select shop_id from shop_members where user_id = auth.uid()));

-- No insert, update or delete policy for clients.
-- Writes go through complete_sale() and friends.

The design in RetailFlow is deliberately strict. Clients get SELECT only policies. There is no client write policy on any financial table, so even a stolen user session cannot edit a ledger directly. Every write goes through a permission checked function like the one above. There are 41 of those functions across 35 tables, and 22 permissions decide what an owner, manager or cashier may call.

#How to test that it holds

• Create two shops and a user in each. Read sales as each user and assert that neither sees the other’s rows.
• Try to insert and update a sale directly as a client. Both should fail.
• Call the sale function with a user who lacks the permission and assert that it raises.
• Make the function fail halfway on purpose and assert that stock and ledger are unchanged.

RetailFlow has 231 tests, 92 migrations and a live demo. The write up of the whole system, including khata credit ledgers and shift reconciliation, is in the RetailFlow case study.