Read-only mirrors. Development, issues and pull requests are on GitHub.

summaryrefslogtreecommitdiffhomepage
path: root/apps/api/Accounts/PostgresAccountStore.cs
diff options
context:
space:
mode:
authorLeitwolf2026-10-07 07:09:39 +0300
committerGitHub2026-10-07 07:09:39 +0300
commit111a6175e0a92808ddc2a47088fa9858001eeed9 (patch)
tree0668bcb5fb3f52c92e94e40280228bad546a7511 /apps/api/Accounts/PostgresAccountStore.cs
parent5d4a9699675c0c0b9cd84c67f423e6e0f836a5d1 (diff)
parenta1383b688d3d2159e3ba525682180f9d5a4a2c80 (diff)
downloadprogmaweb-111a6175e0a92808ddc2a47088fa9858001eeed9.tar.gz
Merge pull request #28 from Progmasoft/feature/durable-account-store
Keep accounts and sessions in PostgreSQL
Diffstat (limited to 'apps/api/Accounts/PostgresAccountStore.cs')
-rw-r--r--apps/api/Accounts/PostgresAccountStore.cs200
1 files changed, 200 insertions, 0 deletions
diff --git a/apps/api/Accounts/PostgresAccountStore.cs b/apps/api/Accounts/PostgresAccountStore.cs
new file mode 100644
index 0000000..a0e3444
--- /dev/null
+++ b/apps/api/Accounts/PostgresAccountStore.cs
@@ -0,0 +1,200 @@
+// SPDX-FileCopyrightText: 2026 Progmasoft <support@progmasoft.com>
+// SPDX-License-Identifier: AGPL-3.0-or-later WITH AdditionRef-Progmasoft-Patent-Grant-1.1
+
+using Npgsql;
+
+namespace Progmasoft.Progmaweb.Api.Accounts;
+
+/// <summary>An <see cref="IAccountStore"/> that keeps accounts and sessions in PostgreSQL.</summary>
+/// <remarks>
+/// <para>
+/// The uniqueness rules are unique indexes of the <c>accounts</c> table, so two requests that race for one name,
+/// address or Google identity are decided by the database: one row is stored and the other request is refused. A
+/// session row refers to its account, and the database refuses a session of an account that does not exist.
+/// </para>
+/// <para>
+/// PostgreSQL keeps a timestamp to the microsecond. A moment is cut to that precision before it is stored, and the
+/// store returns what it stored, so a value read later equals the value returned when it was written.
+/// </para>
+/// </remarks>
+/// <param name="dataSource">Connections to the database that holds the tables of <see cref="AccountSchema"/>.</param>
+internal sealed class PostgresAccountStore(NpgsqlDataSource dataSource) : IAccountStore
+{
+ /// <summary>How often a creation is tried again when it was refused and no rule that refuses it is found.</summary>
+ private const int CreateAttempts = 3;
+
+ /// <summary>The columns of an account, in the order <see cref="ReadAccount"/> reads them.</summary>
+ private const string AccountColumns =
+ "id, account_name, normalized_account_name, email, normalized_email, google_subject, password_hash, created_at";
+
+ /// <inheritdoc/>
+ public async ValueTask<CreateAccountResult> CreateAsync(AccountRecord account, CancellationToken cancellationToken)
+ {
+ AccountRecord stored = account with { CreatedAt = ToStoredPrecision(account.CreatedAt) };
+
+ for (int attempt = 0; attempt < CreateAttempts; attempt++)
+ {
+ // A row that breaks a unique index is skipped instead of raising an error, so that a taken name is an
+ // ordinary answer and the transaction log holds no failed statement for it.
+ await using (NpgsqlCommand insert = dataSource.CreateCommand(
+ $"INSERT INTO accounts ({AccountColumns}) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) ON CONFLICT DO NOTHING"))
+ {
+ insert.Parameters.AddWithValue(stored.Id);
+ insert.Parameters.AddWithValue(stored.AccountName);
+ insert.Parameters.AddWithValue(stored.NormalizedAccountName);
+ insert.Parameters.AddWithValue(stored.Email);
+ insert.Parameters.AddWithValue(stored.NormalizedEmail);
+ insert.Parameters.AddWithValue(NullableText(stored.GoogleSubject));
+ insert.Parameters.AddWithValue(NullableText(stored.PasswordHash));
+ insert.Parameters.AddWithValue(stored.CreatedAt);
+ if (await insert.ExecuteNonQueryAsync(cancellationToken) == 1)
+ {
+ return CreateAccountResult.Created(stored);
+ }
+ }
+
+ // The rules are reported in one fixed order, the order of the in-memory store, whichever index the
+ // database happened to check first.
+ await using NpgsqlCommand taken = dataSource.CreateCommand(
+ """
+ SELECT EXISTS (SELECT 1 FROM accounts WHERE google_subject = $1),
+ EXISTS (SELECT 1 FROM accounts WHERE normalized_account_name = $2),
+ EXISTS (SELECT 1 FROM accounts WHERE normalized_email = $3)
+ """);
+ taken.Parameters.AddWithValue(NullableText(stored.GoogleSubject));
+ taken.Parameters.AddWithValue(stored.NormalizedAccountName);
+ taken.Parameters.AddWithValue(stored.NormalizedEmail);
+ await using NpgsqlDataReader reader = await taken.ExecuteReaderAsync(cancellationToken);
+ await reader.ReadAsync(cancellationToken);
+ if (reader.GetBoolean(0))
+ {
+ return CreateAccountResult.GoogleSubjectUnavailable();
+ }
+
+ if (reader.GetBoolean(1))
+ {
+ return CreateAccountResult.AccountNameUnavailable();
+ }
+
+ if (reader.GetBoolean(2))
+ {
+ return CreateAccountResult.EmailUnavailable();
+ }
+ }
+
+ // Accounts are never deleted, so a refused row always has a row that refuses it. Reaching this line means
+ // the identifier itself was taken, which a random identifier does not do.
+ throw new InvalidOperationException("The account store refused an account without a rule that explains it.");
+ }
+
+ /// <inheritdoc/>
+ public ValueTask<AccountRecord?> FindByEmailAsync(string normalizedEmail, CancellationToken cancellationToken) =>
+ FindAccountAsync("normalized_email", normalizedEmail, cancellationToken);
+
+ /// <inheritdoc/>
+ public ValueTask<AccountRecord?> FindByGoogleSubjectAsync(
+ string googleSubject,
+ CancellationToken cancellationToken) =>
+ FindAccountAsync("google_subject", googleSubject, cancellationToken);
+
+ /// <inheritdoc/>
+ public ValueTask<AccountRecord?> FindByIdAsync(Guid id, CancellationToken cancellationToken) =>
+ FindAccountAsync("id", id, cancellationToken);
+
+ /// <inheritdoc/>
+ public async ValueTask StoreSessionAsync(SessionRecord session, CancellationToken cancellationToken)
+ {
+ await using NpgsqlCommand command = dataSource.CreateCommand(
+ """
+ INSERT INTO sessions (token_digest, account_id, created_at, expires_at) VALUES ($1, $2, $3, $4)
+ ON CONFLICT (token_digest) DO UPDATE
+ SET account_id = EXCLUDED.account_id, created_at = EXCLUDED.created_at, expires_at = EXCLUDED.expires_at
+ """);
+ command.Parameters.AddWithValue(session.TokenDigest);
+ command.Parameters.AddWithValue(session.AccountId);
+ command.Parameters.AddWithValue(ToStoredPrecision(session.CreatedAt));
+ command.Parameters.AddWithValue(ToStoredPrecision(session.ExpiresAt));
+ await command.ExecuteNonQueryAsync(cancellationToken);
+ }
+
+ /// <inheritdoc/>
+ public async ValueTask<SessionRecord?> FindSessionAsync(byte[] tokenDigest, CancellationToken cancellationToken)
+ {
+ await using NpgsqlCommand command = dataSource.CreateCommand(
+ "SELECT token_digest, account_id, created_at, expires_at FROM sessions WHERE token_digest = $1");
+ command.Parameters.AddWithValue(tokenDigest);
+ await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
+ if (!await reader.ReadAsync(cancellationToken))
+ {
+ return null;
+ }
+
+ return new SessionRecord(
+ reader.GetFieldValue<byte[]>(0),
+ reader.GetGuid(1),
+ reader.GetFieldValue<DateTimeOffset>(2),
+ reader.GetFieldValue<DateTimeOffset>(3));
+ }
+
+ /// <inheritdoc/>
+ public async ValueTask RevokeSessionAsync(byte[] tokenDigest, CancellationToken cancellationToken)
+ {
+ await using NpgsqlCommand command = dataSource.CreateCommand("DELETE FROM sessions WHERE token_digest = $1");
+ command.Parameters.AddWithValue(tokenDigest);
+ await command.ExecuteNonQueryAsync(cancellationToken);
+ }
+
+ /// <inheritdoc/>
+ public async ValueTask<int> RemoveExpiredSessionsAsync(DateTimeOffset now, CancellationToken cancellationToken)
+ {
+ await using NpgsqlCommand command = dataSource.CreateCommand("DELETE FROM sessions WHERE expires_at <= $1");
+ command.Parameters.AddWithValue(now.ToUniversalTime());
+ return await command.ExecuteNonQueryAsync(cancellationToken);
+ }
+
+ /// <summary>Finds the one account whose column has a value.</summary>
+ /// <typeparam name="TKey">Type of the value.</typeparam>
+ /// <param name="column">Name of a column with a unique index. It is written into the statement.</param>
+ /// <param name="value">The value to look for.</param>
+ /// <param name="cancellationToken">Cancels the operation.</param>
+ /// <returns>The account, or <see langword="null"/> when no account has the value.</returns>
+ private async ValueTask<AccountRecord?> FindAccountAsync<TKey>(
+ string column,
+ TKey value,
+ CancellationToken cancellationToken)
+ where TKey : notnull
+ {
+ await using NpgsqlCommand command = dataSource.CreateCommand(
+ $"SELECT {AccountColumns} FROM accounts WHERE {column} = $1");
+ command.Parameters.AddWithValue(value);
+ await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
+ return await reader.ReadAsync(cancellationToken) ? ReadAccount(reader) : null;
+ }
+
+ /// <summary>Reads the account of the current row; the columns are <see cref="AccountColumns"/>.</summary>
+ /// <param name="reader">A reader positioned on a row.</param>
+ /// <returns>The account of the row.</returns>
+ private static AccountRecord ReadAccount(NpgsqlDataReader reader) => new(
+ reader.GetGuid(0),
+ reader.GetString(1),
+ reader.GetString(2),
+ reader.GetString(3),
+ reader.GetString(4),
+ reader.IsDBNull(5) ? null : reader.GetString(5),
+ reader.IsDBNull(6) ? null : reader.GetString(6),
+ reader.GetFieldValue<DateTimeOffset>(7));
+
+ /// <summary>Converts an optional text to a parameter value.</summary>
+ /// <param name="value">The text, or <see langword="null"/>.</param>
+ /// <returns>The text, or the database null.</returns>
+ private static object NullableText(string? value) => value is null ? DBNull.Value : value;
+
+ /// <summary>Cuts a moment to the microsecond PostgreSQL keeps, in UTC.</summary>
+ /// <param name="moment">The moment to store.</param>
+ /// <returns>The moment as the database will return it.</returns>
+ internal static DateTimeOffset ToStoredPrecision(DateTimeOffset moment)
+ {
+ DateTimeOffset utc = moment.ToUniversalTime();
+ return utc.AddTicks(-(utc.Ticks % (TimeSpan.TicksPerMillisecond / 1000)));
+ }
+}