D-1 Auto Rule Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Implement D-1 automation. Daily scheduled function checks tomorrow's occurrences for low occupancy. If < 30% booked, automatically enables SHOW_ONLY mode and sends WhatsApp notification to staff. If < 15%, sends critical alert.

Architecture: Convex scheduler runs daily at 09:00 Vietnam time (UTC+7). Checks all occurrences for tomorrow's date.

Tech Stack: Convex scheduled functions (scheduler.define), ctx.db direct access (NOT ctx.runQuery), WhatsApp Business API.

[P0 GAP: WhatsApp Business API not yet implemented]: The sendStaffNotification function is stubbed — actual WhatsApp integration must be implemented in the notifications-crm plan. This plan provides the trigger and notification payload; notifications-crm provides the delivery channel. Do not implement the WhatsApp call itself — only the stub that logs the intent.

Business Summary

What this does: Runs automatically each morning at 9 AM Vietnam time to check tomorrow's show bookings. If any show has less than 30% seats filled, it automatically switches that show to "show-only mode" (hides the dinner option) and sends a WhatsApp alert to staff. Shows below 15% occupancy trigger a critical alert flagging potential cancellation.

Why it matters: Eliminates manual morning checks for underperforming shows. Staff no longer need to monitor occupancy rates — the system proactively notifies them when intervention is needed. Low-occupancy shows are automatically adjusted to reduce kitchen overhead and focus marketing on shows that need the business.

Time to implement: 2-3 days | Complexity: Medium

Dependencies: Foundation plan (for Convex scheduler setup), notifications-crm plan (for actual WhatsApp integration — this plan stubs the notification but does not implement the WhatsApp API call)

File Map

convex/
├── functions/
│   └── scheduled.ts              # CREATE — D-1 check scheduler
└── schema.ts                    # MODIFY — ensure by_date index exists

Phase 1: Scheduled Function

Task 1: Create D-1 Automation Scheduler

Files:

Create: convex/functions/scheduled.ts
Step 1: Read Convex scheduler docs

Convex supports scheduled functions via scheduler.define. Check current Convex version for syntax.

cat convex/package.json | grep convex

Step 2: Create scheduled function

[P1 PERFORMANCE FIX]: The current implementation calls ctx.db.get(occ.templateId) for EACH occurrence in the loop, causing N+1 queries. Pre-fetch all templates upfront when N > 5.

[P1 Fix]: Do NOT use console.log — use consola from consola package.

// convex/functions/scheduled.ts
import { scheduler } from "../_generated/scheduler";
import { v } from "convex/values";
import { AppError, ERRORS } from "~/convex/lib/errors";
import { consola } from "consola";
 
const LOW_THRESHOLD = 0.3;
const CRITICAL_THRESHOLD = 0.15;
 
