Refactor db schema and add audits

6 files changed, 104 insertions, 25 deletions

diff --git a/api/WhatApi/Tables/AuditTrail.cs b/api/WhatApi/Tables/AuditTrail.cs
new file mode 100644
index 0000000..9613af4
--- /dev/null
+++ b/api/WhatApi/Tables/AuditTrail.cs
@@ -0,0 +1,39 @@
+namespace WhatApi.Tables;
+
+public class AuditTrail
+{
+    public Guid Id { get; init; }
+    public Guid? UserId { get; init; }
+    public required string EntityName { get; init; }
+    public string? PrimaryKey { get; set; }
+    public TrailType TrailType { get; set; }
+    public DateTimeOffset DateUtc { get; init; }
+
+    public Dictionary<string, object?> OldValues { get; init; } = [];
+    public Dictionary<string, object?> NewValues { get; init; } = [];
+    public List<string> ChangedColumns { get; init; } = [];
+}
+
+public class AuditTrailConfiguration : IEntityTypeConfiguration<AuditTrail>
+{
+    public void Configure(EntityTypeBuilder<AuditTrail> builder)
+    {
+        builder.ToTable("audit_trails");
+        builder.HasIndex(e => e.EntityName);
+        builder.Property(e => e.EntityName).HasMaxLength(100).IsRequired();
+        builder.Property(e => e.PrimaryKey).HasMaxLength(100);
+        builder.Property(e => e.DateUtc).IsRequired();
+        builder.Property(e => e.TrailType).HasConversion<string>();
+        builder.Property(e => e.OldValues).HasColumnType("jsonb");
+        builder.Property(e => e.NewValues).HasColumnType("jsonb");
+        builder.Property(e => e.ChangedColumns).HasColumnType("jsonb");
+    }
+}
+
+public enum TrailType : byte
+{
+    None = 0,
+    Create = 1,
+    Update = 2,
+    Delete = 3
+}
\ No newline at end of file
diff --git a/api/WhatApi/Tables/Content.cs b/api/WhatApi/Tables/Content.cs
index 79f2579..0b26b82 100644
--- a/api/WhatApi/Tables/Content.cs
+++ b/api/WhatApi/Tables/Content.cs
@@ -2,11 +2,23 @@ using System.Net;
 
 namespace WhatApi.Tables;
 
-public class Content
+public class Content : IAuditableEntity
 {
     public Guid Id { get; set; }
-    public string Mime { get; set; }
-    public DateTime Created { get; set; }
+    public required string Mime { get; set; }
     public Guid BlobId { get; set; }
-    public IPAddress Ip { get; set; }
+    public required IPAddress Ip { get; set; }
+    public DateTimeOffset CreatedAtUtc { get; set; }
+    public DateTimeOffset? UpdatedAtUtc { get; set; }
+    public Guid CreatedBy { get; set; }
+    public Guid? UpdatedBy { get; set; }
+}
+
+public class ContentConfiguration : IEntityTypeConfiguration<Content>
+{
+    public void Configure(EntityTypeBuilder<Content> builder) {
+        builder.HasKey(x => x.Id);
+        builder.Property(x => x.Mime).HasMaxLength(100).IsRequired();
+        builder.ToTable("content");
+    }
 }
\ No newline at end of file
diff --git a/api/WhatApi/Tables/IAuditableEntity.cs b/api/WhatApi/Tables/IAuditableEntity.cs
new file mode 100644
index 0000000..a11e7f2
--- /dev/null
+++ b/api/WhatApi/Tables/IAuditableEntity.cs
@@ -0,0 +1,9 @@
+namespace WhatApi.Tables;
+
+public interface IAuditableEntity
+{
+    public DateTimeOffset CreatedAtUtc { get; set; }
+    public DateTimeOffset? UpdatedAtUtc { get; set; }
+    public Guid CreatedBy { get; set; }
+    public Guid? UpdatedBy { get; set; }
+}
\ No newline at end of file
diff --git a/api/WhatApi/Tables/Place.cs b/api/WhatApi/Tables/Place.cs
index ff95c96..abbd71a 100644
--- a/api/WhatApi/Tables/Place.cs
+++ b/api/WhatApi/Tables/Place.cs
@@ -1,11 +1,25 @@
-using NetTopologySuite.Geometries;
-
 namespace WhatApi.Tables;
 
-public class Place
+public class Place : IAuditableEntity
 {
     public Guid Id { get; set; }
     public Guid ContentId { get; set; }
-    public Content Content { get; set; }
+    public Content Content { get; set; } = null!;
     public required Point Location { get; set; }
+    public DateTimeOffset CreatedAtUtc { get; set; }
+    public DateTimeOffset? UpdatedAtUtc { get; set; }
+    public Guid CreatedBy { get; set; }
+    public Guid? UpdatedBy { get; set; }
+}
+
+public class PlaceConfiguration : IEntityTypeConfiguration<Place>
+{
+    public void Configure(EntityTypeBuilder<Place> builder) {
+        builder.ToTable("place");
+        builder.HasKey(x => x.Id);
+        builder.Property(x => x.Location).IsRequired();
+        builder.HasOne(x => x.Content);
+        builder.Property(x => x.Location).HasColumnType($"geometry(point,{Constants.Wgs84SpatialReferenceId})");
+        builder.HasIndex(x => x.Location).HasMethod("gist");
+    }
 }
\ No newline at end of file
diff --git a/api/WhatApi/Tables/Session.cs b/api/WhatApi/Tables/Session.cs
deleted file mode 100644
index a0affa8..0000000
--- a/api/WhatApi/Tables/Session.cs
+++ /dev/null
@@ -1,10 +0,0 @@
-namespace WhatApi.Tables;
-
-public class Session
-{
-    public Guid Id { get; set; }
-    public string Token { get; set; }
-    public DateTime Created { get; set; }
-    public DateTime? Expires { get; set; }
-    public User User { get; set; }
-}
\ No newline at end of file
diff --git a/api/WhatApi/Tables/User.cs b/api/WhatApi/Tables/User.cs
index 0ebe9b6..5c068b2 100644
--- a/api/WhatApi/Tables/User.cs
+++ b/api/WhatApi/Tables/User.cs
@@ -1,12 +1,27 @@
 namespace WhatApi.Tables;
 
-public class User
+public class User : IAuditableEntity
 {
     public Guid Id { get; set; }
-    public string Name { get; set; }
-    public string Email { get; set; }
-    public string Password { get; set; }
-    public DateTime Created { get; set; }
-    public DateTime? LastSeen { get; set; }
-    public IEnumerable<Place> Places { get; set; }
+    public required string Name { get; set; }
+    public required string Email { get; set; }
+    public required string Password { get; set; }
+    public DateTimeOffset? LastSeen { get; set; }
+    public IEnumerable<Place> Places { get; set; } = null!;
+    public DateTimeOffset CreatedAtUtc { get; set; }
+    public DateTimeOffset? UpdatedAtUtc { get; set; }
+    public Guid CreatedBy { get; set; }
+    public Guid? UpdatedBy { get; set; }
+}
+
+public class UserConfiguration : IEntityTypeConfiguration<User>
+{
+
+    public void Configure(EntityTypeBuilder<User> builder) {
+        builder.HasKey(x => x.Id);
+        builder.Property(x => x.Name).HasMaxLength(50);
+        builder.Property(x => x.Email).HasMaxLength(100);
+        builder.HasMany(x => x.Places);
+        builder.ToTable("user");
+    }
 }
\ No newline at end of file