schema.cql

CREATE SCHEMA provider
    WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1};

CREATE TABLE provider.client (
    client_id TEXT,                  -- OAuth 2 client ID
    realm TEXT,                      -- Data pool this entity belongs to
    client_secret_hash TEXT,         -- Bcrypt hash of the client secret
    scopes SET<TEXT>,                -- scopes this client is allowed to request
    default_scopes SET<TEXT>,        -- default requested scopes in case the client does not provide them explicitly
    is_confidential BOOLEAN,         -- whether the client is confidential or not (non-confidential clients should only be allowed to use the implicit flow)
    name TEXT,                       -- client's human readable name (shown on authorization consent form)
    description TEXT,                -- client's human readable description (for consent form)
    redirect_uris SET<TEXT>,         -- configured redirect URLs for Authorization Code Grant flow
    image_uri TEXT,                  -- an optional URI to the client application logo
    homepage_url TEXT,               -- an optional URL that points to the client application
    created_by TEXT,                 -- name of the user who created this entry
    last_modified_by TEXT,           -- name of the user did the last modification on this entry
    PRIMARY KEY ((client_id), realm)
);

CREATE TYPE provider.user_password_hash (
    password_hash TEXT,
    created INT,         -- seconds since epoch
    created_by TEXT      -- user that created this password
);

CREATE TABLE provider.user (
    username TEXT,                                    -- name of service user
    realm TEXT,                                       -- Data pool this entity belongs to
    password_hashes SET<frozen<user_password_hash>>,  -- set of Bcrypt hashes
    scopes MAP<TEXT, TEXT>,                           -- scopes and values for this user
    created_by TEXT,                                  -- name of the user who created this entry
    last_modified_by TEXT,                            -- name of the user who did the last modification on this entry
    PRIMARY KEY ((username), realm)
);

CREATE TABLE provider.keypair (
    kid TEXT PRIMARY KEY,       -- JWK key ID
    realms SET<TEXT>,           -- for which realms to use this key, allows to tune the system for speed vs. size
    private_key_pem TEXT,       -- private key in PEM format
    algorithm TEXT,             -- JWK algorithm according to RFC 7518 3.1 https://tools.ietf.org/html/rfc7518#section-3.1
    valid_from INT,             -- start date of key validity in seconds since epoch
    created_by TEXT,            -- name of the user who created this entry
    last_modified_by TEXT       -- name of the user who did the last modification on this entry
);

CREATE TABLE provider.authorization_code (
    code TEXT PRIMARY KEY,      -- OAuth2 authorization code generated by the authorization server
    state TEXT,                 -- optional state (passed thru), see http://tools.ietf.org/html/rfc6749#section-4.1.1
    client_id TEXT,             -- client ID
    realm TEXT,                 -- realm
    scopes SET<TEXT>,           -- scopes
    claims MAP<TEXT, TEXT>,     -- claim values (e.g. "sub" containing user ID)
    redirect_uri TEXT,          -- used redirect URI
    expires INT                 -- expiry date in seconds since epoch
);

CREATE TABLE provider.consent (
    realm TEXT,                                     -- realm
    username TEXT,                                  -- username
    client_id TEXT,                                 -- client ID
    scopes SET<TEXT>,                               -- consented scopes
    PRIMARY KEY ((username, realm), client_id)
);