relationship_edges

Overview

The relationship_edges table stores labeled relationship connections between users. Each edge links two users with a relationship type, optional label, notes, and a since-date. Edges support client-side encryption for sensitive relationship data.

Relevant Invariants

• Invariant #7: "Human-Scale Social Contexts" -- Edges help users understand and organize their relationships
• Invariant #14: "Privacy Is Infrastructure" -- Encryption support for sensitive relationship data
• Invariant #16: "AI Never Observes Individuals" -- Encrypted data is opaque to the system

Schema

-- Pre-existing table with additions from 20260205_relationship_encryption.sql
CREATE TABLE relationship_edges (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  from_user_id UUID REFERENCES auth.users(id) NOT NULL,
  to_user_id UUID REFERENCES auth.users(id) NOT NULL,
  type_id UUID REFERENCES relationship_types(id),
  type_name TEXT,
  label TEXT,
  notes TEXT,
  since_date DATE,
  visibility TEXT DEFAULT 'private',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  -- Encryption (20260205_relationship_encryption.sql)
  label_encrypted TEXT,
  notes_encrypted TEXT,
  is_encrypted BOOLEAN DEFAULT FALSE
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	uuid_generate_v4()	Primary key
from_user_id	uuid	No	--	User who created the edge
to_user_id	uuid	No	--	User the edge points to
type_id	uuid	Yes	--	Reference to relationship_types
type_name	text	Yes	--	Denormalized type name
label	text	Yes	--	Custom label for this connection
notes	text	Yes	--	Private notes about the relationship
since_date	date	Yes	--	When the relationship began
visibility	text	No	'private'	Visibility setting
created_at	timestamptz	No	NOW()	Creation timestamp
updated_at	timestamptz	No	NOW()	Last update timestamp
label_encrypted	text	Yes	--	Client-side encrypted label
notes_encrypted	text	Yes	--	Client-side encrypted notes
is_encrypted	boolean	No	FALSE	Whether this edge uses encryption

RLS Policies

-- SELECT: Users can view edges they created
CREATE POLICY "Users can view own relationship edges"
  ON relationship_edges FOR SELECT
  USING (auth.uid() = from_user_id);

-- INSERT/UPDATE/DELETE: Users manage their own edges
CREATE POLICY "Users can manage own relationship edges"
  ON relationship_edges FOR ALL
  USING (auth.uid() = from_user_id);

Related

• relationship_types -- Type definitions for edges
• profiles -- Both parties in the relationship

---

Last updated: 2026-02-07