# Current Database State ## Implementation Status (v2.0-beta) This document describes the **current implementation** of the Zellij database. For the target architecture and future plans, see [Database Design](/docs/architecture/database-design). ### What's Implemented ✅ - Core entities (institutions, actors, projects, fields, models, collections) - Pattern deduplication and sharing - Legacy system integration (Airtable imports) - Modern ontology system (RDFS/OWL support) - Basic authentication and authorization - Project-pattern associations ### What's Planned 🚧 - Version control (branches, commits, changes) - Social features (stars, watches, issues) - Advanced RBAC with Casbin - Pull requests and merges - Notifications system - API rate limiting ## Current Entity Relationship Diagram This diagram shows the actual tables and relationships currently in the production database: ```mermaid erDiagram %% Core Entities institutions ||--o{ actors : "affiliates" actors ||--o{ projects : "owns" actors ||--o{ fields : "authors" actors ||--o{ models : "authors" actors ||--o{ collections : "authors" %% Project Relationships projects ||--o{ project_fields : "contains" projects ||--o{ project_models : "contains" projects ||--o{ project_collections : "contains" projects ||--o{ categories : "has" %% Pattern Relationships fields ||--o{ project_fields : "used_in" fields ||--o{ model_fields : "used_by" fields ||--o{ collection_fields : "used_by" fields }o--|| categories : "categorized_as" models ||--o{ project_models : "used_in" models ||--o{ model_fields : "contains" collections ||--o{ project_collections : "used_in" collections ||--o{ collection_fields : "contains" %% Legacy System Integration projects ||--o{ project_legacy_ontologies : "uses" projects ||--o{ project_legacy_ontology_classes : "uses" projects ||--o{ project_legacy_vocabularies : "uses" projects ||--o{ project_legacy_control_lists : "uses" legacy_ontologies ||--o{ project_legacy_ontologies : "referenced_by" legacy_ontologies ||--o{ legacy_ontology_classes : "contains" legacy_ontology_classes ||--o{ project_legacy_ontology_classes : "referenced_by" legacy_vocabularies ||--o{ project_legacy_vocabularies : "referenced_by" legacy_control_lists ||--o{ project_legacy_control_lists : "referenced_by" %% New Ontology System ontologies ||--o{ ontology_versions : "has_versions" ontology_versions ||--o{ ontology_classes : "defines" ontology_versions ||--o{ ontology_properties : "defines" ontology_versions ||--o{ project_ontology_versions : "used_in" projects ||--o{ project_ontology_versions : "uses" %% Services projects ||--o{ services : "provides" %% User tables (excluded auth_* for clarity) actors ||--o{ user_projects : "member_of" projects ||--o{ user_projects : "has_members" ``` ## Core Entity Details ### Institutions - Represents organizations that actors are affiliated with - Links to actors via institution_id foreign key ### Actors - Central user/organization entity - Can be either person or organization (actor_type) - Links to institution for affiliation ### Projects - Main organizing entity for semantic patterns - Contains fields, models, collections - Has visibility settings (public/private) - Supports multiple ontologies and vocabularies ### Fields - Atomic semantic units (properties, attributes) - Can be shared across projects - Categorized for organization - Include metadata from original imports ### Models - Complex semantic structures (classes, entities) - Composed of multiple fields with cardinality - Project-scoped definitions ### Collections - Named groups of fields and models - Vocabulary-like groupings - Can span multiple models ## Relationship Tables ### Project Associations - `project_fields`: Links fields to projects - `project_models`: Links models to projects - `project_collections`: Links collections to projects - `project_ontology_versions`: Links ontology versions to projects - `project_legacy_*`: Links legacy entities to projects ### Pattern Compositions - `model_fields`: Fields within models (with cardinality) - `collection_fields`: Fields within collections ## Ontology System ### Modern Ontology Support (RDFS/OWL) - `ontologies`: Base ontology definitions - `ontology_versions`: Versioned ontology content - `ontology_classes`: Class definitions per version - `ontology_properties`: Property definitions per version - Supports full RDFS/OWL semantics with JSONB metadata ### Legacy Ontology Support - `legacy_ontologies`: Imported from Airtable - `legacy_ontology_classes`: Class definitions from legacy system - `legacy_vocabularies`: Controlled vocabularies - `legacy_control_lists`: Value lists for fields ## Key Design Patterns ### 1. Deduplication - Fields are deduplicated across projects (82% reduction) - Shared fields stored once, linked to multiple projects - Original project associations preserved in metadata ### 2. Soft Deletes - All primary entities use `deleted_at` timestamps - Maintains referential integrity - Enables recovery and audit trails ### 3. JSONB Metadata - Flexible storage for variable attributes - Preserves original import data - Stores translations and multilingual content ### 4. UUID Primary Keys - All tables use UUID primary keys - Supports distributed systems - Avoids ID conflicts ## Database Statistics (Current) | Entity Type | Count | Notes | |------------|-------|-------| | Institutions | 48 | Organizations | | Actors | 10 | Users/Orgs | | Projects | 40 | Active projects | | Fields | 4,769 | Deduplicated from 26,518 | | Models | 565 | Semantic models | | Collections | 233 | Field groupings | | Categories | 146 | Field categories | | Ontologies | 2 | Modern RDFS/OWL | | Legacy Ontologies | 46 | From Airtable | | Legacy Classes | 1,443 | Ontology classes | | Legacy Vocabularies | 234 | Controlled terms | ## Access Patterns ### Common Queries 1. **Project Dashboard**: Projects → Fields/Models/Collections 2. **Pattern Explorer**: Fields → Categories, Models → Fields 3. **Ontology Browser**: Ontologies → Versions → Classes/Properties 4. **Legacy Integration**: Projects → Legacy Ontologies/Vocabularies ### Performance Considerations - Indexes on identifiers and foreign keys - JSONB GIN indexes for metadata queries - Composite indexes for common join patterns - Full-text search indexes on name/description fields ## Migration Notes ### From Airtable (Phase 1) - All data successfully imported - Deduplication reduced storage by 82% - Legacy relationships preserved ### Ontology System (Phase 2) - Added modern RDFS/OWL support - Maintains backward compatibility with legacy system - Supports ontology versioning and project associations ### Upcoming (Phase 3) - Version control tables (branches, commits, changes) - Social features (stars, watches, issues) - Advanced RBAC with Casbin