Issue #5: implement sqlite auth store and migrations

Verified with dotnet build src\\MxGateway.sln and dotnet test src\\MxGateway.sln.

docs/gateway-process-design.md

@@ -612,6 +612,23 @@ SQLite auth storage should use startup migrations with a `schema_version` table.
 Migrations should run inside transactions and fail startup if the database
 schema is newer than the running binary understands.
 
+The v1 auth store uses `Microsoft.Data.Sqlite` and creates the
+`schema_version`, `api_keys`, and `api_key_audit` tables through
+`SqliteAuthStoreMigrator`. `AuthStoreMigrationHostedService` runs those
+migrations at gateway startup when API-key authentication and
+`Authentication:RunMigrationsOnStartup` are enabled. A database with a newer
+schema version fails startup instead of being modified by an older gateway
+binary.
+
+`IApiKeyStore` reads stored key records and exposes an active-key lookup that
+excludes rows with `revoked_utc` set. Hash verification belongs to the API-key
+hashing layer, but the store preserves the `secret_hash` bytes, display name,
+scopes, timestamps, and revocation state needed by that layer.
+
+`IApiKeyAuditStore` appends audit events to `api_key_audit` and returns recent
+events for diagnostics and future administrative tools. Audit records store key
+ids and event metadata only; they do not store raw API key secrets.
+
 Commands requiring authorization:
 
 - writes,

src/MxGateway.Server/GatewayApplication.cs

@@ -2,6 +2,7 @@ using MxGateway.Contracts;
 using MxGateway.Server.Configuration;
 using MxGateway.Server.Diagnostics;
 using MxGateway.Server.Metrics;
+using MxGateway.Server.Security.Authentication;
 
 namespace MxGateway.Server;
 
@@ -23,6 +24,7 @@ public static class GatewayApplication
         WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
 
         builder.Services.AddGatewayConfiguration();
+        builder.Services.AddSqliteAuthStore();
         builder.Services.AddHealthChecks();
         builder.Services.AddSingleton<GatewayMetrics>();
 

src/MxGateway.Server/MxGateway.Server.csproj

@@ -4,6 +4,10 @@
     <TargetFramework>net10.0</TargetFramework>
   </PropertyGroup>
 
+  <ItemGroup>
+    <PackageReference Include="Microsoft.Data.Sqlite" Version="10.0.7" />
+  </ItemGroup>
+
   <ItemGroup>
     <ProjectReference Include="..\MxGateway.Contracts\MxGateway.Contracts.csproj" />
   </ItemGroup>

src/MxGateway.Server/Security/Authentication/ApiKeyAuditEntry.cs

@@ -0,0 +1,7 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed record ApiKeyAuditEntry(
+    string? KeyId,
+    string EventType,
+    string? RemoteAddress,
+    string? Details);

src/MxGateway.Server/Security/Authentication/ApiKeyAuditRecord.cs

@@ -0,0 +1,9 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed record ApiKeyAuditRecord(
+    long AuditId,
+    string? KeyId,
+    string EventType,
+    string? RemoteAddress,
+    DateTimeOffset CreatedUtc,
+    string? Details);

src/MxGateway.Server/Security/Authentication/ApiKeyRecord.cs

@@ -0,0 +1,11 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed record ApiKeyRecord(
+    string KeyId,
+    string KeyPrefix,
+    byte[] SecretHash,
+    string DisplayName,
+    IReadOnlySet<string> Scopes,
+    DateTimeOffset CreatedUtc,
+    DateTimeOffset? LastUsedUtc,
+    DateTimeOffset? RevokedUtc);

src/MxGateway.Server/Security/Authentication/ApiKeyScopeSerializer.cs

