Data Model
The knowledge graph is the foundation of the entire platform. All data — regardless of which connector brought it in — maps to this unified schema.
v1 Core Entities
Person
persons (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
emails jsonb DEFAULT '[]', -- string[]
phones jsonb DEFAULT '[]', -- string[]
addresses jsonb DEFAULT '[]', -- Address[]
tags jsonb DEFAULT '[]', -- string[] (e.g. ['family', 'work', 'vip'])
birthday date,
notes text,
avatar_url text,
sources jsonb DEFAULT '[]', -- [{connectorId, externalId, lastSynced}]
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Organization
organizations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
domain text,
industry text,
address jsonb,
phone text,
website text,
sources jsonb DEFAULT '[]',
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Channel
channels (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
plugin_id text NOT NULL, -- e.g. 'io.twilio.sms'
type text NOT NULL, -- 'sms' | 'voice' | 'video' | 'email' | 'whatsapp'
name text NOT NULL, -- e.g. 'My Twilio Number (+1 727...)'
capabilities jsonb DEFAULT '{}', -- {canSend, canReceive, canCall, canVideo}
config jsonb DEFAULT '{}', -- plugin-specific config (phone number, etc.)
enabled boolean DEFAULT true,
created_at timestamptz DEFAULT now()
)
Conversation
conversations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text,
channel_id uuid REFERENCES channels(id),
type text NOT NULL, -- 'sms' | 'voice' | 'video' | 'email' | 'chat'
participants jsonb DEFAULT '[]', -- uuid[] (Person ids)
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Message
messages (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
conversation_id uuid REFERENCES conversations(id),
sender_person_id uuid REFERENCES persons(id),
direction text NOT NULL, -- 'inbound' | 'outbound'
body text,
content_type text DEFAULT 'text/plain', -- 'text/plain' | 'audio/transcript' | 'text/html'
timestamp timestamptz NOT NULL,
provider_message_id text, -- external ID (Twilio SID, Graph message ID)
metadata jsonb DEFAULT '{}',
created_at timestamptz DEFAULT now()
)
Attachment
attachments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
message_id uuid REFERENCES messages(id),
storage_path text NOT NULL, -- path in Supabase Storage
mime_type text NOT NULL,
filename text,
size_bytes int,
created_at timestamptz DEFAULT now()
)
Event (Calendar)
events (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text NOT NULL,
description text,
start_time timestamptz NOT NULL,
end_time timestamptz,
location text,
location_geo point,
attendees jsonb DEFAULT '[]', -- Person ids
organizer_id uuid REFERENCES persons(id),
all_day boolean DEFAULT false,
recurrence jsonb,
sources jsonb DEFAULT '[]',
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Location
locations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text, -- 'Home', 'Work', 'Gym'
address text,
coordinates point, -- PostGIS or simple point
type text, -- 'home' | 'work' | 'venue' | 'device-fix'
phone text, -- useful for "call the store" scenarios
radius_meters int DEFAULT 100, -- geofence radius
created_at timestamptz DEFAULT now()
)
Location History (time-series)
location_history (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
device_id uuid REFERENCES devices(id),
lat float8 NOT NULL,
lon float8 NOT NULL,
accuracy_m float4,
altitude_m float4,
speed_mps float4,
wifi_ssid text,
recorded_at timestamptz NOT NULL, -- indexed for time-range queries
created_at timestamptz DEFAULT now()
)
-- Index: CREATE INDEX ON location_history (device_id, recorded_at DESC)
-- TODO: migrate to TimescaleDB hypertable for compression + fast range queries
Task
tasks (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text NOT NULL,
description text,
due_date timestamptz,
status text DEFAULT 'pending', -- 'pending' | 'in_progress' | 'done' | 'cancelled'
priority text DEFAULT 'medium', -- 'low' | 'medium' | 'high' | 'urgent'
assignee_id uuid REFERENCES persons(id),
related_event uuid REFERENCES events(id),
sources jsonb DEFAULT '[]',
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Document
documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text NOT NULL,
content text, -- full text content (for indexing)
storage_path text, -- path in Supabase Storage
mime_type text,
created_by uuid REFERENCES persons(id),
source text, -- 'onedrive' | 'local' | 'windows-agent' | etc.
source_id text,
tags jsonb DEFAULT '[]',
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Device
devices (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
platform text NOT NULL, -- 'android' | 'ios' | 'windows' | 'macos'
owner_id uuid, -- references auth.users
device_token text UNIQUE, -- for authenticating device events
last_seen_at timestamptz,
created_at timestamptz DEFAULT now()
)
Rule
rules (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL,
description text,
enabled boolean DEFAULT true,
trigger_event_types text[] NOT NULL, -- ['communication.CallStarted']
condition_ast jsonb NOT NULL, -- serialized condition tree
actions jsonb[] NOT NULL, -- list of action descriptors
priority int DEFAULT 100, -- lower = higher priority
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Job Record (audit / DLQ UI)
job_records (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
job_type text NOT NULL,
queue text NOT NULL, -- 'realtime' | 'interactive' | 'background'
payload jsonb NOT NULL,
status text DEFAULT 'enqueued', -- 'enqueued' | 'running' | 'completed' | 'failed' | 'dead'
attempts int DEFAULT 0,
last_error text,
triggered_by_event_id text,
correlation_id text,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Audit Log
audit_log (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
actor_type text NOT NULL, -- 'user' | 'plugin' | 'service' | 'system'
actor_id text NOT NULL, -- userId, pluginId, or service name
action text NOT NULL, -- 'read' | 'write' | 'delete' | 'publish' | 'execute'
resource_type text NOT NULL, -- entity type or event type
resource_id text,
outcome text NOT NULL, -- 'allowed' | 'denied'
metadata jsonb DEFAULT '{}',
occurred_at timestamptz DEFAULT now()
)
-- This table is append-only. No updates or deletes. Enforce with RLS.
User Credentials (Metadata)
user_credentials (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
credential_type text NOT NULL, -- 'twilio' | 'microsoft365' | 'openrouter' | 'ollama'
display_info text, -- non-secret hint (e.g., phone number, tenant ID)
is_active boolean DEFAULT true,
vault_keys text[] NOT NULL, -- references to Supabase Vault secret names
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
UNIQUE (owner_id, credential_type)
)
-- RLS: users see only their own credentials
-- Note: actual secret values are stored in Supabase Vault (pgsodium encryption), not here
Key Relationships
Person ──── works_at ────► Organization
Person ──── sent ────────► Message
Person ──── attended ────► Event
Person ──── owns ────────► Document
Message ──── part_of ───► Conversation
Message ──── has ────────► Attachment
Conversation ──── via ──► Channel
Device ──── owned_by ───► (auth user)
Device ──── emits ──────► location_history
User ───── owns ──────► user_credentials ── references ──► Supabase Vault
Vector Search (pgvector)
All textual content is embedded at write time for semantic search:
embeddings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
entity_type text NOT NULL, -- 'message' | 'document' | 'person' | etc.
entity_id uuid NOT NULL,
model text NOT NULL, -- embedding model used
embedding vector(1536), -- or 768 for smaller models
created_at timestamptz DEFAULT now()
)
CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops);
Extended Entities
These tables are added by later migrations as their respective features are built. See data-model-complete.md for the full inventory: 177 unique tables across 53 migrations (180 CREATE TABLE statements — three tables are declared twice with IF NOT EXISTS: organizations, intentions, context_snapshots). Key tables below match the actual migration schemas.
Note on core schemas above: the illustrative DDL in the "v1 Core Entities" section omits the
owner_id uuid NOT NULL REFERENCES auth.users(id)column that every user-owned table actually carries insupabase/migrations/00001_initial_schema.sql. Every core entity is RLS-scoped byowner_id.
Marketplace: Listing
listings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
listing_type text NOT NULL, -- 'data_product' | 'service' | 'physical' | 'digital'
title text NOT NULL,
description text,
price_amount numeric(12,2),
price_currency text DEFAULT 'USD',
price_type text, -- 'fixed' | 'hourly' | 'negotiable' | 'free' | 'time_credit'
status text NOT NULL DEFAULT 'draft', -- 'draft' | 'active' | 'paused' | 'sold' | 'expired'
tags text[] DEFAULT '{}',
location_pref text,
visibility text DEFAULT 'public',
data_product_id uuid REFERENCES data_products(id),
federated_ap_id text,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Marketplace: Data Product
data_products (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
name text NOT NULL,
description text,
query_def jsonb NOT NULL,
privacy_policy jsonb NOT NULL DEFAULT '{}',
output_format text NOT NULL DEFAULT 'json', -- 'json' | 'csv' | 'parquet'
sample_rows integer DEFAULT 0,
schema_json jsonb,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Resource Coordination: Declaration
declarations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
decl_type text NOT NULL, -- 'offer' | 'need' | 'willing_to_do'
category text,
title text NOT NULL,
description text,
exchange_type text, -- 'reciprocal' | 'gift' | 'time_banking' | 'monetary'
location_pref text,
expires_at timestamptz,
status text DEFAULT 'active',
federated_ap_id text,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
exchanges (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
offer_decl_id uuid REFERENCES declarations(id),
need_decl_id uuid REFERENCES declarations(id),
offer_user_id uuid REFERENCES auth.users(id),
need_user_id uuid REFERENCES auth.users(id),
status text DEFAULT 'proposed',
exchange_type text,
notes text,
completed_at timestamptz,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Reputation: Trust and Scores
trust_relationships (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
trusted_did text NOT NULL,
weight float4 DEFAULT 1.0,
context text,
created_at timestamptz DEFAULT now()
)
reputation_scores (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
domain text NOT NULL, -- 'marketplace' | 'governance' | 'resource_sharing' | etc.
score float8,
vc_count int DEFAULT 0,
last_computed timestamptz DEFAULT now()
)
Governance: DAOs and Proposals
daos (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
name text NOT NULL,
description text,
slug text UNIQUE,
dao_type text DEFAULT 'direct',
quorum_pct int DEFAULT 51,
pass_threshold int DEFAULT 51,
voting_period_h int DEFAULT 168,
is_public boolean DEFAULT true,
treasury_credits numeric(18,4) DEFAULT 0,
federated_ap_id text,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
dao_members (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
dao_id uuid REFERENCES daos(id),
user_id uuid REFERENCES auth.users(id),
role text DEFAULT 'member',
joined_at timestamptz DEFAULT now()
)
proposals (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
dao_id uuid REFERENCES daos(id),
author_id uuid REFERENCES auth.users(id),
title text NOT NULL,
description text,
proposal_type text,
status text DEFAULT 'draft',
action_payload jsonb,
votes_yes int DEFAULT 0,
votes_no int DEFAULT 0,
votes_abstain int DEFAULT 0,
quorum_reached boolean,
voting_opens_at timestamptz,
voting_closes_at timestamptz,
executed_at timestamptz,
federated_ap_id text,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Data Portability Engine
Users can export their entire dataset at any time in open, machine-readable formats. No lock-in is architecturally possible — the data is in standard PostgreSQL tables, and the export produces portable formats.
Export formats: JSON, CSV, Markdown, ActivityPub JSON-LD
What gets exported: All entities (persons, messages, events, documents, location history, etc.) plus metadata (sources, created/updated timestamps, audit log).
Migration support: Exported data can be imported into any other Made Open hub instance. The import process maps entity IDs (to avoid collisions) and reconstructs the knowledge graph.
Implementation: A Data Portability Service module in the hub, accessible via:
POST /api/export Initiate export job (async, background queue)
GET /api/export/:jobId Check export status / download when ready
Deep Communications Entities
These tables extend the model with a full communications stack. All new tables carry owner_id + RLS and participate in the same audit and embedding pipelines as core entities.
Inbox Item
inbox_items (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
channel_type text NOT NULL, -- 'sms' | 'voice' | 'email' | 'chat' | 'push'
conversation_id uuid REFERENCES conversations(id),
subject text,
preview text,
sender_person_id uuid REFERENCES persons(id),
read_at timestamptz,
archived_at timestamptz,
snoozed_until timestamptz,
metadata jsonb DEFAULT '{}',
created_at timestamptz DEFAULT now()
)
Phone Line + Voicemail
phone_lines (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
phone_number text NOT NULL,
provider text NOT NULL, -- 'twilio' | 'sip'
friendly_name text,
capabilities jsonb DEFAULT '{}', -- {voice, sms, mms}
active boolean DEFAULT true,
created_at timestamptz DEFAULT now()
)
voicemails (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
phone_line_id uuid REFERENCES phone_lines(id),
caller_number text,
caller_person_id uuid REFERENCES persons(id),
duration_secs int,
storage_path text NOT NULL, -- audio file in Supabase Storage
transcript text, -- populated by TranscriptionService
listened_at timestamptz,
created_at timestamptz DEFAULT now()
)
Email Folders + Threads
email_folders (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
name text NOT NULL,
folder_type text DEFAULT 'custom', -- 'inbox' | 'sent' | 'drafts' | 'trash' | 'custom'
provider_id text, -- external folder ID (IMAP, Graph)
unread_count int DEFAULT 0,
created_at timestamptz DEFAULT now()
)
email_threads (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
folder_id uuid REFERENCES email_folders(id),
subject text,
participant_ids uuid[] DEFAULT '{}', -- Person ids
last_message_at timestamptz,
unread_count int DEFAULT 0,
provider_thread_id text, -- external thread ID
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Meetings
meetings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
title text NOT NULL,
room_name text NOT NULL UNIQUE,
scheduled_start timestamptz,
scheduled_end timestamptz,
status text NOT NULL DEFAULT 'scheduled', -- 'scheduled' | 'active' | 'ended'
settings jsonb NOT NULL DEFAULT '{}',
calendar_event_id uuid REFERENCES events(id) ON DELETE SET NULL,
invite_link text,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
)
-- AI-generated summaries, transcripts, recordings, and action items live in
-- companion tables: meeting_analyses, meeting_recordings, meeting_participants.
meeting_participants (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
meeting_id uuid REFERENCES meetings(id),
person_id uuid REFERENCES persons(id),
role text DEFAULT 'attendee', -- 'host' | 'attendee' | 'optional'
joined_at timestamptz,
left_at timestamptz
)
Contact Notes
contact_notes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
person_id uuid NOT NULL REFERENCES persons(id),
note_type text DEFAULT 'manual', -- 'manual' | 'ai_summary' | 'reminder' | 'meeting_followup'
body text NOT NULL,
source_entity_type text, -- 'meeting' | 'message' | 'call'
source_entity_id uuid,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Device Push Tokens
device_push_tokens (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id uuid NOT NULL REFERENCES auth.users(id),
device_id uuid REFERENCES devices(id),
platform text NOT NULL, -- 'fcm' | 'apns'
token text NOT NULL UNIQUE,
topic_subscriptions text[] DEFAULT '{}', -- NATS subject prefixes to fan out
last_delivered_at timestamptz,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
)
Cross-Channel Conversation Threading
All channel types — SMS, voice, email, and chat — share the same conversations + messages spine. Deep communications layers add channel-specific satellite tables (email_threads, voicemails, meetings) but always reference back to conversations for unified thread history.
inbox_items ──── conversation_id ────► conversations
│
┌─────────────────────┼──────────────────────┐
▼ ▼ ▼
messages email_threads voicemails
(channel='sms') (folder_id) (phone_line_id)
meetings ──── event_id ────► events (calendar)
meetings ──── meeting_participants ────► persons
contact_notes ──── person_id ────► persons
contact_notes ──── source_entity_id ──► messages | meetings | voicemails
Threading rules:
- Every inbound communication produces an
inbox_itemsrow pointing to aconversation_id - If a thread already exists for the same participants + channel, the new item appends to it; otherwise a new
conversationsrow is created - Email threads use
email_threadsas a folder-aware grouping layer while the underlying messages still land inmessageswithcontent_type = 'text/html' - Voicemail transcripts are stored in
voicemails.transcriptand also pushed as amessagesrow (content_type = 'audio/transcript') so they appear in conversation history - Meeting notes and AI summaries live in
meeting_analyses(linked tomeetings) and are mirrored ascontact_notes(typemeeting_followup) per participant for the per-contact timeline view
Extensibility
Connector plugins can define new entity types by creating additional tables and registering their schemas with the Data Service. All plugin-defined entities follow the same id + sources + created_at + updated_at pattern.