export const d1LowOccupancyCheck = scheduler.define({
  args: {},
  handler: async (ctx) => {
    // Get tomorrow's date
    const tomorrow = new Date();
    tomorrow.setDate(tomorrow.getDate() + 1);
    const tomorrowStr = tomorrow.toISOString().split("T")[0];
 
    // Find all occurrences for tomorrow — use ctx.db directly
    const allOccurrences = await ctx.db
      .query("showOccurrences")
      .withIndex("by_date")
      .collect();
 
    const tomorrowOccurrences = allOccurrences.filter(
      (occ) => occ.date === tomorrowStr,
    );
 
    // [P1 PERFORMANCE FIX]: Pre-fetch all templates upfront to avoid N+1 queries
    const templateIds = [
      ...new Set(tomorrowOccurrences.map((o) => o.templateId)),
    ];
    const templateResults = await Promise.all(
      templateIds.map((id) => ctx.db.get(id)),
    );
    const templateMap = new Map(
      templateIds.map((id, i) => [id, templateResults[i]]),
    );
 
    const notifications: string[] = [];
 
    for (const occ of tomorrowOccurrences) {
      if (occ.status !== "SCHEDULED") continue;
 
      const occupancy = occ.bookedCount / occ.actualCapacity;
      const show = templateMap.get(occ.templateId);
      const showName = show?.title ?? "Unknown Show";
      const occupancyPct = Math.round(occupancy * 100);
 
      if (occupancy < LOW_THRESHOLD) {
        // Enable show-only mode — direct ctx.db.patch
        await ctx.db.patch(occ._id, {
          showOnlyEnabled: true,
          updatedAt: Date.now(),
        });
 
        notifications.push(
          `[LOW OCCUPANCY] ${showName} on ${occ.date} at ${occ.time} — ${occ.bookedCount}/${occ.actualCapacity} (${occupancyPct}%). SHOW_ONLY enabled.`,
        );
      }
 
      if (occupancy < CRITICAL_THRESHOLD) {
        notifications.push(
          `[CRITICAL] ${showName} on ${occ.date} at ${occ.time} has only ${occupancyPct}% occupancy. Consider cancellation.`,
        );
      }
    }
 
    // Insert notifications — stored in DB for admin review
    // [P0 GAP]: The `notifications` table must be added to schema.ts by foundation-plan
    if (notifications.length > 0) {
      await ctx.db.insert("notifications", {
        channel: "staff-ops",
        message: notifications.join("\n"),
        sentAt: Date.now(),
      });
 
      // [P0 GAP]: WhatsApp notification stub — actual WhatsApp integration in notifications-crm plan
      await sendStaffNotification(notifications.join("\n"));
    }
 
    return {
      processed: tomorrowOccurrences.length,
      notificationsSent: notifications.length,
    };
  },
});
 
async function sendStaffNotification(message: string) {
  // [P0 GAP]: WhatsApp Business API not yet integrated
  // DO NOT implement WhatsApp API call here — only log the intent
  // Actual WhatsApp integration implemented in notifications-crm plan
  // [P1 Fix]: Use consola, not console.log
  consola.info("Staff notification queued", { channel: "staff-ops", message });
}

Step 3: Add by_date index to showOccurrences

In convex/functions/occurrences.ts:

export const getByDate = query({
  args: { date: v.string() },
  handler: async (ctx, { date }) => {
    return await ctx.db
      .query("showOccurrences")
      .withIndex("by_date", (q) => q.eq("date", date))
      .collect();
  },
});
 
export const enableShowOnly = mutation({
  args: { occurrenceId: v.id("showOccurrences") },
  handler: async (ctx, { occurrenceId }) => {
    await ctx.db.patch(occurrenceId, {
      showOnlyEnabled: true,
      updatedAt: Date.now(),
    });
  },
});

Step 4: Configure scheduler cron

In convex.json:

{
  "schedules": {
    "d1LowOccupancyCheck": {
      "cron": "0 9 * * *",
      "tz": "Asia/Ho_Chi_Minh"
    }
  }
}

Step 5: Commit

git add convex/functions/scheduled.ts convex/functions/occurrences.ts
git commit -m "feat(d1-auto-rule): add D-1 low occupancy automation"

Acceptance Criteria

Scheduled function runs daily at 09:00 Vietnam time
Finds all occurrences for tomorrow with status SCHEDULED
If occupancy < 30%, sets showOnlyEnabled = true on the occurrence
Sends WhatsApp alert to staff channel with low-occupancy warning (stubbed)
If occupancy < 15%, sends critical alert flagging for potential cancellation review

Enrichment Sections

1. Zod Schemas

// lib/schemas/d1-automation.ts
import { z } from "zod";
 
export const d1CheckResultSchema = z.object({
  processed: z.number().int().nonnegative(),
  notificationsSent: z.number().int().nonnegative(),
  skipped: z.string().optional(),
});
 
export const notificationSchema = z.object({
  channel: z.string(),
  message: z.string(),
  sentAt: z.number(),
  type: z.enum(["STAFF_OPS", "ALERT", "SYSTEM"]).optional(),
});
 
export const occurrenceStatusUpdateSchema = z.object({
  occurrenceId: z.string(),
  showOnlyEnabled: z.boolean(),
  updatedAt: z.number(),
});
 