@@ -0,0 +1,23 @@
+using System.Text.Json;
+
+namespace MxGateway.Server.Security.Authentication;
+
+public static class ApiKeyScopeSerializer
+{
+    public static string Serialize(IReadOnlySet<string> scopes)
+    {
+        return JsonSerializer.Serialize(scopes.Order(StringComparer.Ordinal));
+    }
+
+    public static IReadOnlySet<string> Deserialize(string value)
+    {
+        if (string.IsNullOrWhiteSpace(value))
+        {
+            return new HashSet<string>(StringComparer.Ordinal);
+        }
+
+        string[]? scopes = JsonSerializer.Deserialize<string[]>(value);
+
+        return new HashSet<string>(scopes ?? [], StringComparer.Ordinal);
+    }
+}

src/MxGateway.Server/Security/Authentication/AuthSqliteConnectionFactory.cs

@@ -0,0 +1,27 @@
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Options;
+using MxGateway.Server.Configuration;
+
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed class AuthSqliteConnectionFactory(IOptions<GatewayOptions> options)
+{
+    public SqliteConnection CreateConnection()
+    {
+        string sqlitePath = options.Value.Authentication.SqlitePath;
+        string? directory = Path.GetDirectoryName(sqlitePath);
+
+        if (!string.IsNullOrWhiteSpace(directory))
+        {
+            Directory.CreateDirectory(directory);
+        }
+
+        SqliteConnectionStringBuilder builder = new()
+        {
+            DataSource = sqlitePath,
+            Mode = SqliteOpenMode.ReadWriteCreate
+        };
+
+        return new SqliteConnection(builder.ToString());
+    }
+}

src/MxGateway.Server/Security/Authentication/AuthStoreMigrationException.cs

@@ -0,0 +1,3 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed class AuthStoreMigrationException(string message) : InvalidOperationException(message);

src/MxGateway.Server/Security/Authentication/AuthStoreMigrationHostedService.cs

@@ -0,0 +1,24 @@
+using Microsoft.Extensions.Options;
+using MxGateway.Server.Configuration;
+
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed class AuthStoreMigrationHostedService(
+    IOptions<GatewayOptions> options,
+    IAuthStoreMigrator migrator) : IHostedService
+{
+    public async Task StartAsync(CancellationToken cancellationToken)
+    {
+        AuthenticationOptions authentication = options.Value.Authentication;
+
+        if (authentication.Mode == AuthenticationMode.ApiKey && authentication.RunMigrationsOnStartup)
+        {
+            await migrator.MigrateAsync(cancellationToken).ConfigureAwait(false);
+        }
+    }
+
+    public Task StopAsync(CancellationToken cancellationToken)
+    {
+        return Task.CompletedTask;
+    }
+}

src/MxGateway.Server/Security/Authentication/AuthStoreServiceCollectionExtensions.cs

@@ -0,0 +1,15 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public static class AuthStoreServiceCollectionExtensions
+{
+    public static IServiceCollection AddSqliteAuthStore(this IServiceCollection services)
+    {
+        services.AddSingleton<AuthSqliteConnectionFactory>();
+        services.AddSingleton<IAuthStoreMigrator, SqliteAuthStoreMigrator>();
+        services.AddSingleton<IApiKeyStore, SqliteApiKeyStore>();
+        services.AddSingleton<IApiKeyAuditStore, SqliteApiKeyAuditStore>();
+        services.AddHostedService<AuthStoreMigrationHostedService>();
+
+        return services;
+    }
+}

src/MxGateway.Server/Security/Authentication/IApiKeyAuditStore.cs

@@ -0,0 +1,8 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public interface IApiKeyAuditStore
+{
+    Task AppendAsync(ApiKeyAuditEntry entry, CancellationToken cancellationToken);
+
+    Task<IReadOnlyList<ApiKeyAuditRecord>> ListRecentAsync(int count, CancellationToken cancellationToken);
+}

src/MxGateway.Server/Security/Authentication/IApiKeyStore.cs

@@ -0,0 +1,10 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public interface IApiKeyStore
+{
+    Task<ApiKeyRecord?> FindByKeyIdAsync(string keyId, CancellationToken cancellationToken);
+
+    Task<ApiKeyRecord?> FindActiveByKeyIdAsync(string keyId, CancellationToken cancellationToken);
+
+    Task MarkKeyUsedAsync(string keyId, DateTimeOffset usedUtc, CancellationToken cancellationToken);
+}

