PulseTrade.Comm.Login.SqlServer 0.1.0-alpha3

PulseTrade.Comm.Login.SqlServer

PulseTrade.Comm.Login.SqlServer is the SQL Server durable session-store and credential verifier provider for PulseTrade.Comm.Login.Core.

Journal / Snapshot / DB

This package does not use Akka.Persistence journal or snapshot-store tables. It owns SQL Server tables in the caller-selected database.

Current database selection:

• LoginSqlServerProviderConfig.create connectionString uses the database in the supplied connection string.
• LoginSqlServerProviderConfig.fromEnvironment envName is legacy/local support only. Production hosts must use encrypted-file secret resolution before calling create.
• EnsureDatabase=false by default; set withEnsureDatabase true only when the host wants this package to create the database from Initial Catalog.
• Schema="dbo" by default; override with withSchema.

Session DDL:

CREATE TABLE [dbo].[LoginSession](
    SessionId NVARCHAR(256) NOT NULL CONSTRAINT PK_LoginSession PRIMARY KEY,
    AccessTokenHash CHAR(64) NOT NULL,
    RefreshTokenHash CHAR(64) NOT NULL,
    PrincipalId NVARCHAR(256) NOT NULL,
    Provider NVARCHAR(128) NOT NULL,
    ExpiresAtUtc DATETIMEOFFSET(7) NOT NULL,
    RefreshExpiresAtUtc DATETIMEOFFSET(7) NOT NULL,
    Revoked BIT NOT NULL,
    RecordJson NVARCHAR(MAX) NOT NULL,
    UpdatedAtUtc DATETIMEOFFSET(7) NOT NULL,
    InsertedAtUtc DATETIMEOFFSET(7) NOT NULL
);

CREATE INDEX IX_LoginSession_AccessTokenHash ON [dbo].[LoginSession](AccessTokenHash);
CREATE INDEX IX_LoginSession_RefreshTokenHash ON [dbo].[LoginSession](RefreshTokenHash);
CREATE INDEX IX_LoginSession_Principal ON [dbo].[LoginSession](PrincipalId, Provider);

RecordJson uses ptc.login.session-store.snapshot.v1 row JSON from PulseTrade.Comm.Login.Core.LoginSessionRecordJson. Token hashes are indexed for lookup; the full opaque tokens remain inside RecordJson, so the database is sensitive runtime state. Runtime DDL quotes schema identifiers and SQL string literals before generating OBJECT_ID / CREATE TABLE statements.

Credential DDL:

CREATE TABLE [dbo].[LoginUser](
    UserId NVARCHAR(256) NOT NULL CONSTRAINT PK_LoginUser PRIMARY KEY,
    LoginName NVARCHAR(256) NOT NULL,
    DisplayName NVARCHAR(256) NULL,
    Provider NVARCHAR(128) NOT NULL,
    Enabled BIT NOT NULL,
    PasswordAlgorithm NVARCHAR(64) NOT NULL,
    PasswordHashBase64 NVARCHAR(512) NOT NULL,
    PasswordSaltBase64 NVARCHAR(512) NOT NULL,
    PasswordIterations INT NOT NULL,
    CreatedAtUtc DATETIMEOFFSET(7) NOT NULL,
    UpdatedAtUtc DATETIMEOFFSET(7) NOT NULL,
    CONSTRAINT UQ_LoginUser_LoginName UNIQUE(LoginName)
);

CREATE TABLE [dbo].[LoginUserGroup](
    UserId NVARCHAR(256) NOT NULL,
    GroupName NVARCHAR(256) NOT NULL,
    CONSTRAINT PK_LoginUserGroup PRIMARY KEY(UserId, GroupName)
);

CREATE TABLE [dbo].[LoginUserRole](
    UserId NVARCHAR(256) NOT NULL,
    RoleName NVARCHAR(256) NOT NULL,
    CONSTRAINT PK_LoginUserRole PRIMARY KEY(UserId, RoleName)
);

Passwords are stored as PBKDF2-HMACSHA256 hashes with per-user salt and iteration metadata. SqlServerLoginCredentialVerifier implements ILoginCredentialVerifier and returns an AclPrincipal with SQL-projected groups/roles. It does not authorize commands; authorization remains the responsibility of PulseTrade.Comm.ACL.Core.