nanaselect

Appearance

Drupal Dump Extraction Pipeline ​

The extraction pipeline (issue #1095, scripts/drupal-dump/extract.ts) turns a raw nanawall.com Drupal MySQL dump into a sanitized, neutral extract the bulk loader (#1097) consumes. It is the foundation of the Drupal DB-dump bulk ingestion capability: one offline pass produces the full catalog, instead of broadening the per-entity JSON:API sync one contract at a time.

The pipeline is offline and local. It parses the dump file directly — plain .sql or gzipped .sql.gz — and never starts a MySQL server, never connects to one, and never touches the network. The Worker is never involved. Because the dump is read as a text stream, excluded tables (users, webform, order data) never leave the stream, let alone reach a live database.

PII is excluded by whitelist, never by blacklist ​

A full production dump carries users, webform submissions, commerce/order rows, sessions, caches, and key_value/config (which can embed API keys). The extractor admits a table only when it is named on an explicit whitelist (src/lib/server/drupal-dump/whitelist.ts); anything not listed — including a new or unknown table in a future dump — is excluded by default. This is the capability's hard sanitization gate (#1094): omission fails safe.

The whitelist admits current-revision content tables only:

AdmittedWhat it holds
node, node_field_dataProject/product nodes
media, media_field_dataMedia entities (image entities, Wistia ids)
taxonomy_term_data, taxonomy_term_field_dataTaxonomy terms
file_managedFile records referenced by media
path_aliasURL aliases (the slug = id seam, #414)
node__field_*, media__field_*, taxonomy_term__field_*Per-entity field storage

Deliberately excluded: every *_revision* table (the app ingests current state, not history), key_value, config, and all user / webform / commerce / session / cache / log tables. The guardrail spec (src/lib/server/drupal-dump/extract.guardrail.spec.ts) fails the build if a non-whitelisted table ever reaches the extract sink, the row counts, or the manifest — AC-02 of #1095.

Running the pipeline ​

One command restores a dump to the neutral extract:

bash
npm run drupal:extract -- <dump.sql[.gz]> [--out <dir>] \
  [--drupal-version <ver>] [--dump-date <iso>]
ArgumentMeaning
<dump.sql[.gz]>The Drupal MySQL dump. .gz is decompressed in-stream.
--out <dir>Output directory (default extract/).
--drupal-version <ver>Drupal core version of the source site. Not derivable from the dump — pass it, or the extract records null and the run warns.
--dump-date <iso>Overrides the dump timestamp when the dump has no -- Dump completed on footer.

The run prints a summary — source metadata, whitelisted table/row counts, and the number of non-whitelisted tables skipped — and warns when the dump date or Drupal version could not be recorded, so a provenance gap is never silent.

Example:

extract complete in 12.4s → extract/
  source: nanawall.sql.gz (db nanawall, dumped 2026-07-01 03:14:15, Drupal 10.3)
  tables: 41 whitelisted, 58231 rows
  skipped: 96 non-whitelisted tables (names in manifest)

Extract format (version 1) ​

The extract format is versioned — EXTRACT_FORMAT_VERSION in src/lib/server/drupal-dump/extract.ts. The version is stamped into every manifest so the loader (#1097) can refuse an extract whose shape it does not understand. Bump it whenever the manifest fields, the JSONL row encoding, or the file layout change shape.

Output layout:

<out>/
├── manifest.json          versioned manifest + source provenance
└── tables/
    ├── node.jsonl         one JSON object per row, keys = column names
    ├── node__field_teaser.jsonl
    └── …                  one file per whitelisted table that produced rows

manifest.json ​

jsonc
{
	"formatVersion": 1,
	"source": {
		"file": "nanawall.sql.gz", // dump file name as given
		"database": "nanawall", // from the dump header, or null
		"mysqlVersion": "5.7.44-log", // MySQL server version, or null
		"dumpDate": "2026-07-01 03:14:15", // dump footer, or --dump-date, or null
		"drupalVersion": "10.3" // operator-supplied, or null
	},
	"tables": [
		// whitelisted tables that produced rows
		{ "name": "node", "rows": 1284 },
		{ "name": "node__field_teaser", "rows": 1284 }
	],
	"skippedTables": [
		// excluded tables — names only, no data
		"users",
		"users_field_data",
		"webform_submission",
		"…"
	]
}

The manifest records the source dump's date and Drupal version alongside the data (AC-03) — the #343 freshness view reports the dump's date, not the load time, so staleness stays honest after the JSON:API syncs are retired (#1099). skippedTables lists every excluded table by name so the sanitization is auditable and visible, never a silent omission.

tables/<table>.jsonl ​

One JSON Lines object per row. Keys are the dump's column names (from CREATE TABLE, or the INSERT's own column list when the dump used --complete-insert). Values are typed: MySQL NULL → JSON null, safe integers stay numeric, everything else (including hex/binary literals) is a string. JSONL streams row-by-row on both write and read, so the extractor and loader both run in bounded memory regardless of catalog size.

How the parser reads a dump ​

src/lib/server/drupal-dump/dump-parser.ts scans the dump line by line. mysqldump keeps every statement on a single line (string newlines are escaped as \n), so line-based scanning is sound. It emits three event kinds — table schemas from CREATE TABLE, data rows from INSERT, and dump-level metadata from the header/footer comments. Tables the whitelist rejects are skipped without parsing their values — only the table name is read — so excluded data is never materialized.

Schema-mapping contract (#1096) ​

The extract is neutral — raw Drupal field storage. Turning it into NanaSelect's stores is the job of the schema-mapping contract (src/lib/server/drupal-dump/mapping.ts), the only module allowed to know Drupal's internal table shapes. It declares, per target store, the source tables/columns and a pure transform, so the bulk loader (#1097) carries no ad-hoc SQL and no Drupal knowledge.

Drupal stores each field denormalized — its own <entity>__<field> table keyed by entity_id, with kind-specific columns (a scalar _value, a link's _uri/_title, a geofield's _lat/_lon, an entity reference's _target_id). The contract reassembles those into the columns the JSON:API syncs produce, so a dump-loaded row is indistinguishable from a sync-loaded one — same provenance shape (source='drupal', the store's *_synced_at, drupal_raw where it has a baseline).

Target storeSourceNotable mappings
drupal_projectsproject taxonomy term + taxonomy_term__field_*location (geofield → lat/lon), canonical URL (path_alias), architect/construction/design-build, gallery order, case-study node → uuid, publish state (taxonomy_term_field_data.status → published, #1744)
drupal_project_productsfield_products (multi-valued)each product term ref resolved tid → uuid + name
media_assetsmedia entity + media__field_* + file_managedreferenced file remote_url (no byte copy), dimensions, alt, field_wistia_id → Wistia embed
media_tagsfield_project, field_productsproject tag (known projects only), system tags via the system mapping
system_attribute_valuesproducts-vocabulary term fieldsdynamic — each drupal_attribute_mappings entry maps a Drupal field → attribute, typed via values.ts

Extensibility (#1096 AC-03). Exposing a Drupal product field the JSON:API contracts never surfaced is a mapping entry, not a new fetch/sync trio — add a drupal_attribute_mappings row (for attribute values) or a FieldSpec (for the projects/media stores) and the field flows through.

Publish state & the buyer-surface filter (#1744). The projects mapping carries Drupal's term publish flag — taxonomy_term_field_data.status → the drupal_projects.published column (1 published, 0 unpublished). The mapping fails safe: a term whose status can't be read maps to unpublished, and the column's schema default is 0, so a row whose publish state was never ingested stays hidden until a re-ingest carries the real state (backfill = re-run the dump load; the planner's content diff updates every changed row). The status column is part of the drift gate for the projects store — a dump without it aborts the load rather than silently ingesting hidden-state rows. Enforcement on the buyer surface lives in the matching layer: selectProjectViews (src/lib/server/project-matching.ts) excludes unpublished projects from every "Projects like yours" row — matched or backfill — silently (two-tier degradation policy). Admin surfaces read drupal_projects directly and continue to see unpublished rows.

Drift guard (#1096 AC-04). The mapping is versioned against the dump's Drupal major (MAPPING_DRUPAL_VERSION). Before any write, assertExtractSchema() validates the extract's table/column inventory against every registered mapping and aborts with a per-store report on a missing table or column — schema drift fails loudly instead of loading garbage. A sparse field (Wistia id, taxonomy references) is marked optional and excluded from the gate.

Bulk load into D1 (#1097) ​

The loader (src/lib/server/drupal-dump/loader.ts + loader-apply.ts) writes the neutral extract into D1 through the mapping contract. It splits into a pure planner and a thin apply:

  • planLoad() groups the extract by entity, builds the mapping-context resolver indexes, runs the schema-drift gate, and diffs each store against a snapshot of D1 → create / update / unchanged. It is vocabulary-aware (project-vocabulary terms → drupal_projects, product-vocabulary terms → attribute values) and idempotent: re-planning the same extract against prior writes yields an empty plan. The content diff — not a timestamp — decides a rewrite, so a re-run touches nothing and source_synced_at stays put.
  • applyLoad() executes the plan. The stores it owns (projects, the product graph, attribute values) are written in bounded atomic db.batch() groups (respecting D1's statement cap), so a partial failure never leaves a half-written batch and an idempotent re-run resumes. Media assets and tags go through the app's invariant helpers — createReferencedAsset and addMediaTag (existence-checked, #377) — never raw inserts; enum-typed attribute values refresh the managed catalog via upsertEnumValues (#340). Per-item failures are collected, never thrown, so the run always reports and the retry is a re-run.

Run it after an extract:

bash
npm run drupal:load -- <extract-dir> \
  [--remote] [--account-id <id>] [--yes] \
  [--project-vocabulary <v>] [--product-vocabulary <v>] [--files-base-url <url>]

By default the load targets the local dev D1 (getPlatformProxy). --remote (#1336) points the same plan at the prod nanaselect-db via the Cloudflare D1 REST API instead — a new execution backend behind the identical planner/applier, guarded by a confirm-the-target prompt (needs CLOUDFLARE_API_TOKEN). See the loading how-to.

Provenance (#343). Dump-loaded rows carry source = 'db-dump' on the stores that discriminate origin (media, attribute values) and are stamped with the dump's date (from the manifest), not the load time — so the freshness view attributes them to a distinguishable "Drupal DB dump" source and reports honest staleness. An admin manual value is never overwritten by a load (the same override guard the JSON:API sync honors).