src/MxGateway.Server/Security/Authentication/IAuthStoreMigrator.cs

@@ -0,0 +1,6 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public interface IAuthStoreMigrator
+{
+    Task MigrateAsync(CancellationToken cancellationToken);
+}

src/MxGateway.Server/Security/Authentication/SqliteApiKeyAuditStore.cs

@@ -0,0 +1,65 @@
+using Microsoft.Data.Sqlite;
+
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed class SqliteApiKeyAuditStore(AuthSqliteConnectionFactory connectionFactory) : IApiKeyAuditStore
+{
+    public async Task AppendAsync(ApiKeyAuditEntry entry, CancellationToken cancellationToken)
+    {
+        await using SqliteConnection connection = connectionFactory.CreateConnection();
+        await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            INSERT INTO api_key_audit (key_id, event_type, remote_address, created_utc, details)
+            VALUES ($key_id, $event_type, $remote_address, $created_utc, $details);
+            """;
+        command.Parameters.AddWithValue("$key_id", (object?)entry.KeyId ?? DBNull.Value);
+        command.Parameters.AddWithValue("$event_type", entry.EventType);
+        command.Parameters.AddWithValue("$remote_address", (object?)entry.RemoteAddress ?? DBNull.Value);
+        command.Parameters.AddWithValue("$created_utc", DateTimeOffset.UtcNow.ToString("O"));
+        command.Parameters.AddWithValue("$details", (object?)entry.Details ?? DBNull.Value);
+
+        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
+    }
+
+    public async Task<IReadOnlyList<ApiKeyAuditRecord>> ListRecentAsync(int count, CancellationToken cancellationToken)
+    {
+        if (count <= 0)
+        {
+            return [];
+        }
+
+        await using SqliteConnection connection = connectionFactory.CreateConnection();
+        await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            SELECT audit_id, key_id, event_type, remote_address, created_utc, details
+            FROM api_key_audit
+            ORDER BY audit_id DESC
+            LIMIT $count;
+            """;
+        command.Parameters.AddWithValue("$count", count);
+
+        List<ApiKeyAuditRecord> records = [];
+
+        await using SqliteDataReader reader = await command.ExecuteReaderAsync(cancellationToken)
+            .ConfigureAwait(false);
+
+        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
+        {
+            records.Add(new ApiKeyAuditRecord(
+                AuditId: reader.GetInt64(0),
+                KeyId: reader.IsDBNull(1) ? null : reader.GetString(1),
+                EventType: reader.GetString(2),
+                RemoteAddress: reader.IsDBNull(3) ? null : reader.GetString(3),
+                CreatedUtc: DateTimeOffset.Parse(
+                    reader.GetString(4),
+                    System.Globalization.CultureInfo.InvariantCulture),
+                Details: reader.IsDBNull(5) ? null : reader.GetString(5)));
+        }
+
+        return records;
+    }
+}

src/MxGateway.Server/Security/Authentication/SqliteApiKeyStore.cs

