Backend — Configure Supabase

After you've setup your Supabase account, you'll need to configure your Supabase project.

We highly recommend to use the Supabase CLI to facilitate working with Supabase. Of course you can also work directly with Supabase Studio in the browser. However, single steps and configurations might not be reproducible and thus are prone to errors and not very developer friendly. Thus, we use the Supabase CLI run Supabase locally. First edit your backend locally, before you migrate/push it to our remote Supabase project.

Use Supabase CLI for local instance, database setup and migrations

1. Install the Supabase CLI. See their documentation for instructions
2. Open your favored terminal and navigate to your fork of the Gieß den Kiez API Repo
3. Run npm ci to install dependencies
4. Run supabase login to login to your Supabase account that you've created earlier
5. Run supabase start to start a local instance of Supabase on your computer. Please note that you need Docker to be installed and running.

Supabase will create a database according to the SQL statements in the supabase/migrations folder, afterwards it will execute all the SQL statements within the supabase/seed.sql. You should now have a Supabase instance running locally. Credentials and the link to the locally running Supabase Studio will be provided in your terminal.

1. Adjust your tree data to meet the structure of the trees table.
2. Push the tree data into your locally running trees table. We use this Python script to update our tree data on a yearly basis. The script might be a good starting point for your own Python script
3. Run supabase link --project-ref <YOUR REMOTE PROJECT ID> to connect your CLI to the remote instance. The project id can be found in the URL of your supabase project and on the settings.
4. Run supabase db push to push your local changes to your remote Supabase project.

💡 We highly recommend to transform your tree data into the current Gieß den Kiez data structure to avoid customization of the Frontend. However, if you like to use a customized database schema, please read about Supabase migrations to keep track of your changes.

Role Level Security (RLS)

The database migrations defined in the Gieß den Kiez API Repo repository define important security mechanisms of the database, called "Role Level Security". Those rules prevent the database tables to be accessible publicly. They are defined to be as restrictive as possible while at the same time enabling all functionality required for operating the Gieß den Kiez application. In case you want to setup your own database schema or adapt our schema, make sure to follow the best practice guidelines of Supabase for Role Level Security.

An example RLS for the table trees_watered which stores the waterings for specific trees is:

create policy "Enable select for authenticated users only"
on "public"."trees_watered"
as permissive
for select
to authenticated
using (true);

Please view the migration files in the migrations folder to see all RLS rules.

Database Functions

Please note, that the mentioned migrations in the migrations folder also define some database functions, which define Postgres queries which are too complex to be constructed using the Supabase Javascript Client. Using Remove Procedure Calls, the Javascript client can call those functions. An example is the function waterings_for_tree which allows the frontend to fetch all waterings for a specific tree:

CREATE OR REPLACE FUNCTION public.waterings_for_tree(t_id text)
 RETURNS TABLE(amount numeric, "timestamp" timestamp with time zone, username text, id integer, tree_id text)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
	RETURN query
	
	SELECT trees_watered.amount, trees_watered.timestamp, trees_watered.username, trees_watered.id, trees_watered.tree_id FROM trees_watered WHERE trees_watered.tree_id = t_id;
		
END;
$function$

This function also demonstrates the use of SECURITY DEFINER, which bypasses the restrictive RLS on the trees_watered. This way it is possible to prevent the frontend to fetch all waterings, but allows it to fetch waterings for a single tree. Please note that this is only an example function used for Gieß den Kiez, for a complete list of the database functions, read all migrations in the migrations folder.

Use Supabase for Storage

Gieß den Kiez uses Supabase Storage to store two files:

• pumps.geojson
• weather_light.geojson

In ordern to Setup the DWD (weather data) Harvester and the OSM Pumps Harvester, you'll first need to activate Supabase Storage. Therefore, navigate to your Supabase project and click on Storage in the sidebar. Create a new folder called data_assets.

Run the following SQL once in your Supabase Studio SQL editor to configure the storage buckets.

CREATE POLICY "Public Access" ON storage.objects
	FOR SELECT
		USING (bucket_id = 'data_assets');

UPDATE
	"storage".buckets
SET
	"public" = TRUE
WHERE
	buckets.id = 'data_assets';

You'll need the storage, once you will configure the GitHub Actions of the DWD Harvester Repository and the OSM Pumps Repository.