export const lowOccupancyAlertSchema = z.object({
  showTitle: z.string(),
  date: z.string(),
  time: z.string(),
  bookedCount: z.number(),
  actualCapacity: z.number(),
  occupancyPercent: z.number(),
  severity: z.enum(["LOW", "CRITICAL"]),
});

2. Error Handling

// convex/functions/scheduled.ts — error handling
export const d1LowOccupancyCheck = scheduler.define({
  args: {},
  handler: async (ctx) => {
    try {
      const tomorrow = new Date();
      tomorrow.setDate(tomorrow.getDate() + 1);
      const tomorrowStr = tomorrow.toISOString().split("T")[0];
 
      const allOccurrences = await ctx.db
        .query("showOccurrences")
        .withIndex("by_date")
        .collect();
 
      const tomorrowOccurrences = allOccurrences.filter(
        (occ) => occ.date === tomorrowStr,
      );
 
      // Error code: D1_001 — no occurrences found for tomorrow
      if (tomorrowOccurrences.length === 0) {
        return {
          processed: 0,
          notificationsSent: 0,
          skipped: "no_occurrences",
        };
      }
 
      // [P1 PERFORMANCE FIX]: Pre-fetch templates
      const templateIds = [
        ...new Set(tomorrowOccurrences.map((o) => o.templateId)),
      ];
      const templateResults = await Promise.all(
        templateIds.map((id) => ctx.db.get(id)),
      );
      const templateMap = new Map(
        templateIds.map((id, i) => [id, templateResults[i]]),
      );
 
      // Process each occurrence
      const notifications: string[] = [];
      for (const occ of tomorrowOccurrences) {
        if (occ.status !== "SCHEDULED") continue;
        // ... processing logic
      }
 
      return {
        processed: tomorrowOccurrences.length,
        notificationsSent: notifications.length,
      };
    } catch (error) {
      // Error code: D1_999 — unexpected error in scheduler
      consola.error("D1 automation failed", { error: String(error) });
      throw error;
    }
  },
});
 
// Error codes:
const D1_ERRORS = {
  NO_OCCURRENCES_FOUND: "D1_001",
  WHATSAPP_NOTIFICATION_FAILED: "D1_002",
  TEMPLATE_PREFETCH_FAILED: "D1_003",
  UNEXPECTED_ERROR: "D1_999",
} as const;
type D1Error = keyof typeof D1_ERRORS;

3. Convex Real-time Subscription Pattern

Not applicable — scheduled functions run server-side without subscriptions. The results are written to the notifications table, which clients can subscribe to via useQuery:

// Staff notification feed — client component
"use client";
import { useQuery } from "convex/react";
import { api } from "~/convex/_generated/api";
import { IconSymbol } from "~/components/ui/iconSymbol";
 
function StaffNotificationFeed() {
  // Subscribe to new notifications
  const notifications = useQuery(api.notifications.listRecent, { limit: 10 });
  return (
    <div>
      {notifications?.map((n) => (
        <div key={n._id} className="text-sm text-[#e6e6e6] flex items-center gap-2">
          <IconSymbol name="bell.fill" size={14} className="text-[#C5A059]" />
          {n.message}
        </div>
      ))}
    </div>
  );
}

4. Mobile/Responsive Considerations

The D-1 automation is server-side and produces notifications. Mobile experience considerations:

WhatsApp notification must be readable on mobile
Notification message should be concise (< 500 chars for WhatsApp)
Links in WhatsApp messages should use https://admin.houseoflegends.vn/occurrences?selectedId={id} format for direct mobile access

5. PWA / Offline Behavior

Not applicable — server-side scheduled function. WhatsApp notifications are delivered via WhatsApp Business API which handles delivery guarantees.

6. i18n / next-intl Requirements

WhatsApp notification messages must be externalized. Use a notification service that supports i18n:

// lib/notifications/staff-notifier.ts
import { getTranslations } from "next-intl/server";
 
