Scheduled posts never become visible on SQLite/D1: string comparison between ISO `scheduled_at` and SQLite `CURRENT_TIMESTAMP` always evaluates false

[adamslowe]

Description

On SQLite (and presumably D1), scheduled content remains hidden indefinitely after its scheduled_at time has passed. The post stays in status = 'scheduled', the public-facing query never includes it, and the rendered site continues to 404 the post until something else flips its status manually.

The cause is at packages/core/src/loader.ts:260-267 (the buildStatusCondition helper). When filtering for status = 'published', the query is widened to also include rows where status = 'scheduled' AND scheduled_at <= CURRENT_TIMESTAMP. On Postgres the scheduled_at text column is explicitly cast to timestamptz for the comparison. On SQLite there is no cast — the comparison is a raw text compare.

The two values are formatted differently:

• scheduled_at is stored as ISO 8601 with T and Z (e.g. 2026-05-05T01:41:59.000Z) — that's how the API writes it.
• SQLite's CURRENT_TIMESTAMP is space-separated, no Z (e.g. 2026-05-05 01:43:15).

Lexicographic comparison sees T (0x54) as greater than space (0x20), so '2026-05-05T01:41:59.000Z' <= '2026-05-05 01:43:15' is always false even when the scheduled time is well in the past. The scheduled row is never included in published results.

Expected: when the wall-clock time passes scheduled_at, the post should appear in published listings and at its public URL — same behavior as on Postgres.

Actual: the post remains hidden indefinitely. A site that depends on schedule-and-walk-away publishing will silently fail to publish.

A scorecard hit aside, this also has a downstream consequence: because EmDash's design is to flip visibility at query time rather than tick the row's status, the content:afterPublish hook never fires for scheduled content. Plugins that listen for publish (e.g. to fire a deploy-hook webhook for a static rebuild) never see scheduled posts on any backend, but on Postgres at least the post becomes visible.

Suggested fix: wrap both sides of the SQLite comparison in datetime(...), e.g.

return sql`(${sql.ref(statusField)} = 'published' OR (${sql.ref(statusField)} = 'scheduled' AND datetime(${sql.ref(scheduledAtField)}) <= datetime('now')))`;

(or normalize scheduled_at to space-separated UTC at write time, but the read-side fix is the smaller change and matches the existing Postgres branch).

Related work

• PR feat(cron): add publish-due endpoint and rebuildHooks config #772 (feat(cron): add publish-due endpoint and rebuildHooks config) — open since 2026-04-26. Adds an external POST /_emdash/api/cron/publish-due endpoint that flips status to 'published' and fires content:afterPublish + rebuildHooks. Complementary, not duplicative: that PR addresses the rebuild-hook side of scheduling and would incidentally mask this bug for users who wire an external cron trigger (once status is 'published', the broken comparison no longer matters because the row is included by the status='published' branch). It does not touch loader.ts and does not fix the read-path comparison itself, so anyone running on SQLite/D1 without the new cron wired up still hits the silent failure described above. The two changes belong together — happy to defer the patch shape to whichever ordering the maintainers prefer.

No matching open or closed issue found in emdash-cms/emdash as of 2026-05-04.

Steps to reproduce

Reproduces against a fresh npm create emdash@latest Node + SQLite scaffold.

npm create emdash@latest emdash-bug -- --template starter --platform node --pm pnpm --yes
cd emdash-bug
pnpm install
pnpm dev --host 0.0.0.0 --port 4321 &

# Bypass setup and mint an admin PAT (dev-only endpoint)
TOKEN=$(curl -s -X POST 'http://localhost:4321/_emdash/api/setup/dev-bypass?token=1' \
  | python3 -c "import sys,json;print(json.load(sys.stdin)['data']['token'])")

# Create a draft post
POST_ID=$(curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  http://localhost:4321/_emdash/api/content/posts \
  -d '{
    "slug":"sched-repro",
    "status":"draft",
    "data":{
      "title":"Scheduled repro",
      "content":[{"_type":"block","style":"normal","_key":"p1","children":[{"_type":"span","_key":"s1","text":"body"}]}]
    }
  }' | python3 -c "import sys,json;print(json.load(sys.stdin)['data']['item']['id'])")

# Schedule it 30 seconds in the future
FUTURE=$(date -u -d "+30 seconds" +%Y-%m-%dT%H:%M:%S.000Z)
curl -s -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  "http://localhost:4321/_emdash/api/content/posts/$POST_ID/schedule" \
  -d "{\"scheduledAt\":\"$FUTURE\"}" > /dev/null

# Wait until well past the scheduled time
sleep 60

# Fetch the post anonymously — expect 200, observe 302 -> /404
curl -s -o /dev/null -w "Public URL: %{http_code}\n" "http://localhost:4321/posts/sched-repro"

# Inspect the loader's WHERE clause directly against the SQLite file
node -e "
const Database = require('better-sqlite3');
const db = new Database('data.db', { readonly: true });
console.table(db.prepare(\`
  SELECT slug, status, scheduled_at,
         CURRENT_TIMESTAMP                        AS sqlite_now,
         scheduled_at <= CURRENT_TIMESTAMP        AS broken_compare,
         datetime(scheduled_at) <= datetime('now') AS fixed_compare
  FROM ec_posts WHERE status = 'scheduled'
\`).all());
"

Expected last command output: broken_compare = 0, fixed_compare = 1, with scheduled_at clearly older than sqlite_now.

Environment

• emdash version: 0.9.0 (verified at commit a945a88, pushed 2026-05-04)
• Node.js version: 22.22.2
• Runtime: Node (@astrojs/node standalone) with better-sqlite3 12.8.0
• Database: SQLite (default file:./data.db from the starter template)
• OS: Linux (Ubuntu 26.04)
• Reproducible with the unmodified npm create emdash@latest starter template

D1 is SQLite-compatible and almost certainly affected by the same comparison; not independently verified in this report.

Logs / error output

There is no error output — that is part of the problem. The schedule API returns success and the row is written correctly:


{
  "data": {
    "item": {
      "id": "01KQTWPE648DQ34GEKQ37ADTSF",
      "slug": "sched-repro",
      "status": "scheduled",
      "scheduledAt": "2026-05-05T01:41:59.000Z",
      "publishedAt": null
    }
  }
}


The DB row past the scheduled time:

| slug         | status      | scheduled_at               | sqlite_now            | broken_compare | fixed_compare |
| ------------ | ----------- | -------------------------- | --------------------- | -------------- | ------------- |
| sched-repro  | scheduled   | 2026-05-05T01:41:59.000Z   | 2026-05-05 01:43:15   | 0              | 1             |

The public route response:


HTTP/1.1 302 Found
location: /404


The relevant code path:


packages/core/src/loader.ts:260-267



if (status === "published") {
  // Include both published content AND scheduled content past its publish time.
  // scheduled_at is stored as text (ISO 8601). On Postgres, we must cast it
  // to timestamptz for the comparison with CURRENT_TIMESTAMP to work.
  const scheduledAtExpr = isPostgres(db)
    ? sql`${sql.ref(scheduledAtField)}::timestamptz`
    : sql.ref(scheduledAtField);
  return sql`(${sql.ref(statusField)} = 'published' OR (${sql.ref(statusField)} = 'scheduled' AND ${scheduledAtExpr} <= ${currentTimestampValue(db)}))`;
}


The Postgres branch is correct; the SQLite branch needs an equivalent normalization (e.g. wrapping both operands in `datetime(...)`).