@@ -0,0 +1,86 @@
+using Microsoft.Data.Sqlite;
+
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed class SqliteApiKeyStore(AuthSqliteConnectionFactory connectionFactory) : IApiKeyStore
+{
+    public Task<ApiKeyRecord?> FindByKeyIdAsync(string keyId, CancellationToken cancellationToken)
+    {
+        return FindByKeyIdAsync(keyId, requireActive: false, cancellationToken);
+    }
+
+    public Task<ApiKeyRecord?> FindActiveByKeyIdAsync(string keyId, CancellationToken cancellationToken)
+    {
+        return FindByKeyIdAsync(keyId, requireActive: true, cancellationToken);
+    }
+
+    public async Task MarkKeyUsedAsync(string keyId, DateTimeOffset usedUtc, CancellationToken cancellationToken)
+    {
+        await using SqliteConnection connection = connectionFactory.CreateConnection();
+        await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            UPDATE api_keys
+            SET last_used_utc = $last_used_utc
+            WHERE key_id = $key_id AND revoked_utc IS NULL;
+            """;
+        command.Parameters.AddWithValue("$key_id", keyId);
+        command.Parameters.AddWithValue("$last_used_utc", usedUtc.ToString("O"));
+
+        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
+    }
+
+    private async Task<ApiKeyRecord?> FindByKeyIdAsync(
+        string keyId,
+        bool requireActive,
+        CancellationToken cancellationToken)
+    {
+        await using SqliteConnection connection = connectionFactory.CreateConnection();
+        await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = requireActive
+            ? """
+              SELECT key_id, key_prefix, secret_hash, display_name, scopes, created_utc, last_used_utc, revoked_utc
+              FROM api_keys
+              WHERE key_id = $key_id AND revoked_utc IS NULL;
+              """
+            : """
+              SELECT key_id, key_prefix, secret_hash, display_name, scopes, created_utc, last_used_utc, revoked_utc
+              FROM api_keys
+              WHERE key_id = $key_id;
+              """;
+        command.Parameters.AddWithValue("$key_id", keyId);
+
+        await using SqliteDataReader reader = await command.ExecuteReaderAsync(cancellationToken)
+            .ConfigureAwait(false);
+
+        if (!await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
+        {
+            return null;
+        }
+
+        return ReadApiKeyRecord(reader);
+    }
+
+    private static ApiKeyRecord ReadApiKeyRecord(SqliteDataReader reader)
+    {
+        return new ApiKeyRecord(
+            KeyId: reader.GetString(0),
+            KeyPrefix: reader.GetString(1),
+            SecretHash: (byte[])reader["secret_hash"],
+            DisplayName: reader.GetString(3),
+            Scopes: ApiKeyScopeSerializer.Deserialize(reader.GetString(4)),
+            CreatedUtc: DateTimeOffset.Parse(reader.GetString(5), System.Globalization.CultureInfo.InvariantCulture),
+            LastUsedUtc: ReadNullableDateTimeOffset(reader, 6),
+            RevokedUtc: ReadNullableDateTimeOffset(reader, 7));
+    }
+
+    private static DateTimeOffset? ReadNullableDateTimeOffset(SqliteDataReader reader, int ordinal)
+    {
+        return reader.IsDBNull(ordinal)
+            ? null
+            : DateTimeOffset.Parse(reader.GetString(ordinal), System.Globalization.CultureInfo.InvariantCulture);
+    }
+}

src/MxGateway.Server/Security/Authentication/SqliteAuthSchema.cs

@@ -0,0 +1,12 @@
+namespace MxGateway.Server.Security.Authentication;
+
+public static class SqliteAuthSchema
+{
+    public const int CurrentVersion = 1;
+
+    public const string SchemaVersionTable = "schema_version";
+
+    public const string ApiKeysTable = "api_keys";
+
+    public const string ApiKeyAuditTable = "api_key_audit";
+}

src/MxGateway.Server/Security/Authentication/SqliteAuthStoreMigrator.cs

@@ -0,0 +1,135 @@
+using Microsoft.Data.Sqlite;
+
+namespace MxGateway.Server.Security.Authentication;
+
+public sealed class SqliteAuthStoreMigrator(AuthSqliteConnectionFactory connectionFactory) : IAuthStoreMigrator
+{
+    public async Task MigrateAsync(CancellationToken cancellationToken)
+    {
+        await using SqliteConnection connection = connectionFactory.CreateConnection();
+        await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
+
+        await using SqliteTransaction transaction =
+            (SqliteTransaction)await connection.BeginTransactionAsync(cancellationToken).ConfigureAwait(false);
+
+        int existingVersion = await ReadExistingSchemaVersionAsync(connection, transaction, cancellationToken)
+            .ConfigureAwait(false);
+
+        if (existingVersion > SqliteAuthSchema.CurrentVersion)
+        {
+            throw new AuthStoreMigrationException(
+                $"Auth database schema version {existingVersion} is newer than supported version {SqliteAuthSchema.CurrentVersion}.");
+        }
+
+        await ApplyVersionOneAsync(connection, transaction, cancellationToken).ConfigureAwait(false);
+
+        await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);
+    }
+
+    private static async Task<int> ReadExistingSchemaVersionAsync(
+        SqliteConnection connection,
+        SqliteTransaction transaction,
+        CancellationToken cancellationToken)
+    {
+        await using SqliteCommand tableExistsCommand = connection.CreateCommand();
+        tableExistsCommand.Transaction = transaction;
+        tableExistsCommand.CommandText = """
+            SELECT COUNT(*)
+            FROM sqlite_master
+            WHERE type = 'table' AND name = $table_name;
+            """;
+        tableExistsCommand.Parameters.AddWithValue("$table_name", SqliteAuthSchema.SchemaVersionTable);
+
+        long tableCount = (long)(await tableExistsCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false) ?? 0L);
+
+        if (tableCount == 0)
+        {
+            return 0;
+        }
+
+        await using SqliteCommand versionCommand = connection.CreateCommand();
+        versionCommand.Transaction = transaction;
+        versionCommand.CommandText = """
+            SELECT version
+            FROM schema_version
+            WHERE id = 1;
+            """;
+
+        object? version = await versionCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
+
+        return version is null || version == DBNull.Value
+            ? 0
+            : Convert.ToInt32(version, System.Globalization.CultureInfo.InvariantCulture);
+    }
+
+    private static async Task ApplyVersionOneAsync(
+        SqliteConnection connection,
+        SqliteTransaction transaction,
+        CancellationToken cancellationToken)
+    {
+        await ExecuteNonQueryAsync(
+            connection,
+            transaction,
+            """
+            CREATE TABLE IF NOT EXISTS schema_version (
+                id INTEGER PRIMARY KEY CHECK (id = 1),
+                version INTEGER NOT NULL,
+                applied_utc TEXT NOT NULL
+            );
+
+            CREATE TABLE IF NOT EXISTS api_keys (
+                key_id TEXT PRIMARY KEY,
+                key_prefix TEXT NOT NULL,
+                secret_hash BLOB NOT NULL,
+                display_name TEXT NOT NULL,
+                scopes TEXT NOT NULL,
+                created_utc TEXT NOT NULL,
+                last_used_utc TEXT NULL,
+                revoked_utc TEXT NULL
+            );
+
+            CREATE TABLE IF NOT EXISTS api_key_audit (
+                audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
+                key_id TEXT NULL,
+                event_type TEXT NOT NULL,
+                remote_address TEXT NULL,
+                created_utc TEXT NOT NULL,
+                details TEXT NULL
+            );
+
+            CREATE INDEX IF NOT EXISTS ix_api_keys_revoked_utc
+                ON api_keys (revoked_utc);
+
+            CREATE INDEX IF NOT EXISTS ix_api_key_audit_key_id_created_utc
+                ON api_key_audit (key_id, created_utc);
+            """,
+            cancellationToken).ConfigureAwait(false);
+
+        await using SqliteCommand versionCommand = connection.CreateCommand();
+        versionCommand.Transaction = transaction;
+        versionCommand.CommandText = """
+            INSERT INTO schema_version (id, version, applied_utc)
+            VALUES (1, $version, $applied_utc)
+            ON CONFLICT(id) DO UPDATE SET
+                version = excluded.version,
+                applied_utc = excluded.applied_utc;
+            """;
+        versionCommand.Parameters.AddWithValue("$version", SqliteAuthSchema.CurrentVersion);
+        versionCommand.Parameters.AddWithValue("$applied_utc", DateTimeOffset.UtcNow.ToString("O"));
+
+        await versionCommand.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
+    }
+
+    private static async Task ExecuteNonQueryAsync(
+        SqliteConnection connection,
+        SqliteTransaction transaction,
+        string commandText,
+        CancellationToken cancellationToken)
+    {
+        await using SqliteCommand command = connection.CreateCommand();
+        command.Transaction = transaction;
+        command.CommandText = commandText;
+
+        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
+    }
+}

src/MxGateway.Tests/Security/Authentication/SqliteAuthStoreTests.cs

@@ -0,0 +1,280 @@
+using Microsoft.AspNetCore.Builder;
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.DependencyInjection;
+using MxGateway.Server;
+using MxGateway.Server.Configuration;
+using MxGateway.Server.Security.Authentication;
+
+namespace MxGateway.Tests.Security.Authentication;
+
+public sealed class SqliteAuthStoreTests
+{
+    [Fact]
+    public async Task MigrateAsync_EmptyDatabase_InitializesCurrentSchema()
+    {
+        string databasePath = CreateTempDatabasePath();
+        await using ServiceProvider services = BuildAuthServices(databasePath);
+
+        IAuthStoreMigrator migrator = services.GetRequiredService<IAuthStoreMigrator>();
+
+        await migrator.MigrateAsync(CancellationToken.None);
+
+        Assert.Equal(SqliteAuthSchema.CurrentVersion, await ReadSchemaVersionAsync(databasePath));
+        Assert.True(await TableExistsAsync(databasePath, SqliteAuthSchema.ApiKeysTable));
+        Assert.True(await TableExistsAsync(databasePath, SqliteAuthSchema.ApiKeyAuditTable));
+    }
+
+    [Fact]
+    public async Task MigrateAsync_ExistingVersionZeroDatabase_MigratesIdempotently()
+    {
+        string databasePath = CreateTempDatabasePath();
+        await CreateVersionZeroDatabaseAsync(databasePath);
+        await using ServiceProvider services = BuildAuthServices(databasePath);
+
+        IAuthStoreMigrator migrator = services.GetRequiredService<IAuthStoreMigrator>();
+
+        await migrator.MigrateAsync(CancellationToken.None);
+        await migrator.MigrateAsync(CancellationToken.None);
+
+        Assert.Equal(SqliteAuthSchema.CurrentVersion, await ReadSchemaVersionAsync(databasePath));
+        Assert.True(await TableExistsAsync(databasePath, SqliteAuthSchema.ApiKeysTable));
+        Assert.True(await TableExistsAsync(databasePath, SqliteAuthSchema.ApiKeyAuditTable));
+    }
+
+    [Fact]
+    public async Task StartAsync_NewerSchemaVersion_BlocksStartup()
+    {
+        string databasePath = CreateTempDatabasePath();
+        await CreateSchemaVersionDatabaseAsync(databasePath, SqliteAuthSchema.CurrentVersion + 1);
+
+        await using WebApplication app = GatewayApplication.Build(
+            [
+                $"--MxGateway:Authentication:SqlitePath={databasePath}",
+                "--urls=http://127.0.0.1:0"
+            ]);
+
+        AuthStoreMigrationException exception = await Assert.ThrowsAsync<AuthStoreMigrationException>(
+            () => app.StartAsync(CancellationToken.None));
+
+        Assert.Contains("newer than supported version", exception.Message, StringComparison.Ordinal);
+    }
+
+    [Fact]
+    public async Task FindActiveByKeyIdAsync_ExistingActiveKey_ReturnsKey()
+    {
+        string databasePath = CreateTempDatabasePath();
+        await using ServiceProvider services = BuildAuthServices(databasePath);
+        await services.GetRequiredService<IAuthStoreMigrator>().MigrateAsync(CancellationToken.None);
+        await InsertApiKeyAsync(databasePath, revokedUtc: null);
+
+        IApiKeyStore store = services.GetRequiredService<IApiKeyStore>();
+
+        ApiKeyRecord? key = await store.FindActiveByKeyIdAsync("test-key", CancellationToken.None);
+
+        Assert.NotNull(key);
+        Assert.Equal("test-key", key.KeyId);
+        Assert.Equal("mxgw_test", key.KeyPrefix);
+        Assert.Equal([1, 2, 3, 4], key.SecretHash);
+        Assert.Contains("session:open", key.Scopes);
+        Assert.Null(key.RevokedUtc);
+    }
+
+    [Fact]
+    public async Task FindActiveByKeyIdAsync_RevokedKey_ReturnsNull()
+    {
+        string databasePath = CreateTempDatabasePath();
+        await using ServiceProvider services = BuildAuthServices(databasePath);
+        await services.GetRequiredService<IAuthStoreMigrator>().MigrateAsync(CancellationToken.None);
+        await InsertApiKeyAsync(databasePath, DateTimeOffset.UtcNow);
+
+        IApiKeyStore store = services.GetRequiredService<IApiKeyStore>();
+
+        ApiKeyRecord? activeKey = await store.FindActiveByKeyIdAsync(
+            "test-key",
+            CancellationToken.None);
+        ApiKeyRecord? storedKey = await store.FindByKeyIdAsync("test-key", CancellationToken.None);
+
+        Assert.Null(activeKey);
+        Assert.NotNull(storedKey);
+        Assert.NotNull(storedKey.RevokedUtc);
+    }
+
+    [Fact]
+    public async Task ApiKeyAuditStore_AppendAsync_PersistsAuditEvent()
+    {
+        string databasePath = CreateTempDatabasePath();
+        await using ServiceProvider services = BuildAuthServices(databasePath);
+        await services.GetRequiredService<IAuthStoreMigrator>().MigrateAsync(CancellationToken.None);
+
+        IApiKeyAuditStore auditStore = services.GetRequiredService<IApiKeyAuditStore>();
+
+        await auditStore.AppendAsync(
+            new ApiKeyAuditEntry(
+                KeyId: "test-key",
+                EventType: "lookup",
+                RemoteAddress: "127.0.0.1",
+                Details: "matched active key"),
+            CancellationToken.None);
+
+        IReadOnlyList<ApiKeyAuditRecord> records = await auditStore.ListRecentAsync(
+            10,
+            CancellationToken.None);
+
+        ApiKeyAuditRecord record = Assert.Single(records);
+        Assert.Equal("test-key", record.KeyId);
+        Assert.Equal("lookup", record.EventType);
+        Assert.Equal("127.0.0.1", record.RemoteAddress);
+        Assert.Equal("matched active key", record.Details);
+    }
+
+    private static ServiceProvider BuildAuthServices(string databasePath)
+    {
+        IConfigurationRoot configuration = new ConfigurationBuilder()
+            .AddInMemoryCollection(
+                new Dictionary<string, string?>
+                {
+                    ["MxGateway:Authentication:SqlitePath"] = databasePath
+                })
+            .Build();
+
+        ServiceCollection services = new();
+        services.AddSingleton<IConfiguration>(configuration);
+        services.AddGatewayConfiguration();
+        services.AddSqliteAuthStore();
+
+        return services.BuildServiceProvider(validateScopes: true);
+    }
+
+    private static string CreateTempDatabasePath()
+    {
+        string directory = Path.Combine(Path.GetTempPath(), "mxgateway-auth-tests", Guid.NewGuid().ToString("N"));
+        Directory.CreateDirectory(directory);
+
+        return Path.Combine(directory, "gateway-auth.db");
+    }
+
+    private static async Task CreateVersionZeroDatabaseAsync(string databasePath)
+    {
+        await using SqliteConnection connection = CreateConnection(databasePath);
+        await connection.OpenAsync(CancellationToken.None);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            CREATE TABLE schema_version (
+                id INTEGER PRIMARY KEY CHECK (id = 1),
+                version INTEGER NOT NULL,
+                applied_utc TEXT NOT NULL
+            );
+
+            INSERT INTO schema_version (id, version, applied_utc)
+            VALUES (1, 0, $applied_utc);
+            """;
+        command.Parameters.AddWithValue("$applied_utc", DateTimeOffset.UtcNow.ToString("O"));
+
+        await command.ExecuteNonQueryAsync(CancellationToken.None);
+    }
+
+    private static async Task CreateSchemaVersionDatabaseAsync(string databasePath, int version)
+    {
+        await using SqliteConnection connection = CreateConnection(databasePath);
+        await connection.OpenAsync(CancellationToken.None);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            CREATE TABLE schema_version (
+                id INTEGER PRIMARY KEY CHECK (id = 1),
+                version INTEGER NOT NULL,
+                applied_utc TEXT NOT NULL
+            );
+
+            INSERT INTO schema_version (id, version, applied_utc)
+            VALUES (1, $version, $applied_utc);
+            """;
+        command.Parameters.AddWithValue("$version", version);
+        command.Parameters.AddWithValue("$applied_utc", DateTimeOffset.UtcNow.ToString("O"));
+
+        await command.ExecuteNonQueryAsync(CancellationToken.None);
+    }
+
+    private static async Task InsertApiKeyAsync(string databasePath, DateTimeOffset? revokedUtc)
+    {
+        await using SqliteConnection connection = CreateConnection(databasePath);
+        await connection.OpenAsync(CancellationToken.None);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            INSERT INTO api_keys (
+                key_id,
+                key_prefix,
+                secret_hash,
+                display_name,
+                scopes,
+                created_utc,
+                last_used_utc,
+                revoked_utc)
+            VALUES (
+                $key_id,
+                $key_prefix,
+                $secret_hash,
+                $display_name,
+                $scopes,
+                $created_utc,
+                NULL,
+                $revoked_utc);
+            """;
+        command.Parameters.AddWithValue("$key_id", "test-key");
+        command.Parameters.AddWithValue("$key_prefix", "mxgw_test");
+        command.Parameters.Add("$secret_hash", SqliteType.Blob).Value = new byte[] { 1, 2, 3, 4 };
+        command.Parameters.AddWithValue("$display_name", "Test Key");
+        command.Parameters.AddWithValue(
+            "$scopes",
+            ApiKeyScopeSerializer.Serialize(new HashSet<string>(StringComparer.Ordinal) { "session:open", "events:read" }));
+        command.Parameters.AddWithValue("$created_utc", DateTimeOffset.UtcNow.ToString("O"));
+        command.Parameters.AddWithValue("$revoked_utc", revokedUtc?.ToString("O") ?? (object)DBNull.Value);
+
+        await command.ExecuteNonQueryAsync(CancellationToken.None);
+    }
+
+    private static async Task<int> ReadSchemaVersionAsync(string databasePath)
+    {
+        await using SqliteConnection connection = CreateConnection(databasePath);
+        await connection.OpenAsync(CancellationToken.None);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = "SELECT version FROM schema_version WHERE id = 1;";
+
+        object? result = await command.ExecuteScalarAsync(CancellationToken.None);
+
+        return Convert.ToInt32(result, System.Globalization.CultureInfo.InvariantCulture);
+    }
+
+    private static async Task<bool> TableExistsAsync(string databasePath, string tableName)
+    {
+        await using SqliteConnection connection = CreateConnection(databasePath);
+        await connection.OpenAsync(CancellationToken.None);
+
+        await using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = """
+            SELECT COUNT(*)
+            FROM sqlite_master
+            WHERE type = 'table' AND name = $table_name;
+            """;
+        command.Parameters.AddWithValue("$table_name", tableName);
+
+        long result = (long)(await command.ExecuteScalarAsync(CancellationToken.None) ?? 0L);
+
+        return result == 1;
+    }
+
+    private static SqliteConnection CreateConnection(string databasePath)
+    {
+        SqliteConnectionStringBuilder builder = new()
+        {
+            DataSource = databasePath,
+            Mode = SqliteOpenMode.ReadWriteCreate
+        };
+
+        return new SqliteConnection(builder.ToString());
+    }
+}