Relational Virtual Tables

Source: This page is based on docs/PROPOSALS/RELATIONAL_VIRTUAL_TABLES.md

Last Updated: January 1, 2026

Relational Virtual Tables are how we let people "link records" between Eddy Sheets, without turning our whole data layer into full SQL joins.

The big idea: The Sheet stays simple (cells store JSON), but we maintain a fast side-index so queries don't crawl.

Core Concepts

What a "relationship" is in Eddy

A relationship is a column type that stores links from a row in one sheet to rows in another sheet.

The cell value always looks like this:

{ "ids": ["row_uuid_1", "row_uuid_2"] }

Even if the max is 1, it's still an array under ids. Less mental overhead, fewer edge cases.

Relationship configuration lives on the column

We already have columns.type and columns.formula, and we store config in JSON via columns.options. We extend that.

For a relationship column we add:

target_sheet_id — what sheet we link to
inverse — whether we also write a real column on the other sheet
inverse_column_id — the actual column we created on the target sheet
max_links — "many" or a number (including 1)
(phase 2) min_links — 0..n for integrity rules

Inverse behaviour

We create a real column on the other table (an inverse relationship column), rather than a "virtual backlink".

Why:

Users can see it
It behaves like Airtable's linked records
It gives us a clean place to enforce symmetry

Rule: If either column is deleted/archived, the link is considered broken and we remove the inverse references too.

The "sidecar" (why we need it)

If relationships only live inside cells.value, then "find all rows that link to X" becomes painful because JSON arrays are not a normal indexed FK path.

So we add a sidecar index table (think: an edge table):

It's derived data
It's cheap to query
It gives us indexes for relationship lookups

End-to-End Implementation

Configuration (Builder experience)

Create a relationship column
- In Sheet UI, user chooses "Relationship" as columns.type
Choose target
- Select target_sheet_id
Choose max links
- Options: many, 1, 2, 3, 5, etc.
Inverse column is created automatically
- We create a matching relationship column on the target sheet:
  - name: something predictable like "Linked from <Sheet Name> (<Column Name>)"
  - type: relationship
  - target_sheet_id: back to source
  - max_links: usually many (unless we want strict symmetry)
Store both ends
- Source column gets inverse_column_id
- Inverse column gets inverse_column_id back to the source

This keeps the relationship definition explicit and debuggable.

Writes (when a user links/unlinks rows)

When a relationship cell changes, we do two things:

Persist the cell value as usual
- cells.value = { ids: [...] }
Update the sidecar edge index (transactionally)

We maintain an "edges" table that contains one row per link:

Table: sheet_relationship_edges (new)

id uuid
group_id uuid
source_sheet_id uuid
source_row_id uuid
source_column_id uuid
target_sheet_id uuid
target_row_id uuid
inverse_column_id uuid (nullable but usually set)
created_at

Indexes we care about:

(source_column_id, source_row_id) — "what does this row link to?"
(target_row_id) — "who links to this row?"
(target_sheet_id, target_row_id) — common lookup
optionally (group_id, target_row_id) for multi-tenant safety

How we update it:

On save:
- compute oldIds vs newIds
- delete edges for removed ids
- insert edges for added ids
- Wrap in the same DB transaction as the cells update

Update the inverse column (so users see the backlink)

Because we chose "real inverse column" behaviour:

for every added link A → B, we also ensure B's inverse cell includes A
for every removed link, we remove A from B's inverse cell

If either side's column is archived/deleted:

we stop maintaining symmetry
and we remove edges + inverse references that relied on it

Reads (querying, filtering, views)

Basic queries:

// Get all rows that this row links to
SELECT * FROM rows
WHERE id IN (
  SELECT target_row_id FROM sheet_relationship_edges
  WHERE source_row_id = :rowId
  AND source_column_id = :columnId
)

// Get all rows that link to this row
SELECT * FROM rows
WHERE id IN (
  SELECT source_row_id FROM sheet_relationship_edges
  WHERE target_row_id = :rowId
  AND inverse_column_id = :columnId
)

For views/filters:

"Show me all Projects where Status = Active"
"Show me all Tasks linked to Project X"
These become joins on the edges table

Relationship Lifecycle

Column deletion / archiving

When a relationship column is deleted or archived:

Delete all edges where source_column_id = deleted column
Update all inverse cells to remove references
Optionally delete the inverse column (if it was auto-created)

Row deletion / archiving

When a row is deleted or archived:

Delete all edges where source_row_id or target_row_id = deleted row
Update all linked cells to remove the deleted row ID
Check min_links constraints (phase 2)

Required Infrastructure

Schema changes (minimum viable)

New table:

CREATE TABLE sheet_relationship_edges (
  id UUID PRIMARY KEY,
  group_id UUID NOT NULL,
  source_sheet_id UUID NOT NULL,
  source_row_id UUID NOT NULL,
  source_column_id UUID NOT NULL,
  target_sheet_id UUID NOT NULL,
  target_row_id UUID NOT NULL,
  inverse_column_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_edges_source ON sheet_relationship_edges(source_column_id, source_row_id);
CREATE INDEX idx_edges_target ON sheet_relationship_edges(target_row_id);
CREATE INDEX idx_edges_target_sheet ON sheet_relationship_edges(target_sheet_id, target_row_id);

Column options extension:

type RelationshipColumnOptions = {
  target_sheet_id: string
  inverse: boolean
  inverse_column_id?: string
  max_links: number | 'many'
  min_links?: number  // phase 2
}

A single "write path" that owns consistency

All relationship updates must go through a single service:

updateRelationshipCell({ sheetId, rowId, columnId, ids })
This service handles:
- Validating max_links
- Updating cells.value
- Updating sheet_relationship_edges
- Updating inverse cells
- All in one transaction