Deletion & History

A balance that cannot be wrong

Difficulty 5/5Subscribers

A wallet product moves money between accounts.

The requirements are the ones an auditor asks for:

Every movement of money is recorded and permanent. Nothing is updated, nothing is deleted. A correction is a new movement, not an edit.
Money is never created or destroyed. A transfer takes from one account and gives to another, and the two halves are one indivisible fact: one cannot exist without the other.
An account's balance is derivable from the movements, and always agrees with them. There is no way to set a balance directly.
A customer account cannot go negative. The house account is where money enters the system from, so it is negative by design, and that is what makes the books sum to zero.

The tests read balances through a view you provide, account_balances, with account_id and balance_cents.

accounts already exists, holding two customers and the house, and the two customer accounts were each opened with 10,000 cents, recorded as movements out of the house account.

The one thing that is fixed

3 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

accounts
idholderkind
entries
idtransfer_idaccount_idamount_cents
account_balances
account_idbalance_cents

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE accounts (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  holder TEXT NOT NULL,
  kind   TEXT NOT NULL CHECK (kind IN ('customer', 'house'))
);

INSERT INTO accounts (holder, kind)
VALUES ('Ana', 'customer'), ('Bruno', 'customer'), ('House', 'house');

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…