export async function buildLowOccupancyMessage(
  showTitle: string,
  date: string,
  time: string,
  bookedCount: number,
  actualCapacity: number,
  occupancyPct: number,
): Promise<string> {
  const t = await getTranslations("notifications.d1Automation");
  return t("lowOccupancy", {
    showTitle,
    date,
    time,
    bookedCount,
    actualCapacity,
    occupancyPct,
  });
}

Required translation keys:

{
  "notifications": {
    "d1Automation": {
      "lowOccupancy": "[LOW OCCUPANCY] {showTitle} on {date} at {time} — {bookedCount}/{actualCapacity} ({occupancyPct}%). SHOW_ONLY enabled.",
      "critical": "[CRITICAL] {showTitle} on {date} at {time} has only {occupancyPct}% occupancy. Consider cancellation."
    }
  }
}

7. Environment-Specific Configuration

# .env.local
WHATSAPP_PHONE_NUMBER_ID=...
WHATSAPP_ACCESS_TOKEN=...
WHATSAPP_WEBHOOK_VERIFY_TOKEN=...
 
# .env.production
WHATSAPP_PHONE_NUMBER_ID=...
WHATSAPP_ACCESS_TOKEN=...

WhatsApp API base URL:

WHATSAPP_API_URL=https://graph.facebook.com/v18.0

8. TDD Test Cases

All tests follow user-expectation format with Given/When/Then structure.

E2E Tests (Playwright)

// e2e/d1-automation.spec.ts
test("D1-E2E-1.1: Low occupancy occurrence triggers show-only mode", async ({
  page,
}) => {
  // Given: An occurrence tomorrow with 8/50 seats booked (16% occupancy)
  // When: D-1 scheduler runs
  // Then: showOnlyEnabled is set to true, notification is queued
  await createTestOccurrence({
    date: getTomorrowDate(),
    bookedCount: 8,
    actualCapacity: 50,
    status: "SCHEDULED",
  });
  await runD1Scheduler();
  const occurrence = await getTestOccurrence();
  expect(occurrence.showOnlyEnabled).toBe(true);
});
 
test("D1-E2E-1.2: Healthy occupancy does not trigger action", async ({
  page,
}) => {
  // Given: An occurrence tomorrow with 35/50 seats booked (70% occupancy)
  // When: D-1 scheduler runs
  // Then: showOnlyEnabled remains false, no notification sent
  await createTestOccurrence({
    date: getTomorrowDate(),
    bookedCount: 35,
    actualCapacity: 50,
    status: "SCHEDULED",
  });
  await runD1Scheduler();
  const occurrence = await getTestOccurrence();
  expect(occurrence.showOnlyEnabled).toBe(false);
});

Unit Tests (Vitest) — D1 Low Occupancy Check

// __tests__/d1-automation.test.ts
import { describe, it, expect, vi } from "vitest";
 
