Data Lineage

Overview

Data Lineage visualizes the full journey of data from its source through every transformation until it becomes objects in the ontology. It is rendered inside the Graph editor as a DAG (directed acyclic graph) flowing left-to-right, where each node is a rectangular artifact card representing a stage in the pipeline.

Pipeline Stages

Data flows through the following stages, each represented as a distinct artifact type in the lineage view:

Credential ──→ Dataset ──→ Object Type ──→ Objects
   (source)      (rows)      (schema)     (instances)

Stage 1: Credential / Data Source

The origin of the data. Can be:

Tracked in: api_connections (OAuth), user_credentials (DB/polling)

Lineage fields:

• service_type — the integration type
• credential_type — how data is fetched (oauth, api_key, database)
• metadata.sync_schedule — polling configuration

Stage 2: Sync / Ingestion Process

The mechanism that moves data from the source into a dataset. Represented as a process artifact (not a data artifact).

Tracked in: dataset_transactions table

• type — SNAPSHOT, UPDATE, APPEND
• status — committed, aborted, in_progress
• metadata — row count, size, schema changes
• created_at — when the sync happened

Stage 3: Dataset

The structured data container. Each dataset has rows and a schema.

Tracked in: datasets table

• credential_id — link back to source
• service — source service type
• file_id — project_files reference
• schema — column definitions (JSONB)
• row_count, column_count — stats
• last_sync_at — freshness indicator

Row storage: dataset_rows table

• dataset_id — parent dataset
• transaction_id — which sync produced this row
• row_index — position
• data — actual values (JSONB)

Stage 4: Column Mapping / Property Definition

The schema transformation that maps dataset columns to object type properties. This is where raw columns become typed, named properties.

Tracked in: object_type_properties table

• source_dataset_id — which dataset backs this property
• column_name — source column
• display_name — semantic name in ontology
• data_type — typed (Text, Number, Date, etc.)

Multi-dataset junction: object_type_datasets table

• dataset_id — backing dataset
• key_column — PK mapping for this dataset
• role — 'backing'

Stage 5: Object Type

The ontology schema definition. Created from one or more datasets.

Tracked in: object_types table

• dataset_id — primary dataset (legacy)
• primary_key_column — how objects are identified
• title_column — how objects are named
• source_service — origin service

Stage 6: Objects (Instances)

The materialized instances. Each row from backing datasets becomes an object.

Tracked in: objects table

• source_dataset_ids — array of ALL datasets that contributed
• dataset_row_id — specific row that created this object
• property_overrides — merged properties from enrichment datasets
• created_by — 'dataset-sync', 'manual', 'ai'

Stage 7: Links (Relationships)

Links between objects, backed by link types.

Tracked in: object_links table

• link_type_id — the schema definition
• source_object_id, target_object_id
• created_by — 'manual', 'ai', 'fk-resolution'

Artifact Card Types

Each stage is rendered as a rectangular card in the lineage view. Cards have distinct visual styles per type:

Card Content

Each card displays:

• Name — artifact name (dataset name, object type name, etc.)
• Type badge — small label indicating the artifact type
• Stats — relevant counts (row count, object count, property count)
• Expand chevron (>) — click to navigate to the artifact

Edge Styles

Lineage Resolution

Per Object Type

To build the lineage for an object type, resolve backwards:

1. From object_type_datasets → get all backing dataset_ids
2. For each dataset → get credential_id from datasets table
3. For each credential → get service_type, connection metadata
4. For each dataset → get recent transactions from dataset_transactions
5. From object_type_properties → get source_dataset_id mappings
6. From link_types → get relationships to other object types
7. Build the DAG from credentials (left) → datasets → mappings → object type (right)

Per Object

To build lineage for a specific object:

1. From objects.source_dataset_ids → get contributing datasets
2. From objects.dataset_row_id → get the specific row + transaction
3. From transaction → get sync type, timestamp
4. Continue backwards same as object type lineage
5. Additionally: show property_overrides to see which values came from which dataset

Data Model for Lineage Storage

The lineage data does not need a separate table — it is reconstructed on-demand from existing relationships:

API Endpoint Design

GET /api/lineage?objectTypeId=X&userId=Y

Returns the full lineage DAG for an object type:

interface LineageResponse {
  nodes: LineageNode[];
  edges: LineageEdge[];
}

interface LineageNode {
  id: string;
  type: 'credential' | 'sync' | 'dataset' | 'time_series' | 'mapping' | 'object_type' | 'link_type';
  name: string;
  metadata: {
    service?: string;        // credential
    rowCount?: number;       // dataset
    columnCount?: number;    // dataset
    lastSyncAt?: string;     // dataset
    syncType?: string;       // sync process
    objectCount?: number;    // object type
    propertyCount?: number;  // object type / mapping
    cardinality?: string;    // link type
    color?: string;          // object type
    icon?: string;           // object type
  };
  // Position hints (depth from right, computed by layout)
  depth: number;  // 0 = object type (rightmost), 1 = dataset, 2 = credential, etc.
}

interface LineageEdge {
  id: string;
  sourceId: string;  // upstream node
  targetId: string;  // downstream node
  type: 'sources' | 'backs' | 'maps_to' | 'links_to' | 'syncs';
  metadata?: {
    keyColumn?: string;       // PK mapping
    propertyCount?: number;   // how many properties from this edge
  };
}

Layout Algorithm

The lineage view uses a layered DAG layout (left-to-right):

1. Layer assignment: Each node gets a depth based on its distance from the target object type

   • Object Type: depth 0 (rightmost)
   • Datasets: depth 1
   • Sync processes: depth 2
   • Credentials: depth 3
   • Related Object Types (via links): depth 0 (same layer, separate row)

2. Vertical ordering: Nodes within the same layer are ordered to minimize edge crossings

3. Edge routing: Edges flow left-to-right, with bezier curves for crossing avoidance

4. Grouping: Datasets from the same credential are grouped vertically

Integration with Graph Editor

The lineage view is a mode of the existing Graph editor, not a separate page:

• User clicks "View Data Lineage" from object type context menu or toolbar
• Graph switches to lineage mode, loading artifacts as rectangular cards
• Existing graph nodes (objects) can coexist with lineage artifacts
• Lineage artifacts are read-only (no drag to create links)
• Click on a card navigates to that resource (dataset, credential config, etc.)

Stack

• Frontend: React component rendering SVG cards in the Graph canvas
• Backend: Next.js API route /api/lineage that resolves the DAG from existing tables
• No new tables needed: All lineage data is derived from existing foreign key relationships
• Layout: Dagre or custom layered layout algorithm for left-to-right DAG positioning