chore(todo): finalize mvp1 commissions and portfolio status

feat(media): add in-place media file replacement flow
feat(pages): add scheduled publish workflow for page management
2026-02-12 23:13:41 +01:00 · 2026-02-12 23:12:29 +01:00 · 2026-02-12 23:10:47 +01:00 · 2026-02-12 23:08:25 +01:00 · 2026-02-12 23:05:39 +01:00 · 2026-02-12 22:59:53 +01:00
29 changed files with 701 additions and 57 deletions
--- a/TODO.md
+++ b/TODO.md
@@ -112,15 +112,15 @@ This file is the single source of truth for roadmap and delivery progress.

 - [x] [P1] `todo/mvp1-media-foundation`:
  media model, artwork entity, grouping primitives (gallery/album/category/tag), rendition slots
- [~] [P1] `todo/mvp1-media-upload-pipeline`:
+- [x] [P1] `todo/mvp1-media-upload-pipeline`:
  S3/local upload adapter, media processing presets, metadata input flows, admin media CRUD UI
- [~] [P1] `todo/mvp1-pages-navigation-builder`:
+- [x] [P1] `todo/mvp1-pages-navigation-builder`:
  page CRUD, navigation tree, reusable page blocks (forms/price cards/gallery embeds)
- [~] [P1] `todo/mvp1-commissions-customers`:
+- [x] [P1] `todo/mvp1-commissions-customers`:
  commission request intake + admin CRUD + kanban + customer entity/linking
- [~] [P1] `todo/mvp1-announcements-news`:
+- [x] [P1] `todo/mvp1-announcements-news`:
  announcement management/rendering + news/blog CRUD and public rendering
- [~] [P1] `todo/mvp1-public-rendering-integration`:
+- [x] [P1] `todo/mvp1-public-rendering-integration`:
  public rendering for pages/navigation/media/portfolio/announcements and commissioning entrypoints
 - [~] [P1] `todo/mvp1-e2e-happy-paths`:
  end-to-end scenarios for page publish, media flow, announcement display, commission flow
@@ -136,10 +136,10 @@ This file is the single source of truth for roadmap and delivery progress.

 ### Admin App (Primary Focus)

- [~] [P1] Page management (create/edit/publish/unpublish/schedule)
+- [x] [P1] Page management (create/edit/publish/unpublish/schedule)
 - [x] [P1] Page builder with reusable content blocks (hero, rich text, gallery, CTA, forms, price cards)
 - [x] [P1] Navigation management (menus, nested items, order, visibility)
- [~] [P1] Media library (upload, browse, replace, delete) with media-type classification (artwork, banner, promo, generic, video/gif)
+- [x] [P1] Media library (upload, browse, replace, delete) with media-type classification (artwork, banner, promo, generic, video/gif)
 - [x] [P1] Media enrichment metadata (alt text, copyright, author, source, tags, licensing, usage context)
 - [x] [P1] Portfolio grouping primitives (galleries, albums, categories, tags) with ordering/visibility controls
 - [x] [P1] Artwork refinement fields (medium, dimensions, year, framing, availability, price visibility)
@@ -148,32 +148,32 @@ This file is the single source of truth for roadmap and delivery progress.
 - [x] [P1] Users management (invite, roles, status)
 - [x] [P1] Disable/ban user function and enforcement in auth/session checks
 - [x] [P1] Owner/support protection rules in user management actions (cannot delete/demote)
- [~] [P1] Commissions management (request intake, owner, due date, notes, linked customer, linked artworks)
- [~] [P1] Customer records (contact profile, notes, consent flags, recurrence marker)
- [~] [P1] Customer-to-commission linkage and reuse workflow (no re-entry for recurring customers)
- [~] [P1] Kanban workflow for commissions (new, scoped, in-progress, review, done)
+- [x] [P1] Commissions management (request intake, owner, due date, notes, linked customer, linked artworks)
+- [x] [P1] Customer records (contact profile, notes, consent flags, recurrence marker)
+- [x] [P1] Customer-to-commission linkage and reuse workflow (no re-entry for recurring customers)
+- [x] [P1] Kanban workflow for commissions (new, scoped, in-progress, review, done)
 - [x] [P1] Header banner management (message, CTA, active window)
- [~] [P1] Announcements management (prominent site notices with schedule, priority, and audience targeting)
+- [x] [P1] Announcements management (prominent site notices with schedule, priority, and audience targeting)
 - [~] [P2] News/blog editorial workflow (draft/review/publish, authoring metadata)

 ### Public App

- [~] [P1] Dynamic page rendering from CMS page entities
- [~] [P1] Navigation rendering from managed menu structure
- [~] [P1] Media entity rendering with enrichment data
- [~] [P1] Portfolio views (gallery/album/category/tag) for artworks with filter and sort controls
- [~] [P1] Rendition-aware media delivery (thumbnail/card/full) per template slot
- [~] [P1] Translation-ready content model for public entities (pages/news/navigation labels)
- [ ] [P2] Artwork views and listing filters
- [~] [P1] Commission request submission flow
+- [x] [P1] Dynamic page rendering from CMS page entities
+- [x] [P1] Navigation rendering from managed menu structure
+- [x] [P1] Media entity rendering with enrichment data
+- [x] [P1] Portfolio views (gallery/album/category/tag) for artworks with filter and sort controls
+- [x] [P1] Rendition-aware media delivery (thumbnail/card/full) per template slot
+- [x] [P1] Translation-ready content model for public entities (pages/news/navigation labels)
+- [x] [P2] Artwork views and listing filters
+- [x] [P1] Commission request submission flow
 - [x] [P1] Header banner render logic and fallbacks
 - [x] [P1] Announcement render slots (homepage + optional global/top banner position)

 ### News / Blog (Secondary Track)

- [~] [P1] News/blog content type (editorial content for artist updates and process posts)
- [~] [P1] Admin list/editor for news posts
- [~] [P1] Public news index + detail pages
+- [x] [P1] News/blog content type (editorial content for artist updates and process posts)
+- [x] [P1] Admin list/editor for news posts
+- [x] [P1] Public news index + detail pages
 - [ ] [P2] Tag/category and basic archive support

 ### Testing
@@ -368,6 +368,11 @@ This file is the single source of truth for roadmap and delivery progress.
 - [2026-02-12] Page builder reusable blocks completed: admin block editor now supports full field editing + ordering controls for hero/rich-text/gallery/cta/form/price-cards; public renderer includes form-link behavior for `contact`/`commission` keys.
 - [2026-02-12] Navigation management completed: admin `/navigation` now supports menu update/delete controls, nested item parent selection via menu-local dropdown, and full order/visibility updates across menus and items.
 - [2026-02-12] Users management baseline completed: admin `/users` now supports managed user creation, role changes (`admin/editor/manager`), status changes (ban/unban), and protected/system guardrails for role-change/delete/ban actions.
