Create a free account at supabase.com and start a new project. Wait ~1 min for it to provision.
2
Go to SQL Editor in the left sidebar and run this script. It creates all tables, RLS security, and a default admin login:
-- PINNS Hours Tracker - Full Database Setup (Security Hardened)
CREATE TABLE employees (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
name TEXT NOT NULL,
username TEXT UNIQUE NOT NULL,
pin TEXT NOT NULL,
is_admin BOOLEAN DEFAULT false,
is_archived BOOLEAN DEFAULT false,
has_unpaid_break BOOLEAN DEFAULT false,
fob_uid TEXT UNIQUE,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Existing database? Add the key fob column with:
-- ALTER TABLE employees ADD COLUMN IF NOT EXISTS fob_uid TEXT UNIQUE;
CREATE TABLE time_entries (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
employee_id UUID REFERENCES employees(id) ON DELETE CASCADE,
entry_date DATE NOT NULL,
start_time TIME,
end_time TIME,
break_start TIME,
break_end TIME,
notes TEXT,
is_clocked_in BOOLEAN DEFAULT false,
is_on_break BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now(),
UNIQUE(employee_id, entry_date)
);
CREATE TABLE audit_log (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
employee_id UUID REFERENCES employees(id) ON DELETE SET NULL,
employee_name TEXT NOT NULL,
action TEXT NOT NULL,
detail TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
-- ══════════════════════════════════════════════════════════════════════
-- PINNS Hours Tracker: database lock-down (run once in Supabase, SQL Editor)
-- Safe to run again. Nothing works without a signed-in session afterwards,
-- so update the app file at the same time.
-- ══════════════════════════════════════════════════════════════════════
create extension if not exists pgcrypto;
-- 1. Sessions: a signed-in device holds a token and sends it as the x-session header
create table if not exists sessions (
token text primary key,
employee_id uuid not null references employees(id) on delete cascade,
created_at timestamptz default now(),
last_seen timestamptz default now(),
expires_at timestamptz not null
);
alter table sessions enable row level security;
-- (no policies on sessions: only the functions below can read or write it)
create table if not exists login_attempts (username text not null, at timestamptz default now());
create index if not exists login_attempts_idx on login_attempts (username, at);
alter table login_attempts enable row level security;
-- 2. Who is calling? Reads the x-session header that PostgREST passes through
create or replace function session_token() returns text
language sql stable as $$
select nullif(current_setting('request.headers', true)::json->>'x-session', '');
$$;
create or replace function cur_emp_id() returns uuid
language sql stable security definer set search_path = public, extensions as $$
select s.employee_id from sessions s join employees e on e.id = s.employee_id
where s.token = session_token() and s.expires_at > now() and not e.is_archived
limit 1;
$$;
create or replace function cur_is_admin() returns boolean
language sql stable security definer set search_path = public, extensions as $$
select coalesce((select e.is_admin from sessions s join employees e on e.id = s.employee_id
where s.token = session_token() and s.expires_at > now() and not e.is_archived limit 1), false);
$$;
-- 3. Sign in, sign out, who am I, PIN check, server clock
create or replace function login(p_username text, p_pin text) returns json
language plpgsql security definer set search_path = public, extensions as $$
declare v_emp employees; v_token text; v_fails int;
begin
select count(*) into v_fails from login_attempts
where username = lower(p_username) and at > now() - interval '1 minute';
if v_fails >= 5 then raise exception 'Too many attempts. Wait a minute and try again.'; end if;
select * into v_emp from employees
where username = lower(p_username) and not is_archived
and pin = encode(digest(p_pin, 'sha256'), 'hex');
if v_emp.id is null then
insert into login_attempts (username) values (lower(p_username));
raise exception 'Incorrect username or PIN.';
end if;
delete from login_attempts where username = lower(p_username);
v_token := encode(gen_random_bytes(32), 'hex');
insert into sessions (token, employee_id, expires_at) values (v_token, v_emp.id, now() + interval '30 days');
return json_build_object('token', v_token, 'employee', json_build_object(
'id', v_emp.id, 'name', v_emp.name, 'username', v_emp.username, 'is_admin', v_emp.is_admin,
'has_unpaid_break', v_emp.has_unpaid_break, 'fob_uid', v_emp.fob_uid));
end $$;
create or replace function whoami() returns json
language plpgsql security definer set search_path = public, extensions as $$
declare v_emp employees;
begin
select e.* into v_emp from employees e where e.id = cur_emp_id();
if v_emp.id is null then return null; end if;
update sessions set last_seen = now(), expires_at = now() + interval '30 days' where token = session_token();
return json_build_object(
'id', v_emp.id, 'name', v_emp.name, 'username', v_emp.username, 'is_admin', v_emp.is_admin,
'has_unpaid_break', v_emp.has_unpaid_break, 'fob_uid', v_emp.fob_uid);
end $$;
create or replace function logout() returns void
language sql security definer set search_path = public, extensions as $$
delete from sessions where token = session_token();
$$;
create or replace function check_pin(p_pin text) returns boolean
language sql stable security definer set search_path = public, extensions as $$
select exists (select 1 from employees where id = cur_emp_id() and pin = encode(digest(p_pin, 'sha256'), 'hex'));
$$;
create or replace function clock_now() returns json
language sql stable as $$
select json_build_object(
'date', to_char(now() at time zone 'Europe/London', 'YYYY-MM-DD'),
'time', to_char(now() at time zone 'Europe/London', 'HH24:MI'));
$$;
grant execute on function login(text, text), whoami(), logout(), check_pin(text), clock_now() to anon;
-- 4. Row level policies: nothing without a session, staff see their own hours, admins see everything
alter table employees enable row level security;
alter table time_entries enable row level security;
alter table audit_log enable row level security;
-- Remove every existing policy on these tables, whatever it was called, so no old open policy survives
do $$ declare p record;
begin
for p in select policyname, tablename from pg_policies where schemaname = 'public' and tablename in ('employees','time_entries','audit_log','sessions','login_attempts') loop
execute format('drop policy if exists %I on %I', p.policyname, p.tablename);
end loop;
end $$;
drop policy if exists employees_select on employees;
drop policy if exists employees_insert on employees;
drop policy if exists employees_update on employees;
drop policy if exists time_entries_all on time_entries;
drop policy if exists time_entries_select on time_entries;
drop policy if exists time_entries_insert on time_entries;
drop policy if exists time_entries_update on time_entries;
drop policy if exists time_entries_delete on time_entries;
drop policy if exists audit_log_all on audit_log;
drop policy if exists audit_insert on audit_log;
drop policy if exists audit_select on audit_log;
create policy employees_select on employees for select to anon using (cur_emp_id() is not null);
create policy employees_insert on employees for insert to anon with check (cur_is_admin());
create policy employees_update on employees for update to anon using (cur_is_admin()) with check (cur_is_admin());
create policy time_entries_select on time_entries for select to anon using (employee_id = cur_emp_id() or cur_is_admin());
create policy time_entries_insert on time_entries for insert to anon with check (employee_id = cur_emp_id() or cur_is_admin());
create policy time_entries_update on time_entries for update to anon using (employee_id = cur_emp_id() or cur_is_admin()) with check (employee_id = cur_emp_id() or cur_is_admin());
create policy time_entries_delete on time_entries for delete to anon using (employee_id = cur_emp_id() or cur_is_admin());
create policy audit_insert on audit_log for insert to anon with check (cur_emp_id() is not null);
create policy audit_select on audit_log for select to anon using (cur_is_admin());
-- 5. PIN hashes never leave the database again
revoke select on employees from anon;
grant select (id, name, username, is_admin, is_archived, has_unpaid_break, fob_uid, created_at) on employees to anon;
-- Default admin: username=admin, PIN=1234 (SHA-256 hashed)
-- ⚠️ CHANGE THE ADMIN PIN IMMEDIATELY after first login via Manage Staff
-- SHA-256 of "1234" = 03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4
INSERT INTO employees (name, username, pin, is_admin)
VALUES ('Admin', 'admin', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', true);
-- ═══════════════════════════════════════════
-- MIGRATION: If upgrading from plain-text PINs
-- Run this ONCE to hash existing PINs, then delete it
-- ═══════════════════════════════════════════
-- UPDATE employees SET pin = encode(digest(pin, 'sha256'), 'hex');
-- (Requires pgcrypto extension: CREATE EXTENSION IF NOT EXISTS pgcrypto;)
3
Go to Project Settings → API, copy your Project URL and anon public key, paste below.
Hours Tracker
Sign in with your username and PIN.
Incorrect username or PIN.
-
-
Key Fob Station
Hours Tracker
-
My Hours
-
Not Started
-
Log your hours for today
Week
-
Total This Week
0.00 hrs
Days Logged
0/7
Daily Avg
-
My History
All logged hours
All Time
-
This Month
-
Days Worked
-
All Entries
All Staff Hours
Admin overview
Staff SummaryClick a name to see each day, then click a day to amend it
Export Hours
Select data to include
What to Include
Preview
Audit Log
All activity: who did what and when
Activity (last 200)
Manage Staff
Add, edit, archive employees
Edit Staff Hours
Add or amend hours for any employee
Employee
-
Week Total
0.00 hrs
Days Logged
0
Daily Avg
-
Select an employee above to view their week ✨
Log Hours
-
End time must be after start time.
Quick Fill
Now → Start
-
Now → End
-
Both → Now
-
🕐
🕐
🕐
🕐
Paid Hours (excl. break)-
Add Employee
Username already taken.
30 min break is auto-deducted every day they clock out (fob or on screen). Manual entries auto-fill 12:00–12:30.
