Spec: Tabular Worker — Rows-to-Graph Projection¶

File: .ai/specs/core-engine/2026-07-02-tabular-projection.md Status: Draft Owners: Workers lane Related: PR-036; ingestion-provenance spec; schema-inference spec (type mapping target)

1. Overview¶

Defines CSV/XLSX projection: rows→nodes, columns→properties with polars-driven type coercion into the Value enum, cross-reference (FK-like) edge detection, header handling, and streaming limits for large files.

2. Problem Statement¶

Tabular data is deceptively simple until: headers are missing or duplicated; a "date" column is 3% garbage strings; one file holds two logical tables; a column obviously references another sheet's key but nothing links them; and a 5GB CSV OOMs the worker. Each needs a pinned rule or two ingests of the same file diverge.

3. Proposed Solution¶

One node per data row, labeled by explicit config or derived from (filename|sheet name) sanitized to UPPER_SNAKE. Headers: first row by default; missing/duplicate headers get positional names (col_3) / dedup suffixes (name, name_2). Types: polars inference per column mapped onto the Value enum (Utf8→Str, Int64→Int, Float64→Float, Boolean→Bool, Date/Datetime→Datetime, everything else→Str); per-cell coercion failures store the raw string and increment a per-column dirty_count stat (never fail the row). Cross-references v1: explicit mapping in job payload (refs: [{from_col, to_label, to_col}]) creates typed edges; a conservative heuristic (column name endswith _id/Id AND ≥90% value overlap with a candidate key column in the same ingestion) only suggests refs in the job result stats — it never creates edges uninvited. Streaming: polars lazy scan, chunked at 50k rows, per-chunk Submit batching.

4. Architecture¶

PollJobs(kind=ingest:tabular) → polars lazy scan → per-chunk: project rows → SubmitIngestionResult (multi-submit per job supported; job completes on final chunk flag).

5. Data Models¶

Node label per table; properties per column; row provenance span = the row's byte range in the canonical text (canonical text for tabular = RFC4180-normalized CSV rendering of the sheet, NFC/LF — giving byte-stable spans even for XLSX). Edges from explicit refs: type from config (default REFERS_TO), from row-node to matched row-node. Sheet handling: each XLSX sheet is its own table/label; empty sheets skipped with a stat.

6. API Contracts¶

Job payload: {format: csv|xlsx, label?, header: auto|none|row_n, refs?: [...], delimiter?}. Result stats: rows, dirty_counts per column, suggested_refs, skipped_sheets. Failure taxonomy: corrupt_input, resource_limit (retryable), transient.

7. UI/UX¶

Suggested_refs surface in job status so an operator can re-ingest with explicit refs — the human-in-the-loop path for cross-reference quality.

8. Configuration¶

tabular.chunk_rows (50k), tabular.max_rows (10M), tabular.max_cols (2000), tabular.heuristic_overlap (0.90).

9. Alternatives Considered¶

Auto-creating heuristic edges (rejected: silent wrong joins are worse than missing joins; suggest-then-confirm keeps the graph trustworthy). Columns as nodes (rejected: schema-inference already captures column-level typing; column nodes duplicate that as graph noise). Cell-level spans (rejected: span-per-row balances provenance granularity against ledger size; cell addressing = row span + column name, reconstructible).

10. Implementation Approach¶

PR-036: this spec → worker on harness → polars projection + coercion table → explicit refs + heuristic suggester → chunked streaming with memory ceiling test → cross-ref accuracy fixtures.

11. Migration Path¶

Greenfield. Coercion table changes are projection changes ⇒ spec amendment ⇒ new source versions on re-ingest.

12. Success Metrics¶

1M-row stream under memory ceiling. Determinism (identical file ⇒ identical submission hash). Coercion fixtures: every polars-dtype × dirty-cell case produces the documented Value. Heuristic precision fixture: suggested refs on the fixture set ≥0.9 precision (it suggests, so recall matters less).

13. Open Questions¶

OQ-1: Multi-table detection within one CSV (blank-row-separated blocks)? Stance: out of scope v1 — one file/sheet = one table; blocks ingest as one table with dirty stats flagging the mess.

14. Changelog¶

2026-07-02 — v0.1: initial draft for peer review.
2026-07-03 — v0.2 (PR-036 as built): (1) Unified coercion pipeline: both formats load as all-string tables; per-column target inferred from the first-chunk sample (first 1000 non-empty cells, ≥80% must cast — the threshold tolerates the "3% garbage" case §2 exists for), then non-strict casts with dirty cells keeping the raw string. Boolean lexicon is {true,false} case-insensitive only ("1"/"0" stay ints). Datetimes accept four ISO-8601 shapes (explicit format list, per-cell coalesce) and emit µs-since-epoch ints — JSON cannot express Value::Datetime (ast.rs convention). (2) Header rules implemented by always reading headerless and resolving ourselves: auto = row 0; none = col_; integer n = row n with earlier rows skipped; duplicates suffix _2, _3…. (3) Deviation: single submission per job — core has no partial-submission protocol yet (SubmitIngestionResult completes the job); the chunked polars scan (collect_batches) bounds parse memory, and tabular.max_rows is the output ceiling → resource_limit. Multi-submit streaming is a recorded follow-up. (4) Explicit refs gained optional fromLabel (multi-sheet disambiguation; defaults to the first table) and optional type (default REFERS_TO); unmatched ref values are counted in stats, and the suggester's candidate-key/overlap sets sample the leading 50k rows per table (deterministic cap). (5) Options flow: REST options → job payload options ({label?, header, refs?, delimiter?}).