+- [2026-02-12] Commissions management completed: admin kanban cards now include inline detail editing (assignee/customer/budget/due date/notes), linked-artwork references via `linkedArtworkIds`, and creation/edit flows use assignable users instead of raw ID entry.
+- [2026-02-12] Announcements/news completed: announcements now support locale audience targeting (`targetLocales`) with public locale-aware rendering, and homepage news list now uses locale-aware published posts only.
+- [2026-02-12] Public rendering integration completed: portfolio now supports locale-aware tag filters and explicit sort controls, while db/service sorting and rendition selection align public listing/detail media delivery.
+- [2026-02-12] Page scheduling completed: `Page.scheduledPublishAt` added with admin create/edit support and public page resolution now treating due scheduled pages as published.
+- [2026-02-12] Media library replace flow completed: admin `/media/:id` can now replace an asset’s source file in-place, update `storageKey`/MIME/size metadata, and clean up old storage objects with fallback notices.
 - [2026-02-12] Public UX pass: commission request flow now reports explicit invalid budget range errors, and header navigation now falls back to localized defaults (`home`, `portfolio`, `news`, `commissions`) when no CMS menu exists; seed data now creates those default menu entries.
 - [2026-02-12] Added `e2e/public-rendering.pw.ts` web coverage for fallback navigation visibility, portfolio routes, and commission submission validation (invalid budget range + successful submission path).
 - [2026-02-12] Testing execution is temporarily paused for delivery velocity: root test scripts are stubbed and CI test steps are disabled; all testing backlog is consolidated under `MVP 3: Testing and Quality`.
