Data
Five-table schema
The canonical data model, with ER diagram and rationale.
The entire domain lives in five core tables + two auth tables. No ORM magic, no per-type tables, no NoSQL side-store.
erDiagram
USERS ||--o{ ENTITIES : owns
USERS ||--o{ EDGES : owns
USERS ||--o{ EVENTS : owns
USERS ||--o{ FACTS : owns
USERS ||--o{ SOURCES : owns
USERS ||--o{ OAUTH : has
SOURCES ||--o{ ENTITIES : provenance
SOURCES ||--o{ EDGES : provenance
SOURCES ||--o{ EVENTS : provenance
ENTITIES ||--o{ EDGES : participates
FACTS ||--o| FACTS : superseded_by
ENTITIES {
uuid id PK
uuid user_id FK
text type
text name
jsonb properties
vector embedding
tsvector search_vector
text status
}
EDGES {
uuid id PK
uuid from_id FK
uuid to_id FK
text type
timestamptz valid_from
timestamptz valid_to
}
EVENTS {
uuid id PK
text type
timestamptz occurred_at
jsonb properties
}
FACTS {
uuid id PK
text statement
text category
real confidence
uuid superseded_by
vector embedding
}
SOURCES {
uuid id PK
text kind
text external_id
jsonb raw_payload
}Why these five
| Table | Role | Key property |
|---|---|---|
| entities | Nouns — anything the user cares about | type discriminator + type-specific properties jsonb |
| edges | Typed relations | bitemporal (valid_from / valid_to) — historical reasoning, soft-delete, past states |
| events | Time-stamped occurrences | entity_ids uuid[] + GIN index → "events involving X" in O(log n) |
| facts | Extracted durable claims | append-on-update via superseded_by. Old facts are never mutated, just superseded |
| sources | Provenance | Unique on (kind, external_id) → idempotent ingestion |
Auth-adjacent tables
- users — mirror of
auth.users(Supabase Auth), kept in sync by a trigger inpackages/db/rls/auth-mirror.sql. Holds non-auth attributes likedisplay_name,timezone. - oauth_connections — per-(user, provider) OAuth tokens. Tokens are AES-GCM-256 encrypted with
OAUTH_ENCRYPTION_KEYat rest.
Entity types
The canonical types live in @soma/core:
book,person,note,project,habit,workout
Adding a new type is a three-line change: extend the enum in @soma/core/entities/types.ts, add a Zod schema for its properties, done. No migration required.