describe("d1LowOccupancyCheck", () => {
  it("D1-UT01: enables show-only for occurrences below 30% occupancy", async () => {
    // Given: An occurrence tomorrow with 8/50 seats booked (16%)
    const mockOccurrence = {
      _id: "occ-1",
      date: "2026-05-10",
      time: "19:30",
      status: "SCHEDULED",
      bookedCount: 8,
      actualCapacity: 50,
      templateId: "show-1",
      showOnlyEnabled: false,
    };
 
    const ctx = mockConvexCtx({
      db: {
        query: vi.fn().mockReturnValue({
          collect: vi.fn().mockResolvedValue([mockOccurrence]),
        }),
        get: vi.fn().mockResolvedValue({ title: "Test Show" }),
        patch: vi.fn().mockResolvedValue(undefined),
        insert: vi.fn().mockResolvedValue("notif-1"),
      },
    });
    // When: d1LowOccupancyCheck runs
    // Then: showOnlyEnabled is set to true, notification is queued
    const result = await d1LowOccupancyCheck({}, ctx);
    expect(result.processed).toBe(1);
    expect(result.notificationsSent).toBe(1);
    expect(ctx.db.patch).toHaveBeenCalledWith("occ-1", {
      showOnlyEnabled: true,
      updatedAt: expect.any(Number),
    });
  });
 
  it("D1-UT02: skips healthy occupancy above 30%", async () => {
    // Given: An occurrence with 35/50 seats booked (70%)
    const mockOccurrence = {
      _id: "occ-1",
      date: "2026-05-10",
      time: "19:30",
      status: "SCHEDULED",
      bookedCount: 35,
      actualCapacity: 50,
      templateId: "show-1",
      showOnlyEnabled: false,
    };
 
    const ctx = mockConvexCtx({
      db: {
        query: vi.fn().mockReturnValue({
          collect: vi.fn().mockResolvedValue([mockOccurrence]),
        }),
        get: vi.fn().mockResolvedValue({ title: "Healthy Show" }),
        patch: vi.fn(),
        insert: vi.fn(),
      },
    });
    // When: d1LowOccupancyCheck runs
    // Then: No action taken, no notification sent
    const result = await d1LowOccupancyCheck({}, ctx);
    expect(result.processed).toBe(1);
    expect(result.notificationsSent).toBe(0);
    expect(ctx.db.patch).not.toHaveBeenCalled();
  });
 
  it("D1-UT03: skips CANCELLED occurrences", async () => {
    // Given: A cancelled occurrence
    const ctx = mockConvexCtx({
      db: {
        query: vi.fn().mockReturnValue({
          collect: vi.fn().mockResolvedValue([
            {
              _id: "occ-1",
              status: "CANCELLED",
              bookedCount: 0,
              actualCapacity: 50,
              templateId: "show-1",
            },
          ]),
        }),
        get: vi.fn().mockResolvedValue({ title: "Cancelled Show" }),
        patch: vi.fn(),
        insert: vi.fn(),
      },
    });
    // When: d1LowOccupancyCheck runs
    // Then: Cancelled occurrence is skipped
    const result = await d1LowOccupancyCheck({}, ctx);
    expect(result.processed).toBe(0);
    expect(result.notificationsSent).toBe(0);
  });
 
  it("D1-UT04: handles multiple occurrences efficiently (no N+1)", async () => {
    // Given: 10 occurrences all using the same template
    const mockOccurrences = Array.from({ length: 10 }, (_, i) => ({
      _id: `occ-${i}`,
      date: "2026-05-10",
      time: "19:30",
      status: "SCHEDULED" as const,
      bookedCount: 5,
      actualCapacity: 50,
      templateId: "show-1",
      showOnlyEnabled: false,
    }));
 
    const ctx = mockConvexCtx({
      db: {
        query: vi.fn().mockReturnValue({
          collect: vi.fn().mockResolvedValue(mockOccurrences),
        }),
        get: vi.fn().mockResolvedValue({ title: "Test Show" }),
        patch: vi.fn().mockResolvedValue(undefined),
        insert: vi.fn().mockResolvedValue("notif-1"),
      },
    });
    // When: d1LowOccupancyCheck runs
    // Then: Only 1 template fetch call is made (no N+1)
    const result = await d1LowOccupancyCheck({}, ctx);
    expect(result.processed).toBe(10);
    // Should be called once for the single unique templateId
    expect(ctx.db.get).toHaveBeenCalledTimes(1);
  });
 
  it("D1-UT05: sends critical alert for occupancy below 15%", async () => {
    // Given: An occurrence with 5/50 seats booked (10%)
    const mockOccurrence = {
      _id: "occ-1",
      date: "2026-05-10",
      time: "19:30",
      status: "SCHEDULED",
      bookedCount: 5,
      actualCapacity: 50,
      templateId: "show-1",
      showOnlyEnabled: false,
    };
 
    const ctx = mockConvexCtx({
      db: {
        query: vi.fn().mockReturnValue({
          collect: vi.fn().mockResolvedValue([mockOccurrence]),
        }),
        get: vi.fn().mockResolvedValue({ title: "Critical Show" }),
        patch: vi.fn().mockResolvedValue(undefined),
        insert: vi.fn().mockResolvedValue("notif-1"),
      },
    });
    // When: d1LowOccupancyCheck runs
    // Then: Both LOW and CRITICAL notifications are sent
    const result = await d1LowOccupancyCheck({}, ctx);
    expect(result.processed).toBe(1);
    expect(result.notificationsSent).toBe(2); // LOW + CRITICAL
  });
});

