Admin UI silently buckets published content into Trash when ec_{collection} table schema lacks EmDash's expected columns

[contestra]

Description

On emdash@0.5.0 + @emdash-cms/cloudflare@0.5.0, the admin UI at /_emdash/admin/content/{collection} silently buckets 100% of a collection's published entries into the "Trash" tab when the backing ec_{collection} table was created with a minimal schema missing columns EmDash's list-view filter expects. Source-code review of emdash@0.6.0 shows the same findMany() filter (WHERE deleted_at IS NULL) is unchanged; the bug should reproduce identically on 0.6.0 — happy to confirm if a maintainer wants explicit verification.

Symptoms:

• "All" tab shows zero entries.
• "Trash" tab shows all entries, labeled "Deleted on: Invalid Date".
• Published content at status='published' is completely invisible to editors.
• No error is shown to the operator; the admin endpoint returns HTTP 200.
• delivery_content (public bot-facing) is unaffected — only the admin view is broken.

Expected: rows with status='published' appear in the "All" tab as normal editable entries.
Actual: rows bucket into "Trash" with invalid dates; collection is unusable for editing.

Steps to reproduce

1. Start with a working EmDash install on 0.5.0+ (we reproduced on both 0.5.0 and, per Issue SQLite Database Corrupt Error on new site #649 pattern, 0.6.0 is also susceptible to adjacent silent-fallback bugs).
2. Register a collection via direct SQL on _emdash_collections:

   INSERT INTO _emdash_collections (
     id, slug, label, label_singular, supports, has_seo, created_at, updated_at
   ) VALUES (
     'coll_repro_test',
     'repro_test',
     'Repro Test',
     'Repro Test Entry',
     '["drafts","revisions","search","seo"]',
     1,
     strftime('%Y-%m-%dT%H:%M:%fZ','now'),
     strftime('%Y-%m-%dT%H:%M:%fZ','now')
   );

3. Create the backing table with a MINIMAL schema — missing deleted_at, live_revision_id, draft_revision_id, version, author_id, primary_byline_id, scheduled_at, translation_group, featured_image:

   CREATE TABLE ec_repro_test (
     id TEXT PRIMARY KEY,
     slug TEXT NOT NULL,
     title TEXT NOT NULL,
     content JSON,
     excerpt TEXT,
     status TEXT NOT NULL,
     locale TEXT NOT NULL DEFAULT 'en',
     published_at TEXT,
     created_at TEXT NOT NULL,
     updated_at TEXT NOT NULL
   );

4. Insert a single row with status = 'published':

   INSERT INTO ec_repro_test (id, slug, title, content, excerpt, status, locale, published_at, created_at, updated_at)
   VALUES (
     '01KPQZA8J1E206F60QSNFRX3Y2',
     'repro-minimal-schema',
     'Minimal schema repro entry',
     '[]',
     'Repro',
     'published',
     'en',
     strftime('%Y-%m-%dT%H:%M:%fZ','now'),
     strftime('%Y-%m-%dT%H:%M:%fZ','now'),
     strftime('%Y-%m-%dT%H:%M:%fZ','now')
   );

5. Log into the admin UI and navigate to /_emdash/admin/content/repro_test.

Expected: the row appears in the "All" tab as a published entry, editable.
Actual: the row appears in the "Trash" tab with Deleted on: Invalid Date; "All" tab is empty.

Environment

• EmDash: affects 0.6.0 (latest) AND 0.5.0. Verified directly on emdash@0.5.0. 0.6.0 not directly tested (we haven't bumped) but findMany() in the relevant content module is unchanged between 0.5.0 and 0.6.0 source — repro should be identical. Happy to retest on 0.6.0 if a maintainer requests verification.
• @emdash-cms/cloudflare@0.5.0 (verified) / 0.6.0 (inferred via same logic)
• Astro: 6.1.5
• Runtime: Cloudflare Workers + D1 (SQLite)
• Node: 22.x (for build)

Root cause analysis

The list-view query in packages/emdash/.../content-BsBoyj8G.mjs (source @emdash-cms/core/src/content.ts ContentRepository.findMany) runs:

let query = this.db.selectFrom(tableName).selectAll().where("deleted_at", "is", null);

Against a table without a deleted_at column, SQLite raises:

no such column: deleted_at at offset 43: SQLITE_ERROR [code: 7500]

Verified with a direct D1 query against our installation:

wrangler d1 execute <tenant>-cms --remote \
  --command "SELECT COUNT(*) FROM ec_repro_test WHERE deleted_at IS NULL"
→ no such column: deleted_at at offset 43: SQLITE_ERROR [code: 7500]

The admin endpoint (at /_emdash/api/content/{collection} handled by api/content/[collection]/index.ts) catches this error silently and returns an empty payload for the All view, while the Trash view returns the full row set with each row's deleted_at resolved to NULL (new Date(null) in the React admin rendering to "Invalid Date"). The end-user sees all their content in Trash with invalid deletion dates.

Why this is a real-world problem (not just a weird edge case)

EmDash's migration tooling and admin UI assume all ec_{collection} tables are created via EmDash's own content-type migration path, which guarantees the full column set. However, teams who provision content via:

• Custom migration scripts (e.g. a one-time import from a prior CMS)
• Data-engineering pipelines (Airflow/dbt-style ingest into D1)
• Direct wrangler d1 execute CREATE TABLE during bespoke tenant setup
• Older versions of EmDash where the expected column set was smaller (schema drift on a long-lived install)

…can end up with minimal-schema tables that silently break the admin UI while leaving the public bot-facing surface intact. The diagnostic cost is high because the admin UI shows 200 OK, Cloudflare logs show no errors, and the only tell is the subtle "Invalid Date" tooltip in the Trash view.

Impact

In our multi-tenant deployment on EmDash 0.5.0:

• 3 production tenants affected, with 7 / 25 / 15 rows respectively trapped in the Trash bucket (47 entries total across the fleet). Every entry was status='published' and had been correctly populated by our migration scripts — none were actually deleted.
• Editor logins into the affected collections showed zero items in the "All" tab with all content visible only in the "Trash" tab labeled "Deleted: Invalid Date". For an editor unaware of the bug this is indistinguishable from "my content is gone."
• Diagnosis required a multi-hour session of schema probing, FTS health checks, admin-UI vs database cross-reference, EmDash source-code inspection to identify the deleted_at filter path, and design of a mirror-to-proper-schema workaround. The silent fallback behavior was the single biggest cost — there was no signal pointing at the schema mismatch root cause.

Suggested fix (any one helps; in priority order)

1. Surface the error instead of silently bucketing to Trash. When the admin endpoint catches a "no such column" error on the list query, return a 500 with a diagnostic message (e.g. "collection table ec_repro_test is missing required columns: deleted_at, live_revision_id, ...") instead of falling through to the Trash view. Operator sees the real problem immediately.

2. Schema validation at collection-registry time. Add a sanity-check on _emdash_collections INSERT/UPDATE that verifies the backing ec_{slug} table exists AND has the full expected column set. Refuse (or warn loudly) if the table is missing or incomplete. This would prevent the silent-bucketing state from ever manifesting for operators using the registry path.

3. Document the required schema. Publish the canonical column list for ec_{collection} tables in operator docs — with a runnable reference CREATE TABLE statement. Today this information lives in packages/core/src/content/registry.ts createContentTable() and isn't surfaced anywhere operators read.

4. Graceful degradation. In ContentRepository.findMany, detect missing columns before running the query (via an introspection cache) and omit the deleted_at IS NULL filter when the column doesn't exist. Rows then correctly surface in the "All" tab. This is the most complex fix but is the kindest to operators who've already ended up in the broken state.

Our workaround (for anyone else hitting this before a fix lands)

Mirror the affected collection's content into a fresh ec_posts table (or any properly-schema'd backing table) via a 3-step FK-ordered migration:

-- Step 1: INSERT into proper-schema table with live_revision_id=NULL
INSERT INTO ec_posts (
  id, slug, status, author_id, primary_byline_id,
  created_at, updated_at, published_at, scheduled_at, deleted_at,
  version, live_revision_id, draft_revision_id,
  locale, translation_group, title, featured_image, content, excerpt
)
SELECT
  id, slug, status, NULL, NULL,
  created_at, updated_at, published_at, NULL, NULL,
  1, NULL, NULL,
  locale, translation_group_id, title, NULL, content, excerpt
FROM ec_affected;

-- Step 2: Create revisions (required for EmDash's publish semantics)
INSERT INTO revisions (id, collection, entry_id, data, author_id, created_at)
SELECT
  'rev_posts_' || id,
  'posts',
  id,
  json_object('title', title, 'content', json(content), 'excerpt', excerpt),
  NULL,
  strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
FROM ec_posts;

-- Step 3: Point ec_posts.live_revision_id at the newly-created revision
UPDATE ec_posts SET live_revision_id = 'rev_posts_' || id WHERE live_revision_id IS NULL;

Then update the _emdash_collections row for the affected collection to point at the new table (or relabel the posts slug entry to a user-meaningful name and DELETE the broken answers/custom collection entry). Bot-facing delivery_content is unaffected by the mirror.

Related

• Issue FTS5 integrity-check corrupts D1 shadow tables — publish fails with SQLITE_CORRUPT_VTAB on all collections #252 / PR fix(core): remove FTS5 integrity-check to prevent D1 shadow table corruption #313 — prior FTS5 corruption class. Similar "silent fallback masks real problem" pattern. PR fix(core): remove FTS5 integrity-check to prevent D1 shadow table corruption #313 fixed one cause; Issue SQLite Database Corrupt Error on new site #649 shows corruption still reproduces via other paths on 0.6.0.
• No existing issue matches this bug (verified 2026-04-21 via gh search issues against emdash-cms/emdash). Net-new report.

Happy to provide additional D1 dumps, schema diffs, or admin UI screenshots on request.