Bulk Select/Delete/Archive/Restore Quests, Adventures, Background Assets

Author: Christian Denzon
Reviewer: Patrick Babala, Clyde Ador
Creation Date: September 30, 2026
Last Updated: September 30, 2026
Status: Approved and Merged
References:

• Issue #727 — [3.6.1] Bulk Select/Delete/Archive/Restore Quests, Adventures, Background Assets

• Parent epic: Issue #37 — [3.6] Archive Asset

• Update PRs: #728 Bulk Selection (core), #711 Archive Asset, #725 Cron Auth Fix, #726 Deletion-Warning Email Link, #770 Archive Auto-Purge & Sorting

• Related, documented separately: #745 MFPS Core Logic (bulk Move on the shared action bar, folder-aware authorization), #780 Reusable Confirm-Destructive Modal (issue #777)

INTRODUCTION AND GOALS

Problem Summary: This feature is the creator-side content clean-up toolkit for the three main content types on WyzQuests: quests, adventures, and background assets. Before it, deleting or archiving required clicking each item's own trash button, and every delete API accepted exactly one resource ID per request. Bulk Actions (issue #727, delivered by PR #728) adds hover-revealed checkboxes and a floating contextual action bar so a creator can select many items at once and Archive, Restore, or Delete (permanently) them in one flow.

