Data Models - lkubis/AspNetCore.Identity.Cassandra GitHub Wiki

CREATE TABLE identity.users (
    id uuid PRIMARY KEY,
    email text,
    emailconfirmationtime timestamp,
    lockout frozen<lockoutinfo>,
    logins frozen<list<frozen<logininfo>>>,
    normalizedemail text,
    normalizedusername text,
    passwordhash text,
    phone frozen<phoneinfo>,
    roles list<text>,
    securitystamp text,
    tokens frozen<list<frozen<tokeninfo>>>,
    twofactorenabled boolean,
    username text
);
CREATE INDEX users_roles_idx ON identity.users (values(roles));

CREATE MATERIALIZED VIEW identity.users_by_username AS
    SELECT *
    FROM identity.users
    WHERE normalizedusername IS NOT NULL
    PRIMARY KEY (normalizedusername, id)
    WITH CLUSTERING ORDER BY (id ASC);

CREATE MATERIALIZED VIEW identity.users_by_email AS
    SELECT *
    FROM identity.users
    WHERE normalizedemail IS NOT NULL
    PRIMARY KEY (normalizedemail, id)
    WITH CLUSTERING ORDER BY (id ASC)

CREATE TABLE identity.userclaims (
    userid uuid,
    type text,
    value text,
    PRIMARY KEY (userid, type, value)
) WITH CLUSTERING ORDER BY (type ASC, value ASC)

CREATE MATERIALIZED VIEW identity.userclaims_by_type_and_value AS
    SELECT *
    FROM identity.userclaims
    WHERE type IS NOT NULL AND value IS NOT NULL
    PRIMARY KEY ((type, value), userid)
    WITH CLUSTERING ORDER BY (userid ASC)

CREATE TABLE identity.roles (
    id uuid PRIMARY KEY,
    name text,
    normalizedname text
);

CREATE MATERIALIZED VIEW identity.roles_by_name AS
    SELECT *
    FROM identity.roles
    WHERE normalizedname IS NOT NULL
    PRIMARY KEY (normalizedname, id)
    WITH CLUSTERING ORDER BY (id ASC);