ASP.NET Core 二重送信に強いAPIを作る② 受付結果をDBに保存する

CREATE TABLE inquiry_receipt (
    id uuid PRIMARY KEY,
    user_id uuid NOT NULL,
    operation_key uuid NOT NULL,
    subject text NOT NULL CHECK (char_length(subject) BETWEEN 1 AND 100),
    message text NOT NULL CHECK (char_length(message) BETWEEN 1 AND 2000),
    received_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, operation_key)
);

using System.Data.Common;

public sealed class ReceiptStore(DbDataSource dataSource)
{
    public async Task<Receipt> GetOrCreateAsync(
        Guid userId, Guid key, InquiryBody body, CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        await using (var insert = connection.CreateCommand())
        {
            insert.CommandText = """
                INSERT INTO inquiry_receipt
                    (id, user_id, operation_key, subject, message)
                VALUES (@id, @user_id, @operation_key, @subject, @message)
                ON CONFLICT (user_id, operation_key) DO NOTHING
                RETURNING id, subject, message, received_at;
                """;
            insert.CommandTimeout = 10;
            Add(insert, "id", Guid.NewGuid());
            Add(insert, "user_id", userId);
            Add(insert, "operation_key", key);
            Add(insert, "subject", body.Subject);
            Add(insert, "message", body.Message);
            await using var reader = await insert.ExecuteReaderAsync(ct);
            if (await reader.ReadAsync(ct)) return Read(reader);
        }

        // INSERTとは別のSQL文で、先行する処理が保存した行を読み取る。
        await using var select = connection.CreateCommand();
        select.CommandText = """
            SELECT id, subject, message, received_at
            FROM inquiry_receipt
            WHERE user_id = @user_id AND operation_key = @operation_key;
            """;
        select.CommandTimeout = 10;
        Add(select, "user_id", userId);
        Add(select, "operation_key", key);
        await using var existing = await select.ExecuteReaderAsync(ct);
        if (!await existing.ReadAsync(ct))
            throw new InvalidOperationException("受付結果を取得できませんでした。");
        return Read(existing);
    }

    private static void Add(DbCommand command, string name, object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
    }

    private static Receipt Read(DbDataReader reader) => new(
        reader.GetGuid(0), reader.GetString(1), reader.GetString(2),
        reader.GetFieldValue<DateTimeOffset>(3));
}

using System.Security.Claims;
using Microsoft.AspNetCore.Antiforgery;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;

[ApiController]
[Authorize]
[AutoValidateAntiforgeryToken]
[Route("api/inquiries")]
[ResponseCache(NoStore = true, Location = ResponseCacheLocation.None)]
public sealed class InquiriesController(ReceiptStore store) : ControllerBase
{
    [HttpGet("csrf")]
    public IActionResult Csrf([FromServices] IAntiforgery antiforgery) =>
        Ok(new { token = antiforgery.GetAndStoreTokens(HttpContext).RequestToken });

    [HttpPost]
    public async Task<IActionResult> Create(
        InquiryInput input, CancellationToken ct)
    {
        if (!Guid.TryParse(User.FindFirstValue(ClaimTypes.NameIdentifier), out var userId))
            return Unauthorized();
        var values = Request.Headers["Idempotency-Key"];
        if (values.Count != 1 || !Guid.TryParseExact(values[0], "D", out var key) || key == Guid.Empty)
            return Problem(statusCode: 400, title: "操作キーが不正です。");
        var body = InquiryRules.Normalize(input);
        if (!InquiryRules.IsValid(body))
            return Problem(statusCode: 400, title: "件名は1〜100文字、本文は1〜2000文字で入力してください。");

        var receipt = await store.GetOrCreateAsync(userId, key, body, ct);
        if (!InquiryRules.Matches(receipt, body))
            return Problem(statusCode: 409, title: "同じ操作キーで異なる内容は送信できません。");

        return StatusCode(201, new ReceiptResult(receipt.Id, receipt.ReceivedAt));
    }
}

using System.Data.Common;
using Npgsql;

builder.Services.AddSingleton<DbDataSource>(_ =>
    NpgsqlDataSource.Create(
        builder.Configuration.GetConnectionString("App")
        ?? throw new InvalidOperationException("DB接続設定がありません。")));
builder.Services.AddScoped<ReceiptStore>();
builder.Services.AddControllersWithViews();
builder.Services.AddAntiforgery(options =>
{
    options.HeaderName = "X-CSRF-TOKEN";
    options.Cookie.SecurePolicy = CookieSecurePolicy.Always;
});