Files
Romulus21andClaude Opus 5.5 cbda09ed40 Ajoute l'authentification et le partage de listes
- Inscription / connexion par email + mot de passe (argon2), prénom ou
  pseudo enregistré ; session par cookie HttpOnly, jeton haché en base.
- Listes privées par propriétaire, partageables par email ; les membres
  peuvent gérer les articles et quitter la liste.
- Contrôle d'accès dans chaque server function.
- Suggestions propres à chaque utilisateur.
- Avatar avec initiales et feuille de compte (déconnexion).
- La page 404 reçoit le pool SQLite.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-09 12:47:09 +02:00

42 lines
1.3 KiB
SQL

CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE COLLATE NOCASE,
name TEXT NOT NULL,
password_hash TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
-- Only a SHA-256 of the cookie token is stored.
CREATE TABLE sessions (
token_hash TEXT PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
expires_at TEXT NOT NULL
);
CREATE INDEX sessions_user_id ON sessions(user_id);
-- NULL owner = list created before accounts existed; claimed by the first user to register.
ALTER TABLE lists ADD COLUMN owner_id INTEGER REFERENCES users(id) ON DELETE CASCADE;
CREATE INDEX lists_owner_id ON lists(owner_id);
CREATE TABLE list_members (
list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (list_id, user_id)
);
CREATE INDEX list_members_user_id ON list_members(user_id);
-- Suggestions become per user.
DROP TABLE catalog;
CREATE TABLE catalog (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL COLLATE NOCASE,
icon TEXT NOT NULL,
category TEXT NOT NULL,
use_count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (user_id, name)
);