fix(kubernetes): support localized template schema

Update migration, runtime grants, sandbox seed, and live upgrade guidance for the normalized email template schema.

Ref: IT-1601

HrynCo.NotificationService.Web.IntegrationTests/TemplateCatalogPresentationTests.cs

@@ -8,6 +8,37 @@ namespace HrynCo.NotificationService.Web.IntegrationTests;
 
 public sealed class TemplateCatalogPresentationTests(TemplateDatabaseFixture fixture) : IClassFixture<TemplateDatabaseFixture>
 {
+    [Fact]
+    public async Task EditorTabsShowSavedVersionsAfterInvalidUnsavedChanges()
+    {
+        var connection = await fixture.CreateDatabaseAsync();
+        await using var app = new TemplateWebApplicationFactory(connection);
+        using var scope = app.Services.CreateScope();
+        var service = (await scope.ServiceProvider.GetRequiredService<INotificationServiceRepository>().GetAllAsync()).Single();
+        var versions = scope.ServiceProvider.GetRequiredService<IEmailTemplateRepository>();
+        await versions.AddAsync(new EmailTemplate
+        {
+            ServiceName = service.Name, Key = "TabStatus", LanguageCode = "en",
+            Subject = "Saved", HtmlBody = "Saved", TextBody = "Saved"
+        });
+        var definition = (await scope.ServiceProvider.GetRequiredService<IEmailTemplateDefinitionRepository>()
+            .GetDefinitionAsync(service.Name, "TabStatus"))!;
+        using var client = app.CreateClient(new() { AllowAutoRedirect = false });
+        var baseUrl = $"/admin/services/{service.Id}/templates";
+        var html = await client.GetStringAsync($"{baseUrl}/{definition.Id}");
+        Assert.Matches("(?s)<span>en</span>\\s*<span class=\"template-language-status template-language-present\">Present</span>", html);
+        Assert.Matches("(?s)<span>uk</span>\\s*<span class=\"template-language-status template-language-missing\">Missing</span>", html);
+
+        var fields = TemplateEditorTestFields.Read(html);
+        TemplateEditorTestFields.SetLanguage(fields, "uk", "Unsaved", "", "");
+        fields["Languages[0].IsPresent"] = "false";
+        var response = await client.PostAsync(baseUrl + "/save", new FormUrlEncodedContent(fields));
+        Assert.Equal(HttpStatusCode.OK, response.StatusCode);
+        var invalidHtml = await response.Content.ReadAsStringAsync();
+        Assert.Matches("(?s)<span>en</span>\\s*<span class=\"template-language-status template-language-present\">Present</span>", invalidHtml);
+        Assert.Matches("(?s)<span>uk</span>\\s*<span class=\"template-language-status template-language-missing\">Missing</span>", invalidHtml);
+    }
+
     [Fact]
     public async Task CatalogStatusesUseExactCodes_AndEmptyDefinitionsShowEveryLanguageMissing()
     {

HrynCo.NotificationService.Web/Controllers/Admin/AdminTemplatesController.cs

@@ -69,8 +69,12 @@ public sealed class AdminTemplatesController(IMediator mediator, IEmailTemplateD
         var service = await services.GetAsync(serviceId, ct);
         if (service is null || model.ServiceId != serviceId || model.ServiceName != service.Name)
             return NotFound();
-        if (model.Id is Guid definitionId && await OwnedDefinitionAsync(service, definitionId, ct) is null)
+        var persisted = model.Id is Guid definitionId ? await OwnedDefinitionAsync(service, definitionId, ct) : null;
+        if (model.Id is not null && persisted is null)
             return NotFound();
+        var presentCodes = persisted?.Languages.Select(l => l.LanguageCode).ToHashSet(StringComparer.Ordinal);
+        foreach (var language in model.Languages)
+            language.IsPresent = presentCodes?.Contains(language.LanguageCode) == true;
         ValidateLanguageCatalog(model, service);
         if (!ModelState.IsValid)
             return EditorView(model, service);
@@ -226,7 +230,8 @@ public sealed class AdminTemplatesController(IMediator mediator, IEmailTemplateD
         VariablesJson = JsonSerializer.Serialize(definition.Variables, JsonOptions),
         Languages = definition.Languages.Select(l => new EmailTemplateLanguageEditViewModel
         {
-            LanguageCode = l.LanguageCode, Subject = l.Subject, HtmlBody = l.HtmlBody, TextBody = l.TextBody
+            LanguageCode = l.LanguageCode, Subject = l.Subject, HtmlBody = l.HtmlBody, TextBody = l.TextBody,
+            IsPresent = true
         }).ToList()
     };
 }

HrynCo.NotificationService.Web/Controllers/Admin/ViewModels/EmailTemplateLanguageEditViewModel.cs

@@ -1,4 +1,5 @@
 using System.ComponentModel.DataAnnotations;
+using Microsoft.AspNetCore.Mvc.ModelBinding;
 
 namespace HrynCo.NotificationService.Web.Controllers.Admin.ViewModels;
 
@@ -12,6 +13,8 @@ public sealed class EmailTemplateLanguageEditViewModel : IValidatableObject
     public string HtmlBody { get; set; } = "";
     [DisplayFormat(ConvertEmptyStringToNull = false)]
     public string TextBody { get; set; } = "";
+    [BindNever]
+    public bool IsPresent { get; set; }
     public bool HasContent => !string.IsNullOrWhiteSpace(Subject) || !string.IsNullOrWhiteSpace(HtmlBody) || !string.IsNullOrWhiteSpace(TextBody);
 
     public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)

HrynCo.NotificationService.Web/Views/AdminTemplates/Edit.cshtml

@@ -34,18 +34,22 @@
     </div>
     <div class="mb-3">
         <label asp-for="VariablesJson" class="form-label fw-semibold">Shared variables (JSON)</label>
-        <textarea asp-for="VariablesJson" class="form-control font-monospace" rows="4"></textarea>
+        <textarea asp-for="VariablesJson" class="form-control font-monospace" rows="14"></textarea>
         <div class="form-text">One contract for every language: <code>[{"name":"UserName","required":true}]</code></div>
     </div>
-    <p class="text-muted">Every configured service language has a tab. Leave all three content fields blank for an unused language; clear them to remove an existing version. Shared variables and the template remain.</p>
+    <p class="text-muted">Every configured service language has a tab. Present and Missing show saved versions. Leave all three content fields blank for an unused language; clear them to remove an existing version. Shared variables and the template remain.</p>
     @if (Model.Languages.Count == 0) { <p>No service languages configured. You can save the key and shared variables now, then add language codes in Edit Service.</p> }
     <div class="template-language-sheet mb-3" hidden="@(Model.Languages.Count == 0)">
         <div class="nav nav-tabs" role="tablist" aria-label="Template languages" id="languageTabs">
             @for (var i = 0; i < Model.Languages.Count; i++)
             {
+                var language = Model.Languages[i];
                 <button type="button" role="tab" id="language-tab-@i" aria-controls="language-pane-@i"
                         aria-selected="@(i == active ? "true" : "false")" tabindex="@(i == active ? 0 : -1)"
-                        class="nav-link @(i == active ? "active" : "")" data-pane="language-pane-@i">@Model.Languages[i].LanguageCode</button>
+                        class="nav-link @(i == active ? "active" : "")" data-pane="language-pane-@i">
+                    <span>@language.LanguageCode</span>
+                    <span class="template-language-status @(language.IsPresent ? "template-language-present" : "template-language-missing")">@(language.IsPresent ? "Present" : "Missing")</span>
+                </button>
             }
         </div>
         <div id="languagePanes">
@@ -59,11 +63,11 @@
                     </div>
                     <div class="mb-3">
                         <label asp-for="Languages[i].HtmlBody" class="form-label fw-semibold">HTML Body</label>
-                        <textarea asp-for="Languages[i].HtmlBody" class="form-control font-monospace" rows="10" data-content="html"></textarea>
+                        <textarea asp-for="Languages[i].HtmlBody" class="form-control font-monospace" rows="15" data-content="html"></textarea>
                     </div>
                     <div class="mb-3">
                         <label asp-for="Languages[i].TextBody" class="form-label fw-semibold">Text Body</label>
-                        <textarea asp-for="Languages[i].TextBody" class="form-control font-monospace" rows="5" data-content="text"></textarea>
+                        <textarea asp-for="Languages[i].TextBody" class="form-control font-monospace" rows="8" data-content="text"></textarea>
                     </div>
                 </section>
             }

