Database

Database

• TL;DR;
• profiles
• transactions
• subscriptions
• categories
• paymentMethods

TL;DR;

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

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

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))

---

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

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

---

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))

---