Basic push notification system for service alerts

Co-authored-by: Copilot <copilot@github.com>

7 files changed, 600 insertions, 1 deletions

diff --git a/src/Enmarcha.Backend/Data/AppDbContext.cs b/src/Enmarcha.Backend/Data/AppDbContext.cs
index d5a29ee..e191b26 100644
--- a/src/Enmarcha.Backend/Data/AppDbContext.cs
+++ b/src/Enmarcha.Backend/Data/AppDbContext.cs
@@ -14,6 +14,7 @@ public class AppDbContext : IdentityDbContext<IdentityUser>
     }
 
     public DbSet<ServiceAlert> ServiceAlerts { get; set; }
+    public DbSet<PushSubscription> PushSubscriptions { get; set; }
 
     protected override void OnModelCreating(ModelBuilder builder)
     {
@@ -72,5 +73,11 @@ public class AppDbContext : IdentityDbContext<IdentityUser>
                     v => JsonSerializer.Deserialize<List<string>>(v, (JsonSerializerOptions?)null) ?? new List<string>(),
                     JsonComparer<List<string>>());
         });
+
+        builder.Entity<PushSubscription>(b =>
+        {
+            b.HasKey(x => x.Id);
+            b.HasIndex(x => x.Endpoint).IsUnique();
+        });
     }
 }
