0

I'm quite new to Supabase and PostgreSQL and have stumbled upon a question. If I'm creating a Todo app where people should be able to sign in and depending on subscription type (regular users or pro accounts) the total number of todos should be restricted. Let's say that regular users should be able to post 20 todos but pro users should be unlimited.

Here is the table I'm using:

account table

  • id(uuid)
  • subscriptionType(users/pro)
  • toDoCount (number. Totalt number of todos)

todo table

  • id
  • userId
  • title
  • description

But how would I write policies that allow inserts only if 1. User is authenticated 2. toDoCount limit is not exceeded.

I'm creating the app with Ionic/angular.

Grateful for answers

crushen
  • 78
  • 10

1 Answers1

1

I think you are looking at the problem wrong, perhaps you should validate if the user can add a new to-do record before you actually send the request

Pseudocode example:

if user.number_of_todos >= 20 and user.subscription_type != 'pro':
    handle_insufficient_plan_error()
else:
    add_new_todo()

I think in the near future it will be supported by Supabase functions ( if you don't want client-side validations)

Alternatively, you can use a more complicated query to ensure that the user can not add todos

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO todo_table (user_id, title, description) 
VALUES ('123', 'call mom', 'call my mom')
WHERE
    (
        SELECT COUNT(id) FROM todo_table WHERE user_id = '123'
    ) < 5;
END;

Source: Postgres insert row only if row count is under a limit

Hoped I helped :)

Alon Barad
  • 1,491
  • 1
  • 13
  • 26
  • 1
    Yes, I may think about it the wrong way, so thanks for answering :). I could have mentioned that I disable the add new button and the possibility to send the request to Supabase in my Ionic app. But thought it would be a good idea to secure it backend as well in some way – crushen Jul 09 '21 at 18:28
  • your "Alternative query" example doesn't include he clause about the user's `subscription_type` – Technolo Jesus Jul 09 '23 at 18:14