Scheduled-publish sweep scans every collection on every tick; partial scheduled_at index unused

[MA2153]

Description

On every scheduled-maintenance tick, the scheduled-publishing sweep issues one query per collection:

SELECT * FROM "ec_article"
WHERE scheduled_at IS NOT NULL AND scheduled_at <= ? AND deleted_at IS NULL
ORDER BY scheduled_at ASC LIMIT ?

Two problems compound:

1. The partial index built for this query is not used. The migrations create exactly the right index:

CREATE INDEX "idx_ec_article_scheduled" ON "ec_article" (scheduled_at) WHERE scheduled_at IS NOT NULL

but SQLite plans the query against the low-selectivity deleted_at index instead, then sorts:

SEARCH ec_article USING INDEX idx_ec_article_deleted_status (deleted_at=?)
USE TEMP B-TREE FOR ORDER BY

Measured on a collection with 7,753 live rows: 7,755 rows read, 0 rows returned.

2. The sweep runs unconditionally, per collection, per tick — even when no content in the database is scheduled at all. On a site where no entry has scheduled_at set, every tick reads every live content row across every collection to publish nothing. With six collections totalling ~13,000 live rows and a one-minute Cron Trigger, that is ~18.7M D1 rows read per day, entirely wasted.

This is D1-billable read volume that scales with total content size and tick frequency, not with the amount of scheduled work.

Two independent fixes are possible, and they stack:

• Guard the scan. Skip the per-collection query when nothing is scheduled — e.g. a single cheap probe against the partial index up front, or tracking a "next scheduled at" watermark that the sweep can compare against before touching any collection.
• Get the partial index used. Ordering by an indexed column with a LIMIT should be an index walk. Dropping the redundant scheduled_at IS NOT NULL predicate (already implied by scheduled_at <= ?) may be enough to let the planner match the partial index, but the reliable fix is to not issue the query at all when there is no work.

Note that this is the second of the two queries reported in #2211. That issue was closed by #2407, which fixed the revision-history scan only — the scheduled_at scan was never addressed. Filing separately rather than reopening.

Steps to reproduce

1. Deploy an EmDash site on Cloudflare Workers + D1 with several content collections holding a few thousand entries each.
2. Leave scheduled_at unset on every entry (no scheduled publishing in use).
3. Enable a Cron Trigger per the Scheduled Publishing guide.
4. Observe D1 "Rows read" climb steadily with no scheduled content and no traffic.
5. Confirm per query with the GraphQL Analytics API (d1QueriesAdaptiveGroups, ordered by sum_rowsRead_DESC): each collection's scheduled_at query shows rowsRead ≈ live row count and rowsReturned = 0 on every tick.

Environment

• emdash version: 0.35.0
• Runtime: Cloudflare Workers + D1
• Cron Trigger: * * * * *

Logs / error output

# EXPLAIN QUERY PLAN, measured against remote D1
SEARCH ec_article USING INDEX idx_ec_article_deleted_status (deleted_at=?)
USE TEMP B-TREE FOR ORDER BY

# Actual cost, same query, same database
rows_read: 7755
rows_returned: 0

# Index that exists and is not chosen
CREATE INDEX "idx_ec_article_scheduled" ON "ec_article" (scheduled_at) WHERE scheduled_at IS NOT NULL

# Rows with scheduled_at set, all collections
0