Database Schema
Complete schema documentation with realms, signals, clusters, and synthesis.
Schema Overview
Autonomy's database schema is designed with three core principles:
- Epistemic integrity — The schema enforces truth about data ownership and relationships
- Multi-tenant isolation — Every entity belongs to a realm, enforced by foreign keys
- Extensibility — JSON fields allow flexible metadata without schema migrations
Core Models
users
User accounts with authentication and role-based access control.
user_idString (ULID) - Primary keyuser_emailString - Uniqueuser_passwordString - Hashed (bcrypt)user_nameString? - Optional display nameuser_roleString - OWNER, SANCTUM, GUESTis_ownerBoolean - System owner flagrealms
Sovereign territories for signals. Multi-tenant data isolation.
realm_idString (ULID) - Primary keyuser_idString - Foreign key to usersrealm_typeString - PRIVATE, PUBLIC, SHAREDrealm_nameString - Display namerealm_descriptionString? - Optional descriptionflag_registryBoolean - Listed in public registry?realms_users
Many-to-many relationship for realm membership with roles.
realm_idString - Foreign key to realmsuser_idString - Foreign key to usersuser_roleString - OWNER, CONTRIBUTOR, OBSERVERFuture feature for shared realm collaboration
signals
Atomic units of lived data with geospatial and embedding support.
signal_idString (ULID) - Primary keyrealm_idString - Foreign key to realms (REQUIRED)signal_typeString - TEXT, PHOTO, VIDEO, AUDIO, etc.signal_titleString - Brief titlesignal_descriptionString? - Longer descriptionsignal_visibilityString - PUBLIC, PRIVATE, SANCTUM, SHAREDsignal_locationPoint? - PostGIS point (PostgreSQL)signal_latitudeFloat? - Latitude (MySQL)signal_longitudeFloat? - Longitude (MySQL)signal_metadataJSON? - Structured metadatasignal_payloadJSON? - Full content datasignal_tagsJSON? - Array of tagssignal_embeddingVector? - pgvector embeddingstamp_createdDateTime - When created in systemstamp_importedDateTime? - When originally capturedclusters
Hierarchical groupings of signals.
cluster_idString (ULID) - Primary keyrealm_idString - Foreign key to realms (REQUIRED)parent_cluster_idString? - Foreign key to clusters (self-reference)cluster_nameString - Display namecluster_descriptionString? - Descriptioncluster_typeString - TEMPORAL, SPATIAL, THEMATIC, etc.cluster_metadataJSON? - Structured metadatacluster_payloadJSON? - Full cluster datacluster_tagsJSON? - Array of tagscluster_annotationsJSON? - User notesclusters_signals
Many-to-many relationship between clusters and signals with positioning.
cluster_idString - Foreign key to clusterssignal_idString - Foreign key to signalspositionInt? - Order within clustersynthesis
Polymorphic AI analysis layer (attaches to signals or clusters).
synthesis_idString (ULID) - Primary keyrealm_idString - Foreign key to realms (REQUIRED)polymorphic_typeString - "Signal" or "Cluster"polymorphic_idString - ID of target entitysynthesis_typeString - METADATA or REFLECTIONsynthesis_subtypeString - SURFACE/STRUCTURE/PATTERNS or MIRROR/MYTH/NARRATIVEsynthesis_depthInt - Processing depth levelsynthesis_contentJSON? - Generated synthesissynthesis_annotationsJSON? - User notessynthesis_historyJSON? - Audit trailsynthesis_errorsJSON? - Processing errorssynthesis_sourceString? - AI model usedKey Relationships
users
└─ realms (one-to-many)
└─ signals (one-to-many)
└─ clusters (one-to-many)
└─ clusters_signals (many-to-many with signals)
└─ synthesis (one-to-many)
└─ polymorphic to signals OR clusters
realms
└─ realms_users (many-to-many with users)
clusters
└─ parent_cluster (self-referencing for hierarchies)Foreign Key Constraints
All foreign keys cascade on delete, ensuring data integrity:
- Deleting a realm → Deletes all signals, clusters, and synthesis in that realm
- Deleting a cluster → Removes cluster-signal associations (signals remain)
- Deleting a signal → Removes cluster-signal associations
- Deleting a user → Deletes all their owned realms (cascade)
ULID Identifiers
All primary keys use ULID (Universally Unique Lexicographically Sortable Identifier):
- 26 characters long
- Timestamp-based prefix (sortable by creation time)
- Unique across distributed systems
- URL-safe (no special characters)
01HQXYZ123ABC456DEF789GHI0Optional Database Features
PostGIS (PostgreSQL)
Geospatial extension for location-based queries:
- • Distance calculations
- • Radius searches
- • Spatial indexing
- • Geometric operations
pgvector (PostgreSQL)
Vector similarity search for embeddings:
- • Semantic search across signals
- • Find similar content
- • Cluster by meaning
- • Vector indexing
Schema Generation
The schema is generated from TypeScript schema definitions using a custom generator:
npm run db:generate-schemaThis creates schema.prisma which Prisma uses to generate TypeScript types and database migrations.
Benefits:
- Type-safe schema definitions
- Single source of truth in TypeScript
- Automatic Prisma client generation
- Database-agnostic (supports PostgreSQL and MySQL)
Database Migrations
Migration Workflow
- 1. Modify schema in
lib/db/schemas/ - 2. Run
npm run db:generate-schema - 3. Review generated
schema.prisma - 4. Run
npx prisma migrate dev --name description_of_change - 5. Commit migration files to version control