feat(i18n): add localized navigation and news translations

apps/admin/src/app/navigation/page.tsx

@@ -5,6 +5,7 @@ import {
   listNavigationMenus,
   listPages,
   updateNavigationItem,
+  upsertNavigationItemTranslation,
 } from "@cms/db"
 import { Button } from "@cms/ui/button"
 import { revalidatePath } from "next/cache"
@@ -18,6 +19,9 @@ import { requirePermissionForRoute } from "@/lib/route-guards"
 export const dynamic = "force-dynamic"
 
 type SearchParamsInput = Record<string, string | string[] | undefined>
+const SUPPORTED_LOCALES = ["de", "en", "es", "fr"] as const
+
+type SupportedLocale = (typeof SUPPORTED_LOCALES)[number]
 
 function readFirstValue(value: string | string[] | undefined): string | null {
   if (Array.isArray(value)) {
@@ -53,6 +57,14 @@ function readInt(formData: FormData, field: string, fallback = 0): number {
   return parsed
 }
 
+function normalizeLocale(input: string | null): SupportedLocale {
+  if (input && SUPPORTED_LOCALES.includes(input as SupportedLocale)) {
+    return input as SupportedLocale
+  }
+
+  return "en"
+}
+
 function redirectWithState(params: { notice?: string; error?: string }) {
   const query = new URLSearchParams()
 
@@ -165,6 +177,31 @@ async function deleteItemAction(formData: FormData) {
   redirectWithState({ notice: "Navigation item deleted." })
 }
 
+async function upsertItemTranslationAction(formData: FormData) {
+  "use server"
+
+  await requirePermissionForRoute({
+    nextPath: "/navigation",
+    permission: "navigation:write",
+    scope: "team",
+  })
+
+  const locale = normalizeLocale(readInputString(formData, "locale"))
+
+  try {
+    await upsertNavigationItemTranslation({
+      navigationItemId: readInputString(formData, "navigationItemId"),
+      locale,
+      label: readInputString(formData, "label"),
+    })
+  } catch {
+    redirectWithState({ error: "Failed to save item translation." })
+  }
+
+  revalidatePath("/navigation")
+  redirectWithState({ notice: "Navigation item translation saved." })
+}
+
 export default async function NavigationManagementPage({
   searchParams,
 }: {
@@ -184,6 +221,7 @@ export default async function NavigationManagementPage({
 
   const notice = readFirstValue(resolvedSearchParams.notice)
   const error = readFirstValue(resolvedSearchParams.error)
+  const selectedLocale = normalizeLocale(readFirstValue(resolvedSearchParams.locale))
 
   return (
     <AdminShell
@@ -218,6 +256,22 @@ export default async function NavigationManagementPage({
       </section>
 
       <section className="space-y-4">
+        <div className="flex flex-wrap gap-2">
+          {SUPPORTED_LOCALES.map((locale) => (
+            <a
+              key={locale}
+              href={`/navigation?locale=${locale}`}
+              className={`inline-flex rounded border px-3 py-1.5 text-xs ${
+                selectedLocale === locale
+                  ? "border-neutral-800 bg-neutral-900 text-white"
+                  : "border-neutral-300 text-neutral-700"
+              }`}
+            >
+              {locale.toUpperCase()}
+            </a>
+          ))}
+        </div>
+
         {menus.length === 0 ? (
           <article className="rounded-xl border border-neutral-200 p-6 text-sm text-neutral-600">
             No navigation menus yet.
@@ -238,12 +292,14 @@ export default async function NavigationManagementPage({
                 {menu.items.length === 0 ? (
                   <p className="text-sm text-neutral-600">No items in this menu.</p>
                 ) : (
-                  menu.items.map((item) => (
+                  menu.items.map((item) => {
-                    <form
+                    const translation = item.translations.find(
-                      key={item.id}
+                      (entry) => entry.locale === selectedLocale,
-                      action={updateItemAction}
+                    )
-                      className="rounded-lg border border-neutral-200 p-3"
+
-                    >
+                    return (
+                      <div key={item.id} className="rounded-lg border border-neutral-200 p-3">
+                        <form action={updateItemAction}>
                           <input type="hidden" name="id" value={item.id} />
                           <div className="grid gap-3 md:grid-cols-5">
                             <label className="space-y-1 md:col-span-2">
@@ -325,7 +381,37 @@ export default async function NavigationManagementPage({
                             </div>
                           </div>
                         </form>
-                  ))
+
+                        <form
+                          action={upsertItemTranslationAction}
+                          className="mt-3 rounded border border-neutral-200 p-3"
+                        >
+                          <input type="hidden" name="navigationItemId" value={item.id} />
+                          <input type="hidden" name="locale" value={selectedLocale} />
+
+                          <p className="text-xs text-neutral-600">
+                            Translation ({selectedLocale.toUpperCase()}) - saved locales:{" "}
+                            {item.translations.length > 0
+                              ? item.translations
+                                  .map((entry) => entry.locale.toUpperCase())
+                                  .join(", ")
+                              : "none"}
+                          </p>
+
+                          <div className="mt-2 flex gap-2">
+                            <input
+                              name="label"
+                              defaultValue={translation?.label ?? item.label}
+                              className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
+                            />
+                            <Button type="submit" size="sm" variant="secondary">
+                              Save translation
+                            </Button>
+                          </div>
+                        </form>
+                      </div>
+                    )
+                  })
                 )}
               </div>
             </article>

apps/admin/src/app/news/page.tsx

@@ -1,4 +1,10 @@
-import { createPost, deletePost, listPosts, updatePost } from "@cms/db"
+import {
+  createPost,
+  deletePost,
+  listPostsWithTranslations,
+  updatePost,
+  upsertPostTranslation,
+} from "@cms/db"
 import { Button } from "@cms/ui/button"
 import { revalidatePath } from "next/cache"
 import { redirect } from "next/navigation"
@@ -9,6 +15,9 @@ import { requirePermissionForRoute } from "@/lib/route-guards"
 export const dynamic = "force-dynamic"
 
 type SearchParamsInput = Record<string, string | string[] | undefined>
+const SUPPORTED_LOCALES = ["de", "en", "es", "fr"] as const
+
+type SupportedLocale = (typeof SUPPORTED_LOCALES)[number]
 
 function readFirstValue(value: string | string[] | undefined): string | null {
   if (Array.isArray(value)) {
@@ -28,6 +37,14 @@ function readNullableString(formData: FormData, field: string): string | undefin
   return value.length > 0 ? value : undefined
 }
 
+function normalizeLocale(input: string | null): SupportedLocale {
+  if (input && SUPPORTED_LOCALES.includes(input as SupportedLocale)) {
+    return input as SupportedLocale
+  }
+
+  return "en"
+}
+
 function redirectWithState(params: { notice?: string; error?: string }) {
   const query = new URLSearchParams()
 
@@ -115,6 +132,34 @@ async function deleteNewsAction(formData: FormData) {
   redirectWithState({ notice: "Post deleted." })
 }
 
+async function upsertNewsTranslationAction(formData: FormData) {
+  "use server"
+
+  await requirePermissionForRoute({
+    nextPath: "/news",
+    permission: "news:write",
+    scope: "team",
+  })
+
+  const locale = normalizeLocale(readInputString(formData, "locale"))
+
+  try {
+    await upsertPostTranslation({
+      postId: readInputString(formData, "postId"),
+      locale,
+      title: readInputString(formData, "title"),
+      excerpt: readNullableString(formData, "excerpt") ?? null,
+      body: readInputString(formData, "body"),
+    })
+  } catch {
+    redirectWithState({ error: "Failed to save translation." })
+  }
+
+  revalidatePath("/news")
+  revalidatePath("/")
+  redirectWithState({ notice: "Post translation saved." })
+}
+
 export default async function NewsManagementPage({
   searchParams,
 }: {
@@ -126,10 +171,14 @@ export default async function NewsManagementPage({
     scope: "team",
   })
 
-  const [resolvedSearchParams, posts] = await Promise.all([searchParams, listPosts()])
+  const [resolvedSearchParams, posts] = await Promise.all([
+    searchParams,
+    listPostsWithTranslations(),
+  ])
 
   const notice = readFirstValue(resolvedSearchParams.notice)
   const error = readFirstValue(resolvedSearchParams.error)
+  const selectedLocale = normalizeLocale(readFirstValue(resolvedSearchParams.locale))
 
   return (
     <AdminShell
@@ -204,12 +253,28 @@ export default async function NewsManagementPage({
       </section>
 
       <section className="space-y-3">
-        {posts.map((post) => (
+        <div className="flex flex-wrap gap-2">
-          <form
+          {SUPPORTED_LOCALES.map((locale) => (
-            key={post.id}
+            <a
-            action={updateNewsAction}
+              key={locale}
-            className="rounded-xl border border-neutral-200 p-6"
+              href={`/news?locale=${locale}`}
+              className={`inline-flex rounded border px-3 py-1.5 text-xs ${
+                selectedLocale === locale
+                  ? "border-neutral-800 bg-neutral-900 text-white"
+                  : "border-neutral-300 text-neutral-700"
+              }`}
             >
+              {locale.toUpperCase()}
+            </a>
+          ))}
+        </div>
+
+        {posts.map((post) => {
+          const translation = post.translations.find((entry) => entry.locale === selectedLocale)
+
+          return (
+            <div key={post.id} className="rounded-xl border border-neutral-200 p-6">
+              <form action={updateNewsAction}>
                 <input type="hidden" name="id" value={post.id} />
                 <div className="grid gap-3 md:grid-cols-2">
                   <label className="space-y-1">
@@ -269,7 +334,65 @@ export default async function NewsManagementPage({
                   </div>
                 </div>
               </form>
-        ))}
+
+              <form
+                action={upsertNewsTranslationAction}
+                className="mt-4 rounded-lg border border-neutral-200 p-4"
+              >
+                <input type="hidden" name="postId" value={post.id} />
+                <input type="hidden" name="locale" value={selectedLocale} />
+
+                <h3 className="text-sm font-medium">
+                  Translation ({selectedLocale.toUpperCase()})
+                </h3>
+                <p className="mt-1 text-xs text-neutral-600">
+                  Missing fields fall back to base post content on public pages.
+                </p>
+                {post.translations.length > 0 ? (
+                  <p className="mt-2 text-xs text-neutral-600">
+                    Saved locales:{" "}
+                    {post.translations.map((entry) => entry.locale.toUpperCase()).join(", ")}
+                  </p>
+                ) : null}
+
+                <div className="mt-3 grid gap-3 md:grid-cols-2">
+                  <label className="space-y-1">
+                    <span className="text-xs text-neutral-600">Title</span>
+                    <input
+                      name="title"
+                      defaultValue={translation?.title ?? post.title}
+                      className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
+                    />
+                  </label>
+                  <label className="space-y-1">
+                    <span className="text-xs text-neutral-600">Excerpt</span>
+                    <input
+                      name="excerpt"
+                      defaultValue={translation?.excerpt ?? post.excerpt ?? ""}
+                      className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
+                    />
+                  </label>
+                </div>
+
+                <label className="mt-3 block space-y-1">
+                  <span className="text-xs text-neutral-600">Body</span>
+                  <textarea
+                    name="body"
+                    rows={4}
+                    defaultValue={translation?.body ?? post.body}
+                    className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
+                  />
+                </label>
+
+                <div className="mt-3">
+                  <Button type="submit" size="sm">
+                    Save translation
+                  </Button>
+                </div>
+              </form>
+            </div>
+          )
+        })}
       </section>
     </AdminShell>
   )

apps/web/src/app/[locale]/news/[slug]/page.tsx

@@ -1,15 +1,15 @@
-import { getPostBySlug } from "@cms/db"
+import { getPostBySlugForLocale } from "@cms/db"
 import { notFound } from "next/navigation"
 
 export const dynamic = "force-dynamic"
 
 type PageProps = {
-  params: Promise<{ slug: string }>
+  params: Promise<{ locale: string; slug: string }>
 }
 
 export default async function PublicNewsDetailPage({ params }: PageProps) {
-  const { slug } = await params
+  const { locale, slug } = await params
-  const post = await getPostBySlug(slug)
+  const post = await getPostBySlugForLocale(slug, locale)
 
   if (!post || post.status !== "published") {
     notFound()

apps/web/src/app/[locale]/news/page.tsx

@@ -1,10 +1,15 @@
-import { listPosts } from "@cms/db"
+import { listPostsForLocale } from "@cms/db"
 import Link from "next/link"
 
 export const dynamic = "force-dynamic"
 
-export default async function PublicNewsIndexPage() {
+type PublicNewsIndexPageProps = {
-  const posts = await listPosts()
+  params: Promise<{ locale: string }>
+}
+
+export default async function PublicNewsIndexPage({ params }: PublicNewsIndexPageProps) {
+  const { locale } = await params
+  const posts = await listPostsForLocale(locale)
 
   return (
     <section className="mx-auto w-full max-w-4xl space-y-4 px-6 py-16">

apps/web/src/components/public-site-header.tsx

@@ -1,11 +1,13 @@
 import { listPublicNavigation } from "@cms/db"
+import { getLocale } from "next-intl/server"
 
 import { Link } from "@/i18n/navigation"
 
 import { LanguageSwitcher } from "./language-switcher"
 
 export async function PublicSiteHeader() {
-  const navItems = await listPublicNavigation("header")
+  const locale = await getLocale()
+  const navItems = await listPublicNavigation("header", locale)
 
   return (
     <header className="border-b border-neutral-200 bg-white/80 backdrop-blur">

packages/db/prisma/migrations/20260212224500_navigation_post_translations/migration.sql

@@ -0,0 +1,43 @@
+-- CreateTable
+CREATE TABLE "PostTranslation" (
+    "id" TEXT NOT NULL,
+    "postId" TEXT NOT NULL,
+    "locale" TEXT NOT NULL,
+    "title" TEXT NOT NULL,
+    "excerpt" TEXT,
+    "body" TEXT NOT NULL,
+    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "updatedAt" TIMESTAMP(3) NOT NULL,
+
+    CONSTRAINT "PostTranslation_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateTable
+CREATE TABLE "NavigationItemTranslation" (
+    "id" TEXT NOT NULL,
+    "navigationItemId" TEXT NOT NULL,
+    "locale" TEXT NOT NULL,
+    "label" TEXT NOT NULL,
+    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    "updatedAt" TIMESTAMP(3) NOT NULL,
+
+    CONSTRAINT "NavigationItemTranslation_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateIndex
+CREATE INDEX "PostTranslation_locale_idx" ON "PostTranslation"("locale");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "PostTranslation_postId_locale_key" ON "PostTranslation"("postId", "locale");
+
+-- CreateIndex
+CREATE INDEX "NavigationItemTranslation_locale_idx" ON "NavigationItemTranslation"("locale");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "NavigationItemTranslation_navigationItemId_locale_key" ON "NavigationItemTranslation"("navigationItemId", "locale");
+
+-- AddForeignKey
+ALTER TABLE "PostTranslation" ADD CONSTRAINT "PostTranslation_postId_fkey" FOREIGN KEY ("postId") REFERENCES "Post"("id") ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- AddForeignKey
+ALTER TABLE "NavigationItemTranslation" ADD CONSTRAINT "NavigationItemTranslation_navigationItemId_fkey" FOREIGN KEY ("navigationItemId") REFERENCES "NavigationItem"("id") ON DELETE CASCADE ON UPDATE CASCADE;

packages/db/prisma/schema.prisma

@@ -16,6 +16,22 @@ model Post {
   status    String
   createdAt DateTime @default(now())
   updatedAt DateTime @updatedAt
+  translations PostTranslation[]
+}
+
+model PostTranslation {
+  id        String   @id @default(uuid())
+  postId    String
+  locale    String
+  title     String
+  excerpt   String?
+  body      String
+  createdAt DateTime @default(now())
+  updatedAt DateTime @updatedAt
+  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
+
+  @@unique([postId, locale])
+  @@index([locale])
 }
 
 model User {
@@ -315,6 +331,7 @@ model NavigationItem {
   page      Page?            @relation(fields: [pageId], references: [id], onDelete: SetNull)
   parent    NavigationItem?  @relation("NavigationItemParent", fields: [parentId], references: [id], onDelete: Cascade)
   children  NavigationItem[] @relation("NavigationItemParent")
+  translations NavigationItemTranslation[]
 
   @@index([menuId])
   @@index([pageId])
@@ -322,6 +339,19 @@ model NavigationItem {
   @@unique([menuId, parentId, sortOrder, label])
 }
 
+model NavigationItemTranslation {
+  id               String         @id @default(uuid())
+  navigationItemId String
+  locale           String
+  label            String
+  createdAt        DateTime       @default(now())
+  updatedAt        DateTime       @updatedAt
+  navigationItem   NavigationItem @relation(fields: [navigationItemId], references: [id], onDelete: Cascade)
+
+  @@unique([navigationItemId, locale])
+  @@index([locale])
+}
+
 model Customer {
   id          String       @id @default(uuid())
   name        String

packages/db/src/index.ts

@@ -49,6 +49,7 @@ export {
   listPublishedPageSlugs,
   updateNavigationItem,
   updatePage,
+  upsertNavigationItemTranslation,
   upsertPageTranslation,
 } from "./pages-navigation"
 export {
@@ -56,9 +57,13 @@ export {
   deletePost,
   getPostById,
   getPostBySlug,
+  getPostBySlugForLocale,
   listPosts,
+  listPostsForLocale,
+  listPostsWithTranslations,
   registerPostCrudAuditHook,
   updatePost,
+  upsertPostTranslation,
 } from "./posts"
 export type { PublicHeaderBanner, PublicHeaderBannerConfig } from "./settings"
 export {

packages/db/src/pages-navigation.test.ts

@@ -24,6 +24,9 @@ const { mockDb } = vi.hoisted(() => ({
       update: vi.fn(),
       delete: vi.fn(),
     },
+    navigationItemTranslation: {
+      upsert: vi.fn(),
+    },
   },
 }))
 
@@ -38,6 +41,7 @@ import {
   getPublishedPageBySlugForLocale,
   listPublicNavigation,
   updatePage,
+  upsertNavigationItemTranslation,
   upsertPageTranslation,
 } from "./pages-navigation"
 
@@ -112,22 +116,33 @@ describe("pages-navigation service", () => {
             slug: "home",
             status: "published",
           },
+          translations: [{ locale: "de", label: "Startseite" }],
         },
       ],
     })
 
-    const navigation = await listPublicNavigation("header")
+    const navigation = await listPublicNavigation("header", "de")
 
     expect(navigation).toEqual([
       {
         id: "item-1",
-        label: "Home",
+        label: "Startseite",
         href: "/",
         children: [],
       },
     ])
   })
 
+  it("validates locale when upserting navigation item translation", async () => {
+    await expect(() =>
+      upsertNavigationItemTranslation({
+        navigationItemId: "550e8400-e29b-41d4-a716-446655440001",
+        locale: "it",
+        label: "Home",
+      }),
+    ).rejects.toThrow()
+  })
+
   it("validates locale when upserting page translation", async () => {
     await expect(() =>
       upsertPageTranslation({

packages/db/src/pages-navigation.ts

@@ -6,6 +6,7 @@ import {
   updatePageInputSchema,
   upsertPageTranslationInputSchema,
 } from "@cms/content"
+import { z } from "zod"
 
 import { db } from "./client"
 
@@ -16,6 +17,13 @@ export type PublicNavigationItem = {
   children: PublicNavigationItem[]
 }
 
+const supportedLocaleSchema = z.enum(["de", "en", "es", "fr"])
+const upsertNavigationItemTranslationInputSchema = z.object({
+  navigationItemId: z.string().uuid(),
+  locale: supportedLocaleSchema,
+  label: z.string().min(1).max(180),
+})
+
 function resolvePublishedAt(status: string): Date | null {
   return status === "published" ? new Date() : null
 }
@@ -159,6 +167,9 @@ export async function listNavigationMenus() {
               slug: true,
             },
           },
+          translations: {
+            orderBy: [{ locale: "asc" }],
+          },
         },
       },
     },
@@ -183,7 +194,12 @@ function resolveNavigationHref(item: {
   return null
 }
 
-export async function listPublicNavigation(location = "header"): Promise<PublicNavigationItem[]> {
+export async function listPublicNavigation(
+  location = "header",
+  locale?: string,
+): Promise<PublicNavigationItem[]> {
+  const normalizedLocale = locale ? supportedLocaleSchema.safeParse(locale).data : undefined
+
   const menu = await db.navigationMenu.findFirst({
     where: {
       location,
@@ -203,6 +219,12 @@ export async function listPublicNavigation(location = "header"): Promise<PublicN
               status: true,
             },
           },
+          translations: normalizedLocale
+            ? {
+                where: { locale: normalizedLocale },
+                take: 1,
+              }
+            : false,
         },
       },
     },
@@ -232,7 +254,7 @@ export async function listPublicNavigation(location = "header"): Promise<PublicN
 
     itemMap.set(item.id, {
       id: item.id,
-      label: item.label,
+      label: item.translations?.[0]?.label ?? item.label,
       href,
       parentId: item.parentId,
       children: [],
@@ -298,3 +320,20 @@ export async function deleteNavigationItem(id: string) {
     where: { id },
   })
 }
+
+export async function upsertNavigationItemTranslation(input: unknown) {
+  const payload = upsertNavigationItemTranslationInputSchema.parse(input)
+
+  return db.navigationItemTranslation.upsert({
+    where: {
+      navigationItemId_locale: {
+        navigationItemId: payload.navigationItemId,
+        locale: payload.locale,
+      },
+    },
+    create: payload,
+    update: {
+      label: payload.label,
+    },
+  })
+}

packages/db/src/posts.test.ts

@@ -9,6 +9,9 @@ const { mockDb } = vi.hoisted(() => ({
       update: vi.fn(),
       delete: vi.fn(),
     },
+    postTranslation: {
+      upsert: vi.fn(),
+    },
   },
 }))
 
@@ -16,7 +19,15 @@ vi.mock("./client", () => ({
   db: mockDb,
 }))
 
-import { createPost, getPostBySlug, listPosts, updatePost } from "./posts"
+import {
+  createPost,
+  getPostBySlug,
+  getPostBySlugForLocale,
+  listPosts,
+  listPostsForLocale,
+  updatePost,
+  upsertPostTranslation,
+} from "./posts"
 
 describe("posts service", () => {
   beforeEach(() => {
@@ -25,6 +36,7 @@ describe("posts service", () => {
         fn.mockReset()
       }
     }
+    mockDb.postTranslation.upsert.mockReset()
   })
 
   it("lists posts ordered by update date desc", async () => {
@@ -72,4 +84,63 @@ describe("posts service", () => {
       },
     })
   })
+
+  it("upserts post translation and reads localized/fallback post views", async () => {
+    mockDb.postTranslation.upsert.mockResolvedValue({ id: "pt-1" })
+    mockDb.post.findUnique
+      .mockResolvedValueOnce({
+        id: "post-1",
+        slug: "hello",
+        title: "Base title",
+        excerpt: "Base excerpt",
+        body: "Base body",
+        translations: [{ locale: "de", title: "Titel", excerpt: "Auszug", body: "Text" }],
+      })
+      .mockResolvedValueOnce({
+        id: "post-1",
+        slug: "hello",
+        title: "Base title",
+        excerpt: "Base excerpt",
+        body: "Base body",
+        translations: [],
+      })
+    mockDb.post.findMany.mockResolvedValue([
+      {
+        id: "post-1",
+        slug: "hello",
+        title: "Base title",
+        excerpt: "Base excerpt",
+        body: "Base body",
+        status: "published",
+        translations: [{ locale: "de", title: "Titel", excerpt: "Auszug", body: "Text" }],
+      },
+    ])
+
+    await upsertPostTranslation({
+      postId: "550e8400-e29b-41d4-a716-446655440000",
+      locale: "de",
+      title: "Titel",
+      body: "Text",
+    })
+
+    const localized = await getPostBySlugForLocale("hello", "de")
+    const fallback = await getPostBySlugForLocale("hello", "fr")
+    const localizedList = await listPostsForLocale("de")
+
+    expect(mockDb.postTranslation.upsert).toHaveBeenCalledTimes(1)
+    expect(localized?.title).toBe("Titel")
+    expect(fallback?.title).toBe("Base title")
+    expect(localizedList[0]?.title).toBe("Titel")
+  })
+
+  it("validates locale for post translations", async () => {
+    await expect(() =>
+      upsertPostTranslation({
+        postId: "550e8400-e29b-41d4-a716-446655440000",
+        locale: "it",
+        title: "Titolo",
+        body: "Testo",
+      }),
+    ).rejects.toThrow()
+  })
 })

packages/db/src/posts.ts

@@ -5,6 +5,7 @@ import {
   updatePostInputSchema,
 } from "@cms/content"
 import { type CrudAuditHook, type CrudMutationContext, createCrudService } from "@cms/crud"
+import { z } from "zod"
 import type { Post } from "../prisma/generated/client/client"
 
 import { db } from "./client"
@@ -35,6 +36,15 @@ const postRepository = {
     }),
 }
 
+const supportedLocaleSchema = z.enum(["de", "en", "es", "fr"])
+const upsertPostTranslationInputSchema = z.object({
+  postId: z.string().uuid(),
+  locale: supportedLocaleSchema,
+  title: z.string().min(3).max(180),
+  excerpt: z.string().max(320).nullable().optional(),
+  body: z.string().min(1),
+})
+
 const postAuditHooks: Array<CrudAuditHook<Post>> = []
 
 const postCrudService = createCrudService({
@@ -73,6 +83,100 @@ export async function getPostBySlug(slug: string) {
   })
 }
 
+export async function getPostBySlugForLocale(slug: string, locale: string) {
+  const normalizedLocale = supportedLocaleSchema.safeParse(locale).data
+  const post = await db.post.findUnique({
+    where: { slug },
+    include: {
+      translations: normalizedLocale
+        ? {
+            where: {
+              locale: normalizedLocale,
+            },
+            take: 1,
+          }
+        : false,
+    },
+  })
+
+  if (!post) {
+    return null
+  }
+
+  const translation = post.translations?.[0]
+
+  return {
+    ...post,
+    title: translation?.title ?? post.title,
+    excerpt: translation?.excerpt ?? post.excerpt,
+    body: translation?.body ?? post.body,
+  }
+}
+
+export async function listPostsForLocale(locale: string) {
+  const normalizedLocale = supportedLocaleSchema.safeParse(locale).data
+  const posts = await db.post.findMany({
+    where: {
+      status: "published",
+    },
+    orderBy: {
+      updatedAt: "desc",
+    },
+    include: {
+      translations: normalizedLocale
+        ? {
+            where: { locale: normalizedLocale },
+            take: 1,
+          }
+        : false,
+    },
+  })
+
+  return posts.map((post) => {
+    const translation = post.translations?.[0]
+
+    return {
+      ...post,
+      title: translation?.title ?? post.title,
+      excerpt: translation?.excerpt ?? post.excerpt,
+      body: translation?.body ?? post.body,
+    }
+  })
+}
+
+export async function listPostsWithTranslations() {
+  return db.post.findMany({
+    orderBy: {
+      updatedAt: "desc",
+    },
+    include: {
+      translations: {
+        orderBy: [{ locale: "asc" }],
+      },
+    },
+  })
+}
+
+export async function upsertPostTranslation(input: unknown) {
+  const payload = upsertPostTranslationInputSchema.parse(input)
+  const { postId, locale, ...data } = payload
+
+  return db.postTranslation.upsert({
+    where: {
+      postId_locale: {
+        postId,
+        locale,
+      },
+    },
+    create: {
+      postId,
+      locale,
+      ...data,
+    },
+    update: data,
+  })
+}
+
 export async function createPost(input: unknown, context?: CrudMutationContext) {
   return postCrudService.create(input, context)
 }