Add a Points System to Your Users Table

Aug 19, 2025

Add a points or credits system to your Supabase users table. Copy-paste SQL to create, update, and query points—perfect for loyalty programs, games, or leaderboards.

DevKit

Supabase

SQL

Foundation

Points are everywhere—loyalty apps, games, rewards. Let’s extend your `users` table with a `points` column so you can start tracking scores and rewarding activity.

Think about your favourite apps: coffee shops giving you rewards, games tracking your score, or social apps rewarding referrals. All of these are just points systems under the hood.

In databases, a points system is nothing fancy—it’s just a number column in a table. But once you have it, you can:

Reward users for actions (purchases, invites, posts).
Build simple leaderboards.
Track loyalty over time.

The trick is to set it up safely so every new user starts at zero and never has a “null” value. By the end of this lesson, you’ll be able to add points to any user and fetch a leaderboard—all with just a few lines of SQL.

Implementation

Add a points column, test it with inserts, update values, and run a simple leaderboard query.

1) Add the points column

Open your Supabase SQL editor and run:

ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS points INTEGER NOT NULL DEFAULT 0;

INTEGER → stores whole numbers.
NOT NULL → column can’t be empty.
DEFAULT 0 → every new user starts with 0 points.

2) Insert test users with points

Let’s add a few rows (if you don’t already have data):

INSERT INTO public.users (name, email, points)
VALUES
  ('Alice Example', 'alice@example.com', 50),
  ('Bob Builder',   'bob@example.com',   120),
  ('Charlie Test',  'charlie@example.com', 10);

3) Update points for a user

Give Alice 20 more points:

UPDATE public.users
SET points = points + 20
WHERE email = 'alice@example.com';

Now Alice has 70 points.

You can also subtract points the same way (points - 20).

4) Fetch a simple leaderboard

Show top users by points (highest first):

SELECT name, email, points
FROM public.users
ORDER BY points DESC
LIMIT 10;

This gives you a top 10 leaderboard.

5) Reset everyone’s points (careful ⚠️)

If you ever need to start over:

UPDATE public.users
SET points = 0;

Optimization

Indexes, safety checks, and next steps for making your points system scalable.

1) Add an index (faster leaderboards)

If your app grows and you have thousands of users, sorting by points will get slower. Add an index:

CREATE INDEX IF NOT EXISTS idx_users_points ON public.users (points DESC);

This makes ORDER BY points DESC queries much faster.

2) Prevent negative points (optional constraint)

If your logic should never allow negative points, add a check:

ALTER TABLE public.users
ADD CONSTRAINT points_nonnegative CHECK (points >= 0);

Now, if someone tries UPDATE users SET points = -50, Postgres will block it.

3) Wrap updates in a transaction (advanced tip)

If you’re adding points based on actions (like purchases), use transactions to avoid race conditions. Example in pseudo-SQL:

BEGIN;
UPDATE public.users
SET points = points + 10
WHERE id = 'user-uuid-here';
COMMIT;

Supabase handles this for single updates, but it’s good practice when combining multiple queries.

4) Teaser: Moving logic into RPCs

For now, we’re updating points directly with SQL. Later in DevKit we’ll show how to wrap this into a Supabase RPC (add_points(user_id, amount)) so your frontend can call it securely in one line.

You now have a working points system! Every new user starts at zero, you can safely add or subtract points, and you can rank them in a leaderboard.

In Article #3 we’ll create a posts table and connect it to users—your first real relational table.

Latest Updates

(GQ® — 02)

Create Your First Table in Supabase

Aug 16, 2025

DevKit

FAQ

01

What’s the difference between a template and a subscription?

02

Do I need coding skills to use your templates?

03

Can you work on my own app, not just your templates?

04

How many revisions are included?

05

Are template purchases refundable?success?

06

Can I cancel my subscription anytime?

07

What happens to my project if I cancel the subscription?

08

Do you handle App Store & Google Play submission?

Aug 19, 2025

Add a Points System to Your Users Table

Add a points or credits system to your Supabase users table. Copy-paste SQL to create, update, and query points—perfect for loyalty programs, games, or leaderboards.

DevKit

Supabase

SQL

Foundation

Points are everywhere—loyalty apps, games, rewards. Let’s extend your `users` table with a `points` column so you can start tracking scores and rewarding activity.

Think about your favourite apps: coffee shops giving you rewards, games tracking your score, or social apps rewarding referrals. All of these are just points systems under the hood.

In databases, a points system is nothing fancy—it’s just a number column in a table. But once you have it, you can:

Reward users for actions (purchases, invites, posts).
Build simple leaderboards.
Track loyalty over time.

The trick is to set it up safely so every new user starts at zero and never has a “null” value. By the end of this lesson, you’ll be able to add points to any user and fetch a leaderboard—all with just a few lines of SQL.

Implementation

Add a points column, test it with inserts, update values, and run a simple leaderboard query.

1) Add the points column

Open your Supabase SQL editor and run:

ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS points INTEGER NOT NULL DEFAULT 0;

INTEGER → stores whole numbers.
NOT NULL → column can’t be empty.
DEFAULT 0 → every new user starts with 0 points.

2) Insert test users with points

Let’s add a few rows (if you don’t already have data):

INSERT INTO public.users (name, email, points)
VALUES
  ('Alice Example', 'alice@example.com', 50),
  ('Bob Builder',   'bob@example.com',   120),
  ('Charlie Test',  'charlie@example.com', 10);

3) Update points for a user

Give Alice 20 more points:

UPDATE public.users
SET points = points + 20
WHERE email = 'alice@example.com';

Now Alice has 70 points.

You can also subtract points the same way (points - 20).

4) Fetch a simple leaderboard

Show top users by points (highest first):

SELECT name, email, points
FROM public.users
ORDER BY points DESC
LIMIT 10;

This gives you a top 10 leaderboard.

5) Reset everyone’s points (careful ⚠️)

If you ever need to start over:

UPDATE public.users
SET points = 0;

Optimization

Indexes, safety checks, and next steps for making your points system scalable.

1) Add an index (faster leaderboards)

If your app grows and you have thousands of users, sorting by points will get slower. Add an index:

CREATE INDEX IF NOT EXISTS idx_users_points ON public.users (points DESC);

This makes ORDER BY points DESC queries much faster.

2) Prevent negative points (optional constraint)

If your logic should never allow negative points, add a check:

ALTER TABLE public.users
ADD CONSTRAINT points_nonnegative CHECK (points >= 0);

Now, if someone tries UPDATE users SET points = -50, Postgres will block it.

3) Wrap updates in a transaction (advanced tip)

If you’re adding points based on actions (like purchases), use transactions to avoid race conditions. Example in pseudo-SQL:

BEGIN;
UPDATE public.users
SET points = points + 10
WHERE id = 'user-uuid-here';
COMMIT;

Supabase handles this for single updates, but it’s good practice when combining multiple queries.

4) Teaser: Moving logic into RPCs

For now, we’re updating points directly with SQL. Later in DevKit we’ll show how to wrap this into a Supabase RPC (add_points(user_id, amount)) so your frontend can call it securely in one line.

You now have a working points system! Every new user starts at zero, you can safely add or subtract points, and you can rank them in a leaderboard.

In Article #3 we’ll create a posts table and connect it to users—your first real relational table.

Latest Updates

(GQ® — 02)

Create Your First Table in Supabase

Aug 16, 2025

DevKit

FAQ

01

What’s the difference between a template and a subscription?

02

Do I need coding skills to use your templates?

03

Can you work on my own app, not just your templates?

04

How many revisions are included?

05

Are template purchases refundable?success?

06

Can I cancel my subscription anytime?

07

What happens to my project if I cancel the subscription?

08

Do you handle App Store & Google Play submission?

Aug 19, 2025

Add a Points System to Your Users Table

Add a points or credits system to your Supabase users table. Copy-paste SQL to create, update, and query points—perfect for loyalty programs, games, or leaderboards.

DevKit

Supabase

SQL

Foundation

Points are everywhere—loyalty apps, games, rewards. Let’s extend your `users` table with a `points` column so you can start tracking scores and rewarding activity.

Think about your favourite apps: coffee shops giving you rewards, games tracking your score, or social apps rewarding referrals. All of these are just points systems under the hood.

In databases, a points system is nothing fancy—it’s just a number column in a table. But once you have it, you can:

Reward users for actions (purchases, invites, posts).
Build simple leaderboards.
Track loyalty over time.

The trick is to set it up safely so every new user starts at zero and never has a “null” value. By the end of this lesson, you’ll be able to add points to any user and fetch a leaderboard—all with just a few lines of SQL.

Implementation

Add a points column, test it with inserts, update values, and run a simple leaderboard query.

1) Add the points column

Open your Supabase SQL editor and run:

ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS points INTEGER NOT NULL DEFAULT 0;

INTEGER → stores whole numbers.
NOT NULL → column can’t be empty.
DEFAULT 0 → every new user starts with 0 points.