HrynCo.NotificationService.Web/wwwroot/css/admin.css

@@ -160,7 +160,13 @@ body {
 .admin-data-table .channel-actions {
     width: 1%;
     white-space: nowrap;
-    text-align: right;
+}
+
+.admin-data-table .template-actions,
+.admin-data-table .service-actions,
+.admin-data-table .channel-actions,
+.admin-data-table .service-language-actions {
+    text-align: center;
 }
 
 .page-header .service-template-service-name {
@@ -243,12 +249,11 @@ body {
 /* ── Service language actions ─────────────────────────── */
 .service-language-table .service-language-actions {
     width: 1%;
-    text-align: right;
 }
 
 .service-language-table .service-language-action-buttons {
     display: flex;
-    justify-content: flex-end;
+    justify-content: center;
     gap: .5rem;
 }
 
@@ -263,7 +268,7 @@ body {
 @media (max-width: 575.98px) {
     .service-language-table .service-language-action-buttons {
         flex-direction: column;
-        align-items: flex-end;
+        align-items: center;
     }
 
     .service-language-table .service-language-action-buttons > .btn {
@@ -405,6 +410,9 @@ body {
 
 .template-language-sheet > .nav-tabs .nav-link {
     flex: 0 0 auto;
+    display: inline-flex;
+    align-items: center;
+    gap: .375rem;
     margin-bottom: 0;
     white-space: nowrap;
 }

docker/kubernetes/shared-notification/README.md

@@ -167,7 +167,8 @@ existing Dockerfile. Record the checked commit and archive SHA256, including any
 explicitly recorded uncommitted delta. Mirror PostgreSQL, RabbitMQ, Mailpit and a
 Python 3 Linux image to `registry.hrynco.com`. Copy `artifacts.example.json` to
 `artifacts.local.json` and record all seven immutable image digests, source hashes
-and the two reviewed migration IDs. The script rejects placeholder/tag references,
+and the three reviewed migration IDs, ending with `20261017072410_EmailTemplateLocalizations`.
+The script rejects placeholder/tag references,
 other registries, mismatched application/migrator archives and unsupported DB/broker
 major versions. The archive hash fields are an operator provenance record; independently
 verify the build inputs and registry digests before live application.
@@ -255,13 +256,15 @@ python3 notification.py render --config config.local.json --artifacts artifacts.
 python3 notification.py check --config config.local.json --artifacts artifacts.local.json
 ```
 
-5. First install applies storage/isolated foundation and runs migrations with Web and
-   Worker stopped. The migration target is exactly `InitialCreate` + `PendingChanges`.
+5. First install or a schema upgrade applies storage/isolated foundation and runs
+   migrations with Web and Worker stopped. The migration target is exactly
+   `InitialCreate` + `PendingChanges` + `EmailTemplateLocalizations`.
    After migration success, the script verifies history, grants a separate
-   `notification_runtime` role only DML on the three application tables, and idempotently
-   seeds the Mailpit channel and neutral English `TestEmail`. Existing matching-service
-   channels/templates are left unchanged. It resumes Web after these gates; Worker
-   remains stopped for inspection:
+   `notification_runtime` role only DML on the application tables, including the
+   service/language/definition catalog, and idempotently seeds the Mailpit channel,
+   sandbox service/catalog and neutral English `TestEmail`. Existing matching-service
+   channels/templates and shared variable contracts are left unchanged. It resumes
+   Web after these gates; Worker remains stopped for inspection:
 
 ```sh
 python3 notification.py deploy --config config.local.json --artifacts artifacts.local.json --migrate
@@ -320,6 +323,18 @@ a snapshot of RabbitMQ's queue files. Migration does not upgrade/recreate the br
 volume; durable queues remain on that retained volume. Broker disk backup/recovery
 must be verified before a broker upgrade or storage migration.
 
+Before upgrading a live flat database to `EmailTemplateLocalizations`, inspect the
+exact source columns/indexes/history and audit every template's identities and JSON
+variable shape. Versions of one service/key must have the same variable contract
+after sorting by variable name. Rehearse the current Migrator and sandbox/grants SQL
+on a disposable restored PostgreSQL 17 copy, then verify preserved content and a
+repeat no-op migration. The live `deploy --migrate` creates its own final backup after
+stopping Web and Worker. Do not use the old Web/Worker images with the new schema.
+Rollback requires restoring a verified pre-upgrade database backup with the matching
+old application images; migration `Down` is not a general rollback path. The
+production network add-on and production broker access remain separate, preserved
+resources; verify them and the existing production queues before re-enabling Worker.
+
 ## Access and networking
 
 Use SSH to the deployment host, then forward one local port when needed:

docker/kubernetes/shared-notification/artifacts.example.json

@@ -1,7 +1,7 @@
 {
   "sourceCommit": "REPLACE_WITH_CHECKED_SOURCE_COMMIT",
   "sourceArchiveSha256": "REPLACE_WITH_VERIFIED_SOURCE_ARCHIVE_SHA256",
-  "migrations": ["20260501214629_InitialCreate", "20260502154249_PendingChanges"],
+  "migrations": ["20260501214629_InitialCreate", "20260502154249_PendingChanges", "20261017072410_EmailTemplateLocalizations"],
   "images": {
     "web": {"image": "registry.hrynco.com/hrynco.notificationservice.web@sha256:REPLACE", "sourceArchiveSha256": "REPLACE_WITH_VERIFIED_SOURCE_ARCHIVE_SHA256"},
     "worker": {"image": "registry.hrynco.com/hrynco.notificationservice.worker@sha256:REPLACE", "sourceArchiveSha256": "REPLACE_WITH_VERIFIED_SOURCE_ARCHIVE_SHA256"},

docker/kubernetes/shared-notification/notification.py

@@ -18,7 +18,8 @@ import time
 
 NAMESPACE = "shared-notification"
 MANAGED = "hrynco-shared-notification"
-MIGRATIONS = ["20260501214629_InitialCreate", "20260502154249_PendingChanges"]
+MIGRATIONS = ["20260501214629_InitialCreate", "20260502154249_PendingChanges",
+              "20261017072410_EmailTemplateLocalizations"]
 IMAGE = re.compile(r"registry\.hrynco\.com/[a-z0-9._/-]+@sha256:[a-f0-9]{64}$")
 SHA = re.compile(r"[a-f0-9]{64}$")
 PRODUCTION_RESULT_QUEUE = "invemory.production.notification-results"
@@ -53,7 +54,8 @@ REVOKE ALL ON DATABASE notification_service FROM PUBLIC;
 GRANT CONNECT ON DATABASE notification_service TO notification_runtime;
 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
 GRANT USAGE ON SCHEMA public TO notification_runtime;
-GRANT SELECT, INSERT, UPDATE, DELETE ON email_channels, email_templates, email_channel_usage TO notification_runtime;
+GRANT SELECT, INSERT, UPDATE, DELETE ON email_channels, email_templates, email_channel_usage,
+    services, service_languages, email_template_definitions TO notification_runtime;
 '''
 
 SANDBOX = r'''
@@ -62,11 +64,24 @@ INSERT INTO email_channels (id, service_name, priority, provider_type, settings,
 SELECT gen_random_uuid(), 'Invemory.Staging.Sandbox', 1, 1,
  '{"EmailChannelType":1,"Host":"mailpit","Port":1025,"Username":"","Password":"","UseSsl":false,"FromEmail":"no-reply@local.hrynco.test","FromName":"Invemory Sandbox"}'::jsonb, 90, true, now()
 WHERE NOT EXISTS (SELECT 1 FROM email_channels WHERE service_name = 'Invemory.Staging.Sandbox');
-INSERT INTO email_templates (id, service_name, key, language_code, subject, html_body, text_body, variables, created)
-VALUES (gen_random_uuid(), 'Invemory.Staging.Sandbox', 'TestEmail', 'en', 'Test notification',
- '<p>Hello {{RecipientName}},</p><p>{{Message}}</p>', 'Hello {{RecipientName}}, {{Message}}',
- '[{"name":"RecipientName","required":true},{"name":"Message","required":true}]'::jsonb, now())
-ON CONFLICT (service_name, key, language_code) DO NOTHING;
+INSERT INTO services (id, name, revision, created)
+VALUES (gen_random_uuid(), 'Invemory.Staging.Sandbox', gen_random_uuid(), now())
+ON CONFLICT (name) DO NOTHING;
+INSERT INTO service_languages (id, service_id, code, created)
+SELECT gen_random_uuid(), s.id, 'en', now() FROM services s WHERE s.name = 'Invemory.Staging.Sandbox'
+ON CONFLICT (service_id, code) DO NOTHING;
+INSERT INTO email_template_definitions (id, service_id, key, revision, variables, created)
+SELECT gen_random_uuid(), s.id, 'TestEmail', gen_random_uuid(),
+ '[{"name":"RecipientName","required":true},{"name":"Message","required":true}]'::jsonb, now()
+FROM services s WHERE s.name = 'Invemory.Staging.Sandbox'
+ON CONFLICT (service_id, key) DO NOTHING;
+INSERT INTO email_templates (id, definition_id, service_id, service_language_id, subject, html_body, text_body, created)
+SELECT gen_random_uuid(), d.id, s.id, l.id, 'Test notification',
+ '<p>Hello {{RecipientName}},</p><p>{{Message}}</p>', 'Hello {{RecipientName}}, {{Message}}', now()
+FROM services s JOIN service_languages l ON l.service_id = s.id AND l.code = 'en'
+    JOIN email_template_definitions d ON d.service_id = s.id AND d.key = 'TestEmail'
+WHERE s.name = 'Invemory.Staging.Sandbox'
+ON CONFLICT (definition_id, service_language_id) DO NOTHING;
 COMMIT;
 '''
 
@@ -75,9 +90,12 @@ SANDBOX_CHECK = """SELECT
  settings->>'Host' = 'mailpit' AND settings->>'Port' = '1025' AND
  settings->>'UseSsl' = 'false' AND settings->>'Username' = '' AND settings->>'Password' = '')
  FROM email_channels WHERE service_name = 'Invemory.Staging.Sandbox') AND
- EXISTS (SELECT 1 FROM email_templates WHERE service_name = 'Invemory.Staging.Sandbox'
- AND key = 'TestEmail' AND language_code = 'en'
- AND variables @> '[{"name":"RecipientName","required":true},{"name":"Message","required":true}]'::jsonb)"""
+ EXISTS (SELECT 1 FROM email_templates t
+ JOIN email_template_definitions d ON d.id = t.definition_id AND d.service_id = t.service_id
+ JOIN service_languages l ON l.id = t.service_language_id AND l.service_id = t.service_id
+ JOIN services s ON s.id = t.service_id
+ WHERE s.name = 'Invemory.Staging.Sandbox' AND d.key = 'TestEmail' AND l.code = 'en'
+ AND d.variables @> '[{"name":"RecipientName","required":true},{"name":"Message","required":true}]'::jsonb)"""
 
 
 def read_json(path):
@@ -640,7 +658,7 @@ def deploy(cluster, config, artifacts, args):
             except Exception:
                 cluster.scale("worker", 0)
                 raise
-        print("deployment complete; SMTP is sandbox-only, Invemory publisher cutover is separate")
+        print("deployment complete; verify configured SMTP channels and publisher routes separately")
 
 
 def main(argv=None):

docs/email-template-localizations.md

@@ -27,7 +27,7 @@ The separate **Delete language and versions…** confirmation shows the current
 
 The administration starts at `/admin/services` (also the root redirect). A service name opens `/admin/services/{serviceId}`, which lists only that service's Email Templates, including definitions without translations. **Edit Service** is a separate action at `/admin/services/{id}/edit` for Name and the inline language table. There is no independent global Email Templates menu or extra page for a key: Template Key identifies the template edited directly from this service list. Create/edit/save/delete routes live under `/admin/services/{serviceId}/templates`; breadcrumbs, cancel and successful saves/deletes return to the selected service and retain its exact Key filter. Route IDs are bound explicitly from the route and cannot be replaced by form/query values. GET/POST validate definition ownership, and the mutation transaction also checks the selected service ID. Legacy global GET links redirect to the canonical service/template when identifiable, otherwise to Services; old global template POST routes are not retained. The language-specific API is unchanged.
 
-The service's exact configured language codes appear near its heading. Each template row lists every catalog language with a text-and-color **Present** or **Missing** status, comparing exact ordinal codes (`en` and `EN`, or `ru` and `ru-RU`, remain distinct). A definition with no versions shows all configured languages as Missing; a service with no catalog has an explicit no-languages state. Status display never creates translations. The GET Key filter, Filter and Clear controls live in the Template Key column header, which stays visible for an empty service or filter miss; the scoped header strip has a contrasting blue background. Clear returns to the same service without a key filter, while create/edit/save/cancel retain the selected key filter.
+The service's exact configured language codes appear near its heading. Each template row lists every catalog language with a text-and-color **Present** or **Missing** status, comparing exact ordinal codes (`en` and `EN`, or `ru` and `ru-RU`, remain distinct). The editor repeats these statuses on its language tabs. They show saved versions, including after a failed form submission; unsaved field changes do not alter the status. A definition with no versions shows all configured languages as Missing; a service with no catalog has an explicit no-languages state. Status display never creates translations. The GET Key filter, Filter and Clear controls live in the Template Key column header, which stays visible for an empty service or filter miss; the scoped header strip has a contrasting blue background. Clear returns to the same service without a key filter, while create/edit/save/cancel retain the selected key filter. The Action header and controls are centered in all four administration grids; their column widths are unchanged. The editor initially shows fourteen rows for Shared variables, fifteen for HTML Body and eight for Text Body, without changing their content or contracts.
 
 A template belongs to the selected service; its create/edit form shows Template Key and a tab for **every** language configured in that service. Languages are added, renamed and removed only through Edit Service. A tab with Subject, HTML Body and Text Body all blank does not persist a version; clearing all three removes an existing version while retaining the definition. A partially populated tab requires all three fields and prevents the entire save if incomplete. All tabs may be blank, and a service with no configured languages may still have a key/shared-variables definition. Submitted tabs must match the current service catalog exactly, including uniqueness. Tabs retain unsaved content and support Left/Right/Home/End keyboard navigation. Shared variables appear once as a camelCase JSON array: `[{"name":"RecipientName","required":true}]`. Each variable has a unique nonblank name and a boolean `required`; unknown JSON properties and malformed shapes are rejected. Variable contracts compare Name and Required semantically, independent of variable order.