inanzzz | User account management with Cassandra

This is a very simple example which demonstrates how a user account could be managed with Cassandra database. It helps you adopt some public and private (authenticated) features. Check this excellent video tutorial for Cassandra data modelling and its relevant code here.

Database

CREATE KEYSPACE IF NOT EXISTS blog
WITH REPLICATION = {
    'class': 'NetworkTopologyStrategy',
    'datacenter1': 1
};


DROP TABLE IF EXISTS blog.user_accounts;
CREATE TABLE IF NOT EXISTS blog.user_accounts (
    id uuid,
    name text,
    email text,
    created_at timestamp,
    deleted_at timestamp,
    PRIMARY KEY (id)
);


DROP TABLE IF EXISTS blog.user_credentials;
CREATE TABLE IF NOT EXISTS blog.user_credentials (
    email text,
    password text,
    user_account_id uuid,
    deleted_at timestamp,
    PRIMARY KEY (email)
);


DROP TABLE IF EXISTS password_recovery_tokens;
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    "token" text,
    email text,
    user_account_id uuid,
    PRIMARY KEY ("token")
) WITH default_time_to_live = 3600;

Design

It is inevitable that multiple queries will have to be run to achieve one thing. However, due to design, batch operations with conditions cannot span multiple tables so you have to make sure manual rollback.

Register a new user (public)

Creating a new user starts from user_credentials because it enforces email uniqueness. After that, user_accounts is created using the same email and id coming from user_credentials. The uniqueness of id is enforced by user_accounts so in case of a conflict, user_credentials must be rolled back. The id is the global identifier of a user and never changes. This value can be used in other tables to represent user association.

// Creates `user_credentials` unless `email` value is taken.
INSERT INTO user_credentials (email, password, user_account_id)
VALUES (?, ?, ?) IF NOT EXISTS;

// Creates `user_accounts` unless `id` value is taken.
INSERT INTO user_accounts (id, name, created_at, email)
VALUES (?, ?, ?, ?) IF NOT EXISTS;

Forgot password (public)

All you need to know is the email address to send a password recovery link. It is up to you how to construct the token value. It might just be a hash or encoded token. Tokens expire in 1 hour by default as defined with default_time_to_live at table level.

// Select current data.
SELECT * FROM user_credentials WHERE email = 'current email';

// Insert new password reset token record.
INSERT INTO password_reset_tokens (token, email, user_account_id)
VALUES (?, ?, ?) IF NOT EXISTS;

Reset password (public)

You already have the token attached to the link. If your token was an encoded value that contains user data, you could decode and verify against data coming from database.

// Select current token.
SELECT * FROM password_reset_tokens WHERE token = ?;

// Update password.
UPDATE user_credentials SET password = ? WHERE email = 'current email' IF EXISTS;

// Delete current token.
DELETE FROM password_reset_tokens WHERE token = ?;

Login user (public)

// Select user and verify password.
SELECT * FROM user_credentials WHERE email = ?;

Change email address (private)

You are already logged in so the email is known.

// Select current data.
SELECT * FROM user_credentials WHERE email = 'current email';

// Create a new record using new and current data.
INSERT INTO user_credentials (email, password, user_account_id)
VALUES ('new email', 'current password', current uuid) IF NOT EXISTS;

// Update current email with new one.
UPDATE user_accounts SET email = 'new email' WHERE id = current uuid IF EXISTS;

// Delete redundant record.
DELETE FROM user_credentials WHERE email = 'current email' IF EXISTS;

Change password (private)

You are already logged in so the email is known. Such operations would often require password confirmation up front.

// Update current password with new one.
UPDATE user_credentials SET password = ? WHERE email = ? IF EXISTS;

Delete account (private)

You are already logged in so the email is known. Such operations would often require password confirmation up front. Hard-delete is often not an option. Use soft-delete instead by marking records as "deleted" with a timestamp.

// Select current data.
SELECT * FROM user_credentials WHERE email = 'current email';

// Soft delete.
UPDATE user_credentials SET deleted_at = ? WHERE email = 'current email' IF EXISTS;
UPDATE user_accounts SET deleted_at = ? WHERE id = current uuid IF EXISTS;