I have following schema in my supabase project I want to add Supabase #sql

I have following schema in my supabase project: I ...

ak4zh

03/18/2022, 7:18 AM

I have following schema in my supabase project: I want to add some constraints to ensure following: - for each record in

transactions

atleast 2 record will be inserted in

journals

- for each record in

journals

against a

transaction_id

the sum of amount for

is_credit=true

must be equal to

is_credit=false

otherwise all records roll back - I also do not want any record in

transactions

without any corresponding records in

journals

How to achieve above ? I will be using supabase.js to push new records.

Copy code

sql
CREATE TABLE IF NOT EXISTS public.accounts (
  id uuid NOT NULL DEFAULT uuid_generate_v4(),
  created_at timestamp with time zone DEFAULT now(),
  name text COLLATE pg_catalog."default" NOT NULL,
  CONSTRAINT accounts_pkey PRIMARY KEY (id),
);


CREATE TABLE transactions (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
  created_at timestamp with time zone DEFAULT now(),
  transaction_date date not null default current_date,
  description text NOT NULL,
  CONSTRAINT transactions_pkey PRIMARY KEY (id),
);

CREATE TABLE journals(
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
  transaction_id bigint NOT NULL,
  account_id uuid NOT NULL,
  amount NUMERIC(20, 2) NOT NULL,
  is_credit boolean NOT NULL,
  CONSTRAINT journals_pkey PRIMARY KEY (id),
  CONSTRAINT journals_transaction_id_fkey FOREIGN KEY (transaction_id)
    REFERENCES public.transactions (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
  CONSTRAINT journals_account_id_fkey FOREIGN KEY (account_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
);

ak4zh

03/18/2022, 9:32 AM

Copy code

sql
CREATE OR REPLACE FUNCTION insert_transaction(transaction_record json, journal_records json[])
RETURNS TABLE (id bigint, created_at timestamp with time zone, transaction_date date, description text) AS $$
DECLARE
  new_transaction_id int;
  x json;
  new_journal_ids int[];
  newest_journal_id int;
  credit_total numeric;
  debit_total numeric;
BEGIN
  credit_total = 0;
  debit_total = 0;
  FOREACH x IN ARRAY journal_records LOOP
    IF (x->>'is_credit')::boolean is true
    THEN credit_total = credit_total + (x->>'amount')::numeric;
    ELSE debit_total = debit_total + (x->>'amount')::numeric;
    END IF;
  END LOOP;

  IF 
    credit_total != debit_total 
  THEN 
    RAISE EXCEPTION 'Credit amount must match debit amount';
  END IF;

  -- this check ensures to not enter a transaction if:
  -- the debit or credit is 0 or negative
  -- debit or credit entries are not specified for the transaction
  IF 
    credit_total < 1
  THEN 
    RAISE EXCEPTION 'Credit and Debit amount must be greater than 0';
  END IF;

  INSERT INTO public.transactions (transaction_date, description) VALUES(
    (transaction_record->>'transaction_date')::date,
    (transaction_record->>'description')
  ) RETURNING transactions.id INTO new_transaction_id;
  FOREACH x IN ARRAY journal_records LOOP

    INSERT INTO public.journals (transaction_id, account_id, amount, is_credit) VALUES(
      new_transaction_id,
      (x->>'account_id')::uuid,
      (x->>'amount')::numeric,
      (x->>'is_credit')::boolean
    ) RETURNING journals.id INTO newest_journal_id;
    new_journal_ids := new_journal_ids || newest_journal_id;
  END LOOP;
  RETURN QUERY
  SELECT 
    transactions.id, transactions.created_at, transactions.transaction_date, transactions.description
  FROM 
    public.transactions
  WHERE 
    transactions.id = new_transaction_id;
END
$$ LANGUAGE plpgsql;

Previous Next