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) );