Sf import

sf-import — ingestion skill (Phase 5 Wave 2)

Deterministic CLI (scripts/ingestion/sf_import.py), not a workflow_runner run shell. The stages below run in a single main(argv) pass: each DURUR is a print("ERROR: … (DURUR #N)", file=sys.stderr) plus a distinct non-zero exit code (no {run_id}.json state file, no workflow_runner.create_run/complete, no string-typed outputs). Raw CSV envelope discipline is mandatory: every SF batch is described in a single envelope-JSON dropped into inbox/sf/ before any per-sheet projection runs, so a projection bug never costs us the upstream filename → tier mapping.

The 6 SF-derived sheets are written via transaction.replace (an idempotent refresh — clears each sheet's data block, then re-lands the rows), so re-running a batch refreshes rather than duplicates. The tier-validation + envelope-inbox + per-sheet-write shape is shared with the other ingestion skills, but the write mechanism differs per skill (gsc-pull appends; dfs-pull is staging-only). Deviate only with an ADR.

Inputs (frontmatter contract)

Name	Type	Default	Notes
project_slug	string	—	Required. Resolves projects/{slug}/master.xlsx.
sf_export_path	string	—	Required. Path to a SF export dir; raw/ subfolder enforced.

workspace_root is resolved via PSEO_WORKSPACE_ROOT env or explicit test override (mirrors workflow_runner / events_writer).

Outputs (artifacts produced)

• projects/{slug}/master.xlsx#crawl_sitemap — sitemap-vs-crawl summary rows.
• projects/{slug}/master.xlsx#redirect_404 — 404 + redirect chain rows.
• projects/{slug}/master.xlsx#schema — structured-data audit rows.
• projects/{slug}/master.xlsx#on_page_audit — title/meta/h1 coverage rows.
• projects/{slug}/master.xlsx#tech_seo — tech issue summary rows.
• projects/{slug}/master.xlsx#robots_txt — robots/directive issue rows.
• projects/{slug}/_state/events.jsonl — provenance entries (source.kind=sf_csv).
• projects/{slug}/inbox/sf/{date}-{slug}.json — envelope JSON listing every CSV (filename, tier, row_count, file_hash) for drift recovery.

CLI protocol (6 stages, one main() pass)

Invocation (manager, or sf-crawl-orchestrator subprocess):

python3 -m scripts.ingestion.sf_import \
    --project <slug> \
    --sf-export-path projects/<slug>/sf-exports/<date>/ \
    [--workspace-root <root>]  [--dry-run]

--workspace-root defaults to $PSEO_WORKSPACE_ROOT. There are no other flags — in particular --source-run-id does NOT exist (the source_run_id frontmatter input carries provenance correlation, not a CLI flag).

Stage A — workspace + path resolution (DURUR #5)

main(argv) resolves --workspace-root (or $PSEO_WORKSPACE_ROOT). Unset → print("ERROR: … required (DURUR #5)", file=sys.stderr) and return 5. A relative --sf-export-path is joined onto workspace_root.

Stage B — validate_export_path (DURUR #1)

raw_dir = sf_root / "raw" if (sf_root / "raw").is_dir() else sf_root
if not raw_dir.is_dir():
    print(f"ERROR: raw_dir not found at {sf_root} or {sf_root}/raw (DURUR #1)",
          file=sys.stderr)
    return 1

Stage C — validate_tier1 (sf-required-reports.schema Tier 1 14/14, DURUR #2)

match_tiers lives in scripts.ingestion.sf_import itself (there is no separate sf_validate module). It walks raw/, normalizes filenames (lower, strip de_ / v_ / p_ / bi_ prefixes, strip (N) / -copy / _old suffixes, fold Turkish ı→i), and matches each canonical_name against schemas/sf-required-reports.schema.json definitions.canonicalName. Tier 1 missing → RED FAIL (exit 2). Tier 2 missing → AMBER warn, NOT a fail (search_console_all is the canonical Tier 2 exemption surfaced in pilot validation).

matched, missing_t1, missing_t2 = match_tiers(raw_dir)   # defined in this module
if missing_t1:
    print(f"ERROR: Tier 1 missing: {sorted(missing_t1)} (DURUR #2)", file=sys.stderr)
    return 2
amber: list[str] = []
if missing_t2:
    amber.append(f"Tier 2 missing (AMBER): {sorted(missing_t2)}")

Stage D — envelope_inbox (raw → drift-recoverable JSON, DURUR #6)

First confirm the write target exists; a missing master.xlsx → DURUR #6 (exit 6 — init-project must have run). Then the build_envelope helper writes a single envelope JSON to inbox/sf/{date}-{slug}.json BEFORE any projection runs — for every matched CSV it captures sha256, row_count, filename_original + filename_normalized + tier. The envelope is the durable witness that a given SF batch was observed even if the Excel write fails downstream.

master_xlsx = workspace_root / "projects" / args.project / "master.xlsx"
if not master_xlsx.exists():
    print(f"ERROR: master.xlsx missing at {master_xlsx} (DURUR #6)", file=sys.stderr)
    return 6
inbox_path = build_envelope(args.project, sf_root, raw_dir, matched, amber, workspace_root)

Envelope shape (written by build_envelope):

{
  "_meta": { "captured_at": "…Z", "tool": "screaming_frog",
             "project_slug": "<slug>", "sf_export_path": "…",
             "raw_dir": "…", "amber_warnings": ["Tier 2 missing (AMBER): …"] },
  "files": [
    { "canonical_name": "internal_all", "tier": "required",
      "filename_original": "internal_all.csv", "filename_normalized": "internal_all.csv",
      "file_hash": "sha256:…", "row_count": 192 }
  ]
}

Stage E — sitemap_xcheck — Phase-X DEFERRED (not wired)

Status: deferred capability, not part of the shipped contract. The intended cross-check — compare the SF-discovered URL list against the GSC submitted sitemaps and log discrepancies into crawl_sitemap as a diagnostic — is NOT implemented. main() goes straight from the envelope (Stage D) to the projection write (Stage F); it never calls mcp__gsc__list_sitemaps, and there is no sf_sitemap_xcheck module. The GSC sitemap tools are therefore optional in the frontmatter, and DURUR #4 is a deferred-condition placeholder (see DURUR conditions below). When this lands (a future Wave) it belongs at the manager/caller layer — sf-import's core stays GSC-free, disk-CSV only.

Stage F — write_excel (per-sheet, idempotent replace)

Six transaction.replace calls — one per sheet — over the projections returned by sf_projection.project_all(raw_dir). replace (NOT append) clears each sheet's data block then re-lands the rows, so re-importing the same batch refreshes rather than duplicates (idempotent). Each call goes through the single approved write path with backup, lock, schema validation, and post-write tool_computed provenance emission. The 6 sheets and their CSV → row projections (inline mapping; the formal staging-to-excel-map.json arrives in Phase 6):

Logical sheet	Source CSV(s)	Row shape (master-excel.schema)
crawl_sitemap	internal_all, sitemaps_all, crawl_depth	category / metric / value / status / action
redirect_404	response_codes_all, redirect_chains	url / inlinks / action / target_url / status
schema	structured_data_all	schema_type / status / location / scope / remaining_work
on_page_audit	page_titles_all, meta_description_all, h1_all	url / target_query / impressions_30d / clicks_30d / in_title /...
tech_seo	issues_overview_report, directives_all	issue_category / detail / affected_urls / impact / resolution / ...
robots_txt	directives_all, indexability	id / level / issue / detail / resolution

In sf_import.py this is the project_and_write helper:

from scripts.excel import transaction
from scripts.ingestion import sf_projection
projections = sf_projection.project_all(raw_dir)
counts: dict[str, int] = {}
for sheet, rows in projections.items():
    result = transaction.replace(
        master_xlsx, sheet, rows, project_slug, writer="sf-import",
    )
    counts[sheet] = result.rows_affected

transaction.replace itself emits a tool_computed provenance event per write. Stage F then supplements that with a single sf_csv source provenance record (below) so the data lineage is sf_csv → tool_computed. Idempotency: a re-run with the same CSVs replaces the prior rows in place — no duplicate rows accumulate across re-imports.

Stage F (cont.) — sf_csv source provenance

A single sf_csv source-provenance event records the lineage. source_folder is the raw dir relative to the workspace root (via _rel_to_workspace, which falls back to an absolute string for an out-of-tree path rather than crashing); row_count and rows_written are the total rows projected across the 6 sheets. The run_id is auto-allocated race-free under the append flock (P1-11).

from scripts.state import events_writer
total_rows = sum(counts.values())
events_writer.append_provenance(
    project_id=project_slug,
    source={
        "kind": "sf_csv",
        "source_folder": _rel_to_workspace(Path(raw_dir), workspace_root),
        "row_count": total_rows,
    },
    operation="ingest",
    rows_written=total_rows,
    workspace_root=workspace_root,
)

Stage G — completion (stdout summary + exit 0)

There is no workflow_runner.complete and no string-typed outputs dict — main() prints a human-readable summary to stdout and returns 0 on success. --dry-run stops after the envelope (Stage D), before any Excel write.

print(f"OK: matched {len(matched)} files (Tier1 {t1}/14, Tier2 {t2}/10)")
print(f"Envelope: {inbox_path}")
for w in amber:
    print(f"WARN: {w}")
if args.dry_run:
    print("DRY-RUN: skipping Excel projection.")
    return 0
counts = project_and_write(raw_dir, master_xlsx, args.project,
                           workspace_root=workspace_root)
for sheet, n in counts.items():
    print(f"OK: {sheet} ← {n} rows")
return 0

Exit codes — the DURUR signalling channel (there is no run-state file):

Outcome	Exit
Success / --dry-run	0
DURUR #1 — no raw/ subfolder	1
DURUR #2 — Tier 1 < 14/14	2
DURUR #3 — RowSchemaError during projection	non-zero (uncaught traceback)
DURUR #5 — no --workspace-root / $PSEO_WORKSPACE_ROOT	5
DURUR #6 — no master.xlsx	6

Filename normalization (Tier 1 matching)

The pilot project export ships every Tier 1 file twice: once with the canonical name (internal_all.csv) and once with a de_ prefix (German locale shadow de_internal_all.csv). Plus a third Turkish-i variant (de_ınternal_all.csv). Normalization rules, applied in order:

1. Lowercase the basename.
2. Drop extension (.csv).
3. Strip leading locale shadow prefix de_ (defensive — pilot artifact).
4. Strip leading export-mode prefix v_ / p_ / bi_ (SF visualization / page / bulk-import mode).
5. Fold Turkish dotless ı → ASCII i (Turkish-locale filename collision).
6. Drop (N) and -copy / _old version suffixes.

The first matching canonical_name (per sf-required-reports.schema) wins. Ambiguous matches (two canonical names matching the same file) abort the import — no silent picks.

Tier policy

Tier	Missing →	Pilot exemption
Required	RED FAIL	none (all 14 enforced)
Recommended	AMBER	search_console_all (pilot ships 9/10, OK)
Optional	SILENT	full set silent

DURUR conditions (6)

Stop and flag the manager — do not patch, do not fall back. Each is a print(… DURUR #N) + a distinct exit code (DURUR #3 surfaces as an uncaught RowSchemaError traceback).

1. sf_export_path missing or no readable raw/ subfolder. → exit 1
2. Tier 1 < 14/14 — RED FAIL, do not proceed to projection. → exit 2
3. transaction.replace raises RowSchemaError for any sheet — the exception is NOT swallowed, so the batch aborts with a non-zero (traceback) exit; replace schema-validates BEFORE clearing, so a rejected sheet is left untouched.
4. (Phase-X deferred, not wired) — mcp__gsc__list_sitemaps sitemap cross-check is not implemented in sf_import.py; the GSC tools are optional. When the cross-check lands, a list_sitemaps failure becomes the DURUR #4 hard stop. Today this condition never fires.
5. PSEO_WORKSPACE_ROOT env unset and no --workspace-root arg. → exit 5
6. master.xlsx missing under projects/{slug}/ — init-project must have run first. → exit 6

Cross-references

• Schemas: schemas/sf-required-reports.schema.json (Tier 1 14, Tier 2 10, canonicalName enum), schemas/sf-export-mapping.schema.json (filename_alias normalization), schemas/master-excel.schema.json (6 sheet column structures + statusEnum + severityEnum), schemas/events.schema.json (source.kind=sf_csv), schemas/gsc-tool-mapping.schema.json (gsc__list_sitemaps enum — used by the deferred Stage E cross-check, not the shipped core), schemas/skill-frontmatter.schema.json (this frontmatter).
• Cross-modules (IMPORT-only): scripts/excel/transaction.py (replace), scripts/ingestion/sf_projection.py (project_all), scripts/state/events_writer.py (append_provenance). NOT workflow_runner — sf-import is a plain CLI, not a run shell.
• Tests: tests/skills/test_sf_import.py (10 cases: 6-criterion acceptance gate incl. live SF batch + source_run_id handoff pair + frontmatter validation + required-tools parity).
• Pilot data path: projects/{slug}/sf-exports/{export_date}/raw/ (Tier 1 14/14 + Tier 2 9/10 with search_console_all AMBER).

Discipline checklist

• TODO/fallback YASAK — every DURUR raises, none silently downgrade.
• Schema-first — frontmatter validates against schemas/skill-frontmatter.schema.json Draft 7.
• Plugin-agnostik — no slug literals; project_slug flows through.
• ADR-013: Use when/Also use when/Do not use when are STRING content inside description, not separate fields.
• Cross-module IMPORT discipline — transaction / sf_projection / events_writer are imported, never modified from this skill.
• F1: write target is master.xlsx (lowercase, schema-shaped). The legacy demo-dental_MASTER.xlsx (Turkish-emoji) is DOKUNULMAZ.
• Idempotent writes — transaction.replace refreshes each sheet's data block (re-import does not duplicate); DURUR signalling is exit-code based (no workflow_runner run shell, no string-typed outputs).