Database Auditing

[ktwrd]

Implementing an Audit Trail for user actions in the database would be a smart idea, especially for when Cockatoo (maybe) goes open-source and when more than just intcoms and myself have access to the dashboard.

Similar database models should be created for events that occour, like the Bullseye cache being refresh for an application, or a user creating a service account.

Proposed database model for Document audit events

public class DocumentAuditModel : BaseGuidModel
{
    public const string CollectionName = "audit_log;

    [JsonIgnore]
    public BsonTimestamp Timestamp { get; set; }
    [BsonIgnore]
    [JsonPropertyName(nameof(Timestamp))]
    public long TimestampJson => Timestamp.Value;

    public DocumentAuditActionKind Action { get; set; }

    /// <summary>
    /// Foreign Key to <see cref="UserModel.Id"/>
    /// </summary>
    public string UserId { get; set; }
    /// <summary>
    /// Foreign Key to <see cref="UserSessionModel.Id"/>
    /// </summary>
    public string UserSessionId { get; set; }
    /// <summary>
    /// Name of the Collection that had an event happen on it.
    /// </summary>
    public string CollectionName { get; set; }
    /// <summary>
    /// <see cref="Type.ToString()"/> of the document.
    /// </summary>
    public string DocumentType { get; set; }
    /// <summary>
    /// JSON of the document before the action was done.
    /// </summary>
    /// <remarks>
    /// This will be <see langword="null"/> when <see cref="Action"/> is <see cref="DocumentAuditActionKind.Insert"/>
    /// </remarks>
    public string? BeforeJson { get; set; }
    /// <summary>
    /// JSON of the document after the action was done.
    /// </summary>
    /// <remarks>
    /// This will be <see langword="null"/> when <see cref="Action"/> is <see cref="DocumentAuditActionKind.Delete"/>
    /// </remarks>
    public string? AfterJson { get; set; }
}

public enum DocumentAuditActionKind
{
    /// <summary>
    /// New document was inserted. <see cref="DocumentAuditModel.BeforeJson"/> will always be null.
    /// </summary>
    Insert,
    Update,
    Delete
}

[ktwrd]

I've written up a small model for Event Auditing. It should be good enough, maybe an attribute on EventAuditKind to decide what the JsonArguments is actually serialized as so a non-generic method can be used for fetching the parameters.

C# Code

public class EventAuditModel : BaseGuidModel
{
    public const string CollectionName = "audit_event";

    [JsonIgnore]
    public BsonTimestamp Timestamp { get; set; }
    [BsonIgnore]
    [JsonPropertyName(nameof(Timestamp))]
    public long TimestampJson => Timestamp.Value;

    /// <summary>
    /// Foreign Key to <see cref="UserModel.Id"/>
    /// </summary>
    public string UserId { get; set; }
    /// <summary>
    /// Foreign Key to <see cref="UserSessionModel.Id"/>
    /// </summary>
    public string UserSessionId { get; set; }

    public EventAuditKind Kind { get; set; }
    /// <summary>
    /// Arguments that were used for this event serialized into JSON.
    /// </summary>
    [BsonIgnoreIfNull]
    public string? JsonArguments { get; set; } = null;

    public T? ParseArguments<T>()
        where T : class, new()
    {
        if (string.IsNullOrEmpty(JsonArguments)) return null;
        var data = JsonSerializer.Deserialize<T>(JsonArguments, CoreContext.SerializerOptions);
        return data;
    }
}

public EventAuditKind
{
    /// <summary>
    /// <see cref="EventAuditModel.JsonArguments"/> will be an instance of <see cref="EventAuditBullseyeCacheArguments"/>
    /// </summary>
    UpdateBullseyeCache

    // etc...
}