--- a/apps/admin/src/app/announcements/page.tsx
+++ b/apps/admin/src/app/announcements/page.tsx
@@ -14,6 +14,7 @@ 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

 function readFirstValue(value: string | string[] | undefined): string | null {
  if (Array.isArray(value)) {
@@ -49,6 +50,22 @@ function readNullableDate(formData: FormData, field: string): Date | null {
  return parsed
 }

+function readLocaleSelections(formData: FormData, field: string): string[] {
+  const values = formData.getAll(field)
+  const locales = new Set<string>()
+
+  for (const value of values) {
+    if (
+      typeof value === "string" &&
+      SUPPORTED_LOCALES.includes(value as (typeof SUPPORTED_LOCALES)[number])
+    ) {
+      locales.add(value)
+    }
+  }
+
+  return Array.from(locales)
+}
+
 function readInt(formData: FormData, field: string, fallback = 100): number {
  const value = readInputString(formData, field)

@@ -94,6 +111,7 @@ async function createAnnouncementAction(formData: FormData) {
      title: readInputString(formData, "title"),
      message: readInputString(formData, "message"),
      placement: readInputString(formData, "placement"),
+      targetLocales: readLocaleSelections(formData, "targetLocales"),
      priority: readInt(formData, "priority", 100),
      ctaLabel: readNullableString(formData, "ctaLabel"),
      ctaHref: readNullableString(formData, "ctaHref"),
@@ -125,6 +143,7 @@ async function updateAnnouncementAction(formData: FormData) {
      title: readInputString(formData, "title"),
      message: readInputString(formData, "message"),
      placement: readInputString(formData, "placement"),
+      targetLocales: readLocaleSelections(formData, "targetLocales"),
      priority: readInt(formData, "priority", 100),
      ctaLabel: readNullableString(formData, "ctaLabel"),
      ctaHref: readNullableString(formData, "ctaHref"),
@@ -277,6 +296,20 @@ export default async function AnnouncementsPage({
              />
            </label>
          </div>
+          <div className="space-y-1">
+            <p className="text-xs text-neutral-600">Target locales (empty = all locales)</p>
+            <div className="flex flex-wrap gap-3">
+              {SUPPORTED_LOCALES.map((locale) => (
+                <label
+                  key={`create-locale-${locale}`}
+                  className="inline-flex items-center gap-2 text-sm"
+                >
+                  <input name="targetLocales" type="checkbox" value={locale} className="size-4" />
+                  {locale.toUpperCase()}
+                </label>
+              ))}
+            </div>
+          </div>
          <div className="grid gap-3 md:grid-cols-2">
            <label className="space-y-1">
              <span className="text-xs text-neutral-600">Starts at</span>
@@ -390,6 +423,26 @@ export default async function AnnouncementsPage({
                  />
                </label>
              </div>
+              <div className="mt-3 space-y-1">
+                <p className="text-xs text-neutral-600">Target locales (empty = all locales)</p>
+                <div className="flex flex-wrap gap-3">
+                  {SUPPORTED_LOCALES.map((locale) => (
+                    <label
+                      key={`${announcement.id}-locale-${locale}`}
+                      className="inline-flex items-center gap-2 text-sm"
+                    >
+                      <input
+                        name="targetLocales"
+                        type="checkbox"
+                        value={locale}
+                        defaultChecked={announcement.targetLocales.includes(locale)}
+                        className="size-4"
+                      />
+                      {locale.toUpperCase()}
+                    </label>
+                  ))}
+                </div>
+              </div>
              <div className="mt-3 flex flex-wrap items-center justify-between gap-3">
                <label className="inline-flex items-center gap-2 text-sm text-neutral-700">
                  <input
--- a/apps/admin/src/app/commissions/page.tsx
+++ b/apps/admin/src/app/commissions/page.tsx
@@ -2,8 +2,11 @@ import {
  commissionKanbanOrder,
  createCommission,
  createCustomer,
+  db,
+  listArtworks,
  listCommissions,
  listCustomers,
+  updateCommission,
  updateCommissionStatus,
 } from "@cms/db"
 import { Button } from "@cms/ui/button"
@@ -67,6 +70,19 @@ function readNullableDate(formData: FormData, field: string): Date | null {
  return parsed
 }

+function readUuidList(formData: FormData, field: string): string[] {
+  const raw = readInputString(formData, field)
+
+  if (!raw) {
+    return []
+  }
+
+  return raw
+    .split(",")
+    .map((entry) => entry.trim())
+    .filter((entry) => entry.length > 0)
+}
+
 function redirectWithState(params: { notice?: string; error?: string }) {
  const query = new URLSearchParams()

@@ -124,6 +140,7 @@ async function createCommissionAction(formData: FormData) {
      status: readInputString(formData, "status"),
      customerId: readNullableString(formData, "customerId"),
      assignedUserId: readNullableString(formData, "assignedUserId"),
+      linkedArtworkIds: readUuidList(formData, "linkedArtworkIds"),
      budgetMin: readNullableNumber(formData, "budgetMin"),
      budgetMax: readNullableNumber(formData, "budgetMax"),
      dueAt: readNullableDate(formData, "dueAt"),
@@ -136,6 +153,35 @@ async function createCommissionAction(formData: FormData) {
  redirectWithState({ notice: "Commission created." })
 }

+async function updateCommissionAction(formData: FormData) {
+  "use server"
+
+  await requirePermissionForRoute({
+    nextPath: "/commissions",
+    permission: "commissions:write",
+    scope: "own",
+  })
+
+  try {
+    await updateCommission({
+      id: readInputString(formData, "id"),
+      title: readInputString(formData, "title"),
+      description: readNullableString(formData, "description"),
+      customerId: readNullableString(formData, "customerId"),
+      assignedUserId: readNullableString(formData, "assignedUserId"),
+      linkedArtworkIds: readUuidList(formData, "linkedArtworkIds"),
+      budgetMin: readNullableNumber(formData, "budgetMin"),
+      budgetMax: readNullableNumber(formData, "budgetMax"),
+      dueAt: readNullableDate(formData, "dueAt"),
+    })
+  } catch {
+    redirectWithState({ error: "Failed to update commission details." })
+  }
+
+  revalidatePath("/commissions")
+  redirectWithState({ notice: "Commission updated." })
+}
+
 async function updateCommissionStatusAction(formData: FormData) {
  "use server"

@@ -166,6 +212,14 @@ function formatDate(value: Date | null) {
  return value.toLocaleDateString("en-US")
 }

+function formatDateInput(value: Date | null) {
+  if (!value) {
+    return ""
+  }
+
+  return value.toISOString().slice(0, 10)
+}
+
 export default async function CommissionsManagementPage({
  searchParams,
 }: {
@@ -177,10 +231,22 @@ export default async function CommissionsManagementPage({
    scope: "own",
  })

-  const [resolvedSearchParams, customers, commissions] = await Promise.all([
+  const [resolvedSearchParams, customers, commissions, assignees, artworks] = await Promise.all([
    searchParams,
    listCustomers(200),
    listCommissions(300),
+    db.user.findMany({
+      where: {
+        isBanned: false,
+      },
+      orderBy: [{ createdAt: "asc" }],
+      select: {
+        id: true,
+        name: true,
+        username: true,
+      },
+    }),
+    listArtworks(300),
  ])

  const notice = readFirstValue(resolvedSearchParams.notice)
@@ -309,11 +375,18 @@ export default async function CommissionsManagementPage({
            </div>
            <div className="grid gap-3 md:grid-cols-3">
              <label className="space-y-1">
-                <span className="text-xs text-neutral-600">Assigned user id</span>
-                <input
+                <span className="text-xs text-neutral-600">Assigned user</span>
+                <select
                  name="assignedUserId"
                  className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
-                />
+                >
+                  <option value="">(none)</option>
+                  {assignees.map((assignee) => (
+                    <option key={assignee.id} value={assignee.id}>
+                      {assignee.name} @{assignee.username ?? "no-user"}
+                    </option>
+                  ))}
+                </select>
              </label>
              <label className="space-y-1">
                <span className="text-xs text-neutral-600">Budget min</span>
@@ -344,6 +417,14 @@ export default async function CommissionsManagementPage({
                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">Linked artwork IDs (comma separated)</span>
+              <textarea
+                name="linkedArtworkIds"
+                rows={2}
+                className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
+              />
+            </label>
            <Button type="submit">Create commission</Button>
          </form>
        </article>
@@ -383,6 +464,9 @@ export default async function CommissionsManagementPage({
                          <p className="text-xs text-neutral-600">
                            {commission.customer?.name ?? "No customer"}
                          </p>
+                          <p className="text-xs text-neutral-500">
+                            Assignee: {commission.assignedUser?.name ?? "none"}
+                          </p>
                          <p className="text-xs text-neutral-500">
                            Due: {formatDate(commission.dueAt)}
                          </p>
@@ -406,6 +490,99 @@ export default async function CommissionsManagementPage({
                            Move
                          </button>
                        </div>
+                        <details className="mt-2 rounded border border-neutral-200 p-2 text-xs">
+                          <summary className="cursor-pointer text-neutral-700">
+                            Edit details
+                          </summary>
+                          <form action={updateCommissionAction} className="mt-2 space-y-2">
+                            <input type="hidden" name="id" value={commission.id} />
+                            <input
+                              name="title"
+                              defaultValue={commission.title}
+                              className="w-full rounded border border-neutral-300 px-2 py-1"
+                            />
+                            <textarea
+                              name="description"
+                              rows={2}
+                              defaultValue={commission.description ?? ""}
+                              className="w-full rounded border border-neutral-300 px-2 py-1"
+                            />
+                            <select
+                              name="customerId"
+                              defaultValue={commission.customerId ?? ""}
+                              className="w-full rounded border border-neutral-300 px-2 py-1"
+                            >
+                              <option value="">(no customer)</option>
+                              {customers.map((customer) => (
+                                <option
+                                  key={`${commission.id}-customer-${customer.id}`}
+                                  value={customer.id}
+                                >
+                                  {customer.name}
+                                </option>
+                              ))}
+                            </select>
+                            <select
+                              name="assignedUserId"
+                              defaultValue={commission.assignedUserId ?? ""}
+                              className="w-full rounded border border-neutral-300 px-2 py-1"
+                            >
+                              <option value="">(no assignee)</option>
+                              {assignees.map((assignee) => (
+                                <option
+                                  key={`${commission.id}-assignee-${assignee.id}`}
+                                  value={assignee.id}
+                                >
+                                  {assignee.name}
+                                </option>
+                              ))}
+                            </select>
+                            <div className="grid grid-cols-2 gap-2">
+                              <input
+                                name="budgetMin"
+                                type="number"
+                                min={0}
+                                step="0.01"
+                                defaultValue={commission.budgetMin ?? ""}
+                                placeholder="Budget min"
+                                className="rounded border border-neutral-300 px-2 py-1"
+                              />
+                              <input
+                                name="budgetMax"
+                                type="number"
+                                min={0}
+                                step="0.01"
+                                defaultValue={commission.budgetMax ?? ""}
+                                placeholder="Budget max"
+                                className="rounded border border-neutral-300 px-2 py-1"
+                              />
+                            </div>
+                            <input
+                              name="dueAt"
+                              type="date"
+                              defaultValue={formatDateInput(commission.dueAt)}
+                              className="w-full rounded border border-neutral-300 px-2 py-1"
+                            />
+                            <textarea
+                              name="linkedArtworkIds"
+                              rows={2}
+                              defaultValue={commission.linkedArtworkIds.join(",")}
+                              placeholder="Artwork IDs"
+                              className="w-full rounded border border-neutral-300 px-2 py-1"
+                            />
+                            <button
+                              type="submit"
+                              className="rounded border border-neutral-300 px-2 py-1 text-xs"
+                            >
+                              Save details
+                            </button>
+                          </form>
+                        </details>
+                        {commission.linkedArtworkIds.length > 0 ? (
+                          <p className="mt-2 text-[11px] text-neutral-500">
+                            Linked artworks: {commission.linkedArtworkIds.length}
+                          </p>
+                        ) : null}
                      </form>
                    ))
                  )}
@@ -449,6 +626,24 @@ export default async function CommissionsManagementPage({
          </table>
        </div>
      </section>
+
+      <section className="rounded-xl border border-neutral-200 p-6">
+        <h2 className="text-xl font-medium">Artwork Reference</h2>
+        <p className="mt-1 text-sm text-neutral-600">
+          Use these IDs when linking artworks to commissions.
+        </p>
+        <div className="mt-3 max-h-64 overflow-auto rounded border border-neutral-200 p-3 text-xs">
+          {artworks.length === 0 ? (
+            <p className="text-neutral-500">No artworks available.</p>
+          ) : (
+            artworks.map((artwork) => (
+              <p key={artwork.id} className="font-mono text-neutral-700">
+                {artwork.id} - {artwork.title}
+              </p>
+            ))
+          )}
+        </div>
+      </section>
    </AdminShell>
  )
 }
--- a/apps/admin/src/app/media/[id]/page.tsx
+++ b/apps/admin/src/app/media/[id]/page.tsx
@@ -1,10 +1,15 @@
+import {
+  getMediaUploadMaxBytes,
+  isMimeAllowedForMediaType,
+  mediaAssetTypeSchema,
+} from "@cms/content"
 import { deleteMediaAsset, getMediaAssetById, updateMediaAsset } from "@cms/db"
 import { Button } from "@cms/ui/button"
 import Link from "next/link"
 import { redirect } from "next/navigation"

 import { AdminShell } from "@/components/admin-shell"
-import { deleteStoredMediaObject } from "@/lib/media/storage"
+import { deleteStoredMediaObject, storeUpload } from "@/lib/media/storage"
 import { requirePermissionForRoute } from "@/lib/route-guards"

 export const dynamic = "force-dynamic"
@@ -79,7 +84,10 @@ function readTags(formData: FormData): string[] {
    .filter((item) => item.length > 0)
 }

-function redirectWithState(mediaAssetId: string, params: { notice?: string; error?: string }) {
+function redirectWithState(
+  mediaAssetId: string,
+  params: { notice?: string; error?: string },
+): never {
  const query = new URLSearchParams()

  if (params.notice) {
@@ -189,6 +197,80 @@ export default async function MediaAssetEditorPage({ params, searchParams }: Pag
    redirect("/media?notice=Media+asset+deleted")
  }

+  async function replaceMediaFileAction(formData: FormData) {
+    "use server"
+
+    await requirePermissionForRoute({
+      nextPath: "/media",
+      permission: "media:write",
+      scope: "team",
+    })
+
+    const fileEntry = formData.get("file")
+
+    if (!(fileEntry instanceof File) || fileEntry.size === 0) {
+      redirectWithState(mediaAssetId, { error: "Select a replacement file first." })
+    }
+
+    const parsedType = mediaAssetTypeSchema.safeParse(mediaAsset.type)
+
+    if (!parsedType.success) {
+      redirectWithState(mediaAssetId, { error: "Media type is invalid for replacement." })
+    }
+    const mediaType = parsedType.data
+    const maxBytes = getMediaUploadMaxBytes(mediaType)
+
+    if (fileEntry.size > maxBytes) {
+      redirectWithState(mediaAssetId, {
+        error: `Replacement file is too large for ${mediaType}. Max ${Math.floor(maxBytes / 1024 / 1024)} MB.`,
+      })
+    }
+
+    if (!isMimeAllowedForMediaType(mediaType, fileEntry.type)) {
+      redirectWithState(mediaAssetId, {
+        error: `File type ${fileEntry.type || "unknown"} is not allowed for ${mediaType}.`,
+      })
+    }
+
+    const previousStorageKey = mediaAsset.storageKey
+
+    try {
+      const stored = await storeUpload({
+        file: fileEntry,
+        assetId: mediaAssetId,
+        variant: "original",
+        fileRole: "original",
+      })
+
+      await updateMediaAsset({
+        id: mediaAssetId,
+        storageKey: stored.storageKey,
+        mimeType: fileEntry.type || null,
+        sizeBytes: fileEntry.size,
+        width: null,
+        height: null,
+      })
+
+      if (previousStorageKey && previousStorageKey !== stored.storageKey) {
+        try {
+          await deleteStoredMediaObject(previousStorageKey)
+        } catch {
+          redirectWithState(mediaAssetId, {
+            notice: "Media file replaced, but old file cleanup failed in storage backend.",
+          })
+        }
+      }
+
+      const replacementNotice = stored.fallbackReason
+        ? `Media file replaced. ${stored.fallbackReason}`
+        : `Media file replaced via ${stored.provider}.`
+
+      redirectWithState(mediaAssetId, { notice: replacementNotice })
+    } catch {
+      redirectWithState(mediaAssetId, { error: "Failed to replace media file." })
+    }
+  }
+
  return (
    <AdminShell
      role={role}
@@ -254,6 +336,25 @@ export default async function MediaAssetEditorPage({ params, searchParams }: Pag
        ) : null}
      </section>

+      <section className="rounded-xl border border-neutral-200 p-6">
+        <h3 className="text-lg font-medium">Replace File</h3>
+        <p className="mt-1 text-sm text-neutral-600">
+          Upload a new source file for this media asset while keeping the same metadata and asset
+          ID.
+        </p>
+        <form action={replaceMediaFileAction} className="mt-4 flex flex-wrap items-center gap-3">
+          <input
+            name="file"
+            type="file"
+            required
+            className="rounded border border-neutral-300 px-3 py-2 text-sm"
+          />
+          <Button type="submit" variant="secondary">
+            Replace media file
+          </Button>
+        </form>
+      </section>
+
      <section className="rounded-xl border border-neutral-200 p-6">
        <div className="flex flex-wrap items-center justify-between gap-3">
          <div>
--- a/apps/admin/src/app/pages/[id]/page.tsx
+++ b/apps/admin/src/app/pages/[id]/page.tsx
@@ -42,6 +42,31 @@ function readNullableString(formData: FormData, field: string): string | null {
  return value.length > 0 ? value : null
 }

+function readNullableDate(formData: FormData, field: string): Date | null {
+  const value = readInputString(formData, field)
+
+  if (!value) {
+    return null
+  }
+
+  const parsed = new Date(value)
+  return Number.isNaN(parsed.getTime()) ? null : parsed
+}
+
+function formatDateTimeLocalInput(value: Date | null): string {
+  if (!value) {
+    return ""
+  }
+
+  const year = value.getFullYear()
+  const month = String(value.getMonth() + 1).padStart(2, "0")
+  const day = String(value.getDate()).padStart(2, "0")
+  const hour = String(value.getHours()).padStart(2, "0")
+  const minute = String(value.getMinutes()).padStart(2, "0")
+
+  return `${year}-${month}-${day}T${hour}:${minute}`
+}
+
 function redirectWithState(pageId: string, params: { notice?: string; error?: string }) {
  const query = new URLSearchParams()

@@ -109,6 +134,7 @@ export default async function PageEditorPage({ params, searchParams }: PageProps
        content: readInputString(formData, "content"),
        seoTitle: readNullableString(formData, "seoTitle"),
        seoDescription: readNullableString(formData, "seoDescription"),
+        scheduledPublishAt: readNullableDate(formData, "scheduledPublishAt"),
      })
    } catch {
      redirectWithState(pageId, {
@@ -224,6 +250,16 @@ export default async function PageEditorPage({ params, searchParams }: PageProps
            </label>
          </div>

+          <label className="space-y-1">
+            <span className="text-xs text-neutral-600">Scheduled publish (optional)</span>
+            <input
+              name="scheduledPublishAt"
+              type="datetime-local"
+              defaultValue={formatDateTimeLocalInput(page.scheduledPublishAt)}
+              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">Slug</span>
            <input
--- a/apps/admin/src/app/pages/page.tsx
+++ b/apps/admin/src/app/pages/page.tsx
@@ -29,6 +29,17 @@ function readNullableString(formData: FormData, field: string): string | null {
  return value.length > 0 ? value : null
 }

+function readNullableDate(formData: FormData, field: string): Date | null {
+  const value = readInputString(formData, field)
+
+  if (!value) {
+    return null
+  }
+
+  const parsed = new Date(value)
+  return Number.isNaN(parsed.getTime()) ? null : parsed
+}
+
 function redirectWithState(params: { notice?: string; error?: string }) {
  const query = new URLSearchParams()

@@ -62,6 +73,7 @@ async function createPageAction(formData: FormData) {
      content: readInputString(formData, "content"),
      seoTitle: readNullableString(formData, "seoTitle"),
      seoDescription: readNullableString(formData, "seoDescription"),
+      scheduledPublishAt: readNullableDate(formData, "scheduledPublishAt"),
    })
  } catch {
    redirectWithState({
@@ -122,6 +134,7 @@ export default async function PagesManagementPage({
                <th className="py-2 pr-4">Title</th>
                <th className="py-2 pr-4">Slug</th>
                <th className="py-2 pr-4">Status</th>
+                <th className="py-2 pr-4">Scheduled</th>
                <th className="py-2 pr-4">Updated</th>
                <th className="py-2 pr-4">Action</th>
              </tr>
@@ -129,7 +142,7 @@ export default async function PagesManagementPage({
            <tbody>
              {pages.length === 0 ? (
                <tr>
-                  <td className="py-3 text-neutral-500" colSpan={5}>
+                  <td className="py-3 text-neutral-500" colSpan={6}>
                    No pages yet.
                  </td>
                </tr>
@@ -139,6 +152,11 @@ export default async function PagesManagementPage({
                    <td className="py-3 pr-4">{page.title}</td>
                    <td className="py-3 pr-4 text-neutral-600">/{page.slug}</td>
                    <td className="py-3 pr-4">{page.status}</td>
+                    <td className="py-3 pr-4 text-neutral-600">
+                      {page.scheduledPublishAt
+                        ? page.scheduledPublishAt.toLocaleString("en-US")
+                        : "-"}
+                    </td>
                    <td className="py-3 pr-4 text-neutral-600">
                      {page.updatedAt.toLocaleDateString("en-US")}
                    </td>
--- a/apps/admin/src/components/pages/create-page-form.tsx
+++ b/apps/admin/src/components/pages/create-page-form.tsx
@@ -77,6 +77,15 @@ export function CreatePageForm({ action }: CreatePageFormProps) {
        </label>
      </div>

+      <label className="space-y-1">
+        <span className="text-xs text-neutral-600">Scheduled publish (optional)</span>
+        <input
+          name="scheduledPublishAt"
+          type="datetime-local"
+          className="w-full rounded border border-neutral-300 px-3 py-2 text-sm"
+        />
+      </label>
+
      <Button type="submit">Create page</Button>
    </form>
  )
--- a/apps/web/src/app/[locale]/layout.tsx
+++ b/apps/web/src/app/[locale]/layout.tsx
@@ -52,7 +52,7 @@ export default async function LocaleLayout({ children, params }: LocaleLayoutPro
    <NextIntlClientProvider locale={locale}>
      <Providers>
        <PublicHeaderBanner banner={banner} />
-        <PublicAnnouncements placement="global_top" />
+        <PublicAnnouncements placement="global_top" locale={locale} />
        <PublicSiteHeader />
        <main>{children}</main>
        <PublicSiteFooter />
--- a/apps/web/src/app/[locale]/page.tsx
+++ b/apps/web/src/app/[locale]/page.tsx
@@ -1,4 +1,4 @@
-import { getPublishedPageBySlugForLocale, listPosts } from "@cms/db"
+import { getPublishedPageBySlugForLocale, listPostsForLocale } from "@cms/db"
 import { getTranslations } from "next-intl/server"
 import { PublicAnnouncements } from "@/components/public-announcements"
 import { PublicPageView } from "@/components/public-page-view"
@@ -15,14 +15,14 @@ export default async function HomePage({ params }: HomePageProps) {

  const [homePage, posts, t] = await Promise.all([
    getPublishedPageBySlugForLocale("home", locale),
-    listPosts(),
+    listPostsForLocale(locale),
    getTranslations("Home"),
  ])

  return (
    <section>
      {homePage ? <PublicPageView page={homePage} /> : null}
-      <PublicAnnouncements placement="homepage" />
+      <PublicAnnouncements placement="homepage" locale={locale} />

      <section className="mx-auto flex w-full max-w-6xl flex-col gap-6 px-6 py-6 pb-16">
        <header className="space-y-3">
--- a/apps/web/src/app/[locale]/portfolio/page.tsx
+++ b/apps/web/src/app/[locale]/portfolio/page.tsx
@@ -44,6 +44,33 @@ function resolveGroupFilter(searchParams: SearchParamsInput) {
  return null
 }

+function resolveSort(searchParams: SearchParamsInput): "latest" | "title_asc" | "title_desc" {
+  const sort = readFirstValue(searchParams.sort)
+
+  if (sort === "title_asc" || sort === "title_desc") {
+    return sort
+  }
+
+  return "latest"
+}
+
+function buildPortfolioQuery(
+  filter: ReturnType<typeof resolveGroupFilter>,
+  sort: ReturnType<typeof resolveSort>,
+) {
+  const query: Record<string, string> = {}
+
+  if (filter) {
+    query[filter.groupType] = filter.groupSlug
+  }
+
+  if (sort !== "latest") {
+    query.sort = sort
+  }
+
+  return query
+}
+
 function findPreviewAsset(
  renditions: Array<{
    slot: string
@@ -67,6 +94,7 @@ function findPreviewAsset(
 export default async function PortfolioPage({ searchParams }: PortfolioPageProps) {
  const [resolvedSearchParams, t] = await Promise.all([searchParams, getTranslations("Portfolio")])
  const activeFilter = resolveGroupFilter(resolvedSearchParams)
+  const activeSort = resolveSort(resolvedSearchParams)

  const [groups, artworks] = await Promise.all([
    listPublishedPortfolioGroups(),
@@ -75,8 +103,11 @@ export default async function PortfolioPage({ searchParams }: PortfolioPageProps
        ? {
            groupType: activeFilter.groupType,
            groupSlug: activeFilter.groupSlug,
+            sort: activeSort,
          }
-        : undefined,
+        : {
+            sort: activeSort,
+          },
    ),
  ])

@@ -91,7 +122,10 @@ export default async function PortfolioPage({ searchParams }: PortfolioPageProps
      <section className="rounded-xl border border-neutral-200 p-4">
        <div className="flex flex-wrap items-center gap-2">
          <Link
-            href="/portfolio"
+            href={{
+              pathname: "/portfolio",
+              query: activeSort === "latest" ? undefined : { sort: activeSort },
+            }}
            className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
          >
            {t("filters.clear")}
@@ -100,7 +134,16 @@ export default async function PortfolioPage({ searchParams }: PortfolioPageProps
          {groups.galleries.map((group) => (
            <Link
              key={`gallery-${group.id}`}
-              href={{ pathname: "/portfolio", query: { gallery: group.slug } }}
+              href={{
+                pathname: "/portfolio",
+                query: {
+                  ...buildPortfolioQuery(activeFilter, activeSort),
+                  gallery: group.slug,
+                  album: undefined,
+                  category: undefined,
+                  tag: undefined,
+                },
+              }}
              className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
            >
              {t("filters.gallery")}: {group.name}
@@ -110,7 +153,16 @@ export default async function PortfolioPage({ searchParams }: PortfolioPageProps
          {groups.albums.map((group) => (
            <Link
              key={`album-${group.id}`}
-              href={{ pathname: "/portfolio", query: { album: group.slug } }}
+              href={{
+                pathname: "/portfolio",
+                query: {
+                  ...buildPortfolioQuery(activeFilter, activeSort),
+                  gallery: undefined,
+                  album: group.slug,
+                  category: undefined,
+                  tag: undefined,
+                },
+              }}
              className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
            >
              {t("filters.album")}: {group.name}
@@ -120,12 +172,73 @@ export default async function PortfolioPage({ searchParams }: PortfolioPageProps
          {groups.categories.map((group) => (
            <Link
              key={`category-${group.id}`}
-              href={{ pathname: "/portfolio", query: { category: group.slug } }}
+              href={{
+                pathname: "/portfolio",
+                query: {
+                  ...buildPortfolioQuery(activeFilter, activeSort),
+                  gallery: undefined,
+                  album: undefined,
+                  category: group.slug,
+                  tag: undefined,
+                },
+              }}
              className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
            >
              {t("filters.category")}: {group.name}
            </Link>
          ))}
+
+          {groups.tags.map((group) => (
+            <Link
+              key={`tag-${group.id}`}
+              href={{
+                pathname: "/portfolio",
+                query: {
+                  ...buildPortfolioQuery(activeFilter, activeSort),
+                  gallery: undefined,
+                  album: undefined,
+                  category: undefined,
+                  tag: group.slug,
+                },
+              }}
+              className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
+            >
+              {t("filters.tag")}: {group.name}
+            </Link>
+          ))}
+        </div>
+
+        <div className="mt-3 flex flex-wrap items-center gap-2">
+          <span className="text-xs uppercase tracking-wide text-neutral-500">
+            {t("sort.label")}:
+          </span>
+          <Link
+            href={{
+              pathname: "/portfolio",
+              query: buildPortfolioQuery(activeFilter, "latest"),
+            }}
+            className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
+          >
+            {t("sort.latest")}
+          </Link>
+          <Link
+            href={{
+              pathname: "/portfolio",
+              query: buildPortfolioQuery(activeFilter, "title_asc"),
+            }}
+            className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
+          >
+            {t("sort.titleAsc")}
+          </Link>
+          <Link
+            href={{
+              pathname: "/portfolio",
+              query: buildPortfolioQuery(activeFilter, "title_desc"),
+            }}
+            className="rounded-md border border-neutral-300 px-3 py-1.5 text-sm text-neutral-700 hover:bg-neutral-100"
+          >
+            {t("sort.titleDesc")}
+          </Link>
        </div>
      </section>

--- a/apps/web/src/components/public-announcements.tsx
+++ b/apps/web/src/components/public-announcements.tsx
@@ -3,6 +3,7 @@ import Link from "next/link"

 type PublicAnnouncementsProps = {
  placement: "global_top" | "homepage"
+  locale?: string
 }

 function AnnouncementCard({ announcement }: { announcement: PublicAnnouncement }) {
@@ -22,8 +23,8 @@ function AnnouncementCard({ announcement }: { announcement: PublicAnnouncement }
  )
 }

-export async function PublicAnnouncements({ placement }: PublicAnnouncementsProps) {
-  const announcements = await listActiveAnnouncements(placement)
+export async function PublicAnnouncements({ placement, locale }: PublicAnnouncementsProps) {
+  const announcements = await listActiveAnnouncements(placement, new Date(), locale)

  if (announcements.length === 0) {
    return null
--- a/apps/web/src/messages/de.json
+++ b/apps/web/src/messages/de.json
@@ -77,7 +77,14 @@
      "clear": "Filter zurücksetzen",
      "gallery": "Galerie",
      "album": "Album",
-      "category": "Kategorie"
+      "category": "Kategorie",
+      "tag": "Tag"
+    },
+    "sort": {
+      "label": "Sortierung",
+      "latest": "Neueste",
+      "titleAsc": "Titel A-Z",
+      "titleDesc": "Titel Z-A"
    },
    "fields": {
      "medium": "Medium",
--- a/apps/web/src/messages/en.json
+++ b/apps/web/src/messages/en.json
@@ -77,7 +77,14 @@
      "clear": "Clear filters",
      "gallery": "Gallery",
      "album": "Album",
-      "category": "Category"
+      "category": "Category",
+      "tag": "Tag"
+    },
+    "sort": {
+      "label": "Sort",
+      "latest": "Latest",
+      "titleAsc": "Title A-Z",
+      "titleDesc": "Title Z-A"
    },
    "fields": {
      "medium": "Medium",
--- a/apps/web/src/messages/es.json
+++ b/apps/web/src/messages/es.json
@@ -77,7 +77,14 @@
      "clear": "Limpiar filtros",
      "gallery": "Galería",
      "album": "Álbum",
-      "category": "Categoría"
+      "category": "Categoría",
+      "tag": "Etiqueta"
+    },
+    "sort": {
+      "label": "Orden",
+      "latest": "Más recientes",
+      "titleAsc": "Título A-Z",
+      "titleDesc": "Título Z-A"
    },
    "fields": {
      "medium": "Técnica",
--- a/apps/web/src/messages/fr.json
+++ b/apps/web/src/messages/fr.json
@@ -77,7 +77,14 @@
      "clear": "Réinitialiser les filtres",
      "gallery": "Galerie",
      "album": "Album",
-      "category": "Catégorie"
+      "category": "Catégorie",
+      "tag": "Tag"
+    },
+    "sort": {
+      "label": "Tri",
+      "latest": "Plus récents",
+      "titleAsc": "Titre A-Z",
+      "titleDesc": "Titre Z-A"
    },
    "fields": {
      "medium": "Médium",
--- a/packages/content/src/announcements.ts
+++ b/packages/content/src/announcements.ts
@@ -1,11 +1,13 @@
 import { z } from "zod"

 export const announcementPlacementSchema = z.enum(["global_top", "homepage"])
+export const announcementLocaleSchema = z.enum(["de", "en", "es", "fr"])

 export const createAnnouncementInputSchema = z.object({
  title: z.string().min(1).max(180),
  message: z.string().min(1).max(500),
  placement: announcementPlacementSchema.default("global_top"),
+  targetLocales: z.array(announcementLocaleSchema).default([]),
  priority: z.number().int().min(0).default(100),
  ctaLabel: z.string().max(120).nullable().optional(),
  ctaHref: z.string().max(500).nullable().optional(),
@@ -19,6 +21,7 @@ export const updateAnnouncementInputSchema = z.object({
  title: z.string().min(1).max(180).optional(),
  message: z.string().min(1).max(500).optional(),
  placement: announcementPlacementSchema.optional(),
+  targetLocales: z.array(announcementLocaleSchema).optional(),
  priority: z.number().int().min(0).optional(),
  ctaLabel: z.string().max(120).nullable().optional(),
  ctaHref: z.string().max(500).nullable().optional(),
--- a/packages/content/src/commissions.ts
+++ b/packages/content/src/commissions.ts
@@ -23,7 +23,21 @@ export const createCommissionInputSchema = z.object({
  description: z.string().max(4000).nullable().optional(),
  status: commissionStatusSchema.default("new"),
  customerId: z.string().uuid().nullable().optional(),
-  assignedUserId: z.string().max(120).nullable().optional(),
+  assignedUserId: z.string().uuid().nullable().optional(),
+  linkedArtworkIds: z.array(z.string().uuid()).default([]),
+  budgetMin: z.number().nonnegative().nullable().optional(),
+  budgetMax: z.number().nonnegative().nullable().optional(),
+  dueAt: z.date().nullable().optional(),
+})
+
+export const updateCommissionInputSchema = z.object({
+  id: z.string().uuid(),
+  title: z.string().min(1).max(180).optional(),
+  description: z.string().max(4000).nullable().optional(),
+  status: commissionStatusSchema.optional(),
+  customerId: z.string().uuid().nullable().optional(),
+  assignedUserId: z.string().uuid().nullable().optional(),
+  linkedArtworkIds: z.array(z.string().uuid()).optional(),
  budgetMin: z.number().nonnegative().nullable().optional(),
  budgetMax: z.number().nonnegative().nullable().optional(),
  dueAt: z.date().nullable().optional(),
@@ -57,6 +71,7 @@ export const updateCommissionStatusInputSchema = z.object({
 export type CommissionStatus = z.infer<typeof commissionStatusSchema>
 export type CreateCustomerInput = z.infer<typeof createCustomerInputSchema>
 export type CreateCommissionInput = z.infer<typeof createCommissionInputSchema>
+export type UpdateCommissionInput = z.infer<typeof updateCommissionInputSchema>
 export type CreatePublicCommissionRequestInput = z.infer<
  typeof createPublicCommissionRequestInputSchema
 >
--- a/packages/content/src/media.ts
+++ b/packages/content/src/media.ts
@@ -99,6 +99,7 @@ export const updateMediaAssetInputSchema = z.object({
  location: z.string().max(180).nullable().optional(),
  capturedAt: z.date().nullable().optional(),
  tags: z.array(z.string().min(1).max(100)).optional(),
+  storageKey: z.string().max(500).nullable().optional(),
  mimeType: z.string().max(180).nullable().optional(),
  width: z.number().int().positive().nullable().optional(),
  height: z.number().int().positive().nullable().optional(),
--- a/packages/content/src/pages-navigation.ts
+++ b/packages/content/src/pages-navigation.ts
@@ -103,6 +103,7 @@ export const createPageInputSchema = z.object({
  content: z.string().min(1),
  seoTitle: z.string().max(180).nullable().optional(),
  seoDescription: z.string().max(320).nullable().optional(),
+  scheduledPublishAt: z.date().nullable().optional(),
 })

 export const updatePageInputSchema = z.object({
@@ -114,6 +115,7 @@ export const updatePageInputSchema = z.object({
  content: z.string().min(1).optional(),
  seoTitle: z.string().max(180).nullable().optional(),
  seoDescription: z.string().max(320).nullable().optional(),
+  scheduledPublishAt: z.date().nullable().optional(),
 })

 export const upsertPageTranslationInputSchema = z.object({
--- a/packages/db/prisma/migrations/20260213012000_commission_linked_artworks/migration.sql
+++ b/packages/db/prisma/migrations/20260213012000_commission_linked_artworks/migration.sql
@@ -0,0 +1,2 @@
+ALTER TABLE "Commission"
+ADD COLUMN "linkedArtworkIds" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[];
--- a/packages/db/prisma/migrations/20260213014500_announcement_locale_targeting/migration.sql
+++ b/packages/db/prisma/migrations/20260213014500_announcement_locale_targeting/migration.sql
@@ -0,0 +1,2 @@
+ALTER TABLE "Announcement"
+ADD COLUMN "targetLocales" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[];
--- a/packages/db/prisma/migrations/20260213020500_page_scheduled_publish/migration.sql
+++ b/packages/db/prisma/migrations/20260213020500_page_scheduled_publish/migration.sql
@@ -0,0 +1,4 @@
+ALTER TABLE "Page"
+ADD COLUMN "scheduledPublishAt" TIMESTAMP(3);
+
+CREATE INDEX "Page_scheduledPublishAt_idx" ON "Page"("scheduledPublishAt");
--- a/packages/db/prisma/schema.prisma
+++ b/packages/db/prisma/schema.prisma
@@ -291,6 +291,7 @@ model Page {
  seoTitle       String?
  seoDescription String?
  publishedAt    DateTime?
+  scheduledPublishAt DateTime?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  navItems       NavigationItem[]
@@ -386,6 +387,7 @@ model Commission {
  status         String
  customerId     String?
  assignedUserId String?
+  linkedArtworkIds String[] @default([])
  budgetMin      Float?
  budgetMax      Float?
  dueAt          DateTime?
@@ -404,6 +406,7 @@ model Announcement {
  title     String
  message   String
  placement String
+  targetLocales String[] @default([])
  priority  Int      @default(100)
  ctaLabel  String?
  ctaHref   String?
--- a/packages/db/src/announcements.test.ts
+++ b/packages/db/src/announcements.test.ts
@@ -41,13 +41,18 @@ describe("announcements service", () => {
  it("queries only visible announcements in the given placement", async () => {
    mockDb.announcement.findMany.mockResolvedValue([])

-    await listActiveAnnouncements("homepage")
+    await listActiveAnnouncements("homepage", new Date("2026-02-12T10:00:00.000Z"), "en")

    expect(mockDb.announcement.findMany).toHaveBeenCalledTimes(1)
    expect(mockDb.announcement.findMany.mock.calls[0]?.[0]).toMatchObject({
      where: {
        placement: "homepage",
        isVisible: true,
+        AND: [
+          {
+            OR: [{ targetLocales: { isEmpty: true } }, { targetLocales: { has: "en" } }],
+          },
+        ],
      },
    })
  })
--- a/packages/db/src/announcements.ts
+++ b/packages/db/src/announcements.ts
@@ -13,6 +13,7 @@ export type PublicAnnouncement = {
  ctaLabel: string | null
  ctaHref: string | null
  placement: string
+  targetLocales: string[]
  priority: number
 }

@@ -50,13 +51,26 @@ export async function deleteAnnouncement(id: string) {
 export async function listActiveAnnouncements(
  placement: AnnouncementPlacement,
  now = new Date(),
+  locale?: string,
 ): Promise<PublicAnnouncement[]> {
+  const localeFilter =
+    locale && locale.length > 0
+      ? {
+          AND: [
+            {
+              OR: [{ targetLocales: { isEmpty: true } }, { targetLocales: { has: locale } }],
+            },
+          ],
+        }
+      : undefined
+
  const announcements = await db.announcement.findMany({
    where: {
      placement,
      isVisible: true,
      OR: [{ startsAt: null }, { startsAt: { lte: now } }],
      AND: [{ OR: [{ endsAt: null }, { endsAt: { gte: now } }] }],
+      ...(localeFilter ?? {}),
    },
    orderBy: [{ priority: "asc" }, { createdAt: "desc" }],
    select: {
@@ -66,6 +80,7 @@ export async function listActiveAnnouncements(
      ctaLabel: true,
      ctaHref: true,
      placement: true,
+      targetLocales: true,
      priority: true,
    },
  })
--- a/packages/db/src/commissions.ts
+++ b/packages/db/src/commissions.ts
@@ -3,6 +3,7 @@ import {
  createCommissionInputSchema,
  createCustomerInputSchema,
  createPublicCommissionRequestInputSchema,
+  updateCommissionInputSchema,
  updateCommissionStatusInputSchema,
 } from "@cms/content"

@@ -57,6 +58,16 @@ export async function createCommission(input: unknown) {
  })
 }

+export async function updateCommission(input: unknown) {
+  const payload = updateCommissionInputSchema.parse(input)
+  const { id, ...data } = payload
+
+  return db.commission.update({
+    where: { id },
+    data,
+  })
+}
+
 export async function createPublicCommissionRequest(input: unknown) {
  const payload = createPublicCommissionRequestInputSchema.parse(input)
  const normalizedEmail = payload.customerEmail.trim().toLowerCase()
--- a/packages/db/src/index.ts
+++ b/packages/db/src/index.ts
@@ -14,6 +14,7 @@ export {
  createPublicCommissionRequest,
  listCommissions,
  listCustomers,
+  updateCommission,
  updateCommissionStatus,
 } from "./commissions"
 export {
--- a/packages/db/src/media-foundation.ts
+++ b/packages/db/src/media-foundation.ts
@@ -9,15 +9,18 @@ import {
  updateGroupingInputSchema,
  updateMediaAssetInputSchema,
 } from "@cms/content"
+import type { Prisma } from "@prisma/client"

 import { db } from "./client"

 type PublicArtworkGroupType = "gallery" | "album" | "category" | "tag"
+type PublicArtworkSort = "latest" | "title_asc" | "title_desc"

 type ListPublishedArtworksInput = {
  groupType?: PublicArtworkGroupType
  groupSlug?: string
  limit?: number
+  sort?: PublicArtworkSort
 }

 export async function listMediaAssets(limit = 24) {
@@ -431,6 +434,12 @@ export async function listPublishedArtworks(input: ListPublishedArtworksInput =
  const where: Record<string, unknown> = {
    isPublished: true,
  }
+  const orderBy: Prisma.ArtworkOrderByWithRelationInput[] =
+    input.sort === "title_asc"
+      ? [{ title: "asc" }, { updatedAt: "desc" }]
+      : input.sort === "title_desc"
+        ? [{ title: "desc" }, { updatedAt: "desc" }]
+        : [{ updatedAt: "desc" }]

  if (input.groupType && input.groupSlug) {
    if (input.groupType === "gallery") {
@@ -474,7 +483,7 @@ export async function listPublishedArtworks(input: ListPublishedArtworksInput =

  return db.artwork.findMany({
    where,
-    orderBy: [{ updatedAt: "desc" }],
+    orderBy,
    take,
    include: {
      renditions: {
--- a/packages/db/src/pages-navigation.ts
+++ b/packages/db/src/pages-navigation.ts
@@ -29,6 +29,15 @@ function resolvePublishedAt(status: string): Date | null {
  return status === "published" ? new Date() : null
 }

+function resolvePublicPageWhere(slug?: string) {
+  const now = new Date()
+
+  return {
+    ...(slug ? { slug } : {}),
+    OR: [{ status: "published" }, { scheduledPublishAt: { lte: now } }],
+  }
+}
+
 export async function listPages(limit = 50) {
  return db.page.findMany({
    orderBy: [{ updatedAt: "desc" }],
@@ -38,7 +47,7 @@ export async function listPages(limit = 50) {

 export async function listPublishedPageSlugs() {
  const pages = await db.page.findMany({
-    where: { status: "published" },
+    where: resolvePublicPageWhere(),
    orderBy: { updatedAt: "desc" },
    select: {
      slug: true,
@@ -57,19 +66,13 @@ export async function getPageById(id: string) {

 export async function getPublishedPageBySlug(slug: string) {
  return db.page.findFirst({
-    where: {
-      slug,
-      status: "published",
-    },
+    where: resolvePublicPageWhere(slug),
  })
 }

 export async function getPublishedPageBySlugForLocale(slug: string, locale: string) {
  const page = await db.page.findFirst({
-    where: {
-      slug,
-      status: "published",
-    },
+    where: resolvePublicPageWhere(slug),
    include: {
      translations: {
        where: {
@@ -98,11 +101,13 @@ export async function getPublishedPageBySlugForLocale(slug: string, locale: stri

 export async function createPage(input: unknown) {
  const payload = createPageInputSchema.parse(input)
+  const isImmediatelyPublished = payload.status === "published"

  return db.page.create({
    data: {
      ...payload,
      publishedAt: resolvePublishedAt(payload.status),
+      scheduledPublishAt: isImmediatelyPublished ? null : (payload.scheduledPublishAt ?? null),
    },
  })
 }
@@ -110,6 +115,7 @@ export async function createPage(input: unknown) {
 export async function updatePage(input: unknown) {
  const payload = updatePageInputSchema.parse(input)
  const { id, ...data } = payload
+  const isImmediatelyPublished = data.status === "published"

  return db.page.update({
    where: { id },
@@ -117,6 +123,12 @@ export async function updatePage(input: unknown) {
      ...data,
      publishedAt:
        data.status === undefined ? undefined : data.status === "published" ? new Date() : null,
+      scheduledPublishAt:
+        data.scheduledPublishAt === undefined
+          ? undefined
+          : isImmediatelyPublished
+            ? null
+            : data.scheduledPublishAt,
    },
  })
 }
Author	SHA1	Message	Date
Citali	103c45c926	chore(todo): finalize mvp1 commissions and portfolio status	2026-02-12 23:13:41 +01:00
Citali	d2a645df6f	feat(media): add in-place media file replacement flow	2026-02-12 23:12:29 +01:00
Citali	18b709b4b0	feat(pages): add scheduled publish workflow for page management	2026-02-12 23:10:47 +01:00
Citali	a2bea6326e	feat(web): complete portfolio public filter and sort integration	2026-02-12 23:08:25 +01:00
Citali	60c9035743	feat(announcements): add locale audience targeting and published-home news	2026-02-12 23:05:39 +01:00
Citali	741883465c	feat(commissions): add editable assignment and artwork linkage	2026-02-12 22:59:53 +01:00