diff --git a/src/Enmarcha.Backend/Data/Migrations/20260401135403_AddPushNotifications.Designer.cs b/src/Enmarcha.Backend/Data/Migrations/20260401135403_AddPushNotifications.Designer.cs
new file mode 100644
index 0000000..5bedd08
--- /dev/null
+++ b/src/Enmarcha.Backend/Data/Migrations/20260401135403_AddPushNotifications.Designer.cs
@@ -0,0 +1,440 @@
+// <auto-generated />
+using System;
+using Enmarcha.Backend.Data;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Infrastructure;
+using Microsoft.EntityFrameworkCore.Migrations;
+using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
+using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;
+
+#nullable disable
+
+namespace Data.Migrations
+{
+    [DbContext(typeof(AppDbContext))]
+    [Migration("20260401135403_AddPushNotifications")]
+    partial class AddPushNotifications
+    {
+        /// <inheritdoc />
+        protected override void BuildTargetModel(ModelBuilder modelBuilder)
+        {
+#pragma warning disable 612, 618
+            modelBuilder
+                .HasAnnotation("ProductVersion", "10.0.3")
+                .HasAnnotation("Relational:MaxIdentifierLength", 63);
+
+            NpgsqlModelBuilderExtensions.HasPostgresExtension(modelBuilder, "postgis");
+            NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder);
+
+            modelBuilder.Entity("Enmarcha.Backend.Data.Models.PushSubscription", b =>
+                {
+                    b.Property<Guid>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnType("uuid")
+                        .HasColumnName("id");
+
+                    b.Property<string>("AuthKey")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("auth_key");
+
+                    b.Property<DateTime>("CreatedAt")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("created_at");
+
+                    b.Property<string>("Endpoint")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("endpoint");
+
+                    b.Property<string>("P256DhKey")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("p256dh_key");
+
+                    b.HasKey("Id")
+                        .HasName("pK_push_subscriptions");
+
+                    b.HasIndex("Endpoint")
+                        .IsUnique()
+                        .HasDatabaseName("iX_push_subscriptions_endpoint");
+
+                    b.ToTable("push_subscriptions", (string)null);
+                });
+
+            modelBuilder.Entity("Enmarcha.Backend.Data.Models.ServiceAlert", b =>
+                {
+                    b.Property<string>("Id")
+                        .HasColumnType("text")
+                        .HasColumnName("id");
+
+                    b.Property<DateTime?>("ActiveNotifiedAt")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("active_notified_at");
+
+                    b.Property<int>("Cause")
+                        .HasColumnType("integer")
+                        .HasColumnName("cause");
+
+                    b.Property<string>("Description")
+                        .IsRequired()
+                        .HasColumnType("jsonb")
+                        .HasColumnName("description");
+
+                    b.Property<int>("Effect")
+                        .HasColumnType("integer")
+                        .HasColumnName("effect");
+
+                    b.Property<DateTime>("EventEndDate")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("event_end_date");
+
+                    b.Property<DateTime>("EventStartDate")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("event_start_date");
+
+                    b.Property<string>("Header")
+                        .IsRequired()
+                        .HasColumnType("jsonb")
+                        .HasColumnName("header");
+
+                    b.Property<DateTime>("HiddenDate")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("hidden_date");
+
+                    b.Property<string>("InfoUrls")
+                        .IsRequired()
+                        .HasColumnType("jsonb")
+                        .HasColumnName("info_urls");
+
+                    b.Property<DateTime>("InsertedDate")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("inserted_date");
+
+                    b.Property<DateTime?>("PreNoticeNotifiedAt")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("pre_notice_notified_at");
+
+                    b.Property<DateTime>("PublishDate")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("publish_date");
+
+                    b.Property<string>("Selectors")
+                        .IsRequired()
+                        .HasColumnType("jsonb")
+                        .HasColumnName("selectors");
+
+                    b.Property<int>("Version")
+                        .HasColumnType("integer")
+                        .HasColumnName("version");
+
+                    b.HasKey("Id")
+                        .HasName("pK_service_alerts");
+
+                    b.ToTable("service_alerts", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityRole", b =>
+                {
+                    b.Property<string>("Id")
+                        .HasColumnType("text")
+                        .HasColumnName("id");
+
+                    b.Property<string>("ConcurrencyStamp")
+                        .IsConcurrencyToken()
+                        .HasColumnType("text")
+                        .HasColumnName("concurrencyStamp");
+
+                    b.Property<string>("Name")
+                        .HasMaxLength(256)
+                        .HasColumnType("character varying(256)")
+                        .HasColumnName("name");
+
+                    b.Property<string>("NormalizedName")
+                        .HasMaxLength(256)
+                        .HasColumnType("character varying(256)")
+                        .HasColumnName("normalizedName");
+
+                    b.HasKey("Id")
+                        .HasName("pK_roles");
+
+                    b.HasIndex("NormalizedName")
+                        .IsUnique()
+                        .HasDatabaseName("RoleNameIndex");
+
+                    b.ToTable("roles", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityRoleClaim<string>", b =>
+                {
+                    b.Property<int>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnType("integer")
+                        .HasColumnName("id");
+
+                    NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property<int>("Id"));
+
+                    b.Property<string>("ClaimType")
+                        .HasColumnType("text")
+                        .HasColumnName("claimType");
+
+                    b.Property<string>("ClaimValue")
+                        .HasColumnType("text")
+                        .HasColumnName("claimValue");
+
+                    b.Property<string>("RoleId")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("roleId");
+
+                    b.HasKey("Id")
+                        .HasName("pK_role_claims");
+
+                    b.HasIndex("RoleId")
+                        .HasDatabaseName("iX_role_claims_roleId");
+
+                    b.ToTable("role_claims", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUser", b =>
+                {
+                    b.Property<string>("Id")
+                        .HasColumnType("text")
+                        .HasColumnName("id");
+
+                    b.Property<int>("AccessFailedCount")
+                        .HasColumnType("integer")
+                        .HasColumnName("accessFailedCount");
+
+                    b.Property<string>("ConcurrencyStamp")
+                        .IsConcurrencyToken()
+                        .HasColumnType("text")
+                        .HasColumnName("concurrencyStamp");
+
+                    b.Property<string>("Email")
+                        .HasMaxLength(256)
+                        .HasColumnType("character varying(256)")
+                        .HasColumnName("email");
+
+                    b.Property<bool>("EmailConfirmed")
+                        .HasColumnType("boolean")
+                        .HasColumnName("emailConfirmed");
+
+                    b.Property<bool>("LockoutEnabled")
+                        .HasColumnType("boolean")
+                        .HasColumnName("lockoutEnabled");
+
+                    b.Property<DateTimeOffset?>("LockoutEnd")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("lockoutEnd");
+
+                    b.Property<string>("NormalizedEmail")
+                        .HasMaxLength(256)
+                        .HasColumnType("character varying(256)")
+                        .HasColumnName("normalizedEmail");
+
+                    b.Property<string>("NormalizedUserName")
+                        .HasMaxLength(256)
+                        .HasColumnType("character varying(256)")
+                        .HasColumnName("normalizedUserName");
+
+                    b.Property<string>("PasswordHash")
+                        .HasColumnType("text")
+                        .HasColumnName("passwordHash");
+
+                    b.Property<string>("PhoneNumber")
+                        .HasColumnType("text")
+                        .HasColumnName("phoneNumber");
+
+                    b.Property<bool>("PhoneNumberConfirmed")
+                        .HasColumnType("boolean")
+                        .HasColumnName("phoneNumberConfirmed");
+
+                    b.Property<string>("SecurityStamp")
+                        .HasColumnType("text")
+                        .HasColumnName("securityStamp");
+
+                    b.Property<bool>("TwoFactorEnabled")
+                        .HasColumnType("boolean")
+                        .HasColumnName("twoFactorEnabled");
+
+                    b.Property<string>("UserName")
+                        .HasMaxLength(256)
+                        .HasColumnType("character varying(256)")
+                        .HasColumnName("userName");
+
+                    b.HasKey("Id")
+                        .HasName("pK_users");
+
+                    b.HasIndex("NormalizedEmail")
+                        .HasDatabaseName("EmailIndex");
+
+                    b.HasIndex("NormalizedUserName")
+                        .IsUnique()
+                        .HasDatabaseName("UserNameIndex");
+
+                    b.ToTable("users", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserClaim<string>", b =>
+                {
+                    b.Property<int>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnType("integer")
+                        .HasColumnName("id");
+
+                    NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property<int>("Id"));
+
+                    b.Property<string>("ClaimType")
+                        .HasColumnType("text")
+                        .HasColumnName("claimType");
+
+                    b.Property<string>("ClaimValue")
+                        .HasColumnType("text")
+                        .HasColumnName("claimValue");
+
+                    b.Property<string>("UserId")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("userId");
+
+                    b.HasKey("Id")
+                        .HasName("pK_user_claims");
+
+                    b.HasIndex("UserId")
+                        .HasDatabaseName("iX_user_claims_userId");
+
+                    b.ToTable("user_claims", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserLogin<string>", b =>
+                {
+                    b.Property<string>("LoginProvider")
+                        .HasColumnType("text")
+                        .HasColumnName("loginProvider");
+
+                    b.Property<string>("ProviderKey")
+                        .HasColumnType("text")
+                        .HasColumnName("providerKey");
+
+                    b.Property<string>("ProviderDisplayName")
+                        .HasColumnType("text")
+                        .HasColumnName("providerDisplayName");
+
+                    b.Property<string>("UserId")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("userId");
+
+                    b.HasKey("LoginProvider", "ProviderKey")
+                        .HasName("pK_user_logins");
+
+                    b.HasIndex("UserId")
+                        .HasDatabaseName("iX_user_logins_userId");
+
+                    b.ToTable("user_logins", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserRole<string>", b =>
+                {
+                    b.Property<string>("UserId")
+                        .HasColumnType("text")
+                        .HasColumnName("userId");
+
+                    b.Property<string>("RoleId")
+                        .HasColumnType("text")
+                        .HasColumnName("roleId");
+
+                    b.HasKey("UserId", "RoleId")
+                        .HasName("pK_user_roles");
+
+                    b.HasIndex("RoleId")
+                        .HasDatabaseName("iX_user_roles_roleId");
+
+                    b.ToTable("user_roles", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserToken<string>", b =>
+                {
+                    b.Property<string>("UserId")
+                        .HasColumnType("text")
+                        .HasColumnName("userId");
+
+                    b.Property<string>("LoginProvider")
+                        .HasColumnType("text")
+                        .HasColumnName("loginProvider");
+
+                    b.Property<string>("Name")
+                        .HasColumnType("text")
+                        .HasColumnName("name");
+
+                    b.Property<string>("Value")
+                        .HasColumnType("text")
+                        .HasColumnName("value");
+
+                    b.HasKey("UserId", "LoginProvider", "Name")
+                        .HasName("pK_user_tokens");
+
+                    b.ToTable("user_tokens", (string)null);
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityRoleClaim<string>", b =>
+                {
+                    b.HasOne("Microsoft.AspNetCore.Identity.IdentityRole", null)
+                        .WithMany()
+                        .HasForeignKey("RoleId")
+                        .OnDelete(DeleteBehavior.Cascade)
+                        .IsRequired()
+                        .HasConstraintName("fK_role_claims_roles_roleId");
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserClaim<string>", b =>
+                {
+                    b.HasOne("Microsoft.AspNetCore.Identity.IdentityUser", null)
+                        .WithMany()
+                        .HasForeignKey("UserId")
+                        .OnDelete(DeleteBehavior.Cascade)
+                        .IsRequired()
+                        .HasConstraintName("fK_user_claims_users_userId");
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserLogin<string>", b =>
+                {
+                    b.HasOne("Microsoft.AspNetCore.Identity.IdentityUser", null)
+                        .WithMany()
+                        .HasForeignKey("UserId")
+                        .OnDelete(DeleteBehavior.Cascade)
+                        .IsRequired()
+                        .HasConstraintName("fK_user_logins_users_userId");
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserRole<string>", b =>
+                {
+                    b.HasOne("Microsoft.AspNetCore.Identity.IdentityRole", null)
+                        .WithMany()
+                        .HasForeignKey("RoleId")
+                        .OnDelete(DeleteBehavior.Cascade)
+                        .IsRequired()
+                        .HasConstraintName("fK_user_roles_roles_roleId");
+
+                    b.HasOne("Microsoft.AspNetCore.Identity.IdentityUser", null)
+                        .WithMany()
+                        .HasForeignKey("UserId")
+                        .OnDelete(DeleteBehavior.Cascade)
+                        .IsRequired()
+                        .HasConstraintName("fK_user_roles_users_userId");
+                });
+
+            modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserToken<string>", b =>
+                {
+                    b.HasOne("Microsoft.AspNetCore.Identity.IdentityUser", null)
+                        .WithMany()
+                        .HasForeignKey("UserId")
+                        .OnDelete(DeleteBehavior.Cascade)
+                        .IsRequired()
+                        .HasConstraintName("fK_user_tokens_users_userId");
+                });
+#pragma warning restore 612, 618
+        }
+    }
+}
diff --git a/src/Enmarcha.Backend/Data/Migrations/20260401135403_AddPushNotifications.cs b/src/Enmarcha.Backend/Data/Migrations/20260401135403_AddPushNotifications.cs
new file mode 100644
index 0000000..964a86f
--- /dev/null
+++ b/src/Enmarcha.Backend/Data/Migrations/20260401135403_AddPushNotifications.cs
@@ -0,0 +1,74 @@
+using System;
+using Microsoft.EntityFrameworkCore.Migrations;
+
+#nullable disable
+
+namespace Data.Migrations
+{
+    /// <inheritdoc />
+    public partial class AddPushNotifications : Migration
+    {
+        /// <inheritdoc />
+        protected override void Up(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.AddColumn<DateTime>(
+                name: "active_notified_at",
+                table: "service_alerts",
+                type: "timestamp with time zone",
+                nullable: true);
+
+            migrationBuilder.AddColumn<DateTime>(
+                name: "pre_notice_notified_at",
+                table: "service_alerts",
+                type: "timestamp with time zone",
+                nullable: true);
+
+            migrationBuilder.AddColumn<int>(
+                name: "version",
+                table: "service_alerts",
+                type: "integer",
+                nullable: false,
+                defaultValue: 0);
+
+            migrationBuilder.CreateTable(
+                name: "push_subscriptions",
+                columns: table => new
+                {
+                    id = table.Column<Guid>(type: "uuid", nullable: false),
+                    endpoint = table.Column<string>(type: "text", nullable: false),
+                    p256dh_key = table.Column<string>(type: "text", nullable: false),
+                    auth_key = table.Column<string>(type: "text", nullable: false),
+                    created_at = table.Column<DateTime>(type: "timestamp with time zone", nullable: false)
+                },
+                constraints: table =>
+                {
+                    table.PrimaryKey("pK_push_subscriptions", x => x.id);
+                });
+
+            migrationBuilder.CreateIndex(
+                name: "iX_push_subscriptions_endpoint",
+                table: "push_subscriptions",
+                column: "endpoint",
+                unique: true);
+        }
+
+        /// <inheritdoc />
+        protected override void Down(MigrationBuilder migrationBuilder)
+        {
+            migrationBuilder.DropTable(
+                name: "push_subscriptions");
+
+            migrationBuilder.DropColumn(
+                name: "active_notified_at",
+                table: "service_alerts");
+
+            migrationBuilder.DropColumn(
+                name: "pre_notice_notified_at",
+                table: "service_alerts");
+
+            migrationBuilder.DropColumn(
+                name: "version",
+                table: "service_alerts");
+        }
+    }
+}
diff --git a/src/Enmarcha.Backend/Data/Migrations/AppDbContextModelSnapshot.cs b/src/Enmarcha.Backend/Data/Migrations/AppDbContextModelSnapshot.cs
index 88488bf..f02eb20 100644
--- a/src/Enmarcha.Backend/Data/Migrations/AppDbContextModelSnapshot.cs
+++ b/src/Enmarcha.Backend/Data/Migrations/AppDbContextModelSnapshot.cs
@@ -23,12 +23,52 @@ namespace Data.Migrations
             NpgsqlModelBuilderExtensions.HasPostgresExtension(modelBuilder, "postgis");
             NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder);
 
+            modelBuilder.Entity("Enmarcha.Backend.Data.Models.PushSubscription", b =>
+                {
+                    b.Property<Guid>("Id")
+                        .ValueGeneratedOnAdd()
+                        .HasColumnType("uuid")
+                        .HasColumnName("id");
+
+                    b.Property<string>("AuthKey")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("auth_key");
+
+                    b.Property<DateTime>("CreatedAt")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("created_at");
+
+                    b.Property<string>("Endpoint")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("endpoint");
+
+                    b.Property<string>("P256DhKey")
+                        .IsRequired()
+                        .HasColumnType("text")
+                        .HasColumnName("p256dh_key");
+
+                    b.HasKey("Id")
+                        .HasName("pK_push_subscriptions");
+
+                    b.HasIndex("Endpoint")
+                        .IsUnique()
+                        .HasDatabaseName("iX_push_subscriptions_endpoint");
+
+                    b.ToTable("push_subscriptions", (string)null);
+                });
+
             modelBuilder.Entity("Enmarcha.Backend.Data.Models.ServiceAlert", b =>
                 {
                     b.Property<string>("Id")
                         .HasColumnType("text")
                         .HasColumnName("id");
 
+                    b.Property<DateTime?>("ActiveNotifiedAt")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("active_notified_at");
+
                     b.Property<int>("Cause")
                         .HasColumnType("integer")
                         .HasColumnName("cause");
@@ -68,6 +108,10 @@ namespace Data.Migrations
                         .HasColumnType("timestamp with time zone")
                         .HasColumnName("inserted_date");
 
+                    b.Property<DateTime?>("PreNoticeNotifiedAt")
+                        .HasColumnType("timestamp with time zone")
+                        .HasColumnName("pre_notice_notified_at");
+
                     b.Property<DateTime>("PublishDate")
                         .HasColumnType("timestamp with time zone")
                         .HasColumnName("publish_date");
@@ -77,6 +121,10 @@ namespace Data.Migrations
                         .HasColumnType("jsonb")
                         .HasColumnName("selectors");
 
+                    b.Property<int>("Version")
+                        .HasColumnType("integer")
+                        .HasColumnName("version");
+
                     b.HasKey("Id")
                         .HasName("pK_service_alerts");
 
diff --git a/src/Enmarcha.Backend/Data/Models/AlertSelector.cs b/src/Enmarcha.Backend/Data/Models/AlertSelector.cs
index 34b2de3..e2b01f1 100644
--- a/src/Enmarcha.Backend/Data/Models/AlertSelector.cs
+++ b/src/Enmarcha.Backend/Data/Models/AlertSelector.cs
@@ -13,7 +13,8 @@ public class AlertSelector
 
     public static AlertSelector FromStop(string feedId, string stopId) => new() { Raw = $"stop#{feedId}:{stopId}" };
     public static AlertSelector FromRoute(string feedId, string routeId) => new() { Raw = $"route#{feedId}:{routeId}" };
-    public static AlertSelector FromAgency(string feedId) => new() { Raw = $"agency#{feedId}" };
+    /// <param name="agencyGtfsId">Full GTFS agency id in the form <c>feedId:agencyId</c> (e.g. <c>vitrasa:1</c>).</param>
+    public static AlertSelector FromAgency(string agencyGtfsId) => new() { Raw = $"agency#{agencyGtfsId}" };
 
     public override string ToString() => Raw;
 }
diff --git a/src/Enmarcha.Backend/Data/Models/PushSubscription.cs b/src/Enmarcha.Backend/Data/Models/PushSubscription.cs
new file mode 100644
index 0000000..c72c40f
--- /dev/null
+++ b/src/Enmarcha.Backend/Data/Models/PushSubscription.cs
@@ -0,0 +1,20 @@
+using System.ComponentModel.DataAnnotations.Schema;
+
+namespace Enmarcha.Backend.Data.Models;
+
+[Table("push_subscriptions")]
+public class PushSubscription
+{
+    public Guid Id { get; set; }
+
+    /// <summary>Push endpoint URL provided by the browser's push service.</summary>
+    public string Endpoint { get; set; } = string.Empty;
+
+    /// <summary>P-256 DH public key for payload encryption (base64url).</summary>
+    [Column("p256dh_key")] public string P256DhKey { get; set; } = string.Empty;
+
+    /// <summary>Auth secret for payload encryption (base64url).</summary>
+    [Column("auth_key")] public string AuthKey { get; set; } = string.Empty;
+
+    [Column("created_at")] public DateTime CreatedAt { get; set; }
+}
diff --git a/src/Enmarcha.Backend/Data/Models/ServiceAlert.cs b/src/Enmarcha.Backend/Data/Models/ServiceAlert.cs
index 5f80e3c..39cf3fa 100644
--- a/src/Enmarcha.Backend/Data/Models/ServiceAlert.cs
+++ b/src/Enmarcha.Backend/Data/Models/ServiceAlert.cs
@@ -24,6 +24,15 @@ public class ServiceAlert
     [Column("event_end_date")] public DateTime EventEndDate { get; set; }
     [Column("hidden_date")] public DateTime HiddenDate { get; set; }
 
+    /// <summary>Incremented each time a push notification is sent for this alert.</summary>
+    public int Version { get; set; } = 1;
+
+    /// <summary>Set when a push notification was sent for the PreNotice phase.</summary>
+    [Column("pre_notice_notified_at")] public DateTime? PreNoticeNotifiedAt { get; set; }
+
+    /// <summary>Set when a push notification was sent for the Active phase.</summary>
+    [Column("active_notified_at")] public DateTime? ActiveNotifiedAt { get; set; }
+
     public AlertPhase GetPhase(DateTime? now = null)
     {
         now ??= DateTime.UtcNow;