Baseline schema to create AuthenticationService

AuthorizationService.sql

CREATE TABLE [Account].[Client] (
    [ClientId]        BIGINT             IDENTITY (1, 1) NOT NULL,
    [ClientKey]       UNIQUEIDENTIFIER   NOT NULL,
    [CommonName]      NVARCHAR (50)      NOT NULL,
    [TeamName]        NVARCHAR (50)      NOT NULL,
    [EmailDomainName] NVARCHAR (50)      NULL,
    [IsArchived]      BIT                CONSTRAINT [DF_Account.Client_IsArchived] DEFAULT ((0)) NOT NULL,
    [Created]         DATETIMEOFFSET (7) CONSTRAINT [DF_Account.Client_Created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [LastUpdated]     DATETIMEOFFSET (7) NULL,
    [Watermark]       ROWVERSION         NOT NULL,
    CONSTRAINT [PK_Account.Client] PRIMARY KEY CLUSTERED ([ClientId] ASC)
);



CREATE TABLE [Account].[LocalAccount] (
    [LocalAccountId]              BIGINT             IDENTITY (1, 1) NOT NULL,
    [LocalAccountKey]             UNIQUEIDENTIFIER   NOT NULL,
    [ClientId]                    BIGINT             NULL,
    [UserName]                    NVARCHAR (255)     NOT NULL,
    [FirstName]                   NVARCHAR (50)      NULL,
    [LastName]                    NVARCHAR (50)      NULL,
    [Email]                       NVARCHAR (255)     NULL,
    [EmailConfirmed]              BIT                CONSTRAINT [DF_Account.LocalAccount_EmailConfirmed] DEFAULT ((0)) NOT NULL,
    [HashedPassword]              NVARCHAR (100)     NULL,
    [SecurityStamp]               NVARCHAR (100)     NULL,
    [PhoneNumber]                 NVARCHAR (25)      NULL,
    [PhoneNumberConfirmed]        BIT                CONSTRAINT [DF_Account.LocalAccount_PhoneNumberConfirmed] DEFAULT ((0)) NOT NULL,
    [IsTwoFactorEnabled]          BIT                CONSTRAINT [DF_Account.LocalAccount_IsTwoFactorEnabled] DEFAULT ((0)) NOT NULL,
    [HashedTwoFactorAuthCode]     NVARCHAR (100)     NULL,
    [DateTwoFactorAuthCodeIssued] DATETIME           NULL,
    [LockoutEnd]                  DATETIMEOFFSET (7) NULL,
    [LockoutEnabled]              BIT                CONSTRAINT [DF_Account.LocalAccount_LockoutEnabled] DEFAULT ((0)) NOT NULL,
    [AccessFailedCount]           INT                NOT NULL,
    [IsTeamLeader]                BIT                NOT NULL,
    [IsArchived]                  BIT                CONSTRAINT [DF_Account.LocalAccount_IsArchived] DEFAULT ((0)) NOT NULL,
    [Created]                     DATETIMEOFFSET (7) CONSTRAINT [DF_Account.LocalAccount_Created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [LastUpdated]                 DATETIMEOFFSET (7) NULL,
    [Watermark]                   ROWVERSION         NOT NULL,
    CONSTRAINT [PK_Account.LocalAccount] PRIMARY KEY CLUSTERED ([LocalAccountId] ASC),
    CONSTRAINT [FK_Account.LocalAccount_Client] FOREIGN KEY ([ClientId]) REFERENCES [Account].[Client] ([ClientId])
);