Database Schema

The schema is defined in supabase/schema.sql. Run it once in the Supabase SQL editor when setting up a new project.

Tables

public.users

Extends Supabase's built-in auth.users with a public profile row. Created automatically by a trigger when a user registers.

Column	Type	Description
id	UUID (PK)	References auth.users.id. Cascade deletes.
email	TEXT	User's email address
registration_timestamp	BIGINT	Unix timestamp (ms) of registration
created_at	TIMESTAMPTZ	Row creation time
updated_at	TIMESTAMPTZ	Last update time

public.login_events

Append-only log of every successful login.

Column	Type	Description
id	UUID (PK)	Auto-generated
user_id	UUID	References auth.users.id. Cascade deletes.
timestamp	BIGINT	Unix timestamp (ms) of the login
created_at	TIMESTAMPTZ	Row creation time

Row Level Security

Both tables have RLS enabled. Users can only read and write their own rows.

Table	Policy	Rule
users	View own profile	auth.uid() = id
users	Update own profile	auth.uid() = id
users	Insert own profile	auth.uid() = id
login_events	View own events	auth.uid() = user_id
login_events	Insert own events	auth.uid() = user_id

Functions

handle_new_user()

A trigger function that automatically creates a public.users row when a new user is created in auth.users.

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

get_user_count() → INTEGER

Returns the total number of registered users. Callable by anonymous and authenticated roles. Used by setupLiveMetrics() in supabase-auth.js for real-time metrics subscriptions.

get_login_events_count() → INTEGER

Returns the total number of login events recorded. Used alongside get_user_count() for platform metrics.

Running the Schema

In the Supabase dashboard:

1. Go to SQL Editor
2. Paste the contents of supabase/schema.sql
3. Click Run

Re-running is safe — all CREATE TABLE and CREATE POLICY statements use IF NOT EXISTS or CREATE OR REPLACE.