Data Model

CREATE TABLE realms (
  id                             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name                           VARCHAR(100) UNIQUE NOT NULL,  -- slug: ^[a-z0-9-]+$
  display_name                   VARCHAR(255),
  login_theme                    VARCHAR(50)  DEFAULT 'default',
  token_lifespan_seconds         INT          DEFAULT 300,
  refresh_token_lifespan_seconds INT          DEFAULT 2592000,
  mfa_required                   BOOLEAN      DEFAULT false,
  password_policy                JSONB,       -- { minLength, requireUppercase, ... }
  smtp_settings                  JSONB,       -- { host, port, username, ... }
  enabled                        BOOLEAN      DEFAULT true,
  created_at                     TIMESTAMPTZ  DEFAULT NOW(),
  updated_at                     TIMESTAMPTZ  DEFAULT NOW()
);

CREATE TABLE users (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  realm_name      VARCHAR(100) NOT NULL,
  username        VARCHAR(150) NOT NULL,
  email           VARCHAR(255) NOT NULL,
  first_name      VARCHAR(100),
  last_name       VARCHAR(100),
  enabled         BOOLEAN     DEFAULT true,
  email_verified  BOOLEAN     DEFAULT false,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (realm_name, username),
  UNIQUE (realm_name, email)
);

CREATE TABLE user_credentials (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  credential_type VARCHAR(50) NOT NULL,  -- PASSWORD | TOTP
  secret_hash     TEXT        NOT NULL,  -- BCrypt(12) for PASSWORD, base32 for TOTP
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE roles (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  realm_name  VARCHAR(100) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  description TEXT,
  composite   BOOLEAN DEFAULT false,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (realm_name, name)
);

CREATE TABLE user_role_mappings (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID        NOT NULL,
  role_id     UUID        NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  realm_name  VARCHAR(100) NOT NULL,
  UNIQUE (user_id, role_id)
);

CREATE TABLE oauth_clients (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  realm_name         VARCHAR(100) NOT NULL,
  client_id          VARCHAR(100) UNIQUE NOT NULL,  -- client-{uuid8}
  client_secret_hash TEXT,                          -- BCrypt(12), null for public clients
  public_client      BOOLEAN  DEFAULT false,
  pkce_required      BOOLEAN  DEFAULT true,
  redirect_uris      TEXT[],
  web_origins        TEXT[],
  grant_types        TEXT[],  -- authorization_code | client_credentials | refresh_token
  scopes             TEXT[],  -- openid profile email
  enabled            BOOLEAN  DEFAULT true,
  created_at         TIMESTAMPTZ DEFAULT NOW()
);

Key:   session:{realmId}:{sessionId}
Type:  Hash
TTL:   8 hours (default)

Fields:
sessionId    → uuid
userId       → uuid
realmId      → string
clientIds    → comma-separated list
ipAddress    → string
userAgent    → string
createdAt    → ISO-8601 timestamp
expiresAt    → ISO-8601 timestamp

User session index (for listing sessions per user):
Key:   user-sessions:{realmId}:{userId}
Type:  Set  (values = sessionIds)

public record DomainEvent(
  String eventId,              // UUID
  String eventType,            // user.created | auth.login.success | ...
  String aggregateId,          // userId, sessionId, etc.
  String realm,
  Map<String, Object> payload,
  Instant occurredAt
) {
  public static DomainEvent of(
      String type, String id, String realm, Map<String, Object> payload
  ) {
      return new DomainEvent(
          UUID.randomUUID().toString(), type, id, realm, payload, Instant.now()
      );
  }
}