Skip to content

Database

Thorben edited this page Aug 28, 2022 · 15 revisions

Database

TL;DR;

TODO: Add file which contains the statements to setup the database in an Supabase-environment

Views

AllTimeExpenses

CREATE OR REPLACE
  VIEW "AllTimeExpenses"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND transactions.amount < 0
  AND transactions.date <= now()
  GROUP BY
    categories.id,
    transactions.created_by;

AllTimeIncome

CREATE OR REPLACE
  VIEW "AllTimeIncome"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND transactions.amount > 0
  AND transactions.date <= now()
  GROUP BY
    categories.id,
    transactions.created_by;

BudgetProgress

CREATE OR REPLACE
  VIEW "BudgetProgress"
  AS SELECT
    budget.id,
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    budget.budget,
    (SELECT
        SUM(transactions.amount)
      FROM
        transactions
      WHERE
        transactions.amount < 0
      AND
        transactions.date <= now()
      AND
        extract(month FROM transactions.date) = extract(month FROM now())
      AND
        extract(year FROM transactions.date) = extract(year FROM now())
      AND transactions.category = budget.category
    ) as "currentlySpent",
    budget.created_by,
    budget.inserted_at,
    budget.updated_at
  FROM budget
  LEFT JOIN categories
    ON categories.id = budget.category;

CurrentMonthExpenses

CREATE OR REPLACE
  VIEW "CurrentMonthExpenses"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND
    transactions.amount < 0
  AND
    transactions.date <= now()
  AND
    extract(month FROM transactions.date) = extract(month FROM now())
  AND
    extract(year FROM transactions.date) = extract(year FROM now())
  GROUP BY
    categories.id,
    transactions.created_by;

CurrentMonthIncome

CREATE OR REPLACE
  VIEW "CurrentMonthIncome"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND
    transactions.amount > 0
  AND
    transactions.date <= now()
  AND
    extract(month FROM transactions.date) = extract(month FROM now())
  AND
    extract(year FROM transactions.date) = extract(year FROM now())
  GROUP BY
    categories.id,
    transactions.created_by;

DailyIncome

CREATE OR REPLACE
  VIEW "DailyIncome"
  AS SELECT
      transactions.date::DATE,
      sum(transactions.amount),
      transactions.created_by
    FROM transactions
    WHERE transactions.amount > 0
    AND transactions.date < now()
    GROUP BY 1, transactions.created_by
    ORDER BY transactions.date::DATE ASC;

DailyExpense

CREATE OR REPLACE
  VIEW "DailyExpense"
  AS SELECT
      transactions.date::DATE,
      sum(transactions.amount),
      transactions.created_by
    FROM transactions
    WHERE transactions.amount < 0
    AND transactions.date < now()
    GROUP BY 1, transactions.created_by
    ORDER BY transactions.date::DATE ASC;

RPCs

getIncome(userId uuid, startDate Date, endDate Date)

CREATE OR REPLACE
FUNCTION "getIncome"("userId" uuid, "startDate" Date, "endDate" Date)
RETURNS TABLE (
  sum float8,
  category json,
  created_by uuid
)
as $$
  SELECT
      SUM(transactions.amount),
      json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
      transactions.created_by
    FROM
      transactions
    INNER JOIN categories
      ON categories.id = transactions.category
    AND
      transactions.amount > 0
    AND
      transactions.date::DATE >= "startDate"
    AND
      transactions.date::DATE <= "endDate"
    WHERE
      transactions.created_by = "userId"
    GROUP BY
      categories.id,
      transactions.created_by;
$$ language sql;

getExpenses(userId uuid, startDate Date, endDate Date)

CREATE OR REPLACE
FUNCTION "getExpense"("userId" uuid, "startDate" Date, "endDate" Date)
RETURNS TABLE (
  sum float8,
  category json,
  created_by uuid
)
as $$
  SELECT
      SUM(transactions.amount),
      json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
      transactions.created_by
    FROM
      transactions
    INNER JOIN categories
      ON categories.id = transactions.category
    AND
      transactions.amount < 0
    AND
      transactions.date::DATE >= "startDate"
    AND
      transactions.date::DATE <= "endDate"
    WHERE
      transactions.created_by = "userId"
    GROUP BY
      categories.id,
      transactions.created_by;
$$ language sql;

profiles

Columns

Column Type Description
id uuid id-> auth.users.id
username text
updated_at timestampz