2) Insert test users with points

Let’s add a few rows (if you don’t already have data):

INSERT INTO public.users (name, email, points)
VALUES
  ('Alice Example', 'alice@example.com', 50),
  ('Bob Builder',   'bob@example.com',   120),
  ('Charlie Test',  'charlie@example.com', 10);

3) Update points for a user

Give Alice 20 more points:

UPDATE public.users
SET points = points + 20
WHERE email = 'alice@example.com';

Now Alice has 70 points.

You can also subtract points the same way (points - 20).

4) Fetch a simple leaderboard

Show top users by points (highest first):

SELECT name, email, points
FROM public.users
ORDER BY points DESC
LIMIT 10;

This gives you a top 10 leaderboard.

5) Reset everyone’s points (careful ⚠️)

If you ever need to start over:

UPDATE public.users
SET points = 0;

Optimization

Indexes, safety checks, and next steps for making your points system scalable.

1) Add an index (faster leaderboards)

If your app grows and you have thousands of users, sorting by points will get slower. Add an index:

CREATE INDEX IF NOT EXISTS idx_users_points ON public.users (points DESC);

This makes ORDER BY points DESC queries much faster.

2) Prevent negative points (optional constraint)

If your logic should never allow negative points, add a check:

ALTER TABLE public.users
ADD CONSTRAINT points_nonnegative CHECK (points >= 0);

Now, if someone tries UPDATE users SET points = -50, Postgres will block it.

3) Wrap updates in a transaction (advanced tip)

If you’re adding points based on actions (like purchases), use transactions to avoid race conditions. Example in pseudo-SQL:

BEGIN;
UPDATE public.users
SET points = points + 10
WHERE id = 'user-uuid-here';
COMMIT;

Supabase handles this for single updates, but it’s good practice when combining multiple queries.

4) Teaser: Moving logic into RPCs

For now, we’re updating points directly with SQL. Later in DevKit we’ll show how to wrap this into a Supabase RPC (add_points(user_id, amount)) so your frontend can call it securely in one line.

You now have a working points system! Every new user starts at zero, you can safely add or subtract points, and you can rank them in a leaderboard.

In Article #3 we’ll create a posts table and connect it to users—your first real relational table.

Latest Updates

Create Your First Table in Supabase

Aug 16, 2025

DevKit

FAQ

What’s the difference between a template and a subscription?

Do I need coding skills to use your templates?

Can you work on my own app, not just your templates?

How many revisions are included?

Are template purchases refundable?success?

Can I cancel my subscription anytime?

What happens to my project if I cancel the subscription?

Do you handle App Store & Google Play submission?

Add a Points System to Your Users Table

Points are everywhere—loyalty apps, games, rewards. Let’s extend your users table with a points column so you can start tracking scores and rewarding activity.

1) Add the points column

2) Insert test users with points

3) Update points for a user

4) Fetch a simple leaderboard

5) Reset everyone’s points (careful ⚠️)

1) Add an index (faster leaderboards)

2) Prevent negative points (optional constraint)

3) Wrap updates in a transaction (advanced tip)

4) Teaser: Moving logic into RPCs

Add a Points System to Your Users Table

Points are everywhere—loyalty apps, games, rewards. Let’s extend your users table with a points column so you can start tracking scores and rewarding activity.

1) Add the points column

2) Insert test users with points

3) Update points for a user

4) Fetch a simple leaderboard

5) Reset everyone’s points (careful ⚠️)

1) Add an index (faster leaderboards)

2) Prevent negative points (optional constraint)

3) Wrap updates in a transaction (advanced tip)

4) Teaser: Moving logic into RPCs

Add a Points System to Your Users Table

Points are everywhere—loyalty apps, games, rewards. Let’s extend your users table with a points column so you can start tracking scores and rewarding activity.

1) Add the points column

2) Insert test users with points

3) Update points for a user

4) Fetch a simple leaderboard

5) Reset everyone’s points (careful ⚠️)

1) Add an index (faster leaderboards)

2) Prevent negative points (optional constraint)

3) Wrap updates in a transaction (advanced tip)

4) Teaser: Moving logic into RPCs

Points are everywhere—loyalty apps, games, rewards. Let’s extend your `users` table with a `points` column so you can start tracking scores and rewarding activity.

Points are everywhere—loyalty apps, games, rewards. Let’s extend your `users` table with a `points` column so you can start tracking scores and rewarding activity.

Points are everywhere—loyalty apps, games, rewards. Let’s extend your `users` table with a `points` column so you can start tracking scores and rewarding activity.