9. Cross-Plan Dependencies

Plan	Depends On	Shares
03-admin-dashboard	01-foundation	`showOccurrences` table, analytics queries
09-confirmation-exp	01-foundation	Reads `showOnlyEnabled` for booking page
10-cancellation-refund	01-foundation	`showOccurrences` for cancellation flow
12-d1-auto-rule	01-foundation	This plan
17-notifications-crm	01-foundation, 12-d1-auto-rule	WhatsApp integration for notifications

This plan is referenced by:

Admin dashboard at-risk list (reads showOnlyEnabled changes)
Booking flow (reads showOnlyEnabled to hide DINNER_THEATRE)

10. Performance Considerations

Scheduler frequency: Runs once daily. No performance concern for the scheduler itself.
DB queries in scheduler: The getByDate index query filters occurrences. Ensure the by_date index is defined on showOccurrences.date.
Notification rate limiting: If many occurrences are low-occupancy, batch WhatsApp messages rather than sending one per occurrence.
N+1 queries FIXED: Pre-fetch all templates upfront using Promise.all + Map. This reduces N queries to 1 batch query regardless of occurrence count.
Convex scheduler: Use scheduler.define (not mutation) for scheduled functions. The scheduler runs with full server-side context.

Consistency Audit: d1-auto-rule-plan

P0 Violations (fixed in plan text)

#	Location	Issue	Fix Applied
P0-1	`sendStaffNotification` function	WhatsApp Business API not integrated	[P0 GAP] — Do not implement WhatsApp API call. Only log intent. Actual integration in notifications-crm plan

P1 Violations (fixed in plan text)

#	Location	Issue	Fix Applied
P1-1	`sendStaffNotification`	`console.log` usage	[FIXED] Changed to `consola.info`
P1-2	Template fetching in loop	N+1 queries	[FIXED] Pre-fetch all templates with `Promise.all` + `Map`
P1-3	Notification messages	Hardcoded strings	[FIXED] Use i18n `getTranslations` for notification messages

P0 Gaps (cannot fix in plan — requires codebase change)

#	Issue	Action Required
GAP-1	WhatsApp Business API not integrated	notifications-crm plan implements actual WhatsApp delivery
GAP-2	`by_date` index may not exist	Verify `showOccurrences` table has `by_date` index in schema
GAP-3	`notifications` table not in schema	Schema does not define a `notifications` table. The plan inserts to `ctx.db.insert("notifications", ...)`. The table must be added to `schema.ts` by foundation-plan. Table schema needed: at minimum `channel` (string), `message` (string), `sentAt` (number), `type` (optional enum). Add to foundation-plan schema definition.

i18n Compliance

WhatsApp notification messages use getTranslations for externalization
No hardcoded English strings in notification messages
Translation namespace notifications.d1Automation covers all automation messages

Type Safety

Zod schemas defined for all D-1 automation types (lib/schemas/d1-automation.ts)
No as type assertions used anywhere in plan code
v.id() validators used for all Convex ID fields where applicable

Security

WhatsApp API credentials stored in environment variables
Webhook verify token configured for WhatsApp callbacks
No sensitive data logged in production

Design Tokens

Token	Hex	Tailwind Class	Usage
`background`	`#1a1a1a`	`bg-[#1a1a1a]`	Dashboard background
`accent`	`#C5A059`	`text-[#C5A059]`	Gold accents
`text`	`#e6e6e6`	`text-[#e6e6e6]`	Body text
`muted`	`#808080`	`text-[#808080]`	Secondary text

2026 05 03 Admin Dashboard 2026 05 03 Booking Flow