SvelteKit SSR user auth PostgreSQL RLS skeleton

I hope to use this skeleton as a foundation to integrate user authentication, with workspace/groupspace support in SvelteKit web app.

Status : I think it's stable, but it hasn't been tested in production.

Screencast project presentation (audio in french)

This project is a spin-off of sveltekit-user-auth-skeleton.

Repository starting point issue (in French): https://github.com/stephane-klein/sveltekit-user-auth-skeleton/issues/7

In a real project, the tables main.resource_a and main.resource_b should be replaced by products, issues, customers, invoices… entities.

Features:

• User login
• User signup
• User signup by invitation
• User password reset support
• User impersonate support for staff user
• User role support
• Space resource support, to implement workspace or groupspace, multitenancy
• A user can belong to one or more spaces
• A space can belong to another space
• Permission control implemented in PostgreSQL using Row-Level Security
• The data model has been designed with performance in mind, i.e. the POLICY query plans have been reviewed to ensure that the best indexes are used (I'm not claiming that everything is perfect at the moment, but this is one of the main objectives of this project)
• Audit events support

Next tasks on the roadmap:

• Improve unit tests
• Implement end2end tests

Opinions:

• No ORM pattern
• The security layer, i.e. permission control is implemented by PostgreSQL Row-Level Security
• impersonate_user_id is stored in auth.sessions table (this can be challenged)
• I'm trying to move towards Radical Simplicity
• Don’t Build A General Purpose API To Power Your Own Front End
• The page rendering spped, and therefore the execution speed of SQL queries, needs special attention

Components and libraries:

• ✅ SSR SvelteKit with Hydration
• ✅ PostgreSQL database server
• ✅ Postgres.js - PostgreSQL client for Node.js
• ✅ Migration powered by graphile-migrate
• ✅ Token generated with jsonwebtoken
• ✅PostgreSQL Row-Level Security

Tooling:

• ✅ asdf
• ✅ Docker and Docker Compose
• ✅ NodeJS
• ✅ pnpm
• ✅ Jest for unittest

Development time and costs

The 2023-09-21, until commit 3db763a, I spent 56 hours and 20 mintes on this project (The time spent on sveltekit-user-auth-skeleton project has been included in this time (15 hours and 28 minutes)).
This time was measured with a chronometer, which is deep work.
I consider that a "normal" working day corresponds to a maximum of 4 hours of deep work.

This work would correspond to the following price:

• For a French developer on a permanent contract at 65 K€ gross per year: 6555 € (total cost paid by the employer)
• For a freelance developer at 600 € per day: 9000 € (total cost invoiced by the freelance)

$ python
>>> import math
>>> french_developer_on_a_permanent_contract = math.ceil(56.5/4) * 437
>>> french_developer_on_a_permanent_contract
1748
>>> freelance = math.ceil(56.5/4) * 600
>>> freelance
2400
>>>

Getting started

$ asdf install

$ pnpm install

Start database engine:

$ ./scripts/init.sh
$ ./load-fixtures.js

Start web server:

$ pnpm run dev

Go to http://localhost:5173/

Valid logins

• email: john.doe1@example.com password: secret1
• email: john.doe2@example.com password: secret2
• email: john.doe3@example.com password: secret3

Create new user with:

$ pnpm run user create --email=john.doe4@example.com --username=john-doe4 --password=password --firstname=John --lastname=Doe

Maildev

You can access to Maildev on http://localhost:1080

Database migration

$ pnpm run migrate:watch

Apply migration in migrations/current.sql and commit:

$ pnpm run migrate:commit

Execute Unittest

$ pnpm run migrate-test:watch

$ pnpm run -s tests
 PASS  tests/auth.js
  ✓ Create a user (39 ms)

Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total
Snapshots:   0 total
Time:        0.255 s, estimated 1 s
Ran all test suites.

Prettier

Launch Prettier check:

$ pnpm run prettier-check

Apply Prettier fix example:

$ pnpm run prettier src/app.html

ESlint

$ pnpm run eslint