Skip to content
AnglinAI Docs

Step 1 — Import Mapping / Preview UI + Multi-Tab (Implementation Spec)

Hand-off-ready spec for Step 1 of the multi-chart plan (docs/admin/org-chart-multi-chart-design.md, issue #1549). Builds directly on Step 0 (XLSX ingestion, #1551 / org-chart-xlsx-ingestion-spec.md) — do not start Step 1 until Step 0’s parseOrgChartSpreadsheet + shared rowsToPeople core are merged.

All line references verified against main as of 2026-07-13.

Goal

Replace the silent one-shot structured import with an interactive analyze → map → commit flow:

  • Show the user the detected column→field mapping and let them correct it (today’s fuzzy matching guesses silently).
  • Handle multi-sheet workbooks: combine sheets into one org (tab → department/group), create one chart per sheet, or exclude sheets — instead of Step 0’s “first sheet only, rest ignored.”
  • Replace the “Check console for details” error UX with an inline, accessible error/warning summary.

Scope

In scope

  1. Two new server endpoints (analyze + commit) plus a discard endpoint, registered in both entry points.
  2. A short-TTL import session: original file stashed in R2 (authed), analysis metadata in Redis; cleanup job for stale sessions.
  3. Reuse Step 0’s rowsToPeople core with a user-supplied mapping override (confirmed mapping wins over the fuzzy guess).
  4. Multi-tab strategy: single (tab→group column) | per-tab | per-sheet include/exclude, with a schema-similarity heuristic default.
  5. New client import wizard steps (column mapping table + preview grid + tab-strategy screen), WCAG 2.1 AA, replacing the silent path for CSV/XLSX uploads in the UI.
  6. Accessible error/warning summary (kills “check console”).

Out of scope (later steps)

  • The org/view split, “Create view from here”, forking, transclusion (Steps 2–4).
  • ID-based hierarchy (Step 2 candidate).
  • Changing the AI-vision (image/PDF) path — it keeps its existing async extract flow.
  • The legacy one-shot POST /api/orgcharts structured import stays for the developer REST API and backward compat; the interactive flow is layered alongside it, not a replacement.

Current flow (verified)

  • Client wizard apps/org-chart/src/app/wizard/page.tsx already has a step indicator (:243) and copy mentioning “spreadsheet (CSV/Excel)” / “.xlsx” (:298,:464) — UI copy is ahead of the backend. It calls api.createOrgChart(name, file, onProgress) (lib/api.ts:255, XHR for progress), which one-shot POSTs to /api/orgcharts. CSV skips extraction → router.push('/preview?id=...') (wizard/page.tsx:160-174).
  • Server POST /api/orgcharts (workers/api/src/routes/org-chart/orgcharts.ts:29) parses inline and inserts (manager-name resolution :84-232).
  • Routes mount in both workers/api/src/index.ts:308-315 and index-aws.ts:158-167 — new routes must be added to both (dual-entry gotcha) or they 404 in prod.
  • R2 writes via storage.objects.put(key, body, ...) with R2_PATHS (workers/api/src/utils/ir-storage.ts:33). Redis/KV helpers already used across org-chart/* routes. Phase 1 shipped a temp-row cleanup pattern (reuse it for import sessions).
  • Column matching to reuse: STANDARD_FIELDS, FIELD_MAP, fuzzyMatchColumn, normalizePersonRow in workers/api/src/utils/csv-parser.ts.

Architecture: two-phase import

A mapping UI needs the parsed structure before committing, so split the interactive path in two:

POST /api/orgcharts/import/analyze   (multipart: file, name?)
  → stash original file in R2: users/{userId}/imports/{sessionId}/original  (TTL)
  → parse workbook (Step 0 parser, all sheets)
  → per sheet: detect columns + suggested field + confidence + sample values
  → compute suggested tab strategy (schema-similarity heuristic)
  → cache lightweight analysis in Redis: orgchart:import:{sessionId} (TTL 30m)
  → return { importSessionId, sheets[], suggestedStrategy, warnings }   (NO chart created)


POST /api/orgcharts/import/commit
  body: { importSessionId, name, strategy, sheets: [{ name, included, columnMapping, groupValue? }] }
  → re-read original file from R2, re-parse with the CONFIRMED mapping (override fuzzy guess)
  → strategy 'single'  → 1 org_charts row; tab name written into the chosen group/department field
    strategy 'per-tab' → N org_charts rows (one per included sheet)
  → reuse existing insert + manager-resolution path (factor out of orgcharts.ts)
  → delete the R2 stash + Redis session
  → return { orgCharts: [{ id, name, peopleCount, warnings }] }


DELETE /api/orgcharts/import/{sessionId}   → discard stash + session

Why R2 stash + re-parse (not return rows to client): a 10MB xlsx can expand to tens of MB of JSON — too heavy to round-trip through the browser and back. Stashing the original bytes keeps one source of truth and re-parses deterministically on commit. Redis holds only the compact analysis (headers, guesses, a few sample rows), not the full grid.

Response shapes

// analyze
{
  importSessionId: string,
  fileName: string,
  sheets: Array<{
    name: string,
    rowCount: number,            // data rows (excl. header)
    columns: Array<{
      sourceHeader: string,
      sourceIndex: number,
      suggestedField: string | 'ignore',   // canonical field or ignore
      confidence: 'high' | 'low' | 'none',  // from fuzzyMatchColumn distance
      sampleValues: string[],               // up to 3
    }>,
    included: boolean,           // default include if it has a title-mappable column + rows
    titleMapped: boolean,        // required-field precheck
  }>,
  suggestedStrategy: 'single' | 'per-tab',
  schemaCompatible: boolean,     // do all included sheets share a header schema?
  warnings: string[],
}

columnMapping in commit is Record<sourceIndex, canonicalField | 'custom:<Label>' | 'ignore'>. Unmapped columns default to ignore; a column can be mapped to a custom field (custom:<Label>) — the data model already supports unlimited custom fields.

Multi-tab strategy & heuristic

  • Schema-similarity heuristic: normalize each sheet’s header set (via fuzzyMatchColumn); if all included sheets map to the same canonical field set (Jaccard ≥ ~0.7), schemaCompatible = true → default single (tab → department or a user-chosen group field). Otherwise → default per-tab.
  • Always confirm; never silently create N charts. The tab-strategy screen states the default and the reason (“These 4 sheets share the same columns — importing as one org with a Department column”).
  • single requires unifying sheets to one canonical schema; if a sheet’s mapping diverges, the UI flags it before commit.
  • Cross-sheet manager references: in single, manager-name resolution runs across the merged people set (existing two-pass logic). In per-tab, resolution is per-sheet; unresolved managers surface as warnings (existing behavior).

Security & limits

  • Auth required on all three endpoints (owner only); rate-limit analyze (reuse kv-rate-limit).
  • Enforce MAX_FILE_SIZE (10MB) on analyze before stashing.
  • R2 import stash is per-user keyed + short TTL; commit and cleanup both delete it. Never public.
  • Formulas never evaluated (Step 0 parser already sets cellFormula: false); values only.
  • Cap sheet count and total rows processed; if exceeded, return an actionable error (per the actionable-error rule — state the limit).

Client (apps/org-chart/src/app/wizard/page.tsx + new components)

Extend the existing wizard. New states after file drop for a CSV/XLSX:

  1. Analyzing — call analyze; show a progress indicator (required >2s).
  2. Tab strategy (only if sheets.length > 1) — radio group: One org (tab → Department) vs One chart per sheet; per-sheet include checkboxes; default preselected from suggestedStrategy with the reason shown.
  3. Column mapping — a table: each source column → a labeled <select> of target fields, pre-filled with suggestedField; low/none-confidence rows visually flagged; a preview grid of the first ~5 mapped rows; a required-field check (“Title is mapped ✓”). Unmapped → Ignore or Import as custom field.
  4. Confirm → call commit; on success router.push to /preview?id= (single) or the dashboard (per-tab, show all created charts).

Accessibility (WCAG 2.1 AA — this is the moat):

  • Every mapping <select> has a programmatic <label> naming its source column.
  • An error/warning summary region (role="alert"/focus-managed) lists row-level issues — replaces editor/page.tsx:280’s “Check console for details.”
  • Full keyboard operability; visible focus; 44×44 targets; step changes announced.
  • Confidence flags are not color-only (icon/text too).
  • Preview grid is a real, headed <table>.

Server files

  • New workers/api/src/routes/org-chart/import.ts — analyze, commit, DELETE :sessionId. Mount at /api/orgcharts/import in both index.ts and index-aws.ts.
  • Edit workers/api/src/utils/csv-parser.ts — extend rowsToPeople(headerRow, dataRows, opts?) (from Step 0) to accept an explicit mappingOverride: Record<number, string> that wins over fuzzyMatchColumn; support custom:<Label> targets.
  • Edit orgcharts.ts — factor the chart-insert + manager-resolution block (:66-232) into an exported createChartFromPeople(supabase, { userId, name, people, warnings }) reused by import.ts and the legacy path.
  • New import-session helpers: R2 stash put/get/delete under R2_PATHS.orgChartImport(userId, sessionId) (add to constants.ts); Redis get/set/del for the analysis payload.
  • New/extend cleanup job to purge import sessions older than the TTL (reuse the Phase 1 temp-cleanup mechanism).

Tests

  • Route import.test.ts (per route-test-coverage rule): analyze returns sheets + suggestions without creating a chart; commit single writes tab→department and creates 1 chart; commit per-tab creates N; mapping override beats the fuzzy guess; custom-field mapping persists; oversized file → 400; expired/invalid session → 400; discard deletes the stash.
  • Unit: schema-similarity heuristic (compatible vs incompatible sheets); rowsToPeople with override + custom fields; cross-sheet manager resolution in single.
  • A11y: extend test:a11y to the new wizard steps (axe on the mapping table + tab-strategy + preview grid).
  • Existing csv-parser / Step 0 spreadsheet tests still green after the rowsToPeople override change.
  • npm run typecheck (via npm) + test:ci green; coverage not decreased.

Acceptance criteria

  1. Uploading a CSV/XLSX in the wizard shows a mapping screen with pre-filled, correctable column→field guesses and a live preview — no silent import.
  2. Low/no-confidence columns are visibly flagged; the user can remap, ignore, or send a column to a custom field.
  3. A multi-sheet workbook offers one org (tab→Department) vs one chart per sheet, defaulting per the schema heuristic with the reason shown; excluded sheets are skipped.
  4. Committing creates the chart(s) with the confirmed mapping; manager lines resolve; row-level problems appear in an accessible summary (never “check console”).
  5. Import sessions are authed, size-capped, and cleaned up; nothing is left public.
  6. New routes work on both Node and Lambda; test:a11y covers the new UI; all tests + typecheck pass.
  7. The developer REST API’s one-shot structured import still works unchanged.

Runtime & deploy

Node + Lambda (both Node runtimes); Redis + R2 as above. Register routes in both entry points. New shared R2_PATHS entry → rebuild @org-chart/shared; new workers/api deps (none expected beyond Step 0’s SheetJS) follow the Dockerfile workspace-deps rule. After merge: Lambda/CF auto-deploy; Node needs npm run rebuild on 10.1.1.4; smoke-test a multi-sheet .xlsx and a messy-header .csv in prod.

Sequencing

Ship as one PR off feature/org-chart-import-mapping (branch from main after Step 0 merges). This unblocks Step 2 (org/view split), which reuses createChartFromPeople and the session/commit plumbing.