chore(todo): finalize mvp1 commissions and portfolio status

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
 - [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)
@@ -158,14 +158,14 @@ This file is the single source of truth for roadmap and delivery progress.
 
 ### 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)
 
@@ -370,6 +370,9 @@ This file is the single source of truth for roadmap and delivery progress.
 - [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`.

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>

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

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>

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>
   )

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>
 

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",

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",

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",

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",

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(),

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({

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");

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[]

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: {

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,
     },
   })
 }