RLS policy one vote per user per question

Question

I am struggling with creating an RLS policy that makes sure, every user can vote just once per question (in my case, a question is a bill).

The vote table lists users and bills. Every user can vote for every bill once:

| id | user_id | bill_id | vote       |
| -- | ------- | ------- | ---------- |
| 1  | 25      | 12      | yes        |
| 2  | 38      | 12      | yes        |
| 3  | 12      | 12      | no         |
| 4  | 62      | 8       | abstention |

I tried following policy where error Error updating policy: failed to update pg.policies with the given ID: missing FROM-clause entry for table "new" is thrown.

CREATE POLICY vote_once_policy ON vote
  FOR INSERT
  WITH CHECK ((
    SELECT COUNT(*) FROM vote
    WHERE bill_id = NEW.bill_id AND user_id = NEW.user_id
  ) = 0);

Any idea what I am doing wrong? Or is there another way to ensure that each user can only vote once per bill?

Thank you in advance for your appreciated feedback.

Supabase RLS Policy editor:

Supabase vote table:

Answer 1

As mentioned in the comments of the question, RLS can't enforce uniqueness, only a unique constraint can. The query that counts the number of records can only see committed records, nothing that is in flight. (thanks to @frank-heikens and @bergi)

The constraint could looks as follows:

ALTER TABLE vote ADD CONSTRAINT unique_vote_per_user_per_bill UNIQUE (bill_id, user_id);