Investigation: How CMS draft-change items end up with null name/slug in PG storage

cms-pg-null-name-slug-investigation.md

CMS PG Storage: Apparent Null name/slug During Sitemap Generation

Summary

109,574 errors across 12,062 unique sites in 3 days (June 8-10, 2026), all from sitemap generation during publish:

[sitemap] Failed to get published items for draft changes items
AppError [BadDatabaseStateError]: Expected a non-nullable string
    at intoNonNull (CMSItem.ts:298)
    at CMSItem.get name (CMSItem.ts:116)
    at getItemsInCollections (sitemapUtils.ts:537)

A follow-up query over a wider window (June 1-16) found 53,652 errors across 7,884 distinct site/database pairs. The error count varies by window because the errors are transient and cluster around publish bursts.

Update: root cause identified

The likely root cause was not null f_name / f_slug data at rest. It was a missing projection in the draft-changes published-items query, fixed by #111007.

Before #111007, draftChangesPublishedItemsQuery.returnFields selected id, slug, and locale, but did not select name. The code later accessed publishedItem.name while building the sitemap item. In the PG storage layer, CMSItem.name reads this.#item.f_name, and intoNonNull throws the same BadDatabaseStateError for both null and undefined because it checks data == null.

So the Datadog stack trace looked like a null published item name, but the row object most likely had f_name === undefined because the query never selected it. This explains why:

• published PG tables were clean when queried directly;
• the error stopped after June 10, when #111007 merged;
• only PG-backed sites were affected;
• the error only appeared for sites hitting the draft-changes published-items lookup;
• high-frequency publishers with many databases were overrepresented because they had more chances to hit that sitemap path.

The investigation history below is preserved because it documents the hypotheses and evidence that led to this conclusion.

Key findings

1. Published data is clean at rest

Queried published PG tables two ways:

Admin queries using filter[name][exists]=false (translates to WHERE f_name IS NULL):

• fitconprod: current publication (June 16), May 28 publication -- 0 null rows
• vestingfinance: current publication (June 16) -- 0 null rows

Direct Aurora psql query on the enterprise cluster (WF_CMSENTAURORA1_READER_URI):

SELECT _id, id, l, p, f_name, f_slug
FROM "cms_singletenantpgenterprise_64b773371a8be924f500535e".p_64b773371a8be924f500537e
WHERE f_name IS NULL OR f_slug IS NULL;

0 rows. No null name/slug across ANY publication, past or present, in fitconprod's blog collection. The data has never been bad at rest in this table.

The bug is at read time, not at rest.

2. Errors are transient, not continuous

For fitconprod (top-affected site), errors only appeared June 9-10:

Day	Errors
June 9	561
June 10	532
June 11-16	0

The site publishes every ~2 minutes continuously, so the error condition appeared and resolved within a specific window. Publications from that error window have been cleaned up and can't be inspected.

3. All checked affected sites are multi-locale with massive publish volume

• fitconprod: en-US (primary), es-US (secondary), 467 published databases, publishes every ~2 min
• vestingfinance: nl (primary), en (secondary), 440 published databases, publishes every ~2 min

Draft-change items exist across both primary and secondary locales.

4. Error fires in the published-items lookup for draft changes

The error is in the second getAllIterable call in sitemapUtils.ts:getItemsInCollections, which queries the previous publication for published versions of draft-change items:

const liveEnv = {publication: dataStore.stagingMeta.currentlyLive};
const liveDB = await getCMSDataStore(dataStore._id, liveEnv).then(existsOrError());

const publishedItemsIterable = cmsQueryFactory(
  liveDB, liveEnv, query, EVENTUAL_READ_OPTS
).getAllIterable({collections});                  // <-- no waitForReaderSync

for await (const publishedItem of publishedItemsIterable) {
  if (...&& publishedItem.slug) {                 // <-- truthiness check, doesn't throw
    items.push({ name: publishedItem.name, ... }); // <-- .name getter throws via intoNonNull
  }
}

5. The stack trace always shows .name, but that doesn't rule out .slug

Every one of the 53,652 errors throws at CMSItem.get name. Zero show CMSItem.get slug. This initially looked like a signal that only f_name was null. It is not.

The code accesses .name (line 576) before .slug (line 577), and .name uses the strict intoNonNull getter that throws on null. The .slug check on line 573 (publishedItem.slug) is a truthiness guard that doesn't throw. So if both fields are null on the same row, we'd only ever see the .name throw. We cannot determine from these logs whether .slug is also null.

What we ruled out

1. Null data at rest -- published rows have valid name/slug (admin + psql query confirms)
2. Permanent data corruption -- errors are transient, not continuous
3. Staging rows missing name/slug -- all draft-change items have name/slug on staging
4. CMSItem.getFields() dropping defaults -- integration test passes
5. Publish round-trip losing fields -- integration test passes
6. Non-existent items in published table -- returns 0 results, no error
7. Localization duplication missing fields -- duplication copies all fields
8. Secondary-locale-only -- both primary and secondary locales affected

Invalidated hypothesis: missing waitForReaderSync on the published query

What looked like the smoking gun

PR #102413 (CMSEXT-1881) added waitForReaderSync: true to the first getAllIterable call (staging items, line 500) but not the second one (published items, line 566). The diff:

 // First query (staging items) -- GOT waitForReaderSync
