thumbnail_profile-pic.jpeg

xsmall_profile-pic.jpeg

If you're in the development community you will have seen [@dhh talking about dropping TypeScript from Turbo 8](https://world.hey.com/dhh/turbo-8-is-dropping-typescript-70165c01).

![DHH moves Turbo 8 to JavaScript](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/dhh-turbo-8.png-864dfdad10c0b5fb4418839955f957c1.png)

Now, I'm by no way a DHH fan boy, however, it got me thinking about our own project BoardShape. When I reflected on the work we had done, and particulary thinking about our pair-coding (which is something we do often), so much of our time is spent wrangling TypeScript. I don't want to spend too long writing about the good points and bad points of TypeScript, I was just interested to see how quickly I could take our project from entirely TypeScript to entirely JavaScript. 

## Let the fun begin

### Step 1 - Branch:

I branched main (duh!).

```bash
git checkout -b ts-js
```

## Step 2 - Remove packages:

I looked through package.json and removed a bunch of packages.

```
npm remove typescript
npm remove @types/node
npm remove @types/react
```

... etc. You'll need to work out which packaged you don't need.

### Step 3 - Remove tsconfig and check paths:

You might have some paths for libraries in your tsconfig, so copy those out.

Delete **tsconfig.json** from the project. Don't need that anymore.

Create **jsconfig.json** and add your paths, e.g:

```json
{
  "compilerOptions": {
    "baseUrl": ".",
    "paths": {
      "@src/*": ["src/*"],
      "@components/*": ["components/*"],
      "@lib/*": ["src/lib/*"],
      "@utils/*": ["src/utils/*"],
      "@hooks/*": ["src/hooks/*"],
      "@seeds/*": ["seeds/*"]
    }
  }
}
```

### Step 4 - The fun part, converting TypeScript to JavaScript

So, this is the bit that bothered me. We've got about 300 odd .ts and .tsx files in our project. I did not want to manually convert everthing. Obviously, TS just gets transpiled to JS but the transpiled JS is not really easy to read and the formatting is not what we want. I certainly don't want to maintain 300 JS files that are in transpiled format.