SQL

create table profiles (
  id uuid references auth.users not null,
  updated_at timestamp with time zone,
  username text unique,

  primary key (id),
  unique(username),
  constraint username_length check (char_length(username) >= 3)
);

Policies

Enable

alter table profiles
  enable row level security;

Select

create policy "Public profiles are viewable by everyone." on profiles
  for select using (true);

Create

create policy "Users can insert their own profile." on profiles
  for insert with check (auth.uid() = id);

Update

create policy "Users can update own profile." on profiles
  for update using (auth.uid() = id);

transactions

Columns

Column Type Description
id int8 PK
category int8 FK category -> categories.id
paymentMethod int8 FK category -> categories.id
receiver text
amount float8
description text or null
date timestampz
created_by uuid FK created_by -> auth.users.id
inserted_at timestampz
updated_at timestampz

SQL

create table transactions (
  id bigint generated by default as identity primary key,
  category bigint references public.categories not null,
  paymentMethod bigint references public."paymentMethods" not null,
  receiver text not null,
  description text,
  amount float not null,
  date timestamp with time zone not null,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Policies

Enable

alter table "public"."transactions"
  enable row level security;

All (for authentificated)

Used for the web-app

CREATE POLICY "Users can handle their transactions." ON "public"."transactions"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))

All (all, insert alone won't work)

Used for the subscription-service

CREATE POLICY "Subscription-Service can insert transactions." ON "public"."transactions"
AS PERMISSIVE FOR ALL
TO authenticated
USING ((email() = 'EMAIL'::text))
WITH CHECK ((email() = 'EMAIL'::text))

budget

Columns

Column Type Description
id int8 PK
category int8 FK category -> categories.id
budget float8
created_by uuid FK created_by -> auth.users.id
inserted_at timestampz
updated_at timestampz

SQL

create table budget (
  id bigint generated by default as identity primary key,
  category bigint references public.categories not null,
  budget float not null,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Policies

Enable

alter table "public"."budget"
  enable row level security;

All

CREATE POLICY "Users can handle their budget." ON "public"."transactions"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))

subscriptions

Columns

Column Type Description
id int8 PK
category int8 FK category -> categories.id
paymentMethod int8 FK category -> categories.id
receiver text
amount float8
description text or null
execute_at int8
created_by uuid FK created_by -> auth.users.id
inserted_at timestampz
updated_at timestampz

SQL

create table subscriptions (
  id bigint generated by default as identity primary key,
  category bigint references public.categories not null,
  paymentMethod bigint references public."paymentMethods" not null,
  receiver text not null,
  description text,
  amount float not null,
  execute_at bigint not null,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Policies

Enable

alter table "public"."subscriptions"
  enable row level security;

All (for authentificated)

Used for the app

CREATE POLICY "Authentificated users can handle their subscription." ON "public"."subscriptions"
AS PERMISSIVE FOR ALL
TO authenticated
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))

Select (for Subscription-Service)

Used for the subscription-service

CREATE POLICY "Subscription-Service can select subscriptions." ON "public"."subscriptions"
AS PERMISSIVE FOR SELECT
TO authenticated
USING ((email() = 'EMAIL'::text))

categories

Columns

Column Type Description
id int8 PK
name text
description text or null
created_by uuid FK created_by->auth.users.id
inserted_at timestampz
updated_at timestampz

SQL

create table categories (
  id int8 generated by default as identity primary key,
  name text not null,
  description text,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Policies

Enable

alter table "public"."categories"
  enable row level security;

All

CREATE POLICY "Users can handle their categories." ON "public"."categories"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))

paymentMethods

Columns

Column Type Description
id int8 PK
name text
provider text
address text
description text or null
created_by uuid FK created_by -> auth.users.id
inserted_at timestampz
updated_at timestampz

SQL

create table paymentMethods (
  id int8 generated by default as identity primary key,
  name text not null,
  provider text not null,
  address text not null,
  description text,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Policies

Enable

alter table "public"."paymentMethods"
  enable row level security;

All

CREATE POLICY "Users can handle their payment-methods." ON "public"."paymentMethods"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))

feedback

Columns

Column Type Description
id int8 PK
rating float4
text text or null
inserted_at timestampz

SQL

create table feedback (
  id bigint generated by default as identity primary key,
  rating float4 not null,
  text text,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);
Clone this wiki locally