ASP.NET Core 申請・承認機能を作る① 状態とDBを設計する

{
  "status": "Approved",
  "applicantId": "任意の利用者ID"
}

public enum ApprovalStatus { Draft, Submitted, Approved, Rejected }

public sealed record ApprovalRequest(
    Guid Id, Guid OrganizationId, Guid ApplicantId,
    string Title, string Description, ApprovalStatus Status,
    long Version, string? DecisionReason);

public sealed record Member(Guid UserId, Guid OrganizationId, bool CanApprove);
public sealed record AllowedActions(bool CanEdit, bool CanSubmit, bool CanApprove, bool CanReject);

public static class ApprovalPolicy
{
    public static bool CanView(ApprovalRequest item, Member member) =>
        item.OrganizationId == member.OrganizationId &&
        (item.ApplicantId == member.UserId ||
         (member.CanApprove && item.Status != ApprovalStatus.Draft));

    public static AllowedActions GetActions(ApprovalRequest item, Member member)
    {
        var sameOrganization = item.OrganizationId == member.OrganizationId;
        var ownDraft = sameOrganization && item.ApplicantId == member.UserId &&
            item.Status == ApprovalStatus.Draft;
        var canDecide = sameOrganization && member.CanApprove &&
            item.ApplicantId != member.UserId && item.Status == ApprovalStatus.Submitted;
        return new(ownDraft, ownDraft, canDecide, canDecide);
    }
}

public sealed record ApprovalHistory(
    Guid RequestId, long Version, Guid ActorId, string Action,
    ApprovalStatus FromStatus, ApprovalStatus ToStatus,
    string? Reason, DateTimeOffset OccurredAt);

public sealed class WorkflowError(int status, string code) : Exception(code)
{
    public int Status { get; } = status;
    public string Code { get; } = code;
}

CREATE TABLE organization_member (
    organization_id uuid NOT NULL,
    user_id uuid NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    can_approve boolean NOT NULL DEFAULT false,
    PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE approval_request (
    id uuid PRIMARY KEY,
    organization_id uuid NOT NULL,
    applicant_id uuid NOT NULL,
    title text NOT NULL CHECK (char_length(title) <= 100),
    description text NOT NULL CHECK (char_length(description) <= 2000),
    status text NOT NULL CHECK (
        status IN ('Draft', 'Submitted', 'Approved', 'Rejected')
    ),
    version bigint NOT NULL CHECK (version > 0),
    decision_reason text,
    FOREIGN KEY (organization_id, applicant_id)
        REFERENCES organization_member(organization_id, user_id),
    CHECK (
        (status = 'Rejected' AND decision_reason IS NOT NULL
            AND char_length(btrim(decision_reason)) BETWEEN 1 AND 500)
        OR (status <> 'Rejected' AND decision_reason IS NULL)
    )
);

CREATE INDEX approval_request_org_status_idx
    ON approval_request(organization_id, status);

CREATE TABLE approval_history (
    request_id uuid NOT NULL REFERENCES approval_request(id),
    version bigint NOT NULL,
    actor_id uuid NOT NULL,
    action text NOT NULL CHECK (
        action IN ('created', 'edited', 'submitted', 'approved', 'rejected')
    ),
    from_status text NOT NULL,
    to_status text NOT NULL,
    reason text,
    occurred_at timestamptz NOT NULL,
    PRIMARY KEY (request_id, version)
);