A quick google search will lead you to a great project [transform.tools](https://transform.tools/typescript-to-javascript) - it's open source, and you can grab it at https://github.com/ritz078/transform - using the public version it seemed to do a good job of stripping out my TypeScript. I was pretty amazed there isn't just an easy CLI to do this, and it seems like Babel is what transform uses if you [look at the API](https://github.com/ritz078/transform/blob/master/pages/api/typescript-to-javascript.ts) but I couldn't get this to work on the command line easily.

So, after failing to find a better solution, I downloaded the **transform.tools** source code and set it up locally, it ran under localhost just great. I did this because I didn't really want to hit their server with all my source code for a few reasons!

Then, I wrote a simple script to iterate over ts and tsx files. Please excuse how crude this is, I literally through it together and every time I made a mistake I just reverted my branch until I got it right.

![Please excuse the crudity of this model](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/crude-model-doc-brown.jpg-bba5bd313844b8f2c399fdb86063cee5.jpg)

Here is the script:

```javascript
#!/usr/bin/env node
var dir = require('node-dir');
const fs = require('fs');

async function run() {

  dir.readFiles(__dirname, {
    excludeDir: ['node_modules'],
    match: /.ts$/,
    }, function(err, content, filename, next) {
        if (err) throw err;
        
        const response = fetch('http://127.0.0.1:3000/api/typescript-to-javascript', {
          method: "POST",
          body: content,
        }).then((response) => {
          response.text().then((value) => {

            fs.writeFile(filename, value, err => {
              if (err) {
                console.error(err);
              }
            });

          })
        })
        
        next();
    },
    function(err, files){
        if (err) throw err;
        console.log('finished reading files:');
    });


}
run();
```

This just does the .ts files, you can see the match line which you can change to .tsx to process those.

All this does is: loop over files (ignoring node_modules) and posts any .ts content to our transform.tool running locally. It then writes out the response and saves the file.

You will need to ensure you have an up-to-date nodejs install so fetch is available and you'll have to **npm install node-dir.**

With this done, we jus thave to change file extensions.

### Step 5 - Rename files

Just a bit of bash to change our .ts files to .js and our .tsx to .jsx:

```bash
find ./ -depth -name "*.ts" -exec sh -c 'mv "$1" "${1%.ts}.js"' _ {} \;
find ./ -depth -name "*.tsx" -exec sh -c 'mv "$1" "${1%.tsx}.jsx"' _ {} \;
```

### **Step 6 - Build and fix**

Run your build step. See what's broken.

In my instance, there were about 3 occurences where the transform.tool had put in unwanted function brackets. Easy to fix.

### Step 7 - Test

Obviously, don't skip this step. You wouldn't, of course.

And hey-presto, you're done. This took me a couple of hours of experimentation. Hopefully this helps you do things a little faster.

![Our project converted from TypeScript to JavaScript](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/typescript-convert-to-javascript.png-055fc18c59c4db747525207f33f62885.png)

Please check out our project [BoardShape.com](https://boardshape.com) if you’re interested in running better board meetings with our [Board Management Software for Startups.](

large_typescript-convert-to-javascript.png

medium_typescript-convert-to-javascript.png

small_typescript-convert-to-javascript.png

thumbnail_typescript-convert-to-javascript.png

xsmall_typescript-convert-to-javascript.png

TypeScript has been slowing down our development for a while. It makes pair programming more difficult than it needs to be so we converted our project to JavaScript. DHH says we should, so why not. Here's how we did it.

How we converted our project from TypeScript to JavaScript

We have released a small [Deno](https://deno.com/) library to allow you to [verify Paddle webhooks](https://developer.paddle.com/webhooks/signature-verification) in your [Supabase Edge Functions](https://supabase.com/docs/guides/functions). This works with the new [Paddle](https://paddle.com) billing platform.

Head on over to [Github to use the library](https://github.com/boardshape/deno_paddle_verify).

Please follow us on [Twitter](https://twitter.com/boardshape), reach out to us if you want to know more, and, if you have a small business or organization and want to run better board meetings, [try BoardShape](https://boardshape.com/dashboard#signup) 🤟

large_creative-art-for-kids-plasticine-dinosaur-2022-11-16-20-14-15-utc.jpg

medium_creative-art-for-kids-plasticine-dinosaur-2022-11-16-20-14-15-utc.jpg

small_creative-art-for-kids-plasticine-dinosaur-2022-11-16-20-14-15-utc.jpg

thumbnail_creative-art-for-kids-plasticine-dinosaur-2022-11-16-20-14-15-utc.jpg

xsmall_creative-art-for-kids-plasticine-dinosaur-2022-11-16-20-14-15-utc.jpg

A small Deno library you can use to verify Paddle webhooks in your Supabase functions.

Deno library for verifying Paddle Webhooks

# 	

I was excited to start the integration of [Paddle](https://paddle.com/) with [BoardShape](https://boardshape.com/). The fact [Paddle is a MoR (Merchant of Record)](https://www.paddle.com/blog/what-is-merchant-of-record) should make life substantially easier from an admin perspective. Having previously worked with Stripe though and their amazing DX I found the Paddle process for JS somewhat lacking. Link this file, do some stuff.

As [BoardShape (Board Management Software for Startups)](https://boardshape.com/) is written in NextJS and React using TypeScript I wanted a better way to work with it. I found a few examples but none of them were really terribly React friendly.

I know Paddle are working on an improved version:

![Paddle DX improvements in the pipeline](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/paddle-tweet.png-ee48eb334b2f9bf3b7d805cded2940e2.png)

Until then though, the cleanest way to consume Paddle would seem to be a context provider.

Here's what you need to do to make it work:

Add in to a **types.d.ts** somewhere in your project:

```js
declare global {
  var Paddle: any
}

export {}
```

There's a few examples out there (such as https://stackoverflow.com/questions/65842582/how-to-integrate-paddle-with-nextjs) where people have provided stronger types, however, unless there is something official from Paddle I don't massively see the point in adding it so I've just typed it to **any**.

Now, add your context provider **PaddleProvider.tsx**

```js
'use client'

import { createContext, useState } from 'react'
import Script from 'next/script'

type PaddleContext = any

const PaddleContext = createContext<PaddleContext>(null)

const PaddleProvider = ({ children }: { children: React.ReactNode }) => {
  const [paddle, setPaddle] = useState(null)

  return (
    <PaddleContext.Provider value={paddle}>
      <>
        <Script
          src="https://cdn.paddle.com/paddle/paddle.js"
          onLoad={() => {
            if (process.env.NEXT_PUBLIC_PADDLE_SANDBOX) {
              Paddle.Environment.set('sandbox')
            }
            Paddle.Setup({
              vendor: Number(process.env.NEXT_PUBLIC_PADDLE_VENDOR_ID),
            })
            setPaddle(Paddle)
          }}
        />
        {children}
      </>
    </PaddleContext.Provider>
  )
}

export { PaddleProvider, PaddleContext }

```

Note the two env vars NEXT_PUBLIC_PADDLE_SANDBOX and NEXT_PUBLIC_PADDLE_VENDOR_ID that you need to add to your .env file.

Also note, we set the initial state to null, not undefined. The reason will become apparent in the context consumer.

Next, add a **ContextConsumer.tsx**

```js
import { useContext } from 'react'
import { PaddleContext } from '@components/Paddle/PaddleProvider'

// context consumer hook
const usePaddleContext = () => {
  // get the context
  const context = useContext(PaddleContext)

  // if `undefined`, throw an error
  if (context === undefined) {
    throw new Error('usePaddleContext was used outside of its Provider')
  }

  return context
}

export { usePaddleContext }

```

Next, in our page where we use Paddle, wrap everything in the provider (this is only a snippet):

```js
import { PaddleProvider } from '@components/Paddle/PaddleProvider'

...

return (<PaddleProvider>
  YOUR COMPONENTS
</PaddleProvider>)
```

I only wrap paged where I know I need paddle so you may not want to wrap your entire app with this provider as it will cause the remote JS to load.

Now, wherever you need Paddle:

```js
import { usePaddleContext } from '@components/Paddle/PaddleConsumer'

...

const paddle = usePaddleContext()

if (paddle) {
  //It's loaded do some stuff
}
```

That's it. I look forward to this post being made obsolete by Paddle hopefully releasing a React/Typescript friendly library that can just be installed in to your project with a package manager.

medium_paddle-tweet.png

small_paddle-tweet.png

thumbnail_paddle-tweet.png

xsmall_paddle-tweet.png

Paddle JS does not fit in to a React / Next JS world very cleanly. Here we look at how you can create a context provider to load the Paddle JS library and then consume it within your app.

Creating a React Context Provider for Paddle JS with Typescript

Supabase auth is great, very simple to get going with and add third-party oAuth providers such as Google.

For our app [BoardShape](https://boardshape.com) (lightweight board management software for startups) we have a profile table in the public schema. We use a UUID field and when a user is created in Supabase we create a corresponding profile record with the same ID. So any entry in profiles represents a user managed by Supabase with the exact same ID. Our table definition looks like this:

```sql
CREATE TABLE public.profiles (
 id uuid NOT NULL,
 updated_at timestamptz NULL,
 avatar_url text NULL,
 first_name varchar NULL,
 last_name varchar NULL,
 email varchar NULL,
 has_set_password bool NOT NULL DEFAULT false,
 created_at timestamptz NULL DEFAULT now(),
 title text NULL,
 CONSTRAINT profiles_pkey PRIMARY KEY (id)
);
```

Simple table with a few details and we want to get as much as this from the initial account creation handled by Supabase. So, how do we do this and also copy over the user’s profile pic.

We add a function to our public schema (the schema that holds our tables not the supabase auth tables):

```sql
CREATE OR REPLACE FUNCTION public.create_profile_for_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
  INSERT INTO public.profiles (id, email, first_name, last_name, avatar_url, has_set_password)
  SELECT
    new.id,
    new.email,
    COALESCE(split_part(jsonb_extract_path_text(new.raw_user_meta_data, 'full_name'), ' ', 1), NULL),
    COALESCE(split_part(jsonb_extract_path_text(new.raw_user_meta_data, 'full_name'), ' ', 2), NULL),
    COALESCE(jsonb_extract_path_text(new.raw_user_meta_data, 'picture'), jsonb_extract_path_text(new.raw_user_meta_data, 'avatar_url'), NULL),
    CASE WHEN new.encrypted_password IS DISTINCT FROM '' THEN true ELSE false END;

  RETURN new;
END;
$function$
;
```

This is fairly straight forward but the lines for first_name, last_name and avatar_url are a bit icky because they come back from google in the raw_user_meta_data field in a json object. If we don’t get the data, we just insert nulls.

The bit at the end about new.encrypted password is because we just keep a boolean flag in our profiles table to determine if the user has a password set or not.

_**Note.** You can use this exact same process for other oAuth providers but the details are likely not in the same place, so this trigger would need to be more involved to determine the oauth provider which you can pull from raw_app_meta_data on the auth.users table but again, it’s json, so you’ll have to extract it carefully._

Once you have this function you just need a trigger setup on the auth.users table as follows:

```sql
create trigger create_new_profile_for_user after
insert
    on
    auth.users for each row execute function create_profile_for_user();
  ```
  
  We also have an function and update trigger for recording the password being set or not like this:
  
  ```sql
  CREATE OR REPLACE FUNCTION public.handle_update_user_password_status()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
begin
  update public.profiles
  set has_set_password = CASE WHEN new.encrypted_password IS DISTINCT FROM '' THEN true ELSE false END
  where id = new.id;
  return new;
end;
$function$
;
```

```sql
create trigger update_user_password_status after
update
    of encrypted_password on
    auth.users for each row execute function handle_update_user_password_status();
  ```
  
  Remember, when working with Supabase and postgres in general, don’t be afraid of triggers and functions. It takes a bit of a mental shift for some putting logic like this in the database but it’s fast, reliable and [can really help with RLS as well](https://boardshape.com/engineering/how-to-implement-rls-for-a-team-invite-system-with-supabase).
  
  Please head over to [boardshape.com](https://boardshape.com) and ]try our product](https://boardshape.com/dashboard#signup). We’ve had a lot of fun working with Supabase and Next 13. You can also follow us on Twitter at [/boardshape](https://twitter.com/boardshape).

small_sql.webp

thumbnail_sql.webp

xsmall_sql.webp

Supabase auth is great and allows you to easily connect oAuth providers. In this blog post we will show you how you can pull through google profile details just using SQL

How to pull Google profile through on account creation with Supabase

Let's reduce the amount of database queries you're running per page and make your code cleaner at the same time by returning **multi-dimensional data** and type it correctly.

For the purpose of explaining this. we are going to use the relationship that exists in our product between **teams** and the **members** of a team. Each team can can have one or many members. This relationship can be visually seen on a team card on the dashboard of the app where we show team members that belong to each team:

![Teams and members](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/teams-and-members.png-a00063a3a4255f1fd10f892a85185425.png)

## Approach 1 - Multiple Queries 👎

Typically, when developing something like this we might run following queries on our page:

```sql
SELECT * FROM teams; 
```

And, then, for each team we would run:

```sql
SELECT * FROM team_members tm INNER JOIN users u ON tm.user_id = u.id WHERE tm.id = {TEAM_ID} 
```

😱 ok, so whilst this SQL is really simple to understand it creates an **n+1** issue. This basically means for every team you're having to run an additional query, as the number of teams grows so does the number of queries.

## Approach 2 - Single Join Query 👎

The other alternative, is to run something like this:

```sql
SELECT * 
FROM teams t 
INNER JOIN team_members tm ON t.id = tm.team_id 
INNER JOIN users u ON tm.user_id = u.id 
WHERE tm.id = {TEAM_ID} 
```

This is pretty straight-forward SQL, the problem is you end up with results like this:

| Team Name      | First Name | Last Name | Job Title       |
| -------------- | ---------- | --------- | --------------- |
| Boardshape Inc | Nick       | Holder    | CMO             |
| Boardshape Inc | Rick       | Smoulders | CTO             |
| Boardshape Inc | Tom        | Holder    | CEO             |
| Boardshape Inc | Mariana    | Flores    | Chairperson     |
| Boardshape Inc | David      | Anderson  | Non Executive   |
| Boardshape Inc | Mo         | Haider    | Non Executive   |
| Boardshape Inc | Simon      | Hughes    | Secretary       |
| Boardshape Inc | Sara       | Hatfield  | Content Manager |

You have one row for each team member and all the team data is replicated. This is annoying because you want to iterate teams and then iterate members within those teams. Whilst you can do this in JS the code is going to be a bit messy and you're sending more data over the wire.

Another important reason why not to take this approach is that **Supabase can not automatically type this query for you**. You will need to create a view and that view will be it's own type.

## Approach 3 - Strongly typed multi-dimensional results 👍🔥

Here is a different approach you might want to consider. Returning one row per team but with the team members embeded as json per row, and here's how to do it. ***Note.** For the sake of brevity these aren't our actual queries, they only contain a subset of the fields and consequently their may be some minor discrepancies.* We are also doing more complex joins because of our user security context.

Start with creating a postgres function:

```sql
CREATE OR REPLACE FUNCTION public.get_team_members(team_id uuid)
 RETURNS jsonb
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
DECLARE
    result jsonb;
BEGIN
    SELECT json_agg(row_to_json(t)) INTO result
    FROM (
        SELECT 
            tm.team_id, 
            tm.user_id, 
            u.id as user_id, 
            u.first_name, 
            u.last_name 
        FROM 
            public.team_members AS tm
        INNER JOIN 
            public.profiles AS u ON u.id = tm.user_id
        WHERE 
            tm.team_id = get_team_members.team_id
    ) t;
    RETURN result;
END; 
$function$
;

```

The result of this function gives you a **JSON array** that looks like this:

```json
[
  {
   "team_id": "aad4f3b2-2bfa-451a-a1e7-359184c17ca4", 
   "user_id": "5e36b5bf-a446-4d0a-b4f6-e2ed4e6ccb83", 
   "last_name": "Holder",
   "first_name": "Nick",
   "job_title": "CMO"
  }, 
  {
    "team_id": "aad4f3b2-2bfa-451a-a1e7-359184c17ca4", 
    "user_id": "0e23efeb-a011-4d7c-8dbc-d32404f28f5c", 
    "last_name": "Flores", 
    "first_name": "Mariana",
   	"job_title": "Chairperson"
  }, 
  ...Abbreviated
]
```

Now, we want to return the members as above alongside our teams data. So we create a view like the following:

```sql
CREATE OR REPLACE VIEW public.teams_with_team_members
AS SELECT
    t.name,
    get_team_members(t.id) AS members
FROM teams t
```

You then end up with results that look like this:

| Name           | Members                 |
| -------------- | ----------------------- |
| Boardshape Inc | [ JSON ARRAY AS ABOVE ] |

If it isn't obvious, you would have one row per team. You can now iterate teams and iterate members within those teams.

### The magic of Supabase Typing

Wherever possible, we want to leave our typing to Supabase. We just run the following command:

```bash
supabase gen types typescript --local --schema public > src/database.types.ts
```

It spits out all the types for our tables and views. We maintain our own database.alias.types.ts where we shorten down the Supabase types we commonly use like this:

```js
export type ViewTeamsWithTeamMembers =
  Database['public']['Views']['teams_with_team_members']['Row']
```

An issue with this type though is that it looks like this:

```json
teams_with_team_members: {
    Row: {
        name: string | null
        members: Json | null
    }
...
```

The issue here is that members is of type JSON and isn't a strong type that will give us type checking. **We don't want to adjust this type because it's automatically maintained by Supabase.** What we can do to avoid this is create our own type in aliases based on the above which looks like this:

```js 
export interface TeamsWithTeamMemberProfiles
  extends Omit<ViewTeamsWithTeamMembers, 'members'> {
  members: TeamMembers[] | null
}
```

Here we re-type members to be a strongly typed array of TeamMembers which is just another auto-generated type from Supabase:

```js
export type TeamMembers = Database['public']['Tables']['users']['Row']
```

There is actually an error in the above because our **get_team_members** database function doesn't actually return a record from users, it's a joined hybrid record which Supabase doens't know about, so there's actually another custom type you need to put in here but I've kept this simple by way of an explanation. The important thing is, **don't adjust your supabase types**, use them as a starting point for your own types.

This approach can also help with RLS rules because our function on the view is setup with **SECURITY DEFINER**. For more on this read our post [How to implement RLS for a team invite system with Supabase](https://boardshape.com/engineering/how-to-implement-rls-for-a-team-invite-system-with-supabase).

Working with Supabase and Postgres is so enjoyable, we hope this gives you some ideas of ways to adjust your traditional approach of pulling back database records.

***Please [follow us on twitter](https://twitter.com/boardshape) for updates on when we post new engineering content and [give BoardShape a try](https://boardshape.com/dashboard#signup) if you're interested in running better organized board meetings.***

small_teams-and-members.png

thumbnail_teams-and-members.png

xsmall_teams-and-members.png

Supabase and Postgres combined with RLS present opportunities to easily return multi-dimensional data giving you a way to speed up and simplify your app code.

Speed up your app by returning multi-dimensional data with Supabase

I thought I knew enough about RLS but I made a few dangerous assumptions about it that turned out not to be true. **RLS works great until you need to restrict anonymous access to a table.** An invite system is a perfect example of this and I have used this to demonstrate the problem and provide a potential solution. This pattern can be applied to other similar situations that will inevitably crop up in your own app.

Let’s start with an important statement:

**If you use Supabase JS on your client-side and you don’t use RLS then your database is open to the world.**

Selects, inserts, updates, deletes. The whole thing.

So, you need RLS. But, it’s limited… or rather, annoying and non obvious.

Normally with anything Postgres, there are reams of docs, but with RLS there are only really two relevant doc pages [here](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) and [here](https://www.postgresql.org/docs/current/sql-createpolicy.html). [Supabase have their own page](https://supabase.com/docs/guides/auth/row-level-security), and it looks nice, but it doesn’t really say anything that isn’t on the official docs other than how to use **auth.uid()** which is easy and pretty much essential.

## A simple use case: Team Invites

So, let’s get down to business. We have an invite system for inviting fellow board members that uses a simple table defined like this:


```sql
CREATE TABLE public.team_invites (
 id uuid NOT NULL DEFAULT uuid_generate_v4(),
 team_id uuid NOT NULL,
 inviter_id uuid NULL,
 first_name varchar NOT NULL,
 last_name varchar NULL,
 email varchar NULL,
 created_at timestamptz NULL DEFAULT now(),
 CONSTRAINT team_invites_pkey PRIMARY KEY (id)
);
```

Now, using RLS I want to restrict who has access to this. The requirement is:

- The intended recipient of the invite should be able to return the details in order to consume the invite.
- Team members (specifically team owners) should be able to return details or insert/update new invites.

Now, of these two requirements, the latter sounds more complicated. However, this is entirely what RLS is designed to do. Take the context of the calling user, determine if they have access and permit or deny access to the data. The rule is simple and looks like this:

```sql
create policy "Team owners can select and update invites"
  on team_invites
  for all using (
    team_id in (
      select get_teams_with_owner_privilege_for_authenticated_user()
    )
  );
```

With a supporting function:

```js
get_teams_with_owner_privilege_for_authenticated_user()
```

— _what a name_ 😂 defined as:

```sql
create or replace function get_teams_with_owner_privilege_for_authenticated_user()
returns setof uuid
language sql
security definer
set search_path = public
stable
as $$
    select team_id
    from team_members
    where user_id = auth.uid() and user_level = 1
$$;
```

So, with this rule in place, currently the table can only be read and updated by users that are part of the same team as the invite. At this point, the invite system is broken, we can’t access an invite as an anonymous user.

This is the problem, an anonymous user has **no credentials to secure the query with**, the only solution it seems is to give blanket SELECT * access to team_invites. This would mean anyone can adjust my query on the front end and pull back a complete list with every invite in the database, pull out an invite and then join a team they shouldn’t! 💥

I thought with RLS I’d be able to do the equivalent of following:

```sql
create policy "Anonymous users can select a specific team invite by ID"
  on team_invites
  for select using (
    id = id_of_query_parameter
  );
```

Where **id_of_query_parameter** (or some relevant syntax) would be the actual incoming query. This would work nice, because my IDs are UUID so not guessable — you wouldn’t be able to access any invite without knowing the ID. **However, you can not access incoming query parameters with RLS.**

## Functions to the rescue

The key to this working is using UUIDs as you primary keys. I would go as far as to say:

**RLS will not work for all authentication requirements on your Supabase site without the use of UUIDs.**

So, the solution is a function that can skip RLS:

```sql
CREATE OR REPLACE FUNCTION public.get_invite(invite_id uuid)
 RETURNS SETOF team_invites
 LANGUAGE sql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  select * from team_invites where id = invite_id;
$function$
;
```

This is a simple function, it takes an **invite_id** (which is a UUID) and returns any team_invites that match. Of course, this will either be zero or one. It will never be more than that.

The important part of this function is **SECURITY DEFINER** and this is mentioned in the [Supabase docs about calling functions](https://supabase.com/docs/guides/database/functions#returning-data-sets). It essentially means that the function is called with the security context of the user creating the function not the user calling the function and, as such, this means the **RLS rules will be skipped**.

All that is left to do is to replace our code on the front-end that selects the invite with our function call.

So this:

```js
const { data, error } = await supabase
    .from('team_invites')
    .select()
    .eq('id', inviteId)
    .single()
```

Becomes this:

```js
const { data, error } = 
  await supabase.rpc('get_invite', { invite_id: id }).single()
```

Pretty clean. Now, everything works, and there is no way to select anything from the database you shouldn’t be able to. 🥳

I hope this helps someone. I deliberately filled this post with some of the things I was trying to google without any luck.

_**We are building the best online board management solution for small businesses and startups. Want to run your board meetings better? [Sign up for FREE and try us out.](https://boardshape.com/dashboard#signup)**_

small_lumberg.webp

thumbnail_lumberg.webp

xsmall_lumberg.webp

RLS is a great solution for securing queries in your Supabase app but it's not always plain sailing. Securing queries where you have no user context can be tricky. Read out post to find out how you can use postgres functions to get around this issue.

How to implement RLS for a team invite system with Supabase

With [BoardShape](https://boardshape.com) we wanted a seamless experience from public facing site to back-end site. So, how could we build a front-end and have content control to build a blog?

Enter, stage left, [Strapi](https://strapi.io/).

[Strapi](https://strapi.io/) is a great open-source headless content management system. What is headless content management? Well, simply put, it's all the back-end administration controls to manage content but none of the front-end. All the content is made available via API.

Within moments of having Strapi running on a local development machine, we had a content model for our blog. It looks like this:

![The Strapi Content Editor](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/strapi.png-ee1b425af7d93cb3bb4dd52c0583d073.png)

You add your fields, you add the field type and hey-presto.

So, the only thing that stumped us initially is, how can we get reading time? We wanted to add this to our blog so it would appear like this:

![10 tips for running a successful board meeting](https://pedeywbqjyubfaejkuek.supabase.co/storage/v1/object/public/site-content/how-to-run-a-board-meeting.png-5db18326945720e4211667f440d42401.png)

Strapi doesn't support this out of the box, but it's REALLY extensible. You can just write some code to do what you need. In this case, a lifecycle hook. Here is what the code looks like:


```js
const readingTime = require('reading-time');

module.exports = {
  beforeCreate(event) {
    const { data } = event.params;
    if (event.params.data.body) {
      data.reading_time = readingTime(event.params.data.body);
    }
  },
  beforeUpdate(event) {
    const { data } = event.params;
    if (event.params.data.body) {
      data.reading_time = readingTime(event.params.data.body);
    }
  },
};
```
It uses a small NPM module called [reading-time](https://www.npmjs.com/package/reading-time) and whenever an article is saved, it grabs the reading time for the body field. Really simple, and then in our saved content data we end up with:


```js
{
"text":"3 min read",
"time":126900,
"words":423,
"minutes":2.115
}
```

This is saved into a JSON field called reading_time.

Once we had this, we configured Strapi to work with Supabase (more on this later). 

By connecting direct to Supabase, it means we can just pull the content straight out of the database. Strapi has a great API to do this, but it keeps everything clean and simple to just pull it from the database. We'll detail more on this in a future post along with how to get it working with NextJS.

Please follow us on [Twitter](https://twitter.com/boardshape), reach out to us if you want to know more, and, if you have a small business or organisation and want to run better board meetings, [try BoardShape](https://boardshape.com/dashboard#signup) 🤟

medium_strapi.png

small_strapi.png

thumbnail_strapi.png

xsmall_strapi.png

Strapi is a great fit for your NextJS project. It's a headless CMS that is easy to customize and get working with Supabase using Postgres as the back-end. This blog posts goes in to a bit of detail about how we used Strapi and how we extended it to add reading times to our blog.

Engineering at BoardShape

Get your Board Management in Shape