-  ).getAllIterable(collections)) {
+  ).getAllIterable({collections, waitForReaderSync: true})) {

 // Second query (published items) -- DID NOT get waitForReaderSync
-      ).getAllIterable(collections);
+      ).getAllIterable({collections});

This looked like the root cause: the staging query waits for readers to catch up, but the published query doesn't. A clean one-line fix.

Why it doesn't hold up

waitForReaderSync operates at the Aurora cluster level, not per-table. The implementation (helpers/postgres/waitForReaderSync.ts):

1. Captures the writer's durable LSN via getWriterDurableLsn()
2. Polls aurora_replica_status() until all reader replicas' replay LSN catches up
3. Uses getDatabaseConnection(db) which resolves to the Aurora cluster for that database

Both getAllIterable calls operate on the same Aurora cluster (same dataStore._id). The first call reads from staging tables (s_*), the second from published tables (p_*), but they share the same underlying Aurora storage. Once the first call's waitForReaderSync confirms all readers are caught up to the writer's LSN, ALL tables in that cluster are synced, not just the staging ones.

Additionally, the published data being queried was written during a previous publication (potentially minutes ago). It should be well past any replication window by the time the current publish reads it.

Adding waitForReaderSync to the second query would be redundant. PR #111995 proposed this fix and has been withdrawn.

Remaining hypotheses

A. Write-path issue: data was actually null in PG during the error window

If the data was transiently null in PG (not just appearing null due to a read issue), it would show up in Snowflake's CMS replicated data, which snapshots at ingest time.

Next step: Query Snowflake for published items from the affected sites during the June 8-10 window where f_name IS NULL. If we find rows, the write path is the problem, not the read path.

B. Concurrent publish race condition

These sites publish every ~2 minutes. During sitemap generation:

• dataStore.stagingMeta.currentlyLive points to the previous publication
• But a concurrent publish may be overwriting the p_* table rows, temporarily creating rows with null fields
• PG's READ COMMITTED isolation level means each statement sees only committed data, but if the publish process commits in stages (insert row, then update fields), a concurrent read could see the intermediate state

C. Staging collections passed to published query

Line 566 passes staging collections to the live query:

).getAllIterable({collections});  // these are STAGING collections

If staging and published collection schemas differ (field additions, reordering), the query might not map f_name correctly for the published data.

D. Aurora buffer cache divergence

Aurora readers share the underlying storage volume with the writer, but maintain their own buffer caches. Under heavy write load (467 databases publishing every 2 min), a reader's buffer cache could serve a stale page that doesn't reflect a recently committed row update. This is different from replication lag (which waitForReaderSync checks) -- it's a cache coherence issue at the storage layer.

Open questions

• Snowflake: Do any published items from affected sites during June 8-10 have null f_name?
• Can we reproduce with a local PG cluster under concurrent publish load?
• What is the publish write path for p_* tables -- is it a single INSERT or does it INSERT then UPDATE?
• Are staging and published collection schemas always identical, or can they diverge?

Tooling gaps identified

• CONT-5932 -- Admin shows "collection not found" when publication doesn't exist (should say "publication not found")
• CONT-1317 -- Admin: view item slugs across live publications (would have made this investigation much faster)
• Need ability to query published PG tables with null-field filters in admin

Datadog evidence

DD query: "Expected a non-nullable string" service:webflow-batch-publication

Top affected sites (June 1-16 window):

Site ID	Database ID	Errors
64b773371a8be924f500533b	64b773371a8be924f500535e	675
65e877bc56ac4224ea0ba62c	65ec4a1d504f002b9b7672f4	618
63ff9bdfd376ec6456c210b5	63ff9be0d376ec51f8c210bd	447
66a59d3d4524a3df29729282	66a59d3d4524a3df29729296	321
657bc5bc0b611d972cc8007f	657fe4acc69448fefa209b79	292

Full export of all 7,884 affected site/database pairs available from Datadog Logs Explorer.

PG table reference

• Schema: cms_<storageLayerConfig>_<databaseId> (lowercase)
• Staging table: s_<collectionId>
• Published table: p_<collectionId> (rows filtered by p column = publication ID)

Related tickets

• WORK-2410 / CONT-1717 -- Sitemap generation crashes on null name/slug
• WORK-2412 / CONT-1723 -- PG CMSItem intoNonNull getters too strict
• CONT-1317 -- Admin: view item slugs across live publications
• CONT-5932 -- Admin: fix "collection not found" error for missing publications
• Original triage thread: #triage-work
• Investigation thread: #cms-pg-storage

Immediate fixes

Shipped

• PR #111007 -- Added name to draftChangesPublishedItemsQuery.returnFields, fixing the missing-projection cause of the BadDatabaseStateError in the draft-changes sitemap path.

Historical follow-ups

• PR #111877 -- Catch error in sitemap generation (hardening). Opened while the root cause was still unclear; likely not required for this incident after the #111007 projection fix.
• PR #111878 -- Make PG getters defensive (draft). Opened while the root cause was still unclear; the strict intoNonNull behavior correctly surfaced the missing-projection bug.

Withdrawn

• PR #111995 -- Add waitForReaderSync to published query. Withdrawn: the first getAllIterable already syncs the reader at the Aurora cluster level, making this fix redundant (see "Invalidated hypothesis" above).