The lifecycle under the selection layer was established by the Archive Asset work (PR #711) and completed by the auto-purge schedule (PR #770): archiving is a soft delete with a fixed 120-day retention window, a warning email is sent on day 90, and a daily cron permanently purges anything that reaches day 120.

Goals and Non-Goals:

• Bulk selection is available on four creator surfaces: the Quests tab, the Adventures tab, the Archive tab, and the Background Assets pages (library + archive).

• Bulk actions: Archive (quests/adventures/assets), Restore (archive only), Delete — permanent, gated by a typed DELETE confirmation.

• All bulk routes accept an ID array (content_ids / asset_ids) while remaining backward-compatible with the single-ID payloads (content_id / asset_id) used by per-item actions.

• Ownership is verified for every selected item before any write; a single unauthorized item fails the whole request (no partial unauthorized mutations).

• Responses report the exact ID sets the server changed (archivedIds / restoredIds / deletedIds) plus missingIds / skippedIds; the client removes from state only what the server confirms.

• The archive lifecycle is fully automated: 120-day retention, a day-90 warning email grouped per creator, and a day-120 purge that deletes database rows first and then cleans storage.

• Restored quests and adventures return to the Root of the library — their previous folder assignment is cleared.

• Destructive confirmation is standardized on the shared ConfirmDestructiveModal (typed token) with a server-side Zod double-check.

• Non-goals: cross-page or whole-library selection (selection is bound to the rendered, filtered list); bulk Move (shipped with MFPS PR #745, documented in the MFPS Internal Technical Guide — the action bar is shared between both features); undo/restore after a permanent delete; configurable retention per agency or content type (fixed at 120 days); bulk actions on reviewer, learner, or admin surfaces.

Glossary:

• Archive — soft delete. Quests/adventures set publishing_status = 'archived' and stamp archived_at; background assets set deleted_at (and expires_at).

• Purge — permanent delete of the database row plus its media from the public-assets bucket.

• Retention window — 120 days from the archive timestamp (ARCHIVE_RETENTION_DAYS in lib/archive/getDaysRemaining.ts).

• Selection mode — the UI state while at least one item is selected; per-item action buttons hide and the floating action bar appears.

• Typed confirmation — the destructive modal requires the literal token DELETE; the server re-validates the token independently.

• Skipped ID — an item that was found but was already in the target state (already archived, or not archived when restoring).

• Missing ID — a requested ID that was not found (already deleted or not visible to the requester).

• Agency-scoped ownership — a user may act on a resource if they created it, or if it belongs to a creator in the same agency.

• Folder-admin fallback (MFPS) — for quests and adventures only, a folder owner/admin (including inherited authority) may bulk-act on content contained in their folders.

• Bulk bar — SelectionActionBar, the floating contextual action bar shared by bulk actions and bulk move.

HIGH-LEVEL ARCHITECTURE

System Diagram:

Frontend: Next.js (App Router), React, TypeScript, Tailwind CSS, Shadcn/UI components (Button, Card, Checkbox, Dialog, Input, Label), Sonner toast notifications. Selection UI lives in components/creator/SelectionActionBar.tsx, components/creator/MyContent.tsx, components/creator/TrashContent.tsx, components/creator/background-assets/AssetList.tsx, app/creator/archive/page.tsx, with checkboxes rendered by components/content-library/ContentCard.tsx and components/creator/ContentListRow.tsx.

Backend: Next.js route handlers with Zod validation in shared/schemas/contentManagementSchema.ts, standardized responses via ApiResponseHelper (lib/api/response.ts), creator auth and agency ownership checks in lib/auth/agencyOwnership.ts, folder fallback via canManageItemsInFolders (lib/folder/folderAccess.ts), and archive domain logic in lib/archive/.

Database: Supabase Postgres. quests and adventures carry publishing_status, archived_at, and deletion_warning_sent_at; asset_metadata carries deleted_at, expires_at, and deletion_warning_sent_at. No schema change was required for bulk itself — the bulk layer reuses the columns introduced by the archive lifecycle.

Storage: Supabase Storage bucket public-assets. Purge deletes database rows first and then removes media only for the rows that were successfully deleted.

Authentication note: The platform uses Supabase Auth. Creator routes resolve the session through getCreatorAuthContext(), which maps the Supabase user to app_users. The legacy column names clerk_id / user_clerk_id still exist and store Supabase Auth UUIDs — they are naming artifacts only and must never be treated as Clerk IDs. The server uses the service-role Supabase client (lib/supabase.ts), so route-handler authorization is the enforcement point; RLS is defense-in-depth for direct PostgREST access.

Scheduled jobs: Two GitHub Actions workflows run daily at 10:00 UTC against develop (:3000) and staging (:3001): cron-pending-deletion.yml (day-90 warning emails) and cron-purge-expired.yml (day-120 purge). Both authenticate with Authorization: Bearer $CRON_SECRET.

DETAILED DESIGN & IMPLEMENTATION

Bulk Selection Model

Surfaces and wiring (current code):

Interaction rules:

1. Each card/row renders a 24×24 checkbox overlay in the top-left corner. It is role="checkbox", focusable (tabIndex={0}), exposes aria-checked, and toggles on click or on Enter / Space.

2. Checkboxes are revealed on hover (opacity 60% → 100%). They stay fully visible whenever selection mode is active, and forever at 60% opacity on the Quests/Adventures tabs in MyContent (alwaysShowCheckbox). On the cards of TrashContent the checkbox takes the place of the expiry pill on hover.

3. Clicking any checkbox enters selection mode. On MyContent the mode is derived from a non-empty selection, so deselecting everything returns the grid to normal.

4. Shift+click selects the inclusive range between the last toggled index and the clicked index, using the currently rendered list order (additive — it does not deselect).

5. Select All selects every ID in the currently filtered/rendered list (search, status, skills, and sort filters are respected). Deselect All clears the set but keeps the bar open until the user exits via the X button.

6. While selection mode is active: selected cards get a ring-2 ring-blue-500 highlight, per-item action buttons are hidden, and card clicks toggle selection instead of opening the item.

7. After every bulk action the client removes only the IDs returned by the server (archivedIds / restoredIds / deletedIds) and then clears the selection.

8. The confirmation modal is a Radix Dialog, so Escape / overlay click closes it by default; the isSubmitting guard blocks closing while a request is in flight.

SelectionActionBar props (shared with bulk Move):

The bar is fixed to the bottom-center of the viewport (fixed bottom-6 left-1/2 -translate-x-1/2 z-50) with brand-navy styling, so entering selection mode never pushes the content grid down. It renders role="toolbar" with aria-live="polite" on the count and wraps on small screens.

Database Schema

Archive columns (from the Archive Asset lifecycle, reused by bulk actions):

-- quests / adventures (20260728, backfilled 20260729)
ALTER TABLE quests
  ADD COLUMN archived_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE adventures
  ADD COLUMN archived_at TIMESTAMPTZ DEFAULT NULL;
CREATE INDEX idx_quests_archived_at ON quests (archived_at);
CREATE INDEX idx_adventures_archived_at ON adventures (archived_at);
 
-- warning tracking (20260729) — quests, adventures, asset_metadata
ALTER TABLE quests ADD COLUMN deletion_warning_sent_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE adventures ADD COLUMN deletion_warning_sent_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE asset_metadata ADD COLUMN deletion_warning_sent_at TIMESTAMPTZ DEFAULT NULL;
 
CREATE INDEX idx_quests_deletion_warning ON quests (archived_at)
  WHERE publishing_status = 'archived' AND deletion_warning_sent_at IS NULL;
CREATE INDEX idx_adventures_deletion_warning ON adventures (archived_at)
  WHERE publishing_status = 'archived' AND deletion_warning_sent_at IS NULL;
CREATE INDEX idx_asset_metadata_deletion_warning ON asset_metadata (deleted_at)
  WHERE deleted_at IS NOT NULL AND deletion_warning_sent_at IS NULL;
 
-- asset soft delete (pre-existing, 20260424)
ALTER TABLE asset_metadata ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE asset_metadata ADD COLUMN expires_at TIMESTAMPTZ DEFAULT NULL;
CREATE INDEX idx_asset_metadata_deleted_at ON asset_metadata (deleted_at);

Implementation notes:

• publishing_status = 'archived' is the authoritative archive flag for quests/adventures; archived_at is the timestamp that drives the countdown and purge. Rows archived before the column existed were backfilled with archived_at = updated_at.

• Background assets use deleted_at as the archive flag. expires_at is written as deleted_at + 120 days, but the purge selects on deleted_at — expires_at is informational only.

• deletion_warning_sent_at prevents duplicate warning emails and is set only after the email is sent successfully.

• Retention is application-side: ARCHIVE_RETENTION_DAYS = 120 in lib/archive/getDaysRemaining.ts (single source of truth).

Migrations involved (apply in order; PR #728 added none):

API Specification

All content and asset routes return the standard envelope from ApiResponseHelper: { success, message, data, error }. The two asset mutation routes that predate the helper (delete-assets, restore-assets) return the same shape via raw NextResponse ({ success, message, data }).

Endpoint matrix:

PATCH /api/creator/archive-content
Body: { content_ids: string[] } or { content_id: string } (UUID) + content_type: "quests" | "adventures".
Fetches the existing rows with their folder columns, verifies ownership per item, filters out already-archived rows, then updates publishing_status = 'archived', archived_at = now, updated_at = now.

PATCH /api/creator/restore-content
Body: same shape as archive. Restores archived rows to publishing_status = 'draft', clears archived_at, and clears the folder association (quest_folder_id / adventure_folder_id and project_folder_id → null) so the item returns to the Root of the library.

DELETE /api/creator/permanent-delete
Body: { content_ids } or { content_id } + content_type + confirm_text (required; must be exactly DELETE). Ownership is checked per item; the DB delete is a single .in("id", ids) statement. Afterwards, storage cleanup runs for each deleted item at {content_type}/{id}: it lists the folder, deletes matching asset_metadata rows (file_path LIKE '{path}/%') for the item's creator (not the requester), and removes the storage objects. A failed token writes an audit_logs row (action: PERMANENT_DELETE_FAILED) and returns 400.

DELETE /api/creator/delete-assets (archive)
Body: { asset_ids } or { asset_id }. Skips assets that already have deleted_at, then sets deleted_at = now and expires_at = now + 120 days.

PATCH /api/creator/restore-assets
Body: { asset_ids } or { asset_id }. Clears deleted_at and expires_at; reports found-but-not-archived items as skipped.

DELETE /api/creator/purge-assets (permanent)
Body: { asset_ids } or { asset_id } + confirm_text: "DELETE" (Zod literal). Ownership is verified per asset via verifyAgencyResourceOwnership (no folder fallback). Database rows are deleted first; storage removal runs only for the rows in the returned deletedIdSet. For videos, the .jpg poster sibling is removed as well.

API Helpers

• getCreatorAuthContext() (lib/auth/agencyOwnership.ts) — resolves the Supabase Auth session into an AgencyAuthContext (userId, userInternalId, agencyId, isAgencyMember). Throws AuthenticationError (401) or AccountSuspendedError (403, mapped by ApiResponseHelper.handleError).

• verifyAgencyResourceOwnership(context, resourceCreatorId) — returns the creator ID when the requester is the creator, or when the creator belongs to the same agency (checked through agency_members, app_users.agency_id, and agency ownership); otherwise throws AuthorizationError (403). Called once per selected item in every bulk route.

• canManageItemsInFolders(items, context) (lib/folder/folderAccess.ts) — MFPS folder fallback used by the quest/adventure routes: a folder owner/admin (including inherited authority) may archive, delete, or restore contained content even when they are not the creator. Background asset routes do not use this fallback.

• agencyResourceFilter(context) — builds the .or(...) filter used by the listing endpoints so agency members see the same-agency content in their Archive views.

• ApiResponseHelper (lib/api/response.ts) — the standardized envelope and status mapping (validationError 400, forbidden 403, notFound 404, internalError 500, handleError for thrown errors). 5xx responses are logged server-side with stack traces.

• Zod schemas (shared/schemas/contentManagementSchema.ts): archiveContentSchema, restoreContentSchema, permanentDeleteSchema, bulkAssetIdsSchema, purgeAssetsSchema (+ inferred types ArchiveContentInput, RestoreContentInput, PermanentDeleteInput, BulkAssetIdsInput, PurgeAssetsInput). Each accepts a single ID or an ID array and exposes a refine message (“Either content_id or content_ids is required” / “Either asset_id or asset_ids is required”).

• Archive domain: ARCHIVE_RETENTION_DAYS and getDaysRemaining(dateString, retentionDays?) (lib/archive/getDaysRemaining.ts), sortArchivedItems (lib/archive/sortArchivedItems.ts), and purgeExpiredQuests / purgeExpiredAdventures / purgeExpiredAssets (lib/archive/purgeArchivedContent.ts).

Core Logic and Workflow

Bulk archive flow (quests / adventures):

1. The user enters selection mode and picks items; SelectionActionBar appears.

2. Archive sends PATCH /api/creator/archive-content with content_ids and the active content_type.

3. The route validates the payload and fetches all requested rows with their folder columns.

4. For each row: creator/agency ownership is checked first; on failure the route falls back to canManageItemsInFolders. One unauthorized item → 403 for the whole request.

5. Already-archived rows are excluded and reported as skippedIds; rows that were not found are reported as missingIds.

6. The remaining rows are updated in one statement (publishing_status, archived_at, updated_at).

7. The client removes exactly archivedIds from state, shows a toast with that count, and clears the selection.

Bulk permanent delete flow (quests / adventures):

1. Delete opens ConfirmDestructiveModal showing the count and the scrollable list of selected titles; the confirm button stays disabled until DELETE is typed.

2. DELETE /api/creator/permanent-delete re-validates the payload, including confirm_text === "DELETE"; a mismatch writes the PERMANENT_DELETE_FAILED audit row and returns 400.

3. Ownership/folder checks run for every item.

4. Rows are deleted in one statement and the deleted IDs are captured (.select("id")).

5. For each deleted item, storage cleanup runs at {content_type}/{id} using the item's creator ID; cleanup failures are logged as warnings and never turn a successful delete into an error.

6. The client removes deletedIds, toasts the server-reported count, closes the modal, and clears the selection.

Bulk restore flow:

1. The Archive tab mixes quests and adventures, so the client groups the selection by contentType and issues one PATCH /api/creator/restore-content request per type; both must succeed (Promise.all + success:false check).

2. The route restores archived rows to draft, clears archived_at, and clears folder columns.

3. The client removes restoredIds and toasts “Restored N items”.

Asset flows: the same shape with the asset endpoints. Archive sets deleted_at / expires_at; restore clears them; purge deletes rows first and then removes storage for the deleted set only. Asset routes verify creator/agency ownership but have no folder fallback.

Archive retention lifecycle (per content type):

1. Day 0 — archive. A date-based {days}d left badge (or “Today”) renders from getDaysRemaining(archived_at); three days or fewer is styled as expiring soon. New archives are immediately restorable.

2. Day 90 — warning. GET /api/cron/check-pending-deletion selects archived items with archived_at <= now − (120 − 30) days and deletion_warning_sent_at IS NULL, groups them per creator, and sends one AutoDeletionWarningEmail listing every expiring item with a single “Go to Archive” link. The link is built from NEXT_PUBLIC_APP_URL || APP_URL || "https://wyzquests.com" so it is always absolute. Sent items are stamped with deletion_warning_sent_at.

3. Day 120 — purge. GET /api/cron/purge-expired-archived computes the cutoff now − 120 days and runs the three purge functions. Each deletes the database rows first, then cleans storage with Promise.allSettled, and reports { deleted, errors[] } per content type.

4. Cron authentication. Both endpoints require CRON_SECRET, accepted either as Authorization: Bearer <secret> or the legacy ?secret= query parameter. The GitHub Actions workflows deliberately use the header (PR #725): the secret may contain +, which a query string decodes as a space and would 401. Both workflows run daily at 10:00 UTC against develop (:3000) and staging (:3001) and fail the job on a non-200 response.

Partial-success semantics: archive and restore return skippedIds (found but already in the target state) and missingIds (not found); permanent delete and purge return missingIds. Bulk handlers derive their toast counts and state removal exclusively from the returned sets, falling back to the selected set only when the payload is absent. This is why a selection containing an already-archived item does not disappear from the UI while still existing server-side.

Confirmation & Destructive UX

Current implementation: destructive bulk actions use the shared components/shared/confirm-destructive-modal.tsx (ConfirmDestructiveModal, PR #780 / issue #777). It is fully controlled (open / onOpenChange), requires an exact confirmToken (case-insensitive mode optional) before enabling the destructive button, resets the token on every open, guards close/confirm while isSubmitting, and accepts an extraContent slot — used by the bulk flows to render a bounded, scrollable list of the selected item names keyed by stable IDs.

Server-side double-check: purgeAssetsSchema declares confirm_text: z.literal("DELETE"); permanentDeleteSchema requires a non-empty confirm_text that the route compares to DELETE and audits on failure. The modal is the client half only.

Superseded claim (stale facts corrected): PR #728 shipped a bespoke components/creator/BulkDeleteModal.tsx, and its description still says so. That component no longer exists: it was deleted when destructive paths were standardized on ConfirmDestructiveModal (PR #780) and its behavior (scrollable item list, required DELETE token, branded destructive styling) now lives in the shared modal. Similarly, PR #728's description mentions only Archive/Restore/Delete on the bar — the bar later gained Move (showMove / onMove, MFPS PR #745), and the “Restore All” label was shortened to Restore. The single-asset archive path for background assets still uses its own DeleteAssetModal (“Move to Archive”, 120-day copy); permanent purge uses the shared modal.

INFRASTRUCTURE & OPERATIONS

Dependencies:

• Supabase Postgres (quests, adventures, asset_metadata) and Supabase Storage (bucket public-assets).

• Supabase Auth for creator identity; the service-role Supabase client on the server.

• EmailService (Emailit) and the AutoDeletionWarningEmail template for day-90 warnings.

• GitHub Actions + SSH runner for the two cron workflows; CRON_SECRET must exist in the develop and staging env files.

• Zod for all payload validation; Shadcn/UI + Sonner for the UI.

Monitoring and Alerting:

• No dedicated metrics. 5xx errors are logged server-side by ApiResponseHelper with stack traces; cron failures fail the GitHub Actions job, which is the primary alert surface.

• Watch 403s (“You do not have permission to …”) on bulk routes — they usually indicate an ownership/folder-fallback mismatch or a migration not yet deployed in that environment.

• Watch 400s (“already archived” / “not archived”) — usually stale UI state after a concurrent action in another tab.

• Watch the purge cron response JSON: { quests, adventures, assets } each report { deleted, errors[] }. Non-empty errors[] means partial failures (fetch/DB); storage cleanup failures are logged as warnings only.

• Watch the warning cron counters sent / skipped / errors; skipped means the creator has no email, errors usually points at the email provider or branding lookup.

• audit_logs rows with action = PERMANENT_DELETE_FAILED indicate typed-confirmation failures (or probing).

Deployment Plan:

• Apply the three migrations in order where they are not already deployed (see the migration table). They are prerequisite for the archive columns and the warning/purge crons, not for the selection UI itself.

• The bulk endpoints are backward-compatible with the single-ID payloads, so backend and frontend can be deployed together or in either order.

• Ensure CRON_SECRET is present in the develop/staging env files and that both workflows are enabled.

• No feature flags. No production cron target exists in these workflows (develop :3000 and staging :3001 only) — confirm whether production purge/notification is expected to run from these workflows before relying on it.

• Verify retention behavior end-to-end on a seeded environment: archive → countdown badge → (simulate a 90-day-old timestamp) warning email → (simulate a 120-day-old timestamp) purge.

• Run the QA matrix below and confirm toasts, counts, and state removal match the server responses.

TESTING AND QUALITY ASSURANCE

Testing Strategy (manual):

Automated coverage:

• There is no automated test coverage for the bulk paths (selection components, bulk routes, schemas, purge helpers). The nearest existing test is tests/creator/api/background-assets/deleteAsset.test.ts, which covers the single-asset deleteAsset helper (lib/backgroundAssets/tests/deleteAsset) and its asset_id payload.

• Relevant suites run with npx jest tests/creator and npx jest tests/api.

Known Limitations:

• Selection is bound to the currently rendered, filtered list; there is no cross-page or whole-library “select all”.

• The retention window is fixed at 120 days (single application constant); expires_at on assets is written but not read by the purge, which uses deleted_at.

• The purge and warning workflows target develop and staging only; there is no production cron target in these workflow files.

• Restoring clears the previous folder assignment by design — restored items always return to Root.

• Permanent delete cannot be undone, and storage cleanup is best-effort (failures are logged, not retried; rows are deleted first so no broken references remain in the UI).

• Assets have no folder-admin fallback; only the creator or a same-agency member may bulk-act on them.

• No server-side cap on the number of IDs per request; a very large selection is sent as one request per content type.

• Bulk progress is a single in-flight request per type — there is no per-item progress UI, and selection is not preserved across tab changes or refetches.

MAINTENANCE AND SUPPORT

Troubleshooting:

• An item does not disappear after a bulk action → inspect the response archivedIds / restoredIds / deletedIds; the client intentionally removes only those IDs.

• “Content is already archived” / “Assets are already archived” → the item state changed elsewhere (another tab/session); refresh and retry.

• 403 on a bulk action the user should be allowed to perform → check the folder fallback: quests/adventures call canManageItemsInFolders; assets require creator or same-agency ownership.

• Agency member gets 403 → verify the creator's agency linkage (agency_members active row, app_users.agency_id, or agency ownership); verifyAgencyResourceOwnership requires a same-agency match.

• Storage files remain after a purge → cleanup is best-effort and only logs warnings; the DB rows are already gone, so re-running the purge will not retry those files.

• Wrong-token delete → expect 400 plus a PERMANENT_DELETE_FAILED row in audit_logs; verify the client sends confirm_text exactly as DELETE.

• Countdown badge missing or wrong → check archived_at on the row (legacy archived rows needed the backfill migration; getDaysRemaining falls back to the full retention window when it is null).

• Warning emails not sent → verify CRON_SECRET (use the Bearer header; a + in the secret breaks the ?secret= form), NEXT_PUBLIC_APP_URL / APP_URL (falls back to https://wyzquests.com), Emailit limits, and whether deletion_warning_sent_at was already stamped.

• Purge does not run → check the workflow target ports (develop :3000, staging :3001), that PM2 is serving those instances, and that the workflow's sed extraction finds CRON_SECRET in the env file.

• Restored item appears at Root instead of its old folder → intended; the restore route clears folder columns.

• Retention must change → update ARCHIVE_RETENTION_DAYS; the countdown, warning threshold (retention − 30), and purge cutoff derive from it.

DOCUMENT VERSION

1.0 — Initial Internal Technical Guide for Bulk Actions (bulk select / delete / archive / restore for quests, adventures, and background assets), covering the archive lifecycle, retention cron jobs, API contracts, and verified current-code behavior. Written by Christian Denzon, 09/30/2026. PRs: #728, #711, #725, #726, #770.