2026-07-03 — v0.3: multi-submit streaming shipped (retires the v0.2 item-3 deviation). §4 now holds as written: one partial submission per polars chunk (seq/partial protocol, grpc-contracts spec §5), job completes on the final submission. Mechanics: partial builders chain via base_offset, so row spans are GLOBAL offsets into the accumulated canonical text; the server acks each partial with its node ids, which feed the explicit-ref key maps — cross-chunk (and cross-sheet) edges are emitted in the FINAL submission by fromId/toId, resolving refs in either direction across chunk boundaries. Stats ride the final submission. Memory now bounded by chunk size + ref key maps (value → node id for ref target/source columns), not the whole projection; each partial also extends the job lease server-side. Handler contract: handle(job) is a generator of submission dicts driven by workers_common's consumer (ids = yield builder.build()).

2026-07-05 — v0.4: temporal ambiguity detection (§15 addendum, PR-061; temporal-clarification spec §5/§6 is the wire + lifecycle authority). Cell-level detection on ONE designated validity column: reading- ambiguous non-ISO date cells (day-first vs month-first) become conflicting_dates / low_confidence_extraction ambiguities on their row nodes, capped per job. Gated on options.clarify; no severity hints in v1.

15. Addendum — Temporal Ambiguity Detection (PR-061)¶

Detection rules for the ambiguities submission field (temporal-clarification spec §5 owns the wire shape, lifecycle, and binding policy; this addendum owns only the tabular cell rules). Emission is gated on options.clarify == true in the job payload options. No severityHint in v1.

Validity column (the only column examined). Explicit options.clarifyDateColumn (matched against resolved, de-duplicated headers; a miss emits the clarify_column_missing stat and disables detection), else auto-detected: exactly ONE column whose normalized header (lowercased, non-alphanumerics stripped) is in {effectivedate, effective, effectivefrom, effectiveon, validfrom, startdate, dateeffective} — zero or several matches disable detection. Rows are facts; the designated column is when they hold, so a bound answer correcting the row node's valid time is exactly the correction the clarification loop exists to capture. Columns that coerce cleanly (the four ISO shapes, v0.2 above) are unambiguous by construction — detection only ever examines cells the coercion pass left dirty.

Cell rule. A dirty cell in the validity column matching A/B/YYYY or A-B-YYYY (A, B 1–2 digits) is read both ways (month-first: A=month; day-first: A=day), each validated as a real calendar date (µs at UTC midnight):

Both readings valid and different → conflicting_dates, two candidates "YYYY-MM-DD (month-first reading of 'A/B/YYYY')" / "YYYY-MM-DD (day-first reading)". Best guess (first candidate) by column evidence: unambiguous slash/dash cells seen so far in the stream vote (first component > 12 → day-first; second > 12 → month-first); tie or no evidence → month-first. Deterministic for identical input (chunk order is deterministic). detectorConfidence 0.9, appliedCandidateConfidence 0.5.

Exactly one reading valid (e.g. 25/12/2024) → low_confidence_extraction, one candidate, detectorConfidence 0.7, appliedCandidateConfidence 0.75.

Both readings valid and equal (e.g. 6/6/2025) → low_confidence_extraction, one candidate, detectorConfidence 0.7, appliedCandidateConfidence 0.85.

Anything else (not date-like) → no ambiguity; it stays a plain dirty cell in dirty_counts.

validFrom = the reading, validTo absent, spanRef = the row's span, nodeRef = the row node (partial-local index — ambiguities ride the SAME partial submission as their row; core opens clarifications per partial). Empty cells never emit (undated is deliberately out of tabular v1: sparse validity columns would flood stewards; recorded as a revisit-with-usage-data item).

Cap. At most 5 ambiguities per job (constant CLARIFY_MAX_PER_JOB), first-N in row order across the whole stream; suppressed detections are counted in the ambiguities_suppressed stat. Stats ambiguities / ambiguities_suppressed / clarify_column_missing ride the final submission with the other stats.

Multi-sheet + stats pinning (as built, PR-061). Column selection runs PER TABLE (each sheet's resolved headers); evidence counters, the cap, and stats are stream-global. An explicit clarifyDateColumn that misses one sheet disables detection for that sheet (and sets clarify_column_missing) without disabling sheets it matches. A pattern-matching cell with NO valid reading votes both ways (net zero — deterministic, no emission). When the gate is on, ambiguities and ambiguities_suppressed always ride the final submission (including 0); clarify_column_missing only on an actual miss; gate off emits no clarify stats keys at all.